<?
include "../function/excel_reader.php";
include "../function/inc_function.php";
include "../include/config.php";
?>
<html>
<head>
<title>UPLOAD DATA DAN MANAGEMENT STOCK</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!--Date Picker-->
<link type="text/css" rel="stylesheet" href="../include/jq/datepicker/themes/ui-lightness/ui.all.css" />
<script src="../include/jq/datepicker/jquery-1.8.0.min.js"></script>
<script src="../include/jq/datepicker/ui/ui.core.js"></script>
<script src="../include/jq/datepicker/ui/ui.datepicker.js"></script>
<script src="../include/jq/datepicker/ui/i18n/ui.datepicker-id.js"></script>

<link rel='stylesheet' type='text/css' href="../include/css/display_styles.css" />	
<script src="datepicker/codebase/dhtmlxcalendar.js"></script>
<script language="javascript">
	function choose(id)
	{
		//alert(id);
		form.action ='index.php?idpilih='+id;
		form.method = 'post';
		form.submit();			
		return true;
	}
	$(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,
		});
	});
	function button_click(id)
	{
		//alert(flag);
		form.action ='index.php?tombol=Proses&idpilih='+id; 
		form.method = 'post';
		form.submit();			
		return true;	
	}
	function button_upload(id)
	{
		//alert(proses);
		form.action ='index.php?tombol=Upload&idpilih='+id; 
		form.method = 'post';
		form.submit();			
		return true;	
	}
</script>
</head>

<body>
<? 
$idpilih 	= $_REQUEST['idpilih'];
$curr_month = date('m');
$tombol		= $_REQUEST['tombol'];
$kategori	= $_REQUEST['kategori'];
if($tombol == "Proses"){
	$start_date = $_REQUEST['start_date'];
	$end_date 	= $_REQUEST['end_date'];
}else{
	if(($curr_month=='01') OR ($curr_month=='02') OR ($curr_month=='03') OR ($curr_month=='04')) {
		$start_date = date('Y')."-01-01";
		$end_date   = date('Y')."-04-30";
	}elseif(($curr_month=='05') OR ($curr_month=='06') OR ($curr_month=='07') OR ($curr_month=='08')) {
		$start_date = date('Y')."-05-01";
		$end_date   = date('Y')."-08-31";
	}else{
		$start_date = date('Y')."-09-01";
		$end_date   = date('Y')."-12-31";
	}
}

$month 		 = substr($start_date,5,2);
$year		 = substr($start_date,0,4);
$month_e	 = substr($end_date,5,2);
$year_e		 = substr($end_date,0,4);
$cek         = $month-1;
if($cek==0){
	$bln = 12;
	$thn = $year-1;
}else{
	$bln = $cek;
	$thn = $year;
}

?>
<form action="index.php" method="post" name="form" enctype="multipart/form-data">
	<table width="50%">
		<tr>
			<td colspan="2">UPLOAD DATA & MANAGEMENT STOCK</td>
		</tr>
		<tr>
			<td>Pilih Proses</td>
			<td>: 
				<select name="idpilih" onChange="choose(this.value);">
				  <option value="BLANK">PILIH</option> 
					<?php
					  $dataSql = mysql_query("SELECT id, m_desc FROM maintenance WHERE m_status=1 ORDER BY id ASC");
					  while ($dataRow = mysql_fetch_array($dataSql)) {
						if ($dataRow['id']== $idpilih) {
							$cek = " selected";
						} else { $cek=""; }
						echo "<option value='$dataRow[id]' $cek>$dataRow[m_desc]</option>";
					  }
					  $sqlData =""; 
					?>
				 </select>
			</td>
		</tr>
		<? if(($idpilih==1) || ($idpilih==2) || ($idpilih==3) || ($idpilih==4) || ($idpilih==5) || ($idpilih==6) || ($idpilih==7) || ($idpilih==8) || ($idpilih==10)){?>
		<tr>	
			<td>Pilih File</td>
			<td>:
				<input name="userfile" type="file"> 
    			<input type="button" value="PROSES" name="proses" onClick="button_click(<?=$idpilih;?>);">
				<input type="button" value="UPLOAD" name="upload" onClick="button_upload(<?=$idpilih;?>);">
			</td>
		</tr>
		<? }elseif($idpilih==9){?>		
		<tr>	
			<td>Pilih File</td>
			<td>:
				<input type="text" id="fromdate" name="start_date" size="9" value="<?=$start_date;?>" style="border-collapse:inherit"  readonly="true">&nbsp;s/d&nbsp;
				<input type="text" id="todate" name="end_date" size="9" value="<?=$end_date;?>" style="border-collapse:inheri" readonly="true"> 
				<select name="kategori">
				  <option value="BLANK">PILIH</option> 
					<?php
					  $dataSql = mysql_query("SELECT id, prod_cat FROM product_category ORDER BY id ASC");
					  while ($dataRow = mysql_fetch_array($dataSql)) {
						if ($dataRow['id']== $kategori) {
							$cek = " selected";
						} else { $cek=""; }
						echo "<option value='$dataRow[id]' $cek>$dataRow[prod_cat]</option>";
					  }
					  $sqlData =""; 
					?>
				 </select>				
				<input type="button" value="PROSES" name="proses" onClick="button_click(<?=$idpilih;?>);">
			</td>
		</tr>
		<? } ?>
	</table>
