springboot自定义sql的多表查询报表（非实体类）

最新推荐文章于 2024-07-17 09:36:37 发布

hawk_2016

最新推荐文章于 2024-07-17 09:36:37 发布

阅读量909

点赞数

分类专栏： srping boot

本文链接：https://blog.csdn.net/hawk_2016/article/details/117822877

版权

srping boot 专栏收录该内容

7 篇文章 2 订阅

订阅专栏

springboot自定义sql多表报表（非实体类））

概述

在日常开发中，业务部门总有各种报表需求，而系统一开始设计不可能考虑到所有需求。所以，后期得通过拼接SQL语句来拼凑报表。而这些报表是不能用先前的实体类来实现。
我整体用到的有：springboot、Mybatis、layUI、vue、bootstrap、Jquery。
不要问我这么多前段框架，因为身在企业中（bootstrap没有实现总的汇总行，layUI可以）。

说明:在sevice中，汇总行的类名是在明细数据类名后加上Total(类似spring中的约定思想，因为报表基本都要看汇总行，这里是所有行的汇总，不是当前页汇总)
导出的时候字段判断很重要，保证导出来的数值字段不是文本，要不使用人员要抱怨死了，在excel中还得自己转换一下。

以下是我大体的实现代码过程,关键实现是在service层代码,具体代码你们得根据自己情况修改

最后的报表查询效果图：

bootstrap的过滤条件，layUI的表格，可是怪异的组合？
在这里插入图片描述

controller层

// 把用户信息抽离出来
public abstract class AbstractController {
    protected Logger logger = LoggerFactory.getLogger(getClass());
    protected SysUserEntity getUser() {
        return ShiroUtils.getUserEntity();
    }
    protected Long getUserId() {
        return getUser().getUserId();
    }
}

// 通过LinkedHashMap来map所有的sql结果
@RestController
@RequestMapping("/report")
public class ReportController extends AbstractController {

    @Autowired
    private ReportService reportService;

	// 查询结果分页
    @RequestMapping("/reportData")
    public Page<LinkedHashMap<String, Object>> reportData(@RequestBody Map<String, Object> params) {
        return reportService.pages(params);
    }

	// 导出表格数据
    @RequestMapping("/export")
    public R export(@RequestBody Map<String, Object> params) {
        return reportService.export(params);
    }
}

service层

接口层

public interface ReportService {

    Page<LinkedHashMap<String, Object>> pages(Map<String, Object> params);

    R export(Map<String, Object> params);
}

下边附上Page类及其里边的RowBounds类

public class Page<T> extends RowBounds {

    /**
     * 状态码
     */
    protected int code = 0;

    /**
     * 页编号 : 第几页
     */
    protected int pageNo = 1;

    /**
     * 页大小 : 每页的数量
     */
    protected int pageSize = 10;

    /**
     * 偏移量 : 第一条数据在表中的位置
     */
    protected int offset;

    /**
     * 限定数 : 每页的数量
     */
    protected int limit;

    // --结果 --//
    /**
     * 查询结果
     */
    protected List<T> rows = new ArrayList<T>();
    /**
     * 查询结果
     */
    protected List<T> totalRow = new ArrayList<T>();

    /**
     * 总条数
     */
    protected int total;

    /**
     * 总页数
     */
    protected int totalPages;

    /**
     * 数据库类型
     */
    private int type;

    /**
     * 计算偏移量
     */
    private void calcOffset() {
        this.offset = ((pageNo - 1) * pageSize);
    }

