function _rollover($table) { global $id; switch ($table) { //modif Francois: copy School Configuration case 'CONFIG': DBQuery("INSERT INTO CONFIG (SCHOOL_ID,TITLE,CONFIG_VALUE) SELECT '{$id}' AS SCHOOL_ID,TITLE,CONFIG_VALUE FROM CONFIG WHERE SCHOOL_ID='" . UserSchool() . "';"); DBQuery("INSERT INTO PROGRAM_CONFIG (SCHOOL_ID,SYEAR,PROGRAM,VALUE,TITLE) SELECT '{$id}' AS SCHOOL_ID,SYEAR,PROGRAM,VALUE,TITLE FROM PROGRAM_CONFIG WHERE SCHOOL_ID='" . UserSchool() . "' AND SYEAR='" . UserSyear() . "';"); break; case 'SCHOOL_PERIODS': DBQuery("INSERT INTO SCHOOL_PERIODS (PERIOD_ID,SYEAR,SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,ATTENDANCE,ROLLOVER_ID) SELECT nextval('SCHOOL_PERIODS_SEQ'),SYEAR,'{$id}' AS SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,ATTENDANCE,PERIOD_ID FROM SCHOOL_PERIODS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; case 'SCHOOL_GRADELEVELS': $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SCHOOL_ID' && $column != 'NEXT_GRADE_ID') { $columns .= ',' . $column; } } DBQuery("INSERT INTO {$table} (ID,SCHOOL_ID" . $columns . ") SELECT nextval('" . $table . "_SEQ'),'{$id}' AS SCHOOL_ID" . $columns . " FROM {$table} WHERE SCHOOL_ID='" . UserSchool() . "'"); break; case 'SCHOOL_MARKING_PERIODS': DBQuery("INSERT INTO SCHOOL_MARKING_PERIODS (MARKING_PERIOD_ID,PARENT_ID,SYEAR,MP,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_COMMENTS,ROLLOVER_ID) SELECT " . db_seq_nextval('MARKING_PERIOD_SEQ') . ",PARENT_ID,SYEAR,MP,'{$id}' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_COMMENTS,MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("UPDATE SCHOOL_MARKING_PERIODS SET PARENT_ID=(SELECT mp.MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS mp WHERE mp.SYEAR=school_marking_periods.SYEAR AND mp.SCHOOL_ID=school_marking_periods.SCHOOL_ID AND mp.ROLLOVER_ID=school_marking_periods.PARENT_ID) WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='{$id}'"); break; case 'REPORT_CARD_GRADES': DBQuery("INSERT INTO REPORT_CARD_GRADE_SCALES (ID,SYEAR,SCHOOL_ID,TITLE,COMMENT,HR_GPA_VALUE,HHR_GPA_VALUE,SORT_ORDER,ROLLOVER_ID) SELECT " . db_seq_nextval('REPORT_CARD_GRADE_SCALES_SEQ') . ",SYEAR,'{$id}',TITLE,COMMENT,HR_GPA_VALUE,HHR_GPA_VALUE,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') . ",SYEAR,'{$id}',TITLE,COMMENT,BREAK_OFF,GPA_VALUE,(SELECT ID FROM REPORT_CARD_GRADE_SCALES WHERE ROLLOVER_ID=report_card_grades.GRADE_SCALE_ID AND SCHOOL_ID='{$id}'),SORT_ORDER FROM REPORT_CARD_GRADES WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; case 'REPORT_CARD_COMMENTS': DBQuery("INSERT INTO REPORT_CARD_COMMENTS (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,CATEGORY_ID,COURSE_ID) SELECT " . db_seq_nextval('REPORT_CARD_COMMENTS_SEQ') . ",SYEAR,'{$id}',TITLE,SORT_ORDER,NULL,NULL FROM REPORT_CARD_COMMENTS WHERE COURSE_ID IS NULL AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; case 'ELIGIBILITY_ACTIVITIES': case 'ATTENDANCE_CODES': $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SYEAR' && $column != 'SCHOOL_ID') { $columns .= ',' . $column; } } DBQuery("INSERT INTO {$table} (ID,SYEAR,SCHOOL_ID" . $columns . ") SELECT nextval('" . $table . "_SEQ'),SYEAR,'{$id}' AS SCHOOL_ID" . $columns . " FROM {$table} WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; } }
function _rollover($table) { global $id; switch ($table) { case 'SCHOOL_PERIODS': DBQuery("INSERT INTO SCHOOL_PERIODS (PERIOD_ID,SYEAR,SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,ROLLOVER_ID) SELECT fn_school_periods_seq(),SYEAR,'{$id}' AS SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,ROLLOVER_ID FROM SCHOOL_PERIODS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; case 'SCHOOL_GRADELEVELS': $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SCHOOL_ID' && $column != 'NEXT_GRADE_ID') { $columns .= ',' . $column; } } DBQuery("INSERT INTO {$table} (ID,SCHOOL_ID" . $columns . ") SELECT fn_" . strtolower($table) . "_seq(),'{$id}' AS SCHOOL_ID" . $columns . " FROM {$table} WHERE SCHOOL_ID='" . UserSchool() . "'"); break; case 'SCHOOL_YEARS': 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 fn_marking_period_seq()+MARKING_PERIOD_ID,SYEAR,'{$id}' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,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 fn_marking_period_seq()+MARKING_PERIOD_ID,(SELECT MARKING_PERIOD_ID FROM SCHOOL_YEARS y WHERE y.SYEAR=s.SYEAR AND y.ROLLOVER_ID=s.YEAR_ID AND y.SCHOOL_ID='{$id}') AS YEAR_ID,SYEAR,'{$id}' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,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 fn_marking_period_seq()+MARKING_PERIOD_ID,(SELECT MARKING_PERIOD_ID FROM SCHOOL_SEMESTERS s WHERE s.SYEAR=q.SYEAR AND s.ROLLOVER_ID=q.SEMESTER_ID AND s.SCHOOL_ID='{$id}') AS SEMESTER_ID,SYEAR,'{$id}' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,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 fn_marking_period_seq()+MARKING_PERIOD_ID,(SELECT MARKING_PERIOD_ID FROM SCHOOL_QUARTERS q WHERE q.SYEAR=p.SYEAR AND q.ROLLOVER_ID=p.QUARTER_ID AND q.SCHOOL_ID='{$id}'),SYEAR,'{$id}' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM SCHOOL_PROGRESS_PERIODS p WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; case 'REPORT_CARD_GRADES': 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') . ",SYEAR,'{$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') . ",SYEAR,'{$id}',TITLE,COMMENT,BREAK_OFF,GPA_VALUE,(SELECT ID FROM REPORT_CARD_GRADE_SCALES WHERE ROLLOVER_ID=REPORT_CARD_GRADES.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("INSERT INTO REPORT_CARD_COMMENTS (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID) SELECT " . db_seq_nextval('REPORT_CARD_COMMENTS_SEQ') . ",SYEAR,'{$id}',TITLE,SORT_ORDER,NULL FROM REPORT_CARD_COMMENTS WHERE COURSE_ID IS NULL AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; case 'ELIGIBILITY_ACTIVITIES': case 'ATTENDANCE_CODES': $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SYEAR' && $column != 'SCHOOL_ID') { $columns .= ',' . $column; } } DBQuery("INSERT INTO {$table} (ID,SYEAR,SCHOOL_ID" . $columns . ") SELECT fn_" . strtolower($table) . "_seq(),SYEAR,'{$id}' AS SCHOOL_ID" . $columns . " FROM {$table} WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; } }
function SaveData($iu_extra, $fields_done = false, $field_names = false) { if (!$fields_done) { $fields_done = array(); } if (!$field_names) { $field_names = array(); } if ($_REQUEST['month_values']) { foreach ($_REQUEST['month_values'] as $table => $values) { foreach ($values as $id => $columns) { foreach ($columns as $column => $value) { $_REQUEST['values'][$table][$id][$column] = $_REQUEST['day_values'][$table][$id][$column] . '-' . $value . '-' . $_REQUEST['year_values'][$table][$id][$column]; if ($_REQUEST['values'][$table][$id][$column] == '--') { $_REQUEST['values'][$table][$id][$column] = ''; } } } } } foreach ($_REQUEST['values'] as $table => $values) { $table_properties = db_properties($table); foreach ($values as $id => $columns) { foreach ($columns as $column => $value) { if ($field_names[$table][$column]) { $name = 'The value for ' . $field_names[$table][$column]; } else { $name = 'The value for ' . ucwords(strtolower(str_replace('_', ' ', $column))); } // COLUMN DOESN'T EXIST if (!$table_properties[$column]) { $error[] = 'There is no column for ' . $name . '. This value was not saved.'; continue; } // VALUE IS TOO LONG if ($table_properties[$column]['TYPE'] == 'VARCHAR' && strlen($value) > $table_properties[$column]['SIZE']) { $value = substr($value, 0, $table_properties[$column]['SIZE']); $error[] = $name . ' was too long. It was truncated to fit in the field.'; } // FIELD IS NUMERIC, VALUE CONTAINS NON-NUMERICAL CHARACTERS if ($table_properties[$column]['TYPE'] == 'NUMERIC' && ereg('[^0-9-]', $value)) { $value = ereg_replace('[^0-9]', '', $value); $error[] = $name . ', a numerical field, contained non-numerical characters. These characaters were removed.'; } // FIELD IS DATE, DATE IS WRONG if ($table_properties[$column]['TYPE'] == 'DATE' && $value && !VerifyDate($value)) { $error[] = $name . ', a date field, was not a valid date. This value could not be saved.'; continue; } if ($table_properties[$column]['TYPE'] == 'DATE' && $value) { $value = date('Y-m-d', strtotime($value)); } if ($id == 'new') { if ($value) { $ins_fields[$table] .= $column . ','; $ins_values[$table] .= "'" . str_replace("\\'", "''", $value) . "',"; $go = true; } } else { if (strlen($value) > 0) { $sql[$table] .= "{$column}='" . str_replace("\\'", "''", str_replace(''', "''", $value)) . "',"; } else { $sql[$table] .= "{$column}=NULL,"; } } } if ($id == 'new') { $sql[$table] = 'INSERT INTO ' . $table . ' (' . $iu_extra['fields'][$table] . substr($ins_fields[$table], 0, -1) . ') values(' . $iu_extra['values'][$table] . substr($ins_values[$table], 0, -1) . ')'; } else { $sql[$table] = 'UPDATE ' . $table . ' SET ' . substr($sql[$table], 0, -1) . ' WHERE ' . str_replace('__ID__', $id, $iu_extra[$table]); } echo ErrorMessage($error); if ($id != 'new' || $go == true) { DBQuery($sql[$table]); } $error = $ins_fields = $ins_values = $sql = $go = ''; } } }
function _rollover($table) { global $id; switch ($table) { case 'school_periods': DBQuery('INSERT INTO school_periods (SYEAR,SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,START_TIME,END_TIME,IGNORE_SCHEDULING,ATTENDANCE) SELECT SYEAR,\'' . $id . '\' AS SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,START_TIME,END_TIME,IGNORE_SCHEDULING,ATTENDANCE FROM school_periods WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''); break; case 'school_gradelevels': $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SCHOOL_ID' && $column != 'NEXT_GRADE_ID') { $columns .= ',' . $column; } } DBQuery('INSERT INTO ' . $table . ' (SCHOOL_ID' . $columns . ') SELECT \'' . $id . '\' AS SCHOOL_ID' . $columns . ' FROM ' . $table . ' WHERE SCHOOL_ID=\'' . UserSchool() . '\''); DBQuery('UPDATE ' . $table . ' t1,' . $table . ' t2 SET t1.NEXT_GRADE_ID= t1.ID+1 WHERE t1.SCHOOL_ID=\'' . $id . '\' AND t1.ID+1=t2.ID'); break; case 'school_years': 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 fn_marking_period_seq(),(SELECT MARKING_PERIOD_ID FROM school_years y WHERE y.SYEAR=s.SYEAR AND y.ROLLOVER_ID=s.YEAR_ID AND y.SCHOOL_ID=\'' . $id . '\') AS YEAR_ID,SYEAR,\'' . $id . '\' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_semesters s WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' ORDER BY MARKING_PERIOD_ID'); 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 fn_marking_period_seq(),(SELECT MARKING_PERIOD_ID FROM school_semesters s WHERE s.SYEAR=q.SYEAR AND s.ROLLOVER_ID=q.SEMESTER_ID AND s.SCHOOL_ID=\'' . $id . '\') AS SEMESTER_ID,SYEAR,\'' . $id . '\' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_quarters q WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' ORDER BY MARKING_PERIOD_ID'); 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 fn_marking_period_seq(),(SELECT MARKING_PERIOD_ID FROM school_quarters q WHERE q.SYEAR=p.SYEAR AND q.ROLLOVER_ID=p.QUARTER_ID AND q.SCHOOL_ID=\'' . $id . '\'),SYEAR,\'' . $id . '\' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_progress_periods p WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' ORDER BY MARKING_PERIOD_ID'); DBQuery('UPDATE school_semesters SET ROLLOVER_ID = NULL WHERE SCHOOL_ID=\'' . $id . '\''); DBQuery('UPDATE school_quarters SET ROLLOVER_ID = NULL WHERE SCHOOL_ID=\'' . $id . '\''); DBQuery('UPDATE school_progress_periods SET ROLLOVER_ID = NULL WHERE SCHOOL_ID=\'' . $id . '\''); break; case 'report_card_grades': DBQuery('INSERT INTO report_card_grade_scales (SYEAR,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ROLLOVER_ID,GP_SCALE) SELECT SYEAR,\'' . $id . '\',TITLE,COMMENT,SORT_ORDER,ID,GP_SCALE FROM report_card_grade_scales WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''); $qr = DBGet(DBQuery('select * from report_card_grades where school_id=' . UserSchool() . ' and SYEAR= ' . UserSyear() . '')); $c = 1; foreach ($qr as $qk => $qv) { $qr1 = DBGet(DBQuery('select id from report_card_grade_scales where title=(select title from report_card_grade_scales where id=' . $qv['GRADE_SCALE_ID'] . ') and school_id=' . $id . '')); $gr_scale_id = $qr1[1]['ID']; DBQuery('INSERT INTO report_card_grades (SYEAR,SCHOOL_ID,TITLE,COMMENT,BREAK_OFF,GPA_VALUE,UNWEIGHTED_GP,GRADE_SCALE_ID,SORT_ORDER) SELECT SYEAR,\'' . $id . '\',TITLE,COMMENT,BREAK_OFF,GPA_VALUE,UNWEIGHTED_GP,\'' . $gr_scale_id . '\',SORT_ORDER FROM report_card_grades WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' AND ID=' . $qv['ID']); } DBQuery('UPDATE report_card_grade_scales SET ROLLOVER_ID=NULL WHERE SCHOOL_ID=\'' . $id . '\''); break; case 'report_card_comments': $qr = DBGet(DBQuery('SELECT COURSE_ID,ID FROM report_card_comments WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'')); foreach ($qr as $qk => $qv) { $qr1 = DBGet(DBQuery('select COURSE_ID,ID FROM report_card_comments WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\'')); $course_id = $qr1[$qk]['COURSE_ID']; $id1 = $qr1[$qk]['ID']; DBQuery('INSERT INTO report_card_comments (SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID) SELECT SYEAR,\'' . $id . '\',TITLE,SORT_ORDER,\'' . $course_id . '\' FROM report_card_comments WHERE ID =\'' . $id1 . '\' AND SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''); } break; case 'eligibility_activities': case 'attendance_codes': $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SYEAR' && $column != 'SCHOOL_ID') { $columns .= ',' . $column; } } DBQuery('INSERT INTO ' . $table . ' (SYEAR,SCHOOL_ID' . $columns . ') SELECT SYEAR,\'' . $id . '\' AS SCHOOL_ID' . $columns . ' FROM ' . $table . ' WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''); break; } }
function SaveData($iu_extra, $fields_done = false, $field_names = false) { if (!$fields_done) { $fields_done = array(); } if (!$field_names) { $field_names = array(); } if ($_REQUEST['month_values']) { foreach ($_REQUEST['month_values'] as $table => $values) { foreach ($values as $id => $columns) { foreach ($columns as $column => $value) { $_REQUEST['values'][$table][$id][$column] = $_REQUEST['day_values'][$table][$id][$column] . '-' . $value . '-' . $_REQUEST['year_values'][$table][$id][$column]; //modif Francois: bugfix SQL bug when incomplete or non-existent date //if($_REQUEST['values'][$table][$id][$column]=='--') if (mb_strlen($_REQUEST['values'][$table][$id][$column]) < 11) { $_REQUEST['values'][$table][$id][$column] = ''; } else { while (!VerifyDate($_REQUEST['values'][$table][$id][$column])) { $_REQUEST['day_values'][$table][$id][$column]--; $_REQUEST['values'][$table][$id][$column] = $_REQUEST['day_values'][$table][$id][$column] . '-' . $value . '-' . $_REQUEST['year_values'][$table][$id][$column]; } } } } } } foreach ($_REQUEST['values'] as $table => $values) { $table_properties = db_properties($table); foreach ($values as $id => $columns) { foreach ($columns as $column => $value) { if ($field_names[$table][$column]) { $name = sprintf(_('The value for %s'), $field_names[$table][$column]); } else { $name = sprintf(_('The value for %s'), ucwords(mb_strtolower(str_replace('_', ' ', $column)))); } // COLUMN DOESN'T EXIST if (!$table_properties[$column]) { $error[] = sprintf(_('There is no column for %s. This value was not saved.'), $name); continue; } // VALUE IS TOO LONG if ($table_properties[$column]['TYPE'] == 'VARCHAR' && mb_strlen($value) > $table_properties[$column]['SIZE']) { $value = mb_substr($value, 0, $table_properties[$column]['SIZE']); $error[] = sprintf(_('%s was too long. It was truncated to fit in the field.'), $name); } // FIELD IS NUMERIC, VALUE CONTAINS NON-NUMERICAL CHARACTERS if ($table_properties[$column]['TYPE'] == 'NUMERIC' && preg_match('/[^0-9-]/', $value)) { $value = preg_replace('/[^0-9]/', '', $value); $error[] = sprintf(_('%s, a numerical field, contained non-numerical characters. These characters were removed.'), $name); } // FIELD IS DATE, DATE IS WRONG if ($table_properties[$column]['TYPE'] == 'DATE' && $value && !VerifyDate($value)) { $error[] = sprintf(_('%s, a date field, was not a valid date. This value could not be saved.'), $name); continue; } if ($id == 'new') { if ($value) { $ins_fields[$table] .= $column . ','; $ins_values[$table] .= "'" . $value . "',"; $go = true; } } else { $sql[$table] .= "{$column}='" . str_replace(''', "''", $value) . "',"; } } if ($id == 'new') { $sql[$table] = 'INSERT INTO ' . $table . ' (' . $iu_extra['fields'][$table] . mb_substr($ins_fields[$table], 0, -1) . ') values(' . $iu_extra['values'][$table] . mb_substr($ins_values[$table], 0, -1) . ')'; } else { $sql[$table] = 'UPDATE ' . $table . ' SET ' . mb_substr($sql[$table], 0, -1) . ' WHERE ' . str_replace('__ID__', $id, $iu_extra[$table]); } echo ErrorMessage($error); if ($id != 'new' || $go == true) { DBQuery($sql[$table]); } $error = $ins_fields = $ins_values = $sql = $go = ''; } } }
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() . '\''); $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['attendance_codes'] . '|' . '(|' . $total_rolled_data . '|)'; 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'"); $student_enroll_rolled = DBGet(DBQuery('SELECT ID FROM ' . $table . ' WHERE SYEAR=\'' . $next_syear . '\'')); $total_student_enroll_rolled = count($student_enroll_rolled); $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SYEAR') { $columns .= ',' . $column; } } if ($total_student_enroll_rolled == 0) { DBQuery('INSERT INTO ' . $table . ' (SYEAR' . $columns . ') SELECT SYEAR+1' . $columns . ' FROM ' . $table . ' WHERE SYEAR=\'' . UserSyear() . '\''); $roll_RET = DBGet(DBQuery('SELECT ID FROM ' . $table . ' WHERE TYPE=\'Roll\' AND SYEAR=\'' . $next_syear . '\'')); if (!$roll_RET) { DBQuery('INSERT INTO ' . $table . ' (SYEAR' . $columns . ') VALUES(\'' . $next_syear . '\',\'Rolled Over\',\'ROLL\',\'Roll\')'); } } $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'];
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 SaveData($iu_extra, $fields_done = false, $field_names = false) { if (!$fields_done) { $fields_done = array(); } if (!$field_names) { $field_names = array(); } if ($_REQUEST['month_values']) { foreach ($_REQUEST['month_values'] as $table => $values) { foreach ($values as $id => $columns) { foreach ($columns as $column => $value) { if ($value == 'JAN') { $value = '01'; } if ($value == 'FEB') { $value = '02'; } if ($value == 'MAR') { $value = '03'; } if ($value == 'APR') { $value = '04'; } if ($value == 'MAY') { $value = '05'; } if ($value == 'JUN') { $value = '06'; } if ($value == 'JUL') { $value = '07'; } if ($value == 'AUG') { $value = '08'; } if ($value == 'SEP') { $value = '09'; } if ($value == 'OCT') { $value = '10'; } if ($value == 'NOV') { $value = '11'; } if ($value == 'DEC') { $value = '12'; } $_REQUEST['values'][$table][$id][$column] = $_REQUEST['year_values'][$table][$id][$column] . '-' . $value . '-' . $_REQUEST['day_values'][$table][$id][$column]; if ($_REQUEST['values'][$table][$id][$column] == '--') { $_REQUEST['values'][$table][$id][$column] = ''; } } } } } foreach ($_REQUEST['values'] as $table => $values) { $table_properties = db_properties($table); foreach ($values as $id => $columns) { foreach ($columns as $column => $value) { if ($table == 'student_enrollment') { if ($column == 'START_DATE') { $s_date = '1-' . $_REQUEST['month_values'][$table][$id][$column] . '-' . $_REQUEST['year_values'][$table][$id][$column]; $num_days = date('t', strtotime($s_date)); if ($num_days < $_REQUEST['day_values'][$table][$id][$column]) { $error[] = '<font color=red>' . date('F', strtotime($s_date)) . ' has ' . $num_days . ' days</font>'; continue; } } } if ($field_names[$table][$column]) { $name = 'The value for ' . $field_names[$table][$column]; } else { $name = 'The value for ' . ucwords(strtolower(str_replace('_', ' ', $column))); } // COLUMN DOESN'T EXIST if (!$table_properties[$column]) { $error[] = 'There is no column for ' . $name . '. This value was not saved.'; continue; } // VALUE IS TOO LONG if ($table_properties[$column]['TYPE'] == 'VARCHAR' && strlen($value) > $table_properties[$column]['SIZE']) { $value = substr($value, 0, $table_properties[$column]['SIZE']); $error[] = $name . ' was too long. It was truncated to fit in the field.'; } // FIELD IS NUMERIC, VALUE CONTAINS NON-NUMERICAL CHARACTERS if ($table_properties[$column]['TYPE'] == 'NUMERIC' && ereg('[^0-9-]', $value)) { $value = ereg_replace('[^0-9]', '', $value); $error[] = $name . ', a numerical field, contained non-numerical characters. These characaters were removed.'; } // FIELD IS DATE, DATE IS WRONG if ($table_properties[$column]['TYPE'] == 'DATE' && $value && !VerifyDate($value)) { $error[] = $name . ', a date field, was not a valid date. This value could not be saved.'; continue; } if ($table_properties[$column]['TYPE'] == 'DATE' && $value) { $value = date('Y-m-d', strtotime($value)); } if ($id == 'new') { if (trim($value)) { $value = paramlib_validation($column, $value); $ins_fields[$table] .= $column . ','; if (stripos($_SERVER['SERVER_SOFTWARE'], 'linux')) { $ins_values[$table] .= '\'' . str_replace("'", "''", $value) . ' \','; } else { $ins_values[$table] .= '\'' . str_replace("'", "''", $value) . ' \','; } $go = true; } } else { if (strlen($value) > 0) { $value = paramlib_validation($column, $value); if (stripos($_SERVER['SERVER_SOFTWARE'], 'linux')) { $values = $column . '=\'' . str_replace("'", "''", $value) . ' \','; } else { $values = $column . '=\'' . str_replace("'", "''", $value) . ' \','; } $sql[$table] .= str_replace('%u201D', "\"", $values); if ($column == 'END_DATE' && $table == 'student_enrollment') { DBQuery('UPDATE schedule SET END_DATE=\'' . $value . '\' WHERE STUDENT_ID=\'' . $_REQUEST['student_id'] . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' AND SYEAR=\'' . UserSyear() . '\''); } } else { $sql[$table] .= "{$column}=NULL,"; } } } if ($id == 'new') { $sql[$table] = 'INSERT INTO ' . $table . ' (' . $iu_extra['fields'][$table] . substr($ins_fields[$table], 0, -1) . ') values(' . $iu_extra['values'][$table] . substr($ins_values[$table], 0, -1) . ')'; } else { $sql[$table] = 'UPDATE ' . $table . ' SET ' . substr($sql[$table], 0, -1) . ' WHERE ' . str_replace('__ID__', $id, $iu_extra[$table]); if ($table == 'student_enrollment') { $enrollment_record = DBGet(DBQuery("SELECT * FROM student_enrollment WHERE STUDENT_ID='{$_REQUEST['student_id']}' AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'")); $enrollment_record = $enrollment_record[1]; // } } echo ErrorMessage($error); if ($id != 'new' || $go == true) { DBQuery($sql[$table]); } $error = $ins_fields = $ins_values = $sql = $go = ''; } } }
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 ------------------------------------------------------------------------ // }
function Rollover($table) { global $next_syear, $RosarioModules, $moodleError; switch ($table) { case 'SCHOOLS': DBQuery("DELETE FROM SCHOOLS WHERE SYEAR='{$next_syear}'"); DBQuery("INSERT INTO SCHOOLS (SYEAR,ID,TITLE,ADDRESS,CITY,STATE,ZIPCODE,PHONE,PRINCIPAL,WWW_ADDRESS,SCHOOL_NUMBER,SHORT_NAME,REPORTING_GP_SCALE,NUMBER_DAYS_ROTATION) SELECT SYEAR+1,ID,TITLE,ADDRESS,CITY,STATE,ZIPCODE,PHONE,PRINCIPAL,WWW_ADDRESS,SCHOOL_NUMBER,SHORT_NAME,REPORTING_GP_SCALE,NUMBER_DAYS_ROTATION FROM SCHOOLS WHERE SYEAR='" . UserSyear() . "'"); break; case 'STAFF': $user_custom = ''; $fields_RET = DBGet(DBQuery("SELECT ID FROM STAFF_FIELDS")); foreach ($fields_RET as $field) { $user_custom .= ',CUSTOM_' . $field['ID']; } if ($RosarioModules['Food_Service']) { DBQuery("UPDATE FOOD_SERVICE_STAFF_ACCOUNTS SET STAFF_ID=(SELECT ROLLOVER_ID FROM STAFF WHERE STAFF_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID) WHERE exists(SELECT * FROM STAFF WHERE STAFF_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID AND ROLLOVER_ID IS NOT NULL AND SYEAR='{$next_syear}')"); DBQuery("DELETE FROM FOOD_SERVICE_STAFF_ACCOUNTS WHERE exists(SELECT * FROM STAFF WHERE STAFF_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID AND SYEAR='{$next_syear}')"); } 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 (STAFF_ID,SYEAR,CURRENT_SCHOOL_ID,TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,NAME_SUFFIX,USERNAME,PASSWORD,PHONE,EMAIL,PROFILE,HOMEROOM,LAST_LOGIN,SCHOOLS,PROFILE_ID,ROLLOVER_ID{$user_custom}) SELECT " . db_seq_nextval('STAFF_SEQ') . ",SYEAR+1,CURRENT_SCHOOL_ID,TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,NAME_SUFFIX,USERNAME,PASSWORD,PHONE,EMAIL,PROFILE,HOMEROOM,NULL,SCHOOLS,PROFILE_ID,STAFF_ID{$user_custom} FROM STAFF WHERE SYEAR='" . UserSyear() . "'"); //modif Francois: Moodle integrator if (MOODLE_INTEGRATOR) { $staff_RET = DBGet(DBQuery("SELECT STAFF_ID,ROLLOVER_ID FROM STAFF WHERE SYEAR='{$next_syear}' AND ROLLOVER_ID IS NOT NULL")); foreach ($staff_RET as $value) { DBQuery("UPDATE MOODLEXROSARIO SET ROSARIO_ID='{$value['STAFF_ID']}' WHERE ROSARIO_ID='{$value['ROLLOVER_ID']}' AND \"COLUMN\"='staff_id' AND SCHOOL_ID='" . UserSchool() . "'"); } } 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 'ATTENDANCE_CODES': DBQuery("DELETE FROM ATTENDANCE_CODE_CATEGORIES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("DELETE FROM ATTENDANCE_CODES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO ATTENDANCE_CODE_CATEGORIES (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,ROLLOVER_ID) SELECT " . db_seq_nextval('ATTENDANCE_CODE_CATEGORIES_SEQ') . ",SYEAR+1,SCHOOL_ID,TITLE,SORT_ORDER,ID FROM ATTENDANCE_CODE_CATEGORIES WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO ATTENDANCE_CODES (ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,TYPE,STATE_CODE,DEFAULT_CODE,TABLE_NAME,SORT_ORDER) SELECT " . db_seq_nextval('ATTENDANCE_CODES_SEQ') . ",c.SYEAR+1,c.SCHOOL_ID,c.TITLE,c.SHORT_NAME,c.TYPE,c.STATE_CODE,c.DEFAULT_CODE," . db_case(array('c.TABLE_NAME', "'0'", "'0'", '(SELECT ID FROM ATTENDANCE_CODE_CATEGORIES WHERE SCHOOL_ID=c.SCHOOL_ID AND ROLLOVER_ID=c.TABLE_NAME)')) . ",c.SORT_ORDER FROM ATTENDANCE_CODES c WHERE c.SYEAR='" . UserSyear() . "' AND c.SCHOOL_ID='" . UserSchool() . "'"); break; case 'SCHOOL_MARKING_PERIODS': DBQuery("DELETE FROM SCHOOL_MARKING_PERIODS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO SCHOOL_MARKING_PERIODS (MARKING_PERIOD_ID,PARENT_ID,SYEAR,MP,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_COMMENTS,ROLLOVER_ID) SELECT " . db_seq_nextval('MARKING_PERIOD_SEQ') . ",PARENT_ID,SYEAR+1,MP,SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE+365,END_DATE+365,POST_START_DATE+365,POST_END_DATE+365,DOES_GRADES,DOES_COMMENTS,MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("UPDATE SCHOOL_MARKING_PERIODS SET PARENT_ID=(SELECT mp.MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS mp WHERE mp.SYEAR=school_marking_periods.SYEAR AND mp.SCHOOL_ID=school_marking_periods.SCHOOL_ID AND mp.ROLLOVER_ID=school_marking_periods.PARENT_ID) WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); //modif Francois: ROLL Gradebook Config's Final Grading Percentages $db_case_array = array('puc.TITLE'); $mp_next = DBGet(DBQuery("SELECT MARKING_PERIOD_ID,ROLLOVER_ID,MP FROM SCHOOL_MARKING_PERIODS WHERE (MP='QTR' OR MP='SEM') AND SYEAR='" . $next_syear . "' AND SCHOOL_ID='" . UserSchool() . "' ORDER BY SORT_ORDER")); foreach ($mp_next as $mp) { $db_case_array[] = "'FY-" . $mp['ROLLOVER_ID'] . "'"; $db_case_array[] = "'FY-" . $mp['MARKING_PERIOD_ID'] . "'"; if ($mp['MP'] == 'QTR') { $db_case_array[] = "'SEM-" . $mp['ROLLOVER_ID'] . "'"; $db_case_array[] = "'SEM-" . $mp['MARKING_PERIOD_ID'] . "'"; } } DBQuery("UPDATE PROGRAM_USER_CONFIG puc SET TITLE=(SELECT (" . db_case($db_case_array) . ") FROM STAFF s WHERE (puc.TITLE LIKE 'FY-%' OR puc.TITLE LIKE 'SEM-%') AND puc.PROGRAM='Gradebook' AND puc.USER_ID=s.STAFF_ID AND s.SYEAR='{$next_syear}') FROM STAFF s WHERE (puc.TITLE LIKE 'FY-%' OR puc.TITLE LIKE 'SEM-%') AND puc.PROGRAM='Gradebook' AND puc.USER_ID=s.STAFF_ID AND s.SYEAR='{$next_syear}'"); 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_PERIOD_SCHOOL_PERIODS cpsp WHERE EXISTS (SELECT COURSE_PERIOD_ID FROM COURSE_PERIODS cp WHERE cp.COURSE_PERIOD_ID=cpsp.COURSE_PERIOD_ID AND cp.SYEAR='{$next_syear}' AND cp.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,SORT_ORDER,ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID," . db_seq_nextval('COURSE_SUBJECTS_SEQ') . ",TITLE,SHORT_NAME,SORT_ORDER,SUBJECT_ID FROM COURSE_SUBJECTS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); //modif Francois: Moodle integrator if (MOODLE_INTEGRATOR) { $course_subjects_RET = DBGet(DBQuery("SELECT SUBJECT_ID,ROLLOVER_ID FROM COURSES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "' AND ROLLOVER_ID IS NOT NULL")); foreach ($course_subjects_RET as $value) { DBQuery("UPDATE MOODLEXROSARIO SET ROSARIO_ID={$value['SUBJECT_ID']} WHERE ROSARIO_ID={$value['ROLLOVER_ID']} AND \"COLUMN\"='subject_id' AND SCHOOL_ID='" . UserSchool() . "'"); } } // ROLL COURSES 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.SCHOOL_ID=c.SCHOOL_ID 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() . "'"); //modif Francois: Moodle integrator if (MOODLE_INTEGRATOR) { $courses_RET = DBGet(DBQuery("SELECT COURSE_ID,ROLLOVER_ID FROM COURSES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "' AND ROLLOVER_ID IS NOT NULL")); foreach ($courses_RET as $value) { DBQuery("UPDATE MOODLEXROSARIO SET ROSARIO_ID='{$value['COURSE_ID']}' WHERE ROSARIO_ID='{$value['ROLLOVER_ID']}' AND \"COLUMN\"='course_id' AND SCHOOL_ID='" . UserSchool() . "'"); } } // ROLL COURSE_PERIODS DBQuery("INSERT INTO COURSE_PERIODS (SYEAR,SCHOOL_ID,COURSE_PERIOD_ID,COURSE_ID,TITLE,SHORT_NAME,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,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.SCHOOL_ID=p.SCHOOL_ID AND c.ROLLOVER_ID=p.COURSE_ID),TITLE,SHORT_NAME,MP,(SELECT MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS n WHERE n.MP=p.MP AND n.SCHOOL_ID=p.SCHOOL_ID AND n.ROLLOVER_ID=p.MARKING_PERIOD_ID),(SELECT STAFF_ID FROM STAFF n WHERE n.ROLLOVER_ID=p.TEACHER_ID),ROOM,TOTAL_SEATS,0 AS FILLED_SEATS,DOES_ATTENDANCE,(SELECT ID FROM REPORT_CARD_GRADE_SCALES WHERE SCHOOL_ID=p.SCHOOL_ID AND ROLLOVER_ID=p.GRADE_SCALE_ID),DOES_HONOR_ROLL,DOES_CLASS_RANK,DOES_BREAKOFF,GENDER_RESTRICTION,HOUSE_RESTRICTION,CREDITS,AVAILABILITY,HALF_DAY,PARENT_ID,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE SCHOOL_ID=p.SCHOOL_ID AND ROLLOVER_ID=p.CALENDAR_ID),COURSE_PERIOD_ID FROM COURSE_PERIODS p WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("UPDATE COURSE_PERIODS SET PARENT_ID=(SELECT cp.COURSE_PERIOD_ID FROM COURSE_PERIODS cp WHERE cp.ROLLOVER_ID=course_periods.PARENT_ID) WHERE PARENT_ID IS NOT NULL AND SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); $categories_RET = DBGet(DBQuery("SELECT ID,ROLLOVER_ID FROM ATTENDANCE_CODE_CATEGORIES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "' AND ROLLOVER_ID IS NOT NULL")); foreach ($categories_RET as $value) { DBQuery("UPDATE COURSE_PERIODS SET DOES_ATTENDANCE=replace(DOES_ATTENDANCE,',{$value['ROLLOVER_ID']},',',{$value['ID']},') WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); } //modif Francois: Moodle integrator if (MOODLE_INTEGRATOR) { $course_periods_RET = DBGet(DBQuery("SELECT cp.COURSE_PERIOD_ID, cp.COURSE_ID, cp.SHORT_NAME, cp.MARKING_PERIOD_ID, cp.TEACHER_ID FROM COURSE_PERIODS cp, MOODLEXROSARIO mxc WHERE cp.SYEAR='{$next_syear}' AND cp.SCHOOL_ID='" . UserSchool() . "' AND cp.ROLLOVER_ID IS NOT NULL AND cp.ROLLOVER_ID=mxc.ROSARIO_ID AND mxc.\"COLUMN\"='course_period_id'")); foreach ($course_periods_RET as $rolled_course_period) { $moodleError .= Moodle($_REQUEST['modname'], 'core_course_create_courses'); $moodleError .= Moodle($_REQUEST['modname'], 'enrol_manual_enrol_users'); } } //modif Francois: multiple school periods for a course period // ROLL COURSE_PERIOD_SCHOOL_PERIODS DBQuery("INSERT INTO COURSE_PERIOD_SCHOOL_PERIODS (COURSE_PERIOD_SCHOOL_PERIODS_ID,COURSE_PERIOD_ID,PERIOD_ID,DAYS) SELECT " . db_seq_nextval('COURSE_PERIOD_SCHOOL_PERIODS_SEQ') . ",(SELECT COURSE_PERIOD_ID FROM COURSE_PERIODS cp WHERE cpsp.COURSE_PERIOD_ID=cp.ROLLOVER_ID),(SELECT PERIOD_ID FROM SCHOOL_PERIODS n WHERE n.ROLLOVER_ID=cpsp.PERIOD_ID),DAYS FROM COURSE_PERIOD_SCHOOL_PERIODS cpsp, COURSE_PERIODS cp WHERE cp.SYEAR='" . UserSyear() . "' AND cp.SCHOOL_ID='" . UserSchool() . "' AND cpsp.COURSE_PERIOD_ID=cp.COURSE_PERIOD_ID"); 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 //modif Francois: do NOT roll students where next grade is NULL DBQuery("INSERT INTO STUDENT_ENROLLMENT (ID,SYEAR,SCHOOL_ID,STUDENT_ID,GRADE_ID,START_DATE,END_DATE,ENROLLMENT_CODE,DROP_CODE,CALENDAR_ID,NEXT_SCHOOL,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,(SELECT ID FROM STUDENT_ENROLLMENT_CODES WHERE SYEAR=e.SYEAR+1 AND TYPE='Add' AND DEFAULT_CODE='Y') AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_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() . "' AND (SELECT NEXT_GRADE_ID FROM SCHOOL_GRADELEVELS g WHERE g.ID=e.GRADE_ID) IS NOT NULL"); // 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,NEXT_SCHOOL,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,(SELECT ID FROM STUDENT_ENROLLMENT_CODES WHERE SYEAR=e.SYEAR+1 AND TYPE='Add' AND DEFAULT_CODE='Y') AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_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,NEXT_SCHOOL,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,(SELECT ID FROM STUDENT_ENROLLMENT_CODES WHERE SYEAR=e.SYEAR+1 AND TYPE='Add' AND DEFAULT_CODE='Y') AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),NEXT_SCHOOL,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_GRADES': 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,HR_GPA_VALUE,HHR_GPA_VALUE,SORT_ORDER,ROLLOVER_ID,GP_SCALE,HRS_GPA_VALUE) SELECT " . db_seq_nextval('REPORT_CARD_GRADE_SCALES_SEQ') . ",SYEAR+1,SCHOOL_ID,TITLE,COMMENT,HR_GPA_VALUE,HHR_GPA_VALUE,SORT_ORDER,ID,GP_SCALE,HRS_GPA_VALUE 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') . ",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_COMMENT_CATEGORIES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("DELETE FROM REPORT_CARD_COMMENTS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO REPORT_CARD_COMMENT_CATEGORIES (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID,ROLLOVER_ID) SELECT " . db_seq_nextval('REPORT_CARD_COMMENT_CATEGORIES_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)")) . ",ID FROM REPORT_CARD_COMMENT_CATEGORIES rc WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO REPORT_CARD_COMMENTS (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID,CATEGORY_ID,SCALE_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)")) . "," . db_case(array('CATEGORY_ID', "''", 'NULL', "(SELECT ID FROM REPORT_CARD_COMMENT_CATEGORIES WHERE ROLLOVER_ID=rc.CATEGORY_ID)")) . ",SCALE_ID FROM REPORT_CARD_COMMENTS rc WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; case 'ELIGIBILITY_ACTIVITIES': case 'DISCIPLINE_CATEGORIES': //modif Francois: discipline_field_usage rollover //modif Francois: discipline_field_usage rollover case 'DISCIPLINE_FIELD_USAGE': 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; case 'FOOD_SERVICE_STAFF_ACCOUNTS': DBQuery("UPDATE FOOD_SERVICE_STAFF_ACCOUNTS SET STAFF_ID=(SELECT STAFF_ID FROM STAFF WHERE ROLLOVER_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID) WHERE exists(SELECT * FROM STAFF WHERE ROLLOVER_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID AND SYEAR='{$next_syear}')"); break; //modif Francois: add School Configuration //modif Francois: add School Configuration case 'PROGRAM_CONFIG': DBQuery("DELETE FROM PROGRAM_CONFIG WHERE SYEAR='{$next_syear}'"); DBQuery("INSERT INTO PROGRAM_CONFIG (SYEAR,SCHOOL_ID,PROGRAM,TITLE,VALUE) SELECT SYEAR+1,SCHOOL_ID,PROGRAM,TITLE,VALUE FROM PROGRAM_CONFIG WHERE SYEAR='" . UserSyear() . "'"); break; } }
function _rollover($table) { global $id; switch ($table) { case 'school_periods': DBQuery('INSERT INTO school_periods (SYEAR,SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,START_TIME,END_TIME,IGNORE_SCHEDULING,ATTENDANCE) SELECT SYEAR,\'' . $id . '\' AS SCHOOL_ID,SORT_ORDER,TITLE,SHORT_NAME,LENGTH,START_TIME,END_TIME,IGNORE_SCHEDULING,ATTENDANCE FROM school_periods WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''); break; case 'school_gradelevels': $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SCHOOL_ID' && $column != 'NEXT_GRADE_ID') { $columns .= ',' . $column; } } DBQuery('INSERT INTO ' . $table . ' (SCHOOL_ID' . $columns . ') SELECT \'' . $id . '\' AS SCHOOL_ID' . $columns . ' FROM ' . $table . ' WHERE SCHOOL_ID=\'' . UserSchool() . '\''); DBQuery('UPDATE ' . $table . ' t1,' . $table . ' t2 SET t1.NEXT_GRADE_ID= t1.ID+1 WHERE t1.SCHOOL_ID=\'' . $id . '\' AND t1.ID+1=t2.ID'); break; case 'school_years': 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 fn_marking_period_seq(),(SELECT MARKING_PERIOD_ID FROM school_years y WHERE y.SYEAR=s.SYEAR AND y.ROLLOVER_ID=s.YEAR_ID AND y.SCHOOL_ID=\'' . $id . '\') AS YEAR_ID,SYEAR,\'' . $id . '\' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_semesters s WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' ORDER BY MARKING_PERIOD_ID'); 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 fn_marking_period_seq(),(SELECT MARKING_PERIOD_ID FROM school_semesters s WHERE s.SYEAR=q.SYEAR AND s.ROLLOVER_ID=q.SEMESTER_ID AND s.SCHOOL_ID=\'' . $id . '\') AS SEMESTER_ID,SYEAR,\'' . $id . '\' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_quarters q WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' ORDER BY MARKING_PERIOD_ID'); 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 fn_marking_period_seq(),(SELECT MARKING_PERIOD_ID FROM school_quarters q WHERE q.SYEAR=p.SYEAR AND q.ROLLOVER_ID=p.QUARTER_ID AND q.SCHOOL_ID=\'' . $id . '\'),SYEAR,\'' . $id . '\' AS SCHOOL_ID,TITLE,SHORT_NAME,SORT_ORDER,START_DATE,END_DATE,POST_START_DATE,POST_END_DATE,DOES_GRADES,DOES_EXAM,DOES_COMMENTS,MARKING_PERIOD_ID FROM school_progress_periods p WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' ORDER BY MARKING_PERIOD_ID'); DBQuery('UPDATE school_semesters SET ROLLOVER_ID = NULL WHERE SCHOOL_ID=\'' . $id . '\''); DBQuery('UPDATE school_quarters SET ROLLOVER_ID = NULL WHERE SCHOOL_ID=\'' . $id . '\''); DBQuery('UPDATE school_progress_periods SET ROLLOVER_ID = NULL WHERE SCHOOL_ID=\'' . $id . '\''); break; case 'report_card_grades': DBQuery('INSERT INTO report_card_grade_scales (SYEAR,SCHOOL_ID,TITLE,COMMENT,SORT_ORDER,ROLLOVER_ID,GP_SCALE) SELECT SYEAR,\'' . $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,SORT_ORDER) SELECT SYEAR,\''.$id.'\',TITLE,COMMENT,BREAK_OFF,GPA_VALUE,(SELECT ID FROM report_card_grade_scales WHERE ROLLOVER_ID=report_card_grades.GRADE_SCALE_ID),SORT_ORDER FROM report_card_grades 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,\'' . $id . '\',TITLE,COMMENT,BREAK_OFF,GPA_VALUE,(SELECT ID FROM report_card_grade_scales WHERE ROLLOVER_ID=report_card_grades.GRADE_SCALE_ID AND SCHOOL_ID = report_card_grades.SCHOOL_ID),SORT_ORDER FROM report_card_grades WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''); DBQuery('UPDATE report_card_grade_scales SET ROLLOVER_ID=NULL WHERE SCHOOL_ID=\'' . $id . '\''); break; case 'report_card_comments': DBQuery('INSERT INTO report_card_comments (SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID) SELECT SYEAR,\'' . $id . '\',TITLE,SORT_ORDER,NULL FROM report_card_comments WHERE COURSE_ID IS NULL AND SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''); break; case 'eligibility_activities': case 'attendance_codes': $table_properties = db_properties($table); $columns = ''; foreach ($table_properties as $column => $values) { if ($column != 'ID' && $column != 'SYEAR' && $column != 'SCHOOL_ID') { $columns .= ',' . $column; } } DBQuery('INSERT INTO ' . $table . ' (SYEAR,SCHOOL_ID' . $columns . ') SELECT SYEAR,\'' . $id . '\' AS SCHOOL_ID' . $columns . ' FROM ' . $table . ' WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''); break; } }
function Rollover($table) { global $next_syear, $CentreModules; switch ($table) { case 'SCHOOLS': DBQuery("DELETE FROM SCHOOLS WHERE SYEAR='{$next_syear}'"); DBQuery("INSERT INTO SCHOOLS (SYEAR,ID,TITLE,ADDRESS,CITY,STATE,ZIPCODE,PHONE,PRINCIPAL,WWW_ADDRESS,SCHOOL_NUMBER,SHORT_NAME) SELECT SYEAR+1,ID,TITLE,ADDRESS,CITY,STATE,ZIPCODE,PHONE,PRINCIPAL,WWW_ADDRESS,SCHOOL_NUMBER,SHORT_NAME FROM SCHOOLS WHERE SYEAR='" . UserSyear() . "'"); break; case 'STAFF': $user_custom = ''; $fields_RET = DBGet(DBQuery("SELECT ID FROM STAFF_FIELDS")); foreach ($fields_RET as $field) { $user_custom .= ',CUSTOM_' . $field['ID']; } if ($CentreModules['Food_Service']) { DBQuery("UPDATE FOOD_SERVICE_STAFF_ACCOUNTS SET STAFF_ID=(SELECT ROLLOVER_ID FROM STAFF WHERE STAFF_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID) WHERE exists(SELECT * FROM STAFF WHERE STAFF_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID AND ROLLOVER_ID IS NOT NULL AND SYEAR='{$next_syear}')"); DBQuery("DELETE FROM FOOD_SERVICE_STAFF_ACCOUNTS WHERE exists(SELECT * FROM STAFF WHERE STAFF_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID AND SYEAR='{$next_syear}')"); } 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 (STAFF_ID,SYEAR,CURRENT_SCHOOL_ID,TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,NAME_SUFFIX,USERNAME,PASSWORD,PHONE,EMAIL,PROFILE,HOMEROOM,LAST_LOGIN,SCHOOLS,PROFILE_ID,ROLLOVER_ID{$user_custom}) SELECT " . db_seq_nextval('STAFF_SEQ') . ",SYEAR+1,CURRENT_SCHOOL_ID,TITLE,FIRST_NAME,LAST_NAME,MIDDLE_NAME,NAME_SUFFIX,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 'ATTENDANCE_CODES': DBQuery("DELETE FROM ATTENDANCE_CODE_CATEGORIES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("DELETE FROM ATTENDANCE_CODES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO ATTENDANCE_CODE_CATEGORIES (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,ROLLOVER_ID) SELECT " . db_seq_nextval('ATTENDANCE_CODE_CATEGORIES_SEQ') . ",SYEAR+1,SCHOOL_ID,TITLE,SORT_ORDER,ID FROM ATTENDANCE_CODE_CATEGORIES WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO ATTENDANCE_CODES (ID,SYEAR,SCHOOL_ID,TITLE,SHORT_NAME,TYPE,STATE_CODE,DEFAULT_CODE,TABLE_NAME,SORT_ORDER) SELECT " . db_seq_nextval('ATTENDANCE_CODES_SEQ') . ",c.SYEAR+1,c.SCHOOL_ID,c.TITLE,c.SHORT_NAME,c.TYPE,c.STATE_CODE,c.DEFAULT_CODE," . db_case(array('c.TABLE_NAME', "'0'", "'0'", '(SELECT ID FROM ATTENDANCE_CODE_CATEGORIES WHERE SCHOOL_ID=c.SCHOOL_ID AND ROLLOVER_ID=c.TABLE_NAME)')) . ",c.SORT_ORDER FROM ATTENDANCE_CODES c WHERE c.SYEAR='" . UserSyear() . "' AND c.SCHOOL_ID='" . UserSchool() . "'"); break; case 'SCHOOL_MARKING_PERIODS': DBQuery("DELETE FROM SCHOOL_MARKING_PERIODS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO SCHOOL_MARKING_PERIODS (MARKING_PERIOD_ID,PARENT_ID,SYEAR,MP,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') . ",PARENT_ID,SYEAR+1,MP,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_MARKING_PERIODS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("UPDATE SCHOOL_MARKING_PERIODS SET PARENT_ID=(SELECT mp.MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS mp WHERE mp.SYEAR=school_marking_periods.SYEAR AND mp.SCHOOL_ID=school_marking_periods.SCHOOL_ID AND mp.ROLLOVER_ID=school_marking_periods.PARENT_ID) WHERE SYEAR='{$next_syear}' 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,SUBJECT_ID,TITLE,SHORT_NAME,SORT_ORDER,ROLLOVER_ID) SELECT SYEAR+1,SCHOOL_ID," . db_seq_nextval('COURSE_SUBJECTS_SEQ') . ",TITLE,SHORT_NAME,SORT_ORDER,SUBJECT_ID FROM COURSE_SUBJECTS WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); // ROLL COURSES 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.SCHOOL_ID=c.SCHOOL_ID 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 COURSE_PERIODS DBQuery("INSERT INTO COURSE_PERIODS (SYEAR,SCHOOL_ID,COURSE_PERIOD_ID,COURSE_ID,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.SCHOOL_ID=p.SCHOOL_ID AND c.ROLLOVER_ID=p.COURSE_ID),TITLE,SHORT_NAME,(SELECT PERIOD_ID FROM SCHOOL_PERIODS n WHERE n.SCHOOL_ID=p.SCHOOL_ID AND n.ROLLOVER_ID=p.PERIOD_ID),MP,(SELECT MARKING_PERIOD_ID FROM SCHOOL_MARKING_PERIODS n WHERE n.MP=p.MP AND n.SCHOOL_ID=p.SCHOOL_ID AND n.ROLLOVER_ID=p.MARKING_PERIOD_ID),(SELECT STAFF_ID FROM STAFF n WHERE n.ROLLOVER_ID=p.TEACHER_ID),ROOM,TOTAL_SEATS,0 AS FILLED_SEATS,DOES_ATTENDANCE,(SELECT ID FROM REPORT_CARD_GRADE_SCALES WHERE SCHOOL_ID=p.SCHOOL_ID AND 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 WHERE SCHOOL_ID=p.SCHOOL_ID AND ROLLOVER_ID=p.CALENDAR_ID),COURSE_PERIOD_ID FROM COURSE_PERIODS p WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("UPDATE COURSE_PERIODS SET PARENT_ID=(SELECT cp.COURSE_PERIOD_ID FROM COURSE_PERIODS cp WHERE cp.ROLLOVER_ID=course_periods.PARENT_ID) WHERE PARENT_ID IS NOT NULL AND SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); $categories_RET = DBGet(DBQuery("SELECT ID,ROLLOVER_ID FROM ATTENDANCE_CODE_CATEGORIES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "' AND ROLLOVER_ID IS NOT NULL")); foreach ($categories_RET as $value) { DBQuery("UPDATE COURSE_PERIODS SET DOES_ATTENDANCE=replace(DOES_ATTENDANCE,',{$value['ROLLOVER_ID']},',',{$value['ID']},') WHERE 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,NEXT_SCHOOL,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,(SELECT ID FROM STUDENT_ENROLLMENT_CODES WHERE SYEAR=e.SYEAR+1 AND TYPE='Add' AND DEFAULT_CODE='Y') AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_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,NEXT_SCHOOL,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,(SELECT ID FROM STUDENT_ENROLLMENT_CODES WHERE SYEAR=e.SYEAR+1 AND TYPE='Add' AND DEFAULT_CODE='Y') AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),SCHOOL_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,NEXT_SCHOOL,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,(SELECT ID FROM STUDENT_ENROLLMENT_CODES WHERE SYEAR=e.SYEAR+1 AND TYPE='Add' AND DEFAULT_CODE='Y') AS ENROLLMENT_CODE,NULL AS DROP_CODE,(SELECT CALENDAR_ID FROM ATTENDANCE_CALENDARS WHERE ROLLOVER_ID=e.CALENDAR_ID),NEXT_SCHOOL,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_GRADES': 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,HR_GPA_VALUE,HHR_GPA_VALUE,SORT_ORDER,ROLLOVER_ID) SELECT " . db_seq_nextval('REPORT_CARD_GRADE_SCALES_SEQ') . ",SYEAR+1,SCHOOL_ID,TITLE,COMMENT,HR_GPA_VALUE,HHR_GPA_VALUE,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') . ",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_COMMENT_CATEGORIES WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("DELETE FROM REPORT_CARD_COMMENTS WHERE SYEAR='{$next_syear}' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO REPORT_CARD_COMMENT_CATEGORIES (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID,ROLLOVER_ID) SELECT " . db_seq_nextval('REPORT_CARD_COMMENT_CATEGORIES_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)")) . ",ID FROM REPORT_CARD_COMMENT_CATEGORIES rc WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); DBQuery("INSERT INTO REPORT_CARD_COMMENTS (ID,SYEAR,SCHOOL_ID,TITLE,SORT_ORDER,COURSE_ID,CATEGORY_ID,SCALE_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)")) . "," . db_case(array('CATEGORY_ID', "''", 'NULL', "(SELECT ID FROM REPORT_CARD_COMMENT_CATEGORIES WHERE ROLLOVER_ID=rc.CATEGORY_ID)")) . ",SCALE_ID FROM REPORT_CARD_COMMENTS rc WHERE SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"); break; case 'ELIGIBILITY_ACTIVITIES': case 'DISCIPLINE_CATEGORIES': 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; case 'FOOD_SERVICE_STAFF_ACCOUNTS': DBQuery("UPDATE FOOD_SERVICE_STAFF_ACCOUNTS SET STAFF_ID=(SELECT STAFF_ID FROM STAFF WHERE ROLLOVER_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID) WHERE exists(SELECT * FROM STAFF WHERE ROLLOVER_ID=FOOD_SERVICE_STAFF_ACCOUNTS.STAFF_ID AND SYEAR='{$next_syear}')"); break; } }