<?
class DB {
	var $conID;
	var $database;
	var $lastErrorMsg;
	var $PHPVERSION;
	var $MYSQLVERSION;
	var $fetchMode = MYSQL_BOTH;
	var $charset   = "UTF-8";

	//資料庫資訊
	function DB($AutoContent = true)
	{
		if ($AutoContent)
		{
			//設定 DNS
			$this->host = DB_HOST;
			$this->database = DB_DATABASE;
			$this->username = DB_USERNAME;
			$this->password = DB_PASSWORD;

			//設定 Options
			$options = array(
				PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8",		//設定資料庫語系
				PDO::ATTR_PERSISTENT         => true,					//資料庫長連接
				PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION	//顯示異常
			);

			//例外處理
			try {
				$this->dbh = new PDO("mysql:host=".$this->host.";dbname=".$this->database,$this->username,$this->password,$options);
			}

			catch (PDOException $e) {
				var_dump($e->getMessage());
			}
		}
	}

	//取得單筆資料
	function GetRow($sql)
	{
		$st = $this->dbh->prepare($sql);
		$st->execute();

		return $st->fetch();
	}

	//取得筆數
	function GetNumRow($sql)
	{
		$st = $this->dbh->prepare($sql);
		$st->execute();

		return $st->rowCount();
	}

	//資料操作
	function Execute($sql)
	{
		$sql = trim($sql);

		if ((strncasecmp('SELECT', $sql, 6) == 0) OR (strncasecmp('SHOW', $sql, 4) == 0))
		{
			return $this->dbh->query($sql);
		}
		else
		{
			return $this->_Query($sql);
		}
	}

	//新增
	function Insert($table, $arr = '')
	{
		$post = $this->GetPost();

		if (!empty($arr))
		{
			$post = array_merge($post, $arr);
		}
		return $this->Execute($this->GetInsertSQL($this->Select($table, $where = ''), $post));
	}

	//處理新增的SQL字串
	function GetInsertSQL($sql, $array)
	{
		$sql_k = array();
		$sql_v = array();

		$rs = $this->dbh->query($sql);

		for ($i = 0; $i < $rs->columnCount(); $i++)
		{
			$col = $rs->getColumnMeta($i);

			$name  = $col['name'];
			$type  = $col['pdo_type'];
			$len   = $col['len'];
			$flags = $col['flags'];
			$table = $col['table'];

			if (isset($array[$name]))
			{
				$sql_k[] = "`" .$name. "`";

				if (strncasecmp('date', $type, 4) == 0 OR strncasecmp('int', $type, 3) == 0)
				{
					$sql_v[] = $this->Qstr($array[$name]);
				}
				else
				{
					$sql_v[] = "'" .$array[$name]. "'";
				}
			}
		}

		$count = count($sql_k);

		$sql_k_str = implode(',', $sql_k);
		$sql_v_str = implode(',', $sql_v);

		$sql = "INSERT INTO $table ($sql_k_str) VALUES ($sql_v_str)";

		unset($sql_k,$sql_v);

		return $sql;
	}

	//更新
	function Update($table, $where = '', $arr = '')
	{
		$post = $this->GetPost();

		if (!empty($arr))
		{
			$post = array_merge($post, $arr);
		}

		return $this->Execute($this->GetUpdateSQL($this->Select($table, $where), $post));
	}

