blob: d789c6c2eac1bd89ab683209bf8ae41f415b2b81 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
#!/usr/bin/perl -w
use strict;
use vars qw( $DEBUG );
use FS::UID qw(adminsuidsetup);
use FS::Record qw(qsearch qsearchs);
use FS::cust_main;
use DBI;
$DEBUG = 1;
my $user = shift or die &usage;
my $dbh = adminsuidsetup $user;
my $unapplied_payments_sql = <<EOF;
SELECT custnum FROM cust_pay WHERE paid >
( ( SELECT coalesce(sum(amount),0) FROM cust_bill_pay
WHERE cust_pay.paynum = cust_bill_pay.paynum )
+ ( SELECT coalesce(sum(amount),0) FROM cust_pay_refund
WHERE cust_pay.paynum = cust_pay_refund.paynum)
)
EOF
my $unapplied_credits_sql = <<EOF;
SELECT custnum FROM cust_credit WHERE cust_credit.amount >
( ( SELECT coalesce(sum(cust_credit_bill.amount),0) FROM cust_credit_bill
WHERE cust_credit.crednum = cust_credit_bill.crednum )
+ ( SELECT coalesce(sum(cust_Credit_refund.amount),0) FROM cust_credit_refund
WHERE cust_credit.crednum = cust_credit_refund.crednum)
)
EOF
my %custnum = ();
my $sth = $dbh->prepare($unapplied_payments_sql) or die $dbh->errstr;
$sth->execute or die "unapplied payment search failed: ". $sth->errstr;
map { $custnum{$_->[0]} = 1 } @{ $sth->fetchall_arrayref };
$sth = $dbh->prepare($unapplied_credits_sql) or die $dbh->errstr;
$sth->execute or die "unapplied credit search failed: ". $sth->errstr;
map { $custnum{$_->[0]} = 1 } @{ $sth->fetchall_arrayref };
foreach my $custnum ( keys %custnum ) {
warn "processing customer $custnum\n" if $DEBUG;
my $cust_main = qsearchs('cust_main', { 'custnum' => $custnum } )
or die "customer $custnum no longer exists!\n";
my $error = $cust_main->apply_payments_and_credits;
die $error if $error;
}
sub usage {
die "Usage:\n\n freeside-apply_payments_and_credits user\n";
}
=head1 NAME
freeside-apply_payments_and_credits - Command line interface to apply payments and credits to invoice
=head1 SYNOPSIS
freeside-apply_payments_and_credits username
=head1 DESCRIPTION
Finds unapplied payment and credit amounts and applies them to any outstanding
uncovered invoice amounts.
B<username> is a username added by freeside-adduser.
=cut
|
