<?php
/**
 * Update functions
 *
 * Incremental updates
 *
 * Update() function called if ROSARIO_VERSION != version in DB
 *
 * @package RosarioSIS
 * @subpackage ProgramFunctions
 */

/**
 * Update manager function
 *
 * Call the specific versions functions
 *
 * @since 2.9
 *
 * @return boolean false if wrong version or update failed, else true
 */
function Update()
{
	$from_version = Config( 'VERSION' );

	$to_version = ROSARIO_VERSION;

	/**
	 * Check if Update() version < ROSARIO_VERSION.
	 *
	 * Prevent DB version update if new Update.fnc.php file has NOT been uploaded YET.
	 * Update must be run once both new Warehouse.php & Update.fnc.php files are uploaded.
	 */
	if ( version_compare( '12.6', ROSARIO_VERSION, '<' ) )
	{
		return false;
	}

	// Check if version in DB >= ROSARIO_VERSION.
	if ( version_compare( $from_version, $to_version, '>=' ) )
	{
		return false;
	}

	require_once 'ProgramFunctions/UpdateV2_3.fnc.php';
	require_once 'ProgramFunctions/UpdateV4_5.fnc.php';
	require_once 'ProgramFunctions/UpdateV6_8_9.fnc.php';

	$return = true;

	switch ( true )
	{
		case version_compare( $from_version, '2.9-alpha', '<' ) :

			$return = _update29alpha();

		case version_compare( $from_version, '2.9.2', '<' ) :

			$return = _update292();

		case version_compare( $from_version, '2.9.5', '<' ) :

			$return = _update295();

		case version_compare( $from_version, '2.9.12', '<' ) :

			$return = _update2912();

		case version_compare( $from_version, '2.9.13', '<' ) :

			$return = _update2913();

		case version_compare( $from_version, '2.9.14', '<' ) :

			$return = _update2914();

		case version_compare( $from_version, '3.0', '<' ) :

			$return = _update30();

		case version_compare( $from_version, '3.1', '<' ) :

			$return = _update31();

		case version_compare( $from_version, '3.5', '<' ) :

			$return = _update35();

		case version_compare( $from_version, '3.7-beta', '<' ) :

			$return = _update37beta();

		case version_compare( $from_version, '3.9', '<' ) :

			$return = _update39();

		case version_compare( $from_version, '4.0-beta', '<' ) :

			$return = _update40beta();

		case version_compare( $from_version, '4.2-beta', '<' ) :

			$return = _update42beta();

		case version_compare( $from_version, '4.3-beta', '<' ) :

			$return = _update43beta();

		case version_compare( $from_version, '4.4-beta', '<' ) :

			$return = _update44beta();

		case version_compare( $from_version, '4.4-beta2', '<' ) :

			$return = _update44beta2();

		case version_compare( $from_version, '4.5-beta2', '<' ) :

			$return = _update45beta2();

		case version_compare( $from_version, '4.6-beta', '<' ) :

			$return = _update46beta();

		case version_compare( $from_version, '4.7-beta', '<' ) :

			$return = _update47beta();

		case version_compare( $from_version, '4.7-beta2', '<' ) :

			$return = _update47beta2();

		case version_compare( $from_version, '4.9-beta', '<' ) :

			$return = _update49beta();

		case version_compare( $from_version, '5.0-beta', '<' ) :

			$return = _update50beta();

		case version_compare( $from_version, '5.0.1', '<' ) :

			$return = _update501();

		case version_compare( $from_version, '5.2-beta', '<' ) :

			$return = _update52beta();

		case version_compare( $from_version, '5.3-beta', '<' ) :

			$return = _update53beta();

		case version_compare( $from_version, '5.4.1', '<' ) :

			$return = _update541();

		case version_compare( $from_version, '5.4.2', '<' ) :

			$return = _update542();

		case version_compare( $from_version, '5.5-beta3', '<' ) :

			$return = _update55beta3();

		case version_compare( $from_version, '5.7', '<' ) :

			$return = _update57();

		case version_compare( $from_version, '5.8-beta5', '<' ) :

			$return = _update58beta5();

		case version_compare( $from_version, '5.9-beta', '<' ) :

			$return = _update59beta();

		case version_compare( $from_version, '5.9-beta2', '<' ) :

			$return = _update59beta2();

		case version_compare( $from_version, '5.9', '<' ) :

			$return = _update59();

		case version_compare( $from_version, '5.9.1', '<' ) :

			$return = _update591();

		case version_compare( $from_version, '6.3', '<' ) :

			$return = _update63();

		case version_compare( $from_version, '6.6', '<' ) :

			$return = _update66();

		case version_compare( $from_version, '6.9-beta', '<' ) :

			$return = _update69beta();

		case version_compare( $from_version, '8.1', '<' ) :

			$return = _update81();

		case version_compare( $from_version, '8.3', '<' ) :

			$return = _update83();

		case version_compare( $from_version, '8.4', '<' ) :

			$return = _update84();

		case version_compare( $from_version, '8.5', '<' ) :

			$return = _update85();

		case version_compare( $from_version, '8.7', '<' ) :

			$return = _update87();

		case version_compare( $from_version, '9.2', '<' ) :

			$return = _update92();

		case version_compare( $from_version, '9.2.1', '<' ) :

			$return = _update921();

		case version_compare( $from_version, '9.3', '<' ) :

			$return = _update93();

		case version_compare( $from_version, '10.1', '<' ) :

			$return = _update101();

		case version_compare( $from_version, '10.6.1', '<' ) :

			$return = _update1061();

		case version_compare( $from_version, '10.8', '<' ) :

			$return = _update108();

		case version_compare( $from_version, '10.9', '<' ) :

			$return = _update109();

		case version_compare( $from_version, '11.0', '<' ) :

			$return = _update110();

		case version_compare( $from_version, '11.1', '<' ) :

			$return = _update111();

		case version_compare( $from_version, '11.2', '<' ) :

			$return = _update112();

		case version_compare( $from_version, '11.2.1', '<' ) :

			$return = _update1121();

		case version_compare( $from_version, '11.3.1', '<' ) :

			$return = _update1131();

		case version_compare( $from_version, '11.4.4', '<' ) :

			$return = _update1144();

		case version_compare( $from_version, '12.0', '<' ) :

			$return = _update120();

		case version_compare( $from_version, '12.1', '<' ) :

			$return = _update121();

		case version_compare( $from_version, '12.2', '<' ) :

			$return = _update122();

		case version_compare( $from_version, '12.3', '<' ) :

			$return = _update123();

		case version_compare( $from_version, '12.6', '<' ) :

			$return = _update126();
	}

	// Update version in DB config table.
	Config( 'VERSION', ROSARIO_VERSION );

	return $return;
}


/**
 * Is function called by Update()?
 *
 * Local function
 *
 * @example _isCallerUpdate( debug_backtrace() );
 *
 * @since 2.9.13
 *
 * @param  array   $callers debug_backtrace().
 *
 * @return boolean          Exit with error message if not called by Update().
 */
function _isCallerUpdate( $callers )
{
	if ( ! isset( $callers[1]['function'] )
		|| $callers[1]['function'] !== 'Update' )
	{
		exit( 'Error: the update functions must be called by Update() only!' );
	}

	return true;
}


