<?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'];
$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="12">LAPORAN STOCK BAHAN BAKU DAN BAHAN PENOLONG PER DOKUMEN</td>
    </tr>
    <tr>
  		<td colspan="12">KAWASAN BERIKAT <?=strtoupper($dataShow['prof_name']);?></td>
    </tr>
    <tr>
  		<td colspan="12">PERIODE <?=DateToIndo($fromdate);?> S/D <?=DateToIndo($todate);?> </td>
    </tr>
    <tr>
  		<td colspan="12">&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">Stock</th>
		<th bgcolor="#D1D1D1">Nilai</th>
	</tr>
	<?php
        $no=1; $gtprice=0;
        $qd = mysql_query("SELECT b.prodid, b.doctype, b.docno, b.docdate, b.begqty, b.inqty, b.outqty, ((b.begqty+b.inqty)-b.outqty) 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, 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, (stock_price/stock_qty) price 
					FROM stock WHERE stock_status=10 AND stock_date='$fromdate' AND stock_loc=1
					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, (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 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 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 price 
					FROM outgoing_dokasal a
					INNER JOIN outgoing b ON b.id=a.outh_hid            
					WHERE b.outh_status=20 AND a.log_status=20 AND b.outh_docdate BETWEEN '$fromdate' AND '$todate') a
					INNER JOIN product b ON b.id=a.prodid
					WHERE b.prod_cat IN(1,5,6) 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)){	
			$amount = $dd['endqty']*$dd['price'];	
        ?> 
         <tr onmouseover="setTrColor(this, '#FF9900');">
            <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'],5,'.','');?></td>
			<td align="right"><?=number_format($dd['inqty'],5,'.','');?></td>
            <td align="right"><?=number_format($dd['outqty'],5,'.','');?></td>
            <td align="right"><?=number_format($dd['endqty'],5,'.','');?></td>
            <td align="right"><?=number_format($amount,5,'.','');?></td>
          </tr>	  
      <?php $no++; }  ?> 
</table>