<?php
// PDOを用いたDBクラス

class DataSource {

	private $db=NULL;

	
	//テーブルのフィールドリスト auto_incrementは含めない
	public $userFields=array( 
		"name",
		"nameKana",
		"userType",
		"mail",
		"password",
		"capital",
		"businessType",
		"sale",
		"post",
		"chargeName",
		"chargeNameKana",
		"chargeMail",
		"url",
		"zipCode",
		"address",
		"tel",
		"mobileTel",
		"comment",
		"status",
		"registDate"
	);		
	
	public $preUserFields=array(
		"mail"
	);
	
	public $matterFields=array(
		"userId",
		"title",
		"status",
		"class",
		"category",
		"deliveryDate",
		"receiveDay",
		"receiveDate",
		"place1",
		"place2",
		"detail",
		"budgetMin",
		"budgetMax",
		"budgetUnit",
		"budgetType",
		"develop",
		"demand",
		"question",
		"registDate"
	);
	
	public $estimateFields=array(
	"matterId",
	"userId",
	"sujest",
	"price",
	"limitDay",
	"limitDate",
	"question",
	"answer",
	"status",
	"registDate"
	);
	
	
	public $contactFields=array(
"friendly",
"enough",
"price",
"originality",
"answer",
"question",
"charge",
"post",
"position",
"mail",
"zipCode",
"place1",
"place2",
"tel",
"mobileTel",
"estimateId",
"registDate",
	);	
	
	public $feedbackFields=array(
"userId",
"estimateId",
"progress",
"price",
"value",
"comment",
"registDate",


	);
	
	public $mailFields=array(
	"userId",
	"category"
	);
	
	//接続オープンする
	public function open($dsn, $user, $password) {
	
		try {
			$this->db=new PDO($dsn, $user, $password);
		} catch (PDOException $e) {
			return FALSE;
		}
		
		return TRUE;
	}
	
	
	//汎用の挿入関数
	public function insert($table, $fields, $record) {
	
		try {
			$fieldList="";
			$valueList="";
			$valueArray=array();
			$count=0;
			foreach ($fields as $field) {
				if ($count>0) {
					$fieldList .= ",";
					$valueList .= ",";
				}
				$fieldList .= $field;
				$valueList .= "?";
				array_push($valueArray, $record->$field);
				$count++;
			}
		
			$sql="INSERT INTO $table($fieldList) VALUES($valueList)";
		
			error_log("db insert : sql=$sql");
			
			$st=$this->db->prepare($sql);
			
			$st->execute($valueArray);
			return TRUE;
		
		} catch (PDOException $e) {
			return FALSE;
		}
		

	}
	
	//汎用のセレクト関数 オブジェクトの配列を返す
	public function select($table, $fields=NULL, $where="", $whereValues=NULL, $order="", $limit="") {
		try {
			if ($fields==NULL) {
				$fieldList="*";
			}
			else {
				$fieldList=join($fields, ",");
			}
			$sql="SELECT $fieldList FROM $table $where $order $limit";
			$st=$this->db->prepare($sql);
			if ($whereValues==NULL) {
				$st->execute();
			}
			else {
				$st->execute($whereValues);
			}
			
			return $st->fetchAll(PDO::FETCH_OBJ);
			
		} catch (PDOException $e) {
			return FALSE;
		}	
	}

	//汎用のカウント関数 オブジェクトの配列を返す
	public function count($table, $where="", $whereValues=NULL) {
		try {
			$sql="SELECT COUNT(*) FROM $table $where";
			$st=$this->db->prepare($sql);
			if ($whereValues==NULL) {
				$st->execute();
			}
			else {
				$st->execute($whereValues);
			}
			
			$count=$st->fetchColumn();
			$tmp=$st->fetchAll();
			
			return $count;
			
		} catch (PDOException $e) {
			return FALSE;
		}	
	}
	
