2017_03_19_characters.sql

-- BEGIN CURRENCY TOKEN FIXUP
DROP TABLE IF EXISTS temp_currency_items;
CREATE TEMPORARY TABLE temp_currency_items (itemEntry mediumint(8) unsigned);
INSERT INTO temp_currency_items VALUES
-- Add any currency items that need conversion to this list...
(20558),(20559),(20560),(29024),(29434),(37836),(40752),(40753),(41596),(42425),(43016),(43228),(43308),(43589),(44990),(45624),(47395),(47241),(49426),(43949),(41749),(38644),(37742),(37711)
;
DROP FUNCTION IF EXISTS temp_check_currency;
DROP PROCEDURE IF EXISTS temp_currency_item_report;
DROP PROCEDURE IF EXISTS temp_fix_currency;
DELIMITER ;;
CREATE FUNCTION temp_check_currency() RETURNS INT UNSIGNED
BEGIN
   DECLARE bad_count INT UNSIGNED;
   SELECT COUNT(*) INTO bad_count
     FROM character_inventory i
     JOIN item_instance ii ON ii.guid = i.item
    WHERE NOT (bag = 0 AND slot BETWEEN 118 and 150)
          AND ii.itemEntry IN (SELECT itemEntry FROM temp_currency_items);
    RETURN bad_count;
END
;;
CREATE PROCEDURE temp_currency_item_report(IN msg TEXT)
BEGIN
   SELECT msg as `Message`, ii.itemEntry as `Item`, COUNT(*) as `Count Bad Tokens`
     FROM character_inventory i
     JOIN item_instance ii ON ii.guid = i.item
    WHERE NOT (bag = 0 AND slot BETWEEN 118 and 150)
          AND ii.itemEntry IN (SELECT itemEntry FROM temp_currency_items)
    GROUP BY ii.itemEntry;
END
;;
CREATE PROCEDURE temp_fix_currency()
main:BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE bad_count INT UNSIGNED;
    DECLARE character_guid, item_guid INT(10) UNSIGNED;
    DECLARE next_slot TINYINT(3) UNSIGNED;
    DECLARE cur1 CURSOR FOR SELECT i.guid, i.item FROM character_inventory i JOIN item_instance ii ON ii.guid = i.item
                             WHERE NOT (i.bag = 0 AND i.slot BETWEEN 118 AND 150)
                               AND ii.itemEntry IN (SELECT itemEntry FROM temp_currency_items);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur1;

    SELECT temp_check_currency() INTO bad_count;

    IF bad_count = 0 THEN
        SELECT 'All currencies are already properly placed in hidden slots 118-150.' as `Currency Fix Status:`;
        LEAVE main;
    END IF;

    fetch_loop: LOOP
        FETCH cur1 INTO character_guid, item_guid;

        IF done THEN
          LEAVE fetch_loop;
        END IF;
        
        SELECT IFNULL(MAX(slot)+1,118) INTO next_slot FROM character_inventory WHERE guid = character_guid AND bag = 0 AND slot >= 118;

        UPDATE character_inventory
          SET bag = 0, slot = next_slot
             WHERE guid = character_guid AND item = item_guid; 
        END LOOP;
         
        CLOSE cur1;

    SELECT temp_check_currency() INTO bad_count;

    IF bad_count > 0 THEN
        SELECT 'WARNING: Some currency tokens where not properly placed in hidden slots 118-150!' as `Currency Fix Status:`;
        CALL temp_currency_item_report('WARNING: Did not move:');
    ELSE
        SELECT 'SUCCESS: All currency tokens where properly placed in hidden slots 118-150.' as `Currency Fix Status:`;
    END IF;
END
;;
DELIMITER ;

CALL temp_fix_currency();

DROP FUNCTION temp_check_currency;
DROP PROCEDURE temp_currency_item_report;
DROP PROCEDURE temp_fix_currency;
-- END CURRENCY TOKEN FIXUP