<?php
session_start();
error_reporting(E_ALL);
include("excel/PHPExcel.php");
include("config.php");

//if ($_SESSION['username'] && $_SESSION['status'] && $_SESSION['ip'] && $_SESSION['agent'] && $_SESSION['idx'] && $_SESSION['pyrCode']) {

//include("session_regenerate.php");

$username = mysql_escape_string($_SESSION['username']);
$status = mysql_escape_string($_SESSION['status']);
//$approve = mysql_escape_string($_GET['approve']);
//$reject = mysql_escape_string($_GET['reject']);
$ip = mysql_escape_string($_SESSION['ip']);
$idx = mysql_escape_string($_SESSION['idx']);
$agent = mysql_escape_string($_SESSION['agent']);
//$pyrCode = mysql_escape_string($_SESSION[pyrCode]);
//$data_idx = mysql_escape_string($_GET['data_idx']);
$data_idx = mysql_escape_string($_POST['data_idx']);
$effective_date = mysql_escape_string($_POST['effective_date']);
//$con_date1 = mysql_escape_string($_GET['con_date1']);


$objPHPExcel = new PHPExcel();
/******************************************************* Start Header ***********************************************************************/
$DebitedAccountNo = preg_replace('/(?<=^|;)"(.+)"(?=;)/','$1',"Debited Account No");
$objPHPExcel->setActiveSheetIndex(0)
       ->setCellValue('A1', $DebitedAccountNo)
       ->setCellValue('A2', 'Total Detail Records')
       ->setCellValue('A3', 'Total Amount')
       ->setCellValue('A4', 'File Creation Time')
       ->setCellValue('A5', 'Transaction Instruction Date');

$sqlRekDebt = "
	SELECT
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '1'
		AND istatus = '1'
	)AS DAN,
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '2'
		AND istatus = '1'
	)AS BN,
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '3'
		AND istatus = '1'
	)AS CB,
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '4'
		AND istatus = '1'
	)AS SC,
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '5'
		AND istatus = '1'
	)AS NE,
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '6'
		AND istatus = '1'
	)AS EA,
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '7'
		AND istatus = '1'
	)AS BCS,
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '8'
		AND istatus = '1'
	)AS BCCC,
	(
		SELECT
			value_param
		FROM
			tm_parameter_csv_mega
		WHERE
			id_param = '9'
		AND istatus = '1'
	)AS BT
FROM
	tm_parameter_csv_mega
LIMIT 1;
";
$executeSqlRekDebt = mysql_query($sqlRekDebt) or die (mysql_error());
while($list = mysql_fetch_array($executeSqlRekDebt)) {
	$DAN=$list[0];
	$BN=$list[1];
	$CB=$list[2];
	$SC=$list[3];
	$NE=$list[4];
	$EA=$list[5];
	$BCS=$list[6];
	$BCCC=$list[7];
	$BT=$list[8];
}

$sqlHeader = "SELECT count(*) as jumlah, SUM(_amount) as total FROM tmp_file WHERE idx='$data_idx' and _code ='426' ORDER BY id ASC";
$executeSqlHeader = mysql_query($sqlHeader) or die (mysql_error());

while($row = mysql_fetch_array($executeSqlHeader)) {

	//$jumlah = $row[0];
	$jumlah = '1';
	//$total = $row[1];
	$total = '1000';
}
	//$debittedAccount ="010740011003920";
	$timeCreatedOn = date('d/m/Y');
	$stringType = PHPExcel_Cell_DataType::TYPE_STRING;
	$objPHPExcel->setActiveSheetIndex(0)
     //->setCellValueExplicit('B1', $DAN,PHPExcel_Cell_DataType::TYPE_STRING)// 010740011003920, titov@bsmi.co.id
     ->setCellValueExplicit('B1', $DAN,PHPExcel_Cell_DataType::TYPE_STRING)// 010740011003920, titov@bsmi.co.id
     ->setCellValue("B2", $jumlah)
     ->setCellValue("B3", $total)
     ->setCellValue("B4", $timeCreatedOn)
     ->setCellValue("B5", $effective_date);
