5 use POSIX qw(strftime);
10 use SL::DB::PurchaseInvoice;
12 use SL::Locale::String qw(t8);
15 sub retrieve_open_invoices {
16 $main::lxdebug->enter_sub();
21 my $myconfig = \%main::myconfig;
22 my $form = $main::form;
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';
29 my $mandate = $params{vc} eq 'customer' ? " AND COALESCE(vc.mandator_id, '') <> '' AND vc.mandate_date_of_signature IS NOT NULL " : '';
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
36 my $payment_term_type = $params{vc} eq 'customer' ? "${arap}" : 'vc';
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
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,
49 COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' ${mandate} AS vc_bank_info_ok,
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
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)
62 GROUP BY sei.${arap}_id)
63 AS open_transfers ON (${arap}.id = open_transfers.${arap}_id)
65 LEFT JOIN payment_terms pt ON (${payment_term_type}.payment_id = pt.id)
67 WHERE ${arap}.amount > (COALESCE(open_transfers.amount, 0) + ${arap}.paid)
69 ORDER BY lower(vc.name) ASC, lower(${arap}.invnumber) ASC
71 # $main::lxdebug->message(LXDebug->DEBUG2(),"sepa add query:".$query);
73 my $results = selectall_hashref_query($form, $dbh, $query);
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
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} );
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};
90 $main::lxdebug->leave_sub();
96 my ($self, %params) = @_;
97 $main::lxdebug->enter_sub();
99 my $rc = SL::DB->client->with_transaction(\&_create_export, $self, %params);
101 $::lxdebug->leave_sub;
109 Common::check_params(\%params, qw(employee bank_transfers vc));
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';
117 my $dbh = $params{dbh} || SL::DB->client->dbh;
119 my ($export_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('sepa_export_id_seq')|);
121 qq|INSERT INTO sepa_export (id, employee_id, vc)
122 VALUES (?, (SELECT id
124 WHERE login = ?), ?)|;
125 do_query($form, $dbh, $query, $export_id, $params{employee}, $vc);
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' ? ', ?, ?' : '';
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})
141 my $h_insert = prepare_query($form, $dbh, $q_insert);
144 qq|SELECT arap.invnumber,
147 LEFT JOIN chart c ON (at.chart_id = c.id)
148 WHERE (at.trans_id = ?)
149 AND (c.link LIKE '%${ARAP}_paid%'))
152 FROM sepa_export_items sei
153 WHERE (sei.ap_id = ?))
157 my $h_reference = prepare_query($form, $dbh, $q_reference);
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);
165 my ($invnumber, $num_payments) = $h_reference->fetchrow_array();
168 $transfer->{reference} = "${invnumber}-${num_payments}";
171 $h_item_id->execute() || $::form->dberror($q_item_id);
172 my ($item_id) = $h_item_id->fetchrow_array();
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;
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' ) {
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});
194 die "illegal payment_type: " . $transfer->{payment_type} . "\n";
196 push(@values, $transfer->{payment_type});
198 push @values, $transfer->{vc_mandator_id}, conv_date($transfer->{vc_mandate_date_of_signature}) if $params{vc} eq 'customer';
200 do_statement($form, $h_insert, $q_insert, @values);
204 $h_item_id->finish();
209 sub retrieve_export {
210 $main::lxdebug->enter_sub();
215 Common::check_params(\%params, qw(id vc));
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';
222 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
224 my ($joins, $columns);
226 if ($params{details}) {
227 $columns = ', arap.invoice';
228 $joins = "LEFT JOIN ${arap} arap ON (se.${arap}_id = arap.id)";
233 CASE WHEN COALESCE(e.name, '') <> '' THEN e.name ELSE e.login END AS employee
235 LEFT JOIN employee e ON (se.employee_id = e.id)
238 my $export = selectfirst_hashref_query($form, $dbh, $query, conv_i($params{id}));
241 my ($columns, $joins);
243 my $mandator_id = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
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)|;
252 $query = qq|SELECT sei.*
254 FROM sepa_export_items sei
256 WHERE sei.sepa_export_id = ?|;
258 $export->{items} = selectall_hashref_query($form, $dbh, $query, conv_i($params{id}));
261 $export->{items} = [];
264 $main::lxdebug->leave_sub();
270 $main::lxdebug->enter_sub();
275 Common::check_params(\%params, qw(id));
277 my $myconfig = \%main::myconfig;
278 my $form = $main::form;
280 SL::DB->client->with_transaction(sub {
281 my $dbh = $params{dbh} || SL::DB->client->dbh;
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)|;
287 do_query($form, $dbh, $query, map { conv_i($_) } @ids);
289 }) or do { die SL::DB->client->error };
291 $main::lxdebug->leave_sub();
295 $main::lxdebug->enter_sub();
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';
305 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
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', ],
315 my %sort_spec = create_sort_spec('defs' => \%sort_columns, 'default' => 'id', 'column' => $params{sortorder}, 'dir' => $params{sortdir});
317 my (@where, @values, @where_sub, @values_sub, %joins_sub);
319 my $filter = $params{filter} || { };
321 foreach (qw(executed closed)) {
322 push @where, $filter->{$_} ? "se.$_" : "NOT se.$_" if (exists $filter->{$_});
325 my %operators = ('from' => '>=',
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}"};
334 if ($filter->{invnumber}) {
335 push @where_sub, "arap.invnumber ILIKE ?";
336 push @values_sub, like($filter->{invnumber});
337 $joins_sub{$arap} = 1;
340 if ($filter->{message_id}) {
341 push @values, like($filter->{message_id});
344 SELECT sepa_export_id
345 FROM sepa_export_message_ids
346 WHERE message_id ILIKE ?
352 push @where_sub, "vc.name ILIKE ?";
353 push @values_sub, like($filter->{vc});
354 $joins_sub{$arap} = 1;
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_${_}"};
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});
371 my $where_sub = join(' AND ', map { "(${_})" } @where_sub);
373 my $query_sub = qq|se.id IN (SELECT items.sepa_export_id
374 FROM sepa_export_items items
378 push @where, $query_sub;
379 push @values, @values_sub;
382 push @where, 'se.vc = ?';
385 my $where = @where ? ' WHERE ' . join(' AND ', map { "(${_})" } @where) : '';
388 qq|SELECT se.id, se.employee_id, se.executed, se.closed, itime::date AS export_date,
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,
402 CASE WHEN COALESCE(emp.name, '') <> '' THEN emp.name ELSE emp.login END AS name
404 ) AS e ON (se.employee_id = e.id)
406 ORDER BY $sort_spec{sql}|;
408 my $results = selectall_hashref_query($form, $dbh, $query, @values);
410 $main::lxdebug->leave_sub();
416 my ($self, %params) = @_;
417 $main::lxdebug->enter_sub();
419 my $rc = SL::DB->client->with_transaction(\&_post_payment, $self, %params);
421 $::lxdebug->leave_sub;
429 Common::check_params(\%params, qw(items));
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;
438 my $dbh = $params{dbh} || SL::DB->client->dbh;
440 my @items = ref $params{items} eq 'ARRAY' ? @{ $params{items} } : ($params{items});
443 'get_item' => [ qq|SELECT sei.*
444 FROM sepa_export_items sei
447 'get_arap' => [ qq|SELECT at.chart_id
449 LEFT JOIN chart c ON (at.chart_id = c.id)
451 AND ((c.link LIKE '%:${ARAP}') OR (c.link LIKE '${ARAP}:%') OR (c.link = '${ARAP}'))
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=?))| ],
457 'update_arap' => [ qq|UPDATE ${arap}
461 'finish_item' => [ qq|UPDATE sepa_export_items
462 SET execution_date = ?, executed = TRUE
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
470 AND NOT COALESCE(sei1.executed, FALSE)
473 'do_close' => [ qq|UPDATE sepa_export
474 SET executed = TRUE, closed = TRUE
478 map { unshift @{ $_ }, prepare_query($form, $dbh, $_->[0]) } values %handles;
480 foreach my $item (@items) {
482 my $item_id = conv_i($item->{id});
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();
488 next if (!$orig_item);
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);
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);
500 die "sepa_export_item needs either ar_id or ap_id\n";
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
510 # Update the item to reflect that it has been posted.
511 do_statement($form, @{ $handles{finish_item} }, $item->{execution_date}, $item_id);
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();
517 if (!$has_unexecuted) {
518 do_statement($form, @{ $handles{do_close} }, $orig_item->{sepa_export_id});
522 map { $_->[0]->finish() } values %handles;