<?php 
if(!isset($_SESSION)) { 
  session_start(); 
  include('../includes/dbcon.php');
}
if(empty($_SESSION['hrm_id'])):
header('Location:../index.php');
endif;

$edate=date("Y-m-d");
//////////////// Get Sequence Number ----------------------- 

$fdate=$edate;
$tdate=$edate;
$cus_id=0;
$report=0;

if(isset($_POST['SubmitButton'])){ //check if form was submitted
  $tdate = $_POST['tdate'];
  $cus_id = $_POST['cus_id'];
  $report=1;
}

if(isset($_POST['SubmitButton1'])){ //check if form was submitted
  $tdate = $_POST['tdate'];
  $cus_id = $_POST['cus_id'];
  $report=2;
}

//////////////////End of gettinh sequence Number ------------------
?>
<!DOCTYPE html>
<html lang="en">


<head>
  <!-- Required meta tags -->
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <title>SalesHub |Customer Outstanding </title>
  <!-- plugins:css -->
  <?php include('../includes/commonheader.php');?>



</head>
<body>
 <?php include('../includes/header.php');?>
 <?php include('../includes/leftsidebar.php');?>
           <!-- partial -->
 <div class="main-panel">        
        <div class="content-wrapper">

          <div class="page-header">
            <h3 class="page-title">
                Outstandings
            </h3>
          
          </div>
          <div class="col-22 grid-margin">
              <div class="card">
                <div class="card-body">
              
                          
    <form  method="post" >                   
                    <div class="row">                     
                      <div class="col-md-6">
                        <div class="form-group row">
                          <label class="col-sm-3 col-form-label">Customer: </label>
                          <div class="col-sm-9">
                             <select class="form-control select2" style="width: 100%;" name="cus_id" id="cus_id" required >
           <option value="0">All</option>  
          <?php            
         $querydoc=mysqli_query($con,"select *,CONCAT(`customer_master`.`cus_name`,', ',`customer_master`.`cus_code`) AS `cus`
          
          from customer_master  ")or die(mysqli_error());
          
          while($rowd=mysqli_fetch_array($querydoc)){
          ?>
          <option value="<?php echo $rowd['cus_id'];?>"><?php echo $rowd['cus'];?></option>        
          <?php }?>
          </select> 
                          </div>
                        </div>


                      </div>
                                              
                  <div class="col-md-6">
                        <div class="form-group row">
                          <label class="col-sm-3 col-form-label">As at Date: </label>
                          <div class="col-sm-8">
                            <input type="date" class="form-control" id="tdate" name="tdate" >

                          </div>
                        </div>

                    </div> <!-------End of first form raw------->
</div>
<table>
<tr>
                 <td>  <button type="submit"  name="SubmitButton"class="btn btn-primary">View</button></td>
                  <td>  <button type="submit"  name="SubmitButton1"class="btn btn-primary">Age Analysis</button></td>
                 <td>  <button class="btn btn-light">Cancel</button></td>
</tr>
</table>
                  </form>
                </div>
              </div>
            </div>

  <div class="content-wrapper">
          <div class="col-lg-22 grid-margin stretch-card">
              <div class="card">
                <div class="card-body">
                  <h6 > </h6>
                  <p class="card-description">
                    <button type="button" onclick="document.location='home.php'" class="btn btn-warning "><i class="fas fa-arrow-left"></i> Go Back                    
                    </button>
                  </p>
                  <p>Outstanding As at : <?php echo $tdate;  ?></p>
                  <div class="table-responsive">
                    <table class="table table-hover">
                      <thead>
                        <tr>
                          <th>#</th>
                          <th>Document</th>
                          <th>Date</th>
                          <th>Type</th>
                        
                          <th class="text-right">Amount</th>
                          <th class="text-right">O/S</th>
                          <th class="text-right">Excess</th>
                          <th class="text-right">Balance</th>                         
                        </tr>
                      </thead>
                      <tbody>
<?php
$rcount=0;
if ( $cus_id==0) {
$stm="SELECT *,IFNULL((SELECT SUM(applyamount)FROM sedetail
WHERE sedetail.docid=seheader.docid AND seheader.party_id=sedetail.party_id AND
 docid IN (SELECT docid FROM seheader WHERE cancel<>1 AND docdate<= ' $tdate' )
 AND Refdocno IN (SELECT docid FROM seheader AS sh2 WHERE cancel<>1 AND docdate<= ' $tdate' )
 ),0) 
  AS app,
  (seheader.amount-
  IFNULL((SELECT SUM(applyamount)FROM sedetail
WHERE sedetail.docid=seheader.docid AND seheader.party_id=sedetail.party_id AND
 docid IN (SELECT docid FROM seheader WHERE cancel<>1 AND docdate<= ' $tdate' )
 AND Refdocno IN (SELECT docid FROM seheader AS sh2 WHERE cancel<>1 AND docdate<= ' $tdate' )
 ),0) )
  AS bal
 FROM seheader
 LEFT JOIN customer_master ON seheader.party_id=customer_master.cus_id
 WHERE seheader.cancel<>1 and  ((seheader.amount-
  IFNULL((SELECT SUM(applyamount)FROM sedetail
WHERE sedetail.docid=seheader.docid AND seheader.party_id=sedetail.party_id AND
 docid IN (SELECT docid FROM seheader WHERE cancel<>1 AND docdate<= ' $tdate' )
 AND Refdocno IN (SELECT docid FROM seheader AS sh2 WHERE cancel<>1 AND docdate<= ' $tdate' )
 ),0) ))<>0

  order by seheader.party_id ,seheader.docid";
}


