-- ----------------------------
|
-- 会员价格配置表
|
-- ----------------------------
|
CREATE TABLE IF NOT EXISTS `tcm_member_price_config` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`month_card_price` DECIMAL(10,2) NOT NULL COMMENT '月卡会员价格',
|
`month_card_days` INT NOT NULL COMMENT '月卡时长',
|
`month_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '月卡是否启用',
|
`quarter_card_price` DECIMAL(10,2) NOT NULL COMMENT '季卡会员价格',
|
`quarter_card_days` INT NOT NULL COMMENT '季卡时长',
|
`quarter_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '季卡是否启用',
|
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
|
`created_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '创建人',
|
`updated_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '更新人',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员价格配置表';
|
|
SET @has_month_card_price = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'month_card_price'
|
);
|
SET @sql_add_month_card_price = IF(
|
@has_month_card_price = 0,
|
'ALTER TABLE `tcm_member_price_config` ADD COLUMN `month_card_price` DECIMAL(10,2) NOT NULL DEFAULT 69.00 COMMENT ''月卡会员价格'' AFTER `id`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_month_card_price FROM @sql_add_month_card_price;
|
EXECUTE stmt_add_month_card_price;
|
DEALLOCATE PREPARE stmt_add_month_card_price;
|
|
SET @has_month_card_days = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'month_card_days'
|
);
|
SET @sql_add_month_card_days = IF(
|
@has_month_card_days = 0,
|
'ALTER TABLE `tcm_member_price_config` ADD COLUMN `month_card_days` INT NOT NULL DEFAULT 30 COMMENT ''月卡时长'' AFTER `month_card_price`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_month_card_days FROM @sql_add_month_card_days;
|
EXECUTE stmt_add_month_card_days;
|
DEALLOCATE PREPARE stmt_add_month_card_days;
|
|
SET @has_month_card_enabled = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'month_card_enabled'
|
);
|
SET @sql_add_month_card_enabled = IF(
|
@has_month_card_enabled = 0,
|
'ALTER TABLE `tcm_member_price_config` ADD COLUMN `month_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''月卡是否启用'' AFTER `month_card_days`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_month_card_enabled FROM @sql_add_month_card_enabled;
|
EXECUTE stmt_add_month_card_enabled;
|
DEALLOCATE PREPARE stmt_add_month_card_enabled;
|
|
SET @has_quarter_card_price = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'quarter_card_price'
|
);
|
SET @sql_add_quarter_card_price = IF(
|
@has_quarter_card_price = 0,
|
'ALTER TABLE `tcm_member_price_config` ADD COLUMN `quarter_card_price` DECIMAL(10,2) NOT NULL DEFAULT 199.00 COMMENT ''季卡会员价格'' AFTER `month_card_enabled`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_quarter_card_price FROM @sql_add_quarter_card_price;
|
EXECUTE stmt_add_quarter_card_price;
|
DEALLOCATE PREPARE stmt_add_quarter_card_price;
|
|
SET @has_quarter_card_days = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'quarter_card_days'
|
);
|
SET @sql_add_quarter_card_days = IF(
|
@has_quarter_card_days = 0,
|
'ALTER TABLE `tcm_member_price_config` ADD COLUMN `quarter_card_days` INT NOT NULL DEFAULT 90 COMMENT ''季卡时长'' AFTER `quarter_card_price`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_quarter_card_days FROM @sql_add_quarter_card_days;
|
EXECUTE stmt_add_quarter_card_days;
|
DEALLOCATE PREPARE stmt_add_quarter_card_days;
|
|
SET @has_quarter_card_enabled = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'quarter_card_enabled'
|
);
|
SET @sql_add_quarter_card_enabled = IF(
|
@has_quarter_card_enabled = 0,
|
'ALTER TABLE `tcm_member_price_config` ADD COLUMN `quarter_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''季卡是否启用'' AFTER `quarter_card_days`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_quarter_card_enabled FROM @sql_add_quarter_card_enabled;
|
EXECUTE stmt_add_quarter_card_enabled;
|
DEALLOCATE PREPARE stmt_add_quarter_card_enabled;
|
|
SET @has_legacy_member_price_columns = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME IN ('tier_one_days', 'tier_one_original_price', 'tier_one_sale_price', 'tier_two_days', 'tier_two_original_price', 'tier_two_sale_price')
|
);
|
|
SET @member_price_migration_sql = IF(
|
@has_legacy_member_price_columns = 6,
|
'UPDATE `tcm_member_price_config`
|
SET
|
`month_card_price` = CASE
|
WHEN `month_card_price` IS NULL OR `month_card_price` <= 0 THEN COALESCE(`tier_one_sale_price`, `tier_one_original_price`, 69.00)
|
ELSE `month_card_price`
|
END,
|
`month_card_days` = CASE
|
WHEN `month_card_days` IS NULL OR `month_card_days` <= 0 THEN COALESCE(`tier_one_days`, 30)
|
ELSE `month_card_days`
|
END,
|
`quarter_card_price` = CASE
|
WHEN `quarter_card_price` IS NULL OR `quarter_card_price` <= 0 THEN COALESCE(`tier_two_sale_price`, `tier_two_original_price`, 199.00)
|
ELSE `quarter_card_price`
|
END,
|
`quarter_card_days` = CASE
|
WHEN `quarter_card_days` IS NULL OR `quarter_card_days` <= 0 THEN COALESCE(`tier_two_days`, 90)
|
ELSE `quarter_card_days`
|
END',
|
'SELECT 1'
|
);
|
|
PREPARE member_price_migration_stmt FROM @member_price_migration_sql;
|
EXECUTE member_price_migration_stmt;
|
DEALLOCATE PREPARE member_price_migration_stmt;
|
|
SET @has_tier_one_name = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'tier_one_name'
|
);
|
SET @sql_drop_tier_one_name = IF(
|
@has_tier_one_name = 1,
|
'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_one_name`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_tier_one_name FROM @sql_drop_tier_one_name;
|
EXECUTE stmt_drop_tier_one_name;
|
DEALLOCATE PREPARE stmt_drop_tier_one_name;
|
|
SET @has_tier_one_days = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'tier_one_days'
|
);
|
SET @sql_drop_tier_one_days = IF(
|
@has_tier_one_days = 1,
|
'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_one_days`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_tier_one_days FROM @sql_drop_tier_one_days;
|
EXECUTE stmt_drop_tier_one_days;
|
DEALLOCATE PREPARE stmt_drop_tier_one_days;
|
|
SET @has_tier_one_original_price = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'tier_one_original_price'
|
);
|
SET @sql_drop_tier_one_original_price = IF(
|
@has_tier_one_original_price = 1,
|
'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_one_original_price`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_tier_one_original_price FROM @sql_drop_tier_one_original_price;
|
EXECUTE stmt_drop_tier_one_original_price;
|
DEALLOCATE PREPARE stmt_drop_tier_one_original_price;
|
|
SET @has_tier_one_sale_price = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'tier_one_sale_price'
|
);
|
SET @sql_drop_tier_one_sale_price = IF(
|
@has_tier_one_sale_price = 1,
|
'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_one_sale_price`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_tier_one_sale_price FROM @sql_drop_tier_one_sale_price;
|
EXECUTE stmt_drop_tier_one_sale_price;
|
DEALLOCATE PREPARE stmt_drop_tier_one_sale_price;
|
|
SET @has_tier_two_name = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'tier_two_name'
|
);
|
SET @sql_drop_tier_two_name = IF(
|
@has_tier_two_name = 1,
|
'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_two_name`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_tier_two_name FROM @sql_drop_tier_two_name;
|
EXECUTE stmt_drop_tier_two_name;
|
DEALLOCATE PREPARE stmt_drop_tier_two_name;
|
|
SET @has_tier_two_days = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'tier_two_days'
|
);
|
SET @sql_drop_tier_two_days = IF(
|
@has_tier_two_days = 1,
|
'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_two_days`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_tier_two_days FROM @sql_drop_tier_two_days;
|
EXECUTE stmt_drop_tier_two_days;
|
DEALLOCATE PREPARE stmt_drop_tier_two_days;
|
|
SET @has_tier_two_original_price = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'tier_two_original_price'
|
);
|
SET @sql_drop_tier_two_original_price = IF(
|
@has_tier_two_original_price = 1,
|
'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_two_original_price`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_tier_two_original_price FROM @sql_drop_tier_two_original_price;
|
EXECUTE stmt_drop_tier_two_original_price;
|
DEALLOCATE PREPARE stmt_drop_tier_two_original_price;
|
|
SET @has_tier_two_sale_price = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_price_config'
|
AND COLUMN_NAME = 'tier_two_sale_price'
|
);
|
SET @sql_drop_tier_two_sale_price = IF(
|
@has_tier_two_sale_price = 1,
|
'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_two_sale_price`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_tier_two_sale_price FROM @sql_drop_tier_two_sale_price;
|
EXECUTE stmt_drop_tier_two_sale_price;
|
DEALLOCATE PREPARE stmt_drop_tier_two_sale_price;
|
|
-- ----------------------------
|
-- 会员介绍配置主表
|
-- ----------------------------
|
CREATE TABLE IF NOT EXISTS `tcm_member_intro_config` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`module_count` INT NOT NULL DEFAULT 0 COMMENT '模块数量',
|
`description` TEXT DEFAULT NULL COMMENT '会员权益说明',
|
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
|
`created_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '创建人',
|
`updated_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '更新人',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员介绍配置主表';
|
|
SET @has_member_intro_description = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_intro_config'
|
AND COLUMN_NAME = 'description'
|
);
|
SET @sql_add_member_intro_description = IF(
|
@has_member_intro_description = 0,
|
'ALTER TABLE `tcm_member_intro_config` ADD COLUMN `description` TEXT DEFAULT NULL COMMENT ''会员权益说明'' AFTER `module_count`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_member_intro_description FROM @sql_add_member_intro_description;
|
EXECUTE stmt_add_member_intro_description;
|
DEALLOCATE PREPARE stmt_add_member_intro_description;
|
|
UPDATE `tcm_member_intro_config`
|
SET `description` = COALESCE(`description`, '')
|
WHERE `description` IS NULL;
|
|
-- ----------------------------
|
-- 会员介绍图文模块表
|
-- ----------------------------
|
CREATE TABLE IF NOT EXISTS `tcm_member_intro_item` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`config_id` BIGINT UNSIGNED NOT NULL COMMENT '配置ID',
|
`image_url` VARCHAR(255) NOT NULL COMMENT '权益图标',
|
`description` VARCHAR(16) NOT NULL COMMENT '权益名称',
|
`sort` INT NOT NULL DEFAULT 1 COMMENT '排序',
|
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
|
`created_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '创建人',
|
`updated_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '更新人',
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id`),
|
KEY `idx_member_intro_item_config_sort` (`config_id`, `sort`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员介绍图文模块表';
|
|
SET @has_member_intro_item_description = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'tcm_member_intro_item'
|
AND COLUMN_NAME = 'description'
|
);
|
|
SET @sql_fill_member_intro_description = IF(
|
@has_member_intro_item_description = 1,
|
'UPDATE `tcm_member_intro_config` config
|
LEFT JOIN (
|
SELECT
|
`config_id`,
|
GROUP_CONCAT(TRIM(`description`) ORDER BY `sort`, `id` SEPARATOR ''\n'') AS `legacy_description`
|
FROM `tcm_member_intro_item`
|
WHERE `deleted` = 0
|
AND `description` IS NOT NULL
|
AND TRIM(`description`) <> ''''
|
GROUP BY `config_id`
|
) legacy_item ON legacy_item.`config_id` = config.`id`
|
SET config.`description` = CASE
|
WHEN (config.`description` IS NULL OR TRIM(config.`description`) = '''')
|
AND legacy_item.`legacy_description` IS NOT NULL
|
THEN legacy_item.`legacy_description`
|
ELSE config.`description`
|
END
|
WHERE config.`deleted` = 0',
|
'SELECT 1'
|
);
|
PREPARE stmt_fill_member_intro_description FROM @sql_fill_member_intro_description;
|
EXECUTE stmt_fill_member_intro_description;
|
DEALLOCATE PREPARE stmt_fill_member_intro_description;
|
|
SET @sql_normalize_member_intro_item_name = IF(
|
@has_member_intro_item_description = 1,
|
'UPDATE `tcm_member_intro_item`
|
SET `description` = CASE
|
WHEN `description` IS NULL OR TRIM(`description`) = '''' THEN CONCAT(''权益'', `id`)
|
ELSE LEFT(
|
TRIM(
|
REPLACE(
|
REPLACE(
|
REPLACE(`description`, CHAR(13), ''''),
|
CHAR(10), ''''
|
),
|
CHAR(9), ''''
|
)
|
),
|
16
|
)
|
END
|
WHERE `deleted` = 0',
|
'SELECT 1'
|
);
|
PREPARE stmt_normalize_member_intro_item_name FROM @sql_normalize_member_intro_item_name;
|
EXECUTE stmt_normalize_member_intro_item_name;
|
DEALLOCATE PREPARE stmt_normalize_member_intro_item_name;
|
|
SET @sql_modify_member_intro_item_description = IF(
|
@has_member_intro_item_description = 1,
|
'ALTER TABLE `tcm_member_intro_item` MODIFY COLUMN `description` VARCHAR(16) NOT NULL COMMENT ''权益名称''',
|
'SELECT 1'
|
);
|
PREPARE stmt_modify_member_intro_item_description FROM @sql_modify_member_intro_item_description;
|
EXECUTE stmt_modify_member_intro_item_description;
|
DEALLOCATE PREPARE stmt_modify_member_intro_item_description;
|
|
UPDATE `tcm_member_intro_config` config
|
LEFT JOIN (
|
SELECT `config_id`, COUNT(1) AS `module_count`
|
FROM `tcm_member_intro_item`
|
WHERE `deleted` = 0
|
GROUP BY `config_id`
|
) item_stat ON item_stat.`config_id` = config.`id`
|
SET config.`module_count` = COALESCE(item_stat.`module_count`, 0)
|
WHERE config.`deleted` = 0;
|
