$agentnum ||= $opt{'agentnum'};
- my $usage = cust_bill_pkg_detail(@_);
+ my $total_sql =
+ " SELECT COALESCE( SUM(cust_bill_pkg.setup + cust_bill_pkg.recur), 0 ) ";
- my $total = $self->scalar_sql("
- SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)
- FROM cust_bill_pkg
+ $total_sql .=
+ " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
+ if $opt{average_per_cust_pkg};
+
+ $total_sql .=
+ " FROM cust_bill_pkg
LEFT JOIN cust_bill USING ( invnum )
LEFT JOIN cust_main USING ( custnum )
LEFT JOIN cust_pkg USING ( pkgnum )
LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
WHERE pkgnum != 0
AND $where
- AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
- );
+ AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
if ($opt{use_usage} && $opt{use_usage} eq 'recurring') {
+ my $total = $self->scalar_sql($total_sql);
+ my $usage = cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt
return $total-$usage;
} elsif ($opt{use_usage} && $opt{use_usage} eq 'usage') {
- return $usage;
+ return cust_bill_pkg_detail(@_); #$speriod, $eperiod, $agentnum, %opt
} else {
- return $total;
+ return $self->scalar_sql($total_sql);
}
}
my $where = join( ' AND ', @where );
- $self->scalar_sql("
- SELECT SUM(amount)
- FROM cust_bill_pkg_detail
+ my $total_sql = " SELECT SUM(amount) ";
+
+ $total_sql .=
+ " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
+ if $opt{average_per_cust_pkg};
+
+ $total_sql .=
+ " FROM cust_bill_pkg_detail
LEFT JOIN cust_bill_pkg USING ( billpkgnum )
LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
LEFT JOIN cust_main USING ( custnum )
LEFT JOIN part_pkg USING ( pkgpart )
LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
WHERE $where
- AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
- );
+ AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
+
+ $self->scalar_sql($total_sql);
}
}
#eslaf
-my $use_override = 0;
-$use_override = 1 if ( $cgi->param('use_override') );
-
-my $use_usage = 0;
-$use_usage = 1 if ( $cgi->param('use_usage') );
+my $use_override = $cgi->param('use_override') ? 1 : 0;
+my $use_usage = $cgi->param('use_usage') ? 1 : 0;
+my $average_per_cust_pkg = $cgi->param('average_per_cust_pkg') ? 1 : 0;
my $hue = 0;
#my $hue_increment = 170;
my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0;
my $row_agentnum = $agent->agentnum;
- push @params, [ 'classnum' => $row_classnum,
- 'agentnum' => $row_agentnum,
- 'use_override' => $use_override,
- 'use_usage' => $component,
+ push @params, [ 'classnum' => $row_classnum,
+ 'agentnum' => $row_agentnum,
+ 'use_override' => $use_override,
+ 'use_usage' => $component,
+ 'average_per_cust_pkg' => $average_per_cust_pkg,
];
push @links, "$link;agentnum=$row_agentnum;classnum=$row_classnum;".