<?php
	require_once dirname(__FILE__)."/dalBase.php";
	class userDAL extends dalBase{
		//27個字段
		public $UserID; 		//用戶ID
		public $LoginName; 		//登陸名稱
		public $Password; 		//登陸密碼
		public $Question;		//
		public $Answer;			//
		public $UserName; 		//登陸密碼
		public $FirstName;		//
		public $LastName;		//
		public $Email;			//
		public $MSN;			//
		public $Sex=0;			//性別:0未知，1男，2女
		public $CountryID=0;
		public $State;
		public $ZipCode;
		public $Address;
		public $Telephone;
		public $MobilePhone;
		public $Fax;
		public $Company;
		public $City;
		public $AutoEmail=0;
		public $Visible=1;		//是否啟用
		public $UserType=1;		//用戶類型.1為會員,64為管理員,128為超級管理員
		public $Flags=0;		//記錄標識.0為正常,1為鎖定
		public $LoginTimes=0;	//登陸次數
		public $LastLoginIp;	//上次登錄ID
		public $LastLoginTime;	//刪除登陸時間
		public $CreatedTime;
		public $ModifiedTime;

		//查詢用戶
		public function loadRecord (){
			$sql = "SELECT * FROM user";
			if(isset($this->UserID) && $this->UserID != "")
				$sql .= " WHERE UserID='".$this->UserID."'";
			else if(isset($this->LoginName) && $this->LoginName != "")
				$sql .= " WHERE LoginName='".$this->LoginName."'";
			else if(isset($this->Email) && $this->Email != "")
				$sql .= " WHERE Email='".$this->Email."'";
			else
				$sql .= " WHERE 1=2";

			$reader = $this->data->executeReader($sql);

			$this->UserID = $reader["UserID"];
			$this->LoginName = $reader["LoginName"];
			$this->Password = $reader["Password"];
			$this->Question = $reader["Question"];
			$this->Answer = $reader["Answer"];

			$this->UserName = $reader["UserName"];
			$this->FirstName = $reader["FirstName"];
			$this->LastName = $reader["LastName"];
			$this->Email = $reader["Email"];
			$this->MSN = $reader["MSN"];

			$this->Sex = $reader["Sex"];
			$this->CountryID = $reader["CountryID"];
			$this->State = $reader["State"];
			$this->ZipCode = $reader["ZipCode"];
			$this->Address = $reader["Address"];

			$this->Telephone = $reader["Telephone"];
			$this->MobilePhone = $reader["MobilePhone"];
			$this->Fax = $reader["Fax"];
			$this->Company = $reader["Company"];
			$this->Visible = $reader["Visible"];
			$this->City = $reader["City"];
			$this->AutoEmail = $reader["AutoEmail"];

			$this->UserType = $reader["UserType"];
			$this->Flags = $reader["Flags"];
			$this->LoginTimes = $reader["LoginTimes"];
			$this->LastLoginIp = $reader["LastLoginIp"];
			$this->LastLoginTime = $reader["LastLoginTime"];
			$this->CreatedTime = $reader["CreatedTime"];
			$this->ModifiedTime = $reader["ModifiedTime"];

			if(!$this->data->error)
				return true;
			return false;
		}

		//插入用戶.插入成功后,返回用戶ID,否則返回false
		public function insertRecord(){
			$sql = "INSERT INTO `user` (`LoginName`, `Password`, `Question`, `Answer`, `UserName`, `FirstName`, `LastName`, `Email`, `MSN`, `Sex`, `CountryID`, `State`, `ZipCode`, `Address`, `Telephone`, `MobilePhone`, `Fax`, `Company`, `Visible`, `UserType`, `Flags`, `CreatedTime`, `ModifiedTime`,`LoginTimes`,`LastLoginIp`,`LastLoginTime`,`City`,`AutoEmail`) " .
				"VALUES" .
				" (@LoginName,@Password,@Question,@Answer,@UserName,@FirstName,@LastName,@Email,@MSN,@Sex,@CountryID,@State,@ZipCode,@Address,@Telephone,@MobilePhone,@Fax,@Company,@Visible,@UserType,@Flags,@CreatedTime,@ModifiedTime,@LoginTimes,@LastLoginIp,@LastLoginTime,@City,@AutoEmail)";
			$sql = str_replace("@LoginName","'".$this->LoginName."'",$sql);
			$sql = str_replace("@Password","'".$this->Password."'",$sql);
			$sql = str_replace("@Question","'".$this->Question."'",$sql);
			$sql = str_replace("@Answer","'".$this->Answer."'",$sql);
			$sql = str_replace("@UserName","'".$this->UserName."'",$sql);
			$sql = str_replace("@FirstName","'".$this->FirstName."'",$sql);
			$sql = str_replace("@LastName","'".$this->LastName."'",$sql);
			$sql = str_replace("@Email","'".$this->Email."'",$sql);
			$sql = str_replace("@MSN","'".$this->MSN."'",$sql);
			$sql = str_replace("@Sex","'".$this->Sex."'",$sql);
			$sql = str_replace("@CountryID","'".$this->CountryID."'",$sql);
			$sql = str_replace("@State","'".$this->State."'",$sql);
			$sql = str_replace("@ZipCode","'".$this->ZipCode."'",$sql);
			$sql = str_replace("@Address","'".$this->Address."'",$sql);
			$sql = str_replace("@Telephone","'".$this->Telephone."'",$sql);
			$sql = str_replace("@MobilePhone","'".$this->MobilePhone."'",$sql);
			$sql = str_replace("@Fax","'".$this->Fax."'",$sql);
			$sql = str_replace("@Company","'".$this->Company."'",$sql);
			$sql = str_replace("@Visible","'".$this->Visible."'",$sql);
			$sql = str_replace("@City","'".$this->City."'",$sql);
			$sql = str_replace("@AutoEmail","'".$this->AutoEmail."'",$sql);
			$sql = str_replace("@UserType","'".$this->UserType."'",$sql);
			$sql = str_replace("@Flags","'".$this->Flags."'",$sql);
			$sql = str_replace("@LoginTimes","'".$this->LoginTimes."'",$sql);
			$sql = str_replace("@LastLoginIp","'".$this->LastLoginIp."'",$sql);
			$sql = str_replace("@LastLoginTime","'".$this->LastLoginTime."'",$sql);
			$sql = str_replace("@CreatedTime","'".$this->CreatedTime."'",$sql);
			$sql = str_replace("@ModifiedTime","'".$this->ModifiedTime."'",$sql);

			$this->data->executeNonQuery($sql);
			if(!$this->data->error)
				return $this->data->getInsertID();
			return false;
		}

		//更新用戶信息
		public function updateRecord(){
			$sql = "UPDATE `user` SET " .
				"LoginName=@LoginName" .
				",Password=@Password" .
				",Question=@Question" .
				",Answer=@Answer" .
				",UserName=@UserName" .
				",FirstName=@FirstName" .
				",LastName=@LastName" .
				",Email=@Email" .
				",MSN=@MSN" .
				",Sex=@Sex" .
				",CountryID=@CountryID" .
				",State=@State" .
				",ZipCode=@ZipCode" .
				",Address=@Address" .
				",Telephone=@Telephone" .
				",MobilePhone=@MobilePhone" .
				",Fax=@Fax" .
				",Company=@Company" .
				",Visible=@Visible" .
				",UserType=@UserType" .
				",Flags=@Flags" .
				",LoginTimes=@LoginTimes" .
				",LastLoginIp=@LastLoginIp" .
				",CreatedTime=@CreatedTime" .
				",ModifiedTime=@ModifiedTime" .
				",LastLoginTime=@LastLoginTime" .
				",City=@City" .
				",AutoEmail=@AutoEmail" .
				" WHERE UserID=@UserID";
			$sql = str_replace("@UserID","'".$this->UserID."'",$sql);
			$sql = str_replace("@LoginName","'".$this->LoginName."'",$sql);
			$sql = str_replace("@Password","'".$this->Password."'",$sql);
			$sql = str_replace("@Question","'".$this->Question."'",$sql);
			$sql = str_replace("@Answer","'".$this->Answer."'",$sql);
			$sql = str_replace("@UserName","'".$this->UserName."'",$sql);
			$sql = str_replace("@FirstName","'".$this->FirstName."'",$sql);
			$sql = str_replace("@LastName","'".$this->LastName."'",$sql);
			$sql = str_replace("@Email","'".$this->Email."'",$sql);
			$sql = str_replace("@MSN","'".$this->MSN."'",$sql);
			$sql = str_replace("@Sex","'".$this->Sex."'",$sql);
			$sql = str_replace("@CountryID","'".$this->CountryID."'",$sql);
			$sql = str_replace("@State","'".$this->State."'",$sql);
			$sql = str_replace("@ZipCode","'".$this->ZipCode."'",$sql);
			$sql = str_replace("@Address","'".$this->Address."'",$sql);
			$sql = str_replace("@Telephone","'".$this->Telephone."'",$sql);
			$sql = str_replace("@MobilePhone","'".$this->MobilePhone."'",$sql);
			$sql = str_replace("@Fax","'".$this->Fax."'",$sql);
			$sql = str_replace("@Company","'".$this->Company."'",$sql);
			$sql = str_replace("@Visible","'".$this->Visible."'",$sql);
			$sql = str_replace("@City","'".$this->City."'",$sql);
			$sql = str_replace("@AutoEmail","'".$this->AutoEmail."'",$sql);
			$sql = str_replace("@UserType","'".$this->UserType."'",$sql);
			$sql = str_replace("@Flags","'".$this->Flags."'",$sql);
			$sql = str_replace("@LoginTimes","'".$this->LoginTimes."'",$sql);
			$sql = str_replace("@LastLoginIp","'".$this->LastLoginIp."'",$sql);
			$sql = str_replace("@LastLoginTime","'".$this->LastLoginTime."'",$sql);
			$sql = str_replace("@CreatedTime","'".$this->CreatedTime."'",$sql);
			$sql = str_replace("@ModifiedTime","'".$this->ModifiedTime."'",$sql);

			$this->data->executeNonQuery($sql);
			if(!$this->data->error)
				return true;
			return false;
		}

		//查詢
		public function executeRecordset($filter="",$orderExp="",$start=0,$length=-1){
			$sql = "SELECT a.* FROM `user` a";
			if(trim($filter) != "")
				$sql .= " WHERE ".$filter;
			if(trim($orderExp) != "")
				$sql .=" ORDER BY ".$orderExp;
			else
				$sql .= " ORDER BY a.CreatedTime DESC";

			return $this->data->executeRecordset($sql,$start,$length);
		}

		//刪除
		public function deleteRecord($userID){
			$sql = "DELETE FROM `user` WHERE UserID='".$userID."'";
			$this->data->executeNonQuery($sql);
			if(!$this->data->error)
				return true;
			return false;
		}

		//檢查Email唯一性
		public function checkEmailExists($email){
			$sql = "SELECT COUNT(*) FROM `user` WHERE Email='".$email."' AND UserID != '".$this->UserID."'";
			$num = $this->data->executeScalar($sql);
			if($num > 0)
				return true;
			return false;
		}

		//查詢計數
		public function countRecordset($filter){
			$sql = "SELECT COUNT(*) FROM `user` a";
			if($filter != "")
				$sql .= " WHERE ".$filter;

			return $this->data->executeScalar($sql);
		}

		//批量刪除，傳入參數為用逗號分隔的id镩
		public function batDelete($userIDs){
			$sql = "DELETE FROM `user` WHERE UserID IN (".$userIDs.")";
			$this->data->executeNonQuery($sql);

			if(!$this->data->error)
				return true;
			return false;
		}

		//批量修改用戶啟用狀態
		public function batUpdateVisible($userIDs,$value="1"){
			$sql = "UPDATE user SET Flags='".$value."' WHERE UserID IN (".$userIDs.")";
			$this->data->executeNonQuery($sql);
			if(!$this->data->error)
				return true;
			else
				return false;
		}
	}
?>