Player-team checks with magic Cezar CSV

15 files changed, 203 insertions, 5 deletions

diff --git a/.envrc b/.envrc
index 25a7415..cce073c 100644
--- a/.envrc
+++ b/.envrc
diff --git a/Makefile b/Makefile
index e1f0e3e..8f1615f 100644
--- a/Makefile
+++ b/Makefile
@@ -9,9 +9,14 @@ autocommit: pull
 players: FORCE
 	./dumps/import-players.sh
 
+teams: players FORCE
+	./dumps/check-all-teams.sh
+
 paid: players FORCE
 	./dumps/check-all-paid.sh
 
+player-checks: paid teams
+
 dumps: FORCE
 	s3cmd -c dumps/.s3config sync ${LIGA_S3_BUCKET} dumps/sync/
 	./dumps/load-dumps.sh
diff --git a/dumps/.cezar-teams-dmp202021_1n b/dumps/.cezar-teams-dmp202021_1n
new file mode 100644
index 0000000..3b5d002
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_1n
@@ -0,0 +1,16 @@
+1 10761
+2 10868
+3 10845
+4 10837
+5 10770
+6 10838
+7 10823
+8 10795
+9 10841
+10 10727
+11 10801
+12 10817
+13 10734
+14 10744
+15 10865
+16 10852
diff --git a/dumps/.cezar-teams-dmp202021_1n_b b/dumps/.cezar-teams-dmp202021_1n_b
new file mode 100644
index 0000000..3b5d002
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_1n_b
@@ -0,0 +1,16 @@
+1 10761
+2 10868
+3 10845
+4 10837
+5 10770
+6 10838
+7 10823
+8 10795
+9 10841
+10 10727
+11 10801
+12 10817
+13 10734
+14 10744
+15 10865
+16 10852
diff --git a/dumps/.cezar-teams-dmp202021_1n_z1 b/dumps/.cezar-teams-dmp202021_1n_z1
new file mode 100644
index 0000000..3b5d002
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_1n_z1
@@ -0,0 +1,16 @@
+1 10761
+2 10868
+3 10845
+4 10837
+5 10770
+6 10838
+7 10823
+8 10795
+9 10841
+10 10727
+11 10801
+12 10817
+13 10734
+14 10744
+15 10865
+16 10852
diff --git a/dumps/.cezar-teams-dmp202021_1n_z3 b/dumps/.cezar-teams-dmp202021_1n_z3
new file mode 100644
index 0000000..3b5d002
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_1n_z3
@@ -0,0 +1,16 @@
+1 10761
+2 10868
+3 10845
+4 10837
+5 10770
+6 10838
+7 10823
+8 10795
+9 10841
+10 10727
+11 10801
+12 10817
+13 10734
+14 10744
+15 10865
+16 10852
diff --git a/dumps/.cezar-teams-dmp202021_1s b/dumps/.cezar-teams-dmp202021_1s
new file mode 100644
index 0000000..61f2726
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_1s
@@ -0,0 +1,16 @@
+1 10760
+2 10743
+3 10878
+4 10840
+5 10890
+6 10829
+7 10780
+8 10889
+9 10773
+10 10803
+11 10797
+12 10813
+13 10811
+14 10802
+15 10887
+16 10824
diff --git a/dumps/.cezar-teams-dmp202021_2ne b/dumps/.cezar-teams-dmp202021_2ne
new file mode 100644
index 0000000..124c369
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_2ne
@@ -0,0 +1,16 @@
+1 10924
+2 10722
+3 10900
+4 10923
+5 10884
+6 10765
+7 10877
+8 10757
+9 10881
+10 10762
+11 10796
+12 10888
+13 10984
+14 10724
+15 10806
+16 10825
diff --git a/dumps/.cezar-teams-dmp202021_2nw b/dumps/.cezar-teams-dmp202021_2nw
new file mode 100644
index 0000000..bcefd40
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_2nw
@@ -0,0 +1,16 @@
+1 10821
+2 10897
+3 10753
+4 10767
+5 10864
+6 10751
+7 10763
+8 10737
+9 10926
+10 10922
+11 10896
+12 10895
+13 10799
+14 10800
+15 10735
+16 10828
diff --git a/dumps/.cezar-teams-dmp202021_2se b/dumps/.cezar-teams-dmp202021_2se
new file mode 100644
index 0000000..8cada06
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_2se
@@ -0,0 +1,14 @@
+1 10774
+2 10787
+3 10911
+4 10925
+5 10928
+6 10812
+7 10879
+8 10764
+9 10860
+10 10914
+11 10891
+12 10968
+13 10912
+14 10933
diff --git a/dumps/.cezar-teams-dmp202021_2sw b/dumps/.cezar-teams-dmp202021_2sw
new file mode 100644
index 0000000..3fcdd81
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_2sw
@@ -0,0 +1,16 @@
+1 10844
+2 10788
+3 10782
+4 10846
+5 10871
+6 10741
+7 10873
+8 10831
+9 10742
+10 10739
+11 10898
+12 10872
+13 10848
+14 0
+15 10904
+16 10759
diff --git a/dumps/.cezar-teams-dmp202021_e b/dumps/.cezar-teams-dmp202021_e
new file mode 100644
index 0000000..6a76a05
--- /dev/null
+++ b/dumps/.cezar-teams-dmp202021_e
@@ -0,0 +1,16 @@
+1 10793
+2 10804
+3 10836
+4 10784
+5 10798
+6 10749
+7 10875
+8 10882
+9 10777
+10 10772
+11 10835
+12 10771
+13 10805
+14 10779
+15 10880
+16 10740
diff --git a/dumps/check-all-teams.sh b/dumps/check-all-teams.sh
new file mode 100755
index 0000000..24702ab
--- /dev/null
+++ b/dumps/check-all-teams.sh
@@ -0,0 +1,6 @@
+#!/bin/bash
+cd $(dirname $0)
+cat .paid-queries | cut -d' ' -f1 | while read DB
+do
+    ./check-teams.sh $DB
+done
diff --git a/dumps/check-teams.sh b/dumps/check-teams.sh
new file mode 100755
index 0000000..ecf78a5
--- /dev/null
+++ b/dumps/check-teams.sh
@@ -0,0 +1,28 @@
+#!/bin/bash
+DBNAME=$1
+PLAYERSDB=${LIGA_PLAYERS_DB_NAME}
+echo $DBNAME
+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 (
+SELECT rnd, segment, openN as pl FROM $DBNAME.segments
+UNION
+SELECT rnd, segment, openS 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, closeN as pl FROM $DBNAME.segments
+UNION
+SELECT rnd, segment, closeS 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
+) lineup ON lineup.pl = dmppl.id
+WHERE t.internet <> pl.info
+ORDER BY dmppl.link, lineup.rnd, lineup.segment;"
+echo $QUERY | mysql ${LIGA_MYSQL_CONNECTION_OPTS} -N
diff --git a/dumps/import-players.sh b/dumps/import-players.sh
index 5f499ef..9763ab5 100755
--- a/dumps/import-players.sh
+++ b/dumps/import-players.sh
@@ -1,13 +1,14 @@
 #!/bin/bash
 cd $(dirname $0)
 DBNAME=${LIGA_PLAYERS_DB_NAME}
