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

$program = fetch_postget_val('program',$_POST, $_GET);
$all_program = explode(',', $program);
$bayaran_dari = fetch_postget_val('bayaran_dari',$_POST, $_GET);
$current_year = date('Y');

$PDO_stmt = $PDO->prepare("SELECT * FROM i_daerah WHERE aktif = 1 ORDER BY susunan");
$PDO_stmt->bindValue(1, $daerah_sc, PDO::PARAM_INT);
$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 id_dun > 1 AND aktif = 1 ORDER BY susunan");
$PDO_stmt->bindValue(1, $dun_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
$dun_List = [];
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$dun_List[$result['id_dun']] = $result;
}

$PDO_stmt = $PDO->prepare("SELECT * FROM i_program WHERE aktif = 1 ORDER BY susunan");
$PDO_stmt->bindValue(1, $dun_sc, PDO::PARAM_INT);
$PDO_stmt->execute();
$program_List = [];
while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$program_List[$result['id_program']] = $result;
}


//*********************************************************************************************************
//(FOR EFT)
//*********************************************************************************************************
$pemohon_List_daerah = [];
$pemohon_List_program = [];
/*
$query = "SELECT i_daerah.id_daerah, id_program, id_carabayar, count(*) AS total 
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 
WHERE 
m_pemohon.id_statusmohon = 4 
AND m_pemohon.id_statushidup = 1 
AND m_pemohon.id_aktifmohon = 1 
";

//to check inside all program 
$PDO_stmt = $PDO->prepare("
	SELECT *
	FROM i_program
	WHERE aktif = 1
	AND id_program IN (1,2,3,4,7)
");
$PDO_stmt->bindValue(1, $id_program, PDO::PARAM_INT);
$PDO_stmt->execute();
$p_list = [];
while($res = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$mbayar_table = "m_bayaran_".$res['kod_program'];
	$p_list[] = ' m_pemohon.id_pemohon NOT IN (SELECT id_pemohon FROM '.$mbayar_table.' WHERE tahun_bayar = '.date('Y').') ';
}
if(count($p_list) > 0)
{
	$query .= ' AND '.join(' AND ', $p_list).'';
}
$query .= ' GROUP BY id_daerah, id_program, id_carabayar';

$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))
{
	$pemohon_List_daerah[$result['id_daerah']][$result['id_carabayar']] += $result['total'] * $program_List[$result['id_program']]['jum_bayar'];
	$pemohon_List_program[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] = $result['total'];
}
*/

