设备点检标准导入功能

cuijian

2023-11-14 5f213087bd5aaf2be60b306402944f8e4c3636d1

设备点检标准导入功能

 lxzn-module-eam/src/main/java/org/jeecg/modules/eam/controller/DailyInspectionStandardController.java

@@ -2,32 +2,39 @@

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.aspect.annotation.AutoLog;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.system.base.controller.JeecgController;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.modules.eam.entity.DailyInspectionStandard;
import org.jeecg.modules.eam.entity.DailyInspectionStandardDetail;
import org.jeecg.modules.eam.service.IDailyInspectionStandardDetailService;
import org.jeecg.modules.eam.service.IDailyInspectionStandardService;
import org.jeecg.modules.eam.service.IEamEquipmentService;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.common.util.ImportExcelUtil;
import org.jeecg.modules.eam.entity.*;
import org.jeecg.modules.eam.service.*;
import org.jeecg.modules.eam.vo.InspectionStandardImportVo;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.*;

/**
 * @Description: mom_eam_inspection_project
@@ -48,6 +55,15 @@

    @Autowired
    private IDailyInspectionStandardDetailService dailyInspectionStandardDetailService;

    @Autowired
    private IdentityService identityService;

    @Autowired
    private IInspectionProjectService inspectionProjectService;

    @Autowired
    private IInspectionCycleService inspectionCycleService;
    /**
     * åˆ†é¡µåˆ—表查询
     *
@@ -94,8 +110,8 @@
     */
    @GetMapping("findInspectionStandardList")
    public Result<?> findInspectionStandardList(@RequestParam("pageNo") Integer pageNo,
                                               @RequestParam("pageSize") Integer pageSize,
                                               @RequestParam Map<String, Object> params) {
                                                @RequestParam("pageSize") Integer pageSize,
                                                @RequestParam Map<String, Object> params) {
        IPage<Map<String, Object>> inspectionStandardList = dailyInspectionStandardService.findInspectionStandardList(pageNo, pageSize, params);
        return Result.ok(inspectionStandardList);
    }
