From df2be985ab2f1c240a7d028a7d2bb0d06207f281 Mon Sep 17 00:00:00 2001
From: Llewellyn Marshall <llewellyn.marshall@ncdcr.gov>
Date: Fri, 26 May 2023 09:52:56 -0400
Subject: [PATCH] make string agg cut out blank and repeated strings.

---
 Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm       | 2 +-
 Open-ILS/src/templates/staff/reporter/share/report_strings.tt2 | 2 ++
 2 files changed, 3 insertions(+), 1 deletion(-)

diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm
index a559276bb7..5a1f6d7c59 100644
--- a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm
@@ -893,7 +893,7 @@ package OpenILS::Reporter::SQLBuilder::Column::Transform::string_agg;
 
 sub toSQL {
     my $self = shift;
-    return 'STRING_AGG("' . $self->{_relation} . '"."' . $self->name . '"::text,\',\')';
+    return 'STRING_AGG(DISTINCT nullif("' . $self->{_relation} . '"."' . $self->name . '"::text,\'\'),\',\')';
 }
 
 sub is_aggregate { return 1 }
diff --git a/Open-ILS/src/templates/staff/reporter/share/report_strings.tt2 b/Open-ILS/src/templates/staff/reporter/share/report_strings.tt2
index cc49019002..ab8d94d6ca 100644
--- a/Open-ILS/src/templates/staff/reporter/share/report_strings.tt2
+++ b/Open-ILS/src/templates/staff/reporter/share/report_strings.tt2
@@ -82,6 +82,7 @@ s.TFORMS_LABEL_AGE = '[% l( "Age") %]';
 s.TFORMS_LABEL_MONTHS_AGO = '[% l( "Months ago") %]';
 s.TFORMS_LABEL_QUARTERS_AGO = '[% l( "Quarters ago") %]';
 s.TFORMS_LABEL_SUM = '[% l( "Sum") %]';
+s.TFORMS_LABEL_STRING_AGG = '[% l( "String Aggregate") %]';
 s.TFORMS_LABEL_AVERAGE = '[% l( "Average") %]';
 s.TFORMS_LABEL_ROUND = '[% l( "Round") %]';
 s.TFORMS_LABEL_INT = '[% l( "Drop trailing decimals") %]';
@@ -173,6 +174,7 @@ s.TRANSFORMS_AGE = '[% l( "Age") %]';
 s.TRANSFORMS_MONTHS_AGO = '[% l( "Months ago") %]';
 s.TRANSFORMS_QUARTERS_AGO = '[% l( "Quarters ago") %]';
 s.TRANSFORMS_SUM = '[% l( "Sum") %]';
+s.TRANSFORMS_STRING_AGG = '[% l( "String Aggregate") %]';
 s.TRANSFORMS_AVERAGE = '[% l( "Average") %]';
 s.TRANSFORMS_ROUND = '[% l( "Round") %]';
 s.TRANSFORMS_INT = '[% l( "Drop trailing decimals") %]';
-- 
2.11.0