新增数据同步方式及配置，当选择为sql时则为数据库直连同步，全局替换所有的同步方法，用户修改，离职，注销修改调整，文件上传去除同步推送上传

zhongrj

2022-08-22 3c607f075990d6d3238daa7db1c68eaae8126424

 src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java

@@ -1,11 +1,20 @@
package org.springblade.modules.accreditation.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.baomidou.mybatisplus.core.metadata.IPage;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.AllArgsConstructor;
import org.apache.commons.codec.Charsets;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springblade.common.utils.ConvertUtil;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.mp.support.Query;
import org.springblade.core.tool.api.R;
@@ -14,14 +23,23 @@
import org.springblade.modules.FTP.FtpUtil;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcel;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcelVO;
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.service.IUserService;
import org.springblade.modules.accreditation.entity.AccreditationRecords;
import org.springblade.modules.accreditation.service.AccreditationRecordsService;
import org.springblade.modules.accreditation.vo.AccreditationRecordsVo;
import org.springblade.modules.system.service.MyAsyncService;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.web.bind.annotation.*;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
@@ -39,7 +57,7 @@
   private final AccreditationRecordsService accreditationRecordsService;

   private final IUserService userService;

   private final MyAsyncService myAsyncService;

   /**
    * 自定义分页
@@ -53,13 +71,40 @@
   }

   /**
    * 新增
    * 新增（补证）
    * @param accreditationRecords 制证记录信息对象
    */
   @PostMapping("/save")
   @ApiOperation(value = "新增", notes = "传入accreditationRecords")
   public R save(@RequestBody AccreditationRecords accreditationRecords){
      return R.data(accreditationRecordsService.save(accreditationRecords));
      accreditationRecords.setCreateTime(new Date());
      accreditationRecords.setStatus(1);
      accreditationRecords.setAuditStatus(1);
      boolean save = accreditationRecordsService.save(accreditationRecords);

      //修改用户信息
      User user = new User();
      user.setId(accreditationRecords.getUserId());
      user.setUpdateTime(new Date());
      //修改为未制证状态
      user.setUserType(7);
      userService.updateById(user);
      //内网同步
      //内网新增
      String s = "insert into sys_accreditation_records(id,user_id,create_time,create_user,status,type,audit_status) " +
         "values(" + "'" + accreditationRecords.getId() + "'" + ","
         + "'" + accreditationRecords.getUserId() + "'" + ","
         + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getCreateTime()) + "'" + ","
         + "'" + accreditationRecords.getCreateUser() +"'" + ","
         + "'" + accreditationRecords.getStatus() +"'" + ","
         + "'" + accreditationRecords.getType() +"'" + ","
         +"'" + accreditationRecords.getAuditStatus() + "'" + ");" +
      "update blade_user set user_type = " + "'" + user.getUserType() + "'" +
         ",update_time = " + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getUpdateTime()) + "'" +
         " " + "where id = " + "'" + user.getId() + "'";
      //FtpUtil.sqlFileUpload(s);
      myAsyncService.dataSync(s);
      return R.data(save);
   }


@@ -69,9 +114,17 @@
    * @param accreditationRecords 制证记录信息对象
    */
   @PostMapping("/securityApply")
   @ApiOperation(value = "新增", notes = "传入accreditationRecords")
   public R securityApply(@RequestBody AccreditationRecordsVo accreditationRecords){
      return R.data(accreditationRecordsService.securityApply(accreditationRecords));
   }

   /**
    * 批量修改为已制证
    * @param accreditationRecords 制证记录信息对象
    */
   @PostMapping("/batchAccreditation")
   public R batchAccreditation(@RequestBody AccreditationRecordsVo accreditationRecords){
      return R.data(accreditationRecordsService.batchAccreditation(accreditationRecords));
   }


