<?php
/**
* MySQL 的 SQL 构建类
*
* - 方法可以多次调用，会形成合集
* - 所有地方都有支持原生的处理
* @version 1.0.0
*/
class sqler
{
    /** 当前SQL */
    protected $script = '';
    /**db */
    protected $db = null;
    protected $table = [];
    protected $keys = null;
    protected $fields = [];
    protected $distinctFields = null;
    protected $join = [];
    protected $sqlJoin=[];
    protected $where = [];
    protected $groupBy = [];
    protected $having = [];
    protected $orderBy = [];
    protected $limit = 0;
    protected $offset = -1;
    /**查询参数值(?id)绑定*/
    protected $params = [];
    /**待插入或更新的字段与值 使用 set() 方法*/
    protected $sets = [];
    /**待批量插入的值  使用 push() 方法*/
    public $lots = [];
    /**联表插入中的表语句 */
    protected $insertFromTableSQL = '';
    /** 返回的数据集使用某字段做为数组主键 */
    public $mapFetchKeyValue = null;
    /** 返回的一维数组数据集值指定的字段 */
    public $columnField = null;
    

    /** 分页相关 */
    /** 用于统计的字段 */
    public $countFields = null;
    /** 总记录数 */
    public $records = null;
    /** 一页多少条记录 */
    public $pagesize = null;
    /** 当前页码 */
    public $page = null;
    /** 总页码数 */
    public $total = null;

    /**
     * 构造
     *
     * @param db $db 数据库db类
     */
	public function __construct(db $db = null)
    {
        $this->bindingDb($db);
    }

    /**
     * 清空所有设定内容
     */
    public function clear()
    {
        $this->script = '';
        $this->table = [];
        $this->sets = [];
        $this->insertFromTableSQL = '';
        $this->mapFetchKeyValue = null;
        $this->columnField = null;
        $this->clearJoins();
        $this->clearWhereHavingGroupOrderLimit();
        $this->clearFields();
        $this->clearLots();
        $this->params = [];
        $this->keys = [];
        $this->distinctFields = [];
        $this->countFields = null;
        $this->clearLimit();
        $this->clearPages();
    }

    /**
     * 选择数据库db类
     *
     * @param db $db 数据库db类
     */
	public function bindingDb(db $db = null)
    {
        if(!is_null($db)){
            $this->db = $db;
        }
    }

    /**
     * 检测是否有绑定数据库连接,不存在则抛出错误
     */
    public function checkDb()
    {
        if(is_null($this->db)){
            throw new \Exception("执行查询SQL失败,缺失数据库连接，须调用 bindingDb() 方法");
        }
    }

    /**
     * 取查询结果所有行
     *
     * @return array
     */
    public function all():array
    {
        $this->checkDb();
        return $this->db->select($this)->all();
    }

    /**
     * 取一行
     *
     * @return array
     */
    public function row():array
    {
        $this->checkDb();
        return $this->db->select($this)->row();
    }

    /**
     * 取一格
     *
     * @return mixed
     */
    public function one()
    {
        $this->checkDb();
        return $this->db->select($this)->one();
    }

    /**
     * 记录是否存在
     *
     * @return bool
     */
    public function has():bool
    {
        $this->checkDb();
        return $this->db->has($this);
    }

    /**
     * 集合函数
     * 对字段执行指定的数据库函数，并返回结果
     * @param string $function 函数的名称
     * @param string|array $field 字段或字段数组
     * @param string $params 参数
     * @return int|array
     */
    public function aggregation(string $function, $field)
    {
        $this->checkDb();
        $attrs = $this->buildSQL();
        $attrs['fields'] = [];
        $is_array = is_array($field);
        $fields = $is_array ? $field : [$field];
        foreach ($fields as $fieldName) {
            $attrs['fields'][] = $function . '(' . $fieldName . ')';
        }
        $attrs['fields'] = implode(',', $attrs['fields']);
        if($function=='COUNT'){
            if($field){
                $sql = qgprintf("SELECT COUNT(1) FROM (SELECT DISTINCT {:2} FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}) t", $attrs, $field);
            }else{
                $sql = qgprintf("SELECT {:fields} FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}", $attrs);
            }
            //$sql = qgprintf("SELECT {:fields} FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}", $attrs);
            //dump($sql);
        }else{
            $sql = qgprintf("SELECT {:fields} FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}{:limit}{:offset}", $attrs);
        }
        if ($is_array && count($fields)>1) {
            $result = $this->db->query($sql)->row(\PDO::FETCH_NUM);
        } else {
            $result = $this->db->query($sql)->one();
        }
        return $result ?: 0;
    }

    /**
     * 取指定字段最大值
     * max('id') 返回 一个整型值
     * max(['id','age']) 返回 一个一维整型数组
     * @param string|array $field
     * @return int|array
     */
    public function max($field)
    {
        return $this->aggregation('MAX', $field);
    }

    /**
     * 取指定字段最小值
     * @see \sqler::max();
     * @return int|array
     */
    public function min($field)
    {
        return $this->aggregation('MIN', $field);
    }

    /**
     * 取指定字段平均值
     * @see \sqler::max();
     * @return int|array
     */
    public function avg($field)
    {
        return $this->aggregation('AVG', $field);
    }

    /**
     * 取指定字段求和值
     * @see \sqler::max();
     * @return int|array
     */
    public function sum($field)
    {
        return $this->aggregation('SUM', $field);
    }

