\tickymodel

<?php

namespace ticky;

/*
  $orm = new model();
  //查询语句
  $res = $orm->table('user')->order('id desc')->select();
  $res = $orm->table('user')->where("name='test'")->order('id desc')->select();
  $res = $orm->table('user')->where(['id' => 1])->order('id desc')->find();
  $res = $orm->table('user')->where("age > 20")->group('group by name')->order('id desc')->limit(2)->select();
  $res = $orm->table('user')->where("age > 20")->group('group by name')->order('id desc')->limit('2,2')->select();
  $res = $orm->table('user')->add( $data );
  //更新数据
  $res = $orm->table('user')->where(['id' => 4])->update( ['name' => 'sdfdsfdsd', 'salt' => '111'] );
  $res = $orm->table('user')->where(['id' => 4])->update( ['name' => 'sdfdsfdsd', 'salt' => 'salt+1'] );
  //删除数据
  $res = $orm->table('user')->where(['id' => 7, 'id' => 6])->delete();
  //获取执行的sql语句
  echo $orm->getLastSql();
  var_dump($res);
 */

use PDO;
use ticky\debug;

class model {

    public $config;
    public static $sqldebug = array();
    public $prefix;
    public $sql;    //存储最后一条sql
    private $driver;
    private $conn = null;    //数据库链接资源
    private $alias = [];  //记录全局的语句参数
    private $starttime;
    private $querynum = 0;
    // 链操作方法列表
    protected $methods = array('strict', 'order', 'alias', 'having', 'group', 'lock', 'distinct', 'auto', 'filter', 'validate', 'result', 'token', 'index', 'force', 'limit');

