From mboxrd@z Thu Jan 1 00:00:00 1970
Return-Path: <s.ivanov@proxmox.com>
Received: from firstgate.proxmox.com (firstgate.proxmox.com [212.224.123.68])
(using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)
key-exchange X25519 server-signature RSA-PSS (2048 bits))
(No client certificate requested)
by lists.proxmox.com (Postfix) with ESMTPS id 2B15895CD
for <pmg-devel@lists.proxmox.com>; Thu, 17 Nov 2022 16:07:21 +0100 (CET)
Received: from firstgate.proxmox.com (localhost [127.0.0.1])
by firstgate.proxmox.com (Proxmox) with ESMTP id 0CD642FC82
for <pmg-devel@lists.proxmox.com>; Thu, 17 Nov 2022 16:06:51 +0100 (CET)
Received: from proxmox-new.maurer-it.com (proxmox-new.maurer-it.com
[94.136.29.106])
(using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)
key-exchange X25519 server-signature RSA-PSS (2048 bits))
(No client certificate requested)
by firstgate.proxmox.com (Proxmox) with ESMTPS
for <pmg-devel@lists.proxmox.com>; Thu, 17 Nov 2022 16:06:46 +0100 (CET)
Received: from proxmox-new.maurer-it.com (localhost.localdomain [127.0.0.1])
by proxmox-new.maurer-it.com (Proxmox) with ESMTP id A887F44D8B
for <pmg-devel@lists.proxmox.com>; Thu, 17 Nov 2022 16:06:46 +0100 (CET)
From: Stoiko Ivanov <s.ivanov@proxmox.com>
To: pmg-devel@lists.proxmox.com
Date: Thu, 17 Nov 2022 16:06:11 +0100
Message-Id: <20221117150611.253644-9-s.ivanov@proxmox.com>
X-Mailer: git-send-email 2.30.2
In-Reply-To: <20221117150611.253644-1-s.ivanov@proxmox.com>
References: <20221117150611.253644-1-s.ivanov@proxmox.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
X-SPAM-LEVEL: Spam detection results: =?UTF-8?Q?0=0A=09?=AWL 0.118 Adjusted
score from AWL reputation of From: =?UTF-8?Q?address=0A=09?=BAYES_00 -1.9
Bayes spam probability is 0 to 1%
KAM_DMARC_STATUS 0.01 Test Rule for DKIM or SPF Failure with Strict
=?UTF-8?Q?Alignment=0A=09?=PROLO_LEO1 0.1 Meta Catches all Leo drug
variations so =?UTF-8?Q?far=0A=09?=SPF_HELO_NONE 0.001 SPF: HELO does not
publish an SPF =?UTF-8?Q?Record=0A=09?=SPF_PASS -0.001 SPF: sender matches
SPF record
Subject: [pmg-devel] [PATCH pmg-api v2 8/8] statistics: handle utf8 data.
X-BeenThere: pmg-devel@lists.proxmox.com
X-Mailman-Version: 2.1.29
Precedence: list
List-Id: Proxmox Mail Gateway development discussion
<pmg-devel.lists.proxmox.com>
List-Unsubscribe: <https://lists.proxmox.com/cgi-bin/mailman/options/pmg-devel>,
<mailto:pmg-devel-request@lists.proxmox.com?subject=unsubscribe>
List-Archive: <http://lists.proxmox.com/pipermail/pmg-devel/>
List-Post: <mailto:pmg-devel@lists.proxmox.com>
List-Help: <mailto:pmg-devel-request@lists.proxmox.com?subject=help>
List-Subscribe: <https://lists.proxmox.com/cgi-bin/mailman/listinfo/pmg-devel>,
<mailto:pmg-devel-request@lists.proxmox.com?subject=subscribe>
X-List-Received-Date: Thu, 17 Nov 2022 15:07:21 -0000
Signed-off-by: Stoiko Ivanov <s.ivanov@proxmox.com>
---
src/PMG/Statistic.pm | 67 +++++++++++++++++++++++++++++++++-----------
1 file changed, 50 insertions(+), 17 deletions(-)
diff --git a/src/PMG/Statistic.pm b/src/PMG/Statistic.pm
index 6d27930..6cb65ff 100755
--- a/src/PMG/Statistic.pm
+++ b/src/PMG/Statistic.pm
@@ -3,6 +3,7 @@ package PMG::Statistic;
use strict;
use warnings;
use DBI;
+use Encode qw(encode);
use Time::Local;
use Time::Zone;
@@ -545,6 +546,22 @@ my $compute_sql_orderby = sub {
return $orderby;
};
+sub encode_user_stat {
+ my ($entry) = @_;
+
+ my $res = { };
+
+ for my $a (keys %$entry) {
+ if ($a eq 'receiver' || $a eq 'sender' || $a eq 'contact') {
+ $res->{$a} = PMG::Utils::try_decode_utf8($entry->{$a});
+ } else {
+ $res->{$a} = $entry->{$a};
+ }
+ }
+
+ return $res;
+}
+
sub user_stat_contact_details {
my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_;
@@ -554,19 +571,21 @@ sub user_stat_contact_details {
my $cond_good_mail = $self->query_cond_good_mail ($from, $to);
+ my $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%"));
+
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 = ? " .
- ($filter ? "AND sender like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') .
+ ($filter_pattern ? "AND sender like " . $filter_pattern . ' ' : '') .
"ORDER BY $orderby limit $limit";
my $sth = $rdb->{dbh}->prepare($query);
- $sth->execute($receiver);
+ $sth->execute(encode('UTF-8',$receiver));
my $res = [];
while (my $ref = $sth->fetchrow_hashref()) {
- push @$res, $ref;
+ push @$res, encode_user_stat($ref);
}
$sth->finish();
@@ -583,11 +602,14 @@ sub user_stat_contact {
my $cond_good_mail = $self->query_cond_good_mail($from, $to);
+ my $filter_pattern;
+ $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter;
+
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}%") . ' ' : '') .
+ ($filter_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') .
"AND $cond_good_mail AND NOT direction AND sender != '' ";
if ($advfilter) {
@@ -603,7 +625,7 @@ sub user_stat_contact {
my $res = [];
while (my $ref = $sth->fetchrow_hashref()) {
- push @$res, $ref;
+ push @$res, encode_user_stat($ref);
}
$sth->finish();
@@ -620,20 +642,23 @@ sub user_stat_sender_details {
my $cond_good_mail = $self->query_cond_good_mail($from, $to);
+ my $filter_pattern;
+ $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter;
+
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}%") . ' ' : '') .
+ ($filter_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') .
"ORDER BY $orderby limit $limit");
- $sth->execute($sender);
+ $sth->execute(encode('UTF-8',$sender));
my $res = [];
while (my $ref = $sth->fetchrow_hashref()) {
- push @$res, $ref;
+ push @$res, encode_user_stat($ref);
}
$sth->finish();
@@ -650,11 +675,14 @@ sub user_stat_sender {
my $cond_good_mail = $self->query_cond_good_mail ($from, $to);
+ my $filter_pattern;
+ $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter;
+
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 != '' " .
- ($filter ? "AND sender like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') .
+ ($filter_pattern ? "AND sender like " . $filter_pattern . ' ' : '') .
"GROUP BY sender ORDER BY $orderby limit $limit";
my $sth = $rdb->{dbh}->prepare($query);
@@ -662,7 +690,7 @@ sub user_stat_sender {
my $res = [];
while (my $ref = $sth->fetchrow_hashref()) {
- push @$res, $ref;
+ push @$res, encode_user_stat($ref);
}
$sth->finish();
@@ -679,18 +707,21 @@ sub user_stat_receiver_details {
my $cond_good_mail = $self->query_cond_good_mail($from, $to);
+ my $filter_pattern;
+ $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter;
+
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}%") . ' ' : '') .
+ ($filter_pattern ? "AND sender like " . $filter_pattern . ' ' : '') .
"ORDER BY $orderby limit $limit");
- $sth->execute($receiver);
+ $sth->execute(encode('UTF-8',$receiver));
my $res = [];
while (my $ref = $sth->fetchrow_hashref()) {
- push @$res, $ref;
+ push @$res, encode_user_stat($ref);
}
$sth->finish();
@@ -708,6 +739,9 @@ sub user_stat_receiver {
my $cond_good_mail = $self->query_cond_good_mail ($from, $to) . " AND " .
"receiver IS NOT NULL AND receiver != ''";
+ my $filter_pattern;
+ $filter_pattern = $rdb->{dbh}->quote(encode('UTF-8', "%${filter}%")) if $filter;
+
my $query = "SELECT receiver, " .
"count(*) AS count, " .
"sum (bytes) AS bytes, " .
@@ -728,7 +762,7 @@ sub user_stat_receiver {
}
$query .= "AND $cond_good_mail and direction " .
- ($filter ? "AND receiver like " . $rdb->{dbh}->quote("%${filter}%") . ' ' : '') .
+ ($filter_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') .
"GROUP BY receiver ORDER BY $orderby LIMIT $limit";
my $sth = $rdb->{dbh}->prepare($query);
@@ -736,7 +770,7 @@ sub user_stat_receiver {
my $res = [];
while (my $ref = $sth->fetchrow_hashref()) {
- push @$res, $ref;
+ push @$res, encode_user_stat($ref);
}
$sth->finish();
@@ -873,9 +907,8 @@ sub recent_receivers {
my $sth = $rdb->{dbh}->prepare($cmd);
$sth->execute ($from, $limit);
-
while (my $ref = $sth->fetchrow_hashref()) {
- push @$res, $ref;
+ push @$res, encode_user_stat($ref);
}
$sth->finish();
--
2.30.2