yii2 ExtendActiveRecordModel.php

<?php
// model 基本模型类
namespace app\extend;

use yii\data\Pagination;
use yii\widgets\LinkPager;
use app\components\Functions;

class ExtendActiveRecordModel extends \yii\db\ActiveRecord {
    /**
     * 重载 读取数据库配置
     */
    public function buildInCondition($operator, $operands, &$params) {
        if (! isset ( $operands [0], $operands [1] )) {
            throw new \Exception ( "Operator '$operator' requires two operands." );
        }
        
        list ( $column, $values ) = $operands;
        
        if ($column === [ ]) {
            // no columns to test against
            return $operator === 'IN' ? '0=1' : '';
        }
        
        if ($column instanceof \Traversable || count ( $column ) > 1) {
            return $this->buildCompositeInCondition ( $operator, $column, $values, $params );
        } elseif (is_array ( $column )) {
            $column = reset ( $column );
        }
        
        $sqlValues = [ ];
        foreach ( $values as $i => $value ) {
            if (is_array ( $value ) || $value instanceof \ArrayAccess) {
                $value = isset ( $value [$column] ) ? $value [$column] : null;
            }
            if ($value === null) {
                $sqlValues [$i] = 'NULL';
            } else {
                $phName = self::PARAM_PREFIX . $i; // count($params) 改为 $i $$Richard IN 条件慢
                $params [$phName] = $value;
                $sqlValues [$i] = $phName;
            }
        }
        
        if (empty ( $sqlValues )) {
            return $operator === 'IN' ? '0=1' : '';
        }
        
        if (strpos ( $column, '(' ) === false) {
            $column = $this->db->quoteColumnName ( $column );
        }
        
        if (count ( $sqlValues ) > 1) {
            return "$column $operator (" . implode ( ', ', $sqlValues ) . ')';
        } else {
            $operator = $operator === 'IN' ? '=' : '<>';
            return $column . $operator . reset ( $sqlValues );
        }
    }
    
    /**
     * 重载 读取数据库配置
     */
    static public function getDb() {
        if (\Yii::$app->params ['dbSelect'] == 'main') {
            return \Yii::$app->maindb;
        } elseif (\Yii::$app->params ['dbSelect'] == 'company') {
            // 获取 companydsn
            if (\Yii::$app->params ['dsnSelect']) {
                            $companyDSN = require (__DIR__ . '/../config/companydsn.php');
                            \Yii::$app->companydb->dsn = $companyDSN [\Yii::$app->params ['dsnSelect']];
                        }
            return \Yii::$app->companydb;
        } elseif (\Yii::$app->params ['dbSelect'] == 'companyold') {
            return \Yii::$app->companyolddb;
        } elseif (\Yii::$app->params ['dbSelect'] == 'thirdparty') {
            return \Yii::$app->thirdpartydb;
        } elseif (\Yii::$app->params ['dbSelect'] == 'phathirdparty') {
            return \Yii::$app->phathirdpartydb;
        } elseif(\Yii::$app->params ['dbSelect'] == 'yht'){
            return \Yii::$app->yhtdb;
        }
    }
    
