1 <& elements/search.html,
2 'title' => emt('Line items'),
3 'name' => emt('line items'),
5 'count_query' => $count_query,
6 'count_addl' => \@total_desc,
12 FS::UI::Web::cust_header(),
15 sub { $_[0]->pkgnum > 0
16 ? $_[0]->get('pkg') # possibly use override.pkg
17 : $_[0]->get('itemdesc') # but i think this correct
19 #strikethrough or "N/A ($amount)" or something these when
20 # they're not applicable to pkg_tax search
23 sub { time2str('%b %d %Y', shift->_date ) },
24 \&FS::UI::Web::cust_fields,
38 ( map { $_ ne 'Cust. Status' ? $clink : '' }
39 FS::UI::Web::cust_header()
42 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
46 FS::UI::Web::cust_aligns(),
53 FS::UI::Web::cust_colors(),
61 FS::UI::Web::cust_styles(),
67 - distribute: Boolean. If true, recurring fees will be "prorated" for the
68 portion of the package date range (sdate-edate) that falls within the date
69 range of the report. Line items will be limited to those for which this
70 portion is > 0. This disables filtering on invoice date.
72 - use_usage: Separate usage (cust_bill_pkg_detail records) from
73 recurring charges. If set to "usage", will show usage instead of
74 recurring charges. If set to "recurring", will deduct usage and only
75 show the flat rate charge. If not passed, the "recurring charge" column
76 will include usage charges also.
79 - begin, end: Date range. Applies to invoice date, not necessarily package
80 date range. But see "distribute".
82 - status: Customer status (active, suspended, etc.). This will filter on
83 _current_ customer status, not status at the time the invoice was generated.
85 - agentnum: Filter on customer agent.
87 - refnum: Filter on customer reference source.
89 - classnum: Filter on package class.
91 - use_override: Apply "classnum" and "taxclass" filtering based on the
92 override (bundle) pkgpart, rather than always using the true pkgpart.
94 - nottax: Limit to items that are not taxes (pkgnum > 0).
96 - istax: Limit to items that are taxes (pkgnum == 0).
98 - taxnum: Limit to items whose tax definition matches this taxnum.
99 With "nottax" that means items that are subject to that tax;
100 with "istax" it's the tax charges themselves. Can be specified
101 more than once to include multiple taxes.
103 - country, state, county, city: Limit to items whose tax location
104 matches these fields. If "nottax" it's the tax location of the package;
105 if "istax" the location of the tax.
107 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
108 matches a tax with this name. With "istax", limit to items that have
109 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
112 - out: With "nottax", limit to items that don't match any tax definition.
113 With "istax", find tax items that are unlinked to their tax definitions.
114 Current Freeside (> July 2012) always creates tax links, but unlinked
115 items may result from an incomplete upgrade of legacy data.
117 - locationtaxid: With "nottax", limit to packages matching this
118 tax_rate_location ID; with "tax", limit to taxes generated from that
121 - taxclass: Filter on package taxclass.
123 - taxclassNULL: With "nottax", limit to items that would be subject to the
124 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
125 is NULL; it also includes taxclasses that don't have a tax in this region.
127 - itemdesc: Limit to line items with this description. Note that non-tax
128 packages usually have a description of NULL. (Deprecated.)
130 - report_group: Can contain '=' or '!=' followed by a string to limit to
131 line items where itemdesc starts with, or doesn't start with, the string.
133 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
134 specified, limit to customers who are also specifically exempt from that
137 - pkg_tax: Limit to packages that are tax-exempt, and only include the
138 exempt portion (setup, recurring, or both) when calculating totals.
140 - taxable: Limit to packages that are subject to tax, i.e. where a
141 cust_bill_pkg_tax_location record exists.
143 - credit: Limit to line items that received a credit application. The
144 amount of the credit will also be shown.
150 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
152 my $conf = new FS::Conf;
153 my $money_char = $conf->config('money_char') || '$';
155 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
156 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
157 my @total_desc = ( '%d line items', $money_char.'%.2f total' ); # sprintf strings
158 my @peritem = ( 'setup', 'recur' );
159 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
160 my ($join_cust, $join_pkg ) = ('', '');
163 # valid in both the tax and non-tax cases
165 " LEFT JOIN cust_bill USING (invnum)
166 LEFT JOIN cust_main USING (custnum)
169 #agent virtualization
171 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
173 my @where = ( $agentnums_sql );
176 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
178 if ( $cgi->param('distribute') == 1 ) {
179 push @where, "sdate <= $ending",
180 "edate > $beginning",
184 push @where, "cust_bill._date >= $beginning",
185 "cust_bill._date <= $ending";
189 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
190 push @where, FS::cust_main->cust_status_sql . " = '$1'";
194 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
195 push @where, "cust_main.agentnum = $1";
199 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
200 push @where, "cust_main.refnum = $1";
204 if ( $cgi->param('nottax') ) {
206 push @where, 'cust_bill_pkg.pkgnum > 0';
208 # then we want the package and its definition
210 ' LEFT JOIN cust_pkg USING (pkgnum)
211 LEFT JOIN part_pkg USING (pkgpart)';
213 my $part_pkg = 'part_pkg';
214 if ( $cgi->param('use_override') ) {
215 # still need the real part_pkg for tax applicability,
217 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
218 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
220 $part_pkg = 'override';
222 push @select, 'part_pkg.pkg'; # or should this use override?
224 my @tax_where; # will go into a subquery
225 my @exempt_where; # will also go into a subquery
227 # classnum (of override pkgpart if applicable)
228 # not specified: all classes
231 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
232 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
236 if ( $cgi->param('taxclassNULL') ) {
237 # a little different from 'taxclass' in that it applies to the
238 # effective taxclass, not the real one
239 push @tax_where, 'cust_main_county.taxclass IS NULL'
240 } elsif ( $cgi->param('taxclass') ) {
241 push @tax_where, "$part_pkg.taxclass IN (" .
242 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
246 if ( $cgi->param('exempt_cust') eq 'Y' ) {
247 # tax-exempt customers
248 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
250 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
251 # non-taxable package charges
252 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
254 # we don't handle exempt_monthly here
256 if ( $cgi->param('taxname') ) { # specific taxname
257 push @tax_where, 'cust_main_county.taxname = '.
258 dbh->quote($cgi->param('taxname'));
259 } elsif ( $cgi->param('taxnameNULL') ) {
260 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
261 'cust_main_county.taxname = \'Tax\'';
264 # country:state:county:city:district (may be repeated)
265 # You can also pass a big list of taxnums but that leads to huge URLs.
266 # Note that this means "packages whose tax is in this region", not
267 # "packages in this region". It's meant for links from the tax report.
268 if ( $cgi->param('region') ) {
270 foreach ( $cgi->param('region') ) {
272 @loc{qw(country state county city district)} =
273 split(':', $cgi->param('region'));
274 my $string = join(' AND ',
277 "$_ = ".dbh->quote($loc{$_});
283 push @orwhere, "($string)";
285 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
289 if ( $cgi->param('taxnum') ) {
290 my $taxnum_in = join(',',
291 grep /^\d+$/, $cgi->param('taxnum')
293 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
297 # If we're showing exempt items, we need to find those with
298 # cust_tax_exempt_pkg records matching the selected taxes.
299 # If we're showing taxable items, we need to find those with
300 # cust_bill_pkg_tax_location records. We also need to find the
301 # exemption records so that we can show the taxable amount.
302 # If we're showing all items, we need the union of those.
303 # If we're showing 'out' (items that aren't region/class taxable),
304 # then we need the set of all items minus the union of those.
308 if ( @exempt_where or @tax_where
309 or $cgi->param('taxable') or $cgi->param('out') )
311 # process exemption restrictions, including @tax_where
312 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
313 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
315 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
316 if (@tax_where or @exempt_where);
318 $exempt_sub .= ' GROUP BY billpkgnum';
320 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
324 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
325 # process tax restrictions
327 'cust_main_county.tax > 0';
329 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
330 FROM cust_bill_pkg_tax_location
331 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
332 JOIN cust_main_county USING (taxnum)
333 WHERE ". join(' AND ', @tax_where).
334 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
336 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
337 ON (item_tax.invnum = cust_bill_pkg.invnum AND
338 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
341 # now do something with that
342 if ( @exempt_where ) {
344 push @where, 'item_exempt.billpkgnum IS NOT NULL';
345 push @select, 'item_exempt.exempt_amount';
346 push @peritem, 'exempt_amount';
347 push @peritem_desc, 'Exempt';
348 push @total, 'SUM(exempt_amount)';
349 push @total_desc, "$money_char%.2f tax-exempt";
351 } elsif ( $cgi->param('taxable') ) {
353 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
354 '- COALESCE(item_exempt.exempt_amount, 0)';
356 push @where, 'item_tax.invnum IS NOT NULL';
357 push @select, "($taxable) AS taxable_amount";
358 push @peritem, 'taxable_amount';
359 push @peritem_desc, 'Taxable';
360 push @total, "SUM($taxable)";
361 push @total_desc, "$money_char%.2f taxable";
363 } elsif ( $cgi->param('out') ) {
365 push @where, 'item_tax.invnum IS NULL',
366 'item_exempt.billpkgnum IS NULL';
368 } elsif ( @tax_where ) {
370 # union of taxable + all exempt_ cases
372 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
376 # recur/usage separation
377 $use_usage = $cgi->param('usage');
378 if ( $use_usage eq 'recurring' ) {
380 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
381 push @select, "($recur_no_usage) AS recur_no_usage";
382 $peritem[1] = 'recur_no_usage';
383 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
384 $total_desc[1] .= ' (excluding usage)';
386 } elsif ( $use_usage eq 'usage' ) {
388 my $usage = FS::cust_bill_pkg->usage_sql();
389 push @select, "($usage) AS _usage";
390 # there's already a method named 'usage'
391 $peritem[1] = '_usage';
392 $peritem_desc[1] = 'Usage charge';
393 $total[1] = "SUM($usage)";
394 $total_desc[1] .= ' usage charges';
397 } elsif ( $cgi->param('istax') ) {
399 @peritem = ( 'setup' ); # taxes only have setup
400 @peritem_desc = ( 'Tax charge' );
402 push @where, 'cust_bill_pkg.pkgnum = 0';
404 # tax location when using tax_rate_location
405 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
407 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
408 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
409 push @where, FS::tax_rate_location->location_sql(
410 map { $_ => (scalar($cgi->param($_)) || '') }
411 qw( district city county state locationtaxid )
415 COALESCE(cust_bill_pkg_tax_rate_location.amount,
416 cust_bill_pkg.setup + cust_bill_pkg.recur)
419 } elsif ( $cgi->param('out') ) {
422 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
424 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
426 # each billpkgnum should appear only once
427 $total[0] = 'COUNT(*)';
428 $total[1] = 'SUM(cust_bill_pkg.setup)';
430 } else { # not locationtaxid or 'out'--the normal case
433 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
434 JOIN cust_main_county USING (taxnum)
437 # don't double-count the components of consolidated taxes
438 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
439 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
443 if ( $cgi->param('taxclassNULL') ) {
444 push @where, 'cust_main_county.taxclass IS NULL';
448 if ( $cgi->param('taxnameNULL') ) {
449 push @where, 'cust_main_county.taxname IS NULL OR '.
450 'cust_main_county.taxname = \'Tax\'';
451 } elsif ( $cgi->param('taxname') ) {
452 push @where, 'cust_main_county.taxname = '.
453 dbh->quote($cgi->param('taxname'));
457 if ( $cgi->param('taxnum') ) {
458 my $taxnum_in = join(',',
459 grep /^\d+$/, $cgi->param('taxnum')
461 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
465 # report group (itemdesc)
466 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
467 my ( $group_op, $group_value ) = ( $1, $2 );
468 if ( $group_op eq '=' ) {
469 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
470 push @where, 'itemdesc = '. dbh->quote($group_value);
471 } elsif ( $group_op eq '!=' ) {
472 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
474 die "guru meditation #00de: group_op $group_op\n";
478 # itemdesc, for some reason
479 if ( $cgi->param('itemdesc') ) {
480 if ( $cgi->param('itemdesc') eq 'Tax' ) {
481 push @where, "(itemdesc='Tax' OR itemdesc is null)";
483 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
490 if ( $cgi->param('credit') ) {
494 if ( $cgi->param('istax') ) {
495 # then we need to group/join by billpkgtaxlocationnum, to get only the
496 # relevant part of partial taxes
497 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
498 reason.reason as reason_text, access_user.username AS username_text,
499 billpkgtaxlocationnum, billpkgnum
500 FROM cust_credit_bill_pkg
501 JOIN cust_credit_bill USING (creditbillnum)
502 JOIN cust_credit USING (crednum)
503 LEFT JOIN reason USING (reasonnum)
504 LEFT JOIN access_user USING (usernum)
505 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
506 access_user.username";
508 if ( $cgi->param('out') ) {
510 # find credits that are applied to the line items, but not to
511 # a cust_bill_pkg_tax_location link
512 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
514 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
518 # find credits that are applied to the CBPTL links that are
519 # considered "interesting" by the report criteria
520 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
521 USING (billpkgtaxlocationnum)";
526 # then only group by billpkgnum
527 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
528 reason.reason as reason_text, access_user.username AS username_text,
530 FROM cust_credit_bill_pkg
531 JOIN cust_credit_bill USING (creditbillnum)
532 JOIN cust_credit USING (crednum)
533 LEFT JOIN reason USING (reasonnum)
534 LEFT JOIN access_user USING (usernum)
535 GROUP BY billpkgnum, reason.reason, access_user.username";
536 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
539 push @where, 'item_credit.billpkgnum IS NOT NULL';
540 push @select, 'item_credit.credit_amount',
541 'item_credit.username_text',
542 'item_credit.reason_text';
543 push @peritem, 'credit_amount', 'username_text', 'reason_text';
544 push @peritem_desc, 'Credited', 'By', 'Reason';
545 push @total, 'SUM(credit_amount)';
546 push @total_desc, "$money_char%.2f credited";
549 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
551 my $where = join(' AND ', @where);
552 $where &&= "WHERE $where";
555 'table' => 'cust_bill_pkg',
556 'addl_from' => "$join_cust $join_pkg",
558 'select' => join(",\n", @select ),
559 'extra_sql' => $where,
560 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
564 'SELECT ' . join(',', @total) .
565 " FROM cust_bill_pkg $join_cust $join_pkg
568 shift @total_desc; #the first one is implicit
570 @peritem_desc = map {emt($_)} @peritem_desc;
571 my @peritem_sub = map {
573 if ($field =~ /_text$/) { # kludge for credit reason/username fields
574 sub {$_[0]->get($field)};
576 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
579 my @peritem_null = map { '' } @peritem; # placeholders
580 my $peritem_align = 'r' x scalar(@peritem);
582 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
583 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
585 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
586 if $cgi->param('debug');