X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_credit_bill_pkg.html;h=4a14893e84e2b1fdabed5fce74304fec364b8194;hp=63d70c27e06498ad18eb2b8c927d1ca694fbac91;hb=1fc8addc56f8daf12397da568eb1ac1b27fd3984;hpb=3d5b5f472b76999daacc646f8d45dc42aba48bfd diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index 63d70c27e..4a14893e8 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -3,16 +3,18 @@ 'name_singular' => 'credit application', 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], + 'count_addl' => \@count_addl, 'header' => [ #'#', 'Amount', + 'Tax exempt', #credit 'Date', 'By', 'Reason', + 'Info', # line item 'Description', @@ -26,11 +28,14 @@ ], '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 @@ -44,9 +49,11 @@ ], 'sort_fields' => [ 'amount', + 'exempt_credited', 'cust_credit_date', '', #'otaker', '', #reason + '', #addlinfo '', #line item description '', #location @post_desc_null, @@ -61,6 +68,8 @@ '', '', '', + '', + '', @post_desc_null, $ilink, $ilink, @@ -68,11 +77,13 @@ FS::UI::Web::cust_header() ), ], - 'align' => 'rrllll'. + 'align' => 'rrrlllll'. $post_desc_align. 'rr'. FS::UI::Web::cust_aligns(), - 'color' => [ + 'color' => [ + '', + '', '', '', '', @@ -91,6 +102,8 @@ '', '', '', + '', + '', @post_desc_null, '', '', @@ -204,35 +217,33 @@ 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') ) { @@ -242,6 +253,8 @@ if ( $cgi->param('out') ) { #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 { @@ -266,15 +279,23 @@ if ( $cgi->param('out') ) { } 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); @@ -357,7 +378,7 @@ if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ && $cgi->param('istax') ) { } -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') ) { @@ -381,6 +402,9 @@ if ( $cgi->param('cust_tax') ) { 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 )'. @@ -393,6 +417,21 @@ my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum )'; 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 @@ -418,6 +457,8 @@ if ( $cgi->param('nottax') ) { 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') ) { @@ -460,6 +501,12 @@ push @select, 'part_pkg.pkg' unless $cgi->param('istax'); 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 = (); @@ -543,4 +590,13 @@ my $location_sub = sub { }; +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"; +} +