else {

$stm="SELECT *,IFNULL((SELECT SUM(applyamount)FROM sedetail
WHERE sedetail.docid=seheader.docid AND seheader.party_id=sedetail.party_id AND
 docid IN (SELECT docid FROM seheader WHERE cancel<>1 AND docdate<= ' $tdate' )
 AND Refdocno IN (SELECT docid FROM seheader AS sh2 WHERE cancel<>1 AND docdate<= ' $tdate' )
 ),0) 
  AS app,
  (seheader.amount-
  IFNULL((SELECT SUM(applyamount)FROM sedetail
WHERE sedetail.docid=seheader.docid AND seheader.party_id=sedetail.party_id AND
 docid IN (SELECT docid FROM seheader WHERE cancel<>1 AND docdate<= ' $tdate' )
 AND Refdocno IN (SELECT docid FROM seheader AS sh2 WHERE cancel<>1 AND docdate<= ' $tdate' )
 ),0) )
  AS bal
 FROM seheader
 LEFT JOIN customer_master ON seheader.party_id=customer_master.cus_id
 WHERE seheader.cancel<>1 and seheader.party_id=$cus_id and  ((seheader.amount-
  IFNULL((SELECT SUM(applyamount)FROM sedetail
WHERE sedetail.docid=seheader.docid AND seheader.party_id=sedetail.party_id AND
 docid IN (SELECT docid FROM seheader WHERE cancel<>1 AND docdate<= ' $tdate' )
 AND Refdocno IN (SELECT docid FROM seheader AS sh2 WHERE cancel<>1 AND docdate<= ' $tdate' )
 ),0) ))<>0

  order by seheader.party_id ,seheader.docid";


}




$query=mysqli_query($con,$stm)or die(mysqli_error()); 
$Final_outstandingTotal=0;
$previousCustomer = null;
$outstandingTotal = 0;
 while($row=mysqli_fetch_array($query)){   $rcount=$rcount+1;
                $bal=0;
                $excess=0;
                $docid=$row['docid'];
                $docdate=$row['docdate']; 
                $doctype=$row['doctype'];
                $amount=$row['amount'];
                $bal=$row['bal'];
                $cus_name=$row['cus_name'];
                $cus_id=$row['cus_id'];

              if ($bal>0){
                $os= $bal;
                $excess=0;
              }
              else {
                 $excess= $bal;
                  $os=0;
              }


              $Final_outstandingTotal=$Final_outstandingTotal + $bal;
               
              if ($row['cus_id'] != $previousCustomer) {   
                   if ($previousCustomer !== null) {  ?>
                    <tr>
                      <td></td>
                      <td></td>                     
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td align="right"><span style="font-weight:bold"><?php echo number_format($outstandingTotal,2);?></span></td>
                    </tr> 

                   <?php  }    
                   
                   ?>  

                    <tr>
                      <td></td>
                      <td><span style="font-weight:bold"><?php   echo $row['cus_name'];?></span></td>
                    </tr>
                <?php   

                 $outstandingTotal = 0;  
                 $previousCustomer = $row['cus_id']; 
          
                  

                      }   ////// end of  if ($row['cus_id'] != $previousCustomer) {  

$outstandingTotal += $row['bal']; 

?>   




             
   <tr>
                         <td><?php echo $rcount;  ?></td>
                         <td><a href="https://invox.lk/salessys/pages/invoice_duplicate.php?inv_no=<?php echo $docid; ?>" onclick="window.open(this.href, '_blank', 'width=800, height=600'); return false;"><?php echo $row['docid']; ?></a></td>
                         <td><?php echo  date("Y-m-d",strtotime( $row['docdate'])) ;?></td>
                           <td><?php echo $row['doctype'];?></td>          
                        
                         <td align="right"><?php echo $row['amount'];?></td>                         
                          <td align="right"><?php echo $os;?></td>
                           <td align="right"><?php echo $excess;?></td>
                           <td align="right"><?php echo $row['bal'];?></td>
              
                        

</tr>


 <?php }   

                  if ($previousCustomer !== null) {  ?> <tr>
                      <td></td>
                      
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td align="right"><span style="font-weight:bold"><?php echo number_format($outstandingTotal,2);?></span></td>
                    </tr>  
                  <?php }
                  
                  ?>
<tr>
                      <td></td>
                      
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td align="right"><span style="font-weight:bold"><?php echo number_format($Final_outstandingTotal,2);?></span></td>
                    </tr>  
                      </tbody>
                    </table>



        </div>  <!----- <div class="content-wrapper">------>
        </div>
          
          
  </div>

 </div><!-----End of main panel--~~~~~~~~~~~~~~~~~~~~~~~--------------->
 </div><!-----Dont Delete this DIV--~~~~~~~~~~~~~~~~~~~~~~~--------------->
</div>
  <!-- container-scroller -->
<?php include('../includes/footer.php');?>
<?php include('../includes/commonjs.php');?>
 
</body>


</html>

<script>
    $( document ).ready(function() {
         // alert("Please Select Order Type");

    });



</script>

 <!-- End custom js for this page-->
  <script>
  $(function () {
    //Initialize Select2 Elements
    $('.select2').select2()

    //Initialize Select2 Elements
    $('.select2bs4').select2({
      theme: 'bootstrap4'
    })
  })

</script>