    /**
     * 获取列表数据
     */
    static public function baseFindList($class, $condition = []) {
        // 初始化ActiveQuery::find();
        $activeQuery = $class->find ();
        
        // 查询字段
        if (isset ( $condition ['SELECT'] ) && $condition ['SELECT']) {
            $activeQuery->select ( $condition ['SELECT'] );
        }
        // 关联表
        if (isset ( $condition ['WITH'] ) && $condition ['WITH']) {
            // 判断是否立即加载 默认否
            if (isset ( $condition ['WITHEAGER'] ) && $condition ['WITHEAGER']) {
                $activeQuery->joinWith ( $condition ['WITH'], $condition ['WITHEAGER'] );
            } else {
                $activeQuery->joinWith ( $condition ['WITH'], false );
            }
        }
        // 排序条件
        if (isset ( $condition ['ORDER'] ) && $condition ['ORDER']) {
            $activeQuery->orderBy ( $condition ['ORDER'] );
        }
        // 分组条件
        if (isset ( $condition ['GROUP'] ) && $condition ['GROUP']) {
            $activeQuery->groupBy ( $condition ['GROUP'] );
        }
        // 分组条件
        if (isset ( $condition ['HAVING'] ) && $condition ['HAVING']) {
            $activeQuery->having ( $condition ['HAVING'] );
        }
        // 截取
        if (isset ( $condition ['LIMIT'] ) && $condition ['LIMIT']) {
            $activeQuery->limit ( $condition ['LIMIT'] );
        }
        // 截取
        if (isset ( $condition ['OFFSET'] ) && $condition ['OFFSET']) {
            $activeQuery->offset ( $condition ['OFFSET'] );
        }
        if (isset ( $condition ['INDEX'] ) && $condition ['INDEX']) {
            $activeQuery->indexBy ( $condition ['INDEX'] );
        }

        // 查询条件
        $activeQuery = self::_batchCondition ( $activeQuery, $class->tableName (), $condition );
        // 调试用
        if (isset ( $condition ['DEBUG'] ) && $condition ['DEBUG']) {
            $sql = $activeQuery->createCommand ()->getRawSql ();
            die ( $sql );
        }
                
        $list = $activeQuery->asArray ()->all ();
        return $list;
    }
    
    /**
     * 获取单条数据
     */
    static public function baseFindRow($class, $condition) {
        // 初始化ActiveQuery::find();
        $activeQuery = $class->find ();
        
        // 查询字段
        if (isset ( $condition ['SELECT'] ) && $condition ['SELECT']) {
            $activeQuery->select ( $condition ['SELECT'] );
        }
        // 关联表
        if (isset ( $condition ['WITH'] ) && $condition ['WITH']) {
            // 判断是否立即加载 默认否
            if (isset ( $condition ['WITHEAGER'] ) && $condition ['WITHEAGER']) {
                $activeQuery->joinWith ( $condition ['WITH'], $condition ['WITHEAGER'] );
            } else {
                $activeQuery->joinWith ( $condition ['WITH'], false );
            }
        }
        // 分组条件
        if (isset ( $condition ['GROUP'] ) && $condition ['GROUP']) {
            $activeQuery->groupBy ( $condition ['GROUP'] );
        }
        // 分组条件
        if (isset ( $condition ['HAVING'] ) && $condition ['HAVING']) {
            $activeQuery->having ( $condition ['HAVING'] );
        }
        // 排序条件
        if (isset ( $condition ['ORDER'] ) && $condition ['ORDER']) {
            $activeQuery->orderBy ( $condition ['ORDER'] );
        }
        
        $activeQuery->limit ( 1 ); // findRow 只需要返回单条数据
                                   
        // 查询条件
        $activeQuery = self::_batchCondition ( $activeQuery, $class->tableName (), $condition );
        
        // 调试用
        if (isset ( $condition ['DEBUG'] ) && $condition ['DEBUG']) {
            $sql = $activeQuery->createCommand ()->getRawSql ();
            die ( $sql );
        }
        $row = $activeQuery->asArray ()->one ();
        return $row;
    }
    