-curl 'https://msc.com.pl/cezar/download/baza.csv' | grep '^[0-9]' > baza.csv
-mysql ${LIGA_MYSQL_CONNECTION_OPTS} --local-infile=1 $DBNAME -e "LOAD DATA LOCAL INFILE 'baza.csv'
+curl ${LIGA_BAZA_CSV} | iconv -f cp1250 -t utf8 | grep '^[0-9]' | sed 's/;[0-9]\{4\}-[0-9]\{2\}-[0-9]\{2\}/;T/g' | cut -d';' -f1-7,10-11 > baza.csv
+mysql ${LIGA_MYSQL_CONNECTION_OPTS} --local-infile=1 $DBNAME -e "TRUNCATE players;
+LOAD DATA LOCAL INFILE 'baza.csv'
 REPLACE
 INTO TABLE players
 FIELDS TERMINATED BY ';' ENCLOSED BY '\"'
-(ID, gname, sname, rank, loc1, info, loc2, flag1, flag2);
-UPDATE players SET age = REPLACE(info, 'K', ''), sex = IF(POSITION('K' IN info), 'K', '');
-UPDATE players SET info = NULL;
+(ID, gname, sname, rank, loc1, flag1, loc2, flag2, info);
+UPDATE players SET age = REPLACE(flag1, 'K', ''), sex = IF(POSITION('K' IN flag1), 'K', '');
+UPDATE players SET flag1 = NULL;
 "
 rm baza.csv