<?php
// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	/**
	 * Leo
	 * 取得門市列表
	 */
	public function SelectStoreList($params)
	{


		$a_where_arr = []; //sql條件陣列 
		$a_where_arr_val = [];
		$s_where_arr = ""; //sql條件字串，用$a_where_arr條件陣列轉換而來

		// 門市判斷條件
		if ($params['store'] != 0) {
			$a_where_arr[] = "_s.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_user_role = sprintf(
			"SELECT
				_ru.role_id
			FROM 
				[%s].[dbo].[users] AS _u
				LEFT JOIN [%s].[dbo].[roles_users] AS _ru ON _ru.user_id = _u.user_id
			WHERE
				_u.user_id = '%s'
			",
			$this->database_trend,
			$this->database_trend,
			$this->user_id
		);
		//準備執行sql
		$sth = $this->pdo_sql->prepare($sql_user_role);
		$sth->execute([]);
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		$b_isadmin = false;
		foreach ($rows as $k => $v) {
			if ($v["role_id"] == "admin") {
				$b_isadmin = true;
			}
		}

		//拼接sql字串
		$sql = sprintf(
			"SELECT 
				_s.store_id
				,_s.store_name
				,_s.store_state
				,_s.store_open_at
				,_c.cashier_id AS binding_cashier
				,_s.store_api_key
			FROM 
				[%s].[dbo].[store] AS _s
				LEFT JOIN [%s].[dbo].[cashier] AS _c ON _c.store_id = _s.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);
		$a_store_data = $sth->fetchAll(PDO::FETCH_ASSOC);
		foreach ($a_store_data as $k => &$v) {
			$v["binding_cashier"] = empty($v["binding_cashier"]) ? "" : $v["binding_cashier"];
			$v["reset_ctrl"] = false;
			//僅最高管理者有權限對尚未營運中的門市進行初始化
			if ($b_isadmin && $v["store_state"] == 0) {
				$v["reset_ctrl"] = true;
			}
		}

		return $a_store_data;
	}

	/**
	 * Leo
	 * 新增門市
	 */
	public function ChangeAddStore($params)
	{
		$n_store_id = (int) $params["store_id"];
		$s_store_name = (string) $params["store_name"];
		$d_store_open_at = (string) $params["store_open_at"];
		$n_store_area = (int) $params["store_area"];
		$n_store_state = (int) $params["store_state"];
		if ($s_store_name == "") {
			throw new Exception("store_id 參數錯誤");
		}
		if ($n_store_id <= 0) {
			throw new Exception("store_name 參數錯誤");
		}
		if ($d_store_open_at != "" && base_class::check_date_format($d_store_open_at) == false) {
			throw new Exception("store_open_at 參數錯誤");
		}
		//確認區域是否正確 1.北 2.中 3.南
		if (!in_array($n_store_area, [1, 2, 3])) {
			throw new Exception("store_area 參數錯誤");
		}
		//確認門市狀態是否正確 0.尚未營運 1.營運中
		if (!in_array($n_store_state, [0, 1])) {
			throw new Exception("store_state 參數錯誤");
		}
		$d_store_open_at = $d_store_open_at == "" ? null : $d_store_open_at;
		$sql = sprintf(
			"SELECT 
				store_id 
			FROM 
				[%s].[dbo].[store]
			WHERE 
				store_id = :store_id",
			$this->database_trend
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			"store_id" => $n_store_id,
		]);
		if ($sth->rowCount() != 0) {
			throw new Exception("該門市編號已存在");
		}

		//零用金、警戒水位初始化預設
		$default_store_option = [
			"petty_cash_500" => 0,
			"petty_cash_100" => 155,
			"petty_cash_50" => 200,
			"petty_cash_10" => 700,
			"petty_cash_5" => 200,
			"petty_cash_1" => 500,
			"cash_warning_level" => 10,
			"cash_stop_level" => 5,
			"cash_overflow_warning_level" => 95,
			"cash_overflow_stop_level" => 98,
			"coin_warning_water_level" => 95,
			"coin_stop_water_level" => 98,
			"coin_low_warning_water_level" => 10,
			"coin_low_stop_water_level" => 5,
			"coin_overflow_warning_level" => 95,
			"coin_overflow_stop_level" => 98,
		];


		//產生金鑰
		create_api_key:
		$s_store_api_key = $this->CreateRandomStr_php5(20);
		//檢查是否重複
		$sql = sprintf(
			"SELECT 
				store_id 
			FROM 
				[%s].[dbo].[store]
			WHERE 
				store_api_key = :store_api_key",
			$this->database_trend
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			"store_api_key" => $s_store_api_key,
		]);
		if ($sth->rowCount() != 0) {
			goto create_api_key;
		}
		// 開始新增
		$sql = sprintf(
			"INSERT INTO [%s].[dbo].[store] (
					store_id,
					store_name,
					store_api_key,
					store_open_at,
					store_area,
					store_state,
					petty_cash_500,
					petty_cash_100,
					petty_cash_50,
					petty_cash_10,
					petty_cash_5,
					petty_cash_1,
					highest_water_level_500,
					highest_water_level_100,
					highest_water_level_50,
					highest_water_level_10,
					highest_water_level_5,
					highest_water_level_1,
					cash_warning_level,
					cash_stop_level,
					cash_overflow_warning_level,
					cash_overflow_stop_level,
					coin_warning_water_level,
					coin_stop_water_level,
					coin_low_warning_water_level,
					coin_low_stop_water_level,
					coin_overflow_warning_level,
					coin_overflow_stop_level,
					store_otp,
					replace_day,
					cash_box_count
 
				)VALUES
				(
					:store_id,
					:store_name,
					:store_api_key,
					:store_open_at,
					:store_area,
					:store_state,
					:petty_cash_500,
					:petty_cash_100,
					:petty_cash_50,
					:petty_cash_10,
					:petty_cash_5,
					:petty_cash_1,
					0,
					0,
					0,
					0,
					0,
					0,
					:cash_warning_level,
					:cash_stop_level,
					:cash_overflow_warning_level,
					:cash_overflow_stop_level,
					:coin_warning_water_level,
					:coin_stop_water_level,
					:coin_low_warning_water_level,
					:coin_low_stop_water_level,
					:coin_overflow_warning_level,
					:coin_overflow_stop_level,
					0,
					0,
					0
				);
				",
			$this->database_trend
		);
		//準備執行sql
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			"store_id" => $n_store_id,
			"store_name" => $s_store_name,
			"store_api_key" => $s_store_api_key,
			"store_open_at" => $d_store_open_at,
			"store_area" => $n_store_area,
			"store_state" => $n_store_state,
			"petty_cash_500" => $default_store_option["petty_cash_500"],
			"petty_cash_100" => $default_store_option["petty_cash_100"],
			"petty_cash_50" => $default_store_option["petty_cash_50"],
			"petty_cash_10" => $default_store_option["petty_cash_10"],
			"petty_cash_5" => $default_store_option["petty_cash_5"],
			"petty_cash_1" => $default_store_option["petty_cash_1"],
			"cash_warning_level" => $default_store_option["cash_warning_level"],
			"cash_stop_level" => $default_store_option["cash_stop_level"],
			"cash_overflow_warning_level" => $default_store_option["cash_overflow_warning_level"],
			"cash_overflow_stop_level" => $default_store_option["cash_overflow_stop_level"],
			"coin_warning_water_level" => $default_store_option["coin_warning_water_level"],
			"coin_stop_water_level" => $default_store_option["coin_stop_water_level"],
			"coin_low_warning_water_level" => $default_store_option["coin_low_warning_water_level"],
			"coin_low_stop_water_level" => $default_store_option["coin_low_stop_water_level"],
			"coin_overflow_warning_level" => $default_store_option["coin_overflow_warning_level"],
			"coin_overflow_stop_level" => $default_store_option["coin_overflow_stop_level"],
		]);
		if ($sth->rowCount() == 0) {
			throw new Exception("新增門市失敗");
		}
		return true;
	}

	/**
	 * Leo
	 * 編輯門市(撈取編輯資料)
	 */
	public function SelectEditStoreInfo($params)
	{
		$n_store_id = (int)$params["store"];
		//拼接sql字串
		$sql = sprintf(
			"SELECT 
				_s.store_id,
				_s.store_name,
				_s.store_api_key,
				_s.store_open_at,
				_s.store_area,
				_s.store_state
			FROM 
				[%s].[dbo].[store] AS _s
			WHERE
				_s.store_id = :store_id
			",
			$this->database_trend
		);
		//準備執行sql
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			"store_id" => $n_store_id
		]);
		return $sth->fetch(PDO::FETCH_ASSOC);
	}

	/**
	 * Leo
	 * 更新門市
	 */
	public function ChangeUpdateStoreInfo($params)
	{
		$n_store_id = (int) $params["store"];
		$s_store_name = (string) $params["store_name"];
		$d_store_open_at = (string) $params["store_open_at"];
		$n_store_area = (int) $params["store_area"];
		$n_store_state = (int) $params["store_state"];

		if ($s_store_name == "") {
			throw new Exception("store_id 參數錯誤");
		}
		if ($n_store_id <= 0) {
			throw new Exception("store_name 參數錯誤");
		}
		if ($d_store_open_at != "" && base_class::check_date_format($d_store_open_at) == false) {
			throw new Exception("store_open_at 參數錯誤");
		}
		//確認區域是否正確 1.北 2.中 3.南
		if (!in_array($n_store_area, [1, 2, 3])) {
			throw new Exception("store_area 參數錯誤");
		}
		//確認門市狀態是否正確 0.尚未營運 1.營運中
		if (!in_array($n_store_state, [0, 1])) {
			throw new Exception("n_store_state 參數錯誤");
		}
		$d_store_open_at = $d_store_open_at == "" ? null : $d_store_open_at;

		$sql = sprintf(
			"UPDATE [%s].[dbo].[store] 
				SET 
				[store_name] = :store_name,
				[store_open_at] = :store_open_at,
				[store_area] = :store_area,
				[store_state] = :store_state
				WHERE
					[store_id] = :store_id;
				",
			$this->database_trend
		);
		//準備執行sql
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			"store_id" => $n_store_id,
			"store_name" => $s_store_name,
			"store_open_at" => $d_store_open_at,
			"store_area" => $n_store_area,
			"store_state" => $n_store_state,
		]);
		if ($sth->rowCount() == 0) {
			throw new Exception("store資料表更新失敗");
		}
		return true;
	}

	/**
	 * Leo (開發測試用)
	 * 初始化門市
	 */
	public function ChangeUpdateStoreReset($params)
	{
		$res = true;
		$n_store_id = (int) $params["store"];
		$n_petty_cash_500 = $params["petty_cash_500"];
		$n_petty_cash_100 = $params["petty_cash_100"];
		$n_petty_cash_50 = $params["petty_cash_50"];
		$n_petty_cash_10 = $params["petty_cash_10"];
		$n_petty_cash_5 = $params["petty_cash_5"];
		$n_petty_cash_1 = $params["petty_cash_1"];



		//查詢登入的使用者角色(僅最高權限可初始化門市)
		$sql_user_role = sprintf(
			"SELECT
				_ru.role_id
			FROM 
				[%s].[dbo].[users] AS _u
				LEFT JOIN [%s].[dbo].[roles_users] AS _ru ON _ru.user_id = _u.user_id
			WHERE
				_u.user_id = '%s'
			",
			$this->database_trend,
			$this->database_trend,
			$this->user_id
		);
		//準備執行sql
		$sth = $this->pdo_sql->prepare($sql_user_role);
		$sth->execute([]);
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		$b_isadmin = false;
		foreach ($rows as $k => $v) {
			if ($v["role_id"] == "admin") {
				$b_isadmin = true;
			}
		}
		if (!$b_isadmin) {
			throw new Exception("此登入管理者，無初始化權限!");
		}

		$sql_select = sprintf(
			"SELECT 
				_c.cashier_name
				,_c.cashier_id
				,_c.store_id
				,_s.store_state
			FROM 
				[%s].[dbo].[cashier] AS _c
				LEFT JOIN [%s].[dbo].[store] AS _s ON _s.store_id = _c.store_id
			WHERE
				_c.store_id = :store_id
			",
			$this->database_trend,
			$this->database_trend
		);

		//準備執行sql
		$sth = $this->pdo_sql->prepare($sql_select);
		$sth->execute([
			"store_id" => $n_store_id
		]);
		$row = $sth->fetch(PDO::FETCH_ASSOC);
		$s_cashier_id = $row["cashier_id"];
		$n_store_id = $row["store_id"];
		$n_store_state = $row["store_state"];
		if ($n_store_state == 1) {
			throw new Exception("此門市營運中，無法進行初始化");
		}
		$sql = [];
		//先清空該門市所有帳務 在新增一筆補鈔
		$sql[] = "DELETE FROM cashier_currency_ledger WHERE store_id = $n_store_id";
		$sql[] = "DELETE FROM cashier_currency_statement WHERE store_id = $n_store_id";
		$sql[] = "DELETE FROM store_received_ledger WHERE store_id = $n_store_id";
		$sql[] = "DELETE FROM store_received_statement WHERE store_id = $n_store_id";
		$sql[] = "DELETE FROM store_transaction_statement WHERE store_id = $n_store_id";


		$create_at = date("Y-m-d H:i:s");
		$s_sql_insert = "INSERT INTO [dbo].[cashier_currency_statement] (
			[cashier_id],
			[store_id],
			[create_at],
			[cycle_box_50],
			[cycle_box_10],
			[cycle_box_5],
			[cycle_box_1],
			[operate_type],
			[sub_500_out],
			[sub_100_out] 
		)
		VALUES
			(
				'$s_cashier_id',
				$n_store_id,
				'$create_at',
				$n_petty_cash_50,
				$n_petty_cash_10,
				$n_petty_cash_5,
				$n_petty_cash_1,
				2,
				$n_petty_cash_500,
				$n_petty_cash_100
			);";

		//開始更新 beginTransaction
		$this->pdo_sql->beginTransaction();
		try {
			foreach ($sql as $k => $v) {
				$sth = $this->pdo_sql->prepare($v);
				$sth->execute([]);
			}

			$sth = $this->pdo_sql->prepare($s_sql_insert);
			$sth->execute([]);

			$this->pdo_sql->commit();
		} catch (\Throwable $th) {
			$this->pdo_sql->rollBack();
			$res = false;
			throw new Exception("初始化失敗");
		}

		return $res;
	}
}
