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

$current_year = date('Y');
$program_sc = fetch_postget_val('program_sc',$_POST, $_GET);
$daerah_sc = fetch_postget_val('daerah_sc',$_POST, $_GET);
$dun_sc = fetch_postget_val('dun_sc',$_POST, $_GET);
$tahun_sc_year = fetch_postget_val('tahun_sc_year',$_POST, $_GET);
/*
$baucer_tkh = fetch_postget_val('baucer_tkh',$_POST, $_GET);
$baucer_tkh_hingga = fetch_postget_val('baucer_tkh_hingga',$_POST, $_GET);
$baucer_from = fetch_postget_val('baucer_from',$_POST, $_GET);
$baucer_to = fetch_postget_val('baucer_to',$_POST, $_GET);
$baucer_tempat = fetch_postget_val('baucer_tempat',$_POST, $_GET);
$baucer_alamat = fetch_postget_val('baucer_alamat',$_POST, $_GET);

$baucer_from = str_replace('AM', 'PAGI', $baucer_from);
$baucer_from = str_replace('PM', 'PETANG', $baucer_from);
$baucer_to = str_replace('AM', 'PAGI', $baucer_to);
$baucer_to = str_replace('PM', 'PETANG', $baucer_to);
*/

$PDO_stmt = $PDO->prepare("SELECT * FROM i_poskod WHERE aktif = 1 ORDER BY susunan");
$PDO_stmt->execute();
$poskod_List = [];
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$poskod_List[$result['id_poskod']] = $result;
}

$PDO_stmt = $PDO->prepare("SELECT * FROM i_negeri WHERE aktif = 1 ORDER BY susunan");
$PDO_stmt->execute();
$negeri_List = [];
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$negeri_List[$result['id_negeri']] = $result;
}

$PDO_stmt = $PDO->prepare("SELECT * FROM i_daerah WHERE id_daerah = ?");
$PDO_stmt->bindValue(1, $daerah_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
$daerah_List = $PDO_stmt->fetch(PDO::FETCH_ASSOC);

$PDO_stmt = $PDO->prepare("SELECT * FROM i_dun WHERE id_dun = ?");
$PDO_stmt->bindValue(1, $dun_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
$dun_List = $PDO_stmt->fetch(PDO::FETCH_ASSOC);

$PDO_stmt = $PDO->prepare("SELECT * FROM i_program WHERE id_program = ?");
$PDO_stmt->bindValue(1, $program_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
$program_List = $PDO_stmt->fetch(PDO::FETCH_ASSOC);

$PDO_stmt = $PDO->prepare("SELECT * FROM i_kawalan LEFT JOIN i_bayaranfasa ON i_kawalan.id_bayaranfasa_tunai = i_bayaranfasa.id_bayaranfasa WHERE id_program = ?");
$PDO_stmt->bindValue(1, $program_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
$kawalan_List = $PDO_stmt->fetch(PDO::FETCH_ASSOC);
$cutoff = $kawalan_List['tkh_cutoff_tunai'];
$cutoff_saheft = $kawalan_List['tkh_cutoff_sah_EFT'];
$cutoff_aktif = $kawalan_List['tkh_cutoff_aktifmohon'];

$bayaran_table = "m_bayaran_".$program_List['kod_program'];
/*
$query = "SELECT m_pemohon.*, i_poskod.desk_poskod, i_negeri.desk_negeri, i_dun.desk_dun, i_daerah.desk_daerah 
FROM m_pemohon
LEFT JOIN i_dun ON m_pemohon.id_dun = i_dun.id_dun 
LEFT JOIN i_daerah ON i_dun.id_daerah = i_daerah.id_daerah 
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.id_program = ? 
AND m_pemohon.id_statusmohon = 4 
AND m_pemohon.id_statushidup = 1 
AND m_pemohon.id_aktifmohon = 1 
AND m_pemohon.id_dun = ? 
AND i_daerah.id_daerah = ? 
AND m_pemohon.id_pemohon NOT IN (SELECT id_pemohon FROM $bayaran_table WHERE tahun_bayar = ?)
AND m_pemohon.tkh_sah <= ?
AND (m_pemohon.tkh_saheft = ? OR m_pemohon.tkh_saheft >= ?)
ORDER BY nama_pemohon ";
*/
$tahun_bayar = $tahun_sc_year;
$select_column = "SELECT A.*, i_dun.desk_dun, i_daerah.desk_daerah  ";
$query = getNonBayarTunaiQuery($PDO, $select_column, $cutoff, $cutoff_saheft, $tahun_bayar, $cutoff_aktif);
//$query .= ' AND A.id_program = ? ';
if($tahun_bayar != $current_year)
{
	$query .= ' AND A.id_program_ori = ? ';
}
else
{
	$query .= ' AND A.id_program = ? ';
}
$query .= ' AND A.id_dun = ? ';
$query .= ' AND i_daerah.id_daerah = ? ';
$query .= ' ORDER BY nama_pemohon ';
// LIMIT 3

$PDO_stmt = $PDO->prepare($query);
$PDO_stmt->bindValue(1, $program_sc, PDO::PARAM_INT);
$PDO_stmt->bindValue(2, $dun_sc, PDO::PARAM_INT);
$PDO_stmt->bindValue(3, $daerah_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
$pemohon_List_all = [];
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$pemohon_List_all[] = $result;
}

$TITLE = 'BUKU REKOD';
$AUTHOR = 'jj';
$FILE_NAME = str_replace(' ', '_', $TITLE);

//***********************************************************************************************************************
// PRINT EXCEL
//***********************************************************************************************************************
//HEADER TO SET HERE
$headerArray = [];
$headerArray[] = 'Bil';
$headerArray[] = 'Nama';
$headerArray[] = 'KP Baru';
$headerArray[] = 'Alamat';
$headerArray[] = 'No Tel';
$headerArray[] = 'DUN';
$headerArray[] = 'Alamat Bank';
$headerArray[] = 'ADUN/PENYELARAS';
$headerArray[] = 'ADUN/PENYELARAS HP';

$AUTHOR = 'jj';
$TITLE = 'BUKU REKOD '.$program_List['desk_program'].' TAHUN '.$tahun_sc_year;
$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);
$objPHPExcel->getDefaultStyle()->getFont()->setBold(true); //set all bold

// 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); //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
$MY_COLUMN = $START_COLUMN;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, 'BUKU REKOD');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $program_List['desk_program']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, 'TAHUN '.$tahun_sc_year);

$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);
	$MY_COLUMN++;
}
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).($ROW_INDEX))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('E5E7E9');
$ROW_INDEX++; //move to next row
//$ROW_INDEX++; //move to next row

$counter = 1;
foreach ($pemohon_List_all as $item)
{
	$MY_COLUMN = $START_COLUMN;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $counter++);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item['nama_pemohon']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item['kpbaru_pemohon']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item['alamat'].' '.$item['alamat2'].' '.$poskod_List[$item['id_poskod']]['desk_poskod'].' '.$negeri_List[$item['id_negeri']]['desk_negeri']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item['tel_hp']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $dun_List['desk_dun']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $dun_List['alamat_bank']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $dun_List['nama_adun']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $dun_List['no_telhpadun']);
	$ROW_INDEX++;
}
$END_ROW = $ROW_INDEX - 1;

//set border and alignment for table
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).$END_ROW)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).$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;
//***********************************************************************************************************************
// END PRINT EXCEL
//***********************************************************************************************************************

?>

