<?php
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . 'PHPExcel-1.8/Classes/');
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

//Source => http://www.infragistics.com/community/blogs/taz_abdeali/archive/2012/02/08/netadvantage-for-jquery-with-php-101.aspx
//Cek metode
$metode = $_SERVER['REQUEST_METHOD'];

//di aktifkan sblm session
$nameprog =basename($_SERVER['PHP_SELF']);
include ('ceklogin.php');

//aktifkan session
if(!isset($_SESSION)){session_start();}
$fkodekantorx=$_SESSION['f_kodekantor'];

//jika user diberi akses maka bisa pilih kode kantor (butuh ceklogin.php)
$bcadd_haky=(trim($bcadd_hakx)=='disabled')?0:1;

//$wherex=$bcadd_hakx;
//include("LogCekVar.php");
//die();

//open connection
include ('connect-DB.php');


if ($metode=="POST"){
	// parameter bawaan yg di dapat dari jquery easy ui grid
	$page   = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows   = isset($_POST['rows']) ? intval($_POST['rows']) : 25;
	$offset = ($page-1)*$rows;
	//utk short
	$sortx  = isset($_POST['sort']) ? ''.strval($_POST['sort']) : 'no_kirimtk';   
	$orderx = isset($_POST['order']) ? strval($_POST['order']) : 'asc'; 
	//utk search
	$nsbhidx	=isset($_POST['nsbhid']) ? trim($_POST['nsbhid'])	: "";
	$nsbhnmx	=isset($_POST['nsbhnm']) ? $_POST['nsbhnm']		: "";
	$kotax		=isset($_POST['kota'])	 ? $_POST['kota']	: "";
	$isix			=isset($_POST['isi'])		 ? $_POST['isi'] : "";
	$qx				=isset($_POST['q'])			 ? $_POST['q'] : "";
	$kuncix	  =isset($_POST['kunci'])	 ? $_POST['kunci'] : "";
	$prosesx	=isset($_POST['proses']) ? $_POST['proses'] : 0;
	$filex		=isset($_POST['file'])	 ? $_POST['file'] : "";
	$kdbrgx		=isset($_POST['kdbrg'])	 ? $_POST['kdbrg'] : "";
	$tanggalx	=isset($_POST['tanggal'])? $_POST['tanggal'] : "";
	//$addupload_tgmasukx=isset($_POST['addupload_tgmasuk'])	? $_POST['addupload_tgmasuk'] : "";
	//$addupload_tgkeluarx=isset($_POST['addupload_tgkeluar'])	? $_POST['addupload_tgkeluar'] : "";

	//utk save
	$savetox=isset($_POST['saveto'])	? $_POST['saveto'] : "";
	$saveto2x=isset($_POST['saveto2'])	? $_POST['saveto2'] : "";
	$filenamex=isset($_POST['filename'])	? $_POST['filename'] : "";
	$filename2x=isset($_POST['filename2'])	? $_POST['filename2'] : "";
	//utk delete
	$nsbhiddelx=isset($_POST['nsbhiddel'])	? $_POST['nsbhiddel'] : "";
	$deletex=isset($_POST['hapus'])	? $_POST['hapus'] : "";
	$filterRulesx=isset($_POST['filterRules']) ? $_POST['filterRules'] : 	"";

	//Kirimtk
	$addKirimtk_nokirimtkx	=isset($_POST['addKirimtk_nokirimtk'])	? $_POST['addKirimtk_nokirimtk']	: "";
	$addKirimtk_tglkirimtkx	=isset($_POST['addKirimtk_tglkirimtk'])	? $_POST['addKirimtk_tglkirimtk'] : "";
	$addKirimtk_notamanualx	=isset($_POST['addKirimtk_notamanual'])		? $_POST['addKirimtk_notamanual']		: "";
	$addKirimtk_kodecustx		=isset($_POST['addKirimtk_kodecust'])		? $_POST['addKirimtk_kodecust']		: "";
	$addKirimtk_namacustx		=isset($_POST['addKirimtk_namacust'])		? $_POST['addKirimtk_namacust']		: "";
	$addKirimtk_keteranganx=isset($_POST['addKirimtk_keterangan'])? $_POST['addKirimtk_keterangan']: "";

	//Kirimtk_detail
	$addKirimtkdetail_nokirimtkx		=isset($_POST['addKirimtkdetail_nokirimtk'])		? $_POST['addKirimtkdetail_nokirimtk']		: "";
	$addKirimtkdetail_tglkirimtkx		=isset($_POST['addKirimtkdetail_tglkirimtk'])		? $_POST['addKirimtkdetail_tglkirimtk']		: "";	
	$addKirimtkdetail_notamanualx		=isset($_POST['addKirimtkdetail_notamanual'])		? $_POST['addKirimtkdetail_notamanual']		: "";
	$addKirimtkdetail_kodecustx			=isset($_POST['addKirimtkdetail_kodecust'])			?	$_POST['addKirimtkdetail_kodecust']			: "";
	$addKirimtkdetail_kodebrgx			=isset($_POST['addKirimtkdetail_kodebrg'])			?	$_POST['addKirimtkdetail_kodebrg']			: "";
	$addKirimtkdetail_namabrgx			=isset($_POST['addKirimtkdetail_namabrg'])			? $_POST['addKirimtkdetail_namabrg']			: "";
	$addKirimtkdetail_jmlkirimtkx		=isset($_POST['addKirimtkdetail_jmlkirimtk'])		?	$_POST['addKirimtkdetail_jmlkirimtk']		: 0;
	$addKirimtkdetail_hrgkirimtkx		=isset($_POST['addKirimtkdetail_hrgkirimtk'])		? $_POST['addKirimtkdetail_hrgkirimtk']		: 0;
	$addKirimtkdetail_keteranganx	  =isset($_POST['addKirimtkdetail_keterangan'])	  ? $_POST['addKirimtkdetail_keterangan']	: "";

	//Barang customer
	//$addkirimtkcust_kodecustx			=isset($_POST['addkirimtkcust_kodecust'])		? $_POST['addkirimtkcust_kodecust']		: "";
	//$addkirimtkcust_kodebrgx				=isset($_POST['addkirimtkcust_kodebrg'])			? $_POST['addkirimtkcust_kodebrg']			: "";
	//$addkirimtkcust_namabrgx				=isset($_POST['addkirimtkcust_namabrg'])			? $_POST['addkirimtkcust_namabrg']			: "";
	//$addkirimtkcust_jmlstoktkx			=isset($_POST['addkirimtkcust_jmlstoktk'])		?	$_POST['addkirimtkcust_jmlstoktk']		: 0;
	//$addkirimtkcust_hargastoktkx		=isset($_POST['addkirimtkcust_hargastoktk'])	? $_POST['addkirimtkcust_hargastoktk']	: 0;
	//$addkirimtkcust_keteranganx		=isset($_POST['addkirimtkcust_keterangan'])	? $_POST['addkirimtkcust_keterangan']	: "";

}
else
{
	// parameter bawaan yg di dapat dari jquery easy ui grid
	$page   = isset($_GET['page']) ? intval($_GET['page']) : 1;
	$rows   = isset($_GET['rows']) ? intval($_GET['rows']) : 25;
	$offset = ($page-1)*$rows;
	//utk short
	$sortx  = isset($_GET['sort']) ? ''.strval($_GET['sort']) : 'no_kirimtk';   
	$orderx = isset($_GET['order']) ? strval($_GET['order']) : 'asc'; 
	//utk search
	$nsbhidx	=isset($_GET['nsbhid'])	? $_GET['nsbhid']	: "";
	$nsbhnmx	=isset($_GET['nsbhnm'])		? $_GET['nsbhnm']		: "";
	$kotax		=isset($_GET['kota'])	? $_GET['kota']		: "";
	$isix			=isset($_GET['isi'])		? $_GET['isi'] : "";
	$qx				=isset($_GET['q'])			? $_GET['q'] : "";
	$kuncix	  =isset($_GET['kunci'])	? $_GET['kunci'] : "";
	$prosesx	=isset($_GET['proses'])	? $_GET['proses'] : 0;
	$filex		=isset($_GET['file'])	? $_GET['file'] : "";
	$kdbrgx		=isset($_GET['kdbrg'])	? $_GET['kdbrg'] : "";
	$tanggalx	=isset($_GET['tanggal'])	? $_GET['tanggal'] : "";
	//$addupload_tgmasukx=isset($_GET['addupload_tgmasuk'])	? $_GET['addupload_tgmasuk'] : "";
	//$addupload_tgkeluarx=isset($_GET['addupload_tgkeluar'])	? $_GET['addupload_tgkeluar'] : "";

	//utk save
	$savetox=isset($_GET['saveto'])	? $_GET['saveto'] : "";
	$saveto2x=isset($_GET['saveto2'])	? $_GET['saveto2'] : "";
	$filenamex=isset($_GET['filename'])	? $_GET['filename'] : "";
	$filename2x=isset($_GET['filename2'])	? $_GET['filename2'] : "";
	//utk delete	
	$nsbhiddelx=isset($_GET['nsbhiddel'])	? $_GET['nsbhiddel'] : "";
	$deletex=isset($_GET['hapus'])	? $_GET['hapus'] : "";
	$filterRulesx=isset($_GET['filterRules']) ? $_GET['filterRules'] : 	"";
	//$filterRulesx=isset($_GET['filterRules']) ? $_GET['filterRules'] : 	$_SESSION['filterRules'];
	//$filterRulesx=isset($_GET['filterRules']) ? $_GET['filterRules'] : (isset($_SESSION['filterRules']) ? $_SESSION['filterRules']:'');
  /*
	if (isset($_GET['filterRules'])) {
		if ($_GET['filterRules']!=''){
		  $_SESSION['filterRules']=$_GET['filterRules'];
			$filterRulesx						=$_GET['filterRules'];
		}else{
			$_GET['filterRules']=$_SESSION['filterRules'];
			$filterRulesx				 =$_SESSION['filterRules'];
		}
	}else{
		if (isset($_SESSION['filterRules'])) {
			$_GET['filterRules']=$_SESSION['filterRules'];
			$filterRulesx				=$_SESSION['filterRules'];
		}
	}
	*/
}
//include("LogCekVar.php");
//die();

