INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
1300, 1000, '系统设置', 'system_setting_group', 'M', '/system/settings', NULL, NULL, 30, 1, 1, NULL, '系统设置目录', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 1300 OR `menu_code` = 'system_setting_group');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
1310, 1300, '积分设置', 'system_points_settings', 'C', '/system/settings/points', 'System/PointsSettings', NULL, 10, 1, 1, 'system:points-settings:view', '积分设置菜单', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 1310 OR `menu_code` = 'system_points_settings');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
1311, 1310, '编辑积分设置', 'system_points_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:points-settings:edit', '积分设置编辑按钮', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 1311 OR `menu_code` = 'system_points_settings_edit');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4000, 0, '交易与财务', 'trade_root', 'M', '/trade', NULL, 'moneyCollect', 400, 1, 1, NULL, '交易与财务模块', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4000 OR `menu_code` = 'trade_root');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4100, 4000, '订单', 'trade_order_group', 'M', '/trade/order', NULL, NULL, 10, 1, 1, NULL, '订单目录', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4100 OR `menu_code` = 'trade_order_group');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4110, 4100, '积分兑换', 'trade_points_exchange', 'C', '/trade/order/points-exchange', 'Trade/PointsExchange', NULL, 10, 1, 1, 'points:exchange:view', '积分兑换菜单', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4110 OR `menu_code` = 'trade_points_exchange');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4111, 4110, '兑换详情', 'trade_points_exchange_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'points:exchange:detail', '积分兑换详情按钮', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4111 OR `menu_code` = 'trade_points_exchange_detail');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4112, 4110, '积分兑换发货', 'trade_points_exchange_ship', 'B', NULL, NULL, NULL, 2, 1, 1, 'points:exchange:ship', '积分兑换发货按钮', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4112 OR `menu_code` = 'trade_points_exchange_ship');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4113, 4110, '积分兑换删除', 'trade_points_exchange_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'points:exchange:delete', '积分兑换删除按钮', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4113 OR `menu_code` = 'trade_points_exchange_delete');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4114, 4110, '积分兑换导出', 'trade_points_exchange_export', 'B', NULL, NULL, NULL, 4, 1, 1, 'points:exchange:export', '积分兑换导出按钮', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4114 OR `menu_code` = 'trade_points_exchange_export');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4200, 4000, '财务流水', 'trade_finance_flow_group', 'M', '/trade/flow', NULL, NULL, 20, 1, 1, NULL, '财务流水目录', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4200 OR `menu_code` = 'trade_finance_flow_group');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4210, 4200, '积分收支明细', 'trade_points_ledger', 'C', '/trade/flow/points-ledger', 'Trade/PointsLedger', NULL, 10, 1, 1, 'points:ledger:view', '积分收支明细菜单', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4210 OR `menu_code` = 'trade_points_ledger');
|
|
INSERT INTO `sys_menu`
|
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
|
SELECT
|
4211, 4210, '导出积分收支明细', 'trade_points_ledger_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'points:ledger:export', '积分收支明细导出按钮', 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4211 OR `menu_code` = 'trade_points_ledger_export');
|
|
INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
|
SELECT sr.id, sm.id
|
FROM `sys_role` sr
|
JOIN `sys_menu` sm ON sm.id IN (1300, 1310, 1311, 4000, 4100, 4110, 4111, 4112, 4113, 4114, 4200, 4210, 4211)
|
WHERE sr.deleted = 0
|
AND sr.status = 1
|
AND EXISTS (
|
SELECT 1
|
FROM `sys_role_menu` rm
|
WHERE rm.role_id = sr.id
|
AND rm.menu_id IN (1100, 1200, 3060, 3080, 3081)
|
);
|
|
CREATE TABLE IF NOT EXISTS `tcm_points_config` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`activity_start_at` DATETIME NOT NULL COMMENT '活动开始时间',
|
`activity_end_at` DATETIME NOT NULL COMMENT '活动结束时间',
|
`gift_cover` VARCHAR(255) NOT NULL COMMENT '礼品封面图',
|
`gift_name` VARCHAR(64) NOT NULL COMMENT '礼品名称',
|
`required_points` INT NOT NULL COMMENT '所需积分',
|
`settlement_status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '结算状态：0待结算 1已结算',
|
`settled_at` DATETIME 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`),
|
KEY `idx_points_config_end` (`activity_end_at`),
|
KEY `idx_points_config_status` (`settlement_status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分设置表';
|
|
CREATE TABLE IF NOT EXISTS `tcm_user_points_account` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
`points_balance` INT NOT NULL DEFAULT 0 COMMENT '当前积分余额',
|
`last_settled_config_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '最后一次结算配置ID',
|
`last_cleared_at` DATETIME 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`),
|
UNIQUE KEY `uk_points_account_user` (`app_user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户积分账户表';
|
|
CREATE TABLE IF NOT EXISTS `tcm_points_exchange_order` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`order_no` VARCHAR(32) NOT NULL COMMENT '汇总订单编号',
|
`config_id` BIGINT UNSIGNED NOT NULL COMMENT '积分配置ID',
|
`gift_cover` VARCHAR(255) DEFAULT NULL COMMENT '礼品封面图',
|
`gift_name` VARCHAR(64) NOT NULL COMMENT '礼品名称',
|
`required_points` INT NOT NULL COMMENT '所需积分',
|
`activity_start_at` DATETIME NOT NULL COMMENT '活动开始时间',
|
`activity_end_at` DATETIME NOT NULL COMMENT '活动结束时间',
|
`clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
|
`clinic_app_user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所用户ID',
|
`clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
|
`receiver_name` VARCHAR(64) DEFAULT NULL COMMENT '收件人姓名',
|
`receiver_phone` VARCHAR(20) DEFAULT NULL COMMENT '收件人电话',
|
`receiver_address` VARCHAR(255) DEFAULT NULL COMMENT '收货地址',
|
`item_count` INT NOT NULL DEFAULT 0 COMMENT '兑换人数',
|
`total_points` INT NOT NULL DEFAULT 0 COMMENT '汇总积分',
|
`status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '状态：0待发货 1已发货 2已收货',
|
`tracking_no` VARCHAR(32) DEFAULT NULL COMMENT '物流单号',
|
`shipped_at` DATETIME DEFAULT NULL COMMENT '发货时间',
|
`completed_at` DATETIME DEFAULT NULL COMMENT '完成时间',
|
`cancelled_at` DATETIME 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`),
|
UNIQUE KEY `uk_points_exchange_order_no` (`order_no`),
|
KEY `idx_points_exchange_order_config` (`config_id`),
|
KEY `idx_points_exchange_order_clinic_app_user_id` (`clinic_app_user_id`),
|
KEY `idx_points_exchange_order_status` (`status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换汇总订单表';
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order'
|
AND `COLUMN_NAME` = 'clinic_app_user_id'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_points_exchange_order` ADD COLUMN `clinic_app_user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT ''诊所用户ID'' AFTER `clinic_id`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`STATISTICS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order'
|
AND `INDEX_NAME` = 'idx_points_exchange_order_clinic_app_user_id'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_points_exchange_order` ADD INDEX `idx_points_exchange_order_clinic_app_user_id` (`clinic_app_user_id`)'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
CREATE TABLE IF NOT EXISTS `tcm_points_exchange_order_item` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`order_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '汇总订单ID',
|
`order_no` VARCHAR(32) NOT NULL COMMENT '明细订单编号',
|
`app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
`user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
|
`user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户电话快照',
|
`clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
|
`clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
|
`province_code` VARCHAR(32) DEFAULT NULL COMMENT '省编码快照',
|
`province_name` VARCHAR(64) DEFAULT NULL COMMENT '省名称快照',
|
`city_code` VARCHAR(32) DEFAULT NULL COMMENT '市编码快照',
|
`city_name` VARCHAR(64) DEFAULT NULL COMMENT '市名称快照',
|
`district_code` VARCHAR(32) DEFAULT NULL COMMENT '区编码快照',
|
`district_name` VARCHAR(64) DEFAULT NULL COMMENT '区名称快照',
|
`settled_points` INT NOT NULL DEFAULT 0 COMMENT '结算前积分',
|
`status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '状态：0待发货 1待取货 2兑换完成',
|
`tracking_no` VARCHAR(32) DEFAULT NULL COMMENT '物流单号',
|
`shipped_at` DATETIME DEFAULT NULL COMMENT '发货时间',
|
`completed_at` DATETIME DEFAULT NULL COMMENT '完成时间',
|
`cancelled_at` DATETIME 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`),
|
UNIQUE KEY `uk_points_exchange_item_order_no` (`order_no`),
|
KEY `idx_points_exchange_item_order_id` (`order_id`),
|
KEY `idx_points_exchange_item_user_id` (`app_user_id`),
|
KEY `idx_points_exchange_item_status` (`status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换明细订单表';
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`STATISTICS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `INDEX_NAME` = 'idx_points_exchange_item_config_id'
|
),
|
'ALTER TABLE `tcm_points_exchange_order_item` DROP INDEX `idx_points_exchange_item_config_id`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'config_id'
|
),
|
'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `config_id`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'receiver_name'
|
),
|
'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `receiver_name`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'receiver_phone'
|
),
|
'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `receiver_phone`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'receiver_address'
|
),
|
'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `receiver_address`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'gift_cover'
|
),
|
'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `gift_cover`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'gift_name'
|
),
|
'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `gift_name`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'required_points'
|
),
|
'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `required_points`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_clinic'
|
AND `COLUMN_NAME` = 'province_code'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_clinic` ADD COLUMN `province_code` VARCHAR(32) DEFAULT NULL COMMENT ''省编码'' AFTER `contact_phone`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_clinic'
|
AND `COLUMN_NAME` = 'province_name'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_clinic` ADD COLUMN `province_name` VARCHAR(64) DEFAULT NULL COMMENT ''省名称'' AFTER `province_code`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_clinic'
|
AND `COLUMN_NAME` = 'city_code'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_clinic` ADD COLUMN `city_code` VARCHAR(32) DEFAULT NULL COMMENT ''市编码'' AFTER `province_name`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_clinic'
|
AND `COLUMN_NAME` = 'city_name'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_clinic` ADD COLUMN `city_name` VARCHAR(64) DEFAULT NULL COMMENT ''市名称'' AFTER `city_code`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_clinic'
|
AND `COLUMN_NAME` = 'district_code'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_clinic` ADD COLUMN `district_code` VARCHAR(32) DEFAULT NULL COMMENT ''区编码'' AFTER `city_name`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_clinic'
|
AND `COLUMN_NAME` = 'district_name'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_clinic` ADD COLUMN `district_name` VARCHAR(64) DEFAULT NULL COMMENT ''区名称'' AFTER `district_code`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'province_code'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `province_code` VARCHAR(32) DEFAULT NULL COMMENT ''省编码快照'' AFTER `clinic_name`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'province_name'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `province_name` VARCHAR(64) DEFAULT NULL COMMENT ''省名称快照'' AFTER `province_code`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'city_code'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `city_code` VARCHAR(32) DEFAULT NULL COMMENT ''市编码快照'' AFTER `province_name`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'city_name'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `city_name` VARCHAR(64) DEFAULT NULL COMMENT ''市名称快照'' AFTER `city_code`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'district_code'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `district_code` VARCHAR(32) DEFAULT NULL COMMENT ''区编码快照'' AFTER `city_name`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF (
|
EXISTS (
|
SELECT 1
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
|
AND `COLUMN_NAME` = 'district_name'
|
),
|
'SELECT 1',
|
'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `district_name` VARCHAR(64) DEFAULT NULL COMMENT ''区名称快照'' AFTER `district_code`'
|
);
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
CREATE TABLE IF NOT EXISTS `tcm_points_ledger` (
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
`user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
|
`user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户电话快照',
|
`clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
|
`clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
|
`province_code` VARCHAR(32) DEFAULT NULL COMMENT '省编码快照',
|
`province_name` VARCHAR(64) DEFAULT NULL COMMENT '省名称快照',
|
`city_code` VARCHAR(32) DEFAULT NULL COMMENT '市编码快照',
|
`city_name` VARCHAR(64) DEFAULT NULL COMMENT '市名称快照',
|
`district_code` VARCHAR(32) DEFAULT NULL COMMENT '区编码快照',
|
`district_name` VARCHAR(64) DEFAULT NULL COMMENT '区名称快照',
|
`income_expense_type` VARCHAR(32) NOT NULL COMMENT '收支类型：INCOME/EXPENSE',
|
`business_type` VARCHAR(32) NOT NULL COMMENT '业务类型：LIVE_WATCH/POINTS_EXCHANGE',
|
`content_name` VARCHAR(128) DEFAULT NULL COMMENT '内容名称',
|
`reference_type` VARCHAR(32) DEFAULT NULL COMMENT '引用类型',
|
`reference_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '引用ID',
|
`reference_no` VARCHAR(64) DEFAULT NULL COMMENT '引用编号',
|
`points_delta` INT NOT NULL DEFAULT 0 COMMENT '积分变动值',
|
`operated_at` DATETIME NOT NULL COMMENT '操作时间',
|
`source_type` VARCHAR(32) NOT NULL COMMENT '源类型',
|
`source_id` BIGINT UNSIGNED NOT NULL COMMENT '源数据ID',
|
`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`),
|
UNIQUE KEY `uk_points_ledger_source` (`source_type`, `source_id`),
|
KEY `idx_points_ledger_user` (`app_user_id`),
|
KEY `idx_points_ledger_operated_at` (`operated_at`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分收支流水表';
|
