<?php
// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{



	//	撈取趨勢圖資料
	public function SelectBarData($params)
	{
		//第一組資料的時間 
		$s_date1 = (string) $params['date1'];
		//第二組資料的時間 
		$s_date2 = (string) $params['date2'];
		//要撈取的設備ID清單
		$a_id_list = (array) $params['id_list'];
		//要撈取的設備群組 電力、蒸汽、瓦斯、水
		$s_data_group = (string) $params['data_group'];




		//依據條件範圍整理時間格式
		//第一組時間區間
		$s_1_start_time = date("Y-m-01 00:00:00", strtotime($s_date1));
		$s_1_end_time = date("Y-m-t 23:59:59", strtotime($s_date1));
		//第二組時間區間
		$s_2_start_time = date("Y-m-01 00:00:00", strtotime($s_date2));
		$s_2_end_time = date("Y-m-t 23:59:59", strtotime($s_date2));
		//整理設備ID清單
		$a_temp_id_list = explode(",", implode(",", $a_id_list));
		$s_id_list = implode(",", array_unique($a_temp_id_list));

		//區域條件設定
		$s_where_cte = "";
		$s_where_tid = "";
		if ($s_id_list != "") {
			$s_where_cte = " AND _rg.ID IN ($s_id_list) ";
			$s_where_tid = " AND _sf.Tid IN ($s_id_list) ";
		}
		//依據設備群組設定要撈取的資料表
		$s_tabel_prefix = "";
		switch ($s_data_group) {
			case '電力':
				$s_tabel_prefix = "PM";
				break;
			case '蒸汽':
				$s_tabel_prefix = "SM";
				break;
			case '瓦斯':
				$s_tabel_prefix = "GM";
				break;
			case '水':
				$s_tabel_prefix = "WM";
				break;
		}
		if ($s_tabel_prefix == "") {
			throw new Exception("設備群組錯誤", 1);
		}
		$a_temp_date_arr = [];
		if (date("d", strtotime($s_1_end_time)) > date("d", strtotime($s_2_end_time))) {
			$a_temp_date_arr = $this->GetDateArr(strtotime($s_1_start_time), strtotime($s_1_end_time));
		} else {
			$a_temp_date_arr = $this->GetDateArr(strtotime($s_2_start_time), strtotime($s_2_end_time));
		}
		//資料暫存陣列
		$a_temp_date = [
			"date_1" => [],
			"date_2" => [],
		];

		foreach ($a_temp_date_arr["d_list"] as $k => $v) {
			$a_temp_date["date_1"][$v["d"]] = 0;
			$a_temp_date["date_2"][$v["d"]] = 0;
		}

		//電力
		if ($s_tabel_prefix == "PM") {
			//設定兩組時間所使用的查詢sql，注意%s是要依據兩組時間來替換的值
			$sql_base = "WITH range_id_date AS (
								SELECT 
									MAX ( _rg.DATE_TIME ) AS Max_Date,
									MIN ( _rg.DATE_TIME ) AS Min_Date,
									_rg.ID,
									CAST ( _rg.DATE_TIME AS DATE ) AS day_date 
								FROM
									[dbo].[R_PM] AS _rg 
								WHERE
									_rg.DATE_TIME BETWEEN '%s' AND '%s' 
									$s_where_cte
								GROUP BY
									CAST ( _rg.DATE_TIME AS DATE ),
									_rg.ID 
							) SELECT
							_rp.ID,
							_rp.day_date,
							(
							SELECT TOP
								1 KWH 
							FROM
								R_PM 
							WHERE
								ID = _rp.ID 
								AND DATE_TIME = _rp.Min_Date 
							) AS MIN_KWH,
							(
							SELECT TOP
								1 KWH 
							FROM
								R_PM 
							WHERE
								ID = _rp.ID 
								AND DATE_TIME = _rp.Max_Date 
							) AS MAX_KWH 
						FROM
							range_id_date AS _rp
						ORDER BY
							_rp.day_date";
			if ($this->pdo_hty != null) {
				//第一組時間的查詢sql
				$sql_date_1 = sprintf($sql_base, $s_1_start_time, $s_1_end_time);
				//第二組時間的查詢sql
				$sql_date_2 = sprintf($sql_base, $s_2_start_time, $s_2_end_time);
				//開始各別查詢
				$sth = $this->pdo_hty->prepare($sql_date_1);
				$sth->execute([]);
				$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
				foreach ($rows as $k => &$v) {
					$s_day = date("d", strtotime($v["day_date"]));
					$a_temp_date["date_1"][$s_day] += bcsub($v["MAX_KWH"], $v["MIN_KWH"], 2);
				}
				//判斷第一組時間與第二組時間是否相同
				//不相同的話才進行第二組時間的撈取
				//相同的話第二組資料會與第一組相同，就不用再撈了
				if ($s_1_start_time != $s_2_start_time) {
					$sth = $this->pdo_hty->prepare($sql_date_2);
					$sth->execute([]);
					$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
					foreach ($rows as $k => &$v) {
						$s_day = date("d", strtotime($v["day_date"]));
						$a_temp_date["date_2"][$s_day] += bcsub($v["MAX_KWH"], $v["MIN_KWH"], 2);
					}
				} else {
					$a_temp_date["date_2"] = $a_temp_date["date_1"];
				}
			}
		}
		//蒸汽、瓦斯、水 
		else {
			//設定兩組時間所使用的查詢sql，注意%s是要依據兩組時間來替換的值
			$sql_base = "WITH range_row_no AS (
						SELECT 
							MAX( _rg.DATE_TIME ) AS Max_Date,
							MIN ( _rg.DATE_TIME ) AS Min_Date,
							_rg.ID,
							CAST ( _rg.DATE_TIME AS DATE ) AS day_date
						FROM
							[dbo].[R_$s_tabel_prefix] AS _rg 
						WHERE
							_rg.DATE_TIME BETWEEN '%s' AND '%s' 
							$s_where_cte 
						GROUP BY
							CAST ( _rg.DATE_TIME AS DATE ),
							_rg.ID 
					)
					SELECT
						_rg.ID ,
						_rg.day_date,
						( SELECT TOP 1 Acc FROM R_$s_tabel_prefix WHERE ID = _rg.ID AND DATE_TIME = _rg.Min_Date ) AS MIN_Acc,
						( SELECT TOP 1 Acc FROM R_$s_tabel_prefix WHERE ID = _rg.ID AND DATE_TIME = _rg.Max_Date ) AS MAX_Acc,
						( SELECT TOP 1 Flow FROM R_$s_tabel_prefix WHERE ID = _rg.ID AND DATE_TIME = _rg.Min_Date ) AS MIN_Flow,
						( SELECT TOP 1 Flow FROM R_$s_tabel_prefix WHERE ID = _rg.ID AND DATE_TIME = _rg.Max_Date ) AS MAX_Flow 
					FROM
						range_row_no AS _rg
					ORDER BY
						_rg.day_date";
			// return $sql_base;
			if ($this->pdo_hty != null) {
				//第一組時間的查詢sql
				$sql_date_1 = sprintf($sql_base, $s_1_start_time, $s_1_end_time);
				//第二組時間的查詢sql
				$sql_date_2 = sprintf($sql_base, $s_2_start_time, $s_2_end_time);
				//開始各別查詢
				$sth = $this->pdo_hty->prepare($sql_date_1);
				$sth->execute([]);
				$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
				foreach ($rows as $k => &$v) {
					$s_day = date("d", strtotime($v["day_date"]));
					$a_temp_date["date_1"][$s_day] += bcsub($v["MAX_Acc"], $v["MIN_Acc"], 2);
				}
				//判斷第一組時間與第二組時間是否相同
				//不相同的話才進行第二組時間的撈取
				//相同的話第二組資料會與第一組相同，就不用再撈了
				if ($s_1_start_time != $s_2_start_time) {
					$sth = $this->pdo_hty->prepare($sql_date_2);
					$sth->execute([]);
					$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
					foreach ($rows as $k => &$v) {
						$s_day = date("d", strtotime($v["day_date"]));
						$a_temp_date["date_2"][$s_day] += bcsub($v["MAX_Acc"], $v["MIN_Acc"], 2);
					}
				} else {
					$a_temp_date["date_2"] = $a_temp_date["date_1"];
				}
			}
		}
		//整理回傳陣列
		$res = [
			"date_1" => [],
			"date_2" => [],
		];
		if (!empty($a_temp_date["date_1"])) {
			foreach ($a_temp_date["date_1"] as $k => $v) {
				$res["date_1"][] = [
					"Date" => $k,
					"Val" => number_format($v,2,".",""),
				];
			}
			foreach ($a_temp_date["date_2"] as $k => $v) {
				$res["date_2"][] = [
					"Date" => $k,
					"Val" => number_format($v,2,".",""),
				];
			}
		}

		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;
	}
}
