httemplate/search/cust_bill_pkg.cgi

   1 <& elements/search.html,
   2                  'title'       => emt('Line items'),
   3                  'name'        => emt('line items'),
   4                  'query'       => $query,
   5                  'count_query' => $count_query,
   6                  'count_addl'  => \@total_desc,
   7                  'header'      => [
   8                    @pkgnum_header,
   9                    emt('Pkg Def'),
  10                    emt('Description'),
  11                    @post_desc_header,
  12                    @peritem_desc,
  13                    emt('Invoice'),
  14                    emt('Date'),
  15                    emt('Paid'),
  16                    emt('Credited'),
  17                    FS::UI::Web::cust_header(),
  18                  ],
  19                  'fields'      => [
  20                    @pkgnum,
  21                    sub { $_[0]->pkgnum > 0
  22                            ? $_[0]->get('pkgpart')
  23                            : ''
  24                        },
  25                    'itemdesc', # is part_pkg.pkg if applicable
  26                    @post_desc,
  27                    #strikethrough or "N/A ($amount)" or something these when
  28                    # they're not applicable to pkg_tax search
  29                    @peritem_sub,
  30                    'invnum',
  31                    sub { time2str('%b %d %Y', shift->_date ) },
  32                    sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
  33                    sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
  34                    \&FS::UI::Web::cust_fields,
  35                  ],
  36                  'sort_fields' => [
  37                    @pkgnum_null,
  38                    '',
  39                    '',
  40                    @post_desc_null,
  41                    @peritem,
  42                    'invnum',
  43                    '_date',
  44                    '', #'pay_amount',
  45                    '', #'credit_amount',
  46                    FS::UI::Web::cust_sort_fields(),
  47                  ],
  48                  'links'       => [
  49                    @pkgnum_null,
  50                    '',
  51                    '',
  52                    @post_desc_null,
  53                    @peritem_null,
  54                    $ilink,
  55                    $ilink,
  56                    $pay_link,
  57                    $credit_link,
  58                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
  59                          FS::UI::Web::cust_header()
  60                    ),
  61                  ],
  62                  #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
  63                  'align' => $pkgnum_align.
  64                             'rl'.
  65                             $post_desc_align.
  66                             $peritem_align.
  67                             'rcrr'.
  68                             FS::UI::Web::cust_aligns(),
  69                  'color' => [
  70                               @pkgnum_null,
  71                               '',
  72                               '',
  73                               @post_desc_null,
  74                               @peritem_null,
  75                               '',
  76                               '',
  77                               '',
  78                               '',
  79                               FS::UI::Web::cust_colors(),
  80                             ],
  81                  'style' => [
  82                               @pkgnum_null,
  83                               '',
  84                               '',
  85                               @post_desc_null,
  86                               @peritem_null,
  87                               '',
  88                               '',
  89                               '',
  90                               '',
  91                               FS::UI::Web::cust_styles(),
  92                             ],
  93 &>
  94 <%doc>
  95
  96 Output control parameters:
  97 - distribute: Boolean.  If true, recurring fees will be "prorated" for the
  98   portion of the package date range (sdate-edate) that falls within the date
  99   range of the report.  Line items will be limited to those for which this
 100   portion is > 0.  This disables filtering on invoice date.
 101
 102 - usage: Separate usage (cust_bill_pkg_detail records) from
 103   recurring charges.  If set to "usage", will show usage instead of
 104   recurring charges.  If set to "recurring", will deduct usage and only
 105   show the flat rate charge.  If not passed, the "recurring charge" column
 106   will include usage charges also.
 107
 108 Filtering parameters:
 109 - begin, end: Date range.  Applies to invoice date, not necessarily package
 110   date range.  But see "distribute".
 111
 112 - status: Customer status (active, suspended, etc.).  This will filter on
 113   _current_ customer status, not status at the time the invoice was generated.
 114
 115 - agentnum: Filter on customer agent.
 116
 117 - refnum: Filter on customer reference source.
 118
 119 - cust_classnum: Filter on customer class.
 120
 121 - classnum: Filter on package class.
 122
 123 - report_optionnum: Filter on package report class.  Can be a single report
 124   class number or a comma-separated list (where 0 is "no report class"), or the
 125   word "multiple".
 126
 127 - use_override: Apply "classnum" and "taxclass" filtering based on the
 128   override (bundle) pkgpart, rather than always using the true pkgpart.
 129
 130 - nottax: Limit to items that are not taxes (pkgnum > 0 or feepart > 0).
 131
 132 - istax: Limit to items that are taxes (pkgnum == 0 and feepart = null).
 133
 134 - taxnum: Limit to items whose tax definition matches this taxnum.
 135   With "nottax" that means items that are subject to that tax;
 136   with "istax" it's the tax charges themselves.  Can be specified
 137   more than once to include multiple taxes.
 138
 139 - country, state, county, city: Limit to items whose tax location
 140   matches these fields.  If "nottax" it's the tax location of the package;
 141   if "istax" the location of the tax.
 142
 143 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
 144   matches a tax with this name.  With "istax", limit to items that have
 145   this tax name.  taxnameNULL is equivalent to "taxname = '' OR taxname
 146   = 'Tax'".
 147
 148 - out: With "nottax", limit to items that don't match any tax definition.
 149   With "istax", find tax items that are unlinked to their tax definitions.
 150   Current Freeside (> July 2012) always creates tax links, but unlinked
 151   items may result from an incomplete upgrade of legacy data.
 152
 153 - locationtaxid: With "nottax", limit to packages matching this
 154   tax_rate_location ID; with "tax", limit to taxes generated from that
 155   location.
 156
 157 - taxclass: Filter on package taxclass.
 158
 159 - taxclassNULL: With "nottax", limit to items that would be subject to the
 160   tax with taxclass = NULL.  This doesn't necessarily mean part_pkg.taxclass
 161   is NULL; it also includes taxclasses that don't have a tax in this region.
 162
 163 - itemdesc: Limit to line items with this description.  Note that non-tax
 164   packages usually have a description of NULL.  (Deprecated.)
 165
 166 - report_group: Can contain '=' or '!=' followed by a string to limit to
 167   line items where itemdesc starts with, or doesn't start with, the string.
 168
 169 - cust_tax: Limit to customers who are tax-exempt.  If "taxname" is also
 170   specified, limit to customers who are also specifically exempt from that
 171   tax.
 172
 173 - pkg_tax: Limit to packages that are tax-exempt, and only include the
 174   exempt portion (setup, recurring, or both) when calculating totals.
 175
 176 - taxable: Limit to packages that are subject to tax, i.e. where a
 177   cust_bill_pkg_tax_location record exists.
 178
 179 - credit: Limit to line items that received a credit application.  The
 180   amount of the credit will also be shown.
 181
 182 </%doc>
 183 <%init>
 184
 185 my $curuser = $FS::CurrentUser::CurrentUser;
 186
 187 die "access denied" unless $curuser->access_right('Financial reports');
 188
 189 my $conf = new FS::Conf;
 190 my $money_char = $conf->config('money_char') || '$';
 191
 192 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
 193 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
 194 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
 195
 196 my @peritem = ( 'setup', 'recur' );
 197 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
 198
 199 my @pkgnum_header = ();
 200 my @pkgnum = ();
 201 my @pkgnum_null;
 202 my $pkgnum_align = '';
 203 if ( $curuser->option('show_pkgnum') ) {
 204   push @select, 'cust_bill_pkg.pkgnum';
 205   push @pkgnum_header, 'Pkg Num';
 206   push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
 207   push @pkgnum_null, '';
 208   $pkgnum_align .= 'r';
 209 }
 210
 211 my @post_desc_header = ();
 212 my @post_desc = ();
 213 my @post_desc_null = ();
 214 my $post_desc_align = '';
 215 if ( $conf->exists('enable_taxclasses') ) {
 216   push @post_desc_header, 'Tax class';
 217   push @post_desc, 'taxclass';
 218   push @post_desc_null, '';
 219   $post_desc_align .= 'l';
 220 }
 221
 222 # used in several places
 223 my $itemdesc = 'COALESCE(part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)';
 224
 225 # valid in both the tax and non-tax cases
 226 my $join_cust =
 227   " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
 228   # use cust_pkg.locationnum if it exists
 229   FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 230
 231 #agent virtualization
 232 my $agentnums_sql =
 233   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
 234
 235 my @where = ( $agentnums_sql );
 236
 237 # date range
 238 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 239
 240 if ( $cgi->param('distribute') == 1 ) {
 241   push @where, "sdate <= $ending",
 242                "edate >  $beginning",
 243   ;
 244 } else {
 245   push @where, "cust_bill._date >= $beginning",
 246                "cust_bill._date <= $ending";
 247 }
 248
 249 # status
 250 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
 251   push @where, FS::cust_main->cust_status_sql . " = '$1'";
 252 }
 253
 254 # agentnum
 255 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 256   push @where, "cust_main.agentnum = $1";
 257 }
 258
 259 # salesnum--see below
 260 # refnum
 261 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
 262   push @where, "cust_main.refnum = $1";
 263 }
 264
 265 # 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)
 266 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
 267   my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
 268   push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
 269                    join(',', map { $_ || '0' } @classnums ).
 270                ' )'
 271     if @classnums;
 272 }
 273
 274
 275 # custnum
 276 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
 277   push @where, "cust_main.custnum = $1";
 278 }
 279
 280 # we want the package and its definition if available
 281 my $join_pkg =
 282 ' LEFT JOIN cust_pkg      USING (pkgnum)
 283   LEFT JOIN part_pkg      USING (pkgpart)
 284   LEFT JOIN part_fee      USING (feepart)';
 285
 286 my $part_pkg = 'part_pkg';
 287 # "Separate sub-packages from parents"
 288 my $use_override = $cgi->param('use_override') ? 1 : 0;
 289 if ( $use_override ) {
 290   # still need the real part_pkg for tax applicability,
 291   # so alias this one
 292   $join_pkg .= " LEFT JOIN part_pkg AS override ON (
 293   COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
 294   )";
 295   $part_pkg = 'override';
 296 }
 297 push @select, "$part_pkg.pkgpart", "$part_pkg.pkg";
 298 push @select, "COALESCE($part_pkg.taxclass, part_fee.taxclass) AS taxclass"
 299   if $conf->exists('enable_taxclasses');
 300
 301 # the non-tax case
 302 if ( $cgi->param('nottax') ) {
 303
 304   push @select, "($itemdesc) AS itemdesc";
 305
 306   push @where,
 307     '(cust_bill_pkg.pkgnum > 0 OR cust_bill_pkg.feepart IS NOT NULL)';
 308
 309   my @tax_where; # will go into a subquery
 310   my @exempt_where; # will also go into a subquery
 311
 312   # classnum (of override pkgpart if applicable)
 313   # not specified: all classes
 314   # 0: empty class
 315   # N: classnum
 316   if ( grep { $_ eq 'classnum' } $cgi->param ) {
 317     my @classnums = grep /^\d+$/, $cgi->param('classnum');
 318     push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
 319                      join(',', @classnums ).
 320                  ' )'
 321       if @classnums;
 322   }
 323
 324   if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
 325     my $num = join(',', grep /^[\d,]+$/, $cgi->param('report_optionnum'));
 326     my $not_num = join(',', grep /^[\d,]+$/, $cgi->param('not_report_optionnum'));
 327     my $all = $cgi->param('all_report_options') ? 1 : 0;
 328     push @where, # code reuse FTW
 329       FS::Report::Table->with_report_option(
 330         report_optionnum      => $num,
 331         not_report_optionnum  => $not_num,
 332         use_override          => $use_override,
 333         all_report_options    => $all,
 334       );
 335   }
 336
 337   # taxclass
 338   if ( $cgi->param('taxclassNULL') ) {
 339     # a little different from 'taxclass' in that it applies to the
 340     # effective taxclass, not the real one
 341     push @tax_where, 'cust_main_county.taxclass IS NULL'
 342   } elsif ( $cgi->param('taxclass') ) {
 343     push @tax_where, "COALESCE(part_fee.taxclass, $part_pkg.taxclass) IN (" .
 344                  join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
 345                  ')';
 346   }
 347
 348   if ( $cgi->param('exempt_cust') eq 'Y' ) {
 349     # tax-exempt customers
 350     push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
 351
 352   } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
 353     # non-taxable package charges
 354     push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
 355   }
 356   # we don't handle exempt_monthly here
 357
 358   if ( $cgi->param('taxname') ) { # specific taxname
 359       push @tax_where, 'COALESCE(cust_main_county.taxname, \'Tax\') = '.
 360                         dbh->quote($cgi->param('taxname'));
 361   }
 362
 363   # country:state:county:city:district (may be repeated)
 364   # You can also pass a big list of taxnums but that leads to huge URLs.
 365   # Note that this means "packages whose tax is in this region", not
 366   # "packages in this region".  It's meant for links from the tax report.
 367   if ( $cgi->param('region') ) {
 368     my @orwhere;
 369     foreach ( $cgi->param('region') ) {
 370       my %loc;
 371       @loc{qw(country state county city district)} =
 372         split(':', $cgi->param('region'));
 373       my $string = join(' AND ',
 374             map {
 375               if ( $loc{$_} ) {
 376                 "$_ = ".dbh->quote($loc{$_});
 377               } else {
 378                 "$_ IS NULL";
 379               }
 380             } keys(%loc)
 381       );
 382       push @orwhere, "($string)";
 383     }
 384     push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
 385   }
 386
 387   # specific taxnums
 388   if ( $cgi->param('taxnum') =~ /^([0-9,]+)$/ ) {
 389     push @tax_where, "cust_main_county.taxnum IN ($1)";
 390   }
 391
 392   # If we're showing exempt items, we need to find those with
 393   # cust_tax_exempt_pkg records matching the selected taxes.
 394   # If we're showing taxable items, we need to find those with
 395   # cust_bill_pkg_tax_location records.  We also need to find the
 396   # exemption records so that we can show the taxable amount.
 397   # If we're showing all items, we need the union of those.
 398   # If we're showing 'out' (items that aren't region/class taxable),
 399   # then we need the set of all items minus the union of those.
 400
 401   # Always exclude cust_tax_exempt_pkg records with non-NULL creditbillpkgnum.
 402
 403   if ( $cgi->param('out') ) {
 404     # separate from the rest, in that we're not going to join cust_main_county
 405     # in the outer query
 406
 407     my @exclude = ( 'cust_tax_exempt_pkg.billpkgnum',
 408                     'cust_bill_pkg_tax_location.taxable_billpkgnum'
 409                   );
 410     foreach my $col (@exclude) {
 411       my ($table) = split(/\./, $col);
 412       my $this_where = 'WHERE ' .  join(' AND ',
 413         "$col = cust_bill_pkg.billpkgnum",
 414         @tax_where
 415       );
 416
 417       push @where,
 418       "NOT EXISTS(SELECT 1 FROM $table
 419         JOIN cust_main_county USING (taxnum)
 420         $this_where
 421       )";
 422     }
 423
 424   } else {
 425     # everything that returns things joined to a tax definition
 426
 427     my $exempt_sub;
 428     if ( @exempt_where or @tax_where or $cgi->param('taxable') ) {
 429
 430       push @exempt_where, "cust_tax_exempt_pkg.creditbillpkgnum IS NULL";
 431
 432       # process exemption restrictions, including @tax_where
 433       my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
 434       FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
 435
 436       $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where);
 437
 438       $exempt_sub .= ' GROUP BY billpkgnum';
 439
 440       $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
 441       USING (billpkgnum)";
 442     }
 443
 444     if ( @tax_where or $cgi->param('taxable') ) {
 445       # process tax restrictions
 446       unshift @tax_where,
 447         'cust_main_county.tax > 0';
 448
 449       my $tax_sub = "SELECT cust_bill_pkg_tax_location.taxable_billpkgnum
 450       FROM cust_bill_pkg_tax_location
 451       JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
 452       JOIN cust_main_county USING (taxnum)
 453       WHERE ". join(' AND ', @tax_where).
 454       " GROUP BY taxable_billpkgnum";
 455
 456       $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
 457       ON (item_tax.taxable_billpkgnum = cust_bill_pkg.billpkgnum)
 458       ";
 459     }
 460
 461     # now do something with that
 462     if ( $cgi->param('taxable') ) {
 463       # taxable query: needs sale amount - exempt amount
 464
 465       my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
 466                     '- COALESCE(item_exempt.exempt_amount, 0)';
 467
 468       push @where,    'item_tax.taxable_billpkgnum IS NOT NULL';
 469       push @select,   "($taxable) AS taxable_amount";
 470       push @peritem,  'taxable_amount';
 471       push @peritem_desc, 'Taxable';
 472       push @total,    "SUM($taxable)";
 473       push @total_desc, "$money_char%.2f taxable";
 474
 475     } elsif ( $cgi->param('exempt_cust') or $cgi->param('exempt_pkg') ) {
 476
 477       push @where,    'item_exempt.billpkgnum IS NOT NULL';
 478       push @select,   'item_exempt.exempt_amount';
 479       push @peritem,  'exempt_amount';
 480       push @peritem_desc, 'Exempt';
 481       push @total,    'SUM(exempt_amount)';
 482       push @total_desc, "$money_char%.2f tax-exempt";
 483
 484     } elsif ( @tax_where ) {
 485       # union of taxable + all exempt_ cases
 486       push @where,
 487         '(item_tax.taxable_billpkgnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
 488
 489     }
 490
 491   } # handle all joins to cust_main_county
 492
 493   # recur/usage separation
 494   if ( $cgi->param('usage') eq 'recurring' ) {
 495
 496     my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
 497     push @select, "($recur_no_usage) AS recur_no_usage";
 498     $peritem[1] = 'recur_no_usage';
 499     $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
 500     $total_desc[0] .= ' (excluding usage)';
 501
 502   } elsif ( $cgi->param('usage') eq 'usage' ) {
 503
 504     my $usage = FS::cust_bill_pkg->usage_sql();
 505     push @select, "($usage) AS _usage";
 506     # there's already a method named 'usage'
 507     $peritem[1] = '_usage';
 508     $peritem_desc[1] = 'Usage charge';
 509     $total[1] = "SUM($usage)";
 510     $total_desc[0] .= ' usage charges';
 511   }
 512
 513 } elsif ( $cgi->param('istax') ) {
 514
 515   @peritem = ( 'setup' ); # taxes only have setup
 516   @peritem_desc = ( 'Tax charge' );
 517
 518   push @where, 'cust_bill_pkg.pkgnum = 0';
 519
 520   # tax location when using tax_rate_location
 521   if ( $cgi->param('vendortax') ) {
 522
 523     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 524                  ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
 525     foreach (qw( state county city locationtaxid)) {
 526       if ( scalar($cgi->param($_)) ) {
 527         my $place = dbh->quote( $cgi->param($_) );
 528         push @where, "tax_rate_location.$_ = $place";
 529       }
 530     }
 531
 532     $total[1] = 'SUM(
 533       COALESCE(cust_bill_pkg_tax_rate_location.amount,
 534                cust_bill_pkg.setup + cust_bill_pkg.recur)
 535     )';
 536
 537   } else { # the internal-tax case
 538
 539     $join_pkg .= '
 540       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 541       JOIN cust_main_county           USING (taxnum)
 542     ';
 543
 544     # don't double-count the components of consolidated taxes
 545     $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
 546     $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
 547
 548     # taxclass
 549     if ( $cgi->param('taxclassNULL') ) {
 550       push @where, 'cust_main_county.taxclass IS NULL';
 551     }
 552
 553     # taxname
 554     if ( $cgi->param('taxnameNULL') ) {
 555       push @where, 'cust_main_county.taxname IS NULL OR '.
 556                    'cust_main_county.taxname = \'Tax\'';
 557     } elsif ( $cgi->param('taxname') ) {
 558       push @where, 'cust_main_county.taxname = '.
 559                     dbh->quote($cgi->param('taxname'));
 560     }
 561
 562     # specific taxnums
 563     if ( $cgi->param('taxnum') =~ /^([0-9,]+)$/ ) {
 564       push @where, "cust_main_county.taxnum IN ($1)";
 565     }
 566   } # the normal case
 567
 568   # report group (itemdesc)
 569   if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
 570     my ( $group_op, $group_value ) = ( $1, $2 );
 571     if ( $group_op eq '=' ) {
 572       #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
 573       push @where, 'itemdesc = '. dbh->quote($group_value);
 574     } elsif ( $group_op eq '!=' ) {
 575       push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
 576     } else {
 577       die "guru meditation #00de: group_op $group_op\n";
 578     }
 579   }
 580
 581   # itemdesc, for breakdown from the vendor tax report
 582   if ( $cgi->param('itemdesc') ) {
 583     if ( $cgi->param('itemdesc') eq 'Tax' ) {
 584       push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
 585     } else {
 586       push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
 587     }
 588   }
 589
 590   # classnum (of underlying package)
 591   # not specified: all classes
 592   # 0: empty class
 593   # N: classnum
 594   if ( grep { $_ eq 'classnum' } $cgi->param ) {
 595     my @classnums = grep /^\d+$/, $cgi->param('classnum');
 596     push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ".
 597                      join(',', @classnums ).
 598                  ' )'
 599       if @classnums;
 600   }
 601
 602 } # nottax / istax
 603
 604
 605 #total payments
 606 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 607                  FROM cust_bill_pay_pkg
 608                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 609               ";
 610 push @select, "($pay_sub) AS pay_amount";
 611
 612
 613 # credit
 614 if ( $cgi->param('credit') ) {
 615
 616   my $credit_where;
 617
 618   my($cr_begin, $cr_end) = FS::UI::Web::parse_beginning_ending($cgi, 'credit');
 619   $credit_where = "WHERE cust_credit_bill._date >= $cr_begin " .
 620                   "AND cust_credit_bill._date <= $cr_end";
 621
 622   my $credit_sub;
 623
 624   if ( $cgi->param('istax') ) {
 625     # then we need to group/join by billpkgtaxlocationnum, to get only the
 626     # relevant part of partial taxes
 627     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 628       reason.reason as reason_text, access_user.username AS username_text,
 629       billpkgtaxlocationnum, billpkgnum
 630     FROM cust_credit_bill_pkg
 631       JOIN cust_credit_bill USING (creditbillnum)
 632       JOIN cust_credit USING (crednum)
 633       LEFT JOIN reason USING (reasonnum)
 634       LEFT JOIN access_user USING (usernum)
 635     $credit_where
 636     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
 637       access_user.username";
 638
 639     if ( $cgi->param('out') ) {
 640
 641       # find credits that are applied to the line items, but not to
 642       # a cust_bill_pkg_tax_location link
 643       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 644         USING (billpkgnum)";
 645       push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 646
 647     } else {
 648
 649       # find credits that are applied to the CBPTL links that are
 650       # considered "interesting" by the report criteria
 651       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 652         USING (billpkgtaxlocationnum)";
 653
 654     }
 655
 656   } else {
 657     # then only group by billpkgnum
 658     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 659       reason.reason as reason_text, access_user.username AS username_text,
 660       billpkgnum
 661     FROM cust_credit_bill_pkg
 662       JOIN cust_credit_bill USING (creditbillnum)
 663       JOIN cust_credit USING (crednum)
 664       LEFT JOIN reason USING (reasonnum)
 665       LEFT JOIN access_user USING (usernum)
 666     $credit_where
 667     GROUP BY billpkgnum, reason.reason, access_user.username";
 668     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
 669   }
 670
 671   push @where,    'item_credit.billpkgnum IS NOT NULL';
 672   push @select,   'item_credit.credit_amount',
 673                   'item_credit.username_text',
 674                   'item_credit.reason_text';
 675   push @peritem,  'credit_amount', 'username_text', 'reason_text';
 676   push @peritem_desc, 'Credited', 'By', 'Reason';
 677   push @total,    'SUM(credit_amount)';
 678   push @total_desc, "$money_char%.2f credited";
 679
 680 } else {
 681
 682   #still want a credit total column
 683
 684   my $credit_sub = "
 685     SELECT SUM(cust_credit_bill_pkg.amount)
 686       FROM cust_credit_bill_pkg
 687         WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 688   ";
 689   push @select, "($credit_sub) AS credit_amount";
 690
 691 }
 692
 693 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 694
 695 #salesnum
 696 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
 697
 698   my $salesnum = $1;
 699   my $sales = FS::sales->by_key($salesnum)
 700     or die "salesnum $salesnum not found";
 701
 702   my $subsearch = $sales->cust_bill_pkg_search('', '',
 703     'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
 704     'paid'            => ($cgi->param('paid') ? 1 : 0),
 705     'classnum'        => scalar($cgi->param('classnum'))
 706   );
 707   $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_fee.classnum, part_pkg.classnum, 0) )";
 708
 709   my $extra_sql = $subsearch->{extra_sql};
 710   $extra_sql =~ s/^WHERE//;
 711   push @where, $extra_sql;
 712
 713   $cgi->param('classnum', 0) unless $cgi->param('classnum');
 714 }
 715
 716
 717 my $where = join(' AND ', @where);
 718 $where &&= "WHERE $where";
 719
 720 my $query = {
 721   'table'     => 'cust_bill_pkg',
 722   'addl_from' => "$join_pkg $join_cust",
 723   'hashref'   => {},
 724   'select'    => join(",\n", @select ),
 725   'extra_sql' => $where,
 726   'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 727 };
 728
 729 my $count_query =
 730   'SELECT ' . join(',', @total) .
 731   " FROM cust_bill_pkg $join_pkg $join_cust
 732   $where";
 733
 734 @peritem_desc = map {emt($_)} @peritem_desc;
 735 my @peritem_sub = map {
 736   my $field = $_;
 737   if ($field =~ /_text$/) { # kludge for credit reason/username fields
 738     sub {$_[0]->get($field)};
 739   } else {
 740     sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
 741   }
 742 } @peritem;
 743 my @peritem_null = map { '' } @peritem; # placeholders
 744 my $peritem_align = 'r' x scalar(@peritem);
 745
 746 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 747 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 748
 749 my $pay_link    = ''; #[, 'billpkgnum', ];
 750 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 751
 752 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 753   if $cgi->param('debug');
 754
 755 </%init>