function VerifyBlockedSchedule($columns, $course_period_id, $sec, $edit = false)
{
if ($sec == 'cpv') {
$cp_det_RET = DBGet(DBQuery("SELECT * FROM course_periods WHERE course_period_id={$course_period_id}"));
$cp_det_RET = $cp_det_RET[1];
$teacher = $cp_det_RET['TEACHER_ID'];
$secteacher = $cp_det_RET['SECONDARY_TEACHER_ID'];
$all_teacher = $teacher . ($secteacher != '' ? ',' . $secteacher : '');
$mp_id = $cp_det_RET['MARKING_PERIOD_ID'];
$start_date = $cp_det_RET['BEGIN_DATE'];
$end_date = $cp_det_RET['END_DATE'];
if ($edit) {
$period = $columns['PERIOD_ID'];
$room = $columns['ROOM_ID'];
$cp_id = " AND cp.COURSE_PERIOD_ID!={$course_period_id}";
} else {
$cp_id = '';
$period = $_REQUEST['values']['PERIOD_ID'];
$room = $_REQUEST['values']['ROOM_ID'];
}
$period_time = DBGet(DBQuery("SELECT START_TIME, END_TIME FROM school_periods WHERE period_id={$period}"));
$period_time = $period_time[1];
$start_time = $period_time['START_TIME'];
$end_time = $period_time['END_TIME'];
if ($mp_id != '') {
$mp_date = DBGet(DBQuery("SELECT START_DATE,END_DATE FROM marking_periods where marking_period_id ={$mp_id}"));
$mp_date = $mp_date[1];
$end_date = $mp_date[END_DATE];
$start_date = $mp_date[START_DATE];
}
$mp_append_sql = " AND begin_date<='{$end_date}' AND '{$start_date}'<=end_date";
$days_append_sql .= " AND IF (schedule_type='BLOCKED', course_period_date ='" . $_REQUEST['meet_date'] . "' AND start_time<='{$end_time}' AND '{$start_time}'<=end_time,'{$_REQUEST['meet_date']}' BETWEEN begin_date AND end_date AND days like '%" . conv_day(date('D', strtotime($_REQUEST['meet_date'])), 'key') . "%' AND start_time<='{$end_time}' AND '{$start_time}'<=end_time)";
$days_room_append_sql .= " AND IF (schedule_type='BLOCKED', course_period_date ='" . $_REQUEST['meet_date'] . "' AND room_id={$room} AND start_time<='{$end_time}' AND '{$start_time}'<=end_time,'{$_REQUEST['meet_date']}' BETWEEN begin_date AND end_date AND days like '%" . conv_day(date('D', strtotime($_REQUEST['meet_date'])), 'key') . "%' AND room_id={$room} AND start_time<='{$end_time}' AND '{$start_time}'<=end_time)";
$cp_RET = DBGet(DBQuery("SELECT cp.COURSE_PERIOD_ID FROM course_periods cp LEFT JOIN course_period_var cpv ON (cp.course_period_id=cpv.course_period_id) WHERE (secondary_teacher_id IN ({$all_teacher}) OR teacher_id IN ({$all_teacher})){$mp_append_sql}{$days_append_sql}{$cp_id}"));
if ($cp_RET) {
return 'Teacher Not Available';
} else {
$room_RET = DBGet(DBQuery("SELECT cp.COURSE_PERIOD_ID FROM course_periods cp LEFT JOIN course_period_var cpv ON (cp.course_period_id=cpv.course_period_id) WHERE 1{$mp_append_sql}{$days_room_append_sql}{$cp_id}"));
if ($room_RET) {
return 'Room Not Available';
}
return true;
}
} else {
$cp_dates = DBGet(DBQuery("SELECT COURSE_PERIOD_DATE,START_TIME,END_TIME,ROOM_ID FROM course_period_var WHERE COURSE_PERIOD_ID='{$course_period_id}'"));
$teacher = $columns['TEACHER_ID'];
$secteacher = $columns['SECONDARY_TEACHER_ID'];
$all_teacher = $teacher . ($secteacher != '' ? ',' . $secteacher : '');
$start_date = $columns['BEGIN_DATE'];
if ($columns['MARKING_PERIOD_ID']) {
$mp_id = $columns['MARKING_PERIOD_ID'];
} else {
$mp_id = '';
}
$end_date = $columns['END_DATE'];
if ($mp_id != '') {
$mp_date = DBGet(DBQuery("SELECT START_DATE,END_DATE FROM marking_periods where marking_period_id ={$mp_id}"));
$mp_date = $mp_date[1];
$end_date = $mp_date[END_DATE];
$start_date = $mp_date[START_DATE];
}
foreach ($cp_dates as $key => $cp) {
$period = $cp['PERIOD_ID'];
$room = $cp['ROOM_ID'];
$meet_date = $cp['COURSE_PERIOD_DATE'];
$start_time = $cp['START_TIME'];
$end_time = $cp['END_TIME'];
$mp_append_sql = " AND begin_date<='{$end_date}' AND '{$start_date}'<=end_date";
$days_append_sql = " AND IF (schedule_type='BLOCKED', course_period_date ='{$meet_date}' AND start_time<='{$end_time}' AND '{$start_time}'<=end_time,'{$meet_date}' BETWEEN begin_date AND end_date AND days like '%" . conv_day(date('D', strtotime($meet_date)), 'key') . "%' AND start_time<='{$end_time}' AND '{$start_time}'<=end_time)";
$days_room_append_sql = " AND IF (schedule_type='BLOCKED', course_period_date ='{$meet_date}' AND room_id={$room} AND start_time<='{$end_time}' AND '{$start_time}'<=end_time,'{$meet_date}' BETWEEN begin_date AND end_date AND days like '%" . conv_day(date('D', strtotime($meet_date)), 'key') . "%' AND room_id={$room} AND start_time<='{$end_time}' AND '{$start_time}'<=end_time)";
$cp_id = " AND cp.COURSE_PERIOD_ID!={$course_period_id}";
$cp_RET = DBGet(DBQuery("SELECT cp.COURSE_PERIOD_ID FROM course_periods cp LEFT JOIN course_period_var cpv ON (cp.course_period_id=cpv.course_period_id) WHERE (secondary_teacher_id IN ({$all_teacher}) OR teacher_id IN ({$all_teacher})){$mp_append_sql}{$days_append_sql}{$cp_id}"));
if ($cp_RET) {
return 'Teacher Not Available';
} else {
$room_RET = DBGet(DBQuery("SELECT cp.COURSE_PERIOD_ID FROM course_periods cp LEFT JOIN course_period_var cpv ON (cp.course_period_id=cpv.course_period_id) WHERE 1{$mp_append_sql}{$days_room_append_sql}{$cp_id}"));
if ($room_RET) {
return 'Room Not Available';
}
}
}
$total_days = DBGet(DBQuery("SELECT COUNT(course_period_date) AS CP_DATES FROM course_period_var WHERE COURSE_PERIOD_ID={$course_period_id}"));
$mp_RET = DBGet(DBQuery("SELECT COUNT(course_period_date) AS CP_DATES FROM course_period_var WHERE COURSE_PERIOD_ID={$course_period_id} AND course_period_date BETWEEN '{$start_date}' AND '{$end_date}'"));
if ($total_days[1]['CP_DATES'] != $mp_RET[1]['CP_DATES']) {
if ($mp_id != '') {
return 'Marking Period Cannot be Changed.';
} else {
return 'Begin or End Date Cannot be Changed.';
}
}
return true;
}
}
if (count($room_RET)) {
foreach ($room_RET as $room) {
$rooms[$room['ROOM_ID']] = $room['TITLE'];
}
}
$periods_RET = DBGet(DBQuery("SELECT PERIOD_ID,TITLE FROM school_periods WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' ORDER BY SORT_ORDER"));
if (count($periods_RET)) {
foreach ($periods_RET as $period) {
$periods[$period['PERIOD_ID']] = $period['TITLE'];
}
}
if ($calendar_id != '') {
$days_RET = DBGet(DBQuery("SELECT DAYS FROM school_calendars WHERE calendar_id={$calendar_id}"));
$cal_days = str_split($days_RET[1]['DAYS']);
foreach ($cal_days as $day) {
$days[$day] = conv_day($day);
}
}
if ($_REQUEST['sch_type'] == 'variable') {
$header = '<input type=hidden name=tables[course_periods][' . $course_period_id . '][SCHEDULE_TYPE] value=VARIABLE id="variable"/>';
echo '<input type="hidden" name="get_status" id="get_status" value="" />';
echo '<input type="hidden" name="cp_id" id="' . $day . '_id" value="' . $course_period_id . '"/>';
$header .= '<TABLE width=100% class="grid"><TR><TD width="25px"></TD><TD width="100px" class="subtabs"><strong>Days</strong></TD><TD width="200px" class="subtabs"><strong>Period</strong></TD><TD class="subtabs"><strong>Time</strong></TD><TD width="150px" class="subtabs"><strong>Room</strong></TD><TD width="130px" align="center" class="subtabs"><strong>Takes attendance</strong></TD></TR>';
$rowcolor = 'even';
$header .= '<TR class="' . $rowcolor . '"><TD align="center">' . button('add') . '</TD>';
$header .= '<TD>' . SelectInput($RET['DAYS'], 'course_period_variable[' . $course_period_id . '][DAYS]', '', $days, 'N/A', 'id=days') . '</TD>';
$header .= '<TD>' . SelectInput($RET['PERIOD_ID'], 'course_period_variable[' . $course_period_id . '][PERIOD_ID]', '', $periods, 'N/A', 'id=' . $day . '_period class=cell_floating ' . $disable . ' onchange=show_period_time(this.value,"' . $day . '","' . $course_period_id . '","n");') . '</TD>';
$header .= '<TD><div id=' . $day . '_period_time></div></TD>';
$header .= '<TD>' . SelectInput($RET['ROOM'], 'course_period_variable[' . $course_period_id . '][ROOM_ID]', '', $rooms, 'N/A', 'id=' . $day . '_room ' . $disable) . '</TD>';
$header .= '<TD align="center">' . CheckboxInput($RET['DOES_ATTENDANCE'], 'course_period_variable[' . $course_period_id . '][DOES_ATTENDANCE]', '', '', false, 'Yes', 'No', false, ' id=' . $day . '_does_attendance onclick="formcheck_periods_attendance_F2(' . ($day != '' ? 2 : 1) . ',this);"' . $disable) . '<br><div id="ajax_output"></div></TD></TR>';
$header .= '</TR></TABLE>';
for ($i = $begin; $i <= $end; $i += 86400) {
if ($_REQUEST['weekdays'][$weekday] == 'Y') {
DBQuery('INSERT INTO attendance_calendar (SYEAR,SCHOOL_ID,SCHOOL_DATE,MINUTES,CALENDAR_ID) values(\'' . UserSyear() . '\',\'' . UserSchool() . '\',\'' . date('Y-m-d', $i) . '\',\'999\',\'' . $calendar_id . '\')');
}
$weekday++;
if ($weekday == 7) {
$weekday = 0;
}
}
$col = Calender_Title;
$cal_title = str_replace("'", "''", paramlib_validation($col, $_REQUEST['title']));
if ($_REQUEST['default']) {
DBQuery('Update school_calendars SET DEFAULT_CALENDAR=NULL WHERE SCHOOL_ID=\'' . UserSchool() . '\' AND SYEAR=\'' . UserSyear() . '\'');
}
// DBQuery('INSERT INTO attendance_calendars (SYEAR,SCHOOL_ID,TITLE,DEFAULT_CALENDAR) values(\''.UserSyear().'\',\''.UserSchool().'\',\''.str_replace("'","\'",$cal_title).'\',\''.$_REQUEST['default'].'\')');
DBQuery('INSERT INTO school_calendars (SYEAR,SCHOOL_ID,TITLE,DEFAULT_CALENDAR,DAYS) values(\'' . UserSyear() . '\',\'' . UserSchool() . '\',\'' . str_replace("'", "\\'", $cal_title) . '\',\'' . $_REQUEST['default'] . '\',\'' . conv_day($_REQUEST['weekdays']) . '\')');
// print_r($_REQUEST);
if (count($_REQUEST['profiles'])) {
$profile_sql = 'INSERT INTO calendar_events_visibility(calendar_id,profile_id,profile) VALUES';
foreach ($_REQUEST['profiles'] as $key => $profile) {
if (is_numeric($key)) {
$profile_sql .= '(\'' . $calendar_id . '\',\'' . $key . '\',NULL),';
} else {
$profile_sql .= '(\'' . $calendar_id . '\',NULL,\'' . $key . '\'),';
}
}
$profile_sql = substr($profile_sql, 0, -1);
DBQuery($profile_sql);
}
$_REQUEST['calendar_id'] = $calendar_id;
unset($_REQUEST['modfunc']);