//aktifkan session
if(!isset($_SESSION)){session_start();}
$levelx		=isset($_SESSION['f_level'])	  ?$_SESSION['f_level']	:'99'; 
$digitke2x=isset($_SESSION['f_digitke2'])	?$_SESSION['f_digitke2']:"";
$openx		=isset($_SESSION['f_open'])	    ?$_SESSION['f_open']	:null; 
$upuserx	=isset($_SESSION['f_user'])		  ?$_SESSION['f_user']:null;
$fquerybandingx=isset($_SESSION['f_querybanding'])?$_SESSION['f_querybanding']:null;

$uptglx		=date("Y-m-d");
$subjudul =date("d-m-Y");

//parameter utk search 
$wherex ="1=1";
$wherex2="1=1";

//multisearch - simpan info variable yang terkirim dari page, post, session sebelumnya (membaca json stringify to php)
$ss=json_decode($filterRulesx);

//butuh if utk cek - https://stackoverflow.com/questions/2630013/invalid-argument-custplied-for-foreach
if (is_array($ss) || is_object($ss))
{
	//$_SESSION['filterRules']=$filterRulesx;
	foreach($ss as $value ){
 
		$fields = array("periode", "tgl_lahir");
		if (in_array(trim($value->field), $fields)){
			//simbol equal jadi =
			switch (trim($value->op)) {
				case "equal":
					$simbol='='."STR_TO_DATE('".$value->value."', '%d-%m-%Y')";
				  break;
				case "notequal":
					$simbol='!='."STR_TO_DATE('".$value->value."', '%d-%m-%Y')";
				  break;
				case "less":
					$simbol='<'."STR_TO_DATE('".$value->value."', '%d-%m-%Y')";
				  break;
				case "greater":
					$simbol='>'."STR_TO_DATE('".$value->value."', '%d-%m-%Y')";
				  break;
				default:
					$simbol="";
				  break;				 
			}
		}else{		
			//simbol equal jadi =
			switch (trim($value->op)) {
				case "equal":
					$simbol='='.$value->value;
				  break;
				case "notequal":
					$simbol='!='.$value->value;
				  break;
				case "less":
					$simbol='<'.$value->value;
				  break;
				case "greater":
					$simbol='>'.$value->value;
				  break;
				default:
					$simbol=" like '%".$value->value."%'";
				  break;				 
			}
		}

		//jika ada value nya maka di tambahkan syarat berikut ini
		if ($value->value<>""){
  		$wherex2.=" and ".$value->field.$simbol;
		}
	}
	//tambahan session custaya tetap tersimpan where nya krn menggunakan data filter
	//$_SESSION['filterRules']=$filterRulesx;
}



