从0到1构建MES系统15-自定义报表

今天我们来讲一下自定义报表。

1. 概述

本文介绍如何构建企业级动态报表系统，支持SQL动态配置、字段自定义与智能字典解析。直接在web界面就可以实现报表数据展示功能。

2. 背景

很多时候在业务系统中都需要做各种各样的业务统计报表，往往我们做一个报表功能需要：前端写一个表格通过访问后端接口获取数据，后端需要写Controller、service、mapper等功能，实际上我们除了很复杂的报表需要用java代码写数据处理逻辑其他的基本上都是通过sql语句实现，所以为了更快实现报表功能，我们可以直接在web端把sql转成所需的报表。

3. 功能讲解

3.1 新建自定义报表

在【自定义报表】页面点击【新增】按钮

• 报表名称：定义报表名称，作用
• 报表编码：定义报表编码，编码不能重复，通过报表编码获取到报表数据和展示
• 报表SQL：报表数据查询sql，建议在数据库客户端把sql写好再复制过来。
• SQL解析：执行报表SQL解析该sql查询的字段信息
• 报表字段：定义报表显示列，那些字段可以作为查询条件
• 报表参数：定义动态传入sql的参数信息

• 字段名：报表SQL解析出来的字段名信息
• 字段描述：前端报表界面中列的名称
• 字段类型：报表SQL解析出来数据库字段类型
• 是否显示：是否在报表界面中显示该列，如果不勾选则不显示
• 是否查询：该列是否在报表中生成一个查询条件框
• 字段编码：如果该字段使用的是字典编码，定义对应的系统字典编码，查询的时候会自动翻译成字典名称

• 参数名：动态传入sql中的参数名称，在sql中用${status}方式使用
• 参数描述：参数说明
• 默认值：如果没有该参数没有传入值，使用默认值

3.2 报表sql

SQL可以使用Freemarker中的语法来构建出复杂的SQL语句，后端会使用Freemarker引擎转换成可以执行的SQL语句。 使用例子：

//您可以键入“”作为一个参数，这里abc是参数的名称。例如： 
select * from table where id = ${abc} 
select * from table where id like concat('%',${abc},'%')(mysql模糊查询) 
select * from table where id like '%'||${abc}||'%'。(oracle模糊查询) 
select * from table where id like '%'+${abc}+'%'。(sqlserver模糊查询) 

使用IF-ELSE

SELECT u.* 
FROM sys_user u 
LEFT JOIN ${relate_table_name} wu ON u.id  = wu.user_id 
<#if biz_id?has_content>
  AND wu.${relate_filed}  = ${biz_id}
</#if>  
WHERE wu.user_id IS NULL and u.status = 1

定义了报表参数 biz_id，如果调用自定义报表的时候没有传入 biz_id则不会带上AND wu.${relate_filed} = ${biz_id}

3.3 测试自定义报表

新增完自定义报表后，需要查看报表的是否正常，可以在自定义报表列表中-操作列 点击【展示数据】

这里以 物料查询报表 为例，定义六个字段，两个查询条件。

3.4 选择弹出框

很多时候我们都需要在表单填写中，弹出选择框，选择一条或多条数据回填到表单中，这样的需要也可以通过自定义表单实现。以下用一个单选案例来讲解。 销售订单选择客户信息

4. 功能实现

这里主要把重点的代码拿出来讲解，如果需要详细代码可以在gitee上查看，代码已开源。

后端核心代码路径： src/main/java/com/hgyc/mom/tool/service/impl/CustomReportServiceImpl.java

4.1 报表SQL解析

public List<CustomReportField> parseSql(CustomReportVO customReportVO) {  
    List<CustomReportField> customReportFields = new ArrayList<>();  
    String sql = customReportVO.getSqlScript();  
  
    // 1.校验sql是否为空  
    if (StringUtils.isEmpty(sql)) {  
        throw new BizException("sql脚本不能为空" );  
    }  
  
    // 2. 检验是否有参数，参数是否已经配置  
    List<String> sqlVariables = this.getSqlVariables(sql);  
    List<CustomReportParam> customReportParams = customReportVO.getCustomReportParams();  
    // 提取 paramName 列表  
    Set<String> paramNames = customReportParams.stream()  
            .map(CustomReportParam::getParamName)  
            .collect(Collectors.toSet());  
  
    // 找出没有匹配到的变量  
    List<String> unmatchedVariables = sqlVariables.stream()  
            .filter(var -> !paramNames.contains(var))  
            .collect(Collectors.toList());  
  
    if (!unmatchedVariables.isEmpty()) {  
        log.error("缺少参数配置：", unmatchedVariables);  
        throw new BizException("sql脚本存在参数，请配置参数：" + unmatchedVariables);  
    }  
  
    String parsedSql = "";  
    // 3. 获取解析之后的sql  
    Map<String, Object> parseValue = this.getParseValue(customReportParams);  
    try {  
        parsedSql = FreeMarkerUtils.processTemplateStr(sql, parseValue);  
    } catch (Exception e) {  
        log.error("解析sql脚本模板失败", e);  
        throw new BizException("解析sql脚本异常，请检测脚本是否正确！");  
    }  
  
    // 4. 执行sql语句  
    if (StringUtils.isNotEmpty(parsedSql)) {  
        customReportFields = this.getSqlResultInfo(parsedSql);  
    }  
  
    return customReportFields;  
}

