crm/protected/components/DB.php

<?php

use CDbException;
use DbCriteria;
use Yii;

class DB
{
    /**
     * 批量插入
     *
     * @param $table
     * @param  array  $data
     * @param  array  $header  可以不传 但 $data 必须是关联数组集合
     * @return int
     * @throws CDbException
     */
    public static function safeBatchInsert($table, array $data, array $header = [])
    {
        $params = [];
        if (!$data) {
            return false;
        }
        if (!$header) {
            $header = array_keys($data[0]);
        }
        $headerStr = implode(',', $header);
        $sql = "INSERT INTO {$table} ({$headerStr}) values ";
        foreach ($data as $k => $datum) {
            $tempValues = [];
            $i = 0;
            foreach ($datum as $ik => $item) {
                $paramKey = ':'.$header[$i].$k;
                $paramValue = is_numeric($item) ? $item : "{$item}";
                $params[$paramKey] = $paramValue;
                $tempValues[] = $paramKey;
                $i++;
            }

            $valueStr = implode(',', $tempValues);
            $sql .= "({$valueStr}),";
        }

        return Yii::app()->db->createCommand(rtrim($sql, ','))->execute($params);
    }

    /**
     * 获取sql操作command对象
     *
     * @param  string  $dbName
     * @return \CDbCommand
     */
    public static function getDbCommand($dbName = 'db')
    {
        $command = \Yii::app()->$dbName->createCommand();
        $command->reset();

        return $command;
    }

    /**
     * 从数据库获取数据的通用方法
     *
     * @param  string  $tableName  表名
     * @param  array  $filters  筛选条件
     * @param  string  $fields  字段
     * @param  string  $group
     * @param  string  $order
     * @param  integer  $page  分页，如果为0，就不分页
     * @param  integer  $pageSize
     * @param  string  $index  强制指定的索引
     * @param  string  $dbName  数据库名
     * @return array
     */
    public static function getDataByCondition(
        $tableName,
        $filters,
        $fields = '*',
        $group = '',
        $order = '',
        $page = 0,
        $pageSize = 0,
        $index = '',
        $dbName = 'db'
    ): array {
        $db = self::getDbCommand($dbName);

        if (!empty($index)) {
            $tableName .= " FORCE INDEX (`{$index}`)";
        }

        $criteria = new \CDbCriteria();

        if (!empty($filters)) {
            foreach ($filters as $key => $value) {
                if (is_array($value) && 'like' === reset($value)) {
                    $criteria->compare($key, array_pop($value), true);
                } elseif (is_array($value) && 'condition' === reset($value)) {
                    $criteria->addCondition(array_pop($value));
                } elseif (is_array($value) && 'betweenCondition' === reset($value)) {
                    $criteria->addBetweenCondition($key, $value[1], $value[2]);
                } elseif (is_array($value) && $value === array()) {
                    $criteria->addInCondition($key, $value);
                } elseif (empty($value)) {
                    $criteria->addCondition("{$key} = :{$key}");
                    $criteria->params[':'.$key] = $value;
                } else {
                    $criteria->compare($key, $value);
                }
            }
        }

        $build = $db->select($fields)
            ->from($tableName)
            ->where($criteria->condition, $criteria->params);

        !empty($group) && $build->group($group);
        !empty($order) && $build->order($order);

        if ($page > 0) {
            $page = intval($page);
            $pageSize = intval($pageSize);
            $offset = $pageSize * ($page - 1);
            $build->limit($pageSize, $offset);
        }

        /* if ($isLock) {
            $sql = $build->getText() . " FOR UPDATE";
            return $this->queryAllBySql($sql, $build->params);
        } */

        return $build->queryAll();
    }

    /**
     * 从数据库获取数据的通用方法
     *
     * @param  string  $tableName  表名
     * @param  array  $filters  筛选条件
     * @param  string  $fields  字段
     * @param  string  $group
     * @param  string  $order
     * @param  integer  $page  分页，如果为0，就不分页
     * @param  integer  $pageSize
     * @param  string  $index  强制指定的索引
     * @param  string  $dbName  数据库名
     * @return array
     */
    public static function updateData($tableName, $filters, $dbName = 'db')
    {
        $db = self::getDbCommand($dbName);
        if (!isset($filters['id'])) {
            return false;
        }

        $id = $filters['id'];
        unset($filters['id']);

        $ret = $db->update($tableName, $filters, "id=:id", array(':id' => $id));

        return true;
    }

    public static function updateById($tableName, $filters, $id)
    {
        $id = intval($id);
        if ($id <= 0) {
            return false;
        }
        $db = self::getDbCommand();
        $db->update(self::formTableName($tableName), $filters, "id=:id", [':id' => $id]);

        return true;
    }

    public static function deleteById($tableName, $id)
    {
        $id = intval($id);
        if ($id <= 0) {
            return false;
        }
        $db = self::getDbCommand();
        $db->delete(self::formTableName($tableName), "id=:id", [':id' => $id]);
        return true;
    }

