<?php
// 頁面專用WebAPI, 不管任何的頁面, class名稱固定為 [WebAPIForMenuPage], 不可變更
class WebAPIForMenuPage extends base_class
{
	//獲取當前使用者列表
	public function SelectUsers($params)
	{

		$sql = "SELECT 
					_u.user_id
					,_u.user_name
					,_ru.role_id
					,_r.role_name
				FROM 
					users AS _u
					LEFT JOIN roles_users AS _ru ON _ru.user_id = _u.user_id
					LEFT JOIN roles AS _r ON _r.role_id = _ru.role_id
				ORDER BY 
					_u.user_id;";
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$rows = $sth->fetchAll(PDO::FETCH_ASSOC);

		$temp_arr = [];
		foreach ($rows as $k => $v) {
			$temp_arr[$v["user_id"]]["user_id"] = $v["user_id"];
			$temp_arr[$v["user_id"]]["user_name"] = $v["user_name"];
			if (strstr($v["role_id"], "$") === false) {
				$temp_arr[$v["user_id"]]["role_name"][] = $v["role_name"];
			}
		}
		$res = [];
		foreach ($temp_arr as $k => $v) {
			$res[] = [
				"user_id" => $v["user_id"],
				"user_name" => $v["user_name"],
				"user_role" => implode(",", $v["role_name"]),
			];
		}

		return $res;
	}
	//獲取當前角色清單
	public function SelectAddUserInfoLoadingOptios($params)
	{
		$sql = sprintf(
			"SELECT [role_id] ,[role_name]
						FROM [%s].[dbo].[roles]
						WHERE [role_ui_hide] = 'false' 
						",
			$this->database_trend
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		$roles_opt = $sth->fetchAll(PDO::FETCH_ASSOC);

		return compact('roles_opt');
	}

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

		$role_arr = $params['role']['value'];
		$roleStationSpec_arr = $params['roleStationSpec']['value'];
		$test = "ok123";


		$role_id = '$$user$$' . $params['userID'];
		$role_name = $params['name'] . ' PRIVATE';

		try {
			// $this->pdo_sql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->pdo_sql->beginTransaction();

			//	新增users資料表內容
			$sql = sprintf("INSERT INTO [%s].[dbo].[users] (
							 [user_id]
							,[user_name]
							,[enabled]
							,[password]
						) VALUES (
							 :userID
							,:name
							, 1
							,:password
						);", $this->database_trend);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'userID' => $params['userID'],
				'name' => $params['name'],
				//'enabled' => $params['enabled'],
				'password' => $params['password'],
			]);

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


