<?php
	// DB用PHPロード
	require_once("MDB2.php");

	class CMyDBWrapper
	{
		var $DBObj = null;
		var $resultObject = null;

		var $iIDLastInsert = 0;

		var $phptype  = 'mysqli';
		var $username = DB_USER; // DB接続ユーザID
		var $password = DB_PASSWORD; // DB接続PASSWORD
		var $hostspec = DB_HOST; // サーバのドメイン
		var $database = DB_NAME; // データベースの名前

		// エラーチェック
		function checkError( $obj )
		{
			if( PEAR::isError( $obj ) )
			{
				print_r( $obj->userinfo );
				print_r( $obj->getMessage() );
				return false;
			}

			return true;
		}

		// コンストラクタとデストラクタ
		function __construct() {  }
		function __destruct() {  }

		// 接続
		function connect()
		{
			$dsn = array(
				'phptype'  => $this->phptype,
				'username' => $this->username,
				'password' => $this->password,
				'hostspec' => $this->hostspec,
				'database' => $this->database
			);

			// 接続
			$this->DBObj = MDB2::connect( $dsn );
			$this->checkError( $this->DBObj );

			$this->DBObj->query( "SET NAMES utf8mb4" );

			// 作成情報記録
			$this->DBObj->setFetchMode(MDB2_FETCHMODE_ASSOC);
		}

		// 接続
		function upperConnect()
		{
			$dsn = array(
				'phptype'  => $this->phptype,
				'username' => $this->username,
				'password' => $this->password,
				'hostspec' => $this->hostspec,
				'database' => $this->database
			);

			// 接続
			$this->DBObj = MDB2::connect( $dsn );
			$this->checkError( $this->DBObj );

			$this->DBObj->query( "SET NAMES utf8mb4" );

			// 作成情報記録
			$this->DBObj->setFetchMode(MDB2_FETCHMODE_ASSOC);
			$this->DBObj->setOption('field_case', CASE_UPPER);
		}

		// 切断
		function disconnect()
		{
			// 切断
			$this->DBObj->disconnect();
		}

		// クエリ作成
		function query( $szCommand )
		{
			$this->connect();

			$this->resultObject = $this->DBObj->query( $szCommand );

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$ret = $this->resultObject;

			$this->disconnect();

			return $ret;
		}

		// ライン情報取得
		function getIteratorLine()
		{
			return $this->resultObject->fetchRow();
		}

		// 件数（Y）の取得
		function getRowsNum()
		{
			return $this->resultObject->numRows();
		}

		// 項目数（X）の取得
		function getColsNum()
		{
			return $this->resultObject->numCols();
		}

		function selectTable( $tableName, $arrayNames, $hashWhere = null, $bDebug = false )
		{
			if( !$tableName || !$arrayNames )
				return false;

			$sql = "SELECT ";
			foreach( $arrayNames as $v )
				$sql .= $v . ",";

			// カンマを除去
			$sql = substr( $sql, 0, -1 );
			$sql .= " FROM " . $tableName;

			if( $hashWhere )
			{
				$sql .= " WHERE ";
				foreach( $hashWhere as $k => $v )
					$sql .= $k . " = '" . $v . "' AND ";

				// " and "を除去
				$sql = substr( $sql, 0, -5 );
			}

			// SQL実行
			if( !$bDebug )	$ret = $this->query( $sql );
			else			print_r( $sql );

			if( !$ret || !$this->getRowsNum() )
			{
//				var_dump( $sql );
				return null;
			}

			return $ret;
		}

		function updateTable( $tableName, $hashValues, $hashWhere = null, $bDebug = false )
		{
			$sql = "";

			if( !$tableName ) return false;
			if( !$hashValues ) return false;

			$sql = "UPDATE ".$tableName." SET ";

			// キー名カラム名連結
			foreach( $hashValues as $key => $value ){
				if($value === NULL){
					$sql .= $key." = NULL,";
				}else{
					$sql .= $key." = '".$value."',";
				}
			}

			// シングルコート除去
			$sql = substr( $sql, 0, -1 );

			if( $hashWhere )
			{
				$sql .= " WHERE ";
				foreach( $hashWhere as $k => $v )
					$sql .= $k . " = '" . $v . "' AND ";

				// " and "を除去
				$sql = substr( $sql, 0, -5 );
			}

			// SQL実行
			if( !$bDebug )	$ret = $this->query( $sql );
			else			print_r( $sql );

			if(!$ret)
			{
				var_dump( $sql );
				return false;
			}

			return true;
		}

		function insertTable( $tableName, $hashValues, $bDebug = false )
		{
			$sql = "";

			if( !$tableName ) return false;
			if( !$hashValues ) return false;

			$sql = "INSERT INTO ".$tableName."(";
			$values = " VALUES (";

			// キー名カラム名連結
			foreach( $hashValues as $key => $value )
			{
				$sql .= $key.",";
				$values .= "'".$value."',";
			}

			// シングルコート除去
			$sql = substr( $sql, 0, -1 ) . ")";
			$values = substr( $values, 0, -1 ) . ")";

			$sql .= $values;

			// SQL実行
			if( !$bDebug )	$ret = $this->query( $sql );
			else			print_r( $sql );

			if(!$ret)
			{
				return false;
			}

			return true;
		}

		function InsertID( $tableName, $columnName )
		{
			$id = $this->DBObj->lastInsertId( $tableName, $columnName );
			print_r($id);
			return $id;
		}

		/*
		// 項目取得
		function getColNames( $szTableName )
		{
			$this->connect();

			// 配列の初期化
			$arrayCols = array();

			// １行分取得
//			$row = $this->DBObj->getAssoc( "show columns from " . $szTableName );
			$row = $this->DBObj->query( "show columns from " . $szTableName );

			// キーの取得
			while( list($key, $_value) = each( $row ) )
			{
				$arrayCols[] = $key;
			}

			$this->disconnect();

			return $arrayCols;
		}
		*/

		function queryRowPrepare( $sql, $params )
		{
				if( !$sql ) return false;

				$this->connect();

				$ret = null;

				if (is_array($params)) {
						foreach ($params as $param) {
								$escaped = "'" . $this->DBObj->escape($param) . "'";
								$sql = preg_replace('/\?/', $escaped, $sql, 1);
						}
				}

				$this->resultObject = $this->DBObj->query($sql);

				if( !$this->checkError( $this->resultObject ) )
						return null;

				$row = $this->resultObject->fetchRow(MDB2_FETCHMODE_ASSOC);

				if(is_array($row)){
						$ret = array();
						foreach($row as $key => $value){
								$ret[$key] = $value;
						}
				}else{
						$ret = $row;
				}

				$sth = null;

				$this->disconnect();

				return $ret;
		}

/*
		function queryRowPrepare( $sql, $params )
		{
				if( !$sql ) return false;

				$this->connect();

				$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_RESULT);
				$this->resultObject = $sth->execute($params);

				// チェック
				if( !$this->checkError( $this->resultObject ) )
						return null;

				$row = $this->resultObject->fetchRow(MDB2_FETCHMODE_ASSOC);
				if(is_array($row)){
						foreach($row as $key => $value){
								$ret[$key] = $value;
						}
				}else{
						$ret = $row;
				}

				$sth->free();

				$this->disconnect();

				return $ret;
		}
*/

		// プリペアドステートメントでクエリ発行（SELECT文/LIMIT句）
		function queryLimitPrepare( $sql, $params, $start_num, $pageSize )
		{
			if( !$sql ) return false;

			$this->connect();

			$this->DBObj->setLimit($pageSize, $start_num);

			$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_RESULT);
			$this->resultObject = $sth->execute($params);

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$row = $this->resultObject->fetchAll(MDB2_FETCHMODE_ASSOC);
			foreach($row as $key => $value){
				$ret[$key] = $value;
			}

			$sth->free();

			$this->disconnect();

			return $ret;
		}

		// プリペアドステートメントでクエリ発行（SELECT文（配列で返す））
		function queryAllPrepare( $sql, $params )
		{
			if( !$sql ) return false;
			$ret = "";

			$this->connect();

			$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_RESULT);
			if (PEAR::isError($sth)){
				echo $sth->getDebugInfo();
				exit();
			}
			$this->resultObject = $sth->execute($params);

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$row = $this->resultObject->fetchAll(MDB2_FETCHMODE_ASSOC);
			foreach($row as $key => $value){
				$ret[$key] = $value;
			}

			$sth->free();

			$this->disconnect();

			return $ret;
		}

		// プリペアドステートメントでクエリ発行（DELETE文）
		function queryDeletePrepare($table_name, $hash_where)
		{
			$sql = "";

			if( !$table_name ) return false;
			if( !$hash_where ) return false;

			$sql = "DELETE FROM ".$table_name." ";

			// キー名カラム名連結
			$last_key = end(array_keys($hash_where));
			$sql .= " WHERE ";
			foreach($hash_where as $key => $value){
				$sql .= $key . " = ? ";
				if($last_key != $key){
					$sql .= " AND ";
				}
				$params[] = $value;
			}

			$this->connect();

			$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_MANIP);
			$this->resultObject = $sth->execute($params);

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$sth->free();

			$this->disconnect();

			return true;
		}

		// プリペアドステートメントでクエリ発行（INSERT文）
		function queryInsertPrepare($table_name, $hash_values){
			$sql = "";

			if( !$table_name ) return false;
			if( !$hash_values ) return false;

			$sql = "INSERT INTO ".$table_name." ( ";
			$values = " VALUES (";

			// キー名カラム名連結
			$last_key = end(array_keys($hash_values));
			foreach($hash_values as $key => $value){
				$sql .= $key;
				if($key != $last_key){
					$sql .= ", ";
				}else{
					$sql .= " ) ";
				}
				$values .= " ?,";
				$params[] = $value;
			}

			// シングルコート除去
			$values = substr( $values, 0, -1 ) . ")";

			$sql .= $values;

			$this->connect();

			$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_MANIP);
			$this->resultObject = $sth->execute($params);

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$sth->free();
			$params = array();
			$id = $this->queryRowPrepare( "SELECT LAST_INSERT_ID() AS id", $params );

			$this->disconnect();

			return $id['id'];
		}

		// プリペアドステートメントでクエリ発行（UPDATE文）
		function queryUpdatePrepare($table_name, $hash_values, $hash_where = null, $bDebug = false)
		{
			$sql = "";

			if( !$table_name ) return false;
			if( !$hash_values ) return false;

			$sql = "UPDATE ".$table_name." SET ";

			// キー名カラム名連結
			$last_key = end(array_keys($hash_values));
			foreach($hash_values as $key => $value){
				$sql .= $key." = ?";
				if($key != $last_key){
					$sql .= ", ";
				}
				$params[] = $value;
			}

			if($hash_where){
				$last_key = end(array_keys($hash_where));
				$sql .= " WHERE ";
				foreach($hash_where as $k => $v){
					$sql .= $k . " = ? ";
					if($last_key != $k){
						$sql .= " AND ";
					}
					$params[] = $v;
				}
			}

			$this->connect();

			$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_MANIP);
			$this->resultObject = $sth->execute($params);

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$sth->free();

			$this->disconnect();

			return true;
		}

		// プリペアドステートメントでクエリ発行（SELECT文/大文字返却）
		function queryRowUpperPrepare( $sql, $params )
		{
			if( !$sql ) return false;

			$this->upperConnect();

			$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_RESULT);
			$this->resultObject = $sth->execute($params);

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$row = $this->resultObject->fetchRow(MDB2_FETCHMODE_ASSOC);
			if(is_array($row)){
				foreach($row as $key => $value){
					$ret[$key] = $value;
				}
			}else{
				$ret = $row;
			}

			$sth->free();

			$this->disconnect();

			return $ret;
		}

		// プリペアドステートメントでクエリ発行（SELECT文/LIMIT句/大文字返却）
		function queryLimitUpperPrepare( $sql, $params, $start_num, $pageSize )
		{
			if( !$sql ) return false;

			$this->upperConnect();

			$this->DBObj->setLimit($pageSize, $start_num);

			$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_RESULT);
			$this->resultObject = $sth->execute($params);

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$row = $this->resultObject->fetchAll(MDB2_FETCHMODE_ASSOC);
			foreach($row as $key => $value){
				$ret[$key] = $value;
			}

			$sth->free();

			$this->disconnect();

			return $ret;
		}

		// プリペアドステートメントでクエリ発行（SELECT文（配列で返す）/大文字返却）
		function queryAllUpperPrepare( $sql, $params )
		{
			if( !$sql ) return false;
			$ret = "";

			$this->upperConnect();

			$sth = $this->DBObj->prepare($sql, NULL, MDB2_PREPARE_RESULT);
			if (PEAR::isError($sth)){
				echo $sth->getDebugInfo();
				exit();
			}
			$this->resultObject = $sth->execute($params);

			// チェック
			if( !$this->checkError( $this->resultObject ) )
				return null;

			$row = $this->resultObject->fetchAll(MDB2_FETCHMODE_ASSOC);
			foreach($row as $key => $value){
				$ret[$key] = $value;
			}

			$sth->free();

			$this->disconnect();

			return $ret;
		}
	}
?>