# -------------------------- Length Update Start -------------------------- #
$sql_get_length = 'SELECT start_time, end_time from school_periods WHERE period_id=\'' . $id . '\'';
$res_get_length = mysql_query($sql_get_length);
$row_get_length = mysql_fetch_array($res_get_length);
$start_time = strtotime(date('m/d/Y') . ' ' . $row_get_length['start_time']);
$end_time = strtotime(date('m/d/Y') . ' ' . $row_get_length['end_time']);
if ($start_time > $end_time) {
$end_time = strtotime(date('m/d/Y') . ' ' . $row_get_length['end_time']) + 86400;
}
$length = ($end_time - $start_time) / 60;
$sql_length_update = 'UPDATE school_periods set length = ' . $length . ' where period_id=\'' . $id . '\'';
$res_length_update = mysql_query($sql_length_update);
# --------------------------- Length Update End --------------------------- #
} else {
$sql = 'SELECT TITLE,SHORT_NAME,SORT_ORDER,START_TIME,END_TIME FROM school_periods WHERE SYEAR= \'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'';
$periods = DBGET(DBQuery($sql));
for ($i = 1; $i <= count($periods); $i++) {
$shortname[$i] = $periods[$i]['SHORT_NAME'];
$p_title[$i] = $periods[$i]['TITLE'];
$sort_order[$i] = $periods[$i]['SORT_ORDER'];
$st_time[$i] = strtotime($periods[$i]['START_TIME']);
$end_time[$i] = strtotime($periods[$i]['END_TIME']);
}
if (in_array($columns['TITLE'], $p_title)) {
$err_msg = '' . _("Title already exists") . '';
break;
} else {
if (in_array($columns['SHORT_NAME'], $shortname)) {
$err_msg = '' . _("Short name already exists") . '';
break;
} else {
echo '<input type="hidden" name="cp_id" id="cp_id" value="' . $_REQUEST['course_period_id'] . '"/>';
DrawHeaderHome($title, $delete_button . SubmitButton('Save', '', 'id=save_cp class=btn_medium onclick="validate_course_period();"'));
$header .= '<TABLE cellpadding=4 width=100%>';
$header .= '<TR>';
$header .= '<TD>' . TextInput($RET['SHORT_NAME'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][SHORT_NAME]', 'Short Name', 'class=cell_floating', $div) . '</TD>';
echo '<input type="hidden" id="hidden_cp_id" value="' . $_REQUEST['course_period_id'] . '">';
$cal_RET = DBGet(DBQuery("SELECT TITLE,CALENDAR_ID FROM school_calendars WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "' ORDER BY DEFAULT_CALENDAR DESC"));
$options = array();
foreach ($cal_RET as $option) {
$options[$option['CALENDAR_ID']] = $option['TITLE'];
}
if ($_REQUEST[course_period_id] == 'new') {
$header .= '<TD>' . SelectInput($RET['CALENDAR_ID'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][CALENDAR_ID]', 'Calendar', $options, 'N/A', ' id=calendar_id onchange=reset_schedule();', $div) . '</TD>';
} else {
$cal_sql = "SELECT TITLE FROM school_calendars WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "' AND CALENDAR_ID='" . $RET['CALENDAR_ID'] . "'";
$cal_RET = DBGET(DBQuery($cal_sql));
$cal_RET = $cal_RET[1];
$header .= '<TD>' . NoInput($cal_RET['TITLE'], 'Calendar') . '</TD>';
}
$teachers_RET = DBGet(DBQuery("SELECT STAFF_ID,LAST_NAME,FIRST_NAME,MIDDLE_NAME FROM staff INNER JOIN staff_school_relationship USING (staff_id) WHERE school_id='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' AND PROFILE='teacher' AND ISNULL(IS_DISABLE) AND (END_DATE>=CURDATE() OR END_DATE IS NULL OR END_DATE='0000-00-00') AND START_DATE<=CURDATE() ORDER BY LAST_NAME,FIRST_NAME "));
if (count($teachers_RET)) {
foreach ($teachers_RET as $teacher) {
$teachers[$teacher['STAFF_ID']] = $teacher['LAST_NAME'] . ', ' . $teacher['FIRST_NAME'] . ' ' . $teacher['MIDDLE_NAME'];
}
}
$header .= '<TD>' . SelectInput($RET['TEACHER_ID'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][TEACHER_ID]', 'Primary Teacher', $teachers, 'N/A', '', $div) . '</TD>';
$header .= '<TD>' . SelectInput($RET['SECONDARY_TEACHER_ID'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][SECONDARY_TEACHER_ID]', 'Secondary Teacher', $teachers, 'N/A', '', $div) . '</TD>';
$header .= '<TD>' . TextInput($RET['TOTAL_SEATS'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][TOTAL_SEATS]', 'Seats', 'size=4 class=cell_floating', $div) . '</TD>';
if ($_REQUEST['course_period_id'] != 'new') {
$header .= '<TD><FONT color=green>' . $RET['AVAILABLE_SEATS'] . '</FONT><BR><FONT color=gray><SMALL>Available Seats</SMALL></FONT></TD>';
} else {
WHERE acc.SYEAR=\'' . UserSyear() . '\'
AND cp.SCHOOL_ID=acc.SCHOOL_ID
AND cp.SYEAR=acc.SYEAR
AND acc.SCHOOL_DATE=\'' . date('Y-m-d', strtotime($date)) . '\'
AND cp.CALENDAR_ID=acc.CALENDAR_ID
AND cp.COURSE_PERIOD_ID=\'' . UserCoursePeriod() . '\'
AND ( cp.MARKING_PERIOD_ID IN (' . $mps . ') OR (cp.BEGIN_DATE<=\'' . date('Y-m-d') . '\' AND cp.END_DATE>=\'' . date('Y-m-d') . '\' ))
AND sp.PERIOD_ID=cpv.PERIOD_ID
AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID
AND (sp.BLOCK IS NULL AND position(substring(\'UMTWHFS\' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cpv.DAYS)>0
OR sp.BLOCK IS NOT NULL
AND acc.BLOCK IS NOT NULL
AND sp.BLOCK=acc.BLOCK)
' . ($_REQUEST['table'] == '0' ? 'AND cpv.DOES_ATTENDANCE=\'Y\'' : '');
if ($mps != '') {
$course_RET = DBGET(DBQuery($sql));
}
$mp_id = GetCurrentMP('QTR', $date, false);
if (!$mp_id) {
$mp_id = GetCurrentMP('SEM', $date, false);
}
if (!$mp_id) {
$mp_id = GetCurrentMP('FY', $date, false);
}
// if running as a teacher program then openSIS[allow_edit] will already be set according to admin permissions
if (!isset($_openSIS['allow_edit'])) {
// allow teacher edit if selected date is in the current quarter or in the corresponding grade posting period
$current_qtr_id = $mp_id;
$time = strtotime(DBDate('postgres'));
if (($current_qtr_id || GetMP($mp_id, 'POST_START_DATE') && $time <= strtotime(GetMP($mp_id, 'POST_END_DATE'))) && ($edit_days_before == '' || strtotime($date) <= $time + $edit_days_before * 86400) && ($edit_days_after == '' || strtotime($date) >= $time - $edit_days_after * 86400)) {
$_openSIS['allow_edit'] = true;
ErrorMessage(array(_('You cannot take attendance for this course period.')), 'fatal');
}
if ($_REQUEST['table'] == '') {
$_REQUEST['table'] = $categories_RET[1]['ID'];
}
if ($_REQUEST['table'] == '0') {
$table = 'ATTENDANCE_PERIOD';
} else {
$table = 'LUNCH_PERIOD';
}
//modif Francois: days numbered
//modif Francois: multiple school periods for a course period
if (SchoolInfo('NUMBER_DAYS_ROTATION') !== null) {
$course_RET = DBGET(DBQuery("SELECT cp.HALF_DAY FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp, COURSE_PERIOD_SCHOOL_PERIODS cpsp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND acc.SYEAR='" . UserSyear() . "' AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND acc.SCHOOL_DATE='{$date}' AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cpsp.COURSE_PERIOD_SCHOOL_PERIODS_ID='" . UserCoursePeriodSchoolPeriod() . "'\n\tAND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE (MP='FY' OR MP='SEM' OR MP='QTR') AND SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)\n\tAND sp.PERIOD_ID=cpsp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('MTWHFSU' FROM cast((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 FROM attendance_calendar WHERE school_date>=(SELECT start_date FROM school_marking_periods WHERE start_date<=acc.SCHOOL_DATE AND end_date>=acc.SCHOOL_DATE AND mp='QTR') AND school_date<=acc.SCHOOL_DATE) AS INT) FOR 1) IN cpsp.DAYS)>0\n\t\tOR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)\n\tAND position(',{$_REQUEST['table']},' IN cp.DOES_ATTENDANCE)>0"));
} else {
$course_RET = DBGET(DBQuery("SELECT cp.HALF_DAY FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp, COURSE_PERIOD_SCHOOL_PERIODS cpsp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND acc.SYEAR='" . UserSyear() . "' AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND acc.SCHOOL_DATE='{$date}' AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cpsp.COURSE_PERIOD_SCHOOL_PERIODS_ID='" . UserCoursePeriodSchoolPeriod() . "'\n\tAND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE (MP='FY' OR MP='SEM' OR MP='QTR') AND SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)\n\tAND sp.PERIOD_ID=cpsp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM cast(extract(DOW FROM acc.SCHOOL_DATE) AS INT)+1 FOR 1) IN cpsp.DAYS)>0\n\t\tOR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)\n\tAND position(',{$_REQUEST['table']},' IN cp.DOES_ATTENDANCE)>0"));
}
if (count($course_RET) == 0) {
echo '<FORM action="Modules.php?modname=' . $_REQUEST['modname'] . '&table=' . $_REQUEST['table'] . '" method="POST">';
DrawHeader(PrepareDate($date, '_date', false, array('submit' => true)));
echo '</FORM>';
ErrorMessage(array(_('You cannot take attendance for this period on this day.')), 'fatal');
}
$qtr_id = GetCurrentMP('QTR', $date, false);
if (!$qtr_id) {
echo '<FORM action="Modules.php?modname=' . $_REQUEST['modname'] . '&table=' . $_REQUEST['table'] . '" method="POST">';
DrawHeader(PrepareDate($date, '_date', false, array('submit' => true)));
echo '</FORM>';
ErrorMessage(array(_('The selected date is not in a school quarter.')), 'fatal');
}
// if running as a teacher program then rosario[allow_edit] will already be set according to admin permissions
# echo $to;
# ------------------------ Old Query It's Also Working Start ---------------------------------- #
/*
$RET = DBGET(DBQuery("SELECT DISTINCT s.TITLE AS SCHOOL,acc.SCHOOL_DATE,cp.TITLE FROM attendance_calendar acc,course_periods cp,school_periods sp,schools s,staff st,schedule sch WHERE acc.SYEAR='".UserSyear()."' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND st.STAFF_ID='".User('STAFF_ID')."' AND (st.SCHOOLS IS NULL OR position(acc.SCHOOL_ID IN st.SCHOOLS)>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.FILLED_SEATS<>0 AND acc.SCHOOL_DATE>=sch.START_DATE AND acc.SCHOOL_DATE<'".DBDate()."'
AND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM school_years WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM school_semesters WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM school_quarters WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)
AND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0
OR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)
AND NOT EXISTS(SELECT '' FROM attendance_completed ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' AND s.ID=acc.SCHOOL_ID ORDER BY cp.TITLE,acc.SCHOOL_DATE"),array('SCHOOL_DATE'=>'ProperDate'));
*/
# ------------------------ Old Query It's Also Working End ---------------------------------- #
#if($_REQUEST['From'] && $_REQUEST['to'])
if ($From && $to) {
#$queryMP = UserMP();
#echo GetAllMP('',$queryMP);
// $RET = DBGET(DBQuery("SELECT DISTINCT s.TITLE AS SCHOOL,acc.SCHOOL_DATE,cp.TITLE,cp.COURSE_PERIOD_ID FROM attendance_calendar acc,course_periods cp,school_periods sp,schools s,staff st,schedule sch WHERE acc.SYEAR='".UserSyear()."' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND st.STAFF_ID='".User('STAFF_ID')."' AND (cp.TEACHER_ID='".User('STAFF_ID')."' OR cp.SECONDARY_TEACHER_ID='".User('STAFF_ID')."') AND (st.SCHOOLS IS NULL OR position(acc.SCHOOL_ID IN st.SCHOOLS)>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.FILLED_SEATS<>0 AND sch.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND acc.SCHOOL_DATE>='".$From."' AND acc.SCHOOL_DATE<='".$to."' AND acc.SCHOOL_DATE>=sch.START_DATE AND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM school_years WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM school_semesters WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM school_quarters WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE ) AND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0 OR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)AND NOT EXISTS(SELECT '' FROM attendance_completed ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND (ac.STAFF_ID=cp.TEACHER_ID OR ac.STAFF_ID=cp.SECONDARY_TEACHER_ID) AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' AND s.ID=acc.SCHOOL_ID AND cp.TITLE in(select cp.TITLE FROM schedule s,courses c,course_periods cp,school_periods sp WHERE s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND s.SCHOOL_ID = sp.SCHOOL_ID AND s.SCHOOL_ID=".UserSchool()." AND s.SYEAR = c.SYEAR AND sp.PERIOD_ID = cp.PERIOD_ID AND s.SYEAR='".UserSyear()."') ORDER BY cp.TITLE,acc.SCHOOL_DATE"),array('SCHOOL_DATE'=>'ProperDate'));
$RET = DBGET(DBQuery('SELECT DISTINCT s.TITLE AS SCHOOL,mi.SCHOOL_DATE,cp.TITLE, mi.COURSE_PERIOD_ID FROM missing_attendance mi,course_periods cp,schools s WHERE mi.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND s.ID=mi.SCHOOL_ID AND mi.SCHOOL_ID=\'' . UserSchool() . '\' AND (mi.TEACHER_ID=\'' . User('STAFF_ID') . '\' OR mi.SECONDARY_TEACHER_ID=\'' . User('STAFF_ID') . '\') AND mi.SCHOOL_DATE>=\'' . $From . '\' AND mi.SCHOOL_DATE<\'' . $to . '\' ORDER BY cp.TITLE,mi.SCHOOL_DATE'), array('SCHOOL_DATE' => 'ProperDate'));
}
if (!UserStudentID() || substr($_REQUEST['modname'], 0, 5) == 'Users') {
$RET_Users = DBGet(DBQuery('SELECT FIRST_NAME,LAST_NAME FROM staff WHERE STAFF_ID=\'' . UserStaffID() . '\''));
DrawHeaderHome('' . _('Selected User') . ': ' . $RET_Users[1]['FIRST_NAME'] . ' ' . $RET_Users[1]['LAST_NAME'] . ' ( <A HREF=Side.php?modname=' . $_REQUEST['modname'] . '&staff_id=new&From=' . $From . '&to=' . $to . ' >' . _('Back to User List') . ' )</A>');
}
#echo count($RET);
if (count($RET)) {
echo '<p><center><font color=#FF0000><b>' . _('Warning') . _('!!') . '</b></font> - ' . _('Teachers have missing attendance data') . ':</center>';
$modname = "Users/TeacherPrograms.php?include=Attendance/TakeAttendance.php&miss_attn=1&From={$From}&to={$to}";
$link['remove']['link'] = "Modules.php?modname={$modname}&modfunc=attn&username=admin";
$link['remove']['variables'] = array('date' => 'SCHOOL_DATE', 'cp_id' => 'COURSE_PERIOD_ID');
$_SESSION['miss_attn'] = 1;
ListOutput_missing_attn($RET, array('SCHOOL_DATE' => _('Date'), 'TITLE' => _('Period-Teacher'), 'SCHOOL' => _('School')), _('Period'), _('Periods'), $link, array(), array('save' => false, 'search' => false));
echo '</p>';
} else {
DrawBC($welcome . ' | Role: Teacher');
$notes_RET = DBGet(DBQuery("SELECT s.TITLE AS SCHOOL,pn.PUBLISHED_DATE,CONCAT('<B>',pn.TITLE,'</B>') AS TITLE,pn.CONTENT FROM PORTAL_NOTES pn,SCHOOLS s,STAFF st WHERE pn.SYEAR='" . UserSyear() . "' AND pn.START_DATE<=CURRENT_DATE AND (pn.END_DATE>=CURRENT_DATE OR pn.END_DATE IS NULL) AND st.STAFF_ID='" . User('STAFF_ID') . "' AND FIND_IN_SET(pn.SCHOOL_ID,st.SCHOOLS)>0 AND (st.SCHOOLS IS NULL OR FIND_IN_SET(pn.SCHOOL_ID,st.SCHOOLS)>0) AND (st.PROFILE_ID IS NULL AND FIND_IN_SET('teacher',pn.PUBLISHED_PROFILES)>0 OR st.PROFILE_ID IS NOT NULL AND FIND_IN_SET(st.PROFILE_ID,pn.PUBLISHED_PROFILES)>0) AND s.ID=pn.SCHOOL_ID ORDER BY pn.SORT_ORDER,pn.PUBLISHED_DATE DESC"), array('PUBLISHED_DATE' => 'ProperDate', 'CONTENT' => '_nl2br'));
if (count($notes_RET)) {
echo '<p>';
ListOutput($notes_RET, array('PUBLISHED_DATE' => 'Date Posted', 'TITLE' => 'Title', 'CONTENT' => 'Note', 'SCHOOL' => 'School'), 'Note', 'Notes', array(), array(), array('save' => false, 'search' => false));
echo '</p>';
}
$events_RET = DBGet(DBQuery("SELECT ce.TITLE,ce.DESCRIPTION,ce.SCHOOL_DATE,s.TITLE AS SCHOOL FROM CALENDAR_EVENTS ce,SCHOOLS s WHERE ce.SCHOOL_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE+30 AND ce.SYEAR='" . UserSyear() . "' AND FIND_IN_SET(ce.SCHOOL_ID,(SELECT SCHOOLS FROM STAFF WHERE STAFF_ID='" . User('STAFF_ID') . "'))>0 AND s.ID=ce.SCHOOL_ID ORDER BY ce.SCHOOL_DATE,s.TITLE"), array('SCHOOL_DATE' => 'ProperDate'));
if (count($events_RET)) {
echo '<p>';
ListOutput($events_RET, array('SCHOOL_DATE' => 'Date', 'TITLE' => 'Event', 'DESCRIPTION' => 'Description', 'SCHOOL' => 'School'), 'Upcoming Event', 'Upcoming Events', array(), array(), array('save' => false, 'search' => false));
echo '</p>';
}
if (Preferences('HIDE_ALERTS') != 'Y') {
// warn if missing attendance
$RET = DBGET(DBQuery("SELECT acc.SCHOOL_DATE,cp.TITLE FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp WHERE acc.SYEAR='" . UserSyear() . "' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND acc.SCHOOL_DATE<'" . DBDate() . "' AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.TEACHER_ID='" . User('STAFF_ID') . "'\n\t\tAND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)\n\t\tAND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND FIND_IN_SET(DAYOFWEEK(acc.SCHOOL_DATE) ,cp.DAYS)>0\n\t\t\tOR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)\n\t\tAND NOT EXISTS(SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' ORDER BY cp.TITLE,acc.SCHOOL_DATE"), array('SCHOOL_DATE' => 'ProperDate'));
if (count($RET)) {
echo '<p><font color=#FF0000><b>Warning!!</b></font> - You have missing attendance data:';
ListOutput($RET, array('SCHOOL_DATE' => 'Date', 'TITLE' => 'Period - Teacher'), 'Period', 'Periods', array(), array(), array('save' => false, 'search' => false));
echo '</p>';
}
}
if ($CentreModules['Food_Service'] && Preferences('HIDE_ALERTS') != 'Y') {
// warn if negative food service balance
$staff = DBGet(DBQuery('SELECT (SELECT STATUS FROM FOOD_SERVICE_STAFF_ACCOUNTS WHERE STAFF_ID=s.STAFF_ID) AS STATUS,(SELECT BALANCE FROM FOOD_SERVICE_STAFF_ACCOUNTS WHERE STAFF_ID=s.STAFF_ID) AS BALANCE FROM STAFF s WHERE s.STAFF_ID=' . User('STAFF_ID')));
$staff = $staff[1];
if ($staff['BALANCE'] && $staff['BALANCE'] < 0) {
echo '<p><font color=#FF0000><b>Warning!!</b></font> - You have a <b>negative</b> food service balance of <font color=#FF0000>' . $staff['BALANCE'] . '</font></p>';
}
}
#echo '<p> Happy teaching...</p>';
$sql2 = 'INSERT INTO FOOD_SERVICE_STAFF_TRANSACTIONS (' . $fields . ') values (' . $values . ')';
DBQuery('BEGIN; ' . $sql1 . '; ' . $sql2 . '; COMMIT');
unset($_SESSION['SALE']);
}
unset($_REQUEST['modfunc']);
} else {
unset($_REQUEST['modfunc']);
}
} else {
unset($_REQUEST['modfunc']);
}
}
if ($_REQUEST['modfunc'] == 'add') {
if (UserStaffID() && AllowEdit() && $_REQUEST['new_item'] && $_POST['new_item']) {
// get info for transcation item
$item = DBGET(DBQuery('SELECT * FROM FOOD_SERVICE_MENU_ITEMS WHERE SHORT_NAME=\'' . $_REQUEST['new_item'] . '\''));
$item = $item[1];
$price = $item['PRICE_STAFF'];
$_SESSION['SALE'][] = array('SHORT_NAME' => $item['SHORT_NAME'], 'DESCRIPTION' => $item['DESCRIPTION'], 'PRICE' => $price);
}
unset($_REQUEST['modfunc']);
}
if ($_REQUEST['modfunc'] == 'remove') {
if (AllowEdit()) {
unset($_SESSION['SALE'][$_REQUEST['id']]);
}
unset($_REQUEST['modfunc']);
}
if (UserStaffID() && !$_REQUEST['modfunc']) {
$staff = DBGet(DBQuery('SELECT STAFF_ID,FIRST_NAME,LAST_NAME,MIDDLE_NAME FROM STAFF WHERE STAFF_ID=' . UserStaffID()));
$staff = DBGet(DBQuery('SELECT s.STAFF_ID,s.FIRST_NAME,s.LAST_NAME,s.MIDDLE_NAME,(SELECT BALANCE FROM FOOD_SERVICE_STAFF_ACCOUNTS WHERE STAFF_ID=s.STAFF_ID) AS BALANCE FROM STAFF s WHERE s.STAFF_ID=' . UserStaffID()));
#
# This program is released under the terms of the GNU General Public License as
# published by the Free Software Foundation, version 2 of the License.
# See license.txt.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
#
#***************************************************************************************
include '../../RedirectModulesInc.php';
$schoolinfo = DBGET(DBQUERY('SELECT * FROM schools WHERE ID = ' . UserSchool()));
$schoolinfo = $schoolinfo[1];
$tsyear = UserSyear();
$tpicturepath = $openSISPath . $StudentPicturesPath;
$studataquery = 'select
s.first_name
, s.last_name
, s.middle_name
, s.gender as gender
, s.birthdate as birthdate
, s.phone as student_phone
, a.STREET_ADDRESS_1 as address
, a.city
, a.state
, a.zipcode
, sg.title as grade_title
} else {
$cal_sql = "SELECT TITLE FROM school_calendars WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "' AND CALENDAR_ID='" . $RET['CALENDAR_ID'] . "'";
$cal_RET = DBGET(DBQuery($cal_sql));
$cal_RET = $cal_RET[1];
$header .= '<TD>' . NoInput($cal_RET['TITLE'], 'Calendar') . '</TD>';
}
$teachers_RET = DBGet(DBQuery("SELECT STAFF_ID,LAST_NAME,FIRST_NAME,MIDDLE_NAME FROM staff INNER JOIN staff_school_relationship USING (staff_id) WHERE school_id='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' AND PROFILE='teacher' AND (ISNULL(IS_DISABLE) OR IS_DISABLE='N') AND (END_DATE>=CURDATE() OR END_DATE IS NULL OR END_DATE='0000-00-00') AND START_DATE<=CURDATE() ORDER BY LAST_NAME,FIRST_NAME "));
// ECHO "SELECT STAFF_ID,LAST_NAME,FIRST_NAME,MIDDLE_NAME FROM staff INNER JOIN staff_school_relationship USING (staff_id) WHERE school_id='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' AND PROFILE='teacher' AND ISNULL(IS_DISABLE) AND (END_DATE>=CURDATE() OR END_DATE IS NULL OR END_DATE='0000-00-00') AND START_DATE<=CURDATE() ORDER BY LAST_NAME,FIRST_NAME ";
if (count($teachers_RET)) {
foreach ($teachers_RET as $teacher) {
$teachers[$teacher['STAFF_ID']] = trim($teacher['LAST_NAME']) . ', ' . $teacher['FIRST_NAME'] . ' ' . $teacher['MIDDLE_NAME'];
}
}
// ECHO 'SELECT TEACHER FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID='.$_REQUEST['course_period_id'].'';
if ($_REQUEST['course_period_id'] != 'new') {
$qr = DBGET(DBQuery('SELECT TEACHER_ID FROM course_periods WHERE COURSE_PERIOD_ID=' . $_REQUEST['course_period_id'] . ''));
$teacher_id = $qr[1]['TEACHER_ID'];
$qr1 = DBGet(DBQuery('SELECT STAFF_ID,LAST_NAME,FIRST_NAME,MIDDLE_NAME FROM staff WHERE STAFF_ID=' . $qr[1]['TEACHER_ID'] . ''));
$k = $qr1[1]['STAFF_ID'];
$teachers[$k] = trim($qr1[1]['LAST_NAME']) . ', ' . $qr1[1]['FIRST_NAME'] . ' ' . $qr1[1]['MIDDLE_NAME'];
}
//
if ($RET['AVAILABLE_SEATS'] != $RET['TOTAL_SEATS']) {
$header .= '<TD>' . SelectInputDisabledMsg($RET['TEACHER_ID'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][TEACHER_ID]', 'Teacher / 任课教师', $teachers, 'N/A', '', $div, "Not allowed") . '</TD>';
} else {
$header .= '<TD>' . SelectInput($RET['TEACHER_ID'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][TEACHER_ID]', 'Teacher / 任课教师', $teachers, 'N/A', '', $div) . '</TD>';
}
//$header .= '<TD>' . SelectInput($RET['SECONDARY_TEACHER_ID'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][SECONDARY_TEACHER_ID]', 'Secondary Teacher', $teachers,'N/A','',$div) . '</TD>';
$header .= '<TD>' . TextInput($RET['TOTAL_SEATS'], 'tables[course_periods][' . $_REQUEST['course_period_id'] . '][TOTAL_SEATS]', 'Seats / 课程人数', 'size=4 class=cell_floating', $div) . '</TD>';
if ($_REQUEST['course_period_id'] != 'new') {
$header .= '<TD><FONT color=green>' . $RET['AVAILABLE_SEATS'] . '</FONT><BR><FONT color=gray><SMALL>Available Seats / 空位</SMALL></FONT></TD>';
function GetStaffList_Miss_Atn(&$extra)
{
global $profiles_RET;
$functions = array('PROFILE' => 'makeProfile');
switch (User('PROFILE')) {
case 'admin':
$profiles_RET = mysql_query("SELECT * FROM USER_PROFILES");
$sql = "SELECT CONCAT(s.LAST_NAME, ' ', s.FIRST_NAME) AS FULL_NAME,\r\n\t\t\t\t\t\ts.PROFILE,s.PROFILE_ID,s.STAFF_ID,s.SCHOOLS " . $extra['SELECT'] . "\r\n\t\t\t\t\tFROM\r\n\t\t\t\t\t\tSTAFF s " . $extra['FROM'] . "\r\n\t\t\t\t\tWHERE\r\n\t\t\t\t\t\ts.SYEAR='" . UserSyear() . "'";
if ($_REQUEST['_search_all_schools'] != 'Y') {
$sql .= " AND (s.SCHOOLS LIKE '%," . UserSchool() . ",%' OR s.SCHOOLS IS NULL OR s.SCHOOLS='') ";
}
if ($_REQUEST['username']) {
$sql .= "AND UPPER(s.USERNAME) LIKE '" . strtoupper($_REQUEST['username']) . "%' ";
}
if ($_REQUEST['last']) {
$sql .= "AND UPPER(s.LAST_NAME) LIKE '" . strtoupper($_REQUEST['last']) . "%' ";
}
if ($_REQUEST['first']) {
$sql .= "AND UPPER(s.FIRST_NAME) LIKE '" . strtoupper($_REQUEST['first']) . "%' ";
}
if ($_REQUEST['profile']) {
$sql .= "AND s.PROFILE='" . $_REQUEST['profile'] . "' ";
}
$sql .= "AND (";
$sql_st = "SELECT staff_id FROM STAFF where profile='teacher' ";
$res_st = mysql_query($sql_st);
$a = 0;
while ($row_st = mysql_fetch_array($res_st)) {
/* $RET = DBGET(DBQuery("SELECT DISTINCT s.TITLE AS SCHOOL,acc.SCHOOL_DATE,cp.TITLE FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp,SCHOOLS s,STAFF st,SCHEDULE sch WHERE acc.SYEAR='".UserSyear()."' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND st.STAFF_ID='".User('STAFF_ID')."' AND cp.TEACHER_ID='".$row_st['staff_id']."' AND (st.SCHOOLS IS NULL OR position(acc.SCHOOL_ID IN st.SCHOOLS)>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.FILLED_SEATS<>0 AND sch.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND acc.SCHOOL_DATE>=sch.START_DATE AND acc.SCHOOL_DATE<'".DBDate()."'
AND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)
AND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0
OR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)
AND NOT EXISTS(SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' AND s.ID=acc.SCHOOL_ID ORDER BY cp.TITLE,acc.SCHOOL_DATE"),array('SCHOOL_DATE'=>'ProperDate'));
*/
$RET = DBGET(DBQuery("SELECT DISTINCT s.TITLE AS SCHOOL,acc.SCHOOL_DATE,cp.TITLE FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp,SCHOOLS s,STAFF st,SCHEDULE sch WHERE acc.SYEAR='" . UserSyear() . "' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND st.STAFF_ID='" . User('STAFF_ID') . "' AND (cp.TEACHER_ID='" . $row_st['staff_id'] . "' OR cp.SECONDARY_TEACHER_ID='" . $row_st['staff_id'] . "') AND (st.SCHOOLS IS NULL OR position(acc.SCHOOL_ID IN st.SCHOOLS)>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.FILLED_SEATS<>0 AND sch.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID " . $extra['WHERE1'] . "\r\n\t\tAND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)\r\n\t\tAND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0\r\n\t\t\tOR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)\r\n\t\tAND NOT EXISTS(SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' AND s.ID=acc.SCHOOL_ID AND s.ID='" . UserSchool() . "' ORDER BY cp.TITLE,acc.SCHOOL_DATE"), array('SCHOOL_DATE' => 'ProperDate'));
if (count($RET)) {
$sql .= "s.STAFF_ID='" . $row_st['staff_id'] . "' OR ";
$a++;
}
}
if ($a != 0) {
$sql = substr($sql, 0, -4);
$sql .= ")";
} else {
$sql = substr($sql, 0, -5);
}
$sql .= $extra['WHERE'] . ' ';
$sql .= "ORDER BY FULL_NAME";
if ($extra['functions']) {
$functions += $extra['functions'];
}
if ($a != 0) {
return DBGet(DBQuery($sql), $functions);
}
break;
}
}
$month = 'AUG';
} elseif ($temp_month == '09') {
$month = 'SEP';
} elseif ($temp_month == '10') {
$month = 'OCT';
} elseif ($temp_month == '11') {
$month = 'NOV';
} elseif ($temp_month == '12') {
$month = 'DEC';
}
$day = substr($mother_date, 8, 2);
$select_date = $day . '-' . $month . '-' . $year;
return $select_date;
}
# ------------------------ Functions End ----------------------------- #
$full_period = DBGET(DBQuery("SELECT IGNORE_SCHEDULING,PERIOD_ID FROM SCHOOL_PERIODS WHERE SCHOOL_ID='" . UserSchool() . "' AND IGNORE_SCHEDULING='Y'"));
$FULL_PERIOD = $full_period[1]['IGNORE_SCHEDULING'];
$block_period = $full_period[1]['PERIOD_ID'];
$periods_list .= ",'" . $block_period . "'";
$periods_list = '(' . substr($periods_list, 1) . ')';
$course_per_id = clean_param($_REQUEST['course_period_id'], PARAM_INT);
$per_id = DBGet(DBQuery("SELECT PERIOD_ID, DAYS, MARKING_PERIOD_ID FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID = {$course_per_id}"));
$period_id = $per_id[1]['PERIOD_ID'];
$days = $per_id[1]['DAYS'];
$day_st_count = strlen($days);
$mp_id = $per_id[1]['MARKING_PERIOD_ID'];
#$st_time = DBGet(DBQuery("SELECT START_TIME, END_TIME FROM SCHOOL_PERIODS WHERE PERIOD_ID = $period_id"));
if ($FULL_PERIOD) {
$st_time = DBGet(DBQuery("SELECT START_TIME, END_TIME FROM SCHOOL_PERIODS WHERE PERIOD_ID = {$period_id} AND PERIOD_ID NOT IN {$periods_list}"));
} else {
$st_time = DBGet(DBQuery("SELECT START_TIME, END_TIME FROM SCHOOL_PERIODS WHERE PERIOD_ID = {$period_id} "));
#
#***************************************************************************************
include '../../RedirectModulesInc.php';
if ($_REQUEST['modfunc'] == 'attn') {
header("Location:Modules.php?modname=users/TeacherPrograms.php?include=attendance/TakeAttendance.php");
}
if ($_REQUEST['From'] && $_REQUEST['to']) {
$_SESSION['from_date'] = $_REQUEST['From'];
$_SESSION['to_date'] = $_REQUEST['to'];
}
$From = $_SESSION['from_date'];
$to = $_SESSION['to_date'];
# ------------------------ Old Query It's Also Working Start ---------------------------------- #
# ------------------------ Old Query It's Also Working End ---------------------------------- #
if ($From && $to) {
$RET = DBGET(DBQuery('SELECT DISTINCT s.TITLE AS SCHOOL,cpv.ID AS CPV_ID,mi.SCHOOL_DATE,cp.TITLE, mi.COURSE_PERIOD_ID FROM missing_attendance mi,course_periods cp,schools s,course_period_var cpv WHERE mi.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID AND cpv.PERIOD_ID=mi.PERIOD_ID AND s.ID=mi.SCHOOL_ID AND mi.SCHOOL_ID=\'' . UserSchool() . '\' AND (mi.TEACHER_ID=\'' . User('STAFF_ID') . '\' OR mi.SECONDARY_TEACHER_ID=\'' . User('STAFF_ID') . '\') AND mi.SCHOOL_DATE>=\'' . $From . '\' AND mi.SCHOOL_DATE<\'' . $to . '\' AND (mi.SCHOOL_DATE=cpv.COURSE_PERIOD_DATE OR POSITION(IF(DATE_FORMAT(mi.SCHOOL_DATE,\'%a\') LIKE \'Thu\',\'H\',(IF(DATE_FORMAT(mi.SCHOOL_DATE,\'%a\') LIKE \'Sun\',\'U\',SUBSTR(DATE_FORMAT(mi.SCHOOL_DATE,\'%a\'),1,1)))) IN cpv.DAYS)>0) ORDER BY cp.TITLE,mi.SCHOOL_DATE'), array('SCHOOL_DATE' => 'ProperDate'));
} else {
unset($RET);
}
if (!UserStudentID() || substr($_REQUEST['modname'], 0, 5) == 'users') {
$RET_Users = DBGet(DBQuery('SELECT FIRST_NAME,LAST_NAME FROM staff WHERE STAFF_ID=\'' . UserStaffID() . '\''));
DrawHeaderHome('Selected User: '******'FIRST_NAME'] . ' ' . $RET_Users[1]['LAST_NAME'] . ' ( <A HREF=Side.php?modname=' . $_REQUEST['modname'] . '&staff_id=new&From=' . $From . '&to=' . $to . ' >Back to User List )</A>');
}
if (count($RET)) {
echo '<p><center><font color=#FF0000><b>Warning!!</b></font> - Teachers have missing attendance data:</center>';
$modname = "users/TeacherPrograms.php?include=attendance/TakeAttendance.php&miss_attn=1&From={$From}&to={$to}";
$link['remove']['link'] = "Modules.php?modname={$modname}&modfunc=attn&username=admin";
$link['remove']['variables'] = array('date' => 'SCHOOL_DATE', 'cp_id' => 'COURSE_PERIOD_ID', 'cpv_id' => 'CPV_ID');
$_SESSION['miss_attn'] = 1;
ListOutput_missing_attn($RET, array('SCHOOL_DATE' => 'Date', 'TITLE' => 'Period -Teacher', 'SCHOOL' => 'School'), 'Period', 'Periods', $link, array(), array('save' => false, 'search' => false));
echo '</p>';
$to = $_SESSION['to_date'];
# echo $to;
# ------------------------ Old Query It's Also Working Start ---------------------------------- #
/*
$RET = DBGET(DBQuery("SELECT DISTINCT s.TITLE AS SCHOOL,acc.SCHOOL_DATE,cp.TITLE FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp,SCHOOLS s,STAFF st,SCHEDULE sch WHERE acc.SYEAR='".UserSyear()."' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND st.STAFF_ID='".User('STAFF_ID')."' AND (st.SCHOOLS IS NULL OR position(acc.SCHOOL_ID IN st.SCHOOLS)>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.FILLED_SEATS<>0 AND acc.SCHOOL_DATE>=sch.START_DATE AND acc.SCHOOL_DATE<'".DBDate()."'
AND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)
AND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0
OR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)
AND NOT EXISTS(SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' AND s.ID=acc.SCHOOL_ID ORDER BY cp.TITLE,acc.SCHOOL_DATE"),array('SCHOOL_DATE'=>'ProperDate'));
*/
# ------------------------ Old Query It's Also Working End ---------------------------------- #
#$RET = DBGET(DBQuery("SELECT DISTINCT s.TITLE AS SCHOOL,acc.SCHOOL_DATE,cp.TITLE,cp.COURSE_PERIOD_ID FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp,SCHOOLS s,STAFF st,SCHEDULE sch WHERE acc.SYEAR='".UserSyear()."' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND st.STAFF_ID='".User('STAFF_ID')."' AND cp.TEACHER_ID='".User('STAFF_ID')."' AND (st.SCHOOLS IS NULL OR position(acc.SCHOOL_ID IN st.SCHOOLS)>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.FILLED_SEATS<>0 AND sch.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND acc.SCHOOL_DATE>=sch.START_DATE AND acc.SCHOOL_DATE<='".DBDate()."' AND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE ) AND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0 OR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)AND NOT EXISTS(SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' AND s.ID=acc.SCHOOL_ID AND cp.TITLE in(select cp.TITLE FROM SCHEDULE s,COURSES c,COURSE_PERIODS cp,SCHOOL_PERIODS sp WHERE s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND s.SCHOOL_ID = sp.SCHOOL_ID AND s.SYEAR = c.SYEAR AND sp.PERIOD_ID = cp.PERIOD_ID AND s.SYEAR='".UserSyear()."') ORDER BY cp.TITLE,acc.SCHOOL_DATE"),array('SCHOOL_DATE'=>'ProperDate'));
#if($_REQUEST['From'] && $_REQUEST['to'])
if ($From && $to) {
#$RET = DBGET(DBQuery("SELECT DISTINCT s.TITLE AS SCHOOL,acc.SCHOOL_DATE,cp.TITLE,cp.COURSE_PERIOD_ID FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp,SCHOOLS s,STAFF st,SCHEDULE sch WHERE acc.SYEAR='".UserSyear()."' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND st.STAFF_ID='".User('STAFF_ID')."' AND cp.TEACHER_ID='".User('STAFF_ID')."' AND (st.SCHOOLS IS NULL OR position(acc.SCHOOL_ID IN st.SCHOOLS)>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.FILLED_SEATS<>0 AND sch.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND acc.SCHOOL_DATE>='".$From."' AND acc.SCHOOL_DATE<='".$to."' AND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE ) AND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0 OR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)AND NOT EXISTS(SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' AND s.ID=acc.SCHOOL_ID AND cp.TITLE in(select cp.TITLE FROM SCHEDULE s,COURSES c,COURSE_PERIODS cp,SCHOOL_PERIODS sp WHERE s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND s.SCHOOL_ID = sp.SCHOOL_ID AND s.SYEAR = c.SYEAR AND sp.PERIOD_ID = cp.PERIOD_ID AND s.SYEAR='".UserSyear()."') ORDER BY cp.TITLE,acc.SCHOOL_DATE"),array('SCHOOL_DATE'=>'ProperDate'));
$RET = DBGET(DBQuery("SELECT DISTINCT s.TITLE AS SCHOOL,acc.SCHOOL_DATE,cp.TITLE,cp.COURSE_PERIOD_ID FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp,SCHOOLS s,STAFF st,SCHEDULE sch WHERE acc.SYEAR='" . UserSyear() . "' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND st.STAFF_ID='" . User('STAFF_ID') . "' AND (cp.TEACHER_ID='" . User('STAFF_ID') . "' OR cp.SECONDARY_TEACHER_ID='" . User('STAFF_ID') . "') AND (st.SCHOOLS IS NULL OR position(acc.SCHOOL_ID IN st.SCHOOLS)>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.FILLED_SEATS<>0 AND sch.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND acc.SCHOOL_DATE>='" . $From . "' AND acc.SCHOOL_DATE<='" . $to . "' AND acc.SCHOOL_DATE>=sch.START_DATE AND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE ) AND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0 OR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)AND NOT EXISTS(SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID) AND cp.DOES_ATTENDANCE='Y' AND s.ID=acc.SCHOOL_ID AND cp.TITLE in(select cp.TITLE FROM SCHEDULE s,COURSES c,COURSE_PERIODS cp,SCHOOL_PERIODS sp WHERE s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND s.SCHOOL_ID = sp.SCHOOL_ID AND s.SYEAR = c.SYEAR AND sp.PERIOD_ID = cp.PERIOD_ID AND s.SYEAR='" . UserSyear() . "') ORDER BY cp.TITLE,acc.SCHOOL_DATE"), array('SCHOOL_DATE' => 'ProperDate'));
}
if (!UserStudentID() || substr($_REQUEST['modname'], 0, 5) == 'Users') {
$RET_Users = DBGet(DBQuery("SELECT FIRST_NAME,LAST_NAME FROM STAFF WHERE STAFF_ID='" . UserStaffID() . "'"));
DrawHeaderHome('Selected User: '******'FIRST_NAME'] . ' ' . $RET_Users[1]['LAST_NAME'] . ' ( <A HREF=Side.php?modname=' . $_REQUEST['modname'] . '&staff_id=new&From=' . $From . '&to=' . $to . ' >Back to User List )</A>');
}
#echo count($RET);
if (count($RET)) {
echo '<p><center><font color=#FF0000><b>Warning!!</b></font> - Teachers have missing attendance data:</center>';
$modname = "Users/TeacherPrograms.php?include=Attendance/TakeAttendance.php&miss_attn=1&From={$From}&to={$to}";
$link['remove']['link'] = "Modules.php?modname={$modname}&modfunc=attn&username=admin";
$link['remove']['variables'] = array('date' => 'SCHOOL_DATE', 'cp_id' => 'COURSE_PERIOD_ID');
$_SESSION['miss_attn'] = 1;
ListOutput_missing_attn($RET, array('SCHOOL_DATE' => 'Date', 'TITLE' => 'Period -Teacher', 'SCHOOL' => 'School'), 'Period', 'Periods', $link, array(), array('save' => false, 'search' => false));
echo '</p>';
} else {
ListOutput($notes_RET, array('PUBLISHED_DATE' => _('Date Posted'), 'TITLE' => _('Title'), 'CONTENT' => _('Note'), 'SCHOOL' => _('School')), _('Note'), _('Notes'), array(), array(), array('save' => false, 'search' => false));
echo '</p>';
}
$events_RET = DBGet(DBQuery("SELECT ce.TITLE,ce.DESCRIPTION,ce.SCHOOL_DATE,to_char(ce.SCHOOL_DATE,'Dy') AS DAY,s.TITLE AS SCHOOL FROM CALENDAR_EVENTS ce,SCHOOLS s WHERE ce.SCHOOL_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE+6 AND ce.SYEAR='" . UserSyear() . "' AND position(','||ce.SCHOOL_ID||',' IN (SELECT SCHOOLS FROM STAFF WHERE STAFF_ID='" . User('STAFF_ID') . "'))>0 AND s.ID=ce.SCHOOL_ID AND s.SYEAR=ce.SYEAR ORDER BY ce.SCHOOL_DATE,s.TITLE"), array('SCHOOL_DATE' => 'ProperDate'), array('SCHOOL_DATE'));
if (count($events_RET)) {
echo '<p>';
ListOutput($events_RET, array('DAY' => _('Day'), 'SCHOOL_DATE' => _('Date'), 'TITLE' => _('Event'), 'DESCRIPTION' => _('Description'), 'SCHOOL' => _('School')), _('Day With Upcoming Events'), _('Days With Upcoming Events'), array(), array('SCHOOL_DATE'), array('save' => false, 'search' => false));
echo '</p>';
}
if (Preferences('HIDE_ALERTS') != 'Y') {
// warn if missing attendances
$categories_RET = DBGet(DBQuery("SELECT '0' AS ID,'Attendance' AS TITLE,0,NULL AS SORT_ORDER UNION SELECT ID,TITLE,1,SORT_ORDER FROM ATTENDANCE_CODE_CATEGORIES WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "' ORDER BY 3,SORT_ORDER"));
foreach ($categories_RET as $category) {
$daysWeek = _('SuMoTuWeThFrSa');
/// Days of the week
$RET = DBGET(DBQuery("SELECT cp.COURSE_PERIOD_ID,acc.SCHOOL_DATE,cp.TITLE FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp WHERE acc.SYEAR='" . UserSyear() . "' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0) AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND acc.SCHOOL_DATE<'" . DBDate() . "' AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.TEACHER_ID='" . User('STAFF_ID') . "'\n AND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE MP='FY' AND SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE MP='SEM' AND SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE MP='QTR' AND SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)\n\t\t\tAND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('{$daysWeek}' FROM cast(extract(DOW FROM acc.SCHOOL_DATE) AS INT)*2+1 FOR 2) IN cp.DAYS)>0\n\t\t\t\tOR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)\n\t\t\tAND NOT exists(SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID=cp.PERIOD_ID AND TABLE_NAME='{$category['ID']}') AND position(',{$category['ID']},' IN cp.DOES_ATTENDANCE)>0 ORDER BY cp.TITLE,acc.SCHOOL_DATE"), array('SCHOOL_DATE' => 'ProperDate'), array('COURSE_PERIOD_ID'));
if (count($RET)) {
echo '<p><font color=red><b>' . _('Warning!') . '</b></font> - ' . sprintf(Localize('colon', _('You have missing <b>%s</b> attendance data')), $category['TITLE']);
ListOutput($RET, array('SCHOOL_DATE' => _('Date'), 'TITLE' => _('Period - Teacher')), sprintf(_('Period with missing %s attendance data'), $category['TITLE']), sprintf(_('Periods with missing %s attendance data'), $category['TITLE']), array(), array('COURSE_PERIOD_ID'), array('save' => false, 'search' => false));
echo '</p>';
}
}
}
if ($CentreModules['Food_Service'] && Preferences('HIDE_ALERTS') != 'Y') {
// warn if negative food service balance
$staff = DBGet(DBQuery('SELECT (SELECT STATUS FROM FOOD_SERVICE_STAFF_ACCOUNTS WHERE STAFF_ID=s.STAFF_ID) AS STATUS,(SELECT BALANCE FROM FOOD_SERVICE_STAFF_ACCOUNTS WHERE STAFF_ID=s.STAFF_ID) AS BALANCE FROM STAFF s WHERE s.STAFF_ID=' . User('STAFF_ID')));
$staff = $staff[1];
if ($staff['BALANCE'] && $staff['BALANCE'] < 0) {
echo '<p><font color=red><b>' . _('Warning!') . '</b></font> - ' . sprintf(_('You have a <b>negative</b> food service balance of <font color=red>%s</font>'), $staff['BALANCE']) . '</p>';
}
}
} else {
$_REQUEST['day_date'] = date('d');
$_REQUEST['month_date'] = strtoupper(date('M'));
$_REQUEST['year_date'] = date('y');
$date = $_REQUEST['day_date'] . '-' . $_REQUEST['month_date'] . '-' . $_REQUEST['year_date'];
}
DrawBC("Attendance > " . ProgramTitle());
if (!isset($_REQUEST['table'])) {
$_REQUEST['table'] = '0';
}
if ($_REQUEST['table'] == '0') {
$table = 'ATTENDANCE_PERIOD';
} else {
$table = 'LUNCH_PERIOD';
}
$course_RET = DBGET(DBQuery("SELECT cp.HALF_DAY FROM ATTENDANCE_CALENDAR acc,COURSE_PERIODS cp,SCHOOL_PERIODS sp WHERE acc.SYEAR='" . UserSyear() . "' AND cp.SCHOOL_ID=acc.SCHOOL_ID AND cp.SYEAR=acc.SYEAR AND acc.SCHOOL_DATE='{$date}' AND cp.CALENDAR_ID=acc.CALENDAR_ID AND cp.COURSE_PERIOD_ID='" . UserCoursePeriod() . "'\nAND cp.MARKING_PERIOD_ID IN (SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE UNION SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS WHERE SCHOOL_ID=acc.SCHOOL_ID AND acc.SCHOOL_DATE BETWEEN START_DATE AND END_DATE)\nAND sp.PERIOD_ID=cp.PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring('UMTWHFS' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0\n\tOR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)\n" . ($_REQUEST['table'] == '0' ? "AND cp.DOES_ATTENDANCE='Y'" : '')));
$qtr_id = GetCurrentMP('QTR', $date, false);
// if running as a teacher program then centre[allow_edit] will already be set according to admin permissions
if (!isset($_CENTRE['allow_edit'])) {
// allow teacher edit if selected date is in the current quarter or in the corresponding grade posting period
$current_qtr_id = GetCurrentMP('QTR', DBDate(), false);
$time = strtotime(DBDate('postgres'));
if (($current_qtr_id && $qtr_id == $current_qtr_id || GetMP($qtr_id, 'POST_START_DATE') && $time <= strtotime(GetMP($qtr_id, 'POST_END_DATE'))) && ($edit_days_before == '' || strtotime($date) <= $time + $edit_days_before * 86400) && ($edit_days_after == '' || strtotime($date) >= $time - $edit_days_after * 86400)) {
$_CENTRE['allow_edit'] = true;
}
}
$current_Q = "SELECT ATTENDANCE_TEACHER_CODE,STUDENT_ID,ADMIN,COMMENT FROM {$table} WHERE SCHOOL_DATE='{$date}' AND COURSE_PERIOD_ID='" . UserCoursePeriod() . "'" . ($table == 'LUNCH_PERIOD' ? " AND TABLE_NAME='{$_REQUEST['table']}'" : '');
$current_RET = DBGet(DBQuery($current_Q), array(), array('STUDENT_ID'));
if ($_REQUEST['attendance'] && ($_POST['attendance'] || $_REQUEST['ajax'])) {
foreach ($_REQUEST['attendance'] as $student_id => $value) {
if ($current_RET[$student_id]) {
