1 <% include('/elements/header.html', $title) %>
2 <% include('/elements/table-grid.html') %>
5 <TH CLASS="grid" BGCOLOR="#cccccc"><% $_ %></TH>
9 % foreach my $row (@rows) {
12 <TD CLASS="grid" STYLE="border: 1px solid #cccccc" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><% $_ %></TD>
19 <TD CLASS="grid" STYLE="border: 1px solid #cccccc" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><B><% $_ %></B></TD>
25 my $curuser = $FS::CurrentUser::CurrentUser;
28 unless $curuser->access_right('Summarize packages');
30 my $money_char = FS::Conf->new()->config('money_char') || '$';
34 my $title = 'Suspension/Unsuspension Report';
35 my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
38 ($cgi->param('beginning') || 'beginning').' - '.
39 ($cgi->param('ending') || 'present').')';
43 my $begin_sql = $begin ? "AND h2.history_date > $begin" : '';
44 my $end_sql = $end ? "AND h2.history_date < $end" : '';
46 my $h_sql = # self-join FTW!
47 "SELECT h1.pkgpart, count(h1.pkgnum) as pkgcount
48 FROM h_cust_pkg AS h1 INNER JOIN h_cust_pkg AS h2 ON (h1.pkgnum = h2.pkgnum)
49 WHERE h1.history_action = 'replace_old' AND h2.history_action = 'replace_new'
50 AND h2.historynum - h1.historynum = 1
52 # This assumes replace_old and replace_new records get consecutive
53 # numbers. That's true in every case I've seen but is not actually
54 # enforced anywhere. If this is a problem we can match them up
55 # line by line but that's cumbersome.
58 '(h1.susp is null OR h1.susp = 0) AND (h2.susp is not null AND h2.susp != 0)',
59 '(h1.susp is not null AND h1.susp != 0) AND (h2.susp is null OR h2.susp = 0)',
63 foreach my $cond (@conds) {
64 my $sql = "$h_sql AND $cond GROUP BY h1.pkgpart";
65 my $sth = dbh->prepare($sql) or die dbh->errstr;
66 $sth->execute() or die $sth->errstr;
67 push @results, { map { @$_ } @{ $sth->fetchall_arrayref() } };
71 push @pay_cond, "cust_bill_pay._date < $end" if $end;
72 push @pay_cond, "cust_bill_pay._date > $begin" if $begin;
75 $extra_sql = 'WHERE '. join(' AND ', @pay_cond) if @pay_cond;
76 $extra_sql .= ( $extra_sql ? ' AND ' : ' WHERE ' ).
77 $curuser->agentnums_sql( 'table' => 'cust_main' );
80 map { $_->pkgpart => $_->total_pay }
82 'table' => 'cust_pkg',
83 'select' => 'pkgpart, sum(cust_bill_pay_pkg.amount) AS total_pay',
84 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )
85 INNER JOIN cust_bill_pkg USING (pkgnum)
86 INNER JOIN cust_bill_pay_pkg USING (billpkgnum)
87 INNER JOIN cust_bill_pay USING (billpaynum)',
88 'extra_sql' => "$extra_sql GROUP BY pkgpart",
91 my @head = ('Package', 'Suspended', 'Unsuspended', 'Payments');
93 my @totals = map {0} @head;
96 my $pkg_agentnums_sql = $curuser->agentnums_sql(
98 'table' => 'part_pkg',
101 my $extra_sql = " WHERE $pkg_agentnums_sql";
103 foreach my $part_pkg (qsearch({ 'table' => 'part_pkg',
105 'extra_sql' => $extra_sql,
110 next if !$part_pkg->freq; # exclude one-time packages
111 my $pkgpart = $part_pkg->pkgpart;
114 $results[0]->{$pkgpart} || 0,
115 $results[1]->{$pkgpart} || 0,
116 sprintf("%.02f",$pkg_payments->{$pkgpart});
118 $totals[$_] += $row[$_] foreach (1..3);
119 $row[3] = $money_char.$row[3];
123 $totals[3] = $money_char.$totals[3];