shadowsocks5/app/dao/commonDao.php

<?php

/**
 * 数据层的基类，$table_name和$fields需要被实例化
 * @author Tinson Ho <hts@meitu.com>
 */
class commonDao extends Dao
{

    protected $prefix = 'ph_'; // 表前缀
    protected $table_name; // 表名，待实例化
    protected $fields; // 字段名组合，用,分隔，待实例化
    protected $dbType; // 多数据库服务器，不实例化时使用默认数据库

    public function __construct()
    {
        parent::__construct();
        if (!empty($this->dbType)) {
            $this->init_db($this->dbType);
        }
    }

    /**
     * 获取数据库连接（多数据库）
     * @return dbInit
     */
    protected function getDb()
    {
        return !empty($this->dbType) ? $this->init_db($this->dbType) : $this->init_db('default');
    }

    /**
     * 通过ID获取单条记录
     * @param int $id
     * @param string $table_name
     * @return array
     */
    public function getOne($id, $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        return $this->getDb()->get_one($id, $table_name);
    }

    /**
     * 分页获取
     * @param array $field
     * @param int $num
     * @param int $offset
     * @param string $id_key
     * @param string $sort
     * @param string $table_name
     * @return array
     */
    public function getList($field = array(), $num = 20, $offset = 0, $id_key = 'id', $sort = 'ASC', $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        $id_key = !empty($this->idKey) && $id_key == 'id' ? $this->idKey : $id_key;
        $result = $this->getDb()->get_all($table_name, $field, $num, $offset, $id_key, $sort);
        return $result [0];
    }

    /**
     * 获取全部
     * @param array $field
     * @param string $id_key
     * @param string $sort
     * @param string $table_name
     * @return array
     */
    public function getAll($field = array(), $id_key = 'id', $sort = 'ASC', $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        $id_key = !empty($this->idKey) && $id_key == 'id' ? $this->idKey : $id_key;
        return $this->getAllByMutiple($field, null, array($id_key => $sort), null, null, $table_name);
    }

    /**
     * 计数
     * @param array $field
     * @param string $table_name
     * @return int
     */
    public function count($field = array(), $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        return $this->getDb()->get_count($table_name, $field);
    }

    /**
     * 添加
     * @param array $data
     * @param bool $ignore
     * @param string $table_name
     * @return int
     */
    public function add($data, $ignore = false, $table_name = null)
    {
        if (!$table_name) {
            $table_name = $this->table_name;
            $data = $this->dao->db->build_key($data, $this->fields);
        }
        return $this->getDb()->insert($data, $table_name, $ignore);
    }

    /**
     * 多条记录插入
     * @param array $data
     * @param bool $ignore
     * @param string $table_name
     * @return int
     */
    public function addBatch($data, $ignore = false, $table_name = null)
    {
        $fields = array();
        foreach ($data as $one_data) {
            foreach ($one_data as $key => $val) {
                array_push($fields, $key);
            }
            break;
        }
        !$table_name ? $table_name = $this->table_name : null;
        return $this->getDb()->insert_more($fields, $data, $table_name, $ignore);
    }

    /**
     * 更新(基于ID)
     * @param int $id
     * @param array $data
     * @param string $table_name
     * @return int
     */
    public function update($id, $data, $table_name = null)
    {
        if (!$table_name) {
            $table_name = $this->table_name;
            $data = $this->dao->db->build_key($data, $this->fields);
        }
        return $this->getDb()->update($id, $data, $table_name);
    }

    /**
     * 更新（基于其他字段）
     * @param array $data
     * @param array $field
     * @param string $table_name
     * @return int
     */
    public function updateByField($data, $field, $table_name = null)
    {
        if (!is_array($field)) {
            return false;
        }
        if (!$table_name) {
            $table_name = $this->table_name;
            $data = $this->dao->db->build_key($data, $this->fields);
        }
        return $this->getDb()->update_by_field($data, $field, $table_name);
    }

    /**
     * 删除（可以是ID数组，也可以是单个ID）
     * @param array / int $ids
     * @param string $table_name
     * @return int
     */
    public function delete($ids, $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        return $this->getDb()->delete($ids, $table_name);
    }

