<?php
// http://127.0.0.1/hsintungyang-energy/action/schedule/excel_export_report_year.php?report_date=2024
/*
	必須執行過daily_energy_increment.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.'/yearly/';
if (!file_exists($report_output_path)) {
	mkdir($report_output_path, 0777, true);
}

// $template_path = "../../excel_template/report_year.xlsx"; // excel模版
$excel_type = [
	["name"=> "用量", "template_path"=> "../../excel_template/report_year_used.xlsx"],
	["name"=> "碳排量", "template_path"=> "../../excel_template/report_year_co2e.xlsx"],
];
// 報表設定
$report_cfg = [
	/*
	["type" => "能源種類", "unit" => "單位", "r_table"=> "過賬資料表名", "r_col_key"=> "過賬用量累計欄位名", 
		"ep_item_key" => "能源參數key", 
		"area" => [
			["name" => "區域名稱", "id" => [區域id]]
		]
	],
	*/
	["type" => "電力", "unit" => "kWh", "r_table"=> "R_PM_daily", "r_col_key"=> "incr_kwh", "ep_item_key" => "PM1",
		"area" => [
			["name" => "MCB", "id" => [1]],
			["name" => "汙水", "id" => [2]],
			["name" => "空調主機#1", "id" => [3]],
			["name" => "空調主機#2", "id" => [4]],
			["name" => "空調主機#3", "id" => [5]],
			["name" => "空調主機#4", "id" => [6]],
			["name" => "空壓機#1", "id" => [7]],
			["name" => "空壓機#2", "id" => [8]],
			["name" => "2°C冷凍主機(包裝冷凍庫)", "id" => [9]],
			["name" => "A倉冷凍庫總用電(A倉機房)", "id" => [10]],
			["name" => "冷凍主機#1", "id" => [11]],
			["name" => "冷凍主機#2", "id" => [12]],
			["name" => "冷凍主機#3", "id" => [13]],
			["name" => "2EL(二樓緊急電源-中控室)", "id" => [14]],
			["name" => "1ELA(一樓緊急電源)", "id" => [15]],
			["name" => "MCC", "id" => [16]],
			["name" => "BIP(空調專用-地下室冰水泵)", "id" => [17]],
			["name" => "蒸發冷凝器", "id" => [18]],
			["name" => "1LE(罐頭加工區照明)", "id" => [19]],
			["name" => "1LD(烘烤區照明)", "id" => [20]],
			["name" => "1LC(蒸煮區照明)", "id" => [21]],
			["name" => "1LB(209月台照明)", "id" => [22]],
			["name" => "1LB(西式調理區照明)", "id" => [23]],
			["name" => "2LA(2樓辦公區照明)", "id" => [24]],
			["name" => "RAC2(蒸煮區抽風機)", "id" => [25]],
			["name" => "A倉", "id" => [26]],
			["name" => "B倉", "id" => [27]],
			["name" => "RAC6(烘烤區排風機)", "id" => [28]],
			["name" => "RAC5(肉鬆包裝區)", "id" => [29]],
			["name" => "2LC(2樓左員工活動中心)", "id" => [30]],
			["name" => "2LB(2樓右餐廳照明)", "id" => [31]],
			["name" => "1LF(肉品包裝,中西照明)", "id" => [32]],
			["name" => "1LG(物資倉庫照明)", "id" => [33]],
			["name" => "1LH(糕餅中式整型區照明)", "id" => [34]],
			["name" => "1LI(1樓照明)", "id" => [35]],
			["name" => "ＭＰ(滷味廠)", "id" => [36]],
			["name" => "空壓", "id" => [37]],
			["name" => "EPA(一期冷凍庫控制盤)", "id" => [38]],
			["name" => "PL1(工務周邊)", "id" => [39]],
			["name" => "EBP(水處理專用)", "id" => [40]],
			["name" => "BP(鍋爐機房)", "id" => [41]],
			["name" => "宿舍", "id" => [42]],
			["name" => "醬油槽區", "id" => [43]],
			["name" => "1PC(蒸煮區)", "id" => [44]],
			["name" => "1PB(切割區)", "id" => [45]],
			["name" => "1PA(大調理區)", "id" => [46]],
			["name" => "脆片包裝作業區", "id" => [47]],
			["name" => "1PM(糕餅旋轉烤爐)", "id" => [48]],
			["name" => "1PL(糕餅走道)", "id" => [49]],
			["name" => "1PK(糕餅瓦斯隧道爐)", "id" => [50]],
			["name" => "BLP(大地下室)", "id" => [51]],
			["name" => "1PE-1(金瑛發煮鍋.吊車)", "id" => [52]],
			["name" => "1PF(香腸包裝區)", "id" => [53]],
			["name" => "1PG(罐頭加工區.殺菌釜)", "id" => [54]],
			["name" => "1PI(糕餅冷凍庫)", "id" => [55]],
			["name" => "RAC7(糕餅頂層排風機)", "id" => [56]],
			["name" => "1PD(烘烤區)", "id" => [57]],
			["name" => "1PE(罐頭封罐機.遠紅外線)", "id" => [58]],
			["name" => "1PJ(糕餅調理室)", "id" => [59]],
			["name" => "ATS(緊急電源)", "id" => [60]],
			["name" => "GRAM主機-頂樓(肉乾胚冷凍庫)", "id" => [61]],
			["name" => "ＥＰＢ9烘烤室3Ø220V電源)", "id" => [62]],
			["name" => "1PA(A站110V插座)", "id" => [63]],
			["name" => "RP2(B站110V插座)", "id" => [64]],
			["name" => "空調冷藏系統-ACMP", "id" => [65]],
			["name" => "空調主機-RFMPP", "id" => [66]],
			["name" => "大冷藏庫RFMPB", "id" => [67]],
			["name" => "物資冷凍庫-RFMPC", "id" => [68]],
		]
	],
	["type" => "蒸汽", "unit" => "kg/hr", "r_table"=> "R_SM_daily", "r_col_key"=> "incr_acc", "ep_item_key" => "SM1",
		"area" => [
			["name" => "蒸氣總管線", "id" => [1]],
			["name" => "罐頭煮釜", "id" => [2]],
			["name" => "二重鍋", "id" => [3]],
			["name" => "四方煮鍋", "id" => [4]],
			["name" => "肉鬆炒鍋", "id" => [5]],
			["name" => "遠紅外線", "id" => [6]],
			["name" => "殺菌釜NO:1", "id" => [7]],
			["name" => "殺菌釜NO:2", "id" => [8]],
			["name" => "殺菌釜NO:3", "id" => [9]],
			["name" => "殺菌釜NO:4", "id" => [10]],
			["name" => "Vemag隧道燻煙爐", "id" => [11]],
			["name" => "Fessmann多功能爐", "id" => [12]],
			["name" => "Fessmann多功能爐", "id" => [13]],
			["name" => "宣柏蒸煮爐", "id" => [14]],
			["name" => "KOCH乾燥爐", "id" => [15]],
			["name" => "蒸煮鍋", "id" => [16]],
			["name" => "熱水槽", "id" => [17]],
			["name" => "殺菌槽", "id" => [18]],
			["name" => "炒餡鍋", "id" => [19]],
		]
	],
	["type" => "瓦斯", "unit" => "m³/hr", "r_table"=> "R_GM_daily", "r_col_key"=> "incr_acc", "ep_item_key" => "GM1",
		"area" => [
			["name" => "瓦斯LPG總管", "id" => [1]],
			["name" => "炒肉鬆機", "id" => [2]],
			["name" => "肉乾隧道烤爐", "id" => [3]],
			["name" => "肉乾乾燥爐NO.1", "id" => [4]],
			["name" => "肉乾乾燥爐NO.2", "id" => [5]],
			["name" => "炒牛肉", "id" => [6]],
			["name" => "瓦斯隧道爐(舊)", "id" => [7]],
			["name" => "瓦斯隧道爐(新)", "id" => [8]],
			["name" => "旋風烤爐", "id" => [9]],
			["name" => "燒臘區", "id" => [10]],
			["name" => "炒餡鍋", "id" => [11]],
			["name" => "燃氣發電機", "id" => [12]],
		]
	],
	["type" => "水", "unit" => "m³/hr", "r_table"=> "R_WM_daily", "r_col_key"=> "incr_acc", "ep_item_key" => "WM1",
		"area" => [
			["name" => "屋頂熱水出水", "id" => [1]],
			["name" => "屋頂蒸氣冷凝水回收", "id" => [2]],
			["name" => "屋頂燒臘生飲水", "id" => [3]],
			["name" => "屋頂10噸儲水槽", "id" => [4]],
			["name" => "自來水總表", "id" => [5]],
			["name" => "地下100噸儲水槽進水", "id" => [6]],
			["name" => "宿舍用水", "id" => [7]],
			["name" => "軟水處理", "id" => [8]],
			["name" => "100T--10T一般水槽", "id" => [9]],
			["name" => "生飲水處理槽", "id" => [10]],
			["name" => "頂樓10噸生飲水進水", "id" => [11]],
			["name" => "消防水", "id" => [12]],
			["name" => "筏基回收水", "id" => [13]],
			["name" => "廢水排放水", "id" => [14]],
			["name" => "SS監測儀", "id" => [15]],
			["name" => "殺菌釜進水", "id" => [16]],
			["name" => "殺菌釜回收水", "id" => [17]],
		]
	],
];

