member_level.sql
6.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
-- 会员表增加字段
alter table member_user add column experience int not null default 0 comment '经验';
alter table member_user add column level_id bigint comment '等级编号';
-- 增加3张表
create table member_level
(
id bigint auto_increment comment '编号' primary key,
name varchar(30) default '' not null comment '等级名称',
experience int default 0 not null comment '升级经验',
level int default 0 not null comment '等级',
discount int(4) default 100 not null comment '享受折扣',
icon varchar(255) default '' not null comment '等级图标',
background_url varchar(255) default '' not null comment '等级背景图',
status tinyint default 0 not null comment '状态',
creator varchar(64) default '' null comment '创建者',
create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
updater varchar(64) default '' null comment '更新者',
update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
deleted bit default b'0' not null comment '是否删除',
tenant_id bigint default 0 not null comment '租户编号'
)
comment '会员等级';
create table member_level_log
(
id bigint auto_increment comment '编号' primary key,
user_id bigint default 0 not null comment '用户编号',
level_id bigint default 0 not null comment '等级编号',
level int default 0 not null comment '会员等级',
discount int(4) default 100 not null comment '享受折扣',
experience int(4) default 100 not null comment '升级经验',
user_experience int(4) default 100 not null comment '会员此时的经验',
remark varchar(255) default '' not null comment '备注',
description varchar(255) default '' not null comment '描述',
creator varchar(64) default '' null comment '创建者',
create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
updater varchar(64) default '' null comment '更新者',
update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
deleted bit default b'0' not null comment '是否删除',
tenant_id bigint default 0 not null comment '租户编号'
)
comment '会员等级记录';
create index idx_user_id on member_level_log (user_id) comment '会员等级记录-用户编号';
create table member_experience_log
(
id bigint auto_increment comment '编号' primary key,
user_id bigint default 0 not null comment '用户编号',
biz_id varchar(64) default '' not null comment '业务编号',
biz_type tinyint default 0 not null comment '业务类型',
title varchar(30) default '' not null comment '标题',
experience int default 0 not null comment '经验',
total_experience int default 0 not null comment '变更后的经验',
description varchar(512) default '' not null comment '描述',
creator varchar(64) default '' null comment '创建者',
create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
updater varchar(64) default '' null comment '更新者',
update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
deleted bit default b'0' not null comment '是否删除',
tenant_id bigint default 0 not null comment '租户编号'
)
comment '会员经验记录';
create index idx_user_id on member_experience_log (user_id) comment '会员经验记录-用户编号';
create index idx_user_biz_type on member_experience_log (user_id, biz_type) comment '会员经验记录-用户业务类型';
-- 增加字典
insert system_dict_type(name, type) values ('会员经验业务类型', 'member_experience_biz_type');
insert system_dict_data(dict_type, label, value, sort) values ('member_experience_biz_type', '系统', '0', 0);
insert system_dict_data(dict_type, label, value, sort) values ('member_experience_biz_type', '订单', '1', 1);
insert system_dict_data(dict_type, label, value, sort) values ('member_experience_biz_type', '签到', '2', 2);
-- 菜单 SQL
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status, component_name
)
VALUES (
'会员等级', '', 2, 3, 2262,
'level', '', 'member/level/index', 0, 'MemberLevel'
);
-- 按钮父菜单ID
-- 暂时只支持 MySQL。如果你是 Oracle、PostgreSQL、SQLServer 的话，需要手动修改 @parentId 的部分的代码
SELECT @parentId := LAST_INSERT_ID();
-- 按钮 SQL
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'会员等级查询', 'member:level:query', 3, 1, @parentId,
'', '', '', 0
);
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'会员等级创建', 'member:level:create', 3, 2, @parentId,
'', '', '', 0
);
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'会员等级更新', 'member:level:update', 3, 3, @parentId,
'', '', '', 0
);
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'会员等级删除', 'member:level:delete', 3, 4, @parentId,
'', '', '', 0
);