aklast/common/helpers/Excel.php

<?php
/**
 * Created by PhpStorm.
 * User: leo
 * Date: 2018/5/17
 * Time: 上午11:34
 */

namespace common\helpers;

use common\helpers\http\RemoteUploadApi;
use common\models\ExcelAddUser;
use common\models\ExcelChangeBalance;
use common\models\ExcelImport;
use common\models\ExcelRegInfo;
use common\models\ExcelWithdrawPaidFalse;
use common\models\Export;
use common\models\ExportFile;
use common\models\forms\AdminAddUserForm;
use common\models\forms\ChangeBalanceForm;
use common\models\forms\ExcelOrderDecForm;
use common\models\forms\ExcelOrderShopForm;
use common\models\forms\ExcelOrderStandardForm;
use common\models\forms\RegInfoAuditForm;
use common\models\forms\WithdrawForm;
use common\models\Uploads;
use common\models\UserNetwork;
use common\models\User;
use yii\base\BaseObject;
use yii\base\Exception;
use yii\base\StaticInstanceTrait;

class Excel extends BaseObject {
    use StaticInstanceTrait;

    const CHILD_PATH = 'excel_export';

    public $isRemote = true;
    public $uploadInfo;
    public $exportInfo;

    private $_errors = [];
    const EXCEL_STRUCTURE = [
        'addUser' => [
            'formClass' => AdminAddUserForm::class,
            'formScenario' => 'add',
            'formAction' => 'edit',
            'errorTipField' => 'USER_NAME',
            'excelTableClass' => ExcelAddUser::class,
            'excelTableField' => [
                '序号' => 'SORT',
                '会员编号' => 'USER_NAME',
                '会员姓名' => 'REAL_NAME',
                '会员级别' => 'DEC_LV',
                '身份证' => 'ID_CARD',
                '手机' => 'MOBILE',
                '备用手机号' => 'TEL',
                '省/市' => 'AREA_PROVINCE',
                '市/区' => 'AREA_CITY',
                '区/县' => 'AREA_COUNTY',
                '身份证地址' => 'ADDRESS',
                '开户银行' => 'OPEN_BANK',
                '开户行地址' => 'BANK_ADDRESS',
                '银行账号' => 'BANK_NO',
                '银行 省/市' => 'BANK_PROVINCE',
                '银行 市/区' => 'BANK_CITY',
                '银行 区/县' => 'BANK_COUNTY',
                '是否报单中心' => 'IS_DEC',
                '报单中心级别' => 'DEC_ROLE',
                '所属报单中心编号' => 'DEC_USER_NAME',
                '生日' => 'BIRTHDAY',
                '接点人' => 'CON_USER_NAME',
                '开拓人' => 'REC_USER_NAME',
                '区位' => 'LOCATION',
            ],
            'formField' => [
                'USER_NAME' => [
                    'name' => 'userName',
                    'type' => 'common',
                ],
                'REAL_NAME' => [
                    'name' => 'realName',
                    'type' => 'common',
                ],
                'DEC_LV' => [
                    'name' => 'decLv',
                    'type' => 'table',
                    'table' => 'DECLARATION_LEVEL',
                    'field' => 'LEVEL_NAME',
                    'select' => 'ID',
                ],
                'ID_CARD' => [
                    'name' => 'idCard',
                    'type' => 'common',
                ],
                'MOBILE' => [
                    'name' => 'mobile',
                    'type' => 'common',
                ],
                'TEL' => [
                    'name' => 'tel',
                    'type' => 'common',
                ],
                'AREA_PROVINCE' => [
                    'name' => 'areaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=2',
                    'select' => 'REGION_CODE',
                ],
                'AREA_CITY' => [
                    'name' => 'areaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=3',
                    'select' => 'REGION_CODE',
                ],
                'AREA_COUNTY' => [
                    'name' => 'areaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=4',
                    'select' => 'REGION_CODE',
                ],
                'ADDRESS' => [
                    'name' => 'address',
                    'type' => 'common',
                ],
                'OPEN_BANK' => [
                    'name' => 'openBank',
                    'type' => 'table',
                    'table' => 'OPEN_BANK',
                    'field' => 'BANK_NAME',
                    'select' => 'BANK_CODE',
                ],
                'BANK_ADDRESS' => [
                    'name' => 'bankAddress',
                    'type' => 'common',
                ],
                'BANK_NO' => [
                    'name' => 'bankNo',
                    'type' => 'common',
                ],
                'BANK_PROVINCE' => [
                    'name' => 'bankAreaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=2',
                    'select' => 'REGION_CODE',
                ],
                'BANK_CITY' => [
                    'name' => 'bankAreaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=3',
                    'select' => 'REGION_CODE',
                ],
                'BANK_COUNTY' => [
                    'name' => 'bankAreaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=4',
                    'select' => 'REGION_CODE',
                ],
                'IS_DEC' => [
                    'name' => 'isDec',
                    'type' => 'bool',
                ],
                'DEC_ROLE' => [
                    'name' => 'decRoleId',
                    'type' => 'table',
                    'table' => 'DEC_ROLE',
                    'field' => 'ROLE_NAME',
                    'select' => 'ID',
                    'isAllowNull' => true,
                ],
                'DEC_USER_NAME' => [
                    'name' => 'decUserName',
                    'type' => 'common',
                ],
                'BIRTHDAY' => [
                    'name' => 'birthday',
                    'type' => 'common',
                ],
                'CON_USER_NAME' => [
                    'name' => 'conUserName',
                    'type' => 'common',
                ],
                'REC_USER_NAME' => [
                    'name' => 'recUserName',
                    'type' => 'common',
                ],
                'LOCATION' => [
                    'name' => 'location',
                    'type' => 'int',
                ],
            ]
        ],
        'withdrawPaidFalse' => [
            'formClass' => WithdrawForm::class,
            'formScenario' => 'excelPaidFalse',
            'formAction' => 'excelPaidFalse',
            'errorTipField' => 'SN',
            'excelTableClass' => ExcelWithdrawPaidFalse::class,
            'excelTableField' => [
                '序号' => 'SORT',
                '提现流水号' => 'SN',
                '提现期数' => 'WITHDRAW_PERIOD_NUM',
                '付款日期' => 'PAID_AT',
                '付款失败原因' => 'PAID_FAIL_REMARK',
                '会员编号' => 'USER_NAME',
                '会员姓名' => 'REAL_NAME',
                '实付金额' => 'AMOUNT',
                '实时开户名' => 'BANK_REAL_NAME',
                '实时银行账户' => 'BANK_NO',
            ],
            'formField' => [
                'SN' => [
                    'name' => 'sn',
                    'type' => 'common',
                ],
                'WITHDRAW_PERIOD_NUM' => [
                    'name' => 'withdrawPeriodNum',
                    'type' => 'common',
                ],
                'PAID_AT' => [
                    'name' => 'paidAt',
                    'type' => 'common',
                ],
                'PAID_FAIL_REMARK' => [
                    'name' => 'paidFailRemark',
                    'type' => 'common',
                ],
                'USER_NAME' => [
                    'name' => 'userName',
                    'type' => 'common',
                ],
                'REAL_NAME' => [
                    'name' => 'realName',
                    'type' => 'common',
                ],
                'AMOUNT' => [
                    'name' => 'amount',
                    'type' => 'common',
                ],
                'BANK_REAL_NAME' => [
                    'name' => 'bankRealName',
                    'type' => 'common',
                ],
                'BANK_NO' => [
                    'name' => 'bankNo',
                    'type' => 'common',
                ],
            ]
        ],
        'changeBalance' => [
            'formClass' => ChangeBalanceForm::class,
            'formScenario' => 'excelChangeBalance',
            'formAction' => 'excelChangeBalance',
            'errorTipField' => 'USER_NAME',
            'excelTableClass' => ExcelChangeBalance::class,
            'excelTableField' => [
                '序号' => 'SORT',
                '会员编号' => 'USER_NAME',
                '会员姓名' => 'REAL_NAME',
                '账户类型' => 'TYPE',
                '交易类型' => 'DEAL_TYPE',
                '调整金额' => 'AMOUNT',
                '备注' => 'REMARK',
                '备注是否前台显示' => 'REMARK_IS_SHOW',
            ],
            'formField' => [
                'USER_NAME' => [
                    'name' => 'userName',
                    'type' => 'common',
                ],
                'REAL_NAME' => [
                    'name' => 'realName',
                    'type' => 'common',
                ],
                'TYPE' => [
                    'name' => 'type',
                    'type' => 'param',
                    'index' => 'bonusWalletType',
                ],
                'DEAL_TYPE' => [
                    'name' => 'dealType',
                    'type' => 'table',
                    'db' => 'db',
                    'table' => 'DEAL_TYPE',
                    'field' => 'TYPE_NAME',
                    'select' => 'ID',
                ],
                'AMOUNT' => [
                    'name' => 'amount',
                    'type' => 'common',
                ],
                'REMARK' => [
                    'name' => 'remark',
                    'type' => 'common',
                ],
                'REMARK_IS_SHOW' => [
                    'name' => 'isShow',
                    'type' => 'bool',
                ],
            ]
        ],
        'regInfo' => [
            'formClass' => RegInfoAuditForm::class,
            'formScenario' => 'excelAdd',
            'formAction' => 'excelAdd',
            'errorTipField' => 'USER_NAME',
            'excelTableClass' => ExcelRegInfo::class,
            'excelTableField' => [
                '序号' => 'SORT',
                '会员编号' => 'USER_NAME',
                '会员姓名' => 'REAL_NAME',
                '身份证' => 'ID_CARD',
                '注册类型' => 'REG_TYPE',
                '注册名称' => 'REG_NAME',
                '统一社会信用代码' => 'CREDIT_CODE',
                '经营场所' => 'PREMISES',
                '注册有效期' => 'REG_EXPIRES',
                '法人' => 'LEGAL_PERSON',
                '企业开户名称' => 'OPEN_NAME',
                '企业开户银行' => 'OPEN_BANK',
                '企业开户行地址' => 'BANK_ADDRESS',
                '企业银行账号' => 'BANK_NO',
                '企业银行 省/市' => 'BANK_PROVINCE',
                '企业银行 市/区' => 'BANK_CITY',
                '企业银行 区/县' => 'BANK_COUNTY',
                '备注' => 'REMARK',
            ],
            'formField' => [
                'USER_NAME' => [
                    'name' => 'userName',
                    'type' => 'common',
                ],
                'REAL_NAME' => [
                    'name' => 'realName',
                    'type' => 'common',
                ],
                'ID_CARD' => [
                    'name' => 'idCard',
                    'type' => 'common',
                ],
                'REG_TYPE' => [
                    'name' => 'type',
                    'type' => 'table',
                    'db' => 'db',
                    'table' => 'REG_TYPE',
                    'field' => 'TYPE_NAME',
                    'select' => 'ID',
                ],
                'REG_NAME' => [
                    'name' => 'regName',
                    'type' => 'common',
                ],
                'CREDIT_CODE' => [
                    'name' => 'creditCode',
                    'type' => 'common',
                ],
                'PREMISES' => [
                    'name' => 'premises',
                    'type' => 'common',
                ],
                'REG_EXPIRES' => [
                    'name' => 'regExpires',
                    'type' => 'common',
                ],
                'LEGAL_PERSON' => [
                    'name' => 'legalPerson',
                    'type' => 'common',
                ],
                'OPEN_NAME' => [
                    'name' => 'openName',
                    'type' => 'common',
                ],
                'OPEN_BANK' => [
                    'name' => 'openBank',
                    'type' => 'table',
                    'table' => 'OPEN_BANK',
                    'field' => 'BANK_NAME',
                    'select' => 'BANK_CODE',
                    'isAllowNull' => true,
                ],
                'BANK_ADDRESS' => [
                    'name' => 'bankAddress',
                    'type' => 'common',
                    'isAllowNull' => true,
                ],
                'BANK_NO' => [
                    'name' => 'bankNo',
                    'type' => 'common',
                    'isAllowNull' => true,
                ],
                'BANK_PROVINCE' => [
                    'name' => 'bankAreaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=2',
                    'select' => 'REGION_CODE',
                    'isAllowNull' => true,
                ],
                'BANK_CITY' => [
                    'name' => 'bankAreaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=3',
                    'select' => 'REGION_CODE',
                    'isAllowNull' => true,
                ],
                'BANK_COUNTY' => [
                    'name' => 'bankAreaSelected',
                    'isArray' => true,
                    'type' => 'table',
                    'table' => 'REGION',
                    'field' => 'REGION_NAME',
                    'where' => 'DEEP=4',
                    'select' => 'REGION_CODE',
                    'isAllowNull' => true,
                ],
                'REMARK' => [
                    'name' => 'createRemark',
                    'type' => 'common',
                ],
            ]
        ],
        'orderShop' => [
            'excelTableClass' => ExcelOrderShopForm::class,
            'excelTableAction' => 'run',
        ],
        'orderDec' => [
            'excelTableClass' => ExcelOrderDecForm::class,
            'excelTableAction' => 'run',
        ],
        'orderShopStandard' => [
            'excelTableClass' => ExcelOrderStandardForm::class,
            'excelTableAction' => 'run',
        ],
    ];

