diff options
| -rw-r--r-- | Makefile | 5 | ||||
| -rwxr-xr-x | dumps/check-all-lineups.sh | 6 | ||||
| -rwxr-xr-x | dumps/check-lineups.sh | 47 |
3 files changed, 57 insertions, 1 deletions
@@ -16,7 +16,10 @@ teams: players FORCE paid: players FORCE ./dumps/check-all-paid.sh -player-checks: paid teams +lineups: FORCE + ./dumps/check-all-lineups.sh + +player-checks: paid teams lineups dumps: FORCE s3cmd -c dumps/.s3config sync ${LIGA_S3_BUCKET} dumps/sync/ 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 |