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



// $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']. "' ";

//     }       

//      }    
//   echo $sql;

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

 
 

 ?>

<!DOCTYPE html>
<html lang="en">
  <head>
   
     <title> Outstanding List | EduPortal </title>
     <?php include('RootMe/css_links.php');?> 

 <link href="https://cdn.datatables.net/1.10.25/css/dataTables.bootstrap4.min.css" rel="stylesheet">

  </head>
  
 <script type="text/javascript">


function getsecall(str)

{



   if(str=="")

      alert("Please select any Class");

  else

     var ajx;  // The variable that makes Ajax possible!

  

  if(window.XMLHttpRequest)

  ajx=new XMLHttpRequest();

  ajx.onreadystatechange = function()

  {



    if(ajx.readyState == 4)

    {

          // alert(ajx.responseText); 

         document.getElementById("txt_section").innerHTML= ajx.responseText;

    }

  }

  ajx.open("GET", "get_section_all.php?class_id="+str);

  ajx.send(); 



}

  </script>

  <body class="nav-md">
    <div class="container body">
      <div class="main_container">
        <div class="col-md-3 left_col">
     
            <?php include('RootMe/sidebar.php');?>

              <?php include('RootMe/header.php');?>
     
        <!-- page content -->
            <div class="right_col" role="main">
            <div class="row" style="background-color: white;">
  
   <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; 
          
      Outstanding Fee Report

      <br><br>

<section class="add_sub_agent" id="table_sub_agent">
  <div class="container">



 <div class="" style="margin-top:10px;">



            <form class="" action="outstanding_fee.php" enctype="multipart/form-data" method="get">
        <div class="panel panel-default">
          <div class="panel-heading"><?php //echo count($WorkAssignListings);?> <!-- Students Records --> <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="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-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 ($month!=NULL) {
                    echo $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=""> 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-2 col-xs-12" style="padding:8px;" >

       <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-2 col-xs-12" style="padding:8px;" >

       <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>
        <?php if (isset($_REQUEST['class_id'])) { ?>
         <a style="" href="print_outstanding_fee.php?kldjasfihasdfdsfjkdfshenkcihasndfnsadcihasdnfsandchsdcaksfnoicdh&class_id=<?php echo $_REQUEST['class_id'];?>&section=<?php echo $_REQUEST['section'];?>&month=<?php echo $_REQUEST['month'];?>&std_status=<?php echo $_REQUEST['std_status'];?>&fee_status=<?php echo $_REQUEST['fee_status'];?>&due_date=<?php echo $_REQUEST['due_date'];?>" class="btn btn-primary">Print</a>
        <?php } ?>
         
      
        </div>

        </div>

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

     
          


 
<table class="table table-bordered table-striped" id="myTable">
   
        <thead>
           <tr style="background-color: #042954; color: white;">

              
                <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="8%" style="text-align:center;"> Invc Amt</th>
                <th width="8%" style="text-align:center;"> Recip#</th>
                <th width="8%"> Amt Recv</th>
                <th width="8%"> Settle#</th>                
                <th width="8%"> Amt Setl</th>
                <th width="8%"> Balance</th>
                <th width="8%" style="text-align:center;"> Status </th>
          
                                
            </tr>
        </thead>
        <tbody>

     <?php 

      $count=1;
      foreach($WorkAssignListings as $WorkAssignListVal)
      { 

          $c_id=$WorkAssignListVal['challan_id'];
        // $sq = "SELECT SUM(received) AS rcvd, receipt_no  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'] + $WorkAssignListVal['arrears'] + $student_fine)-($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

//         $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']);

//         }
//       //  echo $WorkAssignListVal['challan_duedate']."==".$WorkAssignListVal['challan_validity_date']."<br>";
       
//         $datediffrence =($end_date - $start_date)/60/60/24;
// echo $datediffrence."==<br>";
//         if($brid[0]['fine_type']=="0" || $brid[0]['fine_type']==0){
//         $student_fine= $brid[0]['late_fee_fine'];
//         } else {
//         $student_fine= ($datediffrencenewchallan * $brid[0]['late_fee_fine']);
//         }


//             if($WorkAssignListVal['paid_status']==1){            

//             $Acutal_student_fine=$getFineAmountReceived[0]['fee_amount'];
           
//             }

//             // if row is first and fee is not paid then calculate fine

//             else if($WorkAssignListVal['paid_status']==0){
            
//             $Acutal_student_fine=$student_fine;

//             }



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'] + $WorkAssignListVal['arrears'] + $student_fine)-($sumAmt[0]['rcvd']+$sumsettleAmt[0]['settlmntAmt']);

      ?>

<tr>
<td><?php echo $count++;?></td>
<td><?php echo $WorkAssignListVal['ref_no'];?></td>
<td><?php echo $WorkAssignListVal['candidate_roll_no'];?></td>
<td><?php echo $WorkAssignListVal['first_name'];?></td>
<td><?php echo $WorkAssignListVal['last_name'];?></td>
<td><?php echo getClassName($m,$WorkAssignListVal['class_id']);?></td>
<td><?php echo getSectionName($m,$WorkAssignListVal['section_id']);?></td>
<td><?php echo $WorkAssignListVal['cell_no'];?></td>
<td><?php echo $WorkAssignListVal['mother_cell'];?></td>
<td><?php echo $WorkAssignListVal['challan_id'];?></td>
<td><?php echo date("d-M-Y", strtotime($WorkAssignListVal['challan_duedate'])); if ($WorkAssignListVal['editDueDate']==1) {
  echo "Ext";
} ?></td>
<td><?php echo $WorkAssignListVal['total_challan_amt'] + $WorkAssignListVal['arrears'] + $student_fine;?></td>
<td><?php echo $sumAmt[0]['receipt_numbers'];?></td>
<td><?php echo $sumAmt[0]['rcvd'];?></td>
<td><?php echo $sumsettleAmt[0]['settle_numbers'];?></td>
<td><?php echo $sumsettleAmt[0]['settlmntAmt'];?></td>
<td><?php echo ($WorkAssignListVal['total_challan_amt'] + $WorkAssignListVal['arrears'] + $student_fine)-($sumAmt[0]['rcvd']+$sumsettleAmt[0]['settlmntAmt']);?></t>
<td><?php if ($balance==0) {
    echo "Clear";
} else { echo "Pending";} ?></td>

</tr> <?php   } ?>


<!-- <tr>
  
  <td colspan="10">total</td>
  <td><?php echo $t_total;?></td>
  <td><?php echo $t_rcvd;?></td>
  <td><?php echo $t_stl;?></td>
  <td><?php echo $t_balance;?></td>
  <td></td>
</tr> -->
 
        </tbody>
        
    </table>



    <br><br>


  </div>
  <div class="clearfix"></div>
</section>

        </div> 
        </div>


            </div>
          </div>
        </div>


     
        <!-- /page content -->


 <!-- jQuery -->
    <script src="js/ajax_functions.js"></script>
  <?php include('RootMe/js_linksscript.php');?> 
  <!-- <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script> -->
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.25/js/dataTables.bootstrap4.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.print.min.js"></script>

    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.7.1/css/buttons.bootstrap4.min.css">

    <script>
        $(document).ready(function() {
            var table = $('#myTable').DataTable({
                dom: 'Bfrtip',
                buttons: [
                    'excel'
                ]
            });
        });
    </script>


  </body>
</html>
