<?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');
    }


     /* code for advance Search*/


$sql = "SELECT esd.student_id, esd.first_name, esd.status, esd.last_name, esd.ref_no, esd.candidate_roll_no, esd.class_id, esd.section_id,esd.date_of_adms, emc.challan_id, emc.classs_id, emc.challan_duedate, emc.total_challan_amt, esd.cell_no, esd.mother_cell, emc.editDueDate, emc.arrears, emc.challan_validity_date, emc.from_year_month, emc.year, emc.paid_status
FROM edu_student_details AS esd
INNER JOIN eduportal_monthly_challan AS emc
ON esd.student_id = emc.student_id
WHERE emc.branch_id='$branch' ";

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

    }       

     if($_REQUEST['section']=="All"){
      $sql;
    } else {
    $sql .= " AND esd.section_id = '" .$_REQUEST['section']. "' ";

    }


    if($_REQUEST['std_status']=="All"){
      $sql;
    } else {
    $sql .= " AND esd.status = '" .$_REQUEST['std_status']. "' ";

    }


     if($_REQUEST['due_date']=="All"){
      $sql;
    } else {
    $sql .= " AND emc.editDueDate = '" .$_REQUEST['due_date']. "' ";

    }

}

    $sql .= " AND emc.from_year_month = '" . $month . "'";

    $sql .= " ORDER BY esd.class_id, esd.section_id, 
          CAST(SUBSTRING_INDEX(esd.candidate_roll_no, '-', 1) AS UNSIGNED) ASC, 
          CAST(SUBSTRING_INDEX(esd.candidate_roll_no, '-', -1) AS UNSIGNED) ASC";

//  echo $sql;

  $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 Outstanding 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_outstanding_fee.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 Outstanding 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" />

<input type="hidden" name="orderBy" value="GradeWise">

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

            
                    <select name="class_id" onChange="getsec(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-1 col-xs-12" style="padding: 8px;width: 150px;">
                <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-1 col-xs-12" style="padding: 8px;width: 150px;">
                    <div class="form-group ">

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

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


                    </div>
                    </div>



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

       <div class="form-group ">

          <label class=""> Status</label>
        <select name="std_status" id="std_status" class="form-control" >
       
      <option <?php if($_REQUEST['std_status']=='All'){echo 'selected';}?>  value="All">All</option>
        <option <?php if($_REQUEST['std_status']=='0'){echo 'selected';}?>  value="0">Active</option>
        <option  <?php if($_REQUEST['std_status']=='1'){echo 'selected';}?> value="1">InActive</option>
                        
       </select>
       
       </div>
  </div>



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

       <div class="form-group ">

          <label class=""> Fee Status</label>
        <select name="fee_status" id="fee_status" class="form-control" >
       
      <option <?php if($_REQUEST['fee_status']=='All'){echo 'selected';}?>  value="All">All</option>
        <option <?php if($_REQUEST['fee_status']=='Clear'){echo 'selected';}?>  value="Clear">Clear</option>
        <option  <?php if($_REQUEST['fee_status']=='Pending'){echo 'selected';}?> value="Pending">Pending</option>
                        
       </select>
       
       </div>
  </div>                        


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

       <div class="form-group ">

          <label class=""> Due Date</label>
        <select name="due_date" id="due_date" class="form-control" >
       
            <option <?php if($_REQUEST['due_date']=='All'){echo 'selected';}?>  value="All">All</option>
            <option <?php if($_REQUEST['due_date']=='0'){echo 'selected';}?>  value="0">Default</option>
            <option  <?php if($_REQUEST['due_date']=='1'){echo 'selected';}?> value="1">Extend</option>
                        
       </select>
       
       </div>
  </div>

        <div class="col-md-2" style="padding:2px;margin-top: 29px;">
        <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>
       
        <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>
            <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;"> Outstanding Report </div>

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

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

<div style="width:15%;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> Std Status:</b> 


<?php if ($_REQUEST['std_status']=="All") {
   echo "All";
} elseif ($_REQUEST['std_status']==0) {
    echo "Active";
} elseif ($_REQUEST['std_status']==1) {
    echo "In-Active";
}
?>

</div> 

</div>


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

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

</div>


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

<?php if ($_REQUEST['due_date']=="All") {
   echo "All";
} elseif ($_REQUEST['due_date']==0) {
    echo "Default";
} elseif ($_REQUEST['due_date']==1) {
    echo "Extend";
}
?>

</div>




