<?php
include "../../include/config.php";
include "../../function/inc_function.php";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="data_stok_brgjadi_'.get_datetime().'.xls"');
header('Cache-Control: max-age=0');

$fromdate = $_REQUEST['fromdate'];
$todate   = $_REQUEST['todate'];
$codename	= isset($_REQUEST['codename']) ? $_REQUEST['codename'] : '';	
$idprodcat	= isset($_REQUEST['idprodcat']) ? $_REQUEST['idprodcat'] : 1000;
if(!empty($codename)){
	$search	= "AND CONCAT_WS('-',a.prod_code,a.prod_name) LIKE '%$codename%'";
}else{$search	= "";}
if($idprodcat =="ALL"){$s_idcat = "WHERE id IN(3,4,5)";}else{$s_idcat = "WHERE id=$idprodcat";}
$month 		= substr($fromdate,5,2);
$year		= substr($fromdate,0,4);
$year_sto   = substr($todate,0,4);
$month_sto  = substr($todate,5,2);
$thnbln = "$year$month";
$sqlShow 	= mysql_query("SELECT prof_name FROM profil");
$dataShow 	= mysql_fetch_array($sqlShow);
$start_date_m2year = start_date_m2year($fromdate);

?>
<table width="100%" border="0">
    <tr>
  		<td colspan="11">LAPORAN STOCK BARANG JADI</td>
    </tr>
    <tr>
  		<td colspan="11">KAWASAN BERIKAT <?=strtoupper($dataShow['prof_name']);?></td>
    </tr>
    <tr>
  		<td colspan="11">PERIODE <?=DateToIndo($fromdate);?> S/D <?=DateToIndo($todate);?> </td>
    </tr>
    <tr>
  		<td colspan="11">&nbsp;</td>
    </tr>
