}
-my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 );
-my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0 );
+my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0 );
+my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0, 0 );
my $out = 'Out of taxable region(s)';
my %regions = ();
-foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) {
+foreach my $r ( qsearch({ 'table' => 'cust_main_county',
+ 'extra_sql' => $gotcust,
+ })
+ )
+{
#warn $r->county. ' '. $r->state. ' '. $r->country. "\n";
my $label = getlabel($r);
$regions{$label}->{'label'} = $label;
- $regions{$label}->{'url_param'} = join(';', map "$_=".$r->$_(), qw( county state country ) );
+ $regions{$label}->{'url_param'} =
+ join(';', map "$_=".uri_escape($r->$_()),
+ qw( county state country taxname )
+ );
my @param = @base_param;
my $mywhere = $where;
$mywhere .= " AND taxclass = ? ";
push @param, 'taxclass';
- $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass
+ $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass)
if $cgi->param('show_taxclasses');
} else {
- my $same_query = 'SELECT taxclass FROM cust_main_county '.
- ' WHERE taxnum != ? AND country = ?';
- my @same_param = ( 'taxnum', '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 @taxclasses = list_sql( $r, \@same_param, $same_query );
-
- if ( scalar(@taxclasses) ) {
- $mywhere .= ' AND '. join(' AND ', map ' taxclass != ? ', @taxclasses );
- push @param, map \$_, @taxclasses;
- }
-
+ $regions{$label}->{'url_param'} .= ';taxclassNULL=1'
+ if $cgi->param('show_taxclasses');
+
+ my $same_sql = $r->sql_taxclass_sameregion;
+ $mywhere .= " AND $same_sql" if $same_sql;
+
}
my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' ";
}
+my $distinct = "country, state, county,
+ CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname";
+my $taxclass_distinct =
+ #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";
+
+my %qsearch = (
+ 'select' => "DISTINCT $distinct, $taxclass_distinct",
+ 'table' => 'cust_main_county',
+ 'hashref' => {},
+ 'extra_sql' => $gotcust,
+);
+
my $taxwhere = "$from_join_cust $where AND payby != 'COMP' ";
my @taxparam = @base_param;
-my %base_regions = ();
-#foreach my $label ( keys %regions ) {
-foreach my $r (
- qsearch( 'cust_main_county',
- {},
- "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
- )
-) {
-
- #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n";
-
- my $label = getlabel($r);
- #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' ";
- #my @param = @base_param;
+#should i be a cust_main_county method or something
+#need to pass in $taxwhere & @taxparam???
+my $_taxamount_sub = sub {
+ my $r = shift;
#match itemdesc if necessary!
my $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;
+ scalar_sql($r, \@taxparam, $sql );
+};
+
+#foreach my $label ( keys %regions ) {
+foreach my $r ( qsearch(\%qsearch) ) {
+
+ #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n";
+
+ my $label = getlabel($r);
+
+ #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' ";
+ #my @param = @base_param;
+
+ my $x = &{$_taxamount_sub}($r);
+
+ $tax += $x unless $cgi->param('show_taxclasses');
$regions{$label}->{'tax'} += $x;
- if ( $cgi->param('show_taxclasses') ) {
+}
+
+my %base_regions = ();
+if ( $cgi->param('show_taxclasses') ) {
+
+ $qsearch{'select'} = "DISTINCT $distinct";
+ foreach my $r ( qsearch(\%qsearch) ) {
+
+ my $x = &{$_taxamount_sub}($r);
+
my $base_label = getlabel($r, 'no_taxclass'=>1 );
$base_regions{$base_label}->{'label'} = $base_label;
+
$base_regions{$base_label}->{'url_param'} =
- join(';', map "$_=".$r->$_(), qw( county state country ) );
+ join(';', map "$_=". uri_escape($r->$_()),
+ qw( county state country taxname )
+ );
+
$base_regions{$base_label}->{'tax'} += $x;
+ $tax += $x;
}
}
+
#ordering
my @regions =
map $regions{$_},
my( $r, $param, $sql ) = @_;
#warn "$sql\n";
my $sth = dbh->prepare($sql) or die dbh->errstr;
- $sth->execute( map { ref($_) ? ${$_} : $r->$_() } @$param )
- or die "Unexpected error executing statement $sql: ". $sth->errstr;
- $sth->fetchrow_arrayref->[0] || 0;
-}
-
-sub list_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;
- map $_->[0], @{ $sth->fetchall_arrayref };
+ $sth->fetchrow_arrayref->[0] || 0;
}
my $dateagentlink = "begin=$beginning;end=$ending";
projects / freeside.git / blobdiff