-my $active_sql = FS::cust_pkg->active_sql;
-my $suspended_sql = FS::cust_pkg->suspended_sql;
-my @conds = (
- # SQL WHERE clauses for each column of the table.
- " $begin_sql AND ($active_sql OR $suspended_sql)",
- '',
- " $end_sql AND ($active_sql OR $suspended_sql)",
- " $end_sql AND $active_sql",
- " $end_sql AND $suspended_sql",
+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)"