    /**
     * 计算限定数
     */
    private void calcLimit() {
        this.limit = pageSize;
    }


    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }

    public Page() {
        this.calcOffset();
        this.calcLimit();
    }

    public Page(int pageNo, int pageSize) {
        this.pageNo = pageNo;
        this.pageSize = pageSize;
        this.calcOffset();
        this.calcLimit();
    }

    public Page(Query search) {
        this.pageNo = search.getAsInt("pageNumber");
        this.pageSize = search.getAsInt("pageSize");
        this.calcOffset();
        this.calcLimit();
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    /**
     * 获得当前页的页号,序号从1开始,默认为1.
     */
    public int getPageNo() {
        return pageNo;
    }

    /**
     * 获得每页的记录数量,默认为1.
     */
    public int getPageSize() {
        return pageSize;
    }

    /**
     * 根据pageNo和pageSize计算当前页第一条记录在总结果集中的位置,序号从1开始.
     */
    public int getFirst() {
        return ((pageNo - 1) * pageSize) + 1;
    }

    /**
     * 根据pageNo和pageSize计算当前页第一条记录在总结果集中的位置,序号从0开始.
     */
    public int getOffset() {
        return offset;
    }

    public int getLimit() {
        return limit;
    }

    /**
     * 取得页内的记录列表.
     */
    public List<T> getRows() {
        return rows;
    }

    /**
     * 设置页内的记录列表.
     */
    public void setRows(final List<T> rows) {
        this.rows = rows;
    }

    public List<T> getTotalRow() {
        return totalRow;
    }

    public void setTotalRow(List<T> totalRow) {
        this.totalRow = totalRow;
    }

    /**
     * 取得总记录数, 默认值为-1.
     */
    public int getTotal() {
        return total;
    }

    /**
     * 设置总记录数.
     */
    public void setTotal(final int total) {
        this.total = total;
        this.totalPages = this.getTotalPages();
    }

    /**
     * 根据pageSize与total计算总页数, 默认值为-1.
     */
    public int getTotalPages() {
        if (total < 0) {
            return -1;
        }
        int pages = total / pageSize;
        return total % pageSize > 0 ? ++pages : pages;
    }

    public void setTotalPages(int totalPages) {
        this.totalPages = totalPages;
    }
}

public class RowBounds {
    public static final int NO_ROW_OFFSET = 0;
    public static final int NO_ROW_LIMIT = 2147483647;
    public static final org.apache.ibatis.session.RowBounds DEFAULT;
    private int offset;
    private int limit;

    public RowBounds() { /* compiled code */ }

    public RowBounds(int offset, int limit) { /* compiled code */ }

    public int getOffset() { /* compiled code */ }

    public int getLimit() { /* compiled code */ }
}

service实现层

@Service
public class ReportServiceImpl implements ReportService {

    private static final Logger log = LoggerFactory.getLogger(ReportServiceImpl.class);

    // 这个是用于自定义报表的字段业务名称map：报表ID-字段业务名称列表（没有实体类，得手工映射）
    // 通俗点就是把英文字段名map到中文名
    private static Map<String, List<ExcelColumn>> GLOBAL_COLUMNS = new HashMap<>();

    @Autowired
    private ReportMapper reportMapper;