			//	新增roles資料表內容
			$sql = sprintf("INSERT INTO [%s].[dbo].[roles] (
							 [role_id]
							,[role_name]
							,[role_is_personal]
							,[role_ui_hide]
							,[enabled]
						) VALUES (
							 :role_id
							,:role_name
							, 1
							,:role_ui_hide
							, 1
						);", $this->database_trend);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'role_id' => $role_id,
				'role_name' => $params['userID'],
				'role_ui_hide' => 1,
				/*'enabled' => $params['enabled'],*/
			]);

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

			//	新增roles_users資料表內容 || 新增roles_station_spec資料表內容


			foreach ($role_arr as $role_id_arr) {
				$sql = sprintf("INSERT INTO [%s].[dbo].[roles_users] (
									 [role_id]
		      						,[user_id]
								) VALUES (
									 :role_id
									,:user_id
								);", $this->database_trend);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'role_id' => $role_id_arr,
					'user_id' => $params['userID'],
				]);

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


			$sql = sprintf("INSERT INTO [%s].[dbo].[roles_users] (
								 [role_id]
	      						,[user_id]
							) VALUES (
								 :role_id
								,:user_id
							);", $this->database_trend);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'role_id' => $role_id,
				'user_id' => $params['userID'],
			]);

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




			$this->pdo_sql->commit();
			return true;
		} catch (Exception $mes) {
			$this->pdo_sql->rollBack();
			return $mes->getMessage();
		}
	}


	//	編輯使用者
	public function SelecteditUserInfo($params)
	{

		$res = [
			"user_id" => "",
			"user_name" => "",
			"role_name" => [],
			"password" => "",
			"enabled" => "",
		];
		$sql = sprintf(
			"SELECT 	
				_u.user_id
				,_u.user_name
				,_ru.role_id AS role_name
				,_u.password
				,_u.enabled
			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 = :user_id",
			$this->database_trend,
			$this->database_trend
		);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'user_id' => $params['user_id']
		]);
		$row = $sth->fetchAll(PDO::FETCH_ASSOC);
		foreach ($row as $k => $v) {
			$res['user_id'] = $v["user_id"];
			$res['user_name'] = $v["user_name"];
			if (strpos($v["role_name"], "$$") === false) {
				$res['role_name'][] = $v["role_name"];
			}
			$res['password'] = $v["password"];
			$res['enabled'] = $v["enabled"];
		}
		//角色陣列轉字串讓前端好解析，以,區隔
		$res["role_name"] = implode(",", $res["role_name"]);
		return $res;
	}

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

		$role_arr = $params['role']['value'];
		$roleStationSpec_arr = $params['roleStationSpec']['value'];

		$role_id = '$$user$$' . $params['userID'];
		$role_name = $params['name'] . ' PRIVATE';


		$newpassword = $params['password'];
		$oldpassword = $params['hide_password'];
		//return $oldpassword

		try {
			$this->pdo_sql->beginTransaction();


			if ($newpassword == null || $newpassword == '') {
				//	更新users資料表內容
				$sql = sprintf(
					"UPDATE 	[%s].[dbo].[users] 
								SET 	 [user_name] = :name
										,[enabled] = :enabled
								WHERE [user_id] = :userID ;",
					$this->database_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'userID' => $params['userID'],
					'name' => $params['name'],
					'enabled' => $params['enabled'],
				]);

				if ($sth->rowCount() == 0) {
					throw new Exception("users資料表更新失敗");
				}
			} else {
				$sql = sprintf(
					"UPDATE 	[%s].[dbo].[users] 
								SET 	 [user_name] = :name
										,[enabled] = :enabled
										,[password] = :password
								WHERE [user_id] = :userID ;",
					$this->database_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'userID' => $params['userID'],
					'name' => $params['name'],
					'enabled' => $params['enabled'],
					'password' => $params['password'],
				]);

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

			//	更新roles資料表內容
			$sql = sprintf(
				"SELECT *
								FROM [%s].[dbo].[roles] 
								WHERE [role_id] = :role_id;",
				$this->database_trend
			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'role_id' => $role_id,
			]);

			if ($sth->fetch(PDO::FETCH_BOUND) == 1) {
				$sql = sprintf(
					"UPDATE  [%s].[dbo].[roles] 
							SET 	 [role_name] = :role_name
									,[enabled] = :enabled
							WHERE [role_id] = :role_id ;",
					$this->database_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'role_id' => $role_id,
					'role_name' => $params['userID'],
					'enabled' => $params['enabled'],
				]);

				if ($sth->rowCount() == 0) {
					throw new Exception("roles資料表更新失敗");
				}
			} else {
				$sql = sprintf("INSERT INTO [%s].[dbo].[roles] (
									 [role_id]
									,[role_name]
									,[role_is_personal]
									,[role_ui_hide]
									,[enabled]
								) VALUES (
									 :role_id
									,:role_name
									, 1
									,:role_ui_hide
									, 1
								);", $this->database_trend);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'role_id' => $role_id,
					'role_name' => $params['userID'],
					'role_ui_hide' => 1,
					/*'enabled' => $params['enabled'],*/
				]);

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


			//	更新roles資料表內容


			//	更新roles_users資料表內容 || 更新roles_station_spec資料表內容


			$sql = sprintf(
				"DELETE 
								FROM [%s].[dbo].[roles_users] 
								WHERE [user_id] = :user_id",
				$this->database_trend
			);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'user_id' => $params['userID'],
			]);

			foreach ($role_arr as $role_id_arr) {
				$sql = sprintf("INSERT INTO [%s].[dbo].[roles_users] (
									 [role_id]
		      						,[user_id]
								) VALUES (
									 :role_id
									,:user_id
								);", $this->database_trend);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'role_id' => $role_id_arr,
					'user_id' => $params['userID'],
				]);

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

			$this->pdo_sql->commit();
			return true;
		} catch (Exception $mes) {
			$this->pdo_sql->rollBack();
			return $mes->getMessage();
		}
	}


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

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

		try {
			$this->pdo_sql->beginTransaction();

			//	查詢roles_station_spec有無此使用者資料，有刪除
			$sql = sprintf(
				"SELECT [role_id],[STN]
							FROM [%s].[dbo].[roles_station_spec] 
							WHERE [role_id] = :role_id;",
				$this->database_trend
			);
			$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_station_spec] 
								WHERE [role_id] = :role_id;",
					$this->database_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'role_id' => $role_id,
				]);
				if ($sth->rowCount() == 0) {
					throw new Exception("roles_station_spec刪除失敗");
				}
			} else {
			}

			//	查詢roles_menu有無此使用者資料，有刪除，但這似乎是多餘的動作 => roles_menu？？？
			$sql = sprintf(
				"SELECT [role_id],[menu_id]
							FROM [%s].[dbo].[roles_menu]
							WHERE [role_id] = :role_id;",
				$this->database_trend
			);
			$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_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'role_id' => $role_id,
				]);
				if ($sth->rowCount() == 0) {
					throw new Exception("roles_menu刪除失敗");
				}
			} else {
			}

			//	查詢roles_users有無此使用者資料，有刪除
			$sql = sprintf(
				"SELECT [role_id],[user_id]
							FROM [%s].[dbo].[roles_users]
							WHERE [role_id] = :role_id;",
				$this->database_trend
			);
			$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_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'user_id' => $params['userID'],
				]);
				if ($sth->rowCount() == 0) {
					throw new Exception("roles_users刪除失敗");
				}
			} else {
			}

			//	查詢roles有無此使用者資料，有刪除
			$sql = sprintf(
				"SELECT [role_id],[role_name]
							FROM [%s].[dbo].[roles]
							WHERE [role_id] = :role_id;",
				$this->database_trend
			);
			$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_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'role_id' => $role_id,
				]);
				if ($sth->rowCount() == 0) {
					throw new Exception("roles刪除失敗");
				}
			} else {
			}

			//	查詢users有無此使用者資料，有刪除
			$sql = sprintf(
				"SELECT [user_id]
							FROM [%s].[dbo].[users]
							WHERE [user_id] = :user_id;",
				$this->database_trend
			);
			$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_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'user_id' => $params['userID'],
				]);
				if ($sth->rowCount() == 0) {
					throw new Exception("users刪除失敗");
				}
			} else {
			}

			//	查詢users_page_setting有無此使用者資料，有刪除
			$sql = sprintf(
				"SELECT [user_id]
							FROM [%s].[dbo].[users_page_setting]
							WHERE [user_id] = :user_id;",
				$this->database_trend
			);
			$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_trend
				);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'user_id' => $params['userID'],
				]);
				if ($sth->rowCount() == 0) {
					throw new Exception("users_page_setting刪除失敗");
				}
			} else {
			}


			$this->pdo_sql->commit();
			return true;
		} catch (Exception $mes) {
			$this->pdo_sql->rollBack();
			return $mes->getMessage();
		}
	}
}
