<?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="9">LAPORAN STOCK FG/WIP/SCRAP</td>
	</tr>
	<tr>
		<td colspan="9">KAWASAN BERIKAT <?= strtoupper($dataShow['prof_name']); ?></td>
	</tr>
	<tr>
		<td colspan="9">PERIODE <?= DateToIndo($fromdate); ?> S/D <?= DateToIndo($todate); ?> </td>
	</tr>
	<tr>
		<td colspan="9">&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="5">Jumlah</th>
		<th bgcolor="#D1D1D1" rowspan="2">Stock</th>
	</tr>
	<tr>
		<th bgcolor="#D1D1D1">Awal</th>
		<th bgcolor="#D1D1D1">Masuk</th>
		<th bgcolor="#D1D1D1">Keluar</th>
		<th bgcolor="#D1D1D1">Penyesuaian</th>
		<th bgcolor="#D1D1D1">Temporary</th>
	</tr>
	<?php
	$qh = mysql_query("SELECT * FROM product_category $s_idcat");
	while ($dh = mysql_fetch_array($qh)) {
	?>
		<tr bgcolor="#92fa05">
			<td colspan="10">KATEGORI :<?= $dh['prod_cat']; ?></td>
		</tr>
		<?php
		$no = 1;
		$gtprice = 0;
		if($dh['id']==3){
			$qd = mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, b.qty as beg_qty, e.qty as adj_qty, 
			ifnull(k.qty,0) as tpin_qty, ifnull(l.qty,0) as tpout_qty, 
			(ifnull(c.qty,0) + ifnull(h.qty,0) + ifnull(i.qty,0)) as in_qty, 
			(ifnull(d.qty,0) + ifnull(g.qty,0)+ ifnull(j.qty,0)) as out_qty,
			((ifnull(b.qty,0) + ifnull(c.qty,0) + ifnull(h.qty,0) + ifnull(i.qty,0)) - ifnull(l.qty,0) + ifnull(e.qty,0)) as temp_qty,
			(ifnull(k.qty,0) - (ifnull(d.qty,0) + ifnull(g.qty,0)+ ifnull(j.qty,0))) as end_qty, a.prod_cat
			
			FROM product a             

			LEFT JOIN (SELECT stock_prodid, SUM(stock_qty) as qty 
			FROM stock 
			WHERE stock_date='$fromdate' 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_docdate 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 prod_cat IN(3,4)
			where b.inch_status=20 and inch_docdate 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

			LEFT JOIN (SELECT a.kvd_prodid, sum(a.kvd_qty) as qty 
			from konversi_external_detail a 
			INNER JOIN konversi_external 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 j ON j.kvd_prodid=a.id

			LEFT JOIN (SELECT kv_prodid, sum(kv_qty) as qty 
			from konversi_barangjadi 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) as qty 
			from konversi_barangjadi_detail a 
			INNER JOIN konversi_barangjadi 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

			
			WHERE a.prod_cat='$dh[id]' $search ORDER BY a.prod_code ASC");
		}else{
			$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)+ifnull(j.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(j.qty,0)) + ifnull(e.qty,0)) as end_qty, 0 as temp_qty, a.prod_cat	
			FROM product a             
			LEFT JOIN (SELECT stock_prodid, SUM(stock_qty) as qty FROM stock WHERE stock_date='$fromdate' 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_docdate 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 prod_cat IN(3,4)
				   where b.inch_status=20 and inch_docdate 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
			LEFT JOIN (SELECT a.kvd_prodid, sum(a.kvd_qty) as qty from konversi_external_detail a INNER JOIN konversi_external 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 j ON j.kvd_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['prod_cat']==3 && $dd['temp_qty'] > 0) || ($dd['prod_cat']!=3 && $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['temp_qty'], 2, '.', ''); ?></td>
					<td align="right"><?= number_format($dd['end_qty'], 2, '.', ''); ?></td>
				</tr>
	<?php $no++;
			}
		}
	} ?>
</table>