brokerage_about.sql 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135
  1. alter table item_stage add COLUMN weight decimal(16,2) DEFAULT NULL comment '提成权重';
  2. alter table item_stage add COLUMN coefficient decimal(16,2) DEFAULT NULL comment '逾期提成系数';
  3. alter table item_stage add COLUMN overdue bit(1) DEFAULT 0 not null comment '是否逾期';
  4. alter table item add COLUMN upload_date date DEFAULT null comment '合同上传日期';
  5. alter table global_config CHANGE column `name` `key` varchar(128);
  6. CREATE TABLE `brokerage_base_amount` (
  7. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  8. `user_id` bigint(20) NOT NULL COMMENT '用户id',
  9. `base_amount` decimal(16,2) DEFAULT NULL COMMENT '自定义提成基数金额',
  10. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  11. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  12. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  13. PRIMARY KEY (`id`)
  14. ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='自定义提成基数';
  15. CREATE TABLE `brokerage_marketer_ratio` (
  16. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  17. `item_cate` bigint(20) NOT NULL COMMENT '项目类型字典id',
  18. `item_source` bigint(20) NOT NULL COMMENT '项目来源字典id',
  19. `ratio` double(16,4) DEFAULT NULL COMMENT '提成比例',
  20. `remark` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '备注',
  21. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  22. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  23. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  24. PRIMARY KEY (`id`)
  25. ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目提成比例(市场人员)';
  26. CREATE TABLE `brokerage_post_ratio` (
  27. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  28. `post_id` bigint(20) NOT NULL COMMENT '岗位id',
  29. `brokerage_ratio` decimal(16,2) DEFAULT NULL COMMENT '岗位抽成率',
  30. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  31. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  32. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  33. PRIMARY KEY (`id`)
  34. ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='领导岗位抽成率';
  35. CREATE TABLE `business_reply` (
  36. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  37. `biz_type` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '业务类型',
  38. `biz_table_id` bigint(20) NOT NULL COMMENT '业务表id',
  39. `parent_id` bigint(20) DEFAULT NULL COMMENT '父级id',
  40. `content` text COLLATE utf8_unicode_ci COMMENT '内容',
  41. `replier_id` bigint(20) NOT NULL COMMENT '回复者id',
  42. `receiver_id` int(11) DEFAULT NULL COMMENT '接受者id',
  43. `state` bit(1) NOT NULL DEFAULT b'0' COMMENT '状态(已读,未读)',
  44. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  45. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  46. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  47. PRIMARY KEY (`id`)
  48. ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='业务回复表';
  49. CREATE TABLE `item_brokerage_detail` (
  50. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  51. `general_id` bigint(20) NOT NULL COMMENT '项目提成总表id',
  52. `user_id` bigint(20) NOT NULL COMMENT '用户id',
  53. `predict_amount` decimal(16,2) DEFAULT NULL COMMENT '预计提成金额',
  54. `ahead_amount` decimal(16,2) DEFAULT NULL COMMENT '预提金额',
  55. `actual_amount` decimal(16,2) DEFAULT NULL COMMENT '实际提成金额',
  56. `advance_amount` decimal(16,2) DEFAULT NULL COMMENT '已结算金额',
  57. `brokerage_rule` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '提成规则',
  58. `brokerage_mode` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '提成方式',
  59. `brokerage_stage_id` bigint(20) DEFAULT NULL COMMENT '项目提成阶段id(非市场人员用)',
  60. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  61. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  62. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  63. PRIMARY KEY (`id`)
  64. ) ENGINE=InnoDB AUTO_INCREMENT=481 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目提成明细';
  65. CREATE TABLE `item_brokerage_general` (
  66. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  67. `item_id` bigint(20) NOT NULL COMMENT '项目id',
  68. `marketer_brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '客户经理提成金额',
  69. `marketer_advance_brokerage_able` bit(1) NOT NULL DEFAULT b'0' COMMENT '客户经理预提',
  70. `marketer_advance_brokerage_done` bit(1) NOT NULL DEFAULT b'0' COMMENT '客户经理已预提',
  71. `participant_predict_brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '参与人预计提成总金额',
  72. `participant_actual_brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '参与人实际提成总金额',
  73. `participant_advance_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '参与人预提中',
  74. `brokerage_predict_amount` decimal(16,2) DEFAULT NULL COMMENT '预计提成总金额',
  75. `brokerage_actual_amount` decimal(16,2) DEFAULT NULL COMMENT '已提成总金额',
  76. `brokerage_rate` decimal(16,2) DEFAULT NULL COMMENT '提成率',
  77. `participant_status` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '参与人提成状态',
  78. `marketer_status` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '客户经理提成状态',
  79. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  80. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  81. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  82. PRIMARY KEY (`id`)
  83. ) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目提成总表';
  84. CREATE TABLE `item_brokerage_sequence` (
  85. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  86. `user_id` bigint(20) NOT NULL COMMENT '用户id',
  87. `brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '提成总金额',
  88. `embody` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '提成体现',
  89. `remark` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '备注',
  90. `cate` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '结算类型(项目结算,默认预提)',
  91. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  92. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间(业务)',
  93. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  94. PRIMARY KEY (`id`)
  95. ) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目提成流水';
  96. CREATE TABLE `item_brokerage_sequence_detail` (
  97. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  98. `brokerage_detail_id` bigint(20) NOT NULL COMMENT '项目提成明细表id',
  99. `sequence_id` bigint(20) NOT NULL COMMENT '项目提成流水表id',
  100. `user_id` bigint(20) NOT NULL COMMENT '用户id',
  101. `brokerage_amount` decimal(16,2) DEFAULT NULL COMMENT '提成金额',
  102. `brokerage_cate` varchar(32) COLLATE utf8_unicode_ci NOT NULL COMMENT '提成类型(预提,结算)',
  103. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  104. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  105. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  106. PRIMARY KEY (`id`)
  107. ) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='提成流水明细表';
  108. CREATE TABLE `item_evaluate` (
  109. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  110. `item_id` bigint(20) NOT NULL COMMENT '项目id',
  111. `weight` decimal(16,2) DEFAULT NULL COMMENT '项目整体提成权重',
  112. `comments` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '项目评价',
  113. `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
  114. `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  115. `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
  116. PRIMARY KEY (`id`)
  117. ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目完结评价表';
  118. CREATE TABLE `item_stage_user` (
  119. `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  120. `user_id` bigint(20) NOT NULL COMMENT '用户id',
  121. `item_id` bigint(20) NOT NULL COMMENT '项目id',
  122. `stage_id` bigint(20) NOT NULL COMMENT '项目阶段id',
  123. `weight` decimal(16,2) DEFAULT NULL COMMENT '提成比例',
  124. PRIMARY KEY (`id`)
  125. ) ENGINE=InnoDB AUTO_INCREMENT=394 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='项目人员阶段参与表';