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