1 package PMG
::Statistic
;
11 use PMG
::ClusterConfig
;
15 my ($self, $start, $end) = @_;
21 if (defined($start) && defined($end)) {
22 $self->timespan($start, $end);
25 $self->timespan($ctime, $ctime - 24*3600);
37 $dbh->do ("LOCK TABLE StatInfo");
38 $dbh->do ("LOCK TABLE ClusterInfo");
40 $dbh->do ("DELETE FROM Statinfo");
41 $dbh->do ("DELETE FROM DailyStat");
42 $dbh->do ("DELETE FROM LocalStat");
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'");
47 $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_LocalStat'");
48 $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_VirusInfo'");
58 sub update_stats_generic
{
59 my ($dbh, $statinfoid, $select, $update, $insert) = @_;
62 my $maxentries = 100000;
68 $dbh->do("LOCK TABLE StatInfo IN EXCLUSIVE MODE");
70 my $sth = $dbh->prepare("SELECT last_value FROM cstatistic_id_seq");
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);
77 $sth = $dbh->prepare("SELECT ivalue as value FROM StatInfo WHERE NAME = '$statinfoid'");
79 my $statinfo = $sth->fetchrow_hashref();
81 my $startid = $statinfo ?
$statinfo->{value
} : 0;
82 goto COMMIT
if $startid > $last_value;
84 my $endid = $startid + $maxentries;
85 $endid = $last_value + 1 if $endid > $last_value;
86 $todo = $last_value + 1 - $endid;
88 my $timezone = tz_local_offset
();;
90 $select =~ s/__timezone__/$timezone/g;
91 $select =~ s/__startid__/$startid/g;
92 $select =~ s/__endid__/$endid/g;
94 $sth = $dbh->prepare($select);
98 #print "TEST:$last_value:$endid:$todo\n";
100 while (my $ref = $sth->fetchrow_hashref()) {
101 if ($ref->{exists}) {
102 $cmd .= &$update($ref);
104 $cmd .= &$insert($ref);
108 $dbh->do ($cmd) if $cmd;
113 $dbh->do("UPDATE StatInfo SET ivalue = $endid WHERE NAME = '$statinfoid'");
115 $dbh->do("INSERT INTO StatInfo VALUES ('$statinfoid', $endid)");
130 sub update_stats_dailystat
{
131 my ($dbh, $cinfo) = @_;
133 my $role = $cinfo->{local}->{type
} // '-';
134 return 0 if !(($role eq '-') || ($role eq 'master'));
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)";
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())";
174 if (scalar (@values)) {
175 $sql .= "UPDATE dailystat SET ";
176 $sql .= join (',', @values);
177 $sql .= " WHERE time = $ref->{hour};";
185 my $sql = "INSERT INTO dailystat " .
186 "(Time,CountIn,CountOut,BytesIn,BytesOut,VirusIn,VirusOut,SpamIn,SpamOut," .
187 "BouncesIn,BouncesOut,GreylistCount,SPFCount,RBLCount,PTimeSum,Mtime) " .
188 "VALUES ($ref->{hour}," . ($ref->{count_in
} || 0) . ',' . ($ref->{count_out
} || 0) . ',' .
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) . ',' .
193 ($ref->{glcount
} || 0) . ',' . ($ref->{spfcount
} || 0) . ',0,' . ($ref->{ptimesum
} || 0) .
194 ",EXTRACT(EPOCH FROM now()));";
199 return update_stats_generic
($dbh, 'dailystat_index', $select, $update, $insert);
203 sub update_stats_domainstat_in
{
204 my ($dbh, $cinfo) = @_;
206 my $role = $cinfo->{local}->{type
} // '-';
207 return 0 if !(($role eq '-') || ($role eq 'master'));
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";
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)";
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())";
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
}) . ';';
251 my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain
}) . ',' .
252 ($ref->{count_in
} || 0) . ',0,' .
253 ($ref->{bytes_in
} || 0) . ',0,' .
254 ($ref->{virus_in
} || 0) . ',0,' .
255 ($ref->{spam_in
} || 0) . ',0,' .
256 ($ref->{bounces_in
} || 0) . ',0,' .
257 ($ref->{ptimesum
} || 0) .
258 ",EXTRACT(EPOCH FROM now()));";
263 update_stats_generic
($dbh, 'domainstat_in_index', $select, $update, $insert);
267 sub update_stats_domainstat_out
{
268 my ($dbh, $cinfo) = @_;
270 my $role = $cinfo->{local}->{type
} // '-';
271 return 0 if !(($role eq '-') || ($role eq 'master'));
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)";
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())";
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
}) . ';';
309 my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain
}) .
310 ',0,' . ($ref->{count_out
} || 0) .
311 ',0,' . ($ref->{bytes_out
} || 0) .
312 ',0,' . ($ref->{virus_out
} || 0) .
313 ',0,' . ($ref->{spam_out
} || 0) .
314 ',0,' . ($ref->{bounces_out
} || 0) .
315 ','. ($ref->{ptimesum
} || 0) .
316 ",EXTRACT(EPOCH FROM now()));";
321 update_stats_generic
($dbh, 'domainstat_out_index', $select, $update, $insert);
325 sub update_stats_virusinfo
{
326 my ($dbh, $cinfo) = @_;
328 my $role = $cinfo->{local}->{type
} // '-';
329 return 0 if !(($role eq '-') || ($role eq 'master'));
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)";
343 push @values, "Count = Count + $ref->{count}" if $ref->{count
};
344 push @values, "MTime = EXTRACT(EPOCH FROM now())";
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
}) . ';';
357 my $sql .= "INSERT INTO VirusInfo values ($ref->{day}, " . $dbh->quote($ref->{name
}) .
358 ',' . ($ref->{count
} || 0) .
359 ",EXTRACT(EPOCH FROM now()));";
364 update_stats_generic
($dbh, 'virusinfo_index', $select, $update, $insert);
370 my ($dbh, $cinfo) = @_;
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) {};
378 sub total_mail_stat
{
379 my ($self, $rdb) = @_;
381 my ($from, $to) = $self->localdayspan();
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 < ? ");
389 # $sth->execute($from, $to);
390 # $ref = $sth->fetchrow_hashref();
391 # $glcount = $ref->{gl};
393 my $cmds = "SELECT sum(CountIn) + $glcount AS count_in, sum(CountOut) AS count_out, " .
394 "sum (VirusIn) AS viruscount_in, sum (VirusOut) AS viruscount_out, " .
395 "sum (SpamIn) AS spamcount_in, sum (SpamOut) AS spamcount_out, " .
396 "sum (BytesIn) AS bytes_in, sum (BytesOut) AS bytes_out, " .
397 "sum (BouncesIn) AS bounces_in, sum (BouncesOut) AS bounces_out, " .
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";
404 $sth = $rdb->{dbh
}->prepare($cmds);
406 $ref = $sth->fetchrow_hashref();
409 foreach my $k (keys %$ref) { $ref->{$k} += 0; } # convert to numbers
411 if (!$ref->{avptime
}) {
412 $ref->{count_in
} = $ref->{count_out
} = $ref->{viruscount_in
} = $ref->{viruscount_out
} =
413 $ref->{spamcount_in
} = $ref->{spamcount_out
} = $ref->{glcount
} = $ref->{spfcount
} =
414 $ref->{rblcount
} = $ref->{bounces_in
} = $ref->{bounces_out
} = $ref->{bytes_in
} =
415 $ref->{bytes_out
} = $ref->{avptime
} = 0;
418 $ref->{count
} = $ref->{count_in
} + $ref->{count_out
};
420 $ref->{junk_in
} = $ref->{viruscount_in
} + $ref->{spamcount_in
} + $ref->{glcount
} +
421 $ref->{spfcount
} + $ref->{rblcount
};
423 $ref->{junk_out
} = $ref->{viruscount_out
} + $ref->{spamcount_out
};
428 sub total_spam_stat
{
429 my ($self, $rdb) = @_;
430 my ($from, $to) = $self->timespan();
432 my $sth = $rdb->{dbh
}->prepare("SELECT spamlevel, COUNT(spamlevel) AS count FROM CStatistic " .
433 "WHERE virusinfo IS NULL and time >= ? AND time < ? AND ptime > 0 AND spamlevel > 0 " .
434 "GROUP BY spamlevel ORDER BY spamlevel LIMIT 10");
435 $sth->execute($from, $to);
437 my $res = $sth->fetchall_arrayref({});
444 sub total_virus_stat
{
445 my ($self, $rdb, $order) = @_;
447 my ($from, $to) = $self->localdayspan();
449 $order = "count" if !$order;
451 my @oa = split (',', $order);
453 $order = join (' DESC, ', @oa);
456 my $sth = $rdb->{dbh
}->prepare("SELECT Name, SUM (Count) as count FROM VirusInfo " .
457 "WHERE time >= ? AND time < ? " .
458 "GROUP BY name ORDER BY $order, name");
460 $sth->execute($from, $to);
462 my $res = $sth->fetchall_arrayref({});
470 my ($self, $rdb) = @_;
472 my $sth = $rdb->{dbh
}->prepare("SELECT id, name, count from rule order by count desc, name");
475 my $res = $sth->fetchall_arrayref({});
481 sub total_domain_stat
{
482 my ($self, $rdb) = @_;
484 my ($from, $to) = $self->localdayspan();
486 my $query = "SELECT domain, SUM (CountIn) AS count_in, SUM (CountOut) AS count_out," .
487 "SUM (BytesIn) AS mbytes_in, SUM (BytesOut) AS mbytes_out, " .
488 "SUM (VirusIn) AS viruscount_in, SUM (VirusOut) AS viruscount_out," .
489 "SUM (SpamIn) as spamcount_in, SUM (SpamOut) as spamcount_out " .
490 "FROM DomainStat where time >= $from AND time < $to " .
491 "GROUP BY domain ORDER BY domain ASC";
493 my $sth = $rdb->{dbh
}->prepare($query);
496 my $res = $sth->fetchall_arrayref({});
503 sub clear_rule_count
{
504 my ($self, $rdb, $id) = @_;
507 $rdb->{dbh
}->do ("UPDATE rule set count = 0 where id = ?", undef, $id);
509 $rdb->{dbh
}->do("UPDATE rule set count = 0");
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";
518 sub query_active_workers
{
520 my ($from, $to) = $self->timespan();
522 my $start = $from - (3600*24)*90; # from - 90 days
523 my $cond_good_mail = $self->query_cond_good_mail ($start, $to);
525 return "SELECT DISTINCT sender as worker FROM CStatistic WHERE $cond_good_mail AND NOT direction";
528 my $compute_sql_orderby = sub {
529 my ($sorters, $sort_default, $sort_always_prop) = @_;
531 my $has_default_sort;
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}"
541 $orderby .= $sort_default if !$orderby;
543 $orderby .= ", $sort_always_prop" if !$has_default_sort;
548 sub user_stat_contact_details
{
549 my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_;
551 my ($from, $to) = $self->timespan();
553 my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'sender');
555 my $cond_good_mail = $self->query_cond_good_mail ($from, $to);
557 my $query = "SELECT * FROM CStatistic, CReceivers " .
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";
563 my $sth = $rdb->{dbh
}->prepare($query);
565 $sth->execute($receiver);
568 while (my $ref = $sth->fetchrow_hashref()) {
577 sub user_stat_contact
{
578 my ($self, $rdb, $limit, $sorters, $filter, $advfilter) = @_;
580 my ($from, $to) = $self->timespan();
582 my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'contact');
584 my $cond_good_mail = $self->query_cond_good_mail($from, $to);
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 != '' ";
594 my $active_workers = $self->query_active_workers ();
596 $query .= "AND receiver NOT IN ($active_workers) ";
599 $query .="GROUP BY contact ORDER BY $orderby limit $limit";
600 my $sth = $rdb->{dbh
}->prepare($query);
605 while (my $ref = $sth->fetchrow_hashref()) {
614 sub user_stat_sender_details
{
615 my ($self, $rdb, $sender, $limit, $sorters, $filter) = @_;
617 my ($from, $to) = $self->timespan();
619 my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'receiver');
621 my $cond_good_mail = $self->query_cond_good_mail($from, $to);
623 my $sth = $rdb->{dbh
}->prepare(
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");
632 $sth->execute($sender);
635 while (my $ref = $sth->fetchrow_hashref()) {
644 sub user_stat_sender
{
645 my ($self, $rdb, $limit, $sorters, $filter) = @_;
647 my ($from, $to) = $self->timespan();
649 my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'sender');
651 my $cond_good_mail = $self->query_cond_good_mail ($from, $to);
653 my $query = "SELECT sender,count(*) AS count, sum (bytes) AS bytes, " .
654 "count (virusinfo) as viruscount, " .
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 != '' " .
657 ($filter ?
"AND sender like " . $rdb->{dbh
}->quote("%${filter}%") . ' ' : '') .
658 "GROUP BY sender ORDER BY $orderby limit $limit";
660 my $sth = $rdb->{dbh
}->prepare($query);
664 while (my $ref = $sth->fetchrow_hashref()) {
673 sub user_stat_receiver_details
{
674 my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_;
676 my ($from, $to) = $self->timespan();
678 my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'sender');
680 my $cond_good_mail = $self->query_cond_good_mail($from, $to);
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");
689 $sth->execute($receiver);
692 while (my $ref = $sth->fetchrow_hashref()) {
701 sub user_stat_receiver
{
702 my ($self, $rdb, $limit, $sorters, $filter, $advfilter) = @_;
704 my ($from, $to) = $self->timespan();
706 my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'receiver');
708 my $cond_good_mail = $self->query_cond_good_mail ($from, $to) . " AND " .
709 "receiver IS NOT NULL AND receiver != ''";
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 ";
718 my $active_workers = $self->query_active_workers ();
720 $query .= "FROM CStatistic, CReceivers, ($active_workers) as workers ";
722 $query .= "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND worker=receiver ";
725 $query .= "FROM CStatistic, CReceivers ";
727 $query .= "WHERE cid = cstatistic_cid AND rid = cstatistic_rid ";
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";
734 my $sth = $rdb->{dbh
}->prepare($query);
738 while (my $ref = $sth->fetchrow_hashref()) {
747 sub rbl_count_stats
{
748 my ($self, $rdb, $span) = @_;
751 my ($from, $to) = $self->localhourspan();
752 my $timezone = tz_local_offset
();;
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";
760 my $sth = $rdb->{dbh
}->prepare($cmd);
763 while (my $ref = $sth->fetchrow_hashref()) {
764 @$res[$ref->{index}] = $ref;
767 my $c = int (($to - $from) / $span);
769 for (my $i = 0; $i < $c; $i++) {
770 @$res[$i] //= { index => $i, count
=> 0, };
773 $d->{time} = $from + ($i+1)*$span - $timezone;
780 sub traffic_stat_graph
{
781 my ($self, $rdb, $span) = @_;
784 my ($from, $to) = $self->localhourspan();
785 my $timezone = tz_local_offset
();;
787 my $cmd = "SELECT " .
788 "(time - $from) / $span AS index, " .
789 "sum(CountIn) as count_in, sum(CountOut) as count_out, " .
790 "sum(VirusIn) as viruscount_in, sum (VirusOut) as viruscount_out, " .
791 "sum(SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount) as spamcount_in, " .
792 "sum(SpamOut) as spamcount_out, " .
793 "sum(BouncesIn) as bounces_in, " .
794 "sum(BouncesOut) as bounces_out " .
795 "FROM DailyStat WHERE time >= $from AND time < $to " .
796 "GROUP BY index ORDER BY index";
798 my $sth = $rdb->{dbh
}->prepare($cmd);
802 while (my $ref = $sth->fetchrow_hashref()) {
803 @$res[$ref->{index}] = $ref;
806 my $c = int (($to - $from) / $span);
808 for (my $i = 0; $i < $c; $i++) {
811 count
=> 0, count_in
=> 0, count_out
=> 0,
812 spamcount_in
=> 0, spamcount_out
=> 0,
813 viruscount_in
=> 0, viruscount_out
=> 0,
814 bounces_in
=> 0, bounces_out
=> 0 };
818 $d->{time} = $from + ($i+1)*$span - $timezone;
819 $d->{count
} = $d->{count_in
} + $d->{count_out
};
826 sub traffic_stat_day_dist
{
827 my ($self, $rdb) = @_;
830 my ($from, $to) = $self->localhourspan();
832 my $cmd = "SELECT " .
833 "((time - $from) / 3600) % 24 AS index, " .
834 "sum(CountIn) as count_in, sum(CountOut) as count_out, " .
835 "sum(VirusIn) as viruscount_in, sum (VirusOut) as viruscount_out, " .
836 "sum(SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount) as spamcount_in, " .
837 "sum(SpamOut) as spamcount_out, " .
838 "sum(BouncesIn) as bounces_in, sum(BouncesOut) as bounces_out " .
839 "FROM DailyStat WHERE time >= $from AND time < $to " .
840 "GROUP BY index ORDER BY index";
842 my $sth = $rdb->{dbh
}->prepare($cmd);
846 while (my $ref = $sth->fetchrow_hashref()) {
847 @$res[$ref->{index}] = $ref;
850 for (my $i = 0; $i < 24; $i++) {
853 count
=> 0, count_in
=> 0, count_out
=> 0,
854 spamcount_in
=> 0, spamcount_out
=> 0,
855 viruscount_in
=> 0, viruscount_out
=> 0,
856 bounces_in
=> 0, bounces_out
=> 0 };
859 $d->{count
} = $d->{count_in
} + $d->{count_out
};
867 my ($self, $from, $to) = @_;
869 if (defined ($from) && defined ($to)) {
870 $self->{from
} = $from;
874 return ($self->{from
}, $self->{to
});
880 my ($from, $to) = $self->timespan();
882 my $timezone = tz_local_offset
();;
883 $from = (($from + $timezone)/86400) * 86400;
884 $to = (($to + $timezone)/86400) * 86400;
886 $to += 86400 if $from == $to;
894 my ($from, $to) = $self->timespan();
896 my $timezone = tz_local_offset
();;
897 $from = (($from + $timezone)/3600) * 3600;
898 $to = (($to + $timezone)/3600) * 3600;
900 $to += 3600 if $from == $to;