<?php
// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	/**
	 * Leo
	 * 取得失敗的物流交易資料(fail)
	 * @param start_date, end_date, store
	 * @return list[]
	 */
	public function SelectTransactionList($params)
	{
		
		$a_where_arr = []; //sql條件陣列 
		$a_where_arr_val = [];
		$s_where_arr = ""; //sql條件字串，用$a_where_arr條件陣列轉換而來

		// 時間條件
		$a_where_arr[] = "_stf.transaction_end_at BETWEEN :startTime AND :endTime";
		$a_where_arr_val["startTime"] = date('Y-m-d H:i:00', strtotime($params['start_date']));
		$a_where_arr_val["endTime"] = date('Y-m-d H:i:59', strtotime($params['end_date']));

		// 門市判斷條件
		if ($params['store'] != 0) {
			$a_where_arr[] = "_stf.store_id = :store";
			$a_where_arr_val["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);
		}
		//拼接sql字串
		$sql = sprintf(
			"SELECT 
				_stf.store_id,
				_stf.trading_id,
				_stf.received_ledger_id,
				_stf.shopee_order_id,
				_stf.shopee_shipment_id,
				_stf.order_amount,
				_stf.trading_status,
				_stf.transaction_start_at AS transaction_start_time,
				_stf.transaction_end_at AS transaction_end_time,
				_stf.received_at AS received_datetime,
				_stf.order_amount,
				_stf.cash_1000_in,
				_stf.cash_500_in,
				_stf.cash_100_in,
				_stf.cash_50_in,
				_stf.cash_10_in,
				_stf.cash_5_in,
				_stf.cash_1_in,
				_stf.cash_500_out,
				_stf.cash_100_out,
				_stf.cash_50_out,
				_stf.cash_10_out,
				_stf.cash_5_out,
				_stf.cash_1_out,
				_stf.sub_1000_in,
				_stf.sub_500_in,
				_stf.sub_100_in,
				_stf.sub_500_out,
				_stf.sub_100_out,
				_stf.cashier_id,
				_stf.store_id,
				_s.store_name
			FROM 
				[%s].[dbo].[store_transaction_fail] AS _stf
				LEFT JOIN [%s].[dbo].[store] AS _s ON _s.store_id = _stf.store_id				
			%s
			",
			$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["cash_1000"] = $v["sub_1000_in"] + $v["cash_1000_in"];
			$v["cash_500"] = $v["sub_500_in"] + $v["cash_500_in"] - $v["cash_500_out"] - $v["sub_500_out"];
			$v["cash_100"] = $v["sub_100_in"] + $v["cash_100_in"] - $v["cash_100_out"] - $v["sub_100_out"];
			$v["cash_50"] = $v["cash_50_in"] - $v["cash_50_out"];
			$v["cash_10"] = $v["cash_10_in"] - $v["cash_10_out"];
			$v["cash_5"] = $v["cash_5_in"] - $v["cash_5_out"];
			$v["cash_1"] = $v["cash_1_in"] - $v["cash_1_out"];
			$a_store_data[] = $v;
		}
		return $a_store_data;
	}
}
