These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Pulling BOM from the dump
Author
Previous Topic Next Topic
Byrrssa Crendraven
Anti - Social
#1 - 2013-10-25 01:04:39 UTC
I'm so confused...

Why is it that when I pull the BOM for the Bantam out of the dump that I always get the data for the refined amount of the minerals instead of the base amount of the minerals required to build it?

And, how do I get the the base amount of minerals out of the db so that I can calculate my wastage in my app???

Thanks in advance...
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#2 - 2013-10-25 04:45:44 UTC
Byrrssa Crendraven
Anti - Social
#3 - 2013-10-25 08:08:53 UTC
Steve,

I really, really appreciate your reply...but, I've taken your links...I've fed them into MS Sql Server Studio and I've modified them to work so with it (basically, cleared out the greatest and then set a value for the question marks) and, I still come up with same values for the Bantam that I pulled out of the database but, with the very basic of sql statements listed on the wiki

SELECT t.typeName, m.quantity
FROM invTypeMaterials AS m
INNER JOIN invTypes AS t
ON m.materialTypeID = t.typeID
WHERE m.typeID = 582 --Bantam

Gives the result of:

typeName quantity
Tritanium 2706
Pyerite 725
Mexallon 40
Isogen 46
Nocxium 2
Zydrine 2

Which of course is the refined materials of this ship. Where to next? Thanks!
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#4 - 2013-10-25 14:28:43 UTC  |  Edited by: Steve Ronuken
Did you see that there are two queries to use?

There's the one for the base materials, and the one for the extra materials.

The full BOM query should give you the complete set, with ME taken into account. Otherwise, you'll need to use the two queries. One hitting invTypeMaterials, and removing anything marked recyclable in the extra materials from them, and the one that hits the ramTypeRequirements table for the extra materials.




https://www.fuzzwork.co.uk/2013/09/02/bom-sql-server-guest-post/ may be of interest, if you're using SQL server.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Byrrssa Crendraven
Anti - Social
#5 - 2013-10-26 01:01:53 UTC
Thank you very much!

That link helped out alot! I can now add the data from the two queries and get my base.

Thank you!
Byrrssa Crendraven
Anti - Social
#6 - 2013-10-28 22:31:57 UTC
Sorry to bother you again Steve,

But, I'm not finding the material info in the database for the Giant Secure Container, TypeID 11489... is it just not in the db? I've tried pulling it with the basic bom sql and then the extra mats sql and then the huge greatest sql...they all return an empty dataset... not sure what to do with these things...

Thanks in advance...
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#7 - 2013-10-29 08:29:16 UTC
That's weird.

I've run the full sql myself and it's returning the results I'd expect.

Do you get anything back from
select * from ramTypeRequirements where typeid=32863;

If you don't, you might have an old copy of the database, from before it was put in.


full bom for it:
select typeid,name,sum(quantity)+(sum(perfect)*(0.25-(0.05*0))*max(base)) quantity from(
select typeid,name,round(if(0>=0,greatest(0,sum(quantity))+(greatest(0,sum(quantity))*((wastefactor/(0+1))/100)),greatest(0,sum(quantity))+(greatest(0,sum(quantity))*(wastefactor/100)*(1-0)))) 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=11489
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=11489 and r.recycle=1
) t join invBlueprintTypes on (invBlueprintTypes.productTypeID=11489) 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=11489 and g.categoryID != 16
and t.groupID = g.groupID) outside group by typeid,name

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Byrrssa Crendraven
Anti - Social
#8 - 2013-10-29 16:40:36 UTC  |  Edited by: Byrrssa Crendraven
Actually, this was correct. CCP used the blueprint id's on the containers instead of the itemID's and then instead of using basic materials, they made it to where it only used extra materials. Talking about a totally ass backwards way of introducing it.

HEY, CCP!!!! YEAH YOU!!!!! A LITTLE BIT OF CONSISTENCY?!?!?!?! Twisted


Did the same damn thing with the strategic cruiser subsystems... WHISKEY TANGO FOXTROT CCP!!!! WHAT ARE YOU THINKING???
Byrrssa Crendraven
Anti - Social
#9 - 2013-10-29 16:51:14 UTC
Steve Ronuken wrote:
That's weird.

I've run the full sql myself and it's returning the results I'd expect.

Do you get anything back from
select * from ramTypeRequirements where typeid=32863;

If you don't, you might have an old copy of the database, from before it was put in.


This does pull up some information from my database... but,

Quote:

full bom for it:
select typeid,name,sum(quantity)+(sum(perfect)*(0.25-(0.05*0))*max(base)) quantity from(
select typeid,name,round(if(0>=0,greatest(0,sum(quantity))+(greatest(0,sum(quantity))*((wastefactor/(0+1))/100)),greatest(0,sum(quantity))+(greatest(0,sum(quantity))*(wastefactor/100)*(1-0)))) 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=11489
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=11489 and r.recycle=1
) t join invBlueprintTypes on (invBlueprintTypes.productTypeID=11489) 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=11489 and g.categoryID != 16
and t.groupID = g.groupID) outside group by typeid,name

- This returns an empty dataset... Even the most basic of data requests to the database returns an empty dataset.
Byrrssa Crendraven
Anti - Social
#10 - 2013-10-29 21:19:42 UTC
Steve,

Thanks again for pointing me in the right direction... if I could +1 ya again..I would... you've been a tremendous help!