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 item_stat(db, typeid, hours = 48, sql_system = " ", regionlimit = [], buysell = True, minQ = 0, nocache = False):
global CACHE_TIME
obj_name = cache_name(typeid, hours, sql_system, regionlimit, buysell, minQ)
if not nocache:
cache_obj = cache.get(obj_name)
if cache_obj:
return cache_obj
sql_age = `hours`+" hours"
reg_block_stat = evec_func.build_regionquery("current_market", regionlimit)
stat = db.cursor()
query_string = "SELECT price,bid,volremain,volenter FROM current_market WHERE " + reg_block_stat + " AND typeid = %s AND price > 0.15 AND volenter >= %s AND age(reportedtime) < '" + sql_age + "'" + sql_system
sell = {}
buy = {}
median_price_sell = []
volenter_sell = []
volremain_sell = []
median_price_buy = []
volenter_buy = []
volremain_buy = []
stat.execute(query_string , [typeid, minQ])
r = stat.fetchone()
while r:
if r[1] == 1:
median_price_buy.append(r[0])
volenter_buy.append(r[3])
volremain_buy.append(r[2])
else:
median_price_sell.append(r[0])
volenter_sell.append(r[3])
volremain_sell.append(r[2])
r = stat.fetchone()
if buysell:
res_s = calculate_stats(median_price_sell, volenter_sell)
res_b = calculate_stats(median_price_buy, volenter_buy)
vol_s = sum_volumes(volenter_sell, volremain_sell)
vol_b = sum_volumes(volenter_buy, volremain_buy)
for (r, v, emap) in [(res_s,vol_s,sell), (res_b,vol_b,buy)]:
emap['median'] = r[0]
emap['avg_price'] = r[1]
emap['total_vol'] = v[0]
emap['total_movement'] = v[2]
emap['stddev'] = r[2]
emap['max'] = r[4]
emap['min'] = r[5]
cache.set(obj_name, (sell, buy), CACHE_TIME)
return (sell,buy)
else:
median_price_sell.extend(median_price_buy)
volenter_sell.extend(volenter_buy)
volremain_sell.extend(volremain_buy)
r = calculate_stats(median_price_sell)
v = sum_volumes(volenter_sell, volremain_sell)
try:
sell['median'] = r[0]
sell['avg_price'] = r[1]
sell['total_vol'] = v[0]
sell['total_movement'] = v[2]
sell['stddev'] = r[2]
sell['max'] = r[4]
sell['min'] = r[5]
except:
sell['avg_price'] = 0
sell['total_vol'] = 0
sell['total_movement'] = 0
sell['stddev'] = 0
sell['max'] = 0
sell['min'] = 0
cache.set(obj_name, sell, CACHE_TIME)
return sell
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()
def item_stat(db,
typeid,
hours=48,
sql_system=" ",
regionlimit=[],
buysell=True,
minQ=0,
nocache=False):
global CACHE_TIME
obj_name = cache_name(typeid, hours, sql_system, regionlimit, buysell,
minQ)
if not nocache:
cache_obj = cache.get(obj_name)
if cache_obj:
return cache_obj
sql_age = ` hours ` + " hours"
reg_block_stat = evec_func.build_regionquery("current_market", regionlimit)
stat = db.cursor()
query_string = "SELECT price,bid,volremain,volenter FROM current_market WHERE " + reg_block_stat + " AND typeid = %s AND price > 0.15 AND volenter >= %s AND age(reportedtime) < '" + sql_age + "'" + sql_system
sell = {}
buy = {}
median_price_sell = []
volenter_sell = []
volremain_sell = []
median_price_buy = []
volenter_buy = []
volremain_buy = []
stat.execute(query_string, [typeid, minQ])
r = stat.fetchone()
while r:
if r[1] == 1:
# buy order
median_price_buy.append(r[0])
volenter_buy.append(r[3])
volremain_buy.append(r[2])
else:
# sell order
median_price_sell.append(r[0])
volenter_sell.append(r[3])
volremain_sell.append(r[2])
r = stat.fetchone()
if buysell:
res_s = calculate_stats(median_price_sell, volremain_sell, True)
res_b = calculate_stats(median_price_buy, volremain_buy)
vol_s = sum_volumes(volenter_sell, volremain_sell)
vol_b = sum_volumes(volenter_buy, volremain_buy)
for (r, v, emap) in [(res_s, vol_s, sell), (res_b, vol_b, buy)]:
emap['median'] = r[0]
emap['avg_price'] = r[1]
emap['total_vol'] = v[0]
emap['total_movement'] = v[2]
emap['stddev'] = r[2]
emap['max'] = r[4]
emap['min'] = r[5]
emap['percentile'] = r[6]
cache.set(obj_name, (sell, buy), CACHE_TIME)
return (sell, buy)
else:
median_price_sell.extend(median_price_buy)
volenter_sell.extend(volenter_buy)
volremain_sell.extend(volremain_buy)
r = calculate_stats(median_price_sell)
v = sum_volumes(volenter_sell, volremain_sell)
try:
sell['median'] = r[0]
sell['avg_price'] = r[1]
sell['total_vol'] = v[0]
sell['total_movement'] = v[2]
sell['stddev'] = r[2]
sell['max'] = r[4]
sell['min'] = r[5]
sell['percentile'] = 0
except:
sell['avg_price'] = 0
sell['total_vol'] = 0
sell['total_movement'] = 0
sell['stddev'] = 0
sell['max'] = 0
sell['min'] = 0
sell['percentile'] = 0
cache.set(obj_name, sell, CACHE_TIME)
return sell