    /**
     * 取指定字段记录数量, 默认 1
     * @see \sqler::max();
     * @return int|array
     */
    public function count($field=null)
    {
        if (is_null($field)) {
            if (!empty($this->countFields)) {
                // 统计字段
                $field = implode(',', $this->countFields);
            } else if (!empty($this->distinctFields)) {
                // 唯一字段
                $field = implode(',', array_column($this->distinctFields, 0));
            } else {
                // 默认
                $field = 1;
            }
        }
        return $this->aggregation('COUNT', $field);
    }



    /**
     * 空格转AS关键字
     *
     * @param string $word
     * @return string
     */
	protected function spaceToAs(string $word):string
    {
        return  preg_replace('/(\w+)((\s+as\s+)|([\s]+))/i','$1 AS ',trim($word));
    }

    /**
     * sql转义
     *
     * @param string $word
     * @return string
     */
	protected function sqlSlashes(string $word):string
    {
        return str_replace("'","\'",trim($word));
    }

   
    /**
     * 清空所有join
     */
    public function clearJoins():self
    {
        $this->join = [];
        $this->sqlJoin = [];
        return $this;
    }

    /**
     * 清空 where,group,having order,limit,offset
     */
    public function clearWhere(): self
    {
        $this->clearWhereHavingGroupOrderLimit();
        return $this;
    }

    /**
     * 清空 where,group,having order,limit,offset
     */
    public function clearWhereHavingGroupOrderLimit(): self
    {
        $this->where = [];
        $this->groupBy = [];
        $this->having = [];
        $this->orderBy = [];
        $this->limit = 0;
        $this->offset = -1;
        return $this;
    }

    /**
     * 清空字段设定
     */
    public function clearFields():self
    {
        $this->fields = [];
        $this->keys = [];
        $this->countFields = null;
        $this->distinctFields = [];
        $this->columnField = null;
        $this->mapFetchKeyValue = null;
        return $this;
    }

    /**
     * 清空批量插入数据, 外层更大数数组分批时，为照顾内存时使用
     */
    public function clearLots():self
    {
        $this->lots = [];
        return $this;
    }
    

    /**
     * 清空Limit
     */
    public function clearLimit(): self
    {
        $this->limit = 0;
        $this->offset = -1;
        return $this;
    }

    /**
     * 清空页码信息
     */
    public function clearPages(): self
    {
        $this->records = null;
        $this->pagesize = null;
        $this->page = null;
        $this->total = null;
        $this->clearLimit();
        return $this;
    }

    /**
     * 遍历多维数组中所有元素形成一维
     *
     * @param array $arr
     * @return array
     */
    protected function recursionArrayUint(array $arr,\Closure $func=null):array
    {
        $result = [];
        foreach($arr as $k=>$v){
            if(is_array($v)){
                $result = array_merge($result,$this->recursionArrayUint($v));
            }else{
                if(!is_null($func)){
                    $v = $func($k,$v);
                }
                $result[$k] = $v;
            }
        }
        return $result;
    }

    /**
     * 构建
     *
     * @return array
     */
	protected function buildSQL():array
    {
        $attrs = [];
        if($this->table){
            $attrs['table'] = implode(',',$this->table);
        }else{
            throw new \Exception("构建SQL失败,缺失主表表名，须调用 table() 方法");
        }
        if($this->fields){
            $attrs['fields'] = implode(',',$this->fields);
        }else{
            $attrs['fields'] = '*';
        }
        if($this->join){
            $attrs['join'] = ' '.implode(' ',$this->join);
        }
        if($this->sqlJoin){
            $attrs['sqlJoin'] = ' '.implode(' ',$this->sqlJoin);
        }
        if($this->where){
            if(count($this->where)==1){
                $attrs['where'] = ' WHERE ' . $this->where[0];
            }else{
                $attrs['where'] = ' WHERE ('.implode(') AND (',$this->where).')';
            }
        }
        if($this->orderBy){
            $attrs['orderBy'] = ' ORDER BY '.implode(',',$this->orderBy);
        }
        if($this->groupBy){
            $attrs['groupBy'] = ' GROUP BY '.implode(',',$this->groupBy);
        }
        if($this->having){
            if(count($this->having)==1){
                $attrs['having'] = ' HAVING ' . $this->having[0];
            }else{
                $attrs['having'] = ' HAVING ('.implode(') AND (',$this->having).')';
            }
        }
        if($this->limit > 0){
            $attrs['limit'] = ' LIMIT '.$this->limit;
        }
        if($this->offset >= 0){
            $attrs['offset'] = ' OFFSET '.$this->offset;
        }

        $t = ['select','join','sqlJoin','sets','where','groupBy','having','orderBy','limit','offset'];
        foreach($t as $v){
            if(!isset($attrs[$v])) $attrs[$v]='';
        }
        return $attrs;
    }

    public function sql():string
    {
        return $this->script ?: $this->getSelectSQL();
    }

    public function getSelectSQL():string
    {
        $attrs = $this->buildSQL();
        $this->script = qgprintf("SELECT {:fields} FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}{:orderBy}{:limit}{:offset}",$attrs);
        return $this->script;
    }

    public function getHasSQL():string
    {
        $attrs = $this->buildSQL();
        $attrs['limit'] = ' LIMIT 1';
        $attrs['offset'] = '';
        $this->script = qgprintf("SELECT 1 FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}",$attrs);
        return $this->script;
    }

    public function getCountSQL(): string
    {
        $attrs = $this->buildSQL();
        $this->script = qgprintf("SELECT COUNT(1) as total FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}", $attrs);
        return $this->script;
    }

