X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill_pkg.cgi;h=0c5fbd211b2795da507e40aefd000ccf90be75d3;hb=65acf1fd9746f1dd98e061044657c185367f7903;hp=fd8b04da96afbc66d5e01ddca57c5324df447bed;hpb=ce1fa204e25ae8605bdb4f3dbfdf9bd1bff1a590;p=freeside.git diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index fd8b04da9..0c5fbd211 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -1,497 +1,592 @@ -<% include( 'elements/search.html', - 'title' => 'Line items', - 'name' => 'line items', +<& elements/search.html, + 'title' => emt('Line items'), + 'name' => emt('line items'), 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], + 'count_addl' => \@total_desc, 'header' => [ - '#', - 'Description', - 'Setup charge', - ( $use_usage eq 'usage' - ? 'Usage charge' - : 'Recurring charge' - ), - 'Invoice', - 'Date', + emt('Description'), + @peritem_desc, + emt('Invoice'), + emt('Date'), FS::UI::Web::cust_header(), ], 'fields' => [ - 'billpkgnum', sub { $_[0]->pkgnum > 0 ? $_[0]->get('pkg') # possibly use override.pkg : $_[0]->get('itemdesc') # but i think this correct }, #strikethrough or "N/A ($amount)" or something these when # they're not applicable to pkg_tax search - sub { sprintf($money_char.'%.2f', shift->setup ) }, - sub { my $row = shift; - my $value = 0; - if ( $use_usage eq 'recurring' ) { - $value = $row->recur - $row->usage; - } elsif ( $use_usage eq 'usage' ) { - $value = $row->usage; - } else { - $value = $row->recur; - } - sprintf($money_char.'%.2f', $value ); - }, + @peritem_sub, 'invnum', sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, ], - 'links' => [ - '', - '', + 'sort_fields' => [ '', + @peritem, + 'invnum', + '_date', + ], + 'links' => [ + #'', '', + @peritem_null, $ilink, $ilink, ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header() ), ], - 'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), + #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), + 'align' => 'l'. + $peritem_align. + 'rc'. + FS::UI::Web::cust_aligns(), 'color' => [ + #'', '', - '', - '', - '', + @peritem_null, '', '', FS::UI::Web::cust_colors(), ], 'style' => [ + #'', '', - '', - '', - '', + @peritem_null, '', '', FS::UI::Web::cust_styles(), ], - ) -%> +&> +<%doc> + +Output parameters: +- distribute: Boolean. If true, recurring fees will be "prorated" for the + portion of the package date range (sdate-edate) that falls within the date + range of the report. Line items will be limited to those for which this + portion is > 0. This disables filtering on invoice date. + +- use_usage: Separate usage (cust_bill_pkg_detail records) from + recurring charges. If set to "usage", will show usage instead of + recurring charges. If set to "recurring", will deduct usage and only + show the flat rate charge. If not passed, the "recurring charge" column + will include usage charges also. + +Filtering parameters: +- begin, end: Date range. Applies to invoice date, not necessarily package + date range. But see "distribute". + +- status: Customer status (active, suspended, etc.). This will filter on + _current_ customer status, not status at the time the invoice was generated. + +- agentnum: Filter on customer agent. + +- refnum: Filter on customer reference source. + +- classnum: Filter on package class. + +- use_override: Apply "classnum" and "taxclass" filtering based on the + override (bundle) pkgpart, rather than always using the true pkgpart. + +- nottax: Limit to items that are not taxes (pkgnum > 0). + +- istax: Limit to items that are taxes (pkgnum == 0). + +- taxnum: Limit to items whose tax definition matches this taxnum. + With "nottax" that means items that are subject to that tax; + with "istax" it's the tax charges themselves. Can be specified + more than once to include multiple taxes. + +- country, state, county, city: Limit to items whose tax location + matches these fields. If "nottax" it's the tax location of the package; + if "istax" the location of the tax. + +- taxname, taxnameNULL: With "nottax", limit to items whose tax location + matches a tax with this name. With "istax", limit to items that have + this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname + = 'Tax'". + +- out: With "nottax", limit to items that don't match any tax definition. + With "istax", find tax items that are unlinked to their tax definitions. + Current Freeside (> July 2012) always creates tax links, but unlinked + items may result from an incomplete upgrade of legacy data. + +- locationtaxid: With "nottax", limit to packages matching this + tax_rate_location ID; with "tax", limit to taxes generated from that + location. + +- taxclass: Filter on package taxclass. + +- taxclassNULL: With "nottax", limit to items that would be subject to the + tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass + is NULL; it also includes taxclasses that don't have a tax in this region. + +- itemdesc: Limit to line items with this description. Note that non-tax + packages usually have a description of NULL. (Deprecated.) + +- report_group: Can contain '=' or '!=' followed by a string to limit to + line items where itemdesc starts with, or doesn't start with, the string. + +- cust_tax: Limit to customers who are tax-exempt. If "taxname" is also + specified, limit to customers who are also specifically exempt from that + tax. + +- pkg_tax: Limit to packages that are tax-exempt, and only include the + exempt portion (setup, recurring, or both) when calculating totals. + +- taxable: Limit to packages that are subject to tax, i.e. where a + cust_bill_pkg_tax_location record exists. + +- credit: Limit to line items that received a credit application. The + amount of the credit will also be shown. + + <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; -#here is the agent virtualization +my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' ); +my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)'); +my @total_desc = ( '%d line items', $money_char.'%.2f total' ); # sprintf strings +my @peritem = ( 'setup', 'recur' ); +my @peritem_desc = ( 'Setup charge', 'Recurring charge' ); +my ($join_cust, $join_pkg ) = ('', ''); +my $use_usage; + +# valid in both the tax and non-tax cases +$join_cust = + " LEFT JOIN cust_bill USING (invnum) + LEFT JOIN cust_main USING (custnum) + "; + +#agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' ); my @where = ( $agentnums_sql ); +# date range my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -push @where, "_date >= $beginning", - "_date <= $ending"; -push @where , " payby != 'COMP' " - unless $cgi->param('include_comp_cust'); +if ( $cgi->param('distribute') == 1 ) { + push @where, "sdate <= $ending", + "edate > $beginning", + ; +} +else { + push @where, "cust_bill._date >= $beginning", + "cust_bill._date <= $ending"; +} +# status +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + push @where, FS::cust_main->cust_status_sql . " = '$1'"; +} + +# agentnum if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } -#classnum -# not specified: all classes -# 0: empty class -# N: classnum -my $use_override = $cgi->param('use_override'); -if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { - my $comparison = ''; - if ( $1 == 0 ) { - $comparison = "IS NULL"; - } else { - $comparison = "= $1"; - } - - if ( $use_override ) { - push @where, "( - part_pkg.classnum $comparison AND pkgpart_override IS NULL OR - override.classnum $comparison AND pkgpart_override IS NOT NULL - )"; - } else { - push @where, "part_pkg.classnum $comparison"; - } +# refnum +if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { + push @where, "cust_main.refnum = $1"; } -if ( $cgi->param('taxclass') - && ! $cgi->param('istax') #no part_pkg.taxclass in this case - #(should we save a taxclass or a link to taxnum - # in cust_bill_pkg or something like - # cust_bill_pkg_tax_location?) - ) -{ - - #override taxclass when use_override is specified? probably - #if ( $use_override ) { - # - # push @where, - # ' ( '. join(' OR ', - # map { - # ' ( part_pkg.taxclass = '. dbh->quote($_). - # ' AND pkgpart_override IS NULL '. - # ' OR '. - # ' override.taxclass = '. dbh->quote($_). - # ' AND pkgpart_override IS NOT NULL '. - # ' ) ' - # } - # $cgi->param('taxclass') - # ). - # ' ) '; - # - #} else { - - push @where, - ' ( '. join(' OR ', - map ' part_pkg.taxclass = '.dbh->quote($_), - $cgi->param('taxclass') - ). - ' ) '; - - #} +# custnum +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + push @where, "cust_main.custnum = $1"; +} +# then we want the package and its definition +$join_pkg = +' LEFT JOIN cust_pkg USING (pkgnum) + LEFT JOIN part_pkg USING (pkgpart)'; + +my $part_pkg = 'part_pkg'; +if ( $cgi->param('use_override') ) { + # still need the real part_pkg for tax applicability, + # so alias this one + $join_pkg .= " LEFT JOIN part_pkg AS override ON ( + COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart + )"; + $part_pkg = 'override'; } +push @select, 'part_pkg.pkg'; # or should this use override? -my @loc_param = qw( city county state country ); +# the non-tax case +if ( $cgi->param('nottax') ) { -if ( $cgi->param('out') ) { + push @where, 'cust_bill_pkg.pkgnum > 0'; - 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 @tax_where; # will go into a subquery + my @exempt_where; # will also go into a subquery - $loc_sql =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g - if $cgi->param('istax'); + # classnum (of override pkgpart if applicable) + # not specified: all classes + # 0: empty class + # N: classnum + if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { + push @where, "COALESCE($part_pkg.classnum, 0) = $1"; + } - push @where, " - 0 = ( - SELECT COUNT(*) FROM cust_main_county - WHERE cust_main_county.tax > 0 - AND $loc_sql - ) - "; + # taxclass + if ( $cgi->param('taxclassNULL') ) { + # a little different from 'taxclass' in that it applies to the + # effective taxclass, not the real one + push @tax_where, 'cust_main_county.taxclass IS NULL' + } elsif ( $cgi->param('taxclass') ) { + push @tax_where, "$part_pkg.taxclass IN (" . + join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ). + ')'; + } - #not linked to by anything, but useful for debugging "out of taxable region" - if ( grep $cgi->param($_), @loc_param ) { + if ( $cgi->param('exempt_cust') eq 'Y' ) { + # tax-exempt customers + push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')"; - my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param; + } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package + # non-taxable package charges + push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')"; + } + # we don't handle exempt_monthly here + + if ( $cgi->param('taxname') ) { # specific taxname + push @tax_where, 'cust_main_county.taxname = '. + dbh->quote($cgi->param('taxname')); + } elsif ( $cgi->param('taxnameNULL') ) { + push @tax_where, 'cust_main_county.taxname IS NULL OR '. + 'cust_main_county.taxname = \'Tax\''; + } - 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; + # country:state:county:city:district (may be repeated) + # You can also pass a big list of taxnums but that leads to huge URLs. + # Note that this means "packages whose tax is in this region", not + # "packages in this region". It's meant for links from the tax report. + if ( $cgi->param('region') ) { + my @orwhere; + foreach ( $cgi->param('region') ) { + my %loc; + @loc{qw(country state county city district)} = + split(':', $cgi->param('region')); + my $string = join(' AND ', + map { + if ( $loc{$_} ) { + "$_ = ".dbh->quote($loc{$_}); + } else { + "$_ IS NULL"; + } + } keys(%loc) + ); + push @orwhere, "($string)"; } + push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere; + } - push @where, $loc_sql; - + # specific taxnums + if ( $cgi->param('taxnum') ) { + my $taxnum_in = join(',', + grep /^\d+$/, $cgi->param('taxnum') + ); + push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)" + if $taxnum_in; } -} elsif ( $cgi->param('country') ) { + # If we're showing exempt items, we need to find those with + # cust_tax_exempt_pkg records matching the selected taxes. + # If we're showing taxable items, we need to find those with + # cust_bill_pkg_tax_location records. We also need to find the + # exemption records so that we can show the taxable amount. + # If we're showing all items, we need the union of those. + # If we're showing 'out' (items that aren't region/class taxable), + # then we need the set of all items minus the union of those. - my @counties = $cgi->param('county'); - - if ( scalar(@counties) > 1 ) { + my $exempt_sub; - #hacky, could be more efficient. care if it is ever used for more than the - # tax-report_groups filtering kludge + if ( @exempt_where or @tax_where + or $cgi->param('taxable') or $cgi->param('out') ) + { + # process exemption restrictions, including @tax_where + my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum + FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)'; - my $locs_sql = - ' ( '. join(' OR ', map { + $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) + if (@tax_where or @exempt_where); - my %ph = ( 'county' => dbh->quote($_), - map { $_ => dbh->quote( $cgi->param($_) ) } - qw( city state country ) - ); + $exempt_sub .= ' GROUP BY billpkgnum'; - 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; - } + $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt + USING (billpkgnum)"; + } + + if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) { + # process tax restrictions + unshift @tax_where, + 'cust_main_county.tax > 0'; + + my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum + FROM cust_bill_pkg_tax_location + JOIN cust_bill_pkg AS tax_item USING (billpkgnum) + JOIN cust_main_county USING (taxnum) + WHERE ". join(' AND ', @tax_where). + " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum"; + + $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax + ON (item_tax.invnum = cust_bill_pkg.invnum AND + item_tax.pkgnum = cust_bill_pkg.pkgnum)"; + } - $loc_sql; + # now do something with that + if ( @exempt_where ) { - } @counties + push @where, 'item_exempt.billpkgnum IS NOT NULL'; + push @select, 'item_exempt.exempt_amount'; + push @peritem, 'exempt_amount'; + push @peritem_desc, 'Exempt'; + push @total, 'SUM(exempt_amount)'; + push @total_desc, "$money_char%.2f tax-exempt"; - ). ' ) '; + } elsif ( $cgi->param('taxable') ) { - push @where, $locs_sql; + my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. + '- COALESCE(item_exempt.exempt_amount, 0)'; - } else { + push @where, 'item_tax.invnum IS NOT NULL'; + push @select, "($taxable) AS taxable_amount"; + push @peritem, 'taxable_amount'; + push @peritem_desc, 'Taxable'; + push @total, "SUM($taxable)"; + push @total_desc, "$money_char%.2f taxable"; - my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param; + } elsif ( $cgi->param('out') ) { + + push @where, 'item_tax.invnum IS NULL', + 'item_exempt.billpkgnum IS NULL'; - 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; - } + } elsif ( @tax_where ) { - push @where, $loc_sql; + # union of taxable + all exempt_ cases + push @where, + '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)'; } - - if ( $cgi->param('istax') ) { - if ( $cgi->param('taxname') ) { - push @where, 'itemdesc = '. dbh->quote( $cgi->param('taxname') ); - #} elsif ( $cgi->param('taxnameNULL') { - } else { - push @where, "( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - } - } elsif ( $cgi->param('nottax') ) { - #what can we usefully do with "taxname" ???? look up a class??? - } else { - #warn "neither nottax nor istax parameters specified"; - } - if ( $cgi->param('taxclassNULL') ) { + # recur/usage separation + $use_usage = $cgi->param('usage'); + if ( $use_usage eq 'recurring' ) { - my %hash = ( 'country' => scalar($cgi->param('country')) ); - foreach (qw( state county )) { - $hash{$_} = scalar($cgi->param($_)) if $cgi->param($_); - } - my $cust_main_county = qsearchs('cust_main_county', \%hash); - die "unknown base region for empty taxclass" unless $cust_main_county; + my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1); + push @select, "($recur_no_usage) AS recur_no_usage"; + $peritem[1] = 'recur_no_usage'; + $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)"; + $total_desc[1] .= ' (excluding usage)'; - my $same_sql = $cust_main_county->sql_taxclass_sameregion; - push @where, $same_sql if $same_sql; + } elsif ( $use_usage eq 'usage' ) { + my $usage = FS::cust_bill_pkg->usage_sql(); + push @select, "($usage) AS _usage"; + # there's already a method named 'usage' + $peritem[1] = '_usage'; + $peritem_desc[1] = 'Usage charge'; + $total[1] = "SUM($usage)"; + $total_desc[1] .= ' usage charges'; } -} elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { - - # this should really be shoved out to FS::cust_pkg->location_sql or something - # along with the code in report_newtax.cgi - - my %pn = ( - 'county' => 'tax_rate_location.county', - 'state' => 'tax_rate_location.state', - 'city' => 'tax_rate_location.city', - 'locationtaxid' => 'cust_bill_pkg_tax_rate_location.locationtaxid', - ); +} elsif ( $cgi->param('istax') ) { - my %ph = map { ( $pn{$_} => dbh->quote( $cgi->param($_) || '' ) ) } - qw( city county state locationtaxid ); + @peritem = ( 'setup' ); # taxes only have setup + @peritem_desc = ( 'Tax charge' ); - push @where, - join( ' AND ', map { "( $_ = $ph{$_} OR $ph{$_} = '' AND $_ IS NULL)" } - keys %ph - ); + push @where, 'cust_bill_pkg.pkgnum = 0'; -} + # tax location when using tax_rate_location + if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { -if ( $cgi->param('itemdesc') ) { - if ( $cgi->param('itemdesc') eq 'Tax' ) { - push @where, "(itemdesc='Tax' OR itemdesc is null)"; - } else { - push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc')); - } -} + $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. + ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )'; + push @where, FS::tax_rate_location->location_sql( + map { $_ => (scalar($cgi->param($_)) || '') } + qw( district city county state locationtaxid ) + ); -if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ && $cgi->param('istax') ) { - my ( $group_op, $group_value ) = ( $1, $2 ); - if ( $group_op eq '=' ) { - #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%'); - push @where, 'itemdesc = '. dbh->quote($group_value); - } elsif ( $group_op eq '!=' ) { - push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )'; - } else { - die "guru meditation #00de: group_op $group_op\n"; - } - -} + $total[1] = 'SUM( + COALESCE(cust_bill_pkg_tax_rate_location.amount, + cust_bill_pkg.setup + cust_bill_pkg.recur) + )'; -push @where, 'cust_bill_pkg.pkgnum != 0' if $cgi->param('nottax'); -push @where, 'cust_bill_pkg.pkgnum = 0' if $cgi->param('istax'); - -if ( $cgi->param('cust_tax') ) { - #false laziness -ish w/report_tax.cgi - my $cust_exempt; - if ( $cgi->param('taxname') ) { - my $q_taxname = dbh->quote($cgi->param('taxname')); - $cust_exempt = - "( tax = 'Y' - OR EXISTS ( SELECT 1 FROM cust_main_exemption - WHERE cust_main_exemption.custnum = cust_main.custnum - AND cust_main_exemption.taxname = $q_taxname ) - ) - "; - } else { - $cust_exempt = " tax = 'Y' "; - } + } elsif ( $cgi->param('out') ) { - push @where, $cust_exempt; -} + $join_pkg = ' + LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) + '; + push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL'; -my $use_usage = $cgi->param('use_usage'); - -my $count_query; -if ( $cgi->param('pkg_tax') ) { - - $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') ) { - - my $setup_taxable = "( - CASE WHEN part_pkg.setuptax = 'Y' - THEN 0 - ELSE cust_bill_pkg.setup - END - )"; + # each billpkgnum should appear only once + $total[0] = 'COUNT(*)'; + $total[1] = 'SUM(cust_bill_pkg.setup)'; - my $recur_taxable = "( - CASE WHEN part_pkg.recurtax = 'Y' - THEN 0 - ELSE cust_bill_pkg.recur - END - )"; + } else { # not locationtaxid or 'out'--the normal case - my $exempt = "( - SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg - WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum - )"; + $join_pkg = ' + LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) + JOIN cust_main_county USING (taxnum) + '; - $count_query = - "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )"; + # don't double-count the components of consolidated taxes + $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)'; + $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)'; + } - 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 )", + # taxclass + if ( $cgi->param('taxclassNULL') ) { + push @where, 'cust_main_county.taxclass IS NULL'; + } -} else { + # taxname + if ( $cgi->param('taxnameNULL') ) { + push @where, 'cust_main_county.taxname IS NULL OR '. + 'cust_main_county.taxname = \'Tax\''; + } elsif ( $cgi->param('taxname') ) { + push @where, 'cust_main_county.taxname = '. + dbh->quote($cgi->param('taxname')); + } - $count_query = "SELECT COUNT(*), "; + # specific taxnums + if ( $cgi->param('taxnum') ) { + my $taxnum_in = join(',', + grep /^\d+$/, $cgi->param('taxnum') + ); + push @where, "cust_main_county.taxnum IN ($taxnum_in)" + if $taxnum_in; + } - if ( $use_usage eq 'recurring' ) { - $count_query .= "SUM(setup + recur - usage)"; - } elsif ( $use_usage eq 'usage' ) { - $count_query .= "SUM(usage)"; - } else { - $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; + # report group (itemdesc) + if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) { + my ( $group_op, $group_value ) = ( $1, $2 ); + if ( $group_op eq '=' ) { + #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%'); + push @where, 'itemdesc = '. dbh->quote($group_value); + } elsif ( $group_op eq '!=' ) { + push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )'; + } else { + die "guru meditation #00de: group_op $group_op\n"; + } } -} + # itemdesc, for some reason + if ( $cgi->param('itemdesc') ) { + if ( $cgi->param('itemdesc') eq 'Tax' ) { + push @where, "(itemdesc='Tax' OR itemdesc is null)"; + } else { + push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc')); + } + } -my $join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +} # nottax / istax +# credit +if ( $cgi->param('credit') ) { -my $join_pkg; -if ( $cgi->param('nottax') ) { + my $credit_sub; - $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN part_pkg AS override - ON pkgpart_override = override.pkgpart '; - $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) ' - if $conf->exists('tax-pkg_address'); + if ( $cgi->param('istax') ) { + # then we need to group/join by billpkgtaxlocationnum, to get only the + # relevant part of partial taxes + my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount, + reason.reason as reason_text, access_user.username AS username_text, + billpkgtaxlocationnum, billpkgnum + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_credit USING (crednum) + LEFT JOIN reason USING (reasonnum) + LEFT JOIN access_user USING (usernum) + GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason, + access_user.username"; + + if ( $cgi->param('out') ) { + + # find credits that are applied to the line items, but not to + # a cust_bill_pkg_tax_location link + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit + USING (billpkgnum)"; + push @where, 'item_credit.billpkgtaxlocationnum IS NULL'; -} elsif ( $cgi->param('istax') ) { + } else { - #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 ) ) || - $cgi->param('iscredit') eq 'rate') { - $join_pkg .= - ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. - ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; - } + # find credits that are applied to the CBPTL links that are + # considered "interesting" by the report criteria + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit + USING (billpkgtaxlocationnum)"; - if ( $cgi->param('iscredit') ) { - $join_pkg .= ' JOIN cust_credit_bill_pkg USING ( billpkgnum'; - if ( $conf->exists('tax-pkg_address') ) { - $join_pkg .= ', billpkgtaxlocationnum )'; - push @where, "billpkgtaxratelocationnum IS NULL"; - } elsif ( $cgi->param('iscredit') eq 'rate' ) { - $join_pkg .= ', billpkgtaxratelocationnum )'; - } else { - $join_pkg .= ' )'; - push @where, "billpkgtaxratelocationnum IS NULL"; } - } - -} else { - #die? - warn "neiether nottax nor istax parameters specified"; - #same as before? - $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) '; + } else { + # then only group by billpkgnum + my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount, + reason.reason as reason_text, access_user.username AS username_text, + billpkgnum + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_credit USING (crednum) + LEFT JOIN reason USING (reasonnum) + LEFT JOIN access_user USING (usernum) + GROUP BY billpkgnum, reason.reason, access_user.username"; + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)"; + } -} + push @where, 'item_credit.billpkgnum IS NOT NULL'; + push @select, 'item_credit.credit_amount', + 'item_credit.username_text', + 'item_credit.reason_text'; + push @peritem, 'credit_amount', 'username_text', 'reason_text'; + push @peritem_desc, 'Credited', 'By', 'Reason'; + push @total, 'SUM(credit_amount)'; + push @total_desc, "$money_char%.2f credited"; +} # if credit -my $where = ' WHERE '. join(' AND ', @where); - -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_bill_pkg $join_cust $join_pkg $where"; -} -warn "count_query is $count_query\n"; +push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); -my @select = ( - 'cust_bill_pkg.*', - 'cust_bill._date', - ); -push @select, 'part_pkg.pkg' unless $cgi->param('istax'); -push @select, 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(); +my $where = join(' AND ', @where); +$where &&= "WHERE $where"; my $query = { 'table' => 'cust_bill_pkg', 'addl_from' => "$join_cust $join_pkg", 'hashref' => {}, - 'select' => join(', ', @select ), + 'select' => join(",\n", @select ), 'extra_sql' => $where, - 'order_by' => 'ORDER BY _date, billpkgnum', + 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum', }; +my $count_query = + 'SELECT ' . join(',', @total) . + " FROM cust_bill_pkg $join_cust $join_pkg + $where"; + +shift @total_desc; #the first one is implicit + +@peritem_desc = map {emt($_)} @peritem_desc; +my @peritem_sub = map { + my $field = $_; + if ($field =~ /_text$/) { # kludge for credit reason/username fields + sub {$_[0]->get($field)}; + } else { + sub { sprintf($money_char.'%.2f', $_[0]->get($field)) } + } +} @peritem; +my @peritem_null = map { '' } @peritem; # placeholders +my $peritem_align = 'r' x scalar(@peritem); + my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - +warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n" + if $cgi->param('debug');