X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax.cgi;h=6dc9ffcfb586d318f970a7741953c5c8ad25f9e8;hb=c9f959936fe3c12a565c2152e9a967ab88916c64;hp=22e6ab26819b1f75126d0999e452c97b2ef8156c;hpb=ce1fa204e25ae8605bdb4f3dbfdf9bd1bff1a590;p=freeside.git diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 22e6ab268..6dc9ffcfb 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -10,49 +10,79 @@ ) ) %> - + +Download full results
+as Excel spreadsheet + + + <% include('/elements/table-grid.html') %> + + + Sales + + Rate + + Tax owed +% unless ( $cgi->param('show_taxclasses') ) { + Tax invoiced + + Tax credited + + Tax collected +% } + + Total - Sales + Non-taxable - Rate + Non-taxable - Tax owed -% unless ( $cgi->param('show_taxclasses') ) { - Tax invoiced - - Tax credited - - Tax collected -% } + Non-taxable + + Taxable - Total - - Non-taxable
(tax-exempt customer) - - Non-taxable
(tax-exempt package) - - Non-taxable
(monthly exemption) - - Taxable + (tax-exempt customer) + (tax-exempt package) + (monthly exemption) +% foreach my $class (@pkgclasses ) { +% next if @{ $class->{regions} } == 0; +% if ( $class->{classname} ) { + + <% $class->{classname} %> + +% } + % my $bgcolor1 = '#eeeeee'; % my $bgcolor2 = '#ffffff'; % my $bgcolor; -% + +% my @regions = @{ $class->{regions} }; % foreach my $region ( @regions ) { % % my $link = ''; +% if ( $with_pkgclass and length($class->{classnum}) ) { +% $link = ';classnum='.$class->{classnum}; +% } # else we're not breaking down pkg class, or this is the grand total +% % if ( $region->{'label'} eq $out ) { -% $link = ';out=1'; -% } else { -% $link = ';'. $region->{'url_param'} -% if $region->{'url_param'}; +% $link .= ';out=1'; +% } elsif ( $region->{'taxnums'} ) { +% # might be nicer to specify this as country:state:city +% $link .= ';'.join(';', map { "taxnum=$_" } @{ $region->{'taxnums'} }); % } % % if ( $bgcolor eq $bgcolor1 ) { @@ -61,15 +91,12 @@ % $bgcolor = $bgcolor1; % } % -% #my $diff = 0; % my $hicolor = $bgcolor; % unless ( $cgi->param('show_taxclasses') ) { % my $diff = abs( sprintf( '%.2f', $region->{'owed'} ) % - sprintf( '%.2f', $region->{'tax'} ) % ); % if ( $diff > 0.02 ) { -% # $hicolor = $hicolor eq '#eeeeee' ? '#eeee66' : '#ffff99'; -% #} elsif ( $diff ) { % $hicolor = $hicolor eq '#eeeeee' ? '#eeee99' : '#ffffcc'; % } % } @@ -80,20 +107,28 @@ % my $bigmath = ''; % my $bme = ''; +% if ( $region->{'is_total'} ) { + + Total +% } else { <<%$td%>><% $region->{'label'} %> +% } <<%$td%> ALIGN="right"> <% &$money_sprintf( $region->{'total'} ) %> + ><% &$money_sprintf( $region->{'sales'} ) %> +% if ( $region->{'label'} eq $out ) { + <<%$td%> COLSPAN=12> +% } else { #not $out <<%$td%>> - <<%$td%> ALIGN="right"> - <% &$money_sprintf( $region->{'exempt_cust'} ) %> <<%$td%>> - <<%$td%> ALIGN="right"> - <% &$money_sprintf( $region->{'exempt_pkg'} ) %> <<%$td%>> - @@ -112,29 +147,44 @@ <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'owed'} ) %> - -% unless ( $cgi->param('show_taxclasses') ) { -% my $invlink = $region->{'url_param_inv'} +% } # if !$out +% unless ( $cgi->param('show_taxclasses') ) { +% my $invlink = $region->{'url_param_inv'} % ? ';'. $region->{'url_param_inv'} % : $link; +% if ( $region->{'label'} eq $out ) { + <<%$td%> ALIGN="right"> + <% &$money_sprintf_nonzero( $region->{'tax'} ) %> + + <<%$td%>> + <<%$td%> ALIGN="right"> + <% &$money_sprintf_nonzero( $region->{'credit'} ) %> + + <<%$td%> COLSPAN=2> +% } else { #not $out <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'tax'} ) %> <<%$tdh%>> - <<%$tdh%> ALIGN="right"> - <% &$money_sprintf( $region->{'credit'} ) %> <<%$tdh%>> = <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> -% } +% } +% } # show_taxclasses -% } +% } # foreach $region + +%} # foreach $class @@ -152,16 +202,28 @@ % #some false laziness w/above -% $bgcolor1 = '#eeeeee'; -% $bgcolor2 = '#ffffff'; +% foreach my $class (@pkgclasses) { +% if ( $class->{classname} ) { + + <% $class->{classname} %> + +% } + +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; % -% foreach my $region ( @base_regions ) { +% foreach my $region ( @{ $class->{base_regions} } ) { % % my $link = ''; +% if ( $with_pkgclass and length($class->{classnum}) ) { +% $link = ';classnum='.$class->{classnum}; +% } +% % if ( $region->{'label'} eq $out ) { -% $link = ';out=1'; +% $link .= ';out=1'; % } else { -% $link = ';'. $region->{'url_param'} +% $link .= ';'. $region->{'url_param'} % if $region->{'url_param'}; % } % @@ -180,13 +242,25 @@ <<%$td%>><% $region->{'label'} %> +% if ( $region->{'label'} eq $out ) { + <<%$td%> ALIGN="right"> + <% &$money_sprintf_nonzero( $region->{'tax'} ) %> + + <<%$td%>> + <<%$td%> ALIGN="right"> + <% &$money_sprintf_nonzero( $region->{'credit'} ) %> + + <<%$td%> COLSPAN=2> +% } else { #not $out <<%$td%> ALIGN="right"> <% &$money_sprintf( $region->{'tax'} ) %> <<%$td%>> - <<%$tdh%> ALIGN="right"> - <% &$money_sprintf( $region->{'credit'} ) %> <<%$td%>> = @@ -194,36 +268,13 @@ <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> - -% } - -% if ( $bgcolor eq $bgcolor1 ) { -% $bgcolor = $bgcolor2; -% } else { -% $bgcolor = $bgcolor1; -% } -% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor"); - - - <<%$td%>>Total - <<%$td%> ALIGN="right"> - <% &$money_sprintf( $tot_tax ) %> - - <<%$td%>> - - <<%$td%> ALIGN="right"> - <% &$money_sprintf( $tot_credit ) %> - - <<%$td%>> = - <<%$td%> ALIGN="right"> - <% &$money_sprintf( $tot_tax - $tot_credit ) %> - - +% } # if $out +% } #foreach $region +% } #foreach $class -% } +% } # if show_taxclasses <% include('/elements/footer.html') %> @@ -232,26 +283,55 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $DEBUG = $cgi->param('debug') || 0; + my $conf = new FS::Conf; -my $user = getotaker; +my $out = 'Out of taxable region(s)'; + +my %label_opt = ( out => 1 ); #enable 'Out of Taxable Region' label +$label_opt{with_city} = 1 if $cgi->param('show_cities'); +$label_opt{with_district} = 1 if $cgi->param('show_districts'); + +$label_opt{with_taxclass} = 1 if $cgi->param('show_taxclasses'); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); my $join_cust = ' JOIN cust_bill USING ( invnum ) LEFT JOIN cust_main USING ( custnum ) '; + my $join_cust_pkg = $join_cust. ' LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) '; -$join_cust_pkg .= ' LEFT JOIN cust_location USING ( locationnum )' - if $conf->exists('tax-pkg_address'); my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; -my $where = "WHERE _date >= $beginning AND _date <= $ending "; +my $with_pkgclass = $cgi->param('show_pkgclasses'); + +# either or both of these can be used to link cust_bill_pkg to cust_main_county +my $pkg_tax = "SELECT SUM(amount) as tax_amount, invnum, taxnum, ". + "cust_bill_pkg_tax_location.pkgnum ". + "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ". + "GROUP BY billpkgnum, invnum, taxnum, cust_bill_pkg_tax_location.pkgnum"; + +my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". + "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum"; -my( $location_sql, @base_param ) = FS::cust_pkg->location_sql; -$where .= " AND $location_sql "; +my $where = "WHERE _date >= $beginning AND _date <= $ending "; +# SELECT/GROUP clauses for first-level queries +# classnum is a placeholder; they all go in one class in this case. +my $select = "SELECT NULL AS classnum, cust_main_county.taxnum, "; +my $group = "GROUP BY cust_main_county.taxnum"; +# SELECT/GROUP clauses for second-level (totals) queries +my $select_all = "SELECT NULL AS classnum, "; +my $group_all = ""; + +if ( $with_pkgclass ) { + $select = "SELECT COALESCE(part_pkg.classnum,0), cust_main_county.taxnum, "; + $group = "GROUP BY part_pkg.classnum, cust_main_county.taxnum"; + $select_all = "SELECT COALESCE(part_pkg.classnum,0), "; + $group_all = "GROUP BY COALESCE(part_pkg.classnum,0)"; +} my $agentname = ''; if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -261,303 +341,228 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $where .= ' AND cust_main.agentnum = '. $agent->agentnum; } -sub gotcust { - my $table = shift; - my $prefix = @_ ? shift : ''; - " - ( $table.${prefix}city = cust_main_county.city - OR cust_main_county.city = '' - OR cust_main_county.city IS NULL ) - AND ( $table.${prefix}county = cust_main_county.county - OR cust_main_county.county = '' - OR cust_main_county.county IS NULL ) - AND ( $table.${prefix}state = cust_main_county.state - OR cust_main_county.state = '' - OR cust_main_county.state IS NULL ) - AND ( $table.${prefix}country = cust_main_county.country ) - "; +my $nottax = + '(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)'; + +# one query for each column of the report +# plus separate queries for the totals row +my (%sql, %all_sql); + +# SALES QUERIES (taxable sales, all types of exempt sales) +# ------------- + +# general form +my $exempt = "$select SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax $group"; + +my $all_exempt = "$select_all SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group_all"; + +# sales to tax-exempt customers +$sql{exempt_cust} = $exempt; +$sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; +$all_sql{exempt_cust} = $all_exempt; +$all_sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + +# sales of tax-exempt packages +$sql{exempt_pkg} = $exempt; +$sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; +$all_sql{exempt_pkg} = $all_exempt; +$all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; + +# monthly per-customer exemptions +$sql{exempt_monthly} = $exempt; +$sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; +$all_sql{exempt_monthly} = $all_exempt; +$all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; + +# taxable sales +$sql{taxable} = "$select + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum + AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + $join_cust_pkg $where AND $nottax $group"; + +# Here we're going to sum all line items that are taxable _at all_, +# under any tax. exempt_charged is the sum of all exemptions for a +# particular billpkgnum + taxnum; we take the taxnum that has the +# smallest sum of exemptions and subtract that from the charged amount. +$all_sql{taxable} = "$select_all + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(min_exempt, 0)) + FROM cust_bill_pkg + JOIN ( + SELECT invnum, pkgnum, MIN(exempt_charged) AS min_exempt + FROM ($pkg_tax) AS pkg_tax + JOIN cust_bill_pkg USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + GROUP BY invnum, pkgnum + ) AS pkg_is_taxable + USING (invnum, pkgnum) + $join_cust_pkg $where AND $nottax $group_all"; + +$sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted +$all_sql{taxable} =~ s/EXEMPT_WHERE//; + +# there isn't one for 'sales', because we calculate sales by adding up +# the taxable and exempt columns. + +# TAX QUERIES (billed tax, credited tax) +# ----------- + +# sum of billed tax: +# join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location +my $taxfrom = " FROM cust_bill_pkg + $join_cust + LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) + LEFT JOIN cust_main_county USING ( taxnum )"; + +if ( $with_pkgclass ) { + # If we're not grouping by package class, this is unnecessary, and + # probably really expensive. + $taxfrom .= " + LEFT JOIN cust_bill_pkg AS taxable + ON (cust_bill_pkg_tax_location.taxable_billpkgnum = taxable.billpkgnum) + LEFT JOIN cust_pkg ON (taxable.pkgnum = cust_pkg.pkgnum) + LEFT JOIN part_pkg USING (pkgpart)"; } -my $gotcust; -if ( $conf->exists('tax-ship_address') ) { - - $gotcust = " - ( cust_main_county.country = cust_main.country - OR cust_main_county.country = cust_main.ship_country - ) - - AND - - ( - ( ( ship_last IS NULL OR ship_last = '' ) - AND ". gotcust('cust_main'). " - ) - OR - ( ship_last IS NOT NULL AND ship_last != '' - AND ". gotcust('cust_main', 'ship_'). " - ) - ) - "; - -} else { - - $gotcust = gotcust('cust_main'); - -} -if ( $conf->exists('tax-pkg_address') ) { - $gotcust = " - ( cust_pkg.locationnum IS NULL AND $gotcust) - OR ( cust_pkg.locationnum IS NOT NULL AND ". gotcust('cust_location'). " )"; - $gotcust = - "WHERE 0 < ( SELECT COUNT(*) FROM cust_pkg - LEFT JOIN cust_main USING ( custnum ) - LEFT JOIN cust_location USING ( locationnum ) - WHERE $gotcust - LIMIT 1 - ) - "; -} else { - $gotcust = - "WHERE 0 < ( SELECT COUNT(*) FROM cust_main WHERE $gotcust LIMIT 1 )"; +my $istax = "cust_bill_pkg.pkgnum = 0"; +my $named_tax = + "COALESCE(taxname,'Tax') = COALESCE(cust_bill_pkg.itemdesc,'Tax')"; + +$sql{tax} = "$select SUM(cust_bill_pkg_tax_location.amount) + $taxfrom + $where AND $istax AND $named_tax + $group"; + +$all_sql{tax} = "$select_all SUM(cust_bill_pkg.setup) + FROM cust_bill_pkg + $join_cust + $where AND $istax + $group_all"; + +# sum of credits applied against billed tax +# ($creditfrom includes join of taxable item to part_pkg if with_pkgclass +# is on) +my $creditfrom = $taxfrom . + ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)'; +my $creditwhere = $where . + ' AND billpkgtaxratelocationnum IS NULL'; + +$sql{credit} = "$select SUM(cust_credit_bill_pkg.amount) + $creditfrom + $creditwhere AND $istax AND $named_tax + $group"; + +$all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust + $where AND $istax + $group_all"; + +if ( $with_pkgclass ) { + # the slightly more complicated version, with lots of joins that are + # unnecessary if you're not breaking down by package class + $all_sql{tax} = "$select_all SUM(cust_bill_pkg_tax_location.amount) + $taxfrom + $where AND $istax + $group_all"; + + $all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount) + $creditfrom + $creditwhere AND $istax + $group_all"; } -my $out = 'Out of taxable region(s)'; -my %regions = (); - -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}->{$_} = $r->$_() for (qw( county state country )); #taxname? - - my @url_param = qw( county state country taxname ); - push @url_param, 'city' if $cgi->param('show_cities') && $r->city(); - - $regions{$label}->{'url_param'} = - join(';', map "$_=".uri_escape($r->$_()), @url_param ); - - my @param = @base_param; - my $mywhere = $where; - - if ( $r->taxclass ) { - - $mywhere .= " AND taxclass = ? "; - push @param, 'taxclass'; - $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass); - #no, always# if $cgi->param('show_taxclasses'); - - $regions{$label}->{'taxclass'} = $r->taxclass; - - } else { - - $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_pkg $mywhere"; # AND payby != 'COMP' "; - -# my $label = getlabel($r); -# $regions{$label}->{'label'} = $label; - - my $nottax = 'pkgnum != 0'; - - ## calculate total for this region - - my $t_sql = - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"; - my $t = scalar_sql($r, \@param, $t_sql); - $regions{$label}->{'total'} += $t; - - #if ( $label eq $out ) # && $t ) { - # warn "adding $t for ". - # join('/', map $r->$_, qw( taxclass county state country ) ). "\n"; - # #warn $t_sql if $r->state eq 'FL'; - #} - - ## calculate customer-exemption for this region - -## my $taxable = $t; - -# my($taxable, $x_cust) = (0, 0); -# foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } -# qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) { -# $taxable += scalar_sql($r, \@param, -# "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )" -# ); -# -# $x_cust += scalar_sql($r, \@param, -# "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'" -# ); -# } - - #false laziness -ish w/report_tax.cgi - my $cust_exempt; - if ( $r->taxname ) { - my $q_taxname = dbh->quote($r->taxname); - $cust_exempt = - "( tax = 'Y' - OR EXISTS ( SELECT 1 FROM cust_main_exemption - WHERE cust_main_exemption.custnum = cust_main.custnum - AND cust_main_exemption.taxname = $q_taxname - ) - ) - "; - } else { - $cust_exempt = " tax = 'Y' "; +# "out of taxable region" sales +$all_sql{out_sales} = + "$select_all SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM (cust_bill_pkg $join_cust_pkg) + LEFT JOIN ($pkg_tax) AS pkg_tax USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum) + $where AND $nottax + AND pkg_tax.taxnum IS NULL AND pkg_tax_exempt.taxnum IS NULL + $group_all" +; + +$all_sql{out_sales} =~ s/EXEMPT_WHERE//; + +my %data; +my %total; +foreach my $k (keys(%sql)) { + my $stmt = $sql{$k}; + warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG; + my $sth = dbh->prepare($stmt); + # three columns: classnum, taxnum, value + $sth->execute + or die "failed to execute $k query: ".$sth->errstr; + while ( my $row = $sth->fetchrow_arrayref ) { + $data{$k}{$row->[0]}{$row->[1]} = $row->[2]; } - - my $x_cust = scalar_sql($r, \@param, - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) - $fromwhere AND $nottax AND $cust_exempt " - ); - - $regions{$label}->{'exempt_cust'} += $x_cust; - - ## calculate package-exemption for this region - - my $x_pkg = scalar_sql($r, \@param, - "SELECT SUM( - ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 - END - ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 - END - ) - ) - $fromwhere - AND $nottax - AND ( - ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) - ) - AND ( tax != 'Y' OR tax IS NULL ) - " - ); - $regions{$label}->{'exempt_pkg'} += $x_pkg; - - ## calculate monthly exemption (texas tax) for this region - - # count up all the cust_tax_exempt_pkg records associated with - # the actual line items. - - my $x_monthly = scalar_sql($r, \@param, - "SELECT SUM(amount) - FROM cust_tax_exempt_pkg - JOIN cust_bill_pkg USING ( billpkgnum ) - $join_cust_pkg - $mywhere" - ); - $regions{$label}->{'exempt_monthly'} += $x_monthly; - - my $taxable = $t - $x_cust - $x_pkg - $x_monthly; - $regions{$label}->{'taxable'} += $taxable; - - $regions{$label}->{'owed'} += $taxable * ($r->tax/100); - - if ( defined($regions{$label}->{'rate'}) - && $regions{$label}->{'rate'} != $r->tax.'%' ) { - $regions{$label}->{'rate'} = 'variable'; - } else { - $regions{$label}->{'rate'} = $r->tax.'%'; +} +warn "DATA:\n".Dumper(\%data) if $DEBUG > 1; + +foreach my $k (keys %all_sql) { + warn "\n".$all_sql{$k}."\n" if $DEBUG; + my $sth = dbh->prepare($all_sql{$k}); + # two columns: classnum, value + $sth->execute + or die "failed to execute $k totals query: ".$sth->errstr; + while ( my $row = $sth->fetchrow_arrayref ) { + $total{$k}{$row->[0]} = $row->[1]; } - } - -my $distinct = "country, state, county, city, - 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, +warn "TOTALS:\n".Dumper(\%total);# if $DEBUG > 1; +# so $data{tax}, for example, is now a hash with one entry +# for each classnum, containing a hash with one entry for each +# taxnum, containing the tax billed on that taxnum. +# if with_pkgclass is off, then the classnum is always null. + +# integrity checks +# unlinked tax collected +my $out_tax_sql = + "SELECT SUM(cust_bill_pkg.setup) + FROM (cust_bill_pkg $join_cust) + LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) + $where AND $istax AND cust_bill_pkg_tax_location.billpkgnum IS NULL" +; +my $unlinked_tax = FS::Record->scalar_sql($out_tax_sql); +# unlinked tax credited +my $out_credit_sql = + "SELECT SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust + $where AND $istax AND cust_credit_bill_pkg.billpkgtaxlocationnum IS NULL" +; +my $unlinked_credit = FS::Record->scalar_sql($out_credit_sql); + +# all sales +my $all_sales = FS::Record->scalar_sql( + "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM cust_bill_pkg $join_cust $where AND $nottax" ); -my $taxfromwhere = " FROM cust_bill_pkg $join_cust "; -my $taxwhere = $where; -if ( $conf->exists('tax-pkg_address') ) { - - $taxfromwhere .= 'LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) - LEFT JOIN cust_location USING ( locationnum ) '; - - #quelle kludge - $taxwhere =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g; - -} -my $creditfromwhere = $taxfromwhere. - " JOIN cust_credit_bill_pkg USING (billpkgnum"; -$creditfromwhere .= " ,billpkgtaxlocationnum" - if $conf->exists('tax-pkg_address'); -$creditfromwhere .= ")"; - -$taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' "; -$creditfromwhere .= " $taxwhere AND billpkgtaxratelocationnum IS NULL"; #AND payby != 'COMP' "; - -my @taxparam = @base_param; - - -#should i be a cust_main_county method or something -#need to pass in $taxfromwhere & @taxparam??? -my $_taxamount_sub = sub { - my $r = shift; - - #match itemdesc if necessary! - my $named_tax = - $r->taxname - ? 'AND itemdesc = '. dbh->quote($r->taxname) - : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - - my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ". - " $taxfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax"; - - scalar_sql($r, \@taxparam, $sql ); -}; - -my $_creditamount_sub = sub { - my $r = shift; - - #match itemdesc if necessary! - my $named_tax = - $r->taxname - ? 'AND itemdesc = '. dbh->quote($r->taxname) - : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - - my $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ". - " $creditfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax"; - - scalar_sql($r, \@taxparam, $sql ); -}; - #tax-report_groups filtering my($group_op, $group_value) = ( '', '' ); if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) { ( $group_op, $group_value ) = ( $1, $2 ); } -my $group_test = sub { +my $group_test = sub { # to be applied to a tax label my $label = shift; return 1 unless $group_op; #in case we get called inadvertantly if ( $label eq $out ) { #don't display "out of taxable region" in this case @@ -571,215 +576,213 @@ my $group_test = sub { } }; -my $tot_tax = 0; -my $tot_credit = 0; -#foreach my $label ( keys %regions ) { -foreach my $r ( qsearch(\%qsearch) ) { - - #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; - - my $label = getlabel($r); - if ( $group_op ) { - next unless &{$group_test}($label); - } - - #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' "; - #my @param = @base_param; - - my $x = &{$_taxamount_sub}($r); - - $regions{$label}->{'tax'} += $x; - $tot_tax += $x unless $cgi->param('show_taxclasses'); - - ## calculate credit for this region - - $x = &{$_creditamount_sub}($r); - - $regions{$label}->{'credit'} += $x; - $tot_credit += $x unless $cgi->param('show_taxclasses'); - +my @pkgclasses; +if ($with_pkgclass) { + @pkgclasses = qsearch('pkg_class', {}); + push @pkgclasses, FS::pkg_class->new({ + classnum => '0', + classname => 'Unclassified', + }); +} else { + @pkgclasses = ( FS::pkg_class->new({ + classnum => '', + classname => '', + }) ); } +my %pkgclass_data; + +foreach my $class (@pkgclasses) { + my $classnum = $class->classnum; + my $classname = $class->classname; + + # if show_taxclasses is on, %base_regions will contain the same data + # as %regions, but with taxclasses merged together (and ignoring report_group + # filtering). + my (%regions, %base_regions); + + my @loc_params = qw(country state county); + push @loc_params, 'city' if $cgi->param('show_cities'); + push @loc_params, 'district' if $cgi->param('show_districts'); + + foreach my $r ( qsearch({ 'table' => 'cust_main_county', })) { + my $taxnum = $r->taxnum; + # set up a %regions entry for this region's tax label + my $label = $r->label(%label_opt); + next if $label eq $out; + $regions{$label} ||= { label => $label }; + + $regions{$label}->{$_} = $r->get($_) foreach @loc_params; + $regions{$label}->{taxnums} ||= []; + push @{ $regions{$label}->{taxnums} }, $r->taxnum; + + my %x; # keys are data items (like 'tax', 'exempt_cust', etc.) + foreach my $k (keys %data) { + next unless exists($data{$k}{$classnum}{$taxnum}); + $x{$k} = $data{$k}{$classnum}{$taxnum}; + $regions{$label}{$k} += $x{$k}; + if ( $k eq 'taxable' or $k =~ /^exempt/ ) { + $regions{$label}->{'sales'} += $x{$k}; + } + } + + my $owed = $data{'taxable'}{$classnum}{$taxnum} * ($r->tax/100); + $regions{$label}->{'owed'} += $owed; + $total{'owed'}{$classnum} += $owed; + + if ( defined($regions{$label}->{'rate'}) + && $regions{$label}->{'rate'} != $r->tax.'%' ) { + $regions{$label}->{'rate'} = 'variable'; + } else { + $regions{$label}->{'rate'} = $r->tax.'%'; + } + + if ( $cgi->param('show_taxclasses') ) { + my $base_label = $r->label(%label_opt, 'with_taxclass' => 0); + $base_regions{$base_label} ||= + { + label => $base_label, + tax => 0, + credit => 0, + }; + $base_regions{$base_label}->{tax} += $x{tax}; + $base_regions{$base_label}->{credit} += $x{credit}; + } -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 "$_=". uri_escape($r->$_()), - qw( county state country taxname ) - ); + my @regions = map { $_->{label} } + sort { + ($b eq $out) <=> ($a eq $out) + or $a->{country} cmp $b->{country} + or $a->{state} cmp $b->{state} + or $a->{county} cmp $b->{county} + or $a->{city} cmp $b->{city} + } + grep { $_->{sales} > 0 or $_->{tax} > 0 or $_->{credit} > 0 } + values %regions; + + #tax-report_groups filtering + @regions = grep &{$group_test}($_), @regions + if $group_op; + + #calculate totals + my %taxclasses = (); + my %county = (); + my %state = (); + my %country = (); + foreach my $label (@regions) { + $taxclasses{$regions{$_}->{'taxclass'}} = 1 + if $regions{$_}->{'taxclass'}; + $county{$regions{$_}->{'county'}} = 1; + $state{$regions{$_}->{'state'}} = 1; + $country{$regions{$_}->{'country'}} = 1; + } - $base_regions{$base_label}->{'tax'} += $x; - $tot_tax += $x; + my $total_url_param = ''; + my $total_url_param_invoiced = ''; + if ( $group_op ) { - ## calculate credit for this region + my @country = keys %country; + warn "WARNING: multiple countries on this grouped report; total links broken" + if scalar(@country) > 1; + my $country = $country[0]; - $x = &{$_creditamount_sub}($r); + my @state = keys %state; + warn "WARNING: multiple countries on this grouped report; total links broken" + if scalar(@state) > 1; + my $state = $state[0]; - $base_regions{$base_label}->{'credit'} += $x; - $tot_credit += $x; + $total_url_param_invoiced = + $total_url_param = + 'report_group='.uri_escape("$group_op $group_value").';'. + join(';', map 'taxclass='.uri_escape($_), keys %taxclasses ); + $total_url_param .= ';'. + "country=$country;state=".uri_escape($state).';'. + join(';', map 'county='.uri_escape($_), keys %county ) ; } -} + #ordering + @regions = + map $regions{$_}, + sort { $a cmp $b } + @regions; + + my @base_regions = + map $base_regions{$_}, + sort { $a cmp $b } + keys %base_regions; + + #add "Out of taxable" and total lines + if ( $total{out_sales}{$classnum} ) { + my %out = ( + 'sales' => $total{out_sales}{$classnum}, + 'label' => $out, + 'rate' => '' + ); + push @regions, \%out; + push @base_regions, \%out; + } -my @regions = keys %regions; + if ( @regions ) { + my %class_total = map { $_ => $total{$_}{$classnum} } keys(%total); + $class_total{is_total} = 1; + $class_total{sales} = sum( + @class_total{ 'taxable', + 'out_sales', + grep(/^exempt/, keys %class_total) } + ); + + push @regions, \%class_total; + push @base_regions, \%class_total; + } -#tax-report_groups filtering -@regions = grep &{$group_test}($_), @regions - if $group_op; - -#calculate totals -my( $total, $tot_taxable, $tot_owed ) = ( 0, 0, 0 ); -my( $exempt_cust, $exempt_pkg, $exempt_monthly, $tot_credit ) = ( 0, 0, 0, 0 ); -my %taxclasses = (); -my %county = (); -my %state = (); -my %country = (); -foreach (@regions) { - $total += $regions{$_}->{'total'}; - $tot_taxable += $regions{$_}->{'taxable'}; - $tot_owed += $regions{$_}->{'owed'}; - $exempt_cust += $regions{$_}->{'exempt_cust'}; - $exempt_pkg += $regions{$_}->{'exempt_pkg'}; - $exempt_monthly += $regions{$_}->{'exempt_monthly'}; - $tot_credit += $regions{$_}->{'credit'}; - $taxclasses{$regions{$_}->{'taxclass'}} = 1 - if $regions{$_}->{'taxclass'}; - $county{$regions{$_}->{'county'}} = 1; - $state{$regions{$_}->{'state'}} = 1; - $country{$regions{$_}->{'country'}} = 1; + $pkgclass_data{$classname} = { + classnum => $classnum, + classname => $classname, + regions => \@regions, + base_regions => \@base_regions, + }; } -my $total_url_param = ''; -my $total_url_param_invoiced = ''; -if ( $group_op ) { - - my @country = keys %country; - warn "WARNING: multiple countries on this grouped report; total links broken" - if scalar(@country) > 1; - my $country = $country[0]; +if ( $with_pkgclass ) { + my $class_zero = delete( $pkgclass_data{'Unclassified'} ); + @pkgclasses = map { $pkgclass_data{$_} } + sort { $a cmp $b } + keys %pkgclass_data; + push @pkgclasses, $class_zero; - my @state = keys %state; - warn "WARNING: multiple countries on this grouped report; total links broken" - if scalar(@state) > 1; - my $state = $state[0]; + my %grand_total = map { + $_ => sum( values(%{ $total{$_} }) ) + } keys(%total); - $total_url_param_invoiced = - $total_url_param = - 'report_group='.uri_escape("$group_op $group_value").';'. - join(';', map 'taxclass='.uri_escape($_), keys %taxclasses ); - $total_url_param .= ';'. - "country=$country;state=".uri_escape($state).';'. - join(';', map 'county='.uri_escape($_), keys %county ) ; + $grand_total{sales} = $all_sales; + push @pkgclasses, { + classnum => '', + classname => 'Total', + regions => [ \%grand_total ], + base_regions => [ \%grand_total ], + } +} else { + @pkgclasses = $pkgclass_data{''}; } -#ordering -@regions = - map $regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } - @regions; - -my @base_regions = - map $base_regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } - keys %base_regions; - -#add total line -push @regions, { - 'label' => 'Total', - 'url_param' => $total_url_param, - 'url_param_inv' => $total_url_param_invoiced, - 'total' => $total, - 'exempt_cust' => $exempt_cust, - 'exempt_pkg' => $exempt_pkg, - 'exempt_monthly' => $exempt_monthly, - 'taxable' => $tot_taxable, - 'rate' => '', - 'owed' => $tot_owed, - 'tax' => $tot_tax, - 'credit' => $tot_credit, -}; - #-- my $money_char = $conf->config('money_char') || '$'; my $money_sprintf = sub { $money_char. sprintf('%.2f', shift ); }; - -sub getlabel { - my $r = shift; - my %opt = @_; - - my $label; - if ( - $r->tax == 0 - && ! scalar( qsearch('cust_main_county', { 'city' => $r->city, - 'county' => $r->county, - 'state' => $r->state, - 'country' => $r->country, - 'tax' => { op=>'>', value=>0 }, - } - ) - ) - - ) { - #kludge to avoid "will not stay shared" warning - my $out = 'Out of taxable region(s)'; - $label = $out; -# } elsif ( $r->taxname && count_taxname($r->taxname) == 1 ) { -# $label = $r->taxname; -## $regions{$label}->{'taxname'} = $label; -## push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country ); - } else { - $label = $r->country; - $label = $r->state.", $label" if $r->state; - $label = $r->county." county, $label" if $r->county; - $label = $r->city. ", $label" if $r->city && $cgi->param('show_cities'); - $label = "$label (". $r->taxclass. ")" - if $r->taxclass - && $cgi->param('show_taxclasses') - && ! $opt{'no_taxclass'}; - $label = $r->taxname. " ($label)" if $r->taxname; - } - return $label; -} - -#my %count_taxname = (); #cache -#sub count_taxname { -# my $taxname = shift; -# return $count_taxname{$taxname} if exists $count_taxname{$taxname}; -# my $sql = 'SELECT COUNT(*) FROM cust_main_county WHERE taxname = ?'; -# my $sth = dbh->prepare($sql) or die dbh->errstr; -# $sth->execute( $taxname ) -# or die "Unexpected error executing statement $sql: ". $sth->errstr; -# $count_taxname{$taxname} = $sth->fetchrow_arrayref->[0]; -#} - -#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; -} +my $money_sprintf_nonzero = sub { + $_[0] == 0 ? '' : &$money_sprintf($_[0]) +}; my $dateagentlink = "begin=$beginning;end=$ending"; $dateagentlink .= ';agentnum='. $cgi->param('agentnum') if length($agentname); my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink"; my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; +my $creditlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;credit=1";