git.alexw.nyc home about git garden
    1
    2
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   36
   37
   38
   39
   40
   41
   42
   43
   44
   45
   46
   47
   48
   49
   50
   51
   52
   53
   54
   55
   56
   57
   58
   59
   60
   61
   62
   63
   64
   65
   66
   67
   68
   69
   70
   71
   72
   73
   74
   75
   76
   77
   78
   79
   80
   81
   82
   83
   84
   85
   86
   87
   88
   89
   90
   91
   92
   93
   94
   95
   96
   97
   98
   99
  100
  101
  102
  103
  104
  105
  106
  107
  108
  109
  110
  111
  112
  113
  114
  115
  116
  117
  118
  119
  120
  121
  122
  123
  124
  125
  126
  127
  128
  129
  130
  131
  132
  133
  134
  135
  136
  137
  138
  139
  140
  141
  142
  143
  144
  145
  146
  147
  148
  149
  150
  151
  152
  153
  154
  155
  156
  157
  158
  159
  160
  161
  162
  163
  164
  165
  166
  167
  168
  169
  170
  171
  172
  173
  174
  175
  176
  177
  178
  179
  180
  181
  182
  183
  184
  185
  186
  187
  188
  189
  190
  191
  192
  193
  194
  195
  196
  197
  198
  199
  200
  201
  202
  203
  204
  205
  206
# set GOOG_KEY env variable to a valid api key
# all rounds available at https://sites.google.com/view/3cb-metashape/pairings-results/past-results?authuser=0
# depends on requests and stdlib
# writes to 3cmdata.csv
# TODO upload the csv, print out the round msg

import requests, os, csv, re, io, sqlite3, titlecase, string, openpyxl, urllib
from Levenshtein import distance

con = sqlite3.connect("3cb.db")
allcards = set(open('allcards.txt','r').read().splitlines())

# cache
def fileids_in_db():
    return [a[0] for a in con.cursor().execute("select fileid from round").fetchall()]

def bans_from_db():
    return [a[0] for a in con.cursor().execute("select name from bans").fetchall()]

def main():
    cache = fileids_in_db()
    for n, file in enumerate(get_round_fileids()):
        if file in cache:
            continue
        if file not in cache and file != "1LGHvTrQz2zhBjz1PhlW61ZYmwRWJyWkEHj-png7ZKdw": # misc file
            print(f"analyzing round {n+1}")
            save_round(n+1, file)

# scrape site to list rounds
def get_round_fileids():
    allrounds = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results/past-results")
    sheetre = re.compile("spreadsheets/d/(.*?)/")
    curr_round = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results")
    ids = sheetre.findall(allrounds.text + curr_round.text)
    return ids

