<?php
	require_once dirname(__FILE__) . "/dalBase.php";
	class faq_categoryDAL extends dalBase {
		public $CategoryID;
		public $CategoryName;
		public $TwPathName;
		public $Introduction;
		public $Description;
		public $Image;
		public $BannerImage;
		public $BannerTitle;
		public $BannerLink;
		public $BannerColor;
		public $BannerDesc;
		public $OGTitle;
		public $OGDescription;
		public $OGImage;
		public $IsIndex;
		public $Visible;
		public $OrderNO;
		public $Disable;
		public $WebTitle;
		public $WebKeyword;
		public $WebDescription;
		public $BestRating;
		public $RatingValue;
		public $RatingCount;
		public $ReviewCount;
		public $IsShowJason;
		public $IsShowStars;
		public $CreatedTime;
		public $CreatedUserID;
		public $CreatedIP;
		public $ModifiedTime;
		public $ModifiedUserID;
		public $ModifiedIP;
		//外部欄位
		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 `faq_category` a".
				" LEFT JOIN `user` b ON a.`CreatedUserID`=b.`UserID`".
				" LEFT JOIN `user` c ON a.`ModifiedUserID`=c.`UserID` WHERE ";
			if ($this->CategoryID !== null) {
				$sql .= "a.`CategoryID` = :categoryID";
				$intoAry[] = parent::addArray(":categoryID", $this->CategoryID, "int");
			} else if ($this->TwPathName !== null) {
				$sql .= "a.`TwPathName` = :twPathName";
				$this->TwPathName = preg_replace("/\s+/", "+", $this->TwPathName);
				$intoAry[] = parent::addArray(":twPathName", $this->TwPathName, "str", 100);
			} else {
				$sql .= "1 = 2";
			}
			$reader = $this->data->executeReader($sql, $intoAry);

			if (!empty($reader)) {
				$this->CategoryID = $reader["CategoryID"];
				$this->CategoryName = $reader["CategoryName"];
				$this->TwPathName = $reader["TwPathName"];
				$this->Introduction = $reader["Introduction"];
				$this->Description = $reader["Description"];
				$this->Image = $reader["Image"];
				$this->BannerImage = $reader["BannerImage"];
				$this->BannerTitle = $reader["BannerTitle"];
				$this->BannerLink = $reader["BannerLink"];
				$this->BannerColor = $reader["BannerColor"];
				$this->BannerDesc = $reader["BannerDesc"];
				$this->OGTitle = $reader["OGTitle"];
				$this->OGDescription = $reader["OGDescription"];
				$this->OGImage = $reader["OGImage"];
				$this->IsIndex = $reader["IsIndex"];
				$this->Visible = $reader["Visible"];
				$this->OrderNO = $reader["OrderNO"];
				$this->Disable = $reader["Disable"];
				$this->WebTitle = $reader["WebTitle"];
				$this->WebKeyword = $reader["WebKeyword"];
				$this->WebDescription = $reader["WebDescription"];
				$this->BestRating = $reader["BestRating"];
				$this->RatingValue = $reader["RatingValue"];
				$this->RatingCount = $reader["RatingCount"];
				$this->ReviewCount = $reader["ReviewCount"];
				$this->IsShowJason = $reader["IsShowJason"];
				$this->IsShowStars = $reader["IsShowStars"];
				$this->CreatedTime = $reader["CreatedTime"];
				$this->CreatedUserID = $reader["CreatedUserID"];
				$this->CreatedIP = $reader["CreatedIP"];
				$this->ModifiedTime = $reader["ModifiedTime"];
				$this->ModifiedUserID = $reader["ModifiedUserID"];
				$this->ModifiedIP = $reader["ModifiedIP"];
				$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 {
			$nowTime = time();
			$intoAry = [
				parent::addArray(":categoryName", $this->CategoryName, "str", 50),
				parent::addArray(":twPathName", $this->TwPathName, "str", 100),
				parent::addArray(":introduction", $this->Introduction, "str", 600),
				parent::addArray(":description", $this->Description, "str"),
				parent::addArray(":image", $this->Image, "str", 128),
				parent::addArray(":bannerImage", $this->BannerImage, "str", 200),
				parent::addArray(":bannerTitle", $this->BannerTitle, "str", 50),
				parent::addArray(":bannerLink", $this->BannerLink, "str", 200),
				parent::addArray(":bannerColor", $this->BannerColor, "str", 11),
				parent::addArray(":bannerDesc", $this->BannerDesc, "str", 50),
				parent::addArray(":ogTitle", $this->OGTitle, "str", 256),
				parent::addArray(":ogDescription", $this->OGDescription, "str", 512),
				parent::addArray(":ogImage", $this->OGImage, "str", 200),
				parent::addArray(":isIndex", $this->IsIndex, "int"),
				parent::addArray(":visible", $this->Visible, "int"),
				parent::addArray(":orderNO", $this->OrderNO, "int"),
				parent::addArray(":disable", $this->Disable, "int"),
				parent::addArray(":webTitle", $this->WebTitle, "str", 256),
				parent::addArray(":webKeyword", $this->WebKeyword, "str", 256),
				parent::addArray(":webDescription", $this->WebDescription, "str", 512),
				parent::addArray(":bestRating", $this->BestRating, "int"),
				parent::addArray(":ratingValue", $this->RatingValue, "str"),
				parent::addArray(":ratingCount", $this->RatingCount, "int"),
				parent::addArray(":reviewCount", $this->ReviewCount, "int"),
				parent::addArray(":isShowJason", $this->IsShowJason, "int"),
				parent::addArray(":isShowStars", $this->IsShowStars, "int"),
				parent::addArray(":createdTime", $nowTime, "int"),
				parent::addArray(":createdUserID", $this->CreatedUserID, "int"),
				parent::addArray(":createdIP", $this->CreatedIP, "str", 50)
			];
			$sql = "INSERT INTO `faq_category` (`CategoryName`, `TwPathName`, `Introduction`, `Description`, `Image`, `BannerImage`, `BannerTitle`, `BannerLink`, `BannerColor`, `BannerDesc`, `OGTitle`, `OGDescription`, `OGImage`, `IsIndex`, `Visible`, `OrderNO`, `Disable`, `WebTitle`, `WebKeyword`, `WebDescription`, `BestRating`, `RatingValue`, `RatingCount`, `ReviewCount`, `IsShowJason`, `IsShowStars`, `CreatedTime`, `CreatedUserID`, `CreatedIP`, `ModifiedTime`, `ModifiedUserID`, `ModifiedIP`)".
				" VALUES (:categoryName, :twPathName, :introduction, :description, :image, :bannerImage, :bannerTitle, :bannerLink, :bannerColor, :bannerDesc, :ogTitle, :ogDescription, :ogImage, :isIndex, :visible, :orderNO, :disable, :webTitle, :webKeyword, :webDescription, :bestRating, :ratingValue, :ratingCount, :reviewCount, :isShowJason, :isShowStars, :createdTime, :createdUserID, :createdIP, 0, 0, '')";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return (!$this->data->error) ? $this->data->getInsertID() : false;
		}

		public function updateRecord(): bool {
			$nowTime = time();
			$intoAry = [
				parent::addArray(":categoryID", $this->CategoryID, "int"),
				parent::addArray(":categoryName", $this->CategoryName, "str", 50),
				parent::addArray(":twPathName", $this->TwPathName, "str", 100),
				parent::addArray(":introduction", $this->Introduction, "str", 600),
				parent::addArray(":description", $this->Description, "str"),
				parent::addArray(":image", $this->Image, "str", 128),
				parent::addArray(":bannerImage", $this->BannerImage, "str", 200),
				parent::addArray(":bannerTitle", $this->BannerTitle, "str", 50),
				parent::addArray(":bannerLink", $this->BannerLink, "str", 200),
				parent::addArray(":bannerColor", $this->BannerColor, "str", 11),
				parent::addArray(":bannerDesc", $this->BannerDesc, "str", 50),
				parent::addArray(":ogTitle", $this->OGTitle, "str", 256),
				parent::addArray(":ogDescription", $this->OGDescription, "str", 512),
				parent::addArray(":ogImage", $this->OGImage, "str", 200),
				parent::addArray(":isIndex", $this->IsIndex, "int"),
				parent::addArray(":visible", $this->Visible, "int"),
				parent::addArray(":orderNO", $this->OrderNO, "int"),
				parent::addArray(":disable", $this->Disable, "int"),
				parent::addArray(":webTitle", $this->WebTitle, "str", 256),
				parent::addArray(":webKeyword", $this->WebKeyword, "str", 256),
				parent::addArray(":webDescription", $this->WebDescription, "str", 512),
				parent::addArray(":bestRating", $this->BestRating, "int"),
				parent::addArray(":ratingValue", $this->RatingValue, "str"),
				parent::addArray(":ratingCount", $this->RatingCount, "int"),
				parent::addArray(":reviewCount", $this->ReviewCount, "int"),
				parent::addArray(":isShowJason", $this->IsShowJason, "int"),
				parent::addArray(":isShowStars", $this->IsShowStars, "int"),
				parent::addArray(":modifiedTime", $nowTime, "int"),
				parent::addArray(":modifiedUserID", $this->ModifiedUserID, "int"),
				parent::addArray(":modifiedIP", $this->ModifiedIP, "str")
			];
			$sql = "UPDATE `faq_category` SET ".
				"`CategoryName`=:categoryName".
				",`TwPathName`=:twPathName".
				",`Introduction`=:introduction".
				",`Description`=:description".
				",`Image`=:image".
				",`BannerImage`=:bannerImage".
				",`BannerTitle`=:bannerTitle".
				",`BannerLink`=:bannerLink".
				",`BannerColor`=:bannerColor".
				",`BannerDesc`=:bannerDesc".
				",`OGTitle`=:ogTitle".
				",`OGDescription`=:ogDescription".
				",`OGImage`=:ogImage".
				",`IsIndex`=:isIndex".
				",`Visible`=:visible".
				",`OrderNO`=:orderNO".
				",`Disable`=:disable".
				",`WebTitle`=:webTitle".
				",`WebKeyword`=:webKeyword".
				",`WebDescription`=:webDescription".
				",`BestRating`=:bestRating".
				",`RatingValue`=:ratingValue".
				",`RatingCount`=:ratingCount".
				",`ReviewCount`=:reviewCount".
				",`IsShowJason`=:isShowJason".
				",`IsShowStars`=:isShowStars".
				",`ModifiedTime`=:modifiedTime".
				",`ModifiedUserID`=:modifiedUserID".
				",`ModifiedIP`=:modifiedIP".
				" WHERE `CategoryID`=:categoryID";
			$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 `faq_category` 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.`OrderNO`,a.CategoryID" : " 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 `faq_category` 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 $categoryID, int $type): bool {
			$intoAry = [
				parent::addArray(":categoryID", $categoryID, "int"),
				parent::addArray(":disable", $type, "int")
			];
			$sql = "UPDATE `faq_category` SET `Disable`=:disable WHERE `CategoryID`=:categoryID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

		//檢查中文路徑是否已存在
		public function checkTwPathNameExists(string $twPathName): bool {
			$intoAry = [
				parent::addArray(":twPathName", $twPathName, "str", 100)
			];
			$sql = "SELECT COUNT(*) FROM `faq_category` WHERE `TwPathName`=:twPathName AND `Disable`=0";
			$num = $this->data->executeScalar($sql, $intoAry);
			return ($num > 0) ? true : false;
		}
	}
?>