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

$program_sc = fetch_postget_val('program_sc',$_POST, $_GET);
$tahun_mati = fetch_postget_val('tahun_mati',$_POST, $_GET);
$tahun_sah = fetch_postget_val('tahun_sah',$_POST, $_GET);

$PDO_stmt = $PDO->prepare("SELECT * FROM i_kawalan 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'];
$tahun_bayar = $kawalan_List['tahun_bayaran_tunai'];

//$tahun_sc_year = date('Y');//fetch_postget_val('tahun_sc_year',$_POST, $_GET);
$tahun_sc_year = $tahun_bayar;

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

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

$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);
	
//to get tunai

$select_column = "SELECT i_daerah.id_daerah, i_dun.id_dun, id_carabayar, count(*) AS total ";
$query = getNonBayarTunaiQuery_meninggal($PDO, $select_column, $cutoff, $cutoff_saheft, $tahun_bayar, $cutoff_aktif);
$query .= ' AND A.id_program = ? ';
$query .= " AND A.tkh_keyinmati >= '{$tahun_sah}-01-01 00:00:00' ";
$query .= " AND A.tkh_mati >= '{$tahun_mati}-01-01' ";
$query .= ' AND A.id_khairatkat = 1 ';
$query .= ' GROUP BY id_daerah, id_dun, id_carabayar';

$PDO_stmt = $PDO->prepare($query);
$PDO_stmt->bindValue(1, $program_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
$pemohon_List_all = [];
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$result['id_carabayar'] = 1; //hardcode to tunai
	$pemohon_List_all[$result['id_daerah']][$result['id_dun']][$result['id_carabayar']] += $result['total'];
	$pemohon_List_all[$result['id_daerah']]['jumlah'] += $result['total'];
	$pemohon_List_all[$result['id_daerah']]['jumlah_'.$result['id_carabayar']] += $result['total'];
}

//to get EFT

$select_column = "SELECT i_daerah.id_daerah, i_dun.id_dun, id_carabayar, count(*) AS total ";
$query = getNonBayarEftQuery_meninggal($PDO, $select_column, $cutoff, $cutoff_saheft, $tahun_bayar, $cutoff_aktif);
$query .= ' AND A.id_program = ? ';
$query .= " AND A.tkh_keyinmati >= '{$tahun_sah}-01-01 00:00:00' ";
$query .= " AND A.tkh_mati >= '{$tahun_mati}-01-01' ";
$query .= ' AND A.id_khairatkat = 1 ';
$query .= ' GROUP BY id_daerah, id_dun, id_carabayar';
//var_dump($query);
//exit(0);
$PDO_stmt = $PDO->prepare($query);
$PDO_stmt->bindValue(1, $program_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$result['id_carabayar'] = 2; //hardcode to EFT
	$pemohon_List_all[$result['id_daerah']][$result['id_dun']][$result['id_carabayar']] += $result['total'];
	$pemohon_List_all[$result['id_daerah']]['jumlah'] += $result['total'];
	$pemohon_List_all[$result['id_daerah']]['jumlah_'.$result['id_carabayar']] += $result['total'];
}
$TITLE = 'BILANGAN PENERIMA (TELAH MENINGGAL) BAGI BAYARAN '.$program_List['desk_program'].' TAHUN '.$tahun_sc_year;
$AUTHOR = 'jj';
$FILE_NAME = str_replace(' ', '_', $TITLE);
//***********************************************************************************************************************
// PRINT PDF
//***********************************************************************************************************************

require_once('classes/tcpdf/config/tcpdf_config.php');
require_once('classes/tcpdf/tcpdf.php');
include_once ("classes/phpqrcode/qrlib.php");

//include_once ("inc/setup.inc.php");
error_reporting(0);

// Extend the TCPDF class to create custom Header and Footer
class MYPDF extends TCPDF {

	private $pageTitle = '';
	public function setHeaderTitle($title)
	{
		$this->pageTitle = $title;
	}
	
