$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
+ ";
+
# 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";
$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";
my @loc_param = qw( district city county state country );
if ( $cgi->param('out') ) {
+ # probably don't need this part
my ( $loc_sql, @param ) = FS::cust_pkg->location_sql( 'ornull' => 1 );
while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
#hacky, could be more efficient. care if it is ever used for more than the
# tax-report_groups filtering kludge
+ # (does that even still exist? if so, correct this (or location_sql itself)
+ # to work with modern cust_location links)
my $locs_sql =
' ( '. join(' OR ', map {
} else {
- my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param;
-
- my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
- while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
- $loc_sql =~ s/\?/$ph{shift(@param)}/e;
+ my @loc_where;
+ foreach (@loc_param) {
+ if ( length($cgi->param($_)) ) {
+ my $quoted = dbh->quote($cgi->param($_));
+ push @loc_where, "(COALESCE(cust_location.$_, '') = $quoted)";
+ }
}
+ my $loc_sql = join(' AND ', @loc_where);
- push @where, $loc_sql;
+ #my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param;
+ #
+ #my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
+ #while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
+ # $loc_sql =~ s/\?/$ph{shift(@param)}/e;
+ #}
+ push @where, $loc_sql;
+warn $loc_sql;
}
my($title, $name);
<THEAD>
<TR>
<TH ROWSPAN=3></TH>
- <TH COLSPAN=5>Sales</TH>
+ <TH COLSPAN=6>Sales</TH>
<TH ROWSPAN=3></TH>
<TH ROWSPAN=3>Rate</TH>
<TH ROWSPAN=3></TH>
<TH ROWSPAN=1>Non-taxable</TH>
<TH ROWSPAN=1>Non-taxable</TH>
<TH ROWSPAN=1>Non-taxable</TH>
+ <TH ROWSPAN=2>Credited</TH>
<TH ROWSPAN=2>Taxable</TH>
</TR>
% } # if $row->{pkgclass} ne ...
% # construct base links that limit to the tax rates described by this row
+% # cust_bill_pkg.cgi wants a list of specific taxnums (and package class)
+% # cust_credit_bill_pkg.html wants a geographic scope (and package class)
% my $rowlink = ';taxnum=' . $row->{taxnums};
+% my $rowregion = '';
+% foreach my $loc (qw(state county city district)) {
+% if ( $row->{$loc} ) {
+% $rowregion .= ";$loc=" . uri_escape($row->{$loc});
+% }
+% }
% # and also the package class, if we're limiting package class
% if ( $params{breakdown}->{pkgclass} ) {
% $rowlink .= ';classnum=' . ($row->{pkgclass} || 0);
+% $rowregion .= ';classnum=' . ($row->{pkgclass} || 0);
% }
%
% if ( $row->{total} ) {
<% $money_sprintf->( $row->{exempt_monthly} ) %>
</A>
</TD>
+% # credited sales
+ <TD>
+ <A HREF="<% $salescreditlink . $rowregion %>">
+ <% $money_sprintf->( $row->{sales_credited} ) %>
+ </A>
+ </TD>
+ <TD CLASS="bigmath"> × </TD>
+ <TD><% $row->{rate} %></TD>
% # taxable sales
<TD>
<A HREF="<% $saleslink . $rowlink . ";taxable=1" %>">
<% $money_sprintf->( $row->{taxable} ) %>
</A>
</TD>
- <TD CLASS="bigmath"> × </TD>
- <TD><% $row->{rate} %></TD>
% # estimated tax
<TD CLASS="bigmath"> = </TD>
<TD>
<TD CLASS="bigmath"> − </TD>
<TD>
%# <A HREF="<% $creditlink . $rowlink %>"> currently broken
- <% $money_sprintf->( $row->{credit} ) %>
+ <% $money_sprintf->( $row->{tax_credited} ) %>
%# </A>
</TD>
% # net tax due
<TD CLASS="bigmath"> = </TD>
- <TD><% $money_sprintf->( $row->{tax} - $row->{credit} ) %></TD>
+ <TD><% $money_sprintf->( $row->{tax} - $row->{tax_credited} ) %></TD>
% # tax collected
<TD> </TD>
<TD><% $money_sprintf->( $row->{tax_paid} ) %></TD>
my $saleslink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;nottax=1";
my $taxlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;istax=1";
my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink";
+my $salescreditlink = $p. "search/cust_credit_bill_pkg.html?$dateagentlink;nottax=1";
+if ( $params{'credit_date'} eq 'cust_credit_bill' ) {
+ $salescreditlink =~ s/begin/credit_begin/;
+ $salescreditlink =~ s/end/credit_end/;
+}
#my $creditlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;credit=1;istax=1";
#if ( $params{'credit_date'} eq 'cust_credit_bill' ) {
# $creditlink =~ s/begin/credit_begin/;
projects / freeside.git / commitdiff