/******************************************************* End Header ***********************************************************************/

/******************************************************* Start Body ***********************************************************************/
$objPHPExcel->setActiveSheetIndex(0)
       ->setCellValue('A7', 'Nama')
       ->setCellValue('B7', 'No Rekening')
       ->setCellValue('C7', 'Nominal')
       ->setCellValue('D7', 'Nama Bank Tujuan')
       ->setCellValue('E7', 'Kode Bank')
       ->setCellValue('F7', 'Service Code')
       ->setCellValue('G7', 'Remarks')
       ->setCellValue('H7', 'Notification Email')
       ->setCellValue('I7', 'Email Address')
       ->setCellValue('J7', 'Beneficiary Citizenship Status')
       ->setCellValue('K7', 'Beneficiary Citizenship Country Code')
       ->setCellValue('L7', 'Beneficiary Type');
/************************************* Start Query *******************************************************/
$sql = "SELECT * FROM tmp_file WHERE idx='$data_idx' and _code ='426' ORDER BY id ASC limit 1";
//$sql = "SELECT * FROM tmp_file WHERE idx='$data_idx' and _code not in ('506','426') ORDER BY id ASC";
//$qr_file = mysql_query("SELECT * FROM tmp_file WHERE idx='$data_idx' and _code='426' ORDER BY id ASC");
$qr_file = mysql_query($sql) or die (mysql_error());
//$qr_file = mysql_query("SELECT * FROM tmp_file WHERE idx='$data_idx' and _code='426' and _amount < 100000000 ORDER BY id ASC");
//$qr_file = mysql_query("SELECT * FROM tmp_file WHERE idx='125' and _code='426' and _amount < 100000000 ORDER BY id ASC");

$baris = 8;
$no_urut = 1;
while($temp = mysql_fetch_array($qr_file)) {
	$no = $temp[2];
	$nama = $temp[3];
	$bank = $temp[4];
	$code = $temp[5];
	$branch = $temp[6];
	//$account = $temp[7];
	$account = preg_replace('/[^0-9]/','',$temp[7]);
	//$amount = $temp[8];
	$amount = '1000';
	//$bankTujuan = "Mega";
	$kodeBank ="4260448";
	$serviceCode = "PYR";
	$remark = $temp[9];
	//$notificationEmail = "N";
	$emailAddress ="cops@bsmi.co.id";
	$beneficiaryCitizenshipStatus = '1';
	$beneficiaryCitizenshipCountryCode = "IND";
	$beneficiaryType = '1';
	//print_r($code);die;
$type = PHPExcel_Cell_DataType::TYPE_STRING;
$objPHPExcel->setActiveSheetIndex(0)
     ->setCellValue("A$baris", $nama)
     ->setCellValueExplicit("B$baris", $account,PHPExcel_Cell_DataType::TYPE_STRING)
     ->setCellValue("C$baris", $amount);
// Set Account as String, jadi jika ada nomor rekening dengan awal 0 maka akan bisa dibaca oleh Excel
// Contohnya No rekening 052201005665504 akan tetap dibaca oleh Excel 052201005665504 bukan dibaca 52201005665504
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("D$baris", $BN);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("E$baris", $CB);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("F$baris", $SC);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("G$baris", $remark);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("H$baris", $NE);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("I$baris", $EA);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("J$baris", $BCS);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("K$baris", $BCCC);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("L$baris", $BT);
$baris = $baris + 1;
}
/******************************************************* End Body ***********************************************************************/
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
//$objPHPExcel->setActiveSheetIndex(0);
$tgl = date('Ymd');
$waktu = substr(gmdate("his",time()+60*60*7),0,5);
$fileName ="payrollupload_".$tgl.$waktu;
// Redirect output to a client’s web browser (Excel5)
// Jika CSV

header('Content-Type: application/csv');
header('Content-Disposition: attachment;filename="'.$fileName.'".csv"');
header('Pragma: no-cache');

/*
// Jika xls
// We'll be outputting an excel file
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.'".xls"');
header("Cache-Control: max-age=0");
*/
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
//}
?>