    /**
     * 构建插入语句
     *
     * @return string
     */
    public function getInsertSQL():string
    {
        if($this->lots){ //通过 add 添加的,走 批量 方法
            $this->script = $this->getInsertLotsSQLs();
        }else{
            $attrs = $this->buildSQL();
            if($this->sets){
                $attrs['sets'] = ' SET '.implode(',',$this->sets);
            }else{
                throw new \Exception('没有要插入的数据,须调用 set() 或者 push');
            }
            if($this->insertFromTableSQL){
                $attrs['select'] = ' '.$this->insertFromTableSQL;
            }
            $this->script = qgprintf("INSERT IGNORE INTO {:table}{:select}{:join}{:sqlJoin}{:sets}",$attrs);
        }
        return $this->script;
    }

    /**
     * 构建插入语句 - 批量
     *
     * @param int $chunkQty array_chunk 按多少条数来分割
     * @return array 多批插入SQL
     */
    public function getInsertLotsSQLs(int $chunkQty = 100):array
    {
        $result = [];
        $attrs = $this->buildSQL();
        if($this->lots){
            $fields = array_keys(reset($this->lots));
            $arr = array_chunk($this->lots,$chunkQty);
            foreach($arr as $i=>$rows){
                $values = [];
                foreach($rows as $row){
                    foreach($row as $k=>$v){
                        $type =  strtolower(gettype($v));
                        if ($type == 'string') {
                            $v = "'{$this->sqlSlashes($v)}'";
                        }else if($type == 'null') {
                            $v = "NULL";
                        } else {
                            ;
                        }
                        $row[$k] = $v;
                    }
                    $values[] = '('.implode(',',$row).')';
                }
                $attrs['fields'] = '`'.implode('`,`',$fields).'`';
                $attrs['values'] = implode(',',$values);
                $result[] = qgprintf("INSERT IGNORE INTO {:table} ({:fields}) VALUES {:values}",$attrs);
            }
            $this->script = reset($result);
        }
        return $result;
    }

    /**
     * 构建完全字段替换插入语句(原生 REPLACE INTO)
     *
     * @return string
     */
    public function getReplaceIntoSQL():string
    {
        $attrs = $this->buildSQL();
        if($this->sets){
            $attrs['sets'] = ' SET '.implode(',',$this->sets);
        }
        if($this->insertFromTableSQL){
            $attrs['select'] = ' '.$this->insertFromTableSQL;
        }
        $this->script = qgprintf("REPLACE INTO {:table}{:select}{:join}{:sqlJoin}{:sets}{:where}{:groupBy}{:having}{:orderBy}{:limit}{:offset}",$attrs);
        return $this->script;
    }

    public function getUpdateSQL():string
    {
        $attrs = $this->buildSQL();
        if($this->sets){
            $attrs['sets'] = ' SET '.implode(',',$this->sets);
        }
        $this->script = qgprintf("UPDATE {:table}{:join}{:sqlJoin}{:sets}{:where}{:groupBy}{:having}{:orderBy}{:limit}{:offset}",$attrs);
        return $this->script;
    }

    public function getDeleteSQL():string
    {
        $attrs = $this->buildSQL();
        $this->script = qgprintf("DELETE FROM {:table}{:join}{:sqlJoin}{:where}{:groupBy}{:having}{:orderBy}{:limit}{:offset}",$attrs);
        return $this->script;
    }

    public function getTruncateSQL():string
    {
        $attrs = $this->buildSQL();
        $this->script = qgprintf("TRUNCATE TABLE {:table}",$attrs);
        return $this->script;
    }
 
    /**
     * 主表 或 嵌套 tempTable()
     *
     * 调用 table() 会初始化所有之前的所有值的设定(绑定的参数值除外，清除参数值使用clearParams()方法)
     * @param string $tableName 表名
     * @param string $as 表的别名
     * @return self
     */
	public function table(string $tableName,string $as=''):self
    {
        $params = array_merge([], $this->params);
        $this->clear();
        $this->params = $params;
        $str = trim($tableName);
        if($str){
            if($as!='') $str .= ' AS '. str_replace('.','_',$as);
            $this->table[] = $str;
        }else{
            throw new \Exception("构建SQL失败,缺失主表表名");
        }
        return $this;
    }

	/**
     * 临时表
     *
     * @param string $tempTableName 临时表别名
     * @param Closure $func  function($t){SQL构建语句} $t 是一个 sqler 对象
     * @return string SQL
     * @example 
     * <pre>
     *  $t1 = $db->tempTable('t1',function($t){
     *      $t->table('user');
     *  });
     *  $rows = $db->table($t1)->all();
     * </pre>
     */
	public function tempTable(string $tempTableName,\Closure $func):string
    {
        $builder = new sqler();
        $func($builder);
        return '('.$builder->getSelectSQL().') AS '.$tempTableName;
    }

    /**
     * 联表插入
     *
     * @param \Closure $func
     * @return string
     * <pre>
     *  $db->table($destTable)->insertFromTable(function($t){
     *      $t->table('srcTable')->where('id{>}',1000);
     *  })->insert();
     * 将生成 INSERT INTO <destTable> SELECT * FROM <srcTable> WHERE id > 1000
     * </pre>
     */
	public function insertFromTable(\Closure $func):self
    {
        $builder = new sqler();
        $func($builder);
        $this->insertFromTableSQL = $builder->getSelectSQL();
        return $this;
    }
    