</table>	
<table width="100%" border="1">	
  <tr>
	<th bgcolor="#D1D1D1" rowspan="2">No</th>
	<th bgcolor="#D1D1D1" rowspan="2">Kode Barang</th>
	<th bgcolor="#D1D1D1" rowspan="2">Nama Barang</th>
	<th bgcolor="#D1D1D1" rowspan="2">Satuan</th>
    <th bgcolor="#D1D1D1" colspan="4">Jumlah</th>
    <th bgcolor="#D1D1D1" rowspan="2">Stock</th>
    <th bgcolor="#D1D1D1" rowspan="2">Harga</th>
	<th bgcolor="#D1D1D1" rowspan="2">Nilai</th>
 </tr>
 <tr>
	<th bgcolor="#D1D1D1">Awal</th>
	<th bgcolor="#D1D1D1">Masuk</th>
	<th bgcolor="#D1D1D1">Keluar</th>
	<th bgcolor="#D1D1D1">Penyesuaian</th>	
  </tr>
  <?php
	$qh = mysql_query("SELECT * FROM product_category $s_idcat");
	while($dh=mysql_fetch_array($qh)){
  ?>
  <tr bgcolor="#92fa05">
	<td colspan="11">KATEGORI :<?=$dh['prod_cat'];?></td>
  </tr>
  <?php
	$no=1; $gtprice=0;
	$qd = mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, b.qty as beg_qty, (ifnull(c.qty,0) + ifnull(h.qty,0) + ifnull(i.qty,0)) as in_qty, (ifnull(d.qty,0)+ifnull(g.qty,0)) as out_qty,
				   e.qty as adj_qty, (ifnull(b.qty,0) + (ifnull(c.qty,0) + ifnull(h.qty,0) + ifnull(i.qty,0)) - (ifnull(d.qty,0)+ifnull(g.qty,0)) + ifnull(e.qty,0)) as end_qty	
			FROM product a             
			LEFT JOIN (SELECT stock_prodid, SUM(stock_qty) as qty FROM stock WHERE MONTH(stock_date)='$month' AND YEAR(stock_date)='$year' AND stock_status=10 AND stock_loc=3 GROUP BY stock_prodid) AS b
				   ON b.stock_prodid = a.id
				LEFT JOIN (select a.trfd_prodid, sum(a.trfd_qty) as qty from transfer_detail a 
				   inner join transfer b on b.id = a.trfd_hid
				   where b.trf_status=20 and b.trf_category IN (4) and trf_spbdate between '$fromdate' and '$todate' 
				   group by trfd_prodid) AS c ON c.trfd_prodid=a.id                                       
			LEFT JOIN (SELECT a.outd_prodid, sum(a.outd_qty) as qty from outgoing_detail a 
				   inner join outgoing b on b.id = a.outd_hid
				   where b.outh_status=20 and outh_sjdate between '$fromdate' and '$todate'
				   group by outd_prodid) AS d ON d.outd_prodid=a.id							    
			LEFT JOIN (select a.adjd_prodid, sum(adjd_qty) as qty from adjusment_detail a
				   inner join adjusment b on b.id=a.adjd_hid
				   where b.adjh_status=20 and adjh_date between '$fromdate' and '$todate'
				   group by adjd_prodid) AS e ON e.adjd_prodid=a.id
			LEFT JOIN (select a.stod_prodid, max(b.stoh_date), a.stod_qtysaldo as qty from stockopname_detail a 
				   inner join stockopname b on b.id=a.stod_hid
				   where b.stoh_status=20 and month(b.stoh_date)='$month_sto' and year(b.stoh_date)='$year_sto' 
				   group by a.stod_prodid) AS f  ON f.stod_prodid=a.id  
			LEFT JOIN (select a.trfd_prodid, sum(a.trfd_qty) as qty from transfer_detail a 
				   inner join transfer b on b.id = a.trfd_hid
				   where b.trf_status=20 and b.trf_category IN (5,6) and trf_spbdate between '$fromdate' and '$todate'
				   group by trfd_prodid) AS g ON g.trfd_prodid=a.id
			LEFT JOIN (SELECT a.incd_prodid, sum(a.incd_qty) as qty from incoming_detail a 
				   inner join incoming b on b.id = a.incd_hid
				   inner join product c on c.id = a.incd_prodid and c.prod_cat in(3,4)
				   where b.inch_status=20 and inch_bpbdate between '$fromdate' and '$todate' 
				   group by incd_prodid) AS h ON h.incd_prodid=a.id
			LEFT JOIN (SELECT kv_prodid, sum(kv_qty) as qty from konversi_external where kv_status=20 and kv_date between '$fromdate' and '$todate' 
				  group by kv_prodid) AS i ON i.kv_prodid=a.id	   
			WHERE a.prod_cat='$dh[id]' $search ORDER BY a.prod_code ASC");
	while($dd=mysql_fetch_array($qd)){		
		$price = get_unitprice_bj($start_date_m2year,$todate,$dd['id']); #get_pricebj($dd['id'],$fromdate,$todate,$inv);
		$tprice=$dd['end_qty']*$price;
		$gtprice=$gtprice+$tprice;
		if($dd['end_qty'] > 0){
	?> 
	 <tr onmouseover="setTrColor(this, '#FF9900');">
		<td align="center"><?=$no;?>.</td>
        <td align="left"><?=$dd['prod_code'];?></td>
        <td align="left"><?=$dd['prod_name'];?></td>
        <td align="center"><?=$dd['prod_sat'];?></td>
		<td align="right"><?=number_format($dd['beg_qty'],2,'.','');?></td>
		<td align="right"><?=number_format($dd['in_qty'],2,'.','');?></td>
		<td align="right"><?=number_format($dd['out_qty'],2,'.','');?></td>
		<td align="right"><?=number_format($dd['adj_qty'],2,'.','');?></td>
        <td align="right"><?=number_format($dd['end_qty'],2,'.','');?></td>
        <td align="right"><?=number_format($price,2,'.','');?></td>
        <td align="right"><?=number_format($tprice,2,'.','');?></td>
	  </tr>	  
	<?php $no++; } } } ?> 
</table>