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 . "')"); } }
$RET = DBGet(DBQuery("SELECT sju.STUDENT_ID, CONCAT(s.LAST_NAME,', ',s.FIRST_NAME) AS FULL_NAME,se.SCHOOL_ID FROM students s,students_join_users sju, student_enrollment se WHERE s.STUDENT_ID=sju.STUDENT_ID AND sju.STAFF_ID='" . User('STAFF_ID') . "' AND se.SYEAR=" . UserSyear() . " AND se.STUDENT_ID=sju.STUDENT_ID AND (('" . DBDate() . "' BETWEEN se.START_DATE AND se.END_DATE OR se.END_DATE IS NULL) AND '" . DBDate() . "'>=se.START_DATE)")); if (!UserStudentID()) { $_SESSION['student_id'] = $RET[1]['STUDENT_ID']; } echo "<SELECT name=student_id onChange='document.forms[0].submit();'>"; if (count($RET)) { foreach ($RET as $student) { echo "<OPTION value={$student['STUDENT_ID']}" . (UserStudentID() == $student['STUDENT_ID'] ? ' SELECTED' : '') . ">" . $student['FULL_NAME'] . "</OPTION>"; if (UserStudentID() == $student['STUDENT_ID']) { $_SESSION['UserSchool'] = $student['SCHOOL_ID']; } } } echo "</SELECT><BR>"; if (!UserMP()) { $_SESSION['UserMP'] = GetCurrentMP('QTR', DBDate()); } } if (User('PROFILE') == 'teacher') { /* //if(UserMP()) // $QI = DBQuery("SELECT DISTINCT cp.PERIOD_ID,cp.COURSE_PERIOD_ID,sp.TITLE,sp.SHORT_NAME,cp.MARKING_PERIOD_ID,cp.DAYS,cp.SCHOOL_ID,sp.SORT_ORDER,c.TITLE AS COURSE_TITLE 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')."' AND cp.MARKING_PERIOD_ID IN (".GetAllMP('QTR',UserMP()).") ORDER BY sp.SORT_ORDER "); //else $QI = DBQuery("SELECT DISTINCT cp.PERIOD_ID,cp.COURSE_PERIOD_ID,sp.TITLE,sp.SHORT_NAME,cp.MARKING_PERIOD_ID,cp.DAYS,cp.SCHOOL_ID,sp.SORT_ORDER,c.TITLE AS COURSE_TITLE 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')."' ORDER BY sp.SORT_ORDER"); $RET = DBGet($QI); // get the fy marking period id's, there should be exactly one fy marking period per school $fy_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,SCHOOL_ID FROM school_years WHERE SYEAR='".UserSyear()."'"),array(),array('SCHOOL_ID')); // only need quarters and semesters since years are suppressed and progress_periods aren't used $mp_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,TITLE,SHORT_NAME FROM school_quarters WHERE SYEAR='".UserSyear()."' UNION SELECT MARKING_PERIOD_ID,TITLE,SHORT_NAME FROM school_semesters WHERE SYEAR='".UserSyear()."'"),array(),array('MARKING_PERIOD_ID'));
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']); }
$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 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) . '\')'); } } }
$mps_RET = DBGet(DBQuery('SELECT MARKING_PERIOD_ID,SHORT_NAME FROM school_years WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' ORDER BY SORT_ORDER'), array(), array('MARKING_PERIOD_ID')); } $extra['extra_header_left'] .= '<TR><TD align=right>Marking Periods</TD><TD><TABLE><TR><TD><TABLE>'; foreach ($mps_RET as $sem => $quarters) { $extra['extra_header_left'] .= '<TR>'; foreach ($quarters as $qtr) { $pro = GetChildrenMP('PRO', $qtr['MARKING_PERIOD_ID']); if ($pro) { $pros = explode(',', str_replace("'", '', $pro)); foreach ($pros as $pro) { if (GetMP($pro, 'DOES_GRADES') == 'Y') { $extra['extra_header_left'] .= '<TD><INPUT type=checkbox name=mp_arr[] value=' . $pro . '>' . GetMP($pro, 'SHORT_NAME') . '</TD>'; } } } $extra['extra_header_left'] .= '<TD><INPUT type=checkbox name=mp_arr[] value=' . $qtr['MARKING_PERIOD_ID'] . ' ' . ($qtr['MARKING_PERIOD_ID'] == GetCurrentMP($MP_TYPE, DBDate()) ? 'checked' : '') . '>' . $qtr['SHORT_NAME'] . '</TD>'; } if (GetMP($sem, 'DOES_EXAM') == 'Y') { if (GetMP($sem, 'DOES_EXAM') == 'Y') { $extra['extra_header_left'] .= '<TD><INPUT type=checkbox name=mp_arr[] value=E' . $sem . '>' . GetMP($sem, 'SHORT_NAME') . ' Exam</TD>'; } } if (GetMP($sem, 'DOES_GRADES') == 'Y' && $sem != $quarters[1]['MARKING_PERIOD_ID']) { $extra['extra_header_left'] .= '<TD><INPUT type=checkbox name=mp_arr[] value=' . $sem . '>' . GetMP($sem, 'SHORT_NAME') . '</TD>'; } $extra['extra_header_left'] .= '</TR>'; } $extra['extra_header_left'] .= '</TABLE></TD>'; if ($sem) { $fy = GetParentMP('FY', $sem); $extra['extra_header_left'] .= '<TD><TABLE><TR>';
AND sp.PERIOD_ID=cpv.PERIOD_ID AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID AND (sp.BLOCK IS NULL AND position(substring(\'UMTWHFS\' FROM DAYOFWEEK(acc.SCHOOL_DATE) FOR 1) IN cpv.DAYS)>0 OR sp.BLOCK IS NOT NULL AND acc.BLOCK IS NOT NULL AND sp.BLOCK=acc.BLOCK) ' . ($_REQUEST['table'] == '0' ? 'AND cpv.DOES_ATTENDANCE=\'Y\'' : ''); if ($mps != '') { $course_RET = DBGET(DBQuery($sql)); } $mp_id = GetCurrentMP('QTR', $date, false); if (!$mp_id) { $mp_id = GetCurrentMP('SEM', $date, false); } if (!$mp_id) { $mp_id = GetCurrentMP('FY', $date, false); } // if running as a teacher program then openSIS[allow_edit] will already be set according to admin permissions if (!isset($_openSIS['allow_edit'])) { // allow teacher edit if selected date is in the current quarter or in the corresponding grade posting period $current_qtr_id = $mp_id; $time = strtotime(DBDate('postgres')); if (($current_qtr_id || GetMP($mp_id, 'POST_START_DATE') && $time <= strtotime(GetMP($mp_id, 'POST_END_DATE'))) && ($edit_days_before == '' || strtotime($date) <= $time + $edit_days_before * 86400) && ($edit_days_after == '' || strtotime($date) >= $time - $edit_days_after * 86400)) { $_openSIS['allow_edit'] = true; } } if ($_SESSION['PROFILE'] == 'teacher') { if ($_REQUEST['cpv_id'] != '') { $_SESSION['CpvId'] = $_REQUEST['cpv_id']; } }
} } echo "</SELECT></TD></TR></TABLE>"; ################################################################################################################### $sql = "SELECT CONCAT(s.LAST_NAME,', ',coalesce(s.COMMON_NAME,s.FIRST_NAME)) AS FULL_NAME,s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,s.ALT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ,c.TITLE AS COURSE_TITLE,p_cp.TITLE AS PERIOD_TITLE,sr.MARKING_PERIOD_ID,cpv.DAYS, CONCAT(sp.START_TIME, ' to ', sp.END_TIME) AS DURATION,r.TITLE AS ROOM FROM students s,student_enrollment ssm LEFT OUTER JOIN schedule sr ON (sr.STUDENT_ID=ssm.STUDENT_ID),courses c,course_periods p_cp,course_period_var cpv,rooms r,school_periods sp WHERE ssm.STUDENT_ID=s.STUDENT_ID AND p_cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID AND r.ROOM_ID=cpv.ROOM_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='" . UserStudentID() . "' AND s.STUDENT_ID = '" . UserStudentID() . "' AND cpv.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 cpv.PERIOD_ID=sp.PERIOD_ID AND ('" . DBDate() . "' BETWEEN sr.START_DATE AND sr.END_DATE OR sr.END_DATE IS NULL) ORDER BY FULL_NAME,sp.SORT_ORDER"; echo "<br>"; echo "<br>"; $stu_id = UserStudentID(); $RET_show[$stu_id] = DBGet(DBQuery($sql)); $date = date(Y . "-" . m . "-" . d); if (!$_REQUEST['sel_mp']) { $sel_mp = GetCurrentMP('QTR', $date); if (!$sel_mp) { $sel_mp = GetCurrentMP('SEM', $date); if (!$sel_mp) { $sel_mp = GetCurrentMP('FY', $date); } } } else { $sel_mp = $_REQUEST['sel_mp']; } $sql_mp_detail = 'SELECT title, start_date, end_date, parent_id, grandparent_id from marking_periods WHERE marking_period_id = \'' . $sel_mp . '\''; $res_mp_detail = mysql_query($sql_mp_detail); $row_mp_detail = mysql_fetch_array($res_mp_detail); $mp_string = '(s.marking_period_id=' . $sel_mp . ''; if ($row_mp_detail['parent_id'] != -1) { $mp_string .= ' or s.marking_period_id=' . $row_mp_detail['parent_id'] . ''; } if ($row_mp_detail['grandparent_id'] != -1) { $mp_string .= ' or s.marking_period_id=' . $row_mp_detail['grandparent_id'] . ''; }
} if ($_REQUEST['month_include_active_date']) { $date = $_REQUEST['day_include_active_date'] . '-' . $_REQUEST['month_include_active_date'] . '-' . $_REQUEST['year_include_active_date']; } else { $date = DBDate(); } if ($_REQUEST['fields']['PERIOD_ATTENDANCE']) { //modif Francois: multiple school periods for a course period //$extra['SELECT'] .= ',(SELECT st.FIRST_NAME||\' \'||st.LAST_NAME||\' - \'||coalesce(cp.ROOM,\' \') FROM STAFF st,SCHEDULE ss,COURSE_PERIODS cp,SCHOOL_PERIODS p WHERE ss.STUDENT_ID=ssm.STUDENT_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND cp.TEACHER_ID=st.STAFF_ID AND cp.PERIOD_ID=p.PERIOD_ID AND (\''.$date.'\' BETWEEN ss.START_DATE AND ss.END_DATE OR \''.$date.'\'>=ss.START_DATE AND ss.END_DATE IS NULL) AND ss.MARKING_PERIOD_ID IN ('.GetAllMP('QTR',GetCurrentMP('QTR',$date)).') AND p.ATTENDANCE=\'Y\') AS PERIOD_ATTENDANCE'; $extra['SELECT'] .= ',(SELECT st.FIRST_NAME||\' \'||st.LAST_NAME||\' - \'||coalesce(cp.ROOM,\' \') FROM STAFF st,SCHEDULE ss,COURSE_PERIODS cp,SCHOOL_PERIODS p,COURSE_PERIOD_SCHOOL_PERIODS cpsp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND ss.STUDENT_ID=ssm.STUDENT_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND cp.TEACHER_ID=st.STAFF_ID AND cpsp.PERIOD_ID=p.PERIOD_ID AND (\'' . $date . '\' BETWEEN ss.START_DATE AND ss.END_DATE OR \'' . $date . '\'>=ss.START_DATE AND ss.END_DATE IS NULL) AND ss.MARKING_PERIOD_ID IN (' . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ') AND p.ATTENDANCE=\'Y\') AS PERIOD_ATTENDANCE'; } foreach ($periods_RET as $period) { if ($_REQUEST['fields']['PERIOD_' . $period['PERIOD_ID']] == 'Y') { //modif Francois: multiple school periods for a course period //$extra['SELECT'] .= ',array(SELECT st.FIRST_NAME||\' \'||st.LAST_NAME||\' - \'||coalesce(cp.ROOM,\' \') FROM STAFF st,SCHEDULE ss,COURSE_PERIODS cp WHERE ss.STUDENT_ID=ssm.STUDENT_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND cp.TEACHER_ID=st.STAFF_ID AND cp.PERIOD_ID=\''.$period['PERIOD_ID'].'\' AND (\''.$date.'\' BETWEEN ss.START_DATE AND ss.END_DATE OR \''.$date.'\'>=ss.START_DATE AND ss.END_DATE IS NULL) AND ss.MARKING_PERIOD_ID IN ('.GetAllMP('QTR',GetCurrentMP('QTR',$date)).')) AS PERIOD_'.$period['PERIOD_ID']; $extra['SELECT'] .= ',array(SELECT st.FIRST_NAME||\' \'||st.LAST_NAME||\' - \'||coalesce(cp.ROOM,\' \') FROM STAFF st,SCHEDULE ss,COURSE_PERIODS cp,COURSE_PERIOD_SCHOOL_PERIODS cpsp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND ss.STUDENT_ID=ssm.STUDENT_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND cp.TEACHER_ID=st.STAFF_ID AND cpsp.PERIOD_ID=\'' . $period['PERIOD_ID'] . '\' AND (\'' . $date . '\' BETWEEN ss.START_DATE AND ss.END_DATE OR \'' . $date . '\'>=ss.START_DATE AND ss.END_DATE IS NULL) AND ss.MARKING_PERIOD_ID IN (' . GetAllMP('QTR', GetCurrentMP('QTR', $date)) . ')) AS PERIOD_' . $period['PERIOD_ID']; $extra['functions']['PERIOD_' . $period['PERIOD_ID']] = '_makeTeachers'; } } if ($RosarioModules['Food_Service'] && ($_REQUEST['fields']['FS_ACCOUNT_ID'] == 'Y' || $_REQUEST['fields']['FS_DISCOUNT'] == 'Y' || $_REQUEST['fields']['FS_STATUS'] == 'Y' || $_REQUEST['fields']['FS_BARCODE'] == 'Y' || $_REQUEST['fields']['FS_BALANCE'] == 'Y')) { $extra['FROM'] .= ',FOOD_SERVICE_STUDENT_ACCOUNTS fssa'; $extra['WHERE'] .= ' AND fssa.STUDENT_ID=ssm.STUDENT_ID'; if ($_REQUEST['fields']['FS_ACCOUNT_ID'] == 'Y') { $extra['SELECT'] .= ',fssa.ACCOUNT_ID AS FS_ACCOUNT_ID'; } if ($_REQUEST['fields']['FS_DISCOUNT'] == 'Y') { $extra['SELECT'] .= ',coalesce(fssa.DISCOUNT,\'Full\') AS FS_DISCOUNT'; } if ($_REQUEST['fields']['FS_STATUS'] == 'Y') { $extra['SELECT'] .= ',coalesce(fssa.STATUS,\'Active\') AS FS_STATUS'; }
$handle = PDFStart(); foreach ($RET as $student) { $student_points = $total_points = $percent_weights = array(); unset($_CENTRE['DrawHeader']); echo "<table width=100% style=\" font-family:Arial; font-size:12px;\" >"; echo "<tr><td style=\"font-size:15px; font-weight:bold; padding-top:20px;\">" . GetSchool(UserSchool()) . "<div style=\"font-size:12px;\">Student Progress Report</div></td><td align=right style=\"padding-top:20px;\">" . ProperDate(DBDate()) . "<br/>Powered by openSIS</td></tr><tr><td colspan=2 style=\"border-top:1px solid #333;\"> </td></tr></table>"; echo '<table border=0 style=\\"font-size:12px;\\">'; echo "<tr><td>Student Name:</td>"; echo "<td>" . $student['FULL_NAME'] . "</td></tr>"; echo "<tr><td>ID:</td>"; echo "<td>" . $student['STUDENT_ID'] . " </td></tr>"; echo "<tr><td>Grade:</td>"; echo "<td>" . $student['GRADE_ID'] . " </td></tr>"; echo "<tr><td>Course</td><td>" . $course_title . "</td></tr>"; echo "<tr><td>Marking Period:</td>"; echo "<td>" . GetMP(GetCurrentMP('QTR', DBDate())) . " </td></tr>"; #echo '</table>'; #if($_REQUEST['mailing_labels']=='Y') if ($_REQUEST['mailing_labels'] == 'Y') { echo '<tr><TD colspan=2>' . $student['MAILING_LABEL'] . '</TD></TR>'; } unset($student_points); unset($total_points); unset($percent_weights); if ($programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y') { $sql = "SELECT a.TITLE,a.ASSIGNED_DATE,a.DUE_DATE, t.ASSIGNMENT_TYPE_ID, t.FINAL_GRADE_PERCENT,g.POINTS,a.POINTS AS TOTAL_POINTS,g.COMMENT,g.POINTS AS LETTER_GRADE,CASE WHEN (a.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=a.ASSIGNED_DATE) AND (a.DUE_DATE IS NULL OR CURRENT_DATE>=a.DUE_DATE) THEN 'Y' ELSE NULL END AS DUE FROM GRADEBOOK_ASSIGNMENT_TYPES t,GRADEBOOK_ASSIGNMENTS a LEFT OUTER JOIN GRADEBOOK_GRADES g ON (a.ASSIGNMENT_ID=g.ASSIGNMENT_ID AND g.STUDENT_ID='{$student['STUDENT_ID']}' AND g.COURSE_PERIOD_ID='" . UserCoursePeriod() . "') WHERE a.ASSIGNMENT_TYPE_ID=t.ASSIGNMENT_TYPE_ID AND (a.COURSE_PERIOD_ID='" . UserCoursePeriod() . "' OR a.COURSE_ID='{$course_id}' AND a.STAFF_ID='" . User('STAFF_ID') . "') AND t.COURSE_ID='{$course_id}' AND a.MARKING_PERIOD_ID='" . UserMP() . "'"; } else { $sql = "SELECT a.TITLE,a.ASSIGNED_DATE,a.DUE_DATE,'-1' AS ASSIGNMENT_TYPE_ID,'1' AS FINAL_GRADE_PERCENT,g.POINTS,a.POINTS AS TOTAL_POINTS,g.COMMENT,g.POINTS AS LETTER_GRADE,CASE WHEN (a.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=a.ASSIGNED_DATE) AND (a.DUE_DATE IS NULL OR CURRENT_DATE>=a.DUE_DATE) THEN 'Y' ELSE NULL END AS DUE FROM GRADEBOOK_ASSIGNMENTS a LEFT OUTER JOIN GRADEBOOK_GRADES g ON (a.ASSIGNMENT_ID=g.ASSIGNMENT_ID AND g.STUDENT_ID='{$student['STUDENT_ID']}' AND g.COURSE_PERIOD_ID='" . UserCoursePeriod() . "') WHERE (a.COURSE_PERIOD_ID='" . UserCoursePeriod() . "' OR a.COURSE_ID='{$course_id}' AND a.STAFF_ID='" . User('STAFF_ID') . "') AND a.MARKING_PERIOD_ID='" . UserMP() . "'"; } if ($_REQUEST['exclude_notdue'] == 'Y') { $sql .= " AND ((a.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=a.ASSIGNED_DATE) AND (a.DUE_DATE IS NULL OR CURRENT_DATE>=DUE_DATE) OR g.POINTS IS NOT NULL)";
} else { $LO_group = array(); $singular = _('Assignment'); $plural = _('Assignments'); } $extra2['functions'] = array('ASSIGNED_DATE' => '_removeSpaces', 'DUE_DATE' => '_removeSpaces', 'TITLE' => '_removeSpaces', 'POINTS' => '_makeExtra', 'PERCENT_GRADE' => '_makeExtra', 'LETTER_GRADE' => '_makeExtra'); $handle = PDFStart(); foreach ($RET as $student) { unset($_CENTRE['DrawHeader']); if ($_REQUEST['mailing_labels'] == 'Y') { echo '<BR><BR><BR>'; } DrawHeader(Config('TITLE') . ' ' . _('Progress Report')); DrawHeader('</font>' . $student['FULL_NAME'] . '<font>', $student['STUDENT_ID']); DrawHeader($student['GRADE_ID'], GetSchool(UserSchool())); DrawHeader($course_title, GetMP(GetCurrentMP('QTR', DBDate()))); DrawHeader(ProperDate(DBDate())); if ($_REQUEST['mailing_labels'] == 'Y') { echo '<BR><BR><TABLE width=100%><TR><TD width=50> </TD><TD>' . $student['MAILING_LABEL'] . '</TD></TR></TABLE><BR>'; } $extra = $extra2; $extra['WHERE'] .= " AND s.STUDENT_ID='{$student['STUDENT_ID']}'"; $student_points = $total_points = $percent_weights = array(); $grades_RET = GetStuList($extra); $sum_student_points = $sum_total_points = 0; $sum_points = $sum_percent = 0; foreach ($percent_weights as $assignment_type_id => $percent) { $sum_student_points += $student_points[$assignment_type_id]; $sum_total_points += $total_points[$assignment_type_id]; $sum_points += $student_points[$assignment_type_id] * ($programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y' ? $percent / $total_points[$assignment_type_id] : 1); $sum_percent += $programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y' ? $percent : $total_points[$assignment_type_id];
function GetStuList(&$extra) { global $contacts_RET, $view_other_RET, $_openSIS; $offset = 'GRADE_ID'; if ((!$extra['SELECT_ONLY'] || strpos($extra['SELECT_ONLY'], $offset) !== 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 = GetCurrentMP('QTR', $extra['DATE'], false); } 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_fields_RET = DBGet(DBQuery("SELECT cf.ID,cf.TYPE,cf.TITLE FROM PROGRAM_USER_CONFIG puc,CUSTOM_FIELDS cf WHERE puc.TITLE=cf.ID AND puc.PROGRAM='StudentFieldsView' AND puc.USER_ID='".User('STAFF_ID')."' AND puc.VALUE='Y'")); ############################################################################################# $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 ('PHONE','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('PHONE' => 'Phone', 'GENDER' => 'Gender', 'ETHNICITY' => 'Ethnicity', 'ADDRESS' => 'Mailing Address', 'CITY' => 'City', 'STATE' => 'State', 'ZIPCODE' => 'Zipcode') + $extra['columns_after']; $select = ',s.PHONE,s.GENDER,s.ETHNICITY,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.MAILING='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ".$extra['FROM']; $extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID ) LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $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.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'] .= 'COALESCE(sjp.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.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,a.PHONE,sjp.ADDRESS_ID,COALESCE(sjp.CUSTODY,\'N\') '; $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'] .= 'COALESCE(sjp.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) { $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(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; } 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 '; } 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,s.PHONE,ssm.SCHOOL_ID,s.ALT_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID,s.PHONE ' . $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.START_DATE) 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 "; if ($_REQUEST['mp_comment']) { $sql .= ",STUDENT_MP_COMMENTS smc "; } if ($_REQUEST['goal_title'] || $_REQUEST['goal_description']) { $sql .= ",GOAL g "; } if ($_REQUEST['progress_name'] || $_REQUEST['progress_description']) { $sql .= ",PROGRESS p "; } if ($_REQUEST['doctors_note_comments'] || $_REQUEST['med_day'] || $_REQUEST['med_month'] || $_REQUEST['med_year']) { $sql .= ",STUDENT_MEDICAL_NOTES smn "; } if ($_REQUEST['type'] || $_REQUEST['imm_comments'] || $_REQUEST['imm_day'] || $_REQUEST['imm_month'] || $_REQUEST['imm_year']) { $sql .= ",STUDENT_MEDICAL sm "; } if ($_REQUEST['med_alrt_title'] || $_REQUEST['ma_day'] || $_REQUEST['ma_month'] || $_REQUEST['ma_year']) { $sql .= ",STUDENT_MEDICAL_ALERTS sma "; } if ($_REQUEST['reason'] || $_REQUEST['result'] || $_REQUEST['med_vist_comments'] || $_REQUEST['nv_day'] || $_REQUEST['nv_month'] || $_REQUEST['nv_year']) { $sql .= ",STUDENT_MEDICAL_VISITS smv "; } $sql .= ",STUDENT_ENROLLMENT ssm "; $sql .= $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.START_DATE) AND ('" . date('Y-m-d', strtotime($extra['DATE'])) . "'<=ssm.END_DATE OR ssm.END_DATE IS NULL)) "; } //$sql .= " AND ssm.SYEAR='".UserSyear()."' AND ('".date('Y-m-d',strtotime($extra['DATE']))."'>=ssm.START_DATE 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 { if (User('SCHOOLS')) { $sql .= " AND ssm.SCHOOL_ID IN (" . substr(str_replace(',', "','", User('SCHOOLS')), 2, -2) . ") "; } $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,s.PHONE,s.ALT_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("(ssm.START_DATE IS NOT NULL 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 "; if ($_REQUEST['mp_comment']) { $sql .= ",STUDENT_MP_COMMENTS smc "; } if ($_REQUEST['goal_title'] || $_REQUEST['goal_description']) { $sql .= ",GOAL g "; } if ($_REQUEST['progress_name'] || $_REQUEST['progress_description']) { $sql .= ",PROGRESS p "; } if ($_REQUEST['doctors_note_comments'] || $_REQUEST['med_day'] || $_REQUEST['med_month'] || $_REQUEST['med_year']) { $sql .= ",STUDENT_MEDICAL_NOTES smn "; } if ($_REQUEST['type'] || $_REQUEST['imm_comments'] || $_REQUEST['imm_day'] || $_REQUEST['imm_month'] || $_REQUEST['imm_year']) { $sql .= ",STUDENT_MEDICAL sm "; } if ($_REQUEST['med_alrt_title'] || $_REQUEST['ma_day'] || $_REQUEST['ma_month'] || $_REQUEST['ma_year']) { $sql .= ",STUDENT_MEDICAL_ALERTS sma "; } if ($_REQUEST['reason'] || $_REQUEST['result'] || $_REQUEST['med_vist_comments'] || $_REQUEST['nv_day'] || $_REQUEST['nv_month'] || $_REQUEST['nv_year']) { $sql .= ",STUDENT_MEDICAL_VISITS smv "; } $sql .= " ,STUDENT_ENROLLMENT ssm "; $sql .= $extra['FROM'] . " WHERE ssm.STUDENT_ID=s.STUDENT_ID AND ssm.STUDENT_ID=ss.STUDENT_ID\r\n\t\t\t\t\tAND ssm.SCHOOL_ID='" . UserSchool() . "' AND ssm.SYEAR='" . UserSyear() . "' AND ssm.SYEAR=cp.SYEAR AND ssm.SYEAR=ss.SYEAR\r\n\t\t\t\t\tAND ss.MARKING_PERIOD_ID IN (" . GetAllMP('', $queryMP) . ")\r\n\t\t\t\t\tAND (cp.TEACHER_ID='" . User('STAFF_ID') . "' OR cp.SECONDARY_TEACHER_ID='" . User('STAFF_ID') . "') AND cp.COURSE_PERIOD_ID='" . UserCoursePeriod() . "'\r\n\t\t\t\t\tAND cp.COURSE_ID=ss.COURSE_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID"; 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.END_DATE OR ssm.END_DATE IS NULL))"; $sql .= " AND (ssm.START_DATE IS NOT NULL AND ('" . $extra['DATE'] . "'<=ss.END_DATE OR ss.END_DATE IS NULL))"; // $sql .= " 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,s.ALT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT']; } $sql .= " FROM STUDENTS s,STUDENT_ENROLLMENT ssm " . $extra['FROM'] . "\r\n\t\t\t\t\tWHERE 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 .= CustomFields('where'); $sql = appendSQL($sql, $extra); $sql .= $extra['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']); }
$extra['WHERE'] = " AND s.STUDENT_ID IN ({$st_list})"; $extra['SELECT'] .= ",coalesce(s.CUSTOM_200000002,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 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 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(" . GetAllMP('QTR', GetCurrentMP('QTR', DBDate(), false)) . ") 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(" . GetAllMP('QTR', GetCurrentMP('QTR', DBDate(), false)) . ") 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"; } } } else { if ($_REQUEST['teacher']) { $extra['SELECT'] .= ",(SELECT st.FIRST_NAME||' '||st.LAST_NAME FROM STAFF st,COURSE_PERIODS cp WHERE st.STAFF_ID=cp.TEACHER_ID AND cp.COURSE_PERIOD_ID='" . UserCoursePeriod() . "') AS TEACHER"; } if ($_REQUEST['room']) { $extra['SELECT'] .= ",(SELECT cp.ROOM FROM COURSE_PERIODS cp WHERE cp.COURSE_PERIOD_ID='" . UserCoursePeriod() . "') AS ROOM"; } } $RET = GetStuList($extra); if (count($RET)) { $skipRET = array(); for ($i = ($_REQUEST['start_row'] - 1) * $max_cols + $_REQUEST['start_col']; $i > 1; $i--) { $skipRET[-$i] = array('LAST_NAME' => ' ');
Widgets('course'); Widgets('request'); Widgets('activity'); Widgets('absences'); Widgets('gpa'); Widgets('class_rank'); Widgets('letter_grade'); Widgets('eligibility'); $extra['force_search'] = true; $extra['search'] .= '<TR><TD valign=top align=right>Letter Text</TD><TD><TEXTAREA name=letter_text rows=5 cols=40></TEXTAREA>'; $current_mp = GetCurrentMP('QTR', DBDate()); if (!$current_mp) { $current_mp = GetCurrentMP('SEM', DBDate()); } if (!$current_mp) { $current_mp = GetCurrentMP('FY', DBDate()); } if (!$_REQUEST['search_modfunc'] || $_openSIS['modules_search']) { DrawBC("Students -> " . ProgramTitle()); $extra['new'] = true; $extra['pdf'] = 'true'; Search('student_id', $extra); } else { $RET = GetStuList($extra); if (count($RET)) { $_REQUEST['letter_text'] = nl2br(str_replace("\\'", "'", str_replace(' ', ' ', $_REQUEST['letter_text']))); $handle = PDFStart(); foreach ($RET as $student) { $student_points = $total_points = 0; unset($_openSIS['DrawHeader']); 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_mp = GetCurrentMP('QTR', $date); $all_mp = GetAllMP(GetMPTable(GetMP($current_mp, 'TABLE')), $current_mp); $current_schedule_RET[$THIS_RET['STUDENT_ID']] = DBGet(DBQuery("SELECT cp.PERIOD_ID,cp.COURSE_PERIOD_ID,cp.HALF_DAY 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 ('{$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 ({$all_mp}) ORDER BY s.START_DATE ASC"), 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']; $code_title = 'TITLE'; } else { $period_id = substr($title, 7); $period = $current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id][1]['COURSE_PERIOD_ID']; #$code_title = 'SHORT_NAME'; $code_title = 'TITLE'; } if ($current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id]) { foreach ($codes_RET as $code) { if ($current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id][1]['HALF_DAY'] != 'Y' || $code['STATE_CODE'] != 'H') { // prune half day codes for half day courses $options[$code['ID']] = $code[$code_title]; } } $val = $current_RET[$THIS_RET['STUDENT_ID']][$period][1]['ATTENDANCE_CODE']; return SelectInput($val, 'attendance[' . $THIS_RET['STUDENT_ID'] . '][' . $period . '][ATTENDANCE_CODE]', '', $options); } else { return false; } }
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_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); $current_schedule_RET[$THIS_RET['STUDENT_ID']] = DBGet(DBQuery('SELECT cpv.PERIOD_ID,cp.COURSE_PERIOD_ID,cp.HALF_DAY FROM schedule s,course_periods cp,course_period_var cpv WHERE s.STUDENT_ID=\'' . $THIS_RET['STUDENT_ID'] . '\' AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID AND s.SYEAR=\'' . UserSyear() . '\' AND s.SCHOOL_ID=\'' . UserSchool() . '\' AND cp.COURSE_PERIOD_ID = s.COURSE_PERIOD_ID AND cpv.DOES_ATTENDANCE=\'Y\' 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 (' . $all_mp . ') or s.MARKING_PERIOD_ID IS NULL) ORDER BY s.START_DATE ASC'), 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['PERIOD_ID'] . '_' . $THIS_RET['COURSE_PERIOD_ID']; $period_id = $THIS_RET['PERIOD_ID']; $code_title = 'TITLE'; } else { $period_id = substr($title, 7); $period = $current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id][1]['PERIOD_ID'] . '_' . $current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id][1]['COURSE_PERIOD_ID']; $code_title = 'TITLE'; } if ($current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id]) { foreach ($codes_RET as $code) { if ($current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id][1]['HALF_DAY'] != 'Y' || $code['STATE_CODE'] != 'H') { // prune half day codes for half day courses $options[$code['ID']] = $code[$code_title]; } } $crp_id = $current_schedule_RET[$THIS_RET['STUDENT_ID']][$period_id][1]['COURSE_PERIOD_ID']; $val = $current_RET[$THIS_RET['STUDENT_ID']][$crp_id][1]['ATTENDANCE_CODE']; return SelectInput($val, 'attendance[' . $THIS_RET['STUDENT_ID'] . '][' . $crp_id . '][ATTENDANCE_CODE]', '', $options); } else { return false; } }
echo '<FORM action="Modules.php?modname=' . $_REQUEST['modname'] . '&table=' . $_REQUEST['table'] . '" method="POST">'; DrawHeader(PrepareDate($date, '_date', false, array('submit' => true))); echo '</FORM>'; ErrorMessage(array(_('You cannot take attendance for this period on this day.')), 'fatal'); } $qtr_id = GetCurrentMP('QTR', $date, false); if (!$qtr_id) { echo '<FORM action="Modules.php?modname=' . $_REQUEST['modname'] . '&table=' . $_REQUEST['table'] . '" method="POST">'; DrawHeader(PrepareDate($date, '_date', false, array('submit' => true))); echo '</FORM>'; ErrorMessage(array(_('The selected date is not in a school quarter.')), 'fatal'); } // if running as a teacher program then rosario[allow_edit] will already be set according to admin permissions if (!isset($_ROSARIO['allow_edit'])) { // allow teacher edit if selected date is in the current quarter or in the corresponding grade posting period $current_qtr_id = GetCurrentMP('QTR', DBDate(), false); $time = strtotime(DBDate('postgres')); if (($current_qtr_id && $qtr_id == $current_qtr_id || GetMP($qtr_id, 'POST_START_DATE') && $time <= strtotime(GetMP($qtr_id, 'POST_END_DATE'))) && ($program_config['ATTENDANCE_EDIT_DAYS_BEFORE'][1]['VALUE'] == null || strtotime($date) <= $time + $program_config['ATTENDANCE_EDIT_DAYS_BEFORE'][1]['VALUE'] * 86400) && ($program_config['ATTENDANCE_EDIT_DAYS_AFTER'][1]['VALUE'] == '' || strtotime($date) >= $time - $program_config['ATTENDANCE_EDIT_DAYS_AFTER'][1]['VALUE'] * 86400)) { $_ROSARIO['allow_edit'] = true; } } $current_Q = "SELECT ATTENDANCE_TEACHER_CODE,STUDENT_ID,ADMIN,COMMENT,COURSE_PERIOD_ID,ATTENDANCE_REASON FROM {$table} t WHERE SCHOOL_DATE='{$date}' AND PERIOD_ID='" . UserPeriod() . "'" . ($table == 'LUNCH_PERIOD' ? " AND TABLE_NAME='{$_REQUEST['table']}'" : ''); $current_RET = DBGet(DBQuery($current_Q), array(), array('STUDENT_ID')); if ($_REQUEST['attendance'] && $_POST['attendance']) { foreach ($_REQUEST['attendance'] as $student_id => $value) { if ($current_RET[$student_id]) { $sql = "UPDATE {$table} SET ATTENDANCE_TEACHER_CODE='" . mb_substr($value, 5) . "',COURSE_PERIOD_ID='" . UserCoursePeriod() . "'"; if ($current_RET[$student_id][1]['ADMIN'] != 'Y') { $sql .= ",ATTENDANCE_CODE='" . mb_substr($value, 5) . "'"; } if ($_REQUEST['comment'][$student_id]) {
function GetStuList_Absence_Summary(&$extra) { global $contacts_RET, $view_other_RET, $_openSIS; $offset = 'GRADE_ID'; if ((!$extra['SELECT_ONLY'] || strpos($extra['SELECT_ONLY'], $offset) !== 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 = GetCurrentMP('QTR', $extra['DATE'], false); } 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_fields_RET = DBGet(DBQuery('SELECT cf.ID,cf.TYPE,cf.TITLE FROM program_user_config puc,custom_fields cf WHERE puc.TITLE=cf.ID AND puc.PROGRAM=\'StudentFieldsView\' AND puc.USER_ID=\'' . User('STAFF_ID') . '\' AND puc.VALUE=\'Y\'')); ############################################################################################# $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 (\'PHONE\',\'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('PHONE' => 'Phone', 'GENDER' => 'Gender', 'ETHNICITY' => 'Ethnicity', 'ADDRESS' => 'Mailing Address', 'CITY' => 'City', 'STATE' => 'State', 'ZIPCODE' => 'Zipcode') + $extra['columns_after']; $select = ',s.PHONE,s.GENDER,s.ETHNICITY,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.MAILING='Y') LEFT OUTER JOIN address a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ".$extra['FROM']; $extra['FROM'] = ' LEFT OUTER JOIN students_join_address sam ON (ssm.STUDENT_ID=sam.STUDENT_ID ) LEFT OUTER JOIN address a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ' . $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.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'] .= 'COALESCE(sjp.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.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,a.PHONE,sjp.ADDRESS_ID,COALESCE(sjp.CUSTODY,\'N\') '; $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'] .= 'COALESCE(sjp.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) { $custom = DBGet(DBQuery('SHOW COLUMNS FROM students WHERE FIELD=\'CUSTOM_' . $field['ID'] . '\'')); $custom = $custom[1]; if ($custom) { $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']; } else { $custom_stu = DBGet(DBQuery("SELECT TYPE,TITLE FROM custom_fields WHERE ID='" . $field['ID'] . "'")); $custom_stu = $custom_stu[1]; if ($custom_stu['TYPE'] == 'date') { $functions[strtolower(str_replace(" ", "_", $custom_stu['TITLE']))] = 'ProperDate'; } elseif ($custom_stu['TYPE'] == 'numeric') { $functions[strtolower(str_replace(" ", "_", $custom_stu['TITLE']))] = 'removeDot00'; } elseif ($custom_stu['TYPE'] == 'codeds') { $functions[strtolower(str_replace(" ", "_", $custom_stu['TITLE']))] = 'DeCodeds'; } $select .= ',s.' . strtoupper(str_replace(" ", "_", $custom_stu['TITLE'])); $extra['columns_after'] += array(strtoupper(str_replace(" ", "_", $custom_stu['TITLE'])) => $custom_stu['TITLE']); } } 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(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; } 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 '; } $_SESSION['new_customsql'] = $extra['SELECT']; 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,'; } $_SESSION['new_sql'] = $sql; $sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,s.PHONE,ssm.SCHOOL_ID,s.ALT_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID' . $extra['SELECT']; $_SESSION['new_sql'] .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,s.PHONE,ssm.SCHOOL_ID,s.ALT_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID' . $_SESSION['new_customsql']; 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.START_DATE) 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 '; } $_SESSION['new_sql'] .= ',' . db_case(array('(ssm.SYEAR=\'' . UserSyear() . '\' AND ( (ssm.START_DATE IS NOT NULL AND \'' . date('Y-m-d', strtotime($extra['DATE'])) . '\'>=ssm.START_DATE) 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 '; $_SESSION['new_sql'] .= ' FROM students s '; if ($_REQUEST['mp_comment']) { $sql .= ",student_mp_comments smc "; $_SESSION['newsql'] .= ',student_mp_comments smc '; } if ($_REQUEST['goal_title'] || $_REQUEST['goal_description']) { $sql .= ',goal g '; $_SESSION['newsql'] .= ',goal g '; } if ($_REQUEST['progress_name'] || $_REQUEST['progress_description']) { $sql .= ",progress p "; $_SESSION['newsql'] .= ',progress p '; } if ($_REQUEST['doctors_note_comments'] || $_REQUEST['med_day'] || $_REQUEST['med_month'] || $_REQUEST['med_year']) { $sql .= ",student_medical_notes smn "; $_SESSION['newsql'] .= ',student_medical_notes smn '; } if ($_REQUEST['type'] || $_REQUEST['imm_comments'] || $_REQUEST['imm_day'] || $_REQUEST['imm_month'] || $_REQUEST['imm_year']) { $sql .= ',student_immunization sm '; $_SESSION['newsql'] .= ',student_immunization sm '; } if ($_REQUEST['med_alrt_title'] || $_REQUEST['ma_day'] || $_REQUEST['ma_month'] || $_REQUEST['ma_year']) { $sql .= ",student_medical_alerts sma "; $_SESSION['newsql'] .= ',student_medical_alerts sma '; } if ($_REQUEST['reason'] || $_REQUEST['result'] || $_REQUEST['med_vist_comments'] || $_REQUEST['nv_day'] || $_REQUEST['nv_month'] || $_REQUEST['nv_year']) { $sql .= ",student_medical_visits smv "; $_SESSION['newsql'] .= ',student_medical_visits smv '; } $_SESSION['new_sql'] .= $_SESSION['newsql']; $sql .= ',student_enrollment ssm '; $_SESSION['new_sql'] .= ',student_enrollment ssm '; $sql .= $extra['FROM'] . ' WHERE ssm.STUDENT_ID=s.STUDENT_ID '; $_SESSION['new_sql'] .= $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)'; $_SESSION['new_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 .= $_SESSION['inactive_stu_filter'] = ' AND ssm.SYEAR=\'' . UserSyear() . '\' AND ((ssm.START_DATE IS NOT NULL AND \'' . date('Y-m-d', strtotime($extra['DATE'])) . '\'>=ssm.START_DATE) AND (\'' . date('Y-m-d', strtotime($extra['DATE'])) . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL)) '; //$sql .= " AND ssm.SYEAR='".UserSyear()."' AND ('".date('Y-m-d',strtotime($extra['DATE']))."'>=ssm.START_DATE AND ('".date('Y-m-d',strtotime($extra['DATE']))."'<=ssm.END_DATE OR ssm.END_DATE IS NULL)) "; $_SESSION['new_sql'] .= ' AND ssm.SYEAR=\'' . UserSyear() . '\' AND ((ssm.START_DATE IS NOT NULL AND \'' . date('Y-m-d', strtotime($extra['DATE'])) . '\'>=ssm.START_DATE) 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.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=\'' . UserSchool() . '\''; $_SESSION['new_sql'] .= ' AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=\'' . UserSchool() . '\''; } else { // if(User('SCHOOLS')) // { $sql .= ' AND ssm.SCHOOL_ID IN (' . GetUserSchools(UserID(), true) . ') '; $_SESSION['new_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,'; } $_SESSION['new_sql'] = $sql; $sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,s.PHONE,s.ALT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT']; $_SESSION['new_sql'] .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,s.PHONE,s.ALT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $_SESSION['new_customsql']; 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('(ssm.START_DATE IS NOT NULL 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'; $_SESSION['new_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'; $_SESSION['new_sql'] .= ',' . db_case(array('(ssm.START_DATE IS NOT NULL 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 '; $_SESSION['new_sql'] .= ' FROM students s,course_periods cp,schedule ss '; if ($_REQUEST['mp_comment']) { $sql .= ',student_mp_comments smc '; $_SESSION['newsql'] .= ',student_mp_comments smc '; } if ($_REQUEST['goal_title'] || $_REQUEST['goal_description']) { $sql .= ',goal g '; $_SESSION['newsql'] .= ',goal g '; } if ($_REQUEST['progress_name'] || $_REQUEST['progress_description']) { $sql .= ',progress p '; $_SESSION['newsql'] .= ',progress p '; } if ($_REQUEST['doctors_note_comments'] || $_REQUEST['med_day'] || $_REQUEST['med_month'] || $_REQUEST['med_year']) { $sql .= ',student_medical_notes smn '; $_SESSION['newsql'] .= ',student_medical_notes smn '; } if ($_REQUEST['type'] || $_REQUEST['imm_comments'] || $_REQUEST['imm_day'] || $_REQUEST['imm_month'] || $_REQUEST['imm_year']) { $sql .= ',student_immunization sm '; $_SESSION['newsql'] .= ',student_immunization sm '; } if ($_REQUEST['med_alrt_title'] || $_REQUEST['ma_day'] || $_REQUEST['ma_month'] || $_REQUEST['ma_year']) { $sql .= ',student_medical_alerts sma '; $_SESSION['newsql'] .= ',student_medical_alerts sma '; } if ($_REQUEST['reason'] || $_REQUEST['result'] || $_REQUEST['med_vist_comments'] || $_REQUEST['nv_day'] || $_REQUEST['nv_month'] || $_REQUEST['nv_year']) { $sql .= ',student_medical_visits smv '; $_SESSION['newsql'] .= ',student_medical_visits smv '; } $_SESSION['new_sql'] .= $_SESSION['newsql']; $sql .= ' ,student_enrollment ssm '; $_SESSION['new_sql'] .= ' ,student_enrollment ssm '; $sql .= $extra['FROM'] . ' WHERE ssm.STUDENT_ID=s.STUDENT_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 (ss.MARKING_PERIOD_ID IN (' . GetAllMP('', $queryMP) . ') OR (ss.START_DATE<=\'' . date('Y-m-d') . '\' AND (ss.END_DATE>=\'' . date('Y-m-d') . '\' OR ss.END_DATE IS NULL))) AND (cp.TEACHER_ID=\'' . User('STAFF_ID') . '\' OR cp.SECONDARY_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'; $_SESSION['new_sql'] .= $extra['FROM'] . ' WHERE ssm.STUDENT_ID=s.STUDENT_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 (ss.MARKING_PERIOD_ID IN (' . GetAllMP('', $queryMP) . ') OR (ss.START_DATE<=\'' . date('Y-m-d') . '\' AND (ss.END_DATE>=\'' . date('Y-m-d') . '\' OR ss.END_DATE IS NULL))) AND (cp.TEACHER_ID=\'' . User('STAFF_ID') . '\' OR cp.SECONDARY_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 ($_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)'; $_SESSION['new_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)'; $_SESSION['new_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 .= $_SESSION['inactive_stu_filter'] = ' AND (ssm.START_DATE IS NOT NULL AND (\'' . $extra['DATE'] . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL))'; $sql .= $_SESSION['inactive_stu_filter'] = ' AND (ssm.START_DATE IS NOT NULL AND (\'' . $extra['DATE'] . '\'<=ss.END_DATE OR ss.END_DATE IS NULL))'; // $sql .= " 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))"; $_SESSION['new_sql'] .= ' AND (ssm.START_DATE IS NOT NULL AND (\'' . $extra['DATE'] . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL))'; $_SESSION['new_sql'] .= ' AND (ssm.START_DATE IS NOT NULL 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,s.ALT_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_people WHERE PERSON_ID=\'' . $extra['ASSOCIATED'] . '\')' : '=\'' . UserStudentID() . '\''); break; default: exit('Error'); } if ($expanded_view == true) { $custom_str = CustomFields('where', '', 1); if ($custom_str != '') { $_SESSION['custom_count_sql'] = $custom_str; } $sql .= $custom_str; } elseif ($expanded_view == false) { $custom_str = CustomFields('where', '', 2); if ($custom_str != '') { $_SESSION['custom_count_sql'] = $custom_str; } $sql .= $custom_str; } else { $custom_str = CustomFields('where'); if ($custom_str != '') { $_SESSION['custom_count_sql'] = $custom_str; } $sql .= $custom_str; } $sql .= $extra['WHERE'] . ' '; $sql = appendSQL_Absence_Summary($sql, $extra); // TODO Modification Required // if($_SESSION['stu_search']['sql'] && $_REQUEST['return_session'] && $extra['SELECT']!='' && strpos($sql,'address a')==0) // { // $sql = str_replace("FROM", $extra['SELECT']." FROM",$sql); // } // // if($_SESSION['stu_search']['sql'] && $_REQUEST['return_session'] && $extra['FROM']!='' && strpos($sql,'address a')==0) // { // $sql = str_replace("WHERE",$extra['FROM']." WHERE",$sql); // // } // -------------------------------------------------- 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'] && !($_SESSION['stu_search']['sql'] && $_REQUEST['return_session'])) { $sql .= ' ORDER BY ' . $extra['ORDER_BY']; } if ($extra['DEBUG'] === true) { echo '<!--' . $sql . '-->'; } $return = DBGet(DBQuery($sql), $functions, $extra['group']); $_SESSION['count_stu'] = count($return); return $return; }
case 'Thu': $day = 'H'; break; default: $day = substr($day, 0, 1); break; } $p = optional_param('period', '', PARAM_SPCL); $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 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'));
$_REQUEST['day_date'] = date('d'); $_REQUEST['month_date'] = mb_strtoupper(date('M')); $_REQUEST['year_date'] = date('Y'); } if ($_REQUEST['table'] == '') { $_REQUEST['table'] = '0'; } if ($_REQUEST['table'] == '0') { $table = 'ATTENDANCE_PERIOD'; $extra_sql = ''; } else { $table = 'LUNCH_PERIOD'; $extra_sql = " AND TABLE_NAME='{$_REQUEST['table']}'"; } $_SESSION['Administration.php']['date'] = $date; $current_mp = GetCurrentMP('QTR', $date, false); if (!$current_mp) { echo '<FORM action="' . PreparePHP_SELF($_REQUEST, array('day_date', 'month_date', 'year_date', 'codes')) . '" method="POST">'; DrawHeader(PrepareDate($date, '_date', false, array('submit' => true))); echo '</FORM>'; ErrorMessage(array(_('The selected date is not in a school quarter.')), 'fatal'); } $all_mp = GetAllMP('QTR', $current_mp); $current_Q = "SELECT ATTENDANCE_TEACHER_CODE,ATTENDANCE_CODE,ATTENDANCE_REASON,COMMENT,STUDENT_ID,ADMIN,PERIOD_ID FROM {$table} WHERE SCHOOL_DATE='{$date}'" . $extra_sql; //modif Francois: days numbered //modif Francois: multiple school periods for a course period if (SchoolInfo('NUMBER_DAYS_ROTATION') !== null) { $current_schedule_Q = "SELECT cpsp.PERIOD_ID,cp.COURSE_PERIOD_ID,cp.HALF_DAY FROM SCHEDULE s,COURSE_PERIODS cp, COURSE_PERIOD_SCHOOL_PERIODS cpsp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND s.STUDENT_ID='__student_id__' AND s.SYEAR='" . UserSyear() . "' AND s.SCHOOL_ID='" . UserSchool() . "' AND cp.COURSE_PERIOD_ID = s.COURSE_PERIOD_ID AND position(',{$_REQUEST['table']},' IN cp.DOES_ATTENDANCE)>0 AND ('{$date}' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND '{$date}'>=s.START_DATE)) AND position(substring('MTWHFSU' FROM cast((SELECT CASE COUNT(school_date)% " . SchoolInfo('NUMBER_DAYS_ROTATION') . " WHEN 0 THEN " . SchoolInfo('NUMBER_DAYS_ROTATION') . " ELSE COUNT(school_date)% " . SchoolInfo('NUMBER_DAYS_ROTATION') . " END AS day_number FROM attendance_calendar WHERE school_date>=(SELECT start_date FROM school_marking_periods WHERE start_date<='{$date}' AND end_date>='{$date}' AND mp='QTR') AND school_date<='{$date}') AS INT) FOR 1) IN cpsp.DAYS)>0 AND s.MARKING_PERIOD_ID IN ({$all_mp}) ORDER BY s.START_DATE ASC"; } else { $current_schedule_Q = "SELECT cpsp.PERIOD_ID,cp.COURSE_PERIOD_ID,cp.HALF_DAY FROM SCHEDULE s,COURSE_PERIODS cp, COURSE_PERIOD_SCHOOL_PERIODS cpsp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND s.STUDENT_ID='__student_id__' AND s.SYEAR='" . UserSyear() . "' AND s.SCHOOL_ID='" . UserSchool() . "' AND cp.COURSE_PERIOD_ID = s.COURSE_PERIOD_ID AND position(',{$_REQUEST['table']},' IN cp.DOES_ATTENDANCE)>0 AND ('{$date}' BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND '{$date}'>=s.START_DATE)) AND position(substring('UMTWHFS' FROM cast(extract(DOW FROM cast('{$date}' AS DATE)) AS INT)+1 FOR 1) IN cpsp.DAYS)>0 AND s.MARKING_PERIOD_ID IN ({$all_mp}) ORDER BY s.START_DATE ASC"; }
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']; }
echo '<tr><td>' . $letter_text . '</td></tr>'; echo "<tr><td colspan=2 style=\"height:18px;\"> </td></tr>"; echo "</table>"; echo "<div style=\"page-break-before: always;\"></div>"; } else { unset($_CENTRE['DrawHeader']); echo "<tr><td colspan=2 style=\"height:18px\"></td></tr>"; echo "<table width=100% style=\" font-family:Arial; font-size:12px;\" >"; echo "<tr><td style=\"font-size:15px; font-weight:bold; padding-top:20px;\">" . GetSchool(UserSchool()) . "<div style=\"font-size:12px;\">Student Letter</div></td><td align=right style=\"padding-top:20px;\">" . ProperDate(DBDate()) . "<br \\>Powered by openSIS</td></tr><tr><td colspan=2 style=\"border-top:1px solid #333;\"> </td></tr></table>"; echo '<table border=0 style=\\" font-family:Arial; font-size:12px;\\">'; echo '<tr>'; echo '<td>' . $student['FULL_NAME'] . ', #' . $student['STUDENT_ID'] . '</td></tr>'; echo '<tr>'; echo '<td>' . $student['GRADE_ID'] . ' Grade</td></tr>'; echo '<tr>'; echo '<td>Course: ' . $course_title . "" . GetMP(GetCurrentMP('QTR', DBDate())) . '</td></tr>'; echo '</table>'; echo '<br>'; $letter_text = $_REQUEST['letter_text']; foreach ($student as $column => $value) { $letter_text = str_replace('__' . $column . '__', $value, $letter_text); } echo "<tr><td colspan=2 style=\"height:18px\"></td></tr>"; echo '<tr><td>' . $letter_text . '</td></tr>'; echo "<tr><td colspan=2 style=\"height:18px;\"> </td></tr>"; echo "</table>"; echo "<div style=\"page-break-before: always;\"></div>"; } } PDFStop($handle); } else {
DBQuery($sql); } } if ($asso_err) { $err_msg = 'Start Date or End Date cannot be changed because marking period has association'; } //---------------------------------------------------------------------------------------------------------------------- if ($go) { $UserMp = GetCurrentMP('QTR', DBDate()); $_SESSION['UserMP'] = $UserMp; if (!$UserMp) { $UserMp = GetCurrentMP('SEM', DBDate()); $_SESSION['UserMP'] = $UserMp; } if (!$UserMp) { $UserMp = GetCurrentMP('FY', DBDate()); $_SESSION['UserMP'] = $UserMp; } } //--------------------------------------------------------------------------------------------------------------------------- } // ---------------------- Insert & Update End ------------------------------ // unset($_REQUEST['tables']); unset($_SESSION['_REQUEST_vars']['tables']); } if (clean_param($_REQUEST['modfunc'], PARAM_ALPHAMOD) == 'delete') { $extra = array(); switch ($table) { case 'school_years': $name = 'year'; $parent_term = '';
foreach ($_REQUEST['period'] as $period_id => $yes) { $periods_list .= ",'" . $period_id . "'"; } $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 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')); $state_code = DBGet(DBQuery("SELECT STATE_CODE FROM ATTENDANCE_CODES WHERE ID='{$_REQUEST['absence_code']}'")); $state_code = $state_code[1]['STATE_CODE']; foreach ($_REQUEST['student'] as $student_id => $yes) { foreach ($_REQUEST['dates'] as $date => $yes) { $daysWeek = _('SuMoTuWeThFrSa'); /// Days of the week $current_mp = GetCurrentMP('QTR', $date); $all_mp = GetAllMP('QTR', $current_mp); $course_periods_RET = DBGet(DBQuery("SELECT s.COURSE_PERIOD_ID,cp.PERIOD_ID,cp.HALF_DAY FROM SCHEDULE s,COURSE_PERIODS cp,ATTENDANCE_CALENDAR ac,SCHOOL_PERIODS sp WHERE sp.PERIOD_ID=cp.PERIOD_ID AND ac.SCHOOL_DATE='{$date}' AND ac.CALENDAR_ID=cp.CALENDAR_ID AND (ac.BLOCK=sp.BLOCK OR sp.BLOCK IS NULL) AND s.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND s.STUDENT_ID='{$student_id}' AND cp.PERIOD_ID IN {$periods_list} AND position(',0,' IN cp.DOES_ATTENDANCE)>0 AND (ac.SCHOOL_DATE BETWEEN s.START_DATE AND s.END_DATE OR (s.END_DATE IS NULL AND ac.SCHOOL_DATE>=s.START_DATE)) AND position(substring('{$daysWeek}' FROM cast(extract(DOW FROM ac.SCHOOL_DATE) AS INT)*2+1 FOR 2) IN cp.DAYS)>0 AND s.MARKING_PERIOD_ID IN ({$all_mp})"), array(), array('PERIOD_ID')); //echo '<pre>'; var_dump($course_periods_RET); echo '</pre>'; foreach ($_REQUEST['period'] as $period_id => $yes) { $course_period_id = $course_periods_RET[$period_id][1]['COURSE_PERIOD_ID']; if ($course_period_id && !($course_periods_RET[$period_id][1]['COURSE_PERIOD_ID'] == 'Y' && $state_code == 'H')) { if (!$current_RET[$student_id][$date][$period_id]) { $sql = "INSERT INTO ATTENDANCE_PERIOD (STUDENT_ID,SCHOOL_DATE,PERIOD_ID,MARKING_PERIOD_ID,COURSE_PERIOD_ID,ATTENDANCE_CODE,ATTENDANCE_REASON,ADMIN)\n\t\t\t\t\t\t\t\t\t\tvalues('{$student_id}','{$date}','{$period_id}','{$current_mp}','{$course_period_id}','{$_REQUEST['absence_code']}','{$_REQUEST['absence_reason']}','Y')"; DBQuery($sql); } else { $sql = "UPDATE ATTENDANCE_PERIOD SET ATTENDANCE_CODE='{$_REQUEST['absence_code']}',ATTENDANCE_REASON='{$_REQUEST['absence_reason']}',ADMIN='Y',COURSE_PERIOD_ID='{$course_period_id}'\n\t\t\t\t\t\t\t\tWHERE STUDENT_ID='{$student_id}' AND SCHOOL_DATE='{$date}' AND PERIOD_ID='{$period_id}'"; DBQuery($sql); } } }
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; } }
echo "<FORM action=Modules.php?modname={$_REQUEST['modname']}&table={$_REQUEST['table']} method=POST>"; DrawHeader(PrepareDate($date, '_date', false, array('submit' => true))); echo '</FORM>'; ErrorMessage(array('<IMG SRC=assets/x.gif>' . _('This period does not meet on the selected date.')), 'fatal'); } // if running as a teacher program then centre[allow_edit] will already be set according to admin permissions if (!isset($_CENTRE['allow_edit'])) { $time = strtotime(DBDate('postgres')); if (GetMP($qtr_id, 'POST_START_DATE') && $time <= strtotime(GetMP($qtr_id, 'POST_END_DATE'))) { $_CENTRE['allow_edit'] = true; } } $current_RET = DBGet(DBQuery('SELECT ITEM_ID FROM FOOD_SERVICE_COMPLETED WHERE STAFF_ID=\'' . User('STAFF_ID') . '\' AND SCHOOL_DATE=\'' . $date . '\' AND PERIOD_ID=\'' . UserPeriod() . '\' AND MENU_ID=\'' . $_REQUEST['menu_id'] . '\''), array(), array('ITEM_ID')); //echo '<pre>'; var_dump($current_RET); echo '</pre>'; if ($_REQUEST['values'] && $_POST['values']) { GetCurrentMP('QTR', $date); foreach ($_REQUEST['values'] as $id => $value) { if ($current_RET[$id]) { $sql = 'UPDATE FOOD_SERVICE_COMPLETED SET '; $sql .= 'COUNT=\'' . str_replace("\\'", "''", str_replace("`", "''", $value['COUNT'])) . '\' '; $sql .= 'WHERE STAFF_ID=\'' . User('STAFF_ID') . '\' AND SCHOOL_DATE=\'' . $date . '\' AND PERIOD_ID=\'' . UserPeriod() . '\' AND MENU_ID=\'' . $_REQUEST['menu_id'] . '\' AND ITEM_ID=\'' . $id . '\''; } else { $fields = 'STAFF_ID,SCHOOL_DATE,PERIOD_ID,MENU_ID,ITEM_ID,COUNT'; $values = '\'' . User('STAFF_ID') . '\',\'' . $date . '\',\'' . UserPeriod() . '\',\'' . $_REQUEST['menu_id'] . '\',\'' . $id . '\',\'' . $value['COUNT'] . '\''; $sql = 'INSERT INTO FOOD_SERVICE_COMPLETED (' . $fields . ') values (' . $values . ')'; } DBQuery($sql); } unset($_SESSION['_REQUEST_vars']['values']); } if ($date != DBDate()) {
# 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 (isset($_REQUEST['modfunc']) && $_REQUEST['modfunc'] == 'save') { if (count($_REQUEST['st_arr'])) { //modif Francois: add Template $template_update = DBGet(DBQuery("SELECT 1 FROM TEMPLATES WHERE MODNAME = '" . $_REQUEST['modname'] . "' AND STAFF_ID = '" . User('STAFF_ID') . "'")); if (!$template_update) { DBQuery("INSERT INTO TEMPLATES (MODNAME, STAFF_ID, TEMPLATE) VALUES ('" . $_REQUEST['modname'] . "', '" . User('STAFF_ID') . "', '" . $REQUEST_honor_roll_text . "')"); } else { DBQuery("UPDATE TEMPLATES SET TEMPLATE = '" . $REQUEST_honor_roll_text . "' WHERE MODNAME = '" . $_REQUEST['modname'] . "' AND STAFF_ID = '" . User('STAFF_ID') . "'"); } $st_list = '\'' . implode('\',\'', $_REQUEST['st_arr']) . '\''; $extra['WHERE'] = " AND s.STUDENT_ID IN ({$st_list})"; $mp_RET = DBGet(DBQuery("SELECT TITLE,END_DATE FROM SCHOOL_MARKING_PERIODS WHERE MP='QTR' AND MARKING_PERIOD_ID='" . UserMP() . "'")); $school_info_RET = DBGet(DBQuery("SELECT TITLE,PRINCIPAL FROM SCHOOLS WHERE ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "'")); $extra['SELECT'] .= ",(SELECT SORT_ORDER FROM SCHOOL_GRADELEVELS WHERE ID=ssm.GRADE_ID) AS SORT_ORDER"; $extra['SELECT'] .= ",(SELECT st.FIRST_NAME||coalesce(' '||st.MIDDLE_NAME||' ',' ')||st.LAST_NAME FROM STAFF st,COURSE_PERIODS cp,SCHEDULE ss WHERE st.STAFF_ID=cp.TEACHER_ID 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 (" . GetAllMP('QTR', GetCurrentMP('QTR', DBDate(), false)) . ") AND (ss.START_DATE<='" . DBDate() . "' AND (ss.END_DATE>='" . DBDate() . "' OR ss.END_DATE IS NULL)) LIMIT 1) AS TEACHER"; $extra['ORDER_BY'] = 'SORT_ORDER DESC,FULL_NAME'; $RET = GetStuList($extra); $no_margins = array('top' => 0, 'bottom' => 0, 'left' => 0, 'right' => 0); $handle = PDFStart(false, $no_margins); //echo '<!-- MEDIA SIZE 8.5x11in -->'; $_SESSION['orientation'] = 'landscape'; foreach ($RET as $student) { //note Francois: bug: small white border at the bottom of page echo '<style type="text/css"> body {margin:0; padding:0;} div#background {background: width:1462px; height:1032px; position:relative;} div#background * {z-index:1; position:relative;}</style>'; echo '<div id="background">'; if (!empty($_REQUEST['frame'])) { echo '<img src="assets/Frames/' . $_REQUEST['frame'] . '" style="z-index:0; width:1462px; height:1032px; position:absolute;" />'; } echo '<TABLE style="margin:0 auto; height:77%;">'; $honor_roll_text = nl2br(str_replace("''", "'", str_replace(' ', ' ', $REQUEST_honor_roll_text)));