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('custnum') =~ /^(\d+)$/ ) {
205 push @where, "cust_main.custnum = $1";
209 if ( $cgi->param('nottax') ) {
211 push @where, 'cust_bill_pkg.pkgnum > 0';
213 # then we want the package and its definition
215 ' LEFT JOIN cust_pkg USING (pkgnum)
216 LEFT JOIN part_pkg USING (pkgpart)';
218 my $part_pkg = 'part_pkg';
219 if ( $cgi->param('use_override') ) {
220 # still need the real part_pkg for tax applicability,
222 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
223 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
225 $part_pkg = 'override';
227 push @select, 'part_pkg.pkg'; # or should this use override?
229 my @tax_where; # will go into a subquery
230 my @exempt_where; # will also go into a subquery
232 # classnum (of override pkgpart if applicable)
233 # not specified: all classes
236 if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
237 push @where, "COALESCE($part_pkg.classnum, 0) = $1";
241 if ( $cgi->param('taxclassNULL') ) {
242 # a little different from 'taxclass' in that it applies to the
243 # effective taxclass, not the real one
244 push @tax_where, 'cust_main_county.taxclass IS NULL'
245 } elsif ( $cgi->param('taxclass') ) {
246 push @tax_where, "$part_pkg.taxclass IN (" .
247 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
251 if ( $cgi->param('exempt_cust') eq 'Y' ) {
252 # tax-exempt customers
253 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
255 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
256 # non-taxable package charges
257 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
259 # we don't handle exempt_monthly here
261 if ( $cgi->param('taxname') ) { # specific taxname
262 push @tax_where, 'cust_main_county.taxname = '.
263 dbh->quote($cgi->param('taxname'));
264 } elsif ( $cgi->param('taxnameNULL') ) {
265 push @tax_where, 'cust_main_county.taxname IS NULL OR '.
266 'cust_main_county.taxname = \'Tax\'';
269 # country:state:county:city:district (may be repeated)
270 # You can also pass a big list of taxnums but that leads to huge URLs.
271 # Note that this means "packages whose tax is in this region", not
272 # "packages in this region". It's meant for links from the tax report.
273 if ( $cgi->param('region') ) {
275 foreach ( $cgi->param('region') ) {
277 @loc{qw(country state county city district)} =
278 split(':', $cgi->param('region'));
279 my $string = join(' AND ',
282 "$_ = ".dbh->quote($loc{$_});
288 push @orwhere, "($string)";
290 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
294 if ( $cgi->param('taxnum') ) {
295 my $taxnum_in = join(',',
296 grep /^\d+$/, $cgi->param('taxnum')
298 push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
302 # If we're showing exempt items, we need to find those with
303 # cust_tax_exempt_pkg records matching the selected taxes.
304 # If we're showing taxable items, we need to find those with
305 # cust_bill_pkg_tax_location records. We also need to find the
306 # exemption records so that we can show the taxable amount.
307 # If we're showing all items, we need the union of those.
308 # If we're showing 'out' (items that aren't region/class taxable),
309 # then we need the set of all items minus the union of those.
313 if ( @exempt_where or @tax_where
314 or $cgi->param('taxable') or $cgi->param('out') )
316 # process exemption restrictions, including @tax_where
317 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
318 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
320 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
321 if (@tax_where or @exempt_where);
323 $exempt_sub .= ' GROUP BY billpkgnum';
325 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
329 if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
330 # process tax restrictions
332 'cust_main_county.tax > 0';
334 my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
335 FROM cust_bill_pkg_tax_location
336 JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
337 JOIN cust_main_county USING (taxnum)
338 WHERE ". join(' AND ', @tax_where).
339 " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
341 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
342 ON (item_tax.invnum = cust_bill_pkg.invnum AND
343 item_tax.pkgnum = cust_bill_pkg.pkgnum)";
346 # now do something with that
347 if ( @exempt_where ) {
349 push @where, 'item_exempt.billpkgnum IS NOT NULL';
350 push @select, 'item_exempt.exempt_amount';
351 push @peritem, 'exempt_amount';
352 push @peritem_desc, 'Exempt';
353 push @total, 'SUM(exempt_amount)';
354 push @total_desc, "$money_char%.2f tax-exempt";
356 } elsif ( $cgi->param('taxable') ) {
358 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
359 '- COALESCE(item_exempt.exempt_amount, 0)';
361 push @where, 'item_tax.invnum IS NOT NULL';
362 push @select, "($taxable) AS taxable_amount";
363 push @peritem, 'taxable_amount';
364 push @peritem_desc, 'Taxable';
365 push @total, "SUM($taxable)";
366 push @total_desc, "$money_char%.2f taxable";
368 } elsif ( $cgi->param('out') ) {
370 push @where, 'item_tax.invnum IS NULL',
371 'item_exempt.billpkgnum IS NULL';
373 } elsif ( @tax_where ) {
375 # union of taxable + all exempt_ cases
377 '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
381 # recur/usage separation
382 $use_usage = $cgi->param('usage');
383 if ( $use_usage eq 'recurring' ) {
385 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
386 push @select, "($recur_no_usage) AS recur_no_usage";
387 $peritem[1] = 'recur_no_usage';
388 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
389 $total_desc[1] .= ' (excluding usage)';
391 } elsif ( $use_usage eq 'usage' ) {
393 my $usage = FS::cust_bill_pkg->usage_sql();
394 push @select, "($usage) AS _usage";
395 # there's already a method named 'usage'
396 $peritem[1] = '_usage';
397 $peritem_desc[1] = 'Usage charge';
398 $total[1] = "SUM($usage)";
399 $total_desc[1] .= ' usage charges';
402 } elsif ( $cgi->param('istax') ) {
404 @peritem = ( 'setup' ); # taxes only have setup
405 @peritem_desc = ( 'Tax charge' );
407 push @where, 'cust_bill_pkg.pkgnum = 0';
409 # tax location when using tax_rate_location
410 if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
412 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
413 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
414 push @where, FS::tax_rate_location->location_sql(
415 map { $_ => (scalar($cgi->param($_)) || '') }
416 qw( district city county state locationtaxid )
420 COALESCE(cust_bill_pkg_tax_rate_location.amount,
421 cust_bill_pkg.setup + cust_bill_pkg.recur)
424 } elsif ( $cgi->param('out') ) {
427 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
429 push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
431 # each billpkgnum should appear only once
432 $total[0] = 'COUNT(*)';
433 $total[1] = 'SUM(cust_bill_pkg.setup)';
435 } else { # not locationtaxid or 'out'--the normal case
438 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
439 JOIN cust_main_county USING (taxnum)
442 # don't double-count the components of consolidated taxes
443 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
444 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
448 if ( $cgi->param('taxclassNULL') ) {
449 push @where, 'cust_main_county.taxclass IS NULL';
453 if ( $cgi->param('taxnameNULL') ) {
454 push @where, 'cust_main_county.taxname IS NULL OR '.
455 'cust_main_county.taxname = \'Tax\'';
456 } elsif ( $cgi->param('taxname') ) {
457 push @where, 'cust_main_county.taxname = '.
458 dbh->quote($cgi->param('taxname'));
462 if ( $cgi->param('taxnum') ) {
463 my $taxnum_in = join(',',
464 grep /^\d+$/, $cgi->param('taxnum')
466 push @where, "cust_main_county.taxnum IN ($taxnum_in)"
470 # report group (itemdesc)
471 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
472 my ( $group_op, $group_value ) = ( $1, $2 );
473 if ( $group_op eq '=' ) {
474 #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
475 push @where, 'itemdesc = '. dbh->quote($group_value);
476 } elsif ( $group_op eq '!=' ) {
477 push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
479 die "guru meditation #00de: group_op $group_op\n";
483 # itemdesc, for some reason
484 if ( $cgi->param('itemdesc') ) {
485 if ( $cgi->param('itemdesc') eq 'Tax' ) {
486 push @where, "(itemdesc='Tax' OR itemdesc is null)";
488 push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
495 if ( $cgi->param('credit') ) {
499 if ( $cgi->param('istax') ) {
500 # then we need to group/join by billpkgtaxlocationnum, to get only the
501 # relevant part of partial taxes
502 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
503 reason.reason as reason_text, access_user.username AS username_text,
504 billpkgtaxlocationnum, billpkgnum
505 FROM cust_credit_bill_pkg
506 JOIN cust_credit_bill USING (creditbillnum)
507 JOIN cust_credit USING (crednum)
508 LEFT JOIN reason USING (reasonnum)
509 LEFT JOIN access_user USING (usernum)
510 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
511 access_user.username";
513 if ( $cgi->param('out') ) {
515 # find credits that are applied to the line items, but not to
516 # a cust_bill_pkg_tax_location link
517 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
519 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
523 # find credits that are applied to the CBPTL links that are
524 # considered "interesting" by the report criteria
525 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
526 USING (billpkgtaxlocationnum)";
531 # then only group by billpkgnum
532 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
533 reason.reason as reason_text, access_user.username AS username_text,
535 FROM cust_credit_bill_pkg
536 JOIN cust_credit_bill USING (creditbillnum)
537 JOIN cust_credit USING (crednum)
538 LEFT JOIN reason USING (reasonnum)
539 LEFT JOIN access_user USING (usernum)
540 GROUP BY billpkgnum, reason.reason, access_user.username";
541 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
544 push @where, 'item_credit.billpkgnum IS NOT NULL';
545 push @select, 'item_credit.credit_amount',
546 'item_credit.username_text',
547 'item_credit.reason_text';
548 push @peritem, 'credit_amount', 'username_text', 'reason_text';
549 push @peritem_desc, 'Credited', 'By', 'Reason';
550 push @total, 'SUM(credit_amount)';
551 push @total_desc, "$money_char%.2f credited";
554 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
556 my $where = join(' AND ', @where);
557 $where &&= "WHERE $where";
560 'table' => 'cust_bill_pkg',
561 'addl_from' => "$join_cust $join_pkg",
563 'select' => join(",\n", @select ),
564 'extra_sql' => $where,
565 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
569 'SELECT ' . join(',', @total) .
570 " FROM cust_bill_pkg $join_cust $join_pkg
573 shift @total_desc; #the first one is implicit
575 @peritem_desc = map {emt($_)} @peritem_desc;
576 my @peritem_sub = map {
578 if ($field =~ /_text$/) { # kludge for credit reason/username fields
579 sub {$_[0]->get($field)};
581 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
584 my @peritem_null = map { '' } @peritem; # placeholders
585 my $peritem_align = 'r' x scalar(@peritem);
587 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
588 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
590 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
591 if $cgi->param('debug');