	//汎用のアップデート関数
	public function update($table, $fields, $record, $where, $whereValues) {
		try {
			$setList="";
			$fieldCount=0;
			$values=array();
			foreach ($fields as $field) {
				if ($fieldCount>0) $setList .= ",";
				$setList .= "$field=?";
				array_push($values, $record->$field);
				$fieldCount++;
			}
			$sql="UPDATE $table SET $setList $where";
			$values=array_merge($values, $whereValues);
			$st=$this->db->prepare($sql);
			return $st->execute($values);
		} catch (PDOException $e) {
			return FALSE;
		}
	}
		
	//汎用の削除関数
	public function delete($table, $where, $whereValues) {
		try {
			$sql="DELETE FROM $table $where";
			$st=$this->db->prepare($sql);
			$st->execute($whereValues);
			return $st->rowCount();
		} catch (PDOException $e) {
			return FALSE;
		}
	}
		
	//最後のIDを取得する
	public function lastInsertId() {
		return $this->db->lastInsertId();
	}
				
	//ユーザーを登録する
	public function userInsert($record) {
		//$record->status=0;
		return $this->insert("muser", $this->userFields, $record);
	}
	
	//仮ユーザーを登録する
	public function preUserInsert($record) {
		//return $this->insert("preUser", $this->preUserFields, $record);
		$record->status=-1;
		return $this->insert("muser", $this->userFields, $record);
	}
	
	//ユーザー数を取得する
	public function userCount() {
		return $this->count("muser", "WHERE status>=0");
	}

	//仮ユーザー数を取得する
	public function preUserCount() {
		return $this->count("muser", "WHERE status=-1");
	}
	
	//ユーザーを取得する
	//IDの降順
	public function userSelect($startIndex, $maxCount) {
		$startIndexInt=(int)$startIndex;
		$maxCountInt=(int)$maxCount;
		$limit="LIMIT $startIndexInt, $maxCountInt";
		$order="ORDER BY id DESC";
		return $this->select("muser", NULL, "WHERE status>=0", NULL, $order, $limit);
	}

	//全ユーザーを取得する
	public function userSelectAll() {
		return $this->select("muser", NULL, "WHERE status>=0");
	}
	

	//指定したIDのユーザーを取得する
	public function userSelectById($id) {
		$records=$this->select("muser", NULL, "WHERE id=?", array($id));
		if ( $records===FALSE ) return FALSE;
		if ( count($records)<1 ) return FALSE;
		return $records[0];
	}
	
	//指定したpassword, mailのユーザーを取得する
	public function userSelectByPasswordMail($user) {
		$records=$this->select("muser", NULL, "WHERE password=? AND mail=?", array($user->password, $user->mail));
		if ( $records===FALSE ) return FALSE;
		if ( count($records)<1 ) return FALSE;
		return $records[0];
	}
		
	//ユーザーをフリーワードで検索しユーザー数を取得する
	//IDの降順
	public function userCountByWord($word) {
		$where="WHERE name LIKE ? OR  nameKana LIKE ? OR mail LIKE ? OR capital LIKE ? OR businessType LIKE ? OR sale LIKE ? OR post LIKE ? OR chargeName LIKE ? OR chargeNameKana LIKE ? OR chargeMail LIKE ? OR url LIKE ? OR zipCode LIKE ? OR address LIKE ? OR tel LIKE ? OR mobileTel LIKE ? OR comment LIKE ?";
		$whereValues=array_fill(0, 16, "%$word%");
		
		return $this->count("muser", $where, $whereValues);
	}	
	
	//指定した日付のユーザー数を取得する
	public function userCountByDate($date) {
		return $this->count("muser", "WHERE status>=0 AND registDate=?", array($date));
	}

	//指定した日付の仮登録ユーザー数を取得する
	public function preUserCountByDate($date) {
		return $this->count("muser", "WHERE status=-1 AND registDate=?", array($date));
	}
	
	//日別登録者数を取得する
	public function userCountOnDate() {
		try {
			$sql="SELECT registDate, COUNT(*) as count FROM muser GROUP BY registDate";
			$st=$this->db->prepare($sql);
			$st->execute();
			return $st->fetchAll(PDO::FETCH_OBJ);
			
		} catch (PDOException $e) {
			return FALSE;
		}	
	}
		
