<?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']) : 'parameter_cari';   
	$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'] : "";
	$tahunx		=isset($_POST['tahun'])	? $_POST['tahun'] : "";
	$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'] : 	$_SESSION['filterRules'];
	//$filterRulesx=isset($_POST['filterRules']) ? $_POST['filterRules'] : (isset($_SESSION['filterRules']) ? $_SESSION['filterRules']:'');  
	if (isset($_POST['filterRules'])) {
		if ($_POST['filterRules']!=''){
		  $_SESSION['filterRules']=$_POST['filterRules'];
			$filterRulesx						=$_POST['filterRules'];
		}else{
			$_POST['filterRules']=$_SESSION['filterRules'];
			$filterRulesx				 =$_SESSION['filterRules'];
		}
	}else{
		if (isset($_SESSION['filterRules'])) {
			$_POST['filterRules']=$_SESSION['filterRules'];
			$filterRulesx				 =$_SESSION['filterRules'];
		}
	}
}
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']) : 'parameter_cari';   
	$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'] : "";
	$tahunx		=isset($_GET['tahun'])	? $_GET['tahun'] : "";
	$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'] : 	$_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-supplied-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 supaya 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'];
	}
}

//simpan ke csv
//$wherex=$saveto2x;
//$wherex=$saveto2x;
//include("LogCekVar.php");
//die();

if ($savetox=='1' || $saveto2x=='1'){
	//membuang karakter tab dan enter
	$karakteraneh = array("\r","\n");

	// query
	if ($savetox=='1'){
		$filename=$filenamex.".xls";
		$query = "
			select periode,parameter_cari,nama_asli,tempat_lahir,tgl_lahir,npwp,ktp,identitas_lain from sipendar 
			where $wherex2
			order by $sorty
		";
	} 
	if ($saveto2x=='1'){
		$filename=$filename2x.".xls";
		$query=$fquerybandingx;
	}

	//$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 SIPENDAR');
  $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('B'.$rowCount, ($rowx['parameter_cari'])); 
			$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, ($rowx['nama_asli'])); 
			//menghilangkan character aneh			
			//$rowx['nama']=trim(str_replace($karakteraneh, "", $rowx['nama_asli'])); //tambahan saja
			$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:I1")->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:I".$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();
}


