X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTax.pm;h=f1f6be38e78aaf48b321c54d37e8395fd82101ff;hb=fbb0b4a9c32444f89e0b5aacfebdf883070c3b21;hp=f53414b8cbf8e7fab55f427b8bd0859be43f267c;hpb=642f5b08d9e9ac63252d07523d8f04b9e09752c2;p=freeside.git diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index f53414b8c..f1f6be38e 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -2,12 +2,12 @@ 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; -$DEBUG = 2; +$DEBUG = 0; =item report_internal OPTIONS @@ -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,15 +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"; + "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 "; @@ -109,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 @@ -120,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+)$/ ) { @@ -159,79 +166,175 @@ 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'/; + + # credits applied to taxable sales + # Note that negative exemptions (from exempt sales being credited) are NOT + # counted when calculating the exempt amount. (See above.) Therefore we need + # to NOT include any credits against exempt sales in this amount, either. + # These two subqueries implement that. They have joins to cust_credit_bill + # and cust_bill so that credits can be filtered by application date if + # requested. + + # Each row here is the sum of credits applied to a line item. + my $sales_credit = + "SELECT billpkgnum, SUM(cust_credit_bill_pkg.amount) AS credited + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_bill USING (invnum) + WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending + GROUP BY billpkgnum + "; + + # Each row here is the sum of negative exemptions applied to a combination + # of line item and tax definition. + my $exempt_credit = + "SELECT cust_credit_bill_pkg.billpkgnum, taxnum, + 0 - SUM(cust_tax_exempt_pkg.amount) AS exempt_credited + FROM cust_credit_bill_pkg + LEFT JOIN cust_tax_exempt_pkg USING (creditbillpkgnum) + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_bill USING (invnum) + WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending + GROUP BY cust_credit_bill_pkg.billpkgnum, taxnum + "; + + if ( $opt{credit_date} eq 'cust_credit_bill' ) { + $sales_credit =~ s/cust_bill._date/cust_credit_bill._date/g; + $exempt_credit =~ s/cust_bill._date/cust_credit_bill._date/g; + } + + $sql{sales_credited} = "$select + SUM(COALESCE(credited, 0) - COALESCE(exempt_credited, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group + "; + + $all_sql{sales_credited} = "$select_all + SUM(COALESCE(credited, 0) - COALESCE(exempt_credited, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, taxnum) + $join_cust_pkg $where AND $nottax + $group_all + "; + + # also include the exempt-sales credit amount, for the credit report + $sql{exempt_credited} = "$select + SUM(COALESCE(exempt_credited, 0)) + FROM cust_main_county + LEFT JOIN ($exempt_credit) AS exempt_credit USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group + "; + + $all_sql{exempt_credited} = "$select_all + SUM(COALESCE(exempt_credited, 0)) + FROM cust_main_county + LEFT JOIN ($exempt_credit) AS exempt_credit USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group_all + "; # taxable sales $sql{taxable} = "$select - SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 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 (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum - AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, 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)) + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 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 (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum - AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, 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)) + (cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 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 (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum - AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, 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)) + (cust_bill_pkg.setup + cust_bill_pkg.recur + - COALESCE(exempt_charged, 0) + - COALESCE(credited, 0) + + COALESCE(exempt_credited, 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 (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum - AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + LEFT JOIN ($sales_credit) AS sales_credit USING (billpkgnum) + LEFT JOIN ($exempt_credit) AS exempt_credit USING (billpkgnum, 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: @@ -244,28 +347,30 @@ 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) + $sql{tax} = "$select COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) $taxfrom $where AND $istax $group"; - $all_sql{tax} = "$select_all SUM(cust_bill_pkg_tax_location.amount) + $all_sql{tax} = "$select_all COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) $taxfrom $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) + # ($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)'; @@ -278,16 +383,37 @@ sub report_internal { $creditwhere =~ s/cust_bill._date/cust_credit_bill._date/g; } - $sql{credit} = "$select SUM(cust_credit_bill_pkg.amount) + $sql{tax_credited} = "$select COALESCE(SUM(cust_credit_bill_pkg.amount),0) $creditfrom $creditwhere AND $istax $group"; - $all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount) + $all_sql{tax_credited} = "$select_all COALESCE(SUM(cust_credit_bill_pkg.amount),0) $creditfrom $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 COALESCE(SUM(cust_bill_pay_pkg.amount),0) + $paidfrom + $where AND $istax + $group"; + + $all_sql{tax_paid} = "$select_all COALESCE(SUM(cust_bill_pay_pkg.amount),0) + $paidfrom + $where AND $istax + $group_all"; + my %data; my %total; # note that we use keys(%sql) here and keys(%all_sql) later. nothing @@ -295,7 +421,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 @@ -314,7 +440,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; @@ -349,26 +475,50 @@ sub report_internal { if ( $agentnum ) { $outside_where .= " AND cust_main.agentnum = $agentnum"; } - my $sql_outside = "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) - FROM cust_bill_pkg - $join_cust_pkg - $outside_where - AND $nottax + $outside_where .= " AND NOT EXISTS( SELECT 1 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum) WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum - AND cust_main_county.taxname = '$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 cust_main_county.taxname = '$taxname' + AND COALESCE(cust_main_county.taxname,'Tax') = $taxname + )"; + my $sql_outside = "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM cust_bill_pkg + $join_cust_pkg + $outside_where + AND $nottax + "; + warn "\nOUT_SALES:\n$sql_outside\n" if $DEBUG; + my $out_sales = FS::Record->scalar_sql($sql_outside); + + # and out-of-region credit applications, also (excluding those applied + # to out-of-region sales _or taxes_) + if ( $opt{credit_date} eq 'cust_credit_bill' ) { + $outside_where =~ s/cust_bill._date/cust_credit_bill._date/g; + } + + $sql_outside = "SELECT SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg + JOIN cust_credit_bill USING (creditbillnum) + $outside_where + AND NOT EXISTS( + SELECT 1 FROM cust_bill_pkg_tax_location + JOIN cust_main_county USING (taxnum) + WHERE cust_bill_pkg_tax_location.billpkgnum = cust_bill_pkg.billpkgnum + AND COALESCE(cust_main_county.taxname,'Tax') = $taxname ) "; - warn "\nOUTSIDE:\n$sql_outside\n" if $DEBUG; - my $total_outside = FS::Record->scalar_sql($sql_outside); + warn "\nOUT_CREDIT:\n$sql_outside\n" if $DEBUG; + my $out_credit = FS::Record->scalar_sql($sql_outside); my %taxrates; foreach my $tax ( @@ -382,11 +532,12 @@ sub report_internal { # return the data bless { - 'opt' => \%opt, - 'data' => \%data, - 'total' => \%total, - 'taxrates' => \%taxrates, - 'outside' => $total_outside, + 'opt' => \%opt, + 'data' => \%data, + 'total' => \%total, + 'taxrates' => \%taxrates, + 'out_sales' => $out_sales, + 'out_credit' => $out_credit, }, $class; } @@ -449,10 +600,16 @@ sub table { # and calculate row totals $this_row{sales} = sprintf('%.2f', $this_row{taxable} + + $this_row{sales_credited} + $this_row{exempt_cust} + $this_row{exempt_pkg} + $this_row{exempt_monthly} ); + $this_row{credits} = sprintf('%.2f', + $this_row{sales_credited} + + $this_row{exempt_credited} + + $this_row{tax_credited} + ); # and give it a label if ( $this_row{total} ) { $this_row{label} = 'Total'; @@ -500,7 +657,6 @@ sub title { if ( $self->{opt}->{agentnum} ) { my $agent = qsearchs('agent', { agentnum => $self->{opt}->{agentnum} }); $string .= $agent->agent . ' '; - warn $string; } $string .= 'Tax Report: '; # XXX localization if ( $self->{opt}->{beginning} ) {