文章目录
1 报表的使用及理解
1.1 什么是报表
[forms for reporting to the higher organizations] 向上级报告情况的表格。简单的说:报表就是用表格、图表等格式来动态显示数据,可以用公式表示为:“报表 = 多样的格式 + 动态的数据”。
1.2 报表的作用
直观 好看 体验性好
统计数据,供用户分析,作出决策
1.3 使用报表
1.3.1 引入js文件
datagrid-groupview.js
1.3.2 定义一个普通表格
<table id="purchaseBillItemGrid"></table>
1.3.3 创建表格
$(function(){
$('#purchaseBillItemGrid').datagrid({
nowrap:false,
fitColumns:true,
fit:true,
fixed:true,
fitColumns:true,
toolbar:'#tb',
url:'/purchasebillitem/findPurchaseBillItemVo',
columns:[[
{field:'id',title:'编号',width:50},
{field:'supplier',title:'供应商名称',width:100,sortable:true},
{field:'buyer',title:'采购员名称',width:80,sortable:true},
{field:'product',title:'产品名称',width:80,sortable:true},
{field:'productType',title:'产品分类',width:80,sortable:true},
{field:'vdate',title:'交易时间',width:150,align:'center',sortable:true},
{field:'num',title:'采购数量',width:60,align:'center'},
{field:'price',title:'价格',width:60,align:'center'},
{field:'amount',title:'小计',width:60,align:'center'},
{field:'status',title:'状态',width:60,align:'center',formatter:statusFormatter}
]],
groupField:'groupField',
view: groupview,
groupFormatter:function(value, rows){
var totalNum = 0;
//总金额
var totalAmount = 0;
//rows表示当前分组下面的所有行
for (var i = 0; i < rows.length; i++) {
var row = rows[i];//循环每一行
totalNum += row.num;
totalAmount += row.amount;
}
return value + ' - ' + rows.length + ' 条数据' +" <span style='color:green;'>共"+totalNum+"件商品</span>" +"<span style='color:#5d2f80;'>总金额:"+totalAmount+"</span>";
}
});
});
1.3.4 后台查询相应的数据
构造对象 PurchasebillitemVo这个对象 — 封装数据
public class PurchaseBillItemVo {
private Long id; //编号
private String supplier; //供应商名称
private String buyer; //采购员名称
private String product; //产品名称
private String productType; //产品分类
private Date vdate; //交易时间
private BigDecimal num; //采购数量
private BigDecimal price; //价格
private BigDecimal amount; //小计 = 价格*数量
private Integer status;
private String groupField = ""; //分组字段
private String groupBy;
//构造方法 创建对象
public PurchaseBillItemVo(Purchasebillitem item,String groupBy){
this.id = item.getId();
this.supplier = item.getBill().getSupplier().getName();
this.buyer = item.getBill().getBuyer().getUsername();
this.product = item.getProduct().getName();
this.productType = item.getProduct().getTypes().getName();
this.vdate = item.getBill().getVdate();
this.num = item.getNum();
this.price = item.getPrice();
this.amount = item.getAmount();
this.status = item.getBill().getStatus();
if ("o.bill.supplier.name".equals(groupBy)){
this.groupField = this.supplier;
}
else if ("o.bill.buyer.username".equals(groupBy)){
this.groupField = this.buyer;
}
else if("MONTH(o.bill.vdate)".equals(groupBy)){
this.groupField = (DateUtils.toCalendar(vdate).get(Calendar.MONTH)+1) + "月";
}else {
this.groupField = this.supplier;
}
}
public PurchaseBillItemVo() {
}
get()/set()方法
核心构造方法:封装前台需要的数据
if ("o.bill.supplier.name".equals(groupBy)){
this.groupField = this.supplier;
}
else if ("o.bill.buyer.username".equals(groupBy)){
this.groupField = this.buyer;
}
else if("MONTH(o.bill.vdate)".equals(groupBy)){
this.groupField = (DateUtils.toCalendar(vdate).get(Calendar.MONTH)+1) + "月";
}else {
this.groupField = this.supplier;
}
1.3.5 后台service层
IPurchasebillitemService层
//数据表格报表
List<PurchaseBillItemVo> findItems(PurchasebillitemQuery itemQuery);
PurchasebillitemServiceImpl层
@Override
public List<PurchaseBillItemVo> findItems(PurchasebillitemQuery itemQuery) {
System.out.println(itemQuery);
//查询所有采购明细
List<Purchasebillitem> query = purchasebillitemRepository.findByQuery(itemQuery);
List<PurchaseBillItemVo> itemVoList = new ArrayList<>();
for (Purchasebillitem purchasebillitem : query) {
PurchaseBillItemVo vo = new PurchaseBillItemVo(purchasebillitem,itemQuery.getGroupBy());
itemVoList.add(vo);
}
return itemVoList;
}
1.3.6 高级查询PurchasebillitemQuery层
//接受时间和状态
private Date beginDate;
private Date endDate;
private Integer status;
private String groupBy = "o.bill.supplier.name";
@Override
public Specification createSpecification() {
//如果日期不为空 将日期时间加一天
Date tempDate = null;
if(endDate != null){
tempDate = DateUtils.addDays(endDate, 1);
}
//根据条件把数据返回即可
return Specifications.<Purchasebillitem>and()
.ge(this.beginDate !=null,"bill.vdate",this.beginDate )
.lt(this.endDate != null,"bill.vdate",tempDate)
.eq(this.status !=null ,"bill.status",this.status)
.build();
}
1.3.7 最终效果图
2 图形报表
2.1 效果图
2.2 怎么完成图形报表
已经有很多现成的框架 可以去完成图表
flash(Flex actionScript) —界面很好看 容易崩溃
HTML5 (Canvas画布) – 手动画图 很麻烦
现在已经有很多图形的报表的框架
echarts — https://echarts.baidu.com/ 百度开发–国内公司在使用
hightcharts – 全球在使用
2.3 使用报表
2.3.1 项目中引入HighChart
2.4 制作图形报表
2.4.1 引入js文件
<!-- 引入highchart的支持 -->
<script src="/easyui/plugin/highcharts/code/highcharts.js"></script>
<script src="/easyui/plugin/highcharts/code/highcharts-3d.js"></script>
<script src="/easyui/plugin/highcharts/code/modules/exporting.js"></script>
2.4.2 添加前台界面按钮&&添加一个弹出框
<a href="#" data-method="charts2D" class="easyui-linkbutton" iconCls="icon-search">2D图</a>
<a href="#" data-method="charts3D" class="easyui-linkbutton" iconCls="icon-search">3D图</a>
<div id="purchaseBillItemDialog" class="easyui-dialog" data-options="closed:true,modal:true" title="功能操作" style="width:600px;height:300px; ">
2.4.3 js代码
charts3D:function(){
var param = searchForm.serializeObject();
$.post("/purchasebillitem/findAllGraphic",param,function (result) {
//发送ajax请求 到后台请求数据
Highcharts.chart('purchaseBillItemDialog', {
chart: {
type: 'pie',
options3d: {
enabled: true,
alpha: 45,
beta: 0
}
},
title: {
text: '消费图'
},
tooltip: {
pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
},
plotOptions: {
pie: {
allowPointSelect: true,
cursor: 'pointer',
depth: 35,
dataLabels: {
enabled: true,
format: '{point.name}'
}
}
},
series: [{
type: 'pie',
name: "消费比例",
data: result
}]
});
})
//3D图表 --打开对话框
purchaseBillItemDialog.dialog('center').dialog('open');
}
2.4.4 后台PurchasebillitemQuery层拼接sql语句
public String getWhereSql(){
String sql = "";
if (beginDate != null && !"".equals(beginDate)){
sql += " and b.vdate >= ?";
params.add(beginDate);
}
if (endDate != null && !"".equals(endDate)){
sql += " and b.vdate <= ?";
params.add(endDate);
}
if (status != null && !"".equals(status)){
sql += " and b.status = ?";
params.add(status);
}
return sql.replace("and", "where");
}
2.4.5 PurchasebillitemServiceImpl层判断
//根据jpql语句 查询数据
@Override
public List findItemsBuJpql(PurchasebillitemQuery itemQuery) {
List<Map> mapList = new ArrayList<>();
String groupBy = itemQuery.getGroupBy();
//根据jpql语句查询
String jpql="select "+groupBy+",sum(b.totalAmount) from Purchasebillitem o join o.bill b "+itemQuery.getWhereSql()+" group by " + groupBy;
List<Object[]> list = purchasebillitemRepository.findByJpql(jpql, itemQuery.getParams().toArray());
for (Object[] objects : list) {
Object name = objects[0];
BigDecimal y = (BigDecimal) objects[1];
Map map = new HashMap();
map.put("name", name);
map.put("y", y);
mapList.add(map);
}
return mapList;
}
2.4.6 PurchasebillitemController层查询数据返回json数据到前台
//查询图形报表
@RequestMapping("/findAllGraphic")
@ResponseBody
public List findAllGraphic(PurchasebillitemQuery itemQuery){
Map map = new HashMap();
List list = purchasebillitemService.findItemsBuJpql(itemQuery);
return list;
}