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                            # possibly use override.pkg but i think this correct
  23                            ? $_[0]->get('pkgpart')
  24                            : ''
  25                        },
  26                    sub { $_[0]->pkgnum > 0
  27                            # possibly use override.pkg but i think this correct
  28                            ? $_[0]->get('pkg')
  29                            : $_[0]->get('itemdesc')
  30                        },
  31                    @post_desc,
  32                    #strikethrough or "N/A ($amount)" or something these when
  33                    # they're not applicable to pkg_tax search
  34                    @peritem_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                    'invnum',
  48                    '_date',
  49                    #'pay_amount',
  50                    #'credit_amount',
  51                  ],
  52                  'links'       => [
  53                    @pkgnum_null,
  54                    '',
  55                    '',
  56                    @post_desc_null,
  57                    @peritem_null,
  58                    $ilink,
  59                    $ilink,
  60                    $pay_link,
  61                    $credit_link,
  62                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
  63                          FS::UI::Web::cust_header()
  64                    ),
  65                  ],
  66                  #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
  67                  'align' => $pkgnum_align.
  68                             'rl'.
  69                             $post_desc_align.
  70                             $peritem_align.
  71                             'rcrr'.
  72                             FS::UI::Web::cust_aligns(),
  73                  'color' => [
  74                               @pkgnum_null,
  75                               '',
  76                               '',
  77                               @post_desc_null,
  78                               @peritem_null,
  79                               '',
  80                               '',
  81                               '',
  82                               '',
  83                               FS::UI::Web::cust_colors(),
  84                             ],
  85                  'style' => [
  86                               @pkgnum_null,
  87                               '',
  88                               '',
  89                               @post_desc_null,
  90                               @peritem_null,
  91                               '',
  92                               '',
  93                               '',
  94                               '',
  95                               FS::UI::Web::cust_styles(),
  96                             ],
  97 &>
  98 <%doc>
  99
 100 Output control parameters:
 101 - distribute: Boolean.  If true, recurring fees will be "prorated" for the
 102   portion of the package date range (sdate-edate) that falls within the date
 103   range of the report.  Line items will be limited to those for which this
 104   portion is > 0.  This disables filtering on invoice date.
 105
 106 - usage: Separate usage (cust_bill_pkg_detail records) from
 107   recurring charges.  If set to "usage", will show usage instead of
 108   recurring charges.  If set to "recurring", will deduct usage and only
 109   show the flat rate charge.  If not passed, the "recurring charge" column
 110   will include usage charges also.
 111
 112 Filtering parameters:
 113 - begin, end: Date range.  Applies to invoice date, not necessarily package
 114   date range.  But see "distribute".
 115
 116 - status: Customer status (active, suspended, etc.).  This will filter on
 117   _current_ customer status, not status at the time the invoice was generated.
 118
 119 - agentnum: Filter on customer agent.
 120
 121 - refnum: Filter on customer reference source.
 122
 123 - cust_classnum: Filter on customer class.
 124
 125 - classnum: Filter on package class.
 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).
 131
 132 - istax: Limit to items that are taxes (pkgnum == 0).
 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   push @select, 'part_pkg.taxclass'; # or should this use override?
 221 }
 222
 223 # valid in both the tax and non-tax cases
 224 my $join_cust =
 225   " LEFT JOIN cust_bill USING (invnum)".
 226   # use cust_pkg.locationnum if it exists
 227   FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 228
 229 #agent virtualization
 230 my $agentnums_sql =
 231   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
 232
 233 my @where = ( $agentnums_sql );
 234
 235 # date range
 236 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 237
 238 if ( $cgi->param('distribute') == 1 ) {
 239   push @where, "sdate <= $ending",
 240                "edate >  $beginning",
 241   ;
 242 }
 243 else {
 244   push @where, "cust_bill._date >= $beginning",
 245                "cust_bill._date <= $ending";
 246 }
 247
 248 # status
 249 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
 250   push @where, FS::cust_main->cust_status_sql . " = '$1'";
 251 }
 252
 253 # agentnum
 254 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 255   push @where, "cust_main.agentnum = $1";
 256 }
 257
 258 # refnum
 259 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
 260   push @where, "cust_main.refnum = $1";
 261 }
 262
 263 # 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)
 264 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
 265   my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
 266   push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
 267                    join(',', map { $_ || '0' } @classnums ).
 268                ' )'
 269     if @classnums;
 270 }
 271
 272
 273 # custnum
 274 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
 275   push @where, "cust_main.custnum = $1";
 276 }
 277
 278 # we want the package and its definition if available
 279 my $join_pkg =
 280 ' LEFT JOIN cust_pkg      USING (pkgnum)
 281   LEFT JOIN part_pkg      USING (pkgpart)';
 282
 283 my $part_pkg = 'part_pkg';
 284 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
 285   # still need the real part_pkg for tax applicability,
 286   # so alias this one
 287   $join_pkg .= " LEFT JOIN part_pkg AS override ON (
 288   COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
 289   )";
 290   $part_pkg = 'override';
 291 }
 292 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
 293
 294 # the non-tax case
 295 if ( $cgi->param('nottax') ) {
 296
 297   push @where, 'cust_bill_pkg.pkgnum > 0';
 298
 299   my @tax_where; # will go into a subquery
 300   my @exempt_where; # will also go into a subquery
 301
 302   # classnum (of override pkgpart if applicable)
 303   # not specified: all classes
 304   # 0: empty class
 305   # N: classnum
 306   if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
 307     push @where, "COALESCE($part_pkg.classnum, 0) = $1";
 308   }
 309
 310   # taxclass
 311   if ( $cgi->param('taxclassNULL') ) {
 312     # a little different from 'taxclass' in that it applies to the
 313     # effective taxclass, not the real one
 314     push @tax_where, 'cust_main_county.taxclass IS NULL'
 315   } elsif ( $cgi->param('taxclass') ) {
 316     push @tax_where, "$part_pkg.taxclass IN (" .
 317                  join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
 318                  ')';
 319   }
 320
 321   if ( $cgi->param('exempt_cust') eq 'Y' ) {
 322     # tax-exempt customers
 323     push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
 324
 325   } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
 326     # non-taxable package charges
 327     push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
 328   }
 329   # we don't handle exempt_monthly here
 330
 331   if ( $cgi->param('taxname') ) { # specific taxname
 332       push @tax_where, 'cust_main_county.taxname = '.
 333                         dbh->quote($cgi->param('taxname'));
 334   } elsif ( $cgi->param('taxnameNULL') ) {
 335       push @tax_where, 'cust_main_county.taxname IS NULL OR '.
 336                        'cust_main_county.taxname = \'Tax\'';
 337   }
 338
 339   # country:state:county:city:district (may be repeated)
 340   # You can also pass a big list of taxnums but that leads to huge URLs.
 341   # Note that this means "packages whose tax is in this region", not
 342   # "packages in this region".  It's meant for links from the tax report.
 343   if ( $cgi->param('region') ) {
 344     my @orwhere;
 345     foreach ( $cgi->param('region') ) {
 346       my %loc;
 347       @loc{qw(country state county city district)} =
 348         split(':', $cgi->param('region'));
 349       my $string = join(' AND ',
 350             map {
 351               if ( $loc{$_} ) {
 352                 "$_ = ".dbh->quote($loc{$_});
 353               } else {
 354                 "$_ IS NULL";
 355               }
 356             } keys(%loc)
 357       );
 358       push @orwhere, "($string)";
 359     }
 360     push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
 361   }
 362
 363   # specific taxnums
 364   if ( $cgi->param('taxnum') ) {
 365     my $taxnum_in = join(',',
 366       grep /^\d+$/, $cgi->param('taxnum')
 367     );
 368     push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
 369       if $taxnum_in;
 370   }
 371
 372   # If we're showing exempt items, we need to find those with
 373   # cust_tax_exempt_pkg records matching the selected taxes.
 374   # If we're showing taxable items, we need to find those with
 375   # cust_bill_pkg_tax_location records.  We also need to find the
 376   # exemption records so that we can show the taxable amount.
 377   # If we're showing all items, we need the union of those.
 378   # If we're showing 'out' (items that aren't region/class taxable),
 379   # then we need the set of all items minus the union of those.
 380
 381   my $exempt_sub;
 382
 383   if ( @exempt_where or @tax_where
 384     or $cgi->param('taxable') or $cgi->param('out') )
 385   {
 386     # process exemption restrictions, including @tax_where
 387     my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
 388     FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
 389
 390     $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
 391       if (@tax_where or @exempt_where);
 392
 393     $exempt_sub .= ' GROUP BY billpkgnum';
 394
 395     $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
 396     USING (billpkgnum)";
 397   }
 398
 399   if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
 400     # process tax restrictions
 401     unshift @tax_where,
 402       'cust_main_county.tax > 0';
 403
 404     my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
 405     FROM cust_bill_pkg_tax_location
 406     JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
 407     JOIN cust_main_county USING (taxnum)
 408     WHERE ". join(' AND ', @tax_where).
 409     " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
 410
 411     $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
 412     ON (item_tax.invnum = cust_bill_pkg.invnum AND
 413         item_tax.pkgnum = cust_bill_pkg.pkgnum)";
 414   }
 415
 416   # now do something with that
 417   if ( @exempt_where ) {
 418
 419     push @where,    'item_exempt.billpkgnum IS NOT NULL';
 420     push @select,   'item_exempt.exempt_amount';
 421     push @peritem,  'exempt_amount';
 422     push @peritem_desc, 'Exempt';
 423     push @total,    'SUM(exempt_amount)';
 424     push @total_desc, "$money_char%.2f tax-exempt";
 425
 426   } elsif ( $cgi->param('taxable') ) {
 427
 428     my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
 429                   '- COALESCE(item_exempt.exempt_amount, 0)';
 430
 431     push @where,    'item_tax.invnum IS NOT NULL';
 432     push @select,   "($taxable) AS taxable_amount";
 433     push @peritem,  'taxable_amount';
 434     push @peritem_desc, 'Taxable';
 435     push @total,    "SUM($taxable)";
 436     push @total_desc, "$money_char%.2f taxable";
 437
 438   } elsif ( $cgi->param('out') ) {
 439
 440     push @where,    'item_tax.invnum IS NULL',
 441                     'item_exempt.billpkgnum IS NULL';
 442
 443   } elsif ( @tax_where ) {
 444
 445     # union of taxable + all exempt_ cases
 446     push @where,
 447       '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
 448
 449   }
 450
 451   # recur/usage separation
 452   if ( $cgi->param('usage') eq 'recurring' ) {
 453
 454     my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
 455     push @select, "($recur_no_usage) AS recur_no_usage";
 456     $peritem[1] = 'recur_no_usage';
 457     $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
 458     $total_desc[0] .= ' (excluding usage)';
 459
 460   } elsif ( $cgi->param('usage') eq 'usage' ) {
 461
 462     my $usage = FS::cust_bill_pkg->usage_sql();
 463     push @select, "($usage) AS _usage";
 464     # there's already a method named 'usage'
 465     $peritem[1] = '_usage';
 466     $peritem_desc[1] = 'Usage charge';
 467     $total[1] = "SUM($usage)";
 468     $total_desc[0] .= ' usage charges';
 469   }
 470
 471 } elsif ( $cgi->param('istax') ) {
 472
 473   @peritem = ( 'setup' ); # taxes only have setup
 474   @peritem_desc = ( 'Tax charge' );
 475
 476   push @where, 'cust_bill_pkg.pkgnum = 0';
 477
 478   # tax location when using tax_rate_location
 479   if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 480
 481     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 482                  ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
 483     push @where, FS::tax_rate_location->location_sql(
 484                    map { $_ => (scalar($cgi->param($_)) || '') }
 485                      qw( district city county state locationtaxid )
 486                  );
 487
 488     $total[1] = 'SUM(
 489       COALESCE(cust_bill_pkg_tax_rate_location.amount,
 490                cust_bill_pkg.setup + cust_bill_pkg.recur)
 491     )';
 492
 493   } elsif ( $cgi->param('out') ) {
 494
 495     $join_pkg .= '
 496       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 497     ';
 498     push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
 499
 500     # each billpkgnum should appear only once
 501     $total[0] = 'COUNT(*)';
 502     $total[1] = 'SUM(cust_bill_pkg.setup)';
 503
 504   } else { # not locationtaxid or 'out'--the normal case
 505
 506     $join_pkg .= '
 507       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 508       JOIN cust_main_county           USING (taxnum)
 509     ';
 510
 511     # don't double-count the components of consolidated taxes
 512     $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
 513     $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
 514   }
 515
 516   # taxclass
 517   if ( $cgi->param('taxclassNULL') ) {
 518     push @where, 'cust_main_county.taxclass IS NULL';
 519   }
 520
 521   # taxname
 522   if ( $cgi->param('taxnameNULL') ) {
 523     push @where, 'cust_main_county.taxname IS NULL OR '.
 524                  'cust_main_county.taxname = \'Tax\'';
 525   } elsif ( $cgi->param('taxname') ) {
 526     push @where, 'cust_main_county.taxname = '.
 527                   dbh->quote($cgi->param('taxname'));
 528   }
 529
 530   # specific taxnums
 531   if ( $cgi->param('taxnum') ) {
 532     my $taxnum_in = join(',',
 533       grep /^\d+$/, $cgi->param('taxnum')
 534     );
 535     push @where, "cust_main_county.taxnum IN ($taxnum_in)"
 536       if $taxnum_in;
 537   }
 538
 539   # report group (itemdesc)
 540   if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
 541     my ( $group_op, $group_value ) = ( $1, $2 );
 542     if ( $group_op eq '=' ) {
 543       #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
 544       push @where, 'itemdesc = '. dbh->quote($group_value);
 545     } elsif ( $group_op eq '!=' ) {
 546       push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
 547     } else {
 548       die "guru meditation #00de: group_op $group_op\n";
 549     }
 550   }
 551
 552   # itemdesc, for some reason
 553   if ( $cgi->param('itemdesc') ) {
 554     if ( $cgi->param('itemdesc') eq 'Tax' ) {
 555       push @where, "(itemdesc='Tax' OR itemdesc is null)";
 556     } else {
 557       push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
 558     }
 559   }
 560
 561 } # nottax / istax
 562
 563
 564 #total payments
 565 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 566                  FROM cust_bill_pay_pkg
 567                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 568               ";
 569 push @select, "($pay_sub) AS pay_amount";
 570
 571
 572 # credit
 573 if ( $cgi->param('credit') ) {
 574
 575   my $credit_sub;
 576
 577   if ( $cgi->param('istax') ) {
 578     # then we need to group/join by billpkgtaxlocationnum, to get only the
 579     # relevant part of partial taxes
 580     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 581       reason.reason as reason_text, access_user.username AS username_text,
 582       billpkgtaxlocationnum, billpkgnum
 583     FROM cust_credit_bill_pkg
 584       JOIN cust_credit_bill USING (creditbillnum)
 585       JOIN cust_credit USING (crednum)
 586       LEFT JOIN reason USING (reasonnum)
 587       LEFT JOIN access_user USING (usernum)
 588     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
 589       access_user.username";
 590
 591     if ( $cgi->param('out') ) {
 592
 593       # find credits that are applied to the line items, but not to
 594       # a cust_bill_pkg_tax_location link
 595       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 596         USING (billpkgnum)";
 597       push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 598
 599     } else {
 600
 601       # find credits that are applied to the CBPTL links that are
 602       # considered "interesting" by the report criteria
 603       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 604         USING (billpkgtaxlocationnum)";
 605
 606     }
 607
 608   } else {
 609     # then only group by billpkgnum
 610     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 611       reason.reason as reason_text, access_user.username AS username_text,
 612       billpkgnum
 613     FROM cust_credit_bill_pkg
 614       JOIN cust_credit_bill USING (creditbillnum)
 615       JOIN cust_credit USING (crednum)
 616       LEFT JOIN reason USING (reasonnum)
 617       LEFT JOIN access_user USING (usernum)
 618     GROUP BY billpkgnum, reason.reason, access_user.username";
 619     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
 620   }
 621
 622   push @where,    'item_credit.billpkgnum IS NOT NULL';
 623   push @select,   'item_credit.credit_amount',
 624                   'item_credit.username_text',
 625                   'item_credit.reason_text';
 626   push @peritem,  'credit_amount', 'username_text', 'reason_text';
 627   push @peritem_desc, 'Credited', 'By', 'Reason';
 628   push @total,    'SUM(credit_amount)';
 629   push @total_desc, "$money_char%.2f credited";
 630
 631 } else {
 632
 633   #still want a credit total column
 634
 635   my $credit_sub = "
 636     SELECT SUM(cust_credit_bill_pkg.amount)
 637       FROM cust_credit_bill_pkg
 638         WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 639   ";
 640   push @select, "($credit_sub) AS credit_amount";
 641
 642 }
 643
 644 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 645
 646 my $where = join(' AND ', @where);
 647 $where &&= "WHERE $where";
 648
 649 my $query = {
 650   'table'     => 'cust_bill_pkg',
 651   'addl_from' => "$join_pkg $join_cust",
 652   'hashref'   => {},
 653   'select'    => join(",\n", @select ),
 654   'extra_sql' => $where,
 655   'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 656 };
 657
 658 my $count_query =
 659   'SELECT ' . join(',', @total) .
 660   " FROM cust_bill_pkg $join_pkg $join_cust
 661   $where";
 662
 663 @peritem_desc = map {emt($_)} @peritem_desc;
 664 my @peritem_sub = map {
 665   my $field = $_;
 666   if ($field =~ /_text$/) { # kludge for credit reason/username fields
 667     sub {$_[0]->get($field)};
 668   } else {
 669     sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
 670   }
 671 } @peritem;
 672 my @peritem_null = map { '' } @peritem; # placeholders
 673 my $peritem_align = 'r' x scalar(@peritem);
 674
 675 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 676 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 677
 678 my $pay_link    = ''; #[, 'billpkgnum', ];
 679 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 680
 681 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 682   if $cgi->param('debug');
 683
 684 </%init>