如何对报表实现业务数据列转行

关键词：

报表开发、列转行、algo、分组

一、需求背景

        某公司每年6，7月份会进行下半年的物资进行全面采购，财务部门需要需要一份报表数据向领导汇报，报表实现显示今年6、7月份的各个采购组织和物料的采购申请数量和今年总的采购申请数量

二、实现方案

根据采购组织、物料、6，7月份的数据进行分组，统计分组之后每一组的采购数量，根据正常的查询逻辑dataSet.copy().groupBy(new String[] { FIELDS[0], FIELDS[1], "substr(" + ICLZ_APPLYDATE + ",0,7) " + ICLZ_APPLYDATE }) .sum(ICLZ_APPLYQTY).finish();

，第一步，我们可以得到分组数据结构如下

第二步就是要把相同采购组织、物料同一个月份的申请数量放在同一列，格式如下

可以看出两组数据的区别，从第一步数据转换成第二步数据，需要重新创建数据行。在遍历第一步数据行的时候，相同的“采购组织和物料”的6，7月份的采购申请数量的数据，放到第二步新创建数据的一行的两列中。

三、实现过程

1、在开发平台创建报表，并在报表列表中加入显示的字段，注意这里的字段标识必须和查询插件（下面步骤）返回的字段标识一致才能映射上。

2、添加常用过滤条件

3、报表查询插件继承AbstractReportListDataPlugin，并注册在报表列表的查询插件属性下

public class DemoRptListDataPlugin extends AbstractReportListDataPlugin {
       // 源数据字段
       // 采购申请单单据标识
       private static String ICLZ_PURREQ = "kded_reqbill";
       // 采购组织
       private static String ICLZ_APPLYORG = "kded_orgfield";
       //物料字段标识kded_materielfield，查询时，要加上单据体标识，如下
       private static String ICLZ_MATER = "kded_entryentity.kded_materielfield";
       //申请时间
       private static String ICLZ_APPLYDATE = "kded_appdate";
       //申请数量
       private static String ICLZ_APPLYQTY = "kded_entryentity.kded_qtyfield";
       // 报表字段，报表字段要保证字段类型和字段标识和查询的源数据的一致
       private static String[] FIELDS = { "kded_applyorg", "kded_materie", "kded_qty6", "kded_qty7", "kded_qtyall" };
       private static DataType[] DATATYPES = { DataType.LongType, DataType.LongType, DataType.LongType, DataType.LongType,
                     DataType.LongType };
 
}

4、参考代码

（1）常用过滤条件

（1）常用过滤条件
@Override
       public DataSet query(ReportQueryParam param, Object obj) throws Throwable {
              //常用过滤条件
              List<FilterItemInfo> filters = param.getFilter().getFilterItems();
              FastFilter fastFilter = param.getFilter().getFastFilter();
              String applyorg = null;
              String materia = null;
              for (FilterItemInfo filter : filters) {
                     switch (filter.getPropName()) {
                     case "kded_search_applyorg.id":
                            applyorg = (filter == null) ? null
                                          : String.valueOf((filter.getValue()));
                     case "kded_search_materia.id":
                            materia = (filter == null) ? null
                                          : String.valueOf((filter.getValue()));
                            break;
                     default:
                            break;
                     }
              }

（2）查询字段，类似于sql中的select选择字段

StringBuilder selectSettlementFields = new StringBuilder();

       selectSettlementFields.append(ICLZ_APPLYORG).append(" AS ").append(FIELDS[0]).append(", ").append(ICLZ_MATER)

                            .append(" AS ").append(FIELDS[1]).append(", ").append(ICLZ_APPLYDATE).append(",").append(ICLZ_APPLYQTY)

                            .append(", billno");

(3) 查询条件，类似于sql中的where过滤条件

List<QFilter> searchFilterList = new ArrayList<>();
              //过滤掉空数据数据，add起来的过滤条件类似于sql中的 and where a="xx"
              searchFilterList.add(new QFilter(ICLZ_APPLYORG, QCP.is_notnull,""));
              searchFilterList.add(new QFilter(ICLZ_MATER, QCP.is_notnull,""));
              searchFilterList.add(new QFilter(ICLZ_APPLYDATE, QCP.is_notnull,""));
              Date nowTime=new Date();
              Calendar calendar = Calendar.getInstance();
              int i = calendar.get(Calendar.YEAR);//当前年份
              String lastYearEnd=String.valueOf(i-1);
              lastYearEnd=lastYearEnd+"-12-31";
              //报表只显示今年的数据
              searchFilterList.add(new QFilter(ICLZ_APPLYDATE, QCP.large_than,lastYearEnd));
              searchFilterList.add(new QFilter(ICLZ_APPLYQTY, QCP.is_notnull,""));
              if (StringUtils.isNotEmpty(applyorg)) {
                     searchFilterList.add(new QFilter(ICLZ_APPLYORG, QCP.equals, applyorg));
              }
              if (StringUtils.isNotEmpty(materia)) {
                     searchFilterList.add(new QFilter(ICLZ_MATER, QCP.equals, materia));
              }

(4) 查询采购申请单数据

DataSet dataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), ICLZ_PURREQ,
                            selectSettlementFields.toString(), searchFilterList.toArray(new QFilter[] {}), null);

