src/PMG/Statistic.pm

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