LMeve dev blog: Big POCO update coming

Just as promised in the previous LMeve dev blog, I have recently added the ability to track income from individual planets. Moreover, with the new percentage bars, you will know what are your best planets simply by looking at the table. To check how much a specific planet earned you in the last 30 days, simply hover your mouse above that planet’s name to see a tooltip with the exact number.

But this is not the only thing added in this release. Some pages require crunching a lot of data, which means they render rather slowly. Most of this data doesn’t change very often, so it seems to be a good idea to generate them once, and then save a cached copy. Then every time users come back to this page, a cached version is displayed, saving everyones time.

One of the pages, the Profit Chart can have several hundred records, which need their manufacturing and invention prices calculated, and it can load for as long as few minutes (depending on individual configuration of course). I used a different approach here: a lazy loader. Instead of one big 2 minute request, I display the page immediately, and as user is scrolling, more data is being loaded into the table in packs of 10 rows.

How to link POCOs with planets & income?

It’s not trivial, but far from impossible. Let’s have a look at columns returned by the /corp/CustomsOffices.xml.aspx API:

<row itemID="101296791423" solarSystemID="30004295" solarSystemName="Keba" reinforceHour="19" allowAlliance="True" allowStandings="True" standingLevel="-10" taxRateAlliance="0" taxRateCorp="0" taxRateStandingHigh="0" taxRateStandingGood="0.05" taxRateStandingNeutral="0.07" taxRateStandingBad="0.1" taxRateStandingHorrible="0.15" />

There is POCO itemID, which stays the same in all API endpoints, there is solarSystemID and system name. And of course the tax settings. But there is nothing about planet itemID here. Maybe /corp/Locations.xml.aspx endpoint will reveal a bit more?

<row itemID="101296791423" itemName="Customs Office (Keba I)" x="58598019193.2515" y="-5937030740.62145" z="-3890977687.95432" />

Okay, so now we have x, y, z coordinates, and POCOs name. In the first version of POCO management screen I simply cut planet name out of this using a regular expression. But to link it with other data we still need the planet itemID. How do we find it? Well, POCO is orbiting a planet, right? And we have cooridnates of all planets in the Static Data Export database – in the mapDenormalize table. What are we waiting for?

CREATE FUNCTION `findNearest`(`x1` DOUBLE, `y1` DOUBLE, `z1` DOUBLE, `solarSystemID1` INT) RETURNS int(11)
 READS SQL DATA
 RETURN (
 SELECT a.itemID FROM
 (SELECT SQRT(POW(x1-x,2)+POW(y1-y,2)+POW(z1-z,2)) AS dist,itemID
 FROM mapDenormalize
 WHERE `solarSystemID`=solarSystemID1
 ORDER BY dist ASC
 LIMIT 1) a
 )

40271955

Bingo! The function returns the closest solar body, in this case, itemID of the planet. We just have to JOIN two other tables from SDE: invNames to convert the planet ID we just got into planet name, Keba I, and invItems to find it’s typeID (Temperate, Barren, Oceanic, etc.).

Ok, now on to income for this specific POCO. For this, we will need  /corp/WalletJournal.xml.aspx endpoint, which contains all the transactions of a corp. The records we are looking for will have refTypeID equal to 96 and 97 (import/export taxes) and the planet itemID will be in argID1 field. Once all joins are in place, we simply have to filter last thirty days and SUM all the amount values.

As usual, the current LMeve version can be downloaded from https://github.com/roxlukas/lmeve