FS/FS/cust_pkg/Search.pm

   1 package FS::cust_pkg::Search;
   2
   3 use strict;
   4 use FS::CurrentUser;
   5 use FS::UI::Web;
   6 use FS::cust_main;
   7 use FS::cust_pkg;
   8
   9 =item search HASHREF
  10
  11 (Class method)
  12
  13 Returns a qsearch hash expression to search for parameters specified in HASHREF.
  14 Valid parameters are
  15
  16 =over 4
  17
  18 =item agentnum
  19
  20 =item status
  21
  22 on hold, active, inactive (or one-time charge), suspended, canceled (or cancelled)
  23
  24 =item magic
  25
  26 Equivalent to "status", except that "canceled"/"cancelled" will exclude
  27 packages that were changed into a new package with the same pkgpart (i.e.
  28 location or quantity changes).
  29
  30 =item custom
  31
  32  boolean selects custom packages
  33
  34 =item classnum
  35
  36 =item pkgpart
  37
  38 pkgpart or arrayref or hashref of pkgparts
  39
  40 =item setup
  41
  42 arrayref of beginning and ending epoch date
  43
  44 =item last_bill
  45
  46 arrayref of beginning and ending epoch date
  47
  48 =item bill
  49
  50 arrayref of beginning and ending epoch date
  51
  52 =item adjourn
  53
  54 arrayref of beginning and ending epoch date
  55
  56 =item susp
  57
  58 arrayref of beginning and ending epoch date
  59
  60 =item expire
  61
  62 arrayref of beginning and ending epoch date
  63
  64 =item cancel
  65
  66 arrayref of beginning and ending epoch date
  67
  68 =item query
  69
  70 pkgnum or APKG_pkgnum
  71
  72 =item cust_fields
  73
  74 a value suited to passing to FS::UI::Web::cust_header
  75
  76 =item CurrentUser
  77
  78 specifies the user for agent virtualization
  79
  80 =item fcc_line
  81
  82 boolean; if true, returns only packages with more than 0 FCC phone lines.
  83
  84 =item state, country
  85
  86 Limit to packages with a service location in the specified state and country.
  87 For FCC 477 reporting, mostly.
  88
  89 =item location_cust
  90
  91 Limit to packages whose service locations are the same as the customer's
  92 default service location.
  93
  94 =item location_nocust
  95
  96 Limit to packages whose service locations are not the customer's default
  97 service location.
  98
  99 =item location_census
 100
 101 Limit to packages whose service locations have census tracts.
 102
 103 =item location_nocensus
 104
 105 Limit to packages whose service locations do not have a census tract.
 106
 107 =item location_geocode
 108
 109 Limit to packages whose locations have geocodes.
 110
 111 =item location_geocode
 112
 113 Limit to packages whose locations do not have geocodes.
 114
 115 =item towernum
 116
 117 Limit to packages associated with a svc_broadband, associated with a sector,
 118 associated with this towernum (or any of these, if it's an arrayref) (or NO
 119 towernum, if it's zero). This is an extreme niche case.
 120
 121 =item 477part, 477rownum, date
 122
 123 Limit to packages included in a specific row of one of the FCC 477 reports.
 124 '477part' is the section name (see L<FS::Report::FCC_477> methods), 'date'
 125 is the report as-of date (completely unrelated to the package setup/bill/
 126 other date fields), and '477rownum' is the row number of the report starting
 127 with zero. Row numbers have no inherent meaning, so this is useful only
 128 for explaining a 477 report you've already run.
 129
 130 =back
 131
 132 =cut
 133
 134 sub search {
 135   my ($class, $params) = @_;
 136   my @where = ();
 137
 138   ##
 139   # parse agent
 140   ##
 141
 142   if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
 143     push @where,
 144       "cust_main.agentnum = $1";
 145   }
 146
 147   ##
 148   # parse cust_status
 149   ##
 150
 151   if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
 152     push @where, FS::cust_main->cust_status_sql . " = '$1' ";
 153   }
 154
 155   ##
 156   # parse customer sales person
 157   ##
 158
 159   if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
 160     push @where, ($1 > 0) ? "cust_main.salesnum = $1"
 161                           : 'cust_main.salesnum IS NULL';
 162   }
 163
 164
 165   ##
 166   # parse sales person
 167   ##
 168
 169   if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
 170     push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
 171                           : 'cust_pkg.salesnum IS NULL';
 172   }
 173
 174   ##
 175   # parse custnum
 176   ##
 177
 178   if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
 179     push @where,
 180       "cust_pkg.custnum = $1";
 181   }
 182
 183   ##
 184   # custbatch
 185   ##
 186
 187   if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
 188     push @where,
 189       "cust_pkg.pkgbatch = '$1'";
 190   }
 191
 192   ##
 193   # parse status
 194   ##
 195
 196   if (    $params->{'magic'}  eq 'active'
 197        || $params->{'status'} eq 'active' ) {
 198
 199     push @where, FS::cust_pkg->active_sql();
 200
 201   } elsif (    $params->{'magic'}  =~ /^not[ _]yet[ _]billed$/
 202             || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
 203
 204     push @where, FS::cust_pkg->not_yet_billed_sql();
 205
 206   } elsif (    $params->{'magic'}  =~ /^(one-time charge|inactive)/
 207             || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
 208
 209     push @where, FS::cust_pkg->inactive_sql();
 210
 211   } elsif (    $params->{'magic'}  =~ /^on[ _]hold$/
 212             || $params->{'status'} =~ /^on[ _]hold$/ ) {
 213
 214     push @where, FS::cust_pkg->on_hold_sql();
 215
 216
 217   } elsif (    $params->{'magic'}  eq 'suspended'
 218             || $params->{'status'} eq 'suspended'  ) {
 219
 220     push @where, FS::cust_pkg->suspended_sql();
 221
 222   } elsif (    $params->{'magic'}  =~ /^cancell?ed$/
 223             || $params->{'status'} =~ /^cancell?ed$/ ) {
 224
 225     push @where, FS::cust_pkg->cancelled_sql();
 226
 227   }
 228
 229   ### special case: "magic" is used in detail links from browse/part_pkg,
 230   # where "cancelled" has the restriction "and not replaced with a package
 231   # of the same pkgpart".  Be consistent with that.
 232   ###
 233
 234   if ( $params->{'magic'} =~ /^cancell?ed$/ ) {
 235     my $new_pkgpart = "SELECT pkgpart FROM cust_pkg AS cust_pkg_next ".
 236                       "WHERE cust_pkg_next.change_pkgnum = cust_pkg.pkgnum";
 237     # ...may not exist, if this was just canceled and not changed; in that
 238     # case give it a "new pkgpart" that never equals the old pkgpart
 239     push @where, "COALESCE(($new_pkgpart), 0) != cust_pkg.pkgpart";
 240   }
 241
 242   ###
 243   # parse package class
 244   ###
 245
 246   if ( exists($params->{'classnum'}) ) {
 247
 248     my @classnum = ();
 249     if ( ref($params->{'classnum'}) ) {
 250
 251       if ( ref($params->{'classnum'}) eq 'HASH' ) {
 252         @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
 253       } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
 254         @classnum = @{ $params->{'classnum'} };
 255       } else {
 256         die 'unhandled classnum ref '. $params->{'classnum'};
 257       }
 258
 259
 260     } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
 261       @classnum = ( $1 );
 262     }
 263
 264     if ( @classnum ) {
 265
 266       my @c_where = ();
 267       my @nums = grep $_, @classnum;
 268       push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
 269       my $null = scalar( grep { $_ eq '' } @classnum );
 270       push @c_where, 'part_pkg.classnum IS NULL' if $null;
 271
 272       if ( scalar(@c_where) == 1 ) {
 273         push @where, @c_where;
 274       } elsif ( @c_where ) {
 275         push @where, ' ( '. join(' OR ', @c_where). ' ) ';
 276       }
 277
 278     }
 279
 280
 281   }
 282
 283   ###
 284   # parse package report options
 285   ###
 286
 287   my @report_option = ();
 288   if ( exists($params->{'report_option'}) ) {
 289     if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
 290       @report_option = @{ $params->{'report_option'} };
 291     } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
 292       @report_option = split(',', $1);
 293     }
 294
 295   }
 296
 297   if (@report_option) {
 298     # this will result in the empty set for the dangling comma case as it should
 299     push @where,
 300       map{ "0 < ( SELECT count(*) FROM part_pkg_option
 301                     WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 302                     AND optionname = 'report_option_$_'
 303                     AND optionvalue = '1' )"
 304          } @report_option;
 305   }
 306
 307   foreach my $any ( grep /^report_option_any/, keys %$params ) {
 308
 309     my @report_option_any = ();
 310     if ( ref($params->{$any}) eq 'ARRAY' ) {
 311       @report_option_any = @{ $params->{$any} };
 312     } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
 313       @report_option_any = split(',', $1);
 314     }
 315
 316     if (@report_option_any) {
 317       # this will result in the empty set for the dangling comma case as it should
 318       push @where, ' ( '. join(' OR ',
 319         map{ "0 < ( SELECT count(*) FROM part_pkg_option
 320                       WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 321                       AND optionname = 'report_option_$_'
 322                       AND optionvalue = '1' )"
 323            } @report_option_any
 324       ). ' ) ';
 325     }
 326
 327   }
 328
 329   ###
 330   # parse custom
 331   ###
 332
 333   push @where,  "part_pkg.custom = 'Y'" if $params->{custom};
 334
 335   ###
 336   # parse fcc_line
 337   ###
 338
 339   push @where,  "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
 340                                                         if $params->{fcc_line};
 341
 342   ###
 343   # parse censustract
 344   ###
 345
 346   if ( exists($params->{'censustract'}) ) {
 347     $params->{'censustract'} =~ /^([.\d]*)$/;
 348     my $censustract = "cust_location.censustract = '$1'";
 349     $censustract .= ' OR cust_location.censustract is NULL' unless $1;
 350     push @where,  "( $censustract )";
 351   }
 352
 353   ###
 354   # parse censustract2
 355   ###
 356   if ( exists($params->{'censustract2'})
 357        && $params->{'censustract2'} =~ /^(\d*)$/
 358      )
 359   {
 360     if ($1) {
 361       push @where, "cust_location.censustract LIKE '$1%'";
 362     } else {
 363       push @where,
 364         "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
 365     }
 366   }
 367
 368   ###
 369   # parse country/state/zip
 370   ###
 371   for (qw(state country)) { # parsing rules are the same for these
 372   if ( exists($params->{$_})
 373     && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
 374     {
 375       # XXX post-2.3 only--before that, state/country may be in cust_main
 376       push @where, "cust_location.$_ = '$1'";
 377     }
 378   }
 379   if ( exists($params->{zip}) ) {
 380     push @where, "cust_location.zip = " . dbh->quote($params->{zip});
 381   }
 382
 383   ###
 384   # location_* flags
 385   ###
 386   if ( $params->{location_cust} xor $params->{location_nocust} ) {
 387     my $op = $params->{location_cust} ? '=' : '!=';
 388     push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
 389   }
 390   if ( $params->{location_census} xor $params->{location_nocensus} ) {
 391     my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
 392     push @where, "cust_location.censustract $op";
 393   }
 394   if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
 395     my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
 396     push @where, "cust_location.geocode $op";
 397   }
 398
 399   ###
 400   # parse part_pkg
 401   ###
 402
 403   if ( ref($params->{'pkgpart'}) ) {
 404
 405     my @pkgpart = ();
 406     if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
 407       @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
 408     } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
 409       @pkgpart = @{ $params->{'pkgpart'} };
 410     } else {
 411       die 'unhandled pkgpart ref '. $params->{'pkgpart'};
 412     }
 413
 414     @pkgpart = grep /^(\d+)$/, @pkgpart;
 415
 416     push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
 417
 418   } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 419     push @where, "pkgpart = $1";
 420   }
 421
 422   ###
 423   # parse dates
 424   ###
 425
 426   my $orderby = '';
 427
 428   #false laziness w/report_cust_pkg.html
 429   my %disable = (
 430     'all'             => {},
 431     'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
 432     'active'          => { 'susp'=>1, 'cancel'=>1 },
 433     'suspended'       => { 'cancel' => 1 },
 434     'cancelled'       => {},
 435     ''                => {},
 436   );
 437
 438   if( exists($params->{'active'} ) ) {
 439     # This overrides all the other date-related fields, and includes packages
 440     # that were active at some time during the interval.  It excludes:
 441     # - packages that were set up after the end of the interval
 442     # - packages that were canceled before the start of the interval
 443     # - packages that were suspended before the start of the interval
 444     #   and are still suspended now
 445     my($beginning, $ending) = @{$params->{'active'}};
 446     push @where,
 447       "cust_pkg.setup IS NOT NULL",
 448       "cust_pkg.setup <= $ending",
 449       "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
 450       "(cust_pkg.susp   IS NULL OR cust_pkg.susp   >= $beginning )",
 451       "NOT (".FS::cust_pkg->onetime_sql . ")";
 452   }
 453   else {
 454     my $exclude_change_from = 0;
 455     my $exclude_change_to = 0;
 456
 457     foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
 458
 459       next unless exists($params->{$field});
 460
 461       my($beginning, $ending) = @{$params->{$field}};
 462
 463       next if $beginning == 0 && $ending == 4294967295;
 464
 465       push @where,
 466         "cust_pkg.$field IS NOT NULL",
 467         "cust_pkg.$field >= $beginning",
 468         "cust_pkg.$field <= $ending";
 469
 470       $orderby ||= "ORDER BY cust_pkg.$field";
 471
 472       if ( $field eq 'setup' ) {
 473         $exclude_change_from = 1;
 474       } elsif ( $field eq 'cancel' ) {
 475         $exclude_change_to = 1;
 476       } elsif ( $field eq 'change_date' ) {
 477         # if we are given setup and change_date ranges, and the setup date
 478         # falls in _both_ ranges, then include the package whether it was
 479         # a change or not
 480         $exclude_change_from = 0;
 481       }
 482     }
 483
 484     if ($exclude_change_from) {
 485       push @where, "change_pkgnum IS NULL";
 486     }
 487     if ($exclude_change_to) {
 488       # a join might be more efficient here
 489       push @where, "NOT EXISTS(
 490         SELECT 1 FROM cust_pkg AS changed_to_pkg
 491         WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
 492       )";
 493     }
 494   }
 495
 496   $orderby ||= 'ORDER BY bill';
 497
 498   ###
 499   # parse magic, legacy, etc.
 500   ###
 501
 502   if ( $params->{'magic'} &&
 503        $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
 504   ) {
 505
 506     $orderby = 'ORDER BY pkgnum';
 507
 508     if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 509       push @where, "pkgpart = $1";
 510     }
 511
 512   } elsif ( $params->{'query'} eq 'pkgnum' ) {
 513
 514     $orderby = 'ORDER BY pkgnum';
 515
 516   } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
 517
 518     $orderby = 'ORDER BY pkgnum';
 519
 520     push @where, '0 < (
 521       SELECT count(*) FROM pkg_svc
 522        WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
 523          AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
 524                                    WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
 525                                      AND cust_svc.svcpart = pkg_svc.svcpart
 526                                 )
 527     )';
 528
 529   }
 530
 531   ##
 532   # parse the extremely weird 'towernum' param
 533   ##
 534
 535   if ($params->{towernum}) {
 536     my $towernum = $params->{towernum};
 537     $towernum = [ $towernum ] if !ref($towernum);
 538     my $in = join(',', grep /^\d+$/, @$towernum);
 539     if (length $in) {
 540       # inefficient, but this is an obscure feature
 541       eval "use FS::Report::Table";
 542       FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
 543       push @where, "EXISTS(
 544       SELECT 1 FROM tower_pkg_cache
 545       WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
 546         AND tower_pkg_cache.towernum IN ($in)
 547       )"
 548     }
 549   }
 550
 551   ##
 552   # parse the 477 report drill-down options
 553   ##
 554
 555   if ($params->{'477part'} =~ /^([a-z]+)$/) {
 556     my $section = $1;
 557     my ($date, $rownum, $agentnum);
 558     if ($params->{'date'} =~ /^(\d+)$/) {
 559       $date = $1;
 560     }
 561     if ($params->{'477rownum'} =~ /^(\d+)$/) {
 562       $rownum = $1;
 563     }
 564     if ($params->{'agentnum'} =~ /^(\d+)$/) {
 565       $agentnum = $1;
 566     }
 567     if ($date and defined($rownum)) {
 568       my $report = FS::Report::FCC_477->report($section,
 569         'date'      => $date,
 570         'agentnum'  => $agentnum,
 571         'detail'    => 1
 572       );
 573       my $row = $report->[$rownum]
 574         or die "row $rownum is past the end of the report";
 575       my $pkgnums = $row->[-1] || '0';
 576         # '0' so that if there are no pkgnums (empty string) it will create
 577         # a valid query that returns nothing
 578       warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
 579
 580       # and this overrides everything
 581       @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
 582     } # else we're missing some params, ignore the whole business
 583   }
 584
 585   ##
 586   # setup queries, links, subs, etc. for the search
 587   ##
 588
 589   # here is the agent virtualization
 590   if ($params->{CurrentUser}) {
 591     my $access_user =
 592       qsearchs('access_user', { username => $params->{CurrentUser} });
 593
 594     if ($access_user) {
 595       push @where, $access_user->agentnums_sql('table'=>'cust_main');
 596     } else {
 597       push @where, "1=0";
 598     }
 599   } else {
 600     push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
 601   }
 602
 603   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 604
 605   my $addl_from = 'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
 606                   'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
 607                   'LEFT JOIN cust_location USING ( locationnum ) '.
 608                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 609
 610   my $select;
 611   my $count_query;
 612   if ( $params->{'select_zip5'} ) {
 613     my $zip = 'cust_location.zip';
 614
 615     $select = "DISTINCT substr($zip,1,5) as zip";
 616     $orderby = "ORDER BY substr($zip,1,5)";
 617     $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
 618   } else {
 619     $select = join(', ',
 620                          'cust_pkg.*',
 621                          ( map "part_pkg.$_", qw( pkg freq ) ),
 622                          'pkg_class.classname',
 623                          'cust_main.custnum AS cust_main_custnum',
 624                          FS::UI::Web::cust_sql_fields(
 625                            $params->{'cust_fields'}
 626                          ),
 627                   );
 628     $count_query = 'SELECT COUNT(*)';
 629   }
 630
 631   $count_query .= " FROM cust_pkg $addl_from $extra_sql";
 632
 633   my $sql_query = {
 634     'table'       => 'cust_pkg',
 635     'hashref'     => {},
 636     'select'      => $select,
 637     'extra_sql'   => $extra_sql,
 638     'order_by'    => $orderby,
 639     'addl_from'   => $addl_from,
 640     'count_query' => $count_query,
 641   };
 642
 643 }
 644
 645 1;
 646