DN: Bei Fehlern in der Transaktion Fehler anzeigen

[mfinanz.git] / SL / DN.pm

diff --git a/SL/DN.pm b/SL/DN.pm
index b4ad666d0bbee34182a971ca255e3ca1f4a87e5d..9273bf56482f009c6c044c8299ebf1729ff46784 100644 (file)
--- 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::DB::Printer;
 use SL::DB::Language;
 use SL::TransNumber;

+use SL::Util qw(trim);
+use SL::DB;

 
 use strict;
 
 
 use strict;
 


@@ -54,7 +56,7 @@ sub get_config {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
   my ($self, $myconfig, $form) = @_;
 
   # connect to database

-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;

 
   my $query =
     qq|SELECT * | .
 
   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);
 
        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 {
   $main::lxdebug->leave_sub();
 }
 
 sub save_config {

+  my ($self, $myconfig, $form) = @_;

   $main::lxdebug->enter_sub();
 
   $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) = @_;
 
   my ($self, $myconfig, $form) = @_;
 

-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
+  my $dbh = SL::DB->client->dbh;

 
   my ($query, @values);
 
 
   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);
 
   @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 {
 }
 
 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,
   $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
                        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
          (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 = ?)
          ?,                     -- notes
          -- employee_id:
          (SELECT id FROM employee WHERE login = ?)


@@ -243,24 +255,27 @@ sub create_invoice_for_fees {
              $amount,
              $dunning_id,       # duedate
              $curr,             # default currency
              $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
              sprintf($main::locale->text('Automatically created invoice for fee and interest for dunning %s'), $dunning_id), # notes

-             $form->{login});   # employee_id
+             $::myconfig{login});   # employee_id

   do_query($form, $dbh, $query, @values);
 
   $query =
   do_query($form, $dbh, $query, @values);
 
   $query =

-    qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, taxkey)
-       VALUES (?, ?, ?, current_date, current_date, 0)|;
+    qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, taxkey, tax_id, chart_link)
+       VALUES (?, ?, ?, current_date, current_date, 0,
+               (SELECT id   FROM tax   WHERE (taxkey = 0) AND (rate = 0)),
+               (SELECT link FROM chart WHERE id = ?))|;

   $sth = prepare_query($form, $dbh, $query);
 
   $sth = prepare_query($form, $dbh, $query);
 

-  @values = ($ar_id, conv_i($form->{AR_amount_fee}), $fee_remaining);
+  @values = ($ar_id, conv_i($form->{AR_amount_fee}), $fee_remaining, conv_i($form->{AR_amount_fee}));

   do_statement($form, $sth, $query, @values);
 
   if ($interest_remaining) {
   do_statement($form, $sth, $query, @values);
 
   if ($interest_remaining) {

-    @values = ($ar_id, conv_i($form->{AR_amount_interest}), $interest_remaining);
+    @values = ($ar_id, conv_i($form->{AR_amount_interest}), $interest_remaining, conv_i($form->{AR_amount_interest}));

     do_statement($form, $sth, $query, @values);
   }
 
     do_statement($form, $sth, $query, @values);
   }
 

-  @values = ($ar_id, conv_i($form->{AR}), -1 * $amount);
+  @values = ($ar_id, conv_i($form->{AR}), -1 * $amount, conv_i($form->{AR}));

   do_statement($form, $sth, $query, @values);
 
   $sth->finish();
   do_statement($form, $sth, $query, @values);
 
   $sth->finish();


@@ -271,12 +286,26 @@ sub create_invoice_for_fees {
   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }
 

