ngds/common/components/ActiveRecord.php

<?php
/**
 * Created by PhpStorm.
 * User: Leo
 * Date: 2017/9/3
 * Time: 下午9:56
 */

namespace common\components;

use common\helpers\snowflake\SnowFake;
use Yii;
use yii\base\ErrorException;
use common\helpers\Date;
use common\helpers\Tool;
use common\models\PerfOrder;
use yii\data\Pagination;
use yii\db\Exception;
use yii\db\Expression;
use yii\db\Migration;
use yii\helpers\ArrayHelper;

/**
 * Class ActiveRecord
 * @package common\components
 */
class ActiveRecord extends \yii\db\ActiveRecord {

    /**
     * 最后一次执行的SQL
     * @var null
     */
    public static $rawSql = null;
    /**
     * Query对象
     * @var null
     */
    public static $query = null;
    /**
     * @var array
     */
    public static $argv = [];

    public function behaviors()
    {
        $behaviors = parent::behaviors(); // TODO: Change the autogenerated stub
        $behaviors[] = \common\behaviors\PrimaryKeyBehavior::class;

        return $behaviors;
    }

    /**
     * 重写find方法达到强制使用主库还是从库的问题
     * @param bool $enableSlaves
     * @param string $fromDb
     * @return ActiveQuery
     */
    public static function find(bool $enableSlaves = false, string $fromDb = '')
    {
        $modelClass = get_called_class();
        if(class_exists($modelClass) && method_exists($modelClass, 'getDb')){
            $db = $modelClass::getDb();
            $enableSlavesTemp = $db->enableSlaves;
            $db->enableSlaves = $enableSlaves;
            return \Yii::createObject(ActiveQuery::class, [get_called_class(), ['oriEnableSlaves' => $enableSlavesTemp, 'fromDb' => $fromDb]]);
        } else {
            return parent::find();
        }
    }

    /**
     * 使用主库查询数据
     * @return ActiveQuery
     */
    public static function findUseMaster(){
        return self::find(false);
    }

    /**
     * 使用从库查询数据
     * @return ActiveQuery
     */
    public static function findUseSlaves(){
//        return self::find(true);
        return self::find(false);
    }

    /**
     * 从其他库查询数据
     * @param string $fromDb
     * @return ActiveQuery
     */
    public static function findUseOtherDb(string $fromDb){
        return self::find(false, $fromDb);
    }

    /**
     * 从奖金专属结算库查询数据
     * @return ActiveQuery
     */
    public static function findUseDbCalc(){
        return self::findUseOtherDb('dbCalc');
    }

    /**
     * 以数组的方式获取一条数据
     * @param string $condition
     * @param array $params
     * @param null $select
     * @return array|null
     */
    public static function findOneAsArray($condition='', $params= [], $select=null){
        $query = static::find()->where($condition, $params)->asArray();
        if($select){
            $query->select($select);
        }
        return $query->one();
    }

    /**
     * 已数组形式获取所有数据
     * @param string $condition
     * @param array $params
     * @param null $select
     * @return array|null
     */
    public static function findAllAsArray($condition='', $params= [], $select=null){
        $query = static::find()->where($condition, $params)->asArray();
        if($select){
            $query->select($select);
        }
        return $query->all();
    }

    /**
     * 插入一条数据
     * @param array $insertData
     * @param null $tableName
     * @throws Exception
     */
    public static function insertOne(array $insertData, $tableName = null){
        $modelClass = get_called_class();
        if($tableName === null){
            $tableName = $modelClass::tableName();
        }
        $db = $modelClass::getDb();
        if(!empty($insertData)){
            if( !isset($insertData['ID']) ) $insertData['ID'] = SnowFake::instance()->generateId();
            if(!$db->createCommand()->insert($tableName, $insertData)->execute()){
                throw new Exception($tableName.'表添加一条数据失败');
            }
        }
    }

    /**
     * 批量添加数据
     * @param array $insertData
     * @param null $tableName
     * @param string $db
     * @throws Exception
     */
    public static function batchInsert(array $insertData, $tableName = null, $db = 'db'){
        $modelClass = get_called_class();
        if($tableName === null){
            $tableName = $modelClass::tableName();
        }
        if(!empty($insertData)){
            $insertKey = [];
            foreach($insertData[0] as $key=>$data){
                $insertKey[] = $key;
            }
            if( !in_array('ID', $insertKey ) ) {
                array_push($insertKey, 'ID');
                foreach ($insertData as & $everyData) {
                    $everyData['ID'] = SnowFake::instance()->generateId();
                }
            }
            if(!\Yii::$app->$db->createCommand()->batchInsert($tableName, $insertKey, $insertData)->execute()){
                throw new Exception($tableName.'表批量添加数据失败');
            }
        }
    }

