<!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-BrgJadi&Act=CARI';
			form.method = 'post';
			form.submit();
			return true;
		}

		function excel_click() {
			form.action = './transaksi/akuntansi/export_stock_brgjadi.php';
			form.method = 'post';
			form.submit();
			return true;
		}

		function pdf_click() {
			form.action = './transaksi/akuntansi/pdf_stock_brgjadi.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(3,4,5)";
	} 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 FG/WIP/SCRAP</b></h1>
				</td>
			</tr>
			<tr>
				<td colspan="10">Periode :
					<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(3,4,5) 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" style="cursor:pointer" onClick="excel_click();" />
					<!--<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>
					<th rowspan="2">No</th>
					<th rowspan="2">Kode Barang</th>
					<th rowspan="2">Nama Barang</th>
					<th rowspan="2">Satuan</th>
					<th colspan="5">Jumlah</th>
				</tr>
				<tr>
					<th>Awal</th>
					<th>Masuk</th>					
					<th>Keluar</th>
					<th>Penyesuaian</th>
					<th>Temp</th>
					<th>Stok</th>
				</tr>
				<?php
				$qh = mysql_query("SELECT * FROM product_category $s_idcat");
				while ($dh = mysql_fetch_array($qh)) {
				?>
					<tr bgcolor="#92fa05">
						<td colspan="10">KATEGORI :<?= $dh['prod_cat']; ?></td>
					</tr>
					<?php
					$no = 1;
					$gtprice = 0;
					if($dh['id']==3){
						$qd = mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, b.qty as beg_qty, e.qty as adj_qty, 
						ifnull(k.qty,0) as tpin_qty, ifnull(l.qty,0) as tpout_qty, 
						(ifnull(c.qty,0) + ifnull(h.qty,0) + ifnull(i.qty,0)) as in_qty, 
						(ifnull(d.qty,0) + ifnull(g.qty,0)+ ifnull(j.qty,0)) as out_qty,
						((ifnull(b.qty,0) + ifnull(c.qty,0) + ifnull(h.qty,0) + ifnull(i.qty,0)) - ifnull(l.qty,0) + ifnull(e.qty,0)) as temp_qty,
						(ifnull(k.qty,0) - (ifnull(d.qty,0) + ifnull(g.qty,0)+ ifnull(j.qty,0))) as end_qty, a.prod_cat
						
						FROM product a             
	
						LEFT JOIN (SELECT stock_prodid, SUM(stock_qty) as qty 
						FROM stock 
						WHERE stock_date='$fromdate' AND stock_status=10 AND stock_loc=3 
						GROUP BY stock_prodid) AS b ON b.stock_prodid = a.id
						
						LEFT JOIN (select a.trfd_prodid, sum(a.trfd_qty) as qty 
						from transfer_detail a 
						inner join transfer b on b.id = a.trfd_hid
						where b.trf_status=20 and b.trf_category IN (4) and trf_spbdate between '$fromdate' and '$todate' 
						group by trfd_prodid) AS c ON c.trfd_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 '$fromdate' and '$todate'
						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 '$fromdate' and '$todate'
						group by adjd_prodid) AS e ON e.adjd_prodid=a.id
						
						LEFT JOIN (select a.stod_prodid, max(b.stoh_date), 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 month(b.stoh_date)='$month_sto' and year(b.stoh_date)='$year_sto' 
						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_detail a 
						inner join transfer b on b.id = a.trfd_hid
						where b.trf_status=20 and b.trf_category IN (5,6) and trf_spbdate between '$fromdate' and '$todate'
						group by trfd_prodid) AS g ON g.trfd_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
						INNER JOIN product c ON c.id=a.incd_prodid AND prod_cat IN(3,4)
						where b.inch_status=20 and inch_docdate between '$fromdate' and '$todate' 
						group by incd_prodid) AS h ON h.incd_prodid=a.id  
						
						LEFT JOIN (SELECT kv_prodid, sum(kv_qty) as qty 
						from konversi_external where kv_status=20 and kv_date between '$fromdate' and '$todate' 
						group by kv_prodid) AS i ON i.kv_prodid=a.id
	
						LEFT JOIN (SELECT a.kvd_prodid, sum(a.kvd_qty) as qty 
						from konversi_external_detail a 
						INNER JOIN konversi_external 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 j ON j.kvd_prodid=a.id
	
						LEFT JOIN (SELECT kv_prodid, sum(kv_qty) as qty 
						from konversi_barangjadi 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) as qty 
						from konversi_barangjadi_detail a 
						INNER JOIN konversi_barangjadi 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
	
						
						WHERE a.prod_cat='$dh[id]' $search ORDER BY a.prod_code ASC");
					}else{
						$qd = mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, b.qty as beg_qty, (ifnull(c.qty,0) + ifnull(h.qty,0) + ifnull(i.qty,0)) as in_qty, (ifnull(d.qty,0)+ifnull(g.qty,0)+ifnull(j.qty,0)) as out_qty,
							   e.qty as adj_qty, (ifnull(b.qty,0) + (ifnull(c.qty,0) + ifnull(h.qty,0) + ifnull(i.qty,0)) - (ifnull(d.qty,0)+ifnull(g.qty,0)+ifnull(j.qty,0)) + ifnull(e.qty,0)) as end_qty, 0 as temp_qty, a.prod_cat	
						FROM product a             
						LEFT JOIN (SELECT stock_prodid, SUM(stock_qty) as qty FROM stock WHERE stock_date='$fromdate' AND stock_status=10 AND stock_loc=3 GROUP BY stock_prodid) AS b
							   ON b.stock_prodid = a.id
						LEFT JOIN (select a.trfd_prodid, sum(a.trfd_qty) as qty from transfer_detail a 
							   inner join transfer b on b.id = a.trfd_hid
							   where b.trf_status=20 and b.trf_category IN (4) and trf_spbdate between '$fromdate' and '$todate' 
							   group by trfd_prodid) AS c ON c.trfd_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 '$fromdate' and '$todate'
							   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 '$fromdate' and '$todate'
							   group by adjd_prodid) AS e ON e.adjd_prodid=a.id
						LEFT JOIN (select a.stod_prodid, max(b.stoh_date), 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 month(b.stoh_date)='$month_sto' and year(b.stoh_date)='$year_sto' 
							   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_detail a 
							   inner join transfer b on b.id = a.trfd_hid
							   where b.trf_status=20 and b.trf_category IN (5,6) and trf_spbdate between '$fromdate' and '$todate'
							   group by trfd_prodid) AS g ON g.trfd_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
							   INNER JOIN product c ON c.id=a.incd_prodid AND prod_cat IN(3,4)
							   where b.inch_status=20 and inch_docdate between '$fromdate' and '$todate' 
							   group by incd_prodid) AS h ON h.incd_prodid=a.id  
						LEFT JOIN (SELECT kv_prodid, sum(kv_qty) as qty from konversi_external where kv_status=20 and kv_date between '$fromdate' and '$todate' 
							  group by kv_prodid) AS i ON i.kv_prodid=a.id
						LEFT JOIN (SELECT a.kvd_prodid, sum(a.kvd_qty) as qty from konversi_external_detail a INNER JOIN konversi_external 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 j ON j.kvd_prodid=a.id
						WHERE a.prod_cat='$dh[id]' $search ORDER BY a.prod_code ASC");
					}	
					while ($dd = mysql_fetch_array($qd)) {
						#$price = get_unitprice_bj($start_date_m2year,$todate,$dd['id']); #get_pricebj($dd['id'],$fromdate,$todate,$inv);
						#$tprice=$dd['end_qty']*$price;
						#$gtprice=$gtprice+$tprice;
						if (($dd['prod_cat']==3 && $dd['temp_qty'] > 0) || ($dd['prod_cat']!=3 && $dd['end_qty'] > 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($dd['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($dd['temp_qty'], 2, '.', ','); ?></td>
								<td align="right"><?= number_format($dd['end_qty'], 2, '.', ','); ?></td>
							</tr>
				<?php $no++;
						}
					}
				} ?>
			</table>
		</div>
	</form>
</body>

</html>