item-management-1phase/sql/brokerage_about.sql

alter table item_stage add COLUMN weight decimal(16,2) DEFAULT NULL comment '提成权重';
alter table item_stage add COLUMN coefficient decimal(16,2) DEFAULT NULL comment '逾期提成系数';
alter table item_stage add COLUMN overdue bit(1) DEFAULT 0 not null comment '是否逾期';
alter table item add COLUMN upload_date date DEFAULT null comment '合同上传日期';
alter table global_config CHANGE column `name` `key` varchar(128);

CREATE TABLE `brokerage_base_amount` (
                                         `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                         `user_id` bigint(20) NOT NULL COMMENT '用户id',
                                         `base_amount` decimal(16,2) DEFAULT NULL COMMENT '自定义提成基数金额',
                                         `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                         `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                         `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                         PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='自定义提成基数';

CREATE TABLE `brokerage_marketer_ratio` (
                                            `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                            `item_cate` bigint(20) NOT NULL COMMENT '项目类型字典id',
                                            `item_source` bigint(20) NOT NULL COMMENT '项目来源字典id',
                                            `ratio` double(16,4) DEFAULT NULL COMMENT '提成比例',
  `remark` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '备注',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目提成比例（市场人员）';

CREATE TABLE `brokerage_post_ratio` (
                                        `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                        `post_id` bigint(20) NOT NULL COMMENT '岗位id',
                                        `brokerage_ratio` decimal(16,2) DEFAULT NULL COMMENT '岗位抽成率',
                                        `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                        `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                        `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                        PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='领导岗位抽成率';

CREATE TABLE `business_reply` (
                                  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                  `biz_type` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '业务类型',
                                  `biz_table_id` bigint(20) NOT NULL COMMENT '业务表id',
                                  `parent_id` bigint(20) DEFAULT NULL COMMENT '父级id',
                                  `content` text COLLATE utf8_unicode_ci COMMENT '内容',
                                  `replier_id` bigint(20) NOT NULL COMMENT '回复者id',
                                  `receiver_id` int(11) DEFAULT NULL COMMENT '接受者id',
                                  `state` bit(1) NOT NULL DEFAULT b'0' COMMENT '状态（已读，未读）',
                                  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                  `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='业务回复表';

CREATE TABLE `item_brokerage_detail` (
                                         `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                         `general_id` bigint(20) NOT NULL COMMENT '项目提成总表id',
                                         `user_id` bigint(20) NOT NULL COMMENT '用户id',
                                         `predict_amount` decimal(16,2) DEFAULT NULL COMMENT '预计提成金额',
                                         `ahead_amount` decimal(16,2) DEFAULT NULL COMMENT '预提金额',
                                         `actual_amount` decimal(16,2) DEFAULT NULL COMMENT '实际提成金额',
                                         `advance_amount` decimal(16,2) DEFAULT NULL COMMENT '已结算金额',
                                         `brokerage_rule` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '提成规则',
                                         `brokerage_mode` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '提成方式',
                                         `brokerage_stage_id` bigint(20) DEFAULT NULL COMMENT '项目提成阶段id(非市场人员用)',
                                         `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                         `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                         `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                         PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=481 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目提成明细';

CREATE TABLE `item_brokerage_general` (
                                          `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                          `item_id` bigint(20) NOT NULL COMMENT '项目id',
                                          `marketer_brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '客户经理提成金额',
                                          `marketer_advance_brokerage_able` bit(1) NOT NULL DEFAULT b'0' COMMENT '客户经理预提',
                                          `marketer_advance_brokerage_done` bit(1) NOT NULL DEFAULT b'0' COMMENT '客户经理已预提',
                                          `participant_predict_brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '参与人预计提成总金额',
                                          `participant_actual_brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '参与人实际提成总金额',
                                          `participant_advance_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '参与人预提中',
                                          `brokerage_predict_amount` decimal(16,2) DEFAULT NULL COMMENT '预计提成总金额',
                                          `brokerage_actual_amount` decimal(16,2) DEFAULT NULL COMMENT '已提成总金额',
                                          `brokerage_rate` decimal(16,2) DEFAULT NULL COMMENT '提成率',
                                          `participant_status` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '参与人提成状态',
                                          `marketer_status` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '客户经理提成状态',
                                          `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                          `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                          `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                          PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目提成总表';

CREATE TABLE `item_brokerage_sequence` (
                                           `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                           `user_id` bigint(20) NOT NULL COMMENT '用户id',
                                           `brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '提成总金额',
                                           `embody` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '提成体现',
                                           `remark` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '备注',
                                           `cate` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '结算类型（项目结算，默认预提）',
                                           `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                           `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（业务）',
                                           `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                           PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目提成流水';

CREATE TABLE `item_brokerage_sequence_detail` (
                                                  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                                  `brokerage_detail_id` bigint(20) NOT NULL COMMENT '项目提成明细表id',
                                                  `sequence_id` bigint(20) NOT NULL COMMENT '项目提成流水表id',
                                                  `user_id` bigint(20) NOT NULL COMMENT '用户id',
                                                  `brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '提成金额',
                                                  `brokerage_cate` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '提成类型（预提，结算）',
                                                  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                                  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                  `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                                  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='提成流水明细表';

CREATE TABLE `item_evaluate` (
                                 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                 `item_id` bigint(20) NOT NULL COMMENT '项目id',
                                 `weight` decimal(16,2) DEFAULT NULL COMMENT '项目整体提成权重',
                                 `comments` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '项目评价',
                                 `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                 `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                 `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目完结评价表';

CREATE TABLE `item_stage_user` (
                                   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                   `user_id` bigint(20) NOT NULL COMMENT '用户id',
                                   `item_id` bigint(20) NOT NULL COMMENT '项目id',
                                   `stage_id` bigint(20) NOT NULL COMMENT '项目阶段id',
                                   `weight` decimal(16,2) DEFAULT NULL COMMENT '提成比例',
                                   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=394 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目人员阶段参与表';