<?php



require_once 'pdf.php';

 

 include('db_con.php');

 $html = '';

 $fdate = $_GET['fdate'];

 $store_id = $_GET['store_id'];

 $tdate = $_GET['tdate'];

 $item_no = $_GET['item_no'];

 $opening = 0;

 $qty_in = 0;

 $qty_out = 0;

 $item_name = 'Item';

 $count = 0;

 $stock = 0;



 $end_date = '2019-04-01';





  $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 item_name FROM items WHERE 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."' and d.store_id='".$store_id."'";

           $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."' and d.store_id='".$store_id."'";

           $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."' and d.store_id='".$store_id."'";

           $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."' and d.store_id='".$store_id."'";

           $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_out),0) FROM adjusted_stock WHERE dated <'".$fdate."' and item_no ='".$item_no."' and store_id='".$store_id."'";

           $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."' and store_id='".$store_id."'";

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

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

           $qty_in = $statement->fetchColumn();







             $sql = "SELECT IFNULL(sum(qty),0) FROM adjust_stock WHERE dated <'".$fdate."' and item_no ='".$item_no."' and to_store='".$store_id."'";

           $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),0) FROM adjust_stock WHERE dated <'".$fdate."' and item_no ='".$item_no."' and from_store='".$store_id."'";

           $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.inv_no,a.dated,LEFT(a.cust,35)cust,a.description,a.item_no,a.rate,a.ctn,a.qty_in,a.qty_out from(  

  select m.s_id as id,m.inv_no,m.dated,c.name as cust,'Sale' as description,d.item_no,d.rate,d.ctn,0 as qty_in, sum(d.qty)as qty_out

  from sale_detail d, sale m,customers c where c.cust_id=m.cust_id and  

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

  group by m.s_id, m.dated,m.description,d.item_no

  union all 

  select m.pr_id as id,m.pr_id as inv_no, m.dated,v.name as cust,'Purchase Return' as description,d.item_no,d.rate,d.ctn,0 as qty_in, sum(d.qty)as qty_out

  from pr_detail d, pr m,vendors v where v.v_id=m.v_id and 

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

  group by m.pr_id, m.dated,m.description,d.item_no

  union    

  select m.sr_id as id,m.sr_id as inv_no, m.dated,c.name as cust,'Sale Return' as description,d.item_no,d.rate,d.ctn,sum(d.qty) as qty_in, 0 as qty_out

  from sr_detail d, sr m, customers c where c.cust_id=m.cust_id and 

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

  group by m.sr_id, m.dated,m.description,d.item_no

  union 

  select m.p_id as id, m.p_id as inv_no,m.dated, v.name as cust,'Purchase' as description,d.item_no,d.rate,d.ctn,sum(d.qty) as qty_in, 0 as qty_out

  from purchase_detail d, purchase m, vendors v where v.v_id=m.v_id and 

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

  group by m.p_id, m.dated,m.description,d.item_no

  union all

  select m.tr_id as id,m.tr_id as inv_no,m.dated,CONCAT('From Store ', s.store_name) as cust,'Transfer In' as description ,m.item_no,0 as rate,m.ctn,m.qty as qty_in ,0 as qty_out

  from adjust_stock m,store s where s.store_id=m.from_store and

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

  

  union all

  select m.tr_id as id,m.tr_id as inv_no,m.dated,CONCAT('To Store ', s.store_name) as cust, 'Transfer Out' as description ,m.item_no,0 as rate,m.ctn,0 as qty_in ,m.qty as qty_out

  from adjust_stock m,store s where m.to_store=s.store_id and 

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

  

  union all

  select tr_id as id,tr_id as inv_no,dated,'' as cust, 'Adjustment' as description ,item_no, 0 as rate, 0 ctn,sum(qty_in) as qty_in ,sum(qty_out) as qty_out

  from adjusted_stock where 

  dated between :fdate and :tdate and store_id=:store_id and item_no = :item_no

  group by tr_id,dated,description,item_no

  )a order by dated,id;");  



  $statement->execute(

  array(

   ':item_no'       =>  $item_no,

   ':store_id'       =>  $store_id,

   ':fdate'       =>  $fdate,

   ':tdate'       =>  $tdate

  )

 );  

$result = $statement->fetchAll(); 



  

 





 $html .=' <style>

    @page { margin:160px 20px; }

    @page {margin-bottom: 40px;}

    #header { position: fixed; left: 0px; top: -160px; right: 0px; height: 160px; text-align: center; }

    #footer { position: fixed; left: 0px; bottom: -20px; right: 0px; height: 50px; background-color: #fff; }

    #content {margin:0;font-size:13px;font-weight:normal;}

    .rightside{text-align:right;}

    #footer .page:after { content: counter(page); }

    

    #table_header th {

    border-top:1px solid black;

    border-bottom:1px solid black;

  }

  .smallfont{font-size:12px;text-align:right;}

  </style>



  <div id="header">



  <h3>Item Transfer History of '.$store_name.'</h3>

  <table width="100%">

  <tr>

  <td width="20%">Item No. :</td><td width="20%">'.$item_no.'</td><td width="55%" class="rightside">From :</td><td width="20%" class="rightside smallfont">'.$fdate.'</td>

  </tr>

  <tr>

  <td width="15%">Name : </td><td width="70%" style="font-size:15px">'.$item_name.'</td><td width="20%" class="rightside">To :</td><td width="20%" class="rightside smallfont">'.$tdate.'</td>

  </tr>

  <tr>

  <td width="10%"></td><td width="20%"></td><td width="70%" class="rightside">Opening Balance :</td><td width="20%" class="rightside">'.$opening.'</td>

  </tr>

  </table>

  <table width="100%" id="table_header">



  <tr>

  <th width="3%">#</th>

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

  

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

  <th width="23%">Cust/Vendor</th> 

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

  

  

  <th width="10%" class="rightside">Rate</th>

  <th width="5%" class="rightside">Ctn</th>
  <th width="10%" class="rightside">In</th>

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

  <th width="10%" class="rightside">Stock</th>







  </tr>



  </table>



  </div>

  

';

foreach($result as $row)

 {

  $qty_in = $row["qty_in"];

  $ctn = $row["ctn"];

  $qty_out = $row["qty_out"];

  $rate = $row["rate"];





 

   $count = $count+1;

   if($count == 1){$stock = $opening + $qty_in - $qty_out; }else{$stock = $stock + $qty_in - $qty_out;}

   

 

  

    

  

  



$html .= '

  <div id="content">

  

  <table width="100%">

<tr>

<td width="3%">'.$count.'</td>

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

  

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

<td width="23%">'.$row["cust"].'</td>





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

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

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

    <td width="15%" class="rightside">'.number_format($stock, 2).'</td>

  

   

    

   </tr>

   

  </table>





  </div>



  <div id="footer">

  <hr />

  <table width="100%">

  <tr>

  <td width="70%" class="rightside">Stock : </td><td width="30%" class="rightside">'.$stock.'</td>

  </tr>

  </table>

    

  </div>



        ';



}

 $pdf = new Pdf();



 $file_name = 'Item_Transfer.pdf';

 $pdf->setPaper('A4', 'portrait');



 $pdf->loadHtml($html);

 $pdf->render();

 $pdf->stream($file_name, array("Attachment" => false));



?>