</form>
<?
#Pilih =1 Pemasukan
if(($tombol=="Proses") AND ($idpilih==1)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		 = $data->val($i, 1);
	  $inch_nomor   = $data->val($i, 2);
	  $inch_bpbdate   = $data->val($i, 3);
	  $kd_pemasok = $data->val($i, 4);
	  $inch_invno = $data->val($i, 5);
	  $inch_invdate = $data->val($i, 6);
	  $inch_dono  = $data->val($i, 7);
	  $jenis_dokpab  = $data->val($i, 8);
	  $inch_purpid  = $data->val($i, 9);
	  $inch_docno  = $data->val($i, 10);
	  $inch_docdate  = $data->val($i, 11);
	  $kode  = str_replace(' ','',($data->val($i, 12)));
	  $incd_qty  = $data->val($i, 13);
	  $incd_currency  = $data->val($i, 14);
	  $incd_price  = $data->val($i, 15);
	  $incd_weight  = $data->val($i, 16);
	  #SupID
	  $inch_supid  = get_supid($kd_pemasok);
	  $inch_docid  = get_docidinc($jenis_dokpab);
	  $incd_prodid = get_prodid($kode);
	  
	  //echo "Kode : $incd_prodid == $kode <br> ";
	  
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO incoming_log SET inch_bpbdate='$inch_bpbdate', inch_supid='$inch_supid', 
					  inch_invno='$inch_invno', inch_invdate='$inch_invdate', inch_dono='$inch_dono',
					  inch_purpid='$inch_purpid', inch_docid='$inch_docid',inch_docdate='$inch_docdate', 
					  inch_docno='$inch_docno', incd_prodid='$incd_prodid', incd_qty='$incd_qty', 
					  incd_price='$incd_price', incd_currency='$incd_currency', incd_weight='$incd_weight', 
					  inch_inputdate='".get_datetime()."', inch_status=10, inch_nomor='$inch_nomor'");	  
	  }		  
	}
	//exit;
	?>	
	<table width="100%">
		<tr>
			<th>No</th>
			<th>Nomor</th>
			<th>Tanggal BPB</th>
			<th>Supplier ID</th>
			<th>No Invoice</th>
			<th>Tanggal Invoice</th>
			<th>No DO</th>
			<th>Jenis Dokumen</th>
			<th>No Tujuan</th>
			<th>No Dokumen</th>
			<th>Tanggal Dokumen</th>
			<th>Kode</th>
			<th>Jumlah</th>
			<th>Mata Uang</th>
			<th>Nilai</th>
			<th>Berat</th>
		</tr>	
		<?
		$q = mysql_query("SELECT * FROM incoming_log WHERE inch_status=10");
		while($data=mysql_fetch_object($q)){
		$no++;
		if ($data->incd_prodid == 0){
			$tr = "<tr bgcolor=#FF0000>";
		}else{
			$tr = "<tr bgcolor=white>";
		}
		echo $tr;
		?>
				<td><?=$no;?></td>
				<td><?=$data->inch_nomor;?></td>
				<td><?=$data->inch_bpbdate;?></td>
				<td><?=$data->inch_supid;?></td>
				<td><?=$data->inch_invno;?></td>
				<td><?=$data->inch_invdate;?></td>
				<td><?=$data->inch_dono;?></td>
				<td><?=$data->inch_docid;?></td>
				<td><?=$data->inch_purpid;?></td>
				<td><?=$data->inch_docno;?></td>
				<td><?=$data->inch_docdate;?></td>
				<td><?=$data->incd_prodid;?></td>
				<td><?=$data->incd_qty;?></td>
				<td><?=$data->incd_currency;?></td>
				<td><?=$data->incd_price;?></td>
				<td><?=$data->incd_weight;?></td>
			</tr>
		<? } ?>
	</table>
	<?
}
if(($tombol=="Upload") AND ($idpilih==1)){
	$q = mysql_query("SELECT inch_nomor, inch_bpbdate, inch_supid, inch_invno, inch_invdate, inch_dono,
							   inch_purpid, inch_docid, inch_docno, inch_docdate 
						FROM incoming_log WHERE inch_status=10
						GROUP BY inch_nomor 
						ORDER BY inch_nomor ASC");
	while($data=mysql_fetch_object($q)){
		$year 		= substr($data->inch_bpbdate,0,4);
		$month 		= substr($data->inch_bpbdate,5,2);
		$inch_bpbno = get_bpbnoupload($year,$month);
		
		mysql_query("INSERT INTO incoming SET inch_bpbno='$inch_bpbno', inch_bpbdate='$data->inch_bpbdate', inch_supid='$data->inch_supid', 
					  inch_invno='$data->inch_invno', inch_invdate='$data->inch_invdate', inch_dono='$data->inch_dono',
					  inch_purpid='$data->inch_purpid', inch_docid='$data->inch_docid',inch_docdate='$data->inch_docdate', 
					  inch_docno='$data->inch_docno', inch_inputdate='".get_datetime()."', inch_status=10, inch_nomor='$data->inch_nomor'");
		$flag=1;			  
	}
	if($flag==1){
		$qd = mysql_query("SELECT a.id, b.incd_prodid, b.incd_qty, b.incd_currency, b.incd_price, b.incd_weight
							FROM incoming a
							INNER JOIN incoming_log b ON  b.inch_nomor=a.inch_nomor
							WHERE b.inch_status=10      
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO incoming_detail SET incd_hid='$dt->id',incd_prodid='$dt->incd_prodid', incd_qty='$dt->incd_qty', 
											  incd_price='$dt->incd_price', incd_currency='$dt->incd_currency', 
											  incd_weight='$dt->incd_weight', incd_wunit='KG'");
		}		
		$flag=2;			
	}			
	if($flag==2){
		mysql_query("UPDATE incoming_log SET inch_status=20 WHERE inch_status=10");
		mysql_query("UPDATE incoming SET inch_status=20 WHERE inch_status=10");
		$results = "OK";
	}			
}
if(($tombol=="Proses") AND ($idpilih==2)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		 	 = $data->val($i, 1);
	  $outh_nomor    = $data->val($i, 2);
	  $outh_sjdate   = $data->val($i, 3);
	  $kd_penerima 	 = $data->val($i, 4);
	  $outh_invno 	 = $data->val($i, 5);
	  $outh_invdate  = $data->val($i, 6);
	  $outh_dono  	 = $data->val($i, 7);
	  $jenis_dokpab  = $data->val($i, 8);
	  $outh_purpid   = $data->val($i, 9);
	  $outh_docno    = $data->val($i, 10);
	  $outh_docdate  = $data->val($i, 11);
	  $kode  		 = $data->val($i, 12);
	  $outd_qty  	 = $data->val($i, 13);
	  $outd_currency = $data->val($i, 14);
	  $outd_price  	 = $data->val($i, 15);
	  $outd_weight   = $data->val($i, 16);
	  #SupID
	  $outh_custid = get_custid($kd_penerima);
	  $outh_docid  = get_docidout($jenis_dokpab);
	  $outd_prodid = get_prodid($kode);
	  
	  //echo "$outd_prodid == $kode<br>";
	  
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO outgoing_log SET outh_sjdate='$outh_sjdate', outh_custid='$outh_custid', 
					  outh_invno='$outh_invno', outh_invdate='$outh_invdate', outh_dono='$outh_dono',
					  outh_purpid='$outh_purpid', outh_docid='$outh_docid',outh_docdate='$outh_docdate', 
					  outh_docno='$outh_docno', outd_prodid='$outd_prodid', outd_qty='$outd_qty', 
					  outd_price='$outd_price', outd_currency='$outd_currency', outd_weight='$outd_weight', 
					  outh_inputdate='".get_datetime()."', outh_status=10, outh_nomor='$outh_nomor'");	  
	  }			  
	}
	//exit;
	?>	
	<table width="100%">
		<tr>
			<th>No</th>
			<th>ID</th>
			<th>Tanggal SJ</th>
			<th>Customer ID</th>
			<th>No Invoice</th>
			<th>Tanggal Invoice</th>
			<th>No DO</th>
			<th>Jenis Dokumen</th>
			<th>No Tujuan</th>
			<th>No Dokumen</th>
			<th>Tanggal Dokumen</th>
			<th>Kode</th>
			<th>Jumlah</th>
			<th>Mata Uang</th>
			<th>Nilai</th>
			<th>Berat</th>
		</tr>	
		<?
		$q = mysql_query("SELECT * FROM outgoing_log WHERE outh_status=10");
		while($data=mysql_fetch_object($q)){
		$no++;
		if ($data->outd_prodid == 0){
			$tr = "<tr bgcolor=#FF0000>";
		}else{
			$tr = "<tr bgcolor=white>";
		}
		echo $tr;
		?>
				<td><?=$no;?></td>
				<td><?=$data->outh_nomor;?></td>
				<td><?=$data->outh_sjdate;?></td>
				<td><?=$data->outh_custid;?></td>
				<td><?=$data->outh_invno;?></td>
				<td><?=$data->outh_invdate;?></td>
				<td><?=$data->outh_dono;?></td>
				<td><?=$data->outh_docid;?></td>
				<td><?=$data->outh_purpid;?></td>
				<td><?=$data->outh_docno;?></td>
				<td><?=$data->outh_docdate;?></td>
				<td><?=$data->outd_prodid;?></td>
				<td><?=$data->outd_qty;?></td>
				<td><?=$data->outd_currency;?></td>
				<td><?=$data->outd_price;?></td>
				<td><?=$data->outd_weight;?></td>
			</tr>
		<? } ?>
	</table>
	<? 
}
if(($tombol=="Upload") AND ($idpilih==2)){
	$q = mysql_query("SELECT outh_nomor, outh_sjdate, outh_custid, outh_invno, outh_invdate, outh_dono,
							   outh_purpid, outh_docid, outh_docno, outh_docdate 
						FROM outgoing_log WHERE outh_status=10
						GROUP BY outh_nomor
						ORDER BY outh_sjdate ASC");
	while($data=mysql_fetch_object($q)){
		$year 		= substr($data->outh_sjdate,0,4);
		$month 		= substr($data->outh_sjdate,5,2);
		$outh_sjno = get_uploadsjno($year,$month);
		
		mysql_query("INSERT INTO outgoing SET outh_sjno='$outh_sjno', outh_sjdate='$data->outh_sjdate', outh_custid='$data->outh_custid', 
					  outh_invno='$data->outh_invno', outh_invdate='$data->outh_invdate', outh_dono='$data->outh_dono',
					  outh_purpid='$data->outh_purpid', outh_docid='$data->outh_docid',outh_docdate='$data->outh_docdate', 
					  outh_docno='$data->outh_docno', outh_inputdate='".get_datetime()."', outh_status=10, outh_nomor='$data->outh_nomor'");
		$flag=1;			  
	}//exit;
	if($flag==1){
		$qd = mysql_query("SELECT a.id, b.outd_prodid, b.outd_qty, b.outd_currency, b.outd_price, b.outd_weight
							FROM outgoing a
							INNER JOIN outgoing_log b ON  b.outh_nomor=a.outh_nomor
							WHERE b.outh_status=10      
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO outgoing_detail SET outd_hid='$dt->id',outd_prodid='$dt->outd_prodid', outd_qty='$dt->outd_qty', 
											  outd_price='$dt->outd_price', outd_currency='$dt->outd_currency', 
											  outd_weight='$dt->outd_weight', outd_wunit='KG'");
		}		
		$flag=2;			
	}			
	if($flag==2){
		mysql_query("UPDATE outgoing_log SET outh_status=20 WHERE outh_status=10");
		mysql_query("UPDATE outgoing SET outh_status=20 WHERE outh_status=10");
		$results = "OK";
	}			
}
if(($tombol=="Proses") AND ($idpilih==3)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		 	 = $data->val($i, 1);
	  $trf_nomor     = $data->val($i, 2);
	  $trf_spbdate   = $data->val($i, 3);
	  $trf_reqno 	 = $data->val($i, 4);
	  $trf_reqdate 	 = $data->val($i, 5);
	  $trf_deptid    = $data->val($i, 6);
	  $trf_pic  	 = $data->val($i, 7);
	  $trf_desc      = $data->val($i, 8);
	  $kode  		 = $data->val($i, 9);
	  $trfd_qty  	 = $data->val($i, 10);
	  $trfd_desc     = $data->val($i, 11);

	  $trfd_prodid   = get_prodid($kode);
	  
	  //echo "$trfd_prodid == $kode<br>";
	  
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO transfer_log SET trf_spbdate='$trf_spbdate', trf_reqno='$trf_reqno', 
					  trf_reqdate='$trf_reqdate', trf_deptid='$trf_deptid', trf_pic='$trf_pic',
					  trf_desc='$trf_desc', trfd_prodid='$trfd_prodid',trfd_qty='$trfd_qty', 
					  trfd_desc='$trfd_desc', trf_inputdate='".get_datetime()."', trf_status=10, trf_category=1, trf_nomor='$trf_nomor'");	  
	  }	  
	}
	//exit;
	?>	
	<table width="100%">
		<tr>
			<th>No</th>
			<th>ID</th>
			<th>Tanggal SPB</th>
			<th>No Request</th>
			<th>Tanggal Request</th>
			<th>Dept ID</th>
			<th>PIC</th>
			<th>Ket 1</th>
			<th>Kode</th>
			<th>Jumlah</th>
			<th>Ket 2</th>
		</tr>	
		<?
		$q = mysql_query("SELECT * FROM transfer_log WHERE trf_status=10");
		while($data=mysql_fetch_object($q)){
		$no++;
		if ($data->trfd_prodid == 0){
			$tr = "<tr bgcolor=#FF0000>";
		}else{
			$tr = "<tr bgcolor=white>";
		}
		echo $tr;
		?>
				<td><?=$no;?></td>
				<td><?=$data->trf_nomor;?></td>
				<td><?=$data->trf_spbdate;?></td>
				<td><?=$data->trf_reqno;?></td>
				<td><?=$data->trf_reqdate;?></td>
				<td><?=$data->trf_deptid;?></td>
				<td><?=$data->trf_pic;?></td>
				<td><?=$data->trf_desc;?></td>
				<td><?=$data->trfd_prodid;?></td>
				<td><?=$data->trfd_qty;?></td>
				<td><?=$data->trfd_desc;?></td>
			</tr>
		<? } ?>
	</table>
	<? 
}
if(($tombol=="Upload") AND ($idpilih==3)){
	$q = mysql_query("SELECT trf_nomor, trf_spbdate, trf_reqno, trf_reqdate, trf_deptid, trf_pic, trf_desc
					  FROM transfer_log WHERE trf_status=10
					  GROUP BY trf_nomor
					  ORDER BY trf_nomor ASC");
	while($data=mysql_fetch_object($q)){
		$year 		= substr($data->trf_spbdate,0,4);
		$month 		= substr($data->trf_spbdate,5,2);
		$trf_spbno = get_uploadspbno(1,$year,$month);
		
		mysql_query("INSERT INTO transfer SET trf_spbno='$trf_spbno', trf_spbdate='$data->trf_spbdate', trf_reqno='$data->trf_reqno', 
					  trf_reqdate='$data->trf_reqdate', trf_deptid='$data->trf_deptid', trf_pic='$data->trf_pic',
					  trf_desc='$data->trf_desc', trf_inputdate='".get_datetime()."', trf_status=10, trf_category=1, trf_inputby='Admin', trf_nomor='$data->trf_nomor'");
		$flag=1;			  
	}
	if($flag==1){
		$qd = mysql_query("SELECT a.id, b.trfd_prodid, b.trfd_qty, b.trfd_desc
							FROM transfer a
							INNER JOIN transfer_log b ON b.trf_nomor=a.trf_nomor
							WHERE b.trf_status=10
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO transfer_detail SET trfd_hid='$dt->id',trfd_prodid='$dt->trfd_prodid', trfd_qty='$dt->trfd_qty', 
											  trfd_desc='$dt->trfd_desc'");
		}		
		$flag=2;			
	}		
	if($flag==2){
		$q = mysql_query("SELECT * FROM transfer WHERE trf_status=10 ORDER BY id ASC");
		while($data=mysql_fetch_object($q)){
			$year 		= substr($data->trf_spbdate,0,4);
			$month 		= substr($data->trf_spbdate,5,2);
			$rci_lpbno = get_uploadlpbno(1,$year,$month);
			
			mysql_query("INSERT INTO receive SET rci_lpbno='$rci_lpbno', rci_lpbdate='$data->trf_spbdate', rci_trfid='$data->id', 
						  rci_deptid='$data->trf_deptid', rci_pic='$data->trf_pic',
						  rci_desc='$data->trf_desc', rci_inputdate='".get_datetime()."', rci_status=10, rci_category=1, rci_inputby='Admin'");
			$flag=3;
		}	
	}//exit;	
	if($flag==3){
		$qd = mysql_query("SELECT a.id, c.trfd_prodid, c.trfd_qty, c.trfd_desc
							FROM receive a
							INNER JOIN transfer b ON b.id=a.rci_trfid
							INNER JOIN transfer_detail c ON c.trfd_hid=b.id
							WHERE a.rci_status=10
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO receive_detail SET rcid_hid='$dt->id',rcid_prodid='$dt->trfd_prodid', rcid_qty='$dt->trfd_qty', 
											  rcid_desc='$dt->trfd_desc'");
		}		
		$flag=4;
	}
	if($flag==4){
		mysql_query("UPDATE transfer_log SET trf_status=20 WHERE trf_status=10");
		mysql_query("UPDATE transfer SET trf_status=30 WHERE trf_status=10");
		mysql_query("UPDATE receive SET rci_status=30 WHERE rci_status=10");
		$results = "OK";
	}			
}
if(($tombol=="Proses") AND ($idpilih==4)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		 	 = $data->val($i, 1);
	  $trf_nomor     = $data->val($i, 2);
	  $trf_spbdate   = $data->val($i, 3);
	  $trf_reqno 	 = $data->val($i, 4);
	  $trf_reqdate 	 = $data->val($i, 5);
	  $trf_deptid    = $data->val($i, 6);
	  $trf_pic  	 = $data->val($i, 7);
	  $trf_desc      = $data->val($i, 8);
	  $kode  		 = $data->val($i, 9);
	  $trfd_qty  	 = $data->val($i, 10);
	  $trfd_desc     = $data->val($i, 11);
	  $trf_category  = $data->val($i, 12);

	  $trfd_prodid   = get_prodid($kode);
	  
	  //echo "$trfd_prodid == $kode<br>";
	  
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO transfer_log SET trf_spbdate='$trf_spbdate', trf_reqno='$trf_reqno', 
					 trf_reqdate='$trf_reqdate', trf_deptid='$trf_deptid', trf_pic='$trf_pic',
					  trf_desc='$trf_desc', trfd_prodid='$trfd_prodid',trfd_qty='$trfd_qty', 
					  trfd_desc='$trfd_desc', trf_inputdate='".get_datetime()."', trf_status=10, trf_category='$trf_category', trf_nomor='$trf_nomor'");	  
	  }			  
	}
	//exit;
	?>	
	<table width="100%">
		<tr>
			<th>No</th>
			<th>ID</th>
			<th>Tanggal SPB</th>
			<th>No Request</th>
			<th>Tanggal Request</th>
			<th>Dept ID</th>
			<th>PIC</th>
			<th>Ket 1</th>
			<th>Kode</th>
			<th>Jumlah</th>
			<th>Ket 2</th>
		</tr>	
		<?
		$q = mysql_query("SELECT * FROM transfer_log WHERE trf_status=10");
		while($data=mysql_fetch_object($q)){
		$no++;
		if ($data->trfd_prodid == 0){
			$tr = "<tr bgcolor=#FF0000>";
		}else{
			$tr = "<tr bgcolor=white>";
		}
		echo $tr;
		?>
				<td><?=$no;?></td>
				<td><?=$data->trf_nomor;?></td>
				<td><?=$data->trf_spbdate;?></td>
				<td><?=$data->trf_reqno;?></td>
				<td><?=$data->trf_reqdate;?></td>
				<td><?=$data->trf_deptid;?></td>
				<td><?=$data->trf_pic;?></td>
				<td><?=$data->trf_desc;?></td>
				<td><?=$data->trfd_prodid;?></td>
				<td><?=$data->trfd_qty;?></td>
				<td><?=$data->trfd_desc;?></td>
			</tr>
		<? } ?>
	</table>
	<? 
}
if(($tombol=="Upload") AND ($idpilih==4)){
	$q = mysql_query("SELECT trf_nomor, trf_spbdate, trf_reqno, trf_reqdate, trf_deptid, trf_pic, trf_desc, trf_category
					  FROM transfer_log WHERE trf_status=10
					  GROUP BY trf_nomor
					  ORDER BY trf_spbdate ASC");
	while($data=mysql_fetch_object($q)){
		$year 		= substr($data->trf_spbdate,0,4);
		$month 		= substr($data->trf_spbdate,5,2);
		$trf_spbno = get_uploadspbno($data->trf_category,$year,$month);
		
		mysql_query("INSERT INTO transfer SET trf_spbno='$trf_spbno', trf_spbdate='$data->trf_spbdate', trf_reqno='$data->trf_reqno', 
					  trf_reqdate='$data->trf_reqdate', trf_deptid='$data->trf_deptid', trf_pic='$data->trf_pic',
					  trf_desc='$data->trf_desc', trf_inputdate='".get_datetime()."', trf_status=10, trf_category='$data->trf_category', trf_inputby='Admin', trf_nomor='$data->trf_nomor'");
		$flag=1;			  
	}
	if($flag==1){
		$qd = mysql_query("SELECT a.id, b.trfd_prodid, b.trfd_qty, b.trfd_desc
							FROM transfer a
							INNER JOIN transfer_log b ON b.trf_nomor=a.trf_nomor
							WHERE b.trf_status=10
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO transfer_detail SET trfd_hid='$dt->id',trfd_prodid='$dt->trfd_prodid', trfd_qty='$dt->trfd_qty', 
											  trfd_desc='$dt->trfd_desc'");
		}		
		$flag=2;			
	}		
	if($flag==2){
		$q = mysql_query("SELECT * FROM transfer WHERE trf_status=10 ORDER BY id ASC");
		while($data=mysql_fetch_object($q)){
			$year 		= substr($data->trf_spbdate,0,4);
			$month 		= substr($data->trf_spbdate,5,2);
			$rci_lpbno = get_uploadlpbno($data->trf_category,$year,$month);
			
			mysql_query("INSERT INTO receive SET rci_lpbno='$rci_lpbno', rci_lpbdate='$data->trf_spbdate', rci_trfid='$data->id', 
						  rci_deptid='$data->trf_deptid', rci_pic='$data->trf_pic',
						  rci_desc='$data->trf_desc', rci_inputdate='".get_datetime()."', rci_status=10, rci_category='$data->trf_category', rci_inputby='Admin'");
			$flag=3;
		}	
	}//exit;	
	if($flag==3){
		$qd = mysql_query("SELECT a.id, c.trfd_prodid, c.trfd_qty, c.trfd_desc
							FROM receive a
							INNER JOIN transfer b ON b.id=a.rci_trfid
							INNER JOIN transfer_detail c ON c.trfd_hid=b.id
							WHERE a.rci_status=10
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO receive_detail SET rcid_hid='$dt->id',rcid_prodid='$dt->trfd_prodid', rcid_qty='$dt->trfd_qty', 
											  rcid_desc='$dt->trfd_desc'");
		}		
		$flag=4;
	}
	if($flag==4){
		mysql_query("UPDATE transfer_log SET trf_status=20 WHERE trf_status=10");
		mysql_query("UPDATE transfer SET trf_status=30 WHERE trf_status=10");
		mysql_query("UPDATE receive SET rci_status=30 WHERE rci_status=10");
		$results = "OK";
	}			
}
if(($tombol=="Proses") AND ($idpilih==5)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		 	 = $data->val($i, 1);
	  $fgh_nomor     = $data->val($i, 2);
	  $fgh_lpdate    = $data->val($i, 3);
	  $fgh_deptid 	 = $data->val($i, 4);
	  $fgh_pic   	 = $data->val($i, 5);
	  $fgh_desc      = $data->val($i, 6);
	  $kode     	 = $data->val($i, 7);
	  $fgd_qty       = $data->val($i, 8);

	  $fgd_prodid    = get_prodid($kode);
	  
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO finishgoods_log SET fgh_nomor='$fgh_nomor', fgh_lpdate='$fgh_lpdate', fgh_deptid='$fgh_deptid', 
					  fgh_pic='$fgh_pic', fgh_desc='$fgh_desc', fgd_prodid='$fgd_prodid',
					  fgd_qty='$fgd_qty', fgh_inputdate='".get_datetime()."', fgh_status=10, fgh_inputby='Admin'");	  
	  }			  
	}
	?>	
	<table width="100%">
		<tr>
			<th>No</th>
			<th>ID</th>
			<th>Tanggal LPB</th>
			<th>Dept ID</th>
			<th>PIC</th>
			<th>Ket 1</th>
			<th>Kode</th>
			<th>Jumlah</th>
		</tr>	
		<?
		$q = mysql_query("SELECT * FROM finishgoods_log WHERE fgh_status=10");
		while($data=mysql_fetch_object($q)){
		$no++;
		?>
			<tr>
				<td><?=$no;?></td>
				<td><?=$data->fgh_nomor;?></td>
				<td><?=$data->fgh_lpdate;?></td>
				<td><?=$data->fgh_deptid;?></td>
				<td><?=$data->fgh_pic;?></td>
				<td><?=$data->fgh_desc;?></td>
				<td><?=$data->fgd_prodid;?></td>
				<td><?=$data->fgd_qty;?></td>
			</tr>
		<? } ?>
	</table>
	<? 
}
if(($tombol=="Upload") AND ($idpilih==5)){
	$q = mysql_query("SELECT fgh_nomor,fgh_lpdate, fgh_deptid, fgh_pic, fgh_desc
					  FROM finishgoods_log
					  WHERE fgh_status=10
					  GROUP BY fgh_nomor,fgh_lpdate, fgh_deptid, fgh_pic, fgh_desc
					  ORDER BY fgh_nomor ASC");
	while($data=mysql_fetch_object($q)){
		$year 		= substr($data->fgh_lpdate,0,4);
		$month 		= substr($data->fgh_lpdate,5,2);
		$fgh_lpno = get_uploadlpjno($year,$month);
		
		mysql_query("INSERT INTO finishgoods SET fgh_nomor='$data->fgh_nomor', fgh_lpno='$fgh_lpno', fgh_lpdate='$data->fgh_lpdate', 
					  fgh_deptid='$data->fgh_deptid', fgh_pic='$data->fgh_pic', fgh_desc='$data->fgh_desc',
					  fgh_inputdate='".get_datetime()."', fgh_status=10, fgh_inputby='Admin'");
		$flag=1;		
		//echo "$fgh_lpno == $data->fgh_lpdate<br>";	  
	}//exit;
	if($flag==1){
		$qd = mysql_query("SELECT a.id, b.fgd_prodid, b.fgd_qty
							FROM finishgoods a
							INNER JOIN finishgoods_log b ON b.fgh_nomor=a.fgh_nomor
							WHERE b.fgh_status=10
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO finishgoods_detail SET fgd_hid='$dt->id',fgd_prodid='$dt->fgd_prodid', fgd_qty='$dt->fgd_qty'");
		}		
		$flag=2;			
	}			
	if($flag==2){
		mysql_query("UPDATE finishgoods_log SET fgh_status=20 WHERE fgh_status=10");
		mysql_query("UPDATE finishgoods SET fgh_status=30 WHERE fgh_status=10");
		$results = "OK";
	}			
}
if(($tombol=="Proses") AND ($idpilih==6)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		 	 = $data->val($i, 1);
	  $scrh_nomor    = $data->val($i, 2);
	  $scrh_lpdate   = $data->val($i, 3);
	  $scrh_pic   	 = $data->val($i, 4);
	  $scrh_desc     = $data->val($i, 5);
	  $kode     	 = $data->val($i, 6);
	  $scrd_qty      = $data->val($i, 7);

	  $scrd_prodid    = get_prodid($kode);
	  
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO scrap_log SET scrh_nomor='$scrh_nomor', scrh_lpdate='$scrh_lpdate', 
					  scrh_pic='$scrh_pic', scrh_desc='$scrh_desc', scrd_prodid='$scrd_prodid',
					  scrd_qty='$scrd_qty', scrh_inputdate='".get_datetime()."', scrh_status=10, scrh_inputby='Admin'");	  
	  }			  
	}
	?>	
	<table width="100%">
		<tr>
			<th>No</th>
			<th>ID</th>
			<th>Tanggal LPB</th>
			<th>PIC</th>
			<th>Ket 1</th>
			<th>Kode</th>
			<th>Jumlah</th>
		</tr>	
		<?
		$q = mysql_query("SELECT * FROM scrap_log WHERE scrh_status=10");
		while($data=mysql_fetch_object($q)){
		$no++;
		?>
			<tr>
				<td><?=$no;?></td>
				<td><?=$data->scrh_nomor;?></td>
				<td><?=$data->scrh_lpdate;?></td>
				<td><?=$data->scrh_deptid;?></td>
				<td><?=$data->scrh_desc;?></td>
				<td><?=$data->scrd_prodid;?></td>
				<td><?=$data->scrd_qty;?></td>
			</tr>
		<? } ?>
	</table>
	<? 
}
if(($tombol=="Upload") AND ($idpilih==6)){
	$q = mysql_query("SELECT scrh_nomor,scrh_lpdate, scrh_pic, scrh_desc
					  FROM scrap_log
					  WHERE scrh_status=10
					  GROUP BY scrh_nomor,scrh_lpdate, scrh_pic, scrh_desc
					  ORDER BY scrh_nomor ASC");
	while($data=mysql_fetch_object($q)){
		$year 		= substr($data->scrh_lpdate,0,4);
		$month 		= substr($data->scrh_lpdate,5,2);
		$scrh_lpno = get_uploadlpsno($year,$month);
		
		mysql_query("INSERT INTO scrap SET scrh_nomor='$data->scrh_nomor', scrh_lpno='$scrh_lpno', scrh_lpdate='$data->scrh_lpdate', 
					  scrh_pic='$data->scrh_pic', scrh_desc='$data->scrh_desc',
					  scrh_inputdate='".get_datetime()."', scrh_status=10, scrh_inputby='Admin'");
		$flag=1;		
		//echo "$scrh_lpno == $data->scrh_lpdate<br>";	  
	}//exit;
	if($flag==1){
		$qd = mysql_query("SELECT a.id, b.scrd_prodid, b.scrd_qty
							FROM scrap a
							INNER JOIN scrap_log b ON b.scrh_nomor=a.scrh_nomor
							WHERE b.scrh_status=10
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO scrap_detail SET scrd_hid='$dt->id',scrd_prodid='$dt->scrd_prodid', scrd_qty='$dt->scrd_qty'");
		}		
		$flag=2;			
	}			
	if($flag==2){
		mysql_query("UPDATE scrap_log SET scrh_status=20 WHERE scrh_status=10");
		mysql_query("UPDATE scrap SET scrh_status=30 WHERE scrh_status=10");
		$results = "OK";
	}			
}
if(($tombol=="Proses") AND ($idpilih==7)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		 	 = $data->val($i, 1);
	  $tanggal       = $data->val($i, 2);
	  $kode          = $data->val($i, 3);
	  $cl_qty   	 = $data->val($i, 4);

	  $cl_prodid    = get_prodid($kode);
	  $cl_year 		= substr($tanggal,0,4);
	  $cl_month 	= substr($tanggal,5,2);
	  
	  //echo "$cl_prodid ==$kode<br>";
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO closing SET cl_month='$cl_month', cl_year='$cl_year', 
					  cl_prodid='$cl_prodid', cl_qty='$cl_qty', cl_inputdate='".get_datetime()."', cl_inputby='Admin'");	  
	  }			  
	$results = "OK";
	}
	//exit;
}
if(($tombol=="Proses") AND ($idpilih==8)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		 	 = $data->val($i, 1);
	  $stoh_date     = $data->val($i, 2);
	  $stoh_cekno    = $data->val($i, 3);
	  $stoh_cekdate	 = $data->val($i, 4);
	  $stoh_pic      = $data->val($i, 5);
	  $kode     	 = $data->val($i, 6);
	  $stod_qtysaldo = $data->val($i, 7);
	  $stod_qtysto   = $data->val($i, 7);

	  $stod_prodid    = get_prodid($kode);
	  $stoh_prodcat   = cek_kategori($stod_prodid);
	  
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO stockopname_log SET stoh_date='$stoh_date', stoh_cekno='$stoh_cekno', 
					  stoh_cekdate='$stoh_cekdate', stoh_pic='$stoh_pic', stod_prodid='$stod_prodid', stoh_prodcat='$stoh_prodcat',
					  stod_qtysaldo='$stod_qtysaldo', stod_qtysto='$stod_qtysto', stoh_inputdate='".get_datetime()."', stoh_status=10, stoh_inputby='Admin'");	  
	  }			  
	}
	?>	
	<table width="100%">
		<tr>
			<th>No</th>
			<th>Tanggal</th>
			<th>No Cek</th>
			<th>tanggal Cek</th>
			<th>Petugas</th>
			<th>Kode</th>
			<th>saldo</th>
			<th>stock</th>
		</tr>	
		<?
		$q = mysql_query("SELECT * FROM stockopname_log WHERE stoh_status=10");
		while($data=mysql_fetch_object($q)){
		$no++;
		?>
			<tr>
				<td><?=$no;?></td>
				<td><?=$data->stoh_date;?></td>
				<td><?=$data->stoh_cekno;?></td>
				<td><?=$data->stoh_cekdate;?></td>
				<td><?=$data->stoh_pic;?></td>
				<td><?=$data->stod_prodid;?></td>
				<td><?=$data->stod_qtysaldo;?></td>
				<td><?=$data->stod_qtysto;?></td>
			</tr>
		<? } ?>
	</table>
	<? 
}
if(($tombol=="Upload") AND ($idpilih==8)){
	$q = mysql_query("SELECT stoh_date, stoh_cekno, stoh_cekdate, stoh_pic, stoh_prodcat
					  FROM stockopname_log
					  WHERE stoh_status=10 AND stoh_prodcat <> 0
					  GROUP BY stoh_date, stoh_cekno, stoh_cekdate, stoh_pic, stoh_prodcat
					  ORDER BY stoh_date ASC");
	while($data=mysql_fetch_object($q)){
		$year 		= substr($data->stoh_date,0,4);
		$month 		= substr($data->stoh_date,5,2);
		$stoh_no    = get_uploadstono($year,$month);
		
		mysql_query("INSERT INTO stockopname SET stoh_date='$data->stoh_date', stoh_no='$stoh_no', stoh_cekno='$data->stoh_cekno', 
					  stoh_cekdate='$data->stoh_cekdate', stoh_pic='$data->stoh_pic', stoh_prodcat='$data->stoh_prodcat',
					  stoh_inputdate='".get_datetime()."', stoh_status=10, stoh_inputby='Admin'");
		$flag=1;		
		//echo "$scrh_lpno == $data->scrh_lpdate<br>";	  
	}//exit;
	if($flag==1){
		$qd = mysql_query("SELECT a.id, b.stod_prodid, b.stod_qtysaldo,stod_qtysto
							FROM stockopname a
							INNER JOIN stockopname_log b ON b.stoh_date=a.stoh_date
								   AND b.stoh_cekno=a.stoh_cekno
								   AND b.stoh_cekdate=a.stoh_cekdate
								   AND b.stoh_pic=a.stoh_pic
								   AND b.stoh_prodcat=a.stoh_prodcat
							WHERE a.stoh_status=10
							ORDER BY id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO stockopname_detail SET stod_hid='$dt->id',stod_prodid='$dt->stod_prodid', stod_qtysaldo='$dt->stod_qtysaldo', stod_qtysto='$dt->stod_qtysto'");
		}		
		$flag=2;			
	}			
	if($flag==2){
		mysql_query("UPDATE stockopname_log SET stoh_status=20 WHERE stoh_status=10");
		mysql_query("UPDATE stockopname SET stoh_status=20 WHERE stoh_status=10");
		$results = "OK";
	}			
}
if(($tombol=="Proses") AND ($idpilih==9)){
	if($kategori==1){
		$q= mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, b.qty as beg_qty, (c.qty + g.qty) as in_qty, d.qty as out_qty,
						   e.qty as adj_qty, (ifnull(b.qty,0) + ifnull(c.qty,0) + ifnull(g.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0)) as end_qty,
						   f.qty as stock_opname, 
						   ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) + ifnull(g.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0))) as dif_qty, 
						   CASE WHEN ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) + ifnull(g.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0))) > 0 THEN 'Selisih Lebih'
								WHEN ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) + ifnull(g.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0))) < 0 THEN 'Selisih Kurang'
								ELSE 'Sesuai'
						   END AS ket
					FROM product a             
					LEFT JOIN (SELECT cl_prodid, cl_qty as qty FROM closing WHERE cl_month=$bln AND cl_year=$thn ) AS b
							  ON b.cl_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_bpbdate between '$start_date' and '$end_date' 
							 group by incd_prodid) AS c ON c.incd_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=30 and b.trf_category IN (1,2) and trf_spbdate between '$start_date' and '$end_date'
							 group by trfd_prodid) AS d ON d.trfd_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, 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 b.stoh_date between '$start_date' and '$end_date'
							   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=30 and b.trf_category IN (3,5) and trf_spbdate between '$start_date' and '$end_date'
							 group by trfd_prodid) AS g ON g.trfd_prodid=a.id		      
					WHERE a.prod_cat=1  ORDER BY a.prod_code ASC");
	}elseif($kategori==2){
		$q=mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, b.qty as beg_qty, c.qty as in_qty, d.qty 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, 
						   ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0))) as dif_qty, 
						   CASE WHEN ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0))) > 0 THEN 'Selisih Lebih'
								WHEN ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0))) < 0 THEN 'Selisih Kurang'
								ELSE 'Sesuai'
						   END AS ket
					FROM product a             
					LEFT JOIN (SELECT cl_prodid, cl_qty as qty FROM closing WHERE cl_month=$bln AND cl_year=$thn ) AS b
							  ON b.cl_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_bpbdate 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_sjdate 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, 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 b.stoh_date between '$start_date' and '$end_date'
							   group by a.stod_prodid) AS f  ON f.stod_prodid=a.id     
					WHERE a.prod_cat=2  ORDER BY a.prod_code ASC");
	}elseif($kategori==3){
		$q= mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, b.qty as beg_qty, c.qty as in_qty, (d.qty + g.qty) as out_qty,
						   e.qty as adj_qty, (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) - ifnull(g.qty,0) + ifnull(e.qty,0)) as end_qty,
						   f.qty as stock_opname, 
						   ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) - ifnull(g.qty,0) + ifnull(e.qty,0))) as dif_qty, 
						   CASE WHEN ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) - ifnull(g.qty,0) + ifnull(e.qty,0))) > 0 THEN 'Selisih Lebih'
								WHEN ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) - ifnull(g.qty,0) + ifnull(e.qty,0))) < 0 THEN 'Selisih Kurang'
								ELSE 'Sesuai'
						   END AS ket
					FROM product a             
					LEFT JOIN (SELECT cl_prodid, cl_qty as qty FROM closing WHERE cl_month=$bln AND cl_year=$thn ) AS b
							  ON b.cl_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=30 and b.trf_category IN (2,4) and trf_spbdate between '$start_date' and '$end_date' 
							 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_sjdate 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, 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 b.stoh_date between '$start_date' and '$end_date' 
							   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=30 and b.trf_category IN (5,6) and trf_spbdate between '$start_date' and '$end_date' 
							 group by trfd_prodid) AS g ON g.trfd_prodid=a.id		      
					WHERE a.prod_cat=3  ORDER BY a.prod_code ASC");
	}else{
		$q= mysql_query("SELECT a.id, a.prod_code, a.prod_name, a.prod_type, a.prod_sat, b.qty as beg_qty, c.qty as in_qty, (d.qty + g.qty) as out_qty,
						   e.qty as adj_qty, (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) - ifnull(g.qty,0) + ifnull(e.qty,0)) as end_qty,
						   f.qty as stock_opname, 
						   ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) - ifnull(g.qty,0) + ifnull(e.qty,0))) as dif_qty, 
						   CASE WHEN ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) - ifnull(g.qty,0) + ifnull(e.qty,0))) > 0 THEN 'Selisih Lebih'
								WHEN ((ifnull(f.qty,0)) - (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) - ifnull(g.qty,0) + ifnull(e.qty,0))) < 0 THEN 'Selisih Kurang'
								ELSE 'Sesuai'
						   END AS ket
					FROM product a             
					LEFT JOIN (SELECT cl_prodid, cl_qty as qty FROM closing WHERE cl_month=$bln AND cl_year=$thn ) AS b
							  ON b.cl_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=30 and b.trf_category IN (4) and trf_spbdate between '$start_date' and '$end_date' 
							 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_sjdate 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, 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 b.stoh_date between '$start_date' and '$end_date' 
							   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=30 and b.trf_category IN (6) and trf_spbdate between '$start_date' and '$end_date' 
							 group by trfd_prodid) AS g ON g.trfd_prodid=a.id		      
					WHERE a.prod_cat=4  ORDER BY a.prod_code ASC");
	}
	while($data=mysql_fetch_object($q)){
		#Update Stock
		if($kategori==1){
			$cekprod = cek_product($data->id,1);
			if(empty($cekprod)){
				mysql_query("INSERT INTO stock SET stock_prodid='$data->id', stock_qty='$data->end_qty', stock_loc=1");
			}else{
				mysql_query("UPDATE stock SET stock_qty='$data->end_qty' WHERE stock_prodid='$data->id' AND stock_loc=1");
			}
		}elseif($kategori==2){
			$cekprod = cek_product($data->id,4);
			if(empty($cekprod)){
				mysql_query("INSERT INTO stock SET stock_prodid='$data->id', stock_qty='$data->end_qty', stock_loc=4");
			}else{
				mysql_query("UPDATE stock SET stock_qty='$data->end_qty' WHERE stock_prodid='$data->id' AND stock_loc=4");
			}
		}elseif($kategori==3){
			$cekprod = cek_product($data->id,3);
			if(empty($cekprod)){
				mysql_query("INSERT INTO stock SET stock_prodid='$data->id', stock_qty='$data->end_qty', stock_loc=3");
			}else{
				mysql_query("UPDATE stock SET stock_qty='$data->end_qty' WHERE stock_prodid='$data->id' AND stock_loc=3");
			}
		}elseif($kategori==4){
			$cekprod = cek_product($data->id,5);
			if(empty($cekprod)){
				mysql_query("INSERT INTO stock SET stock_prodid='$data->id', stock_qty='$data->end_qty', stock_loc=5");
			}else{
				mysql_query("UPDATE stock SET stock_qty='$data->end_qty' WHERE stock_prodid='$data->id' AND stock_loc=5");
			}
		}		
		$results = "OK";					 
	}	
}
#Pilih =10 WIP
if(($tombol=="Proses") AND ($idpilih==10)){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $id 		  	= $data->val($i, 1);
	  $wip_date   	= $data->val($i, 2);
	  $kode			= str_replace(' ','',($data->val($i, 3)));
	  $wipd_qty 	= $data->val($i, 4);
	  $wip_desc 	= $data->val($i, 5);
	  $wip_nomor 	= $data->val($i, 6);
	  #Kode Barang
	  $wipd_prodid = get_prodid($kode);
	  
	  //echo "Kode : $wipd_prodid == $kode <br> ";
	  
	  #Insert log
	  if(!empty($id)){
		  mysql_query("INSERT INTO wip_log SET wip_date='$wip_date', wipd_prodid='$wipd_prodid', 
					  wipd_qty='$wipd_qty', wip_desc='$wip_desc', wip_inputby='Admin', 
					  wip_inputdate='".get_datetime()."', wip_status=10, wip_nomor='$wip_nomor'");	  
	  }  
	}
	//exit;
	?>	
	<table width="100%">
		<tr>
			<th>No</th>
			<th>Tanggal</th>
			<th>Kode Barang</th>
			<th>Jumlah</th>
			<th>Keterangan</th>
			<th>ID</th>
		</tr>	
		<?
		$q = mysql_query("SELECT * FROM wip_log WHERE wip_status=10");
		while($data=mysql_fetch_object($q)){
		$no++;
		if ($data->wipd_prodid == 0){
			$tr = "<tr bgcolor=#FF0000>";
		}else{
			$tr = "<tr bgcolor=white>";
		}
		echo $tr;
		?>
				<td><?=$no;?></td>
				<td><?=$data->wip_date;?></td>
				<td><?=$data->wipd_prodid;?></td>
				<td><?=$data->wipd_qty;?></td>
				<td><?=$data->wip_desc;?></td>
				<td><?=$data->wip_nomor;?></td>
			</tr>
		<? } ?>
	</table>
	<?
}
if(($tombol=="Upload") AND ($idpilih==10)){
	$q = mysql_query("SELECT wip_date, wip_desc,wip_nomor
						FROM wip_log WHERE wip_status=10
						GROUP BY wip_nomor 
						ORDER BY wip_nomor ASC");
	while($data=mysql_fetch_object($q)){
		$year 		= substr($data->wip_date,0,4);
		$month 		= substr($data->wip_date,5,2);
		$wip_no = get_uploadwipno($year,$month);
		
		mysql_query("INSERT INTO wip SET wip_no='$wip_no', wip_date='$data->wip_date', wip_desc='$data->wip_desc',
		 			 wip_inputdate='".get_datetime()."', wip_status=10, wip_nomor='$data->wip_nomor', wip_inputby='Admin' ");
		$flag=1;			  
	}
	if($flag==1){
		$qd = mysql_query("SELECT a.id, b.wipd_prodid, b.wipd_qty
							FROM wip a
							INNER JOIN wip_log b ON  b.wip_nomor=a.wip_nomor
							WHERE b.wip_status=10      
							ORDER BY a.id ASC");
		while($dt=mysql_fetch_object($qd)){
			mysql_query("INSERT INTO wip_detail SET wipd_hid='$dt->id',wipd_prodid='$dt->wipd_prodid', wipd_qty='$dt->wipd_qty'");
		}		
		$flag=2;			
	}			
	if($flag==2){
		mysql_query("UPDATE wip_log SET wip_status=20 WHERE wip_status=10");
		mysql_query("UPDATE wip SET wip_status=20 WHERE wip_status=10");
		$results = "OK";
	}			
}
if($results == "OK"){
	?>
		<script language="javascript">
			alert("Proses sudah selesai...!");
		</script>
	<? 
}
?>
</body>
</html>