    public function init() {
        parent::init();
        $this->isRemote = \Yii::$app->params['isRemoteUpload'];
    }

    /**
     * 加入错误错误
     * @param $attr
     * @param $error
     */
    public function addError($attr, $error) {
        $this->_errors[$attr][] = $error;
    }

    /**
     * 获取错误信息
     * @return array
     */
    public function getErrors() {
        return $this->_errors;
    }

    /**
     * 导出到文件
     * @param $exportId
     * @param array $dataArr
     * @param array $columns
     * @param $fileName
     * @param callable|null $callBack
     * @return bool
     */
    public function exportToFile($exportId, array $dataArr, array $columns, $fileName, callable $callBack = null) {
        $result = true;
        $headers = $columns;
        $columns = array_keys($columns);
        $fileName = $fileName . '_export_' . date('YmdHis') . uniqid() . '.xlsx';
        $path = \Yii::getAlias('@common/runtime/' . self::CHILD_PATH . '/');
        if (!is_dir($path)) {
            mkdir($path, 0755);
        }
        try {
            \sunmoon\phpspreadsheet\Excel::export([
                'models' => $dataArr,
                'columns' => $columns,
                'headers' => $headers,
                'savePath' => $path,
                'asAttachment' => false,
                'format' => 'Xlsx',
                'fileName' => $fileName,
                'exit' => false,
                'exportIterationCallback' => $callBack,
            ]);
            // 把导出的文件上传至静态文件服务器
            if ($this->isRemote) {
                $remoteUploadApi = RemoteUploadApi::instance();
                if ($uploadResult = $remoteUploadApi->upload($path . $fileName)) {
                    $this->exportInfo = [
                        'fileName' => $uploadResult['name'],
                        'url' => $uploadResult['url'],
                        'fileSize' => $uploadResult['size'] ?? null,
                        'md5' => $uploadResult['md5'] ?? null,
                    ];
                } else {
                    throw new Exception('文件远程上传失败');
                }
                // 删除本地临时文件
                unlink($path . $fileName);
            } else {
                $this->exportInfo = [
                    'fileName' => $fileName,
                    'url' => $path . $fileName,
                    'fileSize' => null,
                    'md5' => null,
                ];
            }
            // 把文件对应的相关资料存入数据库中
            $uploads = new ExportFile();
            $uploads->FILE_NAME = $this->exportInfo['fileName'];
            $uploads->EXPORT_ID = $exportId;
            $uploads->URL = $this->exportInfo['url'];
            $uploads->FILE_SIZE = $this->exportInfo['fileSize'] ?? null;
            $uploads->MD5 = $this->exportInfo['md5'] ?? null;
            $uploads->CREATED_AT = Date::nowTime();
            if (!$uploads->save()) {
                throw new Exception(Form::formatErrorsForApi($uploads->getErrors()));
            }
        } catch (\Exception $e) {
            $this->addError('export', $e->getMessage());
            $result = false;
        }
        return $result;
    }

