$tmp_REQUEST = $_REQUEST; unset($tmp_REQUEST['include_inactive']); echo "<FORM action=Modules.php?modname={$_REQUEST['modname']} method=POST>"; DrawHeaderHome('<INPUT type=checkbox name=include_inactive value=Y' . ($_REQUEST['include_inactive'] == 'Y' ? " CHECKED onclick='document.location.href=\"" . PreparePHP_SELF($tmp_REQUEST) . "&include_inactive=\";'" : " onclick='document.location.href=\"" . PreparePHP_SELF($tmp_REQUEST) . "&include_inactive=Y\";'") . '>Include Inactive Students'); echo '</FORM>'; $course_period_id = UserCoursePeriod(); $course_id = DBGet(DBQuery("SELECT COURSE_ID FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID='{$course_period_id}'")); $course_id = $course_id[1]['COURSE_ID']; $max_allowed = Preferences('ANOMALOUS_MAX', 'Gradebook') / 100; $extra['SELECT'] = ",ga.ASSIGNMENT_ID,gt.TITLE AS TYPE_TITLE,ga.TITLE,ga.POINTS AS TOTAL_POINTS,'' AS LETTER_GRADE"; $extra['SELECT'] .= ',(SELECT POINTS FROM GRADEBOOK_GRADES WHERE STUDENT_ID=s.STUDENT_ID AND ASSIGNMENT_ID=ga.ASSIGNMENT_ID) AS POINTS'; $extra['SELECT'] .= ',(SELECT COMMENT FROM GRADEBOOK_GRADES WHERE STUDENT_ID=s.STUDENT_ID AND ASSIGNMENT_ID=ga.ASSIGNMENT_ID) AS COMMENT'; $extra['FROM'] = ",GRADEBOOK_ASSIGNMENTS ga,GRADEBOOK_ASSIGNMENT_TYPES gt"; $extra['WHERE'] = 'AND ((SELECT POINTS FROM GRADEBOOK_GRADES WHERE STUDENT_ID=s.STUDENT_ID AND ASSIGNMENT_ID=ga.ASSIGNMENT_ID) IS NULL AND (ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR (SELECT POINTS FROM GRADEBOOK_GRADES WHERE STUDENT_ID=s.STUDENT_ID AND ASSIGNMENT_ID=ga.ASSIGNMENT_ID)<0 OR (SELECT POINTS FROM GRADEBOOK_GRADES WHERE STUDENT_ID=s.STUDENT_ID AND ASSIGNMENT_ID=ga.ASSIGNMENT_ID)>ga.POINTS*' . $max_allowed . ') AND ((ga.COURSE_ID=\'' . $course_id . '\' AND ga.STAFF_ID=\'' . User('STAFF_ID') . '\') OR ga.COURSE_PERIOD_ID=\'' . $course_period_id . '\') AND ga.MARKING_PERIOD_ID=\'' . UserMP() . '\' AND gt.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID'; $extra['functions'] = array('POINTS' => '_makePoints'); $students_RET = GetStuList($extra); //echo '<pre>'; var_dump($students_RET); echo '</pre>'; if (AllowUse('Grades/Grades.php')) { $link = array('FULL_NAME' => array('link' => "Modules.php?modname=Grades/Grades.php&include_ianctive={$_REQUEST['include_inactive']}&assignment_id=all", 'variables' => array('student_id' => 'STUDENT_ID')), 'TITLE' => array('link' => "Modules.php?modname=Grades/Grades.php&include_inactive={$_REQUEST['include_inactive']}", 'variables' => array('assignment_id' => 'ASSIGNMENT_ID', 'student_id' => 'STUDENT_ID'))); } $columns = array('FULL_NAME' => 'Name', 'STUDENT_ID' => 'Student ID', 'POINTS' => 'Problem', 'TYPE_TITLE' => 'Category', 'TITLE' => 'Assignment', 'COMMENT' => 'Comment'); ListOutput($students_RET, $columns, 'Anomalous Grade', 'Anomalous Grades', $link, array(), array('center' => false, 'save' => false, 'search' => false)); function _makePoints($value, $column) { global $THIS_RET; if ($value == '') { return '<FONT class=red>Missing</FONT>'; } elseif ($value == '-1') { return '<FONT color=#00a000>Excused</FONT>'; } elseif ($value < 0) { return '<FONT class=red>Negative!</FONT>';
$RET = DBGet(DBQuery('SELECT ID FROM attendance_codes WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' AND (DEFAULT_CODE!=\'Y\' OR DEFAULT_CODE IS NULL)')); if (count($RET)) { $extra['WHERE'] .= 'AND ac.ID IN ('; foreach ($RET as $code) { $extra['WHERE'] .= '\'' . $code['ID'] . '\','; } $extra2['WHERE'] = $extra['WHERE'] = substr($extra['WHERE'], 0, -1) . ')'; } } $extra['WHERE'] .= ')'; $extra2['WHERE'] .= ')'; $extra2['SELECT'] .= ',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,people p,people_join_contacts pjc,students_join_people sjp,students_join_address sja '; $extra2['WHERE'] .= ' AND sja.STUDENT_ID=ssm.STUDENT_ID AND sjp.STUDENT_ID=sja.STUDENT_ID AND pjc.PERSON_ID=sjp.PERSON_ID AND p.PERSON_ID=sjp.PERSON_ID AND sjp.ADDRESS_ID=a.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\') '; $extra2['group'] = array('STUDENT_ID', 'PERSON_ID'); $contacts_RET = GetStuList($extra2); $columns = array(); $extra['SELECT'] .= ',NULL AS STATE_VALUE,NULL AS PHONE'; $extra['functions']['PHONE'] = '_makePhone'; $extra['functions']['STATE_VALUE'] = '_makeStateValue'; $extra['columns_before']['PHONE'] = '' . _('Contact') . ''; $extra['columns_after']['STATE_VALUE'] = '' . _('Present') . ''; $extra['BackPrompt'] = false; $extra['Redirect'] = false; $extra['new'] = true; foreach ($periods_RET as $period) { $extra['SELECT'] .= ',\'' . '' . '\' AS PERIOD_' . $period['PERIOD_ID']; $extra['functions']['PERIOD_' . $period['PERIOD_ID']] = '_makeCodePulldown'; $extra['columns_after']['PERIOD_' . $period['PERIOD_ID']] = $period['SHORT_NAME']; } echo "<FORM action=Modules.php?modname={$_REQUEST['modname']} method=POST>";
$attendance_RET = GetStuList($extra); } if ($_REQUEST['elements']['mp_absences'] == 'Y' || $_REQUEST['elements']['ytd_absences'] == 'Y') { // GET THE DAILY ATTENDANCE unset($extra); $extra['WHERE'] = " AND s.STUDENT_ID IN ({$st_list})"; $extra['SELECT_ONLY'] .= "ad.SCHOOL_DATE,ad.MARKING_PERIOD_ID,ad.STATE_VALUE,ssm.STUDENT_ID"; $extra['FROM'] .= ",ATTENDANCE_DAY ad"; $extra['WHERE'] .= " AND ad.STUDENT_ID=ssm.STUDENT_ID AND ad.SYEAR=ssm.SYEAR AND (ad.STATE_VALUE='0.0' OR ad.STATE_VALUE='.5') AND ad.SCHOOL_DATE<='" . GetMP($last_mp, 'END_DATE') . "'"; $extra['group'][] = 'STUDENT_ID'; $extra['group'][] = 'MARKING_PERIOD_ID'; //Widgets('course'); // mab - same as above //Widgets('gpa'); //Widgets('class_rank'); //Widgets('letter_grade'); $attendance_day_RET = GetStuList($extra); } if (count($RET)) { $columns = array('FULL_NAME' => _('Student'), 'COURSE_TITLE' => _('Course')); if ($_REQUEST['elements']['teacher'] == 'Y') { $columns += array('TEACHER' => _('Teacher')); } if ($_REQUEST['elements']['period_absences'] == 'Y') { $columns['ABSENCES'] = _('Abs<BR />YTD / MP'); } foreach ($_REQUEST['mp_arr'] as $mp) { if ($_REQUEST['elements']['percents'] == 'Y') { $columns[$mp . '%'] = '%'; } $columns[$mp] = GetMP($mp); }
$start2 = $startr; $end2 = $endr; } //echo "$totalrows"; unset($extra); $extra['SELECT_ONLY'] .= 'ap.COURSE_PERIOD_ID, s.STUDENT_ID, s.FIRST_NAME, s.LAST_NAME, ap.SCHOOL_DATE, cp.TITLE, cp.SHORT_NAME, ap.PERIOD_ID, sc.START_DATE, sc.END_DATE '; $extra['FROM'] .= ' ,attendance_period ap, course_periods cp, schedule sc '; //$extra['WHERE'] .= " AND ssm.student_id=s.student_id AND ap.STUDENT_ID=s.STUDENT_ID AND ap.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND ('".DBDate()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL) "; //$extra['WHERE'] .= " AND ssm.student_id=s.student_id AND ap.STUDENT_ID=s.STUDENT_ID AND ap.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID "; $extra['WHERE'] .= ' AND ap.STUDENT_ID=s.STUDENT_ID AND sc.STUDENT_ID=s.STUDENT_ID AND ap.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND ap.COURSE_PERIOD_ID = sc.COURSE_PERIOD_ID AND (sc.END_DATE > \'' . date('Y-m-d') . ' \' OR sc.END_DATE IS NULL OR sc.END_DATE=\'0000-00-00\' ) '; $extra['ORDER_BY'] = ' STUDENT_ID, COURSE_PERIOD_ID, SCHOOL_DATE'; Widgets('course'); Widgets('gpa'); Widgets('class_rank'); Widgets('letter_grade'); $result1 = GetStuList($extra); DrawBC("Attendance > " . ProgramTitle()); echo "{$delete_message}"; //echo "<form action=Modules.php?modname=Attendance/DuplicateAttendance.php&modfunc=&search_modfunc=list&next_modname=Attendance/DuplicateAttendance.php&_openSIS_PDF=true method=POST>"; echo "<form action=Modules.php?modname=attendance/DuplicateAttendance.php&modfunc=&search_modfunc=list&next_modname=attendance/DuplicateAttendance.php&delete=true method=POST>"; #DrawHeader('',SubmitButton(Delete,'','class=btn_medium')); $num_rows = $totalrows; if ($num_rows > $rows_per_page) { $totalpages = $num_rows / $rows_per_page; $totalpages = ceil($totalpages); echo "<center><small>Page:</small> "; $first = 0; $ii = 1; for ($i = 0; $i < $totalpages; $i++) { if ($urlpage == $ii) { echo "<b>{$ii}</b> ";
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']); }
ListOutput($standards_ret, $standards_columns, 'Effort', 'Efforts', false, false, array('search' => false, 'sort' => false)); echo '<br>'; if ($grade_status != 'closed' && $grade_status != 'not_set') { echo "<br><CENTER><INPUT class='btn_medium' type=submit value=Save onclick=formload_ajax('popform') > <INPUT class='btn_medium' type=button value=Close onclick='window.close()'></CENTER>"; } echo "</FORM>"; } else { echo "No effort found"; } } else { echo '<table align="" style="margin:5px 0 0 15px; width:100%"><tr><td>'; echo $mps_select; echo '</td></tr></table>'; $extra['SELECT'] .= ",'' AS EFFORT "; $extra['functions'] = array('EFFORT' => '_makeStandards'); $all_students = GetStuList($extra); $columns = array('FULL_NAME' => 'Student', 'STUDENT_ID' => 'Student ID', 'EFFORT' => 'Effort Grades'); //print_r($all_students); echo "<center>"; PopTable_wo_header('header'); echo '<div style="width:800px; overflow-x:scroll;">'; ListOutput($all_students, $columns, 'Student', 'Students', false, false, array('yscroll' => true)); echo '</div>'; PopTable('footer'); echo "</center>"; } function _makeStandards($value, $column) { // global $THIS_RET,$current_RET,$import_comments_RET,$course_RET,$tabindex; global $THIS_RET; $standard_exists = DBGet(DBQuery("SELECT COUNT(*) AS TOTAL FROM student_efforts WHERE STUDENT_ID=" . $THIS_RET['STUDENT_ID'] . " AND MARKING_PERIOD_ID='" . $_REQUEST['mp'] . "' "));
# This program is released under the terms of the GNU General Public License as # published by the Free Software Foundation, version 2 of the License. # See license.txt. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program. If not, see <http://www.gnu.org/licenses/>. # #*************************************************************************************** include '../../Redirect_modules.php'; DrawBC("" . _('Attendance') . " > " . ProgramTitle()); $message = '<TABLE><TR><TD colspan=7 align=center>' . _('From') . ' ' . PrepareDate(DBDate(), '_min') . ' ' . _('to') . ' ' . PrepareDate(DBDate(), '_max') . '</TD></TR></TABLE>'; if (Prompt_Home(_('Confirm'), _('When do you want to recalculate the daily attendance?'), $message)) { $current_RET = DBGet(DBQuery('SELECT DISTINCT DATE_FORMAT(SCHOOL_DATE,\'%d-%m-%Y\') as SCHOOL_DATE FROM attendance_calendar WHERE SCHOOL_ID=\'' . UserSchool() . '\' AND SYEAR=\'' . UserSyear() . '\''), array(), array('SCHOOL_DATE')); $students_RET = GetStuList(); $begin = mktime(0, 0, 0, MonthNWSwitch($_REQUEST['month_min'], 'to_num'), $_REQUEST['day_min'] * 1, $_REQUEST['year_min']) + 43200; $end = mktime(0, 0, 0, MonthNWSwitch($_REQUEST['month_max'], 'to_num'), $_REQUEST['day_max'] * 1, $_REQUEST['year_max']) + 43200; for ($i = $begin; $i <= $end; $i += 86400) { if ($current_RET[strtoupper(date('d-M-y', $i))]) { foreach ($students_RET as $student) { UpdateAttendanceDaily($student['STUDENT_ID'], date('d-M-y', $i)); } } } unset($_REQUEST['modfunc']); DrawHeader('<table><tr><td><IMG SRC=assets/check.gif></td><td>' . _('The Daily Attendance for that timeframe has been recalculated.') . '</td></tr></table>'); }
$extra['SELECT_ONLY'] = "ad.SCHOOL_DATE,ad.MARKING_PERIOD_ID,ad.STATE_VALUE,ssm.STUDENT_ID"; $extra['FROM'] = ",ATTENDANCE_DAY ad"; $extra['WHERE'] .= " AND ad.STUDENT_ID=ssm.STUDENT_ID AND ad.SYEAR=ssm.SYEAR AND (ad.STATE_VALUE='0.0' OR ad.STATE_VALUE='.5') AND ad.SCHOOL_DATE<='" . GetMP($last_mp, 'END_DATE') . "'"; $extra['group'] = array('STUDENT_ID', 'MARKING_PERIOD_ID'); $attendance_day_RET = GetStuList($extra); } if ($_REQUEST['mailing_labels'] == 'Y') { // GET THE ADDRESSES unset($extra); $extra['WHERE'] = " AND s.STUDENT_ID IN ({$st_list})"; $extra['SELECT'] = 's.STUDENT_ID'; Widgets('mailing_labels'); $extra['SELECT_ONLY'] = $extra['SELECT']; $extra['SELECT'] = ''; $extra['group'] = array('STUDENT_ID', 'ADDRESS_ID'); $addresses_RET = GetStuList($extra); } $comment_codes_RET = DBGet(DBQuery("SELECT TITLE,COMMENT FROM REPORT_CARD_COMMENT_CODES WHERE SCHOOL_ID='" . UserSchool() . "' ORDER BY (SELECT SORT_ORDER FROM REPORT_CARD_COMMENT_CODE_SCALES WHERE ID=SCALE_ID),SORT_ORDER,ID")); if (count($RET)) { $columns = array('COURSE_TITLE' => _('Course')); if ($_REQUEST['elements']['teacher'] == 'Y') { $columns += array('TEACHER' => _('Teacher')); } if ($_REQUEST['elements']['period_absences'] == 'Y') { //$columns += array('ABSENCES'=>_('Abs<BR />YTD / MP')); $columns += array('ABSENCES' => _('Absences')); } if (count($_REQUEST['mp_arr']) > 3) { $mp_TITLE = 'SHORT_NAME'; } else { $mp_TITLE = 'TITLE';
for ($i = $start; $i <= $end; $i++) { $index++; $chart['chart_data'][$index][$o] = 0; } } $chart['chart_data'][0][$o - 1] = $diff_max > $diff ? ceil($diff / $diff_max) * ($o - 1) : ceil($diff / $diff_max) * ($o - 2) . '+'; $mins[$o] = ceil($diff / $diff_max) * ($o - 1); $extra['SELECT_ONLY'] = "CATEGORY_" . intval($_REQUEST['category_id']) . " AS TITLE," . $timeframe . " AS TIMEFRAME"; $extra['FROM'] = ",DISCIPLINE_REFERRALS dr"; $extra['WHERE'] = " AND dr.STUDENT_ID=ssm.STUDENT_ID AND dr.SCHOOL_ID=ssm.SCHOOL_ID AND dr.ENTRY_DATE BETWEEN '{$start_date}' AND '{$end_date}' AND CATEGORY_" . intval($_REQUEST['category_id']) . " IS NOT NULL "; $extra['functions'] = array('TITLE' => '_makeNumeric'); //Widgets('all'); //modif Francois: fix Advanced Search $extra['WHERE'] .= appendSQL('', $extra); $extra['WHERE'] .= CustomFields('where'); $referrals_RET = GetStuList($extra); ksort($chart['chart_data']); } if ($_ROSARIO['SearchTerms']) { $chart['draw_text'][] = array('x' => 0, 'y' => 35, 'width' => $width + 200, 'height' => 100, 'h_align' => 'center', 'v_align' => 'top', 'rotation' => 0, 'text' => strip_tags(str_replace('<BR />', "\n", $_ROSARIO['SearchTerms'])), 'font' => 'Arial', 'color' => '000000', 'alpha' => 25, 'size' => 20); } if ($_REQUEST['chart_type'] != 'list') { //modif Francois: jqplot charts ?> <script type="text/javascript"> <?php $datacolumns = 0; $series_labels = array(); foreach ($chart['chart_data'] as $chart_data) { if ($datacolumns == 0) { $jsData .= 'var ticks = [';
} $columns = array('DAYS' => 'Days', 'DURATION' => 'Time', 'PERIOD_TITLE' => 'Period - Teacher', 'ROOM' => 'Room/Location', 'MARKING_PERIOD_ID' => 'Term', 'DAYS' => 'Days', 'COURSE_TITLE' => 'Course'); $extra['SELECT'] .= ',c.TITLE AS COURSE_TITLE,p_cp.TITLE AS PERIOD_TITLE,sg.TITLE AS GRD_LVL,sr.MARKING_PERIOD_ID,cpv.DAYS, CONCAT(sp.START_TIME, " to ", sp.END_TIME) AS DURATION,r.TITLE AS ROOM'; $extra['FROM'] .= ' LEFT OUTER JOIN schedule sr ON (sr.STUDENT_ID=ssm.STUDENT_ID),courses c, school_gradelevels sg, course_periods p_cp,course_period_var cpv,school_periods sp,rooms r '; $extra['WHERE'] .= ' AND cpv.PERIOD_ID=sp.PERIOD_ID AND p_cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID AND cpv.ROOM_ID=r.ROOM_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 ssm.GRADE_ID=sg.ID AND (\'' . $date . '\' BETWEEN sr.START_DATE AND sr.END_DATE ' . $date_extra . ')'; if ($_REQUEST['mp_id']) { $extra['WHERE'] .= ' AND sr.MARKING_PERIOD_ID IN (' . GetAllMP(GetMPTable(GetMP($_REQUEST['mp_id'], 'TABLE')), $_REQUEST['mp_id']) . ')'; } $extra['functions'] = array('MARKING_PERIOD_ID' => 'GetMP', 'DAYS' => '_makeDays'); $extra['group'] = array('STUDENT_ID'); $extra['ORDER'] = ',sp.SORT_ORDER'; if ($_REQUEST['mailing_labels'] == 'Y') { $extra['group'][] = 'ADDRESS_ID'; } Widgets('mailing_labels'); $RET_stu = GetStuList($extra); $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 . '\''; $row_mp_detail = DBGet(DBQuery($sql_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[1]['PARENT_ID'] . ''; } if ($row_mp_detail['grandparent_id'] != -1) { $mp_string .= ' or s.marking_period_id=' . $row_mp_detail[1]['GRANDPARENT_ID'] . ''; } # -------------------------- Date Function Start ------------------------------- # function cov_date($dt) { $temp_date = explode("-", $dt); $final_date = $temp_date[1] . '-' . $temp_date[2] . '-' . $temp_date[0];
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; }
function GetStuListAttn(&$extra) { global $contacts_RET, $view_other_RET, $_openSIS; if ((!$extra['SELECT_ONLY'] || strpos($extra['SELECT_ONLY'], 'GRADE_ID') !== false) && !$extra['functions']['GRADE_ID']) { $functions = array('GRADE_ID' => 'GetGrade'); } else { $functions = array(); } if ($extra['functions']) { $functions += $extra['functions']; } if (!$extra['DATE']) { $queryMP = UserMP(); $extra['DATE'] = DBDate(); } else { # $queryMP = GetCurrentMP('QTR',$extra['DATE'],false); $queryMP = UserMP(); } if ($_REQUEST['expanded_view'] == 'true') { if (!$extra['columns_after']) { $extra['columns_after'] = array(); } ############################################################################################# //Commented as it crashing for Linux due to Blank Database tables //$view_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 ('CONTACT_INFO','HOME_PHONE','GUARDIANS','ALL_CONTACTS') AND USER_ID='" . User('STAFF_ID') . "'"), array(), array('TITLE')); if (!count($view_fields_RET) && !isset($view_address_RET) && !isset($view_other_RET['CONTACT_INFO'])) { $extra['columns_after'] = array('CONTACT_INFO' => '<IMG SRC=assets/down_phone_button.gif border=0>', 'gender' => 'Gender', 'ethnicity' => 'Ethnicity', 'ADDRESS' => 'Mailing Address', 'CITY' => 'City', 'STATE' => 'State', 'ZIPCODE' => 'Zipcode') + $extra['columns_after']; $select = ',s.STUDENT_ID AS CONTACT_INFO,s.GENDER,s.ETHNICITY,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']; $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,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT']; if ($_REQUEST['include_inactive'] == 'Y') { $sql .= ',' . db_case(array("(ssm.SYEAR='" . UserSyear() . "' AND ('" . 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,STUDENT_ENROLLMENT ssm " . $extra['FROM'] . " WHERE ssm.STUDENT_ID=s.STUDENT_ID "; if ($_REQUEST['include_inactive'] == 'Y') { $sql .= " AND ssm.ID=(SELECT ID FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<='" . UserSyear() . "' ORDER BY START_DATE DESC LIMIT 1)"; } else { $sql .= " AND ssm.SYEAR='" . UserSyear() . "' AND ('" . 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,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT']; if ($_REQUEST['include_inactive'] == 'Y') { $sql .= ',' . db_case(array("('" . $extra['DATE'] . "'>=ssm.START_DATE AND ('" . $extra['DATE'] . "'<=ssm.END_DATE OR ssm.END_DATE IS NULL))", 'true', "'<FONT color=green>Active</FONT>'", "'<FONT color=red>Inactive</FONT>'")) . ' AS ACTIVE'; $sql .= ',' . db_case(array("('" . $extra['DATE'] . "'>=ss.START_DATE AND ('" . $extra['DATE'] . "'<=ss.END_DATE OR ss.END_DATE IS NULL))", 'true', "'<FONT color=green>Active</FONT>'", "'<FONT color=red>Inactive</FONT>'")) . ' AS ACTIVE_SCHEDULE'; } } $sql .= " FROM STUDENTS s,COURSE_PERIODS cp,SCHEDULE ss,STUDENT_ENROLLMENT ssm " . $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 ('" . $extra['DATE'] . "'>=ssm.START_DATE AND ('" . $extra['DATE'] . "'<=ssm.END_DATE OR ssm.END_DATE IS NULL))"; $sql .= " AND ('" . $extra['DATE'] . "'>=ss.START_DATE AND ('" . $extra['DATE'] . "'<=ss.END_DATE OR ss.END_DATE IS NULL))"; } if (!$extra['SELECT_ONLY'] && $_REQUEST['include_inactive'] == 'Y') { $extra['columns_after']['ACTIVE'] = 'School Status'; $extra['columns_after']['ACTIVE_SCHEDULE'] = 'Course Status'; } break; case 'parent': case 'student': $sql = 'SELECT '; if ($extra['SELECT_ONLY']) { $sql .= $extra['SELECT_ONLY']; } else { if (Preferences('NAME') == 'Common') { $sql .= "CONCAT(s.LAST_NAME,', ',coalesce(s.COMMON_NAME,s.FIRST_NAME)) AS FULL_NAME,"; } else { $sql .= "CONCAT(s.LAST_NAME,', ',s.FIRST_NAME,' ',COALESCE(s.MIDDLE_NAME,' ')) AS FULL_NAME,"; } $sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT']; } $sql .= " FROM STUDENTS s,STUDENT_ENROLLMENT ssm " . $extra['FROM'] . "\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 = appendSQL($sql, $extra); $sql .= $extra['WHERE'] . ' '; $sql .= CustomFields('where'); if ($extra['GROUP']) { $sql .= ' GROUP BY ' . $extra['GROUP']; } if (!$extra['ORDER_BY'] && !$extra['SELECT_ONLY']) { if (Preferences('SORT') == 'Grade') { $sql .= " ORDER BY (SELECT SORT_ORDER FROM SCHOOL_GRADELEVELS WHERE ID=ssm.GRADE_ID),FULL_NAME"; } else { $sql .= " ORDER BY FULL_NAME"; } $sql .= $extra['ORDER']; } elseif ($extra['ORDER_BY']) { $sql .= ' ORDER BY ' . $extra['ORDER_BY']; } if ($extra['DEBUG'] === true) { echo '<!--' . $sql . '-->'; } return DBGet(DBQuery($sql), $functions, $extra['group']); }
if (!$_REQUEST['LO_sort']) { $_REQUEST['LO_sort'] = 'DATE'; } //$RET = DBGet(DBQuery("SELECT s.LAST_NAME||', '||s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ') AS FULL_NAME,f.AMOUNT AS DEBIT,'' AS CREDIT,f.TITLE||' '||COALESCE(f.COMMENTS,' ') AS EXPLANATION,f.ASSIGNED_DATE AS DATE,f.ID AS ID FROM BILLING_FEES f,STUDENTS s WHERE f.STUDENT_ID=s.STUDENT_ID AND f.SYEAR='".UserSyear()."' AND f.SCHOOL_ID='".UserSchool()."' AND f.ASSIGNED_DATE BETWEEN '$start_date' AND '$end_date' UNION SELECT s.LAST_NAME||', '||s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ') AS FULL_NAME,'' AS DEBIT,p.AMOUNT AS CREDIT,COALESCE(p.COMMENTS,' ') AS EXPLANATION,p.PAYMENT_DATE AS DATE,p.ID AS ID FROM BILLING_PAYMENTS p,STUDENTS s WHERE p.STUDENT_ID=s.STUDENT_ID AND p.SYEAR='".UserSyear()."' AND p.SCHOOL_ID='".UserSchool()."' AND p.PAYMENT_DATE BETWEEN '$start_date' AND '$end_date' ORDER BY DATE"),$functions); //Widgets('all'); $extra['functions'] = array('DEBIT' => '_makeCurrency', 'CREDIT' => '_makeCurrency', 'DATE' => 'ProperDate'); $fees_extra = $extra; $fees_extra['SELECT'] .= ",f.AMOUNT AS DEBIT,'' AS CREDIT,f.TITLE||' '||COALESCE(f.COMMENTS,' ') AS EXPLANATION,f.ASSIGNED_DATE AS DATE,f.ID AS ID"; $fees_extra['FROM'] .= ',BILLING_FEES f'; $fees_extra['WHERE'] .= " AND f.STUDENT_ID=s.STUDENT_ID AND f.SYEAR=ssm.SYEAR AND f.SCHOOL_ID=ssm.SCHOOL_ID AND f.ASSIGNED_DATE BETWEEN '{$start_date}' AND '{$end_date}'"; $RET = GetStuList($fees_extra); $payments_extra = $extra; $payments_extra['SELECT'] .= ",'' AS DEBIT,p.AMOUNT AS CREDIT,COALESCE(p.COMMENTS,' ') AS EXPLANATION,p.PAYMENT_DATE AS DATE,p.ID AS ID"; $payments_extra['FROM'] .= ',BILLING_PAYMENTS p'; $payments_extra['WHERE'] .= " AND p.STUDENT_ID=s.STUDENT_ID AND p.SYEAR=ssm.SYEAR AND p.SCHOOL_ID=ssm.SCHOOL_ID AND p.PAYMENT_DATE BETWEEN '{$start_date}' AND '{$end_date}'"; $payments_RET = GetStuList($payments_extra); foreach ($payments_RET as $payment) { $RET[] = $payment; } $columns = array('FULL_NAME' => _('Student'), 'DEBIT' => _('Fee'), 'CREDIT' => _('Payment'), 'DATE' => _('Date'), 'EXPLANATION' => _('Comment')); $link['add']['html'] = array('FULL_NAME' => '<B>' . _('Total') . '</B>', 'DEBIT' => '<b>' . Currency($totals['DEBIT']) . '</b>', 'CREDIT' => '<b>' . Currency($totals['CREDIT']) . '</b>', 'DATE' => ' ', 'EXPLANATION' => ' '); ListOutput($RET, $columns, 'Transaction', 'Transactions', $link); //$payments_RET = DBGet(DBQuery("SELECT s.LAST_NAME||', '||s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ') AS FULL_NAME,'' AS DEBIT,p.AMOUNT AS CREDIT,COALESCE(p.COMMENTS,' ') AS EXPLANATION,p.PAYMENT_DATE AS DATE FROM BILLING_PAYMENTS p,STUDENTS s WHERE p.STUDENT_ID=s.STUDENT_ID AND p.SYEAR='".UserSyear()."' AND p.SCHOOL_ID='".UserSchool()."' AND p.ASSIGNED_DATE BETWEEN '$start_date' AND '$end_date'")); function _makeCurrency($value, $column) { global $totals; $totals[$column] += $value; if ($value) { return Currency($value); } }
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]; } if ($sum_percent > 0) { $sum_points /= $sum_percent; } else { $sum_points = 0; } if ($_REQUEST['by_category'] == 'Y') { foreach ($grades_RET as $assignment_type_id => $grades) {
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'; } } } else { 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=\'' . 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' => ' '); } $handle = PDFstart(); $cols = 0; $rows = 0; echo "<meta charset='UTF-8'><table width=100% border=0 style=\" font-family:Arial; font-size:12px;\" >"; echo "<meta charset='UTF-8'><tr><td width=105>" . DrawLogo() . "</td><td style=\"font-size:15px; font-weight:bold; padding-top:20px;\">" . GetSchool(UserSchool()) . "<div style=\"font-size:12px;\">" . _('Student Labels') . "</div></td><td align=right style=\"padding-top:20px;\">" . ProperDate(DBDate()) . "<br \\>" . _('Powered by openSIS') . "</td></tr><tr><td colspan=3 style=\"border-top:1px solid #333;\"> </td></tr></table>"; echo '<table width="100%" border="0" cellspacing="0" cellpadding="0" style=font-family:Arial; font-size:12px;>'; foreach ($skipRET + $RET as $i => $student) { if ($cols < 1) { echo '<tr>'; }
function GetStuListAttn(&$extra) { global $contacts_RET, $view_other_RET, $_openSIS; if ((!$extra['SELECT_ONLY'] || strpos($extra['SELECT_ONLY'], 'GRADE_ID') !== false) && !$extra['functions']['GRADE_ID']) { $functions = array('GRADE_ID' => 'GetGrade'); } else { $functions = array(); } if ($extra['functions']) { $functions += $extra['functions']; } if (!$extra['DATE']) { $queryMP = UserMP(); $extra['DATE'] = DBDate(); } else { $queryMP = UserMP(); } if ($_REQUEST['expanded_view'] == 'true') { if (!$extra['columns_after']) { $extra['columns_after'] = array(); } ############################################################################################# //Commented as it crashing for Linux due to Blank Database tables ############################################################################################# $view_address_RET = DBGet(DBQuery('SELECT VALUE FROM program_user_config WHERE PROGRAM=\'StudentFieldsView\' AND TITLE=\'ADDRESS\' AND USER_ID=\'' . User('STAFF_ID') . '\'')); $view_address_RET = $view_address_RET[1]['VALUE']; $view_other_RET = DBGet(DBQuery('SELECT TITLE,VALUE FROM program_user_config WHERE PROGRAM=\'StudentFieldsView\' AND TITLE IN (\'CONTACT_INFO\',\'HOME_PHONE\',\'GUARDIANS\',\'ALL_CONTACTS\') AND USER_ID=\'' . User('STAFF_ID') . '\''), array(), array('TITLE')); if (!count($view_fields_RET) && !isset($view_address_RET) && !isset($view_other_RET['CONTACT_INFO'])) { $extra['columns_after'] = array('CONTACT_INFO' => '<IMG SRC=assets/down_phone_button.gif border=0>', 'gender' => 'Gender', 'ethnicity' => 'Ethnicity', 'ADDRESS' => 'Mailing Address', 'CITY' => 'City', 'STATE' => 'State', 'ZIPCODE' => 'Zipcode') + $extra['columns_after']; $select = ',s.STUDENT_ID AS CONTACT_INFO,s.GENDER,s.ETHNICITY,a.STREET_ADDRESS_1 as ADDRESS,a.CITY,a.STATE,a.ZIPCODE'; $extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID AND a.TYPE=\'Mail\') ' . $extra['FROM']; $functions['CONTACT_INFO'] = 'makeContactInfo'; // if gender is converted to codeds type //$functions['CUSTOM_200000000'] = 'DeCodeds'; $extra['singular'] = 'Student Address'; $extra['plural'] = 'Student Addresses'; $extra2['NoSearchTerms'] = true; $extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.STAFF_ID AS PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.RELATIONSHIP as STUDENT_RELATION,p.TITLE,s.PHONE,a.ID AS ADDRESS_ID '; $extra2['FROM'] .= ',student_address a LEFT OUTER JOIN students_join_people sjp ON (a.STUDENT_ID=sjp.STUDENT_ID AND (p.CUSTODY=\'Y\' OR sjp.IS_EMERGENCY=\'Y\')) LEFT OUTER JOIN people p ON (p.STAFF_ID=sjp.PERSON_ID) '; $extra2['WHERE'] .= ' AND a.STUDENT_ID=sjp.STUDENT_ID AND sjp.STUDENT_ID=ssm.STUDENT_ID '; $extra2['ORDER_BY'] .= 'COALESCE(p.CUSTODY,\'N\') DESC'; $extra2['group'] = array('STUDENT_ID', 'PERSON_ID'); // EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF if (!$_REQUEST['_openSIS_PDF']) { $expanded_view = $_REQUEST['expanded_view']; $_REQUEST['expanded_view'] = false; $addr = $_REQUEST['addr']; unset($_REQUEST['addr']); $contacts_RET = GetStuList($extra2); $_REQUEST['expanded_view'] = $expanded_view; $_REQUEST['addr'] = $addr; } else { unset($extra2['columns_after']['CONTACT_INFO']); } } else { if ($view_other_RET['CONTACT_INFO'][1]['VALUE'] == 'Y' && !$_REQUEST['_openSIS_PDF']) { $select .= ',NULL AS CONTACT_INFO '; $extra['columns_after']['CONTACT_INFO'] = '<IMG SRC=assets/down_phone_button.gif border=0>'; $functions['CONTACT_INFO'] = 'makeContactInfo'; $extra2 = $extra; $extra2['NoSearchTerms'] = true; $extra2['SELECT'] = ''; $extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.STAFF_ID AS PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.RELATIONSHIP AS STUDENT_RELATION,p.TITLE,s.PHONE,a.ID AS ADDRESS_ID,COALESCE(p.CUSTODY,\'N\') '; $extra2['FROM'] .= ',student_address a LEFT OUTER JOIN students_join_people sjp ON (a.STUDENT_ID=sjp.STUDENT_ID AND (p.CUSTODY=\'Y\' OR sjp.IS_EMERGENCY=\'Y\')) LEFT OUTER JOIN people p ON (p.STAFF_ID=sjp.PERSON_ID) '; $extra2['WHERE'] .= ' AND a.STUDENT_ID=sjp.STUDENT_ID AND sjp.STUDENT_ID=ssm.STUDENT_ID '; $extra2['ORDER_BY'] .= 'COALESCE(p.CUSTODY,\'N\') DESC'; $extra2['group'] = array('STUDENT_ID', 'PERSON_ID'); $extra2['functions'] = array(); $extra2['link'] = array(); // EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF $expanded_view = $_REQUEST['expanded_view']; $_REQUEST['expanded_view'] = false; $addr = $_REQUEST['addr']; unset($_REQUEST['addr']); $contacts_RET = GetStuList($extra2); $_REQUEST['expanded_view'] = $expanded_view; $_REQUEST['addr'] = $addr; } foreach ($view_fields_RET as $field) { $extra['columns_after']['CUSTOM_' . $field['ID']] = $field['TITLE']; if ($field['TYPE'] == 'date') { $functions['CUSTOM_' . $field['ID']] = 'ProperDate'; } elseif ($field['TYPE'] == 'numeric') { $functions['CUSTOM_' . $field['ID']] = 'removeDot00'; } elseif ($field['TYPE'] == 'codeds') { $functions['CUSTOM_' . $field['ID']] = 'DeCodeds'; } $select .= ',s.CUSTOM_' . $field['ID']; } if ($view_address_RET) { if ($view_address_RET == 'RESIDENCE') { $extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID AND a.TYPE=\'Home Address\') ' . $extra['FROM']; } elseif ($view_address_RET == 'MAILING') { $extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID AND a.TYPE=\'Mail\') ' . $extra['FROM']; } elseif ($view_address_RET == 'BUS_PICKUP') { $extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (a.STUDENT_ID=a.STUDENT_ID AND a.BUS_PICKUP=\'Y\') ' . $extra['FROM']; } else { $extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (a.STUDENT_ID=a.STUDENT_ID AND a.BUS_DROPOFF=\'Y\') ' . $extra['FROM']; } $extra['columns_after'] += array('ADDRESS' => ucwords(strtolower(str_replace('_', ' ', $view_address_RET))) . ' Address', 'CITY' => 'City', 'STATE' => 'State', 'ZIPCODE' => 'Zipcode'); $select .= ',a.ID AS ADDRESS_ID,a.STREET_ADDRESS_1 as ADDRESS,a.CITY,a.STATE,a.ZIPCODE,s.PHONE,ssm.STUDENT_ID AS PARENTS'; $extra['singular'] = 'Student Address'; $extra['plural'] = 'Student Addresses'; if ($view_other_RET['HOME_PHONE'][1]['VALUE'] == 'Y') { $functions['PHONE'] = 'makePhone'; $extra['columns_after']['PHONE'] = 'Home Phone'; } if ($view_other_RET['GUARDIANS'][1]['VALUE'] == 'Y' || $view_other_RET['ALL_CONTACTS'][1]['VALUE'] == 'Y') { $functions['PARENTS'] = 'makeParents'; if ($view_other_RET['ALL_CONTACTS'][1]['VALUE'] == 'Y') { $extra['columns_after']['PARENTS'] = 'Contacts'; } else { $extra['columns_after']['PARENTS'] = 'Guardians'; } } } elseif ($_REQUEST['addr'] || $extra['addr']) { $extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID) ' . $extra['FROM']; $distinct = 'DISTINCT '; } } $extra['SELECT'] .= $select; } elseif ($_REQUEST['addr'] || $extra['addr']) { $extra['FROM'] = ' LEFT OUTER JOIN student_address a ON (ssm.STUDENT_ID=a.STUDENT_ID) ' . $extra['FROM']; $distinct = 'DISTINCT '; } switch (User('PROFILE')) { case 'admin': $sql = 'SELECT '; if ($extra['SELECT_ONLY']) { $sql .= $extra['SELECT_ONLY']; } else { if (Preferences('NAME') == 'Common') { $sql .= 'CONCAT(s.LAST_NAME,\', \',coalesce(s.COMMON_NAME,s.FIRST_NAME)) AS FULL_NAME,'; } else { $sql .= 'CONCAT(s.LAST_NAME,\', \',s.FIRST_NAME,\' \',COALESCE(s.MIDDLE_NAME,\' \')) AS FULL_NAME,'; } $sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT']; if ($_REQUEST['include_inactive'] == 'Y') { $sql .= ',' . db_case(array('(ssm.SYEAR=\'' . UserSyear() . '\' AND (ssm.START_DATE IS NOT NULL AND (\'' . date('Y-m-d', strtotime($extra['DATE'])) . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL)))', 'true', "'<FONT color=green>Active</FONT>'", "'<FONT color=red>Inactive</FONT>'")) . ' AS ACTIVE '; } } $sql .= ' FROM students s,student_enrollment ssm ' . $extra['FROM'] . ' WHERE ssm.STUDENT_ID=s.STUDENT_ID '; if ($_REQUEST['include_inactive'] == 'Y') { $sql .= ' AND ssm.ID=(SELECT ID FROM student_enrollment WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<=\'' . UserSyear() . '\' ORDER BY START_DATE DESC LIMIT 1)'; } else { $sql .= ' AND ssm.SYEAR=\'' . UserSyear() . '\' AND (ssm.START_DATE IS NOT NULL AND (\'' . date('Y-m-d', strtotime($extra['DATE'])) . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL)) '; } if (UserSchool() && $_REQUEST['_search_all_schools'] != 'Y') { $sql .= ' AND ssm.SCHOOL_ID=\'' . UserSchool() . '\''; } else { // $sql .= ' AND ssm.SCHOOL_ID IN (' . GetUserSchools(UserID(), true) . ') '; $extra['columns_after']['LIST_SCHOOL_ID'] = 'School'; $functions['LIST_SCHOOL_ID'] = 'GetSchool'; } if (!$extra['SELECT_ONLY'] && $_REQUEST['include_inactive'] == 'Y') { $extra['columns_after']['ACTIVE'] = 'Status'; } break; case 'teacher': $sql = 'SELECT '; if ($extra['SELECT_ONLY']) { $sql .= $extra['SELECT_ONLY']; } else { if (Preferences('NAME') == 'Common') { $sql .= 'CONCAT(s.LAST_NAME,\', \',coalesce(s.COMMON_NAME,s.FIRST_NAME)) AS FULL_NAME,'; } else { $sql .= 'CONCAT(s.LAST_NAME,\', \',s.FIRST_NAME,\' \',COALESCE(s.MIDDLE_NAME,\' \')) AS FULL_NAME,'; } $sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT']; if ($_REQUEST['include_inactive'] == 'Y') { $sql .= ',' . db_case(array('(ssm.START_DATE IS NOT NULL AND (\'' . $extra['DATE'] . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL))', 'true', "'<FONT color=green>Active</FONT>'", "'<FONT color=red>Inactive</FONT>'")) . ' AS ACTIVE'; $sql .= ',' . db_case(array('(\'' . $extra['DATE'] . '\'>=ss.START_DATE AND (\'' . $extra['DATE'] . '\'<=ss.END_DATE OR ss.END_DATE IS NULL))', 'true', "'<FONT color=green>Active</FONT>'", "'<FONT color=red>Inactive</FONT>'")) . ' AS ACTIVE_SCHEDULE'; } } // $sql .= ' FROM students s,course_periods cp,schedule ss,student_enrollment ssm,course_period_var cpv ' . $extra['FROM'] . ' WHERE ssm.STUDENT_ID=s.STUDENT_ID AND cpv.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND cpv.ID="' . $extra['ID'] . '" AND ssm.STUDENT_ID=ss.STUDENT_ID AND ssm.SCHOOL_ID=\'' . UserSchool() . '\' AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SYEAR=cp.SYEAR AND ssm.SYEAR=ss.SYEAR AND ' . db_case(array(User('STAFF_ID'), 'cp.teacher_id', ' cp.teacher_id=' . User('STAFF_ID'), 'cp.secondary_teacher_id', ' cp.secondary_teacher_id=' . User('STAFF_ID'), 'cp.course_period_id IN(SELECT course_period_id from teacher_reassignment tra WHERE cp.course_period_id=tra.course_period_id AND tra.pre_teacher_id=' . User('STAFF_ID') . ')')) . ' AND cp.COURSE_PERIOD_ID=\'' . UserCoursePeriod() . '\' AND cp.COURSE_ID=ss.COURSE_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID'; if ($extra['cpvdate'] != '') { $sql .= $extra['cpvdate']; } if ($_REQUEST['include_inactive'] == 'Y') { $sql .= ' AND ssm.ID=(SELECT ID FROM student_enrollment WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR=ssm.SYEAR ORDER BY START_DATE DESC LIMIT 1)'; $sql .= ' AND ss.START_DATE=(SELECT START_DATE FROM schedule WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR=ssm.SYEAR AND MARKING_PERIOD_ID IN (' . GetAllMP('', $queryMP) . ') AND COURSE_ID=cp.COURSE_ID AND COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID ORDER BY START_DATE DESC LIMIT 1)'; } else { $sql .= ' AND (ssm.START_DATE IS NOT NULL AND \'' . $extra['DATE'] . '\'>=ssm.START_DATE AND (\'' . $extra['DATE'] . '\'<=ssm.END_DATE OR ssm.END_DATE IS NULL))'; $sql .= ' AND (\'' . $extra['DATE'] . '\'>=ss.START_DATE AND (\'' . $extra['DATE'] . '\'<=ss.END_DATE OR ss.END_DATE IS NULL))'; } if (!$extra['SELECT_ONLY'] && $_REQUEST['include_inactive'] == 'Y') { $extra['columns_after']['ACTIVE'] = 'School Status'; $extra['columns_after']['ACTIVE_SCHEDULE'] = 'Course Status'; } break; case 'parent': case 'student': $sql = 'SELECT '; if ($extra['SELECT_ONLY']) { $sql .= $extra['SELECT_ONLY']; } else { if (Preferences('NAME') == 'Common') { $sql .= 'CONCAT(s.LAST_NAME,\', \',coalesce(s.COMMON_NAME,s.FIRST_NAME)) AS FULL_NAME,'; } else { $sql .= 'CONCAT(s.LAST_NAME,\', \',s.FIRST_NAME,\' \',COALESCE(s.MIDDLE_NAME,\' \')) AS FULL_NAME,'; } $sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT']; } $sql .= ' FROM students s,student_enrollment ssm ' . $extra['FROM'] . ' WHERE ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=\'' . UserSchool() . '\' AND (\'' . DBDate() . '\' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL AND \'' . DBDate() . '\'>ssm.START_DATE)) AND ssm.STUDENT_ID' . ($extra['ASSOCIATED'] ? ' IN (SELECT STUDENT_ID FROM students_join_users WHERE STAFF_ID=\'' . $extra['ASSOCIATED'] . '\')' : '=\'' . UserStudentID() . '\''); break; default: exit('Error'); } $sql = appendSQL($sql, $extra); $sql .= $extra['WHERE'] . ' '; $sql .= CustomFields('where'); if ($extra['GROUP']) { $sql .= ' GROUP BY ' . $extra['GROUP']; } if (!$extra['ORDER_BY'] && !$extra['SELECT_ONLY']) { if (Preferences('SORT') == 'Grade') { $sql .= ' ORDER BY (SELECT SORT_ORDER FROM school_gradelevels WHERE ID=ssm.GRADE_ID),FULL_NAME'; } else { $sql .= ' ORDER BY FULL_NAME'; } $sql .= $extra['ORDER']; } elseif ($extra['ORDER_BY']) { $sql .= ' ORDER BY ' . $extra['ORDER_BY']; } if ($extra['DEBUG'] === true) { echo '<!--' . $sql . '-->'; } return DBGet(DBQuery($sql), $functions, $extra['group']); }
$sql = 'UPDATE eligibility SET ELIGIBILITY_CODE=\'' . $value . '\' WHERE SCHOOL_DATE BETWEEN \'' . $start_date . '\' AND \'' . $end_date . '\' AND PERIOD_ID=\'' . UserPeriod() . '\' AND STUDENT_ID=\'' . $student_id . '\''; } else { $sql = 'INSERT INTO eligibility (STUDENT_ID,SCHOOL_DATE,SYEAR,PERIOD_ID,COURSE_PERIOD_ID,ELIGIBILITY_CODE) values(\'' . $student_id . '\',\'' . DBDate() . '\',\'' . UserSyear() . '\',\'' . UserPeriod() . '\',\'' . $course_period_id . '\',\'' . $value . '\')'; } DBQuery($sql); } $RET = DBGet(DBQuery('SELECT \'completed\' AS COMPLETED FROM eligibility_completed WHERE STAFF_ID=\'' . User('STAFF_ID') . '\' AND SCHOOL_DATE BETWEEN \'' . $start_date . '\' AND \'' . $end_date . '\' AND PERIOD_ID=\'' . UserPeriod() . '\'')); if (!count($RET)) { DBQuery('INSERT INTO eligibility_completed (STAFF_ID,SCHOOL_DATE,PERIOD_ID) values(\'' . User('STAFF_ID') . '\',\'' . DBDate() . '\',\'' . UserPeriod() . '\')'); } $current_RET = DBGet(DBQuery('SELECT ELIGIBILITY_CODE,STUDENT_ID FROM eligibility WHERE SCHOOL_DATE BETWEEN \'' . $start_date . '\' AND \'' . $end_date . '\' AND PERIOD_ID=\'' . UserPeriod() . '\''), array(), array('STUDENT_ID')); } $extra['SELECT'] .= ",'' AS PASSING,'' AS BORDERLINE,'' AS FAILING,'' AS INCOMPLETE"; $extra['functions'] = array('PASSING' => 'makeRadio', 'BORDERLINE' => 'makeRadio', 'FAILING' => 'makeRadio', 'INCOMPLETE' => 'makeRadio'); $columns = array('PASSING' => '' . _('Passing') . '', 'BORDERLINE' => '' . _('Borderline') . '', 'FAILING' => '' . _('Failing') . '', 'INCOMPLETE' => '' . _('Incomplete') . ''); $stu_RET = GetStuList($extra); echo "<FORM ACTION=Modules.php?modname={$_REQUEST['modname']} method=POST>"; DrawHeaderHome(ProgramTitle()); if ($today > $END_DAY || $today < $START_DAY || $today == $START_DAY && date('Gi') < $START_HOUR . $START_MINUTE || $today == $END_DAY && date('Gi') > $END_HOUR . $END_MINUTE) { if ($START_HOUR > 12) { $START_HOUR -= 12; $START_M = 'PM'; } else { $START_M = 'AM'; } if ($END_HOUR > 12) { $END_HOUR -= 12; $END_M = 'PM'; } else { $END_M = 'AM'; }
//$extra['WHERE'] = " AND $email_column IS NOT NULL"; $extra['WHERE'] .= " AND NOT EXISTS (SELECT '' FROM STUDENTS_JOIN_USERS sju,STAFF st WHERE sju.STUDENT_ID=s.STUDENT_ID AND st.STAFF_ID=sju.STAFF_ID AND SYEAR='" . UserSyear() . "')"; $extra['link'] = array('FULL_NAME' => false); $extra['functions'] = array('CHECKBOX' => '_makeChooseCheckbox', 'CONTACT' => '_makeContactSelect'); $extra['columns_before'] = array('CHECKBOX' => '</A><INPUT type=checkbox value=Y name=controller onclick="checkAll(this.form,this.form.controller.checked,\'student\');"><A>'); $extra['columns_after'] = array('EMAIL' => _('Email'), 'CONTACT' => _('Contact')); $extra['LO_group'] = $extra['group'] = array('EMAIL'); $extra['addr'] = true; $extra['SELECT'] .= ",a.ADDRESS_ID"; $extra['WHERE'] .= " AND sam.RESIDENCE='Y'"; if (!UserStudentID()) { Search('student_id', $extra); } else { DrawHeader('', $extra['header_right']); $extra['WHERE'] .= " AND s.STUDENT_ID='" . UserStudentID() . "'"; $LO_ret = GetStuList($extra); $LO_columns = $extra['columns_before'] + array('FULL_NAME' => _('Student'), 'STUDENT_ID' => _('Centre ID'), 'GRADE_ID' => _('Grade')) + $extra['columns_after']; ListOutput($LO_ret, $LO_columns, _('Student'), _('Students'), $extra['link'], $extra['LO_group']); } if ($_REQUEST['search_modfunc'] == 'list' || UserStudentID()) { echo '<BR><CENTER>' . SubmitButton('Create Parent Accounts for Selected Students') . '</CENTER>'; echo "</FORM>"; } } function _makeChooseCheckbox($value, $title) { global $THIS_RET; if (strpos($THIS_RET['EMAIL'], '@')) { return "<INPUT type=checkbox name=student[{$value}] value={$value}>"; } else { return '';