//multisorting - membuat menjadi multi sorting 
if ($savetox<>'1'){
	$sortx  = explode(",", $sortx);
	$orderx = explode(",", $orderx);
	$arrlength = count($sortx);
	$sorty='';
	for($x = 0; $x < $arrlength; $x++) {
		if ($x>0){
			$sorty.=',';
		}
		$sorty.=$sortx[$x].' '.$orderx[$x];
	}
	$_SESSION['sorty']=$sorty;
}else{
	if (!isset($_SESSION['sorty'])) {
		$_SESSION['sorty']=$sortx.' '.$orderx;
	}else{
		$sorty=$_SESSION['sorty'];
	}
}

//ambil no kirimtk terakhir 
//$kuncix="nokirimtk";
if ($kuncix=="nokirimtk"){
	$qstring="select count(*) from kirimtk";
	$rs = mysqli_query($conn_login,$qstring);
	$row = mysqli_fetch_row($rs);
	$nokirimtkx = substr( 100000+$row[0]+1 ,-5);
	$arr = array ('lastno'=>$nokirimtkx,'berhasil'=>true);
	echo json_encode($arr);
	die();
}

//isi combobox 
//$kuncix="kodecust";
//$qx="A";
if ($kuncix=="kodecust"){
	$qstring = "
	select kode_cust as value, nama_cust as id 
	from customer 
	where kode_cust like '%$qx%' or nama_cust like '%$qx%'
	order by kode_cust";
	//$wherex=$qstring;
	//include("LogCekVar.php");
	$rs = mysqli_query($conn_login,$qstring); //query the database for entries containing the term
	if (mysqli_num_rows($rs) == 0) {
		//echo "No rows found, nothing to print so am exiting";
		$row['text']="";
		$row['id']="";
		$result[] = $row;//build an array
	}
	while ($row = mysqli_fetch_array($rs,MYSQL_ASSOC))//loop through the retrieved values
	{
			$row['text']=htmlentities(stripslashes($row['value']));
			$row['id']=htmlentities(stripslashes($row['id']));
			$result[] = $row;//build an array
	}
	echo json_encode($result);
	die();
}
if ($kuncix=="kodebrg"){
	$qstring = "
	select kode_brg as value, nama_brg as id 
	from barang 
	where kode_brg like '%$qx%' or nama_brg like '%$qx%'
	order by kode_brg";
	//$wherex=$qstring;
	//include("LogCekVar.php");
	$rs = mysqli_query($conn_login,$qstring); //query the database for entries containing the term
	if (mysqli_num_rows($rs) == 0) {
		//echo "No rows found, nothing to print so am exiting";
		$row['text']="";
		$row['id']="";
		$result[] = $row;//build an array
	}
	while ($row = mysqli_fetch_array($rs,MYSQL_ASSOC))//loop through the retrieved values
	{
			$row['text']=htmlentities(stripslashes($row['value']));
			$row['id']=htmlentities(stripslashes($row['id']));
			$result[] = $row;//build an array
	}
	echo json_encode($result);
	die();
}
if ($kuncix=="hargabrg"){
	//khusus numeric
	$qx=$qx==''?0:$qx; 

	$qstring = "
	select kode_brg as value, hrg_kirimtk as id 
	from kirimtk_detail 
	where kode_cust='$addKirimtkdetail_kodecustx' and kode_brg='$addKirimtkdetail_kodebrgx' -- and harga_stokgd > $qx
	group by hrg_kirimtk
	order by no_kirimtk desc -- harga berdasarkan nota terakhir
	-- order by harga_stokgd";
	//$wherex=$qstring;
	//include("LogCekVar.php");
	$rs = mysqli_query($conn_login,$qstring); //query the database for entries containing the term
	if (mysqli_num_rows($rs) == 0) {
		//echo "No rows found, nothing to print so am exiting";
		$row['text']="";
		$row['id']="";
		$result[] = $row;//build an array
	}
	while ($row = mysqli_fetch_array($rs,MYSQL_ASSOC))//loop through the retrieved values
	{
			$row['text']=htmlentities(stripslashes($row['value']));
			$row['id']=htmlentities(stripslashes($row['id']));
			$result[] = $row;//build an array
	}
	echo json_encode($result);
	die();
}

//add
//$kuncix='addkirimtkdetail';
if ($kuncix=='addkirimtkdetail'){
	if ($addKirimtkdetail_jmlkirimtkx>0) {
		if ($addKirimtkdetail_nokirimtkx!="" && $addKirimtkdetail_kodebrgx!=""){
			//sebaiknya cek dulu sudah ada blm
			$qstring="
								SELECT * FROM kirimtk_detail WHERE no_kirimtk='$addKirimtkdetail_nokirimtkx' and kode_brg='$addKirimtkdetail_kodebrgx' and kode_cust='$addKirimtkdetail_kodecustx'
							";
			$rs = mysqli_query($conn_login,$qstring);
			$rowcount=mysqli_num_rows($rs);
			//jika tidak ada maka lanjut
			if ($rowcount == 0) {
				$qstring="
				  SET @TRIGGER_CHECKS = TRUE;

					INSERT IGNORE INTO `kirimtk_detail` (`no_kirimtk`,`tgl_kirimtk`,`nota_manual`,`kode_cust`,`kode_brg`,`nama_brg`,`jml_kirimtk`,`hrg_kirimtk`,`keterangan` )
					VALUES ('$addKirimtkdetail_nokirimtkx',STR_TO_DATE('$addKirimtkdetail_tglkirimtkx','%d-%m-%Y'),'$addJualtkdetail_notamanualx','$addKirimtkdetail_kodecustx','$addKirimtkdetail_kodebrgx','$addKirimtkdetail_namabrgx',$addKirimtkdetail_jmlkirimtkx,$addKirimtkdetail_hrgkirimtkx,'$addKirimtkdetail_keteranganx');

					UPDATE `stock`.`kirimtk` 
					INNER JOIN 
					 	(SELECT no_kirimtk,kode_cust ,SUM(jml_kirimtk*hrg_kirimtk) AS total FROM `stock`.`kirimtk_detail` where kirimtk_detail.no_kirimtk='$addKirimtkdetail_nokirimtkx' and kirimtk_detail.kode_cust='$addKirimtkdetail_kodecustx') temp ON temp.no_kirimtk=kirimtk.no_kirimtk AND temp.kode_cust=kirimtk.kode_cust 
					SET 
					 	kirimtk.total=temp.total;

				";
				//$arr = array ('isimsg'=>"$qstring",'berhasil'=>false);
				//echo json_encode($arr);
				//$wherex=$qstring;
				//include("LogCekVar.php");
				//die();
				$rs = mysqli_multi_query($conn_login,$qstring);
				if (!$rs) {
					$arr = array ('isimsg'=>'Invalid query: '.$qstring.' - '.mysqli_connect_error());
				}else{
					$arr = array ('isimsg'=>'<font color="blue"><b>Berhasil disave</b></font>','berhasil'=>true);
				}
			}else{
				//jika ada maka stop
				$arr = array ('isimsg'=>'<font color="red"><b>Err... kode kirimtkdetail sudah ada!</b></font>','berhasil'=>false);
			}
		}else{
				$arr = array ('isimsg'=>'<font color="red"><b>Err... kode kirimtkdetail harus di isi!</b></font>','berhasil'=>false);
		}
	}else{
		$arr = array ('isimsg'=>'<font color="red"><b>Err... jml harus >0 !</b></font>','berhasil'=>false);
	}
	echo json_encode($arr);
	die();
}
//edit
if ($kuncix=='editkirimtkdetail'){
	if ($addKirimtkdetail_jmlkirimtkx>0) {
		if ($addKirimtkdetail_nokirimtkx!="" && $addKirimtkdetail_kodebrgx!="" && $addKirimtkdetail_kodecustx!=""){
			$qstring="
  			SET @TRIGGER_CHECKS = TRUE;

				update kirimtk_detail set 
					`jml_kirimtk`	=	$addKirimtkdetail_jmlkirimtkx,	
					`hrg_kirimtk`	=	$addKirimtkdetail_hrgkirimtkx,	
					`keterangan` 	=	'$addKirimtkdetail_keteranganx'	
				where `no_kirimtk`='$addKirimtkdetail_nokirimtkx' and `kode_brg`='$addKirimtkdetail_kodebrgx' and kode_cust='$addKirimtkdetail_kodecustx';

				UPDATE `stock`.`kirimtk` 
				INNER JOIN 
					(SELECT no_kirimtk,kode_cust ,SUM(jml_kirimtk*hrg_kirimtk) AS total FROM `stock`.`kirimtk_detail` where kirimtk_detail.no_kirimtk='$addKirimtkdetail_nokirimtkx' and kirimtk_detail.kode_cust='$addKirimtkdetail_kodecustx') temp ON temp.no_kirimtk=kirimtk.no_kirimtk AND temp.kode_cust=kirimtk.kode_cust 
				SET 
					kirimtk.total=temp.total;

			";

			//$wherex=$qstring;
			//include("LogCekVar.php");
			//die();


			$rs = mysqli_multi_query($conn_login,$qstring);
			if (!$rs) {
				$arr = array ('isimsg'=>'Invalid query: '.$qstring.' - '.mysqli_connect_error(),'berhasil'=>false);
			}else{
				$arr = array ('isimsg'=>'<font color="blue"><b>Berhasil update</b></font>','berhasil'=>true);
			}
		}else{
			$arr = array ('isimsg'=>'<font color="red"><b>Err... kode kirimtkdetail harus di isi!</b></font>','berhasil'=>false);
		}
	}else{
		$arr = array ('isimsg'=>'<font color="red"><b>Err... jml harus >0 !</b></font>','berhasil'=>false);
	}
	echo json_encode($arr);
	die();
}
//del
//$kuncix='delkirimtkdetail';
//$addKirimtkdetail_nokirimtkx='1';
//$addKirimtkdetail_kodebrgx='0002';
if ($kuncix=='delkirimtkdetail'){
	if ($addKirimtkdetail_nokirimtkx!="" && $addKirimtkdetail_kodebrgx!="" && $addKirimtkdetail_kodecustx!=""){
		//hapus
		$qstring="
		      SET @TRIGGER_CHECKS = TRUE;

					delete from kirimtk_detail where 
					`no_kirimtk`='$addKirimtkdetail_nokirimtkx' and 
					`kode_cust`='$addKirimtkdetail_kodecustx' and
					`kode_brg`='$addKirimtkdetail_kodebrgx';

					UPDATE `stock`.`kirimtk` 
						INNER JOIN 
							(SELECT no_kirimtk,kode_cust ,SUM(jml_kirimtk*hrg_kirimtk) AS total FROM `stock`.`kirimtk_detail` where kirimtk_detail.no_kirimtk='$addKirimtkdetail_nokirimtkx' and kirimtk_detail.kode_cust='$addKirimtkdetail_kodecustx') temp ON temp.no_kirimtk=kirimtk.no_kirimtk AND temp.kode_cust=kirimtk.kode_cust 
					SET 
						kirimtk.total=temp.total;


					-- Otomatis index ulang (bisa di hapus kl ganggu), sumber => https://stackoverflow.com/questions/2214141/auto-increment-after-delete-in-mysql#
					-- ALTER TABLE `your_table` AUTO_INCREMENT = 1;
				 ";
		//$wherex=$qstring;
		//include("LogCekVar.php");
		//$arr = array ('isimsg'=>"$qstring",'berhasil'=>false);
		//echo json_encode($arr);
		//die();
		$rs = mysqli_multi_query($conn_login,$qstring);
		if (!$rs) {
			$arr = array ('isimsg'=>'Invalid query: '.$qstring.' - '.mysqli_connect_error(),'berhasil'=>false);
		}else{
			$arr = array ('isimsg'=>'<font color="blue"><b>Berhasil hapus : '.$addKirimtkdetail_nokirimtkx.' ; '.$addKirimtkdetail_kodebrgx.' </b></font>','berhasil'=>true);
		}
	}else{
		$arr = array ('isimsg'=>'<font color="red"><b>Err... kode kirimtkdetail harus di isi!</b></font>','berhasil'=>false);
		}
	echo json_encode($arr);
	die();
}


