20241223lh_01.sql

ALTER TABLE member_user_level_config
ADD COLUMN level INT COMMENT '等级',
ADD COLUMN status INT COMMENT '状态（0.未发布，1.已启用，2.已禁用，3.已过期）',
ADD COLUMN rule_number VARCHAR(20) COMMENT '规则编号',
ADD COLUMN validity_period INT COMMENT '等级有效期',
ADD COLUMN accumulation_period INT COMMENT '积分累计周期',
ADD COLUMN start_date DATETIME COMMENT '积分开始累计时间',
ADD COLUMN enable_date DATETIME COMMENT '启用日期',
ADD COLUMN start_time DATETIME COMMENT '等级规则有效期开始时间',
ADD COLUMN end_time DATETIME COMMENT '等级规则有效期结束时间',
ADD COLUMN description VARCHAR(200) COMMENT '等级说明',
ADD COLUMN sort INT COMMENT '匹配顺序（限制为数字）';

INSERT INTO jiedao.system_menu (name, permission, menu_type, sort, parent_id, `path`, icon, component, status, creator, create_time, updater, update_time, deleted, is_show_in_menu_bar, name_en, keepalive, redirect, badge_field) VALUES('会员等级设置', '', 2, 1, 1520, 'operatingLevel', '#', 'ecw/memberManagement/operatingLevel', 0, '1', '2024-12-13 14:37:24', '1', '2024-12-13 15:14:29', 0, 0, '会员等级设置', 0, NULL, NULL);

CREATE TABLE `jiedao`.`member_user_level_log`
(
    `id` bigint   NOT NULL AUTO_INCREMENT COMMENT '主键',
    `config_id` bigint NOT NULL COMMENT '会员等级配置id',
    `operator` VARCHAR(255) NOT NULL COMMENT '操作人',
    `remarks` TEXT  NULL COMMENT '备注',
    `operate_type` INT NOT NULL COMMENT '操作类型',
    `bpm_process_id` BIGINT  NULL COMMENT '流程ID',
    `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` bit(1)   NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '会员等级日志表' ROW_FORMAT = Dynamic;



INSERT INTO jiedao.system_dict_type (name, `type`, status, remark, creator, create_time, updater, update_time, deleted) VALUES('会员等级', 'membership_levels', 0, NULL, '1', '2024-12-12 14:03:26', '1', '2024-12-12 14:09:37', 0);
INSERT INTO jiedao.system_dict_data (sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted, label_en, label_fr) VALUES(0, '青铜会员', '1', 'membership_levels', 0, 'default', '', NULL, '1', '2024-12-12 14:10:46', '1', '2024-12-12 14:17:06', 0, 'Bronze Member', 'Bronze Member');
INSERT INTO jiedao.system_dict_data (sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted, label_en, label_fr) VALUES(0, '银级会员', '2', 'membership_levels', 0, 'default', '', NULL, '1', '2024-12-12 14:14:17', '1', '2024-12-12 14:17:25', 0, 'Silver Member', 'Silver Member');
INSERT INTO jiedao.system_dict_data (sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted, label_en, label_fr) VALUES(0, '黄金级会员', '3', 'membership_levels', 0, 'default', '', NULL, '1', '2024-12-12 14:15:09', '1', '2024-12-12 14:17:37', 0, 'Gold Member', 'Gold Member');
INSERT INTO jiedao.system_dict_data (sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted, label_en, label_fr) VALUES(0, '白金级会员', '4', 'membership_levels', 0, 'default', '', NULL, '1', '2024-12-12 14:15:59', '1', '2024-12-12 14:17:48', 0, 'Platinum Member', 'Platinum Member');

INSERT INTO jiedao.system_dict_type
(name, `type`, status, remark, creator, create_time, updater, update_time, deleted)
VALUES('用户等级操作类型', 'member_user_operate_log', 0, NULL, '1', '2024-12-25 09:55:40', '1', '2024-12-25 09:55:40', 0);
INSERT INTO jiedao.system_dict_data
(sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted, label_en, label_fr)
VALUES(0, '升级', '1', 'member_user_operate_log', 0, 'default', '', NULL, '1', '2024-12-25 09:58:31', '1', '2024-12-25 10:07:04', 0, 'Upgrade', 'Mise à niveau');
INSERT INTO jiedao.system_dict_data
(sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted, label_en, label_fr)
VALUES(1, '降级', '2', 'member_user_operate_log', 0, 'default', '', NULL, '1', '2024-12-25 09:58:50', '1', '2024-12-25 10:06:59', 0, 'Downgrade', 'Rétrogradation');
INSERT INTO jiedao.system_dict_data
(sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted, label_en, label_fr)
VALUES(2, '修改信息', '3', 'member_user_operate_log', 0, 'default', '', NULL, '1', '2024-12-25 09:59:24', '1', '2024-12-25 10:06:54', 0, 'Modify Information', 'Modifier les informations');

CREATE TABLE `jiedao`.`member_user_level_details`
(
    `id` bigint   NOT NULL AUTO_INCREMENT COMMENT '主键',
    `config_id` bigint NOT NULL COMMENT '会员等级配置id',
    `member_id` bigint NOT NULL COMMENT '会员id',
    `level` INT NULL COMMENT '等级',
    `validity_period` INT NULL COMMENT '等级有效期',
    `specific_settings` bit(1)   NOT NULL DEFAULT b'0' COMMENT '特殊设置(0关闭 1开启)',
    `remarks` TEXT  NULL COMMENT '备注',
    `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` bit(1)   NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '会员等级详情表' ROW_FORMAT = Dynamic;



CREATE TABLE `jiedao`.`member_user_level_operate_log`
(
    `id` bigint   NOT NULL AUTO_INCREMENT COMMENT '主键',
    `config_id` bigint NOT NULL COMMENT '会员等级配置id',
    `config_name` VARCHAR(255) NOT NULL COMMENT '会员等级配置名称',
    `member_id` bigint NOT NULL COMMENT '会员id',
    `operator` VARCHAR(255) NOT NULL COMMENT '操作人',
    `remarks` TEXT  NULL COMMENT '说明',
    `level` INT COMMENT '等级',
    `operate_type` INT NOT NULL COMMENT '操作类型',
    `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` bit(1)   NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '会员等级操作记录日志表' ROW_FORMAT = Dynamic;