<?php

DisplayHeader(".:".ProgramTitle().":.");

$_SYSTEM['DisableAddonLoader']=true;

$mps = get_all_school_term('PRO',get_user_school_term());
$mps = explode(',',str_replace("'",'',$mps));
$table = '<TABLE><TR  BGCOLOR=#'.Preferences('HIGHLIGHT_BACKGROUND').'></TR><TR><TD align=center><TABLE width=100%><TR>
		<TD width=50% align=center><fieldset style="border:1px solid gray;"><legend>'._('Calculate GPA/PA for').'</legend><TABLE><TR><TD height=90>';

foreach($mps as $mp)
{
	if($mp!='0')
		$table .= '<INPUT type=radio name=school_term_id id=mpid'.$mp.' value='.$mp.($mp==get_user_school_term()?' CHECKED':'').'><label for="mpid'.$mp.'">'.get_school_term($mp).'</label><BR>';
}

$table .= '</TD></TR></TABLE></fieldset></TD>';

if(SystemPreferences('WEIGHTED_GPA')=='Y')
{
	if(SystemPreferences('GPA_POINTS_OR_PERCENT_GRADE', 'system', true)!='PERCENT_GRADE')
		$table .= '<TD width=50% align=center><fieldset style="border:1px solid gray;"><legend>'._('Calculate Student Rating in Class List using').'</legend>
			<TABLE><TR><TD height=90>
				<INPUT type=radio name=rank id=rank_weighted_radio value=WEIGHTED_GPA CHECKED><label for="rank_weighted_radio">'._('Weighted GPA').'</label>
				<BR>
				<INPUT type=radio name=rank value=GPA id=rank_unweighted_radio><label for="rank_unweighted_radio">'._('Unweighted GPA').'</label>
			</TD></TR></TABLE></fieldset></TD></TR>';
	else
		$table .= '<TD width=50% align=center><fieldset style="border:1px solid gray;"><legend>'._('Calculate Student Rating in Class List using').'</legend><TABLE><TR><TD height=90>Student Rating in Class List will be calculated by Percent Average<INPUT type=hidden name=rank value=PA></TD></TR></TABLE></fieldset></TD></TR>';
}
else
	$table .= '<TR BGCOLOR=#'.Preferences('HIGHLIGHT_BACKGROUND').'><TD colspan=2><INPUT type=hidden name=rank value=GPA><INPUT type=hidden name=weight></TD></TR>';

$table .= '</TABLE><HR><font color=red>'._('Warning!').'</font> '._('GPA/PA calculation overwrites existing records.');
if(SystemPreferences('WEIGHTED_GPA')=='Y')
{
	if(SystemPreferences('GPA_POINTS_OR_PERCENT_GRADE')!='PERCENT_GRADE')
	        $table .= '<BR>'.sprintf(_('To calculate Weighted GPA the Program multiplies <font color=green><b>Weighted GPA points</b></font> by the <font color=green><b>Credits</b></font> assigned to a course (%s).'),ProgramLink('mod_grades/GrdRCProperties.php',ProgramTitle('mod_grades/GrdRCProperties.php')));
//	else
//		$table .= '<BR>'.sprintf(_('To calculate BLA BLA BLA the Program multiplyses <font color=green><b>BLA BLA BLA POINTS</b></font> by the <font color=green><b>Credits</b></font> assigned to a course (%s).'),ProgramLink('mod_grades/GrdRCProperties.php',ProgramTitle('mod_grades/GrdRCProperties.php')));
}
$table .= '</TD></TR></TABLE>';

$go = Prompt(_('GPA/PA Calculation'),_('Calculate GPA/PA and Student Rating in Class List'),$table);
if(!$_REQUEST['school_term_id'] && $go)
    DisplayHeader(ErrorMessage(array(_('There are no school terms added. Recalculation impossible')),'fatal'),' &nbsp;');
