<?php
	require_once dirname(__FILE__) . "/dalBase.php";
	class messageDAL extends dalBase {
		public $ID;
		public $TypeID;
		public $FromType;
		public $MemberID;
		public $LevelID;
		public $Title;
		public $Content;
		public $Visible;
		public $IsHot;
		public $IsRead;
		public $CreatedTime;
		public $CreatedIP;
		public $CreatedUserID;
		public $ModifiedTime;
		public $ModifiedIP;
		public $ModifiedUserID;
		//外部欄位
		public $TypeName;
		public $LoginName;
		public $MemberName;
		public $MemberGender;
		public $LevelName;
		public $CLName;
		public $CUName;
		public $MLName;
		public $MUName;

		public function loadRecord(): bool {
			$intoAry = [];
			$sql = "SELECT a.*,b.`TypeName`,c.`LoginName`,c.`MemberName`,c.`MemberGender`".
				",d.`LevelName`,e.`LoginName` AS `CLName`,e.`UserName` AS `CUName`".
				",f.`LoginName` AS `MLName`,f.`UserName` AS `MUName` FROM `message` a".
				" LEFT JOIN `message_type` b ON a.`TypeID`=b.`TypeID`".
				" LEFT JOIN `member` c ON a.`MemberID`=c.`MemberID`".
				" LEFT JOIN `level` d ON a.`LevelID`=d.`LevelID`".
				" LEFT JOIN `user` e ON a.`CreatedUserID`=e.`UserID`".
				" LEFT JOIN `user` f ON a.`ModifiedUserID`=f.`UserID` WHERE a.`ID`=:id";
			$intoAry[] = parent::addArray(":id", $this->ID, "int");
			$reader = $this->data->executeReader($sql, $intoAry);

			if (!empty($reader)) {
				$this->ID = $reader["ID"];
				$this->TypeID = $reader["TypeID"];
				$this->FromType = $reader["FromType"];
				$this->MemberID = $reader["MemberID"];
				$this->LevelID = $reader["LevelID"];
				$this->Title = $reader["Title"];
				$this->Content = $reader["Content"];
				$this->Visible = $reader["Visible"];
				$this->IsHot = $reader["IsHot"];
				$this->IsRead = $reader["IsRead"];
				$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->TypeName = $reader["TypeName"];
				$this->LoginName = $reader["LoginName"];
				$this->MemberName = $reader["MemberName"];
				$this->MemberGender = $reader["MemberGender"];
				$this->LevelName = $reader["LevelName"];
				$this->CLName = $reader["CLName"];
				$this->CUName = $reader["CUName"];
				$this->MLName = $reader["MLName"];
				$this->MUName = $reader["MUName"];
				return true;
			} else {
				return false;
			}
		}

		//新增
		public function insertRecord(): bool|int {
			$intoAry = [
				parent::addArray(":typeID", $this->TypeID, "int"),
				parent::addArray(":fromType", $this->FromType, "int"),
				parent::addArray(":memberID", $this->MemberID, "int"),
				parent::addArray(":levelID", $this->LevelID, "int"),
				parent::addArray(":title", $this->Title, "str", 200),
				parent::addArray(":content", $this->Content, "str", 6000),
				parent::addArray(":visible", $this->Visible, "int"),
				parent::addArray(":isHot", $this->IsHot, "int"),
				parent::addArray(":createdIP", $this->CreatedIP, "str", 50),
				parent::addArray(":createdUserID", $this->CreatedUserID, "int")
			];
			$sql = "INSERT INTO `message` (`TypeID`, `FromType`, `MemberID`, `LevelID`, `Title`, `Content`, `Visible`, `IsHot`, `IsRead`, `CreatedTime`, `CreatedIP`, `CreatedUserID`, `ModifiedTime`, `ModifiedIP`, `ModifiedUserID`)".
				" VALUES (:typeID, :fromType, :memberID, :levelID, :title, :content, :visible, :isHot, 0, UNIX_TIMESTAMP(), :createdIP, :createdUserID, 0, '', 0)";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return (!$this->data->error) ? $this->data->getInsertID() : false;
		}

		//讀取
		public function readRecord($id = 0): bool {
			$intoAry = [];
			$sql = "UPDATE `message` SET `IsRead`=1 WHERE `ID`=:id";
			$intoAry[] = parent::addArray(":id", $id, "int");
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//修改
		public function updateRecord(): bool {
			$intoAry = [
				parent::addArray(":id", $this->ID, "int"),
				parent::addArray(":title", $this->Title, "str", 200),
				parent::addArray(":content", $this->Content, "str", 6000),
				parent::addArray(":visible", $this->Visible, "int"),
				parent::addArray(":isHot", $this->IsHot, "int"),
				parent::addArray(":modifiedTime", $this->ModifiedTime, "int"),
				parent::addArray(":modifiedIP", $this->ModifiedIP, "str", 50),
				parent::addArray(":modifiedUserID", $this->ModifiedUserID, "int")
			];
			$sql = "UPDATE `message` SET ".
				"`Title`=:title".
				",`Content`=:content".
				",`Visible`=:visible".
				",`IsHot`=:isHot".
				",`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.`TypeName`,c.`LoginName`,c.`MemberName`,c.`MemberGender`".
				",d.`LevelName`,e.`LoginName` AS `CLName`,e.`UserName` AS `CUName`".
				",f.`LoginName` AS `MLName`,f.`UserName` AS `MUName` FROM `message` a".
				" LEFT JOIN `message_type` b ON a.`TypeID`=b.`TypeID`".
				" LEFT JOIN `member` c ON a.`MemberID`=c.`MemberID`".
				" LEFT JOIN `level` d ON a.`LevelID`=d.`LevelID`".
				" LEFT JOIN `user` e ON a.`CreatedUserID`=e.`UserID`".
				" LEFT JOIN `user` f ON a.`ModifiedUserID`=f.`UserID`";
			if ($filter !== "") $sql .= " WHERE $filter";
			$sql .= (!isset($order) || $order === "") ? " ORDER BY a.`ID`" : " 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 `message` a LEFT JOIN `message_type` b ON a.`TypeID`=b.`TypeID`".
				" LEFT JOIN `member` c ON a.`MemberID`=c.`MemberID`".
				" LEFT JOIN `level` d ON a.`LevelID`=d.`LevelID`".
				" LEFT JOIN `user` e ON a.`CreatedUserID`=e.`UserID`".
				" LEFT JOIN `user` f ON a.`ModifiedUserID`=f.`UserID`";
			if ($filter !== "") $sql .= " WHERE $filter";
			return $this->data->executeScalar($sql, $intoAry);
		}
	}
?>