commit f4b4442e0f97c9a3db091fc3172210fa0a318f45
parent 2b5bf718695db3610a9c94903c84aeca3e3376eb
Author: alex wennerberg <alex@alexwennerberg.com>
Date: Sun, 4 Feb 2024 02:08:50 -0500
Improve export etc
Diffstat:
2 files changed, 71 insertions(+), 10 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -4,7 +4,7 @@
# writes to 3cmdata.csv
# TODO upload the csv, print out the round msg
-import requests, os, csv, re, io, sqlite3, titlecase, string, openpyxl
+import requests, os, csv, re, io, sqlite3, titlecase, string, openpyxl, urllib
from Levenshtein import distance
con = sqlite3.connect("3cb.db")
@@ -103,7 +103,7 @@ def save_sheet(cur, sheet, n, final):
row = rows[i]
i += 1
player = row[0]
- if player.startswith("BÌ"):
+ if len(player) > 50:
player = "[messy-name]"
cards = row[1].split("\n")
if not final: #already done
@@ -184,9 +184,57 @@ def clean_card(card_name):
return best_guess(clean)
return clean
+# TODO
+def card_link(card):
+ return f"https://scryfall.com/search?q={urllib.parse.quote_plus(card)}"
+
+def export_html():
+ with open("sql/export.sql") as f:
+ query = f.read()
+ res = con.cursor().execute(query)
+ print("""<html><head>
+ <style> table { font-family: Arial, Helvetica, sans-serif; border-collapse: collapse; width: 100%; }
+ td, th { border: 1px solid #ddd; padding: 8px; }
+ tr:nth-child(even){background-color: #f2f2f2;}
+ tr:hover {background-color: #ddd;}
+ #th {
+ padding-top: 12px;
+ padding-bottom: 12px;
+ text-align: left;
+ background-color: #04AA6D;
+ color: white;
+}</style>
+ </head><body><h1>3 card blind all data</h1><b>bold</b> = best showing for this card<br>
+ <table>
+ <th><td>player</td><td>card_1</td><td>card 2</td>
+ <td>card 3</td><td>group</td><td>score</td><td>final</td>
+ </th>
+ """)
+ # header TODO
+ # html escape TODO
+ for row in res:
+ print("<tr>")
+ print(f"<td>{row[0]}</td><td>{row[1]}</td>")
+ for i in range(2,5):
+ card = row[i]
+ cardl = card_link(card)
+ if row[i+3]:
+ print(f"<td><b><a href='{cardl}'>{card}</a></b></td>")
+ else:
+ print(f"<td><a href='{cardl}'>{card}</a></td>")
+ print(f"<td>{row[8]}</td>")
+ print(f"<td>{row[9]}</td>")
+ final = row[10] or ""
+ print(f"<td>{final}</td>")
+ print("</tr>")
+ print("</table></body></html>")
+
+
+
if __name__ == "__main__":
for table in schema:
con.execute(table)
- update_bans()
- main()
+ # update_bans()
+ # main()
+ export_html()
diff --git a/sql/export.sql b/sql/export.sql
@@ -1,7 +1,7 @@
/* best-per-round */
-.headers on
-.mode list
-.separator "\t"
+/* .headers on */
+/* .mode list */
+/* .separator "\t" */
with card as (
select round,player,card1 as card from deck
@@ -20,16 +20,29 @@ select
group by 1),
top_scores as (
-select card, round, player, max(score) from ranked_cards group by 1)
+select card, round, player, max(score) as score from ranked_cards group by 1),
-select deck.round,deck.player,
+summary as (select deck.round,deck.player,
card1,
card2,
card3,
+ts1.score is not null as card1top,
+ts2.score is not null as card2top,
+ts3.score is not null as card3top,
min(match.group_name) as prelim_group,
sum(case when match.group_name != 'final' then match.score else 0 end) as prelim_score,
sum(case when match.group_name = 'final' then match.score else null end) as final_score
from deck join match
on deck.round = match.round and deck.player = match.player
-group by 1,2;
+left join top_scores ts1
+on deck.player = ts1.player
+and deck.card1 = ts1.card
+left join top_scores ts2
+on deck.player = ts2.player
+and deck.card2 = ts2.card
+left join top_scores ts3
+on deck.player = ts3.player
+and deck.card3 = ts3.card
+group by 1,2 order by 1 desc, prelim_group,2)
+select * from summary;
