1 view渲染
<form action="../src/website/import/report-flow" method="post" enctype="multipart/form-data"> </br> type<input type="text" name="type" /> year<input type="text" name="year" /> month<input type="text" name="month" /> <input type="file" name="fileExcel" id="fileExcel" /> <br /> <input type="submit" name="submit" value="Submit" /> </form>
2 上传接口
src/modules/website/controllers/ImportController.php
/** * @author Anana * 上传商销/盒 文件 异步处理 */ public function actionReportFlow() { $uploadFile = UploadedFile::getInstanceByName ('fileExcel'); $type = intval(parent::getPostParam ('type')); // 文件上传类型 $year = intval(parent::getPostParam('year'));//年 $month = intval(parent::getPostParam('month'));//月 if($month<10){ $month = '0'.$month; } $accountRoleId= Yii::$app->session['accountRoleRow']['account_role_id']; if(!in_array($type,array('1','2'))){ parent::apiReturn ( 1, [ ], '请先选择要上传的文件类型' ); }else if(!$year){ parent::apiReturn ( 1, [ ], '请选择年' ); }else if(!$month || $month > 12 ){ parent::apiReturn ( 1, [ ], '请选择月' ); }else if($uploadFile){ $fileExt = $uploadFile->getExtension (); // 文件后缀 $size = $uploadFile->size;//文件大小 if (! in_array ( $fileExt, array('xlsx','xls','csv') )) { $reason [] = '上传的文件后缀名必须为xlsx、xls、csv'; }else if($size > 20*1024*1024){ $reason [] = '文件大小不能超过20M'; }else{ $fileName = date('Ymdhis').'_'.$accountRoleId; if($type==1){ //商销/盒数据 $dir = 'report_flow_month'; }else { //终端开户明细 $dir = 'open_accounts_month'; } $fileName = $fileName.'.'.$fileExt; $companyCode = Yii::$app->session['companyRow']['code']; $updateMonth =date('Y-m-00',strtotime("{$year}-{$month}")); $buildsDir = dirname(yii::$app->basePath)."/builds/{$companyCode}/{$dir}/$updateMonth"; //创建生成目录文件夹 if(!file_exists($buildsDir)){ mkdir($buildsDir,0777,true); } $csvPath = $buildsDir.'/'.$fileName; $rs = $uploadFile->saveAs($csvPath);//文件另存为 if($rs){ $reportDaySalesFlowUploadLogModel = new ReportDaySalesFlowUploadLogModel(); $reportDaySalesFlowUploadLogModel->file_type = $type; $reportDaySalesFlowUploadLogModel->role_id = $accountRoleId; $reportDaySalesFlowUploadLogModel->new_file_name= $fileName; $reportDaySalesFlowUploadLogModel->create_time = date("Y-m-d H:i:s"); $reportDaySalesFlowUploadLogModel->company = $companyCode; $reportDaySalesFlowUploadLogModel->update_month = $updateMonth; $rs = $reportDaySalesFlowUploadLogModel->insert(); if(!$rs){ parent::apiReturn ( 1, $jsonData, '上传日志更新失败' ); } parent::apiReturn ( 0, [ ], '上传成功' ); }else{ $reason [] = '上传文件失败' . $uploadFile->error; } } $jsonData = [ ]; if ($reason) { $jsonData ['errorList'] = implode ( ' ', $reason ); parent::apiReturn ( 1, $jsonData, '错误信息提示' ); } }else{ parent::apiReturn ( 1, [ ], '上传失败' ); } }
3 上传以后处理数据
/** * 1 删除report_day_sales_flow_month表当月的老数据 * 2 插入report_day_sales_flow_month表当月新数据 * * * $fileName 文件名 * $fileLogId 文件id */ private function _updateReportDaySalesFlowMonth($fileName,$fileLogId,$updateMonth) { $connection = $this->_selectDb(); $buildsDir = dirname(yii::$app->basePath)."/builds/yiling/xlsx/report_flow_month/$fileName"; if(!file_exists($buildsDir)){ echo "FILE NOT FOUND 找不到文件 "; return; } $objReader = PHPExcel_IOFactory::createReader('CSV'); $objReader->setReadDataOnly(true);// //只读去数据,忽略里面各种格式等(对于Excel读去,有很大优化) $phpExcel = PHPExcel_IOFactory::load ($buildsDir); // 自动判断Excel版本解析读取 $sheet = $phpExcel->getSheet ( 0 ); $collectionList = [ ]; // 初始化数据集合 $errorData = [ ]; // 导入的错误信息数据 有错误数据无法导入 $errorArray = [ ]; // 读取Excel数据行记录 $totalRow = $sheet->getHighestRow(); // 取得总行数 $thisTime = date("Y-m-d H:i:s"); for($i = 2; $i <= $totalRow; ++$i) { $year = $sheet->getCellByColumnAndRow(0, $i)->getValue(); $month = $sheet->getCellByColumnAndRow(1, $i)->getValue(); $row['year_str'] = $year; $row['month_str'] = $month; $year_and_month = date("Y-m-00",strtotime(intval($year).'-'.intval($month))); $row['update_month'] = $year_and_month; $row['dept'] =$sheet->getCellByColumnAndRow(2, $i)->getValue();//部门 $row['business_dept'] =$sheet->getCellByColumnAndRow(3, $i)->getValue();//业务部门 $row['business_type'] =$sheet->getCellByColumnAndRow(4, $i)->getValue();//业务类型 $row['region_description'] =$sheet->getCellByColumnAndRow(5, $i)->getValue();//省区 $row['bussiness_region_description'] =$sheet->getCellByColumnAndRow(6, $i)->getValue();//业务省区 $row['region_named_code'] =$sheet->getCellByColumnAndRow(7, $i)->getValue();//区办代码 $row['region_named'] =$sheet->getCellByColumnAndRow(8, $i)->getValue();//区办 $row['parent_role_number'] =$sheet->getCellByColumnAndRow(9, $i)->getValue();//主管工号 $row['parent_role_name'] =$sheet->getCellByColumnAndRow(10, $i)->getValue();//主管姓名 $row['role_number'] =$sheet->getCellByColumnAndRow(11, $i)->getValue();//代表工号 $row['role_name'] =$sheet->getCellByColumnAndRow(12, $i)->getValue();//代表姓名 $row['client_number'] =$sheet->getCellByColumnAndRow(13, $i)->getValue();//终端代码 $row['client_name'] =$sheet->getCellByColumnAndRow(14, $i)->getValue();//终端名称 $row['province'] =$sheet->getCellByColumnAndRow(15, $i)->getValue();//省份 $row['city'] =$sheet->getCellByColumnAndRow(16, $i)->getValue();//城市 $row['district'] =$sheet->getCellByColumnAndRow(17, $i)->getValue();//区县 $row['hospital_type'] =$sheet->getCellByColumnAndRow(18, $i)->getValue();//医院类型 $row['custom_field_1'] =$sheet->getCellByColumnAndRow(19, $i)->getValue();//以岭级别 $row['nation_level'] =$sheet->getCellByColumnAndRow(20, $i)->getValue();//国家等级 $row['contract_type'] =$sheet->getCellByColumnAndRow(21, $i)->getValue();//承包类型 $row['custom_field_2'] =$sheet->getCellByColumnAndRow(22, $i)->getValue();//管理级别1 $row['group_name'] =$sheet->getCellByColumnAndRow(23, $i)->getValue();//品种 $row['goods_number'] =$sheet->getCellByColumnAndRow(24, $i)->getValue();//产品代码 $row['goods_name_spec'] =$sheet->getCellByColumnAndRow(25, $i)->getValue();//产品名称与品规 $row['purchase'] =$sheet->getCellByColumnAndRow(26, $i)->getValue();//商销/盒 $row['create_time'] =$thisTime;//导入时间 $row['file_log_id'] =$fileLogId; $data[] = $row; } //释放内存 $sheet = $phpExcel =$phpExcel= null; $fields = [ 'year_str', 'month_str', 'update_month', 'dept', 'business_dept', 'business_type', 'region_description', 'bussiness_region_description', 'region_named_code', 'region_named', 'parent_role_number', 'parent_role_name', 'role_number', 'role_name', 'client_number', 'client_name', 'province', 'city', 'district', 'hospital_type', 'custom_field_1', 'nation_level', 'contract_type', 'custom_field_2', 'group_name', 'goods_number', 'goods_name_spec', 'purchase', 'create_time', 'file_log_id' ]; $rs = $connection->createCommand()->batchInsert('report_day_sales_flow_month',$fields,$data)->execute(); return $rs; }
4 同步上传
/** * @author Anana * http://localhost/src/website/import/region-named * 上传以岭区办 */ public function actionRegionNamed() { $uploadFile = UploadedFile::getInstanceByName ('fileExcel'); if(!$uploadFile){ parent::apiReturn ( 1, [ ], '上传失败' ); } $fileExt = $uploadFile->getExtension (); // 文件后缀 $size = $uploadFile->size;//文件大小 if (! in_array ( $fileExt, array('xlsx','xls','csv') )) { parent::apiReturn ( 1, [ ], '上传的文件后缀名必须为xlsx、xls、csv' ); } if($size > 20*1024*1024){ parent::apiReturn ( 1, [ ], '文件大小不能超过20M' ); } $objReader = PHPExcel_IOFactory::createReader('CSV'); $objReader->setReadDataOnly(true);// //只读去数据,忽略里面各种格式等(对于Excel读去,有很大优化) $phpExcel = PHPExcel_IOFactory::load ( $uploadFile->tempName ); // 自动判断Excel版本解析读取 $sheet = $phpExcel->getSheet ( 0 ); $collectionList = [ ]; // 初始化数据集合 // 读取Excel数据行记录 $totalRow = $sheet->getHighestRow (); // 取得总行数 //所有部门 $deptArr = array( 1 => '医院一部', 2 => '医院二部', 3 => '社区部', 4 => '城乡部' ); $deptFlipArr = array_flip($deptArr); //所有业务部门 $bussinessDeptArr = array( 1=>'医院一部', 2=>'肿瘤产品部', 3=>'医院二部', 4 =>'社区部', 5 =>'城乡部', 6=>'城乡二部' ); $bussinessDeptFlipArr = array_flip($bussinessDeptArr); //所有省区 $regionArea = RegionAreaModel::find()->select(['region_area_id','name'])->where(['is_valid'=>VALID])->asArray()->all(); foreach ($regionArea as $v){ $regionAreaArr[$v['region_area_id']] = $v['name']; } $regionAreaFlipArr = array_flip($regionAreaArr); //所有业务省区 $bussinessProvince = BussinessProvinceModel::find()->select(['bussiness_province_id','bussiness_province'])->where(['is_valid'=>VALID])->asArray()->all(); foreach ($bussinessProvince as $v){ $bussinessProvinceArr[$v['bussiness_province_id']] = $v['bussiness_province']; } $bussinessProvinceFlipArr = array_flip($bussinessProvinceArr); $hasError = False; $roleId = Yii::$app->session['accountRoleRow']['account_role_id']; $thisTime = date("Y-m-d H:i:s"); // 跳过标题行 for($i = 2; $i <= $totalRow; ++ $i) { $error = array(); $dept = trim ( $sheet->getCellByColumnAndRow ( 0, $i )->getValue () ); // 部门 $bussinessDept = trim ( $sheet->getCellByColumnAndRow ( 1, $i )->getValue () ); //业务部门 $regionArea = trim ( $sheet->getCellByColumnAndRow ( 2, $i )->getValue () ); //省区 $bussinessProvince = trim ( $sheet->getCellByColumnAndRow ( 3, $i )->getValue () ); //业务省区 $regionNamedCode = trim ( $sheet->getCellByColumnAndRow ( 4, $i )->getValue () ); //区办代码 $regionNamed = trim ( $sheet->getCellByColumnAndRow ( 5, $i )->getValue () ); //区办 if(!in_array($dept,$deptArr)){ $hasError = $hasError | true; $error[] = '【部门】不存在 '; }else{ $row['dept_id'] = $deptFlipArr[$dept]; } if(!in_array($bussinessDept,$bussinessDeptArr)){ $hasError = $hasError | true; $error[] = '【业务部门】不存在 '; }else{ $row['bussiness_dept_id'] = $bussinessDeptFlipArr[$bussinessDept]; } if(!in_array($regionArea,$regionAreaArr)){ $hasError = $hasError | true; $error[] = '【省区】不存在 '; }else{ $row['region_area_id'] = $regionAreaFlipArr[$regionArea]; } if(!in_array($bussinessProvince,$bussinessProvinceArr)){ $hasError = $hasError | true; $error[] = '【业务省区】不存在 '; }else{ $row['bussiness_province_id'] = $bussinessProvinceFlipArr[$bussinessProvince]; } if(mb_strlen($regionNamedCode)>20){ $hasError = $hasError | true; $error[] = '【区办代码】太长了'; } if(!$regionNamed || mb_strlen($regionNamed)>20){ $hasError = $hasError | true; $error[] = '【区办】未填或太长了'; }else{ $regionNamedOne = RegionNamedModel::find()->select(['region_named_id'])->where(['region_named'=>$regionNamed, 'is_valid'=>VALID])->one(); if($regionNamedOne){ $hasError = $hasError | true; $error[] = '此【区办】已存在，不可重复添加'; } } //$row['dept'] =$dept; //$row['bussiness_dept']=$bussinessDept; //$row['region_area'] = $regionArea; //$row['bussiness_province'] = $bussinessProvince; $row['region_named_code'] =$regionNamedCode; $row['region_named'] = $regionNamed; $row['role_id'] = $roleId; $row['create_time'] = $thisTime; if(isset($error) && $error ){ $row['error'] = implode('：',$error); $sheet->setCellValueExplicitByColumnAndRow ( 6, $i,$row['error']); } $collectionList[] = $row; } $fileName = 'region_named_error'; $fileName = $fileName.'.xlsx'; $companyCode = Yii::$app->session['companyRow']['code']; $buildsDir = dirname(yii::$app->basePath)."/builds/{$companyCode}/region_named_error"; //创建生成目录文件夹 if(!file_exists($buildsDir)){ mkdir($buildsDir,0777,true); } $csvPath = $buildsDir.'/'.$fileName; //释放内存 $bussinessDeptFlipArr = $bussinessDeptArr = $bussinessProvinceFlipArr=$bussinessProvinceArr = null; //上传数据正确 插入数据表 if($hasError){ //释放内存 $collectionList = null; //上传数据错误，写入文件 $phpExcelWriter = PHPExcel_IOFactory::createWriter ( $phpExcel, 'Excel5' ); $rs = $phpExcelWriter->save ( $csvPath ); //$rs = $uploadFile->saveAs($csvPath);//文件另存为 parent::apiReturn ( 1, [ ], '上传数据有错误，已生成错误数据文件，请下载并按提示修改' ); }else{ //释放内存 $phpExcel = null; Yii::$app->params ['dbSelect'] = 'company'; // 切换到 company 数据库 Yii::$app->params ['dsnSelect'] = 51;//yiling $connection = Yii::$app->companydb; //$queryBuilder = $connection->queryBuilder; $fields = ['dept_id','bussiness_dept_id','region_area_id','bussiness_province_id','region_named_code','region_named','role_id','create_time']; $rs = $connection ->createCommand()->batchInsert(RegionNamedModel::tableName(),$fields, $collectionList)->execute(); if($rs){ //删除错误文件 unlink($csvPath); parent::apiReturn ( 0, [ ], '导入数据成功' ); } parent::apiReturn ( 1, [ ], '导入数据失败，请联系程序员'); } }