+ # open_amount is not the current open amount according to bookkeeping, but
+ # the open amount minus the SEPA transfer amounts that haven't been closed yet
+ my $query =
+ qq|
+ SELECT ${arap}.id, ${arap}.invnumber, ${arap}.transdate, ${arap}.${vc}_id as vc_id, ${arap}.amount AS invoice_amount, ${arap}.invoice,
+ (${arap}.transdate + pt.terms_skonto) as skonto_date, (pt.percent_skonto * 100) as percent_skonto,
+ (${arap}.amount - (${arap}.amount * pt.percent_skonto)) as amount_less_skonto,
+ (${arap}.amount * pt.percent_skonto) as skonto_amount,
+ vc.name AS vcname, vc.language_id, ${arap}.duedate as duedate, ${arap}.direct_debit,
+ vc.${vc_vc_id} as vc_vc_id,
+
+ COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' ${mandate} AS vc_bank_info_ok,
+
+ ${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount,
+ COALESCE(open_transfers.amount, 0) AS transfer_amount,
+ pt.description as pt_description
+
+ FROM ${arap}
+ LEFT JOIN ${vc} vc ON (${arap}.${vc}_id = vc.id)
+ LEFT JOIN (SELECT sei.${arap}_id, SUM(sei.amount) + SUM(COALESCE(sei.skonto_amount,0)) AS amount