+ my $nottax = 'pkgnum != 0';
+
+ my $a = scalar_sql($r, \@param,
+ "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"
+ );
+ $total += $a;
+ $regions{$label}->{'total'} += $a;
+
+ foreach my $e ( grep { $r->get($_.'tax') =~ /^Y/i }
+ qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) {
+ my $x = scalar_sql($r, \@param,
+ "SELECT SUM($e) $fromwhere AND $nottax"
+ );
+ $exempt += $x;
+ $regions{$label}->{'exempt'} += $x;
+ }
+
+ foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i }
+ qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) {
+ my $t = scalar_sql($r, \@param,
+ "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )"
+ );
+ $taxable += $t;
+ $regions{$label}->{'taxable'} += $t;
+
+ my $x = scalar_sql($r, \@param,
+ "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'"
+ );
+ $exempt += $x;
+ $regions{$label}->{'exempt'} += $x;
+ }
+
+ if ( defined($regions{$label}->{'rate'})
+ && $regions{$label}->{'rate'} != $r->tax.'%' ) {
+ $regions{$label}->{'rate'} = 'variable';
+ } else {
+ $regions{$label}->{'rate'} = $r->tax.'%';
+ }
+
+ #match itemdesc if necessary!
+ my $named_tax = $r->taxname ? 'AND itemdesc = '. dbh->quote($r->taxname) : '';
+ my $x = scalar_sql($r, \@taxparam,
+ "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ".
+ "AND pkgnum = 0 $named_tax",
+ );
+ $tax += $x;
+ $regions{$label}->{'tax'} += $x;
+
+ $regions{$label}->{'label'} = $label;
+
+}
+
+#ordering
+my @regions = map $regions{$_},
+ sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) }
+ keys %regions;
+
+push @regions, {
+ 'label' => 'Total',
+ 'total' => $total,
+ 'exempt' => $exempt,
+ 'taxable' => $taxable,
+ 'rate' => '',
+ 'tax' => $tax,
+};
+
+#--
+
+#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up
+#to FS::Report or FS::Record or who the fuck knows where)
+sub scalar_sql {
+ my( $r, $param, $sql ) = @_;
+ #warn "$sql\n";
+ my $sth = dbh->prepare($sql) or die dbh->errstr;
+ $sth->execute( map $r->$_(), @$param )
+ or die "Unexpected error executing statement $sql: ". $sth->errstr;
+ $sth->fetchrow_arrayref->[0] || 0;
+}