1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
package MasterServer::Database::SQLite::dbStats;
use strict;
use warnings;
use Exporter 'import';
our @EXPORT = qw| get_gamenames
get_gamestats
get_listedstats
write_stat
write_direct_beacons
write_kfstats |;
################################################################################
## get a list of distinct gamenames currently in the server list
################################################################################
sub get_gamenames {
my $self = shift;
return $self->{dbh}->selectall_arrayref(
"SELECT distinct gamename
FROM serverlist");
}
################################################################################
## get statistics (num_direct, num_total) per gamename
################################################################################
sub get_gamestats {
my ($self, $gn) = @_;
return $self->db_all(
"SELECT COUNT(CASE WHEN b333ms THEN 1 END) as num_uplink, count(*) as num_total
FROM serverlist
WHERE gamename = ? AND updated > datetime(?, \'unixepoch\')",
lc $gn, time-7200);
}
################################################################################
## get a list of distinct gamenames currently in the database. it does not
## matter whether they are recent or old, as long as the game is currently in
## the database.
################################################################################
sub get_listedstats {
my $self = shift;
return $self->{dbh}->selectall_arrayref(
"SELECT gamename
FROM games
WHERE num_uplink > 0
OR num_total > 0");
}
################################################################################
# Write the stats to the games table
# A stat can not exist without existing gamename. Was inserted by cipher loader.
################################################################################
sub write_stat {
my ($self, %o) = @_;
return $self->{dbh}->do(
"UPDATE games
SET num_uplink = ?,
num_total = ?
WHERE gamename = ?",
undef, $o{num_uplink}, $o{num_total}, lc $o{gamename});
}
################################################################################
# Determine from the last beacon whether the server is still uplinking
# directly to us, or whether it stopped uplinking and is now artificially
# kept in the database.
################################################################################
sub write_direct_beacons {
my $self = shift;
my $u = $self->{dbh}->do(
"UPDATE serverlist
SET b333ms = CAST(0 AS BOOLEAN)
WHERE beacon < datetime(?, \'unixepoch\') AND b333ms",
undef, time-3600);
$self->log("unset", "Lost $u direct beacons.") if ($u > 0);
}
################################################################################
## Write the KFStats to the database
################################################################################
sub write_kfstats {
my ($self, $h) = @_;
# check if entry already exists.
my $u = $self->{dbh}->selectall_arrayref(
"SELECT * FROM kfstats WHERE UTkey = ? ", undef, $h->{UTkey});
if ( !defined $u->[0] ) {
$u = $self->{dbh}->do(
"INSERT INTO kfstats (UTkey, Username) VALUES (?,?)",
undef, $h->{UTkey}, $h->{Username});
$self->log("kfnew", "New KF Player $h->{Username} added");
}
# update existing information
$self->{dbh}->do("UPDATE kfstats SET
Username = ?,
CurrentVeterancy = ?,
TotalKills = ?,
DecaptedKills = ?,
TotalMeleeDamage = ?,
MeleeKills = ?,
PowerWpnKills = ?,
BullpupDamage = ?,
StalkerKills = ?,
TotalWelded = ?,
TotalHealed = ?,
TotalPlaytime =?,
GamesWon = ?,
GamesLost = ?
WHERE UTkey = ?", undef,
$h->{Username},
$h->{CurrentVeterancy},
$h->{TotalKills},
$h->{DecaptedKills},
$h->{TotalMeleeDamage},
$h->{MeleeKills},
$h->{PowerWpnKills},
$h->{BullpupDamage},
$h->{StalkerKills},
$h->{TotalWelded},
$h->{TotalHealed},
$h->{TotalPlaytime},
$h->{GamesWon},
$h->{GamesLost},
$h->{UTkey}
);
}
1;
|
