Reprocessing Materials

From EVEDev
Jump to: navigation, search

Mineral quantities from reprocessing Items. The queries below work in mysql. Modify to suit your database flavor.

List most reprocessable items and what they reprocess into

If you pay close attention, you'll notice that in the line INNER JOIN dgmtypeattributes only meta levels 0-4 are selected. No T2 / officer loot will be output by this query. This version of the query returns most reprocessable items (some items such as battlecruisers are not found with this query), with all attributes on a single row.

SELECT
	t1.typeID,
	t1.typeName,
	coalesce(t2.valueFloat, t2.valueInt) AS metalevel,
	t1.groupID,
	t3.groupName,
	t1.volume,
	t1.basePrice,
	SUM(CASE WHEN m1.materialTypeID = 34 THEN m1.quantity ELSE 0 END) AS Tritanium,
	SUM(CASE WHEN m1.materialTypeID = 35 THEN m1.quantity ELSE 0 END) AS Pyerite,
	SUM(CASE WHEN m1.materialTypeID = 36 THEN m1.quantity ELSE 0 END) AS Mexallon,
	SUM(CASE WHEN m1.materialTypeID = 37 THEN m1.quantity ELSE 0 END) AS Isogen,
	SUM(CASE WHEN m1.materialTypeID = 38 THEN m1.quantity ELSE 0 END) AS Nocxium,
	SUM(CASE WHEN m1.materialTypeID = 39 THEN m1.quantity ELSE 0 END) AS Zydrine,
	SUM(CASE WHEN m1.materialTypeID = 40 THEN m1.quantity ELSE 0 END) AS Megacyte,
	SUM(CASE WHEN m1.materialTypeID = 11399 THEN m1.quantity ELSE 0 END) AS Morphite
FROM invTypes t1
INNER JOIN dgmTypeAttributes t2 ON t1.typeID = t2.typeID AND t2.attributeID = 633 AND t1.published = 1 AND t2.valueInt IN (0, 1, 2, 3, 4) -- metaLevel
INNER JOIN invGroups t3 ON t1.groupID = t3.groupID
INNER JOIN invTypeMaterials m1 ON t1.typeID = m1.typeID
GROUP BY
	t1.typeID,
	t1.typeName,
	coalesce(t2.valueFloat,t2.valueInt),
	t1.groupID,
	t3.groupName,
	t1.volume,
	t1.basePrice
ORDER BY t1.typeName;

Get the minerals a single item reprocesses into

  • This version of the query returns has the same filters as the one above (no T2 / officer loot), but only returns the reprocessing results for a single item, specified by WHERE m1.typeid = %d, replace %d with the item ID (Example: 209 = Trauma Heavy Missile).

    In the case of Trauma Heavy Missile, be sure you figure out through some other method that you reprocess ammo in lots of 100, not one missile at a time, as this query assumes you already know and deal with that reality.
SELECT
	SUM(CASE WHEN m1.materialTypeID = 34 THEN m1.quantity ELSE 0 END) AS Tritanium, 
	SUM(CASE WHEN m1.materialTypeID = 35 THEN m1.quantity ELSE 0 END) AS Pyerite, 
	SUM(CASE WHEN m1.materialTypeID = 36 THEN m1.quantity ELSE 0 END) AS Mexallon, 
	SUM(CASE WHEN m1.materialTypeID = 37 THEN m1.quantity ELSE 0 END) AS Isogen, 
	SUM(CASE WHEN m1.materialTypeID = 38 THEN m1.quantity ELSE 0 END) AS Nocxium, 
	SUM(CASE WHEN m1.materialTypeID = 39 THEN m1.quantity ELSE 0 END) AS Zydrine, 
	SUM(CASE WHEN m1.materialTypeID = 40 THEN m1.quantity ELSE 0 END) AS Megacyte, 
	SUM(CASE WHEN m1.materialTypeID = 11399 THEN m1.quantity ELSE 0 END) AS Morphite 
FROM invTypes t1 
INNER JOIN invTypeMaterials m1 ON t1.typeID = m1.typeID 
WHERE m1.typeID = %d;
Personal tools