<?php

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

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

$num_errors = 0;

$dun_sc = fetch_postget_val("dun_sc", $_POST, $_GET);
$daerah_sc = fetch_postget_val("daerah_sc", $_POST, $_GET);
$parlimen_sc = fetch_postget_val("parlimen_sc", $_POST, $_GET);
$program_sc = fetch_postget_val("program_sc", $_POST, $_GET);
$sahmati_sc = fetch_postget_val("sahmati_sc", $_POST, $_GET);
$khairatkat_sc = fetch_postget_val("khairatkat_sc", $_POST, $_GET);
$telahbayar_sc = fetch_postget_val("telahbayar_sc", $_POST, $_GET);
$fasa_sc = fetch_postget_val("fasa_sc", $_POST, $_GET);
$jantina_sc = fetch_postget_val("jantina_sc", $_POST, $_GET);
$blnthnDari_month = fetch_postget_val("blnthnDari_month", $_POST, $_GET);
$blnthnDari_year = fetch_postget_val("blnthnDari_year", $_POST, $_GET);
$blnthnHingga_month = fetch_postget_val("blnthnHingga_month", $_POST, $_GET);
$blnthnHingga_year = fetch_postget_val("blnthnHingga_year", $_POST, $_GET);

try
{
	//PDO INIT
	$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	$statistikResult = array();
	//*****************************************
	//GET FOR OTHER PROGRAM
	//*****************************************
	
	$tkh_fetch = 'tkh_keyinmati';
	if($telahbayar_sc == 2)
	{
		$tkh_fetch = 'tkh_bayar';
	}
	
	$Query = "SELECT m_pemohon.tkh_keyinmati, m_pemohon.id_dun, m_pemohon.id_program, m_pemohon.id_bangsa, m_bayaran_KH.tkh_bayar
			FROM m_pemohon
			LEFT JOIN i_dun ON m_pemohon.id_dun = i_dun.id_dun
			LEFT JOIN m_bayaran_KH ON m_bayaran_KH.id_pemohon = m_pemohon.id_pemohon
			WHERE $tkh_fetch between ? AND ?
			AND id_statushidup = 2
			AND id_program IN (1,2,4)";
			
	if($daerah_sc > 0)
	{
		$Query .= " AND i_dun.id_daerah = ".$daerah_sc;
	}
	
	if($parlimen_sc > 0)
	{
		$Query .= " AND i_dun.id_parlimen = ".$parlimen_sc;
	}
	
	if($sahmati_sc > 0)
	{
		$Query .= " AND id_sahmati = ".$sahmati_sc;
	}
	
	if($khairatkat_sc > 0)
	{
		$Query .= " AND id_khairatkat = ".$khairatkat_sc;
	}
		
	if($program_sc > 0)
	{
		$Query .= " AND m_pemohon.id_program = ".$program_sc;
	}
	
	if($telahbayar_sc == 1)
	{
		$Query .= " AND flag_bayarkhairat = 0";
	}
	else if($telahbayar_sc == 2)
	{
		$Query .= " AND flag_bayarkhairat = 1";
	}
	
	if($jantina_sc > 0)
	{
		$Query .= " AND m_pemohon.id_jantina = ".$jantina_sc;
	}
	
	if($fasa_sc > 0)
	{
		$Query .= " AND id_bayaranfasa = ".$fasa_sc;
	}
	
	$PDO_stmt = $PDO->prepare($Query);
	$PDO_stmt->bindValue(1, $blnthnDari_year.'-'.$blnthnDari_month.'-01 00:00:00', PDO::PARAM_STR);
	$PDO_stmt->bindValue(2, $blnthnHingga_year.'-'.$blnthnHingga_month.'-31 23:59:59', PDO::PARAM_STR);
	$PDO_stmt->execute();
	while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC))
	{
		if(!in_array($result['id_bangsa'], array(1,2,3)))
		{
			$result['id_bangsa'] = 5; //if not 1,2,3 , all go to 5 (lain-lain)
		}
		$statistikResult[formatDate($result[$tkh_fetch], 'Y')][$result['id_dun']][$result['id_bangsa']] += 1;
	}
	
	$TITLE = "Statistik Permohonan Khiarat";
	
	$dun_List = array();
	$PDO_stmt = $PDO->prepare("
		SELECT A.id_dun AS id, CONCAT(A.kadun, ' - ',A.desk_dun) as name, A.id_daerah, B.desk_daerah
		FROM i_dun A
		LEFT JOIN i_daerah B ON B.id_daerah = A.id_daerah
		WHERE A.aktif = 1
		ORDER BY A.id_daerah, A.susunan
	");
	$PDO_stmt->execute();
	while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC)){
		if(($dun_sc == 0 || $result['id'] == $dun_sc) && ($daerah_sc == 0 || $result['id_daerah'] == $daerah_sc))
		{
			$dun_List[] = array($result['id'], $result['name'], $result['desk_daerah']);
		}
	}
	
	$program_name = 'SEMUA PROGRAM';
	
	if($program_sc > 0)
	{
		$program_List = array();
		$PDO_stmt = $PDO->prepare("
			SELECT id_program AS id, desk_program as name
			FROM i_program
			WHERE id_program = $program_sc
		");
		$PDO_stmt->execute();
		while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC)){
			$program_name = $result['name'];
		}
	}
	
	$bangsa_List = array();
	$PDO_stmt = $PDO->prepare("
		SELECT id_bangsa AS id, desk_bangsa as name
		FROM i_bangsa
		WHERE aktif = 1
		AND id_bangsa IN (1,2,3,5)
		ORDER BY susunan
	");
	$PDO_stmt->execute();
	while($result = $PDO_stmt->fetch(PDO::FETCH_ASSOC)){
		$bangsa_List[] = array($result['id'], $result['name']);
	}

	$bulan_List = array();
	for($num = 1; $num <= 12; $num++)
	{
		if(in_array($num, $allBulan))
		{
			$bulan_List[] = array($num, monthToMalay($num));
		}
	}
	
	$combine_value = array();
	$resultArray = array();
	ksort($statistikResult);
	for($year = $blnthnDari_year; $year <= $blnthnHingga_year; $year++)
	//foreach ($statistikResult as $year => $obj)
	{
		$name = 'statTable_'.$year;
		$title = 'Maklumat '.$TITLE.' '.$program_name.' Tahun '.$year;
		
		$resultArray['data'] .= '<div class="card">';
		$resultArray['data'] .= '<div class="card-header">';
		$resultArray['data'] .= '<div class="card-title" data-toggle="collapse" data-target="#'.$name.'_div">';
		$resultArray['data'] .= '<i class="flaticon2-layers-1"></i>'.$title;
		$resultArray['data'] .= '</div>';
		$resultArray['data'] .= '</div>';
		$resultArray['data'] .= '<div id="'.$name.'_div" class="show" data-parent="#accordionExample1">';
		$resultArray['data'] .= '<div class="card-body">';
		$resultArray['data'] .= '<label id="'.$name.'_title">'.$title.'</label>';
		$resultArray['data'] .= '<div class="table-responsive">';
		$resultArray['data'] .= '<table class="table table-striped table-bordered table-hover statTable" style="text-align:center" id="'.$name.'" name="'.$name.'">';
		$resultArray['data'] .= '<thead>';
		$resultArray['data'] .= '<tr>';
		$resultArray['data'] .= '<td rowspan=2>BIL.</td>';
		$resultArray['data'] .= '<td rowspan=2>DAERAH</td>';
		$resultArray['data'] .= '<td rowspan=2>DUN</td>';
		$resultArray['data'] .= '<td colspan='.(count($bangsa_List) + 1).'>Bangsa</td>';
		$resultArray['data'] .= '</tr>';
		
		$resultArray['data'] .= '<tr>';
		foreach ($bangsa_List as $item2)
		{
			$resultArray['data'] .= '<td>'.$item2[1].'</td>';
		}
		$resultArray['data'] .= '<td>Jumlah</td>';
		$resultArray['data'] .= '</tr>';
		
		$resultArray['data'] .= '</thead>';
		$resultArray['data'] .= '<tbody>';
		$counter = 1;
		$jumlah = array();
		
		foreach ($dun_List as $item)
		{
			$resultArray['data'] .= '<tr>';
			$resultArray['data'] .= '<td>'.$counter++.'.</td>';
			$resultArray['data'] .= '<td>'.$item[2].'</td>';
			$resultArray['data'] .= '<td>'.$item[1].'</td>';
			$jumlah_row = 0;
			$statData = array();
			$statData['dun'] = $item[1];
			foreach ($bangsa_List as $item2)
			{
				
				$value = (($statistikResult[$year][$item[0]][$item2[0]])?($statistikResult[$year][$item[0]][$item2[0]]):0);
				$jumlah[$year][$item2[0]] += $value;
				$resultArray['data'] .= '<td>'.$value.'</td>';
				$statData['data_'.$item2[0]] = $value;
				$jumlah_row += $value;
				$combine_value[$item[0]][$item2[0]] += $value;
				
				//$resultArray['statData'][] = $statData;
				//$statData['dun'] = $item2[1]." - ".$item[1];
			}
				$resultArray[$year]['statData'][] = $statData;
			$resultArray['data'] .= '<td>'.$jumlah_row.'</td>';
			$resultArray['data'] .= '</tr>';
			//$resultArray['statData'][] = array('dun' => '');
			$resultArray[$year]['statData'][] = array('dun' => '');
		}
		
		$resultArray['data'] .= '<tr style="font-weight:bold">';
		$resultArray['data'] .= '<td></td>';
		$resultArray['data'] .= '<td></td>';
		$resultArray['data'] .= '<td>Jumlah</td>';
		$jumlahBesar = array();
		foreach ($bangsa_List as $item2)
		{
			$senarai_nama_href = '<a href="javascript:;" onclick="cetakSenaraiNama('.$year.', '.$item2[0].')">'.$jumlah[$year][$item2[0]].'</a>';
			$resultArray['data'] .= '<td>'.$senarai_nama_href.'</td>';
			//$resultArray['data'] .= '<td>'.$jumlah[$year][$item2[0]].'</td>';
			$jumlahBesar[$year] += $jumlah[$year][$item2[0]];
		}
		$senarai_nama_href = '<a href="javascript:;" onclick="cetakSenaraiNama('.$year.', 0)">'.$jumlahBesar[$year].'</a>';
		$resultArray['data'] .= '<td>'.$senarai_nama_href.'</td>';
		//$resultArray['data'] .= '<td>'.$jumlahBesar[$year].'</td>';
		$resultArray['data'] .= '</tr>';
		
		//JUMLAH RM HERE
		$resultArray['data'] .= '<tr style="font-weight:bold">';
		$resultArray['data'] .= '<td></td>';
		$resultArray['data'] .= '<td></td>';
		$resultArray['data'] .= '<td>Jumlah(RM)</td>';
		$jumlahBesar = array();
		foreach ($bangsa_List as $item2)
		{
			$jumlah_rm = number_format($jumlah[$year][$item2[0]] * 1000, 2).'</a>';
			$resultArray['data'] .= '<td style="text-align:right">RM '.$jumlah_rm.'</td>';
			//$resultArray['data'] .= '<td>'.$jumlah[$year][$item2[0]].'</td>';
			$jumlahBesar[$year] += $jumlah[$year][$item2[0]];
		}
		$resultArray['data'] .= '<td>RM '.number_format($jumlahBesar[$year] * 1000, 2).'</td>';
		$resultArray['data'] .= '</tr>';
		
		$resultArray['data'] .= '<tfoot>';
		$resultArray['data'] .= '</tfoot>';
		$resultArray['data'] .= '</tbody>';
		$resultArray['data'] .= '</table>';
		$resultArray['data'] .= '</div>';
		$resultArray['data'] .= '<br>';
		$resultArray['data'] .= '<div id="chartDiv_'.$year.'" style="height:600px;width:100%;font-size:11px;display:none"></div>';
		$resultArray['data'] .= '</div>';
		$resultArray['data'] .= '</div>';
		$resultArray['data'] .= '</div>';
		
		$resultArray[$year]['title'][] = array("text" => $title, "size" => 15);
		
		foreach ($bangsa_List as $item2)
		{
			$graphSetting = array();
			$graphSetting["balloonText"] = "<b>[[title]]</b><br><span style='font-size:14px'>[[category]]: <b>[[value]]</b></span>";
			$graphSetting["fillAlphas"] = 0.8;
			$graphSetting["labelText"] = "[[value]]";
			$graphSetting["lineAlpha"] = 0.3;
			$graphSetting["title"] = $item2[1];
			$graphSetting["type"] = "column";
			$graphSetting["color"] = "#000000";
			$graphSetting["valueField"] = 'data_'.$item2[0];
			$resultArray[$year]['graphSetting'][] = $graphSetting;
		}
		
	}
	
	//jumlah table
	$name = 'statTable_jumlah';
	$title = 'Jumlah Maklumat '.$TITLE.' '.$program_name.' '.$blnthnDari_month.'/'.$blnthnDari_year.' - '.$blnthnHingga_month.'/'.$blnthnHingga_year;
	
	$resultArray['jumlah_data'] .= '<div class="card">';
	$resultArray['jumlah_data'] .= '<div class="card-header">';
	$resultArray['jumlah_data'] .= '<div class="card-title" data-toggle="collapse" data-target="#'.$name.'_div">';
	$resultArray['jumlah_data'] .= '<i class="flaticon2-layers-1"></i>'.$title;
	$resultArray['jumlah_data'] .= '</div>';
	$resultArray['jumlah_data'] .= '</div>';
	$resultArray['jumlah_data'] .= '<div id="'.$name.'_div" class="show" data-parent="#accordionExample1">';
	$resultArray['jumlah_data'] .= '<div class="card-body">';
	$resultArray['jumlah_data'] .= '<label id="'.$name.'_title">'.$title.'</label>';
	$resultArray['jumlah_data'] .= '<div class="table-responsive"';
	$resultArray['jumlah_data'] .= '<table class="table table-striped table-bordered table-hover statTable" style="text-align:center" id="'.$name.'" name="'.$name.'">';
	$resultArray['jumlah_data'] .= '<thead>';
	$resultArray['jumlah_data'] .= '<tr>';
	$resultArray['jumlah_data'] .= '<td rowspan=2>BIL.</td>';
	$resultArray['jumlah_data'] .= '<td rowspan=2>DAERAH</td>';
	$resultArray['jumlah_data'] .= '<td rowspan=2>DUN</td>';
	$resultArray['jumlah_data'] .= '<td colspan='.(count($bangsa_List) + 1).'>Program</td>';
	$resultArray['jumlah_data'] .= '</tr>';
	
	$resultArray['jumlah_data'] .= '<tr>';
	foreach ($bangsa_List as $item2)
	{
		$resultArray['jumlah_data'] .= '<td>'.$item2[1].'</td>';
	}
	$resultArray['jumlah_data'] .= '<td>Jumlah</td>';
	$resultArray['jumlah_data'] .= '</tr>';
	
	$resultArray['jumlah_data'] .= '</thead>';
	$resultArray['jumlah_data'] .= '<tbody>';
	$counter = 1;
	$jumlah = array();
	
	foreach ($dun_List as $item)
	{
		$resultArray['jumlah_data'] .= '<tr>';
		$resultArray['jumlah_data'] .= '<td>'.$counter++.'.</td>';
		$resultArray['jumlah_data'] .= '<td>'.$item[2].'</td>';
		$resultArray['jumlah_data'] .= '<td>'.$item[1].'</td>';
		$jumlah_row = 0;
		$statData = array();
		$statData['dun'] = $item[1];
		foreach ($bangsa_List as $item2)
		{
			
			$value = (($combine_value[$item[0]][$item2[0]])?($combine_value[$item[0]][$item2[0]]):0);
			$resultArray['jumlah_data'] .= '<td>'.$combine_value[$item[0]][$item2[0]].'</td>';
			$statData['data_'.$item2[0]] = $value;
			$jumlah[$item2[0]] += $value;
			$jumlah_row += $value;
			
		}
			$resultArray['statData'][] = $statData;
		$resultArray['jumlah_data'] .= '<td>'.$jumlah_row.'</td>';
		$resultArray['jumlah_data'] .= '</tr>';
		$resultArray['statData'][] = array('dun' => '');
	}
	$resultArray['jumlah_data'] .= '<tr style="font-weight:bold">';
	$resultArray['jumlah_data'] .= '<td></td>';
	$resultArray['jumlah_data'] .= '<td></td>';
	$resultArray['jumlah_data'] .= '<td>Jumlah</td>';
	$jumlahBesar = 0;
	foreach ($bangsa_List as $item2)
	{
		$senarai_nama_href = '<a href="javascript:;" onclick="cetakSenaraiNama(0, '.$item2[0].')">'.$jumlah[$item2[0]].'</a>';
		$resultArray['jumlah_data'] .= '<td>'.$senarai_nama_href.'</td>';
		//$resultArray['jumlah_data'] .= '<td>'.$jumlah[$item2[0]].'</td>';
		$jumlahBesar += $jumlah[$item2[0]];
	}
	$senarai_nama_href = '<a href="javascript:;" onclick="cetakSenaraiNama(0, 0)">'.$jumlahBesar.'</a>';
	$resultArray['jumlah_data'] .= '<td>'.$senarai_nama_href.'</td>';
	//$resultArray['jumlah_data'] .= '<td>'.$jumlahBesar.'</td>';
	$resultArray['jumlah_data'] .= '</tr>';
	
	$resultArray['jumlah_data'] .= '<tfoot>';
	$resultArray['jumlah_data'] .= '</tfoot>';
	$resultArray['jumlah_data'] .= '</tbody>';
	$resultArray['jumlah_data'] .= '</table>';
	$resultArray['jumlah_data'] .= '</div>';
	$resultArray['jumlah_data'] .= '<br>';
	$resultArray['jumlah_data'] .= '<div id="chartDiv_jumlah" style="height:600px;width:100%;font-size:11px;display:none"></div>';
	$resultArray['jumlah_data'] .= '</div>';
	$resultArray['jumlah_data'] .= '</div>';
	$resultArray['jumlah_data'] .= '</div>';
	
	foreach ($bangsa_List as $item2)
	{
		$graphSetting = array();
		$graphSetting["balloonText"] = "<b>[[title]]</b><br><span style='font-size:14px'>[[category]]: <b>[[value]]</b></span>";
		$graphSetting["fillAlphas"] = 0.8;
		$graphSetting["labelText"] = "[[value]]";
		$graphSetting["lineAlpha"] = 0.3;
		$graphSetting["title"] = $item2[1];
		$graphSetting["type"] = "column";
		$graphSetting["color"] = "#000000";
		$graphSetting["valueField"] = 'data_'.$item2[0];
		$resultArray['graphSetting'][] = $graphSetting;
	}
	$resultArray['catField'] = "dun";
	
	$resultArray['title'][] = array("text" => $title, "size" => 15);
	
	echo json_encode($resultArray);
}
catch (Exception $e) 
{
	$PDO->rollBack();
	echo "<label>Failed: " . $e->getMessage()."</label>";
}


?>