1.8 库存报表

基本

• 报表没有实体类，没有DML，是联合其他表的分组查询
• 新建订货／销售报表查询对象，用静态代码块封装分组信息

以OrderChartsQueryObject为例：
//分组查询的条件
    private String groupByType="iu.name";
    
public static Map<String,String> groupByTypeMap;

static{
        groupByTypeMap = new LinkedHashMap<>();
        groupByTypeMap.put("iu.name","订货人员");
        groupByTypeMap.put("p.name","货品名称");
        groupByTypeMap.put("s.name","供应商");
        groupByTypeMap.put("p.brandName","品牌");
        groupByTypeMap.put("DATE_FORMAT(bill.vdate,'%Y-%m')","订货日期(月)");
        groupByTypeMap.put("DATE_FORMAT(bill.vdate,'%Y-%m-%d')","订货日期(日)");
    }

后台统一处理程序

Controller

• 查询各自的报表，注意把把各自的groupByTypeMap加入模型中，方便页面直接取值-

ChartsMapper

• Map封装查询结果集，如：List<Map<String, Object>> selectOrderCharts(QueryObject qo);

订货报表

• 分组查询使用统计函数，日期分组要用范围
• 分组传值用${groupByType}，拼接字符串

<select id="selectOrderCharts" resultType="java.util.Map">
  SELECT
  ${groupByType} groupByType,
  sum(item.number) totalNumber,
  sum(item.amount) totalAmount
  FROM orderbillitem item
  JOIN orderbill bill
  ON item.bill_id = bill.id
  JOIN employee iu ON bill.inputUser_id = iu.id
  JOIN product p ON item.product_id = p.id
  JOIN supplier s ON bill.supplier_id = s.id
  <include refid="orderCharts_sql"/>
  GROUP BY ${groupByType}
</select>

• 未审核的订单不应该计入，condition中加where bill.status = 1，替换掉标签

  <sql id="orderCharts_sql">
    where bill.status = 1
    <if test="beginDate!=null">
      and bill.vdate >= #{beginDate}
    </if>
    <if test="endDate!=null">
      and bill.vdate &lt;= #{endDate}
    </if>
    <if test="supplierId>0">
      and s.id=#{supplierId}
    </if>
    <if test="brandId>0">
      and p.brand_id=#{brandId}
    </if>
  </sql>

• orderCharts页面

销售报表

出库单审核生成销售账，为统计做准备

• 代码生成器自动生成saleAccount，只有insert方法，注意改属性
• StockOutcomeBillServiceImpl，审核方法中增加生成销售账操作，核心仍然是明细

//生成销售帐
List<StockOutcomeBillItem> items = old.getItems();
for (StockOutcomeBillItem item : items) {
    //查询出库存信息
    ProductStock productStock = productStockMapper.selectByProductIdAndDepotId(
            item.getProduct().getId(), old.getDepot().getId());
    SaleAccount saleAccount = new SaleAccount();
    saleAccount.setProduct(item.getProduct());
    saleAccount.setClient(old.getClient());
    saleAccount.setNumber(item.getNumber());
    //使用库存价格作为成本价
    saleAccount.setCostPrice(productStock.getPrice());
    saleAccount.setCostAmount(saleAccount.getNumber()
    .multiply(saleAccount.getCostPrice()));
    //使用明细中的商品的销售价作为当前的销售价
    saleAccount.setSalePrice(item.getSalePrice());
    saleAccount.setSaleAmount(saleAccount.getNumber()
    .multiply(saleAccount.getSalePrice()));
    saleAccount.setSaleMan(old.getInputUser());
    saleAccount.setVdate(old.getVdate());
    saleAccountMapper.insert(saleAccount);
}

• 查询出库存信息、使用库存价格作为成本价、使用明细中的商品的销售价作为当前的销售价

依赖productStockMapper查询、依赖saleAccountMapper设置

• 查询库存信息不需要再判断是否存在
• saleCharts页面

图形报表：ECharts插件

• 引入依赖：

子页面：柱状图/饼状图jsp

• body中为ECharts准备一个Dom:

<div id="main" style="width: 600px;height:400px;"></div>

