会员沉淀资产计算程序开发文档

# 1.   文档说明

本文档作为乐的文化管理系统的财务模块，对会员客户的沉淀资产进行计算程序的开发文档，对项目中使用的技术标准和技术开发方案进行描述。

# 2.   项目目标

由于企业在长期的运营过程中，会产生出沉淀资产，这一部分的沉淀资产，它可能是短期的，它也可能是长期的，所以需要对这部分资产进行定期的计算及查看，以方便财务人员更精准的业务处理。

通过以上目标的实现，最终以定期对沉淀数据进行计算的方式，以达到整个数据完整性、统一性、安全性、可追溯性机制，从而满足工作需要，提高工作效率。

# 3.数据表设计

| 表 名                | log_precipitate_assets |             |         |      |                           |
| -------------------- | ---------------------- | ----------- | ------- | ---- | ------------------------- |
| 中文描述             | 会员沉淀资产表         |             |         |      |                           |
| 建立日期             | 2022/12/25             | 修改日期    |         |      |                           |
| 列 名                | 中文描述               | 数据类型    | 空/非空 | 主键 | 备注                      |
| lpa_id               | 主键id                 | varchar(13) | 非空    | 是   |                           |
| years_month          | 计算年月               | varchar(20) | 非空    |      | 计算资产价值时“yyyy-MM” |
| store_id             | 门店ID                 | varchar(13) | 非空    |      |                           |
| m_id                 | 会员ID                 | varchar(13) | 非空    |      |                           |
| card_id              | 会员卡ID               | varchar(13) | 非空    |      |                           |
| assets_id            | 资产ID                 | varchar(13) | 非空    |      |                           |
| log_table            | 日志表                 | varchar(50) | 非空    |      |                           |
| recode_id            | 记录ID                 | varchar(13) | 非空    |      |                           |
| recode_last_datetime | 记录时间               | datetime    | 非空    |      |                           |
| precipitate_income   | 资产价值               | decimal     | 非空    |      |                           |
| activation_rate      | 激活率                 | decimal     | 非空    |      |                           |
| retail_format        | 业态                   | decimal     | 非空    |      |                           |
| add_date             | 添加时间               | datetime    | 非空    |      |                           |
| 补充说明             | 新增表，保存沉淀结果   |             |         |      |                           |

| 表 名       | Member_info_card_assets |             |         |        |        |
| ----------- | ----------------------- | ----------- | ------- | ------ | ------ |
| 中文描述    | 会员卡资产              |             |         |        |        |
| 建立日期    |                         | 修改日期    |         |        |        |
| 列 名       | 中文描述                | 数据类型    | 空/非空 | 主  键 | 备  注 |
| id          | 主键id                  | varchar(13) | 非空    | 是     |        |
| Assets_type | 资产类型                | int         | 非空    |        |        |
| 补充说明    | 查询使用                |             |         |        |        |

| 表 名       | base_store   |             |         |        |        |
| ----------- | ------------ | ----------- | ------- | ------ | ------ |
| 中文描述    | 门店基础信息 |             |         |        |        |
| 建立日期    |              | 修改日期    |         |        |        |
| 列 名       | 中文描述     | 数据类型    | 空/非空 | 主  键 | 备  注 |
| id          | 主键id       | varchar(13) | 非空    | 是     |        |
| Store_class | 业态         | int         | 非空    |        |        |
| Is_login    | 是否启用     | bool        | 非空    |        |        |
| 补充说明    | 查询使用     |             |         |        |        |

| 表 名     | log_chg_rec |             |         |        |        |
| --------- | ----------- | ----------- | ------- | ------ | ------ |
| 中文描述  | 资产消耗表  |             |         |        |        |
| 建立日期  |             | 修改日期    |         |        |        |
| 列 名     | 中文描述    | 数据类型    | 空/非空 | 主  键 | 备  注 |
| id        | 主键id      | varchar(13) | 非空    | 是     |        |
| Store_id  | 是否启用    | varchar(13) | 非空    |        |        |
| M_id      | 会员ID      | varchar(13) | 非空    |        |        |
| Card_id   | 会员卡ID    | varchar(13) | 非空    |        |        |
| Assets_id | 资产ID      | varchar(13) | 非空    |        |        |
| New_price | 新值价值    | decimal     | 非空    |        |        |
| Log_table | 记录表名    | varchar(50) | 非空    |        |        |
| Add_date  | 新增时间    | datetime    | 非空    |        |        |
| 补充说明  | 查询使用    |             |         |        |        |