	//ユーザーをフリーワードで検索し結果を取得する
	//IDの降順
	public function userSelectByWord($word, $startIndex, $maxCount) {
		$startIndexInt=(int)$startIndex;
		$maxCountInt=(int)$maxCount;
		$limit="LIMIT $startIndexInt, $maxCountInt";
		$order="ORDER BY id DESC";
		$where="WHERE name LIKE ? OR  nameKana LIKE ? OR mail LIKE ? OR capital LIKE ? OR businessType LIKE ? OR sale LIKE ? OR post LIKE ? OR chargeName LIKE ? OR chargeNameKana LIKE ? OR chargeMail LIKE ? OR url LIKE ? OR zipCode LIKE ? OR address LIKE ? OR tel LIKE ? OR mobileTel LIKE ? OR comment LIKE ?";
		$whereValues=array_fill(0, 16, "%$word%");
		
		return $this->select("muser", NULL, $where, $whereValues, $order, $limit);
	}	
	
	//ユーザーの会員種別を変更する
	// record : id,userType
	public function userUserTypeUpdate($record) {
		return $this->update("muser", array("userType"), $record, "WHERE id=?", array($record->id));
	}

	//ユーザーのステータスを変更する
	// record : id,status
	public function userStatusUpdate($record) {
		return $this->update("muser", array("status"), $record, "WHERE id=?", array($record->id));
	}
	
	//指定したidのユーザーを更新する
	public function userUpdateById($id, $user) {
		return $this->update("muser", $this->userFields, $user, "WHERE id=?", array($id));
	}
	
	//指定したid,passwordのユーザーを削除する
	public function userDeleteByIdPassword($user) {
		return $this->delete("muser", "WHERE id=? AND password=?", array($user->id, $user->password));
	}
	
	
	//案件を挿入する
	public function matterInsert($matter) {
		return $this->insert("mmatter", $this->matterFields, $matter);
	}
	
	
	//全ての案件を取得する
	public function matterSelect($start, $count) {
		return $this->select("mmatter", NULL, "", NULL, "ORDER BY registDate DESC", "LIMIT $start, $count");
	}
	
	//指定したidの案件を取得する
	public function matterSelectById($id) {
		$records=$this->select("mmatter", NULL, "WHERE id=?", array($id));
		return $records[0];
	}
	
	//複合条件で案件を取得する
	public function matterSelectByCondition($condition, $start, $count) {
		$whereSqls=array();
		$whereValues=array();
		if ( $condition->category>0 ) {
			array_push($whereSqls, " category=? ");
			array_push($whereValues, $condition->category);
		}
		if ( $condition->deliveryType=="on" ) {
			array_push($whereSqls, " deliveryDate>=? ");
			array_push($whereValues, $condition->deliveryDate);
		}
		if ( $condition->place1>0 ) {
			array_push($whereSqls, " place1=? ");
			array_push($whereValues, $condition->place1);
		}
		if ( $condition->place2!="" ) {
			array_push($whereSqls, " place2=? ");
			array_push($whereValues, $condition->place2);
		}
		if ( isset($condition->receiveDate) ) {
			array_push($whereSqls, " receiveDate>=? ");
			array_push($whereValues, $condition->receiveDate);
		}
		
		if ( count($whereSqls)>0 ) {
			$where = "WHERE status>=1 AND " . implode("AND", $whereSqls);
		}
		else {
			$where="WHERE status>=1";
			$whereValues=NULL;
		}
		return $this->select("mmatter", NULL, $where, $whereValues, " ORDER BY id DESC ", " LIMIT $start, $count");
	}


