function makeSchoolInput($value = '')
{
$QI = DBQuery("SELECT NAME,SCHOOL,SCHOOL_TYPE FROM SCHOOLS WHERE SCHOOL > '00' AND DISPLAY_FORMS='Y' ORDER BY " . db_case(array("SCHOOL", "'ALL'", "'2'", "'1'")) . "," . db_case(array("SCHOOL_TYPE", "'ELM'", "'1'", "'MID'", "'2'", "'HIGH'", "'3'", "'4'")) . ",NAME ");
$RET = DBReturn($QI, $count);
$return .= "<SELECT name=school>";
$return .= "<OPTION value=''>Not Specified</OPTION>";
foreach ($RET as $key => $school) {
$return .= "<OPTION value={$school['SCHOOL']}";
if ($school[SCHOOL] == $value) {
$return .= " SELECTED";
}
$return .= ">{$school['NAME']}</OPTION>";
}
$return .= "</SELECT>";
return $return;
}
<?php
if ($_REQUEST['detailed_view'] == 'true') {
$RET = DBGet(DBQuery("SELECT fst.TRANSACTION_ID AS TRANS_ID,fst.TRANSACTION_ID,fst.ACCOUNT_ID,fst.SHORT_NAME,fst.STUDENT_ID,fst.DISCOUNT,(SELECT sum(AMOUNT) FROM FOOD_SERVICE_TRANSACTION_ITEMS WHERE TRANSACTION_ID=fst.TRANSACTION_ID) AS AMOUNT,fst.BALANCE,to_char(fst.TIMESTAMP,'YYYY-MM-DD') AS DATE,to_char(fst.TIMESTAMP,'HH:MI:SS AM') AS TIME,fst.DESCRIPTION," . db_case(array('fst.STUDENT_ID', "''", 'NULL', "(SELECT LAST_NAME||', '||" . (Preferences('NAME') == 'Common' ? 'coalesce(CUSTOM_200000002,FIRST_NAME)' : 'FIRST_NAME') . " FROM STUDENTS WHERE STUDENT_ID=fst.STUDENT_ID)")) . " AS FULL_NAME," . db_case(array('fst.SELLER_ID', "''", 'NULL', "(SELECT FIRST_NAME||' '||LAST_NAME FROM STAFF WHERE STAFF_ID=fst.SELLER_ID)")) . " AS SELLER FROM FOOD_SERVICE_TRANSACTIONS fst WHERE SYEAR='" . UserSyear() . "' AND fst.TIMESTAMP BETWEEN '" . $date . "' AND date '" . $date . "' +1 AND SCHOOL_ID='" . UserSchool() . "'" . $where . "ORDER BY " . ($_REQUEST['by_name'] ? "FULL_NAME," : '') . "fst.TRANSACTION_ID DESC"), array('DATE' => 'ProperDate', 'SHORT_NAME' => 'bump_count'));
foreach ($RET as $key => $value) {
// get details of each transaction
$tmpRET = DBGet(DBQuery("SELECT TRANSACTION_ID AS TRANS_ID,*,'" . $value['SHORT_NAME'] . "' AS TRANSACTION_SHORT_NAME FROM FOOD_SERVICE_TRANSACTION_ITEMS WHERE TRANSACTION_ID='" . $value['TRANSACTION_ID'] . "'"), array('SHORT_NAME' => 'bump_items_count'));
// merge transaction and detail records
$RET[$key] = array($value) + $tmpRET;
}
//echo '<pre>'; var_dump($RET); echo '</pre>';
$columns = array('TRANSACTION_ID' => _('ID'), 'ACCOUNT_ID' => _('Account ID'), 'FULL_NAME' => _('Student'), 'DATE' => _('Date'), 'TIME' => _('Time'), 'BALANCE' => _('Balance'), 'DISCOUNT' => _('Discount'), 'DESCRIPTION' => _('Description'), 'DISCOUNT' => _('Discount'), 'AMOUNT' => _('Amount'), 'SELLER' => _('User'));
$group = array(array('TRANSACTION_ID'));
$link['remove']['link'] = PreparePHP_SELF($_REQUEST, array(), array('modfunc' => 'delete'));
$link['remove']['variables'] = array('transaction_id' => 'TRANS_ID', 'item_id' => 'ITEM_ID');
} else {
$RET = DBGet(DBQuery("SELECT fst.TRANSACTION_ID,fst.ACCOUNT_ID,fst.SHORT_NAME,fst.STUDENT_ID,fst.DISCOUNT,(SELECT sum(AMOUNT) FROM FOOD_SERVICE_TRANSACTION_ITEMS WHERE TRANSACTION_ID=fst.TRANSACTION_ID) AS AMOUNT,fst.BALANCE,to_char(fst.TIMESTAMP,'YYYY-MM-DD') AS DATE,to_char(fst.TIMESTAMP,'HH:MI:SS AM') AS TIME,fst.DESCRIPTION," . db_case(array('fst.STUDENT_ID', "''", 'NULL', "(SELECT LAST_NAME||', '||" . (Preferences('NAME') == 'Common' ? 'coalesce(CUSTOM_200000002,FIRST_NAME)' : 'FIRST_NAME') . " FROM STUDENTS WHERE STUDENT_ID=fst.STUDENT_ID)")) . " AS FULL_NAME FROM FOOD_SERVICE_TRANSACTIONS fst WHERE SYEAR='" . UserSyear() . "' AND fst.TIMESTAMP BETWEEN '" . $date . "' AND date '" . $date . "' +1 AND SCHOOL_ID='" . UserSchool() . "'" . $where . "ORDER BY " . ($_REQUEST['by_name'] ? "FULL_NAME," : '') . "fst.TRANSACTION_ID DESC"), array('DATE' => 'ProperDate', 'SHORT_NAME' => 'bump_count'));
$columns = array('TRANSACTION_ID' => _('ID'), 'ACCOUNT_ID' => _('Account ID'), 'FULL_NAME' => _('Student'), 'DATE' => _('Date'), 'TIME' => _('Time'), 'BALANCE' => _('Balance'), 'DISCOUNT' => _('Discount'), 'DESCRIPTION' => _('Description'), 'AMOUNT' => _('Amount'));
}
}
}
}
unset($_REQUEST['student']);
unset($_REQUEST['staff']);
unset($_REQUEST['modfunc']);
}
$schools_RET = DBGet(DBQuery("SELECT ID,SYEAR,TITLE FROM SCHOOLS"), array(), array('SYEAR'));
//echo '<pre>'; var_dump($schools_RET); echo '</pre>';
foreach ($schools_RET as $syear => $schools) {
foreach ($schools as $school) {
$schools_select[$syear][$school['ID']] = $school['TITLE'];
}
}
//echo '<pre>'; var_dump($schools_select); echo '</pre>';
$students_RET = DBGet(DBQuery("SELECT fst.TRANSACTION_ID,fst.ACCOUNT_ID,fst.SYEAR," . db_case(array('fst.STUDENT_ID', "''", 'NULL', "(SELECT FIRST_NAME||' '||LAST_NAME FROM STUDENTS WHERE STUDENT_ID=fst.STUDENT_ID)")) . " AS FULL_NAME,fst.ACCOUNT_ID AS STUDENTS,fst.SCHOOL_ID FROM FOOD_SERVICE_TRANSACTIONS fst WHERE fst.SCHOOL_ID IS NULL"), array('STUDENTS' => '_students', 'SCHOOL_ID' => '_make_school'));
$staff_RET = DBGet(DBQuery("SELECT fst.TRANSACTION_ID,fst.STAFF_ID,fst.SYEAR,(SELECT FIRST_NAME||' '||LAST_NAME FROM STAFF WHERE STAFF_ID=fst.STAFF_ID) AS FULL_NAME,fst.SCHOOL_ID FROM FOOD_SERVICE_STAFF_TRANSACTIONS fst WHERE fst.SCHOOL_ID IS NULL"), array('SCHOOL_ID' => '_make_staff_school'));
//echo '<pre>'; var_dump($students_RET); echo '</pre>';
//echo '<pre>'; var_dump($users_RET); echo '</pre>';
echo "<FORM action=Modules.php?modname={$_REQUEST['modname']}&modfunc=update method=POST>";
DrawHeader('', SubmitButton(_('Save')));
$columns = array('TRANSACTION_ID' => _('ID'), 'ACCOUNT_ID' => _('Account ID'), 'SYEAR' => _('School Year'), 'FULL_NAME' => _('Student'), 'STUDENTS' => _('Students'), 'SCHOOL_ID' => _('School'));
ListOutput($students_RET, $columns, _('Student Transaction w/o School'), _('Student Transactions w/o School'), false, array(), array('save' => false, 'search' => false));
$columns = array('TRANSACTION_ID' => _('ID'), 'SYEAR' => _('School Year'), 'FULL_NAME' => _('User'), 'SCHOOL_ID' => _('School'));
ListOutput($staff_RET, $columns, _('User Transaction w/o School'), _('User Transactions w/o School'), false, array(), array('save' => false, 'search' => false));
echo '<CENTER>' . SubmitButton(_('Save')) . '</CENTER>';
echo '</FORM>';
function _students($value, $column)
{
$RET = DBGet(DBQuery("SELECT s.FIRST_NAME||' '||s.LAST_NAME AS FULL_NAME FROM STUDENTS s,FOOD_SERVICE_STUDENT_ACCOUNTS fsa WHERE s.STUDENT_ID=fsa.STUDENT_ID AND fsa.ACCOUNT_ID='" . $value . "'"));
foreach ($RET as $student) {
#
# This program is released under the terms of the GNU General Public License as
# published by the Free Software Foundation, version 2 of the License.
# See license.txt.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
#
#***************************************************************************************
include '../../Redirect_modules.php';
$requests_RET = DBGet(DBQuery("SELECT concat(r.COURSE_ID, '_', r.COURSE_WEIGHT) AS CRS,\r\n\t\t\t\t\tr.COURSE_ID,r.COURSE_WEIGHT,cp.COURSE_PERIOD_ID,\r\n\t\t\t\t\tc.TITLE AS COURSE_TITLE,cp.PERIOD_ID,\r\n\t\t\t\t\t(cp.TOTAL_SEATS-cp.FILLED_SEATS) AS OPEN_SEATS,s.STUDENT_ID AS SCHEDULED\r\n\t\t\t\tFROM SCHEDULE_REQUESTS r,\r\n\t\t\t\tCOURSES c,SCHOOL_PERIODS sp,\r\n\t\t\t\tCOURSE_PERIODS cp LEFT OUTER JOIN SCHEDULE s ON \r\n\t\t\t\t\t(s.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID AND s.STUDENT_ID='" . UserStudentID() . "')\r\n\t\t\t\tWHERE \r\n\t\t\t\t\tr.SYEAR='" . UserSyear() . "' AND r.SCHOOL_ID='" . UserSchool() . "'\r\n\t\t\t\t\tAND r.COURSE_ID=cp.COURSE_ID AND c.COURSE_ID=cp.COURSE_ID\r\n\t\t\t\t\tAND r.STUDENT_ID='" . UserStudentID() . "'\r\n\t\t\t\t\tAND sp.PERIOD_ID=cp.PERIOD_ID\r\n\t\t\t\t\tORDER BY " . db_case(array('s.STUDENT_ID', "''", "NULL", 'sp.SORT_ORDER')) . "\r\n\t\t\t\t"), array(), array('CRS', 'PERIOD_ID'));
$periods_RET = DBGet(DBQuery("SELECT PERIOD_ID,SHORT_NAME FROM SCHOOL_PERIODS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "' ORDER BY SORT_ORDER"));
echo '<CENTER><TABLE style="border: 1px solid;">';
echo '<TR><TD></TD>';
foreach ($periods_RET as $period) {
echo '<TD><small><b>' . $period['SHORT_NAME'] . '</b></small></TD>';
}
foreach ($requests_RET as $course => $periods) {
echo '<TR><TD><small><b>' . $periods[key($periods)][1]['COURSE_TITLE'] . '</b><small></TD>';
foreach ($periods_RET as $period) {
if ($periods[$period['PERIOD_ID']][1]['SCHEDULED']) {
$color = '0000FF';
} elseif ($periods[$period['PERIOD_ID']]) {
if ($periods[$period['PERIOD_ID']][1]['OPEN_SEATS'] == 0) {
$color = 'FFFF00';
} else {
foreach ($percents_RET as $student_id => $percents) {
$total = $total_percent = 0;
foreach ($percents as $percent) {
$total += $percent['GRADE_PERCENT'] * $programconfig[User('STAFF_ID')][$prefix . $percent['MARKING_PERIOD_ID']];
$total_percent += $programconfig[User('STAFF_ID')][$prefix . $percent['MARKING_PERIOD_ID']];
}
#echo $total /= $total_percent;
$total /= $total_percent;
$import_RET[$student_id] = array(1 => array('REPORT_CARD_GRADE_ID' => _makeLetterGrade($total / 100, $course_period_id, 0, 'ID'), 'GRADE_PERCENT' => round($total, 2)));
}
} else {
if ($programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y') {
$points_RET = DBGet(DBQuery('SELECT DISTINCT s.STUDENT_ID,gt.ASSIGNMENT_TYPE_ID, gt.ASSIGNMENT_TYPE_ID,sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ') AS PARTIAL_POINTS,sum(' . db_case(array('gg.POINTS', '\'-1\' OR (ga.due_date < (select DISTINCT ssm.start_date from student_enrollment ssm where ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=' . UserSchool() . ' AND (ssm.START_DATE IS NOT NULL AND (CURRENT_DATE<=ssm.END_DATE OR CURRENT_DATE>=ssm.END_DATE OR ssm.END_DATE IS NULL)) order by ssm.start_date desc limit 1
) ) ', '\'0\'', 'ga.POINTS')) . ') AS PARTIAL_TOTAL, gt.FINAL_GRADE_PERCENT FROM students s JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID=\'' . $course_period_id . '\') JOIN gradebook_assignments ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID=\'' . $course_id . '\' AND ga.STAFF_ID=\'' . User('STAFF_ID') . '\') AND ga.MARKING_PERIOD_ID=\'' . UserMP() . '\') LEFT OUTER JOIN gradebook_grades gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID),gradebook_assignment_types gt WHERE gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gt.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND gt.COURSE_ID=\'' . $course_id . '\' AND ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE,gt.ASSIGNMENT_TYPE_ID,gt.FINAL_GRADE_PERCENT'), array(), array('STUDENT_ID'));
} else {
$points_RET = DBGet(DBQuery('SELECT DISTINCT s.STUDENT_ID,\'-1\' AS ASSIGNMENT_TYPE_ID,sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ') AS PARTIAL_POINTS,sum(' . db_case(array('gg.POINTS', '\'-1\' OR (ga.due_date < (select DISTINCT ssm.start_date from student_enrollment ssm where ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=' . UserSchool() . ' AND (ssm.START_DATE IS NOT NULL AND (CURRENT_DATE<=ssm.END_DATE OR CURRENT_DATE>=ssm.END_DATE OR ssm.END_DATE IS NULL)) order by ssm.start_date desc limit 1
) ) ', '\'0\'', 'ga.POINTS')) . ') AS PARTIAL_TOTAL,\'1\' AS FINAL_GRADE_PERCENT FROM students s JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID=\'' . $course_period_id . '\') JOIN gradebook_assignments ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID=\'' . $course_id . '\' AND ga.STAFF_ID=\'' . User('STAFF_ID') . '\') AND ga.MARKING_PERIOD_ID=\'' . UserMP() . '\') LEFT OUTER JOIN gradebook_grades gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID) WHERE gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE,FINAL_GRADE_PERCENT'), array(), array('STUDENT_ID'));
}
if (count($points_RET)) {
foreach ($points_RET as $student_id => $student) {
$total = $total_percent = 0;
foreach ($student as $partial_points) {
if ($partial_points['PARTIAL_TOTAL'] != 0) {
$total += $partial_points['PARTIAL_POINTS'] * $partial_points['FINAL_GRADE_PERCENT'] / $partial_points['PARTIAL_TOTAL'];
$total_percent += $partial_points['FINAL_GRADE_PERCENT'];
}
}
if ($total_percent != 0) {
$total /= $total_percent;
}
$import_RET[$student_id] = array(1 => array('REPORT_CARD_GRADE_ID' => _makeLetterGrade($total, $course_period_id, 0, 'ID'), 'GRADE_PERCENT' => round(100 * $total, 2)));
}
if (count($config_RET)) {
foreach ($config_RET as $title => $value) {
$programconfig[User('STAFF_ID')][$title] = $value[1]['VALUE'];
}
} else {
$programconfig[User('STAFF_ID')] = true;
}
include 'ProgramFunctions/_makeLetterGrade.fnc.php';
$course_period_id = UserCoursePeriod();
$course_id = DBGet(DBQuery("SELECT COURSE_ID FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID='" . UserCoursePeriod() . "'"));
$course_id = $course_id[1]['COURSE_ID'];
$grades_RET = DBGet(DBQuery("SELECT ID,TITLE,GPA_VALUE FROM REPORT_CARD_GRADES WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "'"), array(), array('ID'));
if ($programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y') {
$points_RET = DBGet(DBQuery("SELECT DISTINCT ON (s.STUDENT_ID,gt.ASSIGNMENT_TYPE_ID) s.STUDENT_ID, gt.ASSIGNMENT_TYPE_ID,sum(" . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ") AS PARTIAL_POINTS,sum(" . db_case(array('gg.POINTS', "'-1'", "'0'", 'ga.POINTS')) . ") AS PARTIAL_TOTAL, gt.FINAL_GRADE_PERCENT FROM STUDENTS s JOIN SCHEDULE ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID='{$course_period_id}') JOIN GRADEBOOK_ASSIGNMENTS ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID='{$course_id}' AND ga.STAFF_ID='" . User('STAFF_ID') . "') AND ga.MARKING_PERIOD_ID" . ($programconfig[User('STAFF_ID')]['ELIGIBILITY_CUMULITIVE'] == 'Y' ? " IN (" . GetChildrenMP('SEM', UserMP()) . ")" : "='" . UserMP() . "'") . ") LEFT OUTER JOIN GRADEBOOK_GRADES gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID),GRADEBOOK_ASSIGNMENT_TYPES gt WHERE gt.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND gt.COURSE_ID='{$course_id}' AND ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE,gt.ASSIGNMENT_TYPE_ID,gt.FINAL_GRADE_PERCENT"), array(), array('STUDENT_ID'));
} else {
$points_RET = DBGet(DBQuery("SELECT DISTINCT ON (s.STUDENT_ID) s.STUDENT_ID,'-1' AS ASSIGNMENT_TYPE_ID,sum(" . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ") AS PARTIAL_POINTS,sum(" . db_case(array('gg.POINTS', "'-1'", "'0'", 'ga.POINTS')) . ") AS PARTIAL_TOTAL,'1' AS FINAL_GRADE_PERCENT FROM STUDENTS s JOIN SCHEDULE ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID='{$course_period_id}') JOIN GRADEBOOK_ASSIGNMENTS ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID='{$course_id}' AND ga.STAFF_ID='" . User('STAFF_ID') . "') AND ga.MARKING_PERIOD_ID" . ($programconfig[User('STAFF_ID')]['ELIGIBILITY_CUMULITIVE'] == 'Y' ? " IN (" . GetChildrenMP('SEM', UserMP()) . ")" : "='" . UserMP() . "'") . ") LEFT OUTER JOIN GRADEBOOK_GRADES gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID) WHERE ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE "), array(), array('STUDENT_ID'));
}
if (count($points_RET)) {
foreach ($points_RET as $student_id => $student) {
$total = $total_percent = 0;
foreach ($student as $partial_points) {
if ($partial_points['PARTIAL_TOTAL'] != 0) {
$total += $partial_points['PARTIAL_POINTS'] * $partial_points['FINAL_GRADE_PERCENT'] / $partial_points['PARTIAL_TOTAL'];
$total_percent += $partial_points['FINAL_GRADE_PERCENT'];
}
}
if ($total_percent != 0) {
$total /= $total_percent;
}
$grade = $grades_RET[_makeLetterGrade($total, 0, 0, 'ID')][1];
if ($grade['GPA_VALUE'] == '0' || !$grade['GPA_VALUE']) {
FROM
schedule s,courses c,course_periods cp,course_period_var cpv,school_periods sp
WHERE
s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID AND cp.COURSE_PERIOD_ID=cpv.COURSE_PERIOD_ID
AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND cpv.PERIOD_ID = sp.PERIOD_ID AND cpv.DOES_ATTENDANCE=\'Y\'
AND s.SYEAR = c.SYEAR AND cp.MARKING_PERIOD_ID IN (' . GetAllMP('QTR', UserMP()) . ')
AND s.STUDENT_ID=\'' . UserStudentID() . '\' AND s.SYEAR=\'' . UserSyear() . '\'
AND (\'' . DBDate() . '\' BETWEEN s.START_DATE AND s.END_DATE OR s.END_DATE IS NULL)
ORDER BY sp.SORT_ORDER
';
$schedule_RET = DBGet(DBQuery($sql));
$sql = 'SELECT ap.SCHOOL_DATE,ap.PERIOD_ID,ac.SHORT_NAME,ac.STATE_CODE,ac.DEFAULT_CODE FROM attendance_period ap,attendance_codes ac WHERE ap.SCHOOL_DATE BETWEEN \'' . $start_date . '\' AND \'' . $end_date . '\' AND ap.ATTENDANCE_CODE=ac.ID AND ap.STUDENT_ID=\'' . UserStudentID() . '\'';
$attendance_RET = DBGet(DBQuery($sql), array(), array('SCHOOL_DATE', 'PERIOD_ID'));
} else {
$schedule_RET[1] = array('COURSE_PERIOD' => 'Daily Attendance', 'PERIOD_ID' => '0');
$attendance_RET = DBGet(DBQuery('SELECT ad.SCHOOL_DATE,\'0\' AS PERIOD_ID,ad.STATE_VALUE AS STATE_CODE,' . db_case(array('ad.STATE_VALUE', "'0.0'", "'A'", "'1.0'", "'P'", "'H'")) . ' AS SHORT_NAME FROM attendance_day ad WHERE ad.SCHOOL_DATE BETWEEN \'' . $start_date . '\' AND \'' . $end_date . '\' AND ad.STUDENT_ID=\'' . UserStudentID() . '\''), array(), array('SCHOOL_DATE', 'PERIOD_ID'));
}
$i = 0;
if (count($schedule_RET)) {
foreach ($schedule_RET as $course) {
$i++;
$student_RET[$i]['TITLE'] = $course['COURSE_PERIOD'];
foreach ($cal_RET as $value) {
$student_RET[$i][$value['SHORT_DATE']] = _makePeriodColor($attendance_RET[$value['SCHOOL_DATE']][$course['PERIOD_ID']][1]['SHORT_NAME'], $attendance_RET[$value['SCHOOL_DATE']][$course['PERIOD_ID']][1]['STATE_CODE'], $attendance_RET[$value['SCHOOL_DATE']][$course['PERIOD_ID']][1]['DEFAULT_CODE']);
}
}
}
$columns = array('TITLE' => 'Course');
if (count($cal_RET)) {
foreach ($cal_RET as $value) {
$columns[$value['SHORT_DATE']] = ShortDate($value['SCHOOL_DATE']);
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 Rollover($table)
{
global $next_syear;
switch ($table) {
case 'STAFF':
$user_custom = '';
$fields_RET = DBGet(DBQuery("SELECT ID FROM STAFF_FIELDS"));
foreach ($fields_RET as $field) {
$user_custom .= ',CUSTOM_' . $field['ID'];
}
DBQuery("DELETE FROM STUDENTS_JOIN_USERS WHERE STAFF_ID IN (SELECT STAFF_ID FROM STAFF WHERE SYEAR={$next_syear})");
DBQuery("DELETE FROM STAFF_EXCEPTIONS WHERE USER_ID IN (SELECT STAFF_ID FROM STAFF WHERE SYEAR={$next_syear})");
DBQuery("DELETE FROM PROGRAM_USER_CONFIG WHERE USER_ID IN (SELECT STAFF_ID FROM STAFF WHERE SYEAR={$next_syear})");
DBQuery("DELETE FROM STAFF WHERE SYEAR='{$next_syear}'");
DBQuery("INSERT INTO STAFF (SYEAR,STAFF_ID,CURRENT_SCHOOL_ID,TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,USERNAME,PASSWORD,PHONE,EMAIL,PROFILE,HOMEROOM,LAST_LOGIN,SCHOOLS,PROFILE_ID,ROLLOVER_ID{$user_custom}) SELECT SYEAR+1," . db_seq_nextval('STAFF_SEQ') . ",CURRENT_SCHOOL_ID,TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,USERNAME,PASSWORD,PHONE,EMAIL,PROFILE,HOMEROOM,NULL,SCHOOLS,PROFILE_ID,STAFF_ID{$user_custom} FROM STAFF WHERE SYEAR='" . UserSyear() . "'");
DBQuery("INSERT INTO PROGRAM_USER_CONFIG (USER_ID,PROGRAM,TITLE,VALUE) SELECT s.STAFF_ID,puc.PROGRAM,puc.TITLE,puc.VALUE FROM STAFF s,PROGRAM_USER_CONFIG puc WHERE puc.USER_ID=s.ROLLOVER_ID AND puc.PROGRAM='Preferences' AND s.SYEAR='{$next_syear}'");
DBQuery("INSERT INTO STAFF_EXCEPTIONS (USER_ID,MODNAME,CAN_USE,CAN_EDIT) SELECT STAFF_ID,MODNAME,CAN_USE,CAN_EDIT FROM STAFF,STAFF_EXCEPTIONS WHERE USER_ID=ROLLOVER_ID AND SYEAR='{$next_syear}'");
DBQuery("INSERT INTO STUDENTS_JOIN_USERS (STUDENT_ID,STAFF_ID) SELECT j.STUDENT_ID,s.STAFF_ID FROM STAFF s,STUDENTS_JOIN_USERS j WHERE j.STAFF_ID=s.ROLLOVER_ID AND s.SYEAR='{$next_syear}'");
break;
case 'SCHOOL_PERIODS':
DBQuery("DELETE FROM SCHOOL_PERIODS WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='{$next_syear}'");
DBQuery("INSERT INTO SCHOOL_PERIODS (PERIOD_ID,SYEAR,SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,ATTENDANCE,ROLLOVER_ID) SELECT " . db_seq_nextval('SCHOOL_PERIODS_SEQ') . ",SYEAR+1,SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,ATTENDANCE,PERIOD_ID FROM SCHOOL_PERIODS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
break;
case 'ATTENDANCE_CALENDARS':
DBQuery("DELETE FROM ATTENDANCE_CALENDARS WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='{$next_syear}'");
DBQuery("INSERT INTO ATTENDANCE_CALENDARS (CALENDAR_ID,SYEAR,SCHOOL_ID,TITLE,DEFAULT_CALENDAR,ROLLOVER_ID) SELECT " . db_seq_nextval('CALENDARS_SEQ') . ",SYEAR+1,SCHOOL_ID,TITLE,DEFAULT_CALENDAR,CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
break;
case 'SCHOOL_YEARS':
DBQuery("DELETE FROM SCHOOL_PROGRESS_PERIODS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("DELETE FROM SCHOOL_QUARTERS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("DELETE FROM SCHOOL_SEMESTERS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("DELETE FROM SCHOOL_YEARS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
$r = DBGet(DBQuery("select max(m.marking_period_id) as marking_period_id from (select max(marking_period_id) as marking_period_id from SCHOOL_YEARS union select max(marking_period_id) as marking_period_id from SCHOOL_SEMESTERS union select max(marking_period_id) as marking_period_id from SCHOOL_QUARTERS) m"));
$mpi = $r[1]['MARKING_PERIOD_ID'] + 1;
DBQuery("ALTER TABLE marking_period_seq AUTO_INCREMENT = {$mpi}");
DBQuery("INSERT INTO SCHOOL_YEARS (MARKING_PERIOD_ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,ROLLOVER_ID) SELECT " . db_seq_nextval('MARKING_PERIOD_SEQ') . ",SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,date_add(START_DATE,INTERVAL 365 DAY),date_add(END_DATE,INTERVAL 365 DAY),date_add(POST_START_DATE,INTERVAL 365 DAY),date_add(POST_END_DATE,INTERVAL 365 DAY),DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM SCHOOL_YEARS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("INSERT INTO SCHOOL_SEMESTERS (MARKING_PERIOD_ID,YEAR_ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,ROLLOVER_ID) SELECT " . db_seq_nextval('MARKING_PERIOD_SEQ') . ",(SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS y WHERE y.SYEAR=s.SYEAR+1 AND y.ROLLOVER_ID=s.YEAR_ID),SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,date_add(START_DATE, INTERVAL 365 DAY),date_add(END_DATE,INTERVAL 365 DAY),date_add(POST_START_DATE,INTERVAL 365 DAY),date_add(POST_END_DATE,INTERVAL 365 DAY),DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS s WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("INSERT INTO SCHOOL_QUARTERS (MARKING_PERIOD_ID,SEMESTER_ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,ROLLOVER_ID) SELECT " . db_seq_nextval('MARKING_PERIOD_SEQ') . ",(SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS s WHERE s.SYEAR=q.SYEAR+1 AND s.ROLLOVER_ID=q.SEMESTER_ID),SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE+365,END_DATE+365,POST_START_DATE+365,POST_END_DATE+365,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM SCHOOL_QUARTERS q WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("INSERT INTO SCHOOL_PROGRESS_PERIODS (MARKING_PERIOD_ID,QUARTER_ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,ROLLOVER_ID) SELECT " . db_seq_nextval('MARKING_PERIOD_SEQ') . ",(SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS q WHERE q.SYEAR=p.SYEAR+1 AND q.ROLLOVER_ID=p.QUARTER_ID),SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,date_add(START_DATE,INTERVAL 365 DAY),date_add(END_DATE,INTERVAL 365 DAY),date_add(POST_START_DATE,INTERVAL 365 DAY),date_add(POST_END_DATE,INTERVAL 365 DAY),DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM SCHOOL_PROGRESS_PERIODS p WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
break;
case 'COURSES':
DBQuery("DELETE FROM COURSE_SUBJECTS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
/*DBQuery("DELETE FROM COURSE_WEIGHTS WHERE SYEAR='$next_syear' AND SCHOOL_ID='".UserSchool()."'");*/
DBQuery("DELETE FROM COURSES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("DELETE FROM COURSE_PERIODS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
// ROLL COURSE_SUBJECTS
DBQuery("INSERT INTO COURSE_SUBJECTS (SYEAR,SCHOOL_ID,SUBJECT_ID,TITLE,SHORT_NAME,ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID," . db_seq_nextval('COURSE_SUBJECTS_SEQ') . ",TITLE,SHORT_NAME,SUBJECT_ID FROM COURSE_SUBJECTS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
// ROLL COURSE WEIGHTS
DBQuery("INSERT INTO COURSES (SYEAR,COURSE_ID,SUBJECT_ID,SCHOOL_ID,GRADE_LEVEL,TITLE,SHORT_NAME,ROLLOVER_ID) SELECT SYEAR+1," . db_seq_nextval('COURSES_SEQ') . ",(SELECT SUBJECT_ID FROM COURSE_SUBJECTS s WHERE s.SYEAR=c.SYEAR+1 AND s.ROLLOVER_ID=c.SUBJECT_ID),SCHOOL_ID,GRADE_LEVEL,TITLE,SHORT_NAME,COURSE_ID FROM COURSES c WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
// ROLL COURSES
/*DBQuery("INSERT INTO COURSE_WEIGHTS (SYEAR,SCHOOL_ID,COURSE_ID,GPA_MULTIPLIER,COURSE_WEIGHT) SELECT SYEAR+1,SCHOOL_ID,(SELECT COURSE_ID FROM COURSES c WHERE c.SYEAR=w.SYEAR+1 AND c.ROLLOVER_ID=w.COURSE_ID),GPA_MULTIPLIER,COURSE_WEIGHT FROM COURSE_WEIGHTS w WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'");*/
// ROLL COURSE_PERIODS
DBQuery("INSERT INTO COURSE_PERIODS (SYEAR,SCHOOL_ID,COURSE_PERIOD_ID,COURSE_ID,COURSE_WEIGHT,TITLE,SHORT_NAME,PERIOD_ID,MP,MARKING_PERIOD_ID,TEACHER_ID,ROOM,TOTAL_SEATS,FILLED_SEATS,DOES_ATTENDANCE,GRADE_SCALE_ID,DOES_HONOR_ROLL,DOES_CLASS_RANK,DOES_BREAKOFF,GENDER_RESTRICTION,HOUSE_RESTRICTION,CREDITS,AVAILABILITY,DAYS,HALF_DAY,PARENT_ID,CALENDAR_ID,ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID," . db_seq_nextval('COURSE_PERIODS_SEQ') . ",(SELECT COURSE_ID FROM COURSES c WHERE c.SYEAR=p.SYEAR+1 AND c.ROLLOVER_ID=p.COURSE_ID),COURSE_WEIGHT,TITLE,SHORT_NAME,(SELECT PERIOD_ID FROM SCHOOL_PERIODS n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.PERIOD_ID),MP," . db_case(array('MP', "'FY'", '(SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.MARKING_PERIOD_ID)', "'SEM'", '(SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.MARKING_PERIOD_ID)', "'QTR'", '(SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.MARKING_PERIOD_ID)')) . ",(SELECT STAFF_ID FROM STAFF n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.TEACHER_ID),ROOM,TOTAL_SEATS,0 AS FILLED_SEATS,DOES_ATTENDANCE,(SELECT ID FROM REPORT_CARD_GRADE_SCALES n WHERE n.ROLLOVER_ID=p.GRADE_SCALE_ID),DOES_HONOR_ROLL,DOES_CLASS_RANK,DOES_BREAKOFF,GENDER_RESTRICTION,HOUSE_RESTRICTION,CREDITS,AVAILABILITY,DAYS,HALF_DAY,PARENT_ID,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS n WHERE n.ROLLOVER_ID=p.CALENDAR_ID),COURSE_PERIOD_ID FROM COURSE_PERIODS p WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
$rowq = DBQUERY("SELECT * FROM COURSE_PERIODS WHERE ROLLOVER_ID=PARENT_ID");
DBQuery("UPDATE COURSE_PERIODS SET PARENT_ID='" . $rowq['course_period_id'] . "' WHERE PARENT_ID IS NOT NULL AND SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
break;
case 'STUDENT_ENROLLMENT':
$next_start_date = DBDate();
DBQuery("DELETE FROM STUDENT_ENROLLMENT WHERE SYEAR='{$next_syear}' AND LAST_SCHOOL='" . UserSchool() . "'");
// ROLL STUDENTS TO NEXT GRADE
DBQuery("INSERT INTO STUDENT_ENROLLMENT (ID,SYEAR,SCHOOL_ID,STUDENT_ID,GRADE_ID,START_DATE,END_DATE,ENROLLMENT_CODE,DROP_CODE,CALENDAR_ID,LAST_SCHOOL) SELECT " . db_seq_nextval('STUDENT_ENROLLMENT_SEQ') . ",SYEAR+1,SCHOOL_ID,STUDENT_ID,(SELECT NEXT_GRADE_ID FROM SCHOOL_GRADELEVELS g WHERE g.ID=e.GRADE_ID),'{$next_start_date}' AS START_DATE,NULL AS END_DATE,NULL AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_ID FROM STUDENT_ENROLLMENT e WHERE e.SYEAR='" . UserSyear() . "' AND e.SCHOOL_ID='" . UserSchool() . "' AND (('" . DBDate() . "' BETWEEN e.START_DATE AND e.END_DATE OR e.END_DATE IS NULL) AND '" . DBDate() . "'>=e.START_DATE) AND e.NEXT_SCHOOL='" . UserSchool() . "'");
// ROLL STUDENTS WHO ARE TO BE RETAINED
DBQuery("INSERT INTO STUDENT_ENROLLMENT (ID,SYEAR,SCHOOL_ID,STUDENT_ID,GRADE_ID,START_DATE,END_DATE,ENROLLMENT_CODE,DROP_CODE,CALENDAR_ID,LAST_SCHOOL) SELECT " . db_seq_nextval('STUDENT_ENROLLMENT_SEQ') . ",SYEAR+1,SCHOOL_ID,STUDENT_ID,GRADE_ID,'{$next_start_date}' AS START_DATE,NULL AS END_DATE,NULL AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_ID FROM STUDENT_ENROLLMENT e WHERE e.SYEAR='" . UserSyear() . "' AND e.SCHOOL_ID='" . UserSchool() . "' AND (('" . DBDate() . "' BETWEEN e.START_DATE AND e.END_DATE OR e.END_DATE IS NULL) AND '" . DBDate() . "'>=e.START_DATE) AND e.NEXT_SCHOOL='0'");
// ROLL STUDENTS TO NEXT SCHOOL
DBQuery("INSERT INTO STUDENT_ENROLLMENT (ID,SYEAR,SCHOOL_ID,STUDENT_ID,GRADE_ID,START_DATE,END_DATE,ENROLLMENT_CODE,DROP_CODE,CALENDAR_ID,LAST_SCHOOL) SELECT " . db_seq_nextval('STUDENT_ENROLLMENT_SEQ') . ",SYEAR+1,NEXT_SCHOOL,STUDENT_ID,(SELECT g.ID FROM SCHOOL_GRADELEVELS g WHERE g.SORT_ORDER=1 AND g.SCHOOL_ID=e.NEXT_SCHOOL),'{$next_start_date}' AS START_DATE,NULL AS END_DATE,NULL AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_ID FROM STUDENT_ENROLLMENT e WHERE e.SYEAR='" . UserSyear() . "' AND e.SCHOOL_ID='" . UserSchool() . "' AND (('" . DBDate() . "' BETWEEN e.START_DATE AND e.END_DATE OR e.END_DATE IS NULL) AND '" . DBDate() . "'>=e.START_DATE) AND e.NEXT_SCHOOL NOT IN ('" . UserSchool() . "','0','-1')");
break;
case 'REPORT_CARD_GRADE_SCALES':
DBQuery("DELETE FROM REPORT_CARD_GRADE_SCALES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("DELETE FROM REPORT_CARD_GRADES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("INSERT INTO REPORT_CARD_GRADE_SCALES (ID,SYEAR,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ROLLOVER_ID) SELECT " . db_seq_nextval('REPORT_CARD_GRADE_SCALES_SEQ') . "+ID,SYEAR+1,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ID FROM REPORT_CARD_GRADE_SCALES WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("INSERT INTO REPORT_CARD_GRADES (ID,SYEAR,SCHOOL_ID,TITLE,COMMENT,BREAK_OFF,GPA_VALUE,GRADE_SCALE_ID,SORT_ORDER) SELECT " . db_seq_nextval('REPORT_CARD_GRADES_SEQ') . "+ID,SYEAR+1,SCHOOL_ID,TITLE,COMMENT,BREAK_OFF,GPA_VALUE,(SELECT ID FROM REPORT_CARD_GRADE_SCALES WHERE ROLLOVER_ID=GRADE_SCALE_ID AND SCHOOL_ID=REPORT_CARD_GRADES.SCHOOL_ID),SORT_ORDER FROM REPORT_CARD_GRADES WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
break;
case 'REPORT_CARD_COMMENTS':
DBQuery("DELETE FROM REPORT_CARD_COMMENTS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
DBQuery("INSERT INTO REPORT_CARD_COMMENTS (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID) SELECT " . db_seq_nextval('REPORT_CARD_COMMENTS_SEQ') . ",SYEAR+1,SCHOOL_ID,TITLE,SORT_ORDER," . db_case(array('COURSE_ID', "''", 'NULL', "(SELECT COURSE_ID FROM COURSES WHERE ROLLOVER_ID=rc.COURSE_ID)")) . " FROM REPORT_CARD_COMMENTS rc WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
break;
case 'ELIGIBILITY_ACTIVITIES':
case 'ATTENDANCE_CODES':
DBQuery("DELETE FROM {$table} WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'");
$table_properties = db_properties($table);
$columns = '';
foreach ($table_properties as $column => $values) {
if ($column != 'ID' && $column != 'SYEAR') {
$columns .= ',' . $column;
}
}
DBQuery("INSERT INTO {$table} (ID,SYEAR" . $columns . ") SELECT " . db_seq_nextval($table . '_SEQ') . ",SYEAR+1" . $columns . " FROM {$table} WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'");
break;
// DOESN'T HAVE A SCHOOL_ID
// DOESN'T HAVE A SCHOOL_ID
case 'STUDENT_ENROLLMENT_CODES':
DBQuery("DELETE FROM {$table} WHERE SYEAR='{$next_syear}'");
$table_properties = db_properties($table);
$columns = '';
foreach ($table_properties as $column => $values) {
if ($column != 'ID' && $column != 'SYEAR') {
$columns .= ',' . $column;
}
}
DBQuery("INSERT INTO {$table} (ID,SYEAR" . $columns . ") SELECT " . db_seq_nextval($table . '_SEQ') . ",SYEAR+1" . $columns . " FROM {$table} WHERE SYEAR='" . UserSyear() . "'");
break;
}
// ---------------------------------------------------------------------- data write start ----------------------------------------------------------------------- //
$string .= "<" . "?php \n";
$string .= "\$" . "DatabaseType = '" . $_SESSION['DT'] . "'; \n";
$string .= "\$" . "DatabaseServer = '" . $_SESSION['DS'] . "'; \n";
$string .= "\$" . "DatabaseUsername = '******'DU'] . "'; \n";
$string .= "\$" . "DatabasePassword = '******'DP'] . "'; \n";
$string .= "\$" . "DatabaseName = '" . $_SESSION['DB'] . "'; \n";
$string .= "\$" . "DatabasePort = '" . $_SESSION['DBP'] . "'; \n";
$string .= "\$" . "DefaultSyear = '" . $_SESSION['NY'] . "'; \n";
$string .= "?" . ">";
$err = "Can't write to file";
$myFile = "data.php";
$fh = fopen($myFile, 'w') or exit($err);
fwrite($fh, $string);
fclose($fh);
// ---------------------------------------------------------------------- data write end ------------------------------------------------------------------------ //
}
echo '<TD valign=top>' . NoInput(red($student['BALANCE']), _('Balance')) . '</TD>';
echo '</TR></TABLE>';
if ($_REQUEST['detailed_view'] != 'true') {
DrawHeader('<A HREF=' . PreparePHP_SELF($_REQUEST, array(), array('detailed_view' => 'true')) . '>' . _('Detailed View') . '</A>');
} else {
DrawHeader('<A HREF=' . PreparePHP_SELF($_REQUEST, array(), array('detailed_view' => 'false')) . '>' . _('Original View') . '</A>');
}
if ($student['BALANCE']) {
if ($_REQUEST['student_select']) {
$where = " AND fst.STUDENT_ID='" . $_REQUEST['student_select'] . "'";
}
if ($_REQUEST['type_select']) {
$where .= " AND fst.SHORT_NAME='" . $_REQUEST['type_select'] . "'";
}
if ($_REQUEST['detailed_view'] == 'true') {
$RET = DBGet(DBQuery("SELECT fst.TRANSACTION_ID AS TRANS_ID,fst.TRANSACTION_ID,fst.STUDENT_ID,fst.DISCOUNT,(SELECT sum(AMOUNT) FROM FOOD_SERVICE_TRANSACTION_ITEMS WHERE TRANSACTION_ID=fst.TRANSACTION_ID) AS AMOUNT,fst.BALANCE,to_char(fst.TIMESTAMP,'YYYY-MM-DD') AS DATE,to_char(fst.TIMESTAMP,'HH:MI:SS AM') AS TIME,fst.DESCRIPTION," . db_case(array('fst.STUDENT_ID', "''", 'NULL', "(SELECT FIRST_NAME||' '||LAST_NAME FROM STUDENTS WHERE STUDENT_ID=fst.STUDENT_ID)")) . " AS STUDENT," . db_case(array('fst.SELLER_ID', "''", 'NULL', "(SELECT FIRST_NAME||' '||LAST_NAME FROM STAFF WHERE STAFF_ID=fst.SELLER_ID)")) . " AS SELLER FROM FOOD_SERVICE_TRANSACTIONS fst WHERE fst.ACCOUNT_ID='" . $student['ACCOUNT_ID'] . "' AND SYEAR='" . UserSyear() . "' AND fst.TIMESTAMP BETWEEN '" . $start_date . "' AND date '" . $end_date . "' +1" . $where . " ORDER BY fst.TRANSACTION_ID DESC"), array('DATE' => 'ProperDate', 'BALANCE' => 'red'));
// get details of each transaction
foreach ($RET as $key => $value) {
$tmpRET = DBGet(DBQuery("SELECT TRANSACTION_ID AS TRANS_ID,* FROM FOOD_SERVICE_TRANSACTION_ITEMS WHERE TRANSACTION_ID='" . $value['TRANSACTION_ID'] . "'"));
// merge transaction and detail records
$RET[$key] = array($RET[$key]) + $tmpRET;
}
$columns = array('TRANSACTION_ID' => _('ID'), 'STUDENT' => _('Student'), 'DATE' => _('Date'), 'TIME' => _('Time'), 'BALANCE' => _('Balance'), 'DISCOUNT' => _('Discount'), 'DESCRIPTION' => _('Description'), 'AMOUNT' => _('Amount'), 'SELLER' => _('Seller'));
$group = array(array('TRANSACTION_ID'));
$link['remove']['link'] = PreparePHP_SELF($_REQUEST, array(), array('modfunc' => 'delete'));
$link['remove']['variables'] = array('transaction_id' => 'TRANS_ID', 'item_id' => 'ITEM_ID');
} else {
$RET = DBGet(DBQuery("SELECT fst.TRANSACTION_ID,fst.DISCOUNT,(SELECT sum(AMOUNT) FROM FOOD_SERVICE_TRANSACTION_ITEMS WHERE TRANSACTION_ID=fst.TRANSACTION_ID) AS AMOUNT,fst.BALANCE,to_char(fst.TIMESTAMP,'YYYY-MM-DD') AS DATE,to_char(fst.TIMESTAMP,'HH:MI:SS AM') AS TIME,fst.DESCRIPTION FROM FOOD_SERVICE_TRANSACTIONS fst WHERE fst.ACCOUNT_ID='" . $student['ACCOUNT_ID'] . "' AND SYEAR='" . UserSyear() . "' AND fst.TIMESTAMP BETWEEN '" . $start_date . "' AND date '" . $end_date . "'+1 " . $where . " ORDER BY fst.TRANSACTION_ID DESC"), array('DATE' => 'ProperDate', 'BALANCE' => 'red'));
$columns = array('TRANSACTION_ID' => _('ID'), 'DATE' => _('Date'), 'TIME' => _('Time'), 'BALANCE' => _('Balance'), 'DISCOUNT' => _('Discount'), 'DESCRIPTION' => _('Description'), 'AMOUNT' => _('Amount'));
}
ListOutput($RET, $columns, _('Transaction'), _('Transactions'), $link, $group);
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;
}
<?php
/**
* @file $Id: ReferralLog.php 405 2007-01-22 21:10:19Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/
$categories_RET = DBGet(DBQuery("SELECT f.ID,u.TITLE,u.SELECT_OPTIONS,f.DATA_TYPE,u.SORT_ORDER FROM DISCIPLINE_FIELDS f,DISCIPLINE_FIELD_USAGE u WHERE f.DATA_TYPE!='multiple_checkbox' AND u.DISCIPLINE_FIELD_ID=f.ID ORDER BY " . db_case(array('DATA_TYPE', "'textarea'", "'1'", "'0'")) . ",SORT_ORDER"), array(), array('ID'));
$extra['new'] = true;
$extra['second_col'] .= '<fieldset><legend>' . _('Include in Referral Log') . ':</legend><TABLE>';
$extra['second_col'] .= '<TR><TD><INPUT type=checkbox name=elements[ENTRY_DATE] value=Y CHECKED>' . _('Entry Date') . '</TD>';
$extra['second_col'] .= '<TD><INPUT type=checkbox name=elements[STAFF_ID] value=Y CHECKED>' . _('Reporter') . '</TD></TR>';
foreach ($categories_RET as $id => $category) {
if ($i % 2 == 0) {
$extra['second_col'] .= '</TR><TR>';
}
$extra['second_col'] .= '<TD><INPUT type=checkbox name=elements[CATEGORY_' . $id . '] value=Y' . ($category[1]['DATA_TYPE'] == 'textarea' ? ' CHECKED' : '') . '>' . $category[1]['TITLE'] . '</TD>';
$i++;
}
$extra['second_col'] .= '</TABLE></fieldset>';
$templates_RET = DBGet(DBQuery("SELECT ID,TITLE FROM HEADER_TEMPLATES ORDER BY TITLE"));
foreach ($templates_RET as $template) {
$options .= "<OPTION value={$template['ID']}>" . $template['TITLE'] . '</OPTION>';
}
$extra['second_col'] .= '<TABLE><TR><TD width=100> </TD><TD align=' . ALIGN_RIGHT . '>' . _('Document Template') . ' </TD><TD><SELECT name=_template_id><OPTION value="">' . _('None') . '</OPTION>' . $options . '</SELECT></TD></TR></TABLE>';
function MyWidgets($item)
{
global $extra, $_ROSARIO;
switch ($item) {
case 'honor_roll':
if ($_REQUEST['honor_roll'] == 'Y' && $_REQUEST['high_honor_roll'] == 'Y') {
$extra['SELECT'] .= "," . db_case(array("exists(SELECT rg.GPA_VALUE FROM STUDENT_REPORT_CARD_GRADES sg,COURSE_PERIODS cp,REPORT_CARD_GRADES rg WHERE sg.STUDENT_ID=s.STUDENT_ID AND cp.SYEAR=ssm.SYEAR AND sg.SYEAR=ssm.SYEAR AND sg.MARKING_PERIOD_ID='" . UserMP() . "' AND cp.COURSE_PERIOD_ID=sg.COURSE_PERIOD_ID AND cp.DOES_HONOR_ROLL='Y' AND rg.GRADE_SCALE_ID=cp.GRADE_SCALE_ID AND sg.REPORT_CARD_GRADE_ID=rg.ID AND rg.GPA_VALUE<(SELECT HHR_GPA_VALUE FROM REPORT_CARD_GRADE_SCALES WHERE ID=rg.GRADE_SCALE_ID))", 'true', 'NULL', "'<img src=\"assets/check.png\" height=\"15\" />'")) . " AS HIGH_HONOR";
$extra['WHERE'] .= " AND exists(SELECT '' FROM STUDENT_REPORT_CARD_GRADES sg,COURSE_PERIODS cp WHERE sg.STUDENT_ID=s.STUDENT_ID AND cp.SYEAR=ssm.SYEAR AND sg.SYEAR=ssm.SYEAR AND sg.MARKING_PERIOD_ID='" . UserMP() . "' AND cp.COURSE_PERIOD_ID=sg.COURSE_PERIOD_ID AND cp.DOES_HONOR_ROLL='Y')";
$extra['WHERE'] .= " AND NOT exists(SELECT '' FROM STUDENT_REPORT_CARD_GRADES sg,COURSE_PERIODS cp,REPORT_CARD_GRADES rg WHERE sg.STUDENT_ID=s.STUDENT_ID AND cp.SYEAR=ssm.SYEAR AND sg.SYEAR=ssm.SYEAR AND sg.MARKING_PERIOD_ID='" . UserMP() . "' AND cp.COURSE_PERIOD_ID=sg.COURSE_PERIOD_ID AND cp.DOES_HONOR_ROLL='Y' AND rg.GRADE_SCALE_ID=cp.GRADE_SCALE_ID AND sg.REPORT_CARD_GRADE_ID=rg.ID AND rg.GPA_VALUE<(SELECT HR_GPA_VALUE FROM REPORT_CARD_GRADE_SCALES WHERE ID=rg.GRADE_SCALE_ID))";
$extra['columns_after']['HIGH_HONOR'] = _('High Honor');
if (!$extra['NoSearchTerms']) {
//modif Francois: add translation
$_ROSARIO['SearchTerms'] .= '<b>' . _('Honor Roll') . ' & ' . _('High Honor Roll') . '</b><BR />';
}
} elseif ($_REQUEST['honor_roll'] == 'Y') {
$extra['WHERE'] .= " AND exists(SELECT '' FROM STUDENT_REPORT_CARD_GRADES sg,COURSE_PERIODS cp WHERE sg.STUDENT_ID=s.STUDENT_ID AND cp.SYEAR=ssm.SYEAR AND sg.SYEAR=ssm.SYEAR AND sg.MARKING_PERIOD_ID='" . UserMP() . "' AND cp.COURSE_PERIOD_ID=sg.COURSE_PERIOD_ID AND cp.DOES_HONOR_ROLL='Y')";
$extra['WHERE'] .= " AND NOT exists(SELECT '' FROM STUDENT_REPORT_CARD_GRADES sg,COURSE_PERIODS cp,REPORT_CARD_GRADES rg WHERE sg.STUDENT_ID=s.STUDENT_ID AND cp.SYEAR=ssm.SYEAR AND sg.SYEAR=ssm.SYEAR AND sg.MARKING_PERIOD_ID='" . UserMP() . "' AND cp.COURSE_PERIOD_ID=sg.COURSE_PERIOD_ID AND cp.DOES_HONOR_ROLL='Y' AND rg.GRADE_SCALE_ID=cp.GRADE_SCALE_ID AND sg.REPORT_CARD_GRADE_ID=rg.ID AND rg.GPA_VALUE<(SELECT HR_GPA_VALUE FROM REPORT_CARD_GRADE_SCALES WHERE ID=rg.GRADE_SCALE_ID))";
$extra['WHERE'] .= " AND exists(SELECT '' FROM STUDENT_REPORT_CARD_GRADES sg,COURSE_PERIODS cp,REPORT_CARD_GRADES rg WHERE sg.STUDENT_ID=s.STUDENT_ID AND cp.SYEAR=ssm.SYEAR AND sg.SYEAR=ssm.SYEAR AND sg.MARKING_PERIOD_ID='" . UserMP() . "' AND cp.COURSE_PERIOD_ID=sg.COURSE_PERIOD_ID AND cp.DOES_HONOR_ROLL='Y' AND rg.GRADE_SCALE_ID=cp.GRADE_SCALE_ID AND sg.REPORT_CARD_GRADE_ID=rg.ID AND rg.GPA_VALUE<(SELECT HHR_GPA_VALUE FROM REPORT_CARD_GRADE_SCALES WHERE ID=rg.GRADE_SCALE_ID))";
if (!$extra['NoSearchTerms']) {
$_ROSARIO['SearchTerms'] .= '<b>' . _('Honor Roll') . '</b><BR />';
}
} elseif ($_REQUEST['high_honor_roll'] == 'Y') {
$extra['WHERE'] .= " AND exists(SELECT '' FROM STUDENT_REPORT_CARD_GRADES sg,COURSE_PERIODS cp WHERE sg.STUDENT_ID=s.STUDENT_ID AND cp.SYEAR=ssm.SYEAR AND sg.SYEAR=ssm.SYEAR AND sg.MARKING_PERIOD_ID='" . UserMP() . "' AND cp.COURSE_PERIOD_ID=sg.COURSE_PERIOD_ID AND cp.DOES_HONOR_ROLL='Y')";
$extra['WHERE'] .= " AND NOT exists(SELECT '' FROM STUDENT_REPORT_CARD_GRADES sg,COURSE_PERIODS cp,REPORT_CARD_GRADES rg WHERE sg.STUDENT_ID=s.STUDENT_ID AND cp.SYEAR=ssm.SYEAR AND sg.SYEAR=ssm.SYEAR AND sg.MARKING_PERIOD_ID='" . UserMP() . "' AND cp.COURSE_PERIOD_ID=sg.COURSE_PERIOD_ID AND cp.DOES_HONOR_ROLL='Y' AND rg.GRADE_SCALE_ID=cp.GRADE_SCALE_ID AND sg.REPORT_CARD_GRADE_ID=rg.ID AND rg.GPA_VALUE<(SELECT HHR_GPA_VALUE FROM REPORT_CARD_GRADE_SCALES WHERE ID=rg.GRADE_SCALE_ID))";
if (!$extra['NoSearchTerms']) {
$_ROSARIO['SearchTerms'] .= '<b>' . _('High Honor Roll') . '</b><BR />';
}
}
//modif Francois: add <label> on checkbox
$extra['search'] .= '<TR><TD style="text-align:right; width:120px:">' . _('Honor Roll') . '</TD><TD><label><INPUT type="checkbox" name="honor_roll" value="Y" checked /> ' . _('Honor') . '</label> <label><INPUT type="checkbox" name="high_honor_roll" value="Y" checked /> ' . _('High Honor') . '</label></TD></TR>';
break;
}
}
}
} else {
$programconfig[User('STAFF_ID')] = true;
}
$_openSIS['_makeLetterGrade']['courses'][$course_period_id] = DBGet(DBQuery("SELECT DOES_BREAKOFF,GRADE_SCALE_ID FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID='" . $course_period_id . "'"));
include '_makeLetterGrade.fnc.php';
if (false && GetMP($_REQUEST['mp'], 'TABLE') == 'SCHOOL_SEMESTERS') {
$points_RET = DBGet(DBQuery("SELECT STUDENT_ID,MARKING_PERIOD_ID FROM STUDENT_REPORT_CARD_GRADES WHERE COURSE_PERIOD_ID='" . $course_period_id . "' AND MARKING_PERIOD_ID IN (" . GetAllMP('SEM', $_REQUEST['mp']) . ",'E" . GetParentMP('SEM', UserMP()) . "')"), array(), array('STUDENT_ID'));
}
if (GetMP($_REQUEST['mp'], 'TABLE') == 'SCHOOL_QUARTERS' || GetMP($_REQUEST['mp'], 'TABLE') == 'SCHOOL_PROGRESS_PERIODS') {
// the 'populate the form' approach does not require that we get precisely the right students because nothing is modified here
// so we don't need to filter on enrollment dates - in fact, for include_inactive we want 'em all anyway
if ($programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y') {
$points_RET = DBGet(DBQuery("SELECT DISTINCT s.STUDENT_ID,gt.ASSIGNMENT_TYPE_ID, gt.ASSIGNMENT_TYPE_ID,sum(" . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ") AS PARTIAL_POINTS,sum(" . db_case(array('gg.POINTS', "'-1'", "'0'", 'ga.POINTS')) . ") AS PARTIAL_TOTAL, gt.FINAL_GRADE_PERCENT FROM STUDENTS s JOIN SCHEDULE ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID='{$course_period_id}') JOIN GRADEBOOK_ASSIGNMENTS ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID='{$course_id}' AND ga.STAFF_ID='" . User('STAFF_ID') . "') AND ga.MARKING_PERIOD_ID='" . UserMP() . "') LEFT OUTER JOIN GRADEBOOK_GRADES gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID),GRADEBOOK_ASSIGNMENT_TYPES gt WHERE gt.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND gt.COURSE_ID='{$course_id}' AND ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE,gt.ASSIGNMENT_TYPE_ID,gt.FINAL_GRADE_PERCENT"), array(), array('STUDENT_ID'));
} else {
$points_RET = DBGet(DBQuery("SELECT DISTINCT s.STUDENT_ID,'-1' AS ASSIGNMENT_TYPE_ID,sum(" . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ") AS PARTIAL_POINTS,sum(" . db_case(array('gg.POINTS', "'-1'", "'0'", 'ga.POINTS')) . ") AS PARTIAL_TOTAL,'1' AS FINAL_GRADE_PERCENT FROM STUDENTS s JOIN SCHEDULE ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID='{$course_period_id}') JOIN GRADEBOOK_ASSIGNMENTS ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID='{$course_id}' AND ga.STAFF_ID='" . User('STAFF_ID') . "') AND ga.MARKING_PERIOD_ID='" . UserMP() . "') LEFT OUTER JOIN GRADEBOOK_GRADES gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID) WHERE ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE, FINAL_GRADE_PERCENT"), array(), array('STUDENT_ID'));
}
if (count($points_RET)) {
foreach ($points_RET as $student_id => $student) {
$total = $total_percent = 0;
foreach ($student as $partial_points) {
if ($partial_points['PARTIAL_TOTAL'] != 0) {
$total += $partial_points['PARTIAL_POINTS'] * $partial_points['FINAL_GRADE_PERCENT'] / $partial_points['PARTIAL_TOTAL'];
$total_percent += $partial_points['FINAL_GRADE_PERCENT'];
}
}
if ($total_percent != 0) {
$total /= $total_percent;
}
$import_RET[$student_id] = array(1 => array('REPORT_CARD_GRADE_ID' => _makeLetterGrade($total, $course_period_id, 0, 'ID'), 'GRADE_PERCENT' => round(100 * $total, 1)));
}
$referrals_RET = GetStuList($extra);
$chart['chart_data'][0][0] = '';
$chart['chart_data'][1][0] = 'Series';
foreach ($referrals_RET as $referral) {
$referral['TITLE'] = explode("||", trim($referral['TITLE'], '|'));
foreach ($referral['TITLE'] as $option) {
$options_count[$option]++;
}
}
foreach ($category_RET[1]['OPTIONS'] as $option) {
$chart['chart_data'][0][] = $option;
$chart['chart_data'][1][] = $options_count[$option];
}
} elseif ($category_RET[1]['TYPE'] == 'radio') {
$extra = array();
$extra['SELECT_ONLY'] = db_case(array("s.CUSTOM_" . $_REQUEST['category_id'], "'Y'", "'" . _('Yes') . "'", "'" . _('No') . "'")) . " AS TITLE,COUNT(*) AS COUNT ";
$extra['FROM'] = ',DISCIPLINE_REFERRALS dr ';
$extra['WHERE'] = "AND dr.STUDENT_ID=ssm.STUDENT_ID AND dr.SCHOOL_ID=ssm.SCHOOL_ID AND dr.ENTRY_DATE BETWEEN '{$start_date}' AND '{$end_date}' ";
$extra['GROUP'] = 'CUSTOM_' . $_REQUEST['category_id'];
$extra['group'] = array('TITLE');
$extra['DEBUG'] = true;
Widgets('all');
$totals_RET = GetStuList($extra);
// $sql = "SELECT dr.CATEGORY_".$_REQUEST['category_id']." AS TITLE,COUNT(*) AS COUNT FROM DISCIPLINE_REFERRALS dr,STUDENTS s,STUDENT_ENROLLMENT ssm WHERE ssm.STUDENT_ID=s.STUDENT_ID AND dr.ENTRY_DATE BETWEEN '$start_date' AND '$end_date' AND dr.SCHOOL_ID=ssm.SCHOOL_ID AND dr.SCHOOL_ID='".UserSchool()."' ";
// $sql = appendSQL($sql);
// $sql .= " GROUP BY CATEGORY_".$_REQUEST['category_id'];
//$totals_RET = DBGet(DBQuery($sql),array(),array('TITLE'));
$chart['chart_data'][0][0] = '';
$chart['chart_data'][1][0] = 'Series';
$chart['chart_data'][0][0] = _('Yes');
$chart['chart_data'][1][0] = $totals_RET['Yes'][1]['COUNT'];
$assignments_RET = DBGet(DBQuery("SELECT ga.ASSIGNMENT_ID,gg.POINTS,ga.ASSIGNED_DATE,ga.DUE_DATE,gg.COMMENT,ga.TITLE,ga.DESCRIPTION,ga.ASSIGNED_DATE,ga.DUE_DATE,ga.POINTS AS POINTS_POSSIBLE,at.TITLE AS CATEGORY\r\n FROM GRADEBOOK_ASSIGNMENTS ga LEFT OUTER JOIN GRADEBOOK_GRADES gg\r\n ON (gg.COURSE_PERIOD_ID='{$course['COURSE_PERIOD_ID']}' AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.STUDENT_ID='" . UserStudentID() . "'),GRADEBOOK_ASSIGNMENT_TYPES at\r\n WHERE (ga.COURSE_PERIOD_ID='{$course['COURSE_PERIOD_ID']}' OR ga.COURSE_ID='{$course['COURSE_ID']}' AND ga.STAFF_ID='{$staff_id}') AND ga.MARKING_PERIOD_ID='" . UserMP() . "'\r\n AND at.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND ((ga.ASSIGNED_DATE IS NOT NULL )\r\n or gg.POINTS IS NOT NULL) AND (ga.POINTS!='0' OR gg.POINTS IS NOT NULL AND gg.POINTS!='-1') ORDER BY ga.ASSIGNMENT_ID DESC"), array('TITLE' => '_makeTipTitle'));
/*$assignments_RET = DBGet(DBQuery("SELECT ga.ASSIGNMENT_ID,gg.POINTS,gg.COMMENT,ga.TITLE,ga.DESCRIPTION,ga.ASSIGNED_DATE,ga.DUE_DATE,ga.POINTS AS POINTS_POSSIBLE,at.TITLE AS CATEGORY
FROM GRADEBOOK_ASSIGNMENTS ga LEFT OUTER JOIN GRADEBOOK_GRADES gg
ON (gg.COURSE_PERIOD_ID='$course[COURSE_PERIOD_ID]' AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.STUDENT_ID='".UserStudentID()."'),GRADEBOOK_ASSIGNMENT_TYPES at
WHERE (ga.COURSE_PERIOD_ID='$course[COURSE_PERIOD_ID]' OR ga.COURSE_ID='$course[COURSE_ID]' AND ga.STAFF_ID='$staff_id') AND ga.MARKING_PERIOD_ID='".UserMP()."'
AND at.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE+".round($programconfig[$staff_id]['LATENCY']).")
OR gg.POINTS IS NOT NULL) AND (ga.POINTS!='0' OR gg.POINTS IS NOT NULL AND gg.POINTS!='-1') ORDER BY ga.ASSIGNMENT_ID DESC"),array('TITLE'=>'_makeTipTitle'));
*/
//echo '<pre>'; var_dump($assignments_RET); echo '</pre>';
if (count($assignments_RET)) {
if ($_REQUEST['id'] == 'all') {
# echo '<BR>';
DrawHeader('<br><B>' . $course['COURSE_TITLE'] . '</B> - ' . substr($course['TITLE'], strrpos(str_replace(' - ', ' ^ ', $course['TITLE']), '^') + 2), "<A HREF=Modules.php?modname={$_REQUEST['modname']}>Back to Totals</A>");
}
if ($do_stats) {
$all_RET = DBGet(DBQuery("SELECT ga.ASSIGNMENT_ID,gg.POINTS,min(" . db_case(array('gg.POINTS', "'-1'", 'ga.POINTS', 'gg.POINTS')) . ") AS MIN,max(" . db_case(array('gg.POINTS', "'-1'", '0', 'gg.POINTS')) . ") AS MAX," . db_case(array("sum(" . db_case(array('gg.POINTS', "'-1'", '0', '1')) . ")", "'0'", "'0'", "sum(" . db_case(array('gg.POINTS', "'-1'", '0', 'gg.POINTS')) . ") / sum(" . db_case(array('gg.POINTS', "'-1'", '0', '1')) . ")")) . " AS AVG,sum(CASE WHEN gg.POINTS<=g.POINTS AND gg.STUDENT_ID!=g.STUDENT_ID THEN 1 ELSE 0 END) AS LOWER,sum(CASE WHEN gg.POINTS>g.POINTS THEN 1 ELSE 0 END) AS HIGHER FROM GRADEBOOK_GRADES gg,GRADEBOOK_ASSIGNMENTS ga LEFT OUTER JOIN GRADEBOOK_GRADES g ON (g.COURSE_PERIOD_ID='{$course['COURSE_PERIOD_ID']}' AND g.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND g.STUDENT_ID='" . UserStudentID() . "'),GRADEBOOK_ASSIGNMENT_TYPES at WHERE (ga.COURSE_PERIOD_ID='{$course['COURSE_PERIOD_ID']}' OR ga.COURSE_ID='{$course['COURSE_ID']}' AND ga.STAFF_ID='{$staff_id}') AND ga.MARKING_PERIOD_ID='" . UserMP() . "' AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND at.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND ((ga.ASSIGNED_DATE IS NOT NULL ) OR g.POINTS IS NOT NULL) AND ga.POINTS!='0' GROUP BY ga.ASSIGNMENT_ID"), array(), array('ASSIGNMENT_ID'));
}
//echo '<pre>'; var_dump($all_RET); echo '</pre>';
$LO_columns = array('TITLE' => 'Title', 'CATEGORY' => 'Category', 'POINTS' => 'Points / Possible', 'PERCENT' => 'Percent', 'LETTER' => 'Letter', 'ASSIGNED_DATE' => 'Assigned Date', 'DUE_DATE' => 'Due Date') + ($do_stats ? array('BAR1' => 'Grade Range', 'BAR2' => 'Class Rank') : array());
$LO_ret = array(0 => array());
foreach ($assignments_RET as $assignment) {
if ($do_stats) {
unset($bargraph1);
unset($bargraph2);
if ($all_RET[$assignment['ASSIGNMENT_ID']]) {
$all = $all_RET[$assignment['ASSIGNMENT_ID']][1];
$all_RET1 = DBGet(DBQuery("SELECT g.ASSIGNMENT_ID,g.POINTS FROM GRADEBOOK_GRADES g where g.COURSE_PERIOD_ID='" . $course[COURSE_PERIOD_ID] . "' "));
$count_tot = 0;
foreach ($all_RET1 as $all1) {
if ($assignment['ASSIGNMENT_ID'] == $all1['ASSIGNMENT_ID']) {
$assg_tot[] = $all1['POINTS'];
echo '<FORM action="Modules.php?modname=' . $_REQUEST['modname'] . '&modfunc=save" method="POST">';
DrawHeader('', SubmitButton(_('Drop Course for Selected Students')));
//modif Francois: css WPadmin
echo '<BR /><TABLE class="postbox cellpadding-0 cellspacing-0" style="margin:0 auto;"><TR><TH><H3>' . _('Course to Drop') . '</H3></TH></TR><TR><TD><TABLE style="border-collapse:separate; border-spacing:4px;"><TR><TD> </TD><TD><DIV id=course_div>';
if ($_SESSION['MassDrops.php']) {
$course_title = DBGet(DBQuery("SELECT TITLE FROM COURSES WHERE COURSE_ID='" . $_SESSION['MassDrops.php']['course_id'] . "'"));
$course_title = $course_title[1]['TITLE'];
$period_title = DBGet(DBQuery("SELECT TITLE FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID='" . $_SESSION['MassDrops.php']['course_period_id'] . "'"));
$period_title = $period_title[1]['TITLE'];
echo "{$course_title}<BR />{$period_title}";
}
echo '</DIV>' . '<A HREF="#" onclick=\'window.open("Modules.php?modname=' . $_REQUEST['modname'] . '&modfunc=choose_course","","scrollbars=yes,resizable=yes,width=800,height=400");\'>' . _('Choose a Course') . '</A></TD></TR>';
echo '<TR><TD>' . _('Drop Date') . '</TD><TD>' . PrepareDate(DBDate(), '') . '</TD></TR>';
echo '<TR><TD>' . _('Marking Period') . '</TD><TD>';
echo '<SELECT name=marking_period_id>';
$mp_RET = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,TITLE," . db_case(array('MP', "'FY'", "'0'", "'SEM'", "'1'", "'QTR'", "'2'")) . " AS TBL FROM SCHOOL_MARKING_PERIODS WHERE (MP='FY' OR MP='SEM' OR MP='QTR') AND SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "' ORDER BY TBL,SORT_ORDER"));
foreach ($mp_RET as $mp) {
echo '<OPTION value="' . $mp['MARKING_PERIOD_ID'] . '">' . $mp['TITLE'] . '</OPTION>';
}
echo '</SELECT>';
echo '</TD></TR>';
echo '</TABLE></TD></TR></TABLE><BR />';
}
}
if (empty($_REQUEST['modfunc'])) {
if ($_REQUEST['search_modfunc'] != 'list') {
unset($_SESSION['MassDrops.php']);
}
$extra['link'] = array('FULL_NAME' => false);
$extra['SELECT'] = ",CAST (NULL AS CHAR(1)) AS CHECKBOX";
$extra['functions'] = array('CHECKBOX' => '_makeChooseCheckbox');
$months = array(1 => _('January'), _('February'), _('March'), _('April'), _('May'), _('June'), _('July'), _('August'), _('September'), _('October'), _('November'), _('December'));
$custom_RET = DBGet(DBQuery("SELECT TITLE,ID FROM CUSTOM_FIELDS WHERE ID IN ('200000000','200000003')"), array(), array('ID'));
$extra['SELECT'] = ",ssm.CALENDAR_ID,ssm.START_DATE,ssm.END_DATE";
foreach ($custom_RET as $id => $custom) {
$extra['SELECT'] .= ",CUSTOM_" . $id;
}
// ACTIVE logic taken from GetStuList()
$extra['SELECT'] .= ',' . db_case(array("(ssm.SYEAR='" . UserSyear() . "' AND ('" . DBDate() . "'>=ssm.START_DATE AND ('" . DBDate() . "'<=ssm.END_DATE OR ssm.END_DATE IS NULL)))", 'TRUE', "'Active'", "'Inactive'")) . ' AS STATUS';
$RET = GetStuList($extra);
if (count($RET)) {
$school_RET = DBGet(DBQuery("SELECT TITLE FROM SCHOOLS WHERE ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "'"));
// override default options
$options = "--webpage --quiet -t pdf12 --jpeg --no-links --portrait --footer t --header . --left 0.25in --right 0.25in --top 0.5in --size 8.5x11in --bodyfont Courier --fontsize 8";
$handle = PDFStart($options);
foreach ($RET as $student) {
$calendar_RET = DBGet(DBquery("SELECT " . db_case(array("MINUTES>=" . Config('ATTENDANCE_FULL_DAY_MINUTES'), 'true', "'1.0'", "'0.5'")) . "AS POS,trim(leading '0' from to_char(SCHOOL_DATE,'MM')) AS MON,trim(leading '0' from to_char(SCHOOL_DATE,'DD')) AS DAY FROM ATTENDANCE_CALENDAR WHERE CALENDAR_ID='" . $student['CALENDAR_ID'] . "' AND SCHOOL_DATE>='" . $student['START_DATE'] . "'" . ($student['END_DATE'] ? " AND SCHOOL_DATE<='" . $student['END_DATE'] . "'" : '')), array(), array('MON', 'DAY'));
$attendance_RET = DBGet(DBQuery("SELECT trim(leading '0' from to_char(ap.SCHOOL_DATE,'MM')) AS MON,trim(leading '0' from to_char(ap.SCHOOL_DATE,'DD')) AS DAY,ac.STATE_CODE,ac.SHORT_NAME FROM ATTENDANCE_PERIOD ap,ATTENDANCE_CODES ac,SCHOOL_PERIODS sp WHERE ap.STUDENT_ID='" . $student['STUDENT_ID'] . "' AND ap.PERIOD_ID=sp.PERIOD_ID AND sp.SCHOOL_ID='" . UserSchool() . "' AND sp.SYEAR='" . UserSyear() . "' AND ac.ID=ap.ATTENDANCE_CODE AND sp.ATTENDANCE='Y'"), array(), array('MON', 'DAY'));
//echo '<pre>'; var_dump($calendar_RET); echo '</pre>';
echo '<TABLE class="width-100p">';
echo '<TR><TD class="width-100p center">';
echo '<TABLE style="width:96%">';
echo '<TR><TD class="width-100p center">';
echo '<span class="sizep2"><B>' . $student['FULL_NAME'] . '</B></span>';
echo '</TD><TR>';
echo '</TABLE>';
echo '<TABLE style="width:96%; border: solid 1px">';
echo '<TR class="center"><TD><B>' . _('Student Name') . '</B></TD><TD><B>ID#</B></TD><TD><B>' . _('School') . ' / ' . _('Year') . '</B></TD></TR>';
//modif Francois: school year over one/two calendar years format
echo '<TR><TD class="center">' . $student['FULL_NAME'] . '</TD><TD class="center">' . $student['STUDENT_ID'] . '</TD><TD class="center">' . $school_RET[1]['TITLE'] . ' / ' . FormatSyear(UserSyear(), Config('SCHOOL_SYEAR_OVER_2_YEARS')) . '</TD></TR>';
echo '<TR><TD colspan="3"><span class="sizep1"><B>' . _('Demographics') . '</B></span><TABLE style="width:98%; margin:0 auto;" class="cellpadding-0 cellspacing-0">';
echo '<TR><TD style="text-align:right">' . ParseMLField($custom_RET[200000000][1]['TITLE']) . ': </TD><TD>' . $student['CUSTOM_200000000'] . '</TD><TD style="text-align:right">' . _('Status') . ': </TD><TD>' . _($student['STATUS']) . '</TD></TR>';
<?php
if ($_REQUEST['detailed_view'] == 'true') {
$RET = DBGet(DBQuery("SELECT fst.TRANSACTION_ID AS TRANS_ID,fst.TRANSACTION_ID,fst.STAFF_ID,fst.SHORT_NAME,(SELECT sum(AMOUNT) FROM FOOD_SERVICE_STAFF_TRANSACTION_ITEMS WHERE TRANSACTION_ID=fst.TRANSACTION_ID) AS AMOUNT,fst.BALANCE,to_char(fst.TIMESTAMP,'YYYY-MM-DD') AS DATE,to_char(fst.TIMESTAMP,'HH:MI:SS AM') AS TIME,fst.DESCRIPTION,(SELECT FIRST_NAME||' '||LAST_NAME FROM STAFF WHERE STAFF_ID=fst.STAFF_ID) AS FULL_NAME," . db_case(array('fst.SELLER_ID', "''", 'NULL', "(SELECT FIRST_NAME||' '||LAST_NAME FROM STAFF WHERE STAFF_ID=fst.SELLER_ID)")) . " AS SELLER FROM FOOD_SERVICE_STAFF_TRANSACTIONS fst WHERE SYEAR='" . UserSyear() . "' AND fst.TIMESTAMP BETWEEN '" . $date . "' AND date '" . $date . "' +1 AND SCHOOL_ID='" . UserSchool() . "'" . $where . "ORDER BY fst.TRANSACTION_ID DESC"), array('DATE' => 'ProperDate', 'SHORT_NAME' => 'bump_count'));
foreach ($RET as $key => $value) {
// get details of each transaction
$tmpRET = DBGet(DBQuery("SELECT TRANSACTION_ID AS TRANS_ID,*,'" . $value['SHORT_NAME'] . "' AS TRANSACTION_SHORT_NAME FROM FOOD_SERVICE_STAFF_TRANSACTION_ITEMS WHERE TRANSACTION_ID='" . $value['TRANSACTION_ID'] . "'"), array('SHORT_NAME' => 'bump_items_count'));
// merge transaction and detail records
$RET[$key] = array($value) + $tmpRET;
}
//echo '<pre>'; var_dump($RET); echo '</pre>';
$columns = array('TRANSACTION_ID' => '#', 'FULL_NAME' => 'User', 'DATE' => 'Date', 'TIME' => 'Time', 'BALANCE' => 'Balance', 'DESCRIPTION' => 'Description', 'AMOUNT' => 'Amount', 'SELLER' => 'User');
$group = array(array('TRANSACTION_ID'));
$link['remove']['link'] = PreparePHP_SELF($_REQUEST, array(), array('modfunc' => 'delete'));
$link['remove']['variables'] = array('transaction_id' => 'TRANS_ID', 'item_id' => 'ITEM_ID');
} else {
$RET = DBGet(DBQuery("SELECT fst.TRANSACTION_ID,fst.STAFF_ID,fst.SHORT_NAME,(SELECT sum(AMOUNT) FROM FOOD_SERVICE_STAFF_TRANSACTION_ITEMS WHERE TRANSACTION_ID=fst.TRANSACTION_ID) AS AMOUNT,fst.BALANCE,to_char(fst.TIMESTAMP,'YYYY-MM-DD') AS DATE,to_char(fst.TIMESTAMP,'HH:MI:SS AM') AS TIME,fst.DESCRIPTION,(SELECT FIRST_NAME||' '||LAST_NAME FROM STAFF WHERE STAFF_ID=fst.STAFF_ID) AS FULL_NAME FROM FOOD_SERVICE_STAFF_TRANSACTIONS fst WHERE SYEAR='" . UserSyear() . "' AND fst.TIMESTAMP BETWEEN '" . $date . "' AND date '" . $date . "' +1 AND SCHOOL_ID='" . UserSchool() . "'" . $where . "ORDER BY fst.TRANSACTION_ID DESC"), array('DATE' => 'ProperDate', 'SHORT_NAME' => 'bump_count'));
$columns = array('TRANSACTION_ID' => '#', 'FULL_NAME' => 'User', 'DATE' => 'Date', 'TIME' => 'Time', 'BALANCE' => 'Balance', 'DESCRIPTION' => 'Description', 'AMOUNT' => 'Amount');
}
}
}
} else {
if (!count($types_RET)) {
$_REQUEST['tab_id'] = 'new';
}
}
if (count($types_RET)) {
$tabs = array(array('title' => _('All'), 'link' => "Modules.php?modname={$_REQUEST['modname']}&tab_id=&allow_edit={$_REQUEST['allow_edit']}"));
}
foreach ($types_RET as $id => $type) {
$tabs[] = array('title' => $type[1]['TITLE'], 'link' => "Modules.php?modname={$_REQUEST['modname']}&tab_id={$id}&allow_edit={$_REQUEST['allow_edit']}", 'color' => $type[1]['COLOR']);
$type_options[$id] = !$_REQUEST['tab_id'] && $type[1]['COLOR'] ? array($type[1]['TITLE'], '<FONT color=' . $type[1]['COLOR'] . '>' . $type[1]['TITLE'] . '</FONT>') : $type[1]['TITLE'];
}
if ($_REQUEST['tab_id'] != 'new') {
$sql = "SELECT ASSIGNMENT_ID,TITLE,ASSIGNED_DATE,DUE_DATE,POINTS,COURSE_ID,DESCRIPTION,ASSIGNMENT_TYPE_ID," . db_case(array('(DUE_DATE<ASSIGNED_DATE)', 'TRUE', "'Y'", 'NULL')) . " AS DATE_ERROR," . db_case(array('(ASSIGNED_DATE>(SELECT END_DATE FROM SCHOOL_MARKING_PERIODS WHERE MARKING_PERIOD_ID=\'' . UserMP() . '\'))', 'TRUE', "'Y'", 'NULL')) . " AS ASSIGNED_ERROR," . db_case(array('DUE_DATE>(SELECT END_DATE+1 FROM SCHOOL_MARKING_PERIODS WHERE MARKING_PERIOD_ID=\'' . UserMP() . '\')', 'TRUE', "'Y'", 'NULL')) . " AS DUE_ERROR " . "FROM GRADEBOOK_ASSIGNMENTS " . "WHERE STAFF_ID='" . User('STAFF_ID') . "' AND (COURSE_ID=(SELECT COURSE_ID FROM COURSE_PERIODS WHERE COURSE_PERIOD_ID='" . UserCoursePeriod() . "') OR COURSE_PERIOD_ID='" . UserCoursePeriod() . "')" . ($_REQUEST['tab_id'] ? " AND ASSIGNMENT_TYPE_ID='" . $_REQUEST['tab_id'] . "'" : '') . " AND MARKING_PERIOD_ID='" . UserMP() . "' ORDER BY " . Preferences('ASSIGNMENT_SORTING', 'Gradebook') . " DESC,ASSIGNMENT_ID DESC,TITLE";
$functions = array('TITLE' => '_makeAssnInput', 'POINTS' => '_makeAssnInput', 'ASSIGNED_DATE' => '_makeAssnInput', 'DUE_DATE' => '_makeAssnInput', 'COURSE_ID' => '_makeAssnInput', 'DESCRIPTION' => '_makeAssnInput');
if ($_REQUEST['allow_edit'] == 'Y' || !$_REQUEST['tab_id']) {
$functions['ASSIGNMENT_TYPE_ID'] = '_makeAssnInput';
}
$LO_ret = DBGet(DBQuery($sql), $functions);
$LO_columns = array('TITLE' => _('Title'), 'POINTS' => _('Points'), 'ASSIGNED_DATE' => _('Assigned Date'), 'DUE_DATE' => _('Due Date'), 'COURSE_ID' => _('All'), 'DESCRIPTION' => _('Description'));
if ($_REQUEST['allow_edit'] == 'Y' || !$_REQUEST['tab_id']) {
$LO_columns += array('ASSIGNMENT_TYPE_ID' => _('Type'));
}
$link['add']['html'] = array('TITLE' => _makeAssnInput('', 'TITLE'), 'POINTS' => _makeAssnInput('', 'POINTS'), 'ASSIGNED_DATE' => _makeAssnInput('', 'ASSIGNED_DATE'), 'DUE_DATE' => _makeAssnInput('', 'DUE_DATE'), 'COURSE_ID' => _makeAssnInput('', 'COURSE_ID'), 'DESCRIPTION' => _makeAssnInput('', 'DESCRIPTION'));
if (!$_REQUEST['tab_id']) {
$link['add']['html'] += array('ASSIGNMENT_TYPE_ID' => _makeAssnInput('', 'ASSIGNMENT_TYPE_ID'));
}
$link['remove']['link'] = "Modules.php?modname={$_REQUEST['modname']}&modfunc=remove&tab_id={$_REQUEST['tab_id']}&allow_edit={$_REQUEST['allow_edit']}";
$link['remove']['variables'] = array('id' => 'ASSIGNMENT_ID');
$extra['SELECT'] .= ',\'\' AS POINTS,\'\' AS LETTER_GRADE,\'\' AS COMMENT';
$extra['WHERE'] = ' AND \'' . DBDate('mysql') . '\'>=ssm.START_DATE';
$extra['functions'] = array('POINTS' => '_makeExtraAssnCols', 'LETTER_GRADE' => '_makeExtraAssnCols');
$LO_columns += array('POINTS' => 'Points', 'LETTER_GRADE' => 'Grade');
// this will get the grades for all students ever enrolled in the class
// the "group by start_date" and "distinct on" are needed in case a student is enrolled more than once (re-enrolled)
if ($programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y') {
$points_RET = DBGet(DBQuery('SELECT DISTINCT s.STUDENT_ID, gt.ASSIGNMENT_TYPE_ID,
sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ') AS PARTIAL_POINTS,
sum(' . db_case(array('gg.POINTS', '\'-1\' OR gg.POINTS IS NULL OR (ga.due_date < (select DISTINCT ssm.start_date from student_enrollment ssm where ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=' . UserSchool() . ' AND (ssm.START_DATE IS NOT NULL AND (CURRENT_DATE<=ssm.END_DATE OR CURRENT_DATE>=ssm.END_DATE OR ssm.END_DATE IS NULL)) order by ssm.start_date desc limit 1
) ) ', "'0'", 'ga.POINTS')) . ') AS PARTIAL_TOTAL, gt.FINAL_GRADE_PERCENT FROM students s JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID=\'' . $course_period_id . '\') JOIN gradebook_assignments ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID=\'' . $course_id . '\' AND ga.STAFF_ID=\'' . User('STAFF_ID') . '\') AND ga.MARKING_PERIOD_ID=\'' . UserMP() . '\') LEFT OUTER JOIN gradebook_grades gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID)
,gradebook_assignment_types gt WHERE gt.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND gt.COURSE_ID=\'' . $course_id . '\' AND ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE,gt.ASSIGNMENT_TYPE_ID,gt.FINAL_GRADE_PERCENT'), array(), array('STUDENT_ID'));
} else {
$points_RET = DBGet(DBQuery('SELECT DISTINCT s.STUDENT_ID,\'-1\' AS ASSIGNMENT_TYPE_ID, sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ') AS PARTIAL_POINTS,
sum(' . db_case(array('gg.POINTS', '\'-1\' OR gg.POINTS IS NULL OR (ga.due_date < (select DISTINCT ssm.start_date from student_enrollment ssm where ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=\'' . UserSyear() . '\' AND ssm.SCHOOL_ID=' . UserSchool() . ' AND (ssm.START_DATE IS NOT NULL AND (CURRENT_DATE<=ssm.END_DATE OR CURRENT_DATE>=ssm.END_DATE OR ssm.END_DATE IS NULL)) order by ssm.start_date desc limit 1) ) ', "'0'", 'ga.POINTS')) . ') AS PARTIAL_TOTAL,\'1\' AS FINAL_GRADE_PERCENT
FROM students s JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID=\'' . $course_period_id . '\') JOIN gradebook_assignments ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID=\'' . $course_id . '\' AND ga.STAFF_ID=\'' . User('STAFF_ID') . '\') AND ga.MARKING_PERIOD_ID=\'' . UserMP() . '\') LEFT OUTER JOIN gradebook_grades gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID)
WHERE ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE'), array(), array('STUDENT_ID'));
}
foreach ($assignments_RET as $id => $assignment) {
$total_points[$id] = $assignment[1]['POINTS'];
}
}
}
}
if (clean_param($_REQUEST['values'], PARAM_NOTAGS) && ($_POST['values'] || $_REQUEST['ajax']) && $_SESSION['assignment_id'] == clean_param($_REQUEST['assignment_id'], PARAM_INT)) {
foreach ($_REQUEST['values'] as $student_id => $assignments) {
foreach ($assignments as $assignment_id => $columns) {
if ($columns['POINTS']) {
if ($columns['POINTS'] == '*') {
$columns['POINTS'] = '-1';
function Rollover($table)
{
global $next_syear;
switch ($table) {
case 'staff':
$user_custom = '';
$fields_RET = DBGet(DBQuery("SELECT ID FROM staff_fields"));
foreach ($fields_RET as $field) {
$user_custom .= ',CUSTOM_' . $field['ID'];
}
DBQuery('DELETE FROM students_join_users WHERE STAFF_ID IN (SELECT STAFF_ID FROM staff WHERE SYEAR=' . $next_syear . ')');
DBQuery('DELETE FROM program_user_config WHERE USER_ID IN (SELECT STAFF_ID FROM staff WHERE SYEAR=' . $next_syear . ')');
DBQuery('DELETE FROM staff WHERE SYEAR=\'' . $next_syear . '\'');
DBQuery('INSERT INTO staff (SYEAR,CURRENT_SCHOOL_ID,TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,USERNAME,PASSWORD,PHONE,EMAIL,PROFILE,HOMEROOM,LAST_LOGIN,SCHOOLS,PROFILE_ID,ROLLOVER_ID' . $user_custom . ') SELECT SYEAR+1,CURRENT_SCHOOL_ID,TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,USERNAME,PASSWORD,PHONE,EMAIL,PROFILE,HOMEROOM,NULL,SCHOOLS,PROFILE_ID,STAFF_ID' . $user_custom . ' FROM staff WHERE SYEAR=\'' . UserSyear() . '\'');
DBQuery('INSERT INTO program_user_config (USER_ID,PROGRAM,TITLE,VALUE) SELECT s.STAFF_ID,puc.PROGRAM,puc.TITLE,puc.VALUE FROM staff s,program_user_config puc WHERE puc.USER_ID=s.ROLLOVER_ID AND puc.PROGRAM=\'Preferences\' AND s.SYEAR=\'' . $next_syear . '\'');
DBQuery('INSERT INTO students_join_users (STUDENT_ID,STAFF_ID) SELECT j.STUDENT_ID,s.STAFF_ID FROM staff s,students_join_users j WHERE j.STAFF_ID=s.ROLLOVER_ID AND s.SYEAR=\'' . $next_syear . '\'');
break;
case 'school_periods':
DBQuery('DELETE FROM school_periods WHERE SCHOOL_ID=\'' . UserSchool() . '\' AND SYEAR=\'' . $next_syear . '\'');
DBQuery('INSERT INTO school_periods (SYEAR,SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,ATTENDANCE,ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,ATTENDANCE,PERIOD_ID FROM school_periods WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
break;
case 'school_calendars':
DBQuery('DELETE FROM school_calendars WHERE SCHOOL_ID=\'' . UserSchool() . '\' AND SYEAR=\'' . $next_syear . '\'');
DBQuery('INSERT INTO school_calendars (SYEAR,SCHOOL_ID,TITLE,DEFAULT_CALENDAR,ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID,TITLE,DEFAULT_CALENDAR,CALENDAR_ID FROM school_calendars WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
break;
case 'school_years':
DBQuery('DELETE FROM school_progress_periods WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('DELETE FROM school_quarters WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('DELETE FROM school_semesters WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('DELETE FROM school_years WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
$r = DBGet(DBQuery('select max(m.marking_period_id) as marking_period_id from (select max(marking_period_id) as marking_period_id from school_years union select max(marking_period_id) as marking_period_id from school_semesters union select max(marking_period_id) as marking_period_id from school_quarters) m'));
$mpi = $r[1]['MARKING_PERIOD_ID'] + 1;
DBQuery('ALTER TABLE marking_period_id_generator AUTO_INCREMENT = ' . $mpi . '');
DBQuery('INSERT INTO school_years (MARKING_PERIOD_ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,ROLLOVER_ID) SELECT ' . db_seq_nextval('marking_period_seq') . ',SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,date_add(START_DATE,INTERVAL 365 DAY),date_add(END_DATE,INTERVAL 365 DAY),date_add(POST_START_DATE,INTERVAL 365 DAY),date_add(POST_END_DATE,INTERVAL 365 DAY),DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_years WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('INSERT INTO school_semesters (MARKING_PERIOD_ID,YEAR_ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,ROLLOVER_ID) SELECT ' . db_seq_nextval('marking_period_seq') . ',(SELECT MARKING_PERIOD_ID FROM school_years y WHERE y.SYEAR=s.SYEAR+1 AND y.ROLLOVER_ID=s.YEAR_ID),SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,date_add(START_DATE, INTERVAL 365 DAY),date_add(END_DATE,INTERVAL 365 DAY),date_add(POST_START_DATE,INTERVAL 365 DAY),date_add(POST_END_DATE,INTERVAL 365 DAY),DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_semesters s WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('INSERT INTO school_quarters (MARKING_PERIOD_ID,SEMESTER_ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,ROLLOVER_ID) SELECT ' . db_seq_nextval('marking_period_seq') . ',(SELECT MARKING_PERIOD_ID FROM school_semesters s WHERE s.SYEAR=q.SYEAR+1 AND s.ROLLOVER_ID=q.SEMESTER_ID),SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE+365,END_DATE+365,POST_START_DATE+365,POST_END_DATE+365,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_quarters q WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('INSERT INTO school_progress_periods (MARKING_PERIOD_ID,QUARTER_ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,ROLLOVER_ID) SELECT ' . db_seq_nextval('marking_period_seq') . ',(SELECT MARKING_PERIOD_ID FROM school_quarters q WHERE q.SYEAR=p.SYEAR+1 AND q.ROLLOVER_ID=p.QUARTER_ID),SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,date_add(START_DATE,INTERVAL 365 DAY),date_add(END_DATE,INTERVAL 365 DAY),date_add(POST_START_DATE,INTERVAL 365 DAY),date_add(POST_END_DATE,INTERVAL 365 DAY),DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_progress_periods p WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
break;
case 'courses':
DBQuery('DELETE FROM course_subjects WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('DELETE FROM courses WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('DELETE FROM course_periods WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
// ROLL course_subjects
DBQuery('INSERT INTO course_subjects (SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID,TITLE,SHORT_NAME,SUBJECT_ID FROM course_subjects WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
// ROLL COURSE WEIGHTS
DBQuery('INSERT INTO courses (SYEAR,SUBJECT_ID,SCHOOL_ID,GRADE_LEVEL,TITLE,SHORT_NAME,ROLLOVER_ID) SELECT SYEAR+1,(SELECT SUBJECT_ID FROM course_subjects s WHERE s.SYEAR=c.SYEAR+1 AND s.ROLLOVER_ID=c.SUBJECT_ID),SCHOOL_ID,GRADE_LEVEL,TITLE,SHORT_NAME,COURSE_ID FROM courses c WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
// ROLL courses
/*DBQuery("INSERT INTO COURSE_WEIGHTS (SYEAR,SCHOOL_ID,COURSE_ID,GPA_MULTIPLIER,COURSE_WEIGHT) SELECT SYEAR+1,SCHOOL_ID,(SELECT COURSE_ID FROM courses c WHERE c.SYEAR=w.SYEAR+1 AND c.ROLLOVER_ID=w.COURSE_ID),GPA_MULTIPLIER,COURSE_WEIGHT FROM COURSE_WEIGHTS w WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'");*/
// ROLL course_periods
DBQuery('INSERT INTO course_periods (SYEAR,SCHOOL_ID,COURSE_ID,COURSE_WEIGHT,TITLE,
SHORT_NAME,PERIOD_ID,MP,MARKING_PERIOD_ID,TEACHER_ID,ROOM,
TOTAL_SEATS,FILLED_SEATS,DOES_ATTENDANCE,GRADE_SCALE_ID,DOES_HONOR_ROLL,
DOES_CLASS_RANK,DOES_BREAKOFF,GENDER_RESTRICTION,HOUSE_RESTRICTION,CREDITS,
AVAILABILITY,DAYS,HALF_DAY,PARENT_ID,CALENDAR_ID,
ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID,
(SELECT COURSE_ID FROM courses c WHERE c.SYEAR=p.SYEAR+1 AND c.ROLLOVER_ID=p.COURSE_ID),
COURSE_WEIGHT,TITLE,SHORT_NAME,(SELECT PERIOD_ID FROM school_periods n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.PERIOD_ID),MP,' . db_case(array('MP', "'FY'", '(SELECT MARKING_PERIOD_ID FROM school_years n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.MARKING_PERIOD_ID)', "'SEM'", '(SELECT MARKING_PERIOD_ID FROM school_semesters n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.MARKING_PERIOD_ID)', "'QTR'", '(SELECT MARKING_PERIOD_ID FROM school_quarters n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.MARKING_PERIOD_ID)')) . ',(SELECT STAFF_ID FROM staff n WHERE n.SYEAR=p.SYEAR+1 AND n.ROLLOVER_ID=p.TEACHER_ID),ROOM,TOTAL_SEATS,0 AS FILLED_SEATS,DOES_ATTENDANCE,(SELECT ID FROM report_card_grade_scales n WHERE n.ROLLOVER_ID=p.GRADE_SCALE_ID AND n.SCHOOL_ID=' . UserSchool() . '),DOES_HONOR_ROLL,DOES_CLASS_RANK,DOES_BREAKOFF,GENDER_RESTRICTION,HOUSE_RESTRICTION,CREDITS,AVAILABILITY,DAYS,HALF_DAY,PARENT_ID,(SELECT CALENDAR_ID FROM school_calendars n WHERE n.ROLLOVER_ID=p.CALENDAR_ID),COURSE_PERIOD_ID FROM course_periods p WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
$rowq = DBQUERY('SELECT * FROM course_periods WHERE ROLLOVER_ID=PARENT_ID');
DBQuery('UPDATE course_periods SET PARENT_ID=\'' . $rowq['course_period_id'] . '\' WHERE PARENT_ID IS NOT NULL AND SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
break;
case 'student_enrollment':
$next_start_date = DBDate();
DBQuery('DELETE FROM student_enrollment WHERE SYEAR=\'' . $next_syear . '\' AND LAST_SCHOOL=\'' . UserSchool() . '\'');
// ROLL STUDENTS TO NEXT GRADE
DBQuery('INSERT INTO student_enrollment (SYEAR,SCHOOL_ID,STUDENT_ID,GRADE_ID,START_DATE,END_DATE,ENROLLMENT_CODE,DROP_CODE,CALENDAR_ID,LAST_SCHOOL) SELECT SYEAR+1,SCHOOL_ID,STUDENT_ID,(SELECT NEXT_GRADE_ID FROM school_gradelevels g WHERE g.ID=e.GRADE_ID),\'' . $next_start_date . '\' AS START_DATE,NULL AS END_DATE,NULL AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM school_calendars WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_ID FROM student_enrollment e WHERE e.SYEAR=\'' . UserSyear() . '\' AND e.SCHOOL_ID=\'' . UserSchool() . '\' AND ((\'' . DBDate() . '\' BETWEEN e.START_DATE AND e.END_DATE OR e.END_DATE IS NULL) AND \'' . DBDate() . '\'>=e.START_DATE) AND e.NEXT_SCHOOL=\'' . UserSchool() . '\'');
// ROLL STUDENTS WHO ARE TO BE RETAINED
DBQuery('INSERT INTO student_enrollment (SYEAR,SCHOOL_ID,STUDENT_ID,GRADE_ID,START_DATE,END_DATE,ENROLLMENT_CODE,DROP_CODE,CALENDAR_ID,LAST_SCHOOL) SELECT SYEAR+1,SCHOOL_ID,STUDENT_ID,GRADE_ID,\'' . $next_start_date . '\' AS START_DATE,NULL AS END_DATE,NULL AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM school_calendars WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_ID FROM student_enrollment e WHERE e.SYEAR=\'' . UserSyear() . '\' AND e.SCHOOL_ID=\'' . UserSchool() . '\' AND ((\'' . DBDate() . '\' BETWEEN e.START_DATE AND e.END_DATE OR e.END_DATE IS NULL) AND \'' . DBDate() . '\'>=e.START_DATE) AND e.NEXT_SCHOOL=\'0\'');
// ROLL STUDENTS TO NEXT SCHOOL
DBQuery('INSERT INTO student_enrollment (SYEAR,SCHOOL_ID,STUDENT_ID,GRADE_ID,START_DATE,END_DATE,ENROLLMENT_CODE,DROP_CODE,CALENDAR_ID,LAST_SCHOOL) SELECT SYEAR+1,NEXT_SCHOOL,STUDENT_ID,(SELECT g.ID FROM school_gradelevels g WHERE g.SORT_ORDER=1 AND g.SCHOOL_ID=e.NEXT_SCHOOL),\'' . $next_start_date . '\' AS START_DATE,NULL AS END_DATE,NULL AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM school_calendars WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_ID FROM student_enrollment e WHERE e.SYEAR=\'' . UserSyear() . '\' AND e.SCHOOL_ID=\'' . UserSchool() . '\' AND ((\'' . DBDate() . '\' BETWEEN e.START_DATE AND e.END_DATE OR e.END_DATE IS NULL) AND \'' . DBDate() . '\'>=e.START_DATE) AND e.NEXT_SCHOOL NOT IN (\'' . UserSchool() . '\',\'0\',\'-1\')');
break;
case 'report_card_grade_scales':
DBQuery('DELETE FROM report_card_grade_scales WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('DELETE FROM report_card_grades WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('INSERT INTO report_card_grade_scales (SYEAR,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ID FROM report_card_grade_scales WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('INSERT INTO report_card_grades (SYEAR,SCHOOL_ID,TITLE,COMMENT,BREAK_OFF,GPA_VALUE,GRADE_SCALE_ID,SORT_ORDER) SELECT SYEAR+1,SCHOOL_ID,TITLE,COMMENT,BREAK_OFF,GPA_VALUE,(SELECT ID FROM report_card_grade_scales WHERE ROLLOVER_ID=GRADE_SCALE_ID AND SCHOOL_ID=report_card_grades.SCHOOL_ID),SORT_ORDER FROM report_card_grades WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
break;
case 'report_card_comments':
DBQuery('DELETE FROM report_card_comments WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('INSERT INTO report_card_comments (SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID) SELECT SYEAR+1,SCHOOL_ID,TITLE,SORT_ORDER,' . db_case(array('COURSE_ID', "''", 'NULL', "(SELECT COURSE_ID FROM courses WHERE ROLLOVER_ID=rc.COURSE_ID)")) . ' FROM report_card_comments rc WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
break;
case 'eligibility_activities':
case 'attendance_codes':
DBQuery('DELETE FROM ' . $table . ' WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
$table_properties = db_properties($table);
$columns = '';
foreach ($table_properties as $column => $values) {
if ($column != 'ID' && $column != 'SYEAR') {
$columns .= ',' . $column;
}
}
DBQuery('INSERT INTO ' . $table . ' (SYEAR' . $columns . ') SELECT SYEAR+1' . $columns . ' FROM ' . $table . ' WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
break;
// DOESN'T HAVE A SCHOOL_ID
// DOESN'T HAVE A SCHOOL_ID
case 'student_enrollment_codes':
DBQuery('DELETE FROM ' . $table . ' WHERE SYEAR=\'' . $next_syear . '\'');
$table_properties = db_properties($table);
$columns = '';
foreach ($table_properties as $column => $values) {
if ($column != 'ID' && $column != 'SYEAR') {
$columns .= ',' . $column;
}
}
DBQuery('INSERT INTO ' . $table . ' (SYEAR' . $columns . ') SELECT SYEAR+1' . $columns . ' FROM ' . $table . ' WHERE SYEAR=\'' . UserSyear() . '\'');
break;
}
// ---------------------------------------------------------------------- data write start ----------------------------------------------------------------------- //
$string .= "<" . "?php \n";
$string .= "\$" . "DatabaseType = '" . $_SESSION['DT'] . "'; \n";
$string .= "\$" . "DatabaseServer = '" . $_SESSION['DS'] . "'; \n";
$string .= "\$" . "DatabaseUsername = '******'DU'] . "'; \n";
$string .= "\$" . "DatabasePassword = '******'DP'] . "'; \n";
$string .= "\$" . "DatabaseName = '" . $_SESSION['DB'] . "'; \n";
$string .= "\$" . "DatabasePort = '" . $_SESSION['DBP'] . "'; \n";
$string .= "\$" . "DefaultSyear = '" . $_SESSION['NY'] . "'; \n";
$string .= "?" . ">";
$err = "Can't write to file";
$myFile = "Data.php";
$fh = fopen($myFile, 'w') or exit($err);
fwrite($fh, $string);
fclose($fh);
// ---------------------------------------------------------------------- data write end ------------------------------------------------------------------------ //
}
function GetStuList(&$extra = array())
{
global $contacts_RET, $view_other_RET, $_ROSARIO;
if ((empty($extra['SELECT_ONLY']) || mb_strpos($extra['SELECT_ONLY'], 'GRADE_ID') !== false) && !isset($extra['functions']['GRADE_ID'])) {
$functions = array('GRADE_ID' => 'GetGrade');
} else {
$functions = array();
}
if (isset($extra['functions'])) {
$functions += $extra['functions'];
}
if (!isset($extra['MP']) && !isset($extra['DATE'])) {
$extra['MP'] = UserMP();
$extra['DATE'] = DBDate();
} elseif (!$extra['MP']) {
$extra['MP'] = GetCurrentMP('QTR', $extra['DATE'], false);
} elseif (!$extra['DATE']) {
$extra['DATE'] = DBDate();
}
if (isset($_REQUEST['expanded_view']) && $_REQUEST['expanded_view'] == 'true') {
if (!$extra['columns_after']) {
$extra['columns_after'] = array();
}
$view_fields_RET = DBGet(DBQuery("SELECT cf.ID,cf.TYPE,cf.TITLE FROM CUSTOM_FIELDS cf WHERE ((SELECT VALUE FROM PROGRAM_USER_CONFIG WHERE TITLE=cast(cf.ID AS TEXT) AND PROGRAM='StudentFieldsView' AND USER_ID='" . User('STAFF_ID') . "')='Y'" . ($extra['student_fields']['view'] ? " OR cf.ID IN (" . $extra['student_fields']['view'] . ")" : '') . ") ORDER BY cf.SORT_ORDER,cf.TITLE"));
$view_address_RET = DBGet(DBQuery("SELECT VALUE FROM PROGRAM_USER_CONFIG WHERE PROGRAM='StudentFieldsView' AND TITLE='ADDRESS' AND USER_ID='" . User('STAFF_ID') . "'"));
$view_address_RET = $view_address_RET[1]['VALUE'];
$view_other_RET = DBGet(DBQuery("SELECT TITLE,VALUE FROM PROGRAM_USER_CONFIG WHERE PROGRAM='StudentFieldsView' AND TITLE IN ('CONTACT_INFO','HOME_PHONE','GUARDIANS','ALL_CONTACTS') AND USER_ID='" . User('STAFF_ID') . "'"), array(), array('TITLE'));
if (!count($view_fields_RET) && !isset($view_address_RET) && !isset($view_other_RET['CONTACT_INFO'])) {
//modif Francois: add translation
$extra['columns_after'] = array('CONTACT_INFO' => '<IMG SRC="assets/down_phone_button.png" width="24">', 'CUSTOM_200000000' => _('Gender'), 'CUSTOM_200000001' => _('Ethnicity'), 'ADDRESS' => _('Mailing Address'), 'CITY' => _('City'), 'STATE' => _('State'), 'ZIPCODE' => _('Zipcode')) + $extra['columns_after'];
$select = ',ssm.STUDENT_ID AS CONTACT_INFO,s.CUSTOM_200000000,s.CUSTOM_200000001,coalesce(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,coalesce(a.MAIL_CITY,a.CITY) AS CITY,coalesce(a.MAIL_STATE,a.STATE) AS STATE,coalesce(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE ';
$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam.RESIDENCE='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];
$functions['CONTACT_INFO'] = 'makeContactInfo';
$RET = DBGet(DBQuery("SELECT ID,TYPE FROM CUSTOM_FIELDS WHERE ID IN ('200000000','200000001')"), array(), array('ID'));
// if gender and ethnicity are converted to codeds or exports type
if ($RET['200000000'][1]['TYPE'] == 'codeds' || $RET['200000000'][1]['TYPE'] == 'exports') {
$functions['CUSTOM_200000000'] = 'DeCodeds';
}
if ($RET['200000001'][1]['TYPE'] == 'codeds' || $RET['200000001'][1]['TYPE'] == 'exports') {
$functions['CUSTOM_200000001'] = 'DeCodeds';
}
$extra['singular'] = 'Student Address';
$extra['plural'] = 'Student Addresses';
$extra2['NoSearchTerms'] = true;
$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,a.PHONE,sjp.ADDRESS_ID ';
$extra2['FROM'] .= ',ADDRESS a,STUDENTS_JOIN_ADDRESS sja LEFT OUTER JOIN STUDENTS_JOIN_PEOPLE sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\')) LEFT OUTER JOIN PEOPLE p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN PEOPLE_JOIN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';
$extra2['WHERE'] .= ' AND a.ADDRESS_ID=sja.ADDRESS_ID AND sja.STUDENT_ID=ssm.STUDENT_ID ';
$extra2['ORDER_BY'] .= 'sjp.CUSTODY';
$extra2['group'] = array('STUDENT_ID', 'PERSON_ID');
// EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF
if (!isset($_REQUEST['_ROSARIO_PDF'])) {
$expanded_view = $_REQUEST['expanded_view'];
$_REQUEST['expanded_view'] = false;
$addr = $_REQUEST['addr'];
unset($_REQUEST['addr']);
$contacts_RET = GetStuList($extra2);
$_REQUEST['expanded_view'] = $expanded_view;
$_REQUEST['addr'] = $addr;
} else {
unset($extra2['columns_after']['CONTACT_INFO']);
}
} else {
if ($view_other_RET['CONTACT_INFO'][1]['VALUE'] == 'Y' && !isset($_REQUEST['_ROSARIO_PDF'])) {
$select .= ',ssm.STUDENT_ID AS CONTACT_INFO ';
$extra['columns_after']['CONTACT_INFO'] = '<IMG SRC="assets/down_phone_button.png" width="24">';
$functions['CONTACT_INFO'] = 'makeContactInfo';
$extra2 = $extra;
$extra2['NoSearchTerms'] = true;
$extra2['SELECT'] = '';
$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,a.PHONE,sjp.ADDRESS_ID ';
$extra2['FROM'] .= ',ADDRESS a,STUDENTS_JOIN_ADDRESS sja LEFT OUTER JOIN STUDENTS_JOIN_PEOPLE sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\')) LEFT OUTER JOIN PEOPLE p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN PEOPLE_JOIN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';
$extra2['WHERE'] .= ' AND a.ADDRESS_ID=sja.ADDRESS_ID AND sja.STUDENT_ID=ssm.STUDENT_ID ';
$extra2['ORDER_BY'] .= 'sjp.CUSTODY';
$extra2['group'] = array('STUDENT_ID', 'PERSON_ID');
$extra2['functions'] = array();
$extra2['link'] = array();
// EXPANDED VIEW AND ADDR BREAKS THIS QUERY ... SO, TURN 'EM OFF
$expanded_view = $_REQUEST['expanded_view'];
$_REQUEST['expanded_view'] = false;
$addr = $_REQUEST['addr'];
unset($_REQUEST['addr']);
$contacts_RET = GetStuList($extra2);
$_REQUEST['expanded_view'] = $expanded_view;
$_REQUEST['addr'] = $addr;
}
foreach ($view_fields_RET as $field) {
$extra['columns_after']['CUSTOM_' . $field['ID']] = $field['TITLE'];
if ($field['TYPE'] == 'date') {
$functions['CUSTOM_' . $field['ID']] = 'ProperDate';
} elseif ($field['TYPE'] == 'numeric') {
$functions['CUSTOM_' . $field['ID']] = 'removeDot00';
} elseif ($field['TYPE'] == 'codeds') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
} elseif ($field['TYPE'] == 'exports') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
}
$select .= ',s.CUSTOM_' . $field['ID'];
}
if ($view_address_RET) {
$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam." . $view_address_RET . "='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];
$extra['columns_after'] += array('ADDRESS' => _(ucwords(mb_strtolower(str_replace('_', ' ', $view_address_RET)))) . ' ' . _('Address'), 'CITY' => _('City'), 'STATE' => _('State'), 'ZIPCODE' => _('Zipcode'));
if ($view_address_RET != 'MAILING') {
$select .= ",a.ADDRESS_ID,a.ADDRESS,a.CITY,a.STATE,a.ZIPCODE,a.PHONE,ssm.STUDENT_ID AS PARENTS";
} else {
$select .= ",a.ADDRESS_ID,coalesce(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,coalesce(a.MAIL_CITY,a.CITY) AS CITY,coalesce(a.MAIL_STATE,a.STATE) AS STATE,coalesce(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE,a.PHONE,ssm.STUDENT_ID AS PARENTS ";
}
$extra['singular'] = 'Student Address';
$extra['plural'] = 'Student Addresses';
if ($view_other_RET['HOME_PHONE'][1]['VALUE'] == 'Y') {
$functions['PHONE'] = 'makePhone';
$extra['columns_after']['PHONE'] = _('Home Phone');
}
if ($view_other_RET['GUARDIANS'][1]['VALUE'] == 'Y' || $view_other_RET['ALL_CONTACTS'][1]['VALUE'] == 'Y') {
$functions['PARENTS'] = 'makeParents';
if ($view_other_RET['ALL_CONTACTS'][1]['VALUE'] == 'Y') {
$extra['columns_after']['PARENTS'] = _('Contacts');
} else {
$extra['columns_after']['PARENTS'] = _('Guardians');
}
}
} elseif ($_REQUEST['addr'] || $extra['addr']) {
$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID " . $extra['STUDENTS_JOIN_ADDRESS'] . ") LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];
$distinct = 'DISTINCT ';
}
}
$extra['SELECT'] .= $select;
} else {
if (isset($extra['student_fields']['view'])) {
if (!$extra['columns_after']) {
$extra['columns_after'] = array();
}
$view_fields_RET = DBGet(DBQuery("SELECT cf.ID,cf.TYPE,cf.TITLE FROM CUSTOM_FIELDS cf WHERE cf.ID IN (" . $extra['student_fields']['view'] . ") ORDER BY cf.SORT_ORDER,cf.TITLE"));
foreach ($view_fields_RET as $field) {
$extra['columns_after']['CUSTOM_' . $field['ID']] = $field['TITLE'];
if ($field['TYPE'] == 'date') {
$functions['CUSTOM_' . $field['ID']] = 'ProperDate';
} elseif ($field['TYPE'] == 'numeric') {
$functions['CUSTOM_' . $field['ID']] = 'removeDot00';
} elseif ($field['TYPE'] == 'codeds') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
} elseif ($field['TYPE'] == 'exports') {
$functions['CUSTOM_' . $field['ID']] = 'DeCodeds';
}
$select .= ',s.CUSTOM_' . $field['ID'];
}
$extra['SELECT'] .= $select;
}
if (!empty($_REQUEST['addr']) || !empty($extra['addr'])) {
$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID " . $extra['STUDENTS_JOIN_ADDRESS'] . ") LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) " . $extra['FROM'];
$distinct = 'DISTINCT ';
}
}
switch (User('PROFILE')) {
case 'admin':
$sql = 'SELECT ';
//$sql = 'SELECT '.$distinct;
if (isset($extra['SELECT_ONLY'])) {
$sql .= $extra['SELECT_ONLY'];
} else {
$sql .= "s.LAST_NAME||', '||s.FIRST_NAME||' '||coalesce(s.MIDDLE_NAME,' ') AS FULL_NAME,";
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
if (isset($_REQUEST['include_inactive']) && $_REQUEST['include_inactive'] == 'Y') {
$sql .= ',' . db_case(array("(ssm.SYEAR='" . UserSyear() . "' AND ('" . $extra['DATE'] . "'>=ssm.START_DATE AND ('" . $extra['DATE'] . "'<=ssm.END_DATE OR ssm.END_DATE IS NULL)))", 'TRUE', '\'<span style="color:green">' . _('Active') . '</span>\'', '\'<span style="color:red">' . _('Inactive') . '</span>\'')) . ' AS ACTIVE';
}
}
$sql .= " FROM STUDENTS s JOIN STUDENT_ENROLLMENT ssm ON (ssm.STUDENT_ID=s.STUDENT_ID";
if (isset($_REQUEST['include_inactive']) && $_REQUEST['include_inactive'] == 'Y') {
//$sql .= " AND ssm.ID=(SELECT max(ID) FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<='".UserSyear()."')";
$sql .= " AND ssm.ID=(SELECT ID FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<='" . UserSyear() . "' ORDER BY SYEAR DESC,START_DATE DESC LIMIT 1)";
} else {
$sql .= " AND ssm.SYEAR='" . UserSyear() . "' AND ('" . $extra['DATE'] . "'>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE))";
}
if (UserSchool() && $_REQUEST['_search_all_schools'] != 'Y') {
$sql .= " AND ssm.SCHOOL_ID='" . UserSchool() . "'";
} else {
if (User('SCHOOLS')) {
$sql .= " AND ssm.SCHOOL_ID IN (" . mb_substr(str_replace(',', "','", User('SCHOOLS')), 2, -2) . ") ";
}
$extra['columns_after']['LIST_SCHOOL_ID'] = 'School';
$functions['LIST_SCHOOL_ID'] = 'GetSchool';
}
$sql .= ")" . $extra['FROM'] . " WHERE TRUE";
if (empty($extra['SELECT_ONLY']) && isset($_REQUEST['include_inactive']) && $_REQUEST['include_inactive'] == 'Y') {
$extra['columns_after']['ACTIVE'] = _('Status');
}
break;
case 'teacher':
$sql = 'SELECT ';
//$sql = 'SELECT '.$distinct;
if ($extra['SELECT_ONLY']) {
$sql .= $extra['SELECT_ONLY'];
} else {
$sql .= "s.LAST_NAME||', '||s.FIRST_NAME||' '||coalesce(s.MIDDLE_NAME,' ') AS FULL_NAME,";
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= ',' . db_case(array("('" . $extra['DATE'] . "'>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE))", 'TRUE', '\'<span style="color:green">' . _('Active') . '</span>\'', '\'<span style="color:red">' . _('Inactive') . '</span>\'')) . ' AS ACTIVE';
$sql .= ',' . db_case(array("('" . $extra['DATE'] . "'>=ss.START_DATE AND (ss.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ss.END_DATE)) AND ss.MARKING_PERIOD_ID IN (" . GetAllMP($extra['MPTable'], $extra['MP']) . ")", 'TRUE', '\'<span style="color:green">' . _('Active') . '</span>\'', '\'<span style="color:red">' . _('Inactive') . '</span>\'')) . ' AS ACTIVE_SCHEDULE';
}
}
$sql .= " FROM STUDENTS s JOIN SCHEDULE ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.SYEAR='" . UserSyear() . "'";
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= " AND ss.START_DATE=(SELECT START_DATE FROM SCHEDULE WHERE STUDENT_ID=s.STUDENT_ID AND SYEAR=ss.SYEAR AND COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID ORDER BY START_DATE DESC LIMIT 1)";
} else {
$sql .= " AND ss.MARKING_PERIOD_ID IN (" . GetAllMP($extra['MPTable'], $extra['MP']) . ") AND ('" . $extra['DATE'] . "'>=ss.START_DATE AND ('" . $extra['DATE'] . "'<=ss.END_DATE OR ss.END_DATE IS NULL))";
}
$sql .= ") JOIN COURSE_PERIODS cp ON (cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID AND " . ($extra['all_courses'] == 'Y' ? "cp.TEACHER_ID='" . User('STAFF_ID') . "'" : "cp.COURSE_PERIOD_ID='" . UserCoursePeriod() . "'") . ")\n\t\t\t\tJOIN STUDENT_ENROLLMENT ssm ON (ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR=ss.SYEAR AND ssm.SCHOOL_ID='" . UserSchool() . "'";
if ($_REQUEST['include_inactive'] == 'Y') {
$sql .= " AND ssm.ID=(SELECT ID FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR=ssm.SYEAR ORDER BY START_DATE DESC LIMIT 1)";
} else {
$sql .= " AND ('" . $extra['DATE'] . "'>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE))";
}
$sql .= ")" . $extra['FROM'] . " WHERE TRUE";
if (!$extra['SELECT_ONLY'] && $_REQUEST['include_inactive'] == 'Y') {
$extra['columns_after']['ACTIVE'] = _('School Status');
$extra['columns_after']['ACTIVE_SCHEDULE'] = _('Course Status');
}
break;
case 'parent':
case 'student':
$sql = 'SELECT ';
if ($extra['SELECT_ONLY']) {
$sql .= $extra['SELECT_ONLY'];
} else {
$sql .= "s.LAST_NAME||', '||s.FIRST_NAME||' '||coalesce(s.MIDDLE_NAME,' ') AS FULL_NAME,";
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ' . $extra['SELECT'];
}
$sql .= " FROM STUDENTS s JOIN STUDENT_ENROLLMENT ssm ON (ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR='" . UserSyear() . "' AND ssm.SCHOOL_ID='" . UserSchool() . "'\n\t\t\t\t\tAND ('" . $extra['DATE'] . "'>=ssm.START_DATE AND (ssm.END_DATE IS NULL OR '" . $extra['DATE'] . "'<=ssm.END_DATE)) AND s.STUDENT_ID" . ($extra['ASSOCIATED'] ? " IN (SELECT STUDENT_ID FROM STUDENTS_JOIN_USERS WHERE STAFF_ID='" . $extra['ASSOCIATED'] . "')" : "='" . UserStudentID() . "'");
$sql .= ")" . $extra['FROM'] . " WHERE TRUE";
break;
default:
exit(_('Error'));
}
//$sql = appendSQL($sql,array('NoSearchTerms'=>$extra['NoSearchTerms']));
$sql .= ' ' . $extra['WHERE'] . ' ';
if (isset($extra['GROUP'])) {
$sql .= ' GROUP BY ' . $extra['GROUP'];
}
if (!isset($extra['ORDER_BY']) && !isset($extra['SELECT_ONLY'])) {
$sql .= ' ORDER BY ';
if (Preferences('SORT') == 'Grade') {
$sql .= '(SELECT SORT_ORDER FROM SCHOOL_GRADELEVELS WHERE ID=ssm.GRADE_ID),';
}
// it would be easier to sort on full_name but postgres sometimes yields strange results
$sql .= 's.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME';
$sql .= $extra['ORDER'];
} elseif (isset($extra['ORDER_BY'])) {
$sql .= ' ORDER BY ' . $extra['ORDER_BY'];
}
//modif Francois: bugfix if PDF, dont echo SQL
if (!isset($_REQUEST['_ROSARIO_PDF']) && 0) {
//activate only for debug purpose
echo '<!--' . $sql . '-->';
}
return DBGet(DBQuery($sql), $functions, $extra['group']);
}
} else {
if ($MP_TYPE == 'quarter') {
$MP_TYPE = 'QTR';
} else {
$MP_TYPE = '';
}
}
}
if ($_REQUEST['period_id']) {
$sql = "SELECT\r\n cp.TITLE as COURSE_PERIOD,sp.TITLE as PERIOD,cp.PERIOD_ID, cp.COURSE_PERIOD_ID\r\n FROM\r\n SCHEDULE s,COURSES c,COURSE_PERIODS cp,SCHOOL_PERIODS sp\r\n WHERE\r\n s.COURSE_ID = c.COURSE_ID AND s.COURSE_ID = cp.COURSE_ID\r\n AND s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID AND cp.PERIOD_ID = sp.PERIOD_ID AND cp.DOES_ATTENDANCE='Y'\r\n AND s.SYEAR = c.SYEAR AND cp.MARKING_PERIOD_ID IN (" . GetAllMP($MP_TYPE, UserMP()) . ")\r\n AND s.STUDENT_ID='" . UserStudentID() . "' AND s.SYEAR='" . UserSyear() . "'\r\n AND ('" . date('Y-m-d', strtotime(DBDate())) . "' BETWEEN s.START_DATE AND s.END_DATE OR s.END_DATE IS NULL)\r\n ORDER BY sp.SORT_ORDER\r\n ";
$schedule_RET = DBGet(DBQuery($sql));
$sql = "SELECT ap.SCHOOL_DATE,ap.COURSE_PERIOD_ID,ac.SHORT_NAME,ac.STATE_CODE,ac.DEFAULT_CODE FROM ATTENDANCE_PERIOD ap,ATTENDANCE_CODES ac WHERE ap.SCHOOL_DATE BETWEEN '" . date('Y-m-d', strtotime($start_date)) . "' AND '" . date('Y-m-d', strtotime($end_date)) . "' AND ap.ATTENDANCE_CODE=ac.ID AND ap.STUDENT_ID='" . UserStudentID() . "'";
$attendance_RET = DBGet(DBQuery($sql), array(), array('SCHOOL_DATE', 'COURSE_PERIOD_ID'));
} else {
$schedule_RET[1] = array('COURSE_PERIOD' => 'Daily Attendance', 'COURSE_PERIOD_ID' => '0');
$attendance_RET = DBGet(DBQuery("SELECT ad.SCHOOL_DATE,'0' AS COURSE_PERIOD_ID,ad.STATE_VALUE AS STATE_CODE," . db_case(array('ad.STATE_VALUE', "'0.0'", "'A'", "'1.0'", "'P'", "'H'")) . " AS SHORT_NAME FROM ATTENDANCE_DAY ad WHERE ad.SCHOOL_DATE BETWEEN '" . date('Y-m-d', strtotime($start_date)) . "' AND '" . date('Y-m-d', strtotime($end_date)) . "' AND ad.STUDENT_ID='" . UserStudentID() . "'"), array(), array('SCHOOL_DATE', 'COURSE_PERIOD_ID'));
}
$i = 0;
if (count($schedule_RET)) {
foreach ($schedule_RET as $course) {
$i++;
$student_RET[$i]['TITLE'] = $course['COURSE_PERIOD'];
foreach ($cal_RET as $value) {
$student_RET[$i][$value['SHORT_DATE']] = _makePeriodColor($attendance_RET[$value['SCHOOL_DATE']][$course['COURSE_PERIOD_ID']][1]['SHORT_NAME'], $attendance_RET[$value['SCHOOL_DATE']][$course['COURSE_PERIOD_ID']][1]['STATE_CODE'], $attendance_RET[$value['SCHOOL_DATE']][$course['COURSE_PERIOD_ID']][1]['DEFAULT_CODE']);
}
}
}
$columns = array('TITLE' => 'Course');
if (count($cal_RET)) {
foreach ($cal_RET as $value) {
$columns[$value['SHORT_DATE']] = ShortDate($value['SCHOOL_DATE']);
<?php
DrawHeader(ProgramTitle());
$begin_date = $_REQUEST['day_begin'] . '-' . $_REQUEST[month_begin] . '-' . $_REQUEST[year_begin];
$end_date = $_REQUEST[day_end] . '-' . $_REQUEST[month_end] . '-' . $_REQUEST[year_end];
DrawHeader(ProperDate($begin_date) . ' - ' . ProperDate($end_date) . ' : ' . GetSchool($_REQUEST[sch]));
// LUNCH DEBITS
$sql = "SELECT MENU_ID," . db_case(array('FSC', "''", "'REG'", "'1'", "'FREE'", "'2'", "'REDUCED'")) . " as FSC,\n\t\t\tcount(*) as COUNT \n\t\tFROM STU_BILLING_ACT_LUNCH sba,STUDENT_ENROLLMENT se \n\t\tWHERE se.SYEAR=sba.SYEAR AND se.STUDENT_ID=sba.STUDENT_ID AND se.SCHOOL_ID='" . UserSchool() . "' AND ";
$sql .= "PAYMENT_DATE BETWEEN '{$begin_date}' AND '{$end_date}'\n\t\tGROUP BY MENU_ID,FSC";
$QI = DBQuery($sql);
$counts_RET = DBGet($QI, array(), array('MENU_ID', 'FSC'));
$sql = "SELECT ID,SCHOOL_ID,TITLE FROM LUNCH_MENU WHERE SCHOOL_ID='" . UserSchool() . "' ";
$QI = DBQuery($sql);
$menu_RET = DBGet($QI, array(), array('SCHOOL_ID'));
$columns = array('TITLE' => 'Lunch Item', 'REG_COUNT' => 'Regular Purchases', 'FREE_COUNT' => 'Free Purchases', 'REDUCED_COUNT' => 'Reduced Purchases', 'TOTAL' => 'Total');
foreach ($menu_RET as $school => $items) {
unset($RET);
$RET[] = '';
foreach ($items as $item) {
$total = $counts_RET[$item[ID]]['REG'][1]['COUNT'] + $counts_RET[$item[ID]]['FREE'][1]['COUNT'] + $counts_RET[$item[ID]]['REDUCED'][1]['COUNT'];
$RET[] = array('TITLE' => $item[TITLE], 'REG_COUNT' => $counts_RET[$item[ID]]['REG'][1]['COUNT'], 'FREE_COUNT' => $counts_RET[$item[ID]]['FREE'][1]['COUNT'], 'REDUCED_COUNT' => $counts_RET[$item[ID]]['REDUCED'][1]['COUNT'], 'TOTAL' => $total);
}
unset($RET[0]);
ListOutput($RET, $columns, '.', '.', '', array(), array('save' => false));
}
function calcSeats0($period)
{
$mp = $period['MARKING_PERIOD_ID'];
$seats = DBGet(DBQuery("SELECT max((SELECT count(1) FROM SCHEDULE ss JOIN STUDENT_ENROLLMENT sem ON (sem.STUDENT_ID=ss.STUDENT_ID AND sem.SYEAR=ss.SYEAR) WHERE ss.COURSE_PERIOD_ID='{$period['COURSE_PERIOD_ID']}' AND (ss.MARKING_PERIOD_ID='{$mp}' OR ss.MARKING_PERIOD_ID IN (" . GetAllMP(GetMP($mp, 'MP'), $mp) . ")) AND (ac.SCHOOL_DATE>=ss.START_DATE AND (ss.END_DATE IS NULL OR ac.SCHOOL_DATE<=ss.END_DATE)) AND (ac.SCHOOL_DATE>=sem.START_DATE AND (sem.END_DATE IS NULL OR ac.SCHOOL_DATE<=sem.END_DATE)))) AS FILLED_SEATS FROM ATTENDANCE_CALENDAR ac WHERE ac.CALENDAR_ID='{$period['CALENDAR_ID']}' AND ac.SCHOOL_DATE BETWEEN " . db_case(array("(CURRENT_DATE>'" . GetMP($mp, 'END_DATE') . "')", 'TRUE', "'" . GetMP($mp, 'START_DATE') . "'", 'CURRENT_DATE')) . " AND '" . GetMP($mp, 'END_DATE') . "'"));
return $seats[1]['FILLED_SEATS'];
}
function calcSeats1($period, &$total_seats, &$filled_seats)
{
if ($_REQUEST['include_child_mps']) {
$mps = GetChildrenMP($period['MP'], $period['MARKING_PERIOD_ID']);
if ($period['MP'] == 'FY' || $period['MP'] == 'SEM') {
$mps = "'{$period['MARKING_PERIOD_ID']}'" . ($mps ? ',' . $mps : '');
}
} else {
$mps = "'" . $period['MARKING_PERIOD_ID'] . "'";
}
foreach (explode(',', $mps) as $mp) {
$mp = trim($mp, "'");
$seats = DBGet(DBQuery("SELECT max((SELECT count(1) FROM SCHEDULE ss JOIN STUDENT_ENROLLMENT sem ON (sem.STUDENT_ID=ss.STUDENT_ID AND sem.SYEAR=ss.SYEAR) WHERE ss.COURSE_PERIOD_ID='{$period['COURSE_PERIOD_ID']}' AND (ss.MARKING_PERIOD_ID='{$mp}' OR ss.MARKING_PERIOD_ID IN (" . GetAllMP(GetMP($mp, 'MP'), $mp) . ")) AND (ac.SCHOOL_DATE>=ss.START_DATE AND (ss.END_DATE IS NULL OR ac.SCHOOL_DATE<=ss.END_DATE)) AND (ac.SCHOOL_DATE>=sem.START_DATE AND (sem.END_DATE IS NULL OR ac.SCHOOL_DATE<=sem.END_DATE)))) AS FILLED_SEATS FROM ATTENDANCE_CALENDAR ac WHERE ac.CALENDAR_ID='{$period['CALENDAR_ID']}' AND ac.SCHOOL_DATE BETWEEN " . db_case(array("(CURRENT_DATE>'" . GetMP($mp, 'END_DATE') . "')", 'TRUE', "'" . GetMP($mp, 'START_DATE') . "'", 'CURRENT_DATE')) . " AND '" . GetMP($mp, 'END_DATE') . "'"));
if ($_REQUEST['include_child_mps']) {
if ($total_seats[$mp] !== false) {
if ($period['TOTAL_SEATS']) {
$total_seats[$mp] += $period['TOTAL_SEATS'];
} else {
$total_seats[$mp] = false;
}
}
if ($filled_seats !== false) {
if ($seats[1]['FILLED_SEATS'] != '') {
$filled_seats[$mp] += $seats[1]['FILLED_SEATS'];
} else {
$filled_seats[$mp] = false;
}
}
} else {
if ($total_seats !== false) {
if ($period['TOTAL_SEATS']) {
$total_seats += $period['TOTAL_SEATS'];
} else {
$total_seats = false;
}
}
if ($filled_seats !== false) {
if ($seats[1]['FILLED_SEATS'] != '') {
$filled_seats += $seats[1]['FILLED_SEATS'];
} else {
$filled_seats = false;
}
}
}
}
}
$total_rolled_data = $exists_RET[$table][1]['COUNT'];
echo $tables['student_enrollment'] . '|' . '(|' . $total_rolled_data . '|)';
break;
case 'report_card_grade_scales':
DBQuery('DELETE FROM report_card_grade_scales WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('DELETE FROM report_card_grades WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
// DBQuery("INSERT INTO report_card_grade_scales (ID,SYEAR,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ROLLOVER_ID) SELECT ".db_seq_nextval('REPORT_CARD_GRADE_SCALES_SEQ')."+ID,SYEAR+1,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ID FROM report_card_grade_scales WHERE SYEAR='".UserSyear()."' AND SCHOOL_ID='".UserSchool()."'");
DBQuery('INSERT INTO report_card_grade_scales (SYEAR,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ROLLOVER_ID,GP_SCALE) SELECT SYEAR+1,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ID,GP_SCALE FROM report_card_grade_scales WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('INSERT INTO report_card_grades (SYEAR,SCHOOL_ID,TITLE,COMMENT,BREAK_OFF,GPA_VALUE,GRADE_SCALE_ID,UNWEIGHTED_GP,SORT_ORDER) SELECT SYEAR+1,SCHOOL_ID,TITLE,COMMENT,BREAK_OFF,GPA_VALUE,(SELECT ID FROM report_card_grade_scales WHERE ROLLOVER_ID=GRADE_SCALE_ID AND SCHOOL_ID=report_card_grades.SCHOOL_ID),UNWEIGHTED_GP,SORT_ORDER FROM report_card_grades WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
$exists_RET[$table] = DBGet(DBQuery('SELECT count(*) AS COUNT from ' . $table . ' WHERE SYEAR=\'' . $next_syear . '\'' . (!$no_school_tables[$table] ? ' AND SCHOOL_ID=\'' . UserSchool() . '\'' : '')));
$total_rolled_data = $exists_RET[$table][1]['COUNT'];
echo $tables['report_card_grade_scales'] . '|' . '(|' . $total_rolled_data . '|)';
break;
case 'report_card_comments':
DBQuery('DELETE FROM report_card_comments WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
DBQuery('INSERT INTO report_card_comments (SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID) SELECT SYEAR+1,SCHOOL_ID,TITLE,SORT_ORDER,' . db_case(array('COURSE_ID', "''", 'NULL', '(SELECT COURSE_ID FROM courses WHERE ROLLOVER_ID=rc.COURSE_ID)')) . ' FROM report_card_comments rc WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
$exists_RET[$table] = DBGet(DBQuery('SELECT count(*) AS COUNT from ' . $table . ' WHERE SYEAR=\'' . $next_syear . '\'' . (!$no_school_tables[$table] ? ' AND SCHOOL_ID=\'' . UserSchool() . '\'' : '')));
$total_rolled_data = $exists_RET[$table][1]['COUNT'];
echo $tables['report_card_comments'] . '|' . '(|' . $total_rolled_data . '|)';
break;
case 'honor_roll':
//case 'eligibility_activities':
DBQuery('DELETE FROM ' . $table . ' WHERE SYEAR=\'' . $next_syear . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
$table_properties = db_properties($table);
$columns = '';
foreach ($table_properties as $column => $values) {
if ($column != 'ID' && $column != 'SYEAR') {
$columns .= ',' . $column;
}
}
DBQuery('INSERT INTO ' . $table . ' (SYEAR' . $columns . ') SELECT SYEAR+1' . $columns . ' FROM ' . $table . ' WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'');
if (count($config_RET)) {
foreach ($config_RET as $title => $value) {
$programconfig[User('STAFF_ID')][$title] = $value[1]['VALUE'];
}
} else {
$programconfig[User('STAFF_ID')] = true;
}
include_once 'functions/_makeLetterGrade.fnc.php';
$course_period_id = UserCoursePeriod();
$course_id = DBGet(DBQuery('SELECT COURSE_ID FROM course_periods WHERE COURSE_PERIOD_ID=\'' . UserCoursePeriod() . '\''));
$course_id = $course_id[1]['COURSE_ID'];
$grades_RET = DBGet(DBQuery('SELECT ID,TITLE,GPA_VALUE FROM report_card_grades WHERE SCHOOL_ID=\'' . UserSchool() . '\' AND SYEAR=\'' . UserSyear() . '\''), array(), array('ID'));
if ($programconfig[User('STAFF_ID')]['WEIGHT'] == 'Y') {
$points_RET = DBGet(DBQuery('SELECT DISTINCT s.STUDENT_ID,gt.ASSIGNMENT_TYPE_ID, gt.ASSIGNMENT_TYPE_ID,sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ') AS PARTIAL_POINTS,sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'ga.POINTS')) . ') AS PARTIAL_TOTAL, gt.FINAL_GRADE_PERCENT FROM students s JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID=\'' . $course_period_id . '\') JOIN gradebook_assignments ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID=\'' . $course_id . '\' AND ga.STAFF_ID=\'' . User('STAFF_ID') . '\') AND ga.MARKING_PERIOD_ID' . ($programconfig[User('STAFF_ID')]['ELIGIBILITY_CUMULITIVE'] == 'Y' ? ' IN (' . GetChildrenMP('SEM', UserMP()) . ')' : '=\'' . UserMP() . '\'') . ') LEFT OUTER JOIN gradebook_grades gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID),gradebook_assignment_types gt WHERE gt.ASSIGNMENT_TYPE_ID=ga.ASSIGNMENT_TYPE_ID AND gt.COURSE_ID=\'' . $course_id . '\' AND ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE,gt.ASSIGNMENT_TYPE_ID,gt.FINAL_GRADE_PERCENT'), array(), array('STUDENT_ID'));
} else {
$points_RET = DBGet(DBQuery('SELECT s.STUDENT_ID,\'' . '-1' . '\' AS ASSIGNMENT_TYPE_ID,sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'gg.POINTS')) . ') AS PARTIAL_POINTS,sum(' . db_case(array('gg.POINTS', "'-1'", "'0'", 'ga.POINTS')) . ') AS PARTIAL_TOTAL,\'' . '1' . '\' AS FINAL_GRADE_PERCENT FROM students s JOIN schedule ss ON (ss.STUDENT_ID=s.STUDENT_ID AND ss.COURSE_PERIOD_ID=\'' . $course_period_id . '\') JOIN gradebook_assignments ga ON ((ga.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID OR ga.COURSE_ID=\'' . $course_id . '\' AND ga.STAFF_ID=\'' . User('STAFF_ID') . '\') AND ga.MARKING_PERIOD_ID' . ($programconfig[User('STAFF_ID')]['ELIGIBILITY_CUMULITIVE'] == 'Y' ? ' IN (' . GetChildrenMP('SEM', UserMP()) . ')' : '=\'' . UserMP() . '\'') . ') LEFT OUTER JOIN gradebook_grades gg ON (gg.STUDENT_ID=s.STUDENT_ID AND gg.ASSIGNMENT_ID=ga.ASSIGNMENT_ID AND gg.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID) WHERE ((ga.ASSIGNED_DATE IS NULL OR CURRENT_DATE>=ga.ASSIGNED_DATE) AND (ga.DUE_DATE IS NULL OR CURRENT_DATE>=ga.DUE_DATE) OR gg.POINTS IS NOT NULL) GROUP BY s.STUDENT_ID,ss.START_DATE '), array(), array('STUDENT_ID'));
}
if (count($points_RET)) {
foreach ($points_RET as $student_id => $student) {
$total = $total_percent = 0;
foreach ($student as $partial_points) {
if ($partial_points['PARTIAL_TOTAL'] != 0) {
$total += $partial_points['PARTIAL_POINTS'] * $partial_points['FINAL_GRADE_PERCENT'] / $partial_points['PARTIAL_TOTAL'];
$total_percent += $partial_points['FINAL_GRADE_PERCENT'];
}
}
if ($total_percent != 0) {
$total /= $total_percent;
}
$grade = $grades_RET[_makeLetterGrade($total, 0, 0, 'ID')][1];
if ($grade['GPA_VALUE'] == '0' || !$grade['GPA_VALUE']) {
PopTable('footer');
echo '</FORM>';
Warehouse('footer_plain');
DBQuery("UPDATE CONFIG SET CONFIG_VALUE='Yes' WHERE TITLE='LOGIN'");
exit;
}
} elseif ($login_RET && $login_RET[1]['PROFILE'] == 'none') {
$error[] = _('Your account has not yet been activated.') . ' ' . _('You will be notified when it has been verified by a school administrator.');
} elseif ($student_RET) {
$_SESSION['STUDENT_ID'] = $student_RET[1]['STUDENT_ID'];
$_SESSION['LAST_LOGIN'] = $student_RET[1]['LAST_LOGIN'];
$failed_login = $student_RET[1]['FAILED_LOGIN'];
DBQuery("UPDATE STUDENTS SET LAST_LOGIN=CURRENT_TIMESTAMP,FAILED_LOGIN=NULL WHERE STUDENT_ID='" . $student_RET[1]['STUDENT_ID'] . "'");
} else {
DBQuery("UPDATE STAFF SET FAILED_LOGIN="******"''", '1', 'FAILED_LOGIN+1')) . " WHERE UPPER(USERNAME)=UPPER('{$_REQUEST['USERNAME']}') AND SYEAR='" . Config('SYEAR') . "'");
DBQuery("UPDATE STUDENTS SET FAILED_LOGIN="******"''", '1', 'FAILED_LOGIN+1')) . " WHERE UPPER(USERNAME)=UPPER('{$_REQUEST['USERNAME']}')");
$error[] = _('Incorrect username or password.') . ' ' . _('Please try logging in again.');
}
}
if ($_REQUEST['modfunc'] == 'create_account') {
Warehouse('header');
$_ROSARIO['allow_edit'] = true;
if (!$_REQUEST['staff']['USERNAME']) {
$_REQUEST['staff_id'] = 'new';
include 'modules/Users/User.php';
Warehouse('footer_plain');
} else {
$_REQUEST['modfunc'] = 'update';
include 'modules/Users/User.php';
$note[] = _('Your account has been created.') . ' ' . _('You will be notified when it has been verified by a school administrator.') . ' ' . _('You will then be able to log in.');
session_destroy();
