brokerage.sql 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241
  1. -- 增加配置表
  2. CREATE TABLE trade_config
  3. (
  4. id BIGINT AUTO_INCREMENT COMMENT '自增主键' PRIMARY KEY,
  5. brokerage_enabled BIT DEFAULT 1 NOT NULL COMMENT '是否启用分佣',
  6. brokerage_enabled_condition TINYINT DEFAULT 1 NOT NULL COMMENT '分佣模式:1-人人分销 2-指定分销',
  7. brokerage_bind_mode TINYINT DEFAULT 1 NOT NULL COMMENT '分销关系绑定模式: 1-没有推广人,2-新用户, 3-扫码覆盖',
  8. brokerage_poster_urls VARCHAR(2000) DEFAULT '' NULL COMMENT '分销海报图地址数组',
  9. brokerage_first_percent INT DEFAULT 0 NOT NULL COMMENT '一级返佣比例',
  10. brokerage_second_percent INT DEFAULT 0 NOT NULL COMMENT '二级返佣比例',
  11. brokerage_withdraw_min_price INT DEFAULT 0 NOT NULL COMMENT '用户提现最低金额',
  12. brokerage_withdraw_fee_percent INT DEFAULT 0 NOT NULL COMMENT '提现手续费百分比',
  13. brokerage_bank_names VARCHAR(200) DEFAULT '' NOT NULL COMMENT '提现银行(字典类型=brokerage_bank_name)',
  14. brokerage_frozen_days INT DEFAULT 7 NOT NULL COMMENT '佣金冻结时间(天)',
  15. brokerage_withdraw_types VARCHAR(32) DEFAULT '1,2,3,4' NOT NULL COMMENT '提现方式:1-钱包;2-银行卡;3-微信;4-支付宝',
  16. creator VARCHAR(64) DEFAULT '' NULL COMMENT '创建者',
  17. create_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
  18. updater VARCHAR(64) DEFAULT '' NULL COMMENT '更新者',
  19. update_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  20. deleted BIT DEFAULT b'0' NOT NULL COMMENT '是否删除',
  21. tenant_id BIGINT DEFAULT 0 NOT NULL COMMENT '租户编号'
  22. ) COMMENT '交易中心配置';
  23. # alter table trade_config
  24. # add brokerage_withdraw_fee_percent int default 0 not null comment '提现手续费百分比' after brokerage_withdraw_min_price;
  25. # alter table trade_brokerage_user
  26. # add level int not null default 1 comment '等级' after frozen_price;
  27. # alter table trade_brokerage_user
  28. # add path varchar(2000) null comment '路径' after level;
  29. -- 增加分销用户扩展表
  30. create table trade_brokerage_user
  31. (
  32. id bigint auto_increment comment '用户编号' primary key,
  33. bind_user_id bigint null comment '推广员编号',
  34. bind_user_time datetime null comment '推广员绑定时间',
  35. brokerage_enabled bit default 1 not null comment '是否成为推广员',
  36. brokerage_time datetime null comment '成为分销员时间',
  37. price int default 0 not null comment '可用佣金',
  38. frozen_price int default 0 not null comment '冻结佣金',
  39. creator varchar(64) default '' null comment '创建者',
  40. create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
  41. updater varchar(64) default '' null comment '更新者',
  42. update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
  43. deleted bit default b'0' not null comment '是否删除',
  44. tenant_id bigint default 0 not null comment '租户编号'
  45. )
  46. comment '分销用户';
  47. create index idx_invite_user_id on trade_brokerage_user (bind_user_id) comment '推广员编号';
  48. create index idx_agent on trade_brokerage_user (brokerage_enabled) comment '是否成为推广员';
  49. create table trade_brokerage_record
  50. (
  51. id int auto_increment comment '编号'
  52. primary key,
  53. user_id bigint not null comment '用户编号',
  54. biz_id varchar(64) default '' not null comment '业务编号',
  55. biz_type tinyint default 0 not null comment '业务类型:1-订单,2-提现',
  56. title varchar(64) default '' not null comment '标题',
  57. price int default 0 not null comment '金额',
  58. total_price int default 0 not null comment '当前总佣金',
  59. description varchar(500) default '' not null comment '说明',
  60. status tinyint default 0 not null comment '状态:0-待结算,1-已结算,2-已取消',
  61. frozen_days int default 0 not null comment '冻结时间(天)',
  62. unfreeze_time datetime null comment '解冻时间',
  63. source_user_level int not null comment '来源用户等级',
  64. source_user_id bigint not null comment '来源用户编号',
  65. creator varchar(64) default '' null comment '创建者',
  66. create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
  67. updater varchar(64) default '' null comment '更新者',
  68. update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
  69. deleted bit default b'0' not null comment '是否删除',
  70. tenant_id bigint default 0 not null comment '租户编号'
  71. )
  72. comment '佣金记录';
  73. create index idx_user_id on trade_brokerage_record (user_id) comment '用户编号';
  74. create index idx_biz on trade_brokerage_record (biz_type, biz_id) comment '业务';
  75. create index idx_status on trade_brokerage_record (status) comment '状态';
  76. create table trade_brokerage_withdraw
  77. (
  78. id bigint auto_increment comment '编号'
  79. primary key,
  80. user_id bigint not null comment '用户编号',
  81. price int default 0 not null comment '提现金额',
  82. fee_price int default 0 not null comment '提现手续费',
  83. total_price int default 0 not null comment '当前总佣金',
  84. type tinyint default 0 not null comment '提现类型:1-钱包;2-银行卡;3-微信;4-支付宝',
  85. name varchar(64) null comment '真实姓名',
  86. account_no varchar(64) null comment '账号',
  87. bank_name varchar(100) null comment '银行名称',
  88. bank_address varchar(200) null comment '开户地址',
  89. account_qr_code_url varchar(512) null comment '收款码',
  90. status tinyint(2) default 0 not null comment '状态:0-审核中,10-审核通过 20-审核不通过;预留:11 - 提现成功;21-提现失败',
  91. audit_reason varchar(128) null comment '审核驳回原因',
  92. audit_time datetime null comment '审核时间',
  93. remark varchar(500) null comment '备注',
  94. creator varchar(64) default '' null comment '创建者',
  95. create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
  96. updater varchar(64) default '' null comment '更新者',
  97. update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
  98. deleted bit default b'0' not null comment '是否删除',
  99. tenant_id bigint default 0 not null comment '租户编号'
  100. )
  101. comment '佣金提现';
  102. create index idx_user_id on trade_brokerage_withdraw (user_id) comment '用户编号';
  103. create index idx_audit_status on trade_brokerage_withdraw (status) comment '状态';
  104. -- 增加字典
  105. insert into system_dict_type(type, name)
  106. values ('brokerage_enabled_condition', '分佣模式');
  107. insert into system_dict_data(dict_type, label, value, sort, remark)
  108. values ('brokerage_enabled_condition', '人人分销', 1, 1, '所有用户都可以分销'),
  109. ('brokerage_enabled_condition', '指定分销', 2, 2, '仅可后台手动设置推广员');
  110. insert into system_dict_type(type, name)
  111. values ('brokerage_bind_mode', '分销关系绑定模式');
  112. insert into system_dict_data(dict_type, label, value, sort, remark)
  113. values ('brokerage_bind_mode', '没有推广人', 1, 1, '只要用户没有推广人,随时都可以绑定推广关系'),
  114. ('brokerage_bind_mode', '新用户', 2, 2, '仅新用户注册时才能绑定推广关系'),
  115. ('brokerage_bind_mode', '扫码覆盖', 3, 3, '如果用户已经有推广人,推广人会被变更');
  116. insert into system_dict_type(type, name)
  117. values ('brokerage_withdraw_type', '佣金提现类型');
  118. insert into system_dict_data(dict_type, label, value, sort)
  119. values ('brokerage_withdraw_type', '钱包', 1, 1),
  120. ('brokerage_withdraw_type', '银行卡', 2, 2),
  121. ('brokerage_withdraw_type', '微信', 3, 3),
  122. ('brokerage_withdraw_type', '支付宝', 4, 4);
  123. insert into system_dict_type(type, name)
  124. values ('brokerage_record_biz_type', '佣金记录业务类型');
  125. insert into system_dict_data(dict_type, label, value, sort)
  126. values ('brokerage_record_biz_type', '订单返佣', 1, 1),
  127. ('brokerage_record_biz_type', '申请提现', 2, 2),
  128. ('brokerage_record_biz_type', '申请提现驳回', 3, 3);
  129. insert into system_dict_type(type, name)
  130. values ('brokerage_record_status', '佣金记录状态');
  131. insert into system_dict_data(dict_type, label, value, sort)
  132. values ('brokerage_record_status', '待结算', 0, 0),
  133. ('brokerage_record_status', '已结算', 1, 1),
  134. ('brokerage_record_status', '已取消', 2, 2);
  135. insert into system_dict_type(type, name)
  136. values ('brokerage_withdraw_status', '佣金提现状态');
  137. insert into system_dict_data(dict_type, label, value, sort, color_type)
  138. values ('brokerage_withdraw_status', '审核中', 0, 0, ''),
  139. ('brokerage_withdraw_status', '审核通过', 10, 10, 'success'),
  140. ('brokerage_withdraw_status', '提现成功', 11, 11, 'success'),
  141. ('brokerage_withdraw_status', '审核不通过', 20, 20, 'danger'),
  142. ('brokerage_withdraw_status', '提现失败', 21, 21, 'danger');
  143. insert into system_dict_type(type, name)
  144. values ('brokerage_bank_name', '佣金提现银行');
  145. insert into system_dict_data(dict_type, label, value, sort)
  146. values ('brokerage_bank_name', '工商银行', 0, 0),
  147. ('brokerage_bank_name', '建设银行', 1, 1),
  148. ('brokerage_bank_name', '农业银行', 2, 2),
  149. ('brokerage_bank_name', '中国银行', 3, 3),
  150. ('brokerage_bank_name', '交通银行', 4, 4),
  151. ('brokerage_bank_name', '招商银行', 5, 5);
  152. -- 交易中心配置:菜单 SQL
  153. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status, component_name)
  154. VALUES ('交易中心配置', '', 2, 0, 2072, 'config', 'ep:setting', 'trade/config/index', 0, 'TradeConfig');
  155. -- 按钮父菜单ID
  156. -- 暂时只支持 MySQL。如果你是 Oracle、PostgreSQL、SQLServer 的话,需要手动修改 @parentId 的部分的代码
  157. SELECT @parentId := LAST_INSERT_ID();
  158. -- 按钮 SQL
  159. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  160. VALUES ('交易中心配置查询', 'trade:config:query', 3, 1, @parentId, '', '', '', 0);
  161. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  162. VALUES ('交易中心配置保存', 'trade:config:save', 3, 2, @parentId, '', '', '', 0);
  163. -- 增加菜单:分销
  164. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status, component_name)
  165. VALUES ('分销', '', 1, 5, 2072, 'brokerage', 'fa-solid:project-diagram', '', 0, '');
  166. -- 按钮父菜单ID
  167. SELECT @brokerageMenuId := LAST_INSERT_ID();
  168. -- 增加菜单:分销员
  169. -- 菜单 SQL
  170. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status, component_name)
  171. VALUES ('分销用户', '', 2, 0, @brokerageMenuId, 'brokerage-user', 'fa-solid:user-tie', 'trade/brokerage/user/index', 0,
  172. 'TradeBrokerageUser');
  173. -- 按钮父菜单ID
  174. -- 暂时只支持 MySQL。如果你是 Oracle、PostgreSQL、SQLServer 的话,需要手动修改 @parentId 的部分的代码
  175. SELECT @parentId := LAST_INSERT_ID();
  176. -- 按钮 SQL
  177. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  178. VALUES ('分销用户查询', 'trade:brokerage-user:query', 3, 1, @parentId, '', '', '', 0);
  179. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  180. VALUES ('分销用户推广人查询', 'trade:brokerage-user:user-query', 3, 2, @parentId, '', '', '', 0);
  181. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  182. VALUES ('分销用户推广订单查询', 'trade:brokerage-user:order-query', 3, 3, @parentId, '', '', '', 0);
  183. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  184. VALUES ('分销用户修改推广资格', 'trade:brokerage-user:update-brokerage-enable', 3, 4, @parentId, '', '', '', 0);
  185. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  186. VALUES ('分销用户修改推广员', 'trade:brokerage-user:update-bind-user', 3, 5, @parentId, '', '', '', 0);
  187. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  188. VALUES ('分销用户清除推广员', 'trade:brokerage-user:clear-bind-user', 3, 6, @parentId, '', '', '', 0);
  189. -- 增加菜单:佣金记录
  190. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status, component_name)
  191. VALUES ('佣金记录', '', 2, 1, @brokerageMenuId, 'brokerage-record', 'fa:money', 'trade/brokerage/record/index', 0,
  192. 'TradeBrokerageRecord');
  193. -- 按钮父菜单ID
  194. -- 暂时只支持 MySQL。如果你是 Oracle、PostgreSQL、SQLServer 的话,需要手动修改 @parentId 的部分的代码
  195. SELECT @parentId := LAST_INSERT_ID();
  196. -- 按钮 SQL
  197. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  198. VALUES ('佣金记录查询', 'trade:brokerage-record:query', 3, 1, @parentId, '', '', '', 0);
  199. -- 增加菜单:佣金提现
  200. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status, component_name)
  201. VALUES ('佣金提现', '', 2, 2, @brokerageMenuId, 'brokerage-withdraw', 'fa:credit-card',
  202. 'trade/brokerage/withdraw/index', 0, 'TradeBrokerageWithdraw');
  203. -- 按钮父菜单ID
  204. -- 暂时只支持 MySQL。如果你是 Oracle、PostgreSQL、SQLServer 的话,需要手动修改 @parentId 的部分的代码
  205. SELECT @parentId := LAST_INSERT_ID();
  206. -- 按钮 SQL
  207. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  208. VALUES ('佣金提现查询', 'trade:brokerage-withdraw:query', 3, 1, @parentId, '', '', '', 0);
  209. INSERT INTO system_menu(name, permission, type, sort, parent_id, path, icon, component, status)
  210. VALUES ('佣金提现审核', 'trade:brokerage-withdraw:audit', 3, 2, @parentId, '', '', '', 0);
  211. -- 站内信模板
  212. INSERT INTO `ruoyi-vue-pro`.system_notify_template (name, code, nickname, content, type, params, status)
  213. VALUES
  214. ('佣金提现(审核通过)', 'brokerage_withdraw_audit_approve', 'system', '您在{createTime}提现¥{price}元的申请已通过审核', 2, '["createTime","price"]', 0),
  215. ('佣金提现(审核不通过)', 'brokerage_withdraw_audit_reject', 'system', '您在{createTime}提现¥{price}元的申请未通过审核,原因:{reason}', 2, '["createTime","price","reason"]', 0);