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