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


		$a_where_arr = []; //sql條件陣列 
		$s_where_arr = ""; //sql條件字串，用$a_where_arr條件陣列轉換而來
		$a_cte_where_arr = []; //cte用 sql條件陣列 
		$s_cte_where_arr = ""; //cte用 sql條件字串，用$a_where_arr條件陣列轉換而來
		$a_where_arr_val = [];
		// 時間條件
		$a_where_arr[] = "_ccl.statistics_at BETWEEN :startTime AND :endTime";
		$a_where_arr_val["startTime"] = date('Y-m-d', strtotime($params['start_date']));
		$a_where_arr_val["endTime"] = date('Y-m-d', strtotime($params['end_date']));

		$a_cte_where_arr[] = "_ccl.statistics_at BETWEEN :y_startTime AND :y_endTime";
		$a_where_arr_val["y_startTime"] = date('Y-m-d', strtotime("-1 day", strtotime($params['start_date'])));
		$a_where_arr_val["y_endTime"] = date('Y-m-d', strtotime("-1 day", strtotime($params['end_date'])));

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

		//門市查詢控制項 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);
		}
		//cte sql條件陣列轉換
		if (!empty($a_cte_where_arr)) {
			$s_cte_where_arr = " WHERE " . implode(" AND ", $a_cte_where_arr);
		}

		//拼接sql字串
		$sql = sprintf(
			"WITH yesterday_currency_ledger AS (
				SELECT 
					_ccl.last_cycle_box_500 AS  yesterday_cycle_box_500
					,_ccl.last_cycle_box_100 AS  yesterday_cycle_box_100
					,_ccl.last_cycle_box_50 AS  yesterday_cycle_box_50
					,_ccl.last_cycle_box_10 AS  yesterday_cycle_box_10
					,_ccl.last_cycle_box_5 AS  yesterday_cycle_box_5
					,_ccl.last_cycle_box_1 AS  yesterday_cycle_box_1
					,_ccl.last_safe_box_1000 AS  yesterday_safe_box_1000
					,_ccl.last_safe_box_500 AS  yesterday_safe_box_500
					,_ccl.last_safe_box_100 AS  yesterday_safe_box_100
					,_ccl.last_safe_box_50 AS  yesterday_safe_box_50
					,_ccl.last_safe_box_10 AS  yesterday_safe_box_10
					,_ccl.last_safe_box_5 AS  yesterday_safe_box_5
					,_ccl.last_safe_box_1 AS  yesterday_safe_box_1
					,CONVERT(VARCHAR(20),DATEADD(day , 1 ,_ccl.statistics_at),23) AS statistics_at				
					,_ccl.cashier_id
					,_ccl.store_id
				FROM 
					[%s].[dbo].[cashier_currency_ledger] AS _ccl
				%s
			)
			SELECT 
					_ccl.cashier_id
					,_ccl.store_id
					,_ccl.statistics_at
					,_ccl.reject_cash_total
					,_ccl.total_1000_in
					,_ccl.total_500_in
					,_ccl.total_100_in
					,_ccl.total_50_in
					,_ccl.total_10_in
					,_ccl.total_5_in
					,_ccl.total_1_in
					,_ccl.total_500_out
					,_ccl.total_100_out
					,_ccl.total_50_out
					,_ccl.total_10_out
					,_ccl.total_5_out
					,_ccl.total_1_out
					,CASE WHEN _ycl.yesterday_cycle_box_500 IS NULL THEN 0 ELSE _ycl.yesterday_cycle_box_500 END AS yesterday_cycle_box_500
					,CASE WHEN _ycl.yesterday_cycle_box_100 IS NULL THEN 0 ELSE _ycl.yesterday_cycle_box_100 END AS yesterday_cycle_box_100
					,CASE WHEN _ycl.yesterday_cycle_box_50 IS NULL THEN 0 ELSE _ycl.yesterday_cycle_box_50 END AS yesterday_cycle_box_50
					,CASE WHEN _ycl.yesterday_cycle_box_10 IS NULL THEN 0 ELSE _ycl.yesterday_cycle_box_10 END AS yesterday_cycle_box_10
					,CASE WHEN _ycl.yesterday_cycle_box_5 IS NULL THEN 0 ELSE _ycl.yesterday_cycle_box_5 END AS yesterday_cycle_box_5
					,CASE WHEN _ycl.yesterday_cycle_box_1 IS NULL THEN 0 ELSE _ycl.yesterday_cycle_box_1 END AS yesterday_cycle_box_1
					,CASE WHEN _ycl.yesterday_safe_box_1000 IS NULL THEN 0 ELSE _ycl.yesterday_safe_box_1000 END AS yesterday_safe_box_1000
					,CASE WHEN _ycl.yesterday_safe_box_500 IS NULL THEN 0 ELSE _ycl.yesterday_safe_box_500 END AS yesterday_safe_box_500
					,CASE WHEN _ycl.yesterday_safe_box_100 IS NULL THEN 0 ELSE _ycl.yesterday_safe_box_100 END AS yesterday_safe_box_100
					,CASE WHEN _ycl.yesterday_safe_box_50 IS NULL THEN 0 ELSE _ycl.yesterday_safe_box_50 END AS yesterday_safe_box_50
					,CASE WHEN _ycl.yesterday_safe_box_10 IS NULL THEN 0 ELSE _ycl.yesterday_safe_box_10 END AS yesterday_safe_box_10
					,CASE WHEN _ycl.yesterday_safe_box_5 IS NULL THEN 0 ELSE _ycl.yesterday_safe_box_5 END AS yesterday_safe_box_5
					,CASE WHEN _ycl.yesterday_safe_box_1 IS NULL THEN 0 ELSE _ycl.yesterday_safe_box_1 END AS yesterday_safe_box_1
					,_ccl.last_cycle_box_500
					,_ccl.last_cycle_box_100
					,_ccl.last_cycle_box_50
					,_ccl.last_cycle_box_10
					,_ccl.last_cycle_box_5
					,_ccl.last_cycle_box_1
					,_ccl.last_safe_box_1000
					,_ccl.last_safe_box_500
					,_ccl.last_safe_box_100
					,_ccl.last_safe_box_50
					,_ccl.last_safe_box_10
					,_ccl.last_safe_box_5
					,_ccl.last_safe_box_1
					,_c.cashier_name
					,_s.store_id
					,_s.store_name
			FROM 
				[%s].[dbo].[cashier_currency_ledger] AS _ccl	
				LEFT JOIN [%s].[dbo].[cashier] AS _c ON _c.cashier_id = _ccl.cashier_id
				LEFT JOIN [%s].[dbo].[store] AS _s ON _s.store_id = _ccl.store_id
				LEFT JOIN yesterday_currency_ledger AS _ycl ON _ycl.statistics_at = _ccl.statistics_at AND _ycl.store_id = _ccl.store_id AND _ycl.cashier_id = _ccl.cashier_id
			%s
			",
			$this->database_trend,
			$s_cte_where_arr,
			$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 = [];
		foreach ($row as $k => &$v) {
			//設定當日保全箱(鈔票)剩餘數量、總金額
			$v['total_safe_cash_count'] =
				$v["last_safe_box_1000"] + $v["last_safe_box_500"] + $v["last_safe_box_100"];
			$v['total_safe_cash_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);
			//設定當日保全箱(硬幣)剩餘數量、總金額
			$v['total_safe_coin_count'] =
				$v["last_safe_box_50"] + $v["last_safe_box_10"] + $v["last_safe_box_5"] + $v["last_safe_box_1"];
			$v['total_safe_coin_amount'] =
				bcmul($v["last_safe_box_50"], 50, 0) +
				bcmul($v["last_safe_box_10"], 10, 0) +
				bcmul($v["last_safe_box_5"], 5, 0) +
				bcmul($v["last_safe_box_1"], 1, 0);
			//設定前日保全箱(鈔票)剩餘數量
			$yesterday_safe_cash_count =
				$v["yesterday_safe_box_1000"] + $v["yesterday_safe_box_500"] + $v["yesterday_safe_box_100"];
			//計算拒鈔率
			$v["reject_rate"] =
				bcmul(bcdiv($v["reject_cash_total"], ($v["total_1000_in"] + $v["total_500_in"] + $v["total_100_in"]), 2), 100, 2);
			//計算循環率
			$v["cycle_rate"] = 100 -
				bcdiv(($v['total_safe_cash_count'] - $v["total_1000_in"] - $yesterday_safe_cash_count), ($v["total_500_in"] + $v["total_100_in"]), 4) * 100;
			//計算機台內當日剩餘總金額
			$total_amount =
				$v['total_safe_cash_amount'] +
				$v['total_safe_coin_amount'] +
				bcmul($v["last_cycle_box_500"], 500, 0) +
				bcmul($v["last_cycle_box_100"], 100, 0) +
				bcmul($v["last_cycle_box_50"], 50, 0) +
				bcmul($v["last_cycle_box_10"], 10, 0) +
				bcmul($v["last_cycle_box_5"], 5, 0) +
				bcmul($v["last_cycle_box_1"], 1, 0);

			$arr = [
				"store_id" => $v["store_id"],
				"store_name" => $v["store_name"],
				"cashier_name" => $v["cashier_name"],
				"statistics_date" => $v["statistics_at"],
				"total_1000_in" => $v["total_1000_in"],
				"total_500_in" => $v["total_500_in"],
				"total_100_in" => $v["total_100_in"],
				"total_500_out" => $v["total_500_out"],
				"total_100_out" => $v["total_100_out"],
				"reject_rate" => $v["reject_rate"],
				"cycle_rate" => (float) number_format($v["cycle_rate"], 2, ".", ""),
				"total_amount" => $total_amount,
				"total_500" => $v["last_cycle_box_500"],
				"total_100" => $v["last_cycle_box_100"],
				"total_50" => $v["last_cycle_box_50"],
				"total_10" => $v["last_cycle_box_10"],
				"total_5" => $v["last_cycle_box_5"],
				"total_1" => $v["last_cycle_box_1"],
				"total_cash_count" => $v["total_safe_cash_count"],
				"total_cash_amount" => $v["total_safe_cash_amount"],
				"total_coin_count" => $v["total_safe_coin_count"],
				"total_coin_amount" => $v["total_safe_coin_amount"],
			];
			$a_store_data[] = $arr;
		}
		return $a_store_data;
	}
}
