From 5d76e93e770f858f86ce1e46198f94b60bcbe32e Mon Sep 17 00:00:00 2001
From: "G. Richardson" <grichardson@kivitec.de>
Date: Wed, 20 Feb 2019 12:30:16 +0100
Subject: [PATCH] Aggregatfunktion comma entfernt und Templates angepasst
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

"comma" war eine alte benutzerdefinierte Aggregatfunktion, die benutzt
wurde, um mehrere aggregierte Werte aus einem GROUP BY in einen
kommaseparierten String umzuwandeln.

Mittlerweile würde man das einfach mit array_agg und array_to_string machen:
 array_to_string(array_agg(startdate), ', ') as startdate

Im Template wurden die ',' dann durch '<br>' ersetzt. Stattdessen werden
die Werte im Query nun als array_agg ausgegeben, und im Template wird
eine Schleife über das Arrayref gebildet.
---
 SL/CA.pm                                             | 10 +++++-----
 sql/Pg-upgrade2/remove_comma_aggregate_functions.sql |  6 ++++++
 templates/webpages/am/list_account_details.html      | 10 +++++-----
 3 files changed, 16 insertions(+), 10 deletions(-)
 create mode 100644 sql/Pg-upgrade2/remove_comma_aggregate_functions.sql

diff --git a/SL/CA.pm b/SL/CA.pm
index 85f8dfdb9..30d471298 100644
--- a/SL/CA.pm
+++ b/SL/CA.pm
@@ -101,11 +101,11 @@ sub all_accounts {
       c.pos_eur,
       c.valid_from,
       c.datevautomatik,
-      comma(tk.startdate::text) AS startdate,
-      comma(tk.taxkey_id::text) AS taxkey,
-      comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription,
-      comma(taxchart.accno::text) AS taxaccount,
-      comma(tk.pos_ustva::text) AS tk_ustva,
+      array_agg(tk.startdate) AS startdates,
+      array_agg(tk.taxkey_id) AS taxkeys,
+      array_agg(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescriptions,
+      array_agg(taxchart.accno) AS taxaccounts,
+      array_agg(tk.pos_ustva) AS pos_ustvas,
       ( SELECT accno
       FROM chart c2
       WHERE c2.id = c.id
diff --git a/sql/Pg-upgrade2/remove_comma_aggregate_functions.sql b/sql/Pg-upgrade2/remove_comma_aggregate_functions.sql
new file mode 100644
index 000000000..00c748dc6
--- /dev/null
+++ b/sql/Pg-upgrade2/remove_comma_aggregate_functions.sql
@@ -0,0 +1,6 @@
+-- @tag: remove_comma_aggregate_functions
+-- @description: Entfernt Aggregate Funktion comma
+-- @depends: release_3_5_3
+
+DROP AGGREGATE IF EXISTS comma(text);
+DROP FUNCTION IF EXISTS comma_aggregate ( text, text) ;
diff --git a/templates/webpages/am/list_account_details.html b/templates/webpages/am/list_account_details.html
index 884dc7d2b..e35b7d243 100644
--- a/templates/webpages/am/list_account_details.html
+++ b/templates/webpages/am/list_account_details.html
@@ -35,11 +35,11 @@
  </tr>
 
  <tr class="coa_listrow[% loop.count % 2 %]">
-  <td class="coa_detail_emph">[% IF row.taxkey         %][% HTML.escape(row.taxkey).replace(',', '<br>')         %][% ELSE %]-[% END %]</td>
-  <td class="coa_detail_emph">[% IF row.taxaccount     %][% HTML.escape(row.taxaccount).replace(',', '<br>')     %][% ELSE %]-[% END %]</td>
-  <td class="coa_detail_emph">[% IF row.taxdescription %][% HTML.escape(row.taxdescription).replace(',', '<br>') %][% ELSE %]-[% END %]</td>
-  <td class="coa_detail_emph">[% IF row.tk_ustva       %][% HTML.escape(row.tk_ustva).replace(',', '<br>')       %][% ELSE %]-[% END %]</td>
-  <td class="coa_detail_emph">[% IF row.startdate      %][% HTML.escape(row.startdate).replace(',', '<br>')      %][% ELSE %]-[% END %]</td>
+  <td class = "coa_detail_emph">[% IF row.taxkeys.size         %][% FOR taxkey         = row.taxkeys         %][% HTML.escape(taxkey)         %]<br>[% END %][% ELSE %]-[% END %]</td>
+  <td class = "coa_detail_emph">[% IF row.taxaccounts.size     %][% FOR taxaccount     = row.taxaccounts     %][% HTML.escape(taxaccount)     %]<br>[% END %][% ELSE %]-[% END %]</td>
+  <td class = "coa_detail_emph">[% IF row.taxdescriptions.size %][% FOR taxdescription = row.taxdescriptions %][% HTML.escape(taxdescription) %]<br>[% END %][% ELSE %]-[% END %]</td>
+  <td class = "coa_detail_emph">[% IF row.pos_ustvas.size      %][% FOR pos_ustva      = row.pos_ustvas      %][% HTML.escape(pos_ustva)      %]<br>[% END %][% ELSE %]-[% END %]</td>
+  <td class = "coa_detail_emph">[% IF row.startdates.size      %][% FOR startdate      = row.startdates      %][% HTML.escape(startdate)      %]<br>[% END %][% ELSE %]-[% END %]</td>
  </tr>
 
  <tr class="coa_listrow[% loop.count % 2 %]">
-- 
2.20.1