]>
Commit | Line | Data |
---|---|---|
5414dee4 | 1 | package PMG::Statistic; |
e350fb98 DM |
2 | |
3 | use strict; | |
5414dee4 | 4 | use warnings; |
e350fb98 | 5 | use DBI; |
7a6e6966 | 6 | use Encode qw(encode); |
e350fb98 DM |
7 | use Time::Local; |
8 | use Time::Zone; | |
9 | ||
5414dee4 DM |
10 | use PVE::SafeSyslog; |
11 | ||
9f67f5b3 | 12 | use PMG::ClusterConfig; |
5414dee4 DM |
13 | use PMG::RuleDB; |
14 | ||
e350fb98 | 15 | sub new { |
44017d49 | 16 | my ($self, $start, $end) = @_; |
3d511edd | 17 | |
e350fb98 | 18 | $self = {}; |
3d511edd | 19 | |
e350fb98 DM |
20 | bless($self); |
21 | ||
9b1db2e4 DM |
22 | if (defined($start) && defined($end)) { |
23 | $self->timespan($start, $end); | |
e350fb98 | 24 | } else { |
9b1db2e4 DM |
25 | my $ctime = time(); |
26 | $self->timespan($ctime, $ctime - 24*3600); | |
e350fb98 DM |
27 | } |
28 | ||
e350fb98 DM |
29 | return $self; |
30 | } | |
31 | ||
32 | sub clear_stats { | |
33 | my ($dbh) = @_; | |
34 | ||
35 | eval { | |
36 | $dbh->begin_work; | |
3d511edd | 37 | |
e350fb98 DM |
38 | $dbh->do ("LOCK TABLE StatInfo"); |
39 | $dbh->do ("LOCK TABLE ClusterInfo"); | |
40 | ||
41 | $dbh->do ("DELETE FROM Statinfo"); | |
42 | $dbh->do ("DELETE FROM DailyStat"); | |
5e1408fd | 43 | $dbh->do ("DELETE FROM LocalStat"); |
e350fb98 DM |
44 | $dbh->do ("DELETE FROM DomainStat"); |
45 | $dbh->do ("DELETE FROM VirusInfo"); | |
46 | $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_DomainStat'"); | |
47 | $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_DailyStat'"); | |
5e1408fd | 48 | $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_LocalStat'"); |
e350fb98 DM |
49 | $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_VirusInfo'"); |
50 | ||
51 | $dbh->commit; | |
52 | }; | |
53 | if ($@) { | |
54 | $dbh->rollback; | |
55 | die $@; | |
3d511edd | 56 | } |
e350fb98 DM |
57 | } |
58 | ||
59 | sub update_stats_generic { | |
60 | my ($dbh, $statinfoid, $select, $update, $insert) = @_; | |
61 | ||
62 | my $todo = 0; | |
63 | my $maxentries = 100000; | |
64 | ||
65 | ||
66 | eval { | |
67 | $dbh->begin_work; | |
68 | ||
69 | $dbh->do("LOCK TABLE StatInfo IN EXCLUSIVE MODE"); | |
70 | ||
71 | my $sth = $dbh->prepare("SELECT last_value FROM cstatistic_id_seq"); | |
72 | $sth->execute(); | |
73 | my $maxinfo = $sth->fetchrow_hashref(); | |
74 | goto COMMIT if !$maxinfo; | |
75 | my $last_value = $maxinfo->{last_value}; | |
76 | goto COMMIT if !defined ($last_value); | |
77 | ||
78 | $sth = $dbh->prepare("SELECT ivalue as value FROM StatInfo WHERE NAME = '$statinfoid'"); | |
79 | $sth->execute(); | |
80 | my $statinfo = $sth->fetchrow_hashref(); | |
81 | ||
82 | my $startid = $statinfo ? $statinfo->{value} : 0; | |
83 | goto COMMIT if $startid > $last_value; | |
84 | ||
85 | my $endid = $startid + $maxentries; | |
86 | $endid = $last_value + 1 if $endid > $last_value; | |
87 | $todo = $last_value + 1 - $endid; | |
88 | ||
89 | my $timezone = tz_local_offset();; | |
90 | ||
91 | $select =~ s/__timezone__/$timezone/g; | |
92 | $select =~ s/__startid__/$startid/g; | |
93 | $select =~ s/__endid__/$endid/g; | |
94 | ||
95 | $sth = $dbh->prepare($select); | |
96 | $sth->execute(); | |
97 | ||
98 | my $cmd = ""; | |
99 | #print "TEST:$last_value:$endid:$todo\n"; | |
100 | ||
101 | while (my $ref = $sth->fetchrow_hashref()) { | |
102 | if ($ref->{exists}) { | |
103 | $cmd .= &$update($ref); | |
104 | } else { | |
105 | $cmd .= &$insert($ref); | |
106 | } | |
107 | } | |
108 | ||
109 | $dbh->do ($cmd) if $cmd; | |
110 | ||
111 | $sth->finish(); | |
112 | ||
113 | if ($statinfo) { | |
114 | $dbh->do("UPDATE StatInfo SET ivalue = $endid WHERE NAME = '$statinfoid'"); | |
115 | } else { | |
116 | $dbh->do("INSERT INTO StatInfo VALUES ('$statinfoid', $endid)"); | |
117 | } | |
118 | ||
3d511edd | 119 | COMMIT: |
e350fb98 DM |
120 | $dbh->commit; |
121 | }; | |
122 | ||
123 | if ($@) { | |
124 | $dbh->rollback; | |
125 | die $@; | |
126 | } | |
127 | ||
128 | return $todo; | |
129 | } | |
130 | ||
131 | sub update_stats_dailystat { | |
132 | my ($dbh, $cinfo) = @_; | |
133 | ||
9f67f5b3 DM |
134 | my $role = $cinfo->{local}->{type} // '-'; |
135 | return 0 if !(($role eq '-') || ($role eq 'master')); | |
e350fb98 DM |
136 | |
137 | my $select = "SELECT sub.*, dailystat.time IS NOT NULL as exists FROM " . | |
138 | "(SELECT COUNT (CASE WHEN direction THEN 1 ELSE NULL END) as count_in, " . | |
139 | "COUNT (CASE WHEN NOT direction THEN 1 ELSE NULL END) as count_out, " . | |
140 | "SUM (CASE WHEN direction THEN bytes ELSE NULL END) / (1024.0*1024) as bytes_in, " . | |
141 | "SUM (CASE WHEN NOT direction THEN bytes ELSE NULL END) / (1024.0*1024) as bytes_out, " . | |
142 | "COUNT (CASE WHEN virusinfo IS NOT NULL AND direction THEN 1 ELSE NULL END) AS virus_in, " . | |
143 | "COUNT (CASE WHEN virusinfo IS NOT NULL AND NOT direction THEN 1 ELSE NULL END) AS virus_out, " . | |
144 | "COUNT (CASE WHEN virusinfo IS NULL AND direction AND ptime > 0 AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_in, " . | |
145 | "COUNT (CASE WHEN virusinfo IS NULL AND NOT direction AND ptime > 0 AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_out, " . | |
146 | "COUNT (CASE WHEN virusinfo IS NULL AND direction AND sender = '' THEN 1 ELSE NULL END) as bounces_in, " . | |
147 | "COUNT (CASE WHEN virusinfo IS NULL AND NOT direction AND sender = '' THEN 1 ELSE NULL END) as bounces_out, " . | |
148 | "COUNT (CASE WHEN virusinfo IS NULL AND ptime = 0 AND spamlevel = 5 THEN 1 ELSE NULL END) as glcount, " . | |
149 | "COUNT (CASE WHEN virusinfo IS NULL AND ptime = 0 AND spamlevel = 4 THEN 1 ELSE NULL END) as spfcount, " . | |
150 | "sum (cstatistic.ptime) / 1000.0 as ptimesum, " . | |
151 | "((cstatistic.time + __timezone__) / 3600) * 3600 as hour " . | |
152 | "from cstatistic where id >= __startid__ and id < __endid__ group by hour) as sub " . | |
153 | "left join dailystat on (sub.hour = dailystat.time)"; | |
154 | ||
155 | my $update = sub { | |
156 | my $ref = shift; | |
157 | my @values = (); | |
158 | my $sql = ''; | |
159 | ||
160 | push @values, "CountIn = CountIn + $ref->{count_in}" if $ref->{count_in}; | |
161 | push @values, "CountOut = CountOut + $ref->{count_out}" if $ref->{count_out}; | |
162 | push @values, "BytesIn = BytesIn + $ref->{bytes_in}" if $ref->{bytes_in}; | |
163 | push @values, "BytesOut = BytesOut + $ref->{bytes_out}" if $ref->{bytes_out}; | |
164 | push @values, "VirusIn = VirusIn + $ref->{virus_in}" if $ref->{virus_in}; | |
165 | push @values, "VirusOut = VirusOut + $ref->{virus_out}" if $ref->{virus_out}; | |
166 | push @values, "SpamIn = SpamIn + $ref->{spam_in}" if $ref->{spam_in}; | |
167 | push @values, "SpamOut = SpamOut + $ref->{spam_out}" if $ref->{spam_out}; | |
168 | push @values, "BouncesIn = BouncesIn + $ref->{bounces_in}" if $ref->{bounces_in}; | |
169 | push @values, "BouncesOut = BouncesOut + $ref->{bounces_out}" if $ref->{bounces_out}; | |
170 | push @values, "GreylistCount = GreylistCount + $ref->{glcount}" if $ref->{glcount}; | |
171 | push @values, "SPFCount = SPFCount + $ref->{spfcount}" if $ref->{spfcount}; | |
172 | push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum}; | |
9972a7ce | 173 | push @values, "MTime = EXTRACT(EPOCH FROM now())::INTEGER"; |
e350fb98 DM |
174 | |
175 | if (scalar (@values)) { | |
176 | $sql .= "UPDATE dailystat SET "; | |
177 | $sql .= join (',', @values); | |
178 | $sql .= " WHERE time = $ref->{hour};"; | |
179 | } | |
180 | return $sql; | |
181 | }; | |
182 | ||
183 | my $insert = sub { | |
184 | my $ref = shift; | |
185 | ||
3d511edd | 186 | my $sql = "INSERT INTO dailystat " . |
e350fb98 DM |
187 | "(Time,CountIn,CountOut,BytesIn,BytesOut,VirusIn,VirusOut,SpamIn,SpamOut," . |
188 | "BouncesIn,BouncesOut,GreylistCount,SPFCount,RBLCount,PTimeSum,Mtime) " . | |
3d511edd | 189 | "VALUES ($ref->{hour}," . ($ref->{count_in} || 0) . ',' . ($ref->{count_out} || 0) . ',' . |
e350fb98 DM |
190 | ($ref->{bytes_in} || 0) . ',' . ($ref->{bytes_out} || 0) . ',' . |
191 | ($ref->{virus_in} || 0) . ',' . ($ref->{virus_out} || 0) . ',' . | |
192 | ($ref->{spam_in} || 0) . ',' . ($ref->{spam_out} || 0) . ',' . | |
193 | ($ref->{bounces_in} || 0) . ',' . ($ref->{bounces_out} || 0) . ',' . | |
3d511edd | 194 | ($ref->{glcount} || 0) . ',' . ($ref->{spfcount} || 0) . ',0,' . ($ref->{ptimesum} || 0) . |
9972a7ce | 195 | ",EXTRACT(EPOCH FROM now())::INTEGER);"; |
e350fb98 DM |
196 | |
197 | return $sql; | |
198 | }; | |
199 | ||
200 | return update_stats_generic ($dbh, 'dailystat_index', $select, $update, $insert); | |
201 | ||
202 | } | |
203 | ||
204 | sub update_stats_domainstat_in { | |
205 | my ($dbh, $cinfo) = @_; | |
206 | ||
9f67f5b3 DM |
207 | my $role = $cinfo->{local}->{type} // '-'; |
208 | return 0 if !(($role eq '-') || ($role eq 'master')); | |
e350fb98 DM |
209 | |
210 | my $sub1 = "select distinct cstatistic_cid, cstatistic_rid, " . | |
211 | "lower(substring(receiver from position ('\@' in receiver) + 1)) as domain, " . | |
212 | "((cstatistic.time + __timezone__) / 86400) * 86400 as day " . | |
213 | "from CStatistic, CReceivers where cid = cstatistic_cid AND rid = cstatistic_rid AND " . | |
214 | "id >= __startid__ and id < __endid__ AND direction " . | |
215 | "group by cstatistic_cid, cstatistic_rid, day, domain"; | |
3d511edd | 216 | |
e350fb98 DM |
217 | |
218 | my $select = "SELECT sub.*, domainstat.time IS NOT NULL as exists FROM " . | |
219 | "(SELECT day, domain, COUNT (id) as count_in, SUM (bytes) / (1024.0*1024) as bytes_in, " . | |
220 | "COUNT (CASE WHEN virusinfo IS NOT NULL THEN 1 ELSE NULL END) AS virus_in, " . | |
221 | "COUNT (CASE WHEN virusinfo IS NULL AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_in, " . | |
222 | "COUNT (CASE WHEN virusinfo IS NULL AND sender = '' THEN 1 ELSE NULL END) as bounces_in, " . | |
223 | "sum (cstatistic.ptime) / 1000.0 as ptimesum " . | |
224 | "from cstatistic, ($sub1) as ddb " . | |
225 | "WHERE ddb.cstatistic_cid = cstatistic.cid AND ddb.cstatistic_rid = cstatistic.rid GROUP BY day, domain) as sub " . | |
226 | "left join domainstat on (day = domainstat.time and sub.domain = domainstat.domain)"; | |
227 | ||
228 | my $update = sub { | |
229 | my $ref = shift; | |
230 | my @values = (); | |
231 | my $sql = ''; | |
232 | ||
233 | push @values, "CountIn = CountIn + $ref->{count_in}" if $ref->{count_in}; | |
234 | push @values, "BytesIn = BytesIn + $ref->{bytes_in}" if $ref->{bytes_in}; | |
235 | push @values, "VirusIn = VirusIn + $ref->{virus_in}" if $ref->{virus_in}; | |
236 | push @values, "SpamIn = SpamIn + $ref->{spam_in}" if $ref->{spam_in}; | |
237 | push @values, "BouncesIn = BouncesIn + $ref->{bounces_in}" if $ref->{bounces_in}; | |
238 | push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum}; | |
9972a7ce | 239 | push @values, "MTime = EXTRACT(EPOCH FROM now())::INTEGER"; |
3d511edd | 240 | |
e350fb98 DM |
241 | if (scalar (@values)) { |
242 | $sql .= "UPDATE domainstat SET "; | |
243 | $sql .= join (',', @values); | |
244 | $sql .= " WHERE time = $ref->{day} and domain = " . $dbh->quote($ref->{domain}) . ';'; | |
245 | } | |
246 | return $sql; | |
247 | }; | |
248 | ||
249 | my $insert = sub { | |
250 | my $ref = shift; | |
251 | ||
3d511edd DM |
252 | my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain}) . ',' . |
253 | ($ref->{count_in} || 0) . ',0,' . | |
e350fb98 DM |
254 | ($ref->{bytes_in} || 0) . ',0,' . |
255 | ($ref->{virus_in} || 0) . ',0,' . | |
256 | ($ref->{spam_in} || 0) . ',0,' . | |
3d511edd | 257 | ($ref->{bounces_in} || 0) . ',0,' . |
e350fb98 | 258 | ($ref->{ptimesum} || 0) . |
9972a7ce | 259 | ",EXTRACT(EPOCH FROM now())::INTEGER);"; |
3d511edd | 260 | |
e350fb98 DM |
261 | return $sql; |
262 | }; | |
263 | ||
264 | update_stats_generic ($dbh, 'domainstat_in_index', $select, $update, $insert); | |
265 | ||
266 | } | |
267 | ||
268 | sub update_stats_domainstat_out { | |
269 | my ($dbh, $cinfo) = @_; | |
270 | ||
9f67f5b3 DM |
271 | my $role = $cinfo->{local}->{type} // '-'; |
272 | return 0 if !(($role eq '-') || ($role eq 'master')); | |
e350fb98 DM |
273 | |
274 | my $select = "SELECT sub.*, domainstat.time IS NOT NULL as exists FROM " . | |
275 | "(SELECT COUNT (ID) as count_out, SUM (bytes) / (1024.0*1024) as bytes_out, " . | |
276 | "COUNT (CASE WHEN virusinfo IS NOT NULL THEN 1 ELSE NULL END) AS virus_out, " . | |
277 | "COUNT (CASE WHEN virusinfo IS NULL AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_out, " . | |
278 | "COUNT (CASE WHEN virusinfo IS NULL AND sender = '' THEN 1 ELSE NULL END) as bounces_out, " . | |
279 | "sum (cstatistic.ptime) / 1000.0 as ptimesum, " . | |
280 | "((cstatistic.time + __timezone__) / 86400) * 86400 as day, " . | |
281 | "lower(substring(sender from position ('\@' in sender) + 1)) as domain " . | |
282 | "from cstatistic where id >= __startid__ and id < __endid__ and not direction " . | |
283 | "group by day, domain) as sub " . | |
284 | "left join domainstat on (day = domainstat.time and sub.domain = domainstat.domain)"; | |
285 | ||
286 | my $update = sub { | |
287 | my $ref = shift; | |
288 | my @values = (); | |
289 | my $sql = ''; | |
290 | ||
291 | push @values, "CountOut = CountOut + $ref->{count_out}" if $ref->{count_out}; | |
292 | push @values, "BytesOut = BytesOut + $ref->{bytes_out}" if $ref->{bytes_out}; | |
293 | push @values, "VirusOut = VirusOut + $ref->{virus_out}" if $ref->{virus_out}; | |
294 | push @values, "SpamOut = SpamOut + $ref->{spam_out}" if $ref->{spam_out}; | |
295 | push @values, "BouncesOut = BouncesOut + $ref->{bounces_out}" if $ref->{bounces_out}; | |
296 | push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum}; | |
9972a7ce | 297 | push @values, "MTime = EXTRACT(EPOCH FROM now())::INTEGER"; |
3d511edd | 298 | |
e350fb98 DM |
299 | if (scalar (@values)) { |
300 | $sql .= "UPDATE domainstat SET "; | |
301 | $sql .= join (',', @values); | |
302 | $sql .= " WHERE time = $ref->{day} and domain = " . $dbh->quote($ref->{domain}) . ';'; | |
303 | } | |
304 | return $sql; | |
305 | }; | |
306 | ||
307 | my $insert = sub { | |
308 | my $ref = shift; | |
309 | ||
310 | my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain}) . | |
3d511edd | 311 | ',0,' . ($ref->{count_out} || 0) . |
e350fb98 DM |
312 | ',0,' . ($ref->{bytes_out} || 0) . |
313 | ',0,' . ($ref->{virus_out} || 0) . | |
3d511edd DM |
314 | ',0,' . ($ref->{spam_out} || 0) . |
315 | ',0,' . ($ref->{bounces_out} || 0) . | |
316 | ','. ($ref->{ptimesum} || 0) . | |
9972a7ce | 317 | ",EXTRACT(EPOCH FROM now())::INTEGER);"; |
e350fb98 DM |
318 | |
319 | return $sql; | |
320 | }; | |
321 | ||
322 | update_stats_generic ($dbh, 'domainstat_out_index', $select, $update, $insert); | |
323 | ||
324 | } | |
325 | ||
326 | sub update_stats_virusinfo { | |
327 | my ($dbh, $cinfo) = @_; | |
328 | ||
9f67f5b3 DM |
329 | my $role = $cinfo->{local}->{type} // '-'; |
330 | return 0 if !(($role eq '-') || ($role eq 'master')); | |
e350fb98 DM |
331 | |
332 | my $select = "SELECT sub.*, virusinfo.time IS NOT NULL as exists FROM " . | |
333 | "(SELECT ((cstatistic.time + __timezone__) / 86400) * 86400 as day, " . | |
334 | "count (virusinfo) as count, virusinfo AS name " . | |
335 | "FROM cstatistic WHERE id >= __startid__ AND id < __endid__ AND virusinfo IS NOT NULL " . | |
336 | "group by day, name) as sub " . | |
337 | "left join VirusInfo on (day = virusinfo.time and sub.name = virusinfo.name)"; | |
338 | ||
339 | my $update = sub { | |
340 | my $ref = shift; | |
341 | my @values = (); | |
342 | my $sql = ''; | |
343 | ||
344 | push @values, "Count = Count + $ref->{count}" if $ref->{count}; | |
1828db45 | 345 | push @values, "MTime = EXTRACT(EPOCH FROM now())::INTEGER"; |
3d511edd | 346 | |
e350fb98 DM |
347 | if (scalar (@values)) { |
348 | $sql .= "UPDATE VirusInfo SET "; | |
349 | $sql .= join (',', @values); | |
350 | $sql .= " WHERE time = $ref->{day} and Name = " . $dbh->quote($ref->{name}) . ';'; | |
351 | } | |
352 | return $sql; | |
353 | }; | |
354 | ||
355 | my $insert = sub { | |
356 | my $ref = shift; | |
357 | ||
358 | my $sql .= "INSERT INTO VirusInfo values ($ref->{day}, " . $dbh->quote($ref->{name}) . | |
3d511edd | 359 | ',' . ($ref->{count} || 0) . |
9972a7ce | 360 | ",EXTRACT(EPOCH FROM now())::INTEGER);"; |
e350fb98 DM |
361 | |
362 | return $sql; | |
363 | }; | |
364 | ||
365 | update_stats_generic ($dbh, 'virusinfo_index', $select, $update, $insert); | |
366 | ||
367 | } | |
368 | ||
369 | ||
370 | sub update_stats { | |
371 | my ($dbh, $cinfo) = @_; | |
372 | ||
373 | while (update_stats_dailystat ($dbh, $cinfo) > 0) {}; | |
374 | while (update_stats_domainstat_in ($dbh, $cinfo) > 0) {}; | |
375 | while (update_stats_domainstat_out ($dbh, $cinfo) > 0) {}; | |
376 | while (update_stats_virusinfo ($dbh, $cinfo) > 0) {}; | |
377 | } | |
378 | ||
379 | sub total_mail_stat { | |
380 | my ($self, $rdb) = @_; | |
381 | ||
382 | my ($from, $to) = $self->localdayspan(); | |
3d511edd | 383 | |
e350fb98 DM |
384 | my ($sth, $ref); |
385 | my $glcount = 0; | |
386 | ||
387 | # this is to slow for high volume sites | |
388 | # $sth = $rdb->{dbh}->prepare("SELECT COUNT(DISTINCT Instance) AS GL FROM CGreylist " . | |
389 | # "WHERE passed = 0 AND rctime >= ? AND rctime < ? "); | |
3d511edd | 390 | # $sth->execute($from, $to); |
e350fb98 DM |
391 | # $ref = $sth->fetchrow_hashref(); |
392 | # $glcount = $ref->{gl}; | |
393 | ||
394 | my $cmds = "SELECT sum(CountIn) + $glcount AS count_in, sum(CountOut) AS count_out, " . | |
3d511edd DM |
395 | "sum (VirusIn) AS viruscount_in, sum (VirusOut) AS viruscount_out, " . |
396 | "sum (SpamIn) AS spamcount_in, sum (SpamOut) AS spamcount_out, " . | |
9f17f410 | 397 | "sum (BytesIn)*1024*1024 AS bytes_in, sum (BytesOut)*1024*1024 AS bytes_out, " . |
3d511edd | 398 | "sum (BouncesIn) AS bounces_in, sum (BouncesOut) AS bounces_out, " . |
e350fb98 DM |
399 | "sum (GreylistCount) + $glcount as glcount, " . |
400 | "sum (SPFCount) as spfcount, " . | |
4681645a | 401 | "sum (RBLCount) as rbl_rejects, " . |
e350fb98 DM |
402 | "sum(PTimeSum)/(sum(CountIn) + $glcount + sum(CountOut)) AS avptime " . |
403 | "FROM DailyStat where time >= $from and time < $to"; | |
404 | ||
405 | $sth = $rdb->{dbh}->prepare($cmds); | |
3d511edd | 406 | $sth->execute(); |
e350fb98 DM |
407 | $ref = $sth->fetchrow_hashref(); |
408 | $sth->finish(); | |
409 | ||
26e55ddd DM |
410 | foreach my $k (keys %$ref) { $ref->{$k} += 0; } # convert to numbers |
411 | ||
e350fb98 | 412 | if (!$ref->{avptime}) { |
3d511edd DM |
413 | $ref->{count_in} = $ref->{count_out} = $ref->{viruscount_in} = $ref->{viruscount_out} = |
414 | $ref->{spamcount_in} = $ref->{spamcount_out} = $ref->{glcount} = $ref->{spfcount} = | |
4681645a | 415 | $ref->{rbl_rejects} = $ref->{bounces_in} = $ref->{bounces_out} = $ref->{bytes_in} = |
6af8eea7 | 416 | $ref->{bytes_out} = $ref->{avptime} = 0; |
e350fb98 DM |
417 | } |
418 | ||
419 | $ref->{count} = $ref->{count_in} + $ref->{count_out}; | |
420 | ||
3d511edd | 421 | $ref->{junk_in} = $ref->{viruscount_in} + $ref->{spamcount_in} + $ref->{glcount} + |
4681645a | 422 | $ref->{spfcount} + $ref->{rbl_rejects}; |
e350fb98 DM |
423 | |
424 | $ref->{junk_out} = $ref->{viruscount_out} + $ref->{spamcount_out}; | |
425 | ||
e350fb98 DM |
426 | return $ref; |
427 | } | |
428 | ||
429 | sub total_spam_stat { | |
430 | my ($self, $rdb) = @_; | |
431 | my ($from, $to) = $self->timespan(); | |
3d511edd | 432 | |
0fb71fe1 TL |
433 | my $sth = $rdb->{dbh}->prepare( |
434 | "SELECT spamlevel, COUNT(spamlevel) AS count FROM CStatistic" | |
435 | ." WHERE virusinfo IS NULL and time >= ? AND time < ? AND ptime > 0 AND spamlevel > 0 " | |
436 | ." GROUP BY spamlevel ORDER BY spamlevel" | |
437 | ); | |
3d511edd | 438 | $sth->execute($from, $to); |
e350fb98 DM |
439 | |
440 | my $res = $sth->fetchall_arrayref({}); | |
441 | ||
442 | $sth->finish(); | |
443 | ||
444 | return $res; | |
445 | } | |
446 | ||
447 | sub total_virus_stat { | |
448 | my ($self, $rdb, $order) = @_; | |
449 | ||
450 | my ($from, $to) = $self->localdayspan(); | |
3d511edd | 451 | |
e350fb98 DM |
452 | $order = "count" if !$order; |
453 | ||
454 | my @oa = split (',', $order); | |
455 | ||
456 | $order = join (' DESC, ', @oa); | |
457 | $order .= ' DESC'; | |
3d511edd | 458 | |
0fb71fe1 TL |
459 | my $sth = $rdb->{dbh}->prepare( |
460 | "SELECT Name, SUM (Count) as count FROM VirusInfo WHERE time >= ? AND time < ? " | |
461 | ." GROUP BY name ORDER BY $order, name" | |
462 | ); | |
e350fb98 | 463 | |
3d511edd | 464 | $sth->execute($from, $to); |
e350fb98 DM |
465 | |
466 | my $res = $sth->fetchall_arrayref({}); | |
467 | ||
468 | $sth->finish(); | |
469 | ||
470 | return $res; | |
471 | } | |
472 | ||
473 | sub rule_count { | |
474 | my ($self, $rdb) = @_; | |
475 | ||
476 | my $sth = $rdb->{dbh}->prepare("SELECT id, name, count from rule order by count desc, name"); | |
3d511edd DM |
477 | $sth->execute(); |
478 | ||
e350fb98 DM |
479 | my $res = $sth->fetchall_arrayref({}); |
480 | $sth->finish(); | |
481 | ||
3d511edd | 482 | return $res; |
e350fb98 DM |
483 | } |
484 | ||
485 | sub total_domain_stat { | |
4b6ff4f1 | 486 | my ($self, $rdb) = @_; |
e350fb98 DM |
487 | |
488 | my ($from, $to) = $self->localdayspan(); | |
489 | ||
490 | my $query = "SELECT domain, SUM (CountIn) AS count_in, SUM (CountOut) AS count_out," . | |
6bc396fc | 491 | "SUM (BytesIn)*1024*1024 AS bytes_in, SUM (BytesOut)*1024*1024 AS bytes_out, " . |
6af8eea7 DM |
492 | "SUM (VirusIn) AS viruscount_in, SUM (VirusOut) AS viruscount_out," . |
493 | "SUM (SpamIn) as spamcount_in, SUM (SpamOut) as spamcount_out " . | |
e350fb98 | 494 | "FROM DomainStat where time >= $from AND time < $to " . |
4b6ff4f1 | 495 | "GROUP BY domain ORDER BY domain ASC"; |
e350fb98 DM |
496 | |
497 | my $sth = $rdb->{dbh}->prepare($query); | |
3d511edd | 498 | $sth->execute(); |
e350fb98 DM |
499 | |
500 | my $res = $sth->fetchall_arrayref({}); | |
501 | ||
502 | $sth->finish(); | |
503 | ||
504 | return $res; | |
505 | } | |
506 | ||
507 | sub clear_rule_count { | |
508 | my ($self, $rdb, $id) = @_; | |
509 | ||
510 | if (defined($id)) { | |
511 | $rdb->{dbh}->do ("UPDATE rule set count = 0 where id = ?", undef, $id); | |
512 | } else { | |
513 | $rdb->{dbh}->do("UPDATE rule set count = 0"); | |
514 | } | |
515 | } | |
516 | ||
517 | sub query_cond_good_mail { | |
518 | my ($self, $from, $to) = @_; | |
519 | return "time >= $from AND time < $to AND bytes > 0 AND sender IS NOT NULL"; | |
520 | } | |
521 | ||
522 | sub query_active_workers { | |
523 | my ($self) = @_; | |
524 | my ($from, $to) = $self->timespan(); | |
3d511edd | 525 | |
e350fb98 DM |
526 | my $start = $from - (3600*24)*90; # from - 90 days |
527 | my $cond_good_mail = $self->query_cond_good_mail ($start, $to); | |
528 | ||
529 | return "SELECT DISTINCT sender as worker FROM CStatistic WHERE $cond_good_mail AND NOT direction"; | |
530 | } | |
531 | ||
0ab6dc2a DM |
532 | my $compute_sql_orderby = sub { |
533 | my ($sorters, $sort_default, $sort_always_prop) = @_; | |
534 | ||
535 | my $has_default_sort; | |
536 | ||
537 | my $orderby = ''; | |
538 | ||
539 | foreach my $obj (@$sorters) { | |
540 | $has_default_sort = 1 if $obj->{property} eq $sort_always_prop; | |
541 | $orderby .= ', ' if $orderby; | |
542 | $orderby .= "$obj->{property} $obj->{direction}" | |
543 | } | |
544 | ||
545 | $orderby .= $sort_default if !$orderby; | |
546 | ||
547 | $orderby .= ", $sort_always_prop" if !$has_default_sort; | |
548 | ||
549 | return $orderby; | |
550 | }; | |
551 | ||
7a6e6966 SI |
552 | sub user_stat_to_perlstring { |
553 | my ($entry) = @_; | |
554 | ||
555 | my $res = { }; | |
556 | ||
557 | for my $a (keys %$entry) { | |
558 | if ($a eq 'receiver' || $a eq 'sender' || $a eq 'contact') { | |
559 | $res->{$a} = PMG::Utils::try_decode_utf8($entry->{$a}); | |
560 | } else { | |
561 | $res->{$a} = $entry->{$a}; | |
562 | } | |
563 | } | |
564 | ||
565 | return $res; | |
566 | } | |
567 | ||
be4961c2 DC |
568 | my sub get_filter_text { |
569 | my ($dbh, $field, $filter) = @_; | |
570 | ||
571 | if (!$filter || !$field) { | |
572 | return ''; | |
573 | } | |
574 | ||
575 | my $pattern = $dbh->quote(encode('UTF-8', "%${filter}%")); | |
576 | ||
577 | return "AND ${field} like ${pattern} "; | |
578 | } | |
579 | ||
e350fb98 | 580 | sub user_stat_contact_details { |
bcf493af DM |
581 | my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_; |
582 | ||
e350fb98 | 583 | my ($from, $to) = $self->timespan(); |
e350fb98 | 584 | |
bcf493af | 585 | my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'sender'); |
e350fb98 DM |
586 | |
587 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to); | |
588 | ||
be4961c2 | 589 | my $filter_text = get_filter_text($rdb->{dbh}, 'sender', $filter); |
7a6e6966 | 590 | |
e350fb98 | 591 | my $query = "SELECT * FROM CStatistic, CReceivers " . |
bcf493af DM |
592 | "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail " . |
593 | "AND NOT direction AND sender != '' AND receiver = ? " . | |
be4961c2 | 594 | $filter_text . |
bcf493af | 595 | "ORDER BY $orderby limit $limit"; |
e350fb98 | 596 | |
bcf493af | 597 | my $sth = $rdb->{dbh}->prepare($query); |
e350fb98 | 598 | |
7a6e6966 | 599 | $sth->execute(encode('UTF-8',$receiver)); |
e350fb98 | 600 | |
bcf493af | 601 | my $res = []; |
e350fb98 | 602 | while (my $ref = $sth->fetchrow_hashref()) { |
7a6e6966 | 603 | push @$res, user_stat_to_perlstring($ref); |
e350fb98 DM |
604 | } |
605 | ||
606 | $sth->finish(); | |
607 | ||
608 | return $res; | |
609 | } | |
610 | ||
611 | sub user_stat_contact { | |
44017d49 | 612 | my ($self, $rdb, $limit, $sorters, $filter, $advfilter) = @_; |
bcf493af | 613 | |
e350fb98 | 614 | my ($from, $to) = $self->timespan(); |
e350fb98 | 615 | |
bcf493af | 616 | my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'contact'); |
e350fb98 | 617 | |
bcf493af DM |
618 | my $cond_good_mail = $self->query_cond_good_mail($from, $to); |
619 | ||
be4961c2 | 620 | my $filter_text = get_filter_text($rdb->{dbh}, 'receiver', $filter); |
7a6e6966 | 621 | |
bcf493af DM |
622 | my $query = "SELECT receiver as contact, count(*) AS count, sum (bytes) AS bytes, " . |
623 | "count (virusinfo) as viruscount " . | |
624 | "FROM CStatistic, CReceivers " . | |
625 | "WHERE cid = cstatistic_cid AND rid = cstatistic_rid " . | |
be4961c2 | 626 | $filter_text . |
bcf493af | 627 | "AND $cond_good_mail AND NOT direction AND sender != '' "; |
e350fb98 | 628 | |
44017d49 | 629 | if ($advfilter) { |
e350fb98 DM |
630 | my $active_workers = $self->query_active_workers (); |
631 | ||
bcf493af | 632 | $query .= "AND receiver NOT IN ($active_workers) "; |
e350fb98 DM |
633 | } |
634 | ||
bcf493af DM |
635 | $query .="GROUP BY contact ORDER BY $orderby limit $limit"; |
636 | my $sth = $rdb->{dbh}->prepare($query); | |
e350fb98 | 637 | |
3d511edd | 638 | $sth->execute(); |
e350fb98 | 639 | |
bcf493af | 640 | my $res = []; |
e350fb98 | 641 | while (my $ref = $sth->fetchrow_hashref()) { |
7a6e6966 | 642 | push @$res, user_stat_to_perlstring($ref); |
e350fb98 DM |
643 | } |
644 | ||
645 | $sth->finish(); | |
646 | ||
647 | return $res; | |
648 | } | |
649 | ||
650 | sub user_stat_sender_details { | |
15a1e62c DM |
651 | my ($self, $rdb, $sender, $limit, $sorters, $filter) = @_; |
652 | ||
e350fb98 | 653 | my ($from, $to) = $self->timespan(); |
15a1e62c | 654 | |
0ab6dc2a | 655 | my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'receiver'); |
15a1e62c | 656 | |
0ab6dc2a | 657 | my $cond_good_mail = $self->query_cond_good_mail($from, $to); |
e350fb98 | 658 | |
be4961c2 | 659 | my $filter_text = get_filter_text($rdb->{dbh}, 'receiver', $filter); |
7a6e6966 | 660 | |
0ab6dc2a | 661 | my $sth = $rdb->{dbh}->prepare( |
15a1e62c DM |
662 | "SELECT " . |
663 | "blocked, bytes, ptime, sender, receiver, spamlevel, time, virusinfo " . | |
664 | "FROM CStatistic, CReceivers " . | |
665 | "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND " . | |
666 | "$cond_good_mail AND NOT direction AND sender = ? " . | |
be4961c2 | 667 | $filter_text . |
15a1e62c DM |
668 | "ORDER BY $orderby limit $limit"); |
669 | ||
7a6e6966 | 670 | $sth->execute(encode('UTF-8',$sender)); |
e350fb98 | 671 | |
15a1e62c | 672 | my $res = []; |
e350fb98 | 673 | while (my $ref = $sth->fetchrow_hashref()) { |
7a6e6966 | 674 | push @$res, user_stat_to_perlstring($ref); |
e350fb98 DM |
675 | } |
676 | ||
677 | $sth->finish(); | |
678 | ||
679 | return $res; | |
680 | } | |
681 | ||
682 | sub user_stat_sender { | |
15a1e62c DM |
683 | my ($self, $rdb, $limit, $sorters, $filter) = @_; |
684 | ||
e350fb98 | 685 | my ($from, $to) = $self->timespan(); |
e350fb98 | 686 | |
0ab6dc2a | 687 | my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'sender'); |
e350fb98 DM |
688 | |
689 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to); | |
690 | ||
be4961c2 | 691 | my $filter_text = get_filter_text($rdb->{dbh}, 'sender', $filter); |
7a6e6966 | 692 | |
bcf493af | 693 | my $query = "SELECT sender,count(*) AS count, sum (bytes) AS bytes, " . |
3d511edd | 694 | "count (virusinfo) as viruscount, " . |
e350fb98 DM |
695 | "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount " . |
696 | "FROM CStatistic WHERE $cond_good_mail AND NOT direction AND sender != '' " . | |
be4961c2 | 697 | $filter_text . |
15a1e62c | 698 | "GROUP BY sender ORDER BY $orderby limit $limit"; |
3d511edd | 699 | |
bcf493af | 700 | my $sth = $rdb->{dbh}->prepare($query); |
3d511edd | 701 | $sth->execute(); |
e350fb98 | 702 | |
15a1e62c | 703 | my $res = []; |
e350fb98 | 704 | while (my $ref = $sth->fetchrow_hashref()) { |
7a6e6966 | 705 | push @$res, user_stat_to_perlstring($ref); |
e350fb98 DM |
706 | } |
707 | ||
708 | $sth->finish(); | |
709 | ||
710 | return $res; | |
711 | } | |
712 | ||
713 | sub user_stat_receiver_details { | |
0ab6dc2a DM |
714 | my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_; |
715 | ||
e350fb98 | 716 | my ($from, $to) = $self->timespan(); |
e350fb98 | 717 | |
0ab6dc2a | 718 | my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'sender'); |
e350fb98 | 719 | |
0ab6dc2a DM |
720 | my $cond_good_mail = $self->query_cond_good_mail($from, $to); |
721 | ||
be4961c2 | 722 | my $filter_text = get_filter_text($rdb->{dbh}, 'sender', $filter); |
7a6e6966 | 723 | |
0ab6dc2a DM |
724 | my $sth = $rdb->{dbh}->prepare( |
725 | "SELECT blocked, bytes, ptime, sender, receiver, spamlevel, time, virusinfo " . | |
726 | "FROM CStatistic, CReceivers " . | |
727 | "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND receiver = ? " . | |
be4961c2 | 728 | $filter_text . |
0ab6dc2a | 729 | "ORDER BY $orderby limit $limit"); |
e350fb98 | 730 | |
7a6e6966 | 731 | $sth->execute(encode('UTF-8',$receiver)); |
e350fb98 | 732 | |
0ab6dc2a | 733 | my $res = []; |
e350fb98 | 734 | while (my $ref = $sth->fetchrow_hashref()) { |
7a6e6966 | 735 | push @$res, user_stat_to_perlstring($ref); |
e350fb98 DM |
736 | } |
737 | ||
738 | $sth->finish(); | |
739 | ||
740 | return $res; | |
741 | } | |
742 | ||
743 | sub user_stat_receiver { | |
44017d49 | 744 | my ($self, $rdb, $limit, $sorters, $filter, $advfilter) = @_; |
0ab6dc2a | 745 | |
e350fb98 | 746 | my ($from, $to) = $self->timespan(); |
e350fb98 | 747 | |
0ab6dc2a | 748 | my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'receiver'); |
e350fb98 DM |
749 | |
750 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to) . " AND " . | |
751 | "receiver IS NOT NULL AND receiver != ''"; | |
752 | ||
be4961c2 | 753 | my $filter_text = get_filter_text($rdb->{dbh}, 'receiver', $filter); |
7a6e6966 | 754 | |
0ab6dc2a DM |
755 | my $query = "SELECT receiver, " . |
756 | "count(*) AS count, " . | |
757 | "sum (bytes) AS bytes, " . | |
758 | "count (virusinfo) as viruscount, " . | |
759 | "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount "; | |
760 | ||
44017d49 | 761 | if ($advfilter) { |
e350fb98 DM |
762 | my $active_workers = $self->query_active_workers (); |
763 | ||
0ab6dc2a DM |
764 | $query .= "FROM CStatistic, CReceivers, ($active_workers) as workers "; |
765 | ||
766 | $query .= "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND worker=receiver "; | |
767 | ||
e350fb98 | 768 | } else { |
0ab6dc2a DM |
769 | $query .= "FROM CStatistic, CReceivers "; |
770 | ||
771 | $query .= "WHERE cid = cstatistic_cid AND rid = cstatistic_rid "; | |
e350fb98 DM |
772 | } |
773 | ||
0ab6dc2a | 774 | $query .= "AND $cond_good_mail and direction " . |
be4961c2 | 775 | $filter_text . |
0ab6dc2a DM |
776 | "GROUP BY receiver ORDER BY $orderby LIMIT $limit"; |
777 | ||
bcf493af | 778 | my $sth = $rdb->{dbh}->prepare($query); |
3d511edd | 779 | $sth->execute(); |
e350fb98 | 780 | |
0ab6dc2a | 781 | my $res = []; |
e350fb98 | 782 | while (my $ref = $sth->fetchrow_hashref()) { |
7a6e6966 | 783 | push @$res, user_stat_to_perlstring($ref); |
e350fb98 DM |
784 | } |
785 | ||
786 | $sth->finish(); | |
787 | ||
788 | return $res; | |
789 | } | |
790 | ||
4681645a DM |
791 | sub postscreen_stat { |
792 | my ($self, $rdb) = @_; | |
793 | ||
794 | my ($from, $to) = $self->localhourspan(); | |
795 | my $timezone = tz_local_offset();; | |
796 | ||
797 | my $cmd = "SELECT " . | |
798 | "sum(rblcount) as rbl_rejects, " . | |
799 | "sum(PregreetCount) as pregreet_rejects " . | |
800 | "FROM LocalStat WHERE time >= $from AND time < $to"; | |
801 | ||
802 | my $sth = $rdb->{dbh}->prepare($cmd); | |
803 | $sth->execute(); | |
804 | my $res = $sth->fetchrow_hashref(); | |
805 | $sth->finish(); | |
806 | ||
807 | return $res; | |
808 | } | |
809 | ||
810 | sub postscreen_stat_graph { | |
2719ef94 DM |
811 | my ($self, $rdb, $span) = @_; |
812 | my $res; | |
813 | ||
814 | my ($from, $to) = $self->localhourspan(); | |
815 | my $timezone = tz_local_offset();; | |
816 | ||
817 | my $cmd = "SELECT " . | |
818 | "(time - $from) / $span AS index, " . | |
980ce907 DM |
819 | "sum(rblcount) as rbl_rejects, " . |
820 | "sum(PregreetCount) as pregreet_rejects " . | |
2719ef94 DM |
821 | "FROM LocalStat WHERE time >= $from AND time < $to " . |
822 | "GROUP BY index ORDER BY index"; | |
823 | ||
824 | my $sth = $rdb->{dbh}->prepare($cmd); | |
825 | $sth->execute (); | |
826 | ||
cb609ca0 | 827 | my $max_entry = int(($to - $from) / $span); |
2719ef94 | 828 | while (my $ref = $sth->fetchrow_hashref()) { |
cb609ca0 WB |
829 | my $i = $ref->{index}; |
830 | $res->[$i] = $ref; | |
831 | $max_entry = $i if $i > $max_entry; | |
2719ef94 DM |
832 | } |
833 | ||
cb609ca0 | 834 | for my $i (0..$max_entry) { |
3b6c2b72 | 835 | $res->[$i] //= { index => $i, rbl_rejects => 0, pregreet_rejects => 0}; |
2719ef94 | 836 | |
3b6c2b72 | 837 | my $d = $res->[$i]; |
3d6870aa | 838 | $d->{time} = $from + $i*$span - $timezone; |
2719ef94 DM |
839 | } |
840 | $sth->finish(); | |
841 | ||
842 | return $res; | |
843 | } | |
844 | ||
fea9d463 DC |
845 | sub recent_mailcount { |
846 | my ($self, $rdb, $span) = @_; | |
847 | my $res; | |
848 | ||
849 | my ($from, $to) = $self->timespan(); | |
850 | ||
851 | my $cmd = "SELECT". | |
852 | "(time - $from) / $span AS index, ". | |
853 | "COUNT (CASE WHEN direction THEN 1 ELSE NULL END) as count_in, ". | |
854 | "COUNT (CASE WHEN NOT direction THEN 1 ELSE NULL END) as count_out, ". | |
855 | "SUM (CASE WHEN direction THEN bytes ELSE 0 END) as bytes_in, ". | |
856 | "SUM (CASE WHEN NOT direction THEN bytes ELSE 0 END) as bytes_out, ". | |
857 | "SUM (ptime) / 1000.0 as ptimesum, ". | |
858 | "COUNT (CASE WHEN virusinfo IS NOT NULL AND direction THEN 1 ELSE NULL END) as virus_in, ". | |
859 | "COUNT (CASE WHEN virusinfo IS NOT NULL AND NOT direction THEN 1 ELSE NULL END) as virus_out, ". | |
860 | "COUNT (CASE WHEN virusinfo IS NULL AND direction AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_in, ". | |
861 | "COUNT (CASE WHEN virusinfo IS NULL AND NOT direction AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_out ". | |
862 | "FROM cstatistic ". | |
e0dc6200 | 863 | "WHERE time >= $from AND time < $to ". |
fea9d463 DC |
864 | "GROUP BY index ORDER BY index"; |
865 | ||
866 | my $sth = $rdb->{dbh}->prepare($cmd); | |
867 | ||
868 | $sth->execute (); | |
869 | ||
870 | while (my $ref = $sth->fetchrow_hashref()) { | |
871 | @$res[$ref->{index}] = $ref; | |
872 | } | |
873 | ||
874 | $sth->finish(); | |
875 | ||
876 | my $c = int(($to - $from) / $span); | |
877 | ||
878 | for (my $i = 0; $i < $c; $i++) { | |
879 | @$res[$i] //= { | |
880 | index => $i, | |
881 | count => 0, count_in => 0, count_out => 0, | |
882 | spam => 0, spam_in => 0, spam_out => 0, | |
883 | virus => 0, virus_in => 0, virus_out => 0, | |
884 | bytes => 0, bytes_in => 0, bytes_out => 0, | |
885 | }; | |
886 | ||
887 | my $d = @$res[$i]; | |
888 | ||
889 | $d->{time} = $from + $i*$span; | |
890 | $d->{count} = $d->{count_in} + $d->{count_out}; | |
891 | $d->{spam} = $d->{spam_in} + $d->{spam_out}; | |
892 | $d->{virus} = $d->{virus_in} + $d->{virus_out}; | |
893 | $d->{bytes} = $d->{bytes_in} + $d->{bytes_out}; | |
894 | $d->{timespan} = $span+0; | |
895 | $d->{ptimesum} += 0; | |
896 | } | |
897 | ||
898 | return $res; | |
899 | } | |
900 | ||
56e1cb1b DC |
901 | sub recent_receivers { |
902 | my ($self, $rdb, $limit) = @_; | |
903 | my $res = []; | |
904 | ||
905 | my ($from, $to) = $self->timespan(); | |
906 | ||
907 | my $cmd = "SELECT ". | |
908 | "COUNT(receiver) as count, receiver ". | |
909 | "FROM CStatistic, CReceivers ". | |
910 | "WHERE time >= ? ". | |
911 | "AND cid = cstatistic_cid ". | |
912 | "AND rid = cstatistic_rid ". | |
913 | "AND blocked = false ". | |
914 | "AND direction = true ". | |
915 | "GROUP BY receiver ORDER BY count DESC LIMIT ?;"; | |
916 | ||
917 | my $sth = $rdb->{dbh}->prepare($cmd); | |
918 | ||
919 | $sth->execute ($from, $limit); | |
56e1cb1b | 920 | while (my $ref = $sth->fetchrow_hashref()) { |
7a6e6966 | 921 | push @$res, user_stat_to_perlstring($ref); |
56e1cb1b DC |
922 | } |
923 | $sth->finish(); | |
924 | ||
925 | return $res; | |
926 | } | |
927 | ||
e350fb98 | 928 | sub traffic_stat_graph { |
2ea3de39 | 929 | my ($self, $rdb, $span) = @_; |
e350fb98 DM |
930 | my $res; |
931 | ||
932 | my ($from, $to) = $self->localhourspan(); | |
e350fb98 DM |
933 | my $timezone = tz_local_offset();; |
934 | ||
2ea3de39 DM |
935 | my $cmd = "SELECT " . |
936 | "(time - $from) / $span AS index, " . | |
937 | "sum(CountIn) as count_in, sum(CountOut) as count_out, " . | |
938 | "sum(VirusIn) as viruscount_in, sum (VirusOut) as viruscount_out, " . | |
939 | "sum(SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount) as spamcount_in, " . | |
940 | "sum(SpamOut) as spamcount_out, " . | |
941 | "sum(BouncesIn) as bounces_in, " . | |
942 | "sum(BouncesOut) as bounces_out " . | |
e350fb98 DM |
943 | "FROM DailyStat WHERE time >= $from AND time < $to " . |
944 | "GROUP BY index ORDER BY index"; | |
945 | ||
946 | my $sth = $rdb->{dbh}->prepare($cmd); | |
3d511edd | 947 | $sth->execute (); |
e350fb98 | 948 | |
59ad663d | 949 | my $max_entry = int(($to - $from) / $span); |
e350fb98 | 950 | while (my $ref = $sth->fetchrow_hashref()) { |
af81e7a0 AP |
951 | my $i = $ref->{index}; |
952 | $res->[$i] = $ref; | |
953 | $max_entry = $i if $i > $max_entry; | |
e350fb98 DM |
954 | } |
955 | ||
af81e7a0 AP |
956 | for my $i (0..$max_entry) { |
957 | $res->[$i] //= { | |
2ea3de39 | 958 | index => $i, |
6156e4b6 | 959 | count => 0, count_in => 0, count_out => 0, |
2ea3de39 DM |
960 | spamcount_in => 0, spamcount_out => 0, |
961 | viruscount_in => 0, viruscount_out => 0, | |
962 | bounces_in => 0, bounces_out => 0 }; | |
963 | ||
af81e7a0 | 964 | my $d = $res->[$i]; |
2ea3de39 | 965 | |
3d6870aa | 966 | $d->{time} = $from + $i*$span - $timezone; |
6156e4b6 | 967 | $d->{count} = $d->{count_in} + $d->{count_out}; |
e350fb98 DM |
968 | } |
969 | $sth->finish(); | |
970 | ||
971 | return $res; | |
972 | } | |
973 | ||
974 | sub traffic_stat_day_dist { | |
f7307874 | 975 | my ($self, $rdb) = @_; |
e350fb98 DM |
976 | my $res; |
977 | ||
978 | my ($from, $to) = $self->localhourspan(); | |
e350fb98 | 979 | |
f7307874 DM |
980 | my $cmd = "SELECT " . |
981 | "((time - $from) / 3600) % 24 AS index, " . | |
982 | "sum(CountIn) as count_in, sum(CountOut) as count_out, " . | |
983 | "sum(VirusIn) as viruscount_in, sum (VirusOut) as viruscount_out, " . | |
984 | "sum(SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount) as spamcount_in, " . | |
985 | "sum(SpamOut) as spamcount_out, " . | |
986 | "sum(BouncesIn) as bounces_in, sum(BouncesOut) as bounces_out " . | |
e350fb98 DM |
987 | "FROM DailyStat WHERE time >= $from AND time < $to " . |
988 | "GROUP BY index ORDER BY index"; | |
3d511edd | 989 | |
e350fb98 DM |
990 | my $sth = $rdb->{dbh}->prepare($cmd); |
991 | ||
3d511edd | 992 | $sth->execute (); |
e350fb98 DM |
993 | |
994 | while (my $ref = $sth->fetchrow_hashref()) { | |
995 | @$res[$ref->{index}] = $ref; | |
996 | } | |
997 | ||
998 | for (my $i = 0; $i < 24; $i++) { | |
f7307874 DM |
999 | @$res[$i] //= { |
1000 | index => $i, | |
1001 | count => 0, count_in => 0, count_out => 0, | |
1002 | spamcount_in => 0, spamcount_out => 0, | |
1003 | viruscount_in => 0, viruscount_out => 0, | |
1004 | bounces_in => 0, bounces_out => 0 }; | |
1005 | ||
1006 | my $d = @$res[$i]; | |
1007 | $d->{count} = $d->{count_in} + $d->{count_out}; | |
e350fb98 DM |
1008 | } |
1009 | $sth->finish(); | |
1010 | ||
1011 | return $res; | |
1012 | } | |
1013 | ||
3d511edd DM |
1014 | sub timespan { |
1015 | my ($self, $from, $to) = @_; | |
1016 | ||
e350fb98 DM |
1017 | if (defined ($from) && defined ($to)) { |
1018 | $self->{from} = $from; | |
1019 | $self->{to} = $to; | |
1020 | } | |
1021 | ||
1022 | return ($self->{from}, $self->{to}); | |
1023 | } | |
1024 | ||
3d511edd | 1025 | sub localdayspan { |
e350fb98 | 1026 | my ($self) = @_; |
3d511edd | 1027 | |
e350fb98 DM |
1028 | my ($from, $to) = $self->timespan(); |
1029 | ||
1030 | my $timezone = tz_local_offset();; | |
955cc90d DM |
1031 | $from = int(($from + $timezone)/86400) * 86400; |
1032 | $to = int(($to + $timezone)/86400) * 86400; | |
e350fb98 DM |
1033 | |
1034 | $to += 86400 if $from == $to; | |
1035 | ||
1036 | return ($from, $to); | |
1037 | } | |
1038 | ||
3d511edd | 1039 | sub localhourspan { |
e350fb98 | 1040 | my ($self) = @_; |
3d511edd | 1041 | |
e350fb98 DM |
1042 | my ($from, $to) = $self->timespan(); |
1043 | ||
1044 | my $timezone = tz_local_offset();; | |
955cc90d DM |
1045 | $from = int(($from + $timezone)/3600) * 3600; |
1046 | $to = int(($to + $timezone)/3600) * 3600; | |
e350fb98 DM |
1047 | |
1048 | $to += 3600 if $from == $to; | |
1049 | ||
1050 | return ($from, $to); | |
1051 | } | |
1052 | ||
e350fb98 DM |
1053 | |
1054 | 1; |