-
Notifications
You must be signed in to change notification settings - Fork 206
/
Copy pathRecalCulateProcess.php
100 lines (91 loc) · 9.42 KB
/
RecalCulateProcess.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
<?php
#**************************************************************************
# openSIS is a free student information system for public and non-public
# schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
# openSIS is web-based, open source, and comes packed with features that
# include student demographic info, scheduling, grade book, attendance,
# report cards, eligibility, transcripts, parent portal,
# student portal and more.
#
# Visit the openSIS web site at http://www.opensis.com to learn more.
# If you have question regarding this system or the license, please send
# an email to [email protected].
#
# This program is released under the terms of the GNU General Public License as
# published by the Free Software Foundation, version 2 of the License.
# See license.txt.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
#
#***************************************************************************************
error_reporting(0);
include('RedirectRootInc.php');
include 'Warehouse.php';
include 'Data.php';
DBQuery('Create Table TEMP_SRCG AS SELECT * FROM student_report_card_grades WHERE SYEAR=\'' . UserSyear() . '\' AND COURSE_PERIOD_ID IS NOT NULL AND STUDENT_ID IN (\'' . $_REQUEST['students'] . '\') AND MARKING_PERIOD_ID=\'' . $_REQUEST['mp'] . '\'');
DBQuery('DELETE FROM student_report_card_grades WHERE SYEAR=\'' . UserSyear() . '\' AND COURSE_PERIOD_ID IS NOT NULL AND STUDENT_ID IN (\'' . $_REQUEST['students'] . '\') AND MARKING_PERIOD_ID=\'' . $_REQUEST['mp'] . '\'');
DBQuery('INSERT INTO `student_report_card_grades`(`syear`, `school_id`, `student_id`, `course_period_id`, `report_card_grade_id`, `report_card_comment_id`, `comment`, `grade_percent`, `marking_period_id`, `grade_letter`, `weighted_gp`, `unweighted_gp`, `gp_scale`,`gpa_cal`, `credit_attempted`, `credit_earned`, `credit_category`,`course_code`, `course_title`, `id`) SELECT `syear`, `school_id`, `student_id`, `course_period_id`, `report_card_grade_id`, `report_card_comment_id`, `comment`, `grade_percent`, `marking_period_id`, `grade_letter`, `weighted_gp`, `unweighted_gp`, `gp_scale`,`gpa_cal`, `credit_attempted`, `credit_earned`, `credit_category`, `course_code`,`course_title`, `id` FROM TEMP_SRCG ');
DBQuery('DROP TABLE TEMP_SRCG');
$students_RET = DBGet(DBQuery('SELECT STUDENT_ID,MARKING_PERIOD_ID FROM student_report_card_grades WHERE SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\' AND COURSE_PERIOD_ID IS NULL GROUP BY MARKING_PERIOD_ID'));
foreach ($students_RET as $stu_key => $stu_val) {
$res = DBGet(DBQuery('SELECT
SUM(srcg.weighted_gp/s.reporting_gp_scale) AS sum_weighted_factors, COUNT(*) AS count_weighted_factors,
SUM(srcg.unweighted_gp/srcg.gp_scale) AS sum_unweighted_factors,
COUNT(*) AS count_unweighted_factors,
IF(ISNULL( sum(srcg.unweighted_gp) ), (select SUM(sg.weighted_gp*sg.credit_earned) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\')/ (select sum(sg.credit_attempted) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\'),
IF(ISNULL( sum(srcg.weighted_gp) ), (select SUM(sg.unweighted_gp*sg.credit_earned) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\')/(select sum(sg.credit_attempted) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\'),
( (select SUM(sg.unweighted_gp*sg.credit_attempted) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\')+ (select SUM(sg.weighted_gp*sg.credit_earned) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\'))/(select sum(sg.credit_attempted) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\')
)
) AS gpa,
(select SUM(sg.weighted_gp*sg.credit_earned) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\')/(select sum(sg.credit_attempted) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\'
AND sg.gpa_cal=\'Y\' AND sg.weighted_gp IS NOT NULL AND sg.unweighted_gp IS NULL GROUP BY sg.student_id, sg.marking_period_id) AS weighted_gpa,
(select SUM(sg.unweighted_gp*sg.credit_earned) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND sg.gpa_cal=\'Y\')/ (select sum(sg.credit_attempted) from student_report_card_grades sg where sg.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND sg.student_id=\'' . $stu_val['STUDENT_ID'] . '\'
AND sg.gpa_cal=\'Y\' AND sg.unweighted_gp IS NOT NULL AND sg.weighted_gp IS NULL GROUP BY sg.student_id, sg.marking_period_id) unweighted_gpa,
eg.short_name AS grade_level_short FROM student_report_card_grades srcg
INNER JOIN schools s ON s.id=srcg.school_id
LEFT JOIN enroll_grade eg on eg.student_id=srcg.student_id AND eg.syear=srcg.syear AND eg.school_id=srcg.school_id
WHERE srcg.student_id=\'' . $stu_val['STUDENT_ID'] . '\' AND srcg.gp_scale<>0 AND srcg.gpa_cal=\'Y\' AND srcg.course_period_id IS NULL AND srcg.marking_period_id NOT LIKE \'E%\'
GROUP BY srcg.marking_period_id,eg.short_name'));
$stu_stat = DBGet(DBQuery('SELECT COUNT(*) AS COUNT FROM student_gpa_calculated WHERE marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND student_id=\'' . $stu_val['STUDENT_ID'] . '\''));
if ($stu_stat[1]['COUNT'] == 0)
DBQuery('INSERT INTO student_gpa_calculated(student_id,marking_period_id)
VALUES(\'' . $stu_val['STUDENT_ID'] . '\',\'' . $stu_val['MARKING_PERIOD_ID'] . '\')');
DBQuery('UPDATE student_gpa_calculated g
INNER JOIN (
SELECT s.student_id,
SUM(s.weighted_gp/sc.reporting_gp_scale)/COUNT(*) AS cum_weighted_factor,
SUM(s.unweighted_gp/s.gp_scale)/COUNT(*) AS cum_unweighted_factor
FROM student_report_card_grades s
INNER JOIN schools sc ON sc.id=s.school_id
WHERE s.marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND s.course_period_id IS NULL AND s.gpa_cal=\'Y\' AND
s.student_id=\'' . $stu_val['STUDENT_ID'] . '\') gg ON gg.student_id=g.student_id
SET g.cum_unweighted_factor=gg.cum_unweighted_factor
WHERE g.student_id=\'' . $stu_val['STUDENT_ID'] . '\'');
$stu_gpa_cal = DBGet(DBQuery('SELECT COUNT(*) AS COUNT FROM student_gpa_calculated WHERE marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND student_id=\'' . $stu_val['STUDENT_ID'] . '\''));
if ($stu_gpa_cal[1]['COUNT'] != 0) {
DBQuery('UPDATE student_gpa_calculated SET gpa=\'' . $res[1]['GPA'] . '\', weighted_gpa=\'' . $res[1]['WEIGHTED_GPA'] . '\',unweighted_gpa=\'' . $res[1]['UNWEIGHTED_GPA'] . '\' WHERE marking_period_id=\'' . $stu_val['MARKING_PERIOD_ID'] . '\' AND student_id=\'' . $stu_val['STUDENT_ID'] . '\'');
} else
DBQuery('INSERT INTO student_gpa_calculated(student_id,marking_period_id,mp,gpa,weighted_gpa,unweighted_gpa,grade_level_short)
VALUES(\'' . $stu_val['STUDENT_ID'] . '\',\'' . $stu_val['MARKING_PERIOD_ID'] . '\',\'' . $stu_val['MARKING_PERIOD_ID'] . '\',\'' . $res[1]['GPA'] . '\',\'' . $res[1]['WEIGHTED_GPA'] . '\',
\'' . $res[1]['unweighted_gpa'] . '\',\'' . $res[1]['GRADE_LEVEL_SHORT'] . '\')');
}
$sql = "CREATE TEMPORARY table temp_cum_gpa AS
SELECT * FROM student_report_card_grades srcg WHERE credit_attempted=
(SELECT MAX(credit_attempted) FROM student_report_card_grades srcg1 WHERE srcg.course_period_id=srcg1.course_period_id and srcg.student_id=srcg1.student_id AND srcg1.course_period_id IS NOT NULL)
GROUP BY course_period_id,student_id,marking_period_id
UNION SELECT * FROM student_report_card_grades WHERE course_period_id IS NULL AND report_card_grade_id IS NULL;"; //);
$sql .= "DROP TABLE IF EXISTS tmp;";
$sql .= "SELECT CALC_CUM_GPA_MP('" . $_REQUEST['mp'] . "');";
$sql .= "DROP TABLE IF EXISTS tmp;";
$sql .= "SELECT SET_CLASS_RANK_MP('" . $_REQUEST['mp'] . "');";
if (mysqli_multi_query($connection, $sql)) {
echo '<br/><table><tr><td width="38"><img src="assets/icon_ok.png" /></td><td valign="middle"><span style="font-size:14px;">The grades for ' . GetMP($_REQUEST['mp']) . ' has been recalculated.</span></td></tr></table>';
}
unset($_REQUEST['modfunc']);