<?php
include_once ("includes/setup.inc.php");

$kriteria = explode('|BRE|', fetch_postget_val("kriteria", $_POST, $_GET));
$cetakInputValue = explode('|BRE|', fetch_postget_val("cetakInputValue", $_POST, $_GET));
$tahun = fetch_postget_val("tahun", $_POST, $_GET);
$bangsa = fetch_postget_val("bangsa", $_POST, $_GET);

$dun_sc = $cetakInputValue[1];
$daerah_sc = $cetakInputValue[2];
$parlimen_sc = $cetakInputValue[3];
$program_sc = $cetakInputValue[4];
$blnthnDari_month = $cetakInputValue[5];
$blnthnDari_year = $cetakInputValue[6];
$blnthnHingga_month = $cetakInputValue[7];
$blnthnHingga_year = $cetakInputValue[8];
$sahmati_sc = $cetakInputValue[9];
$khairatkat_sc = $cetakInputValue[10];
$telahbayar_sc = $cetakInputValue[11];
$jantina_sc = $cetakInputValue[12];
$fasa_sc = $cetakInputValue[13];

$statistikResult = array();
//*****************************************
//GET Daftar
//*****************************************

$tkh_fetch = 'tkh_keyinmati';
$header_tkh = 'Tarikh Daftar Khairat';
if($telahbayar_sc == 2)
{
	$tkh_fetch = 'tkh_bayar';
	$header_tkh = 'Tarikh Pembayaran Khairat';
}
	
$Query = "SELECT m_pemohon.*, m_waris.nama_waris, m_waris.kpbaru_waris, m_waris.kpbaru_waris, i_bank.desk_bank, m_waris.no_akaunwaris,i_dun.desk_dun,
		m_waris.tel_hpwaris, m_waris.alamat_waris, m_waris.alamat_waris2, m_waris.alamat_waris3, i_poskod.desk_poskod, m_waris.bandar_waris, i_negeri.desk_negeri, m_bayaran_KH.tkh_bayar
		FROM m_pemohon
		LEFT JOIN i_dun ON m_pemohon.id_dun = i_dun.id_dun
		LEFT JOIN m_bayaran_KH ON m_bayaran_KH.id_pemohon = m_pemohon.id_pemohon
		LEFT JOIN m_waris ON m_waris.id_pemohon = m_pemohon.id_pemohon
		LEFT JOIN i_bank ON m_waris.id_bankwaris = i_bank.id_bank 
		LEFT JOIN i_poskod ON m_waris.id_poskodwaris = i_poskod.id_poskod
		LEFT JOIN i_negeri ON m_waris.id_negeriwaris = i_negeri.id_negeri
		WHERE $tkh_fetch between ? AND ? 
		AND id_statushidup = 2
		AND id_program IN (1,2,4)";

if($dun_sc > 0)
{
	$Query .= " AND m_pemohon.id_dun = ".$dun_sc;
}

if($daerah_sc > 0)
{
	$Query .= " AND i_dun.id_daerah = ".$daerah_sc;
}

if($parlimen_sc > 0)
{
	$Query .= " AND i_dun.id_parlimen = ".$parlimen_sc;
}

if($sahmati_sc > 0)
{
	$Query .= " AND m_pemohon.id_sahmati = ".$sahmati_sc;
}

if($khairatkat_sc > 0)
{
	$Query .= " AND m_pemohon.id_khairatkat = ".$khairatkat_sc;
}

if($program_sc > 0)
{
	$Query .= " AND m_pemohon.id_program = ".$program_sc;
}
	
if($bangsa > 0)
{
	if($bangsa == 5) //to include others
	{
		$Query .= " AND m_pemohon.id_bangsa NOT IN (1, 2, 3)";
	}
	else
	{
		$Query .= " AND m_pemohon.id_bangsa = ".$bangsa;
	}
}
	
if($telahbayar_sc == 1)
{
	$Query .= " AND flag_bayarkhairat = 0";
}
else if($telahbayar_sc == 2)
{
	$Query .= " AND flag_bayarkhairat = 1";
}	
	
if($jantina_sc > 0)
{
	$Query .= " AND id_jantina = ".$jantina_sc;
}
	
if($fasa_sc > 0)
{
	$Query .= " AND id_bayaranfasa = ".$fasa_sc;
}

$Query .= " ORDER BY nama_pemohon";

$PDO_stmt = $PDO->prepare($Query);
$PDO_stmt->bindValue(1, $blnthnDari_year.'-'.$blnthnDari_month.'-01 00:00:00', PDO::PARAM_STR);
$PDO_stmt->bindValue(2, $blnthnHingga_year.'-'.$blnthnHingga_month.'-31 23:59:59', PDO::PARAM_STR);

$PDO_stmt->execute();
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	/*if(!in_array($result['id_bangsa'], array(1,2,3)))
	{
		$result['id_bangsa'] = 5; //if not 1,2,3 , all go to 5 (lain-lain)
	}
	$statistikResult[formatDate($result[$tkh_fetch], 'Y')][$result['id_dun']][$result['id_bangsa']][] = $result;*/
	$statistikResult[formatDate($result[$tkh_fetch], 'Y')][] = $result;
}

