<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" href="function/dataTables/media/css/demo_page.css">
<link rel="stylesheet" href="function/dataTables/media/css/demo_table_jui.css">
<link rel="stylesheet" href="function/dataTables/media/themes/smoothness/jquery-ui-1.8.4.custom.css">
<script type="text/javascript" src="function/dataTables/media/js/jquery.dataTables.js"></script>
<script type="text/javascript" language="javascript">
	$(document).ready(function(){ 
		$("#fromdate").datepicker({
			dateFormat : "yy-mm-dd",
			changeMonth : true,
			changeYear : true,
		});
	});
	$(document).ready(function(){
		$("#todate").datepicker({
			dateFormat : "yy-mm-dd",
			changeMonth : true,
			changeYear : true,
		});
	});
	$(document).ready( function () {
		var oTable = $('#myTables').dataTable( {
			 "sDom": 'R<"H"lfr>t<"F"ip>',
			 "bJQueryUI": true,
			 "sPaginationType": "full_numbers",
			 "iDisplayLength" :10
		});
	});
	function setTrColor(obj, color)
	{
		obj.style.backgroundColor=color;
		obj.onmouseout = function(){
				obj.style.backgroundColor=''; 
		}
	}
	function search_click(){
		form.action ='?page=Data-Stock-WIP&Act=CARI';
		form.method = 'post';
		form.submit();			
		return true;
	}
	function excel_click(){
		form.action ='./transaksi/manual/excel_stock_wip.php';
		form.method = 'post';
		form.submit();			
		return true;
	}
	/*function pdf_click(){
		form.action ='./transaksi/akuntansi/pdf_pembelian.php';
		form.method = 'post';
		form.submit();			
		return true;
	}*/
</script>
<?php
$fromdate	= isset($_REQUEST['fromdate']) ? $_REQUEST['fromdate'] : $start_date;
$todate     = isset($_REQUEST['todate']) ? $_REQUEST['todate'] : $end_date; 
$codename	= isset($_REQUEST['codename']) ? $_REQUEST['codename'] : '';
$idprodcat	= isset($_REQUEST['idprodcat']) ? $_REQUEST['idprodcat'] : 1000;
if(!empty($codename)){
	$search	= "AND CONCAT_WS('-',a.prod_code,a.prod_name) LIKE '%$codename%'";
}else{$search	= "";}

if($idprodcat =="ALL"){$s_idcat = "WHERE id IN(1,2,3,4,5,6,7)";}else{$s_idcat = "WHERE id=$idprodcat";}

$month 		= substr($fromdate,5,2);
$year		= substr($fromdate,0,4);
$thnbln 	= "$year$month";
$year_sto  	= substr($todate,0,4);
$month_sto 	= substr($todate,5,2);
$start_date_m2year = start_date_m2year($fromdate);
?>
</head>
<body>
<form action="#" method="post" name="form">
<table width="100%" border="0" cellspacing="1" cellpadding="1">
  <tr>
	<td colspan="10" align="center"><h1><b>DATA STOCK PRODUKSI</b></h1></td>
  </tr>
  <tr>
	<td colspan="10">Periode :
	     <!--<select id="bulan" name="bulan">
		   <?php
			$bln = array('','Januari','Februari','Maret','April','Mei','Juni','Juli','Agustus','September','Oktober','November','Desember');
			for($n=1; $n<=12; $n++){
			if ($n == $bulan) {
				$cek = " selected";
			} else { $cek=""; }
		   ?>
		   	<option value="<?=$n;?>" <?=$cek;?>><?=$bln[$n];?></option>
		   <?php } ?>
		  </select>
		  <input type="text" id="tahun" name="tahun" size="4" value="<?=$tahun;?>">-->
		  <input type="text" id="fromdate" name="fromdate" size="9" value="<?=$fromdate;?>" style="border-collapse:inherit"  readonly="true">&nbsp;s/d&nbsp;
	      <input type="text" id="todate" name="todate" size="9" value="<?=$todate;?>" style="border-collapse:inheri" readonly="true">
		  <select name="idprodcat" class="form_select">
				<option value="BLANK" selected="selected">PILIH</option>
				<?php
				  $dataSql = mysql_query("SELECT * FROM product_category WHERE id IN(1,2,3,4,5,6,7) ORDER BY id ASC");
				  while ($dataRow = mysql_fetch_array($dataSql)) {
					if ($dataRow['id']== $idprodcat) {
						$cek = " selected";
					} else { $cek=""; }
					echo "<option value='$dataRow[id]' $cek>$dataRow[prod_cat]</option>";
				  }
				  $sqlData ="";
				?>
				<option value="ALL">SEMUA</option>
		  </select>
		  <input type="text" name="codename" size="30" value="<?=$codename;?>">
	      <input type="button" name="cari" value="CARI" style="cursor:pointer" onclick="search_click();">
		  <input type="button" name="export" value="EXCEL" onClick="excel_click();" style="cursor:pointer" />
		  <!--<input type="button" name="pdf" value="Export to PDF" style="cursor:pointer" onClick="pdf_click();"/>-->
	</td>		
  </tr>
