<?php 

class Sql
{
    protected $_dbHandle;
    protected $_result;
    private $filter = '';
    protected $_table;
    private $field = '*';

    // 连接数据库
    public function connect($host, $user, $pass, $dbname)
    {
        try {
            $dsn = sprintf("mysql:host=%s;dbname=%s;charset=utf8", $host, $dbname);
            $option = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
            $this->_dbHandle = new PDO($dsn, $user, $pass, $option);
        } catch (PDOException $e) {
            exit('错误: ' . $e->getMessage());
        }
    }


    public function table($table='')
    {
        $this->_table = TABLE_PREFIX.'_'.$table;
        return $this;
    }

    // 查询条件
    public function where($where = array())
    {
        if (isset($where)) {
            $where = (array)$where;
            $this->filter = '';
            $this->field = '*';
            $this->filter .= ' WHERE ';
            $this->filter .= implode(' ', $where);
        }
        return $this;
    }

    /**
     * @param int $start
     * @param int $page_size
     */
    public function limit($start=0,$page_size=20)
    {
        $this->filter .= " limit {$start},{$page_size}";
        return $this;
    }

    // 排序条件
    public function order($order = array())
    {
        $order = (array)$order;
        if(isset($order)) {
            $this->filter .= ' ORDER BY ';
            $this->filter .= implode(',', $order);
        }

        return $this;
    }

    // 查询所有
    public function selectAll()
    {
        $sql = sprintf("select %s from `%s` %s",$this->field, $this->_table, $this->filter);
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }

    public function get_row_count(){	//返回总记录数
        $sql = sprintf("select count(*) as row_count from `%s` %s", $this->_table, $this->filter);
        if(f::get_session('manage_user')){
//            echo $sql."\r\n";
        }
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();
        $result = $sth->fetch();
        return $result['row_count'];
    }

    // 查询所有
    public function fetch_one()
    {
        $sql = sprintf("select %s from `%s` %s",$this->field, $this->_table, $this->filter);
//        echo $sql."<br />";
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();
        return $sth->fetch();
    }


    // 根据条件 (id) 查询
    public function select($id)
    {
        $sql = sprintf("select %s from `%s` where `id` = '%s'",$this->field, $this->_table, $id);
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();

        return $sth->fetch();
    }

    // 根据条件 (id) 删除
    public function delete()
    {
        $sql = sprintf("delete from `%s` %s", $this->_table, $this->filter);
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();

        return $sth->rowCount();
    }

    // 自定义SQL查询，返回影响的行数
    public function query($sql)
    {
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();

        return $sth->fetchAll();
    }

    // 新增数据
    public function add($data)
    {
        $sql = sprintf("insert into `%s` %s", $this->_table, $this->formatInsert($data));
//       echo $sql."<br />";
        return $this->query($sql);
    }

    // 修改数据
    public function update($data)
    {
        $sql = sprintf("update `%s` set %s %s", $this->_table, $this->formatUpdate($data), $this->filter);
        return $this->query($sql);
    }

    // 将数组转换成插入格式的sql语句
    private function formatInsert($data)
    {
        $fields = array();
        $values = array();
        foreach ($data as $key => $value) {
            $fields[] = sprintf("`%s`", $key);
            $values[] = sprintf("'%s'", $value);
        }

        $field = implode(',', $fields);
        $value = implode(',', $values);

        return sprintf("(%s) values (%s)", $field, $value);
    }

    // 将数组转换成更新格式的sql语句
    private function formatUpdate($data)
    {
        $fields = array();
        foreach ($data as $key => $value) {
            $fields[] = sprintf("`%s` = '%s'", $key, $value);
        }

        return implode(',', $fields);
    }

    public function field($field=array())
    {
        !is_array($field) && $field=(array)$field;
        if($field){
            $this->field = trim(implode(',',$field),',');
        }
        return $this;
    }

    public function get_insert_id(){	//最后一次操作关联ID号
        $sth = $this->_dbHandle;
        return $sth->lastInsertId();
    }
}