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