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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
<!-- mason kludge -->
<%
my $charged = <<END;
sum( charged
- coalesce(
( select sum(amount) from cust_bill_pay
where cust_bill.invnum = cust_bill_pay.invnum )
,0
)
- coalesce(
( select sum(amount) from cust_credit_bill
where cust_bill.invnum = cust_credit_bill.invnum )
,0
)
)
END
my $owed_cols = <<END;
coalesce(
( select $charged from cust_bill
where cust_bill._date > extract(epoch from now())-2592000
and cust_main.custnum = cust_bill.custnum
)
,0
) as owed_0_30,
coalesce(
( select $charged from cust_bill
where cust_bill._date > extract(epoch from now())-5184000
and cust_bill._date <= extract(epoch from now())-2592000
and cust_main.custnum = cust_bill.custnum
)
,0
) as owed_30_60,
coalesce(
( select $charged from cust_bill
where cust_bill._date > extract(epoch from now())-7776000
and cust_bill._date <= extract(epoch from now())-5184000
and cust_main.custnum = cust_bill.custnum
)
,0
) as owed_60_90,
coalesce(
( select $charged from cust_bill
where cust_bill._date <= extract(epoch from now())-7776000
and cust_main.custnum = cust_bill.custnum
)
,0
) as owed_90_plus,
coalesce(
( select $charged from cust_bill
where cust_main.custnum = cust_bill.custnum
)
,0
) as owed_total
END
my $recurring = <<END;
0 < ( select freq from part_pkg
where cust_pkg.pkgpart = part_pkg.pkgpart )
END
my $packages_cols = <<END;
( select count(*) from cust_pkg
where cust_main.custnum = cust_pkg.custnum
and $recurring
and ( cancel = 0 or cancel is null )
) as uncancelled_pkgs,
( select count(*) from cust_pkg
where cust_main.custnum = cust_pkg.custnum
and $recurring
and ( cancel = 0 or cancel is null )
and ( susp = 0 or susp is null )
) as active_pkgs
END
my $sql = <<END;
select *, $owed_cols, $packages_cols from cust_main
where 0 <
coalesce(
( select $charged from cust_bill
where cust_main.custnum = cust_bill.custnum
)
,0
)
order by coalesce(lower(company), ''), lower(last)
END
my $total_sql = "select $owed_cols";
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute or die $sth->errstr;
my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
$total_sth->execute or die $total_sth->errstr;
%>
<%= header('Accounts Receivable Aging Summary', menubar( 'Main Menu'=>$p, ) ) %>
<%= table() %>
<TR>
<TH>Customer</TH>
<TH>Status</TH>
<TH>0-30</TH>
<TH>30-60</TH>
<TH>60-90</TH>
<TH>90+</TH>
<TH>Total</TH>
</TR>
<% while ( my $row = $sth->fetchrow_hashref() ) {
my $status = 'Cancelled';
my $statuscol = 'FF0000';
if ( $row->{uncancelled_pkgs} ) {
$status = 'Suspended';
$statuscol = 'FF9900';
if ( $row->{active_pkgs} ) {
$status = 'Active';
$statuscol = '00CC00';
}
}
%>
<TR>
<TD><A HREF="<%= $p %>view/cust_main.cgi?<%= $row->{'custnum'} %>"><%= $row->{'custnum'} %>:
<%= $row->{'company'} ? $row->{'company'}. ' (' : '' %><%= $row->{'last'}. ', '. $row->{'first'} %><%= $row->{'company'} ? ')' : '' %></A>
</TD>
<TD><B><FONT SIZE=-1 COLOR="#<%= $statuscol %>"><%= $status %></FONT></B></TD>
<TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD>
<TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD>
<TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD>
<TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD>
<TD ALIGN="right"><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></TD>
</TR>
<% } %>
<% my $row = $total_sth->fetchrow_hashref(); %>
<TR>
<TD COLSPAN=6> </TD>
</TR>
<TR>
<TD COLSPAN=2><I>Total</I></TD>
<TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD>
<TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD>
<TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD>
<TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD>
<TD ALIGN="right"><I><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></I></TD>
</TR>
</TABLE>
</BODY>
</HTML>
|
