Lineup check scripts

1 files changed, 47 insertions, 0 deletions

diff --git a/dumps/check-lineups.sh b/dumps/check-lineups.sh
new file mode 100755
index 0000000..a32da38
--- /dev/null
+++ b/dumps/check-lineups.sh
@@ -0,0 +1,47 @@
+#!/bin/bash
+DBNAME=$1
+echo $DBNAME
+echo "SELECT 'in lineup, not in players', player, CONCAT(rnd, '-', segment), tabl, position FROM (
+    SELECT openN player, rnd, segment, CONCAT(tabl, 'o') tabl, 'N' position FROM segments
+    UNION
+    SELECT openS player, rnd, segment, CONCAT(tabl, 'o') tabl, 'S' position FROM segments
+    UNION
+    SELECT openE player, rnd, segment, CONCAT(tabl, 'o') tabl, 'E' position FROM segments
+    UNION
+    SELECT openW player, rnd, segment, CONCAT(tabl, 'o') tabl, 'W' position FROM segments
+    UNION
+    SELECT closeN player, rnd, segment, CONCAT(tabl, 'z') tabl, 'N' position FROM segments
+    UNION
+    SELECT closeS player, rnd, segment, CONCAT(tabl, 'z') tabl, 'S' position FROM segments
+    UNION
+    SELECT closeE player, rnd, segment, CONCAT(tabl, 'z') tabl, 'E' position FROM segments
+    UNION
+    SELECT closeW player, rnd, segment, CONCAT(tabl, 'z') tabl, 'W' position FROM segments
+) lineup_players WHERE player NOT IN (SELECT id FROM players) AND player > 0;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N
+ROUNDS=$(echo "SELECT roundcnt FROM admin;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N)
+SEGMENTS=$(echo "SELECT segmentsperround FROM admin;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N)
+for R in $( eval echo {1..$ROUNDS} )
+do
+    for SEGMENT in $( eval echo {1..$SEGMENTS} )
+    do
+        ROUND=$(printf "%02d" $R)
+        echo "SELECT 'in butler, not in players', id, CONCAT($R, '-', $SEGMENT) FROM butler WHERE id NOT IN (SELECT id FROM players) AND seg${ROUND}_${SEGMENT} IS NOT NULL;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N
+        echo "SELECT 'in butler, not in lineup', id, CONCAT($R, '-', $SEGMENT) FROM butler WHERE id NOT IN (
+SELECT openN FROM segments WHERE rnd = $R AND segment = $SEGMENT
+UNION
+SELECT openS FROM segments WHERE rnd = $R AND segment = $SEGMENT
+UNION
+SELECT openE FROM segments WHERE rnd = $R AND segment = $SEGMENT
+UNION
+SELECT openW FROM segments WHERE rnd = $R AND segment = $SEGMENT
+UNION
+SELECT closeN FROM segments WHERE rnd = $R AND segment = $SEGMENT
+UNION
+SELECT closeS FROM segments WHERE rnd = $R AND segment = $SEGMENT
+UNION
+SELECT closeE FROM segments WHERE rnd = $R AND segment = $SEGMENT
+UNION
+SELECT closeW FROM segments WHERE rnd = $R AND segment = $SEGMENT
+) AND seg${ROUND}_${SEGMENT} IS NOT NULL;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N
+    done
+done