    public static function formTableName($tableName)
    {
        return 'wx_' . trim($tableName, 'wx_');
    }


    /**
     * 从数据库获取数据的通用方法，与上面的方法相比，就是多了分页的内容
     *
     * @param  string  $tableName  表名
     * @param  array  $filters  筛选条件
     * @param  string  $fields  字段
     * @param  string  $group
     * @param  string  $order
     * @param  integer  $page  分页，如果为0，就不分页
     * @param  integer  $pageSize
     * @param  string  $index  强制指定的索引
     * @param  string  $dbName  数据库名
     * @return array
     */
    public static function getWebDataByCondition(
        $tableName,
        $filters,
        $fields = '*',
        $group = '',
        $order = '',
        $page = 0,
        $pageSize = 0,
        $index = '',
        $dbName = 'db'
    ) {
        $return = [
            'page'      => $page,
            'pageSize'  => $pageSize,
            'totalPage' => -1,
            'counts'    => -1,
            'records'     => [],
        ];

        $db = self::getDbCommand($dbName);

        if (!empty($index)) {
            $tableName .= " FORCE INDEX (`{$index}`)";
        }

        $criteria = new \CDbCriteria();

        if (!empty($filters)) {
            foreach ($filters as $key => $value) {
                if (is_array($value) && 'like' === reset($value)) {
                    $criteria->compare($key, array_pop($value), true);
                } elseif (is_array($value) && 'condition' === reset($value)) {
                    $criteria->addCondition(array_pop($value));
                } elseif (is_array($value) && 'betweenCondition' === reset($value)) {
                    $criteria->addBetweenCondition($key, $value[1], $value[2]);
                } elseif (is_array($value) && $value === array()) {
                    $criteria->addInCondition($key, $value);
                } elseif (empty($value)) {
                    $criteria->addCondition("{$key} = :{$key}");
                    $criteria->params[':'.$key] = $value;
                } else {
                    $criteria->compare($key, $value);
                }
            }
        }

        // 如果有分页，则查询总记录数
        $counts = -1;
        if ($page > 0) {
            if (empty($group)) {
                $counts = $db->select("count(*) as total")
                    ->from($tableName)
                    ->where($criteria->condition, $criteria->params)
                    ->queryScalar();
                $db->reset();
            } else {
                $count_data = $db->select("{$group}")
                    ->from($tableName)
                    ->where($criteria->condition, $criteria->params)
                    ->group($group)
                    ->queryAll();
                $db->reset();
                $counts = count($count_data);
            }

            $counts = intval($counts);
            if ($counts < 1) {
                return $return;  // 数据为空
            }
        }

        $build = $db->select($fields)
            ->from($tableName)
            ->where($criteria->condition, $criteria->params);

        !empty($group) && $build->group($group);
        !empty($order) && $build->order($order);

        // 如果有分页，则对查询条件做处理
        $totalPages = -1;
        if ($pageSize > 0) {
            //计算总页数
            $totalPages = ceil($counts / $pageSize);
            $offset = $pageSize * ($page - 1);
            $build->limit($pageSize, $offset);

            $return['totalPage'] = $totalPages;
            $return['counts'] = $counts;
        }

        $return['records'] = $build->queryAll();

        return $return;
    }

    /**
     * 从数据库获取数据的通用方法，只获取一条数据
     *
     * @param  string  $tableName  表名
     * @param  array  $filters  筛选条件
     * @param  string  $fields  字段
     * @param  string  $group
     * @param  string  $order
     * @param  integer  $page  分页，如果为0，就不分页
     * @param  integer  $pageSize
     * @param  string  $index  强制指定的索引
     * @param  string  $dbName  数据库名
     * @return array
     */
    public static function getOneByCondition(
        $tableName,
        $filters,
        $fields = '*',
        $group = '',
        $index = '',
        $dbName = 'db'
    ) {
        $data = self::getDataByCondition($tableName, $filters, $fields, $group, '', 0, 0, $index, $dbName);

        return !empty($data) ? reset($data) : [];
    }

    /**
     * 向数据库插入数据
     *
     * @param  string  $tableName  表名
     * @param  array  $info
     * @param  string  $dbName
     * @return int
     */
    public static function addData($tableName, $info, $dbName = 'db')
    {
        if (empty($info)) {
            return false;
        }
        $command = self::getDbCommand($dbName);

        $ret = $command->insert(self::formTableName($tableName), $info);

        if ($ret) {
            return $command->getConnection()->getLastInsertID();
        }

        return false;
    }

    /**
     * 根据条件删除数据库内容
     *
     * @param  string  $tableName
     * @param  array  $filters
     * @param  string  $dbName
     * @return int
     */
    public static function deleteByCondition($tableName, $filters, $dbName = 'db')
    {
        if (empty($filters)) {
            return false;
        }
        $command = self::getDbCommand($dbName);

        $condition = '';
        $params = [];
        foreach ($filters as $key => $value) {
            $condition .= " {$key}=:{$key} AND";
            $params[':'.$key] = $value;
        }
        $condition = trim($condition, 'AND');
        $condition = trim($condition);

        return $command->delete($tableName, $condition, $params);
    }