    /**
     * 更新全部带着表名
     * @param $attributes
     * @param string $condition
     * @param array $params
     * @param null $tableName
     * @param string $db
     * @return mixed
     */
    public static function batchUpdate($attributes, $condition = '', $params = [], $tableName = null, $db = 'db') {
        $modelClass = get_called_class();
        if($tableName === null){
            $tableName = $modelClass::tableName();
        }
        $command = \Yii::$app->$db->createCommand();
        $command->update($tableName, $attributes, $condition, $params);
        return $command->execute();
    }

    /**
     * @param string $condition
     * @param array $params
     * @param array $argv
     * @param bool $isList
     */
    public static function prepare($condition = '', $params = [], $argv = [], $isList=false){
        $default = [
            'page' => null,
            'pageSize'=>0, //10
            'orderBy'=>'CREATED_AT DESC',
            'with'=>null,
            'select'=>null,
            'asArray'=>true,
            'joinWith'=>null,
            'from'=>null,
            'join'=>null,
            'yearMonth'=>null,
            'useSlaves' => false,
            'count' => '*',
        ];
        self::$argv = Tool::deepParse($argv ,$default);

        if (!self::$argv['pageSize'] && $isList) {
            $pageSize = \Yii::$app->request->all('pageSize', \Yii::$app->params['pageSize']);
            self::$argv['pageSize'] = $pageSize;
        }
        if ($condition == '') {
            $condition = '1=1';
            $params = [];
        } else {
            $condition = '1=1 ' . $condition;
        }
        if(self::$argv['useSlaves']){
            self::$query = self::findUseSlaves();
        } else {
            self::$query = self::findUseMaster();
        }
        if(self::$argv['select']){
            self::$query->select(self::$argv['select']);
        }
        if(self::$argv['with']){
            self::$query->with(self::$argv['with']);
        }
        if(self::$argv['joinWith']){
            self::$query->joinWith(self::$argv['joinWith']);
        }
        if(self::$argv['from']){
            self::$query->from(self::$argv['from']);
        }
        if(self::$argv['yearMonth']){
            if(is_array(self::$argv['yearMonth'])){
                self::$query->yearMonth(self::$argv['yearMonth'][0], self::$argv['yearMonth'][1]);
            } else {
                self::$query->yearMonth(self::$argv['yearMonth']);
            }
        }
        if(self::$argv['join']){
            if(!is_array(self::$argv['join'][0])){
                self::$query->join(self::$argv['join'][0], self::$argv['join'][1], self::$argv['join'][2]);
            } else {
                foreach(self::$argv['join'] as $value){
                    self::$query->join($value[0], $value[1], $value[2]);
                }
            }
        }
        self::$query->where($condition, $params);
        if(self::$argv['orderBy']){
            self::$query->orderBy(self::$argv['orderBy']);
        }
        unset($condition, $params, $argv, $default);
    }

    /**
     * 获取列表
     * @param string $condition
     * @param array $params
     * @param array $argv
     * @return array
     */
    public static function lists($condition = '', $params = [], $argv = []) {
        $limit = $argv['limit'] ?? 0;
        self::prepare($condition, $params, $argv, true);
        unset($condition, $params, $argv);
        $countQuery = clone self::$query;
        $count = $countQuery->count(self::$argv['count']); // 得到总数

        $pagination = new Pagination(['totalCount' => $count]);
        $pagination->setPageSize(self::$argv['pageSize']);
        if(self::$argv['page'] !== null){
            $pagination->setPage(self::$argv['page']);
        }
        self::$query->offset($pagination->offset)->limit($limit ?: $pagination->limit);
        if(self::$argv['asArray']){
            self::$query->asArray();
        }
        $lists = self::$query->all();
        
        self::$query = null;
        unset($countQuery);
        $startNum = $pagination->page * $pagination->pageSize + 1;
        return [
            'list' => $lists ?: [],
            'pagination' => $pagination,
            'currentPage'=>$pagination->page,
            'totalPages'=>$pagination->pageCount,
            'startNum' => $startNum,
            'totalCount' => intval($pagination->totalCount),
            'pageSize' => $pagination->pageSize,
        ];
    }

