6 # Read preprocessed GS-Logs from STDIN, and flush them into a SQL-Database
8 # Copyright (C) 2001 Andreas Ulbrich, Ralf Ertzinger (ndccode@ndc.sh)
10 # This program is free software; you can redistribute it and/or modify
11 # it under the terms of the GNU General Public License as published by
12 # the Free Software Foundation; either version 2 of the License, or
13 # (at your option) any later version.
15 # This program is distributed in the hope that it will be useful,
16 # but WITHOUT ANY WARRANTY; without even the implied warranty of
17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
27 # Define SQL-Variables
29 $sql_database = "stats";
30 $sql_db_table = "PlayerStats";
31 $sql_datasource = "DBI:mysql:$sql_database";
34 $sql_db_handle = DBI->connect($sql_datasource, $sql_username, $sql_password) or die "Could not connect to database.\n";
40 # Load a list of players who are _not_ updated.
42 open(NOUPDATE,"noupdate") or die "Could not open noupdate: $!\n";
43 @noupdate = <NOUPDATE>;
46 print "List of players not being updated by specific request:\n";
47 print join("\n", @noupdate);
50 foreach $nick (@noupdate) { $noupdate{$nick} = 1 }
53 # First, delete all players who didn't play within the last 100 days
55 print "Deleting players...\n";
57 $sql_query = $sql_db_handle->prepare("DELETE from $sql_db_table WHERE TO_DAYS(NOW()) - TO_DAYS(LastSeen) > 100");
58 $sql_query->execute or die "Could not delete datasets.";
61 # Flush data from previous day (in fact, flush only 'mapsplayed'. The
62 # DailyStats.pl script will ignore these datasets for table generation.
64 print "Flushing datasets...\n";
66 $sql_query = $sql_db_handle->prepare("UPDATE $sql_db_table SET MapsPlayed=0, fe_rank=0");
67 $sql_query->execute or die "Could not flush database.\n";
72 print "Processing...\n";
74 LOOP: while ($gslog_line = shift(@gslog)) {
76 ($pl_nick,$pl_kills,$pl_victims,$pl_suicides,$pl_maps_played,$pl_maps_won,$pl_time) = split(":",$gslog_line);
78 # Check if the player is on the "noupdate" list. Skip it then.
79 if (defined($noupdate{$pl_nick})) {
80 print "Skipping $pl_nick due to noupdate.";
85 if (($pl_nick ne "") & ($pl_time > 60) & ($pl_maps_played != 0)) {
87 # Check if there is already a dataset for this player.
88 # If not, init all values, and create an entry.
90 $sql_query = $sql_db_handle->prepare("SELECT nick FROM $sql_db_table WHERE nick=?");
91 $sql_query->execute($pl_nick);
92 $sql_result = $sql_query->fetchrow_hashref;
95 unless (defined($sql_result)) {
96 $sql_query = $sql_db_handle->prepare("INSERT INTO $sql_db_table (Nick, Email, Picture, Homepage, Skill, Skill_top, Kills, Victims, Suicides, MapsPlayed, MapsWon, Time, FPH, fe_Skill, fe_Kills, fe_Victims, fe_Suicides, fe_MapsPlayed, fe_MapsWon, fe_Time, fe_FPH, LastSeen) VALUES (?, '', 'empty.jpg', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NOW())");
97 $sql_query->execute($pl_nick) or die "Could not insert dataset for $pl_nick\n";
103 $sql_query = $sql_db_handle->prepare("SELECT Skill, Skill_top, Kills, Victims, Suicides, MapsPlayed, MapsWon, Time, FPH, fe_Skill, fe_Kills, fe_Victims, fe_Suicides, fe_MapsPlayed, fe_MapsWon, fe_Time, fe_FPH FROM $sql_db_table WHERE nick=?");
104 $sql_query->execute($pl_nick);
105 $sql_result = $sql_query->fetchrow_hashref;
107 $pl_sql_skill = $sql_result->{Skill};
108 $pl_sql_skill_top = $sql_result->{Skill_top};
109 $pl_sql_kills = $sql_result->{Kills};
110 $pl_sql_victims = $sql_result->{Victims};
111 $pl_sql_suicides = $sql_result->{Suicides};
112 $pl_sql_mapsplayed = $sql_result->{MapsPlayed};
113 $pl_sql_mapswon = $sql_result->{MapsWon};
114 $pl_sql_time = $sql_result->{"Time"};
115 $pl_sql_fph = $sql_result->{FPH};
116 $pl_sql_fe_skill = $sql_result->{fe_Skill};
117 $pl_sql_fe_kills = $sql_result->{fe_Kills};
118 $pl_sql_fe_victims = $sql_result->{fe_Victims};
119 $pl_sql_fe_suicides = $sql_result->{fe_Suicides};
120 $pl_sql_fe_mapsplayed = $sql_result->{fe_MapsPlayed};
121 $pl_sql_fe_mapswon = $sql_result->{fe_MapsWon};
122 $pl_sql_fe_time = $sql_result->{fe_Time};
123 $pl_sql_fe_fph = $sql_result->{fe_FPH};
127 $pl_skill = ($pl_kills / ($pl_victims + $pl_suicides + 1)) * (1 + ($pl_maps_won / $pl_maps_played));
128 $pl_fph = ($pl_kills * 3600) / $pl_time;
129 $pl_time = $pl_time / 60;
131 # Flush the data into the fields
133 $pl_sql_skill = $pl_skill;
134 $pl_sql_kills = $pl_kills;
135 $pl_sql_victims = $pl_victims;
136 $pl_sql_suicides = $pl_suicides;
137 $pl_sql_mapsplayed = $pl_maps_played;
138 $pl_sql_mapswon = $pl_maps_won;
139 $pl_sql_time = $pl_time;
140 $pl_sql_fph = $pl_fph;
142 $pl_sql_fe_kills += $pl_kills;
143 $pl_sql_fe_victims += $pl_victims;
144 $pl_sql_fe_suicides += $pl_suicides;
145 $pl_sql_fe_mapsplayed += $pl_maps_played;
146 $pl_sql_fe_mapswon += $pl_maps_won;
147 $pl_sql_fe_time += $pl_time;
148 $pl_sql_fe_fph = ($pl_sql_fe_kills * 60) / $pl_sql_fe_time;
149 $pl_sql_fe_skill = ($pl_sql_fe_kills / ($pl_sql_fe_victims + $pl_sql_fe_suicides + 1)) * (1 + ($pl_sql_fe_mapswon / $pl_sql_fe_mapsplayed));
150 if ($pl_sql_fe_mapsplayed < 10) {
151 $pl_sql_fe_skill = 0;
153 if ($pl_sql_fe_skill > $pl_sql_skill_top) {
154 $pl_sql_skill_top = $pl_sql_fe_skill;
157 $sql_query = $sql_db_handle->prepare("UPDATE $sql_db_table SET Skill='$pl_sql_skill', Skill_top='$pl_sql_skill_top', Kills='$pl_sql_kills', Victims='$pl_sql_victims', Suicides='$pl_sql_suicides', MapsPlayed='$pl_sql_mapsplayed', MapsWon='$pl_sql_mapswon', Time='$pl_sql_time', FPH='$pl_sql_fph', fe_Skill='$pl_sql_fe_skill', fe_kills='$pl_sql_fe_kills', fe_Victims='$pl_sql_fe_victims', fe_Suicides='$pl_sql_fe_suicides', fe_MapsPlayed='$pl_sql_fe_mapsplayed', fe_MapsWon='$pl_sql_fe_mapswon', fe_Time='$pl_sql_fe_time', fe_FPH='$pl_sql_fe_fph', LastSeen=NOW() WHERE nick=?");
158 $sql_query->execute($pl_nick) or die "Could not update entry: $pl_nick\n";
166 $sql_db_handle->disconnect;