<?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);
$program = fetch_postget_val("program", $_POST, $_GET);

//var_dump($kriteria);
//var_dump($program);
$dun_sc = $cetakInputValue[1];
$daerah_sc = $cetakInputValue[2];
$parlimen_sc = $cetakInputValue[3];
$program_sc = (($program > 0)?$program:$cetakInputValue[4]);
$blnthnDari_month = $cetakInputValue[5];
$blnthnDari_year = $cetakInputValue[6];
$blnthnHingga_month = $cetakInputValue[7];
$blnthnHingga_year = $cetakInputValue[8];
$statusmohon_sc = $cetakInputValue[9];
$statushidup_sc = $cetakInputValue[10];
$aktifmohon_sc = $cetakInputValue[11];
$carabayar_sc = $cetakInputValue[12];
$bangsa_sc = $cetakInputValue[13];
$bayaran_sc = $cetakInputValue[14];

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

$tkh_fetch = 'tkh_daftar';
$header_tkh = 'Tarikh Daftar';
if($statusmohon_sc == 4)
{
	$tkh_fetch = 'tkh_sah';
	$header_tkh = 'Tarikh Sah';
}
	
if($program_sc == 5) //anak emas
{
$Query = "SELECT nama_anakemas, kpbaru_anakemas, m_anakemas.$tkh_fetch, m_pemohon.id_dun, 5 AS id_program, i_dun.desk_dun, m_pemohon.nama_pemohon, m_pemohon.kpbaru_pemohon, m_pemohon.tel_hp, m_pemohon.nama_pasangan, m_pemohon.kpbaru_pasangan, m_pemohon.tel_hppasangan, i_poskod.desk_poskod, i_negeri.desk_negeri, m_pemohon.alamat, m_pemohon.alamat2, m_pemohon.alamat3, m_pemohon.bandar
		FROM m_anakemas
		LEFT JOIN m_pemohon ON m_pemohon.id_pemohon = m_anakemas.id_pemohon
		LEFT JOIN i_dun ON m_pemohon.id_dun = i_dun.id_dun
		LEFT JOIN i_poskod ON m_pemohon.id_poskod = i_poskod.id_poskod
		LEFT JOIN i_negeri ON m_pemohon.id_negeri = i_negeri.id_negeri
		WHERE m_anakemas.$tkh_fetch between ? AND ?";
}
else
{
$Query = "SELECT m_pemohon.*, i_poskod.desk_poskod, i_negeri.desk_negeri, i_dun.desk_dun
		FROM m_pemohon
		LEFT JOIN i_dun ON m_pemohon.id_dun = i_dun.id_dun
		LEFT JOIN i_poskod ON m_pemohon.id_poskod = i_poskod.id_poskod
		LEFT JOIN i_negeri ON m_pemohon.id_negeri = i_negeri.id_negeri
		WHERE m_pemohon.$tkh_fetch between ? AND ?";
}

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($statusmohon_sc > 0)
{
	if($program_sc == 5) //anak emas
	{
		$Query .= " AND m_anakemas.id_statusmohon = ".$statusmohon_sc;
	}
	else
	{
		$Query .= " AND m_pemohon.id_statusmohon = ".$statusmohon_sc;
	}
}
	
if($bangsa_sc == 1)
{
	$Query .= " AND m_pemohon.id_bangsa = 1";
}
else if($bangsa_sc == 2)
{
	$Query .= " AND m_pemohon.id_bangsa = 2";
}
else if($bangsa_sc == 3)
{
	$Query .= " AND m_pemohon.id_bangsa = 3";
}
else if($bangsa_sc == 5)
{
	$Query .= " AND (m_pemohon.id_bangsa = 0 OR m_pemohon.id_bangsa > 3)";
}

if($program_sc != 5)
{
	if($statushidup_sc > 0)
	{
		$Query .= " AND m_pemohon.id_statushidup = ".$statushidup_sc;
	}

	if($aktifmohon_sc > 0)
	{
		$Query .= " AND m_pemohon.id_aktifmohon = ".$aktifmohon_sc;
	}

	if($carabayar_sc > 0)
	{
		$Query .= " AND m_pemohon.id_carabayar = ".$carabayar_sc;
	}	
}

