X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=inline;f=SL%2FSEPA.pm;h=94579179bc559ab1def7075adafea7468d1aef77;hb=bebca3b60f8c019b9acbc436da7437d67dc19e67;hp=cbea8417e317b5d1457b912665d84ca957974303;hpb=a87694dc525b4eabff11029f1e5401621e2bd3bc;p=kivitendo-erp.git

diff --git a/SL/SEPA.pm b/SL/SEPA.pm
index cbea8417e..94579179b 100644
--- a/SL/SEPA.pm
+++ b/SL/SEPA.pm
@@ -49,13 +49,13 @@ sub retrieve_open_invoices {
 
        FROM ${arap}
        LEFT JOIN ${vc} vc ON (${arap}.${vc}_id = vc.id)
-       LEFT JOIN (SELECT sei.ap_id, SUM(sei.amount) AS amount
+       LEFT JOIN (SELECT sei.${arap}_id, SUM(sei.amount) AS amount
                   FROM sepa_export_items sei
                   LEFT JOIN sepa_export se ON (sei.sepa_export_id = se.id)
                   WHERE NOT se.closed
                     AND (se.vc = '${vc}')
-                  GROUP BY sei.ap_id)
-         AS open_transfers ON (${arap}.id = open_transfers.ap_id)
+                  GROUP BY sei.${arap}_id)
+         AS open_transfers ON (${arap}.id = open_transfers.${arap}_id)
 
        LEFT JOIN payment_terms pt ON (${payment_term_type}.payment_id = pt.id)
 
@@ -326,6 +326,17 @@ sub list_exports {
     $joins_sub{$arap} = 1;
   }
 
+  if ($filter->{message_id}) {
+    push @values, '%' . $filter->{message_id} . '%';
+    push @where,  <<SQL;
+      se.id IN (
+        SELECT sepa_export_id
+        FROM sepa_export_message_ids
+        WHERE message_id ILIKE ?
+      )
+SQL
+  }
+
   if ($filter->{vc}) {
     push @where_sub,  "vc.name ILIKE ?";
     push @values_sub, '%' . $filter->{vc} . '%';
@@ -364,6 +375,15 @@ sub list_exports {
 
   my $query =
     qq|SELECT se.id, se.employee_id, se.executed, se.closed, itime::date AS export_date,
+         (SELECT COUNT(*)
+          FROM sepa_export_items sei
+          WHERE (sei.sepa_export_id = se.id)) AS num_invoices,
+         (SELECT SUM(sei.amount)
+          FROM sepa_export_items sei
+          WHERE (sei.sepa_export_id = se.id)) AS sum_amounts,
+         (SELECT string_agg(semi.message_id, ', ')
+          FROM sepa_export_message_ids semi
+          WHERE semi.sepa_export_id = se.id) AS message_ids,
          e.name AS employee
        FROM sepa_export se
        LEFT JOIN (