<?php
! defined ( 'P_W' ) && exit ( 'Forbidden' );
/*
 * 結構化查詢語句組裝器
 * @author L.IuHu.I@2010/10/19 developer.liuhui@gmail.com
 */
! defined ( 'PW_COLUMN' ) && define ( 'PW_COLUMN', 'column' ); //查詢字段
! defined ( 'PW_EXPR' ) && define ( 'PW_EXPR', 'expr' ); //查詢表達式
! defined ( 'PW_ORDERBY' ) && define ( 'PW_ORDERBY', 'orderby' ); //排序
! defined ( 'PW_GROUPBY' ) && define ( 'PW_GROUPBY', 'groupby' ); //分組
! defined ( 'PW_LIMIT' ) && define ( 'PW_LIMIT', 'limit' ); //分頁
! defined ( 'PW_ASC' ) && define ( 'PW_ASC', 'asc' ); //升序
! defined ( 'PW_DESC' ) && define ( 'PW_DESC', 'desc' ); //降序
define ( 'PW_DEBUG', 0 ); //是否開啟調試打印
class PW_QueryBuilder {
	/**
	 * 生成新增語句
	 * @param $tableName   數據表名稱,如:pw_threads
	 * @param $col_names   字段名稱數組,如:array('tid'=>1,'fid'=>2)
	 */
	function insertClause($tableName, $col_names) {
		if (! $tableName || ! is_array ( $col_names ))
			return '';
		$sql = "INSERT INTO " . S::sqlMetadata ( $tableName ) . " ";
		$sql .= $this->_parseSetSQL ( $col_names );
		$this->_smallHook ( 'insert', $sql, array ($tableName ), $col_names );
		return $sql;
	}
	/**
	 * 生成替換語句
	 * @param $tableName   數據表名稱,如:pw_threads
	 * @param $col_names   字段名稱數組,如:array('tid'=>1,'fid'=>2)
	 */
	function replaceClause($tableName, $col_names) {
		if (! $tableName || ! is_array ( $col_names ))
			return '';
		$sql = "REPLACE INTO " . S::sqlMetadata ( $tableName ) . " ";
		$sql .= $this->_parseSetSQL ( $col_names );
		$this->_smallHook ( 'replace', $sql, array ($tableName ), $col_names );
		return $sql;
	}
	/**
	 * 生成查詢語句
	 * @param $tableName         數據表名稱,如:pw_threads
	 * @param $where_statement   查詢條件語句,如:fid=:fid and ifcheck=:ifcheck,注意後面部分與字段一致
	 * @param $where_conditions  查詢條件參數,如array(1,2),與上在的條件語句順序保持一致
	 * @param $expand            擴展條件,可選,說明如下
	 * $expand = array(
	 * PW_COLUMN  = array('fid','tid'),//需要查詢的字段,默認為*,參數為數組
	 * PW_EXPR    = array('count(*) as c','max(tid)'),//特殊的查詢,如統計,最大/小值,參數為數組
	 * PW_ORDERBY = array('postdate'=> PW_ASC,'tid'=>PW_DESC),//排序條件,字段=>升/降形式,參數為數組
	 * PW_GROUPBY = array('tid'),//分組條件,數組
	 * PW_LIMIT   = array(offset,limit),查詢起點數與查詢個數
	 * );
	 */
	function selectClause($tableName, $where_statement = null, $where_conditions = null, $expand = null) {
		if (! $tableName)
			return '';
		list ( $where_statement, $fields ) = $this->_parseStatement ( $where_statement, $where_conditions );
		$sql = "SELECT ";
		$sql .= $this->_parseColumns ( isset ( $expand [PW_COLUMN] ) ? $expand [PW_COLUMN] : '', isset ( $expand [PW_EXPR] ) ? $expand [PW_EXPR] : '' );
		$sql .= " FROM " . S::sqlMetadata ( $tableName ) . " ";
		($where_statement) && $sql .= " WHERE " . $where_statement;
		(isset ( $expand [PW_GROUPBY] )) && $sql .= $this->_parseGroupBy ( $expand [PW_GROUPBY] );
		(isset ( $expand [PW_ORDERBY] )) && $sql .= $this->_parseOrderBy ( $expand [PW_ORDERBY] );
		(isset ( $expand [PW_LIMIT] )) && $sql .= $this->_parseLimit ( $expand [PW_LIMIT] );
		$this->_smallHook ( 'select', $sql, array ($tableName ), $fields );
		return $sql;
	}
	/**
	 * 生成更新語句
	 * @param $tableName        數據表名稱,如pw_threads
	 * @param $where_statement  同上 selectClause()參數
	 * @param $where_conditions 同上 selectClause()參數
	 * @param $col_names        字段名稱數組,如:array('tid'=>1,'fid'=>2)
	 * @param $expand           同上 selectClause()參數說明,但只有排序部分
	 */
	function updateClause($tableName, $where_statement = null, $where_conditions = null, $col_names, $expand = null) {
		if (! $tableName || (! is_array ( $col_names ) && ! isset ( $expand [PW_EXPR] )))
			return '';
		list ( $where_statement, $fields ) = $this->_parseStatement ( $where_statement, $where_conditions );
		$sql = "UPDATE " . S::sqlMetadata ( $tableName ) . " ";
		$sql .= $this->_parseSetSQL ( $col_names, (isset ( $expand [PW_EXPR] ) ? $expand [PW_EXPR] : '') );
		($where_statement) && $sql .= " WHERE " . $where_statement;
		(isset ( $expand [PW_ORDERBY] )) && $sql .= $this->_parseOrderBy ( $expand [PW_ORDERBY] );
		(isset ( $expand [PW_LIMIT] )) && $sql .= $this->_parseLimit ( $expand [PW_LIMIT] );
		$this->_smallHook ( 'update', $sql, array ($tableName ), $fields, $col_names );
		return $sql;
	}
	/**
	 * 生成刪除語句
	 * @param $tableName        數據表名稱,如pw_threads
	 * @param $where_statement  同上 selectClause()參數
	 * @param $where_conditions 同上 selectClause()參數
	 * @param $col_names        字段名稱數組,如:array('tid'=>1,'fid'=>2)
	 * @param $expand           同上 selectClause()參數說明,但只有排序部分
	 */
	function deleteClause($tableName, $where_statement = null, $where_conditions = null, $expand = null) {
		if (! $tableName)
			return '';
		list ( $where_statement, $fields ) = $this->_parseStatement ( $where_statement, $where_conditions );
		$sql = "DELETE FROM " . S::sqlMetadata ( $tableName ) . " ";
		($where_statement) && $sql .= " WHERE " . $where_statement;
		(isset ( $expand [PW_ORDERBY] )) && $sql .= $this->_parseOrderBy ( $expand [PW_ORDERBY] );
		(isset ( $expand [PW_LIMIT] )) && $sql .= $this->_parseLimit ( $expand [PW_LIMIT] );
		$this->_smallHook ( 'delete', $sql, array ($tableName ), $fields );
		return $sql;
	}
	/**
	 * 通用查詢語句組裝
	 * @param $format      查詢語句格式,注意數據表名采用:pw_table的形式,多個表名pw_table1,pw_table2
	 * @param $parameters  查詢語句變量
	 */
	function buildClause($format, $parameters, $clauses = array()) {
		if (! $format || ! is_array ( $parameters ))
			return '';
		list ( $sql, $matchInfo ) = $this->_parseStatement ( $format, $parameters, true );
		list ( $tables, $fields ) = $this->_parseMatchs ( $matchInfo );
		$this->_smallHook ( trim ( substr ( $format, 0, 7 ) ), $sql, $tables, $fields );
		return $sql;
	}
	/**
	 * 私有解析匹配結果，獲取數據表名稱和條件字段
	 * @param $matchInfo  匹配結果數組 
	 */
	function _parseMatchs($matchInfo) {
		if (! $matchInfo) {
			return array (array (), array () );
		}
		foreach ( $matchInfo as $k => $v ) {
			if (strpos ( $k, 'pw_table' ) !== false) {
				$tables [] = $v;
				unset ( $matchInfo [$k] );
			}
		}
		return array ($tables, $matchInfo );
	}
	