	//處理更新的SQL字串
	function GetUpdateSQL($sql, $array)
	{
		$where = '';
		$sql_upper = strtoupper($sql);

		$pos = strpos($sql_upper, " WHERE ");

		if ($pos !== false)
		{
			$sql_len = strlen($sql);
			$offset =  $sql_len - $pos;

			if (false !== ($pos3 = strpos($sql_upper, " GROUP BY ")))
			{
				$offset = $pos3  - $pos;
			}
			elseif(false !== ($pos3 =strpos($sql_upper, " ORDER BY ")))
			{
				$offset = $pos3 - $pos;
			}

			$where = substr($sql, $pos, $offset);
		}

		$set_arr = array();

		$rs = $this->dbh->query($sql);

		for ($i = 0; $i < $rs->columnCount(); $i++)
		{
			$col = $rs->getColumnMeta($i);

			$name  = $col['name'];
			$type  = $col['pdo_type'];
			$len   = $col['len'];
			$flags = $col['flags'];
			$table = $col['table'];

			if(isset($array[$name]))
			{
				if(strncasecmp('date', $type, 4) == 0 || strncasecmp('int', $type, 3) == 0)
				{
					$v = $array[$name];
				}
				else
				{
					$v = "\"" .$array[$name]. "\"";
				}

				$set_arr[] = " `$name` = $v";
			}
		}

		if (!empty($set_arr))
		{
			$set = ' SET '. implode(',', $set_arr);
		}
		else
		{
			$set = ' SET 1=1 ';
		}

		$sql = "UPDATE $table $set $where";
echo $sql;
		return $sql;
	}

	//刪除
	function Delete($table, $where = '')
	{
		if ($where != '')
		{
			$where = " WHERE $where";
		}

		return $this->Execute("DELETE FROM $table $where");
	}

	//新增表格欄位
	function AlterAdd($table, $tfield, $tmemo)
	{
		return $this->Execute("ALTER TABLE `$table` ADD `$tfield` $tmemo");
	}

	//刪除表格欄位
	function AlterDelete($table, $tfield)
	{
		return $this->Execute("ALTER TABLE `$table` DROP `$tfield`");
	}

	//選取記錄並轉成陣列
	function GetArray($sql)
	{
		$rs = $this->Execute($sql);
		$rows = array();

		while ($row = $rs->fetch())
		{
			$rows[] = $row;
		}

		return $rows;
	}

	//選取異動的單筆記錄
	function Select($table, $where = '')
	{
		if ($where != '')
		{
			$prefix = substr(strtoupper(trim($where)), 0, 8);

			if (!in_array($prefix, array('GROUP BY','ORDER BY','WHERE 1')))
			{
				$where = " WHERE $where";
			}
		}

		if ($where != '')
			return "SELECT * FROM $table $where";
		else
			return "SELECT * FROM $table limit 0,1";
	}

	//取得新增的記錄編號
	function Insert_ID()
	{
		return $this->dbh->lastInsertId();
	}

	//執行sql字串
	function _Query($sql)
	{
		$sql = $this->PreFix($sql);

		if (DB_DEBUG == 'Y')
		{
			echo "<pre>\n";
			echo "$sql\n";
			echo "</pre>\n";
		}

		$st = $this->dbh->prepare($sql);

		return $st->execute();
	}

	//字串處理
	function Qstr($str)
	{
		$str = trim($str);
		//$str = addslashes($str);

		return $str;
	}

	//資料表前綴
	function PreFix($sql)
	{
		return str_replace("#@#", DB_PREFIX,$sql);
	}

	//處理HTML碼的字串編碼
	function HtmlEncode($str)
	{
		$ret = $this->Qstr($str);

		return htmlspecialchars($ret, ENT_QUOTES, $this->charset);
		//return $ret;
	}

	//取得POST元素， For GetInsertSQL, GetUpdateSQL
	function GetPost()
	{
		$num_args = func_num_args();
		$args     = func_get_args();
		$post     = array();

		foreach ($_POST as $k => $v)
		{
			if (is_array($v))
			{
				$tmp_arr = array();

				foreach ($v as $v_k => $v_v)
				{
					$tmp_arr[$v_k] = $this->HtmlEncode($v_v);
				}

				$post[$k] = implode(',', $tmp_arr);
				continue;
			}

			if ($num_args > 0 && in_array($k, $args))
			{
				$post[$k] = $this->Qstr($v);
			}
			else
			{
				$post[$k] = $this->HtmlEncode($v);
			}
		}

		return $post;
	}
}
?>