    /**
     * 删除（通过字段条件删除）
     * @param array $field
     * @param string $table_name
     * @return int
     */
    public function deleteByField($field, $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        return $this->getDb()->delete_by_field($field, $table_name);
    }

    /**
     * 通过字段查询获取第一条记录
     * @param array $data
     * @param string $table_name
     * @return array
     */
    public function getOneByField($data = array(), $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        return $this->getDb()->get_one_by_field($data, $table_name);
    }

    /**
     * Sql语句查询，获取全部记录
     * @param string $sql
     * @return array / bool
     */
    public function queryAll($sql)
    {
        $result = $this->getDb()->query($sql, false);
        if (!$result) {
            return false;
        }
        $arr = array();
        while ($row = $this->getDb()->fetch_assoc($result)) {
            $arr [] = $row;
        }
        return $arr;
    }

    /**
     * 从Sql语句查询，获取第一条记录
     * @param string $sql
     * @return array / bool
     */
    public function queryRow($sql)
    {
        $result = $this->getDb()->query($sql, false);
        if (!$result) {
            return false;
        }
        $arr = $this->getDb()->fetch_assoc($result);
        return $arr;
    }

    /**
     * SQL语句查询，获取第一条记录的第一个字段
     * @param string $sql
     * @return string / bool
     */
    public function queryOne($sql)
    {
        $arr = $this->queryRow($sql);
        foreach ($arr as $val) {
            return $val;
        }
        return false;
    }

    /**
     * 执行SQL语句
     * @param string $sql
     * @return mix
     */
    public function query($sql)
    {
        return $this->getDb()->query($sql);
    }

    /**
     * 获取表前缀
     * @return string
     */
    public function getPrefix()
    {
        return isset($this->prefix) ? $this->prefix : '';
    }

    /**
     * 获取表名
     * @return string
     */
    public function getTableName()
    {
        return $this->table_name;
    }

    /**
     * 生成WHERE子句
     *
     * @param array $map
     *            array('field1'=>'11','field2'=>array('value1','value3'),'field3'=>array('_logic'=>'>=','value'=>1) );
     * @param string $tag 数据库表标识名
     * @return string
     */
    public function buildWhere($map, $tag = '')
    {
        if (!is_array($map) || empty($map)) {
            return '';
        }
        $temp = array();
        foreach ($map as $k => $v) {
            if (is_array($v)) {
                if (!empty($v ['_link'])) {
                    $link = $v ['_link'];
                    unset($v ['_link']);
                    $str = $this->buildWhereWithLink($v, $link, $tag);
                    !empty($str) ? $temp [] = ' ( ' . $str . ' ) ' : null;
                } else {
                    $temp [] = $this->buildWhereWithLink(array(
                        $k => $v
                    ), 'AND', $tag);
                }
            } else {
                $temp [] = $this->buildWhereWithLink(array(
                    $k => $v
                ), 'AND', $tag);
            }
        }
        return ' WHERE ' . implode(' AND ', $temp);
    }

    public function buildWhereWithLink($map, $link = 'AND', $tag = '')
    {
        if (!is_array($map) || empty($map)) {
            return '';
        }
        $link = strtoupper($link);
        if (!in_array($link, array(
            'AND',
            'OR'
        ))
        ) {
            return '';
        }
        $temp = array();
        $tag = $tag ? $this->dao->db->build_escape($tag, 1) . '.' : '';
        $fhArr = array(
            '<',
            '<=',
            '>',
            '>=',
            '!=',
            '<>',
            'LIKE',
            '='
        );
        foreach ($map as $k => $v) {
            if (is_array($v)) {
                if (!empty($v ['_logic'])) {
                    $v ['_logic'] = strtoupper($v ['_logic']);
                    if (in_array($v ['_logic'], $fhArr)) {
                        $temp [] = $tag . $this->dao->db->build_escape($k, 1) . ' ' . $v ['_logic'] . ' ' . $this->dao->db->build_escape($v ['value']) . '';
                    }
                } else {
                    $mixArr = array();
                    foreach ($v as $ke => $va) {
                        if (is_array($va)) {
                            if (!empty($va ['_logic'])) {
                                $va ['_logic'] = strtoupper($va ['_logic']);
                                if (in_array($va ['_logic'], $fhArr)) {
                                    $temp [] = $tag . $this->dao->db->build_escape($k, 1) . ' ' . $va ['_logic'] . ' ' . $this->dao->db->build_escape($va ['value']) . '';
                                }
                            }
                        } else {
                            $mixArr [] = $this->dao->db->build_escape($va);
                        }
                    }
                    !empty($mixArr) ? $temp [] = $tag . $this->dao->db->build_escape($k, 1) . 'IN (' . implode(',', $mixArr) . ') ' : null;
                }
            } else {
                $temp [] = $tag . $this->dao->db->build_escape($k, 1) . '=' . $this->dao->db->build_escape($v);
            }
        }
        return implode(' ' . $link . ' ', $temp);
    }

