1 <& elements/search.html,
2 'title' => emt('Line items'),
3 'name' => emt('line items'),
5 'count_query' => $count_query,
6 'count_addl' => \@total_desc,
18 FS::UI::Web::cust_header(),
22 sub { $_[0]->pkgnum > 0
23 ? $_[0]->get('pkgpart')
26 'itemdesc', # is part_pkg.pkg if applicable
28 #strikethrough or "N/A ($amount)" or something these when
29 # they're not applicable to pkg_tax search
33 sub { time2str('%b %d %Y', shift->_date ) },
34 sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
35 sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
36 \&FS::UI::Web::cust_fields,
49 FS::UI::Web::cust_sort_fields(),
62 ( map { $_ ne 'Cust. Status' ? $clink : '' }
63 FS::UI::Web::cust_header()
66 #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
67 'align' => $pkgnum_align.
73 FS::UI::Web::cust_aligns(),
85 FS::UI::Web::cust_colors(),
98 FS::UI::Web::cust_styles(),
103 Output control parameters:
104 - distribute: Boolean. If true, recurring fees will be "prorated" for the
105 portion of the package date range (sdate-edate) that falls within the date
106 range of the report. Line items will be limited to those for which this
107 portion is > 0. This disables filtering on invoice date.
109 - usage: Separate usage (cust_bill_pkg_detail records) from
110 recurring charges. If set to "usage", will show usage instead of
111 recurring charges. If set to "recurring", will deduct usage and only
112 show the flat rate charge. If not passed, the "recurring charge" column
113 will include usage charges also.
115 Filtering parameters:
116 - begin, end: Date range. Applies to invoice date, not necessarily package
117 date range. But see "distribute".
119 - status: Customer status (active, suspended, etc.). This will filter on
120 _current_ customer status, not status at the time the invoice was generated.
122 - agentnum: Filter on customer agent.
124 - refnum: Filter on customer reference source.
126 - cust_classnum: Filter on customer class.
128 - classnum: Filter on package class.
130 - report_optionnum: Filter on package report class. Can be a single report
131 class number or a comma-separated list (where 0 is "no report class"), or the
134 - use_override: Apply "classnum" and "taxclass" filtering based on the
135 override (bundle) pkgpart, rather than always using the true pkgpart.
137 - nottax: Limit to items that are not taxes (pkgnum > 0 or feepart > 0).
139 - istax: Limit to items that are taxes (pkgnum == 0 and feepart = null).
141 - taxnum: Limit to items whose tax definition matches this taxnum.
142 With "nottax" that means items that are subject to that tax;
143 with "istax" it's the tax charges themselves. Can be a comma-separated
144 list to include multiple taxes.
146 - country, state, county, city: Limit to items whose tax location
147 matches these fields. If "nottax" it's the tax location of the package;
148 if "istax" the location of the tax.
150 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
151 matches a tax with this name. With "istax", limit to items that have
152 this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname
155 - out: With "nottax", limit to items that don't match any tax definition.
156 With "istax", find tax items that are unlinked to their tax definitions.
157 Current Freeside (> July 2012) always creates tax links, but unlinked
158 items may result from an incomplete upgrade of legacy data.
160 - locationtaxid: With "nottax", limit to packages matching this
161 tax_rate_location ID; with "tax", limit to taxes generated from that
164 - taxclass: Filter on package taxclass.
166 - taxclassNULL: With "nottax", limit to items that would be subject to the
167 tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass
168 is NULL; it also includes taxclasses that don't have a tax in this region.
170 - itemdesc: Limit to line items with this description. Note that non-tax
171 packages usually have a description of NULL. (Deprecated.)
173 - report_group: Can contain '=' or '!=' followed by a string to limit to
174 line items where itemdesc starts with, or doesn't start with, the string.
176 - cust_tax: Limit to customers who are tax-exempt. If "taxname" is also
177 specified, limit to customers who are also specifically exempt from that
180 - pkg_tax: Limit to packages that are tax-exempt, and only include the
181 exempt portion (setup, recurring, or both) when calculating totals.
183 - taxable: Limit to packages that are subject to tax, i.e. where a
184 cust_bill_pkg_tax_location record exists.
186 - credit: Limit to line items that received a credit application. The
187 amount of the credit will also be shown.
192 my $curuser = $FS::CurrentUser::CurrentUser;
194 die "access denied" unless $curuser->access_right('Financial reports');
196 my $conf = new FS::Conf;
197 my $money_char = $conf->config('money_char') || '$';
199 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
200 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
201 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
203 my @peritem = ( 'setup', 'recur' );
204 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
206 my @currency_desc = ();
207 my @currency_sub = ();
209 if ( $conf->config('currencies') ) {
210 @currency_desc = ( 'Setup billed', 'Recurring billed' );
214 sub { my $currency = $_[0]->get($what.'_billed_currency') or return '';
215 $currency. ' '. currency_symbol($currency, SYM_HTML).
216 $_[0]->get($what.'_billed_amount');
220 @currency = ( 'setup_billed_amount', 'recur_billed_amount' ); #for sorting
223 my @pkgnum_header = ();
226 my $pkgnum_align = '';
227 if ( $curuser->option('show_pkgnum') ) {
228 push @select, 'cust_bill_pkg.pkgnum';
229 push @pkgnum_header, 'Pkg Num';
230 push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
231 push @pkgnum_null, '';
232 $pkgnum_align .= 'r';
235 my @post_desc_header = ();
237 my @post_desc_null = ();
238 my $post_desc_align = '';
239 if ( $conf->exists('enable_taxclasses') ) {
240 push @post_desc_header, 'Tax class';
241 push @post_desc, 'taxclass';
242 push @post_desc_null, '';
243 $post_desc_align .= 'l';
246 # used in several places
247 my $itemdesc = 'COALESCE(part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)';
249 # valid in both the tax and non-tax cases
251 " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
252 # use cust_pkg.locationnum if it exists
253 FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
255 #agent virtualization
257 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
259 my @where = ( $agentnums_sql );
262 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
264 if ( $cgi->param('distribute') == 1 ) {
265 push @where, "sdate <= $ending",
266 "edate > $beginning",
269 push @where, "cust_bill._date >= $beginning",
270 "cust_bill._date <= $ending";
274 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
275 push @where, FS::cust_main->cust_status_sql . " = '$1'";
279 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
280 push @where, "cust_main.agentnum = $1";
283 # salesnum--see below
285 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
286 push @where, "cust_main.refnum = $1";
289 # cust_classnum (false laziness w/ elements/cust_main_dayranges.html, elements/cust_pay_or_refund.html, prepaid_income.html, cust_bill_pay.html, cust_bill_pkg_referral.html, unearned_detail.html, cust_credit.html, cust_credit_refund.html, cust_main::Search::search_sql)
290 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
291 my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
292 push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
293 join(',', map { $_ || '0' } @classnums ).
300 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
301 push @where, "cust_main.custnum = $1";
304 # we want the package and its definition if available
306 ' LEFT JOIN cust_pkg USING (pkgnum)
307 LEFT JOIN part_pkg USING (pkgpart)
308 LEFT JOIN part_fee USING (feepart)';
310 my $part_pkg = 'part_pkg';
311 # "Separate sub-packages from parents"
312 my $use_override = $cgi->param('use_override') ? 1 : 0;
313 if ( $use_override ) {
314 # still need the real part_pkg for tax applicability,
316 $join_pkg .= " LEFT JOIN part_pkg AS override ON (
317 COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
319 $part_pkg = 'override';
321 push @select, "$part_pkg.pkgpart", "$part_pkg.pkg";
322 push @select, "COALESCE($part_pkg.taxclass, part_fee.taxclass) AS taxclass"
323 if $conf->exists('enable_taxclasses');
326 if ( $cgi->param('nottax') ) {
328 push @select, "($itemdesc) AS itemdesc";
331 '(cust_bill_pkg.pkgnum > 0 OR cust_bill_pkg.feepart IS NOT NULL)';
333 my @tax_where; # will go into a subquery
334 my @exempt_where; # will also go into a subquery
336 # classnum (of override pkgpart if applicable)
337 # not specified: all classes
340 if ( grep { $_ eq 'classnum' } $cgi->param ) {
341 my @classnums = grep /^\d+$/, $cgi->param('classnum');
342 push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
343 join(',', @classnums ).
348 if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
349 my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
350 my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
351 my $all = $cgi->param('all_report_options') ? 1 : 0;
352 push @where, # code reuse FTW
353 FS::Report::Table->with_report_option(
354 report_optionnum => $num,
355 not_report_optionnum => $not_num,
356 use_override => $use_override,
357 all_report_options => $all,
362 if ( $cgi->param('taxclassNULL') ) {
363 # a little different from 'taxclass' in that it applies to the
364 # effective taxclass, not the real one
365 push @tax_where, 'cust_main_county.taxclass IS NULL'
366 } elsif ( $cgi->param('taxclass') ) {
367 push @tax_where, "COALESCE(part_fee.taxclass, $part_pkg.taxclass) IN (" .
368 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
372 if ( $cgi->param('exempt_cust') eq 'Y' ) {
373 # tax-exempt customers
374 push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
376 } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
377 # non-taxable package charges
378 push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
380 # we don't handle exempt_monthly here
382 if ( $cgi->param('taxname') ) { # specific taxname
383 push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
384 dbh->quote($cgi->param('taxname'));
387 # country:state:county:city:district (may be repeated)
388 # You can also pass a big list of taxnums but that leads to huge URLs.
389 # Note that this means "packages whose tax is in this region", not
390 # "packages in this region". It's meant for links from the tax report.
391 if ( $cgi->param('region') ) {
393 foreach ( $cgi->param('region') ) {
395 @loc{qw(country state county city district)} =
396 split(':', $cgi->param('region'));
397 my $string = join(' AND ',
400 "$_ = ".dbh->quote($loc{$_});
406 push @orwhere, "($string)";
408 push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
412 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
413 push @tax_where, "cust_main_county.taxnum IN ($1)";
416 # If we're showing exempt items, we need to find those with
417 # cust_tax_exempt_pkg records matching the selected taxes.
418 # If we're showing taxable items, we need to find those with
419 # cust_bill_pkg_tax_location records. We also need to find the
420 # exemption records so that we can show the taxable amount.
421 # If we're showing all items, we need the union of those.
422 # If we're showing 'out' (items that aren't region/class taxable),
423 # then we need the set of all items minus the union of those.
425 # Always exclude cust_tax_exempt_pkg records with non-NULL creditbillpkgnum.
427 if ( $cgi->param('out') ) {
428 # separate from the rest, in that we're not going to join cust_main_county
431 my @exclude = ( 'cust_tax_exempt_pkg.billpkgnum',
432 'cust_bill_pkg_tax_location.taxable_billpkgnum'
434 foreach my $col (@exclude) {
435 my ($table) = split(/\./, $col);
436 my $this_where = 'WHERE ' . join(' AND ',
437 "$col = cust_bill_pkg.billpkgnum",
442 "NOT EXISTS(SELECT 1 FROM $table
443 JOIN cust_main_county USING (taxnum)
449 # everything that returns things joined to a tax definition
451 if ( @exempt_where or @tax_where or $cgi->param('taxable') ) {
453 push @exempt_where, "cust_tax_exempt_pkg.creditbillpkgnum IS NULL";
455 # process exemption restrictions, including @tax_where
456 my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
457 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
459 $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where);
461 $exempt_sub .= ' GROUP BY billpkgnum';
463 $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
464 ON (cust_bill_pkg.billpkgnum = item_exempt.billpkgnum)";
468 my $credit_sub = 'SELECT SUM(amount) AS credit_amount, billpkgnum
469 FROM cust_credit_bill_pkg GROUP BY billpkgnum';
471 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
472 ON (cust_bill_pkg.billpkgnum = item_credit.billpkgnum)";
474 if ( @tax_where or $cgi->param('taxable') ) {
475 # process tax restrictions
477 'cust_main_county.tax > 0';
479 my $tax_sub = "SELECT taxable_billpkgnum
480 FROM cust_bill_pkg_tax_location
481 JOIN cust_main_county USING (taxnum)
482 WHERE ". join(' AND ', @tax_where).
483 " GROUP BY taxable_billpkgnum";
485 $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
486 ON (cust_bill_pkg.billpkgnum = item_tax.taxable_billpkgnum)"
489 # now do something with that
490 if ( $cgi->param('taxable') ) {
491 # taxable query: needs sale amount - exempt amount
492 my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
493 '- COALESCE(item_exempt.exempt_amount, 0)';
495 push @where, "item_tax.taxable_billpkgnum IS NOT NULL";
496 push @select, "($taxable) AS taxable_amount";
497 push @peritem, 'taxable_amount';
498 push @peritem_desc, 'Taxable';
499 push @total, "SUM($taxable)";
500 push @total_desc, "$money_char%.2f taxable";
502 } elsif ( $cgi->param('exempt_cust') or $cgi->param('exempt_pkg') ) {
504 push @where, 'item_exempt.billpkgnum IS NOT NULL';
505 push @select, 'item_exempt.exempt_amount';
506 push @peritem, 'exempt_amount';
507 push @peritem_desc, 'Exempt';
508 push @total, 'SUM(exempt_amount)';
509 push @total_desc, "$money_char%.2f tax-exempt";
511 } elsif ( @tax_where ) {
512 # union of taxable + all exempt_ cases
514 '(item_tax.taxable_billpkgnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
518 } # handle all joins to cust_main_county
520 # recur/usage separation
521 if ( $cgi->param('usage') eq 'recurring' ) {
523 my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
524 push @select, "($recur_no_usage) AS recur_no_usage";
525 $peritem[1] = 'recur_no_usage';
526 $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
527 $total_desc[0] .= ' (excluding usage)';
529 } elsif ( $cgi->param('usage') eq 'usage' ) {
531 my $usage = FS::cust_bill_pkg->usage_sql();
532 push @select, "($usage) AS _usage";
533 # there's already a method named 'usage'
534 $peritem[1] = '_usage';
535 $peritem_desc[1] = 'Usage charge';
536 $total[1] = "SUM($usage)";
537 $total_desc[0] .= ' usage charges';
540 } elsif ( $cgi->param('istax') ) {
542 @peritem = ( 'setup' ); # taxes only have setup
543 @peritem_desc = ( 'Tax charge' );
545 push @where, 'cust_bill_pkg.pkgnum = 0';
547 # tax location when using tax_rate_location
548 if ( $cgi->param('vendortax') ) {
550 $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
551 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
552 foreach (qw( state county city locationtaxid)) {
553 if ( scalar($cgi->param($_)) ) {
554 my $place = dbh->quote( $cgi->param($_) );
555 push @where, "tax_rate_location.$_ = $place";
560 COALESCE(cust_bill_pkg_tax_rate_location.amount,
561 cust_bill_pkg.setup + cust_bill_pkg.recur)
564 } else { # the internal-tax case
567 LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
568 JOIN cust_main_county USING (taxnum)
571 # don't double-count the components of consolidated taxes
572 $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
573 $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
576 if ( grep { $_ eq 'classnum' } $cgi->param ) {
577 my @classnums = grep /^\d*$/, $cgi->param('classnum');
579 JOIN cust_pkg AS taxed_pkg
580 ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
581 JOIN part_pkg AS taxed_part_pkg
582 ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
584 push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
585 join(',', @classnums ).
591 if ( $cgi->param('taxclassNULL') ) {
592 push @where, 'cust_main_county.taxclass IS NULL';
596 if ( $cgi->param('taxnameNULL') ) {
597 push @where, 'cust_main_county.taxname IS NULL OR '.
598 'cust_main_county.taxname = \'Tax\'';
599 } elsif ( $cgi->param('taxname') ) {
600 push @where, 'cust_main_county.taxname = '.
601 dbh->quote($cgi->param('taxname'));
604 # itemdesc, for breakdown from the vendor tax report
605 if ( $cgi->param('itemdesc') ) {
606 if ( $cgi->param('itemdesc') eq 'Tax' ) {
607 push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
609 push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
614 if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
615 push @where, "cust_main_county.taxnum IN ($1)";
618 } #end of "normal case"
620 # classnum (of underlying package)
621 # not specified: all classes
624 if ( grep { $_ eq 'classnum' } $cgi->param ) {
625 my @classnums = grep /^\d+$/, $cgi->param('classnum');
626 push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
627 join(',', @classnums ).
636 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
637 FROM cust_bill_pay_pkg
638 WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
640 push @select, "($pay_sub) AS pay_amount";
644 if ( $cgi->param('credit') ) {
648 my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit');
649 $credit_where = "WHERE cust_credit_bill._date >= $cr_begin " .
650 "AND cust_credit_bill._date <= $cr_end";
654 if ( $cgi->param('istax') ) {
655 # then we need to group/join by billpkgtaxlocationnum, to get only the
656 # relevant part of partial taxes
657 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
658 reason.reason as reason_text, access_user.username AS username_text,
659 billpkgtaxlocationnum, billpkgnum
660 FROM cust_credit_bill_pkg
661 JOIN cust_credit_bill USING (creditbillnum)
662 JOIN cust_credit USING (crednum)
663 LEFT JOIN reason USING (reasonnum)
664 LEFT JOIN access_user USING (usernum)
666 GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
667 access_user.username";
669 if ( $cgi->param('out') ) {
671 # find credits that are applied to the line items, but not to
672 # a cust_bill_pkg_tax_location link
673 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
675 push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
679 # find credits that are applied to the CBPTL links that are
680 # considered "interesting" by the report criteria
681 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
682 USING (billpkgtaxlocationnum)";
687 # then only group by billpkgnum
688 my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
689 reason.reason as reason_text, access_user.username AS username_text,
691 FROM cust_credit_bill_pkg
692 JOIN cust_credit_bill USING (creditbillnum)
693 JOIN cust_credit USING (crednum)
694 LEFT JOIN reason USING (reasonnum)
695 LEFT JOIN access_user USING (usernum)
697 GROUP BY billpkgnum, reason.reason, access_user.username";
698 $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
701 push @where, 'item_credit.billpkgnum IS NOT NULL';
702 push @select, 'item_credit.credit_amount',
703 'item_credit.username_text',
704 'item_credit.reason_text';
705 push @peritem, 'credit_amount', 'username_text', 'reason_text';
706 push @peritem_desc, 'Credited', 'By', 'Reason';
707 push @total, 'SUM(credit_amount)';
708 push @total_desc, "$money_char%.2f credited";
712 #still want a credit total column
715 SELECT SUM(cust_credit_bill_pkg.amount)
716 FROM cust_credit_bill_pkg
717 WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
719 push @select, "($credit_sub) AS credit_amount";
723 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
726 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
729 my $sales = FS::sales->by_key($salesnum)
730 or die "salesnum $salesnum not found";
732 my $subsearch = $sales->cust_bill_pkg_search('', '',
733 'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
734 'paid' => ($cgi->param('paid') ? 1 : 0),
735 'classnum' => scalar($cgi->param('classnum'))
737 $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_fee.classnum, part_pkg.classnum, 0) )";
739 my $extra_sql = $subsearch->{extra_sql};
740 $extra_sql =~ s/^WHERE//;
741 push @where, $extra_sql;
743 $cgi->param('classnum', 0) unless $cgi->param('classnum');
747 my $where = join(' AND ', @where);
748 $where &&= "WHERE $where";
751 'table' => 'cust_bill_pkg',
752 'addl_from' => "$join_pkg $join_cust",
754 'select' => join(",\n", @select ),
755 'extra_sql' => $where,
756 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
760 'SELECT ' . join(',', @total) .
761 " FROM cust_bill_pkg $join_pkg $join_cust
764 @peritem_desc = map {emt($_)} @peritem_desc;
765 my @peritem_sub = map {
767 if ($field =~ /_text$/) { # kludge for credit reason/username fields
768 sub {$_[0]->get($field)};
770 sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
773 my @peritem_null = map { '' } @peritem; # placeholders
774 my $peritem_align = 'r' x scalar(@peritem);
776 @currency_desc = map {emt($_)} @currency_desc;
777 my @currency_null = map { '' } @currency; # placeholders
778 my $currency_align = 'r' x scalar(@currency);
780 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
781 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
783 my $pay_link = ''; #[, 'billpkgnum', ];
784 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
786 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
787 if $cgi->param('debug');