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                    'itemdesc', # is part_pkg.pkg if applicable
  27                    @post_desc,
  28                    #strikethrough or "N/A ($amount)" or something these when
  29                    # they're not applicable to pkg_tax search
  30                    @peritem_sub,
  31                    @currency_sub,
  32                    'invnum',
  33                    sub { time2str('%b %d %Y', shift->_date ) },
  34                    sub { sprintf($money_char.'%.2f', shift->get('pay_amount')) },
  35                    sub { sprintf($money_char.'%.2f', shift->get('credit_amount')) },
  36                    \&FS::UI::Web::cust_fields,
  37                  ],
  38                  'sort_fields' => [
  39                    @pkgnum_null,
  40                    '',
  41                    '',
  42                    @post_desc_null,
  43                    @peritem,
  44                    @currency,
  45                    'invnum',
  46                    '_date',
  47                    '', #'pay_amount',
  48                    '', #'credit_amount',
  49                    FS::UI::Web::cust_sort_fields(),
  50                  ],
  51                  'links'       => [
  52                    @pkgnum_null,
  53                    '',
  54                    '',
  55                    @post_desc_null,
  56                    @peritem_null,
  57                    @currency_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                             $currency_align.
  72                             'rcrr'.
  73                             FS::UI::Web::cust_aligns(),
  74                  'color' => [
  75                               @pkgnum_null,
  76                               '',
  77                               '',
  78                               @post_desc_null,
  79                               @peritem_null,
  80                               @currency_null,
  81                               '',
  82                               '',
  83                               '',
  84                               '',
  85                               FS::UI::Web::cust_colors(),
  86                             ],
  87                  'style' => [
  88                               @pkgnum_null,
  89                               '',
  90                               '',
  91                               @post_desc_null,
  92                               @peritem_null,
  93                               @currency_null,
  94                               '',
  95                               '',
  96                               '',
  97                               '',
  98                               FS::UI::Web::cust_styles(),
  99                             ],
 100 &>
 101 <%doc>
 102
 103 Output control parameters:
 104 - distribute: Boolean.  If true, recurring fees will be "prorated" for the
 105   portion of the package date range (sdate-edate) that falls within the date
 106   range of the report.  Line items will be limited to those for which this
 107   portion is > 0.  This disables filtering on invoice date.
 108
 109 - usage: Separate usage (cust_bill_pkg_detail records) from
 110   recurring charges.  If set to "usage", will show usage instead of
 111   recurring charges.  If set to "recurring", will deduct usage and only
 112   show the flat rate charge.  If not passed, the "recurring charge" column
 113   will include usage charges also.
 114
 115 Filtering parameters:
 116 - begin, end: Date range.  Applies to invoice date, not necessarily package
 117   date range.  But see "distribute".
 118
 119 - status: Customer status (active, suspended, etc.).  This will filter on
 120   _current_ customer status, not status at the time the invoice was generated.
 121
 122 - agentnum: Filter on customer agent.
 123
 124 - refnum: Filter on customer reference source.
 125
 126 - cust_classnum: Filter on customer class.
 127
 128 - classnum: Filter on package class.
 129
 130 - report_optionnum: Filter on package report class.  Can be a single report
 131   class number or a comma-separated list (where 0 is "no report class"), or the
 132   word "multiple".
 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 or feepart > 0).
 138
 139 - istax: Limit to items that are taxes (pkgnum == 0 and feepart = null).
 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 a comma-separated
 144   list 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') or return '';
 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 }
 245
 246 # used in several places
 247 my $itemdesc = 'COALESCE(part_fee.itemdesc, part_pkg.pkg, cust_bill_pkg.itemdesc)';
 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, "($itemdesc) AS 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 ( $cgi->param('vendortax') ) {
 513
 514     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 515                  ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
 516     foreach (qw( state county city locationtaxid)) {
 517       if ( scalar($cgi->param($_)) ) {
 518         my $place = dbh->quote( $cgi->param($_) );
 519         push @where, "tax_rate_location.$_ = $place";
 520       }
 521     }
 522
 523     $total[1] = 'SUM(
 524       COALESCE(cust_bill_pkg_tax_rate_location.amount,
 525                cust_bill_pkg.setup + cust_bill_pkg.recur)
 526     )';
 527
 528   } elsif ( $cgi->param('out') ) {
 529
 530     $join_pkg .= '
 531       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 532     ';
 533     push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
 534
 535     # each billpkgnum should appear only once
 536     $total[0] = 'COUNT(*)';
 537     $total[1] = 'SUM(cust_bill_pkg.setup)';
 538
 539   } else { # not locationtaxid or 'out'--the normal case
 540
 541     $join_pkg .= '
 542       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 543       JOIN cust_main_county           USING (taxnum)
 544     ';
 545
 546     # don't double-count the components of consolidated taxes
 547     $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
 548     $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
 549
 550     # package classnum
 551     if ( grep { $_ eq 'classnum' } $cgi->param ) {
 552       my @classnums = grep /^\d*$/, $cgi->param('classnum');
 553       $join_pkg .= '
 554         JOIN cust_pkg AS taxed_pkg
 555           ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
 556         JOIN part_pkg AS taxed_part_pkg
 557           ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
 558       ';
 559       push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
 560                        join(',', @classnums ).
 561                    ' )'
 562         if @classnums;
 563     }
 564   }
 565
 566   # taxclass
 567   if ( $cgi->param('taxclassNULL') ) {
 568     push @where, 'cust_main_county.taxclass IS NULL';
 569   }
 570
 571   # taxname
 572   if ( $cgi->param('taxnameNULL') ) {
 573     push @where, 'cust_main_county.taxname IS NULL OR '.
 574                  'cust_main_county.taxname = \'Tax\'';
 575   } elsif ( $cgi->param('taxname') ) {
 576     push @where, 'cust_main_county.taxname = '.
 577                   dbh->quote($cgi->param('taxname'));
 578   }
 579
 580   # itemdesc, for breakdown from the vendor tax report
 581   if ( $cgi->param('itemdesc') ) {
 582     if ( $cgi->param('itemdesc') eq 'Tax' ) {
 583       push @where, "($itemdesc = 'Tax' OR $itemdesc is null)";
 584     } else {
 585       push @where, "$itemdesc = ". dbh->quote($cgi->param('itemdesc'));
 586     }
 587   }
 588
 589 } # nottax / istax
 590
 591
 592 #total payments
 593 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 594                  FROM cust_bill_pay_pkg
 595                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 596               ";
 597 push @select, "($pay_sub) AS pay_amount";
 598
 599
 600 # credit
 601 if ( $cgi->param('credit') ) {
 602
 603   my $credit_sub;
 604
 605   if ( $cgi->param('istax') ) {
 606     # then we need to group/join by billpkgtaxlocationnum, to get only the
 607     # relevant part of partial taxes
 608     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 609       reason.reason as reason_text, access_user.username AS username_text,
 610       billpkgtaxlocationnum, billpkgnum
 611     FROM cust_credit_bill_pkg
 612       JOIN cust_credit_bill USING (creditbillnum)
 613       JOIN cust_credit USING (crednum)
 614       LEFT JOIN reason USING (reasonnum)
 615       LEFT JOIN access_user USING (usernum)
 616     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
 617       access_user.username";
 618
 619     if ( $cgi->param('out') ) {
 620
 621       # find credits that are applied to the line items, but not to
 622       # a cust_bill_pkg_tax_location link
 623       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 624         USING (billpkgnum)";
 625       push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 626
 627     } else {
 628
 629       # find credits that are applied to the CBPTL links that are
 630       # considered "interesting" by the report criteria
 631       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 632         USING (billpkgtaxlocationnum)";
 633
 634     }
 635
 636   } else {
 637     # then only group by billpkgnum
 638     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 639       reason.reason as reason_text, access_user.username AS username_text,
 640       billpkgnum
 641     FROM cust_credit_bill_pkg
 642       JOIN cust_credit_bill USING (creditbillnum)
 643       JOIN cust_credit USING (crednum)
 644       LEFT JOIN reason USING (reasonnum)
 645       LEFT JOIN access_user USING (usernum)
 646     GROUP BY billpkgnum, reason.reason, access_user.username";
 647     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
 648   }
 649
 650   push @where,    'item_credit.billpkgnum IS NOT NULL';
 651   push @select,   'item_credit.credit_amount',
 652                   'item_credit.username_text',
 653                   'item_credit.reason_text';
 654   push @peritem,  'credit_amount', 'username_text', 'reason_text';
 655   push @peritem_desc, 'Credited', 'By', 'Reason';
 656   push @total,    'SUM(credit_amount)';
 657   push @total_desc, "$money_char%.2f credited";
 658
 659 } else {
 660
 661   #still want a credit total column
 662
 663   my $credit_sub = "
 664     SELECT SUM(cust_credit_bill_pkg.amount)
 665       FROM cust_credit_bill_pkg
 666         WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 667   ";
 668   push @select, "($credit_sub) AS credit_amount";
 669
 670 }
 671
 672 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 673
 674 #salesnum
 675 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
 676
 677   my $salesnum = $1;
 678   my $sales = FS::sales->by_key($salesnum)
 679     or die "salesnum $salesnum not found";
 680
 681   my $subsearch = $sales->cust_bill_pkg_search('', '',
 682     'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
 683     'paid'            => ($cgi->param('paid') ? 1 : 0),
 684     'classnum'        => scalar($cgi->param('classnum'))
 685   );
 686   $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_fee.classnum, part_pkg.classnum, 0) )";
 687
 688   my $extra_sql = $subsearch->{extra_sql};
 689   $extra_sql =~ s/^WHERE//;
 690   push @where, $extra_sql;
 691
 692   $cgi->param('classnum', 0) unless $cgi->param('classnum');
 693 }
 694
 695
 696 my $where = join(' AND ', @where);
 697 $where &&= "WHERE $where";
 698
 699 my $query = {
 700   'table'     => 'cust_bill_pkg',
 701   'addl_from' => "$join_pkg $join_cust",
 702   'hashref'   => {},
 703   'select'    => join(",\n", @select ),
 704   'extra_sql' => $where,
 705   'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 706 };
 707
 708 my $count_query =
 709   'SELECT ' . join(',', @total) .
 710   " FROM cust_bill_pkg $join_pkg $join_cust
 711   $where";
 712
 713 @peritem_desc = map {emt($_)} @peritem_desc;
 714 my @peritem_sub = map {
 715   my $field = $_;
 716   if ($field =~ /_text$/) { # kludge for credit reason/username fields
 717     sub {$_[0]->get($field)};
 718   } else {
 719     sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
 720   }
 721 } @peritem;
 722 my @peritem_null = map { '' } @peritem; # placeholders
 723 my $peritem_align = 'r' x scalar(@peritem);
 724
 725 @currency_desc = map {emt($_)} @currency_desc;
 726 my @currency_null = map { '' } @currency; # placeholders
 727 my $currency_align = 'r' x scalar(@currency);
 728
 729 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 730 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 731
 732 my $pay_link    = ''; #[, 'billpkgnum', ];
 733 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 734
 735 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 736   if $cgi->param('debug');
 737
 738 </%init>