    /**
     * 获取翻页数据
     */
    static public function baseFindPage($class, $condition = []) {
        // 初始化ActiveQuery::find();
        $activeQuery = $class->find ();
        
        // 关联表
        if (isset ( $condition ['WITH'] ) && $condition ['WITH']) {
            // 判断是否立即加载 默认否
            if (isset ( $condition ['WITHEAGER'] ) && $condition ['WITHEAGER']) {
                $activeQuery->joinWith ( $condition ['WITH'], $condition ['WITHEAGER'] );
            } else {
                $activeQuery->joinWith ( $condition ['WITH'], false );
            }
        }
        // 分组条件
        if (isset ( $condition ['GROUP'] ) && $condition ['GROUP']) {
            $activeQuery->groupBy ( $condition ['GROUP'] );
        }
        // 分组条件
        if (isset ( $condition ['HAVING'] ) && $condition ['HAVING']) {
            $activeQuery->having ( $condition ['HAVING'] );
        }
        
        // 查询条件
        $activeQuery = self::_batchCondition ( $activeQuery, $class->tableName (), $condition );
        // 翻页数据
        $pageParams = isset ( $condition ['PAGEPARAMS'] ) ? $condition ['PAGEPARAMS'] : [ ];
        $dataCount = isset ( $condition ['DATACOUNT'] ) ? $condition ['DATACOUNT'] : '';
        
        $primaryKeyArray = $class->primaryKey ();
        $primaryKey = reset ( $primaryKeyArray );
        if(isset ( $condition ['COUNTSELECT'] )){
            $activeQuery->select ( $condition ['COUNTSELECT'] );
        } else {
            $activeQuery->select ( $class->tableName () . '.' . $primaryKey );
        }
        $dataCount = $activeQuery->count ();
        if (isset ( $condition ['DEBUG'] ) && $condition ['DEBUG']) {
            $sql = $activeQuery->createCommand ()->getRawSql ();
            echo ( $sql.'<br><br>' );
        }
        $pagination = self::_loadPagination ( $activeQuery, $condition ['PAGE'], $condition ['PAGESIZE'], $pageParams, $dataCount );
        
        // Page 方法 在 _loadPagination 方法前 先count一下 再重新赋值select
        if (isset ( $condition ['SELECT'] ) && $condition ['SELECT']) {
            $activeQuery->select ( $condition ['SELECT'] );
        }
        // 排序条件
        if (isset ( $condition ['ORDER'] ) && $condition ['ORDER']) {
            $activeQuery->orderBy ( $condition ['ORDER'] );
        }
        $activeQuery->offset ( $pagination->offset )->limit ( $pagination->limit );
        
        // 调试用
        if (isset ( $condition ['DEBUG'] ) && $condition ['DEBUG']) {
            $sql = $activeQuery->createCommand ()->getRawSql ();
            die ( $sql );
        }
        $list = $activeQuery->asArray ()->all ();

        $return ['dataCount'] = $pagination->totalCount;
        $return ['pageCount'] = $pagination->pageCount;
        $return ['currentPage'] = $condition ['PAGE'];
        
        // 判断是否需要显示linkPager PC 需要
        if (isset ( $condition ['PAGELINK'] )) {
            $return ['linkPager'] = LinkPager::widget ( [ 
                    'pagination' => $pagination,
                    'nextPageLabel' => \Yii::$app->params ['nextPageLabel'],
                    'prevPageLabel' => \Yii::$app->params ['prevPageLabel'] 
            ] );
        }
        
        $return ['list'] = $list;
        
        return $return;
    }
    
    /**
     * 统计数据
     */
    static public function baseFindCount($class, $condition) {
        // 初始化ActiveQuery::find();
        $activeQuery = $class->find ();

        // 关联表
        if (isset ( $condition ['WITH'] ) && $condition ['WITH']) {
            $activeQuery->joinWith ( $condition ['WITH'], false );
        }
        
        // 查询条件
        $activeQuery = self::_batchCondition ( $activeQuery, $class->tableName (), $condition );

        $condition ['COUNTSELECT'] = 'COUNT(1) AS count';
        // 调试用
        if (isset ( $condition ['DEBUG'] ) && $condition ['DEBUG']) {
            $sql = $activeQuery->createCommand ()->getRawSql ();
            die ( $sql );
        }

        $activeQuery->select ( $condition ['COUNTSELECT'] );

//        $count = $activeQuery->count ();

        $row = $activeQuery->asArray ()->one ();
//        Functions::dumpData($row);
        return $row['count'];
    }
    