//proses upload file ke database
//$prosesx=1;
//$filex="w.xlsx";
//$tahunx=2023;
//$tanggalx='29-04-2023';
//Error Code: 2013Lost connection to MySQL server during query
//Parse error: syntax error, unexpected '000000000059' (T_LNUMBER)
if ($prosesx==1){
	//create table sipendar jika blm ada
	$table="sipendar";
	//$qstring="
	//DROP TABLE IF EXISTS ".$table;
	//$rs = mysqli_multi_query($conn_login, $qstring);
	
	$qstring="
	CREATE TABLE IF NOT EXISTS `".$table."` (
		`periode` DATE DEFAULT NULL,
		`id` CHAR(10) DEFAULT NULL,
		`kode_watch` CHAR(10) DEFAULT NULL,
		`jenis_pelaku` CHAR(10) DEFAULT NULL,
		`nama_asli` CHAR(100) DEFAULT NULL,
		`parameter_cari` CHAR(100) DEFAULT NULL,
		`tempat_lahir` CHAR(150) DEFAULT NULL,
		`tgl_lahir` DATE DEFAULT NULL,
		`npwp` CHAR(155) DEFAULT NULL,
		`ktp` CHAR(135) DEFAULT NULL,
		`pas` CHAR(20) DEFAULT NULL,
		`kitas` CHAR(20) DEFAULT NULL,
		`suket` CHAR(20) DEFAULT NULL,
		`sim` CHAR(14) DEFAULT NULL,
		`kitap` CHAR(20) DEFAULT NULL,
		`kims` CHAR(20) DEFAULT NULL,
		`identitas_lain` CHAR(160) DEFAULT NULL,
		`upuser` CHAR(20) DEFAULT NULL,
		`uptgl` DATE DEFAULT NULL
	) ENGINE=MYISAM DEFAULT CHARSET=latin1;
	";
	$rs = mysqli_multi_query($conn_login, $qstring);

	$inputFileName="temp/".$filex;
	try {
			$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
			$objReader = PHPExcel_IOFactory::createReader($inputFileType);
			$objPHPExcel = $objReader->load($inputFileName);
	} catch(Exception $e) {
			die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
	}

	//  Get worksheet dimensions
	$sheet = $objPHPExcel->getSheet(0); 
	$highestRow = $sheet->getHighestRow(); 
	$highestColumn = $sheet->getHighestColumn();

	//echo $highestRow;die();

	//$wherex=$highestRow;
	//include("LogCekVar.php");die();
  if ($highestRow>1){
		//  Loop through each row of the worksheet in turn, ambil data mulai dari row ke 2 
		$rowData=array();
		$cekperiode=0; //jika periode 0 berarti blm di cek, kalau 1 sdh di cek
		for ($row = 2; $row <= $highestRow; $row++){ 
			//gabung array
			//array_push($rowData,$sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
			//                                NULL,
			//                                TRUE,
			//                                FALSE));

			//  Read a row of data into an array
			$rowData=$sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
																			NULL,
																			TRUE,
																			FALSE);

			//convert isi array tanggal yg berupa string to date
			$rowData[0][0]= date('Ymd',strtotime($rowData[0][0])) ;
			$rowData[0][4]=addslashes($rowData[0][4]); //supaya tanda ' \ " bisa ke input
			$rowData[0][5]=addslashes($rowData[0][5]); //supaya tanda ' \ " bisa ke input
			$rowData[0][7]= date('Ymd',strtotime($rowData[0][7])) ;
			$rowData[0][8]=addslashes($rowData[0][8]); //supaya tanda ' \ " npwp - bisa ke input
			$rowData[0][9]=addslashes($rowData[0][9]); //supaya tanda ' \ " npwp - bisa ke input
			$rowData[0][16]=addslashes($rowData[0][16]); //supaya tanda ' \ " bisa ke input
			$rowData[0][]=$upuserx;
			$rowData[0][]=$uptglx;

			//hapus dl isi table sipendar jika periode yg di upload sama
			if ($cekperiode==0){
			  $qstring="delete from    `hensap`.`".$table."` where periode='".$rowData[0][0]."';";
				//$wherex=$qstring;
				//include("LogCekVar.php");die();
			  $rs   = mysqli_query($conn_login,$qstring);
				$cekperiode=1;
			}

			//Insert row data array into your database of choice here
			$valuex='"'.implode('","',$rowData[0]).'"';
			$qstring="
			INSERT INTO `".$table."` (
				`periode`,
				`id`,
				`kode_watch`,
				`jenis_pelaku`,
				`nama_asli`,
				`parameter_cari`,
				`tempat_lahir`,
				`tgl_lahir`,
				`npwp`,
				`ktp`,
				`pas`,
				`kitas`,
				`suket`,
				`sim`,
				`kitap`,
				`kims`,
				`identitas_lain`,
				`upuser`,
				`uptgl`
			) 
			VALUES
				(".
					$valuex
				.") ;
			";
			$rs   = mysqli_query($conn_login,$qstring);

			if (!$rs){
		    //$response["msg"] = "Err, Ada kesalahan di baris: $row, ".mysqli_error($conn_login);
		    $response["msg"] = "<b><font color='red'>Err, Ada kesalahan baris $row- ".mysqli_error($conn_login)."</font></b>";
				echo json_encode($response);
				die();
		  }
			//$qstring="select count(*) from ".$table;
			//$rs   = mysqli_query($conn_login,$qstring);
			////$row  = mysqli_fetch_row($rs);echo $row[0];echo $rs -> num_rows;
		}

		//informasi data terupload
    //$row=$row-2;
		//$highestRow=$highestRow-1;
		//if ($row<>$highestRow){
		//  $response["msg"] = 'Err, Ada data yg tidak terupload, data terupload='.$highestRow.', data total='.$row;
		//}else{
		//  $response["msg"] = 'Berhasil input table!!, data terupload='.$highestRow;
		//}
		$response["msg"] = "<b><font color='blue'>Berhasil input table!!</font></b>, data terupload=<b><font color='blue'>".($highestRow-1)."</font></b>";
		echo json_encode($response);
	}
	die();
}