try 
{
	// 接收參數
	$last_year = new DateTime();
	$last_year->modify("-1 year");
	$report_date = $last_year->format('Y');
	if(isset($_REQUEST['report_date']))
	{
		$report_date = $_REQUEST['report_date'];
	}
	$report_date_2 = $report_date-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);

	// excel檔類型
	foreach ($excel_type as $et) 
	{
		// 走訪報表設定
		foreach ($report_cfg as $i_cfg => $cfg) 
		{
			if($et["name"] == "碳排量" && $cfg["type"] == "水")
			{
				// 水系統沒碳排量
				continue;
			}
			$output_filename = "年報表_{$cfg['type']}_{$et['name']}_{$report_date}.xlsx"; // 匯出excel檔名

			// 載入Excel模板
			$objReader = PHPExcel_IOFactory::createReader('Excel2007');
			$objPHPExcel = $objReader->load($et['template_path']);
			$ews = $objPHPExcel->getSheet(0); // 選取工作表1
			$max_row = $ews->getHighestRow(); // 最大row數
			// 標題
			$title = "新東陽-大園廠-".$report_date."年各廠區".$cfg['type'].$et['name']."(年報)";
			$ews->setCellValue("A1", $title);
			// 單位
			if($et['name'] == '用量')
			{
				$unit = "單位".$cfg['unit'];
				$ews->setCellValue("Q1", $unit);
			}

			// 撈出能源參數
			$convert_rate_co2e = 0;
			$co2e = 0;
			if($cfg['ep_item_key'] !== "")
			{
				$ep_data = $api->SelectEnergyParam(['item_key'=> $cfg['ep_item_key']])[0];
				$convert_rate_co2e = floatval($ep_data['convert_rate_co2e']);
				$co2e = floatval($ep_data['co2e']);
			}

			// 撈取 當日增加量
			$tb = $cfg['r_table'];
			$col = $cfg['r_col_key'];
			$id_arr = [];
			foreach (array_column($cfg['area'], 'id') as $arr) 
			{
				if(!empty($arr))
				{
					$id_arr = array_merge($id_arr,$arr);
				}
			}
			$id_arr = array_unique($id_arr);
			$id_in = implode(",", $id_arr);
			$date_prm = [
				'date_start' => "{$report_date}-01-01",
				'date_end' => "{$report_date}-12-31",
			];
			$date_prm_2 = [
				'date_start' => "{$report_date_2}-01-01",
				'date_end' => "{$report_date_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_date] = $sth->fetchAll(PDO::FETCH_ASSOC);
			$sth->execute($date_prm_2);
			$r_data[$report_date_2] = $sth->fetchAll(PDO::FETCH_ASSOC);

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

			$rno = 3; // 開始row數
			foreach ($cfg['area'] as $i_ar => $ar) // 區域
			{
				$ews->setCellValue("A{$rno}", $ar['name']); // 寫入區域
				$dt_row_inx = 0;
				foreach ($date_arr as $date_str) // 年度
				{
					$dt_rno = $rno + $dt_row_inx;
					$ews->setCellValue("B{$dt_rno}", $date_str); // 寫入年度
					$start_col = PHPExcel_Cell::columnIndexFromString('D') - 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 ($ar,$m) {
							return in_array($row['id'], $ar['id']) 
								&& date("n", strtotime($row['year_month'])) == $m;
						}));
						$use_val = array_sum(array_column($fil_data,'sum_use_val'));
						$co2e_val = ($use_val * $convert_rate_co2e) * $co2e;
						if($et['name'] == '用量')
						{
							$ews->setCellValue($col_str.$dt_rno, $use_val); // 用量
						}
						else if($et['name'] == '碳排量')
						{
							$ews->setCellValue($col_str.$dt_rno, $co2e_val); // 排碳量
						}						
					}
					$dt_row_inx += 1; // 每個日期+1 rows
				}
				$rno += 3; // 每個區域3 rows

			}

			// 隱藏row
			for($h = $rno; $h <= $max_row; $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__).' ['.$cfg['type'].'|'.$et['name'].'] report_date: '.$report_date.' 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);
}

?>