//add
//$kuncix='addkirimtk';
if ($kuncix=='addkirimtk'){
	if ($addKirimtk_nokirimtkx!="" && $addKirimtk_kodecustx!=""){
		//sebaiknya cek dulu sudah ada blm
		$qstring="
							SELECT * FROM kirimtk WHERE no_kirimtk='$addKirimtk_nokirimtkx' and kode_cust='$addKirimtk_kodecustx'
						";
		$rs = mysqli_query($conn_login,$qstring);
		$rowcount=mysqli_num_rows($rs);
		//jika tidak ada maka lanjut
		if ($rowcount == 0) {
			$qstring="
				INSERT INTO `stock`.`kirimtk` (
					`no_kirimtk`           , `tgl_kirimtk`                                     , `nota_manual`           , `kode_cust`           , `nama_cust`           , `keterangan`
				)VALUES(
					'$addKirimtk_nokirimtkx', STR_TO_DATE('$addKirimtk_tglkirimtkx','%d-%m-%Y'), '$addJualtk_notamanualx', '$addKirimtk_kodecustx', '$addKirimtk_namacustx', '$addKirimtk_keteranganx'
				);
			";
			$rs = mysqli_multi_query($conn_login,$qstring);
			if (!$rs) {
				$arr = array ('isimsg'=>'Invalid query: '.$qstring.' - '.mysqli_connect_error());
			}else{
				$arr = array ('isimsg'=>'<font color="blue"><b>Berhasil disave</b></font>','berhasil'=>true);
			}
		}else{
			//jika ada maka stop
			$arr = array ('isimsg'=>'<font color="red"><b>Err... kode kirimtk sudah ada!</b></font>','berhasil'=>false);
		}
	}else{
			$arr = array ('isimsg'=>'<font color="red"><b>Err... kode kirimtk harus di isi!</b></font>','berhasil'=>false);
	}
	echo json_encode($arr);
	die();
}
//edit
if ($kuncix=='editkirimtk'){
	if ($addKirimtk_nokirimtkx!="" && $addKirimtk_kodecustx!=""){
		$qstring="
			update kirimtk set 
				`tgl_kirimtk`=STR_TO_DATE('$addKirimtk_tglkirimtkx','%d-%m-%Y'),
				`nota_manual`='$addKirimtk_notamanualx',
				`kode_cust`='$addKirimtk_kodecustx', 
				`nama_cust`='$addKirimtk_namacustx', 
				`keterangan`='$addKirimtk_keteranganx'
			where `no_kirimtk`='$addKirimtk_nokirimtkx';

			update kirimtk_detail set 
				`tgl_kirimtk`=STR_TO_DATE('$addKirimtk_tglkirimtkx','%d-%m-%Y'),
				`nota_manual`='$addKirimtk_notamanualx',
				`kode_cust`='$addKirimtk_kodecustx'
			where `no_kirimtk`='$addKirimtk_nokirimtkx';

		";
		$rs = mysqli_multi_query($conn_login,$qstring);
		if (!$rs) {
			$arr = array ('isimsg'=>'Invalid query: '.$qstring.' - '.mysqli_connect_error(),'berhasil'=>false);
		}else{
			$arr = array ('isimsg'=>'<font color="blue"><b>Berhasil update</b></font>','berhasil'=>true);
		}
	}else{
		$arr = array ('isimsg'=>'<font color="red"><b>Err... kode kirimtk harus di isi!</b></font>','berhasil'=>false);
	}
	echo json_encode($arr);
	die();
}
//del
if ($kuncix=='delkirimtk'){
	if ($addKirimtk_nokirimtkx!="" && $addKirimtk_kodecustx!=""){
		//hapus
		$qstring="
					delete from kirimtk where `no_kirimtk`='$addKirimtk_nokirimtkx' and `kode_cust`='$addKirimtk_kodecustx';

					delete from kirimtk_detail where `no_kirimtk`='$addKirimtk_nokirimtkx' and `kode_cust`='$addKirimtk_kodecustx';
				 ";
		//$arr = array ('isimsg'=>"$qstring",'berhasil'=>false);
		//echo json_encode($arr);
		//die();
		$rs = mysqli_query($conn_login,$qstring);
		if (!$rs) {
			$arr = array ('isimsg'=>'Invalid query: '.$qstring.' - '.mysqli_connect_error(),'berhasil'=>false);
		}else{
			$arr = array ('isimsg'=>'<font color="blue"><b>Berhasil hapus : '.$addKirimtk_nokirimtkx.' ; '.$addKirimtk_kodecustx.' </b></font>','berhasil'=>true);
		}
	}else{
		$arr = array ('isimsg'=>'<font color="red"><b>Err... kode kirimtk harus di isi!</b></font>','berhasil'=>false);
		}
	echo json_encode($arr);
	die();
}


