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

	public function SelectSearchData ($params) 
	{
		$range = $params['range'];
		$year = $params['year'];
		$year_month = $params['year_month'];
		
		// 生產食品對應設備(參考文件:設備能耗績效指標建立-0826修改說明.xlsx)
		$prod_id = [
			'肉乾產量'=> ['M-CAN-15','M-SMK-66'],
			'罐頭產量'=> ['M-CAN-08','M-CAN-09','M-CAN-38','M-CAN-10'],
			'鳳梨酥產量'=> ['C-BAK-22','C-BAK-01'],
			'肉鬆產量'=> [
				'M-COK-01',
				'M-COK-02',
				'M-COK-03',
				'M-COK-04',
				'M-COK-05',
				'M-COK-06',
				'M-COK-07',
				'M-COK-08',
				'M-COK-09',
				'M-COK-10',
				'M-COK-11',
				'M-COK-12',
				'M-COK-13',
				'M-COK-14',
				'M-COK-15',
				'M-COK-16',
				'M-COK-17'
			],
		];
		$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 = [];
		$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,
				"MCB" => null,
				"肉乾產量" => null,
				"罐頭產量" => null,
				"鳳梨酥產量" => null,
				"肉鬆產量" => null,
			];
			$date_data[] = $data_temp;
		}

		// 撈取-基線參數
		$bl = $this->SelectBaselineParam(['item_label'=> '全廠用電基線'])[0];

		// 撈取-電力用量
		$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] = 1
										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 = "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] = [
					'成品產量'=> 0,
					// '肉乾產量'=> 0,
					// '罐頭產量'=> 0,
					// '鳳梨酥產量'=> 0,
					// '肉鬆產量'=> 0,
				];
				foreach($prod_id as $k => $pid)
				{
					$prod_data[$d][$k] = 0;
				}
				foreach($row as $r)
				{
					if($r['ProductionID'] == 'F-25')
					{
						$prod_data[$d]['成品產量'] += floatval($r['sum_Weight']);
					}
					foreach($prod_id as $k => $pid)
					{
						if(in_array($r['ProductionID'],$pid) === true)
						{
							$prod_data[$d][$k] += floatval($r['sum_Weight']);
						}
					}
				}
			}
		}

		// 將撈出資料計算寫入
		foreach($date_data as &$dd)
		{
			$total_qty = isset($prod_data[$dd['Date']]['成品產量']) ? floatval($prod_data[$dd['Date']]['成品產量']) : 0; // 成品產量
			$temp_val = isset($temper_data[$dd['Date']]) ? floatval($temper_data[$dd['Date']]) : 0; // 室外溫度
			// 全廠用電基線計算公式 = 截距 + 產品係數 * 成品產量 + 溫度係數 * 室外溫度
			$dd['基線'] = floatval($bl['intercept']) + 
										floatval($bl['product_coef']) * 
										$total_qty + 
										floatval($bl['temperature_coef']) * 
										$temp_val;
			$dd['MCB'] = isset($pm_data[$dd['Date']]) ? $pm_data[$dd['Date']] : 0;
			$dd['肉乾產量'] = isset($prod_data[$dd['Date']]['肉乾產量']) ? $prod_data[$dd['Date']]['肉乾產量'] : 0;
			$dd['罐頭產量'] = isset($prod_data[$dd['Date']]['罐頭產量']) ? $prod_data[$dd['Date']]['罐頭產量'] : 0;
			$dd['鳳梨酥產量'] = isset($prod_data[$dd['Date']]['鳳梨酥產量']) ? $prod_data[$dd['Date']]['鳳梨酥產量'] : 0;
			$dd['肉鬆產量'] = isset($prod_data[$dd['Date']]['肉鬆產量']) ? $prod_data[$dd['Date']]['肉鬆產量'] : 0;
		}

		return $date_data;
	}
	
}
?>