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" ALIGN="right" BGCOLOR="<% $r % 2 ? '#ffffff' : '#eeeeee' %>"><% $_ %></TD>
19 <TD CLASS="grid" 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 $title = 'Package Summary Report';
31 my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
34 $cgi->param('beginning').' - '.$cgi->param('ending').')';
37 my @h_sql = FS::h_cust_pkg->sql_h_search($end);
39 my ($end_sql, $addl_from) = @h_sql[1,3];
40 $end_sql =~ s/ORDER BY.*//; # breaks aggregate queries
42 my $begin_sql = $end_sql;
43 $begin_sql =~ s/$end/$begin/g;
45 my $active_sql = FS::cust_pkg->active_sql;
46 my $suspended_sql = FS::cust_pkg->suspended_sql;
48 # SQL WHERE clauses for each column of the table.
49 " $begin_sql AND ($active_sql OR $suspended_sql)",
51 " $end_sql AND ($active_sql OR $suspended_sql)",
52 " $end_sql AND $active_sql",
53 " $end_sql AND $suspended_sql",
56 $_ =~ s/\bcust_pkg/maintable/g foreach @conds;
58 my @head = ('Package', 'Before Period', 'Sales', 'Total', 'Active', 'Suspended');
60 my @totals = ('Total', 0, 0, 0, 0, 0);
67 my $agentnums_sql = $curuser->agentnums_sql(
69 'table' => 'part_pkg',
72 my $extra_sql = " WHERE $agentnums_sql";
74 foreach my $part_pkg (qsearch({ 'table' => 'part_pkg',
76 'extra_sql' => $extra_sql,
81 next if !$part_pkg->freq; # exclude one-time packages
82 push @row, $part_pkg->pkg;
84 foreach my $cond (@conds) {
86 my $result = qsearchs({
87 'table' => 'h_cust_pkg',
88 'addl_from' => $addl_from.
89 ' LEFT JOIN cust_main USING ( custnum )',
92 'select' => 'count(*)',
93 'extra_sql' => 'WHERE pkgpart = '.$part_pkg->pkgpart.$cond.
94 ' AND '. $curuser->agentnums_sql(
95 'table' => 'cust_main',
98 $row[$i] = $result->getfield('count');
99 $totals[$i] += $row[$i];
103 $row[2] = $row[3]-$row[1];
104 $totals[2] += $row[2];