	//複合条件で案件数を取得する
	public function matterCountByCondition($condition, $start, $count) {
		$whereSqls=array();
		$whereValues=array();
		if ( $condition->category>0 ) {
			array_push($whereSqls, " category=? ");
			array_push($whereValues, $condition->category);
		}
		if ( $condition->deliveryType=="on" ) {
			array_push($whereSqls, " deliveryDate>=? ");
			array_push($whereValues, $condition->deliveryDate);
		}
		if ( $condition->place1>0 ) {
			array_push($whereSqls, " place1=? ");
			array_push($whereValues, $condition->place1);
		}
		if ( $condition->place2!="" ) {
			array_push($whereSqls, " place2=? ");
			array_push($whereValues, $condition->place2);
		}
		if ( isset($condition->receiveDate) ) {
			array_push($whereSqls, " receiveDate>=? ");
			array_push($whereValues, $condition->receiveDate );
		}
		
		if ( count($whereSqls)>0 ) {
			$where = "WHERE status>=1 AND " . implode("AND", $whereSqls);
		}
		else {
			$where="WHERE status>=1";
			$whereValues=NULL;
		}
		return $this->count("mmatter",$where, $whereValues);
	}
	
	//statusで指定した案件を取得する
	public function matterSelectByStatus($status) {
		return $this->select("mmatter", NULL, "WHERE status=?", array($status), "ORDER BY registDate DESC");
	}

	public function matterSelectForTopNew() {
		return $this->select("mmatter", NULL, "WHERE status=1 AND newOrder>=1 AND receiveDate>=DATE(NOW())", NULL, "ORDER BY newOrder DESC");
	}

	public function matterSelectForNew() {
		return $this->select("mmatter", NULL, "WHERE status>=1 AND receiveDate>=DATE(NOW())", NULL, "ORDER BY registDate DESC");
	}

	public function matterSelectForTopNice() {
		return $this->select("mmatter", NULL, "WHERE status>=1 AND niceOrder>=1 AND class=1 AND receiveDate>=DATE(NOW())", NULL, "ORDER BY niceOrder DESC");
	}

	public function matterSelectForNice() {
		return $this->select("mmatter", NULL, "WHERE status>=1 AND class>=1 AND receiveDate>=DATE(NOW()) ", NULL, "ORDER BY niceOrder DESC");
	}

	//statusで指定した以上の案件を取得する
	public function matterSelectByStatusMin($status) {
		return $this->select("mmatter", NULL, "WHERE status>=?", array($status), "ORDER BY registDate DESC");
	}


	//status, classで指定した案件を取得する
	public function matterSelectByStatusMinClass($matter) {
		return $this->select("mmatter", NULL, "WHERE status>=? AND class=?", array($matter->status, $matter->class), "ORDER BY id DESC");
	}
	//status, classで指定した以上の案件を取得する
	public function matterSelectByStatusClassMin($matter) {
		return $this->select("mmatter", NULL, "WHERE status>=? AND class>=?", array($matter->status, $matter->class), "ORDER BY id DESC");
	}
	
	//userId, statusで指定した案件を取得する
	public function matterSelectByUserIdStatus($matter) {
		return $this->select("mmatter", NULL, "WHERE userId=?", array($matter->userId));
	}

	//案件数を取得する
	public function matterCount() {
		return $this->count("mmatter");
	}
	

	//案件をフリーワードで検索し案件数を取得する
	//IDの降順
	public function matterCountByWord($word) {
		$where="WHERE (title LIKE ? OR place2 LIKE ? OR detail LIKE ? OR develop LIKE ? OR demand LIKE ? OR question LIKE ?) AND status>=1";
		$whereValues=array_fill(0, 6, "%$word%");
		
		return $this->count("mmatter", $where, $whereValues);
	}	
	
	//案件ーをフリーワードで検索し結果を取得する
	//IDの降順
	public function matterSelectByWord($word, $startIndex, $maxCount) {
		$startIndexInt=(int)$startIndex;
		$maxCountInt=(int)$maxCount;
		$limit="LIMIT $startIndexInt, $maxCountInt";
		$order="ORDER BY id DESC";
		$where="WHERE title LIKE ? OR place2 LIKE ? OR detail LIKE ? OR develop LIKE ? OR demand LIKE ? OR question LIKE ?";
		$whereValues=array_fill(0, 6, "%$word%");
		
		return $this->select("mmatter", NULL, $where, $whereValues, $order, $limit);		}	


