<?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.last_name, esd.ref_no, esd.candidate_roll_no, esd.class_id, esd.section_id,esd.date_of_adms, esd.cell_no, esd.mother_cell, emc.challan_id, emc.classs_id, emc.challan_duedate, emc.total_challan_amt, emc.editDueDate, emc.challan_validity_date, 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 ASC, esd.section_id ASC, CAST(esd.ref_no 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> Student Receivable Reports </title>
     <?php include('RootMe/css_links.php');?> 


   <link href="https://cdn.datatables.net/1.10.25/css/dataTables.bootstrap4.min.css" rel="stylesheet">
    <!-- <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet"> -->


  </head>
  

  <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; 
          
     Students Receivable Report

      <br><br>

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



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

<form class="" action="student_receivable.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=""> 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>
        <a style="" href="print_student_receivable.php?session=d702958bebc5b126fcc2ecba847749b7d702958bebc5b126fcc2ecba847749b7d702958bebc5b126fcc2ecba847749b7d702958bebc5b126fcc2ecba847749b7d702958bebc5b126fcc2ecba847749b7&class_id=<?php echo $_REQUEST['class_id'];?>&section=<?php echo $_REQUEST['section'];?>&month=<?php echo $_REQUEST['month'];?>&std_status=<?php echo $_REQUEST['std_status'];?>&due_date=<?php echo $_REQUEST['due_date'];?>" class="btn btn-primary">Print</a>
      
        </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="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;"> F.Cell </th>
                <th width="8%" style="text-align:center;"> M.Cell </th>
                <th width="8%" style="text-align:center;"> Due Date </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.Chrgs </th>
                <th width="8%" style="text-align:center;"> T.Recvbl </th>
          
                                
            </tr>
        </thead>
        <tbody>

     <?php 

      $count=1;
      foreach($WorkAssignListings as $WorkAssignListVal)
      { 
       
        
        $c_id=$WorkAssignListVal['challan_id'];
        $sq = "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 NOT IN (1, 5, 4) THEN fee_amount ELSE 0 END) AS Other FROM  eduportal_monthly_challan_details  WHERE branch_id='$branch' and challan_id='$c_id' ";        
        $sumAmt = $m->query($sq);


        $sqR = "SELECT SUM(CASE WHEN feetypeid = 1 THEN received ELSE 0 END) AS monthly, SUM(CASE WHEN feetypeid = 5 THEN received ELSE 0 END) AS Afund, SUM(CASE WHEN feetypeid = 4 THEN received ELSE 0 END) AS Fine, SUM(CASE WHEN feetypeid NOT IN (1, 5, 4) THEN received ELSE 0 END) AS Other FROM  eduportal_challan_fee_receiving  WHERE branch_id='$branch' and against_challan_id='$c_id' ";        
        $sumAmtRec = $m->query($sqR);


        $sqS = "SELECT SUM(CASE WHEN feetypeid = 1 THEN settellment_amount ELSE 0 END) AS monthly, SUM(CASE WHEN feetypeid = 5 THEN settellment_amount ELSE 0 END) AS Afund, SUM(CASE WHEN feetypeid = 4 THEN settellment_amount ELSE 0 END) AS Fine, SUM(CASE WHEN feetypeid NOT IN (1, 5, 4) THEN settellment_amount ELSE 0 END) AS Other FROM  eduportal_challan_fee_settlement  WHERE branch_id='$branch' and against_challan_id='$c_id' ";               
        $sumAmtStle = $m->query($sqS);


        $sqA = "SELECT SUM(CASE WHEN feetype_id = 1 THEN arrears_amount ELSE 0 END) AS monthly, SUM(CASE WHEN feetype_id = 5 THEN arrears_amount ELSE 0 END) AS Afund, SUM(CASE WHEN feetype_id = 4 THEN arrears_amount ELSE 0 END) AS Fine, SUM(CASE WHEN feetype_id NOT IN (1, 5, 4) THEN arrears_amount ELSE 0 END) AS Other FROM  eduportal_monthly_challan_arrears  WHERE branch_id='$branch' and against_challan_id='$c_id' ";                
        $sumAmtArr = $m->query($sqA);



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


        if (isset($_REQUEST['section']) && $_REQUEST['section'] === 'All') {

        } else {

        if (isset($_REQUEST['section']) && $WorkAssignListVal['section_id']!=$_REQUEST['section']) {
          continue;
        }

        }

        $t_month+=$sumAmt[0]['monthly']-$sumAmtRec[0]['monthly']-$sumAmtStle[0]['monthly'];
        $t_Afund+=$sumAmt[0]['Afund']-$sumAmtRec[0]['Afund']-$sumAmtStle[0]['Afund'];
        $t_Fine+=$student_fine;
        $t_other+=$sumAmt[0]['Other']-$sumAmtRec[0]['Other']-$sumAmtStle[0]['Other'];

        $t_recevalbe+=$sumAmt[0]['monthly']+$sumAmt[0]['Afund']+$sumAmt[0]['Fine']+$sumAmt[0]['Other'];
        $t_recevd+=$sumAmtRec[0]['monthly']+$sumAmtRec[0]['Afund']+$sumAmtRec[0]['Fine']+$sumAmtRec[0]['Other'];
      ?>

<tr>
<td><?php echo $count++;?></td>
<td><?php echo $WorkAssignListVal['ref_no']."/".$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 date("d-M-Y", strtotime($WorkAssignListVal['challan_duedate'])); if ($WorkAssignListVal['editDueDate']==1) {
  echo " Ext";
} ?></td>
<td><?php echo ($sumAmt[0]['monthly']+$sumAmtArr[0]['monthly'])-($sumAmtRec[0]['monthly']+$sumAmtStle[0]['monthly']);?></td>
<td><?php echo ($sumAmt[0]['Afund']+$sumAmtArr[0]['Afund'])-($sumAmtRec[0]['Afund']+$sumAmtStle[0]['Afund']);?></td>
<td><?php echo $student_fine;?></td>
<td><?php echo ($sumAmt[0]['Other']+$sumAmtArr[0]['Other'])-($sumAmtRec[0]['Other']+$sumAmtStle[0]['Other']);?></td>
<td><?php echo ($sumAmt[0]['monthly']+$sumAmtArr[0]['monthly']+$sumAmt[0]['Afund']+$sumAmtArr[0]['Afund']+$student_fine+$sumAmt[0]['Other']+$sumAmtArr[0]['Other'])-($sumAmtRec[0]['monthly']+$sumAmtRec[0]['Afund']+$sumAmtRec[0]['Other']+$sumAmtStle[0]['monthly']+$sumAmtStle[0]['Afund']+$sumAmtStle[0]['Other']);?></t>

</tr> <?php   } ?>

<!-- <tr style="background-color: #042954; color: white;">
  <th><?php echo $count+1;?></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th></th>
  <th>Total</th>
  <th><?php echo $t_month;?></th>
  <th><?php echo $t_Afund;?></th>
  <th><?php echo $t_Fine;?></th>
  <th><?php echo $t_other;?></th>
  <th><?php echo $t_recevalbe-$t_recevd;?></th>
</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>
