<?php
	require_once dirname(__FILE__) . "/dalBase.php";
	class levelDAL extends dalBase {
		public $LevelID;
		public $LevelName;
		public $LevelDiscount;
		public $LevelDescription;
		public $Visible;
		public $CreatedTime;
		public $CreatedIP;
		public $CreatedUserID;
		public $ModifiedTime;
		public $ModifiedIP;
		public $ModifiedUserID;
		//外部欄位
		public $CreatedLoginName;
		public $CreatedUserName;
		public $ModifiedLoginName;
		public $ModifiedUserName;

		public function loadRecord(): bool {
			$intoAry = [];
			$sql = "SELECT a.*,b.`LoginName` AS `CreatedLoginName`,b.`UserName` AS `CreatedUserName`".
				",c.`LoginName` AS `ModifiedLoginName`,c.`UserName` AS `ModifiedUserName`".
				" FROM `level` a LEFT JOIN `user` b ON a.`CreatedUserID`=b.`UserID`".
				" LEFT JOIN `user` c ON a.`ModifiedUserID`=c.`UserID` WHERE a.`LevelID`=:levelID";
			$intoAry[] = parent::addArray(":levelID", $this->LevelID, "int");
			$reader = $this->data->executeReader($sql, $intoAry);

			if (!empty($reader)) {
				$this->LevelID = $reader["LevelID"];
				$this->LevelName = $reader["LevelName"];
				$this->LevelDiscount = $reader["LevelDiscount"];
				$this->LevelDescription = $reader["LevelDescription"];
				$this->Visible = $reader["Visible"];
				$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->CreatedLoginName = $reader["CreatedLoginName"];
				$this->CreatedUserName = $reader["CreatedUserName"];
				$this->ModifiedLoginName = $reader["ModifiedLoginName"];
				$this->ModifiedUserName = $reader["ModifiedUserName"];
				return true;
			} else {
				return false;
			}
		}
		public function insertRecord(): bool|int {
			$intoAry = [
				parent::addArray(":levelName", $this->LevelName, "str", 50),
				parent::addArray(":levelDiscount", $this->LevelDiscount, "int"),
				parent::addArray(":levelDescription", $this->LevelDescription, "str"),
				parent::addArray(":visible", $this->Visible, "int"),
				parent::addArray(":createdIP", $this->CreatedIP, "str", 50),
				parent::addArray(":createdUserID", $this->CreatedUserID, "int")
			];
			$sql = "INSERT INTO `level` (`LevelName`, `LevelDiscount`, `LevelDescription`, `Visible`, `CreatedTime`, `CreatedIP`, `CreatedUserID`, `ModifiedTime`, `ModifiedIP`, `ModifiedUserID`)".
				" VALUES (:levelName, :levelDiscount, :levelDescription, :visible, UNIX_TIMESTAMP(), :createdIP, :createdUserID, 0, 0, 0)";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return (!$this->data->error) ? $this->data->getInsertID() : false;
		}
		public function updateRecord(): bool {
			$intoAry = [
				parent::addArray(":levelID", $this->LevelID, "int"),
				parent::addArray(":levelName", $this->LevelName, "str", 50),
				parent::addArray(":levelDiscount", $this->LevelDiscount, "int"),
				parent::addArray(":levelDescription", $this->LevelDescription, "str"),
				parent::addArray(":visible", $this->Visible, "int"),
				parent::addArray(":modifiedTime", $this->ModifiedTime, "int"),
				parent::addArray(":modifiedIP", $this->ModifiedIP, "str", 50),
				parent::addArray(":modifiedUserID", $this->ModifiedUserID, "int")
			];
			$sql = "UPDATE `level` SET ".
				"`LevelName`=:levelName".
				",`LevelDiscount`=:levelDiscount".
				",`LevelDescription`=:levelDescription".
				",`Visible`=:visible".
				",`ModifiedTime`=:modifiedTime".
				",`ModifiedIP`=:modifiedIP".
				",`ModifiedUserID`=:modifiedUserID".
				" WHERE `LevelID`=:levelID";
			$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` AS `CreatedLoginName`,b.`UserName` AS `CreatedUserName`".
				",c.`LoginName` AS `ModifiedLoginName`,c.`UserName` AS `ModifiedUserName` FROM `level` a".
				" LEFT JOIN `user` b ON a.`CreatedUserID`=b.`UserID`".
				" LEFT JOIN `user` c ON a.`ModifiedUserID`=c.`UserID`";
			if ($filter !== "") $sql .= " WHERE $filter";
			$sql .= (!isset($order) || $order === "") ? " ORDER BY a.`LevelDiscount`,a.`LevelID`" : " 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 `level` a LEFT JOIN `user` b ON a.`CreatedUserID`=b.`UserID` LEFT JOIN `user` c ON a.`ModifiedUserID`=c.`UserID`";
			if ($filter !== "") $sql .= " WHERE $filter";
			return $this->data->executeScalar($sql, $intoAry);
		}
		//刪除
		public function deleteRecord(int $levelID): bool {
			$intoAry = [
				parent::addArray(":levelID", $levelID, "int")
			];
			$sql = "DELETE FROM `level` WHERE `LevelID`=:levelID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}
	}
?>