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