X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FSEPA.pm;h=fc92bcf162ba65d29eddfad057f48ee3296155f4;hb=d21bdcaf73d55909d130fbbe26137fd45114adde;hp=53fd2e1aa2aad6561a1b9c5c19573f6ec0e2606d;hpb=6b1f644cf4630cd22a91892eac6c3a2dfb25659c;p=kivitendo-erp.git diff --git a/SL/SEPA.pm b/SL/SEPA.pm index 53fd2e1aa..fc92bcf16 100644 --- a/SL/SEPA.pm +++ b/SL/SEPA.pm @@ -4,7 +4,14 @@ use strict; use POSIX qw(strftime); +use Data::Dumper; use SL::DBUtils; +use SL::DB::Invoice; +use SL::DB::PurchaseInvoice; +use SL::DB; +use SL::Locale::String qw(t8); +use DateTime; +use Carp; sub retrieve_open_invoices { $main::lxdebug->enter_sub(); @@ -18,84 +25,128 @@ 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 $vc_vc_id = $params{vc} eq 'customer' ? 'c_vendor_id' : 'v_customer_id'; + my $mandate = $params{vc} eq 'customer' ? " AND COALESCE(vc.mandator_id, '') <> '' AND vc.mandate_date_of_signature IS NOT NULL " : ''; + + # 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, ${arap}.amount AS invoice_amount, ${arap}.invoice, - vc.name AS vcname, ${arap}.duedate as duedate, + 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, + vc.${vc_vc_id} as vc_vc_id, - 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 + ${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount, + COALESCE(open_transfers.amount, 0) AS transfer_amount, + pt.description as pt_description 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) + SUM(COALESCE(sei.skonto_amount,0)) 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 (${arap}.payment_id = pt.id) WHERE ${arap}.amount > (COALESCE(open_transfers.amount, 0) + ${arap}.paid) ORDER BY lower(vc.name) ASC, lower(${arap}.invnumber) ASC |; + # $main::lxdebug->message(LXDebug->DEBUG2(),"sepa add query:".$query); 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; } sub create_export { + my ($self, %params) = @_; $main::lxdebug->enter_sub(); + my $rc = SL::DB->client->with_transaction(\&_create_export, $self, %params); + + $::lxdebug->leave_sub; + return $rc; +} + +sub _create_export { my $self = shift; my %params = @_; - Common::check_params(\%params, qw(employee bank_transfers)); + Common::check_params(\%params, qw(employee bank_transfers vc)); my $myconfig = \%main::myconfig; my $form = $main::form; + my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap'; + my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor'; + my $ARAP = uc $arap; - my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + my $dbh = $params{dbh} || SL::DB->client->dbh; my ($export_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('sepa_export_id_seq')|); my $query = - qq|INSERT INTO sepa_export (id, employee_id) + qq|INSERT INTO sepa_export (id, employee_id, vc) VALUES (?, (SELECT id FROM employee - WHERE login = ?))|; - do_query($form, $dbh, $query, $export_id, $params{employee}); + WHERE login = ?), ?)|; + do_query($form, $dbh, $query, $export_id, $params{employee}, $vc); 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, ap_id, chart_id, + 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, vendor_iban, vendor_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 = - qq|SELECT ap.invnumber, + qq|SELECT arap.invnumber, (SELECT COUNT(at.*) FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (at.trans_id = ?) - AND (c.link LIKE '%AP_paid%')) + AND (c.link LIKE '%${ARAP}_paid%')) + (SELECT COUNT(sei.*) FROM sepa_export_items sei WHERE (sei.ap_id = ?)) AS num_payments - FROM ap + FROM ${arap} arap WHERE id = ?|; my $h_reference = prepare_query($form, $dbh, $q_reference); @@ -103,7 +154,7 @@ sub create_export { foreach my $transfer (@{ $params{bank_transfers} }) { if (!$transfer->{reference}) { - do_statement($form, $h_reference, $q_reference, (conv_i($transfer->{ap_id})) x 3); + do_statement($form, $h_reference, $q_reference, (conv_i($transfer->{"${arap}_id"})) x 3); my ($invnumber, $num_payments) = $h_reference->fetchrow_array(); $num_payments++; @@ -111,7 +162,7 @@ sub create_export { $transfer->{reference} = "${invnumber}-${num_payments}"; } - $h_item_id->execute(); + $h_item_id->execute() || $::form->dberror($q_item_id); my ($item_id) = $h_item_id->fetchrow_array(); my $end_to_end_id = strftime "LXO%Y%m%d%H%M%S", localtime; @@ -121,11 +172,24 @@ sub create_export { $end_to_end_id .= $item_id; $end_to_end_id = substr $end_to_end_id, 0, 35; - my @values = ($item_id, $export_id, - conv_i($transfer->{ap_id}), conv_i($transfer->{chart_id}), - $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 vendor)); + my @values = ($item_id, $export_id, + conv_i($transfer->{"${arap}_id"}), conv_i($transfer->{chart_id}), + $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); } @@ -133,10 +197,6 @@ sub create_export { $h_insert->finish(); $h_item_id->finish(); - $dbh->commit() unless ($params{dbh}); - - $main::lxdebug->leave_sub(); - return $export_id; } @@ -146,18 +206,20 @@ sub retrieve_export { my $self = shift; my %params = @_; - Common::check_params(\%params, qw(id)); + Common::check_params(\%params, qw(id vc)); my $myconfig = \%main::myconfig; my $form = $main::form; + my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor'; + my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap'; my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); my ($joins, $columns); if ($params{details}) { - $columns = ', ap.invoice'; - $joins = 'LEFT JOIN ap ON (se.ap_id = ap.id)'; + $columns = ', arap.invoice'; + $joins = "LEFT JOIN ${arap} arap ON (se.${arap}_id = arap.id)"; } my $query = @@ -172,11 +234,13 @@ 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|, ap.invnumber, ap.invoice, v.name AS vendor_name, c.accno AS chart_accno, c.description AS chart_description|; - $joins = qq|LEFT JOIN ap ON (sei.ap_id = ap.id) - LEFT JOIN vendor v ON (ap.vendor_id = v.id) - LEFT JOIN chart c ON (sei.chart_id = c.id)|; + $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)|; } $query = qq|SELECT sei.* @@ -184,6 +248,7 @@ sub retrieve_export { FROM sepa_export_items sei $joins WHERE sei.sepa_export_id = ?|; + $export->{items} = selectall_hashref_query($form, $dbh, $query, conv_i($params{id})); } else { @@ -206,15 +271,35 @@ sub close_export { my $myconfig = \%main::myconfig; my $form = $main::form; - my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + SL::DB->client->with_transaction(sub { + my $dbh = $params{dbh} || SL::DB->client->dbh; + + my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id}); + my $placeholders = join ', ', ('?') x scalar @ids; + my $query = qq|UPDATE sepa_export SET closed = TRUE WHERE id IN ($placeholders)|; + + do_query($form, $dbh, $query, map { conv_i($_) } @ids); + 1; + }) or do { die SL::DB->client->error }; + + $main::lxdebug->leave_sub(); +} + +sub undo_export { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(id)); - my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id}); - my $placeholders = join ', ', ('?') x scalar @ids; - my $query = qq|UPDATE sepa_export SET closed = TRUE WHERE id IN ($placeholders)|; + my $sepa_export = SL::DB::Manager::SepaExport->find_by(id => $params{id}); - do_query($form, $dbh, $query, map { conv_i($_) } @ids); + croak "Not a valid SEPA Export id: $params{id}" unless $sepa_export; + croak "Cannot undo closed exports." if $sepa_export->closed; + croak "Cannot undo executed exports." if $sepa_export->executed; - $dbh->commit() unless ($params{dbh}); + die "Could not undo $sepa_export->id" if !$sepa_export->delete(); $main::lxdebug->leave_sub(); } @@ -227,6 +312,8 @@ sub list_exports { my $myconfig = \%main::myconfig; my $form = $main::form; + my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor'; + my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap'; my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); @@ -258,16 +345,27 @@ sub list_exports { } if ($filter->{invnumber}) { - push @where_sub, "ap.invnumber ILIKE ?"; - push @values_sub, '%' . $filter->{invnumber} . '%'; - $joins_sub{ap} = 1; + push @where_sub, "arap.invnumber ILIKE ?"; + push @values_sub, like($filter->{invnumber}); + $joins_sub{$arap} = 1; } - if ($filter->{vendor}) { - push @where_sub, "v.name ILIKE ?"; - push @values_sub, '%' . $filter->{vendor} . '%'; - $joins_sub{ap} = 1; - $joins_sub{vendor} = 1; + if ($filter->{message_id}) { + push @values, like($filter->{message_id}); + push @where, <{vc}) { + push @where_sub, "vc.name ILIKE ?"; + push @values_sub, like($filter->{vc}); + $joins_sub{$arap} = 1; + $joins_sub{vc} = 1; } foreach my $type (qw(requested_execution execution)) { @@ -280,8 +378,8 @@ sub list_exports { if (@where_sub) { my $joins_sub = ''; - $joins_sub .= ' LEFT JOIN ap ON (items.ap_id = ap.id)' if ($joins_sub{ap}); - $joins_sub .= ' LEFT JOIN vendor v ON (ap.vendor_id = v.id)' if ($joins_sub{vendor}); + $joins_sub .= " LEFT JOIN ${arap} arap ON (items.${arap}_id = arap.id)" if ($joins_sub{$arap}); + $joins_sub .= " LEFT JOIN ${vc} vc ON (arap.${vc}_id = vc.id)" if ($joins_sub{vc}); my $where_sub = join(' AND ', map { "(${_})" } @where_sub); @@ -294,10 +392,22 @@ sub list_exports { push @values, @values_sub; } - my $where = ' WHERE ' . join(' AND ', map { "(${_})" } @where) if (@where); + push @where, 'se.vc = ?'; + push @values, $vc; + + my $where = @where ? ' WHERE ' . join(' AND ', map { "(${_})" } @where) : ''; 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 ( @@ -316,8 +426,16 @@ sub list_exports { } sub post_payment { + my ($self, %params) = @_; $main::lxdebug->enter_sub(); + my $rc = SL::DB->client->with_transaction(\&_post_payment, $self, %params); + + $::lxdebug->leave_sub; + return $rc; +} + +sub _post_payment { my $self = shift; my %params = @_; @@ -325,8 +443,12 @@ sub post_payment { my $myconfig = \%main::myconfig; my $form = $main::form; + my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor'; + my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap'; + my $mult = $params{vc} eq 'customer' ? -1 : 1; + my $ARAP = uc $arap; - my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + my $dbh = $params{dbh} || SL::DB->client->dbh; my @items = ref $params{items} eq 'ARRAY' ? @{ $params{items} } : ($params{items}); @@ -335,17 +457,17 @@ sub post_payment { FROM sepa_export_items sei WHERE sei.id = ?| ], - 'get_ap' => [ qq|SELECT at.chart_id + 'get_arap' => [ qq|SELECT at.chart_id FROM acc_trans at LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) - AND ((c.link LIKE '%:AP') OR (c.link LIKE 'AP:%') OR (c.link = 'AP')) + 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_ap' => [ qq|UPDATE ap + 'update_arap' => [ qq|UPDATE ${arap} SET paid = paid + ? WHERE id = ?| ], @@ -369,6 +491,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. @@ -377,16 +500,25 @@ sub post_payment { next if (!$orig_item); - # Retrieve the invoice's AP chart ID. - do_statement($form, @{ $handles{get_ap} }, $orig_item->{ap_id}); - my ($ap_chart_id) = $handles{get_ap}->[0]->fetchrow_array(); + # 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; - # Record the payment in acc_trans offsetting AP. - do_statement($form, @{ $handles{add_acc_trans} }, $orig_item->{ap_id}, $ap_chart_id, -1 * $orig_item->{amount}, $item->{execution_date}, ''); - do_statement($form, @{ $handles{add_acc_trans} }, $orig_item->{ap_id}, $orig_item->{chart_id}, $orig_item->{amount}, $item->{execution_date}, $orig_item->{reference}); + 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"; + }; - # Update the invoice to reflect the new paid amount. - do_statement($form, @{ $handles{update_ap} }, $orig_item->{amount}, $orig_item->{ap_id}); + $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); @@ -402,9 +534,53 @@ sub post_payment { map { $_->[0]->finish() } values %handles; - $dbh->commit() unless ($params{dbh}); - - $main::lxdebug->leave_sub(); + return 1; } 1; + + +__END__ + +=head1 NAME + +SL::SEPA - Base class for SEPA objects + +=head1 SYNOPSIS + + # get all open invoices we like to pay via SEPA + my $invoices = SL::SEPA->retrieve_open_invoices(vc => 'vendor'); + + # add some IBAN and purposes for open transaction + # and assign this to a SEPA export + my $id = SL::SEPA->create_export('employee' => $::myconfig{login}, + 'bank_transfers' => \@bank_transfers, + 'vc' => 'vendor'); + +=head1 DESCRIPTIONS + +This is the base class for SEPA. SEPA and the underlying directories +(SEPA::XML etc) are used to genereate valid XML files for the SEPA +(Single European Payment Area) specification and offers this structure +as a download via a xml file. + +An export can have one or more transaction which have to +comply to the specification (IBAN, BIC, amount, purpose, etc). + +Furthermore kivitendo sepa exports have two +valid states: Open or closed and executed or not executed. + +The state closed can be set via a user interface and the +state executed is automatically assigned if the action payment +is triggered. + +=head1 FUNCTIONS + +=head2 C $sepa_export_id + +Needs a valid sepa_export id and deletes the sepa export if +the state of the export is neither executed nor closed. +Returns undef if the deletion was successfully. +Otherwise the function just dies with a short notice of the id. + +=cut