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

$fromdate = $_REQUEST['fromdate'];
$todate   = $_REQUEST['todate'];
$codename	= isset($_REQUEST['codename']) ? $_REQUEST['codename'] : '';
if(!empty($codename)){
	$search	= "AND CONCAT_WS('-',a.prod_code,a.prod_name) LIKE '%$codename%'";
}else{$search	= "";}
$month 		= substr($fromdate,5,2);
$year		= substr($fromdate,0,4);
$thnbln 	= "$year$month";
$year_sto  	= substr($todate,0,4);
$month_sto 	= substr($todate,5,2);
$start_date_m2year = start_date_m2year($fromdate);
$sqlShow 	= mysql_query("SELECT prof_name FROM profil");
$dataShow 	= mysql_fetch_array($sqlShow);
?>
<table width="100%" border="0">
    <tr>
  		<td colspan="6">LAPORAN POSISI WIP</td>
    </tr>
    <tr>
  		<td colspan="6">KAWASAN BERIKAT <?=strtoupper($dataShow['prof_name']);?></td>
    </tr>
    <tr>
  		<td colspan="6">PERIODE <?=DateToIndo($fromdate);?> S/D <?=DateToIndo($todate);?> </td>
    </tr>
    <tr>
  		<td colspan="6">&nbsp;</td>
    </tr>
</table>	
<table width="100%" border="1">	
  <tr height="30px;">
	<th align="center" bgcolor="#D1D1D1">No</th>
	<th align="center" bgcolor="#D1D1D1">Kode Barang</th>
	<th align="center" bgcolor="#D1D1D1">Nama Barang</th>
	<th align="center" bgcolor="#D1D1D1">Satuan</th>
    <th align="center" bgcolor="#D1D1D1">Jumlah</th>
	<th align="center" bgcolor="#D1D1D1">Keterangan</th>
  </tr>
  <?php
  	$gtprice=0;
  	$qh = mysql_query("SELECT * FROM product_category WHERE id IN(1,3,6,4,5,7) ORDER BY prod_cat ASC");
	while($dh=mysql_fetch_array($qh)){
  ?>
  <tr bgcolor="#CCFFFF">
  	<td colspan="8">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  = (number_format($stk,2,'.','')+number_format($in,2,'.',''))-number_format($out,2,'.','');
			$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  = (number_format($stk,2,'.','')+number_format($in,2,'.',''))-number_format($out,2,'.','');
			$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  = (number_format($stk,2,'.','')+number_format($in,2,'.',''))-number_format($out,2,'.','');
			$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  = (number_format($stk,2,'.','')+number_format($in,2,'.',''))-number_format($out,2,'.','');
			$price  = 0;
			$tprice = $stock*$price;
		}
		if($stock > 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($stock,2,'.','');?></td>
		<td align="left">Barang dalam proses</td>
	  </tr>	  
		<?php $no++; } } } ?> 
</table>