<?php
include "../../include/config.php";
include "../../function/inc_function.php";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="data_stock_produksi'.date('Y-m-d H:i:s').'.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(1,2,3,4,5,6,7)";}else{$s_idcat = "WHERE id=$idprodcat";}
$start_date_m2year = start_date_m2year($fromdate);
?>
<table width="100%" border="0">
    <tr>
  		<td colspan="10" align="left"><strong>DATA STOCK PRODUKSI</strong></td>
    </tr>
	<tr>
  		<td colspan="10" align="left"><strong>PERIODE : <?=$fromdate;?> s/d <?=$todate;?></strong></td>
    </tr>
</table>	
<table width="100%" border="1">	
  <tr>
	<th align="center" bgcolor="#D1D1D1" rowspan="2">No</th>
	<th align="center" bgcolor="#D1D1D1" rowspan="2">Kode Barang</th>
	<th align="center" bgcolor="#D1D1D1" rowspan="2">Nama Barang</th>
	<th align="center" bgcolor="#D1D1D1" rowspan="2">Satuan</th>
    <th align="center" bgcolor="#D1D1D1" colspan="4">Jumlah</th>
    <th align="center" bgcolor="#D1D1D1" rowspan="2">Harga</th>
	<th align="center" bgcolor="#D1D1D1" rowspan="2">Nilai</th>
  </tr>
  <tr>
  	<th align="center" bgcolor="#D1D1D1">Awal</th>
	<th align="center" bgcolor="#D1D1D1">Masuk</th>
    <th align="center" bgcolor="#D1D1D1">Keluar</th>
	<th align="center" bgcolor="#D1D1D1">Stok</th>
  </tr>
  <?php
  	$gtprice=0;
  	$qh = mysql_query("SELECT * FROM product_category $s_idcat ORDER BY prod_cat ASC");
	while($dh=mysql_fetch_array($qh)){
  ?>
  <tr bgcolor="#CCFFFF">
  	<td colspan="10">KATEGORI : <?=$dh['prod_cat'];?> </td>
  </tr>
  <?php
	$no=1; $price=0;
	$qd = mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_sat, a.prod_cat, b.stock_qty, c.trfgd_qty, d.rtngd_qty, e.wobb_qty, f.wobj_qty, g.fgd_qty, h.trfqc_qty, i.rtnqc_qty,
							  j.scr_qty, k.kvp_qty, l.kvm_qty, m.wobbbj_qty, n.kvp2_qty, o.kvm2_qty, p.wonbd_qty
						FROM product a
						LEFT JOIN (SELECT stock_prodid, SUM(stock_qty) stock_qty FROM stock WHERE stock_date='$fromdate' AND stock_status=10 
										   AND stock_loc=2 GROUP BY stock_prodid) as b ON b.stock_prodid=a.id
						LEFT JOIN (SELECT a.trfd_prodid, SUM(a.trfd_qty) trfgd_qty FROM transfer_detailexbc a INNER JOIN transfer b ON b.id=a.trfd_hid
									   WHERE b.trf_spbdate BETWEEN '$fromdate' AND '$todate' AND b.trf_status=20 AND b.trf_category=1 GROUP BY a.trfd_prodid) as c ON c.trfd_prodid=a.id
						LEFT JOIN (SELECT a.trfd_prodid, SUM(a.trfd_qty) rtngd_qty FROM transfer_detailexbc a INNER JOIN transfer b ON b.id=a.trfd_hid
									   WHERE b.trf_spbdate BETWEEN '$fromdate' AND '$todate' AND b.trf_status=20 AND b.trf_category=3 GROUP BY a.trfd_prodid) as d ON d.trfd_prodid=a.id  
						LEFT JOIN (SELECT a.wod_prodid, SUM(a.wod_qty) wobb_qty FROM workorder_detail a INNER JOIN workorder b ON b.id=a.wod_hid
									   WHERE b.wo_date BETWEEN '$fromdate' AND '$todate' AND b.wo_status=20 AND b.wo_level<>'Level FG' GROUP BY a.wod_prodid) as e ON e.wod_prodid=a.id           
						LEFT JOIN (SELECT wo_prodid, SUM(wo_qty) wobj_qty FROM workorder WHERE wo_date BETWEEN '$fromdate' AND '$todate'
								   AND wo_status=20 AND wo_level<>'Level FG' GROUP BY wo_prodid) as f ON f.wo_prodid=a.id           
						LEFT JOIN (SELECT c.id, SUM(b.fgd_qty) fgd_qty FROM finishgoods a INNER JOIN finishgoods_detail b ON b.fgd_hid=a.id
										   INNER JOIN product c ON c.id=b.fgd_prodid WHERE a.fgh_status=20 AND a.fgh_lpdate BETWEEN '$fromdate' AND '$todate' GROUP BY c.id) as g ON g.id=a.id
						LEFT JOIN (SELECT a.trfd_prodid, SUM(a.trfd_qty) trfqc_qty FROM transfer_detail a INNER JOIN transfer b ON b.id=a.trfd_hid
									   WHERE b.trf_spbdate BETWEEN '$fromdate' AND '$todate' AND b.trf_status=20 AND b.trf_category=4 GROUP BY a.trfd_prodid) as h ON h.trfd_prodid=a.id
						LEFT JOIN (SELECT a.trfd_prodid, SUM(a.trfd_qty) rtnqc_qty FROM transfer_detail a INNER JOIN transfer b ON b.id=a.trfd_hid
									   WHERE b.trf_spbdate BETWEEN '$fromdate' AND '$todate' AND b.trf_status=20 AND b.trf_category=6 GROUP BY a.trfd_prodid) as i ON i.trfd_prodid=a.id
						LEFT JOIN (SELECT c.id, SUM(b.scrd_qty) scr_qty FROM scrap a INNER JOIN scrap_detail b ON b.scrd_hid=a.id
										   INNER JOIN product c ON c.id=b.scrd_prodid WHERE a.scrh_status=20 AND a.scrh_lpdate BETWEEN '$fromdate' AND '$todate' GROUP BY c.id) as j ON j.id=a.id
						LEFT JOIN (SELECT kv_prodid, SUM(kv_qty) kvp_qty FROM konversi WHERE kv_status=20 AND kv_date BETWEEN '$fromdate' AND '$todate' GROUP BY kv_prodid) as k ON k.kv_prodid=a.id
						LEFT JOIN (SELECT a.kvd_prodid, SUM(a.kvd_qty) kvm_qty FROM konversi_detail a INNER JOIN konversi b ON b.id=a.kvd_hid 
										 WHERE b.kv_status=20 AND b.kv_date BETWEEN '$fromdate' AND '$todate'	GROUP BY a.kvd_prodid) as l ON l.kvd_prodid=a.id
						LEFT JOIN (SELECT a.wod_prodid, SUM(a.wod_qty) wobbbj_qty FROM workorder_detail a INNER JOIN workorder b ON b.id=a.wod_hid
										 WHERE b.wo_date BETWEEN '$fromdate' AND '$todate' AND b.wo_status=20 AND b.wo_level='Level FG' GROUP BY a.wod_prodid) as m ON m.wod_prodid=a.id
						LEFT JOIN (SELECT kv_prodid, SUM(kv_qty) kvp2_qty FROM konversi2 WHERE kv_status=20 AND kv_date BETWEEN '$fromdate' AND '$todate' GROUP BY kv_prodid) as n ON n.kv_prodid=a.id
						LEFT JOIN (SELECT a.kvd_prodid, SUM(a.kvd_qty) kvm2_qty FROM konversi_detail2 a INNER JOIN konversi2 b ON b.id=a.kvd_hid 
										   WHERE b.kv_status=20 AND b.kv_date BETWEEN '$fromdate' AND '$todate'	GROUP BY a.kvd_prodid) as o ON o.kvd_prodid=a.id 
						LEFT JOIN (SELECT a.wonbd_prodid, SUM(a.wonbd_qty) wonbd_qty FROM workorder_nonbom_detail a INNER JOIN workorder_nonbom b ON b.id=a.wonbd_hid 
										   WHERE b.wonb_status=20 AND b.wonb_date BETWEEN '$fromdate' AND '$todate'	GROUP BY a.wonbd_prodid) as p ON p.wonbd_prodid=a.id                               
						WHERE a.prod_cat='$dh[id]' $search AND (b.stock_qty > 0 OR c.trfgd_qty > 0 OR d.rtngd_qty > 0 OR e.wobb_qty > 0 OR f.wobj_qty > 0 OR g.fgd_qty > 0 OR h.trfqc_qty > 0 OR i.rtnqc_qty > 0 OR j.scr_qty > 0 OR
							  k.kvp_qty > 0 OR l.kvm_qty > 0 OR m.wobbbj_qty > 0 OR n.kvp2_qty > 0 OR o.kvm2_qty > 0 OR p.wonbd_qty > 0) 
						ORDER BY a.prod_code ASC");
	while($dd=mysql_fetch_array($qd)){		
		#Summary	   
		if($dd['prod_cat']==1 || $dd['prod_cat']==6 || $dd['prod_cat']==7 ){			
			$stk    = $dd['stock_qty']; 
			$in     = $dd['trfgd_qty']+$dd['kvp_qty']+$dd['kvm2_qty']; 
			$out    = $dd['rtngd_qty']+$dd['wobb_qty']+$dd['kvm_qty']+$dd['wobbbj_qty']+$dd['kvp2_qty']+$dd['wonbd_qty'];	
			$stock  = ($stk+$in)-$out;
			$price  = get_unitprice_bb($start_date_m2year,$todate,$dd['id']); 
			$tprice = $stock*$price;
		}elseif($dd['prod_cat']==3){
			$stk    = $dd['stock_qty'];
			$in     = $dd['trfgd_qty']+$dd['wobj_qty']+$dd['rtnqc_qty']+$dd['fgd_qty']+$dd['kvp_qty']+$dd['kvm2_qty'];
			$out    = $dd['trfqc_qty']+$dd['wobb_qty']+$dd['kvm_qty']+$dd['wobbbj_qty']+$dd['kvp2_qty']+$dd['wonbd_qty'];	
			$stock  = ($stk+$in)-$out;
			$price  = get_unitprice_bj($start_date_m2year,$todate,$dd['id']); 
			$tprice = $stock*$price;
		}elseif($dd['prod_cat']==5){
			$stk    = $dd['stock_qty'];
			$in     = $dd['trfgd_qty']+$dd['wobj_qty']+$dd['rtnqc_qty']+$dd['kvp_qty']+$dd['kvm2_qty'];
			$out    = $dd['trfqc_qty']+$dd['wobb_qty']+$dd['kvm_qty']+$dd['wobbbj_qty']+$dd['kvp2_qty']+$dd['wonbd_qty'];	
			$stock  = ($stk+$in)-$out;
			$price  = get_unitprice_wip($start_date_m2year,$todate,$dd['id']); 
			$tprice = $stock*$price;
		}else{
			$stk    = $dd['stock_qty'];
			$in     = $dd['scr_qty']+$dd['wobj_qty']+$dd['rtnqc_qty']+$dd['kvp_qty']+$dd['kvm2_qty'];
			$out    = $dd['trfqc_qty']+$dd['kvm_qty']+$dd['kvp2_qty']+$dd['wonbd_qty'];	
			$stock  = ($stk+$in)-$out;
			$price  = 0;
			$tprice = $stock*$price;
		}
	?> 
	 <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($stk,2,'.','');?></td>
		<td align="right"><?=number_format($in,2,'.','');?></td>
        <td align="right"><?=number_format($out,2,'.','');?></td>
        <td align="right"><?=number_format($stock,2,'.','');?></td>
        <td align="right"><?=number_format($price,2,'.','');?></td>
		<td align="right"><?=number_format($tprice,2,'.','');?></td>
	  </tr>	  
  <?php $no++; } } ?> 
</table>