效果展示
定义一个类接收我们报表中需要的数据,这里使用的是easyui中的插件
<script type="text/javascript" src="/easyui/plugins/datagrid-groupview.js"></script>
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 = ""; //分组字段
public PurchaseBillItemVo(){}
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.buyer.username".equals(groupBy)){
groupField = this.buyer;
}else if("MONTH(o.bill.vdate)".equals(groupBy)){
groupField = (DateUtils.toCalendar(vdate).get(Calendar.MONTH)+1) + "月份";
}else {
groupField = this.supplier;
}
}
…
3.1.2.PurchaseBillItemServiceImpl
//根据传递的条件拿到所有的明细数据(并且把它们变成咱们的vo对象)
@Override
public List<PurchaseBillItemVo> findItems(PurchaseBillItemQuery itemQuery){
List<PurchaseBillItem> items = purchaseBillItemRepository.findByQuery(itemQuery);
List<PurchaseBillItemVo> vos = new ArrayList<>();
for (PurchaseBillItem item : items) {
PurchaseBillItemVo vo = new PurchaseBillItemVo(item,null);
vos.add(vo);
}
return vos;
}
注意query中的
Specification<PurchaseBillItem> spec = Specifications.<PurchaseBillItem>and()
.eq(status!=null,"bill.status",status )//等于
.ge(beginDate!=null, "bill.vdate",beginDate) //大于等于
.lt(endDate!=null, "bill.vdate",tempDate) //小于等于
.build();
使用HighChart制作图形报表
插件的导入
<script src="/js/jquery/plugin/highcharts/code/highcharts.js"></script>
<script src="/js/jquery/plugin/highcharts/code/highcharts-3d.js"></script>
<script src="/js/jquery/plugin/highcharts/code/modules/exporting.js"></script>
需要的格式是
[{“name”:“东莞供应商”,“y”:4.00},{“name”:“成都供应商”,“y”:6955.00}]
//装数据的容器
private List params = new ArrayList<>();
//拿到查询的条件
public String getWhereSql(){
StringBuilder whereSql = new StringBuilder("");
if(status!=null){
whereSql.append(" and ").append("bill.status = ?");
params.add(status);
}
if(beginDate!=null){
whereSql.append(" and ").append("bill.vdate >= ?");
params.add(beginDate);
}
if(endDate!=null){
Date date = DateUtils.addDays(endDate,1);
whereSql.append(" and ").append("bill.vdate < ?");
params.add(endDate);
}
return whereSql.toString().replaceFirst("and","where");
}
public List<Map> findCharts(PurchaseBillItemQuery itemQuery){
List<Map> chartList = new ArrayList<>();
//完成查询的JPQL拼接
String jpql = "select "+itemQuery.getGroupBy()+",sum(o.amount) from PurchaseBillItem o "+itemQuery.getWhereSql()+" group by "+itemQuery.getGroupBy();
//System.out.println(jpql);
List<Object[]> list = findByJpql(jpql, itemQuery.getParams().toArray());
for (Object[] objects : list) {
Map map = new HashMap();
map.put("name",objects[0]);
map.put("y",objects[1]);
chartList.add(map);
}
return chartList;
}