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

$start_date = $_REQUEST['fromdate'];
$end_date   = $_REQUEST['todate'];
$month 		= substr($start_date,5,2);
$year		= substr($start_date,0,4);
$year_sto   = substr($end_date,0,4);
$month_sto  = substr($end_date,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);
#cek closing
$cekclg = mysql_fetch_array(mysql_query("SELECT stock_date FROM stock WHERE stock_date='$start_date' AND stock_status=10 AND stock_loc<>'' GROUP BY stock_date"));
if(empty($cekclg['stock_date'])){
	$sdate = mysql_fetch_array(mysql_query("SELECT MAX(stock_date) stock_date FROM stock WHERE stock_date<='$start_date' AND stock_status=10 AND stock_loc<>''"));
	$start_date_sa = $sdate['stock_date'];
	$end_date_sa   = date('Y-m-d', strtotime('-1 days', strtotime( $start_date )));
	$bulan 	  	   = substr($start_date_sa,5,2);
	$tahun	  	   = substr($start_date_sa,0,4);
}
$prod_cat		= isset($_REQUEST['prod_cat']) ? $_REQUEST['prod_cat'] : 1000;
	if($prod_cat==1000){
		$prodcat = "WHERE a.prod_cat IN(2,7,8)";
	}else{
		$prodcat = "WHERE a.prod_cat IN($prod_cat)";
	}
