SL/SEPA.pm

   1 package SL::SEPA;
   2
   3 use strict;
   4
   5 use POSIX qw(strftime);
   6
   7 use Data::Dumper;
   8 use SL::DBUtils;
   9 use SL::DB::Invoice;
  10 use SL::DB::PurchaseInvoice;
  11 use SL::DB;
  12 use SL::Locale::String qw(t8);
  13 use DateTime;
  14
  15 sub retrieve_open_invoices {
  16   $main::lxdebug->enter_sub();
  17
  18   my $self     = shift;
  19   my %params   = @_;
  20
  21   my $myconfig = \%main::myconfig;
  22   my $form     = $main::form;
  23
  24   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
  25   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
  26   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
  27   my $vc_vc_id = $params{vc} eq 'customer' ? 'c_vendor_id' : 'v_customer_id';
  28
  29   my $mandate  = $params{vc} eq 'customer' ? " AND COALESCE(vc.mandator_id, '') <> '' AND vc.mandate_date_of_signature IS NOT NULL " : '';
  30
  31   # in query: for customers, use payment terms from invoice, for vendors use
  32   # payment terms from vendor settings
  33   # currently there is no option in vendor invoices for setting payment terms,
  34   # so the vendor settings are always used
  35
  36   my $payment_term_type = $params{vc} eq 'customer' ? "${arap}" : 'vc';
  37
  38   # open_amount is not the current open amount according to bookkeeping, but
  39   # the open amount minus the SEPA transfer amounts that haven't been closed yet
  40   my $query =
  41     qq|
  42        SELECT ${arap}.id, ${arap}.invnumber, ${arap}.transdate, ${arap}.${vc}_id as vc_id, ${arap}.amount AS invoice_amount, ${arap}.invoice,
  43          (${arap}.transdate + pt.terms_skonto) as skonto_date, (pt.percent_skonto * 100) as percent_skonto,
  44          (${arap}.amount - (${arap}.amount * pt.percent_skonto)) as amount_less_skonto,
  45          (${arap}.amount * pt.percent_skonto) as skonto_amount,
  46          vc.name AS vcname, vc.language_id, ${arap}.duedate as duedate, ${arap}.direct_debit,
  47          vc.${vc_vc_id} as vc_vc_id,
  48
  49          COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' ${mandate} AS vc_bank_info_ok,
  50
  51          ${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount,
  52          COALESCE(open_transfers.amount, 0) AS transfer_amount,
  53          pt.description as pt_description
  54
  55        FROM ${arap}
  56        LEFT JOIN ${vc} vc ON (${arap}.${vc}_id = vc.id)
  57        LEFT JOIN (SELECT sei.${arap}_id, SUM(sei.amount) + SUM(COALESCE(sei.skonto_amount,0)) AS amount
  58                   FROM sepa_export_items sei
  59                   LEFT JOIN sepa_export se ON (sei.sepa_export_id = se.id)
  60                   WHERE NOT se.closed
  61                     AND (se.vc = '${vc}')
  62                   GROUP BY sei.${arap}_id)
  63          AS open_transfers ON (${arap}.id = open_transfers.${arap}_id)
  64
  65        LEFT JOIN payment_terms pt ON (${payment_term_type}.payment_id = pt.id)
  66
  67        WHERE ${arap}.amount > (COALESCE(open_transfers.amount, 0) + ${arap}.paid)
  68
  69        ORDER BY lower(vc.name) ASC, lower(${arap}.invnumber) ASC
  70 |;
  71     #  $main::lxdebug->message(LXDebug->DEBUG2(),"sepa add query:".$query);
  72
  73   my $results = selectall_hashref_query($form, $dbh, $query);
  74
  75   # add some more data to $results:
  76   # create drop-down data for payment types and suggest amount to be paid according
  77   # to open amount or skonto
  78
  79   foreach my $result ( @$results ) {
  80     my $invoice = $vc eq 'customer' ? SL::DB::Manager::Invoice->find_by(         id => $result->{id} )
  81                                     : SL::DB::Manager::PurchaseInvoice->find_by( id => $result->{id} );
  82
  83     $invoice->get_payment_suggestions(sepa => 1); # consider amounts of open entries in sepa_export_items
  84     $result->{skonto_amount}             = $invoice->skonto_amount;
  85     $result->{within_skonto_period}      = $invoice->within_skonto_period;
  86     $result->{invoice_amount_suggestion} = $invoice->{invoice_amount_suggestion};
  87     $result->{payment_select_options}    = $invoice->{payment_select_options};
  88   };
  89
  90   $main::lxdebug->leave_sub();
  91
  92   return $results;
  93 }
  94
  95 sub create_export {
  96   my ($self, %params) = @_;
  97   $main::lxdebug->enter_sub();
  98
  99   my $rc = SL::DB->client->with_transaction(\&_create_export, $self, %params);
 100
 101   $::lxdebug->leave_sub;
 102   return $rc;
 103 }
 104
 105 sub _create_export {
 106   my $self     = shift;
 107   my %params   = @_;
 108
 109   Common::check_params(\%params, qw(employee bank_transfers vc));
 110
 111   my $myconfig = \%main::myconfig;
 112   my $form     = $main::form;
 113   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
 114   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
 115   my $ARAP     = uc $arap;
 116
 117   my $dbh      = $params{dbh} || SL::DB->client->dbh;
 118
 119   my ($export_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('sepa_export_id_seq')|);
 120   my $query       =
 121     qq|INSERT INTO sepa_export (id, employee_id, vc)
 122        VALUES (?, (SELECT id
 123                    FROM employee
 124                    WHERE login = ?), ?)|;
 125   do_query($form, $dbh, $query, $export_id, $params{employee}, $vc);
 126
 127   my $q_item_id = qq|SELECT nextval('id')|;
 128   my $h_item_id = prepare_query($form, $dbh, $q_item_id);
 129   my $c_mandate = $params{vc} eq 'customer' ? ', vc_mandator_id, vc_mandate_date_of_signature' : '';
 130   my $p_mandate = $params{vc} eq 'customer' ? ', ?, ?' : '';
 131
 132   my $q_insert =
 133     qq|INSERT INTO sepa_export_items (id,          sepa_export_id,           ${arap}_id,  chart_id,
 134                                       amount,      requested_execution_date, reference,   end_to_end_id,
 135                                       our_iban,    our_bic,                  vc_iban,     vc_bic,
 136                                       skonto_amount, payment_type ${c_mandate})
 137        VALUES                        (?,           ?,                        ?,           ?,
 138                                       ?,           ?,                        ?,           ?,
 139                                       ?,           ?,                        ?,           ?,
 140                                       ?,           ? ${p_mandate})|;
 141   my $h_insert = prepare_query($form, $dbh, $q_insert);
 142
 143   my $q_reference =
 144     qq|SELECT arap.invnumber,
 145          (SELECT COUNT(at.*)
 146           FROM acc_trans at
 147           LEFT JOIN chart c ON (at.chart_id = c.id)
 148           WHERE (at.trans_id = ?)
 149             AND (c.link LIKE '%${ARAP}_paid%'))
 150          +
 151          (SELECT COUNT(sei.*)
 152           FROM sepa_export_items sei
 153           WHERE (sei.ap_id = ?))
 154          AS num_payments
 155        FROM ${arap} arap
 156        WHERE id = ?|;
 157   my $h_reference = prepare_query($form, $dbh, $q_reference);
 158
 159   my @now         = localtime;
 160
 161   foreach my $transfer (@{ $params{bank_transfers} }) {
 162     if (!$transfer->{reference}) {
 163       do_statement($form, $h_reference, $q_reference, (conv_i($transfer->{"${arap}_id"})) x 3);
 164
 165       my ($invnumber, $num_payments) = $h_reference->fetchrow_array();
 166       $num_payments++;
 167
 168       $transfer->{reference} = "${invnumber}-${num_payments}";
 169     }
 170
 171     $h_item_id->execute();
 172     my ($item_id)      = $h_item_id->fetchrow_array();
 173
 174     my $end_to_end_id  = strftime "LXO%Y%m%d%H%M%S", localtime;
 175     my $item_id_len    = length "$item_id";
 176     my $num_zeroes     = 35 - $item_id_len - length $end_to_end_id;
 177     $end_to_end_id    .= '0' x $num_zeroes if (0 < $num_zeroes);
 178     $end_to_end_id    .= $item_id;
 179     $end_to_end_id     = substr $end_to_end_id, 0, 35;
 180
 181     my @values = ($item_id,                          $export_id,
 182                   conv_i($transfer->{"${arap}_id"}), conv_i($transfer->{chart_id}),
 183                   $transfer->{amount},               conv_date($transfer->{requested_execution_date}),
 184                   $transfer->{reference},            $end_to_end_id,
 185                   map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic) } qw(our vc));
 186     # save value of skonto_amount and payment_type
 187     if ( $transfer->{payment_type} eq 'without_skonto' ) {
 188       push(@values, 0);
 189     } elsif ($transfer->{payment_type} eq 'difference_as_skonto' ) {
 190       push(@values, $transfer->{amount});
 191     } elsif ($transfer->{payment_type} eq 'with_skonto_pt' ) {
 192       push(@values, $transfer->{skonto_amount});
 193     } else {
 194       die "illegal payment_type: " . $transfer->{payment_type} . "\n";
 195     };
 196     push(@values, $transfer->{payment_type});
 197
 198     push @values, $transfer->{vc_mandator_id}, conv_date($transfer->{vc_mandate_date_of_signature}) if $params{vc} eq 'customer';
 199
 200     do_statement($form, $h_insert, $q_insert, @values);
 201   }
 202
 203   $h_insert->finish();
 204   $h_item_id->finish();
 205
 206   return $export_id;
 207 }
 208
 209 sub retrieve_export {
 210   $main::lxdebug->enter_sub();
 211
 212   my $self     = shift;
 213   my %params   = @_;
 214
 215   Common::check_params(\%params, qw(id vc));
 216
 217   my $myconfig = \%main::myconfig;
 218   my $form     = $main::form;
 219   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
 220   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
 221
 222   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 223
 224   my ($joins, $columns);
 225
 226   if ($params{details}) {
 227     $columns = ', arap.invoice';
 228     $joins   = "LEFT JOIN ${arap} arap ON (se.${arap}_id = arap.id)";
 229   }
 230
 231   my $query =
 232     qq|SELECT se.*,
 233          CASE WHEN COALESCE(e.name, '') <> '' THEN e.name ELSE e.login END AS employee
 234        FROM sepa_export se
 235        LEFT JOIN employee e ON (se.employee_id = e.id)
 236        WHERE se.id = ?|;
 237
 238   my $export = selectfirst_hashref_query($form, $dbh, $query, conv_i($params{id}));
 239
 240   if ($export->{id}) {
 241     my ($columns, $joins);
 242
 243     my $mandator_id = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
 244
 245     if ($params{details}) {
 246       $columns = qq|, arap.invnumber, arap.invoice, arap.transdate AS reference_date, vc.name AS vc_name, vc.${vc}number AS vc_number, c.accno AS chart_accno, c.description AS chart_description ${mandator_id}|;
 247       $joins   = qq|LEFT JOIN ${arap} arap ON (sei.${arap}_id = arap.id)
 248                     LEFT JOIN ${vc} vc     ON (arap.${vc}_id  = vc.id)
 249                     LEFT JOIN chart c      ON (sei.chart_id   = c.id)|;
 250     }
 251
 252     $query = qq|SELECT sei.*
 253                   $columns
 254                 FROM sepa_export_items sei
 255                 $joins
 256                 WHERE sei.sepa_export_id = ?|;
 257
 258     $export->{items} = selectall_hashref_query($form, $dbh, $query, conv_i($params{id}));
 259
 260   } else {
 261     $export->{items} = [];
 262   }
 263
 264   $main::lxdebug->leave_sub();
 265
 266   return $export;
 267 }
 268
 269 sub close_export {
 270   $main::lxdebug->enter_sub();
 271
 272   my $self     = shift;
 273   my %params   = @_;
 274
 275   Common::check_params(\%params, qw(id));
 276
 277   my $myconfig = \%main::myconfig;
 278   my $form     = $main::form;
 279
 280   SL::DB->client->with_transaction(sub {
 281     my $dbh      = $params{dbh} || SL::DB->client->dbh;
 282
 283     my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
 284     my $placeholders = join ', ', ('?') x scalar @ids;
 285     my $query        = qq|UPDATE sepa_export SET closed = TRUE WHERE id IN ($placeholders)|;
 286
 287     do_query($form, $dbh, $query, map { conv_i($_) } @ids);
 288     1;
 289   }) or do { die SL::DB->client->error };
 290
 291   $main::lxdebug->leave_sub();
 292 }
 293
 294 sub list_exports {
 295   $main::lxdebug->enter_sub();
 296
 297   my $self     = shift;
 298   my %params   = @_;
 299
 300   my $myconfig = \%main::myconfig;
 301   my $form     = $main::form;
 302   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
 303   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
 304
 305   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 306
 307   my %sort_columns = (
 308     'id'          => [ 'se.id',                ],
 309     'export_date' => [ 'se.itime',             ],
 310     'employee'    => [ 'e.name',      'se.id', ],
 311     'executed'    => [ 'se.executed', 'se.id', ],
 312     'closed'      => [ 'se.closed',   'se.id', ],
 313     );
 314
 315   my %sort_spec = create_sort_spec('defs' => \%sort_columns, 'default' => 'id', 'column' => $params{sortorder}, 'dir' => $params{sortdir});
 316
 317   my (@where, @values, @where_sub, @values_sub, %joins_sub);
 318
 319   my $filter = $params{filter} || { };
 320
 321   foreach (qw(executed closed)) {
 322     push @where, $filter->{$_} ? "se.$_" : "NOT se.$_" if (exists $filter->{$_});
 323   }
 324
 325   my %operators = ('from' => '>=',
 326                    'to'   => '<=');
 327
 328   foreach my $dir (qw(from to)) {
 329     next unless ($filter->{"export_date_${dir}"});
 330     push @where,  "se.itime $operators{$dir} ?::date";
 331     push @values, $filter->{"export_date_${dir}"};
 332   }
 333
 334   if ($filter->{invnumber}) {
 335     push @where_sub,  "arap.invnumber ILIKE ?";
 336     push @values_sub, like($filter->{invnumber});
 337     $joins_sub{$arap} = 1;
 338   }
 339
 340   if ($filter->{message_id}) {
 341     push @values, like($filter->{message_id});
 342     push @where,  <<SQL;
 343       se.id IN (
 344         SELECT sepa_export_id
 345         FROM sepa_export_message_ids
 346         WHERE message_id ILIKE ?
 347       )
 348 SQL
 349   }
 350
 351   if ($filter->{vc}) {
 352     push @where_sub,  "vc.name ILIKE ?";
 353     push @values_sub, like($filter->{vc});
 354     $joins_sub{$arap} = 1;
 355     $joins_sub{vc}    = 1;
 356   }
 357
 358   foreach my $type (qw(requested_execution execution)) {
 359     foreach my $dir (qw(from to)) {
 360       next unless ($filter->{"${type}_date_${dir}"});
 361       push @where_sub,  "(items.${type}_date IS NOT NULL) AND (items.${type}_date $operators{$dir} ?)";
 362       push @values_sub, $filter->{"${type}_date_${_}"};
 363     }
 364   }
 365
 366   if (@where_sub) {
 367     my $joins_sub  = '';
 368     $joins_sub    .= " LEFT JOIN ${arap} arap ON (items.${arap}_id = arap.id)" if ($joins_sub{$arap});
 369     $joins_sub    .= " LEFT JOIN ${vc} vc      ON (arap.${vc}_id   = vc.id)"   if ($joins_sub{vc});
 370
 371     my $where_sub  = join(' AND ', map { "(${_})" } @where_sub);
 372
 373     my $query_sub  = qq|se.id IN (SELECT items.sepa_export_id
 374                                   FROM sepa_export_items items
 375                                   $joins_sub
 376                                   WHERE $where_sub)|;
 377
 378     push @where,  $query_sub;
 379     push @values, @values_sub;
 380   }
 381
 382   push @where,  'se.vc = ?';
 383   push @values, $vc;
 384
 385   my $where = @where ? ' WHERE ' . join(' AND ', map { "(${_})" } @where) : '';
 386
 387   my $query =
 388     qq|SELECT se.id, se.employee_id, se.executed, se.closed, itime::date AS export_date,
 389          (SELECT COUNT(*)
 390           FROM sepa_export_items sei
 391           WHERE (sei.sepa_export_id = se.id)) AS num_invoices,
 392          (SELECT SUM(sei.amount)
 393           FROM sepa_export_items sei
 394           WHERE (sei.sepa_export_id = se.id)) AS sum_amounts,
 395          (SELECT string_agg(semi.message_id, ', ')
 396           FROM sepa_export_message_ids semi
 397           WHERE semi.sepa_export_id = se.id) AS message_ids,
 398          e.name AS employee
 399        FROM sepa_export se
 400        LEFT JOIN (
 401          SELECT emp.id,
 402            CASE WHEN COALESCE(emp.name, '') <> '' THEN emp.name ELSE emp.login END AS name
 403          FROM employee emp
 404        ) AS e ON (se.employee_id = e.id)
 405        $where
 406        ORDER BY $sort_spec{sql}|;
 407
 408   my $results = selectall_hashref_query($form, $dbh, $query, @values);
 409
 410   $main::lxdebug->leave_sub();
 411
 412   return $results;
 413 }
 414
 415 sub post_payment {
 416   my ($self, %params) = @_;
 417   $main::lxdebug->enter_sub();
 418
 419   my $rc = SL::DB->client->with_transaction(\&_post_payment, $self, %params);
 420
 421   $::lxdebug->leave_sub;
 422   return $rc;
 423 }
 424
 425 sub _post_payment {
 426   my $self     = shift;
 427   my %params   = @_;
 428
 429   Common::check_params(\%params, qw(items));
 430
 431   my $myconfig = \%main::myconfig;
 432   my $form     = $main::form;
 433   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
 434   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
 435   my $mult     = $params{vc} eq 'customer' ? -1         : 1;
 436   my $ARAP     = uc $arap;
 437
 438   my $dbh      = $params{dbh} || SL::DB->client->dbh;
 439
 440   my @items    = ref $params{items} eq 'ARRAY' ? @{ $params{items} } : ($params{items});
 441
 442   my %handles  = (
 443     'get_item'       => [ qq|SELECT sei.*
 444                              FROM sepa_export_items sei
 445                              WHERE sei.id = ?| ],
 446
 447     'get_arap'       => [ qq|SELECT at.chart_id
 448                              FROM acc_trans at
 449                              LEFT JOIN chart c ON (at.chart_id = c.id)
 450                              WHERE (trans_id = ?)
 451                                AND ((c.link LIKE '%:${ARAP}') OR (c.link LIKE '${ARAP}:%') OR (c.link = '${ARAP}'))
 452                              LIMIT 1| ],
 453
 454     'add_acc_trans'  => [ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate,       source, memo, taxkey, tax_id ,                                     chart_link)
 455                              VALUES                (?,        ?,        ?,      ?,         current_date, ?,      '',   0,      (SELECT id FROM tax WHERE taxkey=0 LIMIT 1), (SELECT link FROM chart WHERE id=?))| ],
 456
 457     'update_arap'    => [ qq|UPDATE ${arap}
 458                              SET paid = paid + ?
 459                              WHERE id = ?| ],
 460
 461     'finish_item'    => [ qq|UPDATE sepa_export_items
 462                              SET execution_date = ?, executed = TRUE
 463                              WHERE id = ?| ],
 464
 465     'has_unexecuted' => [ qq|SELECT sei1.id
 466                              FROM sepa_export_items sei1
 467                              WHERE (sei1.sepa_export_id = (SELECT sei2.sepa_export_id
 468                                                            FROM sepa_export_items sei2
 469                                                            WHERE sei2.id = ?))
 470                                AND NOT COALESCE(sei1.executed, FALSE)
 471                              LIMIT 1| ],
 472
 473     'do_close'       => [ qq|UPDATE sepa_export
 474                              SET executed = TRUE, closed = TRUE
 475                              WHERE (id = ?)| ],
 476     );
 477
 478   map { unshift @{ $_ }, prepare_query($form, $dbh, $_->[0]) } values %handles;
 479
 480   foreach my $item (@items) {
 481
 482     my $item_id = conv_i($item->{id});
 483
 484     # Retrieve the item data belonging to the ID.
 485     do_statement($form, @{ $handles{get_item} }, $item_id);
 486     my $orig_item = $handles{get_item}->[0]->fetchrow_hashref();
 487
 488     next if (!$orig_item);
 489
 490     # fetch item_id via Rose (same id as orig_item)
 491     my $sepa_export_item = SL::DB::Manager::SepaExportItem->find_by( id => $item_id);
 492
 493     my $invoice;
 494
 495     if ( $sepa_export_item->ar_id ) {
 496       $invoice = SL::DB::Manager::Invoice->find_by( id => $sepa_export_item->ar_id);
 497     } elsif ( $sepa_export_item->ap_id ) {
 498       $invoice = SL::DB::Manager::PurchaseInvoice->find_by( id => $sepa_export_item->ap_id);
 499     } else {
 500       die "sepa_export_item needs either ar_id or ap_id\n";
 501     };
 502
 503     $invoice->pay_invoice(amount       => $sepa_export_item->amount,
 504                           payment_type => $sepa_export_item->payment_type,
 505                           chart_id     => $sepa_export_item->chart_id,
 506                           source       => $sepa_export_item->reference,
 507                           transdate    => $item->{execution_date},  # value from user form
 508                          );
 509
 510     # Update the item to reflect that it has been posted.
 511     do_statement($form, @{ $handles{finish_item} }, $item->{execution_date}, $item_id);
 512
 513     # Check whether or not we can close the export itself if there are no unexecuted items left.
 514     do_statement($form, @{ $handles{has_unexecuted} }, $item_id);
 515     my ($has_unexecuted) = $handles{has_unexecuted}->[0]->fetchrow_array();
 516
 517     if (!$has_unexecuted) {
 518       do_statement($form, @{ $handles{do_close} }, $orig_item->{sepa_export_id});
 519     }
 520   }
 521
 522   map { $_->[0]->finish() } values %handles;
 523
 524   return 1;
 525 }
 526
 527 1;