<?php include('parent/callMe.php');?>
<?php include('Sessioncheck.php');?>
<?php


     $branch=$_SESSION['branch'];

     if ($_REQUEST['month']!=NULL) {
       $month=$_REQUEST['month'];
     } else {
     $month=date('Y-m');
     }

   $session;

   if($_REQUEST['session']!=NULL){

      $session=$_REQUEST['session'];
      
    }else {
      
      $session=$_SESSION['current_session'];
    }


     /* code for advance Search*/


$sql = "SELECT * FROM  eduportal_monthly_challan  WHERE branch_id='$branch' and from_year_month='$month' ";

   if (isset($_REQUEST['class_id'])) {    
 

    if($_REQUEST['class_id']=="All"){
      $sql;
    } else {
    $sql .= " AND classs_id = '" .$_REQUEST['class_id']. "' ";

    }       

     }     
            if($_REQUEST['orderBy']=='GradeWise'){

          //  $sql .= " order by CAST(classs_id as UNSIGNED) asc";
            $sql .= "ORDER BY CAST(classs_id AS UNSIGNED) ASC, CAST(challan_id AS UNSIGNED) ASC";


            }

            if($_REQUEST['orderBy']=='InvoiceWise'){

            $sql .= " order by CAST(challan_id as UNSIGNED) asc";

            }

              if(!isset($_REQUEST['orderBy'])){

            $sql .= "order by CAST(challan_id as UNSIGNED) desc ";

            }  

  $WorkAssignListings = $m->query($sql);
 
 
?>

<!DOCTYPE HTML>
<html>
<head>

<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Print Fee Invoice Report</title>
<link rel="icon" type="image/png" href="img/favicon.png" sizes="32x32" />
<?php include('RootMe/css_links.php');?> 
<script type="text/javascript">
// window.print();
</script>
<style type="text/css">

@media print {
.print-link {
display:none;
}
}

tr th {border-top:1px solid gray;border-left:1px solid gray;border-bottom:1px solid gray;text-align:center;background-color:lightgray;font-size:14px;}
tr td {border-bottom:1px solid gray;border-left:1px solid gray;font-size:14px;}
  
.border{border:1px solid black;}
.center{text-align:center;}
.alignment{padding-left:1%;text-align:left;}

/* CSS for print */
@media print {
  .hidden-on-print {
    display: none;
  }
  #naving{display: none;}
}

</style>

</head>


<body style="font-size:12px;color: black;">



    <!-- ================================== search code ================================================ -->
