src/PMG/Statistic.pm

   1 package PMG::Statistic;
   2
   3 use strict;
   4 use warnings;
   5 use DBI;
   6 use Time::Local;
   7 use Time::Zone;
   8
   9 use PVE::SafeSyslog;
  10
  11 use PMG::ClusterConfig;
  12 use PMG::RuleDB;
  13
  14 sub new {
  15     my ($self, $start, $end) = @_;
  16
  17     $self = {};
  18
  19     bless($self);
  20
  21     if (defined($start) && defined($end)) {
  22         $self->timespan($start, $end);
  23     } else {
  24         my $ctime = time();
  25         $self->timespan($ctime, $ctime - 24*3600);
  26     }
  27
  28     return $self;
  29 }
  30
  31 sub clear_stats {
  32     my ($dbh) = @_;
  33
  34     eval {
  35         $dbh->begin_work;
  36
  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");
  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'");
  49
  50         $dbh->commit;
  51     };
  52     if ($@) {
  53         $dbh->rollback;
  54         die $@;
  55     }
  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
 118       COMMIT:
 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
 133     my $role = $cinfo->{local}->{type} // '-';
 134     return 0 if !(($role eq '-') || ($role eq 'master'));
 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
 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()));";
 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
 206     my $role = $cinfo->{local}->{type} // '-';
 207     return 0 if !(($role eq '-') || ($role eq 'master'));
 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";
 215
 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())";
 239
 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
 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()));";
 259
 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
 270     my $role = $cinfo->{local}->{type} // '-';
 271     return 0 if !(($role eq '-') || ($role eq 'master'));
 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())";
 297
 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}) .
 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()));";
 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
 328     my $role = $cinfo->{local}->{type} // '-';
 329     return 0 if !(($role eq '-') || ($role eq 'master'));
 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())";
 345
 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}) .
 358             ',' . ($ref->{count} || 0) .
 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();
 382
 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 < ? ");
 389 #    $sth->execute($from, $to);
 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, " .
 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)*1024*1024 AS bytes_in, sum (BytesOut)*1024*1024 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 rbl_rejects, " .
 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);
 405     $sth->execute();
 406     $ref = $sth->fetchrow_hashref();
 407     $sth->finish();
 408
 409     foreach my $k (keys %$ref) { $ref->{$k} += 0; } # convert to numbers
 410
 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->{rbl_rejects} = $ref->{bounces_in} = $ref->{bounces_out} = $ref->{bytes_in} =
 415             $ref->{bytes_out} = $ref->{avptime} = 0;
 416     }
 417
 418     $ref->{count} = $ref->{count_in} + $ref->{count_out};
 419
 420     $ref->{junk_in} = $ref->{viruscount_in} + $ref->{spamcount_in} + $ref->{glcount} +
 421         $ref->{spfcount} + $ref->{rbl_rejects};
 422
 423     $ref->{junk_out} = $ref->{viruscount_out} + $ref->{spamcount_out};
 424
 425     return $ref;
 426 }
 427
 428 sub total_spam_stat {
 429     my ($self, $rdb) = @_;
 430     my ($from, $to) = $self->timespan();
 431
 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");
 435     $sth->execute($from, $to);
 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();
 448
 449     $order = "count" if !$order;
 450
 451     my @oa = split (',', $order);
 452
 453     $order = join (' DESC, ', @oa);
 454     $order .= ' DESC';
 455
 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");
 459
 460     $sth->execute($from, $to);
 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");
 473     $sth->execute();
 474
 475     my $res = $sth->fetchall_arrayref({});
 476     $sth->finish();
 477
 478     return $res;
 479 }
 480
 481 sub total_domain_stat {
 482     my ($self, $rdb) = @_;
 483
 484     my ($from, $to) = $self->localdayspan();
 485
 486     my $query = "SELECT domain, SUM (CountIn) AS count_in, SUM (CountOut) AS count_out," .
 487         "SUM (BytesIn)*1024*1024 AS bytes_in, SUM (BytesOut)*1024*1024 AS bytes_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";
 492
 493     my $sth = $rdb->{dbh}->prepare($query);
 494     $sth->execute();
 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();
 521
 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
 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
 548 sub user_stat_contact_details {
 549     my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_;
 550
 551     my ($from, $to) = $self->timespan();
 552
 553     my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'sender');
 554
 555     my $cond_good_mail = $self->query_cond_good_mail ($from, $to);
 556
 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";
 562
 563     my $sth = $rdb->{dbh}->prepare($query);
 564
 565     $sth->execute($receiver);
 566
 567     my $res = [];
 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 {
 578     my ($self, $rdb, $limit, $sorters, $filter, $advfilter) = @_;
 579
 580     my ($from, $to) = $self->timespan();
 581
 582     my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'contact');
 583
 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 != '' ";
 592
 593     if ($advfilter) {
 594         my $active_workers = $self->query_active_workers ();
 595
 596         $query .= "AND receiver NOT IN ($active_workers) ";
 597     }
 598
 599     $query .="GROUP BY contact ORDER BY $orderby limit $limit";
 600     my $sth = $rdb->{dbh}->prepare($query);
 601
 602     $sth->execute();
 603
 604     my $res = [];
 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 {
 615     my ($self, $rdb, $sender, $limit, $sorters, $filter) = @_;
 616
 617     my ($from, $to) = $self->timespan();
 618
 619     my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'receiver');
 620
 621     my $cond_good_mail = $self->query_cond_good_mail($from, $to);
 622
 623     my $sth = $rdb->{dbh}->prepare(
 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
 632     $sth->execute($sender);
 633
 634     my $res = [];
 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 {
 645     my ($self, $rdb, $limit, $sorters, $filter) = @_;
 646
 647     my ($from, $to) = $self->timespan();
 648
 649     my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'sender');
 650
 651     my $cond_good_mail = $self->query_cond_good_mail ($from, $to);
 652
 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";
 659
 660     my $sth = $rdb->{dbh}->prepare($query);
 661     $sth->execute();
 662
 663     my $res = [];
 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 {
 674     my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_;
 675
 676     my ($from, $to) = $self->timespan();
 677
 678     my $orderby = $compute_sql_orderby->($sorters, 'time ASC', 'sender');
 679
 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");
 688
 689     $sth->execute($receiver);
 690
 691     my $res = [];
 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 {
 702     my ($self, $rdb, $limit, $sorters, $filter, $advfilter) = @_;
 703
 704     my ($from, $to) = $self->timespan();
 705
 706     my $orderby = $compute_sql_orderby->($sorters, 'count DESC', 'receiver');
 707
 708     my $cond_good_mail = $self->query_cond_good_mail ($from, $to) . " AND " .
 709         "receiver IS NOT NULL AND receiver != ''";
 710
 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
 717     if ($advfilter) {
 718         my $active_workers = $self->query_active_workers ();
 719
 720         $query .= "FROM CStatistic, CReceivers, ($active_workers) as workers ";
 721
 722         $query .= "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND worker=receiver ";
 723
 724     } else {
 725         $query .= "FROM CStatistic, CReceivers ";
 726
 727         $query .= "WHERE cid = cstatistic_cid AND rid = cstatistic_rid ";
 728     }
 729
 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
 734     my $sth = $rdb->{dbh}->prepare($query);
 735     $sth->execute();
 736
 737     my $res = [];
 738     while (my $ref = $sth->fetchrow_hashref()) {
 739         push @$res, $ref;
 740     }
 741
 742     $sth->finish();
 743
 744     return $res;
 745 }
 746
 747 sub postscreen_stat {
 748     my ($self, $rdb) = @_;
 749
 750     my ($from, $to) = $self->localhourspan();
 751     my $timezone = tz_local_offset();;
 752
 753     my $cmd = "SELECT " .
 754         "sum(rblcount) as rbl_rejects, " .
 755         "sum(PregreetCount) as pregreet_rejects " .
 756         "FROM LocalStat WHERE time >= $from AND time < $to";
 757
 758     my $sth = $rdb->{dbh}->prepare($cmd);
 759     $sth->execute();
 760     my $res = $sth->fetchrow_hashref();
 761     $sth->finish();
 762
 763     return $res;
 764 }
 765
 766 sub postscreen_stat_graph {
 767     my ($self, $rdb, $span) = @_;
 768     my $res;
 769
 770     my ($from, $to) = $self->localhourspan();
 771     my $timezone = tz_local_offset();;
 772
 773     my $cmd = "SELECT " .
 774         "(time - $from) / $span AS index, " .
 775         "sum(rblcount) as rbl_rejects, " .
 776         "sum(PregreetCount) as pregreet_rejects " .
 777         "FROM LocalStat WHERE time >= $from AND time < $to " .
 778         "GROUP BY index ORDER BY index";
 779
 780     my $sth = $rdb->{dbh}->prepare($cmd);
 781     $sth->execute ();
 782
 783     my $max_entry = int(($to - $from) / $span);
 784     while (my $ref = $sth->fetchrow_hashref()) {
 785         my $i = $ref->{index};
 786         $res->[$i] = $ref;
 787         $max_entry = $i if $i > $max_entry;
 788     }
 789
 790     for my $i (0..$max_entry) {
 791         $res->[$i] //= { index => $i, rbl_rejects => 0, pregreet_rejects => 0};
 792
 793         my $d = $res->[$i];
 794         $d->{time} = $from + $i*$span - $timezone;
 795     }
 796     $sth->finish();
 797
 798     return $res;
 799 }
 800
 801 sub recent_mailcount {
 802     my ($self, $rdb, $span) = @_;
 803     my $res;
 804
 805     my ($from, $to) = $self->timespan();
 806
 807     my $cmd = "SELECT".
 808         "(time - $from) / $span AS index, ".
 809         "COUNT (CASE WHEN direction THEN 1 ELSE NULL END) as count_in, ".
 810         "COUNT (CASE WHEN NOT direction THEN 1 ELSE NULL END) as count_out, ".
 811         "SUM (CASE WHEN direction THEN bytes ELSE 0 END) as bytes_in, ".
 812         "SUM (CASE WHEN NOT direction THEN bytes ELSE 0 END) as bytes_out, ".
 813         "SUM (ptime) / 1000.0 as ptimesum, ".
 814         "COUNT (CASE WHEN virusinfo IS NOT NULL AND direction THEN 1 ELSE NULL END) as virus_in, ".
 815         "COUNT (CASE WHEN virusinfo IS NOT NULL AND NOT direction THEN 1 ELSE NULL END) as virus_out, ".
 816         "COUNT (CASE WHEN virusinfo IS NULL AND direction AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_in, ".
 817         "COUNT (CASE WHEN virusinfo IS NULL AND NOT direction AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_out ".
 818         "FROM cstatistic ".
 819         "WHERE time >= $from AND time < $to ".
 820         "GROUP BY index ORDER BY index";
 821
 822     my $sth =  $rdb->{dbh}->prepare($cmd);
 823
 824     $sth->execute ();
 825
 826     while (my $ref = $sth->fetchrow_hashref()) {
 827         @$res[$ref->{index}] = $ref;
 828     }
 829
 830     $sth->finish();
 831
 832     my $c = int(($to - $from) / $span);
 833
 834     for (my $i = 0; $i < $c; $i++) {
 835         @$res[$i] //= {
 836             index => $i,
 837             count => 0, count_in => 0, count_out => 0,
 838             spam => 0, spam_in => 0, spam_out => 0,
 839             virus => 0, virus_in => 0, virus_out => 0,
 840             bytes => 0, bytes_in => 0, bytes_out => 0,
 841             };
 842
 843         my $d = @$res[$i];
 844
 845         $d->{time} = $from + $i*$span;
 846         $d->{count} = $d->{count_in} + $d->{count_out};
 847         $d->{spam} = $d->{spam_in} + $d->{spam_out};
 848         $d->{virus} = $d->{virus_in} + $d->{virus_out};
 849         $d->{bytes} = $d->{bytes_in} + $d->{bytes_out};
 850         $d->{timespan} = $span+0;
 851         $d->{ptimesum} += 0;
 852     }
 853
 854     return $res;
 855 }
 856
 857 sub recent_receivers {
 858     my ($self, $rdb, $limit) = @_;
 859     my $res = [];
 860
 861     my ($from, $to) = $self->timespan();
 862
 863     my $cmd = "SELECT ".
 864         "COUNT(receiver) as count, receiver ".
 865         "FROM CStatistic, CReceivers ".
 866         "WHERE time >= ? ".
 867         "AND cid = cstatistic_cid ".
 868         "AND rid = cstatistic_rid ".
 869         "AND blocked = false ".
 870         "AND direction = true ".
 871         "GROUP BY receiver ORDER BY count DESC LIMIT ?;";
 872
 873     my $sth =  $rdb->{dbh}->prepare($cmd);
 874
 875     $sth->execute ($from, $limit);
 876
 877     while (my $ref = $sth->fetchrow_hashref()) {
 878         push @$res, $ref;
 879     }
 880     $sth->finish();
 881
 882     return $res;
 883 }
 884
 885 sub traffic_stat_graph {
 886     my ($self, $rdb, $span) = @_;
 887     my $res;
 888
 889     my ($from, $to) = $self->localhourspan();
 890     my $timezone = tz_local_offset();;
 891
 892     my $cmd = "SELECT " .
 893         "(time - $from) / $span AS index, " .
 894         "sum(CountIn) as count_in, sum(CountOut) as count_out, " .
 895         "sum(VirusIn) as viruscount_in, sum (VirusOut) as viruscount_out, " .
 896         "sum(SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount) as spamcount_in, " .
 897         "sum(SpamOut) as spamcount_out, " .
 898         "sum(BouncesIn) as bounces_in, " .
 899         "sum(BouncesOut) as bounces_out " .
 900         "FROM DailyStat WHERE time >= $from AND time < $to " .
 901         "GROUP BY index ORDER BY index";
 902
 903     my $sth =  $rdb->{dbh}->prepare($cmd);
 904     $sth->execute ();
 905
 906     my $max_entry = int(($to - $from) / $span);
 907     while (my $ref = $sth->fetchrow_hashref()) {
 908         my $i = $ref->{index};
 909         $res->[$i] = $ref;
 910         $max_entry = $i if $i > $max_entry;
 911     }
 912
 913     for my $i (0..$max_entry) {
 914         $res->[$i] //= {
 915             index => $i,
 916             count => 0, count_in => 0, count_out => 0,
 917             spamcount_in => 0, spamcount_out => 0,
 918             viruscount_in => 0, viruscount_out => 0,
 919             bounces_in => 0, bounces_out => 0 };
 920
 921         my $d = $res->[$i];
 922
 923         $d->{time} = $from + $i*$span - $timezone;
 924         $d->{count} = $d->{count_in} + $d->{count_out};
 925     }
 926     $sth->finish();
 927
 928     return $res;
 929 }
 930
 931 sub traffic_stat_day_dist {
 932     my ($self, $rdb) = @_;
 933     my $res;
 934
 935     my ($from, $to) = $self->localhourspan();
 936
 937     my $cmd = "SELECT " .
 938         "((time - $from) / 3600) % 24 AS index, " .
 939         "sum(CountIn) as count_in, sum(CountOut) as count_out, " .
 940         "sum(VirusIn) as viruscount_in, sum (VirusOut) as viruscount_out, " .
 941         "sum(SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount) as spamcount_in, " .
 942         "sum(SpamOut) as spamcount_out, " .
 943         "sum(BouncesIn) as bounces_in, sum(BouncesOut) as bounces_out " .
 944         "FROM DailyStat WHERE time >= $from AND time < $to " .
 945         "GROUP BY index ORDER BY index";
 946
 947     my $sth =  $rdb->{dbh}->prepare($cmd);
 948
 949     $sth->execute ();
 950
 951     while (my $ref = $sth->fetchrow_hashref()) {
 952         @$res[$ref->{index}] = $ref;
 953     }
 954
 955     for (my $i = 0; $i < 24; $i++) {
 956         @$res[$i] //= {
 957             index => $i,
 958             count => 0, count_in => 0, count_out => 0,
 959             spamcount_in => 0, spamcount_out => 0,
 960             viruscount_in => 0, viruscount_out => 0,
 961             bounces_in => 0, bounces_out => 0 };
 962
 963         my $d = @$res[$i];
 964         $d->{count} = $d->{count_in} + $d->{count_out};
 965     }
 966     $sth->finish();
 967
 968     return $res;
 969 }
 970
 971 sub timespan {
 972     my ($self, $from, $to) = @_;
 973
 974     if (defined ($from) && defined ($to)) {
 975         $self->{from} = $from;
 976         $self->{to} = $to;
 977     }
 978
 979     return ($self->{from}, $self->{to});
 980 }
 981
 982 sub localdayspan {
 983     my ($self) = @_;
 984
 985     my ($from, $to) = $self->timespan();
 986
 987     my $timezone = tz_local_offset();;
 988     $from = int(($from + $timezone)/86400) * 86400;
 989     $to = int(($to + $timezone)/86400) * 86400;
 990
 991     $to += 86400 if $from == $to;
 992
 993     return ($from, $to);
 994 }
 995
 996 sub localhourspan {
 997     my ($self) = @_;
 998
 999     my ($from, $to) = $self->timespan();
1000
1001     my $timezone = tz_local_offset();;
1002     $from = int(($from + $timezone)/3600) * 3600;
1003     $to = int(($to + $timezone)/3600) * 3600;
1004
1005     $to += 3600 if $from == $to;
1006
1007     return ($from, $to);
1008 }
1009
1010
1011 1;