Example #1
0
 def __init__(self):
     self.connQ = psycopg2.connect("dbname='eveclient'  user='******'")
     self.currQ = self.connQ.cursor()
     expire_after = datetime.timedelta(hours = 6)
     self.s = requests_cache.CachedSession(cache_name="first_transaction_cache", expire_after=expire_after)
     self.s.hooks = {'response': self.make_throttle_hook(0.1)}
 
     self.queries = SDEQueries()
     self.markets = EVECrest()
Example #2
0
class MarketQuestions(object):
    
    def __init__(self):
        self.connQ = psycopg2.connect("dbname='eveclient'  user='******'")
        self.currQ = self.connQ.cursor()
        expire_after = datetime.timedelta(hours = 6)
        self.s = requests_cache.CachedSession(cache_name="first_transaction_cache", expire_after=expire_after)
        self.s.hooks = {'response': self.make_throttle_hook(0.1)}
    
        self.queries = SDEQueries()
        self.markets = EVECrest()

    def populate_corp_assets_table(self, assets):
        #curr.executescript('drop table if exists corpassets;')    
        """currQ.execute("CREATE TABLE corpassets "
                    "(itemid INT PRIMARY KEY  NOT NULL,"
                    "locationid     INT    NOT NULL,"
                    "typeid    INT    NOT NULL,"
                    "quantity    INT    NOT NULL,"
                    "flag    INT    NOT NULL,"
                    "singleton    INT    NOT NULL"
                    ");")
        """
        self.currQ.execute("DELETE FROM corpassets")
        for x in assets:
            '''conn.execute("INSERT INTO corpassets "
                         "(itemid, locationid, typeid, quantity, flags, singleton) "
                         "VALUES (", x["itemID"],", "x['locationID'],", ",x['typeID'],", ",x['quantity']",","
                         ""x['flags']", ",x['singleton']);""
                         )'''
            self.currQ.execute("INSERT INTO corpassets ({ii}, {li}, {ti}, {q}, {f}, {s}) VALUES ({vii}, {vli}, {vti}, {vq}, {vf}, {vs})".
                        format(ii="itemid", li="locationid", ti="typeid", q="quantity", f="flag", s="singleton",
                                vii=x["itemID"], vli=x["locationID"], vti=x["typeID"], vq=x["quantity"], vf=x["flag"], vs=x["singleton"]))
        self.connQ.commit()
        
    def make_throttle_hook(self, timeout=1.0):  # for eve market api calls
        """
        Returns a response hook function which sleeps for `timeout` seconds if
        response is not cached
        """
        def hook(response, **kwargs):
            if not getattr(response, 'from_cache', False):
                # ('sleeping')
                time.sleep(timeout)
            return response
        return hook
    
    def now_value_system(self, system, interestingItem):
        marketStatUrl = "http://api.eve-central.com/api/marketstat/json?usesystem=" + str(system) + "&typeid=" + str(interestingItem)
        resp = self.s.get(url=marketStatUrl)
        # print resp.text
        data = json.loads(resp.text)[0]
        #print ("data", data["buy"]["generated"])
        return data
        # pprint.pprint(data)
        # print ("They Buy ", data[0]['buy']['max'])
        # print ("They Sell ", data[0]['sell']['min'])

    def now_value_region(self, region, interestingItem):
        marketStatUrl = "http://api.eve-central.com/api/marketstat/json?useregion=" + str(region) + "&typeid=" + str(interestingItem)
        resp = self.s.get(url=marketStatUrl)
        # print resp.text
        data = json.loads(resp.text)[0]
        #print ("data", data["buy"]["generated"])
        return data
        # pprint.pprint(data)
        # print ("They Buy ", data[0]['buy']['max'])
        # print ("They Sell ", data[0]['sell']['min'])

        
    def insert_market_price(self, data):
        """ insert into the psql table 'marketprices' the data given as an input
        the input data shouls usually be a json lookup on the eve-central API
        """
        logger.debug("attempting to insert_market_price {dt}".
                     format(dt = data))
        # buy orders - wehere we sell an item
        x = data["buy"]
        if x["forQuery"]["regions"]:
            pass
        else:
            x["forQuery"]["regions"].append(0)
        
        if x["forQuery"]["systems"]:
            pass
        else:
            x["forQuery"]["systems"].append(0)
        try:
            self.currQ.execute("INSERT INTO marketprices " 
                        "(direction, " 
                        "item, "
                        "region, "
                        "system, "
                        "avg, "
                        "fivepercent, "
                        "generated, "
                        "hightolow, "
                        "max, "
                        "median, "
                        "min, "
                        "stddev, "
                        "variance, "
                        "volume, "
                        "wavg, "
                        "source) "
                        "VALUES ( "
                        "{dir}, " 
                        "{itm}, "
                        "{rgn}, "
                        "{sys}, "
                        "{avg}, "
                        "{pcn}, "
                        "{gnr}, "
                        "{htl}, "
                        "{max}, "
                        "{mdn}, "
                        "{min}, "
                        "{dev}, "
                        "{vrn}, "
                        "{vol}, "
                        "{wvg}, "
                        "{src})".
                        format(
                        dir = "'they_buy'", 
                        itm = x["forQuery"]["types"][0],
                        rgn = x["forQuery"]["regions"][0],
                        sys = x["forQuery"]["systems"][0],
                        avg = x["avg"],
                        pcn = x["fivePercent"],
                        gnr = x["generated"],
                        htl = x["highToLow"],
                        max = x["max"],
                        mdn = x["median"],
                        min = x["min"],
                        dev = x["stdDev"],
                        vrn = x["variance"],
                        vol = x["volume"],
                        wvg = x["wavg"], 
                        src = "'eve-market'")
                        )
        except psycopg2.IntegrityError:
            self.connQ.rollback()
            logger.debug('Duplicate market data, connQ.execute rolled back!')
        else:
            self.connQ.commit() 
        #sell orders, where we buy something from them
        x = data["sell"]
        if x["forQuery"]["regions"]:
            pass
        else:
            x["forQuery"]["regions"].append(0)
        
        if x["forQuery"]["systems"]:
            pass
        else:
            x["forQuery"]["systems"].append(0)
        try:
            self.currQ.execute("INSERT INTO marketprices " 
                        "(direction, " 
                        "item, "
                        "region, "
                        "system, "
                        "avg, "
                        "fivepercent, "
                        "generated, "
                        "hightolow, "
                        "max, "
                        "median, "
                        "min, "
                        "stddev, "
                        "variance, "
                        "volume, "
                        "wavg, "
                        "source) "
                        "VALUES ( "
                        "{dir}, " 
                        "{itm}, "
                        "{rgn}, "
                        "{sys}, "
                        "{avg}, "
                        "{pcn}, "
                        "{gnr}, "
                        "{htl}, "
                        "{max}, "
                        "{mdn}, "
                        "{min}, "
                        "{dev}, "
                        "{vrn}, "
                        "{vol}, "
                        "{wvg}, "
                        "{src})".
                        format(
                        dir = "'they_sell'", 
                        itm = x["forQuery"]["types"][0],
                        rgn = x["forQuery"]["regions"][0],
                        sys = x["forQuery"]["systems"][0],
                        avg = x["avg"],
                        pcn = x["fivePercent"],
                        gnr = x["generated"],
                        htl = x["highToLow"],
                        max = x["max"],
                        mdn = x["median"],
                        min = x["min"],
                        dev = x["stdDev"],
                        vrn = x["variance"],
                        vol = x["volume"],
                        wvg = x["wavg"], 
                        src = "'eve-market'")
                        )
        except psycopg2.IntegrityError:
            self.connQ.rollback()
            logger.debug('Duplicate market data, connQ.execute rolled back!')
        else:
            self.connQ.commit() 
        #all orders - given as an average of both
        x = data["all"]
        if x["forQuery"]["regions"]:
            pass
        else:
            x["forQuery"]["regions"].append(0)
        
        if x["forQuery"]["systems"]:
            pass
        else:
            x["forQuery"]["systems"].append(0)
        try:
            self.currQ.execute("INSERT INTO marketprices " 
                        "(direction, " 
                        "item, "
                        "region, "
                        "system, "
                        "avg, "
                        "fivepercent, "
                        "generated, "
                        "hightolow, "
                        "max, "
                        "median, "
                        "min, "
                        "stddev, "
                        "variance, "
                        "volume, "
                        "wavg, "
                        "source) "
                        "VALUES ( "
                        "{dir}, " 
                        "{itm}, "
                        "{rgn}, "
                        "{sys}, "
                        "{avg}, "
                        "{pcn}, "
                        "{gnr}, "
                        "{htl}, "
                        "{max}, "
                        "{mdn}, "
                        "{min}, "
                        "{dev}, "
                        "{vrn}, "
                        "{vol}, "
                        "{wvg}, "
                        "{src})".
                        format(
                        dir = "'they_all'", 
                        itm = x["forQuery"]["types"][0],
                        rgn = x["forQuery"]["regions"][0],
                        sys = x["forQuery"]["systems"][0],
                        avg = x["avg"],
                        pcn = x["fivePercent"],
                        gnr = x["generated"],
                        htl = x["highToLow"],
                        max = x["max"],
                        mdn = x["median"],
                        min = x["min"],
                        dev = x["stdDev"],
                        vrn = x["variance"],
                        vol = x["volume"],
                        wvg = x["wavg"], 
                        src = "'eve-market'")
                        )
        except psycopg2.IntegrityError:
            self.connQ.rollback()
            logger.debug('Duplicate market data, connQ.execute rolled back!')
        else:
            self.connQ.commit() 
        
    #get Stored Sell values
    def get_stored_sale_price(self, item, system):
        logger.debug("get_stored_market_price item = {id}, system = {sys}".
                     format(id = item, sys = system))
        self.currQ.execute("SELECT min FROM marketprices WHERE (direction = \'they_sell\' AND "
                      "item = {it} AND system = {sys}) "
                      "ORDER BY generated DESC "
                      "LIMIT 1".
                      format(it = item, sys = system))
        #print ("item {id} system {sys}".format(id = item, sys = system))
        #print ("db returns.. {db}".format(db = self.currQ.fetchone()[0]))
        x = self.currQ.fetchone()
        return (round(x[0], 2))
    
    def get_ingame_history(self, item, region):
        logger.debug("entering get_ingame_history item  = {id}, region = {rg}".
                     format(id = item, rg = region))
        self.currQ.execute("SELECT * FROM markethistory "
            "WHERE (type = {id} AND region = {rgr}) "
            "ORDER BY date DESC "
            "LIMIT 1".
            format(id = item, rgr = region))
        data = self.currQ.fetchone()
        results = {}
        logger.debug("databse returned but has not yet looked at {db} type {ty}".format(db = data, ty = type(data)))
        try:
            assert type(data) != type(None)
            results["min"] = round(data[5], 2)
            results["avg"] = round(data[2], 2)
            results["max"] = round(data[4], 2)
            results["vol"] = data[7]
            results["ord"] = data[6]
        except:
            logger.info("database resturned, {db}, the item probably isn't being sold in that region ".format(db = data))
            results["min"] = 0
            results["avg"] = 0
            results["max"] = 0
            results["vol"] = 0
            results["ord"] = 0
        return results
    
    #itemID, locationID, typeID, quantity, flag, singleto
    def find_cheapest(self, item):
        logger.debug("finding cheapest")
        hubs = ["Rens", "Jita", "Amarr"]
        cheapest_location = None
        cheapest_price = -1
        for hub in hubs:
            #self.now_value_system(hub, item)
            try:
                if (cheapest_price == -1) and (self.get_stored_sale_price(item, self.queries.get_system_id(hub)) > 0):
                    #print ("cheapest price = -1")
                    cheapest_location = hub
                    cheapest_price = self.get_stored_sale_price(item, self.queries.get_system_id(hub))
                elif self.get_stored_sale_price(item, self.queries.get_system_id(hub)) < cheapest_price:
                    cheapest_location = hub
                    cheapest_price = self.get_stored_sale_price(item, self.queries.get_system_id(hub))
            except:
                logger.debug("finding chepest - exception block")
                self.insert_market_price(self.now_value_system(self.queries.get_system_id(hub), item))
                self.get_stored_sale_price(item, self.queries.get_system_id(hub))
        return (cheapest_location, cheapest_price)
             
        
    def get_none_sold_dict(self, items, system):
        logger.debug("get_none_sold_dict")
        these_arent_sold = []
        for item in items:
            if self.get_stored_sale_price(item, system) == 0:
                these_arent_sold.append(item)
        return these_arent_sold
    
    def get_profit_on_items(self, items, from_system, to_system):
        """ Returns a dictionary {item: [from, to, difference]}
        """
        profit_from_item = {}
        for item in items:
            profit = ([self.get_stored_sale_price(item, self.queries.get_system_id(from_system)),
                       self.get_stored_sale_price(item, self.queries.get_system_id(to_system)),
                       (round(self.get_stored_sale_price(item, self.queries.get_system_id(to_system)) - 
                              self.get_stored_sale_price(item, self.queries.get_system_id(from_system)), 2))])
            profit_from_item[self.queries.get_item_name(item)] = profit
        #headers = ["Item", from_system, to_system] 
        return profit_from_item

    #find items which are cheaper in hek than they are in rens
    
    def trade_between_two_locations(self, items, from_location = "Rens", to_location = "Hek"):
        """ Returns a list of items (and the profit) which are selling for higher at the to_location than they are the from_location 
        """
        sell_these = []
        for theItems in items:
            diff = (self.get_stored_sale_price(theItems, self.queries.get_system_id(to_location)) - 
                    self.get_stored_sale_price(theItems, self.queries.get_system_id(from_location)))
            if (diff > 0):
                #print (queries.get_item_name(theItems), diff, theItems)
                sell_this = [self.queries.get_item_name(theItems), round(diff, 2)]
                sell_these.append(sell_this)
            else:
                pass
        headers = ["item", "profit"]
        sell_these.sort(key=lambda x: x[1], reverse=True)
        return headers, sell_these
    

    # finds items unsold in a system and finds the price in two other hubs
    
    def unsold_prices_elsewhere(self, items, location = "Lustrevik", location1 = "Jita", location2 = "Rens"):
        """ Returns a list items which are unsold at location and the prices where they are available at location 1 and 2 
        """
        # find items which aren't being sold at the location
        sell_these = self.get_none_sold_dict(items, self.queries.get_system_id(location))
        # 
        sell_these = self.get_profit_on_items(sell_these, location1, location2)
        # sort them based on the profit
        sell_these = sorted(sell_these.items(), key=lambda e: e[1][2], reverse = True)
        text = [[aa, bb, cc, dd] for aa, (bb, cc, dd) in sell_these]
        headers = ["item", "{l1}".format(l1 = location1), "{l2}".format(l2 = location2), "profit"]
        return headers, text

    def pupulate_psql(self, items, systems):
        # populate marketprices and markethistory
        logger.debug("entering pupulate_psql")
        for system in systems:
            for theItems in items:
                region = self.queries.get_region_id_from_system(self.queries.get_system_id(system))
                self.markets.get_date_last_entry(theItems, region)
                self.insert_market_price(self.now_value_system(self.queries.get_system_id(system), theItems))

    def buy_or_sell(self, item, system):
        region = self.queries.get_region_id_from_system(system)
        ec_data = self.get_stored_sale_price(item, system)
        eve_data = self.get_ingame_history(item, region)
        range = eve_data["max"] - eve_data["min"]
        equal_avg = range / 2.0
        weak_threshold = (equal_avg * 0.1)
        strong_threshold = (equal_avg * 0.40)
        if eve_data["avg"] < equal_avg - weak_threshold:
            action =  "buy"
        if eve_data["avg"] < equal_avg - strong_threshold:
            actiona = "strong buy"
        if eve_data["avg"] > equal_avg + weak_threshold:
            action =  "sell"
        if eve_data["avg"] > equal_avg + strong_threshold:
            action = "strong sell"
        else:
            action = "either"
        #print (range, equal_avg, weak_threshold, strong_threshold)
        return action