<?php
 
 include('db_con.php');


 $html = '';
 
 
 
 



  $sql = "select a.item_no, a.item_name, a.qty_in, a.qty_out from (
          SELECT b.item_no, b.item_name, sum(b.qty_in) qty_in, sum(b.qty_out) qty_out from (
          select p.item_no, concat(t.type_name, ' ',i.item_name) item_name , sum(qty) qty_in, 0 qty_out from purchase_detail p, items i, item_type t where t.type_id=i.type_id and p.item_no=i.item_id group by p.item_no
          union all
          select p.item_no, concat(t.type_name, ' ',i.item_name) item_name , sum(qty_in) qty_in, 0 qty_out from adjusted_stock p, items i, item_type t where t.type_id=i.type_id and p.item_no=i.item_id group by p.item_no
          union all
          select r.item_no, concat(t.type_name, ' ',i.item_name) item_name , sum(qty) qty_in, 0 qty_out from sr_detail r, items i, item_type t where t.type_id=i.type_id and r.item_no=i.item_id group by r.item_no
          union all
          select s.item_no, concat(t.type_name, ' ',i.item_name) item_name , 0 qty_in, sum(qty) qty_out from sale_detail s, items i, item_type t where t.type_id=i.type_id and s.item_no=i.item_id group by s.item_no
          union all
          select s.item_no, concat(t.type_name, ' ',i.item_name) item_name , 0 qty_in, sum(qty_out) qty_out from adjusted_stock s, items i, item_type t where t.type_id=i.type_id and s.item_no=i.item_id group by s.item_no
          union all
          select s.item_no, concat(t.type_name, ' ',i.item_name) item_name , 0 qty_in, sum(qty) qty_out from pr_detail s, items i, item_type t where t.type_id=i.type_id and s.item_no=i.item_id group by s.item_no
          ) b group by b.item_no order by qty_out desc
  ) a";
  
 $statement = $connect->query($sql);
  
 
 $result = $statement->fetchAll();


 
  ?>
<!DOCTYPE html>
<html>
<head>
  <title>Top Items</title>
  <style>
    * {
      font-family: calibri;
      padding: 3px;
      line-height: 12px;
     
    }
    @media print {
  
  body { margin: 1cm; }
}
  .rightside {text-align: right;}
  table td{   
    border-bottom: 1px solid gray;
    font-size: 12px;
  }
  .head_row {
    font-weight: bold;
  }
  thead td {
    font-weight: bold;
     border-bottom: 2px solid gray;
      border-top: 2px solid gray;
       background-color :  #f3f3f3;
  }
.total {
  font-weight: bold;
}
  </style>

</head>
<body>

  <table width="100%">
  <thead>
  <tr class="head_row">
  
  <td width="5%">Sr</td>
<td width="65%" >Description</td>
<td width="10%" class="rightside">Qty In</td>
<td width="10%" class="rightside">Qty Out</td>
<td width="10%" class="rightside">Stock</td>

  </tr>
  </thead>
  <tbody>
<?php



$count = 0;
$tot_qty_in = 0;
$tot_qty_out = 0;
$tot_stock = 0;
foreach($result as $row)
 {
 
  $count++;
  
  $qty_in = $row["qty_in"];
  $tot_qty_in = $tot_qty_in + $qty_in;

  $qty_out = $row["qty_out"];

  $stock = $qty_in - $qty_out;
  $tot_qty_out = $tot_qty_out + $qty_out;
  $tot_stock = $tot_stock + $stock;
 
echo ' 
  <tr>
  <td>'.$count.'</td> 
  
  <td>'.$row["item_name"].'</td>
  <td class="rightside">'.number_format($qty_in,0).'</td>
  <td class="rightside">'.number_format($qty_out,0).'</td>
  <td class="rightside">'.number_format($stock,0).'</td>
  
  </tr>
  ';
   
   
   
  }
  echo 
  '
    <tr class="total">
  <td colspan="2" class="rightside">Total:</td>

  <td class="rightside">'.number_format($tot_qty_in,0).'</td>
  <td class="rightside">'.number_format($tot_qty_out,0).'</td>
  <td class="rightside">'.number_format($tot_stock,0).'</td>
  
  </tr>';
  
  ?>
  </tbody>
</table>

</body>
</html>