导出增加筛选条件 导出根据任务名称分sheet 添加索引

huliguo

2 天以前 b21549bfd07dd43525d453d9a0282e25260c092c

 guns-admin/src/main/java/com/stylefeng/guns/modular/system/service/impl/TaskDetailServiceImpl.java

@@ -8,10 +8,7 @@
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.stylefeng.guns.core.util.ToolUtil;
import com.stylefeng.guns.modular.system.dao.TaskDetailMapper;
import com.stylefeng.guns.modular.system.model.PatrolTask;
import com.stylefeng.guns.modular.system.model.TaskDetail;
import com.stylefeng.guns.modular.system.model.TaskDetailVehicles;
import com.stylefeng.guns.modular.system.model.TaskDetailVehiclesChannel;
import com.stylefeng.guns.modular.system.model.*;
import com.stylefeng.guns.modular.system.model.enums.VideoChannelEnum;
import com.stylefeng.guns.modular.system.model.vo.*;
import com.stylefeng.guns.modular.system.service.*;
@@ -31,6 +28,7 @@
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
@@ -64,6 +62,9 @@
   @Autowired
   private IUserService userService;
   
   @Autowired
   private IIconService iconService;
	
   
   
   
@@ -86,11 +87,12 @@
    */
   @Override
   public void delTaskDetail(List<String> ids) {
      this.baseMapper.deleteBatchIds(ids);
      List<TaskDetail> taskDetails = this.selectBatchIds(ids);
      for (TaskDetail taskDetail : taskDetails) {
         JobKey jobKey = new JobKey(taskDetail.getId().toString());
         quartzUtil.deleteQuartzTask(jobKey);
         taskDetail.setDelFlag(1);
         this.updateById(taskDetail);
      }
   }
   