    /**
     * 过滤读取到的数据把空的未填写的行都删掉
     * @param $data
     * @return mixed
     */
    public static function filterData(&$data) {
        foreach ($data as $key => $value) {
            $isAllNull = true;
            foreach ($value as $k => $v) {
                if ($v !== null) {
                    $isAllNull = false;
                }
            }
            if ($isAllNull) {
                unset($data[$key]);
            }
        }
        return $data;
    }

    /**
     * 分页导入数据
     * @param $excelStructureKey
     * @param $excelImportId
     * @param int $rowCount
     * @param int $startRow
     * @param int $limit
     * @return int
     * @throws Exception
     */
    public function pageImportDataFromExcel($excelStructureKey, $excelImportId, $rowCount = 1000, $startRow = 1, $limit = 1000) {
        if ($startRow > 1) {
            $startRow = $startRow + 1;
            $limit = $limit - 1;
        }
        $fileNameArray = ExcelImport::find()->select('U.FILE_NAME')->from(ExcelImport::tableName() . ' AS ET')->join('LEFT JOIN', Uploads::tableName() . ' AS U', 'ET.UPLOAD_ID=U.ID')->where('ET.ID=:ID', [':ID' => $excelImportId])->asArray()->one();
        $filePath = \Yii::getAlias('@common/runtime/uploads/' . $fileNameArray['FILE_NAME']);
        if ($startRow > $rowCount) {
            return 0;
        }
        try {
            // 临时文件不存在则创建
            $tempFileName = \Yii::getAlias('@common/runtime/uploads/' . 'importTemp.txt');
            if (!file_exists($tempFileName)) {
                $fp = fopen($tempFileName, 'w+');
                fclose($fp);
            } else {
                if ($startRow == 1) {
                    file_put_contents($tempFileName, '');
                }
            }
            $data = \sunmoon\phpspreadsheet\Excel::import($filePath, [
                'setFirstRecordAsKeys' => true,
                'readStartRow' => $startRow,
                'readEndRow' => $startRow + $limit,
                'storeFile' => $tempFileName,
                'dropKeysRow' => $startRow == 1 ? false : true,
            ]);
        } catch (\Exception $e) {
            throw new Exception($e->getMessage());
        }
        self::filterData($data);


        if ($data) {

//            if($startRow != 1){
//                unset($data[0]);
//            }
//            if(!$data) return 0;
//            self::filterData($data);
            $this->importDataToExcelTable($excelStructureKey, $excelImportId, $data);
            return 1;
        } else {
            return 0;
        }
    }