	//案件ーをフリーワードおよびステータスの最小値で検索し結果を取得する
	//IDの降順
	public function matterSelectByWordStatusMin($word, $status, $startIndex, $maxCount) {
		$startIndexInt=(int)$startIndex;
		$maxCountInt=(int)$maxCount;
		$limit="LIMIT $startIndexInt, $maxCountInt";
		$order="ORDER BY id DESC";
		$where="WHERE ( mmatter.title LIKE ? OR place2 LIKE ? OR detail LIKE ? OR develop LIKE ? OR demand LIKE ? OR question LIKE ? OR mcategory.title LIKE ? ) AND status>=? AND mmatter.category=mcategory.id";
		$whereValues=array_fill(0, 7, "%$word%");
		array_push($whereValues, $status);
		return $this->select("mmatter,mcategory", array("mmatter.*"), $where, $whereValues, $order, $limit);		}	

	//案件数をフリーワードおよびステータスの最小値で検索し取得する
	//IDの降順
	public function matterCountByWordStatusMin($word, $status) {
		$where="WHERE ( mmatter.title LIKE ? OR place2 LIKE ? OR detail LIKE ? OR develop LIKE ? OR demand LIKE ? OR question LIKE ? OR mcategory.title LIKE ? ) AND status>=? AND mmatter.category=mcategory.id";
		$whereValues=array_fill(0, 7, "%$word%");
		array_push($whereValues, $status);
		return $this->count("mmatter,mcategory", $where, $whereValues);
	}	
	
		//指定したidの案件のステータスを更新する
		public function matterUpdateStatusById($id, $matter) {
			return $this->update("mmatter", array("status"), $matter, "WHERE id=?", array($id));
			
		}
		
		//指定したユーザーの案件数を取得する
		function matterCountByUserId($userId) {
			return $this->count("mmatter", "WHERE userId=?", array($userId));
		}

		//指定したuserIdの案件のうち仮登録のものを本登録に変更する
		public function matterUpdateForUserRegist($userId) {
			$matter->status=0;
			return $this->update("mmatter", array("status"), $matter, "WHERE userId=? AND status<0", array($userId));
			
		}

		//指定したidの案件のクラスを更新する
		public function matterUpdateClassById($id, $matter) {
			return $this->update("mmatter", array("class"), $matter, "WHERE id=?", array($id));
		}
		
		//指定したidの案件のnewOrderを更新する
		public function matterUpdateNewOrderById($id, $matter) {
			return $this->update("mmatter", array("newOrder"), $matter, "WHERE id=?", array($id));
		}

		//指定したidの案件のniceOrderを更新する
		public function matterUpdateNiceOrderById($id, $matter) {
			return $this->update("mmatter", array("niceOrder"), $matter, "WHERE id=?", array($id));
		}
		
		//指定したidの案件を削除する
		public function matterDeleteById($id) {
			return $this->delete("mmatter", "WHERE id=?", array($id));
		}
		
		
		//見積もりを挿入する
		public function estimateInsert($record) {
			return $this->insert("testimate", $this->estimateFields, $record);
		}
		
		//指定したmatteIdの見積もり数を取得する
		public function estimateCountByMatterId($matterId) {
			return $this->count("testimate", "WHERE matterId=?", array($matterId));
		}
		
		//指定したidの見積もりを取得する
		public function estimateSelectById($id) {
			$records=$this->select("testimate", NULL, "WHERE id=?", array($id));
			return $records[0];
		}

		//指定したmatterIdの見積もりを取得する
		public function estimateSelectByMatterId($matterId) {
			return $this->select("testimate", NULL, "WHERE matterId=?", array($matterId));
		}
		
