Merge branch 'master' of github.com:kivitendo/kivitendo-erp
[kivitendo-erp.git] / SL / SEPA.pm
1 package SL::SEPA;
2
3 use strict;
4
5 use POSIX qw(strftime);
6
7 use SL::DBUtils;
8
9 sub retrieve_open_invoices {
10   $main::lxdebug->enter_sub();
11
12   my $self     = shift;
13   my %params   = @_;
14
15   my $myconfig = \%main::myconfig;
16   my $form     = $main::form;
17
18   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
19   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
20   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
21
22   my $query =
23     qq|
24        SELECT ${arap}.id, ${arap}.invnumber, ${arap}.${vc}_id as vc_id, ${arap}.amount AS invoice_amount, ${arap}.invoice,
25          vc.name AS vcname, vc.language_id, ${arap}.duedate as duedate, ${arap}.direct_debit,
26
27          COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' AS vc_bank_info_ok,
28
29          ${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount
30
31        FROM ${arap}
32        LEFT JOIN ${vc} vc ON (${arap}.${vc}_id = vc.id)
33        LEFT JOIN (SELECT sei.ap_id, SUM(sei.amount) AS amount
34                   FROM sepa_export_items sei
35                   LEFT JOIN sepa_export se ON (sei.sepa_export_id = se.id)
36                   WHERE NOT se.closed
37                     AND (se.vc = '${vc}')
38                   GROUP BY sei.ap_id)
39          AS open_transfers ON (${arap}.id = open_transfers.ap_id)
40
41        WHERE ${arap}.amount > (COALESCE(open_transfers.amount, 0) + ${arap}.paid)
42
43        ORDER BY lower(vc.name) ASC, lower(${arap}.invnumber) ASC
44 |;
45
46   my $results = selectall_hashref_query($form, $dbh, $query);
47
48   $main::lxdebug->leave_sub();
49
50   return $results;
51 }
52
53 sub create_export {
54   $main::lxdebug->enter_sub();
55
56   my $self     = shift;
57   my %params   = @_;
58
59   Common::check_params(\%params, qw(employee bank_transfers vc));
60
61   my $myconfig = \%main::myconfig;
62   my $form     = $main::form;
63   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
64   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
65   my $ARAP     = uc $arap;
66
67   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
68
69   my ($export_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('sepa_export_id_seq')|);
70   my $query       =
71     qq|INSERT INTO sepa_export (id, employee_id, vc)
72        VALUES (?, (SELECT id
73                    FROM employee
74                    WHERE login = ?), ?)|;
75   do_query($form, $dbh, $query, $export_id, $params{employee}, $vc);
76
77   my $q_item_id = qq|SELECT nextval('id')|;
78   my $h_item_id = prepare_query($form, $dbh, $q_item_id);
79
80   my $q_insert =
81     qq|INSERT INTO sepa_export_items (id,          sepa_export_id,           ${arap}_id,  chart_id,
82                                       amount,      requested_execution_date, reference,   end_to_end_id,
83                                       our_iban,    our_bic,                  vc_iban,     vc_bic)
84        VALUES                        (?,           ?,                        ?,           ?,
85                                       ?,           ?,                        ?,           ?,
86                                       ?,           ?,                        ?,           ?)|;
87   my $h_insert = prepare_query($form, $dbh, $q_insert);
88
89   my $q_reference =
90     qq|SELECT arap.invnumber,
91          (SELECT COUNT(at.*)
92           FROM acc_trans at
93           LEFT JOIN chart c ON (at.chart_id = c.id)
94           WHERE (at.trans_id = ?)
95             AND (c.link LIKE '%${ARAP}_paid%'))
96          +
97          (SELECT COUNT(sei.*)
98           FROM sepa_export_items sei
99           WHERE (sei.ap_id = ?))
100          AS num_payments
101        FROM ${arap} arap
102        WHERE id = ?|;
103   my $h_reference = prepare_query($form, $dbh, $q_reference);
104
105   my @now         = localtime;
106
107   foreach my $transfer (@{ $params{bank_transfers} }) {
108     if (!$transfer->{reference}) {
109       do_statement($form, $h_reference, $q_reference, (conv_i($transfer->{"${arap}_id"})) x 3);
110
111       my ($invnumber, $num_payments) = $h_reference->fetchrow_array();
112       $num_payments++;
113
114       $transfer->{reference} = "${invnumber}-${num_payments}";
115     }
116
117     $h_item_id->execute();
118     my ($item_id)      = $h_item_id->fetchrow_array();
119
120     my $end_to_end_id  = strftime "LXO%Y%m%d%H%M%S", localtime;
121     my $item_id_len    = length "$item_id";
122     my $num_zeroes     = 35 - $item_id_len - length $end_to_end_id;
123     $end_to_end_id    .= '0' x $num_zeroes if (0 < $num_zeroes);
124     $end_to_end_id    .= $item_id;
125     $end_to_end_id     = substr $end_to_end_id, 0, 35;
126
127     my @values = ($item_id,                          $export_id,
128                   conv_i($transfer->{"${arap}_id"}), conv_i($transfer->{chart_id}),
129                   $transfer->{amount},               conv_date($transfer->{requested_execution_date}),
130                   $transfer->{reference},            $end_to_end_id,
131                   map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic) } qw(our vc));
132
133     do_statement($form, $h_insert, $q_insert, @values);
134   }
135
136   $h_insert->finish();
137   $h_item_id->finish();
138
139   $dbh->commit() unless ($params{dbh});
140
141   $main::lxdebug->leave_sub();
142
143   return $export_id;
144 }
145
146 sub retrieve_export {
147   $main::lxdebug->enter_sub();
148
149   my $self     = shift;
150   my %params   = @_;
151
152   Common::check_params(\%params, qw(id vc));
153
154   my $myconfig = \%main::myconfig;
155   my $form     = $main::form;
156   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
157   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
158
159   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
160
161   my ($joins, $columns);
162
163   if ($params{details}) {
164     $columns = ', arap.invoice';
165     $joins   = "LEFT JOIN ${arap} arap ON (se.${arap}_id = arap.id)";
166   }
167
168   my $query =
169     qq|SELECT se.*,
170          CASE WHEN COALESCE(e.name, '') <> '' THEN e.name ELSE e.login END AS employee
171        FROM sepa_export se
172        LEFT JOIN employee e ON (se.employee_id = e.id)
173        WHERE se.id = ?|;
174
175   my $export = selectfirst_hashref_query($form, $dbh, $query, conv_i($params{id}));
176
177   if ($export->{id}) {
178     my ($columns, $joins);
179
180     if ($params{details}) {
181       $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|;
182       $joins   = qq|LEFT JOIN ${arap} arap ON (sei.${arap}_id = arap.id)
183                     LEFT JOIN ${vc} vc     ON (arap.${vc}_id  = vc.id)
184                     LEFT JOIN chart c      ON (sei.chart_id   = c.id)|;
185     }
186
187     $query = qq|SELECT sei.*
188                   $columns
189                 FROM sepa_export_items sei
190                 $joins
191                 WHERE sei.sepa_export_id = ?|;
192
193     $export->{items} = selectall_hashref_query($form, $dbh, $query, conv_i($params{id}));
194
195   } else {
196     $export->{items} = [];
197   }
198
199   $main::lxdebug->leave_sub();
200
201   return $export;
202 }
203
204 sub close_export {
205   $main::lxdebug->enter_sub();
206
207   my $self     = shift;
208   my %params   = @_;
209
210   Common::check_params(\%params, qw(id));
211
212   my $myconfig = \%main::myconfig;
213   my $form     = $main::form;
214
215   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
216
217   my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
218   my $placeholders = join ', ', ('?') x scalar @ids;
219   my $query        = qq|UPDATE sepa_export SET closed = TRUE WHERE id IN ($placeholders)|;
220
221   do_query($form, $dbh, $query, map { conv_i($_) } @ids);
222
223   $dbh->commit() unless ($params{dbh});
224
225   $main::lxdebug->leave_sub();
226 }
227
228 sub list_exports {
229   $main::lxdebug->enter_sub();
230
231   my $self     = shift;
232   my %params   = @_;
233
234   my $myconfig = \%main::myconfig;
235   my $form     = $main::form;
236   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
237   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
238
239   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
240
241   my %sort_columns = (
242     'id'          => [ 'se.id',                ],
243     'export_date' => [ 'se.itime',             ],
244     'employee'    => [ 'e.name',      'se.id', ],
245     'executed'    => [ 'se.executed', 'se.id', ],
246     'closed'      => [ 'se.closed',   'se.id', ],
247     );
248
249   my %sort_spec = create_sort_spec('defs' => \%sort_columns, 'default' => 'id', 'column' => $params{sortorder}, 'dir' => $params{sortdir});
250
251   my (@where, @values, @where_sub, @values_sub, %joins_sub);
252
253   my $filter = $params{filter} || { };
254
255   foreach (qw(executed closed)) {
256     push @where, $filter->{$_} ? "se.$_" : "NOT se.$_" if (exists $filter->{$_});
257   }
258
259   my %operators = ('from' => '>=',
260                    'to'   => '<=');
261
262   foreach my $dir (qw(from to)) {
263     next unless ($filter->{"export_date_${dir}"});
264     push @where,  "se.itime $operators{$dir} ?::date";
265     push @values, $filter->{"export_date_${dir}"};
266   }
267
268   if ($filter->{invnumber}) {
269     push @where_sub,  "arap.invnumber ILIKE ?";
270     push @values_sub, '%' . $filter->{invnumber} . '%';
271     $joins_sub{$arap} = 1;
272   }
273
274   if ($filter->{vc}) {
275     push @where_sub,  "vc.name ILIKE ?";
276     push @values_sub, '%' . $filter->{vc} . '%';
277     $joins_sub{$arap} = 1;
278     $joins_sub{vc}    = 1;
279   }
280
281   foreach my $type (qw(requested_execution execution)) {
282     foreach my $dir (qw(from to)) {
283       next unless ($filter->{"${type}_date_${dir}"});
284       push @where_sub,  "(items.${type}_date IS NOT NULL) AND (items.${type}_date $operators{$dir} ?)";
285       push @values_sub, $filter->{"${type}_date_${_}"};
286     }
287   }
288
289   if (@where_sub) {
290     my $joins_sub  = '';
291     $joins_sub    .= " LEFT JOIN ${arap} arap ON (items.${arap}_id = arap.id)" if ($joins_sub{$arap});
292     $joins_sub    .= " LEFT JOIN ${vc} vc      ON (arap.${vc}_id   = vc.id)"   if ($joins_sub{vc});
293
294     my $where_sub  = join(' AND ', map { "(${_})" } @where_sub);
295
296     my $query_sub  = qq|se.id IN (SELECT items.sepa_export_id
297                                   FROM sepa_export_items items
298                                   $joins_sub
299                                   WHERE $where_sub)|;
300
301     push @where,  $query_sub;
302     push @values, @values_sub;
303   }
304
305   push @where,  'se.vc = ?';
306   push @values, $vc;
307
308   my $where = @where ? ' WHERE ' . join(' AND ', map { "(${_})" } @where) : '';
309
310   my $query =
311     qq|SELECT se.id, se.employee_id, se.executed, se.closed, itime::date AS export_date,
312          e.name AS employee
313        FROM sepa_export se
314        LEFT JOIN (
315          SELECT emp.id,
316            CASE WHEN COALESCE(emp.name, '') <> '' THEN emp.name ELSE emp.login END AS name
317          FROM employee emp
318        ) AS e ON (se.employee_id = e.id)
319        $where
320        ORDER BY $sort_spec{sql}|;
321
322   my $results = selectall_hashref_query($form, $dbh, $query, @values);
323
324   $main::lxdebug->leave_sub();
325
326   return $results;
327 }
328
329 sub post_payment {
330   $main::lxdebug->enter_sub();
331
332   my $self     = shift;
333   my %params   = @_;
334
335   Common::check_params(\%params, qw(items));
336
337   my $myconfig = \%main::myconfig;
338   my $form     = $main::form;
339   my $vc       = $params{vc} eq 'customer' ? 'customer' : 'vendor';
340   my $arap     = $params{vc} eq 'customer' ? 'ar'       : 'ap';
341   my $mult     = $params{vc} eq 'customer' ? -1         : 1;
342   my $ARAP     = uc $arap;
343
344   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
345
346   my @items    = ref $params{items} eq 'ARRAY' ? @{ $params{items} } : ($params{items});
347
348   my %handles  = (
349     'get_item'       => [ qq|SELECT sei.*
350                              FROM sepa_export_items sei
351                              WHERE sei.id = ?| ],
352
353     'get_arap'       => [ qq|SELECT at.chart_id
354                              FROM acc_trans at
355                              LEFT JOIN chart c ON (at.chart_id = c.id)
356                              WHERE (trans_id = ?)
357                                AND ((c.link LIKE '%:${ARAP}') OR (c.link LIKE '${ARAP}:%') OR (c.link = '${ARAP}'))
358                              LIMIT 1| ],
359
360     'add_acc_trans'  => [ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate,       source, memo, taxkey, tax_id ,                                     chart_link)
361                              VALUES                (?,        ?,        ?,      ?,         current_date, ?,      '',   0,      (SELECT id FROM tax WHERE taxkey=0 LIMIT 1), (SELECT link FROM chart WHERE id=?))| ],
362
363     'update_arap'    => [ qq|UPDATE ${arap}
364                              SET paid = paid + ?
365                              WHERE id = ?| ],
366
367     'finish_item'    => [ qq|UPDATE sepa_export_items
368                              SET execution_date = ?, executed = TRUE
369                              WHERE id = ?| ],
370
371     'has_unexecuted' => [ qq|SELECT sei1.id
372                              FROM sepa_export_items sei1
373                              WHERE (sei1.sepa_export_id = (SELECT sei2.sepa_export_id
374                                                            FROM sepa_export_items sei2
375                                                            WHERE sei2.id = ?))
376                                AND NOT COALESCE(sei1.executed, FALSE)
377                              LIMIT 1| ],
378
379     'do_close'       => [ qq|UPDATE sepa_export
380                              SET executed = TRUE, closed = TRUE
381                              WHERE (id = ?)| ],
382     );
383
384   map { unshift @{ $_ }, prepare_query($form, $dbh, $_->[0]) } values %handles;
385
386   foreach my $item (@items) {
387     my $item_id = conv_i($item->{id});
388
389     # Retrieve the item data belonging to the ID.
390     do_statement($form, @{ $handles{get_item} }, $item_id);
391     my $orig_item = $handles{get_item}->[0]->fetchrow_hashref();
392
393     next if (!$orig_item);
394
395     # Retrieve the invoice's AR/AP chart ID.
396     do_statement($form, @{ $handles{get_arap} }, $orig_item->{"${arap}_id"});
397     my ($arap_chart_id) = $handles{get_arap}->[0]->fetchrow_array();
398
399     # Record the payment in acc_trans offsetting AR/AP.
400     do_statement($form, @{ $handles{add_acc_trans} }, $orig_item->{"${arap}_id"}, $arap_chart_id,         -1 * $mult * $orig_item->{amount}, $item->{execution_date}, '', $arap_chart_id);
401     do_statement($form, @{ $handles{add_acc_trans} }, $orig_item->{"${arap}_id"}, $orig_item->{chart_id},      $mult * $orig_item->{amount}, $item->{execution_date}, $orig_item->{reference},
402                                                       $orig_item->{chart_id});
403
404     # Update the invoice to reflect the new paid amount.
405     do_statement($form, @{ $handles{update_arap} }, $orig_item->{amount}, $orig_item->{"${arap}_id"});
406
407     # Update datepaid of invoice. set_datepaid (which has some extra logic)
408     # finds the date from acc_trans, where the payment has already been
409     # recorded above, so we don't need to explicitly pass
410     # $item->{execution_date}
411     IO->set_datepaid(table => "$arap", id => $orig_item->{"${arap}_id"}, dbh => $dbh);
412
413     # Update the item to reflect that it has been posted.
414     do_statement($form, @{ $handles{finish_item} }, $item->{execution_date}, $item_id);
415
416     # Check whether or not we can close the export itself if there are no unexecuted items left.
417     do_statement($form, @{ $handles{has_unexecuted} }, $item_id);
418     my ($has_unexecuted) = $handles{has_unexecuted}->[0]->fetchrow_array();
419
420     if (!$has_unexecuted) {
421       do_statement($form, @{ $handles{do_close} }, $orig_item->{sepa_export_id});
422     }
423   }
424
425   map { $_->[0]->finish() } values %handles;
426
427   $dbh->commit() unless ($params{dbh});
428
429   $main::lxdebug->leave_sub();
430 }
431
432 1;