    /**
     * Excel数据导入到待导入的表中
     * @param $excelStructureKey
     * @param $excelImportId
     * @param $excelData
     */
    public function importDataToExcelTable($excelStructureKey, $excelImportId, $excelData) {
        $tableClass = self::EXCEL_STRUCTURE[$excelStructureKey]['excelTableClass'];
        $tableIndex = self::EXCEL_STRUCTURE[$excelStructureKey]['excelTableField'];
        $insertData = [];

        foreach ($excelData as $data) {
            $oneInsertData = [
                'EXCEL_IMPORT_ID' => $excelImportId,
                'CREATED_AT' => Date::nowTime(),
            ];
            foreach ($data as $key => $value) {
                $oneInsertData[$tableIndex[$key]] = $value;
            }
            $insertData[] = $oneInsertData;
        }
        $tableClass::batchInsert($insertData);
    }

    /**
     * 分页从待导入表中导入数据到真实数据中
     * @param $excelStructureKey
     * @param $excelImportId
     * @param int $offset
     * @param int $limit
     * @return int
     * @throws Exception
     */
    public function pageImportDataFromExcelTable($excelStructureKey, $excelImportId, $offset = 0, $limit = 1000) {
        $tableClass = self::EXCEL_STRUCTURE[$excelStructureKey]['excelTableClass'];
        // 获取1000条数据
        $allData = $tableClass::find()->where('EXCEL_IMPORT_ID=:EXCEL_IMPORT_ID', [':EXCEL_IMPORT_ID' => $excelImportId])->orderBy('SORT ASC')->offset($offset)->limit($limit)->asArray()->all();
        if ($allData) {
            $this->importToDbWithForm($excelStructureKey, $allData);
            return 1;
        }
        return 0;
    }

