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.