Revision 840aea13
Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt
| SL/DN.pm | ||
|---|---|---|
| 121 | 121 |
sub save_dunning {
|
| 122 | 122 |
$main::lxdebug->enter_sub(); |
| 123 | 123 |
|
| 124 |
my ($self, $myconfig, $form, $rows, $userspath,$spool, $sendmail) = @_; |
|
| 124 |
my ($self, $myconfig, $form, $rows, $userspath, $spool, $sendmail) = @_;
|
|
| 125 | 125 |
# connect to database |
| 126 | 126 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 127 | 127 |
|
| 128 | 128 |
my ($query, @values); |
| 129 | 129 |
|
| 130 |
my ($dunning_id) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
|
|
| 131 |
|
|
| 130 | 132 |
foreach my $row (@{ $rows }) {
|
| 131 | 133 |
|
| 132 | 134 |
$form->{"interest_$row"} = $form->parse_amount($myconfig,$form->{"interest_$row"});
|
| 133 | 135 |
$form->{"fee_$row"} = $form->parse_amount($myconfig,$form->{"fee_$row"});
|
| 134 | 136 |
$form->{send_email} = $form->{"email_$row"};
|
| 135 | 137 |
|
| 136 |
$query = qq|UPDATE ar SET dunning_id = ? WHERE id = ?|; |
|
| 137 |
@values = ($form->{"next_dunning_id_$row"}, $form->{"inv_id_$row"});
|
|
| 138 |
$query = qq|UPDATE ar SET dunning_config_id = ? WHERE id = ?|; |
|
| 139 |
@values = ($form->{"next_dunning_config_id_$row"},
|
|
| 140 |
$form->{"inv_id_$row"});
|
|
| 138 | 141 |
do_query($form, $dbh, $query, @values); |
| 142 |
|
|
| 139 | 143 |
$query = |
| 140 |
qq|INSERT INTO dunning (dunning_id, dunning_level, trans_id, fee,
|
|
| 141 |
interest, transdate, duedate) |
|
| 142 |
VALUES (?, (SELECT dunning_level FROM dunning_config WHERE id = ?), |
|
| 144 |
qq|INSERT INTO dunning (dunning_id, dunning_config_id, dunning_level,
|
|
| 145 |
trans_id, fee, interest, transdate, duedate)
|
|
| 146 |
VALUES (?, ?, (SELECT dunning_level FROM dunning_config WHERE id = ?),
|
|
| 143 | 147 |
?, ?, ?, current_date, ?)|; |
| 144 |
@values = (conv_i($form->{"next_dunning_id_$row"}),
|
|
| 145 |
conv_i($form->{"next_dunning_id_$row"}),
|
|
| 148 |
@values = ($dunning_id, |
|
| 149 |
conv_i($form->{"next_dunning_config_id_$row"}),
|
|
| 150 |
conv_i($form->{"next_dunning_config_id_$row"}),
|
|
| 146 | 151 |
conv_i($form->{"inv_id_$row"}), $form->{"fee_$row"},
|
| 147 | 152 |
$form->{"interest_$row"},
|
| 148 | 153 |
conv_date($form->{"next_duedate_$row"}));
|
| ... | ... | |
| 155 | 160 |
template AS formname, email_subject, email_body, email_attachment, |
| 156 | 161 |
da.fee, da.interest, da.transdate AS dunning_date, |
| 157 | 162 |
da.duedate AS dunning_duedate |
| 158 |
FROM ar LEFT JOIN dunning_config ON (dunning_config.id = ar.dunning_id) |
|
| 163 |
FROM ar LEFT JOIN dunning_config ON (dunning_config.id = ar.dunning_config_id)
|
|
| 159 | 164 |
LEFT JOIN dunning da ON (ar.id = da.trans_id AND dunning_config.dunning_level = da.dunning_level) |
| 160 | 165 |
WHERE ar.id IN (| |
| 161 | 166 |
. join(", ", map("?", @{ $form->{"inv_ids"} })) . qq|)|;
|
| ... | ... | |
| 233 | 238 |
$form->{keep_tmpfile} = 1;
|
| 234 | 239 |
} |
| 235 | 240 |
|
| 241 |
delete($form->{tmpfile});
|
|
| 236 | 242 |
$form->parse_template($myconfig, $userspath); |
| 237 | 243 |
|
| 238 | 244 |
$dbh->commit; |
| ... | ... | |
| 258 | 264 |
WHERE dunning_level > |
| 259 | 265 |
(SELECT |
| 260 | 266 |
CASE |
| 261 |
WHEN a.dunning_id IS NULL |
|
| 267 |
WHEN a.dunning_config_id IS NULL
|
|
| 262 | 268 |
THEN 0 |
| 263 |
ELSE (SELECT dunning_level FROM dunning_config WHERE id = a.dunning_id ORDER BY dunning_level LIMIT 1) |
|
| 269 |
ELSE (SELECT dunning_level |
|
| 270 |
FROM dunning_config |
|
| 271 |
WHERE id = a.dunning_config_id |
|
| 272 |
ORDER BY dunning_level |
|
| 273 |
LIMIT 1) |
|
| 264 | 274 |
END |
| 265 | 275 |
FROM dunning_config LIMIT 1) |
| 266 | 276 |
LIMIT 1)) |; |
| ... | ... | |
| 289 | 299 |
} |
| 290 | 300 |
|
| 291 | 301 |
if ($form->{dunning_level}) {
|
| 292 |
$where .= qq| AND a.dunning_id = ?|; |
|
| 302 |
$where .= qq| AND a.dunning_config_id = ?|;
|
|
| 293 | 303 |
push(@values, conv_i($form->{dunning_level}));
|
| 294 | 304 |
} |
| 295 | 305 |
|
| ... | ... | |
| 310 | 320 |
a.duedate + dnn.terms - current_date AS nextlevel, |
| 311 | 321 |
$paymentdate - a.duedate AS pastdue, dn.dunning_level, |
| 312 | 322 |
current_date + dnn.payment_terms AS next_duedate, |
| 313 |
dnn.dunning_description AS next_dunning_description, dnn.id AS next_dunning_id, |
|
| 323 |
dnn.dunning_description AS next_dunning_description, dnn.id AS next_dunning_config_id,
|
|
| 314 | 324 |
dnn.interest AS interest_rate, dnn.terms |
| 315 | 325 |
FROM dunning_config dnn, ar a |
| 316 | 326 |
JOIN customer ct ON (a.customer_id = ct.id) |
| 317 |
LEFT JOIN dunning_config dn ON (dn.id = a.dunning_id) |
|
| 327 |
LEFT JOIN dunning_config dn ON (dn.id = a.dunning_config_id)
|
|
| 318 | 328 |
LEFT JOIN dunning da ON ((da.trans_id = a.id) AND (dn.dunning_level = da.dunning_level)) |
| 319 | 329 |
$where |
| 320 | 330 |
ORDER BY a.id, transdate, duedate, name|; |
| ... | ... | |
| 378 | 388 |
} |
| 379 | 389 |
|
| 380 | 390 |
if ($form->{dunning_level}) {
|
| 381 |
$where .= qq| AND a.dunning_id = ?|; |
|
| 391 |
$where .= qq| AND a.dunning_config_id = ?|;
|
|
| 382 | 392 |
push(@values, conv_i($form->{dunning_level}));
|
| 383 | 393 |
} |
| 384 | 394 |
|
| ... | ... | |
| 389 | 399 |
} |
| 390 | 400 |
|
| 391 | 401 |
if (!$form->{showold}) {
|
| 392 |
$where .= qq| AND (a.amount > a.paid) AND (da.dunning_id = a.dunning_id) |;
|
|
| 402 |
$where .= qq| AND (a.amount > a.paid) AND (da.dunning_config_id = a.dunning_config_id) |;
|
|
| 393 | 403 |
} |
| 394 | 404 |
|
| 395 | 405 |
if ($form->{transdatefrom}) {
|
| ... | ... | |
| 411 | 421 |
|
| 412 | 422 |
$query = |
| 413 | 423 |
qq|SELECT a.id, a.ordnumber, a.invoice, a.transdate, a.invnumber, a.amount, |
| 414 |
ct.name AS customername, ct.id AS customer_id, a.duedate, da.fee, |
|
| 415 |
da.interest, dn.dunning_description, da.transdate AS dunning_date, da.duedate AS dunning_duedate, da.dunning_id |
|
| 416 |
FROM ar a |
|
| 417 |
JOIN customer ct ON (a.customer_id = ct.id), dunning da |
|
| 418 |
LEFT JOIN dunning_config dn ON (da.dunning_id = dn.id) |
|
| 419 |
$where |
|
| 420 |
ORDER BY name, a.id|; |
|
| 424 |
ct.name AS customername, ct.id AS customer_id, a.duedate, da.fee, |
|
| 425 |
da.interest, dn.dunning_description, da.transdate AS dunning_date, |
|
| 426 |
da.duedate AS dunning_duedate, da.dunning_id, da.dunning_config_id |
|
| 427 |
FROM ar a |
|
| 428 |
JOIN customer ct ON (a.customer_id = ct.id), dunning da |
|
| 429 |
LEFT JOIN dunning_config dn ON (da.dunning_config_id = dn.id) |
|
| 430 |
$where |
|
| 431 |
ORDER BY name, a.id|; |
|
| 421 | 432 |
|
| 422 | 433 |
$form->{DUNNINGS} = selectall_hashref_query($form, $dbh, $query, @values);
|
| 423 | 434 |
|
| ... | ... | |
| 485 | 496 |
|
| 486 | 497 |
my ($self, $myconfig, $form, $userspath) = @_; |
| 487 | 498 |
|
| 488 |
map({ $_ =~ s|.*/||g; } @{ $form->{DUNNING_PDFS} });
|
|
| 489 |
|
|
| 490 | 499 |
foreach my $file (@{ $form->{DUNNING_PDFS} }) {
|
| 491 | 500 |
$inputfiles .= " $userspath/$file "; |
| 492 | 501 |
} |
| ... | ... | |
| 543 | 552 |
sub print_dunning {
|
| 544 | 553 |
$main::lxdebug->enter_sub(); |
| 545 | 554 |
|
| 546 |
my ($self, $myconfig, $form, $dunning_id, $customer_id, $userspath,$spool, $sendmail) = @_;
|
|
| 555 |
my ($self, $myconfig, $form, $dunning_id, $userspath,$spool, $sendmail) = @_; |
|
| 547 | 556 |
# connect to database |
| 548 | 557 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 549 | 558 |
|
| ... | ... | |
| 552 | 561 |
ar.transdate, ar.duedate, paid, amount - paid AS open_amount, |
| 553 | 562 |
template AS formname, email_subject, email_body, email_attachment, |
| 554 | 563 |
da.fee, da.interest, da.transdate AS dunning_date, da.duedate AS dunning_duedate |
| 555 |
FROM ar
|
|
| 556 |
LEFT JOIN dunning_config ON (dunning_config.id = ar.dunning_id)
|
|
| 557 |
LEFT JOIN dunning da ON ((ar.id = da.trans_id) AND (dunning_config.dunning_level = da.dunning_level))
|
|
| 558 |
WHERE (ar.dunning_id = ?) AND (customer_id = ?)|;
|
|
| 564 |
FROM dunning da
|
|
| 565 |
LEFT JOIN dunning_config ON (dunning_config.id = da.dunning_config_id)
|
|
| 566 |
LEFT JOIN ar ON (ar.id = da.trans_id)
|
|
| 567 |
WHERE (da.dunning_id = ?)|;
|
|
| 559 | 568 |
|
| 560 |
my $sth = prepare_execute_query($form, $dbh, $query, $dunning_id, $customer_id);
|
|
| 569 |
my $sth = prepare_execute_query($form, $dbh, $query, $dunning_id); |
|
| 561 | 570 |
my $first = 1; |
| 562 | 571 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 563 | 572 |
if ($first) {
|
Auch abrufbar als: Unified diff
Mahnwesen: Die Tabelle dunning so umgebaut, dass gemeinsam gestartete Mahnungen auch später gemeinsam erneut ausgedruckt werden können. Dafür auch die Listenansicht bereits gestarteter Mahnungen verbessert.