(5)分组计数：根据采购组织、物料，月份进行分组，注意select 字段时As 改变的字段名 ;substr(string,start,length),这里取月份。

DataSet groupDataSet = dataSet.copy()
                            .groupBy(new String[] { FIELDS[0], FIELDS[1], "substr(" + ICLZ_APPLYDATE + ",0,7) " + ICLZ_APPLYDATE })
                            .sum(ICLZ_APPLYQTY).finish();

（6）数据转换，创建空的DataSet，实现源数据在一列的数据转成显示在一行上

Collection<Object[]> coll = new ArrayList<>();
              //创建显示行字段
              RowMeta createRowMeta = RowMetaFactory.createRowMeta(FIELDS, DATATYPES);
              CollectionInput collectionInput = new CollectionInput(createRowMeta, coll);
              DataSet createDataSet = Algo.create(this.getClass().getName()).createDataSet(collectionInput);
              //初始创建一个空的报表数据行
              String preApplyorg = null;
              String preMater = null;
              for (Row row : groupDataSet.copy()) {
                     preApplyorg = row.getString(FIELDS[0]);
                     preMater = row.getString(FIELDS[1]);
                     break;
              }
              Object[] tempData = new Object[FIELDS.length];
              coll.add(tempData);
              String tempapplyorg = null;
              String tempmater = null;
              // Row游标消费完一个结果集之后，不能再消费该结果集
              for (Row row : groupDataSet.copy()) {
                     tempapplyorg = row.getString(FIELDS[0]);
                     tempmater = row.getString(FIELDS[1]);
                     //这里比较巧妙，这里控制一个采购组织和物料的组合6，7月份数据共同使用一个coll,即一行
                     if (!StringUtils.equals(preApplyorg, tempapplyorg) || !StringUtils.equals(preMater, tempmater)) {
                            tempData = new Object[FIELDS.length];
                            coll.add(tempData);
                            preApplyorg = tempapplyorg;
                            preMater = tempmater;
                     }
                     tempData[0] = row.getString(FIELDS[0]);
                     tempData[1] = row.getString(FIELDS[1]);
//                   if (row.getString(ICLZ_APPLYDATE) == null || row.getString(ICLZ_APPLYDATE).isEmpty()) {
//                          continue;
//                   }
                     switch (row.getString(ICLZ_APPLYDATE).substring(5, 7)) {
                     case "06":
                            tempData[2] = row.getLong(ICLZ_APPLYQTY);
                            break;
                     case "07":
                            tempData[3] = row.getLong(ICLZ_APPLYQTY);
                            break;
                     }
              }

（7）获取采购组织、物料对应今年总的采购申请量（即按年分组求和)

DataSet qtyAllDataSet = dataSet.copy()
                            .groupBy(new String[] { FIELDS[0], FIELDS[1], "substr(" + ICLZ_APPLYDATE + ",0,4) " + ICLZ_APPLYDATE })
                            .sum(ICLZ_APPLYQTY).finish();

（8）join合并数据并返回：join合并组和的结果集在select中需要分别new一个String数组选字段，join之后必须select选择字段,这里select选择之后的字段要和报表上的字段标识一致

createDataSet = createDataSet.join(qtyAllDataSet).on(FIELDS[0], FIELDS[0]).on(FIELDS[1], FIELDS[1])
                            .select(new String[] { FIELDS[0], FIELDS[1], FIELDS[2], FIELDS[3] },
                                          new String[] { ICLZ_APPLYQTY + " AS " + FIELDS[4] })
                            .finish();

四、实现效果

（1）过滤效果

（2）全部显示效果

五、开发环境版本

COSMICV4.0.010.0

六、参考资料

【开发平台】指导手册

学习成长中心