<?php
// Initialize variables to store class-wise total and overall total
$overallTotalInvoiceAmount = 0;
$overallTotalAmountReceived = 0;
$overallTotalAmountSettle = 0;
$overallTotalBalance = 0;
$currentClass = null;
$invoiceAmount = 0;
$AmountReceived = 0;
$AmountSettle = 0;
$AmountBalance = 0;
$count=1;
$classSerialNumber = 1;
?>

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

              
                <th width="3%">S.No</th>
                <th width="3%">Adm</th> 
                <th width="3%">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;"> F.Cell# </th>
                <th width="8%" style="text-align:center;"> M.Cell# </th>
                <th width="8%" style="text-align:center;"> Invc# </th>
                <th width="8%" style="text-align:center;"> Due Date </th>
                <th width="6%" style="text-align:center;"> Invc Amt</th>
                <th width="8%" style="text-align:center;"> Recip#</th>
                <th width="6%"> Amt Recv</th>
                <th width="8%"> Settle#</th>                
                <th width="8%" style="padding:16px;"> Amt Setl</th>
                <th width="8%" style="padding:4px;"> Balance</th>
                <th width="8%" style="text-align:center;"> Status </th>
          
                                
            </tr>
    </thead>
    <tbody>
        <?php
        // Loop through the query results
        foreach ($WorkAssignListings as $WorkAssignListVal) {

           $c_id=$WorkAssignListVal['challan_id'];
        // $sq = "SELECT SUM(received) AS rcvd FROM  eduportal_challan_fee_receiving  WHERE branch_id='$branch' and against_challan_id='$c_id' ";        
        // $sumAmt = $m->query($sq);

$sq = "SELECT
against_challan_id,
GROUP_CONCAT(DISTINCT receipt_no) AS receipt_numbers,
SUM(received) AS rcvd
FROM eduportal_challan_fee_receiving
WHERE branch_id='$branch' AND against_challan_id='$c_id'
GROUP BY against_challan_id";
$sumAmt = $m->query($sq);


        // $sq = "SELECT SUM(settellment_amount) AS settlmntAmt FROM  eduportal_challan_fee_settlement  WHERE branch_id='$branch' and against_challan_id='$c_id' ";        
        // $sumsettleAmt = $m->query($sq);

$sq = "SELECT
against_challan_id,
GROUP_CONCAT(DISTINCT settellment_no) AS settle_numbers,
SUM(settellment_amount) AS settlmntAmt
FROM eduportal_challan_fee_settlement
WHERE branch_id='$branch' AND against_challan_id='$c_id'
GROUP BY against_challan_id";
$sumsettleAmt = $m->query($sq);

        $balance=($WorkAssignListVal['total_challan_amt'])-($sumAmt[0]['rcvd']+$sumsettleAmt[0]['settlmntAmt']);

        if ($_REQUEST['fee_status']=="All") {
          
        } elseif ($_REQUEST['fee_status']=="Pending") {
          
          if ($balance==0) {
          continue;
         }

        } elseif ($_REQUEST['fee_status']=="Clear") {
          
          if ($balance!=0) {
          continue;
         }

        }


$mm=$WorkAssignListVal['from_year_month'];
$yy=$WorkAssignListVal['year'];
$getFineReceived = "SELECT sum(received) as recevd FROM  eduportal_challan_fee_receiving  WHERE branch_id='$branch' and challan_id='$c_id' and month='$mm' and year='$yy' and feetypeid=4";
//echo $getFineReceived;
$getFineAmountReceived = $m->query($getFineReceived);


        // 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=($sumAmt[0]['Fine']+$sumAmtArr[0]['Fine'])-($sumAmtRec[0]['Fine']);
// }

        
        $t_total+=$WorkAssignListVal['total_challan_amt'];
        $t_rcvd+=$sumAmt[0]['rcvd'];
        $t_stl+=$sumsettleAmt[0]['settlmntAmt'];
        $t_balance+=($WorkAssignListVal['total_challan_amt'])-($sumAmt[0]['rcvd']+$sumsettleAmt[0]['settlmntAmt']);





        //   if ($_REQUEST['orderBy']=="GradeWise") {
                 $usrCls=$WorkAssignListVal['class_id'];
                 $clsUsrName="Class Name: ". getClassName($m, $WorkAssignListVal['class_id']);
          // } elseif ($_REQUEST['orderBy']=="UserWise") {
          //        $usrCls=$WorkAssignListVal['user_id'];
          //        $clsUsrName="User Name: ". $userName[0]['user_fname'];
          // }


            // 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='11' style='text-align:center;'>Total</td>
                            <td>" . $invoiceAmount . "</td>
                            <td></td>
                            <td>" . $AmountReceived . "</td>
                            <td></td>
                            <td>" . $AmountSettle . "</td>
                            <td>" . $AmountBalance . "</td>
                            <td></td>
                          </tr>";
                    // Update the overall total
                    $overallTotalInvoiceAmount += $invoiceAmount;
                    $overallTotalAmountReceived += $AmountReceived;
                    $overallTotalAmountSettle += $AmountSettle;
                    $overallTotalBalance += $AmountBalance;
                }

                // Reset the class-wise total for the new class
                $invoiceAmount = 0;
                $AmountReceived = 0;
                $AmountSettle = 0;
                $AmountBalance = 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['ref_no'] . "</td>
                    <td>" . $WorkAssignListVal['candidate_roll_no'] . "</td>
                    <td>" . $WorkAssignListVal['first_name'] . "</td>
                    <td>" . $WorkAssignListVal['last_name'] . "</td>
                    <td>" . getClassName($m,$WorkAssignListVal['class_id']) . "</td>
                    <td>" . getSectionName($m,$WorkAssignListVal['section_id']) . "</td>
                    <td>" . $WorkAssignListVal['cell_no'] . "</td>
                    <td>" . $WorkAssignListVal['mother_cell'] . "</td>
                    <td>" . $WorkAssignListVal['challan_id'] . "</td>
                    <td>" . date("d-M-Y", strtotime($WorkAssignListVal['challan_duedate']));
                    if ($WorkAssignListVal['editDueDate'] == 1) {
                        echo "Ext";
                    }
                    echo "</td>
                    <td>" . ($WorkAssignListVal['total_challan_amt'] + $WorkAssignListVal['arrears'] + $student_fine) . "</td>
                    <td>" . $sumAmt[0]['receipt_numbers'] . "</td>
                    <td>" . $sumAmt[0]['rcvd'] . "</td>
                    <td>" . $sumsettleAmt[0]['settle_numbers'] . "</td>
                    <td>" . $sumsettleAmt[0]['settlmntAmt'] . "</td>
                    <td>" . ($WorkAssignListVal['total_challan_amt'] + $WorkAssignListVal['arrears'] + $student_fine - ($sumAmt[0]['rcvd'] + $sumsettleAmt[0]['settlmntAmt'])) . "</td>
    
                    <td>";
                    if ($balance == 0) {
                        echo "Clear";
                    } else {
                        echo "Pending";
                    }
                    echo "</td>
                  </tr>";
                  
            // Accumulate the settlement amount for the current class
            $invoiceAmount += $WorkAssignListVal['total_challan_amt'] + $WorkAssignListVal['arrears'] + $student_fine;
            $AmountReceived += $sumAmt[0]['rcvd'];
            $AmountSettle += $sumsettleAmt[0]['settlmntAmt'];
            $AmountBalance += ($WorkAssignListVal['total_challan_amt'] + $WorkAssignListVal['arrears'] + $student_fine - ($sumAmt[0]['rcvd'] + $sumsettleAmt[0]['settlmntAmt']));
        }

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

        // if ($_REQUEST['orderBy']=="GradeWise") {
                 
                  if ($currentClass !== null) {
            echo "<tr style='background-color:lightgray;'>
                    <td colspan='3' style='text-align:center;'>Total Record: $totalRecord</td>
                    <td colspan='8' style='text-align:center;'>Total</td>
                    <td>" . $invoiceAmount . "</td>
                    <td></td>
                    <td>" . $AmountReceived . "</td>
                    <td></td>
                    <td>" . $AmountSettle . "</td>
                    <td>" . $AmountBalance . "</td>
                    <td></td>
                  </tr>";
            // Update the overall total for the last class
            $overallTotalInvoiceAmount += $invoiceAmount;
            $overallTotalAmountReceived += $AmountReceived;
            $overallTotalAmountSettle += $AmountSettle;
            $overallTotalBalance += $AmountBalance;
        }

        //  }

       
         if ($_REQUEST['orderBy']=="SettlWise") {
            $totalInv=$totalSettellmetnAmount;
        } else {
            $totalInv=$overallTotalInvoiceAmount;
            $totalRec=$overallTotalAmountReceived;
            $totalStle=$overallTotalAmountSettle;
            $totalBal=$overallTotalBalance;
        }

         // Print the overall total
        echo "<tr style='background-color:lightgray;'>
                <td colspan='11' style='text-align:center;'>Overall Total</td>
                <td>" . $totalInv . "</td>
                <td></td>
                <td>" . $totalRec . "</td>
                <td></td>
                <td>" . $totalStle . "</td>
                <td>" . $totalBal . "</td>
                <td></td>
              </tr></tbody></table>";
        ?>
    </tbody>
</table>

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