<?php
header('Content-type:text/html;charset=utf-8');
class Db{
    protected $last_sql;
}
class Init extends Db{
    private $db_type; //数据库类型
    private $dsn;    //dsn
    private $host;   //服务器名
    private $port;  //端口号
    private $user;  //账号
    private $password;  //密码
    private $db_name;   //数据库名
    private $charset;   //字符集
    private $prefix;    //表前缀
    private $link;  //连接资源
    //定义参数错误
    public $para_error = [];
    //表达式
    protected $exp = [];
    //表名(不含前缀)
    protected $name = '';
    //表名(含前缀)
    protected $table = '';
    //操作的数据表
    protected $tables = '';
    //查询参数
    protected $options = [];
    //查询模版
    protected $select_model = '';
    //删除模板
    protected $delete_model = '';
    //增加模板
    protected $insert_model = '';
    //修改模板
    protected $update_model = '';


    /**
     * 构造函数
     * 自动获取数据库连接
     * init constructor.
     * @param array $arr
     */
    public function __construct($arr = array()){
        //信息初始化
        $this->db_type = isset($arr['db_type']) ? $arr['db_type'] : 'mysql';
        $this->host = isset($arr['host']) ? $arr['host'] : '127.0.0.1';
        $this->port = isset($arr['port']) ? $arr['port'] : '3306';
        $this->user = isset($arr['user']) ? $arr['user'] : 'root';
        $this->password = isset($arr['password']) ? $arr['password'] : 'root';
        $this->db_name = isset($arr['db_name']) ? $arr['db_name'] : 'test';
        $this->charset = isset($arr['charset']) ? $arr['charset'] : 'utf8';
        $this->prefix = isset($arr['prefix']) ? $arr['prefix'] : '';
        $this->dsn = $this->db_type .':host='. $this->host .';port='.$this->port.';dbname='. $this->db_name;

        try{
            //连接数据库
            $this->link = new PDO($this->dsn, $this->user, $this->password);
            //设置字符集
            $this -> setCharset();
        }catch (PDOException $e){
            $this->para_error[1000] = '数据库连接失败:' . $e->getMessage();
        }

        //查询表达式
        $this->exp();
        //查询语句
        $this->selectSQL();
        //增加语句
        $this->insertSQL();
        //修改语句
        $this->updateSQL();
        //删除语句
        $this->deleteSQL();
    }

    /**
     * 设置字符
     * @return $this
     */
    public function setCharset(){
        $this->exec("SET NAMES {$this->charset}");
        return $this;
    }

    /**
     * 执行语句
     * @param $sql
     * @return PDOStatement
     */
    public function query($sql){
        $rst = $this->link->query($sql);
        return $rst;
    }

    /**
     * 执行语句
     * @param $sql
     * @return int
     */
    public function exec($sql){
        $rst = $this->link->exec($sql);
        return $rst;
    }

    /**
     * 表名
     * @param $name
     * @return $this
     */
    public function name($name){
        $this->name = $this->prefix.$name;
        $this->tables = $this->name;
        return $this;
    }

    /**
     * 设置表名
     * @param $table
     * @return $this
     */
    public function table($table){
        $this->table = $table;
        $this->tables = $this->table;
        return $this;
    }

    /**
     * 查询字段
     * @param $field
     * @return $this
     */
    public function field($field){
        if(gettype($field) == 'array'){
            $this->para_error[1012] = '查询字段格式有误!';
        }
        $this->options['FIELD'] = $field;
        return $this;
    }

    /**
     * 联表查询 默认left
     * @param $table
     * @param $on
     * @param string $type
     * @return $this
     */
    public function join($table,$on,$type = 'left'){
        $this->options['JOIN'][] = ' '.strtoupper($type) . ' JOIN ' .$table .' ON ' .$on;
        return $this;
    }

    /**
     * 分组
     * @param $group
     * @return $this
     */
    public function group($group){
        if(gettype($group) == 'array'){
            $this->para_error[1012] = '查询字段格式有误!';
        }
        $this->options['GROUP'] = 'GROUP BY ' . $group;
        return $this;
    }

    /**
     * 查询限制
     * @param $limit
     * @return $this
     */
    public function limit($limit){
        if(gettype($limit) == 'array'){
            $this->para_error[1012] = '查询字段格式有误!';
        }
        $num = explode(',',$limit);
        if(isset($num[1]) && $num[1]){
            $this->options['LIMIT'] = 'LIMIT ' . $limit;
        }else{
            $this->options['LIMIT'] = 'LIMIT ' . $num[0];
        }
        return $this;
    }

