<?php
$message 	 	= array();
$Act         	= isset($_REQUEST['Act']) ? $_REQUEST['Act'] : '';
$pilih			= isset($_REQUEST['pilih']) ? $_REQUEST['pilih'] : '';
$pilih_reg		= isset($_REQUEST['pilih_reg']) ? $_REQUEST['pilih_reg'] : '';

#Proses
if($Act=="GET"){
	$data 	= new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
	$baris 	= $data->rowcount($sheet_index=0);
	for ($i=2; $i<=$baris; $i++)
	{
	  $message = array();
	  $no 		 	= $data->val($i, 1);
	  $no_sj 		= $data->val($i, 2);
	  $tgl_sj 		= date("Y-m-d",strtotime($data->val($i,3)));
	  $no_aju 		= $data->val($i, 4);
	  $tgl_aju 		= date("Y-m-d",strtotime($data->val($i,5)));
	  $nm_penerima  = $data->val($i, 6);
	  $jenis_dokpab = $data->val($i, 7);
	  $no_dokpab  	= $data->val($i, 8);
	  $tgl_dokpab  	= date("Y-m-d",strtotime($data->val($i,9)));
	  $no_npe  		= $data->val($i, 10);
	  $tgl_npe  	= date("Y-m-d",strtotime($data->val($i,11)));
	  $no_invoice	= $data->val($i, 12);
	  $tgl_invoice 	= date("Y-m-d",strtotime($data->val($i,13)));
	  $no_do		= $data->val($i, 14);
	  $kode  		= $data->val($i, 15);
	  $nm_barang	= $data->val($i, 16);	  
	  $satuan  		= $data->val($i, 17);
	  $jumlah  		= $data->val($i, 18);
	  $nilai_nidr	= $data->val($i, 19);
	  $nilai_idr	= $data->val($i, 20);
	  $valuta  		= $data->val($i, 21);
	  $berat  		= $data->val($i, 22);
	  $no_urut  	= $data->val($i, 23);
	  $tujuan	  	= $data->val($i, 24);
	  $no_kontrak  	= $data->val($i, 25);
	  $tgl_kontrak 	= date("Y-m-d",strtotime($data->val($i,26)));
	  $tipe  		= $data->val($i, 27);
	  $ukuran  		= $data->val($i, 28);	  
	  $house_bl  		= $data->val($i, 29);
	  $master_bl  		= $data->val($i, 30);
	  $no_kontainer  	= $data->val($i, 31);
	  $pos  			= $data->val($i, 32);
	  $nomor_manifes  	= $data->val($i, 33);
	  $tanggal_manifes  = date("Y-m-d",strtotime($data->val($i,34)));
	  $pelabuhan  		= $data->val($i, 35);
	  $jumlah_kemasan  	= $data->val($i, 36);
	  $harga_maklon  	= $data->val($i, 37);
	  $harga_fob  		= $data->val($i, 38);
	  $tipe_dokumen		= $data->val($i, 39);
	  
	  if(!empty($no)){
		  mysql_query("INSERT INTO log_outgoing SET no_sj='$no_sj', tgl_sj='$tgl_sj', kode='$kode', jumlah='$jumlah', valuta='$valuta', nilai='$nilai_idr', 
		  			   log_status=1000, log_input=now(), log_user='$user_id', nm_penerima='".mysql_real_escape_string($nm_penerima)."', jenis_dokpab='$jenis_dokpab', no_dokpab='$no_dokpab',
					   tgl_dokpab='$tgl_dokpab', berat='$berat', no_aju='$no_aju', tgl_aju='$tgl_aju', no_invoice='$no_invoice', tgl_invoice='$tgl_invoice', no_do='$no_do',
					   no_npe='$no_npe', tgl_npe='$tgl_npe', no_urut='$no_urut', nm_barang='".mysql_real_escape_string($nm_barang)."', satuan='$satuan', non_rupiah='$nilai_nidr', tujuan='$tujuan',
					   no_kontrak='$no_kontrak', tgl_kontrak='$tgl_kontrak', tipe='$tipe', ukuran='$ukuran', house_bl='$house_bl',
					   master_bl='$master_bl', no_kontainer='$no_kontainer', pos='$pos', nomor_manifes='$nomor_manifes', pelabuhan='$pelabuhan',
					   jumlah_kemasan='$jumlah_kemasan', harga_maklon='$harga_maklon', harga_fob='$harga_fob', tanggal_manifes='$tanggal_manifes', tipe_dokumen='$tipe_dokumen'"); 
	  }
	}
}
if($Act=="PROCESS"){
	if(!empty($pilih)){	
		foreach ( $pilih as $i => $val)
		{
			#Update status temp
			mysql_query("UPDATE log_outgoing SET log_status=2000 WHERE id='$val' ");
		}
		require "add_upload_pengeluaran.php";
	}else{
		require "add_upload_pengeluaran.php";
	}	
}elseif($Act=="INV"){
	#Insert header pemasukan
	$qh=mysql_query("SELECT * FROM log_outgoing WHERE log_status=2000 AND log_user='$user_id' GROUP BY no_dokpab, jenis_dokpab");	
	while($dh=mysql_fetch_array($qh)){
		$ary_nomor 		= get_nomorout(1);
		$ary_nosj 		= get_nosjout(1);
		if(!empty($dh['no_sj'])){
			$no_sj = $dh['no_sj'];
		}else{
			$no_sj = $ary_nosj;
		}
		#Insert Pemasukan Headar
		mysql_query("INSERT INTO outgoing SET outh_sjno='$no_sj', outh_sjdate='$dh[tgl_sj]', outh_invno='$dh[no_invoice]', outh_doctype='$dh[jenis_dokpab]',
		             outh_invdate='$dh[tgl_invoice]', outh_dono='$dh[no_do]', outh_docno='$dh[no_dokpab]', outh_docdate='$dh[tgl_dokpab]', nm_penerima='".mysql_real_escape_string($dh['nm_penerima'])."',
					 outh_status=13, outh_inputby='$user_id', outh_inputdate='$datetime', outh_nomor='$dh[flag]', no_aju='$dh[no_aju]', tgl_aju='$dh[tgl_aju]',
					 no_sppb='$dh[no_sppb]', tgl_sppb='$dh[tgl_sppb]', no_ppb='$dh[no_ppb]', tgl_ppb='$dh[tgl_ppb]', no_npe='$dh[no_npe]', tgl_npe='$dh[tgl_npe]',
					 no_jaminan='$dh[no_jaminan]', tgl_jaminan='$dh[tgl_jaminan]', jumlah_jaminan='$dh[jumlah_jaminan]', jenis_dokpab_asal='$dh[jenis_dokpab_asal]',
					 no_dokpab_asal='$dh[no_dokpab_asal]', tgl_dokpab_asal='$dh[tgl_dokpab_asal]', jatuh_tempo='$dh[jatuh_tempo]', no_kontainer='$dh[no_kontainer]',
					 ukuran_kontainer='$dh[ukuran_kontainer]', tipe_kontainer='$dh[tipe_kontainer]', jumlah_kemasan='$dh[jumlah_kemasan]', kode_kemasan='$dh[kode_kemasan]',
					 jenis_kemasan='$dh[jenis_kemasan]', valuta='$dh[valuta]', sumber='UPLOAD', status_dokpab='$dh[status_dokpab]', id_header='$dh[id_header]',
					 no_izin='$dh[no_izin]', tgl_izin='$dh[tgl_izin]', tujuan='$dh[tujuan]', no_kontrak='$dh[no_kontrak]', tgl_kontrak='$dh[tgl_kontrak]',
					 house_bl='$dh[house_bl]',master_bl='$dh[master_bl]', pos='$dh[pos]', nomor_manifes='$dh[nomor_manifes]', pelabuhan='$dh[pelabuhan]', 
					 harga_maklon='$dh[harga_maklon]', harga_fob='$dh[harga_fob]', tanggal_manifes='$dh[tanggal_manifes]', tipe_dokumen='$dh[tipe_dokumen]'");
		#Aktivitas User
		mysql_query("INSERT INTO user_activity SET user_id='$user_id',user_act='Menambahkan transaksi pengeluaran No. $dh[no_sj]', user_date='$date', user_time='$time'");
		$status=1;
	}
	if($status == 1){
		$qd = mysql_query("SELECT b.id,a.tgl_sj,a.kode,a.jumlah,a.valuta,a.nilai,a.berat,a.no_urut, a.kode_hs, a.non_rupiah, a.harga_fob, a.harga_maklon
						  FROM log_outgoing a  INNER JOIN outgoing b ON b.outh_docno=a.no_dokpab AND a.jenis_dokpab=b.outh_doctype												 
						  WHERE a.log_status =2000 AND b.outh_status=13 AND b.sumber='UPLOAD' AND a.log_user='$user_id' ORDER BY b.id, a.no_urut ASC");
		while($dd = mysql_fetch_array($qd)){
			$prd2 = mysql_fetch_array(mysql_query("SELECT id FROM product WHERE prod_code='$dd[kode]' AND prod_cat<>100"));
			if($dd['valuta'] =="IDR"){$unit_price=$dd['nilai']/$dd['jumlah'];}else{$unit_price=$dd['non_rupiah']/$dd['jumlah'];}
			
			mysql_query("INSERT INTO outgoing_detail SET outd_hid='$dd[id]', outd_prodid='$prd2[id]', 
						 outd_qty='$dd[jumlah]', outd_price='$dd[nilai]', outd_currency='$dd[valuta]', harga_maklon='$dd[harga_maklon]', harga_fob='$dd[harga_fob]',
						 outd_weight='$dd[berat]', outd_wunit='KG', no_urut='$dd[no_urut]', kode_hs='$dd[kode_hs]', outd_price_usd='$dd[non_rupiah]', outd_price_satuan='$unit_price'");
			#insert temp
			mysql_query("INSERT INTO outgoing_temp SET outd_prodid='$prd2[id]', outd_qty='$dd[jumlah]', outd_price='$dd[nilai]', outd_currency='$dd[valuta]', 
						outd_hid='$dd[id]', outd_weight='$dd[berat]', outd_wunit='KG',  outd_price_usd='$dd[non_rupiah]'");				 
		}
		$status = 2;
	}
	if($status == 2){
		/*$qd2 = mysql_query("SELECT a.*, b.id outh_id FROM log_outgoing_dokasal a
						   INNER JOIN outgoing b ON b.id_header=a.id_header
						   WHERE a.log_status=2 AND b.outh_doctype='$jenis_dokpab' ORDER BY a.no_urut,kode_dokasal, no_urut_dokpab ASC ");
		while($dd2 = mysql_fetch_array($qd2)){
			mysql_query("INSERT INTO outgoing_dokasal SET outh_hid='$dd2[outh_id]', no_urut='$dd2[no_urut]', no_urut_dokpab='$dd2[no_urut_dokpab]', 
			             kode_barang='$dd2[kode_barang]', nm_barang='$dd2[nm_barang]', satuan='$dd2[satuan]', jumlah='$dd2[jumlah]', nilai='$dd2[nilai]',
						 noaju_dokpab_asal='$dd2[noaju_dokpab_asal]', jenis_dokpab_asal='$dd2[jenis_dokpab_asal]', no_dokpab_asal='$dd2[no_dokpab_asal]',
						 tgl_dokpab_asal='$dd2[tgl_dokpab_asal]', nourut_dokpab_asal='$dd2[nourut_dokpab_asal]', kode_dokasal='$dd2[kode_dokasal]',
						 log_status=20, id_header='$dd2[id_header]'");
		}*/
		#cek header outgoing
		$qh = mysql_query("SELECT id, outh_sjno FROM outgoing WHERE outh_status=13 AND sumber='UPLOAD' AND outh_inputby='$user_id' ORDER BY id ASC");
		while($dh=mysql_fetch_array($qh)){
			#cek item ditemp
			/*$qc = mysql_query("SELECT a.*, b.prod_code FROM outgoing_temp a INNER JOIN product b ON b.id=a.outd_prodid WHERE a.outd_hid='$dh[id]' 
			ORDER BY a.outd_prodid ASC");
			while($dtc=mysql_fetch_array($qc)){
				$stock = get_stockfg($dtc['outd_prodid'],$start_date,$end_date,$inv);
				if($dtc['outd_qty'] > $stock){
					 $messagecek[] = "Kode Barang :$dtc[prod_code] Jumlah pengeluaran lebih besar dari jumlah stock : $dtc[outd_qty] > $stock";
				 }
			}*/
			if (count($message)==0 ){
				#Update Status
				#$qd = mysql_query("UPDATE outgoing SET outh_status=14 WHERE id='$dh[id]'");
				#get Exbc
				get_outexbc($start_date,$end_date,$dh['id']);					
			}
			#delete temp
			mysql_query("DELETE FROM outgoing_temp WHERE outd_hid='$dh[id]'");
			#Update Status
			$qd = mysql_query("UPDATE outgoing SET outh_status=15 WHERE id='$dh[id]'");
			#Insert History User
			$itemp = mysql_query("INSERT INTO user_activity SET user_id='$user_id', user_act='Posting transaksi pengeluaran No. $dh[outh_sjno]', user_date='$date', 
					 user_time='$time'");
			#$status = "OK";
		}		
		$status = 3;
	}
	if($status == 3){	
		#update status log dokumen asal
		#mysql_query("UPDATE outgoing_dokasal SET log_status=3 WHERE log_status=2 AND outh_doctype='BC 2.7'");
		#update status log
		mysql_query("UPDATE log_outgoing SET log_status=3000 WHERE log_status=2000 AND log_user='$user_id'");
		#header
		mysql_query("UPDATE outgoing SET outh_status=20 WHERE outh_status=15 AND sumber='UPLOAD'");
		$message[] = "Data Sukses dikirim.";	
		$status    = "OK";
	}		 
}elseif($Act=="REG"){
	$search = "";
	if(!empty($pilih_reg)){	
		foreach ( $pilih_reg as $i => $val)
		{
			$kd_barang   = $_REQUEST['kd_barang'][$val];
			$nm_barang   = $_REQUEST['nm_barang'][$val];
			$satuan      = $_REQUEST['satuan'][$val];
			$kd_kategori = $_REQUEST['kd_kategori'][$val];
			$tipe        = $_REQUEST['tipe'][$val];
			$ukuran      = $_REQUEST['ukuran'][$val];
			#Insert master barang
			if($kd_kategori != "BLANK"){
				#Update barang ke log outgoing
				mysql_query("UPDATE log_outgoing SET kode='$kd_barang', nm_barang='$nm_barang', satuan='$satuan', tipe='$tipe', ukuran='$ukuran' WHERE id='$val'");
				#Cek data di master barang
				$cek = mysql_fetch_array(mysql_query("SELECT prod_code FROM product WHERE prod_code='$kd_barang'"));
				if(empty($cek['prod_code'])){
					#Insert kode barau
					mysql_query("INSERT INTO product SET prod_code='$kd_barang', prod_name='$nm_barang', prod_sat='$satuan', prod_cat='$kd_kategori', prod_status=10, prod_type='$tipe', prod_spec='$ukuran'");
					#Aktivitas user					
					mysql_query("INSERT INTO user_activity SET user_id='$user_id', user_act='Menambahkan data barang - $kd_barang', user_date='$date', user_time='$time'");
				}
			}
		}
		require "add_upload_pengeluaran.php";
	}else{
		require "add_upload_pengeluaran.php";
	}	
}elseif($Act=="DEL_TEMP"){
	mysql_query("UPDATE log_outgoing SET log_status=100 WHERE log_status=1000 AND log_user='$user_id'");
	require "add_upload_pengeluaran.php";
}elseif($Act=="DELDET_TEMP"){
	mysql_query("UPDATE log_outgoing SET log_status=1000 WHERE id='".$_REQUEST['id_temp']."' ");
	require "add_upload_pengeluaran.php";
}elseif($Act=="ADD" || $Act=="GET" || $Act=="TEMP"){
	require "add_upload_pengeluaran.php";
}elseif($Act=="CANCEL" || $Act=="CLOSE"){
	echo "<meta http-equiv='refresh' content='0; url=?page=Data-Upload-Pengeluaran'>";	
	exit;
}
#Messages
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-Upload-Pengeluaran'>";	
		exit;
	}else{
		echo "<meta http-equiv='refresh' content='0; url=?page=Proses-Upload-Pengeluaran&Act=TEMP'>";
	}
}
?>