<?php
/**
* Update functions for versions 2 to 3
*
* Incremental updates
*
* @package RosarioSIS
* @subpackage ProgramFunctions
*/
/**
* Update to version 2.9-alpha
*
* 1. Add VERSION to config table
* 2. Add STUDENTS_EMAIL_FIELD to config table.
* 3. Add course_period_school_periods_id column to course_period_school_periods table PRIMARY KEY
* 4. Update student_mp_comments table
* 5. Create school_fields_seq Sequence
* 6. Add student_assignments table & SUBMISSION column to gradebook_assignments table
*
* Local function
*
* @since 2.9
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update29alpha()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add VERSION to config table.
*/
$version_added = DBGet( "SELECT 1 FROM config WHERE TITLE='VERSION'" );
if ( ! $version_added )
{
DBQuery( "INSERT INTO config VALUES (0, 'VERSION', '2.9-alpha');" );
}
/**
* 2. Add STUDENTS_EMAIL_FIELD to config table.
*/
$students_email_field_added = DBGet( "SELECT 1 FROM config WHERE TITLE='STUDENTS_EMAIL_FIELD'" );
if ( ! $students_email_field_added )
{
DBQuery( "INSERT INTO config VALUES (0, 'STUDENTS_EMAIL_FIELD', NULL);" );
}
/**
* 3. Add course_period_school_periods_id column to course_period_school_periods table PRIMARY KEY
*
* DROP PRIMARY KEY
* And ADD it again with course_period_school_periods_id
*/
$SQL_add_ID = 'ALTER TABLE ONLY course_period_school_periods
DROP CONSTRAINT course_period_school_periods_pkey;
ALTER TABLE ONLY course_period_school_periods
ADD CONSTRAINT course_period_school_periods_pkey
PRIMARY KEY (course_period_school_periods_id, course_period_id, period_id);';
DBQuery( $SQL_add_ID );
/**
* 4. Update student_mp_comments table
*
* WARNING: no Downgrade possible!
*
* Serialize comments from:
* [date1]|[staff_id1]||[comment1]||[date1]|[staff_id1]||[comment1]
*
* to array of comments:
* array(
* 'date' => 'date1',
* 'staff_id' => 'staff_id1',
* 'comment' => 'comment1',
* )
*/
$comments_RET = DBGet( "SELECT SYEAR, MARKING_PERIOD_ID, STUDENT_ID, COMMENT
FROM student_mp_comments
WHERE COMMENT IS NOT NULL
AND COMMENT!=''" );
if ( is_array( $comments_RET )
&& ! unserialize( $comments_RET[0]['COMMENT'] ) )
{
$ser_comments = [];
$SQL_updt_coms = '';
foreach ( $comments_RET as $comment )
{
$coms = explode( '||', $comment['COMMENT'] );
$ser_coms = [];
$i = 0;
foreach ( $coms as $com )
{
if ( is_array( list( $date, $staff_id ) = explode( '|', $com ) )
&& (int) $staff_id > 0 )
{
$ser_coms[ $i ]['date'] = $date;
$ser_coms[ $i ]['staff_id'] = $staff_id;
}
else
{
$ser_coms[ $i ]['comment'] = $com;
$i++;
}
}
$ser_coms = DBEscapeString( serialize( array_reverse( $ser_coms ) ) );
$SQL_updt_coms .= "UPDATE student_mp_comments
SET COMMENT='" . $ser_coms . "'
WHERE SYEAR='" . $comment['SYEAR'] . "'
AND MARKING_PERIOD_ID='" . $comment['MARKING_PERIOD_ID'] . "'
AND STUDENT_ID='" . $comment['STUDENT_ID'] . "';";
}
if ( $SQL_updt_coms )
{
DBGet( $SQL_updt_coms );
}
}
else
$return = false;
// 5. Create school_fields_seq Sequence.
$sequence_exists = DBGet( "SELECT 1 FROM pg_class
WHERE relname = 'school_fields_seq'" );
if ( ! $sequence_exists )
{
DBQuery( "CREATE SEQUENCE school_fields_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
SELECT pg_catalog.setval('school_fields_seq', 99, true);" );
}
/**
* 6. Add student_assignments table (& its composite primary key)
* & add SUBMISSION column to gradebook_assignments table
* & add StudentAssignments.php to profile_exceptions table.
*/
DBQuery( "CREATE TABLE IF NOT EXISTS student_assignments (
assignment_id numeric NOT NULL,
student_id numeric NOT NULL,
data text
);");
$sa_constraint_exists = DBGet( "SELECT 1
FROM information_schema.constraint_column_usage
WHERE table_name = 'student_assignments'
AND constraint_name = 'student_assignments_pkey'" );
if ( ! $sa_constraint_exists )
{
DBQuery( "ALTER TABLE ONLY student_assignments
ADD CONSTRAINT student_assignments_pkey PRIMARY KEY (assignment_id, student_id);" );
}
$submission_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'gradebook_assignments')
AND attname = 'submission';" );
if ( ! $submission_column_exists )
{
DBQuery( "ALTER TABLE ONLY gradebook_assignments
ADD COLUMN submission character varying(1);" );
}
$sa_exceptions_exists = DBGet( "SELECT 1
FROM profile_exceptions
WHERE profile_id IN (0,3)
AND modname='Grades/StudentAssignments.php'" );
if ( ! $sa_exceptions_exists )
{
DBQuery( "INSERT INTO profile_exceptions VALUES (0, 'Grades/StudentAssignments.php', 'Y', NULL);
INSERT INTO profile_exceptions VALUES (3, 'Grades/StudentAssignments.php', 'Y', NULL);" );
}
return $return;
}
/**
* Update to version 2.9.2
*
* 1. Add GP_PASSING_VALUE to report_card_grade_scales table
*
* Local function
*
* @since 2.9.2
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update292()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add GP_PASSING_VALUE to report_card_grade_scales table
* & Set minimum passing grade to '0' for already present scales.
*/
$gppassingvalue_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'report_card_grade_scales')
AND attname = 'gp_passing_value';" );
if ( ! $gppassingvalue_column_exists )
{
DBQuery( "ALTER TABLE ONLY report_card_grade_scales
ADD COLUMN gp_passing_value numeric(10,3);
UPDATE report_card_grade_scales
SET gp_passing_value=0;" );
}
return $return;
}
/**
* Update to version 2.9.5
*
* 1. Add LIMIT_EXISTING_CONTACTS_ADDRESSES to config table.
*
* Local function
*
* @since 2.9.5
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update295()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add LIMIT_EXISTING_CONTACTS_ADDRESSES to config table.
*/
$limit_existing_contacts_addresses_field_added = DBGet( "SELECT 1 FROM config
WHERE TITLE='LIMIT_EXISTING_CONTACTS_ADDRESSES'" );
if ( ! $limit_existing_contacts_addresses_field_added )
{
DBQuery( "INSERT INTO config VALUES (0, 'LIMIT_EXISTING_CONTACTS_ADDRESSES', NULL);" );
}
return $return;
}
/**
* Update to version 2.9.12
*
* 1. Add THEME_FORCE to config table.
*
* Local function
*
* @since 2.9.12
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update2912()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add THEME_FORCE to config table.
*/
$theme_force_field_added = DBGet( "SELECT 1 FROM config
WHERE TITLE='THEME_FORCE'" );
if ( ! $theme_force_field_added )
{
DBQuery( "INSERT INTO config VALUES (0, 'THEME_FORCE', NULL);" );
}
return $return;
}
/**
* Update to version 2.9.13
*
* Admin Schools restriction.
* 1. Add Users/User.php&category_id=1&schools to profile_exceptions table.
* 2. Add Users/User.php&category_id=1&schools to staff_exceptions table.
*
* Local function
*
* @since 2.9.13
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update2913()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add Users/User.php&category_id=1&schools to profile_exceptions table.
*/
$admin_profiles_RET = DBGet( "SELECT id
FROM profile_exceptions, user_profiles
WHERE profile='admin'" );
foreach ( (array) $admin_profiles_RET as $admin_profile )
{
$profile_id = $admin_profile['ID'];
$as_profile_exceptions_exists = DBGet( "SELECT 1
FROM profile_exceptions
WHERE profile_id='" . $profile_id . "'
AND modname='Users/User.php&category_id=1&schools'" );
if ( ! $as_profile_exceptions_exists )
{
DBQuery( "INSERT INTO profile_exceptions
VALUES ('" . $profile_id . "', 'Users/User.php&category_id=1&schools', 'Y', 'Y');" );
}
}
/**
* 2. Add Users/User.php&category_id=1&schools to staff_exceptions table.
*/
$as_staff_exceptions_exists = DBGet( "SELECT 1
FROM staff_exceptions
WHERE modname='Users/User.php&category_id=1&schools'" );
// Check if we have staff_exceptions.
$staff_exceptions_user_ids = DBGet( "SELECT user_id
FROM staff_exceptions
WHERE modname='Users/User.php&category_id=1'" );
if ( ! $as_staff_exceptions_exists
&& $staff_exceptions_user_ids )
{
foreach ( (array) $staff_exceptions_user_ids as $user_id )
{
DBQuery( "INSERT INTO staff_exceptions
VALUES ('" . $user_id['USER_ID'] . "', 'Users/User.php&category_id=1&schools', 'Y', 'Y');" );
}
}
return $return;
}
/**
* Update to version 2.9.14
*
* Add School Field types.
* 1. Add SELECT_OPTIONS column to school_fields table.
* Admin User Profile restriction.
* 2. Add Users/User.php&category_id=1&user_profile to profile_exceptions table.
* 3. Add Users/User.php&category_id=1&user_profile to staff_exceptions table.
*
* Local function
*
* @since 2.9.14
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update2914()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add SELECT_OPTIONS column to school_fields table.
*/
$select_options_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'school_fields')
AND attname = 'select_options';" );
if ( ! $select_options_column_exists )
{
DBQuery( "ALTER TABLE ONLY school_fields
ADD COLUMN select_options character varying(10000);" );
}
/**
* 2. Add Users/User.php&category_id=1&user_profile to profile_exceptions table.
*/
$admin_profiles_RET = DBGet( "SELECT id
FROM user_profiles
WHERE profile='admin'" );
foreach ( (array) $admin_profiles_RET as $admin_profile )
{
$profile_id = $admin_profile['ID'];
$up_profile_exceptions_exists = DBGet( "SELECT 1
FROM profile_exceptions
WHERE profile_id='" . $profile_id . "'
AND modname='Users/User.php&category_id=1&user_profile'" );
if ( ! $up_profile_exceptions_exists )
{
DBQuery( "INSERT INTO profile_exceptions
VALUES ('" . $profile_id . "', 'Users/User.php&category_id=1&user_profile', 'Y', 'Y');" );
}
}
/**
* 3. Add Users/User.php&category_id=1&user_profile to staff_exceptions table.
*/
$up_staff_exceptions_exists = DBGet( "SELECT 1
FROM staff_exceptions
WHERE modname='Users/User.php&category_id=1&user_profile'" );
// Check if we have staff_exceptions.
$staff_exceptions_user_ids = DBGet( "SELECT user_id
FROM staff_exceptions
WHERE modname='Users/User.php&category_id=1'" );
if ( ! $up_staff_exceptions_exists
&& $staff_exceptions_user_ids )
{
foreach ( (array) $staff_exceptions_user_ids as $user_id )
{
DBQuery( "INSERT INTO staff_exceptions
VALUES ('" . $user_id['USER_ID'] . "', 'Users/User.php&category_id=1&user_profile', 'Y', 'Y');" );
}
}
return $return;
}
/**
* Update to version 3.0
*
* Add Access Log.
* 1. Add access_log table.
* Will not grant access to the program to Admins.
* Go to Users > User Profiles for that.
*
* Local function
*
* @since 3.0
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update30()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add access_log table.
*/
$access_log_table_exists = DBGet( "SELECT 1
FROM pg_catalog.pg_tables
WHERE tablename = 'access_log'" );
if ( ! $access_log_table_exists )
{
DBQuery( "CREATE TABLE access_log (
syear numeric(4,0),
username character varying(100),
profile character varying(30),
login_time timestamp(0) without time zone,
ip_address character varying(50),
user_agent text,
status character varying(50)
);" );
} else {
// Add user_agent column.
$user_agent_column_exists = DBGet( "SELECT 1 FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'access_log')
AND attname = 'user_agent';" );
if ( ! $user_agent_column_exists )
{
DBQuery( "ALTER TABLE ONLY access_log
ADD COLUMN user_agent text;" );
}
}
return $return;
}
/**
* Update to version 3.1
*
* Fix SQL error when entering (Unweighted) GPA Value > 99.99
* 1. report_card_grades table:
* Change gpa_value & unweighted_gp columns type to numeric
*
* 2. report_card_grade_scales table:
* Change hhr_gpa_value & hr_gpa_value & hrs_gpa_value columns type to numeric
* Was numeric(4,2) which would prevent to enter values like 100 (or above).
*
* Add Mass Create Assignments program.
* 3. Add Grades/MassCreateAssignments.php to profile_exceptions table.
*
* Local function
*
* @since 3.1
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update31()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. report_card_grades table:
* Change gpa_value & unweighted_gp columns type to numeric
* Was numeric(4,2) which would prevent to enter values like 100.
*/
DBQuery( "ALTER TABLE report_card_grades
ALTER COLUMN gpa_value TYPE numeric;" );
DBQuery( "ALTER TABLE report_card_grades
ALTER COLUMN unweighted_gp TYPE numeric;" );
/**
* 2. report_card_grade_scales table:
* Change hhr_gpa_value & hr_gpa_value & hrs_gpa_value columns type to numeric
* Was numeric(4,2) which would prevent to enter values like 100 (or above).
*/
DBQuery( "ALTER TABLE report_card_grade_scales
ALTER COLUMN hhr_gpa_value TYPE numeric;" );
DBQuery( "ALTER TABLE report_card_grade_scales
ALTER COLUMN hr_gpa_value TYPE numeric;" );
DBQuery( "ALTER TABLE report_card_grade_scales
ALTER COLUMN hrs_gpa_value TYPE numeric;" );
/**
* 3. Add Grades/MassCreateAssignments.php to profile_exceptions table.
*/
$admin_profiles_RET = DBGet( "SELECT id
FROM user_profiles
WHERE profile='admin'" );
foreach ( (array) $admin_profiles_RET as $admin_profile )
{
$profile_id = $admin_profile['ID'];
$mca_profile_exceptions_exists = DBGet( "SELECT 1
FROM profile_exceptions
WHERE profile_id='" . $profile_id . "'
AND modname='Grades/MassCreateAssignments.php'" );
if ( ! $mca_profile_exceptions_exists )
{
DBQuery( "INSERT INTO profile_exceptions
VALUES ('" . $profile_id . "', 'Grades/MassCreateAssignments.php', 'Y', 'Y');" );
}
}
return $return;
}
/**
* Update to version 3.5
*
* 1. Add FAILED_LOGIN_LIMIT to config table
*
* Local function
*
* @since 3.5
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update35()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add FAILED_LOGIN_LIMIT to config table.
*/
$failed_login_limit_added = DBGet( "SELECT 1 FROM config
WHERE TITLE='FAILED_LOGIN_LIMIT'" );
if ( ! $failed_login_limit_added )
{
DBQuery( "INSERT INTO config VALUES (0, 'FAILED_LOGIN_LIMIT', NULL);" );
}
return $return;
}
/**
* Update to version 3.7-beta
*
* 1. Add DISPLAY_NAME to config table
*
* Local function
*
* @since 3.7
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update37beta()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add DISPLAY_NAME to config table.
*/
$display_name_added = DBGet( "SELECT 1 FROM config WHERE TITLE='DISPLAY_NAME'" );
if ( ! $display_name_added )
{
// Fix empty string to NULL using Posix escape string syntax (E + backslash).
DBQuery( "INSERT INTO config VALUES (0, 'DISPLAY_NAME', E'FIRST_NAME||coalesce(\' \'||MIDDLE_NAME||\' \',\' \')||LAST_NAME');" );
}
return $return;
}
/**
* Update to version 3.9
*
* 1. Add DISPLAY_NAME to config table for every school.
*
* Local function
*
* @since 3.9
*
* @return boolean false if update failed or if not called by Update(), else true
*/
function _update39()
{
_isCallerUpdate( debug_backtrace() );
$return = true;
/**
* 1. Add DISPLAY_NAME to config table for every school.
*/
$display_name_added = DBGet( "SELECT 1 FROM config WHERE TITLE='DISPLAY_NAME'
AND SCHOOL_ID<>0" );
if ( ! $display_name_added )
{
// Fix empty string to NULL using Posix escape string syntax (E + backslash).
DBQuery( "INSERT INTO config SELECT DISTINCT ID, 'DISPLAY_NAME', E'FIRST_NAME||coalesce(\' \'||MIDDLE_NAME||\' \',\' \')||LAST_NAME' FROM schools;" );
}
return $return;
}