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 - report_optionnum: Filter on package report class.  Can be a single report
 128   class number, a comma-separated list, the word "multiple", or an empty
 129   string (for "no report class").
 130
 131 - use_override: Apply "classnum" and "taxclass" filtering based on the
 132   override (bundle) pkgpart, rather than always using the true pkgpart.
 133
 134 - nottax: Limit to items that are not taxes (pkgnum > 0).
 135
 136 - istax: Limit to items that are taxes (pkgnum == 0).
 137
 138 - taxnum: Limit to items whose tax definition matches this taxnum.
 139   With "nottax" that means items that are subject to that tax;
 140   with "istax" it's the tax charges themselves.  Can be specified
 141   more than once to include multiple taxes.
 142
 143 - country, state, county, city: Limit to items whose tax location
 144   matches these fields.  If "nottax" it's the tax location of the package;
 145   if "istax" the location of the tax.
 146
 147 - taxname, taxnameNULL: With "nottax", limit to items whose tax location
 148   matches a tax with this name.  With "istax", limit to items that have
 149   this tax name.  taxnameNULL is equivalent to "taxname = '' OR taxname
 150   = 'Tax'".
 151
 152 - out: With "nottax", limit to items that don't match any tax definition.
 153   With "istax", find tax items that are unlinked to their tax definitions.
 154   Current Freeside (> July 2012) always creates tax links, but unlinked
 155   items may result from an incomplete upgrade of legacy data.
 156
 157 - locationtaxid: With "nottax", limit to packages matching this
 158   tax_rate_location ID; with "tax", limit to taxes generated from that
 159   location.
 160
 161 - taxclass: Filter on package taxclass.
 162
 163 - taxclassNULL: With "nottax", limit to items that would be subject to the
 164   tax with taxclass = NULL.  This doesn't necessarily mean part_pkg.taxclass
 165   is NULL; it also includes taxclasses that don't have a tax in this region.
 166
 167 - itemdesc: Limit to line items with this description.  Note that non-tax
 168   packages usually have a description of NULL.  (Deprecated.)
 169
 170 - report_group: Can contain '=' or '!=' followed by a string to limit to
 171   line items where itemdesc starts with, or doesn't start with, the string.
 172
 173 - cust_tax: Limit to customers who are tax-exempt.  If "taxname" is also
 174   specified, limit to customers who are also specifically exempt from that
 175   tax.
 176
 177 - pkg_tax: Limit to packages that are tax-exempt, and only include the
 178   exempt portion (setup, recurring, or both) when calculating totals.
 179
 180 - taxable: Limit to packages that are subject to tax, i.e. where a
 181   cust_bill_pkg_tax_location record exists.
 182
 183 - credit: Limit to line items that received a credit application.  The
 184   amount of the credit will also be shown.
 185
 186 </%doc>
 187 <%init>
 188
 189 my $curuser = $FS::CurrentUser::CurrentUser;
 190
 191 die "access denied" unless $curuser->access_right('Financial reports');
 192
 193 my $conf = new FS::Conf;
 194 my $money_char = $conf->config('money_char') || '$';
 195
 196 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
 197 my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
 198 my @total_desc = ( $money_char.'%.2f total' ); # sprintf strings
 199
 200 my @peritem = ( 'setup', 'recur' );
 201 my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
 202
 203 my @pkgnum_header = ();
 204 my @pkgnum = ();
 205 my @pkgnum_null;
 206 my $pkgnum_align = '';
 207 if ( $curuser->option('show_pkgnum') ) {
 208   push @select, 'cust_bill_pkg.pkgnum';
 209   push @pkgnum_header, 'Pkg Num';
 210   push @pkgnum, sub { $_[0]->pkgnum > 0 ? $_[0]->pkgnum : '' };
 211   push @pkgnum_null, '';
 212   $pkgnum_align .= 'r';
 213 }
 214
 215 my @post_desc_header = ();
 216 my @post_desc = ();
 217 my @post_desc_null = ();
 218 my $post_desc_align = '';
 219 if ( $conf->exists('enable_taxclasses') ) {
 220   push @post_desc_header, 'Tax class';
 221   push @post_desc, 'taxclass';
 222   push @post_desc_null, '';
 223   $post_desc_align .= 'l';
 224   push @select, 'part_pkg.taxclass'; # or should this use override?
 225 }
 226
 227 # valid in both the tax and non-tax cases
 228 my $join_cust =
 229   " LEFT JOIN cust_bill ON (cust_bill_pkg.invnum = cust_bill.invnum)".
 230   # use cust_pkg.locationnum if it exists
 231   FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 232
 233 #agent virtualization
 234 my $agentnums_sql =
 235   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
 236
 237 my @where = ( $agentnums_sql );
 238
 239 # date range
 240 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 241
 242 if ( $cgi->param('distribute') == 1 ) {
 243   push @where, "sdate <= $ending",
 244                "edate >  $beginning",
 245   ;
 246 }
 247 else {
 248   push @where, "cust_bill._date >= $beginning",
 249                "cust_bill._date <= $ending";
 250 }
 251
 252 # status
 253 if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
 254   push @where, FS::cust_main->cust_status_sql . " = '$1'";
 255 }
 256
 257 # agentnum
 258 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 259   push @where, "cust_main.agentnum = $1";
 260 }
 261
 262 # refnum
 263 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
 264   push @where, "cust_main.refnum = $1";
 265 }
 266
 267 # 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)
 268 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
 269   my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
 270   push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
 271                    join(',', map { $_ || '0' } @classnums ).
 272                ' )'
 273     if @classnums;
 274 }
 275
 276
 277 # custnum
 278 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
 279   push @where, "cust_main.custnum = $1";
 280 }
 281
 282 # we want the package and its definition if available
 283 my $join_pkg =
 284 ' LEFT JOIN cust_pkg      USING (pkgnum)
 285   LEFT JOIN part_pkg      USING (pkgpart)';
 286
 287 my $part_pkg = 'part_pkg';
 288 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
 289   # still need the real part_pkg for tax applicability,
 290   # so alias this one
 291   $join_pkg .= " LEFT JOIN part_pkg AS override ON (
 292   COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
 293   )";
 294   $part_pkg = 'override';
 295 }
 296 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
 297
 298 # the non-tax case
 299 if ( $cgi->param('nottax') ) {
 300
 301   push @where, 'cust_bill_pkg.pkgnum > 0';
 302
 303   my @tax_where; # will go into a subquery
 304   my @exempt_where; # will also go into a subquery
 305
 306   # classnum (of override pkgpart if applicable)
 307   # not specified: all classes
 308   # 0: empty class
 309   # N: classnum
 310   if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
 311     push @where, "COALESCE($part_pkg.classnum, 0) = $1";
 312   }
 313
 314   if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
 315     # code reuse FTW
 316     my $num = $1;
 317     push @where,
 318       FS::Report::Table->with_report_option( $1, $cgi->param('use_override') )
 319     ;
 320   }
 321
 322   # taxclass
 323   if ( $cgi->param('taxclassNULL') ) {
 324     # a little different from 'taxclass' in that it applies to the
 325     # effective taxclass, not the real one
 326     push @tax_where, 'cust_main_county.taxclass IS NULL'
 327   } elsif ( $cgi->param('taxclass') ) {
 328     push @tax_where, "$part_pkg.taxclass IN (" .
 329                  join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
 330                  ')';
 331   }
 332
 333   if ( $cgi->param('exempt_cust') eq 'Y' ) {
 334     # tax-exempt customers
 335     push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
 336
 337   } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
 338     # non-taxable package charges
 339     push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
 340   }
 341   # we don't handle exempt_monthly here
 342
 343   if ( $cgi->param('taxname') ) { # specific taxname
 344       push @tax_where, 'cust_main_county.taxname = '.
 345                         dbh->quote($cgi->param('taxname'));
 346   } elsif ( $cgi->param('taxnameNULL') ) {
 347       push @tax_where, 'cust_main_county.taxname IS NULL OR '.
 348                        'cust_main_county.taxname = \'Tax\'';
 349   }
 350
 351   # country:state:county:city:district (may be repeated)
 352   # You can also pass a big list of taxnums but that leads to huge URLs.
 353   # Note that this means "packages whose tax is in this region", not
 354   # "packages in this region".  It's meant for links from the tax report.
 355   if ( $cgi->param('region') ) {
 356     my @orwhere;
 357     foreach ( $cgi->param('region') ) {
 358       my %loc;
 359       @loc{qw(country state county city district)} =
 360         split(':', $cgi->param('region'));
 361       my $string = join(' AND ',
 362             map {
 363               if ( $loc{$_} ) {
 364                 "$_ = ".dbh->quote($loc{$_});
 365               } else {
 366                 "$_ IS NULL";
 367               }
 368             } keys(%loc)
 369       );
 370       push @orwhere, "($string)";
 371     }
 372     push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
 373   }
 374
 375   # specific taxnums
 376   if ( $cgi->param('taxnum') ) {
 377     my $taxnum_in = join(',',
 378       grep /^\d+$/, $cgi->param('taxnum')
 379     );
 380     push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
 381       if $taxnum_in;
 382   }
 383
 384   # If we're showing exempt items, we need to find those with
 385   # cust_tax_exempt_pkg records matching the selected taxes.
 386   # If we're showing taxable items, we need to find those with
 387   # cust_bill_pkg_tax_location records.  We also need to find the
 388   # exemption records so that we can show the taxable amount.
 389   # If we're showing all items, we need the union of those.
 390   # If we're showing 'out' (items that aren't region/class taxable),
 391   # then we need the set of all items minus the union of those.
 392
 393   my $exempt_sub;
 394
 395   if ( @exempt_where or @tax_where
 396     or $cgi->param('taxable') or $cgi->param('out') )
 397   {
 398     # process exemption restrictions, including @tax_where
 399     my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
 400     FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
 401
 402     $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
 403       if (@tax_where or @exempt_where);
 404
 405     $exempt_sub .= ' GROUP BY billpkgnum';
 406
 407     $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
 408     USING (billpkgnum)";
 409   }
 410
 411   if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
 412     # process tax restrictions
 413     unshift @tax_where,
 414       'cust_main_county.tax > 0';
 415
 416     my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
 417     FROM cust_bill_pkg_tax_location
 418     JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
 419     JOIN cust_main_county USING (taxnum)
 420     WHERE ". join(' AND ', @tax_where).
 421     " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
 422
 423     $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
 424     ON (item_tax.invnum = cust_bill_pkg.invnum AND
 425         item_tax.pkgnum = cust_bill_pkg.pkgnum)";
 426   }
 427
 428   # now do something with that
 429   if ( @exempt_where ) {
 430
 431     push @where,    'item_exempt.billpkgnum IS NOT NULL';
 432     push @select,   'item_exempt.exempt_amount';
 433     push @peritem,  'exempt_amount';
 434     push @peritem_desc, 'Exempt';
 435     push @total,    'SUM(exempt_amount)';
 436     push @total_desc, "$money_char%.2f tax-exempt";
 437
 438   } elsif ( $cgi->param('taxable') ) {
 439
 440     my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
 441                   '- COALESCE(item_exempt.exempt_amount, 0)';
 442
 443     push @where,    'item_tax.invnum IS NOT NULL';
 444     push @select,   "($taxable) AS taxable_amount";
 445     push @peritem,  'taxable_amount';
 446     push @peritem_desc, 'Taxable';
 447     push @total,    "SUM($taxable)";
 448     push @total_desc, "$money_char%.2f taxable";
 449
 450   } elsif ( $cgi->param('out') ) {
 451
 452     push @where,    'item_tax.invnum IS NULL',
 453                     'item_exempt.billpkgnum IS NULL';
 454
 455   } elsif ( @tax_where ) {
 456
 457     # union of taxable + all exempt_ cases
 458     push @where,
 459       '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
 460
 461   }
 462
 463   # recur/usage separation
 464   if ( $cgi->param('usage') eq 'recurring' ) {
 465
 466     my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
 467     push @select, "($recur_no_usage) AS recur_no_usage";
 468     $peritem[1] = 'recur_no_usage';
 469     $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
 470     $total_desc[0] .= ' (excluding usage)';
 471
 472   } elsif ( $cgi->param('usage') eq 'usage' ) {
 473
 474     my $usage = FS::cust_bill_pkg->usage_sql();
 475     push @select, "($usage) AS _usage";
 476     # there's already a method named 'usage'
 477     $peritem[1] = '_usage';
 478     $peritem_desc[1] = 'Usage charge';
 479     $total[1] = "SUM($usage)";
 480     $total_desc[0] .= ' usage charges';
 481   }
 482
 483 } elsif ( $cgi->param('istax') ) {
 484
 485   @peritem = ( 'setup' ); # taxes only have setup
 486   @peritem_desc = ( 'Tax charge' );
 487
 488   push @where, 'cust_bill_pkg.pkgnum = 0';
 489
 490   # tax location when using tax_rate_location
 491   if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 492
 493     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 494                  ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
 495     push @where, FS::tax_rate_location->location_sql(
 496                    map { $_ => (scalar($cgi->param($_)) || '') }
 497                      qw( district city county state locationtaxid )
 498                  );
 499
 500     $total[1] = 'SUM(
 501       COALESCE(cust_bill_pkg_tax_rate_location.amount,
 502                cust_bill_pkg.setup + cust_bill_pkg.recur)
 503     )';
 504
 505   } elsif ( $cgi->param('out') ) {
 506
 507     $join_pkg .= '
 508       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 509     ';
 510     push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
 511
 512     # each billpkgnum should appear only once
 513     $total[0] = 'COUNT(*)';
 514     $total[1] = 'SUM(cust_bill_pkg.setup)';
 515
 516   } else { # not locationtaxid or 'out'--the normal case
 517
 518     $join_pkg .= '
 519       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 520       JOIN cust_main_county           USING (taxnum)
 521     ';
 522
 523     # don't double-count the components of consolidated taxes
 524     $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
 525     $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
 526   }
 527
 528   # taxclass
 529   if ( $cgi->param('taxclassNULL') ) {
 530     push @where, 'cust_main_county.taxclass IS NULL';
 531   }
 532
 533   # taxname
 534   if ( $cgi->param('taxnameNULL') ) {
 535     push @where, 'cust_main_county.taxname IS NULL OR '.
 536                  'cust_main_county.taxname = \'Tax\'';
 537   } elsif ( $cgi->param('taxname') ) {
 538     push @where, 'cust_main_county.taxname = '.
 539                   dbh->quote($cgi->param('taxname'));
 540   }
 541
 542   # specific taxnums
 543   if ( $cgi->param('taxnum') ) {
 544     my $taxnum_in = join(',',
 545       grep /^\d+$/, $cgi->param('taxnum')
 546     );
 547     push @where, "cust_main_county.taxnum IN ($taxnum_in)"
 548       if $taxnum_in;
 549   }
 550
 551   # report group (itemdesc)
 552   if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
 553     my ( $group_op, $group_value ) = ( $1, $2 );
 554     if ( $group_op eq '=' ) {
 555       #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
 556       push @where, 'itemdesc = '. dbh->quote($group_value);
 557     } elsif ( $group_op eq '!=' ) {
 558       push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
 559     } else {
 560       die "guru meditation #00de: group_op $group_op\n";
 561     }
 562   }
 563
 564   # itemdesc, for some reason
 565   if ( $cgi->param('itemdesc') ) {
 566     if ( $cgi->param('itemdesc') eq 'Tax' ) {
 567       push @where, "(itemdesc='Tax' OR itemdesc is null)";
 568     } else {
 569       push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
 570     }
 571   }
 572
 573 } # nottax / istax
 574
 575
 576 #total payments
 577 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 578                  FROM cust_bill_pay_pkg
 579                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 580               ";
 581 push @select, "($pay_sub) AS pay_amount";
 582
 583
 584 # credit
 585 if ( $cgi->param('credit') ) {
 586
 587   my $credit_sub;
 588
 589   if ( $cgi->param('istax') ) {
 590     # then we need to group/join by billpkgtaxlocationnum, to get only the
 591     # relevant part of partial taxes
 592     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 593       reason.reason as reason_text, access_user.username AS username_text,
 594       billpkgtaxlocationnum, billpkgnum
 595     FROM cust_credit_bill_pkg
 596       JOIN cust_credit_bill USING (creditbillnum)
 597       JOIN cust_credit USING (crednum)
 598       LEFT JOIN reason USING (reasonnum)
 599       LEFT JOIN access_user USING (usernum)
 600     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
 601       access_user.username";
 602
 603     if ( $cgi->param('out') ) {
 604
 605       # find credits that are applied to the line items, but not to
 606       # a cust_bill_pkg_tax_location link
 607       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 608         USING (billpkgnum)";
 609       push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 610
 611     } else {
 612
 613       # find credits that are applied to the CBPTL links that are
 614       # considered "interesting" by the report criteria
 615       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 616         USING (billpkgtaxlocationnum)";
 617
 618     }
 619
 620   } else {
 621     # then only group by billpkgnum
 622     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 623       reason.reason as reason_text, access_user.username AS username_text,
 624       billpkgnum
 625     FROM cust_credit_bill_pkg
 626       JOIN cust_credit_bill USING (creditbillnum)
 627       JOIN cust_credit USING (crednum)
 628       LEFT JOIN reason USING (reasonnum)
 629       LEFT JOIN access_user USING (usernum)
 630     GROUP BY billpkgnum, reason.reason, access_user.username";
 631     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
 632   }
 633
 634   push @where,    'item_credit.billpkgnum IS NOT NULL';
 635   push @select,   'item_credit.credit_amount',
 636                   'item_credit.username_text',
 637                   'item_credit.reason_text';
 638   push @peritem,  'credit_amount', 'username_text', 'reason_text';
 639   push @peritem_desc, 'Credited', 'By', 'Reason';
 640   push @total,    'SUM(credit_amount)';
 641   push @total_desc, "$money_char%.2f credited";
 642
 643 } else {
 644
 645   #still want a credit total column
 646
 647   my $credit_sub = "
 648     SELECT SUM(cust_credit_bill_pkg.amount)
 649       FROM cust_credit_bill_pkg
 650         WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 651   ";
 652   push @select, "($credit_sub) AS credit_amount";
 653
 654 }
 655
 656 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 657
 658 my $where = join(' AND ', @where);
 659 $where &&= "WHERE $where";
 660
 661 my $query = {
 662   'table'     => 'cust_bill_pkg',
 663   'addl_from' => "$join_pkg $join_cust",
 664   'hashref'   => {},
 665   'select'    => join(",\n", @select ),
 666   'extra_sql' => $where,
 667   'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 668 };
 669
 670 my $count_query =
 671   'SELECT ' . join(',', @total) .
 672   " FROM cust_bill_pkg $join_pkg $join_cust
 673   $where";
 674
 675 @peritem_desc = map {emt($_)} @peritem_desc;
 676 my @peritem_sub = map {
 677   my $field = $_;
 678   if ($field =~ /_text$/) { # kludge for credit reason/username fields
 679     sub {$_[0]->get($field)};
 680   } else {
 681     sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
 682   }
 683 } @peritem;
 684 my @peritem_null = map { '' } @peritem; # placeholders
 685 my $peritem_align = 'r' x scalar(@peritem);
 686
 687 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 688 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 689
 690 my $pay_link    = ''; #[, 'billpkgnum', ];
 691 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 692
 693 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 694   if $cgi->param('debug');
 695
 696 </%init>