<?php
// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	// 撈取 全廠指標警戒範圍
	public function SelectIndicatorAlarm($params)
	{
		$sql = sprintf("SELECT * FROM [indicator_alarm_settings] ORDER BY [sort]");
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$key_data = [];
		foreach($sth->fetchAll(PDO::FETCH_ASSOC) as $row)
		{
			$key_data[$row['item_key']]['h'] = $row['h'];
			$key_data[$row['item_key']]['hh'] = $row['hh'];
		}
		return $key_data;
	}
	
	//指標總覽資料撈取
	public function SelectMeterIndex($params)
	{


		//指定設備ID資料
		$a_id_list = (array) $params["id_list"];
		//指定時間
		$s_target_date = (string) $params["date"];
		$s_target_date_to = (string) $params["date_to"]; // 全廠每日指標查詢 結束日期
		$query_month = false;
		if(strlen($s_target_date) == 7)
		{
			$query_month = true; // 查詢月份
		}
		if ($s_target_date != "") {
			$s_target_date = date("Y-m-d", strtotime($s_target_date));
		} else {
			$s_target_date = date("Y-m-d");
		}

		//設定查詢時間
		$s_start_time = date("Y-m-d 00:00:00", strtotime($s_target_date)); //當天起始時間
		$s_end_time = date("Y-m-d 23:59:59", strtotime($s_target_date)); //資料結束時間 
		if($s_target_date_to != '')
		{
			$s_end_time = date("Y-m-d 23:59:59", strtotime($s_target_date_to)); //資料結束時間 
		}
		if($query_month)
		{
			$s_start_time = date("Y-m-01 00:00:00", strtotime($s_target_date)); //當天起始時間
			$s_end_time = date("Y-m-t 23:59:59", strtotime($s_target_date)); //資料結束時間 
		}

		//依據時間準備時間區間陣列
		$a_date_range = $this->GetDateArr(strtotime($s_start_time), strtotime($s_end_time));
		//指標名稱對應陣列
		$a_label_name = [];
		//資料暫存陣列
		$a_temp_date = [];
		//準備查詢對應產線的sql字串
		$a_ProductionData_array = [];
		//準備查詢對應設備的sql字串
		$a_item_id_list_array = [];

		foreach ($a_id_list as $k => $v) {
			//整理pid字串
			$a_temp_pid = explode(",", $v["pid"]);
			if (!empty($a_temp_pid)) {
				foreach ($a_temp_pid as $k_2 => &$v_2) {
					$v_2 = "'" . $v_2 . "'";
				}
			}
			$s_temp_pid = implode(",", $a_temp_pid);
			$s_ID = $v["id"];
			$s_Table = $v["table"];
			$s_Name = $v["name"];
			$s_Key = $v["key"];
			$s_arr_key = $s_Table . "-" . $s_ID;
			//指標名稱對應陣列設定
			$a_label_name[$s_arr_key] = $s_Key;
			//產線部分
			//沒有指定ProductionID
			if (trim($v["pid"]) == '') {
				$a_ProductionData_array[] = sprintf(
					"SELECT
							'%s' As ID,
							'%s' As TBN,
							SUM (Weight) AS day_weight,
							CAST (EndTime AS DATE) AS day_date
						FROM
							[dbo].[ProductionData] 
						WHERE
							EndTime BETWEEN '%s' AND '%s'
						GROUP BY
							CAST (EndTime AS DATE)",
					$s_ID,
					$s_Table,
					$s_start_time,
					$s_end_time
				);
			}
			//指定ProductionID
			else {
				$a_ProductionData_array[] = sprintf(
					"SELECT
							'%s' As ID,
							'%s' As TBN,
							SUM (Weight) AS day_weight,
							CAST (EndTime AS DATE) AS day_date
						FROM
							[dbo].[ProductionData] 
						WHERE
							EndTime BETWEEN '%s' AND '%s'
							AND ProductionID IN (%s)
						GROUP BY
							CAST (EndTime AS DATE),
							ProductionID",
					$s_ID,
					$s_Table,
					$s_start_time,
					$s_end_time,
					$s_temp_pid
				);
			}
			//設備部分 (新東陽的)
			if (strpos($s_Table, "R_") !== false) {
				//先依據各表來存放稍後要查詢的ID
				$a_item_id_list_array[$s_Table][] = $s_ID;
			}
		}
		//撈取產能資料
		$s_ProductionData_sql = implode(" UNION ", $a_ProductionData_array);
		// return $s_ProductionData_sql;
		if ($this->pdo_ems != null) {
			$sth = $this->pdo_ems->prepare($s_ProductionData_sql);
			$sth->execute([]);
			$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
			foreach ($rows as $k => $v) {
				$s_arr_key = $v["TBN"] . "-" . $v["ID"];
				$a_temp_date["ProductionData"][$a_label_name[$s_arr_key]][$v["day_date"]] = $v;
			}
		}
		//撈取各設備資料(新東陽) $a_item_id_list_array 不為空才查詢
		if (!empty($a_item_id_list_array)) {
			//根據cte表來union的存放陣列
			$a_item_id_sql_array = [];
			//整理範圍CTE sql 根據$a_item_id_list_array 內的資料
			$a_item_id_sql_cte = [];
			foreach ($a_item_id_list_array as $k => $v) {
				$s_table_name = $k;
				$s_id_in = implode(",", $v);
				$a_item_id_sql_array[] = sprintf(" SELECT * FROM range_date_%s ", $s_table_name);
				if ($k == "R_PM") {
					$a_item_id_sql_cte[] = sprintf(
						" range_date_%s AS (
							SELECT 
								'%s' AS TBN,
								_rg.[incr_kwh] AS [dosage],
								_rg.[id] AS [ID],
								[date_stamp] AS day_date 
							FROM
							  [dbo].[%s_daily] AS _rg 
							WHERE
								_rg.[date_stamp] BETWEEN '%s' AND '%s' 
								AND _rg.[id] IN (%s)
						  ) ",
						$s_table_name,
						$s_table_name,
						$s_table_name,
						$s_start_time,
						$s_end_time,
						$s_id_in
					);
				} else {
					$a_item_id_sql_cte[] = sprintf(
						" range_date_%s AS (
							SELECT 
								'%s' AS TBN,
								_rg.[incr_acc] AS [dosage],
								_rg.[id] AS [ID],
								[date_stamp] AS day_date 
							FROM
								[dbo].[%s_daily] AS _rg 
							WHERE
								_rg.[date_stamp] BETWEEN '%s' AND '%s' 
								AND _rg.[id] IN (%s)
						) ",
						$s_table_name,
						$s_table_name,
						$s_table_name,
						$s_start_time,
						$s_end_time,
						$s_id_in
					);
				}
			}
			//cte表轉換sql字串
			$s_item_id_sql_cte = implode(",", $a_item_id_sql_cte);
			//union語句陣列轉換sql字串		
			$s_item_id_sql_union = implode(" UNION ", $a_item_id_sql_array);
			//組合設備查詢語句 (新東陽)
			$s_item_id_sql = sprintf(
				"WITH %s %s",
				$s_item_id_sql_cte,
				$s_item_id_sql_union
			);
			// return $s_item_id_sql;
			$sth = $this->pdo_sql->prepare($s_item_id_sql);
			$sth->execute([]);
			$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
			foreach ($rows as $k => $v) {
				$s_arr_key = $v["TBN"] . "-" . (int) $v["ID"];
				$a_temp_date["ValueData"][$a_label_name[$s_arr_key]][$v["day_date"]] = $v;
			}
		}

		$res = [];
		//依據一開始建立的指標名稱陣列來整理回傳資料
		foreach ($a_label_name as $k => $v) {
			//依據時間陣列來建立以日為區間的資料
			foreach ($a_date_range["d_list"] as $a_date_k => $a_date_v) {
				$s_date = $a_date_v["y"] . "-" . $a_date_v["m"] . "-" . $a_date_v["d"];
				if (date("Y-m-d") >= $s_date) {

					$a_temp_info = [
						"day_date" => $s_date,
						"dosage" => 0,
						// "Min_value" => 0,
						// "Max_value" => 0,
						"day_weight" => 0,
					];
					//產量整理
					if (isset($a_temp_date["ProductionData"][$v][$s_date])) {
						$a_temp_info["day_weight"] = 
						number_format($a_temp_date["ProductionData"][$v][$s_date]["day_weight"] , 2, ".", "");
					}
					//能耗整理
					if (isset($a_temp_date["ValueData"][$v][$s_date])) {
						$a_temp_info["dosage"] = number_format($a_temp_date["ValueData"][$v][$s_date]["dosage"], 2, ".", "");
						// $a_temp_info["Max_value"] = number_format($a_temp_date["ValueData"][$v][$s_date]["Max_value"], 2, ".", "");
						// $a_temp_info["Min_value"] = number_format($a_temp_date["ValueData"][$v][$s_date]["Min_value"], 2, ".", "");
					}
					$res[$v][] = $a_temp_info;
				}
			}
		}


		return $res;
	}
	//取得時間區間陣列
	public function GetDateArr($_dStartDate, $_dEndtDate): array
	{

		/*
			# 取得時間區間轉換為年月及月日區間之陣列 
			# $_dStartDate -> 起始時間(時間戳)
			# $_dEndtDate -> 結束時間(時間戳)
			# 回傳範例
			# array[y_list] => 以年來顯示的區間陣列
			# array[m_list] => 以年月來顯示的區間陣列
			# array[d_list] => 以月日來顯示的區間陣列
			*/

		#建立時間區間陣列
		$aDTimeTab = array();



		#判斷是否傳入為正確時間戳
		if (is_numeric($_dStartDate) && is_numeric($_dEndtDate) && $_dStartDate <= $_dEndtDate) {

			$sStart = date('Y-m-d', $_dStartDate);
			$sEnd =  date('Y-m-d', ($_dEndtDate + 86400));

			$period = new DatePeriod(
				new DateTime($sStart),
				new DateInterval('P1D'),
				new DateTime($sEnd)
			);
			$_sYear = '0';
			$_sMonth = '0';
			$_sDay = '0';

			foreach ($period as $date) {
				$arr = array();
				if ($_sYear != $date->format('Y')) {
					$_sYear = $date->format('Y');
					$arr['y'] = $_sYear;
					$aDTimeTab['y_list'][] = $arr;
				};
				if ($_sMonth != $date->format('m')) {
					$_sMonth = $date->format('m');
					$arr['y'] = $_sYear;
					$arr['m'] = $_sMonth;
					$aDTimeTab['m_list'][] = $arr;
				}
				if ($_sDay != $date->format('d')) {
					$_sDay = $date->format('d');
					$arr['y'] = $_sYear;
					$arr['m'] = $_sMonth;
					$arr['d'] = $_sDay;
					$aDTimeTab['d_list'][] = $arr;
				}
			}
		}

		return $aDTimeTab;
	}
}

