<?php
	require_once dirname(__FILE__) . "/dalBase.php";
	class productDAL extends dalBase {
		public $ProductID;
		public $CategoryID;
		public $ProductName;
		public $PathName;
		public $TwPathName;
		public $ProductNO;
		public $OldPrice;
		public $Price;
		public $IconImage;
		public $BannerImage;
		public $BannerTitle;
		public $BannerLink;
		public $BannerColor;
		public $BannerDesc;
		public $Introduction;
		public $Specification;
		public $Title1;
		public $Title2;
		public $Title3;
		public $Description1;
		public $Description2;
		public $Description3;
		public $OGTitle;
		public $OGDescription;
		public $OGImage;
		public $ViewCount;
		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 $CategoryName;
		public $CategoryIsIndex;
		public $CategoryVisible;
		public $CategoryDisable;
		public $ParentID;
		public $ParentName;
		public $ParentIsIndex;
		public $ParentVisible;
		public $ParentDisable;
		public $GrandID;
		public $GrandName;
		public $GrandIsIndex;
		public $GrandVisible;
		public $GrandDisable;
		public $CreatedLoginName;
		public $CreatedUserName;
		public $ModifiedLoginName;
		public $ModifiedUserName;
		public $ProductImageArray = [];

		public function loadRecord(): bool {
			$intoAry = [];
			$sql = "SELECT a.*,b.`CategoryName`,b.`IsIndex` AS `CategoryIsIndex`,b.`Visible` AS `CategoryVisible`,b.`Disable` AS `CategoryDisable`,b.`ParentID`".
				",c.`CategoryName` AS `ParentName`,c.`IsIndex` AS `ParentIsIndex`,c.`Visible` AS `ParentVisible`,c.`Disable` AS `ParentDisable`,c.`ParentID` AS `GrandID`".
				",d.`CategoryName` AS `GrandName`,d.`IsIndex` AS `GrandIsIndex`,d.`Visible` AS `GrandVisible`,d.`Disable` AS `GrandDisable`".
				",e.`LoginName` AS `CreatedLoginName`,e.`UserName` AS `CreatedUserName`".
				",f.`LoginName` AS `ModifiedLoginName`,f.`UserName` AS `ModifiedUserName` FROM `product` a".
				" LEFT JOIN `category` b ON a.`CategoryID`=b.`CategoryID`".
				" LEFT JOIN `category` c ON b.`ParentID`=c.`CategoryID`".
				" LEFT JOIN `category` d ON c.`ParentID`=d.`CategoryID`".
				" LEFT JOIN `user` e ON a.`CreatedUserID`=e.`UserID`".
				" LEFT JOIN `user` f ON a.`ModifiedUserID`=f.`UserID` WHERE ";
			if ($this->ProductID !== null) {
				$sql .= "a.`ProductID`=:productID";
				$intoAry[] = parent::addArray(":productID", $this->ProductID, "int");
			} else if ($this->PathName !== null) {
				$sql .= "a.`PathName`=:pathName";
				$intoAry[] = parent::addArray(":pathName", $this->PathName, "str", 128);
			} else if ($this->TwPathName !== null) {
				$sql .= "a.`TwPathName` = :twPathName";
				$this->TwPathName = preg_replace("/\s+/", "+", $this->TwPathName);
				$intoAry[] = parent::addArray(":twPathName", $this->TwPathName, "str", 256);
			} else {
				$sql .= "1 = 2";
			}
			$reader = $this->data->executeReader($sql, $intoAry);

			if (!empty($reader)) {
				$this->ProductID = $reader["ProductID"];
				$this->CategoryID = $reader["CategoryID"];
				$this->ProductName = $reader["ProductName"];
				$this->PathName = $reader["PathName"];
				$this->TwPathName = $reader["TwPathName"];
				$this->ProductNO = $reader["ProductNO"];
				$this->OldPrice = $reader["OldPrice"];
				$this->Price = $reader["Price"];
				$this->IconImage = $reader["IconImage"];
				$this->BannerImage = $reader["BannerImage"];
				$this->BannerTitle = $reader["BannerTitle"];
				$this->BannerLink = $reader["BannerLink"];
				$this->BannerColor = $reader["BannerColor"];
				$this->BannerDesc = $reader["BannerDesc"];
				$this->Introduction = $reader["Introduction"];
				$this->Specification = $reader["Specification"];
				$this->Title1 = $reader["Title1"];
				$this->Title2 = $reader["Title2"];
				$this->Title3 = $reader["Title3"];
				$this->Description1 = $reader["Description1"];
				$this->Description2 = $reader["Description2"];
				$this->Description3 = $reader["Description3"];
				$this->OGTitle = $reader["OGTitle"];
				$this->OGDescription = $reader["OGDescription"];
				$this->OGImage = $reader["OGImage"];
				$this->ViewCount = $reader["ViewCount"];
				$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->CategoryName = $reader["CategoryName"];
				$this->CategoryIsIndex = $reader["CategoryIsIndex"];
				$this->CategoryVisible = $reader["CategoryVisible"];
				$this->CategoryDisable = $reader["CategoryDisable"];
				$this->ParentID = $reader["ParentID"];
				$this->ParentName = $reader["ParentName"];
				$this->ParentIsIndex = $reader["ParentIsIndex"];
				$this->ParentVisible = $reader["ParentVisible"];
				$this->ParentDisable = $reader["ParentDisable"];
				$this->GrandID = $reader["GrandID"];
				$this->GrandName = $reader["GrandName"];
				$this->GrandIsIndex = $reader["GrandIsIndex"];
				$this->GrandVisible = $reader["GrandVisible"];
				$this->GrandDisable = $reader["GrandDisable"];
				$this->CreatedLoginName = $reader["CreatedLoginName"];
				$this->CreatedUserName = $reader["CreatedUserName"];
				$this->ModifiedLoginName = $reader["ModifiedLoginName"];
				$this->ModifiedUserName = $reader["ModifiedUserName"];

				$into2Ary = [];
				$sql = "SELECT * FROM `product_image` WHERE `ProductID`=:productID ORDER BY `OrderNO`";
				$into2Ary[] = parent::addArray(":productID", $this->ProductID, "int");
				$this->ProductImageArray = $this->data->executeRecordset($sql, $into2Ary, 0, -1);
				return true;
			} else {
				return false;
			}
		}

		public function insertRecord(): bool|int {
			//開始事務
			$this->data->beginTransaction();

			$nowTime = time();
			$intoAry = [
				parent::addArray(":categoryID", $this->CategoryID, "int"),
				parent::addArray(":productName", $this->ProductName, "str", 100),
				parent::addArray(":pathName", $this->PathName, "str", 128),
				parent::addArray(":twPathName", $this->TwPathName, "str", 200),
				parent::addArray(":productNO", $this->ProductNO, "str", 50),
				parent::addArray(":oldPrice", $this->OldPrice, "int"),
				parent::addArray(":price", $this->Price, "int"),
				parent::addArray(":iconImage", $this->IconImage, "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(":introduction", $this->Introduction, "str", 600),
				parent::addArray(":specification", $this->Specification, "str"),
				parent::addArray(":title1", $this->Title1, "str", 50),
				parent::addArray(":title2", $this->Title2, "str", 50),
				parent::addArray(":title3", $this->Title3, "str", 50),
				parent::addArray(":description1", $this->Description1, "str"),
				parent::addArray(":description2", $this->Description2, "str"),
				parent::addArray(":description3", $this->Description3, "str"),
				parent::addArray(":ogTitle", $this->OGTitle, "str", 256),
				parent::addArray(":ogDescription", $this->OGDescription, "str", 512),
				parent::addArray(":ogImage", $this->OGImage, "str", 200),
				parent::addArray(":viewCount", $this->ViewCount, "int"),
				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 `product` (`CategoryID`, `ProductName`, `PathName`, `TwPathName`, `ProductNO`, `OldPrice`, `Price`, `IconImage`, `BannerImage`, `BannerTitle`, `BannerLink`, `BannerColor`, `BannerDesc`, `Introduction`, `Specification`, `Title1`, `Title2`, `Title3`, `Description1`, `Description2`, `Description3`, `OGTitle`, `OGDescription`, `OGImage`, `ViewCount`, `IsIndex`, `Visible`, `OrderNO`, `Disable`, `WebTitle`, `WebKeyword`, `WebDescription`, `BestRating`, `RatingValue`, `RatingCount`, `ReviewCount`, `IsShowJason`, `IsShowStars`, `CreatedTime`, `CreatedUserID`, `CreatedIP`, `ModifiedTime`, `ModifiedUserID`, `ModifiedIP`)".
				" VALUES (:categoryID, :productName, :pathName, :twPathName, :productNO, :oldPrice, :price, :iconImage, :bannerImage, :bannerTitle, :bannerLink, :bannerColor, :bannerDesc, :introduction, :specification, :title1, :title2, :title3, :description1, :description2, :description3, :ogTitle, :ogDescription, :ogImage, :viewCount, :isIndex, :visible, :orderNO, :disable, :webTitle, :webKeyword, :webDescription, :bestRating, :ratingValue, :ratingCount, :reviewCount, :isShowJason, :isShowStars, :createdTime, :createdUserID, :createdIP, 0, 0, '')";
			$this->data->executeNonQuery($sql, $intoAry, true);
			$insertID = $this->data->getInsertID();

			if (is_array($this->ProductImageArray) && count($this->ProductImageArray) > 0) {
				foreach ($this->ProductImageArray as $key => $rows) {
					$into2Ary = [
						parent::addArray(":productID", $insertID, "int"),
						parent::addArray(":image", $rows["Image"] ?? '', "str", 200),
						parent::addArray(":title", $rows["Title"] ?? '', "str", 256),
						parent::addArray(":description", $rows["Description"] ?? '', "str", 512),
						parent::addArray(":orderNO", $key, "int")
					];
					$sql = "INSERT INTO `product_image` (`ProductID`, `Image`, `Title`, `Description`, `OrderNO`)".
						" VALUES (:productID, :image, :title, :description, :orderNO)";
					$this->data->executeNonQuery($sql, $into2Ary, true);
				}
			}

			if (!$this->data->error) {
				$this->data->commitTransaction();
				return $insertID;
			} else {
				$this->data->rollbackTransaction();
				return false;
			}
		}
		public function updateRecord(): bool {
			// 開始事務
			$this->data->beginTransaction();

			$nowTime = time();
			$intoAry = [
				parent::addArray(":productID", $this->ProductID, "int"),
				parent::addArray(":categoryID", $this->CategoryID, "int"),
				parent::addArray(":productName", $this->ProductName, "str", 100),
				parent::addArray(":pathName", $this->PathName, "str", 128),
				parent::addArray(":twPathName", $this->TwPathName, "str", 200),
				parent::addArray(":productNO", $this->ProductNO, "str", 50),
				parent::addArray(":oldPrice", $this->OldPrice, "int"),
				parent::addArray(":price", $this->Price, "int"),
				parent::addArray(":iconImage", $this->IconImage, "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(":introduction", $this->Introduction, "str", 600),
				parent::addArray(":specification", $this->Specification, "str"),
				parent::addArray(":title1", $this->Title1, "str", 50),
				parent::addArray(":title2", $this->Title2, "str", 50),
				parent::addArray(":title3", $this->Title3, "str", 50),
				parent::addArray(":description1", $this->Description1, "str"),
				parent::addArray(":description2", $this->Description2, "str"),
				parent::addArray(":description3", $this->Description3, "str"),
				parent::addArray(":ogTitle", $this->OGTitle, "str", 256),
				parent::addArray(":ogDescription", $this->OGDescription, "str", 512),
				parent::addArray(":ogImage", $this->OGImage, "str", 200),
				parent::addArray(":viewCount", $this->ViewCount, "int"),
				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", 50)
			];
			$sql = "UPDATE `product` SET ".
				"`CategoryID`=:categoryID".
				",`ProductName`=:productName".
				",`PathName`=:pathName".
				",`TwPathName`=:twPathName".
				",`ProductNO`=:productNO".
				",`OldPrice`=:oldPrice".
				",`Price`=:price".
				",`IconImage`=:iconImage".
				",`BannerImage`=:bannerImage".
				",`BannerTitle`=:bannerTitle".
				",`BannerLink`=:bannerLink".
				",`BannerColor`=:bannerColor".
				",`BannerDesc`=:bannerDesc".
				",`Introduction`=:introduction".
				",`Specification`=:specification".
				",`Title1`=:title1".
				",`Title2`=:title2".
				",`Title3`=:title3".
				",`Description1`=:description1".
				",`Description2`=:description2".
				",`Description3`=:description3".
				",`OGTitle`=:ogTitle".
				",`OGDescription`=:ogDescription".
				",`OGImage`=:ogImage".
				",`ViewCount`=:viewCount".
				",`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 `ProductID`=:productID";
			$this->data->executeNonQuery($sql, $intoAry, true);

			$into2Ary = [];
			$sql = "DELETE FROM `product_image` WHERE `ProductID`=:productID";
			$into2Ary[] = parent::addArray(":productID", $this->ProductID, "int");
			$this->data->executeNonQuery($sql, $into2Ary, true);

			if (is_array($this->ProductImageArray) && count($this->ProductImageArray) > 0) {
				foreach ($this->ProductImageArray as $key => $rows) {
					$into3Ary = [
						parent::addArray(":productID", $this->ProductID, "int"),
						parent::addArray(":image", $rows["Image"] ?? '', "str", 200),
						parent::addArray(":title", $rows["Title"] ?? '', "str", 256),
						parent::addArray(":description", $rows["Description"] ?? '', "str", 512),
						parent::addArray(":orderNO", $key, "int")
					];
					$sql = "INSERT INTO `product_image` (`ProductID`, `Image`, `Title`, `Description`, `OrderNO`)".
						" VALUES (:productID, :image, :title, :description, :orderNO)";
					$this->data->executeNonQuery($sql, $into3Ary, true);
				}
			}

			if (!$this->data->error) {
				$this->data->commitTransaction();
				return true;
			} else {
				$this->data->rollbackTransaction();
				return false;
			}
		}

		//查詢
		public function executeRecordset(array $intoAry = [], string $filter = "", string $order = "", int $start = 0, int $length = -1): array {
			$sql = "SELECT a.*,b.`CategoryName`,b.`IsIndex` AS `CategoryIsIndex`,b.`Visible` AS `CategoryVisible`,b.`Disable` AS `CategoryDisable`,b.`ParentID`".
				",c.`CategoryName` AS `ParentName`,c.`IsIndex` AS `ParentIsIndex`,c.`Visible` AS `ParentVisible`,c.`Disable` AS `ParentDisable`,c.`ParentID` AS `GrandID`".
				",d.`CategoryName` AS `GrandName`,d.`IsIndex` AS `GrandIsIndex`,d.`Visible` AS `GrandVisible`,d.`Disable` AS `GrandDisable`".
				",e.`LoginName` AS `CreatedLoginName`,e.`UserName` AS `CreatedUserName`".
				",f.`LoginName` AS `ModifiedLoginName`,f.`UserName` AS `ModifiedUserName` FROM `product` a".
				" LEFT JOIN `category` b ON a.`CategoryID`=b.`CategoryID`".
				" LEFT JOIN `category` c ON b.`ParentID`=c.`CategoryID`".
				" LEFT JOIN `category` d ON c.`ParentID`=d.`CategoryID`".
				" 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.`OrderNO`" : " ORDER BY $order";
			$length = is_int($length) ? $length : -1;
			return $this->data->executeRecordset($sql, $intoAry, $start, $length);
		}
		
		//查詢2
		public function execute2Recordset(array $intoAry = [], string $filter = "", string $order = "", int $start = 0, int $length = -1): array {
			$sql = "SELECT a.*,b.`CategoryName`,b.`IsIndex` AS `CategoryIsIndex`,b.`Visible` AS `CategoryVisible`,b.`Disable` AS `CategoryDisable`,b.`ParentID`".
				",c.`CategoryName` AS `ParentName`,c.`IsIndex` AS `ParentIsIndex`,c.`Visible` AS `ParentVisible`,c.`Disable` AS `ParentDisable`,c.`ParentID` AS `GrandID`".
				",d.`CategoryName` AS `GrandName`,d.`IsIndex` AS `GrandIsIndex`,d.`Visible` AS `GrandVisible`,d.`Disable` AS `GrandDisable`".
				",e.`LoginName` AS `CreatedLoginName`,e.`UserName` AS `CreatedUserName`".
				",f.`LoginName` AS `ModifiedLoginName`,f.`UserName` AS `ModifiedUserName`".
				",IFNULL(d.`OrderNO`, IFNULL(c.`OrderNO`, IFNULL(b.`OrderNO`, a.`OrderNO`))) AS `NO1`".
				",IF(d.`OrderNO` IS NULL, IF(c.`OrderNO` IS NULL, IF(b.`OrderNO` IS NULL, NULL, a.`OrderNO`), b.`OrderNO`), c.`OrderNO`) AS `NO2`".
				",IF(d.`OrderNO` IS NULL, IF(c.`OrderNO` IS NULL, NULL, a.`OrderNO`), b.`OrderNO`) AS `NO3`,IF(d.`OrderNO` IS NULL, NULL, a.`OrderNO`) AS `NO4`".
				" FROM `product` a LEFT JOIN `category` b ON a.`CategoryID`=b.`CategoryID`".
				" LEFT JOIN `category` c ON b.`ParentID`=c.`CategoryID`".
				" LEFT JOIN `category` d ON c.`ParentID`=d.`CategoryID`".
				" 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 `NO1`,`NO2`,`NO3`,`NO4`,a.`ModifiedTime` DESC" : " ORDER BY $order";
			$length = is_int($length) ? $length : -1;
			return $this->data->executeRecordset($sql, $intoAry, $start, $length);
		}
		//查詢3
		public function execute3Recordset(array $intoAry = [], string $filter = "", string $order = "", int $start = 0, int $length = -1): array {
			$sql = "SELECT a.*,b.`CategoryName`,b.`IsIndex` AS `CategoryIsIndex`,b.`Visible` AS `CategoryVisible`,b.`Disable` AS `CategoryDisable`,b.`ParentID`".
				",c.`CategoryName` AS `ParentName`,c.`IsIndex` AS `ParentIsIndex`,c.`Visible` AS `ParentVisible`,c.`Disable` AS `ParentDisable`,c.`ParentID` AS `GrandID`".
				",d.`CategoryName` AS `GrandName`,d.`IsIndex` AS `GrandIsIndex`,d.`Visible` AS `GrandVisible`,d.`Disable` AS `GrandDisable`".
				",e.`LoginName` AS `CreatedLoginName`,e.`UserName` AS `CreatedUserName`".
				",f.`LoginName` AS `ModifiedLoginName`,f.`UserName` AS `ModifiedUserName`".
				",IFNULL(d.`OrderNO`, IFNULL(c.`OrderNO`, b.`OrderNO`)) AS `NO1`".
				",IF(d.`OrderNO` IS NULL, IF(c.`OrderNO` IS NULL, NULL, b.`OrderNO`), c.`OrderNO`) AS `NO2`".
				",IF(d.`OrderNO` IS NULL, NULL, b.`OrderNO`) AS `NO3` FROM `product` a".
				" LEFT JOIN `category` b ON a.`CategoryID`=b.`CategoryID`".
				" LEFT JOIN `category` c ON b.`ParentID`=c.`CategoryID`".
				" LEFT JOIN `category` d ON c.`ParentID`=d.`CategoryID`".
				" 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 `NO1`,`NO2`,`NO3`,a.`CategoryID`,a.`OrderNO`,a.`ModifiedTime` 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 `product` a LEFT JOIN `category` b ON a.`CategoryID`=b.`CategoryID` LEFT JOIN `category`".
			" c ON b.`ParentID`=c.`CategoryID` LEFT JOIN `category` d ON c.`ParentID`=d.`CategoryID` 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);
		}
		
		//點擊計數遞增
		public function click(): bool {
			$intoAry = [
				parent::addArray(":productID", $this->ProductID, "int")
			];
			$sql = "UPDATE `product` SET `ViewCount`=`ViewCount`+1 WHERE `ProductID`=:productID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			
			return !$this->data->error;
		}
		
		//刪除
		public function deleteRecord(int $productID, int $type): bool {
			$intoAry = [
				parent::addArray(":productID", $productID, "int"),
				parent::addArray(":disable", $type, "int")
			];
			$sql = "UPDATE `product` SET `Disable`=:disable WHERE `ProductID`=:productID";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}
		
		//批量刪除，傳入參數為用逗號分隔的id镩
		public function batDelete(string $productIDs, int $type): bool {
			$sql = "UPDATE `product` SET `Disable`=$type WHERE `ProductID` IN ($productIDs)";
			$this->data->executeNonQuery($sql, [], true);
			return !$this->data->error;
		}

		//批量修改產品啟用狀態
		public function batUpdateVisible(string $productIDs, int $value = 1): bool {
			$intoAry = [
				parent::addArray(":visible", $value, "int")
			];
			$sql = "UPDATE `product` SET `Visible`=:visible WHERE `ProductID` IN ($productIDs)";
			$this->data->executeNonQuery($sql, $intoAry, true);
			return !$this->data->error;
		}

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