多层级汇总报表生成

现在需求是，根据数据库的明细数据，组装生成多层级的汇总统计报表。例如数据库基础数据如下:

 

 根据给定基础数据导出如下层级汇总表:

 

   

考虑大数据量数据组装的效率，可以组装成多叉树结构实现。 

结合设计模式中组合模式，实现如下:

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 类功能描述：多层级汇总报表生成
 *
 * @author BarryWang create at 19-9-20 下午10:24
 * @version 1.0.0
 */
public class CompositeNode {
    /**
     * 报表层级
     */
    private int level = 0;
    private List detailColList;
    private List<CompositeNode> groupColList;
    private List groupList;
    private CompositeNode rootGroup;
    /**
     * 当前层级的关键字
     */
    private String groupKey;
    private String[] detailLine;
    private Map existingDetailValueMap;
    private Map sutotalLineMap;

    public CompositeNode(List detailColList, List groupColList) {
        this.level = -1;
        this.groupKey = "Root";
        this.rootGroup = this;
        this.detailColList = detailColList;
        this.groupColList = groupColList;
    }

    public CompositeNode(int level, String groupKey, CompositeNode rootGroup, String[] detailLine) {
        if (level == 0) {
            this.rootGroup = this;
        } else {
            this.rootGroup = rootGroup;
        }

        this.level = level;
        this.groupKey = groupKey;
        this.detailColList = rootGroup.detailColList;
        this.groupColList = rootGroup.groupColList;
        this.detailLine = detailLine;
    }

    /**
     * 根据查询出的明细记录构建树状结构
     * @param s
     */
    public void buildUp(String[][] s) {
        for (int i = 0; i < s.length; i++) {
            String[] ss = s[i];
            this.setDetailLine(ss);
            this.addOneRow();
        }
    }

    public void setDetailLine(String[] detailLine) {
        this.detailLine = detailLine;
        if (groupList != null) {
            for (int i = 0; i < this.groupList.size(); i++) {
                ((CompositeNode) this.groupList.get(i)).setDetailLine(detailLine);
            }
        }
    }


    public void addOneRow() {
        if (level == this.groupColList.size()) {
            addDeatilValue(detailLine);
        }

        if (level < this.groupColList.size()) {
            String newGroupKey = detailLine[level + 1];
            CompositeNode nextGroup = this.getNextGroup(newGroupKey);
            nextGroup.addOneRow();
        }

        this.addSubtotal();
    }


    public CompositeNode getNextGroup(String groupKey) {
        if (this.groupList == null) {
            this.groupList = new ArrayList<CompositeNode>();
        }

        for (int i = 0; i < this.groupList.size(); i++) {
            if (((CompositeNode) this.groupList.get(i)).groupKey.equalsIgnoreCase(groupKey)) {
                return (CompositeNode) this.groupList.get(i);
            }
        }

        CompositeNode tempGroup = new CompositeNode(level + 1, groupKey, this.rootGroup, this.detailLine);
        groupList.add(tempGroup);
        return tempGroup;
    }


    public void addDeatilValue(String[] detailLine) {
        if (existingDetailValueMap == null) {
            existingDetailValueMap = new HashMap();
        }

        Integer detailValue = (Integer) existingDetailValueMap.get(detailLine[2]);
        if (detailValue == null) {
            existingDetailValueMap.put(detailLine[2], Integer.parseInt(detailLine[3]));
        } else {
            existingDetailValueMap.put(detailLine[2], (detailValue + Integer.parseInt(detailLine[3])));
        }
    }


    public void addSubtotal() {
        if (sutotalLineMap == null) {
            sutotalLineMap = new HashMap();
        }

        Integer subVal = (Integer) sutotalLineMap.get(1);
        if (subVal == null) {
            sutotalLineMap.put(1, Integer.parseInt(detailLine[3]));
        } else {
            subVal += Integer.parseInt(detailLine[3]);
            sutotalLineMap.put(1, subVal);
        }
    }


    public String toString() {
        String returnStr = "";
        for (int i = 0; i <= level; i++) {
            returnStr += "   ";
        }

        returnStr += this.groupKey;
        returnStr += ((level == this.groupColList.size()) ? "" : "(total:" + this.sutotalLineMap.get(1) + ")");
        returnStr += ((this.existingDetailValueMap == null) ? "" : "(detail:" + this.existingDetailValueMap + ")");

        if (groupList != null) {
            for (int i = 0; i < this.groupList.size(); i++) {
                returnStr += "\n" + ((CompositeNode) this.groupList.get(i)).toString();
            }
        }

        return returnStr;
    }


    public static void main(String[] args) {
        String[][] sqlOneResult = {{"A", "B", "C", "10"},
                {"A1", "B1", "C1", "20"},
                {"A2", "B2", "C2", "30"},
                {"A", "B1", "C2", "40"},
                {"A", "B", "C1", "50"}};

        String[][] sqlTwoResult = {{"A", "B", "C", "10"},
                {"A1", "B1", "C1", "20"},
                {"A2", "B2", "C2", "30"},
                {"A", "B1", "C2", "40"},
                {"A", "B", "C1", "50"}};

        String[][] sqlThreeResult = {{"A", "B", "C", "10"},
                {"A1", "B1", "C1", "20"},
                {"A2", "B2", "C2", "30"},
                {"A", "B1", "C2", "40"},
                {"A", "B", "C1", "50"}};

        List<String[][]> sqlResultList = new ArrayList<String[][]>();
        sqlResultList.add(sqlOneResult);
        sqlResultList.add(sqlTwoResult);
// sqlResultList.add(sqlThreeResult);
        List detailColList = new ArrayList();
        List groupColList = new ArrayList();
        groupColList.add(0);
        groupColList.add(1);
        CompositeNode rootGroup = new CompositeNode(detailColList, groupColList);
        for (int i = 0; i < sqlResultList.size(); i++) {
            rootGroup.buildUp(sqlResultList.get(i));
        }
        System.out.print(rootGroup.toString());
    }
}

 

输出结果如下：

Root(total:300)
   A(total:200)
      B(total:120)
         C(detail:{C=20})
         C1(detail:{C1=100})
      B1(total:80)
         C2(detail:{C2=80})
   A1(total:40)
      B1(total:40)
         C1(detail:{C1=40})
   A2(total:60)
      B2(total:60)
         C2(detail:{C2=60})