<?php
	require_once dirname(__FILE__) . "/dalBase.php";
	class memberDAL extends dalBase {
		public $MemberID;
		public $LevelID;
		public $LoginName;
		public $LoginPass;
		public $MemberNick;
		public $MemberName;
		public $MemberGender;
		public $MemberEmail;
		public $MemberMobile;
		public $MemberCityID;
		public $MemberAreaID;
		public $MemberAddress;
		public $MemberPhone;
		public $MemberLine;
		public $CheckCode;
		public $Status;
		public $CreatedTime;
		public $CreatedIP;
		public $CheckedTime;
		public $CheckedIP;
		public $LastLoginTime;
		public $LastLoginIP;
		public $ModifiedTime;
		public $ModifiedIP;
		public $ModifiedUserID;
		public $LevelUpTime;
		public $LevelUpIP;
		public $LevelUpUserID;
		//外部欄位
		public $LevelName;
		public $LevelDiscount;
		public $CityName;
		public $AreaName;
		public $ZipCode;
		public $ModifiedLoginName;
		public $ModifiedUserName;
		public $LevelUpLoginName;
		public $LevelUpUserName;

		public function loadRecord(): bool {
			$intoAry = [];
			$sql = "SELECT a.*,b.`LevelName`,b.`LevelDiscount`,c.`LoginName` AS `ModifiedLoginName`,c.`UserName` AS `ModifiedUserName`".
				",d.`LoginName` AS `LevelUpLoginName`,d.`UserName` AS `LevelUpUserName`".
				",e.`CityName`,f.`AreaName`,f.`ZipCode` FROM `member` a".
				" LEFT JOIN `level` b ON a.`LevelID`=b.`LevelID`".
				" LEFT JOIN `user` c ON a.`ModifiedUserID`=c.`UserID`".
				" LEFT JOIN `user` d ON a.`LevelUpUserID`=d.`UserID`".
				" LEFT JOIN `city` e ON a.`MemberCityID`=e.`CityID`".
				" LEFT JOIN `area` f ON a.`MemberAreaID`=f.`AreaID` WHERE ";
			if ($this->MemberID !== null) {
				$sql .= "a.`MemberID`=:memberID";
				$intoAry[] = parent::addArray(":memberID", $this->MemberID, "int");
			} else {
				$sql .= "1 = 2";
			}
			$reader = $this->data->executeReader($sql, $intoAry);

			if (!empty($reader)) {
				$this->MemberID = $reader["MemberID"];
				$this->LevelID = $reader["LevelID"];
				$this->LoginName = $reader["LoginName"];
				$this->LoginPass = $reader["LoginPass"];
				$this->MemberNick = $reader["MemberNick"];
				$this->MemberName = $reader["MemberName"];
				$this->MemberGender = $reader["MemberGender"];
				$this->MemberEmail = $reader["MemberEmail"];
				$this->MemberMobile = $reader["MemberMobile"];
				$this->MemberCityID = $reader["MemberCityID"];
				$this->MemberAreaID = $reader["MemberAreaID"];
				$this->MemberAddress = $reader["MemberAddress"];
				$this->MemberPhone = $reader["MemberPhone"];
				$this->MemberLine = $reader["MemberLine"];
				$this->CheckCode = $reader["CheckCode"];
				$this->Status = $reader["Status"];
				$this->CreatedTime = $reader["CreatedTime"];
				$this->CreatedIP = $reader["CreatedIP"];
				$this->CheckedTime = $reader["CheckedTime"];
				$this->CheckedIP = $reader["CheckedIP"];
				$this->LastLoginTime = $reader["LastLoginTime"];
				$this->LastLoginIP = $reader["LastLoginIP"];
				$this->ModifiedTime = $reader["ModifiedTime"];
				$this->ModifiedIP = $reader["ModifiedIP"];
				$this->ModifiedUserID = $reader["ModifiedUserID"];
				$this->LevelUpTime = $reader["LevelUpTime"];
				$this->LevelUpIP = $reader["LevelUpIP"];
				$this->LevelUpUserID = $reader["LevelUpUserID"];
				//外部欄位
				$this->LevelName = $reader["LevelName"];
				$this->LevelDiscount = $reader["LevelDiscount"];
				$this->CityName = $reader["CityName"];
				$this->AreaName = $reader["AreaName"];
				$this->ZipCode = $reader["ZipCode"];
				$this->ModifiedLoginName = $reader["ModifiedLoginName"];
				$this->ModifiedUserName = $reader["ModifiedUserName"];
				$this->LevelUpLoginName = $reader["LevelUpLoginName"];
				$this->LevelUpUserName = $reader["LevelUpUserName"];
				return true;
			} else {
				return false;
			}
		}

		//註冊
		public function insertRecord(): bool|int {
			$intoAry = [
				parent::addArray(":loginName", $this->LoginName, "str", 200),
				parent::addArray(":loginPass", $this->LoginPass, "str", 200),
				parent::addArray(":memberNick", $this->MemberNick, "str", 50),
				parent::addArray(":memberName", $this->MemberName, "str", 50),
				parent::addArray(":memberGender", $this->MemberGender, "int"),
				parent::addArray(":memberEmail", $this->MemberEmail, "str", 200),
				parent::addArray(":memberMobile", $this->MemberMobile, "str", 20),
				parent::addArray(":memberCityID", $this->MemberCityID, "int"),
				parent::addArray(":memberAreaID", $this->MemberAreaID, "int"),
				parent::addArray(":memberAddress", $this->MemberAddress, "str", 200),
				parent::addArray(":memberPhone", $this->MemberPhone, "str", 20),
				parent::addArray(":memberLine", $this->MemberLine, "str", 50),
				parent::addArray(":checkCode", $this->CheckCode, "str", 10),
				parent::addArray(":createdIP", $this->CreatedIP, "str", 50)
			];
			$sql = "INSERT INTO `member` (`LevelID`, `LoginName`, `LoginPass`, `MemberNick`, `MemberName`, `MemberGender`, `MemberEmail`, `MemberMobile`, `MemberCityID`, `MemberAreaID`, `MemberAddress`, `MemberPhone`, `MemberLine`, `CheckCode`, `Status`, `CreatedTime`, `CreatedIP`, `CheckedTime`, `CheckedIP`, `LastLoginTime`, `LastLoginIP`, `ModifiedTime`, `ModifiedIP`, `ModifiedUserID`, `LevelUpTime`, `LevelUpIP`, `LevelUpUserID`)".
				" VALUES (1, :loginName, :loginPass, :memberNick, :memberName, :memberGender, :memberEmail, :memberMobile, :memberCityID, :memberAreaID, :memberAddress, :memberPhone, :memberLine, :checkCode, 0, UNIX_TIMESTAMP(), :createdIP, 0, '' , 0, '', 0, '', 0, 0, '', 0)";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return (!$this->data->error) ? $this->data->getInsertID() : false;
		}

		//更新
		public function updateRecord(): bool {
			$intoAry = [
				parent::addArray(":memberID", $this->MemberID, "int"),
				parent::addArray(":memberNick", $this->MemberNick, "str", 50),
				parent::addArray(":memberName", $this->MemberName, "str", 50),
				parent::addArray(":memberGender", $this->MemberGender, "int"),
				parent::addArray(":memberEmail", $this->MemberEmail, "str", 200),
				parent::addArray(":memberMobile", $this->MemberMobile, "str", 20),
				parent::addArray(":memberCityID", $this->MemberCityID, "int"),
				parent::addArray(":memberAreaID", $this->MemberAreaID, "int"),
				parent::addArray(":memberAddress", $this->MemberAddress, "str", 200),
				parent::addArray(":memberPhone", $this->MemberPhone, "str", 20),
				parent::addArray(":memberLine", $this->MemberLine, "str", 50),
				parent::addArray(":status", $this->Status, "int"),
				parent::addArray(":modifiedTime", $this->ModifiedTime, "int"),
				parent::addArray(":modifiedIP", $this->ModifiedIP, "str", 50),
				parent::addArray(":modifiedUserID", $this->ModifiedUserID, "int")
			];
			$sql = "UPDATE `member` SET ".
				"`MemberNick`=:memberNick".
				",`MemberName`=:memberName".
				",`MemberGender`=:memberGender".
				",`MemberEmail`=:memberEmail".
				",`MemberMobile`=:memberMobile".
				",`MemberCityID`=:memberCityID".
				",`MemberAreaID`=:memberAreaID".
				",`MemberAddress`=:memberAddress".
				",`MemberPhone`=:memberPhone".
				",`MemberLine`=:memberLine".
				",`Status`=:status".
				",`ModifiedTime`=:modifiedTime".
				",`ModifiedIP`=:modifiedIP".
				",`ModifiedUserID`=:modifiedUserID".
				" WHERE `MemberID`=:memberID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//驗證
		public function checkedRecord(): bool {
			$intoAry = [
				parent::addArray(":memberID", $this->MemberID, "int"),
				parent::addArray(":status", $this->Status, "int"),
				parent::addArray(":checkedIP", $this->CheckedIP, "str", 50)
			];
			$sql = "UPDATE `member` SET `Status`=:status, `CheckedTime`=UNIX_TIMESTAMP(), `CheckedIP`=:checkedIP WHERE `MemberID`=:memberID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//登入
		public function loginRecord(): bool {
			$intoAry = [
				parent::addArray(":memberID", $this->MemberID, "int"),
				parent::addArray(":lastLoginIP", $this->LastLoginIP, "str", 50)
			];
			$sql = "UPDATE `member` SET `LastLoginTime`=UNIX_TIMESTAMP(), `LastLoginIP`=:lastLoginIP WHERE `MemberID`=:memberID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//等級變更
		public function updateLevelRecord(): bool {
			$intoAry = [
				parent::addArray(":memberID", $this->MemberID, "int"),
				parent::addArray(":levelID", $this->LevelID, "int"),
				parent::addArray(":levelUpIP", $this->LevelUpIP, "str", 50),
				parent::addArray(":levelUpUserID", $this->LevelUpUserID, "int")
			];
			$sql = "UPDATE `member` SET `LevelID`=:levelID, `LevelUpTime`=UNIX_TIMESTAMP(), `LevelUpIP`=:levelUpIP, `LevelUpUserID`=:levelUpUserID WHERE `MemberID`=:memberID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//變更密碼
		public function changePassRecord(): bool {
			$intoAry = [
				parent::addArray(":memberID", $this->MemberID, "int"),
				parent::addArray(":loginPass", $this->LoginPass, "str", 200),
				parent::addArray(":modifiedIP", $this->ModifiedIP, "str", 50),
				parent::addArray(":modifiedUserID", $this->ModifiedUserID, "int")
			];
			$sql = "UPDATE `member` SET `LoginPass`=:loginPass, `ModifiedTime`=UNIX_TIMESTAMP(), `ModifiedIP`=:modifiedIP, `ModifiedUserID`=:modifiedUserID WHERE `MemberID`=:memberID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//查詢
		public function executeRecordset(array $intoAry = [], string $filter = "", string $order = "", int $start = 0, int $length = -1): array {
			$sql = "SELECT a.*,b.`LevelName`,b.`LevelDiscount`,c.`LoginName` AS `ModifiedLoginName`,c.`UserName` AS `ModifiedUserName`".
				",d.`LoginName` AS `LevelUpLoginName`,d.`UserName` AS `LevelUpUserName`".
				",e.`CityName`,f.`AreaName`,f.`ZipCode` FROM `member` a".
				" LEFT JOIN `level` b ON a.`LevelID`=b.`LevelID`".
				" LEFT JOIN `user` c ON a.`ModifiedUserID`=c.`UserID`".
				" LEFT JOIN `user` d ON a.`LevelUpUserID`=d.`UserID`".
				" LEFT JOIN `city` e ON a.`MemberCityID`=e.`CityID`".
				" LEFT JOIN `area` f ON a.`MemberAreaID`=f.`AreaID`";
			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 `member` a LEFT JOIN `level` b ON a.`LevelID`=b.`LevelID`".
				" LEFT JOIN `user` c ON a.`ModifiedUserID`=c.`UserID`".
				" LEFT JOIN `user` d ON a.`LevelUpUserID`=d.`UserID`".
				" LEFT JOIN `city` e ON a.`MemberCityID`=e.`CityID`".
				" LEFT JOIN `area` f ON a.`MemberAreaID`=f.`AreaID`";
			if ($filter !== "") $sql .= " WHERE $filter";
			return $this->data->executeScalar($sql, $intoAry);
		}
	}
?>