Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save karlbunch/7569bb444418ba9fd4ccbcfd9c5d76a7 to your computer and use it in GitHub Desktop.
Save karlbunch/7569bb444418ba9fd4ccbcfd9c5d76a7 to your computer and use it in GitHub Desktop.
-- BEGIN CURRENCY CONVERSION
-- Convert character tokens/currencies from 3.x to 4.x
-- http://web.archive.org/web/20100918101232/http://forums.worldofwarcraft.com/thread.html?topicId=26850245012&sid=1&pageNo=10
SET @HONOR_RATE = 0.024;
SET @ARENA_RATE = 0.85;
SET @HONOR_HARDCAP = 4000;
SET @HONOR_TO_MONEY = 35;
SET @JUSTICE_HARDCAP = 4000;
SET @JUSTICE_TO_MONEY = 20000;
SET @MAX_MONEY_AMOUNT = 9999999999;
SET @CURRENCY_TYPE_HONOR_POINTS = 392;
SET @CURRENCY_TYPE_JUSTICE_POINTS = 395;
DROP TABLE IF EXISTS temp_token_to_money
;
CREATE TEMPORARY TABLE temp_token_to_money (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, moneyRate INT(10) UNSIGNED)
;
INSERT INTO temp_token_to_money VALUES
(40752, 55000), -- Emblem of Heroism
(40735, 55000), -- Emblem of Valor
(45624, 55000), -- Emblem of Conquest
(29434, 18330) -- Badge of Justice
;
DROP TABLE IF EXISTS temp_token_to_honor
;
CREATE TEMPORARY TABLE temp_token_to_honor (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, honorRate FLOAT(6))
;
INSERT INTO temp_token_to_honor VALUES
(20558, 2.976), -- Warsong Gulch Mark of Honor
(20559, 2.976), -- Arathi Basin Mark of Honor
(20560, 2.976), -- Alterac Valley Mark of Honor
(29024, 2.976), -- Eye of the Storm Mark of Honor
(42425, 2.976), -- Strand of the Ancients Mark of Honor
(43589, 19.08), -- Wintergrasp Mark of Honor
(43228, 1.600), -- Stone Keeper's Shard
(37836, 3.000), -- Venture Coin
(28558, 1.000) -- Spirit Shard
;
DROP TABLE IF EXISTS temp_token_to_justice
;
CREATE TEMPORARY TABLE temp_token_to_justice (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY, justiceRate FLOAT(6))
;
INSERT INTO temp_token_to_justice VALUES
(47241, 2.75), -- Emblem of Triumph
(49426, 2.75) -- Emblem of Frost
;
DROP TABLE IF EXISTS temp_all_tokens
;
CREATE TEMPORARY TABLE temp_all_tokens (itemEntry MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY)
SELECT t1.itemEntry FROM temp_token_to_money t1
UNION ALL
SELECT t2.itemEntry FROM temp_token_to_honor t2
UNION ALL
SELECT t3.itemEntry FROM temp_token_to_justice t3
;
DROP TABLE IF EXISTS temp_currency_tokens
;
CREATE TEMPORARY TABLE temp_currency_tokens (
`item_guid` int(10) unsigned NOT NULL DEFAULT '0',
`itemEntry` mediumint(8) unsigned NOT NULL DEFAULT '0',
`item_count` int(10) unsigned NOT NULL DEFAULT '1',
`owner_guid` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`item_guid`),
KEY `idx_owner_guid` (`owner_guid`),
KEY `idx_itemEntry` (`itemEntry`)
)
;
INSERT INTO temp_currency_tokens
SELECT ii.`guid`, ii.`itemEntry`, ii.`count`, ii.`owner_guid`
FROM item_instance ii, temp_all_tokens r
WHERE r.itemEntry = ii.itemEntry
;
DROP TABLE IF EXISTS temp_characters_updates
;
CREATE TEMPORARY TABLE temp_characters_updates (guid INT(10) UNSIGNED NOT NULL PRIMARY KEY, moneyDelta INT(10) UNSIGNED, newHonor INT(10) UNSIGNED, newJustice INT(10) UNSIGNED)
SELECT guid, money AS moneyDelta, 0 AS newHonor, 0 AS newJustice
FROM characters
;
UPDATE temp_characters_updates u
SET u.moneyDelta = (SELECT IFNULL(SUM(r.moneyRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_money r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
;
UPDATE temp_characters_updates u
SET u.newHonor = (SELECT IFNULL(SUM(r.honorRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_honor r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
;
UPDATE temp_characters_updates u
SET u.newJustice = (SELECT IFNULL(SUM(r.justiceRate * i.item_count), 0) FROM temp_currency_tokens i JOIN temp_token_to_justice r ON r.itemEntry = i.itemEntry WHERE i.owner_guid = u.guid)
;
UPDATE temp_characters_updates u, characters c
SET u.newHonor = u.newHonor + IFNULL(c.totalHonorPoints * @HONOR_RATE, 0) + IFNULL(c.arenaPoints * @ARENA_RATE, 0)
WHERE c.guid = u.guid
;
UPDATE temp_characters_updates u
SET u.moneyDelta = u.moneyDelta + (u.newHonor - @HONOR_HARDCAP) * @HONOR_TO_MONEY, u.newHonor = @HONOR_HARDCAP
WHERE u.newHonor > @HONOR_HARDCAP
;
UPDATE temp_characters_updates u
SET u.moneyDelta = u.moneyDelta + (u.newJustice - @JUSTICE_HARDCAP) * @JUSTICE_TO_MONEY, u.newJustice = @JUSTICE_HARDCAP
WHERE u.newJustice > @JUSTICE_HARDCAP
;
SELECT c.guid, c.name, c.totalHonorPoints, c.arenaPoints, c.money AS `currentMoney`, '=>', (c.money + u.moneyDelta) AS `newMoney`, u.moneyDelta, u.newHonor, u.newJustice
FROM characters c, temp_characters_updates u
WHERE c.guid = u.guid
ORDER BY 7 DESC
LIMIT 20
;
DROP TABLE IF EXISTS `character_currency`
;
CREATE TABLE `character_currency` (
`guid` int(10) unsigned NOT NULL,
`currency` smallint(5) unsigned NOT NULL,
`total_count` int(10) unsigned NOT NULL,
`week_count` int(10) unsigned NOT NULL,
PRIMARY KEY (`guid`,`currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
START TRANSACTION
;
INSERT INTO `character_currency`
SELECT u.guid, @CURRENCY_TYPE_HONOR_POINTS, u.newHonor, 0
FROM temp_characters_updates u
WHERE u.newHonor > 0
;
INSERT INTO `character_currency`
SELECT u.guid, @CURRENCY_TYPE_JUSTICE_POINTS, u.newJustice, 0
FROM temp_characters_updates u
WHERE u.newHonor > 0
;
UPDATE characters c, temp_characters_updates u
SET c.money = c.money + u.moneyDelta
WHERE c.guid = u.guid
;
DELETE FROM character_inventory
WHERE item IN (SELECT item_guid FROM temp_currency_tokens)
;
DELETE FROM item_instance
WHERE guid IN (SELECT item_guid FROM temp_currency_tokens)
;
COMMIT
;
DROP TABLE temp_token_to_money
;
DROP TABLE temp_token_to_honor
;
DROP TABLE temp_token_to_justice
;
DROP TABLE temp_all_tokens
;
DROP TABLE temp_currency_tokens
;
DROP TABLE temp_characters_updates
;
-- END CURRENCY CONVERSION
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment