From c763002b75c9fc14a5f098a02b0c7edb9e29c7b1 Mon Sep 17 00:00:00 2001
From: emkael <emkael@tlen.pl>
Date: Sun, 11 Jul 2021 15:30:54 +0200
Subject: Lineup check scripts

---
 dumps/check-all-lineups.sh |  6 ++++++
 dumps/check-lineups.sh     | 47 ++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 53 insertions(+)
 create mode 100755 dumps/check-all-lineups.sh
 create mode 100755 dumps/check-lineups.sh

(limited to 'dumps')

diff --git a/dumps/check-all-lineups.sh b/dumps/check-all-lineups.sh
new file mode 100755
index 0000000..9bab79e
--- /dev/null
+++ b/dumps/check-all-lineups.sh
@@ -0,0 +1,6 @@
+#!/bin/bash
+cd $(dirname $0)
+cat .paid-queries | cut -d' ' -f1 | while read DB
+do
+    ./check-lineups.sh $DB
+done
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
-- 
cgit v1.2.3