//to get i_kawalan cutoff date
$PDO_stmt = $PDO->prepare("
	SELECT *
	FROM i_kawalan
	WHERE id_program IN (1,2,3,4,7)
");
$PDO_stmt->execute();
$queryList_EFT = [];
$queryList_Tunai = [];
while($res = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$cutoff = $res['tkh_cutoff_EFT'];
	$cutoff_saheft = $res['tkh_cutoff_sah_EFT'];
	$cutoff_aktif = $res['tkh_cutoff_aktifmohon'];
	$tahun_bayar = $bayaran_dari;
	$select_column = "SELECT i_daerah.id_daerah, i_dun.id_dun, id_program, id_program_ori, id_carabayar, count(*) AS total ";
	$query = getNonBayarEftQuery($PDO, $select_column, $cutoff, $cutoff_saheft, $tahun_bayar, $cutoff_aktif);
	//$query .= ' AND A.id_program = '.$res['id_program'];
	if($tahun_bayar != $current_year)
	{
		$query .= ' AND A.id_program_ori = '.$res['id_program'];
	}
	else
	{
		$query .= ' AND A.id_program = '.$res['id_program'];
	}
	$query .= ' GROUP BY id_daerah, id_dun, id_carabayar';
	$queryList_EFT[] = $query;
	
	$cutoff = $res['tkh_cutoff_tunai'];
	$cutoff_saheft = $res['tkh_cutoff_sah_EFT'];
	$cutoff_aktif = $res['tkh_cutoff_aktifmohon'];
	$tahun_bayar = $bayaran_dari;
	$select_column = "SELECT i_daerah.id_daerah, i_dun.id_dun, id_program, id_program_ori, id_carabayar, count(*) AS total ";
	$query = getNonBayarTunaiQuery($PDO, $select_column, $cutoff, $cutoff_saheft, $tahun_bayar, $cutoff_aktif);
	//$query .= ' AND A.id_program = '.$res['id_program'];
	if($tahun_bayar != $current_year)
	{
		$query .= ' AND A.id_program_ori = '.$res['id_program'];
	}
	else
	{
		$query .= ' AND A.id_program = '.$res['id_program'];
	}
	$query .= ' GROUP BY id_daerah, id_dun, id_carabayar';
	$queryList_Tunai[] = $query;
}

foreach ($queryList_EFT as $queries)
{
	$PDO_stmt2 = $PDO->prepare($queries);
	$PDO_stmt2->execute();
	while($result = $PDO_stmt2->fetch(PDO::FETCH_ASSOC))
	{
		$result['id_carabayar'] = 2;//hardcode to eft
		//$pemohon_List_daerah[$result['id_daerah']][$result['id_carabayar']] += $result['total'] * $program_List[$result['id_program']]['jum_bayar'];
		//$pemohon_List_program[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		if($tahun_bayar != $current_year)
		{
			$pemohon_List_daerah[$result['id_daerah']][$result['id_carabayar']] += $result['total'] * $program_List[$result['id_program_ori']]['jum_bayar'];
			$pemohon_List_program[$result['id_daerah']][$result['id_program_ori']][$result['id_carabayar']] += $result['total'];
		}
		else
		{
			$pemohon_List_daerah[$result['id_daerah']][$result['id_carabayar']] += $result['total'] * $program_List[$result['id_program']]['jum_bayar'];
			$pemohon_List_program[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		}
	}
}

foreach ($queryList_Tunai as $queries)
{
	$PDO_stmt2 = $PDO->prepare($queries);
	$PDO_stmt2->execute();
	while($result = $PDO_stmt2->fetch(PDO::FETCH_ASSOC))
	{
		$result['id_carabayar'] = 1;//hardcode to tunai
		//$pemohon_List_daerah[$result['id_daerah']][$result['id_carabayar']] += $result['total'] * $program_List[$result['id_program']]['jum_bayar'];
		//$pemohon_List_program[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		if($tahun_bayar != $current_year)
		{
			$pemohon_List_daerah[$result['id_daerah']][$result['id_carabayar']] += $result['total'] * $program_List[$result['id_program_ori']]['jum_bayar'];
			$pemohon_List_program[$result['id_daerah']][$result['id_program_ori']][$result['id_carabayar']] += $result['total'];
		}
		else
		{
			$pemohon_List_daerah[$result['id_daerah']][$result['id_carabayar']] += $result['total'] * $program_List[$result['id_program']]['jum_bayar'];
			$pemohon_List_program[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		}
	}
}
//*********************************************************************************************************
//END (FOR TUNAI)
//*********************************************************************************************************

//***********************************************************************************************************************
// PRINT PDF
//***********************************************************************************************************************
$TITLE = 'LAPORAN PROGRAM i-SEJAHTERA BAGI PENERIMA BELUM DIBAYAR TAHUN '.$bayaran_dari;
$AUTHOR = 'jj';
$FILE_NAME = str_replace(' ', '_', $TITLE);

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 = '<h3 style="text-align:right;">LAMPIRAN A</h3>';
$HTML .= '<h1></h1>';
$HTML .= '<h3>LAPORAN PROGRAM i-SEJAHTERA BAGI PENERIMA BELUM DIBAYAR TAHUN '.$bayaran_dari.'</h3>';
$HTML .= '<table border="1" style="padding:5px;text-align:center">';
	$HTML .= '<tr style="vertical-align: middle;background-color:#C5E0FC;font-weight:bold">';
		$HTML .= '<th style="width:30%;">DAERAH</th>';
		$HTML .= '<th style="width:20%;">EFT<br>(RM)</th>';
		$HTML .= '<th style="width:20%;">TUNAI<br>(RM)</th>';
		$HTML .= '<th style="width:30%">JUMLAH MENGIKUT<br>DAERAH (RM)</th>';
	$HTML .= '</tr>';
	
	$jumlah = 0;
	foreach ($daerah_List as $dKey => $item)
	{		
		$value_eft = $pemohon_List_daerah[$dKey][2];
		$value_tunai = $pemohon_List_daerah[$dKey][1];
		$value_all = $pemohon_List_daerah[$dKey][1] + $pemohon_List_daerah[$dKey][2];
		$jumlah += $value_all;
		
		$HTML .= '<tr>';
			$HTML .= '<td>'.$item['desk_daerah'].'</td>';
			$HTML .= '<td style="text-align:right">'.number_format(($value_eft > 0?$value_eft : 0), 2).'</td>';
			$HTML .= '<td style="text-align:right">'.number_format(($value_tunai > 0?$value_tunai : 0), 2).'</td>';
			$HTML .= '<td style="text-align:right">'.number_format(($value_all > 0?$value_all : 0), 2).'</td>';
		$HTML .= '</tr>';
	}
	$HTML .= '<tr style="font-weight:bold">';
		$HTML .= '<td>JUMLAH</td>';
		$HTML .= '<td></td>';
		$HTML .= '<td></td>';
		$HTML .= '<td style="text-align:right">'.number_format(($jumlah > 0?$jumlah : 0), 2).'</td>';
	$HTML .= '</tr>';
$HTML .= '</table>';

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

//2nd page
$counter = 0;
foreach ($daerah_List as $dKey => $item)
{
	if($counter % 2 == 0)
	{
		$pdf->AddPage();// add a page
	}
	$counter++;
	$HTML = '<h3>Pejabat Daerah dan Tanah '.$item['desk_daerah'].'</h3>';
	$HTML .= '<table border="1" style="padding:3px;text-align:center">';
		$HTML .= '<tr style="vertical-align: middle;background-color:#C5E0FC;font-weight:bold">';
			$HTML .= '<th style="width:21%;"></th>';
			$HTML .= '<th style="width:12%;">Penerima EFT</th>';
			$HTML .= '<th style="width:12%;">Penerima Tunai</th>';
			$HTML .= '<th style="width:10%;">Kadar<br>(RM)</th>';
			$HTML .= '<th style="width:15%;">Jumlah EFT<br>(RM)</th>';
			$HTML .= '<th style="width:15%;">Jumlah Tunai<br>(RM)</th>';
			$HTML .= '<th style="width:15%;">Jumlah Keseluruhan<br>(RM)</th>';
		$HTML .= '</tr>';
		
		$jumlah = [];
		foreach ($all_program as $pItem)
		{		
			$value_eft = $pemohon_List_program[$dKey][$pItem][2];
			$value_tunai = $pemohon_List_program[$dKey][$pItem][1];
			$kadar = $program_List[$pItem]['jum_bayar'];
			$total_eft = $value_eft * $kadar;
			$total_tunai = $value_tunai * $kadar;
			$total_all = $total_eft + $total_tunai;
			
			$jumlah['value_eft'] += $value_eft;
			$jumlah['value_tunai'] += $value_tunai;
			$jumlah['total_eft'] += $total_eft;
			$jumlah['total_tunai'] += $total_tunai;
			$jumlah['total_all'] += $total_all;
			
			$HTML .= '<tr>';
				$HTML .= '<td>'.$program_List[$pItem]['desk_program'].'</td>';
				$HTML .= '<td style="text-align:center">'.($value_eft > 0?$value_eft : 0).'</td>';
				$HTML .= '<td style="text-align:center">'.($value_tunai > 0?$value_tunai : 0).'</td>';
				$HTML .= '<td style="text-align:right">'.number_format(($kadar > 0?$kadar : 0), 2).'</td>';
				$HTML .= '<td style="text-align:right">'.number_format(($total_eft > 0?$total_eft : 0), 2).'</td>';
				$HTML .= '<td style="text-align:right">'.number_format(($total_tunai > 0?$total_tunai : 0), 2).'</td>';
				$HTML .= '<td style="text-align:right">'.number_format(($total_all > 0?$total_all : 0), 2).'</td>';
			$HTML .= '</tr>';
		}
		$HTML .= '<tr style="font-weight:bold">';
			$HTML .= '<td>JUMLAH</td>';
			$HTML .= '<td>'.$jumlah['value_eft'].'</td>';
			$HTML .= '<td>'.$jumlah['value_tunai'].'</td>';
			$HTML .= '<td>-</td>';
			$HTML .= '<td style="text-align:right">'.number_format($jumlah['total_eft'], 2).'</td>';
			$HTML .= '<td style="text-align:right">'.number_format($jumlah['total_tunai'], 2).'</td>';
			$HTML .= '<td></td>';
		$HTML .= '</tr>';
		$HTML .= '<tr style="font-weight:bold">';
			$HTML .= '<td colspan="6">Jumlah Peruntukan '.$item['desk_daerah'].'</td>';
			$HTML .= '<td style="text-align:right">'.number_format($jumlah['total_all'], 2).'</td>';
		$HTML .= '</tr>';
	$HTML .= '</table>';
	$HTML .= '<br>';
	$HTML .= '<br>';

	$pdf->SetFont('helvetica', '', 11); //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
//***********************************************************************************************************************

?>