    /**
     * 是否存在某个表
     * @param $tableName
     * @param string $db
     * @return mixed
     */
    public static function isExistsTable($tableName, $db='db'){
        if(preg_match('/^\{\{\%(\w+)\}\}$/', $tableName)){
            $tableName = preg_replace('/^\{\{\%(\w+)\}\}$/', \Yii::$app->$db->tablePrefix.'${1}', $tableName);
        }
        \Yii::$app->$db->getDriverName();
//        $table=\Yii::$app->$db->createCommand("select count(*) AS EXI from User_Tables where table_name = '{$tableName}'")->queryOne();
        $tableNames = \Yii::$app->$db->getSchema()->getTableNames();
        return in_array($tableName, $tableNames);
    }

    /**
     * 删除表
     * @param $tableName
     * @param string $db
     * @return mixed
     */
    public static function deleteTable($tableName, $db='db'){
        return \Yii::$app->$db->createCommand("DROP TABLE {$tableName}")->execute();
    }

    /**
     * 获取表中的所有字段
     * @param $modelClassName
     * @return array
     */
    public static function getAllFields($modelClassName){
        $table = $modelClassName::tableName();
        $tableSchema = \Yii::$app->db->schema->getTableSchema($table);
        return $fields = ArrayHelper::getColumn($tableSchema->columns, 'name', false);
    }

    /**
     * 表中是否存在某字段
     * @param $modelClassName
     * @param $field
     * @return bool
     */
    public static function isExistsField($modelClassName, $field){
        $allFields = self::getAllFields($modelClassName);
        return in_array($field, $allFields);
    }

    /**
     * 分区表的按月的分区表名
     * @param $yearMonth
     * @param $format
     * @return Expression
     */
    public static function yearMonthPartName($yearMonth, $format=Date::OCI_TIME_FORMAT_SHORT_MONTH){
        $modelClass = get_called_class();
        return new Expression($modelClass::tableName()." PARTITION FOR(TO_DATE('$yearMonth'".','."'$format'))");
    }


    /**
     * 通过一张模板表创建一个新的表
     * @param $createTableName
     * @param $fromTableName
     * @param array $indexes
     * @param string $creatDb
     * @param string $fromDb
     */
    public static function createTableFromTable($createTableName, $fromTableName, $indexes = [], $creatDb='db', $fromDb='db'){
        $fromTableSchema = \Yii::$app->$fromDb->getTableSchema($fromTableName);
        if($fromTableSchema){
            $mir = new Migration();
            $mir->db = \Yii::$app->$creatDb;
            $mir->compact = true;
            $newColumn = [];
            foreach($fromTableSchema->columns as $column){
                $tempStr = $column->dbType;
                if($column->defaultValue !== null && strtoupper($column->dbType) == 'varchar'){
                    $tempStr .= ' DEFAULT '."'".trim($column->defaultValue, "'")."'";
                } elseif ($column->defaultValue !== null && strtoupper($column->dbType) == 'int') {
                    $tempStr .= ' DEFAULT '.$column->defaultValue;
                }elseif ($column->defaultValue !== null && strtoupper($column->dbType) == 'decimal') {
                    $tempStr .= ' DEFAULT '.$column->defaultValue;
                }
                if($column->allowNull){
                    $tempStr .= '';
                } else {
                    $tempStr .= ' NOT NULL';
                }
                if($column->isPrimaryKey){
                    $tempStr .= ' PRIMARY KEY';
                }
                $newColumn[$column->name] = $tempStr;
                unset($tempStr);
            }
            $mir->createTable($createTableName, $newColumn);
            if(!empty($indexes)){
                foreach($indexes as $index){
                    [
                        'name' => $name,
                        'table' => $table,
                        'columns' => $columns,
                        'unique' => $unique,
                    ] = $index;
                    $unique = $unique ? $unique : false;
                    $mir->createIndex($name, $table, $columns, $unique);
                }
            }
        }


    }


    /**
     * 删除数据
     * @param string $where
     * @return bool
     */
    public static function pageDeleteAll($where='') {
        $limit = 10000;
        $sql = sprintf('DELETE FROM %s WHERE %s LIMIT %d', strtoupper(self::tableName()), $where, $limit);
        $affectRow = self::getDb()->createCommand($sql)->execute();
        if( $affectRow == $limit ) {
            unset($limit, $sql, $affectRow);
            return self::pageDeleteAll($where);
        }

        unset($limit, $sql, $affectRow);
        return true;
    }
}