superdesk-operation-center-dubbo/sql/lmz/V1.0.1.20230412.sql

ALTER TABLE `sys_enterprise` ADD COLUMN `biz_platform_code` varchar(100) DEFAULT NULL COMMENT '业务平台-引用enum OpCenterBizPlatformEnum';
ALTER TABLE `sys_enterprise` ADD COLUMN `biz_platform_name` varchar(600) DEFAULT NULL COMMENT '业务平台名';
ALTER TABLE `sys_enterprise` ADD COLUMN `biz_operator_id` varchar(64) DEFAULT NULL COMMENT '业务方运营商id';



ALTER TABLE `sys_organization` ADD COLUMN `biz_platform_code` varchar(100) DEFAULT NULL COMMENT '业务平台-引用enum OpCenterBizPlatformEnum';
ALTER TABLE `sys_organization` ADD COLUMN `biz_operator_id` varchar(64) DEFAULT NULL COMMENT '业务方运营商id';
ALTER TABLE `sys_organization` ADD COLUMN `biz_platform_name` varchar(600) DEFAULT NULL COMMENT '业务平台名';


ALTER TABLE `unified_supplier` ADD COLUMN `biz_platform_code` varchar(100) DEFAULT NULL COMMENT '业务平台-引用enum OpCenterBizPlatformEnum';
ALTER TABLE `unified_supplier` ADD COLUMN `biz_platform_name` varchar(600) DEFAULT NULL COMMENT '业务平台名';

CREATE TABLE `unified_business` (
 `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
 `user_name` VARCHAR ( 30 ) NOT NULL COMMENT '姓名',
 `user_number` VARCHAR ( 60 ) DEFAULT NULL COMMENT '员工工号',
 `company_name` VARCHAR ( 110 ) NOT NULL COMMENT '所属公司',
 `dept_name` VARCHAR ( 110 ) NOT NULL COMMENT '所属部门',
 `area_name` VARCHAR ( 110 ) DEFAULT NULL COMMENT '所属区域',
 `post_name` VARCHAR ( 110 ) DEFAULT NULL COMMENT '岗位名称',
 `phone` VARCHAR ( 110 ) NOT NULL COMMENT '电话',
 `email` VARCHAR ( 160 ) NOT NULL COMMENT '邮箱',
 `job_status` TINYINT ( 4 ) NOT NULL DEFAULT '1' COMMENT '在职状态:1-在职;0-离职;',
 `remark` VARCHAR ( 210 ) DEFAULT NULL COMMENT '备注',
 `create_time` datetime DEFAULT NULL COMMENT '创建时间',
 `create_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '创建人',
 `update_time` datetime DEFAULT NULL COMMENT '更新时间',
 `update_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '更新人',
 `deleted` TINYINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '软删除 0-未删除 1-已删除',
 `enabled` TINYINT ( 4 ) NOT NULL DEFAULT '1' COMMENT '状态:1-启用;0-停用;',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '运营中心-业务人员表';



CREATE TABLE `unified_settlement_programme` (
`id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`customer_id` BIGINT ( 20 ) NOT NULL COMMENT '客户id',
`type` TINYINT ( 4 ) NOT NULL DEFAULT '1' COMMENT '类型:1-运营商；',
`programme_name` VARCHAR ( 110 ) NOT NULL COMMENT '方案名称',
`programme_no` VARCHAR ( 50 ) NOT NULL COMMENT '方案编号',
`settlement_scope` TINYINT ( 4 ) DEFAULT NULL COMMENT '结算范围',
`settlement_rule` TINYINT ( 4 ) DEFAULT NULL COMMENT '结算规则',
`bill_name` VARCHAR ( 110 ) DEFAULT NULL COMMENT '账单名称',
`export_field` VARCHAR ( 1000 ) DEFAULT '{"org_name":1,"virt_name":1,"supplier_name":0,"third_no":0,"agreement_cost":0,"integral":0}' COMMENT '导出字段',
`supname_display_rule` TINYINT ( 4 ) DEFAULT NULL COMMENT '供应商名称展示规则',
`custom_supplier_name` VARCHAR ( 110 ) DEFAULT NULL COMMENT '自定义供应商名称',
`is_default` TINYINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '是否默认:1-默认;0-不是默认；',
`remark` VARCHAR ( 210 ) DEFAULT NULL COMMENT '备注',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`create_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '创建人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`update_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '更新人',
`deleted` TINYINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '软删除 0-未删除 1-已删除',
`enabled` TINYINT ( 4 ) NOT NULL DEFAULT '1' COMMENT '状态:1-启用;0-停用;',
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '结算方案表';


CREATE TABLE `unified_settlement_programme_range_rule` (
 `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
 `type` TINYINT ( 4 ) NOT NULL COMMENT '类型:1-部分订单不参与结算；2-部分按原供应商名称导出；',
 `programme_id` BIGINT ( 20 ) NOT NULL COMMENT '结算方案id',
 `display` TINYINT ( 4 ) DEFAULT NULL COMMENT '排序',
 `way` TINYINT ( 4 ) DEFAULT NULL COMMENT '方式',
 `not_joining_range` VARCHAR ( 1000 ) DEFAULT NULL COMMENT '不参与范围(显示)',
 `not_joining_range_json` text DEFAULT NULL COMMENT '不参与范围json',
 `create_time` datetime DEFAULT NULL COMMENT '创建时间',
 `create_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '创建人',
 `update_time` datetime DEFAULT NULL COMMENT '更新时间',
 `update_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '更新人',
 `deleted` TINYINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '软删除 0-未删除 1-已删除',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '结算方案-不参与范围表';


CREATE TABLE `unified_settlement_programme_amount_rule` (
 `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
 `programme_id` BIGINT ( 20 ) NOT NULL COMMENT '结算方案id',
 `display` TINYINT ( 4 ) DEFAULT NULL COMMENT '排序',
 `operator_ids` VARCHAR ( 200 ) DEFAULT NULL COMMENT '适用运营商ids',
 `operator_names` VARCHAR ( 1000 ) DEFAULT NULL COMMENT '适用运营商',
 `org_ids` VARCHAR ( 200 ) DEFAULT NULL COMMENT '适用项目ids',
 `org_names` VARCHAR ( 1000 ) DEFAULT NULL COMMENT '适用项目',
 `order_type_ids` VARCHAR ( 200 ) DEFAULT NULL COMMENT '适用订单类型ids',
 `order_type_names` VARCHAR ( 1000 ) DEFAULT NULL COMMENT '适用订单类型',
 `amount_rule` TINYINT ( 4 ) DEFAULT NULL COMMENT '订单结算金额规则',
 `amount_rate` DECIMAL ( 13, 2 ) DEFAULT NULL COMMENT '结算规则比例0.01~100 %',
 `create_time` datetime DEFAULT NULL COMMENT '创建时间',
 `create_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '创建人',
 `update_time` datetime DEFAULT NULL COMMENT '更新时间',
 `update_user_id` BIGINT ( 20 ) DEFAULT NULL COMMENT '更新人',
 `deleted` TINYINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '软删除 0-未删除 1-已删除',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '结算方案-订单结算金额表';






ALTER TABLE `unified_settlement_programme_range_rule` ADD INDEX index_range_rule_type ( `type`, `programme_id`);
ALTER TABLE `unified_settlement_programme_amount_rule` ADD INDEX index_amount_rule (`programme_id`);