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;
}
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
- $loc_sql =~ s/\?/'cust_main_county.'.shift(@param)/e;
+ my $has_taxname = '';
+ if ( $cgi->param('taxname') ) {
+ $has_taxname = " AND COALESCE(cust_main_county.taxname, 'Tax') = "
+ . dbh->quote( $cgi->param('taxname') );
}
- $loc_sql =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g
- if $cgi->param('istax');
-
- push @where, "
- 0 = (
- SELECT COUNT(*) FROM cust_main_county
- WHERE cust_main_county.tax > 0
- AND $loc_sql
- )
- ";
-
- #not linked to by anything, but useful for debugging "out of taxable region"
- if ( grep $cgi->param($_), @loc_param ) {
-
- 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;
-
- }
+ # use the exact criteria from the tax report
+ push @where,
+ "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_tax_exempt_pkg.creditbillpkgnum IS NULL
+ $has_taxname
+ )
+ 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
+ $has_taxname
+ )
+ 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
+ $has_taxname
+ )";
} elsif ( $cgi->param('country') ) {
}
# at the end of everything
-if ( $report->{outside} > 0 ) {
+if ( $report->{out_sales} > 0 ) {
my $f = $format[0];
$ws->set_row($y, 30); # height
$ws->write($y, 0, mt('Out of taxable region'), $f->{rowhead_outside});
- $ws->write($y, 1, $report->{outside}, $f->{currency_outside});
+ $ws->write($y, 1, $report->{out_sales}, $f->{currency_outside});
$y++;
}
$prev_row = $row;
}
+if ( $report->{out_credit} > 0 ) {
+ my $f = $format[0];
+ $ws->set_row($y, 30); # height
+ $ws->write($y, 0, mt('Out of taxable region'), $f->{rowhead_outside});
+ $ws->write($y, 1, $report->{out_credit}, $f->{currency_outside});
+ $y++;
+}
+
+
for my $x (0..4) {
$ws->set_column($x, $x, $widths[$x]);
}
% } # foreach my $row
% # at the end of everything
</TBODY>
-% if ( $report->{outside} > 0 ) {
+% if ( $report->{out_sales} > 0 ) {
<TBODY CLASS="total" STYLE="background-color: #cccccc; line-height: 3">
<TR>
<TD CLASS="rowhead">
</TD>
<TD STYLE="text-align: right">
<A HREF="<% $saleslink %>;out=1;taxname=<% encode_entities($params{'taxname'}) %>">
- <% $money_sprintf->( $report->{outside } ) %>
+ <% $money_sprintf->( $report->{out_sales } ) %>
</A>
</TD>
<TD COLSPAN=0></TD>
% $rownum++;
% $prev_row = $row;
% } # foreach my $row
-% # no "out of taxable region" for credits (yet)
+% # "out of taxable region" for credits (there is a need for it)
+% if ( $report->{out_credit} > 0 ) {
+% my $creditlink = "cust_credit_bill_pkg.html?out=1;$dateagentlink";
+% if ( $params{'credit_date'} eq 'cust_credit_bill' ) {
+% $creditlink =~ s/begin/credit_begin/;
+% $creditlink =~ s/end/credit_end/;
+% }
+% $creditlink .= ";taxname=" . encode_entities($params{'taxname'});
+ <TBODY CLASS="total" STYLE="background-color: #cccccc; line-height: 3">
+ <TR>
+ <TD CLASS="rowhead">
+ <% emt('Out of taxable region') %>
+ </TD>
+ <TD STYLE="text-align: right">
+ <A HREF="<% $creditlink %>">
+ <% $money_sprintf->( $report->{out_credit } ) %>
+ </A>
+ </TD>
+ <TD COLSPAN=0></TD>
+ </TR>
+ </TBODY>
+% }
</TBODY>
</TABLE>