	/**
	 * 私有解析SET部分結構函數
	 * @param $arrays
	 */
	function _parseSetSQL($arrays, $expr = null) {
		if (! is_array ( $arrays ) && ! $expr) {
			return '';
		}
		$sets = " SET ";
		if ($expr) {
			foreach ( $expr as $v ) {
				$sets .= " " . $v . ",";
			}
		}
		if ($arrays) {
			foreach ( $arrays as $k => $v ) {
				$sets .= " " . S::sqlMetadata ( $k ) . " = " . S::sqlEscape ( $v ) . ",";
			}
		}
		$sets = trim ( $sets, "," );
		return ($sets) ? $sets : '';
	}
	/**
	 * 私有解析格式模板，並實現格式與參數匹配
	 * @param $statement
	 * @param $conditions
	 */
	function _parseStatement($statement, $conditions, $isCheck = false) {
		if (! $statement || ! is_array ( $conditions ))
			return array ('', array () );
		preg_match_all ( '/:(\w+)/', $statement, $matchs );
		if (! $matchs [0])
			return array ('', array () );
		$fields = array ();
		//fix WooYun-2011-02720.感謝Ray在 http://www.wooyun.org/bugs/wooyun-2010-02720 上的反饋
		$seg = randstr(4);
		$statement = preg_replace ('/(:\w+)/', $seg . '${1}' . $seg, $statement );
		foreach ( $matchs [0] as $k => $field ) {
			$fields [$matchs [1] [$k]] = $conditions [$k];
			$param = (is_array ( $conditions [$k] )) ? S::sqlImplode ( $conditions [$k] ) : (($isCheck && strpos ( $field, 'pw_table' ) !== false) ? $conditions [$k] : S::sqlEscape ( $conditions [$k] ));
			$statement = str_replace ( $seg . $field . $seg, $param, $statement );
		}
		return array ($statement, $fields );
	}
	
