<?php // model 基本模型类 namespace appextend; use yiidataPagination; use yiiwidgetsLinkPager; use appcomponentsFunctions; class ExtendActiveRecordModel extends yiidbActiveRecord { /** * 重载 读取数据库配置 */ 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 ] ); } } // appcomponentsFunctions::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} "; // echo "rowCount = {$rowCount} "; } } // var_dump ( $insertIdArray ); return $insertIdArray; } }
srcextendExtendActiveRecordModel.php