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 ".
+# Either or both of these can be used to link cust_bill_pkg to
+# cust_main_county. This one links a taxed line item (billpkgnum) to a tax rate
+# (taxnum), and gives the amount of tax charged on that line item under that
+# rate (as tax_amount).
+my $pkg_tax = "SELECT SUM(amount) as tax_amount, taxnum, ".
+ "taxable_billpkgnum AS billpkgnum ".
"FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ".
- "GROUP BY billpkgnum, invnum, taxnum, cust_bill_pkg_tax_location.pkgnum";
+ "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.
my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ".
"FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum";
-my $where = "WHERE _date >= $beginning AND _date <= $ending ";
+my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._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, ";
$where .= ' AND cust_main.agentnum = '. $agent->agentnum;
}
-my $nottax = 'cust_bill_pkg.pkgnum != 0';
+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
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)
+ 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)
# 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.
+#
+# (This isn't an exact result, since line items can be taxable under
+# one tax and not another. Under 4.x the tax report is designed to
+# consider only one variety of tax at a time, which should solve this.)
+
$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
+ SELECT billpkgnum, MIN(exempt_charged) AS min_exempt
FROM ($pkg_tax) AS pkg_tax
- JOIN cust_bill_pkg USING (invnum, pkgnum)
+ JOIN cust_bill_pkg USING (billpkgnum)
LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum)
- GROUP BY invnum, pkgnum
+ GROUP BY billpkgnum
) AS pkg_is_taxable
- USING (invnum, pkgnum)
+ USING (billpkgnum)
$join_cust_pkg $where AND $nottax $group_all";
$sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted
# ($creditfrom includes join of taxable item to part_pkg if with_pkgclass
# is on)
my $creditfrom = $taxfrom .
- ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)';
+ ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)' .
+ ' JOIN cust_credit_bill USING (creditbillnum)';
my $creditwhere = $where .
- ' AND billpkgtaxratelocationnum IS NULL';
+ ' AND billpkgtaxratelocationnum IS NULL';
+my $creditwhere_all = $where;
+
+# if the credit_date option is set to application date, change
+# $creditwhere accordingly
+if ( $cgi->param('credit_date') eq 'cust_credit_bill' ) {
+ $creditwhere =~ s/cust_bill._date/cust_credit_bill._date/g;
+ $creditwhere_all =~ s/cust_bill._date/cust_credit_bill._date/g;
+}
$sql{credit} = "$select SUM(cust_credit_bill_pkg.amount)
$creditfrom
FROM cust_credit_bill_pkg
JOIN cust_bill_pkg USING (billpkgnum)
$join_cust
- $where AND $istax
+ JOIN cust_credit_bill USING (creditbillnum)
+ $creditwhere_all AND $istax
$group_all";
-
+warn "\n\n$all_sql{credit}\n\n";
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{credit} = "$select_all SUM(cust_credit_bill_pkg.amount)
$creditfrom
- $creditwhere AND $istax
+ $creditwhere_all AND $istax
$group_all";
}
$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) AS pkg_tax USING (billpkgnum)
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
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";
+
+my $creditlink = $baselink . ";credit=1";
+if ( $cgi->param('credit_date') eq 'cust_credit_bill' ) {
+ $creditlink =~ s/begin/credit_begin/;
+ $creditlink =~ s/end/credit_end/;
+}
+warn $creditlink;
+
</%init>
projects / freeside.git / blobdiff