?>
<table width="100%" border="0">
    <tr>
  		<td colspan="12">LAPORAN MUTASI MESIN DAN PERALATAN</td>
    </tr>
    <tr>
  		<td colspan="12">KAWASAN BERIKAT <?=strtoupper($dataShow['prof_name']);?></td>
    </tr>
    <tr>
  		<td colspan="12">PERIODE <?=DateToIndo($start_date);?> S/D <?=DateToIndo($end_date);?> </td>
    </tr>
    <tr>
  		<td colspan="12">&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 Barang</th>
		<th bgcolor="#D1D1D1" rowspan="2">Jumlah Barang</th>
		<th bgcolor="#D1D1D1">Saldo Awal</th>
		<th bgcolor="#D1D1D1" rowspan="2">Jumlah Pemasukan Barang</th>
		<th bgcolor="#D1D1D1" rowspan="2">Jumlah Pengeluaran Barang</th>
		<th bgcolor="#D1D1D1" rowspan="2">Penyesuaian (Adjustment)</th>
		<th bgcolor="#D1D1D1">Saldo Akhir</th>
		<th bgcolor="#D1D1D1" rowspan="2">Hasil Pencacahan</th>
		<th bgcolor="#D1D1D1" rowspan="2">Jumlah Selisih</th>
		<th bgcolor="#D1D1D1" rowspan="2">Keterangan</th>
	</tr>
	<tr>
		<th align="center" bgcolor="#D1D1D1"><?=DateToIndo($start_date);?></th>
		<th align="center" bgcolor="#D1D1D1"><?=DateToIndo($end_date);?></th>
	</tr>
	<?php		
		$no=0; $tbeg_qty=0; $tin_qty=0; $tout_qty=0; $tadj_qty=0; $tend_qty=0; $tstock_opname=0; $tdif_qty=0;
		$val = valstockopname($month_sto,$year_sto,2);
		if(empty($val)){$sto =2;}else{$sto =1;}	
		#$qd = mysql_query("CALL spvw_mutasibm('$start_date','$end_date','$month','$year','$month_sto','$year_sto','$sto');");
		$qd = mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, a.prod_cat, b.qty as beg_qty, (ifnull(c.qty,0)+ifnull(h.qty,0)) as in_qty, (ifnull(d.qty,0)+ifnull(g.qty,0)) as out_qty,
			   e.qty as adj_qty, (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0)) as end_qty,
			   f.qty as stock_opname
		FROM product a            
		LEFT JOIN (SELECT stock_prodid, SUM(stock_qty) as qty FROM stock WHERE MONTH(stock_date)='$month' AND YEAR(stock_date)='$year' AND stock_status=10 AND stock_loc IN(1,4) GROUP BY stock_prodid) AS b
			   ON b.stock_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
			   where b.inch_status=20 and inch_docdate between '$start_date' and '$end_date' 
			   group by incd_prodid) AS c ON c.incd_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 '$start_date' and '$end_date'
			   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 '$start_date' and '$end_date'
			   group by adjd_prodid) AS e ON e.adjd_prodid=a.id
		LEFT JOIN (select a.stod_prodid, SUM(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 b.stoh_date='$end_date' AND b.stoh_prodcat IN(2,7,8)
			   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_detailexbc a 
			   inner join transfer b on b.id = a.trfd_hid
			   where b.trf_status=20 and b.trf_category IN (1,2) and trf_spbdate between '$start_date' and '$end_date'
			   group by trfd_prodid) AS g ON g.trfd_prodid=a.id
		LEFT JOIN (select a.trfd_prodid, sum(a.trfd_qty) as qty from transfer_detailexbc a 
			   inner join transfer b on b.id = a.trfd_hid
			   where b.trf_status=20 and b.trf_category IN (3,5) and trf_spbdate between '$start_date' and '$end_date'
			   group by trfd_prodid) AS h ON h.trfd_prodid=a.id
		$prodcat ORDER BY a.prod_code ASC;");
		while($dd=mysql_fetch_object($qd)){		
			$no++;
			#tanggal stock awal
			if(!empty($cekclg['stock_date'])){
			$beg_qty  = $dd->beg_qty;
			}else{
				$beg_qty  = get_stock($start_date_sa,$end_date_sa,$bulan,$tahun,$dd->id,$dd->prod_cat,$inv);
			}
			$end_qty = number_format($beg_qty,2,'.','')+number_format($dd->in_qty,2,'.','')-number_format($dd->out_qty,2,'.','')+number_format($dd->adj_qty,2,'.','');			
			if($sto==1){
				if((number_format($dd->stock_opname,2,'.','')- number_format($end_qty,2,'.','')) > 0){
					$ket = "Selisih Lebih";
				}elseif((number_format($dd->stock_opname,2,'.','')- number_format($end_qty,2,'.','')) < 0){
					$ket = "Selisih Kurang";
				}else{
					$ket = "Sesuai";
				}	
				$dif_qty = number_format($dd->stock_opname,2,'.','') - number_format($end_qty,2,'.','');
			}elseif($sto==2){
				if(number_format($end_qty,2,'.','') > 0){
					$ket = "Normal";
				}elseif(number_format($end_qty,2,'.','') < 0){
					$ket = "Abnormal";
				}else{
					$ket = "Normal";
				}	
				$dif_qty = 0;
			}
			#total
			$tbeg_qty = $tbeg_qty + $beg_qty;
			$tin_qty  = $tin_qty  + $dd->in_qty;
			$tout_qty = $tout_qty + $dd->out_qty;
			$tadj_qty = $tadj_qty + $dd->adj_qty;
			$tend_qty = $tend_qty + $end_qty;
			$tstock_opname = $tstock_opname + $dd->stock_opname;
			$tdif_qty = $tdif_qty + $dif_qty;
		?> 
		  <tr>
			<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="center">&nbsp;</td>
			<td align="right"><?=number_format($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($end_qty,2,'.','');?></td>
			<td align="right"><?=number_format($dd->stock_opname,2,'.','');?></td>
			<td align="right"><?=number_format($dif_qty,2,'.','');?></td>
			<td align="left"><?=$ket;?></td>
		  </tr>
      <?php } ?>
	  <tr>
		<td bgcolor="#CCFFFF" colspan="5" align="center">TOTAL</td>
		<td bgcolor="#CCFFFF" align="right"><?=number_format($tbeg_qty,2,'.','');?></td>
		<td bgcolor="#CCFFFF" align="right"><?=number_format($tin_qty,2,'.','');?></td>
		<td bgcolor="#CCFFFF" align="right"><?=number_format($tout_qty,2,'.','');?></td>
		<td bgcolor="#CCFFFF" align="right"><?=number_format($tadj_qty,2,'.','');?></td>
		<td bgcolor="#CCFFFF" align="right"><?=number_format($tend_qty,2,'.','');?></td>
		<td bgcolor="#CCFFFF" align="right"><?=number_format($tstock_opname,2,'.','');?></td>
		<td bgcolor="#CCFFFF" align="right"><?=number_format($tdif_qty,2,'.','');?></td>
		<td bgcolor="#CCFFFF">&nbsp;</td>
	</tr>
</table>