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 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 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 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 marketstat(self, hours = "360", minQ = 0, typeid = None, regionlimit = None, usesystem = None): cherrypy.response.headers['Content-Type'] = 'text/xml' db = evec_func.db_con() if typeid is None: typeid = [] 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) typeids = [] if type(typeid) != list: typeids = [typeid] else: typeids = typeid if len(typeids) > 100: db.close() return "<evec_api><error>No more than 100 results allowed</error></evec_api>" sql_system = " " if usesystem: usesystem = int(usesystem) sql_system = " AND current_market.systemid = " + `usesystem` + " " statslist = [] for typeid in typeids: typeid = int(typeid) useMinQ = minQ if useMinQ == 0 and typeid in stats.MINQ_TYPES: useMinQ = stats.MINQ_VOL prices = stats.item_stat(db, typeid, hours, sql_system, buysell = False, regionlimit = regionlimit, minQ = useMinQ) newprices = {} # Reformat prices as 2 digit float strings for key in prices.keys(): newprices[key] = "%0.2f" % prices[key] (sell,buy) = stats.item_stat(db, typeid, hours, sql_system, regionlimit = regionlimit, minQ = useMinQ) newsell = {} newbuy = {} for key in sell.keys(): newsell[key] = "%0.2f" % sell[key] for key in buy.keys(): newbuy[key] = "%0.2f" % buy[key] statslist.append(StatHolder(typeid, newprices, newbuy, newsell)) t = display.template('marketstat_xml.tmpl', None) t.types = statslist db.close() return t.respond()