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

alter table item_stage add COLUMN weight double(16,2) DEFAULT NULL comment '提成权重';
alter table item_stage add COLUMN coefficient double(16,2) DEFAULT NULL comment '逾期提成系数';
alter table item_stage add COLUMN overdue bit(1) DEFAULT 0 comment '是否逾期';
alter table item add COLUMN upload_date date DEFAULT null 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 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',
`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 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 '预计提成金额',
`atcual_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 '提成方式',
`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 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'1' COMMENT '参与人预提中',
`brokerage_predict_amount` decimal(16,2) DEFAULT NULL COMMENT '预计提成总金额',
`brokerage_actual_amount` decimal(16,2) DEFAULT NULL COMMENT '已提成总金额',
`brokerage_rate` decimal(3,2) DEFAULT NULL COMMENT '提成率',
`status` enum('未开始','预提中','冻结','已结算') COLLATE utf8_unicode_ci NOT NULL DEFAULT '未开始' 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 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 '备注',
`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 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` double(16,2) DEFAULT NULL COMMENT '提成比例',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 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` double(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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目完结评价表';