<?php

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

//Header("Content-type: application/xml");
Header("Cache-Control: no-cache");

$num_errors = 0;

$id_carabayar = fetch_postget_val("id_carabayar", $_POST, $_GET);
$id_sah = fetch_postget_val("id_sah", $_POST, $_GET);
$id_daerah = fetch_postget_val("id_daerah", $_POST, $_GET);
$tkh_mula = formatDate(fetch_postget_val("tkh_mula", $_POST, $_GET), 'Y-m-d');
$tkh_hingga = formatDate(fetch_postget_val("tkh_hingga", $_POST, $_GET), 'Y-m-d');
$tahun_bayar_year = fetch_postget_val("tahun_bayar_year", $_POST, $_GET);
$bayaranfasa_sc = fetch_postget_val("bayaranfasa_sc", $_POST, $_GET);
$dun_sc = fetch_postget_val("dun_sc", $_POST, $_GET);

$PDO_stmt = $PDO->prepare("
	SELECT *
	FROM i_program
	WHERE id_program IN (1,2,3,4,5,7)
	AND aktif = 1
");
$PDO_stmt->execute();
$program_List = [];
while($res = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
{
	$program_List[$res['id_program']] = $res;
}

if($id_daerah == 0)
{
	$PDO_stmt = $PDO->prepare("
		SELECT *
		FROM i_dun
	");
	$PDO_stmt->execute();
	$dun_List = [];
	while($res = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
	{
		$dun_List[$res['id_dun']] = $res;
	}
}
else
{
	if($dun_sc > 0)
	{
		$PDO_stmt = $PDO->prepare("
			SELECT *
			FROM i_dun
			WHERE id_daerah = ? AND id_dun = ?
		");
		$PDO_stmt->bindValue(1, $id_daerah, PDO::PARAM_STR);
		$PDO_stmt->bindValue(2, $dun_sc, PDO::PARAM_STR);
		$PDO_stmt->execute();
		$dun_List = [];
		while($res = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
		{
			$dun_List[$res['id_dun']] = $res;
		}		
	}
	else
	{
		$PDO_stmt = $PDO->prepare("
			SELECT *
			FROM i_dun
			WHERE id_daerah = ?
		");
		$PDO_stmt->bindValue(1, $id_daerah, PDO::PARAM_STR);
		$PDO_stmt->execute();
		$dun_List = [];
		while($res = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
		{
			$dun_List[$res['id_dun']] = $res;
		}
	}
}

$bgColorAry = [];
$bgColorAry[] = 'warning';
$bgColorAry[] = 'danger';
$bgColorAry[] = 'success';
$bgColorAry[] = 'primary';
$bgColorAry[] = 'info';
$counter = 0;
$pemohon_All = [];
$pemohon_Kadun = [];

if($id_carabayar == 1)
{
	$carabayar_query = ' AND id_carabayar = 1 ';		
}
else if($id_carabayar == 2)
{
	$carabayar_query = ' AND id_carabayar = 2 ';		
}
else
{		
	$carabayar_query = '';
}

if($id_sah > 0)
{
	$pengesah_query = ' AND id_sah = '.$id_sah.' ';		
}
else
{		
	$pengesah_query = '';
}

if($id_daerah > 0)
{
	$daerah_query = ' AND id_daerah = '.$id_daerah.' ';		
}
else
{		
	$daerah_query = '';
}

if($tahun_bayar_year > 0)
{
	$tahun_bayar_query = ' AND tahun_bayar = '.$tahun_bayar_year.' ';		
}
else
{		
	$tahun_bayar_query = '';
}

if($bayaranfasa_sc > 0)
{
	$bayaranfasa_query = ' AND id_bayaranfasa = '.$bayaranfasa_sc.' ';		
}
else
{		
	$bayaranfasa_query = '';
}

if($dun_sc > 0)
{
	$dun_query = ' AND id_dun = '.$dun_sc.' ';		
}
else
{		
	$dun_query = '';
}
	
foreach ($program_List as $key => $item)
{
	
	
	$mbayar_table = "m_bayaran_".$item['kod_program'];
	$PDO_stmt = $PDO->prepare("
			SELECT id_dun, count(*) AS total
				FROM $mbayar_table
				WHERE tkh_bayar BETWEEN ? AND ? 
				".$daerah_query.$carabayar_query.$pengesah_query.$tahun_bayar_query.$bayaranfasa_query.$dun_query."
			GROUP BY id_dun
		");
	/*
	if($item['id_program'] == 5) //anak emas
	{
		$PDO_stmt = $PDO->prepare("
			SELECT A.id_dun, count(*) AS total
				FROM $mbayar_table B
				WHERE B.tkh_bayar BETWEEN ? AND ?
				AND i_daerah.id_daerah = ?
			GROUP BY id_dun
		");
	}
	else
	{
		$PDO_stmt = $PDO->prepare("
			SELECT A.id_dun, count(*) AS total
			FROM m_pemohon A
			LEFT JOIN i_dun ON A.id_dun = i_dun.id_dun 
			LEFT JOIN i_daerah ON i_dun.id_daerah = i_daerah.id_daerah 
			JOIN 
			(
				SELECT id_pemohon 
				FROM $mbayar_table B
				WHERE B.tkh_bayar BETWEEN ? AND ?
			) C ON A.id_pemohon = C.id_pemohon
			WHERE i_daerah.id_daerah = ?
			GROUP BY id_dun
		");
	}
	*/
	$PDO_stmt->bindValue(1, $tkh_mula.' 00:00:00', PDO::PARAM_STR);
	$PDO_stmt->bindValue(2, $tkh_hingga.' 23:59:59', PDO::PARAM_STR);
	//$PDO_stmt->bindValue(3, $id_daerah, PDO::PARAM_STR);
	$PDO_stmt->execute();
	while($res = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
	{
		$pemohon_All[$item['id_program']] += $res['total'];
		$pemohon_Kadun[$item['id_program']][$res['id_dun']] += $res['total'];
	}
	$functionCall = "showPemohon({$item['id_program']}, {$id_carabayar}, {$id_daerah}, '{$tkh_mula}', '{$tkh_hingga}', '{$id_sah}', '{$tahun_bayar_year}', '{$bayaranfasa_sc}', '{$dun_sc}')";
	echo '<div class="d-flex align-items-center mb-9 bg-light-'.$bgColorAry[$counter].' rounded p-5">';
		echo '<span class="svg-icon svg-icon-'.$bgColorAry[$counter].' mr-5">';
			echo '<span class="svg-icon svg-icon-lg">';
				echo $book_svg;
			echo '</span>';
		echo '</span>';
		echo '<div class="d-flex flex-column flex-grow-1 mr-2">';
			echo '<a href="#" class="font-weight-bold text-dark-75 text-hover-primary font-size-lg mb-1">Program '.$item['desk_program'].'</a>';
			echo '<span class="text-muted font-weight-bold">';
			if($pemohon_All[$item['id_program']]>0)
			{
				echo '<a href="javascript:;" onclick="'.$functionCall.'" target="_blank">Senarai Penerima</a>';
			}
			else
			{
				echo 'Senarai Penerima';
			}
			echo '</span>';
		echo '</div>';
		echo '<span class="font-weight-bolder text-'.$bgColorAry[$counter].' py-1 font-size-lg">'.($pemohon_All[$item['id_program']]>0?$pemohon_All[$item['id_program']]:0).'</span>';
	echo '</div>';
	$counter++;
}

$name = "statTable_all";
$title = "Laporan Pembayaran Harian Program Kebajikan Mengikut KADUN";

echo '<h4><label id="'.$name.'_title">'.$title.'</label></h4>';
echo '<div class="table-responsive">';
echo '<table class="table table-striped table-bordered table-hover statTable" id="'.$name.'" name="'.$name.'" style="text-align:center">';
	echo '<thead>';
		echo '<tr>';
			echo '<td>DUN / PROGRAM</td>';
			foreach($program_List as $key=>$item)
			{
				echo '<td>'.$item['desk_program'].'</td>';
			}
		echo '</tr>';
	echo '</thead>';
	echo '<tbody>';
		foreach ($dun_List as $d_key => $d_item)
		{
			echo '<tr>';
				echo '<td>'.$d_item['desk_dun'].'</td>';
				foreach($program_List as $key=>$item)
				{
					echo '<td>'.($pemohon_Kadun[$item['id_program']][$d_item['id_dun']]>0?$pemohon_Kadun[$item['id_program']][$d_item['id_dun']]:0).'</td>';
				}
			echo '</tr>';
		}
		echo '<tr style="font-weight:bold">';
			echo '<td>Jumlah Keseluruhan</td>';
			foreach($program_List as $key=>$item)
			{
				echo '<td>'.($pemohon_All[$item['id_program']]>0?$pemohon_All[$item['id_program']]:0).'</td>';
			}
		echo '</tr>';
	echo '</tbody>';
	echo '</table>';
echo '</div>';

?>