if($program_sc == 5) //anak emas
{
	if($bayaran_sc == 1)
	{
		$Query .= " AND m_anakemas.flag_bayaran = 0";
	}
	else if($bayaran_sc == 2)
	{
		$Query .= " AND m_anakemas.flag_bayaran = 1";		
	}
	
	$Query .= " ORDER BY m_anakemas.nama_anakemas";
}
else
{
	$Query .= " ORDER BY m_pemohon.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))
{
	$statistikResult[formatDate($result[$tkh_fetch], 'Y')][$result['id_dun']][$result['id_program']][] = $result;
}
//var_dump($statistikResult);
$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']);
	}
}

$program_List = array();
$PDO_stmt = $PDO->prepare("
	SELECT id_program AS id, kod_program as name
	FROM i_program
	WHERE aktif = 1
	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
$nama = 'Nama Pemohon';
$kpbaru = 'KP Baru Pemohon';
if($program_sc == 5)
{
	$nama = 'Nama Anak Emas';
	$kpbaru = 'No MyKid';	
}

$headerArray = [];
$headerArray[] = 'Bil.';
$headerArray[] = $nama;
$headerArray[] = $kpbaru;
$headerArray[] = $header_tkh;
if($program_sc == 5)
{
	$headerArray[] = 'Nama Pemohon';	
	$headerArray[] = 'KP Baru Pemohon';	
	$headerArray[] = 'No. Tel. Pemohon';	
	$headerArray[] = 'Nama Pasangan';	
	$headerArray[] = 'KP Baru Pasangan';	
	$headerArray[] = 'No. Tel. Pasangan';
	$headerArray[] = 'Alamat';	
	$headerArray[] = 'Alamat(samb)';	
	$headerArray[] = 'Alamat(samb)';	
	$headerArray[] = 'Poskod';	
	$headerArray[] = 'Bandar';	
	$headerArray[] = 'Negeri';	
	$headerArray[] = 'DUN';	
}
else
{
	$headerArray[] = 'No. Telefon';	
	$headerArray[] = 'Alamat';	
	$headerArray[] = 'Alamat(samb)';	
	$headerArray[] = 'Alamat(samb)';	
	$headerArray[] = 'Poskod';	
	$headerArray[] = 'Bandar';	
	$headerArray[] = 'Negeri';	
	$headerArray[] = 'DUN';	
}


$AUTHOR = 'jj';
$TITLE = 'Senarai Nama Statistik Permohonan Borang';
$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 : ");

/*if($tahun > 0)
{
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX, $tahun);
}
else
{
	$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]);
}*/

$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 Permohonan : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[9]);
if($program_sc != 5)
{
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Status Hidup : ");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[10]);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Status Aktif : ");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[11]);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Cara Pembayaran : ");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[12]);
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Bangsa : ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[13]);
if($program_sc == 5)
{
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN, $ROW_INDEX, "Status Pembayaran : ");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($START_COLUMN + 1, $ROW_INDEX++, $kriteria[14]);
}
$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 ($program_List as $item2)
		{
			if(isset($statistikResult[$year][$item[0]][$item2[0]]))
			{
				foreach ($statistikResult[$year][$item[0]][$item2[0]] as $item3)
				{
					//var_dump($item3);
					$MY_COLUMN = $START_COLUMN;
					$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, ($counter++));
					if($program_sc == 5)
					{
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['nama_anakemas']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['kpbaru_anakemas']);
					}
					else
					{
						$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'));
					if($program_sc == 5)
					{
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['nama_pemohon']);
						$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['kpbaru_pemohon'], PHPExcel_Cell_DataType::TYPE_STRING);
						$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['tel_hp'], PHPExcel_Cell_DataType::TYPE_STRING);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['nama_pasangan']);
						$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['kpbaru_pasangan'], PHPExcel_Cell_DataType::TYPE_STRING);
						$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['tel_hppasangan'], PHPExcel_Cell_DataType::TYPE_STRING);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat2']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat3']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_poskod']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['bandar']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_negeri']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_dun']);
					}
					else
					{
						$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['tel_hp'], PHPExcel_Cell_DataType::TYPE_STRING);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat2']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['alamat3']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_poskod']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['bandar']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_negeri']);
						$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item3['desk_dun']);
					}
					$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;
?>