my $agent = qsearchs('agent', { 'agentnum' => $1 } );
die "agent not found" unless $agent;
$agentname = $agent->agent;
- $where .= ' AND agentnum = '. $agent->agentnum;
+ $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
}
my $gotcust = "
my $mywhere = $where;
if ( $r->taxclass ) {
+
$mywhere .= " AND taxclass = ? ";
push @param, 'taxclass';
$regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass
if $cgi->param('show_taxclasses');
+
+ } else {
+
+ my $same_query = "SELECT COUNT(*) FROM cust_main_county WHERE country = ?";
+ my @same_param = ( 'country' );
+ foreach my $opt_field (qw( state county )) {
+ if ( $r->$opt_field() ) {
+ $same_query .= " AND $opt_field = ?";
+ push @same_param, $opt_field;
+ } else {
+ $same_query .= " AND $opt_field IS NULL";
+ }
+ }
+
+ my $num_same_region = scalar_sql( $r, \@same_param, $same_query );
+
+ if ( $num_same_region > 1 ) {
+
+ $mywhere .= " AND taxclass IS NULL";
+
+ }
+
}
my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' ";
foreach my $r (
qsearch( 'cust_main_county',
{},
- 'DISTINCT ON (country, state, county, taxname) *',
+ "DISTINCT
+ country,
+ state,
+ county,
+ CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname,".
+
+ #a little bit unsure of this part... test?
+ #ah, it looks like it winds up being irrelevant as ->{'tax'}
+ # from $regions is not displayed when show_taxclasses is on
+ ( $cgi->param('show_taxclasses')
+ ? " CASE WHEN taxclass IS NULL THEN '' ELSE taxclass END "
+ : " '' "
+ )." AS taxclass"
+ ,
$gotcust
)
) {
$r->taxname
? 'AND itemdesc = '. dbh->quote($r->taxname)
: "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
- my $x = scalar_sql($r, \@taxparam,
- "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ".
- "AND pkgnum = 0 $named_tax",
- );
+
+ my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ".
+ " $taxwhere AND pkgnum = 0 $named_tax";
+
+ my $x = scalar_sql($r, \@taxparam, $sql );
$tax += $x;
$regions{$label}->{'tax'} += $x;