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