//control UPLOAD file dari file box
//$kuncix='upload';
if ($kuncix=='upload'){
	set_time_limit(0);
	$uploadpath=(string)filter_input(INPUT_POST, 'uploadpath');
	$response = array();
	$result = array();
	set_error_handler(function ($errno, $errstr, $errfile, $errline ,array $errcontex) {
			throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
	});
	try {
		// if single file
			if(!is_array($_FILES["userfile"]["name"])) { 
					if ($_FILES["userfile"]["error"] === UPLOAD_ERR_OK) {
							//$fileName = $_FILES["userfile"]["name"];
							$fileName = $_SESSION['f_user'].'_'.md5(time()).'_'.$_FILES["userfile"]["name"];
							if (!move_uploaded_file($_FILES["userfile"]["tmp_name"],$uploadpath.DIRECTORY_SEPARATOR.$fileName)) {
									$msg="Upload not completed.<br />Error on copy file \"".$fileName."\" in folder \"".$uploadpath."\"";
									exit_with_error($msg);
							}else{
									$response["file"]= $fileName;
									$response["msg"] = 'File:'.$fileName.', berhasil di upload!!';
							}
					}
					else {
							$upldmsg = codeToMessage($_FILES["userfile"]["error"]);
							$msg = "Upload not completed.<br />\"".$_FILES["userfile"]["name"]."\" : ".$upldmsg;
							exit_with_error($msg);
					}
			}
		//else if Multiple files
			else { 
					$fileCount = count($_FILES["userfile"]["name"]);
					for($i=0; $i < $fileCount; $i++) {
							if ($_FILES["userfile"]["error"][$i] === UPLOAD_ERR_OK) {
									//$fileName = $_FILES["userfile"]["name"][$i];
									$fileName = $_SESSION['f_user'].'_'.md5(time()).'_'.$_FILES["userfile"]["name"][$i];
									if (!move_uploaded_file($_FILES["userfile"]["tmp_name"][$i],$uploadpath.DIRECTORY_SEPARATOR.$fileName)) {
											$msg="Upload not completed.<br />Error on copy file \"".$fileName."\" in folder \"".$uploadpath."\"";
											exit_with_error($msg);
									}else{
										$response["file"]= $fileName;
										$response["msg"] = 'Berhasil di upload!!';
										//echo json_encode($response);
									}
							}
							else {
									$upldmsg = codeToMessage($_FILES["userfile"]["error"][$i]);
									$msg = "Upload not completed.<br />\"".$_FILES["userfile"]["name"][$i]."\" : ".$upldmsg;
									exit_with_error($msg);
							}
					}	
			}
	} catch (ErrorException $e) {
			$response["error"] = true;
			$msg = "ERROR: Upload not completed.<br /><br /><br/><b>URL:</b> ".$_SERVER['PHP_SELF']."<br />"
							. "<b>Error on line n. :</b> ".$e->getLine()."<br/><b>MESSAGE:</b><br />";
			$response["message"] = $msg.$e->getMessage();
			//echo json_encode($response,JSON_HEX_TAG);
			echo json_encode($response);
			die;    
	}
	$response["error"] = false;
	echo json_encode($response);

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

	die();
}
function exit_with_error ($msg) {
		$response = array();
		$response["error"] = true;
		$response["message"] = $msg;
		echo json_encode($response,JSON_HEX_TAG);
		die;
}
function codeToMessage($code) { 
		switch ($code) { 
				case UPLOAD_ERR_INI_SIZE: 
						$message = "File too big. Check \"upload_max_filesize\" in php.ini."; 
						break; 
				case UPLOAD_ERR_FORM_SIZE: 
						$message = "File too big. Check \"MAX_FILE_SIZE\" specified on HTML form.";
						break; 
				case UPLOAD_ERR_PARTIAL: 
						$message = "File partially uploaded."; 
						break; 
				case UPLOAD_ERR_NO_FILE: 
						$message = "File not uploaded."; 
						break; 
				case UPLOAD_ERR_NO_TMP_DIR: 
						$message = "Temporary folder not availaible."; 
						break; 
				case UPLOAD_ERR_CANT_WRITE: 
						$message = "Write error on file-system."; 
						break; 
				case UPLOAD_ERR_EXTENSION: 
						$message = "Upload blocked by extension."; 
						break; 

				default: 
						$message = "General error."; 
						break; 
		} 
		return $message; 
}


