X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FSEPA.pm;h=94579179bc559ab1def7075adafea7468d1aef77;hb=8e5b1f0e363dd22883e931006b8ac414bad9bbc5;hp=311b58e338d82825e3638e1b1aa72d0f6a234178;hpb=b6d6ad18d758a2725f46a6706b709fbf92d8d0eb;p=kivitendo-erp.git diff --git a/SL/SEPA.pm b/SL/SEPA.pm index 311b58e33..94579179b 100644 --- a/SL/SEPA.pm +++ b/SL/SEPA.pm @@ -4,7 +4,12 @@ use strict; use POSIX qw(strftime); +use Data::Dumper; use SL::DBUtils; +use SL::DB::Invoice; +use SL::DB::PurchaseInvoice; +use SL::Locale::String qw(t8); +use DateTime; sub retrieve_open_invoices { $main::lxdebug->enter_sub(); @@ -18,26 +23,41 @@ sub retrieve_open_invoices { my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap'; my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor'; - my $extra_columns = $arap eq 'ar' ? 'ar.direct_debit, ' : ''; + my $mandate = $params{vc} eq 'customer' ? " AND COALESCE(vc.mandator_id, '') <> '' AND vc.mandate_date_of_signature IS NOT NULL " : ''; + + # in query: for customers, use payment terms from invoice, for vendors use + # payment terms from vendor settings + # currently there is no option in vendor invoices for setting payment terms, + # so the vendor settings are always used + + my $payment_term_type = $params{vc} eq 'customer' ? "${arap}" : 'vc'; + + # open_amount is not the current open amount according to bookkeeping, but + # the open amount minus the SEPA transfer amounts that haven't been closed yet my $query = qq| - SELECT ${arap}.id, ${arap}.invnumber, ${arap}.${vc}_id as vc_id, ${arap}.amount AS invoice_amount, ${arap}.invoice, - vc.name AS vcname, vc.language_id, ${arap}.duedate as duedate, ${extra_columns} + SELECT ${arap}.id, ${arap}.invnumber, ${arap}.transdate, ${arap}.${vc}_id as vc_id, ${arap}.amount AS invoice_amount, ${arap}.invoice, + (${arap}.transdate + pt.terms_skonto) as skonto_date, (pt.percent_skonto * 100) as percent_skonto, + (${arap}.amount - (${arap}.amount * pt.percent_skonto)) as amount_less_skonto, + (${arap}.amount * pt.percent_skonto) as skonto_amount, + vc.name AS vcname, vc.language_id, ${arap}.duedate as duedate, ${arap}.direct_debit, - COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' AS vc_bank_info_ok, + COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' ${mandate} AS vc_bank_info_ok, ${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount FROM ${arap} LEFT JOIN ${vc} vc ON (${arap}.${vc}_id = vc.id) - LEFT JOIN (SELECT sei.ap_id, SUM(sei.amount) AS amount + LEFT JOIN (SELECT sei.${arap}_id, SUM(sei.amount) AS amount FROM sepa_export_items sei LEFT JOIN sepa_export se ON (sei.sepa_export_id = se.id) WHERE NOT se.closed AND (se.vc = '${vc}') - GROUP BY sei.ap_id) - AS open_transfers ON (${arap}.id = open_transfers.ap_id) + GROUP BY sei.${arap}_id) + AS open_transfers ON (${arap}.id = open_transfers.${arap}_id) + + LEFT JOIN payment_terms pt ON (${payment_term_type}.payment_id = pt.id) WHERE ${arap}.amount > (COALESCE(open_transfers.amount, 0) + ${arap}.paid) @@ -46,6 +66,21 @@ sub retrieve_open_invoices { my $results = selectall_hashref_query($form, $dbh, $query); + # add some more data to $results: + # create drop-down data for payment types and suggest amount to be paid according + # to open amount or skonto + + foreach my $result ( @$results ) { + my $invoice = $vc eq 'customer' ? SL::DB::Manager::Invoice->find_by( id => $result->{id} ) + : SL::DB::Manager::PurchaseInvoice->find_by( id => $result->{id} ); + + $invoice->get_payment_suggestions(sepa => 1); # consider amounts of open entries in sepa_export_items + $result->{skonto_amount} = $invoice->skonto_amount; + $result->{within_skonto_period} = $invoice->within_skonto_period; + $result->{invoice_amount_suggestion} = $invoice->{invoice_amount_suggestion}; + $result->{payment_select_options} = $invoice->{payment_select_options}; + }; + $main::lxdebug->leave_sub(); return $results; @@ -77,14 +112,18 @@ sub create_export { my $q_item_id = qq|SELECT nextval('id')|; my $h_item_id = prepare_query($form, $dbh, $q_item_id); + my $c_mandate = $params{vc} eq 'customer' ? ', vc_mandator_id, vc_mandate_date_of_signature' : ''; + my $p_mandate = $params{vc} eq 'customer' ? ', ?, ?' : ''; my $q_insert = qq|INSERT INTO sepa_export_items (id, sepa_export_id, ${arap}_id, chart_id, amount, requested_execution_date, reference, end_to_end_id, - our_iban, our_bic, vc_iban, vc_bic) + our_iban, our_bic, vc_iban, vc_bic, + skonto_amount, payment_type ${c_mandate}) VALUES (?, ?, ?, ?, ?, ?, ?, ?, - ?, ?, ?, ?)|; + ?, ?, ?, ?, + ?, ? ${p_mandate})|; my $h_insert = prepare_query($form, $dbh, $q_insert); my $q_reference = @@ -130,6 +169,19 @@ sub create_export { $transfer->{amount}, conv_date($transfer->{requested_execution_date}), $transfer->{reference}, $end_to_end_id, map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic) } qw(our vc)); + # save value of skonto_amount and payment_type + if ( $transfer->{payment_type} eq 'without_skonto' ) { + push(@values, 0); + } elsif ($transfer->{payment_type} eq 'difference_as_skonto' ) { + push(@values, $transfer->{amount}); + } elsif ($transfer->{payment_type} eq 'with_skonto_pt' ) { + push(@values, $transfer->{skonto_amount}); + } else { + die "illegal payment_type: " . $transfer->{payment_type} . "\n"; + }; + push(@values, $transfer->{payment_type}); + + push @values, $transfer->{vc_mandator_id}, conv_date($transfer->{vc_mandate_date_of_signature}) if $params{vc} eq 'customer'; do_statement($form, $h_insert, $q_insert, @values); } @@ -178,8 +230,10 @@ sub retrieve_export { if ($export->{id}) { my ($columns, $joins); + my $mandator_id = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : ''; + if ($params{details}) { - $columns = qq|, arap.invnumber, arap.invoice, arap.transdate AS reference_date, vc.name AS vc_name, c.accno AS chart_accno, c.description AS chart_description|; + $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}|; $joins = qq|LEFT JOIN ${arap} arap ON (sei.${arap}_id = arap.id) LEFT JOIN ${vc} vc ON (arap.${vc}_id = vc.id) LEFT JOIN chart c ON (sei.chart_id = c.id)|; @@ -272,6 +326,17 @@ sub list_exports { $joins_sub{$arap} = 1; } + if ($filter->{message_id}) { + push @values, '%' . $filter->{message_id} . '%'; + push @where, <{vc}) { push @where_sub, "vc.name ILIKE ?"; push @values_sub, '%' . $filter->{vc} . '%'; @@ -310,6 +375,15 @@ sub list_exports { my $query = qq|SELECT se.id, se.employee_id, se.executed, se.closed, itime::date AS export_date, + (SELECT COUNT(*) + FROM sepa_export_items sei + WHERE (sei.sepa_export_id = se.id)) AS num_invoices, + (SELECT SUM(sei.amount) + FROM sepa_export_items sei + WHERE (sei.sepa_export_id = se.id)) AS sum_amounts, + (SELECT string_agg(semi.message_id, ', ') + FROM sepa_export_message_ids semi + WHERE semi.sepa_export_id = se.id) AS message_ids, e.name AS employee FROM sepa_export se LEFT JOIN ( @@ -358,8 +432,8 @@ sub post_payment { AND ((c.link LIKE '%:${ARAP}') OR (c.link LIKE '${ARAP}:%') OR (c.link = '${ARAP}')) LIMIT 1| ], - 'add_acc_trans' => [ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo) - VALUES (?, ?, ?, ?, current_date, ?, '')| ], + 'add_acc_trans' => [ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, tax_id , chart_link) + VALUES (?, ?, ?, ?, current_date, ?, '', 0, (SELECT id FROM tax WHERE taxkey=0 LIMIT 1), (SELECT link FROM chart WHERE id=?))| ], 'update_arap' => [ qq|UPDATE ${arap} SET paid = paid + ? @@ -385,6 +459,7 @@ sub post_payment { map { unshift @{ $_ }, prepare_query($form, $dbh, $_->[0]) } values %handles; foreach my $item (@items) { + my $item_id = conv_i($item->{id}); # Retrieve the item data belonging to the ID. @@ -393,22 +468,25 @@ sub post_payment { next if (!$orig_item); - # Retrieve the invoice's AR/AP chart ID. - do_statement($form, @{ $handles{get_arap} }, $orig_item->{"${arap}_id"}); - my ($arap_chart_id) = $handles{get_arap}->[0]->fetchrow_array(); - - # Record the payment in acc_trans offsetting AR/AP. - do_statement($form, @{ $handles{add_acc_trans} }, $orig_item->{"${arap}_id"}, $arap_chart_id, -1 * $mult * $orig_item->{amount}, $item->{execution_date}, ''); - 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}); - - # Update the invoice to reflect the new paid amount. - do_statement($form, @{ $handles{update_arap} }, $orig_item->{amount}, $orig_item->{"${arap}_id"}); - - # Update datepaid of invoice. set_datepaid (which has some extra logic) - # finds the date from acc_trans, where the payment has already been - # recorded above, so we don't need to explicitly pass - # $item->{execution_date} - IO->set_datepaid(table => "$arap", id => $orig_item->{"${arap}_id"}, dbh => $dbh); + # fetch item_id via Rose (same id as orig_item) + my $sepa_export_item = SL::DB::Manager::SepaExportItem->find_by( id => $item_id); + + my $invoice; + + if ( $sepa_export_item->ar_id ) { + $invoice = SL::DB::Manager::Invoice->find_by( id => $sepa_export_item->ar_id); + } elsif ( $sepa_export_item->ap_id ) { + $invoice = SL::DB::Manager::PurchaseInvoice->find_by( id => $sepa_export_item->ap_id); + } else { + die "sepa_export_item needs either ar_id or ap_id\n"; + }; + + $invoice->pay_invoice(amount => $sepa_export_item->amount, + payment_type => $sepa_export_item->payment_type, + chart_id => $sepa_export_item->chart_id, + source => $sepa_export_item->reference, + transdate => $item->{execution_date}, # value from user form + ); # Update the item to reflect that it has been posted. do_statement($form, @{ $handles{finish_item} }, $item->{execution_date}, $item_id);