采购报表展示饼状图

数据表格的形式：

package cn.itsource.domain;

import com.fasterxml.jackson.annotation.JsonFormat;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.format.annotation.DateTimeFormat;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;

/**
 *  数据表格对象
 *  List<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 = ""; //分组字段

    public String getSupplier() {
        return supplier;
    }

    public void setSupplier(String supplier) {
        this.supplier = supplier;
    }

    public String getBuyer() {
        return buyer;
    }

    public void setBuyer(String buyer) {
        this.buyer = buyer;
    }

    public String getProduct() {
        return product;
    }

    public void setProduct(String product) {
        this.product = product;
    }

    public String getProductType() {
        return productType;
    }

    public void setProductType(String productType) {
        this.productType = productType;
    }
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    public Date getVdate() {
        return vdate;
    }
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    public void setVdate(Date vdate) {
        this.vdate = vdate;
    }

    public BigDecimal getNum() {
        return num;
    }

    public void setNum(BigDecimal num) {
        this.num = num;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    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 Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getGroupField() {
        return groupField;
    }

    public void setGroupField(String groupField) {
        this.groupField = groupField;
    }

    public PurchasebillitemVo(){}
	/提供有参的构造方法来传值
    public PurchasebillitemVo(Purchasebillitem purchasebillitem,String groupBy){
        //编号
        this.id = purchasebillitem.getId();
        //供应商名称
        this.supplier=purchasebillitem.getBill().getSupplier().getName();
        //采购员名称
        this.buyer=purchasebillitem.getBill().getBuyer().getUsername();
        //产品名称
        this.product=purchasebillitem.getProduct().getName();
        //产品分类名称
        this.productType=purchasebillitem.getProduct().getTypes().getName();
        //交易时间
        this.vdate=purchasebillitem.getBill().getVdate();
        //采购数量
        this.num=purchasebillitem.getNum();
        //价格
        this.price=purchasebillitem.getPrice();
        //小计 = 价格*数量
        this.amount=purchasebillitem.getAmount();
        //采购单据状态
        this.status=purchasebillitem.getBill().getStatus();
        //分组字段
        this.groupField = this.supplier;
        if(("o.bill.buyer.username").equals(groupBy)){
            this.groupField = this.buyer;
        }else if(("MONTH(o.bill.vdate)").equals(groupBy)){
            //取到月份
            int month  =   (DateUtils.toCalendar(vdate).get(Calendar.MONTH)+1);
            this.groupField = month+"月";
        }
    }
}

在IPurchasebillitemSevice里面加上一个方法

//采购订单数据报表方法
   List<PurchasebillitemVo> findItems(PurchasebillitemQuery purchasebillitemQuery);
   

在实现类复写方法：

@Override
   //返回PurchasebillitemVo
   public List<PurchasebillitemVo> findItems(PurchasebillitemQuery purchasebillitemQuery) {
       List<PurchasebillitemVo> vos = new ArrayList<>();
       List<Purchasebillitem> byQuery = purchasebillitemRepository.findByQuery(purchasebillitemQuery);

       for (Purchasebillitem purchasebillitem : byQuery) {
           PurchasebillitemVo purchasebillitemVo = new PurchasebillitemVo(purchasebillitem, purchasebillitemQuery.getGroupBy());
           vos.add(purchasebillitemVo);
       }
       return vos;
   }

通过这个方法拿到数据并且以PurchasebillitemVo的对象返回数据；

在controller层里面调用得到数据并以json形式返回给前端：

@RequestMapping("/findItems")
  @ResponseBody
   public Map finditems(PurchasebillitemQuery purchasebillitemQuery){
      Map mp=new HashMap();
      List<PurchasebillitemVo> items = purchasebillitemService.findItems(purchasebillitemQuery);
      for (PurchasebillitemVo item : items) {
          mp.put("total",items.size());
          mp.put("rows",items);
      }
      return mp;
  }

前端页面：

<%--
 Created by IntelliJ IDEA.
 User: 12575
 Date: 2019/10/17
 Time: 18:48
 To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
   <title>Title</title>
   <%@include file="/WEB-INF/head.jsp" %>
   <!-- 引入datagrid-view的支持 -->
   <script type="text/javascript" src="/easyui/plugin/groupview/datagrid-groupview.js"></script>
   <script src="/highcharts/code/highcharts.js"></script>
   <script src="/highcharts/code/highcharts-3d.js"></script>
   <script src="/highcharts/code/modules/exporting.js"></script>
   <script src="/highcharts/code/modules/export-data.js"></script>
   <script type="text/javascript" src="/js/purchasebillItem.js"></script>
</head>
<body>

<%--查询工具栏--%>
<div id="tb" style="padding:10px;height:auto">
   <div>
       <div id="cc" class="easyui-calendar"></div>
       <form id="searchForm" action="/purchasebill/download" method="post">
           日期 ： <input name="beginDate" class="easyui-datebox" style="height:32px" sharedCalendar="#cc">
           - <input name="endDate" class="easyui-datebox" style="height:32px" sharedCalendar="#cc">

           状态 ：<select class="easyui-combobox" name="status"
                       data-options="panelHeight:'auto'">
           <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'" style="width:100px">
           <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>


<!-- 采购订单的表格-->
<table id="purchasebillitemGrid"></table>
//饼状图
<div id="purchaseBillItemDialog" class="easyui-dialog" data-options="closed:true,modal:true" title="功能操作" style="width:600px;height:300px; ">
</body>
</html>

数据表格的js代码：

$('#purchasebillitemGrid').datagrid({
       title:'分组表格',
       fit:true,
       fixed:true,
       fitColumns:true,
       toolbar:'#tb',
       remoteSort:false,
       singleSelect:true,
       fitColumns:true,
       url:'/purchasebillItem/findItems',
       columns:[[
           {field:'id',title:'编号',width:100},
           {field:'supplier',title:'供应商',width:100},
           {field:'buyer',title:'采购员',width:100},
           {field:'product',title:'产品',width:100},
           {field:'productType',title:'产品类型',width:100},
           {field:'vdate',title:'日期',width:100},
           {field:'num',title:'数量',width:100},
           {field:'price',title:'单价',width:100},
           {field:'amount',title:'小计',width:100}, {field:'status',title:'状态',width:100,formatter:function (action) {
                   var data = {
                       0:"<div style='color:red;'>待审</div>",
                       1:"<div style='color: green'>已审</div>",
                       "-1":"<div><s>作废</s></div>"
                   };
                   return data[action];
               }}
       ]],
       groupField:'groupField',
       view: groupview,
       groupFormatter:function(value, rows){
           //总数据量
           var totalNum = 0;
           //总金额
           var totalAmount = 0;
           for(var i=0;i<rows.length;i++){
               var row = rows[i];
               totalNum += row.num;
               totalAmount += row.amount;
           }
           return value + ' - ' + rows.length + ' 条数据 '+ totalNum+' 件商品,总金额:'+totalAmount+"元";
       }
   });

高级查询js代码：

//表格
   var purchasebillitemGrid = $("#purchasebillitemGrid");
   //给所有a标签都注册一个事件
   $("a").on("click", function () {
       //动态获取data-method属性对应的值
       var method = $(this).data("method");
       //method不能为空
       if(method){
           //动态触发事件

           itsource[method]();
       }
   });
   var purchaseBillItemDialog=$("#purchaseBillItemDialog");
   itsource={
       //高级查询
       "search": function () {
           //把form表单元素，直接封装成一个json对象
           var jsonObj = $("#searchForm").serializeObject();
           //加载datagrid
           purchasebillitemGrid.datagrid('load', jsonObj);
       }
       }

后台query层代码：

package cn.itsource.query;


import cn.itsource.domain.Employee;
import cn.itsource.domain.Purchasebillitem;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.github.wenhao.jpa.Specifications;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PurchasebillitemQuery extends BaseQuery{

   //    //自身条件
   private Date beginDate ;//开始时间
   private Date endDate;//结束时间
   private Integer status;//审核状态

   //分组字段
   private String groupBy;

   //前台参数的值
   private List params = new ArrayList<>();

   public List getParams() {
       return params;
   }

   public void setParams(List params) {
       this.params = params;
   }

   @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
   public Date getBeginDate() {
       return beginDate;
   }

   @DateTimeFormat(pattern = "yyyy-MM-dd")
   public void setBeginDate(Date beginDate) {
       this.beginDate = beginDate;
   }
   @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
   public Date getEndDate() {
       return endDate;
   }
   @DateTimeFormat(pattern = "yyyy-MM-dd")
   public void setEndDate(Date endDate) {
       this.endDate = endDate;
   }

   public Integer getStatus() {
       return status;
   }

   public void setStatus(Integer status) {
       this.status = status;
   }



   public String getGroupBy() {
       return groupBy;
   }

   public void setGroupBy(String groupBy) {
       this.groupBy = groupBy;
   }



   public Specification createSpecification() {

       //处理结束时间
       Date tempDate=null;
       if(this.getEndDate() != null){
           tempDate = DateUtils.addDays(this.getEndDate(), 1);
       }
       //ge 大于等 vdate>= beginDate and vdate< endDate+1
       Specification<Purchasebillitem> specification = Specifications.<Purchasebillitem>and()
               .ge(this.beginDate != null,"bill.vdate", this.getBeginDate())
               .lt(this.endDate!=null, "bill.vdate",tempDate)
               .eq(this.status != null ,"bill.status",this.getStatus())
               .build();
       return specification;
   }

普通饼状图
在selvice里面加上一个方法：

@Override
   //图形报表查询 --jpql 查询
   public List<Object[]> findCharts(PurchasebillitemQuery purchasebillitemQuery) {
       String jpql="select "+purchasebillitemQuery.getGroupBy()+",sum(o.bill.totalAmount) from Purchasebillitem o " + purchasebillitemQuery.getWhereSql()+
               " group by "+purchasebillitemQuery.getGroupBy();
       List<Object[]> byJpql = purchasebillitemRepository.findByJpql(jpql,purchasebillitemQuery.getParams().toArray());
       return byJpql;
   }

在query层拼接jpql语句：

//组装sql的方法 where bill.vdate >= 1111  and bill.vdate< 111 and bill.status = 1
   public String getWhereSql(){
       StringBuffer whereSql = new StringBuffer();
       if(this.beginDate != null && !"".equals(this.beginDate)){
           whereSql.append("and").append(" o.bill.vdate >=?");
           params.add(this.beginDate);
       }
       if(this.endDate != null && !"".equals(this.endDate)){

           Date tempDate=null;
           if(this.getEndDate() != null){
               tempDate = DateUtils.addDays(this.getEndDate(), 1);
           }
           whereSql.append("and").append(" o.bill.vdate < ?");
           params.add(tempDate);
       }

       if(this.status != null && !"".equals(this.status)){

           whereSql.append("and").append(" o.bill.status = ?");
           params.add(this.status);
       }

       return whereSql.toString().replaceFirst("and", "where");
   }

在controller层调用：

   //查询数据
   //jpql select p.supplier,sum(amount) from purchasebillitem p group by p.supplier
   // [{ name:'成都供应商',y:8000},{name:'东莞供应商',90000}....] --安装供应商分组 算出总金额
   @RequestMapping("/findCharts")
   @ResponseBody
   public List<Map> findCharts(PurchasebillitemQuery purchasebillitemQuery){
       //查询数据[{cd供应商,11}，{dw，22}]
       System.out.println("==========================55");
       List<Object[]> charts = purchasebillitemService.findCharts(purchasebillitemQuery);
       List<Map> mapList=new ArrayList<>();
       for (Object[] chart : charts) {
           Map mp = new HashMap();
           mp.put("name",chart[0]);
           mp.put("y",chart[1]);
           mapList.add(mp);
       }
       return mapList;
   }

前端js代码：

"charts2D": function () {

           //获取查询表单的参数
           var params = $("#searchForm").serializeObject();
           //发送ajax请求 到后台查询
           //$.post()
           $("#purchaseBillItemDialog").dialog("center").dialog("open");
           $.post("/purchasebillItem/findCharts", params, function (result) {
               //数据表格
               Highcharts.chart('purchaseBillItemDialog', {
                   chart: {
                       plotBackgroundColor: null,
                       plotBorderWidth: null,
                       plotShadow: false,
                       type: 'pie'
                   },
                   title: {
                       text: '采购订单的数据情况'
                   },
                   tooltip: {
                       pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
                   },
                   plotOptions: {
                       pie: {
                           allowPointSelect: true,
                           cursor: 'pointer',
                           dataLabels: {
                               enabled: true,
                               format: '<b>{point.name}</b>: {point.percentage:.1f} %',
                               style: {
                                   color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black'
                               }
                           }
                       }
                   },
                   series: [{
                       name: '总金额比例',
                       colorByPoint: true,
                       data: result
                   }]
               });
           });

       },

3D饼状图：

"charts3D": function () {
           //获取查询表单的参数
           var params = $("#searchForm").serializeObject();
           //发送ajax请求 到后台查询
           //$.post()
           $("#purchaseBillItemDialog").dialog("center").dialog("open");

           $.post("/purchasebillItem/findCharts", params, function (result) {

               Highcharts.chart('purchaseBillItemDialog', {
                   chart: {
                       type: 'pie',
                       options3d: {
                           enabled: true,
                           alpha: 45,
                           beta: 0
                       }
                   },
                   title: {
                       text: 'Browser market shares at a specific website, 2014'
                   },
                   tooltip: {
                       pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
                   },
                   plotOptions: {
                       pie: {
                           allowPointSelect: true,
                           cursor: 'pointer',
                           depth: 70,
                           dataLabels: {
                               enabled: true,
                               format: '{point.name}'
                           }
                       }
                   },
                   series: [{
                       type: 'pie',
                       name: 'Browser share',
                       data: result
                   }]
               });


           });
       }
   }