+

 sub save_dunning {
 sub save_dunning {

+  my ($self, $myconfig, $form, $rows) = @_;

   $main::lxdebug->enter_sub();
 
   $main::lxdebug->enter_sub();
 

+  my $rc = SL::DB->client->with_transaction(\&_save_dunning, $self, $myconfig, $form, $rows);
+
+  if (!$rc) {
+    die SL::DB->client->error
+  }
+  $::lxdebug->leave_sub;
+
+  return $rc;
+}
+
+
+sub _save_dunning {

   my ($self, $myconfig, $form, $rows) = @_;
   my ($self, $myconfig, $form, $rows) = @_;

-  # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
+
+  my $dbh = SL::DB->client->dbh;

 
   my ($query, @values);
 
 
   my ($query, @values);
 


@@ -341,10 +370,7 @@ sub save_dunning {
     $self->send_email($myconfig, $form, $dunning_id, $dbh);
   }
 
     $self->send_email($myconfig, $form, $dunning_id, $dbh);
   }
 

-  $dbh->commit();
-  $dbh->disconnect();
-
-  $main::lxdebug->leave_sub();
+  return 1;

 }
 
 sub send_email {
 }
 
 sub send_email {


@@ -377,9 +403,7 @@ sub send_email {
   $mail->{subject} = $template->parse_block($ref->{email_subject});
   $mail->{message} = $template->parse_block($ref->{email_body});
 
   $mail->{subject} = $template->parse_block($ref->{email_subject});
   $mail->{message} = $template->parse_block($ref->{email_body});
 

-  if ($myconfig->{signature}) {
-    $mail->{message} .= "\n-- \n$myconfig->{signature}";
-  }
+  $mail->{message} .= $form->create_email_signature();

 
   $mail->{message} =~ s/\r\n/\n/g;
 
 
   $mail->{message} =~ s/\r\n/\n/g;
 


@@ -469,7 +493,7 @@ sub get_invoices {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
   my ($self, $myconfig, $form) = @_;
 
   # connect to database

-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = SL::DB->client->dbh;

 
   my $where;
   my @values;
 
   my $where;
   my @values;


@@ -482,7 +506,7 @@ sub get_invoices {
 
   } elsif ($form->{customer}) {
     $where .= qq| AND (ct.name ILIKE ?)|;
 
   } elsif ($form->{customer}) {
     $where .= qq| AND (ct.name ILIKE ?)|;

-    push(@values, '%' . $form->{customer} . '%');
+    push(@values, like($form->{customer}));

   }
 
   my %columns = (
   }
 
   my %columns = (


@@ -494,7 +518,7 @@ sub get_invoices {
   foreach my $key (keys(%columns)) {
     next unless ($form->{$key});
     $where .= qq| AND $columns{$key} ILIKE ?|;
   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}) {
   }
 
   if ($form->{dunning_level}) {


@@ -505,7 +529,7 @@ sub get_invoices {
   $form->{minamount} = $form->parse_amount($myconfig,$form->{minamount});
   if ($form->{minamount}) {
     $where .= qq| AND ((a.amount - a.paid) > ?) |;
   $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 =
   }
 
   my $query =


@@ -514,11 +538,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);
 
        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
   $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,
          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,
 
 
          cfg.dunning_description, cfg.dunning_level,
 


@@ -581,7 +610,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);
 
   $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();
 }
 
   $main::lxdebug->leave_sub();
 }
 


@@ -592,7 +620,7 @@ sub get_dunning {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
   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)|;
 
 
   my $where = qq| WHERE (da.trans_id = a.id)|;
 


@@ -604,7 +632,7 @@ sub get_dunning {
 
   } elsif ($form->{customer}) {
     $where .= qq| AND (ct.name ILIKE ?)|;
 
   } elsif ($form->{customer}) {
     $where .= qq| AND (ct.name ILIKE ?)|;

-    push(@values, '%' . $form->{customer} . '%');
+    push(@values, like($form->{customer}));

   }
 
   my %columns = (
   }
 
   my %columns = (


@@ -615,7 +643,7 @@ sub get_dunning {
   foreach my $key (keys(%columns)) {
     next unless ($form->{$key});
     $where .= qq| AND $columns{$key} ILIKE ?|;
   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}) {
   }
 
   if ($form->{dunning_level}) {


@@ -694,7 +722,6 @@ sub get_dunning {
     map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2)} qw(amount fee interest);
   }
 
     map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2)} qw(amount fee interest);
   }
 

-  $dbh->disconnect;

   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }
 


@@ -750,7 +777,7 @@ sub print_dunning {
   my ($self, $myconfig, $form, $dunning_id, $provided_dbh) = @_;
 
   # connect to database
   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;
 
 
   $dunning_id =~ s|[^\d]||g;
 


@@ -776,7 +803,8 @@ sub print_dunning {
          ar.transdate,       ar.duedate,      ar.customer_id,
          ar.invnumber,       ar.ordnumber,    ar.cp_id,
          ar.amount,          ar.netamount,    ar.paid,
          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
 
          ar.amount - ar.paid AS open_amount,
          ar.amount - ar.paid + da.fee + da.interest AS linetotal
 


@@ -873,12 +901,10 @@ sub print_dunning {
 
   push @{ $form->{DUNNING_PDFS} }, $filename;
   push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => "${spool}/$filename",
 
   push @{ $form->{DUNNING_PDFS} }, $filename;
   push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => "${spool}/$filename",

-                                           'name'     => "dunning_${dunning_id}.pdf" };
+                                           'name'     => $form->get_formname_translation('dunning') . "_${dunning_id}.pdf" };

 
   $form->parse_template($myconfig);
 
 
   $form->parse_template($myconfig);
 

-  $dbh->disconnect() unless $provided_dbh;
-

   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }
 


@@ -887,7 +913,7 @@ sub print_invoice_for_fees {
 
   my ($self, $myconfig, $form, $dunning_id, $provided_dbh) = @_;
 
 
   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);
 
 
   my ($query, @values, $sth);
 


@@ -926,7 +952,7 @@ sub print_invoice_for_fees {
   map { $form->{$_} = $ref->{$_} } keys %{ $ref };
 
   $query = qq|SELECT * FROM employee WHERE login = ?|;
   map { $form->{$_} = $ref->{$_} } keys %{ $ref };
 
   $query = qq|SELECT * FROM employee WHERE login = ?|;

-  $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{login});
+  $ref = selectfirst_hashref_query($form, $dbh, $query, $::myconfig{login});

   map { $form->{"employee_${_}"} = $ref->{$_} } keys %{ $ref };
 
   $query = qq|SELECT * FROM acc_trans WHERE trans_id = ? ORDER BY acc_trans_id ASC|;
   map { $form->{"employee_${_}"} = $ref->{$_} } keys %{ $ref };
 
   $query = qq|SELECT * FROM acc_trans WHERE trans_id = ? ORDER BY acc_trans_id ASC|;


@@ -974,8 +1000,6 @@ sub print_invoice_for_fees {
   push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => "${spool}/$filename",
                                            'name'     => "dunning_invoice_${dunning_id}.pdf" };
 
   push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => "${spool}/$filename",
                                            'name'     => "dunning_invoice_${dunning_id}.pdf" };
 

-  $dbh->disconnect() unless $provided_dbh;
-

   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }