<?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 = '';
 $fdate = $_GET['fdate'];
 $cust_id = $_GET['cust_id'];
  $v_id = $_GET['v_id'];
 $tdate = $_GET['tdate'];
 $item_no = $_GET['item_no'];
 $opening = 0;
 $qty_in = 0;
 $qty_out = 0;
 $tot_qty_in = 0;
 $tot_qty_out = 0;
 $tot_ctn = 0;
 $item_name = 'Item';
 $count = 0;
 $stock = 0;
 $sql = "SELECT CONCAT(t.type_name,' ',i.item_name) item_name FROM items i,item_type t WHERE i.type_id=t.type_id and i.item_id='".$item_no."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $item_name = $statement->fetchColumn();
          
 $sql = "SELECT IFNULL(sum(d.qty),0) FROM purchase_detail d,purchase m  WHERE m.p_id=d.p_id and m.dated <'".$fdate."' and d.item_no ='".$item_no."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $qty_in = $statement->fetchColumn();
           
            $opening = $opening + $qty_in;
            $sql = "SELECT IFNULL(sum(d.qty),0) FROM sr_detail d,sr m  WHERE m.sr_id=d.sr_id and m.dated <'".$fdate."' and d.item_no ='".$item_no."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $qty_in = $statement->fetchColumn();
           
            $opening = $opening + $qty_in;
            $sql = "SELECT IFNULL(sum(d.qty),0) FROM sale_detail d,sale m  WHERE m.s_id=d.s_id and m.dated <'".$fdate."' and d.item_no ='".$item_no."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $qty_out = $statement->fetchColumn();
           
            $opening = $opening - $qty_out;
             $sql = "SELECT IFNULL(sum(d.qty),0) FROM pr_detail d,pr m  WHERE m.pr_id=d.pr_id and m.dated <'".$fdate."' and d.item_no ='".$item_no."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $qty_out = $statement->fetchColumn();
           
            $opening = $opening - $qty_out;  
             $sql = "SELECT IFNULL(sum(qty_in),0) FROM adjusted_stock WHERE dated <'".$fdate."' and item_no ='".$item_no."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $qty_in = $statement->fetchColumn();
           
            $opening = $opening + $qty_in; 
             $sql = "SELECT IFNULL(sum(qty_out),0) FROM adjusted_stock WHERE dated <'".$fdate."' and item_no ='".$item_no."'";
           $statement = $connect->prepare($sql);
           $statement->execute(); // no need to add `$sql` here, you can take that out
           $qty_out = $statement->fetchColumn();
           
            $opening = $opening - $qty_out;   
            $statement = $connect->prepare("
  select a.id,a.dated,a.branch_name,a.store_name,a.name,a.description,a.rate,a.ctn,a.qty_in,a.qty_out from(
  
  select m.s_id as id, m.dated, b.branch_name,c.acc_name name,'Sale' as description,d.rate,d.ctn,0 as qty_in,sum(d.qty) as qty_out From
  sale m,sale_detail d,accounts c,branch b,store s where m.s_id=d.s_id and c.acc_no=m.cust_id and m.cust_id = '".$cust_id."' and b.branch_id=m.branch_id and b.branch_id=m.branch_id and s.store_id=d.store_id and m.dated between :fdate and :tdate and d.item_no = :item_no group by  id, dated, name, description 
  
  union all
  select m.sr_id as id, m.dated,b.branch_name,s.store_name, c.acc_name name,'Sale Return' as description,d.rate, d.ctn,d.qty as qty_in,0 as qty_out From
  sr m,sr_detail d,accounts c,branch b, store s where m.sr_id=d.sr_id and c.acc_no=m.cust_id and b.branch_id=m.branch_id and d.store_id=s.store_id and  m.cust_id = '".$cust_id."' and m.dated between :fdate and :tdate and d.item_no = :item_no group by  id, dated, name, description 
  union all
  select m.tr_id as id, m.dated,'' as branch_name,s.store_name 'Store Adjusted' as name,'Adjustment' as description,0 as rate,0 ctn,qty_in,qty_out From
  adjusted_stock m.store s where m.store_id=s.store_id and m.dated between :fdate and :tdate and m.item_no = :item_no group by  id, dated, name, description 
  )a order by dated;");    
  
 
 if($cust_id==0)
 {
  $statement = $connect->prepare("
  select a.id,a.inv_no,a.dated,a.branch_name,a.store_name,a.name,a.description,a.rate,a.ctn,a.qty_in,a.qty_out from(
  select m.p_id as id,m.p_id as inv_no, m.dated,b.branch_name, s.store_name,v.acc_name name,'Purchased' as description,FORMAT(d.rate,2)as rate,d.ctn,sum(d.qty) as qty_in,0 as qty_out From
  purchase m,purchase_detail d,accounts v,branch b, store s where m.p_id=d.p_id and v.acc_no=m.v_id and b.branch_id=m.branch_id and s.store_id=d.store_id and m.dated between :fdate and :tdate and d.item_no = :item_no group by  id, dated, name, description
  union all
  select m.s_id as id,m.inv_no, m.dated, b.branch_name,s.store_name,c.acc_name name,'Sale' as description,d.rate,d.ctn,0 as qty_in,sum(d.qty) as qty_out From
  sale m,sale_detail d,accounts c,branch b,store s where m.s_id=d.s_id and c.acc_no=m.cust_id and b.branch_id=m.branch_id and s.store_id=d.store_id and m.dated between :fdate and :tdate and d.item_no = :item_no group by  id, dated, name, description 
  union all
  select m.pr_id as id,m.pr_id as inv_no, m.dated, b.branch_name,s.store_name,v.acc_name name,'Pur Return' as description,d.rate,d.ctn,0 as qty_in,sum(d.qty) as qty_out From
  pr m,pr_detail d,accounts v,branch b,store s where m.pr_id=d.pr_id and v.acc_no=m.v_id and b.branch_id=m.branch_id and s.store_id=d.store_id and m.dated between :fdate and :tdate and d.item_no = :item_no group by  id, dated, name, description
  union all
  select m.sr_id as id,m.sr_id as inv_no, m.dated,b.branch_name,s.store_name, c.acc_name name,'Sale Return' as description,d.rate, d.ctn,sum(d.qty) as qty_in,0 as qty_out From
  sr m,sr_detail d,accounts c,branch b,store s where m.sr_id=d.sr_id and c.acc_no=m.cust_id and b.branch_id=m.branch_id and s.store_id=d.store_id and  m.dated between :fdate and :tdate and d.item_no = :item_no group by  id, dated, name, description 
  union all
  select m.tr_id as id, m.tr_id as inv_no,m.dated,'' as branch_name,s.store_name, 'Store Adjusted' as name,'Adjustment' as description,0 as rate,0 ctn,sum(qty_in) qty_in,sum(qty_out) qty_out From
  adjusted_stock m,store s where m.store_id=s.store_id and m.dated between :fdate and :tdate and m.item_no = :item_no group by  id, dated, name, description 
  )a order by dated;");
 }
 if($v_id > 0)
 {
  $statement = $connect->prepare("
  select a.id,a.inv_no,a.dated,a.branch_name,a.store_name,a.name,a.description,a.rate,a.ctn,a.qty_in,a.qty_out from(
  select m.p_id as id,m.p_id as inv_no, m.dated,b.branch_name, v.acc_name name,'Purchased' as description,FORMAT(d.rate,2)as rate,d.ctn,sum(d.qty) as qty_in,0 as qty_out From
  purchase m,purchase_detail d,accounts v,branch b,store s where m.p_id=d.p_id and v.acc_no=m.v_id and b.branch_id=m.branch_id and d.store_id=s.store_id and m.dated between :fdate and :tdate and m.v_id='".$v_id."' and d.item_no = :item_no group by  id, dated, name, description
  
  )a order by dated;");
 }
 $statement->execute(
  array(
   ':item_no'       =>  $item_no,
   ':fdate'       =>  $fdate,
   ':tdate'       =>  $tdate
  )
 );
 if($cust_id > 0)
 {
  $statement = $connect->prepare("
  select a.id,a.inv_no,a.dated,a.branch_name,a.store_name,a.name,a.description,a.rate,a.ctn,a.qty_in,a.qty_out from(
  select m.s_id as id,m.inv_no, m.dated,b.branch_name, s.store_name,c.acc_name name,'Sale' as description,d.rate,d.ctn,0 as qty_in,sum(d.qty) as qty_out From
  sale m,sale_detail d,customers c,branch b,store s where m.s_id=d.s_id and c.acc_no=m.cust_id and b.branch_id=m.branch_id and s.store_id=d.store_id and m.dated between :fdate and :tdate and m.cust_id='".$cust_id."' and d.item_no = :item_no group by  id, dated, name, description
  union all
  select m.sr_id as id,m.inv_no, m.dated,b.branch_name,s.store_name, c.acc_name name,'Sale Return' as description,d.rate,d.ctn,sum(d.qty)  as qty_in,0 as qty_out From
  sr m,sr_detail d,customers c,branch b,store s where m.sr_id=d.sr_id and c.acc_no=m.cust_id and b.branch_id=m.branch_id and d.store_id=s.store_id and m.dated between :fdate and :tdate and m.cust_id='".$cust_id."' and d.item_no = :item_no group by  id, dated, name, description
  
  )a order by dated;");
 }
 $statement->execute(
  array(
   ':item_no'       =>  $item_no,
   ':fdate'       =>  $fdate,
   ':tdate'       =>  $tdate
  )
 );
 $result = $statement->fetchAll(); 
 ?>
 <!DOCTYPE html>
<html>
<head>
  <title>Item Ledger</title>
  <style>
    * {
      margin: 0;
      padding: 0;
      font-family: arial;
    }
table thead td {
  font-size: 13px;
  border-bottom: 2px solid black;
  border-top:  2px solid black;
}
table tbody td {
  font-size: 12px;
 border-bottom: 1px solid gray;
  border-top: : 1px solid gray; 
}
.rightside {
  text-align: right;
}
}
@media print
{
    @page {
        margin-top: 0;
        margin-bottom: 0;
    }
    body  {
        padding-top: 72px;
        padding-bottom: 72px ;
    }
} 
h3 {
  text-align: center;
  border-bottom: 1px solid gray;
}
thead {
  font-weight: bold;
 
}
tfoot td {
  border-top: 1px solid gray;
}
</style>
</head>
<body>
  
  <h3>Item Ledger</h3>
  <table width="100%">
  <tr>
  <td width="20%">Item No. :</td><td width="40%"><?php echo $item_no;?></td><td width="20%" class="rightside">From :</td><td width="20%" class="rightside"><?php echo $fdate;?></td>
  </tr>
  <tr>
  <td width="20%">Name : </td><td width="40%" style="font-size:15px"><?php echo $item_name;?></td><td width="20%" class="rightside">To :</td><td width="20%" class="rightside"><?php echo $tdate;?></td>
  </tr>
  <tr>
  <td colspan="3" width="80%" class="rightside">Opening Balance :</td><td width="20%" class="rightside"><?php echo $opening;?></td>
  </tr>
  </table>
  <table width="100%" id="table_header">
    <thead>
  <tr>
  <td width="2%">#</td>
  <td width="4%">ID</td>
  <td width="5%">InvNo</td>
  <td width="10%">Date</td> 
  
  
  <td colspan="2" width="53%">From/To</td>
  
  <td width="6%" class="smallfont">Rate/Cost</th>
  
  <td width="6%" class="rightside">In</td>
  <td width="6%" class="rightside">Out</td>
  <td width="8%" class="rightside">Stock</td>
  </tr>
  </thead>
  <tbody>
  <?php
  
foreach($result as $row)
 {
  $qty_in = $row["qty_in"];
  $qty_out = $row["qty_out"];
  $ctn = $row["ctn"];
  $tot_ctn = $tot_ctn + $ctn;
  $tot_qty_in = $tot_qty_in + $qty_in;
  $tot_qty_out = $tot_qty_out + $qty_out;
  
  $rate = $row['rate'];  

  
 
   $count = $count+1;
   if($count == 1){$stock = $opening + $qty_in - $qty_out; }else{$stock = $stock + $qty_in - $qty_out;}
   
 
  
    echo '
  
<tr>
<td width="2%">'.$count.'</td>
  <td width="4%">'.$row["id"].'</td>
  <td width="5%">'.$row["inv_no"].'</td>
<td width="10%">'.$row["dated"].'</td>


<td width="30%">'.$row["name"].'</td>
<td width="23%">'.$row["description"].'</td>
<td width="6%" class="rightside">'.$rate.'</td>

<td width="6%" class="rightside">'.number_format($qty_in, 2).'</td>
   <td width="6%" class="rightside">'.number_format($qty_out, 2).'</td>
    <td width="8%" class="rightside">'.number_format($stock, 2).'</td>    
   </tr>
   ';  
  
}
?>
</tbody>

</tfoot>
</table>
<table width="100%" style="font-weight: bold; border-top:1px solid gray;border-bottom: 1px solid gray"> 
  <tr>
    <td colspan="7" class="rightside" width="75%">Total :</td>
    <td class="rightside"  width="6%"><?php echo $tot_qty_in;?></td>
    <td class="rightside"  width="6%"><?php echo $tot_qty_out;?></td>
    <td class="rightside"  width="8%"><?php echo $stock;?></td>
  </tr>
  </table>
</body>
</html>