X-Git-Url: http://wagnertech.de/git?p=kivitendo-erp.git;a=blobdiff_plain;f=SL%2FSEPA.pm;fp=SL%2FSEPA.pm;h=fc92bcf162ba65d29eddfad057f48ee3296155f4;hp=94579179bc559ab1def7075adafea7468d1aef77;hb=53593baa211863fbf66540cf1bcc36c8fb37257f;hpb=deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44 diff --git a/SL/SEPA.pm b/SL/SEPA.pm index 94579179b..fc92bcf16 100644 --- a/SL/SEPA.pm +++ b/SL/SEPA.pm @@ -8,8 +8,10 @@ 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(); @@ -23,16 +25,10 @@ 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 " : ''; - # 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 = @@ -42,14 +38,17 @@ sub retrieve_open_invoices { (${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, '') <> '' ${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.${arap}_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 @@ -57,12 +56,13 @@ sub retrieve_open_invoices { 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) + 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); @@ -87,8 +87,16 @@ sub retrieve_open_invoices { } 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 = @_; @@ -100,7 +108,7 @@ sub create_export { 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 = @@ -154,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; @@ -189,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; } @@ -267,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(); } @@ -322,12 +346,12 @@ sub list_exports { if ($filter->{invnumber}) { push @where_sub, "arap.invnumber ILIKE ?"; - push @values_sub, '%' . $filter->{invnumber} . '%'; + push @values_sub, like($filter->{invnumber}); $joins_sub{$arap} = 1; } if ($filter->{message_id}) { - push @values, '%' . $filter->{message_id} . '%'; + push @values, like($filter->{message_id}); push @where, <{vc}) { push @where_sub, "vc.name ILIKE ?"; - push @values_sub, '%' . $filter->{vc} . '%'; + push @values_sub, like($filter->{vc}); $joins_sub{$arap} = 1; $joins_sub{vc} = 1; } @@ -402,8 +426,16 @@ SQL } 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 = @_; @@ -416,7 +448,7 @@ sub post_payment { 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}); @@ -502,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