IP : 18.218.50.150Hostname : host45.registrar-servers.comKernel : Linux host45.registrar-servers.com 4.18.0-513.18.1.lve.2.el8.x86_64 #1 SMP Sat Mar 30 15:36:11 UTC 2024 x86_64Disable Function : None :) OS : Linux
PATH:
/
home/
../
var/
softaculous/
yii/
../
genix/
../
moodle39/
../
gedview/
../
rosariosis/
rosariosis.sql/
/
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;
CREATE TABLE `templates` ( `modname` varchar(150) COLLATE utf8mb4_unicode_520_ci NOT NULL, `staff_id` int(11) NOT NULL, `template` longtext COLLATE utf8mb4_unicode_520_ci, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`modname`,`staff_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
-- -- Dumping data for table `templates` --
INSERT INTO `templates` VALUES ('Custom/CreateParents.php', 0, 'Dear __PARENT_NAME__,\r\n\r\nA parent account for the __SCHOOL_ID__ has been created to access school information and student information for the following students:\r\n__ASSOCIATED_STUDENTS__\r\n\r\nYour account credentials are:\r\nUsername: __USERNAME__\r\nPassword: __PASSWORD__\r\n\r\nA link to the SIS website and instructions for access are available on the school''s website__BLOCK2__Dear __PARENT_NAME__,\r\n\r\nThe following students have been added to your parent account on the SIS:\r\n__ASSOCIATED_STUDENTS__', '[[regtime]]', NULL), ('Custom/NotifyParents.php', 0, 'Dear __PARENT_NAME__,\r\n\r\nA parent account for the __SCHOOL_ID__ has been created to access school information and student information for the following students:\r\n__ASSOCIATED_STUDENTS__\r\n\r\nYour account credentials are:\r\nUsername: __USERNAME__\r\nPassword: __PASSWORD__\r\n\r\nA link to the SIS website and instructions for access are available on the school''s website', '[[regtime]]', NULL), ('Grades/HonorRoll.php', 0, '<br /><br /><br />\r\n<div style="text-align: center;"><span style="font-size: xx-large;"><strong>__SCHOOL_ID__</strong><br /></span><br /><span style="font-size: xx-large;">We hereby recognize<br /><br /></span></div>\r\n<div style="text-align: center;"><span style="font-size: xx-large;"><strong>__FIRST_NAME__ __LAST_NAME__</strong><br /><br /></span></div>\r\n<div style="text-align: center;"><span style="font-size: xx-large;">Who has completed all the academic requirements for <br />Honor Roll</span></div>', '[[regtime]]', NULL), ('Grades/Transcripts.php', 0, '<h2 style="text-align: center;">Studies Certificate</h2>\r\n<p>The Principal here undersigned certifies:</p>\r\n<p>That __FIRST_NAME__ __LAST_NAME__ attended at this school the following courses corresponding to grade __GRADE_ID__ in year __YEAR__ with the following grades and credit hours.</p>\r\n<p>__BLOCK2__</p>\r\n<p> </p>\r\n<table style="border-collapse: collapse; width: 100%;" border="0" cellpadding="10"><tbody><tr>\r\n<td style="width: 50%; text-align: center;"><hr />\r\n<p>Signature</p>\r\n<p> </p><hr />\r\n<p>Title</p></td>\r\n<td style="width: 50%; text-align: center;"><hr />\r\n<p>Signature</p>\r\n<p> </p><hr />\r\n<p>Title</p></td></tr></tbody></table>', '[[regtime]]', NULL), ('Students/Letters.php', 0, '<p></p>', '[[regtime]]', NULL);
-- -- Structure for view `course_details` -- DROP TABLE IF EXISTS `course_details`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `course_details` AS select `cp`.`school_id` AS `school_id`,`cp`.`syear` AS `syear`,`cp`.`marking_period_id` AS `marking_period_id`,`c`.`subject_id` AS `subject_id`,`cp`.`course_id` AS `course_id`,`cp`.`course_period_id` AS `course_period_id`,`cp`.`teacher_id` AS `teacher_id`,`c`.`title` AS `course_title`,`cp`.`title` AS `cp_title`,`cp`.`grade_scale_id` AS `grade_scale_id`,`cp`.`mp` AS `mp`,`cp`.`credits` AS `credits` from (`course_periods` `cp` join `courses` `c`) where (`cp`.`course_id` = `c`.`course_id`);
-- -- Structure for view `enroll_grade` -- DROP TABLE IF EXISTS `enroll_grade`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `enroll_grade` AS select `e`.`id` AS `id`,`e`.`syear` AS `syear`,`e`.`school_id` AS `school_id`,`e`.`student_id` AS `student_id`,`e`.`start_date` AS `start_date`,`e`.`end_date` AS `end_date`,`sg`.`short_name` AS `short_name`,`sg`.`title` AS `title` from (`student_enrollment` `e` join `school_gradelevels` `sg`) where (`e`.`grade_id` = `sg`.`id`);
-- -- Structure for view `marking_periods` -- DROP TABLE IF EXISTS `marking_periods`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `marking_periods` AS select `school_marking_periods`.`marking_period_id` AS `marking_period_id`,'Rosario' AS `mp_source`,`school_marking_periods`.`syear` AS `syear`,`school_marking_periods`.`school_id` AS `school_id`,(case when (`school_marking_periods`.`mp` = 'FY') then 'year' when (`school_marking_periods`.`mp` = 'SEM') then 'semester' when (`school_marking_periods`.`mp` = 'QTR') then 'quarter' else NULL end) AS `mp_type`,`school_marking_periods`.`title` AS `title`,`school_marking_periods`.`short_name` AS `short_name`,`school_marking_periods`.`sort_order` AS `sort_order`,(case when (`school_marking_periods`.`parent_id` > 0) then `school_marking_periods`.`parent_id` else -(1) end) AS `parent_id`,(case when ((select `smp`.`parent_id` from `school_marking_periods` `smp` where (`smp`.`marking_period_id` = `school_marking_periods`.`parent_id`)) > 0) then (select `smp`.`parent_id` from `school_marking_periods` `smp` where (`smp`.`marking_period_id` = `school_marking_periods`.`parent_id`)) else -(1) end) AS `grandparent_id`,`school_marking_periods`.`start_date` AS `start_date`,`school_marking_periods`.`end_date` AS `end_date`,`school_marking_periods`.`post_start_date` AS `post_start_date`,`school_marking_periods`.`post_end_date` AS `post_end_date`,`school_marking_periods`.`does_grades` AS `does_grades`,`school_marking_periods`.`does_comments` AS `does_comments` from `school_marking_periods` union select `history_marking_periods`.`marking_period_id` AS `marking_period_id`,'History' AS `mp_source`,`history_marking_periods`.`syear` AS `syear`,`history_marking_periods`.`school_id` AS `school_id`,`history_marking_periods`.`mp_type` AS `mp_type`,`history_marking_periods`.`name` AS `title`,`history_marking_periods`.`short_name` AS `short_name`,NULL AS `sort_order`,`history_marking_periods`.`parent_id` AS `parent_id`,-(1) AS `grandparent_id`,NULL AS `start_date`,`history_marking_periods`.`post_end_date` AS `end_date`,NULL AS `post_start_date`,`history_marking_periods`.`post_end_date` AS `post_end_date`,'Y' AS `does_grades`,NULL AS `does_comments` from `history_marking_periods`;
-- -- Structure for view `transcript_grades` -- DROP TABLE IF EXISTS `transcript_grades`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `transcript_grades` AS select `mp`.`syear` AS `syear`,`mp`.`school_id` AS `school_id`,`mp`.`marking_period_id` AS `marking_period_id`,`mp`.`mp_type` AS `mp_type`,`mp`.`short_name` AS `short_name`,`mp`.`parent_id` AS `parent_id`,`mp`.`grandparent_id` AS `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` AS `end_date`,`sms`.`student_id` AS `student_id`,(`sms`.`cum_weighted_factor` * coalesce(`schools`.`reporting_gp_scale`,(select `schools`.`reporting_gp_scale` from `schools` where (`mp`.`school_id` = `schools`.`id`) order by `schools`.`syear` limit 1))) AS `cum_weighted_gpa`,(`sms`.`cum_unweighted_factor` * `schools`.`reporting_gp_scale`) AS `cum_unweighted_gpa`,`sms`.`cum_rank` AS `cum_rank`,`sms`.`mp_rank` AS `mp_rank`,`sms`.`class_size` AS `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` AS `grade_level_short`,`srcg`.`comment` AS `comment`,`srcg`.`grade_percent` AS `grade_percent`,`srcg`.`grade_letter` AS `grade_letter`,`srcg`.`weighted_gp` AS `weighted_gp`,`srcg`.`unweighted_gp` AS `unweighted_gp`,`srcg`.`gp_scale` AS `gp_scale`,`srcg`.`credit_attempted` AS `credit_attempted`,`srcg`.`credit_earned` AS `credit_earned`,`srcg`.`course_title` AS `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` AS `class_rank`,`sms`.`comments` AS `comments`,`srcg`.`credit_hours` AS `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 join `schools` on(((`mp`.`school_id` = `schools`.`id`) and (`mp`.`syear` = `schools`.`syear`)))) order by `srcg`.`course_period_id`;
-- -- Constraints for table `student_report_card_grades` -- ALTER TABLE `student_report_card_grades` ADD CONSTRAINT `student_report_card_grades_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`), ADD CONSTRAINT `student_report_card_grades_ibfk_2` FOREIGN KEY (`course_period_id`) REFERENCES `course_periods` (`course_period_id`); DELIMITER $$ -- -- Procedures -- 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$$
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$$
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 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;
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 group by srcg.student_id, srcg.marking_period_id, short_name; END IF; END$$
select marking_period_id,mp,credits into course_detail_mp_id,course_detail_mp,course_detail_credits from course_periods where course_period_id = cp_id; select marking_period_id,mp_type into mp_detail_mp_id,mp_detail_mp_type from marking_periods where marking_period_id = mp_id;
IF course_detail_mp_id = mp_detail_mp_id THEN RETURN course_detail_credits; ELSEIF course_detail_mp = 'FY' AND mp_detail_mp_type = 'semester' THEN select count(*) into val_mp_count from marking_periods where parent_id = course_detail_mp_id group by parent_id; ELSEIF course_detail_mp = 'FY' and mp_detail_mp_type = 'quarter' THEN select count(*) into val_mp_count from marking_periods where grandparent_id = course_detail_mp_id group by grandparent_id; ELSEIF course_detail_mp = 'SEM' and mp_detail_mp_type = 'quarter' THEN select count(*) into val_mp_count from marking_periods where parent_id = course_detail_mp_id group by parent_id; ELSE RETURN course_detail_credits; END IF;
IF val_mp_count > 0 THEN RETURN course_detail_credits/val_mp_count; ELSE RETURN course_detail_credits; END IF; END$$
CREATE FUNCTION `set_class_rank_mp`(mp_id integer) RETURNS int(11) 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 ;
-- -- Triggers `student_report_card_grades` -- DROP TRIGGER IF EXISTS `srcg_mp_stats_delete`; DELIMITER $$ CREATE TRIGGER `srcg_mp_stats_delete` AFTER DELETE ON `student_report_card_grades` FOR EACH ROW CALL t_update_mp_stats(OLD.student_id, OLD.marking_period_id) $$ DELIMITER ; DROP TRIGGER IF EXISTS `srcg_mp_stats_insert`; DELIMITER $$ CREATE TRIGGER `srcg_mp_stats_insert` AFTER INSERT ON `student_report_card_grades` FOR EACH ROW CALL t_update_mp_stats(NEW.student_id, NEW.marking_period_id) $$ DELIMITER ; DROP TRIGGER IF EXISTS `srcg_mp_stats_update`; DELIMITER $$ CREATE TRIGGER `srcg_mp_stats_update` AFTER UPDATE ON `student_report_card_grades` FOR EACH ROW CALL t_update_mp_stats(NEW.student_id, NEW.marking_period_id) $$ DELIMITER ;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;