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=0f78344468b3d861e404d069c4a1c5c2100e1c8e;hb=1fc8addc56f8daf12397da568eb1ac1b27fd3984;hpb=f3c6b6245a3ac0f32ff6d30b0315c009687fcffc diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index 0f7834446..4a14893e8 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -1,57 +1,88 @@ -<% 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') ) }, - 'otaker', - sub { shift->cust_credit_bill->cust_credit->reason }, + sub { shift->cust_credit_bill->cust_credit->otaker }, + 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, - ], - 'links' => [ + ], + 'sort_fields' => [ + 'amount', + 'exempt_credited', + 'cust_credit_date', + '', #'otaker', + '', #reason + '', #addlinfo + '', #line item description + '', #location + @post_desc_null, + 'invnum', + '_date', + #cust fields + ], + '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' => [ + '', '', '', '', @@ -59,23 +90,31 @@ '', '', '', + @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'); @@ -88,14 +127,31 @@ my $agentnums_sql = 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 @@ -157,39 +213,37 @@ if ( $cgi->param('taxclass') } -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') ) { @@ -199,13 +253,15 @@ 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 { 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; @@ -223,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); @@ -254,7 +318,10 @@ if ( $cgi->param('out') ) { #warn "neither nottax nor istax parameters specified"; } - if ( $cgi->param('taxclassNULL') ) { + if ( $cgi->param('taxclassNULL') + && ! $cgi->param('istax') #no taxclass in this case + ) + { my %hash = ( 'country' => scalar($cgi->param('country')) ); foreach (qw( state county )) { @@ -311,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') ) { @@ -333,95 +400,38 @@ if ( $cgi->param('cust_tax') ) { push @where, $cust_exempt; } -my $use_usage = $cgi->param('use_usage'); - -my $count_query; -if ( $cgi->param('pkg_tax') ) { #does this mean anything here? - - $count_query = - "SELECT COUNT(*), - SUM( - ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 - END - ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 - END - ) - ) - "; - - push @where, "( ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) )", - "( tax != 'Y' OR tax IS NULL )"; - -} elsif ( $cgi->param('taxable') ) { #again, meaningful? - - my $setup_taxable = "( - CASE WHEN part_pkg.setuptax = 'Y' - THEN 0 - ELSE cust_bill_pkg.setup - END - )"; - - my $recur_taxable = "( - CASE WHEN part_pkg.recurtax = 'Y' - THEN 0 - ELSE cust_bill_pkg.recur - END - )"; - - my $exempt = "( - SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg - WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum - )"; - - $count_query = - "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )"; - - push @where, - #not tax-exempt package (setup or recur) - "( - ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL ) - AND cust_bill_pkg.setup > 0 ) - OR - ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL ) - AND cust_bill_pkg.recur > 0 ) - )", - #not a tax_exempt customer - "( tax != 'Y' OR tax IS NULL )"; - #not covered in full by a monthly tax exemption (texas tax) - "0 < ( $setup_taxable + $recur_taxable - $exempt )", - -} else { - - $count_query = "SELECT COUNT(*), "; - - if ( $use_usage eq 'recurring' ) { #mean anything? - $count_query .= "SUM(setup + recur - usage)"; - } elsif ( $use_usage eq 'usage' ) { #mean anything? - $count_query .= "SUM(usage)"; - } else { - $count_query .= "SUM(cust_credit_bill_pkg.amount)"; - } - +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; -my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum '; +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 @@ -432,33 +442,37 @@ if ( $cgi->param('nottax') ) { } elsif ( $cgi->param('istax') ) { #false laziness w/report_tax.cgi $taxfromwhere - if ( $conf->exists('tax-pkg_address') ) { - $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) - LEFT JOIN cust_location USING ( locationnum ) '; - - #quelle kludge, somewhat false laziness w/report_tax.cgi - s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where; - } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) || + if ( scalar( grep( /locationtaxid/, $cgi->param ) ) || $cgi->param('iscredit') eq 'rate') { - $join_pkg .= - ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. - ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; - } - if ( $conf->exists('tax-pkg_address') ) { - $join_cust_bill_pkg .= ', billpkgtaxlocationnum )'; + $join_pkg .= + ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum '; + if ( $cgi->param('iscredit') eq 'rate' ) { + $join_pkg .= ', billpkgtaxratelocationnum )'; + } elsif ( $conf->exists('tax-pkg_address') ) { + $join_pkg .= ', billpkgtaxlocationnum )'; push @where, "billpkgtaxratelocationnum IS NULL"; - #} elsif ( $cgi->param('iscredit') eq 'rate' ) { - # $join_pkg .= ', billpkgtaxratelocationnum )'; } else { - $join_cust_bill_pkg .= ' )'; + $join_pkg .= ' )'; push @where, "billpkgtaxratelocationnum IS NULL"; } -} else { + $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) '; + + $join_pkg .= ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; + + } elsif ( $conf->exists('tax-pkg_address') ) { - #die? - warn "neiether nottax nor istax parameters specified"; + $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) + LEFT JOIN cust_location USING ( locationnum ) '; + + #quelle kludge, somewhat false laziness w/report_tax.cgi + s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where; + } + +} else { + + #warn "neither nottax nor istax parameters specified"; #same as before? $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) '; @@ -470,21 +484,12 @@ my $where = ' WHERE '. join(' AND ', @where); my $join_credit = ' LEFT JOIN cust_credit_bill USING ( creditbillnum ) LEFT JOIN cust_credit USING ( crednum ) '; -#if ($use_usage) { -# $count_query .= -# " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur, -# ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail -# WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum -# ) AS usage FROM cust_bill_pkg $join_cust $join_pkg $where -# ) AS countquery"; -#} else { - $count_query .= " FROM cust_credit_bill_pkg - $join_pkg - $join_cust_bill_pkg - $join_credit - $join_cust - $where"; -#} +$count_query .= " FROM cust_credit_bill_pkg + $join_cust_bill_pkg + $join_pkg + $join_credit + $join_cust + $where"; my @select = ( 'cust_credit_bill_pkg.*', 'cust_bill_pkg.*', @@ -496,10 +501,28 @@ 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 = (); +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' => {}, @@ -514,4 +537,66 @@ my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; 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"; +} +