@@ -82,15 +135,53 @@
   @PostMapping("/audit")
   public R audit(@RequestBody AccreditationRecords accreditationRecords){
      accreditationRecords.setAuditTime(new Date());

      boolean b = accreditationRecordsService.updateById(accreditationRecords);
      //内网同步
      String s =
         "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" +
            ",audit_detail = " + accreditationRecords.getAuditDetail() + "" +
            ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" +
            ",audit_user = " + accreditationRecords.getAuditUser() + " " +
            "where id = " + "'" + accreditationRecords.getId() + "'";
      FtpUtil.sqlFileUpload(s);
      //审核通过
      if (accreditationRecords.getAuditStatus()==2){
         //修改为未制证状态
         AccreditationRecords records = accreditationRecordsService.getById(accreditationRecords);
         User user = userService.getById(records.getUserId());
         if(accreditationRecords.getType()==2) {
            user.setUserType(7);
            user.setUpdateTime(new Date());
            //更新
            userService.updateById(user);

            //内网同步
            String s =
               "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" +
                  ",audit_detail = " + accreditationRecords.getAuditDetail() + "" +
                  ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" +
                  ",audit_user = " + accreditationRecords.getAuditUser() + " " +
                  "where id = " + "'" + accreditationRecords.getId() + "';" +
                  "update blade_user set user_type = " + user.getUserType() + "" +
                  ",update_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getUpdateTime()) + "" +
                  "where id = " + "'" + user.getId() + "'";
            //FtpUtil.sqlFileUpload(s);
            myAsyncService.dataSync(s);
         }else {
            //内网同步
            String s =
               "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" +
                  ",audit_detail = " + accreditationRecords.getAuditDetail() + "" +
                  ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" +
                  ",audit_user = " + accreditationRecords.getAuditUser() + " " +
                  "where id = " + "'" + accreditationRecords.getId() + "'";
            //FtpUtil.sqlFileUpload(s);
            myAsyncService.dataSync(s);
         }
      }else {
         //内网同步
         String s =
            "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" +
               ",audit_detail = " + accreditationRecords.getAuditDetail() + "" +
               ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" +
               ",audit_user = " + accreditationRecords.getAuditUser() + " " +
               "where id = " + "'" + accreditationRecords.getId() + "'";
         //FtpUtil.sqlFileUpload(s);
         myAsyncService.dataSync(s);
      }
      //返回
      return R.status(b);
   }
@@ -110,14 +201,51 @@
         accreditationRecords.setId(Long.parseLong(id));
         accreditationRecords.setAuditTime(new Date());
         accreditationRecordsService.updateById(accreditationRecords);
         //内网同步
         String s =
            "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" +
               ",audit_detail = " + accreditationRecords.getAuditDetail() + "" +
               ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" +
               ",audit_user = " + accreditationRecords.getAuditUser() + " " +
               "where id = " + "'" + accreditationRecords.getId() + "'";
         FtpUtil.sqlFileUpload(s);
         //审核通过
         if (accreditationRecords.getAuditStatus()==2){
            if (accreditationRecords.getType()==2) {
               //修改为未制证状态
               AccreditationRecords records = accreditationRecordsService.getById(id);
               User user = userService.getById(records.getUserId());
               user.setUserType(7);
               user.setUpdateTime(new Date());
               //更新
               userService.updateById(user);

               //内网同步
               String s =
                  "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" +
                     ",audit_detail = " + accreditationRecords.getAuditDetail() + "" +
                     ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" +
                     ",audit_user = " + accreditationRecords.getAuditUser() + " " +
                     "where id = " + "'" + accreditationRecords.getId() + "';" +
                     "update blade_user set user_type = " + user.getUserType() + "" +
                     ",update_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getUpdateTime()) + "" +
                     "where id = " + "'" + user.getId() + "'";
               //FtpUtil.sqlFileUpload(s);
               myAsyncService.dataSync(s);
            }else {
               //内网同步
               String s =
                  "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" +
                     ",audit_detail = " + accreditationRecords.getAuditDetail() + "" +
                     ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" +
                     ",audit_user = " + accreditationRecords.getAuditUser() + " " +
                     "where id = " + "'" + accreditationRecords.getId() + "'";
               //FtpUtil.sqlFileUpload(s);
               myAsyncService.dataSync(s);
            }
         }else {
            //内网同步
            String s =
               "update sys_accreditation_records set audit_status = " + accreditationRecords.getAuditStatus() + "" +
                  ",audit_detail = " + accreditationRecords.getAuditDetail() + "" +
                  ",audit_time = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accreditationRecords.getAuditTime()) + "" +
                  ",audit_user = " + accreditationRecords.getAuditUser() + " " +
                  "where id = " + "'" + accreditationRecords.getId() + "'";
            //FtpUtil.sqlFileUpload(s);
            myAsyncService.dataSync(s);
         }
      });
      //返回
      return R.status(true);
