inventory_accno_id aufräumen

[kivitendo-erp.git] / SL / SEPA.pm

diff --git a/SL/SEPA.pm b/SL/SEPA.pm
index 2b5738d..7724a52 100644 (file)
--- a/SL/SEPA.pm
+++ b/SL/SEPA.pm
@@ -8,6 +8,7 @@ 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;
 
@@ -23,6 +24,7 @@ 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 " : '';
 
@@ -42,10 +44,13 @@ 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)
@@ -63,6 +68,7 @@ sub retrieve_open_invoices {
 
        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 +93,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 +114,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       =
@@ -189,10 +203,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 +277,16 @@ 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)|;
+    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);
-
-  $dbh->commit() unless ($params{dbh});
+    do_query($form, $dbh, $query, map { conv_i($_) } @ids);
+    1;
+  }) or do { die SL::DB->client->error };
 
   $main::lxdebug->leave_sub();
 }
@@ -322,12 +333,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,  <<SQL;
       se.id IN (
         SELECT sepa_export_id
@@ -339,7 +350,7 @@ SQL
 
   if ($filter->{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 +413,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 +435,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 +521,7 @@ sub post_payment {
 
   map { $_->[0]->finish() } values %handles;
 
-  $dbh->commit() unless ($params{dbh});
-
-  $main::lxdebug->leave_sub();
+  return 1;
 }
 
 1;