<?php
    include("../../db.php");

    header('Content-Type: application/json');

    $_SESSION["admin"]["session"] = $_REQUEST["session"];
    $m = $_REQUEST['month'];

    $monthsList1 = array(
        1=>'Apr',
        2=>'May',
        3=>'Jun',
        4=>'Jul',
        5=>'Aug',
        6=>'Sep',
        7=>'Oct',
        8=>'Nov',
        9=>'Dec',
    	10=>'Jan',
        11=>'Feb',
        12=>'Mar'
    );

	$overAllTotal=0;$overAlltution=0;$overAllTransport=0;$overAllhotel=0;$overAllOther=0;$overAllOther_hostel=0;$overAllFine=0;$overAllextra=array();					
	$overAllNewextra=0; $totalstrength = 0; $totalamount = 0;

    $classAry = $db->getRows("select class from class");

	$aryFeeType=$db->getRows("select distinct(type) from extrafee_type where session='".$_SESSION["admin"]["session"]."' and status=1 order by type");

        $classfee = array();

		$max_amount = 0;

        foreach($classAry as $class){  $z++;
            $cls = $class['class'];

            $secstu = $db->getRows("select * from student where class='$cls' and session='".$_SESSION["admin"]["session"]."' and status='1' ");
            $totalstrength+=count($secstu);

            $aryStudentList=$db->getRows("select idno, class, stype, htype from student where class='$cls' and session='".$_SESSION["admin"]["session"]."' and status=1 and issue_TC=0 order by class,section");

            $i=0;$total_comp=0;
			for($j=1; $j<=$m; $j++){
				$md[]=$j;
			}

			$mdf= implode(',',$md);

            $monthlycolumn = [];
            $otherfeecolumn = [];
			$backduescolumn = [];
			$hostelothercolumn = [];
			$totalothercolumn = [];
			$finecolumn = [];

            foreach($aryStudentList as $fStudent){
				$mn=$m;

						$tution=0;$trasnport=0;$hostel=0;$otherExtra=0;$total=0;$fine=0;$total_full=0;
						$j=0;$other=0;$k=0;$total_extra=0;$count=0;
							$m1=$m+1;			
							foreach($monthsList1 as $key=>$val){$count++;

							if($mn===0){
							
							}
							else{ $mn--;

						$installTution=$db->getRow("select i".$key.",ip".$key." from installment where idno='".$fStudent["idno"]."'");
						if(is_array($installTution) && count($installTution)>0){
						    $tution = $installTution["i$key"]-$installTution["ip$key"];
						}else{
							$tution="";
						}
						$overAlltution=$tution;

						$installTution=$db->getRow("select b".$key.",bp".$key." from installment where idno='".$fStudent["idno"]."'");
						if(is_array($installTution) && count($installTution)>0){
						    $trasnport = $installTution["b$key"]-$installTution["bp$key"];
						}else{
							$trasnport="";
						}
						$overAllTransport=$trasnport;

						$installTution=$db->getRow("select i".$key.",ip".$key." from hostelinstallment where idno='".$fStudent["idno"]."'");
						if(is_array($installTution) && count($installTution)>0){
						    $hostel = $installTution["i$key"] - $installTution["ip$key"];
						} else{
							$hostel="";
						}
						$overAllhotel=$hostel;

						$extrasum=array();
						$extrafeesingle = 0;
						if(is_array($aryFeeType) && count($aryFeeType)>0){
							foreach($aryFeeType as $ftype){  
								$sums=0;
								$Ftypeid=$db->getVal("select id from extrafee_type where type='".$ftype["type"]."' and class='".$fStudent["class"]."'");
						        $installTution=$db->getRow("select fee,pay from extrafee where type='".$Ftypeid."' and pid= 'p".$key."' and idno='".$fStudent["idno"]."'");

								$no=array();
								if(is_array($installTution) && count($installTution)>0){
								    $sums = $installTution["fee"]-$installTution["pay"];
								}else{
									$sums="";
								}
								$extrasum[]=$sums;
								$total_extra+=$sums;
								$tp=$ftype["type"];
								$overAllextra[$tp]+=$sums;
								$extrafeesingle+=$sums;
							}
						}
                        $monthlycolumn[$key] +=$tution+$trasnport+$extrafeesingle; 
						
						 $overAllNewextra=0;
						for($ij=0; $ij<count($aryFeeType); $ij++){
						 $overAllNewextra+=$extrasum[$ij];
						}

						  $total=0;
						if($overAlltution=="0"&&$overAllTransport==0&&$overAllhotel==0&&$overAllNewextra==0)
						 {$total==0;}
						elseif($overAlltution==""&&$overAllTransport==""&&$overAllhotel==""&&$overAllNewextra=="")
						{$total="";}
						 else{ $total=$overAlltution+$overAllTransport+$overAllhotel+$overAllNewextra;}
			
        		if($key=="12"){
            		$total23=$db->getVal("select (COALESCE(i12,0)+COALESCE(b12,0)-COALESCE(ip12,0)-COALESCE(bp12,0)) from installment where idno='".$fStudent["idno"]."'");
            		
            		$total24=$db->getVal("select (COALESCE(i12,0)-COALESCE(ip12,0)) from hostelinstallment where idno='".$fStudent["idno"]."'");
            		
            		$te=$db->getVal("SELECT sum(fee-pay) FROM `extrafee` WHERE idno='".$fStudent["idno"]."' and pid='p12' and session='".$_SESSION["admin"]["session"]."'  ");
            		
            		$total22=$total23+$total24+$te;
            		$total_full+=$total22;
                }	else    {
        			$total_full=$total_full+$total;
                }

// 			$total_full=$total_full+$total;
			 }}
						$other2=0; $c = 0;
                    $install=$db->getRows("select * from otherfee_structure where (otype = '' or otype = 'old' or otype = 'new' ) and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."' GROUP BY name");
                    foreach($install as $ins){ $c++;
                        $over = "";
                            $exists = $db->getRow("select * from otherfee_structure where name='$ins[name]' and class='".$fStudent["class"]."' and (otype =  '' or otype = 'old' or otype = 'new' ) and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."' ");
						    if($exists > 1){
    						    $fee = $exists['fee'];
    						    $paid = $db->getVal("select sum(amount) from reff_receipt where idno=".$fStudent['idno']." and type='$exists[name]' and session='".$_SESSION["admin"]["session"]."' ");

    						    $discount= $db->getVal("SELECT sum(discount) from otherfee_discount  WHERE  idno='$fStudent[idno]' and other_id='$exists[id]'   and session='".$_SESSION["admin"]["session"]."' "); 

    						    if($fStudent['stype']=="old" && $exists['otype']=="new"){
								    $over="";
    							}
    						   elseif($fStudent['stype']=="new" && $exists['otype']=="old"){
    								$over="";
    							}
    							else {
    							    $over = $exists['fee'] - $discount - $paid;
        							$other2+=$over;
    							}

						    }
						    else {
						        $over = "";
						    }
                        $otherfeecolumn[$c]+= $over;

					}
					$total_full+=$other2;

					$install=$db->getRow("select * from otherfee_structure where otype ='".$fStudent['idno']."' and class='".$fStudent['class']."' and name like 'b%' and session='".$_SESSION["admin"]["session"]."'");
					
									
					$discount= $db->getVal("SELECT sum(discount) from otherfee_discount  WHERE  idno='$fStudent[idno]' and class='".$fStudent['class']."' and other_id='$install[id]'   and session='".$_SESSION["admin"]["session"]."' ");
					
					$install2=$db->getRow("select id from otherfee_structure where otype ='".$fStudent['idno']."' and name like 'Latefine%' and session='".$_SESSION["admin"]["session"]."'");
					
					$discount2= $db->getVal("SELECT COALESCE(sum(discount),0) from otherfee_discount  WHERE  idno='$fStudent[idno]' and other_id in(select id from otherfee_structure where otype ='".$fStudent['idno']."' and name like 'Latefine%' and session='".$_SESSION["admin"]["session"]."')   and session='".$_SESSION["admin"]["session"]."' ");

							$dues=0;
							if(is_array($install) && count($install)>0){
								$installTution=$db->getRows("select * from installment_fine where idno=".$fStudent['idno']." and reasion = '".$install['name']."'"); 

								if(is_array($installTution) && count($installTution)>0){
									$total_dues=0;
									foreach($installTution as $ins){
										$total_dues+=$ins['fine'];
									}
									if($install['fee']==$total_dues)
										$dues=0;
									else $dues=$install['fee']-$total_dues-$discount;	
									   
									
									$installTution_remain=$db->getVal("select sum(fine) from installment_fine where idno=".$fStudent['idno']." and reasion like 'remain%' ");
											$dues=$dues-$installTution_remain;
								}
							  else 		
									$dues=$install['fee']-$discount;
							}
							else
								$dues="";
							$alldues+=$dues;
							
							$total_full+=$dues;
						$backduescolumn[0]+=$dues;	

						$install=$db->getRows("select * from otherfee_structure where class='".$fStudent['class']."' and otype ='".$fStudent['idno']."' and name like 'Latefine%' and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."'");
							$dues=0;$total_dues=0;$fine_t=0;
							if(is_array($install) && count($install)>0){
								foreach($install as $ins){
									$fine_t+=$ins["fee"];
								$installTution=$db->getRows("select * from installment_fine where idno=".$fStudent['idno']." and reasion = '".$ins['name']."'");
								      if(is_array($installTution) && count($installTution)>0){
											foreach($installTution as $ins){
												$total_dues+=$ins['fine'];
											}
									   }
									  else
										$dues=$ins['fee'];
								}
										if($fine_t==$total_dues)
												$dues=0;
								else $dues=$fine_t-$total_dues;
						    }
							else
								$dues="";
							$alldues+=$dues;
							
							$total_full+=$dues-$discount2;
						
                        $install2=$db->getRow("SELECT otype,GROUP_CONCAT(id) as id FROM `otherfee_structure` where otype='".$fStudent['idno']."'  and class='".$fStudent['class']."' and name like 'Latefine%'");

						$dis3= $db->getVal("SELECT sum(discount) from otherfee_discount  WHERE  idno='$fStudent[idno]' and class='".$fStudent['class']."' and other_id in($install2[id])   and session='".$_SESSION["admin"]["session"]."' ");

                        $to=$db->getVal("select sum(fee) from otherfee_structure where otype='".$fStudent['idno']."' and class='".$fStudent['class']."' and name!='Back_Dues' and name NOT like 'Latefine%' and month in ($mdf) and session='".$_SESSION["admin"]["session"]."'");
                        $to1=$db->getVal("SELECT sum(amount) FROM reff_receipt WHERE idno='".$fStudent['idno']."' and session='".$_SESSION["admin"]["session"]."' and type in (SELECT name FROM otherfee_structure WHERE otype='".$fStudent['idno']."' and class='".$fStudent['class']."' and name!='Back_Dues' and name NOT like 'Latefine%' and month in ($mdf))");
                        $dis1=$db->getVal("SELECT sum(discount) FROM otherfee_discount WHERE idno='".$fStudent['idno']."' and class='".$fStudent['class']."' and session='".$_SESSION["admin"]["session"]."' and other_id in (SELECT id FROM otherfee_structure WHERE otype='".$fStudent['idno']."' and class='".$fStudent['class']."' and name!='Back_Dues' and name NOT like 'Latefine%' and month in ($mdf))");

                        $t2=$to-$to1-$dis1;
                        $totalothercolumn[0]+=$t2;

    					if($dues=="0"){
                            $finetotal=$dues;
    					}
    					else
    					{
    					    $finetotal=$dues-$dis3;
    					}
    					$finecolumn[0]+=$finetotal;

    					$other2_hostel=0; $b = 0;
                        $install=$db->getRows("select * from hostel_otherfee_structure where (otype = '' or otype = 'old' or otype = 'new' ) and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."' GROUP BY name");
                        foreach($install as $ins){ $b++;
                            $over_hostel = "";
                                $exists = $db->getRow("select * from hostel_otherfee_structure where name='$ins[name]' and class='".$fStudent["class"]."' and (otype =  '' or otype = 'old' or otype = 'new' ) and month <= '".$m."' and session='".$_SESSION["admin"]["session"]."' ");
    						    if($exists > 1){
        						    $fee = $exists['fee'];
        						    $paid = $db->getVal("select sum(amount) from reff_receipt where idno=".$fStudent['idno']." and type='$exists[name]' and session='".$_SESSION["admin"]["session"]."' ");
    
        						    $discount= $db->getVal("SELECT sum(discount) from hostelotherfee_discount  WHERE  idno='$fStudent[idno]' and other_id='$exists[id]'   and session='".$_SESSION["admin"]["session"]."' "); 
    
        						    if($fStudent['htype']=="old" && $exists['otype']=="new"){
    								    $over_hostel="";
        							} 
        						   elseif($fStudent['htype']=="new" && $exists['otype']=="old"){
        								$over_hostel="";
        							}
        							 elseif($fStudent['htype']==""){
        								$over_hostel="";
        							}
        							else {
        							    $over_hostel = $exists['fee'] - $discount - $paid;
            							$other2_hostel+=$over_hostel;
        							}
    						    }
    						    else {
    						        $over_hostel = "";
    						    }
    					}
    					$total_full+=$other2_hostel;

                $total_comp+=$total_full+$t2;
			}
			$totalamount+=$total_comp;
// 			$classfee[$cls] = $total_comp;
            array_push($classfee,["class"=> $cls, "amt"=> $total_comp]);

			if ($total_comp > $max_amount){
				$max_amount = $total_comp;
			}
        }

   	echo json_encode(array("status" => 1, "total_students"=>$totalstrength, "total_dues"=>$totalamount, "data" => $classfee, "max_amount" => ceil($max_amount)));

?>