    /**
     * 字段
     *
     * @param array|string $var
     * @param string $as 别名
     * @param array|string $change 当传字符串时即为空时的默认值,包括 null 和 '', 当传数组时,将根据数组改变值
     * @example field('au_id','id')
     * @example field(['au_id id','au_name'])
     * @example field(['au_id'=>'id,'au_name'=>'name'])
     * @example field(['@1'=>'checkbox]) @支持数字，相当于 SELECT 1 AS checkbox FROM table
     * @example field('department_id','depa',0)  当为null 和 ''时,赋值 0
     * @example field('department_id','depa',['1'=>'aaa','2'=>'@name','{null}'=>'0','{else}'=>'@']) 
     * @example field(['department_id'=>['depa',['1'=>'aaa','2'=>'@name','{null}'=>'0','{else}'=>'@']]) 
     *  - {null} 为 null 或 '' 时
     *  - {else} 其他
     *  - @ 表示自己
     *  - @name 表示 name 字段
     *  - @SUM(price) 原生
     * @example field('au_id,au_name') 纯原生
     * @return self
     */
	public function field($var, string $as='', $change=null):self
    {
        if(is_null($var)){
            $this->fields = [];
            return $this;
        }
        if(is_string($var)){
            $var = ($as=='') ? [$var] : [$var=>[$as,$change]];
        }
        $s = '';
        foreach($var as $k=>$v){
            if($s!='') $s .= ',';
            if(is_numeric($k)){
                $sTemp = $this->spaceToAs($v);
                $aTemp = explode(' AS ',$sTemp);
                $aTemp[0] = $this->getTrueField($aTemp[0]);
                $s .= implode(' AS ',$aTemp);
            }else{
                $k = trim(preg_replace('/#[^\{]+/','',$k));
                if(substr($k,0,1)=='@'){
                    $k = substr($k,1);
                }
                $k = $this->getTrueField($k);
                if(is_string($v)){
                    $s .= $k.' AS '.$v;
                }else{
                    if(is_null($change)){
                        $s .= $k.' AS '.$v[0];
                    }else{
                        if(is_string($v[1])){
                            //当传字符串时即为空时的默认值,包括 null 和 ''
                            $s .= qgprintf(
                                "CASE {:field} WHEN null THEN {:val}  WHEN '' THEN {:val} ELSE {:field} END AS {:as}"
                                ,['field'=>$k,'as'=>$v[0],'val'=>$v[1]]
                            );
                        }else{
                            //当传数组时,将根据数组改变值
                            $temp = '';
                            foreach($v[1] as $old=>$new){
                                if($new=='@'){
                                    $new = $k;
                                }elseif(substr($new,0,1)=='@'){
                                        $new = substr($new,1);
                                }else{
                                    $new = $this->getTypeValue($new);
                                }
                                if($old=="{null}"){
                                    $temp .= sprintf(' WHEN null THEN %s',$new);
                                    $temp .= sprintf(" WHEN '' THEN %s",$new);
                                }elseif($old=="{else}"){
                                    $temp .= sprintf(' ELSE %s',$new);
                                }else{
                                    $old = $this->getTypeValue($old);
                                    $temp .= sprintf(' WHEN %s THEN %s',$old,$new);
                                }
                            }
                            $s .= 'CASE '. $k. $temp .' END AS '.$v[0];
                        }
                    }
                }
            }
        }
        if($s) $this->fields[] = $s;
        return $this;
    }

    /**
     * 设置关键字
     *
     * @param string|array $fields 关键字字段或者列表, key(null) 清空
     * @return self
     */
    public function key($fields): self
    {
        if (is_null($fields)) {
            $this->keys = [];
        } else {
            if (is_string($fields)) $fields = [$fields];
            $this->keys = $fields;
        }
        return $this;
    }
    /**
     * 设置用于统计的字段，一般是多表级表时使用,使用 coutField 独立计算，不影响 $q 中其他fields的逻辑
     *
     * @param string|array $fields 关键字字段或者列表, countField(null) 清空
     * @return self
     */
    public function countField($fields): self
    {
        if (is_null($fields)) {
            $this->countFields = [];
        } else {
            if (is_string($fields)) $fields = [$fields];
            $this->countFields = $fields;
        }
        return $this;
    }


    /**
     * 设置唯一键
     *
     * @param array|string $field
     * @param string $as 别名
     * @return self
     */
    public function distinct($field, $as=''): self
    {
        if (is_null($field)) {
            $this->distinctFields = [];
            return $this;
        }
        if($as=='') $as = $field;
        $this->field('DISTINCT('.$field.')', $as);
        $this->distinctFields[] = [$field, $as];
        return $this;
    }

    /**
     * 内联接
     *
     * @param array|string $table
     * @param array|string $on 参考where()
     * - @ 表示后面的内容不是字符串
     * @example join(["0_department depa"=>['core_department_id'=>'@core_user_department_id']])
     * @example join(["0_department depa"=>['core_department_id\{@\}'=>'core_user_department_id']])与上面等价
     * @example join('0_department','core_department_id=core_user_department_id') 原生条件
     * @return self
     */
	public function join($table,$on=''):self
    {
        $this->_join('INNER JOIN',$table,$on);
        return $this;
    }
    
    /**
     * 左外连接 参考参考 join()
     *
     * @param string $table
     * @param array|string $on
     * @return self
     */
	public function leftJoin($table,$on=''):self
    {
        $this->_join('LEFT OUTER JOIN',$table,$on);
        return $this;
    }
    
    /**
     * 左右完全外连接 参考参考 join()
     *
     * @param string $table
     * @param array|string $on
     * @return self
     */
	public function fullJoin($table,$on=''):self
    {
        $this->_join('FULL OUTER JOIN',$table,$on);
        return $this;
    }
    