</table>  
<div class="scroll">
<table id="myTables" class="display" cellpadding="0" cellspacing="0">
  <tr height="30px;">
	<th rowspan="2">No</th>
	<th rowspan="2">Kode Barang</th>
	<th rowspan="2">Nama Barang</th>
	<th rowspan="2">Satuan</th>
    <th colspan="4">Jumlah</th>
    <th rowspan="2">Harga</th>
	<th rowspan="2">Nilai</th>
  </tr>
  <tr>
  	<th>Awal</th>
	<th>Masuk</th>
    <th>Keluar</th>
	<th>Stok</th>
  </tr>
  <?php
  	$gtprice=0;
  	$qh = mysql_query("SELECT * FROM product_category $s_idcat ORDER BY prod_cat ASC");
	while($dh=mysql_fetch_array($qh)){
  ?>
  <tr bgcolor="#CCFFFF">
  	<td colspan="10">KATEGORI : <?=$dh['prod_cat'];?> </td>
  </tr>
  <?php
	$no=1; $price=0;
	$qd = mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_sat, a.prod_cat, b.stock_qty, c.trfgd_qty, d.rtngd_qty, e.wobb_qty, f.wobj_qty, g.fgd_qty, h.trfqc_qty, i.rtnqc_qty,
							  j.scr_qty, k.kvp_qty, l.kvm_qty, m.wobbbj_qty, n.kvp2_qty, o.kvm2_qty, p.wonbd_qty
						FROM product a
						LEFT JOIN (SELECT stock_prodid, SUM(stock_qty) stock_qty FROM stock WHERE stock_date='$fromdate' AND stock_status=10 
										   AND stock_loc=2 GROUP BY stock_prodid) as b ON b.stock_prodid=a.id
						LEFT JOIN (SELECT a.trfd_prodid, SUM(a.trfd_qty) trfgd_qty FROM transfer_detailexbc a INNER JOIN transfer b ON b.id=a.trfd_hid
									   WHERE b.trf_spbdate BETWEEN '$fromdate' AND '$todate' AND b.trf_status=20 AND b.trf_category=1 GROUP BY a.trfd_prodid) as c ON c.trfd_prodid=a.id
						LEFT JOIN (SELECT a.trfd_prodid, SUM(a.trfd_qty) rtngd_qty FROM transfer_detailexbc a INNER JOIN transfer b ON b.id=a.trfd_hid
									   WHERE b.trf_spbdate BETWEEN '$fromdate' AND '$todate' AND b.trf_status=20 AND b.trf_category=3 GROUP BY a.trfd_prodid) as d ON d.trfd_prodid=a.id  
						LEFT JOIN (SELECT a.wod_prodid, SUM(a.wod_qty) wobb_qty FROM workorder_detail a INNER JOIN workorder b ON b.id=a.wod_hid
									   WHERE b.wo_date BETWEEN '$fromdate' AND '$todate' AND b.wo_status=20 AND b.wo_level<>'Level FG' GROUP BY a.wod_prodid) as e ON e.wod_prodid=a.id           
						LEFT JOIN (SELECT wo_prodid, SUM(wo_qty) wobj_qty FROM workorder WHERE wo_date BETWEEN '$fromdate' AND '$todate'
								   AND wo_status=20 AND wo_level<>'Level FG' GROUP BY wo_prodid) as f ON f.wo_prodid=a.id           
						LEFT JOIN (SELECT c.id, SUM(b.fgd_qty) fgd_qty FROM finishgoods a INNER JOIN finishgoods_detail b ON b.fgd_hid=a.id
										   INNER JOIN product c ON c.id=b.fgd_prodid WHERE a.fgh_status=20 AND a.fgh_lpdate BETWEEN '$fromdate' AND '$todate' GROUP BY c.id) as g ON g.id=a.id
						LEFT JOIN (SELECT a.trfd_prodid, SUM(a.trfd_qty) trfqc_qty FROM transfer_detail a INNER JOIN transfer b ON b.id=a.trfd_hid
									   WHERE b.trf_spbdate BETWEEN '$fromdate' AND '$todate' AND b.trf_status=20 AND b.trf_category=4 GROUP BY a.trfd_prodid) as h ON h.trfd_prodid=a.id
						LEFT JOIN (SELECT a.trfd_prodid, SUM(a.trfd_qty) rtnqc_qty FROM transfer_detail a INNER JOIN transfer b ON b.id=a.trfd_hid
									   WHERE b.trf_spbdate BETWEEN '$fromdate' AND '$todate' AND b.trf_status=20 AND b.trf_category=6 GROUP BY a.trfd_prodid) as i ON i.trfd_prodid=a.id
						LEFT JOIN (SELECT c.id, SUM(b.scrd_qty) scr_qty FROM scrap a INNER JOIN scrap_detail b ON b.scrd_hid=a.id
										   INNER JOIN product c ON c.id=b.scrd_prodid WHERE a.scrh_status=20 AND a.scrh_lpdate BETWEEN '$fromdate' AND '$todate' GROUP BY c.id) as j ON j.id=a.id
						LEFT JOIN (SELECT kv_prodid, SUM(kv_qty) kvp_qty FROM konversi WHERE kv_status=20 AND kv_date BETWEEN '$fromdate' AND '$todate' GROUP BY kv_prodid) as k ON k.kv_prodid=a.id
						LEFT JOIN (SELECT a.kvd_prodid, SUM(a.kvd_qty) kvm_qty FROM konversi_detail a INNER JOIN konversi b ON b.id=a.kvd_hid 
										 WHERE b.kv_status=20 AND b.kv_date BETWEEN '$fromdate' AND '$todate'	GROUP BY a.kvd_prodid) as l ON l.kvd_prodid=a.id
						LEFT JOIN (SELECT a.wod_prodid, SUM(a.wod_qty) wobbbj_qty FROM workorder_detail a INNER JOIN workorder b ON b.id=a.wod_hid
										 WHERE b.wo_date BETWEEN '$fromdate' AND '$todate' AND b.wo_status=20 AND b.wo_level='Level FG' GROUP BY a.wod_prodid) as m ON m.wod_prodid=a.id
						LEFT JOIN (SELECT kv_prodid, SUM(kv_qty) kvp2_qty FROM konversi2 WHERE kv_status=20 AND kv_date BETWEEN '$fromdate' AND '$todate' GROUP BY kv_prodid) as n ON n.kv_prodid=a.id
						LEFT JOIN (SELECT a.kvd_prodid, SUM(a.kvd_qty) kvm2_qty FROM konversi_detail2 a INNER JOIN konversi2 b ON b.id=a.kvd_hid 
										   WHERE b.kv_status=20 AND b.kv_date BETWEEN '$fromdate' AND '$todate'	GROUP BY a.kvd_prodid) as o ON o.kvd_prodid=a.id 
						LEFT JOIN (SELECT a.wonbd_prodid, SUM(a.wonbd_qty) wonbd_qty FROM workorder_nonbom_detail a INNER JOIN workorder_nonbom b ON b.id=a.wonbd_hid 
										   WHERE b.wonb_status=20 AND b.wonb_date BETWEEN '$fromdate' AND '$todate'	GROUP BY a.wonbd_prodid) as p ON p.wonbd_prodid=a.id                               
						WHERE a.prod_cat='$dh[id]' $search AND (b.stock_qty > 0 OR c.trfgd_qty > 0 OR d.rtngd_qty > 0 OR e.wobb_qty > 0 OR f.wobj_qty > 0 OR g.fgd_qty > 0 OR h.trfqc_qty > 0 OR i.rtnqc_qty > 0 OR j.scr_qty > 0 OR
							  k.kvp_qty > 0 OR l.kvm_qty > 0 OR m.wobbbj_qty > 0 OR n.kvp2_qty > 0 OR o.kvm2_qty > 0 OR p.wonbd_qty > 0) 
						ORDER BY a.prod_code ASC");
	while($dd=mysql_fetch_array($qd)){		
		#Summary	   
		if($dd['prod_cat']==1 || $dd['prod_cat']==6 || $dd['prod_cat']==7 ){			
			$stk    = $dd['stock_qty']; 
			$in     = $dd['trfgd_qty']+$dd['kvp_qty']+$dd['kvm2_qty']; 
			$out    = $dd['rtngd_qty']+$dd['wobb_qty']+$dd['kvm_qty']+$dd['wobbbj_qty']+$dd['kvp2_qty']+$dd['wonbd_qty'];	
			$stock  = ($stk+$in)-$out;
			$price  = get_unitprice_bb($start_date_m2year,$todate,$dd['id']); 
			$tprice = $stock*$price;
		}elseif($dd['prod_cat']==3){
			$stk    = $dd['stock_qty'];
			$in     = $dd['trfgd_qty']+$dd['wobj_qty']+$dd['rtnqc_qty']+$dd['fgd_qty']+$dd['kvp_qty']+$dd['kvm2_qty'];
			$out    = $dd['trfqc_qty']+$dd['wobb_qty']+$dd['kvm_qty']+$dd['wobbbj_qty']+$dd['kvp2_qty']+$dd['wonbd_qty'];	
			$stock  = ($stk+$in)-$out;
			$price  = get_unitprice_bj($start_date_m2year,$todate,$dd['id']); 
			$tprice = $stock*$price;
		}elseif($dd['prod_cat']==5){
			$stk    = $dd['stock_qty'];
			$in     = $dd['trfgd_qty']+$dd['wobj_qty']+$dd['rtnqc_qty']+$dd['kvp_qty']+$dd['kvm2_qty'];
			$out    = $dd['trfqc_qty']+$dd['wobb_qty']+$dd['kvm_qty']+$dd['wobbbj_qty']+$dd['kvp2_qty']+$dd['wonbd_qty'];	
			$stock  = ($stk+$in)-$out;
			$price  = get_unitprice_wip($start_date_m2year,$todate,$dd['id']); 
			$tprice = $stock*$price;
		}else{
			$stk    = $dd['stock_qty'];
			$in     = $dd['scr_qty']+$dd['wobj_qty']+$dd['rtnqc_qty']+$dd['kvp_qty']+$dd['kvm2_qty'];
			$out    = $dd['trfqc_qty']+$dd['kvm_qty']+$dd['kvp2_qty']+$dd['wonbd_qty'];	
			$stock  = ($stk+$in)-$out;
			$price  = 0;
			$tprice = $stock*$price;
		}
		if($stock > 0){	   
	?> 
	 <tr onmouseover="setTrColor(this, '#FF9900');">
		<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="right"><?=number_format($stk,2,'.',',');?></td>
		<td align="right"><?=number_format($in,2,'.',',');?></td>
        <td align="right"><?=number_format($out,2,'.',',');?></td>
        <td align="right"><?=number_format($stock,2,'.',',');?></td>
        <td align="right"><?=number_format($price,2,'.',',');?></td>
		<td align="right"><?=number_format($tprice,2,'.',',');?></td>
	  </tr>	  
  <?php $no++; } } } ?> 
</table>
</div>
</form>
</body>
</html>
