Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Tools
Reload this Page General EQEmu SQL Utility Scripts (plat exploit finder, etc)

Development::Tools 3rd Party Tools for EQEMu (DB management tools, front ends, etc...)

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 12-18-2010, 03:38 PM
SqlDev01
Fire Beetle
  
Join Date: Dec 2010
Location: N/A
Posts: 5
Default General EQEmu SQL Utility Scripts (plat exploit finder, etc)
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/u62jq0...y 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
Code:
-- 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
Code:
-- 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
Code:
-- 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.
Code:
/*
	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_table(
	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_table(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_table, (
	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_table 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_table.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_table
	,  (
		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_table.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_table
			WHERE 
				NEW_PROFILE IS NULL
				AND character_backup_pre_money_wipe_semi_permanent_table.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_table.CHARACTER_ID)
	,(4720+1)
	,52
	,RPAD(newPlat,52,CHAR(0))
) 
WHERE 
	character_backup_pre_money_wipe_semi_permanent_table.CHARACTER_ID = newCharacterMoneyValues.CHARACTER_ID
	AND
	character_backup_pre_money_wipe_semi_permanent_table.NEW_PROFILE IS NULL
	AND character_backup_pre_money_wipe_semi_permanent_table.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_table 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_table, account
SET 
	character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_table.NEW_PROFILE
	, character_backup_pre_money_wipe_semi_permanent_table.DATE_EXECUTED = NOW()
	, account.sharedplat = 0
WHERE 
	character_.ID = character_backup_pre_money_wipe_semi_permanent_table.CHARACTER_ID
	AND character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_table.ORIGINAL_PROFILE
	AND character_backup_pre_money_wipe_semi_permanent_table.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_table 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_table
SET 
	character_.PROFILE = character_backup_pre_money_wipe_semi_permanent_table.ORIGINAL_PROFILE
	, character_backup_pre_money_wipe_semi_permanent_table.DATE_EXECUTED = NULL
WHERE 
	character_.ID = character_backup_pre_money_wipe_semi_permanent_table.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_table.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_table WHERE DATE_EXECUTED IS NULL;
;
5 - LightWeight - TradeskillSearching_FindUsersPossiblyAbusingRecipe s_v1.7.sql
Code:
-- 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
Code:
-- 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
Code:
-- 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
Code:
-- 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;

;
Reply With Quote
 

« Previous Thread | Next Thread »

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

   

All times are GMT -4. The time now is 11:20 PM.

EQEmulator - Archive - Top

 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3