<?php

 

 include('db_con.php');

 include('function.php');

 

 $store_id = $_GET['store_id'];

 

 $fdate = $_GET['fdate'];

 $tdate = $_GET['tdate'];

 $tot_qty_in = 0;

 $tot_qty_out = 0;

 $tot_ctn = 0;

 

 $sql = "SELECT store_name FROM store WHERE store_id='".$store_id."'";

            $statement = $connect->prepare($sql);

            $statement->execute(); // no need to add `$sql` here, you can take that out

           $store_name = $statement->fetchColumn();

         

 $sql = "

  select a.id,a.dated,a.item_name,a.user_name,a.description,a.ctn,a.qty_in,a.qty_out from(

  select m.p_id as id, m.dated, CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name,u.user_name,'Purchased' as description,d.ctn,d.qty as qty_in,0 as qty_out From items i,item_brand b,item_type t,item_color c,purchase m,purchase_detail d , user_detail u WHERE i.type_id=t.type_id and i.brand_id=b.brand_id and i.color_id=c.color_id and i.item_id=d.item_no and m.p_id=d.p_id and m.user_id=u.user_id

   and m.dated between :fdate and :tdate  and d.store_id = :store_id

  union all

  select m.s_id as id, m.dated,CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name,u.user_name,'Sale' as description,d.ctn,0 as qty_in,d.qty as qty_out From

items i,item_brand b,item_type t,item_color c,sale m,sale_detail d,user_detail u WHERE u.user_id=m.user_id and i.type_id=t.type_id and i.brand_id=b.brand_id and i.color_id=c.color_id and i.item_id=d.item_no and m.s_id=d.s_id

   and m.dated between :fdate and :tdate and d.store_id = :store_id

  union all

  select m.pr_id as id, m.dated, CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name, u.user_name,'Purchase Return' as description,d.ctn,0 as qty_in,d.qty as qty_out from items i,item_brand b,item_type t,item_color c,pr m,pr_detail d,store s,user_detail u WHERE i.type_id=t.type_id and i.brand_id=b.brand_id and i.color_id=c.color_id and i.item_id=d.item_no and m.pr_id=d.pr_id and u.user_id=m.user_id

   and m.dated between :fdate and :tdate  and d.store_id = :store_id 

  union all

  select m.sr_id as id, m.dated,CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name,u.user_name, 'Sale Return' as description, d.ctn,d.qty as qty_in,0 as qty_out from items i,item_brand b,item_type t,item_color c,sr m,sr_detail d,store s,user_detail u WHERE u.user_id=m.user_id and  i.type_id=t.type_id and i.brand_id=b.brand_id and i.color_id=c.color_id and i.item_id=d.item_no and m.sr_id=d.sr_id

   and m.dated between :fdate and :tdate  and d.store_id = :store_id 

  union all

  select m.tr_id as id, m.dated,CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name, u.user_name,'Adjustment' as description,ctn,qty_in qty_in,qty_out qty_out from items i,item_brand b,item_type t,item_color c,adjusted_stock m,user_detail u WHERE i.type_id=t.type_id and i.brand_id=b.brand_id and i.color_id=c.color_id and i.item_id=m.item_no and u.user_id=m.user_id

   and m.dated between :fdate and :tdate and m.store_id  = :store_id  

   union all

  select m.tr_id as id, m.dated,CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name, u.user_name,'Transfer' as description,0 ctn, qty qty_in,0 qty_out from items i,item_brand b,item_type t,item_color c,adjust_stock m,user_detail u WHERE i.type_id=t.type_id and i.brand_id=b.brand_id and i.color_id=c.color_id and i.item_id=m.item_no and u.user_id=m.user_id

   and m.dated between :fdate and :tdate and m.to_store = :store_id

    union all

  select m.tr_id as id, m.dated,CONCAT(i.item_name,' ',t.type_name,' ',b.brand_name,' ',c.color_name) item_name, u.user_name,'Transfer' as description,0 ctn,0 qty_in,qty qty_out from items i,item_brand b,item_type t,item_color c,adjust_stock m , user_detail u WHERE i.type_id=t.type_id and i.brand_id=b.brand_id and i.color_id=c.color_id and i.item_id=m.item_no and m.user_id=u.user_id

   and m.dated between :fdate and :tdate and m.from_store = :store_id

  )a order by dated, description, id";

 

   $statement = $connect->prepare($sql);

 $statement->execute(

  array(

   ':store_id'       =>  $store_id,

   ':fdate'       =>  $fdate,

   ':tdate'       =>  $tdate

  )

 );

  

 

 $result = $statement->fetchAll();

 

  ?>

<!DOCTYPE html>

<html>

<head>

  <title></title>

  <style>

@page { size: auto;  margin: 0mm; }

.rightside {text-align: right;}

</style>

</head>

<body>

<table width="100%">

<tr>

<td>Store Inquiry Report</td>

</tr>

<tr>

<td>Store Name :</td><td><?php echo $store_name;?></td>

</tr>

</table>

  <table width="100%" id="items" border="1">

  <tr>

  <th width="4%">ID</th>

<th width="12%">Date</th>

  <th width="35%">Item Name</th>

  <th width="18%">User</th>

  <th width="10%">Term</th>

  <th width="5%">Ctn</th>

  <th width="8%" class="rightside">In</th>

  <th width="8%" class="rightside">Out</th>

  </tr>

  

  

<?php

foreach($result as $row)

 {

  $ctn = $row['ctn'];

  $qty_in = $row['qty_in'];

  $qty_out = $row['qty_out'];

  $tot_qty_in = $tot_qty_in + $qty_in;

  $tot_qty_out = $tot_qty_out + $qty_out;

  $tot_ctn = $tot_ctn + $ctn;

echo '

  

  

  

<tr>

  

<td width="4%">'.$row["id"].'</td>

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

<td width="35%">'.$row["item_name"].'</td>

<td width="18%">'.$row["user_name"].'</td>

<td width="10%">'.$row["description"].'</td>

<td width="5%" class="rightside">'.number_format($ctn, 0).'</td>

   <td width="8%" class="rightside">'.number_format($qty_in, 0).'</td>

   <td width="8%" class="rightside">'.number_format($qty_out, 0).'</td>

  

   

    

   </tr>

  ';

   

  }

  ?>

<tr>

  

<td width="79%" colspan="5" class="rightside">Total :</td>

<td width="5%" class="rightside"><?php echo number_format($tot_ctn, 0); ?></td>

   <td width="8%" class="rightside"><?php echo number_format($tot_qty_in, 0); ?></td>

   <td width="8%" class="rightside"><?php echo number_format($tot_qty_out, 0);?></td>      

   </tr>

  </table>

</body>

</html>