<?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,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_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)
//*********************************************************************************************************

//***********************************************************************************************************************
// PRINT EXCEL
//***********************************************************************************************************************

$AUTHOR = 'jj';
$TITLE = 'STATISTIK PENERIMA PEMBAYARAN TAHUN '.$bayaran_dari.' MENGIKUT KADUN';
$FILE_NAME = str_replace(' ', '_', $TITLE);

/** Error reporting */
//error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once 'classes/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator($AUTHOR)
							 ->setLastModifiedBy($AUTHOR)
							 ->setTitle($TITLE)
							 ->setSubject($TITLE)
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml $TITLE")
							 ->setCategory("Test result file");

							 
//set default style
$objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
//$objPHPExcel->getDefaultStyle()->getFont()->setBold(true); //set all bold

// Add data
//One can use the possibility to access cell values by column and row index like (0,1) instead of 'A1' for reading and writing cell values in loops.
//In PHPExcel column index is 0-based while row index is 1-based. That means 'A1' ~ (0,1)
$objPHPExcel->setActiveSheetIndex(0);
$ROW_INDEX = 1; //use this to control row indexing

//***********************************************************************************************************************
//PART 1
//***********************************************************************************************************************
//print table header
$START_COLUMN = 1; //normally start at 0
$END_COLUMN = (count($all_program) * 3) + 2; //based on number of header

$START_COLUMN_ALPHABET = PHPExcel_Cell::stringFromColumnIndex($START_COLUMN); //convert to alphabet
$END_COLUMN_ALPHABET = PHPExcel_Cell::stringFromColumnIndex($END_COLUMN); //convert to alphabet

//set anything before print table
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $ROW_INDEX++, $TITLE);
$ROW_INDEX++; //move to next row
$ROW_INDEX++; //move to next row

$START_ROW = $ROW_INDEX;
$MY_COLUMN = $START_COLUMN + 3;

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $ROW_INDEX, 'BIL.');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $ROW_INDEX, 'DAERAH');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $ROW_INDEX, 'DUN');

foreach($all_program as $key=>$item)
{
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN, $ROW_INDEX, $program_List[$item]['desk_program']);
	$objPHPExcel->getActiveSheet()->mergeCells(PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN).$ROW_INDEX.":".PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN + 2).($ROW_INDEX));
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN, $ROW_INDEX + 1, 'JUMLAH EFT');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN + 1, $ROW_INDEX + 1, 'JUMLAH TUNAI');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN + 2, $ROW_INDEX + 1, 'JUMLAH');
	$MY_COLUMN += 3;
}
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).($ROW_INDEX + 1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('F4D03F');
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).($ROW_INDEX + 1))->getFont()->setBold(true);
$ROW_INDEX++; //move to next row
$ROW_INDEX++; //move to next row

$MY_COLUMN = $START_COLUMN;
$counter = 1;
$jumlah_list = [];
foreach ($dun_List as $dKey => $item)
{
	$MY_COLUMN = $START_COLUMN;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $counter);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item['desk_daerah']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item['desk_dun']);
	
	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;
		
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $value_eft > 0?$value_eft : 0);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN, $ROW_INDEX, $value_tunai > 0?$value_tunai : 0);
		$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN).$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN).($ROW_INDEX))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('E5E7E9');
		$MY_COLUMN++;
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $value_all > 0?$value_all : 0);
	}
	$counter++;
	$ROW_INDEX++;
}
//jumlah
$MY_COLUMN = $START_COLUMN;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, 'Jumlah');
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;
	
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $value_eft > 0?$value_eft : 0);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN, $ROW_INDEX, $value_tunai > 0?$value_tunai : 0);
	$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN).$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN).($ROW_INDEX))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('E5E7E9');
	$MY_COLUMN++;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $value_all > 0?$value_all : 0);
}
$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($START_COLUMN).$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN).($ROW_INDEX))->getFont()->setBold(true);
$ROW_INDEX++;

