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;
}
}else{
$amount = sprintf("%.2f", $self->balance - $self->in_transit_payments);
}
- return '' unless $amount > 0;
+ if ($amount <= 0) {
+ warn(sprintf("Customer balance %.2f - in transit amount %.2f is <= 0.\n",
+ $self->balance,
+ $self->in_transit_payments
+ ));
+ return;
+ }
my $invnum = delete $options{invnum};
foreach my $cust_pay_batch ( qsearch('cust_pay_batch', {
'batchnum' => $pay_batch->batchnum,
'custnum' => $self->custnum,
+ 'status' => '',
} ) ) {
$in_transit_payments += $cust_pay_batch->amount;
}
return $e;
}
+ $cust_pay_pending->set('jobnum','');
+
}
if ( $options{'paynum_ref'} ) {
if ( $placeholder ) {
my $error = $placeholder->depended_delete;
$error ||= $placeholder->delete;
+ $cust_pay_pending->set('jobnum','');
warn "error removing provisioning jobs after declined paypendingnum ".
- $cust_pay_pending->paypendingnum. ": $error\n";
+ $cust_pay_pending->paypendingnum. ": $error\n" if $error;
} else {
my $e = "error finding job $jobnum for declined paypendingnum ".
$cust_pay_pending->paypendingnum. "\n";
warn $e;
return $e;
}
+
+ $self->set('jobnum','');
}
if ( $opt{'paynum_ref'} ) {
}
}
+ my $oldAutoCommit = $FS::UID::AutoCommit;
+ local $FS::UID::AutoCommit = 0;
+ my $dbh = dbh;
+
+ foreach my $cust_pay_pending (qsearch('cust_pay_pending',{ jobnum => $self->jobnum })) {
+ $cust_pay_pending->set('jobnum','');
+ my $error = $cust_pay_pending->replace();
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+ }
+
my $error = $self->SUPER::delete;
- return $error if $error;
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return $error;
+ }
+ $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+
unlink $reportname if $reportname;
'';
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>