• script中
  1. 基于准备好的dom，初始化echarts实例

  var myChart = echarts.init(document.getElementById('main'));
  

  2. 柱状图

  option = {
  title : {text: '销售报表', subtext: '${groupByType}', x:'center'},
  tooltip : { trigger: 'axis'},
  legend: { data:['销售总额'], x:'left'},
  toolbox: {show : true,
    feature : {
        mark : {show: true},
        dataView : {show: true, readOnly: false},
        magicType : {show: true, type: ['line', 'bar']},
        restore : {show: true},
        saveAsImage : {show: true}
    }
  },
  calculable : true,
  xAxis : [ { type : 'category', data :${groupByTypes} } ],
  yAxis : [{ type : 'value'}],
  series : [{  name:'销售总额',
      type:'bar',
      data:${totalAmounts},
      markPoint : { data : [
              {type : 'max', name: '最大值'},
              {type : 'min', name: '最小值'}
          ] },
      markLine : {
          data : [ {type : 'average', name: '平均值'} ]
      }
  }]
  };
  

  3. 饼状图

  option = {
  title: { text: '销售报表',  subtext: '${groupByType}',  x: 'center'},
  tooltip: {  trigger: 'item',  formatter: "{a} <br/>{b} : {c} ({d}%)"},
  legend: { orient: 'vertical',  x: 'left',  data:${groupByTypes}},
  toolbox: {  show: true,  feature: {
        mark: {show: true},
        dataView: {show: true, readOnly: false},
        magicType: { show: true, type: ['pie', 'funnel'],
            option: {    funnel: {  x: '25%', width: '50%', funnelAlign: 'left', max: ${max}   }    }
        },
        restore: {show: true},
        saveAsImage: {show: true}   }
  },
  calculable: true,
  series: [{  name: '销售总额',   type: 'pie',   radius: '55%',  
     center: ['50%', '60%'], data:${datas}  }]
  };
  

  4. 使用刚指定的配置项和数据显示图表myChart.setOption(option)
• 父页面：saleCharts
  • 绑定按钮点击事件，artDialog.open弹框，serialize()序列化可获取表单所有查询条件

  $(".btn_bar").click(function () {
  //获取到表单中所有的查询条件
  console.log();
  $.dialog.open("/chart/saleChartByBar.do?"+$("#searchForm").serialize(), {
      id: 'bar',
      title: '柱状报表',
      width: 650,
      height: 450,
      lock: true,
      opacity: 0.2
  });
  }) ;
  

• 后台controller提供图形报表访问资源地址，并处理提交数据
  1. 在QO的Map中取出当前分组的类型value值

  String groupByType = SaleChartsQueryObject.groupByTypeMap.get(queryObject.getGroupByType());
          model.addAttribute("groupByType", groupByType);
  

  2. 按照分组类型，封装所有查询结果

   List<Map<String, Object>> charts = chartsService.selectSaleCharts(queryObject);
  

  3. 存放所有分组类型

  List<String> groupByTypes = new ArrayList<>();
  

  4. 柱状图存放销售总额

  List<String> totalAmounts = new ArrayList<>();
          for (Map<String, Object> chart : charts) {
              groupByTypes.add(chart.get("groupByType").toString());
              totalAmounts.add(chart.get("totalAmount").toString());
          }
  
  model.addAttribute("groupByTypes", JSON.toJSONString(groupByTypes));
  model.addAttribute("totalAmounts", JSON.toJSONString(totalAmounts));
  

  5. 饼状图存放销售总额

  List<Map<String, Object>> datas = new ArrayList<>();
  //存放最大的销售总额
  BigDecimal max = BigDecimal.ZERO;
  for (Map<String, Object> chart : charts) {
      groupByTypes.add(chart.get("groupByType").toString());
      //将每条报表数据封装到Map集合中
      Map<String, Object> map = new HashMap<>();
      map.put("value", chart.get("totalAmount"));
      map.put("name", chart.get("groupByType"));
  
      datas.add(map);
      BigDecimal totalAmount = new BigDecimal(chart.get("totalAmount").toString());
      if (max.compareTo(totalAmount) < 0) {
          max = totalAmount;
      }
  }
  model.addAttribute("datas", JSON.toJSONString(datas));
  model.addAttribute("max", max);