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

$template_path = "../../excel_template/report_month.xlsx"; // excel模版
// 報表設定
$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_month = new DateTime();
	$last_month->modify("-1 month");
	$report_date = $last_month->format('Y-m');
	if(isset($_REQUEST['report_date']))
	{
		$report_date = $_REQUEST['report_date'];
	}
	$report_date_2 = date("Y-m", strtotime("$report_date -1 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);

	// 走訪報表設定
	foreach ($report_cfg as $i_cfg => $cfg) 
	{
		$output_filename = "月報表_{$cfg['type']}_{$report_date}.xlsx"; // 匯出excel檔名

		// 載入Excel模板
		$objReader = PHPExcel_IOFactory::createReader('Excel2007');
		$objPHPExcel = $objReader->load($template_path);
		$ews = $objPHPExcel->getSheet(0); // 選取工作表1
		$max_row = $ews->getHighestRow(); // 最大row數
		// 標題
		$title = "新東陽-大園廠-".$report_date."各廠區".$cfg['type']."用量(月報)";
		$ews->setCellValue("A1", $title);
		$ews->setCellValue("T1", $title);
		// 單位
		$unit = "單位".$cfg['unit'];
		$ews->setCellValue("S1", $unit);
		$ews->setCellValue("AJ1", $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' => date("Y-m-01", strtotime($report_date)),
			'date_end' => date("Y-m-t", strtotime($report_date)),
		];
		$date_prm_2 = [
			'date_start' => date("Y-m-01", strtotime($report_date_2)),
			'date_end' => date("Y-m-t", strtotime($report_date_2)),
		];
		$r_data = [];

		$sql = "SELECT [id]
					,[date_stamp]
					,[$col] AS [use_val]
					,DAY([date_stamp]) AS [dom]
				FROM [$tb]
				WHERE [date_stamp] BETWEEN :date_start AND :date_end AND [id] IN ($id_in)
				ORDER BY [id] ASC, [date_stamp] 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 ($d=1; $d<= 31; $d++) // 每日
				{ 
					if($d <= intval(date("t", strtotime($date_str)))) // 只處理每月最大天數
					{
						$col_str = PHPExcel_Cell::stringFromColumnIndex($start_col + ($d-1)); // 取欄位字母(0='A', 1='B'...)
						// 取出區域id下的用量資料
						$fil_data = array_values(array_filter($r_data[$date_str], function($row) use ($ar,$d) {
							return in_array($row['id'], $ar['id']) 
								&& $row['dom'] == $d;
						}));
						$use_val = array_sum(array_column($fil_data,'use_val'));
						$co2e_val = ($use_val * $convert_rate_co2e) * $co2e;
						$ews->setCellValue($col_str.$dt_rno, $use_val); // 用量
						$ews->setCellValue($col_str.($dt_rno+1), $co2e_val); // 排碳量
					}
				}
				$dt_row_inx += 2; // 每個日期+2 rows
			}
			$rno += 5; // 每個區域5 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'].'] 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);
}

?>