AJ-Report-SQL资源

package com.anjiplus.template.gaea.business.modules.reportexcel.service.impl; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.alibaba.fastjson.serializer.SerializerFeature; import com.anji.plus.gaea.constant.BaseOperationEnum; import com.anji.plus.gaea.curd.mapper.GaeaBaseMapper; import com.anji.plus.gaea.exception.BusinessException; import com.anji.plus.gaea.exception.BusinessExceptionBuilder; import com.anji.plus.gaea.utils.GaeaAssert; import com.anji.plus.gaea.utils.GaeaBeanUtils; import com.anjiplus.template.gaea.business.code.ResponseCode; import com.anjiplus.template.gaea.business.enums.ExportTypeEnum; import com.anjiplus.template.gaea.business.modules.dataset.controller.dto.DataSetDto; import com.anjiplus.template.gaea.business.modules.dataset.controller.dto.OriginalDataDto; import com.anjiplus.template.gaea.business.modules.dataset.service.DataSetService; import com.anjiplus.template.gaea.business.modules.file.service.GaeaFileService; import com.anjiplus.template.gaea.business.modules.file.util.FileUtils; import com.anjiplus.template.gaea.business.modules.report.dao.ReportMapper; import com.anjiplus.template.gaea.business.modules.report.dao.entity.Report; import com.anjiplus.template.gaea.business.modules.reportexcel.controller.dto.ReportExcelDto; import com.anjiplus.template.gaea.business.modules.reportexcel.controller.dto.ReportExcelStyleDto; import com.anjiplus.template.gaea.business.modules.reportexcel.dao.ReportExcelMapper; import com.anjiplus.template.gaea.business.modules.reportexcel.dao.entity.ReportExcel; import com.anjiplus.template.gaea.business.modules.reportexcel.service.ReportExcelService; import com.anjiplus.template.gaea.business.modules.reportexcel.util.CellType; import com.anjiplus.template.gaea.business.modules.reportexcel.util.XlsUtil; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.itextpdf.text.*; import com.itextpdf.text.pdf.BaseFont; import com.itextpdf.text.pdf.PdfPCell; import com.itextpdf.text.pdf.PdfPTable; import com.itextpdf.text.pdf.PdfWriter; import org.apache.commons.lang3.StringUtils; import org.apache.poi.openxml4j.util.ZipSecureFile; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.nio.file.Files; import java.nio.file.Paths; import java.util.List; import java.util.*; import java.util.stream.Collectors; /** * TODO * * @author chenkening * @date 2021/4/13 15:14 */ @Service public class ReportExcelServiceImpl implements ReportExcelService { private Logger logger = LoggerFactory.getLogger(this.getClass()); @Autowired private ReportExcelMapper reportExcelMapper; @Autowired private DataSetService dataSetService; @Autowired private GaeaFileService gaeaFileService; @Autowired private ReportMapper reportMapper; @Value("${customer.file.tmp-path:.}") private String dictPath; private final static String ZIP_PATH = "/tmp_zip/"; @Override public GaeaBaseMapper<ReportExcel> getMapper() { return reportExcelMapper; } @Override public ReportExcelDto detailByReportCode(String reportCode) { QueryWrapper<ReportExcel> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("report_code", reportCode); ReportExcel reportExcel = reportExcelMapper.selectOne(queryWrapper); if (reportExcel != null) { ReportExcelDto dto = new ReportExcelDto(); BeanUtils.copyProperties(reportExcel, dto); return dto; } return null; } /** * 操作前处理 * * @param entity 前端传递的对象 * @param operationEnum 操作类型 * @throws BusinessException 阻止程序继续执行或回滚事务 */ @Override public void processBeforeOperation(ReportExcel entity, BaseOperationEnum operationEnum) throws BusinessException { if (operationEnum.equals(BaseOperationEnum.INSERT)) { String reportCode = entity.getReportCode(); ReportExcel report = this.selectOne("report_code", reportCode); if (null != report) { this.deleteById(report.getId()); } } } /** * 报表预览 */ @Override public ReportExcelDto preview(ReportExcelDto reportExcelDto) { // 根据id查询 报表详情 ReportExcel reportExcel = selectOne("report_code", reportExcelDto.getReportCode()); QueryWrapper<Report> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("report_code", reportExcelDto.getReportCode()); Report report = reportMapper.selectOne(queryWrapper); GaeaAssert.notNull(reportExcel, ResponseCode.RULE_CONTENT_NOT_EXIST, "reportExcel"); String setParam = reportExcelDto.getSetParam(); GaeaBeanUtils.copyAndFormatter(reportExcel, reportExcelDto); if (StringUtils.isNotBlank(setParam)) { reportExcelDto.setSetParam(setParam); } reportExcelDto.setReportName(report.getReportName()); // 数据集解析 String jsonStr = analysisReportData(reportExcelDto); reportExcelDto.setJsonStr(jsonStr); // reportExcelDto.setTotal(jsonObject.getJSONObject("rows").size()); return reportExcelDto; } /** * Excel导出 * * @param request * @param response * @param reportExcelDto * @return * @throws IOException */ @Override public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response, ReportExcelDto reportExcelDto) throws IOException { String userAgent = request.getHeader("User-Agent"); boolean isIeBrowser = userAgent.indexOf("MSIE") > 0; String reportCode = reportExcelDto.getReportCode(); String exportType = reportExcelDto.getExportType(); List<List<ReportExcelStyleDto>> reportExcelStyleList = new ArrayList<>(); JSONObject rowData = JSON.parseObject(reportExcelDto.getRowDatas()); // 将JSONObject对象转换为列表 List<Integer> dataNumList = rowData.keySet().stream().map(Integer::parseInt).sorted().collect(Collectors.toList()); for (Integer i : dataNumList) { JSONArray jsonArray = rowData.getJSONArray(i.toString()); List<ReportExcelStyleDto> reportExcelStyleDtos = jsonArray.toJavaList(ReportExcelStyleDto.class); reportExcelStyleList.add(reportExcelStyleDtos); } ReportExcelDto report = detailByReportCode(reportCode); reportExcelDto.setJsonStr(report.getJsonStr()); String jsonStr = analysisReportData(reportExcelDto); List<JSONObject> lists = (List<JSONObject>) JSON.parse(jsonStr); OutputStream out = null; File file = null; File pdfFile = null; String fileExtName = ".xlsx"; String fileName = report.getReportCode(); File dir = new File(dictPath + ZIP_PATH); if (!dir.exists()) { dir.mkdirs(); } String filePath = dir.getAbsolutePath() + File.separator + fileName + ".xlsx"; file = new File(filePath); out = Files.newOutputStream(Paths.get(filePath)); XlsUtil.exportXlsF