$self->send_email($myconfig, $form, $dunning_id, $dbh);
}
-# $dbh->commit();
+ $dbh->commit();
$dbh->disconnect();
$main::lxdebug->leave_sub();
push(@values, $form->{minamount});
}
+ $query =
+ qq|SELECT id
+ FROM dunning_config
+ WHERE dunning_level = (SELECT MAX(dunning_level) FROM dunning_config)|;
+ my ($id_for_max_dunning_level) = selectrow_query($form, $dbh, $query);
+
$query =
qq|SELECT
a.id, a.ordnumber, a.transdate, a.invnumber, a.amount,
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))
+ COALESCE(
+ (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)
+ ORDER BY dunning_level ASC
+ LIMIT 1)
+ , ?))
LEFT JOIN dunning d ON ((d.trans_id = a.id) AND (cfg.dunning_level = d.dunning_level))
WHERE (a.paid < a.amount)
$where
ORDER BY a.id, transdate, duedate, name|;
- my $sth = prepare_execute_query($form, $dbh, $query, @values);
+ my $sth = prepare_execute_query($form, $dbh, $query, $id_for_max_dunning_level, @values);
$form->{DUNNINGS} = [];
ar.transdate, ar.duedate, ar.customer_id,
ar.invnumber, ar.ordnumber,
ar.amount, ar.netamount, ar.paid,
- ar.amount - ar.paid AS open_amount
+ ar.amount - ar.paid AS open_amount,
+ ar.amount - ar.paid + da.fee + da.interest AS linetotal
FROM dunning da
LEFT JOIN dunning_config dcfg ON (dcfg.id = da.dunning_config_id)
map({ $form->{"dn_$_"} = []; } keys(%{$ref}));
$first = 0;
}
- map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2) } qw(amount netamount paid open_amount fee interest);
+ map { $ref->{$_} = $form->format_amount($myconfig, $ref->{$_}, 2) } qw(amount netamount paid open_amount fee interest linetotal);
map { $form->{$_} = $ref->{$_} } keys %$ref;
map { push @{ $form->{"dn_$_"} }, $ref->{$_}} keys %$ref;
}
$query =
qq|SELECT
c.id AS customer_id, c.name, c.street, c.zipcode, c.city,
- c.country, c.department_1, c.department_2, c.email
+ c.country, c.department_1, c.department_2, c.email, c.customernumber
FROM dunning d
LEFT JOIN ar ON (d.trans_id = ar.id)
LEFT JOIN customer c ON (ar.customer_id = c.id)
delete $form->{tmpfile};
push @{ $form->{DUNNING_PDFS} }, $filename;
- push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => $filename,
+ push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => "${main::spool}/$filename",
'name' => "dunning_${dunning_id}.pdf" };
$form->parse_template($myconfig, $main::userspath);
restore_form($saved_form);
push @{ $form->{DUNNING_PDFS} }, $filename;
- push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => $filename,
+ push @{ $form->{DUNNING_PDFS_EMAIL} }, { 'filename' => "${main::spool}/$filename",
'name' => "dunning_invoice_${dunning_id}.pdf" };
$dbh->disconnect() unless $provided_dbh;