<?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 A.*, B.desk_daerah FROM i_dun A
							LEFT JOIN i_daerah B ON B.id_daerah = A.id_daerah
							WHERE A.id_dun > 1 
							AND A.aktif = 1 
							ORDER BY A.id_daerah, A.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_all = [];
$pemohon_List_daerah = [];

//to get i_kawalan cutoff date
$PDO_stmt = $PDO->prepare("
	SELECT *
	FROM i_kawalan
	WHERE id_program IN (1,2,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_all[$result['id_dun']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		//$pemohon_List_daerah[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		if($tahun_bayar != $current_year)
		{			
			$pemohon_List_all[$result['id_dun']][$result['id_program_ori']][$result['id_carabayar']] += $result['total'];
			$pemohon_List_daerah[$result['id_daerah']][$result['id_program_ori']][$result['id_carabayar']] += $result['total'];
		}
		else
		{
			$pemohon_List_all[$result['id_dun']][$result['id_program']][$result['id_carabayar']] += $result['total'];
			$pemohon_List_daerah[$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_all[$result['id_dun']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		//$pemohon_List_daerah[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		if($tahun_bayar != $current_year)
		{			
			$pemohon_List_all[$result['id_dun']][$result['id_program_ori']][$result['id_carabayar']] += $result['total'];
			$pemohon_List_daerah[$result['id_daerah']][$result['id_program_ori']][$result['id_carabayar']] += $result['total'];
		}
		else
		{
			$pemohon_List_all[$result['id_dun']][$result['id_program']][$result['id_carabayar']] += $result['total'];
			$pemohon_List_daerah[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] += $result['total'];
		}
	}
}

/*
$query = "SELECT i_daerah.id_daerah, i_dun.id_dun, 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_dun, 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_all[$result['id_dun']][$result['id_program']][$result['id_carabayar']] = $result['total'];
	$pemohon_List_daerah[$result['id_daerah']][$result['id_program']][$result['id_carabayar']] += $result['total'];
}
*/

//*********************************************************************************************************
//END (FOR TUNAI)
//*********************************************************************************************************

//***********************************************************************************************************************
// SHOW TABLE
//************************************************************************
$output = '<h4 style="margin-top:10px">STATISTIK PERUNTUKAN BAYARAN TAHUN '.$bayaran_dari.' MENGIKUT KADUN</h4>';
$output .= '<div class="table-responsive">';
$output .= '<table class="table table-striped table-bordered table-hover statTable" style="text-align:center">';
$output .= '<thead>';
	$output .= '<tr>';
		$output .= '<td rowspan=2>BIL.</td>';
		$output .= '<td rowspan=2>DAERAH</td>';
		$output .= '<td rowspan=2>DUN</td>';
		foreach($all_program as $key=>$item)
		{
			$output .= '<td colspan=3>'.$program_List[$item]['desk_program'].'</td>';
		}
	$output .= '</tr>';
	$output .= '<tr>';
		foreach($all_program as $key=>$item)
		{
			$output .= '<td>EFT</td>';
			$output .= '<td>TUNAI</td>';
			$output .= '<td>JUMLAH</td>';
		}
	$output .= '</tr>';
$output .= '</thead>';
$output .= '<tbody>';
$counter = 1;
foreach ($dun_List as $dKey => $dItem)
{
	$output .= '<tr>';
	$output .= '<td>'.$counter.'</td>';
	$output .= '<td>'.$dItem['desk_daerah'].'</td>';
	$output .= '<td>'.$dItem['desk_dun'].'</td>';
	
	foreach($all_program as $key=>$item)
	{
		$value_tunai = $pemohon_List_all[$dKey][$item][1]; //tunai
		$value_eft = $pemohon_List_all[$dKey][$item][2]; //eft
		$value_all = $value_tunai + $value_eft;
		$jumlah_list[$item][1] += $value_tunai;
		$jumlah_list[$item][2] += $value_eft;
		
		if($value_eft > 0)
		{
			$output .= '<td><a href="la_bayaran-cetak_jumlah_pemohon.php?program='.$item.'&dun='.$dItem['id_dun'].'&tahun='.$bayaran_dari.'&carabayar=2" target="_blank">'.$value_eft.'</a></td>';
		}
		else
		{
			$output .= '<td>0</td>';
		}
		
		if($value_tunai > 0)
		{
			$output .= '<td><a href="la_bayaran-cetak_jumlah_pemohon.php?program='.$item.'&dun='.$dItem['id_dun'].'&tahun='.$bayaran_dari.'&carabayar=1" target="_blank">'.$value_tunai.'</a></td>';
		}
		else
		{
			$output .= '<td>0</td>';
		}
		
		if($value_all > 0)
		{
			$output .= '<td><a href="la_bayaran-cetak_jumlah_pemohon.php?program='.$item.'&dun='.$dItem['id_dun'].'&tahun='.$bayaran_dari.'&carabayar=3" target="_blank">'.$value_all.'</a></td>';
		}
		else
		{
			$output .= '<td>0</td>';
		}
	}
	$counter++;
	$output .= '</tr>';
}
//jumlah
$output .= '<tr>';
$output .= '<td></td>';
$output .= '<td></td>';
$output .= '<td>Jumlah</td>';
foreach($all_program as $key=>$item)
{
	$value_tunai = $jumlah_list[$item][1]; //tunai
	$value_eft = $jumlah_list[$item][2]; //eft
	$value_all = $value_tunai + $value_eft;
	
	if($value_eft > 0)
	{
		$output .= '<td><a href="la_bayaran-cetak_jumlah_pemohon.php?program='.$item.'&tahun='.$bayaran_dari.'&carabayar=2" target="_blank">'.$value_eft.'</a></td>';
	}
	else
	{
		$output .= '<td>0</td>';
	}
	
	if($value_tunai > 0)
	{
		$output .= '<td><a href="la_bayaran-cetak_jumlah_pemohon.php?program='.$item.'&tahun='.$bayaran_dari.'&carabayar=1" target="_blank">'.$value_tunai.'</a></td>';
	}
	else
	{
		$output .= '<td>0</td>';
	}
	
	if($value_all > 0)
	{
		$output .= '<td><a href="la_bayaran-cetak_jumlah_pemohon.php?program='.$item.'&tahun='.$bayaran_dari.'&carabayar=3" target="_blank">'.$value_all.'</a></td>';
	}
	else
	{
		$output .= '<td>0</td>';
	}
}
$output .= '</tr>';
$output .= '</tbody>';
$output .= '</table>';
$output .= '</div>';
//***********************************************************************************************************************
//END PART 1
//***********************************************************************************************************************

//***********************************************************************************************************************
//PART 2
//***********************************************************************************************************************
$TITLE_List = [];
$TITLE_List[] = 'STATISTIK PERUNTUKAN BAYARAN TAHUN '.$bayaran_dari.' MENGIKUT DAERAH';
$TITLE_List[] = 'STATISTIK PERUNTUKAN BAYARAN SECARA EFT TAHUN '.$bayaran_dari.' MENGIKUT DAERAH';
$TITLE_List[] = 'STATISTIK PERUNTUKAN BAYARAN SECARA TUNAI TAHUN '.$bayaran_dari.' MENGIKUT DAERAH';

for($num = 0; $num <= 2; $num++)
{
	$TITLE = $TITLE_List[$num];
	$output .= '<h4>'.$TITLE.'</h4>';
	$output .= '<div class="table-responsive">';
	$output .= '<table class="table table-striped table-bordered table-hover statTable" style="text-align:center">';
	$output .= '<thead>';
		$output .= '<tr>';
			$output .= '<td>DAERAH</td>';
			foreach($all_program as $key=>$item)
			{
				$output .= '<td>'.$program_List[$item]['desk_program'].'</td>';
			}
		$output .= '</tr>';
	$output .= '</thead>';
	$output .= '<tbody>';
	$counter = 1;
	$jumlah_list = [];
	foreach ($daerah_List as $dKey => $item)
	{
		$output .= '<tr>';
			$output .= '<td>'.$item['desk_daerah'].'</td>';
			foreach($all_program as $key=>$item)
			{
				if($num == 0)
				{
					$value_all = $pemohon_List_daerah[$dKey][$item][1] + $pemohon_List_daerah[$dKey][$item][2];
				}
				else if($num == 1)//eft
				{
					$value_all = $pemohon_List_daerah[$dKey][$item][2];
				}
				else
				{
					$value_all = $pemohon_List_daerah[$dKey][$item][1];
				}
				$jumlah_list[$item] += $value_all;
				$output .= '<td>'.($value_all > 0?$value_all : 0).'</td>';
			}
			$counter++;
		$output .= '</tr>';
	}
	//jumlah
	
	$output .= '<tr>';
		$output .= '<td>Jumlah</td>';
		foreach($all_program as $key=>$item)
		{
			$value_all = $jumlah_list[$item];
			$output .= '<td>'.($value_all > 0?$value_all : 0).'</td>';
		}
	$output .= '</tr>';
	$output .= '</tbody>';
	$output .= '</table>';
	$output .= '</div>';
}

//***********************************************************************************************************************
//END PART 2
//***********************************************************************************************************************
echo $output;
?>

