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 or a comma-separated list (where 0 is "no report class"), or the
 129   word "multiple".
 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 # salesnum--see below
 263 # refnum
 264 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
 265   push @where, "cust_main.refnum = $1";
 266 }
 267
 268 # 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)
 269 if ( grep { $_ eq 'cust_classnum' } $cgi->param ) {
 270   my @classnums = grep /^\d*$/, $cgi->param('cust_classnum');
 271   push @where, 'COALESCE( cust_main.classnum, 0) IN ( '.
 272                    join(',', map { $_ || '0' } @classnums ).
 273                ' )'
 274     if @classnums;
 275 }
 276
 277
 278 # custnum
 279 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
 280   push @where, "cust_main.custnum = $1";
 281 }
 282
 283 # we want the package and its definition if available
 284 my $join_pkg =
 285 ' LEFT JOIN cust_pkg      USING (pkgnum)
 286   LEFT JOIN part_pkg      USING (pkgpart)';
 287
 288 my $part_pkg = 'part_pkg';
 289 if ( $cgi->param('use_override') ) { #"Separate sub-packages from parents"
 290   # still need the real part_pkg for tax applicability,
 291   # so alias this one
 292   $join_pkg .= " LEFT JOIN part_pkg AS override ON (
 293   COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = override.pkgpart
 294   )";
 295   $part_pkg = 'override';
 296 }
 297 push @select, 'part_pkg.pkgpart', 'part_pkg.pkg'; # or should this use override?
 298
 299 # the non-tax case
 300 if ( $cgi->param('nottax') ) {
 301
 302   push @where, 'cust_bill_pkg.pkgnum > 0';
 303
 304   my @tax_where; # will go into a subquery
 305   my @exempt_where; # will also go into a subquery
 306
 307   # classnum (of override pkgpart if applicable)
 308   # not specified: all classes
 309   # 0: empty class
 310   # N: classnum
 311   if ( grep { $_ eq 'classnum' } $cgi->param ) {
 312     my @classnums = grep /^\d+$/, $cgi->param('classnum');
 313     push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
 314                      join(',', @classnums ).
 315                  ' )'
 316       if @classnums;
 317   }
 318
 319   if ( grep { $_ eq 'report_optionnum' } $cgi->param ) {
 320     my @nums = grep /^\w+$/, $cgi->param('report_optionnum');
 321     my $num = join(',', @nums);
 322     push @where, # code reuse FTW
 323       FS::Report::Table->with_report_option( $num, $cgi->param('use_override'));
 324   }
 325
 326   if ( $cgi->param('report_optionnum') =~ /^(\w+)$/ ) {
 327     ;
 328   }
 329
 330   # taxclass
 331   if ( $cgi->param('taxclassNULL') ) {
 332     # a little different from 'taxclass' in that it applies to the
 333     # effective taxclass, not the real one
 334     push @tax_where, 'cust_main_county.taxclass IS NULL'
 335   } elsif ( $cgi->param('taxclass') ) {
 336     push @tax_where, "$part_pkg.taxclass IN (" .
 337                  join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
 338                  ')';
 339   }
 340
 341   if ( $cgi->param('exempt_cust') eq 'Y' ) {
 342     # tax-exempt customers
 343     push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
 344
 345   } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
 346     # non-taxable package charges
 347     push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
 348   }
 349   # we don't handle exempt_monthly here
 350
 351   if ( $cgi->param('taxname') ) { # specific taxname
 352       push @tax_where, 'cust_main_county.taxname = '.
 353                         dbh->quote($cgi->param('taxname'));
 354   } elsif ( $cgi->param('taxnameNULL') ) {
 355       push @tax_where, 'cust_main_county.taxname IS NULL OR '.
 356                        'cust_main_county.taxname = \'Tax\'';
 357   }
 358
 359   # country:state:county:city:district (may be repeated)
 360   # You can also pass a big list of taxnums but that leads to huge URLs.
 361   # Note that this means "packages whose tax is in this region", not
 362   # "packages in this region".  It's meant for links from the tax report.
 363   if ( $cgi->param('region') ) {
 364     my @orwhere;
 365     foreach ( $cgi->param('region') ) {
 366       my %loc;
 367       @loc{qw(country state county city district)} =
 368         split(':', $cgi->param('region'));
 369       my $string = join(' AND ',
 370             map {
 371               if ( $loc{$_} ) {
 372                 "$_ = ".dbh->quote($loc{$_});
 373               } else {
 374                 "$_ IS NULL";
 375               }
 376             } keys(%loc)
 377       );
 378       push @orwhere, "($string)";
 379     }
 380     push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
 381   }
 382
 383   # specific taxnums
 384   if ( $cgi->param('taxnum') ) {
 385     my $taxnum_in = join(',',
 386       grep /^\d+$/, $cgi->param('taxnum')
 387     );
 388     push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
 389       if $taxnum_in;
 390   }
 391
 392   # If we're showing exempt items, we need to find those with
 393   # cust_tax_exempt_pkg records matching the selected taxes.
 394   # If we're showing taxable items, we need to find those with
 395   # cust_bill_pkg_tax_location records.  We also need to find the
 396   # exemption records so that we can show the taxable amount.
 397   # If we're showing all items, we need the union of those.
 398   # If we're showing 'out' (items that aren't region/class taxable),
 399   # then we need the set of all items minus the union of those.
 400
 401   my $exempt_sub;
 402
 403   if ( @exempt_where or @tax_where
 404     or $cgi->param('taxable') or $cgi->param('out') )
 405   {
 406     # process exemption restrictions, including @tax_where
 407     my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum
 408     FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
 409
 410     $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
 411       if (@tax_where or @exempt_where);
 412
 413     $exempt_sub .= ' GROUP BY billpkgnum';
 414
 415     $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
 416     USING (billpkgnum)";
 417   }
 418
 419   if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) {
 420     # process tax restrictions
 421     unshift @tax_where,
 422       'cust_main_county.tax > 0';
 423
 424     my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
 425     FROM cust_bill_pkg_tax_location
 426     JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
 427     JOIN cust_main_county USING (taxnum)
 428     WHERE ". join(' AND ', @tax_where).
 429     " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
 430
 431     $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
 432     ON (item_tax.invnum = cust_bill_pkg.invnum AND
 433         item_tax.pkgnum = cust_bill_pkg.pkgnum)";
 434   }
 435
 436   # now do something with that
 437   if ( @exempt_where ) {
 438
 439     push @where,    'item_exempt.billpkgnum IS NOT NULL';
 440     push @select,   'item_exempt.exempt_amount';
 441     push @peritem,  'exempt_amount';
 442     push @peritem_desc, 'Exempt';
 443     push @total,    'SUM(exempt_amount)';
 444     push @total_desc, "$money_char%.2f tax-exempt";
 445
 446   } elsif ( $cgi->param('taxable') ) {
 447
 448     my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
 449                   '- COALESCE(item_exempt.exempt_amount, 0)';
 450
 451     push @where,    'item_tax.invnum IS NOT NULL';
 452     push @select,   "($taxable) AS taxable_amount";
 453     push @peritem,  'taxable_amount';
 454     push @peritem_desc, 'Taxable';
 455     push @total,    "SUM($taxable)";
 456     push @total_desc, "$money_char%.2f taxable";
 457
 458   } elsif ( $cgi->param('out') ) {
 459
 460     push @where,    'item_tax.invnum IS NULL',
 461                     'item_exempt.billpkgnum IS NULL';
 462
 463   } elsif ( @tax_where ) {
 464
 465     # union of taxable + all exempt_ cases
 466     push @where,
 467       '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
 468
 469   }
 470
 471   # recur/usage separation
 472   if ( $cgi->param('usage') eq 'recurring' ) {
 473
 474     my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
 475     push @select, "($recur_no_usage) AS recur_no_usage";
 476     $peritem[1] = 'recur_no_usage';
 477     $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
 478     $total_desc[0] .= ' (excluding usage)';
 479
 480   } elsif ( $cgi->param('usage') eq 'usage' ) {
 481
 482     my $usage = FS::cust_bill_pkg->usage_sql();
 483     push @select, "($usage) AS _usage";
 484     # there's already a method named 'usage'
 485     $peritem[1] = '_usage';
 486     $peritem_desc[1] = 'Usage charge';
 487     $total[1] = "SUM($usage)";
 488     $total_desc[0] .= ' usage charges';
 489   }
 490
 491 } elsif ( $cgi->param('istax') ) {
 492
 493   @peritem = ( 'setup' ); # taxes only have setup
 494   @peritem_desc = ( 'Tax charge' );
 495
 496   push @where, 'cust_bill_pkg.pkgnum = 0';
 497
 498   # tax location when using tax_rate_location
 499   if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 500
 501     $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
 502                  ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
 503     push @where, FS::tax_rate_location->location_sql(
 504                    map { $_ => (scalar($cgi->param($_)) || '') }
 505                      qw( district city county state locationtaxid )
 506                  );
 507
 508     $total[1] = 'SUM(
 509       COALESCE(cust_bill_pkg_tax_rate_location.amount,
 510                cust_bill_pkg.setup + cust_bill_pkg.recur)
 511     )';
 512
 513   } elsif ( $cgi->param('out') ) {
 514
 515     $join_pkg .= '
 516       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 517     ';
 518     push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
 519
 520     # each billpkgnum should appear only once
 521     $total[0] = 'COUNT(*)';
 522     $total[1] = 'SUM(cust_bill_pkg.setup)';
 523
 524   } else { # not locationtaxid or 'out'--the normal case
 525
 526     $join_pkg .= '
 527       LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
 528       JOIN cust_main_county           USING (taxnum)
 529     ';
 530
 531     # don't double-count the components of consolidated taxes
 532     $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
 533     $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
 534   }
 535
 536   # taxclass
 537   if ( $cgi->param('taxclassNULL') ) {
 538     push @where, 'cust_main_county.taxclass IS NULL';
 539   }
 540
 541   # taxname
 542   if ( $cgi->param('taxnameNULL') ) {
 543     push @where, 'cust_main_county.taxname IS NULL OR '.
 544                  'cust_main_county.taxname = \'Tax\'';
 545   } elsif ( $cgi->param('taxname') ) {
 546     push @where, 'cust_main_county.taxname = '.
 547                   dbh->quote($cgi->param('taxname'));
 548   }
 549
 550   # specific taxnums
 551   if ( $cgi->param('taxnum') ) {
 552     my $taxnum_in = join(',',
 553       grep /^\d+$/, $cgi->param('taxnum')
 554     );
 555     push @where, "cust_main_county.taxnum IN ($taxnum_in)"
 556       if $taxnum_in;
 557   }
 558
 559   # report group (itemdesc)
 560   if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
 561     my ( $group_op, $group_value ) = ( $1, $2 );
 562     if ( $group_op eq '=' ) {
 563       #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
 564       push @where, 'itemdesc = '. dbh->quote($group_value);
 565     } elsif ( $group_op eq '!=' ) {
 566       push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
 567     } else {
 568       die "guru meditation #00de: group_op $group_op\n";
 569     }
 570   }
 571
 572   # itemdesc, for some reason
 573   if ( $cgi->param('itemdesc') ) {
 574     if ( $cgi->param('itemdesc') eq 'Tax' ) {
 575       push @where, "(itemdesc='Tax' OR itemdesc is null)";
 576     } else {
 577       push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
 578     }
 579   }
 580
 581 } # nottax / istax
 582
 583
 584 #total payments
 585 my $pay_sub = "SELECT SUM(cust_bill_pay_pkg.amount)
 586                  FROM cust_bill_pay_pkg
 587                    WHERE cust_bill_pkg.billpkgnum = cust_bill_pay_pkg.billpkgnum
 588               ";
 589 push @select, "($pay_sub) AS pay_amount";
 590
 591
 592 # credit
 593 if ( $cgi->param('credit') ) {
 594
 595   my $credit_sub;
 596
 597   if ( $cgi->param('istax') ) {
 598     # then we need to group/join by billpkgtaxlocationnum, to get only the
 599     # relevant part of partial taxes
 600     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 601       reason.reason as reason_text, access_user.username AS username_text,
 602       billpkgtaxlocationnum, billpkgnum
 603     FROM cust_credit_bill_pkg
 604       JOIN cust_credit_bill USING (creditbillnum)
 605       JOIN cust_credit USING (crednum)
 606       LEFT JOIN reason USING (reasonnum)
 607       LEFT JOIN access_user USING (usernum)
 608     GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason,
 609       access_user.username";
 610
 611     if ( $cgi->param('out') ) {
 612
 613       # find credits that are applied to the line items, but not to
 614       # a cust_bill_pkg_tax_location link
 615       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 616         USING (billpkgnum)";
 617       push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 618
 619     } else {
 620
 621       # find credits that are applied to the CBPTL links that are
 622       # considered "interesting" by the report criteria
 623       $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
 624         USING (billpkgtaxlocationnum)";
 625
 626     }
 627
 628   } else {
 629     # then only group by billpkgnum
 630     my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
 631       reason.reason as reason_text, access_user.username AS username_text,
 632       billpkgnum
 633     FROM cust_credit_bill_pkg
 634       JOIN cust_credit_bill USING (creditbillnum)
 635       JOIN cust_credit USING (crednum)
 636       LEFT JOIN reason USING (reasonnum)
 637       LEFT JOIN access_user USING (usernum)
 638     GROUP BY billpkgnum, reason.reason, access_user.username";
 639     $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
 640   }
 641
 642   push @where,    'item_credit.billpkgnum IS NOT NULL';
 643   push @select,   'item_credit.credit_amount',
 644                   'item_credit.username_text',
 645                   'item_credit.reason_text';
 646   push @peritem,  'credit_amount', 'username_text', 'reason_text';
 647   push @peritem_desc, 'Credited', 'By', 'Reason';
 648   push @total,    'SUM(credit_amount)';
 649   push @total_desc, "$money_char%.2f credited";
 650
 651 } else {
 652
 653   #still want a credit total column
 654
 655   my $credit_sub = "
 656     SELECT SUM(cust_credit_bill_pkg.amount)
 657       FROM cust_credit_bill_pkg
 658         WHERE cust_bill_pkg.billpkgnum = cust_credit_bill_pkg.billpkgnum
 659   ";
 660   push @select, "($credit_sub) AS credit_amount";
 661
 662 }
 663
 664 push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 665
 666 #salesnum
 667 if ( $cgi->param('salesnum') =~ /^(\d+)$/ ) {
 668
 669   my $salesnum = $1;
 670   my $sales = FS::sales->by_key($salesnum)
 671     or die "salesnum $salesnum not found";
 672
 673   my $subsearch = $sales->cust_bill_pkg_search('', '',
 674     'cust_main_sales' => ($cgi->param('cust_main_sales') ? 1 : 0),
 675     'paid'            => ($cgi->param('paid') ? 1 : 0),
 676     'classnum'        => scalar($cgi->param('classnum'))
 677   );
 678   $join_pkg .= " JOIN sales_pkg_class ON ( COALESCE(sales_pkg_class.classnum, 0) = COALESCE( part_pkg.classnum, 0) )";
 679
 680   my $extra_sql = $subsearch->{extra_sql};
 681   $extra_sql =~ s/^WHERE//;
 682   push @where, $extra_sql;
 683
 684   $cgi->param('classnum', 0) unless $cgi->param('classnum');
 685 }
 686
 687
 688 my $where = join(' AND ', @where);
 689 $where &&= "WHERE $where";
 690
 691 my $query = {
 692   'table'     => 'cust_bill_pkg',
 693   'addl_from' => "$join_pkg $join_cust",
 694   'hashref'   => {},
 695   'select'    => join(",\n", @select ),
 696   'extra_sql' => $where,
 697   'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 698 };
 699
 700 my $count_query =
 701   'SELECT ' . join(',', @total) .
 702   " FROM cust_bill_pkg $join_pkg $join_cust
 703   $where";
 704
 705 @peritem_desc = map {emt($_)} @peritem_desc;
 706 my @peritem_sub = map {
 707   my $field = $_;
 708   if ($field =~ /_text$/) { # kludge for credit reason/username fields
 709     sub {$_[0]->get($field)};
 710   } else {
 711     sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
 712   }
 713 } @peritem;
 714 my @peritem_null = map { '' } @peritem; # placeholders
 715 my $peritem_align = 'r' x scalar(@peritem);
 716
 717 my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
 718 my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 719
 720 my $pay_link    = ''; #[, 'billpkgnum', ];
 721 my $credit_link = [ "${p}search/cust_credit_bill_pkg.html?billpkgnum=", 'billpkgnum', ];
 722
 723 warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
 724   if $cgi->param('debug');
 725
 726 </%init>