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;
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;
}
# %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',
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 ";
"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 ";
$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
$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+)$/ ) {
# 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
+ ";
# 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:
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
$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)';
$creditwhere =~ s/cust_bill._date/cust_credit_bill._date/g;
}
- $sql{credit} = "$select SUM(cust_credit_bill_pkg.amount)
+ $sql{tax_credited} = "$select SUM(cust_credit_bill_pkg.amount)
$creditfrom
$creditwhere AND $istax
$group";
- $all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount)
+ $all_sql{tax_credited} = "$select_all SUM(cust_credit_bill_pkg.amount)
$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 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
# 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
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;
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;
projects / freeside.git / blobdiff