	protected function _join($type,$table,$on=''):self
    {
        if (is_null($table)) {
            $this->join = [];
            $this->sqlJoin = [];
            return $this;
        }
        if(is_array($table)){
            $s = '';
            foreach($table as $name=>$on){
                if(is_array($on)){
                    $on = $this->getWhere($on);
                    $on = substr($on,1,-1);
                }
                $s = ' '.$type.' '.$this->spaceToAs($name).' ON '.$on;
            }
        }elseif(empty($on)){
            throw new \Exception("构建SQL失败,缺失JOIN条件");
        }else{
            if(is_array($on)){
                $on = $this->getWhere($on);
                $on = substr($on,1,-1);
            }
            $s = ' '.$type.' '.$this->spaceToAs($table).' ON '.$on;
        }
        $this->join[] = $s;
        return $this;
    }

    /**
     * 使用纯 sql 连接
     * sqlJoin("JOIN (SELECT * FROM temp) t1 ON t1.id = xid");
     *
     * @param array|string $value SQL语句或者数组SQL
     * @return self
     */
	public function sqlJoin($value):self
    {
        if (is_null($value)) {
            $this->join = [];
            $this->sqlJoin = [];
            return $this;
        }
        if(is_string($value)) $value = [$value];
        foreach($value as $s){
            $this->sqlJoin[] = $s;
        }
        return $this;
    }

	protected function getTrueField(string $field):string
    {
        $result = $field;
        return $result;
    }

    protected function getTypeValue($value, string $type = '')
    {
        $type =  $type ?: strtolower(gettype($value));
        switch ($type) {
            case 'boolean':
                $value = $value ? 1 : 0;
                break;
            case 'string':
                if (substr(trim($value), 0, 1) == '@') {
                    $value = substr(trim($value), 1);
                    if (substr($value, 0, 1) === "'" && substr($value, -1) === "'") {
                        //用单引号包括
                        $value = "'" . $this->sqlSlashes(trim($value, "'")) . "'";
                    } elseif (strpos($value, ' ') === false && strpos($value, '(') === false) {
                        //非 IS NUll 或者 函数 EXISTS(SELELCT 1 FROM...)
                        $value = $this->getTrueField($value);
                    }
                } else {
                    $value = "'" . $this->sqlSlashes($value) . "'";
                }
                break;
            case 'null':
                $value = 'NULL';
                break;
            default:
                $value = "'" . $this->sqlSlashes($value) . "'";
        }
        return $value;
    }

    /**
     * 将多个keyValue数组合并成一个，并将相同的键自动加 #xxx 处理
     *
     * @param array $args 一般来自 func_get_args()
     * @return array
     */
    protected function changeSameKeyRef(array $args):array
    {
        $result = [];
        foreach ($args as $i => $arg) {
            foreach ($arg as $field => $value) {
                if (isset($result[$field])) {
                    $op = '';
                    preg_match('/\{([^\}]+)\}/', $field, $m);
                    if (isset($m[1])) {
                        $op = strtolower(trim($m[1]));
                        $field = rtrim(substr($field, 0, strpos($field, $op) - 1));
                    }
                    $field = $field . '#' . $i;
                    if ($op) $field .= ' {' . $op . '}';
                }
                $result[$field] = $value;
            }
        }
        return $result;
    }

    protected function parseWhere(string $field, $rule='?'):string
    {
        if($field=='@'){
            return $rule;
        }
        if($rule && is_string($rule) && substr($rule,0,1)=='?'){
            $paramKey = substr($rule,1) ?: $field;
            if(!isset($this->params[$paramKey])){
                throw new \Exception("构建SQL失败,缺失参数值 ?".$paramKey." 的设定，须调用 params()");
            }
            $rule = $this->params[$paramKey];
        }
        $op = '=';
        $type = strtolower(gettype($rule));
        preg_match('/\{([^\}]+)\}/',$field,$m);
        if(isset($m[1])){
            $op = strtolower(trim($m[1]));
            $field = rtrim(substr($field,0,strpos($field,$op)-1));
        }
        $field = $this->getTrueField($field);
        $value = $rule;
        $s = '';
        if($type == 'null'){
            $s = ($op == '!' || $op == '<>' || $op == '!=') ? $field . " IS NOT NULL" : $field . " IS NULL";
        }elseif($op=='='){
            if(is_array($value)){
                $tmp = [];
                $hasNull = false;
                foreach($value as $k=>$v){
                    $t = $this->getTypeValue($v);
                    if($t === 'NULL') {
                        $hasNull = true;
                    }else{
                        $tmp[] = $t;
                    }
                }
                $value = implode(",",$tmp);
                if(count($tmp)==1){
                    if($value==='NULL'){
                        $s = $field . ' IS NULL';
                    }else{
                        $s = $field .'='. $value;
                    }
                }else{
                    if($hasNull){
                        //where('name',['a','',null]) 变成 (name IN('a','') OR name IS NULL)
                        $s = $field . " IN(" . $value . ") OR ". $field." IS NULL";
                    }else{
                        $s = $field . " IN(" . $value . ")";
                    }
                }
            }else{
                $value = $this->getTypeValue($value,$type);
                $s = $field .'='. $value;
            }
        } elseif (in_array($op, ['>', '>=', '<', '<='])) {
            $value = $this->getTypeValue($value, $type);
            $s = $field . $op . $value;
        }elseif($op == '!' || $op=='<>' || $op == '!='){
            if(is_array($value)){
                $tmp = [];
                $hasNull = false;
                foreach ($value as $k => $v) {
                    $t = $this->getTypeValue($v);
                    if ($t === 'NULL') {
                        $hasNull = true;
                    } else {
                        $tmp[] = $t;
                    }
                }
                $value = implode(",",$tmp);
                if(count($tmp)==1){
                    if ($value === 'NULL') {
                        $s = $field . ' IS NOT NULL';
                    } else {
                        $s = $field . '<>' . $value;
                    }
                }else{
                    if ($hasNull) {
                        $s = $field . " NOT IN(" . $value . ") AND " . $field . " IS NOT NULL";
                    } else {
                        $s = $field . "NOT IN(" . $value . ")";
                    }
                }
            }else{
                $value = $this->getTypeValue($value,$type);
                $s = $field .'<>'. $value;
            }
        }elseif($op=='...'){
            $value0 = $this->getTypeValue($value[0]);
            $value1 = $this->getTypeValue($value[1]);
            $s = $field . " BETWEEN " . $value0 . " AND ". $value1;
        }elseif($op=='FINDSET'){
            $value = $this->getTypeValue($value);
            $s = "FIND_IN_SET(".$value.",".$field.")";
        }elseif($op=='%'){
            $value = $this->sqlSlashes($value);
            $value = str_replace('%','%%',$value);
            $s = $field . " LIKE '%" . $value . "%'";
        }elseif($op=='!%'){
            $value = $this->sqlSlashes($value);
            $value = str_replace('%','%%',$value);
            $s = $field . " NOT LIKE '%" . $value . "%'";
        }elseif($op=='=%'){
            $value = $this->sqlSlashes($value);
            $value = str_replace('%','%%',$value);
            $s = $field . " LIKE '" . $value . "%'";
        }elseif($op=='%='){
            $value = $this->sqlSlashes($value);
            $value = str_replace('%','%%',$value);
            $s = $field . " LIKE '%" . $value . "'";
        }elseif($op=='@'){
            $s = $field .'='. $value;
        }elseif($op=='exists'){
            $s = 'EXISTS('. $value.')';
        }elseif($op=='!exists'){
            $s = 'NOT EXISTS('. $value.')';
        }else{
            throw new \Exception('构建SQL失败,Where op '.$field.'{'.$op.'} '."未定义");
        }
        return $s;
    }
    
