<?php
// http://127.0.0.1/hsintungyang-energy/action/schedule/excel_export_energy_saving_comparison.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_comparison/';
if (!file_exists($report_output_path)) {
	mkdir($report_output_path, 0777, true);
}

$template_path = "../../excel_template/energy_saving_comparison.xlsx"; // excel模版
// 報表設定
$report_cfg = [
	/*
	["item" => "項目", "unit" => "單位", "r_table"=> "過賬資料表名", "r_col_key"=> "過賬用量累計欄位名",  
		"id" => [區域id], "has_cost_cal" => "是否有費用計算"],
	*/
	["item" => "電費", "type" => "電力", "unit" => "kWh", "r_table"=> "R_PM_daily", "r_col_key"=> "incr_kwh", "has_cost_cal" => true, "id" => [1]],
	["item" => "自來水", "type" => "水", "unit" => "m³/hr", "r_table"=> "R_WM_daily", "r_col_key"=> "incr_acc", "has_cost_cal" => true, "id" => [5]],
	["item" => "廢水", "type" => "水", "unit" => "m³/hr", "r_table"=> "R_WM_daily", "r_col_key"=> "incr_acc", "has_cost_cal" => true, "id" => [13,14,15]],
	["item" => "污泥", "type" => "水", "unit" => "m³/hr", "r_table"=> "R_WM_daily", "r_col_key"=> "incr_acc", "has_cost_cal" => true, "id" => []],
	["item" => "冷凍空調", "type" => "電力", "unit" => "kWh", "r_table"=> "R_PM_daily", "r_col_key"=> "incr_kwh", "has_cost_cal" => false, "id" => [17,18,61,55,38,9,68,16,10,65]],
];
// 能源費用（電以外）
$energy_cost = [
	"蒸汽" => 0,
	"瓦斯" => 11.27,
	"水" => 9.24,
];

// 西苑轉民國年
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'];
	}
	$report_year_2 = $report_year-1; // 前一年

	// 建立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數
	// 標題
	$title = get_roc_year($report_year_2)."年與".get_roc_year($report_year)."年節能比較表";
	$ews->setCellValue("A1", $title);
	// 走訪報表設定
	$rno = 3; // 開始row數
	foreach ($report_cfg as $i_cfg => $cfg) 
	{
		$tb = $cfg['r_table'];
		$col = $cfg['r_col_key'];
		$id_in = implode(",", $cfg['id']);
		$date_prm = [
			'date_start' => "{$report_year}-01-01",
			'date_end' => "{$report_year}-12-31",
		];
		$date_prm_2 = [
			'date_start' => "{$report_year_2}-01-01",
			'date_end' => "{$report_year_2}-12-31",
		];
		$r_data = [];
		// 撈取 當年用量
		$sql = "SELECT [id]
					,CONVERT(varchar(7),[date_stamp],120) AS [year_month]
					,SUM([$col]) AS [sum_use_val]
				FROM [$tb]
				WHERE [date_stamp] BETWEEN :date_start AND :date_end AND [id] IN ($id_in)
				GROUP BY [id], CONVERT(varchar(7),[date_stamp],120)
				ORDER BY [id] ASC, CONVERT(varchar(7),[date_stamp],120) ASC";
		$sth = $pdo->prepare($sql);
		$sth->execute($date_prm);
		$r_data[$report_year] = $sth->fetchAll(PDO::FETCH_ASSOC);
		$sth->execute($date_prm_2);
		$r_data[$report_year_2] = $sth->fetchAll(PDO::FETCH_ASSOC);
		// 用電費用(年)
		$pm_data = [];
		if($cfg['has_cost_cal'] && $cfg['type'] == '電力')
		{
			$sql = "SELECT [id]
						,CONVERT(varchar(7),[date_stamp],120) AS [year_month]
						,SUM([cost]) AS [sum_cost]
						,SUM([peak_1]+[peak_2_1]+[peak_2_6]+[peak_3]) as [sum_use_val]
					FROM [PM_range_daily]
					WHERE [date_stamp] BETWEEN :date_start AND :date_end AND [id] IN ($id_in)
					GROUP BY [id], CONVERT(varchar(7),[date_stamp],120)
					ORDER BY [id] ASC, CONVERT(varchar(7),[date_stamp],120) ASC";
			$sth = $pdo->prepare($sql);
			$sth->execute($date_prm);
			$pm_data[$report_year] = $sth->fetchAll(PDO::FETCH_ASSOC);
			$sth->execute($date_prm_2);
			$pm_data[$report_year_2] = $sth->fetchAll(PDO::FETCH_ASSOC);
		}

		$date_arr = [$report_year_2,$report_year]; // 日期順序（由上到下）

		$ews->setCellValue("A{$rno}", $cfg['item']); // 寫入項目
		$dt_row_inx = 0;
		foreach ($date_arr as $date_str) // 年度
		{
			$dt_rno = $rno + $dt_row_inx;
			$ews->setCellValue("B".$dt_rno, get_roc_year($date_str)."年用量"); // 寫入年度用量標題
			$ews->setCellValue("B".($dt_rno+4), get_roc_year($date_str)."年費用"); // 寫入年度費用標題
			$start_col = PHPExcel_Cell::columnIndexFromString('C') - 1; // 開始欄位(A=1, B=2 ...)
			for ($m=1; $m<= 12; $m++) // 每月
			{ 
				$col_str = PHPExcel_Cell::stringFromColumnIndex($start_col + ($m-1)); // 取欄位字母(0='A', 1='B'...)
				// 取出區域id下的用量資料
				$fil_data = array_values(array_filter($r_data[$date_str], function($row) use ($cfg,$m) {
					return in_array($row['id'], $cfg['id']) 
						&& date("n", strtotime($row['year_month'])) == $m;
				}));
				$use_val = array_sum(array_column($fil_data,'sum_use_val'));
				$ews->setCellValue($col_str.$dt_rno, $use_val); // 用量
				// 費用計算
				if($cfg['has_cost_cal'])
				{
					$cost = 0;
					if($cfg['type'] == '電力')
					{
						// 電力費用
						// 取出區域id下的費用資料
						$fil_data_pm = array_values(array_filter($pm_data[$date_str], function($row) use ($cfg,$m) {
							return in_array($row['id'], $cfg['id']) 
								&& date("n", strtotime($row['year_month'])) == $m;
						}));
						$cost = array_sum(array_column($fil_data_pm,'sum_cost'));
					}
					else
					{
						// 其他能源費用
						$cost = $use_val * $energy_cost[$cfg['type']];
					}
					$ews->setCellValue($col_str.($dt_rno+4), $cost); // 費用
				}
			}
			$dt_row_inx ++; // 每個日期+1rows
		}
		// 寫入年度差異標題
		$ews->setCellValue("B".($rno+2), get_roc_year($date_arr[1]).'-'.get_roc_year($date_arr[0])."差異");
		$ews->setCellValue("B".($rno+6), get_roc_year($date_arr[1]).'-'.get_roc_year($date_arr[0])."差異"); 
		if(!$cfg['has_cost_cal'])
		{
			// 隱藏費用row
			for($ri = 0; $ri < 3; $ri++)
			{
				$hide_row_inx = $rno + 4 + $ri;
				$ews->getRowDimension($hide_row_inx)->setVisible(false);
			}
		}
		$rno += 7; // 每個項目+7rows
	}
	// 隱藏row（最後一row不隱藏）
	for($h = $rno; $h <= $max_row-1; $h++)
	{
		$ews->getRowDimension($h)->setVisible(false);
	}

	$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);
}

?>