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