AddAbsences.php 10.1 KB
Newer Older
François Jacquet's avatar
François Jacquet committed
1
<?php
François Jacquet's avatar
François Jacquet committed
2
//FJ move Attendance.php from functions/ to modules/Attendance/includes
3
require_once 'modules/Attendance/includes/UpdateAttendanceDaily.fnc.php';
4

François Jacquet's avatar
François Jacquet committed
5 6
DrawHeader( ProgramTitle() );

7
if ( empty( $_REQUEST['month'] ) )
8 9 10 11
{
	$_REQUEST['month'] = date( 'm' );
}

12
if ( empty( $_REQUEST['year'] ) )
13 14 15
{
	$_REQUEST['year'] = date( 'Y' );
}
François Jacquet's avatar
François Jacquet committed
16
else
17 18 19
{
	$_REQUEST['year'] = ( $_REQUEST['year'] < 1900 ? '20' . $_REQUEST['year'] : $_REQUEST['year'] );
}
François Jacquet's avatar
François Jacquet committed
20

21
if ( $_REQUEST['modfunc'] === 'save' )
François Jacquet's avatar
François Jacquet committed
22
{
23 24 25
	if ( ! empty( $_REQUEST['period'] )
		&& ! empty( $_REQUEST['student'] )
		&& ! empty( $_REQUEST['dates'] ) )
François Jacquet's avatar
François Jacquet committed
26
	{
27
		$periods_list = "'" . implode( "','", array_keys( $_REQUEST['period'] ) ) . "'";
28

29
		$students_list = "'" . implode( "','", $_REQUEST['student'] ) . "'";
François Jacquet's avatar
François Jacquet committed
30

31
		$current_RET = DBGet( "SELECT STUDENT_ID,PERIOD_ID,SCHOOL_DATE
32
		FROM ATTENDANCE_PERIOD
33 34
		WHERE EXTRACT(MONTH FROM SCHOOL_DATE)='" . ( $_REQUEST['month'] * 1 ) . "'
		AND EXTRACT(YEAR FROM SCHOOL_DATE)='" . $_REQUEST['year'] . "'
35
		AND PERIOD_ID IN (" . $periods_list . ")
36
		AND STUDENT_ID IN (" . $students_list . ")", array(), array( 'STUDENT_ID', 'SCHOOL_DATE', 'PERIOD_ID' ) );
37

38
		$state_code = DBGetOne( "SELECT STATE_CODE
39
			FROM ATTENDANCE_CODES
40
			WHERE ID='" . $_REQUEST['absence_code'] . "'" );
41

42
		foreach ( (array) $_REQUEST['student'] as $student_id )
François Jacquet's avatar
François Jacquet committed
43
		{
44
			foreach ( (array) $_REQUEST['dates'] as $date => $yes )
François Jacquet's avatar
François Jacquet committed
45
			{
46 47
				$current_mp = GetCurrentMP( 'QTR', $date );
				$all_mp = GetAllMP( 'QTR', $current_mp );
François Jacquet's avatar
François Jacquet committed
48 49
				//FJ days numbered
				//FJ multiple school periods for a course period
50 51

				if ( SchoolInfo( 'NUMBER_DAYS_ROTATION' ) !== null )
François Jacquet's avatar
François Jacquet committed
52
				{
53
					$course_periods_RET = DBGet( "SELECT s.COURSE_PERIOD_ID,cpsp.PERIOD_ID,cp.HALF_DAY
54 55 56
					FROM SCHEDULE s,COURSE_PERIODS cp,ATTENDANCE_CALENDAR ac,SCHOOL_PERIODS sp,COURSE_PERIOD_SCHOOL_PERIODS cpsp
					WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID
					AND sp.PERIOD_ID=cpsp.PERIOD_ID
57
					AND ac.SCHOOL_DATE='" . $date . "'
58 59 60
					AND ac.CALENDAR_ID=cp.CALENDAR_ID
					AND (ac.BLOCK=sp.BLOCK OR sp.BLOCK IS NULL)
					AND s.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID
61
					AND s.STUDENT_ID='" . $student_id . "'
François Jacquet's avatar
François Jacquet committed
62
					AND cpsp.PERIOD_ID IN (" . $periods_list . ")
63 64
					AND position(',0,' IN cp.DOES_ATTENDANCE)>0
					AND (ac.SCHOOL_DATE BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND ac.SCHOOL_DATE>=s.START_DATE))
65
					AND position(substring('MTWHFSU' FROM cast(
66
						(SELECT CASE COUNT(school_date)% " . SchoolInfo( 'NUMBER_DAYS_ROTATION' ) . " WHEN 0 THEN " . SchoolInfo( 'NUMBER_DAYS_ROTATION' ) . " ELSE COUNT(school_date)% " . SchoolInfo( 'NUMBER_DAYS_ROTATION' ) . " END AS day_number
67 68 69
						FROM attendance_calendar
						WHERE school_date>=(SELECT start_date FROM school_marking_periods WHERE start_date<=ac.SCHOOL_DATE AND end_date>=ac.SCHOOL_DATE AND mp='QTR' AND SCHOOL_ID=ac.SCHOOL_ID)
						AND school_date<=ac.SCHOOL_DATE
70
						AND SCHOOL_ID=ac.SCHOOL_ID)
71
					AS INT) FOR 1) IN cpsp.DAYS)>0
72
					AND s.MARKING_PERIOD_ID IN (" . $all_mp . ")
73
					AND ac.SCHOOL_ID=s.SCHOOL_ID", array(), array( 'PERIOD_ID' ) );
74 75 76
				}
				else
				{
77
					$course_periods_RET = DBGet( "SELECT s.COURSE_PERIOD_ID,cpsp.PERIOD_ID,cp.HALF_DAY
78 79 80 81 82 83 84
						FROM SCHEDULE s,COURSE_PERIODS cp,ATTENDANCE_CALENDAR ac,SCHOOL_PERIODS sp,COURSE_PERIOD_SCHOOL_PERIODS cpsp
						WHERE sp.PERIOD_ID=cpsp.PERIOD_ID
						AND ac.SCHOOL_DATE='" . $date . "'
						AND ac.CALENDAR_ID=cp.CALENDAR_ID
						AND (ac.BLOCK=sp.BLOCK OR sp.BLOCK IS NULL)
						AND s.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID
						AND s.STUDENT_ID='" . $student_id . "'
François Jacquet's avatar
François Jacquet committed
85
						AND cpsp.PERIOD_ID IN (" . $periods_list . ")
86 87
						AND position(',0,' IN cp.DOES_ATTENDANCE)>0
						AND (ac.SCHOOL_DATE BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND ac.SCHOOL_DATE>=s.START_DATE))
88
						AND position(substring('UMTWHFS' FROM cast(extract(DOW FROM ac.SCHOOL_DATE) AS INT)+1 FOR 1) IN cpsp.DAYS)>0 AND s.MARKING_PERIOD_ID IN ($all_mp)", array(), array( 'PERIOD_ID' ) );
François Jacquet's avatar
François Jacquet committed
89
				}
90

François Jacquet's avatar
François Jacquet committed
91
				//echo '<pre>'; var_dump($course_periods_RET); echo '</pre>';
92

93
				foreach ( (array) $_REQUEST['period'] as $period_id => $yes )
François Jacquet's avatar
François Jacquet committed
94
				{
95 96 97 98 99
					if ( ! $yes )
					{
						continue;
					}

100 101 102
					$course_period_id = $course_periods_RET[$period_id][1]['COURSE_PERIOD_ID'];

					if ( $course_period_id && ! ( $course_periods_RET[$period_id][1]['COURSE_PERIOD_ID'] == 'Y' && $state_code == 'H' ) )
François Jacquet's avatar
François Jacquet committed
103
					{
104
						if ( empty( $current_RET[$student_id][$date][$period_id] ) )
François Jacquet's avatar
François Jacquet committed
105
						{
106 107 108 109 110 111
							$sql = "INSERT INTO ATTENDANCE_PERIOD
							(STUDENT_ID,SCHOOL_DATE,PERIOD_ID,MARKING_PERIOD_ID,COURSE_PERIOD_ID,ATTENDANCE_CODE,ATTENDANCE_REASON,ADMIN)
							VALUES('" . $student_id . "','" . $date . "','" . $period_id . "','" .
							$current_mp . "','" . $course_period_id . "','" . $_REQUEST['absence_code'] . "','" .
							$_REQUEST['absence_reason'] . "','Y')";

112
							DBQuery( $sql );
François Jacquet's avatar
François Jacquet committed
113 114 115
						}
						else
						{
116 117 118 119 120 121 122
							$sql = "UPDATE ATTENDANCE_PERIOD
							SET ATTENDANCE_CODE='" . $_REQUEST['absence_code'] . "',ATTENDANCE_REASON='" .
							$_REQUEST['absence_reason'] . "',ADMIN='Y',COURSE_PERIOD_ID='" . $course_period_id . "'
							WHERE STUDENT_ID='" . $student_id . "'
							AND SCHOOL_DATE='" . $date . "'
							AND PERIOD_ID='" . $period_id . "'";

123
							DBQuery( $sql );
François Jacquet's avatar
François Jacquet committed
124 125 126
						}
					}
				}
127 128 129 130 131 132

				UpdateAttendanceDaily(
					$student_id,
					$date,
					( $_REQUEST['absence_reason'] ? $_REQUEST['absence_reason'] : false )
				);
François Jacquet's avatar
François Jacquet committed
133 134
			}
		}
135 136

		$note[] = button( 'check' ) . '&nbsp;' . _( 'Absence records were added for the selected students.' );
François Jacquet's avatar
François Jacquet committed
137 138
	}
	else
139 140 141
	{
		$error[] = _( 'You must choose at least one period and one student.' );
	}
142

François Jacquet's avatar
François Jacquet committed
143 144
	// Unset modfunc & redirect URL.
	RedirectURL( 'modfunc' );
François Jacquet's avatar
François Jacquet committed
145 146
}

147 148 149
echo ErrorMessage( $note, 'note' );

echo ErrorMessage( $error );
150

151
if ( ! $_REQUEST['modfunc'] )
François Jacquet's avatar
François Jacquet committed
152
{
153
	$extra['link'] = array( 'FULL_NAME' => false );
François Jacquet's avatar
François Jacquet committed
154 155
	$extra['SELECT'] = ",CAST (NULL AS CHAR(1)) AS CHECKBOX";

156
	if ( $_REQUEST['search_modfunc'] === 'list' )
François Jacquet's avatar
François Jacquet committed
157
	{
158
		echo '<form action="' . URLEscape( 'Modules.php?modname=' . $_REQUEST['modname'] . '&modfunc=save' ) . '" method="POST">';
159

160
		DrawHeader( '', SubmitButton( _( 'Add Absences to Selected Students' ) ) );
François Jacquet's avatar
François Jacquet committed
161

François Jacquet's avatar
François Jacquet committed
162
		echo '<br />';
François Jacquet's avatar
François Jacquet committed
163

164 165
		PopTable( 'header', _( 'Add Absences' ) );

François Jacquet's avatar
François Jacquet committed
166
		echo '<table class="cellpadding-5"><tr><td><table><tr>';
François Jacquet's avatar
François Jacquet committed
167

François Jacquet's avatar
François Jacquet committed
168
		//FJ multiple school periods for a course period
169
		//$periods_RET = DBGet( "SELECT SHORT_NAME,PERIOD_ID FROM SCHOOL_PERIODS WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."' AND EXISTS (SELECT '' FROM COURSE_PERIODS WHERE PERIOD_ID=SCHOOL_PERIODS.PERIOD_ID AND position(',0,' IN DOES_ATTENDANCE)>0) ORDER BY SORT_ORDER" );
170
		$periods_RET = DBGet( "SELECT SHORT_NAME,PERIOD_ID
171
		FROM SCHOOL_PERIODS
172 173
		WHERE SYEAR='" . UserSyear() . "'
		AND SCHOOL_ID='" . UserSchool() . "'
174
		AND EXISTS (SELECT '' FROM COURSE_PERIOD_SCHOOL_PERIODS cpsp, COURSE_PERIODS cp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND cpsp.PERIOD_ID=SCHOOL_PERIODS.PERIOD_ID AND position(',0,' IN cp.DOES_ATTENDANCE)>0)
175
		ORDER BY SORT_ORDER" );
176 177 178

		foreach ( (array) $periods_RET as $period )
		{
179
			echo '<td><label><input type="CHECKBOX" value="Y" name="period[' . $period['PERIOD_ID'] . ']"> ' . $period['SHORT_NAME'] . '</label></td>';
180 181
		}

François Jacquet's avatar
François Jacquet committed
182
		echo '</tr></table>' . FormatInputTitle( _( 'Add Absence to Periods' ), '', false, '' ) . '</td></tr>';
183

François Jacquet's avatar
François Jacquet committed
184
		echo '<tr><td><label><select name="absence_code">';
185

186
		$codes_RET = DBGet( "SELECT TITLE,ID
187 188 189
			FROM ATTENDANCE_CODES
			WHERE SYEAR='" . UserSyear() . "'
			AND SCHOOL_ID='" . UserSchool() . "'
190
			AND TABLE_NAME='0'" );
191 192 193 194 195 196

		foreach ( (array) $codes_RET as $code )
		{
			echo '<option value=' . $code['ID'] . '>' . $code['TITLE'] . '</option>';
		}

François Jacquet's avatar
François Jacquet committed
197
		echo '</select>' . FormatInputTitle( _( 'Absence Code' ) ) . '</label></td></tr>';
198

François Jacquet's avatar
François Jacquet committed
199
		echo '<tr><td>';
200 201 202 203

		echo TextInput(
			'',
			'absence_reason',
François Jacquet's avatar
François Jacquet committed
204
			_( 'Absence Reason' ),
205 206
			'size=20 maxlength=100',
			false
François Jacquet's avatar
François Jacquet committed
207
		) . '</td></tr>';
208

François Jacquet's avatar
François Jacquet committed
209
		echo '<tr><td><div class="center">';
François Jacquet's avatar
François Jacquet committed
210

211 212 213 214 215
		$time = mktime( 0, 0, 0, $_REQUEST['month'] * 1, 1, mb_substr( $_REQUEST['year'], 2 ) );

		echo PrepareDate( mb_strtoupper( date( "d-M-y", $time ) ), '', false, array( 'M' => 1, 'Y' => 1, 'submit' => true ) );

		$skip = date( "w", $time );
François Jacquet's avatar
François Jacquet committed
216
		$last = 31;
217 218 219

		while ( ! checkdate( $_REQUEST['month'] * 1, $last, mb_substr( $_REQUEST['year'], 2 ) ) )
		{
François Jacquet's avatar
François Jacquet committed
220
			$last--;
221
		}
François Jacquet's avatar
François Jacquet committed
222

François Jacquet's avatar
François Jacquet committed
223
		echo '</div><table class="width-100p"><thead><tr>';
224 225 226 227 228 229 230 231 232
		//echo '<th>S</th><th>M</th><th>T</th><th>W</th><th>Th</th><th>F</th><th>S</th></tr><tr>';

		echo '<th>' . mb_substr( _( 'Sunday' ), 0, 3 ) .
		'</th><th>' . mb_substr( _( 'Monday' ), 0, 3 ) .
		'</th><th>' . mb_substr( _( 'Tuesday' ), 0, 3 ) .
		'</th><th>' . mb_substr( _( 'Wednesday' ), 0, 3 ) .
		'</th><th>' . mb_substr( _( 'Thursday' ), 0, 3 ) .
		'</th><th>' . mb_substr( _( 'Friday' ), 0, 3 ) .
		'</th><th>' . mb_substr( _( 'Saturday' ), 0, 3 ) .
François Jacquet's avatar
François Jacquet committed
233
		'</th></tr></thead><tbody><tr>';
234

235
		$calendar_RET = DBGet( "SELECT SCHOOL_DATE
236 237 238 239
			FROM ATTENDANCE_CALENDAR
			WHERE SYEAR='" . UserSyear() . "'
			AND SCHOOL_ID='" . UserSchool() . "'
			AND MINUTES!='0'
240
			AND EXTRACT(MONTH FROM SCHOOL_DATE)='" . ( $_REQUEST['month'] * 1 ) . "'", array(), array( 'SCHOOL_DATE' ) );
241 242 243

		for ( $i = 1; $i <= $skip; $i++ )
		{
François Jacquet's avatar
François Jacquet committed
244
			echo '<td></td>';
245
		}
François Jacquet's avatar
François Jacquet committed
246

247
		for ( $i = 1; $i <= $last; $i++ )
François Jacquet's avatar
François Jacquet committed
248
		{
249 250
			$this_date = $_REQUEST['year'] . '-' . $_REQUEST['month'] . '-' . ( $i < 10 ? '0' . $i : $i );

251
			if ( empty( $calendar_RET[$this_date] ) )
252
			{
François Jacquet's avatar
François Jacquet committed
253
				$disabled = ' DISABLED';
254 255 256
			}
			elseif ( date( 'Y-m-d' ) === $this_date )
			{
François Jacquet's avatar
François Jacquet committed
257
				$disabled = ' checked';
258
			}
François Jacquet's avatar
François Jacquet committed
259
			else
260
			{
François Jacquet's avatar
François Jacquet committed
261
				$disabled = '';
262
			}
François Jacquet's avatar
François Jacquet committed
263

François Jacquet's avatar
François Jacquet committed
264
			echo '<td><label><input type="checkbox" name="dates[' . $this_date . ']" value="Y"' . $disabled . '> ' . $i . '</label></td>';
265

François Jacquet's avatar
François Jacquet committed
266
			$skip++;
267 268 269

			if ( $skip % 7 == 0 && $i != $last )
			{
François Jacquet's avatar
François Jacquet committed
270
				echo '</tr><tr>';
271
			}
François Jacquet's avatar
François Jacquet committed
272
		}
273

François Jacquet's avatar
François Jacquet committed
274
		echo '</tr></tbody></table>';
275 276 277 278 279
		echo '</td></tr></table>';

		PopTable( 'footer' );

		echo '<br />';
François Jacquet's avatar
François Jacquet committed
280 281
	}

282 283 284
	Widgets( 'course' );
	Widgets( 'absences' );

285
	$extra['functions'] = array( 'CHECKBOX' => 'MakeChooseCheckbox' );
286

287 288 289
	$extra['columns_before'] = array(
		'CHECKBOX' => MakeChooseCheckbox( '', 'STUDENT_ID', 'student' ),
	);
François Jacquet's avatar
François Jacquet committed
290 291 292

	$extra['new'] = true;

293 294
	Search( 'student_id', $extra );

François Jacquet's avatar
François Jacquet committed
295
	if ( $_REQUEST['search_modfunc'] === 'list' )
296
	{
297
		echo '<br /><div class="center">' . SubmitButton( _( 'Add Absences to Selected Students' ) ) . '</div></form>';
298
	}
François Jacquet's avatar
François Jacquet committed
299
}