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

	public function SelectUsers ($params) {
		$sql = "SELECT distinct _u.[user_id]
							      ,_u.[user_name]
							      ,_u.[enabled]
							      ,_u.[priv_no]
							      ,_u.[password]
							      ,_u.[email]
							      ,_u.[tel]
							      ,_u.[mobile]
								  , STUFF((select ',' + role_name from [v_roles_users] as inner_list
											where inner_list.user_id = _u.user_id AND inner_list.role_is_personal = 0
											for xml path('')),1,1,'')as role_name
  				FROM [HsinTungYang_Alarm].[dbo].[users] AS _u
  				LEFT JOIN　[HsinTungYang_Alarm].[dbo].[v_roles_users] AS _vu　ON _u.user_id = _vu.user_id ";
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute();
		return $sth->fetchAll(PDO::FETCH_ASSOC);
	}

	public function SelectAddUserInfoLoadingOptios ($params) {

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

	}

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

		$role_id = '$$user$$'.$params['userID'];
		$role_arr = $params['role']['value'];


		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]
							,[priv_no]
							,[password]
							,[email]
							,[tel]
							,[mobile]
						) VALUES (
							 :userID
							,:name
							, 1
							,:priv_no
							,:password
							,:email
							,:telephone
							,:cellphone
						);", $this->database_trend);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'userID' => $params['userID'],
				'name' => $params['name'],
				'priv_no' => $params['priv_no'],
				'password' => $params['password'],
				'email' => $params['email'],
				'telephone' => $params['telephone'],
				'cellphone' => $params['cellphone'],	
			]);

			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]
							,[is_ui_visible]
						) VALUES (
							 :role_id
							,:role_name
							, 1
							, 1
							, 1
							, 0
						);", $this->database_trend);
			$sth = $this->pdo_sql->prepare($sql);
			$sth->execute([
				'role_id' => $role_id,
				'role_name' => $params['userID'],
			]);

			if ( $sth->rowCount() == 0) {
				throw new Exception("roles資料表建立失敗");
			}
					
			//	新增roles_users資料表內容 
			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){

		$sql = sprintf("SELECT 	 [A].[role_id],[A].[user_id],[A].[user_name],[A].[email]
	  							 ,+STUFF((SELECT ','+CAST([role_id] AS varchar) FROM [%s].[dbo].[v_roles_users] WHERE [user_id] = [A].[user_id] AND [role_ui_hide] = 'false'  FOR XML PATH('')),1,1,'') AS role_name
	  							 ,+STUFF((SELECT ','+CAST([role_id] AS varchar) FROM [%s].[dbo].[v_roles_users] WHERE [user_id] = [A].[user_id] FOR XML PATH('')),1,1,'') AS role_name2
	  							 ,(SELECT [password] FROM [%s].[dbo].[users] WHERE [user_id] = [A].[user_id] ) AS password
	  							 ,(SELECT [enabled] FROM [%s].[dbo].[users] WHERE [user_id] = [A].[user_id] ) AS enabled
	  							 ,(SELECT [tel] FROM [%s].[dbo].[users] WHERE [user_id] = [A].[user_id] ) AS tel
	  							 ,(SELECT [mobile] FROM [%s].[dbo].[users] WHERE [user_id] = [A].[user_id] ) AS mobile
  						FROM [%s].[dbo].[v_roles_users] AS [A]
  						WHERE [A].[user_id] = :user_id 
  						GROUP BY [role_id],[user_id],[user_name],[email] "
						,$this->database_trend ,$this->database_trend ,$this->database_trend ,$this->database_trend 
						,$this->database_trend ,$this->database_trend ,$this->database_trend ,$this->database_trend);
		$sth = $this->pdo_sql->prepare($sql);
		$sth->execute([
			'user_id' => $params['user_id']
		]);
		return $sth->fetch(PDO::FETCH_ASSOC);
	}

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

		$role_arr = $params['role']['value'];
		$role_id = '$$user$$'.$params['userID'];

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


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


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

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

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

			//	更新roles資料表內容，有找到資料走更新。 沒有再插入一筆資料(但好像沒必要做此動作？
			//	2023/08/25 => 要得，如果是複製其他資料表裡面的資料，需要自動新增一筆
			$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]
								,[is_ui_visible]
							) VALUES (
								 :role_id
								,:role_name
								, 1
								, 1
								, 1
								, 0
							);", $this->database_trend);
				$sth = $this->pdo_sql->prepare($sql);
				$sth->execute([
					'role_id' => $role_id,
					'role_name' => $params['userID'],
				]);

				if ( $sth->rowCount() == 0) {
					throw new Exception("roles資料表建立失敗");
				}
			}
			
					
			//	更新roles_users資料表內容 ，先刪舊資料，再新增一次

			$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刪除all_stn失敗");
			}

			//	新增roles_users資料表內容 
			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'],
			]);


			$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_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();
		}

	}

}
?>