";
var p2 = document.createElement("p");
p2.className = "c t";
var b2 = document.createElement("button");
@@ -646,8 +647,8 @@
nmsel = document.querySelector("select[name=name]");
txsel = document.getElementById("text");
loginhead = document.getElementById("loginhead");
- for (let b of ["ranking", "today", "2h", "2w", "byteam", "byteam2h"]) {
- if (btn = document.getElementById(b))
+ for (let btn of document.querySelectorAll("button")) {
+ if (btn.id.match(/^ranking/))
btn.addEventListener("click", ranking);
}
let entry = document.createElement("input");
diff --git a/jtserv/adduser.sh b/jtserv/adduser.sh
index e895938..c1ef59a 100755
--- a/jtserv/adduser.sh
+++ b/jtserv/adduser.sh
@@ -1,14 +1,21 @@
#!/bin/sh
+## Feeding CSV as follows:
+## user,gecos,email,team
+# j2100,"HIROSE Yuuji",yuuji@es.gentei.org,Teacher
+
+## This script can be run repeatedly in order to change team allocation.
+
DB=${1:-users.sq3}
-CSV=${2:-users.csv}
+CSV=${2:-trrusers.csv}
sqlite3 ${DB} <<-EOF
.mode csv
- PRAGMA foreign_keys=on;
CREATE TEMPORARY TABLE newuser(user, gecos, email, team);
+ .read inittbl.sql
.import $CSV newuser
+ DELETE FROM newuser WHERE user LIKE 'user';
INSERT INTO teams SELECT distinct team FROM newuser
WHERE team NOT IN (SELECT team FROM teams);
- INSERT INTO users SELECT user, gecos, email, team
- FROM newuser WHERE user NOT IN (SELECT user FROM users);
+ PRAGMA foreign_keys=off; -- To avoid score deletion
+ REPLACE INTO users SELECT user, gecos, email, team FROM newuser;
EOF
diff --git a/jtserv/chgrp.sh b/jtserv/chgrp.sh
new file mode 100755
index 0000000..9164b6d
--- /dev/null
+++ b/jtserv/chgrp.sh
@@ -0,0 +1,22 @@
+#!/bin/sh
+
+## Feeding CSV as follows:
+## user,team
+# j2100,Teacher
+
+## This script can be run repeatedly in order to change team allocation.
+
+DB=${1:-users.sq3}
+CSV=${2:-newgrp.csv}
+sqlite3 ${DB} <<-EOF
+ .mode csv
+ CREATE TEMPORARY TABLE newgrp(user, team);
+ .read inittbl.sql
+ .import $CSV newgrp
+ DELETE FROM newgrp WHERE user LIKE 'user';
+ INSERT INTO teams SELECT distinct team FROM newgrp
+ WHERE team NOT IN (SELECT team FROM teams);
+ PRAGMA foreign_keys=off; -- To avoid score deletion
+ UPDATE users SET team=(SELECT team FROM newgrp WHERE user=users.user)
+ WHERE user IN (SELECT user FROM newgrp);
+EOF
diff --git a/jtserv/inittbl.sh b/jtserv/inittbl.sh
index 8f72bed..c5efbcd 100755
--- a/jtserv/inittbl.sh
+++ b/jtserv/inittbl.sh
@@ -5,32 +5,18 @@
sqlite3 ${DB} <<-EOF
.mode csv
PRAGMA foreign_keys=on;
- CREATE TABLE IF NOT EXISTS teams(team primary key);
+ .read inittbl.sql
CREATE TEMPORARY TABLE guest(team);
INSERT INTO guest VALUES('guest');
INSERT INTO teams SELECT team FROM GUEST
WHERE team NOT IN (SELECT team FROM teams);
- CREATE TABLE IF NOT EXISTS users(
- user primary key, gecos, email, team,
- FOREIGN KEY(team) REFERENCES teams(team)
- ON DELETE CASCADE ON UPDATE CASCADE);
- CREATE TEMPORARY TABLE teamcsv(teamno,teamname,uid,name);
+ CREATE TEMPORARY TABLE teamcsv(teamno,teamname,user,name);
.import $CSV teamcsv
- DELETE FROM teamcsv WHERE teamno NOT GLOB '[0-9]*';
+ DELETE FROM teamcsv WHERE name LIKE 'name' OR name LIKE 'gecos';
INSERT INTO teams SELECT distinct teamname FROM teamcsv
WHERE teamname NOT IN (SELECT team FROM teams);
- INSERT INTO users SELECT uid, name, uid||'@itl.koeki-u.ac.jp', teamname
- FROM teamcsv WHERE uid NOT IN (SELECT user FROM users);
+ INSERT INTO users SELECT user, name, user||'@itl.koeki-u.ac.jp', teamname
+ FROM teamcsv WHERE user NOT IN (SELECT user FROM users);
- CREATE TABLE IF NOT EXISTS skey(
- user, skey, expire,
- UNIQUE(user, skey),
- FOREIGN KEY(user) REFERENCES users(user)
- ON DELETE CASCADE ON UPDATE CASCADE);
- CREATE TABLE IF NOT EXISTS tmpkey(
- user, tmpkey, token, expire,
- UNIQUE(user, tmpkey),
- FOREIGN KEY(user) REFERENCES users(user)
- ON DELETE CASCADE ON UPDATE CASCADE);
EOF
diff --git a/jtserv/inittbl.sql b/jtserv/inittbl.sql
new file mode 100644
index 0000000..6e8b8a2
--- /dev/null
+++ b/jtserv/inittbl.sql
@@ -0,0 +1,18 @@
+CREATE TABLE IF NOT EXISTS teams(team primary key);
+
+CREATE TABLE IF NOT EXISTS users(
+ user primary key, gecos, email, team,
+ FOREIGN KEY(team) REFERENCES teams(team)
+ ON DELETE CASCADE ON UPDATE CASCADE);
+
+CREATE TABLE IF NOT EXISTS skey(
+ user, skey, expire,
+ UNIQUE(user, skey),
+ FOREIGN KEY(user) REFERENCES users(user)
+ ON DELETE CASCADE ON UPDATE CASCADE);
+
+CREATE TABLE IF NOT EXISTS tmpkey(
+ user, tmpkey, token, expire,
+ UNIQUE(user, tmpkey),
+ FOREIGN KEY(user) REFERENCES users(user)
+ ON DELETE CASCADE ON UPDATE CASCADE);
diff --git a/jtserv/jtserv.rb b/jtserv/jtserv.rb
index a7c1f84..b5b1f42 100755
--- a/jtserv/jtserv.rb
+++ b/jtserv/jtserv.rb
@@ -5,7 +5,7 @@
require 'csv'
require 'sqlite3'
-PORT = 8814
+PORT = (ENV["JTSV_PORT"]||8814).to_i
WARNMAX = 3
# Interchange of Information is done in JSON form.
@@ -328,7 +328,7 @@
end
return rv
end
- def ranking(text, mode=nil)
+ def ranking(text, mode=nil, user=nil)
rankbase = <<~EOF
SELECT "User", "Team", "Score", "Step", "Try", "Minutes", "Time"
UNION
@@ -366,6 +366,14 @@
else
@db.execute(sprintf(teambase, ""), text)
end
+ when /^inteam/
+ return unless user
+ cond2 = " AND team=(SELECT team FROM users WHERE user=?)"
+ if limit
+ @db.execute(sprintf(rankbase, cond2 + " AND at > ?"), text, user, limit)
+ else
+ @db.execute(sprintf(rankbase, cond2), text, user)
+ end
else
@db.execute(sprintf(rankbase, ""), text)
end
@@ -499,7 +507,7 @@
elsif json["ranking"]
text = json["ranking"]
mode = json["mode"]
- p rank = db.ranking(text, mode)
+ p rank = db.ranking(text, mode, user)
ws_conn.send(JSON.generate({"ranking": rank, "mode": mode}))
elsif json["gettext"]
db.updateSkey(user, skey)