		//指定したuserIdの見積もりを取得する
		public function estimateSelectByUserId($userId) {
			return $this->select("testimate", NULL, "WHERE userId=?", array($userId));
		}
		
		//指定したuserIdの見積もりと対応する案件を取得する
		public function estimateMatterSelectByUserId($userId) {
			$fields=array(
	"testimate.id",
	"testimate.matterId",
	"testimate.userId",
	"testimate.sujest",
	"testimate.price",
	"testimate.limitDay",
	"testimate.limitDate",
	"testimate.question",
	"testimate.answer",
	"testimate.status",
	"testimate.registDate"
	);			
			array_push($fields, "mmatter.registDate as matterRegistDate");
			array_push($fields, "mmatter.title as matterTitle");
			return $this->select("testimate, mmatter", $fields, "WHERE testimate.matterId=mmatter.id AND  testimate.userId=?", array($userId));
		}
		
		
		//商談を挿入する
		public function contactInsert($record) {
			return $this->insert("tcontact", $this->contactFields, $record);
		}
		
		//指定した見積に対する商談を取得する
		public function contactSelectByEstimateId($estimateId) {
			$records=$this->select("tcontact", $this->contactFields, "WHERE estimateId=?", array($estimateId));
			return $records[0];
		}
		
		//フィードバックを挿入する
		public function feedbackInsert($record) {
			return $this->insert("tfeedback", $this->feedbackFields, $record);
		}
		
		//指定したユーザーに対するフィードバックを取得する
		public function feedbackSelectByUserId($userId) {
			return $this->select("tfeedback, testimate", array("tfeedback.*"), "WHERE tfeedback.estimateId=testimate.id AND testimate.userId=?", array($userId), "ORDER BY tfeedback.registDate" );
		}
		
		//指定したユーザーに対するフィードバック平均点を取得する
		public function feedbackAverageByUserId($userId) {
			$records=$this->select("tfeedback, testimate", array("tfeedback.*"), "WHERE tfeedback.estimateId=testimate.id AND testimate.userId=?", array($userId), "ORDER BY tfeedback.registDate" );
			$point=0;
			foreach ($records as $r) {
				$point+=$r->value;
			}
			$count=count($records);
			if ($count>0) return $point/$count;
			else return "-";

		}
		
		//指定したユーザーに対するフィードバック数を取得する
		public function feedbackCountByUserId($userId) {
			return $this->count("tfeedback, testimate", "WHERE tfeedback.estimateId=testimate.id AND testimate.userId=?", array($userId));
		}
		
		//指定したユーザーがだしたフィードバック数を取得する
		public function feedbackCountByUserIdOrigin($userId) {
			return $this->count("tfeedback", "WHERE userId=?", array($userId));
		}
		
		//指定したユーザーがだした成約時フィードバック数を取得する
		public function feedbackCountByUserIdOriginCommit($userId) {
			return $this->count("tfeedback", "WHERE userId=? AND progress=1", array($userId));
		}
		
		//指定したユーザーがだしたフィードバック平均点を取得する
		public function feedbackAverageByUserIdOrigin($userId) {
			$records=$this->select("tfeedback", NULL, "WHERE userId=?", array($userId));
			$point=0;
			foreach ($records as $r) {
				$point+=$r->value;
			}
			$count=count($records);
			if ($count>0) return $point/$count;
			else return "-";
			
		}
		
		//メール通知を挿入する
		public function mailInsert($mail) {
			return $this->insert("tmail", $this->mailFields, $mail);
		}
		
		//指定したユーザーのメール通知を削除する
		public function mailDeleteByUserId($userId) {
			return $this->delete("tmail", "WHERE userId=?", array($userId));
		}
		
		//指定したユーザーのメール通知を取得する
		public function mailSelectByUserId($userId) {
			return $this->select("tmail", NULL, "WHERE userId=?", array($userId));
		}
		
		//指定したカテゴリーのメール通知を取得する
		public function mailSelectByCategory($category) {
			return $this->select("tmail", NULL, "WHERE category=?", array($category));
		}	
}