    /**
     * 生成ORDER BY字句
     *
     * @param array $orderby array('field'=>'desc');
     * @param string $tag 数据库表标识名
     * @return string
     */
    public function buildOrderBy($orderby, $tag = '')
    {
        if (!is_array($orderby) || empty($orderby)) {
            return '';
        }
        $tag = $tag ? $this->dao->db->build_escape($tag, 1) . '.' : '';
        $temp = array();
        foreach ($orderby as $key => $val) {
            $temp [] = $tag . $this->dao->db->build_escape($key, 1) . ' ' . ($val == 'desc' ? 'desc' : 'asc');
        }
        return ' ORDER BY  ' . implode(',', $temp);
    }

    /**
     * 生成LIMIT字句
     *
     * @param array / int $limit 限制数 eg. (1)array(0,10) (2)10
     * @return string
     */
    public function buildLimit($limit)
    {
        if (empty($limit)) {
            return '';
        }
        if (is_array($limit)) {
            return isset($limit [1]) ? ' LIMIT ' . intval($limit [0]) . ',' . intval($limit [1]) : ' LIMIT ' . intval($limit [0]);
        } else {
            return ' LIMIT ' . intval($limit);
        }
    }

    /**
     * 生成GROUP BY字句
     *
     * @param mixed(array,string) $groupby 字段
     * @param string $tag 数据库表标识名
     * @return string
     */
    public function buildGroupBy($groupby, $tag = '')
    {
        if (empty($groupby)) {
            return '';
        }
        $tag = $tag ? $this->dao->db->build_escape($tag, 1) . '.' : '';
        if (is_array($groupby)) {
            foreach ($groupby as $key => $val) {
                $groupby [$key] = $tag . $this->dao->db->build_escape($val, 1);
            }
            return ' GROUP BY ' . implode(',', $groupby);
        } else {
            return ' GROUP BY ' . $this->dao->db->build_escape($tag . $groupby, 1);
        }
    }

    /**
     * 生成字段字句
     *
     * @param mixed(array,string) $fields 字段
     * @param string $tag 数据库表标识名
     * @return string
     */
    public function buildFields($fields, $tag = '')
    {
        $tag = $tag ? $this->dao->db->build_escape($tag, 1) . '.' : '';
        if (empty($fields)) {
            return ' ' . $tag . '* ';
        } else if (is_array($fields)) {
            $temp = array();
            foreach ($fields as $k => $v) {
                $temp [] = $tag . $this->dao->db->build_escape($v, 1);
            }
            return implode(',', $temp);
        } else {
            return $fields;
        }
    }

    /**
     * 复杂的单表查询接口
     *
     * @param array $map
     *            查询条件 eg. array('field1'=>'11','field2'=>array('value1','value3'),'field3'=>array('_logic'=>'>=','value'=>1) );
     * @param array / string $fields
     *            查询字段 eg. （1）array('字段1','字段2') （2） '字段1，字段2'
     * @param array $orderby
     *            排序条件 eg. array('id'=>'asc','appid'=>'desc')
     * @param array / int $limit
     *            限制查询数量 eg. (1)array(0,10) (2)10
     * @param array / string $groupby
     *            Group条件 eg (1)array('sid','pid') (2)pid
     * @param string $table_name
     * @return array
     */
    public function getAllByMutiple($map = array(), $fields = array(), $orderby = array(), $limit = array(), $groupby = array(), $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        $fields = $this->buildFields($fields);
        $sql = 'SELECT ' . $fields . ' FROM `' . $table_name . '` ';
        $sql .= $this->buildWhere($map);
        $sql .= $this->buildGroupBy($groupby);
        $sql .= $this->buildOrderBy($orderby);
        $sql .= $this->buildLimit($limit);
        return $this->queryAll($sql);
    }

