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.
 

Mineral only BPO list

First post
Author
Previous Topic Next Topic
Simon Hashur
Imperial Academy
Amarr Empire
#1 - 2017-05-13 12:20:03 UTC
Hi folks,

I'm looking for a list of BPO's that require only minerals to produce and a way to get those into a useable spreadsheet.

i've been banging away at this on and off for the last 2-3 weeks (5hrs straight today), searched high and low for what i want and how to do it.

i'm trying to do it in excel as i'm finding the g.docs hamsters go on strike for some of the sheets (Fuzz's BPmats QUERY sheet for example (which i can't get to work in excel))

i've downloaded all relevant files from Fuzzworks, looked up god know's how many google sheets, made/manipulated a boat load of pivot tables, tediously gone trhough and deleted rows/columns containing stuff that's not relevant to what i'm trying to do but i'm still no closer to what i want Sad

i don't have much of a clue when it comes to excel/g.docs so any info/advice anyone has, could you ELI5

thanks
Blacksmoke16
Resilience.
#2 - 2017-05-13 13:11:23 UTC  |  Edited by: Blacksmoke16
So all you want is the names of the bpos? No other information?

EDIT:

MySQL statement for it:
SELECT 
    bpI.typeName
FROM
    industryactivitymaterials bp
        JOIN
    invTypes bpI ON bp.typeID = bpI.typeID
        JOIN
    invTypes matI ON bp.materialTypeID = matI.typeID
WHERE
    bp.activityID = 1
    and bpI.published = true
GROUP BY bpI.typeName
HAVING GROUP_CONCAT(DISTINCT matI.groupID) = '18'


CSV File of it:
https://drive.google.com/open?id=0B0aKNcciVJ-UUEpvclEzVUh0ZDA
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2017-05-13 17:01:28 UTC
As blacksmoke shows, it's _far_ easier to do this in a a database, than in a spreadsheet.

I highly recommend getting a copy (from my site) and using an odbc connection with excel to query it. Either mysql or sqlite (postgres is best, but more painful to work with if you don't know what you're doing)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Simon Hashur
Imperial Academy
Amarr Empire
#4 - 2017-05-20 14:18:25 UTC
thanks for the replies, i appreciate it.

also, sorry for the late reply.

@Blacksmoke

yes mate, i was after the minerals for them too. i forgot to specify in my original post (rush rush rush!! lol!!)

the list you provided was still very helpful though so thankyou for that Smile i managed to filter out the vast majority of what i didn't need from it. after that, i went through and removed faction/special edition items.

pivot table and filtering got me the mineral requirements for them too.

all i need to do now is figure out an easy way to pull all the market info i require for them into a spread.

@Steve

thanks for the advice. i think databases and SQL stuff is a bit beyond my scope atm but it is something i'm interested in learning though.

do you have a link to the one you mentioned on your site? i have had a look around but can't see it.
Blacksmoke16
Resilience.
#5 - 2017-05-20 16:21:04 UTC
With each mineral/quantity:

SELECT 
    bpI.typeName,
    bpI.typeID,
    matI.typeName,
    bp.quantity
FROM
    industryactivitymaterials bp
        JOIN
    invTypes bpI ON bp.typeID = bpI.typeID
        JOIN
    invTypes matI ON bp.materialTypeID = matI.typeID
WHERE
    bp.activityID = 1
    and bpI.published = true
GROUP BY bpI.typeName, matI.typeName
HAVING GROUP_CONCAT(DISTINCT matI.groupID) = '18'


https://drive.google.com/open?id=0B0aKNcciVJ-UTEU3Z0trOEpqNE0

I included the typeID for the item as well. You can use https://github.com/nuadi/googlecrestscript to get market prices given IDs if using Google sheets.
guigui lechat
the no fock given
#6 - 2017-05-21 15:09:03 UTC
what he wants I guess is a spreadsheet containing all the material requirement of the BPos so to know the build cost of each.
Simon Hashur
Imperial Academy
Amarr Empire
#7 - 2017-05-24 21:54:12 UTC
Quote:
guigui lechat
Posted: 2017.05.21 15:09

what he wants I guess is a spreadsheet containing all the material requirement of the BPos so to know the build cost of each.


yes, that is what i'm aiming to do.

i'd like to try and do/learn as much as i can without being spoon fed everything or being given a completed project. i'll have a better understanding of it then.

asking for help/pointers is something i'll do when i really get stuck on something.

Quote:
Blacksmoke16
Posted: 2017.05.20 16:21

With each mineral/quantity:

SELECT
bpI.typeName,
bpI.typeID,
matI.typeName,
bp.quantity
FROM
industryactivitymaterials bp
JOIN
invTypes bpI ON bp.typeID = bpI.typeID
JOIN
invTypes matI ON bp.materialTypeID = matI.typeID
WHERE
bp.activityID = 1
and bpI.published = true
GROUP BY bpI.typeName, matI.typeName
HAVING GROUP_CONCAT(DISTINCT matI.groupID) = '18'



https://drive.google.com/open?id=0B0aKNcciVJ-UTEU3Z0trOEpqNE0

I included the typeID for the item as well. You can use https://github.com/nuadi/googlecrestscript to get market prices given IDs if using Google sheets.
'


again, thanks for the info, i appreciate it :)

i did manage to get to there with excel but your query/statement will deffo come in useful if i move on to SQL.

i was a bit dissappointed to see that the doc. would only pull 10-20 items at a time though. i'd like to be able to pull a lot more than that tbh.