    /**
     * 保存单条数据 需要验证
     */
    static public function baseSaveRow($class, $primaryKey, $attributes, $scenario = '') {
        if ($primaryKey && isset ( $attributes [$primaryKey] ) && $attributes [$primaryKey]) {
            // 修改
            $model = $class->findOne ( $attributes [$primaryKey] );
            $model->isNewRecord = false;
        } else {
            // 新建
            $model = $class;
            $model->isNewRecord = true;
        }
        
        if ($scenario) {
            $model->scenario = $scenario;
        }
        
        $model->setAttributes ( $attributes ); // 批量赋值属性
        if ($model->validate ()) {
            $model->save ();
            
            if ($primaryKey) {
                $primaryKeyValue = $model->primaryKey; // 主键ID
                $return [$primaryKey] = $primaryKeyValue;
            }
            
            $return ['status'] = 0;
        } else {
            $return ['status'] = 1;
            $return ['msg'] = Functions::getModelError ( $model->errors );
        }
        
        return $return;
    }
    
    /**
     * 更新单条数据 不用验证
     */
    static public function baseUpdateRow($class, $condition, $attributes) {
        $model = $class->findOne ( $condition );
        if ($model) {
            foreach ( $attributes as $key => $val ) {
                $model->$key = $val;
            }
            
            return $model->update ( false );
        } else {
            return false;
        }
    }
    
    /**
     * 单条数据设为无效 默认 is_valid 设置为 0
     */
    static public function baseInvalidRow($class, $condition) {
        $model = $class->findOne ( $condition );
        $model->is_valid = 0;
        
        return $model->update ( false );
    }
    
    /**
     * 单条数据物理删除
     */
    static public function baseDeleteRow($class, $condition) {
        $model = $class->findOne ( $condition );
        if ($model == null) {
            return;
        }
        return $model->delete ();
    }
    
