Bill of Materials

From EVEDev

1 General Query
2 T1 Items
3 T2 Items

[edit] General Query

 
 SELECT typeReq.typeName, graphics.icon, CEIL(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) AS quantity, materials.damagePerJob
 FROM typeActivityMaterials AS materials
 INNER JOIN invTypes AS typeReq
 ON materials.requiredtypeID = typeReq.typeID
 INNER JOIN invBlueprintTypes AS bluePrint
 ON materials.typeID = bluePrint.blueprintTypeID
 INNER JOIN eveGraphics AS graphics
 ON typeReq.graphicID = graphics.graphicID
 WHERE materials.typeID = <BLUEPRINT_ID> AND materials.activityId = 1
 ORDER BY typeReq.typeName;

[edit] T1 Items

Example for item 944 (Punisher Blueprint)

SELECT typeReq.typeName, graphics.icon, CEIL(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) AS quantity, materials.damagePerJob
FROM typeActivityMaterials AS materials
INNER JOIN invTypes AS typeReq
ON materials.requiredtypeID = typeReq.typeID
INNER JOIN invBlueprintTypes AS bluePrint
ON materials.typeID = bluePrint.blueprintTypeID
INNER JOIN eveGraphics AS graphics
ON typeReq.graphicID = graphics.graphicID
WHERE materials.typeID = 944 AND materials.activityId = 1
ORDER BY typeReq.typeName;

[edit] T2 Items

Example for item 11394 (Retribution Blueprint) [T2 Punisher, above]

SELECT typeReq.typeName, graphics.icon, CEIL(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) AS quantity, materials.damagePerJob
FROM typeActivityMaterials AS materials
INNER JOIN invTypes AS typeReq
ON materials.requiredtypeID = typeReq.typeID
INNER JOIN invBlueprintTypes AS bluePrint
ON materials.typeID = bluePrint.blueprintTypeID
INNER JOIN eveGraphics AS graphics
ON typeReq.graphicID = graphics.graphicID
WHERE materials.typeID = 11394 AND materials.activityId = 1
ORDER BY typeReq.typeName

As you can see there is a mix of Skills required, T1 Items required, Materials and the wastage factor is being applied to Skills and T1 Items, too! You have to do 3 separate queries to obtain each part: Skills, T1 Items, Materials (with wastage).

[edit] Skills

 
 SELECT typeReq.typeName, graphics.icon, materials.quantity AS level
 FROM typeActivityMaterials AS materials
 INNER JOIN invTypes AS typeReq ON materials.requiredtypeID = typeReq.typeID
 INNER JOIN invGroups AS typeGroup ON typeReq.groupID = typeGroup.groupID
 INNER JOIN eveGraphics AS graphics ON typeReq.graphicID = graphics.graphicID
 WHERE materials.typeID = <BLUEPRINT_ID> AND materials.activityId = 1 AND typeGroup.categoryID = 16
 ORDER BY typeReq.typeName;

Error while connecting to host "" !

[edit] T1 Items

 
 SELECT typeReq.typeName, graphics.icon, materials.quantity, materials.damagePerJob
 FROM typeActivityMaterials AS materials
 INNER JOIN invTypes AS typeReq ON materials.requiredtypeID = typeReq.typeID
 INNER JOIN invGroups AS typeGroup ON typeReq.groupID = typeGroup.groupID
 INNER JOIN eveGraphics AS graphics ON typeReq.graphicID = graphics.graphicID
 WHERE materials.typeID = <BLUEPRINT_ID> AND materials.activityId = 1 AND typeGroup.categoryID IN (6, 7)
 ORDER BY typeReq.typeName;

Error while connecting to host "" !

[edit] Materials

Note: Waste Factor doesn't apply to groupID 332 (Tools used in manufacturing and Research --> R.A.M. and R.Db)

 
 SELECT typeReq.typeName, graphics.icon, IF(typeReq.groupID = 332, materials.quantity, CEIL(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) ) AS quantity, materials.damagePerJob
 FROM typeActivityMaterials AS materials
 INNER JOIN invTypes AS typeReq ON materials.requiredtypeID = typeReq.typeID
 INNER JOIN invGroups AS typeGroup ON typeReq.groupID = typeGroup.groupID
 INNER JOIN invBlueprintTypes AS bluePrint ON materials.typeID = bluePrint.blueprintTypeID
 INNER JOIN eveGraphics AS graphics ON typeReq.graphicID = graphics.graphicID
 WHERE materials.typeID = <BLUEPRINT_ID> AND materials.activityId = 1 AND typeGroup.categoryID NOT IN (6, 7, 16)
 ORDER BY typeReq.typeName;