    /**
     * 排序
     * @param $order
     * @return $this
     */
    public function order($order){
        if(gettype($order) == 'array'){
            $this->para_error[1012] = '查询字段格式有误!';
        }
        $this->options['ORDER'] = 'ORDER BY ' . $order;
        return $this;
    }

    /**
     * 插入语句
     * @param $data
     * @return $this|array|int|null
     */
    public function insert($data){
        //判断是否为一维数组
        if($this->array_depth($data) != 1){
            $this->para_error[1010] = '传入数据类型必须为一维数组';
            return $this;
        }
        $result = null;
        //判断查询表名是否存在
        if(!$this->tables){
            $this->para_error[1001] = '数据表未选择!';
        }else{
            //判断是否错误
            if($this->para_error){
                $result = $this->para_error;
                return $result;
            }
            $pre= array('k'=>'`');
            $suf = array('k'=>'`,','v'=>',');
            $font = array('k'=>'(','v'=>'(');
            $end = array('k'=>')','v'=>')');
            $arr = $this->dimeReduction($data,$pre,$suf,$font,$end);
            $field = $arr['key'];
            $value = $arr['value'];
            //拼接插入语句
            $sql = $this->repSql(1,$field,$value);
            $this->last_sql = $sql;
            //执行结果
            $result = $this->exec($sql);
        }
        return $result;
    }

    /**
     * 插入所有数据
     * @param $data
     * @return array|int
     */
    public function insertAll($data){
        if($this->array_depth($data) < 2){
            $this->para_error[1011] = '数据有误!';
        }

        if($this->para_error){
            return $this->para_error;
        }
        $pre = array('k'=>'`',);
        $suf = array('k'=>'`,','v'=>',','m'=>',');
        $font = array('k'=>'(','v'=>'(','m'=>'(');
        $end = array('k'=>')','v'=>')','m'=>')');
        $field = $this->dimeReduction($data,$pre,$suf,$font,$end);

        $fields = $field[0]['key'];
        $value = null;
        foreach ($field as $k=>$v){
            $value .= $v['value'] .',';
        }
        $value = substr($value,0,-1);
        //获取插入数据库值
        //sql拼接语句
        $sql = $this->repSql(1,$fields,$value);
        $this->last_sql = $sql;
        //执行结果
        $result = $this->exec($sql);
        return $result;
    }

    /**
     * 删除
     * @return array|int
     */
    public function delete(){
        if(!$this->tables){
            $this->para_error[1001] = '数据表未选择!';
        }
        if(!isset($this->options['WHERE'])){
            $this->para_error[1015] = '请输入查询条件!';
        }
        //如果有错误则返回错误
        if($this->para_error){
            return $this->para_error;
        }
        $this->last_sql = $this->repSql(2);
        $result = $this->exec($this->last_sql);
        return $result;
    }

    /**
     * 修改
     * @param $data
     * @return array|int
     */
    public function update($data){
        if(!$this->tables){
            $this->para_error[1001] = '数据表需选择';
        }
        if($this->para_error){
            return $this->para_error;
        }

        $suf = array('m'=>',');
        $arr = $this->dimeReduction($data,'',$suf);
        $insert_data = $arr['mixture'];
        $this->last_sql = $this->repSql(3,'',$insert_data);
        $result = $this->exec($this->last_sql);
        return $result;
    }

    /**
     * 查询
     * @return array|string
     */
    public function select(){
        isset($this->options['FIELD']) ? $field = $this->options['FIELD'] : $field = '*';
        $this->last_sql = $this->repSql(0,$field);
        $result = $this->query($this->last_sql);
        $temp = '';
        while ($obj = $result->fetch(PDO::FETCH_ASSOC)) {
           $temp[] = $obj;
        }
        return $temp;
    }

    /**
     * 获取一条字段
     * @return mixed|string
     */
    public function find(){
        isset($this->options['FIELD']) ? $field = $this->options['FIELD'] : $field = '*';
        $this->options['LIMIT'] = ' LIMIT 1';
        $this->last_sql = $this->repSql(0,$field);
        $result = $this->query($this->last_sql);
        $temp = '';
        while ($obj = $result->fetch(PDO::FETCH_ASSOC)) {
            $temp = $obj;
        }
        return $temp;
    }

    /**
     * 条件语句
     * @param $condition
     * @return $this|array
     */
    public function where($condition){
        if($condition){
            $where = null;
            foreach ($condition as $k => $v){
                if($this->array_depth($v) <= 2 && $this->array_depth($v) >= 1){
                    $str = $this->disposeWhere($k,$v[0],$v[1]);
                    $where .= " AND {$str}";
                }else if( count($v) == 1){
                    $where .= " AND {$k}={$v}";
                }else{
                    $this->para_error[1009] = '条件格式有误!';
                }
            }
            if($this->para_error){
                return $this->para_error;
            }
            $this->options['WHERE'] = substr($where,4);
            return $this;
        }
    }

