<?php
// http://127.0.0.1/hsintungyang-energy/action/schedule/excel_export_report_day.php?report_date=2024-07-17
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.'/daily/';
if (!file_exists($report_output_path)) {
	mkdir($report_output_path, 0777, true);
}

$template_path = "../../excel_template/report_day.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", "r_col_key"=> "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(A倉機房)", "id" => [11]],
			["name" => "冷凍#2(A倉機房)", "id" => [12]],
			["name" => "冷凍#3(A倉機房)", "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" => "1LA(西式調理區照明)", "id" => [23]],
			["name" => "2LA(2樓辦公區照明)", "id" => [24]],
			["name" => "RAC2(蒸煮區抽風機)", "id" => [25]],
			["name" => "A倉", "id" => [2610]],
			["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" => "頂樓135HP空壓機房", "id" => []],
			["name" => "冷凍庫控制盤ID38", "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" => "肉干胚冷凍庫", "id" => [61]],
			["name" => "ＥＰＢ烘烤區２", "id" => [62]],
			["name" => "1PA(A站110V插座)", "id" => [63]],
			["name" => "RP2(B站110V插座)", "id" => [64]],
			["name" => "物資冷凍庫RFMPC", "id" => [68]],
			["name" => "空調冷藏系統ACMP", "id" => [65]],
			["name" => "空調主機RFMPP", "id" => [66]],
			["name" => "大冷藏庫RFMPB", "id" => [67]],
			["name" => "低溫空調APMP-1", "id" => [656667]],
		]
	],
	["type" => "蒸汽", "unit" => "kg/hr", "r_table"=> "R_SM", "r_col_key"=> "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", "r_col_key"=> "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", "r_col_key"=> "Acc", "ep_item_key" => "WM1",
		"area" => [
			["name" => "屋頂熱水槽出水", "id" => [1]],
			["name" => "屋頂蒸汽冷凝水回收", "id" => [2]],
			["name" => "屋頂燒臘生飲水", "id" => [3]],
			["name" => "屋頂一般水", "id" => [4]],
			["name" => "自來水總表", "id" => [5]],
			["name" => "300T--100T蓄水池", "id" => [6]],
			["name" => "宿舍用水", "id" => [7]],
			["name" => "軟化水", "id" => [8]],
			["name" => "100T--10T一般水槽", "id" => [9]],
			["name" => "100T--地下室生飲水槽", "id" => [10]],
			["name" => "生飲水槽--10T屋頂生飲水槽", "id" => [11]],
			["name" => "消防水", "id" => [12]],
			["name" => "閥基水及回收水", "id" => [13]],
			["name" => "廢水流量計", "id" => [14]],
			["name" => "SS監測儀", "id" => [15]],
			["name" => "過濾水表", "id" => [16]],
		]
	],
];

