普通报表和hightcharts3D报表的使用

普通报表和饼行3D使用
使用报表能更加直观的统计数据，来进行数据的分析
常见的几种图形框架
flash(Flex actionScript) —界面很好看 容易崩溃
HTML5 (Canvas画布) – 手动画图 很麻烦
echarts —百度开发–国内公司在使用
hightcharts – 全球在使用
报表的使用：
引入js文件
datagrid-groupview.js
定义普通表格

创建这个表格

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; //价格

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

    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() {
        return groupField;
    }

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

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

    }
   //添加个无参构造
    public PurchasebillitemVo(){}

}

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 Date status;

    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 Date getStatus() {
        return status;
    }

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

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

    public String getGroupBy() {
        return groupBy;
    }

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


    //抽取查询
    @Override
    public Specification createSpecification() {
        Date tempDate = null;
        if (this.beginDate !=null){
            tempDate = DateUtils.addDays(this.endDate, 1);
        }

        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;
    }
    //自己组装条件
    List params  =  new ArrayList<>();

    public List getParams() {
        return params;
    }

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

    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 class PurchasebillitemServiceImpl extends BaseServiceImpl<Purchasebillitem,Long> implements IPurchasebillitemService {

    @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;
    }
    //根据jpql语句查询的数据
    @Override
    public List findItemsByJql(PurchasebillitemQuery itemQuery) {

        List<Map> mpList = new ArrayList<>();
        String groupBy = itemQuery.getGroupBy();
        //根据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.toArray()
        List<Object[]> list = purchasebillitemRepository.findByJpql(jpql,itemQuery.getParams().toArray());
        //JPA框架的bug的 group by的语句 不要写？  group by 直接就拼接sql
        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;
    }

}

 //分组报表查询
    @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;
    }

    //查询图形报表
    @RequestMapping("/findAllGraphic")
    @ResponseBody
    public List findAllGraphic(PurchasebillitemQuery billitemQuery){
        List itemsListMap = purchasebillitemService.findItemsByJql(billitemQuery);
        return itemsListMap;
    }

}