@@ -157,19 +159,40 @@
         }
         wrapper.in("vehicleIdUnique", vehicleIdList);
      }
      if(null != vo.getStatus()){
         wrapper.eq("status", vo.getStatus());
      if(null != vo.getStatus() && !vo.getStatus().isEmpty()){
         wrapper.in("status", vo.getStatus());
      }
      if(null != vo.getSysStatus()){
         List<TaskDetailVehiclesChannel> sys_status = taskDetailVehiclesChannelService.selectList(new EntityWrapper<TaskDetailVehiclesChannel>().eq("sys_status", vo.getSysStatus()));

      if(null != vo.getSysStatus() && !vo.getSysStatus().isEmpty()){
         Wrapper<TaskDetailVehiclesChannel> channelWrapper = new EntityWrapper<TaskDetailVehiclesChannel>()
                    .eq("task_detail_id", vo.getId())
               .in("sys_status", vo.getSysStatus());
         if (null != vo.getHavePic()){
            if(vo.getHavePic().equals("0")){
               channelWrapper.isNull("image_url");
            }else if(vo.getHavePic().equals("1")){
               channelWrapper.isNotNull("image_url");
            }
         }
         List<TaskDetailVehiclesChannel> sys_status = taskDetailVehiclesChannelService.selectList(channelWrapper);
         List<Integer> collect = sys_status.stream().map(TaskDetailVehiclesChannel::getTaskDetailVehiclesId).collect(Collectors.toList());
         if(collect.size() == 0){
            collect.add(-1);
         }
         wrapper.in("id", collect);
      }
      if(null != vo.getArtificialStatus()){
         List<TaskDetailVehiclesChannel> sys_status = taskDetailVehiclesChannelService.selectList(new EntityWrapper<TaskDetailVehiclesChannel>().eq("artificial_status", vo.getArtificialStatus()));
      if(null != vo.getArtificialStatus()&& !vo.getArtificialStatus().isEmpty()){
         Wrapper<TaskDetailVehiclesChannel> channelWrapper = new EntityWrapper<TaskDetailVehiclesChannel>()
               .eq("task_detail_id", vo.getId())
               .in("artificial_status", vo.getArtificialStatus());
         if (null != vo.getHavePic()){
            if(vo.getHavePic().equals("0")){
               channelWrapper.isNull("image_url");
            }else if(vo.getHavePic().equals("1")){
               channelWrapper.isNotNull("image_url");
            }
         }
         List<TaskDetailVehiclesChannel> sys_status = taskDetailVehiclesChannelService.selectList(channelWrapper);
         List<Integer> collect = sys_status.stream().map(TaskDetailVehiclesChannel::getTaskDetailVehiclesId).collect(Collectors.toList());
         if(collect.size() == 0){
            collect.add(-1);
@@ -177,7 +200,7 @@
         wrapper.in("id", collect);
      }
      
      List<TaskDetailVehicles> taskDetailVehiclesList = taskDetailVehiclesService.selectList(wrapper);
      List<TaskDetailVehicles> taskDetailVehiclesList = taskDetailVehiclesService.selectList(wrapper.groupBy("vehicleIdUnique"));
      List<TaskDetailVehicles> vehiclesList = taskDetailVehiclesList.stream().filter(s -> s.getVehicleType() == 1).collect(Collectors.toList());
      List<VehicleVo> vehicle = new ArrayList<>();
      for (TaskDetailVehicles taskDetailVehicles : vehiclesList) {
@@ -218,19 +241,31 @@
         }
         wrapper.in("vehicleIdUnique", vehicleIdList);
      }
      if(null != vo.getStatus()){
         wrapper.eq("status", vo.getStatus());
      if(null != vo.getStatus() && !vo.getStatus().isEmpty()){
         wrapper.in("status", vo.getStatus());
      }
      List<TaskDetailVehicles> taskDetailVehiclesList = taskDetailVehiclesService.selectList(wrapper);
      List<Integer> collect = taskDetailVehiclesList.stream().map(TaskDetailVehicles::getId).collect(Collectors.toList());
      if(collect.size() > 0){
         collect.add(-1);
      }
      List<PictureDetails> pictureDetails = taskDetailVehiclesChannelService.getPictureDetails(pageInfo, collect, vo.getSysStatus(), vo.getArtificialStatus());
      List<PictureDetails> pictureDetails = taskDetailVehiclesChannelService.getPictureDetails(pageInfo, collect, vo.getSysStatus(), vo.getArtificialStatus(), vo.getHavePic() != null ? Integer.valueOf(vo.getHavePic()) : null);
      List<Icon> icons = iconService.selectList(null);
      for (PictureDetails pictureDetail : pictureDetails) {
         String videoChannel = pictureDetail.getVideoChannel();
         String name = VideoChannelEnum.getName(Integer.valueOf(videoChannel));
         pictureDetail.setVideoChannel(name);
         //异常
         if(pictureDetail.getStatus() == 5){
            pictureDetail.setImageUrl(icons.stream().filter(s->s.getType() == 2).findFirst().get().getUrl());
         }else if(ToolUtil.isEmpty(pictureDetail.getImageUrl())){
            //没有图片返回
            pictureDetail.setImageUrl(icons.stream().filter(s->s.getType() == 3).findFirst().get().getUrl());
         }
         //离线
         if(pictureDetail.getStatus() == 4){
            pictureDetail.setImageUrl(icons.stream().filter(s->s.getType() == 1).findFirst().get().getUrl());
         }
      }
      return pictureDetails;
   }
@@ -245,14 +280,15 @@
   public ResultUtil manualAudit(ManualAuditVo vo, String userId) {
      List<TaskDetailVehiclesChannel> taskDetailVehiclesChannels = taskDetailVehiclesChannelService.selectBatchIds(vo.getId());
      for (TaskDetailVehiclesChannel taskDetailVehiclesChannel : taskDetailVehiclesChannels) {
         if(null != taskDetailVehiclesChannel.getArtificialStatus() && 1 != taskDetailVehiclesChannel.getArtificialStatus()){
         /*if(null != taskDetailVehiclesChannel.getArtificialStatus() && 1 != taskDetailVehiclesChannel.getArtificialStatus()){
            return ResultUtil.error("不能重复审核");
         }
         }*/
         taskDetailVehiclesChannel.setArtificialStatus(vo.getStatus());
         taskDetailVehiclesChannel.setRemark(vo.getRemark());
         taskDetailVehiclesChannel.setArtificialCreateTime(LocalDateTime.now());
         taskDetailVehiclesChannel.setArtificialUserId(Integer.valueOf(userId));
         taskDetailVehiclesChannelService.updateById(taskDetailVehiclesChannel);
//         taskDetailVehiclesChannelService.updateById(taskDetailVehiclesChannel);
         taskDetailVehiclesChannelService.updateAudit(taskDetailVehiclesChannel);//需要将Remark字段设置为null
      }
      return ResultUtil.success();
   }
@@ -265,9 +301,9 @@
    * @param status
    * @param response
    */
   @Override
   public void downloadTaskRecord(List<String> ids, String code, Integer status, HttpServletResponse response) {
      List<Map<String, Object>> mapList = this.baseMapper.getDownloadTaskRecord(ids, code, status);
   /*@Override
   public void downloadTaskRecord(List<String> ids, String code, List<Integer> status,Integer sysStatus, Integer type,String typeName,HttpServletResponse response) {
      List<Map<String, Object>> mapList = this.baseMapper.getDownloadTaskRecord(ids, code, status,sysStatus,type,typeName);
      try {
         HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
         HSSFSheet hssfSheet = hssfWorkbook.createSheet();
@@ -308,7 +344,7 @@
            hssfCell.setCellStyle(style);
            hssfCell.setCellValue(titles.get(l));
         }
			
         List<Icon> icons = iconService.selectList(null);
         HSSFPatriarch patriarch = hssfSheet.createDrawingPatriarch();
         for (int i = 0; i < mapList.size(); i++) {
            hssfRow = hssfSheet.createRow(i + 2);
@@ -352,6 +388,17 @@
               Integer status1 = Integer.valueOf(map.get("status").toString());
               //执行状态（1=待执行，2=进行中，3=成功，4=失败）
               hssfCell4.setCellValue(1 == status1 ? "待执行" : 2 == status1 ? "进行中" : 3 == status1 ? "成功" : "失败");
               //异常
               if(status1 == 5){
                  map.put("imageUrl", icons.stream().filter(s->s.getType() == 2).findFirst().get().getUrl());
               }else if(ToolUtil.isEmpty(map.get("imageUrl"))){
                  //没有图片返回
                  map.put("imageUrl", icons.stream().filter(s->s.getType() == 3).findFirst().get().getUrl());
               }
               //离线
               if(status1 == 4){
                  map.put("imageUrl", icons.stream().filter(s->s.getType() == 1).findFirst().get().getUrl());
               }
            }
            //车牌号
            HSSFCell hssfCell5 = hssfRow.createCell(5);
@@ -424,8 +471,8 @@
            hssfCell11.setCellValue("");
            if(null != map.get("sysStatus")){
               //系统审核状态（1=未执行，2=正常，3=异常）
               Integer sysStatus = Integer.valueOf(map.get("sysStatus").toString());
               hssfCell11.setCellValue(1 == sysStatus ? "未执行" : 2 == sysStatus ? "正常" : "异常");
               Integer sysStatus1 = Integer.valueOf(map.get("sysStatus").toString());
               hssfCell11.setCellValue(1 == sysStatus1 ? "未执行" : 2 == sysStatus1 ? "正常" : "异常");
            }
            //系统审核时间
            HSSFCell hssfCell12 = hssfRow.createCell(12);
@@ -482,4 +529,236 @@
         e.printStackTrace();
      }
   }
   */
   @Override
   public void downloadTaskRecord(List<String> ids, String code, List<Integer> status,Integer sysStatus, Integer type,String typeName,HttpServletResponse response){
      List<Map<String, Object>> mapList = this.baseMapper.getDownloadTaskRecord(ids, code, status, sysStatus, type, typeName);
      try {
         HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
         // 1. 按 name 分组（先获取所有唯一 name）
         Set<Object> nameSet = new HashSet<>();
         for (Map<String, Object> map : mapList) {
            nameSet.add(map.get("name")); // 收集所有不重复的 name
         }

         // 2. 创建单元格样式（提取为公共方法，避免重复代码）
         HSSFCellStyle titleStyle = createTitleStyle(hssfWorkbook);
         HSSFCellStyle contentStyle = createContentStyle(hssfWorkbook);
         List<Icon> icons = iconService.selectList(null);

         // 3. 为每个 name 创建一个 Sheet
         for (Object name : nameSet) {
            if (name == null) continue; // 跳过 name 为 null 的情况

            // 创建 Sheet（以 name 为 Sheet 名称，可根据需求自定义）
            HSSFSheet sheet = hssfWorkbook.createSheet(name.toString());
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeight((short) 800);

            // 设置表头（与原逻辑一致）
            initSheetHeader(sheet, hssfWorkbook, titleStyle);

            // 筛选当前 name 的数据
            List<Map<String, Object>> groupData = mapList.stream()
                  .filter(map -> name.equals(map.get("name")))
                  .collect(Collectors.toList());

            // 填充当前 Sheet 的数据
            fillSheetData(sheet, hssfWorkbook, groupData, contentStyle, icons);
         }

         // 4. 输出 Excel 到响应
         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
         response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("任务记录(" + sdf.format(new Date()) + ").xls", "utf-8"));
         response.setContentType("application/vnd.ms-excel");
         ServletOutputStream out = response.getOutputStream();
         hssfWorkbook.write(out);
         out.flush();
         out.close();

      } catch (Exception e) {
         e.printStackTrace();
      }
   }
   // -------------------- 以下是抽取的工具方法 --------------------

   /**
    * 创建标题样式（加粗、居中）
    */
   private HSSFCellStyle createTitleStyle(HSSFWorkbook workbook) {
      HSSFCellStyle style = workbook.createCellStyle();
      style.setVerticalAlignment(VerticalAlignment.CENTER);
      style.setAlignment(HorizontalAlignment.CENTER);
      HSSFFont font = workbook.createFont();
      font.setBold(true);
      style.setFont(font);
      return style;
   }

   /**
    * 创建内容样式（居中，不加粗）
    */
   private HSSFCellStyle createContentStyle(HSSFWorkbook workbook) {
      HSSFCellStyle style = workbook.createCellStyle();
      style.setVerticalAlignment(VerticalAlignment.CENTER);
      style.setAlignment(HorizontalAlignment.CENTER);
      return style;
   }

   /**
    * 初始化 Sheet 表头（合并单元格 + 标题）
    */
   private void initSheetHeader(HSSFSheet sheet, HSSFWorkbook workbook, HSSFCellStyle titleStyle) {
      // 合并单元格
      for (int i = 0; i < 10; i++) {
         CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, i, i);
         sheet.addMergedRegion(cellRangeAddress);
      }
      CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 10, 16);
      sheet.addMergedRegion(cellRangeAddress);

      // 第一行标题
      HSSFRow row0 = sheet.createRow(0);
      List<String> titles0 = Arrays.asList("序号", "任务名称", "任务类型", "执行时间", "执行状态", "车牌号", "运输公司", "年审有效期", "巡检状态", "车辆轨迹图片", "货箱通道");
      for (int i = 0; i < titles0.size(); i++) {
         HSSFCell cell = row0.createCell(i);
         cell.setCellType(CellType.STRING);
         cell.setCellStyle(titleStyle);
         cell.setCellValue(titles0.get(i));
      }

      // 第二行标题
      HSSFRow row1 = sheet.createRow(1);
      List<String> titles1 = Arrays.asList("图片", "系统审核", "系统审核时间", "人工审核", "审核意见", "审核人", "审核时间");
      for (int i = 0; i < titles1.size(); i++) {
         HSSFCell cell = row1.createCell(i + 10);
         cell.setCellType(CellType.STRING);
         cell.setCellStyle(titleStyle);
         cell.setCellValue(titles1.get(i));
      }
   }

   /**
    * 填充 Sheet 数据（根据分组数据填充行）
    */
   private void fillSheetData(HSSFSheet sheet, HSSFWorkbook workbook, List<Map<String, Object>> dataList, HSSFCellStyle contentStyle, List<Icon> icons) {
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 用于插入图片

      for (int i = 0; i < dataList.size(); i++) {
         HSSFRow row = sheet.createRow(i + 2); // 从第3行开始（索引2）
         Map<String, Object> map = dataList.get(i);

         // 1. 序号
         setCellValue(row, 0, (i + 1) + "", contentStyle);

         // 2. 任务名称
         setCellValue(row, 1, map.get("name"), contentStyle);

         // 3. 任务类型
         setCellValue(row, 2, map.get("taskType"), contentStyle);

         // 4. 执行时间
         setCellValue(row, 3, map.get("executionTime"), contentStyle);

         // 5. 执行状态（带状态转换逻辑）
         Object statusObj = map.get("status");
         String statusValue = "";
         if (statusObj != null) {
            Integer status = Integer.valueOf(statusObj.toString());
            statusValue = status == 1 ? "待执行" : status == 2 ? "进行中" : status == 3 ? "成功" : "失败";

            // 处理图片逻辑（根据状态设置默认图片）
            if (status == 5) {
               map.put("imageUrl", icons.stream().filter(s -> s.getType() == 2).findFirst().orElse(null).getUrl());
            } else if (ToolUtil.isEmpty(map.get("imageUrl"))) {
               map.put("imageUrl", icons.stream().filter(s -> s.getType() == 3).findFirst().orElse(null).getUrl());
            }
            if (status == 4) {
               map.put("imageUrl", icons.stream().filter(s -> s.getType() == 1).findFirst().orElse(null).getUrl());
            }
         }
         setCellValue(row, 4, statusValue, contentStyle);

         // 6. 车牌号
         setCellValue(row, 5, map.get("vehicleNum"), contentStyle);

         // 7. 运输公司
         setCellValue(row, 6, map.get("companyName"), contentStyle);

         // 8. 年审有效期
         setCellValue(row, 7, map.get("inspectPeriodEnd"), contentStyle);

         // 9. 巡检状态
         Object vehiclesStatusObj = map.get("vehiclesStatus");
         String vehiclesStatusValue = "";
         if (vehiclesStatusObj != null) {
            Integer vehiclesStatus = Integer.valueOf(vehiclesStatusObj.toString());
            vehiclesStatusValue = vehiclesStatus == 1 ? "未执行" : vehiclesStatus == 2 ? "正常" : "异常";
         }
         setCellValue(row, 8, vehiclesStatusValue, contentStyle);

         // 10. 车辆轨迹图片（占位，实际图片在第10列插入）
         setCellValue(row, 9, "", contentStyle);

         // 11. 图片（插入图片到第10列）
         Object imageUrlObj = map.get("imageUrl");
         if (imageUrlObj != null) {
            try {
               URL url = new URL(imageUrlObj.toString());
               URLConnection conn = url.openConnection();
               conn.connect();
               InputStream is = conn.getInputStream();
               byte[] bytes = IOUtils.toByteArray(is);
               int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
               is.close();

               // 图片位置：第10列（索引10），当前行（i+2）
               HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 10, i + 2, (short) 11, i + 3);
               patriarch.createPicture(anchor, pictureIdx);
            } catch (Exception e) {
               e.printStackTrace();
            }
         }

         // 12. 系统审核
         Object sysStatusObj = map.get("sysStatus");
         String sysStatusValue = "";
         if (sysStatusObj != null) {
            Integer sysStatus = Integer.valueOf(sysStatusObj.toString());
            sysStatusValue = sysStatus == 1 ? "未执行" : sysStatus == 2 ? "正常" : "异常";
         }
         setCellValue(row, 11, sysStatusValue, contentStyle);

         // 13. 系统审核时间
         setCellValue(row, 12, map.get("sysCreateTime"), contentStyle);

         // 14. 人工审核
         Object artificialStatusObj = map.get("artificialStatus");
         String artificialStatusValue = "";
         if (artificialStatusObj != null) {
            Integer artificialStatus = Integer.valueOf(artificialStatusObj.toString());
            artificialStatusValue = artificialStatus == 1 ? "未执行" : artificialStatus == 2 ? "正常" : "异常";
         }
         setCellValue(row, 13, artificialStatusValue, contentStyle);

         // 15. 审核意见
         setCellValue(row, 14, map.get("remark"), contentStyle);

         // 16. 审核人
         setCellValue(row, 15, map.get("artificialUser"), contentStyle);

         // 17. 审核时间
         setCellValue(row, 16, map.get("artificialCreateTime"), contentStyle);
      }
   }

   /**
    * 通用单元格赋值方法（处理 null 情况）
    */
   private void setCellValue(HSSFRow row, int cellIndex, Object value, HSSFCellStyle style) {
      HSSFCell cell = row.createCell(cellIndex);
      cell.setCellType(CellType.STRING);
      cell.setCellStyle(style);
      cell.setCellValue(value != null ? value.toString() : "");
   }
}