    @Override
    public Page<LinkedHashMap<String, Object>> pages(Map<String, Object> params) {
        Query query = new Query(params);
        Page<LinkedHashMap<String, Object>> page = new Page<>(query);
        try {
        	// 报表明细
        	// 考虑到报表功能的通用性，通过反射代理方式来动态调用mapper中的方法：
        	// 根据参数中的reportId获取报表对应的方法名字
            Object reportId = params.get("reportId");
            String methodName = StringUtils.isNull(reportId) ? "now" : reportId.toString();
            Method method = reportMapper.getClass().getDeclaredMethod(methodName, Page.class, Query.class);
            method.invoke(reportMapper, page, query);
            try {
            	// 总计行实现（约定：明细类名加上Total就是总计行的类名）
                Method methodTotal = reportMapper.getClass().getDeclaredMethod(methodName + "Total", Query.class);
                if (methodTotal != null) {
                    Object object = methodTotal.invoke(reportMapper, query);
                    page.setTotalRow((List) object);
                }
            } catch (Exception e) {
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询数据异常{}", e.getMessage());
            throw new RRException("查询失败，请联系网站管理员！");
        }
        return page;
    }

    @Override
    public R export(Map<String, Object> params) {
        try {
            // 工作薄
            int rowNumber = 1; // 行号
            int pageNumber = 1; // 页码
            int pageSize = 2000; // 页大小
            String sheetName = "数据";
            Workbook workbook = new SXSSFWorkbook(20000);
            Sheet sheet = workbook.createSheet(sheetName);
            sheet.createFreezePane(1, 1);
            // 初始化标题行
            log.debug("初始化标题行列名 ...");
            List<ExcelColumn> columns = new ArrayList<>();
            Object reportId = params.get("reportId");
            if (reportId != null && GLOBAL_COLUMNS.containsKey(reportId)) {
                log.debug("存在自定义标题行列名 ...");
                columns = GLOBAL_COLUMNS.get(reportId);
            } else {
                log.debug("不存在自定义标题行列名 ...");
                params.put("pageNumber", 1);
                params.put("pageSize", 1);
                Page<LinkedHashMap<String, Object>> page = this.pages(params);
                // 不存在就取sql中的字段名
                Map<String, Object> title = page.getRows().get(0);
                for (Object key : title.keySet()) {
                    columns.add(new ExcelColumn(key.toString(), key.toString()));
                }
            }
            if (!columns.isEmpty()) {
            	// 自定义方法：初始化表格的标题行，具体代码详见下边
                initExcelTitleRow(workbook, sheet, columns);
                log.debug("columns: " + JSON.toJSONString(columns));
                log.debug("填充数据 ...");
                Page<LinkedHashMap<String, Object>> page;
                // 逐页把明细数据写入到excel中
                do {
                    params.put("pageNumber", pageNumber++);
                    params.put("pageSize", pageSize);
                    page = this.pages(params);
                    for (Map<String, Object> data : page.getRows()) {
                        Row row = sheet.createRow(rowNumber++);
                        for (int i = 0; i < columns.size(); i++) {
                            ExcelColumn column = columns.get(i);
                            Object value = data.get(column.getField());
                            Cell cell = row.createCell(i);
                            // 判断字段类型：如果是数字，就保存成数值，其他的以文本存储
                            if (column.getType() == ExcelColumn.ColumnType.Numeric) {
                                cell.setCellType(CellType.NUMERIC);
                                cell.setCellValue(value == null ? 0 : Double.parseDouble(value.toString()));
                            } else {
                                if (StringUtils.isNotEmpty(column.getReadConverterExp()) && StringUtils.isNotNull(value)) {
                                    value = ExcelUtil.convertByExp(value.toString(), column.getReadConverterExp());
                                }
                                cell.setCellType(CellType.STRING);
                                cell.setCellValue(value == null ? (column.getDefaultValue() == null ? "" : column.getDefaultValue().toString()) : value.toString());
                            }
                        }
                    }
                } while (pageNumber <= page.getTotalPages());
            }
            // 保存文件
            String fileName = UUID.randomUUID().toString() + "_" + sheetName + ".xlsx";
            String filePath = Global.getDownloadPath() + fileName;
            File desc = new File(filePath);
            if (!desc.getParentFile().exists()) {
                desc.getParentFile().mkdirs();
            }
            OutputStream out = null;
            try {
                out = new FileOutputStream(filePath);
                workbook.write(out);
            } catch (IOException e) {
                log.error("导出Excel异常{}", e.getMessage());
                throw new RRException("导出Excel失败，请联系网站管理员！");
            } finally {
                if (workbook != null) {
                    try {
                        workbook.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
                if (out != null) {
                    try {
                        out.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
            }
            return R.ok(fileName);
        } catch (Exception e) {
            return R.error(e.getMessage());
        }
    }

    /**
     * 初始化标题行
     *
     * @param workbook
     * @param sheet
     * @param title
     * @return
     */
    @Deprecated
    private List<String> initExcelTitleRow(Workbook workbook, Sheet sheet, Map<String, Object> title) {
        List<String> columns = new ArrayList<>();
        for (Object key : title.keySet()) {
            columns.add(key.toString());
        }
        Font font = workbook.createFont();
        font.setBold(true); // 粗体显示
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);
        Row row = sheet.createRow(0);
        for (int i = 0; i < columns.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(columns.get(i));
        }
        return columns;
    }


    /**
     * 初始化标题行
     *
     * @param workbook
     * @param sheet
     * @param columns
     * @return
     */
    private void initExcelTitleRow(Workbook workbook, Sheet sheet, List<ExcelColumn> columns) {
        log.debug("初始化标题行 ...");
        // 字体
        Font font = workbook.createFont();
        font.setBold(true); // 粗体显示
        // 样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);
        // 创建标题行
        Row row = sheet.createRow(0);
        for (int i = 0; i < columns.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(columns.get(i).getTitle());
        }
    }

    @PostConstruct
    public void init() {
        System.out.println("初始化 Excel 导出列名 ...");
        // 把自定义的报表添加到GLOBAL_COLUMNS中
        // deliverPaymentTrance方法是获得自定义字段名列表
        GLOBAL_COLUMNS.put("deliverPaymentTrance", deliverPaymentTrance());
        GLOBAL_COLUMNS.put("其他报表类名", tableName());
    }

    /**
     * 获取 Excel 导出列名（****追踪)
     *
     * @return
     */
    private List<ExcelColumn> deliverPaymentTrance() {
    	// ExcelColumn类定义在后边代码中
        List<ExcelColumn> columns = new ArrayList<>();
        columns.add(new ExcelColumn("project_name", "项目名称"));
        columns.add(new ExcelColumn("supplier_name", "供应商名称"));
        columns.add(new ExcelColumn("driver", "承运人"));
        columns.add(new ExcelColumn("deliver_no", "送货单号"));
        columns.add(new ExcelColumn("deliver_date", "送货日期"));
        columns.add(new ExcelColumn("goods_name", "商品名称"));
        columns.add(new ExcelColumn("goods_specs", "规格"));
        columns.add(new ExcelColumn("deliver_amount", "送货款(S)", ExcelColumn.ColumnType.Numeric));
        columns.add(new ExcelColumn("goods_quantity", "验收数量", ExcelColumn.ColumnType.Numeric));
        columns.add(new ExcelColumn("receipt_amount", "应付款(Y)", ExcelColumn.ColumnType.Numeric));
        columns.add(new ExcelColumn("apply_amount", "批准额(P)", ExcelColumn.ColumnType.Numeric));
        columns.add(new ExcelColumn("debt_amount", "应付余额(Y-P)", ExcelColumn.ColumnType.Numeric));
        columns.add(new ExcelColumn("receipt_no", "验收单号"));
        columns.add(new ExcelColumn("receipt_date", "验收日期"));
        columns.add(new ExcelColumn("apply_no", "申请单号"));
        columns.add(new ExcelColumn("apply_date", "申请日期"));
        columns.add(new ExcelColumn("payment_no", "支付单号"));
        columns.add(new ExcelColumn("pay_date", "支付日期"));
        columns.add(new ExcelColumn("is_receipt", "验收状态", DictConstant.RECEIPT_STATUS, ""));
        columns.add(new ExcelColumn("apply_status", "审批状态", DictConstant.APPROVAL_STATUS, ""));
        columns.add(new ExcelColumn("is_pay", "支付状态", DictConstant.PAY_STATUS, ""));
        return columns;
    }

public class ExcelColumn {

    public static final String YYYY_MM = "yyyy-MM";
    public static final String YYYY_MM_DD = "yyyy-MM-dd";
    public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";

    /**
     * 字段名称
     */
    private String field;

    /**
     * 标题
     */
    private String title;

    /**
     * 类型
     */
    private ColumnType type;

    /**
     * 日期格式, 如: yyyy-MM-dd
     */
    private String dateFormat;

    /**
     * 读取内容转表达式
     */
    private String readConverterExp;

    /**
     * 默认值
     */
    private Object defaultValue;

    /**
     * 前缀
     */
    private String prefix;

    /**
     * 后缀
     */
    private String suffix;

    public ExcelColumn() {
    }

    public ExcelColumn(String field, String title) {
        this(field, title, ColumnType.String, null, null, null, null, null);
    }

    public ExcelColumn(String field, String title, ColumnType type) {
        this(field, title, type, null, null, null, null, null);
    }

    public ExcelColumn(String field, String title, String dateFormat) {
        this(field, title, ColumnType.Date, dateFormat, null, null, null, null);
    }

    public ExcelColumn(String field, String title, String readConverterExp, Object defaultValue) {
        this(field, title, ColumnType.String, null, readConverterExp, defaultValue, null, null);
    }

    public ExcelColumn(String field, String title, ColumnType type, String dateFormat, String readConverterExp, Object defaultValue, String prefix, String suffix) {
        this.field = field;
        this.title = title;
        this.type = type;
        this.dateFormat = dateFormat;
        this.readConverterExp = readConverterExp;
        this.defaultValue = defaultValue;
        this.prefix = prefix;
        this.suffix = suffix;
    }

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public ColumnType getType() {
        return type;
    }

    public void setType(ColumnType type) {
        this.type = type;
    }

    public String getDateFormat() {
        return dateFormat;
    }

    public void setDateFormat(String dateFormat) {
        this.dateFormat = dateFormat;
    }

    public String getReadConverterExp() {
        return readConverterExp;
    }

    public void setReadConverterExp(String readConverterExp) {
        this.readConverterExp = readConverterExp;
    }

    public Object getDefaultValue() {
        return defaultValue;
    }

    public void setDefaultValue(Object defaultValue) {
        this.defaultValue = defaultValue;
    }

    public String getPrefix() {
        return prefix;
    }

    public void setPrefix(String prefix) {
        this.prefix = prefix;
    }

    public String getSuffix() {
        return suffix;
    }

    public void setSuffix(String suffix) {
        this.suffix = suffix;
    }

    public enum ColumnType {
        String,
        Numeric,
        Date;
    }

}

Mapper层

@Mapper
public interface ReportMapper extends BaseMapper<LinkedHashMap<String, Object>> {

     /**
     * @param page：分页 query：查询语句
     * @return 
     */
    List<LinkedHashMap<String, Object>> deliverPaymentTrance(Page<LinkedHashMap<String, Object>> page, Query query);

    /**
     * @return 
     */
    List<LinkedHashMap<String, Object>> deliverPaymentTranceTotal(Query query);

mapperXML

<select id="deliverPaymentTrance" resultType="java.util.LinkedHashMap">
        select * from rep_deliver_payment_trance_v
        <where>
            <if test="projectName != null and projectName != ''">and project_name like concat('%', #{projectName},
                '%')
            </if>
            <if test="supplierName != null and supplierName != ''">and supplier_name like concat('%', #{supplierName},
                '%')
            </if>
            <if test="deliverDate != null">
                <![CDATA[and DATE_FORMAT(deliver_date,'%Y-%m-%d') >= DATE_FORMAT(#{deliverDate, jdbcType=DATE},'%Y-%m-%d')]]></if>
            <if test="deliverDateEnd != null">
                <![CDATA[and DATE_FORMAT(deliver_date,'%Y-%m-%d') <= DATE_FORMAT(#{deliverDateEnd, jdbcType=DATE},'%Y-%m-%d')]]></if>
            <if test="driver != null and driver != ''">and driver = #{driver}</if>
            <if test="deliverNo != null and deliverNo != ''">and deliver_no = #{deliverNo}</if>
            <if test="goodsName != null and goodsName != ''">and goods_name like concat('%', #{goodsName},
                '%')
            </if>
            <if test="receiptNo != null and receiptNo != ''">and receipt_no = #{receiptNo}</if>
            <if test="applyNo != null and applyNo != ''">and apply_no = #{applyNo}</if>
            <if test="isReceipt != null and isReceipt != ''">and is_receipt = #{isReceipt}</if>
            <if test="isApply != null and isApply != ''">and is_apply = #{isApply}</if>
            <if test="isPay != null and isPay != ''">and is_pay = #{isPay}</if>
        </where>
    </select>

<select id="deliverPaymentTranceTotal" resultType="java.util.LinkedHashMap">
        select round(sum(deliver_amount),2) as deliver_amount,round(sum(receipt_amount),2) as receipt_amount,
        round(sum(apply_amount),2) as apply_amount,round(sum(debt_amount),2) as debt_amount,
        round(sum(goods_quantity),2) as goods_quantity
        from rep_deliver_payment_trance_v
        <where>
            <if test="projectName != null and projectName != ''">and project_name like concat('%', #{projectName},
                '%')
            </if>
            <if test="supplierName != null and supplierName != ''">and supplier_name like concat('%', #{supplierName},
                '%')
            </if>
            <if test="deliverDate != null">
                <![CDATA[and DATE_FORMAT(deliver_date,'%Y-%m-%d') >= DATE_FORMAT(#{deliverDate, jdbcType=DATE},'%Y-%m-%d')]]></if>
            <if test="deliverDateEnd != null">
                <![CDATA[and DATE_FORMAT(deliver_date,'%Y-%m-%d') <= DATE_FORMAT(#{deliverDateEnd, jdbcType=DATE},'%Y-%m-%d')]]></if>
            <if test="driver != null and driver != ''">and driver = #{driver}</if>
            <if test="deliverNo != null and deliverNo != ''">and deliver_no = #{deliverNo}</if>
            <if test="receiptNo != null and receiptNo != ''">and receipt_no = #{receiptNo}</if>
            <if test="goodsName != null and goodsName != ''">and goods_name like concat('%', #{goodsName},
                '%')
            </if>
            <if test="applyNo != null and applyNo != ''">and apply_no = #{applyNo}</if>
            <if test="isReceipt != null and isReceipt != ''">and is_receipt = #{isReceipt}</if>
            <if test="isApply != null and isApply != ''">and is_apply = #{isApply}</if>
            <if test="isPay != null and isPay != ''">and is_pay = #{isPay}</if>
        </where>
    </select>

HTML层

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head th:include="include/header_css::header('***追溯')">
    <link rel="stylesheet" href="/plugins/layui-v2.5.6/css/layui.css" media="all">
</head>
<body>

<div id="dpLTE" class="container-fluid">
    <div class="row">
        <div class="col-md-12  search-collapse" id="search">
            <form id="ff">
                <table class="input-group form-input">
                    <tr>
                        <td class="formTitle">项目名称：</td>
                        <td class="formValue">
                            <input class="search-input form-control" type="text" name="projectName"
                                   autocomplete="off"/>
                        </td>
                        <td class="formTitle">供应商名称：</td>
                        <td class="formValue">
                            <input class="search-input form-control" type="text" name="supplierName"
                                   autocomplete="off"/>
                        </td>
                        <td class="formTitle">送货日期：</td>
                        <td class="formValue">
                            <input id="deliverDate" class="form-control layui-input" type="text" autocomplete="off"
                                   placeholder="yyyy-MM-dd - yyyy-MM-dd"/>
                        </td>
                        <td class="formTitle">承运人：</td>
                        <td class="formValue">
                            <input class="search-input form-control" type="text" name="driver" autocomplete="off"/>
                        </td>
                    </tr>
                    <tr>
                        <td class="formTitle">送货单号：</td>
                        <td class="formValue">
                            <input class="search-input form-control" type="text" name="deliverNo"
                                   autocomplete="off"/>
                        </td>
                        <td class="formTitle">验收单号：</td>
                        <td class="formValue">
                            <input class="search-input form-control" type="text" name="receiptNo"
                                   autocomplete="off"/>
                        </td>
                        <td class="formTitle">费用申请号：</td>
                        <td class="formValue">
                            <input class="search-input form-control" type="text" name="applyNo"
                                   autocomplete="off"/>
                        </td>
                        <td class="formTitle">商品名称：</td>
                        <td class="formValue">
                            <input class="search-input form-control" type="text" name="goodsName"
                                   autocomplete="off"/>
                        </td>
                    </tr>
                    <tr>
                        <td class="formTitle">验收状态：</td>
                        <td class="formValue">
                            <select class="form-control" v-model="isReceipt">
                                <option value="">-- 所有 --</option>
                                <option value="0">未验收</option>
                                <option value="1">已验收</option>
                            </select>
                        </td>
                        <td class="formTitle">审批状态：</td>
                        <td class="formValue">
                            <select class="form-control" v-model="isApply">
                                <option value="">-- 所有 --</option>
                                <option value="0">未审批</option>
                                <option value="1">已审批</option>
                            </select>
                        </td>
                        <td class="formTitle">支付状态：</td>
                        <td class="formValue">
                            <select class="form-control" v-model="isPay">
                                <option value="">-- 所有 --</option>
                                <option value="0">未支付</option>
                                <option value="1">已支付</option>
                            </select>
                        </td>
                        <td class="formTitle"></td>
                        <td class="formValue">
                            <a class="btn btn-primary" onclick="load()"><i class="fa fa-search"></i>&nbsp;查询</a>
                        </td>
                    </tr>
                </table>
            </form>
        </div>
        <div class="btn-toolbar" id="toolbar">
            <div class="btn-group">
                <a class="btn btn-success" onclick="excelExport()"><i
                        class="fa fa-download"></i>&nbsp;导出</a>
                <a class="btn btn-success" onclick="$('#search').toggle();"><i
                        class="fa fa-arrows-alt"></i>&nbsp;收缩条件框</a>
            </div>
        </div>
    </div>
    <div class="row table-responsive">
        <table id="dataGrid" class="table text-nowrap"></table>
    </div>
</div>
<div th:include="include/footer_js::footer"></div>

<script src="/plugins/layui-v2.5.6/layui.js"></script>
<script src="/js/common-layui.js"></script>

<script type="text/javascript">

    function load() {
        var paras = {};
        paras = getParams();
        var table, tableIns;
        layui.use('table', function () {
            table = layui.table;
            tableIns = table.render({
                elem: '#dataGrid',
                url: '/report/reportData?_' + $.now(),
                method: 'post',
                cellMinWidth: 80,
                contentType: 'application/json',
                totalRow: true,
                page: true,
                request: {
                    pageName: 'pageNumber', //页码的参数名称，默认：page
                    limitName: 'pageSize' //每页数据量的参数名，默认：limit
                },
                parseData: function (res) { //res 即为原始返回的数据
                    return {
                        "code": res.code, //解析接口状态
                        "msg": res.msg, //解析提示文本
                        "count": res.total, //解析数据长度
                        "data": res.rows, //解析数据列表
                        "totalRow": (res.totalRow && res.totalRow.length) ? res.totalRow[0] : {} // 总计行数据
                    };
                },
                where: paras,
                cols: [[
                    // {type: 'checkbox', fixed: 'left'},
                    {
                        field: "project_name",
                        title: "项目名称",
                        align: 'left',
                        width: 160,
                        totalRowText: '合计'
                    }, {
                        field: "supplier_name",
                        title: "供应商名称",
                        align: 'left',
                        width: 140
                    }, {
                        field: "driver",
                        title: "承运人",
                        align: 'left'
                    }, {
                        field: "deliver_no",
                        title: "送货单号",
                        align: 'left',
                        width: 170
                    }, {
                        field: "deliver_date",
                        title: "送货日期",
                        align: 'left',
                        width: 100
                    }, {
                        field: "goods_name",
                        title: "商品名称",
                        align: 'left',
                        width: 120
                    }, {
                        field: "goods_specs",
                        title: "规格",
                        align: 'left'
                    }, {
                        field: "deliver_amount",
                        title: "送货款(S)",
                        align: 'right',
                        width: 110,
                        templet: function (d) {
                            var val = formatCurrency(d.deliver_amount);
                            return '<span>' + val + '</span>'
                        },
                        totalRow: true
                    }, {
                        field: "goods_quantity",
                        title: "验收数量",
                        align: 'right',
                        width: 90,
                        templet: function (d) {
                            var val = formatCurrency(d.goods_quantity);
                            return '<span>' + val + '</span>'
                        },
                        totalRow: true
                    }, {
                        field: "receipt_amount",
                        title: "应付款(Y)",
                        align: 'right',
                        width: 110,
                        templet: function (d) {
                            var val = formatCurrency(d.receipt_amount);
                            return '<span>' + val + '</span>'
                        },
                        totalRow: true
                    }, {
                        field: "apply_amount",
                        title: "批准额(P)",
                        align: 'right',
                        width: 110,
                        templet: function (d) {
                            var val = formatCurrency(d.apply_amount);
                            return '<span>' + val + '</span>'
                        },
                        totalRow: true
                    }, {
                        field: "debt_amount",
                        title: "应付余额(Y-P)",
                        align: 'right',
                        width: 120,
                        templet: function (d) {
                            var val = formatCurrency(d.debt_amount);
                            return '<span>' + val + '</span>'
                        },
                        totalRow: true
                    }, {
                        field: "receipt_no",
                        title: "验收单号",
                        align: 'left',
                        width: 190
                    }, {
                        field: "receipt_date",
                        title: "验收日期",
                        align: 'left',
                        width: 100
                    }, {
                        field: "apply_no",
                        title: "申请单号",
                        align: 'left',
                        width: 190
                    }, {
                        field: "apply_date",
                        title: "申请日期",
                        align: 'left',
                        width: 100
                    }, {
                        field: "payment_no",
                        title: "支付单号",
                        align: 'left',
                        width: 190
                    }, {
                        field: "pay_date",
                        title: "支付日期",
                        align: 'left',
                        width: 100
                    }, {
                        field: "is_receipt",
                        title: "验收状态",
                        width: 100,
                        align: 'center',
                        templet: function (d) {
                            return reportReceiptFlag(d.is_receipt);
                        }
                    }, {
                        field: "apply_status",
                        title: "申请状态",
                        width: 100,
                        align: 'center',
                        templet: function (d) {
                            return reportApproveFlag(d.apply_status);
                        }
                    }, {
                        field: "is_pay",
                        title: "支付状态",
                        width: 100,
                        align: 'center',
                        templet: function (d) {
                            return reportPayFlag(d.is_pay);
                        }
                    }
                    // {
                    //     title: "操作", fixed: 'right', width: 78, align: 'center', toolbar: '#detail'
                    // }
                ]]
            });
            //监听工具条
            table.on('tool(deliver)', function (obj) {
                var data = obj.data;
                if (obj.event === 'detail') {
                    vm.detailed(data.deliver_id)
                }
            });
        });
    }

    function getParams() {
        var params = {reportId: 'deliverPaymentTrance'};
        var array = $('#ff').serializeArray();
        $.each(array, function () {
            if (params[this.name] !== undefined) {
                if (!params[this.name].push) {
                    params[this.name] = [params[this.name]];
                }
                params[this.name].push(this.value || '');
            } else {
                params[this.name] = this.value || '';
            }
        });
        if ((vm.deliverDate != null) && (vm.deliverDate != "undefined-undefined-undefined")) {
            params['deliverDate'] = vm.deliverDate;
            params['deliverDateEnd'] = vm.deliverDateEnd;
        } else {
            params['deliverDate'] = null;
            params['deliverDateEnd'] = null;
        }
        if (vm.isReceipt >= 0) {
            params['isReceipt'] = vm.isReceipt;
        } else {
            params['isReceipt'] = '';
        }
        if (vm.isApply >= 0) {
            params['isApply'] = vm.isApply;
        } else {
            params['isPay'] = '';
        }
        if (vm.isPay >= 0) {
            params['isPay'] = vm.isPay;
        } else {
            params['isPay'] = '';
        }
        return params;
    }

    function excelExport() {
        $.exportExcel({
            url: '/report/export?_' + $.now(),
            param: getParams(),
            success: function (data) {
            }
        });
    }

    var vm = new Vue({
        el: '#dpLTE',
        data: {
            deliverDate: null,
            deliverDateEnd: null,
            isReceipt: null,
            isApply: null,
            isPay: null,
        },
        methods: {
            detailed: function (id) {
                if (id) {
                    dialogOpenDetailed({
                        title: '详情',
                        url: '/base/deliver/detailed.html?_' + $.now(),
                        width: '900px',
                        height: '650px',
                        success: function (iframeId) {
                            top.frames[iframeId].vm.deliverId = id;
                            top.frames[iframeId].vm.setForm();
                        }
                    });
                }
            },
        }
    });

    layui.use('laydate', function () {
        var laydate = layui.laydate;
        laydate.render({
            elem: '#deliverDate',
            range: true,
            done: function (value, date, endDate) {
                vm.deliverDate = date.year + '-' + date.month + '-' + date.date;
                vm.deliverDateEnd = endDate.year + '-' + endDate.month + '-' + endDate.date;
            }
        });
    });
</script>
</body>
</html>

总结

关键是动态反射把字段映射起来。
即使没有本文html部分，大家根据前边的语句块，也能得到自己系统的异步数据了。
最后，不要留言索要源码，公司摘抄出来，主要是与大家交流实现思路。

hawk_2016

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
springboot自定义sql的多表查询报表（非实体类）

springboot自定义sql多表报表（非实体类））概述最后的查询效果如下：controller层service层接口层实现层Mapper层mapperXMLHTML层概述在日常开发中，业务部门总有各种报表需求，而系统一开始设计不可能考虑到所有需求。所以，后期得通过拼接SQL语句来拼凑报表。而这些报表是不能用先前的实体类来实现。我整体用到的有：springboot、Mybatis、layUI、vue说明:在sevice中，汇总行的类名是在明细数据类名后加上Total(类似spring中的约定思想
复制链接

扫一扫

专栏目录