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

	public function SelectSearchData ($params) 
	{
		$range = $params['range'];
		$year = $params['year'];
		$year_month = $params['year_month'];
		
		// 生產食品對應設備
		$prod_id = [
			'牛肉乾產量'=> ['M-CAN-15'],
			'罐頭產量'=> ['M-CAN-38'], // 水浴室殺菌釜能耗指標(殺菌釜NO:3)
		];
		$start_time = date("Y-m-d 00:00:00");
		$end_time = date("Y-m-d 23:59:59");
		$group_str_0 = '';
		$group_str_1 = '';
		$group_str_2 = '';
		$date_data_1 = []; // 一期冷凍主機基線
		$date_data_2 = []; // 遠紅乾燥爐基線
		$date_data_3 = []; // 殺菌釜基線
		$date_fmt = 'Y-m-d';
		$add_key = '';
		if($range == 'month')
		{
			$start_time = date("Y-m-01 00:00:00", strtotime($year_month));
			$end_time = date("Y-m-t 23:59:59", strtotime($year_month));
			$group_str_0 = "[date_stamp]";
			$group_str_1 = "CAST ( [DATE_TIME] AS DATE )";
			$group_str_2 = "CAST ( [EndTime] AS DATE )";
			$date_fmt = 'Y-m-d';
			$add_key = 'day';
		}
		else if($range == 'year')
		{
			$start_time = date("Y-01-01 00:00:00", strtotime("$year-01"));
			$end_time = date("Y-12-31 23:59:59", strtotime("$year-01"));
			$group_str_0 = "CONVERT(varchar(7),[date_stamp],120)";
			$group_str_1 = "CONVERT(varchar(7),[DATE_TIME],120)";
			$group_str_2 = "CONVERT(varchar(7),[EndTime],120)";
			$date_fmt = 'Y-m';
			$add_key = 'month';
		}
		// 整理空資料
		for($dt = new DateTime($start_time); $dt <= new DateTime($end_time); $dt->modify("+1 $add_key"))
		{
			$data_temp = [
				"Date"=> $dt->format($date_fmt),
				"基線" => null,
				"用量" => null,
			];
			$date_data_1[] = $data_temp;
			$date_data_2[] = $data_temp;
			$date_data_3[] = $data_temp;
		}

		// 撈取-基線參數
		$res = $this->SelectBaselineParam();
		foreach($res as $row)
		{
			$bl[$row['item_key']] = $row;
		}

		// 撈取-電力用量
		$sql = sprintf("SELECT 
											[id] AS [ID],
											SUM([incr_kwh]) AS dosage,
											$group_str_0 AS [Date] 
										FROM
											[%s].[dbo].[R_PM_daily]
										WHERE
											[date_stamp] BETWEEN '$start_time' AND '$end_time' 
											AND [id] = 16
										GROUP BY
											$group_str_0,
											[id]
										ORDER BY $group_str_0 ASC", $this->database_alarm);
		$pm_data = [];
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$res = $sth->fetchAll(PDO::FETCH_ASSOC);
		foreach($res as $row)
		{
			$pm_data[$row['Date']] = $row['dosage'];
		}

		// 撈取-蒸汽用量
		$sql = sprintf("SELECT 
											[id] AS [ID],
											SUM([incr_acc]) AS dosage,
											$group_str_0 AS [Date] 
										FROM
											[%s].[dbo].[R_SM_daily]
										WHERE
											[date_stamp] BETWEEN '$start_time' AND '$end_time' 
											AND [id] IN (6,9)
										GROUP BY
											$group_str_0,
											[id]
										ORDER BY $group_str_0 ASC", $this->database_alarm);
		$sm_data = [];
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$res = $sth->fetchAll(PDO::FETCH_ASSOC);
		foreach($res as $row)
		{
			$sm_data[$row['Date']][] = $row;
		}
		// return $sql;

		// 撈取-室外溫度
		$sql = "SELECT 
							$group_str_1 AS [Date],
							AVG([P1]) AS avg_p1
						FROM 
							R_Other
						WHERE 
							DATE_TIME BETWEEN '$start_time' AND '$end_time' 
							AND [ID] = 1
						GROUP BY $group_str_1
						ORDER BY $group_str_1 ASC";
		$temper_data = [];
		if ($this->pdo_hty != null)
		{
			$sth = $this->pdo_hty->prepare($sql);
			$sth->execute();
			$res = $sth->fetchAll(PDO::FETCH_ASSOC);
			foreach($res as $row)
			{
				$temper_data[$row['Date']] = $row['avg_p1'];
			}
		}

		// 撈取-產量(肉乾、罐頭...)
		$sql = "SELECT 
							$group_str_2 AS [Date],
							RTRIM(LTRIM(ProductionID)) AS [ProductionID],
							ISNULL(SUM([Weight]),0) AS [sum_Weight]
						FROM 
							ProductionData
						WHERE 
							EndTime BETWEEN '$start_time' AND '$end_time' 
						GROUP BY 
							$group_str_2,
							RTRIM(LTRIM(ProductionID))
						ORDER BY 
							$group_str_2 ASC";
		$prod_data = [];
		$prod_data_temp = [];
		if ($this->pdo_ems != null)
		{
			$sth = $this->pdo_ems->prepare($sql);
			$sth->execute();
			$res = $sth->fetchAll(PDO::FETCH_ASSOC);
			// 以Date為key做組合
			foreach($res as $row)
			{
				$prod_data_temp[$row['Date']][] = $row;
			}
			foreach($prod_data_temp as $d => $row)
			{
				// 建立初始資料，產量為0
				$prod_data[$d] = [];
				foreach($prod_id as $k => $pid)
				{
					$prod_data[$d][$k] = 0;
				}
				foreach($row as $r)
				{
					foreach($prod_id as $k => $pid)
					{
						if(in_array($r['ProductionID'],$pid) === true)
						{
							$prod_data[$d][$k] += floatval($r['sum_Weight']);
						}
					}
				}
			}
		}

		// 將撈出資料計算寫入-一期冷凍主機基線
		foreach($date_data_1 as &$dd)
		{
			$temp_val = isset($temper_data[$dd['Date']]) ? floatval($temper_data[$dd['Date']]) : 0; // 室外溫度
			// 單項用電基線計算公式 = 截距 + 係數 * 室外溫度
			$dd['基線'] = floatval($bl['BL02']['intercept']) + 
										floatval($bl['BL02']['coef']) * 
										$temp_val;
			$dd['用量'] = isset($pm_data[$dd['Date']]) ? $pm_data[$dd['Date']] : 0; // 用電MCC-16
		}
		// 將撈出資料計算寫入-遠紅乾燥爐基線
		foreach($date_data_2 as &$dd)
		{
			$output_qty = isset($prod_data[$dd['Date']]['牛肉乾產量']) ? floatval($prod_data[$dd['Date']]['牛肉乾產量']) : 0; // 牛肉乾產量
			$sm_6 = array_filter($sm_data[$dd['Date']], function($r) {
				return $r['ID'] == 6;
			});
			// 單項用電基線計算公式 = 截距 + 係數 * 牛肉乾產量
			$dd['基線'] = floatval($bl['BL03']['intercept']) + 
										floatval($bl['BL03']['coef']) * 
										$output_qty;
			$dd['用量'] = isset($sm_6[0]['dosage']) ? $sm_6[0]['dosage'] : 0; // 蒸汽-遠紅-6
		}
		// 將撈出資料計算寫入-殺菌釜基線
		foreach($date_data_3 as &$dd)
		{
			$output_qty = isset($prod_data[$dd['Date']]['罐頭產量']) ? floatval($prod_data[$dd['Date']]['罐頭產量']) : 0; // 罐頭產量
			$sm_9 = array_filter($sm_data[$dd['Date']], function($r) {
				return $r['ID'] == 9;
			});
			// 單項用電基線計算公式 = 截距 + 係數 * 罐頭產量
			$dd['基線'] = floatval($bl['BL04']['intercept']) + 
										floatval($bl['BL04']['coef']) * 
										$output_qty;
			$dd['用量'] = isset($sm_9[0]['dosage']) ? $sm_9[0]['dosage'] : 0; // 蒸汽-殺菌釜NO3-9
		}

		return compact('date_data_1','date_data_2','date_data_3');
	}
	
}
?>