<?php
// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{

	public function SelectUsers($params)
	{
		//指定查詢user_id
		$s_user_id = (string) $params["user_id"];
		$s_user_id = trim($s_user_id);

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

		//不顯示個人權限角色
		$a_where_arr[] = " _r.role_is_personal = 0";

		//指定查詢user判斷條件
		if ($s_user_id != "") {
			$a_where_arr[] = "_u.user_id = :user_id";
			$a_where_arr_val["user_id"] = $s_user_id;
		}

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

		$sql = sprintf(
			"SELECT
					_u.updated_at,
					_u.updated_by,
					_u.user_name,
					_u.user_id,
					_ru.role_id,
					_r.role_name,
					_u.enabled
				FROM
					[%s].[dbo].[users] AS _u
					LEFT JOIN [%s].[dbo].[roles_users] AS _ru ON _ru.user_id = _u.user_id
					LEFT JOIN [%s].[dbo].[roles] AS _r ON _r.role_id = _ru.role_id
				%s",
			$this->database_alarm,
			$this->database_alarm,
			$this->database_alarm,
			$s_where_arr
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute($a_where_arr_val);
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
		foreach ($rows as $k => &$v) {
			$v["enabled_str"] = $v["enabled"] == 1 ? "No" : "Yes";
			$v["edit_ctrl"] = $v["role_id"] == 'admin' ? 0 : 1;
			$v["updated_at"] = empty($v["updated_at"]) ? "" : date("Y-m-d H:i:s", strtotime($v["updated_at"]));
		}
		$res = $rows;
		//指定查詢的話只會有一筆資料，直接回傳第一筆
		if ($s_user_id != "") {
			$res = $rows[0];
		}
		return $res;
	}

	public function Selectsetting_optios($params)
	{
		$res = [];

		$sql = sprintf(
			"SELECT 
				[role_id],
				[role_name]
			FROM 
				[%s].[dbo].[roles]
			WHERE 
				[role_ui_hide] = 0 ",
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);

		return $rows;
	}

	//	新增使用者
	public function ChangeAddUserInfo($params)
	{

		$s_user_id = (string) $params["user_id"];
		$s_user_id = trim($s_user_id);

		$s_name = (string) $params["name"];
		$s_name = trim($s_name);

		$s_password = (string) $params["password"];
		$s_password = trim($s_password);

		$s_check_password = (string) $params["check_password"];
		$s_check_password = trim($s_check_password);

		$s_role_id = (string) $params["role_id"];
		$s_role_id = trim($s_role_id);

		$n_enabled = (int) $params["enabled"];


		//資料驗證
		if ($s_user_id == "") {
			throw new Exception("未輸入員工代號", 1);
		} else {
			$sql = sprintf(
				"SELECT
					user_id 
				FROM
					[%s].[dbo].[users] 
				WHERE 
					user_id = :user_id",
				$this->database_alarm
			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'user_id' => $s_user_id,
			]);
			$rows = $sth->fetch(PDO::FETCH_ASSOC);
			if (!empty($rows["user_id"])) {
				throw new Exception("該員工代號已存在", 1);
			}
		}
		if ($s_name == "") {
			throw new Exception("未輸入姓名", 1);
		}
		if ($s_password == "") {
			throw new Exception("未輸入密碼", 1);
		}
		if ($s_check_password == "") {
			throw new Exception("未輸入確認密碼", 1);
		}
		if (
			$s_password != $s_check_password
		) {
			throw new Exception("密碼與確認密碼不符合", 1);
		}
		if ($s_role_id == "") {
			throw new Exception("未選擇權限", 1);
		}
		if (!in_array($n_enabled, [0, 1])) {
			throw new Exception("停用狀態異常", 1);
		}

		//開始交易
		$this->pdo_sql->beginTransaction();

		//	新增users資料表內容
		$sql = sprintf("INSERT INTO [%s].[dbo].[users] (
							 [user_id]
							,[user_name]
							,[updated_by]
							,[enabled]
							,[password]
						) VALUES (
							 :user_id
							,:user_name
							,:updated_by
							,:enabled
							,:password
						);", $this->database_alarm);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'user_id' => $s_user_id,
			'user_name' => $s_name,
			'updated_by' => $this->user_id,
			'enabled' => $n_enabled,
			'password' => $s_password,
		]);

		if ($sth->rowCount() == 0) {
			$this->pdo_sql->rollBack();
			throw new Exception("users資料表建立失敗");
		}

		//	新增roles資料表內容 (個人權限角色)
		$sql = sprintf("INSERT INTO [%s].[dbo].[roles] (
							 [role_id]
							,[role_name]
							,[role_is_personal]
							,[role_ui_hide]
							,[enabled]
							,[is_ui_visible]
						) VALUES (
							 :role_id
							,:role_name
							, 1
							, 1
							, 1
							, 0
						);", $this->database_alarm);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'role_id' => '$$user$$' . $s_user_id,
			'role_name' => $s_user_id,
		]);

		if ($sth->rowCount() == 0) {
			$this->pdo_sql->rollBack();
			throw new Exception("roles資料表建立失敗");
		}

		//	新增roles_users資料表內容 
		$sql = sprintf("INSERT INTO [%s].[dbo].[roles_users] (
								 [role_id]
	      						,[user_id]
							) VALUES (
								 :role_id
								,:user_id
							);", $this->database_alarm);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'role_id' => $s_role_id,
			'user_id' => $s_user_id,
		]);
		if ($sth->rowCount() == 0) {
			$this->pdo_sql->rollBack();
			throw new Exception("roles_users資料表建立失敗");
		}

		//	新增roles_users資料表內容 (個人權限角色)
		$sql = sprintf("INSERT INTO [%s].[dbo].[roles_users] (
							 [role_id]
      						,[user_id]
						) VALUES (
							 :role_id
							,:user_id
						);", $this->database_alarm);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'role_id' => '$$user$$' . $s_user_id,
			'user_id' => $s_user_id,
		]);

		if ($sth->rowCount() == 0) {
			$this->pdo_sql->rollBack();
			throw new Exception("roles_users資料表建立失敗");
		}

		$this->pdo_sql->commit();
		return true;
	}

	//	更新使用者
	public function ChangeUpdateUserInfo($params)
	{

		$s_user_id = (string) $params["user_id"];
		$s_user_id = trim($s_user_id);

		$s_name = (string) $params["name"];
		$s_name = trim($s_name);

		$s_password = (string) $params["password"];
		$s_password = trim($s_password);

		$s_check_password = (string) $params["check_password"];
		$s_check_password = trim($s_check_password);

		$s_role_id = (string) $params["role_id"];
		$s_role_id = trim($s_role_id);

		$n_enabled = (int) $params["enabled"];


		//資料驗證
		if ($s_name == "") {
			throw new Exception("未輸入姓名", 1);
		}
		if ($s_password != "") {
			if ($s_check_password == "") {
				throw new Exception("未輸入確認密碼", 1);
			}
			if (
				$s_password != $s_check_password
			) {
				throw new Exception("密碼與確認密碼不符合", 1);
			}
		}
		if ($s_role_id == "") {
			throw new Exception("未選擇權限", 1);
		}
		if (!in_array($n_enabled, [0, 1])) {
			throw new Exception("停用狀態異常", 1);
		}
		//更新資料整理
		$a_user_update = [
			"user_name" => $s_name,
			"updated_by" => $this->user_id,
			"updated_at" => date("Y-m-d H:i:s"),
			"enabled" => $n_enabled,
		];
		if ($s_password != "") {
			$a_update["password"] = $s_password;
		}
		//	更新users資料表內容
		$a_user_update_sql = [];
		foreach ($a_user_update as $k => $v) {
			$a_user_update_sql[] = " $k = :$k";
		}
		$s_user_update_sql = implode(",", $a_user_update_sql);
		//開始交易
		$this->pdo_sql->beginTransaction();
		$sql = sprintf(
			"UPDATE [%s].[dbo].[users] 
			SET
				%s
			WHERE
				[user_id] = '%s';",
			$this->database_alarm,
			$s_user_update_sql,
			$s_user_id
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute($a_user_update);

		if ($sth->rowCount() == 0) {
			$this->pdo_sql->rollBack();
			throw new Exception("users資料表更新失敗");
		}

		//	更新roles_users資料表內容 
		$sql = sprintf(
			"UPDATE _ru 
			SET 
				_ru.role_id = :role_id
			FROM
				[%s].[dbo].[roles_users] AS _ru
				LEFT JOIN [%s].[dbo].[roles] AS _r ON _r.role_id = _ru.role_id 
			WHERE
				_ru.user_id = :user_id
				AND _r.role_is_personal = 0;",
			$this->database_alarm,
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'role_id' => $s_role_id,
			'user_id' => $s_user_id,
		]);

		if ($sth->rowCount() == 0) {
			$this->pdo_sql->rollBack();
			throw new Exception("roles_users資料表更新失敗");
		}

		$this->pdo_sql->commit();
		return true;
	}

	//	刪除使用者
	public function ChangeDeleteUserInfo($params)
	{

		$role_id = '$$user$$' . $params['userID'];


		$this->pdo_sql->beginTransaction();

		//	查詢roles_menu有無此使用者資料，有刪除，但這似乎是多餘的動作 => roles_menu？？？
		$sql = sprintf(
			"SELECT [role_id],[menu_id]
							FROM [%s].[dbo].[roles_menu]
							WHERE [role_id] = :role_id;",
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'role_id' => $role_id,
		]);

		if ($sth->fetch(PDO::FETCH_BOUND) == 1) {
			$sql = sprintf(
				"DELETE 
								FROM [%s].[dbo].[roles_menu] 
								WHERE [role_id] = :role_id;",
				$this->database_alarm
			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'role_id' => $role_id,
			]);
			if ($sth->rowCount() == 0) {
				$this->pdo_sql->rollBack();
				throw new Exception("roles_menu刪除失敗");
			}
		}

		//	查詢roles_users有無此使用者資料，有刪除
		$sql = sprintf(
			"SELECT [role_id],[user_id]
							FROM [%s].[dbo].[roles_users]
							WHERE [role_id] = :role_id;",
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'role_id' => $role_id,
		]);

		if ($sth->fetch(PDO::FETCH_BOUND) == 1) {
			$sql = sprintf(
				"DELETE 
								FROM [%s].[dbo].[roles_users] 
								WHERE [user_id] = :user_id;",
				$this->database_alarm
			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'user_id' => $params['userID'],
			]);
			if ($sth->rowCount() == 0) {
				$this->pdo_sql->rollBack();
				throw new Exception("roles_users刪除失敗");
			}
		}

		//	查詢roles有無此使用者資料，有刪除
		$sql = sprintf(
			"SELECT [role_id],[role_name]
							FROM [%s].[dbo].[roles]
							WHERE [role_id] = :role_id;",
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'role_id' => $role_id,
		]);

		if ($sth->fetch(PDO::FETCH_BOUND) == 1) {
			$sql = sprintf(
				"DELETE 
								FROM [%s].[dbo].[roles] 
								WHERE [role_id] = :role_id;",
				$this->database_alarm
			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'role_id' => $role_id,
			]);
			if ($sth->rowCount() == 0) {
				$this->pdo_sql->rollBack();
				throw new Exception("roles刪除失敗");
			}
		}

		//	查詢users有無此使用者資料，有刪除
		$sql = sprintf(
			"SELECT [user_id]
							FROM [%s].[dbo].[users]
							WHERE [user_id] = :user_id;",
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'user_id' => $params['userID'],
		]);

		if ($sth->fetch(PDO::FETCH_BOUND) == 1) {
			$sql = sprintf(
				"DELETE 
								FROM [%s].[dbo].[users]
								WHERE [user_id] = :user_id;",
				$this->database_alarm
			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'user_id' => $params['userID'],
			]);
			if ($sth->rowCount() == 0) {
				$this->pdo_sql->rollBack();
				throw new Exception("users刪除失敗");
			}
		}

		//	查詢users_page_setting有無此使用者資料，有刪除
		$sql = sprintf(
			"SELECT [user_id]
							FROM [%s].[dbo].[users_page_setting]
							WHERE [user_id] = :user_id;",
			$this->database_alarm
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'user_id' => $params['userID'],
		]);

		if ($sth->fetch(PDO::FETCH_BOUND) == 1) {
			$sql = sprintf(
				"DELETE 
								FROM [%s].[dbo].[users_page_setting]
								WHERE [user_id] = :user_id;",
				$this->database_alarm
			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'user_id' => $params['userID'],
			]);
			if ($sth->rowCount() == 0) {
				$this->pdo_sql->rollBack();
				throw new Exception("users_page_setting刪除失敗");
			}
		}


		$this->pdo_sql->commit();
		return true;
	}
}
