1 #======================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Dunning process module
33 #======================================================================
44 $main::lxdebug->enter_sub();
46 my ($self, $myconfig, $form) = @_;
49 my $dbh = $form->dbconnect($myconfig);
53 qq|FROM dunning_config | .
54 qq|ORDER BY dunning_level|;
55 $form->{DUNNING} = selectall_hashref_query($form, $dbh, $query);
57 foreach my $ref (@{ $form->{DUNNING} }) {
58 $ref->{fee} = $form->format_amount($myconfig, $ref->{fee}, 2);
59 $ref->{interest_rate} = $form->format_amount($myconfig, ($ref->{interest_rate} * 100));
64 $main::lxdebug->leave_sub();
68 $main::lxdebug->enter_sub();
70 my ($self, $myconfig, $form) = @_;
73 my $dbh = $form->dbconnect_noauto($myconfig);
77 for my $i (1 .. $form->{rowcount}) {
78 $form->{"fee_$i"} = $form->parse_amount($myconfig, $form->{"fee_$i"}) * 1;
79 $form->{"interest_rate_$i"} = $form->parse_amount($myconfig, $form->{"interest_rate_$i"}) / 100;
81 if (($form->{"dunning_level_$i"} ne "") &&
82 ($form->{"dunning_description_$i"} ne "")) {
83 @values = (conv_i($form->{"dunning_level_$i"}), $form->{"dunning_description_$i"},
84 $form->{"email_subject_$i"}, $form->{"email_body_$i"},
85 $form->{"template_$i"}, $form->{"fee_$i"}, $form->{"interest_rate_$i"},
86 $form->{"active_$i"} ? 't' : 'f', $form->{"auto_$i"} ? 't' : 'f', $form->{"email_$i"} ? 't' : 'f',
87 $form->{"email_attachment_$i"} ? 't' : 'f', conv_i($form->{"payment_terms_$i"}), conv_i($form->{"terms_$i"}));
88 if ($form->{"id_$i"}) {
90 qq|UPDATE dunning_config SET
91 dunning_level = ?, dunning_description = ?,
92 email_subject = ?, email_body = ?,
93 template = ?, fee = ?, interest_rate = ?,
94 active = ?, auto = ?, email = ?,
95 email_attachment = ?, payment_terms = ?, terms = ?
97 push(@values, conv_i($form->{"id_$i"}));
100 qq|INSERT INTO dunning_config
101 (dunning_level, dunning_description, email_subject, email_body,
102 template, fee, interest_rate, active, auto, email,
103 email_attachment, payment_terms, terms)
104 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
106 do_query($form, $dbh, $query, @values);
109 if (($form->{"dunning_description_$i"} eq "") && ($form->{"id_$i"})) {
110 $query = qq|DELETE FROM dunning_config WHERE id = ?|;
111 do_query($form, $dbh, $query, $form->{"id_$i"});
118 $main::lxdebug->leave_sub();
122 $main::lxdebug->enter_sub();
124 my ($self, $myconfig, $form, $rows, $userspath, $spool, $sendmail) = @_;
125 # connect to database
126 my $dbh = $form->dbconnect_noauto($myconfig);
128 my ($query, @values);
130 my ($dunning_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
132 my $q_update_ar = qq|UPDATE ar SET dunning_config_id = ? WHERE id = ?|;
133 my $h_update_ar = prepare_query($form, $dbh, $q_update_ar);
135 my $q_insert_dunning =
136 qq|INSERT INTO dunning (dunning_id, dunning_config_id, dunning_level,
137 trans_id, fee, interest, transdate, duedate)
139 (SELECT dunning_level FROM dunning_config WHERE id = ?),
143 WHERE dunning_level <= (SELECT dunning_level FROM dunning_config WHERE id = ?)),
144 (SELECT (amount - paid) * (current_date - transdate) FROM ar WHERE id = ?)
145 * (SELECT interest_rate FROM dunning_config WHERE id = ?)
148 current_date + (SELECT payment_terms FROM dunning_config WHERE id = ?))|;
149 my $h_insert_dunning = prepare_query($form, $dbh, $q_insert_dunning);
152 my ($next_dunning_config_id, $customer_id);
155 foreach my $row (@{ $rows }) {
156 push @invoice_ids, $row->{invoice_id};
157 $next_dunning_config_id = $row->{next_dunning_config_id};
158 $customer_id = $row->{customer_id};
160 @values = ($row->{next_dunning_config_id}, $row->{invoice_id});
161 do_statement($form, $h_update_ar, $q_update_ar, @values);
163 $send_email |= $row->{email};
165 my $next_config_id = conv_i($row->{next_dunning_config_id});
166 my $invoice_id = conv_i($row->{invoice_id});
168 @values = ($dunning_id, $next_config_id, $next_config_id,
169 $invoice_id, $next_config_id, $invoice_id,
170 $next_config_id, $next_config_id);
171 do_statement($form, $h_insert_dunning, $q_insert_dunning, @values);
174 $h_update_ar->finish();
175 $h_insert_dunning->finish();
179 ar.invnumber, ar.ordnumber, ar.amount, ar.netamount,
180 ar.transdate, ar.duedate, ar.paid, ar.amount - ar.paid AS open_amount,
181 da.fee, da.interest, da.transdate AS dunning_date, da.duedate AS dunning_duedate
183 LEFT JOIN dunning_config cfg ON (cfg.id = ar.dunning_config_id)
184 LEFT JOIN dunning da ON (ar.id = da.trans_id AND cfg.dunning_level = da.dunning_level)
186 . join(", ", map { "?" } @invoice_ids) . qq|)|;
188 my $sth = prepare_execute_query($form, $dbh, $query, @invoice_ids);
190 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
192 map({ $form->{"dn_$_"} = []; } keys(%{$ref}));
196 $ref->{interest_rate} = $form->format_amount($myconfig, $ref->{interest_rate} * 100);
197 map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2) } qw(amount netamount paid open_amount fee interest);
198 map { push(@{ $form->{"dn_$_"} }, $ref->{$_})} keys %$ref;
199 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
204 qq|SELECT id AS customer_id, name, street, zipcode, city, country, department_1, department_2, email
207 $ref = selectfirst_hashref_query($form, $dbh, $query, $customer_id);
208 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
212 cfg.interest_rate, cfg.template AS formname,
213 cfg.email_subject, cfg.email_body, cfg.email_attachment,
216 WHERE dunning_id = ?)
218 (SELECT SUM(interest)
220 WHERE dunning_id = ?)
222 (SELECT SUM(amount) - SUM(paid)
224 WHERE id IN (| . join(", ", map { "?" } @invoice_ids) . qq|))
226 FROM dunning_config cfg
228 $ref = selectfirst_hashref_query($form, $dbh, $query, $dunning_id, $dunning_id, @invoice_ids, $next_dunning_config_id);
229 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
231 $form->{interest_rate} = $form->format_amount($myconfig, $ref->{interest_rate} * 100);
232 $form->{fee} = $form->format_amount($myconfig, $ref->{fee}, 2);
233 $form->{total_interest} = $form->format_amount($myconfig, $form->round_amount($ref->{total_interest}, 2), 2);
234 $form->{total_open_amount} = $form->format_amount($myconfig, $form->round_amount($ref->{total_open_amount}, 2), 2);
235 $form->{total_amount} = $form->format_amount($myconfig, $form->round_amount($ref->{fee} + $ref->{total_interest} + $ref->{total_open_amount}, 2), 2);
237 $form->{templates} = "$myconfig->{templates}";
238 $form->{language} = $form->get_template_language(\%myconfig);
239 $form->{printer_code} = $form->get_printer_code(\%myconfig);
241 if ($form->{language} ne "") {
242 $form->{language} = "_" . $form->{language};
245 if ($form->{printer_code} ne "") {
246 $form->{printer_code} = "_" . $form->{printer_code};
249 $form->{IN} = "$form->{formname}$form->{language}$form->{printer_code}.html";
250 if ($form->{format} eq 'postscript') {
251 $form->{postscript} = 1;
252 $form->{IN} =~ s/html$/tex/;
253 } elsif ($form->{"format"} =~ /pdf/) {
255 if ($form->{"format"} =~ /opendocument/) {
256 $form->{IN} =~ s/html$/odt/;
258 $form->{IN} =~ s/html$/tex/;
260 } elsif ($form->{"format"} =~ /opendocument/) {
261 $form->{"opendocument"} = 1;
262 $form->{"IN"} =~ s/html$/odt/;
265 if ($send_email && ($form->{email} ne "")) {
266 $form->{media} = 'email';
269 $form->{keep_tmpfile} = 0;
270 if ($form->{media} eq 'email') {
271 $form->{subject} = qq|$form->{label} $form->{"${inv}number"}|
272 unless $form->{subject};
273 if (!$form->{email_attachment}) {
274 $form->{do_not_attach} = 1;
276 $form->{do_not_attach} = 0;
278 $form->{subject} = parse_strings($myconfig, $form, $userspath, $form->{email_subject});
279 $form->{message} = parse_strings($myconfig, $form, $userspath, $form->{email_body});
281 $form->{OUT} = "$sendmail";
285 my $filename = Common::unique_id() . $form->{login} . ".pdf";
286 $form->{OUT} = ">$spool/$filename";
287 push(@{ $form->{DUNNING_PDFS} }, $filename);
288 $form->{keep_tmpfile} = 1;
291 delete($form->{tmpfile});
292 $form->parse_template($myconfig, $userspath);
297 $main::lxdebug->leave_sub();
302 $main::lxdebug->enter_sub();
304 my ($self, $myconfig, $form) = @_;
306 # connect to database
307 my $dbh = $form->dbconnect($myconfig);
312 $form->{customer_id} = $1 if ($form->{customer} =~ /--(\d+)$/);
314 if ($form->{customer_id}) {
315 $where .= qq| AND (a.customer_id = ?)|;
316 push(@values, $form->{customer_id});
318 } elsif ($form->{customer}) {
319 $where .= qq| AND (ct.name ILIKE ?)|;
320 push(@values, '%' . $form->{customer} . '%');
324 "ordnumber" => "a.ordnumber",
325 "invnumber" => "a.invnumber",
326 "notes" => "a.notes",
328 foreach my $key (keys(%columns)) {
329 next unless ($form->{$key});
330 $where .= qq| AND $columns{$key} ILIKE ?|;
331 push(@values, '%' . $form->{$key} . '%');
334 if ($form->{dunning_level}) {
335 $where .= qq| AND nextcfg.id = ?|;
336 push(@values, conv_i($form->{dunning_level}));
339 $form->{minamount} = $form->parse_amount($myconfig,$form->{minamount});
340 if ($form->{minamount}) {
341 $where .= qq| AND ((a.amount - a.paid) > ?) |;
342 push(@values, $form->{minamount});
347 a.id, a.ordnumber, a.transdate, a.invnumber, a.amount,
348 ct.name AS customername, a.customer_id, a.duedate,
350 cfg.dunning_description, cfg.dunning_level,
352 d.transdate AS dunning_date, d.duedate AS dunning_duedate,
355 a.duedate + cfg.terms - current_date AS nextlevel,
356 current_date - COALESCE(d.duedate, a.duedate) AS pastdue,
357 current_date + cfg.payment_terms AS next_duedate,
359 nextcfg.dunning_description AS next_dunning_description,
360 nextcfg.id AS next_dunning_config_id,
361 nextcfg.terms, nextcfg.active, nextcfg.email
365 LEFT JOIN customer ct ON (a.customer_id = ct.id)
366 LEFT JOIN dunning_config cfg ON (a.dunning_config_id = cfg.id)
367 LEFT JOIN dunning_config nextcfg ON
371 WHERE dunning_level >
372 COALESCE((SELECT dunning_level
374 WHERE id = a.dunning_config_id
375 ORDER BY dunning_level DESC
379 LEFT JOIN dunning d ON ((d.trans_id = a.id) AND (cfg.dunning_level = d.dunning_level))
381 WHERE (a.paid < a.amount)
382 AND (a.duedate < current_date)
386 ORDER BY a.id, transdate, duedate, name|;
387 my $sth = prepare_execute_query($form, $dbh, $query, @values);
389 $form->{DUNNINGS} = [];
391 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
392 next if !$ref->{terms} || ($ref->{pastdue} < $ref->{terms});
394 $ref->{interest} = $form->round_amount($ref->{interest}, 2);
395 push(@{ $form->{DUNNINGS} }, $ref);
400 $query = qq|SELECT id, dunning_description FROM dunning_config ORDER BY dunning_level|;
401 $form->{DUNNING_CONFIG} = selectall_hashref_query($form, $dbh, $query);
404 $main::lxdebug->leave_sub();
409 $main::lxdebug->enter_sub();
411 my ($self, $myconfig, $form) = @_;
413 # connect to database
414 my $dbh = $form->dbconnect($myconfig);
416 $where = qq| WHERE (da.trans_id = a.id)|;
420 if ($form->{customer_id}) {
421 $where .= qq| AND (a.customer_id = ?)|;
422 push(@values, $form->{customer_id});
424 } elsif ($form->{customer}) {
425 $where .= qq| AND (ct.name ILIKE ?)|;
426 push(@values, '%' . $form->{customer} . '%');
430 "ordnumber" => "a.ordnumber",
431 "invnumber" => "a.invnumber",
432 "notes" => "a.notes",
434 foreach my $key (keys(%columns)) {
435 next unless ($form->{$key});
436 $where .= qq| AND $columns{$key} ILIKE ?|;
437 push(@values, '%' . $form->{$key} . '%');
440 if ($form->{dunning_level}) {
441 $where .= qq| AND a.dunning_config_id = ?|;
442 push(@values, conv_i($form->{dunning_level}));
445 if ($form->{department_id}) {
446 $where .= qq| AND a.department_id = ?|;
447 push @values, conv_i($form->{department_id});
450 $form->{minamount} = $form->parse_amount($myconfig, $form->{minamount});
451 if ($form->{minamount}) {
452 $where .= qq| AND ((a.amount - a.paid) > ?) |;
453 push(@values, $form->{minamount});
456 if (!$form->{showold}) {
457 $where .= qq| AND (a.amount > a.paid) AND (da.dunning_config_id = a.dunning_config_id) |;
460 if ($form->{transdatefrom}) {
461 $where .= qq| AND a.transdate >= ?|;
462 push(@values, $form->{transdatefrom});
464 if ($form->{transdateto}) {
465 $where .= qq| AND a.transdate <= ?|;
466 push(@values, $form->{transdateto});
468 if ($form->{dunningfrom}) {
469 $where .= qq| AND da.transdate >= ?|;
470 push(@values, $form->{dunningfrom});
472 if ($form->{dunningto}) {
473 $where .= qq| AND da.transdate >= ?|;
474 push(@values, $form->{dunningto});
478 qq|SELECT a.id, a.ordnumber, a.invoice, a.transdate, a.invnumber, a.amount,
479 ct.name AS customername, ct.id AS customer_id, a.duedate, da.fee,
480 da.interest, dn.dunning_description, da.transdate AS dunning_date,
481 da.duedate AS dunning_duedate, da.dunning_id, da.dunning_config_id
483 JOIN customer ct ON (a.customer_id = ct.id), dunning da
484 LEFT JOIN dunning_config dn ON (da.dunning_config_id = dn.id)
486 ORDER BY name, a.id|;
488 $form->{DUNNINGS} = selectall_hashref_query($form, $dbh, $query, @values);
490 foreach my $ref (@{ $form->{DUNNINGS} }) {
491 map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2)} qw(amount fee interest);
495 $main::lxdebug->leave_sub();
500 $main::lxdebug->enter_sub();
502 my ($myconfig, $form, $userspath, $string) = @_;
506 my $format = $form->{format};
507 $form->{format} = "html";
509 $tmpstring = "parse_string.html";
510 $tmpfile = "$myconfig->{templates}/$tmpstring";
511 open(OUT, ">", $tmpfile) or $form->error("$tmpfile : $!");
516 my $in = $form->{IN};
517 $form->{IN} = $tmpstring;
518 $template = HTMLTemplate->new($tmpstring, $form, $myconfig, $userspath);
521 $form->{tmpfile} = "$userspath/${fileid}.$tmpstring";
523 open(OUT, ">", $form->{tmpfile}) or $form->error("$form->{OUT} : $!");
524 if (!$template->parse(*OUT)) {
526 $form->error("$form->{IN} : " . $template->get_error());
531 open(IN, "<", $form->{tmpfile}) or $form->error($form->cleanup . "$form->{tmpfile} : $!");
539 # unlink($form->{tmpfile});
541 $form->{format} = $format;
543 $main::lxdebug->leave_sub();
549 $main::lxdebug->enter_sub();
551 my ($self, $myconfig, $form, $userspath) = @_;
555 # Don't allow access outside of $userspath.
556 map { $_ =~ s|.*/||; } @{ $form->{DUNNING_PDFS} };
558 my $inputfiles = join " ", map { "$userspath/$_" } @{ $form->{DUNNING_PDFS} };
559 my $outputfile = "$userspath/dunning.pdf";
561 system("gs -dBATCH -dNOPAUSE -q -sDEVICE=pdfwrite -sOutputFile=$outputfile $inputfiles");
563 map { unlink("$userspath/$_") } @{ $form->{DUNNING_PDFS} };
565 my $numbytes = (-s $outputfile);
566 open(IN, $outputfile) || $form->error($self->cleanup() . "$outputfile : $!");
568 $form->{copies} = 1 unless $form->{media} eq 'printer';
572 for my $i (1 .. $form->{copies}) {
574 print qq|Content-Type: Application/PDF
575 Content-Disposition: attachment; filename="$outputfile"
576 Content-Length: $numbytes
580 open(OUT, ">-") or $form->error($form->cleanup . "$!: STDOUT");
594 $main::lxdebug->leave_sub();
598 $main::lxdebug->enter_sub();
600 my ($self, $myconfig, $form, $dunning_id, $userspath, $spool, $sendmail) = @_;
601 # connect to database
602 my $dbh = $form->dbconnect_noauto($myconfig);
605 qq|SELECT invnumber, ordnumber, customer_id, amount, netamount,
606 ar.transdate, ar.duedate, paid, amount - paid AS open_amount,
607 template AS formname, email_subject, email_body, email_attachment,
608 da.fee, da.interest, da.transdate AS dunning_date, da.duedate AS dunning_duedate
610 LEFT JOIN dunning_config ON (dunning_config.id = da.dunning_config_id)
611 LEFT JOIN ar ON (ar.id = da.trans_id)
612 WHERE (da.dunning_id = ?)|;
614 my $sth = prepare_execute_query($form, $dbh, $query, $dunning_id);
616 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
618 map({ $form->{"dn_$_"} = []; } keys(%{$ref}));
621 map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2) } qw(amount netamount paid open_amount fee interest);
622 map { $form->{$_} = $ref->{$_} } keys %$ref;
623 map { push @{ $form->{"dn_$_"} }, $ref->{$_}} keys %$ref;
628 qq|SELECT id AS customer_id, name, street, zipcode, city, country, department_1, department_2, email
633 LEFT JOIN ar ON (d.trans_id = ar.id)
635 $ref = selectfirst_hashref_query($form, $dbh, $query, $dunning_id);
636 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
640 cfg.interest_rate, cfg.template AS formname,
641 cfg.email_subject, cfg.email_body, cfg.email_attachment,
642 d.transdate AS dunning_date,
645 WHERE dunning_id = ?)
647 (SELECT SUM(interest)
649 WHERE dunning_id = ?)
651 (SELECT SUM(amount) - SUM(paid)
656 WHERE dunning_id = ?))
659 LEFT JOIN dunning_config cfg ON (d.dunning_config_id = cfg.id)
660 WHERE d.dunning_id = ?
662 $ref = selectfirst_hashref_query($form, $dbh, $query, $dunning_id, $dunning_id, $dunning_id, $dunning_id);
663 map { $form->{$_} = $ref->{$_} } keys %{ $ref };
665 $form->{interest_rate} = $form->format_amount($myconfig, $ref->{interest_rate} * 100);
666 $form->{fee} = $form->format_amount($myconfig, $ref->{fee}, 2);
667 $form->{total_interest} = $form->format_amount($myconfig, $form->round_amount($ref->{total_interest}, 2), 2);
668 $form->{total_open_amount} = $form->format_amount($myconfig, $form->round_amount($ref->{total_open_amount}, 2), 2);
669 $form->{total_amount} = $form->format_amount($myconfig, $form->round_amount($ref->{fee} + $ref->{total_interest} + $ref->{total_open_amount}, 2), 2);
672 $form->{templates} = "$myconfig->{templates}";
674 $form->{language} = $form->get_template_language(\%myconfig);
675 $form->{printer_code} = $form->get_printer_code(\%myconfig);
677 if ($form->{language} ne "") {
678 $form->{language} = "_" . $form->{language};
681 if ($form->{printer_code} ne "") {
682 $form->{printer_code} = "_" . $form->{printer_code};
685 $form->{IN} = "$form->{formname}$form->{language}$form->{printer_code}.html";
686 if ($form->{format} eq 'postscript') {
687 $form->{postscript} = 1;
688 $form->{IN} =~ s/html$/tex/;
689 } elsif ($form->{"format"} =~ /pdf/) {
691 if ($form->{"format"} =~ /opendocument/) {
692 $form->{IN} =~ s/html$/odt/;
694 $form->{IN} =~ s/html$/tex/;
696 } elsif ($form->{"format"} =~ /opendocument/) {
697 $form->{"opendocument"} = 1;
698 $form->{"IN"} =~ s/html$/odt/;
701 if ($form->{"send_email"} && ($form->{email} ne "")) {
702 $form->{media} = 'email';
705 $form->{keep_tmpfile} = 0;
706 if ($form->{media} eq 'email') {
707 $form->{subject} = qq|$form->{label} $form->{"${inv}number"}|
708 unless $form->{subject};
709 if (!$form->{email_attachment}) {
710 $form->{do_not_attach} = 1;
712 $form->{do_not_attach} = 0;
714 $form->{subject} = parse_strings($myconfig, $form, $userspath, $form->{email_subject});
715 $form->{message} = parse_strings($myconfig, $form, $userspath, $form->{email_body});
717 $form->{OUT} = "$sendmail";
721 my $filename = Common::unique_id() . $form->{login} . ".pdf";
723 push(@{ $form->{DUNNING_PDFS} }, $filename);
724 $form->{keep_tmpfile} = 1;
727 $form->parse_template($myconfig, $userspath);
732 $main::lxdebug->leave_sub();