	protected function getWhereRecursive(array $var,string $logic='AND')
    {
        $result = '';
        foreach($var as $k=>$v){
            $k = trim(preg_replace('/#[^\{]+/','',$k));
            $not = false;
            if($k=='NOT OR' || $k=='NOT AND'){
                $k = substr($k,4);
                $not = true;
            }
            if($k=='OR' || $k=='AND'){
                if($result!='') $result .= " AND ";
                $result .=  $this->getWhereRecursive($v,strtoupper($k));
                if($not){
                    $result = "NOT(".$result.")";
                }
            }else{
                if($result!='') $result .= " ". $logic." ";
                $result .= $this->parseWhere($k,$v);
            }
        }
        $result = "( ". $result." )";
        return $result;
    }
    
	protected function getWhere($var, $second='<<<@>>>',string $logic='AND')
    {
        if(is_string($var)){
           if($second==='<<<@>>>'){
                if(substr($var,0,1)=='?'){ //where('?id'),where('?id,name,age')
                    $var = substr($var,1);
                    $vars = explode(',',$var);
                    $result = [];
                    foreach($vars as $var){
                        $result[] = $this->getWhere($var,'?');
                    }
                    return $result;
                }else{
                    return  $var; //原生sql where('id<100')
                }
            }else{
                $var = [$var=>$second];
            }
        }
        $s = $this->getWhereRecursive($var,$logic);
        if($s){
            $s = substr($s,1,-1);
        }
        return $s;
    }

    /**
     * where条件组装
     * 
     * @param array:string $var
     * @param mixed $second 非数组下的条件参数,传''空字符也是会有条件的
     * @return self
     * @example where('core_user_department_id{...}',[1,50])
     * @example where(['core_user_department_id{...}'=>[1,50]])
     * @example where(['id'=>1,'id#abc'=>'2']) 键名相同的用#加锚点,锚点仅用于正确传递PHP数组，没其他意义
     * @example where(['id'=>1],['id'=>'2']) 键名相同的不加 # 锚点方法, 可以传多个 [key=>value1],[key=>value2]
     * @example where("core_user_department_id=1") 纯原生
     * @example where("?id") = where("id"，"?") 配合 params("id",1)使用
     * @example where("?id,name") 配合 params(["id",1,"name"="abc"])使用
     * @example
     * ###操作符
     * - <b>=</b>                     等于,可以省略
     * - <b><></b>                    不等于
     * - <b>!</b>                     取非,'field'{!}'=>null 样当于 field IS NOT NULL
     * - <b>...</b>                   区间(三个点) 'field{...}'=>[20,50] 相当于 field BETWEEN 20 AND 50
     * - <b>findset</b>               'field{findset}'=>'b',相当于 FIND_IN_SET('b',field)
     * - <b>%</b>                     全模糊
     * - <b>!%</b>                    不包含
     * - <b>=%</b>                    前匹配后模糊
     * - <b>%=</b>                    后匹配前模糊
     * - @                            对value不做任何处理 'filed1{@}'=>'field2' 会生成 filed1=field2, field2不会变成字符串
     * - <b>></b>             
     * - <b>>=</b>             
     * - <b><</b>            
     * - <b><=</b>             
     * - <b>exists</b>                存在 where('{exists}','select 1 from t where t_id=my_id')           
     * - <b>!exists</b>               不存在 where('{!exists}','select 1 from t where t_id=my_id')           
     *  
     * ###其他
     * - '@'=>'exists(select 1 from t where id=p.id)' 键为@表示右侧为原生SQL
     * - 'field'=>'@xxx' 第一个字符为@表示右侧为原生SQL
     * - 'field'=>'' 查空,相当于 field='' AND field IS NULL
     * - 'field'=>[1,2,3] 相当于 field IN (1,2,3)
     * - 'field{!}'=>[1,2,3] 相当于 field NOT IN (1,2,3)
     *
     * ###组合
     * - <b>AND</b> , <b>OR</b> ,支持嵌套
     *  <pre>
     *  where([
     *      'OR'=>[
     *          'id{!}'=>[2,3],
     *          'username'=>'test',
     *          'email{#}'=>'b',
     *          '@'=>'exists(select 1 from t where id=p.id)',
     *          'NOT AND'=>[
     *              'username'=>'test',
     *              'email{#}'=>'www',
     *          ],
     *          'AND#2'=>[
     *              'username'=>'test2',
     *              'email{#}'=>'www',
     *          ]
     *      ],
     *      'department_zh_name{%}'=>'部',
     *  ])
     *  </pre>
     *
     */
	public function where($var, $second='<<<@>>>'):self
    {
        if (is_null($var)) {
            $this->where = [];
            return $this;
        }
        if (is_array($second) && array_values($second) != $second) {
            //[key=>value1],[key=>value2] 非数字下标的多个keyValue数组
            $var = $this->changeSameKeyRef(func_get_args());
            $second = '<<<@>>>';
        }
        $res = $this->getWhere($var,$second,'AND');
        if($res){
            if(is_array($res)){
                foreach($res as $s){
                    $this->where[] = $s;
                }
            }else{
                $this->where[] = $res;
            }
        }
        return $this;
    }
    /**
     * where()方法的别名 --
     * where条件组装
     * 
     */
    public function and($var, $second = '<<<@>>>'): self
    {
        if (is_array($second) && array_values($second) != $second) {
            $var = $this->changeSameKeyRef(func_get_args());
            return $this->where($var);
        } else {
            return $this->where($var, $second);
        }
    }

