<?php
include("../../db.php");
header('Content-Type: application/json');

    $appSession = $_REQUEST["session"];

    $schoolId = 1;

    //$_SESSION["admin"]["session"] = $_REQUEST["session"];
    $i = $_GET["month"];
    //$session = explode("-", $_SESSION["admin"]["session"]);

    $type = $_GET["type"];
        
    $_GET['month'] = $_GET["month"];
    // $i = $_GET["month"];
    //$session = explode("-", $_SESSION["admin"]["session"]);
    $session = explode("-", $appSession);

        /* Tuition fee Starts */
			$monthlytuitionfee = 0; $tuitiondis = 0;
			$allclass = $db->getRows("select * from class");
			foreach($allclass as $cls){
                $fee = $cls["fee"]/$cls["installment"];
                $stu = $db->getVal("select count(*) from student where class='$cls[class]' and session='".$appSession."' and status='1' and bpl!='1' ");

    			$monthlytuitionfee+=$fee*$stu;
			}

            for($i = 1; $i<=$_GET['month']; $i++){
                $tuitiondis += $db->getVal("select sum(discount_amount) from discount_detail where installment='i$i' and type='tution' and session='$session[0]' ");
            }

            // $tuition = ($monthlytuitionfee*$_GET['month'])-$tuitiondis;
            $tuition = ($monthlytuitionfee*$_GET['month']);
        /* Tuition fee end */

        /* Bus fee Starts */
            $monthlybusfee = 0; $busdis = 0;
            $allbus = $db->getRows("select * from bus");
            foreach($allbus as $bus){
                $fee = $bus["fee"]/$bus["installment"];
                $stu = $db->getVal("select count(*) from student where bus='$bus[id]' and session='".$appSession."' and status='1' and bpl!='1' ");

    			$monthlybusfee+=$fee*$stu;
            }
            for($i = 1; $i<=$_GET['month']; $i++){
                $busdis += $db->getVal("select sum(discount_amount) from discount_detail where installment='b$i' and type='bus' and session='$session[0]' "); 
            }
            // $bus = ($monthlybusfee*$_GET['month'])-$busdis;
            $bus = ($monthlybusfee*$_GET['month']);
        /* Bus fee end */


        /* Hostel fee Starts */
            $monthlyhostelfee = 0; $hosteldis = 0;
            $allhostel = $db->getRows("select * from hostel");
            foreach($allhostel as $hostel){
                $fee = $hostel["fee"]/$hostel["installment"];
                // $stu = $db->getVal("select count(*) from student where hostel='$hostel[id]' and session='".$appSession."' and status='1' and bpl!='1' ");
                   $stu = $db->getVal("select count(s.idno) from hostelinstallment as h,student as s where s.school_id='".$schoolId."' and s.session='".$appSession."' and s.idno=h.idno and  s.issue_TC=0 and s.status=1 and h.hid='$hostel[id]' ");
    			$monthlyhostelfee+=$fee*$stu;
            }
            for($i = 1; $i<=$_GET['month']; $i++){
                $hosteldis += $db->getVal("select sum(discount_amount) from discount_detail where installment='i$i' and type='hostel' and session='$session[0]' ");
            }
            // $hostel = ($monthlyhostelfee*$_GET['month'])-$hosteldis;
            $hostel = ($monthlyhostelfee*$_GET['month']);
        /* Hostel fee end */

        /* Extra fee Starts */
            $monthlyextrafee = 0; $extradis = 0;
            $extratype = $db->getRows("select * from extrafee_type where session='".$appSession."' ");
            foreach($extratype as $extra){
                $fee = $extra["p1"];
                $stu = $db->getVal("select count(*) from student where class='$extra[class]' and session='".$appSession."' and status='1' and bpl!='1' ");
                $monthlyextrafee+=$fee*$stu;
            }
            for($i = 1; $i<=$_GET['month']; $i++){
                $extradis += $db->getVal("select sum(discount_amount) from discount_detail where installment='i$i' and type='extra' and (session='$session[0]' or session='$session[1]' ) ");
            }

            // $totalextra = ($monthlyextrafee*$_GET["month"])-$extradis;
            $totalextra = ($monthlyextrafee*$_GET["month"]);

        /* Extra fee end */ 

        /* Other Fee starts */

		$totalother = 0;
        for($i = 1; $i<=$_GET['month']; $i++){
    
            $otherfee = $db->getRows("select * from otherfee_structure where session='".$appSession."' and month='$i' ");
                foreach($otherfee as $fee){
                    if($fee["otype"] == "new"){
                        $newstu = $db->getVal("select count(idno) from student where session='".$appSession."' and class='$fee[class]' and stype='new' and status='1' ");
                        $total = $newstu*$fee['fee'];
                    } elseif($fee["otype"] == "old"){
                        $oldstu = $db->getVal("select count(idno) from student where session='".$appSession."' and class='$fee[class]' and stype='old' and status='1'  ");
                        $total = $oldstu*$fee['fee'];
                    } elseif($fee["otype"] != ""){
                        $total = $fee['fee'];
                    } else {
                        $stu = $db->getVal("select count(idno) from student where session='".$appSession."' and class='$fee[class]' and status='1'  ");
                        $total = $stu*$fee['fee'];
                    }
                    $totalother+=$total;
                }

            $otherdiscount += $db->getVal("select sum(discount) from otherfee_discount where month='$i' and session='".$appSession."' ");

        }
        
        /* Other Fee end */

        /* Hostel Other Fee starts */
		$hosteltotalother = 0; $total = 0;
        for($i = 1; $i<=$_GET['month']; $i++){

            $hostelotherfee = $db->getRows("select * from hostel_otherfee_structure where session='".$appSession."' and month='$i' ");
                foreach($hostelotherfee as $fee){
                    // echo $fee['fee'];

                    if($fee["otype"] == "new"){
                        $newstu = $db->getVal("select count(s.idno) from hostelinstallment as h,student as s where s.school_id='".$schoolId."' and s.session='".$appSession."' and s.idno=h.idno and  s.issue_TC=0 and s.status=1 and s.htype='new' and s.class='$fee[class]' ");
                        // $newstu = $db->getVal("select count(idno) from student where session='".$appSession."' and class='$fee[class]' and htype='new' and status='1' ");
                        $total = $newstu*$fee['fee'];
                    } elseif($fee["otype"] == "old"){
                        // $oldstu = $db->getVal("select count(idno) from student where session='".$appSession."' and class='$fee[class]' and htype='old' and status='1'  ");
                        $oldstu = $db->getVal("select count(s.idno) from hostelinstallment as h,student as s where s.school_id='".$schoolId."' and s.session='".$appSession."' and s.idno=h.idno and  s.issue_TC=0 and s.status=1 and s.htype='old' and s.class='$fee[class]' ");
                        $total = $oldstu*$fee['fee'];
                    } elseif($fee["otype"] != ""){
                        $total = $fee['fee'];
                    } else {
                        // $stu = $db->getVal("select count(idno) from student where session='".$appSession."' and class='$fee[class]' and status='1'  ");
                        $stu = $db->getVal("select count(s.idno) from hostelinstallment as h,student as s where s.school_id='".$schoolId."' and s.session='".$appSession."' and s.idno=h.idno and  s.issue_TC=0 and s.status=1 and s.class='$fee[class]' ");
                        $total = $stu*$fee['fee'];
                    }
                    $hosteltotalother = $hosteltotalother+$total;
                }

            $hostelotherdiscount += $db->getVal("select sum(discount) from hostelotherfee_discount where month='$i' and session='".$appSession."' ");

        }
        /* Hostel Other Fee end */

        $totaloutstanding = $tuition+$bus+$totalother+$totalextra+$hostel+$hosteltotalother;

        $totaldiscount = $tuitiondis+$busdis+$otherdiscount+$extradis+$hosteldis+$hostelotherdiscount;
        
        $between="";
        $newDate = $session[0]."-1-1";
        
        $icheck = $_GET["month"];

        if($icheck<=9){
			$year = $session[0];
// 			$newDate = $year."-".($icheck+2)."-1";
			$d=cal_days_in_month(CAL_GREGORIAN,($icheck+3),$year);
			$todate = $year."-".($icheck+3)."-$d";
        }
        else{
            $year = $session[1];
            // $newDate = $year."-".($icheck-10)."-1";
            $d=cal_days_in_month(CAL_GREGORIAN,($icheck-9),$year);
            $todate = $year."-".($icheck-9)."-$d";
        }

    $todate1 = date('Y-m-d', strtotime($todate . ' +1 day'));
    $between = " and date between '$newDate' and '$todate1' ";