    /**
     * 通过表单类导入
     * @param $excelStructureKey
     * @param $excelDatas
     * @throws Exception
     */
    public function importToDbWithForm($excelStructureKey, $excelDatas) {
        foreach ($excelDatas as $value) {
            $formClassName = self::EXCEL_STRUCTURE[$excelStructureKey]['formClass'];
            $formScenario = self::EXCEL_STRUCTURE[$excelStructureKey]['formScenario'];
            $formAction = self::EXCEL_STRUCTURE[$excelStructureKey]['formAction'];
            $formData = $this->excelDataToFormData($excelStructureKey, $value);
            if ($formData) {
                $form = new $formClassName();
                $form->scenario = $formScenario;
                foreach ($formData as $formKey => $formValue) {
                    $form->$formKey = $formValue;
                }
                $errorTipField = self::EXCEL_STRUCTURE[$excelStructureKey]['errorTipField'];
                $tableClass = self::EXCEL_STRUCTURE[$excelStructureKey]['excelTableClass'];

                if (!$form->$formAction()) {

                    // 失败信息写入到待导入数据库excel表中
                    $tableClass::updateAll(['STATUS' => 2, 'ERROR_REMARK' => Form::formatErrorsForApi($form->getErrors())], 'ID=:ID', [':ID' => $value['ID']]);
                    throw new Exception('错误序号【'.$value['SORT'].'】会员编号【'.$value['USER_NAME'].'】。导入' . $value[$errorTipField] . '发生错误，原因：' . Form::formatErrorsForApi($form->getErrors()));
                } else {
                    $tableClass::updateAll(['STATUS' => 1], 'ID=:ID', [':ID' => $value['ID']]);
                }
            }
        }
    }

