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)));
