X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDN.pm;h=5dd6fe91881379e82fae2dafa0d2a5c0209683ec;hb=e1f93c184a9f6fe0826306383f23c454011dc9b3;hp=c9289e46fb10442e2a6b020ffa8a08c58a3d2d4b;hpb=4bd1e2f8b588972f10f92728301feacefd5ee4dd;p=kivitendo-erp.git

diff --git a/SL/DN.pm b/SL/DN.pm
index c9289e46f..5dd6fe918 100644
--- a/SL/DN.pm
+++ b/SL/DN.pm
@@ -45,6 +45,8 @@ use SL::Template;
 use SL::DB::Printer;
 use SL::DB::Language;
 use SL::TransNumber;
+use SL::Util qw(trim);
+use SL::DB;
 
 use strict;
 
@@ -54,7 +56,7 @@ sub get_config {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $query =
     qq|SELECT * | .
@@ -72,18 +74,23 @@ sub get_config {
        FROM defaults|;
   ($form->{AR_amount_fee}, $form->{AR_amount_interest}, $form->{AR}) = selectrow_query($form, $dbh, $query);
 
-  $dbh->disconnect();
-
   $main::lxdebug->leave_sub();
 }
 
 sub save_config {
+  my ($self, $myconfig, $form) = @_;
   $main::lxdebug->enter_sub();
 
+  my $rc = SL::DB->client->with_transaction(\&_save_config, $self, $myconfig, $form);
+
+  $::lxdebug->leave_sub;
+  return $rc;
+}
+
+sub _save_config {
   my ($self, $myconfig, $form) = @_;
 
-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my ($query, @values);
 
@@ -131,10 +138,7 @@ sub save_config {
   @values = (conv_i($form->{AR_amount_fee}), conv_i($form->{AR_amount_interest}), conv_i($form->{AR}));
   do_query($form, $dbh, $query, @values);
 
-  $dbh->commit();
-  $dbh->disconnect();
-
-  $main::lxdebug->leave_sub();
+  return 1;
 }
 
 sub create_invoice_for_fees {
@@ -211,7 +215,7 @@ sub create_invoice_for_fees {
   $query =
     qq|INSERT INTO ar (id,          invnumber, transdate, gldate, customer_id,
                        taxincluded, amount,    netamount, paid,   duedate,
-                       invoice,     currency_id,      notes,
+                       invoice,     currency_id, taxzone_id,      notes,
                        employee_id)
        VALUES (
          ?,                     -- id
@@ -232,6 +236,14 @@ sub create_invoice_for_fees {
          (SELECT duedate FROM dunning WHERE dunning_id = ? LIMIT 1),
          'f',                   -- invoice
          (SELECT id FROM currencies WHERE name = ?), -- curr
+         --taxzone_id:
+         (SELECT taxzone_id FROM customer WHERE id =
+          (SELECT ar.customer_id
+           FROM dunning dn
+           LEFT JOIN ar ON (dn.trans_id = ar.id)
+           WHERE dn.dunning_id = ?
+           LIMIT 1)
+         ),
          ?,                     -- notes
          -- employee_id:
          (SELECT id FROM employee WHERE login = ?)
@@ -243,6 +255,7 @@ sub create_invoice_for_fees {
              $amount,
              $dunning_id,       # duedate
              $curr,             # default currency
+             $dunning_id,       # taxzone_id
              sprintf($main::locale->text('Automatically created invoice for fee and interest for dunning %s'), $dunning_id), # notes
              $::myconfig{login});   # employee_id
   do_query($form, $dbh, $query, @values);
@@ -273,12 +286,22 @@ sub create_invoice_for_fees {
   $main::lxdebug->leave_sub();
 }
 
+
 sub save_dunning {
+  my ($self, $myconfig, $form, $rows) = @_;
   $main::lxdebug->enter_sub();
 
+  my $rc = SL::DB->client->with_transaction(\&_save_dunning, $self, $myconfig, $form, $rows);
+  $::lxdebug->leave_sub;
+
+  return $rc;
+}
+
+
+sub _save_dunning {
   my ($self, $myconfig, $form, $rows) = @_;
-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
+
+  my $dbh = SL::DB->client->dbh;
 
   my ($query, @values);
 
@@ -343,10 +366,7 @@ sub save_dunning {
     $self->send_email($myconfig, $form, $dunning_id, $dbh);
   }
 
-  $dbh->commit();
-  $dbh->disconnect();
-
-  $main::lxdebug->leave_sub();
+  return 1;
 }
 
 sub send_email {
@@ -469,7 +489,7 @@ sub get_invoices {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $where;
   my @values;
@@ -482,7 +502,7 @@ sub get_invoices {
 
   } elsif ($form->{customer}) {
     $where .= qq| AND (ct.name ILIKE ?)|;
-    push(@values, '%' . $form->{customer} . '%');
+    push(@values, like($form->{customer}));
   }
 
   my %columns = (
@@ -494,7 +514,7 @@ sub get_invoices {
   foreach my $key (keys(%columns)) {
     next unless ($form->{$key});
     $where .= qq| AND $columns{$key} ILIKE ?|;
-    push(@values, '%' . $form->{$key} . '%');
+    push(@values, like($form->{$key}));
   }
 
   if ($form->{dunning_level}) {
@@ -505,7 +525,7 @@ sub get_invoices {
   $form->{minamount} = $form->parse_amount($myconfig,$form->{minamount});
   if ($form->{minamount}) {
     $where .= qq| AND ((a.amount - a.paid) > ?) |;
-    push(@values, $form->{minamount});
+    push(@values, trim($form->{minamount}));
   }
 
   my $query =
@@ -514,11 +534,16 @@ sub get_invoices {
        WHERE dunning_level = (SELECT MAX(dunning_level) FROM dunning_config)|;
   my ($id_for_max_dunning_level) = selectrow_query($form, $dbh, $query);
 
+  if (!$form->{l_include_direct_debit}) {
+    $where .= qq| AND NOT COALESCE(a.direct_debit, FALSE) |;
+  }
+
   $query =
     qq|SELECT
-         a.id, a.ordnumber, a.transdate, a.invnumber, a.amount, a.language_id,
+         a.id, a.invoice, a.ordnumber, a.transdate, a.invnumber, a.amount, a.language_id,
          ct.name AS customername, a.customer_id, a.duedate,
          a.amount - a.paid AS open_amount,
+         a.direct_debit,
 
          cfg.dunning_description, cfg.dunning_level,
 
@@ -581,7 +606,6 @@ sub get_invoices {
   $query = qq|SELECT id, dunning_description FROM dunning_config ORDER BY dunning_level|;
   $form->{DUNNING_CONFIG} = selectall_hashref_query($form, $dbh, $query);
 
-  $dbh->disconnect;
   $main::lxdebug->leave_sub();
 }
 
@@ -592,7 +616,7 @@ sub get_dunning {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;
 
   my $where = qq| WHERE (da.trans_id = a.id)|;
 
@@ -604,7 +628,7 @@ sub get_dunning {
 
   } elsif ($form->{customer}) {
     $where .= qq| AND (ct.name ILIKE ?)|;
-    push(@values, '%' . $form->{customer} . '%');
+    push(@values, like($form->{customer}));
   }
 
   my %columns = (
@@ -615,7 +639,7 @@ sub get_dunning {
   foreach my $key (keys(%columns)) {
     next unless ($form->{$key});
     $where .= qq| AND $columns{$key} ILIKE ?|;
-    push(@values, '%' . $form->{$key} . '%');
+    push(@values, like($form->{$key}));
   }
 
   if ($form->{dunning_level}) {
@@ -694,7 +718,6 @@ sub get_dunning {
     map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2)} qw(amount fee interest);
   }
 
-  $dbh->disconnect;
   $main::lxdebug->leave_sub();
 }
 
@@ -750,7 +773,7 @@ sub print_dunning {
   my ($self, $myconfig, $form, $dunning_id, $provided_dbh) = @_;
 
   # connect to database
-  my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
+  my $dbh = $provided_dbh || SL::DB->client->dbh;
 
   $dunning_id =~ s|[^\d]||g;
 
@@ -776,7 +799,8 @@ sub print_dunning {
          ar.transdate,       ar.duedate,      ar.customer_id,
          ar.invnumber,       ar.ordnumber,    ar.cp_id,
          ar.amount,          ar.netamount,    ar.paid,
-         (SELECT cu.name FROM currencies cu WHERE cu.id=ar.currency_id) AS curr,
+         (SELECT cu.name FROM currencies cu WHERE cu.id = ar.currency_id) AS curr,
+         (SELECT description from department WHERE id = ar.department_id) AS department,
          ar.amount - ar.paid AS open_amount,
          ar.amount - ar.paid + da.fee + da.interest AS linetotal
 
@@ -877,8 +901,6 @@ sub print_dunning {
 
   $form->parse_template($myconfig);
 
-  $dbh->disconnect() unless $provided_dbh;
-
   $main::lxdebug->leave_sub();
 }
 
@@ -887,7 +909,7 @@ sub print_invoice_for_fees {
 
   my ($self, $myconfig, $form, $dunning_id, $provided_dbh) = @_;
 
-  my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig);
+  my $dbh = $provided_dbh || SL::DB->client->dbh;
 
   my ($query, @values, $sth);
 
@@ -974,8 +996,6 @@ sub print_invoice_for_fees {
   push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => "${spool}/$filename",
                                            'name'     => "dunning_invoice_${dunning_id}.pdf" };
 
-  $dbh->disconnect() unless $provided_dbh;
-
   $main::lxdebug->leave_sub();
 }