    /**
     * excel数据转为表单可提交的数据
     * @param $excelStructureKey
     * @param $excelData
     * @return array
     * @throws Exception
     */
    public function excelDataToFormData($excelStructureKey, $excelData) {
        $formFieldArr = self::EXCEL_STRUCTURE[$excelStructureKey]['formField'];
        $excelTableField = self::EXCEL_STRUCTURE[$excelStructureKey]['excelTableField'];
        $sort = $excelData['SORT'];
        $result = [];
        foreach ($excelData as $key => $value) {
            if (in_array($key, ['ID', 'EXCEL_IMPORT_ID', 'STATUS', 'ERROR_REMARK', 'CREATED_AT', 'SORT', 'ROWNUMID'])) continue;
            if (!array_key_exists($key, $formFieldArr)) {
                throw new Exception('excel表格字段不符合要求，字段为:' . $key);
            }
            $resultKey = $formFieldArr[$key]['name'];
            // 这里要判断是否允许为空值，如果允许则可以直接付空值
            if (isset($formFieldArr[$key]['isAllowNull']) && $formFieldArr[$key]['isAllowNull'] && $value === null) {
                $result[$resultKey] = $value;
            } elseif ($formFieldArr[$key]['type'] === 'common') {
                $result[$resultKey] = $value;
            } elseif ($formFieldArr[$key]['type'] === 'bool') {
                $result[$resultKey] = intval($value == '是');
            } elseif ($formFieldArr[$key]['type'] === 'int') {
                $result[$resultKey] = intval($value);
            } elseif ($formFieldArr[$key]['type'] === 'param') {
                // 从param的数据中获取
                $tempArray = \Yii::$app->params[$formFieldArr[$key]['index']];
                if ($formFieldArr[$key]['name'] === 'nation') {
                    array_unshift($tempArray, [
                        'id' => '0',
                        'name' => '',
                    ]);
                    $nationsArray = array_column($tempArray, 'name');
                    $tempResultIndex = array_search($value, $nationsArray);
                    if ($tempResultIndex === false){
                        if($errTableField = array_search($key, $excelTableField)){
                            throw new Exception('序号' . $sort . '的表格【' . $errTableField . '】值填写有误，系统中未找到填写的"' . $value . '"');
                        }else{
                            throw new Exception('序号' . $sort . '的表格' . $key . '值填写有误，系统中未找到"' . $value . '"');
                        }
                    }
                    $result[$resultKey] = $tempArray[$tempResultIndex]['id'];
                } elseif ($formFieldArr[$key]['index'] === 'bonusWalletType') {
                    $nationsArray = array_column($tempArray, 'name');
                    $tempResultIndex = array_search($value, $nationsArray);
                    if ($tempResultIndex === false){
                        if($errTableField = array_search($key, $excelTableField)){
                            throw new Exception('序号' . $sort . '的表格【' . $errTableField . '】值填写有误，系统中未找到填写的"' . $value . '"');
                        }else{
                            throw new Exception('序号' . $sort . '的表格' . $key . '值填写有误，系统中未找到"' . $value . '"');
                        }
                    }
                    $result[$resultKey] = $nationsArray[$tempResultIndex];
                }
            } elseif ($formFieldArr[$key]['type'] === 'table') {
                // 从数据库中获取对应的参数值
                $db = $formFieldArr[$key]['db'] ?? 'db';
                $field = $formFieldArr[$key]['field'];
                $select = $formFieldArr[$key]['select'];
                $table = $formFieldArr[$key]['table'];
                $where = $field . '=:' . $field;
                $where .= (isset($formFieldArr[$key]['where']) && $formFieldArr[$key]['where']) ? ' AND ' . $formFieldArr[$key]['where'] : '';
                $bindValues = [$field => $value];
                $tempResult = \Yii::$app->$db->createCommand("SELECT $select FROM {{%$table}} WHERE $where")->bindValues($bindValues)->queryOne();
                if (!$tempResult){
                    if($errTableField = array_search($key, $excelTableField)){
                        throw new Exception('序号' . $sort . '的表格【' . $errTableField . '】值填写有误，系统中未找到填写的"' . $value . '"');
                    }else{
                        throw new Exception('序号' . $sort . '的表格' . $key . '值填写有误，系统中未找到"' . $value . '"');
                    }
                }
                if (isset($formFieldArr[$key]['isArray']) && $formFieldArr[$key]['isArray']) {
                    $result[$resultKey][] = $tempResult[$select];
                } else {
                    $result[$resultKey] = $tempResult[$select];
                }

            }
        }
        return $result;
    }

