function UpdateAttendanceDaily($student_id, $date = '', $comment = false)
{
$daysWeek = _('SuMoTuWeThFrSa');
/// Days of the week
if (!$date) {
$date = DBDate();
}
$sql = "SELECT\n\t\t\t\tsum(sp.LENGTH) AS TOTAL\n\t\t\tFROM SCHEDULE s,COURSE_PERIODS cp,SCHOOL_PERIODS sp,ATTENDANCE_CALENDAR ac\n\t\t\tWHERE\n\t\t\t\ts.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND position(',0,' IN cp.DOES_ATTENDANCE)>0\n\t\t\t\tAND ac.SCHOOL_DATE='{$date}' AND (ac.BLOCK=sp.BLOCK OR sp.BLOCK IS NULL)\n\t\t\t\tAND ac.CALENDAR_ID=cp.CALENDAR_ID AND ac.SCHOOL_ID=s.SCHOOL_ID AND ac.SYEAR=s.SYEAR\n\t\t\t\tAND s.SYEAR = cp.SYEAR AND sp.PERIOD_ID = cp.PERIOD_ID\n\t\t\t\tAND position(substring('{$daysWeek}' FROM cast(extract(DOW FROM cast('{$date}' AS DATE)) AS INT)*2+1 FOR 2) IN cp.DAYS)>0\n\t\t\t\tAND s.STUDENT_ID='{$student_id}'\n\t\t\t\tAND s.SYEAR='" . UserSyear() . "'\n\t\t\t\tAND ('{$date}' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND '{$date}'>=s.START_DATE))\n\t\t\t\tAND s.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ")\n\t\t\t";
$RET = DBGet(DBQuery($sql));
$total = $RET[1]['TOTAL'];
if ($total == 0) {
return;
}
$sql = "SELECT sum(sp.LENGTH) AS TOTAL\n\t\t\tFROM ATTENDANCE_PERIOD ap,SCHOOL_PERIODS sp,ATTENDANCE_CODES ac\n\t\t\tWHERE ap.STUDENT_ID='{$student_id}' AND ap.SCHOOL_DATE='{$date}' AND ap.PERIOD_ID=sp.PERIOD_ID AND ac.ID = ap.ATTENDANCE_CODE AND ac.STATE_CODE='A'\n\t\t\tAND sp.SYEAR='" . UserSyear() . "'";
$RET = DBGet(DBQuery($sql));
$total -= $RET[1]['TOTAL'];
$sql = "SELECT sum(sp.LENGTH) AS TOTAL\n\t\t\tFROM ATTENDANCE_PERIOD ap,SCHOOL_PERIODS sp,ATTENDANCE_CODES ac\n\t\t\tWHERE ap.STUDENT_ID='{$student_id}' AND ap.SCHOOL_DATE='{$date}' AND ap.PERIOD_ID=sp.PERIOD_ID AND ac.ID = ap.ATTENDANCE_CODE AND ac.STATE_CODE='H'\n\t\t\tAND sp.SYEAR='" . UserSyear() . "'";
$RET = DBGet(DBQuery($sql));
$total -= $RET[1]['TOTAL'] * 0.5;
if ($total >= 300) {
$length = '1.0';
} elseif ($total >= 150) {
$length = '.5';
} else {
$length = '0.0';
}
$current_RET = DBGet(DBQuery("SELECT MINUTES_PRESENT,STATE_VALUE,COMMENT FROM ATTENDANCE_DAY WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'"));
if (count($current_RET) && $current_RET[1]['MINUTES_PRESENT'] != $total) {
DBQuery("UPDATE ATTENDANCE_DAY SET MINUTES_PRESENT='{$total}',STATE_VALUE='{$length}'" . ($comment !== false ? ",COMMENT='" . str_replace("\\'", "''", $comment) . "'" : '') . " WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'");
} elseif (count($current_RET) && $comment !== false && $current_RET[1]['COMMENT'] != $comment) {
DBQuery("UPDATE ATTENDANCE_DAY SET COMMENT='" . str_replace("\\'", "''", $comment) . "' WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'");
} elseif (count($current_RET) == 0) {
DBQuery("INSERT INTO ATTENDANCE_DAY (SYEAR,STUDENT_ID,SCHOOL_DATE,MINUTES_PRESENT,STATE_VALUE,MARKING_PERIOD_ID,COMMENT) values('" . UserSyear() . "','{$student_id}','{$date}','{$total}','{$length}','" . GetCurrentMP('QTR', $date) . "','" . str_replace("\\'", "''", $comment) . "')");
}
}
function UpdateAttendanceDaily($student_id, $date = '', $comment = false)
{
if (!$date) {
$date = DBDate();
}
$sql = "SELECT\r\n\t\t\t\tsum(sp.LENGTH) AS TOTAL\r\n\t\t\tFROM SCHEDULE s,COURSE_PERIODS cp,SCHOOL_PERIODS sp,ATTENDANCE_CALENDAR ac\r\n\t\t\tWHERE\r\n\t\t\t\ts.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND cp.DOES_ATTENDANCE='Y'\r\n\t\t\t\tAND ac.SCHOOL_DATE='{$date}' AND (ac.BLOCK=sp.BLOCK OR sp.BLOCK IS NULL)\r\n\t\t\t\tAND ac.CALENDAR_ID=cp.CALENDAR_ID AND ac.SCHOOL_ID=s.SCHOOL_ID AND ac.SYEAR=s.SYEAR\r\n\t\t\t\tAND s.SYEAR = cp.SYEAR AND sp.PERIOD_ID = cp.PERIOD_ID\r\n\t\t\t\tAND position(substring('UMTWHFS' FROM DAYOFWEEK('{$date}') FOR 1) IN cp.DAYS)>0\r\n\t\t\t\tAND s.STUDENT_ID='{$student_id}'\r\n\t\t\t\tAND s.SYEAR='" . UserSyear() . "'\r\n\t\t\t\tAND ('{$date}' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND '{$date}'>=s.START_DATE))\r\n\t\t\t\tAND s.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ")\r\n\t\t\t";
$RET = DBGet(DBQuery($sql));
$total = $RET[1]['TOTAL'];
if ($total == 0) {
return;
}
$sql = "SELECT sum(sp.LENGTH) AS TOTAL\r\n\t\t\tFROM ATTENDANCE_PERIOD ap,SCHOOL_PERIODS sp,ATTENDANCE_CODES ac\r\n\t\t\tWHERE ap.STUDENT_ID='{$student_id}' AND ap.SCHOOL_DATE='{$date}' AND ap.PERIOD_ID=sp.PERIOD_ID AND ac.ID = ap.ATTENDANCE_CODE AND ac.STATE_CODE='A'\r\n\t\t\tAND sp.SYEAR='" . UserSyear() . "'";
$RET = DBGet(DBQuery($sql));
$total -= $RET[1]['TOTAL'];
$sql = "SELECT sum(sp.LENGTH) AS TOTAL\r\n\t\t\tFROM ATTENDANCE_PERIOD ap,SCHOOL_PERIODS sp,ATTENDANCE_CODES ac\r\n\t\t\tWHERE ap.STUDENT_ID='{$student_id}' AND ap.SCHOOL_DATE='{$date}' AND ap.PERIOD_ID=sp.PERIOD_ID AND ac.ID = ap.ATTENDANCE_CODE AND ac.STATE_CODE='H'\r\n\t\t\tAND sp.SYEAR='" . UserSyear() . "'";
$RET = DBGet(DBQuery($sql));
$total -= $RET[1]['TOTAL'] * 0.5;
/*
if($total>=300)
$length = '1.0';
elseif($total>=150)
$length = '.5';
else
$length = '0.0';
*/
if (stripos($_SERVER['SERVER_SOFTWARE'], 'linux')) {
$comment = str_replace("'", "\\'", $comment);
}
$sys_pref = DBGet(DBQuery("SELECT * FROM SYSTEM_PREFERENCE WHERE SCHOOL_ID=" . UserSchool()));
$fdm = $sys_pref[1]['FULL_DAY_MINUTE'];
$hdm = $sys_pref[1]['HALF_DAY_MINUTE'];
if ($total >= $fdm) {
$length = '1.0';
} elseif ($total >= $hdm) {
$length = '.5';
} else {
$length = '0.0';
}
$current_RET = DBGet(DBQuery("SELECT MINUTES_PRESENT,STATE_VALUE,COMMENT FROM ATTENDANCE_DAY WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'"));
if (count($current_RET) && $current_RET[1]['MINUTES_PRESENT'] != $total) {
DBQuery("UPDATE ATTENDANCE_DAY SET MINUTES_PRESENT='{$total}',STATE_VALUE='{$length}'" . ($comment !== false ? ",COMMENT='" . str_replace("", "", $comment) . "'" : '') . " WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'");
} elseif (count($current_RET) && $comment !== false && $current_RET[1]['COMMENT'] != $comment) {
DBQuery("UPDATE ATTENDANCE_DAY SET COMMENT='" . str_replace("", "", $comment) . "' WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'");
} elseif (count($current_RET) == 0) {
DBQuery("INSERT INTO ATTENDANCE_DAY (SYEAR,STUDENT_ID,SCHOOL_DATE,MINUTES_PRESENT,STATE_VALUE,MARKING_PERIOD_ID,COMMENT) values('" . UserSyear() . "','{$student_id}','{$date}','{$total}','{$length}','" . GetCurrentMP('QTR', $date) . "','" . str_replace("", "", $comment) . "')");
}
}
function UpdateAttendanceDaily($student_id, $date = '', $comment = false)
{
if (!$date) {
$date = DBDate();
}
//modif Francois: days numbered
//modif Francois: multiple school periods for a course period
if (SchoolInfo('NUMBER_DAYS_ROTATION') !== null) {
$sql = "SELECT\n\t\t\t\t\tsum(sp.LENGTH) AS TOTAL\n\t\t\t\tFROM SCHEDULE s,COURSE_PERIODS cp,SCHOOL_PERIODS sp,ATTENDANCE_CALENDAR ac, COURSE_PERIOD_SCHOOL_PERIODS cpsp \n\t\t\t\tWHERE \n\t\t\t\t\tcp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND\n\t\t\t\t\ts.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND position(',0,' IN cp.DOES_ATTENDANCE)>0\n\t\t\t\t\tAND ac.SCHOOL_DATE='{$date}' AND (ac.BLOCK=sp.BLOCK OR sp.BLOCK IS NULL)\n\t\t\t\t\tAND ac.CALENDAR_ID=cp.CALENDAR_ID AND ac.SCHOOL_ID=s.SCHOOL_ID AND ac.SYEAR=s.SYEAR\n\t\t\t\t\tAND s.SYEAR = cp.SYEAR AND sp.PERIOD_ID = cpsp.PERIOD_ID\n\t\t\t\t\tAND 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<='{$date}' AND end_date>='{$date}' AND mp='QTR') AND school_date<='{$date}') AS INT) FOR 1) IN cpsp.DAYS)>0\n\t\t\t\t\tAND s.STUDENT_ID='{$student_id}'\n\t\t\t\t\tAND s.SYEAR='" . UserSyear() . "'\n\t\t\t\t\tAND ('{$date}' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND '{$date}'>=s.START_DATE))\n\t\t\t\t\tAND s.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ")\n\t\t\t\t";
} else {
$sql = "SELECT\n\t\t\t\t\tsum(sp.LENGTH) AS TOTAL\n\t\t\t\tFROM SCHEDULE s,COURSE_PERIODS cp,SCHOOL_PERIODS sp,ATTENDANCE_CALENDAR ac, COURSE_PERIOD_SCHOOL_PERIODS cpsp \n\t\t\t\tWHERE \n\t\t\t\t\tcp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND\n\t\t\t\t\ts.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND position(',0,' IN cp.DOES_ATTENDANCE)>0\n\t\t\t\t\tAND ac.SCHOOL_DATE='{$date}' AND (ac.BLOCK=sp.BLOCK OR sp.BLOCK IS NULL)\n\t\t\t\t\tAND ac.CALENDAR_ID=cp.CALENDAR_ID AND ac.SCHOOL_ID=s.SCHOOL_ID AND ac.SYEAR=s.SYEAR\n\t\t\t\t\tAND s.SYEAR = cp.SYEAR AND sp.PERIOD_ID = cpsp.PERIOD_ID\n\t\t\t\t\tAND position(substring('UMTWHFS' FROM cast(extract(DOW FROM cast('{$date}' AS DATE)) AS INT)+1 FOR 1) IN cpsp.DAYS)>0\n\t\t\t\t\tAND s.STUDENT_ID='{$student_id}'\n\t\t\t\t\tAND s.SYEAR='" . UserSyear() . "'\n\t\t\t\t\tAND ('{$date}' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND '{$date}'>=s.START_DATE))\n\t\t\t\t\tAND s.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ")\n\t\t\t\t";
}
$RET = DBGet(DBQuery($sql));
$total = $RET[1]['TOTAL'];
if ($total == 0) {
return;
}
$sql = "SELECT sum(sp.LENGTH) AS TOTAL\n\t\t\tFROM ATTENDANCE_PERIOD ap,SCHOOL_PERIODS sp,ATTENDANCE_CODES ac\n\t\t\tWHERE ap.STUDENT_ID='{$student_id}' AND ap.SCHOOL_DATE='{$date}' AND ap.PERIOD_ID=sp.PERIOD_ID AND ac.ID = ap.ATTENDANCE_CODE AND ac.STATE_CODE='A'\n\t\t\tAND sp.SYEAR='" . UserSyear() . "'";
$RET = DBGet(DBQuery($sql));
$total -= $RET[1]['TOTAL'];
$sql = "SELECT sum(sp.LENGTH) AS TOTAL\n\t\t\tFROM ATTENDANCE_PERIOD ap,SCHOOL_PERIODS sp,ATTENDANCE_CODES ac\n\t\t\tWHERE ap.STUDENT_ID='{$student_id}' AND ap.SCHOOL_DATE='{$date}' AND ap.PERIOD_ID=sp.PERIOD_ID AND ac.ID = ap.ATTENDANCE_CODE AND ac.STATE_CODE='H'\n\t\t\tAND sp.SYEAR='" . UserSyear() . "'";
$RET = DBGet(DBQuery($sql));
$total -= $RET[1]['TOTAL'] * 0.5;
if ($total >= Config('ATTENDANCE_FULL_DAY_MINUTES')) {
$length = '1.0';
} elseif ($total >= Config('ATTENDANCE_FULL_DAY_MINUTES') / 2) {
$length = '.5';
} else {
$length = '0.0';
}
$current_RET = DBGet(DBQuery("SELECT MINUTES_PRESENT,STATE_VALUE,COMMENT FROM ATTENDANCE_DAY WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'"));
if (count($current_RET) && $current_RET[1]['MINUTES_PRESENT'] != $total) {
DBQuery("UPDATE ATTENDANCE_DAY SET MINUTES_PRESENT='{$total}',STATE_VALUE='{$length}'" . ($comment !== false ? ",COMMENT='" . $comment . "'" : '') . " WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'");
} elseif (count($current_RET) && $comment !== false && $current_RET[1]['COMMENT'] != $comment) {
DBQuery("UPDATE ATTENDANCE_DAY SET COMMENT='" . $comment . "' WHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}'");
} elseif (count($current_RET) == 0) {
DBQuery("INSERT INTO ATTENDANCE_DAY (SYEAR,STUDENT_ID,SCHOOL_DATE,MINUTES_PRESENT,STATE_VALUE,MARKING_PERIOD_ID,COMMENT) values('" . UserSyear() . "','{$student_id}','{$date}','{$total}','{$length}','" . GetCurrentMP('QTR', $date) . "','" . $comment . "')");
}
}
} else {
$date = DBDate();
$date_extra = 'OR sr.END_DATE IS NULL';
}
$columns = array('PERIOD_TITLE' => '' . _('Period - Teacher') . '', 'MARKING_PERIOD_ID' => '' . _('Term') . '', 'DAYS' => '' . _('Days') . '', 'DURATION' => '' . _('Time') . '', 'ROOM' => '' . _('Room') . '', 'COURSE_TITLE' => '' . _('Course') . '');
$extra['SELECT'] .= ',c.TITLE AS COURSE_TITLE,p_cp.TITLE AS PERIOD_TITLE,sr.MARKING_PERIOD_ID,p_cp.DAYS, CONCAT(sp.START_TIME,\'' . ' to ' . '\', sp.END_TIME) AS DURATION,p_cp.ROOM';
$extra['FROM'] .= ' LEFT OUTER JOIN schedule sr ON (sr.STUDENT_ID=ssm.STUDENT_ID),courses c,course_periods p_cp,school_periods sp ';
// $extra['WHERE'] .= " AND p_cp.PERIOD_ID=sp.PERIOD_ID AND ssm.SYEAR=sr.SYEAR AND sr.COURSE_ID=c.COURSE_ID AND sr.COURSE_PERIOD_ID=p_cp.COURSE_PERIOD_ID AND p_cp.PERIOD_ID=sp.PERIOD_ID AND ('$date' BETWEEN sr.START_DATE AND sr.END_DATE $date_extra)";
$extra['WHERE'] .= ' AND p_cp.PERIOD_ID=sp.PERIOD_ID AND ssm.SYEAR=sr.SYEAR AND sr.COURSE_ID=c.COURSE_ID AND sr.COURSE_PERIOD_ID=p_cp.COURSE_PERIOD_ID AND p_cp.PERIOD_ID=sp.PERIOD_ID';
if ($_REQUEST['include_inactive'] != 'Y') {
$extra['WHERE'] .= ' AND (\'' . date('Y-m-d', strtotime($date)) . '\' BETWEEN sr.START_DATE AND sr.END_DATE OR (sr.END_DATE IS NULL AND sr.START_DATE<=\'' . date('Y-m-d', strtotime($date)) . '\')) ';
}
if ($_REQUEST['mp_id'] && (User('PROFILE_ID') != 0 && User('PROFILE_ID') != 3)) {
$extra['WHERE'] .= ' AND sr.MARKING_PERIOD_ID=' . $_REQUEST['mp_id'] . '';
} else {
$extra['WHERE'] .= ' AND sr.MARKING_PERIOD_ID IN (' . GetAllMP(GetMPTable(GetMP($_REQUEST['mp_id'], 'TABLE')), $_REQUEST['mp_id']) . ')';
}
$extra['functions'] = array('MARKING_PERIOD_ID' => 'GetMP', 'DAYS' => '_makeDays');
$extra['group'] = array('STUDENT_ID');
$extra['ORDER'] = ',sp.SORT_ORDER';
if ($_REQUEST['mailing_labels'] == 'Y') {
$extra['group'][] = 'ADDRESS_ID';
}
Widgets('mailing_labels');
$RET = GetStuList($extra);
if (count($RET)) {
$handle = PDFStart();
foreach ($RET as $student_id => $courses) {
echo "<meta charset='UTF-8'><table width=100% style=\" font-family:Arial; font-size:12px;\" >";
echo "<tr><td width=105>" . DrawLogo() . "</td><td style=\"font-size:15px; font-weight:bold; padding-top:20px;\">" . GetSchool(UserSchool()) . "<div style=\"font-size:12px;\">" . _('Student Schedules Report') . "</div></td><td align=right style=\"padding-top:20px;\">" . ProperDate(DBDate()) . "<br />" . _('Powered by openSIS') . "</td></tr><tr><td colspan=3 style=\"border-top:1px solid #333;\"> </td></tr></table>";
if ($_REQUEST['mailing_labels'] == 'Y') {
function _makeCodePulldown($value, $title)
{
global $THIS_RET, $codes_RET, $current_RET, $current_schedule_RET, $date;
if (!is_array($current_schedule_RET[$THIS_RET['STUDENT_ID']])) {
$current_schedule_RET[$THIS_RET['STUDENT_ID']] = DBGet(DBQuery('SELECT cp.PERIOD_ID,cp.COURSE_PERIOD_ID FROM schedule s,course_periods cp WHERE s.STUDENT_ID=\'' . $THIS_RET['STUDENT_ID'] . '\' AND s.SYEAR=\'' . UserSyear() . '\' AND s.SCHOOL_ID=\'' . UserSchool() . '\' AND cp.COURSE_PERIOD_ID = s.COURSE_PERIOD_ID AND cp.DOES_ATTENDANCE=\'Y\' AND s.MARKING_PERIOD_ID IN (' . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ') AND (\'' . $date . '\' BETWEEN s.START_DATE AND s.END_DATE OR s.END_DATE IS NULL)'), array(), array('PERIOD_ID'));
if (!$current_schedule_RET[$THIS_RET['STUDENT_ID']]) {
$current_schedule_RET[$THIS_RET['STUDENT_ID']] = array();
}
}
if ($THIS_RET['COURSE']) {
$period = $THIS_RET['COURSE_PERIOD_ID'];
$period_id = $THIS_RET['PERIOD_ID'];
foreach ($codes_RET as $code) {
$options[$code['ID']] = $code['TITLE'];
}
} else {
$period_id = substr($title, 7);
$period = $current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id][1]['COURSE_PERIOD_ID'];
foreach ($codes_RET as $code) {
$options[$code['ID']] = $code['SHORT_NAME'];
}
}
$val = $current_RET[$THIS_RET['STUDENT_ID']][$period][1]['ATTENDANCE_CODE'];
if ($current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id]) {
return SelectInput($val, 'attendance[' . $THIS_RET['STUDENT_ID'] . '][' . $period . '][ATTENDANCE_CODE]', '', $options);
} else {
return false;
}
}
function GetStuList(&$extra = array())
{
global $contacts_RET, $view_other_RET, $_ROSARIO;
if ((empty($extra['SELECT_ONLY']) || mb_strpos($extra['SELECT_ONLY'], 'GRADE_ID') !== false) && !isset($extra['functions']['GRADE_ID'])) {
$functions = array('GRADE_ID' => 'GetGrade');
} else {
$functions = array();
}
if (isset($extra['functions'])) {
$functions += $extra['functions'];
}
if (!isset($extra['MP']) && !isset($extra['DATE'])) {
$extra['MP'] = UserMP();
$extra['DATE'] = DBDate();
} elseif (!$extra['MP']) {
$extra['MP'] = GetCurrentMP('QTR', $extra['DATE'], false);
} elseif (!$extra['DATE']) {
$extra['DATE'] = DBDate();
}
if (isset($_REQUEST['expanded_view']) && $_REQUEST['expanded_view'] == 'true') {
if (!$extra['columns_after']) {
$extra['columns_after'] = array();
}
$view_fields_RET = DBGet(DBQuery("SELECT cf.ID,cf.TYPE,cf.TITLE FROM CUSTOM_FIELDS cf WHERE ((SELECT VALUE FROM PROGRAM_USER_CONFIG WHERE TITLE=cast(cf.ID AS TEXT) AND PROGRAM='StudentFieldsView' AND USER_ID='" . User('STAFF_ID') . "')='Y'" . ($extra['student_fields']['view'] ? " OR cf.ID IN (" . $extra['student_fields']['view'] . ")" : '') . ") ORDER BY cf.SORT_ORDER,cf.TITLE"));
$view_address_RET = DBGet(DBQuery("SELECT VALUE FROM PROGRAM_USER_CONFIG WHERE PROGRAM='StudentFieldsView' AND TITLE='ADDRESS' AND USER_ID='" . User('STAFF_ID') . "'"));
$view_address_RET = $view_address_RET[1]['VALUE'];
$view_other_RET = DBGet(DBQuery("SELECT TITLE,VALUE FROM PROGRAM_USER_CONFIG WHERE PROGRAM='StudentFieldsView' AND TITLE IN ('CONTACT_INFO','HOME_PHONE','GUARDIANS','ALL_CONTACTS') AND USER_ID='" . User('STAFF_ID') . "'"), array(), array('TITLE'));
if (!count($view_fields_RET) && !isset($view_address_RET) && !isset($view_other_RET['CONTACT_INFO'])) {
//modif Francois: add translation
$extra['columns_after'] = array('CONTACT_INFO' => '<IMG SRC="assets/down_phone_button.png" width="24">', 'CUSTOM_200000000' => _('Gender'), 'CUSTOM_200000001' => _('Ethnicity'), 'ADDRESS' => _('Mailing Address'), 'CITY' => _('City'), 'STATE' => _('State'), 'ZIPCODE' => _('Zipcode')) + $extra['columns_after'];
$select = ',ssm.STUDENT_ID AS CONTACT_INFO,s.CUSTOM_200000000,s.CUSTOM_200000001,coalesce(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,coalesce(a.MAIL_CITY,a.CITY) AS CITY,coalesce(a.MAIL_STATE,a.STATE) AS STATE,coalesce(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE ';
$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam.RESIDENCE='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];
$functions['CONTACT_INFO'] = 'makeContactInfo';
$RET = DBGet(DBQuery("SELECT ID,TYPE FROM CUSTOM_FIELDS WHERE ID IN ('200000000','200000001')"), array(), array('ID'));
// if gender and ethnicity are converted to codeds or exports type
if ($RET['200000000'][1]['TYPE'] == 'codeds' || $RET['200000000'][1]['TYPE'] == 'exports') {
$functions['CUSTOM_200000000'] = 'DeCodeds';
}
if ($RET['200000001'][1]['TYPE'] == 'codeds' || $RET['200000001'][1]['TYPE'] == 'exports') {
$functions['CUSTOM_200000001'] = 'DeCodeds';
}
$extra['singular'] = 'Student Address';
$extra['plural'] = 'Student Addresses';
$extra2['NoSearchTerms'] = true;
$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,a.PHONE,sjp.ADDRESS_ID ';
$extra2['FROM'] .= ',ADDRESS a,STUDENTS_JOIN_ADDRESS sja LEFT OUTER JOIN STUDENTS_JOIN_PEOPLE sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\')) LEFT OUTER JOIN PEOPLE p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN PEOPLE_JOIN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';
$extra2['WHERE'] .= ' AND a.ADDRESS_ID=sja.ADDRESS_ID AND sja.STUDENT_ID=ssm.STUDENT_ID ';
$extra2['ORDER_BY'] .= 'sjp.CUSTODY';
$extra2['group'] = array('STUDENT_ID', 'PERSON_ID');
// EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF
if (!isset($_REQUEST['_ROSARIO_PDF'])) {
$expanded_view = $_REQUEST['expanded_view'];
$_REQUEST['expanded_view'] = false;
$addr = $_REQUEST['addr'];
unset($_REQUEST['addr']);
$contacts_RET = GetStuList($extra2);
$_REQUEST['expanded_view'] = $expanded_view;
$_REQUEST['addr'] = $addr;
} else {
unset($extra2['columns_after']['CONTACT_INFO']);
}
} else {
if ($view_other_RET['CONTACT_INFO'][1]['VALUE'] == 'Y' && !isset($_REQUEST['_ROSARIO_PDF'])) {
$select .= ',ssm.STUDENT_ID AS CONTACT_INFO ';
$extra['columns_after']['CONTACT_INFO'] = '<IMG SRC="assets/down_phone_button.png" width="24">';
$functions['CONTACT_INFO'] = 'makeContactInfo';
$extra2 = $extra;
$extra2['NoSearchTerms'] = true;
$extra2['SELECT'] = '';
$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,a.PHONE,sjp.ADDRESS_ID ';
$extra2['FROM'] .= ',ADDRESS a,STUDENTS_JOIN_ADDRESS sja LEFT OUTER JOIN STUDENTS_JOIN_PEOPLE sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\')) LEFT OUTER JOIN PEOPLE p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN PEOPLE_JOIN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';
$extra2['WHERE'] .= ' AND a.ADDRESS_ID=sja.ADDRESS_ID AND sja.STUDENT_ID=ssm.STUDENT_ID ';
$extra2['ORDER_BY'] .= 'sjp.CUSTODY';
$extra2['group'] = array('STUDENT_ID', 'PERSON_ID');
$extra2['functions'] = array();
$extra2['link'] = array();
// EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF
$expanded_view = $_REQUEST['expanded_view'];
$_REQUEST['expanded_view'] = false;
$addr = $_REQUEST['addr'];
unset($_REQUEST['addr']);
$contacts_RET = GetStuList($extra2);
$_REQUEST['expanded_view'] = $expanded_view;
$_REQUEST['addr'] = $addr;
}
foreach ($view_fields_RET as $field) {
$extra['columns_after']['CUSTOM_' . $field['ID']] = $field['TITLE'];
if ($field['TYPE'] == 'date') {
$functions['CUSTOM_' . $field['ID']] = 'ProperDate';
} elseif ($field['TYPE'] == 'numeric') {
$functions['CUSTOM_' . $field['ID']] = 'removeDot00';
} elseif ($field['TYPE'] == 'codeds') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
} elseif ($field['TYPE'] == 'exports') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
}
$select .= ',s.CUSTOM_' . $field['ID'];
}
if ($view_address_RET) {
$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam." . $view_address_RET . "='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];
$extra['columns_after'] += array('ADDRESS' => _(ucwords(mb_strtolower(str_replace('_', ' ', $view_address_RET)))) . ' ' . _('Address'), 'CITY' => _('City'), 'STATE' => _('State'), 'ZIPCODE' => _('Zipcode'));
if ($view_address_RET != 'MAILING') {
$select .= ",a.ADDRESS_ID,a.ADDRESS,a.CITY,a.STATE,a.ZIPCODE,a.PHONE,ssm.STUDENT_ID AS PARENTS";
} else {
$select .= ",a.ADDRESS_ID,coalesce(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,coalesce(a.MAIL_CITY,a.CITY) AS CITY,coalesce(a.MAIL_STATE,a.STATE) AS STATE,coalesce(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE,a.PHONE,ssm.STUDENT_ID AS PARENTS ";
}
$extra['singular'] = 'Student Address';
$extra['plural'] = 'Student Addresses';
if ($view_other_RET['HOME_PHONE'][1]['VALUE'] == 'Y') {
$functions['PHONE'] = 'makePhone';
$extra['columns_after']['PHONE'] = _('Home Phone');
}
if ($view_other_RET['GUARDIANS'][1]['VALUE'] == 'Y' || $view_other_RET['ALL_CONTACTS'][1]['VALUE'] == 'Y') {
$functions['PARENTS'] = 'makeParents';
if ($view_other_RET['ALL_CONTACTS'][1]['VALUE'] == 'Y') {
$extra['columns_after']['PARENTS'] = _('Contacts');
} else {
$extra['columns_after']['PARENTS'] = _('Guardians');
}
}
} elseif ($_REQUEST['addr'] || $extra['addr']) {
$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID " . $extra['STUDENTS_JOIN_ADDRESS'] . ") LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];
$distinct = 'DISTINCT ';
}
}
$extra['SELECT'] .= $select;
} else {
if (isset($extra['student_fields']['view'])) {
if (!$extra['columns_after']) {
$extra['columns_after'] = array();
}
$view_fields_RET = DBGet(DBQuery("SELECT cf.ID,cf.TYPE,cf.TITLE FROM CUSTOM_FIELDS cf WHERE cf.ID IN (" . $extra['student_fields']['view'] . ") ORDER BY cf.SORT_ORDER,cf.TITLE"));
foreach ($view_fields_RET as $field) {
$extra['columns_after']['CUSTOM_' . $field['ID']] = $field['TITLE'];
if ($field['TYPE'] == 'date') {
$functions['CUSTOM_' . $field['ID']] = 'ProperDate';
} elseif ($field['TYPE'] == 'numeric') {
$functions['CUSTOM_' . $field['ID']] = 'removeDot00';
} elseif ($field['TYPE'] == 'codeds') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
} elseif ($field['TYPE'] == 'exports') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
}
$select .= ',s.CUSTOM_' . $field['ID'];
}
$extra['SELECT'] .= $select;
}
if (!empty($_REQUEST['addr']) || !empty($extra['addr'])) {
$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID " . $extra['STUDENTS_JOIN_ADDRESS'] . ") LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];
$distinct = 'DISTINCT ';
}
}
switch (User('PROFILE')) {
case 'admin':
$sql = 'SELECT ';
//$sql = 'SELECT '.$distinct;
if (isset($extra['SELECT_ONLY'])) {
$sql .= $extra['SELECT_ONLY'];
} else {
$sql .= "s.LAST_NAME||', '||s.FIRST_NAME||' '||coalesce(s.MIDDLE_NAME,' ') AS FULL_NAME,";
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
if (isset($_REQUEST['include_inactive']) && $_REQUEST['include_inactive'] == 'Y') {
$sql .= ',' . db_case(array("(ssm.SYEAR='" . UserSyear() . "' AND ('" . $extra['DATE'] . "'>=ssm.START_DATE AND ('" . $extra['DATE'] . "'<=ssm.END_DATE OR ssm.END_DATE IS NULL)))", 'TRUE', '\'<span style="color:green">' . _('Active') . '</span>\'', '\'<span style="color:red">' . _('Inactive') . '</span>\'')) . ' AS ACTIVE';
}
}
$sql .= " FROM STUDENTS s JOIN STUDENT_ENROLLMENT ssm ON (ssm.STUDENT_ID=s.STUDENT_ID";
if (isset($_REQUEST['include_inactive']) && $_REQUEST['include_inactive'] == 'Y') {
//$sql .= " AND ssm.ID=(SELECT max(ID) FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<='".UserSyear()."')";
$sql .= " AND ssm.ID=(SELECT ID FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<='" . UserSyear() . "' ORDER BY SYEAR DESC,START_DATE DESC LIMIT 1)";
} else {
$sql .= " AND ssm.SYEAR='" . UserSyear() . "' AND ('" . $extra['DATE'] . "'>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE))";
}
if (UserSchool() && $_REQUEST['_search_all_schools'] != 'Y') {
$sql .= " AND ssm.SCHOOL_ID='" . UserSchool() . "'";
} else {
if (User('SCHOOLS')) {
$sql .= " AND ssm.SCHOOL_ID IN (" . mb_substr(str_replace(',', "','", User('SCHOOLS')), 2, -2) . ") ";
}
$extra['columns_after']['LIST_SCHOOL_ID'] = 'School';
$functions['LIST_SCHOOL_ID'] = 'GetSchool';
}
$sql .= ")" . $extra['FROM'] . " WHERE TRUE";
if (empty($extra['SELECT_ONLY']) && isset($_REQUEST['include_inactive']) && $_REQUEST['include_inactive'] == 'Y') {
$extra['columns_after']['ACTIVE'] = _('Status');
}
break;
case 'teacher':
$sql = 'SELECT ';
//$sql = 'SELECT '.$distinct;
if ($extra['SELECT_ONLY']) {
$sql .= $extra['SELECT_ONLY'];
} else {
$sql .= "s.LAST_NAME||', '||s.FIRST_NAME||' '||coalesce(s.MIDDLE_NAME,' ') AS FULL_NAME,";
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= ',' . db_case(array("('" . $extra['DATE'] . "'>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE))", 'TRUE', '\'<span style="color:green">' . _('Active') . '</span>\'', '\'<span style="color:red">' . _('Inactive') . '</span>\'')) . ' AS ACTIVE';
$sql .= ',' . db_case(array("('" . $extra['DATE'] . "'>=ss.START_DATE AND (ss.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ss.END_DATE)) AND ss.MARKING_PERIOD_ID IN (" . GetAllMP($extra['MPTable'], $extra['MP']) . ")", 'TRUE', '\'<span style="color:green">' . _('Active') . '</span>\'', '\'<span style="color:red">' . _('Inactive') . '</span>\'')) . ' AS ACTIVE_SCHEDULE';
}
}
$sql .= " FROM STUDENTS s JOIN SCHEDULE ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.SYEAR='" . UserSyear() . "'";
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= " AND ss.START_DATE=(SELECT START_DATE FROM SCHEDULE WHERE STUDENT_ID=s.STUDENT_ID AND SYEAR=ss.SYEAR AND COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID ORDER BY START_DATE DESC LIMIT 1)";
} else {
$sql .= " AND ss.MARKING_PERIOD_ID IN (" . GetAllMP($extra['MPTable'], $extra['MP']) . ") AND ('" . $extra['DATE'] . "'>=ss.START_DATE AND ('" . $extra['DATE'] . "'<=ss.END_DATE OR ss.END_DATE IS NULL))";
}
$sql .= ") JOIN COURSE_PERIODS cp ON (cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND " . ($extra['all_courses'] == 'Y' ? "cp.TEACHER_ID='" . User('STAFF_ID') . "'" : "cp.COURSE_PERIOD_ID='" . UserCoursePeriod() . "'") . ")\n\t\t\t\tJOIN STUDENT_ENROLLMENT ssm ON (ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=ss.SYEAR AND ssm.SCHOOL_ID='" . UserSchool() . "'";
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= " AND ssm.ID=(SELECT ID FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR=ssm.SYEAR ORDER BY START_DATE DESC LIMIT 1)";
} else {
$sql .= " AND ('" . $extra['DATE'] . "'>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE))";
}
$sql .= ")" . $extra['FROM'] . " WHERE TRUE";
if (!$extra['SELECT_ONLY'] && $_REQUEST['include_inactive'] == 'Y') {
$extra['columns_after']['ACTIVE'] = _('School Status');
$extra['columns_after']['ACTIVE_SCHEDULE'] = _('Course Status');
}
break;
case 'parent':
case 'student':
$sql = 'SELECT ';
if ($extra['SELECT_ONLY']) {
$sql .= $extra['SELECT_ONLY'];
} else {
$sql .= "s.LAST_NAME||', '||s.FIRST_NAME||' '||coalesce(s.MIDDLE_NAME,' ') AS FULL_NAME,";
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
}
$sql .= " FROM STUDENTS s JOIN STUDENT_ENROLLMENT ssm ON (ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR='" . UserSyear() . "' AND ssm.SCHOOL_ID='" . UserSchool() . "'\n\t\t\t\t\tAND ('" . $extra['DATE'] . "'>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE)) AND s.STUDENT_ID" . ($extra['ASSOCIATED'] ? " IN (SELECT STUDENT_ID FROM STUDENTS_JOIN_USERS WHERE STAFF_ID='" . $extra['ASSOCIATED'] . "')" : "='" . UserStudentID() . "'");
$sql .= ")" . $extra['FROM'] . " WHERE TRUE";
break;
default:
exit(_('Error'));
}
//$sql = appendSQL($sql,array('NoSearchTerms'=>$extra['NoSearchTerms']));
$sql .= ' ' . $extra['WHERE'] . ' ';
if (isset($extra['GROUP'])) {
$sql .= ' GROUP BY ' . $extra['GROUP'];
}
if (!isset($extra['ORDER_BY']) && !isset($extra['SELECT_ONLY'])) {
$sql .= ' ORDER BY ';
if (Preferences('SORT') == 'Grade') {
$sql .= '(SELECT SORT_ORDER FROM SCHOOL_GRADELEVELS WHERE ID=ssm.GRADE_ID),';
}
// it would be easier to sort on full_name but postgres sometimes yields strange results
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME';
$sql .= $extra['ORDER'];
} elseif (isset($extra['ORDER_BY'])) {
$sql .= ' ORDER BY ' . $extra['ORDER_BY'];
}
//modif Francois: bugfix if PDF, dont echo SQL
if (!isset($_REQUEST['_ROSARIO_PDF']) && 0) {
//activate only for debug purpose
echo '<!--' . $sql . '-->';
}
return DBGet(DBQuery($sql), $functions, $extra['group']);
}
echo "<FORM action=Modules.php?modname={$_REQUEST['modname']} method=POST>";
DrawHeaderHome(PrepareDateSchedule($date, '_date', false, array('submit' => true)) . ' - ' . $period_select . ' : <INPUT type=submit class=btn_medium value=Go>');
echo '</FORM>';
$day = date('D', strtotime($date));
switch ($day) {
case 'Sun':
$day = 'U';
break;
case 'Thu':
$day = 'H';
break;
default:
$day = substr($day, 0, 1);
break;
}
$sql = "SELECT CONCAT(s.LAST_NAME,', ',s.FIRST_NAME) AS FULL_NAME,sp.TITLE,cp.PERIOD_ID,s.STAFF_ID\n\t\tFROM STAFF s,COURSE_PERIODS cp,SCHOOL_PERIODS sp\n\t\tWHERE\n\t\t\tsp.PERIOD_ID = cp.PERIOD_ID\n\t\t\tAND cp.TEACHER_ID=s.STAFF_ID AND cp.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ")\n\t\t\tAND cp.SYEAR='" . UserSyear() . "' AND cp.SCHOOL_ID='" . UserSchool() . "' AND s.PROFILE='teacher'\n\t\t\tAND cp.DOES_ATTENDANCE='Y' AND instr(cp.DAYS,'{$day}')>0" . ($_REQUEST['period'] ? " AND cp.PERIOD_ID='{$_REQUEST['period']}'" : '') . "\n\t\t\tAND NOT EXISTS (SELECT '' FROM ATTENDANCE_COMPLETED ac WHERE ac.STAFF_ID=cp.TEACHER_ID AND ac.SCHOOL_DATE='" . date('Y-m-d', strtotime($date)) . "' AND ac.PERIOD_ID=sp.PERIOD_ID)\n\t\t";
$RET = DBGet(DBQuery($sql), array(), array('STAFF_ID', 'PERIOD_ID'));
if (count($RET)) {
foreach ($RET as $staff_id => $periods) {
$i++;
$staff_RET[$i]['FULL_NAME'] = $periods[key($periods)][1]['FULL_NAME'];
foreach ($periods as $period_id => $period) {
$staff_RET[$i][$period_id] = '<IMG SRC=assets/x.gif>';
}
}
}
$columns = array('FULL_NAME' => 'Teacher');
if (!$_REQUEST['period']) {
foreach ($periods_RET as $id => $period) {
$columns[$id] = $period[1]['TITLE'];
}
function UpdateAttendanceDaily($student_id, $date = '', $comment = false)
{
if (!$date) {
$date = DBDate();
}
$current_mp = GetCurrentMP('QTR', $date);
$MP_TYPE = 'QTR';
if (!$current_mp) {
$current_mp = GetCurrentMP('SEM', $date);
$MP_TYPE = 'SEM';
}
if (!$current_mp) {
$current_mp = GetCurrentMP('FY', $date);
$MP_TYPE = 'FY';
}
$sql = 'SELECT
SUM(sp.LENGTH) AS TOTAL
FROM schedule s,course_periods cp,course_period_var cpv,school_periods sp,attendance_calendar ac
WHERE
s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND cpv.DOES_ATTENDANCE=\'Y\'
AND ac.SCHOOL_DATE=\'' . $date . '\' AND (ac.BLOCK=sp.BLOCK OR sp.BLOCK IS NULL)
AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID
AND ac.CALENDAR_ID=cp.CALENDAR_ID AND ac.SCHOOL_ID=s.SCHOOL_ID AND ac.SYEAR=s.SYEAR
AND s.SYEAR = cp.SYEAR AND sp.PERIOD_ID = cpv.PERIOD_ID
AND position(substring(\'UMTWHFS\' FROM DAYOFWEEK(\'' . $date . '\') FOR 1) IN cpv.DAYS)>0
AND s.STUDENT_ID=\'' . $student_id . '\'
AND s.SYEAR=\'' . UserSyear() . '\'
AND (\'' . $date . '\' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND \'' . $date . '\'>=s.START_DATE))
AND s.MARKING_PERIOD_ID IN (' . GetAllMP($MP_TYPE, $current_mp) . ')
';
$RET = DBGet(DBQuery($sql));
$total = $RET[1]['TOTAL'];
if ($total == 0) {
return;
}
$current_RET = DBGet(DBQuery('SELECT MINUTES_PRESENT,STATE_VALUE,COMMENT FROM attendance_day WHERE STUDENT_ID=' . $student_id . ' AND SCHOOL_DATE=\'' . $date . '\''));
$total = $current_RET['MINUTES_PRESENT'];
$sql = 'SELECT SUM(sp.LENGTH) AS TOTAL
FROM attendance_period ap,school_periods sp,attendance_codes ac
WHERE ap.STUDENT_ID=\'' . $student_id . '\' AND ap.SCHOOL_DATE=\'' . $date . '\' AND ap.PERIOD_ID=sp.PERIOD_ID AND ac.ID = ap.ATTENDANCE_CODE AND ac.STATE_CODE=\'P\'
AND sp.SYEAR=\'' . UserSyear() . '\'';
$RET = DBGet(DBQuery($sql));
$total += $RET[1]['TOTAL'];
$sql = 'SELECT SUM(sp.LENGTH) AS TOTAL
FROM attendance_period ap,school_periods sp,attendance_codes ac
WHERE ap.STUDENT_ID=\'' . $student_id . '\' AND ap.SCHOOL_DATE=\'' . $date . '\' AND ap.PERIOD_ID=sp.PERIOD_ID AND ac.ID = ap.ATTENDANCE_CODE AND ac.STATE_CODE=\'H\'
AND sp.SYEAR=\'' . UserSyear() . '\'';
$RET = DBGet(DBQuery($sql));
$total += $RET[1]['TOTAL'] * 0.5;
if (stripos($_SERVER['SERVER_SOFTWARE'], 'linux')) {
$comment = str_replace("'", "\\'", $comment);
}
$sys_pref = DBGet(DBQuery('SELECT * FROM system_preference WHERE SCHOOL_ID=' . UserSchool()));
$fdm = $sys_pref[1]['FULL_DAY_MINUTE'];
$hdm = $sys_pref[1]['HALF_DAY_MINUTE'];
if ($total >= $fdm) {
$length = '1.0';
} elseif ($total >= $hdm) {
$length = '.5';
} else {
$length = '0.0';
}
$current_RET = DBGet(DBQuery('SELECT MINUTES_PRESENT,STATE_VALUE,COMMENT FROM attendance_day WHERE STUDENT_ID=\'' . $student_id . '\' AND SCHOOL_DATE=\'' . $date . '\''));
if (count($current_RET) && $current_RET[1]['MINUTES_PRESENT'] == $total && $length != $current_RET[1]['STATE_VALUE']) {
DBQuery('UPDATE attendance_day SET STATE_VALUE=\'' . $length . '\' WHERE STUDENT_ID=\'' . $student_id . '\' AND SCHOOL_DATE=\'' . $date . '\'');
}
if (count($current_RET) && $current_RET[1]['MINUTES_PRESENT'] != $total) {
DBQuery('UPDATE attendance_day SET MINUTES_PRESENT=\'' . $total . '\',STATE_VALUE=\'' . $length . '\'' . ($comment != false ? ',COMMENT=\'' . str_replace("", "", $comment) . '\'' : '') . ' WHERE STUDENT_ID=\'' . $student_id . '\' AND SCHOOL_DATE=\'' . $date . '\'');
} elseif (count($current_RET) && $comment != false && $current_RET[1]['COMMENT'] != $comment) {
DBQuery('UPDATE attendance_day SET COMMENT=\'' . str_replace("", "", $comment) . '\' WHERE STUDENT_ID=\'' . $student_id . '\' AND SCHOOL_DATE=\'' . $date . '\'');
} elseif (count($current_RET) == 0) {
$check_assoc = DBGet(DBQuery('SELECT COUNT(*) as REC_EX FROM attendance_period ap,course_periods cp WHERE ap.STUDENT_ID=' . $student_id . ' AND ap.SCHOOL_DATE=\'' . $date . '\' AND cp.COURSE_PERIOD_ID=ap.COURSE_PERIOD_ID AND cp.SCHOOL_ID=' . UserSchool() . ' AND cp.SYEAR=' . UserSyear()));
if ($check_assoc[1]['REC_EX'] > 0) {
DBQuery('INSERT INTO attendance_day (SYEAR,STUDENT_ID,SCHOOL_DATE,MINUTES_PRESENT,STATE_VALUE,MARKING_PERIOD_ID,COMMENT) values(\'' . UserSyear() . '\',\'' . $student_id . '\',\'' . $date . '\',\'' . $total . '\',\'' . $length . '\',\'' . $current_mp . '\',\'' . str_replace("", "", $comment) . '\')');
}
}
}
echo "<div style='width:795px; overflow:scroll; overflow-y:hidden;'>";
DrawHeaderHome('<table><tr><td>' . PrepareDate($start_date, '_start') . '</td><td> - </td><td>' . PrepareDate($end_date, '_end') . '</td><td> : </td><td>' . $period_select . ' : <INPUT type=submit class=btn_medium value=Go></td></tr></table>');
}
$cal_RET = DBGet(DBQuery('SELECT DISTINCT SCHOOL_DATE,CONCAT(\'_\',DATE_FORMAT(SCHOOL_DATE,\'%y%b%d\')) AS SHORT_DATE FROM attendance_calendar WHERE SCHOOL_ID=\'' . UserSchool() . '\' AND SCHOOL_DATE BETWEEN \'' . date('Y-m-d', strtotime($start_date)) . '\' AND \'' . date('Y-m-d', strtotime($end_date)) . '\' ORDER BY SCHOOL_DATE'));
if (UserStudentID() || $_REQUEST['student_id'] || User('PROFILE') == 'parent') {
// JUST TO SET USERSTUDENTID()
Search('student_id');
if ($_REQUEST['period_id']) {
$sql = 'SELECT
cp.TITLE as COURSE_PERIOD,sp.TITLE as PERIOD,cpv.PERIOD_ID
FROM
schedule s,courses c,course_periods cp,course_period_var cpv,school_periods sp
WHERE
s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID
AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND cpv.PERIOD_ID = sp.PERIOD_ID AND cpv.DOES_ATTENDANCE=\'Y\'
AND s.SYEAR = c.SYEAR AND cp.MARKING_PERIOD_ID IN (' . GetAllMP('QTR', UserMP()) . ')
AND s.STUDENT_ID=\'' . UserStudentID() . '\' AND s.SYEAR=\'' . UserSyear() . '\'
AND (\'' . DBDate() . '\' BETWEEN s.START_DATE AND s.END_DATE OR s.END_DATE IS NULL)
ORDER BY sp.SORT_ORDER
';
$schedule_RET = DBGet(DBQuery($sql));
$sql = 'SELECT ap.SCHOOL_DATE,ap.PERIOD_ID,ac.SHORT_NAME,ac.STATE_CODE,ac.DEFAULT_CODE FROM attendance_period ap,attendance_codes ac WHERE ap.SCHOOL_DATE BETWEEN \'' . $start_date . '\' AND \'' . $end_date . '\' AND ap.ATTENDANCE_CODE=ac.ID AND ap.STUDENT_ID=\'' . UserStudentID() . '\'';
$attendance_RET = DBGet(DBQuery($sql), array(), array('SCHOOL_DATE', 'PERIOD_ID'));
} else {
$schedule_RET[1] = array('COURSE_PERIOD' => 'Daily Attendance', 'PERIOD_ID' => '0');
$attendance_RET = DBGet(DBQuery('SELECT ad.SCHOOL_DATE,\'0\' AS PERIOD_ID,ad.STATE_VALUE AS STATE_CODE,' . db_case(array('ad.STATE_VALUE', "'0.0'", "'A'", "'1.0'", "'P'", "'H'")) . ' AS SHORT_NAME FROM attendance_day ad WHERE ad.SCHOOL_DATE BETWEEN \'' . $start_date . '\' AND \'' . $end_date . '\' AND ad.STUDENT_ID=\'' . UserStudentID() . '\''), array(), array('SCHOOL_DATE', 'PERIOD_ID'));
}
$i = 0;
if (count($schedule_RET)) {
foreach ($schedule_RET as $course) {
$i++;
function calcSeats0($period)
{
$mp = $period['MARKING_PERIOD_ID'];
$seats = DBGet(DBQuery("SELECT max((SELECT count(1) FROM SCHEDULE ss JOIN STUDENT_ENROLLMENT sem ON (sem.STUDENT_ID=ss.STUDENT_ID AND sem.SYEAR=ss.SYEAR) WHERE ss.COURSE_PERIOD_ID='{$period['COURSE_PERIOD_ID']}' AND (ss.MARKING_PERIOD_ID='{$mp}' OR ss.MARKING_PERIOD_ID IN (" . GetAllMP(GetMP($mp, 'MP'), $mp) . ")) AND (ac.SCHOOL_DATE>=ss.START_DATE AND (ss.END_DATE IS NULL OR ac.SCHOOL_DATE<=ss.END_DATE)) AND (ac.SCHOOL_DATE>=sem.START_DATE AND (sem.END_DATE IS NULL OR ac.SCHOOL_DATE<=sem.END_DATE)))) AS FILLED_SEATS FROM ATTENDANCE_CALENDAR ac WHERE ac.CALENDAR_ID='{$period['CALENDAR_ID']}' AND ac.SCHOOL_DATE BETWEEN " . db_case(array("(CURRENT_DATE>'" . GetMP($mp, 'END_DATE') . "')", 'TRUE', "'" . GetMP($mp, 'START_DATE') . "'", 'CURRENT_DATE')) . " AND '" . GetMP($mp, 'END_DATE') . "'"));
return $seats[1]['FILLED_SEATS'];
}
$MP_TYPE = $MP_TYPE_RET[1]['MP_TYPE'];
if ($MP_TYPE == 'year') {
$MP_TYPE = 'FY';
} else {
if ($MP_TYPE == 'semester') {
$MP_TYPE = 'SEM';
} else {
if ($MP_TYPE == 'quarter') {
$MP_TYPE = 'QTR';
} else {
$MP_TYPE = '';
}
}
}
if ($_REQUEST['period_id']) {
$sql = "SELECT\r\n cp.TITLE as COURSE_PERIOD,sp.TITLE as PERIOD,cp.PERIOD_ID, cp.COURSE_PERIOD_ID\r\n FROM\r\n SCHEDULE s,COURSES c,COURSE_PERIODS cp,SCHOOL_PERIODS sp\r\n WHERE\r\n s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID\r\n AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND cp.PERIOD_ID = sp.PERIOD_ID AND cp.DOES_ATTENDANCE='Y'\r\n AND s.SYEAR = c.SYEAR AND cp.MARKING_PERIOD_ID IN (" . GetAllMP($MP_TYPE, UserMP()) . ")\r\n AND s.STUDENT_ID='" . UserStudentID() . "' AND s.SYEAR='" . UserSyear() . "'\r\n AND ('" . date('Y-m-d', strtotime(DBDate())) . "' BETWEEN s.START_DATE AND s.END_DATE OR s.END_DATE IS NULL)\r\n ORDER BY sp.SORT_ORDER\r\n ";
$schedule_RET = DBGet(DBQuery($sql));
$sql = "SELECT ap.SCHOOL_DATE,ap.COURSE_PERIOD_ID,ac.SHORT_NAME,ac.STATE_CODE,ac.DEFAULT_CODE FROM ATTENDANCE_PERIOD ap,ATTENDANCE_CODES ac WHERE ap.SCHOOL_DATE BETWEEN '" . date('Y-m-d', strtotime($start_date)) . "' AND '" . date('Y-m-d', strtotime($end_date)) . "' AND ap.ATTENDANCE_CODE=ac.ID AND ap.STUDENT_ID='" . UserStudentID() . "'";
$attendance_RET = DBGet(DBQuery($sql), array(), array('SCHOOL_DATE', 'COURSE_PERIOD_ID'));
} else {
$schedule_RET[1] = array('COURSE_PERIOD' => 'Daily Attendance', 'COURSE_PERIOD_ID' => '0');
$attendance_RET = DBGet(DBQuery("SELECT ad.SCHOOL_DATE,'0' AS COURSE_PERIOD_ID,ad.STATE_VALUE AS STATE_CODE," . db_case(array('ad.STATE_VALUE', "'0.0'", "'A'", "'1.0'", "'P'", "'H'")) . " AS SHORT_NAME FROM ATTENDANCE_DAY ad WHERE ad.SCHOOL_DATE BETWEEN '" . date('Y-m-d', strtotime($start_date)) . "' AND '" . date('Y-m-d', strtotime($end_date)) . "' AND ad.STUDENT_ID='" . UserStudentID() . "'"), array(), array('SCHOOL_DATE', 'COURSE_PERIOD_ID'));
}
$i = 0;
if (count($schedule_RET)) {
foreach ($schedule_RET as $course) {
$i++;
$student_RET[$i]['TITLE'] = $course['COURSE_PERIOD'];
foreach ($cal_RET as $value) {
$student_RET[$i][$value['SHORT_DATE']] = _makePeriodColor($attendance_RET[$value['SCHOOL_DATE']][$course['COURSE_PERIOD_ID']][1]['SHORT_NAME'], $attendance_RET[$value['SCHOOL_DATE']][$course['COURSE_PERIOD_ID']][1]['STATE_CODE'], $attendance_RET[$value['SCHOOL_DATE']][$course['COURSE_PERIOD_ID']][1]['DEFAULT_CODE']);
}
continue;
}
}
# ------------------------------------ PARENT RESTRICTION ENDS----------------------------------------- #
if ($_SESSION['MassSchedule.php']['gender'] != 'N') {
$select_stu_RET = DBGet(DBQuery('SELECT FIRST_NAME,LAST_NAME,LEFT(GENDER,1) AS GENDER FROM students WHERE STUDENT_ID=\'' . $student_id . '\''));
if ($_SESSION['MassSchedule.php']['gender'] != $select_stu_RET[1]['GENDER']) {
$select_stu = $select_stu_RET[1]['FIRST_NAME'] . " " . $select_stu_RET[1]['LAST_NAME'];
$gender_conflict .= $select_stu . "<br>";
continue;
}
#$clash = true;
}
# ------------------------------------ Same Days Conflict Start ------------------------------------------ #
$mp_RET = DBGet(DBQuery('SELECT cp.MP,cp.MARKING_PERIOD_ID,cpv.DAYS,cpv.PERIOD_ID,cp.MARKING_PERIOD_ID,cp.TOTAL_SEATS,COALESCE(cp.FILLED_SEATS,0) AS FILLED_SEATS FROM course_periods cp,course_period_var cpv WHERE cp.COURSE_PERIOD_ID=\'' . $_SESSION['MassSchedule.php']['course_period_id'] . '\' AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID'));
$mps = GetAllMP(GetMPTable(GetMP($mp_RET[1]['MARKING_PERIOD_ID'], 'TABLE')), $mp_RET[1]['MARKING_PERIOD_ID']);
$period_RET = DBGet(DBQuery('SELECT cpv.DAYS FROM schedule s,course_periods cp,course_period_var cpv WHERE cp.COURSE_PERIOD_ID=s.COURSE_PERIOD_ID AND s.STUDENT_ID=\'' . $student_id . '\' AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID AND cpv.PERIOD_ID=\'' . $mp_RET[1]['PERIOD_ID'] . '\' AND s.MARKING_PERIOD_ID IN (' . $mps . ') AND (s.END_DATE IS NULL OR \'' . $convdate . '\'<=s.END_DATE)'));
$ig_scheld = DBGet(DBQuery('SELECT IGNORE_SCHEDULING FROM school_periods WHERE PERIOD_ID=\'' . $mp_RET[1]['PERIOD_ID'] . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''));
$sql_dupl = 'SELECT COURSE_PERIOD_ID FROM schedule WHERE STUDENT_ID = \'' . $student_id . '\' AND COURSE_PERIOD_ID = \'' . $_SESSION['MassSchedule.php']['course_period_id'] . '\' AND (END_DATE IS NULL OR (\'' . $convdate . '\' BETWEEN START_DATE AND END_DATE)) AND SCHOOL_ID=\'' . UserSchool() . '\'';
$rit_dupl = DBQuery($sql_dupl);
$count_entry = mysql_num_rows($rit_dupl);
$days_conflict = false;
if ($count_entry < 1 && $ig_scheld[1]['IGNORE_SCHEDULING'] != 'Y') {
foreach ($period_RET as $existing) {
if (strlen($mp_RET[1]['DAYS']) + strlen($existing['DAYS']) > 7) {
$days_conflict = true;
break;
} else {
foreach (_str_split($mp_RET[1]['DAYS']) as $i) {
if (strpos($existing['DAYS'], $i) !== false) {
$days_conflict = true;
# 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 '../../Redirect_modules.php';
DrawBC(_('Gradebook') . " > " . ProgramTitle());
if (!$_REQUEST['LO_sort']) {
$_REQUEST['LO_sort'] = "CUM_RANK";
$_REQUEST['LO_direction'] = 1;
}
if ($_REQUEST['search_modfunc'] == 'list') {
if (!$_REQUEST['mp'] && GetMP(UserMP(), 'POST_START_DATE')) {
$_REQUEST['mp'] = UserMP();
} elseif (strpos(GetAllMP('QTR', UserMP()), str_replace('E', '', $_REQUEST['mp'])) === false && strpos(GetChildrenMP('PRO', UserMP()), "'" . $_REQUEST['mp'] . "'") === false && GetMP(UserMP(), 'POST_START_DATE')) {
$_REQUEST['mp'] = UserMP();
}
if (!$_REQUEST['mp'] && GetMP(GetParentMP('SEM', UserMP()), 'POST_START_DATE')) {
$_REQUEST['mp'] = GetParentMP('SEM', UserMP());
}
$sem = GetParentMP('SEM', UserMP());
$pro = GetChildrenMP('PRO', UserMP());
$pros = explode(',', str_replace("'", '', $pro));
$pro_grading = false;
$pro_select = '';
foreach ($pros as $pro) {
if (GetMP($pro, 'POST_START_DATE')) {
if (!$_REQUEST['mp']) {
$_REQUEST['mp'] = $pro;
$current_RET = DBGet(DBQuery('SELECT g.STUDENT_ID,g.REPORT_CARD_GRADE_ID,g.REPORT_CARD_COMMENT_ID,g.COMMENT FROM student_report_card_grades g,course_periods cp WHERE cp.COURSE_PERIOD_ID=g.COURSE_PERIOD_ID AND cp.COURSE_PERIOD_ID=' . $course_period_id . ' AND g.MARKING_PERIOD_ID=\'' . $_REQUEST['mp'] . '\''), array(), array('STUDENT_ID'));
$date_select .= '<OPTION value="' . $i . '"' . ($i + 86400 >= $start_time && $i - 86400 <= $start_time ? ' SELECTED="SELECTED"' : '') . '>' . ProperDate(date('Y.m.d', $i)) . ' - ' . ProperDate(date('Y.m.d', $i + 1 + ($END_DAY - $START_DAY) * 60 * 60 * 24)) . '</OPTION>';
}
}
DrawHeader(_('Timeframe') . ': <SELECT name="start_date">' . $date_select . '</SELECT> - ' . _('Period') . ': ' . $period_select, SubmitButton(_('Go')));
echo '</FORM>';
//modif Francois: multiple school periods for a course period
/*$sql = "SELECT s.LAST_NAME||', '||s.FIRST_NAME AS FULL_NAME,sp.TITLE,cp.PERIOD_ID,s.STAFF_ID
FROM STAFF s,COURSE_PERIODS cp,SCHOOL_PERIODS sp
WHERE
sp.PERIOD_ID = cp.PERIOD_ID
AND cp.TEACHER_ID=s.STAFF_ID AND cp.MARKING_PERIOD_ID IN (".GetAllMP('QTR',UserMP()).")
AND cp.SYEAR='".UserSyear()."' AND cp.SCHOOL_ID='".UserSchool()."' AND s.PROFILE='teacher'
".(($_REQUEST['period'])?" AND cp.PERIOD_ID='$_REQUEST[period]'":'')."
AND NOT EXISTS (SELECT '' FROM ELIGIBILITY_COMPLETED ac WHERE ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID = sp.PERIOD_ID AND ac.SCHOOL_DATE BETWEEN '$start_date' AND '$end_date')
";*/
$sql = "SELECT s.LAST_NAME||', '||s.FIRST_NAME AS FULL_NAME,sp.TITLE,cpsp.PERIOD_ID,s.STAFF_ID \n\t\tFROM STAFF s,COURSE_PERIODS cp,SCHOOL_PERIODS sp,COURSE_PERIOD_SCHOOL_PERIODS cpsp \n\t\tWHERE \n\t\t\tcp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND \n\t\t\tsp.PERIOD_ID = cpsp.PERIOD_ID\n\t\t\tAND cp.TEACHER_ID=s.STAFF_ID AND cp.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', UserMP()) . ")\n\t\t\tAND cp.SYEAR='" . UserSyear() . "' AND cp.SCHOOL_ID='" . UserSchool() . "' AND s.PROFILE='teacher'\n\t\t\t" . ($_REQUEST['period'] ? " AND cpsp.PERIOD_ID='{$_REQUEST['period']}'" : '') . "\n\t\t\tAND NOT EXISTS (SELECT '' FROM ELIGIBILITY_COMPLETED ac WHERE ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID = sp.PERIOD_ID AND ac.SCHOOL_DATE BETWEEN '{$start_date}' AND '{$end_date}')\n\t\t";
$RET = DBGet(DBQuery($sql), array(), array('STAFF_ID', 'PERIOD_ID'));
$i = 0;
if (count($RET)) {
foreach ($RET as $staff_id => $periods) {
$i++;
$staff_RET[$i]['FULL_NAME'] = $periods[key($periods)][1]['FULL_NAME'];
foreach ($periods as $period_id => $period) {
$staff_RET[$i][$period_id] = '<IMG SRC="assets/x.png" height="24">';
}
}
}
$columns = array('FULL_NAME' => _('Teacher'));
if (!$_REQUEST['period']) {
foreach ($periods_RET as $period) {
$columns[$period['PERIOD_ID']] = $period['TITLE'];
function GetStaffList(&$extra)
{
global $profiles_RET, $_CENTRE;
$functions = array('PROFILE' => 'makeProfile');
switch (User('PROFILE')) {
case 'admin':
case 'teacher':
if ($_REQUEST['expanded_view'] == 'true') {
$select = ',LAST_LOGIN';
$extra['columns_after']['LAST_LOGIN'] = _('Last Login');
$functions['LAST_LOGIN'] = '******';
$view_fields_RET = DBGet(DBQuery("SELECT cf.ID,cf.TYPE,cf.TITLE FROM STAFF_FIELDS cf WHERE ((SELECT VALUE FROM PROGRAM_USER_CONFIG WHERE TITLE=cast(cf.ID AS TEXT) AND PROGRAM='StaffFieldsView' AND USER_ID='" . User('STAFF_ID') . "')='Y'" . ($extra['staff_fields']['view'] ? " OR cf.ID IN (" . $extra['staff_fields']['view'] . ")" : '') . ") ORDER BY cf.SORT_ORDER,cf.TITLE"));
foreach ($view_fields_RET as $field) {
$extra['columns_after']['CUSTOM_' . $field['ID']] = $field['TITLE'];
if ($field['TYPE'] == 'date') {
$functions['CUSTOM_' . $field['ID']] = 'ProperDate';
} elseif ($field['TYPE'] == 'numeric') {
$functions['CUSTOM_' . $field['ID']] = 'removeDot00';
} elseif ($field['TYPE'] == 'codeds') {
$functions['CUSTOM_' . $field['ID']] = 'StaffDeCodeds';
} elseif ($field['TYPE'] == 'exports') {
$functions['CUSTOM_' . $field['ID']] = 'StaffDeCodeds';
}
$select .= ',s.CUSTOM_' . $field['ID'];
}
$extra['SELECT'] .= $select;
} else {
if (!$extra['columns_after']) {
$extra['columns_after'] = array();
}
if ($extra['staff_fields']['view']) {
$view_fields_RET = DBGet(DBQuery("SELECT cf.ID,cf.TYPE,cf.TITLE FROM STAFF_FIELDS cf WHERE cf.ID IN (" . $extra['staff_fields']['view'] . ") ORDER BY cf.SORT_ORDER,cf.TITLE"));
foreach ($view_fields_RET as $field) {
$extra['columns_after']['CUSTOM_' . $field['ID']] = $field['TITLE'];
if ($field['TYPE'] == 'date') {
$functions['CUSTOM_' . $field['ID']] = 'ProperDate';
} elseif ($field['TYPE'] == 'numeric') {
$functions['CUSTOM_' . $field['ID']] = 'removeDot00';
} elseif ($field['TYPE'] == 'codeds') {
$functions['CUSTOM_' . $field['ID']] = 'StaffDeCodeds';
} elseif ($field['TYPE'] == 'exports') {
$functions['CUSTOM_' . $field['ID']] = 'StaffDeCodeds';
}
$select .= ',s.CUSTOM_' . $field['ID'];
}
$extra['SELECT'] .= $select;
}
}
if (User('PROFILE') != 'admin') {
$extra['WHERE'] .= " AND (s.STAFF_ID='" . User('STAFF_ID') . "' OR s.PROFILE='parent' AND exists(SELECT '' FROM STUDENTS_JOIN_USERS _sju,STUDENT_ENROLLMENT _sem,SCHEDULE _ss WHERE _sju.STAFF_ID=s.STAFF_ID AND _sem.STUDENT_ID=_sju.STUDENT_ID AND _sem.SYEAR='" . UserSYEAR() . "' AND _ss.STUDENT_ID=_sem.STUDENT_ID AND _ss.COURSE_PERIOD_ID='" . UserCoursePeriod() . "'";
if ($_REQUEST['include_inactive'] != 'Y') {
$extra['WHERE'] .= " AND _ss.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', UserMP()) . ") AND ('" . DBDate() . "'>=_sem.START_DATE AND ('" . DBDate() . "'<=_sem.END_DATE OR _sem.END_DATE IS NULL)) AND ('" . DBDate() . "'>=_ss.START_DATE AND ('" . DBDate() . "'<=_ss.END_DATE OR _ss.END_DATE IS NULL))";
}
$extra['WHERE'] .= "))";
}
$profiles_RET = DBGet(DBQuery("SELECT * FROM USER_PROFILES"), array(), array('ID'));
$sql = "SELECT\n\t\t\t\t\ts.LAST_NAME||', '||s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ') AS FULL_NAME,\n\t\t\t\t\ts.PROFILE,s.PROFILE_ID,s.STAFF_ID,s.SCHOOLS " . $extra['SELECT'] . "\n\t\t\t\tFROM\n\t\t\t\t\tSTAFF s " . $extra['FROM'] . "\n\t\t\t\tWHERE\n\t\t\t\t\ts.SYEAR='" . UserSyear() . "'";
//$sql = appendStaffSQL($sql,array('NoSearchTerms'=>$extra['NoSearchTerms']));
if ($_REQUEST['_search_all_schools'] != 'Y') {
$sql .= " AND (s.SCHOOLS LIKE '%," . UserSchool() . ",%' OR s.SCHOOLS IS NULL OR s.SCHOOLS='') ";
}
$sql .= $extra['WHERE'] . ' ';
//$sql .= CustomFields('where','staff',array('NoSearchTerms'=>$extra['NoSearchTerms']));
// it would be easier to sort on full_name but postgres sometimes yields strange results
$sql .= 'ORDER BY s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME';
if ($extra['functions']) {
$functions += $extra['functions'];
}
return DBGet(DBQuery($sql), $functions);
break;
}
}
$courses_RET[$key]['row_color'] = Preferences('HIGHLIGHT');
}
}
}
}
echo '<TD valign=top>';
$columns = array('GRADE_COURSE' => 'Course');
$link = array();
$link['GRADE_COURSE']['link'] = "Modules.php?modname={$_REQUEST['modname']}&subject_id={$_REQUEST['subject_id']}";
//$link['TITLE']['link'] = "#"." onclick='check_content(\"ajax.php?modname=$_REQUEST[modname]&subject_id=$_REQUEST[subject_id]\");'";
$link['GRADE_COURSE']['variables'] = array('course_id' => 'COURSE_ID');
$link['add']['link'] = "Modules.php?modname={$_REQUEST['modname']}&subject_id={$_REQUEST['subject_id']}&course_id=new";
ListOutput($courses_RET, $columns, 'Course', 'Courses', $link, array(), $LO_options);
echo '</TD>';
if (clean_param($_REQUEST['course_id'], PARAM_ALPHANUM) && $_REQUEST['course_id'] != 'new') {
$sql = "SELECT COURSE_PERIOD_ID,TITLE,COALESCE(TOTAL_SEATS-FILLED_SEATS,0) AS AVAILABLE_SEATS FROM course_periods WHERE COURSE_ID='{$_REQUEST['course_id']}' AND (marking_period_id IN(" . GetAllMP(GetMPTable(GetMP(UserMP(), 'TABLE')), UserMP()) . ") OR (CURDATE() <= end_date AND marking_period_id IS NULL)) ORDER BY TITLE";
$QI = DBQuery($sql);
$periods_RET = DBGet($QI);
if (count($periods_RET)) {
if (clean_param($_REQUEST['course_period_id'], PARAM_ALPHANUM)) {
foreach ($periods_RET as $key => $value) {
if ($value['COURSE_PERIOD_ID'] == $_REQUEST['course_period_id']) {
$periods_RET[$key]['row_color'] = Preferences('HIGHLIGHT');
}
}
}
}
echo '<TD valign=top>';
$columns = array('TITLE' => 'Period');
if ($_REQUEST['modname'] == 'Schdeuling/Schedule.php') {
$columns += array('AVAILABLE_SEATS' => 'Available Seats');
<?php
$QI = DBQuery("SELECT PERIOD_ID,TITLE FROM SCHOOL_PERIODS WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' ORDER BY SORT_ORDER ");
$RET = DBGet($QI);
//modif Francois: fix bug scale for the working year
$SCALE_RET = DBGet(DBQuery("SELECT * from schools where ID = '" . UserSchool() . "' AND SYEAR='" . UserSyear() . "'"));
DrawHeader(ProgramTitle());
$mps = GetAllMP('PRO', UserMP());
$mps = explode(',', str_replace("'", '', $mps));
//modif Francois: add translation
$table = '<TABLE><TR><TD class="valign-top"><TABLE>
<TR>
<TD style="text-align:right; vertical-align: top;"><span style="color:gray">' . _('Calculate GPA for') . '</span></TD>
<TD>';
foreach ($mps as $mp) {
if ($mp != '0') {
//modif Francois: add <label> on radio
$table .= '<label><INPUT type="radio" name="marking_period_id" value="' . $mp . '"' . ($mp == UserMP() ? ' checked' : '') . '> ' . GetMP($mp) . '</label><BR />';
}
}
$table .= '</TD>
</TR>
<TR>
<TD colspan="2" class="center"><span style="color:gray">' . sprintf(_('GPA based on a scale of %d'), $SCALE_RET[1]['REPORTING_GP_SCALE']) . '</span></TD>
</TR>' . '</TABLE></TD><TD style="width:350px;">' . _('GPA calculation modifies existing records.') . '<BR /><BR />' . _('Weighted and unweighted GPA is calculated by dividing the weighted and unweighted grade points configured for each letter grade (assigned in the Report Card Codes setup program) by the base grading scale specified in the school setup.') . ' </TD></TR></TABLE>';
$go = Prompt(_('GPA Calculation'), _('Calculate GPA and Class Rank'), $table);
if ($go) {
//modif Francois: waiting message
echo '<BR />';
PopTable('header', _('Calculating GPA and class rank'));
echo '<span class="center"><DIV id="statusDIV"><IMG SRC="assets/spinning.gif"> ' . _('Calculating ...') . ' </DIV></span>';
function calcSeats1($period, &$total_seats, &$filled_seats)
{
if ($_REQUEST['include_child_mps']) {
$mps = GetChildrenMP($period['MP'], $period['MARKING_PERIOD_ID']);
if ($period['MP'] == 'FY' || $period['MP'] == 'SEM') {
$mps = "'{$period['MARKING_PERIOD_ID']}'" . ($mps ? ',' . $mps : '');
}
} else {
$mps = "'" . $period['MARKING_PERIOD_ID'] . "'";
}
foreach (explode(',', $mps) as $mp) {
$mp = trim($mp, "'");
$seats = DBGet(DBQuery("SELECT max((SELECT count(1) FROM SCHEDULE ss JOIN STUDENT_ENROLLMENT sem ON (sem.STUDENT_ID=ss.STUDENT_ID AND sem.SYEAR=ss.SYEAR) WHERE ss.COURSE_PERIOD_ID='{$period['COURSE_PERIOD_ID']}' AND (ss.MARKING_PERIOD_ID='{$mp}' OR ss.MARKING_PERIOD_ID IN (" . GetAllMP(GetMP($mp, 'MP'), $mp) . ")) AND (ac.SCHOOL_DATE>=ss.START_DATE AND (ss.END_DATE IS NULL OR ac.SCHOOL_DATE<=ss.END_DATE)) AND (ac.SCHOOL_DATE>=sem.START_DATE AND (sem.END_DATE IS NULL OR ac.SCHOOL_DATE<=sem.END_DATE)))) AS FILLED_SEATS FROM ATTENDANCE_CALENDAR ac WHERE ac.CALENDAR_ID='{$period['CALENDAR_ID']}' AND ac.SCHOOL_DATE BETWEEN " . db_case(array("(CURRENT_DATE>'" . GetMP($mp, 'END_DATE') . "')", 'TRUE', "'" . GetMP($mp, 'START_DATE') . "'", 'CURRENT_DATE')) . " AND '" . GetMP($mp, 'END_DATE') . "'"));
if ($_REQUEST['include_child_mps']) {
if ($total_seats[$mp] !== false) {
if ($period['TOTAL_SEATS']) {
$total_seats[$mp] += $period['TOTAL_SEATS'];
} else {
$total_seats[$mp] = false;
}
}
if ($filled_seats !== false) {
if ($seats[1]['FILLED_SEATS'] != '') {
$filled_seats[$mp] += $seats[1]['FILLED_SEATS'];
} else {
$filled_seats[$mp] = false;
}
}
} else {
if ($total_seats !== false) {
if ($period['TOTAL_SEATS']) {
$total_seats += $period['TOTAL_SEATS'];
} else {
$total_seats = false;
}
}
if ($filled_seats !== false) {
if ($seats[1]['FILLED_SEATS'] != '') {
$filled_seats += $seats[1]['FILLED_SEATS'];
} else {
$filled_seats = false;
}
}
}
}
}
# 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 '../../Redirect_modules.php';
echo '<div id="calculating" style="display: none; padding-top:20px; padding-bottom:15px;"><img src="assets/missing_attn_loader.gif" /><br/><br/><br/><span style="color:#c90000;"><span style=" font-size:15px; font-weight:bold;">Please wait.</span><br /><span style=" font-size:12px;">Calculating GPA . Do not click anywhere.</span></span></div>
<div id="resp" style="font-size:14px"></div>';
$QI = DBQuery("SELECT PERIOD_ID,TITLE FROM school_periods WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' ORDER BY SORT_ORDER ");
$RET = DBGet($QI);
$SCALE_RET = DBGet(DBQuery('SELECT * from schools where ID = \'' . UserSchool() . '\''));
DrawBC("Gradebook > " . ProgramTitle());
$mps = GetAllMP(GetMPTable(GetMP(UserMP(), 'TABLE')), UserMP());
$mps = explode(',', str_replace("'", '', $mps));
$table = '<TABLE><TR><TD valign=top><TABLE>
</TR>
<TD align=right valign=top><font color=gray>Calculate GPA for</font></TD>
<TD>';
foreach ($mps as $mp) {
if ($mp != '0') {
$table .= '<INPUT type=radio name=marking_period_id value=' . $mp . ($mp == UserMP() ? ' CHECKED' : '') . '>' . GetMP($mp) . '<BR>';
}
}
$table .= '</TD>
</TR>
<TR>
<TD colspan = 2 align=center><font color=gray>GPA based on a scale of ' . $SCALE_RET[1]['REPORTING_GP_SCALE'] . '</TD>
</TR>' . '</TABLE></TD><TD width=350><small>GPA calculation modifies existing records.<BR><BR>Weighted and unweighted GPA is calculated by dividing the weighted and unweighted grade points configured for each letter grade (assigned in the Report Card Codes setup program) by the base grading scale specified in the school setup. </small></TD></TR></TABLE>';
$periods_list = '(' . substr($periods_list, 1) . ')';
foreach ($_REQUEST['student'] as $student_id => $yes) {
$students_list .= ",'" . $student_id . "'";
}
$students_list = '(' . substr($students_list, 1) . ')';
$current_RET = DBGet(DBQuery('SELECT STUDENT_ID,PERIOD_ID,SCHOOL_DATE,ATTENDANCE_CODE FROM attendance_period WHERE EXTRACT(MONTH FROM SCHOOL_DATE)=\'' . $_REQUEST['month'] * 1 . '\' AND EXTRACT(YEAR FROM SCHOOL_DATE)=\'' . $_REQUEST[year] . '\' AND PERIOD_ID IN ' . $periods_list . ' AND STUDENT_ID IN ' . $students_list . ''), array(), array('STUDENT_ID', 'SCHOOL_DATE', 'PERIOD_ID'));
foreach ($_REQUEST['student'] as $student_id => $yes) {
foreach ($_REQUEST['dates'] as $date => $yes) {
$current_mp = GetCurrentMP('QTR', $date);
if (!$current_mp) {
$current_mp = GetCurrentMP('SEM', $date);
}
if (!$current_mp) {
$current_mp = GetCurrentMP('FY', $date);
}
$all_mp = GetAllMP(GetMPTable(GetMP($current_mp, 'TABLE')), $current_mp);
/*$course_periods_RET = DBGet(DBQuery("SELECT s.COURSE_PERIOD_ID,cp.PERIOD_ID FROM schedule s,course_periods cp,attendance_calendar ac,school_periods sp WHERE sp.PERIOD_ID=cp.PERIOD_ID AND ac.SCHOOL_DATE='".date('Y-m-d',strtotime($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' AND cp.PERIOD_ID IN $periods_list AND cp.DOES_ATTENDANCE='Y' 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)) AND position(substring('UMTWHFS' FROM DAYOFWEEK(ac.SCHOOL_DATE) FOR 1) IN cp.DAYS)>0 AND cp.MARKING_PERIOD_ID IN ($all_mp) AND s.MARKING_PERIOD_ID IN ($all_mp) AND NOT (cp.HALF_DAY='Y' AND (SELECT STATE_CODE FROM attendance_codes WHERE ID='$_REQUEST[absence_code]')='H')"),array(),array('PERIOD_ID'));*/
$course_periods_RET = DBGet(DBQuery('SELECT s.COURSE_PERIOD_ID,cpv.PERIOD_ID FROM schedule s,course_periods cp,course_period_var cpv,attendance_calendar ac,school_periods sp WHERE sp.PERIOD_ID=cpv.PERIOD_ID AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID AND ac.SCHOOL_DATE=\'' . date('Y-m-d', strtotime($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 . ' AND cpv.PERIOD_ID IN ' . $periods_list . ' AND cpv.DOES_ATTENDANCE=\'Y\' 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)) AND position(substring(\'UMTWHFS\' FROM DAYOFWEEK(ac.SCHOOL_DATE) FOR 1) IN cpv.DAYS)>0 AND cp.MARKING_PERIOD_ID IN (' . $all_mp . ') AND s.MARKING_PERIOD_ID IN (' . $all_mp . ') AND NOT (cp.HALF_DAY=\'Y\' AND (SELECT STATE_CODE FROM attendance_codes WHERE ID=\'' . optional_param('absence_code', '', PARAM_NUMBER) . '\')=\'H\')'), array(), array('PERIOD_ID'));
foreach ($_REQUEST['period'] as $period_id => $yes) {
if (!$current_RET[$student_id][$date][$period_id]) {
$course_period_id = $course_periods_RET[$period_id][1]['COURSE_PERIOD_ID'];
if ($course_period_id) {
/*$sql = "INSERT INTO attendance_period (STUDENT_ID,SCHOOL_DATE,PERIOD_ID,MARKING_PERIOD_ID,COURSE_PERIOD_ID,ATTENDANCE_CODE,ATTENDANCE_TEACHER_CODE,ATTENDANCE_REASON,ADMIN)
values('$student_id','$date','$period_id','$current_mp','$course_period_id','$_REQUEST[absence_code]','$_REQUEST[absence_code]','$_REQUEST[absence_reason]','Y')";*/
$sql = 'INSERT INTO attendance_period (STUDENT_ID,SCHOOL_DATE,PERIOD_ID,MARKING_PERIOD_ID,COURSE_PERIOD_ID,ATTENDANCE_CODE,ATTENDANCE_TEACHER_CODE,ATTENDANCE_REASON,ADMIN)values(\'' . $student_id . '\',\'' . $date . '\',\'' . $period_id . '\',\'' . $current_mp . '\',\'' . $course_period_id . '\',\'' . optional_param('absence_code', '', PARAM_NUMBER) . '\',\'' . optional_param('absence_code', '', PARAM_NUMBER) . '\',\'' . optional_param('absence_reason', '', PARAM_SPCL) . '\',\'Y\')';
DBQuery($sql);
}
} else {
/*$sql = "UPDATE attendance_period SET ATTENDANCE_CODE='$_REQUEST[absence_code]',ATTENDANCE_TEACHER_CODE='$_REQUEST[absence_code]',ATTENDANCE_REASON='$_REQUEST[absence_reason]',ADMIN='Y'
WHERE STUDENT_ID='$student_id' AND SCHOOL_DATE='$date' AND PERIOD_ID='$period_id'";
*/
$period_select .= "<OPTION value={$period['PERIOD_ID']}" . ($_REQUEST['period'] == $period['PERIOD_ID'] ? ' SELECTED' : '') . ">" . $period['TITLE'] . "</OPTION>";
}
$period_select .= "</SELECT>";
DrawBC("Eligibility > " . ProgramTitle());
echo "<FORM name=teach_comp id=teach_comp action=Modules.php?modname={$_REQUEST['modname']} method=POST>";
$begin_year = DBGet(DBQuery("SELECT min(unix_timestamp(SCHOOL_DATE)) as SCHOOL_DATE FROM ATTENDANCE_CALENDAR WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "'"));
$begin_year = $begin_year[1]['SCHOOL_DATE'];
if ($start && $begin_year) {
$date_select = "<OPTION value={$start}>" . date('M d, Y', $start) . ' - ' . date('M d, Y', $end) . '</OPTION>';
for ($i = $start - 60 * 60 * 24 * 7; $i >= $begin_year; $i -= 60 * 60 * 24 * 7) {
$date_select .= "<OPTION value={$i}" . ($i + 86400 >= $start_time && $i - 86400 <= $start_time ? ' SELECTED' : '') . ">" . date('M d, Y', $i) . ' - ' . date('M d, Y', $i + 1 + ($END_DAY - $START_DAY) * 60 * 60 * 24) . '</OPTION>';
}
}
DrawHeaderHome('<SELECT name=start_date>' . $date_select . '</SELECT>' . $period_select, '<INPUT type=submit class=btn_medium value=Go onclick=\'formload_ajax("teach_comp");\'>');
echo '</FORM>';
$sql = "SELECT CONCAT(s.LAST_NAME,', ',s.FIRST_NAME) AS FULL_NAME,sp.TITLE,cp.PERIOD_ID,s.STAFF_ID \n\t\tFROM STAFF s,COURSE_PERIODS cp,SCHOOL_PERIODS sp \n\t\tWHERE \n\t\t\tsp.PERIOD_ID = cp.PERIOD_ID\n\t\t\tAND cp.TEACHER_ID=s.STAFF_ID AND cp.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', UserMP()) . ")\n\t\t\tAND cp.SYEAR='" . UserSyear() . "' AND cp.SCHOOL_ID='" . UserSchool() . "' AND s.PROFILE='teacher'\n\t\t\t" . ($_REQUEST['period'] ? " AND cp.PERIOD_ID='{$_REQUEST['period']}'" : '') . "\n\t\t\tAND NOT EXISTS (SELECT '' FROM ELIGIBILITY_COMPLETED ac WHERE ac.STAFF_ID=cp.TEACHER_ID AND ac.PERIOD_ID = sp.PERIOD_ID AND ac.SCHOOL_DATE BETWEEN '" . date('Y-m-d', $start_time) . "' AND '" . date('Y-m-d', $start_time + 60 * 60 * 24 * 7) . "')";
$RET = DBGet(DBQuery($sql), array(), array('STAFF_ID', 'PERIOD_ID'));
$i = 0;
if (count($RET)) {
foreach ($RET as $staff_id => $periods) {
$i++;
$staff_RET[$i]['FULL_NAME'] = $periods[key($periods)][1]['FULL_NAME'];
foreach ($periods as $period_id => $period) {
$staff_RET[$i][$period_id] = '<IMG SRC=assets/x.gif>';
}
}
}
$columns = array('FULL_NAME' => 'Teacher');
if (!$_REQUEST['period']) {
foreach ($periods_RET as $period) {
$columns[$period['PERIOD_ID']] = $period['TITLE'];
$val1 .= '<table width="95%" cellpadding="2" cellspacing="2" border="0" align="center">';
$val1 .= '<tr><td valign=top > <strong>Title</strong></td><td valign=top>:</td><td valign=top >' . $assignments_RET[1]['TITLE'] . '</td>';
$val1 .= '<td valign=top > <strong>Description</strong></td><td valign=top >:</td><td valign=top width=55%>' . $assignments_RET[1]['DESCRIPTION'] . '</td></tr>';
$val1 .= '<tr><td valign=top > <strong>Assignement Type</strong></td><td valign=top >:</td><td valign=top>' . $assignments_RET[1]['ASSIGNMENT_TYPE'] . '</td>';
$val1 .= '<td valign=top> <strong>Points</strong></td><td valign=top>:</td><td valign=top>' . $assignments_RET[1]['POINTS'] . '</td></tr>';
$val1 .= '<tr><td valign=top>';
$val1 .= '<strong>Assigned Date</strong>';
$val1 .= '</td><td valign=top>:</td><td valign=top>' . $assignments_RET[1]['ASSIGNED_DATE'] . '</td>';
$val1 .= '<td valign=top> <strong>Due Date</strong></td><td valign=top>:</td><td valign=top>' . $assignments_RET[1]['DUE_DATE'] . '</td></tr>';
# $val1 .= '</table></fieldset></tr></td></table>';
$val1 .= '</table>';
$val1 .= '</div>';
#$val1 .= PopTable('footer');
}
if ($_REQUEST['id'] == 'all') {
$courses_RET = DBGet(DBQuery("SELECT c.TITLE AS COURSE_TITLE,cp.TITLE,cp.COURSE_PERIOD_ID,cp.COURSE_ID,cp.TEACHER_ID AS STAFF_ID FROM SCHEDULE s,COURSE_PERIODS cp,COURSES c WHERE s.SYEAR='" . UserSyear() . "' AND cp.COURSE_PERIOD_ID=s.COURSE_PERIOD_ID AND s.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', UserMP()) . ") AND ('" . DBDate() . "' BETWEEN s.START_DATE AND s.END_DATE OR '" . DBDate() . "'>=s.START_DATE AND s.END_DATE IS NULL) AND s.STUDENT_ID='" . UserStudentID() . "' AND cp.GRADE_SCALE_ID IS NOT NULL" . (User('PROFILE') == 'teacher' ? ' AND cp.TEACHER_ID=\'' . User('STAFF_ID') . '\'' : '') . " AND c.COURSE_ID=cp.COURSE_ID ORDER BY cp.COURSE_ID"));
DrawHeader('All Courses', '');
DrawHeader($val1);
} else {
$courses_RET = DBGet(DBQuery("SELECT c.TITLE AS COURSE_TITLE,cp.TITLE,cp.COURSE_PERIOD_ID,cp.COURSE_ID,cp.TEACHER_ID AS STAFF_ID FROM COURSE_PERIODS cp,COURSES c WHERE cp.COURSE_PERIOD_ID='" . clean_param($_REQUEST[id], PARAM_INT) . "' AND c.COURSE_ID=cp.COURSE_ID"));
DrawHeaderhome($val1);
DrawHeader('<br><B>' . $courses_RET[1]['COURSE_TITLE'] . '</B> - ' . substr($courses_RET[1]['TITLE'], strrpos(str_replace(' - ', ' ^ ', $courses_RET[1]['TITLE']), '^') + 2), "<A HREF=Modules.php?modname={$_REQUEST['modname']}>Back to Totals</A>");
}
//echo '<pre>'; var_dump($courses_RET); echo '</pre>';
foreach ($courses_RET as $course) {
$staff_id = $course['STAFF_ID'];
if (!$programconfig[$staff_id]) {
$config_RET = DBGet(DBQuery("SELECT TITLE,VALUE FROM PROGRAM_USER_CONFIG WHERE USER_ID='{$staff_id}' AND PROGRAM='Gradebook'"), array(), array('TITLE'));
if (count($config_RET)) {
foreach ($config_RET as $title => $value) {
$programconfig[$staff_id][$title] = $value[1]['VALUE'];
$category_select .= "<OPTION value={$category['ID']}" . ($_REQUEST['table'] == $category['ID'] ? ' SELECTED' : '') . ">" . $category['TITLE'] . "</OPTION>";
}
$category_select .= "</SELECT>";
$QI = DBQuery("SELECT sp.PERIOD_ID,sp.TITLE FROM SCHOOL_PERIODS sp WHERE sp.SCHOOL_ID='" . UserSchool() . "' AND sp.SYEAR='" . UserSyear() . "' AND EXISTS (SELECT '' FROM COURSE_PERIODS WHERE SYEAR=sp.SYEAR AND PERIOD_ID=sp.PERIOD_ID AND position(',{$_REQUEST['table']},' IN DOES_ATTENDANCE)>0) ORDER BY sp.SORT_ORDER");
$periods_RET = DBGet($QI, array(), array('PERIOD_ID'));
$period_select = "<SELECT name=period onChange='this.form.submit();'><OPTION value=''>" > _('All') . "</OPTION>";
foreach ($periods_RET as $id => $period) {
$period_select .= "<OPTION value={$id}" . ($_REQUEST['period'] == $id ? ' SELECTED' : '') . ">" . $period[1]['TITLE'] . "</OPTION>";
}
$period_select .= "</SELECT>";
echo "<FORM action=Modules.php?modname={$_REQUEST['modname']} method=POST>";
DrawHeader(PrepareDate($date, '_date', false, array('submit' => true)) . ' - ' . $period_select, $category_select);
echo '</FORM>';
$daysWeek = _('SuMoTuWeThFrSa');
/// Days of the week
$sql = "SELECT s.STAFF_ID,s.LAST_NAME||', '||s.FIRST_NAME AS FULL_NAME,sp.TITLE,cp.PERIOD_ID,cp.TITLE AS COURSE_TITLE,\n\t\t(SELECT 'Y' FROM ATTENDANCE_COMPLETED ac WHERE ac.STAFF_ID=cp.TEACHER_ID AND ac.SCHOOL_DATE=acc.SCHOOL_DATE AND ac.PERIOD_ID=sp.PERIOD_ID AND TABLE_NAME='{$_REQUEST['table']}') AS COMPLETED\n\t\tFROM STAFF s,COURSE_PERIODS cp,SCHOOL_PERIODS sp,ATTENDANCE_CALENDAR acc\n\t\tWHERE\n\t\t\tsp.PERIOD_ID = cp.PERIOD_ID AND position(',{$_REQUEST['table']},' IN cp.DOES_ATTENDANCE)>0\n\t\t\tAND cp.TEACHER_ID=s.STAFF_ID AND cp.MARKING_PERIOD_ID IN (" . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ")\n\t\t\tAND cp.SYEAR='" . UserSyear() . "' AND cp.SCHOOL_ID='" . UserSchool() . "' AND s.PROFILE='teacher'\n\t\t\t" . ($_REQUEST['period'] ? " AND cp.PERIOD_ID='{$_REQUEST['period']}'" : '') . " AND acc.CALENDAR_ID=cp.CALENDAR_ID AND acc.SCHOOL_DATE='{$date}'\n\t\t\tAND acc.SYEAR='" . UserSyear() . "' AND (acc.MINUTES IS NOT NULL AND acc.MINUTES>0)\n\t\t\tAND (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\tOR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK)\n\t\tORDER BY FULL_NAME";
$RET = DBGet(DBQuery($sql), array(), array('STAFF_ID'));
if (!$_REQUEST['period']) {
foreach ($RET as $staff_id => $periods) {
$i++;
$staff_RET[$i]['FULL_NAME'] = $periods[1]['FULL_NAME'];
foreach ($periods as $period) {
if (!$_REQUEST['_CENTRE_PDF']) {
$staff_RET[$i][$period['PERIOD_ID']] .= button($period['COMPLETED'] == 'Y' ? 'check' : 'x', '', '# onMouseOver=\'stm(["Course Title","' . $period['COURSE_TITLE'] . '"],["white","#006699","","","",,"black","#e8e8ff","","","",,,,2,"#006699",2,,,,,"",,,,]);\' onMouseOut=\'htm()\'') . ' ';
} else {
$staff_RET[$i][$period['PERIOD_ID']] = ($period['COMPLETED'] == 'Y' ? _('Yes') : _('No')) . " ";
}
}
}
$columns = array('FULL_NAME' => _('Teacher'));
foreach ($periods_RET as $id => $period) {
function VerifySchedule(&$schedule)
{
$conflicts = array();
$ij = count($schedule);
for ($i = 1; $i < $ij; $i++) {
for ($j = $i + 1; $j <= $ij; $j++) {
if (!$conflicts[$i] || !$conflicts[$j]) {
if (strpos(GetAllMP(GetMPTable(GetMP($schedule[$i]['MARKING_PERIOD_ID'], 'TABLE')), $schedule[$i]['MARKING_PERIOD_ID']), "'" . $schedule[$j]['MARKING_PERIOD_ID'] . "'") !== false && (!$schedule[$i]['END_EPOCH'] || $schedule[$j]['START_EPOCH'] <= $schedule[$i]['END_EPOCH']) && (!$schedule[$j]['END_EPOCH'] || $schedule[$i]['START_EPOCH'] <= $schedule[$j]['END_EPOCH'])) {
if ($schedule[$i]['COURSE_ID'] == $schedule[$j]['COURSE_ID']) {
//&& $schedule[$i]['COURSE_WEIGHT']==$schedule[$j]['COURSE_WEIGHT'])
$conflicts[$i] = $conflicts[$j] = true;
} else {
if ($schedule[$i]['PERIOD_ID'] == $schedule[$j]['PERIOD_ID']) {
if (strlen($schedule[$i]['DAYS']) + strlen($schedule[$j]['DAYS']) > 7) {
$conflicts[$i] = $conflicts[$j] = true;
} else {
foreach (veriry_str_split($schedule[$i]['DAYS']) as $k) {
if (strpos($schedule[$j]['DAYS'], $k) !== false) {
$conflicts[$i] = $conflicts[$j] = true;
break;
}
}
}
}
}
}
}
}
}
foreach ($conflicts as $i => $true) {
$schedule[$i]['TITLE'] = '<FONT color=red>' . $schedule[$i]['TITLE'] . '</FONT>';
}
}
}
if (clean_param($_REQUEST['modfunc'], PARAM_ALPHAMOD) == 'gradebook') {
if ($_REQUEST['mp']) {
$config_RET = DBGet(DBQuery('SELECT TITLE,VALUE FROM program_user_config WHERE USER_ID=\'' . User('STAFF_ID') . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' AND PROGRAM=\'Gradebook\''), array(), array('TITLE'));
if (count($config_RET)) {
foreach ($config_RET as $title => $value) {
$programconfig[User('STAFF_ID')][$title] = $value[1]['VALUE'];
}
} else {
$programconfig[User('STAFF_ID')] = true;
}
$_openSIS['_makeLetterGrade']['courses'][$course_period_id] = DBGet(DBQuery('SELECT DOES_BREAKOFF,GRADE_SCALE_ID FROM course_periods WHERE COURSE_PERIOD_ID=\'' . $course_period_id . '\''));
$_SESSION['ROUNDING'] = $programconfig[User('STAFF_ID')]['ROUNDING'];
include '_makeLetterGrade.fnc.php';
if (false && GetMP($_REQUEST['mp'], 'TABLE') == 'school_semesters') {
$points_RET = DBGet(DBQuery('SELECT STUDENT_ID,MARKING_PERIOD_ID FROM student_report_card_grades WHERE COURSE_PERIOD_ID=\'' . $course_period_id . '\' AND MARKING_PERIOD_ID IN (' . GetAllMP('SEM', $_REQUEST['mp']) . ",'E" . GetParentMP('SEM', UserMP()) . '\')'), array(), array('STUDENT_ID'));
}
if (GetMP($_REQUEST['mp'], 'TABLE') == 'school_quarters' || GetMP($_REQUEST['mp'], 'TABLE') == 'school_progress_periods') {
// the 'populate the form' approach does not require that we get precisely the right students because nothing is modified here
// so we don't need to filter on enrollment dates - in fact, for include_inactive we want 'em all anyway
if ($programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y') {
$points_RET = DBGet(DBQuery('SELECT DISTINCT s.STUDENT_ID,gt.ASSIGNMENT_TYPE_ID, gt.ASSIGNMENT_TYPE_ID,sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ') AS PARTIAL_POINTS,
sum(' . db_case(array('gg.POINTS', '\'-1\' OR gg.POINTS IS NULL OR (ga.due_date < (select DISTINCT ssm.start_date from student_enrollment ssm where ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=' . UserSchool() . ' AND (ssm.START_DATE IS NOT NULL AND (CURRENT_DATE<=ssm.END_DATE OR CURRENT_DATE>=ssm.END_DATE OR ssm.END_DATE IS NULL)) order by ssm.start_date desc limit 1
) ) ', "'0'", 'ga.POINTS')) . ') AS PARTIAL_TOTAL,
gt.FINAL_GRADE_PERCENT FROM students s JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID=\'' . $course_period_id . '\') JOIN gradebook_assignments ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID=\'' . $course_id . '\' AND ga.STAFF_ID=\'' . User('STAFF_ID') . '\') AND ga.MARKING_PERIOD_ID=\'' . UserMP() . '\') LEFT OUTER JOIN gradebook_grades gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID)
,gradebook_assignment_types gt WHERE gt.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND gt.COURSE_ID=\'' . $course_id . '\' AND ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE,gt.ASSIGNMENT_TYPE_ID,gt.FINAL_GRADE_PERCENT'), array(), array('STUDENT_ID'));
} else {
$points_RET = DBGet(DBQuery('SELECT DISTINCT s.STUDENT_ID,\'-1\' AS ASSIGNMENT_TYPE_ID,sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ') AS PARTIAL_POINTS,
sum(' . db_case(array('gg.POINTS', '\'-1\' OR gg.POINTS IS NULL OR (ga.due_date < (select DISTINCT ssm.start_date from student_enrollment ssm where ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=' . UserSchool() . ' AND (ssm.START_DATE IS NOT NULL AND (CURRENT_DATE<=ssm.END_DATE OR CURRENT_DATE>=ssm.END_DATE OR ssm.END_DATE IS NULL)) order by ssm.start_date desc limit 1
) ) ', "'0'", 'ga.POINTS')) . ') AS PARTIAL_TOTAL,
cpv.PERIOD_ID,cp.MARKING_PERIOD_ID as COURSE_MARKING_PERIOD_ID,cp.MP,sp.SORT_ORDER,
c.TITLE ,cp.COURSE_PERIOD_ID AS PERIOD_PULLDOWN,
s.STUDENT_ID,r.TITLE AS ROOM,cpv.DAYS,SCHEDULER_LOCK
FROM schedule s,courses c,course_periods cp,school_periods sp,course_period_var cpv,rooms r
WHERE
s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID
AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID
AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID
AND r.ROOM_ID=cpv.ROOM_ID
AND s.SCHOOL_ID = sp.SCHOOL_ID AND s.SYEAR = c.SYEAR AND sp.PERIOD_ID = cpv.PERIOD_ID
AND POSITION(\'' . $day . '\' IN cpv.days)>0
AND sp.PERIOD_ID=\'' . $_REQUEST[period] . '\'
AND s.STUDENT_ID=\'' . UserStudentID() . '\'
AND s.SYEAR=\'' . UserSyear() . '\' AND s.SCHOOL_ID = \'' . UserSchool() . '\' AND (cpv.COURSE_PERIOD_DATE=\'' . date('Y-m-d', strtotime($date)) . '\' OR cpv.COURSE_PERIOD_DATE IS NULL)
AND (\'' . date('Y-m-d', strtotime($date)) . '\' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND s.START_DATE<=\'' . date('Y-m-d', strtotime($date)) . '\'))
AND s.MARKING_PERIOD_ID IN (' . GetAllMP(GetMPTable(GetMP($mp_id, 'TABLE')), $mp_id) . ')
ORDER BY sp.SORT_ORDER,s.MARKING_PERIOD_ID';
$QI = DBQuery($sql);
$schedule_RET = DBGet($QI, array('TIME_PERIOD' => '_makeTimePeriod', 'TITLE' => '_makeTitle', 'PERIOD_PULLDOWN' => '_makePeriodSelect', 'COURSE_MARKING_PERIOD_ID' => '_makeMPSelect'));
$columns = array('TIME_PERIOD' => 'Period', 'TITLE' => 'Course', 'PERIOD_PULLDOWN' => 'Period - Teacher', 'ROOM' => 'Room', 'DAYS' => 'Days of Week', 'COURSE_MARKING_PERIOD_ID' => 'Term');
}
if ($_REQUEST['view_mode'] != 'month_view') {
ListOutput($schedule_RET, $columns, 'Course', 'Courses', $link);
if ($_REQUEST['modfunc'] == 'detail') {
echo '<br /><div align="center"><input type="button" class=btn_medium value="Close" onclick="window.close();"></div>';
}
}
if ($schedule_RET && $_REQUEST['view_mode'] == 'day_view') {
DrawHeader("<table><tr><td> </td><td>" . ProgramLinkforExport('scheduling/PrintSchedules.php', '<img src=assets/print.png>', '&modfunc=save&st_arr[]=' . UserStudentID() . '&mp_id=' . $mp_id . '&date1=' . $date1 . '&include_inactive=' . $_REQUEST['include_inactive'] . '&_openSIS_PDF=true target=_blank') . "</td><td>" . ProgramLinkforExport('scheduling/PrintSchedules.php', 'Print Schedule', '&modfunc=save&st_arr[]=' . UserStudentID() . '&mp_id=' . $mp_id . '&include_inactive=' . $_REQUEST['include_inactive'] . '&date1=' . $date1 . '&_openSIS_PDF=true target=_blank') . "</td></tr></table>");
}
echo '</FORM>';
// $_SESSION['UserSyear'] = $DefaultSyear;
if (!$_SESSION['student_id'] && User('PROFILE') == 'student') {
$_SESSION['student_id'] = $_SESSION['STUDENT_ID'];
}
if (!$_SESSION['UserSchool']) {
if (User('PROFILE') == 'admin' && (!User('SCHOOLS') || strpos(User('SCHOOLS'), ',' . User('CURRENT_SCHOOL_ID') . ',') !== false)) {
$_SESSION['UserSchool'] = User('CURRENT_SCHOOL_ID');
} elseif (User('PROFILE') == 'student') {
$_SESSION['UserSchool'] = trim(User('SCHOOLS'), ',');
} elseif (User('PROFILE') == 'teacher') {
$mp = GetAllMP('QTR', UserMP());
if (!isset($mp)) {
$mp = GetAllMP('SEM', UserMP());
}
if (!isset($mp)) {
$mp = GetAllMP('FY', UserMP());
}
$QI = DBQuery("SELECT cp.SCHOOL_ID FROM course_periods cp, school_periods sp,courses c WHERE c.COURSE_ID=cp.COURSE_ID AND cp.PERIOD_ID=sp.PERIOD_ID AND cp.SYEAR='" . UserSyear() . "' AND cp.TEACHER_ID='" . User('STAFF_ID') . "''" . (UserMP() ? ' AND cp.MARKING_PERIOD_ID IN (' . $mp . ')' : '') . "' ORDER BY sp.SORT_ORDER LIMIT 1");
$RET = DBGet($QI);
$_SESSION['UserSchool'] = $RET[1]['SCHOOL_ID'];
}
}
/*if((!$_SESSION['UserMP'] || ($_REQUEST['school'] && $_REQUEST['school'] != $old_school) || ($_REQUEST['syear'] && $_REQUEST['syear'] != $old_syear) || ($_REQUEST['period'] && $_REQUEST['period'] != $old_period)) && User('PROFILE')!='parent'){
$_SESSION['UserMP'] = GetCurrentMP('QTR',DBDate());
}*/
//if(($_REQUEST['school'] && $_REQUEST['school'] != $old_school) || ($_REQUEST['syear'] && $_REQUEST['syear'] != $old_syear))
if (optional_param('school', '', PARAM_SPCL) && optional_param('school', '', PARAM_SPCL) != $old_school || optional_param('syear', '', PARAM_SPCL) && optional_param('syear', '', PARAM_SPCL) != $old_syear) {
unset($_SESSION['UserPeriod']);
unset($_SESSION['UserCoursePeriod']);
}
//if($_REQUEST['student_id']=='new')
function GetStuListAttn(&$extra)
{
global $contacts_RET, $view_other_RET, $_openSIS;
if ((!$extra['SELECT_ONLY'] || strpos($extra['SELECT_ONLY'], 'GRADE_ID') !== false) && !$extra['functions']['GRADE_ID']) {
$functions = array('GRADE_ID' => 'GetGrade');
} else {
$functions = array();
}
if ($extra['functions']) {
$functions += $extra['functions'];
}
if (!$extra['DATE']) {
$queryMP = UserMP();
$extra['DATE'] = DBDate();
} else {
$queryMP = UserMP();
}
if ($_REQUEST['expanded_view'] == 'true') {
if (!$extra['columns_after']) {
$extra['columns_after'] = array();
}
#############################################################################################
//Commented as it crashing for Linux due to Blank Database tables
#############################################################################################
$view_address_RET = DBGet(DBQuery('SELECT VALUE FROM program_user_config WHERE PROGRAM=\'StudentFieldsView\' AND TITLE=\'ADDRESS\' AND USER_ID=\'' . User('STAFF_ID') . '\''));
$view_address_RET = $view_address_RET[1]['VALUE'];
$view_other_RET = DBGet(DBQuery('SELECT TITLE,VALUE FROM program_user_config WHERE PROGRAM=\'StudentFieldsView\' AND TITLE IN (\'CONTACT_INFO\',\'HOME_PHONE\',\'GUARDIANS\',\'ALL_CONTACTS\') AND USER_ID=\'' . User('STAFF_ID') . '\''), array(), array('TITLE'));
if (!count($view_fields_RET) && !isset($view_address_RET) && !isset($view_other_RET['CONTACT_INFO'])) {
$extra['columns_after'] = array('CONTACT_INFO' => '<IMG SRC=assets/down_phone_button.gif border=0>', 'gender' => 'Gender', 'ethnicity' => 'Ethnicity', 'ADDRESS' => 'Mailing Address', 'CITY' => 'City', 'STATE' => 'State', 'ZIPCODE' => 'Zipcode') + $extra['columns_after'];
$select = ',s.STUDENT_ID AS CONTACT_INFO,s.GENDER,s.ETHNICITY,a.STREET_ADDRESS_1 as ADDRESS,a.CITY,a.STATE,a.ZIPCODE';
$extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID AND a.TYPE=\'Mail\') ' . $extra['FROM'];
$functions['CONTACT_INFO'] = 'makeContactInfo';
// if gender is converted to codeds type
//$functions['CUSTOM_200000000'] = 'DeCodeds';
$extra['singular'] = 'Student Address';
$extra['plural'] = 'Student Addresses';
$extra2['NoSearchTerms'] = true;
$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.STAFF_ID AS PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.RELATIONSHIP as STUDENT_RELATION,p.TITLE,s.PHONE,a.ID AS ADDRESS_ID ';
$extra2['FROM'] .= ',student_address a LEFT OUTER JOIN students_join_people sjp ON (a.STUDENT_ID=sjp.STUDENT_ID AND (p.CUSTODY=\'Y\' OR sjp.IS_EMERGENCY=\'Y\')) LEFT OUTER JOIN people p ON (p.STAFF_ID=sjp.PERSON_ID) ';
$extra2['WHERE'] .= ' AND a.STUDENT_ID=sjp.STUDENT_ID AND sjp.STUDENT_ID=ssm.STUDENT_ID ';
$extra2['ORDER_BY'] .= 'COALESCE(p.CUSTODY,\'N\') DESC';
$extra2['group'] = array('STUDENT_ID', 'PERSON_ID');
// EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF
if (!$_REQUEST['_openSIS_PDF']) {
$expanded_view = $_REQUEST['expanded_view'];
$_REQUEST['expanded_view'] = false;
$addr = $_REQUEST['addr'];
unset($_REQUEST['addr']);
$contacts_RET = GetStuList($extra2);
$_REQUEST['expanded_view'] = $expanded_view;
$_REQUEST['addr'] = $addr;
} else {
unset($extra2['columns_after']['CONTACT_INFO']);
}
} else {
if ($view_other_RET['CONTACT_INFO'][1]['VALUE'] == 'Y' && !$_REQUEST['_openSIS_PDF']) {
$select .= ',NULL AS CONTACT_INFO ';
$extra['columns_after']['CONTACT_INFO'] = '<IMG SRC=assets/down_phone_button.gif border=0>';
$functions['CONTACT_INFO'] = 'makeContactInfo';
$extra2 = $extra;
$extra2['NoSearchTerms'] = true;
$extra2['SELECT'] = '';
$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.STAFF_ID AS PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.RELATIONSHIP AS STUDENT_RELATION,p.TITLE,s.PHONE,a.ID AS ADDRESS_ID,COALESCE(p.CUSTODY,\'N\') ';
$extra2['FROM'] .= ',student_address a LEFT OUTER JOIN students_join_people sjp ON (a.STUDENT_ID=sjp.STUDENT_ID AND (p.CUSTODY=\'Y\' OR sjp.IS_EMERGENCY=\'Y\')) LEFT OUTER JOIN people p ON (p.STAFF_ID=sjp.PERSON_ID) ';
$extra2['WHERE'] .= ' AND a.STUDENT_ID=sjp.STUDENT_ID AND sjp.STUDENT_ID=ssm.STUDENT_ID ';
$extra2['ORDER_BY'] .= 'COALESCE(p.CUSTODY,\'N\') DESC';
$extra2['group'] = array('STUDENT_ID', 'PERSON_ID');
$extra2['functions'] = array();
$extra2['link'] = array();
// EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF
$expanded_view = $_REQUEST['expanded_view'];
$_REQUEST['expanded_view'] = false;
$addr = $_REQUEST['addr'];
unset($_REQUEST['addr']);
$contacts_RET = GetStuList($extra2);
$_REQUEST['expanded_view'] = $expanded_view;
$_REQUEST['addr'] = $addr;
}
foreach ($view_fields_RET as $field) {
$extra['columns_after']['CUSTOM_' . $field['ID']] = $field['TITLE'];
if ($field['TYPE'] == 'date') {
$functions['CUSTOM_' . $field['ID']] = 'ProperDate';
} elseif ($field['TYPE'] == 'numeric') {
$functions['CUSTOM_' . $field['ID']] = 'removeDot00';
} elseif ($field['TYPE'] == 'codeds') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
}
$select .= ',s.CUSTOM_' . $field['ID'];
}
if ($view_address_RET) {
if ($view_address_RET == 'RESIDENCE') {
$extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID AND a.TYPE=\'Home Address\') ' . $extra['FROM'];
} elseif ($view_address_RET == 'MAILING') {
$extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID AND a.TYPE=\'Mail\') ' . $extra['FROM'];
} elseif ($view_address_RET == 'BUS_PICKUP') {
$extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (a.STUDENT_ID=a.STUDENT_ID AND a.BUS_PICKUP=\'Y\') ' . $extra['FROM'];
} else {
$extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (a.STUDENT_ID=a.STUDENT_ID AND a.BUS_DROPOFF=\'Y\') ' . $extra['FROM'];
}
$extra['columns_after'] += array('ADDRESS' => ucwords(strtolower(str_replace('_', ' ', $view_address_RET))) . ' Address', 'CITY' => 'City', 'STATE' => 'State', 'ZIPCODE' => 'Zipcode');
$select .= ',a.ID AS ADDRESS_ID,a.STREET_ADDRESS_1 as ADDRESS,a.CITY,a.STATE,a.ZIPCODE,s.PHONE,ssm.STUDENT_ID AS PARENTS';
$extra['singular'] = 'Student Address';
$extra['plural'] = 'Student Addresses';
if ($view_other_RET['HOME_PHONE'][1]['VALUE'] == 'Y') {
$functions['PHONE'] = 'makePhone';
$extra['columns_after']['PHONE'] = 'Home Phone';
}
if ($view_other_RET['GUARDIANS'][1]['VALUE'] == 'Y' || $view_other_RET['ALL_CONTACTS'][1]['VALUE'] == 'Y') {
$functions['PARENTS'] = 'makeParents';
if ($view_other_RET['ALL_CONTACTS'][1]['VALUE'] == 'Y') {
$extra['columns_after']['PARENTS'] = 'Contacts';
} else {
$extra['columns_after']['PARENTS'] = 'Guardians';
}
}
} elseif ($_REQUEST['addr'] || $extra['addr']) {
$extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID) ' . $extra['FROM'];
$distinct = 'DISTINCT ';
}
}
$extra['SELECT'] .= $select;
} elseif ($_REQUEST['addr'] || $extra['addr']) {
$extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID) ' . $extra['FROM'];
$distinct = 'DISTINCT ';
}
switch (User('PROFILE')) {
case 'admin':
$sql = 'SELECT ';
if ($extra['SELECT_ONLY']) {
$sql .= $extra['SELECT_ONLY'];
} else {
if (Preferences('NAME') == 'Common') {
$sql .= 'CONCAT(s.LAST_NAME,\', \',coalesce(s.COMMON_NAME,s.FIRST_NAME)) AS FULL_NAME,';
} else {
$sql .= 'CONCAT(s.LAST_NAME,\', \',s.FIRST_NAME,\' \',COALESCE(s.MIDDLE_NAME,\' \')) AS FULL_NAME,';
}
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= ',' . db_case(array('(ssm.SYEAR=\'' . UserSyear() . '\' AND (ssm.START_DATE IS NOT NULL AND (\'' . date('Y-m-d', strtotime($extra['DATE'])) . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL)))', 'true', "'<FONT color=green>Active</FONT>'", "'<FONT color=red>Inactive</FONT>'")) . ' AS ACTIVE ';
}
}
$sql .= ' FROM students s,student_enrollment ssm ' . $extra['FROM'] . ' WHERE ssm.STUDENT_ID=s.STUDENT_ID ';
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= ' AND ssm.ID=(SELECT ID FROM student_enrollment WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<=\'' . UserSyear() . '\' ORDER BY START_DATE DESC LIMIT 1)';
} else {
$sql .= ' AND ssm.SYEAR=\'' . UserSyear() . '\' AND (ssm.START_DATE IS NOT NULL AND (\'' . date('Y-m-d', strtotime($extra['DATE'])) . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL)) ';
}
if (UserSchool() && $_REQUEST['_search_all_schools'] != 'Y') {
$sql .= ' AND ssm.SCHOOL_ID=\'' . UserSchool() . '\'';
} else {
//
$sql .= ' AND ssm.SCHOOL_ID IN (' . GetUserSchools(UserID(), true) . ') ';
$extra['columns_after']['LIST_SCHOOL_ID'] = 'School';
$functions['LIST_SCHOOL_ID'] = 'GetSchool';
}
if (!$extra['SELECT_ONLY'] && $_REQUEST['include_inactive'] == 'Y') {
$extra['columns_after']['ACTIVE'] = 'Status';
}
break;
case 'teacher':
$sql = 'SELECT ';
if ($extra['SELECT_ONLY']) {
$sql .= $extra['SELECT_ONLY'];
} else {
if (Preferences('NAME') == 'Common') {
$sql .= 'CONCAT(s.LAST_NAME,\', \',coalesce(s.COMMON_NAME,s.FIRST_NAME)) AS FULL_NAME,';
} else {
$sql .= 'CONCAT(s.LAST_NAME,\', \',s.FIRST_NAME,\' \',COALESCE(s.MIDDLE_NAME,\' \')) AS FULL_NAME,';
}
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= ',' . db_case(array('(ssm.START_DATE IS NOT NULL AND (\'' . $extra['DATE'] . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL))', 'true', "'<FONT color=green>Active</FONT>'", "'<FONT color=red>Inactive</FONT>'")) . ' AS ACTIVE';
$sql .= ',' . db_case(array('(\'' . $extra['DATE'] . '\'>=ss.START_DATE AND (\'' . $extra['DATE'] . '\'<=ss.END_DATE OR ss.END_DATE IS NULL))', 'true', "'<FONT color=green>Active</FONT>'", "'<FONT color=red>Inactive</FONT>'")) . ' AS ACTIVE_SCHEDULE';
}
}
//
$sql .= ' FROM students s,course_periods cp,schedule ss,student_enrollment ssm,course_period_var cpv ' . $extra['FROM'] . ' WHERE ssm.STUDENT_ID=s.STUDENT_ID AND cpv.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND cpv.ID="' . $extra['ID'] . '" AND ssm.STUDENT_ID=ss.STUDENT_ID AND ssm.SCHOOL_ID=\'' . UserSchool() . '\' AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SYEAR=cp.SYEAR AND ssm.SYEAR=ss.SYEAR AND ' . db_case(array(User('STAFF_ID'), 'cp.teacher_id', ' cp.teacher_id=' . User('STAFF_ID'), 'cp.secondary_teacher_id', ' cp.secondary_teacher_id=' . User('STAFF_ID'), 'cp.course_period_id IN(SELECT course_period_id from teacher_reassignment tra WHERE cp.course_period_id=tra.course_period_id AND tra.pre_teacher_id=' . User('STAFF_ID') . ')')) . ' AND cp.COURSE_PERIOD_ID=\'' . UserCoursePeriod() . '\' AND cp.COURSE_ID=ss.COURSE_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID';
if ($extra['cpvdate'] != '') {
$sql .= $extra['cpvdate'];
}
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= ' AND ssm.ID=(SELECT ID FROM student_enrollment WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR=ssm.SYEAR ORDER BY START_DATE DESC LIMIT 1)';
$sql .= ' AND ss.START_DATE=(SELECT START_DATE FROM schedule WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR=ssm.SYEAR AND MARKING_PERIOD_ID IN (' . GetAllMP('', $queryMP) . ') AND COURSE_ID=cp.COURSE_ID AND COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID ORDER BY START_DATE DESC LIMIT 1)';
} else {
$sql .= ' AND (ssm.START_DATE IS NOT NULL AND \'' . $extra['DATE'] . '\'>=ssm.START_DATE AND (\'' . $extra['DATE'] . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL))';
$sql .= ' AND (\'' . $extra['DATE'] . '\'>=ss.START_DATE AND (\'' . $extra['DATE'] . '\'<=ss.END_DATE OR ss.END_DATE IS NULL))';
}
if (!$extra['SELECT_ONLY'] && $_REQUEST['include_inactive'] == 'Y') {
$extra['columns_after']['ACTIVE'] = 'School Status';
$extra['columns_after']['ACTIVE_SCHEDULE'] = 'Course Status';
}
break;
case 'parent':
case 'student':
$sql = 'SELECT ';
if ($extra['SELECT_ONLY']) {
$sql .= $extra['SELECT_ONLY'];
} else {
if (Preferences('NAME') == 'Common') {
$sql .= 'CONCAT(s.LAST_NAME,\', \',coalesce(s.COMMON_NAME,s.FIRST_NAME)) AS FULL_NAME,';
} else {
$sql .= 'CONCAT(s.LAST_NAME,\', \',s.FIRST_NAME,\' \',COALESCE(s.MIDDLE_NAME,\' \')) AS FULL_NAME,';
}
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
}
$sql .= ' FROM students s,student_enrollment ssm ' . $extra['FROM'] . '
WHERE ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=\'' . UserSchool() . '\' AND (\'' . DBDate() . '\' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL AND \'' . DBDate() . '\'>ssm.START_DATE)) AND ssm.STUDENT_ID' . ($extra['ASSOCIATED'] ? ' IN (SELECT STUDENT_ID FROM students_join_users WHERE STAFF_ID=\'' . $extra['ASSOCIATED'] . '\')' : '=\'' . UserStudentID() . '\'');
break;
default:
exit('Error');
}
$sql = appendSQL($sql, $extra);
$sql .= $extra['WHERE'] . ' ';
$sql .= CustomFields('where');
if ($extra['GROUP']) {
$sql .= ' GROUP BY ' . $extra['GROUP'];
}
if (!$extra['ORDER_BY'] && !$extra['SELECT_ONLY']) {
if (Preferences('SORT') == 'Grade') {
$sql .= ' ORDER BY (SELECT SORT_ORDER FROM school_gradelevels WHERE ID=ssm.GRADE_ID),FULL_NAME';
} else {
$sql .= ' ORDER BY FULL_NAME';
}
$sql .= $extra['ORDER'];
} elseif ($extra['ORDER_BY']) {
$sql .= ' ORDER BY ' . $extra['ORDER_BY'];
}
if ($extra['DEBUG'] === true) {
echo '<!--' . $sql . '-->';
}
return DBGet(DBQuery($sql), $functions, $extra['group']);
}
# 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 '../../Redirect_modules.php';
$max_cols = 3;
$max_rows = 10;
if (clean_param($_REQUEST['modfunc'], PARAM_ALPHAMOD) == 'save') {
if (count($_REQUEST['st_arr'])) {
$st_list = '\'' . implode('\',\'', $_REQUEST['st_arr']) . '\'';
$extra['WHERE'] = ' AND s.STUDENT_ID IN (' . $st_list . ')';
$qtr = GetAllMP('QTR', GetCurrentMP('QTR', DBDate(), false));
$extra['SELECT'] .= ',coalesce(s.COMMON_NAME,s.FIRST_NAME) AS NICK_NAME';
if (User('PROFILE') == 'admin') {
if ($_REQUEST['w_course_period_id_which'] == 'course_period' && $_REQUEST['w_course_period_id']) {
if ($_REQUEST['teacher']) {
$extra['SELECT'] .= ',(SELECT CONCAT(st.FIRST_NAME,\'' . ' ' . '\',st.LAST_NAME) FROM staff st,course_periods cp WHERE st.STAFF_ID=cp.TEACHER_ID AND cp.COURSE_PERIOD_ID=\'' . $_REQUEST[w_course_period_id] . '\') AS TEACHER';
}
if ($_REQUEST['room']) {
$extra['SELECT'] .= ',(SELECT cp.ROOM FROM course_periods cp WHERE cp.COURSE_PERIOD_ID=\'' . $_REQUEST[w_course_period_id] . '\') AS ROOM';
}
} else {
if ($_REQUEST['teacher']) {
$extra['SELECT'] .= ',(SELECT CONCAT(st.FIRST_NAME,\'' . ' ' . '\',st.LAST_NAME) FROM staff st,course_periods cp,school_periods p,schedule ss WHERE st.STAFF_ID=cp.TEACHER_ID AND cp.PERIOD_id=p.PERIOD_ID AND p.ATTENDANCE=\'' . 'Y' . '\' AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND ss.STUDENT_ID=s.STUDENT_ID AND ss.SYEAR=\'' . UserSyear() . '\' AND ss.MARKING_PERIOD_ID IN(' . $qtr . ') AND (ss.START_DATE<=\'' . DBDate() . '\' AND (ss.END_DATE>=\'' . DBDate() . '\' OR ss.END_DATE IS NULL)) ORDER BY p.SORT_ORDER LIMIT 1) AS TEACHER';
}
if ($_REQUEST['room']) {
$extra['SELECT'] .= ',(SELECT cp.ROOM FROM course_periods cp,school_periods p,schedule ss WHERE cp.PERIOD_id=p.PERIOD_ID AND p.ATTENDANCE=\'' . 'Y' . '\' AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND ss.STUDENT_ID=s.STUDENT_ID AND ss.SYEAR=\'' . UserSyear() . '\' AND ss.MARKING_PERIOD_ID IN(' . $qtr . ') AND (ss.START_DATE<=\'' . DBDate() . '\' AND (ss.END_DATE>=\'' . DBDate() . '\' OR ss.END_DATE IS NULL)) ORDER BY p.SORT_ORDER LIMIT 1) AS ROOM';
if (!$current_mp) {
$current_mp = GetCurrentMP('SEM', $date);
$MP_TYPE = 'SEM';
}
if (!$current_mp) {
$current_mp = GetCurrentMP('FY', $date);
$MP_TYPE = 'FY';
}
$sql = 'SELECT concat(s.LAST_NAME,\',\',s.FIRST_NAME, \' \') AS FULL_NAME,sp.TITLE,cpv.PERIOD_ID,s.STAFF_ID
FROM staff s,course_periods cp,course_period_var cpv,school_periods sp,attendance_calendar acc
WHERE
sp.PERIOD_ID = cpv.PERIOD_ID
AND acc.CALENDAR_id=cp.CALENDAR_ID
AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID
AND acc.SCHOOL_DATE=\'' . date('Y-m-d', strtotime($date)) . '\'
AND cp.TEACHER_ID=s.STAFF_ID AND cp.MARKING_PERIOD_ID IN (' . GetAllMP($MP_TYPE, $current_mp) . ')
AND cp.SYEAR=\'' . UserSyear() . '\' AND cp.SCHOOL_ID=\'' . UserSchool() . '\' AND s.PROFILE=\'teacher\'
AND cpv.DOES_ATTENDANCE=\'Y\' AND instr(cpv.DAYS,\'' . $day . '\')>0' . ($p ? ' AND cpv.PERIOD_ID=\'' . $p . '\'' : '') . '
AND NOT EXISTS (SELECT \'\' FROM attendance_completed ac WHERE ac.STAFF_ID=cp.TEACHER_ID AND ac.SCHOOL_DATE=\'' . date('Y-m-d', strtotime($date)) . '\' AND ac.PERIOD_ID=sp.PERIOD_ID)
';
$RET = DBGet(DBQuery($sql), array(), array('STAFF_ID', 'PERIOD_ID'));
$i = 0;
if (count($RET)) {
foreach ($RET as $staff_id => $periods) {
$i++;
$staff_RET[$i]['FULL_NAME'] = $periods[key($periods)][1]['FULL_NAME'];
foreach ($periods as $period_id => $period) {
$staff_RET[$i][$period_id] = '<IMG SRC=assets/false.png>';
}
}
}
