Switching paid checks to both calendar years + players checks also involve players missing from DB (=not paid on either years)

2 files changed, 4 insertions, 4 deletions

diff --git a/dumps/check-paid.sh b/dumps/check-paid.sh
index 3b864c8..3329953 100755
--- a/dumps/check-paid.sh
+++ b/dumps/check-paid.sh
@@ -4,5 +4,5 @@ PLAYERSDB=${LIGA_PLAYERS_DB_NAME}
 ROUNDFROM=$2
 ROUNDTO=$3
 echo $DBNAME
-QUERY="SELECT dmpplayers.link, CONCAT(dmpplayers.gname, ' ', dmpplayers.sname), teams.fullname, CONCAT(segments.rnd, '-', segments.segment) FROM $DBNAME.butler butler JOIN $DBNAME.players dmpplayers ON dmpplayers.id = butler.id JOIN $PLAYERSDB.players players ON players.id = dmpplayers.link JOIN $DBNAME.teams teams ON teams.id = dmpplayers.team JOIN (SELECT rnd, segment, openN as pl FROM $DBNAME.segments UNION SELECT rnd, segment, openE as pl FROM $DBNAME.segments UNION SELECT rnd, segment, openW as pl FROM $DBNAME.segments UNION SELECT rnd, segment, openS as pl FROM $DBNAME.segments UNION SELECT rnd, segment, closeN as pl FROM $DBNAME.segments UNION SELECT rnd, segment, closeE as pl FROM $DBNAME.segments UNION SELECT rnd, segment, closeW as pl FROM $DBNAME.segments UNION SELECT rnd, segment, closeS as pl FROM $DBNAME.segments) segments ON segments.pl = dmpplayers.id WHERE players.flag2 <> 'T' AND segments.rnd >= $ROUNDFROM AND segments.rnd <= $ROUNDTO ORDER BY dmpplayers.link, segments.rnd, segments.segment;"
+QUERY="SELECT dmpplayers.link, CONCAT(dmpplayers.gname, ' ', dmpplayers.sname), teams.fullname, CONCAT(segments.rnd, '-', segments.segment), players.flag1, players.flag2 FROM $DBNAME.butler butler JOIN $DBNAME.players dmpplayers ON dmpplayers.id = butler.id LEFT JOIN $PLAYERSDB.players players ON players.id = dmpplayers.link JOIN $DBNAME.teams teams ON teams.id = dmpplayers.team JOIN (SELECT rnd, segment, openN as pl FROM $DBNAME.segments UNION SELECT rnd, segment, openE as pl FROM $DBNAME.segments UNION SELECT rnd, segment, openW as pl FROM $DBNAME.segments UNION SELECT rnd, segment, openS as pl FROM $DBNAME.segments UNION SELECT rnd, segment, closeN as pl FROM $DBNAME.segments UNION SELECT rnd, segment, closeE as pl FROM $DBNAME.segments UNION SELECT rnd, segment, closeW as pl FROM $DBNAME.segments UNION SELECT rnd, segment, closeS as pl FROM $DBNAME.segments) segments ON segments.pl = dmpplayers.id WHERE COALESCE(players.flag1, 'N') <> 'T' AND COALESCE(players.flag2, 'N') <> 'T' AND segments.rnd >= $ROUNDFROM AND segments.rnd <= $ROUNDTO ORDER BY dmpplayers.link, segments.rnd, segments.segment;"
 echo $QUERY | mysql ${LIGA_MYSQL_CONNECTION_OPTS} -N
diff --git a/dumps/check-teams.sh b/dumps/check-teams.sh
index ecf78a5..8cd3e31 100755
--- a/dumps/check-teams.sh
+++ b/dumps/check-teams.sh
@@ -6,7 +6,7 @@ cat .cezar-teams-$DBNAME | while read TEAM CEZARTEAM
 do
     echo "UPDATE teams SET internet = $CEZARTEAM WHERE id = $TEAM;"
 done | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME
-QUERY="SELECT dmppl.link, CONCAT(dmppl.gname, ' ', dmppl.sname), TRIM(t.shortname), TRIM(pl.loc2), CONCAT(lineup.rnd, '-', lineup.segment) FROM $DBNAME.players dmppl JOIN $PLAYERSDB.players pl ON dmppl.link = pl.ID JOIN $DBNAME.teams t ON t.id = dmppl.team JOIN (
+QUERY="SELECT dmppl.link, CONCAT(dmppl.gname, ' ', dmppl.sname), TRIM(t.shortname), TRIM(pl.loc2), CONCAT(lineup.rnd, '-', lineup.segment) FROM $DBNAME.players dmppl JOIN $DBNAME.teams t ON t.id = dmppl.team JOIN (
 SELECT rnd, segment, openN as pl FROM $DBNAME.segments
 UNION
 SELECT rnd, segment, openS as pl FROM $DBNAME.segments
@@ -22,7 +22,7 @@ UNION
 SELECT rnd, segment, closeE as pl FROM $DBNAME.segments
 UNION
 SELECT rnd, segment, closeW as pl FROM $DBNAME.segments
-) lineup ON lineup.pl = dmppl.id
-WHERE t.internet <> pl.info
+) lineup ON lineup.pl = dmppl.id LEFT JOIN $PLAYERSDB.players pl ON dmppl.link = pl.ID
+WHERE COALESCE(t.internet, '') <> COALESCE(pl.info, '')
 ORDER BY dmppl.link, lineup.rnd, lineup.segment;"
 echo $QUERY | mysql ${LIGA_MYSQL_CONNECTION_OPTS} -N