    /**
     * 批量查询条件解析赋值
     *
     * @param $activeQuery 查询对象            
     * @param $tableName 查询表名            
     * @param $condition 条件数组            
     * @return $activeQuery object
     */
    static private function _batchCondition($activeQuery, $tableName, $condition) {
        $SQLoperator = ''; // SQL 操作符
        $SQLfield = ''; // SQL 字段
        
        foreach ( $condition as $key => $val ) {
            // 判断是否存在#
            if (strpos ( $key, '#' ) && $val !== null && $val !== '' && $val !== false && $val !== [ ]) {
                $arrKey = explode ( '#', $key ); // 解析条件
                switch ($arrKey [0]) {
                    case 'EQ' :
                        $SQLoperator = '=';
                        break;
                    case 'NEQ' :
                        $SQLoperator = '!=';
                        break;
                    case 'GT' :
                        $SQLoperator = '>';
                        break;
                    case 'LT' :
                        $SQLoperator = '<';
                        break;
                    case 'GTE' :
                        $SQLoperator = '>=';
                        break;
                    case 'LTE' :
                        $SQLoperator = '<=';
                        break;
                    case 'LIKE' :
                        $SQLoperator = 'LIKE';
                        break;
                    case 'NLIKE' :
                        $SQLoperator = 'NOT LIKE';
                        break;
                    case 'REGEXP' :
                        $SQLoperator = 'REGEXP';
                        break;
                    case 'IN' :
                        $SQLoperator = 'IN';
                        break;
                    case 'NIN' :
                        $SQLoperator = 'NOT IN';
                        break;
                }
                
                // 判断是否存在 点 号
                if (strpos ( $arrKey [1], '.' )) {
                    list ( $joinTableName, $joinTableField ) = explode ( '.', $arrKey [1] );
                    $SQLfield = "`{$joinTableName}`.`{$joinTableField}`";
                } else {
                    $SQLfield = "`{$tableName}`.`{$arrKey[1]}`";
                }
                
                if (in_array ( $SQLoperator, [ 
                        'IN',
                        'NOT IN' 
                ] )) {
                    // andWhere $val 数组太大 生成SQL很慢
                    if (is_array ( $val )) {
                        $SQLstring = "'" . implode ( "','", $val ) . "'";
                        $activeQuery->andWhere ( "{$SQLfield} {$SQLoperator} ({$SQLstring})" );
                    } else {
                        $activeQuery->andWhere ( "{$SQLfield} {$SQLoperator} ('{$val}')" );
                    }
                } else {
                    $activeQuery->andWhere ( [ 
                            // 使用链接符
                            $SQLoperator,
                            // 查询字段
                            $SQLfield,
                            // 查询值
                            $val 
                    ] );
                }
            } elseif ($key == 'CUSTOM') { // 关键字为CUSTOM 自定义查询条件
                if (is_array ( $val ) ) {
                    if (is_array ( $val ['param'] ) ) {
                        $isNull = 1;
                        foreach ( $val ['param'] as $pkey => $pval ) {
                            if($pval){
                                $isNull = 0;
                                $val ['condition'] = str_replace ( "{param-{$pkey}}", $pval, $val ['condition'] );
                            }
                        }
                        if(!$isNull)
                            $activeQuery->andWhere ( $val ['condition'] );
                    } else {
                        if(!is_array ( $val ['param']) && $val ['param']){
                            $val ['condition'] = str_replace ( '{param}', $val ['param'], $val ['condition'] );
                            $activeQuery->andWhere ( $val ['condition'] );
                        }
                    }

                } else {
                    $activeQuery->andWhere ( $val );
                }
            } elseif (strpos ( $key, '@' ) && $val !== null && $val !== '' && $val !== false && $val !== [ ]) {
                $keyArray = explode ( '@', $key ); // 解析条件
                switch ($keyArray [0]) {
                    case 'IS' : // 判断字段 0: = '' 或者 1: != ''
                        if ($val == '0') {
                            $SQLoperator = '=';
                        } elseif ($val == '1') {
                            $SQLoperator = '!=';
                        }
                        
                        $SQLval = '';
                        break;
                    
                    case 'NULL' : // 判断字段 0: IS NULL 或者 1: IS NOT NULL
                        if ($val == '0') {
                            $SQLoperator = 'IS';
                        } elseif ($val == '1') {
                            $SQLoperator = 'IS NOT';
                        }
                        
                        $SQLval = NULL;
                        break;
                }
                
                // 判断是否存在 .
                if (strpos ( $keyArray [1], '.' )) {
                    $SQLfield = $keyArray [1];
                } else {
                    $SQLfield = "{$tableName}.{$keyArray[1]}";
                }
                
                $activeQuery->andWhere ( [ 
                        // 使用链接符
                        $SQLoperator,
                        // 查询字段
                        $SQLfield,
                        // 查询值
                        $SQLval 
                ] );
            }
        }
        
        // \app\components\Functions::dumpData ( $activeQuery );
        
        return $activeQuery;
    }
    
    /**
     * 读取翻页数据
     *
     * @param $activeQuery 查询对象            
     * @param $currentPage 当前页数            
     * @param $pageSize 每页条数            
     * @param $urlParams get参数            
     * @param $dataCount 总记录数
     *            外部传进来的话 不用计算 $activeQuery->count ()
     * @return $pages object
     */
    static private function _loadPagination($activeQuery, $currentPage, $pageSize, $urlParams, $dataCount) {
        $pagination = new Pagination ( [ 
                'page' => intval ( $currentPage ) - 1,
                'pageSize' => $pageSize,
                'totalCount' => $dataCount,
                'params' => $urlParams 
        ] );
        
        return $pagination;
    }
    
    /**
     * merge 条件
     */
    public function mergeCondition($condition) {
        return $condition;
    }
    
    /**
     * 列表数据
     */
    public function findList($condition = []) {
        $condition = $this->mergeCondition ( $condition );
        return self::baseFindList ( $this, $condition );
    }
    
    /**
     * 单条数据
     */
    public function findRow($condition) {
        $condition = $this->mergeCondition ( $condition );
        return self::baseFindRow ( $this, $condition );
    }
    
    /**
     * 翻页数据
     */
    public function findPage($condition) {
        $condition = $this->mergeCondition ( $condition );
        return self::baseFindPage ( $this, $condition );
    }
    