	public function whereOr($var, $second='<<<@>>>'):self
    {
        if (is_null($var)) {
            $this->where = [];
            return $this;
        }
        if (is_array($second) && array_values($second) != $second) {
            $var = $this->changeSameKeyRef(func_get_args());
            $second ='<<<@>>>';
        }
        $res = $this->getWhere($var,$second,'OR');
        if($res){
            if(is_array($res)){
                foreach($res as $s){
                    $this->where[] = $s;
                }
            }else{
                $this->where[] = $res;
            }
        }
        return $this;
    }
    /**
     * whereOr()方法的别名 --
     * whereOr条件组装
     * 
     */
    public function or($var, $second='<<<@>>>'):self
    {
        if (is_array($second) && array_values($second) != $second) {
            $var = $this->changeSameKeyRef(func_get_args());
            return $this->whereOr($var);
        }else{
            return $this->whereOr($var, $second);
        }
    }
    
    /**
     * 分组
     *
     * @param array|string $var
     * @example group('au_id')
     * @example group(['au_id','au_name'])
     * @example group('au_id,au_name') 纯原生
     * @return self
     */
	public function group($var):self
    {
        if (is_null($var)) {
            $this->groupBy = [];
            return $this;
        }
        if(is_string($var)){
            $var = explode(',',$var);
        }
        foreach($var as $k=>$v){
            $var[$k] = $this->getTrueField(trim($v));
        }
        $var = implode(',', $var);
        $var = trim($var);
        if($var) $this->groupBy[] = $var;
        return $this;
    }

    /**
     * having 参考 where
     *
     * @param array|string $var
     * @param mixed $second
     * @return self
     */
	public function having($var, $second='<<<@>>>'):self
    {
        if (is_null($var)) {
            $this->having = [];
            return $this;
        }
        if (is_array($second) && array_values($second) != $second) {
            $var = $this->changeSameKeyRef(func_get_args());
            $second = '<<<@>>>';
        }
        $res = $this->getWhere($var,$second,'AND');
        if($res){
            if(is_array($res)){
                foreach($res as $s){
                    $this->having[] = $s;
                }
            }else{
                $this->having[] = $res;
            }
        }
        return $this;
    }

	public function havingOr($var, $second='<<<@>>>'):self
    {
        if (is_null($var)) {
            $this->having = [];
            return $this;
        }
        if (is_array($second) && array_values($second) != $second) {
            $var = $this->changeSameKeyRef(func_get_args());
            $second = '<<<@>>>';
        }
        $res = $this->getWhere($var,$second,'OR');
        if($res){
            if(is_array($res)){
                foreach($res as $s){
                    $this->having[] = $s;
                }
            }else{
                $this->having[] = $res;
            }
        }
        return $this;
    }
    
    /**
     * 排序
     *
     * @param array|string $var
     * @param array|string $type ASC 或 DESC,或者 [0,1,null]
     * @example order('au_id')
     * @example order('au_id','DESC')
     * @example order(['au_id','au_name'])
     * @example order(['au_id','au_name'=>'DESC'])
     * @example order('au_id',[0,1,null]) 按值的顺序排序
     * @example order('field1 DESC,field2')
     * @return self
     */
	public function order($var,$type=''):self
    {
        if (is_null($var)) {
            $this->orderBy = [];
            return $this;
        }
        if($type!='' && is_string($type)) $type = strtoupper($type);
        if(is_string($var) && $type!=''){
            $var = [$var=>$type];
        }elseif(is_string($var) && $type==''){
            $aTemp = explode(',',$var);
            $arr = [];
            foreach($aTemp as $k=>$v){
                $v = trim($v);
                $aTemp2 = explode(' ',$v);
                if(count($aTemp2)==2){
                    $arr[$aTemp2[0]] = $aTemp2[1];
                }else{
                    $arr[] = $aTemp2[0];
                }
            }
            $var = $arr;
        }
        if(is_array($var)){
            $s = '';
            foreach($var as $k=>$v){
                if($s!='') $s .= ',';
                if(is_numeric($k)){
                    $s .= $this->getTrueField($v);
                    if($type!='') $s .= ' '.$type;
                }else{
                    if(is_string($v)){
                        $s .= $this->getTrueField($k).' '.strtoupper($v);
                    }else{
                        foreach($v as $iv=>$vv){
                            $v[$iv] = $this->getTypeValue($vv);
                        }
                        $v = implode(',',$v);
                        $s .= 'FIELD('.$this->getTrueField($k).', '.$v.')';
                    }
                }
            }
            $var = $s;
        }
        if($var) $this->orderBy[] = $var;
        return $this;
    }
    