elseif($go)
{
//	$multiplier = "*g.CREDITS";
	$multiplier = "";
	$divide = "sum(g.CREDITS)";

	$max_term_array = db_request(db_execute("SELECT MAX(SORT_ORDER) as MAX_SORT_ORDER FROM ".get_school_term($_REQUEST['school_term_id'],'MP_TABLE')." WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"));
//echo get_school_term($_REQUEST['school_term_id'],'MP_TABLE')."<br>";
	db_execute("DELETE FROM STUDENT_GPA_TOTALS WHERE SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
	db_execute("INSERT INTO STUDENT_GPA_TOTALS (student_id,school_term_id,gpa,weighted_gpa,PERCENT_AVERAGE)
		SELECT g.STUDENT_ID,
		".db_to_number('g.school_term_id')." AS SCHOOL_TERM_ID,
		sum(g.GPA_POINTS".$multiplier.")/".$divide." AS GPA,
		sum(g.WEIGHTED_GPA_POINTS".$multiplier.")/".$divide." AS WEIGHTED_GPA,
		COALESCE(AVG(g.PERCENT_GRADE),0) AS PERCENT_AVERAGE
		FROM STUDENT_GRADES_CARD g
		WHERE g.CREDITS>0
		AND g.AFFECTS_GPA='Y'
		AND g.school_term_id='".$_REQUEST['school_term_id']."'
		GROUP BY g.STUDENT_ID,g.SCHOOL_TERM_ID
		HAVING sum(g.CREDITS)>0");
	// первое условие не будет работать для импортированныз оценок
	$mps_array = db_request(db_execute("SELECT SCHOOL_TERM_ID FROM ".get_school_term($_REQUEST['school_term_id'],'MP_TABLE')." WHERE (SCHOOL_YEAR < ".get_school_term($_REQUEST['school_term_id'],'SCHOOL_YEAR')." OR (SCHOOL_YEAR=".get_school_term($_REQUEST['school_term_id'],'SCHOOL_YEAR')." AND SORT_ORDER <= ".get_school_term($_REQUEST['school_term_id'],'SORT_ORDER').")) AND SCHOOL_ID='".get_school_id()."'"));
		
	$mps = '';
	foreach($mps_array as $mp)
		$mps .= ",'".$mp['SCHOOL_TERM_ID']."'";
	$mps = '('.substr($mps,1).')';
//	$mps = "('34')";
//	echo "-->".$_REQUEST['rank'];
//echo $mps;
//	if(SystemPreferences('GPA_POINTS_OR_PERCENT_GRADE')=='PERCENT_GRADE')
//	{
//		$gpa_value = 'AVG(g.PERCENT_GRADE)';
//	}else{
//		$gpa_value = 'sum(g.GPA_POINTS)/sum(g.CREDITS)';
//	}
	//SQL query for calculating class rating
	$sql = "SELECT ssm.STUDENT_ID,
	ssm.CLASSLEVEL_ID,
	ssm.INCLUDE_IN_CLASS_RANK,
	COALESCE(AVG(g.PERCENT_GRADE),0) AS PA,
	COALESCE(sum(g.GPA_POINTS".$multiplier.")/".$divide.",0) AS GPA,
	COALESCE(sum(g.WEIGHTED_GPA_POINTS".$multiplier.")/".$divide.",0) AS WEIGHTED_GPA,
		sum(".db_case(array("g.CARRIES_CREDITS","'Y'",'g.CREDITS',"'0'")).") AS CREDITS,
		sum(CASE WHEN g.GPA_POINTS>=0 THEN g.CREDITS ELSE '0' END) as CREDITS_ATTEMPTED
	FROM STUDENT_REGISTRATION ssm LEFT OUTER JOIN STUDENT_GRADES_CARD g
		ON (g.STUDENT_ID=ssm.STUDENT_ID
			AND ssm.SCHOOL_ID='".get_school_id()."'
			AND g.SCHOOL_ID='".get_school_id()."'
			AND ssm.SCHOOL_YEAR='".get_school_year()."'
			AND (('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE
				OR ssm.END_DATE IS NULL) AND '".get_db_date()."'>=ssm.START_DATE)
			AND g.CREDITS>0
			AND g.AFFECTS_GPA='Y'
			AND g.SCHOOL_TERM_ID IN ".$mps.")
	GROUP BY ssm.STUDENT_ID,ssm.CLASSLEVEL_ID,ssm.INCLUDE_IN_CLASS_RANK
	HAVING sum(g.CREDITS)>0
	ORDER BY $_REQUEST[rank] DESC,sum(g.CREDITS) DESC";
	$gpa_array = db_request(db_execute($sql),array(),array('CLASSLEVEL_ID'));
//	echo $sql;
	
//var_dump($gpa_array);

	if(count($gpa_array))
	{
		foreach($gpa_array as $grade_id=>$grade)
		{
			$i = 1;
			$prev_gpa = false;
			$grade_total = 0;

			if($max_term_array[1]['MAX_SORT_ORDER'] == get_school_term($_REQUEST['school_term_id'],'SORT_ORDER'))
				$include_grade_id = true;
//echo "vvv";
			foreach($grade as $student)
			{
				if($student['INCLUDE_IN_CLASS_RANK']=='Y')
					$grade_total++;
			}
			foreach($grade as $student)
			{
//			echo $student[$_REQUEST['rank']]."<br>";
//				echo $student[$_REQUEST['rank']].' - '.$prev_gpa.'<br>';
				if($prev_gpa!==$student[$_REQUEST['rank']])
				{

					$rank = $i;
				}
//echo "$rank <br>";					
				if($student['INCLUDE_IN_CLASS_RANK']=='Y')
				{
//					if($grade_id == '49')
					{
//						echo $student['STUDENT_ID'].' - '.$student['GPA'].' - '.$student['PA'].'<br>';
					
						//echo "UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank',PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'<br>";
					}
					if($prev_gpa!==$student[$_REQUEST['rank']])
						$i++;
					
					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".
						    $student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS'].
						    "',CREDITS_EARNED='".$student['CREDITS']."', CREDITS_ATTEMPTED='".$student['CREDITS_ATTEMPTED'].
						    "' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");					
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank',PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank',PERCENT_AVERAGE='".$student['PA']."', CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
					$prev_gpa = $student[$_REQUEST['rank']];
             
//echo "$i <br>";
//						$i++;
				}
				else
				{
					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA'].
						"',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS'].
						"',CREDITS_EARNED='".$student['CREDITS']."', CREDITS_ATTEMPTED='".$student['CREDITS_ATTEMPTED'].
						"' WHERE STUDENT_ID='".
						$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");	
             
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='', PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='', PERCENT_AVERAGE='".$student['PA']."', CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
				}
			}
		}
	}

//	ssm.CLASSLEVEL_ID,
//	ssm.INCLUDE_IN_CLASS_RANK,
	$sql = "SELECT ssm.STUDENT_ID,
	ssm.GROUP_ID,
	ssm.INCLUDE_IN_GROUP_RANK,
	COALESCE(AVG(g.PERCENT_GRADE),0) AS PA,
	COALESCE(sum(g.GPA_POINTS".$multiplier.")/".$divide.",0) AS GPA,
	COALESCE(sum(g.WEIGHTED_GPA_POINTS".$multiplier.")/".$divide.",0) AS WEIGHTED_GPA,
		sum(".db_case(array("g.CARRIES_CREDITS","'Y'",'g.CREDITS',"'0'")).") AS CREDITS,
		sum(CASE WHEN g.GPA_POINTS>=0 THEN g.CREDITS ELSE '0' END) as CREDITS_ATTEMPTED
	FROM STUDENT_REGISTRATION ssm LEFT OUTER JOIN STUDENT_GRADES_CARD g
		ON (g.STUDENT_ID=ssm.STUDENT_ID
			AND ssm.SCHOOL_ID='".get_school_id()."'
			AND g.SCHOOL_ID='".get_school_id()."'
			AND ssm.SCHOOL_YEAR='".get_school_year()."'
			AND (('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE
				OR ssm.END_DATE IS NULL) AND '".get_db_date()."'>=ssm.START_DATE)
			AND g.CREDITS>0
			AND g.AFFECTS_GPA='Y'
			AND g.SCHOOL_TERM_ID IN ".$mps.")
	GROUP BY ssm.STUDENT_ID,ssm.GROUP_ID,ssm.INCLUDE_IN_GROUP_RANK
	HAVING sum(g.CREDITS)>0
	ORDER BY $_REQUEST[rank] DESC,sum(g.CREDITS) DESC";
	$gpa_cgr_array = db_request(db_execute($sql),array(),array('GROUP_ID'));

//var_dump($gpa_cgr_array);
	if(count($gpa_cgr_array))
	{
		foreach($gpa_cgr_array as $group_id=>$group)
		{
			$g = 1;
			$prev_gr_gpa = false;
			$group_total = 0;

			if($max_term_array[1]['MAX_SORT_ORDER'] == get_school_term($_REQUEST['school_term_id'],'SORT_ORDER'))
				$include_group_id = true;
//echo "vvv";
			foreach($group as $student)
			{
				if($student['INCLUDE_IN_GROUP_RANK']=='Y')
					$group_total++;
			}
			foreach($group as $student)
			{
//			echo $student[$_REQUEST['rank']]."<br>";
//				echo $student[$_REQUEST['rank']].' - '.$prev_gr_gpa.'<br>';
				if($prev_gr_gpa!==$student[$_REQUEST['rank']])
				{

					$grank = $g;
				}
//echo "$grank <br>";					
				if($student['INCLUDE_IN_GROUP_RANK']=='Y')
				{
//					if($grade_id == '49')
					{
//						echo $student['STUDENT_ID'].' - '.$student['GPA'].' - '.$student['PA'].'<br>';
					
						//echo "UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank',PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'<br>";
					}
					if($prev_gr_gpa!==$student[$_REQUEST['rank']])
						$g++;
					
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".
//						    $student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS'].
//						    "',CREDITS_EARNED='".$student['CREDITS']."', CREDITS_ATTEMPTED='".$student['CREDITS_ATTEMPTED'].
//						    "' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");					

					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASSGROUP_RANK='$grank' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
//					echo "UPDATE STUDENT_GPA_TOTALS SET CLASSGROUP_RANK='$grank' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'<br>";

//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank',PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank',PERCENT_AVERAGE='".$student['PA']."', CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
					$prev_gr_gpa = $student[$_REQUEST['rank']];
               
//echo "$i <br>";
//						$i++;
				}
				else
				{
               
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA'].
//						"',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS'].
//						"',CREDITS_EARNED='".$student['CREDITS']."', CREDITS_ATTEMPTED='".$student['CREDITS_ATTEMPTED'].
//						"' WHERE STUDENT_ID='".
//						$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");				
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='', PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='', PERCENT_AVERAGE='".$student['PA']."', CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
				}
			}
		}
	}

/*
	//cum GPA calculation
	$cum_gpa_array = db_request(db_execute("SELECT ssm.STUDENT_ID,
	ssm.CLASSLEVEL_ID,
	ssm.INCLUDE_IN_CLASS_RANK,
	COALESCE(AVG(g.PERCENT_GRADE),0) AS PA,
	COALESCE(sum(g.GPA_POINTS".$multiplier.")/".$divide.",0) AS GPA,
	COALESCE(sum(g.WEIGHTED_GPA_POINTS".$multiplier.")/".$divide.",0) AS WEIGHTED_GPA,
		sum(".db_case(array("g.CARRIES_CREDITS","'Y'",'g.CREDITS',"'0'")).") AS CREDITS
	FROM STUDENT_REGISTRATION ssm LEFT OUTER JOIN STUDENT_GRADES_CARD g
		ON (g.STUDENT_ID=ssm.STUDENT_ID
			AND ssm.SCHOOL_ID='".get_school_id()."'
			AND g.SCHOOL_ID='".get_school_id()."'
			AND ssm.SCHOOL_YEAR='".get_school_year()."'
			AND (('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE
				OR ssm.END_DATE IS NULL) AND '".get_db_date()."'>=ssm.START_DATE)
			AND g.CREDITS>0
			AND g.AFFECTS_GPA='Y'
			AND g.SCHOOL_TERM_ID IN ".$mps.")
	GROUP BY ssm.STUDENT_ID,ssm.CLASSLEVEL_ID,ssm.INCLUDE_IN_CLASS_RANK
	HAVING sum(g.CREDITS)>0
	ORDER BY $_REQUEST[rank] DESC,sum(g.CREDITS) DESC"),array(),array('CLASSLEVEL_ID'));
	
	if(count($cum_gpa_array))
	{
		foreach($cum_gpa_array as $grade_id=>$grade)
		{
			$i = 1;
			$prev_gpa = false;
			$grade_total = 0;

			if($max_term_array[1]['MAX_SORT_ORDER'] == get_school_term($_REQUEST['school_term_id'],'SORT_ORDER'))
				$include_grade_id = true;

			foreach($grade as $student)
			{
				if($student['INCLUDE_IN_CLASS_RANK']=='Y')
					$grade_total++;
			}
			foreach($grade as $student)
			{
				if($prev_gpa!==$student[$_REQUEST['rank']])
					$rank = $i;
				if($student['INCLUDE_IN_CLASS_RANK']=='Y')
				{
//					if($grade_id == '49')
					{
						//echo $student['STUDENT_ID'].' - '.$student['GPA'].' - '.$student['PA'].'<br>';
					
						//echo "UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank',PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'<br>";
					}
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='$rank',PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
					db_execute("UPDATE STUDENT_GPA_TOTALS SET CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."', CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
					$prev_gpa = $student[$_REQUEST['rank']];

					$i++;
				}
				else
				{
//					db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='', PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
					db_execute("UPDATE STUDENT_GPA_TOTALS SET CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."', CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
				}
			}
		}
	}

	unset($cum_gpa_array);
*/	
	//calculation student_score_rating
        $classlelevs_array = db_request(db_execute("SELECT ID FROM SCHOOL_CLASSLEVELS WHERE SCHOOL_ID = '".get_school_id()."'"));
	db_execute("DELETE FROM STUDENTS_SCORES_RATING WHERE SCHOOL_TERM_ID = '".$_REQUEST['school_term_id']."'");
	foreach($classlelevs_array as $classkey=>$classinfo)
	{
     
    		$stu_array = db_request(db_execute("SELECT STUDENT_ID FROM STUDENT_REGISTRATION WHERE CLASSLEVEL_ID = '".$classinfo['ID']."' AND SCHOOL_YEAR = '".get_school_year()."'"),array(),array('STUDENT_ID'));
    		if(count($stu_array) > 0)
    		{
                	$lines_ID = '';
	        	foreach($stu_array as $key)
    	        	{
        	    		$lines_ID .= $key[1]['STUDENT_ID'].",";
            		}
                	//$lines_ID = substr($lines_ID,0,-1);   // comment rakesh  2-12-2020
                $lines_ID = rtrim(substr($lines_ID,0,-1), ',');  // modified rakesh  2-12-2020	
		        $perc_array = db_request(db_execute("SELECT PERCENT_GRADE,STUDENT_ID,COURSE_TITLE,COURSE_ID,SCHOOL_TERM_ID FROM STUDENT_GRADES_CARD WHERE STUDENT_ID IN ($lines_ID) AND SCHOOL_TERM_ID ='".$_REQUEST['school_term_id']."' ORDER BY PERCENT_GRADE,COURSE_ID DESC"),array(),array('COURSE_ID','PERCENT_GRADE'));
               
     			foreach($stu_array as $stu_key=>$studentvalue)
	    		{
	    	        $student_id = $studentvalue[1]['STUDENT_ID'];
		    		$courses_array = db_request(db_execute("SELECT COURSE_ID,COURSE_TITLE,STUDENT_ID,SCHOOL_TERM_ID FROM STUDENT_GRADES_CARD WHERE SCHOOL_ID = '".get_school_id().
    					"' AND STUDENT_ID = '".$studentvalue[1]['STUDENT_ID']."' AND SCHOOL_TERM_ID = '".$_REQUEST['school_term_id']."' AND COURSE_ID IS NOT NULL"),array(),array());
//    				if($student_id == '304')
//    				{
//    					var_dump($courses_array);
//				}
                	
		    	 	for($y=1;$y<count($courses_array)+1;$y++)
			        {
                    	
    	        		        $course_title = $courses_array[$y]['COURSE_TITLE'];
		                        $course_id = $courses_array[$y]['COURSE_ID'];
	        	                $course_rank = 0;
	                	        $high_score = 0;
		                        $low_score = 500;
		                        $new_course = true;
	    		                unset($pg_added);
	            		        unset($spg_added);
	                    		$max_rank=0;
		                        $cur_rank=0;
                
                     
                    			if(isset($perc_array[$course_id]) && !empty($perc_array[$course_id])) {
                                	 	foreach($perc_array[$course_id] as $perc)
            		                	{
                    	                    	for($a=1;$a<count($perc)+1;$a++)
                        	                	{
    												if(!$pg_added[$perc[$a]['PERCENT_GRADE']])
                                    				{
		                            		        	$max_rank++;
		                            	    			$pg_added[$perc[$a]['PERCENT_GRADE']] = true;
			                                		}
    		                                        if($perc[$a]['PERCENT_GRADE']>$high_score)
            		                                {
                    	        	                     $high_score = $perc[$a]['PERCENT_GRADE'];
                        	                        }
                                    		        $lows_score = $perc[$a]['PERCENT_GRADE'];
                                            		if($perc[$a]['PERCENT_GRADE']<$low_score && $perc[$a]['PERCENT_GRADE'] != '0' && $new_course===true)
	                                                {
														$new_course = false;
														if($perc[$a]['PERCENT_GRADE'])
                		                                        $low_score = $perc[$a]['PERCENT_GRADE'];
                		                                else
                		                            		$low_score = 0;
                    		                        }
                            	                }
                            				}
                                }
    		                       
                   
         
                    			if(isset($perc_array[$course_id]) && !empty($perc_array[$course_id]))
                    			{
                        				foreach($perc_array[$course_id] as $perc)
	                                	{
			                        		for($i=1;$i<count($perc)+1;$i++)
            		                		{
                    	                	        if(!$spg_added[$perc[$i]['PERCENT_GRADE']])
                        	    					{
		                	    		        		$cur_rank++;
			                							$spg_added[$perc[$i]['PERCENT_GRADE']] = true;
				            						}
	                	                        	if($perc[$i]['STUDENT_ID'] == $student_id)
    	                	                        {
        	                		                	$course_rank = $max_rank - $cur_rank + 1;
                                                                //$new_id = db_request(db_execute("SELECT ".db_seq_nextval('STUDENTS_SCORES_RATING_SEQ')." AS LAST_VALUE"));
                                                                //$new_id = $new_id[1]['LAST_VALUE'];
                                                                $_SYSTEM['use_sequence_insert'] = false;
                                        				
                	            						db_execute("INSERT INTO STUDENTS_SCORES_RATING (ID,STUDENT_ID,LOW_SCORE,HIGH_SCORE,COURSE_RANK,SCHOOL_TERM_ID,COURSE_ID)VALUES(".
                                                                        db_seq_nextval('STUDENTS_SCORES_RATING_SEQ').",".$student_id.",".$low_score.",".$high_score.",".$course_rank.",".$_REQUEST['school_term_id'].",".$course_id.")");
                                                    }
                                			}
                        				}
                    			}
                
                     
				}  
            
			}
		}
	}

	//die('jjjjjjjjjjjjj');
	$sql = "SELECT g.STUDENT_ID,NULL AS CLASSLEVEL_ID,
	COALESCE(sum(g.GPA_POINTS".$multiplier.")/".$divide.",0) AS GPA,
	COALESCE(sum(g.WEIGHTED_GPA_POINTS".$multiplier.")/".$divide.",0) AS WEIGHTED_GPA,
		sum(".db_case(array("g.CARRIES_CREDITS","'Y'",'g.CREDITS',"'0'")).") AS CREDITS,
		sum(CASE WHEN g.GPA_POINTS>=0 THEN g.CREDITS ELSE '0' END) AS CREDITS_ATTEMPTED
	FROM STUDENT_GRADES_CARD g
	WHERE g.SCHOOL_ID='".get_school_id()."'
			AND g.CREDITS>0
			AND g.AFFECTS_GPA='Y'
			AND g.SCHOOL_TERM_ID IN ".$mps."
			AND NOT EXISTS (SELECT '' FROM STUDENT_REGISTRATION ssm WHERE g.STUDENT_ID=ssm.STUDENT_ID
			AND ssm.SCHOOL_ID=g.SCHOOL_ID
			AND ssm.SCHOOL_YEAR='".get_school_year()."'
			AND (('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE
				OR ssm.END_DATE IS NULL) AND '".get_db_date()."'>=ssm.START_DATE))
	GROUP BY g.STUDENT_ID
	HAVING sum(g.CREDITS)>0";
	$gpa_array = db_request(db_execute($sql),array(),array('CLASSLEVEL_ID'));

	if(count($gpa_array))
	{
		foreach($gpa_array as $grade_id=>$grade)
		{
			$i = 1;
			$prev_gpa = false;
			$grade_total = 0;

			if($max_term_array[1]['MAX_SORT_ORDER'] == get_school_term($_REQUEST['school_term_id'],'SORT_ORDER'))
				$include_grade_id = true;

			foreach($grade as $student)
			{
//				echo $student['STUDENT_ID'].' - '.$student['GPA'].' - '.$student['PA'].'<br>';
//				db_execute("UPDATE STUDENT_GPA_TOTALS SET CLASS_RANK='', PERCENT_AVERAGE='".$student['PA']."', CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA']."',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS']."' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
				db_execute("UPDATE STUDENT_GPA_TOTALS SET CUMULATIVE_GPA='".$student['GPA']."',CUMULATIVE_WEIGHTED_GPA='".$student['WEIGHTED_GPA'].
					    "',CLASSLEVEL_ID='".$grade_id."',GRADE_TOTAL='".$grade_total."',CUMULATIVE_CREDITS='".$student['CREDITS'].
					    "',CREDITS_EARNED='".$student['CREDITS']."', CREDITS_ATTEMPTED='".$student['CREDITS_ATTEMPTED'].
					    "' WHERE STUDENT_ID='".$student['STUDENT_ID']."' AND SCHOOL_TERM_ID='".$_REQUEST['school_term_id']."'");
			}
		}
	}	
	
	$_SYSTEM['DisableAddonLoader']=false;
	unset($_REQUEST['delete_ok']);
	DisplayHeader('<IMG SRC=images/check.gif> '._('GPA and Student Rating in Class for ').get_school_term($_REQUEST['school_term_id'])._(' have been calculated.'));
	Prompt(_('GPA Calculation'),_('Calculate GPA and Student Rating in Class'),$table);
}
?>