def file_to_wb(fileid):
    params = {"mimeType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}
    headers = {"x-goog-api-key": os.environ.get("GOOG_KEY")}
    out = []
    res = requests.get(f"https://www.googleapis.com/drive/v3/files/{fileid}/export", 
                   params=params,
                   headers=headers)
    if res.status_code != 200:
        print(res.text)
        os.exit(1)
    return openpyxl.load_workbook(io.BytesIO(res.content), data_only=True)

def update_bans():
    sheet = file_to_wb("1NZuROOCctbq4p4-CAHE-jOC0675QQjuqVnUqdXFCVD8")["banlist"]
    cards = [row[1].value for row in [*sheet.rows][3:92]]
    cur = con.cursor()
    cur.execute("drop table if exists ban")
    cur.execute("create table ban (name text)")
    for card in cards:
        if card:
            cur.execute("insert into ban values (?)", (clean_card(card),))
    con.commit()

# not really normalized well
schema = ["""
create table if not exists round (
    id integer primary key,
    fileid text not null
);
""",
"""
create table if not exists deck (
    round integer not null,
    player varchar not null,
    card1 text,
    card2 text,
    card3 text
);""","""
create table if not exists match (
    round integer,
    group_name text,
    player text, 
    opp_player text,
    score integer
);"""]

def save_sheet(cur, sheet, n, final):
    sheetrows = list(sheet.rows)
    rows = [[cell.value for cell in row if cell.value != None] for row in sheetrows]
    group = ""
    i = 0
    while i < len(rows):
        row = rows[i]
        i += 1
        if len(row) < 3:
            continue
        group = row[0].partition(" ")[2]
        if final:
            group = "final"
        try:
            start = row.index("VS") + 1
            end = row.index("Score")
            if sheetrows[0][0].value == None: # missing text in upper left cell
                start += 1
                end += 1
        except ValueError:
            start = 2
            end = len(row)
        players = row[start:end]
        for _ in range(len(players)):
            row = rows[i]
            i += 1
            player = row[0]
            if len(player) > 50 and player[0] == "B":
                player = "Beefman" # messy unicode
            if player == "IGNORE WAS MISTAKE":
                continue
            cards = row[1].split("\n")
            if not final: #already done
                cur.execute("insert into deck values (?,?,?,?,?);", (n, player, clean_card(cards[0]), clean_card(cards[1]), clean_card(cards[2])))
            for o, s in enumerate(row[start:end]):
                if s == "?":
                    s = 0
                cur.execute("insert into match values (?, ?, ?, ?, ?);", 
                            (n, group, player,players[o],int(s)));

def save_round(n, fileid):
    wb = file_to_wb(fileid)
    try:
        groups = wb["Groups"]
    except KeyError:
        groups = wb["Group"]
    cur = con.cursor()
    save_sheet(cur, groups, n, False)
    if len(wb._sheets) > 1:
        final = wb["Final Group"]
        save_sheet(cur, final, n, True)
    cur.execute("insert into round values (?, ?);", (n, fileid));
    con.commit()
# fixing some data issues
def best_guess(card):
    m = 6
    outcard = ""
    for c in allcards:
        if c.startswith(card):
            return c
        d = distance(card, c, score_cutoff=5)
        if d < m:
            m = d
            outcard = c
    if outcard:
        return outcard
    return card


# data cleaning
def replacement(card):
    m = {
            "Thallid Oh Yeah": "Thallid",
            "Forest!!!!!!": "Forest",
            "Bayou - Not Legal": "Bayou",
            "Bottomless Depths": "Bottomless Vault",
            "Gargadon (Neither Greater Nor Lesser)": "Gargadon",
            "Tabernacle at Penrall Vale": "The Tabernacle at Pendrell Value",
            "Nesumi Shortfang": "Nezumi Shortfang // Stabwhisker the Odious",
            "Filigree Sylex": "The Filigree Sylex",
            "Gavel of the Righteous, for I Am Stubborn and Naively Hopeful": "Gavel of the Righteous",
            "Annex Chan": "Chancellor of the Annex",
            "Annex-Chan": "Chancellor of the Annex",
            "Forge-Chan": "Chancellor of the Forge",
            "Basic Plains": "Plains",
            "Urborg Tomb Yawgy": "Urborg, Tomb of Yawgmoth",
            "Urborg (The One That Makes All Lands Swamps)": "Urborg, Tomb of Yawgmoth",
            "Chronomatonton (The 1 Cost 1/1 That Taps to Get Bigger)": "Chronomaton",
            "That One Wurm That Makes the Three 5/5s When It Dies I Have Done Too Many Scryfall Searches Today Sorry": "TBD",
            "Chancelor of the Tangle (Sic)": "Chancellor of the Tangle",
            "Karaka (Its Listed as Unbanned but the Form Wont Let Me Submit It Idk Its My First Time Lol)": "Karakas",
            "Restore Balanse (I Have to Misspell This Cuz the Regex Is Buggy Lmao)": "Restore Balance",
            "Elspeth Suns Champion Wooooooo": "Elspeth, Sun's Champion",
            "Plains (ONS 333)": "Plains",
            "Swamp (XLN 270)": "Swamp",
            "Ulamog the Infinite Gyre (Borderless) (Foil)": "Ulamog, the Infinite Gyre",
            "Dark Ritual (BTD 21)": "Dark Ritual",
            "Swamp (BRB 18)": "Swamp",
            "Monty Python and the Holy Grail Black Knight (Oathsworn Knight)": "Oathsworn Knight",
            "Red Sun's Zenith (Again)": "Red Sun's Zenith",
            "Dwarven Hold (Again)": "Dwarven Hold",
            "Speaker of the Heavens!?!?!?!": "Speaker of the Heavens",
            "Dark Ritual (STA #89)": "Dark Ritual",
            "Swamp (8ED #339)": "Swamp",
        }
    if card in m:
        return m[card]
    return card

def clean_card(card_name):
    clean = titlecase.titlecase(card_name.strip())
    clean = ''.join(filter(lambda x: x in string.printable, clean))
    clean = clean.replace("’", "'")
    clean = replacement(clean)
    if clean not in allcards:
        return best_guess(clean)
    return clean

if __name__ == "__main__":
    for table in schema:
        con.execute(table)
    update_bans()
    main()