/////////////////
$today_date = date('Y-m-d');
$betweenOther = " and date between '$newDate' and '$today_date' ";

   // $tuitionpaid = $db->getVal("select sum(amount) from reff_receipt where type='fee' and session='".$appSession."' $between ");
$tuitionpaid = $db->getVal("select sum(amount) from reff_receipt where type='fee' and session='".$appSession."' $between ");

    $buspaid = $db->getVal("select sum(amount) from reff_receipt where type='busfee' and session='".$appSession."' $between ");

    ///
//$other=$db->getVal("select sum(amount) from reff_receipt as r INNER join student as s on s.idno=r.idno where (r.type not like '%extrafee%') and type not in ('busfee','fee','hostelfee') and (r.date between '".$_POST["tfrom"]."' and '".$_POST["tto"]."') and r.session='".$appSession."' " );

$otherpaid = $db->getVal("select sum(amount) from reff_receipt where (type not like '%extrafee%') and type not in ('busfee','fee','hostelfee') and session='".$appSession."' $between ");

    $extrapaid = $db->getVal("select sum(amount) from reff_receipt where (type LIKE '%extrafee%') and session='".$appSession."' $between ");

    $hostelpaid = $db->getVal("select sum(amount) from reff_receipt where type='hostelfee' and session='".$appSession."' $between ");

    $hostelotherpaid = 0;
    $hostelotherfee = $db->getRows("select * from hostel_otherfee_structure where session='".$appSession."' and month='1' group by name ");
    foreach($hostelotherfee as $hostelfee){
        $hostelotherpaid+= $db->getVal("select sum(amount) from reff_receipt where type='$hostelfee[name]' and (type!='fee' and type!='bus' and type NOT LIKE '%extrafee%') and session='".$appSession."' $between ");
    }

    //$totalpaid = $tuitionpaid+$buspaid+$otherpaid+$extrapaid+$hostelpaid+$hostelotherpaid;

$totalpaid = $tuitionpaid+$buspaid+$otherpaid+$hostelpaid;

    $json = array(
            "Payable"   =>  $totaloutstanding,
            "Received"  =>  $totalpaid,
            "Discount"  =>  $totaldiscount,
            "Remaining" =>  $totaloutstanding-$totalpaid-$totaldiscount
    );

    echo json_encode($json);

    