Updated Bill of Materials (BOM) SQL for the SDE

Just an update for my previous post on SQL for blueprints using the SDE. Now you can generate a complete BOM with a single statement.

I now have a use for getting /all/ the numbers out in a single query and that is what you’ll find below. You could even, if you so wanted, join it in as another table, against a table of blueprint product ids, MEs and a set PE, along with prices with some sums, to generate a full costing for a number of blueprints.

This is written specifically for use with an interface that lets you use named parameters (DBI, PDO, JDBC) and requires :typeid as the id of what you’re making. :ME the ME of the blueprint :PE the production efficiency of the character making them. It also requires a greatest function, which MS SQL doesn’t have; so you’d need to replace that with an if()

    select typeid,name,sum(quantity)+(sum(perfect)*(0.25-(0.05*:pe))*max(base)) quantity from(
    select typeid,name,round(if(:me>=0,greatest(0,sum(quantity))+(greatest(0,sum(quantity))*((wastefactor/(:me+1))/100)),greatest(0,sum(quantity))+(greatest(0,sum(quantity))*(wastefactor/100)*(1-:ME)))) quantity,1 base,greatest(0,sum(quantity)) perfect from (
      select invTypes.typeid typeid,invTypes.typeName name,quantity
      from invTypes,invTypeMaterials
      where invTypeMaterials.materialTypeID=invTypes.typeID
       and invTypeMaterials.TypeID=: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=:typeid and r.recycle=1
    ) t join invBlueprintTypes on (invBlueprintTypes.productTypeID=:typeid) group by typeid,name
    union
    SELECT t.typeID typeid,t.typeName tn, r.quantity * r.damagePerJob quantity,0 base,r.quantity * r.damagePerJob perfect
    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=:typeid and g.categoryID != 16
    and t.groupID = g.groupID) outside group by typeid,name

Not a nice query, with more selects in it than I like, but pretty much needed with how it works.