<?php
// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	/**
	 * Leo
	 * 取得首頁用門市資料
	 * @param start_date, end_date, store
	 * @return list[]
	 */
	public function SelectIndexInfo($params)
	{

		//先查出門市列表
		$a_where_arr = []; //sql條件陣列 
		$a_where_arr_val = [];
		$s_where_arr = ""; //sql條件字串，用$a_where_arr條件陣列轉換而來

		//不顯示尚未綁定機台之門市
		$a_where_arr[] = "_c.cashier_id IS NOT NULL";


		// 門市判斷條件
		if ($params['store'] != 0) {
			$a_where_arr[] = "_s.store_id = :store";
			$a_where_arr_val["store"] = $params['store'];
		}
		// 區域條件判斷條件
		if (in_array($params['area'], [1, 2, 3])) {
			$a_where_arr[] = "_s.store_area = :store_area";
			$a_where_arr_val["store_area"] = $params['area'];
		}

		//門市查詢控制項 store_ctrl 0:只顯示營運中門市 1:顯示全門市
		if ($this->store_ctrl == 0) {
			$a_where_arr[] = " _s.store_state = 1 ";
		}

		//sql條件陣列轉換
		if (!empty($a_where_arr)) {
			$s_where_arr = " WHERE " . implode(" AND ", $a_where_arr);
		}

		//拼接sql字串
		$sql = sprintf(
			"WITH last_received_at AS (SELECT
				store_id,
				MAX (received_at) AS max_received_at
			FROM
				[%s].[dbo].[store_received_ledger]
			GROUP BY
				store_id
			)SELECT
				_s.store_id,
				_s.store_otp,
				_s.store_name,
				_s.store_open_at,
				_s.store_area,
				_c.cashier_name,
				_s.petty_cash_500,
				_s.petty_cash_100,
				_s.petty_cash_50,
				_s.petty_cash_10,
				_s.petty_cash_5,
				_s.petty_cash_1,
				_s.cash_warning_level,
				_s.cash_stop_level,
				_s.coin_low_warning_water_level,
				_s.coin_low_stop_water_level,
				_lra.max_received_at,
				v_sls.statistics_at,
				v_sls.last_cycle_box_500,
				v_sls.last_cycle_box_100,
				v_sls.last_cycle_box_50,
				v_sls.last_cycle_box_10,
				v_sls.last_cycle_box_5,
				v_sls.last_cycle_box_1,
				v_sls.last_safe_box_1000,
				v_sls.last_safe_box_500,
				v_sls.last_safe_box_100,
				v_sls.last_safe_box_50,
				v_sls.last_safe_box_10,
				v_sls.last_safe_box_5,
				v_sls.last_safe_box_1,
				v_sls.sub_total_1000_in,
				v_sls.sub_total_500_in,
				v_sls.sub_total_100_in,
				v_sls.sub_total_500_out,
				v_sls.sub_total_100_out,
				v_sts.cash_1000_in,
				v_sts.cash_500_in,
				v_sts.cash_100_in,
				v_sts.cash_50_in,
				v_sts.cash_10_in,
				v_sts.cash_5_in,
				v_sts.cash_1_in,
				v_sts.cash_500_out,
				v_sts.cash_100_out,
				v_sts.cash_50_out,
				v_sts.cash_10_out,
				v_sts.cash_5_out,
				v_sts.cash_1_out,
				v_sts.safe_box_1000,
				v_sts.safe_box_500,
				v_sts.safe_box_100,
				v_sts.safe_box_50,
				v_sts.safe_box_10,
				v_sts.safe_box_5,
				v_sts.safe_box_1,
				v_sts.cycle_box_500,
				v_sts.cycle_box_100,
				v_sts.cycle_box_50,
				v_sts.cycle_box_10,
				v_sts.cycle_box_5,
				v_sts.cycle_box_1,
				v_sts.sub_box_1000_in,
				v_sts.sub_box_500_in,
				v_sts.sub_box_100_in,
				v_sts.sub_box_500_out,
				v_sts.sub_box_100_out
			FROM 
				[%s].[dbo].[store] AS _s		
				LEFT JOIN [%s].[dbo].[cashier] AS _c ON _c.store_id = _s.store_id
				LEFT JOIN [%s].[dbo].[v_store_yesterday_currency_statement] AS v_sls ON v_sls.store_id = _s.store_id
				LEFT JOIN [%s].[dbo].[v_store_today_statistics_data] AS v_sts ON v_sts.store_id = _s.store_id
				LEFT JOIN last_received_at AS _lra ON _lra.store_id = _s.store_id
				%s
			",
			$this->database_trend,
			$this->database_trend,
			$this->database_trend,
			$this->database_trend,
			$this->database_trend,
			$s_where_arr
		);
		//準備執行sql
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute($a_where_arr_val);
		$row = $sth->fetchAll(PDO::FETCH_ASSOC);
		//整理回傳資料
		$a_store_data = [];
		$a_store_petty_low_check = []; //各門市低水位檢查用陣列
		foreach ($row as $k => &$v) {
			//循環箱各面額數量計算 最後一次日結 + (當日循環箱入鈔 - 當日循環箱出鈔) + 當日循環箱換補操作
			$n_cycle_500 = $v["last_cycle_box_500"] + ($v["cash_500_in"] - $v["cash_500_out"]) + $v["cycle_box_500"];
			$n_cycle_100 = $v["last_cycle_box_100"] + ($v["cash_100_in"] - $v["cash_100_out"]) + $v["cycle_box_100"];
			$n_cycle_50 = $v["last_cycle_box_50"] + ($v["cash_50_in"] - $v["cash_50_out"]) + $v["cycle_box_50"];
			$n_cycle_10 = $v["last_cycle_box_10"] + ($v["cash_10_in"] - $v["cash_10_out"]) + $v["cycle_box_10"];
			$n_cycle_5 = $v["last_cycle_box_5"] + ($v["cash_5_in"] - $v["cash_5_out"]) + $v["cycle_box_5"];
			$n_cycle_1 = $v["last_cycle_box_1"] + ($v["cash_1_in"] - $v["cash_1_out"]) + $v["cycle_box_1"];
			$n_sub_out_box_500 = $v["sub_box_500_out"] + $v["sub_total_500_out"];
			$n_sub_out_box_100 = $v["sub_box_100_out"] + $v["sub_total_100_out"];
			$n_sub_in_box_1000 = $v["sub_total_1000_in"] + $v["sub_box_1000_in"];
			$n_sub_in_box_500 = $v["sub_total_500_in"] + $v["sub_box_500_in"];
			$n_sub_in_box_100 = $v["sub_total_100_in"] + $v["sub_box_100_in"];
			//總零用金
			$n_total_cycle_amount =
				bcmul($n_sub_out_box_500, 500, 0) +
				bcmul($n_sub_out_box_100, 100, 0) +
				bcmul($n_cycle_500, 500, 0) +
				bcmul($n_cycle_100, 100, 0) +
				bcmul($n_cycle_50, 50, 0) +
				bcmul($n_cycle_10, 10, 0) +
				bcmul($n_cycle_5, 5, 0) +
				bcmul($n_cycle_1, 1, 0);
			//計算當前機台總金額
			$n_total_cashier_amount =
				bcmul($v["last_safe_box_1000"], 1000, 0) +
				bcmul($v["last_safe_box_500"], 500, 0) +
				bcmul($v["last_safe_box_100"], 100, 0) +
				bcmul($v["cash_1000_in"], 1000, 0) +
				bcmul($v["safe_box_1000"], 1000, 0) +
				bcmul($v["safe_box_500"], 500, 0) +
				bcmul($v["safe_box_100"], 100, 0) +
				bcmul($n_sub_in_box_1000, 1000, 0) +
				bcmul($n_sub_in_box_500, 500, 0) +
				bcmul($n_sub_in_box_100, 100, 0) +
				$n_total_cycle_amount;
			//計算保全箱內的鈔票總數
			$n_safe_cash_box =
				$v["last_safe_box_1000"] +
				$v["last_safe_box_500"] +
				$v["last_safe_box_100"] +
				$v["cash_1000_in"] +
				$v["safe_box_1000"] +
				$v["safe_box_500"] +
				$v["safe_box_100"];
			//計算溢幣區硬幣總數
			$n_safe_coin_box =
				$v["last_safe_box_50"] +
				$v["last_safe_box_10"] +
				$v["last_safe_box_5"] +
				$v["last_safe_box_1"] +
				$v["safe_box_50"] +
				$v["safe_box_10"] +
				$v["safe_box_5"] +
				$v["safe_box_1"];
			//計算sub發鈔機數量
			$n_sub_out_box =
				$v["sub_box_500_out"] +
				$v["sub_box_100_out"] +
				$v["sub_total_500_out"] +
				$v["sub_total_100_out"];
			//計算sub入鈔機數量
			$n_sub_in_box =
				$v["sub_box_1000_in"] +
				$v["sub_box_500_in"] +
				$v["sub_box_100_in"] +
				$v["sub_total_1000_in"] +
				$v["sub_total_500_in"] +
				$v["sub_total_100_in"];
			//距離最後一次換補天數到今日 總天數計算 
			// 假設最後一次換補是在開業日之前 則計算方式用開業日計算
			// 若最後一次換補是在開業日之後 則需判斷換補時間是否大於開業日期
			// 當換補時間大於開業日期 則以換補時間來計算
			$d_replenish_date = 0;
			//當沒有換補紀錄 且門市已開業 則以門市開業日期開始計算未換補天數
			if (empty($v["max_received_at"])) {
				if ($v["store_open_at"] <= date("Y-m-d")) {
					$d_replenish_date = round((strtotime(date("Ymd")) - strtotime($v["store_open_at"])) / 3600 / 24);
				}
			}
			//當有換補紀錄時 需判斷是否開業 並分別處理
			else {
				//門市尚未開業
				if ($v["store_open_at"] > date("Y-m-d")) {
					$d_replenish_date = round((strtotime(date("Ymd")) - strtotime($v["max_received_at"])) / 3600 / 24);
				} else {
					//當最後一次換補時間大於等於開業日期 
					if ($v["max_received_at"] >= $v["store_open_at"]) {
						$d_replenish_date = round((strtotime(date("Ymd")) - strtotime($v["max_received_at"])) / 3600 / 24);
					} else {
						$d_replenish_date = round((strtotime(date("Ymd")) - strtotime($v["store_open_at"])) / 3600 / 24);
					}
				}
			}
			$s_store_area = "";
			switch ($v["store_area"]) {
				case '1':
					$s_store_area = "北部";
					break;
				case '2':
					$s_store_area = "中部";
					break;
				case '3':
					$s_store_area = "南部";
					break;
				default:
					$s_store_area = "未知區域";
					break;
			}
			$arr = [
				"store_id" => $v["store_id"],
				"store_name" => $v["store_name"],
				"store_area" => $s_store_area,
				"store_open_at" => $v["store_open_at"],
				"cashier_name" => $v["cashier_name"],
				"store_otp" => $v["store_otp"],
				"replenish_date" => $d_replenish_date,
				"cycle_500" => $n_cycle_500,
				"cycle_100" => $n_cycle_100,
				"cycle_50" => $n_cycle_50,
				"cycle_10" => $n_cycle_10,
				"cycle_5" => $n_cycle_5,
				"cycle_1" => $n_cycle_1,
				"safe_cash_box" => $n_safe_cash_box,
				"safe_coin_box" => $n_safe_coin_box,
				"sub_out_box" => $n_sub_out_box,
				"sub_in_box" => $n_sub_in_box,
				"total_cycle_amount" => $n_total_cycle_amount,
				"total_cashier_amount" => $n_total_cashier_amount,
			];
			$a_store_data[] = $arr;
			//低水位檢查判斷用
			//低水位(警告)
			$a_store_petty_low_check[$v["store_id"]]["warning"] = [
				"500" => (int)($v["petty_cash_500"] * $v["cash_warning_level"] / 100),
				"100" => (int)($v["petty_cash_100"] * $v["cash_warning_level"] / 100),
				"50" => (int)($v["petty_cash_50"] * $v["coin_low_warning_water_level"] / 100),
				"10" => (int)($v["petty_cash_10"] * $v["coin_low_warning_water_level"] / 100),
				"5" => (int)($v["petty_cash_5"] * $v["coin_low_warning_water_level"] / 100),
				"1" => (int)($v["petty_cash_1"] * $v["coin_low_warning_water_level"] / 100),
			];
			//低水位(停止)
			$a_store_petty_low_check[$v["store_id"]]["stop"] = [
				"500" => (int)($v["petty_cash_500"] * $v["cash_stop_level"] / 100),
				"100" => (int)($v["petty_cash_100"] * $v["cash_stop_level"] / 100),
				"50" => (int)($v["petty_cash_50"] * $v["coin_low_stop_water_level"] / 100),
				"10" => (int)($v["petty_cash_10"] * $v["coin_low_stop_water_level"] / 100),
				"5" => (int)($v["petty_cash_5"] * $v["coin_low_stop_water_level"] / 100),
				"1" => (int)($v["petty_cash_1"] * $v["coin_low_stop_water_level"] / 100),
			];
		}

		//根據門市取得該門市最後一筆異常紀錄 (需加入延遲時間判斷)
		$s_log_delay_time = date("Y-m-d H:i:s", strtotime("-" . Delay_check_alarm . " seconds"));
		foreach ($a_store_data as $k => $v) {
			$temp_store_id = $v["store_id"];
			$sql = sprintf(
				"WITH alarm_reset_check AS (
					SELECT count
						( 1 ) AS is_reset,
						%s AS store_id
					FROM
						[%s].[dbo].[store_alarm_report]
					WHERE
						alarm_code = 0 
						AND create_at BETWEEN :reset_start AND :reset_end 
						AND store_id = :reset_store
				)SELECT TOP	1 
					_sar.alarm_code,
					_sar.alarm_name,
					_sar.create_at,
					_sar.alarm_stop,
					_sar.system_handle,
					_arc.is_reset
				FROM
					[%s].[dbo].[store] AS _s
					LEFT JOIN [%s].[dbo].[cashier] AS _c ON _c.store_id = _s.store_id
					LEFT JOIN [%s].[dbo].[store_alarm_report] AS _sar ON _sar.store_id = _s.store_id AND _sar.cashier_id = _c.cashier_id
					LEFT JOIN alarm_reset_check AS _arc ON _arc.store_id = _sar.store_id
				WHERE 
					_sar.store_id = :store_id
					AND (_sar.alarm_code = 2 OR _sar.create_at <= '%s')
				ORDER BY
					_sar.store_alarm_id DESC",
				$temp_store_id,
				$this->database_alarm,
				$this->database_trend,
				$this->database_trend,
				$this->database_alarm,
				$s_log_delay_time

			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				"store_id" => $temp_store_id,
				"reset_start" => $s_log_delay_time,
				"reset_end" => date("Y-m-d H:i:s"),
				"reset_store" => $temp_store_id,
			]);
			$row = $sth->fetch(PDO::FETCH_ASSOC);

			$s_alarm_code = "";
			$s_alarm_name = "";

			$s_cashier_state = $v["store_open_at"] > date("Y-m-d") ? "未上線" : "";

			if (
				!empty($row) &&
				!in_array($row["alarm_code"], [0, 9, 10, 11]) &&
				($row["is_reset"] == 0 || $row["alarm_code"] == 2)
			) {
				if ($row["alarm_code"] == 2) {
					//當異常代碼為2時 需查看system_handle 內的資料紀錄 看是哪一種面額發出低水位警報 並判斷當前機台是否已經賦歸(硬幣類型有可能因為客人投入而回復水位)
					$petty_err_data = json_decode($row["system_handle"], true);
					//該面額設定的低水位數量
					$n_set_low_count = $a_store_petty_low_check[$temp_store_id][$petty_err_data["type"]][$petty_err_data["val"]];
					//該面額當前數量
					$n_now_count = 0;
					switch ($petty_err_data["val"]) {
						case '500':
							$n_now_count = $v["cycle_500"];
							break;
						case '100':
							$n_now_count = $v["cycle_100"];
							break;
						case '50':
							$n_now_count = $v["cycle_50"];
							break;
						case '10':
							$n_now_count = $v["cycle_10"];
							break;
						case '5':
							$n_now_count = $v["cycle_5"];
							break;
						case '1':
							$n_now_count = $v["cycle_1"];
							break;
					}
					if ($n_now_count <= $n_set_low_count) {
						$s_alarm_code = $row["alarm_code"];
						$s_alarm_name = $row["alarm_name"];
						switch ($row["alarm_stop"]) {
							case 0:	//警報 機台發報但不停機
								$s_cashier_state = "警報";
								break;
							case 1:	//異常 機台發報且停機
								$s_cashier_state = "異常";
								break;
						}
					}
				} else {
					$s_alarm_code = $row["alarm_code"];
					$s_alarm_name = $row["alarm_name"];
					switch ($row["alarm_stop"]) {
						case 0:	//警報 機台發報但不停機
							$s_cashier_state = "警報";
							break;
						case 1:	//異常 機台發報且停機
							$s_cashier_state = "異常";
							break;
					}
				}
			}
			//假設沒有異常代碼，在判斷是否有超過五天未換補，超過的話顯示異常訊息
			if ($s_alarm_code == "") {
				if ($v["replenish_date"] >= 5) {
					$s_alarm_code = 3;
					$s_alarm_name = "超過" . $v["replenish_date"] . "天未收款/補鈔";
					$s_cashier_state = "警報";
				}
			}
			$a_store_data[$k]["store_alarm_code"] = $s_alarm_code;
			$a_store_data[$k]["store_alarm_name"] = $s_alarm_name;
			$a_store_data[$k]["cashier_state"] = $s_cashier_state;
		}

		return $a_store_data;
	}
}
