X-Git-Url: https://git.proxmox.com/?a=blobdiff_plain;f=PMG%2FStatistic.pm;h=c2b103fbdfdae70e57096cd845af55bf49e07b26;hb=cb609ca098823734dde590fcf42164f72bbfbf37;hp=b3065d912c03f82dc40ff43a7f8af0a0185dbc97;hpb=5414dee4ced64c18994cc1bdb9d09b606d0d1794;p=pmg-api.git diff --git a/PMG/Statistic.pm b/PMG/Statistic.pm index b3065d9..c2b103f 100755 --- a/PMG/Statistic.pm +++ b/PMG/Statistic.pm @@ -2,30 +2,29 @@ package PMG::Statistic; use strict; use warnings; -use Carp; use DBI; use Time::Local; use Time::Zone; use PVE::SafeSyslog; +use PMG::ClusterConfig; use PMG::RuleDB; sub new { - my ($self, $start, $end, $advanced) = @_; - + my ($self, $start, $end) = @_; + $self = {}; - + bless($self); - if ((defined($start))&&(defined($end))) { - $self->timespan ($start, $end); + if (defined($start) && defined($end)) { + $self->timespan($start, $end); } else { - $self->timespan (time, time - 24*3600); + my $ctime = time(); + $self->timespan($ctime, $ctime - 24*3600); } - $self->{adv} = $advanced; - return $self; } @@ -34,16 +33,18 @@ sub clear_stats { eval { $dbh->begin_work; - + $dbh->do ("LOCK TABLE StatInfo"); $dbh->do ("LOCK TABLE ClusterInfo"); $dbh->do ("DELETE FROM Statinfo"); $dbh->do ("DELETE FROM DailyStat"); + $dbh->do ("DELETE FROM LocalStat"); $dbh->do ("DELETE FROM DomainStat"); $dbh->do ("DELETE FROM VirusInfo"); $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_DomainStat'"); $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_DailyStat'"); + $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_LocalStat'"); $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_VirusInfo'"); $dbh->commit; @@ -51,7 +52,7 @@ sub clear_stats { if ($@) { $dbh->rollback; die $@; - } + } } sub update_stats_generic { @@ -114,7 +115,7 @@ sub update_stats_generic { $dbh->do("INSERT INTO StatInfo VALUES ('$statinfoid', $endid)"); } - COMMIT: + COMMIT: $dbh->commit; }; @@ -129,8 +130,8 @@ sub update_stats_generic { sub update_stats_dailystat { my ($dbh, $cinfo) = @_; - my $role = $cinfo->{local}->{role}; - return 0 if !(($role eq '-') || ($role eq 'M')); + my $role = $cinfo->{local}->{type} // '-'; + return 0 if !(($role eq '-') || ($role eq 'master')); my $select = "SELECT sub.*, dailystat.time IS NOT NULL as exists FROM " . "(SELECT COUNT (CASE WHEN direction THEN 1 ELSE NULL END) as count_in, " . @@ -181,15 +182,15 @@ sub update_stats_dailystat { my $insert = sub { my $ref = shift; - my $sql = "INSERT INTO dailystat " . + my $sql = "INSERT INTO dailystat " . "(Time,CountIn,CountOut,BytesIn,BytesOut,VirusIn,VirusOut,SpamIn,SpamOut," . "BouncesIn,BouncesOut,GreylistCount,SPFCount,RBLCount,PTimeSum,Mtime) " . - "VALUES ($ref->{hour}," . ($ref->{count_in} || 0) . ',' . ($ref->{count_out} || 0) . ',' . + "VALUES ($ref->{hour}," . ($ref->{count_in} || 0) . ',' . ($ref->{count_out} || 0) . ',' . ($ref->{bytes_in} || 0) . ',' . ($ref->{bytes_out} || 0) . ',' . ($ref->{virus_in} || 0) . ',' . ($ref->{virus_out} || 0) . ',' . ($ref->{spam_in} || 0) . ',' . ($ref->{spam_out} || 0) . ',' . ($ref->{bounces_in} || 0) . ',' . ($ref->{bounces_out} || 0) . ',' . - ($ref->{glcount} || 0) . ',' . ($ref->{spfcount} || 0) . ',0,' . ($ref->{ptimesum} || 0) . + ($ref->{glcount} || 0) . ',' . ($ref->{spfcount} || 0) . ',0,' . ($ref->{ptimesum} || 0) . ",EXTRACT(EPOCH FROM now()));"; return $sql; @@ -202,8 +203,8 @@ sub update_stats_dailystat { sub update_stats_domainstat_in { my ($dbh, $cinfo) = @_; - my $role = $cinfo->{local}->{role}; - return 0 if !(($role eq '-') || ($role eq 'M')); + my $role = $cinfo->{local}->{type} // '-'; + return 0 if !(($role eq '-') || ($role eq 'master')); my $sub1 = "select distinct cstatistic_cid, cstatistic_rid, " . "lower(substring(receiver from position ('\@' in receiver) + 1)) as domain, " . @@ -211,7 +212,7 @@ sub update_stats_domainstat_in { "from CStatistic, CReceivers where cid = cstatistic_cid AND rid = cstatistic_rid AND " . "id >= __startid__ and id < __endid__ AND direction " . "group by cstatistic_cid, cstatistic_rid, day, domain"; - + my $select = "SELECT sub.*, domainstat.time IS NOT NULL as exists FROM " . "(SELECT day, domain, COUNT (id) as count_in, SUM (bytes) / (1024.0*1024) as bytes_in, " . @@ -235,7 +236,7 @@ sub update_stats_domainstat_in { push @values, "BouncesIn = BouncesIn + $ref->{bounces_in}" if $ref->{bounces_in}; push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum}; push @values, "MTime = EXTRACT(EPOCH FROM now())"; - + if (scalar (@values)) { $sql .= "UPDATE domainstat SET "; $sql .= join (',', @values); @@ -247,15 +248,15 @@ sub update_stats_domainstat_in { my $insert = sub { my $ref = shift; - my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain}) . ',' . - ($ref->{count_in} || 0) . ',0,' . + my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain}) . ',' . + ($ref->{count_in} || 0) . ',0,' . ($ref->{bytes_in} || 0) . ',0,' . ($ref->{virus_in} || 0) . ',0,' . ($ref->{spam_in} || 0) . ',0,' . - ($ref->{bounces_in} || 0) . ',0,' . + ($ref->{bounces_in} || 0) . ',0,' . ($ref->{ptimesum} || 0) . ",EXTRACT(EPOCH FROM now()));"; - + return $sql; }; @@ -266,8 +267,8 @@ sub update_stats_domainstat_in { sub update_stats_domainstat_out { my ($dbh, $cinfo) = @_; - my $role = $cinfo->{local}->{role}; - return 0 if !(($role eq '-') || ($role eq 'M')); + my $role = $cinfo->{local}->{type} // '-'; + return 0 if !(($role eq '-') || ($role eq 'master')); my $select = "SELECT sub.*, domainstat.time IS NOT NULL as exists FROM " . "(SELECT COUNT (ID) as count_out, SUM (bytes) / (1024.0*1024) as bytes_out, " . @@ -293,7 +294,7 @@ sub update_stats_domainstat_out { push @values, "BouncesOut = BouncesOut + $ref->{bounces_out}" if $ref->{bounces_out}; push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum}; push @values, "MTime = EXTRACT(EPOCH FROM now())"; - + if (scalar (@values)) { $sql .= "UPDATE domainstat SET "; $sql .= join (',', @values); @@ -306,12 +307,12 @@ sub update_stats_domainstat_out { my $ref = shift; my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain}) . - ',0,' . ($ref->{count_out} || 0) . + ',0,' . ($ref->{count_out} || 0) . ',0,' . ($ref->{bytes_out} || 0) . ',0,' . ($ref->{virus_out} || 0) . - ',0,' . ($ref->{spam_out} || 0) . - ',0,' . ($ref->{bounces_out} || 0) . - ','. ($ref->{ptimesum} || 0) . + ',0,' . ($ref->{spam_out} || 0) . + ',0,' . ($ref->{bounces_out} || 0) . + ','. ($ref->{ptimesum} || 0) . ",EXTRACT(EPOCH FROM now()));"; return $sql; @@ -324,8 +325,8 @@ sub update_stats_domainstat_out { sub update_stats_virusinfo { my ($dbh, $cinfo) = @_; - my $role = $cinfo->{local}->{role}; - return 0 if !(($role eq '-') || ($role eq 'M')); + my $role = $cinfo->{local}->{type} // '-'; + return 0 if !(($role eq '-') || ($role eq 'master')); my $select = "SELECT sub.*, virusinfo.time IS NOT NULL as exists FROM " . "(SELECT ((cstatistic.time + __timezone__) / 86400) * 86400 as day, " . @@ -341,7 +342,7 @@ sub update_stats_virusinfo { push @values, "Count = Count + $ref->{count}" if $ref->{count}; push @values, "MTime = EXTRACT(EPOCH FROM now())"; - + if (scalar (@values)) { $sql .= "UPDATE VirusInfo SET "; $sql .= join (',', @values); @@ -354,7 +355,7 @@ sub update_stats_virusinfo { my $ref = shift; my $sql .= "INSERT INTO VirusInfo values ($ref->{day}, " . $dbh->quote($ref->{name}) . - ',' . ($ref->{count} || 0) . + ',' . ($ref->{count} || 0) . ",EXTRACT(EPOCH FROM now()));"; return $sql; @@ -378,77 +379,60 @@ sub total_mail_stat { my ($self, $rdb) = @_; my ($from, $to) = $self->localdayspan(); - + my ($sth, $ref); my $glcount = 0; # this is to slow for high volume sites # $sth = $rdb->{dbh}->prepare("SELECT COUNT(DISTINCT Instance) AS GL FROM CGreylist " . # "WHERE passed = 0 AND rctime >= ? AND rctime < ? "); -# $sth->execute($from, $to); +# $sth->execute($from, $to); # $ref = $sth->fetchrow_hashref(); # $glcount = $ref->{gl}; my $cmds = "SELECT sum(CountIn) + $glcount AS count_in, sum(CountOut) AS count_out, " . - "sum (VirusIn) AS viruscount_in, sum (VirusOut) AS viruscount_out, " . - "sum (SpamIn) AS spamcount_in, sum (SpamOut) AS spamcount_out, " . - "sum (BytesIn) AS traffic_in, sum (BytesOut) AS traffic_out, " . - "sum (BouncesIn) AS bounces_in, sum (BouncesOut) AS bounces_out, " . + "sum (VirusIn) AS viruscount_in, sum (VirusOut) AS viruscount_out, " . + "sum (SpamIn) AS spamcount_in, sum (SpamOut) AS spamcount_out, " . + "sum (BytesIn)*1024*1024 AS bytes_in, sum (BytesOut)*1024*1024 AS bytes_out, " . + "sum (BouncesIn) AS bounces_in, sum (BouncesOut) AS bounces_out, " . "sum (GreylistCount) + $glcount as glcount, " . "sum (SPFCount) as spfcount, " . - "sum (RBLCount) as rblcount, " . + "sum (RBLCount) as rbl_rejects, " . "sum(PTimeSum)/(sum(CountIn) + $glcount + sum(CountOut)) AS avptime " . "FROM DailyStat where time >= $from and time < $to"; $sth = $rdb->{dbh}->prepare($cmds); - $sth->execute(); + $sth->execute(); $ref = $sth->fetchrow_hashref(); $sth->finish(); + foreach my $k (keys %$ref) { $ref->{$k} += 0; } # convert to numbers + if (!$ref->{avptime}) { - $ref->{count_in} = $ref->{count_out} = $ref->{viruscount_in} = $ref->{viruscount_out} = - $ref->{spamcount_in} = $ref->{spamcount_out} = $ref->{glcount} = $ref->{spfcount} = - $ref->{rblcount} = $ref->{bounces_in} = $ref->{bounces_out} = $ref->{traffic_in} = - $ref->{traffic_out} = $ref->{avptime} = 0; + $ref->{count_in} = $ref->{count_out} = $ref->{viruscount_in} = $ref->{viruscount_out} = + $ref->{spamcount_in} = $ref->{spamcount_out} = $ref->{glcount} = $ref->{spfcount} = + $ref->{rbl_rejects} = $ref->{bounces_in} = $ref->{bounces_out} = $ref->{bytes_in} = + $ref->{bytes_out} = $ref->{avptime} = 0; } $ref->{count} = $ref->{count_in} + $ref->{count_out}; - $ref->{count_in_per} = $ref->{count} > 0 ? ($ref->{count_in} * 100)/$ref->{count} : 0; - $ref->{count_out_per} = 100 - $ref->{count_in_per}; - - $ref->{viruscount_in_per} = $ref->{count_in} > 0 ? ($ref->{viruscount_in} * 100)/$ref->{count_in} : 0; - $ref->{viruscount_out_per} = $ref->{count_out} > 0 ? ($ref->{viruscount_out} * 100)/$ref->{count_out} : 0; - - $ref->{spamcount_in_per} = $ref->{count_in} > 0 ? ($ref->{spamcount_in} * 100)/$ref->{count_in} : 0; - $ref->{spamcount_out_per} = $ref->{count_out} > 0 ? ($ref->{spamcount_out} * 100)/$ref->{count_out} : 0; - - $ref->{bounces_in_per} = $ref->{count_in} > 0 ? ($ref->{bounces_in} * 100)/$ref->{count_in} : 0; - $ref->{bounces_out_per} = $ref->{count_out} > 0 ? ($ref->{bounces_out} * 100)/$ref->{count_out} : 0; - - $ref->{glcount_per} = $ref->{count_in} > 0 ? ($ref->{glcount} * 100)/$ref->{count_in} : 0; - $ref->{spfcount_per} = $ref->{count_in} > 0 ? ($ref->{spfcount} * 100)/$ref->{count_in} : 0; - $ref->{rblcount_per} = $ref->{count_in} > 0 ? ($ref->{rblcount} * 100)/$ref->{count_in} : 0; - - $ref->{junk_in} = $ref->{viruscount_in} + $ref->{spamcount_in} + $ref->{glcount} + - $ref->{spfcount} + $ref->{rblcount}; + $ref->{junk_in} = $ref->{viruscount_in} + $ref->{spamcount_in} + $ref->{glcount} + + $ref->{spfcount} + $ref->{rbl_rejects}; $ref->{junk_out} = $ref->{viruscount_out} + $ref->{spamcount_out}; - $ref->{junk_in_per} = $ref->{count_in} > 0 ? ($ref->{junk_in} * 100)/$ref->{count_in} : 0; - $ref->{junk_out_per} = $ref->{count_out} > 0 ? ($ref->{junk_out} * 100)/$ref->{count_out} : 0; - return $ref; } sub total_spam_stat { my ($self, $rdb) = @_; my ($from, $to) = $self->timespan(); - + my $sth = $rdb->{dbh}->prepare("SELECT spamlevel, COUNT(spamlevel) AS count FROM CStatistic " . - "WHERE virusinfo IS NULL and time >= ? AND time < ? AND ptime > 0 AND spamlevel > 0 " . + "WHERE virusinfo IS NULL and time >= ? AND time < ? AND ptime > 0 AND spamlevel > 0 " . "GROUP BY spamlevel ORDER BY spamlevel LIMIT 10"); - $sth->execute($from, $to); + $sth->execute($from, $to); my $res = $sth->fetchall_arrayref({}); @@ -461,19 +445,19 @@ sub total_virus_stat { my ($self, $rdb, $order) = @_; my ($from, $to) = $self->localdayspan(); - + $order = "count" if !$order; my @oa = split (',', $order); $order = join (' DESC, ', @oa); $order .= ' DESC'; - + my $sth = $rdb->{dbh}->prepare("SELECT Name, SUM (Count) as count FROM VirusInfo " . - "WHERE time >= ? AND time < ? " . + "WHERE time >= ? AND time < ? " . "GROUP BY name ORDER BY $order, name"); - $sth->execute($from, $to); + $sth->execute($from, $to); my $res = $sth->fetchall_arrayref({}); @@ -486,31 +470,28 @@ sub rule_count { my ($self, $rdb) = @_; my $sth = $rdb->{dbh}->prepare("SELECT id, name, count from rule order by count desc, name"); - $sth->execute(); - + $sth->execute(); + my $res = $sth->fetchall_arrayref({}); $sth->finish(); - return $res; + return $res; } sub total_domain_stat { - my ($self, $rdb, $orderby) = @_; - - $orderby || ($orderby = 'domain'); - my $sortdir = sort_dir ($orderby); + my ($self, $rdb) = @_; my ($from, $to) = $self->localdayspan(); my $query = "SELECT domain, SUM (CountIn) AS count_in, SUM (CountOut) AS count_out," . - "SUM (BytesIn) AS bytes_in, SUM (BytesOut) AS bytes_out, " . - "SUM (VirusIn) AS virus_in, SUM (VirusOut) AS virus_out," . - "SUM (SpamIn) as spam_in, SUM (SpamOut) as spam_out " . + "SUM (BytesIn)*1024*1024 AS bytes_in, SUM (BytesOut)*1024*1024 AS bytes_out, " . + "SUM (VirusIn) AS viruscount_in, SUM (VirusOut) AS viruscount_out," . + "SUM (SpamIn) as spamcount_in, SUM (SpamOut) as spamcount_out " . "FROM DomainStat where time >= $from AND time < $to " . - "GROUP BY domain ORDER BY $orderby $sortdir, domain ASC"; + "GROUP BY domain ORDER BY domain ASC"; my $sth = $rdb->{dbh}->prepare($query); - $sth->execute(); + $sth->execute(); my $res = $sth->fetchall_arrayref({}); @@ -537,40 +518,53 @@ sub query_cond_good_mail { sub query_active_workers { my ($self) = @_; my ($from, $to) = $self->timespan(); - + my $start = $from - (3600*24)*90; # from - 90 days my $cond_good_mail = $self->query_cond_good_mail ($start, $to); return "SELECT DISTINCT sender as worker FROM CStatistic WHERE $cond_good_mail AND NOT direction"; } -sub sort_dir { - my ($orderby) = @_; +my $compute_sql_orderby = sub { + my ($sorters, $sort_default, $sort_always_prop) = @_; - my $sortdir = ($orderby eq "virusinfo" || $orderby eq 'sender' || $orderby eq 'domain' || $orderby eq 'receiver') ? 'ASC' : 'DESC'; + my $has_default_sort; - return $sortdir; -} + my $orderby = ''; + + foreach my $obj (@$sorters) { + $has_default_sort = 1 if $obj->{property} eq $sort_always_prop; + $orderby .= ', ' if $orderby; + $orderby .= "$obj->{property} $obj->{direction}" + } + + $orderby .= $sort_default if !$orderby; + + $orderby .= ", $sort_always_prop" if !$has_default_sort; + + return $orderby; +}; sub user_stat_contact_details { - my ($self, $rdb, $receiver, $limit, $orderby) = @_; + my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_; + my ($from, $to) = $self->timespan(); - my $sth; - my $res; - $orderby || ($orderby = 'time'); - my $sortdir = sort_dir ($orderby); + my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'sender'); my $cond_good_mail = $self->query_cond_good_mail ($from, $to); my $query = "SELECT * FROM CStatistic, CReceivers " . - "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND NOT direction AND sender != '' AND receiver = ? " . - "ORDER BY $orderby $sortdir, receiver limit $limit"; + "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail " . + "AND NOT direction AND sender != '' AND receiver = ? " . + ($filter ? "AND sender like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . + "ORDER BY $orderby limit $limit"; - $sth = $rdb->{dbh}->prepare($query); + my $sth = $rdb->{dbh}->prepare($query); - $sth->execute ($receiver); + $sth->execute($receiver); + my $res = []; while (my $ref = $sth->fetchrow_hashref()) { push @$res, $ref; } @@ -581,36 +575,33 @@ sub user_stat_contact_details { } sub user_stat_contact { - my ($self, $rdb, $limit, $orderby) = @_; + my ($self, $rdb, $limit, $sorters, $filter, $advfilter) = @_; + my ($from, $to) = $self->timespan(); - my $sth; - my $res; - my $query; - $orderby || ($orderby = 'count'); - my $sortdir = sort_dir ($orderby); + my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'contact'); - my $cond_good_mail = $self->query_cond_good_mail ($from, $to); + my $cond_good_mail = $self->query_cond_good_mail($from, $to); + + my $query = "SELECT receiver as contact, count(*) AS count, sum (bytes) AS bytes, " . + "count (virusinfo) as viruscount " . + "FROM CStatistic, CReceivers " . + "WHERE cid = cstatistic_cid AND rid = cstatistic_rid " . + ($filter ? "AND receiver like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . + "AND $cond_good_mail AND NOT direction AND sender != '' "; - if ($self->{adv}) { + if ($advfilter) { my $active_workers = $self->query_active_workers (); - $query = "SELECT receiver, count(*) AS count, sum (bytes) AS bytes " . - "FROM CStatistic, CReceivers WHERE cid = cstatistic_cid AND rid = cstatistic_rid " . - "AND $cond_good_mail AND NOT direction AND sender != '' AND " . - "receiver NOT IN ($active_workers) " . - "GROUP BY receiver ORDER BY $orderby $sortdir, receiver limit $limit"; - } else { - $query = "SELECT receiver, count(*) AS count, sum (bytes) AS bytes " . - "FROM CStatistic, CReceivers WHERE cid = cstatistic_cid AND rid = cstatistic_rid " . - "AND $cond_good_mail AND NOT direction AND sender != '' " . - "GROUP BY receiver ORDER BY $orderby $sortdir, receiver limit $limit"; + $query .= "AND receiver NOT IN ($active_workers) "; } - $sth = $rdb->{dbh}->prepare($query); + $query .="GROUP BY contact ORDER BY $orderby limit $limit"; + my $sth = $rdb->{dbh}->prepare($query); - $sth->execute(); + $sth->execute(); + my $res = []; while (my $ref = $sth->fetchrow_hashref()) { push @$res, $ref; } @@ -621,21 +612,26 @@ sub user_stat_contact { } sub user_stat_sender_details { - my ($self, $rdb, $sender, $limit, $orderby) = @_; + my ($self, $rdb, $sender, $limit, $sorters, $filter) = @_; + my ($from, $to) = $self->timespan(); - my $sth; - my $res; - $orderby || ($orderby = 'time'); - my $sortdir = sort_dir ($orderby); + my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'receiver'); - my $cond_good_mail = $self->query_cond_good_mail ($from, $to); + my $cond_good_mail = $self->query_cond_good_mail($from, $to); + + my $sth = $rdb->{dbh}->prepare( + "SELECT " . + "blocked, bytes, ptime, sender, receiver, spamlevel, time, virusinfo " . + "FROM CStatistic, CReceivers " . + "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND " . + "$cond_good_mail AND NOT direction AND sender = ? " . + ($filter ? "AND receiver like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . + "ORDER BY $orderby limit $limit"); - $sth = $rdb->{dbh}->prepare("SELECT * FROM CStatistic, CReceivers WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND " . - "$cond_good_mail AND NOT direction AND sender = ? " . - "ORDER BY $orderby $sortdir, receiver limit $limit"); - $sth->execute($sender); + $sth->execute($sender); + my $res = []; while (my $ref = $sth->fetchrow_hashref()) { push @$res, $ref; } @@ -646,26 +642,25 @@ sub user_stat_sender_details { } sub user_stat_sender { - my ($self, $rdb, $limit, $orderby) = @_; + my ($self, $rdb, $limit, $sorters, $filter) = @_; + my ($from, $to) = $self->timespan(); - my $sth; - my $res; - my $query; - $orderby || ($orderby = 'count'); - my $sortdir = sort_dir ($orderby); + my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'sender'); my $cond_good_mail = $self->query_cond_good_mail ($from, $to); - $query = "SELECT sender,count(*) AS count, sum (bytes) AS bytes, " . - "count (virusinfo) as viruscount, " . + my $query = "SELECT sender,count(*) AS count, sum (bytes) AS bytes, " . + "count (virusinfo) as viruscount, " . "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount " . "FROM CStatistic WHERE $cond_good_mail AND NOT direction AND sender != '' " . - "GROUP BY sender ORDER BY $orderby $sortdir, sender limit $limit"; - - $sth = $rdb->{dbh}->prepare($query); - $sth->execute(); + ($filter ? "AND sender like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . + "GROUP BY sender ORDER BY $orderby limit $limit"; + + my $sth = $rdb->{dbh}->prepare($query); + $sth->execute(); + my $res = []; while (my $ref = $sth->fetchrow_hashref()) { push @$res, $ref; } @@ -676,21 +671,24 @@ sub user_stat_sender { } sub user_stat_receiver_details { - my ($self, $rdb, $receiver, $limit, $orderby) = @_; + my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_; + my ($from, $to) = $self->timespan(); - my $sth; - my $res; - $orderby || ($orderby = 'time'); - my $sortdir = sort_dir ($orderby); + my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'sender'); - my $cond_good_mail = $self->query_cond_good_mail ($from, $to); + my $cond_good_mail = $self->query_cond_good_mail($from, $to); + + my $sth = $rdb->{dbh}->prepare( + "SELECT blocked, bytes, ptime, sender, receiver, spamlevel, time, virusinfo " . + "FROM CStatistic, CReceivers " . + "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND receiver = ? " . + ($filter ? "AND sender like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . + "ORDER BY $orderby limit $limit"); - $sth = $rdb->{dbh}->prepare("SELECT * FROM CStatistic, CReceivers " . - "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND receiver = ? " . - "ORDER BY $orderby $sortdir, sender limit $limit"); - $sth->execute($receiver); + $sth->execute($receiver); + my $res = []; while (my $ref = $sth->fetchrow_hashref()) { push @$res, $ref; } @@ -701,41 +699,42 @@ sub user_stat_receiver_details { } sub user_stat_receiver { - my ($self, $rdb, $limit, $orderby) = @_; + my ($self, $rdb, $limit, $sorters, $filter, $advfilter) = @_; + my ($from, $to) = $self->timespan(); - my $sth; - my $res; - my $query; - $orderby || ($orderby = 'count'); - my $sortdir = sort_dir ($orderby); + my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'receiver'); my $cond_good_mail = $self->query_cond_good_mail ($from, $to) . " AND " . "receiver IS NOT NULL AND receiver != ''"; - if ($self->{adv}) { + my $query = "SELECT receiver, " . + "count(*) AS count, " . + "sum (bytes) AS bytes, " . + "count (virusinfo) as viruscount, " . + "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount "; + + if ($advfilter) { my $active_workers = $self->query_active_workers (); - $query = "SELECT receiver, count(*) AS count, sum (bytes) AS bytes, " . - "count (virusinfo) as viruscount, " . - "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount " . - "FROM CStatistic, CReceivers, ($active_workers) as workers " . - "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND direction AND worker=receiver " . - "GROUP BY receiver " . - "ORDER BY $orderby $sortdir, receiver LIMIT $limit"; + $query .= "FROM CStatistic, CReceivers, ($active_workers) as workers "; + + $query .= "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND worker=receiver "; + } else { - $query = "SELECT receiver, count(*) AS count, sum (bytes) AS bytes, " . - "count (virusinfo) as viruscount, " . - "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount " . - "FROM CStatistic, CReceivers " . - "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail and direction " . - "GROUP BY receiver " . - "ORDER BY $orderby $sortdir, receiver LIMIT $limit"; + $query .= "FROM CStatistic, CReceivers "; + + $query .= "WHERE cid = cstatistic_cid AND rid = cstatistic_rid "; } - $sth = $rdb->{dbh}->prepare($query); - $sth->execute(); + $query .= "AND $cond_good_mail and direction " . + ($filter ? "AND receiver like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') . + "GROUP BY receiver ORDER BY $orderby LIMIT $limit"; + + my $sth = $rdb->{dbh}->prepare($query); + $sth->execute(); + my $res = []; while (my $ref = $sth->fetchrow_hashref()) { push @$res, $ref; } @@ -745,24 +744,165 @@ sub user_stat_receiver { return $res; } -sub traffic_stat_graph { - my ($self, $rdb, $span, $dir) = @_; +sub postscreen_stat { + my ($self, $rdb) = @_; + + my ($from, $to) = $self->localhourspan(); + my $timezone = tz_local_offset();; + + my $cmd = "SELECT " . + "sum(rblcount) as rbl_rejects, " . + "sum(PregreetCount) as pregreet_rejects " . + "FROM LocalStat WHERE time >= $from AND time < $to"; + + my $sth = $rdb->{dbh}->prepare($cmd); + $sth->execute(); + my $res = $sth->fetchrow_hashref(); + $sth->finish(); + + return $res; +} + +sub postscreen_stat_graph { + my ($self, $rdb, $span) = @_; my $res; my ($from, $to) = $self->localhourspan(); - my $p = $dir ? "In" : "Out"; my $timezone = tz_local_offset();; - my $spam = $dir ? "sum (SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount)" : "sum (SpamOut)"; + my $cmd = "SELECT " . + "(time - $from) / $span AS index, " . + "sum(rblcount) as rbl_rejects, " . + "sum(PregreetCount) as pregreet_rejects " . + "FROM LocalStat WHERE time >= $from AND time < $to " . + "GROUP BY index ORDER BY index"; + + my $sth = $rdb->{dbh}->prepare($cmd); + $sth->execute (); + + my $max_entry = int(($to - $from) / $span); + while (my $ref = $sth->fetchrow_hashref()) { + my $i = $ref->{index}; + $res->[$i] = $ref; + $max_entry = $i if $i > $max_entry; + } + + for my $i (0..$max_entry) { + $res->[$i] //= { index => $i, rbl_rejects => 0, pregreet_rejects => 0}; + + my $d = $res->[$i]; + $d->{time} = $from + $i*$span - $timezone; + } + $sth->finish(); + + return $res; +} + +sub recent_mailcount { + my ($self, $rdb, $span) = @_; + my $res; + + my ($from, $to) = $self->timespan(); + + my $cmd = "SELECT". + "(time - $from) / $span AS index, ". + "COUNT (CASE WHEN direction THEN 1 ELSE NULL END) as count_in, ". + "COUNT (CASE WHEN NOT direction THEN 1 ELSE NULL END) as count_out, ". + "SUM (CASE WHEN direction THEN bytes ELSE 0 END) as bytes_in, ". + "SUM (CASE WHEN NOT direction THEN bytes ELSE 0 END) as bytes_out, ". + "SUM (ptime) / 1000.0 as ptimesum, ". + "COUNT (CASE WHEN virusinfo IS NOT NULL AND direction THEN 1 ELSE NULL END) as virus_in, ". + "COUNT (CASE WHEN virusinfo IS NOT NULL AND NOT direction THEN 1 ELSE NULL END) as virus_out, ". + "COUNT (CASE WHEN virusinfo IS NULL AND direction AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_in, ". + "COUNT (CASE WHEN virusinfo IS NULL AND NOT direction AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_out ". + "FROM cstatistic ". + "WHERE time >= $from AND time < $to ". + "GROUP BY index ORDER BY index"; + + my $sth = $rdb->{dbh}->prepare($cmd); + + $sth->execute (); + + while (my $ref = $sth->fetchrow_hashref()) { + @$res[$ref->{index}] = $ref; + } + + $sth->finish(); + + my $c = int(($to - $from) / $span); + + for (my $i = 0; $i < $c; $i++) { + @$res[$i] //= { + index => $i, + count => 0, count_in => 0, count_out => 0, + spam => 0, spam_in => 0, spam_out => 0, + virus => 0, virus_in => 0, virus_out => 0, + bytes => 0, bytes_in => 0, bytes_out => 0, + }; + + my $d = @$res[$i]; + + $d->{time} = $from + $i*$span; + $d->{count} = $d->{count_in} + $d->{count_out}; + $d->{spam} = $d->{spam_in} + $d->{spam_out}; + $d->{virus} = $d->{virus_in} + $d->{virus_out}; + $d->{bytes} = $d->{bytes_in} + $d->{bytes_out}; + $d->{timespan} = $span+0; + $d->{ptimesum} += 0; + } - my $cmd = "SELECT sum(Count$p) as count, (time - $from) / $span AS index, " . - "sum (Virus$p) as viruscount, $spam as spamcount, sum (Bounces$p) as bounces " . + return $res; +} + +sub recent_receivers { + my ($self, $rdb, $limit) = @_; + my $res = []; + + my ($from, $to) = $self->timespan(); + + my $cmd = "SELECT ". + "COUNT(receiver) as count, receiver ". + "FROM CStatistic, CReceivers ". + "WHERE time >= ? ". + "AND cid = cstatistic_cid ". + "AND rid = cstatistic_rid ". + "AND blocked = false ". + "AND direction = true ". + "GROUP BY receiver ORDER BY count DESC LIMIT ?;"; + + my $sth = $rdb->{dbh}->prepare($cmd); + + $sth->execute ($from, $limit); + + while (my $ref = $sth->fetchrow_hashref()) { + push @$res, $ref; + } + $sth->finish(); + + return $res; +} + +sub traffic_stat_graph { + my ($self, $rdb, $span) = @_; + my $res; + + my ($from, $to) = $self->localhourspan(); + my $timezone = tz_local_offset();; + + my $cmd = "SELECT " . + "(time - $from) / $span AS index, " . + "sum(CountIn) as count_in, sum(CountOut) as count_out, " . + "sum(VirusIn) as viruscount_in, sum (VirusOut) as viruscount_out, " . + "sum(SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount) as spamcount_in, " . + "sum(SpamOut) as spamcount_out, " . + "sum(BouncesIn) as bounces_in, " . + "sum(BouncesOut) as bounces_out " . "FROM DailyStat WHERE time >= $from AND time < $to " . "GROUP BY index ORDER BY index"; my $sth = $rdb->{dbh}->prepare($cmd); - $sth->execute (); + $sth->execute (); while (my $ref = $sth->fetchrow_hashref()) { @$res[$ref->{index}] = $ref; @@ -771,9 +911,17 @@ sub traffic_stat_graph { my $c = int (($to - $from) / $span); for (my $i = 0; $i < $c; $i++) { - my $eref = {count => 0, index => $i, spamcount => 0, viruscount => 0, bounces => 0}; - @$res[$i] = $eref if !@$res[$i]; - @$res[$i]->{time} = $from + ($i+1)*$span - $timezone; + @$res[$i] //= { + index => $i, + count => 0, count_in => 0, count_out => 0, + spamcount_in => 0, spamcount_out => 0, + viruscount_in => 0, viruscount_out => 0, + bounces_in => 0, bounces_out => 0 }; + + my $d = @$res[$i]; + + $d->{time} = $from + $i*$span - $timezone; + $d->{count} = $d->{count_in} + $d->{count_out}; } $sth->finish(); @@ -781,39 +929,48 @@ sub traffic_stat_graph { } sub traffic_stat_day_dist { - my ($self, $rdb, $dir) = @_; + my ($self, $rdb) = @_; my $res; my ($from, $to) = $self->localhourspan(); - my $p = $dir ? "In" : "Out"; - my $spam = $dir ? "sum (SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount)" : "sum (SpamOut)"; - - my $cmd = "SELECT sum(Count$p) as count, ((time - $from) / 3600) % 24 AS index, " . - "sum (Virus$p) as viruscount, $spam as spamcount, sum (Bounces$p) as bounces " . + my $cmd = "SELECT " . + "((time - $from) / 3600) % 24 AS index, " . + "sum(CountIn) as count_in, sum(CountOut) as count_out, " . + "sum(VirusIn) as viruscount_in, sum (VirusOut) as viruscount_out, " . + "sum(SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount) as spamcount_in, " . + "sum(SpamOut) as spamcount_out, " . + "sum(BouncesIn) as bounces_in, sum(BouncesOut) as bounces_out " . "FROM DailyStat WHERE time >= $from AND time < $to " . "GROUP BY index ORDER BY index"; - + my $sth = $rdb->{dbh}->prepare($cmd); - $sth->execute (); + $sth->execute (); while (my $ref = $sth->fetchrow_hashref()) { @$res[$ref->{index}] = $ref; } for (my $i = 0; $i < 24; $i++) { - my $eref = {count => 0, index => $i, spamcount => 0, viruscount => 0, bounces => 0}; - @$res[$i] = $eref if !@$res[$i]; + @$res[$i] //= { + index => $i, + count => 0, count_in => 0, count_out => 0, + spamcount_in => 0, spamcount_out => 0, + viruscount_in => 0, viruscount_out => 0, + bounces_in => 0, bounces_out => 0 }; + + my $d = @$res[$i]; + $d->{count} = $d->{count_in} + $d->{count_out}; } $sth->finish(); return $res; } -sub timespan { - my ($self, $from, $to) = @_; - +sub timespan { + my ($self, $from, $to) = @_; + if (defined ($from) && defined ($to)) { $self->{from} = $from; $self->{to} = $to; @@ -822,28 +979,28 @@ sub timespan { return ($self->{from}, $self->{to}); } -sub localdayspan { +sub localdayspan { my ($self) = @_; - + my ($from, $to) = $self->timespan(); my $timezone = tz_local_offset();; - $from = (($from + $timezone)/86400) * 86400; - $to = (($to + $timezone)/86400) * 86400; + $from = int(($from + $timezone)/86400) * 86400; + $to = int(($to + $timezone)/86400) * 86400; $to += 86400 if $from == $to; return ($from, $to); } -sub localhourspan { +sub localhourspan { my ($self) = @_; - + my ($from, $to) = $self->timespan(); my $timezone = tz_local_offset();; - $from = (($from + $timezone)/3600) * 3600; - $to = (($to + $timezone)/3600) * 3600; + $from = int(($from + $timezone)/3600) * 3600; + $to = int(($to + $timezone)/3600) * 3600; $to += 3600 if $from == $to;