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