Shiro_项目_采购报表功能

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;
}

2.4.7 最终图形报表效果