SqlDev01
12-18-2010, 03:38 PM
From chatting with a server admin, I found that there was no actual scripts to let you handle - from a server level - general data mining into what your users were doing. Using a particular tool that exists you could, of course, look up information about a specific account - but what about those accounts you are not paying attention to? What about those cases where a person may hold 60% of your server networth (created due to plat / tradeskill exploits) on a level 26 shadowknight that is logged out currently in Misty Thicket?
These sql scripts should allow you to get an eye into what is going on in the server as well as wipe out characters plat (and replace with a given amount) if you so choose.
You can get the scripts from http://www.mediafire.com/file/u62jq0sdk73o4qu/General EQEmu Utility SQL v1.7.zip but I am also including the individual files in code blocks here. We have had too many good utilities / scripts die out due to a file host removing old files.
These scripts have been tested out and should work without issue on your server. Be aware that the two scripts marked "HeavyWeight" are expensive queries that should be ran during off peak hours to guarantee low server impact. The update script should be read in its entirety before executing.
NOTE - these scripts are based off of the character platinum slot being found in offset 4720 in the character_ profile blob. I do not see this changing in the immediate future but, if it does, you can simply update the scripts and do a global find / replace for 4720 and replace it with the new platinum offset.
Improvements to be made: I will be writing a new script to handle automated suspending / renaming of characters found using names that are inappropriate for your server. I am currently testing a script that will be used to wipe out characters whose accounts have not bothered logging in in years and meet other criteria (data cleanup). It would be -nice- to move a lot of this logic into a view that will read the profile blob as it will make the scripts less complex. I am not planning on doing this in the next few days so if someone would like to take a stab at this, be my guest.
--
*********************************
* EQEMU SQL Script Explanations *
*********************************
1. PlatSearching_DetailedAnalysis_Offset4720_v1.7.sql - This will provide you with all the liquid platinum a player has access to. This includes plat on their cursor and their shared bank. This will also include a characters tradeskill level. You will also see the account id and account login to easy group players together if they are on the same account. Run this script all at once.
2. PlatSearching_SimplePlatByIPAddress_Offset4720_v1. 7.sql - This will provide you with all the platinum a player has access to throughout all their characters and accounts based on their ip address. If a person logged into five accounts on your server with ip address "255.255.255.255" and these accounts have a sum total of "250,000" plat, you will see a row for IP "255.255.255.255" and IP_ADDRESS_NET_WORTH_IN_PLAT "250000". Important note here - a users IP address can change over time. As such, you may see the same plat amount counted multiple times for different ip address - this is not a bug, this is by design. Example: If a player Bob has 10 accounts which each having about 1 million plat, and Bobs IP address changes every week - assuming Bob logs in at least once a week under a new ip address into all his accounts, you would see a total of 10 rows with different ip addresses listing ~10 million plat. This does not mean that you have that much unique money in the wild - please keep this in mind. It should be true, however, that for a given IP address the plat available is accurate - you simply cannot create new aggregations based on this data. Run this script all at once.
3. PlatSearching_SimplePlatAnalysisWithItemValues_Off set4720_v1.7.sql - This will provide you with all the platinum a player has access to including platinum tied up in tradeable items. This will only count the merchant price of tradeable items so if a user is hording some very lucrative tradeskill items, this may not be caught. This will help with users who do not like the idea of someone watching their plat totals and, instead, try to hide plat in stacks of blue diamonds or the like. It is important to note that items in the shared bank will affect the total plat shown here for all users that are on that account. This means that if you see 5 people with 2 million each, it could simply be that the user has 2 million plat in items in the shared bank accessible across 5 of their characters on that account. If you see a better way to handle this, please let me know. Run this script all at once. Note - this is a heavy weight query and may take several minutes (or longer) to run depending on your character count. It is adviseable to run this during off peak hours (or during maintenance) to guarantee that you will not impact players. Adding an appropriate index may help speed this up but this has not be done yet.
4. PlatUpdate_CharacterMoneyUpdateScript_Offset4720_v 1.7.sql - This will update a given users plat to the new plat amount that you put in. You should run the first section of the file to create the persisted table only one time and then remove it from your update script. Each section (except the first section and the last two sections in the file) should be ran one at a time. You simply put the character id of the user you wish to update into the file as well as their new platinum amount. The default script you see modifies character 2 to have a new plat total of 8,675,309. The section that actually modifies a users data is listed at the bottom surrounded by warnings. The section immediately prior to this will show you what their old plat was, what their new plat would be so you can check to make sure this is what you desire. If you wish to abort at this time, you can simply run the last section in the script to delete these records from your backup table. If you wish to revert, you will need to run the reversion section at the bottom of the script. To be frank, though, it is safer to simply update a user twice than it is to revert. Do -not- delete records from the backup table as this will be invaluable if you need to see how things were at a later point. VERY IMPORTANT NOTE - this needs to be ran either when the server is down for maintenance or when a user is logged out. Updating a user while they are logged in as unpredictable results. From testing, these results appear to be relegated to a user not being fully updated but this is not a guarantee. DO NOT RUN THIS SCRIPT ALL AT ONCE. READ.
5. TradeskillSearching_FindUsersPossiblyAbusingRecipe s_v1.7.sql - finds the users who have created a single recipe combine more than __ times. The script defaults this number to 200 but you can modify this yourself. You can also remove some noise recipes from this script by uncommenting the "and recipe.name not in ..." like and adding in the recipe name you wish to avoid seeing. This script will output the IP Address, AccountID, CharacterID, CharacterName, RecipeName, and the CntMade. NOTE - IP Address was provided in this script to allow you to perform some ad hoc data mining - this will, however, cause some characters to show up multiple times due to the fact that a characters ip address may have changed. If this is alarming, you can simply remove the join to the "account_ip" table and comment out the "aip.IP as IP_ADDRESS" line as well as removing the comma on the second line. Run this script all at once.
6. TradeskillSearching_FindUsersPossiblyAbusingRecipe s_ByIPAddress_v1.7.sql - same as 6 except we are aggregating based on the IP Address. This will show, based on a give ip address, who has performed the most combines for a single recipe. As does 6, this removes anyone who has not made enough recipe combines to matter (based on 200 by default). Also, you are still able to remove recipes from showing up if you have too much noise in your result set. Run this script all at once.
7. Utility_BadCharNameFinder_v1.7.sql - General SQL utility to see what users have names that you consider inappropriate. This will limit the search to accounts that have not been suspended or banned. You can modify the list it uses by updating the temporary table at the top of the page. Run this script all at once.
8. PlatSearching_EconomySize_v1.7.sql - Just gives you information on the entire size of your economy. Total liquid plat, Total locked in plat (sellable items), and Total Combined Character Net Worth. This now takes into account items in the shared bank being counted multiple times and will, now, account for these when viewing the total. Note - this is a heavy weight query and may take several minutes (or longer) to run depending on your character count. It is adviseable to run this during off peak hours (or during maintenance) to guarantee that you will not impact players. Adding an appropriate index may help speed this up but this has not be done yet.
Version 1.7 Files:
1 - LightWeight - PlatSearching_DetailedAnalysis_Offset4720_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
, SKILL_FISHING
, SKILL_MKPOISON
, SKILL_TINKERING
, SKILL_RESEARCH
, SKILL_ALCHEMY
, SKILL_BAKING
, SKILL_TAILORING
, SKILL_BLACKSMITHING
, SKILL_FLETCHING
, SKILL_BREWING
, SKILL_BEGGING
, SKILL_JEWELRY
, SKILL_POTTERY
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
/* Fishing = 55 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (55*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (55*4) + 1)) as SKILL_FISHING
/* Make Poison = 56 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (56*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (56*4) + 1)) as SKILL_MKPOISON
/* Tinkering = 57 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (57*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (57*4) + 1)) as SKILL_TINKERING
/* Research = 58 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (58*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (58*4) + 1)) as SKILL_RESEARCH
/* Alchemy = 59 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (59*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (59*4) + 1)) as SKILL_ALCHEMY
/* Baking = 60 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (60*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (60*4) + 1)) as SKILL_BAKING
/* Tailoring = 61 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (61*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (61*4) + 1)) as SKILL_TAILORING
/* BlackSmithing = 63 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (63*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (63*4) + 1)) as SKILL_BLACKSMITHING
/* Fletching = 64 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (64*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (64*4) + 1)) as SKILL_FLETCHING
/* Brewing = 65 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (65*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (65*4) + 1)) as SKILL_BREWING
/* Begging = 67*/
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (67*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (67*4) + 1)) as SKILL_BEGGING
/* Skills JC = 68*/
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (68*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (68*4) + 1)) as SKILL_JEWELRY
/* Pottery = 69 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (69*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (69*4) + 1)) as SKILL_POTTERY
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
order by CHARACTER_NET_WORTH_IN_PLAT DESC
LIMIT 100;
2 - LightWeight - PlatSearching_SimplePlatByIPAddress_Offset4720_v1. 7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT IP_ADDRESS, SUM(CHARACTER_NET_WORTH_IN_PLAT) AS IP_ADDRESS_NET_WORTH_IN_PLAT
FROM (
SELECT
accIp.IP AS IP_ADDRESS
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
INNER JOIN account_ip accIp
ON accIp.accid = IndividualCharacterSlots.ACCOUNT_ID
) Details
GROUP BY IP_ADDRESS
ORDER BY IP_ADDRESS_NET_WORTH_IN_PLAT DESC
LIMIT 100;
3 - HeavyWeight - PlatSearching_SimplePlatAnalysisWithItemValues_Off set4720_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Creation of temp tables... Doing a lot of work with temp tables
-- as the query optimizer is having issues with the complexity of the query (and the estimated)
-- rows returned. Putting into temp table to persist these concrete values.
-- In a perfect world, it would not be necessary and would add overhead - as it is, it should
-- speed this up considerably.
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS(
ACCOUNT_ID INT
, BagSlotIn2500 INT
, BagSlotIn2501 INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEMS(
ACCOUNT_ID INT
, ITEM_ID INT
, CNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT(
ACCOUNT_ID INT
, SHARED_BANK_ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT(
CHARACTER_ID INT
, ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_TOTAL_PURE_PLAT(
ACCOUNT_ID INT
, ACCOUNT_LOGIN VARCHAR(200)
, CHARACTER_ID INT
, CHARACTER_NAME VARCHAR(200)
, CHARACTER_NET_WORTH_IN_PURE_PLATINUM INT
);
-- Pivoting shared bank bank slot data.
INSERT INTO ACCOUNT_SHARED_BANK_BAG_SLOTS
SELECT account_id, MAX(BagSlotIn2500) AS BagSlotIn2500, MAX(BagSlotIn2501) AS BagSlotIn2501
FROM
(
SELECT
account_id
, CASE WHEN slotid = 2500 THEN IsBag ELSE NULL END AS BagSlotIn2500
, CASE WHEN slotid = 2501 THEN IsBag ELSE NULL END AS BagSlotIn2501
FROM
(
SELECT
acct.id AS account_id
, bagSlots.slotid
, CASE WHEN sb.acctid IS NULL OR i.bagslots < 1 THEN 0 ELSE 1 END AS IsBag
FROM
account acct
CROSS JOIN (SELECT 2500 AS slotid UNION SELECT 2501 ) bagSlots
LEFT JOIN sharedBank sb
ON sb.acctid = acct.id
AND sb.slotid = bagslots.slotid
LEFT JOIN items i
ON i.id = sb.itemid
) unPivotedData
) AS preparingForThePivot
GROUP BY account_id
;
INSERT INTO ACCOUNT_SHARED_BANK_ITEMS
SELECT
account_id, bankitem, SUM(bankitemcharges) AS totalCntItems
FROM
(
SELECT
bankBagSlots.account_id
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN itemid
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN itemid
WHEN slotid BETWEEN 2500 AND 2501 THEN itemid
ELSE NULL
END AS BankItem
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN charges
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN charges
WHEN slotid BETWEEN 2500 AND 2501 THEN charges
ELSE NULL
END AS BankItemCharges
FROM
ACCOUNT_SHARED_BANK_BAG_SLOTS bankBagSlots
INNER JOIN sharedbank sb
ON sb.acctid = bankBagSlots.account_id
) SharedBankItems
WHERE bankitem IS NOT NULL
GROUP BY account_id, bankitem;
INSERT INTO ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT
SELECT
bankItems.account_id
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN bankitems.cnt
ELSE 1
END) * 0.94)/1000
FROM
ACCOUNT_SHARED_BANK_ITEMS bankItems
INNER JOIN items i
ON i.id = bankitems.item_id
GROUP BY bankItems.account_id
;
INSERT INTO CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT
SELECT
groupedCharInv.CHARACTER_ID
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN groupedCharInv.cnt
ELSE 1
END) * 0.94)/1000
FROM
(
SELECT
inv.charid AS CHARACTER_ID
, inv.itemid AS ITEM_ID
, SUM(inv.charges) AS cnt
FROM
inventory inv
GROUP BY
inv.charid, inv.itemid
) groupedCharInv
INNER JOIN items i
ON i.id = groupedCharInv.ITEM_ID
GROUP BY groupedCharInv.CHARACTER_ID;
INSERT INTO CHARACTER_TOTAL_PURE_PLAT
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
;
-- Notes -
-- CHARACTER_TOTAL_PURE_PLAT has a guaranteed 1:1 for each character.
-- ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each account but will have at most 1.
-- CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each character but will have at most 1.
-- Final Aggregation
SELECT
charPurePlat.ACCOUNT_ID
, charPurePlat.ACCOUNT_LOGIN
, charPurePlat.CHARACTER_ID
, charPurePlat.CHARACTER_NAME
, COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_ PLATINUM,0) AS CHARACTER_NET_WORTH_IN_PURE_PLATINUM
, COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0) AS ITEM_VALUE_IN_PLAT
, COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT , 0) AS SHARED_BANK_ITEM_VALUE_IN_PLAT
, COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_ PLATINUM, 0)
+ COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0)
+ COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT , 0) AS TotalCombinedCharacterNetWorth
FROM
CHARACTER_TOTAL_PURE_PLAT charPurePlat
LEFT JOIN CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT charNonSharedItemPlat
ON charNonSharedItemPlat.character_ID = charPurePlat.character_id
LEFT JOIN ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT acctShared
ON acctShared.account_id = charPurePlat.account_id
ORDER BY 8 DESC
LIMIT 100
;
DROP TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEMS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_TOTAL_PURE_PLAT;
;
4 - LightWeight - PlatUpdate_CharacterMoneyUpdateScript_Offset4720_v 1.7.sql
NOTE - reformatted to not wrap on the screen. You may wish to download the zip file to get the correctly formatted text as it may be easier to understand.
/*
Create a "Permanent" backup table of any accounts that have their money wiped.
When you run the "Update Money" procedure, you will be taking -all- accounts
that are in this table where the character_id -AND- original_profile match
and you will be updating it to the new profile. This lets you simply add
new new rows to this table whenever you wish to grant a boon or wipe out
someones plat.
This table should probably not be dropped.
*/
/*
RUN ONCE, KEEP THIS TABLE!
*/
CREATE TABLE character_backup_pre_money_wipe_semi_permanent_tab le(
CHARACTER_ID INT
, ORIGINAL_PROFILE BLOB
, ORIGINAL_MONEY_VALUE INT
, NEW_PROFILE BLOB
, NEW_MONEY_VALUE INT
, DATE_ADDED DATETIME
, DATE_EXECUTED DATETIME
)
;
/*
Insert the character_ids and the money you wish this character to have.
Manual process currently - find the character_ids from another script
and you can insert the rows here.
*/
INSERT INTO character_backup_pre_money_wipe_semi_permanent_tab le(CHARACTER_ID, NEW_MONEY_VALUE, DATE_ADDED)
SELECT 2, 8675309, NOW()
;
/*
!!! DO NOT EDIT BELOW THIS LINE !!!
*/
/*
Lets get the original profile and note what their original money was.
NOTE - this simply updates our -backup- table.
*/
UPDATE character_backup_pre_money_wipe_semi_permanent_tab le, (
SELECT
preMoneyBackup.CHARACTER_ID AS ID
, PROFILE
, FLOOR(MONEY_NOTES.CHARACTER_NET_WORTH_IN_PLAT) AS CHARACTER_NET_WORTH_IN_PLAT
FROM
character_backup_pre_money_wipe_semi_permanent_tab le preMoneyBackup
INNER JOIN character_ c
ON c.id = preMoneyBackup.CHARACTER_ID
INNER JOIN (
SELECT ID
, FLOOR(PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000) AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) d
) MONEY_NOTES
ON c.ID = MONEY_NOTES.ID
) derivedTable
SET ORIGINAL_PROFILE = derivedTable.PROFILE, ORIGINAL_MONEY_VALUE = derivedTable.CHARACTER_NET_WORTH_IN_PLAT
WHERE
character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID = derivedTable.ID
AND ORIGINAL_PROFILE IS NULL
AND DATE_EXECUTED IS NULL
;
/*
For those backup entries that are missing a new profile value, go ahead and create it.
NOTE - this simply updates our -backup- table.
*/
UPDATE
character_backup_pre_money_wipe_semi_permanent_tab le
, (
SELECT d.CHARACTER_ID, CONCAT(CHAR(CHAR_ONE), CHAR(CHAR_TWO), CHAR(CHAR_THREE), CHAR(CHAR_FOUR)) AS newPlat
FROM (
SELECT character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID
,
NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) / (256*256)) * 256 * 256) -
FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) * 256 * 256))/256) * 256 AS CHAR_ONE
, FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) * 256 * 256))/256) AS CHAR_TWO
, FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) AS CHAR_THREE
, FLOOR(NEW_MONEY_VALUE/(256*256*256)) AS CHAR_FOUR
FROM
character_backup_pre_money_wipe_semi_permanent_tab le
WHERE
NEW_PROFILE IS NULL
AND character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED IS NULL
) d
) newCharacterMoneyValues
SET NEW_PROFILE = INSERT(
(SELECT ORIGINAL_PROFILE FROM (SELECT * FROM character_) AS X WHERE X.ID = character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID)
,(4720+1)
,52
,RPAD(newPlat,52,CHAR(0))
)
WHERE
character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID = newCharacterMoneyValues.CHARACTER_ID
AND
character_backup_pre_money_wipe_semi_permanent_tab le.NEW_PROFILE IS NULL
AND character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED IS NULL
;
/*
Lets go ahead and query and make sure that this change makes sense.
Look at the users old networth, the new networth (calculated based
off of the new profile so what you see here is what -will- be going
into the database.
*/
SELECT ID AS CHARACTER_ID
, DATE_ADDED AS DATE_THIS_WIPE_REQUEST_WAS_ADDED
, ORIGINAL_MONEY_VALUE AS ORIGINAL_CHARACTER_NETWORTH
, FLOOR(PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000) AS NEW_CHARACTER_NETWORTH
FROM
(
SELECT
ID
, DATE_ADDED
, ORIGINAL_MONEY_VALUE
/* Player Platinum! */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM
character_backup_pre_money_wipe_semi_permanent_tab le preWipeBackup
INNER JOIN character_ c
ON c.ID = preWipeBackup.CHARACTER_ID
AND preWipeBackup.ORIGINAL_PROFILE = c.PROFILE
AND preWipeBackup.DATE_EXECUTED IS NULL
) MONEY_NOTES
;
/*
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
This is the only part of the script that actually updates the character table.
It will update the character profile to be the new character profile we generated
for those accounts that match based on character id -and- that have the same
old profile that we put into the account. This is just another way to make sure
that we do not have unintended consequences (wiping out a profile based on
old data that is no longer valid)
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
*/
UPDATE character_, character_backup_pre_money_wipe_semi_permanent_tab le, account
SET
character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_tab le.NEW_PROFILE
, character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED = NOW()
, account.sharedplat = 0
WHERE
character_.ID = character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID
AND character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_tab le.ORIGINAL_PROFILE
AND character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED IS NULL
and account.id = character_.account_id
;
/*
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT
You can roll back to previous value by using the ORIGINAL_PROFILE value
in the character_backup_pre_money_wipe_semi_permanent_tab le table.
The next part of the script will simply revert out -all- changes that
is in this wipeout table. If you wish to be more granular, you will
need to hand code a snippet that touches this table. Its a minor change.
If you wish to keep the users profile as it is and simply move them back to the older
money, it will require more consideration.
ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
*/
UPDATE character_, character_backup_pre_money_wipe_semi_permanent_tab le
SET
character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_tab le.ORIGINAL_PROFILE
, character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED = NULL
WHERE
character_.ID = character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID
/*
You may need to remove the following condition if the user has logged into his account
- logging into account would be enough to change the profile blob.
*/
AND character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_tab le.NEW_PROFILE
;
/*
Once you roll back someones update, it may be wise to delete any rows from the backup table that have a null execution date
*/
DELETE FROM character_backup_pre_money_wipe_semi_permanent_tab le WHERE DATE_EXECUTED IS NULL;
;
5 - LightWeight - TradeskillSearching_FindUsersPossiblyAbusingRecipe s_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Filter out noise - people who did not make at least 200 of
-- an item are not interesting to me.
-- Optionally filter out recipes like HoH stones.
-- and recipe.name not in ('carved shadewood recurve bow (hemp)', 'fish fries', 'fake recipe name')
;
SELECT
aip.IP AS IP_ADDRESS
, acc.id AS ACCOUNT_ID
, acc.NAME AS ACCOUNT_LOGIN
, c.id AS CHARACTER_ID
, c.NAME AS CHARACTER_NAME
, recipe.NAME AS RECIPE_NAME
, characterCreatedRecipes.madecount AS CNT_MADE
FROM
char_recipe_list characterCreatedRecipes
INNER JOIN character_ c
ON c.id = characterCreatedRecipes.char_id
INNER JOIN tradeskill_recipe recipe
ON recipe.id = characterCreatedRecipes.recipe_id
INNER JOIN account acc
ON acc.id = c.account_id
INNER JOIN account_ip aip
ON aip.accid = c.account_id
WHERE
characterCreatedRecipes.madecount > 200
ORDER BY
characterCreatedRecipes.madecount DESC
6 - LightWeight - TradeskillSearching_FindUsersPossiblyAbusingRecipe s_ByIPAddress_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Filter out noise - people who did not make at least 200 of
-- an item are not interesting to me.
-- Optionally filter out noise recipes like HoH stones.
-- and recipe.name not in ('carved shadewood recurve bow (hemp)', 'fish fries', 'fake recipe name')
;
SELECT
IP_ADDRESS
, recipe.NAME AS RECIPE_NAME
, CNT_MADE
FROM(
SELECT
aip.IP AS IP_ADDRESS
, characterCreatedRecipes.recipe_id AS RECIPE_ID
, SUM(characterCreatedRecipes.madecount) AS CNT_MADE
FROM
char_recipe_list characterCreatedRecipes
INNER JOIN character_ c
ON c.id = characterCreatedRecipes.char_id
INNER JOIN account acc
ON acc.id = c.account_id
INNER JOIN account_ip aip
ON aip.accid = c.account_id
WHERE
characterCreatedRecipes.madecount > 200
GROUP BY aip.IP, characterCreatedRecipes.recipe_id
) IpCreatedRecipes
INNER JOIN tradeskill_recipe recipe
ON recipe.id = IpCreatedRecipes.RECIPE_ID
ORDER BY
3 DESC
7 - LightWeight - Utility_BadCharNameFinder_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TEMPORARY TABLE CHARACTER_NAME_FINDER(
WORD_IN_NAME VARCHAR(50)
);
INSERT INTO CHARACTER_NAME_FINDER
SELECT 'nipple'
UNION SELECT 'shit'
UNION SELECT 'fuck'
UNION SELECT 'cumming'
UNION SELECT 'poon'
UNION SELECT 'herpes'
UNION SELECT 'yourmom'
UNION SELECT 'your_mom'
UNION SELECT 'dick'
UNION SELECT 'toker'
UNION SELECT 'potbowl'
UNION SELECT 'anus'
UNION SELECT 'balls'
UNION SELECT 'bitch'
UNION SELECT 'nigg'
UNION SELECT 'abort'
UNION SELECT 'pussy'
UNION SELECT 'sex'
UNION SELECT 'suck'
UNION SELECT 'damn'
UNION SELECT 'ass'
UNION SELECT 'cunt'
UNION SELECT 'cock'
UNION SELECT 'penis'
UNION SELECT 'whore'
UNION SELECT 'piss'
;
/* DO NOT EDIT BELOW THIS LINE */
SELECT *
FROM (
SELECT
c.id AS CHARACTER_ID
, TRIM(REPLACE(CAST(SUBSTRING(PROFILE, (4+1), 64) AS CHAR(64)),'\0','')) AS FirstName
, TRIM(REPLACE(CAST(SUBSTRING(PROFILE, (68+1), 32) AS CHAR(64)),'\0','')) AS LastName
, LEVEL
FROM
character_ c
INNER JOIN account acc
ON acc.id = c.account_id
WHERE
acc.STATUS >= 0
) CharacterNames
INNER JOIN CHARACTER_NAME_FINDER filter
ON
LOCATE(WORD_IN_NAME, FirstName) > 0
OR
LOCATE(WORD_IN_NAME, LastName) > 0
ORDER BY CHARACTER_ID desc
LIMIT 100
;
DROP TABLE CHARACTER_NAME_FINDER;
8 - HeavyWeight - PlatSearching_EconomySize_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Creation of temp tables... Doing a lot of work with temp tables
-- as the query optimizer is having issues with the complexity of the query (and the estimated)
-- rows returned. Putting into temp table to persist these concrete values.
-- In a perfect world, it would not be necessary and would add overhead - as it is, it should
-- speed this up considerably.
CREATE TABLE ACCOUNT_CHARACTER_COUNT(
ACCOUNT_ID INT
, NUMBER_OF_CHARACTERS_ON_ACCOUNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS(
ACCOUNT_ID INT
, BagSlotIn2500 INT
, BagSlotIn2501 INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEMS(
ACCOUNT_ID INT
, ITEM_ID INT
, CNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT(
ACCOUNT_ID INT
, SHARED_BANK_ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT(
CHARACTER_ID INT
, ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_TOTAL_PURE_PLAT(
ACCOUNT_ID INT
, ACCOUNT_LOGIN VARCHAR(200)
, CHARACTER_ID INT
, CHARACTER_NAME VARCHAR(200)
, CHARACTER_NET_WORTH_IN_PURE_PLATINUM INT
);
INSERT INTO ACCOUNT_CHARACTER_COUNT
SELECT
acc.id
, COUNT(DISTINCT c.id)
FROM
account acc
LEFT JOIN character_ c
ON acc.id = c.account_id
GROUP BY acc.id;
-- Pivoting shared bank bank slot data.
INSERT INTO ACCOUNT_SHARED_BANK_BAG_SLOTS
SELECT account_id, MAX(BagSlotIn2500) AS BagSlotIn2500, MAX(BagSlotIn2501) AS BagSlotIn2501
FROM
(
SELECT
account_id
, CASE WHEN slotid = 2500 THEN IsBag ELSE NULL END AS BagSlotIn2500
, CASE WHEN slotid = 2501 THEN IsBag ELSE NULL END AS BagSlotIn2501
FROM
(
SELECT
acct.id AS account_id
, bagSlots.slotid
, CASE WHEN sb.acctid IS NULL OR i.bagslots < 1 THEN 0 ELSE 1 END AS IsBag
FROM
account acct
CROSS JOIN (SELECT 2500 AS slotid UNION SELECT 2501 ) bagSlots
LEFT JOIN sharedBank sb
ON sb.acctid = acct.id
AND sb.slotid = bagslots.slotid
LEFT JOIN items i
ON i.id = sb.itemid
) unPivotedData
) AS preparingForThePivot
GROUP BY account_id
;
INSERT INTO ACCOUNT_SHARED_BANK_ITEMS
SELECT
account_id, bankitem, SUM(bankitemcharges) AS totalCntItems
FROM
(
SELECT
bankBagSlots.account_id
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN itemid
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN itemid
WHEN slotid BETWEEN 2500 AND 2501 THEN itemid
ELSE NULL
END AS BankItem
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN charges
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN charges
WHEN slotid BETWEEN 2500 AND 2501 THEN charges
ELSE NULL
END AS BankItemCharges
FROM
ACCOUNT_SHARED_BANK_BAG_SLOTS bankBagSlots
INNER JOIN sharedbank sb
ON sb.acctid = bankBagSlots.account_id
) SharedBankItems
WHERE bankitem IS NOT NULL
GROUP BY account_id, bankitem;
INSERT INTO ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT
SELECT
bankItems.account_id
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN bankitems.cnt
ELSE 1
END) * 0.94)/1000
FROM
ACCOUNT_SHARED_BANK_ITEMS bankItems
INNER JOIN items i
ON i.id = bankitems.item_id
GROUP BY bankItems.account_id
;
INSERT INTO CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT
SELECT
groupedCharInv.CHARACTER_ID
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN groupedCharInv.cnt
ELSE 1
END) * 0.94)/1000
FROM
(
SELECT
inv.charid AS CHARACTER_ID
, inv.itemid AS ITEM_ID
, SUM(inv.charges) AS cnt
FROM
inventory inv
GROUP BY
inv.charid, inv.itemid
) groupedCharInv
INNER JOIN items i
ON i.id = groupedCharInv.ITEM_ID
GROUP BY groupedCharInv.CHARACTER_ID;
INSERT INTO CHARACTER_TOTAL_PURE_PLAT
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
;
-- Notes -
-- CHARACTER_TOTAL_PURE_PLAT has a guaranteed 1:1 for each character.
-- ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each account but will have at most 1.
-- CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each character but will have at most 1.
-- Final Aggregation
SELECT
SUM(CAST(COALESCE(charPurePlat.CHARACTER_NET_WORTH _IN_PURE_PLATINUM,0) AS UNSIGNED)) AS TOTAL_LIQUID_PLATINUM
, SUM(CAST(
COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT ,0) / (
CASE
WHEN acctCharCount.ACCOUNT_ID IS NULL then 1
WHEN acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT = 0 then 1
ELSE acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT
END)
AS UNSIGNED)) AS TOTAL_PLATINUM_LOCKED_IN_SHARED_BANK
, SUM(CAST(COALESCE(charNonSharedItemPlat.ITEM_VALUE _IN_PLAT,0) AS UNSIGNED)) AS TOTAL_PLATINUM_LOCKED_IN_ITEMS
, SUM(CAST( COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_ PLATINUM, 0)
+ COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0)
+ COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT ,0) / (
CASE
WHEN acctCharCount.ACCOUNT_ID IS NULL then 1
WHEN acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT = 0 then 1
ELSE acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT
END) AS UNSIGNED)) AS TotalCombinedWorldNetWorth
FROM
CHARACTER_TOTAL_PURE_PLAT charPurePlat
LEFT JOIN CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT charNonSharedItemPlat
ON charNonSharedItemPlat.character_ID = charPurePlat.character_id
LEFT JOIN ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT acctShared
ON acctShared.account_id = charPurePlat.account_id
LEFT JOIN ACCOUNT_CHARACTER_COUNT acctCharCount
ON acctCharCount.ACCOUNT_ID = acctShared.account_id
;
DROP TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEMS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_TOTAL_PURE_PLAT;
DROP TABLE ACCOUNT_CHARACTER_COUNT;
;
These sql scripts should allow you to get an eye into what is going on in the server as well as wipe out characters plat (and replace with a given amount) if you so choose.
You can get the scripts from http://www.mediafire.com/file/u62jq0sdk73o4qu/General EQEmu Utility SQL v1.7.zip but I am also including the individual files in code blocks here. We have had too many good utilities / scripts die out due to a file host removing old files.
These scripts have been tested out and should work without issue on your server. Be aware that the two scripts marked "HeavyWeight" are expensive queries that should be ran during off peak hours to guarantee low server impact. The update script should be read in its entirety before executing.
NOTE - these scripts are based off of the character platinum slot being found in offset 4720 in the character_ profile blob. I do not see this changing in the immediate future but, if it does, you can simply update the scripts and do a global find / replace for 4720 and replace it with the new platinum offset.
Improvements to be made: I will be writing a new script to handle automated suspending / renaming of characters found using names that are inappropriate for your server. I am currently testing a script that will be used to wipe out characters whose accounts have not bothered logging in in years and meet other criteria (data cleanup). It would be -nice- to move a lot of this logic into a view that will read the profile blob as it will make the scripts less complex. I am not planning on doing this in the next few days so if someone would like to take a stab at this, be my guest.
--
*********************************
* EQEMU SQL Script Explanations *
*********************************
1. PlatSearching_DetailedAnalysis_Offset4720_v1.7.sql - This will provide you with all the liquid platinum a player has access to. This includes plat on their cursor and their shared bank. This will also include a characters tradeskill level. You will also see the account id and account login to easy group players together if they are on the same account. Run this script all at once.
2. PlatSearching_SimplePlatByIPAddress_Offset4720_v1. 7.sql - This will provide you with all the platinum a player has access to throughout all their characters and accounts based on their ip address. If a person logged into five accounts on your server with ip address "255.255.255.255" and these accounts have a sum total of "250,000" plat, you will see a row for IP "255.255.255.255" and IP_ADDRESS_NET_WORTH_IN_PLAT "250000". Important note here - a users IP address can change over time. As such, you may see the same plat amount counted multiple times for different ip address - this is not a bug, this is by design. Example: If a player Bob has 10 accounts which each having about 1 million plat, and Bobs IP address changes every week - assuming Bob logs in at least once a week under a new ip address into all his accounts, you would see a total of 10 rows with different ip addresses listing ~10 million plat. This does not mean that you have that much unique money in the wild - please keep this in mind. It should be true, however, that for a given IP address the plat available is accurate - you simply cannot create new aggregations based on this data. Run this script all at once.
3. PlatSearching_SimplePlatAnalysisWithItemValues_Off set4720_v1.7.sql - This will provide you with all the platinum a player has access to including platinum tied up in tradeable items. This will only count the merchant price of tradeable items so if a user is hording some very lucrative tradeskill items, this may not be caught. This will help with users who do not like the idea of someone watching their plat totals and, instead, try to hide plat in stacks of blue diamonds or the like. It is important to note that items in the shared bank will affect the total plat shown here for all users that are on that account. This means that if you see 5 people with 2 million each, it could simply be that the user has 2 million plat in items in the shared bank accessible across 5 of their characters on that account. If you see a better way to handle this, please let me know. Run this script all at once. Note - this is a heavy weight query and may take several minutes (or longer) to run depending on your character count. It is adviseable to run this during off peak hours (or during maintenance) to guarantee that you will not impact players. Adding an appropriate index may help speed this up but this has not be done yet.
4. PlatUpdate_CharacterMoneyUpdateScript_Offset4720_v 1.7.sql - This will update a given users plat to the new plat amount that you put in. You should run the first section of the file to create the persisted table only one time and then remove it from your update script. Each section (except the first section and the last two sections in the file) should be ran one at a time. You simply put the character id of the user you wish to update into the file as well as their new platinum amount. The default script you see modifies character 2 to have a new plat total of 8,675,309. The section that actually modifies a users data is listed at the bottom surrounded by warnings. The section immediately prior to this will show you what their old plat was, what their new plat would be so you can check to make sure this is what you desire. If you wish to abort at this time, you can simply run the last section in the script to delete these records from your backup table. If you wish to revert, you will need to run the reversion section at the bottom of the script. To be frank, though, it is safer to simply update a user twice than it is to revert. Do -not- delete records from the backup table as this will be invaluable if you need to see how things were at a later point. VERY IMPORTANT NOTE - this needs to be ran either when the server is down for maintenance or when a user is logged out. Updating a user while they are logged in as unpredictable results. From testing, these results appear to be relegated to a user not being fully updated but this is not a guarantee. DO NOT RUN THIS SCRIPT ALL AT ONCE. READ.
5. TradeskillSearching_FindUsersPossiblyAbusingRecipe s_v1.7.sql - finds the users who have created a single recipe combine more than __ times. The script defaults this number to 200 but you can modify this yourself. You can also remove some noise recipes from this script by uncommenting the "and recipe.name not in ..." like and adding in the recipe name you wish to avoid seeing. This script will output the IP Address, AccountID, CharacterID, CharacterName, RecipeName, and the CntMade. NOTE - IP Address was provided in this script to allow you to perform some ad hoc data mining - this will, however, cause some characters to show up multiple times due to the fact that a characters ip address may have changed. If this is alarming, you can simply remove the join to the "account_ip" table and comment out the "aip.IP as IP_ADDRESS" line as well as removing the comma on the second line. Run this script all at once.
6. TradeskillSearching_FindUsersPossiblyAbusingRecipe s_ByIPAddress_v1.7.sql - same as 6 except we are aggregating based on the IP Address. This will show, based on a give ip address, who has performed the most combines for a single recipe. As does 6, this removes anyone who has not made enough recipe combines to matter (based on 200 by default). Also, you are still able to remove recipes from showing up if you have too much noise in your result set. Run this script all at once.
7. Utility_BadCharNameFinder_v1.7.sql - General SQL utility to see what users have names that you consider inappropriate. This will limit the search to accounts that have not been suspended or banned. You can modify the list it uses by updating the temporary table at the top of the page. Run this script all at once.
8. PlatSearching_EconomySize_v1.7.sql - Just gives you information on the entire size of your economy. Total liquid plat, Total locked in plat (sellable items), and Total Combined Character Net Worth. This now takes into account items in the shared bank being counted multiple times and will, now, account for these when viewing the total. Note - this is a heavy weight query and may take several minutes (or longer) to run depending on your character count. It is adviseable to run this during off peak hours (or during maintenance) to guarantee that you will not impact players. Adding an appropriate index may help speed this up but this has not be done yet.
Version 1.7 Files:
1 - LightWeight - PlatSearching_DetailedAnalysis_Offset4720_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
, SKILL_FISHING
, SKILL_MKPOISON
, SKILL_TINKERING
, SKILL_RESEARCH
, SKILL_ALCHEMY
, SKILL_BAKING
, SKILL_TAILORING
, SKILL_BLACKSMITHING
, SKILL_FLETCHING
, SKILL_BREWING
, SKILL_BEGGING
, SKILL_JEWELRY
, SKILL_POTTERY
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
/* Fishing = 55 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (55*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (55*4) + 1)) as SKILL_FISHING
/* Make Poison = 56 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (56*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (56*4) + 1)) as SKILL_MKPOISON
/* Tinkering = 57 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (57*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (57*4) + 1)) as SKILL_TINKERING
/* Research = 58 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (58*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (58*4) + 1)) as SKILL_RESEARCH
/* Alchemy = 59 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (59*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (59*4) + 1)) as SKILL_ALCHEMY
/* Baking = 60 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (60*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (60*4) + 1)) as SKILL_BAKING
/* Tailoring = 61 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (61*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (61*4) + 1)) as SKILL_TAILORING
/* BlackSmithing = 63 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (63*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (63*4) + 1)) as SKILL_BLACKSMITHING
/* Fletching = 64 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (64*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (64*4) + 1)) as SKILL_FLETCHING
/* Brewing = 65 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (65*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (65*4) + 1)) as SKILL_BREWING
/* Begging = 67*/
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (67*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (67*4) + 1)) as SKILL_BEGGING
/* Skills JC = 68*/
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (68*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (68*4) + 1)) as SKILL_JEWELRY
/* Pottery = 69 */
, ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (69*4) + 0))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720 + 76 + 1) + (69*4) + 1)) as SKILL_POTTERY
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
order by CHARACTER_NET_WORTH_IN_PLAT DESC
LIMIT 100;
2 - LightWeight - PlatSearching_SimplePlatByIPAddress_Offset4720_v1. 7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT IP_ADDRESS, SUM(CHARACTER_NET_WORTH_IN_PLAT) AS IP_ADDRESS_NET_WORTH_IN_PLAT
FROM (
SELECT
accIp.IP AS IP_ADDRESS
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
INNER JOIN account_ip accIp
ON accIp.accid = IndividualCharacterSlots.ACCOUNT_ID
) Details
GROUP BY IP_ADDRESS
ORDER BY IP_ADDRESS_NET_WORTH_IN_PLAT DESC
LIMIT 100;
3 - HeavyWeight - PlatSearching_SimplePlatAnalysisWithItemValues_Off set4720_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Creation of temp tables... Doing a lot of work with temp tables
-- as the query optimizer is having issues with the complexity of the query (and the estimated)
-- rows returned. Putting into temp table to persist these concrete values.
-- In a perfect world, it would not be necessary and would add overhead - as it is, it should
-- speed this up considerably.
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS(
ACCOUNT_ID INT
, BagSlotIn2500 INT
, BagSlotIn2501 INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEMS(
ACCOUNT_ID INT
, ITEM_ID INT
, CNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT(
ACCOUNT_ID INT
, SHARED_BANK_ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT(
CHARACTER_ID INT
, ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_TOTAL_PURE_PLAT(
ACCOUNT_ID INT
, ACCOUNT_LOGIN VARCHAR(200)
, CHARACTER_ID INT
, CHARACTER_NAME VARCHAR(200)
, CHARACTER_NET_WORTH_IN_PURE_PLATINUM INT
);
-- Pivoting shared bank bank slot data.
INSERT INTO ACCOUNT_SHARED_BANK_BAG_SLOTS
SELECT account_id, MAX(BagSlotIn2500) AS BagSlotIn2500, MAX(BagSlotIn2501) AS BagSlotIn2501
FROM
(
SELECT
account_id
, CASE WHEN slotid = 2500 THEN IsBag ELSE NULL END AS BagSlotIn2500
, CASE WHEN slotid = 2501 THEN IsBag ELSE NULL END AS BagSlotIn2501
FROM
(
SELECT
acct.id AS account_id
, bagSlots.slotid
, CASE WHEN sb.acctid IS NULL OR i.bagslots < 1 THEN 0 ELSE 1 END AS IsBag
FROM
account acct
CROSS JOIN (SELECT 2500 AS slotid UNION SELECT 2501 ) bagSlots
LEFT JOIN sharedBank sb
ON sb.acctid = acct.id
AND sb.slotid = bagslots.slotid
LEFT JOIN items i
ON i.id = sb.itemid
) unPivotedData
) AS preparingForThePivot
GROUP BY account_id
;
INSERT INTO ACCOUNT_SHARED_BANK_ITEMS
SELECT
account_id, bankitem, SUM(bankitemcharges) AS totalCntItems
FROM
(
SELECT
bankBagSlots.account_id
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN itemid
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN itemid
WHEN slotid BETWEEN 2500 AND 2501 THEN itemid
ELSE NULL
END AS BankItem
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN charges
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN charges
WHEN slotid BETWEEN 2500 AND 2501 THEN charges
ELSE NULL
END AS BankItemCharges
FROM
ACCOUNT_SHARED_BANK_BAG_SLOTS bankBagSlots
INNER JOIN sharedbank sb
ON sb.acctid = bankBagSlots.account_id
) SharedBankItems
WHERE bankitem IS NOT NULL
GROUP BY account_id, bankitem;
INSERT INTO ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT
SELECT
bankItems.account_id
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN bankitems.cnt
ELSE 1
END) * 0.94)/1000
FROM
ACCOUNT_SHARED_BANK_ITEMS bankItems
INNER JOIN items i
ON i.id = bankitems.item_id
GROUP BY bankItems.account_id
;
INSERT INTO CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT
SELECT
groupedCharInv.CHARACTER_ID
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN groupedCharInv.cnt
ELSE 1
END) * 0.94)/1000
FROM
(
SELECT
inv.charid AS CHARACTER_ID
, inv.itemid AS ITEM_ID
, SUM(inv.charges) AS cnt
FROM
inventory inv
GROUP BY
inv.charid, inv.itemid
) groupedCharInv
INNER JOIN items i
ON i.id = groupedCharInv.ITEM_ID
GROUP BY groupedCharInv.CHARACTER_ID;
INSERT INTO CHARACTER_TOTAL_PURE_PLAT
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
;
-- Notes -
-- CHARACTER_TOTAL_PURE_PLAT has a guaranteed 1:1 for each character.
-- ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each account but will have at most 1.
-- CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each character but will have at most 1.
-- Final Aggregation
SELECT
charPurePlat.ACCOUNT_ID
, charPurePlat.ACCOUNT_LOGIN
, charPurePlat.CHARACTER_ID
, charPurePlat.CHARACTER_NAME
, COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_ PLATINUM,0) AS CHARACTER_NET_WORTH_IN_PURE_PLATINUM
, COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0) AS ITEM_VALUE_IN_PLAT
, COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT , 0) AS SHARED_BANK_ITEM_VALUE_IN_PLAT
, COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_ PLATINUM, 0)
+ COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0)
+ COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT , 0) AS TotalCombinedCharacterNetWorth
FROM
CHARACTER_TOTAL_PURE_PLAT charPurePlat
LEFT JOIN CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT charNonSharedItemPlat
ON charNonSharedItemPlat.character_ID = charPurePlat.character_id
LEFT JOIN ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT acctShared
ON acctShared.account_id = charPurePlat.account_id
ORDER BY 8 DESC
LIMIT 100
;
DROP TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEMS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_TOTAL_PURE_PLAT;
;
4 - LightWeight - PlatUpdate_CharacterMoneyUpdateScript_Offset4720_v 1.7.sql
NOTE - reformatted to not wrap on the screen. You may wish to download the zip file to get the correctly formatted text as it may be easier to understand.
/*
Create a "Permanent" backup table of any accounts that have their money wiped.
When you run the "Update Money" procedure, you will be taking -all- accounts
that are in this table where the character_id -AND- original_profile match
and you will be updating it to the new profile. This lets you simply add
new new rows to this table whenever you wish to grant a boon or wipe out
someones plat.
This table should probably not be dropped.
*/
/*
RUN ONCE, KEEP THIS TABLE!
*/
CREATE TABLE character_backup_pre_money_wipe_semi_permanent_tab le(
CHARACTER_ID INT
, ORIGINAL_PROFILE BLOB
, ORIGINAL_MONEY_VALUE INT
, NEW_PROFILE BLOB
, NEW_MONEY_VALUE INT
, DATE_ADDED DATETIME
, DATE_EXECUTED DATETIME
)
;
/*
Insert the character_ids and the money you wish this character to have.
Manual process currently - find the character_ids from another script
and you can insert the rows here.
*/
INSERT INTO character_backup_pre_money_wipe_semi_permanent_tab le(CHARACTER_ID, NEW_MONEY_VALUE, DATE_ADDED)
SELECT 2, 8675309, NOW()
;
/*
!!! DO NOT EDIT BELOW THIS LINE !!!
*/
/*
Lets get the original profile and note what their original money was.
NOTE - this simply updates our -backup- table.
*/
UPDATE character_backup_pre_money_wipe_semi_permanent_tab le, (
SELECT
preMoneyBackup.CHARACTER_ID AS ID
, PROFILE
, FLOOR(MONEY_NOTES.CHARACTER_NET_WORTH_IN_PLAT) AS CHARACTER_NET_WORTH_IN_PLAT
FROM
character_backup_pre_money_wipe_semi_permanent_tab le preMoneyBackup
INNER JOIN character_ c
ON c.id = preMoneyBackup.CHARACTER_ID
INNER JOIN (
SELECT ID
, FLOOR(PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000) AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) d
) MONEY_NOTES
ON c.ID = MONEY_NOTES.ID
) derivedTable
SET ORIGINAL_PROFILE = derivedTable.PROFILE, ORIGINAL_MONEY_VALUE = derivedTable.CHARACTER_NET_WORTH_IN_PLAT
WHERE
character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID = derivedTable.ID
AND ORIGINAL_PROFILE IS NULL
AND DATE_EXECUTED IS NULL
;
/*
For those backup entries that are missing a new profile value, go ahead and create it.
NOTE - this simply updates our -backup- table.
*/
UPDATE
character_backup_pre_money_wipe_semi_permanent_tab le
, (
SELECT d.CHARACTER_ID, CONCAT(CHAR(CHAR_ONE), CHAR(CHAR_TWO), CHAR(CHAR_THREE), CHAR(CHAR_FOUR)) AS newPlat
FROM (
SELECT character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID
,
NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) / (256*256)) * 256 * 256) -
FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) * 256 * 256))/256) * 256 AS CHAR_ONE
, FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256) -
(FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) * 256 * 256))/256) AS CHAR_TWO
, FLOOR((NEW_MONEY_VALUE -
(FLOOR(NEW_MONEY_VALUE/(256*256*256)) * 256*256*256)) /
(256*256)) AS CHAR_THREE
, FLOOR(NEW_MONEY_VALUE/(256*256*256)) AS CHAR_FOUR
FROM
character_backup_pre_money_wipe_semi_permanent_tab le
WHERE
NEW_PROFILE IS NULL
AND character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED IS NULL
) d
) newCharacterMoneyValues
SET NEW_PROFILE = INSERT(
(SELECT ORIGINAL_PROFILE FROM (SELECT * FROM character_) AS X WHERE X.ID = character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID)
,(4720+1)
,52
,RPAD(newPlat,52,CHAR(0))
)
WHERE
character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID = newCharacterMoneyValues.CHARACTER_ID
AND
character_backup_pre_money_wipe_semi_permanent_tab le.NEW_PROFILE IS NULL
AND character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED IS NULL
;
/*
Lets go ahead and query and make sure that this change makes sense.
Look at the users old networth, the new networth (calculated based
off of the new profile so what you see here is what -will- be going
into the database.
*/
SELECT ID AS CHARACTER_ID
, DATE_ADDED AS DATE_THIS_WIPE_REQUEST_WAS_ADDED
, ORIGINAL_MONEY_VALUE AS ORIGINAL_CHARACTER_NETWORTH
, FLOOR(PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000) AS NEW_CHARACTER_NETWORTH
FROM
(
SELECT
ID
, DATE_ADDED
, ORIGINAL_MONEY_VALUE
/* Player Platinum! */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(NEW_PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM
character_backup_pre_money_wipe_semi_permanent_tab le preWipeBackup
INNER JOIN character_ c
ON c.ID = preWipeBackup.CHARACTER_ID
AND preWipeBackup.ORIGINAL_PROFILE = c.PROFILE
AND preWipeBackup.DATE_EXECUTED IS NULL
) MONEY_NOTES
;
/*
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
This is the only part of the script that actually updates the character table.
It will update the character profile to be the new character profile we generated
for those accounts that match based on character id -and- that have the same
old profile that we put into the account. This is just another way to make sure
that we do not have unintended consequences (wiping out a profile based on
old data that is no longer valid)
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
*/
UPDATE character_, character_backup_pre_money_wipe_semi_permanent_tab le, account
SET
character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_tab le.NEW_PROFILE
, character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED = NOW()
, account.sharedplat = 0
WHERE
character_.ID = character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID
AND character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_tab le.ORIGINAL_PROFILE
AND character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED IS NULL
and account.id = character_.account_id
;
/*
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT
You can roll back to previous value by using the ORIGINAL_PROFILE value
in the character_backup_pre_money_wipe_semi_permanent_tab le table.
The next part of the script will simply revert out -all- changes that
is in this wipeout table. If you wish to be more granular, you will
need to hand code a snippet that touches this table. Its a minor change.
If you wish to keep the users profile as it is and simply move them back to the older
money, it will require more consideration.
ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT ROLLBACK SCRIPT
!! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !! WARNING !!
*/
UPDATE character_, character_backup_pre_money_wipe_semi_permanent_tab le
SET
character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_tab le.ORIGINAL_PROFILE
, character_backup_pre_money_wipe_semi_permanent_tab le.DATE_EXECUTED = NULL
WHERE
character_.ID = character_backup_pre_money_wipe_semi_permanent_tab le.CHARACTER_ID
/*
You may need to remove the following condition if the user has logged into his account
- logging into account would be enough to change the profile blob.
*/
AND character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_tab le.NEW_PROFILE
;
/*
Once you roll back someones update, it may be wise to delete any rows from the backup table that have a null execution date
*/
DELETE FROM character_backup_pre_money_wipe_semi_permanent_tab le WHERE DATE_EXECUTED IS NULL;
;
5 - LightWeight - TradeskillSearching_FindUsersPossiblyAbusingRecipe s_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Filter out noise - people who did not make at least 200 of
-- an item are not interesting to me.
-- Optionally filter out recipes like HoH stones.
-- and recipe.name not in ('carved shadewood recurve bow (hemp)', 'fish fries', 'fake recipe name')
;
SELECT
aip.IP AS IP_ADDRESS
, acc.id AS ACCOUNT_ID
, acc.NAME AS ACCOUNT_LOGIN
, c.id AS CHARACTER_ID
, c.NAME AS CHARACTER_NAME
, recipe.NAME AS RECIPE_NAME
, characterCreatedRecipes.madecount AS CNT_MADE
FROM
char_recipe_list characterCreatedRecipes
INNER JOIN character_ c
ON c.id = characterCreatedRecipes.char_id
INNER JOIN tradeskill_recipe recipe
ON recipe.id = characterCreatedRecipes.recipe_id
INNER JOIN account acc
ON acc.id = c.account_id
INNER JOIN account_ip aip
ON aip.accid = c.account_id
WHERE
characterCreatedRecipes.madecount > 200
ORDER BY
characterCreatedRecipes.madecount DESC
6 - LightWeight - TradeskillSearching_FindUsersPossiblyAbusingRecipe s_ByIPAddress_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Filter out noise - people who did not make at least 200 of
-- an item are not interesting to me.
-- Optionally filter out noise recipes like HoH stones.
-- and recipe.name not in ('carved shadewood recurve bow (hemp)', 'fish fries', 'fake recipe name')
;
SELECT
IP_ADDRESS
, recipe.NAME AS RECIPE_NAME
, CNT_MADE
FROM(
SELECT
aip.IP AS IP_ADDRESS
, characterCreatedRecipes.recipe_id AS RECIPE_ID
, SUM(characterCreatedRecipes.madecount) AS CNT_MADE
FROM
char_recipe_list characterCreatedRecipes
INNER JOIN character_ c
ON c.id = characterCreatedRecipes.char_id
INNER JOIN account acc
ON acc.id = c.account_id
INNER JOIN account_ip aip
ON aip.accid = c.account_id
WHERE
characterCreatedRecipes.madecount > 200
GROUP BY aip.IP, characterCreatedRecipes.recipe_id
) IpCreatedRecipes
INNER JOIN tradeskill_recipe recipe
ON recipe.id = IpCreatedRecipes.RECIPE_ID
ORDER BY
3 DESC
7 - LightWeight - Utility_BadCharNameFinder_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TEMPORARY TABLE CHARACTER_NAME_FINDER(
WORD_IN_NAME VARCHAR(50)
);
INSERT INTO CHARACTER_NAME_FINDER
SELECT 'nipple'
UNION SELECT 'shit'
UNION SELECT 'fuck'
UNION SELECT 'cumming'
UNION SELECT 'poon'
UNION SELECT 'herpes'
UNION SELECT 'yourmom'
UNION SELECT 'your_mom'
UNION SELECT 'dick'
UNION SELECT 'toker'
UNION SELECT 'potbowl'
UNION SELECT 'anus'
UNION SELECT 'balls'
UNION SELECT 'bitch'
UNION SELECT 'nigg'
UNION SELECT 'abort'
UNION SELECT 'pussy'
UNION SELECT 'sex'
UNION SELECT 'suck'
UNION SELECT 'damn'
UNION SELECT 'ass'
UNION SELECT 'cunt'
UNION SELECT 'cock'
UNION SELECT 'penis'
UNION SELECT 'whore'
UNION SELECT 'piss'
;
/* DO NOT EDIT BELOW THIS LINE */
SELECT *
FROM (
SELECT
c.id AS CHARACTER_ID
, TRIM(REPLACE(CAST(SUBSTRING(PROFILE, (4+1), 64) AS CHAR(64)),'\0','')) AS FirstName
, TRIM(REPLACE(CAST(SUBSTRING(PROFILE, (68+1), 32) AS CHAR(64)),'\0','')) AS LastName
, LEVEL
FROM
character_ c
INNER JOIN account acc
ON acc.id = c.account_id
WHERE
acc.STATUS >= 0
) CharacterNames
INNER JOIN CHARACTER_NAME_FINDER filter
ON
LOCATE(WORD_IN_NAME, FirstName) > 0
OR
LOCATE(WORD_IN_NAME, LastName) > 0
ORDER BY CHARACTER_ID desc
LIMIT 100
;
DROP TABLE CHARACTER_NAME_FINDER;
8 - HeavyWeight - PlatSearching_EconomySize_v1.7.sql
-- Go ahead and force read uncommited so we do not lock player tables while users are playing...
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Creation of temp tables... Doing a lot of work with temp tables
-- as the query optimizer is having issues with the complexity of the query (and the estimated)
-- rows returned. Putting into temp table to persist these concrete values.
-- In a perfect world, it would not be necessary and would add overhead - as it is, it should
-- speed this up considerably.
CREATE TABLE ACCOUNT_CHARACTER_COUNT(
ACCOUNT_ID INT
, NUMBER_OF_CHARACTERS_ON_ACCOUNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS(
ACCOUNT_ID INT
, BagSlotIn2500 INT
, BagSlotIn2501 INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEMS(
ACCOUNT_ID INT
, ITEM_ID INT
, CNT INT
);
CREATE TEMPORARY TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT(
ACCOUNT_ID INT
, SHARED_BANK_ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT(
CHARACTER_ID INT
, ITEM_VALUE_IN_PLAT INT
);
CREATE TEMPORARY TABLE CHARACTER_TOTAL_PURE_PLAT(
ACCOUNT_ID INT
, ACCOUNT_LOGIN VARCHAR(200)
, CHARACTER_ID INT
, CHARACTER_NAME VARCHAR(200)
, CHARACTER_NET_WORTH_IN_PURE_PLATINUM INT
);
INSERT INTO ACCOUNT_CHARACTER_COUNT
SELECT
acc.id
, COUNT(DISTINCT c.id)
FROM
account acc
LEFT JOIN character_ c
ON acc.id = c.account_id
GROUP BY acc.id;
-- Pivoting shared bank bank slot data.
INSERT INTO ACCOUNT_SHARED_BANK_BAG_SLOTS
SELECT account_id, MAX(BagSlotIn2500) AS BagSlotIn2500, MAX(BagSlotIn2501) AS BagSlotIn2501
FROM
(
SELECT
account_id
, CASE WHEN slotid = 2500 THEN IsBag ELSE NULL END AS BagSlotIn2500
, CASE WHEN slotid = 2501 THEN IsBag ELSE NULL END AS BagSlotIn2501
FROM
(
SELECT
acct.id AS account_id
, bagSlots.slotid
, CASE WHEN sb.acctid IS NULL OR i.bagslots < 1 THEN 0 ELSE 1 END AS IsBag
FROM
account acct
CROSS JOIN (SELECT 2500 AS slotid UNION SELECT 2501 ) bagSlots
LEFT JOIN sharedBank sb
ON sb.acctid = acct.id
AND sb.slotid = bagslots.slotid
LEFT JOIN items i
ON i.id = sb.itemid
) unPivotedData
) AS preparingForThePivot
GROUP BY account_id
;
INSERT INTO ACCOUNT_SHARED_BANK_ITEMS
SELECT
account_id, bankitem, SUM(bankitemcharges) AS totalCntItems
FROM
(
SELECT
bankBagSlots.account_id
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN itemid
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN itemid
WHEN slotid BETWEEN 2500 AND 2501 THEN itemid
ELSE NULL
END AS BankItem
, CASE
WHEN bankBagSlots.BagSlotIn2500 = 1 AND slotid BETWEEN 2531 AND 2540 THEN charges
WHEN bankBagSlots.BagSlotIn2501 = 1 AND slotid BETWEEN 2541 AND 2550 THEN charges
WHEN slotid BETWEEN 2500 AND 2501 THEN charges
ELSE NULL
END AS BankItemCharges
FROM
ACCOUNT_SHARED_BANK_BAG_SLOTS bankBagSlots
INNER JOIN sharedbank sb
ON sb.acctid = bankBagSlots.account_id
) SharedBankItems
WHERE bankitem IS NOT NULL
GROUP BY account_id, bankitem;
INSERT INTO ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT
SELECT
bankItems.account_id
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN bankitems.cnt
ELSE 1
END) * 0.94)/1000
FROM
ACCOUNT_SHARED_BANK_ITEMS bankItems
INNER JOIN items i
ON i.id = bankitems.item_id
GROUP BY bankItems.account_id
;
INSERT INTO CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT
SELECT
groupedCharInv.CHARACTER_ID
, (SUM(i.price * CASE
WHEN i.maxcharges >= 1 THEN 1
WHEN i.nodrop = 0 THEN 0
WHEN i.stacksize > 0 THEN groupedCharInv.cnt
ELSE 1
END) * 0.94)/1000
FROM
(
SELECT
inv.charid AS CHARACTER_ID
, inv.itemid AS ITEM_ID
, SUM(inv.charges) AS cnt
FROM
inventory inv
GROUP BY
inv.charid, inv.itemid
) groupedCharInv
INNER JOIN items i
ON i.id = groupedCharInv.ITEM_ID
GROUP BY groupedCharInv.CHARACTER_ID;
INSERT INTO CHARACTER_TOTAL_PURE_PLAT
SELECT * FROM (
SELECT
ACCOUNT_ID
, ACC.NAME AS ACCOUNT_LOGIN
, CHARACTER_ID
, CHARACTER_NAME
,
PLAYER_PLATINUM + BANK_PLATINUM + CURSOR_PLATINUM + SHARED_PLATINUM
+ (PLAYER_GOLD + BANK_GOLD + CURSOR_GOLD) / 10
+ (PLAYER_SILVER + BANK_SILVER + CURSOR_SILVER) / 100
+ (PLAYER_COPPER + BANK_COPPER + CURSOR_COPPER) / 1000 AS CHARACTER_NET_WORTH_IN_PLAT
FROM
(
SELECT
ID AS CHARACTER_ID
,NAME AS CHARACTER_NAME
,ACCOUNT_ID
/* Player Platinum! */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(0*4)+3,1)) AS PLAYER_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(1*4)+3,1)) AS PLAYER_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(2*4)+3,1)) AS PLAYER_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(3*4)+3,1)) AS PLAYER_COPPER
/* Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(4*4)+3,1)) AS BANK_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(5*4)+3,1)) AS BANK_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(6*4)+3,1)) AS BANK_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(7*4)+3,1)) AS BANK_COPPER
/* Cursor Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(8*4)+3,1)) AS CURSOR_PLATINUM
,ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(9*4)+3,1)) AS CURSOR_GOLD
,ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(10*4)+3,1)) AS CURSOR_SILVER
,ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(11*4)+3,1)) AS CURSOR_COPPER
/* Shared Bank Platinum */
,ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+0,1))
+ 256 * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+1,1))
+ (256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+2,1))
+ (256 * 256 * 256) * ASCII(SUBSTRING(PROFILE, (4720+1)+(12*4)+3,1)) AS SHARED_PLATINUM
FROM character_
) IndividualCharacterSlots
INNER JOIN ACCOUNT ACC
ON ACC.ID = IndividualCharacterSlots.ACCOUNT_ID
) Details
;
-- Notes -
-- CHARACTER_TOTAL_PURE_PLAT has a guaranteed 1:1 for each character.
-- ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each account but will have at most 1.
-- CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT is not guaranteed 1:1 for each character but will have at most 1.
-- Final Aggregation
SELECT
SUM(CAST(COALESCE(charPurePlat.CHARACTER_NET_WORTH _IN_PURE_PLATINUM,0) AS UNSIGNED)) AS TOTAL_LIQUID_PLATINUM
, SUM(CAST(
COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT ,0) / (
CASE
WHEN acctCharCount.ACCOUNT_ID IS NULL then 1
WHEN acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT = 0 then 1
ELSE acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT
END)
AS UNSIGNED)) AS TOTAL_PLATINUM_LOCKED_IN_SHARED_BANK
, SUM(CAST(COALESCE(charNonSharedItemPlat.ITEM_VALUE _IN_PLAT,0) AS UNSIGNED)) AS TOTAL_PLATINUM_LOCKED_IN_ITEMS
, SUM(CAST( COALESCE(charPurePlat.CHARACTER_NET_WORTH_IN_PURE_ PLATINUM, 0)
+ COALESCE(charNonSharedItemPlat.ITEM_VALUE_IN_PLAT, 0)
+ COALESCE(acctShared.SHARED_BANK_ITEM_VALUE_IN_PLAT ,0) / (
CASE
WHEN acctCharCount.ACCOUNT_ID IS NULL then 1
WHEN acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT = 0 then 1
ELSE acctCharCount.NUMBER_OF_CHARACTERS_ON_ACCOUNT
END) AS UNSIGNED)) AS TotalCombinedWorldNetWorth
FROM
CHARACTER_TOTAL_PURE_PLAT charPurePlat
LEFT JOIN CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT charNonSharedItemPlat
ON charNonSharedItemPlat.character_ID = charPurePlat.character_id
LEFT JOIN ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT acctShared
ON acctShared.account_id = charPurePlat.account_id
LEFT JOIN ACCOUNT_CHARACTER_COUNT acctCharCount
ON acctCharCount.ACCOUNT_ID = acctShared.account_id
;
DROP TABLE ACCOUNT_SHARED_BANK_BAG_SLOTS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEMS;
DROP TABLE ACCOUNT_SHARED_BANK_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_NON_SHARED_ITEM_VALUE_IN_PLAT;
DROP TABLE CHARACTER_TOTAL_PURE_PLAT;
DROP TABLE ACCOUNT_CHARACTER_COUNT;
;