	/**
	 * 私有解析查詢字段部分
	 * @param $columns    字段數組
	 * @param $statements 特殊查詢語句
	 */
	function _parseColumns($columns, $statements) {
		$sql = '';
		if ($columns) {
			foreach ( $columns as $column ) {
				$sql .= S::sqlMetadata ( $column ) . ",";
			}
		}
		if ($statements) {
			foreach ( $statements as $statement ) {
				$sql .= $statement . ",";
			}
		}
		return ($sql) ? rtrim ( $sql, ',' ) : '*';
	}
	/**
	 * 私有解析分組語句
	 * @param $groupBy
	 */
	function _parseGroupBy($groupBys) {
		if (! $groupBys)
			return '';
		$sql = ' GROUP BY ';
		foreach ( $groupBys as $field ) {
			$sql .= S::sqlMetadata ( $field ) . ',';
		}
		$sql = rtrim ( $sql, ',' );
		return $sql;
	}
	/**
	 * 私用解析排序語句
	 * @param $orderBy
	 */
	function _parseOrderBy($orderBy) {
		if (! $orderBy)
			return '';
		$orderBy = (is_array ( $orderBy )) ? $orderBy : array ($orderBy );
		$sql = " ORDER BY ";
		foreach ( $orderBy as $field => $sort ) {
			if (! in_array ( strtolower ( $sort ), array (PW_DESC, PW_ASC ) ))
				continue;
			$sql .= S::sqlMetadata ( $field ) . " " . $sort . ",";
		}
		$sql = rtrim ( $sql, ',' );
		return $sql;
	}
	/**
	 * 私有解析分頁語句
	 * @param $offset
	 * @param $row_count
	 */
	function _parseLimit($limits) {
		$offset = S::int ( $limits [0] );
		$row_count = S::int ( $limits [1] );
		return ($offset >= 0 && $row_count > 0) ? " LIMIT " . $offset . "," . $row_count : '';
	}
	/**
	 * 調試SQL語句
	 * @param $sql
	 */
	function _debug($sql) {
		if (PW_DEBUG) {
			var_dump ( $sql );
		}
	}
	/**
	 * 小鉤子接口,用于實現可擴展
	 * @param $operate    操作行為,可選insert/replace/update/select
	 * @param $tableName  數據表名稱
	 * @param $fields     數據條件字段
	 */
	function _smallHook($operate, $sql, $tableNames = array(), $fields = array(), $expand = array()) {
		$this->_debug ( $sql );
		Perf::gatherQuery ( $operate, $tableNames, $fields, $expand );
		return true;
	}
}