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