X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTax.pm;h=0923d55cfb32091641118da7f0dc9f927dc5d9f7;hb=d19d491320789ae2e621d35cc7d67ac1c7696367;hp=43337a62193bb7fb372476d987f988f74e704c9d;hpb=fa0223015fe6c03491b1d0d43524e03ac5fdb899;p=freeside.git diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index 43337a621..0923d55cf 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -2,7 +2,7 @@ package FS::Report::Tax; use strict; use vars qw($DEBUG); -use FS::Record qw(dbh qsearch qsearchs); +use FS::Record qw(dbh qsearch qsearchs group_concat_sql); use Date::Format qw( time2str ); use Data::Dumper; @@ -41,13 +41,9 @@ sub report_internal { my ($taxname, $country, %breakdown); - # purify taxname properly here, as we're going to include it in lots of - # SQL statements using single quotes only - if ( $opt{taxname} =~ /^([\w\s]+)$/ ) { - $taxname = $1; - } else { - die "taxname required"; # UI prevents this - } + # taxname can contain arbitrary punctuation; escape it properly and + # include $taxname unquoted elsewhere + $taxname = dbh->quote($opt{'taxname'}); if ( $opt{country} =~ /^(\w\w)$/ ) { $country = $1; @@ -56,9 +52,10 @@ sub report_internal { } # %breakdown: short name => field identifier + # null classnum should remain null, not be converted to zero %breakdown = ( 'taxclass' => 'cust_main_county.taxclass', - 'pkgclass' => 'part_pkg.classnum', + 'pkgclass' => 'COALESCE(part_fee.classnum,part_pkg.classnum)', 'city' => 'cust_main_county.city', 'district' => 'cust_main_county.district', 'state' => 'cust_main_county.state', @@ -73,7 +70,8 @@ sub report_internal { my $join_cust_pkg = $join_cust. ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) '; + LEFT JOIN part_pkg USING ( pkgpart ) + LEFT JOIN part_fee USING ( feepart ) '; my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; @@ -88,19 +86,22 @@ sub report_internal { "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ". "GROUP BY taxable_billpkgnum, taxnum"; - # This one links a tax-exempted line item (billpkgnum) to a tax rate (taxnum), - # and gives the amount of the tax exemption. EXEMPT_WHERE should be replaced - # with a real WHERE clause to further limit the tax exemptions that will be - # included. + # This one links a tax-exempted line item (billpkgnum) to a tax rate + # (taxnum), and gives the amount of the tax exemption. EXEMPT_WHERE must + # be replaced with an expression to further limit the tax exemptions + # that will be included, or "TRUE" to not limit them. + # + # Note that tax exemptions with non-null creditbillpkgnum are always + # excluded. Those are "negative exemptions" created by crediting a sale + # that had received an exemption. my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". - "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum"; - - # This just calculates the sum of credit applications to a line item. - my $pkg_credited = "SELECT SUM(amount) AS credited, billpkgnum ". - "FROM cust_credit_bill_pkg GROUP BY billpkgnum"; + "FROM cust_tax_exempt_pkg WHERE + ( EXEMPT_WHERE ) + AND cust_tax_exempt_pkg.creditbillpkgnum IS NULL + GROUP BY billpkgnum, taxnum"; my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending ". - "AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ". + "AND COALESCE(cust_main_county.taxname,'Tax') = $taxname ". "AND cust_main_county.country = '$country'"; # SELECT/GROUP clauses for first-level queries my $select = "SELECT "; @@ -113,7 +114,8 @@ sub report_internal { $select .= "NULL AS $_, "; } } - $select .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, "; + $select .= group_concat_sql('DISTINCT(cust_main_county.taxnum)', ',') . + ' AS taxnums, '; $group =~ s/, $//; # SELECT/GROUP clauses for second-level (totals) queries @@ -124,7 +126,8 @@ sub report_internal { $select_all = "SELECT $breakdown{pkgclass} AS pkgclass, "; $group_all = "GROUP BY $breakdown{pkgclass}"; } - $select_all .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, "; + $select_all .= group_concat_sql('DISTINCT(cust_main_county.taxnum)', ',') . + ' AS taxnums, '; my $agentnum; if ( $opt{agentnum} and $opt{agentnum} =~ /^(\d+)$/ ) { @@ -163,106 +166,82 @@ sub report_internal { # sales to tax-exempt customers $sql{exempt_cust} = $exempt; - $sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + $sql{exempt_cust} =~ s/EXEMPT_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'/; + $all_sql{exempt_cust} =~ s/EXEMPT_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'/; + $sql{exempt_pkg} =~ s/EXEMPT_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'/; + $all_sql{exempt_pkg} =~ s/EXEMPT_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'/; + $sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; $all_sql{exempt_monthly} = $all_exempt; - $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; + $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; # taxable sales - # (sale - exemptions - credits, except not negative) $sql{taxable} = "$select - SUM( - cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - ) - FROM cust_bill_pkg - LEFT JOIN ($pkg_tax) AS pkg_tax - ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) + 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 (billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) - LEFT JOIN ($pkg_credited) AS pkg_credited - ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) - LEFT JOIN cust_main_county - ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) + 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"; $all_sql{taxable} = "$select_all - SUM( - cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - ) - FROM cust_bill_pkg - LEFT JOIN ($pkg_tax) AS pkg_tax - ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) + 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 (billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) - LEFT JOIN ($pkg_credited) AS pkg_credited - ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) - LEFT JOIN cust_main_county - ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) + 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_all"; - $sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted - $all_sql{taxable} =~ s/EXEMPT_WHERE//; + $sql{taxable} =~ s/EXEMPT_WHERE/TRUE/; # unrestricted + $all_sql{taxable} =~ s/EXEMPT_WHERE/TRUE/; # estimated tax (taxable * rate) $sql{estimated} = "$select SUM(cust_main_county.tax / 100 * - ( cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - ) + (cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) ) - FROM cust_bill_pkg - LEFT JOIN ($pkg_tax) AS pkg_tax - ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) - LEFT JOIN ($pkg_credited) AS pkg_credited - ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) - LEFT JOIN cust_main_county - ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) + 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"; $all_sql{estimated} = "$select_all SUM(cust_main_county.tax / 100 * - ( cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - ) + (cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) ) - FROM cust_bill_pkg - LEFT JOIN ($pkg_tax) AS pkg_tax - ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) - LEFT JOIN ($pkg_credited) AS pkg_credited - ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) - LEFT JOIN cust_main_county - ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) + 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_all"; + $sql{estimated} =~ s/EXEMPT_WHERE/TRUE/; # unrestricted + $all_sql{estimated} =~ s/EXEMPT_WHERE/TRUE/; + # there isn't one for 'sales', because we calculate sales by adding up # the taxable and exempt columns. - # TAX QUERIES (billed tax, credited tax) + # TAX QUERIES (billed tax, credited tax, collected tax) # ----------- # sum of billed tax: @@ -275,14 +254,16 @@ sub report_internal { if ( $breakdown{pkgclass} ) { # If we're not grouping by package class, this is unnecessary, and # probably really expensive. + # Remember that fees also have package classes. $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)"; + LEFT JOIN part_pkg USING (pkgpart) + LEFT JOIN part_fee ON (taxable.feepart = part_fee.feepart) "; } - my $istax = "cust_bill_pkg.pkgnum = 0"; + my $istax = "cust_bill_pkg.pkgnum = 0 and cust_bill_pkg.feepart is null"; $sql{tax} = "$select SUM(cust_bill_pkg_tax_location.amount) $taxfrom @@ -295,8 +276,8 @@ sub report_internal { $group_all"; # sum of credits applied against billed tax - # ($creditfrom includes join of taxable item to part_pkg if with_pkgclass - # is on) + # ($creditfrom includes join of taxable item to part_pkg/part_fee if + # with_pkgclass is on) my $creditfrom = $taxfrom . ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)' . ' JOIN cust_credit_bill USING (creditbillnum)'; @@ -319,6 +300,27 @@ sub report_internal { $creditwhere AND $istax $group_all"; + # sum of tax paid + # this suffers from the same ambiguity as anything else that applies + # received payments to specific packages, but in reality the discrepancy + # should be minimal since people either pay their bill or don't. + # the join is on billpkgtaxlocationnum to avoid cross-producting. + + my $paidfrom = $taxfrom . + ' JOIN cust_bill_pay_pkg'. + ' ON (cust_bill_pay_pkg.billpkgtaxlocationnum ='. + ' cust_bill_pkg_tax_location.billpkgtaxlocationnum)'; + + $sql{tax_paid} = "$select SUM(cust_bill_pay_pkg.amount) + $paidfrom + $where AND $istax + $group"; + + $all_sql{tax_paid} = "$select_all SUM(cust_bill_pay_pkg.amount) + $paidfrom + $where AND $istax + $group_all"; + my %data; my %total; # note that we use keys(%sql) here and keys(%all_sql) later. nothing @@ -326,7 +328,7 @@ sub report_internal { # as for the individual category queries foreach my $k (keys(%sql)) { my $stmt = $sql{$k}; - warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG; + warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG > 1; my $sth = dbh->prepare($stmt); # eight columns: pkgclass, taxclass, state, county, city, district # taxnums (comma separated), value @@ -345,7 +347,7 @@ sub report_internal { push @$bin, [ $k, $row->[6], $row->[7] ]; } } - warn "DATA:\n".Dumper(\%data) if $DEBUG > 1; + warn "DATA:\n".Dumper(\%data) if $DEBUG; foreach my $k (keys %all_sql) { warn "\nTOTAL ".uc($k).":\n".$all_sql{$k}."\n" if $DEBUG; @@ -389,13 +391,14 @@ sub report_internal { SELECT 1 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum) WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum - AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' + AND COALESCE(cust_main_county.taxname,'Tax') = $taxname + AND cust_tax_exempt_pkg.creditbillpkgnum IS NULL ) AND NOT EXISTS( SELECT 1 FROM cust_bill_pkg_tax_location JOIN cust_main_county USING (taxnum) WHERE cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum - AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' + AND COALESCE(cust_main_county.taxname,'Tax') = $taxname ) "; warn "\nOUTSIDE:\n$sql_outside\n" if $DEBUG;