//simpan ke csv
//$wherex=$saveto2x;
//$wherex=$saveto2x;
//include("LogCekVar.php");
//die();
if ($savetox=='1'){
	//membuang karakter tab dan enter
	$karakteraneh = array("\r","\n");

	// query
	if ($savetox=='1'){
		$filename=$filenamex.".xls";
		$query = "
			select * from kirimtk 
			where $wherex2
			order by $sorty
		";
	} 

	//$wherex=$query;
	//include("LogCekVar.php");
	//die();

	// Execute the database query
	$result = 	$rs = mysqli_query($conn_login,$query) or die(mysqli_error());

	//sumber => https://stackoverflow.com/questions/12611148/how-to-export-data-to-an-excel-file-using-phpexcel
	// Instantiate a new PHPExcel object
	$objPHPExcel = new PHPExcel(); 
	// Set the active Excel worksheet to sheet 0
	$objPHPExcel->setActiveSheetIndex(0); 


	// Initialise the Excel row number - header
	$rowCount = 1;  
	$column = 'A';

  //20191025-format horizontal, sumber => https://stackoverflow.com/questions/21285884/how-to-center-the-text-in-phpexcel-merged-cell
	$style = array(
		'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		)
	);
	$objPHPExcel->getActiveSheet()->getStyle("A1:I1")->applyFromArray($style);
	$objPHPExcel->getActiveSheet()->getStyle("A2:I2")->applyFromArray($style);
	
	//20191025-merger cell, sumber => https://stackoverflow.com/questions/15084517/how-to-merge-column-in-excel-using-phpexcel
	//$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:I1');
  $objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
  $objPHPExcel->getActiveSheet()->mergeCells('A2:I2');
  $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, 'DAFTAR BARANG');
  $rowCount++;
  $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, 'PER TGL: '.$subjudul );
  $rowCount++;
  $rowCount++;
	//$rowCount = 4;  
	//start of printing column names as names of MySQL fields  
	$column = 'A';
	//sumber => https://stackoverflow.com/questions/14629636/mysql-field-name-to-the-new-mysqli
	while ($property = mysqli_fetch_field($result)) {
			$objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $property->name);
			$column++;
	}
	//end of adding column names  


	//20191125, setting ganti warna font dan fill cell, sumber => https://stackoverflow.com/questions/6773272/set-background-cell-color-in-phpexcel
	$fillStyle=array(
					'fill' => array(
							'type' => PHPExcel_Style_Fill::FILL_SOLID,
							'color' => array('rgb' => 'FF0000')
					)
			);
	$fontStyle = array(
    'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => 'FF0000'),
        'size'  => 11,
        'name'  => 'Calibri'
    ));


	// Initialise the Excel row number - detail
	$rowCount++;
	//$rowCount = 5; 
	// Iterate through each result from the SQL query in turn
	// We fetch each database result row into $row in turn
	while($rowx = mysqli_fetch_array($result,MYSQLI_ASSOC)){ 
			//20191125, format date cell, sumber => https://stackoverflow.com/questions/44188593/phpexcel-date-required-dd-mm-yy
			//$objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getNumberFormat()->setFormatCode('dd-mm-yyyy');
			//sumber => https://stackoverflow.com/questions/2487921/convert-date-format-yyyy-mm-dd-dd-mm-yyyy
			//$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, date("d-m-Y", strtotime($rowx['periode'])) ); 

			$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, ($rowx['no_kirimtk'])); 
			$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, ($rowx['nama_brg'])); 
			//menghilangkan character aneh			
			$rowx['keterangan']=trim(str_replace($karakteraneh, "", $rowx['keterangan'])); //tambahan saja
			$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, ($rowx['keterangan'])); 
			//$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $rowx['tempat_lahir']); 
			//$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, date("d-m-Y", strtotime($rowx['tgl_lahir'])) ); 
			//$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, $rowx['npwp']); 
			//$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, $rowx['ktp']); 
			//$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, ($rowx['identitas_lain'])); 
    
			//20191125, setting ganti warna font dan fill cell, sumber => https://stackoverflow.com/questions/6773272/set-background-cell-color-in-phpexcel
			//if (!(trim($rowx['keterangan'])==''||is_null($rowx['keterangan']))) {
			//	$objPHPExcel->getActiveSheet()->getStyle('G'.$rowCount)->applyFromArray($fontStyle);
			//}
			//$objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, $rowx['keterangan']); 

			//20191125, setting ganti warna font dan fill cell, sumber => https://stackoverflow.com/questions/6773272/set-background-cell-color-in-phpexcel
			//if (!(trim($rowx['keterangan2'])==''||is_null($rowx['keterangan2']))) {
			//	$objPHPExcel->getActiveSheet()->getStyle('H'.$rowCount)->applyFromArray($fontStyle);
			//}
			//$objPHPExcel->getActiveSheet()->SetCellValue('H'.$rowCount, $rowx['keterangan2']); 

			//$objPHPExcel->getActiveSheet()->SetCellValue('I'.$rowCount, $rowx['harapcek']); 
			//20181022, ini bisa di aktifkan jika tgl masuk dan tgl keluar karyawan sdh di isi dng benar standarnya : a.tgl>=b.tgl_masuk
			// $objPHPExcel->getActiveSheet()->SetCellValue('J'.$rowCount, $rowx['tgl_masuk']); 
			// $objPHPExcel->getActiveSheet()->SetCellValue('K'.$rowCount, $rowx['tgl_keluar']); 

			// Increment the Excel row counter
			$rowCount++; 
	} 
	
	//utk test isi query
	//$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $query); 

	//set bold
	$objPHPExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setBold(true)
                                ->setName('Verdana')
                                ->setSize(20)
                                ->getColor()->setRGB('000000');

	//20191125, set auto colomn, sumber => https://stackoverflow.com/questions/16761897/phpexcel-auto-size-column-width
	for ($i = 'A'; $i !=  $objPHPExcel->getActiveSheet()->getHighestColumn(); $i++) {
			$objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);
	}

	//20191125, tambah border => https://stackoverflow.com/questions/19397953/phpexcel-set-border-and-format-for-all-sheets-in-spreadsheet
  $borderSytle = array(
      'borders' => array(
          'allborders' => array(
              'style' => PHPExcel_Style_Border::BORDER_THIN
          )
      )
  );
  //set all sheet => $objPHPExcel->getDefaultStyle()->applyFromArray($borderSytle);
  $akhirbaris=$rowCount-1;
	$objPHPExcel->getActiveSheet()->getStyle("A4:C".$akhirbaris)->applyFromArray($borderSytle);


	// Redirect output to a client’s web browser (Excel5) 
	header('Content-Type: application/vnd.ms-excel'); 
	header('Content-Disposition: attachment;filename='.$filename); 
	header('Cache-Control: max-age=0'); 
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
	$objWriter->save('php://output');

	//info
	$arr = array ('isimsg'=>"saved");
	echo json_encode($arr);

	//$wherex=$response;
	//include("LogCekVar.php");

	die();
}


