def finance_fromusatoeu():
editions = Gatherer.getEditions()
usdtoeu = ExchangeRate.get("USDEUR=X")
comisionmkm = 0.05 # comision que se queda mkm de las ventas
undercut = 0.05 # undercut para vender
ajustebeneficio = 0.15 # margen de perdida que asumo al vender de usa a eur (store credit me da 0.3)
storecreditbonus = 0.3
with open("output/usatoeu.csv", "w", newline='\n') as f:
writer = csv.DictWriter(
f,
fieldnames=["set", "name", "foil", "ck", "mkm", "available"],
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_MINIMAL)
writer.writeheader()
# TODO: DEFINIR LO QUE ES POTENTIAL ---> marcar cuando es un staple y etc.
sql = "select s.name as set, c.name as name, truefalse.value as foil, round(cast(ck.price * {} as numeric), 2) as ck, round(cast(mkm.price * {} as numeric), 2) as mkm, ck.available as available from scr_cards c CROSS JOIN truefalse left join scr_sets s on c.set = s.code left join ck_cardprices ck on c.idck = ck.card and truefalse.value = ck.foil left join mkm_cardpricesmin mkm on c.idmkm = mkm.id and truefalse.value = mkm.foil where (not mkm.name is null and not ck.card is null) and not s.digital and ck.available > 0 and ck.condition = 'NM'".format(
usdtoeu, 1 - comisionmkm - undercut)
cards = phppgadmin.query(sql)
with open("output/usatoeu.csv", "a", newline='\n') as f:
writer = csv.DictWriter(
f,
fieldnames=["set", "name", "foil", "ck", "mkm", "available"],
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_MINIMAL)
for card in cards:
card["ck"] = card["ck"].replace(".", ",")
card["mkm"] = card["mkm"].replace(".", ",")
writer.writerow(card)
def normalizeEditions():
print("TOTAL", len(phppgadmin.query("SELECT code FROM editions")))
print(
"CK UPDATES",
phppgadmin.execute(
"UPDATE editions SET code_ck = ck.id FROM ck_editions ck WHERE lower(ck.name) = lower(editions.name)"
))
print(
"MKM UPDATES",
phppgadmin.execute(
"UPDATE editions SET code_mkm = mkm.id FROM mkm_editions mkm WHERE lower(mkm.name) = lower(editions.name)"
))
def getEditions():
editions = []
try:
editions = phppgadmin.query(
"select id, name, url from ck_editions")
except: #TODO: capturar excepcion que corresponda
print("Using cached editions")
editions = []
with open("data/ck/editions.csv") as csvfile:
reader = csv.DictReader(csvfile, delimiter="|")
for row in reader:
editions.append(row)
if (len(editions) == 0):
CK.crawlEditions()
return editions
def getEditions():
editions = []
datadir = "data/mkm"
datafile = "{}/editions.csv".format(datadir)
try:
editions = phppgadmin.query(
"select id, name, url from mkm_editions")
except: #TODO: capturar excepcion que corresponda
try:
with open(datafile) as csvfile:
reader = csv.DictReader(csvfile, delimiter="|")
for row in reader:
editions.append(row)
except:
pass
if (len(editions) == 0):
editions = MKM.crawlEditions()
return editions
def getAllPrices():
cards = phppgadmin.query(
"SELECT c.name, s.name as set, c.idmkm, s.isfoil FROM scr_cards c LEFT JOIN (select s1.code, s1.name, s1.set_type, s1.digital, false as isfoil from scr_sets s1 union all select s2.code, s2.name, s2.set_type, s2.digital, true as isfoil from scr_sets s2 where code not in ('lea','leb','2ed','cei','ced','arn','atq','3ed','leg','sum','drk','fem','4ed','ice','chr','hml','all','rqs','mir','mgb','itp','vis','5ed','por','wth','tmp','sth','p02','exo','ugl','usg','ath','6ed','ptk','s99','brb','s00','btd','dkm','phpr') and not foil) s on c.set = s.code WHERE NOT idmkm IS NULL AND NOT s.digital AND s.set_type in ('archenemy','commander','conspiracy','core','duel_deck','expansion','from_the_vault','masterpiece','planechase','premium_deck','starter')"
)
n = 1000
# with open("output.csv", "w", newline='\n') as f:
# writer = csv.DictWriter(f, fieldnames=["idmkm", "price", "isfoil", "available", "seller", "itemlocation"], delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
# writer.writeheader()
q = Queue()
startTGetPriceData(6, q)
for i in range((int)(len(cards) / n) + 1):
print("i=", i)
cardswprices = cards[i * n:i * n + n]
try:
for card in cardswprices:
card["isFoil"] = True if card["isfoil"] == "TRUE" else False
card["idLanguage"] = 1
q.put(card)
q.join()
except KeyboardInterrupt:
sys.exit(1)
# a csv
# with open("output.csv", "a", newline='\n') as f:
# writer = csv.DictWriter(f, fieldnames=["idmkm", "price", "isfoil", "available", "seller", "itemlocation"], delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
# for card in cardswprices:
# for price in card["prices"]:
# writer.writerow({ "idmkm": card["idmkm"], "price": price["price"], "isfoil": card["isfoil"], "available": price["available"], "seller": price["seller"], "itemlocation": price["itemlocation"] })
# a db
sql = ""
for card in cardswprices:
for price in card["prices"]:
sql += "('{}',{},{},{},'{}','{}'),".format(
card["idmkm"], price["price"], card["isfoil"],
price["available"], price["seller"].replace("'", "''"),
price["itemlocation"])
if sql != "":
affected = phppgadmin.execute(
"INSERT INTO mkm_cardprices(id,price,foil,available,seller,itemlocation) VALUES"
+ sql[:-1])
print("Total prices inserted: {}".format(affected))
# materialized view completa
phppgadmin.execute(
"DROP MATERIALIZED VIEW mkm_cardpricesranked;CREATE MATERIALIZED VIEW mkm_cardpricesranked AS SELECT id, price, foil, available, seller, itemlocation, dense_rank() OVER (PARTITION BY id,foil ORDER BY price ASC),first_value(price) OVER (PARTITION BY id,foil ORDER BY price ASC),lead(price) OVER (PARTITION BY id,foil ORDER BY price ASC) FROM mkm_cardprices WITH DATA; ALTER TABLE mkm_cardpricesranked OWNER TO postgres;"
)
def mkmprocess_savestore():
#phppgadmin.execute("delete from mkm_cardprices")
sqlnextedition = "select e.code_mkm as id, e.name from editions e inner join mkm_editions mkm on e.code_mkm = mkm.id left join mkm_cardprices p on p.edition = mkm.id group by e.code_mkm, e.name, mkm.locked having count(p.card) = 0 and not mkm.locked limit 1"
while True:
editions = phppgadmin.query(sqlnextedition)
if (len(editions) == 1):
edition = editions[0]
phppgadmin.execute(
"update mkm_editions set locked = true where id = '{}'".format(
edition["id"]))
print(edition["name"])
cards = MKM.getPrices(edition)
sql = ""
for card in cards:
for entry in card.entries:
sql += "('{}','{}',{},{},{},'{}','{}'),".format(
card.id, card.edition,
entry.price, entry.foil, entry.count,
entry.seller.replace("'", "''"), entry.location)
if sql != "":
affected = phppgadmin.execute(
"INSERT INTO mkm_cardprices(card,edition,price,foil,available,seller,itemlocation) VALUES"
+ sql[:-1])
print("Total prices inserted: {}".format(affected))
else:
print("No price data")
phppgadmin.execute(
"update mkm_editions set locked = false where id = '{}'".
format(edition["id"]))
else:
break
# Rehacer tabla de precios minimos cuando no hay mas ediciones a procesar
if (phppgadmin.count("select id from mkm_editions where locked") == 0):
print("Creando tabla de precios min para hoy (unos 5 minutos)")
phppgadmin.execute(
"DROP MATERIALIZED VIEW mkm_cardpricesmin;CREATE MATERIALIZED VIEW mkm_cardpricesmin AS SELECT mkm_cardprices.edition,mkm_cardprices.card as name, mkm_cardprices.foil, min(mkm_cardprices.price) AS price FROM mkm_cardprices GROUP BY mkm_cardprices.edition, mkm_cardprices.card, mkm_cardprices.foil WITH DATA; ALTER TABLE mkm_cardpricesmin OWNER TO postgres;"
)
print("Finished")
def finance_fromeutousa():
editions = Gatherer.getEditions()
usdtoeu = ExchangeRate.get("USDEUR=X")
comisionporgastosdeenvio = 0.05
precioparaenviocertificado = 25
profittargetpct = 1.1
sql = "select s.name as set, c.name as card, ck.foil as foil, ck.price * {0} as ck, mkm_offers.price as mkm, mkm_offers.seller as seller, mkm_offers.available as available from ck_buylist ck left join scr_cards c on ck.id = c.idck left join scr_sets s on c.set = s.code inner join (select id, foil, round(cast(((price * LEAST(available, 17)) + (select cost from mkm_shippingcosts sc where sc.from = itemlocation and sc.itemcount <= LEAST(available, 17) and tracked = price >= 25 order by itemcount desc limit 1)) / LEAST(available, 17) as numeric), 2) as price, LEAST(available, 17) as available, seller from mkm_cardprices) mkm_offers on mkm_offers.id = c.idmkm and mkm_offers.foil = ck.foil where mkm_offers.price < ck.price * {0}".format(
usdtoeu - comisionporgastosdeenvio)
cards = phppgadmin.query(sql)
with open("output/eutousa.csv", "w", newline='\n') as f:
writer = csv.DictWriter(f,
fieldnames=[
"set", "card", "foil", "ck", "mkm",
"seller", "available"
],
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_MINIMAL)
writer.writeheader()
for card in cards:
writer.writerow(card)
def getEditions():
return phppgadmin.query(
"SELECT code as id, name, code_ck, code_mkm FROM editions")
def getPrices(edition=None):
def do_work(item):
carddir = MKM.cachedir.format("prices/{}/{}".format(
edition["id"], item["card"].id))
try:
if not os.path.exists(carddir):
os.makedirs(carddir)
except:
pass
cardpagecache = "{}/{}.html".format(
carddir, "foil" if item["foil"] else "normal")
try:
f = open(cardpagecache, "r", encoding="utf8")
data = f.read()
f.close()
except IOError:
if item["foil"]:
filter = copy.copy(productFilter)
filter["productFilter[isFoil]"] = "Y"
else:
filter = productFilter
try:
resp = requests.post("{}/Products/Singles/{}/{}".format(
MKM.baseurl, item["card"].edition, item["card"].id),
filter,
headers={},
timeout=10)
data = resp.text
except:
data = ""
# Proteccion contra respuestas vacias, encolamos de nuevo la solicitud
if data != "":
with open(cardpagecache, "w", encoding="utf8") as f:
f.write(data)
else:
print("Me tiran solicitud, relanzo en 10s")
q.put(item, True, 10)
if data != "":
tree = html.fromstring(data)
for row in tree.xpath(
'//tbody[@id="articlesTable"]/tr[not(@class)]'):
itemlocation = row.xpath(
".//td[@class='Seller']/span/span/span[@class='icon']"
)[0].attrib["onmouseover"]
itemlocation = re.search(reItemLocation,
itemlocation).group(1)
seller = row.xpath(".//td[@class='Seller']/span/span/a"
)[0].attrib["href"].replace(
"/en/Magic/Users/", "")
price = row.xpath(".//td[contains(@class,'st_price')]"
)[0].text_content().replace(",",
".").replace(
"€", "")
available = row.xpath(
".//td[contains(@class,'st_ItemCount')]"
)[0].text_content()
ppu = re.search(rPPU, price)
if not ppu is None:
price = ppu.group(1)
available = "4"
item["card"].entries.append(
CardDetails((float)(price), (int)(available),
True if item["foil"] else False, "en",
"NM", seller, itemlocation))
sys.stdout.write("Elementos restantes: %d \r" % q.qsize())
sys.stdout.flush()
def worker():
while True:
do_work(q.get())
time.sleep(0.05)
q.task_done()
if (edition is None):
editions = MKM.selectEdition()
else:
editions = [edition]
sql = "select c.id as id, c.name as name, c.edition as edition, e.type as type from editions e left join mkm_cards c on c.edition = e.code_mkm where not e.code_mkm is null"
if (len(editions) == 1):
sql += " AND c.edition = '{}'".format(editions[0]["id"])
dbcards = phppgadmin.query(sql)
q = Queue()
for i in range(MKM.maxthreads):
t = threading.Thread(target=worker)
t.daemon = True
t.start()
cards = []
productFilter = {
"productFilter[sellerRatings][]": ["1", "2"],
"productFilter[idLanguage][]": [1],
"productFilter[condition][]": ["MT", "NM"]
}
rPPU = '\(PPU: (.*?)\)'
reItemLocation = "'Item location: (.*)'"
for dbcard in dbcards:
card = InventoryCard(dbcard["id"], dbcard["name"],
dbcard["edition"])
cards.append(card)
q.put({"card": card, "foil": False})
if dbcard["type"] == "3":
q.put({"card": card, "foil": True})
q.join()
print("==[ END ]== ")
return cards
def buylist(writecsv=False):
#TODO: numero dinamico de paginas
print("==[ CK BUYLIST ]==")
def addCards(pagehtml):
tree = html.fromstring(pagehtml)
cardshtml = tree.xpath(
"//div[contains(@class,'itemContentWrapper')]")
for cardhtml in cardshtml:
pricewrapper = cardhtml.xpath(".//span[@class='stylePrice']")
# si no tiene precio es una premium card de las que hay que preguntar precio
if len(pricewrapper) == 1:
pricewrapper = pricewrapper[0]
id = pricewrapper.xpath(
'.//form/input[@class="product_id"]'
)[0].attrib["value"]
#name = cardhtml.xpath(".//span[@class='productDetailTitle']/text()")[0]
#edition = cardhtml.xpath(".//div[@class='productDetailSet']/text()")[0]
#edition = re.search(reEdition, edition).group(1).strip()
#editionid = None
# traducimos nombre de edicion a id
# for e in editions:
# if (e["name"] == edition):
# editionid = e["id"]
# break
# if not editionid is None:
# edition = editionid
# else:
# print("Edicion no encontrada", edition, name)
# edition = 0
price = "{}.{}".format(
pricewrapper.xpath(
".//div[@class='usdSellPrice']/span[@class='sellDollarAmount']"
)[0].text_content().replace(",", ""),
pricewrapper.xpath(
".//div[@class='usdSellPrice']/span[@class='sellCentsAmount']"
)[0].text_content())
# el credit se puede sacar multiplicando por 1.3
maxQty = pricewrapper.xpath(
'.//form/input[@class="maxQty"]')[0].attrib["value"]
foil = len(cardhtml.xpath(".//div[@class='foil']")) > 0
#TODO: algunas veces se meten repetidas (mirar tokens)
# traducir id de la foil a la normal
if foil:
for translation in idtranslations:
if id == translation["foil"]:
id = translation["normal"]
break
inventorycard = InventoryCard(id, "", "")
inventorycard.entries.append(
CardDetails(price, maxQty, foil, "en", "NM"))
buylist.append(inventorycard)
def do_work(page):
sys.stdout.write("Paginas procesadas: %d%% \r" %
(page * 100 / npages))
sys.stdout.flush()
filename = "{}/page{}.html".format(cachedir, page)
try:
f = open(filename, "r", encoding="utf8")
data = f.read()
f.close()
except:
page = requests.get("{}{}".format(baseurl, page))
data = page.text
with open(filename, "w", encoding="utf8") as f:
f.write(data)
addCards(data)
def worker():
while True:
item = q.get()
do_work(item)
q.task_done()
baseurl = "https://www.cardkingdom.com/purchasing/mtg_singles?filter%5Bipp%5D=100&filter%5Bsort%5D=name&filter%5Bsearch%5D=mtg_advanced&filter%5Bname%5D=&filter%5Bcategory_id%5D=0&filter%5Bfoil%5D=1&filter%5Bnonfoil%5D=1&filter%5Bprice_op%5D=&filter%5Bprice%5D=&page="
# cuantas paginas?
req = requests.get(baseurl + "1")
tree = html.fromstring(req.text)
results = tree.xpath("//span[@class='resultsHeader']/text()")[0]
npages = (int)((
(int)(re.search("(\d*) results", results).group(1)) / 100) + 1)
print("Paginas:", npages)
today = time.strftime("%Y%m%d")
cachedir = CK.cachedir.format("buylist/" + today)
if not os.path.exists(cachedir):
os.makedirs(cachedir)
#reEdition = "(.*)\("
lock = threading.Lock()
buylist = []
#sys.stdout.write("Obteniendo ediciones CK...")
#sys.stdout.flush()
#editions = CK.getEditions()
idtranslations = phppgadmin.query(
"SELECT foil,normal FROM ck_idtranslator")
#sys.stdout.write("OK [{} ediciones]".format(len(editions)))
#print("")
q = Queue()
for i in range(10):
t = threading.Thread(target=worker)
t.daemon = True
t.start()
start = time.perf_counter()
for p in range(npages):
q.put(p)
q.join()
print("Crawling finalizado ")
if (writecsv):
print("Guardando .csv en disco...")
filename = "{}/buylist.csv".format(cachedir)
with open(filename, "w", newline='\n') as f:
writer = csv.DictWriter(f,
fieldnames=[
"id", "name", "edition", "price",
"count", "foil", "language",
"condition"
],
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_MINIMAL)
writer.writeheader()
for card in buylist:
for entry in card.entries:
writer.writerow({
"id": card.id,
"name": card.name,
"edition": card.edition,
"price": entry.price,
"count": entry.count,
"foil": "true" if entry.foil else "false",
"language": entry.language,
"condition": entry.condition
})
print("==[ END ]==")
return buylist
self.edition = edition
self.foil = foil
self.page = page
class Edition:
def __init__(self, id=0, name='', url=''):
self.id=id
self.name=name
self.url=url
self.cards = []
exectime = time.time()
editions = []
try:
dbeditions = phppgadmin.query("select id, name, url from ck_editions")
#dbeditions = phppgadmin.query("select id, name, url from ck_editions where name = 'Zendikar'")
except:
print ("Using cached editions")
dbeditions = []
with open("data/ck/editions.csv") as csvfile:
reader = csv.DictReader(csvfile, delimiter="|")
for row in reader:
dbeditions.append(row)
for edition in dbeditions:
editions.append(Edition(edition["id"], edition["name"], edition["url"]))
sales = []
def getCards(page):
def finance_dondevendo():
def obtenseleccion(candidatos, myset):
if myset == "":
return None
else:
for i, cand in reversed(list(enumerate(candidatos))):
if cand["set"] == myset or (myset in son
and cand["set"] == son[myset]):
return cand
elif (myset in noson and cand["set"] in noson[myset]):
candidatos.pop(i)
if len(candidatos) == 0:
return None
elif len(candidatos) == 1:
return candidatos[0]
else:
print(":: {} [{}] ::".format(icard.name, icard.edition))
for i, cand in list(enumerate(candidatos)):
print("{}. {}".format(i, cand["set"]))
while True:
sel = input("Opcion: ")
print("")
# meterlas todas en noson
if sel == "":
if not myset in noson:
noson[myset] = []
noson[myset].extend(candidatos)
return None
else:
sel = (int)(sel)
cand = candidatos[sel]
son[myset] = cand["set"]
return cand
usdtoeu = ExchangeRate.get("USDEUR=X")
comisionmkm = 0.05 # comision que se queda mkm de las ventas
undercut = 0.05 # undercut para vender
inventory = Deckbox.inventory(True)
vender = []
print("Inventario: {} cartas diferentes".format(len(inventory)))
sql = "select s.name as set, c.name as name, truefalse.value as foil, round(cast(ck.price * {} as numeric), 2) as ck, round(cast(mkm.price * {} as numeric), 2) as mkm from scr_cards c CROSS JOIN truefalse left join scr_sets s on c.set = s.code left join ck_buylist ck on c.idck = ck.card and truefalse.value = ck.foil left join mkm_cardpricesmin mkm on c.idmkm = mkm.id and truefalse.value = mkm.foil where (not mkm.name is null and not ck.card is null) and not s.digital".format(
usdtoeu, 1 - comisionmkm - undercut)
allcards = phppgadmin.query(sql)
son = {}
noson = {}
for icard in inventory:
for entry in icard.entries:
# solo comparamos las cartas en ingles
if (entry.language == "English"):
candidatos = []
for card in allcards:
if icard.name.lower() == card["name"].lower(
) and entry.foil == (card["foil"] == "TRUE"):
candidatos.append(card)
seleccion = obtenseleccion(candidatos, icard.edition)
if not seleccion is None:
vender.append(seleccion)
with open("output/dondevendo.csv", "w", newline='\n') as f:
writer = csv.DictWriter(
f,
fieldnames=["set", "name", "foil", "ck", "mkm"],
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_MINIMAL)
writer.writeheader()
for card in vender:
card["ck"] = card["ck"].replace(".", ",")
card["mkm"] = card["mkm"].replace(".", ",")
writer.writerow(card)