智能销售系统的订单采购和报表

订单采购

组合关系

组合关系就是强聚合关系，最强级联，一方放弃维护
聚合就是双向的多对一，一对多
单据都是组合关系
保存的时候双方都能找到对象

//一方的配置
/**
    cascade = CascadeType.ALL:包含所有级联(增删改)
    orphanRemoval = true:孤儿删除
    mappedBy = "bill"：放弃关系维护
*/
@OneToMany(cascade = CascadeType.ALL, mappedBy = "bill", fetch = FetchType.LAZY, orphanRemoval = true)
private List<Purchasebillitem> items = new ArrayList<Purchasebillitem>();

//多方的配置
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "bill_id")
@JsonIgnore
private Purchasebill bill;// 组合关系,非空

日期查询问题

SpringMVC获取和设置日期

在get方法上面加

 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")

在set方法上面加

@DateTimeFormat(pattern = "yyyy-MM-dd")

easyui的日期控件

<div id="cc" class="easyui-calendar"></div>
<input name="beginDate" class="easyui-datebox" data-options="sharedCalendar:'#cc'"
                         style="width:120px">

解决时分秒问题

//准备一个方法，创建相应的条件规则
public Specification createSpec(){
    //结束时间加一天
    Date tempDate = null;
    if(endDate!=null){
        tempDate = DateUtils.addDays(endDate, 1);
    }
    //gt:大于  ge:大于等于
    //lt:小于  le:小于等于
    Specification<Purchasebill> spec = Specifications.<Purchasebill>and()
            .ge(beginDate!=null,"vdate",beginDate)
            .lt(endDate!=null,"vdate",tempDate)
            .eq(status!=null,"status",status)
            .build();
    return spec;
}

js代码中数据的展示

//完成editgrid的功能
//定义定量
//dg:拿到编辑的grid  defaultRow:默认有哪些数据  insertPosition:插入数据的位置(底部)
var dg = $("#itemsGrid"),
    defaultRow = { product: "", productColor: "", productImg: "", num: 0, price: 0, amount: 0, descs: "" },
    insertPosition = "bottom";

// 对grid的初始化设置
var dgInit = function () {

    var getColumns = function () {
        var result = [];
        //商品搞成下拉框，从后台获取到商品数据
        var normal = [
            {
                field: 'product', title: '商品', width: 80,
                editor: {
                    type: "combobox",
                    options: {
                        required: true,
                        panelHeight:'auto',
                        valueField:'id',
                        textField:'name',
                        url:'/util/findProducts'
                    }
                },
                //加上format显示产品的名称
                formatter:function (v,r,i) {
                    if(v)return v.name;
                }
            },
            {
                field: 'productColor', title: '颜色', width: 40,
                formatter: function (v, r, i) {
                    if(r && r.product){
                        return `<div style="height: 20px;width: 20px;background: ${r.product.color}"></div>`;
                    }
                }
            },
            {
                field: 'productImg', title: '图片', width: 100,
                formatter: function (v, r, i) {
                    if(r && r.product){
                        return "<img src='"+r.product.smallpic+"' alt='没有图片' />";
                    }
                }
            },
            {
                field: 'num', title: '数量', width: 100,
                editor: {
                    type: "numberbox", //只允许输入数字
                    options: {
                        precision:2, //保留两位小数
                        required: true
                    }
                }
            },
            {
                field: 'price', title: '价格', width: 100,
                editor: {
                    type: "numberbox",
                    options: {
                        precision:2,
                        required: true
                    }
                }
            },
            {
                field: 'amount', title: '小计', width: 100,
                formatter: function (v, r, i) {
                    if(r.num && r.price){
                        //toFixed:保存几位小数
                        return (r.num * r.price).toFixed(2);
                    }
                    return 0;
                }
            },
            {
                field: 'descs', title: '备注', width: 100,
                editor: {
                    type: "text"
                }
            }
        ];
        result.push(normal);

        return result;
    };
    var options = {
        idField: "ID",
        rownumbers: true,
        fitColumns: true,
        fit: true,
        border: true,
        title:"明细编辑",
        singleSelect: true,
        toolbar:"#itemsBtns",
        columns: getColumns(),
        //表示开启单元格编辑功能
        enableCellEdit: true
    };

    dg.datagrid(options);

};

//插入的行的位置(索引)
var getInsertRowIndex = function () {
    return insertPosition == "top" ? 0 : dg.datagrid("getRows").length;
}

//按钮的绑定事件
var buttonBindEvent = function () {
    //添加一行数据
    $("#btnInsert").click(function () {
        var targetIndex = getInsertRowIndex(), targetRow = $.extend({}, defaultRow, { ID: $.util.guid() });
        //在datagrid中插入一行数据
        dg.datagrid("insertRow", { index: targetIndex, row: targetRow });
        //哪一行的哪一列要进行编辑
        dg.datagrid("editCell", { index: targetIndex, field: "product" });
    });

    //删除一行数据
    $("#btnRemove").click(function () {
        //获取选中的行
        var row = dg.datagrid("getSelected");
        //获取这一行的索引
        var index = dg.datagrid("getRowIndex",row);
        //根据索引删除这一行
        dg.datagrid("deleteRow",index);
    });
};


