<?php
// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	/**
	 * Leo
	 * 取得門市貨幣異動明細
	 * @param start_date, end_date, store
	 * @return list[]
	 */
	public function SelectCurrencyDetails($params)
	{
		//門市id
		$s_store_id = (int) $params["store"];
		//資料起始時間
		$s_start_date = (string) $params["start_date"];
		$s_start_date = trim($s_start_date);
		$s_start_date = date("y-m-d H:i:00", strtotime($s_start_date));
		//資料結束時間
		$s_end_date = (string) $params["end_date"];
		$s_end_date = trim($s_end_date);
		$s_end_date = date("y-m-d H:i:59", strtotime($s_end_date));



		// 門市判斷條件
		if ($params['store'] != 0) {
			$a_where_arr[] = "_s.store_id = :store";
			$a_where_arr_val["store"] = $s_store_id;
		}

		//門市查詢控制項 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);
		}
		$a_where_arr_val["t_start_at"] = $s_start_date;
		$a_where_arr_val["t_end_at"] = $s_end_date;
		$a_where_arr_val["c_start_at"] = $s_start_date;
		$a_where_arr_val["c_end_at"] = $s_end_date;

		//拼接sql字串
		$sql = sprintf(
			"WITH online_store AS (
				SELECT
					_s.store_id,
					_s.store_name 
				FROM
					[%s].[dbo].[store] AS _s 
				%s
				),
				store_currency_data AS (
				SELECT
					_sts.store_id,
					_sts.transaction_end_at AS data_create_at,
					_sts.cash_1000_in AS safe_1000,
					0 AS safe_500,
					0 AS safe_100,
					0 AS safe_50,
					0 AS safe_10,
					0 AS safe_5,
					0 AS safe_1,
					_sts.cash_500_in - _sts.cash_500_out AS cycle_500,
					_sts.cash_100_in - _sts.cash_100_out AS cycle_100,
					_sts.cash_50_in - _sts.cash_50_out AS cycle_50,
					_sts.cash_10_in - _sts.cash_10_out AS cycle_10,
					_sts.cash_5_in - _sts.cash_5_out AS cycle_5,
					_sts.cash_1_in - _sts.cash_1_out AS cycle_1,
					99 AS operate_type,
					_sts.sub_1000_in AS sub_in_1000,
					_sts.sub_500_in AS sub_in_500,
					_sts.sub_100_in AS sub_in_100,
					_sts.sub_500_out *- 1 AS sub_out_500,
					_sts.sub_100_out *- 1 AS sub_out_100 
				FROM
					online_store AS _os
					LEFT JOIN [%s].[dbo].[store_transaction_statement] AS _sts ON _sts.store_id = _os.store_id 
				WHERE
					_sts.transaction_end_at BETWEEN :t_start_at AND :t_end_at UNION ALL
				SELECT
					_ccs.store_id,
					_ccs.create_at AS data_create_at,
					_ccs.safe_box_1000 AS safe_1000,
					_ccs.safe_box_500 AS safe_500,
					_ccs.safe_box_100 AS safe_100,
					_ccs.safe_box_50 AS safe_50,
					_ccs.safe_box_10 AS safe_10,
					_ccs.safe_box_5 AS safe_5,
					_ccs.safe_box_1 AS safe_1,
					_ccs.cycle_box_500 AS cycle_500,
					_ccs.cycle_box_100 AS cycle_100,
					_ccs.cycle_box_50 AS cycle_50,
					_ccs.cycle_box_10 AS cycle_10,
					_ccs.cycle_box_5 AS cycle_5,
					_ccs.cycle_box_1 AS cycle_1,
					_ccs.operate_type,
					_ccs.sub_1000_in AS sub_in_1000,
					_ccs.sub_500_in AS sub_in_500,
					_ccs.sub_100_in AS sub_in_100,
					_ccs.sub_500_out AS sub_out_500,
					_ccs.sub_100_out AS sub_out_100 
				FROM
					online_store AS _os
					LEFT JOIN [%s].[dbo].[cashier_currency_statement] AS _ccs ON _ccs.store_id = _os.store_id 
				WHERE
					_ccs.create_at BETWEEN :c_start_at AND :c_end_at
				) SELECT
				_os.store_id,
				_os.store_name,
				_scd.data_create_at,
				_scd.operate_type,
				_scd.safe_1000,
				_scd.safe_500,
				_scd.safe_100,
				_scd.safe_50,
				_scd.safe_10,
				_scd.safe_5,
				_scd.safe_1,
				_scd.cycle_500,
				_scd.cycle_100,
				_scd.cycle_50,
				_scd.cycle_10,
				_scd.cycle_5,
				_scd.cycle_1,
				_scd.sub_in_1000,
				_scd.sub_in_500,
				_scd.sub_in_100,
				_scd.sub_out_500,
				_scd.sub_out_100 
			FROM
				store_currency_data AS _scd 
				LEFT JOIN online_store AS _os ON _os.store_id = _scd.store_id",
			$this->database_trend,
			$s_where_arr,
			$this->database_trend,
			$this->database_trend
		);
		//準備執行sql
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute($a_where_arr_val);
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		$res = [];
		foreach ($rows as $k => &$v) {
			switch ($v["operate_type"]) {
				case '1':
					$v["trading_status"] = "溢鈔/溢幣";
					break;
				case '2':
					$v["trading_status"] = "純補入";
					break;
				case '3':
					$v["trading_status"] = "換補(收款)";
					break;
				case '4':
					$v["trading_status"] = "貨幣異動";
					break;
				case '99':
					$v["trading_status"] = "物流交易";
					break;
				default:
					$v["trading_status"] = "未知";
					break;
			}
			$res[] = $v;
		}
		return $res;
	}
}