| 表 名       | member_coupon |             |         |        |        |
| ----------- | ------------- | ----------- | ------- | ------ | ------ |
| 中文描述    | 核销券表      |             |         |        |        |
| 建立日期    |               | 修改日期    |         |        |        |
| 列  名      | 中文描述      | 数据类型    | 空/非空 | 主  键 | 备  注 |
| id          | 主键id        | varchar(13) | 非空    | 是     |        |
| cupon_type  | 核销券类型    | varchar(2)  | 非空    |        |        |
| Is_used     | 是否使用      | bool        | 非空    |        |        |
| Cupon_price | 价值          | decimal     | 非空    |        |        |
| Add_date    | 新增时间      | datetime    | 非空    |        |        |
| 补充说明    | 查询使用      |             |         |        |        |

| 表 名      | promote_discount_coupon_provide |             |         |        |        |
| ---------- | ------------------------------- | ----------- | ------- | ------ | ------ |
| 中文描述   | 优惠券表                        |             |         |        |        |
| 建立日期   |                                 | 修改日期    |         |        |        |
| 列 名      | 中文描述                        | 数据类型    | 空/非空 | 主  键 | 备  注 |
| id         | 主键id                          | varchar(13) | 非空    | 是     |        |
| Is_used    | 是否使用                        | bool        | 非空    |        |        |
| rest_value | 价值                            | decimal     | 非空    |        |        |
| Add_date   | 新增时间                        | datetime    | 非空    |        |        |
| 补充说明   | 查询使用                        |             |         |        |        |

# 4.数据字典/全局变量

## 4.1 数据字典

| **字典名称** | **类  型** | **键** | **值** |
| ------------------ | ---------------- | ------------ | ------------ |
|                    |                  |              |              |

## 4.2 全局变量

| 变量名称 | 访问路径 |值| 说  明 | 
| ------------------ | ---------------- | ------------ | ------------ |
|                    |                  |              |              |

# 5.接口设计

## 5.1 说明：

调用：

| **调用URL** | |
| ----------------- | -------------------------------------------- |

入参：

| **参 数 名** | **类  型** | **说  明** | **备  注** |
| ------------------ | ---------------- | ---------------- | ---------------- |
|                    |                  |                  |                  |

出参：

| **参 数 名** | **类  型** | **说  明** |
| ------------------ | ---------------- | ---------------- |
|                    |                  |                  |

# 6.实施内容

## 6.1概况描述

在现有财务系统中增加“沉淀查询”模块，财务人员可以通过此模块对任何时期（指定年度）的沉淀数据进行查询，导出等操作。

## 6.2 会员沉淀资产计算

### （1）模块名称：会员沉淀资产计算

### （2）模块路径：会员沉淀资产计算

### （3）逻辑描述：

1：启动程序后，预设执行计算沉淀资产的时间范围，默认时间为当前系统时间的前一月，可手动修改（注：按照默认时间即可，计算数据时，是需要依赖计算时间范围的前一月的数据的）；

2：在设置好计算时间范围之后，直接点击“开始执行”按钮，程序会创建线程，后台自动计算，因为数据量比较大，因此计算时间会比较长，需要耐心等待，此过程中在程序窗口下方会有执行进度提示；

3：计算过程中，程序会一次性加载所有的“门店信息（门店ID,是否闭店）”，“会员所有资产变更记录表资产ID（会员资产类型为非时间类型）”；

4：获取所有资产数据：select DISTINCT assets_id from log_chg_rec where !(chg_type='0' and log_table='log_sell_detail') and assets_id in(SELECT id from member_info_card_assets where assets_type!=0)

