<?php include("parent/callMe.php"); ?>
<?php  include("sessioncheck.php"); ?>

<?php


     $branch=$_SESSION['branch'];
     
     if (isset($_REQUEST['from'])) {
         
     $from1=date("Y-m-d",strtotime($_REQUEST['from'])); 
     $to1=date("Y-m-d",strtotime($_REQUEST['to']));

     } else {

     $from1=date("Y-m-d"); 
     $to1=date("Y-m-d");

     }
     


   $session;

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

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


     /* 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, emc.challan_id,emc.challan_created
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['from'] != "" && $_REQUEST['from'] != NULL) {
    $from = date("Y-m-d", strtotime($_REQUEST['from']));

} else {
    $from = date("Y-m-01");
}

if ($_REQUEST['to'] != "" && $_REQUEST['to'] != NULL) {
    $to = date("Y-m-d", strtotime($_REQUEST['to']));


} else {
    $to = date("Y-m-t");
}
      
    
    //$sql .= " AND challan_created BETWEEN '" .$from. "' AND '" .$to. "'";

$sql .= " AND DATE(emc.challan_created) BETWEEN '" . $from . "' AND '" . $to . "'";
$sql .= " GROUP BY esd.student_id";
        if(!isset($_REQUEST['orderBy'])){
    
        $sql .= " ORDER BY esd.class_id ASC, esd.section_id ASC, CAST(esd.ref_no AS UNSIGNED) ASC";

        }

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

        $sql .= " ORDER BY esd.class_id, esd.section_id ASC";

        }



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

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

        }


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

        $sql .= " ORDER BY  
          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 lang="en">
  <head>
   
    <title> Fee Invoice Summary </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; 
          
      Fee Invoice Summary Report

      <br><br>

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



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



      <form class="" action="fee_invoice_summary.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="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"> From Date</label>
                              <input name="from"  id="from" value="<?php echo $from1;?>" type="date" class="form-control"  >
                       
                          </div>
                  </div>                     
             

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

                       <div class="form-group ">

                          <label class="required"> To Date</label>
                             <input name="to" id="to" value="<?php echo $to1;?>" type="date" class="form-control"  >
                       
                       </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=""> Order By</label>
        <select name="orderBy" id="orderBy" class="form-control" >
       
       <option <?php if($_REQUEST['orderBy']=='AdmNoWise'){echo 'selected';}?>  value="AdmNoWise">Admission NO Wise</option>
       <option <?php if($_REQUEST['orderBy']=='GradeWise'){echo 'selected';}?>  value="GradeWise">Grade Wise</option>
       <option  <?php if($_REQUEST['orderBy']=='RollNoWise'){echo 'selected';}?> value="RollNoWise">Roll No 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>
        <a style="" href="print_fee_invoice_summary.php?session=d702958bebc5b126fcc2ecba847749b7&class_id=<?php echo $_REQUEST['class_id']; ?>&section=<?php echo $_REQUEST['section']; ?>&from=<?php echo $_REQUEST['from']; ?>&to=<?php echo $_REQUEST['to']; ?>&std_status=<?php echo $_REQUEST['std_status']; ?>&orderBy=<?php echo $_REQUEST['orderBy']; ?>" 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 colspan="6" style="text-align:center;"></th>            
                <th colspan="3" style="text-align:center;"> Profile </th>
                <th colspan="8" style="text-align:center;"> Invoice </th>
          
                                
            </tr>
            <tr style="background-color: #042954; color:white;">

                <th>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/Section </th>
                <th width="8%" style="text-align:center;"> Adm Date </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;"> 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;"> Prspcts </th>                
                <th width="8%" style="text-align:center;"> Fine </th>
                <th width="8%" style="text-align:center;"> O.Charges </th>
                <th width="8%" style="text-align:center;"> Total </th>
          
                                
            </tr>
        </thead>
        <tbody>

     <?php 

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

        $std=$WorkAssignListVal['student_id'];       
        $m->where("student_id", $WorkAssignListVal['student_id']);
        $profileFee=$m->get("eduportal_student_fee_structure");

    $c_id=$WorkAssignListVal['challan_id'];
    $sq = "SELECT     
    SUM(CASE WHEN dt.feetype_id = 1 THEN dt.fee_amount ELSE 0 END) AS monthly,
    SUM(CASE WHEN dt.feetype_id = 5 THEN dt.fee_amount ELSE 0 END) AS Afund,
    SUM(CASE WHEN dt.feetype_id = 4 THEN dt.fee_amount ELSE 0 END) AS Fine,
    SUM(CASE WHEN dt.feetype_id = 6 THEN dt.fee_amount ELSE 0 END) AS Reg,
    SUM(CASE WHEN dt.feetype_id = 2 THEN dt.fee_amount ELSE 0 END) AS Adm,
    SUM(CASE WHEN dt.feetype_id = 3 THEN dt.fee_amount ELSE 0 END) AS prscts,
    SUM(CASE WHEN dt.feetype_id NOT IN (1, 5, 4, 6, 2, 3) THEN dt.fee_amount ELSE 0 END) AS Other 
    FROM eduportal_monthly_challan_details AS dt
    INNER JOIN eduportal_monthly_challan AS ch ON ch.challan_id = dt.challan_id
    WHERE ch.branch_id = '$branch' AND ch.student_id = '$std' AND DATE(ch.challan_created) BETWEEN '$from' AND '$to' ";  
    $sumAmt = $m->query($sq);

        $t_s_reg+=$profileFee[0]['std_registration_fee'];
        $t_adm+=$profileFee[0]['standared_admission'];
        $t_tut+=$profileFee[0]['standared_tution_fee'];
        $t_reg+=$sumAmt[0]['Reg'];
        $t_adm+=$sumAmt[0]['Adm'];
        $t_monthly+=$sumAmt[0]['monthly'];
        $t_a_fnd+=$sumAmt[0]['Afund'];
        $t_prspcts+=$sumAmt[0]['prscts'];
        $t_fine+=$sumAmt[0]['Fine'];
        $t_othr+=$sumAmt[0]['Other'];
        $t_total+=$sumAmt[0]['Reg']+$sumAmt[0]['Adm']+$sumAmt[0]['monthly']+$sumAmt[0]['Afund']+$sumAmt[0]['prscts']+$sumAmt[0]['Fine']+$sumAmt[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']);?>/<?php echo getSectionName($m,$WorkAssignListVal['section_id']);?></td>
<td><?php if ($WorkAssignListVal['date_of_adms']!=NULL) {
    echo date("d-M-Y", strtotime($WorkAssignListVal['date_of_adms']));
} else echo "--"; ?></td>

<td><?php echo $profileFee[0]['std_registration_fee'];?></td>
<td><?php echo $profileFee[0]['standared_admission'];?></td>
<td><?php echo $profileFee[0]['standared_tution_fee'];?></td>
<td><?php echo $sumAmt[0]['Reg'];?></td>
<td><?php echo $sumAmt[0]['Adm'];?></td>
<td><?php echo $sumAmt[0]['monthly'];?></td>
<td><?php echo $sumAmt[0]['Afund'];?></td>
<td><?php echo $sumAmt[0]['prscts'];?></td>
<td><?php echo $sumAmt[0]['Fine'];?></td>
<td><?php echo $sumAmt[0]['Other'];?></td>
<td><?php echo $sumAmt[0]['Reg']+$sumAmt[0]['Adm']+$sumAmt[0]['monthly']+$sumAmt[0]['Afund']+$sumAmt[0]['prscts']+$sumAmt[0]['Fine']+$sumAmt[0]['Other'];?></td>

</tr> <?php   } ?>
<!-- 
<tr style="background-color: #042954; color:white;">
    <th><?php echo $count+1;?></th>
    <th></th>
    <th></th>
    <th></th>
    <th></th>
    <th>Total</th>
    <th><?php echo $t_s_reg;?></th>
    <th><?php echo $t_adm;?></th>
    <th><?php echo $t_tut;?></th>
    <th><?php echo $t_reg;?></th>
    <th><?php echo $t_adm;?></th>
    <th><?php echo $t_monthly;?></th>
    <th><?php echo $t_a_fnd;?></th>
    <th><?php echo $t_prspcts;?></th>
    <th><?php echo $t_fine;?></th>
    <th><?php echo $t_othr;?></th>
    <th><?php echo $t_total;?></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');?> 
   <!-- 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>
