1 <& elements/search.html,
3 'name' => 'package types',
5 'count_query' => $count_query,
10 'footer_data' => $totals,
14 my $curuser = $FS::CurrentUser::CurrentUser;
17 unless $curuser->access_right('Summarize packages');
19 my $title = 'Package Summary Report';
20 my ($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi);
23 $cgi->param('beginning').' - '.$cgi->param('ending').')';
26 my $agentnums_sql = $curuser->agentnums_sql(
31 my $extra_sql = " freq != '0' AND $agentnums_sql";
33 #tiny bit of false laziness w/cust_pkg.pm::search
34 if ( grep { $_ eq 'classnum' } $cgi->param ) {
35 if ( $cgi->param('classnum') eq '' ) {
36 $extra_sql .= ' AND main.classnum IS NULL';
37 } elsif ( $cgi->param('classnum') =~ /^(\d+)$/ && $1 ne '0' ) {
38 $extra_sql .= " AND main.classnum = $1 ";
42 my $active_sql = 'setup IS NOT NULL AND susp IS NULL AND cancel IS NULL';
43 my $suspended_sql = 'setup IS NOT NULL AND susp IS NOT NULL AND cancel IS NULL';
44 my $active_or_suspended_sql = 'setup IS NOT NULL AND cancel IS NULL';
47 $conds{'before'} = { 'date' => $begin, 'status' => 'active,suspended' };
48 $conds{'after'} = { 'date' => $end, 'status' => 'active,suspended' };
49 $conds{'active'} = { 'date' => $end, 'status' => 'active' };
50 $conds{'suspended'} = { 'date' => $end, 'status' => 'suspended' };
53 my $totals = FS::part_pkg->new({pkg => 'Total'});
54 foreach my $column (keys %conds) {
55 my $h_search = FS::h_cust_pkg->search($conds{$column});
56 my $count_query = $h_search->{count_query};
58 # push a select expression for the total packages with pkgpart=main.pkgpart
59 # (have to quote $column, otherwise mysql thinks before/after are keywords)
60 push @select, "($count_query AND h_cust_pkg.pkgpart = main.pkgpart) AS \"$column\"";
62 # and query the total packages with pkgpart=any of the main.pkgparts
63 my $total = FS::Record->scalar_sql($count_query .
64 " AND h_cust_pkg.pkgpart IN(SELECT pkgpart FROM part_pkg AS main WHERE $extra_sql)"
66 $totals->set($column => $total);
70 'table' => 'part_pkg',
71 'addl_from' => 'AS main',
72 'select' => join(', ', 'main.*', @select),
73 'extra_sql' => "WHERE $extra_sql",
76 my $count_query = "SELECT COUNT(*) FROM part_pkg AS main WHERE $extra_sql";
78 my $baselink = "h_cust_pkg.html?";
79 if ( $cgi->param('classnum') =~ /^\d*$/ ) {
80 $baselink .= "classnum=".$cgi->param('classnum').';';
82 my @links = ( #arguments to h_cust_pkg.html, except for pkgpart
85 [ $baselink . "status=active,suspended;date=$begin;pkgpart=", 'pkgpart' ],
87 [ $baselink . "status=active,suspended;date=$end;pkgpart=", 'pkgpart' ],
88 [ $baselink . "status=active;date=$end;pkgpart=", 'pkgpart' ],
89 [ $baselink . "status=suspended;date=$end;pkgpart=", 'pkgpart' ],
104 sub { $_[0]->after - $_[0]->before },
111 # remove the irrelevant 'before' column
112 splice(@$_,2,1) foreach \@head, \@fields, \@links;