//$isix='kirimtkdetail';
if ($isix=='kirimtkdetail'){
  //initial
	$wherex2.=" and no_kirimtk='$addKirimtkdetail_nokirimtkx'";

	//query
	$qstring="select count(*) from kirimtk_detail where $wherex2";
	$rs = mysqli_query($conn_login,$qstring);
	$row = mysqli_fetch_row($rs);
	$result["total"] = $row[0];

	$qstring="
		select * from kirimtk_detail 
		where $wherex2
		order by $sorty
		limit $offset,$rows";
	$rs = mysqli_query($conn_login,$qstring);

	//membuat array dalam format (ini cara otomatis kl mau buat manual)
	$items = array();
	while($row = mysqli_fetch_object($rs)){
		//if (!is_null($row->tgllahir)){
		//	$datetmp = strtotime($row->tgllahir);
		//	$row->tgllahir=date("d-m-Y", $datetmp);
		//}
		array_push($items, $row);
	}
	//total keseluruhan isi data
	$result["rows"] = $items;
	 
	echo json_encode($result);
	die();
}

$qstring="select count(*) from kirimtk where $wherex2";
$rs = mysqli_query($conn_login,$qstring);
$row = mysqli_fetch_row($rs);
$result["total"] = $row[0];

$qstring="
  select * from kirimtk 
	where $wherex2
  order by $sorty
	limit $offset,$rows";
$rs = mysqli_query($conn_login,$qstring);

//membuat array dalam format (ini cara otomatis kl mau buat manual)
$items = array();
while($row = mysqli_fetch_object($rs)){
	//if (!is_null($row->tgllahir)){
	//	$datetmp = strtotime($row->tgllahir);
	//	$row->tgllahir=date("d-m-Y", $datetmp);
	//}
	array_push($items, $row);
}
//total keseluruhan isi data
$result["rows"] = $items;
 
echo json_encode($result);


//$wherex=$qstring;
//include("LogCekVar.php");
//die();


//echo "<br>".$qstring;
//echo "Berhasil:".$rs."<br>";
//$result = mysqli_use_result($conn_login);
//while($row = mysqli_fetch_row($result)){
//	//echo $row->no_kirimtk."---".$row->tempat_lahir."---".$row->tgl_lahir."---".$row->ktp."---".$row->npwp ."<br>";
//	echo $row[0]."---".$row[1]."---".$row[2]."---".$row[3]."---".$row[4]."---".$row[10]."<br>";
//}

die();

?>