    /**
     * 生成update字句
     *
     * @param array $set
     *            更新项 array('field1'=>'value1','field2'=>array('_logic'=>'+','value'=>'value2'))
     * @param string $tag
     * @return string
     */
    public function buildSet($set, $tag = '')
    {
        if (!is_array($set) || empty($set)) {
            return '';
        }
        $tag = $tag ? $this->dao->db->build_escape($tag, 1) . '.' : '';
        $temp = array();
        foreach ($set as $k => $v) {
            if (is_array($v)) {
                if (!empty($v ['_logic'])) {
                    if (in_array($v ['_logic'], array(
                        '+',
                        '-',
                        '*',
                        '/',
                        '%'
                    ))) {
                        $temp [] = $tag . $this->dao->db->build_escape($k, 1) . '=' . $tag . $this->dao->db->build_escape($k, 1) . $v ['_logic'] . $v ['value'];
                    }
                }
            } else {
                $temp [] = $tag . $this->dao->db->build_escape($k, 1) . '=' . $this->dao->db->build_escape($v);
            }
        }
        return ' SET ' . implode(',', $temp);
    }

    /**
     * 复杂的单表更新接口
     *
     * @param array $set
     *            设置字段项 eg. array('field1'=>'value1','field2'=>array('_logic'=>'+','value'=>'value2'))
     * @param array $map
     *            条件项 eg. array('field1'=>'11','field2'=>array('value1','value3'),'field3'=>array('_logic'=>'>=','value'=>1) );
     * @param string $table_name
     * @return mix(interger or bool)
     */
    public function updateByMutiple($set, $map, $table_name = null)
    {
        if (!is_array($map)) {
            return false;
        }
        !$table_name ? $table_name = $this->table_name : null;
        $sql = 'UPDATE `' . $table_name . '` ';
        $sql .= $this->buildSet($set);
        $sql .= $this->buildWhere($map);
        return $this->getDb()->query($sql);
    }

    /**
     * 复杂的计算接口
     *
     * @param array $map
     *            条件项 eg. array('field1'=>'11','field2'=>array('value1','value3'),'field3'=>array('_logic'=>'>=','value'=>1) );
     * @param string $table_name
     * @return int
     */
    public function countByMutiple($map = array(), $table_name = null)
    {
        !$table_name ? $table_name = $this->table_name : null;
        $sql = 'SELECT COUNT(*) FROM `' . $table_name . '` ';
        $sql .= $this->buildWhere($map);
        return $this->queryOne($sql);
    }

    /**
     * 复杂的删除语句
     *
     * @param array $map
     *            条件项 eg. array('field1'=>'11','field2'=>array('value1','value3'),'field3'=>array('_logic'=>'>=','value'=>1) );
     * @param string $table_name
     * @return bool
     */
    public function deleteByMutiple($map, $table_name = null)
    {
        if (!is_array($map)) {
            return false;
        }
        !$table_name ? $table_name = $this->table_name : null;
        $sql = 'DELETE FROM `' . $table_name . '` ';
        $sql .= $this->buildWhere($map);
        return $this->query($sql);
    }

    /**
     * 最小自增ID
     *
     * @param string $table_name 表名
     * @param array $map 查询条件
     * @return int
     */
    public function getMinId($table_name = null, $map = array())
    {
        $list = $this->getAllByMutiple($map, "MIN(id)  as min_id", null, null, null, $table_name);
        return isset($list[0]['min_id']) ? (int)$list[0]['min_id'] : 0;
    }

    /**
     * 最大自增ID
     *
     * @param string $table_name 表名
     * @param array $map 查询条件
     * @return int
     */
    public function getMaxId($table_name = null, $map = array())
    {
        $list = $this->getAllByMutiple($map, "MAX(id)  as max_id", null, null, null, $table_name);
        return isset($list[0]['max_id']) ? (int)$list[0]['max_id'] : 0;
    }

}