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