From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: 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 12AC8B7B1 for ; Thu, 24 Nov 2022 13:21:28 +0100 (CET) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id A05642F1C8 for ; Thu, 24 Nov 2022 13:21:27 +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 ; Thu, 24 Nov 2022 13:21:22 +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 9750943953 for ; Thu, 24 Nov 2022 13:21:15 +0100 (CET) From: Dominik Csapak To: pmg-devel@lists.proxmox.com Date: Thu, 24 Nov 2022 13:21:12 +0100 Message-Id: <20221124122112.666868-13-d.csapak@proxmox.com> X-Mailer: git-send-email 2.30.2 In-Reply-To: <20221124122112.666868-1-d.csapak@proxmox.com> References: <20221124122112.666868-1-d.csapak@proxmox.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.064 Adjusted score from AWL reputation of From: address 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 Alignment SPF_HELO_NONE 0.001 SPF: HELO does not publish an SPF Record SPF_PASS -0.001 SPF: sender matches SPF record URIBL_BLOCKED 0.001 ADMINISTRATOR NOTICE: The query to URIBL was blocked. See http://wiki.apache.org/spamassassin/DnsBlocklists#dnsbl-block for more information. [statistic.pm] Subject: [pmg-devel] [PATCH pmg-api v4 12/12] statistics: refactor filter_text generation X-BeenThere: pmg-devel@lists.proxmox.com X-Mailman-Version: 2.1.29 Precedence: list List-Id: Proxmox Mail Gateway development discussion List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 24 Nov 2022 12:21:28 -0000 it's basically always the same and having one place where we encode the filter, makes it more legible Signed-off-by: Dominik Csapak --- src/PMG/Statistic.pm | 41 ++++++++++++++++++++++++----------------- 1 file changed, 24 insertions(+), 17 deletions(-) diff --git a/src/PMG/Statistic.pm b/src/PMG/Statistic.pm index 96ef61d..8d63b40 100755 --- a/src/PMG/Statistic.pm +++ b/src/PMG/Statistic.pm @@ -562,6 +562,18 @@ sub user_stat_to_perlstring { return $res; } +my sub get_filter_text { + my ($dbh, $field, $filter) = @_; + + if (!$filter || !$field) { + return ''; + } + + my $pattern = $dbh->quote(encode('UTF-8', "%${filter}%")); + + return "AND ${field} like ${pattern} "; +} + sub user_stat_contact_details { my ($self, $rdb, $receiver, $limit, $sorters, $filter) = @_; @@ -571,12 +583,12 @@ 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 $filter_text = get_filter_text($rdb->{dbh}, 'sender', $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_pattern ? "AND sender like " . $filter_pattern . ' ' : '') . + $filter_text . "ORDER BY $orderby limit $limit"; my $sth = $rdb->{dbh}->prepare($query); @@ -602,14 +614,13 @@ 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 $filter_text = get_filter_text($rdb->{dbh}, 'receiver', $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_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') . + $filter_text . "AND $cond_good_mail AND NOT direction AND sender != '' "; if ($advfilter) { @@ -642,8 +653,7 @@ 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 $filter_text = get_filter_text($rdb->{dbh}, 'receiver', $filter); my $sth = $rdb->{dbh}->prepare( "SELECT " . @@ -651,7 +661,7 @@ sub user_stat_sender_details { "FROM CStatistic, CReceivers " . "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND " . "$cond_good_mail AND NOT direction AND sender = ? " . - ($filter_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') . + $filter_text . "ORDER BY $orderby limit $limit"); $sth->execute(encode('UTF-8',$sender)); @@ -675,14 +685,13 @@ 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 $filter_text = get_filter_text($rdb->{dbh}, 'sender', $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_pattern ? "AND sender like " . $filter_pattern . ' ' : '') . + $filter_text . "GROUP BY sender ORDER BY $orderby limit $limit"; my $sth = $rdb->{dbh}->prepare($query); @@ -707,14 +716,13 @@ 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 $filter_text = get_filter_text($rdb->{dbh}, 'sender', $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_pattern ? "AND sender like " . $filter_pattern . ' ' : '') . + $filter_text . "ORDER BY $orderby limit $limit"); $sth->execute(encode('UTF-8',$receiver)); @@ -739,8 +747,7 @@ 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 $filter_text = get_filter_text($rdb->{dbh}, 'receiver', $filter); my $query = "SELECT receiver, " . "count(*) AS count, " . @@ -762,7 +769,7 @@ sub user_stat_receiver { } $query .= "AND $cond_good_mail and direction " . - ($filter_pattern ? "AND receiver like " . $filter_pattern . ' ' : '') . + $filter_text . "GROUP BY receiver ORDER BY $orderby LIMIT $limit"; my $sth = $rdb->{dbh}->prepare($query); -- 2.30.2