    /**
     * 分页导入数据
     * @param $excelStructureKey
     * @param $excelImportId
     * @param int $rowCount
     * @param int $startRow
     * @param int $limit
     * @param int $orderDay
     * @param int $orderType
     * @return int
     * @throws Exception
     */
    public function pageImportCustomDataFromExcel($excelStructureKey, $excelImportId, $rowCount, $startRow, $limit, $orderDay, $orderType = 'cash') {
        if ($startRow > 1) {
            $startRow = $startRow + 1;
            $limit = $limit - 1;
        }
        $fileNameArray = ExcelImport::find()->select('U.FILE_NAME')->from(ExcelImport::tableName() . ' AS ET')->join('LEFT JOIN', Uploads::tableName() . ' AS U', 'ET.UPLOAD_ID=U.ID')->where('ET.ID=:ID', [':ID' => $excelImportId])->asArray()->one();
//        $filePath = \Yii::getAlias('@common/runtime/uploads/' . $fileNameArray['FILE_NAME']);
        $filePath = '/Volumes/HDD/workshop/old/ar.upload.ming/files/' . $fileNameArray['FILE_NAME'];
        if ($startRow > $rowCount) {
            return 0;
        }
        try {
            // 临时文件不存在则创建
            $tempFileName = \Yii::getAlias('@common/runtime/uploads/' . 'import'.$excelStructureKey.'Temp.txt');
            if (!file_exists($tempFileName)) {
                $fp = fopen($tempFileName, 'w+');
                fclose($fp);
            } else {
                if ($startRow == 1) {
                    file_put_contents($tempFileName, '');
                }
            }
            $data = \sunmoon\phpspreadsheet\Excel::import($filePath, [
                'setFirstRecordAsKeys' => true,
                'readStartRow' => $startRow,
                'readEndRow' => $startRow + $limit,
                'storeFile' => $tempFileName,
                'dropKeysRow' => $startRow == 1 ? false : true,
            ]);
            $errUserArray = [];
            foreach ($data as $u){ // 记录了导入中不存在的会员
                $u['USER_NAME'] = trim($u['会员编号']);
                if(!$u['USER_NAME']){
                    continue;
                }
                $userInfo = User::checkUser($u['USER_NAME']);
                if(!$userInfo){
                    $errUserArray[] = $u['USER_NAME'];
                }
            }
            if(count($errUserArray)>0){
                $errUserStr = implode(', ', $errUserArray);
                throw new Exception(count($errUserArray).'个用户不存在：'.substr($errUserStr,0, 600).'...');
            }
        } catch (\Exception $e) {
            throw new Exception($e->getMessage());
        }
        self::filterData($data);


        if ($data) {
            $tableClass = self::EXCEL_STRUCTURE[$excelStructureKey]['excelTableClass'];
            $tableAction = self::EXCEL_STRUCTURE[$excelStructureKey]['excelTableAction'];
            $form = new $tableClass();
            if (!$form->$tableAction($data,$orderDay,$orderType)) {
                return 0;
            }
            return 1;
        } else {
            return 0;
        }
    }