$END_ROW = $ROW_INDEX - 1;
//set border and alignment for table
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.$END_COLUMN_ALPHABET.$END_ROW)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.$END_COLUMN_ALPHABET.$END_ROW)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//set border and alignment for table
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).$END_ROW)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).$END_ROW)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//***********************************************************************************************************************
//END PART 1
//***********************************************************************************************************************
$ROW_INDEX++;
$ROW_INDEX++;

//***********************************************************************************************************************
//PART 2
//***********************************************************************************************************************
$TITLE_List = [];
$TITLE_List[] = 'PENERIMA KESELURUHAN BELUM DIBAYAR TAHUN '.$bayaran_dari.' MENGIKUT DAERAH';
$TITLE_List[] = 'PENERIMA SECARA EFT BELUM DIBAYAR TAHUN '.$bayaran_dari.' MENGIKUT DAERAH';
$TITLE_List[] = 'PENERIMA SECARA TUNAI BELUM DIBAYAR TAHUN '.$bayaran_dari.' MENGIKUT DAERAH';
for($num = 0; $num <= 2; $num++)
{
	$TITLE = $TITLE_List[$num];
	//print table header
	$START_COLUMN = 1; //normally start at 0
	$END_COLUMN = (count($all_program)) + 1; //based on number of header

	$START_COLUMN_ALPHABET = PHPExcel_Cell::stringFromColumnIndex($START_COLUMN); //convert to alphabet
	$END_COLUMN_ALPHABET = PHPExcel_Cell::stringFromColumnIndex($END_COLUMN); //convert to alphabet

	//set anything before print table
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $ROW_INDEX++, $TITLE);

	$START_ROW = $ROW_INDEX;
	$MY_COLUMN = $START_COLUMN;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, 'DAERAH');
	foreach($all_program as $key=>$item)
	{
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $program_List[$item]['desk_program']);
	}
	$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).($ROW_INDEX))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('F4D03F');
	$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).($ROW_INDEX))->getFont()->setBold(true);
	$ROW_INDEX++; //move to next row

	$MY_COLUMN = $START_COLUMN;
	$counter = 1;
	$jumlah_list = [];
	foreach ($daerah_List as $dKey => $item)
	{
		$MY_COLUMN = $START_COLUMN;
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $item['desk_daerah']);
		
		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;
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $value_all > 0?$value_all : 0);
		}
		$counter++;
		$ROW_INDEX++;
	}
	//jumlah
	$MY_COLUMN = $START_COLUMN;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, 'Jumlah');
	foreach($all_program as $key=>$item)
	{
		$value_all = $jumlah_list[$item];
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($MY_COLUMN++, $ROW_INDEX, $value_all > 0?$value_all : 0);
	}
	$objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($START_COLUMN).$ROW_INDEX.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN).($ROW_INDEX))->getFont()->setBold(true);
	$ROW_INDEX++;

	$END_ROW = $ROW_INDEX - 1;
	//set border and alignment for table
	$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.$END_COLUMN_ALPHABET.$END_ROW)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.$END_COLUMN_ALPHABET.$END_ROW)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	//set border and alignment for table
	$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).$END_ROW)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle($START_COLUMN_ALPHABET.$START_ROW.':'.PHPExcel_Cell::stringFromColumnIndex($MY_COLUMN - 1).$END_ROW)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$ROW_INDEX++;
	$ROW_INDEX++;
}
//***********************************************************************************************************************
//END PART 2
//***********************************************************************************************************************

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Table Data');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

//set auto width

for( $colNum = $START_COLUMN ; $colNum <= $END_COLUMN ; $colNum++ )
{
	$objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($colNum))->setAutoSize(true);
	
}
//$objPHPExcel->getActiveSheet()->getColumnDimension($START_COLUMN_ALPHABET.':'.$END_COLUMN_ALPHABET)->setAutoSize(true);

// Redirect output to a client抯 web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$FILE_NAME.'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
//***********************************************************************************************************************
// END PRINT EXCEL
//***********************************************************************************************************************

?>

