function calcSeats()
{
global $THIS_RET;
$periods_RET = DBGet(DBQuery("SELECT COURSE_PERIOD_ID,MARKING_PERIOD_ID,CALENDAR_ID,TOTAL_SEATS FROM COURSE_PERIODS WHERE COURSE_ID='{$THIS_RET['COURSE_ID']}' AND (GENDER_RESTRICTION='N' OR GENDER_RESTRICTION='" . mb_substr($THIS_RET['CUSTOM_200000000'], 0, 1) . "')" . ($THIS_RET['WITH_TEACHER_ID'] ? " AND TEACHER_ID='{$THIS_RET['WITH_TEACHER_ID']}'" : '') . ($THIS_RET['NOT_TEACHER_ID'] ? " AND TEACHER_ID!='{$THIS_RET['NOT_TEACHER_ID']}'" : '') . ($THIS_RET['WITH_PERIOD_ID'] ? " AND PERIOD_ID='{$THIS_RET['WITH_PERIOD_ID']}'" : '') . ($THIS_RET['NOT_PERIOD_ID'] ? " AND PERIOD_ID!='{$THIS_RET['NOT_PERIOD_ID']}'" : '')));
//echo '<pre>'; var_dump($periods_RET); echo '</pre>';
foreach ($periods_RET as $period) {
$seats = calcSeats0($period);
if ($total_seats !== false) {
if ($period['TOTAL_SEATS']) {
$total_seats += $period['TOTAL_SEATS'];
} else {
$total_seats = false;
}
}
if ($filled_seats !== false) {
if ($seats != '') {
$filled_seats += $seats;
} else {
$filled_seats = false;
}
}
}
return $total_seats !== false ? $filled_seats !== false ? $total_seats - $filled_seats : '' : 'n/a';
}
PopTable('footer');
ob_flush();
flush();
set_time_limit(0);
// get the fy marking period id, there should be exactly one fy marking period
$fy_id = DBGet(DBQuery("SELECT MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE MP='FY' AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"));
$fy_id = $fy_id[1]['MARKING_PERIOD_ID'];
$sql = "SELECT r.REQUEST_ID,r.STUDENT_ID,s.CUSTOM_200000000 as GENDER,r.SUBJECT_ID,r.COURSE_ID,MARKING_PERIOD_ID,WITH_TEACHER_ID,NOT_TEACHER_ID,WITH_PERIOD_ID,NOT_PERIOD_ID,(SELECT COUNT(*) FROM COURSE_PERIODS cp2 WHERE cp2.COURSE_ID=r.COURSE_ID) AS SECTIONS\n\tFROM SCHEDULE_REQUESTS r,STUDENTS s,STUDENT_ENROLLMENT ssm\n\tWHERE s.STUDENT_ID=ssm.STUDENT_ID AND ssm.SYEAR=r.SYEAR\n\tAND ('" . DBDate() . "' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)\n\tAND s.STUDENT_ID=r.STUDENT_ID AND r.SYEAR='" . UserSyear() . "' AND r.SCHOOL_ID='" . UserSchool() . "' ORDER BY REQUEST_ID";
//ORDER BY SECTIONS
$requests_RET = DBGet(DBQuery($sql), array(), array('REQUEST_ID'));
if ($_REQUEST['delete'] == 'Y' && count($requests_RET) > 0) {
DBQuery("DELETE FROM SCHEDULE WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' AND (SCHEDULER_LOCK!='Y' OR SCHEDULER_LOCK IS NULL)");
}
$periods_RET = DBGet(DBQuery("SELECT COURSE_PERIOD_ID,MARKING_PERIOD_ID,MP,TOTAL_SEATS,CALENDAR_ID FROM COURSE_PERIODS WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "'"));
foreach ($periods_RET as $period) {
$seats = calcSeats0($period);
DBQuery("UPDATE COURSE_PERIODS SET FILLED_SEATS='{$seats}' WHERE COURSE_PERIOD_ID='{$period['COURSE_PERIOD_ID']}'");
}
$count = DBGet(DBQuery("SELECT count(*) as count from schedule WHERE SCHOOL_ID='" . UserSchool() . "'"));
//modif Francois: multiple school periods for a course period
//$sql = "SELECT PARENT_ID,COURSE_PERIOD_ID,COURSE_ID,COURSE_ID AS COURSE,GENDER_RESTRICTION,PERIOD_ID,DAYS,TEACHER_ID,MARKING_PERIOD_ID,MP,COALESCE(TOTAL_SEATS,0)-COALESCE(FILLED_SEATS,0) AS AVAILABLE_SEATS,(SELECT COUNT(*) FROM COURSE_PERIODS cp2 WHERE cp2.COURSE_ID=cp.COURSE_ID) AS SECTIONS FROM COURSE_PERIODS cp ORDER BY SECTIONS,AVAILABLE_SEATS";
$sql = "SELECT PARENT_ID,cp.COURSE_PERIOD_ID,COURSE_ID,COURSE_ID AS COURSE,GENDER_RESTRICTION,cpsp.PERIOD_ID,cpsp.DAYS,TEACHER_ID,MARKING_PERIOD_ID,MP,COALESCE(TOTAL_SEATS,0)-COALESCE(FILLED_SEATS,0) AS AVAILABLE_SEATS,(SELECT COUNT(*) FROM COURSE_PERIODS cp2 WHERE cp2.COURSE_ID=cp.COURSE_ID) AS SECTIONS FROM COURSE_PERIODS cp,COURSE_PERIOD_SCHOOL_PERIODS cpsp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' ORDER BY COURSE_ID,SHORT_NAME";
//ORDER BY SECTIONS,AVAILABLE_SEATS
$cp_parent_RET = DBGet(DBQuery($sql), array(), array('PARENT_ID'));
//$sql = "SELECT PARENT_ID,COURSE_PERIOD_ID,COURSE_ID,COURSE_ID AS COURSE,GENDER_RESTRICTION,PERIOD_ID,DAYS,TEACHER_ID,MARKING_PERIOD_ID,MP,COALESCE(TOTAL_SEATS,0)-COALESCE(FILLED_SEATS,0) AS AVAILABLE_SEATS,(SELECT COUNT(*) FROM COURSE_PERIODS cp2 WHERE cp2.COURSE_ID=cp.COURSE_ID) AS SECTIONS FROM COURSE_PERIODS cp WHERE PARENT_ID=COURSE_PERIOD_ID ORDER BY SECTIONS,AVAILABLE_SEATS";
$sql = "SELECT PARENT_ID,cp.COURSE_PERIOD_ID,COURSE_ID,SHORT_NAME,COURSE_ID AS COURSE,GENDER_RESTRICTION,cpsp.PERIOD_ID,cpsp.DAYS,TEACHER_ID,MARKING_PERIOD_ID,MP,COALESCE(TOTAL_SEATS,0)-COALESCE(FILLED_SEATS,0) AS AVAILABLE_SEATS,(SELECT COUNT(*) FROM COURSE_PERIODS cp2 WHERE cp2.COURSE_ID=cp.COURSE_ID) AS SECTIONS FROM COURSE_PERIODS cp,COURSE_PERIOD_SCHOOL_PERIODS cpsp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND PARENT_ID=cp.COURSE_PERIOD_ID AND SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' ORDER BY COURSE_ID,SHORT_NAME";
//ORDER BY SECTIONS,AVAILABLE_SEATS
$cp_course_RET = DBGet(DBQuery($sql), array(), array('COURSE'));
$mps_RET = DBGet(DBQuery("SELECT PARENT_ID,MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE MP='QTR' AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"), array(), array('PARENT_ID', 'MARKING_PERIOD_ID'));
// GET FILLED/LOCKED REQUESTS
//modif Francois: multiple school periods for a course period
function _makeMPSelect($mp_id, $name)
{
global $_ROSARIO, $THIS_RET, $fy_id;
if (!$_ROSARIO['_makeMPSelect']) {
$semesters_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,TITLE,NULL AS PARENT_ID FROM SCHOOL_MARKING_PERIODS WHERE MP='SEM' AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "' ORDER BY SORT_ORDER"));
$quarters_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,TITLE,PARENT_ID FROM SCHOOL_MARKING_PERIODS WHERE MP='QTR' AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "' ORDER BY SORT_ORDER"));
$_ROSARIO['_makeMPSelect'][$fy_id][1] = array('MARKING_PERIOD_ID' => $fy_id, 'TITLE' => _('Full Year'), 'PARENT_ID' => '');
foreach ($semesters_RET as $sem) {
$_ROSARIO['_makeMPSelect'][$fy_id][] = $sem;
}
foreach ($quarters_RET as $qtr) {
$_ROSARIO['_makeMPSelect'][$fy_id][] = $qtr;
}
$quarters_QI = DBQuery("SELECT MARKING_PERIOD_ID,TITLE,PARENT_ID FROM SCHOOL_MARKING_PERIODS WHERE MP='QTR' AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "' ORDER BY SORT_ORDER");
$quarters_indexed_RET = DBGet($quarters_QI, array(), array('PARENT_ID'));
foreach ($semesters_RET as $sem) {
$_ROSARIO['_makeMPSelect'][$sem['MARKING_PERIOD_ID']][1] = $sem;
foreach ($quarters_indexed_RET[$sem['MARKING_PERIOD_ID']] as $qtr) {
$_ROSARIO['_makeMPSelect'][$sem['MARKING_PERIOD_ID']][] = $qtr;
}
}
foreach ($quarters_RET as $qtr) {
$_ROSARIO['_makeMPSelect'][$qtr['MARKING_PERIOD_ID']][] = $qtr;
}
}
foreach ($_ROSARIO['_makeMPSelect'][$mp_id] as $value) {
if ($value['MARKING_PERIOD_ID'] != $THIS_RET['MARKING_PERIOD_ID'] && $THIS_RET['TOTAL_SEATS'] && $_REQUEST['include_seats']) {
$seats = calcSeats0($THIS_RET);
}
$mps[$value['MARKING_PERIOD_ID']] = ($value['MARKING_PERIOD_ID'] == $THIS_RET['MARKING_PERIOD_ID'] && $value['MARKING_PERIOD_ID'] != $mp_id ? '* ' : '') . $value['TITLE'] . ($value['MARKING_PERIOD_ID'] != $THIS_RET['MARKING_PERIOD_ID'] && $THIS_RET['TOTAL_SEATS'] && $_REQUEST['include_seats'] && $seats != '' ? ' ' . sprintf(_('(%d seats)'), $THIS_RET['TOTAL_SEATS'] - $seats) : '');
}
return SelectInput($THIS_RET['MARKING_PERIOD_ID'], "schedule[{$THIS_RET['COURSE_PERIOD_ID']}][{$THIS_RET['START_DATE']}][MARKING_PERIOD_ID]", '', $mps, false);
}
