<?php

// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	public function SelectLoadOptions($params)
	{
		$rlt = [];
		$sql = sprintf(
			"SELECT 
                 [AreaName] AS [value]
            FROM [%s].[dbo].[TableOrder]
			GROUP BY [AreaName]
            ;",
			$this->database_trend
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$rlt['meter_group'] = $sth->fetchAll(PDO::FETCH_ASSOC);

		$sql = sprintf(
			"SELECT 
				[AreaName]
				,[ColumnName] 
			   	,[ColumnTable]
				,[IsTotal]
			FROM [%s].[dbo].[TableOrder]
			ORDER BY [IsTotal] DESC,[AreaIndex] ASC
            ;",
			$this->database_trend
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		foreach ($rows as $k => $v) {
			$rlt['meter_line'][] = [
				"label" => $v["ColumnName"],
				"value" => $v["ColumnTable"],
			];
			$rlt['meter_group_item'][$v["AreaName"]][] = [
				"label" => $v["ColumnName"],
				"value" => $v["ColumnTable"],
				"IsTotal" => $v["IsTotal"],
			];
		};

		return $rlt;
	}
	public function SelectLoadDeviceOptions()
	{

		$sql = sprintf(
			"SELECT 
				ColumnTable, 
				ColumnName 
			FROM 
				%s.dbo.TableOrder 
			ORDER BY AreaIndex ",
			$this->database_trend
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	// 取得 繪製Chart 的資料
	public function SelectData($params)
	{
		$begTimestamp = date('Y-m-d', strtotime($params["beg_date"]));
		$endTimestamp = date('Y-m-d', strtotime($params["end_date"]));
		$tmSelectSubSql = "LEFT(CONVERT(varchar, Time_Stamp, 120),16) as tm"; // 顯示日期與時間(去秒數)
		$tmWhereSubSql = " and DATEPART(minute, Time_Stamp) = 0 AND DATEPART(second, Time_Stamp) = 0 "; // 只抓整點
		if ($begTimestamp === $endTimestamp) { // 同一天
			$tmSelectSubSql = "CONVERT(varchar, Time_Stamp, 108) as tm"; // 只顯示時間
			$tmWhereSubSql = ""; // 抓每一筆
		}
		$sql = sprintf(
			"SELECT 
				%s,
				* 
			FROM 
				[%s].[dbo].[%s] 
			WHERE 
				Time_Stamp >= :beg_date AND Time_Stamp <= :end_date 
				%s 
			ORDER BY Time_Stamp;",
			$tmSelectSubSql,
			$this->database_trend,
			$params["db_name"],
			$tmWhereSubSql
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			"beg_date" => $params["beg_date"],
			"end_date" => $params["end_date"],
		]);
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	// 取得 繪製Chart 的資料 其他項目
	public function SelectData2($params)
	{
		$begTimestamp = date('Y-m-d', strtotime($params["beg_date"]));
		$endTimestamp = date('Y-m-d', strtotime($params["end_date"]));
		$tmSelectSubSql = "LEFT(CONVERT(varchar, Time_Stamp, 120),16) as tm"; // 顯示日期與時間(去秒數)
		$tmWhereSubSql = " and DATEPART(minute, Time_Stamp) = 0 AND DATEPART(second, Time_Stamp) = 0 "; // 只抓整點
		if ($begTimestamp === $endTimestamp) { // 同一天
			$tmSelectSubSql = "CONVERT(varchar, Time_Stamp, 108) as tm"; // 只顯示時間
			$tmWhereSubSql = ""; // 抓每一筆
		}
		$sql = sprintf(
			"SELECT 
					%s,
					* 
				FROM 
					[%s].[dbo].[%s] 
				WHERE 
					Time_Stamp >= :beg_date AND Time_Stamp <= :end_date 
					%s 
				ORDER BY Time_Stamp;",
			$tmSelectSubSql,
			$this->database_trend,
			$params["db_name"],
			$tmWhereSubSql
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			"beg_date" => $params["beg_date"],
			"end_date" => $params["end_date"],
		]);
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	//長條圖資料撈取
	public function Selectbar1_data($params)
	{
		//第一份資料指定日期區間
		$s_date_1 = (string) $params["date_1"];
		//第一份資料的設備
		$a_date_1_item = (array) $params["item_1"];
		//第二份資料指定日期區間
		$s_date_2 = (string) $params["date_2"];
		//第二份資料的設備
		$a_date_2_item = (array) $params["item_2"];
		//資料顯示類型 hour: 以小時為統計單位(查日) day: 以日為統計單位(查月) month: 以月為統計單位(查年)
		$s_data_type = (string) $params["data_type"];
		$s_data_type = trim($s_data_type);

		if ($s_data_type == "") {
			throw new Exception("資料顯示類型錯誤", 1);
		}

		if (
			empty($a_date_1_item) ||
			empty($a_date_2_item)
		) {
			throw new Exception("廠區資料不可為空", 1);
		}
		//整理第一份資料的設備清單 過濾虛擬電表
		foreach ($a_date_1_item as $k => &$v) {
			$v = "'" . $v . "'";
		}
		$sql_check_IsVirtualMeter = sprintf(
			"SELECT
						ColumnTable
					FROM
						[%s].[dbo].[TableOrder]
					WHERE
						ColumnTable IN (%s)
						AND IsVirtualMeter = 0",
			$this->database_trend,
			implode(",", $a_date_1_item)
		);
		//執行查詢
		$sth = $this->pdo_sql->prepare($sql_check_IsVirtualMeter);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		$a_date_1_item = [];
		foreach ($rows as $k => $v) {
			$a_date_1_item[] = $v["ColumnTable"];
		}

		//整理第二份資料的設備清單 過濾虛擬電表
		foreach ($a_date_2_item as $k => &$v) {
			$v = "'" . $v . "'";
		}
		$sql_check_IsVirtualMeter = sprintf(
			"SELECT
						ColumnTable
					FROM
						[%s].[dbo].[TableOrder]
					WHERE
						ColumnTable IN (%s)
						AND IsVirtualMeter = 0",
			$this->database_trend,
			implode(",", $a_date_2_item)
		);
		//執行查詢
		$sth = $this->pdo_sql->prepare($sql_check_IsVirtualMeter);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		$a_date_2_item = [];
		foreach ($rows as $k => $v) {
			$a_date_2_item[] = $v["ColumnTable"];
		}
		//根據資料顯示型態來設定資料查詢的時間區間 以及資料統計單位
		//第一份資料區間
		$s_1_start_date = "";
		$s_1_end_date = "";
		//第二份資料區間
		$s_2_start_date = "";
		$s_2_end_date = "";
		switch ($s_data_type) {
			case 'hour':
				$s_1_start_date = date("Y-m-d 00:00:00", strtotime($s_date_1));
				$s_1_end_date = date("Y-m-d 23:59:59", strtotime($s_date_1));
				$s_2_start_date = date("Y-m-d 00:00:00", strtotime($s_date_2));
				$s_2_end_date = date("Y-m-d 23:59:59", strtotime($s_date_2));
				break;
			case 'day':
				$s_1_start_date = date("Y-m-01 00:00:00", strtotime($s_date_1));
				$s_1_end_date = date("Y-m-t 23:59:59", strtotime($s_date_1));
				$s_2_start_date = date("Y-m-01 00:00:00", strtotime($s_date_2));
				$s_2_end_date = date("Y-m-t 23:59:59", strtotime($s_date_2));
				break;
			case 'month':
				$s_1_start_date = date("Y-01-01 00:00:00", strtotime($s_date_1));
				$s_1_end_date = date("Y-12-31 23:59:59", strtotime($s_date_1));
				$s_2_start_date = date("Y-01-01 00:00:00", strtotime($s_date_2));
				$s_2_end_date = date("Y-12-31 23:59:59", strtotime($s_date_2));
				break;
		}
		if (date("d", strtotime($s_1_end_date)) > date("d", strtotime($s_2_end_date))) {
			$a_temp_date_arr = $this->GetDateArr(strtotime($s_1_start_date), strtotime($s_1_end_date), $s_data_type);
		} else {
			$a_temp_date_arr = $this->GetDateArr(strtotime($s_2_start_date), strtotime($s_2_end_date), $s_data_type);
		}
		//回傳陣列預設
		$res_temp = [
			"data_1" => [],
			"data_2" => [],
		];
		if ($s_data_type == "hour") {
			foreach ($a_temp_date_arr as $k => $v) {
				$res_temp["data_1"][$v] = 0;
				$res_temp["data_2"][$v] = 0;
			}
		} else {
			if ($s_data_type == "day") {
				foreach ($a_temp_date_arr["d_list"] as $k => $v) {
					$res_temp["data_1"][$v["d"]] = 0;
					$res_temp["data_2"][$v["d"]] = 0;
				}
			} else {
				foreach ($a_temp_date_arr["m_list"] as $k => $v) {
					$res_temp["data_1"][$v["m"]] = 0;
					$res_temp["data_2"][$v["m"]] = 0;
				}
			}
		}
		//開始依據兩個時間區間來撈取資料

		#######################第一份資料區間#########################
		$sql_date_1 = "";
		if ($s_data_type == "hour") {
			$a_item_1_cte = [];
			//小時處理方式
			foreach ($a_date_1_item as $k => $v) {
				//表名稱
				$s_table_name = $v;
				$a_item_1_cte[] = sprintf(
					" SELECT
							DATEPART( HOUR, Time_Stamp ) AS time_group,
							MIN ( KWH ) AS min_KWH,
							MAX ( KWH ) AS max_KWH
						FROM
							[%s].[dbo].[%s] 
						WHERE
							Time_Stamp BETWEEN '%s' AND '%s' 
						GROUP BY
							DATEPART( HOUR, Time_Stamp ) ",
					$this->database_trend,
					$s_table_name,
					$s_1_start_date,
					$s_1_end_date
				);
			}
			$s_item_1_cte = implode(" UNION ALL ", $a_item_1_cte);
			$sql_date_1 = sprintf(
				"SELECT
						_t.time_group,
						_t.max_KWH - _t.min_KWH AS total_KWH
					FROM
					( %s ) AS _t",
				$s_item_1_cte
			);
		} else if ($s_data_type == "day") {

			foreach ($a_date_1_item as $k => &$v) {
				$v = "'" . $v . "'";
			}
			$s_date_1_item = implode(",", $a_date_1_item);
			$sql_date_1 = sprintf(
				"SELECT
						DATEPART( DAY, Time_Stamp ) AS time_group,
						Today_KWH AS total_KWH
					FROM
						[%s].[dbo].[ACM_Daily_KWH]
					WHERE
						Time_Stamp BETWEEN '$s_1_start_date' AND '$s_1_end_date' 
						AND ACM IN ( $s_date_1_item  )
						",
				$this->database_trend
			);
		} else if ($s_data_type == "month") {

			foreach ($a_date_1_item as $k => &$v) {
				$v = "'" . $v . "'";
			}
			$s_date_1_item = implode(",", $a_date_1_item);
			$sql_date_1 = sprintf(
				"SELECT
							DATEPART( MONTH, Time_Stamp ) AS time_group,
							SUM(Today_KWH) AS total_KWH
						FROM
							[%s].[dbo].[ACM_Daily_KWH]
						WHERE
							Time_Stamp BETWEEN '$s_1_start_date' AND '$s_1_end_date'
							AND ACM IN ( $s_date_1_item  )
					GROUP BY
						DATEPART( MONTH, Time_Stamp )",
				$this->database_trend
			);
		}
		//執行查詢
		$sth = $this->pdo_sql->prepare($sql_date_1);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		foreach ($rows as $k => $v) {
			$s_inx = str_pad($v["time_group"], 2, "0", STR_PAD_LEFT);
			$res_temp["data_1"][$s_inx] = bcadd($res_temp["data_1"][$s_inx], $v["total_KWH"], 2);
		}
		#######################第一份資料區間#########################

		#######################第二份資料區間#########################
		$sql_date_2 = "";
		if ($s_data_type == "hour") {
			$a_item_2_cte = [];
			//小時處理方式
			foreach ($a_date_2_item as $k => $v) {
				//表名稱
				$s_table_name = $v;
				$a_item_2_cte[] = sprintf(
					" SELECT
							DATEPART( HOUR, Time_Stamp ) AS time_group,
							MIN ( KWH ) AS min_KWH,
							MAX ( KWH ) AS max_KWH
						FROM
							[%s].[dbo].[%s] 
						WHERE
							Time_Stamp BETWEEN '%s' AND '%s' 
						GROUP BY
							DATEPART( HOUR, Time_Stamp ) ",
					$this->database_trend,
					$s_table_name,
					$s_2_start_date,
					$s_2_end_date
				);
			}
			$s_item_2_cte = implode(" UNION ALL ", $a_item_2_cte);
			$sql_date_2 = sprintf(
				"SELECT
						_t.time_group,
						_t.max_KWH - _t.min_KWH AS total_KWH
					FROM
					( %s ) AS _t",
				$s_item_2_cte
			);
		} else if ($s_data_type == "day") {

			foreach ($a_date_2_item as $k => &$v) {
				$v = "'" . $v . "'";
			}
			$s_date_2_item = implode(",", $a_date_2_item);
			$sql_date_2 = sprintf(
				"SELECT
						DATEPART( DAY, Time_Stamp ) AS time_group,
						Today_KWH AS total_KWH
					FROM
						[%s].[dbo].[ACM_Daily_KWH]
					WHERE
						Time_Stamp BETWEEN '$s_2_start_date' AND '$s_2_end_date' 
						AND ACM IN ( $s_date_2_item  )
						",
				$this->database_trend
			);
		} else if ($s_data_type == "month") {

			foreach ($a_date_2_item as $k => &$v) {
				$v = "'" . $v . "'";
			}
			$s_date_2_item = implode(",", $a_date_2_item);
			$sql_date_2 = sprintf(
				"SELECT
							DATEPART( MONTH, Time_Stamp ) AS time_group,
							SUM(Today_KWH) AS total_KWH
						FROM
							[%s].[dbo].[ACM_Daily_KWH]
						WHERE
							Time_Stamp BETWEEN '$s_2_start_date' AND '$s_2_end_date'
							AND ACM IN ( $s_date_2_item  )
					GROUP BY
						DATEPART( MONTH, Time_Stamp )",
				$this->database_trend
			);
		}
		//執行查詢
		$sth = $this->pdo_sql->prepare($sql_date_2);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		foreach ($rows as $k => $v) {
			$s_inx = str_pad($v["time_group"], 2, "0", STR_PAD_LEFT);
			$res_temp["data_2"][$s_inx] = bcadd($res_temp["data_2"][$s_inx], $v["total_KWH"], 2);
		}
		#######################第二份資料區間#########################

		//回傳資料轉換整理
		$res = [];
		foreach ($res_temp as $k => $v) {
			foreach ($v as $k_2 => $v_2) {
				$res[$k][] = [
					"label" => $k_2,
					"value" => $v_2
				];
			}
		}



		return $res;
	}

	// 取得 電力 趨勢圖資料
	public function Selectelectricity_trend_data($params)
	{
		$res = [
			"x_label" => [], //x軸資料
			"line_data" => [], //各線資料
		];

		//資料起始時間
		$s_start_at = (string) $params["start_at"];
		$s_start_at = trim($s_start_at);
		//資料結束時間
		$s_end_at = (string) $params["end_at"];
		$s_end_at = trim($s_end_at);
		//設備清單
		$a_device_list = (array) $params["device_list"];
		//項目清單
		$a_item_list = (array) $params["item_list"];

		//依據項目清單 整理出需要撈取的欄位
		$s_sql_column = implode(",", $a_item_list);

		//依據設備清單整理出union all 各表語句
		$a_sql_union_all = [];
		foreach ($a_device_list as $k => $v) {
			$a_sql_union_all[] = sprintf(
				"SELECT 
					'$v' AS target_name, 
					LEFT(CONVERT(varchar, Time_Stamp, 120),16) as tm,
					%s
				FROM 
					[%s].[dbo].[%s] 
				WHERE 
					Time_Stamp >= '%s' 
					AND Time_Stamp <= '%s' 
					AND DATEPART( minute, Time_Stamp ) = 0 
					AND DATEPART( second, Time_Stamp ) = 0 
				",
				$s_sql_column,
				$this->database_trend,
				$v,
				$s_start_at,
				$s_end_at
			);
		}
		//組合sql 語句 並執行
		$sql = implode(" UNION ALL ", $a_sql_union_all);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		$line_temp = [];
		foreach ($rows as $k => $v) {
			//x軸label 設定
			if (!in_array($v["tm"], $res["x_label"])) {
				$res["x_label"][] = $v["tm"];
			}
			//各線資料
			foreach ($a_item_list as $item_k => $item_v) {
				$res["line_data"][$v["target_name"]][$item_v][] = [
					$v["tm"],
					(float) number_format($v[$item_v], 2, ".", "")
				];
			}
		}
		//遞增排序整理時間軸
		sort($res["x_label"]);
		//整理各線資料 
		$line_inx = 0;
		//依據查詢欄位來依序對各設備進行線資料處理
		/*
		例如:
		[
			設備1-項目1=>[資料陣列],
			設備2-項目1=>[資料陣列],
			設備1-項目2=>[資料陣列],
			設備2-項目2=>[資料陣列],
		]
		*/
		foreach ($a_item_list as $k => $v) {
			foreach ($line_temp as $k_2 => $v_2) {
				$res["line_data"][$line_inx] = $v_2[$v];
				$line_inx++;
			}
		}

		// $begTimestamp = date('Y-m-d', strtotime($params["beg_date"]));
		// $endTimestamp = date('Y-m-d', strtotime($params["end_date"]));
		// $tmSelectSubSql = "LEFT(CONVERT(varchar, Time_Stamp, 120),16) as tm"; // 顯示日期與時間(去秒數)
		// $tmWhereSubSql = " and DATEPART(minute, Time_Stamp) = 0 AND DATEPART(second, Time_Stamp) = 0 "; // 只抓整點
		// if ($begTimestamp === $endTimestamp) { // 同一天
		// 	$tmSelectSubSql = "CONVERT(varchar, Time_Stamp, 108) as tm"; // 只顯示時間
		// 	$tmWhereSubSql = ""; // 抓每一筆
		// }
		// $sql = sprintf(
		// 	"SELECT 
		// 		%s,
		// 		* 
		// 	FROM 
		// 		[%s].[dbo].[%s] 
		// 	WHERE 
		// 		Time_Stamp >= '%s' 
		// 		AND Time_Stamp <= '%s' 
		// 		AND DATEPART( minute, Time_Stamp ) = 0 
		// 		AND DATEPART( second, Time_Stamp ) = 0 
		// 	",
		// 	$tmSelectSubSql,
		// 	$this->database_trend,
		// 	$params["db_name"],
		// 	$tmWhereSubSql
		// );
		// $sth = $this->pdo_sql->prepare($sql);
		// $sth->execute([
		// 	"beg_date" => $params["beg_date"],
		// 	"end_date" => $params["end_date"],
		// ]);
		// $sth->fetchAll(PDO::FETCH_ASSOC);

		return $res;
	}


	public function GetDateArr($_dStartDate, $_dEndtDate, $s_arr_type = "day"): array
	{

		/*
        # 取得時間區間轉換為年月及月日區間之陣列 (lbs)
        # $_dStartDate -> 起始時間(時間戳)
        # $_dEndtDate -> 結束時間(時間戳)
		# $s_arr_type -> 判斷要產出何種陣列 day: 最小單位以日為區間 hour:回傳24小時區間陣列
        # 回傳範例
        # array[y_list] => 以年來顯示的區間陣列
        # array[m_list] => 以年月來顯示的區間陣列
        # array[d_list] => 以月日來顯示的區間陣列
        */

		#建立時間區間陣列
		$aDTimeTab = array();
		//小時
		if ($s_arr_type == "hour") {
			$sStart = date('Y-m-d 00:00:00');
			$sEnd =  date('Y-m-d 23:59:59');
			$period = new DatePeriod(
				new DateTime($sStart),
				new DateInterval('PT1H'),
				new DateTime($sEnd)
			);
			foreach ($period as $date) {
				$s_hour = $date->format('H');
				$aDTimeTab[] = $s_hour;
			}
		}
		//日
		else {
			#判斷是否傳入為正確時間戳
			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;
	}
}