	//Page header
	public function Header() {
		$this->SetFont('helveticaB', '', 10); //CHANGE THIS FOR TITLE FONT
		$this->MultiCell(0, 0, $this->pageTitle, 0, 'C', 0, 1, '', '', true);
	}

	// Page footer
	public function Footer() {
		// Position at 15 mm from bottom
		$this->SetFont('helvetica', '', 7);
		$this->MultiCell(0, 0, 'Dijana pada '.date('d-m-Y | H:i:s').' - Tarikh Sah Sehingga - Muka Surat '.$this->getAliasNumPage().'/'.$this->getAliasNbPages(), 0, 'C', 0, 1, '', '', true);
	}
}

$PDF_MARGIN_TOP = 15;

// -----------------------------------------------------------------------------
// create new PDF document
$pdf = new MYPDF("P", PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false); //P for portrait, L for landscape
$pdf->setHeaderTitle($TITLE);

// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor($AUTHOR);
$pdf->SetTitle($TITLE);
$pdf->SetSubject($TITLE);
$pdf->SetKeywords('TCPDF, PDF, $TITLE');

// set default header data
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE, PDF_HEADER_STRING);

// set header and footer fonts
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

//set margins
//$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP + $PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetMargins(10, $PDF_MARGIN_TOP, 10);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

//set auto page breaks
$pdf->SetAutoPageBreak(FALSE, PDF_MARGIN_BOTTOM);

//set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

//set some language-dependent strings
$pdf->setLanguageArray($l);

// -----------------------------------------------------------------------------


$pdf->AddPage();// add a page

$HTML = '<table border="1" style="padding:1px 1px 1px 5px;text-align:center">';
	$HTML .= '<tr style="vertical-align: middle;background-color:#C5E0FC;font-weight:bold">';
		$HTML .= '<th style="width:5%;">BIL.</th>';
		$HTML .= '<th style="width:30%;">DUN</th>';
		//$HTML .= '<th style="width:15%">JUMLAH EFT</th>';
		//$HTML .= '<th style="width:15%">JUMLAH TUNAI</th>';
		$HTML .= '<th style="width:30%">JUMLAH</th>';
		$HTML .= '<th style="width:35%">DAERAH</th>';
	$HTML .= '</tr>';
	
	$counter = 1;
	$jumlah_eft = 0;
	$jumlah_tunai = 0;
	$jumlah_kecil = 0;
	foreach($daerah_List as $dKey => $daerah)
	{
		foreach($dun_List[$dKey] as $dunItem)
		{
			$eft = $pemohon_List_all[$dKey][$dunItem['id_dun']][2];
			$tunai = $pemohon_List_all[$dKey][$dunItem['id_dun']][1];
			$jumlah = $eft + $tunai;
			$HTML .= '<tr>';
				$HTML .= '<td>'.($counter++).'</td>';
				$HTML .= '<td style="text-align:left;">'.($dunItem['desk_dun']).'</td>';
				//$HTML .= '<td>'.(($eft > 0) ? $eft : 0 ).'</td>';
				//$HTML .= '<td>'.(($tunai > 0) ? $tunai : 0 ).'</td>';
				$HTML .= '<td>'.(($jumlah > 0) ? $jumlah : 0 ).'</td>';
				$HTML .= '<td>'.($daerah['desk_daerah']).'</td>';
			$HTML .= '</tr>';
			
			$jumlah_eft += $eft;
			$jumlah_tunai += $tunai;
			$jumlah_kecil += $jumlah;
		}
	}
	$HTML .= '<tr style="font-weight:bold">';
		$HTML .= '<td></td>';
		$HTML .= '<td style="text-align:left;">JUMLAH</td>';
		//$HTML .= '<td>'.(($jumlah_eft > 0) ? $jumlah_eft : 0 ).'</td>';
		//$HTML .= '<td>'.(($jumlah_tunai > 0) ? $jumlah_tunai : 0 ).'</td>';
		$HTML .= '<td>'.(($jumlah_kecil > 0) ? $jumlah_kecil : 0 ).'</td>';
		$HTML .= '<td></td>';
	$HTML .= '</tr>';