<!-- 
<form class="" action="print_fee_invoice_report.php" enctype="multipart/form-data" method="get">
        <div class="panel panel-default hidden-on-print">
          <div class="panel-heading"><a href="<?php echo $_SESSION['dashboard'];?>">Dashboard </a> &nbsp; <i style="" class="fa fa-angle-double-right"></i>  &nbsp; 
    
     <a href="multi_fee_reports.php">Fee Reports </a> &nbsp; <i style="" class="fa fa-angle-double-right"></i>  &nbsp; 
          
      Fee Settlement Report

       <span style="float:right;margin-top: -7px;">

          <input type="hidden" value="<?php echo session_id().session_id().session_id().session_id().session_id(); ?>" name="session" />



          </span><div class="clearfix"></div> </div>
          <div class="panel-body">
          
            <div class="col-md-12" id="" style="">
            
              <div class="col-md-2 col-xs-12" style="padding: 8px;">
                <div class="form-group ">
                  <label class="required">Grade</label>

            
                    <select name="class_id" onChange="getsecall(this.value)" class="form-control" >
                     <option value="All">All</option> 
                    <?php
                    $m->where("branch_id", $_SESSION['branch']);
                    $WorkAssignList = $m->get("edu_class_config");
                    foreach($WorkAssignList as $WorkAssignListVal)
                    { ?>

                       <option <?php if($_REQUEST['class_id']==$WorkAssignListVal['class_id']){echo 'selected';}?> value="<?php echo $WorkAssignListVal['class_id']; ?>"><?php echo $WorkAssignListVal['Class_Name']; ?></option>


                            <?php
                    } ?>

                    </select>

                  
                </div>
              </div>
              

                           <div class="col-md-2 col-xs-12" style="padding: 8px;">
                <div class="form-group ">
                  <label class="required">Section</label>

            
                    <select name="section" id="txt_section" class="form-control">
                      
                        <option value="All">All</option> 
                   <?php

                              if($_REQUEST['section']!='')

                              { 
                              $m->where("class_id", $_REQUEST['class_id']);

                              $secondLevel = $m->get("edu_adm_section_detail");

                              foreach($secondLevel as $secondLevel_val)

                              {

                               ?>

                                <option <?php if($_REQUEST['section']==$secondLevel_val['section_id']){echo "selected";} ?> value="<?php echo $secondLevel_val['section_id']; ?>"><?php echo trim(utf8_encode_all($secondLevel_val['section_name'])); ?></option>

                              <?php }   ?>

                                

                          <?php } ?>



                    </select>

                  
                </div>
              </div>
               


                    <div class="col-md-2 col-xs-12" style="padding: 8px;">
                    <div class="form-group ">

                    <label class="required">Month</label>

                    <input type="month" id="month" name="month" required class="form-control" value="<?php
                    if ($_REQUEST['month']!=NULL) {
                    echo $_REQUEST['month'];
                    } else {
                    echo date("Y")."-".date("m"); }?>">


                    </div>
                    </div>



  <div class="col-md-2 col-xs-12" style="padding:8px;" >

       <div class="form-group ">

          <label class=""> Order By</label>
        <select name="orderBy" id="orderBy" class="form-control" >
       
       <option <?php if($_REQUEST['orderBy']=='GradeWise'){echo 'selected';}?>  value="GradeWise">Grade Wise</option>

       <option <?php if($_REQUEST['orderBy']=='InvoiceWise'){echo 'selected';}?>  value="InvoiceWise"> Invoice# wise</option>  

     <option  <?php if($_REQUEST['orderBy']=='UserWise'){echo 'selected';}?> value="UserWise">User Wise</option> 
                              
       </select>
       
       </div>
  </div> 



        <div class="col-md-2" style="padding:2px;margin-top: 29px;float: right;">
        <div class="form-group ">
        <input type="hidden" name="addaccountAdmin" value="1">
        <button type="submit" class="btn btn-primary">Search</button>
        <button id="printButton" class="btn btn-primary">Print</button>
        </div>

         <script>
    // Add an event listener to the print button
    document.getElementById("printButton").addEventListener("click", function() {
        window.print(); // This opens the print dialog
    });
</script>

        </div>

             
            </div>
            <div class="clearfix"></div>
          </div>
        </div>
      </form> -->

    <!-- =================================== end search code ===========================================-->

<div style="width:100%; text-align:center; height:80px;float:left;">
<div style="width:20%;font-size:13px;text-align:center;float:left;height:80px;">

<img src="<?php echo DOMAIN;?>Production/img/<?php echo $brid[0]['logo']; ?>" style="height:100px;width:100px;"  />

</div>

<div style="width:60%; float:left;">

         <span style="font-size:25px; text-align:center;font-family:times new roman;font-weight:bold;color: black;">  <?php echo $brid[0]['Branch_Name'];?> </span>

         <br>
         <span style="font-size:14px; text-align:center;color: black;">  <?php echo $brid[0]['Branch_Location']."<br>"." Cell: ".$brid[0]['Owner_no'];?> </span>



      </div>
</div>
<br><br>
<div style="text-align:center;font-size:22px;font-weight:bold;"> Fee Invoice Report </div>

<hr style="width:100%;">

<div style="width:25%; float:left;font-size:20px; "> <b> Month:</b> <?php echo date("F",strtotime($_REQUEST['month']));?> </div>

<div style="width:25%;float:left;font-size:16px;">  <b> Grade:</b> 

<?php if ($_REQUEST['class_id']=="All") {
   echo "All";
} else { echo getClassName($m,$_REQUEST['class_id']); }?>
 

</div>


<div style="width:15%;float:left;font-size:16px;">  <b> Section:</b> 

<?php if ($_REQUEST['section']=="All") {
   echo "All";
} else { echo getSectionName($m,$_REQUEST['section']); }?>
 

