<?php

 

 include('db_con.php');

 include('function.php');

 

  if (isset($_SESSION['user_id'])) {

    $user_type = $_SESSION['user_type'];

    $user_id = $_SESSION['user_id'];

    $user_name = $_SESSION['user_name'];

    $branch_id = $_SESSION['branch_id'];

    $branch_name = $_SESSION['branch_name'];

    $store_id = $_SESSION['store_id'];

    $store_name = $_SESSION['store_name'];

    $user_acc_no = $_SESSION['user_acc_no'];

}else{

  header('Location: login');

  die();

}

 $html = '';

 $dated  = date('Y-m-d');

 $count = 0;

        accounts_update($connect);

$parent_acc_no = 101;

$fdate = $_GET["fdate"];

$tdate = $_GET["tdate"];

$balance = 0;

$tot_debit = 0;

$tot_credit = 0;

  

     if($user_type=='admin')

     {

      $statement = $connect->prepare("select a.acc_no,a.acc_name from accounts a where a.acc_parent = 101

  ");

     }    else

     {





  $statement = $connect->prepare("select a.acc_no,a.acc_name from accounts a where a.acc_no = '".$user_acc_no."'

  ");

  }

 $statement->execute();

 $result = $statement->fetchAll(); 

?>

<!DOCTYPE html>

<html>

<head>

  <title>

  

  </title>

  <style>
    * {
      font-family: arial;
      font-size: 13px;
    }

  .rightside {text-align: right;}

  table td{

    

    border-bottom: 1px solid gray;

  }

  .head_row {

    font-weight: bold;

  }

  thead td {

    font-weight: bold;

     border-bottom: 1px solid gray;

      border-top: 1px solid gray;

       background-color: :  gray;

  }

  </style>

</head>

<body>

 

  

  <table id="main" width="100%">

  

  <thead>

<tr>

  <td width="3%">Sr.</td>

  <td width="12%">Date/Name</td>

  <td width="12%">Source</td>

   

  <td width="38%">Description</td>

  

  <td width="10%">Receipt</td>

  <td width="10%">Payment</td>

  <td width="15%">Balance</td>

  

</tr>

  </thead>

  

<tbody>

  <?php

  $opening = 0;

foreach($result as $row)

 {

$acc_no = $row["acc_no"];

  $opening_statement = $connect->prepare("select sum(d.debit-d.credit) from jv_detail d, jv m where m.jv_id=d.jv_id and m.dated < '".$fdate."' and d.acc_no='".$acc_no."'");

         $opening_statement->execute(

                   );

         $opening = $opening_statement->fetchColumn();

   $count = $count+1;

          $detail_statement = $connect->prepare("select m.dated, a.acc_name,m.source,m.ref_id,

          CASE WHEN m.source ='AR Inv' THEN (select CONCAT(a.acc_name,' -- ',s.description,' -- Cash Sale') from accounts a,sale s where s.s_id = m.ref_id and s.cust_id=a.acc_no)

           WHEN m.source ='AR InvR' THEN (select CONCAT(a.acc_name,' -- ',s.description,' -- Cash Sale Return') from accounts a,sr s where s.sr_id = m.ref_id and s.cust_id=a.acc_no)

      WHEN m.source ='AR Pay' THEN (select CONCAT(a.acc_name,' -- ',r.description, ' -- Received') from accounts a,rec_voucher r where r.rec_id = m.ref_id and r.cust_id=a.acc_no)

       WHEN m.source ='AP Pay' THEN (select CONCAT(a.acc_name,' -- ',r.description,' -- Pyament') from accounts a,pay_voucher r where r.pay_id = m.ref_id and r.v_id=a.acc_no) END AS description,

           d.debit,d.credit from jv m, jv_detail d, accounts a where m.jv_id=d.jv_id and a.acc_no=d.acc_no and d.acc_no= '".$acc_no."' and m.dated between '".$fdate."' and '".$tdate."' order by dated");

         $detail_statement->execute(

                   );

         $detail_result = $detail_statement->fetchAll();

         

 

  // if($count == 1){$balance = $opening + $debit - $credit; }else{$balance = $balance + $debit - $credit;}

   

 

  echo '

<tr>

<td width="5%">'.$count.'</td>

  

<td colspan="5" width="80%">'.$row["acc_name"].'</td>

<td width="15%">Opening : '.$opening.'</td>

</tr>

</b>

  ';

  

   $sr = 0;

   //$tot_amount = 0;

   //$tot_qty = 0;

   //$tot_dis_amount = 0;

                foreach($detail_result as $detail_row)

                  {

                      $sr = $sr+1;

                     

                      $debit = $detail_row["debit"];

                      $credit = $detail_row["credit"];

                      $tot_debit = $tot_debit + $debit;

                      $tot_credit = $tot_credit + $credit;

                      if($sr == 1){$balance = $opening + $debit - $credit; }else{$balance = $balance + $debit - $credit;}

                      

                      //$tot_amount = $tot_amount + $amount;

                     // $tot_qty = $tot_qty + $qty;

                     // $tot_dis_amount = $tot_dis_amount + $dis_amount;

echo '

                      <tr>

                       

                      

                      

                      <td width="3%">'.$sr.'</td>

                      

                      <td width="12%">'.$detail_row['dated'].'</td>

                      <td width="12%">'.$detail_row['source'].''.$detail_row['ref_id'].'</td>

                      <td width="38%">'.$detail_row['description'].'</td>

    <td width="10%" class="rightside">'.number_format($debit, 2).'</td>

    <td width="10%" class="rightside">'.number_format($credit, 2).'</td>

    <td width="15%" class="rightside">'.number_format($balance, 2).'</td>

  

   

    

   </tr>

   ';

                  }

                  echo '

                 

        <tr>

                       

                      

                      

                      

                      <td colspan="4" width="65%">Total :</td>

    <td width="10%" class="rightside">'.number_format($tot_debit, 2).'</td>

    <td width="10%" class="rightside">'.number_format($tot_credit, 2).'</td>

    <td width="15%" class="rightside">'.number_format($balance, 2).'</td>

  

   

    

   </tr>     ';    

                  

}

?>

</tbody>

<tfoot>

  

  

</tfoot>

 

 </table><!--end main table-->

    

  

 </body>

 </html>