//                        echo '</TR>';
        //                        $i++;
        //
        //                            }
        //		}
        //echo '</TR></TABLE>';
        //                if(!$staff['PROFILE']) echo '<FONT color=red>Schools</FONT>';
        //                else echo '<FONT color='.Preferences('TITLES').'>Schools</FONT>';
    } elseif (User('PROFILE') != 'admin') {
        $i = 0;
        echo '<TABLE><TR><TD>' . _('Schools') . ' : </TD>';
        foreach ($schools_RET as $value) {
            if ($i % 3 == 0) {
                echo '</TR><TR>';
            }
            if (in_array($value['ID'], GetUserSchools($staff['STAFF_ID'])) == true) {
                echo '<TD align = center>' . $value['TITLE'] . '</TD><TD>&nbsp;</TD>';
            }
            $i++;
        }
        echo '</TR></TABLE>';
    }
    echo '</TD>';
    echo '</TR>';
}
echo '<TR>';
echo '<TD><TABLE><tr><td style=width:100px>' . _('Username') . '</td><td>:</td><td>';
echo TextInput($staff['USERNAME'], 'staff[USERNAME]', '', 'size=25 maxlength=100 class=cell_floating  onkeyup="usercheck_init(this)"');
echo '<div id="ajax_output"></div>';
echo '</TD></tr><tr><td style=width:100px>' . _('Password') . '</td><td>:</td><td>';
//for adding new user
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']);
}
function GetUserStaffList(&$extra)
{
    global $profiles_RET;
    $functions = array('PROFILE' => 'makeProfile');
    switch (User('PROFILE')) {
        case 'admin':
            $profiles_RET = DBGet(DBQuery('SELECT * FROM user_profiles'), array(), array('ID'));
            $sql = 'SELECT DISTINCT CONCAT(s.LAST_NAME,  \' \' ,s.FIRST_NAME) AS FULL_NAME,
					s.PROFILE,s.IS_DISABLE,s.PROFILE_ID,ssr.END_DATE,s.STAFF_ID ' . $extra['SELECT'] . '
                FROM
					staff s INNER JOIN staff_school_relationship ssr USING(staff_id) ' . $extra['FROM'] . ',login_authentication la
				WHERE
					(s.PROFILE_ID!=4 OR s.PROFILE_ID IS NULL) AND ssr.SYEAR=\'' . UserSyear() . '\' AND s.STAFF_ID=la.USER_ID AND la.PROFILE_ID NOT IN (3,4)';
            if (User('PROFILE_ID') == '1') {
                $sql .= ' AND s.PROFILE_ID!=0 ';
            }
            if ($_REQUEST['_search_all_schools'] != 'Y') {
                $sql .= ' AND school_id=' . UserSchool() . ' ';
            } else {
                $sql .= ' AND school_id IN(' . GetUserSchools(UserID(), true) . ') ';
            }
            if ($_REQUEST['_dis_user'] != 'Y') {
                $sql .= ' AND (s.IS_DISABLE<>\'Y\' OR  s.IS_DISABLE IS NULL) AND (ssr.END_DATE>=\'' . date('Y-m-d') . '\' OR ssr.END_DATE=\'0000-00-00\' OR ssr.END_DATE IS NULL)';
            }
            if ($_REQUEST['username']) {
                $sql .= 'AND UPPER(la.USERNAME) LIKE \'' . singleQuoteReplace("'", "\\'", strtoupper($_REQUEST['username'])) . '%\' ';
            }
            if ($_REQUEST['last']) {
                $sql .= 'AND UPPER(s.LAST_NAME) LIKE \'' . singleQuoteReplace("'", "\\'", strtoupper($_REQUEST['last'])) . '%\' ';
            }
            if ($_REQUEST['first']) {
                $sql .= 'AND UPPER(s.FIRST_NAME) LIKE \'' . singleQuoteReplace("'", "\\'", strtoupper($_REQUEST['first'])) . '%\' ';
            }
            if ($_REQUEST['profile'] == "") {
                $sql .= ' ';
            } else {
                if ($_REQUEST['profile'] == 0 || $_REQUEST['profile']) {
                    if (is_number($_REQUEST['profile']) == FALSE) {
                        $sql .= ' AND s.PROFILE=\'' . $_REQUEST['profile'] . '\' AND s.PROFILE_ID IS NULL ';
                    } else {
                        $sql .= ' AND s.PROFILE_ID=\'' . $_REQUEST['profile'] . '\' ';
                    }
                }
            }
            $sql .= $extra['WHERE'] . ' ';
            $sql .= 'ORDER BY FULL_NAME ';
            /**************************************for Back to User*************************************************************/
            if ($_SESSION['staf_search']['sql'] && $_REQUEST['return_session']) {
                $sql = $_SESSION['staf_search']['sql'];
            } else {
                if ($_REQUEST['sql_save_session_staf']) {
                    $_SESSION['staf_search']['sql'] = $sql;
                }
            }
            /***************************************************************************************************/
            if ($extra['functions']) {
                $functions += $extra['functions'];
            }
            //echo $sql;
            return DBGet(DBQuery($sql), $functions);
            break;
    }
}
    }
    $user_profs = DBGet(DBQuery('SELECT * FROM user_profiles WHERE profile = \'' . 'parent' . '\''));
    foreach ($user_profs as $user_profs_value) {
        $user_prof_options[$user_profs_value['ID']] = $user_profs_value['TITLE'];
    }
    echo SelectInput($staff['PROFILE_ID'], 'people[PROFILE_ID]', (!$staff['PROFILE'] ? '<FONT color=red>' : '') . '' . (!$staff['PROFILE'] ? '</FONT>' : ''), $user_prof_options, FALSE);
    echo '</TD></TR></TABLE>';
    echo '</TD>';
    echo '<TD>';
    $schools_RET = DBGet(DBQuery('SELECT s.ID,s.TITLE FROM schools s,staff st INNER JOIN staff_school_relationship ssr USING(staff_id) WHERE s.id=ssr.school_id AND ssr.syear=' . UserSyear() . ' AND st.staff_id=' . User('STAFF_ID')));
    unset($options);
    if (count($schools_RET) && User('PROFILE') == 'admin') {
        $i = 0;
        $_SESSION[staff_school_chkbox_id] = 0;
        if ($staff['STAFF_ID']) {
            $schools = GetUserSchools($staff['STAFF_ID']);
        }
    }
    echo '</TD>';
    echo '</TR>';
}
if ($_REQUEST['profile'] != 'none') {
    echo '<TR>';
    echo '<TD><TABLE><tr><td style=width:100px>Username</td><td>:</td><td>';
    echo TextInput($staff['USERNAME'], 'login_authentication[USERNAME]', '', 'size=25 maxlength=100 class=cell_floating  onkeyup="usercheck_init(this)"');
    echo '<div id="ajax_output"></div>';
    echo '</TD></tr><tr><td style=width:100px>Password</td><td>:</td><td>';
    //for adding new user
    if (!isset($staff['STAFF_ID'])) {
        echo TextInput(array($staff['PASSWORD'], str_repeat('*', strlen($staff['PASSWORD']))), 'login_authentication[PASSWORD]', '', "size=25 maxlength=100 class=cell_floating AUTOCOMPLETE = off onkeyup=passwordStrength(this.value);validate_password(this.value);");
    } else {
Exemple #5
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;
}
Exemple #6
0
     FROM portal_notes pn
     WHERE pn.SYEAR=\'' . UserSyear() . '\' 
         AND pn.START_DATE<=CURRENT_DATE AND (pn.END_DATE>=CURRENT_DATE OR pn.END_DATE IS NULL) 
         AND (pn.school_id IS NULL OR pn.school_id IN(' . GetUserSchools(UserID(), true) . '))
         AND (' . (User('PROFILE_ID') == '' ? ' FIND_IN_SET(\'parent\', pn.PUBLISHED_PROFILES)>0' : ' FIND_IN_SET(' . User('PROFILE_ID') . ',pn.PUBLISHED_PROFILES)>0)') . '
         ORDER BY pn.SORT_ORDER,pn.PUBLISHED_DATE DESC'), array('PUBLISHED_DATE' => 'ProperDate', 'CONTENT' => '_nl2br'));
 if (count($notes_RET)) {
     echo '<p>';
     ListOutput($notes_RET, array('PUBLISHED_DATE' => 'Date Posted', 'TITLE' => 'Title', 'CONTENT' => 'Note', 'SCHOOL' => 'School'), 'Note', 'Notes', array(), array(), array('save' => false, 'search' => false));
     echo '</p>';
 }
 $events_RET = DBGet(DBQuery('SELECT ce.TITLE,ce.DESCRIPTION,ce.SCHOOL_DATE,s.TITLE AS SCHOOL 
         FROM calendar_events ce,calendar_events_visibility cev,schools s
         WHERE ce.SCHOOL_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 30 DAY 
             AND ce.SYEAR=\'' . UserSyear() . '\'
             AND ce.school_id IN(' . GetUserSchools(UserID(), true) . ')
             AND s.ID=ce.SCHOOL_ID AND ce.CALENDAR_ID=cev.CALENDAR_ID 
             AND ' . (User('PROFILE_ID') == '' ? 'cev.PROFILE=\'parent\'' : 'cev.PROFILE_ID=' . User('PROFILE_ID')) . ' 
             ORDER BY ce.SCHOOL_DATE,s.TITLE'), array('SCHOOL_DATE' => 'ProperDate'));
 $events_RET1 = DBGet(DBQuery('SELECT ce.TITLE,ce.DESCRIPTION,ce.SCHOOL_DATE,s.TITLE AS SCHOOL 
         FROM calendar_events ce,schools s
         WHERE ce.SCHOOL_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 30 DAY 
             AND ce.SYEAR=\'' . UserSyear() . '\'
             AND s.ID=ce.SCHOOL_ID AND ce.CALENDAR_ID=0 ORDER BY ce.SCHOOL_DATE,s.TITLE'), array('SCHOOL_DATE' => 'ProperDate'));
 $event_count = count($events_RET) + 1;
 foreach ($events_RET1 as $events_RET_key => $events_RET_value) {
     $events_RET[$event_count] = $events_RET_value;
     $event_count++;
 }
 if (count($events_RET)) {
     echo '<p>';
         $admin_COMMON_WHERE .= " AND sma.STUDENT_ID=s.STUDENT_ID ";
         $_SESSION['sma'] = '1';
     }
     if ($_REQUEST['nv_day'] || $_REQUEST['nv_month'] || $_REQUEST['nv_year'] || $_REQUEST['reason'] || $_REQUEST['result'] || $_REQUEST['med_vist_comments'] || $_SESSION['smv']) {
         $admin_COMMON_FROM .= " ,student_medical_visits smv   ";
         $admin_COMMON_WHERE .= " AND smv.STUDENT_ID=s.STUDENT_ID ";
         $_SESSION['smv'] = '1';
     }
     $admin_COMMON = $admin_COMMON_FROM . $admin_COMMON_WHERE;
 }
 /////////////////////////////////// Teacher section ///////////////////////////////////
 if (User('PROFILE') == 'teacher') {
     $teacher_COMMON_FROM = " FROM students s, student_enrollment ssm, course_periods cp,\r\n\tschedule ss,student_address a ";
     $teacher_COMMON_WHERE = " WHERE a.STUDENT_ID=s.STUDENT_ID AND a.TYPE='Home Address' AND s.STUDENT_ID=ssm.STUDENT_ID AND ssm.STUDENT_ID=ss.STUDENT_ID AND ssm.SYEAR=cp.SYEAR AND ssm.SYEAR=ss.SYEAR AND cp.COURSE_ID=ss.COURSE_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND ss.MARKING_PERIOD_ID IN (" . GetAllMP('', $queryMP) . ")\r\n\t\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() . "' AND ssm.SYEAR=" . UserSyear() . " AND ssm.SCHOOL_ID=" . UserSchool() . " ";
     if ($_REQUEST['_search_all_schools'] == 'Y' || $_SESSION['_search_all'] == 1) {
         $teacher_COMMON_WHERE = " WHERE a.STUDENT_ID=s.STUDENT_ID AND a.TYPE='Home Address'  AND s.STUDENT_ID=ssm.STUDENT_ID AND ssm.STUDENT_ID=ss.STUDENT_ID AND ssm.SYEAR=cp.SYEAR AND ssm.SYEAR=ss.SYEAR AND cp.COURSE_ID=ss.COURSE_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND ss.MARKING_PERIOD_ID IN (" . GetAllMP('', $queryMP) . ")\r\n\t\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() . "' AND ssm.SYEAR=" . UserSyear() . " AND ssm.SCHOOL_ID IN (" . GetUserSchools(UserID(), true) . ") ";
         $_SESSION['_search_all'] = 1;
     } else {
         $teacher_COMMON_WHERE = " WHERE a.STUDENT_ID=s.STUDENT_ID AND a.TYPE='Home Address' AND s.STUDENT_ID=ssm.STUDENT_ID AND ssm.STUDENT_ID=ss.STUDENT_ID AND ssm.SYEAR=cp.SYEAR AND ssm.SYEAR=ss.SYEAR AND cp.COURSE_ID=ss.COURSE_ID AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND ss.MARKING_PERIOD_ID IN (" . GetAllMP('', $queryMP) . ")\r\n\t\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() . "' AND ssm.SYEAR=" . UserSyear() . " AND ssm.SCHOOL_ID=" . UserSchool() . " ";
     }
     if ($_REQUEST['mp_comment'] || $_SESSION['smc']) {
         $teacher_COMMON_FROM .= " ,student_mp_comments smc";
         $teacher_COMMON_WHERE .= " AND smc.STUDENT_ID=s.STUDENT_ID ";
         $_SESSION['smc'] = '1';
     }
     if ($_REQUEST['goal_description'] || $_REQUEST['goal_title'] || $_SESSION['g']) {
         $teacher_COMMON_FROM .= " ,student_goal g ";
         $teacher_COMMON_WHERE .= " AND g.STUDENT_ID=s.STUDENT_ID ";
         $_SESSION['g'] = '1';
     }
     if ($_REQUEST['progress_name'] || $_REQUEST['progress_description'] || $_SESSION['p']) {