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;
}
}
