报表简单设计

**1报表分为数据报表 和 图形报表 这里用的 HighChart,插件去官网下载 **
数据报表

图形报表

1,新建一个普通类 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;
        }
    }

Purchasebillitem 类是一个实体类,

@Entity
@Table(name="purchasebillitem")
public class Purchasebillitem extends BaseDomain {


    private BigDecimal price; //价格
    private BigDecimal num; //数量
    private BigDecimal amount; //产品小计
    private String descs; //描述

    @ManyToOne(fetch = FetchType.LAZY,optional = false)
    @JoinColumn(name = "product_id")
    private Product product;

    @ManyToOne(fetch = FetchType.LAZY,optional = false)
    @JoinColumn(name = "bill_id")
    @JsonIgnore //返回页面 不展示出来
    private Purchasebill bill;

在IPurchasebillitemService 写两个方法 ,用来组装和查询数据

 //数据表格报表
    List<PurchaseBillItemVo> findItems(PurchasebillitemQuery itemQuery);
    //数据图形报表
    List findItemsByJql(PurchasebillitemQuery itemQuery);

PurchaseBillItemQuery 重新覆写方法

private String name;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date beginDate;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date endDate;
    private Integer status;

    private String groupBy = "o.bill.supplier.name";

    //装数据的容器

    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");
    }
    //抽取查询
    @Override
    public Specification createSpecification() {
        Date tempDate = null;
        if(endDate!=null){
            tempDate = DateUtils.addDays(endDate,1);
        }
        //根据条件把数据返回即可
        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();
        return spec;
    }

然后在实现层写实现

 @Autowired
    private PurchasebillitemRepository purchasebillitemRepository;

    //数据报表查询
    @Override
    public List<PurchaseBillItemVo> findItems(PurchasebillitemQuery itemQuery) {

        //根据传递的条件拿到所有的明细数据(并且把它们变成咱们的vo对象)
        List<Purchasebillitem> list = purchasebillitemRepository.findByQuery(itemQuery);
        ArrayList<PurchaseBillItemVo> billItemVos = new ArrayList<>();
        for (Purchasebillitem purchasebillitem : list) {
            PurchaseBillItemVo itemVo = new PurchaseBillItemVo(purchasebillitem, itemQuery.getGroupBy());
            billItemVos.add(itemVo);
        }
        return billItemVos;
    }

    //图形报表查询
    @Override
    public List<Map> findItemsByJql(PurchasebillitemQuery itemQuery) {
        //查询到图表需要的值，并且封装成相应的格式[{name:aa,y:43},{name:bb,y:20},...]
        System.out.println(itemQuery.getGroupBy());
        String groupBy = itemQuery.getGroupBy();
        ArrayList<Map> listmaps = new ArrayList<>();
        //拼接sql //根据jpql语句查询 o.bill.supplier.name
        String jpql = "select "+groupBy+",sum(b.totalAmount) from Purchasebillitem o join o.bill b "+itemQuery.getWhereSql()+"  group by "+groupBy;
        //得到容器的list 里面是前台传过来的查询条件   查询出来的list list->数组 list.toArray()
        List<Object[]> list = purchasebillitemRepository.findByJpql(jpql, itemQuery.getParams().toArray());

        //JPA框架的bug的 group by的语句 不要写 ？  group by 直接就拼接sql
       /* String jpql1 = "select  "+itemQuery.getGroupBy()+",sum(b.totalAmount) from Purchasebillitem o join o.bill b group by ? ";
        List<Object[]> list1 = purchasebillitemRepository.findByJpql(jpql1,itemQuery.getGroupBy());
        System.out.println("list1:"+list1);*/

        for (Object[] objects : list) {
            Map<Object, Object> map = new HashMap<>();
            map.put("name",objects[0]);
            map.put("y",(BigDecimal)objects[1]);
            listmaps.add(map);
        }
        return listmaps;
    }

然后是.PurchaseBillItemController

//注：根据之前的分析，返回的数据在的格式是{rows:[{},{},.]}
    @RequestMapping("/findAllItemVo")
    @ResponseBody
    public Map<String,Object> findAllItemVo(PurchasebillitemQuery itemQuery){
        Map<String,Object> map = new HashMap();
        map.put("rows", purchasebillitemService.findItems(itemQuery));
        return  map;
    }
    @RequestMapping("/findAllGraphic")
    @ResponseBody
    public List<Map> findAllGraphic(PurchasebillitemQuery itemQuery){

      List itemsByJql = purchasebillitemService.findItemsByJql(itemQuery);
      return  itemsByJql;
    }