    /**
     * 指定结果起始位置与最大多少行
     *
     * @param integer $offset 起始位置,如果只传入一个参数则offset为取多少行
     * @param integer $pagesize 取多少行
     * @return self
     */
	public function limit(int $offset, int $pagesize=-1):self
    {
        if($pagesize==-1){
            $pagesize = $offset;
            $offset = 0;
        }
        $this->limit = $pagesize;
        $this->offset = $offset;
        return $this;
    }

    /**
     * 分页
     *
     * @param int $page 当前页码, 从1开始
     * @param int $pagesize 一页多少条记录
     * @param int $records 结果记录数，如果不传入，将自动计算
     * @return self
     */
    public function page(int $page, int $pagesize=null, int $records = null):self
    {
        if (is_null($page)) {
            $this->clearPages();
            return $this;
        }
        if (is_null($pagesize)) {
            $pagesize = $this->pagesize;
        }
        if(empty($pagesize)){
            throw new \Exception('pagesize 未定义');
        }
        if (is_null($records)) {
            $records = $this->records ?: $this->count();
            $this->records = $records;
        }
        $this->page = $page;
        $this->pagesize = $pagesize;
        $this->total = ceil($records / $pagesize); //总页码数
        $this->offset = ($page - 1) * $pagesize;
        $this->limit($this->offset, $pagesize);
        return $this;
    }

    /**
     * 设置一页多少条记录
     *
     * @param int $pagesize 一页多少条记录
     * @return self
     */
    public function pagesize(int $pagesize):self
    {
        $this->pagesize = $pagesize;
        return $this;
    }
     
    /**
     * 绑定参数值 -- 
     * where('name','?1') 那么 params('1','hzq') 
     * where('name','?name') 那么 params('name','hzq') 
     *
     * @param string|array $name
     * @param mixed $value
     * @return self
     */
	public function params($name,$value='<<<none>>>'):self
    {
        if (is_null($name)) {
            $this->params = [];
            return $this;
        }
        if(is_array($name)){
            $this->params = array_merge($this->params, $name);
        }else if($value!='<<<none>>>'){
            $this->params[$name] = $value;
        }else{
            throw new \Exception("构建SQL失败,缺失参数的值");
        }
        return $this;
    }   

    /**
     * 设置字段值, 用于插入或更新数据
     *
     * @param array|string $fields
     * @param mixed $value
     * @return self
     * @example set($field,$value)
     * @example set([$field1=>$value1,$field2=>$value2])
     */
    public function set($fields,$value=null):self
    {
        if (is_null($fields)) {
            $this->sets = [];
            return $this;
        }
        if(is_string($fields)){
            $fields = [$fields=>$value];
        }
        foreach($fields as $k=>$v){
            $this->sets[] = $k .'='. $this->getTypeValue($v);
        }
        return $this;
    }

    /**
     * 用于批量插入或更新的数据
     *
     * @param array $data 一维或者二维数组
     * @return self
     * @example push([$field1=>$value1,$field2=>$value2])
     * @example push([[$field1=>$value1,$field2=>$value2],......])
     */
    public function push(array $data):self
    {
        if (is_null($data)) {
            $this->lots = [];
            return $this;
        }
        if(is_array(reset($data))){
            $this->lots = $data + $this->lots;
        }else{
            $this->lots[] = $data;
        }
        return $this;
    }

    /**
     * 重构返回结果数组
     *
     * @param array|string $keyField 指定哪个/组字段做为数组的键
     * @param array|string $valueField 指定哪个/组字段做为数组的值 null 表示全部字段
     * @param Closure $func 回调数据处理,返回 false 则忽略, func($key,$value,$allkey,$row), 级联键 $allkey 为 一级.二级.三级
     * @return self
     * @example map('id')
     * @example map('id','name')
     * @example map('@','name') 不改变数组下标
     * @example map('id',['name','email'])
     * @example map(['id1','id2'],['name','email']) 多个字段做键时用下划线连接 id1_id2
     * @example map('username',['email','depa','items'=>['ck1','ck2']]) 支持嵌套
     */
    public function map($keyField, $valueField = null, \Closure $func = null): self
    {
        if (is_null($keyField)) {
            $this->mapFetchKeyValue = null;
            return $this;
        }
        $this->mapFetchKeyValue = [$keyField, $valueField, $func];
        return $this;
    }

    /**
     * 返回指定字段的一维数组值
     *
     * @param astring $field 指定字段做为一维数组的值
     * @return self
     */
    public function column($field): self
    {
        if (is_null($field)) {
            $this->columnField = null;
            return $this;
        }
        $this->columnField = $field;
        return $this;
    }

    

    /**
     * 是否存在查询条件
     */
    public function hasWhere():bool
    {
        return !empty($this->where);
    }

    /**
     * 是否存在批量处理的数据
     */
    public function hasLots():bool
    {
        return !empty($this->lots);
    }

}