def __init__(self, db_name):
self.db_util = DbUtil(db_name)
self.countries = self.db_util.list_countries()
self.extractor = ListingExtractor()
self.existing_games = self.db_util.list_games()
self.date = datetime.today().date()
def fetchTodoTask(self, TaskMode = 'default', mark='doing', number=-1):
data = None
with TaskScheduler.mutex:
sql_fetch1 = "SELECT id,rogerTime FROM task_%s WHERE status='todo' order by rogerTime ASC, id ASC LIMIT %d"
sql_fetch2 = "UPDATE task_%s SET status='%s' WHERE id=?"
db = DbUtil()
data = db.execute(sql_fetch1 % (TaskMode, TaskScheduler.taskPerTime if number<0 else number))
if len(data) > 0:
ids = [(id,) for (id,rogerTime) in data]
db.executeMany(sql_fetch2 % (TaskMode , mark) , ids)
return data
def rogerHost(self, host):
'''the parameter should be a dic contains items:'''
with HostScheduler.mutex:
sql_checkExists = "SELECT name FROM Host WHERE MAC='%s'"
sql_rogerHost = "INSERT OR REPLACE INTO Host (name, ip_local, ip_remote, mac, type, status) " \
"VALUES ('%s','%s','%s','%s','%s','OK')"
data = None
db = DbUtil()
exists = db.execute(sql_checkExists % host['MAC'])
if len(exists) == 0:
data = db.execute(sql_rogerHost %
(host['HostName'], host['IPLocal'], host['IPRemote'], host['MAC'], host['NodeType']))
return data
def ensureTaskMode(self, TaskMode = 'default'):
with TaskScheduler.mutex:
sql_checkTaskTables = "SELECT name FROM sqlite_master WHERE type='table' and name = 'Task_%s'"
sql_task = '''DROP TABLE IF EXISTS "Task_%s";
CREATE TABLE "Task_%s" (
"id" INTEGER NOT NULL,
"rogerTime" TIMESTAMP NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime')),
"finishTime" TIMESTAMP DEFAULT NULL,
"status" TEXT DEFAULT 'todo',
PRIMARY KEY ("id" ASC) ON CONFLICT FAIL
);'''
db = DbUtil()
exists = len( db.execute(sql_checkTaskTables % TaskMode) ) > 0
if not exists:
db.executeScript(sql_task % (TaskMode,TaskMode))
return 1 if exists else 0
# help="The list of the file name, this option could be specified several times")
options, args = parser.parse_args()
host = options.host
port = options.port
user = options.user
password = options.password
directory = options.directory
day = options.day
if host is None or port is None or user is None or password is None or directory is None or day is None:
parser.print_help()
sys.exit(0)
load_domain_suffix()
ad_map = DbUtil.get_ad_info()
logging.info("read ad info finished, length: %d" % len(ad_map))
city_provice_map = DbUtil.get_city_info()
logging.info("read city province info finished, length: %d" %
len(city_provice_map))
directory = directory.rstrip(os.sep)
t = threading.Thread(target=timer_func, args=(30, ))
t.start()
t2 = threading.Thread(target=flush_func,
args=(30, host, port, user, password))
t2.start()
for hour in range(0, 24, 1):
def rogerTasks(self, ids, TaskMode = 'default'):
with TaskScheduler.mutex:
db = DbUtil()
sql_roger = "INSERT OR REPLACE INTO task_%s (id) VALUES (?)"
data = db.executeMany(sql_roger % TaskMode, ids)
return data
class ListingTracker():
def __init__(self, db_name):
self.db_util = DbUtil(db_name)
self.countries = self.db_util.list_countries()
self.extractor = ListingExtractor()
self.existing_games = self.db_util.list_games()
self.date = datetime.today().date()
def save_listings(self):
self.update_currency_values()
for country in self.countries:
self.save_country_listings(country)
def update_currency_values(self):
currency_codes = self.db_util.list_currency_codes()
converter = CurrencyConverter()
for code in currency_codes:
try:
new_usd_rate = converter.get_conversion_rate(currency_code=code, base='USD')
self.db_util.update_currency_usd_conversion_rate(code, new_usd_rate)
except KeyError as missing_key:
print(f"Could not update conversion rate for currency {missing_key}")
def save_country_listings(self, country):
print(f"Extracting listings from: [{country.name}]")
country_new_listings = self.extractor.extract_listings(country.eshop_url)
for new_listing in country_new_listings:
self.save_country_listing(new_listing, country)
def save_country_listing(self, new_listing, country):
game = self.find_game_by_title(new_listing["game_title"])
new_listing_usd_value = self.calculate_usd_value(new_listing["price"], country)
if game:
if game.last_updated != self.date or new_listing_usd_value < game.min_price:
self.update_game_min_price(game, new_listing_usd_value, country)
else:
game = self.create_new_game(new_listing, new_listing_usd_value, country)
listing = Listing(original_value=new_listing["price"],
usd_value=new_listing_usd_value,
date=self.date,
game_id=game,
country_id=country)
self.db_util.save(listing)
def update_game_min_price(self, game, new_listing_usd_value, country):
game.min_price = new_listing_usd_value
game.min_price_country_id = country
game.last_updated = self.date
self.db_util.save(game)
def calculate_usd_value(self, original_value, country):
return original_value / country.currency_usd_conversion
def create_new_game(self, listing, price, country):
game = Game(title=listing["game_title"],
min_price=price,
min_price_country_id=country,
last_updated=self.date)
game.id = self.db_util.save(game)
self.existing_games.append(game)
return game
def find_game_by_title(self, game_title):
for existing_game in self.existing_games:
if existing_game.title == game_title:
return existing_game
return None