问题背景

F 科技公司新财务系统采用 saas 架构，报表需要从底层科目开始汇总，逐级汇总到顶级科目。Saas 架构要支持多租户，而每个企业租户都可以自由定义科目，因此科目层级和汇总次数是动态的。

报表样式如下：

本报表以时间段为选择条件，统计结果为：科目编号、科目名称、期初余额借方、期初余额贷方、本期发生借方、本期发生贷方、期末余额借方、期末余额贷方。

如何表达这种动态算法是本案例的难点，开发人员尝试过 SQL 语句、存储过程、JAVA 代码等方法，都无法方便地实现该算法，开发周期较长。

问题分析

数据结构

凭证表 fi_voucher

 

凭证明细 fi_voucher_b

凭证明细的外键为 voucherid、accsubject，分别对应凭证表的 id 和科目表的 id

 

科目表 bd_accsubject

科目表是关键，主要字段有：id（科目 id）、code（科目编码）、displayname（科目名称）、direction（稽核方向）、leaf（是否底层节点）。上图是三层科目的示例数据，底层节点在第三层或第二层，待汇总的数据只和底层科目有关联。

解决方案

我们使用开源集算器 SPL 来实现该算法，最终报表开发效率提升了 6 倍。

算法实现思路

1.       根据时间段参数计算出本期和期初的日期

2.       分别统计出本期借贷数据和期初借贷数据

3.       将本期借贷和期初借贷关联到底层科目上

4.       从底层科目开始逐级汇总，直到顶级

5.       根据本期借贷和期初借贷计算期末借贷

 

SPL 脚本：

 

代码解释

A1-B3：根据输入的时间段参数（begin1 和 end1）计算出本期和期初的起止日期。

当 begin1=2017-04，end1=2017-06 时，本期起（本期第一天）=2017-04-01，本期末（本期最后一天）=2017-06-30，期初起（本年第一天）=2017-01-01，期初末（本期上个月最后一天）=2017-03-31。

说明：SPL 有专门的函数计算月初、月底、上个月、年初，比 SQL 直观比 JAVA 精炼。

 

A3-A6：查询数据库，按科目统计出期初借贷和本期借贷。

其中期初借贷要根据稽核方向（direction 字段）计算，当 direction=Debit 时，期初余额借方 = 借 - 贷，无期初余额贷方；当 direction=Credit 时，期初余额贷方 = 贷 - 借，无期初余额借方。

期初借贷如下：

本期借贷如下：

说明：简单算法尽量用 SQL，稽核方向也可以写在 SQL 里，这里之所以拆成两句用 SPL 实现，是为了更方便地比较数据变化。

 

A7-A9：将期初借贷和本期借贷与末级科目关联。

之所以要关联，是因为并非每个科目都有统计结果，而报表需要呈现完整的科目。关联结果如下：

说明：当遇到多步骤的算法时，SQL 不可分步不可调试的弱点就显露出来了，这种情况下可将复杂 SQL 拆成几个简单步骤，以便调试观察每一步的计算结果，同时降低出错概率。

 

A10-B13：上一步的 data 只有末级科目，这一步要进行逐级汇总，直到顶级。

对于三层科目来讲，层级序列 =[3,2]，即动态汇总两次：第三级汇总到第二级，第二级再汇总到第一级。每次汇总时步骤如下：

l  从 data 中查出当前层级对应的数据（B11）

l  按上级分组汇总。比如当前是第三层，科目编码为 6 位，只需按前 4 位分组汇总，即可获得第二层的数据。如果当前为第二层，科目编码 4 位，则需要按前 2 位分组汇总。

l  将上级数据追加到 data 中。

l  继续循环，直到顶级。

下图框里的记录是追加后的科目：

 

A14：对 data 按科目排序，形成层次清晰的树状结构。

 

A15-A16：根据期初和本期计算新字段：期末余额借方、期末余额贷方，最后加入科目名称。

期末余额借方 = 期初余额借方 + 本期发生借方 - 本期发生贷方

期末余额贷方 = 期初余额贷方 + 本期发生贷方 - 本期发生借方

最终计算结果如下：

 

前端报表接收 SPL 的计算结果进行数据呈现（报表的开发步骤省略）。

方案效果

这里对比一下使用 SPL 前后的差异。

指标	JAVA	SPL（SPL）	提升
工作量	6 人天	1 人天	6倍
维护性	较差	好	易管理、热切换

总结

在成熟报表工具的支持下，报表格式开发的工作量已经不大，工作量已经从呈现阶段转到数据准备阶段了（本例仅给出报表数据准备算法，省略了报表的制作过程），这部分开发量占比远远大于报表布局那些事。

SPL 的出现很好地解决了报表数据准备难的问题。SPL 提供了丰富的计算类库可以满足各类复杂计算的需要，过程化脚本编辑使得算法实现也更简单，从而进一步提升报表开发效率。