//调用是相应的方法
dgInit(); buttonBindEvent();

添加和修改

添加时清空明细
dg.datagrid("loadData",[]);
修改时回显

//回显明细数据
//复制一个明细数据
var newItems = [...row.items];
dg.datagrid("loadData",newItems);

保存时提交明细数据

editForm.form('submit', {
    //form提交的路径
    url:url,
    //提交之前你要做什么事件
    onSubmit: function(param){
        // 加一些自己的参数过去
        //1.拿到编辑明细的所有数据
        var rows = dg.datagrid("getRows");
        //2.遍历所有数据，拼接成咱们需要的格式的参数
        //items[0].product.id=1
        for(let i=0;i<rows.length;i++){
            var json = rows[i];
            param[`items[${i}].product.id`] = json.product.id;
            param[`items[${i}].num`] = json.num;
            param[`items[${i}].price`] = json.price;
            param[`items[${i}].descs`] = json.descs;
        }

        // return false to prevent submit; 返回false阻止提交
        return $(this).form('validate');
    },

后台保存与计算

private JsonResult saveOrUpdate(Purchasebill purchasebill){
    JsonResult jsonResult = new JsonResult();
    try {
        //拿到采购订单的所有明细
        List<Purchasebillitem> items = purchasebill.getItems();
        //System.out.println("一方获取多方:"+items);
        //①.准备总金额与总数量
        BigDecimal totalamount = new BigDecimal(0);
        BigDecimal totalnum = new BigDecimal(0);
        for (Purchasebillitem item : items) {
            //System.out.println("多方拿一方:"+item.getBill());
            //设置明细对应的订单
            item.setBill(purchasebill);
            //计算每一个明细的小计
            item.setAmount(item.getNum().multiply(item.getPrice()));
            //②.总金额与总数量进行累加
            totalamount = totalamount.add(item.getAmount());
            totalnum = totalnum.add(item.getNum());
        }
        //③.把值设置到订单中去
        purchasebill.setTotalamount(totalamount);
        purchasebill.setTotalnum(totalnum);
        purchasebillService.save(purchasebill);
    } catch (Exception e) {
        e.printStackTrace();
        //代表出错
        jsonResult.setSuccess(false);
        jsonResult.setMsg(e.getMessage());
    }
    return jsonResult;
}

解决n-to-n错误

@ModelAttribute("editPurchasebill")
public Purchasebill beforeEdit(Long id,String cmd){
    //修改的时候才查询(只要有id会就进行一次查询,这是不对的)
    if(id!=null && "update".equals(cmd)) {
        Purchasebill purchasebill = purchasebillService.findOne(id);
        //把要传过来的关联对象都清空，就可以解决n-to-n的问题
        purchasebill.setSupplier(null);
        purchasebill.setBuyer(null);
        purchasebill.getItems().clear();
        return purchasebill;
    }
    return null;
}

---

报表

报表=多样的格式+动态的数据
表格：详细数据
图表：直观

表格数据展示

报表中准备一个类

public class PurchasebillitemVO {
    //编号
    private Long id;
    //供应商
    private String supplierName;
    //采购员
    private String buyerName;
    //产品
    private String productName;
    //产品类型
    private String productTypeName;
    //日期
    private Date vdate;
    //数量
    private BigDecimal num;
    //单价
    private BigDecimal price;
    //小计
    private BigDecimal amount;
    //状态
    private Integer status;

    //分组字段
    private String groupField;

    public PurchasebillitemVO() {}
    //创建时设置值
    public PurchasebillitemVO(Purchasebillitem item,Integer groupBy) {
        this.id = item.getId();
        this.supplierName = item.getBill().getSupplier().getName();
        this.buyerName = item.getBill().getBuyer().getUsername();
        this.productName = item.getProduct().getName();
        this.productTypeName = 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();

        //确定分组字段
        switch (groupBy){
            case 1:{
                this.groupField = this.buyerName;
                break;
            }
            case 2:{
                this.groupField = (DateUtils.toCalendar(this.vdate).get(Calendar.MONTH)+1) +"月份";
                break;
            }
            default:
                this.groupField =this.supplierName;
        }
    }
    ...
}

前端展示，引入JS支持

<script src="/easyui/plugins/datagrid-groupview.js"></script>

高级查询字段

<div id="tb" style="padding:5px;height:auto">
    <!-- 这里就是一个日历 -->
    <div id="cc" class="easyui-calendar"></div>
    <form id="searchForm" method="post" action="">
        采购时间: <input name="beginDate" class="easyui-datebox" data-options="sharedCalendar:'#cc'"
                     style="width:120px">
        - <input name="endDate" class="easyui-datebox" data-options="sharedCalendar:'#cc'" style="width:120px">
        状态:<select class="easyui-combobox" 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" panelHeight="auto" name="groupBy" style="width: 100px">
            <option value="0">供应商</option>
            <option value="1">采购员</option>
            <option value="2">月份</option>
        </select>
        <a href="javascript:;" data-method="search" class="easyui-linkbutton" iconCls="icon-search">查询</a>
        <a href="javascript:;" data-method="show3d" class="easyui-linkbutton" iconCls="icon-search">3D</a>
        <a href="javascript:;" data-method="show2d" class="easyui-linkbutton" iconCls="icon-search">2D</a>
    </form>
</div>
<table id="itemsGrid"></table>

js代码修改

//创建分组grid
itemsGrid.datagrid({
    //title:'',
    // width:500,
    // height:250,
    fit:true,
    rownumbers:true,
    remoteSort:false,
    nowrap:false,
    fitColumns:true,
    //url:'datagrid_data.json',
    toolbar:"#tb",
    url:'/purchasebillitem/findItems',
    columns:[[
        {field:'id',title:'编号',width:100,sortable:true},
        {field:'supplierName',title:'供应商',width:100,sortable:true},
        {field:'buyerName',title:'采购员',width:100,sortable:true},
        {field:'productName',title:'产品',width:100,sortable:true},
        {field:'productTypeName',title:'产品类型',width:100,sortable:true},
        {field:'vdate',title:'采购日期',width:100,sortable:true},
        {field:'num',title:'数量',width:100,sortable:true},
        {field:'price',title:'价格',width:100,sortable:true},
        {field:'amount',title:'小计',width:100,sortable:true},
        {field:'status',title:'状态',width:100,sortable:true,formatter:statusFormat}
    ]],
    groupField:'groupField', //指示要被分组的字段
    view: groupview,
    groupFormatter:function(value, rows){ //组格式化
        let num = 0,amount = 0;
        for(let r of rows){
            num += r.num;
            amount += r.amount;
        }
        return value + ' - ' + rows.length + '条数据 <span style="color: green;">共'+num+'条数据</span> <span style="color: red;">总金额:'+amount +"</span>";
    }
});

图表展示

两种技术：
1.flash（actionscript）不安全，容易崩溃
2.H5(画布)
两个框架:
1.:highchart(收费,支持IE)
2.echart(百度,开源免费)

前端使用

引入相应的js

<!-- 引入highcharts的js支持 -->
<script src="/js/plugin/highcharts/code/highcharts.js"></script>
<script src="/js/plugin/highcharts/code/highcharts-3d.js"></script>
<script src="/js/plugin/highcharts/code/modules/exporting.js"></script>
<script src="/js/plugin/highcharts/code/modules/export-data.js"></script>

准备div进行展示

<!-- 一个弹出框,里面要装一个form表单 -->
<div id="chartDialog" class="easyui-dialog" title="图表展示"
     data-options="height:400,width:600,closed:true,modal:true">
    <div id="container" style="height: 320px"></div>
</div>

点击按钮弹出图表

show3d(){
    chartDialog.dialog("center").dialog("open");
    //拿到表单中的所有数据
    var params = searchForm.serializeObject();
    //通过Ajax到后台拿到相应的值[{name:xxx,y:10},]
    $.post("/purchasebillitem/findCharts",params,function (data) {
        //注意这里有一个异步问题
        var chart = Highcharts.chart('container', {
            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: data
            }]
        });
    })
}