@@ -240,8 +256,178 @@
     * @return
     */
    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) {
        return super.importExcel(request, response, DailyInspectionStandard.class);
    public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response)  throws IOException {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        LoginUser user= (LoginUser) SecurityUtils.getSubject().getPrincipal();
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        // é”™è¯¯ä¿¡æ¯
        List<String> errorMessage = new ArrayList<>();
        int successLines = 0, errorLines = 0;
        //a = 1开始读取点检项目；b = 1开始读取周保养项目；c = 1该设备导入信息存在错误；projectColumnIndex点检项目所在列；maintenanceColumnIndex周保养项目所在列
        int a = 0,b = 0,c = 0,projectColumnIndex = -1,maintenanceColumnIndex = 0;
        String dailyInspectionStandardId = null;
        String equipmentNum = null;
        DailyInspectionStandard dailyInspectionStandard = new DailyInspectionStandard();
        List<DailyInspectionStandardDetail> dailyInspectionStandardDetailList = new ArrayList<>();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();
            ImportParams params = new ImportParams();
            params.setNeedSave(true);
            try {
                InputStream fis = file.getInputStream();
                Workbook workbook = new XSSFWorkbook(fis);
                int sheets = workbook.getNumberOfSheets();
                //判断点检周期是否存在
                InspectionCycle dayInspectionCycle = inspectionCycleService.lambdaQuery().eq(InspectionCycle::getName,"1日").eq(InspectionCycle::getVersionStatus,"2").one();
                InspectionCycle weekInspectionCycle = inspectionCycleService.lambdaQuery().eq(InspectionCycle::getName,"1周").eq(InspectionCycle::getVersionStatus,"2").one();
                if(dayInspectionCycle == null || weekInspectionCycle == null){
                    c = 1;
                    errorMessage.add("点检周期不存在，请先维护点检周期");
                    errorLines++;
                }else{
                    for(int i = 0;i < sheets;i++){
                        Sheet sheet = workbook.getSheetAt(i);
                        for (Row row : sheet) {
                            // éåŽ†æ¯ä¸€ä¸ªå•å…ƒæ ¼
                            for (Cell cell : row) {
                                // å¤„理单元格数据，例如输出单元格的值
                                if("STRING".equals(cell.getCellType().toString())|| "BLANK".equals(cell.getCellType().toString())){
                                    //点检周期存在，判断设备统一编码是否存在
                                    if(cell.getStringCellValue().contains("设备名称：")){
                                        String[] tybh1  = cell.getStringCellValue().split("统一编号：");
                                        String[] tybh2 = tybh1[1].split("å¹´");
                                        equipmentNum = tybh2[0].trim().substring(0,tybh2[0].trim().length()-4).trim();
                                        /*String year =  tybh2[0].trim().substring(tybh2[0].trim().length()-4,tybh2[0].trim().length()).trim();
                                        String[] month1 = tybh2[1].split("月");
                                        String month = month1[0].trim();
                                        String[] unit1 = cell.getStringCellValue().split("单位：");
                                        String unit = unit1[1].trim();*/
                                        Equipment equipment = equipmentService.lambdaQuery().eq(Equipment::getNum,equipmentNum).eq(Equipment::getDelFlag, CommonConstant.DEL_FLAG_0).one();
                                        if(equipment == null){
                                            c = 1;
                                            errorMessage.add("sheet页为"+sheet.getSheetName()+"，设备统一编码为"+ equipmentNum + "的数据导入失败,原因:设备不存在，请先维护设备台账！");
                                            errorLines++;
                                        }else {
                                            List<DailyInspectionStandard> dailyInspectionStandardList = dailyInspectionStandardService.lambdaQuery().eq(DailyInspectionStandard::getEquipmentId,equipment.getId())
                                                    .eq(DailyInspectionStandard::getVersionStatus,"2").eq(DailyInspectionStandard::getDelFlag,CommonConstant.DEL_FLAG_0).list();
                                            if(CollectionUtils.isNotEmpty(dailyInspectionStandardList)){
                                                c = 1;
                                                errorMessage.add("sheet页为"+sheet.getSheetName()+"，设备统一编码为"+ equipmentNum + "的数据导入失败,原因:该设备已经存在点检标准！");
                                                errorLines++;
                                            }else {
                                                c = 0;
                                                dailyInspectionStandard.setNum(identityService.getNumByTypeAndLength("DailylnspectionStandard",4));
                                                dailyInspectionStandard.setVersionStatus("2");
                                                dailyInspectionStandard.setVersion("1.0");
                                                dailyInspectionStandard.setAssignMode("1");
                                                dailyInspectionStandard.setEquipmentId(equipment.getId());
                                                //dailyInspectionStandardService.save(dailyInspectionStandard);
                                                //dailyInspectionStandardId = dailyInspectionStandard.getId();
                                            }
                                        }
                                        break;
                                    }
                                    //设备统一编码存在，判断点检项目是否存在
                                    if(c == 0){
                                        System.out.print(cell.getStringCellValue() + "\t");
                                        if(cell.getStringCellValue().contains("点检项目")){
                                            a = 1;
                                            projectColumnIndex = cell.getColumnIndex();
                                            break;
                                        }
                                        if(cell.getStringCellValue().contains("维护责任人签字")){
                                            a = 0;
                                        }
                                        if(projectColumnIndex == cell.getColumnIndex() && a == 1){
                                            DailyInspectionStandardDetail dailyInspectionStandardDetail = new DailyInspectionStandardDetail();
                                            dailyInspectionStandardDetail.setDailyInspectionStandardId(dailyInspectionStandardId);
                                            String dayInspectionProjectName = row.getCell(projectColumnIndex).toString();
                                            String standard = row.getCell(projectColumnIndex+1).toString();
                                            //查询点击项目，如果没有新增点检项目
                                            if(StringUtils.isNotBlank(dayInspectionProjectName)){
                                                List<InspectionProject> inspectionProjectList = inspectionProjectService.lambdaQuery().eq(InspectionProject::getName,dayInspectionProjectName).eq(InspectionProject::getDelFlag,CommonConstant.DEL_FLAG_0).list();
                                                if(CollectionUtils.isEmpty(inspectionProjectList)){
                                                    InspectionProject inspectionProject = new InspectionProject();
                                                    inspectionProject.setNum(identityService.getNumByTypeAndLength("InspectionProject",4));
                                                    inspectionProject.setName(dayInspectionProjectName);
                                                    inspectionProject.setDetectionStandard(standard);
                                                    inspectionProjectService.save(inspectionProject);
                                                    dailyInspectionStandardDetail.setInspectionProjectId(inspectionProject.getId());
                                                    dailyInspectionStandardDetail.setInspectionCycleId(dayInspectionCycle.getId());
                                                    dailyInspectionStandardDetailList.add(dailyInspectionStandardDetail);
//                                                    c = 1;
//                                                    errorMessage.add("sheet页为"+sheet.getSheetName()+"，设备统一编码为"+ equipmentNum + "的数据导入失败,原因:点检项目不存在，请先维护点检项目！");
//                                                    errorLines++;
                                                }else {
                                                    dailyInspectionStandardDetail.setInspectionProjectId(inspectionProjectList.get(0).getId());
                                                    dailyInspectionStandardDetail.setInspectionCycleId(dayInspectionCycle.getId());
                                                    dailyInspectionStandardDetailList.add(dailyInspectionStandardDetail);
                                                    //dailyInspectionStandardDetailService.save(dailyInspectionStandardDetail);
                                                }
                                            }
                                            break;
                                        }
                                        if(cell.getStringCellValue().contains("周保养项目")){
                                            b = 1;
                                            maintenanceColumnIndex = cell.getColumnIndex();
                                            break;
                                        }
                                        if(cell.getStringCellValue().contains("周保养操作者执行")){
                                            b = 0;
                                            dailyInspectionStandardService.save(dailyInspectionStandard);
                                            for(DailyInspectionStandardDetail dailyInspectionStandardDetail : dailyInspectionStandardDetailList){
                                                dailyInspectionStandardDetail.setDailyInspectionStandardId(dailyInspectionStandard.getId());
                                            }
                                            dailyInspectionStandardDetailService.saveBatch(dailyInspectionStandardDetailList);
                                            successLines++;
                                            dailyInspectionStandard = null;
                                            dailyInspectionStandardDetailList.clear();
                                        }
                                        if(b == 1 && c == 0 && maintenanceColumnIndex == cell.getColumnIndex()){
                                            DailyInspectionStandardDetail dailyInspectionStandardDetail = new DailyInspectionStandardDetail();
                                            dailyInspectionStandardDetail.setDailyInspectionStandardId(dailyInspectionStandardId);
                                            String weekInspectionProjectName = row.getCell(maintenanceColumnIndex).toString();
                                            String inspectionStandardName = row.getCell(maintenanceColumnIndex+1).toString();
                                            if(StringUtils.isNotBlank(weekInspectionProjectName)){
                                                List<InspectionProject> inspectionProjectList = inspectionProjectService.lambdaQuery().eq(InspectionProject::getName,weekInspectionProjectName).eq(InspectionProject::getDelFlag,CommonConstant.DEL_FLAG_0).list();
                                                if(CollectionUtils.isEmpty(inspectionProjectList)){
                                                    InspectionProject inspectionProject = new InspectionProject();
                                                    inspectionProject.setNum(identityService.getNumByTypeAndLength("InspectionProject",4));
                                                    inspectionProject.setName(weekInspectionProjectName);
                                                    inspectionProject.setDetectionStandard(inspectionStandardName);
                                                    inspectionProjectService.save(inspectionProject);
                                                    dailyInspectionStandardDetail.setInspectionProjectId(inspectionProject.getId());
                                                    dailyInspectionStandardDetail.setInspectionCycleId(weekInspectionCycle.getId());
                                                    dailyInspectionStandardDetailList.add(dailyInspectionStandardDetail);
//                                                    c = 1;
//                                                    errorMessage.add("sheet页为"+sheet.getSheetName()+"，行数为："+row.getRowNum()+"，设备统一编码为"+ equipmentNum + "的数据导入失败,原因:点检项目不存在，请先维护点检项目！");
//                                                    errorLines++;
                                                }else {

                                                    dailyInspectionStandardDetail.setInspectionProjectId(inspectionProjectList.get(0).getId());
                                                    dailyInspectionStandardDetail.setInspectionCycleId(weekInspectionCycle.getId());
                                                    dailyInspectionStandardDetailList.add(dailyInspectionStandardDetail);
                                                    //dailyInspectionStandardDetailService.save(dailyInspectionStandardDetail);
                                                }
                                            }
                                            break;
                                        }
                                    }
                                }/*else if("NUMERIC".equals(cell.getCellType().toString())){
                                System.out.print(cell.getNumericCellValue() + "\t");
                            }
*/
                            }
                            System.out.println();
                        }
                    }
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return ImportExcelUtil.imporReturnRes(errorLines, successLines, errorMessage);
    }

    /**
@@ -250,8 +436,8 @@
     */
    @GetMapping("getDepartByEquipment")
    public Result<?> getDepartByEquipment(@RequestParam("pageNo") Integer pageNo,
                                               @RequestParam("pageSize") Integer pageSize,
                                               @RequestParam Map<String, Object> params) {
                                          @RequestParam("pageSize") Integer pageSize,
                                          @RequestParam Map<String, Object> params) {
        IPage<Map<String, Object>> departByEquipment = dailyInspectionStandardService.getDepartByEquipment(pageNo, pageSize, params);
        return Result.ok(departByEquipment);
    }

 lxzn-module-eam/src/main/java/org/jeecg/modules/eam/controller/OperationCertificateController.java

@@ -12,6 +12,7 @@
import javax.servlet.http.HttpServletResponse;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.util.DateUtils;
@@ -297,7 +298,47 @@
                    for (int i = 0; i < list.size(); i++) {
                    OperationCertificateImportVo operationCertificateImportVo = list.get(i);
                    OperationCertificate operationCertificate = new OperationCertificate();
                    operationCertificate.setNum(identityService.getNumByTypeAndLength("OperationCertificate",4));
                    //判空
                    if(StringUtils.isBlank(operationCertificateImportVo.getName())){
                        errorMessage.add("第" + (i + 1) + "行导入失败,原因:人员不能为空！");
                        errorLines++;
                        continue;
                    }
                    if(StringUtils.isBlank(operationCertificateImportVo.getTechnicalLevel())){
                        errorMessage.add("第" + (i + 1) + "行导入失败,原因:技能等级不能为空！");
                        errorLines++;
                        continue;
                    }
                    if(StringUtils.isBlank(operationCertificateImportVo.getProfession())){
                        errorMessage.add("第" + (i + 1) + "行导入失败,原因:工种不能为空！");
                        errorLines++;
                        continue;
                    }
                    if(StringUtils.isBlank(operationCertificateImportVo.getWorkNo())){
                        errorMessage.add("第" + (i + 1) + "行导入失败,原因:工作证号不能为空！");
                        errorLines++;
                        continue;
                    }
                    if(StringUtils.isBlank(operationCertificateImportVo.getAppointmentCardNum())){
                        errorMessage.add("第" + (i + 1) + "行导入失败,原因:上岗证号不能为空！");
                        errorLines++;
                        continue;
                    }
                    if(StringUtils.isBlank(operationCertificateImportVo.getEquipmentNum())){
                        errorMessage.add("第" + (i + 1) + "行导入失败,原因:统一编号不能为空！");
                        errorLines++;
                        continue;
                    }
                    if(operationCertificateImportVo.getIssueDate() == null){
                        errorMessage.add("第" + (i + 1) + "行导入失败,原因:发证日期不能为空！");
                        errorLines++;
                        continue;
                    }
                    if(StringUtils.isBlank(operationCertificateImportVo.getNum())){
                        operationCertificate.setNum(identityService.getNumByTypeAndLength("OperationCertificate",4));
                    }else {
                        operationCertificate.setNum(operationCertificateImportVo.getNum());
                    }
                    //根据人员姓名查询人员id
                    List<SysUser> userList = userService.lambdaQuery().eq(SysUser::getRealname,operationCertificateImportVo.getName().trim())
                            .eq(SysUser::getWorkNo,operationCertificateImportVo.getWorkNo().trim()).eq(SysUser::getDelFlag, CommonConstant.DEL_FLAG_0).list();
@@ -384,7 +425,8 @@
            } catch (Exception e) {
                errorMessage.add("发生异常：" + e.getMessage());
                log.error(e.getMessage(), e);
                return (Result<?>) errorMessage;
                return Result.error(errorMessage.toString());
                // return (Result<?>) errorMessage;
            } finally {
                try {
                    file.getInputStream().close();

 lxzn-module-eam/src/main/java/org/jeecg/modules/eam/vo/InspectionStandardImportVo.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,31 @@
package org.jeecg.modules.eam.vo;

import lombok.Data;
import lombok.experimental.Accessors;
import org.jeecgframework.poi.excel.annotation.Excel;

import java.math.BigDecimal;


@Data
@Accessors(chain = true)
public class InspectionStandardImportVo {

    @Excel(name= "设备编码")
    private String equipmentNum;


    @Excel(name = "点检项目")
    private String dayInspectionProjectName;

    @Excel(name = "完成数据/要求")
    private String standard;

    @Excel(name = "周保养项目")
    private String weekInspectionProjectName;

    @Excel(name = "检查标准")
    private String inspectionStandardName;


}

 lxzn-module-eam/src/main/java/org/jeecg/modules/eam/vo/OperationCertificateImportVo.java

@@ -45,4 +45,7 @@
    @Excel(name = "备注")
    private String remark;

    @Excel(name = "操作证编号")
    private String num;

}