SELECT typeReq.typeName, graphics.icon, IF(typeReq.groupID = 332, materials.quantity, CEIL(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) ) AS quantity, materials.damagePerJob
FROM typeActivityMaterials AS materials
INNER JOIN invTypes AS typeReq
ON materials.requiredtypeID = typeReq.typeID
INNER JOIN invGroups AS typeGroup
ON typeReq.groupID = typeGroup.groupID
INNER JOIN invBlueprintTypes AS bluePrint
ON materials.typeID = bluePrint.blueprintTypeID
INNER JOIN eveGraphics AS graphics
ON typeReq.graphicID = graphics.graphicID
WHERE materials.typeID = 11394 AND materials.activityId = 1 AND typeGroup.categoryID NOT IN (6, 7, 16)
ORDER BY typeReq.typeName;

(MYSQL tested) It seems like when querying for the Phobos (materials.typeID = 12022) you need to go this way as you get some negative material bills:

 
 SELECT typeReq.typeName, graphics.icon, 
 IF(typeReq.groupID = 332, materials.quantity, round(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) ) AS
 quantity, materials.damagePerJob 
 FROM typeActivityMaterials AS materials
 INNER JOIN invTypes AS typeReq ON materials.requiredtypeID = typeReq.typeID
 INNER JOIN invGroups AS typeGroup ON typeReq.groupID = typeGroup.groupID
 INNER JOIN invBlueprintTypes AS bluePrint ON materials.typeID = bluePrint.blueprintTypeID
 INNER JOIN eveGraphics AS graphics ON typeReq.graphicID = graphics.graphicID
 WHERE materials.typeID = 12022  AND materials.activityId = 1 AND typeGroup.categoryID NOT IN ( 7, 16)
 AND materials.quantity > 0

The output will be:

SELECT typeReq.typeName, graphics.icon, 
IF(typeReq.groupID = 332, materials.quantity, round(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) ) AS quantity, materials.damagePerJob 
FROM typeActivityMaterials AS materials
INNER JOIN invTypes AS typeReq ON materials.requiredtypeID = typeReq.typeID
INNER JOIN invGroups AS typeGroup ON typeReq.groupID = typeGroup.groupID
INNER JOIN invBlueprintTypes AS bluePrint ON materials.typeID = bluePrint.blueprintTypeID
INNER JOIN eveGraphics AS graphics ON typeReq.graphicID = graphics.graphicID
WHERE materials.typeID = 12022  AND materials.activityId = 1 AND typeGroup.categoryID NOT IN ( 7, 16)
AND materials.quantity > 0
ORDER BY typeReq.typeName;

The ship it's nice to appear on the result. CEIL it's not giving the right result in all cases, ROUND seems to preform better.

Previous request must be for MYSQL, in Sql Server 2005 it doesn't give the good results. Here is the sql query needed for SQL server 2005. Notice the casting of wasteFactor as a DOUBLE PRECISION; simple reason is that the waste facte and "100" are integers and the result be an integer as well resulting to be always equal to 0. Casting one of the two numbers will change the result to be give out more precision!

 
SELECT typeReq.typeName,graphics.icon,CASE WHEN typeReq.groupID = 332 THEN  materials.quantity ELSE  ROUND(materials.quantity * (1 + CAST(bluePrint.wasteFactor AS DOUBLE PRECISION) / 100), 0) END AS quantity,materials.damagePerJob 
FROM typeActivityMaterials AS materials
INNER JOIN invTypes AS typeReq ON materials.requiredtypeID = typeReq.typeID
INNER JOIN invGroups AS typeGroup ON typeReq.groupID = typeGroup.groupID
INNER JOIN invBlueprintTypes AS bluePrint ON materials.typeID = bluePrint.blueprintTypeID
INNER JOIN eveGraphics AS graphics ON typeReq.graphicID = graphics.graphicID
WHERE materials.typeID = 28660  AND materials.activityId = 1 AND typeGroup.categoryID NOT IN ( 7, 16)
AND materials.quantity > 0