    public function __construct($table = '') {
        $this->starttime = microtime(true);
        $config = config::get('db');
        $this->driver = $config['driver'];
        $this->config = $config[$this->driver];
        $this->prefix = $this->config['prefix'];
        if (isset($table)) {
            $this->table($table);
        }
        if (is_null($this->conn)) {
            try {
                $this->conn = new PDO($this->dsn(), $this->config['user'], $this->config['password']);
                $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $this->conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); //使用缓冲查询，仅mysql有效
                $this->conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); //启用预处理语句的模拟
                $this->conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL); //强制列名小写
                $this->conn->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL); //指定数据库返回的NULL值在php中对应的数值
                $this->conn->setAttribute(PDO::ATTR_AUTOCOMMIT, 1); //开启自动提交
                //设置字符集
                $this->conn->exec('SET NAMES ' . $this->config['charset']);
            } catch (PDOException $e) {
                $this->conn = null;
                app::halt('Connection failed: ' . $e->getMessage());
            }
        }
    }

    /**
     * 获取DSN
     * @return string
     * @throws exception
     */
    private function dsn() {
        $host = $this->config['host'];
        $port = $this->config['port'];
        $name = $this->config['name'];
        if (!$host || !$port || !$name) {
            throw new exception("Required DSN parameter is missing");
        }
        return "mysql:host={$host};port={$port};dbname={$name}";
    }

    public function getTable() {
        return $this->alias['table'];
    }

    public function count() {
        $this->starttime = microtime(true);
        $this->alias['field'] = " count(*) as count ";
        $this->ParseSelectSql();
        $row = $this->conn->query($this->sql)->fetchAll(PDO::FETCH_ASSOC);
        $result = [];
        foreach ($row as $key => $vo) {
            $result[$key] = $vo;
        }
        $this->printSql();
        $this->clearsql();
        return $result;
    }

    //field语句
    public function field($field) {
        if (!is_string($field)) {
            throw new exception("field语句的参数必须为字符串");
        }
        $this->alias['field'] = $field;
        return $this;
    }

    //table语句
    public function table($table) {
        if (!is_string($table)) {
            throw new exception("table语句的参数必须为字符串");
        }
        if (strpos($table, $this->prefix) !== false) {
            $this->alias['table'] = $table;
        } else {
            $this->alias['table'] = $this->prefix . $table;
        }
        return $this;
    }

    //where语句
    public function where($where) {
        //debug($where);
        $this->alias['where'] = '';
        if (is_array($where)) {
            foreach ($where as $key => $vo) {
                $this->alias['where'] .= " $key" . ' = "' . $vo . '" and ';
            }
            $this->alias['where'] = rtrim($this->alias['where'], 'and ');
        } else if (is_string($where)) {
            $this->alias['where'] = $where;
        } else {
            throw new exception("where语句的参数必须为数组或字符串");
        }
        return $this;
    }

    //limit语句
    public function limit($limit) {
        $this->alias['limit'] = '';
        if (is_numeric($limit)) {
            $this->alias['limit'] = '0,' . $limit;
        } else if (is_string($limit)) {
            $this->alias['limit'] = $limit;
        } else {
            throw new exception("limit语句的参数必须为数字或字符串");
        }
        return $this;
    }

    //order语句
    public function order($order) {
        if (!is_string($order)) {
            throw new exception("order语句的参数必须为字符串");
        }
        $this->alias['order'] = $order;
        return $this;
    }

    //group语句
    public function group($group) {
        if (!is_string($group)) {
            throw new exception("group语句的参数必须为字符串");
        }
        $this->alias['group'] = $group;
        return $this;
    }

    /**
     * 查询SQL组装 join
     * @access public
     * @param mixed $join
     * @param string $type JOIN类型
     * @return Model
     */
    public function join($join, $type = 'INNER') {
        $prefix = $this->prefix;
        $this->alias['join'] = '';
        if (is_array($join)) {
            foreach ($join as $key => &$_join) {
                $_join = preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function($match) use($prefix) {
                    return $prefix . strtolower($match[1]);
                }, $_join);
                $_join = false !== stripos($_join, 'JOIN') ? $_join : $type . ' JOIN ' . $_join;
            }
            $this->alias['join'] = $join;
        } elseif (!empty($join)) {
            //将__TABLE_NAME__字符串替换成带前缀的表名
            $join = preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function($match) use($prefix) {
                return $prefix . strtolower($match[1]);
            }, $join);
            $this->alias['join'][] = false !== stripos($join, 'JOIN') ? $join : $type . ' JOIN ' . $join;
        }
        return $this;
    }

    //解析查询sql语句
    private function ParseSelectSql() {
        $this->sql = 'select *';
        if (!empty($this->alias['field'])) {
            $this->sql = str_replace('*', $this->alias['field'], $this->sql);
        }
        if (empty($this->alias['table'])) {
            throw new exception("请用table子句设置查询表");
        } else {
            $this->sql .= ' from ' . $this->alias['table'];
        }
        if (!empty($this->alias['join'])) {
            $prefix = $this->prefix;
            if (is_array($this->alias['join'])) {
                $join = $this->alias['join'];
                foreach ($join as $key => &$_join) {
                    $_join = preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function($match) use($prefix) {
                        return $prefix . strtolower($match[1]);
                    }, $_join);
                    $_join = false !== stripos($_join, 'JOIN') ? $_join : $type . ' JOIN ' . $_join;
                }
                $this->sql .= '  ' . $_join;
            } else if ($this->alias['join']) {
                $this->sql .= '  ' . $this->alias['join'];
            }
        }
        if (!empty($this->alias['where'])) {
            $this->sql .= ' where ' . $this->alias['where'];
        }
        if (!empty($this->alias['group'])) {
            $this->sql .= ' group by ' . $this->alias['group'];
        }
        if (!empty($this->alias['order'])) {
            $this->sql .= ' order by ' . $this->alias['order'];
        }
        if (!empty($this->alias['limit'])) {
            $this->sql .= ' limit ' . $this->alias['limit'];
        }
        return $this->sql;
    }

    //解析添加sql语句
    private function ParseAddSql() {
        $this->sql = 'insert into ';
        if (empty($this->alias['table'])) {
            throw new exception("请用table子句设置添加表");
        } else {
            $this->sql .= $this->alias['table'] . ' set ';
        }
        return $this->sql;
    }

    //解析更新sql语句
    private function ParseUpdateSql() {
        $this->sql = 'update ';
        if (empty($this->alias['table'])) {
            throw new exception("请用table子句设置修改表");
        } else {
            $this->sql .= $this->alias['table'];
        }
        if (!empty($this->alias['where'])) {
            $this->sql .= ' set ';
        }
        return $this->sql;
    }

    //解析删除sql语句
    private function ParseDeleteSql() {
        $this->sql = 'delete from ';
        if (empty($this->alias['table'])) {
            throw new exception("请用table子句设置删除表");
        } else {
            $this->sql .= $this->alias['table'];
        }
        if (empty($this->alias['where'])) {
            throw new exception("删除语句必须有where子句指定条件");
        }
        $this->sql .= ' where ' . $this->alias['where'];
        return $this->sql;
    }

    //查询语句
    public function select() {
        $this->starttime = microtime(true);
        $this->ParseSelectSql();
        $this->printSql($this->sql);
        $row = $this->conn->query($this->sql)->fetchAll(PDO::FETCH_ASSOC);
        $result = [];
        foreach ($row as $key => $vo) {
            $result[$key] = $vo;
        }

        $this->clearsql();
        return $result;
    }

    //查询一条
    public function find() {
        $this->starttime = microtime(true);
        $this->ParseSelectSql();
        $this->printSql();
        $row = $this->conn->query($this->sql)->fetch(PDO::FETCH_ASSOC);
        $this->clearsql();
        return $row;
    }

    //添加数据
    public function add($data) {
        $this->starttime = microtime(true);
        if (!is_array($data)) {
            throw new exception("添加数据add方法参数必须为数组");
        }
        $this->ParseAddSql();
        foreach ($data as $key => $vo) {
            $this->sql .= " `{$key}` = '" . $this->safe_data($vo) . "',";
        }
        $this->printSql();
        try {
            $this->conn->exec(rtrim($this->sql, ','));
            $newId = $this->conn->lastInsertId();
            $this->clearsql();
            return $newId;
        } catch (Exception $e) {
            return false;
        }
    }

    //更新语句
    public function update($data) {
        $this->starttime = microtime(true);
        if (!is_array($data)) {
            $this->ParseUpdateSql();
            $this->sql .= $data;

            $this->sql = rtrim($this->sql, ',') . ' where ' . $this->alias['where'];
        } else {
            $this->ParseUpdateSql();
            foreach ($data as $key => $vo) {
                $this->sql .= " `{$key}` = '" . $this->safe_data($vo) . "',";
            }
            $this->sql = rtrim($this->sql, ',') . ' where ' . $this->alias['where'];
        }
        $this->printSql();
        $ret = $this->conn->exec($this->sql);
        $this->clearsql();
        return $ret;
    }

    //删除语句
    public function delete() {
        $this->starttime = microtime(true);
        $this->ParseDeleteSql();
        $ret = $this->conn->exec($this->sql);
        $this->printSql();
        $this->clearsql();
        return $ret;
    }

    public function page($table, $search, $order = '', $pageNum = 1, $pageSize = 10) {
        $this->starttime = microtime(true);
        $pageStr = (($pageNum - 1) * $pageSize) . "," . $pageSize;
        $orm = new model();
        $total = count($orm->table($table)->where($search)->select());
        $res = $orm->table($table)->where($search)->order($order)->limit($pageStr)->select();
        $page = new page($total, $pageSize);
        $this->printSql($this->sql);
        $this->clearsql();
        return [
            'items' => $res,
            'page' => $page,
        ];
    }

    public function newpage() {
        $field = $this->alias['field'];
        $total = $this->total();
        $page = new page($total, 10);
        $this->alias['field'] = $field;
        $res = $this->limit($page->limit())->select();
        return [
            'items' => $res,
            'page' => $page,
        ];
    }

    /**
     * 返回记录行数。
     * @return int
     */
    public function total() {
        $this->field('COUNT(*) AS total');
        $this->ParseSelectSql();
        $this->printSql($this->sql);
        $total = @$this->conn->query($this->sql)->fetchAll(PDO::FETCH_ASSOC);
        return $total[0]['total'];
    }

    /**
     * 内部方法：查询部分，开始组装SQL
     * 利用__call方法实现一些特殊的Model方法
     * @access public
     * @param string $method 方法名称
     * @param array $args 调用参数
     * @return mixed
     */
    public function __call($method, $args) {
        if (in_array(strtolower($method), $this->methods, true)) {
            // 连贯操作的实现
            $this->alias[strtolower($method)] = $args[0];
            return $this;
        }
    }

    public function getsql() {
        return $this->sql;
    }

    // 查询过后清空sql表达式组装 避免影响下次查询
    private function clearsql() {
        $this->alias = array();
        $this->sql = "";
    }

    /**
     * 内部方法：
     */
    private function printSql($sql = null) {
        if (APP_DEBUG) {
            if (empty($sql)) {
                $sql = $this->sql;
            }
            $this->querynum++;
            self::$sqldebug[] = $sql;
            debug::addmsg($sql, 1);
            runlog('sql', $sql);
        }
    }

    /**
     * 内部方法：过滤函数
     * @param $value
     * @param $chars
     * @return string
     */
    private function safe_data($value, $chars = false) {
        if (!MAGIC_QUOTES_GPC) {
            $value = addslashes($value);
        }
        if ($chars) {
            $value = htmlspecialchars($value);
        }
        return $value;
    }

    /**
     * 内部方法：过滤非表字段
     * @param $arr
     * @param $primary 是否过滤主键
     * @return array
     */
    private function filter_field($arr, $primary = true) {
        $fields = $this->get_fields();
        foreach ($arr as $k => $v) {
            if (!in_array($k, $fields)) {
                unset($arr[$k]);
            }
        }
        if ($primary) {
            $p = $this->get_primary();
            if (isset($arr[$p])) {
                unset($arr[$p]);
            }
        }
        return $arr;
    }

    /*     * ***Y*************PDO********************************************  */

    //sql查询
    public function query($sql) {
        $this->starttime = microtime(true);
        $this->printSql($sql);
        $row = @$this->conn->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $result = [];
        foreach ($row as $key => $vo) {
            $result[$key] = $vo;
        }
        $this->clearsql();
        return $result;
    }

    //执行sql
    public function exec($sql) {
        $this->starttime = microtime(true);
        $this->printSql($sql);
        $ret = $this->conn->exec($sql);
        $this->clearsql();
        return $ret;
    }

    /**
     * 返回 MySQL 服务器版本信息
     * @return string
     */
    public function version() {
        return @$this->conn->getAttribute(PDO::ATTR_SERVER_VERSION);
    }

    /**
     * 获取数据库 所有表
     * @return array
     */
    public function list_tables() {
        $tables = array();
        $listqeury = @$this->conn->query('SHOW TABLES');
        $data = $listqeury->fetchAll(PDO::FETCH_NUM);
        foreach ($data as $key => $value) {
            $tables[] = $value[0];
        }
        return $tables;
    }

    /**
     * 获取表字段
     * @param $table 		数据表 可选
     * @return array
     */
    public function get_fields($table = '') {
        $table = empty($table) ? $this->getTable() : $table;
        $fields = array();
        $sql = "SHOW COLUMNS FROM $table";
        $this->printSql($sql);
        $r = @$this->conn->query($sql);
        $data = $r->fetchAll(PDO::FETCH_ASSOC);
        foreach ($data as $key => $value) {
            $fields[] = $value['Field'];
        }
        return $fields;
    }

    /**
     * 检查表是否存在
     * @param $table 表名
     * @return boolean
     */
    public function table_exists($table) {
        $tables = $this->list_tables();
        return in_array($table, $tables);
    }

    /**
     * 检查字段是否存在
     * @param $table 表名
     * @param $field 字段名
     * @return boolean
     */
    public function field_exists($table, $field) {
        $fields = $this->get_fields($table);
        return in_array($field, $fields);
    }

    /**
     * 获取数据表主键
     * @param $table 		数据表 可选
     * @return array
     */
    public function get_primary($table = '') {
        $table = empty($table) ? $this->get_tablename() : $table;
        $sql = "SHOW COLUMNS FROM $table";
        $this->printSql($sql);
        if (empty($table))
            return false;
        $r = @$this->conn->query($sql);
        $data = $r->fetchAll(PDO::FETCH_ASSOC);
        foreach ($data as $key => $value) {
            if ($value['Key'] == 'PRI') {
                return $value['Field'];
            }
        }
        return $data[0]['Field'];
    }

    /**
     * 执行添加记录操作
     * @param $data         要增加的数据，参数为数组。数组key为字段值，数组值为数据取值
     * @param $filter       第二个参数选填 如果为真值[1为真] 则开启实体转义
     * @param $primary 		是否过滤主键
     * @return int/boolean  成功：返回自动增长的ID，失败：false
     */
    public function insert($data, $filter = false, $primary = true) {
        if (!is_array($data)) {
            return false;
        }
        $data = $this->filter_field($data, $primary);
        $fields = $values = array();
        foreach ($data AS $key => $val) {
            $fields[] = '`' . $key . '`';
            $values[] = "'" . $this->safe_data($val, $filter) . "'";
        }
        if (empty($fields)) {
            return false;
        }
        $sql = 'INSERT INTO ' . $this->getTable() . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
        $this->printSql($sql);
        $this->conn->exec($sql);
        return $this->conn->lastInsertId();
    }

}