后台最后实现

//接收参数的变量
private List params = new ArrayList();
//准备一个方法，返回JPQL的查询条件
public String createWhereJPQL(){
    StringBuilder jpql = new StringBuilder();
    //开始时间
    if(beginDate!=null){
        jpql.append(" and o.bill.vdate >= ? ");
        params.add(beginDate);
    }
    //结束时间
    if(endDate!=null){
        jpql.append(" and o.bill.vdate < ? ");
        params.add(DateUtils.addDays(endDate, 1));
    }
    //状态
    if(status!=null){
        jpql.append(" and o.bill.status = ? ");
        params.add(status);
    }
    //第一个条件必需是where开头
    return jpql.toString().replaceFirst("and", "where");
}
//创建分组的JPQL
public String createGroupBy(){
    String groupStr = "o.bill.supplier.name";
    switch (groupBy){
        case 1:{
            groupStr="o.bill.buyer.username";
            break;
        }
        case 2:{
            groupStr="MONTH(o.bill.vdate)";
            break;
        }
    }
    return groupStr;
}

PurchasebillitemServiceImpl

/**
 * 查询图表需要的数据
 */
@Override
public List<Map> findCharts(PurchasebillitemQuery query){
    List<Map> mapList = new ArrayList<>();

    //拿到条件JPQL
    String whereJPQL = query.createWhereJPQL();
    //拿到条件对应的参数
    List params = query.getParams();
    //准备分组的条件
    String groupBy = query.createGroupBy();
    //根据供应商分组拿到的数据
    String jpql = "select "+groupBy+",sum(o.amount) from Purchasebillitem o "+whereJPQL+" group by "+groupBy;
    List<Object[]> list = findByJpql(jpql,params.toArray());
    //需要把一个List<Object[]> -> List<Map>
    for (Object[] objects : list) {
        Map map = new HashMap();
        map.put("name", objects[0]);
        map.put("y", objects[1]);
        mapList.add(map);
    }
    return mapList;
}