sphinx/Frame/Package/Database/Single.php

<?php

namespace PhpLife\Frame\Package\Database;
use PhpLife\Frame\Filter;

/**
 * 数据库操作类
 * 单表模式
 */
class Single extends \PhpLife\Frame\Package\Database
{
    const CONFIG_CLASS = 'MySQL\\Core';
    protected static $prefix = 'ph_'; // 表前缀
    protected static $tableName = '';
    protected static $fields = array();
    protected static $primaryKey = 'id';

    /**
     * 获取计数
     * @param array $whereParam
     * @param string $whereString
     * @return int
     */
    public static function count($whereParam, $whereString = '')
    {
        $query = self::getQuery()->setWhere($whereParam, $whereString)->count();
        $result = self::getConnection()->read($query->getSQL(), $query->getData());
        if (!$result) {
            return 0;
        }
        $result = current($result);
        return intval($result['count']);
    }

    /**
     * 获取列表，limit 不传取全部
     * @param array $fields
     * @param array $whereParam
     * @param string $whereString
     * @param int $limit 不传取全部
     * @param int $offset
     * @param array $orderBy
     * @return array
     */
    public static function select(
        $fields = array(),
        $whereParam = array(),
        $whereString = '',
        $limit = null,
        $offset = 0,
        $orderBy = array())
    {
        $query = self::getQuery()
            ->setField($fields)
            ->setWhere($whereParam, $whereString)
            ->setLimit($limit, $offset)
            ->setOrderBy($orderBy)
            ->select();
        $result = self::getConnection()->read($query->getSQL(), $query->getData());
        if (!$result) {
            return $result;
        }
        $data = array();
        $class = get_called_class();
        foreach ($result as $row) {
            $data[$row[$class::$primaryKey]] = $row;
        }
        unset($result);
        return $data;
    }

    /**
     * 获取列表数据，简单版
     * @param array $whereParam
     * @param int $limit
     * @param $orderBy
     * @return array
     */
    public static function getList($whereParam = array(), $limit = 10, $orderBy = array())
    {
        return self::select(array(), $whereParam, '', $limit, 0, $orderBy);
    }

    /**
     * 获取列表ID信息
     * @param string $field
     * @param array $whereParam
     * @param $orderBy
     * @param $limit
     * @return array
     */
    public static function getListId($field = '', $whereParam = array(), $orderBy = array(), $limit = 2000)
    {
        $query = self::getQuery()
            ->setField($field)
            ->setWhere($whereParam)
            ->setLimit($limit)
            ->setOrderBy($orderBy)
            ->select();
        $result = self::getConnection()->read($query->getSQL(), $query->getData());
        if (!$result) {
            return $result;
        }
        $data = array();
        foreach ($result as $row) {
            $data[] = $row[$field];
        }
        unset($result);
        return $data;
    }

    /**
     * 分页获取数据
     * @param array $whereParam
     * @param string $whereString
     * @param int $page
     * @param int $pageSize
     * @param array $orderBy
     * @param array $fields
     * @return array
     */
    public static function getPageList($whereParam = array(), $whereString='', $page = 1, $pageSize = 20, $orderBy = array(), $fields = array()){
        $page = $page ? $page : 1;
        $offset = ($page - 1) * $pageSize;
        //获取总条数
        $total_count = self::count($whereParam,$whereString);
        $pager = array(
            'total_count' => $total_count,
            'total_page' => 0,
            'current_page' => $page,
            'page_size' => $pageSize,
            'data' => array()
        );
        if($total_count > 0){
            list($url,) = explode('?',$_SERVER['REQUEST_URI']);
            unset($_GET['page']);
            //链接
            $pager['url'] = $url;
            //请求参数
            $pager['filter'] =  $_GET ? http_build_query($_GET) : '';
            //总页数
            $pager['total_page'] = ceil($total_count / $pageSize);
            //内容
            $pager['data'] = self::select($fields,$whereParam,$whereString,$pageSize,$offset,$orderBy);
        }
        return $pager;
    }

    /**
     * 获取单条记录
     * @param array $whereParam
     * @param string $whereString
     * @param array $orderBy
     * @return array;
     */
    public static function getOne($whereParam = array(), $whereString = '', $orderBy = array())
    {
        $query = self::getQuery()
            ->setWhere($whereParam, $whereString)
            ->setOrderBy($orderBy)
            ->setLimit(1)
            ->select();
        $result = self::getConnection()->read($query->getSQL(), $query->getData(), false, false);
        return $result;
    }

    /**
     * 根据主键获取单条记录
     * @param int $id
     * @return array
     */
    public static function getLine($id)
    {
        if (!$id) {
            return array();
        }
        $class = get_called_class();
        $where = array($class::$primaryKey => $id);
        $query = self::getQuery()->setWhere($where)->setLimit(1)->select();
        $result = self::getConnection()->read($query->getSQL(), $query->getData(), false, false);
        return $result;
    }

    /**
     * 批量根据主键查询, 按照传入的ID进行排序
     * @param $idList
     * @return array|mixed
     */
    public static function getBatch($idList)
    {
        if (!$idList) {
            return array();
        }
        $class = get_called_class();
        $where = array($class::$primaryKey => $idList);
        $query = self::getQuery()->setWhere($where)->select();
        $result = self::getConnection()->read($query->getSQL(), $query->getData());
        if (!$result) {
            return $result;
        }
        $data = array();
        foreach ($result as $row) {
            $data[$row[$class::$primaryKey]] = $row;
        }
        // 按照传入的id顺序进行重新排序,如果id对应的value不存在则忽略
        $result = array();
        foreach ($idList as $id) {
            if (isset($data[$id])) {
                $result[$id] = $data[$id];
            }
        }
        unset($data);
        return $result;
    }

    /**
     * 聚合获取列表数据
     * @param $field
     * @param $whereParam
     * @param $whereString
     * @param $groupBy
     * @param $limit
     * @return array|mixed
     */
    public static function getListByGroup($field,$whereParam,$whereString,$groupBy,$limit=null){
        $query = self::getQuery()
            ->setField($field)
            ->setWhere($whereParam,$whereString)
            ->setGroupBy($groupBy)
            ->setLimit($limit)
            ->select();
        return self::getConnection()->read($query->getSQL(), $query->getData());
    }

    /**
     * 联合获取列表数据
     * @param $whereString
     * @param $join
     * @param $sort
     * @param string $field
     * @return array|mixed
     */
    public static function getListByJoin($whereString,$join,$sort='',$field=''){
        $class = get_called_class();
        $tableName = $class::$tableName;
        $field = $field ? : $tableName.'.*';
        $sql = 'select '.$field.' from '.$tableName.' left join '.$join[0].' on '.$tableName.'.'.$join[1].'='.$join[0].'.'.$join[2];
        $whereString ? $sql .= ' where '.$whereString : null;
        $sort ? $sql .= ' order by '.$sort : null;
        $sql = str_replace('SELF',$tableName,$sql);//SELF代表主表
        $sql = str_replace('JOIN',$join[0],$sql);//JOIN代表连接表
        $result =  self::getConnection()->read($sql, array());
        $data = array();
        $class = get_called_class();
        foreach ($result as $row) {
            $data[$row[$class::$primaryKey]] = $row;
        }
        return $data;
    }

    /**
     * 创建记录
     * @param $data
     * @param bool 是否返回最后最后插入ID
     * @return bool|int
     */
    public static function insert($data, $returnLastInsertId = true)
    {
        $query = self::getQuery()->setData($data)->insert();
        $result = self::getConnection()->write($query->getSQL(), $query->getData());
        // 返回最后插入的id
        if ($result && $returnLastInsertId) {
            $result = self::getConnection()->getLastInsertId();
        }
        return $result;
    }

    /**
     * 批量插入
     * $data = array(
     *      array(
     *          'id' => 10001,
     *          'target_id' => 100,
     *      ),
     *      array(
     *          'id' => 10002,
     *          'target_id' => 100,
     *      ),
     * );
     * @param $data
     * @return bool|int
     */
    public static function insertBatch($data)
    {
        $prefix = 'prepared_';
        // 确定字段名
        $fields = implode('`, `', array_keys(current($data)));
        //print_r($data);
        // 拼SQL和数据
        $dataList = array();
        $values = array();
        $i = 0;
        foreach ($data as $k => $v) {
            foreach ($v as $x => $y) {
                $key = $prefix . $i;
                $dataList[$key] = $y;
                $v[$x] = ':' . $key;
                $i++;
            }
            $values[] = "(" . implode(", ", $v) . ")";
        }
        $values = implode(', ', $values);
        $class = get_called_class();
        $tableName = $class::$tableName;
        //print_r($values);die();
        $sql = "INSERT INTO `" . $tableName . "` (`$fields`) VALUES $values";
        //print_r($sql);
        $result = self::write($sql, $dataList);
        return $result;
    }

    /**
     * 更新记录
     * @param $data
     * @param $whereParam
     * @param $whereString
     * @return bool
     */
    public static function update($data, $whereParam, $whereString = '')
    {
        $query = self::getQuery()->setData($data)->setWhere($whereParam, $whereString)->update();
        $result = self::getConnection()->write($query->getSQL(), $query->getData());
        return $result;
    }

    /**
     * 替换输入
     * @param $data
     * @param $whereParam
     * @param $whereString
     * @return bool
     */
    public static function replace($data, $whereParam, $whereString = '')
    {
        $query = self::getQuery()->setData($data)->setWhere($whereParam, $whereString)->replace();
        $result = self::getConnection()->write($query->getSQL(), $query->getData());
        return $result;
    }

    /**
     * 删除逻辑
     * @param $whereParam
     * @param $whereString
     * @return bool
     */
    public static function delete($whereParam, $whereString = '')
    {
        $query = self::getQuery()->setWhere($whereParam, $whereString)->delete();
        $result = self::getConnection()->write($query->getSQL(), $query->getData());
        return $result;
    }


    /**
     * 获取一个新的SQL生成器
     * @return \PhpLife\Frame\Library\MySQL\Query
     */
    protected static function getQuery()
    {
        $class = get_called_class();
        return \PhpLife\Frame\Library\MySQL\Query::newInstance()
            ->setTableName($class::$tableName)
            ->setField($class::$fields)
            ->setPrimaryKey($class::$primaryKey);
    }

    /**
     * 从数据库读取数据，可以强制从主库读取
     * @param string $sql
     * @param array $data
     * @param bool $isMaster
     * @return mixed|Array
     */
    protected static function read($sql, $data, $isMaster = false)
    {
        $result = self::getConnection()->read($sql, $data, $isMaster);
        return $result;
    }

    /**
     * 写入
     * @param $sql
     * @param $data
     * @return bool|int
     */
    protected static function write($sql, $data)
    {
        $result = self::getConnection()->write($sql, $data);
        return $result;
    }

    /**
     * 获取连接池
     * @return \PhpLife\Frame\Library\MySQL
     */
    static function getConnection()
    {
        $class = get_called_class();
        $configClass = $class::CONFIG_CLASS;
        return new \PhpLife\Frame\Library\MySQL($configClass);
    }

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

}