90 lines
4.6 KiB
SQL
90 lines
4.6 KiB
SQL
/*
|
|
财务结算接口手动迁移 SQL。
|
|
|
|
执行范围：
|
|
1. 新增统一结算主表 finance_settlement。
|
|
2. 新增统一结算明细表 finance_settlement_detail。
|
|
3. 如果表已经由接口自动创建，补齐 sale_mode 字段。
|
|
|
|
注意：
|
|
- 不修改旧 provider_settle_data 流程。
|
|
- 分销商结算记账依赖既有账户：
|
|
account(accounting_orgid=上级机构, orgid=分销商机构, subject=分销商存放资金)
|
|
account(accounting_orgid=上级机构, orgid=上级机构, subject=资金账号)
|
|
*/
|
|
|
|
CREATE TABLE IF NOT EXISTS `finance_settlement` (
|
|
`id` varchar(32) NOT NULL COMMENT 'id',
|
|
`settlement_no` varchar(64) DEFAULT NULL COMMENT '结算单号',
|
|
`accounting_orgid` varchar(32) DEFAULT NULL COMMENT '账本机构id',
|
|
`counterparty_type` varchar(16) DEFAULT NULL COMMENT '对手方类型 supplier/reseller',
|
|
`counterparty_orgid` varchar(32) DEFAULT NULL COMMENT '对手方机构id',
|
|
`counterparty_name` varchar(255) DEFAULT NULL COMMENT '对手方名称',
|
|
`period_type` varchar(16) DEFAULT NULL COMMENT '账期类型 day/month',
|
|
`period_start` date DEFAULT NULL COMMENT '账期开始日期',
|
|
`period_end` date DEFAULT NULL COMMENT '账期结束日期',
|
|
`sales_amount` double(18,8) DEFAULT 0.00000000 COMMENT '销售金额',
|
|
`settlement_amount` double(18,8) DEFAULT 0.00000000 COMMENT '结算金额',
|
|
`platform_income_amount` double(18,8) DEFAULT 0.00000000 COMMENT '平台收入',
|
|
`bill_count` int(11) DEFAULT 0 COMMENT '账单数量',
|
|
`status` varchar(16) DEFAULT 'draft' COMMENT 'draft/approving/approved/rejected/settled/failed/cancelled',
|
|
`approval_id` varchar(64) DEFAULT NULL COMMENT '审批id',
|
|
`approval_status` varchar(16) DEFAULT NULL COMMENT '审批状态',
|
|
`failure_reason` varchar(1000) DEFAULT NULL COMMENT '失败原因',
|
|
`settled_at` datetime DEFAULT NULL COMMENT '结算记账时间',
|
|
`created_by` varchar(32) DEFAULT NULL COMMENT '创建人',
|
|
`del_flg` varchar(1) DEFAULT '0' COMMENT '删除标志',
|
|
`create_at` datetime DEFAULT current_timestamp() COMMENT '创建时间',
|
|
`update_at` datetime DEFAULT current_timestamp() COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `finance_settlement_un` (`accounting_orgid`, `counterparty_type`, `counterparty_orgid`, `period_start`, `period_end`, `del_flg`),
|
|
KEY `finance_settlement_idx1` (`accounting_orgid`, `status`),
|
|
KEY `finance_settlement_idx2` (`counterparty_type`, `counterparty_orgid`),
|
|
KEY `finance_settlement_idx3` (`period_start`, `period_end`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务结算单主表';
|
|
|
|
CREATE TABLE IF NOT EXISTS `finance_settlement_detail` (
|
|
`id` varchar(32) NOT NULL COMMENT 'id',
|
|
`settlement_id` varchar(32) DEFAULT NULL COMMENT '结算单id',
|
|
`bill_id` varchar(32) DEFAULT NULL COMMENT '账单id',
|
|
`order_id` varchar(32) DEFAULT NULL COMMENT '订单id',
|
|
`bill_date` date DEFAULT NULL COMMENT '账单日期',
|
|
`customerid` varchar(32) DEFAULT NULL COMMENT '客户id',
|
|
`providerid` varchar(32) DEFAULT NULL COMMENT '供应商机构id',
|
|
`productid` varchar(32) DEFAULT NULL COMMENT '产品id',
|
|
`business_op` varchar(64) DEFAULT NULL COMMENT '业务操作',
|
|
`sale_mode` varchar(1) DEFAULT NULL COMMENT '销售模式 0折扣/1代付费/2底价',
|
|
`sales_amount` double(18,8) DEFAULT 0.00000000 COMMENT '销售金额',
|
|
`settlement_amount` double(18,8) DEFAULT 0.00000000 COMMENT '结算金额',
|
|
`platform_income_amount` double(18,8) DEFAULT 0.00000000 COMMENT '平台收入',
|
|
`amount_source` varchar(32) DEFAULT 'bill_detail' COMMENT '金额来源',
|
|
`del_flg` varchar(1) DEFAULT '0' COMMENT '删除标志',
|
|
`create_at` datetime DEFAULT current_timestamp() COMMENT '创建时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `finance_settlement_detail_idx1` (`settlement_id`),
|
|
KEY `finance_settlement_detail_idx2` (`bill_id`),
|
|
KEY `finance_settlement_detail_idx3` (`providerid`, `productid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务结算单明细快照';
|
|
|
|
ALTER TABLE `finance_settlement_detail`
|
|
ADD COLUMN IF NOT EXISTS `sale_mode` varchar(1) DEFAULT NULL COMMENT '销售模式 0折扣/1代付费/2底价' AFTER `business_op`;
|
|
|
|
-- 执行分销商结算前，可用以下 SQL 检查目标账户是否存在：
|
|
-- SELECT a.*
|
|
-- FROM account a
|
|
-- JOIN subject s ON a.subjectid=s.id
|
|
-- WHERE a.accounting_orgid='<上级机构orgid>'
|
|
-- AND a.orgid='<分销商orgid>'
|
|
-- AND s.name='分销商存放资金'
|
|
-- AND a.del_flg='0'
|
|
-- AND s.del_flg='0';
|
|
--
|
|
-- SELECT a.*
|
|
-- FROM account a
|
|
-- JOIN subject s ON a.subjectid=s.id
|
|
-- WHERE a.accounting_orgid='<上级机构orgid>'
|
|
-- AND a.orgid='<上级机构orgid>'
|
|
-- AND s.name='资金账号'
|
|
-- AND a.del_flg='0'
|
|
-- AND s.del_flg='0';
|