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

$prodid 	= $_REQUEST['prodid'];
$start_date = $_REQUEST['start_date'];
$end_date   = $_REQUEST['end_date'];	
?>
<table width="100%" border="1">
    <tr>
  		<td align="center"><strong>DATA HISTORY BARANG</td>
    </tr>	
</table>
<br>
<!-- Master Barang -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="5">1. Master</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Kode Barang</th>
		<th align="center">Nama Barang</th>
		<th align="center">Satuan</th>
		<th align="center">Kategori</th>
		<th align="center">HS Code</th>
	</tr>
	<?php 
		$qm = mysql_query("SELECT a.*, b.prod_cat category FROM product a INNER JOIN product_category b ON b.id=a.prod_cat WHERE a.id='$prodid'");
		while($dm=mysql_fetch_array($qm)){
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dm['prod_code'];?></td>
		<td align="center"><?=$dm['prod_name'];?></td>
		<td align="center"><?=$dm['prod_sat'];?></td>
		<td align="center"><?=$dm['category'];?></td>
		<td align="center"><?=$dm['hscode'];?></td>
	</tr>
		<?php } ?>
</table>
<br>
<!-- Purchase Order -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="4">2. Sales Order</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Customer</th>
		<th align="center">Jumlah</th>
	</tr>
	<?php $tqtypo = 0;
		$qpo = mysql_query("SELECT a.pod_prodid, b.po_no, b.po_date, a.pod_qty, d.cust_name FROM purchase_order_detail a
		INNER JOIN purchase_order b ON b.po_no=a.po_no LEFT JOIN customer d ON d.id=b.po_idcust
		WHERE b.po_status<>100 AND a.pod_status=10 AND a.pod_prodid='$prodid' AND b.po_type='Sales' AND b.po_date BETWEEN '$start_date' AND '$end_date' ORDER BY b.po_no DESC");
		while($dpo=mysql_fetch_array($qpo)){
			$tqtypo = $tqtypo + $dpo['pod_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dpo['po_no'];?></td>
		<td align="center"><?=$dpo['po_date'];?></td>
		<td align="left"><?=$dpo['cust_name'];?></td>
		<td align="right"><?=number_format($dpo['pod_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="3">TOTAL</td>
		<td align="right"><?=number_format($tqtypo,5,'.','');?></td>
	</tr>	
</table>
<br />
<!-- Saldo Awal -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="4">3. Saldo Awal</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Jenis</th>
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
	</tr>
	<?php $tinc = 0;
		$qinc = mysql_query("SELECT * FROM stock WHERE stock_prodid='$prodid' AND stock_status=10 AND stock_loc=3 AND stock_date='$start_date' ORDER BY stock_docdate ASC");
		while($dinc=mysql_fetch_array($qinc)){
			$tinc = $tinc + $dinc['stock_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dinc['stock_doctype'];?></td>
		<td align="center"><?=$dinc['stock_docno'];?></td>
		<td align="center"><?=$dinc['stock_docdate'];?></td>
		<td align="right"><?=number_format($dinc['stock_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="3">TOTAL</td>
		<td align="right"><?=number_format($tinc,5,'.','');?></td>
	</tr>	
</table>
<br>
<!-- Pemasukan -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="4">4. Pemasukan</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Jenis</th>
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
	</tr>
	<?php $tinc = 0;
		$qinc = mysql_query("SELECT a.inch_doctype, a.inch_docno, a.inch_docdate, b.incd_qty FROM incoming a INNER JOIN incoming_detail b ON b.incd_hid=a.id 
		WHERE a.inch_status=20 AND a.inch_doctype NOT IN('NONDOK','NONDOKUMEN','NONCUSTOMS') AND b.incd_prodid='$prodid' AND a.inch_docdate BETWEEN '$start_date' AND '$end_date' ORDER BY a.inch_docdate DESC");
		while($dinc=mysql_fetch_array($qinc)){
			$tinc = $tinc + $dinc['incd_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dinc['inch_doctype'];?></td>
		<td align="center"><?=$dinc['inch_docno'];?></td>
		<td align="center"><?=$dinc['inch_docdate'];?></td>
		<td align="right"><?=number_format($dinc['incd_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="3">TOTAL</td>
		<td align="right"><?=number_format($tinc,5,'.','');?></td>
	</tr>	
</table>
<br>
<!-- Workorder 1 -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="3">5. Workorder 1 (WIP)</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
	</tr>
	<?php $two = 0;
		$qwo = mysql_query("SELECT a.wo_no, a.wo_date, a.wo_qty FROM workorder a
		WHERE a.wo_status=20 AND a.wo_prodid='$prodid' AND a.wo_level='Level1' 
		AND a.wo_date BETWEEN '$start_date' AND '$end_date' ORDER BY a.wo_date DESC");
		while($dwo=mysql_fetch_array($qwo)){
			$two = $two + $dwo['wo_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dwo['wo_no'];?></td>
		<td align="center"><?=$dwo['wo_date'];?></td>
		<td align="right"><?=number_format($dwo['wo_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="2">TOTAL</td>
		<td align="right"><?=number_format($two,5,'.','');?></td>
	</tr>	
</table>
<br>
<!-- Workorder 2 -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="3">6. Workorder 2 (F/G)</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
	</tr>
	<?php $two = 0;
		$qwo = mysql_query("SELECT a.wo_no, a.wo_date, a.wo_qty FROM workorder a
		WHERE a.wo_status=20 AND a.wo_prodid='$prodid' AND a.wo_level='Level2' 
		AND a.wo_date BETWEEN '$start_date' AND '$end_date' ORDER BY a.wo_date DESC");
		while($dwo=mysql_fetch_array($qwo)){
			$two = $two + $dwo['wo_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dwo['wo_no'];?></td>
		<td align="center"><?=$dwo['wo_date'];?></td>
		<td align="right"><?=number_format($dwo['wo_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="2">TOTAL</td>
		<td align="right"><?=number_format($two,5,'.','');?></td>
	</tr>	
</table>
<br>
<!-- Konversi 1 -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="7">7. Konvseri : Konversi 1</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
		<th align="center">Kode Brg Tujuan</th>
		<th align="center">Nama Brg Tujuan</th>
		<th align="center">Satuan Brg Tujuan</th>
		<th align="center">Jumlah Brg Tujuan</th>
	</tr>
	<?php $two = 0;
		$qwo = mysql_query("SELECT a.kv_no, a.kv_date, c.kvd_qty, a.kv_qty, d.prod_code, d.prod_name, d.prod_sat
		FROM konversi a INNER JOIN konversi_detail c ON c.kvd_hid=a.id INNER JOIN product d ON d.id=a.kv_prodid
		WHERE a.kv_status=20 AND c.kvd_prodid='$prodid' AND a.kv_type='KONVERSI' AND a.kv_date BETWEEN '$start_date' AND '$end_date' ORDER BY a.kv_date DESC");
		while($dwo=mysql_fetch_array($qwo)){
			$two = $two + $dwo['kvd_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dwo['kv_no'];?></td>
		<td align="center"><?=$dwo['kv_date'];?></td>
		<td align="right"><?=number_format($dwo['kvd_qty'],5,'.','');?></td>
		<td align="center"><?=$dwo['prod_code'];?></td>
		<td align="left"><?=$dwo['prod_name'];?></td>
		<td align="center"><?=$dwo['prod_sat'];?></td>
		<td align="right"><?=number_format($dwo['kv_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="2">TOTAL</td>
		<td align="right"><?=number_format($two,5,'.','');?></td>
		<td colspan="4">&nbsp;</td>
	</tr>	
</table>
<br>
<!-- Konversi 2 -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="7">8. Konvseri : Konversi 2</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
		<th align="center">Kode Brg Tujuan</th>
		<th align="center">Nama Brg Tujuan</th>
		<th align="center">Satuan Brg Tujuan</th>
		<th align="center">Jumlah Brg Tujuan</th>
	</tr>
	<?php $two = 0;
		$qwo = mysql_query("SELECT a.kv_no, a.kv_date, c.kvd_qty, a.kv_qty, d.prod_code, d.prod_name, d.prod_sat
		FROM konversi2 a INNER JOIN konversi_detail2 c ON c.kvd_hid=a.id INNER JOIN product d ON d.id=c.kvd_prodid
		WHERE a.kv_status=20 AND c.kvd_prodid='$prodid' AND a.kv_type='KONVERSI' AND a.kv_date BETWEEN '$start_date' AND '$end_date' ORDER BY a.kv_date DESC");
		while($dwo=mysql_fetch_array($qwo)){
			$two = $two + $dwo['kv_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dwo['kv_no'];?></td>
		<td align="center"><?=$dwo['kv_date'];?></td>
		<td align="right"><?=number_format($dwo['kv_qty'],5,'.','');?></td>
		<td align="center"><?=$dwo['prod_code'];?></td>
		<td align="left"><?=$dwo['prod_name'];?></td>
		<td align="center"><?=$dwo['prod_sat'];?></td>
		<td align="right"><?=number_format($dwo['kvd_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="2">TOTAL</td>
		<td align="right"><?=number_format($two,5,'.','');?></td>
		<td colspan="4">&nbsp;</td>
	</tr>	
</table>
<br>
<!-- Scrap -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="7">9. Pemakaian : Scrap</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
		<th align="center">Kode Brg Tujuan</th>
		<th align="center">Nama Brg Tujuan</th>
		<th align="center">Satuan Brg Tujuan</th>
		<th align="center">Jumlah Brg Tujuan</th>
	</tr>
	<?php $two = 0;
		$qwo = mysql_query("SELECT a.kv_no, a.kv_date, c.kvd_qty, a.kv_qty, d.prod_code, d.prod_name, d.prod_sat
		FROM konversi a INNER JOIN konversi_detail c ON c.kvd_hid=a.id INNER JOIN product d ON d.id=a.kv_prodid
		WHERE a.kv_status=20 AND c.kvd_prodid='$prodid' AND a.kv_type='SCRAP' AND a.kv_date BETWEEN '$start_date' AND '$end_date' ORDER BY a.kv_date DESC");
		while($dwo=mysql_fetch_array($qwo)){
			$two = $two + $dwo['kvd_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dwo['kv_no'];?></td>
		<td align="center"><?=$dwo['kv_date'];?></td>
		<td align="right"><?=number_format($dwo['kvd_qty'],5,'.','');?></td>
		<td align="center"><?=$dwo['prod_code'];?></td>
		<td align="left"><?=$dwo['prod_name'];?></td>
		<td align="center"><?=$dwo['prod_sat'];?></td>
		<td align="right"><?=number_format($dwo['kv_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="2">TOTAL</td>
		<td align="right"><?=number_format($two,5,'.','');?></td>
		<td colspan="4">&nbsp;</td>
	</tr>	
</table>
<br>
<!-- Transfer FG -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="3">10. Transfer ke F/G</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
	</tr>
	<?php $ttrf= 0;
		$qtrf = mysql_query("SELECT a.trf_spbno, a.trf_spbdate, b.trfd_qty FROM transfer a INNER JOIN transfer_detail b ON b.trfd_hid=a.id 
		WHERE a.trf_status=20 AND trf_category=4 AND b.trfd_prodid='$prodid' AND a.trf_spbdate BETWEEN '$start_date' AND '$end_date' ORDER BY a.trf_spbdate DESC");
		while($dtrf=mysql_fetch_array($qtrf)){
			$ttrf = $ttrf + $dtrf['trfd_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dtrf['trf_spbno'];?></td>
		<td align="center"><?=$dtrf['trf_spbdate'];?></td>
		<td align="right"><?=number_format($dtrf['trfd_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="2">TOTAL</td>
		<td align="right"><?=number_format($ttrf,5,'.','');?></td>
	</tr>	
</table>
<br>
<!-- Return Produksi -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="3">11. Return dari F/G</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
	</tr>
	<?php $ttrf= 0;
		$qtrf = mysql_query("SELECT a.trf_spbno, a.trf_spbdate, b.trfd_qty FROM transfer a INNER JOIN transfer_detail b ON b.trfd_hid=a.id 
		WHERE a.trf_status=20 AND trf_category=6 AND b.trfd_prodid='$prodid' AND a.trf_spbdate BETWEEN '$start_date' AND '$end_date' ORDER BY a.trf_spbdate DESC");
		while($dtrf=mysql_fetch_array($qtrf)){
			$ttrf = $ttrf + $dtrf['trfd_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dtrf['trf_spbno'];?></td>
		<td align="center"><?=$dtrf['trf_spbdate'];?></td>
		<td align="right"><?=number_format($dtrf['trfd_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="2">TOTAL</td>
		<td align="right"><?=number_format($ttrf,5,'.','');?></td>
	</tr>	
</table>
<br>
<!-- Pengeluaran -->
<table width="100%" border="1" bgcolor="#000">	
	<tr bgcolor="#ccffff">
		<td colspan="4">12. Pengeluaran : dengan dokumen Beacukai</td>
	</tr>
	<tr bgcolor="#D1D1D1">
		<th align="center">Jenis</th>
		<th align="center">Nomor</th>
		<th align="center">Tanggal</th>
		<th align="center">Jumlah</th>
	</tr>
	<?php $tout = 0;
		$qout = mysql_query("SELECT a.outh_doctype, a.outh_docno, a.outh_docdate, b.outd_qty FROM outgoing a INNER JOIN outgoing_detail b ON b.outd_hid=a.id 
		WHERE a.outh_status=20 AND a.outh_doctype NOT IN('NONDOK','NONDOKUMEN','SCRAP') AND b.outd_prodid='$prodid' AND a.outh_docdate BETWEEN '$start_date' AND '$end_date' ORDER BY a.outh_docdate DESC");
		while($dout=mysql_fetch_array($qout)){
			$tout = $tout + $dout['outd_qty'];
	?>
	<tr bgcolor="#fff">
		<td align="center"><?=$dout['outh_doctype'];?></td>
		<td align="center"><?=$dout['outh_docno'];?></td>
		<td align="center"><?=$dout['outh_docdate'];?></td>
		<td align="right"><?=number_format($dout['outd_qty'],5,'.','');?></td>
	</tr>
		<?php } ?>
	<tr bgcolor="#ffffcc">
		<td align="right" colspan="3">TOTAL</td>
		<td align="right"><?=number_format($tout,5,'.','');?></td>
	</tr>	
</table>