commit aa1bd88895719d5e06d0462c6185bc75ec34fa0f
parent 3ac5a213ea481d1291168520780e2f8becd54b8e
Author: alex wennerberg <alex@alexwennerberg.com>
Date: Sun, 17 Aug 2025 16:16:59 -0400
Add round dates, player stats page, and banned card indicators
- Add date tracking to rounds with Google Drive API integration
- Create players page showing player statistics and activity
- Display banned card overlays on card images
- Improve UI with card search form and enhanced round listings
- Update deployment script for remote server sync
- Fix delete-last.sql to properly clean up latest round
Diffstat:
8 files changed, 99 insertions(+), 25 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -45,6 +45,23 @@ def file_to_wb(fileid):
os.exit(1)
return openpyxl.load_workbook(io.BytesIO(res.content), data_only=True)
+def get_file_created_date(fileid):
+ headers = {"x-goog-api-key": os.environ.get("GOOG_KEY")}
+ params = {"fields": "createdTime"}
+ res = requests.get(f"https://www.googleapis.com/drive/v3/files/{fileid}",
+ params=params,
+ headers=headers)
+ if res.status_code != 200:
+ print(res.text)
+ return None
+ data = res.json()
+ if "createdTime" in data:
+ # Parse ISO format and return YYYY-MM-DD
+ from datetime import datetime
+ created_time = datetime.fromisoformat(data["createdTime"].replace('Z', '+00:00'))
+ return created_time.strftime('%Y-%m-%d')
+ return None
+
def update_bans():
curr_round = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results")
sheet = file_to_wb("1NZuROOCctbq4p4-CAHE-jOC0675QQjuqVnUqdXFCVD8")["banlist"]
@@ -61,7 +78,8 @@ def update_bans():
schema = ["""
create table if not exists round (
id integer primary key,
- fileid text not null
+ fileid text not null,
+ date text
);
""",
"""
@@ -131,12 +149,16 @@ def save_round(n, fileid):
groups = wb["Groups"]
except KeyError:
groups = wb["Group"]
+
+ # Get creation date from Google Drive API
+ date_str = get_file_created_date(fileid)
+
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));
+ cur.execute("insert into round values (?, ?, ?);", (n, fileid, date_str));
con.commit()
# fixing some data issues
def best_guess(card):
diff --git a/main.rb b/main.rb
@@ -7,17 +7,8 @@ db = SQLite3::Database.new "3cb.db"
db.results_as_hash = true
set :server_settings, :timeout => 5
-configure :production do
- require 'prometheus/middleware/collector'
- require 'prometheus/middleware/exporter'
- use Rack::Deflater
- use Prometheus::Middleware::Collector
- use Prometheus::Middleware::Exporter
-end
-
-
get '/' do
- @rounds = db.execute "select * from round order by id desc"
+ @rounds = db.execute "select r.id, r.fileid, r.date, count(distinct d.player) as player_count from round r left join deck d on r.id = d.round group by r.id, r.fileid, r.date order by r.id desc"
@subtitle = "rounds"
erb :index
end
@@ -25,6 +16,7 @@ end
get '/round/:id' do |id|
@round = db.execute("select * from round where id = ?", id)[0]
@matches = db.execute "select * from round_score where round = ? order by final_score desc,prelim_score desc", id
+ @banned_cards = db.execute("select name from ban").map { |row| row["name"] }
@subtitle = "round " + id
erb :matches
end
@@ -35,6 +27,7 @@ get '/card' do
card1 = ?1 or
card2 = ?1 or
card3 = ?1 order by round desc", name
+ @banned_cards = db.execute("select name from ban").map { |row| row["name"] }
@subtitle = name
erb :matches
end
@@ -42,6 +35,7 @@ end
get '/player' do
name = params[:name]
@matches = db.execute "select * from round_score where player = ? order by round desc", name
+ @banned_cards = db.execute("select name from ban").map { |row| row["name"] }
@subtitle = "player " + name
erb :matches # rename round -> matches
end
@@ -58,6 +52,24 @@ get '/deck' do
card1 = ?3 and card2 = ?1 and card3 = ?2 or
card1 = ?3 and card2 = ?2 and card3 = ?1
order by round desc", [cards[0], cards[1], cards[2]])
+ @banned_cards = db.execute("select name from ban").map { |row| row["name"] }
@subtitle = cards.join("; ")
erb :matches
end
+
+get '/players' do
+ @players = db.execute "
+ select
+ d.player,
+ count(distinct d.round) as rounds_played,
+ sum(case when r.rank = 1 then 1 else 0 end) as rounds_won,
+ min(ro.date) as first_played
+ from deck d
+ left join rank r on d.round = r.round and d.player = r.player
+ left join round ro on d.round = ro.id
+ group by d.player
+ order by rounds_played desc
+ "
+ @subtitle = "players"
+ erb :players
+end
diff --git a/runround.sh b/runround.sh
@@ -1,7 +1,8 @@
sqlite3 3cb.db < sql/delete-last.sql
+source .venv/bin/activate
python getdata.py
sqlite3 3cb.db < sql/new.sql
-sqlite3 3cb.db < sql/export.sql > _site/export.tsv
-python3 buildpage.py > _site/index.html
-rsync -r _site/ alpine@fishbb.org:/var/www/data.3cardmagic.org
-
+scp 3cb.db alpine@fishbb.org:~/
+scp main.rb alpine@fishbb.org:~
+scp -r views/ alpine@fishbb.org:~
+ssh alpine@fishbb.org "doas rc-service 3cm restart"
diff --git a/schema.sql b/schema.sql
@@ -1,6 +1,7 @@
CREATE TABLE round (
id integer primary key,
- fileid text not null
+ fileid text not null,
+ date text
);
CREATE TABLE deck (
round integer not null,
diff --git a/sql/delete-last.sql b/sql/delete-last.sql
@@ -1,3 +1,3 @@
-delete from round where id > 72;
-delete from deck where round > 72;
-delete from match where round > 72;
+delete from deck where round = (select max(id) from round);
+delete from match where round = (select max(id) from round);
+delete from round where id = (select max(id) from round);
diff --git a/views/index.erb b/views/index.erb
@@ -1,9 +1,20 @@
<%= erb :header %>
-<div class="flextable">
+<p><a href="/players">View all players</a></p>
+<form action="/card" method="get" class="card-search">
+ <input type="text" name="name" placeholder="Search for a card..." required>
+ <input type="submit" value="Search">
+</form>
+<div class="rounds-list">
<% @rounds.each do |round| %>
- <div class="flextable-item">
- <a href="/round/<%= round["id"] %>"><%= round["id"].to_s.rjust(2, "0") %></a>
+ <div class="round-item">
+ <a href="/round/<%= round["id"] %>">Round <%= round["id"].to_s.rjust(2, "0") %></a>
+ <span class="round-meta">
+ <% if round["date"] %>
+ <%= round["date"] %>
+ <% end %>
+[<%= round["player_count"] %> players]
+ </span>
</div>
- <% end %>
+<% end %>
</div>
<%= erb :footer %>
diff --git a/views/matches.erb b/views/matches.erb
@@ -1,6 +1,9 @@
<%= erb :header %>
<% if @round %>
<a href='https://docs.google.com/spreadsheets/d/<%= @round["fileid"] %>/'>Google Sheet</a><% end %>
+<% if @matches.empty? %>
+ <p>This card has never been played, or may be spelled wrong!</p>
+<% else %>
<div class="flextable">
<% @matches.each do |match| %>
<div class="deck-box"
@@ -16,9 +19,10 @@
<a href="/deck?c1=<%= match["card1"] %>&c2=<%= match["card2"] %>&c3=<%= match["card3"]%>">deck</a><br>
</div>
<div>
- <% [match["card1"], match["card2"], match["card3"]].each do |card| %><a href="/card?name=<%= card %>"><img alt='<%= card %>' width=146 height=204px loading=lazy src="https://api.scryfall.com/cards/named?exact=<%= url_encode(card) %>&format=image&version=medium" /></a><% end %>
+ <% [match["card1"], match["card2"], match["card3"]].each do |card| %><div style="position: relative; display: inline-block;"><a href="/card?name=<%= card %>"><img alt='<%= card %>' width=146 height=204px loading=lazy src="https://api.scryfall.com/cards/named?exact=<%= url_encode(card) %>&format=image&version=medium" /></a><% if @banned_cards.include?(card) %><div style="position: absolute; top: 50%; left: 50%; transform: translate(-50%, -50%); background: rgba(255, 0, 0, 0.7); color: white; font-weight: bold; font-size: 20px; padding: 5px 10px; border-radius: 5px;">BANNED</div><% end %></div><% end %>
</div>
</div>
<% end %>
</div>
+<% end %>
<%= erb :footer %>
diff --git a/views/players.erb b/views/players.erb
@@ -0,0 +1,22 @@
+<%= erb :header %>
+<table class="players-table">
+ <thead>
+ <tr>
+ <th>Player</th>
+ <th>Rounds</th>
+ <th>Wins</th>
+ <th>First Played</th>
+ </tr>
+ </thead>
+ <tbody>
+ <% @players.each do |player| %>
+ <tr>
+ <td><a href="/player?name=<%= url_encode(player["player"]) %>"><%= player["player"] %></a></td>
+ <td><%= player["rounds_played"] %></td>
+ <td><%= player["rounds_won"] %></td>
+ <td><%= player["first_played"] %></td>
+ </tr>
+ <% end %>
+ </tbody>
+</table>
+<%= erb :footer %>
+\ No newline at end of file
