cf_armory continued
I'd posted a first throw together of Blizzard Armory data a while ago here. Now I've completed phase one. It'll pull data for the guild from armory, save it to the database, pull reputations, and display is nicely (with a stolen CSS file).
I have to thank Matt Shooks, his posts on the topic gave me some good ideas. From his code, I broke up the process so that it can happen more in smaller steps. First load all guild data, then load each character's reputation. If this were in CF8, I'd maybe want to run the reputation processing in blocks of ten characters in separate threads. That would be hot.
The next step will be to put filtering on the display page. Only show druids and priests that can go to heroic Mana Tombs, for example. How about some code first.
The page to load data to the database is pretty short and sweet.
<cfparam name="url.realm" default="Bloodhoof"/>
<cfoutput>
(RE)LOADING GUILD ROSTER FOR #url.guild# - #url.realm#
</cfoutput>
...
<cfflush>
<cfset quickGuildRosterLoad(url.realm,url.guild,68)>
...
ROSTER LOADED<br/>
<br/>
STARTING REPUTATION UPDATE<br/>
<cfflush/>
<cfset loadGuildReputation(url.realm,url.guild)>
GUILD REP UPDATED
(yes, I've changed guilds yet again since the last post) Nothing of note here. Load the roster for the given realm and guild and minimum level. Then update all reputation information for a given realm and guild. The minimum level is assumed on the second function. If level 50+ were loaded once, then 68+ loaded later, all 50-67 would be made inactive and not have factions updated. For this, it would be good to pick a level range to display and stick with it.
The function to load the roster is mostly taken from Matt.
<cfargument name="realm">
<cfargument name="guild">
<cfargument name="minlevel" required="false" default="68">
<cfargument name="baseURL" required="false" default="http://armory.worldofwarcraft.com">
<cfset var url = (
arguments.baseURL
& "/guild-info.xml?r=" & URLEncodedFormat(arguments.realm)
& "&n=" & URLEncodedFormat(arguments.guild)
)/>
<cfset var guildXML = makeHTTPCall(url)>
<!--- check to make sure this was a good request --->
<cfif Left(guildXML.StatusCode, 3) NEQ 200>
<cfthrow message="Error reaching armory"/>
<cfelse>
<!--- deactivate all members, only members still on the roster will be reactived later on --->
<cfquery datasource="#application.dsn#">
UPDATE hmg_tblCharacters
SET active = 0
WHERE realm = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.realm#" />
AND guild = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.guild#" />
</cfquery>
<!--- retrieve characters to use to verify characterID --->
<cfquery name="queryCharacters" datasource="#application.dsn#">
SELECT *
FROM hmg_tblCharacters
WHERE realm = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.realm#" />
AND guild = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.guild#" />
</cfquery>
<cfset guildXML = XmlParse(guildXML.FileContent) />
<!--- loop through the members --->
<cfloop from="1" to="#ArrayLen(guildXML.page.guildInfo.guild.members.character)#" index="i">
<cfset characterName = guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["name"] />
<cfquery name="checkName" dbtype="query">
SELECT *
FROM queryCharacters
WHERE characterName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#characterName#" />
</cfquery>
<!--- if the search has a result, then the user already exists --->
<cfif checkName.RecordCount eq 1>
<cfquery datasource="#application.dsn#">
UPDATE hmg_tblCharacters
SET classID = <cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["classId"]#" />,
genderID = <cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["genderId"]#" />,
level = <cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["level"]#" />,
raceID = <cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["raceId"]#" />,
rank = <cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["rank"]#" />,
url = <cfqueryparam cfsqltype="cf_sql_varchar" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["url"]#" />,
active = 1
WHERE characterID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#checkName.characterID#" />
</cfquery>
<cfoutput>Updating #characterName#</cfoutput><br/>
<cfelse>
<cfif guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["level"] gte arguments.minlevel>
<cfquery datasource="#application.dsn#">
INSERT INTO hmg_tblCharacters (classID, genderID, level, characterName, raceID, rank, url, realm, guild)
VALUES (
<cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["classId"]#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["genderId"]#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["level"]#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["name"]#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["raceId"]#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["rank"]#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#guildXML.page.guildInfo.guild.members.character[i].XmlAttributes["url"]#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.realm#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.guild#" />
)
</cfquery>
<cfoutput>Inserting #characterName#</cfoutput><br/>
</cfif>
</cfif>
</cfloop>
<cfflush />
</cfif>
</cffunction>
I didn't really change that much. Made it throw an error if it fails to get Armory data and put in the statements to be flushed. This whole process takes a long time and some feedback while it happens it nice.
Then you load guild data. This is where I finally got original. This just loops over the guild and says to update each (active) member.
<cfargument name="realm">
<cfargument name="guild">
<cfargument name="baseURL" required="false" default="http://armory.worldofwarcraft.com">
<cfset var url = "">
<cfset var guildRoster = "">
<cfquery name="guildRoster" datasource="#application.dsn#">
select *
from hmg_tblCharacters
where guild = <cfqueryparam value="#arguments.guild#"/>
and active = 1
</cfquery>
<cfoutput query="guildRoster">
<cfset loadCharacterFaction(guildRoster.characterID)>
</cfoutput>
</cffunction>
Now the function for pulling character info and updating stuff
<cfargument name="characterID">
<cfargument name="factionkeys" required="false" default="thevioleteye,lowercity,theshatar,honorhold,keepersoftime,cenarionexpedition">
<cfargument name="baseURL" required="false" default="http://armory.worldofwarcraft.com">
<cfset var characterInfo = "">
<cfset var url = "">
<cfset var factionkey = "">
<cfquery name="characterInfo" datasource="#application.dsn#">
select *
from hmg_tblCharacters
where characterID = <cfqueryparam value="#arguments.characterID#"/>
</cfquery>
<cfset url = (
arguments.baseURL
& "/character-sheet.xml?r=" & URLEncodedFormat(characterInfo.realm)
& "&n=" & URLEncodedFormat(characterInfo.characterName))/>
<cfoutput>UPDATING REPUTATION INFO FOR #characterInfo.charactername#</cfoutput><br/><cfflush/>
<cfset characterXML = makeHTTPCall(url)>
<!--- check to make sure this was a good request --->
<cfif Left(guildXML.StatusCode, 3) NEQ 200>
<cfthrow message="Error reaching armory"/>
<cfelse>
<cfset currentHash = Hash(characterXML.FileContent) />
<!--- check to see if the character data differs from what is currently stored --->
<cfif CompareNoCase(currentHash, characterInfo.characterHash) eq 0>
<!--- Profile is already up to date. Stopping import --->
<cfelse>
<cfset characterXML = XmlParse(characterXML.FileContent) />
<!--- process Reputation --->
<cfif IsXmlNode(characterXML.page.characterInfo.reputationTab)>
<!--- clear out reputation for the character --->
<cfquery datasource="#application.dsn#">
DELETE FROM hmg_tblCharacterReputations
WHERE characterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.characterID#" />
</cfquery>
<!--- loop through each faction category --->
<cfloop from="1" to="#ArrayLen(characterXML.page.characterInfo.reputationTab.factionCategory)#" index="i">
<cfloop from="1" to="#ArrayLen(characterXML.page.characterInfo.reputationTab.factionCategory[i].faction)#" index="j">
<cfset factionkey = replace(characterXML.page.characterInfo.reputationTab.factionCategory[i].faction[j].XmlAttributes["key"],"'","","all")>
<cfif ListFindNoCase(arguments.factionkeys,factionkey)>
<cfquery datasource="#application.dsn#">
INSERT INTO hmg_tblCharacterReputations (
characterID, categoryName, faction, reputation, factionKey, factionDisplay
)
VALUES (
<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.characterID#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#characterXML.page.characterInfo.reputationTab.factionCategory[i].XmlAttributes["name"]#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#characterXML.page.characterInfo.reputationTab.factionCategory[i].faction[j].XmlAttributes["name"]#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#characterXML.page.characterInfo.reputationTab.factionCategory[i].faction[j].XmlAttributes["reputation"]#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#factionkey#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#formatReputation(characterXML.page.characterInfo.reputationTab.factionCategory[i].faction[j].XmlAttributes["reputation"])#" />
)
</cfquery>
</cfif>
</cfloop>
</cfloop>
<!--- the last thing to perform would be to update the hash value (just incase it fails if the hash is different it will try to reload) --->
<cfquery datasource="#application.dsn#">
UPDATE hmg_tblCharacters
SET characterHash = <cfqueryparam cfsqltype="cf_sql_varchar" value="#currentHash#" />
WHERE characterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.characterID#" />
</cfquery>
<cfelse>
<!--- Blizzard character profile is missing detail. Stopping import. --->
</cfif>
</cfif>
</cfif>
</cffunction>
If a character is in guild A and loaded in. Then goes to guild B, a new entry will be made when guild B loads their data. Next time guild A refreshes, the character will be made inactive. This is good. If the autogenerated ID is used to tie the character to DKP entries or forum information, you'll want to keep that around in case the person comes back.
Check that the armory pull was good. The argument passed in, factionkeys, can be a list of whatever factions you're interested in. For a BC key generator, you only really care about these six (note I changed thesha'tar to theshatar by removing 's, why later). If you wanted to see who was exalted with the most things or whatever, add the factions to that list or take out the line that checks the faction before inserting it. Matt's idea of the hash was brilliant, to make sure something was changed before inserting it. This needs some updating with the faction list I added. If you only care about Ironforge rep and run this function, only passing it Ironforge, if Zandalar Tribe reputation changed, it'll update Ironforge still. Can't think of how to use the hash better though.
Now the CFHTTP call that's been used all over in this
<cfargument name="url" required="true">
<!---
Store the user agent that I am using with my browser
--->
<cfset var strUserAgent = (
"Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; " &
"rv:1.8.1.3) Gecko/20070309 Firefox/2.0.0.3"
) />
<cfhttp
url="#arguments.url#"
method="GET"
result="armoryXML"
useragent="#strUserAgent#"
/>
<cfreturn armoryXML>
</cffunction>
Thanks to Ben Nadel for the help on figuring this out.
And last and probably least, the reputation display. This probably should be done on the display end, rather than as stuff is being inserted into the database. But since this this update process would happen as some batch job and the values are tied to data that will be updated anyway, I didn't see the harm in storing this.
<cfargument name="reputation" required="yes" type="numeric" />
<cfif ARGUMENTS.reputation lt -12000>
<cfreturn "Hated (#abs(ARGUMENTS.reputation+42000)#/36000)" />
<cfelseif ARGUMENTS.reputation lt -3000>
<cfreturn "Hostile (#abs(ARGUMENTS.reputation+6000)#/3000)" />
<cfelseif ARGUMENTS.reputation lt 0>
<cfreturn "Unfriendly (#abs(ARGUMENTS.reputation+3000)#/3000)" />
<cfelseif ARGUMENTS.reputation lt 3000>
<cfreturn "Neutral (#ARGUMENTS.reputation#/3000)" />
<cfelseif ARGUMENTS.reputation lt 9000>
<cfreturn "Friendly (#ARGUMENTS.reputation-3000#/6000)" />
<cfelseif ARGUMENTS.reputation lt 21000>
<cfreturn "Honored (#ARGUMENTS.reputation-9000#/9000)" />
<cfelseif ARGUMENTS.reputation lt 33000>
<cfreturn "Revered (#ARGUMENTS.reputation-21000#/21000)" />
<cfelse>
<cfreturn "Exalted (#ARGUMENTS.reputation-42000#/1000)" />
</cfif>
</cffunction>
...I lied! There's more! Doing all this doesn't create a flat table that's easy to query, so here I flatten it.
<cfargument name="guild" type="string" required="yes">
<cfargument name="realm" type="string" required="yes">
<cfargument name="factionkeys" type="string" required="yes">
<cfset var guildrep = "">
<cfset var columnlist = "characterid,charactername,raceid,genderid,classid,level,url," & url.factionkeys>
<cfset var repreturn = "">
<cfset var i = "">
<cfloop list="#factionkeys#" index="i" delimiters=",">
<cfset columnlist = ListAppend(columnlist,i & "display")>
</cfloop>
<cfset repreturn = QueryNew(columnlist)>
<cfquery name="guildrep" datasource="#application.dsn#">
select *
from hmg_vwCharacterReputations
where guild = <cfqueryparam value="#arguments.guild#"/>
and realm = <cfqueryparam value="#arguments.realm#"/>
and factionkey in (<cfqueryparam value="#arguments.factionkeys#" list="yes"/>)
</cfquery>
<cfoutput query="guildrep" group="charactername">
<cfset QueryAddRow(repreturn)>
<cfset QuerySetCell(repreturn,"charactername",guildrep.charactername)>
<cfset QuerySetCell(repreturn,"raceid",guildrep.raceid)>
<cfset QuerySetCell(repreturn,"classid",guildrep.classid)>
<cfset QuerySetCell(repreturn,"genderid",guildrep.genderid)>
<cfset QuerySetCell(repreturn,"level",guildrep.level)>
<cfset QuerySetCell(repreturn,"url",guildrep.url)>
<cfoutput>
<cfset QuerySetCell(repreturn,guildrep.factionkey,guildrep.reputation)>
<cfset QuerySetCell(repreturn,"#guildrep.factionkey#display",guildrep.factiondisplay)>
</cfoutput>
</cfoutput>
<cfreturn repreturn>
</cffunction>
Again with the factionkeys. That's pulling from a view, yes. I'll skip posting the SQL for that, it's just a join of the table containing base character information and the table with all the reputation. Since I'm not displaying the race or class names anywhere at the moment, the IDs are good enough for me.
There are no comments for this entry.
[Add Comment]