· 9 years ago · Mar 04, 2017, 07:02 PM
1#!/usr/bin/perl
2
3# +---------------------------------------------------------------------------+
4# ! MODULES !
5# +---------------------------------------------------------------------------+
6
7use strict;
8use warnings;
9use diagnostics;
10
11use File::Basename;
12use DBI;
13
14# +---------------------------------------------------------------------------+
15# ! CONSTANTS !
16# +---------------------------------------------------------------------------+
17
18my $MAIN_PROG_DDBNAME = "duckhunt";
19my $MAIN_PROG_DBHOST = "localhost";
20my $MAIN_PROG_DBPORT = 3306;
21my $MAIN_PROG_DBUSER = "mediabot";
22my $MAIN_PROG_DBPASS = "*******";
23
24my $duck_hunt_players_table_url = "http://statik.chickenkiller.com/players_table.txt";
25
26# +---------------------------------------------------------------------------+
27# ! SUBS !
28# +---------------------------------------------------------------------------+
29
30sub clean_and_exit(@);
31sub dbConnect(@);
32sub setMysqlUTF8();
33sub truncateStatsTable();
34sub getIdChannel(@);
35sub createChannel(@);
36sub getIdUser(@);
37sub createUser(@);
38
39# +---------------------------------------------------------------------------+
40# ! MAIN !
41# +---------------------------------------------------------------------------+
42
43# Establish a MySQL connection
44my $dbh = dbConnect($MAIN_PROG_DDBNAME,$MAIN_PROG_DBHOST,$MAIN_PROG_DBPORT,$MAIN_PROG_DBUSER,$MAIN_PROG_DBPASS);
45unless (defined($dbh)) {
46 print "Could not connect to database\n";
47 clean_and_exit(1);
48}
49
50unless (truncateStatsTable()) {
51 print "Could not truncate table USER_CHANNEL_STATS\n";
52 clean_and_exit(2);
53}
54
55unless (setMysqlUTF8()) {
56 print "Could not execute UTF-8 queries\n";
57 clean_and_exit(3);
58}
59
60
61
62# Get stats with curl
63unless ( open CURL, "curl -f -s $duck_hunt_players_table_url |") {
64 print STDERR "Could not open curl $duck_hunt_players_table_url\n";
65 clean_and_exit(4);
66}
67
68my ($version,$strDate,$strTime);
69my $line;
70
71# Skip first line
72if (defined($line=<CURL>)) {}
73
74# Grab version and generated time
75if (defined($line=<CURL>)) {
76 (undef,undef,undef,$version,undef,undef,undef,undef,undef,undef,undef,undef,undef,$strDate,undef,$strTime) = split(/\s+/,$line);
77 print "$version $strDate $strTime\n";
78}
79
80while (defined($line=<CURL>)) {
81 # Skip lines until channel
82 while (defined($line=<CURL>) && !($line =~/^#/)) {}
83 unless (defined($line)) { clean_and_exit(0); }
84 chomp($line);
85
86 # Check if channel exists
87 my $channel = $line;
88 print "Channel : $channel\n";
89 my $id_channel = getIdChannel($channel);
90 unless (defined($id_channel)) {
91 $id_channel = createChannel($channel);
92 }
93
94 unless (defined($id_channel)) {
95 print "Error creating channel $channel\n";
96 clean_and_exit(5);
97 }
98
99 my $i = 0;
100 while (defined($line=<CURL>) && ($i < 2)) { $i++; }
101 $i = 0;
102 while (defined($line=<CURL>) && !($line =~ /^\s*$/)) {
103 chomp($line);
104 my ($nick,$creation_date,$creation_time,$last_modified_date,$last_modified_time,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang);
105 my (undef,$test_creation,$test_last_modified) = split (/\s+/,$line);
106
107 if (( $test_creation eq '-' ) &&( $test_last_modified eq '-' )){
108 ($nick,$creation_date,$last_modified_date,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang) = split(/\s+/,$line,39);
109 }
110 elsif ( $test_creation eq '-' ) {
111 ($nick,$creation_date,$last_modified_date,$last_modified_time,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang) = split(/\s+/,$line,40);
112 }
113 else {
114 ($nick,$creation_date,$creation_time,$last_modified_date,$last_modified_time,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang) = split(/\s+/,$line,41);
115 }
116
117 my $id_user = getIdUser($nick);
118 unless (defined($id_user)) {
119 $id_user = createUser($nick);
120 }
121 unless (defined($id_user)) {
122 print "Could not create user $nick\n";
123 clean_and_exit(6);
124 }
125 my $strCreationDate;
126 unless ( $creation_date eq '-' ) {
127 my (undef,undef,$sYear) = split (/\//,$creation_date);
128 my (undef,$sMonth,undef) = split (/\//,$creation_date);
129 my ($sDay,undef,undef) = split (/\//,$creation_date);
130 $strCreationDate = "$sYear-$sMonth-$sDay $creation_time";
131 }
132 my $strLastModifiedDate;
133 unless ( $last_modified_date eq '-' ) {
134 my (undef,undef,$sYear) = split (/\//,$last_modified_date);
135 my (undef,$sMonth,undef) = split (/\//,$last_modified_date);
136 my ($sDay,undef,undef) = split (/\//,$last_modified_date);
137 $strLastModifiedDate = "$sYear-$sMonth-$sDay $last_modified_time";
138 }
139
140 my $sQuery = "INSERT INTO USER_CHANNEL_STATS (id_channel,id_user,creation_date,last_modified,xp,lvl,xp_level_sup,mun,mun_max,charg,charg_max,prec_theorique,efficacite_tirs,deflex,armure,enrayage,enraye,nbr_enrayages,arme,nbr_confisc,canards,super_canards,rates,tirs_a_vide,tirs_enrayes,recharg_compulsifs,accidents,tirs_sauvages,mun_utilis,tirs_recus,tirs_encaisses,tirs_devies,deces,meilleur_tps,tps_react_moyen,fatigue,karma,rentabilite,depense,rang) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
141 my $sth = $dbh->prepare($sQuery);
142
143 unless ($sth->execute($id_channel,$id_user,$strCreationDate,$strLastModifiedDate,$xp,$lvl,$xp_level_sup,$mun,$mun_max,$charg,$charg_max,$prec_theorique,$efficacite_tirs,$deflex,$armure,$enrayage,$enraye,$nbr_enrayages,$arme,$nbr_confisc,$canards,$super_canards,$rates,$tirs_a_vide,$tirs_enrayes,$recharg_compulsifs,$accidents,$tirs_sauvages,$mun_utilis,$tirs_recus,$tirs_encaisses,$tirs_devies,$deces,$meilleur_tps,$tps_react_moyen,$fatigue,$karma,$rentabilite,$depense,$rang)) {
144 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
145 }
146 $i++;
147 }
148 print "$channel $i joueurs traités\n";
149}
150
151close CURL;
152
153clean_and_exit(0);
154
155# +---------------------------------------------------------------------------+
156# ! SUBS IMPLEMENTATION !
157# +---------------------------------------------------------------------------+
158
159sub clean_and_exit(@) {
160 my ($iRetValue) = @_;
161 print "Cleaning and exiting...\n";
162 if (defined($dbh) && ($dbh != 0)) {
163 $dbh->disconnect();
164 }
165 exit $iRetValue;
166}
167
168sub dbConnect(@) {
169 my ($dbname,$dbhost,$dbport,$dbuser,$dbpasswd) = @_;
170 my $connectionInfo="DBI:mysql:database=$dbname;$dbhost:$dbport"; # Database connection string
171 my $dbh; # Database handle
172
173 print "dbConnect() Connecting to Database : " . $dbname;
174
175 unless ( $dbh = DBI->connect($connectionInfo,$dbuser,$dbpasswd) ) {
176 print "dbConnect() DBI Error : " . $DBI::errstr . "\n";
177 print "dbConnect() DBI Native error code : " . $DBI::err . "\n";
178 if ( defined( $DBI::err ) && ( $DBI::err == 1049 ) ) {
179 clean_and_exit($DBI::err);
180 }
181 return undef;
182 }
183 print "dbConnect() Connected to $dbname.\n";
184 return $dbh;
185}
186
187sub getIdChannel(@) {
188 my ($channel) = @_;
189 my $id_channel;
190 my $sQuery = "SELECT * FROM CHANNEL WHERE name=?";
191 my $sth = $dbh->prepare($sQuery);
192 unless ($sth->execute($channel)) {
193 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
194 }
195 else {
196 if (my $ref = $sth->fetchrow_hashref()) {
197 $id_channel = $ref->{'id_channel'};
198 }
199 }
200 return $id_channel;
201}
202
203sub createChannel(@) {
204 my ($channel) = @_;
205 my $id_channel;
206 my $sQuery = "INSERT INTO CHANNEL (name) VALUES (?)";
207 my $sth = $dbh->prepare($sQuery);
208 unless ($sth->execute($channel)) {
209 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
210 }
211 else {
212 $id_channel = getIdChannel($channel);
213 }
214 return $id_channel;
215}
216
217sub getIdUser(@) {
218 my ($nick) = @_;
219 my $id_user;
220 my $sQuery = "SELECT * FROM USER WHERE nick=?";
221 my $sth = $dbh->prepare($sQuery);
222 unless ($sth->execute($nick)) {
223 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
224 }
225 else {
226 if (my $ref = $sth->fetchrow_hashref()) {
227 $id_user = $ref->{'id_user'};
228 }
229 }
230 return $id_user;
231}
232
233sub createUser(@) {
234 my ($nick) = @_;
235 my $id_user;
236 my $sQuery = "INSERT INTO USER (nick) VALUES (?)";
237 my $sth = $dbh->prepare($sQuery);
238 unless ($sth->execute($nick)) {
239 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
240 }
241 else {
242 $id_user = getIdUser($nick);
243 }
244 return $id_user;
245}
246
247sub setMysqlUTF8() {
248 my $sQuery = "SET NAMES 'utf8'";
249 my $sth = $dbh->prepare($sQuery);
250 unless ($sth->execute()) {
251 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
252 return 0;
253 }
254 $sQuery = "SET CHARACTER SET utf8";
255 $sth = $dbh->prepare($sQuery);
256 unless ($sth->execute()) {
257 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
258 return 0;
259 }
260 $sQuery = "SET COLLATION_CONNECTION = 'utf8_general_ci'";
261 $sth = $dbh->prepare($sQuery);
262 unless ($sth->execute()) {
263 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
264 return 0;
265 }
266 else {
267 return 1;
268 }
269}
270
271sub truncateStatsTable() {
272 my $sQuery = "TRUNCATE TABLE USER_CHANNEL_STATS";
273 my $sth = $dbh->prepare($sQuery);
274 unless ($sth->execute()) {
275 print "SQL Error : " . $DBI::errstr . " Query : $sQuery\n";
276 return 0;
277 }
278 else {
279 print "Table USER_CHANNEL_STATS truncated\n";
280 return 1;
281 }
282}