    /**
     * 最近一条执行语句
     * @return mixed
     */
    public function getLastSql(){
        if(!$this->last_sql){
            return '未执行SQL语句!';
        }
        return $this->last_sql;
    }

    /**
     * 获取上一条id记录
     * @param null $sequence 自增id
     * @return string
     */
    public function getLastInsId($sequence = null){
        return $this->link->lastInsertId($sequence);
    }

    /**
     * 防注入过滤
     * @param $value
     * @return string
     */
    private function quote($value){
        return $this->link->quote($value);
    }

    /**
     * 查询模板
     * @return $this
     */
    private function selectSQL(){
        $this->select_model = "SELECT%FIELD%FROM%TABLE%JOIN%WHERE%GROUP%ORDER%LIMIT";
        return $this;
    }

    /**
     * 删除语句
     * @return $this
     */
    private function deleteSQL(){
        $this->delete_model = "DELETE%FROM%TABLE%WHERE";
        return $this;
    }

    /**
     * 增加语句
     * @return $this
     */
    private function insertSQL(){
        $this->insert_model = "INSERT%INTO%TABLE%FIELD%VALUES";
        return $this;
    }

    /**
     * 更新语句
     * @return $this
     */
    private function updateSQL(){
        $this->update_model = "UPDATE%TABLE%SET%VALUES%WHERE";
        return $this;
    }

    /**
     * 表达式
     * @return $this
     */
    public function exp(){
        $this->exp = array(
            "eq" => "=",
            "neq" => "<>",
            "gt" => ">",
            "egt" => ">=",
            "lt" => "<",
            "elt" => "<=",
            "notlike" => "NOT LIKE",
            "not like" => "NOT LIKE",
            "like" => "LIKE",
            "in" => "IN",
//            "exp" => "EXP",
            "notin" => "NOT IN",
            "not in" => "NOT IN",
            "between" => "BETWEEN",
            "not between" => "NOT BETWEEN",
            "notbetween" => "NOT BETWEEN",
//            "exists" => "EXISTS",
//            "notexists" => "NOT EXISTS",
//            "not exists" => "NOT EXISTS",
//            "null" => "NULL",
//            "notnull" => "NOT NULL",
//            "not null" => "NOT NULL"
//            "> time" => "> TIME",
//            ">= time" => ">= TIME",
//            "< time" => "< TIME",
//            "<= time" => "<= TIME",
//            "between time" => "BETWEEN TIME",
//            "notbetween time" => "NOT BETWEEN TIME",
//            "not between time" => "NOT BETWEEN TIME"
        );
        return $this;
    }

    /**
     * 处理条件语句
     * @param $key
     * @param $expre
     * @param $val
     * @return string
     */
    private function disposeWhere($key,$expre,$val){
        if (!array_key_exists($expre,$this->exp)){
            $this->para_error[1008] = '条件表达式不存在!';
        }
        $str = '';
        if(gettype($val) == 'array'){
            foreach ($val as $item){
                $str .= $item.',';
            }
            $str = substr($str,0,-1);
        }else{
            $str = $val;
        }
        $temp = '';
        switch ($expre){
            case "eq" :
            case "neq" :
            case "gt" :
            case "egt" :
            case "lt" :
            case "elt" :
            case "notlike" :
            case "not like" :
            case "like" :
                $temp = $key.' '.$this->exp[$expre].' '.$str;
                break;
            case "in" :
            case "notin" :
            case "not in" :
                $temp = $key.' '.$this->exp[$expre] .' ('. $str.')';
                break;
            case "between" :
            case "not between" :
            case "notbetween" :
                $arr = explode(',',$str);
                if($arr[0] > $arr[1]){
                    $min = $arr[1];
                    $max = $arr[0];
                }else{
                    $min = $arr[0];
                    $max = $arr[1];
                }
                $temp = $key . ' ' . $this->exp[$expre] .' '. $min .' AND '.$max;
                break;
        }
        return $temp;
    }


