- foreach my $row (@{ $rows }) {
-
- $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"};
-
- my $query = qq| UPDATE ar set dunning_id = '$form->{"next_dunning_id_$row"}' WHERE id='$form->{"inv_id_$row"}'|;
- $dbh->do($query) || $form->dberror($query);
- my $query = qq| INSERT into dunning (dunning_id,dunning_level,trans_id,fee,interest,transdate,duedate) VALUES ($form->{"next_dunning_id_$row"},(select dunning_level from dunning_config WHERE id=$form->{"next_dunning_id_$row"}),$form->{"inv_id_$row"},'$form->{"fee_$row"}', '$form->{"interest_$row"}',current_date, |.$dbh->quote($form->{"next_duedate_$row"}) . qq|)|;
- $dbh->do($query) || $form->dberror($query);
- }
-
- 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 FROM ar LEFT JOIN dunning_config ON (dunning_config.id=ar.dunning_id) LEFT JOIN dunning da ON (ar.id=da.trans_id) where ar.id IN $form->{inv_ids}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my ($query, @values);
+
+ 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};
+
+ 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
+ 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 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 { "?" } @invoice_ids) . qq|)|;
+
+ my $sth = prepare_execute_query($form, $dbh, $query, @invoice_ids);