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> 查询</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> 导出</a>
<a class="btn btn-success" onclick="$('#search').toggle();"><i
class="fa fa-arrows-alt"></i> 收缩条件框</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部分,大家根据前边的语句块,也能得到自己系统的异步数据了。
最后,不要留言索要源码,公司摘抄出来,主要是与大家交流实现思路。