<?php
	require_once dirname(__FILE__) . "/dalBase.php";
	class member_redpointDAL extends dalBase {
		public $ID;
		public $MemberID;
		public $OrderID;
		public $RedPoint;
		public $Reason;
		public $IsDelete;
		public $CreatedTime;
		public $CreatedIP;
		public $CreatedUserID;
		public $ModifiedTime;
		public $ModifiedIP;
		public $ModifiedUserID;
		public $DeletedTime;
		public $DeletedIP;
		public $DeletedUserID;
		//外部欄位
		public $LoginName;
		public $MemberName;
		public $MemberGender;
		public $OrderNO;
		public $CLName;
		public $CUName;
		public $MLName;
		public $MUName;
		public $DLName;
		public $DUName;

		public function loadRecord(): bool {
			$intoAry = [];
			$sql = "SELECT a.*,b.`LoginName`,b.`MemberName`,b.`MemberGender`,c.`OrderNO`".
				",d.`LoginName` AS `CLName`,d.`UserName` AS `CUName`,e.`LoginName` AS `MLName`,e.`UserName` AS `MUName`".
				",f.`LoginName` AS `DLName`,f.`UserName` AS `DUName` FROM `member_redpoint` a".
				" LEFT JOIN `member` b ON a.`MemberID`=b.`MemberID`".
				" LEFT JOIN `order` c ON a.`OrderID`=c.`OrderID`".
				" LEFT JOIN `user` d ON a.`CreatedUserID`=d.`UserID`".
				" LEFT JOIN `user` e ON a.`ModifiedUserID`=e.`UserID`".
				" LEFT JOIN `user` f ON a.`DeletedUserID`=f.`UserID` WHERE ";
			if ($this->ID !== null) {
				$sql .= "a.`ID`=:id";
				$intoAry[] = parent::addArray(":id", $this->ID, "int");
			} else {
				$sql .= "1 = 2";
			}
			$reader = $this->data->executeReader($sql, $intoAry);

			if (!empty($reader)) {
				$this->ID = $reader["ID"];
				$this->MemberID = $reader["MemberID"];
				$this->OrderID = $reader["OrderID"];
				$this->RedPoint = $reader["RedPoint"];
				$this->Reason = $reader["Reason"];
				$this->IsDelete = $reader["IsDelete"];
				$this->CreatedTime = $reader["CreatedTime"];
				$this->CreatedIP = $reader["CreatedIP"];
				$this->CreatedUserID = $reader["CreatedUserID"];
				$this->ModifiedTime = $reader["ModifiedTime"];
				$this->ModifiedIP = $reader["ModifiedIP"];
				$this->ModifiedUserID = $reader["ModifiedUserID"];
				$this->DeletedTime = $reader["DeletedTime"];
				$this->DeletedIP = $reader["DeletedIP"];
				$this->DeletedUserID = $reader["DeletedUserID"];
				//外部欄位
				$this->LoginName = $reader["LoginName"];
				$this->MemberName = $reader["MemberName"];
				$this->MemberGender = $reader["MemberGender"];
				$this->OrderNO = $reader["OrderNO"];
				$this->CLName = $reader["CLName"];
				$this->CUName = $reader["CUName"];
				$this->MLName = $reader["MLName"];
				$this->MUName = $reader["MUName"];
				$this->DLName = $reader["DLName"];
				$this->DUName = $reader["DUName"];
				return true;
			} else {
				return false;
			}
		}

		//新增
		public function insertRecord(): bool|int {
			$intoAry = [
				parent::addArray(":memberID", $this->MemberID, "int"),
				parent::addArray(":orderID", $this->OrderID, "int"),
				parent::addArray(":redPoint", $this->RedPoint, "int"),
				parent::addArray(":reason", $this->Reason, "str", 200),
				parent::addArray(":createdIP", $this->CreatedIP, "str", 50),
				parent::addArray(":createdUserID", $this->CreatedUserID, "int")
			];
			$sql = "INSERT INTO `member_redpoint` (`MemberID`, `OrderID`, `RedPoint`, `Reason`, `IsDelete`, `CreatedTime`, `CreatedIP`, `CreatedUserID`, `ModifiedTime`, `ModifiedIP`, `ModifiedUserID`, `DeletedTime`, `DeletedIP`, `DeletedUserID`)".
				" VALUES (:memberID, :orderID, :redPoint, :reason, 0, UNIX_TIMESTAMP(), :createdIP, :createdUserID, 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(":id", $this->ID, "int"),
				parent::addArray(":redPoint", $this->RedPoint, "int"),
				parent::addArray(":reason", $this->Reason, "str", 200),
				parent::addArray(":modifiedTime", $this->ModifiedTime, "int"),
				parent::addArray(":modifiedIP", $this->ModifiedIP, "str", 50),
				parent::addArray(":modifiedUserID", $this->ModifiedUserID, "int")
			];
			$sql = "UPDATE `member_redpoint` SET ".
				"`RedPoint`=:redPoint".
				",`Reason`=:reason".
				",`ModifiedTime`=:modifiedTime".
				",`ModifiedIP`=:modifiedIP".
				",`ModifiedUserID`=:modifiedUserID".
				" WHERE `ID`=:id";
			$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.`LoginName`,b.`MemberName`,b.`MemberGender`,c.`OrderNO`".
				",d.`LoginName` AS `CLName`,d.`UserName` AS `CUName`,e.`LoginName` AS `MLName`,e.`UserName` AS `MUName`".
				",f.`LoginName` AS `DLName`,f.`UserName` AS `DUName` FROM `member_redpoint` a".
				" LEFT JOIN `member` b ON a.`MemberID`=b.`MemberID`".
				" LEFT JOIN `order` c ON a.`OrderID`=c.`OrderID`".
				" LEFT JOIN `user` d ON a.`CreatedUserID`=d.`UserID`".
				" LEFT JOIN `user` e ON a.`ModifiedUserID`=e.`UserID`".
				" LEFT JOIN `user` f ON a.`DeletedUserID`=f.`UserID`";
			if ($filter !== "") $sql .= " WHERE $filter";
			$sql .= (!isset($order) || $order === "") ? " ORDER BY a.`ID` 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_redpoint` a LEFT JOIN `member` b ON a.`MemberID`=b.`MemberID`".
				" LEFT JOIN `order` c ON a.`OrderID`=c.`OrderID`".
				" LEFT JOIN `user` d ON a.`CreatedUserID`=d.`UserID`".
				" LEFT JOIN `user` e ON a.`ModifiedUserID`=e.`UserID`".
				" LEFT JOIN `user` f ON a.`DeletedUserID`=f.`UserID`";
			if ($filter !== "") $sql .= " WHERE $filter";
			return $this->data->executeScalar($sql, $intoAry);
		}

		//刪除
		public function deleteRecord(int $id = 0, string $ip, int $uid = 0): bool {
			$intoAry = [
				parent::addArray(":id", $id, "int"),
				parent::addArray(":deletedIP", $ip, "str", 50),
				parent::addArray(":deletedUserID", $uid, "int")
			];
			$sql = "UPDATE `member_redpoint` SET `IsDelete`=1, `DeletedTime`=UNIX_TIMESTAMP(), `DeletedIP`=:deletedIP, `DeletedUserID`=:deletedUserID WHERE `ID`=:id";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}
	}
?>