$HTML .= '</table>';

$HTML .= '<table border="0" style="padding:3px;">';
	$HTML .= '<tr><td>&nbsp;</td></tr>';
	$HTML .= '<tr style="font-weight:bold">';
		$HTML .= '<td style="width:20%"></td>';
		$HTML .= '<td style="width:55%;border-bottom:1px solid black">JUMLAH BILANGAN PENERIMA (TELAH MENINGGAL) MENGIKUT DAERAH KESELURUHAN</td>';
	$HTML .= '</tr>';
	
	$jumlah_besar = 0;
	foreach($daerah_List as $dKey => $daerah)
	{
		$jumlah = $pemohon_List_all[$dKey]['jumlah'];
		$HTML .= '<tr>';
			$HTML .= '<th style="width:20%"></th>';
			$HTML .= '<td style="width:30%">Daerah '.($daerah['desk_daerah']).'</td>';
			$HTML .= '<td style="width:5%">:</td>';
			$HTML .= '<td style="width:20%">'.(($jumlah > 0) ? $jumlah : 0 ).'</td>';
		$HTML .= '</tr>';
		$jumlah_besar += $jumlah;
	}
	$HTML .= '<tr style="font-weight:bold">';
		$HTML .= '<td></td>';
		$HTML .= '<td style="border-top:1px solid black"><b>JUMLAH</b></td>';
		$HTML .= '<td style="border-top:1px solid black">:</td>';
		$HTML .= '<td style="border-top:1px solid black">'.(($jumlah_besar > 0) ? $jumlah_besar : 0 ).'</td>';
	$HTML .= '</tr>';
$HTML .= '</table>';

/*
$HTML .= '<table border="0" style="padding:3px;">';
	$HTML .= '<tr><td>&nbsp;</td></tr>';
	$HTML .= '<tr style="font-weight:bold">';
		$HTML .= '<td style="width:20%"></td>';
		$HTML .= '<td style="width:55%;border-bottom:1px solid black">JUMLAH BILANGAN PENERIMA (TELAH MENINGGAL) MENGIKUT DAERAH SECARA EFT</td>';
	$HTML .= '</tr>';
	
	$jumlah_besar = 0;
	foreach($daerah_List as $dKey => $daerah)
	{
		$jumlah = $pemohon_List_all[$dKey]['jumlah_2'];
		$HTML .= '<tr>';
			$HTML .= '<th style="width:20%"></th>';
			$HTML .= '<td style="width:30%">Daerah '.($daerah['desk_daerah']).'</td>';
			$HTML .= '<td style="width:5%">:</td>';
			$HTML .= '<td style="width:20%">'.(($jumlah > 0) ? $jumlah : 0 ).'</td>';
		$HTML .= '</tr>';
		$jumlah_besar += $jumlah;
	}
	$HTML .= '<tr style="font-weight:bold">';
		$HTML .= '<td></td>';
		$HTML .= '<td style="border-top:1px solid black"><b>JUMLAH</b></td>';
		$HTML .= '<td style="border-top:1px solid black">:</td>';
		$HTML .= '<td style="border-top:1px solid black">'.(($jumlah_besar > 0) ? $jumlah_besar : 0 ).'</td>';
	$HTML .= '</tr>';
	$HTML .= '<tr><td>&nbsp;</td></tr>';
$HTML .= '</table>';*/

$pdf->SetFont('helvetica', '', 8); //CHANGE THIS FOR TITLE FONT
$pdf->writeHTMLCell(0, 0, '', '', $HTML, 0, 1, 0, true, '', true);

//Close and output PDF document
ob_end_clean();
$pdf->Output($FILE_NAME.'.pdf', 'I');

//***********************************************************************************************************************
// END PRINT PDF
//***********************************************************************************************************************

?>