5：分批次（10W条一批）遍历所有资产消耗记录（select lcr.id as recode_id,lcr.store_id,bs.store_class,lcr.m_id,lcr.card_id,lcr.assets_id,lcr.new_price as price,lcr.log_table,STR_TO_DATE(DATE_FORMAT(lcr.add_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d %H:%i:%s') recode_last_datetime from log_chg_rec as lcr LEFT JOIN base_store bs on lcr.store_id = bs.id where lcr.add_date<'"+ **CurrentMonth** + " 00:00:01' and assets_id in(" + **assete_ids** + ") and !(lcr.chg_type='0' and lcr.log_table='log_sell_detail')）, **CurrentMontho：**计算沉淀数据月份；**assete_ids**：资产ID列表，并以assete_ids10万个（大小以运行时电脑配置决定，防止系统资源不足导致程序异常停止）资产ID为一批次进行计算，并保存结果到数据表中；
A:按10万个资产为一批次的资产查询出资产详细信息（排除chg_type字段值为 '0'且log_table字段值为'log_sell_detail'的记录）进行计算；
B:逐条判断每个资产变更记录表资产ID是否在资产表中存在，如果存在且资产类型为“0”时，则跳过此资产的沉淀计算，继续下一条，以此排除时间卡类型资产（时间卡资产不作计算）；
C:获取每个资产变更记录表资产ID是否在资产变更明细记录，按记录时间排序，取出最近数据的门店类型，判断是否设置为空，为空时则跳过此资产的沉淀计算，继续下一条；
D:按资产变更明细表中时间由近到远进行遍历判断，变更时间在业态范围内（儿童14月，电玩18个月），业态时间范围内有记录，则跳过此资产的沉淀计算，继续下一条；如果最后记录时间在业态范围之外，提取最新记录信息；
E:判断变更明细是否有沉淀记录：
有：从沉淀计算结果中查找是否有记录，如果有则提取，没有跳过此资产的沉淀计算，继续下一条；无：则提取出当前已沉淀价值；
F:再从“资产变更明细记录”中判断沉淀价值所属门店：则变更记录时间由近到远，判断门店为未闭店状态；如所有门店均为“闭店”状态，则将沉淀价值归属到最近时间资产变更时的门店；
G:生成会员沉淀记录，保存在内存表中；
H:重复执行A-G步骤，直到数据完全计算后，结束程序执行，并在程序界面提示已完成。

6：核销券处理：通过SQL查询语句，即可对核销券数据沉淀情况进行查询提取；
A:通过如下SQL：SELECT mc.id as assets_id,mc.store_id,mc.m_id,mc.cupon_price as price,14 as store_class,mc.add_date FROM member_coupon as mc left JOIN base_store as bs on mc.store_id=bs.id where mc.cupon_type!=14 and bs.store_class=0 and mc.is_used=false and mc.cupon_price>0 and mc.add_date<date_sub(now(), interval 14 month)  UNION  SELECT mc.id as assets_id,mc.store_id,mc.m_id,mc.cupon_price as price,18 as store_class,mc.add_date FROM member_coupon as mc left JOIN base_store as bs on mc.store_id=bs.id where mc.cupon_type!=14 and bs.store_class=1 and mc.is_used=false and mc.cupon_price>0 and mc.add_date<date_sub(now(), interval 18 month) 即可对沉淀“核销券”数据进行查询提取，cupon_price大于0，且is_used为false，且cupon_type不为14，且儿童在14个月以前，电玩在18个月以前的数据；
B:循环核销券记录，将核销券ID写入沉淀表assets_id，recode_id字段中，添加到沉淀资产结果内存表中；
C:循环处理核销券记录后，将所有存入内存表中一次性提交到数据库中保存；
D：重复执行A-C步骤，直到数据完全计算后，结束程序执行，并在程序界面提示已完成。

7：优惠券处理：通过SQL查询语句，即可对优惠券数据沉淀情况进行查询提取；
A:通过如下SQL：SELECT mc.id as assets_id,mc.store_id,mc.m_id,mc.rest_value as price,14 as store_class,mc.add_date FROM promote_discount_coupon_provide as mc left JOIN base_store as bs on mc.store_id=bs.id where bs.store_class=0 and mc.is_used=false and mc.rest_value>0 and mc.add_date<date_sub(now(),
interval 14 month)  UNION  SELECT mc.id as assets_id,mc.store_id,mc.m_id,mc.rest_value as price,18 as store_class,mc.add_date FROM promote_discount_coupon_provide as mc left JOIN base_store as bs on mc.store_id=bs.id where bs.store_class=1 and mc.is_used=false and mc.rest_value>0 and mc.add_date<date_sub(now(), interval 18 month) 即可对沉淀“优惠券”数据进行查询提取，rest_value大于0，且is_used为false，且儿童在14个月以前，电玩在18个月以前的数据；
B:循环优惠券记录，将优惠券ID写入沉淀表assets_id，recode_id字段中，添加到沉淀资产结果内存表中；
C:循环处理优惠券记录后，将所有存入内存表中一次性提交到数据库中保存；
D：重复执行A-C步骤，直到数据完全计算后，结束程序执行，并在程序界面提示已完成。

8：到此，三种（点币/次数，核销券，优惠券）数据类型的沉淀提取，一次（一个月）计算已经完成。

9：另外再将上月清卡沉淀数据原样复制一份到本月。
以下为获取清卡数据SQL脚本：
select a.store_id,a.m_id,a.card_id,'' as assets_id,a.id as recode_id, a.submit_date as recode_last_datetime,a.to_money as precipitate_income,if (b.store_class = '0','-14',if (b.store_class = '1','-18',if (b.store_class = '4','-18',''))) as retail_format from log_clear_card a left join base_store b on (a.store_id = b.id) where a.is_returned in ('0', '1', '2') and(a.submit_date is not null or a.submit_date <> '') and (a.finance_operator_id is null or a.finance_operator_id = '') and a.submit_date <= '2023-11-30 23:59:59' and a.store_id not in ('004CSO74SXKYU', '004D5UAISEMM3', '004NBTPCUVBAD', '014M8XII9NA8U') and b.store_class in(0,1)
注：
- SQL脚本中的“store_id not in ('004CSO74SXKYU', '004D5UAISEMM3', '004NBTPCUVBAD', '014M8XII9NA8U')”表示为非测试门店
- SQL脚本中的“store_class in(0,1)”表示仅限于“儿童”和“电玩”业态门店

10:添加自动执行任务，自动在每月1日01:00开始执行计算任务。