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
|
<% include('/elements/header.html', $title) %>
<% include('/elements/table-grid.html') %>
<TR>
% foreach (@head) {
<TH CLASS="grid" BGCOLOR="#cccccc"><% $_ %></TH>
% }
</TR>
% my $r=0;
% foreach my $row (@rows) {
<TR>
% foreach (@$row) {
<TD CLASS="grid" STYLE="border: 1px solid #cccccc" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><% $_ %></TD>
% }
</TR>
% $r++;
% }
<TR>
% foreach (@totals) {
<TD CLASS="grid" STYLE="border: 1px solid #cccccc" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><B><% $_ %></B></TD>
% }
</TR>
</TABLE>
<%init>
my $curuser = $FS::CurrentUser::CurrentUser;
die "access denied"
unless $curuser->access_right('Summarize packages');
my $money_char = FS::Conf->new()->config('money_char') || '$';
$FS::Record::DEBUG=0;
my $title = 'Suspension/Unsuspension Report';
my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
if($begin > 0) {
$title = "$title (".
($cgi->param('beginning') || 'beginning').' - '.
($cgi->param('ending') || 'present').')';
}
my $begin_sql = $begin ? "AND h2.history_date > $begin" : '';
my $end_sql = $end ? "AND h2.history_date < $end" : '';
my $h_sql = # self-join FTW!
"SELECT h1.pkgpart, count(h1.pkgnum) as pkgcount
FROM h_cust_pkg AS h1 INNER JOIN h_cust_pkg AS h2 ON (h1.pkgnum = h2.pkgnum)
WHERE h1.history_action = 'replace_old' AND h2.history_action = 'replace_new'
AND h2.historynum - h1.historynum = 1
$begin_sql $end_sql";
# This assumes replace_old and replace_new records get consecutive
# numbers. That's true in every case I've seen but is not actually
# enforced anywhere. If this is a problem we can match them up
# line by line but that's cumbersome.
my @conds = (
'(h1.susp is null OR h1.susp = 0) AND (h2.susp is not null AND h2.susp != 0)',
'(h1.susp is not null AND h1.susp != 0) AND (h2.susp is null OR h2.susp = 0)',
);
my @results;
foreach my $cond (@conds) {
my $sql = "$h_sql AND $cond GROUP BY h1.pkgpart";
my $sth = dbh->prepare($sql) or die dbh->errstr;
$sth->execute() or die $sth->errstr;
push @results, { map { @$_ } @{ $sth->fetchall_arrayref() } };
}
my @pay_cond;
push @pay_cond, "cust_bill_pay._date < $end" if $end;
push @pay_cond, "cust_bill_pay._date > $begin" if $begin;
my $extra_sql = '';
$extra_sql = 'WHERE '. join(' AND ', @pay_cond) if @pay_cond;
$extra_sql .= ( $extra_sql ? ' AND ' : ' WHERE ' ).
$curuser->agentnums_sql( 'table' => 'cust_main' );
my $pkg_payments = {
map { $_->pkgpart => $_->total_pay }
qsearch({
'table' => 'cust_pkg',
'select' => 'pkgpart, sum(cust_bill_pay_pkg.amount) AS total_pay',
'addl_from' => 'LEFT JOIN cust_main USING ( custnum )
INNER JOIN cust_bill_pkg USING (pkgnum)
INNER JOIN cust_bill_pay_pkg USING (billpkgnum)
INNER JOIN cust_bill_pay USING (billpaynum)',
'extra_sql' => "$extra_sql GROUP BY pkgpart",
}) };
my @head = ('Package', 'Suspended', 'Unsuspended', 'Payments');
my @rows = ();
my @totals = map {0} @head;
$totals[0] = 'Total';
my $pkg_agentnums_sql = $curuser->agentnums_sql(
'null' => 1,
'table' => 'part_pkg',
);
my $extra_sql = " WHERE $pkg_agentnums_sql";
foreach my $part_pkg (qsearch({ 'table' => 'part_pkg',
'hashref' => {},
'extra_sql' => $extra_sql,
})
)
{
my @row = ();
next if !$part_pkg->freq; # exclude one-time packages
my $pkgpart = $part_pkg->pkgpart;
push @row,
$part_pkg->pkg,
$results[0]->{$pkgpart} || 0,
$results[1]->{$pkgpart} || 0,
sprintf("%.02f",$pkg_payments->{$pkgpart});
$totals[$_] += $row[$_] foreach (1..3);
$row[3] = $money_char.$row[3];
push @rows, \@row;
}
$totals[3] = $money_char.$totals[3];
</%init>
|