</div>


<div style="width:15%;float:left;font-size:16px;">  <b> Order By:</b> 

<?php echo $_REQUEST['orderBy'];?> 

</div>





<?php
// Initialize variables to store class-wise total and overall total
$overallTotal = 0;
$currentClass = null;
$Arrear = 0;
$RegFee = 0;
$AdmFee = 0;
$MonthlyFee = 0;
$AnnualFund = 0;
$Fine = 0;
$OtherCharges = 0;
$Total = 0;

$count=1;
$classSerialNumber = 1;
?>

<table style="width:100%; color:black;" cellspacing="0">
    <thead>
        <tr>

              
                <th width="3%">S.No</th>
                <th width="3%">Invc#</th>
                <th width="8%" style="text-align:center;">Adm/Roll</th>                
                <th width="8%" style="text-align:center;"> Student Name </th>
                <th width="8%" style="text-align:center;"> Father Name </th>
                <th width="8%" style="text-align:center;"> Grade </th>
                <th width="8%" style="text-align:center;"> Section </th>
                <th width="8%" style="text-align:center;"> Issue Date </th>
                <th width="8%" style="text-align:center;"> Due Date </th>
                <th width="8%" style="text-align:center;"> Inv Month </th>
                <th width="8%" style="text-align:center;"> Arrear </th>
                <th width="8%" style="text-align:center;"> Reg.Fee </th>                
                <th width="8%" style="text-align:center;"> Adm.Fee </th>
                <th width="8%" style="text-align:center;"> M.Fee </th>
                <th width="8%" style="text-align:center;"> A.Fund </th>
                <th width="8%" style="text-align:center;"> Fine </th>
                <th width="8%" style="text-align:center;"> O.Chargs </th>
                <th width="8%" style="text-align:center;"> Total </th>
          
                                
            </tr>
    </thead>
    <tbody>
        <?php
        // Loop through the query results
        foreach ($WorkAssignListings as $WorkAssignListVal) {

           
        $c_id=$WorkAssignListVal['challan_id'];
        $sq = "SELECT SUM(arrears_amount) AS arrears FROM  eduportal_monthly_challan_arrears  WHERE branch_id='$branch' and against_challan_id='$c_id' ";        
        $sumAmt = $m->query($sq);



         $sqq = "SELECT SUM(CASE WHEN feetype_id = 1 THEN fee_amount ELSE 0 END) AS monthly, SUM(CASE WHEN feetype_id = 5 THEN fee_amount ELSE 0 END) AS Afund, SUM(CASE WHEN feetype_id = 4 THEN fee_amount ELSE 0 END) AS Fine, SUM(CASE WHEN feetype_id = 6 THEN fee_amount ELSE 0 END) AS Reg, SUM(CASE WHEN feetype_id = 2 THEN fee_amount ELSE 0 END) AS Adm, SUM(CASE WHEN feetype_id NOT IN (1, 5, 4, 6, 2) THEN fee_amount ELSE 0 END) AS Other FROM  eduportal_monthly_challan_details  WHERE branch_id='$branch' and challan_id='$c_id' ";        
         $sumAmount = $m->query($sqq);



// fine calculate

if ( date("Y-m-d") > $WorkAssignListVal['challan_duedate'] && $WorkAssignListVal['paid_status']==0) {  

$start_date = strtotime($WorkAssignListVal['challan_duedate']);

if ($WorkAssignListVal['challan_validity_date'] > date("Y-m-d") ) {

$end_date = strtotime(date("Y-m-d"));

} else {

$end_date = strtotime($WorkAssignListVal['challan_validity_date']);

}

$datediffrence =($end_date - $start_date)/60/60/24;

if($brid[0]['fine_type']=="0" || $brid[0]['fine_type']==0){  

$student_fine= $brid[0]['late_fee_fine']; } 

else {    

$student_fine= ($datediffrence * $brid[0]['late_fee_fine']);

}

 } else {

   $student_fine=$sumAmount[0]['Fine'];
 }



        // get student data
        $s_ids=$WorkAssignListVal['student_id'];
        $getStd = "select first_name, last_name, section_id,ref_no,candidate_roll_no from edu_student_details where student_id='$s_ids' AND branch_id='$branch' "; 
        $getStudentData = $m->query($getStd);

        // $toMonthYear=explode("-",$WorkAssignListVal['month']);
        $t_arrears+=$sumAmt[0]['arrears'];
        $t_reg+=$sumAmount[0]['Reg'];
        $t_adm+=$sumAmount[0]['Adm'];
        $t_monthly+=$sumAmount[0]['monthly'];
        $t_Afund+=$sumAmount[0]['Afund'];
        $t_Fine+=$student_fine;
        $t_other+=$sumAmount[0]['Other'];
        $t_total+=$sumAmt[0]['arrears']+$sumAmount[0]['Reg']+$sumAmount[0]['Adm']+$sumAmount[0]['monthly']+$sumAmount[0]['Afund']+$student_fine+$sumAmount[0]['Other'];



                if ($_REQUEST['orderBy']!="InvoiceWise") {
                    $usrCls=$WorkAssignListVal['classs_id'];
                 $clsUsrName="Class Name: ". getClassName($m, $WorkAssignListVal['classs_id']);
                }
                 
         


            // Check if the class has changed, print the class-wise total, and update the overall total
            if ($currentClass !== $usrCls) {
                if ($currentClass !== null) {
                    // Print the class-wise total
                    echo "<tr style='background-color:lightgray;'>
                            <td colspan='10' style='text-align:center;'>Total</td>
                            <td>" . $Arrear . "</td>
                            <td>" . $RegFee . "</td>
                            <td>" . $AdmFee . "</td>
                            <td>" . $MonthlyFee . "</td>
                            <td>" . $AnnualFund . "</td>
                            <td>" . $Fine . "</td>
                            <td>" . $OtherCharges . "</td>
                            <td>" . $Total . "</td>
                          </tr>";
                    // Update the overall total
                    $overallArrearTotal += $Arrear;
                    $overallRegFeeTotal += $RegFee;
                    $overallAdmFeeTotal += $AdmFee;
                    $overallMonthlyFeeTotal += $MonthlyFee;
                    $overallAnnualFundTotal += $AnnualFund;
                    $overallFineTotal += $Fine;
                    $overallOtherChargesTotal += $OtherCharges;
                    $overallTotal += $Total;
                }

                // Reset the class-wise total for the new class
                $Arrear = 0;
                $RegFee = 0;
                $AdmFee = 0;
                $MonthlyFee = 0;
                $AnnualFund = 0;
                $Fine = 0;
                $OtherCharges = 0;
                $Total = 0;
                $classSerialNumber = 1; // Reset the serial number for the new class

                // Print the heading for the new class
                echo "<tr style='background-color:lightblue;'>
                        <td colspan='18' style='text-align:center;'><b> " . $clsUsrName . "</b></td>
                      </tr>";
                $currentClass = $usrCls;
            }
          $totalRecord=$count++;
$monthYear=date("M-y", strtotime($getChlMonth[0]['from_year_month']));
            // Process the current record, you can display the data as needed
            echo "<tr>
                <td>" . $classSerialNumber++ . "</td>
                <td>" . $WorkAssignListVal['challan_id'] . "</td>
                <td>" . $getStudentData[0]['ref_no'] . "/" . $getStudentData[0]['candidate_roll_no'] . "</td>
                <td>" . $getStudentData[0]['first_name'] . "</td>
                <td>" . $getStudentData[0]['last_name'] . "</td>
                <td>" . getClassName($m, $WorkAssignListVal['classs_id']) . "</td>
                <td>" . getSectionName($m, $getStudentData[0]['section_id']) . "</td>
                <td>" . date("d-M-Y", strtotime($WorkAssignListVal['challan_created'])) . "</td>
                <td>" . date("d-M-Y", strtotime($WorkAssignListVal['challan_duedate'])) . "</td>
                <td>" . date("M-y", strtotime($WorkAssignListVal['from_year_month'])) . "</td>
                <td>" . $sumAmt[0]['arrears'] . "</td>
                <td>" . $sumAmount[0]['Reg'] . "</td>
                <td>" . $sumAmount[0]['Adm'] . "</td>
                <td>" . $sumAmount[0]['monthly'] . "</td>
                <td>" . $sumAmount[0]['Afund'] . "</td>
                <td>" . $student_fine . "</td>
                <td>" . $sumAmount[0]['Other'] . "</td>
                <td>" . ($sumAmt[0]['arrears'] + $sumAmount[0]['Reg'] + $sumAmount[0]['Adm'] + $sumAmount[0]['monthly'] + $sumAmount[0]['Afund'] + $student_fine + $sumAmount[0]['Other']) . "</td>
            </tr>";
                  
            // Accumulate the settlement amount for the current class
            $Arrear += $sumAmt[0]['arrears'];
            $RegFee += $sumAmount[0]['Reg'];
            $AdmFee += $sumAmount[0]['Adm'];
            $MonthlyFee += $sumAmount[0]['monthly'];
            $AnnualFund += $sumAmount[0]['Afund'];
            $Fine += $student_fine;
            $OtherCharges += $sumAmount[0]['Other'];
            $Total += ($sumAmt[0]['arrears'] + $sumAmount[0]['Reg'] + $sumAmount[0]['Adm'] + $sumAmount[0]['monthly'] + $sumAmount[0]['Afund'] + $student_fine + $sumAmount[0]['Other']);
        }

        // Print the total settlement for the last class in the result set

                       
                  if ($currentClass !== null) {
            echo "<tr style='background-color:lightgray;'>
                    <td colspan='3' style='text-align:center;'>Total Record: $totalRecord</td>
                    <td colspan='7' style='text-align:center;'>Total</td>
                    <td>" . $Arrear . "</td>
                    <td>" . $RegFee . "</td>
                    <td>" . $AdmFee . "</td>
                    <td>" . $MonthlyFee . "</td>
                    <td>" . $AnnualFund . "</td>
                    <td>" . $Fine . "</td>
                    <td>" . $OtherCharges . "</td>
                    <td>" . $Total . "</td>
                  </tr>";
            // Update the overall total for the last class
            $overallArrearTotal += $Arrear;
            $overallRegFeeTotal += $RegFee;
            $overallAdmFeeTotal += $AdmFee;
            $overallMonthlyFeeTotal += $MonthlyFee;
            $overallAnnualFundTotal += $AnnualFund;
            $overallFineTotal += $Fine;
            $overallOtherChargesTotal += $OtherCharges;
            $overallTotal += $Total;
        }

         
       
         if ($_REQUEST['orderBy']=="InvoiceWise") {
             $totalSumArrear=$t_arrears;
            $totalSumRegFee=$t_reg;
            $totalSumAdmFee=$t_adm;
            $totalSumMonthlyFee=$t_monthly;
            $totalSumAnnualFund=$t_Afund;
            $totalSumFine=$t_Fine;
            $totalSumOtherCharges=$t_other;
            $totalSum=$t_total;
        } else {
            $totalSumArrear=$overallArrearTotal;
            $totalSumRegFee=$overallRegFeeTotal;
            $totalSumAdmFee=$overallAdmFeeTotal;
            $totalSumMonthlyFee=$overallMonthlyFeeTotal;
            $totalSumAnnualFund=$overallAnnualFundTotal;
            $totalSumFine=$overallFineTotal;
            $totalSumOtherCharges=$overallOtherChargesTotal;
            $totalSum=$overallTotal;

        }

         // Print the overall total
        echo "<tr style='background-color:lightgray;'>
                <td colspan='10' style='text-align:center;'>Overall Total</td>
                <td>" . $totalSumArrear . "</td>
                <td>" . $totalSumRegFee . "</td>
                <td>" . $totalSumAdmFee . "</td>
                <td>" . $totalSumMonthlyFee . "</td>
                <td>" . $totalSumAnnualFund . "</td>
                <td>" . $totalSumFine . "</td>
                <td>" . $totalSumOtherCharges . "</td>
                <td>" . $totalSum . "</td>
              </tr></tbody></table>";
        ?>
    </tbody>
</table>

<?php include('RootMe/js_linksscript.php');?>
</body>
</html>