+ $query = qq|UPDATE defaults SET dunning_ar_amount_fee = ?, dunning_ar_amount_interest = ?, dunning_ar = ?|;
+ @values = (conv_i($form->{AR_amount_fee}), conv_i($form->{AR_amount_interest}), conv_i($form->{AR}));
+ do_query($form, $dbh, $query, @values);
+
+ $dbh->commit();
+ $dbh->disconnect();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub create_invoice_for_fees {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form, $dbh, $dunning_id) = @_;
+
+ my ($query, @values, $sth, $ref);
+
+ $query = qq|SELECT dcfg.create_invoices_for_fees
+ FROM dunning d
+ LEFT JOIN dunning_config dcfg ON (d.dunning_config_id = dcfg.id)
+ WHERE d.dunning_id = ?|;
+ my ($create_invoices_for_fees) = selectrow_query($form, $dbh, $query, $dunning_id);
+
+ if (!$create_invoices_for_fees) {
+ $main::lxdebug->leave_sub();
+ return;
+ }
+
+ $query = qq|SELECT dunning_ar_amount_fee, dunning_ar_amount_interest, dunning_ar FROM defaults|;
+ ($form->{AR_amount_fee}, $form->{AR_amount_interest}, $form->{AR}) = selectrow_query($form, $dbh, $query);
+
+ $query =
+ qq|SELECT
+ fee,
+ COALESCE((
+ SELECT MAX(d_fee.fee)
+ FROM dunning d_fee
+ WHERE (d_fee.trans_id = d.trans_id)
+ AND (d_fee.dunning_id <> ?)
+ AND NOT (d_fee.fee_interest_ar_id ISNULL)
+ ), 0)
+ AS max_previous_fee,
+ interest,
+ COALESCE((
+ SELECT MAX(d_interest.interest)
+ FROM dunning d_interest
+ WHERE (d_interest.trans_id = d.trans_id)
+ AND (d_interest.dunning_id <> ?)
+ AND NOT (d_interest.fee_interest_ar_id ISNULL)
+ ), 0)
+ AS max_previous_interest
+ FROM dunning d
+ WHERE dunning_id = ?|;
+ @values = ($dunning_id, $dunning_id, $dunning_id);
+ $sth = prepare_execute_query($form, $dbh, $query, @values);
+
+ my ($fee_remaining, $interest_remaining) = (0, 0);
+ my ($fee_total, $interest_total) = (0, 0);
+
+ while (my $ref = $sth->fetchrow_hashref()) {
+ $fee_remaining += $form->round_amount($ref->{fee}, 2);
+ $fee_remaining -= $form->round_amount($ref->{max_previous_fee}, 2);
+ $fee_total += $form->round_amount($ref->{fee}, 2);
+ $interest_remaining += $form->round_amount($ref->{interest}, 2);
+ $interest_remaining -= $form->round_amount($ref->{max_previous_interest}, 2);
+ $interest_total += $form->round_amount($ref->{interest}, 2);
+ }
+
+ $sth->finish();
+
+ my $amount = $fee_remaining + $interest_remaining;
+
+ if (!$amount) {
+ $main::lxdebug->leave_sub();
+ return;
+ }
+
+ my ($ar_id) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|);
+ my $curr = $form->get_default_currency($myconfig);
+
+ $query =
+ qq|INSERT INTO ar (id, invnumber, transdate, gldate, customer_id,
+ taxincluded, amount, netamount, paid, duedate,
+ invoice, curr, notes,
+ employee_id)
+ VALUES (
+ ?, -- id
+ ?, -- invnumber
+ current_date, -- transdate
+ current_date, -- gldate
+ -- customer_id:
+ (SELECT ar.customer_id
+ FROM dunning dn
+ LEFT JOIN ar ON (dn.trans_id = ar.id)
+ WHERE dn.dunning_id = ?
+ LIMIT 1),
+ 'f', -- taxincluded
+ ?, -- amount
+ ?, -- netamount
+ 0, -- paid
+ -- duedate:
+ (SELECT duedate FROM dunning WHERE dunning_id = ? LIMIT 1),
+ 'f', -- invoice
+ ?, -- curr
+ ?, -- notes
+ -- employee_id:
+ (SELECT id FROM employee WHERE login = ?)
+ )|;
+ @values = ($ar_id, # id
+ $form->update_defaults($myconfig, 'invnumber', $dbh), # invnumber
+ $dunning_id, # customer_id
+ $amount,
+ $amount,
+ $dunning_id, # duedate
+ $curr, # default currency
+ sprintf($main::locale->text('Automatically created invoice for fee and interest for dunning %s'), $dunning_id), # notes
+ $form->{login}); # employee_id
+ do_query($form, $dbh, $query, @values);
+
+ $query =
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, taxkey)
+ VALUES (?, ?, ?, current_date, current_date, 0)|;
+ $sth = prepare_query($form, $dbh, $query);
+
+ @values = ($ar_id, conv_i($form->{AR_amount_fee}), $fee_remaining);
+ do_statement($form, $sth, $query, @values);
+
+ if ($interest_remaining) {
+ @values = ($ar_id, conv_i($form->{AR_amount_interest}), $interest_remaining);
+ do_statement($form, $sth, $query, @values);
+ }
+
+ @values = ($ar_id, conv_i($form->{AR}), -1 * $amount);
+ do_statement($form, $sth, $query, @values);
+
+ $sth->finish();
+
+ $query = qq|UPDATE dunning SET fee_interest_ar_id = ? WHERE dunning_id = ?|;
+ do_query($form, $dbh, $query, $ar_id, $dunning_id);