    /**
     * 分页导入数据
     * @param string $fileName
     * @param int $rowCount
     * @param int $startRow
     * @param int $limit
     * @return array
     * @throws Exception
     */
    public function pageImportDataByExcelFile($fileName, $rowCount = 1000, $startRow = 1, $limit = 1000) {
        if ($startRow > 1) {
            $startRow = $startRow + 1;
            $limit = $limit - 1;
        }
        $filePath = \Yii::getAlias('@common/runtime/uploads/' . $fileName);
        if ($startRow > $rowCount) {
            return [];
        }
        try {
            // 临时文件不存在则创建
            $tempFileName = \Yii::getAlias('@common/runtime/uploads/' . 'importTemp.txt');
            if (!file_exists($tempFileName)) {
                $fp = fopen($tempFileName, 'w+');
                fclose($fp);
            } else {
                if ($startRow == 1) {
                    file_put_contents($tempFileName, '');
                }
            }
            $data = \sunmoon\phpspreadsheet\Excel::import($filePath, [
                'setFirstRecordAsKeys' => true,
                'readStartRow' => $startRow,
                'readEndRow' => $startRow + $limit,
                'storeFile' => $tempFileName,
                'dropKeysRow' => $startRow == 1 ? false : true,
            ]);
        } catch (\Exception $e) {
            throw new Exception($e->getMessage());
        }
        self::filterData($data);


        if ($data) {
            return $data;
        } else {
            return [];
        }
    }

}