/*$dun_List = array();
$PDO_stmt = $PDO->prepare("
	SELECT id_dun AS id, desk_dun as name, id_daerah
	FROM i_dun
	WHERE aktif = 1
	ORDER BY susunan
");
$PDO_stmt->execute();
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC)){
	if(($dun_sc == 0 || $result['id'] == $dun_sc) && ($daerah_sc == 0 || $result['id_daerah'] == $daerah_sc))
	{
		$dun_List[] = array($result['id'], $result['name']);
	}
}

$bangsa_List = array();
$PDO_stmt = $PDO->prepare("
	SELECT id_bangsa AS id, desk_bangsa as name
	FROM i_bangsa
	WHERE aktif = 1
	AND id_bangsa IN (1,2,3,5)
	ORDER BY susunan
");
$PDO_stmt->execute();
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC)){
	$bangsa_List[] = array($result['id'], $result['name']);
}

$program_List = array();
$PDO_stmt = $PDO->prepare("
	SELECT id_program AS id, kod_program as name
	FROM i_program
	WHERE aktif = 1
	AND id_program IN (1,2,4)
	ORDER BY susunan
");
$PDO_stmt->execute();
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC)){
	if($program_sc == 0 || $result['id'] == $program_sc)
	{
		$program_List[] = array($result['id'], $result['name']);
	}
}*/

//HEADER TO SET HERE
$headerArray = [];
$headerArray[] = 'Bil.';
$headerArray[] = 'Nama Pemohon';
$headerArray[] = 'KP Baru Pemohon';
$headerArray[] = $header_tkh;
$headerArray[] = 'DUN';	
$headerArray[] = 'Nama Waris';	
$headerArray[] = 'KP Baru Waris';	
$headerArray[] = 'No. Telefon';	
$headerArray[] = 'Alamat';	
$headerArray[] = 'Alamat(samb)';	
$headerArray[] = 'Alamat(samb)';	
$headerArray[] = 'Poskod';	
$headerArray[] = 'Bandar';	
$headerArray[] = 'Negeri';	
$headerArray[] = 'Bank';	
$headerArray[] = 'No. Akaun';	

$AUTHOR = 'jj';
$TITLE = 'Senarai Nama Statistik Permohonan Khairat';
$FILE_NAME = str_replace(' ', '_', $TITLE);
/** Error reporting */
//error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once 'classes/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator($AUTHOR)
							 ->setLastModifiedBy($AUTHOR)
							 ->setTitle($TITLE)
							 ->setSubject($TITLE)
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml $TITLE")
							 ->setCategory("Test result file");

							 
//set default style
$objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

// Add data
//One can use the possibility to access cell values by column and row index like (0,1) instead of 'A1' for reading and writing cell values in loops.
//In PHPExcel column index is 0-based while row index is 1-based. That means 'A1' ~ (0,1)
$objPHPExcel->setActiveSheetIndex(0);
$ROW_INDEX = 1; //use this to control row indexing

//print table header
$START_COLUMN = 0; //normally start at 0
$END_COLUMN = count($headerArray) - 1; //based on number of header

$START_COLUMN_ALPHABET = PHPExcel_Cell::stringFromColumnIndex($START_COLUMN); //convert to alphabet
$END_COLUMN_ALPHABET = PHPExcel_Cell::stringFromColumnIndex($END_COLUMN); //convert to alphabet

//set anything before print table
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX++, $kriteria[0]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX++, 'Kriteria Carian :');

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Daerah : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[2]);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "DUN : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[1]);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Parlimen : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[3]);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Program : ");
if($program_sc > 0)
{
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $program_List[0][1]);	
}
else
{
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[4]);
}

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Jangka Masa : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX, $kriteria[5].'/'.$kriteria[6]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 2, $ROW_INDEX, ' hingga ');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 3, $ROW_INDEX, $kriteria[7].'/'.$kriteria[8]);
$ROW_INDEX++; //move to next row

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Status Sah : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[9]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Kategori Khairat : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[10]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Status Pembayaran : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[11]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Fasa Pembayaran : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[13]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Jantina : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[12]);
$ROW_INDEX++; //move to next row
$ROW_INDEX++; //move to next row

$START_ROW = $ROW_INDEX;
$MY_COLUMN = $START_COLUMN;
foreach($headerArray as $key=>$item)
{
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item);
}
$ROW_INDEX++; //move to next row

if($tahun > 0)
{
	$blnthnDari_year = $tahun;
	$blnthnHingga_year = $tahun;
}
$counter = 1;
for($year = $blnthnDari_year; $year <= $blnthnHingga_year; $year++)
{
	/*foreach ($dun_List as $item)
	{
		foreach ($bangsa_List as $item2)
		{*/
			//if(isset($statistikResult[$year][$item[0]][$item2[0]]))
			if(isset($statistikResult[$year]))
			{
				//foreach ($statistikResult[$year][$item[0]][$item2[0]] as $item3)
				foreach ($statistikResult[$year] as $item3)
				{
					$MY_COLUMN = $START_COLUMN;
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, ($counter++));
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['nama_pemohon']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['kpbaru_pemohon']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, formatDate($item3[$tkh_fetch],'d M Y'));
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_dun']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['nama_waris']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['kpbaru_waris']);
					$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['tel_hpwaris'], PHPExcel_Cell_DataType::TYPE_STRING);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat_waris']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat_waris2']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat_waris3']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_poskod']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['bandar_waris']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_negeri']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_bank']);
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['no_akaunwaris']);
					$ROW_INDEX++; //move to next row
				}
			}
		/*}
	}*/
}
$END_ROW = $ROW_INDEX - 1;

//set border and alignment for table
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.$END_COLUMN_ALPHABET.$END_ROW)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.$END_COLUMN_ALPHABET.$END_ROW)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Table Data');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

//set auto width

for( $colNum = $START_COLUMN ; $colNum <= $END_COLUMN ; $colNum++ )
{
	$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($colNum))->setAutoSize(true);
	
}

//$objPHPExcel->getActiveSheet()->getColumnDimension($START_COLUMN_ALPHABET.':'.$END_COLUMN_ALPHABET)->setAutoSize(true);

// Redirect output to a client抯 web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$FILE_NAME.'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
?>