bc019cadf5
- business_roles/param_definitions/param_change_batches/param_values 四表 + 种子（3 内置岗位/16 参数定义/19 版本条目/初始批次 P20260101-00） - 双轨迁移：Alembic bfbda9c3191d + db/migration/V3 导出 + init-dev.sql 同步（schema_sync 校验通过） - 参数值以 Fraction 可解析字符串存储（4/360 禁 0.011111）；企业户 2‰→3‰ 双种子支持按到账日回溯 Co-Authored-By: Claude Code <noreply@anthropic.com>
154 lines
15 KiB
SQL
154 lines
15 KiB
SQL
-- Running upgrade a45a8087831b -> bfbda9c3191d
|
||
|
||
CREATE TABLE business_roles (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
role_code VARCHAR(32) NOT NULL COMMENT '岗位编码：salesperson营销员 sales_manager营销经理 info_officer信息员，创建后不可修改',
|
||
role_name VARCHAR(64) NOT NULL COMMENT '岗位名称',
|
||
calc_template VARCHAR(32) NOT NULL COMMENT '计提公式模板编码（以代码内模板注册表为准）',
|
||
description VARCHAR(255) COMMENT '岗位说明',
|
||
sort_order INTEGER NOT NULL COMMENT '显示排序' DEFAULT '0',
|
||
status TINYINT NOT NULL COMMENT '状态：1启用 0禁用' DEFAULT '1',
|
||
is_builtin TINYINT NOT NULL COMMENT '是否内置岗位：1内置（禁删禁停） 0自定义' DEFAULT '0',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_business_roles PRIMARY KEY (id),
|
||
CONSTRAINT uk_business_roles_role_code UNIQUE (role_code)
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='业务岗位角色表（与登录角色roles区分，决定计提公式与参数）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE TABLE param_definitions (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
param_code VARCHAR(64) NOT NULL COMMENT '参数编码，如 salesperson.rate.slope',
|
||
param_name VARCHAR(64) NOT NULL COMMENT '参数名称',
|
||
param_group VARCHAR(32) NOT NULL COMMENT '参数分组：业务员个人户/经理分成/企业户/信息员/支付环节/产品阈值/月工资口径',
|
||
value_type VARCHAR(16) NOT NULL COMMENT '值类型：DECIMAL/INT/BOOL/ENUM/TEXT',
|
||
unit VARCHAR(16) COMMENT '展示单位：‰/%/元/天，无单位为空',
|
||
dimension_type VARCHAR(32) NOT NULL COMMENT '维度类型：NONE/PRODUCT_CATEGORY/BUSINESS_ROLE' DEFAULT 'NONE',
|
||
options_text VARCHAR(255) COMMENT '枚举选项（竖线分隔），仅 ENUM 使用',
|
||
default_value VARCHAR(64) COMMENT '系统默认值（Fraction 可解析字符串）',
|
||
value_min VARCHAR(64) COMMENT '取值下限（含），校验用',
|
||
value_max VARCHAR(64) COMMENT '取值上限（含），校验用',
|
||
description VARCHAR(255) COMMENT '口径说明（配置页展示）',
|
||
sort_order INTEGER NOT NULL COMMENT '显示排序' DEFAULT '0',
|
||
status TINYINT NOT NULL COMMENT '状态：1启用 0停用' DEFAULT '1',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_param_definitions PRIMARY KEY (id),
|
||
CONSTRAINT uk_param_definitions_param_code UNIQUE (param_code)
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='计提参数定义表' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE TABLE param_change_batches (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
version_no VARCHAR(32) NOT NULL COMMENT '版本号，如 P20261012-01',
|
||
effective_date DATE NOT NULL COMMENT '生效日期（业务日期口径）',
|
||
change_type TINYINT NOT NULL COMMENT '生效方式：1立即生效 2指定日期生效',
|
||
remark VARCHAR(255) COMMENT '变更说明',
|
||
operator_id BIGINT UNSIGNED COMMENT '操作人ID（账号删除后置空）',
|
||
operator_name VARCHAR(64) NOT NULL COMMENT '操作人用户名（冗余固化）',
|
||
item_count INTEGER NOT NULL COMMENT '变更参数项数' DEFAULT '0',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_param_change_batches PRIMARY KEY (id),
|
||
CONSTRAINT fk_param_change_batches_operator_id FOREIGN KEY(operator_id) REFERENCES users (id) ON DELETE SET NULL,
|
||
CONSTRAINT uk_param_change_batches_version_no UNIQUE (version_no)
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='参数变更批次表' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE TABLE param_values (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
param_id BIGINT UNSIGNED NOT NULL COMMENT '参数定义ID（param_definitions.id）',
|
||
dimension_value VARCHAR(32) NOT NULL COMMENT '维度取值：NONE维度为空串；产品类别/岗位编码为具体值' DEFAULT '',
|
||
effective_date DATE NOT NULL COMMENT '生效日期（按业务日期时点化取值）',
|
||
value_text VARCHAR(64) NOT NULL COMMENT '参数值（Fraction 可解析字符串/枚举码/开关）',
|
||
old_value_text VARCHAR(64) COMMENT '变更前值（留痕）',
|
||
batch_id BIGINT UNSIGNED NOT NULL COMMENT '变更批次ID（param_change_batches.id）',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_param_values PRIMARY KEY (id),
|
||
CONSTRAINT fk_param_values_batch_id FOREIGN KEY(batch_id) REFERENCES param_change_batches (id) ON DELETE RESTRICT,
|
||
CONSTRAINT fk_param_values_param_id FOREIGN KEY(param_id) REFERENCES param_definitions (id) ON DELETE RESTRICT
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='参数版本条目表（append-only，按生效日期时点化取值）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_param_values_batch_id ON param_values (batch_id);
|
||
|
||
CREATE INDEX idx_param_values_effective_date ON param_values (effective_date);
|
||
|
||
CREATE INDEX idx_param_values_param_id_dimension_value_effective_date ON param_values (param_id, dimension_value, effective_date);
|
||
|
||
INSERT INTO business_roles (id, role_code, role_name, calc_template, description, sort_order, is_builtin) VALUES (1, 'salesperson', '营销员', 'TPL_SALESPERSON_DECAY', '业务员个人户：回款天数线性递减（10‰ − 天数×4/360‰，361 天起固定 4‰）', 1, 1);
|
||
|
||
INSERT INTO business_roles (id, role_code, role_name, calc_template, description, sort_order, is_builtin) VALUES (2, 'sales_manager', '营销经理', 'TPL_MANAGER_SHARE', '经理绩效：∑本团队业务员绩效×12.5% + ∑全公司业务员绩效×5%', 2, 1);
|
||
|
||
INSERT INTO business_roles (id, role_code, role_name, calc_template, description, sort_order, is_builtin) VALUES (3, 'info_officer', '信息员', 'TPL_INFO_SALES_FIXED', '信息员绩效：本人销售额×1‰', 3, 1);
|
||
|
||
INSERT INTO param_change_batches (id, version_no, effective_date, change_type, remark, operator_name, item_count) VALUES (1, 'P20260101-00', '2026-01-01', 1, '初始版本（系统初始化种子，口径依据 2026-10-12 业务裁决）', 'system', 19);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (1, 'salesperson.rate.start', '业务员起始计提比例', '业务员个人户', 'DECIMAL', '‰', 'BUSINESS_ROLE', NULL, '10', '0', NULL, '线性递减段起始值：比例 = 起始 − 回款天数 × 斜率（≤分界天数）', 1);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (2, 'salesperson.rate.slope', '业务员日递减斜率', '业务员个人户', 'DECIMAL', '‰/天', 'BUSINESS_ROLE', NULL, '4/360', '0', NULL, '精确分数 4/360‰/天（=1/90），禁止按 0.011111 截断存储', 2);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (3, 'salesperson.rate.floor', '业务员计提下限', '业务员个人户', 'DECIMAL', '‰', 'BUSINESS_ROLE', NULL, '4', '0', NULL, '回款天数超过分界天数后固定取此比例（≥361 天 = 4‰）', 3);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (4, 'salesperson.days.boundary', '线性递减分界天数', '业务员个人户', 'INT', '天', 'BUSINESS_ROLE', NULL, '360', '0', NULL, '≤分界天数按公式线性递减（360 天=6‰）；超过则取下限', 4);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (5, 'manager.share.team', '经理本团队分成比例', '经理分成', 'DECIMAL', '%', 'BUSINESS_ROLE', NULL, '12.5', '0', '100', '∑本人带领业务员绩效 × 此比例（限本团队）', 5);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (6, 'manager.share.company', '经理全公司分成比例', '经理分成', 'DECIMAL', '%', 'BUSINESS_ROLE', NULL, '5', '0', '100', '∑全部业务员绩效 × 此比例（全公司）', 6);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (7, 'enterprise.rate', '企业户计提比例', '企业户', 'DECIMAL', '‰', 'PRODUCT_CATEGORY', NULL, '3', '0', NULL, '所有企业户适用；2026-08-13 起由 2‰ 提高至 3‰', 7);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (8, 'info.rate', '信息员计提比例', '信息员', 'DECIMAL', '‰', 'BUSINESS_ROLE', NULL, '1', '0', NULL, '信息员绩效 = 本人销售额 × 此比例', 8);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (9, 'prepay.rate', '预付款计提比例', '支付环节', 'DECIMAL', '‰', 'NONE', NULL, '10', '0', NULL, '预付款统一按此比例计提', 9);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (10, 'barter.discount', '以物抵顶折算比例', '支付环节', 'DECIMAL', '%', 'NONE', NULL, '80', '0', '100', '以物抵顶回款绩效按应发绩效的此比例折算', 10);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (11, 'payment.start.ratio', '支付起付比例', '支付环节', 'DECIMAL', '%', 'NONE', NULL, '60', '0', '100', '单项业务回款额达销售额（不含运费）此比例后起付；达标前累计', 11);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (12, 'payment.weight.mode', '支付权重应用方式', '支付环节', 'ENUM', NULL, 'NONE', 'FULL|BALANCE', 'FULL', NULL, NULL, 'FULL=按 R×R 乘当月全额应发绩效；BALANCE=乘余额（语义待 M3 前确认）', 12);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (13, 'product.threshold', '折算毛利润额阈值', '产品阈值', 'DECIMAL', '%', 'PRODUCT_CATEGORY', NULL, '30', '0', '100', '折算毛利润额 = 销售额 ×(毛利润率 − 阈值)；市政水利 30%、轨交 20%', 13);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (14, 'salary.basic.mode', '月工资基本工资口径', '月工资口径', 'ENUM', NULL, 'BUSINESS_ROLE', 'INCLUDE|DEDUCT|EXCLUDE', 'INCLUDE', NULL, NULL, 'INCLUDE含基本工资 / DEDUCT扣除基本工资 / EXCLUDE不计', 14);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (15, 'probation.annual.threshold', '试用期年累计结算阈值', '月工资口径', 'DECIMAL', '元', 'NONE', NULL, '18000', '0', NULL, '试用期年累计绩效超过此值部分年底一次性结算', 15);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order) VALUES (16, 'probation.salary.basic', '试用期基本工资标准', '月工资口径', 'DECIMAL', '元', 'NONE', NULL, '3000', '0', NULL, '试用期仅发基本工资、不发放绩效（归属待 M2 复核）', 16);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (1, 1, 'salesperson', '2026-02-17', '10', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (2, 2, 'salesperson', '2026-02-17', '4/360', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (3, 3, 'salesperson', '2026-02-17', '4', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (4, 4, 'salesperson', '2026-02-17', '360', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (5, 5, 'sales_manager', '2026-08-13', '12.5', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (6, 6, 'sales_manager', '2026-08-13', '5', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (7, 7, '', '2026-02-17', '2', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (8, 7, '', '2026-08-13', '3', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (9, 8, 'info_officer', '2026-08-13', '1', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (10, 9, '', '2026-01-01', '10', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (11, 10, '', '2026-01-01', '80', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (12, 11, '', '2026-01-01', '60', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (13, 12, '', '2026-01-01', 'FULL', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (14, 13, 'municipal_water', '2026-02-17', '30', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (15, 13, 'rail_transit', '2026-02-17', '20', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (16, 14, 'salesperson', '2026-01-01', 'INCLUDE', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (17, 14, 'sales_manager', '2026-01-01', 'DEDUCT', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (18, 15, '', '2026-01-01', '18000', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (19, 16, '', '2026-01-01', '3000', 1);
|
||
|
||
UPDATE alembic_version SET version_num='bfbda9c3191d' WHERE alembic_version.version_num = 'a45a8087831b';
|
||
|