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']); }
echo '<CENTER><INPUT type=checkbox name=_search_all_schools value=Y' . (Preferences('DEFAULT_ALL_SCHOOLS') == 'Y' ? ' CHECKED' : '') . '><font color=black>Search All Schools</font></CENTER><BR>'; } echo '<CENTER>' . Buttons('Submit') . '</CENTER>'; echo '</FORM>'; PopTable('footer'); } if (!$_REQUEST['modfunc']) { if (!isset($extra)) { $extra = array(); } Widgets('user'); if ($_REQUEST['advanced'] == 'Y') { Widgets('all'); } $extra['WHERE'] .= appendSQL(''); $extra['WHERE'] .= CustomFields('where'); echo "<FORM name=ada_from id=ada_from onSubmit='return formcheck_ada_dates();' action=Modules.php?modname=" . strip_tags(trim($_REQUEST[modname])) . "&list_by_day=" . strip_tags(trim($_REQUEST[list_by_day])) . " method=POST>"; $advanced_link = " <A HREF=Modules.php?modname={$_REQUEST['modname']}&modfunc=search&list_by_day={$_REQUEST['list_by_day']}&day_start={$_REQUEST['day_start']}&day_end={$_REQUEST['day_end']}&month_start={$_REQUEST['month_start']}&month_end={$_REQUEST['month_end']}&year_start={$_REQUEST['year_start']}&year_end={$_REQUEST['year_end']}>Advanced</A>"; DrawHeaderHome('<table><tr><td>' . DateInputAY($start_date, 'start', 1) . '</td><td> - </td><td>' . DateInputAY($end_date, 'end', 2) . '</td><td> - </td><td>' . $advanced_link, ' : <INPUT type=submit value=Go class=btn_medium></td></tr></table>'); echo '</FORM>'; if ($_REQUEST['list_by_day'] == 'true') { $cal_days = 1; $search_stu = 0; if ($_REQUEST['last'] != '' || $_REQUEST['first'] != '' || $_REQUEST['stuid'] != '' || $_REQUEST['altid'] != '') { $stu_q = 'SELECT GROUP_CONCAT(STUDENT_ID) as STUDENT_ID FROM students WHERE '; $stu_q .= $_REQUEST['last'] != '' ? ' LAST_NAME=\'' . $_REQUEST['last'] . '\' OR ' : ''; $stu_q .= $_REQUEST['first'] != '' ? ' FIRST_NAME=\'' . $_REQUEST['first'] . '\' OR ' : ''; $stu_q .= $_REQUEST['stuid'] != '' ? ' STUDENT_ID=\'' . $_REQUEST['stuid'] . '\' OR ' : ''; $stu_q .= $_REQUEST['altid'] != '' ? ' ALT_ID=\'' . $_REQUEST['altid'] . '\' OR ' : ''; $stu_q = preg_replace('/ OR $/', '', $stu_q); $stu_q = DBGet(DBQuery($stu_q));
if (User('PROFILE') == 'admin') { if (!isset($extra)) { $extra = array(); } StaffWidgets('user', $extra); if ($_REQUEST['advanced'] == 'Y') { StaffWidgets('all', $extra); } } if (!$extra['NoSearchTerms']) { if ($_REQUEST['_search_all_schools'] == 'Y') { $_ROSARIO['SearchTerms'] .= '<span style="color:gray"><b>' . _('Search All Schools') . '</b></span><BR />'; } } $extra['WHERE'] .= appendStaffSQL('', array('NoSearchTerms' => $extra['NoSearchTerms'])); $extra['WHERE'] .= CustomFields('where', 'staff', array('NoSearchTerms' => $extra['NoSearchTerms'])); if (!isset($_ROSARIO['DrawHeader'])) { DrawHeader(_('Choose A User')); } $staff_RET = GetStaffList($extra); if ($extra['profile']) { // DO NOT translate those strings since they will be passed to ListOutput ultimately $options = array('admin' => 'Administrator', 'teacher' => 'Teacher', 'parent' => 'Parent', 'none' => 'No Access'); $singular = $options[$extra['profile']]; $plural = $singular . ($options[$extra['profile']] == 'none' ? '' : 's'); $columns = array('FULL_NAME' => $singular, 'STAFF_ID' => _('RosarioSIS ID')); } else { $columns = array('FULL_NAME' => _('User'), 'PROFILE' => _('Profile'), 'STAFF_ID' => _('RosarioSIS ID')); } $name_link['FULL_NAME']['link'] = 'Modules.php?modname=' . $_REQUEST['next_modname']; $name_link['FULL_NAME']['variables'] = array('staff_id' => 'STAFF_ID');
function _getResults($type, $number, $index = '') { global $num, $remote_type; $type = trim($type); $start_REQUEST = $_REQUEST; if (isset($_REQUEST['screen'][$num])) { $_REQUEST = $_REQUEST['screen'][$num]; } foreach ((array) $_REQUEST as $key => $value) { // Is array // for example: ['cust[CUSTOM_XXX]'] or ['month_cust_begin[CUSTOM_XXX]'] // => ['cust']['CUSTOM_XXX'] if (mb_strpos($key, 'cust') !== false && mb_strpos($key, '[') !== false) { $_REQUEST[mb_substr($key, 0, mb_strpos($key, '['))][mb_substr($key, mb_strpos($key, '[') + 1)] = $value; unset($_REQUEST[$key]); } } $min_max_date = DBGet(DBQuery("SELECT to_char(min(SCHOOL_DATE),'dd-MON-yy') AS MIN_DATE,\n\t\tto_char(max(SCHOOL_DATE),'dd-MON-yy') AS MAX_DATE\n\t\tFROM ATTENDANCE_CALENDAR\n\t\tWHERE SYEAR='" . UserSyear() . "'")); if (isset($_REQUEST['day_start']) && isset($_REQUEST['month_start']) && isset($_REQUEST['year_start'])) { $start_date = $_REQUEST['day_start'] . '-' . $_REQUEST['month_start'] . '-' . $_REQUEST['year_start']; } else { $start_date = $min_max_date[1]['MIN_DATE']; } if (isset($_REQUEST['day_end']) && isset($_REQUEST['month_end']) && isset($_REQUEST['year_end'])) { $end_date = $_REQUEST['day_end'] . '-' . $_REQUEST['month_end'] . '-' . $_REQUEST['year_end']; } else { $end_date = $min_max_date[1]['MAX_DATE']; } $extra = appendSQL(''); if (isset($_REQUEST['school'])) { $extra .= " AND ssm.SCHOOL_ID = '" . $_REQUEST['school'] . "' "; } $extra .= CustomFields('where'); if (isset($_REQUEST['age'])) { // We could have used AGE() function since PostgreSQL 8.4. $extra .= " AND (DATE_PART('day', CURRENT_TIMESTAMP - s.CUSTOM_200000004::timestamp) / 365.25)::int = " . $_REQUEST['age'] . ' '; } $schools = mb_substr(str_replace(",", "','", User('SCHOOLS')), 2, -2); if (isset($_REQUEST['school'])) { $extra_schools = ''; } elseif (!isset($_REQUEST['_search_all_schools']) || $_REQUEST['_search_all_schools'] != 'Y') { $extra_schools = "SCHOOL_ID='" . UserSchool() . "' AND "; } elseif ($schools) { $extra_schools = "SCHOOL_ID IN (" . $schools . ") AND "; } $array = array(); /*if(mb_substr($type,0,7)=='orchard') { $test_title = mb_substr($type,9,-6); $type = 'orchard_test'; }*/ switch ($type) { case 'present': if (!mb_strpos($extra, 'GROUP')) { $extra .= " GROUP BY ad.SCHOOL_DATE"; } $present_RET = DBGet(DBQuery("SELECT ad.SCHOOL_DATE,COALESCE(sum(ad.STATE_VALUE),0) AS STATE_VALUE\n\t\t\t\tFROM ATTENDANCE_DAY ad,STUDENT_ENROLLMENT ssm,STUDENTS s\n\t\t\t\tWHERE s.STUDENT_ID=ssm.STUDENT_ID\n\t\t\t\tAND " . str_replace('SCHOOL_ID', 'ssm.SCHOOL_ID', $extra_schools) . " ad.STUDENT_ID=ssm.STUDENT_ID\n\t\t\t\tAND ssm.SYEAR='" . UserSyear() . "'\n\t\t\t\tAND ad.SYEAR=ssm.SYEAR\n\t\t\t\tAND ad.SCHOOL_DATE BETWEEN '" . $start_date . "' AND '" . $end_date . "'\n\t\t\t\tAND (ad.SCHOOL_DATE BETWEEN ssm.START_DATE AND ssm.END_DATE\n\t\t\t\t\tOR (ssm.END_DATE IS NULL AND ssm.START_DATE <= ad.SCHOOL_DATE)) " . $extra)); foreach ((array) $present_RET as $present) { if ($index) { $array[$present[$index]] = $present['STATE_VALUE']; } else { $array[] = $present['STATE_VALUE']; } } break; case 'absent': if (!mb_strpos($extra, 'GROUP')) { $extra .= " GROUP BY ad.SCHOOL_DATE"; } $absent_RET = DBGet(DBQuery("SELECT ad.SCHOOL_DATE,COALESCE(sum(ad.STATE_VALUE-1)*-1,0) AS STATE_VALUE\n\t\t\t\tFROM ATTENDANCE_DAY ad,STUDENT_ENROLLMENT ssm,STUDENTS s\n\t\t\t\tWHERE s.STUDENT_ID=ssm.STUDENT_ID\n\t\t\t\tAND " . str_replace('SCHOOL_ID', 'ssm.SCHOOL_ID', $extra_schools) . " ad.STUDENT_ID=ssm.STUDENT_ID\n\t\t\t\tAND ssm.SYEAR='" . UserSyear() . "'\n\t\t\t\tAND ad.SYEAR=ssm.SYEAR\n\t\t\t\tAND ad.SCHOOL_DATE BETWEEN '" . $start_date . "' AND '" . $end_date . "'\n\t\t\t\tAND (ad.SCHOOL_DATE BETWEEN ssm.START_DATE AND ssm.END_DATE\n\t\t\t\t\tOR (ssm.END_DATE IS NULL AND ssm.START_DATE <= ad.SCHOOL_DATE)) " . $extra)); foreach ((array) $absent_RET as $absent) { if ($index) { $array[$absent[$index]] = $absent['STATE_VALUE']; } else { $array[] = $absent['STATE_VALUE']; } } break; case 'enrolled': if (!mb_strpos($extra, 'GROUP')) { $extra .= " GROUP BY ac.SCHOOL_DATE"; } $enrolled_RET = DBGet(DBQuery("SELECT ac.SCHOOL_DATE,count(*) AS STUDENTS\n\t\t\t\tFROM STUDENT_ENROLLMENT ssm,ATTENDANCE_CALENDAR ac,STUDENTS s\n\t\t\t\tWHERE s.STUDENT_ID=ssm.STUDENT_ID\n\t\t\t\tAND ssm.SYEAR='" . UserSyear() . "'\n\t\t\t\tAND ac.SYEAR=ssm.SYEAR\n\t\t\t\tAND ac.CALENDAR_ID=ssm.CALENDAR_ID\n\t\t\t\tAND " . str_replace('SCHOOL_ID', 'ssm.SCHOOL_ID', $extra_schools) . " ssm.SCHOOL_ID=ac.SCHOOL_ID\n\t\t\t\tAND (ac.SCHOOL_DATE BETWEEN ssm.START_DATE AND ssm.END_DATE OR\n\t\t\t\t\t(ssm.END_DATE IS NULL AND ssm.START_DATE <= ac.SCHOOL_DATE))\n\t\t\t\tAND ac.SCHOOL_DATE BETWEEN '" . $start_date . "' AND '" . $end_date . "' " . $extra)); foreach ((array) $enrolled_RET as $enrolled) { if ($index) { $array[$enrolled[$index]] = $enrolled['STUDENTS']; } else { $array[] = $enrolled['STUDENTS']; } } break; case 'student id': $student_id_RET = DBGet(DBQuery("SELECT ssm.STUDENT_ID\n\t\t\t\tFROM STUDENT_ENROLLMENT ssm,STUDENTS s\n\t\t\t\tWHERE s.STUDENT_ID=ssm.STUDENT_ID\n\t\t\t\tAND " . str_replace('SCHOOL_ID', 'ssm.SCHOOL_ID', $extra_schools) . " ssm.SYEAR='" . UserSyear() . "'\n\t\t\t\tAND ('" . DBDate() . "' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL) " . $extra)); foreach ((array) $student_id_RET as $student_id) { if ($index) { $array[$student_id[$index]] = $student_id['STUDENT_ID']; } else { $array[] = $student_id['STUDENT_ID']; } } break; /*case 'orchard_test': $schools = substr(str_replace(",","','",User('SCHOOLS')),2,-2); if($_REQUEST['school']) $extra_schools = ''; elseif($_REQUEST['_search_all_schools']!='Y') $extra_schools = " AND SCHOOL_ID='".UserSchool()."' "; elseif($schools) $extra_schools = " AND SCHOOL_ID IN (".$schools.") "; else $extra_schools = ''; $RET = DBGet(DBQuery("SELECT ssm.STUDENT_ID FROM STUDENT_ENROLLMENT ssm,STUDENTS s WHERE s.STUDENT_ID=ssm.STUDENT_ID AND ssm.SYEAR='".UserSyear()."' ".str_replace('SCHOOL_ID','ssm.SCHOOL_ID',$extra_schools)." ".$extra)); if(count($RET)) { foreach ($RET as $student) { $student_ids .= $student['STUDENT_ID'].','; } $student_ids = substr($student_ids,0,-1); $students_RET = DBGet(DBQuery("SELECT id,SCHOOL_ID FROM orchardstudent where externalid IN (".$student_ids.")",'mysql'),array(),array('SCHOOL_ID')); $remote_type = false; } else $students_RET = array(); if(count($students_RET)) { $student_ids = array(); foreach ($students_RET as $school_id=>$students) { foreach ($students as $student) $student_ids[$student['SCHOOL_ID']] .= $student['ID'].','; } foreach ($student_ids as $i=>$value) $student_ids[$i] = substr($value,0,-1); $tests_RET = DBGet(DBQuery("SELECT testid from orchardtest where name like '%$test_title%'",'mysql')); foreach ($tests_RET as $test) $test_ids .= $test['TESTID'].','; $test_ids = substr($test_ids,0,-1); if(substr($start_date,7,2)<50) $start = '20'.substr($start_date,7,2).MonthNWSwitch(substr($start_date,3,3),'tonum').substr($start_date,0,2).'000000'; else $start = '19'.substr($start_date,7,2).MonthNWSwitch(substr($start_date,3,3),'tonum').substr($start_date,0,2).'000000'; $end = '20'.substr($end_date,7,2).MonthNWSwitch(substr($end_date,3,3),'tonum').substr($end_date,0,2).'999999'; foreach ($student_ids as $school_id=>$student_ids_list) { $RET = DBGet(DBQuery("SELECT correct,total,studentid from orchardtestrecord where slgtime BETWEEN '$start' AND '$end' AND productcode IN ($test_ids) and studentid IN ($student_ids_list) AND SCHOOL_ID='$school_id' ORDER BY STUDENTID,SLGTIME ASC",'mysql')); $remote_type = false; $student_test_count = array(); foreach ($RET as $i=>$value) { if(isset($_REQUEST['test_no'])) $student_test_count[$value['STUDENTID']]++; if(isset($_REQUEST['test_no']) && in_array('0',$_REQUEST['test_no']) && $value['STUDENTID']!=$RET[($i+1)]['STUDENTID']) { if($index!='') $array[$value[$index]][] = ($value['CORRECT']*100)/$value['TOTAL']; else $array[] = ($value['CORRECT']*100)/$value['TOTAL']; } elseif(!isset($_REQUEST['test_no']) || in_array($student_test_count[$value['STUDENTID']],$_REQUEST['test_no'])) { if($index!='') $array[$value[$index]][] = ($value['CORRECT']*100)/$value['TOTAL']; else $array[] = ($value['CORRECT']*100)/$value['TOTAL']; } } } } else $array = array(); break;*/ /*case 'time on task': $schools = substr(str_replace(",","','",User('SCHOOLS')),2,-2); if($_REQUEST['school']) $extra_schools = ''; elseif($_REQUEST['_search_all_schools']!='Y') $extra_schools = " AND SCHOOL_ID='".UserSchool()."' "; elseif($schools) $extra_schools = " AND SCHOOL_ID IN (".$schools.") "; else $extra_schools = ''; $RET = DBGet(DBQuery("SELECT ssm.STUDENT_ID FROM STUDENT_ENROLLMENT ssm,STUDENTS s WHERE s.STUDENT_ID=ssm.STUDENT_ID AND ssm.SYEAR='".UserSyear()."' ".str_replace('SCHOOL_ID','ssm.SCHOOL_ID',$extra_schools)." ".$extra)); if(count($RET)) { foreach ($RET as $student) { $student_ids .= $student['STUDENT_ID'].','; } $student_ids = substr($student_ids,0,-1); $students_RET = DBGet(DBQuery("SELECT id,SCHOOL_ID FROM orchardstudent where externalid IN (".$student_ids.")",'mysql'),array(),array('SCHOOL_ID')); $remote_type = false; } else $students_RET = array(); if(count($students_RET)) { $student_ids = array(); foreach ($students_RET as $school_id=>$students) { foreach ($students as $student) $student_ids[$student['SCHOOL_ID']] .= $student['ID'].','; } foreach ($student_ids as $i=>$value) $student_ids[$i] = substr($value,0,-1); foreach ($student_ids as $school_id=>$student_ids_list) { $RET = DBGet(DBQuery("SELECT studentid,sum(tot) as tot from orchardtimeontask where studentid IN ($student_ids_list) and SCHOOL_ID='$school_id' group by studentid",'mysql')); $remote_type = false; foreach ($RET as $value) $array[] = $value['TOT']; } } else $array = array(); break;*/ } $_REQUEST = $start_REQUEST; //var_dump($array); // Screen++. $num++; return $array; }
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['GENDER']) { $extra .= " AND s.GENDER='{$_REQUEST['GENDER']}'"; } if ($_REQUEST['stuid']) { $extra .= " AND s.STUDENT_ID = '{$_REQUEST['stuid']}' "; } if ($_REQUEST['last']) { $extra .= " AND UPPER(s.LAST_NAME) LIKE '" . strtoupper($_REQUEST['last']) . "%' "; } if ($_REQUEST['first']) { $extra .= " AND UPPER(s.FIRST_NAME) LIKE '" . strtoupper($_REQUEST['first']) . "%' "; } if ($_REQUEST['ethnic']) { $extra .= " AND s.ETHNICITY = '{$_REQUEST['ethnic']}' "; } $extra .= CustomFields('where') . " GROUP BY ssm.GRADE_ID,ssm.CALENDAR_ID"; $student_days_absent = DBGet(DBQuery("SELECT ssm.GRADE_ID,ssm.CALENDAR_ID,COALESCE(sum(ad.STATE_VALUE-1)*-1,0) AS STATE_VALUE FROM ATTENDANCE_DAY ad,STUDENT_ENROLLMENT ssm,STUDENTS s WHERE s.STUDENT_ID=ssm.STUDENT_ID AND ad.STUDENT_ID=ssm.STUDENT_ID AND ssm.SYEAR='" . UserSyear() . "' AND ad.SYEAR=ssm.SYEAR AND ad.SCHOOL_DATE BETWEEN '{$start_date}' AND '{$end_date}' AND (ad.SCHOOL_DATE BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL AND ssm.START_DATE <= ad.SCHOOL_DATE)) " . $extra), array(''), array('GRADE_ID', 'CALENDAR_ID')); $student_days_possible = DBGet(DBQuery("SELECT ssm.GRADE_ID,ssm.CALENDAR_ID,'' AS DAYS_POSSIBLE,count(*) AS ATTENDANCE_POSSIBLE,count(*) AS STUDENTS,'' AS PRESENT,'' AS ABSENT,'' AS ADA,'' AS AVERAGE_ATTENDANCE,'' AS AVERAGE_ABSENT FROM STUDENT_ENROLLMENT ssm,ATTENDANCE_CALENDAR ac,STUDENTS s WHERE s.STUDENT_ID=ssm.STUDENT_ID AND ssm.SYEAR='" . UserSyear() . "' AND ac.SYEAR=ssm.SYEAR AND ac.CALENDAR_ID=ssm.CALENDAR_ID AND " . ($_REQUEST['_search_all_schools'] != 'Y' ? "ssm.SCHOOL_ID='" . UserSchool() . "' AND " : '') . " ssm.SCHOOL_ID=ac.SCHOOL_ID AND (ac.SCHOOL_DATE BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL AND ssm.START_DATE <= ac.SCHOOL_DATE)) AND ac.SCHOOL_DATE BETWEEN '{$start_date}' AND '{$end_date}' " . $extra), array('GRADE_ID' => '_make', 'STUDENTS' => '_make', 'PRESENT' => '_make', 'ABSENT' => '_make', 'ADA' => '_make', 'AVERAGE_ATTENDANCE' => '_make', 'AVERAGE_ABSENT' => '_make', 'DAYS_POSSIBLE' => '_make')); $columns = array('GRADE_ID' => 'Grade', 'STUDENTS' => 'Total Attendance', 'DAYS_POSSIBLE' => 'Days Possible', 'PRESENT' => 'Present', 'ABSENT' => 'Absent', 'ADA' => 'ADA', 'AVERAGE_ATTENDANCE' => 'Average Attendance', 'AVERAGE_ABSENT' => 'Average Absent'); $link['add']['html'] = array('GRADE_ID' => '<b>Total</b>', 'STUDENTS' => round($sum['STUDENTS'], 1), 'DAYS_POSSIBLE' => $cal_days[key($cal_days)][1]['COUNT'], 'PRESENT' => $sum['PRESENT'], 'ADA' => Percent($sum['PRESENT'] / ($sum['PRESENT'] + $sum['ABSENT'])), 'ABSENT' => $sum['ABSENT'], 'AVERAGE_ATTENDANCE' => round($sum['AVERAGE_ATTENDANCE'], 1), 'AVERAGE_ABSENT' => round($sum['AVERAGE_ABSENT'], 1)); ListOutput($student_days_possible, $columns, '', '', $link); } } function _make($value, $column) { global $THIS_RET, $student_days_absent, $cal_days, $sum, $calendars_RET; switch ($column) { case 'STUDENTS': //$sum['STUDENTS'] += $value/$cal_days[$THIS_RET['CALENDAR_ID']][1]['COUNT']; $sum['STUDENTS'] += $value; //return round($value/$cal_days[$THIS_RET['CALENDAR_ID']][1]['COUNT'],1);