37 lines
1.5 KiB
SQL
37 lines
1.5 KiB
SQL
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE hyapp_wallet;
|
|
|
|
SET @now_ms = CAST(UNIX_TIMESTAMP(UTC_TIMESTAMP(3)) * 1000 AS UNSIGNED);
|
|
|
|
SET @ddl := IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'gift_diamond_ratio_configs' AND COLUMN_NAME = 'coin_return_ratio_percent') = 0,
|
|
'ALTER TABLE gift_diamond_ratio_configs ADD COLUMN coin_return_ratio_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT ''收礼人金币返还比例，百分比'' AFTER ratio_percent',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
UPDATE gift_diamond_ratio_configs
|
|
SET coin_return_ratio_percent = CASE gift_type_code
|
|
WHEN 'lucky' THEN 10.00
|
|
WHEN 'super_lucky' THEN 1.00
|
|
ELSE 30.00
|
|
END
|
|
WHERE coin_return_ratio_percent = 0.00;
|
|
|
|
INSERT INTO gift_diamond_ratio_configs (
|
|
app_code, region_id, gift_type_code, status, ratio_percent, coin_return_ratio_percent,
|
|
created_by_admin_id, updated_by_admin_id, created_at_ms, updated_at_ms
|
|
) VALUES
|
|
('lalu', 0, 'normal', 'active', 100.00, 30.00, 0, 0, @now_ms, @now_ms),
|
|
('lalu', 0, 'lucky', 'active', 10.00, 10.00, 0, 0, @now_ms, @now_ms),
|
|
('lalu', 0, 'super_lucky', 'active', 1.00, 1.00, 0, 0, @now_ms, @now_ms)
|
|
ON DUPLICATE KEY UPDATE
|
|
status = VALUES(status),
|
|
ratio_percent = VALUES(ratio_percent),
|
|
coin_return_ratio_percent = VALUES(coin_return_ratio_percent),
|
|
updated_by_admin_id = VALUES(updated_by_admin_id),
|
|
updated_at_ms = VALUES(updated_at_ms);
|