-foreach my $part_pkg (qsearch({ 'table' => 'part_pkg',
- 'hashref' => {},
- 'extra_sql' => $extra_sql,
- })
- )
-{
- my @row = ();
- next if !$part_pkg->freq; # exclude one-time packages
- push @row, $part_pkg->pkg;
- my $i=1;
- foreach my $cond (@conds) {
- if($cond) {
- my $result = qsearchs({
- 'table' => 'h_cust_pkg',
- 'addl_from' => $addl_from.
- ' LEFT JOIN cust_main USING ( custnum )',
-
- 'hashref' => {},
- 'select' => 'count(*)',
- 'extra_sql' => 'WHERE pkgpart = '.$part_pkg->pkgpart.$cond.
- ' AND '. $curuser->agentnums_sql(
- 'table' => 'cust_main',
- ),
- });
- $row[$i] = $result->getfield('count');
- $totals[$i] += $row[$i];
- }
- $i++;
- }
- $row[2] = $row[3]-$row[1];
- $totals[2] += $row[2];
- push @rows, \@row;
+my $active_sql = 'setup IS NOT NULL AND susp IS NULL AND cancel IS NULL';
+my $suspended_sql = 'setup IS NOT NULL AND susp IS NOT NULL AND cancel IS NULL';
+my $active_or_suspended_sql = 'setup IS NOT NULL AND cancel IS NULL';
+my %conds;
+
+$conds{'before'} = { 'date' => $begin, 'status' => 'active,suspended' };
+$conds{'after'} = { 'date' => $end, 'status' => 'active,suspended' };
+$conds{'active'} = { 'date' => $end, 'status' => 'active' };
+$conds{'suspended'} = { 'date' => $end, 'status' => 'suspended' };
+
+my @select;
+my $totals = FS::part_pkg->new({pkg => 'Total'});
+foreach my $column (keys %conds) {
+ my $h_search = FS::h_cust_pkg->search($conds{$column});
+ my $count_query = $h_search->{count_query};
+
+ # push a select expression for the total packages with pkgpart=main.pkgpart
+ # (have to quote $column, otherwise mysql thinks before/after are keywords)
+ push @select, "($count_query AND h_cust_pkg.pkgpart = main.pkgpart) AS \"$column\"";
+
+ # and query the total packages with pkgpart=any of the main.pkgparts
+ my $total = FS::Record->scalar_sql($count_query .
+ " AND h_cust_pkg.pkgpart IN(SELECT pkgpart FROM part_pkg AS main WHERE $extra_sql)"
+ );
+ $totals->set($column => $total);
+}
+
+my $query = {
+ 'table' => 'part_pkg',
+ 'addl_from' => 'AS main',
+ 'select' => join(', ', 'main.*', @select),
+ 'extra_sql' => "WHERE $extra_sql",
+};
+
+my $count_query = "SELECT COUNT(*) FROM part_pkg AS main WHERE $extra_sql";
+
+my $baselink = "h_cust_pkg.html?";
+if ( $cgi->param('classnum') =~ /^\d*$/ ) {
+ $baselink .= "classnum=".$cgi->param('classnum').';';
+}
+my @links = ( #arguments to h_cust_pkg.html, except for pkgpart
+ '',
+ '',
+ [ $baselink . "status=active,suspended;date=$begin;pkgpart=", 'pkgpart' ],
+ '',
+ [ $baselink . "status=active,suspended;date=$end;pkgpart=", 'pkgpart' ],
+ [ $baselink . "status=active;date=$end;pkgpart=", 'pkgpart' ],
+ [ $baselink . "status=suspended;date=$end;pkgpart=", 'pkgpart' ],
+);
+
+my @head = ('#',
+ 'Package',
+ 'Before Period',
+ 'Sales',
+ 'Total',
+ 'Active',
+ 'Suspended');
+
+my @fields = (
+ 'pkgpart',
+ 'pkg',
+ 'before',
+ sub { $_[0]->after - $_[0]->before },
+ 'after',
+ 'active',
+ 'suspended',
+ );
+
+if ( !$begin ) {
+ # remove the irrelevant 'before' column
+ splice(@$_,2,1) foreach \@head, \@fields, \@links;