<?php
//require_once 'pdf.php';
include('db_con.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 = $_GET['dated'];
$today = date('Y-m-d');   

$sql = "select ifnull(sum(d.qty*d.rate),0) - m.discount + m.freight amount from sale_detail d, sale m, accounts a where m.s_id=d.s_id and m.inv_type = 'Cash' and m.dated < '".$dated."'";
$opening_cash_sales = $connect->query($sql);
$fetch_opening_cash_sales = $opening_cash_sales->fetchColumn();

$sql = "select ifnull(sum(d.qty*d.rate),0) - m.discount + m.freight amount from sale_detail d, sale m, accounts a where m.s_id=d.s_id and m.inv_type = 'Credit' and m.dated < '".$dated."'";
$opening_credit_sales = $connect->query($sql);
$fetch_opening_credit_sales = $opening_credit_sales->fetchColumn(); 

$opening_sales = $fetch_opening_credit_sales + $fetch_opening_cash_sales; 

$sql = "select m.s_id,m.inv_no,m.description,a.acc_name,sum(d.qty*d.rate) - m.discount + m.freight amount from sale_detail d, sale m, accounts a where m.s_id=d.s_id and m.cust_id=a.acc_no and m.inv_type = 'Cash' and m.dated = '".$dated."' group by m.s_id";
$cash_sales = $connect->query($sql);
$fetch_cash_sales = $cash_sales->fetchAll();  


$sql = "select m.s_id,m.inv_no,m.description,a.acc_name,sum(d.qty*d.rate) - m.discount + m.freight amount from sale_detail d, sale m, accounts a where m.s_id=d.s_id and m.cust_id=a.acc_no and m.inv_type = 'Credit' and m.dated = '".$dated."' group by m.s_id";
$credit_sales = $connect->query($sql);
$fetch_credit_sales = $credit_sales->fetchAll();


$sql = "select m.p_id,m.inv_no,m.description,a.acc_name,sum(d.qty*d.rate) - m.discount + m.freight amount from purchase_detail d, purchase m, accounts a where m.p_id=d.p_id and m.v_id=a.acc_no and m.dated = '".$dated."' group by m.p_id";
$credit_purchase = $connect->query($sql);
$fetch_credit_purchase = $credit_purchase->fetchAll();

$sql = "select m.pay_id,m.dated,m.description,a.acc_name, m.description,m.amount from pay_voucher m, accounts a where a.acc_no=m.v_id and m.dated = '".$dated."'";
$payments = $connect->query($sql);
$fetch_payments = $payments->fetchAll(); 

$sql = "select m.rec_id,m.dated,m.description,a.acc_name, m.description,m.amount from rec_voucher m, accounts a where a.acc_no=m.cust_id and m.dated = '".$dated."'";
$receipts = $connect->query($sql);
$fetch_receipts = $receipts->fetchAll();    

?>
<!DOCTYPE html>
<html>
<head>
  <title>
    Daybook Report of <?php echo $dated;?>
  </title>
<style>
* {
  font-family: Trebuchet MS;
  padding: 4px;
} 

table.main_table {
  border-collapse: collapse;


}

table.main_table td {
  border:1px dotted; black;
  vertical-align:top;
}

table.data_table {
  border-collapse: collapse;
  font-size: 10px;

}
table.data_table th {
  border:1px solid black;
  background-color: #f3f3f3;
}
table.data_table td {
  border:1px solid black;
}
.rightside {
  text-align: right;
}
h2 {
  text-align:  center;
}

table.gl_head_table {

}

table.gl_table {
  border-collapse: collapse;
  font-size: 11px;
  padding: 4px;

}
table.gl_table td {
  border:1px solid black;
}

table.gl_table thead td {
  font-weight: bold;

}

@media print {
  @page { margin: 0.5cm; }
  body { margin: 0.5cm;}
}
</style>
</head>
<body>

  <h2>Daybook Report of <?php echo $dated;?></h2>
<table width="100%" class="main_table">
  <tr>
    <td width="50%">
      
<table width="100%" class="data_table">
  <tr>
    <th align="center" colspan="3">Sales</th><th colspan="rightside"></th><th class="rightside"></th>
  </tr>
  <?php 
  $tot_cash_sales = 0;
  $rows = $cash_sales->rowCount();
  if($rows > 0)
  {
    echo '<tr>
    <th align="left" class="colheading" colspan="5">Cash Sales</th>
    </tr>';
    echo '
    <th width="5%">#</th>
    <th width="10%">Inv#</th>
    <th width="35%">To Customer</th>
    <th width="30%">Description</th>
    <th width="20%" class="rightside">Amount</th>
    </tr>
    ';
    $sr = 0;
    $tot_amount = 0;
    foreach ($fetch_cash_sales as $row) {
      $sr++;
      $amount = $row['amount'];
      $tot_amount = $tot_amount + $amount;
      echo '
      <tr>
      <td>'.$sr.'</td>
      <td>'.$row["s_id"].'</td>
      <td>'.$row["acc_name"].'</td>
      <td>'.$row["description"].'</td>
      <td class="rightside">'.number_format($amount,0).'</td>
      </tr>';      
    }
    echo '
      <tr>
      <td colspan="4" class="rightside">Total Cash Sales:</td>
      <td class="rightside">'.number_format($tot_amount,0).'</td>
      </tr>'; 
      $tot_cash_sales = $tot_amount;     

  }
  $rows = $credit_sales->rowCount();
  if($rows > 0)
  {
    echo '<tr>
    <th align="left" class="colheading" colspan="5">Credit Sales</th>
    </tr>';
    echo '
    <th width="5%">#</th>
    <th width="10%">Inv#</th>
    <th width="35%">To Customer</th>
    <th width="30%">Description</th>
    <th width="20%" class="rightside">Amount</th>
    </tr>
    ';
    $sr = 0;
    $tot_amount = 0;
    foreach ($fetch_credit_sales as $row) {
      $sr++;
      $amount = $row['amount'];
      $tot_amount = $tot_amount + $amount;
      echo '
      <tr>
      <td>'.$sr.'</td>
      <td>'.$row["s_id"].'</td>
      <td>'.$row["acc_name"].'</td>
      <td>'.$row["description"].'</td>
      <td class="rightside">'.number_format($amount,0).'</td>
      </tr>';      
    }
    $tot_net_sales = $tot_cash_sales + $tot_amount;
    $closing_sales = $tot_net_sales + $opening_sales;
     echo '
      <tr>
      <td colspan="4" class="rightside">Total Credit Sales:</td>
      <td class="rightside">'.number_format($tot_amount,0).'</td>
      </tr>

      <tr>
      <td colspan="4" class="rightside">Total Sales:</td>
      <td class="rightside">'.number_format($tot_net_sales,0).'</td>
      </tr>
       <tr>
      <td colspan="4" class="rightside"></td>
      <td class="rightside"></td>
      </tr>';

  }
  $rows = $receipts->rowCount();
  if($rows > 0)
  {
    echo '<tr>
    <th align="left" class="colheading" colspan="5">Cash Receipts</th>
    </tr>';
    echo '
    <th width="5%">#</th>
    <th width="10%">Rec#</th>
    <th width="35%">From</th>
    <th width="30%">Description</th>
    <th width="20%" class="rightside">Amount</th>
    </tr>
    ';
    $sr = 0;
    $tot_amount = 0;
    foreach ($fetch_receipts as $row) {
      $sr++;
      $amount = $row['amount'];
      $tot_amount = $tot_amount + $amount;
      echo '
      <tr>
      <td>'.$sr.'</td>
      <td>'.$row["rec_id"].'</td>
      <td>'.$row["acc_name"].'</td>
      <td>'.$row["description"].'</td>
      <td class="rightside">'.number_format($amount,0).'</td>
      </tr>';      
    }
     echo '
      <tr>
      <td colspan="4" class="rightside">Total Cash Receipts:</td>
      <td class="rightside">'.number_format($tot_amount,0).'</td>
      </tr>';   

  }
  
?>
</table>


    </td><td width="50%">
      <table width="100%" class="data_table">
  <tr>
    <th align="center" colspan="5">Purchase</th>
  </tr>
  <?php 
  $rows = $credit_purchase->rowCount();
  if($rows > 0)
  {
    echo '<tr>
    <th align="left" class="colheading" colspan="5"></th>
    </tr>';
    echo '
    <th width="5%">#</th>
    <th width="10%">Inv#</th>
    <th width="35%">From Vendor</th>
    <th width="30%">Description</th>
    <th width="20%" class="rightside">Amount</th>
    </tr>
    ';
    $sr = 0;
    $tot_amount = 0;
    foreach ($fetch_credit_purchase as $row) {
      $sr++;
      $amount = $row['amount'];
      $tot_amount = $tot_amount + $amount;
      echo '
      <tr>
      <td>'.$sr.'</td>
      <td>'.$row["p_id"].'</td>
      <td>'.$row["acc_name"].'</td>
      <td>'.$row["description"].'</td>
      <td class="rightside">'.number_format($amount,0).'</td>
      </tr>';      
    }
    echo '
      <tr>
      <td colspan="4" class="rightside">Total Purchase:</td>
      <td class="rightside">'.number_format($tot_amount,0).'</td>
      </tr>';      

  }
  
  $rows = $payments->rowCount();
  if($rows > 0)
  {
    echo '<tr>
    <th align="left" class="colheading" colspan="5">Payments</th>
    </tr>';
    echo '
    <th width="5%">#</th>
    <th width="10%">Pay#</th>
    <th width="35%">To</th>
    <th width="30%">Description</th>
    <th width="20%" class="rightside">Amount</th>
    </tr>
    ';
    $sr = 0;
    $tot_amount = 0;
    foreach ($fetch_payments as $row) {
      $sr++;
      $amount = $row['amount'];
      $tot_amount = $tot_amount + $amount;
      echo '
      <tr>
      <td>'.$sr.'</td>
      <td>'.$row["pay_id"].'</td>
      <td>'.$row["acc_name"].'</td>
      <td>'.$row["description"].'</td>
      <td class="rightside">'.number_format($amount,0).'</td>
      </tr>';      
    }
     echo '
      <tr>
      <td colspan="4" class="rightside">Total Payments:</td>
      <td class="rightside">'.number_format($tot_amount,0).'</td>
      </tr>';   

  }
  
?>
</table>
    </td>
  </tr>
  
</table>
<p style="page-break-after:always;text-align:center"></p>
<?php
$dated = $_GET['dated'];
 $acc_no = $user_acc_no;
 
 $opening = 0;
 $count = 0;
 $balance = 0;
 $tot_debit = 0;
 $tot_credit = 0;
 
      
  $sql = "SELECT IFNULL(sum(d.debit-d.credit),0)as opening FROM jv m,jv_detail d
  WHERE m.jv_id=d.jv_id and m.dated < '".$dated."' and d.acc_no ='".$acc_no."' and m.status ='Active' ";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that ou
           $opening_balance = $statement->fetchColumn();
          
           $opening = $opening + $opening_balance;
            
        
            $statement = $connect->prepare("select m.jv_id,m.dated,m.source,m.ref_id,m.description dsc,
          CASE WHEN m.source ='AR Inv' THEN (select CONCAT(a.acc_name,' ',' - Sale') from sale s, accounts a 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,' ',' - 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,' ', ' - 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,' ',' - 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.narr,
    d.debit,d.credit from jv m, jv_detail d where m.jv_id=d.jv_id 
    and m.dated = :dated and d.acc_no  = :acc_no and m.status = 'Active' 
    order by dated, ref_id");
 $statement->execute(
  array(
   ':acc_no'       =>  $acc_no,
   ':dated'       =>  $dated
 
  )
 );
 $result = $statement->fetchAll();
?> 
<table width="100%" class="gl_head_table">
<tr>
  <td colspan="4" width="25%"><b>Cash In Hand <?php echo $user_name;?></b> as on : <?php echo $dated;?></td>
  </tr>
 
  <tr>
  <td  colspan="3" width="80%" class="rightside">Opening Balance : </td><td width="20%" class="rightside"><?php echo number_format($opening,0);?></td>
  </tr>
  </table>
  <table width="100%" class="gl_table">
<thead>
  <tr>
  <td width="4%">#</td>
  <td width="4%">ID</td>
  <td width="12%">Date</td>
  <td width="12%" class="dataline">Source</td>
  
  <td width="42%">Description</td>
  
  <td width="8%" class="rightside">Debit</td>
  <td width="8%" class="rightside">Credit</td>
  <td width="10%" class="rightside">Balance</td>
  </tr>
</thead>
  
<?php
foreach($result as $row)
 {
  $debit = $row["debit"];
  $credit = $row["credit"];
  $tot_debit = $tot_debit + $debit;
  $tot_credit = $tot_credit + $credit;
  $count++;
  if($count == 1){$balance = $opening + $debit - $credit; }else{$balance = $balance + $debit - $credit;}
echo '
<tr>
<td width="4%" class="dataline">'.$count.'</td>
<td width="4%" class="dataline">'.$row["jv_id"].'</td>  
<td width="12%" class="dataline">'.$row["dated"].'</td>
<td width="12%" class="dataline">'.$row["source"].' - '.$row["ref_id"].'</td>

<td width="42%" class="dataline">'.$row["description"].' </td>
<td width="8%" class="rightside dataline">'.number_format($debit, 0).'</td>
   <td width="8%" class="rightside dataline">'.number_format($credit, 0).'</td>
   <td width="10%" class="rightside dataline">'.number_format($balance, 0).'</td>
  
   
    
   </tr>
';
}
 echo '

  <tr>
  <td colspan="5" width="74%" class="rightside total">Total: </td>
  <td width="8%" class="rightside total">'.number_format($tot_debit, 0).'</td>
  <td width="8%" class="rightside total">'.number_format($tot_credit, 0).'</td>
  <td width="10%" class="rightside total">'.number_format($balance, 0).'</td>
  </tr>
  
  ';?>
  </table>


</body>
</html>
