def run(results, cmdenv, tdb):
from commands.commandenv import ResultRow
if cmdenv.lt and cmdenv.gt:
if cmdenv.lt <= cmdenv.gt:
raise CommandLineError("--gt must be lower than --lt")
item = tdb.lookupItem(cmdenv.item)
cmdenv.DEBUG0("Looking up item {} (#{})", item.name(), item.ID)
avoidSystems = {s for s in cmdenv.avoidPlaces if isinstance(s, System)}
avoidStations = {s for s in cmdenv.avoidPlaces if isinstance(s, Station)}
results.summary = ResultRow()
results.summary.item = item
results.summary.avoidSystems = avoidSystems
results.summary.avoidStations = avoidStations
if cmdenv.detail:
avgPrice = tdb.query("""
SELECT AVG(si.demand_price)
FROM StationItem AS si
WHERE si.item_id = ? AND si.demand_price > 0
""", [item.ID]).fetchone()[0]
results.summary.avg = int(avgPrice)
# Constraints
tables = "StationItem AS si"
constraints = [
"(item_id = {} AND demand_price > 0)".format(item.ID),
]
columns = [
'si.station_id',
'si.demand_price',
'si.demand_units',
]
bindValues = []
if cmdenv.demand:
constraints.append("(demand_units >= ?)")
bindValues.append(cmdenv.demand)
if cmdenv.lt:
constraints.append("(demand_price < ?)")
bindValues.append(cmdenv.lt)
if cmdenv.gt:
constraints.append("(demand_price > ?)")
bindValues.append(cmdenv.gt)
nearSystem = cmdenv.nearSystem
if nearSystem:
maxLy = cmdenv.maxLyPer or tdb.maxSystemLinkLy
results.summary.near = nearSystem
results.summary.ly = maxLy
distanceFn = nearSystem.distanceTo
else:
distanceFn = None
whereClause = ' AND '.join(constraints)
stmt = """SELECT DISTINCT {columns} FROM {tables} WHERE {where}""".format(
columns=','.join(columns),
tables=tables,
where=whereClause
)
cmdenv.DEBUG0('SQL: {}', stmt)
cur = tdb.query(stmt, bindValues)
stationByID = tdb.stationByID
padSize = cmdenv.padSize
planetary = cmdenv.planetary
wantNoPlanet = cmdenv.noPlanet
wantBlackMarket = cmdenv.blackMarket
for (stationID, priceCr, demand) in cur:
station = stationByID[stationID]
if padSize and not station.checkPadSize(padSize):
continue
if planetary and not station.checkPlanetary(planetary):
continue
if wantNoPlanet and station.planetary != 'N':
continue
if wantBlackMarket and station.blackMarket != 'Y':
continue
if station in avoidStations:
continue
if station.system in avoidSystems:
continue
row = ResultRow()
row.station = station
if distanceFn:
distance = distanceFn(row.station.system)
if distance > maxLy:
continue
row.dist = distance
row.price = priceCr
row.demand = demand
row.age = station.itemDataAgeStr
results.rows.append(row)
if not results.rows:
raise NoDataError("No available items found")
results.summary.sort = "Price"
results.rows.sort(key=lambda result: result.demand, reverse=True)
results.rows.sort(key=lambda result: result.price, reverse=True)
if nearSystem and not cmdenv.sortByPrice:
results.summary.sort = "Dist"
results.rows.sort(key=lambda result: result.dist)
limit = cmdenv.limit or 0
if limit > 0:
results.rows = results.rows[:limit]
return results
def run(results, cmdenv, tdb):
from commands.commandenv import ResultRow
cmdenv = results.cmdenv
tdb = cmdenv.tdb
srcSystem = cmdenv.nearSystem
results.summary = ResultRow()
results.limit = cmdenv.limit
fields = [
"si.station_id",
"JULIANDAY('NOW') - JULIANDAY(MAX(si.modified))",
"stn.ls_from_star",
]
joins = []
wheres = []
havings = []
if cmdenv.minAge:
wheres.append(
"(JULIANDAY('NOW') - JULIANDAY(si.modified) >= {})".format(
cmdenv.minAge))
nearSys = cmdenv.nearSystem
if nearSys:
maxLy = cmdenv.maxLyPer or tdb.maxSystemLinkLy
maxLy2 = maxLy**2
fields.append("dist2("
"sys.pos_x, sys.pos_y, sys.pos_z,"
"{}, {}, {}"
") AS d2".format(
nearSys.posX,
nearSys.posY,
nearSys.posZ,
))
joins.append("INNER JOIN System sys USING (system_id)")
wheres.append("""(
sys.pos_x BETWEEN {} and {}
AND sys.pos_y BETWEEN {} and {}
AND sys.pos_z BETWEEN {} and {}
)""".format(
nearSys.posX - maxLy,
nearSys.posX + maxLy,
nearSys.posY - maxLy,
nearSys.posY + maxLy,
nearSys.posZ - maxLy,
nearSys.posZ + maxLy,
))
havings.append("d2 <= {}".format(maxLy2))
else:
fields.append("0")
fieldStr = ','.join(fields)
if joins:
joinStr = ' '.join(joins)
else:
joinStr = ''
if wheres:
whereStr = 'WHERE ' + ' AND '.join(wheres)
else:
whereStr = ''
if havings:
haveStr = 'HAVING ' + ' AND '.join(havings)
else:
haveStr = ''
stmt = """
SELECT {fields}
FROM StationItem as si
INNER JOIN Station stn USING (station_id)
{joins}
{wheres}
GROUP BY 1
{having}
ORDER BY 2 DESC
""".format(
fields=fieldStr,
joins=joinStr,
wheres=whereStr,
having=haveStr,
)
cmdenv.DEBUG1(stmt)
for (stnID, age, ls, dist2) in tdb.query(stmt):
cmdenv.DEBUG2("{}:{}:{}", stnID, age, ls)
row = ResultRow()
row.station = tdb.stationByID[stnID]
row.age = age
if ls:
row.ls = "{:n}".format(ls)
else:
row.ls = "?"
row.dist = dist2**0.5
results.rows.append(row)
if cmdenv.route and len(results.rows) > 1:
def walk(startNode, dist):
rows = results.rows
startNode = rows[startNode]
openList = set(rows)
path = [startNode]
openList.remove(startNode)
while len(path) < len(rows):
lastNode = path[-1]
distFn = lastNode.station.system.distanceTo
nearest = min(openList,
key=lambda row: distFn(row.station.system))
openList.remove(nearest)
path.append(nearest)
dist += distFn(nearest.station.system)
return (path, dist)
if cmdenv.near:
bestPath = walk(0, results.rows[0].dist)
else:
bestPath = (results.rows, float("inf"))
for i in range(len(results.rows)):
path = walk(i, 0)
if path[1] < bestPath[1]:
bestPath = path
results.rows[:] = bestPath[0]
if cmdenv.limit:
results.rows[:] = results.rows[:cmdenv.limit]
return results
def run(results, cmdenv, tdb):
if cmdenv.lt and cmdenv.gt:
if cmdenv.lt <= cmdenv.gt:
raise CommandLineError("--gt must be lower than --lt")
# Find out what we're looking for.
queries, mode = get_lookup_list(cmdenv, tdb)
cmdenv.DEBUG0("{} query: {}", mode, queries.values())
avoidSystems = {s for s in cmdenv.avoidPlaces if isinstance(s, System)}
avoidStations = {s for s in cmdenv.avoidPlaces if isinstance(s, Station)}
# Summarize
results.summary = ResultRow()
results.summary.mode = mode
results.summary.queries = queries
results.summary.oneStop = cmdenv.oneStop
results.summary.avoidSystems = avoidSystems
results.summary.avoidStations = avoidStations
# In single mode with detail enabled, add average reports.
# Thus if you're looking up "algae" or the "asp", it'll
# tell you the average/ship cost.
singleMode = len(queries) == 1
if singleMode and cmdenv.detail:
first = list(queries.values())[0]
if mode is SHIP_MODE:
results.summary.avg = first.cost
else:
avgPrice = tdb.query(
"""
SELECT AVG(si.supply_price)
FROM StationItem AS si
WHERE si.item_id = ? AND si.supply_price > 0
""", [first.ID]).fetchone()[0]
if not avgPrice:
avgPrice = 0
results.summary.avg = int(avgPrice)
# System-based search
nearSystem = cmdenv.nearSystem
if nearSystem:
maxLy = cmdenv.maxLyPer or tdb.maxSystemLinkLy
results.summary.near = nearSystem
results.summary.ly = maxLy
distanceFn = nearSystem.distanceTo
else:
distanceFn = None
oneStopMode = cmdenv.oneStop
padSize = cmdenv.padSize
planetary = cmdenv.planetary
wantNoPlanet = cmdenv.noPlanet
wantBlackMarket = cmdenv.blackMarket
stations = defaultdict(list)
stationByID = tdb.stationByID
cur = sql_query(cmdenv, tdb, queries, mode)
for (ID, stationID, price, units) in cur:
station = stationByID[stationID]
if padSize and not station.checkPadSize(padSize):
continue
if planetary and not station.checkPlanetary(planetary):
continue
if wantNoPlanet and station.planetary != 'N':
continue
if wantBlackMarket and station.blackMarket != 'Y':
continue
if station in avoidStations:
continue
if station.system in avoidSystems:
continue
row = ResultRow()
row.station = station
if distanceFn:
distance = distanceFn(row.station.system)
if distance > maxLy:
continue
row.dist = distance
row.item = queries[ID]
row.price = price
row.units = units
row.age = station.itemDataAgeStr
if oneStopMode:
stationRows = stations[stationID]
stationRows.append(row)
if len(stationRows) >= len(queries):
results.rows.extend(stationRows)
else:
results.rows.append(row)
if not results.rows:
if oneStopMode and len(stations):
raise NoDataError("No one-stop stations found")
raise NoDataError("No available items found")
if oneStopMode and not singleMode:
results.rows.sort(key=lambda result: result.item.name())
results.rows.sort(key=lambda result: result.station.name())
if cmdenv.sortByUnits:
results.summary.sort = "units"
results.rows.sort(key=lambda result: result.price)
results.rows.sort(key=lambda result: result.units, reverse=True)
else:
if not oneStopMode:
results.summary.sort = "Price"
results.rows.sort(key=lambda result: result.units, reverse=True)
results.rows.sort(key=lambda result: result.price)
if nearSystem and not cmdenv.sortByPrice:
results.summary.sort = "Ly"
results.rows.sort(key=lambda result: result.dist)
limit = cmdenv.limit or 0
if limit > 0:
results.rows = results.rows[:limit]
return results
def run(results, cmdenv, tdb):
from commands.commandenv import ResultRow
if cmdenv.lt and cmdenv.gt:
if cmdenv.lt <= cmdenv.gt:
raise CommandLineError("--gt must be lower than --lt")
item = tdb.lookupItem(cmdenv.item)
cmdenv.DEBUG0("Looking up item {} (#{})", item.name(), item.ID)
avoidSystems = {s for s in cmdenv.avoidPlaces if isinstance(s, System)}
avoidStations = {s for s in cmdenv.avoidPlaces if isinstance(s, Station)}
results.summary = ResultRow()
results.summary.item = item
results.summary.avoidSystems = avoidSystems
results.summary.avoidStations = avoidStations
if cmdenv.detail:
avgPrice = tdb.query("""
SELECT AVG(si.demand_price)
FROM StationItem AS si
WHERE si.item_id = ? AND si.demand_price > 0
""", [item.ID]).fetchone()[0]
results.summary.avg = int(avgPrice)
# Constraints
tables = "StationItem AS si"
constraints = [
"(item_id = {} AND demand_price > 0)".format(item.ID),
]
columns = [
'si.station_id',
'si.demand_price',
'si.demand_units',
]
bindValues = []
if cmdenv.demand:
constraints.append("(demand_units >= ?)")
bindValues.append(cmdenv.demand)
if cmdenv.lt:
constraints.append("(demand_price < ?)")
bindValues.append(cmdenv.lt)
if cmdenv.gt:
constraints.append("(demand_price > ?)")
bindValues.append(cmdenv.gt)
nearSystem = cmdenv.nearSystem
if nearSystem:
maxLy = cmdenv.maxLyPer or tdb.maxSystemLinkLy
results.summary.near = nearSystem
results.summary.ly = maxLy
distanceFn = nearSystem.distanceTo
else:
distanceFn = None
whereClause = ' AND '.join(constraints)
stmt = """SELECT DISTINCT {columns} FROM {tables} WHERE {where}""".format(
columns=','.join(columns),
tables=tables,
where=whereClause
)
cmdenv.DEBUG0('SQL: {}', stmt)
cur = tdb.query(stmt, bindValues)
stationByID = tdb.stationByID
padSize = cmdenv.padSize
wantBlackMarket = cmdenv.blackMarket
for (stationID, priceCr, demand) in cur:
station = stationByID[stationID]
if padSize and not station.checkPadSize(padSize):
continue
if wantBlackMarket and station.blackMarket != 'Y':
continue
if station in avoidStations:
continue
if station.system in avoidSystems:
continue
row = ResultRow()
row.station = station
if distanceFn:
distance = distanceFn(row.station.system)
if distance > maxLy:
continue
row.dist = distance
row.price = priceCr
row.demand = demand
row.age = station.itemDataAgeStr
results.rows.append(row)
if not results.rows:
raise NoDataError("No available items found")
results.summary.sort = "Price"
results.rows.sort(key=lambda result: result.demand, reverse=True)
results.rows.sort(key=lambda result: result.price, reverse=True)
if nearSystem and not cmdenv.sortByPrice:
results.summary.sort = "Dist"
results.rows.sort(key=lambda result: result.dist)
limit = cmdenv.limit or 0
if limit > 0:
results.rows = results.rows[:limit]
return results
def run(results, cmdenv, tdb):
if cmdenv.lt and cmdenv.gt:
if cmdenv.lt <= cmdenv.gt:
raise CommandLineError("--gt must be lower than --lt")
# Find out what we're looking for.
queries, mode = get_lookup_list(cmdenv, tdb)
cmdenv.DEBUG0("{} query: {}", mode, queries.values())
avoidSystems = {s for s in cmdenv.avoidPlaces if isinstance(s, System)}
avoidStations = {s for s in cmdenv.avoidPlaces if isinstance(s, Station)}
# Summarize
results.summary = ResultRow()
results.summary.mode = mode
results.summary.queries = queries
results.summary.oneStop = cmdenv.oneStop
results.summary.avoidSystems = avoidSystems
results.summary.avoidStations = avoidStations
# In single mode with detail enabled, add average reports.
# Thus if you're looking up "algae" or the "asp", it'll
# tell you the average/ship cost.
singleMode = len(queries) == 1
if singleMode and cmdenv.detail:
first = list(queries.values())[0]
if mode is SHIP_MODE:
results.summary.avg = first.cost
else:
avgPrice = tdb.query("""
SELECT AVG(si.supply_price)
FROM StationItem AS si
WHERE si.item_id = ? AND si.supply_price > 0
""", [first.ID]).fetchone()[0]
results.summary.avg = int(avgPrice)
# System-based search
nearSystem = cmdenv.nearSystem
if nearSystem:
maxLy = cmdenv.maxLyPer or tdb.maxSystemLinkLy
results.summary.near = nearSystem
results.summary.ly = maxLy
distanceFn = nearSystem.distanceTo
else:
distanceFn = None
oneStopMode = cmdenv.oneStop
padSize = cmdenv.padSize
wantBlackMarket = cmdenv.blackMarket
stations = defaultdict(list)
stationByID = tdb.stationByID
cur = sql_query(cmdenv, tdb, queries, mode)
for (ID, stationID, price, units) in cur:
station = stationByID[stationID]
if padSize and not station.checkPadSize(padSize):
continue
if wantBlackMarket and station.blackMarket != 'Y':
continue
if station in avoidStations:
continue
if station.system in avoidSystems:
continue
row = ResultRow()
row.station = station
if distanceFn:
distance = distanceFn(row.station.system)
if distance > maxLy:
continue
row.dist = distance
row.item = queries[ID]
row.price = price
row.units = units
row.age = station.itemDataAgeStr
if oneStopMode:
stationRows = stations[stationID]
stationRows.append(row)
if len(stationRows) >= len(queries):
results.rows.extend(stationRows)
else:
results.rows.append(row)
if not results.rows:
if oneStopMode and len(stations):
raise NoDataError("No one-stop stations found")
raise NoDataError("No available items found")
if oneStopMode and not singleMode:
results.rows.sort(key=lambda result: result.item.name())
results.rows.sort(key=lambda result: result.station.name())
if cmdenv.sortByUnits:
results.summary.sort = "units"
results.rows.sort(key=lambda result: result.price)
results.rows.sort(key=lambda result: result.units, reverse=True)
else:
if not oneStopMode:
results.summary.sort = "Price"
results.rows.sort(key=lambda result: result.units, reverse=True)
results.rows.sort(key=lambda result: result.price)
if nearSystem and not cmdenv.sortByPrice:
results.summary.sort = "Ly"
results.rows.sort(key=lambda result: result.dist)
limit = cmdenv.limit or 0
if limit > 0:
results.rows = results.rows[:limit]
return results
def run(results, cmdenv, tdb):
from commands.commandenv import ResultRow
cmdenv = results.cmdenv
tdb = cmdenv.tdb
srcSystem = cmdenv.nearSystem
results.summary = ResultRow()
results.limit = cmdenv.limit
fields = [
"si.station_id",
"JULIANDAY('NOW') - JULIANDAY(MAX(si.modified))",
"stn.ls_from_star",
]
joins = []
wheres = []
havings = []
if cmdenv.minAge:
wheres.append(
"(JULIANDAY('NOW') - JULIANDAY(si.modified) >= {})"
.format(cmdenv.minAge)
)
nearSys = cmdenv.nearSystem
if nearSys:
maxLy = cmdenv.maxLyPer or tdb.maxSystemLinkLy
maxLy2 = maxLy ** 2
fields.append(
"dist2("
"sys.pos_x, sys.pos_y, sys.pos_z,"
"{}, {}, {}"
") AS d2".format(
nearSys.posX,
nearSys.posY,
nearSys.posZ,
))
joins.append("INNER JOIN System sys USING (system_id)")
wheres.append("""(
sys.pos_x BETWEEN {} and {}
AND sys.pos_y BETWEEN {} and {}
AND sys.pos_z BETWEEN {} and {}
)""".format(
nearSys.posX - maxLy,
nearSys.posX + maxLy,
nearSys.posY - maxLy,
nearSys.posY + maxLy,
nearSys.posZ - maxLy,
nearSys.posZ + maxLy,
))
havings.append("d2 <= {}".format(maxLy2))
else:
fields.append("0")
fieldStr = ','.join(fields)
if joins:
joinStr = ' '.join(joins)
else:
joinStr = ''
if wheres:
whereStr = 'WHERE ' + ' AND '.join(wheres)
else:
whereStr = ''
if havings:
haveStr = 'HAVING ' + ' AND '.join(havings)
else:
haveStr = ''
stmt = """
SELECT {fields}
FROM StationItem as si
INNER JOIN Station stn USING (station_id)
{joins}
{wheres}
GROUP BY 1
{having}
ORDER BY 2 DESC
""".format(
fields=fieldStr,
joins=joinStr,
wheres=whereStr,
having=haveStr,
)
cmdenv.DEBUG1(stmt)
for (stnID, age, ls, dist2) in tdb.query(stmt):
cmdenv.DEBUG2("{}:{}:{}", stnID, age, ls)
row = ResultRow()
row.station = tdb.stationByID[stnID]
row.age = age
if ls:
row.ls = "{:n}".format(ls)
else:
row.ls = "?"
row.dist = dist2 ** 0.5
results.rows.append(row)
if cmdenv.route and len(results.rows) > 1:
def walk(startNode, dist):
rows = results.rows
startNode = rows[startNode]
openList = set(rows)
path = [startNode]
openList.remove(startNode)
while len(path) < len(rows):
lastNode = path[-1]
distFn = lastNode.station.system.distanceTo
nearest = min(openList, key=lambda row: distFn(row.station.system))
openList.remove(nearest)
path.append(nearest)
dist += distFn(nearest.station.system)
return path, dist
if cmdenv.near:
bestPath = walk(0, results.rows[0].dist)
else:
bestPath = (results.rows, float("inf"))
for i in range(len(results.rows)):
path = walk(i, 0)
if path[1] < bestPath[1]:
bestPath = path
results.rows[:] = bestPath[0]
if cmdenv.limit:
results.rows[:] = results.rows[:cmdenv.limit]
return results
