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

     $branch=$_SESSION['branch'];
     
     $classes = $m->query("select * from edu_class_config where branch_id=".$branch." order by class_id asc limit 1");
     // print_r($classes);    


   $session;

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

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


     /* code for advance Search*/


$sql = "SELECT * FROM  eduportal_challan_fee_settlement  WHERE branch_id='$branch' ";

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

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

    }       

     }

    // if($_REQUEST['settlement_no']==""){
    // $sql;
    // } else {
    // $sql .= " AND settellment_no = '" .$_REQUEST['settlement_no']. "' ";

    // } 

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

     }
    
      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 settellment_date BETWEEN '" .$from. "' AND '" .$to. "'";
    
      if($_REQUEST['orderBy']=='GradeWise'){

      $sql .= "ORDER BY CAST(class_id AS UNSIGNED) ASC, CAST(settellment_no AS UNSIGNED) ASC";


      }



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

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

      }



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

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

      }


  $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 Settlement Report</title>
<link rel="icon" type="image/png" href="img/favicon.png" sizes="32x32" />
<script type="text/javascript">
// window.print();
</script>
<style type="text/css">

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;}

</style>

</head>
<body style="font-size:12px;">


<br>

<div style="text-align:center;font-size:30px;font-weight:bold;"> Settlement Report </div>

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

<div style="width:33%;float:left;font-size:16px;">  <b> <!-- Class Name: --></b> 

<?php //echo $class[0]['Class_Name'];?> 

</div>

<div style="width:33%; float:left;font-size:20px; "> <b> Report Date:</b> <?php echo date("d-M",strtotime($_REQUEST['from']));?> to <?php echo date("d-M",strtotime($_REQUEST['to']));?> </div>

<!-- <div style="width:33%; float:right;font-size:20px; "> <a style="padding: 6px 8px; font-size:14px;float: right;" target="_blank" href="export_excel_expense_report.php?<?php echo session_id().session_id().session_id().session_id().session_id();?>&from=<?php echo $_REQUEST['from'] ;?>&to=<?php echo $_REQUEST['to'] ;?>&PiadBy=<?php echo $_REQUEST['PiadBy'] ;?>&category=<?php echo $_REQUEST['category'] ;?>" style="cursor:pointer;"  class="btn btn-success" class="btn btn-success  fa fa-download pull-right"> Export Excel </a> </div> -->


<?php
// Initialize variables to store class-wise total and overall total
$overallTotal = 0;
$currentUser = null;
$classTotal = 0;
$count=1;
?>

<table style="width:100%;" cellspacing="0">
    <thead>
        <tr>
            <th width="3%">S.No</th>
            <th width="4%">Settl#</th>
            <th width="8%">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;">Invc#</th>
            <th width="8%" style="text-align:center;">Invc Mon</th>
            <th width="8%" style="text-align:center;">Fee</th>
            <th width="8%" style="text-align:center;">Amount</th>
            <th width="8%">Date/Time</th>
            <th width="8%">Settled By</th>
            <th width="8%" style="text-align:center;">Remarks</th>
        </tr>
    </thead>
    <tbody>
        <?php
        // Loop through the query results
        foreach ($WorkAssignListings as $WorkAssignListVal) {

            if ($WorkAssignListVal['settellment_amount']==0) {
            continue;
            }

          $m->where("class_id", $WorkAssignListVal['class_id']);
          $class=$m->get("edu_class_config");

          $m->where("feetype_id", $WorkAssignListVal['feetypeid']);
          $feeTypeName=$m->get("eduportal_fee_types");

          $m->where("user_id", $WorkAssignListVal['user_id']);
          $userName=$m->get("edu_user_config");

          // get Invoice Month
          $c_ids=$WorkAssignListVal['against_challan_id'];
          $getChl = "select from_year_month from eduportal_monthly_challan where challan_id='$c_ids' AND branch_id='$branch' "; 
          $getChlMonth = $m->query($getChl);

          // 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']);
          $totalSettellmetnAmount+=$WorkAssignListVal['settellment_amount'];



            // Check if the class has changed, print the class-wise total, and update the overall total
            if ($currentUser !== $WorkAssignListVal['user_id']) {
                if ($currentUser !== null) {
                    // Print the class-wise total
                    echo "<tr style='background-color:lightgray;'>
                            <td colspan='10' style='text-align:center;'>Total</td>
                            <td>" . $classTotal . "</td>
                            <td colspan='3'></td>
                          </tr>";
                    // Update the overall total
                    $overallTotal += $classTotal;
                }

                // Reset the class-wise total for the new class
                $classTotal = 0;

                // Print the heading for the new class
                echo "<tr style='background-color:lightblue;'>
                        <td colspan='14' style='text-align:center;'><b>User Name: " . $userName[0]['user_fname'] . "</b></td>
                      </tr>";
                $currentUser = $WorkAssignListVal['user_id'];
            }
$monthYear=date("M-y", strtotime($getChlMonth[0]['from_year_month']));
            // Process the current record, you can display the data as needed
            echo "<tr>
                    <td>" . $count++ . "</td>
                    <td>" . $WorkAssignListVal['settellment_no'] . "</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['class_id']) . "</td>
                    <td>" . getSectionName($m,$getStudentData[0]['section_id']) . "</td>
                    <td>" . $WorkAssignListVal['against_challan_id'] . "</td>
                    <td>" . $monthYear . "</td>
                    <td>" . $feeTypeName[0]['feetype_name'].'('. substr(getMonthName($m,$toMonthYear[1]), 0, 3).'-'.$toMonthYear[0].')' . "</td>
                    <td>" . $WorkAssignListVal['settellment_amount'] . "</td>
                    <td>" . date('d-M-Y', strtotime($WorkAssignListVal['settellment_date'])).'/ <small>'.$WorkAssignListVal['settellment_time'].'</small>' . "</td>
                    <td>" . $userName[0]['user_fname'] . "</td>
                    <td>" . $WorkAssignListVal['settellment_remarks'] . "</td>
                  </tr>";
                  
            // Accumulate the settlement amount for the current class
            $classTotal += $WorkAssignListVal['settellment_amount'];
        }

        // Print the total settlement for the last class in the result set
        if ($currentUser !== null) {
            echo "<tr style='background-color:lightgray;'>
                    <td colspan='10' style='text-align:center;'>Total</td>
                    <td>" . $classTotal . "</td>
                    <td colspan='3'></td>
                  </tr>";
            // Update the overall total for the last class
            $overallTotal += $classTotal;
        }

        // Print the overall total
        echo "<tr style='background-color:lightgray;'>
                <td colspan='10' style='text-align:center;'>Overall Total</td>
                <td>" . $overallTotal . "</td>
                <td colspan='3'></td>
              </tr></tbody></table>";
        ?>
    </tbody>
</table>

</body>
</html>