From fd6900cca59ac4a35d5fa4c20db91165f9310ea3 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Thu, 3 May 2007 10:08:04 +0000 Subject: [PATCH] =?utf8?q?Gro=C3=9Fe=20Teile=20des=20Mahncodes=20neu=20ges?= =?utf8?q?chrieben=20bzw.=20umgeschrieben.=20Mehrere=20Fehler=20behoben:?= =?utf8?q?=201.=20Rechnungen,=20die=20bereits=20gemahnt=20wurden,=20wurden?= =?utf8?q?=20zu=20fr=C3=BCh=20erneut=20zur=20Mahnung=20angeboten,=20weil?= =?utf8?q?=20der=20Mahnzeitraum=20auf=20das=20urspr=C3=BCngliche=20F=C3=A4?= =?utf8?q?lligkeitsdatum=20der=20Rechnung=20bezogen=20wurde=20und=20nicht?= =?utf8?q?=20auf=20das=20F=C3=A4lligkeitsdatum=20der=20vorhergehenden=20Ma?= =?utf8?q?hnstufe.=202.=20Wurden=20gleichzeitig=20Mahnungen=20f=C3=BCr=20m?= =?utf8?q?ehrere=20Mahnstufen=20generiert,=20so=20wurden=20die=20falschen?= =?utf8?q?=20Vorlagen=20benutzt.=203.=20Die=20kummulierten=20Mahnkosten=20?= =?utf8?q?und=20die=20Zinsen=20wurden=20in=20der=20Liste=20der=20neu=20zu?= =?utf8?q?=20erstellenden=20Mahnungen=20falsch=20angezeigt.=20Weiterhin=20?= =?utf8?q?wurde=20die=20Dokumentation=20f=C3=BCr=20die=20Vorlagenvariablen?= =?utf8?q?=20um=20eine=20Sektion=20=C3=BCber=20Mahnungen=20erweitert.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/DN.pm | 265 ++++++++++++------ bin/mozilla/dn.pl | 167 ++++++----- doc/dokumentenvorlagen-und-variablen.html | 117 ++++++++ locale/de/all | 4 +- locale/de/am | 74 +---- locale/de/dn | 3 +- locale/de/ir | 1 + .../dunning_config_interest_rate.sql | 4 + 8 files changed, 388 insertions(+), 247 deletions(-) create mode 100644 sql/Pg-upgrade2/dunning_config_interest_rate.sql diff --git a/SL/DN.pm b/SL/DN.pm index 008a538f1..2566cbed7 100644 --- a/SL/DN.pm +++ b/SL/DN.pm @@ -56,7 +56,7 @@ sub get_config { foreach my $ref (@{ $form->{DUNNING} }) { $ref->{fee} = $form->format_amount($myconfig, $ref->{fee}, 2); - $ref->{interest} = $form->format_amount($myconfig, ($ref->{interest} * 100)); + $ref->{interest_rate} = $form->format_amount($myconfig, ($ref->{interest_rate} * 100)); } $dbh->disconnect(); @@ -76,13 +76,13 @@ sub save_config { for my $i (1 .. $form->{rowcount}) { $form->{"fee_$i"} = $form->parse_amount($myconfig, $form->{"fee_$i"}) * 1; - $form->{"interest_$i"} = $form->parse_amount($myconfig, $form->{"interest_$i"}) / 100; + $form->{"interest_rate_$i"} = $form->parse_amount($myconfig, $form->{"interest_rate_$i"}) / 100; if (($form->{"dunning_level_$i"} ne "") && ($form->{"dunning_description_$i"} ne "")) { @values = (conv_i($form->{"dunning_level_$i"}), $form->{"dunning_description_$i"}, $form->{"email_subject_$i"}, $form->{"email_body_$i"}, - $form->{"template_$i"}, $form->{"fee_$i"}, $form->{"interest_$i"}, + $form->{"template_$i"}, $form->{"fee_$i"}, $form->{"interest_rate_$i"}, $form->{"active_$i"} ? 't' : 'f', $form->{"auto_$i"} ? 't' : 'f', $form->{"email_$i"} ? 't' : 'f', $form->{"email_attachment_$i"} ? 't' : 'f', conv_i($form->{"payment_terms_$i"}), conv_i($form->{"terms_$i"})); if ($form->{"id_$i"}) { @@ -90,7 +90,7 @@ sub save_config { qq|UPDATE dunning_config SET dunning_level = ?, dunning_description = ?, email_subject = ?, email_body = ?, - template = ?, fee = ?, interest = ?, + template = ?, fee = ?, interest_rate = ?, active = ?, auto = ?, email = ?, email_attachment = ?, payment_terms = ?, terms = ? WHERE id = ?|; @@ -99,7 +99,7 @@ sub save_config { $query = qq|INSERT INTO dunning_config (dunning_level, dunning_description, email_subject, email_body, - template, fee, interest, active, auto, email, + template, fee, interest_rate, active, auto, email, email_attachment, payment_terms, terms) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; } @@ -129,66 +129,114 @@ sub save_dunning { my ($dunning_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|); + my $q_update_ar = qq|UPDATE ar SET dunning_config_id = ? WHERE id = ?|; + my $h_update_ar = prepare_query($form, $dbh, $q_update_ar); + + my $q_insert_dunning = + qq|INSERT INTO dunning (dunning_id, dunning_config_id, dunning_level, + trans_id, fee, interest, transdate, duedate) + VALUES (?, ?, + (SELECT dunning_level FROM dunning_config WHERE id = ?), + ?, + (SELECT SUM(fee) + FROM dunning_config + WHERE dunning_level <= (SELECT dunning_level FROM dunning_config WHERE id = ?)), + (SELECT (amount - paid) * (current_date - transdate) FROM ar WHERE id = ?) + * (SELECT interest_rate FROM dunning_config WHERE id = ?) + / 360, + current_date, + current_date + (SELECT payment_terms FROM dunning_config WHERE id = ?))|; + my $h_insert_dunning = prepare_query($form, $dbh, $q_insert_dunning); + + my @invoice_ids; + my ($next_dunning_config_id, $customer_id); + my $send_email = 0; + foreach my $row (@{ $rows }) { + push @invoice_ids, $row->{invoice_id}; + $next_dunning_config_id = $row->{next_dunning_config_id}; + $customer_id = $row->{customer_id}; + + @values = ($row->{next_dunning_config_id}, $row->{invoice_id}); + do_statement($form, $h_update_ar, $q_update_ar, @values); + + $send_email |= $row->{email}; - $form->{"interest_$row"} = $form->parse_amount($myconfig,$form->{"interest_$row"}); - $form->{"fee_$row"} = $form->parse_amount($myconfig,$form->{"fee_$row"}); - $form->{send_email} = $form->{"email_$row"}; - - $query = qq|UPDATE ar SET dunning_config_id = ? WHERE id = ?|; - @values = ($form->{"next_dunning_config_id_$row"}, - $form->{"inv_id_$row"}); - do_query($form, $dbh, $query, @values); - - $query = - qq|INSERT INTO dunning (dunning_id, dunning_config_id, dunning_level, - trans_id, fee, interest, transdate, duedate) - VALUES (?, ?, (SELECT dunning_level FROM dunning_config WHERE id = ?), - ?, ?, ?, current_date, ?)|; - @values = ($dunning_id, - conv_i($form->{"next_dunning_config_id_$row"}), - conv_i($form->{"next_dunning_config_id_$row"}), - conv_i($form->{"inv_id_$row"}), $form->{"fee_$row"}, - $form->{"interest_$row"}, - conv_date($form->{"next_duedate_$row"})); - do_query($form, $dbh, $query, @values); + my $next_config_id = conv_i($row->{next_dunning_config_id}); + my $invoice_id = conv_i($row->{invoice_id}); + + @values = ($dunning_id, $next_config_id, $next_config_id, + $invoice_id, $next_config_id, $invoice_id, + $next_config_id, $next_config_id); + do_statement($form, $h_insert_dunning, $q_insert_dunning, @values); } + $h_update_ar->finish(); + $h_insert_dunning->finish(); + my $query = - qq|SELECT invnumber, ordnumber, customer_id, amount, netamount, - ar.transdate, ar.duedate, paid, amount - paid AS open_amount, - template AS formname, email_subject, email_body, email_attachment, - da.fee, da.interest, da.transdate AS dunning_date, - da.duedate AS dunning_duedate, - c.name, c.street, c.zipcode, c.city, c.country, c.department_1, c.department_2 + qq|SELECT + ar.invnumber, ar.ordnumber, ar.amount, ar.netamount, + ar.transdate, ar.duedate, ar.paid, ar.amount - ar.paid AS open_amount, + da.fee, da.interest, da.transdate AS dunning_date, da.duedate AS dunning_duedate FROM ar - LEFT JOIN dunning_config ON (dunning_config.id = ar.dunning_config_id) - LEFT JOIN dunning da ON (ar.id = da.trans_id AND dunning_config.dunning_level = da.dunning_level) - LEFT JOIN customer c ON (ar.customer_id = c.id) + LEFT JOIN dunning_config cfg ON (cfg.id = ar.dunning_config_id) + LEFT JOIN dunning da ON (ar.id = da.trans_id AND cfg.dunning_level = da.dunning_level) WHERE ar.id IN (| - . join(", ", map("?", @{ $form->{"inv_ids"} })) . qq|)|; + . join(", ", map { "?" } @invoice_ids) . qq|)|; - my $sth = prepare_execute_query($form, $dbh, $query, @{ $form->{"inv_ids"} }); + my $sth = prepare_execute_query($form, $dbh, $query, @invoice_ids); my $first = 1; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { if ($first) { map({ $form->{"dn_$_"} = []; } keys(%{$ref})); $first = 0; } + + $ref->{interest_rate} = $form->format_amount($myconfig, $ref->{interest_rate} * 100); map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2) } qw(amount netamount paid open_amount fee interest); - map { $form->{$_} = $ref->{$_} } keys %$ref; - #print(STDERR Dumper($ref)); map { push(@{ $form->{"dn_$_"} }, $ref->{$_})} keys %$ref; + map { $form->{$_} = $ref->{$_} } keys %{ $ref }; } $sth->finish; - IS->customer_details($myconfig,$form); - #print(STDERR Dumper($form->{dn_invnumber})); - $form->{templates} = "$myconfig->{templates}"; - - + $query = + qq|SELECT id AS customer_id, name, street, zipcode, city, country, department_1, department_2, email + FROM customer + WHERE id = ?|; + $ref = selectfirst_hashref_query($form, $dbh, $query, $customer_id); + map { $form->{$_} = $ref->{$_} } keys %{ $ref }; - $form->{language} = $form->get_template_language(\%myconfig); + $query = + qq|SELECT + cfg.interest_rate, cfg.template AS formname, + cfg.email_subject, cfg.email_body, cfg.email_attachment, + (SELECT fee + FROM dunning + WHERE dunning_id = ? + LIMIT 1) + AS fee, + (SELECT SUM(interest) + FROM dunning + WHERE dunning_id = ?) + AS total_interest, + (SELECT SUM(amount) - SUM(paid) + FROM ar + WHERE id IN (| . join(", ", map { "?" } @invoice_ids) . qq|)) + AS total_open_amount + FROM dunning_config cfg + WHERE id = ?|; + $ref = selectfirst_hashref_query($form, $dbh, $query, $dunning_id, $dunning_id, @invoice_ids, $next_dunning_config_id); + map { $form->{$_} = $ref->{$_} } keys %{ $ref }; + + $form->{interest_rate} = $form->format_amount($myconfig, $ref->{interest_rate} * 100); + $form->{fee} = $form->format_amount($myconfig, $ref->{fee}, 2); + $form->{total_interest} = $form->format_amount($myconfig, $form->round_amount($ref->{total_interest}, 2), 2); + $form->{total_open_amount} = $form->format_amount($myconfig, $form->round_amount($ref->{total_open_amount}, 2), 2); + $form->{total_amount} = $form->format_amount($myconfig, $form->round_amount($ref->{fee} + $ref->{total_interest} + $ref->{total_open_amount}, 2), 2); + + $form->{templates} = "$myconfig->{templates}"; + $form->{language} = $form->get_template_language(\%myconfig); $form->{printer_code} = $form->get_printer_code(\%myconfig); if ($form->{language} ne "") { @@ -215,7 +263,7 @@ sub save_dunning { $form->{"IN"} =~ s/html$/odt/; } - if ($form->{"send_email"} && ($form->{email} ne "")) { + if ($send_email && ($form->{email} ne "")) { $form->{media} = 'email'; } @@ -259,24 +307,7 @@ sub get_invoices { # connect to database my $dbh = $form->dbconnect($myconfig); - my $where = - qq| WHERE (a.paid < a.amount) - AND (a.duedate < current_date) - AND (dnn.id = - (SELECT id FROM dunning_config - WHERE dunning_level > - (SELECT - CASE - WHEN a.dunning_config_id IS NULL - THEN 0 - ELSE (SELECT dunning_level - FROM dunning_config - WHERE id = a.dunning_config_id - ORDER BY dunning_level - LIMIT 1) - END - FROM dunning_config LIMIT 1) - LIMIT 1)) |; + my $where; my @values; $form->{customer_id} = $1 if ($form->{customer} =~ /--(\d+)$/); @@ -312,37 +343,57 @@ sub get_invoices { push(@values, $form->{minamount}); } - $paymentdate = $form->{paymentuntil} ? $dbh->quote($form->{paymentuntil}) : - "current_date"; - $query = - qq|SELECT a.id, a.ordnumber, a.transdate, a.invnumber, a.amount, + qq|SELECT + a.id, a.ordnumber, a.transdate, a.invnumber, a.amount, ct.name AS customername, a.customer_id, a.duedate, - da.fee AS old_fee, dnn.active, dnn.email, dnn.fee + da.fee AS fee, - dn.dunning_description, da.transdate AS dunning_date, da.duedate AS dunning_duedate, - a.duedate + dnn.terms - current_date AS nextlevel, - $paymentdate - a.duedate AS pastdue, dn.dunning_level, - current_date + dnn.payment_terms AS next_duedate, - dnn.dunning_description AS next_dunning_description, dnn.id AS next_dunning_config_id, - dnn.interest AS interest_rate, dnn.terms - FROM dunning_config dnn, ar a - JOIN customer ct ON (a.customer_id = ct.id) - LEFT JOIN dunning_config dn ON (dn.id = a.dunning_config_id) - LEFT JOIN dunning da ON ((da.trans_id = a.id) AND (dn.dunning_level = da.dunning_level)) + + cfg.dunning_description, cfg.dunning_level, + + d.transdate AS dunning_date, d.duedate AS dunning_duedate, + d.fee, d.interest, + + a.duedate + cfg.terms - current_date AS nextlevel, + current_date - COALESCE(d.duedate, a.duedate) AS pastdue, + current_date + cfg.payment_terms AS next_duedate, + + nextcfg.dunning_description AS next_dunning_description, + nextcfg.id AS next_dunning_config_id, + nextcfg.terms, nextcfg.active, nextcfg.email + + FROM ar a + + LEFT JOIN customer ct ON (a.customer_id = ct.id) + LEFT JOIN dunning_config cfg ON (a.dunning_config_id = cfg.id) + LEFT JOIN dunning_config nextcfg ON + (nextcfg.id = + (SELECT id + FROM dunning_config + WHERE dunning_level > + COALESCE((SELECT dunning_level + FROM dunning_config + WHERE id = a.dunning_config_id + ORDER BY dunning_level DESC + LIMIT 1), + 0) + LIMIT 1)) + LEFT JOIN dunning d ON ((d.trans_id = a.id) AND (cfg.dunning_level = d.dunning_level)) + + WHERE (a.paid < a.amount) + AND (a.duedate < current_date) + $where - ORDER BY a.id, transdate, duedate, name|; + ORDER BY a.id, transdate, duedate, name|; my $sth = prepare_execute_query($form, $dbh, $query, @values); $form->{DUNNINGS} = []; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $ref->{interest} = ($ref->{amount} * $ref->{pastdue} * $ref->{interest_rate}) / 360; + next if !$ref->{terms} || ($ref->{pastdue} < $ref->{terms}); + $ref->{interest} = $form->round_amount($ref->{interest}, 2); - map({ $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2)} qw(amount fee interest)); - if ($ref->{pastdue} >= $ref->{terms}) { - push(@{ $form->{DUNNINGS} }, $ref); - } + push(@{ $form->{DUNNINGS} }, $ref); } $sth->finish; @@ -555,7 +606,7 @@ Content-Length: $numbytes sub print_dunning { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form, $dunning_id, $userspath,$spool, $sendmail) = @_; + my ($self, $myconfig, $form, $dunning_id, $userspath, $spool, $sendmail) = @_; # connect to database my $dbh = $form->dbconnect_noauto($myconfig); @@ -582,7 +633,47 @@ sub print_dunning { } $sth->finish; - IS->customer_details($myconfig,$form); + $query = + qq|SELECT id AS customer_id, name, street, zipcode, city, country, department_1, department_2, email + FROM customer + WHERE id = + (SELECT customer_id + FROM dunning d + LEFT JOIN ar ON (d.trans_id = ar.id) + WHERE d.id = ?)|; + $ref = selectfirst_hashref_query($form, $dbh, $query, $dunning_id); + map { $form->{$_} = $ref->{$_} } keys %{ $ref }; + + $query = + qq|SELECT + cfg.interest_rate, cfg.template AS formname, + cfg.email_subject, cfg.email_body, cfg.email_attachment, + d.fee, d.dunning_date, + (SELECT SUM(interest) + FROM dunning + WHERE dunning_id = ?) + AS total_interest, + (SELECT SUM(amount) - SUM(paid) + FROM ar + WHERE id IN + (SELECT trans_id + FROM dunning + WHERE dunning_id = ?)) + AS total_open_amount + FROM dunning d + LEFT JOIN dunning_config cfg ON (d.dunning_config_id = cfg.id) + WHERE d.dunning_id = ? + LIMIT 1|; + $ref = selectfirst_hashref_query($form, $dbh, $query, $dunning_id, $dunning_id, $dunning_id); + map { $form->{$_} = $ref->{$_} } keys %{ $ref }; + + $form->{interest_rate} = $form->format_amount($myconfig, $ref->{interest_rate} * 100); + $form->{fee} = $form->format_amount($myconfig, $ref->{fee}, 2); + $form->{total_interest} = $form->format_amount($myconfig, $form->round_amount($ref->{total_interest}, 2), 2); + $form->{total_open_amount} = $form->format_amount($myconfig, $form->round_amount($ref->{total_open_amount}, 2), 2); + $form->{total_amount} = $form->format_amount($myconfig, $form->round_amount($ref->{fee} + $ref->{total_interest} + $ref->{total_open_amount}, 2), 2); + + $form->{templates} = "$myconfig->{templates}"; $form->{language} = $form->get_template_language(\%myconfig); diff --git a/bin/mozilla/dn.pl b/bin/mozilla/dn.pl index 844abf5b5..ee77c8224 100644 --- a/bin/mozilla/dn.pl +++ b/bin/mozilla/dn.pl @@ -54,7 +54,7 @@ sub edit_config { "$form->{script}?action=edit_config&login=$form->{login}&password=$form->{password}" unless $form->{callback}; - @column_index = qw(dunning_level dunning_description active auto email payment_terms terms fee interest template); + @column_index = qw(dunning_level dunning_description active auto email payment_terms terms fee interest_rate template); $column_header{dunning_level} = qq|| @@ -88,7 +88,7 @@ sub edit_config { qq|| . $locale->text('Fee') . qq||; - $column_header{interest} = + $column_header{interest_rate} = qq|| . $locale->text('Interest Rate') . qq||; @@ -141,7 +141,7 @@ sub edit_config { $column_data{payment_terms} = qq||; $column_data{terms} = qq||; $column_data{fee} = qq||; - $column_data{interest} = qq|%|; + $column_data{interest_rate} = qq|%|; $column_data{template} = qq||; @@ -174,7 +174,7 @@ sub edit_config { $column_data{payment_terms} = qq||; $column_data{terms} = qq||; $column_data{fee} = qq||; - $column_data{interest} = qq|%|; + $column_data{interest_rate} = qq|%|; $column_data{template} = qq||; @@ -260,36 +260,19 @@ sub add { | if $form->{selectdepartment}; - $form->{title} = $locale->text('Start Dunning Process'); - $form->{nextsub} = "show_invoices"; - - # use JavaScript Calendar or not - $form->{jsscript} = $jscalendar; - $jsscript = ""; - if ($form->{jsscript}) { - - # with JavaScript Calendar - $button1 = qq| - - text('button') . qq|> - |; - #write Trigger - $jsscript = - Form->write_trigger(\%myconfig, "1", "paymentuntil", "BR", "trigger1"); - } else { + $form->{title} = $locale->text('Start Dunning Process'); + $form->{nextsub} = "show_invoices"; - # without JavaScript Calendar - $button1 = - qq||; - } - $form->{fokus} = "search.customer"; + $form->{jsscript} = 1; + $form->{fokus} = "search.customer"; $form->{javascript} .= qq||; - $form->header; + $form->header(); + $onload = qq|focus()|; $onload .= qq|;setupDateFormat('|. $myconfig{dateformat} .qq|', '|. $locale->text("Falsches Datumsformat!") .qq|')|; $onload .= qq|;setupPoints('|. $myconfig{numberformat} .qq|', '|. $locale->text("wrongformat") .qq|')|; + print qq| @@ -319,10 +302,6 @@ sub add { | . $locale->text('Notes') . qq| - - | . $locale->text('Payment until') . qq| - $button1 - @@ -390,17 +369,17 @@ sub show_invoices { "$form->{script}?action=show_invoices&login=$form->{login}&password=$form->{password}&customer=$form->{customer}&invnumber=$form->{invnumber}&ordnumber=$form->{ordnumber}&paymentuntil=$form->{paymentuntil}&groupinvoices=$form->{groupinvoices}&minamount=$form->{minamount}&dunning_level=$form->{dunning_level}¬es=$form->{notes}" unless $form->{callback}; - @column_index = qw(dunning_description active email customername invnumber invdate inv_duedate invamount next_duedate fee interest ); + @column_index = qw(dunning_description dunning_description_next active email customername invnumber invdate inv_duedate amount next_duedate fee interest ); $column_header{dunning_description} = - qq|| + qq|| . $locale->text('Current / Next Level') . qq||; $column_header{active} = qq|| . NTI($cgi->checkbox('-name' => 'selectall_active', '-label' => $locale->text('Active?'), - '-checked' => 1, + '-checked' => 0, '-onclick' => "checkbox_check_all('selectall_active', 'active_', 1, " . scalar(@{ $form->{DUNNINGS} }) . ")")) . qq||; $column_header{email} = @@ -430,7 +409,7 @@ sub show_invoices { qq|| . $locale->text('Invdate') . qq||; - $column_header{invamount} = + $column_header{amount} = qq|| . $locale->text('Amount') . qq||; @@ -440,7 +419,7 @@ sub show_invoices { . qq||; $column_header{interest} = qq|| - . $locale->text('Total Interest') + . $locale->text('Interest') . qq||; $form->header; @@ -455,11 +434,11 @@ sub show_invoices { - + |; - map { print "$column_header{$_}\n" } @column_index; + map { print "$column_header{$_}\n" if $column_header{$_}; } @column_index; print qq| @@ -474,33 +453,39 @@ sub show_invoices { |; - $form->{selectdunning} =~ s/ selected//g; - if ($ref->{next_dunning_config_id} ne "") { - $form->{selectdunning} =~ s/value=$ref->{next_dunning_config_id}/value=$ref->{next_dunning_config_id} selected/; - } - - - $dunning = qq||; - + $form->{selectdunning} =~ s/ selected//g; + if ($ref->{next_dunning_config_id} ne "") { + $form->{selectdunning} =~ s/value=$ref->{next_dunning_config_id}/value=$ref->{next_dunning_config_id} selected/; + } - $column_data{dunning_description} = qq||; + $column_data{dunning_description} = + qq||; + $column_data{dunning_description_next} = + qq||; my $active = ($ref->{active}) ? "checked" : ""; $column_data{active} = qq||; my $email = ($ref->{email}) ? "checked" : ""; - $column_data{email} = - qq||; - $column_data{next_duedate} = qq||; + $column_data{email} = + qq||; + $column_data{next_duedate} = qq||; $column_data{inv_duedate} = qq||; $column_data{invdate} = qq||; $column_data{invnumber} = qq||; $column_data{customername} = qq||; - $column_data{invamount} = qq||; - $column_data{fee} = qq||; - $column_data{interest} = qq||; - + map { $column_data{$_} = + qq|| + } qw(amount fee interest); map { print "$column_data{$_}\n" } @column_index; @@ -578,55 +563,64 @@ sub save_dunning { my $active=1; my @rows = (); undef($form->{DUNNING_PDFS}); + if ($form->{groupinvoices}) { - while ($active) { - $lastcustomer = 0; - $form->{inv_ids} = []; - $active = 0; - @rows = (); - for my $i (1 .. $form->{rowcount}) { - $form->{"active_$i"} *= 1; - $lastcustomer = $form->{"customer_id_$i"} unless ($lastcustomer); - if ($form->{"active_$i"} && ($form->{"customer_id_$i"} == $lastcustomer)) { - push(@{ $form->{inv_ids} }, $form->{"inv_id_$i"}); - $form->{"active_$i"} = 0; - $form->{"customer_id_$i"} = 0; - push(@rows, $i); - } elsif ($form->{"active_$i"}) { - $active = 1; - } else { - $form->{"customer_id_$i"} = 0; - } - } - if (scalar(@{ $form->{inv_ids} }) != 0) { - DN->save_dunning(\%myconfig, \%$form, \@rows, $userspath, $spool, $sendmail); + my %dunnings_for; + + for my $i (1 .. $form->{rowcount}) { + next unless ($form->{"active_$i"}); + + $dunnings_for{$form->{"customer_id_$i"}} ||= {}; + my $dunning_levels = $dunnings_for{$form->{"customer_id_$i"}}; + + $dunning_levels->{$form->{"next_dunning_config_id_$i"}} ||= []; + my $level = $dunning_levels->{$form->{"next_dunning_config_id_$i"}}; + + push @{ $level }, { "row" => $i, + "invoice_id" => $form->{"inv_id_$i"}, + "customer_id" => $form->{"customer_id_$i"}, + "next_dunning_config_id" => $form->{"next_dunning_config_id_$i"}, + "email" => $form->{"email_$i"}, }; + } + + foreach my $levels (values %dunnings_for) { + foreach my $level (values %{ $levels }) { + next unless scalar @{ $level }; + + DN->save_dunning(\%myconfig, \%$form, $level, $userspath, $spool, $sendmail); } } + } else { for my $i (1 .. $form->{rowcount}) { - if ($form->{"active_$i"}) { - @rows = (); - $form->{inv_ids} = [ $form->{"inv_id_$i"} ]; - push(@rows, $i); - DN->save_dunning(\%myconfig, \%$form, \@rows, $userspath, $spool, $sendmail); - } + next unless $form->{"active_$i"}; + + my $level = [ { "row" => $i, + "invoice_id" => $form->{"inv_id_$i"}, + "customer_id" => $form->{"customer_id_$i"}, + "next_dunning_config_id" => $form->{"next_dunning_config_id_$i"}, + "email" => $form->{"email_$i"}, } ]; + DN->save_dunning(\%myconfig, \%$form, $level, $userspath, $spool, $sendmail); } } + if($form->{DUNNING_PDFS}) { DN->melt_pdfs(\%myconfig, \%$form,$spool); } + # saving the history if(!exists $form->{addition} && $form->{id} ne "") { $form->{snumbers} = qq|dunning_id_| . $form->{"dunning_id"}; $form->{addition} = "DUNNING STARTED"; $form->save_history($form->dbconnect(\%myconfig)); } - # /saving the history + # /saving the history + $form->redirect($locale->text('Dunning Process started for selected invoices!')); $lxdebug->leave_sub(); } - + sub set_email { $lxdebug->enter_sub(); @@ -878,7 +872,7 @@ sub show_dunning { . qq||; $column_header{interest} = qq||; $form->header; @@ -931,8 +925,7 @@ sub show_dunning { - foreach (qw(dunning_date dunning_duedate duedate transdate customername - amount fee interest)) { + foreach (qw(dunning_date dunning_duedate duedate transdate customername amount fee interest)) { my $col = $columns{$_} ? $columns{$_} : $_; $column_data{$col} = ""; } diff --git a/doc/dokumentenvorlagen-und-variablen.html b/doc/dokumentenvorlagen-und-variablen.html index cb2dd6870..986ae971c 100644 --- a/doc/dokumentenvorlagen-und-variablen.html +++ b/doc/dokumentenvorlagen-und-variablen.html @@ -81,6 +81,18 @@ td { +
  • + Variablen in Mahnungen + +
      +
    1. + Allgemeine Variablen
      2. + +
    2. + Variablen für jede gemahnte Rechnung
      3. +
    +
    • +
  • Variablen in anderen Vorlagen
    • @@ -758,6 +770,111 @@ td { zum Inhaltsverzeichnis
    +

    Variablen in Mahnungen

    + +

    Allgemeine Variablen:

    + +

    Die Variablen des Verkäufers stehen wie gewohnt + als employee_... zur Verfügung. Die Adressdaten des + Kunden stehen als Variablen name, street, + zipcode, city, country, + department_1, department_2, und + email zur Verfügung. +

    + +

    Weitere Variablen beinhalten:

    + +

    +

    $form->{title}$form->{title}
    $ref->{dunning_level}: $dunning| + . qq|| + . qq|| + . ($ref->{dunning_level} ? $ref->{dunning_level} : " ") + . qq|| + . qq|| + . qq|$ref->{next_duedate}$ref->{next_duedate}$ref->{duedate}$ref->{transdate}$ref->{invnumber}$ref->{customername}$ref->{amount}$ref->{fee}$ref->{interest}| + . H($form->format_amount(\%myconfig, $ref->{$_} * 1, -2)) + . qq|| - . $locale->text('Total Interest') + . $locale->text('Interest') . qq|" . H($ref->{$_}) . "
    + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
    VariablennameBedeutung
    dunning_dateDatum der Mahnung
    dunning_duedateFälligkeitsdatum für diese Mahhnung
    feeKummulative Mahngebühren
    interest_rateZinssatz per anno in Prozent
    total_amountGesamter noch zu zahlender Betrag als fee + total_interest + total_open_amount
    total_interestZinsen per anno über alle Rechnungen
    total_open_amountSumme über alle offene Beträge der Rechnungen
    +

    + +

    + Variablen für jede gemahnte Rechnung:

    + +

    + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
    VariablennameBedeutung
    dn_amountRechnungssumme (brutto)
    dn_duedateOriginales Fälligkeitsdatum der Rechnung
    dn_dunning_dateDatum der Mahnung
    dn_dunning_duedateFälligkeitsdatum der Mahnung
    dn_feeKummulative Mahngebühr (ist die gleiche wie für die ganze Mahnung, da die Gebühr nur einmal pro Mahnung erhoben wird)
    dn_interestZinsen per anno f&uum;r diese Rechnung
    dn_invnumberRechnungsnummer
    dn_netamountRechnungssumme (netto)
    dn_open_amountOffener Rechnungsbetrag
    dn_ordnumberBestellnummer
    dn_transdateRechnungsdatum
    +

    + + + zum Inhaltsverzeichnis
    +
    +

    Variablen in anderen Vorlagen

    diff --git a/locale/de/all b/locale/de/all index 8d5a96e50..061081279 100644 --- a/locale/de/all +++ b/locale/de/all @@ -557,6 +557,7 @@ gestartet', 'Increase' => 'Erhöhen', 'Individual Items' => 'Einzelteile', 'Information' => 'Information', + 'Interest' => 'Zinsen', 'Interest Rate' => 'Zinssatz', 'Internal Notes' => 'interne Bemerkungen', 'International' => 'Ausland', @@ -792,7 +793,6 @@ gestartet', 'Payment date missing!' => 'Tag der Zahlung fehlt!', 'Payment posted!' => 'Zahlung gebucht!', 'Payment terms deleted!' => 'Zahlungskonditionen gelöscht!', - 'Payment until' => 'Zahlungseingänge bis', 'Payments' => 'Zahlungsausgänge', 'Pg Database Administration' => 'Datenbankadministration', 'Phone' => 'Telefon', @@ -1018,7 +1018,6 @@ gestartet', 'Tax number' => 'Steuernummer', 'Tax paid' => 'Vorsteuer', 'Tax-o-matic Account' => 'Automatikbuchung auf Konto', - 'Tax-o-matic Account: ' => '', 'Taxaccount_coa' => 'Automatikkonto', 'Taxation' => 'Versteuerungs Verfahren', 'Taxdescription_coa' => 'Steuer', @@ -1086,7 +1085,6 @@ gestartet', 'Top Level' => 'Hauptartikelbezeichnung', 'Total' => 'Summe', 'Total Fees' => 'Kumulierte Gebühren', - 'Total Interest' => 'Kumulierte Zinsen', 'Trade Discount' => 'Rabatt', 'Transaction Date missing!' => 'Buchungsdatum fehlt!', 'Transaction deleted!' => 'Buchung gelöscht!', diff --git a/locale/de/am b/locale/de/am index 8c44ebe12..6a7dc4c7f 100644 --- a/locale/de/am +++ b/locale/de/am @@ -11,8 +11,6 @@ $self->{texts} = { '<%total%> -- Amount payable' => '<%total%> -- Noch zu bezahlender Betrag', 'A unit with this name does already exist.' => 'Eine Einheit mit diesem Namen existiert bereits.', 'ADDED' => 'Hinzugefügt', - 'AP' => 'Einkauf', - 'AR' => 'Verkauf', 'Account' => 'Konto', 'Account Category A' => 'Aktiva/Mittelverwendung', 'Account Category C' => 'Kosten', @@ -21,6 +19,7 @@ $self->{texts} = { 'Account Category I' => 'Erlöskonto', 'Account Category L' => 'Passiva/Mittelherkunft', 'Account Category Q' => 'Passiva', + 'Account Description missing!' => 'Beschreibung fehlt!', 'Account Link AP' => 'Einkauf', 'Account Link AP_amount' => 'Verbindlichkeiten Aufwand/Anlagen', 'Account Link AP_paid' => 'Verbindlichkeiten Zahlungsausgang', @@ -37,9 +36,7 @@ $self->{texts} = { 'Account Link IC_sale' => 'Warenliste Erlöskonto', 'Account Link IC_taxpart' => 'Warenliste Steuer', 'Account Link IC_taxservice' => 'Dienstleistungen Steuer', - 'Account Number' => 'Kontonummer', 'Account Number missing!' => 'Kontonummer fehlt!', - 'Account Type' => 'Kontoart', 'Account Type missing!' => 'Kontoart fehlt!', 'Account deleted!' => 'Konto gelöscht!', 'Account saved!' => 'Konto gespeichert!', @@ -64,9 +61,7 @@ $self->{texts} = { 'Aufwand EU m. UStId' => 'Aufwand EU m. UStId', 'Aufwand EU o. UStId' => 'Aufwand EU o. UStId', 'Aufwand Inland' => 'Aufwand Inland', - 'BWA' => 'BWA', 'Bestandskonto' => 'Bestandskonto', - 'Bilanz' => 'Bilanz', 'Books are open' => 'Die Bücher sind geöffnet.', 'Buchungsgruppe' => 'Buchungsgruppe', 'Buchungsgruppe bearbeiten' => 'Buchungsgruppe bearbeiten', @@ -109,7 +104,6 @@ $self->{texts} = { 'Dropdown Limit' => 'Auswahllistenbegrenzung', 'E-mail' => 'eMail', 'ELSE' => 'Zusatz', - 'EUER' => 'Einnahmen-/Überschussrechnung', 'Edit Account' => 'Kontodaten bearbeiten', 'Edit Buchungsgruppe' => 'Buchungsgruppe bearbeiten', 'Edit Business' => 'Kunden-/Lieferantentyp bearbeiten', @@ -130,21 +124,16 @@ $self->{texts} = { 'Error in database control file \'%s\': %s' => 'Fehler in Datenbankupgradekontrolldatei \'%s\': %s', 'Expense' => 'Aufwandskonto', 'Expense Account' => 'Aufwandskonto', - 'Expense/Asset' => 'Aufwand/Anlagen', 'Fax' => 'Fax', - 'Folgekonto' => 'Folgekonto', 'Foreign Exchange Gain' => 'Wechselkurserträge', 'Foreign Exchange Loss' => 'Wechselkursaufwendungen', 'Form details (second row)' => 'Formulardetails (zweite Positionszeile)', - 'Gültig ab' => 'Gültig ab', - 'Heading' => 'Überschrift', + 'Header' => 'Überschrift', 'Hide by default' => 'Standardmäßig verstecken', 'History' => 'Historie', 'History Search' => 'Historien Suche', - 'Include in drop-down menus' => 'In Aufklappmenü aufnehmen', 'Inventory' => 'Inventar', 'Inventory Account' => 'Warenbestand', - 'Is this a summary account to record' => 'Buchungskonto in', 'Language' => 'Sprache', 'Language deleted!' => 'Sprache gelöscht!', 'Language missing!' => 'Sprache fehlt!', @@ -177,9 +166,11 @@ $self->{texts} = { 'No employee was found matching the search parameters.' => 'Es wurde kein Angestellter gefunden, auf den die Suchparameter zutreffen.', 'No part was found matching the search parameters.' => 'Es wurde kein Artikel gefunden, auf den die Suchparameter zutreffen.', 'No project was found matching the search parameters.' => 'Es wurde kein Projekt gefunden, auf das die Suchparameter zutreffen.', + 'None' => 'Kein', 'Number' => 'Nummer', 'Number Format' => 'Zahlenformat', 'Number of copies' => 'Anzahl Kopien', + 'Off' => 'Aus', 'Old (on the side)' => 'Alt (seitlich)', 'On' => 'An', 'OpenDocument/OASIS' => 'OpenDocument/OASIS', @@ -192,10 +183,7 @@ $self->{texts} = { 'PRINTED' => 'Gedruckt', 'Part Number' => 'Artikelnummer', 'Part description' => 'Artikelbeschreibung', - 'Parts Inventory' => 'Warenliste', 'Password' => 'Passwort', - 'Payables' => 'Verbindlichkeiten', - 'Payment' => 'Zahlungsausgang', 'Payment Terms' => 'Zahlungskonditionen', 'Payment Terms saved!' => 'Zahlungskonditionen gespeichert!', 'Payment terms deleted!' => 'Zahlungskonditionen gelöscht!', @@ -215,8 +203,6 @@ $self->{texts} = { 'Project description' => 'Projektbeschreibung', 'Queue' => 'Warteschlange', 'Rate' => 'Rate', - 'Receipt' => 'Zahlungseingang', - 'Receivables' => 'Forderungen', 'Revenue' => 'Erlöskonto', 'Revenue Account' => 'Erlöskonto', 'SAVED' => 'Gespeichert', @@ -228,16 +214,14 @@ $self->{texts} = { 'Select a part' => 'Artikel auswählen', 'Select a project' => 'Projekt auswählen', 'Select an employee' => 'Angestellten auswählen', - 'Service Items' => 'Dienstleistungen', 'Setup Menu' => 'Menüsetup', 'Show by default' => 'Standardmäßig anzeigen', 'Signature' => 'Unterschrift', 'Skonto' => 'Skonto', 'Skonto Terms' => 'Zahlungsziel Skonto', - 'Steuersatz' => 'Steuersatz', 'Stylesheet' => 'Stilvorlage', - 'Tax' => 'Steuer', 'Tax Accounts' => 'Steuerkonto', + 'Tax-o-matic Account' => 'Automatikbuchung auf Konto', 'Template Code' => 'Vorlagenkürzel', 'Template Code missing!' => 'Vorlagenkürzel fehlt!', 'The \'tag\' field must only consist of alphanumeric characters or the carachters - _ ( )' => 'Das Feld \'tag\' darf nur aus alphanumerischen Zeichen und den Zeichen - _ ( ) bestehen.', @@ -260,53 +244,6 @@ $self->{texts} = { 'Translation (%s)' => 'Übersetzung (%s)', 'Trying to call a sub without a name' => 'Es wurde versucht, eine Unterfunktion ohne Namen aufzurufen.', 'Type of Business' => 'Kunden-/Lieferantentyp', - 'UStVA-Nr. 35' => 'Kz. 35', - 'UStVA-Nr. 36' => 'Kz. 36', - 'UStVA-Nr. 39' => 'Kz. 37', - 'UStVA-Nr. 41' => 'Kz. 41', - 'UStVA-Nr. 42' => 'Kz. 42', - 'UStVA-Nr. 43' => 'Kz. 43', - 'UStVA-Nr. 44' => 'Kz. 44', - 'UStVA-Nr. 45' => 'Kz. 45', - 'UStVA-Nr. 48' => 'Kz. 48', - 'UStVA-Nr. 49' => 'Kz. 49', - 'UStVA-Nr. 51 left' => 'Kz. 51 links', - 'UStVA-Nr. 51 right' => 'Kz. 51 rechts', - 'UStVA-Nr. 52' => 'Kz. 52', - 'UStVA-Nr. 53' => 'Kz. 53', - 'UStVA-Nr. 59' => 'Kz. 59', - 'UStVA-Nr. 60' => 'Kz. 60', - 'UStVA-Nr. 61' => 'Kz. 61', - 'UStVA-Nr. 62' => 'Kz. 62', - 'UStVA-Nr. 63' => 'Kz. 63', - 'UStVA-Nr. 64' => 'Kz. 64', - 'UStVA-Nr. 65' => 'Kz. 65', - 'UStVA-Nr. 66' => 'Kz. 66', - 'UStVA-Nr. 67' => 'Kz. 67', - 'UStVA-Nr. 69' => 'Kz. 69', - 'UStVA-Nr. 73' => 'Kz. 73', - 'UStVA-Nr. 74' => 'Kz. 74', - 'UStVA-Nr. 76' => 'Kz. 76', - 'UStVA-Nr. 77' => 'Kz. 77', - 'UStVA-Nr. 80' => 'Kz. 80', - 'UStVA-Nr. 81 left' => 'UStVA-Nr. 81 left', - 'UStVA-Nr. 81 right' => 'UStVA-Nr. 81 right', - 'UStVA-Nr. 84' => 'Kz. 84', - 'UStVA-Nr. 85' => 'Kz. 85', - 'UStVA-Nr. 86 left' => 'Kz. 86 links', - 'UStVA-Nr. 86 right' => 'Kz. 86 rechts', - 'UStVA-Nr. 89 left' => 'UStVA-Nr. 89 left', - 'UStVA-Nr. 89 right' => 'UStVA-Nr. 89 right', - 'UStVA-Nr. 91' => 'Kz. 91', - 'UStVA-Nr. 93 left' => 'Kz. 93 left', - 'UStVA-Nr. 93 right' => 'Kz. 93 right', - 'UStVA-Nr. 94' => 'Kz. 94', - 'UStVA-Nr. 95' => 'Kz. 95', - 'UStVA-Nr. 96' => 'Kz. 96', - 'UStVA-Nr. 97 links' => 'Kz. 97 links', - 'UStVA-Nr. 97 rechts' => 'Kz. 97 rechts', - 'UStVA-Nr. 98' => 'Kz. 98', - 'Umsatzsteuervoranmeldung' => 'Umsatzsteuervoranmeldung', 'Unit' => 'Einheit', 'Unknown Category' => 'Unbekannte Kategorie', 'Unknown Link' => 'Unbekannte Verknüpfung', @@ -387,6 +324,7 @@ $self->{subs} = { 'language_header' => 'language_header', 'lead_header' => 'lead_header', 'list_account' => 'list_account', + 'list_account_details' => 'list_account_details', 'list_buchungsgruppe' => 'list_buchungsgruppe', 'list_business' => 'list_business', 'list_department' => 'list_department', diff --git a/locale/de/dn b/locale/de/dn index eee5e7140..62096fd43 100644 --- a/locale/de/dn +++ b/locale/de/dn @@ -73,6 +73,7 @@ gestartet', 'Group Invoices' => 'Rechnungen zusammenfassen', 'History' => 'Historie', 'In-line' => 'im Text', + 'Interest' => 'Zinsen', 'Interest Rate' => 'Zinssatz', 'Inv. Duedate' => 'Rg. Fälligkeit', 'Invdate' => 'Rechnungsdatum', @@ -135,7 +136,6 @@ gestartet', 'Part Number' => 'Artikelnummer', 'Part description' => 'Artikelbeschreibung', 'Payment Terms missing in row ' => 'Zahlungsfrist fehlt in Zeile ', - 'Payment until' => 'Zahlungseingänge bis', 'Phone' => 'Telefon', 'Pick List' => 'Sammelliste', 'Please enter values' => 'Bitte Werte eingeben', @@ -194,7 +194,6 @@ gestartet', 'The dunning process started' => 'Der Mahnprozess ist gestartet.', 'To' => 'An', 'Total Fees' => 'Kumulierte Gebühren', - 'Total Interest' => 'Kumulierte Zinsen', 'Trying to call a sub without a name' => 'Es wurde versucht, eine Unterfunktion ohne Namen aufzurufen.', 'Unit' => 'Einheit', 'Unknown dependency \'%s\'.' => 'Unbekannte Abhängigkeit \'%s\'.', diff --git a/locale/de/ir b/locale/de/ir index cca849a41..760d7ba56 100644 --- a/locale/de/ir +++ b/locale/de/ir @@ -61,6 +61,7 @@ $self->{texts} = { 'E-mail address missing!' => 'E-Mail-Adresse fehlt!', 'ELSE' => 'Zusatz', 'Edit Vendor Invoice' => 'Einkaufsrechnung bearbeiten', + 'Employee' => 'Bearbeiter', 'Enter longdescription' => 'Langtext eingeben', 'Error in database control file \'%s\': %s' => 'Fehler in Datenbankupgradekontrolldatei \'%s\': %s', 'Exch' => 'Wechselkurs.', diff --git a/sql/Pg-upgrade2/dunning_config_interest_rate.sql b/sql/Pg-upgrade2/dunning_config_interest_rate.sql new file mode 100644 index 000000000..94e06d3c3 --- /dev/null +++ b/sql/Pg-upgrade2/dunning_config_interest_rate.sql @@ -0,0 +1,4 @@ +-- @tag: dunning_config_interest_rate +-- @description: In der Tabelle dunning_config ist interest falsch benannt. +-- @depends: release_2_4_2 +ALTER TABLE dunning_config RENAME interest TO interest_rate; -- 2.20.1