    /**
     * 获取model的错误信息
     *
     * @param  object  $model
     * @return string
     */
    public static function getModelErrorMsg($model)
    {
        $str = '';
        if ($model->hasErrors()) {
            $errors = [];
            foreach ($model->getErrors() as $error) {
                $errors += $error;
            }
            $str = implode("<br/>", $errors);
        }

        return $str;
    }

    /**
     * 通过 DbCriteria 来搜索
     * @param  string  $table_name  表名
     * @param  DbCriteria  $criteria  分页通过 setPage 设置,否则不会查询分页信息
     * @return array|\CDbDataReader 格式: ['page'=>1, 'pageSize'=>1, 'totalPage'=>1, 'counts'=>1, 'records'=>[]]
     * @throws \CException
     */
    public static function getListWithCriteria(string $table_name, DbCriteria $criteria)
    {
        // 数据返回格式
        $retData = [
            'current'      => $criteria->getPage(),
            'size'  => $criteria->getPageSize(),
            'totalPage' => 0,
            'total'    => 0,
            'records'     => [],
        ];
        $table_name = self::formTableName($table_name);
        if ($criteria->alias) {
            $table_name .= ' as '.$criteria->alias;
        }
        // 指定索引
        if (!empty($criteria->forceIndex)) {
            $table_name .= " FORCE INDEX (`{$criteria->forceIndex}`)";
        }

        // 根据 DbCriteria 构建查询
        $build = self::getDbCommand()->select($criteria->select)
            ->from($table_name)
            ->where($criteria->condition, $criteria->params);
        !empty($criteria->group) && $build->group($criteria->group);
        !empty($criteria->having) && $build->having($criteria->having);
        !empty($criteria->join) && $build->setJoin($criteria->join);

        // 有分页需要先查询总数 然后再恢复 builder
        if ($criteria->isFenye()) {
            if (!empty($criteria->group)) {
                // 使用group以后 需要子查询来统计总条数
                $subQuery = $build->select($criteria->getCountSelectStr())->getText();
                $totalNum = self::getDbCommand()->select('count(*)')->from("($subQuery) t")->queryScalar(
                    $criteria->params
                );
            } else {
                $totalNum = $build->select($criteria->getCountSelectStr())->queryScalar();
            }
            $retData['total'] = $totalNum;
            $retData['totalPage'] = ceil($totalNum / $criteria->getPageSize());
            $build = self::getDbCommand()->select($criteria->select)
                ->from($table_name)
                ->where($criteria->condition, $criteria->params);
            !empty($criteria->group) && $build->group($criteria->group);
            !empty($criteria->having) && $build->having($criteria->having);
            !empty($criteria->join) && $build->setJoin($criteria->join);

            $build->limit($criteria->limit, $criteria->offset);
        } elseif ($criteria->limit > 0) {
            // 不做分页 单纯控制偏移及数量
            $build->limit($criteria->limit, $criteria->offset);
        }

        !empty($criteria->order) && $build->order($criteria->order);

        $retData['records'] = $build->queryAll() ?: [];

        // debug
        if ($criteria->getDebugMode()) {
            Logger::info(
                json_encode(
                    [
                        'DbCriteriaDebug' => $criteria->getSql($build->getText(), $criteria->params),
                        'tag'             => $criteria->getDebugTag(),
                    ],
                    JSON_UNESCAPED_UNICODE
                )
            );
        }

        return $retData;
    }

    /**
     * 通过 DbCriteria 来搜索
     * @param  string  $table_name  表名
     * @param  DbCriteria  $criteria
     * @return array|\CDbDataReader
     * @throws \CException
     */
    public static function getInfoWithCriteria(string $table_name, DbCriteria $criteria)
    {
        $table_name = self::formTableName($table_name);
        // 指定索引
        if (!empty($criteria->forceIndex)) {
            $table_name .= " FORCE INDEX (`{$criteria->forceIndex}`)";
        }

        // 根据 DbCriteria 构建查询
        $build = self::getDbCommand()->select($criteria->select)
            ->from($table_name)
            ->limit(1)
            ->where($criteria->condition, $criteria->params);
        !empty($criteria->group) && $build->group($criteria->group);
        !empty($criteria->having) && $build->having($criteria->having);
        !empty($criteria->order) && $build->order($criteria->order);

        // debug
        if ($criteria->getDebugMode()) {
            Logger::info(
                json_encode(
                    [
                        'DbCriteriaDebug' => $criteria->getSql($build->getText(), $criteria->params),
                        'tag'             => $criteria->getDebugTag(),
                    ],
                    JSON_UNESCAPED_UNICODE
                )
            );
        }

        return $build->queryRow();
    }

    public static function getScalerWithCriteria(string $table_name, DbCriteria $criteria)
    {
        $data = self::getInfoWithCriteria($table_name, $criteria);

        return $data ? reset($data) : '';
    }
}