<?php
# Baca Variabel Form
$message = array();
$Act	 	 = isset($_REQUEST['Act']) ? $_REQUEST['Act'] : '';
$kategori 	 = isset($_REQUEST['kategori']) ? $_REQUEST['kategori'] : '';
$start_date	 = isset($_REQUEST['fromdate']) ? $_REQUEST['fromdate'] : $start_date;
$end_date 	 = isset($_REQUEST['todate']) ? $_REQUEST['todate'] : $end_date;
$month 		 = substr($start_date,5,2);
$year		 = substr($start_date,0,4);
$month_end	 = substr($end_date,5,2);
$year_end	 = substr($end_date,0,4);
if($month_end==12){
	$month_close = 1;
	$year_close  = $year_end+1;
}else{
	$month_close = $month_end+1;
	$year_close  = $year_end;
}
$tanggal ="$year_close-$month_close-01";
if($Act =="SAVE"){
	if(count($message)==0){	
		#Bahan Baku
		if($kategori==1){
			$q= mysql_query("SELECT a.id, (ifnull(b.qty,0) + ifnull(c.qty,0) - (ifnull(d.qty,0) - ifnull(g.qty,0)) + ifnull(e.qty,0)) as end_qty
			FROM product a             
			LEFT JOIN (SELECT stock_prodid, stock_qty as qty FROM stock WHERE MONTH(stock_date)='$month' AND YEAR(stock_date)='$year' AND stock_status=10) 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_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=20 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=20 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 IN(1,2)  ORDER BY a.prod_code ASC");
		}elseif($kategori==2){
			$q=mysql_query("SELECT a.id, (ifnull(b.qty,0) + ifnull(c.qty,0) - ifnull(d.qty,0) + ifnull(e.qty,0)) as end_qty
			FROM product a             
			LEFT JOIN (SELECT stock_prodid, stock_qty as qty FROM stock WHERE MONTH(stock_date)='$month' AND YEAR(stock_date)='$year' AND stock_status=10) 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_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 IN(3,4,8)  ORDER BY a.prod_code ASC");
		}elseif($kategori==3){
			$q= mysql_query("SELECT a.id, (ifnull(b.qty,0) + (ifnull(c.qty,0)- ifnull(g.qty,0)) - ifnull(d.qty,0) + ifnull(e.qty,0)) as end_qty
			FROM product a             
			LEFT JOIN (SELECT stock_prodid, stock_qty as qty FROM stock WHERE MONTH(stock_date)='$month' AND YEAR(stock_date)='$year' AND stock_status=10) 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 '$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=20 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=5 ORDER BY a.prod_code ASC");
		}elseif($kategori==4){
			$q= mysql_query("SELECT a.id, (ifnull(b.qty,0) + (ifnull(c.qty,0)- ifnull(g.qty,0)) - ifnull(d.qty,0) + ifnull(e.qty,0)) as end_qty
			FROM product a             
			LEFT JOIN (SELECT stock_prodid, stock_qty as qty FROM stock WHERE MONTH(stock_date)='$month' AND YEAR(stock_date)='$month' AND stock_status=10) 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 '$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=20 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=6 ORDER BY a.prod_code ASC");
		}
		while($data=mysql_fetch_object($q)){
			#cek kategory
			$cekkat    = cek_kategori($data->id);
			#Delete data sebelumnya
			mysql_query("DELETE FROM stock WHERE MONTH(stock_date)='$month_close' AND YEAR(stock_date)='$year_close' AND stock_status=10 AND stock_prodid='$data->id'");
			#Insert			
			if(($cekkat==1 || $cekkat==2) && $data->end_qty > 0){ #bahan baku
				mysql_query("INSERT INTO stock SET stock_trid=0, stock_date='$tanggal', stock_prodid='$data->id', stock_qty='$data->end_qty', stock_status=10, stock_loc=1");
			}elseif(($cekkat==3 || $cekkat==4 || $cekkat==8) && $data->end_qty > 0){ #barang modal
				mysql_query("INSERT INTO stock SET stock_trid=0, stock_date='$tanggal', stock_prodid='$data->id', stock_qty='$data->end_qty', stock_status=10, stock_loc=4");
			}elseif($cekkat==5 && $data->end_qty > 0){ #barang jadi
				mysql_query("INSERT INTO stock SET stock_trid=0, stock_date='$tanggal', stock_prodid='$data->id', stock_qty='$data->end_qty', stock_status=10, stock_loc=3");
			}elseif($cekkat==6 && $data->end_qty > 0){ #barang scrap
				mysql_query("INSERT INTO stock SET stock_trid=0, stock_date='$tanggal', stock_prodid='$data->id', stock_qty='$data->end_qty', stock_status=10, stock_loc=5");
			}
			$flag = 1;							
		}
		if($flag==1){
			#Insert History User
			$itemp = mysql_query("INSERT INTO user_activity SET user_id='$user_id', user_act='Closing data kategori $kategori tanggal $start_date sampai $end_date', 
								  user_date='$date', user_time='$time'");
			$message[] = "Closing data sukses !";	
			$status = "OK";
		}
	}
}	
if($Act == "ADD"){
	require_once "add_closing.php";
}
if($Act == "CLOSE"){
	echo "<meta http-equiv='refresh' content='0; url=?page=Data-Closing'>";
}
if (! count($message)==0 ){
	?>
		<script language="javascript">
			var pesan ='<?php 
							$Num=0;
							foreach ($message as $indeks=>$pesan_tampil) { 
								$Num++;
								echo $Num.'. '.$pesan_tampil.'\n';
							}
						?>';
			alert(pesan);
		</script>
	<?php  
	if ($status=="OK"){	
		echo "<meta http-equiv='refresh' content='0; url=?page=Data-Closing'>";	
		exit;
	}
}	
?>