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