<?php

// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	//	機台options
	public function SelectLoadMachineOptions()
	{
		$sql = sprintf(
			"SELECT 
				_ml.machine_id, 
				_ml.name AS machine_name
			FROM 
				[%s].[dbo].[machine_list] AS _ml
			ORDER BY
				_ml.machine_id ASC",
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		return $rows;
	}

	//	趨勢圖
	public function SelectTrendData($params)
	{
		//查詢時間範圍
		$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_machinesArray = (array) $params["machinesArray"];

		if (
			$s_start_at == "" ||
			$s_end_at == ""
		) {
			throw new Exception("時間區間異常");
		} else {
			$s_start_at = date("Y-m-d 00:00:00", strtotime($s_start_at));
			$s_end_at = date("Y-m-d 23:59:59", strtotime($s_end_at));
		}

		$s_machine_list = "";
		if (!empty($a_machinesArray)) {
			foreach ($a_machinesArray as $k => $v) {
				$a_machinesArray[$k] = "'$v'";
			}
			$s_machine_list = implode(",", $a_machinesArray);
		} else {
			throw new Exception("請必選一個設備後查詢");
		}

		// throw new Exception("請必選一個設備後查詢");

		$sql = sprintf(
			"WITH cnc_dispatch_detail_count AS (
				SELECT 
					SUM	( _cdd.real_qty ) AS real_qty_total,
					_cdd.cnc_dispatch_id,
					CAST ( _cdd.end_time AS DATE ) AS end_date,
					SUM ( DATEDIFF( second, _cdd.start_time, _cdd.end_time ) ) AS work_time 
				FROM
					[%s].[dbo].[cnc_dispatch_detail] AS _cdd 
				WHERE
					_cdd.deleted_at IS NULL 
					AND _cdd.end_time BETWEEN :start_at	AND :end_at 
				GROUP BY
					_cdd.cnc_dispatch_id,
					CAST ( _cdd.end_time AS DATE ) 
				),
				cnc_dispatch_filter AS (
				SELECT
					SUM(_cddc.real_qty_total) AS real_qty_total,
					_cddc.end_date,
					SUM(_cddc.work_time) AS work_time,
					_cd.machine_id
				FROM
					cnc_dispatch_detail_count AS _cddc
					LEFT JOIN [%s].[dbo].[cnc_dispatch] AS _cd ON _cd.id = _cddc.cnc_dispatch_id 
				WHERE
					_cd.machine_id IN ( %s ) 
				GROUP BY
					_cd.machine_id,
					_cddc.end_date
				)SELECT
					_cdf.real_qty_total AS machine_output,
					_cdf.end_date AS work_date,
					_cdf.work_time,
					_cdf.machine_id,
					_ml.name AS machine_name
				FROM
					cnc_dispatch_filter AS _cdf
					LEFT JOIN [%s].[dbo].[machine_list] AS _ml ON _ml.machine_id = _cdf.machine_id",
			$this->database_alarm,
			$this->database_alarm,
			$s_machine_list,
			$this->database_alarm
		);
		// return $sql;
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			"start_at" => $s_start_at,
			"end_at" => $s_end_at,
		]);
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		$res = [];
		foreach ($rows as $k => $v) {
			$v["ute"] = (float)number_format($v["work_time"] / 60 / 60 / 8 * 100, 2, ".", "");
			$res[] = $v;
		}
		return $res;
	}
}
