<?php
include('function.php');
 
 include('db_con.php');
 //stock_update($connect);
 stock_instore($connect);
 $dated = date('d-m-Y h:m A');
 $html = '';
 
 $count = 0;
 $stock = 0;
 
 $tot_stock = 0;

 $fdate = $_GET['fdate'];
 $tdate = $_GET['tdate'];
 $store_id = $_GET['store_id'];
 $store_name = 'ALL';

 
 if($store_id > 0)
 {
  $statement = $connect->prepare("select ifnull(store_name,0) from store where store_id = '".$store_id."'");
 $statement->execute();
 $store_name = $statement->fetchColumn();  
 }
 
 
    
  $statement = $connect->prepare("select z.item_id,z.item_name,z.stock,z.sale from(
    select a.item_id,a.item_name,sum(a.stock) stock,sum(a.sale) sale from(
    SELECT i.item_id,CONCAT(t.type_name, ' ',i.item_name)  as item_name,s.stock,0 sale from items i,stock_instore s,item_type t where t.type_id=i.type_id and s.item_no=i.item_id and s.store_id LIKE '%".$store_id."%'
  AND i.status = 'active' and s.stock > 0
  union all
  SELECT d.item_no item_id,CONCAT(t.type_name, ' ',i.item_name)  as item_name,0 stock,IFNULL(sum(d.qty),0) sale from items i, sale m,sale_detail d,item_type t  where m.s_id=d.s_id and t.type_id=i.type_id 
  AND i.status = 'active' and d.store_id LIKE '%".$store_id."%' and m.dated between '".$fdate."' and '".$tdate."' group by d.item_no

  )a group by a.item_id )z where z.stock > 0 and sale = 0 order by item_name");
 $statement->execute();
 
 $result = $statement->fetchAll(); 
 ?>
<!DOCTYPE html>
<html>
<head>
   <title><?php echo basename(__FILE__, '.php'); ?></title>
  <style>
    * {
      padding: 3px;
      font-family: arial;
      font-size: 14px;
    }
@page { size: auto;  margin: 0mm; }

thead td {
  border-bottom: 2px solid black;
  border-top: 2px solid black;
  font-weight: bold; 
}

tbody td {
  border-bottom: 1px solid black;
}
.rightside {
  text-align: right;
}
</style>

</head>
<body>

  
  
  <table width="100%" class="table_header">
  <tr>
  <td colspan="4" style="text-align:center;font-size:18px;font-weight:bold;margin:5px">Dead Item Report</td>
  </tr>
  <tr>

  <td width="100%" colspan="4">Store : <?php echo $store_name;?></td>
      
  </tr>
  <tr>

  <td width="5%">From:</td><td width="15%"><?php echo $fdate;?></td>
  <td width="5%">To:</td><td width="15%"><?php echo $tdate;?></td>
      
  </tr>
  
  </table>
  <table width="100%" class="print_table">
    <thead>
  <tr>
  <td width="5%">#</td>
  
  <td width="75%">Item Name</td> 
   
  <td width="20%" class="rightside">Stock</td>
   
  </tr>
 
  </thead>
  <tbody>
<?php
$stock = 0;
foreach($result as $row)
 {
  
  $stock = $row["stock"];
  $sale = $row["sale"];
  
  $tot_stock = $tot_stock + $stock;
  
  
 
   $count = $count+1;
     // if($count == 1){$stock = $opening + $qty_in - $qty_out; }else{$stock = $stock + $qty_in - $qty_out;}
   
 echo '
  
<tr>
<td>'.$count.'</td>
  
<td>'.$row["item_name"].'</td>
    
   
    <td class="rightside">'.number_format($stock, 0).'</td>
    
  
   
    
   </tr>
   
  
        ';
}

  
  
echo '
  <tr>
  <td colspan="2" class="rightside">Total : </td><td class="rightside">'.number_format($tot_stock, 0).'</td>
  
 
  </tr>
';
  ?>
</tbody>
<tr><td colspan="3">Printed at : <?php echo $dated;?></td></tr>
  </table>
    
  
</body>
 </html>