109 lines
3.2 KiB
SQL
109 lines
3.2 KiB
SQL
-- MiFaPay V2 region commodity tiers.
|
|
-- Region: LIKEI Middle East, sys_region_config._id = 2046145518364221441.
|
|
|
|
START TRANSACTION;
|
|
|
|
SET @application_id := 2048200000000000701;
|
|
SET @region_id := '2046145518364221441';
|
|
SET @type := 'GOLD';
|
|
|
|
SET @unexpected_existing_ids := (
|
|
SELECT COUNT(*)
|
|
FROM `sys_pay_application_commodity`
|
|
WHERE `id` IN (
|
|
2048200000000000801,
|
|
2048200000000000802,
|
|
2048200000000000803,
|
|
2048200000000000804,
|
|
2048200000000000805,
|
|
2048200000000000806
|
|
)
|
|
AND (`application_id` <> @application_id OR `type` <> @type)
|
|
);
|
|
|
|
SET @abort_sql := IF(
|
|
@unexpected_existing_ids = 0,
|
|
'DO 0',
|
|
'SIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''MiFaPay commodity id collision'''
|
|
);
|
|
PREPARE stmt FROM @abort_sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
INSERT INTO `sys_pay_application` (
|
|
`id`,
|
|
`app_name`,
|
|
`app_code`,
|
|
`android_link`,
|
|
`ios_link`,
|
|
`create_time`,
|
|
`update_time`,
|
|
`create_user`,
|
|
`update_user`
|
|
) VALUES (
|
|
@application_id,
|
|
'LIKEI',
|
|
'LIKEI',
|
|
'',
|
|
'',
|
|
CURRENT_TIMESTAMP,
|
|
CURRENT_TIMESTAMP,
|
|
NULL,
|
|
NULL
|
|
) ON DUPLICATE KEY UPDATE
|
|
`app_name` = VALUES(`app_name`),
|
|
`app_code` = VALUES(`app_code`),
|
|
`android_link` = VALUES(`android_link`),
|
|
`ios_link` = VALUES(`ios_link`),
|
|
`update_time` = CURRENT_TIMESTAMP,
|
|
`update_user` = VALUES(`update_user`);
|
|
|
|
DELETE FROM `sys_pay_application_commodity`
|
|
WHERE `application_id` = @application_id
|
|
AND `region_id` = @region_id
|
|
AND `type` = @type
|
|
AND `amount_usd` IN (1.00, 5.00, 10.00, 25.00, 50.00, 100.00)
|
|
AND `id` NOT IN (
|
|
2048200000000000801,
|
|
2048200000000000802,
|
|
2048200000000000803,
|
|
2048200000000000804,
|
|
2048200000000000805,
|
|
2048200000000000806
|
|
);
|
|
|
|
INSERT INTO `sys_pay_application_commodity` (
|
|
`id`,
|
|
`application_id`,
|
|
`pay_country_id`,
|
|
`region_id`,
|
|
`type`,
|
|
`content`,
|
|
`award_content`,
|
|
`amount_usd`,
|
|
`is_shelf`,
|
|
`create_time`,
|
|
`update_time`,
|
|
`create_user`,
|
|
`update_user`
|
|
) VALUES
|
|
(2048200000000000801, @application_id, NULL, @region_id, @type, '80000', '0', 1.00, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, NULL),
|
|
(2048200000000000802, @application_id, NULL, @region_id, @type, '400000', '0', 5.00, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, NULL),
|
|
(2048200000000000803, @application_id, NULL, @region_id, @type, '800000', '0', 10.00, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, NULL),
|
|
(2048200000000000804, @application_id, NULL, @region_id, @type, '2000000', '0', 25.00, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, NULL),
|
|
(2048200000000000805, @application_id, NULL, @region_id, @type, '4000000', '0', 50.00, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, NULL),
|
|
(2048200000000000806, @application_id, NULL, @region_id, @type, '8000000', '0', 100.00, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, NULL)
|
|
ON DUPLICATE KEY UPDATE
|
|
`application_id` = VALUES(`application_id`),
|
|
`pay_country_id` = VALUES(`pay_country_id`),
|
|
`region_id` = VALUES(`region_id`),
|
|
`type` = VALUES(`type`),
|
|
`content` = VALUES(`content`),
|
|
`award_content` = VALUES(`award_content`),
|
|
`amount_usd` = VALUES(`amount_usd`),
|
|
`is_shelf` = VALUES(`is_shelf`),
|
|
`update_time` = CURRENT_TIMESTAMP,
|
|
`update_user` = VALUES(`update_user`);
|
|
|
|
COMMIT;
|