<?php
	require_once dirname(__FILE__) . "/dalBase.php";
	class scoreDAL extends dalBase {
		public $ID;
		public $MemberID;
		public $TargetType;
		public $TargetID;
		public $Score;
		public $Comment;
		public $ReMessage;
		public $Visible;
		public $CreatedTime;
		public $CreatedIP;
		public $ReplyTime;
		public $ReplyIP;
		public $ReplyUserID;
		//外部欄位
		public $MemberName;
		public $MemberGender;
		public $RLName;
		public $RUName;

		public function loadRecord(): bool {
			$intoAry = [];
			$sql = "SELECT a.*,b.`MemberName`,b.`MemberGender`,c.`LoginName` AS `RLName`,c.`UserName` AS `RUName` FROM `score` a INNER JOIN `member` b ON a.`MemberID`=b.`MemberID` LEFT JOIN `user` c ON a.`ReplyUserID`=c.`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->MemberID = $reader["MemberID"];
				$this->TargetType = $reader["TargetType"];
				$this->TargetID = $reader["TargetID"];
				$this->Score = $reader["Score"];
				$this->Comment = $reader["Comment"];
				$this->ReMessage = $reader["ReMessage"];
				$this->Visible = $reader["Visible"];
				$this->CreatedTime = $reader["CreatedTime"];
				$this->CreatedIP = $reader["CreatedIP"];
				$this->ReplyTime = $reader["ReplyTime"];
				$this->ReplyIP = $reader["ReplyIP"];
				$this->ReplyUserID = $reader["ReplyUserID"];
				//外部欄位
				$this->MemberName = $reader["MemberName"];
				$this->MemberGender = $reader["MemberGender"];
				$this->RLName = $reader["RLName"];
				$this->RUName = $reader["RUName"];
				return true;
			} else {
				return false;
			}
		}

		//新增
		public function insertRecord(): bool|int {
			$intoAry = [
				parent::addArray(":memberID", $this->MemberID, "int"),
				parent::addArray(":targetType", $this->TargetType, "int"),
				parent::addArray(":targetID", $this->TargetID, "int"),
				parent::addArray(":score", $this->Score, "int"),
				parent::addArray(":comment", $this->Comment, "str", 3000),
				parent::addArray(":reMessage", $this->ReMessage, "str", 3000),
				parent::addArray(":createdIP", $this->CreatedIP, "str", 50)
			];
			$sql = "INSERT INTO `score` (`MemberID`, `TargetType`, `TargetID`, `Score`, `Comment`, `ReMessage`, `Visible`, `CreatedTime`, `CreatedIP`, `ReplyTime`, `ReplyIP`, `ReplyUserID`)".
				" VALUES (:memberID, :targetType, :targetID, :score, :comment, :reMessage, 1, UNIX_TIMESTAMP(), :createdIP, 0, '', 0)";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return (!$this->data->error) ? $this->data->getInsertID() : false;
		}

		//回覆
		public function replyRecord(): bool {
			$intoAry = [
				parent::addArray(":id", $this->ID, "int"),
				parent::addArray(":reMessage", $this->ReMessage, "str", 3000),
				parent::addArray(":replyIP", $this->ReplyIP, "str", 50),
				parent::addArray(":replyUserID", $this->ReplyUserID, "int")
			];
			$sql = "UPDATE `score` SET `ReMessage`=:reMessage,`ReplyTime`=UNIX_TIMESTAMP(),`ReplyIP`=:replayIP,`ReplyUserID`=:replyUserID WHERE `ID`=:id";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//上下架
		public function updateRecord(): bool {
			$intoAry = [
				parent::addArray(":id", $this->ID, "int"),
				parent::addArray(":visible", $this->Visible, "int")
			];
			$sql = "UPDATE `score` SET `Visible`=:visible 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.`MemberName`,b.`MemberGender`,c.`LoginName` AS `RLName`,c.`UserName` AS `RUName` FROM `score` a INNER JOIN `member` b ON a.`MemberID`=b.`MemberID` LEFT JOIN `user` c ON a.`ReplyUserID`=c.`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 `score` a INNER JOIN `member` b ON a.`MemberID`=b.`MemberID` LEFT JOIN `user` c ON a.`ReplyUserID`=c.`UserID`";
			if ($filter !== "") $sql .= " WHERE $filter";
			return $this->data->executeScalar($sql, $intoAry);
		}
		//查詢分數
		public function getScore(): float {
			$intoAry = [
				parent::addArray(":targetType", $this->TargetType, "int"),
				parent::addArray(":targetID", $this->TargetID, "int")
			];
			$sql = "SELECT ROUND(IFNULL((SUM(`Score`) / COUNT(*)), 0) * 10) AS `ScoreTen` FROM `score` WHERE `TargetType`=:targetType AND `TargetID`=:targetID AND `Visible`=1";
			$scoreTen = $this->data->executeScalar($sql, $intoAry);
			return ($scoreTen / 10);
		}
	}
?>