/**
 * Update to version 10.1
 *
 * 1. Add dual VIEW for compatibility with MySQL 5.6 to avoid syntax error when WHERE without FROM clause
 *
 * Local function
 *
 * @since 10.1
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update101()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. Add dual VIEW for compatibility with MySQL 5.6 to avoid syntax error when WHERE without FROM clause
	 * PostgreSQL only
	 */
	if ( $DatabaseType !== 'mysql' )
	{
		DBQuery( "CREATE OR REPLACE VIEW dual AS SELECT 'X' AS dummy;" );
	}

	return $return;
}

/**
 * Update to version 10.6.1
 * Fix Class Rank float comparison issue: do NOT use double precision type (inexact), use numeric (exact)
 * @link https://www.rosariosis.org/forum/d/665-le-classement-diff-rent-mais-m-me-moyenne/
 * Fix regression since 10.0, change sum/cum factors & credit_attempted/earned columns type from double precision to numeric
 *
 * PostgreSQL
 * 1. Drop transcript_grades view, so we can alter student_report_card_grades table
 * 2. SQL student_report_card_grades table: convert CREDIT_ATTEMPTED & CREDIT_EARNED columns to numeric
 * 3. SQL student_mp_stats table: convert sum/cum factors & credit_attempted/earned columns to numeric
 * 4. Recreate transcript_grades view
 *
 * MySQL
 * 1. SQL student_report_card_grades table: convert CREDIT_ATTEMPTED & CREDIT_EARNED columns to numeric(22,16)
 * 2. SQL student_mp_stats table: convert sum/cum factors & credit_attempted/earned columns to numeric(22,16)
 *
 * Local function
 *
 * @since 10.6.1
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update1061()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	if ( $DatabaseType === 'postgresql' )
	{
		// Check if data type is numeric already.
		$data_type_is_numeric = DBGetOne( "SELECT 1
			FROM student_report_card_grades
			WHERE CAST(PG_TYPEOF(credit_attempted) AS varchar(7))='numeric'
			LIMIT 1;" );
	}
	else
	{
		// Check if data type is numeric already.
		$data_type_is_numeric = DBGetOne( "SELECT 1
			FROM information_schema.columns
			WHERE table_schema=DATABASE()
			AND table_name='student_report_card_grades'
			AND COLUMN_NAME='credit_attempted'
			AND DATA_TYPE='decimal'
			LIMIT 1;" );
	}

	if ( $data_type_is_numeric )
	{
		return $return;
	}

	if ( $DatabaseType === 'postgresql' )
	{
		// 1. Drop transcript_grades view, so we can alter student_report_card_grades table
		DBQuery( "DROP VIEW transcript_grades;" );

		// 2. SQL student_report_card_grades table: convert CREDIT_ATTEMPTED & CREDIT_EARNED columns to numeric
		DBQuery( "ALTER TABLE student_report_card_grades
		ALTER credit_attempted TYPE numeric,
		ALTER credit_earned TYPE numeric;" );

		// 3. SQL student_mp_stats table: convert sum/cum factors & credit_attempted/earned columns to numeric
		DBQuery( "ALTER TABLE student_mp_stats
		ALTER cum_weighted_factor TYPE numeric,
		ALTER cum_unweighted_factor TYPE numeric,
		ALTER sum_weighted_factors TYPE numeric,
		ALTER sum_unweighted_factors TYPE numeric,
		ALTER cr_weighted_factors TYPE numeric,
		ALTER cr_unweighted_factors TYPE numeric,
		ALTER cum_cr_weighted_factor TYPE numeric,
		ALTER cum_cr_unweighted_factor TYPE numeric,
		ALTER credit_attempted TYPE numeric,
		ALTER credit_earned TYPE numeric,
		ALTER gp_credits TYPE numeric,
		ALTER cr_credits TYPE numeric" );

		// 4. Recreate transcript_grades view
		DBQuery( "CREATE VIEW transcript_grades AS
		SELECT mp.syear,mp.school_id,mp.marking_period_id,mp.mp_type,
		mp.short_name,mp.parent_id,mp.grandparent_id,
		(SELECT mp2.end_date
			FROM student_report_card_grades
				JOIN marking_periods mp2
				ON mp2.marking_period_id = student_report_card_grades.marking_period_id
			WHERE student_report_card_grades.student_id = sms.student_id
			AND (student_report_card_grades.marking_period_id = mp.parent_id
				OR student_report_card_grades.marking_period_id = mp.grandparent_id)
			AND student_report_card_grades.course_title = srcg.course_title
			ORDER BY mp2.end_date LIMIT 1) AS parent_end_date,
		mp.end_date,sms.student_id,
		(sms.cum_weighted_factor * COALESCE(schools.reporting_gp_scale, (SELECT reporting_gp_scale FROM schools WHERE mp.school_id = id ORDER BY syear LIMIT 1))) AS cum_weighted_gpa,
		(sms.cum_unweighted_factor * schools.reporting_gp_scale) AS cum_unweighted_gpa,
		sms.cum_rank,sms.mp_rank,sms.class_size,
		((sms.sum_weighted_factors / sms.count_weighted_factors) * schools.reporting_gp_scale) AS weighted_gpa,
		((sms.sum_unweighted_factors / sms.count_unweighted_factors) * schools.reporting_gp_scale) AS unweighted_gpa,
		sms.grade_level_short,srcg.comment,srcg.grade_percent,srcg.grade_letter,
		srcg.weighted_gp,srcg.unweighted_gp,srcg.gp_scale,srcg.credit_attempted,
		srcg.credit_earned,srcg.course_title,srcg.school AS school_name,
		schools.reporting_gp_scale AS school_scale,
		((sms.cr_weighted_factors / sms.count_cr_factors::numeric) * schools.reporting_gp_scale) AS cr_weighted_gpa,
		((sms.cr_unweighted_factors / sms.count_cr_factors::numeric) * schools.reporting_gp_scale) AS cr_unweighted_gpa,
		(sms.cum_cr_weighted_factor * schools.reporting_gp_scale) AS cum_cr_weighted_gpa,
		(sms.cum_cr_unweighted_factor * schools.reporting_gp_scale) AS cum_cr_unweighted_gpa,
		srcg.class_rank,sms.comments,
		srcg.credit_hours
		FROM marking_periods mp
			JOIN student_report_card_grades srcg
			ON mp.marking_period_id = srcg.marking_period_id
			JOIN student_mp_stats sms
			ON sms.marking_period_id = mp.marking_period_id
				AND sms.student_id = srcg.student_id
			LEFT OUTER JOIN schools
			ON mp.school_id = schools.id
				AND (mp.mp_source<>'History' AND mp.syear = schools.syear)
					OR (mp.mp_source='History' AND mp.syear=(SELECT syear FROM schools WHERE mp.school_id = id ORDER BY syear LIMIT 1))
		ORDER BY srcg.course_period_id;" );
	}
	else
	{
		// MySQL.
		// 1. SQL student_report_card_grades table: convert CREDIT_ATTEMPTED & CREDIT_EARNED columns to numeric(22,16)
		DBQuery( "ALTER TABLE student_report_card_grades
		CHANGE credit_attempted credit_attempted numeric(22,16),
		CHANGE credit_earned credit_earned numeric(22,16);" );

		// 2. SQL student_mp_stats table: convert sum/cum factors & credit_attempted/earned columns to numeric(22,16)
		DBQuery( "ALTER TABLE student_mp_stats
		CHANGE cum_weighted_factor cum_weighted_factor numeric(22,16),
		CHANGE cum_unweighted_factor cum_unweighted_factor numeric(22,16),
		CHANGE sum_weighted_factors sum_weighted_factors numeric(22,16),
		CHANGE sum_unweighted_factors sum_unweighted_factors numeric(22,16),
		CHANGE cr_weighted_factors cr_weighted_factors numeric(22,16),
		CHANGE cr_unweighted_factors cr_unweighted_factors numeric(22,16),
		CHANGE cum_cr_weighted_factor cum_cr_weighted_factor numeric(22,16),
		CHANGE cum_cr_unweighted_factor cum_cr_unweighted_factor numeric(22,16),
		CHANGE credit_attempted credit_attempted numeric(22,16),
		CHANGE credit_earned credit_earned numeric(22,16),
		CHANGE gp_credits gp_credits numeric(22,16),
		CHANGE cr_credits cr_credits numeric(22,16);" );
	}

	return $return;
}


/**
 * Update to version 10.8
 *
 * 1. resources table: Add PUBLISHED_PROFILES & PUBLISHED_GRADE_LEVELS columns.
 *
 * Local function
 *
 * @since 10.8
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update108()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. resources table: Add PUBLISHED_PROFILES & PUBLISHED_GRADE_LEVELS columns.
	 */
	$published_profiles_column_exists = DBGetOne( "SELECT 1
		FROM information_schema.columns
		WHERE table_schema=" . ( $DatabaseType === 'mysql' ? 'DATABASE()' : 'CURRENT_SCHEMA()' ) . "
		AND table_name='resources'
		AND column_name='published_profiles';" );

	if ( ! $published_profiles_column_exists )
	{
		DBQuery( "ALTER TABLE resources
		ADD published_profiles text,
		ADD published_grade_levels text;" );
	}

	return $return;
}


/**
 * Update to version 10.9
 *
 * 1. gradebook_assignments table: Add WEIGHT column.
 *
 * Local function
 *
 * @since 10.9
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update109()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. gradebook_assignments table: Add WEIGHT column.
	 */
	$weight_column_exists = DBGetOne( "SELECT 1
		FROM information_schema.columns
		WHERE table_schema=" . ( $DatabaseType === 'mysql' ? 'DATABASE()' : 'CURRENT_SCHEMA()' ) . "
		AND table_name='gradebook_assignments'
		AND column_name='weight';" );

	if ( ! $weight_column_exists )
	{
		DBQuery( "ALTER TABLE gradebook_assignments
		ADD weight integer;" );
	}

	return $return;
}


/**
 * Update to version 11.0
 *
 * 1. Move "Progress Reports" from Teacher Programs to Grades menu (admin)
 * 2. Add accounting_categories table
 * 3. Add TITLE column to accounting_payments table
 * 4. Add CATEGORY_ID column to accounting_incomes & accounting_payments tables
 * 5. Give admin profile access to Accounting > Categories program
 *
 * Local function
 *
 * @since 11.0
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update110()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. Move "Progress Reports" from Teacher Programs to Grades menu (admin)
	 */
	$profile_exception_exists = DBGetOne( "SELECT 1
		FROM profile_exceptions
		WHERE MODNAME='Grades/ProgressReports.php'
		AND PROFILE_ID='1'" );

	if ( ! $profile_exception_exists )
	{
		DBQuery( "UPDATE profile_exceptions
			SET MODNAME='Grades/ProgressReports.php'
			WHERE MODNAME='Users/TeacherPrograms.php&include=Grades/ProgressReports.php'
			AND PROFILE_ID='1'" );
	}

	$staff_exceptions_user_ids = DBGetOne( "SELECT " . DBSQLCommaSeparatedResult( 'USER_ID' ) . " AS USER_IDS
		FROM staff_exceptions
		WHERE MODNAME='Grades/ProgressReports.php'" );

	$where_not_user_id_sql = '';

	if ( $staff_exceptions_user_ids )
	{
		$where_not_user_id_sql = " AND USER_ID NOT IN(" . $staff_exceptions_user_ids . ")";
	}

	DBQuery( "UPDATE staff_exceptions
		SET MODNAME='Grades/ProgressReports.php'
		WHERE MODNAME='Users/TeacherPrograms.php&include=Grades/ProgressReports.php'" . $where_not_user_id_sql );

	/**
	 * 2. Add accounting_categories table
	 */
	$accounting_categories_table_exists = DBGetOne( "SELECT 1
		FROM information_schema.tables
		WHERE table_schema=" . ( $DatabaseType === 'mysql' ? 'DATABASE()' : 'CURRENT_SCHEMA()' ) . "
		AND table_name='accounting_categories';" );

	if ( ! $accounting_categories_table_exists )
	{
		if ( $DatabaseType === 'mysql' )
		{
			DBQuery( "CREATE TABLE accounting_categories (
				id integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
				school_id integer NOT NULL,
				title text NOT NULL,
				short_name varchar(10),
				type varchar(100),
				sort_order numeric,
				created_at timestamp DEFAULT current_timestamp,
				updated_at timestamp NULL ON UPDATE current_timestamp
			);" );
		}
		else
		{
			DBQuery( "CREATE TABLE accounting_categories (
				id serial PRIMARY KEY,
				school_id integer NOT NULL,
				title text NOT NULL,
				short_name varchar(10),
				type varchar(100),
				sort_order numeric,
				created_at timestamp DEFAULT current_timestamp,
				updated_at timestamp
			);" );
		}
	}

	/**
	 * 3. Add TITLE column to accounting_payments table
	 */
	$title_column_exists = DBGetOne( "SELECT 1
		FROM information_schema.columns
		WHERE table_schema=" . ( $DatabaseType === 'mysql' ? 'DATABASE()' : 'CURRENT_SCHEMA()' ) . "
		AND table_name='accounting_payments'
		AND column_name='title';" );

	if ( ! $title_column_exists )
	{
		DBQuery( "ALTER TABLE accounting_payments ADD title text" );
	}

	/**
	 * 4. Add CATEGORY_ID column to accounting_incomes & accounting_payments tables
	 */
	$category_id_column_exists = DBGetOne( "SELECT 1
		FROM information_schema.columns
		WHERE table_schema=" . ( $DatabaseType === 'mysql' ? 'DATABASE()' : 'CURRENT_SCHEMA()' ) . "
		AND table_name='accounting_incomes'
		AND column_name='category_id';" );

	if ( ! $category_id_column_exists )
	{
		DBQuery( "ALTER TABLE accounting_incomes ADD category_id integer,
			ADD FOREIGN KEY (category_id) REFERENCES accounting_categories(id);
			ALTER TABLE accounting_payments ADD category_id integer,
			ADD FOREIGN KEY (category_id) REFERENCES accounting_categories(id);" );
	}

	/**
	 * 5. Give admin profile access to Accounting > Categories program
	 */
	DBQuery( "INSERT INTO profile_exceptions (profile_id, modname, can_use, can_edit)
		SELECT 1, 'Accounting/Categories.php', 'Y', 'Y'
		FROM DUAL
		WHERE NOT EXISTS (SELECT profile_id
			FROM profile_exceptions
			WHERE modname='Accounting/Categories.php'
			AND profile_id=1);" );

	return $return;
}


/**
 * Update to version 11.1
 *
 * 0. Drop calc_cum_cr_gpa & calc_cum_gpa procedures (MySQL only)
 * 0. Create plpgsql language in case it does not exist (PostgreSQL only)
 * 1. SQL set min Credits to 0 & fix division by zero error
 *
 * Local function
 *
 * @since 11.1
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update111()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	if ( $DatabaseType === 'mysql' )
	{
		/**
		 * MySQL
		 *
		 * 0. Drop calc_cum_cr_gpa & calc_cum_gpa procedures
		 * 1. SQL set min Credits to 0 & fix division by zero error
		 */
		$mysql_no_delimiter = MySQLRemoveDelimiter( "DROP PROCEDURE IF EXISTS calc_cum_cr_gpa;
		DROP PROCEDURE IF EXISTS calc_cum_gpa;

		--
		-- Name: calc_cum_cr_gpa(mp_id integer, s_id integer); Type: FUNCTION;
		-- @since 11.1 SQL set min Credits to 0 & fix division by zero error
		--

		DELIMITER $$
		CREATE PROCEDURE calc_cum_cr_gpa(mp_id integer, s_id integer)
		BEGIN
			UPDATE student_mp_stats
			SET cum_cr_weighted_factor = (case when cr_credits = '0' THEN '0' ELSE cr_weighted_factors/cr_credits END),
				cum_cr_unweighted_factor = (case when cr_credits = '0' THEN '0' ELSE cr_unweighted_factors/cr_credits END)
			WHERE student_mp_stats.student_id = s_id and student_mp_stats.marking_period_id = mp_id;
		END$$
		DELIMITER ;


		--
		-- Name: calc_cum_gpa(mp_id integer, s_id integer); Type: FUNCTION;
		-- @since 11.1 SQL set min Credits to 0 & fix division by zero error
		--

		DELIMITER $$
		CREATE PROCEDURE calc_cum_gpa(mp_id integer, s_id integer)
		BEGIN
			UPDATE student_mp_stats
			SET cum_weighted_factor = (case when gp_credits = '0' THEN '0' ELSE sum_weighted_factors/gp_credits END),
				cum_unweighted_factor = (case when gp_credits = '0' THEN '0' ELSE sum_unweighted_factors/gp_credits END)
			WHERE student_mp_stats.student_id = s_id and student_mp_stats.marking_period_id = mp_id;
		END$$
		DELIMITER ;" );

		DBQuery( $mysql_no_delimiter );

		return $return;
	}

	/**
	 * PostgreSQL
	 *
	 * 0. Create plpgsql language in case it does not exist
	 * 1. SQL set min Credits to 0 & fix division by zero error
	 */
	DBQuery( "CREATE OR REPLACE FUNCTION create_language_plpgsql() RETURNS boolean AS $$
		CREATE LANGUAGE plpgsql;
		SELECT TRUE;
	$$ LANGUAGE SQL;

	SELECT CASE WHEN NOT
		(
			SELECT  TRUE AS exists
			FROM    pg_language
			WHERE   lanname = 'plpgsql'
			UNION
			SELECT  FALSE AS exists
			ORDER BY exists DESC
			LIMIT 1
		)
	THEN
		create_language_plpgsql()
	ELSE
		FALSE
	END AS plpgsql_created;

	DROP FUNCTION create_language_plpgsql();


	--
	-- Name: calc_cum_cr_gpa(mp_id integer, s_id integer); Type: FUNCTION; Schema: public; Owner: postgres
	-- @since 11.1 SQL set min Credits to 0 & fix division by zero error
	--

	CREATE OR REPLACE FUNCTION calc_cum_cr_gpa(mp_id integer, s_id integer) RETURNS integer AS $$
	BEGIN
		UPDATE student_mp_stats
		SET cum_cr_weighted_factor = (case when cr_credits = '0' THEN '0' ELSE cr_weighted_factors/cr_credits END),
			cum_cr_unweighted_factor = (case when cr_credits = '0' THEN '0' ELSE cr_unweighted_factors/cr_credits END)
		WHERE student_mp_stats.student_id = s_id and student_mp_stats.marking_period_id = mp_id;
		RETURN 1;
	END;
	$$ LANGUAGE plpgsql;


	--
	-- Name: calc_cum_gpa(mp_id integer, s_id integer); Type: FUNCTION; Schema: public; Owner: postgres
	-- @since 11.1 SQL set min Credits to 0 & fix division by zero error
	--

	CREATE OR REPLACE FUNCTION calc_cum_gpa(mp_id integer, s_id integer) RETURNS integer AS $$
	BEGIN
		UPDATE student_mp_stats
		SET cum_weighted_factor = (case when gp_credits = '0' THEN '0' ELSE sum_weighted_factors/gp_credits END),
			cum_unweighted_factor = (case when gp_credits = '0' THEN '0' ELSE sum_unweighted_factors/gp_credits END)
		WHERE student_mp_stats.student_id = s_id and student_mp_stats.marking_period_id = mp_id;
		RETURN 1;
	END;
	$$ LANGUAGE plpgsql;" );

	return $return;
}


/**
 * Update to version 11.2
 *
 * 1. Add CREATED_BY column to billing_fees & billing_payments tables
 *
 * Local function
 *
 * @since 11.2
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update112()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. Add CREATED_BY column to billing_fees & billing_payments tables
	 */
	$created_by_column_exists = DBGetOne( "SELECT 1
		FROM information_schema.columns
		WHERE table_schema=" . ( $DatabaseType === 'mysql' ? 'DATABASE()' : 'CURRENT_SCHEMA()' ) . "
		AND table_name='billing_fees'
		AND column_name='created_by';" );

	if ( ! $created_by_column_exists )
	{
		DBQuery( "ALTER TABLE billing_fees ADD created_by text;
			ALTER TABLE billing_payments ADD created_by text;" );
	}

	return $return;
}


/**
 * Update to version 11.2.1
 *
 * 1. Add MENU_ITEM_ID column to food_service_transaction_items & food_service_staff_transaction_items tables
 * Fix regression since 11.2 PostgreSQL error duplicate key value violates unique constraint
 * Fix regression since 11.2 MySQL error duplicate entry '2-3' for key 'PRIMARY'
 *
 * Local function
 *
 * @since 11.2.1
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update1121()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. Add MENU_ITEM_ID column to food_service_transaction_items & food_service_staff_transaction_items tables
	 */
	$menu_item_id_column_exists = DBGetOne( "SELECT 1
		FROM information_schema.columns
		WHERE table_schema=" . ( $DatabaseType === 'mysql' ? 'DATABASE()' : 'CURRENT_SCHEMA()' ) . "
		AND table_name='food_service_transaction_items'
		AND column_name='menu_item_id';" );

	if ( ! $menu_item_id_column_exists )
	{
		DBQuery( "ALTER TABLE food_service_transaction_items
		ADD menu_item_id integer;" );
	}

	$menu_item_id_column_exists = DBGetOne( "SELECT 1
		FROM information_schema.columns
		WHERE table_schema=" . ( $DatabaseType === 'mysql' ? 'DATABASE()' : 'CURRENT_SCHEMA()' ) . "
		AND table_name='food_service_staff_transaction_items'
		AND column_name='menu_item_id';" );

	if ( ! $menu_item_id_column_exists )
	{
		DBQuery( "ALTER TABLE food_service_staff_transaction_items
		ADD menu_item_id integer;" );
	}

	return $return;
}


/**
 * Update to version 11.3.1
 *
 * 1. Update set_class_rank_mp() function
 * Fix regression since 4.7 SQL set_class_rank_mp() Class Rank calculation when various school years
 *
 * Local function
 *
 * @since 11.3.1
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update1131()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. Update set_class_rank_mp() function
	 */
	if ( $DatabaseType === 'mysql' )
	{
		$mysql_no_delimiter = MySQLRemoveDelimiter( "DROP FUNCTION IF EXISTS set_class_rank_mp;

		DELIMITER $$
		CREATE FUNCTION set_class_rank_mp(mp_id integer) RETURNS integer
		BEGIN
		    update student_mp_stats sms
		    JOIN (
		        select mp.marking_period_id, sgm.student_id,
		        (select count(*)+1
		            from student_mp_stats sgm3
		            where sgm3.cum_cr_weighted_factor > sgm.cum_cr_weighted_factor
		            and sgm3.marking_period_id = mp.marking_period_id
		            and sgm3.student_id in (select distinct sgm2.student_id
		                from student_mp_stats sgm2, student_enrollment se2
		                where sgm2.student_id = se2.student_id
		                and sgm2.marking_period_id = mp.marking_period_id
		                and se2.grade_id = se.grade_id
		                and se2.syear = se.syear)) as class_rank,
		        (select count(*)
		            from student_mp_stats sgm4
		            where sgm4.marking_period_id = mp.marking_period_id
		            and sgm4.student_id in (select distinct sgm5.student_id
		                from student_mp_stats sgm5, student_enrollment se3
		                where sgm5.student_id = se3.student_id
		                and sgm5.marking_period_id = mp.marking_period_id
		                and se3.grade_id = se.grade_id
		                and se3.syear = se.syear)) as class_size
		        from student_enrollment se, student_mp_stats sgm, marking_periods mp
		        where se.student_id = sgm.student_id
		        and sgm.marking_period_id = mp.marking_period_id
		        and mp.marking_period_id = mp_id
		        and se.syear = mp.syear
		        and not sgm.cum_cr_weighted_factor is null
		    ) as class_rank
		    ON sms.marking_period_id = class_rank.marking_period_id and sms.student_id = class_rank.student_id
		    set sms.cum_rank = class_rank.class_rank, sms.class_size = class_rank.class_size;
		    RETURN 1;
		END$$
		DELIMITER ;" );

		DBQuery( $mysql_no_delimiter );

		return $return;
	}

	// PostgreSQL.
	// Note: the old set_class_rank_mp(character varying) function was not replaced here
	// as the new set_class_rank_mp(mp_id integer) function's parameter differs...
	DBQuery( "CREATE FUNCTION create_language_plpgsql()
	RETURNS BOOLEAN AS $$
		CREATE LANGUAGE plpgsql;
		SELECT TRUE;
	$$ LANGUAGE SQL;

	SELECT CASE WHEN NOT
		(
			SELECT  TRUE AS exists
			FROM    pg_language
			WHERE   lanname = 'plpgsql'
			UNION
			SELECT  FALSE AS exists
			ORDER BY exists DESC
			LIMIT 1
		)
	THEN
		create_language_plpgsql()
	ELSE
		FALSE
	END AS plpgsql_created;

	DROP FUNCTION create_language_plpgsql();

	CREATE OR REPLACE FUNCTION set_class_rank_mp(mp_id integer) RETURNS integer AS $$
	BEGIN
	update student_mp_stats
	set cum_rank = class_rank.class_rank, class_size = class_rank.class_size
	from (select mp.marking_period_id, sgm.student_id,
	    (select count(*)+1
	        from student_mp_stats sgm3
	        where sgm3.cum_cr_weighted_factor > sgm.cum_cr_weighted_factor
	        and sgm3.marking_period_id = mp.marking_period_id
	        and sgm3.student_id in (select distinct sgm2.student_id
	            from student_mp_stats sgm2, student_enrollment se2
	            where sgm2.student_id = se2.student_id
	            and sgm2.marking_period_id = mp.marking_period_id
	            and se2.grade_id = se.grade_id
	            and se2.syear = se.syear)) as class_rank,
	    (select count(*)
	        from student_mp_stats sgm4
	        where sgm4.marking_period_id = mp.marking_period_id
	        and sgm4.student_id in (select distinct sgm5.student_id
	            from student_mp_stats sgm5, student_enrollment se3
	            where sgm5.student_id = se3.student_id
	            and sgm5.marking_period_id = mp.marking_period_id
	            and se3.grade_id = se.grade_id
	            and se3.syear = se.syear)) as class_size
	    from student_enrollment se, student_mp_stats sgm, marking_periods mp
	    where se.student_id = sgm.student_id
	    and sgm.marking_period_id = mp.marking_period_id
	    and mp.marking_period_id = mp_id
	    and se.syear = mp.syear
	    and not sgm.cum_cr_weighted_factor is null) as class_rank
	where student_mp_stats.marking_period_id = class_rank.marking_period_id
	and student_mp_stats.student_id = class_rank.student_id;
	RETURN 1;
	END;
	$$ LANGUAGE plpgsql;" );

	return $return;
}


/**
 * Update to version 11.4.4
 *
 * 1. Update transcript_grades view
 * Fix SQL transcript_grades view for Historical Marking Periods of existing school years
 *
 * Local function
 *
 * @since 11.4.4
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update1144()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. Update transcript_grades view
	 */
	if ( $DatabaseType === 'mysql' )
	{
		DBQuery( "CREATE OR REPLACE VIEW transcript_grades AS
    SELECT mp.syear,mp.school_id,mp.marking_period_id,mp.mp_type,
    mp.short_name,mp.parent_id,mp.grandparent_id,
    (SELECT mp2.end_date
        FROM student_report_card_grades
            JOIN marking_periods mp2
            ON mp2.marking_period_id = student_report_card_grades.marking_period_id
        WHERE student_report_card_grades.student_id = sms.student_id
        AND (student_report_card_grades.marking_period_id = mp.parent_id
            OR student_report_card_grades.marking_period_id = mp.grandparent_id)
        AND student_report_card_grades.course_title = srcg.course_title
        ORDER BY mp2.end_date LIMIT 1) AS parent_end_date,
    mp.end_date,sms.student_id,
    (sms.cum_weighted_factor * COALESCE(schools.reporting_gp_scale, (SELECT reporting_gp_scale FROM schools WHERE mp.school_id = id ORDER BY syear LIMIT 1))) AS cum_weighted_gpa,
    (sms.cum_unweighted_factor * schools.reporting_gp_scale) AS cum_unweighted_gpa,
    sms.cum_rank,sms.mp_rank,sms.class_size,
    ((sms.sum_weighted_factors / sms.count_weighted_factors) * schools.reporting_gp_scale) AS weighted_gpa,
    ((sms.sum_unweighted_factors / sms.count_unweighted_factors) * schools.reporting_gp_scale) AS unweighted_gpa,
    sms.grade_level_short,srcg.comment,srcg.grade_percent,srcg.grade_letter,
    srcg.weighted_gp,srcg.unweighted_gp,srcg.gp_scale,srcg.credit_attempted,
    srcg.credit_earned,srcg.course_title,srcg.school AS school_name,
    schools.reporting_gp_scale AS school_scale,
    ((sms.cr_weighted_factors / sms.count_cr_factors) * schools.reporting_gp_scale) AS cr_weighted_gpa,
    ((sms.cr_unweighted_factors / sms.count_cr_factors) * schools.reporting_gp_scale) AS cr_unweighted_gpa,
    (sms.cum_cr_weighted_factor * schools.reporting_gp_scale) AS cum_cr_weighted_gpa,
    (sms.cum_cr_unweighted_factor * schools.reporting_gp_scale) AS cum_cr_unweighted_gpa,
    srcg.class_rank,sms.comments,
    srcg.credit_hours
    FROM marking_periods mp
        JOIN student_report_card_grades srcg
        ON mp.marking_period_id = srcg.marking_period_id
        JOIN student_mp_stats sms
        ON sms.marking_period_id = mp.marking_period_id
            AND sms.student_id = srcg.student_id
        LEFT OUTER JOIN schools
        ON mp.school_id = schools.id
            AND mp.syear = schools.syear
    ORDER BY srcg.course_period_id;" );

		return $return;
	}

	// PostgreSQL.
	DBQuery( "CREATE OR REPLACE VIEW transcript_grades AS
    SELECT mp.syear,mp.school_id,mp.marking_period_id,mp.mp_type,
    mp.short_name,mp.parent_id,mp.grandparent_id,
    (SELECT mp2.end_date
        FROM student_report_card_grades
            JOIN marking_periods mp2
            ON mp2.marking_period_id = student_report_card_grades.marking_period_id
        WHERE student_report_card_grades.student_id = sms.student_id
        AND (student_report_card_grades.marking_period_id = mp.parent_id
            OR student_report_card_grades.marking_period_id = mp.grandparent_id)
        AND student_report_card_grades.course_title = srcg.course_title
        ORDER BY mp2.end_date LIMIT 1) AS parent_end_date,
    mp.end_date,sms.student_id,
    (sms.cum_weighted_factor * COALESCE(schools.reporting_gp_scale, (SELECT reporting_gp_scale FROM schools WHERE mp.school_id = id ORDER BY syear LIMIT 1))) AS cum_weighted_gpa,
    (sms.cum_unweighted_factor * schools.reporting_gp_scale) AS cum_unweighted_gpa,
    sms.cum_rank,sms.mp_rank,sms.class_size,
    ((sms.sum_weighted_factors / sms.count_weighted_factors) * schools.reporting_gp_scale) AS weighted_gpa,
    ((sms.sum_unweighted_factors / sms.count_unweighted_factors) * schools.reporting_gp_scale) AS unweighted_gpa,
    sms.grade_level_short,srcg.comment,srcg.grade_percent,srcg.grade_letter,
    srcg.weighted_gp,srcg.unweighted_gp,srcg.gp_scale,srcg.credit_attempted,
    srcg.credit_earned,srcg.course_title,srcg.school AS school_name,
    schools.reporting_gp_scale AS school_scale,
    ((sms.cr_weighted_factors / sms.count_cr_factors::numeric) * schools.reporting_gp_scale) AS cr_weighted_gpa,
    ((sms.cr_unweighted_factors / sms.count_cr_factors::numeric) * schools.reporting_gp_scale) AS cr_unweighted_gpa,
    (sms.cum_cr_weighted_factor * schools.reporting_gp_scale) AS cum_cr_weighted_gpa,
    (sms.cum_cr_unweighted_factor * schools.reporting_gp_scale) AS cum_cr_unweighted_gpa,
    srcg.class_rank,sms.comments,
    srcg.credit_hours
    FROM marking_periods mp
        JOIN student_report_card_grades srcg
        ON mp.marking_period_id = srcg.marking_period_id
        JOIN student_mp_stats sms
        ON sms.marking_period_id = mp.marking_period_id
            AND sms.student_id = srcg.student_id
        LEFT OUTER JOIN schools
        ON mp.school_id = schools.id
            AND mp.syear = schools.syear
    ORDER BY srcg.course_period_id;" );

	return $return;
}

/**
 * Update to version 12.0
 *
 * 1. Remove assets/AssignmentsFiles/ if only contains README
 * 2. Remove assets/PortalNotesFiles/ if only contains README
 * 3. Move Food Service Icons from assets/FS_icons/ to assets/FileUploads/FS_icons/
 *
 * @deprecated Assignments Files upload path $AssignmentsFilesPath global var
 * @deprecated Portal Notes Files upload path $PortalNotesFilesPath global var
 * @deprecated Food Service Icons upload path $FS_IconsPath global var
 *
 * Local function
 *
 * @since 12.0
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update120()
{
	global $FileUploadsPath;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	// 1. Remove assets/AssignmentsFiles/ if only contains README
	if ( is_dir( 'assets/AssignmentsFiles/' )
		// Check if dir has no subdir (apart from README).
		&& ! glob( 'assets/AssignmentsFiles/*', GLOB_ONLYDIR )
		&& is_writable( 'assets/AssignmentsFiles/' ) )
	{
		if ( file_exists( 'assets/AssignmentsFiles/README' ) )
		{
			@unlink( 'assets/AssignmentsFiles/README' );
		}

		if ( ! glob( 'assets/AssignmentsFiles/*' ) )
		{
			// Dir is empty, delete it.
			rmdir( 'assets/AssignmentsFiles/' );
		}
	}

 	// 2. Remove assets/PortalNotesFiles/ if only contains README
	if ( is_dir( 'assets/PortalNotesFiles/' )
		// Check if dir has no uploaded files (apart from README).
		&& ! glob( 'assets/PortalNotesFiles/*.*' )
		&& is_writable( 'assets/PortalNotesFiles/' ) )
	{
		if ( file_exists( 'assets/PortalNotesFiles/README' ) )
		{
			@unlink( 'assets/PortalNotesFiles/README' );
		}

		if ( ! glob( 'assets/PortalNotesFiles/*' ) )
		{
			// Dir is empty, delete it.
			rmdir( 'assets/PortalNotesFiles/' );
		}
	}

	// 3. Move Food Service Icons from assets/FS_icons/ to assets/FileUploads/FS_icons/
	if ( is_dir( 'assets/FileUploads/FS_icons/' )
		&& is_dir( 'assets/FS_icons/' )
		// Check if dir has files.
		&& ( $fs_icons_files = glob( 'assets/FS_icons/*' ) ) )
	{
		foreach ( (array) $fs_icons_files as $fs_icon_file )
		{
			@rename(
				$fs_icon_file,
				str_replace( 'assets/FS_icons/', $FileUploadsPath . 'FS_icons/', $fs_icon_file )
			);
		}

		if ( ! glob( 'assets/FS_icons/*' )
			&& is_writable( 'assets/FS_icons/' ) )
		{
			// Dir is now empty, delete it.
			rmdir( 'assets/FS_icons/' );
		}
	}

	return $return;
}

/**
 * Update to version 12.1
 *
 * Add Admin Delete Permission (Accounting Expenses/Incomes/Salaries/Staff Payments) to
 * 1. profile_exceptions table
 * 2. staff_exceptions table
 *
 * Local function
 *
 * @since 12.1
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update121()
{
	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. profile_exceptions table
	 * Add Admin Delete right (Accounting Expenses/Incomes/Salaries/Staff Payments)
	 */
	$insert_sql = "INSERT INTO profile_exceptions (profile_id, modname, can_use, can_edit)
		SELECT profile_id,'Accounting/Expenses.php&modfunc=remove','Y','Y'
		FROM profile_exceptions
		WHERE modname='Accounting/Expenses.php'
		AND can_edit='Y'
		AND profile_id NOT IN(SELECT profile_id
			FROM profile_exceptions
			WHERE modname='Accounting/Expenses.php&modfunc=remove');";

	$insert_sql .= "INSERT INTO profile_exceptions (profile_id, modname, can_use, can_edit)
		SELECT profile_id,'Accounting/Incomes.php&modfunc=remove','Y','Y'
		FROM profile_exceptions
		WHERE modname='Accounting/Incomes.php'
		AND can_edit='Y'
		AND profile_id NOT IN(SELECT profile_id
			FROM profile_exceptions
			WHERE modname='Accounting/Incomes.php&modfunc=remove');";

	$insert_sql .= "INSERT INTO profile_exceptions (profile_id, modname, can_use, can_edit)
		SELECT profile_id,'Accounting/Salaries.php&modfunc=remove','Y','Y'
		FROM profile_exceptions
		WHERE modname='Accounting/Salaries.php'
		AND can_edit='Y'
		AND profile_id NOT IN(SELECT profile_id
			FROM profile_exceptions
			WHERE modname='Accounting/Salaries.php&modfunc=remove');";

	$insert_sql .= "INSERT INTO profile_exceptions (profile_id, modname, can_use, can_edit)
		SELECT profile_id,'Accounting/StaffPayments.php&modfunc=remove','Y','Y'
		FROM profile_exceptions
		WHERE modname='Accounting/StaffPayments.php'
		AND can_edit='Y'
		AND profile_id NOT IN(SELECT profile_id
			FROM profile_exceptions
			WHERE modname='Accounting/StaffPayments.php&modfunc=remove');";

	DBQuery( $insert_sql );

	/**
	 * 2. staff_exceptions table
	 * Add Admin Delete right (Accounting Expenses/Incomes/Salaries/Staff Payments)
	 */
	$insert_sql = "INSERT INTO staff_exceptions (user_id, modname, can_use, can_edit)
		SELECT user_id,'Accounting/Expenses.php&modfunc=remove','Y','Y'
		FROM staff_exceptions
		WHERE modname='Accounting/Expenses.php'
		AND can_edit='Y'
		AND user_id NOT IN(SELECT user_id
			FROM staff_exceptions
			WHERE modname='Accounting/Expenses.php&modfunc=remove');";

	$insert_sql .= "INSERT INTO staff_exceptions (user_id, modname, can_use, can_edit)
		SELECT user_id,'Accounting/Incomes.php&modfunc=remove','Y','Y'
		FROM staff_exceptions
		WHERE modname='Accounting/Incomes.php'
		AND can_edit='Y'
		AND user_id NOT IN(SELECT user_id
			FROM staff_exceptions
			WHERE modname='Accounting/Incomes.php&modfunc=remove');";

	$insert_sql .= "INSERT INTO staff_exceptions (user_id, modname, can_use, can_edit)
		SELECT user_id,'Accounting/Salaries.php&modfunc=remove','Y','Y'
		FROM staff_exceptions
		WHERE modname='Accounting/Salaries.php'
		AND can_edit='Y'
		AND user_id NOT IN(SELECT user_id
			FROM staff_exceptions
			WHERE modname='Accounting/Salaries.php&modfunc=remove');";

	$insert_sql .= "INSERT INTO staff_exceptions (user_id, modname, can_use, can_edit)
		SELECT user_id,'Accounting/StaffPayments.php&modfunc=remove','Y','Y'
		FROM staff_exceptions
		WHERE modname='Accounting/StaffPayments.php'
		AND can_edit='Y'
		AND user_id NOT IN(SELECT user_id
			FROM staff_exceptions
			WHERE modname='Accounting/StaffPayments.php&modfunc=remove');";

	DBQuery( $insert_sql );

	return $return;
}


/**
 * Update to version 12.2
 *
 * 0. Drop calc_gpa_mp procedure (MySQL only)
 * 0. Create plpgsql language in case it does not exist (PostgreSQL only)
 * 1. SQL N/A grade (empty GPA value) does not affect GPA
 *
 * Local function
 *
 * @since 12.2
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update122()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	if ( $DatabaseType === 'mysql' )
	{
		/**
		 * MySQL
		 *
		 * 0. Drop calc_gpa_mp procedure
		 * 1. SQL set min Credits to 0 & fix division by zero error
		 */
		$mysql_no_delimiter = MySQLRemoveDelimiter( "DROP PROCEDURE IF EXISTS calc_gpa_mp;

		--
		-- Name: calc_gpa_mp(s_id integer, mp_id integer); Type: FUNCTION;
		--
		-- @link https://stackoverflow.com/questions/9845171/run-a-query-in-a-mysql-stored-procedure-if-a-condition-is-true
		-- @since 12.2 SQL N/A grade (empty GPA value) does not affect GPA
		--

		DELIMITER $$
		CREATE PROCEDURE calc_gpa_mp(s_id integer, mp_id integer)
		BEGIN
			DECLARE oldrec integer;

			SELECT count(*) INTO oldrec FROM student_mp_stats WHERE student_id = s_id and marking_period_id = mp_id;

			IF oldrec > 0 THEN
			UPDATE student_mp_stats sms
			JOIN (
				select
				student_id,
				marking_period_id,
				sum(weighted_gp*credit_attempted/gp_scale) as sum_weighted_factors,
				sum(unweighted_gp*credit_attempted/gp_scale) as sum_unweighted_factors,
				sum(credit_attempted) as gp_credits,
				sum( case when class_rank = 'Y' THEN weighted_gp*credit_attempted/gp_scale END ) as cr_weighted,
				sum( case when class_rank = 'Y' THEN unweighted_gp*credit_attempted/gp_scale END ) as cr_unweighted,
				sum( case when class_rank = 'Y' THEN credit_attempted END) as cr_credits
				from student_report_card_grades
				where student_id = s_id
				and marking_period_id = mp_id
				and not gp_scale = 0
				and weighted_gp is not null
				group by student_id, marking_period_id
			) as rcg
			ON rcg.student_id = sms.student_id and rcg.marking_period_id = sms.marking_period_id
			SET
				sms.sum_weighted_factors = rcg.sum_weighted_factors,
				sms.sum_unweighted_factors = rcg.sum_unweighted_factors,
				sms.cr_weighted_factors = rcg.cr_weighted,
				sms.cr_unweighted_factors = rcg.cr_unweighted,
				sms.gp_credits = rcg.gp_credits,
				sms.cr_credits = rcg.cr_credits;

			ELSE
			INSERT INTO student_mp_stats (student_id, marking_period_id, sum_weighted_factors, sum_unweighted_factors, grade_level_short, cr_weighted_factors, cr_unweighted_factors, gp_credits, cr_credits)
				select
					srcg.student_id,
					srcg.marking_period_id,
					sum(weighted_gp*credit_attempted/gp_scale) as sum_weighted_factors,
					sum(unweighted_gp*credit_attempted/gp_scale) as sum_unweighted_factors,
					(select eg.short_name
						from enroll_grade eg, marking_periods mp
						where eg.student_id = s_id
						and eg.syear = mp.syear
						and eg.school_id = mp.school_id
						and eg.start_date <= mp.end_date
						and mp.marking_period_id = mp_id
						order by eg.start_date desc
						limit 1) as short_name,
					sum( case when class_rank = 'Y' THEN weighted_gp*credit_attempted/gp_scale END ) as cr_weighted,
					sum( case when class_rank = 'Y' THEN unweighted_gp*credit_attempted/gp_scale END ) as cr_unweighted,
					sum(credit_attempted) as gp_credits,
					sum(case when class_rank = 'Y' THEN credit_attempted END) as cr_credits
				from student_report_card_grades srcg
				where srcg.student_id = s_id
				and srcg.marking_period_id = mp_id
				and not srcg.gp_scale = 0
				and weighted_gp is not null
				group by srcg.student_id, srcg.marking_period_id, short_name;
			END IF;
		END$$
		DELIMITER ;" );

		DBQuery( $mysql_no_delimiter );

		return $return;
	}

	/**
	 * PostgreSQL
	 *
	 * 0. Create plpgsql language in case it does not exist
	 * 1. SQL set min Credits to 0 & fix division by zero error
	 */
	DBQuery( "CREATE OR REPLACE FUNCTION calc_gpa_mp(s_id integer, mp_id integer) RETURNS integer AS $$
	DECLARE
		oldrec student_mp_stats%ROWTYPE;
	BEGIN
	  SELECT * INTO oldrec FROM student_mp_stats WHERE student_id = s_id and marking_period_id = mp_id;

	  IF FOUND THEN
		UPDATE student_mp_stats SET
			sum_weighted_factors = rcg.sum_weighted_factors,
			sum_unweighted_factors = rcg.sum_unweighted_factors,
			cr_weighted_factors = rcg.cr_weighted,
			cr_unweighted_factors = rcg.cr_unweighted,
			gp_credits = rcg.gp_credits,
			cr_credits = rcg.cr_credits
		FROM (
		select
			sum(weighted_gp*credit_attempted/gp_scale) as sum_weighted_factors,
			sum(unweighted_gp*credit_attempted/gp_scale) as sum_unweighted_factors,
			sum(credit_attempted) as gp_credits,
			sum( case when class_rank = 'Y' THEN weighted_gp*credit_attempted/gp_scale END ) as cr_weighted,
			sum( case when class_rank = 'Y' THEN unweighted_gp*credit_attempted/gp_scale END ) as cr_unweighted,
			sum( case when class_rank = 'Y' THEN credit_attempted END) as cr_credits
		from student_report_card_grades where student_id = s_id
			and marking_period_id = mp_id
			and not gp_scale = 0
			and weighted_gp is not null
			group by student_id, marking_period_id
		) as rcg
		WHERE student_id = s_id and marking_period_id = mp_id;
		RETURN 1;
	  ELSE
		INSERT INTO student_mp_stats (student_id, marking_period_id, sum_weighted_factors, sum_unweighted_factors, grade_level_short, cr_weighted_factors, cr_unweighted_factors, gp_credits, cr_credits)
			select
				srcg.student_id,
				srcg.marking_period_id,
				sum(weighted_gp*credit_attempted/gp_scale) as sum_weighted_factors,
				sum(unweighted_gp*credit_attempted/gp_scale) as sum_unweighted_factors,
				(select eg.short_name
					from enroll_grade eg, marking_periods mp
					where eg.student_id = s_id
					and eg.syear = mp.syear
					and eg.school_id = mp.school_id
					and eg.start_date <= mp.end_date
					and mp.marking_period_id = mp_id
					order by eg.start_date desc
					limit 1) as short_name,
				sum( case when class_rank = 'Y' THEN weighted_gp*credit_attempted/gp_scale END ) as cr_weighted,
				sum( case when class_rank = 'Y' THEN unweighted_gp*credit_attempted/gp_scale END ) as cr_unweighted,
				sum(credit_attempted) as gp_credits,
				sum(case when class_rank = 'Y' THEN credit_attempted END) as cr_credits
			from student_report_card_grades srcg
			where srcg.student_id = s_id
			and srcg.marking_period_id = mp_id
			and not srcg.gp_scale = 0
			and weighted_gp is not null
			group by srcg.student_id, srcg.marking_period_id, short_name;
	  END IF;
	  RETURN 0;
	END;
	$$ LANGUAGE plpgsql;" );

	return $return;
}


/**
 * Update to version 12.3
 *
 * 1. courses & course_subjects tables:
 * Multilingual course title: SQL change title column type from varchar(100) to text
 * Needs to DROP course_details view first to then recreate it.
 *
 * Local function
 *
 * @since 12.3
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update123()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. courses & course_subjects tables:
	 * Multilingual course title: SQL change title column type from varchar(100) to text
	 * Needs to DROP course_details view first to then recreate it.
	 */
	$sql_drop_view = "DROP VIEW course_details;";

	$sql_create_view = "CREATE VIEW course_details AS
		SELECT cp.school_id, cp.syear, cp.marking_period_id, c.subject_id, cp.course_id, cp.course_period_id, cp.teacher_id, c.title AS course_title, cp.title AS cp_title, cp.grade_scale_id, cp.mp, cp.credits FROM course_periods cp, courses c WHERE (cp.course_id = c.course_id);";

	if ( $DatabaseType === 'mysql' )
	{
		$sql_alter_table = "ALTER TABLE courses MODIFY TITLE text;
			ALTER TABLE course_subjects MODIFY TITLE text;";
	}
	else
	{
		// PostgreSQL.
		$sql_alter_table = "ALTER TABLE courses ALTER TITLE TYPE text;
			ALTER TABLE course_subjects ALTER TITLE TYPE text;";
	}

	DBQuery( $sql_drop_view . $sql_alter_table . $sql_create_view );

	return $return;
}


/**
 * Update to version 12.6
 *
 * 1. Add Content Security Policy header to config table
 * 2. Create the csp_reports table
 * 3. Activate the Content Security Policy plugin
 * 4. Add the CREATE_STUDENT_ACCOUNT_DEFAULT_SCHOOL_FORCE option to config table
 *
 * Local function
 *
 * @since 12.6
 *
 * @return boolean false if update failed or if not called by Update(), else true
 */
function _update126()
{
	global $DatabaseType;

	_isCallerUpdate( debug_backtrace() );

	$return = true;

	/**
	 * 1. Add Content Security Policy header to config table
	 */
	$sql_csp_header = "INSERT INTO config (SCHOOL_ID, TITLE, CONFIG_VALUE)
		VALUES (0, 'CONTENT_SECURITY_POLICY', 'script-src ''self'' ''unsafe-eval'' ''report-sample''; style-src ''self'' ''unsafe-inline''; connect-src ''self''; form-action ''self''; base-uri ''self''; frame-ancestors ''none''; object-src ''none''; report-uri plugins/Content_Security_Policy/SaveReport.php');";

	DBQuery( $sql_csp_header );

	/**
	 * 2. Create the csp_reports table
	 */
	if ( $DatabaseType === 'mysql' )
	{
		$sql_csp_reports_table = "CREATE TABLE IF NOT EXISTS csp_reports (
			id integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
			full_report text NOT NULL,
			violated_directive text NOT NULL,
			blocked_uri text NOT NULL,
			script_sample text,
			created_at timestamp DEFAULT current_timestamp
		);";
	}
	else
	{
		// PostgreSQL.
		$sql_csp_reports_table = "CREATE TABLE IF NOT EXISTS csp_reports (
			id serial PRIMARY KEY,
			full_report text NOT NULL,
			violated_directive text NOT NULL,
			blocked_uri text NOT NULL,
			script_sample text,
			created_at timestamp DEFAULT current_timestamp
		);";
	}

	DBQuery( $sql_csp_reports_table );

	/**
	 * 3. Activate the Content Security Policy plugin
	 */
	$RosarioPlugins = unserialize( Config( 'PLUGINS' ) );

	$RosarioPlugins['Content_Security_Policy'] = true;

	Config( 'PLUGINS', serialize( $RosarioPlugins ) );

	/**
	 * 4. Add the CREATE_STUDENT_ACCOUNT_DEFAULT_SCHOOL_FORCE option to config table
	 */
	DBQuery( "INSERT INTO config (SCHOOL_ID, TITLE, CONFIG_VALUE)
		VALUES (0, 'CREATE_STUDENT_ACCOUNT_DEFAULT_SCHOOL_FORCE', NULL);" );

	return $return;
}