//$kuncix="lastwatchlist";
if ($kuncix=="lastwatchlist"){
	$qstring="SELECT MAX(periode) AS periode FROM hensap.sipendar";
	$rs = mysqli_query($conn_login,$qstring);
	$row = mysqli_fetch_row($rs);
	$result["tglperiode"] = date("d-m-Y", strtotime($row[0])); //aslinya $row[0];
	$result["isimsg"] = $qstring;
	
	//$wherex=$qstring2;
	//include("LogCekVar.php");
	//die();

	echo json_encode($result);
	die();
}


//$wherex=$wherex2;
//include("LogCekVar.php");die();
//$kuncix="cekibs07";
//$_POST['nama_nasabah']=1;
if ($kuncix=="cekibs07"){
	//20230405, BELUM DI SAMBUNG KE PROGRAM, tapi query manual nya spt di bawah ini
	$namanasabahx	=isset($_POST['nama_nasabah'])	? $_POST['nama_nasabah'] : "";
	$tempatlahirx	=isset($_POST['tempatlahir'])	? $_POST['tempatlahir'] : "";
	$tgllahirx		=isset($_POST['tgllahir'])	? $_POST['tgllahir'] : "";
	$npwpx			=isset($_POST['npwp'])	? $_POST['npwp'] : "";
	$noidx			=isset($_POST['no_id'])	? $_POST['no_id'] : "";

	//$qstring="SELECT nasabah.nama_nasabah,parameter_cari, nasabah.tempatlahir, tempat_lahir, nasabah.tgllahir,tgl_lahir, nasabah.no_id, ktp
	$qstring="SELECT
				sipendar.periode,sipendar.parameter_cari,sipendar.nama_asli,sipendar.tempat_lahir,sipendar.tgl_lahir,sipendar.npwp,sipendar.ktp,sipendar.identitas_lain
			  FROM (SELECT * FROM sipendar WHERE periode =(SELECT MAX(periode) AS periode FROM sipendar)) sipendar
			  INNER JOIN bprsap.nasabah 
			  ON 1=1";
	
	if ($namanasabahx==1){
		$qstring=$qstring." AND nasabah.nama_nasabah=parameter_cari";
	}
	if ($tempatlahirx==1){
		$qstring=$qstring." AND nasabah.tempatlahir=tempat_lahir";
	}
	if ($tgllahirx==1){
		$qstring=$qstring." AND nasabah.tgllahir=tgl_lahir";
	}
	if ($npwpx==1){
		$qstring=$qstring." AND nasabah.slik_npwp=sipendar.npwp";
	}
	if ($noidx==1){
		$qstring=$qstring." AND nasabah.no_id=ktp";
	}

	$qstring2="select count(*) from (".$qstring.") as temp";
	$rs2 = mysqli_query($conn_login,$qstring2);
	$row2 = mysqli_fetch_row($rs2);
	$result2["total"] = $row2[0];
	
	//$wherex=$qstring;
	//include("LogCekVar.php");
	//die();

	//$qstring="
	//  select * from sipendar 
	//	where $wherex2
	//  order by $sorty
	//	limit $offset,$rows";

  //simpan cookies
	$_SESSION['f_querybanding']=$qstring;
	
	$qstring2=$qstring." order by $sorty limit $offset,$rows";

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

	$rs2 = mysqli_query($conn_login,$qstring2);

	//membuat array dalam format (ini cara otomatis kl mau buat manual)
	$items2 = array();
	while($row2 = mysqli_fetch_object($rs2)){
		array_push($items2, $row2);
	}
	//total keseluruhan isi data
	$result2["rows"] = $items2;
	 

	$result2["isimsg"] = "Berhasil !!!";
	$result2["query"] = $qstring2;

	echo json_encode($result2);

	die();


//1978-02-02 asli      3309090202780005
//1965-05-30 sipendar  3311093005650001
$qstring='
-- npwp
SELECT DISTINCT id,identitas_lain,jenis_pelaku,kims,kitap,kitas,kode_watch,ktp,nama_asli,si.npwp,parameter_cari,pas,periode,sim,suket,tempat_lahir,tgl_lahir,CONCAT (ketemu,",npwp") AS ketemu FROM 
(
	-- ktp
	SELECT DISTINCT id,identitas_lain,jenis_pelaku,kims,kitap,kitas,kode_watch,ktp,nama_asli,si.npwp,parameter_cari,pas,periode,sim,suket,tempat_lahir,tgl_lahir,CONCAT (ketemu,",ktp") AS ketemu FROM  
	(
		-- tgl_lahir
		SELECT DISTINCT id,identitas_lain,jenis_pelaku,kims,kitap,kitas,kode_watch,ktp,nama_asli,si.npwp,parameter_cari,pas,periode,sim,suket,tempat_lahir,tgl_lahir,CONCAT (ketemu,",tgllahir") AS ketemu FROM  
		(  
			-- tempat_lahir
			SELECT DISTINCT id,identitas_lain,jenis_pelaku,kims,kitap,kitas,kode_watch,ktp,nama_asli,si.npwp,parameter_cari,pas,periode,sim,suket,tempat_lahir,tgl_lahir,CONCAT (ketemu,",tmpt") AS ketemu FROM 		  
				(
				 -- parameter_cari 
				 SELECT si.*, "nm" AS ketemu FROM hensap.`sipendar` si 
				 INNER JOIN bprsap.nasabah na ON na.`nama_nasabah`=si.`parameter_cari`
				) si
			INNER JOIN bprsap.nasabah na ON na.`nama_nasabah`=si.`parameter_cari` AND na.`tempatlahir`=si.`tempat_lahir`
		) si
		INNER JOIN bprsap.nasabah na ON na.`nama_nasabah`=si.`parameter_cari` AND na.`tempatlahir`=si.`tempat_lahir` AND na.`tgllahir`=si.`tgl_lahir`
	) si
	INNER JOIN bprsap.nasabah na ON na.`nama_nasabah`=si.`parameter_cari` AND na.`tempatlahir`=si.`tempat_lahir` AND na.`tgllahir`=si.`tgl_lahir` AND na.`no_id`=si.`ktp`
) si
INNER JOIN bprsap.nasabah na ON na.`nama_nasabah`=si.`parameter_cari` AND na.`tempatlahir`=si.`tempat_lahir` AND na.`tgllahir`=si.`tgl_lahir` AND na.`no_id`=si.`ktp` AND na.`npwp`=si.`npwp`
';

$qstring_salah='
-- npwp
SELECT DISTINCT id,identitas_lain,jenis_pelaku,kims,kitap,kitas,kode_watch,ktp,nama_asli,si.npwp,parameter_cari,pas,periode,sim,suket,tempat_lahir,tgl_lahir,CONCAT (ketemu,",npwp") AS ketemu FROM 
(
	-- ktp
	SELECT DISTINCT id,identitas_lain,jenis_pelaku,kims,kitap,kitas,kode_watch,ktp,nama_asli,si.npwp,parameter_cari,pas,periode,sim,suket,tempat_lahir,tgl_lahir,CONCAT (ketemu,",ktp") AS ketemu FROM  
	(
		-- tgl_lahir
		SELECT DISTINCT id,identitas_lain,jenis_pelaku,kims,kitap,kitas,kode_watch,ktp,nama_asli,si.npwp,parameter_cari,pas,periode,sim,suket,tempat_lahir,tgl_lahir,CONCAT (ketemu,",tgllahir") AS ketemu FROM  
		(  
			-- tempat_lahir
			SELECT DISTINCT id,identitas_lain,jenis_pelaku,kims,kitap,kitas,kode_watch,ktp,nama_asli,si.npwp,parameter_cari,pas,periode,sim,suket,tempat_lahir,tgl_lahir,CONCAT (ketemu,",tmpt") AS ketemu FROM 		  
			(
				-- parameter_cari 
				SELECT si.*, "nm" AS ketemu FROM hensap.`sipendar` si 
				INNER JOIN (
					SELECT nasabah.nama_nasabah,nasabah.tempatlahir,nasabah.tgllahir,nasabah.no_id,nasabah.npwp FROM (
					SELECT tabung.`nasabah_id`,"TAB" AS ket FROM tabung WHERE tabung.saldo_akhir>0
					UNION
					SELECT deposito.`nasabah_id`,"DEP" AS ket FROM deposito WHERE deposito.saldo_akhir_pokok>0
					UNION
					SELECT kredit.`nasabah_id`,"KRE" AS ket FROM kredit WHERE kredit.pokok_saldo_akhir>0 
					)temp INNER JOIN nasabah ON temp.nasabah_id=nasabah.`nasabah_id`
					GROUP BY nasabah.`nasabah_id`
				) na ON na.`nama_nasabah`=si.`parameter_cari`
			) si
			INNER JOIN (
				SELECT nasabah.nama_nasabah,nasabah.tempatlahir,nasabah.tgllahir,nasabah.no_id,nasabah.npwp FROM (
				SELECT tabung.`nasabah_id`,"TAB" AS ket FROM tabung WHERE tabung.saldo_akhir>0
				UNION
				SELECT deposito.`nasabah_id`,"DEP" AS ket FROM deposito WHERE deposito.saldo_akhir_pokok>0
				UNION
				SELECT kredit.`nasabah_id`,"KRE" AS ket FROM kredit WHERE kredit.pokok_saldo_akhir>0 
				)temp INNER JOIN nasabah ON temp.nasabah_id=nasabah.`nasabah_id`
				GROUP BY nasabah.`nasabah_id`
			) na ON na.`tempatlahir`=si.`tempat_lahir`
		) si
		INNER JOIN (
			SELECT nasabah.nama_nasabah,nasabah.tempatlahir,nasabah.tgllahir,nasabah.no_id,nasabah.npwp FROM (
			SELECT tabung.`nasabah_id`,"TAB" AS ket FROM tabung WHERE tabung.saldo_akhir>0
			UNION
			SELECT deposito.`nasabah_id`,"DEP" AS ket FROM deposito WHERE deposito.saldo_akhir_pokok>0
			UNION
			SELECT kredit.`nasabah_id`,"KRE" AS ket FROM kredit WHERE kredit.pokok_saldo_akhir>0 
			)temp INNER JOIN nasabah ON temp.nasabah_id=nasabah.`nasabah_id`
			GROUP BY nasabah.`nasabah_id`
		) na ON na.`tgllahir`=si.`tgl_lahir`
	) si
	INNER JOIN (
		SELECT nasabah.nama_nasabah,nasabah.tempatlahir,nasabah.tgllahir,nasabah.no_id,nasabah.npwp FROM (
		SELECT tabung.`nasabah_id`,"TAB" AS ket FROM tabung WHERE tabung.saldo_akhir>0
		UNION
		SELECT deposito.`nasabah_id`,"DEP" AS ket FROM deposito WHERE deposito.saldo_akhir_pokok>0
		UNION
		SELECT kredit.`nasabah_id`,"KRE" AS ket FROM kredit WHERE kredit.pokok_saldo_akhir>0 
		)temp INNER JOIN nasabah ON temp.nasabah_id=nasabah.`nasabah_id`
		GROUP BY nasabah.`nasabah_id`
	) na ON na.`no_id`=si.`ktp`
) si
INNER JOIN (
	SELECT nasabah.nama_nasabah,nasabah.tempatlahir,nasabah.tgllahir,nasabah.no_id,nasabah.npwp FROM (
	SELECT tabung.`nasabah_id`,"TAB" AS ket FROM tabung WHERE tabung.saldo_akhir>0
	UNION
	SELECT deposito.`nasabah_id`,"DEP" AS ket FROM deposito WHERE deposito.saldo_akhir_pokok>0
	UNION
	SELECT kredit.`nasabah_id`,"KRE" AS ket FROM kredit WHERE kredit.pokok_saldo_akhir>0 
	)temp INNER JOIN nasabah ON temp.nasabah_id=nasabah.`nasabah_id`
	GROUP BY nasabah.`nasabah_id`
) na ON na.`npwp`=si.`npwp`
';




}


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

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

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

//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);


//echo "<br>".$qstring;
//echo "Berhasil:".$rs."<br>";
//$result = mysqli_use_result($conn_login);
//while($row = mysqli_fetch_row($result)){
//	//echo $row->parameter_cari."---".$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();

?>