-<% include( 'elements/search.html',
- 'title' => 'Tax credits', #well, actually application of
- 'name' => 'tax credits', # credit to line item
- 'query' => $query,
- 'count_query' => $count_query,
- 'count_addl' => [ $money_char. '%.2f total', ],
- 'header' => [
+<& elements/search.html,
+ 'title' => 'Credit application detail', #to line item
+ 'name_singular' => 'credit application',
+ 'query' => $query,
+ 'count_query' => $count_query,
+ 'count_addl' => \@count_addl,
+ 'header' => [
#'#',
'Amount',
+ 'Tax exempt',
#credit
'Date',
'By',
'Reason',
+ 'Info',
# line item
'Description',
+ 'Location',
+ @post_desc_header,
#invoice
'Invoice',
'Date',
FS::UI::Web::cust_header(),
- ],
- 'fields' => [
+ ],
+ 'fields' => [
#'creditbillpkgnum',
- sub { sprintf($money_char.'%.2f', shift->amount ) },
+ sub { sprintf($money_char.'%.2f', shift->amount ) },
+
+ sub { sprintf($money_char.'%.2f', shift->get('exempt_credited') ) },
sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) },
sub { shift->cust_credit_bill->cust_credit->otaker },
- sub { shift->cust_credit_bill->cust_credit->reason },
+ sub { shift->cust_credit_bill->cust_credit->reason_only },
+ sub { shift->cust_credit_bill->cust_credit->addlinfo },
sub { $_[0]->pkgnum > 0
? $_[0]->get('pkg') # possibly use override.pkg
: $_[0]->get('itemdesc') # but i think this correct
},
+ $location_sub,
+ @post_desc,
'invnum',
sub { time2str('%b %d %Y', shift->_date ) },
\&FS::UI::Web::cust_fields,
- ],
- 'sort_fields' => [
+ ],
+ 'sort_fields' => [
'amount',
+ 'exempt_credited',
'cust_credit_date',
'', #'otaker',
'', #reason
+ '', #addlinfo
'', #line item description
+ '', #location
+ @post_desc_null,
'invnum',
'_date',
#cust fields
- ],
- 'links' => [
+ ],
+ 'links' => [
+ '',
+ '',
+ '',
'',
'',
'',
'',
'',
+ @post_desc_null,
$ilink,
$ilink,
( map { $_ ne 'Cust. Status' ? $clink : '' }
FS::UI::Web::cust_header()
),
- ],
- 'align' => 'rrlllrr'.FS::UI::Web::cust_aligns(),
- 'color' => [
+ ],
+ 'align' => 'rrrlllll'.
+ $post_desc_align.
+ 'rr'.
+ FS::UI::Web::cust_aligns(),
+ 'color' => [
'',
'',
'',
'',
'',
'',
+ '',
+ @post_desc_null,
+ '',
+ '',
FS::UI::Web::cust_colors(),
],
- 'style' => [
+ 'style' => [
+ '',
'',
'',
'',
'',
'',
'',
+ @post_desc_null,
+ '',
+ '',
FS::UI::Web::cust_styles(),
- ],
- )
-%>
+ ],
+
+&>
<%init>
#LOTS of false laziness below w/cust_bill_pkg.cgi
+# and a little w/cust_credit.html
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
my @where = ( $agentnums_sql );
+if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
+ push @where, "cust_credit.usernum = $1";
+}
+
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
push @where, "cust_bill._date >= $beginning",
"cust_bill._date <= $ending";
+my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit');
+push @where, "cust_credit._date >= $cr_begin",
+ "cust_credit._date <= $cr_end";
+
+#credit amount? seems more what is expected than the applied amount
+my @lt_gt = FS::UI::Web::parse_lt_gt($cgi, 'amount' );
+s/amount/cust_credit.amount/g foreach (@lt_gt);
+push @where, @lt_gt;
+
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
push @where, "cust_main.agentnum = $1";
}
+if ( $cgi->param('billpkgnum') =~ /^(\d+)$/ ) {
+ push @where, "billpkgnum = $1";
+}
+
#classnum
# not specified: all classes
# 0: empty class
}
-my @loc_param = qw( city county state country );
+my @loc_param = qw( district city county state country );
if ( $cgi->param('out') ) {
- 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') ) {
#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 {
my %ph = ( 'county' => dbh->quote($_),
map { $_ => dbh->quote( $cgi->param($_) ) }
- qw( city state country )
+ qw( district city state country )
);
my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
} 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;
}
my($title, $name);
}
-push @where, 'cust_bill_pkg.pkgnum != 0' if $cgi->param('nottax');
+push @where, '(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart is not null)' if $cgi->param('nottax');
push @where, 'cust_bill_pkg.pkgnum = 0' if $cgi->param('istax');
if ( $cgi->param('cust_tax') ) {
push @where, $cust_exempt;
}
-my $count_query = "SELECT COUNT(DISTINCT billpkgnum),
+my $count_query = "SELECT COUNT(DISTINCT creditbillpkgnum),
SUM(cust_credit_bill_pkg.amount)";
+if ( $cgi->param('nottax') ) {
+ $count_query .= ", SUM(exempt_credited)";
+}
my $join_cust =
- ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )
- LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum ) ';
+ ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )'.
+ FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
my $join_pkg;
if ( $cgi->param('nottax') ) {
+ # There can be multiple cust_tax_exempt_pkg records with the same
+ # creditbillpkgnum iff the line item is exempt from multiple taxes.
+ # They will all have the same amount, except in the case where there are
+ # different exemption types and so the exemption amounts are different.
+ # In that case, show the amount of the largest exemption.
+
+ $join_cust_bill_pkg .= '
+ LEFT JOIN(
+ SELECT creditbillpkgnum,
+ MAX(0 - cust_tax_exempt_pkg.amount) AS exempt_credited
+ FROM cust_tax_exempt_pkg
+ WHERE creditbillpkgnum IS NOT NULL
+ GROUP BY creditbillpkgnum
+ ) AS exempt_credit USING (creditbillpkgnum)
+ ';
$join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum )
LEFT JOIN part_pkg USING ( pkgpart )
LEFT JOIN part_pkg AS override
push @where, "billpkgtaxratelocationnum IS NULL";
}
+ $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) ';
+
$join_pkg .= ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) ';
} elsif ( $conf->exists('tax-pkg_address') ) {
s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where;
}
-} else {
+} else {
- #die?
- warn "neiether nottax nor istax parameters specified";
+ #warn "neither nottax nor istax parameters specified";
#same as before?
$join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum )
LEFT JOIN part_pkg USING ( pkgpart ) ';
LEFT JOIN cust_credit USING ( crednum ) ';
$count_query .= " FROM cust_credit_bill_pkg
- $join_pkg
$join_cust_bill_pkg
+ $join_pkg
$join_credit
$join_cust
$where";
push @select, 'cust_main.custnum',
FS::UI::Web::cust_sql_fields();
+if ( $cgi->param('istax') ) {
+ push @select, 'NULL AS exempt_credited'; # just display zero
+} elsif ( $cgi->param('nottax') ) {
+ push @select, 'exempt_credited';
+}
+
+my @post_desc_header = ();
+my @post_desc = ();
+my @post_desc_null = ();
+my $post_desc_align = '';
+if ( $conf->exists('enable_taxclasses') && ! $cgi->param('istax') ) {
+ push @post_desc_header, 'Tax class';
+ push @post_desc, 'taxclass';
+ push @post_desc_null, '';
+ $post_desc_align .= 'l';
+ push @select, 'part_pkg.taxclass'; # or should this use override?
+}
+
my $query = {
'table' => 'cust_credit_bill_pkg',
- 'addl_from' => "$join_pkg
- $join_cust_bill_pkg
+ 'addl_from' => "$join_cust_bill_pkg
+ $join_pkg
$join_credit
$join_cust",
'hashref' => {},
my $conf = new FS::Conf;
my $money_char = $conf->config('money_char') || '$';
+my $tax_pkg_address = $conf->exists('tax-pkg_address');
+my $tax_ship_address = $conf->exists('tax-ship_address');
+
+my $location_sub = sub {
+ #my $cust_credit_bill_pkg = shift;
+ my $self = shift;
+ my $tax_Xlocation = $self->cust_bill_pkg_tax_Xlocation;
+ if ( defined($tax_Xlocation) && $tax_Xlocation ) {
+
+ if ( ref($tax_Xlocation) eq 'FS::cust_bill_pkg_tax_location' ) {
+
+ if ( $tax_Xlocation->taxtype eq 'FS::cust_main_county' ) {
+ my $cust_main_county = $tax_Xlocation->cust_main_county;
+ if ( $cust_main_county ) {
+ $cust_main_county->label;
+ } else {
+ ''; #cust_main_county record is gone... history? yuck.
+ }
+ } else {
+ '(CCH tax_rate)'; #XXX FS::tax_rate.. vendor taxes not yet handled here
+ }
+
+ } elsif ( ref($tax_Xlocation) eq 'FS::cust_bill_pkg_tax_rate_location' ) {
+ '(CCH)'; #XXX vendor taxes not yet handled here
+ } else {
+ 'unknown tax_Xlocation '. ref($tax_Xlocation);
+ }
+
+ } else {
+
+ my $cust_bill_pkg = $self->cust_bill_pkg;
+ if ( $cust_bill_pkg->pkgnum > 0 ) {
+ my $cust_pkg = $cust_bill_pkg->cust_pkg;
+ if ( $tax_pkg_address && (my $cust_location = $cust_pkg->cust_location) ){
+ $cust_location->county_state_country;
+ } else {
+ my $cust_main = $cust_pkg->cust_main;
+ if ( $tax_ship_address && $cust_main->has_ship_address ) {
+ $cust_main->county_state_country('ship_');
+ } else {
+ $cust_main->county_state_country;
+ }
+ }
+
+ } else {
+ #tax? we shouldn't have wound up here then...
+ ''; #return customer ship or bill address? (depending on tax-ship_address)
+ }
+
+ }
+
+};
+
+my @count_addl = ( $money_char. '%.2f total', );
+if ( $cgi->param('nottax') ) {
+ push @count_addl, ( $money_char. '%.2f tax exempt' );
+}
+
+if ( $cgi->param('debug') ) {
+ warn "\nQUERY:\n" . Dumper($query) . "\nCOUNT_QUERY:\n$count_query\n\n";
+}
+
</%init>
projects / freeside.git / blobdiff