<?php
include "../../include/config.php";
include "../../function/inc_function.php";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="data_stock_exbc_'.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);
if($month==1 && $year==2021 ){
 	$fromdate = "2021-01-20";
 }else{
 	$fromdate = $fromdate;
 }

?>
<table width="100%" border="0">
    <tr>
  		<td colspan="13">LAPORAN STOCK EXBC</td>
    </tr>    
    <tr>
  		<td colspan="13">&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">S.Akhir</th>
		<th bgcolor="#D1D1D1">Valuta</th>
		<th bgcolor="#D1D1D1">Harga</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.begqty+b.inqty)-b.outqty) endqty, b.price, b.prod_code, 
					b.prod_name, b.prod_sat, b.curr 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, a.curr  FROM
					(SELECT stock_prodid prodid, stock_doctype doctype, stock_docno docno, stock_docdate docdate, stock_qty begqty, 0 inqty, 0 outqty, stock_price price, stock_currency curr 
					FROM stock_exbc WHERE stock_status=10 AND MONTH(stock_date)='$month' AND YEAR(stock_date)='$year'
					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, CASE WHEN a.valuta='IDR' THEN (b.incd_price/b.incd_qty) ELSE (b.incd_price_usd/b.incd_qty) END AS price, a.valuta curr 
					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.obcd_prodid prodid, a.obcd_doctype doctype , a.obcd_docno docno, a.obcd_docdate docdate, 0 begqty, 0 inqty, a.obcd_qty outqty, 0 price, '' curr 
					FROM outexbc_detail a
					INNER JOIN outexbc b ON b.id=a.obcd_hid
					WHERE b.obc_status=20 AND b.obc_date 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.docdate, b.prod_code ASC");
        while($dd=mysql_fetch_array($qd)){	
        ?> 
         <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'],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['endqty'],2,'.','');?></td>
			<td align="center"><?=$dd['curr'];?></td>
			<td align="right"><?=number_format($dd['price'],2,'.',',');?></td>
          </tr>	  
      <?php $no++; }  ?> 
</table>