$all_sql{exempt_monthly} = $all_exempt;
$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";
# 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";
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";
$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";
' ON (cust_bill_pay_pkg.billpkgtaxlocationnum ='.
' cust_bill_pkg_tax_location.billpkgtaxlocationnum)';
- $sql{tax_paid} = "$select SUM(cust_bill_pay_pkg.amount)
+ $sql{tax_paid} = "$select COALESCE(SUM(cust_bill_pay_pkg.amount),0)
$paidfrom
$where AND $istax
$group";
- $all_sql{tax_paid} = "$select_all SUM(cust_bill_pay_pkg.amount)
+ $all_sql{tax_paid} = "$select_all COALESCE(SUM(cust_bill_pay_pkg.amount),0)
$paidfrom
$where AND $istax
$group_all";
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)
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
+ )";
+ 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 (
# 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;
}
# 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';
projects / freeside.git / blobdiff