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

$fromdate = $_REQUEST['fromdate'];
$todate   = $_REQUEST['todate'];
$start_date_m2year = start_date_m2year($fromdate);
$month 		= substr($fromdate,5,2);
$year		= substr($fromdate,0,4);
$year_sto   = substr($todate,0,4);
$month_sto  = substr($todate,5,2);
$cek        = $month-1;
if($cek==0){
	$bln = 12;
	$thn = $year-1;
}else{
	$bln = $cek;
	$thn = $year;
}
$thnbln = "$year$month";
$sqlShow 	= mysql_query("SELECT prof_name FROM profil");
$dataShow 	= mysql_fetch_array($sqlShow);

?>
<table width="100%" border="0">
    <tr>
  		<td colspan="17">LAPORAN STOCK BAHAN BAKU DAN BAHAN PENOLONG PER DOKUMEN</td>
    </tr>
    <tr>
  		<td colspan="17">KAWASAN BERIKAT <?=strtoupper($dataShow['prof_name']);?></td>
    </tr>
    <tr>
  		<td colspan="17">PERIODE <?=DateToIndo($fromdate);?> S/D <?=DateToIndo($todate);?> </td>
    </tr>
    <tr>
  		<td colspan="17">&nbsp;</td>
    </tr>
</table>	
<table width="100%" border="1">	
	<tr>
        <th bgcolor="#D1D1D1">No</th>
        <th bgcolor="#D1D1D1">Jenis</th>
        <th bgcolor="#D1D1D1">No. Daftar</th>
        <th bgcolor="#D1D1D1">Tgl Daftar</th>
        <th bgcolor="#D1D1D1">Kode Barang</th>
        <th bgcolor="#D1D1D1">Nama Barang</th>
        <th bgcolor="#D1D1D1">Satuan</th>
        <th bgcolor="#D1D1D1">S.Awal</th>
		<th bgcolor="#D1D1D1">Masuk</th>
        <th bgcolor="#D1D1D1">Keluar</th>
		<th bgcolor="#D1D1D1">Penyesuaian</th>
        <th bgcolor="#D1D1D1">Stock</th>
		<th bgcolor="#D1D1D1">Harga</th>		
		<th bgcolor="#D1D1D1">Nilai</th>
		<th bgcolor="#D1D1D1">Valuta</th>
		<th bgcolor="#D1D1D1">Kurs</th>
		<th bgcolor="#D1D1D1">Nilai IDR</th>
	</tr>
	<?php
		$qh = mysql_query("SELECT * FROM product_category WHERE id IN(1,5,6,7) ORDER BY id ASC");
		while($dh=mysql_fetch_array($qh)){
	?>
	<tr>
        <th bgcolor="#aaeeaa" colspan="17" align="left">KATEGORI : <?=$dh['prod_cat'];?></th>        
	</tr>
	
	<?php
        $no=1; $gtprice=0;$tendqty=0;$tamount=0;
        $qd = mysql_query("SELECT b.prodid, b.doctype, b.docno, b.docdate, b.begqty, b.inqty, b.outqty, b.adjqty, ((b.begqty+b.inqty)-b.outqty+b.adjqty) endqty,  b.price, b.prod_code, 
					b.prod_name, b.prod_sat FROM
					(SELECT a.prodid, a.doctype, a.docno, a.docdate, SUM(a.begqty) begqty, SUM(a.inqty) inqty, SUM(a.outqty) outqty, SUM(a.adj_qty) adjqty, a.price, b.prod_code, b.prod_name, b.prod_sat  FROM
					(SELECT stock_prodid prodid, stock_doctype doctype, stock_docno docno, stock_docdate docdate, stock_qty begqty, 0 inqty, 0 outqty, 0 adj_qty, (stock_price/stock_qty) price 
					FROM stock WHERE stock_status=10 AND stock_date='$fromdate' AND stock_loc IN(1,4)
					UNION ALL
					SELECT b.incd_prodid prodid, a.inch_doctype doctype , a.inch_docno docno, a.inch_docdate docdate, 0 begqty, b.incd_qty inqty, 0 outqty, 0 adj_qty, (b.incd_price/b.incd_qty) price 
					FROM incoming a
					INNER JOIN incoming_detail b ON b.incd_hid=a.id
					WHERE a.inch_status=20 AND a.inch_docdate BETWEEN '$fromdate' AND '$todate'
					UNION ALL
					SELECT a.trfd_prodid prodid, a.trfd_doctype doctype , a.trfd_docno docno, a.trfd_docdate docdate, 0 begqty, 0 inqty, a.trfd_qty outqty, 0 adj_qty, 0 price 
					FROM transfer_detailexbc a
					INNER JOIN transfer b ON b.id=a.trfd_hid
					WHERE b.trf_status=20 AND a.trfd_status=10 AND b.trf_category=1 AND b.trf_spbdate BETWEEN '$fromdate' AND '$todate'
					UNION ALL
					SELECT a.trfd_prodid prodid, a.trfd_doctype doctype , a.trfd_docno docno, a.trfd_docdate docdate, 0 begqty, a.trfd_qty inqty, 0 outqty, 0 adj_qty, 0 price 
					FROM transfer_detailexbc a
					INNER JOIN transfer b ON b.id=a.trfd_hid
					WHERE b.trf_status=20 AND a.trfd_status=10 AND b.trf_category=3 AND b.trf_spbdate BETWEEN '$fromdate' AND '$todate'
					UNION ALL
         			SELECT a.prodid, a.jenis_dokpab_asal doctype , a.no_dokpab_asal docno, a.tgl_dokpab_asal docdate, 0 begqty, 0 inqty, a.jumlah outqty, 0 adj_qty, 0 price 
					FROM outgoing_dokasal a
					INNER JOIN outgoing b ON b.id=a.outh_hid    
					INNER JOIN product c ON c.id=a.prodid AND prod_cat IN(1,6,7)
					WHERE b.outh_status=20 AND a.log_status=20 AND b.outh_docdate BETWEEN '$fromdate' AND '$todate'
					UNION ALL
         			SELECT a.adjd_prodid prodid, a.adjd_doctype doctype , a.adjd_docno docno, a.adjd_docdate docdate, 0 begqty, 0 inqty, 0 outqty, a.adjd_qty adj_qty, 0 price 
					FROM adjusment_detail a
					INNER JOIN adjusment b ON b.id=a.adjd_hid            
					WHERE b.adjh_status=20 AND b.adjh_date BETWEEN '$fromdate' AND '$todate') a
					INNER JOIN product b ON b.id=a.prodid
					WHERE b.prod_cat='$dh[id]' GROUP BY a.prodid, a.doctype, a.docno, a.docdate) b
					ORDER BY b.docno, b.prod_code ASC");
        while($dd=mysql_fetch_array($qd)){	
			$tendqty   = $tendqty+$dd['endqty'];
			$itemprice = get_unitprice_bbinc($start_date_m2year,$todate,$dd['prodid'],$dd['docdate'],$dd['docno'],$dd['doctype']);
			$curr2     = get_currency($start_date_m2year,$todate,$dd['prodid'],$dd['docdate'],$dd['docno'],$dd['doctype']);
			if(!empty($dd['curr'])){
				$curr  = $dd['curr'];
			}else{
				$curr  = $curr2;
			}				
			$amount    = $dd['endqty']*$itemprice;	
			$tamount   = $tamount+$amount;				
			$kurs	   = get_kurspajak($dd['docdate'],$curr);
			if($curr=="IDR"){
				$amount_idr = $amount;
			}else{
				$amount_idr = $amount * $kurs;
			}
        ?> 
         <tr>
            <td align="center"><?=$no;?>.</td>
            <td align="center"><?=$dd['doctype'];?></td>
            <td align="center">'<?=$dd['docno'];?></td>
            <td align="center"><?=$dd['docdate'];?></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['begqty'],2,'.','');?></td>
			<td align="right"><?=number_format($dd['inqty'],2,'.','');?></td>
            <td align="right"><?=number_format($dd['outqty'],2,'.','');?></td>
            <td align="right"><?=number_format($dd['adjqty'],2,'.','');?></td>
			<td align="right"><?=number_format($dd['endqty'],2,'.','');?></td>
            <td align="right"><?=number_format($itemprice,4,'.','');?></td>
			<td align="right"><?=number_format($amount,4,'.','');?></td>
			<td align="center"><?=$curr;?></td>
			<td align="right"><?=number_format($kurs,4,'.','');?></td>
			<td align="right"><?=number_format($amount_idr,4,'.','');?></td>
          </tr>	  
		<?php $no++; } } ?> 
</table>