8-15智能商贸报表和图形报表

1.课程介绍
1. 采购订单报表页面查询（掌握）
2. 采购订单图形报表处理（掌握）

一,采购单报表页面查询

datagrid
页面效果展示js

$(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>";
        }
    });
});

后台数据
定义一个PurchaseBillItemVo 类
用来处理分组问题, 还有date时间问题,
用构造方法创建对象,并赋值

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(Purchasebillitem item){
        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();
    }

    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 = this.vdate; 7 8
            this.groupField = (DateUtils.toCalendar(this.vdate).get(Calendar.MONTH)+1)+"月";
        }else{
            this.groupField = this.supplier;
        }
    }

    public static void main(String[] args) {
        System.out.println(DateUtils.toCalendar(new Date()).get(Calendar.MONTH)+1);
    }

    public  PurchasebillitemVo(){}



    public Long getId() {
        return id;
    }

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

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

    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 String getGroupField() {
        //设置分组字段 默认按照供应商分组
       // this.groupField = this.supplier;
        return groupField;
    }

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

根据前台传来的日期,状态,和用户名来查询query

public class PurchasebillitemQuery extends BaseQuery{


    private String name;


    public String getName() {
        return name;
    }

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

    //接收时间和状态

    private Date beginDate;

    private Date endDate;

    private Integer status;

    //介绍分组字段
    private String groupBy = "o.bill.supplier.name";

    public String getGroupBy() {
        return groupBy;
    }

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

    public Date getBeginDate() {
        return beginDate;
    }

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    public void setBeginDate(Date beginDate) {
        this.beginDate = beginDate;
    }

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

    //抽取查询
    @Override
    public Specification createSpecification() {
        Date tempDate = null;
        if(this.endDate!=null){
            tempDate =  DateUtils.addDays(this.endDate,1 );
            System.out.println(tempDate);
        }
        //查询 Purchasebillitem select o from purchasebillitem o where o.bill.vdate = 1? and  o.bill.status
        Specification<Purchasebillitem> spe = Specifications.<Purchasebillitem>and().
                ge(this.beginDate != null, "bill.vdate",this.beginDate ).
                lt(this.endDate!=null,"bill.vdate",tempDate).
                eq(this.status!=null && !"".equals(this.status),"bill.status",this.status)
                .build();

        return spe;
    }

service层处理

 @Autowired
    private PurchasebillitemRepository purchasebillitemRepository;

    @Override
    public List<PurchasebillitemVo> findItems(PurchasebillitemQuery itemQuery) {
        //查询所有的采购的明细
        List<Purchasebillitem> items = purchasebillitemRepository.findByQuery(itemQuery);
        List<PurchasebillitemVo> itemsVoList = new ArrayList<>();
        for (Purchasebillitem item : items) {
            PurchasebillitemVo itemVo = new PurchasebillitemVo(item,itemQuery.getGroupBy());
            itemsVoList.add(itemVo);
        }
        return itemsVoList;
    }

controller层处理
这里需要注意的是分组报表查询,因为前台页面效果是这样的
所以需要把查询出来的List放入map里去

 //分组报表查询
   
    @RequestMapping("/findAllItemVo")
    @ResponseBody
    public Map findPurchaseBillItemVo(PurchasebillitemQuery billitemQuery){

        Map mp = new HashMap();
        List<PurchasebillitemVo> items = purchasebillitemService.findItems(billitemQuery);
        mp.put("total", items.size());
        mp.put("rows", items);

        return mp;
    }

前台数据完成
引入js

<script type="text/javascript" src="/js/model/purchasebillitem.js"></script>

js

$(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>";
        }
    });
});

二,HighChart,全球使用,echart(百度开发,开源免费)

引入,HighChart
3D报表图,2D只需改变 alpha到0度就可以了

 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');

通过文档看到其展示数据的类型是[{},{},{}]这样,所以我们需要相应的查询语句
拼接sql语句

 //定义一个容器
  List params = new ArrayList<>();
  
    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");

    }

    public List getParams() {
        return params;
    }

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

查询到了图标需要的值后,在把数据弄成我们想要的样子
这里要注意jpql版本不同的bug,//JPA框架的bug的
group by的语句 不要写？ group by 直接就拼接sql,会导致数据的丢失,
查询不出来

 //根据jpql语句查询的数据
    @Override
    public List findItemsByJql(PurchasebillitemQuery itemQuery) {
		  List<Map> mpList = 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 list->数组 list.toArray()
        List<Object[]> list = purchasebillitemRepository.findByJpql(jpql,itemQuery.getParams().toArray());
		 String jpql1 = "select o.bill.supplier.name,sum(b.totalAmount) from Purchasebillitem o join o.bill b group by ?";

        List<Object[]> list1 = purchasebillitemRepository.findByJpql(jpql1,groupBy);
        System.out.println("list1:"+list1);

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

最后controlller层处理

 //查询图形报表
    @RequestMapping("/findAllGraphic")
    @ResponseBody
    public List findAllGraphic(PurchasebillitemQuery billitemQuery){

        List itemsListMap = purchasebillitemService.findItemsByJql(billitemQuery);

        return itemsListMap;
    }