前台数据展示
引入插件 可以去官网下载和查看,

    <script type="text/javascript" src="/easyui/plugin/datagridview/datagrid-groupview.js"></script>
    <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>

<body>
<table id="purchasebillitemDatagrid"></table>
<div id="tb" style="padding:5px;height:auto">
    <!-- 这部门是查询的功能 -->
    <div>
        <form id="searchForm" action="/purchasebill/download" method="post">
            日期：
            <input name="beginDate" class="easyui-datebox" style="height:32px" >
            - <input name="endDate" class="easyui-datebox" style="height:32px" >
            状态:
            <select id="status" class="easyui-combobox"   data-options="panelHeight:'auto'" name="status" style="width:100px;">
                <option value="">--请选择--</option>
                <option value="0">待审</option>
                <option value="1">已审</option>
                <option value="-1">作废</option>
            </select>
            <select class="easyui-combobox" name="groupBy"
                    data-options="panelHeight:'auto',width:100">
                <option value="o.bill.supplier.name">供应商</option>
                <option value="o.bill.buyer.username">采购员</option>
                <option value="MONTH(o.bill.vdate)">月份</option>
            </select>
            <a href="#" data-method="search"  class="easyui-linkbutton" iconCls="icon-search">查找</a>
            <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>
        </form>
    </div>
</div>

<!-- 准备一个对话框-->
<div id="purchasebillitemDialog" class="easyui-dialog" title="数据操作" data-options="closed:true,modal:true"  style="width:400px;height:300px;padding:10px">

</div>
</body>

然后.purchaseBillItem.js页面

function statusFormatter(value){
    var data = {
        0:"<span style='color:blue'>待审</span>",
        1:"<span style='color:green'>已审</span>",
        "-1":"<span style='color:gray'><s>作废</s></span>"
    }
    return data[value];
}

$(function(){
    //定义form表单
    var searchForm = $("#searchForm");
    var purchasebillitemDialog = $("#purchasebillitemDialog");

    //绑定事件 easyui 第二天的时候
    $("a[data-method]").on('click',function(){
       //获取 data-method属性 <a data-method="seacher">
        var methodName = $(this).data("method");
        //动态调用方法 itsource["seacher"]
        itsource[methodName]();
    });
    //对象
    var itsource = {
        search:function(){
          //怎么完成高级查询 jquery.jdirk.js 这个方法 这是jquery扩展方法
            //该方法返回一个 JSON Object，返回对象中的每个数据都表示一个表单控件值。
            var param = searchForm.serializeObject();
            //发送查询数据库 --加载表格 发送请求 /purchasebillitem/page
            $("#purchasebillitemDatagrid").datagrid('load',param);
        },
        charts3D:function(){
            //发送ajax请求到后台查询的数据吧

            var param = searchForm.serializeObject();
            $.post("/purchasebillitem/findAllGraphic",param,function(result){

                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');
        },
        charts2D:function(){

            var param = searchForm.serializeObject();
            $.post("/purchasebillitem/findAllGraphic",param,function(result){

                Highcharts.chart('purchasebillitemDialog', {
                    chart: {
                        type: 'pie',
                        options3d: {
                            enabled: true,
                            alpha: 0, //倾斜角度
                            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');

        }
    }
});

//数据展示
$(function(){
    $('#purchasebillitemDatagrid').datagrid({
        width:500,
        height:250,
        fit:true,
        rownumbers:true,
        remoteSort:false,
        nowrap:false,
        fitColumns:true,
        toolbar:'#tb',
        url:'/purchasebillitem/findAllItemVo',
        columns:[[
            {field:'id',title:'编号',width:100,sortable:true},
            {field:'supplier',title:'供应商名称',width:80,align:'right',sortable:true},
            {field:'buyer',title:'采购员名称',width:80,align:'right',sortable:true},
            {field:'product',title:'产品名称',width:150,sortable:true},
            {field:'productType',title:'产品分类',width:60,align:'center'},
            {field:'vdate',title:'交易时间',width:180,align:'right',sortable:true},
            {field:'num',title:'数量',width:50,sortable:true},
            {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:#6b3980;'>总金额:"+totalAmount+"</span>";
        }
    });
});