try 
{
	// 接收參數
	$yesterday = new DateTime();
	$yesterday->modify("-1 days");
	$report_date = $yesterday->format('Y-m-d');
	if(isset($_REQUEST['report_date']))
	{
		$report_date = $_REQUEST['report_date'];
	}
	$report_date_2 = date("Y-m-d", strtotime("$report_date -1 year")); // 去年的當天

	// 建立和諧資料庫連綫 ...
	$check = @fsockopen(HTY_Server, '1433', $errno, $errstr, 0.2);
	$pdo_hty = null;
	if ($check)
	{
		$pdo_hty = new PDO(sprintf("sqlsrv:Server=%s; Database=%s;ConnectionPooling=0", HTY_Server, HTY_Database), HTY_UserID, HTY_Password);
	}
	else
	{
		throw new Exception('HTY database conection error.');
	}	

	// 建立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);

	// 整理每15分鐘起訖區間
	$s_dt = strtotime("$report_date");
	$e_dt = strtotime("$report_date +1 day");
	$range_15min = ["$report_date_2"=>[], "$report_date"=>[]];
	for ($dt = $s_dt; $dt < $e_dt; $dt = strtotime("+15 minutes", $dt)) 
	{
		$start = date("Y-m-d H:i:s",$dt);
		// $end = date("Y-m-d H:i:s", strtotime("+15 minutes", $dt));
		$range_15min["$report_date"][] = [
			"start"=> $start, 
			// "end"=> $end
		];
		$start_2 = date("$report_date_2 H:i:s", $dt);
		// $end_2 = date("Y-m-d H:i:s", strtotime("$start_2 +15 minutes"));
		$range_15min["$report_date_2"][] = [
			"start"=> $start_2, 
			// "end"=> $end_2
		];
	}

	// 走訪報表設定
	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 = "新東陽-大園廠-".date("Y", strtotime($report_date))."各廠區".$cfg['type']."用量(日報)";
		$ews->setCellValue("A1", $title);
		$ews->setCellValue("T1", $title);
		$ews->setCellValue("AM1", $title);
		$ews->setCellValue("BF1", $title);
		$ews->setCellValue("BY1", $title);
		// 單位
		$unit = "單位".$cfg['unit'];
		$ews->setCellValue("S1", $unit);
		$ews->setCellValue("AL1", $unit);
		$ews->setCellValue("BE1", $unit);
		$ews->setCellValue("BX1", $unit);
		$ews->setCellValue("CQ1", $unit);
		$ews->setCellValue("CW1", $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-d 00:00:00", strtotime($report_date)),
			'date_end' => date("Y-m-d 23:59:59", strtotime($report_date)),
		];
		$date_prm_2 = [
			'date_start' => date("Y-m-d 00:00:00", strtotime($report_date_2)),
			'date_end' => date("Y-m-d 23:59:59", strtotime($report_date_2)),
		];
		$r_data = [];

		$sql = "SELECT 
					[A].[ID]
					,[A].[interval_start] 
					,SUM([A].[diff_val]) AS [sum_use_val]
				FROM (
					SELECT [ID]
						,[DATE_TIME]
						,[$col]
						,CASE WHEN ISNULL([$col] - LAG([$col],1) OVER (PARTITION BY [ID] ORDER BY [DATE_TIME] ASC),0) < 0 THEN 0 
						ELSE ISNULL([$col] - LAG([$col],1) OVER (PARTITION BY [ID] ORDER BY [DATE_TIME] ASC),0) 
						END AS [diff_val]
						,DATEADD(MINUTE, (DATEDIFF(MINUTE, 0, [DATE_TIME]) / 15) * 15, 0) AS [interval_start]
					FROM [$tb]
					WHERE [DATE_TIME] BETWEEN :date_start AND :date_end AND [$col] > 0 AND [ID] IN ($id_in)
				) AS [A]
				GROUP BY [A].[ID], [A].[interval_start]
				ORDER BY [A].[ID] ASC, [A].[interval_start] ASC";

		$sth = $pdo_hty->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);

		$rno = 3; // 開始row數
		foreach ($cfg['area'] as $i_ar => $ar) // 區域
		{
			$ews->setCellValue("A{$rno}", $ar['name']); // 寫入區域
			$dt_row_inx = 0;
			foreach ($range_15min as $rg_date => $rg) // 日期時間區間
			{
				$dt_rno = $rno + $dt_row_inx;
				$ews->setCellValue("B{$dt_rno}", $rg_date); // 寫入日期
				$start_col = PHPExcel_Cell::columnIndexFromString('D') - 1; // 開始欄位(A=1, B=2 ...)
				foreach ($rg as $i_se => $se) // 15分鐘開始結束
				{
					$col_str = PHPExcel_Cell::stringFromColumnIndex($start_col + $i_se); // 取欄位字母(0='A', 1='B'...)
					// 取出區域id下的用量資料
					$fil_data = array_values(array_filter($r_data[$rg_date], function($row) use ($se,$ar) {
						return in_array($row['ID'], $ar['id']) 
							&& strtotime($row['interval_start']) == strtotime($se['start']);
					}));
					$use_val = array_sum(array_column($fil_data,'sum_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);
}

?>