<?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( '11.8.1', 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();
}
// 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.
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;
}
// @deprecated since 11.4 Assignments Files upload path $AssignmentsFilesPath global var
// @deprecated since 11.4 Portal Notes Files upload path $PortalNotesFilesPath global var
// @deprecated since 11.4 Food Service Icons upload path $FS_IconsPath global var