<?php
trait base_class_trait
{
	// 取得帳號的可讀取案場清單(若未傳帳號,則為目前使用者)
	public function GetUserStations($userId)
	{
		if ($userId == null) {
			$userId = $this->user_id;
		}
		$sql = "select * from v_roles_users_stations where user_id=:user_id";
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute(array("user_id" => $userId));
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	// 取得帳號設定的角色(若未傳帳號,則為目前使用者)
	public function GetUserRoles($userId)
	{
		if ($userId == null) {
			$userId = $this->user_id;
		}
		$sql = "select * from v_roles_users where user_id=:user_id";
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute(array("user_id" => $userId));
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	// 檢查這個帳號是有這個角色
	public function ChkUserRole($roleId, $userRolesList, $userId)
	{
		if ($userId == null) {
			$userId = $this->user_id;
		}
		if ($userRolesList == null) { // 若多次呼叫此函式,可傳入$userRolesList則不用再次取得角色清單,較有效率
			$userRolesList = $this->GetUserRoles($userId);
		}
		if (count($userRolesList) > 0) {
			foreach ($userRolesList as $role) {
				if ($role["role_id"] == $roleId) {
					return true;
				}
			}
		}
		return false;
	}

	// 取得指定案場的INV清單(若不傳入stn則抓全部)
	public function GetStationInv($stn)
	{
		if ($stn == null) {
			$sql = "select * from station_inv_2 order by STN,No";
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute();
		} else {
			$sql = "select * from station_inv_2 where STN=:stn order by No";
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute(array("stn" => $stn));
		}
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	// 取得 http://HostIP:Port/系統路徑
	public function GetHostAndSysUrl()
	{
		return HOST_SYS_URL;
		// 若使用者是使用內部IP,也會抓取內部IP,可能導至錯誤
		/* $protocol = strpos(strtolower($_SERVER['SERVER_PROTOCOL']), 'https') === FALSE ? 'http' : 'https';
		$host = $_SERVER['HTTP_HOST'];
		$request = $_SERVER['REQUEST_URI'];
		$request = explode("/", $request);
		return $protocol . "://" . $host . "/" . $request[1]; */
	}

	// Location下拉選單
	public function SelectLocationOptions($params)
	{
		return $this->GetUserStations();
	}
	// Inverter下拉選單
	public function SelectInverterNoOptions($params)
	{
		return $this->GetStationInv($params['stn']);
	}

	/**
	 * 能源設定參數
	 * 不代以下參數則全撈:
	 * $params["item_key"] = 'E01'
	 * $params["item_label"] = '電力'
	 * 回傳形態: array
	 */
	public function SelectEnergyParam ($params = [])
	{
		$where_str = "";
		if(isset($params["item_key"]))
		{
			$where_str = sprintf(" WHERE [item_key] = '%s'", $params["item_key"]);
		}
		else if(isset($params["item_label"]))
		{
			$where_str = sprintf(" WHERE [item_label] = '%s'", $params["item_label"]);
		}
		$sql = sprintf("SELECT * FROM [%s].[dbo].[energy_parameter_settings] $where_str", $this->database_alarm);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	/**
	 * 基線參數設定
	 * 不代以下參數則全撈:
	 * $params["item_key"] = 'BL01'
	 * $params["item_label"] = '全廠用電基線'
	 * 回傳形態: array
	 */
	public function SelectBaselineParam ($params = [])
	{
		$where_str = "";
		if(isset($params["item_key"]))
		{
			$where_str = sprintf(" WHERE [item_key] = '%s'", $params["item_key"]);
		}
		else if(isset($params["item_label"]))
		{
			$where_str = sprintf(" WHERE [item_label] = '%s'", $params["item_label"]);
		}
		$sql = sprintf("SELECT * FROM [%s].[dbo].[baseline_parameter_settings] $where_str", $this->database_alarm);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	/**
	 * 用量(電力)
	 * $params["id_list"] = [1] (不代則全撈)
	 * 不代以下參數則取當天用量:
	 * $params["range"] = 'year'/'month'/'start_end_date'
	 * $params["year"] = 2024
	 * $params["month"] = '2024-05'
	 * $params["start"] = '2024-05-01'
	 * $params["end"] = '2024-05-10'
	 * 回傳形態: array
	 */
	public function SelectDosageP ($params)
	{
		$s_start_time = date("Y-m-d 00:00:00"); // 開始時間
		$s_end_time = date("Y-m-d 23:59:59"); // 結束時間

		if(isset($params["range"]))
		{
			if($params["range"] == 'year' && isset($params["year"]))
			{
				$y = $params["year"];
				$s_start_time = date("$y-01-01 00:00:00");
				$s_end_time = date("Y-m-t 23:59:59", strtotime("$y-12-01"));
			}
			else if($params["range"] == 'month' && isset($params["month"]))
			{
				$ym = $params["month"]; // 2024-05
				$s_start_time = date("$ym-01 00:00:00");
				$s_end_time = date("Y-m-t 23:59:59", strtotime("$ym-01"));
			}
			else if($params["range"] == 'start_end_date' && isset($params["start"]) && isset($params["end"]))
			{
				$s_start_time = date("Y-m-d 00:00:00", strtotime($params["start"]));
				$s_end_time = date("Y-m-d 23:59:59", strtotime($params["end"]));
			}
		}
		$s_where = "";
		if(isset($params["id_list"]))
		{
			$s_where .= sprintf(" AND [id] IN (%s)", implode(',',$params["id_list"]));
		}

		$sql = sprintf("SELECT [id] AS [ID]
												,SUM([incr_kwh]) AS [dosage]
										FROM [%s].[dbo].[R_PM_daily]
										WHERE [date_stamp] BETWEEN '$s_start_time' AND '$s_end_time' %s
										GROUP BY [id]", $this->database_alarm, $s_where);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		return $sth->fetchAll(PDO::FETCH_ASSOC);

		// $s_where_pm_1 = "";
		// $s_where_pm_2 = "";
		// if(isset($params["id_list"]))
		// {
		// 	$s_where_pm_1 = sprintf(" AND _rp.ID IN (%s) ", implode(',',$params["id_list"]));
		// 	$s_where_pm_2 = sprintf(" AND _sf.Tid IN (%s) ", implode(',',$params["id_list"]));
		// }

		// $sql = "WITH pm_data AS (
		// 					SELECT DISTINCT
		// 						ID,
		// 						(
		// 						/* 第一筆kwh */
		// 						SELECT TOP 1 
		// 							KWH 
		// 						FROM
		// 							R_PM  WITH (NOLOCK)
		// 						WHERE
		// 							ID = _rp.ID 
		// 							AND DATE_TIME BETWEEN '$s_start_time' AND '$s_end_time' 
		// 						ORDER BY
		// 							DATE_TIME ASC 
		// 						) AS MIN_KWH,
		// 						(
		// 						/* 最後一筆kwh */
		// 						SELECT TOP 1 
		// 							KWH 
		// 						FROM
		// 							R_PM  WITH (NOLOCK)
		// 						WHERE
		// 							ID = _rp.ID 
		// 							AND DATE_TIME BETWEEN '$s_start_time' AND '$s_end_time' 
		// 						ORDER BY
		// 							DATE_TIME DESC 
		// 						) AS MAX_KWH
		// 					FROM
		// 						[dbo].[R_PM] AS _rp  WITH (NOLOCK)
		// 					WHERE
		// 						_rp.DATE_TIME BETWEEN '$s_start_time' AND '$s_end_time' 
		// 						$s_where_pm_1
		// 				) 
		// 				SELECT
		// 				_sf.Tid AS ID,
		// 				ISNULL(_pd.MAX_KWH,0) - ISNULL(_pd.MIN_KWH,0) AS dosage
		// 				FROM
		// 					[dbo].[surface] AS _sf
		// 					LEFT JOIN pm_data AS _pd ON _pd.ID = _sf.Tid		
		// 				WHERE
		// 					_sf.Tclass = 'PM'
		// 					$s_where_pm_2 
		// 				ORDER BY 
		// 					_sf.Tid ASC;";
		// // return $sql;
		// $res = [];
		// if ($this->pdo_hty != null)
		// {
		// 	$sth = $this->pdo_hty->prepare($sql);
		// 	$sth->execute();
		// 	$res = $sth->fetchAll(PDO::FETCH_ASSOC);
		// }
		// return $res;
	}

	/**
	 * 用量(蒸汽、瓦斯、用水)
	 * $params["t_class"] = 'SM'/'GM'/'WM'
	 * $params["id_list"] = [1] (不代則全撈)
	 * 不代以下參數則取當天用量:
	 * $params["range"] = 'year'/'month'/'start_end_date'
	 * $params["year"] = 2024
	 * $params["month"] = '2024-05'
	 * $params["start"] = '2024-05-01'
	 * $params["end"] = '2024-05-10'
	 * 回傳形態: array
	 */
	public function SelectDosageSGW ($params) 
	{
		if(!isset($params["t_class"])) 
		{
			return false;
		}
		$s_t_class = $params["t_class"];
		// $s_tb = "R_".$s_t_class;
		$s_tb = "R_".$s_t_class."_daily";

		$s_start_time = date("Y-m-d 00:00:00"); // 開始時間
		$s_end_time = date("Y-m-d 23:59:59"); // 結束時間

		if(isset($params["range"]))
		{
			if($params["range"] == 'year' && isset($params["year"]))
			{
				$y = $params["year"];
				$s_start_time = date("$y-01-01 00:00:00");
				$s_end_time = date("Y-m-t 23:59:59", strtotime("$y-12-01"));
			}
			else if($params["range"] == 'month' && isset($params["month"]))
			{
				$ym = $params["month"]; // 2024-05
				$s_start_time = date("$ym-01 00:00:00");
				$s_end_time = date("Y-m-t 23:59:59", strtotime("$ym-01"));
			}
			else if($params["range"] == 'start_end_date' && isset($params["start"]) && isset($params["end"]))
			{
				$s_start_time = date("Y-m-d 00:00:00", strtotime($params["start"]));
				$s_end_time = date("Y-m-d 23:59:59", strtotime($params["end"]));
			}
		}
		$s_where = "";
		if(isset($params["id_list"]))
		{
			$s_where .= sprintf(" AND [id] IN (%s)", implode(',',$params["id_list"]));
		}

		$sql = sprintf("SELECT [id] AS [ID]
												,SUM([incr_acc]) AS [dosage]
										FROM [%s].[dbo].[$s_tb]
										WHERE [date_stamp] BETWEEN '$s_start_time' AND '$s_end_time' %s
										GROUP BY [id]", $this->database_alarm, $s_where);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		return $sth->fetchAll(PDO::FETCH_ASSOC);

		// $s_where_1 = "";
		// $s_where_2 = "";
		// if(isset($params["id_list"]))
		// {
		// 	$s_where_1 = sprintf(" AND _r.ID IN (%s) ", implode(',',$params["id_list"]));
		// 	$s_where_2 = sprintf(" AND _sf.Tid IN (%s) ", implode(',',$params["id_list"]));
		// }

		// $sql = "WITH r_data AS (
		// 	SELECT DISTINCT
		// 		ID,
		// 		(
		// 		/* 第一筆Acc */
		// 		SELECT TOP 1 
		// 			Acc 
		// 		FROM
		// 			$s_tb  WITH (NOLOCK)
		// 		WHERE
		// 			ID = _r.ID 
		// 			AND DATE_TIME BETWEEN '$s_start_time' AND '$s_end_time' 
		// 		ORDER BY
		// 			DATE_TIME ASC 
		// 		) AS MIN_Acc,
		// 		(
		// 		/* 最後一筆Acc */
		// 		SELECT TOP 1 
		// 			Acc 
		// 		FROM
		// 			$s_tb  WITH (NOLOCK)
		// 		WHERE
		// 			ID = _r.ID 
		// 			AND DATE_TIME BETWEEN '$s_start_time' AND '$s_end_time' 
		// 		ORDER BY
		// 			DATE_TIME DESC 
		// 		) AS MAX_Acc
		// 	FROM
		// 		[dbo].[$s_tb] AS _r  WITH (NOLOCK)
		// 	WHERE
		// 		_r.DATE_TIME BETWEEN '$s_start_time' AND '$s_end_time' 
		// 		$s_where_1
		// ) 
		// SELECT
		// _sf.Tid AS ID,
		// ISNULL(_pd.MAX_Acc,0) - ISNULL(_pd.MIN_Acc,0) AS dosage
		// FROM
		// 	[dbo].[surface] AS _sf
		// 	LEFT JOIN r_data AS _pd ON _pd.ID = _sf.Tid		
		// WHERE
		// 	_sf.Tclass = '$s_t_class'
		// 	$s_where_2 
		// ORDER BY 
		// 	_sf.Tid ASC;";
		// // return $sql;
		// $res = [];
		// if ($this->pdo_hty != null)
		// {
		// 	$sth = $this->pdo_hty->prepare($sql);
		// 	$sth->execute();
		// 	$res = $sth->fetchAll(PDO::FETCH_ASSOC);
		// }
		// return $res;
	}

	
}