@@ -175,7 +303,7 @@
   }

   /**
    * 导出保安员证信息
    * 导出保安员证信息（上岗证）
    */
   @GetMapping("export-security-paper")
   public void exportSecurityPaper(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws IOException {
@@ -226,4 +354,271 @@
   }


   /**
    * 导出证书制证信息(包含照片)
    */
   @GetMapping("export-security-book-papers")
   public void exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws Exception {
      //按条件查询成绩数据
      List<AccreditationRecordsVo> excels = accreditationRecordsService.exportSecurityBookPapers(accreditationRecords);
      List<ExportSecurityPaperExcelVO> list = new ArrayList<>();
      //数据转换封装
      excels.forEach(accreditationRecordsVo -> {
         ExportSecurityPaperExcelVO exportSecurityPaperExcelVO = new ExportSecurityPaperExcelVO();
         //如果头像有
         if (null != accreditationRecordsVo.getAvatar() && accreditationRecordsVo.getAvatar() != "") {
            exportSecurityPaperExcelVO.setAvatar(accreditationRecordsVo.getAvatar());
         }
         exportSecurityPaperExcelVO.setName(accreditationRecordsVo.getRealName()+accreditationRecordsVo.getIdCardNo());
         //封装
         list.add(exportSecurityPaperExcelVO);
      });
      // 声明一个工作薄
      HSSFWorkbook workBook = new HSSFWorkbook();
      // 生成一个表格
      HSSFSheet sheet = workBook.createSheet();
      //首行锁定
//      sheet.createFreezePane(0, 1, 0, 1);
      workBook.setSheetName(0, "证书打印信息");
      CellStyle style = workBook.createCellStyle();
      Font font = workBook.getFontAt((short) 0);
      font.setCharSet(HSSFFont.DEFAULT_CHARSET);
      //更改默认字体大小
      font.setFontHeightInPoints((short) 12);
      font.setFontName("宋体");
      style.setFont(font);
      // 上下居中
      style.setVerticalAlignment(VerticalAlignment.CENTER);
      //默认宽高
      sheet.setDefaultColumnWidth((short)11);

      // 创建表格标题行 第一行
//      HSSFRow titleRow = sheet.createRow(0);
//      titleRow.setHeight((short) 300);
//      titleRow.createCell(0).setCellValue("员工姓名");
//      titleRow.createCell(1).setCellValue("员工照片");
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
      //如果总数小于等于7
      if (list.size()<=7){
         HSSFRow row = sheet.createRow(0);
         row.setHeight((short) 1980);

         HSSFRow row1 = sheet.createRow(1);
         row1.setHeight((short) 300);

         for (int i = 0; i < list.size(); i++) {
            ExportSecurityPaperExcelVO checkWorkVo = list.get(i);
            row1.createCell(i).setCellValue(checkWorkVo.getName());
            //如果头像有
            if (null != list.get(i).getAvatar() && list.get(i).getAvatar() != "") {
               if (list.get(i).getAvatar().contains("http:")) {
                  //获取图片格式
                  int lastIndexOf = list.get(i).getAvatar().lastIndexOf(".");
                  String pictureType = list.get(i).getAvatar().substring(lastIndexOf + 1);
                  //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                  BufferedImage bufferImg = null;
                  //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
                  ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                  try {
                     bufferImg = ImageIO.read(new URL(list.get(i).getAvatar()));
                  } catch (IOException e) {
                     e.printStackTrace();
                  }
                  try {
                     ImageIO.write(bufferImg, pictureType, byteArrayOut);
                  } catch (IOException e) {
                     e.printStackTrace();
                  }
                  //anchor主要用于设置图片的属性
                  HSSFClientAnchor anchor =
                     new HSSFClientAnchor
                        (        0, //x缩放
                           0, // y缩放
                           1023, //最大1023
                           255, //最大255
                           (short) i,  //宽度 0开始
                           0, //在第几行
                           (short) i, //宽度 0开始
                           0 //第几列
                        );
                  //插入图片
                  patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
               }
            }
         }
      }else {
         //总数大于7
         List<ExportSecurityPaperExcelVO> excelList = new ArrayList<>();
         //标记行,初始为0
         int rowNum = -2;
         for (int i = 0; i < list.size(); i++) {
            //每次从集合中读取7个对象,求余数
            if (i != 0 && (i + 1) % 7 == 0) {
               excelList.add(list.get(i));
               rowNum = rowNum + 2;
               //写入表格
               writeBookForEach(excelList,rowNum,workBook,sheet,patriarch);
               //清空集合
               excelList.clear();
            } else {
               excelList.add(list.get(i));
            }
         }
         //判断余下的
         if (excelList.size()>0){
            rowNum = rowNum + 2;
            //写入表格
            writeBookForEach(excelList,rowNum,workBook,sheet,patriarch);
         }
      }
      //导出数据
      response.setContentType("application/vnd.ms-excel");
      response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
      String fileName = URLEncoder.encode("证书打印信息导出"+ DateUtil.time(), Charsets.UTF_8.name());
      response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
      workBook.write(response.getOutputStream());
   }

   /**
    * 循环写入表格
    * @param list 每行需要插入的数据
    * @param rowNum 行标记
    * @param workBook book
    * @param sheet sheet
    * @param patriarch 画图对象
    */
   private void writeBookForEach(List<ExportSecurityPaperExcelVO> list, int rowNum, HSSFWorkbook workBook, HSSFSheet sheet, HSSFPatriarch patriarch) {
      HSSFRow row = sheet.createRow(rowNum);
      row.setHeight((short) 1980);
      HSSFRow row1 = sheet.createRow(rowNum+1);
      row1.setHeight((short) 300);
      for (int i = 0; i < list.size(); i++) {
         ExportSecurityPaperExcelVO checkWorkVo = list.get(i);
         row1.createCell(i).setCellValue(checkWorkVo.getName());
         //如果头像有
         if (null != list.get(i).getAvatar() && list.get(i).getAvatar() != "") {
            if (list.get(i).getAvatar().contains("http:")) {
               //获取图片格式
               int lastIndexOf = list.get(i).getAvatar().lastIndexOf(".");
               String pictureType = list.get(i).getAvatar().substring(lastIndexOf + 1);
               //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
               BufferedImage bufferImg = null;
               //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
               ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
               try {
                  bufferImg = ImageIO.read(new URL(list.get(i).getAvatar()));
               } catch (IOException e) {
                  e.printStackTrace();
               }
               try {
                  ImageIO.write(bufferImg, pictureType, byteArrayOut);
               } catch (IOException e) {
                  e.printStackTrace();
               }
               //anchor主要用于设置图片的属性
               HSSFClientAnchor anchor =
                  new HSSFClientAnchor
                     (        0, //x缩放
                        0, // y缩放
                        1023, //最大1023
                        255, //最大255
                        (short) i,  //宽度占几格 0开始
                        rowNum, //在第几行
                        (short) i, //宽度占几格 0开始
                        rowNum //第几列
                     );
               //插入图片
               patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
            }
         }
      }
   }


//   /**
//    * 导出证书制证信息(包含照片)  easyExcel 填充
//    */
//   @GetMapping("export-security-book-papers")
//   public void exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws Exception {
//      //按条件查询成绩数据
//      List<AccreditationRecordsVo> excels = accreditationRecordsService.exportSecurityBookPapers(accreditationRecords);
//      List<ExportSecurityPaperExcelVO> list = new ArrayList<>();
//      excels.forEach(accreditationRecordsVo -> {
//         ExportSecurityPaperExcelVO exportSecurityPaperExcelVO = new ExportSecurityPaperExcelVO();
//         //如果头像有
//         if (null != accreditationRecordsVo.getAvatar() && accreditationRecordsVo.getAvatar() != "") {
//            try {
//               if (accreditationRecordsVo.getAvatar().contains("http:")) {
//                  exportSecurityPaperExcelVO.setAvatar(new URL(accreditationRecordsVo.getAvatar()));
//               }
//            } catch (MalformedURLException e) {
//               e.printStackTrace();
//            }
//         }
//         exportSecurityPaperExcelVO.setName(accreditationRecordsVo.getRealName()+accreditationRecordsVo.getIdCardNo());
//         //封装
//         list.add(exportSecurityPaperExcelVO);
//      });
//      String fileName = null;
//      try {
//         response.setContentType("application/vnd.ms-excel");
//         response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
//         fileName = URLEncoder.encode("证书制证数据"+ DateUtil.time(), Charsets.UTF_8.name());
////         response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//         //修改单元格格式为文本格式
////         EasyExcel.write(response.getOutputStream(), AccreditationRecordsVo.class)
////            .sheet("证书制证数据表")
////            .doWrite(excels);
//         // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
//         // {} 代表普通变量 {.} 代表是list的变量securityPaper.xlsx
//         String templateFileName = "xls" + File.separator + "securityPaper.xlsx";
////         Resource resource = new ClassPathResource("xls" + File.separator + "securityPaper.xlsx");
////         InputStream is = resource.getInputStream();
////         ByteArrayOutputStream os = ConvertUtil.parse(is);
//////         ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
////         ExcelWriter excelWriter = EasyExcel.write().file(os).withTemplate(templateFileName).build();
//
//         ExcelWriter excelWriter = null;
//         try {
//            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate
//               (new ClassPathResource(templateFileName).getInputStream()).build();
//         } catch (IOException e) {
//         }
//
//         //设置横向填充
//         FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
//         WriteSheet writeSheet = EasyExcel.writerSheet().build();
//         excelWriter.fill(list, fillConfig, writeSheet);
////         //如果总数小于等于8
////         if (list.size()<=8){
////            WriteSheet writeSheet = EasyExcel.writerSheet().build();
////            excelWriter.fill(list, fillConfig, writeSheet);
////         }else {
////            //总数大于8
////            List<ExportSecurityPaperExcelVO> excelList = new ArrayList<>();
////            for (int i = 0; i < list.size(); i++) {
////               //每次从集合中读取8个对象
////               if (i != 0 && (i + 1) / 8 == 0) {
////                  WriteSheet writeSheet = EasyExcel.writerSheet().build();
////                  excelWriter.fill(excelList, fillConfig, writeSheet);
////                  //清空集合
////                  excelList.clear();
////               } else {
////                  excelList.add(list.get(i));
////               }
////            }
////            //判断余下的
////            if (excelList.size()>0){
////               WriteSheet writeSheet = EasyExcel.writerSheet().build();
////               excelWriter.fill(excelList, fillConfig, writeSheet);
////            }
////         }
//         // 别忘记关闭流
//         excelWriter.finish();
//      } catch (Throwable var6) {
//         throw var6;
//      }
//   }


}