32 lines
2.2 KiB
SQL
32 lines
2.2 KiB
SQL
-- 提成结算：核对记录 + 顺延结转（上期「确定业绩」时写入，下期统计自动并入订单池）
|
|
CREATE TABLE IF NOT EXISTS `zyt_commission_settlement_confirm` (
|
|
`id` int unsigned NOT NULL AUTO_INCREMENT,
|
|
`settlement_month` char(7) NOT NULL COMMENT '结算月 YYYY-MM',
|
|
`scope_channel_code` varchar(128) NOT NULL DEFAULT '' COMMENT '与查询渠道一致，空为全渠道',
|
|
`scope_dept_ids_key` varchar(255) NOT NULL DEFAULT '*' COMMENT '部门筛选键：* 或排序后的 id 逗号串',
|
|
`status` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0已保存核对 1已确定业绩',
|
|
`reconcile_note` varchar(500) NOT NULL DEFAULT '' COMMENT '核对备注',
|
|
`totals_json` text COMMENT '确定时汇总快照 JSON',
|
|
`confirmed_admin_id` int unsigned NOT NULL DEFAULT 0,
|
|
`confirmed_admin_name` varchar(64) NOT NULL DEFAULT '',
|
|
`confirmed_at` int unsigned NOT NULL DEFAULT 0 COMMENT '确定业绩时间戳',
|
|
`create_time` int unsigned NOT NULL DEFAULT 0,
|
|
`update_time` int unsigned NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_scope` (`settlement_month`,`scope_channel_code`(64),`scope_dept_ids_key`(128))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='提成结算核对/确定';
|
|
|
|
CREATE TABLE IF NOT EXISTS `zyt_commission_settlement_carry` (
|
|
`id` int unsigned NOT NULL AUTO_INCREMENT,
|
|
`prescription_order_id` int unsigned NOT NULL COMMENT '业务订单 tcm_prescription_order.id',
|
|
`target_settlement_month` char(7) NOT NULL COMMENT '计入哪个月结算池',
|
|
`source_settlement_month` char(7) NOT NULL COMMENT '从哪个月确定顺延出来',
|
|
`scope_channel_code` varchar(128) NOT NULL DEFAULT '',
|
|
`scope_dept_ids_key` varchar(255) NOT NULL DEFAULT '*',
|
|
`create_time` int unsigned NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_carry` (`prescription_order_id`,`target_settlement_month`,`scope_channel_code`(64),`scope_dept_ids_key`(64)),
|
|
KEY `idx_target_scope` (`target_settlement_month`,`scope_channel_code`(32),`scope_dept_ids_key`(64)),
|
|
KEY `idx_source_scope` (`source_settlement_month`,`scope_channel_code`(32),`scope_dept_ids_key`(64))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='提成顺延结转';
|