<?php
// http://127.0.0.1/hsintungyang-energy/action/schedule/excel_export_energy_saving_rate.php?report_year=2024
/*
	必須執行過daily_energy_increment.php和daily_power_range.php產出每日資料後，此報表程式才會有資料可以撈取
*/
set_time_limit(0);
require_once "../PHPExcel-1.8/Classes/PHPExcel.php";
require_once "../Parameters.php";
require_once "../auth/func.php";
require_once "../class.WebApi.inc";
$time_start = new DateTime();
// 日誌檔案建立
$log_file = './excel_export_report.log';
if (!file_exists($log_file)) 
{
	fopen($log_file, "w");
}

// 報表匯出文件建立
$report_output_path = Excel_Output_Path.'/energy_saving_rate/';
if (!file_exists($report_output_path)) {
	mkdir($report_output_path, 0777, true);
}

$template_path = "../../excel_template/energy_saving_rate.xlsx"; // excel模版

$report_prev_years = 8; // 報表前n年

// 西苑轉民國年
function get_roc_year($_year)
{
	return intval($_year)-1911;
}

try 
{
	// 接收參數
	$last_year = new DateTime();
	$last_year->modify("-1 year");
	$report_year = $last_year->format('Y');
	if(isset($_REQUEST['report_year']))
	{
		$report_year = $_REQUEST['report_year'];
	}
	$year_arr = range($report_year - ($report_prev_years - 1), $report_year);

	// 建立DB連綫 ...
	$pdo = new PDO(sprintf("sqlsrv:Server=%s; Database=%s;", $DataSource, $DataBase_Alarm), $UserID, $Password);
	$api = new WebAPI();
	$api->SetDataBase($DataSource, $DataBase_Trend, $DataBase_Alarm, $UserID, $Password, true);

	$output_filename = "年度能源節約率_{$report_year}.xlsx"; // 匯出excel檔名

	// 載入Excel模板
	$objReader = PHPExcel_IOFactory::createReader('Excel2007');
	$objPHPExcel = $objReader->load($template_path);
	$ews = $objPHPExcel->getSheet(0); // 選取工作表1
	$max_row = $ews->getHighestRow(); // 最大row數

	$start_col = PHPExcel_Cell::columnIndexFromString('C') - 1; // 開始欄位(A=1, B=2 ...)
	foreach ($year_arr as $i_year => $year) // 走訪年份
	{
		$col_str = PHPExcel_Cell::stringFromColumnIndex($start_col + $i_year); // 取欄位字母(0='A', 1='B'...)
		$ews->setCellValue($col_str."2", get_roc_year($year)."年"); // 寫入年份標題

		$date_prm = [
			'date_start' => "{$year}-01-01",
			'date_end' => "{$year}-12-31",
		];
		// 用水量
		$sql = "SELECT ISNULL(SUM([incr_acc]),0) AS [sum_use_val]
				FROM [R_WM_daily]
				WHERE [date_stamp] BETWEEN :date_start AND :date_end";
		$sth = $pdo->prepare($sql);
		$sth->execute($date_prm);
		$wm_use = $sth->fetch(PDO::FETCH_ASSOC)['sum_use_val'];
		$ews->setCellValue($col_str."3", $wm_use);

		// 用電量
		$sql = "SELECT ISNULL(SUM([incr_kwh]),0) AS [sum_use_val]
				FROM [R_PM_daily]
				WHERE [date_stamp] BETWEEN :date_start AND :date_end AND [id] IN (1)";
		$sth = $pdo->prepare($sql);
		$sth->execute($date_prm);
		$pm_use = $sth->fetch(PDO::FETCH_ASSOC)['sum_use_val'];
		$ews->setCellValue($col_str."6", $pm_use);

	}

	$time_end = new DateTime();
	$diff = $time_start->diff($time_end);
	$time_start = new DateTime();
	// echo json_encode($temp_data);exit(1);

	// 匯出excel檔
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save($report_output_path.$output_filename);
	$ok_msg = date("Y-m-d H:i:s").' '.basename(__FILE__).' report_year: '.$report_year.' OK ('.$diff->s."sec)\n";
	echo $ok_msg;
	echo '<br>';
	file_put_contents($log_file, $ok_msg, FILE_APPEND);


}
catch (Exception $e) 
{
	echo $e->getMessage();
	$error_msg = date("Y-m-d H:i:s").' '.basename(__FILE__).' '.$e->getMessage()."\n";
	file_put_contents($log_file, $error_msg, FILE_APPEND);
}

?>