<?php

// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	public function SelectLoadData($params)
	{

		//時間條件
		$s_start_time = date("Y-m-d 00:00:00");

		//機台當月關機時間撈取
		$sql = sprintf(
			"SELECT
				_ml.machine_id,
				SUM (
					CASE				
							WHEN _vmsd.sta_status IN (0,1,3,4)  THEN _vmsd.total_duration_seconds ELSE 0
						END 
						)  AS total_duration_seconds,
				_ml.type
			FROM
				[%s].[dbo].[machine_list] AS _ml
				LEFT JOIN [%s].[dbo].[vmx_machine_state_daily] AS _vmsd ON _vmsd.vmx_machine_id = _ml.vmx_machine_id
			WHERE
				_ml.is_itri = 1 AND
				_vmsd.Time_Stamp BETWEEN :start_at AND :end_at
			GROUP BY
				_ml.machine_id,
				_ml.type
		",
			$this->database_alarm,
			$this->database_alarm
		);

		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute(
			[
				"start_at" => date("Y-m-01"),
				"end_at" => date("Y-m-d")
			]
		);
		$machine_unwork_rows = $sth->fetchAll(PDO::FETCH_ASSOC);





		$sql = sprintf(
			"SELECT 
				_ml.machine_id
				,_ml.name AS machine_name
				,_ml.is_itri
				,_ml.type AS machine_type
				,v_cmstct.cnc_dispatch_detail_id
				,v_cmstct.start_time
				,v_cmstct.dispatch_qty
				,ISNULL(v_cmstct.sequence_index,1) AS sequence_index
				,ISNULL(v_cmstct.total_seconds_status_0,0) AS total_seconds_status_0
				,ISNULL(v_cmstct.total_seconds_status_1,0) AS total_seconds_status_1
				,ISNULL(v_cmstct.total_seconds_status_3,0) AS total_seconds_status_3
				,ISNULL(v_cmstct.total_seconds_status_4,0) AS total_seconds_status_4
				,ISNULL(v_cmstct.total_second_working_time,0) AS total_second_working_time
				,v_cmwc.working_qty_count
				,v_woppd.wo_qty
				,v_woppd.work_order_sno
				,v_woppd.work_order_product_id
				,v_wofcu.real_qty_total AS work_order_real_qty_total
				,v_cms.sta_status
			FROM
				[%s].[dbo].[machine_list] AS _ml
				LEFT JOIN [%s].[dbo].[v_cnc_machine_state_time_count_today] AS v_cmstct ON v_cmstct.machine_id = _ml.machine_id
				LEFT JOIN [%s].[dbo].[v_work_order_for_cnc_undone] AS v_wofcu ON v_wofcu.work_order_product_process_id = v_cmstct.work_order_product_process_id
				LEFT JOIN [%s].[dbo].[v_work_order_product_process_detail] AS v_woppd ON v_woppd.work_order_product_process_id = v_cmstct.work_order_product_process_id
				LEFT JOIN [%s].[dbo].[v_cnc_machine_work_count] AS v_cmwc ON v_cmwc.machine_id = _ml.machine_id 
				LEFT JOIN [%s].[dbo].[v_cnc_machine_state] AS v_cms ON v_cms.machine_id = _ml.machine_id 
			",
			$this->database_alarm,
			$this->database_alarm,
			$this->database_alarm,
			$this->database_alarm,
			$this->database_alarm,
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);

		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		$res = [
			"work_order_wo_qty_list" => [],
			"work_order_wo_qty_total" => 0,
			"work_order_real_qty_total" => 0,
			"total_onwork_time" => 0,
			"total_unwork_time" => 0,
			"total_unwork_time_month" => 0,
			"table" => [],
		];
		foreach ($rows as $k => $v) {
			$v["start_time"] = empty($v["start_time"]) ? "" : date("Y-m-d H:i:s", strtotime($v["start_time"]));

			//工單總預計產量計算預處理 (以工單角度切出每筆工單的預計產量)
			if (!empty($v["work_order_product_id"]) && empty($res["work_order_wo_qty_list"][$v["work_order_product_id"]])) {
				$res["work_order_wo_qty_list"][$v["work_order_product_id"]] = [
					"wo_qty" => $v["wo_qty"],
					"real_qty" => $v["work_order_real_qty_total"]
				];
			}
			//表單資料
			if ($v["sequence_index"] == 1) {
				$res["table"][] = $v;
			}
			//總工作中工時統計
			$res["total_onwork_time"] =
				$v["total_second_working_time"] +
				$res["total_onwork_time"];
			//總非工作中工時統計(工研院機台狀態為這四種時 0: 關機/斷線 => 灰色 1: 待機 => 黃色 3: 停止 => 黃色 4: 警報 => 紅)
			$res["total_unwork_time"] =
				$v["total_seconds_status_0"] +
				$v["total_seconds_status_1"] +
				$v["total_seconds_status_3"] +
				$v["total_seconds_status_4"] +
				$res["total_unwork_time"];
		}

		//當月關機總時間整理
		foreach ($machine_unwork_rows as $k => $v) {
			$res["total_unwork_time_month"] += $v["total_duration_seconds"];
		}
		//資料整理
		$res["total_unwork_time_month"] = (float) number_format(($res["total_unwork_time_month"] / 60 / 60), 2, ".", "");
		//稼動率計算
		$work_availability_second = (($res["total_onwork_time"] - $res["total_unwork_time"]) / $res["total_onwork_time"]) * 100;
		$res["work_availability"] = (float) number_format($work_availability_second, 2, ".", "");
		foreach ($res["work_order_wo_qty_list"] as $k => $v) {
			$res["work_order_wo_qty_total"] = (float) bcadd($res["work_order_wo_qty_total"], $v["wo_qty"], 2);
			$res["work_order_real_qty_total"] = (float) bcadd($res["work_order_real_qty_total"], $v["real_qty"], 2);
		}
		//表單資料依據機台編號排序
		if (!empty($res["table"])) {
			usort($res["table"], function ($a, $b) {
				return $a['machine_id'] <=> $b['machine_id'];
			});
		}

		return $res;
	}
}