    /**
     * 数组降维 一维降字符串,二维降一维
     * @param $arr   array 降维数组
     * @param $pre   array 拼接前缀
     * @param $suf   array 拼接后缀
     * @param $font  array 结束拼接前缀
     * @param $end   array 结束拼接后缀
     * @return array
     */
    private function dimeReduction($arr,$pre = null,$suf = null,$font = null,$end = null){
        $rst = array();
        isset($pre['k']) ? $k_pre = $pre['k'] : $k_pre = '';
        isset($pre['v']) ? $v_pre = $pre['v'] : $v_pre = '';
        isset($pre['m']) ? $m_pre = $pre['m'] : $m_pre = '';
        isset($suf['k']) ? $k_suf = $suf['k'] : $k_suf = '';
        isset($suf['v']) ? $v_suf = $suf['v'] : $v_suf = '';
        isset($suf['m']) ? $m_suf = $suf['m'] : $m_suf = '';
        isset($font['k']) ? $k_font = $font['k'] : $k_font = '';
        isset($font['v']) ? $v_font = $font['v'] : $v_font = '';
        isset($font['m']) ? $m_font = $font['m'] : $m_font = '';
        isset($end['k']) ? $k_end = $end['k'] : $k_end = '';
        isset($end['v']) ? $v_end = $end['v'] : $v_end = '';
        isset($end['m']) ? $m_end = $end['m'] : $m_end = '';

        if($this->array_depth($arr) == 1){
            $rst['key'] = '';
            $rst['value'] = '';
            $rst['mixture'] = '';
            foreach ($arr as $k => $v){
                $rst['key'] .= $k_pre.$k.$k_suf;
                $rst['value'] .= $v_pre.$this->quote($v).$v_suf;
                $rst['mixture'] .= $m_pre.$k.'='. $this->quote($v).$m_suf;
            }
            $rst['key'] = $k_font.substr($rst['key'],0,-1).$k_end;
            $rst['value'] = $v_font.substr($rst['value'],0,-1).$v_end;
            $rst['mixture'] = $m_font.substr($rst['mixture'],0,-1).$m_end;
        }
        if($this->array_depth($arr) == 2){

            foreach ($arr as $k => $v){
                $rst[$k]['key'] = '';
                $rst[$k]['value'] = '';
                $rst[$k]['mixture'] = '';
                foreach ($v as $key => $value){
                    $rst[$k]['key'] .= $k_pre.$key.$k_suf;
                    $rst[$k]['value'] .= $v_pre. $this->quote($value).$v_suf;
                    $rst[$k]['mixture'] .= $m_pre.'`'.$key.'`='. $this->quote($value).$m_suf;
                }
                $rst[$k]['key'] = $k_font.substr($rst[$k]['key'],0,-1).$k_end;
                $rst[$k]['value'] = $v_font.substr($rst[$k]['value'],0,-1).$v_end;
                $rst[$k]['mixture'] = $m_font.substr($rst[$k]['mixture'],0,-1).$m_end;
            }
        }
        return $rst;
    }

    /**
     * @param string 类型  1:增加  2: 删除 3:修改 默认查询
     * @param string $field
     * @param null $where
     * @param null $data
     * @return mixed|null
     */
    private function repSql($type,$field = '*',$data = null){
        isset($this->options['WHERE']) ? $where = 'WHERE '. $this->options['WHERE'] : $where = '';
        $join = '';
        if(isset($this->options['JOIN'])  && $this->options['JOIN']){
            foreach ($this->options['JOIN'] as $v){
                $join .= $v;
            }
        }
        isset($this->options['GROUP']) ? $group = $this->options['GROUP'] : $group = '';
        isset($this->options['LIMIT']) ? $limit = $this->options['LIMIT'] : $limit = '';
        isset($this->options['ORDER']) ? $order = $this->options['ORDER'] : $order = '';
        $sql = null;
        switch ($type){
            case '1' :
                $sql = str_replace(array('%','TABLE','FIELD','VALUES'),array(' ',$this->tables," {$field} ","VALUES {$data}"),$this->insert_model);
                break;
            case '2' :
                $sql = str_replace(array('%','TABLE','WHERE'),array(' ',$this->tables,$where),$this->delete_model);
                break;
            case '3' :
                $sql = str_replace(array('%','TABLE','VALUES','WHERE'),array(' ',$this->tables,$data,$where),$this->update_model);
                break;
            default :
                $sql = str_replace(array('%','TABLE','FIELD','JOIN','WHERE','GROUP','LIMIT','ORDER'),array(' ',$this->tables,$field,$join,$where,$group,$limit,$order),$this->select_model);
                break;

        }
        return $sql;
    }

    /**
     * 判断数组维度
     * @param $array
     * @return int
     */
    private function array_depth($array) {
        //判断是否为数组，若不是返回0
        if(!is_array($array))  return 0;
        //初始化为一维数组
        $max_depth = 1;
        //遍历判断数组维度
        foreach ($array as $value) {
            if (is_array($value)) {
                $depth = $this->array_depth($value) + 1;
                if ($depth > $max_depth) {
                    $max_depth = $depth;
                }
            }
        }
        return $max_depth;
    }
}
?>