Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Big FatWallet
Twisted Defence
0
|
Posted - 2012.10.15 07:22:00 -
[1] - Quote
Hi guys, I have been banging my head trying to get a blueprint material list from the datadump. Can anyone help me with this please?
So far I have extracted invTypeMaterials, invTypes, invBlueprintTypes and cross refering them in access. I'm still missing another table and how it links though.
Many thanks |
Muscaat
EVE Markets
12
|
Posted - 2012.10.16 08:44:00 -
[2] - Quote
http://wiki.eve-id.net/Bill_of_Materials should help you out - and bookmark that site, it's awesome |
Steve Ronuken
Fuzzwork Enterprises
725
|
Posted - 2012.10.16 09:15:00 -
[3] - Quote
https://github.com/fuzzysteve/eve-blueprint-calc might be of interest.
specifically:
Base materials: select typeid,name,greatest(0,sum(quantity)) quantity from (select invTypes.typeid typeid,invTypes.typeName name,quantity from invTypes,invTypeMaterials where invTypeMaterials.materialTypeID=invTypes.typeID and invTypeMaterials.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=? and r.recycle=1) t group by typeid,name;
Extra materials: SELECT t.typeName tn, r.quantity qn, r.damagePerJob dmg,t.typeID typeid 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=? and g.categoryID != 16 and t.groupID = g.groupID
The binds are all the type id of the thing that you're making.
quick explanation:
When something is marked as recyclable in the ramTypeRequirements table, you have to take out its components from the base materials (to a minimum of 0)
Things to bear in mind: Base materials are affected by ME. extra materials aren't. Base materials are affected by your production efficiency waste. Extra materials aren't, unless they also appear in the base materials. FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities. |
Big FatWallet
Twisted Defence
0
|
Posted - 2012.10.16 09:55:00 -
[4] - Quote
Perfect thanks guys.
I checked out the ramTypeRequirements last night and realised that anything with a damageperjob of 0 was a skill so didn't include it? At the moment pulling the data into access and using that for queries as my sql isn't the best.
Steve, I saw your queries before and tried them on my machine but to no avail. I got errors and no data so went down the route of pulling the data seperately in access to see what was happening before trying to get it into 1 sql query.
Edited to say I notice: Skills are mixed in with the extra materials; to separate them, you can check their category via invGroups: categoryID 16 is for skills (from "Bill of Materials" info) |
Steve Ronuken
Fuzzwork Enterprises
725
|
Posted - 2012.10.16 10:57:00 -
[5] - Quote
Big FatWallet wrote:Perfect thanks guys.
I checked out the ramTypeRequirements last night and realised that anything with a damageperjob of 0 was a skill so didn't include it? At the moment pulling the data into access and using that for queries as my sql isn't the best.
Steve, I saw your queries before and tried them on my machine but to no avail. I got errors and no data so went down the route of pulling the data seperately in access to see what was happening before trying to get it into 1 sql query.
Edited to say I notice: Skills are mixed in with the extra materials; to separate them, you can check their category via invGroups: categoryID 16 is for skills (from "Bill of Materials" info)
Possibly related to the use of subqueries and a union. Or case sensitivity issues.
Those queries built up over time, so they're possibly not the most efficient they could be.
Something to bear in mind with ramTypeRequirements. You need to specifiy the activityid as 1, or you'll pull the materials needed for invention, ME research, PE research and so on.
FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities. |
Big FatWallet
Twisted Defence
0
|
Posted - 2012.10.16 12:29:00 -
[6] - Quote
Thanks Steve. I think it didn't recognise 'greatest' but can't be certain.
Will try and write some queries tonight to try and grab the data. |
Steve Ronuken
Fuzzwork Enterprises
725
|
Posted - 2012.10.16 13:51:00 -
[7] - Quote
Big FatWallet wrote:Thanks Steve. I think it didn't recognise 'greatest' but can't be certain.
Will try and write some queries tonight to try and grab the data.
ahh. looks like access doesn't have a function for comparing values in a list (which is somewhat silly really).
Either: http://www.swell-services.be/cmsms/greatest-and-least-oracle-functions-in-ms-access
or: http://www.techonthenet.com/access/functions/advanced/iif.php
should do you. It's just there to set anything less than 0 to 0 FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities. |
Big FatWallet
Twisted Defence
0
|
Posted - 2012.10.16 15:09:00 -
[8] - Quote
Was using sql server for the SQL queries, so when it failed I grabbed the data from sql and put into access.
Thanks for the info though, will take a look for Access. |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |