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