As you may know, I’m the ‘author’ of one of the blueprint calculators that some people use in EVE Online. The one at http://www.fuzzwork.co.uk/blueprints/

If you didn’t know that, how did you get here? 😉

Anyway, it’s not uncommon to see people asking how to get the materials for building something out of the Static Data Export; and as such, I thought I’d write up a post here that I can direct people to. Oh, and if you’re looking for the code for the blueprint calculator, take a look at https://github.com/fuzzysteve/eve-blueprint-calc It’s mostly up to date, just missing a few twiddly bits.

The SQL

Now for the meat of this post. Being able to run this SQL is dependent on having a copy of the SDE up and running. If you’re using anything other than mysql, postgresql or oracle, you may have problems with the greatest function, in the first bit of sql. replace it with a case statement. You’re wanting to return 0, if the value is negative.

select typeid,name,greatest(0,sum(quantity)) quantity from (
  select invTypes.typeid typeid,invTypes.typeName name,quantity
  from invTypes,invTypeMaterials
  where invTypeMaterials.materialTypeID=invTypes.typeID
   and invTypeMaterials.TypeID=?
  union
  select invTypes.typeid typeid,invTypes.typeName name,
         invTypeMaterials.quantity*r.quantity*-1 quantity
  from invTypes,invTypeMaterials,ramTypeRequirements r,invBlueprintTypes bt
  where invTypeMaterials.materialTypeID=invTypes.typeID
   and invTypeMaterials.TypeID =r.requiredTypeID
   and r.typeID = bt.blueprintTypeID
   and r.activityID = 1 and bt.productTypeID=? and r.recycle=1
) t group by typeid,name

SELECT t.typeName tn, r.quantity qn, r.damagePerJob dmg,t.typeID typeid
FROM ramTypeRequirements r,invTypes t,invBlueprintTypes bt,invGroups g
where r.requiredTypeID = t.typeID and r.typeID = bt.blueprintTypeID
and r.activityID = 1 and bt.productTypeID=? and g.categoryID != 16
and t.groupID = g.groupID

Notes

There are three bind variables in total, two in the first, one in the second. These are all the same value, the typeid of the item being manufactured. You could just replace them, but bind variables are your friend. Learn how to use them, and love them. People that do string replacement are asking for trouble. PHP has PDO, Perl has DBI, Python has DB-API, Java has JDBC. They all support bind variables.

The reason for the two queries is that CCP have split the materials into those affected by ME, which are also recovered when you recycle it (the base materials), and those which aren’t affected (the extra materials). The base materials query here doesn’t return the materials recovered, but that’s due to the removal of materials which are marked as recyclable in the extra materials.

Extra Credit

The queries above only give you the materials for a perfect blueprint. It’s rare that you actually have one, so you’ll need to take ME and Production Efficiency into account as well. The waste factor referred to in the formulas is that one that’s defined in invBlueprintTypes. Perfect is the number of the materials defined by the above queries.

If your ME is 0 or greater, then for each material in your base materials:

If your ME is less than 0, then for each material in your base materials:

If your production efficiency is less than 5, then you also need to add to your materials. You only do this for:

• Base materials
• Extra materials that also appear in the base materials

If it’s 5, you’re adding a number that’s been multiplied by 0. You may wish to do this for completeness, or so that your code works for more than just your main producer.

That’s it for now. If you have any questions, feel free to ask here, or in Eve.