From: Stoiko Ivanov <s.ivanov@proxmox.com>
To: pmg-devel@lists.proxmox.com
Subject: [pmg-devel] [PATCH pmg-api v3 8/8] statistics: handle utf8 data.
Date: Wed, 23 Nov 2022 10:23:34 +0100 [thread overview]
Message-ID: <20221123092336.11423-9-s.ivanov@proxmox.com> (raw)
In-Reply-To: <20221123092336.11423-1-s.ivanov@proxmox.com>
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..96ef61d 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 user_stat_to_perlstring {
+ 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, user_stat_to_perlstring($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, user_stat_to_perlstring($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, user_stat_to_perlstring($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, user_stat_to_perlstring($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, user_stat_to_perlstring($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, user_stat_to_perlstring($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, user_stat_to_perlstring($ref);
}
$sth->finish();
--
2.30.2
next prev parent reply other threads:[~2022-11-23 9:24 UTC|newest]
Thread overview: 16+ messages / expand[flat|nested] mbox.gz Atom feed top
2022-11-23 9:23 [pmg-devel] [PATCH pmg-api/pmg-gui v3] ruledb - improve experience for non-ascii tests and mails Stoiko Ivanov
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-api v3 1/8] utils: return perl string from decode_rfc1522 Stoiko Ivanov
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-api v3 2/8] ruledb: properly substitute prox_vars in headers Stoiko Ivanov
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-api v3 3/8] fix #2541 ruledb: encode relevant values as utf-8 in database Stoiko Ivanov
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-api v3 4/8] ruledb: encode e-mail addresses for syslog Stoiko Ivanov
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-api v3 5/8] partially fix #2465: handle smtputf8 addresses in the rule-system Stoiko Ivanov
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-api v3 6/8] quarantine: handle utf8 data Stoiko Ivanov
2022-11-23 14:15 ` Dominik Csapak
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-api v3 7/8] pmgqm: handle smtputf8 data Stoiko Ivanov
2022-11-23 14:20 ` Dominik Csapak
2022-11-23 9:23 ` Stoiko Ivanov [this message]
2022-11-23 14:26 ` [pmg-devel] [PATCH pmg-api v3 8/8] statistics: handle utf8 data Dominik Csapak
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-gui v3 1/2] utils: add custom validator for pmg-email-address Stoiko Ivanov
2022-11-23 9:23 ` [pmg-devel] [PATCH pmg-gui v3 2/2] userblocklists: use PMGMail as validator for pmail Stoiko Ivanov
2022-11-23 14:09 ` [pmg-devel] [PATCH pmg-api/pmg-gui v3] ruledb - improve experience for non-ascii tests and mails Dominik Csapak
2022-11-26 7:00 ` [pmg-devel] applied-gui: " Thomas Lamprecht
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=20221123092336.11423-9-s.ivanov@proxmox.com \
--to=s.ivanov@proxmox.com \
--cc=pmg-devel@lists.proxmox.com \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox