def system_search(self, name):
cherrypy.response.headers['Content-Type'] = 'text/json'
db = evec_func.db_con()
cur = db.cursor();
cur_r = db.cursor();
name = name + '%';
cur.execute("SELECT systemid,systemname,regionname FROM systems,regions WHERE regions.regionid = systems.regionid AND systemname ILIKE %s ORDER BY systemname", [name])
cur_r.execute("SELECT regionid,regionname FROM regions WHERE regionname ILIKE %s ORDER BY regionname LIMIT 30", [name])
json_list = []
r = cur.fetchone()
while r:
m = { 'id' : r[0],
'name' : r[1],
'type' : 'System in ' + r[2]}
json_list.append(m)
r = cur.fetchone()
r = cur_r.fetchone()
while r:
m = {'id' : r[0],
'name' : r[1] ,
'type': 'Region'}
r = cur_r.fetchone()
json_list.append(m)
return simplejson.dumps({'results' : json_list})
def system_search(self, name):
cherrypy.response.headers['Content-Type'] = 'text/json'
db = evec_func.db_con()
cur = db.cursor()
cur_r = db.cursor()
name = name + '%'
cur.execute(
"SELECT systemid,systemname,regionname FROM systems,regions WHERE regions.regionid = systems.regionid AND systemname ILIKE %s ORDER BY systemname",
[name])
cur_r.execute(
"SELECT regionid,regionname FROM regions WHERE regionname ILIKE %s ORDER BY regionname LIMIT 30",
[name])
json_list = []
r = cur.fetchone()
while r:
m = {'id': r[0], 'name': r[1], 'type': 'System in ' + r[2]}
json_list.append(m)
r = cur.fetchone()
r = cur_r.fetchone()
while r:
m = {'id': r[0], 'name': r[1], 'type': 'Region'}
r = cur_r.fetchone()
json_list.append(m)
return simplejson.dumps(json_list)
def market(self, regionlimit=None, pickregion=None, empire=""):
session = EVCstate()
db = evec_func.db_con()
t = display.template("market.tmpl", session)
if regionlimit is None:
regionlimit = []
if not isinstance(regionlimit, list):
regionlimit = [regionlimit]
regionlimit = map(int, regionlimit)
if pickregion is not None:
if empire:
regionlimit = empireregions()
session["regionlimit"] = regionlimit
else:
if "regionlimit" in session:
regionlimit = session["regionlimit"]
else:
regionlimit = []
session["regionlimit"] = []
t.regionlimit = regionlimit
t.regions = evec_func.region_list(db)
# t.types = evec_func.type_list(db)
t.types = []
db.close()
return t.respond()
def setapikeys(self, full_apikey = None, apiuserid = None, error = 0):
session = EVCstate(trust=True)
db = evec_func.db_con()
user = User.get(session, db)
if not user.valid:
return
t = display.template('user_setapikeys.tmpl', session)
t.errormsg = ""
t.full_apikey = user.full_apikey
t.apiuserid = user.apiuserid
if full_apikey is not None and full_apikey != "":
user.full_apikey = full_apikey
user.apiuserid = apiuserid
session['user'] = user
user.update_user(db)
session.save()
t.full_apikey = user.full_apikey
t.apiuserid = user.apiuserid
if error == 1:
t.errormsg = "We couldn't access the API services with the keys below - please double check your input"
return t.respond()
def login(self, username, password):
session = EVCstate(trust=True)
db = evec_func.db_con()
res = None
r = User.login(db, session, username, password)
if r is not False:
if 'isigb' not in session or not session['isigb']:
emit_redirect('/users/index.html')
res = "<html><head><title>Logged in</title></head><body>"
res += "Logged in! Go to <a href=/users/index.html>user home</a>. You are getting this page because the IGB does not know how to redirect."
res += "</body></html>"
session['user'] = r
else:
res = "<html><head><title>Login failed</title></head><body>"
res += "Your login failed due to a bad password or username."
res += "<form method=GET action=/users/passreset.html>Send a reset email for the user " + username
res += " to the email address <input type=text name=uemail> (must match email on file!)"
res += "<input type=hidden name=username value=\""+username+"\"><input type=submit value=Send>"
res += "</form>"
res += "</body></html>"
db.close()
return res
def market(self, regionlimit=None, pickregion=None, empire=""):
session = EVCstate()
db = evec_func.db_con()
t = display.template('market.tmpl', session)
if regionlimit is None:
regionlimit = []
if not isinstance(regionlimit, list):
regionlimit = [regionlimit]
regionlimit = map(int, regionlimit)
if pickregion is not None:
if empire:
regionlimit = empireregions()
session['regionlimit'] = regionlimit
else:
if 'regionlimit' in session:
regionlimit = session['regionlimit']
else:
regionlimit = []
session['regionlimit'] = []
t.regionlimit = regionlimit
t.regions = evec_func.region_list(db)
#t.types = evec_func.type_list(db)
t.types = []
db.close()
return t.respond()
def register(self, password,email):
session = EVCstate(trust=True)
charname = None
if 'Eve-Charname' in dict(cherrypy.request.headers):
charname = cherrypy.request.headers['Eve-Charname']
if charname is None:
return evec_func.simple_error("No username found?")
if password == "":
return evec_func.simple_error("Please specify a password")
if '@' not in email:
return evec_func.simple_error("Please specify a semi-valid email address")
db = evec_func.db_con()
password = password.strip()
r = User.register(db, password,email)
if r is False:
db.close()
return evec_func.simple_error("Error: Registration error. You may already be registered or the system messed up")
User.login(db, session, charname, password)
emit_redirect('/users/')
return """<html><head><title>Hi</title></head><body>
def market(self, regionlimit=None, pickregion=None, empire=""):
session = EVCstate()
db = evec_func.db_con()
t = display.template("market.tmpl", session)
if regionlimit is None:
regionlimit = []
if not isinstance(regionlimit, list):
regionlimit = [regionlimit]
regionlimit = map(int, regionlimit)
empireregions = [
10000001,
10000002,
10000016,
10000020,
10000028,
10000030,
10000032,
10000033,
10000043,
10000049,
10000037,
10000038,
10000036,
10000052,
10000064,
10000065,
10000067,
10000068,
10000054,
10000042,
10000044,
10000048,
]
if pickregion is not None:
if empire:
regionlimit = empireregions
session["regionlimit"] = regionlimit
else:
if "regionlimit" in session:
regionlimit = session["regionlimit"]
else:
regionlimit = []
session["regionlimit"] = []
t.regionlimit = regionlimit
t.regions = evec_func.region_list(db)
# t.types = evec_func.type_list(db)
t.types = []
db.close()
return t.respond()
def tradefind_search(self, qtype, fromt, to):
session = EVCstate()
t = display.template('tradefind_search.tmpl', session)
db = evec_func.db_con()
cur_f = db.cursor()
cur_t = db.cursor()
t.qtype = qtype
t.fromt = fromt
t.to = to
fromt = "%" + fromt + "%"
to = "%" + to + "%"
if qtype == "Systems":
cur_f.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemname ILIKE %s ORDER BY systemname",
[fromt])
cur_t.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemname ILIKE %s ORDER BY systemname",
[to])
elif qtype == "Regions":
cur_f.execute(
"SELECT regionid,regionname FROM regions WHERE regionname ILIKE %s ORDER BY regionname",
[fromt])
cur_t.execute(
"SELECT regionid,regionname FROM regions WHERE regionname ILIKE %s ORDER BY regionname",
[to])
elif qtype == "SystemToRegion":
cur_f.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemname ILIKE %s ORDER BY systemname",
[fromt])
cur_t.execute(
"SELECT regionid,regionname FROM regions WHERE regionname ILIKE %s ORDER BY regionname",
[to])
from_list = []
to_list = []
for (q, list) in [(cur_f, from_list), (cur_t, to_list)]:
r = q.fetchone()
while r:
d = {}
d['id'] = r[0]
d['name'] = r[1]
list.append(d)
r = q.fetchone()
t.from_list = from_list
t.to_list = to_list
db.close()
session.save()
return t.respond()
def tradefind_search(self, qtype, fromt, to):
session = EVCstate()
t = display.template("tradefind_search.tmpl", session)
db = evec_func.db_con()
cur_f = db.cursor()
cur_t = db.cursor()
t.qtype = qtype
t.fromt = fromt
t.to = to
fromt = "%" + fromt + "%"
to = "%" + to + "%"
if qtype == "Systems":
cur_f.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemname ILIKE %s ORDER BY systemname",
[fromt],
)
cur_t.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemname ILIKE %s ORDER BY systemname",
[to],
)
elif qtype == "Regions":
cur_f.execute(
"SELECT regionid,regionname FROM regions WHERE regionname ILIKE %s ORDER BY regionname", [fromt]
)
cur_t.execute("SELECT regionid,regionname FROM regions WHERE regionname ILIKE %s ORDER BY regionname", [to])
elif qtype == "SystemToRegion":
cur_f.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemname ILIKE %s ORDER BY systemname",
[fromt],
)
cur_t.execute("SELECT regionid,regionname FROM regions WHERE regionname ILIKE %s ORDER BY regionname", [to])
from_list = []
to_list = []
for (q, list) in [(cur_f, from_list), (cur_t, to_list)]:
r = q.fetchone()
while r:
d = {}
d["id"] = r[0]
d["name"] = r[1]
list.append(d)
r = q.fetchone()
t.from_list = from_list
t.to_list = to_list
db.close()
session.save()
return t.respond()
def upload_suggest(self, region = None):
cherrypy.response.headers['Content-Type'] = 'text/xml'
region = int(region)
db = evec_func.db_con()
sug = suggest.upload_suggest(db, region, "both")
t = display.template('upload_suggest_xml.tmpl', None)
t.suggest = sug
db.close()
return t.respond()
def logout(self):
session = EVCstate(trust=True)
db = evec_func.db_con()
try:
session['user'] = None
del session['user']
except:
pass
return emit_redirect('/')
def view_page(self, ticker, page="index", retry = True):
session = EVCstate(trust=True)
db = evec_func.db_con()
user = User.get(session, db)
corp = None
if user.valid:
corp = Corp(db, user.corpid)
cur = db.cursor()
cur.execute("SELECT corps.corpid,contents,title,view,edit FROM corppages,corps WHERE corppages.corpid = corps.corpid AND corps.ticker = %s AND corppages.pagename = %s",
[ticker,page])
r = cur.fetchone()
if r:
view = r[3]
page_corp = Corp(db, long(r[0]))
t = display.template('corpviewpage.tmpl', session)
t.canedit = False
print view
if user.valid:
if view == "corp only" and not long(r[0]) == corp.corpid and not user.ismember:
return evec_func.simple_error("You are not authorized to view this page")
if view == "director only" and not long(r[0]) == corp.corpid and not user.isdirector:
return evec_func.simple_error("You are not authorized to view this page")
t.canedit = user.isdirector and long(r[0]) == corp.corpid
else:
if view != "public":
return evec_func.simple_error("You are not authorized to view this page.")
t.pcontents = r[1]
t.ptitle = r[2]
t.view = r[3]
t.pagename = page
t.pedit = r[4]
t.corp = page_corp
db.close()
return t.respond()
else:
if retry:
return self.view_page(ticker, page[:-5], retry = False)
db.close()
return evec_func.simple_error("No such page: " + ticker + " page " + page)
def changepw(self, oldpw, newpw, newpw2):
session = EVCstate(trust=True)
db = evec_func.db_con()
if newpw != newpw2:
return evec_func.simple_error("Passwords do not match")
u = User.get(session, db)
u.change_pw(db, oldpw,newpw)
emit_redirect('/users/')
db.close()
def usercount(self, userid):
db = evec_func.db_con()
cur = db.cursor()
cur.execute('SELECT uploads FROM users WHERE userid = %s',
[userid])
r = cur.fetchone()
if r:
return str(r[0])
else:
return '0'
db.close()
def userlogin(self, username, password):
db = evec_func.db_con()
cur = db.cursor()
cur.execute('SELECT userid FROM users WHERE username = %s AND password = md5(%s)',
[username, User.salt(password, username)])
r = cur.fetchone()
if r:
return str(r[0])
else:
return '-1'
db.close()
def leaders(self):
session = EVCstate()
t = display.template('leaders.tmpl', session)
db = evec_func.db_con()
cur = db.cursor()
cur.execute("SELECT username,uploads,userid FROM users WHERE userid != 0 AND uploads > 0 ORDER BY uploads DESC LIMIT 100")
r = cur.fetchall()
r_d = []
t.results = r
db.close()
return t.respond()
def perform_stat(self):
regionid, typeid = self.queue.get()
db = evec_func.db_con()
minq = 0
if typeid in stats.MINQ_TYPES:
minq = stats.MINQ_VOL
(buy,sell) = stats.item_stat(db, typeid, regionlimit = [regionid], nocache = True, minQ = minq)
all_stat = stats.item_stat(db, typeid, regionlimit = [regionid], nocache = True, minQ = minq, buysell = False)
cur = db.cursor()
# Buyup not yet available in stats
#print "DEBUG stat: ",typeid,regionid,all_stat
cur.execute("INSERT INTO trends_type_region (typeid, region, average, median, volume, stddev, buyup, timeat) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW())",
(typeid, regionid, float(all_stat['avg_price']), float(all_stat['median']), float(all_stat['total_vol']), float(all_stat['stddev']), float(0)))
db.commit()
self.queue.task_done()
def advertise(self):
session = EVCstate(self, trust=True)
db = evec_func.db_con()
user = User.get(session, db)
if not user.valid:
return evec_func.simple_error("Not logged in")
if not user.isdirector:
return evec_func.simple_error("Not enough priveleges")
ucorp = Corp(db,user.corpid)
t = display.template('corpmanage.tmpl', session)
t.corp = ucorp
return t.respond()
def upload_suggest(self):
session = EVCstate()
igb = True
regionname = ""
db = evec_func.db_con()
try:
regionname = cherrypy.request.headers["Eve-Regionname"]
except:
igb = False
t = display.template("upload_suggest.tmpl", session)
if igb:
region = int(get_region_id(db, regionname))
sug = suggest.upload_suggest(db, region, "both")
t.suggest = sug
t.igb = igb
t.headers = cherrypy.request.headers
db.close()
return t.respond()
def register(self):
session = EVCstate(trust=True)
db = evec_func.db_con()
user = User.get(session, db)
if user.valid is False:
db.close()
return evec_func.simple_error("Not logged in")
if user.isdirector != 1:
db.close()
return evec_func.simple_error("Not director - only directors can do that")
r = Corp.create(db, user.corpid, user.corporation)
if r is False:
db.close()
return evec_func.simple_error("Corp exists")
emit_redirect('/corps/')
db.close()
def typesearch(self, search):
session = EVCstate()
t = display.template('typesearch.tmpl', session)
t.search = search
db = evec_func.db_con()
search = search.lower()
search = "%" + search + "%"
notmask = '%Blueprint'
if search.find('blueprint') != -1:
notmask = 'this wont ever exist'
cur = db.cursor()
cur.execute("SELECT typename,typeid FROM types WHERE typename ILIKE %s AND typename NOT ILIKE %s ORDER BY typename", [search, notmask])
types = []
item = cur.fetchone()
while item:
types.append({'typeid':item[1], 'typename':item[0]})
item = cur.fetchone()
t.types = types
db.close()
if len(types) == 1:
emit_redirect('/home/quicklook.html?typeid='+str(int(types[0]['typeid'])))
return
return t.respond()
def upload_suggest(self):
session = EVCstate()
igb = True
regionname = ''
db = evec_func.db_con()
try:
regionname = cherrypy.request.headers['Eve-Regionname']
except:
igb = False
t = display.template('upload_suggest.tmpl', session)
if igb:
region = int(get_region_id(db, regionname))
sug = suggest.upload_suggest(db, region, "both")
t.suggest = sug
t.igb = igb
t.headers = cherrypy.request.headers
db.close()
return t.respond()
def index(self, message=""):
session = EVCstate(trust=True)
db = evec_func.db_con()
user = User.get(session, db)
if user.valid is False:
t = display.template('registerlogin.tmpl', session)
if 'Eve-Charname' in dict(cherrypy.request.headers):
t.charname = cherrypy.request.headers['Eve-Charname']
else:
t.charname = ""
else:
t = display.template('usermain.tmpl', session)
t.charname = user.username
t.message = message
t.user = user
hdump = ""
for name in dict(cherrypy.request.headers):
hdump = hdump + name + ":" + cherrypy.request.headers[name] + "<br>"
return t.respond() + hdump
def evemon(self, hours = "360"):
cherrypy.response.headers['Content-Type'] = 'text/xml'
db = evec_func.db_con()
hours = int(hours)
response = ""
response += "<minerals>\n"
empireregions = [10000001, 10000002, 10000016, 10000020, 10000028, 10000030, 10000032, 10000033, 10000043, 10000049, 10000037, 10000038, 10000036, 10000052, 10000064, 10000065, 10000067, 10000068, 10000054, 10000042,10000044, 10000048 ]
for mineral in [34, 35, 36, 37, 38, 39, 40, 11399]:
prices = stats.item_stat(db, mineral, hours, buysell = False, minQ = 5000,
regionlimit = empireregions)
typename = evec_func.get_type_name(db, mineral)
response += " <mineral>\n"
response += " <name>"+typename+"</name>"
response += " <price>"+`prices['median']`+"</price>"
response += " </mineral>\n"
response += "</minerals>\n"
return response
def typesearch(self, search=""):
session = EVCstate()
t = display.template('typesearch.tmpl', session)
t.search = search
db = evec_func.db_con()
search = search.lower()
search = "%" + search + "%"
notmask = '%Blueprint'
if search.find('blueprint') != -1:
notmask = 'this wont ever exist'
cur = db.cursor()
cur.execute(
"SELECT typename,typeid FROM types WHERE typename ILIKE %s AND typename NOT ILIKE %s ORDER BY typename",
[search, notmask])
types = []
item = cur.fetchone()
while item:
types.append({'typeid': item[1], 'typename': item[0]})
item = cur.fetchone()
t.types = types
db.close()
if len(types) == 1:
emit_redirect('/home/quicklook.html?typeid=' +
str(int(types[0]['typeid'])))
return
return t.respond()
def passreset(self, username, uemail):
session = EVCstate(trust=True)
db = evec_func.db_con()
u = User.get(session, db)
email = u.get_email(db, username)
if email is False:
r = "<html><head><title>Password reset failed</title></head><body>"
r+= "Password reset failed. Please check your email address. "
r += "</body></html>"
db.close()
return r
if uemail.lower() != email.lower():
r = "<html><head><title>Email not match</title></head>"
r = "<body>The email address provided does not match the one on file - process aborted.</body></html>"
db.close()
return r
if email:
newpass = str(random.randint(10000,9000000))
msg = "Subject: EVE-Central.com Password Reset\nTo: " + email + "\nFrom: EVE-Central.com <*****@*****.**>\n\nThe password for username " + username + " has been reset to " + newpass
u.change_pw_name(db, username, newpass)
server = smtplib.SMTP('localhost')
server.sendmail("*****@*****.**", email, msg)
r = "<html><head><title>Password reset</title></head><body>"
r += "Password reset mail sent. Please check your inbox. Go to <a href=/users/>user home</a>"
r += "</body></html>"
db.close()
return r
def manage(self, set = 0, description = "", join_password = "", headquarters = "", ticker = ""):
session = EVCstate(trust=True)
db = evec_func.db_con()
user = User.get(session, db)
if not user.valid:
return evec_func.simple_error("Not logged in")
if not user.isdirector:
return evec_func.simple_error("Not enough priveleges")
ucorp = Corp(db,user.corpid)
if set:
ucorp.description = description
ucorp.headquarters = headquarters
ucorp.join_password = join_password
ucorp.ticker = ticker
ucorp.update(db)
t = display.template('corpmanage.tmpl', session)
t.corp = ucorp
return t.respond()
def tradefind_display(self,
qtype,
fromt,
to,
set=None,
age=24,
cashonhand=10000000.00,
minprofit=100000,
size=10000,
startat=0,
limit=50,
newsearch="0",
sort="jprofit",
prefer_sec="0"):
session = EVCstate()
t = display.template('tradefind_display.tmpl', session)
db = evec_func.db_con()
newsearch = int(newsearch)
if 'trade_results' not in session:
newsearch = 1
cur = db.cursor()
limit = int(limit)
prefer_sec = int(prefer_sec)
recalc_route = False
if sort not in ['jprofit', 'sprofit', 'profit', 'jumps']:
return
if set:
session['tf_age'] = age
session['tf_minprofit'] = minprofit
session['tf_size'] = size
session['tf_limit'] = limit
session['tf_sort'] = sort
session['tf_prefer_sec'] = prefer_sec
newsearch = 1
else:
if 'tf_age' in session:
age = session['tf_age']
if 'tf_minprofit' in session:
minprofit = session['tf_minprofit']
if 'tf_size' in session:
size = session['tf_size']
if 'tf_limit' in session:
limit = session['tf_limit']
if 'tf_sort' in session:
sort = session['tf_sort']
if 'tf_prefer_sec' in session:
prefer_sec = session['tf_prefer_sec']
else:
prefer_sec = 1
age_t = str(int(age)) + " hours"
size = str(int(size))
cashonhand = str(double(cashonhand))
sql_profit_size = """ (t.price - f.price)* min(""" + size + """, min(t.volremain,f.volremain) * types.size)/types.size """
sql_profit_jumps = """ '1' """
#sql_cash = """ f.price <= $(cashonhand)s """
sql_sec_limit = """ """
if prefer_sec:
sql_sec_limit = """ fsys.security > 0.4 AND tsys.security > 0.4 AND """
cur_f = db.cursor()
cur_t = db.cursor()
#####
# Warning: This is easily the worst piece of code I have ever written.
# I really am sorry.
#####
# Pure suck query
if qtype == "Systems" and newsearch:
cur.execute(
"""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)* min(t.volremain,f.volremain) AS profit,
""" + sql_profit_size + """ AS profit_size,
""" + sql_profit_jumps + """ AS profit_jumps,
t.price,f.price,
t.volremain,f.volremain
FROM
types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
""" + sql_sec_limit + """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.minvolume <= 1 AND t.minvolume <= 1 AND
f.bid = 0 AND t.bid = 1 AND f.systemid = %(fromt)s AND t.systemid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
""" + sql_profit_size + """ >= %(minprofit)s AND
types.size <= """ + size + """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""", {
'fromt': fromt,
'to': to,
'age': age_t,
'minprofit': minprofit,
})
cur_f.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname",
[fromt])
cur_t.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname",
[to])
elif qtype == "Regions" and newsearch:
cur.execute(
"""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)*min(t.volremain,f.volremain) AS profit,
""" + sql_profit_size + """ AS profit_size,
""" + sql_profit_jumps + """ AS profit_jumps,
t.price,f.price, t.volremain, f.volremain
FROM types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
""" + sql_sec_limit + """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.minvolume <= 1 AND t.minvolume <= 1 AND
f.bid = 0 AND t.bid = 1 AND f.regionid = %(fromt)s AND t.regionid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
""" + sql_profit_size + """ >= %(minprofit)s AND
types.size <= """ + size + """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""", {
'fromt': fromt,
'to': to,
'age': age_t,
'minprofit': minprofit,
})
cur_f.execute(
"SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname",
[fromt])
cur_t.execute(
"SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname",
[to])
elif qtype == "SystemToRegion" and newsearch:
cur.execute(
"""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)*min(t.volremain,f.volremain) AS profit,
""" + sql_profit_size + """ AS profit_size,
""" + sql_profit_jumps + """ AS profit_jumps,
t.price,f.price, t.volremain, f.volremain
FROM types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
""" + sql_sec_limit + """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.minvolume <= 1 AND t.minvolume <= 1 AND
f.bid = 0 AND t.bid = 1 AND f.systemid = %(fromt)s AND t.regionid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
""" + sql_profit_size + """ >= %(minprofit)s AND
types.size <= """ + size + """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""", {
'fromt': fromt,
'to': to,
'age': age_t,
'minprofit': minprofit,
})
cur_f.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname",
[fromt])
cur_t.execute(
"SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname",
[to])
trades = []
time_net = 0.0
import __builtin__
from_set = __builtin__.set()
from_map = {}
from_to_map = {}
error_log = []
fromcache = 0
if newsearch:
r = cur.fetchone()
while r:
row = SorterDict('sortby')
row['typeid'] = r[0]
row['typename'] = r[1]
fr = int(r[2])
to2 = int(r[3])
row['fr'] = int(r[2])
row['to2'] = int(r[3])
row['fromstation'] = r[4]
row['tostation'] = r[5]
row['pricediff'] = r[6]
row['tradeable'] = format_long(long(r[7]))
row['profit'] = format_price(float(r[8]))
row['profit_num'] = float(r[8])
row['profit_size'] = float(r[9])
from_set.add(fr)
row['tprice'] = format_price(float(r[11]))
row['fprice'] = format_price(float(r[12]))
row['tvol'] = format_long(long(r[13]))
row['fvol'] = format_long(long(r[14]))
trades.append(row)
# Add to from_to_map - map source
if fr in from_to_map:
from_to_map[fr].add(row['to2'])
else:
from_to_map[fr] = __builtin__.set()
from_to_map[fr].add(row['to2'])
if fr in from_map:
from_map[fr].append(row)
else:
from_map[fr] = []
from_map[fr].append(row)
r = cur.fetchone()
# Now we try to compute distance
for compsys in from_set:
distance_map = {}
distance = 0
time_net_ = time.time()
for tosystem in from_to_map[compsys]:
load = urllib.urlopen(
'http://127.0.0.1:8081/api/distance/from/' +
str(int(compsys)) + '/to/' + str(int(tosystem)))
s = load.read()
load.close()
distance_map[tosystem] = json.loads(s)['distance']
time_net += time.time() - time_net_
for row in from_map[compsys]:
if row['fr'] == compsys:
if compsys == row['to2']:
distance = 0
else:
distance = distance_map[row['to2']]
ps = row['profit_size']
row['profit_size'] = format_price(ps)
if sort == "jprofit":
row['sortby'] = ps / (max(0, distance) + 1)
t.sort_nice = "Profit per jump"
elif sort == "sprofit":
row['sortby'] = ps
t.sort_nice = "Profit per trip"
elif sort == "profit":
row['sortby'] = row['profit_num']
t.sort_nice = "Profit"
elif sort == "jumps":
t.sort_nice = "Distance"
row['sortby'] = distance
row.reverse = True
row['profit_jumps'] = format_price(
float(ps / (1 + max(0, distance))))
row['distance'] = distance
time_sort = time.time()
trades.sort(reverse=True)
time_sort = time.time() - time_sort
#session['trade_results'] = trades
else:
trades = session['trade_results']
fromcache = 1
trade_num = len(trades)
trades = trades[int(startat):int(startat) + int(limit)]
t.fromcache = fromcache
t.num_trades = trade_num
t.trades = trades
t.fromt = fromt
t.to = to
t.qtype = qtype
t.age = age
t.minprofit = minprofit
t.size = size
t.startat = startat
t.limit = limit
t.sort = sort
t.prefer_sec = prefer_sec
t.fromname = cur_f.fetchone()[1]
t.toname = cur_t.fetchone()[1]
t.page = int((int(startat) + 1) / limit) + 1
t.next = int(startat) + limit
t.prev = max(0, int(startat) - limit)
db.close()
session.save()
return t.respond(
) + "Pathfinder time: %0.3fs, Sort time: %0.3fs, recompute set was %d elements long<br>" % (
time_net, time_sort, len(from_set))
def index(self, join_password = None):
session = EVCstate(trust=True)
db = evec_func.db_con()
user = User.get(session, db)
t = display.template('corpmain.tmpl', session)
ucorp = None
if user.valid:
ucorp = Corp(db,user.corpid)
if join_password:
if ucorp.join_password == join_password:
user.make_member(db)
session.save()
t.can_create = False
t.ucorp = ucorp
if user.valid and (user.ismember or ucorp.join_password == ''):
if user.isdirector:
if not ucorp.exists:
t.can_create = True
t.corp = user.corporation
t.corpid = user.corpid
t.ismember = 1
t.user = user
elif user.valid and not user.ismember:
t.corp = user.corporation
t.corpid = user.corpid
t.user = user
t.ismember = 0
else:
t.corp = None
t.corpid = None
t.can_create = False
t.user = None
t.ismember = 0
cur = db.cursor()
cur.execute("SELECT corpname,description,headquarters,ticker,corpid FROM corps WHERE ticker IS NOT NULL AND ticker != '' ORDER BY corpname")
r = cur.fetchone()
corps = []
while r:
c = {}
c['corpname'] = r[0]
c['description'] = r[1]
c['headquarters'] = r[2]
c['ticker'] = r[3]
corpid = r[4]
ccheck = db.cursor()
ccheck.execute("SELECT contents FROM corppages WHERE corpid = %s", [corpid])
rc = ccheck.fetchone()
if rc[0] == "Placeholder text - this corporation has not yet provided a webpage":
pass
else:
corps.append(c)
r = cur.fetchone()
t.corps = corps
return t.respond()
def tradefind_display(self, qtype, fromt, to, set = None, age = 24, cashonhand = 10000000.00, minprofit = 100000, size = 10000, startat = 0, limit = 10, newsearch = "0", sort = "jprofit", prefer_sec = "0"):
mapserver = Pyro.core.getProxyForURI("PYROLOC://localhost:7766/mapserver")
session = EVCstate()
t = display.template('tradefind_display.tmpl', session)
db = evec_func.db_con()
newsearch = int(newsearch)
if 'trade_results' not in session:
newsearch = 1
cur = db.cursor()
limit = int(limit)
prefer_sec = int(prefer_sec)
recalc_route = False
if sort not in ['jprofit', 'sprofit', 'profit', 'jumps']:
return
if set:
session['tf_age'] = age
session['tf_minprofit'] = minprofit
session['tf_size'] = size
session['tf_limit'] = limit
session['tf_sort'] = sort
session['tf_prefer_sec'] = prefer_sec
newsearch = 1
else:
if 'tf_age' in session:
age = session['tf_age']
if 'tf_minprofit' in session:
minprofit = session['tf_minprofit']
if 'tf_size' in session:
size = session['tf_size']
if 'tf_limit' in session:
limit = session['tf_limit']
if 'tf_sort' in session:
sort = session['tf_sort']
if 'tf_prefer_sec' in session:
prefer_sec = session['tf_prefer_sec']
else:
prefer_sec = 1
age_t = str(int(age)) + " hours"
size = str(int(size))
cashonhand = str(double(cashonhand))
sql_profit_size = """ (t.price - f.price)* min(""" + size + """, min(t.volremain,f.volremain) * types.size)/types.size """
sql_profit_jumps = """ '1' """
#sql_cash = """ f.price <= $(cashonhand)s """
sql_sec_limit = """ """
if prefer_sec:
sql_sec_limit = """ fsys.security > 0.4 AND tsys.security > 0.4 AND """
cur_f = db.cursor()
cur_t = db.cursor()
# Pure suck query
if qtype == "Systems" and newsearch:
cur.execute("""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)* min(t.volremain,f.volremain) AS profit,
""" + sql_profit_size + """ AS profit_size,
""" + sql_profit_jumps + """ AS profit_jumps,
t.price,f.price,
t.volremain,f.volremain
FROM
types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
""" + sql_sec_limit + """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.bid = 0 AND t.bid = 1 AND f.systemid = %(fromt)s AND t.systemid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
""" + sql_profit_size + """ >= %(minprofit)s AND
types.size <= """ + size + """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""",
{'fromt':fromt, 'to':to, 'age':age_t, 'minprofit':minprofit,})
cur_f.execute("SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname", [fromt])
cur_t.execute("SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname", [to])
elif qtype == "Regions" and newsearch:
cur.execute("""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)*min(t.volremain,f.volremain) AS profit,
"""+ sql_profit_size + """ AS profit_size,
""" + sql_profit_jumps + """ AS profit_jumps,
t.price,f.price, t.volremain, f.volremain
FROM types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
""" + sql_sec_limit + """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.bid = 0 AND t.bid = 1 AND f.regionid = %(fromt)s AND t.regionid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
""" + sql_profit_size + """ >= %(minprofit)s AND
types.size <= """ + size + """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""",
{'fromt':fromt, 'to':to, 'age':age_t, 'minprofit':minprofit, })
cur_f.execute("SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname", [fromt])
cur_t.execute("SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname", [to])
elif qtype == "SystemToRegion" and newsearch:
cur.execute("""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)*min(t.volremain,f.volremain) AS profit,
""" + sql_profit_size + """ AS profit_size,
""" + sql_profit_jumps + """ AS profit_jumps,
t.price,f.price, t.volremain, f.volremain
FROM types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
""" + sql_sec_limit + """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.bid = 0 AND t.bid = 1 AND f.systemid = %(fromt)s AND t.regionid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
""" + sql_profit_size + """ >= %(minprofit)s AND
types.size <= """ + size + """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""",
{'fromt':fromt, 'to':to, 'age':age_t, 'minprofit':minprofit, })
cur_f.execute("SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname", [fromt])
cur_t.execute("SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname", [to])
elif qtype == "Global" and newsearch:
cur.execute("""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)*min(t.volremain,f.volremain) AS profit,
"""+ sql_profit_size + """ AS profit_size,
""" + sql_profit_jumps + """ AS profit_jumps,
t.price,f.price
FROM types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
""" + sql_sec_limit + """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.bid = 0 AND t.bid = 1 AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
""" + sql_profit_size + """ >= %(minprofit)s AND
types.size <= """ + size + """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""",
# ORDER BY profit_size DESC LIMIT %(limit)s OFFSET %(startat)s""",
{'age':age_t, 'minprofit':minprofit})
cur_f.execute("SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname", [fromt])
cur_t.execute("SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname", [to])
trades = []
time_net = 0.0
import __builtin__
from_set = __builtin__.set()
from_map = {}
from_to_map = {}
error_log = []
fromcache = 0
if newsearch:
r = cur.fetchone()
while r:
row = SorterDict('sortby')
row['typeid'] = r[0]
row['typename'] = r[1]
fr = int(r[2])
to2 = int(r[3])
row['fr'] = int(r[2])
row['to2'] = int(r[3])
row['fromstation'] = r[4]
row['tostation'] = r[5]
row['pricediff'] = r[6]
row['tradeable'] = format_long(long(r[7]))
row['profit'] = format_price(float(r[8]))
row['profit_size'] = float(r[9])
from_set.add(fr)
row['tprice'] = format_price(float(r[11]))
row['fprice'] = format_price(float(r[12]))
row['tvol'] = format_long(long(r[13]))
row['fvol'] = format_long(long(r[14]))
trades.append(row)
# Add to from_to_map - map source
if fr in from_to_map:
from_to_map[fr].add(row['to2'])
else:
from_to_map[fr] = __builtin__.set()
from_to_map[fr].add(row['to2'])
if fr in from_map:
from_map[fr].append(row)
else:
from_map[fr] = []
from_map[fr].append(row)
r = cur.fetchone()
# Now we try to compute distance
for compsys in from_set:
distance_map = {}
distance = 0
time_net_ = time.time()
distance_map = mapserver.route_comp(compsys, list(from_to_map[compsys]))
time_net += time.time() - time_net_
for row in from_map[compsys]:
if row['fr'] == compsys:
if compsys == row['to2']:
distance = 0
else:
distance = distance_map[row['to2']]
ps = row['profit_size']
row['profit_size'] = format_price(ps)
if sort == "jprofit":
row['sortby'] = ps/(max(0,distance)+1)
t.sort_nice = "Profit per jump"
elif sort == "sprofit":
row['sortby'] = ps
t.sort_nice = "Profit per trip"
elif sort == "profit":
row['sortby'] = row['profit']
t.sort_nice = "Profit"
elif sort == "jumps":
t.sort_nice = "Distance"
row['sortby'] = distance
row.reverse= True
row['profit_jumps'] = format_price(float(ps/(1+max(0,distance))))
row['distance'] = distance
time_sort = time.time()
trades.sort(reverse=True)
time_sort = time.time() - time_sort
#session['trade_results'] = trades
else:
trades = session['trade_results']
fromcache = 1
trade_num = len(trades)
trades = trades[int(startat):int(startat)+int(limit)]
t.fromcache = fromcache
t.num_trades = trade_num
t.trades = trades
t.fromt = fromt
t.to = to
t.qtype = qtype
t.age = age
t.minprofit = minprofit
t.size = size
t.startat = startat
t.limit = limit
t.sort = sort
t.prefer_sec = prefer_sec
t.fromname = cur_f.fetchone()[1]
t.toname = cur_t.fetchone()[1]
t.page = int((int(startat) + 1) / limit) + 1
t.next = int(startat) +limit
t.prev = max(0,int(startat) - limit)
db.close()
session.save()
return t.respond() +"Pathfinder time: %0.3fs, Sort time: %0.3fs, recompute set was %d elements long<br>" % (time_net, time_sort, len(from_set))
def compare(
self,
system1,
system2,
mode1="systems",
mode2="systems",
sethours=None,
setsecfilter=None,
setmaxisk=None,
**kwargs
):
db = evec_func.db_con()
session = EVCstate()
# Get defaults for sessions
hours = set_or_get(session, "compare_hours", sethours, 24)
secfilter = set_or_get(session, "compare_sec", setsecfilter, 0.4)
maxisk = int(set_or_get(session, "compare_max", setmaxisk, 10000000000))
system1 = int(system1)
system2 = int(system2)
if system1 < 30000000:
mode1 = "regions"
if system2 < 30000000:
mode2 = "regions"
t = display.template("tradetool_compare.tmpl", session)
(omap1, t1) = self.build_compare(db, system1, mode1, hours, secfilter=secfilter)
(omap2, t2) = self.build_compare(db, system2, mode2, hours, secfilter=secfilter)
t1.update(t2)
typenames = t1
omap = self.combine_maps(omap1, omap2)
# Build a pricelist from the maps so we can display the page in order
pricelist = []
for type in omap.iterkeys():
comp = omap[type][1].compare(omap[type][0])
# Avoid adding in negative price items (halves the size of the page)
if comp.min <= 0:
continue
if omap[type][0].min >= maxisk:
continue
pricelist.append(comp)
pricelist = sort(pricelist)
# OMap is the order map - comtaining comparisons between the middle
# and medium price values, the sum of orders, and a representitive order
t.omap = omap
t.omap1 = omap1
t.omap2 = omap2
t.pricelist = pricelist
t.namemap = typenames
if mode1 == "regions":
t.system1name = get_region_name(db, system1)
else:
t.system1name = get_system_name(db, system1)
if mode2 == "regions":
t.system2name = get_region_name(db, system2)
else:
t.system2name = get_system_name(db, system2)
t.mode1 = mode1
t.mode2 = mode2
t.maxisk = maxisk
t.system1 = system1
t.system2 = system2
t.hours = hours
t.secfilter = secfilter
return t.respond()
def compare(self,
system1,
system2,
mode1="systems",
mode2="systems",
sethours=None,
setsecfilter=None,
setmaxisk=None,
**kwargs):
db = evec_func.db_con()
session = EVCstate()
# Get defaults for sessions
hours = set_or_get(session, "compare_hours", sethours, 24)
secfilter = set_or_get(session, "compare_sec", setsecfilter, 0.4)
maxisk = int(set_or_get(session, "compare_max", setmaxisk,
10000000000))
system1 = int(system1)
system2 = int(system2)
if system1 < 30000000:
mode1 = "regions"
if system2 < 30000000:
mode2 = "regions"
t = display.template('tradetool_compare.tmpl', session)
(omap1, t1) = self.build_compare(db,
system1,
mode1,
hours,
secfilter=secfilter)
(omap2, t2) = self.build_compare(db,
system2,
mode2,
hours,
secfilter=secfilter)
t1.update(t2)
typenames = t1
omap = self.combine_maps(omap1, omap2)
# Build a pricelist from the maps so we can display the page in order
pricelist = []
for type in omap.iterkeys():
comp = omap[type][1].compare(omap[type][0])
# Avoid adding in negative price items (halves the size of the page)
if comp.min <= 0:
continue
if omap[type][0].min >= maxisk:
continue
pricelist.append(comp)
pricelist = sort(pricelist)
# OMap is the order map - comtaining comparisons between the middle
# and medium price values, the sum of orders, and a representitive order
t.omap = omap
t.omap1 = omap1
t.omap2 = omap2
t.pricelist = pricelist
t.namemap = typenames
if mode1 == 'regions':
t.system1name = get_region_name(db, system1)
else:
t.system1name = get_system_name(db, system1)
if mode2 == 'regions':
t.system2name = get_region_name(db, system2)
else:
t.system2name = get_system_name(db, system2)
t.mode1 = mode1
t.mode2 = mode2
t.maxisk = maxisk
t.system1 = system1
t.system2 = system2
t.hours = hours
t.secfilter = secfilter
return t.respond()
def tradefind_display(
self,
qtype,
fromt,
to,
set=None,
age=24,
cashonhand=10000000.00,
minprofit=100000,
size=10000,
startat=0,
limit=50,
newsearch="0",
sort="jprofit",
prefer_sec="0",
):
session = EVCstate()
t = display.template("tradefind_display.tmpl", session)
db = evec_func.db_con()
newsearch = int(newsearch)
if "trade_results" not in session:
newsearch = 1
cur = db.cursor()
limit = int(limit)
prefer_sec = int(prefer_sec)
recalc_route = False
if sort not in ["jprofit", "sprofit", "profit", "jumps"]:
return
if set:
session["tf_age"] = age
session["tf_minprofit"] = minprofit
session["tf_size"] = size
session["tf_limit"] = limit
session["tf_sort"] = sort
session["tf_prefer_sec"] = prefer_sec
newsearch = 1
else:
if "tf_age" in session:
age = session["tf_age"]
if "tf_minprofit" in session:
minprofit = session["tf_minprofit"]
if "tf_size" in session:
size = session["tf_size"]
if "tf_limit" in session:
limit = session["tf_limit"]
if "tf_sort" in session:
sort = session["tf_sort"]
if "tf_prefer_sec" in session:
prefer_sec = session["tf_prefer_sec"]
else:
prefer_sec = 1
age_t = str(int(age)) + " hours"
size = str(int(size))
cashonhand = str(double(cashonhand))
sql_profit_size = (
""" (t.price - f.price)* min(""" + size + """, min(t.volremain,f.volremain) * types.size)/types.size """
)
sql_profit_jumps = """ '1' """
# sql_cash = """ f.price <= $(cashonhand)s """
sql_sec_limit = """ """
if prefer_sec:
sql_sec_limit = """ fsys.security > 0.4 AND tsys.security > 0.4 AND """
cur_f = db.cursor()
cur_t = db.cursor()
#####
# Warning: This is easily the worst piece of code I have ever written.
# I really am sorry.
#####
# Pure suck query
if qtype == "Systems" and newsearch:
cur.execute(
"""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)* min(t.volremain,f.volremain) AS profit,
"""
+ sql_profit_size
+ """ AS profit_size,
"""
+ sql_profit_jumps
+ """ AS profit_jumps,
t.price,f.price,
t.volremain,f.volremain
FROM
types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
"""
+ sql_sec_limit
+ """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.minvolume <= 1 AND t.minvolume <= 1 AND
f.bid = 0 AND t.bid = 1 AND f.systemid = %(fromt)s AND t.systemid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
"""
+ sql_profit_size
+ """ >= %(minprofit)s AND
types.size <= """
+ size
+ """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""",
{"fromt": fromt, "to": to, "age": age_t, "minprofit": minprofit},
)
cur_f.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname",
[fromt],
)
cur_t.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname",
[to],
)
elif qtype == "Regions" and newsearch:
cur.execute(
"""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)*min(t.volremain,f.volremain) AS profit,
"""
+ sql_profit_size
+ """ AS profit_size,
"""
+ sql_profit_jumps
+ """ AS profit_jumps,
t.price,f.price, t.volremain, f.volremain
FROM types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
"""
+ sql_sec_limit
+ """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.minvolume <= 1 AND t.minvolume <= 1 AND
f.bid = 0 AND t.bid = 1 AND f.regionid = %(fromt)s AND t.regionid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
"""
+ sql_profit_size
+ """ >= %(minprofit)s AND
types.size <= """
+ size
+ """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""",
{"fromt": fromt, "to": to, "age": age_t, "minprofit": minprofit},
)
cur_f.execute("SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname", [fromt])
cur_t.execute("SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname", [to])
elif qtype == "SystemToRegion" and newsearch:
cur.execute(
"""SELECT types.typeid,types.typename,fs.systemid,ts.systemid,fs.stationname,ts.stationname,t.price - f.price AS pricediff,
min(t.volremain,f.volremain),
(t.price - f.price)*min(t.volremain,f.volremain) AS profit,
"""
+ sql_profit_size
+ """ AS profit_size,
"""
+ sql_profit_jumps
+ """ AS profit_jumps,
t.price,f.price, t.volremain, f.volremain
FROM types, current_market AS f, current_market AS t, stations AS fs, stations AS ts, systems AS fsys, systems AS tsys
WHERE
"""
+ sql_sec_limit
+ """
ts.systemid = tsys.systemid AND fs.systemid = fsys.systemid AND
f.minvolume <= 1 AND t.minvolume <= 1 AND
f.bid = 0 AND t.bid = 1 AND f.systemid = %(fromt)s AND t.regionid = %(to)s AND t.typeid = f.typeid AND t.stationid = ts.stationid AND
f.stationid = fs.stationid AND
age(f.reportedtime) < %(age)s AND age(t.reportedtime) < %(age)s AND
"""
+ sql_profit_size
+ """ >= %(minprofit)s AND
types.size <= """
+ size
+ """ AND
t.typeid = types.typeid
AND f.typeid = types.typeid
AND f.price < t.price""",
{"fromt": fromt, "to": to, "age": age_t, "minprofit": minprofit},
)
cur_f.execute(
"SELECT systemid,(systemname || ' / ') || regionname FROM systems,regions WHERE systems.regionid = regions.regionid AND systemid = %s ORDER BY systemname",
[fromt],
)
cur_t.execute("SELECT regionid,regionname FROM regions WHERE regionid = %s ORDER BY regionname", [to])
trades = []
time_net = 0.0
import __builtin__
from_set = __builtin__.set()
from_map = {}
from_to_map = {}
error_log = []
fromcache = 0
if newsearch:
r = cur.fetchone()
while r:
row = SorterDict("sortby")
row["typeid"] = r[0]
row["typename"] = r[1]
fr = int(r[2])
to2 = int(r[3])
row["fr"] = int(r[2])
row["to2"] = int(r[3])
row["fromstation"] = r[4]
row["tostation"] = r[5]
row["pricediff"] = format_price(r[6])
row["tradeable"] = format_long(r[7])
row["profit"] = format_price(r[8])
row["profit_num"] = float(r[8])
row["profit_size"] = float(r[9])
from_set.add(fr)
row["tprice"] = format_price(r[11])
row["fprice"] = format_price(r[12])
row["tvol"] = format_long(r[13])
row["fvol"] = format_long(r[14])
trades.append(row)
# Add to from_to_map - map source
if fr in from_to_map:
from_to_map[fr].add(row["to2"])
else:
from_to_map[fr] = __builtin__.set()
from_to_map[fr].add(row["to2"])
if fr in from_map:
from_map[fr].append(row)
else:
from_map[fr] = []
from_map[fr].append(row)
r = cur.fetchone()
# Now we try to compute distance
for compsys in from_set:
distance_map = {}
distance = 0
time_net_ = time.time()
for tosystem in from_to_map[compsys]:
load = urllib.urlopen(
"http://127.0.0.1:8081/api/distance/from/" + str(int(compsys)) + "/to/" + str(int(tosystem))
)
s = load.read()
load.close()
distance_map[tosystem] = json.loads(s)["distance"]
time_net += time.time() - time_net_
for row in from_map[compsys]:
if row["fr"] == compsys:
if compsys == row["to2"]:
distance = 0
else:
distance = distance_map[row["to2"]]
ps = row["profit_size"]
row["profit_size"] = format_price(ps)
if sort == "jprofit":
row["sortby"] = ps / (max(0, distance) + 1)
t.sort_nice = "Profit per jump"
elif sort == "sprofit":
row["sortby"] = ps
t.sort_nice = "Profit per trip"
elif sort == "profit":
row["sortby"] = row["profit_num"]
t.sort_nice = "Profit"
elif sort == "jumps":
t.sort_nice = "Distance"
row["sortby"] = distance
row.reverse = True
row["profit_jumps"] = format_price(ps / (1 + max(0, distance)))
row["distance"] = distance
time_sort = time.time()
trades.sort(reverse=True)
time_sort = time.time() - time_sort
# session['trade_results'] = trades
else:
trades = session["trade_results"]
fromcache = 1
trade_num = len(trades)
trades = trades[int(startat) : int(startat) + int(limit)]
t.fromcache = fromcache
t.num_trades = trade_num
t.trades = trades
t.fromt = fromt
t.to = to
t.qtype = qtype
t.age = age
t.minprofit = minprofit
t.size = size
t.startat = startat
t.limit = limit
t.sort = sort
t.prefer_sec = prefer_sec
t.fromname = cur_f.fetchone()[1]
t.toname = cur_t.fetchone()[1]
t.page = int((int(startat) + 1) / limit) + 1
t.next = int(startat) + limit
t.prev = max(0, int(startat) - limit)
db.close()
session.save()
return t.respond() + "Pathfinder time: %0.3fs, Sort time: %0.3fs, recompute set was %d elements long<br>" % (
time_net,
time_sort,
len(from_set),
)
def quicklook(self,
typeid,
setorder=None,
setdir=None,
igbover=False,
sethours=None,
regionlimit=None,
usesystem=None,
setminQ=0,
poffset=0,
outtype='html',
api=1.0):
session = {}
session = EVCstate()
cherrypy.response.headers['Content-Type'] = 'text/html'
db = evec_func.db_con()
if regionlimit is None:
regionlimit = []
if type(regionlimit) != list:
regionlimit = [int(regionlimit)]
else:
regionlimit = [int(x) for x in regionlimit]
if 'regionlimit' in session and len(regionlimit) == 0:
regionlimit = session['regionlimit']
if len(regionlimit) == 0:
regionlimit = empireregions()
randomregion = None
up_sug = None
order = 'price'
orderdir = 'ASC'
borderdir = 'DESC'
hours = 24
minQ = 0
if setminQ:
minQ = int(setminQ)
session['minQ'] = minQ
if 'minQ' in session:
minQ = session['minQ']
if setdir:
if setdir == '1':
orderdir = "ASC"
borderdir = 'DESC'
else:
orderdir = "DESC"
borderdir = 'ASC'
session['orderdir'] = orderdir
session['borderdir'] = borderdir
if 'orderdir' in session:
orderdir = session['orderdir']
if 'borderdir' in session:
borderdir = session['borderdir']
if sethours:
hours = int(sethours)
session['orderhours'] = hours
if 'orderhours' in session:
hours = session['orderhours']
if setorder:
if setorder not in [
'volremain', 'stationname', 'regionname', 'price'
]:
raise "SetOrder fail"
order = setorder
session['order'] = order
if 'order' in session:
order = session['order']
# Fetch and generate the template
t = None
if outtype == 'html':
t = display.template('quicklook.tmpl', session)
elif outtype == 'xml':
t = display.template('quicklook_xml.tmpl', session)
t.api = api
typename = ""
try:
typename = evec_func.get_type_name(db, typeid)
except:
return "Can't find that type"
typesize = evec_func.get_type_size(db, typeid)
t.typename = typename
t.typesize = typesize
t.typeid = typeid
t.sortorder = order
reg_block = evec_func.build_regionquery("regions", regionlimit)
sql_age = ` hours ` + " hours"
sql_system = " "
if usesystem:
usesystem = int(usesystem)
sql_system = " AND current_market.systemid = " + ` usesystem ` + " "
# statistics for selling
# do query here
cur_buy = db.cursor()
cur_sell = db.cursor()
limit = "LIMIT 10000 OFFSET 0"
transactions = []
t.poffset = int(poffset)
# Fetch from cache or run query
buys = []
sells = []
cache_key = cache.generic_key("evec_quicklook", typeid, regionlimit,
usesystem, order, orderdir, borderdir,
limit, minQ, hours)
cache_result = cache.get(cache_key)
def run_query():
cur_buy.execute(
"SELECT bid,current_market.systemid,current_market.stationid,price,volremain,(issued+duration),range,regionname, (reportedtime),stationname,security,minvolume,regions.regionid,orderid FROM current_market,regions,stations,systems WHERE "
+ reg_block +
" AND stations.systemid = systems.systemid AND typeid = %s AND stations.stationid = current_market.stationid AND current_market.regionid = regions.regionid AND now() - reportedtime <= '"
+ sql_age +
"' AND volremain >= %s AND current_market.bid = 1 " +
sql_system + " ORDER BY " + order + " " + borderdir + " " +
limit, [typeid, minQ])
cur_sell.execute(
"SELECT bid,current_market.systemid,current_market.stationid,price,volremain,(issued+duration),range,regionname,(reportedtime),stationname,security,regions.regionid,orderid FROM current_market,regions,stations,systems WHERE "
+ reg_block +
" AND typeid = %s AND stations.systemid = systems.systemid AND stations.stationid = current_market.stationid AND current_market.regionid = regions.regionid AND now() - reportedtime <= '"
+ sql_age +
"' AND volremain >= %s AND current_market.bid = 0 " +
sql_system + " ORDER BY " + order + " " + orderdir + " " +
limit, [typeid, minQ])
for (query, lista, isbuy) in [(cur_buy, buys, True),
(cur_sell, sells, False)]:
r = query.fetchone()
while r:
rec = {}
rec['systemid'] = r[1]
rec['stationid'] = r[2]
price = float(r[3])
string = format_price(price)
rec['price'] = string
rec['price_raw'] = price
rec['volremain'] = format_long(r[4])
rec['volremain_raw'] = r[4]
rec['expires'] = str(r[5])[0:10]
if r[0] == True:
rec['range'] = r[6]
else:
rec['range'] = -2
rec['regionname'] = r[7]
reported = DateTime.DateTimeFrom(str(r[8]))
now = DateTime.gmt()
hours = (now - reported).hours
if hours >= 1:
rec['reportedtime'] = "%d hours ago" % (hours)
else:
rec['reportedtime'] = "%d minutes ago" % (
now - reported).minutes
rec['stationname'] = r[9]
sec = r[10]
ssec = str(sec)[0:3]
if sec <= 0.0:
ssec = "0.0"
rec['security'] = ssec
# Try to grab regionid from the end of the query
if isbuy:
if int(r[11]) > 1:
rec['minvolume'] = format_long(int(r[11]))
rec['minvolume_raw'] = int(r[11])
else:
rec['minvolume'] = 1
rec['minvolume_raw'] = 1
rec['regionid'] = r[12]
rec['orderid'] = r[13]
else:
rec['minvolume'] = 1
rec['minvolume_raw'] = 1
rec['regionid'] = r[11]
rec['orderid'] = r[12]
lista.append(rec)
r = query.fetchone()
# pass in info here
if cache_result is None:
run_query()
cache.set(cache_key, (buys, sells))
else:
buys = cache_result[0]
sells = cache_result[1]
t.regions = evec_func.region_list(db)
t.upload_sug = up_sug
t.upload_reg = randomregion
t.regionlimit = regionlimit
t.usesystem = usesystem
t.hours = hours
t.minQ = minQ
t.buys = buys
t.sells = sells
t.mtransaction = transactions
db.close()
return t.respond()
def marketstat_xml(self, hours = "360", minQ = 0, typeid = None, dump=None, evemon = None, regionlimit = None):
cherrypy.response.headers['Content-Type'] = 'text/xml'
db = evec_func.db_con()
if regionlimit is None:
regionlimit = []
if not isinstance(regionlimit, list):
regionlimit = [regionlimit]
regionlimit = map(int, regionlimit)
if int(hours) > 360:
hours = "360"
hours = int(hours)
response = ""
response += "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n"
response += "<!-- Automatically generated data from EVE-Central.com -->\n"
response += "<!-- WARNING: THIS API IS NOW DEPRECATED - PLEASE USE THE NEW API -->\n"
if typeid is not None and dump is None:
typeid = int(typeid)
if minQ == 0 and typeid in stats.MINQ_TYPES:
minQ = MINQ_VOL
prices = stats.item_stat(db, typeid, hours, buysell = False, regionlimit = regionlimit, minQ = minQ)
(sell,buy) = stats.item_stat(db, typeid, hours, regionlimit = regionlimit)
response += "<market_stat>\n"
response += " <typeid>"+`typeid`+"</typeid>\n"
response += " <avg_price>"+`prices['avg_price']`+"</avg_price>\n"
response += " <total_volume>"+`prices['total_vol']`[:-1]+"</total_volume>\n"
response += " <avg_buy_price>"+`buy['avg_price']`+"</avg_buy_price>\n"
response += " <total_buy_volume>"+`buy['total_vol']`[:-1]+"</total_buy_volume>\n"
response += " <stddev_buy_price>" + `buy['stddev']`+"</stddev_buy_price>\n"
response += " <max_buy_price>" + `buy['max']`+"</max_buy_price>\n"
response += " <min_buy_price>" + `buy['min']`+"</min_buy_price>\n"
response += "\n"
response += " <avg_sell_price>"+`sell['avg_price']`+"</avg_sell_price>\n"
response += " <total_sell_volume>"+`sell['total_vol']`[:-1]+"</total_sell_volume>\n"
response += " <stddev_sell_price>" + `sell['stddev']`+"</stddev_sell_price>\n"
response += " <max_sell_price>" + `sell['max']`+"</max_sell_price>\n"
response += " <min_sell_price>" + `sell['min']`+"</min_sell_price>\n"
response += " <median>"
response += " <sell>" + `sell['median']`+ "</sell>"
response += " <buy>" + `buy['median']` + "</buy>"
response += " <all>" + `prices['median']` + "</all></median>"
response += "</market_stat>"
elif typeid is not None and dump is not None:
typeid = int(typeid)
response += "<market_dump>\n"
elif evemon is not None:
response += "<minerals>\n"
for mineral in [34, 35, 36, 37, 38, 39, 40, 11399]:
prices = stats.item_stat(db, mineral, hours, buysell = False, minQ = 5000)
typename = evec_func.get_type_name(db, mineral)
response += " <mineral>\n"
response += " <name>"+typename+"</name>"
response += " <price>"+`prices['median']`+"</price>"
response += " </mineral>\n"
response += "</minerals>\n"
db.close()
return response
def quicklook(
self,
typeid,
setorder=None,
setdir=None,
igbover=False,
sethours=None,
regionlimit=None,
usesystem=None,
setminQ=0,
poffset=0,
outtype="html",
api=1.0,
):
session = {}
session = EVCstate()
cherrypy.response.headers["Content-Type"] = "text/html"
db = evec_func.db_con()
if regionlimit is None:
regionlimit = []
if type(regionlimit) != list:
regionlimit = [int(regionlimit)]
else:
regionlimit = [int(x) for x in regionlimit]
if "regionlimit" in session and len(regionlimit) == 0:
regionlimit = session["regionlimit"]
if len(regionlimit) == 0:
regionlimit = empireregions()
randomregion = None
up_sug = None
order = "price"
orderdir = "ASC"
borderdir = "DESC"
hours = 24
minQ = 0
if setminQ:
minQ = int(setminQ)
session["minQ"] = minQ
if "minQ" in session:
minQ = session["minQ"]
if setdir:
if setdir == "1":
orderdir = "ASC"
borderdir = "DESC"
else:
orderdir = "DESC"
borderdir = "ASC"
session["orderdir"] = orderdir
session["borderdir"] = borderdir
if "orderdir" in session:
orderdir = session["orderdir"]
if "borderdir" in session:
borderdir = session["borderdir"]
if sethours:
hours = int(sethours)
session["orderhours"] = hours
if "orderhours" in session:
hours = session["orderhours"]
if setorder:
if setorder not in ["volremain", "stationname", "regionname", "price"]:
raise "SetOrder fail"
order = setorder
session["order"] = order
if "order" in session:
order = session["order"]
# Fetch and generate the template
t = None
if outtype == "html":
t = display.template("quicklook.tmpl", session)
elif outtype == "xml":
t = display.template("quicklook_xml.tmpl", session)
t.api = api
typename = ""
try:
typename = evec_func.get_type_name(db, typeid)
except:
return "Can't find that type"
typesize = evec_func.get_type_size(db, typeid)
t.typename = typename
t.typesize = typesize
t.typeid = typeid
t.sortorder = order
reg_block = evec_func.build_regionquery("regions", regionlimit)
sql_age = ` hours ` + " hours"
sql_system = " "
if usesystem:
usesystem = int(usesystem)
sql_system = " AND current_market.systemid = " + ` usesystem ` + " "
# statistics for selling
# do query here
cur_buy = db.cursor()
cur_sell = db.cursor()
limit = "LIMIT 10000 OFFSET 0"
transactions = []
t.poffset = int(poffset)
# Fetch from cache or run query
buys = []
sells = []
cache_key = cache.generic_key(
"evec_quicklook", typeid, regionlimit, usesystem, order, orderdir, borderdir, limit, minQ, hours
)
cache_result = cache.get(cache_key)
def run_query():
cur_buy.execute(
"SELECT bid,current_market.systemid,current_market.stationid,price,volremain,(issued+duration),range,regionname, (reportedtime),stationname,security,minvolume,regions.regionid,orderid FROM current_market,regions,stations,systems WHERE "
+ reg_block
+ " AND stations.systemid = systems.systemid AND typeid = %s AND stations.stationid = current_market.stationid AND current_market.regionid = regions.regionid AND now() - reportedtime <= '"
+ sql_age
+ "' AND volremain >= %s AND current_market.bid = 1 "
+ sql_system
+ " ORDER BY "
+ order
+ " "
+ borderdir
+ " "
+ limit,
[typeid, minQ],
)
cur_sell.execute(
"SELECT bid,current_market.systemid,current_market.stationid,price,volremain,(issued+duration),range,regionname,(reportedtime),stationname,security,regions.regionid,orderid FROM current_market,regions,stations,systems WHERE "
+ reg_block
+ " AND typeid = %s AND stations.systemid = systems.systemid AND stations.stationid = current_market.stationid AND current_market.regionid = regions.regionid AND now() - reportedtime <= '"
+ sql_age
+ "' AND volremain >= %s AND current_market.bid = 0 "
+ sql_system
+ " ORDER BY "
+ order
+ " "
+ orderdir
+ " "
+ limit,
[typeid, minQ],
)
for (query, lista, isbuy) in [(cur_buy, buys, True), (cur_sell, sells, False)]:
r = query.fetchone()
while r:
rec = {}
rec["systemid"] = r[1]
rec["stationid"] = r[2]
price = float(r[3])
string = format_price(price)
rec["price"] = string
rec["price_raw"] = price
rec["volremain"] = format_long(r[4])
rec["volremain_raw"] = r[4]
rec["expires"] = str(r[5])[0:10]
if r[0] == True:
rec["range"] = r[6]
else:
rec["range"] = -2
rec["regionname"] = r[7]
reported = DateTime.DateTimeFrom(str(r[8]))
now = DateTime.gmt()
hours = (now - reported).hours
if hours >= 1:
rec["reportedtime"] = "%d hours ago" % (hours)
else:
rec["reportedtime"] = "%d minutes ago" % (now - reported).minutes
rec["stationname"] = r[9]
sec = r[10]
ssec = str(sec)[0:3]
if sec <= 0.0:
ssec = "0.0"
rec["security"] = ssec
# Try to grab regionid from the end of the query
if isbuy:
if int(r[11]) > 1:
rec["minvolume"] = format_long(r[11])
rec["minvolume_raw"] = int(r[11])
else:
rec["minvolume"] = 1
rec["minvolume_raw"] = 1
rec["regionid"] = r[12]
rec["orderid"] = r[13]
else:
rec["minvolume"] = 1
rec["minvolume_raw"] = 1
rec["regionid"] = r[11]
rec["orderid"] = r[12]
lista.append(rec)
r = query.fetchone()
# pass in info here
if cache_result is None:
run_query()
cache.set(cache_key, (buys, sells))
else:
buys = cache_result[0]
sells = cache_result[1]
t.regions = evec_func.region_list(db)
t.upload_sug = up_sug
t.upload_reg = randomregion
t.regionlimit = regionlimit
t.usesystem = usesystem
t.hours = hours
t.minQ = minQ
t.buys = buys
t.sells = sells
t.mtransaction = transactions
db.close()
return t.respond()
def quicklook(self, typeid, setorder=None, setdir = None, igbover = False, sethours = None, regionlimit = None, usesystem = None, setminQ = 0, poffset = 0, outtype = 'html', api = 1.0):
session = {}
if outtype == 'html':
session = EVCstate()
cherrypy.response.headers['Content-Type'] = 'text/html'
elif outtype == 'xml':
cherrypy.response.headers['Content-Type'] = 'text/xml'
db = evec_func.db_con()
if regionlimit is None:
regionlimit = []
if type(regionlimit) != list:
regionlimit = [int(regionlimit)]
else:
regionlimit = [int(x) for x in regionlimit]
if 'regionlimit' in session and len(regionlimit) == 0:
regionlimit = session['regionlimit']
randomregion = None
up_sug = None
if len(regionlimit) == 0:
pass
elif random.randint(1,50) > 10:
random.shuffle(regionlimit)
randomregion = regionlimit[0]
up_sug = upload_suggest(db, randomregion)
order = 'price'
orderdir = 'ASC'
borderdir = 'DESC'
hours = 15*24
minQ = 0
if setminQ:
minQ = int(setminQ)
session['minQ'] = minQ
if 'minQ' in session:
minQ = session['minQ']
if setdir:
if setdir == '1':
orderdir = "ASC"
borderdir = 'DESC'
else:
orderdir = "DESC"
borderdir = 'ASC'
session['orderdir'] = orderdir
session['borderdir'] = borderdir
if 'orderdir' in session:
orderdir = session['orderdir']
if 'borderdir' in session:
borderdir = session['borderdir']
if sethours:
hours = int(sethours)
session['orderhours'] = hours
if 'orderhours' in session:
hours = session['orderhours']
if setorder:
if setorder not in ['volremain', 'stationname', 'regionname', 'price']:
raise "SetOrder fail"
order = setorder
session['order'] = order
if 'order' in session:
order = session['order']
# Fetch and generate the template
t = None
if outtype == 'html':
t = display.template('quicklook.tmpl', session)
elif outtype == 'xml':
t = display.template('quicklook_xml.tmpl', session)
t.api = api
typename = ""
try:
typename = evec_func.get_type_name(db, typeid)
except:
return "Can't find that type"
typesize = evec_func.get_type_size(db, typeid)
t.typename = typename
t.typesize = typesize
t.typeid = typeid
t.sortorder = order
reg_block = evec_func.build_regionquery("regions", regionlimit)
sql_age = `hours`+" hours"
sql_system = " "
if usesystem:
usesystem = int(usesystem)
sql_system = " AND current_market.systemid = " + `usesystem` + " "
# statistics for selling
(sell, buy) = stats.item_stat(db, typeid, hours, sql_system, regionlimit = regionlimit, minQ = minQ)
t.b_avg_price = format_price(buy['median'])
t.b_total_vol = format_long(buy['total_vol'])
t.b_total_movement = format_long(buy['total_movement'])
t.avg_price = format_price(sell['median'])
t.total_vol = format_long(sell['total_vol'])
t.total_movement = format_long(sell['total_movement'])
# do query here
cur_buy = db.cursor()
cur_sell = db.cursor()
limit = "LIMIT 10000 OFFSET 0"
cur_trans = db.cursor()
cur_trans.execute("SELECT wmt.price,wmt.stationname,wmt.transtime,wmt.quantity FROM wallet_market_transactions AS wmt WHERE typeid = %s ORDER BY transtime DESC LIMIT 10", [typeid])
transactions = []
r = cur_trans.fetchone()
while r:
rec = {}
rec['price'] = format_price(float(r[0]))
rec['stationname'] = r[1]
rec['transtime'] = r[2]
rec['quantity'] = format_long(long(r[3]))
r = cur_trans.fetchone()
transactions.append(rec)
t.poffset = int(poffset)
# Fetch from cache or run query
buys = []
sells = []
cache_key = cache.generic_key("evec_quicklook", typeid, regionlimit, usesystem, order, orderdir, borderdir, limit, minQ)
cache_result = cache.get(cache_key)
def run_query():
cur_buy.execute("SELECT bid,current_market.systemid,current_market.stationid,price,volremain,(issued+duration),range,regionname, (reportedtime),stationname,security,minvolume,regions.regionid,orderid FROM current_market,regions,stations,systems WHERE " + reg_block + " AND stations.systemid = systems.systemid AND typeid = %s AND stations.stationid = current_market.stationid AND current_market.regionid = regions.regionid AND age(reportedtime) < '"+sql_age+"' AND volremain >= %s AND current_market.bid = 1 " + sql_system + " ORDER BY " + order + " " + borderdir + " " + limit, [typeid,minQ])
cur_sell.execute("SELECT bid,current_market.systemid,current_market.stationid,price,volremain,(issued+duration),range,regionname,(reportedtime),stationname,security,regions.regionid,orderid FROM current_market,regions,stations,systems WHERE " + reg_block + " AND typeid = %s AND stations.systemid = systems.systemid AND stations.stationid = current_market.stationid AND current_market.regionid = regions.regionid AND age(reportedtime) < '"+sql_age+"' AND volremain >= %s AND current_market.bid = 0 " + sql_system + " ORDER BY " + order + " " + orderdir + " " + limit, [typeid,minQ])
for (query,lista,isbuy) in [(cur_buy, buys, True), (cur_sell, sells, False)]:
r = query.fetchone()
while r:
rec = {}
rec['systemid'] = r[1]
rec['stationid'] = r[2]
price = float(r[3])
string = format_price(price)
rec['price'] = string
rec['price_raw'] = price
rec['volremain'] = format_long(r[4])
rec['volremain_raw'] = r[4]
rec['expires'] = str(r[5])[0:10]
if r[0] == True:
rec['range'] = r[6]
else:
rec['range'] = -2
rec['regionname'] = r[7]
rec['reportedtime'] = str(r[8])[5:-7]
rec['stationname'] = r[9]
rec['security'] = str(r[10])[0:3]
# Try to grab regionid from the end of the query
if isbuy:
if int(r[11]) > 1:
rec['minvolume'] = format_long(int(r[11]))
rec['minvolume_raw'] = int(r[11])
else:
rec['minvolume'] = 1
rec['minvolume_raw'] = 1
rec['regionid'] = r[12]
rec['orderid'] = r[13]
else:
rec['minvolume'] = 1
rec['minvolume_raw'] = 1
rec['regionid'] = r[11]
rec['orderid'] = r[12]
lista.append(rec)
r = query.fetchone()
# pass in info here
if cache_result is None:
run_query()
cache.set(cache_key, (buys,sells))
else:
buys = cache_result[0]
sells = cache_result[1]
t.regions = evec_func.region_list(db)
t.upload_sug = up_sug
t.upload_reg = randomregion
t.regionlimit = regionlimit
t.usesystem = usesystem
t.hours = hours
t.minQ = minQ
t.buys = buys
t.sells = sells
t.mtransaction = transactions
db.close()
return t.respond()
