报表：连接多张数据表，通过一系列的运算的运算得到的结果
VO：普通对象
PO：持久化的对象
目录
1.EasuUI----->datagrid-groupview
1.组装domian--->PurchaseBillItemVo
1.EasuUI----->datagrid-groupview
1.后台返回数据
1.组装domian--->PurchaseBillItemVo
public class PurchasebillitemVO {
//编号
private Long id;
//供应商名称
private String supplierName;
//采购员名称
private String buyerName;
//产品名称
private String prdouctName;
//产品类型名称
private String productTypeName;
//交易时间
private Date vdate;
//价格
private BigDecimal price;
//数量
private BigDecimal num;
//小计(计算:价格*数量)
private BigDecimal amount;
/**
* 单据状态
* 0待审,1已审，-1作废
*/
private Integer status = 0;
//分组字段
private String groupField;
public PurchasebillitemVO() {}
//使用这个构造器，直接把item变成vo(只要报表中需要的数据)
public PurchasebillitemVO(Purchasebillitem item, PurchasebillitemQuery query) {
this.id = item.getId();
this.supplierName = item.getBill().getSupplier().getName();
this.buyerName = item.getBill().getBuyer().getUsername();
this.prdouctName = item.getProduct().getName();
this.productTypeName = 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();
//如果是供应商分组
//this.groupField = this.supplierName;
//如果是采购员分组
//this.groupField = this.buyerName;
//如果是月份
// Calendar cal = Calendar.getInstance();
// cal.setTime(vdate);
// this.groupField = (cal.get(Calendar.MONTH)+1) + "月";
switch (query.getGroupType()){
case 1:
this.groupField = this.supplierName;
break;
case 2:
this.groupField = this.buyerName;
break;
case 3:
Calendar cal = Calendar.getInstance();
cal.setTime(vdate);
this.groupField = (cal.get(Calendar.MONTH)+1) + "月";
break;
default:
this.groupField = this.supplierName;
}
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getSupplierName() {
return supplierName;
}
public void setSupplierName(String supplierName) {
this.supplierName = supplierName;
}
public String getBuyerName() {
return buyerName;
}
public void setBuyerName(String buyerName) {
this.buyerName = buyerName;
}
public String getPrdouctName() {
return prdouctName;
}
public void setPrdouctName(String prdouctName) {
this.prdouctName = prdouctName;
}
public String getProductTypeName() {
return productTypeName;
}
public void setProductTypeName(String productTypeName) {
this.productTypeName = productTypeName;
}
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
public Date getVdate() {
return vdate;
}
public void setVdate(Date vdate) {
this.vdate = vdate;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public BigDecimal getNum() {
return num;
}
public void setNum(BigDecimal num) {
this.num = num;
}
public BigDecimal getAmount() {
return amount;
}
public void setAmount(BigDecimal amount) {
this.amount = amount;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public String getGroupField() {
return groupField;
}
public void setGroupField(String groupField) {
this.groupField = groupField;
}
}
2.查询数据-->业务层
1.接口
List<PurchasebillitemVO> findItems(PurchasebillitemQuery query);
2.实现
@Override
public List<PurchasebillitemVO> findItems(PurchasebillitemQuery query) {
List<PurchasebillitemVO> vos = new ArrayList<>();
//1.根据条件获取所有的采购明细
List<Purchasebillitem> items = purchasebillitemRepository.findByQuery(query);
//2.遍历采购明细(每一个Purchasebillitem变成PurchasebillitemVO)
for (Purchasebillitem item : items) {
PurchasebillitemVO vo = new PurchasebillitemVO(item,query);
vos.add(vo);
}
return vos;
}
3.控制层跳转
@RequestMapping("/index")
public String index() {
//根据配置，这里会跳到/WEB-INF/views/purchasebillitem/purchasebillitem.jsp页面
return "purchasebillitem/purchasebillitem";
}
@RequestMapping("/findItems")
@ResponseBody
public List<PurchasebillitemVO> findItems(PurchasebillitemQuery query){
return purchasebillitemService.findItems(query);
}
2.前台展示数据
1.JSP
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>purchasebillitem管理</title>
<%@include file="/WEB-INF/views/head.jsp" %>
<script type="text/javascript" src="/js/model/purchasebillitem.js"></script>
<script type="text/javascript"
src="/easyui/plugin/jquery-easyui-datagridview/datagrid-groupview.js"></script>
</head>
<body>
<table id="groupItem"></table>
</body>
2.JS
function formatStatus(value,row,index){
if(value==0){
return "<span style='color:red'>待审</span>";
}else if(value ==1){
return "<span style='color:green'>已审</span>";
}else{
return "<s style='color: rgba(0,0,0,0.33);'>作废</s>";
}
}
$(function () {
//常用的控件先获取
var groupItem =$('#groupItem');
var searchForm =$('#searchForm')
//注册方法
$("*[data-method]").on("click",function(){
var methodName = $(this).data("method");
//调用方法
itsource[methodName]();
})
itsource = {
search(){
//拿到表所有参数(变成json)
var params = searchForm.serializeObject();
groupItem.datagrid('load',params);
}
}
groupItem.datagrid({
fit:true,
rownumbers:true,
remoteSort:false, //是否支持远程排序
nowrap:false,
fitColumns:true,
singleSelect:true,
url:'/purchasebillitem/findItems', //访问数据的路径
columns:[[
{field:'id',title:'编号',width:20,sortable:true},
{field:'supplierName',title:'供应商',width:80,align:'right',sortable:true},
{field:'buyerName',title:'采购员',width:80,align:'right',sortable:true},
{field:'prdouctName',title:'产品',width:40,sortable:true},
{field:'productTypeName',title:'产品类型',width:60,align:'center'},
{field:'vdate',title:'交易时间',width:60,align:'center'},
{field:'price',title:'价格',width:60,align:'center'},
{field:'num',title:'数量',width:60,align:'center'},
{field:'amount',title:'小计',width:60,align:'center'},
{field:'status',title:'状态',width:60,align:'center',formatter:formatStatus}
]],
groupField:'groupField', //根据哪一个字段分组
view: groupview, //分组视图必需要加这个
//value:分组的值 rows:这一组的所有值
groupFormatter:function(value, rows){ //分组位置的显示
var totalNum = 0; //总数量
var totalAmount = 0;//总金额
for(let r of rows){
totalNum += r.num;
totalAmount += r.amount;
}
return value + ' - ' + `${rows.length}条数据
<span style="color: #00ee00">共${totalNum}个商品</span>
<span style="color: #aa2aee">总金额:${totalAmount}</span>
`;
}
});
})
3.带过滤条件的查询
1.高级查询
1.按钮
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>purchasebillitem管理</title>
<%@include file="/WEB-INF/views/head.jsp" %>
<script type="text/javascript" src="/js/model/purchasebillitem.js"></script>
<script type="text/javascript"
src="/easyui/plugin/jquery-easyui-datagridview/datagrid-groupview.js"></script>
</head>
<body>
<div id="gridToolBar" style="height:auto">
<form id="searchForm">
交易时间:
<input name="beginDate" class="easyui-datebox" style="width:120px"> -
<input name="endDate" class="easyui-datebox" style="width:120px">
状态:
<select class="easyui-combobox" panelHeight="auto" name="status" style="width:120px;">
<option value="">--请选择--</option>
<option value="0">待审</option>
<option value="1">已审</option>
<option value="-1">作废</option>
</select>
</div>
<table id="groupItem"></table>
</body>
</html>
2.Query
public class PurchasebillitemQuery extends BaseQuery {
private Date beginDate; //开始时间
private Date endDate; //结束时间
private Integer status; //状态
@Override
public Specification createSpecification() {
//结束时间加一天
Date tempDate = null;
if(endDate!=null){
tempDate= DateUtils.addDays(endDate,1);
}
Specification<Purchasebillitem> spec = Specifications.<Purchasebillitem>and()
.ge(beginDate!=null,"bill.vdate",beginDate)
.lt(endDate!=null,"bill.vdate",tempDate)
.eq(status!=null,"bill.status",status)
.build();
return spec;
}
//省略get/set/时间的处理
3.toolbar:"#gridToolBar"
2.分组下拉框
1.下拉框
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>purchasebillitem管理</title>
<%@include file="/WEB-INF/views/head.jsp" %>
<script type="text/javascript" src="/js/model/purchasebillitem.js"></script>
<script type="text/javascript"
src="/easyui/plugin/jquery-easyui-datagridview/datagrid-groupview.js"></script>
</head>
<body>
<div id="gridToolBar" style="height:auto">
<form id="searchForm">
交易时间:
<input name="beginDate" class="easyui-datebox" style="width:120px"> -
<input name="endDate" class="easyui-datebox" style="width:120px">
状态:
<select class="easyui-combobox" panelHeight="auto" name="status" style="width:120px;">
<option value="">--请选择--</option>
<option value="0">待审</option>
<option value="1">已审</option>
<option value="-1">作废</option>
</select>
<%--###############################################################################################--%>
<%--根据什么字段分组--%>
<select class="easyui-combobox" panelHeight="auto" name="groupType" style="width:120px;">
<option value="1">供应商</option>
<option value="2">采购员</option>
<option value="3">月份</option>
</select>
<a href="javascript:;" data-method="search" class="easyui-linkbutton" iconCls="icon-search">查询</a>
</form>
<%--###############################################################################################--%>
</div>
<table id="groupItem"></table>
</body>
</html>
2.分页收受参数
//1.供应商 2.采购员 3.月份
private Integer groupType = 1; //分组的字段
3.Js
function formatStatus(value,row,index){
if(value==0){
return "<span style='color:red'>待审</span>";
}else if(value ==1){
return "<span style='color:green'>已审</span>";
}else{
return "<s style='color: rgba(0,0,0,0.33);'>作废</s>";
}
}
$(function () {
//常用的控件先获取
var groupItem =$('#groupItem');
var searchForm =$('#searchForm')
//注册方法
$("*[data-method]").on("click",function(){
var methodName = $(this).data("method");
//调用方法
itsource[methodName]();
})
itsource = {
search(){
//拿到表所有参数(变成json)
var params = searchForm.serializeObject();
groupItem.datagrid('load',params);
}
}
groupItem.datagrid({
fit:true,
rownumbers:true,
remoteSort:false, //是否支持远程排序
nowrap:false,
fitColumns:true,
singleSelect:true,
toolbar:"#gridToolBar",
url:'/purchasebillitem/findItems', //访问数据的路径
columns:[[
{field:'id',title:'编号',width:20,sortable:true},
{field:'supplierName',title:'供应商',width:80,align:'right',sortable:true},
{field:'buyerName',title:'采购员',width:80,align:'right',sortable:true},
{field:'prdouctName',title:'产品',width:40,sortable:true},
{field:'productTypeName',title:'产品类型',width:60,align:'center'},
{field:'vdate',title:'交易时间',width:60,align:'center'},
{field:'price',title:'价格',width:60,align:'center'},
{field:'num',title:'数量',width:60,align:'center'},
{field:'amount',title:'小计',width:60,align:'center'},
{field:'status',title:'状态',width:60,align:'center',formatter:formatStatus}
]],
groupField:'groupField', //根据哪一个字段分组
view: groupview, //分组视图必需要加这个
//value:分组的值 rows:这一组的所有值
groupFormatter:function(value, rows){ //分组位置的显示
var totalNum = 0; //总数量
var totalAmount = 0;//总金额
for(let r of rows){
totalNum += r.num;
totalAmount += r.amount;
}
return value + ' - ' + `${rows.length}条数据
<span style="color: #00ee00">共${totalNum}个商品</span>
<span style="color: #aa2aee">总金额:${totalAmount}</span>
`;
}
});
})
4.Domain
public class PurchasebillitemVO {
//编号
private Long id;
//供应商名称
private String supplierName;
//采购员名称
private String buyerName;
//产品名称
private String prdouctName;
//产品类型名称
private String productTypeName;
//交易时间
private Date vdate;
//价格
private BigDecimal price;
//数量
private BigDecimal num;
//小计(计算:价格*数量)
private BigDecimal amount;
/**
* 单据状态
* 0待审,1已审，-1作废
*/
private Integer status = 0;
//分组字段
private String groupField;
public PurchasebillitemVO() {}
//使用这个构造器，直接把item变成vo(只要报表中需要的数据)
public PurchasebillitemVO(Purchasebillitem item, PurchasebillitemQuery query) {
this.id = item.getId();
this.supplierName = item.getBill().getSupplier().getName();
this.buyerName = item.getBill().getBuyer().getUsername();
this.prdouctName = item.getProduct().getName();
this.productTypeName = 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();
switch (query.getGroupType()){
case 1:
this.groupField = this.supplierName;
break;
case 2:
this.groupField = this.buyerName;
break;
case 3:
Calendar cal = Calendar.getInstance();
cal.setTime(vdate);
this.groupField = (cal.get(Calendar.MONTH)+1) + "月";
break;
default:
this.groupField = this.supplierName;
}
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getSupplierName() {
return supplierName;
}
public void setSupplierName(String supplierName) {
this.supplierName = supplierName;
}
public String getBuyerName() {
return buyerName;
}
public void setBuyerName(String buyerName) {
this.buyerName = buyerName;
}
public String getPrdouctName() {
return prdouctName;
}
public void setPrdouctName(String prdouctName) {
this.prdouctName = prdouctName;
}
public String getProductTypeName() {
return productTypeName;
}
public void setProductTypeName(String productTypeName) {
this.productTypeName = productTypeName;
}
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
public Date getVdate() {
return vdate;
}
public void setVdate(Date vdate) {
this.vdate = vdate;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public BigDecimal getNum() {
return num;
}
public void setNum(BigDecimal num) {
this.num = num;
}
public BigDecimal getAmount() {
return amount;
}
public void setAmount(BigDecimal amount) {
this.amount = amount;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public String getGroupField() {
return groupField;
}
public void setGroupField(String groupField) {
this.groupField = groupField;
}
}
2.HighChart---->报表
Flash: 加载速度慢 安全性低 容易崩溃 占用内存大
支持IE
H5:(canvas:画布) 页面漂亮 安全性高 性能低
不支持IE低版本
1.前台展示
1.导入js文件
2.页面引入
<!-- 引入highcharts的js支持 -->
<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>
<script src="/easyui/plugin/highcharts/code/modules/export-data.js"></script>
3.Jsp
按钮
<a href="javascript:;" data-method="chart3d" class="easyui-linkbutton" iconCls="icon-search">3D图</a>
弹出框
<div id="chartDialog" class="easyui-dialog" title="图表展示"
data-options="height:400,width:600,closed:true,modal:true">
<div id="container" style="height: 320px"></div>
</div>
4.Js
chart3d(){
//弹出相应的diagle
chartDialog.dialog("center").dialog("open");
$.post("/purchasebillitem/findCharts",function (result) {
//数据得到后进行展示
Highcharts.chart('chartDialog', {
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: 30,
dataLabels: {
enabled: true,
format: '{point.name}'
}
}
},
series: [{
type: 'pie',
name: '移上来',
// 数据
data: result
}]
});
})
2.后台查询数据
1.接口
List<Map> findCharts(PurchasebillitemQuery query);
2.实现
@Override
public List<Map> findCharts(PurchasebillitemQuery query) {
//准备要返回的数据
List<Map> mapList = new ArrayList<>();
//准备好相应的JPQL 供应商
String jpql = "select o.bill.supplier.name,sum(o.amount) from Purchasebillitem o group by o.bill.supplier.name";
//运行这条JPQL(查询多个数据使用数组接收)
List<Object[]> list = super.findByJpql(jpql);
for (Object[] objects : list) {
//把Object[]变成Map
Map map = new HashMap();
map.put("name",objects[0]);
map.put("y",objects[1]);
//把值传过去
mapList.add(map);
}
return mapList;
}
3.控制层
@RequestMapping("/findCharts")
@ResponseBody
public List<Map> findCharts(PurchasebillitemQuery query){
return purchasebillitemService.findCharts(query);
}
3.将分组条件并入
1.前台传递参数
chart3d(){
//弹出相应的diagle
chartDialog.dialog("center").dialog("open");
//把表单中的参数传过去
var params = searchForm.serializeObject();
$.post("/purchasebillitem/findCharts",params,function (result) {
//数据得到后进行展示
Highcharts.chart('chartDialog', {
。。。。。。。
})
2.后台接收--->query
//拿到分组的值
public String getGroupName(){
if(groupType==1){
return "o.bill.supplier.name";
}else if(groupType==2){
return "o.bill.buyer.username";
}else if(groupType==3){
return "MONTH(o.bill.vdate)";
}else{
return "o.bill.supplier.name";
}
}
3.业务层实现----Serviceimpl
@Override
public List<Map> findCharts(PurchasebillitemQuery query) {
//准备要返回的数据
List<Map> mapList = new ArrayList<>();
//准备一个分组的值
String groupName = query.getGroupName();
//准备好相应的JPQL
//String jpql = "select o.bill.supplier.name,sum(o.amount) from Purchasebillitem o group by o.bill.supplier.name";
// String jpql = "select o.bill.buyer.username,sum(o.amount) from Purchasebillitem o group by o.bill.buyer.username";
String jpql = "select "+groupName+",sum(o.amount) from Purchasebillitem o group by "+groupName;
//运行这条JPQL(查询多个数据使用数组接收)
List<Object[]> list = super.findByJpql(jpql);
for (Object[] objects : list) {
//把Object[]变成Map
Map map = new HashMap();
map.put("name",objects[0]);
map.put("y",objects[1]);
//把值传过去
mapList.add(map);
}
return mapList;
}
4.将高级查询带入报表
1.拼接条件
A.Query
//3D高级查询
/**
* String:不适合用于字符串拼接(每次都创建一个新的，性能比较低)
* StringBuffer:拼接字符串性能高,线程安全
* StringBuilder:拼接字符串性能最高,线程不安全
* 只返回 JPQL : where status = ? and date>= beginDate and ...
* @return
*/
public String createWhereJPQL(){
StringBuilder jpql = new StringBuilder();
if(beginDate!=null){
jpql.append(" and o.bill.vdate >=? ");
}
if(endDate!=null){
Date tempDate = DateUtils.addDays(endDate,1);
jpql.append(" and o.bill.vdate<? ");
}
if(status!=null){
jpql.append(" and o.bill.status=? ");
}
return jpql.toString().replaceFirst("and","where");
}
B.业务层--->Serviceimpl
@Override
public List<Map> findCharts(PurchasebillitemQuery query) {
//准备要返回的数据
List<Map> mapList = new ArrayList<>();
//准备一个分组的值
String groupName = query.getGroupName();
//3D高级查询
String whereJpql = query.createWhereJPQL();
//准备好相应的JPQL
//String jpql = "select o.bill.supplier.name,sum(o.amount) from Purchasebillitem o group by o.bill.supplier.name";
// String jpql = "select o.bill.buyer.username,sum(o.amount) from Purchasebillitem o group by o.bill.buyer.username";
String jpql = "select "+groupName+",sum(o.amount) from Purchasebillitem o "+whereJpql+" group by "+groupName;
//运行这条JPQL(查询多个数据使用数组接收)
List<Object[]> list = super.findByJpql(jpql);
for (Object[] objects : list) {
//把Object[]变成Map
Map map = new HashMap();
map.put("name",objects[0]);
map.put("y",objects[1]);
//把值传过去
mapList.add(map);
}
return mapList;
}
2.解决参数问题
- 解决没有赋值(？)
@Override
public List<Map> findCharts(PurchasebillitemQuery query) {
//准备要返回的数据
List<Map> mapList = new ArrayList<>();
//准备一个分组的值
String groupName = query.getGroupName();
//3D高级查询
String whereJpql = query.createWhereJPQL();
String jpql = "select "+groupName+",sum(o.amount) from Purchasebillitem o "+whereJpql+" group by "+groupName;
//运行这条JPQL(查询多个数据使用数组接收)
List<Object[]> list = super.findByJpql(jpql,query.getStatus);
for (Object[] objects : list) {
//把Object[]变成Map
Map map = new HashMap();
map.put("name",objects[0]);
map.put("y",objects[1]);
//把值传过去
mapList.add(map);
}
return mapList;
}
2.类型数量没对应上
1.SQL语句的拼接
//存放SQL语句中？的值
private List params = new ArrayList();
//省略get/set
//3D高级查询
public String createWhereJPQL(){
StringBuilder jpql = new StringBuilder();
if(beginDate!=null){
jpql.append(" and o.bill.vdate >=? ");
params.add(beginDate);
}
if(endDate!=null){
Date tempDate = DateUtils.addDays(endDate,1);
jpql.append(" and o.bill.vdate<? ");
params.add(tempDate);
}
if(status!=null){
jpql.append(" and o.bill.status=? ");
params.add(status);
}
return jpql.toString().replaceFirst("and","where");
}
2.传条件--->impl
@Override
public List<Map> findCharts(PurchasebillitemQuery query) {
//准备要返回的数据
List<Map> mapList = new ArrayList<>();
//准备一个分组的值
String groupName = query.getGroupName();
//3D高级查询
String whereJpql = query.createWhereJPQL();
//获取到？对应的参数值
List params = query.getParams();
String jpql = "select "+groupName+",sum(o.amount) from Purchasebillitem o "+whereJpql+" group by "+groupName;
//运行这条JPQL(查询多个数据使用数组接收) .toArray()：集合转数组
List<Object[]> list = super.findByJpql(jpql,params.toArray());
for (Object[] objects : list) {
//把Object[]变成Map
Map map = new HashMap();
map.put("name",objects[0]);
map.put("y",objects[1]);
//把值传过去
mapList.add(map);
}
return mapList;
}
5607
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