1. 校验sql是否为空
2. 检验是否有参数，参数是否已经配置
3. 提出参数，检验参数配置
4. 使用Freemarker引擎解析sql
5. 执行sql提取查询字段

4.2 获取自定义报表数据

public Page<Map<String, Object>> getReportData(Page page, CustomReportParamVO customReportParamVO) {  
    String reportCode = customReportParamVO.getReportCode();  
    Map<String, Object> params = customReportParamVO.getParams();  
  
    CustomReport customReport = this.getByCode(reportCode);  
    if (customReport == null) {  
        throw new BizException("报表：[" + reportCode + "]" + "不存在");  
    }  
  
    List<CustomReportParam> reportParams = customReportParamService.getByReportId(customReport.getId());  
    Map<String, Object> parseValue = this.getParseValue(reportParams, params);  
  
    String parsedSql = "";  
    // 1. 解析sql  
    try {  
        parsedSql = FreeMarkerUtils.processTemplateStr(customReport.getSqlScript(), parseValue);  
    } catch (Exception e) {  
        log.error("解析sql脚本模板失败", e);  
        throw new BizException("解析sql脚本异常，请检测脚本是否正确！");  
    }  
  
    // 2. 添加查询条件  
    List<CustomReportField> reportFields = customReportFieldService.getByReportId(customReport.getId());  
    String conditionSql = this.buildConditionSql(parsedSql, reportFields, params);  
  
    // 3. 添加分页  
    String countSql = SqlUtil.getCountSql(conditionSql);  
    String pageSql = SqlUtil.getPageSql(conditionSql, page, dataSource);  
  
    long totalCount = this.getTotalCount(countSql);  
    List<Map<String, Object>> records = this.executeSql(pageSql);  
    page.setTotal(totalCount);  
    page.setRecords(records);  
  
    return page;  
}

1. 解析sql
2. 添加查询条件
3. 添加分页
4. 执行sql

4.3 页面中使用自定义报表

<DynamicQueryTable reportCode={showCode} />

说明： DynamicQueryTable为自定义组件，reportCode为自定义报表的编码

4.4 弹窗选择器

用户单选

import { Modal } from "antd";
import DynamicQueryTable, { SelectionType } from "@/components/report/DynamicQueryTable";
import { useState } from "react";
import type { UserInfo } from "#/entity";

interface SingleUserSelectorProps {
	open: boolean;
	onCancel: () => void;
	onOk: (selectedUser?: UserInfo) => void;
}

const SingleUserSelector:React.FC<SingleUserSelectorProps> = ({ open, onCancel, onOk }) => {

// 选中的用户
const [selectedUser, setSelectedUser] = useState<UserInfo>();

const handleOk = () => {
	onOk(selectedUser);
};

return (
		<Modal title="选择用户" open={open} onCancel={onCancel} onOk={handleOk} width={700} destroyOnClose>
			<DynamicQueryTable
			reportCode="single_user_select_report"
			selectionType={SelectionType.RADIO}
			onSelectChange={(selectedRows) => {setSelectedUser(selectedRows[0])}}
			/>
		</Modal>
	);
}
export default SingleUserSelector;

说明：需要自定义个组件，通过使用<DynamicQueryTable>实现单选弹出框。

DynamicQueryTable参数说明：

• reportCode：自定义报表编码
• selectionType: 单选或多选
• onSelectChange: 选中事件

本文源码已上传Gitee 开源项目地址：

• 前端仓库
• 后端仓库

欢迎在评论区分享你的技术选型经验，或对本文方案的改进建议！

关注公众号「慧工云创」