    /**
     * 统计数据
     */
    public function findCount($condition = []) {
        $condition = $this->mergeCondition ( $condition );
        return self::baseFindCount ( $this, $condition );
    }
    
    /**
     * 保存数据 需要验证
     */
    public function saveRow($attributes, $scenario = '') {
        return self::baseSaveRow ( $this, current ( self::primaryKey () ), $attributes, $scenario );
    }
    
    /**
     * 更新不用验证的属性
     */
    public function updateRow($condition, $attributes) {
        return self::baseUpdateRow ( $this, $condition, $attributes );
    }
    
    /**
     * 单条数据设为无效 逻辑删除
     */
    public function invalidRow($condition) {
        return self::baseInvalidRow ( $this, $condition );
    }
    
    /**
     * 删除单条数据 物理删除
     */
    public function deleteRow($condition) {
        return self::baseDeleteRow ( $this, $condition );
    }
    
    /**
     * 批量insert数据
     */
    public function insertBatch($collectionList, $debug = false) {
        $insertIdArray = [ ]; // 批量插入的ID的数组
        $ceilSize = 5000;
        
        $dbSelect=\Yii::$app->params ['dbSelect'];
        
        if ($collectionList) {
            $attributesList = array_keys ( reset ( $collectionList ) );
            $count = count ( $collectionList );
            $ceilCount = ceil ( $count / $ceilSize );
            
            for($i = 0; $i < $ceilCount; $i ++) {
                $offset = $i * $ceilSize;
                $length = $ceilSize;
                $currentList = array_slice ( $collectionList, $offset, $length );
                
                \Yii::getLogger ()->flushInterval = 5; // https://github.com/yiisoft/yii2/issues/6472 batchInsert 内存泄漏
                                                       
                // INSERT 一次插入多行 返回插入数量
                if ($debug) {
                    if($dbSelect=='yht'){
                        $sql = \Yii::$app->yhtdb->createCommand ()->batchInsert ( $this->tableName (), $attributesList, $currentList )->getRawSql ();
                    }elseif($dbSelect=='main'){
                        $sql = \Yii::$app->maindb->createCommand ()->batchInsert ( $this->tableName (), $attributesList, $currentList )->getRawSql ();
                    }else{
                        $sql = \Yii::$app->companydb->createCommand ()->batchInsert ( $this->tableName (), $attributesList, $currentList )->getRawSql ();
                    }
                    die ( $sql );
                }
                if($dbSelect=='yht'){
                    $rowCount = \Yii::$app->yhtdb->createCommand ()->batchInsert ( $this->tableName (), $attributesList, $currentList )->execute ();
                }elseif($dbSelect=='main'){
                    $rowCount = \Yii::$app->maindb->createCommand ()->batchInsert ( $this->tableName (), $attributesList, $currentList )->execute ();
                }else{
                    $rowCount = \Yii::$app->companydb->createCommand ()->batchInsert ( $this->tableName (), $attributesList, $currentList )->execute ();
                }
                
                if($dbSelect=='yht'){
                    $lastInsertId = \Yii::$app->yhtdb->getLastInsertID (); // 返回批量插入 第一条数据插入后返回的的主键ID 比如：插入4,5,6,7 返回4
                }elseif($dbSelect=='main'){
                    $lastInsertId = \Yii::$app->maindb->getLastInsertID (); // 返回批量插入 第一条数据插入后返回的的主键ID 比如：插入4,5,6,7 返回4
                }else{
                    $lastInsertId = \Yii::$app->companydb->getLastInsertID (); // 返回批量插入 第一条数据插入后返回的的主键ID 比如：插入4,5,6,7 返回4
                }
                
                for($j = $lastInsertId; $j < ($lastInsertId + $rowCount); $j ++) {
                    $insertIdArray [] = $j;
                }
                
                // echo "lastInsertId = {$lastInsertId} \n";
                // echo "rowCount = {$rowCount} \n";
            }
        }
        
        // var_dump ( $insertIdArray );
        
        return $insertIdArray;
    }
} 

\src\extend\ExtendActiveRecordModel.php