characters_335a_to_434_convert_currency.sql

-- 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