<?php
	require_once dirname(__FILE__) . "/dalBase.php";
	class userDAL extends dalBase {
		//27個字段
		public $UserID;					//用戶ID
		public $LoginName;				//登陸名稱
		public $Password;				//登陸密碼
		public $UserName;				//登陸密碼
		public $Email;					//
		public $Visible=1;				//是否啟用
		public $AutoEmail;				
		public $UserType=1;				//用戶類型.1為會員,64為管理員,128為超級管理員
		public $Flags=0;				//記錄標識.0為正常,1為鎖定
		public $LoginTimes=0;			//登陸次數
		public $LastLoginIp;			//上次登錄ID
		public $LastLoginTime;			//刪除登陸時間
		public $CreatedTime;
		public $ModifiedTime;
		public $LineUID;				//Line登入userid	
		public $LineName;				//Line名稱
		public $LinePicture;			//Line照片
		public $PathName;				//user唯一值
		public $Gmail;					//Gmail
		public $FbAccount;				//FB登入帳號
		//權限陣列
		public $LimitArray = [];

		//查詢用戶
		public function loadRecord(): bool {
			$intoAry = [];
			$sql = "SELECT * FROM user";
			if (isset($this->UserID) && $this->UserID !== "") {
				$sql .= " WHERE UserID=:userID";
				$intoAry[] = parent::addArray(":userID", $this->UserID, "int");
			} else if (isset($this->LoginName) && $this->LoginName !== "") {
				$sql .= " WHERE LoginName=:loginName";
				$intoAry[] = parent::addArray(":loginName", $this->LoginName, "str", 255);
			} else if (isset($this->Email) && $this->Email !== "") {
				$sql .= " WHERE Email=:email";
				$intoAry[] = parent::addArray(":email", $this->Email, "str", 64);
			} else if (isset($this->LineUID) && $this->LineUID !== "") {
				$sql .= " WHERE LineUID=:lineUID";
				$intoAry[] = parent::addArray(":lineUID", $this->LineUID, "str", 50);
			} else if (isset($this->PathName) && $this->PathName !== "") {
				$sql .= " WHERE PathName=:pathName";
				$intoAry[] = parent::addArray(":pathName", $this->PathName, "str", 50);
			} else if (isset($this->Gmail)) {
				$sql .= " WHERE Gmail=:gmail";
				$intoAry[] = parent::addArray(":gmail", $this->Gmail, "str", 255);
			} else if (isset($this->FbAccount)) {
				$sql .= " WHERE `FbAccount`=:fbAccount";
				$intoAry[] = parent::addArray(":fbAccount", $this->FbAccount, "str", 255);
			} else {
				$sql .= " WHERE 1 = 2";
			}
			$reader = $this->data->executeReader($sql, $intoAry);

			if (!empty($reader)) {
				$this->UserID = $reader["UserID"];
				$this->LoginName = $reader["LoginName"];
				$this->Password = $reader["Password"];
				$this->UserName = $reader["UserName"];
				$this->Email = $reader["Email"];
				$this->Visible = $reader["Visible"];
				$this->AutoEmail = $reader["AutoEmail"];
				$this->UserType = $reader["UserType"];
				$this->Flags = $reader["Flags"];
				$this->LoginTimes = $reader["LoginTimes"];
				$this->LastLoginIp = $reader["LastLoginIp"];
				$this->LastLoginTime = $reader["LastLoginTime"];
				$this->CreatedTime = $reader["CreatedTime"];
				$this->ModifiedTime = $reader["ModifiedTime"];
				$this->LineUID = $reader["LineUID"];
				$this->LineName = $reader["LineName"];
				$this->LinePicture = $reader["LinePicture"];
				$this->PathName = $reader["PathName"];
				$this->Gmail = $reader["Gmail"];
				$this->FbAccount = $reader["FbAccount"];
				
				$into2Ary = [];
				$sql = "SELECT b.`MenuID`,b.`MenuName` FROM `limit` a INNER JOIN `menu` b ON a.`MenuID`=b.`MenuID` WHERE a.`UserID`=:userID AND b.`Visible`=1 AND b.`IsOA`=0 ORDER BY a.`ID`";
				$into2Ary[] = parent::addArray(":userID", $this->UserID, "int");
				$this->LimitArray = $this->data->executeRecordset($sql, $into2Ary, 0, -1);
				return true;
			} else {
				return false;
			}
		}

		//插入用戶.插入成功后,返回用戶ID,否則返回false
		public function insertRecord(): bool|int {
			//開始事務
			$this->data->beginTransaction();

			$intoAry = [
				parent::addArray(":loginName", $this->LoginName, "str", 255),
				parent::addArray(":password", $this->Password, "str", 64),
				parent::addArray(":userName", $this->UserName, "str", 64),
				parent::addArray(":email", $this->Email, "str", 64),
				parent::addArray(":visible", $this->Visible, "int"),
				parent::addArray(":autoEmail", $this->AutoEmail, "int"),
				parent::addArray(":userType", $this->UserType, "int"),
				parent::addArray(":flags", $this->Flags, "int"),
				parent::addArray(":loginTimes", $this->LoginTimes, "int"),
				parent::addArray(":lastLoginIp", $this->LastLoginIp, "str", 50),
				parent::addArray(":lastLoginTime", $this->LastLoginTime, "str"),
				parent::addArray(":lineUID", $this->LineUID, "str", 50),
				parent::addArray(":lineName", $this->LineName, "str", 60),
				parent::addArray(":linePicture", $this->LinePicture, "str", 128),
				parent::addArray(":pathName", $this->PathName, "str", 50),
				parent::addArray(":gmail", $this->Gmail, "str", 255),
				parent::addArray(":fbAccount", $this->FbAccount, "str", 255)
			];
			$sql = "INSERT INTO `user` (`LoginName`, `Password`, `UserName`, `Email`, `Visible`, `AutoEmail`, `UserType`, `Flags`, `LoginTimes`, `LastLoginIp`, `LastLoginTime`, `CreatedTime`, `ModifiedTime`, `LineUID`, `LineName`, `LinePicture`, `PathName`, `Gmail`, `FbAccount`)".
				" VALUES (:loginName, :password, :userName, :email, :visible, :autoEmail, :userType, :flags, :loginTimes, :lastLoginIp, :lastLoginTime, NOW(), NOW(), :lineUID, :lineName, :linePicture, :pathName, :gmail, :fbAccount)";
			$this->data->executeNonQuery($sql, $intoAry, true);
			$insertID = $this->data->getInsertID();

			if (is_array($this->LimitArray) && count($this->LimitArray) > 0) {
				foreach ($this->LimitArray as $rows) {
					$into2Ary = [
						parent::addArray(":userID", $insertID, "int"),
						parent::addArray(":menuID", $rows["MenuID"], "int")
					];
					$sql = "INSERT INTO `limit` (`UserID`, `MenuID`) VALUES (:userID, :menuID)";
					$this->data->executeNonQuery($sql, $into2Ary, true);
				}
			}

			if (!$this->data->error) {
				$this->data->commitTransaction();
				return $insertID;
			} else {
				$this->data->rollbackTransaction();
				return false;
			}
		}

		//更新用戶信息
		public function updateRecord(): bool {
			// 開始事務
			$this->data->beginTransaction();

			$intoAry = [
				parent::addArray(":userID", $this->UserID, "int"),
				parent::addArray(":loginName", $this->LoginName, "str", 255),
				parent::addArray(":password", $this->Password, "str", 64),
				parent::addArray(":userName", $this->UserName, "str", 64),
				parent::addArray(":email", $this->Email, "str", 64),
				parent::addArray(":visible", $this->Visible, "int"),
				parent::addArray(":autoEmail", $this->AutoEmail, "int"),
				parent::addArray(":userType", $this->UserType, "int"),
				parent::addArray(":flags", $this->Flags, "int"),
				parent::addArray(":loginTimes", $this->LoginTimes, "int"),
				parent::addArray(":lastLoginIp", $this->LastLoginIp, "str", 50),
				parent::addArray(":lastLoginTime", $this->LastLoginTime, "str"),
				parent::addArray(":lineUID", $this->LineUID, "str", 50),
				parent::addArray(":lineName", $this->LineName, "str", 60),
				parent::addArray(":linePicture", $this->LinePicture, "str", 128),
				parent::addArray(":pathName", $this->PathName, "str", 50),
				parent::addArray(":gmail", $this->Gmail, "str", 255),
				parent::addArray(":fbAccount", $this->FbAccount, "str", 255)
			];
			$sql = "UPDATE `user` SET ".
				"`LoginName`=:loginName".
				",`Password`=:password".
				",`UserName`=:userName".
				",`Email`=:email".
				",`Visible`=:visible".
				",`AutoEmail`=:autoEmail".
				",`UserType`=:userType".
				",`Flags`=:flags".
				",`LoginTimes`=:loginTimes".
				",`LastLoginIp`=:lastLoginIp".
				",`LastLoginTime`=:lastLoginTime".
				",`ModifiedTime`=NOW()".
				",`LineUID`=:lineUID".
				",`LineName`=:lineName".
				",`LinePicture`=:linePicture".
				",`PathName`=:pathName".
				",`Gmail`=:gmail".
				",`FbAccount`=:fbAccount".
				" WHERE `UserID`=:userID";
			$this->data->executeNonQuery($sql, $intoAry, true);

			$into2Ary = [];
			$sql = "DELETE FROM `limit` WHERE `UserID`=:userID";
			$into2Ary[] = parent::addArray(":userID", $this->UserID, "int");
			$this->data->executeNonQuery($sql, $into2Ary, true);

			if (is_array($this->LimitArray) && count($this->LimitArray) > 0) {
				foreach ($this->LimitArray as $rows) {
					$into3Ary = [
						parent::addArray(":userID", $this->UserID, "int"),
						parent::addArray(":menuID", $rows["MenuID"], "int")
					];
					$sql = "INSERT INTO `limit` (`UserID`, `MenuID`) VALUES (:userID, :menuID)";
					$this->data->executeNonQuery($sql, $into3Ary, true);
				}
			}

			if (!$this->data->error) {
				$this->data->commitTransaction();
				return true;
			} else {
				$this->data->rollbackTransaction();
				return false;
			}
		}

		//查詢
		public function executeRecordset(array $intoAry = [], string $filter = "", string $order = "", int $start = 0, int $length = -1): array {
			$sql = "SELECT a.* FROM `user` a";
			if ($filter !== "") $sql .= " WHERE $filter";
			$sql .= (!isset($order) || $order === "") ? " ORDER BY a.`CreatedTime` DESC" : " ORDER BY $order";
			$length = is_int($length) ? $length : -1;
			return $this->data->executeRecordset($sql, $intoAry, $start, $length);
		}

		//查詢計數
		public function countRecordset(array $intoAry = [], string $filter = ""): int {
			$sql = "SELECT COUNT(*) FROM `user` a";
			if ($filter !== "") $sql .= " WHERE $filter";
			return $this->data->executeScalar($sql, $intoAry);
		}

		//刪除
		public function deleteRecord(int $id): bool {
			$intoAry = [
				parent::addArray(":userID", $id, "int"),
			];
			$sql = "DELETE a,b FROM `user` a INNER JOIN `limit` b ON a.`UserID`=b.`UserID` WHERE a.`UserID`=:userID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//批量刪除，傳入參數為用逗號分隔的id镩
		public function batDelete(string $ids): bool {
			$sql = "DELETE a,b FROM `user` a INNER JOIN `limit` b ON a.`UserID`=b.`UserID` WHERE a.`UserID` IN ($ids)";
			$this->data->executeNonQuery($sql, [], true);
			return !$this->data->error;
		}

		//批量修改用戶啟用狀態
		public function batUpdateVisible(string $ids, int $value = 1): bool {
			$intoAry = [
				parent::addArray(":flags", $value, "int")
			];
			$sql = "UPDATE user SET Flags=:flags WHERE UserID IN ($ids)";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//檢查Email唯一性
		public function checkEmailExists(string $email): bool {
			$intoAry = [
				parent::addArray(":email", $email, "str", 64),
				parent::addArray(":userID", $this->UserID, "int")
			];
			$sql = "SELECT COUNT(*) FROM `user` WHERE `Email`=:email AND `UserID`!=:userID";
			$num = $this->data->executeScalar($sql, $intoAry);
			return ($num > 0) ? true : false;
		}
		
		//檢查Gmail的唯一性
		public function checkGmailExists(string $gmail): bool {
			if (empty($gmail)) return false;
		
			$intoAry = [
				parent::addArray(":gmail", $gmail, "str", 255),
				parent::addArray(":userName", '超級管理員', "str", 255)
			];
			$sql = "SELECT `UserID`, `Gmail` FROM `user` WHERE Gmail=:gmail AND `UserName` <> :userName";
			$userRow = $this->data->executeReader($sql, $intoAry);
			// 如果Gmail存在要判斷是否為自己使用
			if ($userRow !== false && isset($this->UserID)) return intval($userRow['UserID']) !== intval($this->UserID);
			return $userRow !== false;
		}
		
		//檢查LineID唯一性
		public function checkLineUIDExists(string $lineUID): bool {
			$intoAry = [
				parent::addArray(":lineUID", $lineUID, "str", 50),
				parent::addArray(":userID", $this->UserID, "int")
			];
			$sql = "SELECT COUNT(*) FROM `user` WHERE LineUID=:lineUID AND UserID!=:userID";
			$num = $this->data->executeScalar($sql, $intoAry);
			return ($num > 0) ? true : false;
		}

		//檢查FB登入帳號唯一性
		public function checkFbAccountExists(string $fbAccount): bool {
			$intoAry = [
				parent::addArray(":fbAccount", $fbAccount, "str", 255),
				parent::addArray(":userID", $this->UserID, "int")
			];
			$sql = "SELECT COUNT(*) FROM `user` WHERE `FbAccount`=:fbAccount AND `UserID`!=:userID";
			$num = $this->data->executeScalar($sql, $intoAry);
			return ($num > 0) ? true : false;
		}
	}
?>