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 (customer) refnum (advertising source)
 285   ###
 286
 287   if ( exists($params->{'refnum'}) ) {
 288     my @refnum;
 289     if (ref $params->{'refnum'}) {
 290       @refnum = @{ $params->{'refnum'} };
 291     } else {
 292       @refnum = ( $params->{'refnum'} );
 293     }
 294     my $in = join(',', grep /^\d+$/, @refnum);
 295     push @where, "cust_main.refnum IN($in)" if length $in;
 296   }
 297
 298   ###
 299   # parse package report options
 300   ###
 301
 302   my @report_option = ();
 303   if ( exists($params->{'report_option'}) ) {
 304     if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
 305       @report_option = @{ $params->{'report_option'} };
 306     } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
 307       @report_option = split(',', $1);
 308     }
 309
 310   }
 311
 312   if (@report_option) {
 313     # this will result in the empty set for the dangling comma case as it should
 314     push @where,
 315       map{ "EXISTS ( SELECT 1 FROM part_pkg_option
 316                        WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 317                        AND optionname = 'report_option_$_'
 318                        AND optionvalue = '1' )"
 319          } @report_option;
 320   }
 321
 322   foreach my $any ( grep /^report_option_any/, keys %$params ) {
 323
 324     my @report_option_any = ();
 325     if ( ref($params->{$any}) eq 'ARRAY' ) {
 326       @report_option_any = @{ $params->{$any} };
 327     } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
 328       @report_option_any = split(',', $1);
 329     }
 330
 331     if (@report_option_any) {
 332       # this will result in the empty set for the dangling comma case as it should
 333       push @where, ' ( '. join(' OR ',
 334         map{ "EXISTS ( SELECT 1 FROM part_pkg_option
 335                          WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 336                          AND optionname = 'report_option_$_'
 337                          AND optionvalue = '1' )"
 338            } @report_option_any
 339       ). ' ) ';
 340     }
 341
 342   }
 343
 344   ###
 345   # parse custom
 346   ###
 347
 348   push @where,  "part_pkg.custom = 'Y'" if $params->{custom};
 349
 350   ###
 351   # parse fcc_line
 352   ###
 353
 354   push @where,  "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
 355                                                         if $params->{fcc_line};
 356
 357   ###
 358   # parse censustract
 359   ###
 360
 361   if ( exists($params->{'censustract'}) ) {
 362     $params->{'censustract'} =~ /^([.\d]*)$/;
 363     my $censustract = "cust_location.censustract = '$1'";
 364     $censustract .= ' OR cust_location.censustract is NULL' unless $1;
 365     push @where,  "( $censustract )";
 366   }
 367
 368   ###
 369   # parse censustract2
 370   ###
 371   if ( exists($params->{'censustract2'})
 372        && $params->{'censustract2'} =~ /^(\d*)$/
 373      )
 374   {
 375     if ($1) {
 376       push @where, "cust_location.censustract LIKE '$1%'";
 377     } else {
 378       push @where,
 379         "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
 380     }
 381   }
 382
 383   ###
 384   # parse country/state/zip
 385   ###
 386   for (qw(state country)) { # parsing rules are the same for these
 387   if ( exists($params->{$_})
 388     && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
 389     {
 390       # XXX post-2.3 only--before that, state/country may be in cust_main
 391       push @where, "cust_location.$_ = '$1'";
 392     }
 393   }
 394   if ( exists($params->{zip}) ) {
 395     push @where, "cust_location.zip = " . dbh->quote($params->{zip});
 396   }
 397
 398   ###
 399   # location_* flags
 400   ###
 401   if ( $params->{location_cust} xor $params->{location_nocust} ) {
 402     my $op = $params->{location_cust} ? '=' : '!=';
 403     push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
 404   }
 405   if ( $params->{location_census} ) {
 406     push @where, "cust_location.censustract IS NOT NULL",
 407                  "cust_location.censusyear  =  '2020'  ";
 408   } elsif ( $params->{location_nocensus} ) {
 409     push @where, "(    cust_location.censustract IS NULL    ".
 410                  "  OR cust_location.censusyear  != '2020' )";
 411   }
 412   if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
 413     my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
 414     push @where, "cust_location.geocode $op";
 415   }
 416
 417   ###
 418   # parse part_pkg
 419   ###
 420
 421   if ( ref($params->{'pkgpart'}) ) {
 422
 423     my @pkgpart = ();
 424     if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
 425       @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
 426     } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
 427       @pkgpart = @{ $params->{'pkgpart'} };
 428     } else {
 429       die 'unhandled pkgpart ref '. $params->{'pkgpart'};
 430     }
 431
 432     @pkgpart = grep /^(\d+)$/, @pkgpart;
 433
 434     push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
 435
 436   } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 437     push @where, "pkgpart = $1";
 438   }
 439
 440   ###
 441   # parse dates
 442   ###
 443
 444   my $orderby = '';
 445
 446   #false laziness w/report_cust_pkg.html
 447   my %disable = (
 448     'all'             => {},
 449     'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
 450     'active'          => { 'susp'=>1, 'cancel'=>1 },
 451     'suspended'       => { 'cancel' => 1 },
 452     'cancelled'       => {},
 453     ''                => {},
 454   );
 455
 456   if ( exists($params->{'active'} ) ) {
 457
 458     # This overrides all the other date-related fields, and includes packages
 459     # that were active at some time during the interval.  It excludes:
 460     # - packages that were set up after the end of the interval
 461     # - packages that were canceled before the start of the interval
 462     # - packages that were suspended before the start of the interval
 463     #   and are still suspended now
 464     my($beginning, $ending) = @{$params->{'active'}};
 465     push @where,
 466       "cust_pkg.setup IS NOT NULL",
 467       "cust_pkg.setup <= $ending",
 468       "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
 469       "(cust_pkg.susp   IS NULL OR cust_pkg.susp   >= $beginning )",
 470       "NOT (".FS::cust_pkg->onetime_sql . ")";
 471
 472   } else {
 473
 474     my $exclude_change_from = 0;
 475     my $exclude_change_to = 0;
 476
 477     foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
 478
 479       if ( $params->{$field.'_null'} ) {
 480
 481         push @where, "cust_pkg.$field IS NULL";
 482              # this should surely be obsoleted by now: OR cust_pkg.$field == 0 )
 483
 484       } else {
 485
 486         next unless exists($params->{$field});
 487
 488         my($beginning, $ending) = @{$params->{$field}};
 489
 490         next if $beginning == 0 && $ending == 4294967295;
 491
 492         push @where,
 493           "cust_pkg.$field IS NOT NULL",
 494           "cust_pkg.$field >= $beginning",
 495           "cust_pkg.$field <= $ending";
 496
 497         $orderby ||= "ORDER BY cust_pkg.$field";
 498
 499         if ( $field eq 'setup' ) {
 500           $exclude_change_from = 1;
 501         } elsif ( $field eq 'cancel' ) {
 502           $exclude_change_to = 1;
 503         } elsif ( $field eq 'change_date' ) {
 504           # if we are given setup and change_date ranges, and the setup date
 505           # falls in _both_ ranges, then include the package whether it was
 506           # a change or not
 507           $exclude_change_from = 0;
 508         }
 509
 510       }
 511
 512     }
 513
 514     if ($exclude_change_from) {
 515       push @where, "cust_pkg.change_pkgnum IS NULL";
 516     }
 517     if ($exclude_change_to) {
 518       # a join might be more efficient here
 519       push @where, "NOT EXISTS(
 520         SELECT 1 FROM cust_pkg AS changed_to_pkg
 521         WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
 522       )";
 523     }
 524
 525   }
 526
 527   $orderby ||= 'ORDER BY bill';
 528
 529   ###
 530   # parse magic, legacy, etc.
 531   ###
 532
 533   if ( $params->{'magic'} &&
 534        $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
 535   ) {
 536
 537     $orderby = 'ORDER BY pkgnum';
 538
 539     if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 540       push @where, "pkgpart = $1";
 541     }
 542
 543   } elsif ( $params->{'query'} eq 'pkgnum' ) {
 544
 545     $orderby = 'ORDER BY pkgnum';
 546
 547   } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
 548
 549     $orderby = 'ORDER BY pkgnum';
 550
 551     push @where, '0 < (
 552       SELECT count(*) FROM pkg_svc
 553        WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
 554          AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
 555                                    WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
 556                                      AND cust_svc.svcpart = pkg_svc.svcpart
 557                                 )
 558     )';
 559
 560   }
 561
 562   ##
 563   # parse the extremely weird 'towernum' param
 564   ##
 565
 566   if ($params->{towernum}) {
 567     my $towernum = $params->{towernum};
 568     $towernum = [ $towernum ] if !ref($towernum);
 569     my $in = join(',', grep /^\d+$/, @$towernum);
 570     if (length $in) {
 571       # inefficient, but this is an obscure feature
 572       eval "use FS::Report::Table";
 573       FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
 574       push @where, "EXISTS(
 575       SELECT 1 FROM tower_pkg_cache
 576       WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
 577         AND tower_pkg_cache.towernum IN ($in)
 578       )"
 579     }
 580   }
 581
 582   ##
 583   # parse the 477 report drill-down options
 584   ##
 585
 586   if ($params->{'477part'} =~ /^([a-z]+)$/) {
 587     my $section = $1;
 588     my ($date, $rownum, $agentnum);
 589     if ($params->{'date'} =~ /^(\d+)$/) {
 590       $date = $1;
 591     }
 592     if ($params->{'477rownum'} =~ /^(\d+)$/) {
 593       $rownum = $1;
 594     }
 595     if ($params->{'agentnum'} =~ /^(\d+)$/) {
 596       $agentnum = $1;
 597     }
 598     if ($date and defined($rownum)) {
 599       my $report = FS::Report::FCC_477->report($section,
 600         'date'      => $date,
 601         'agentnum'  => $agentnum,
 602         'detail'    => 1
 603       );
 604       my $pkgnums = $report->{detail}->[$rownum]
 605         or die "row $rownum is past the end of the report";
 606         # '0' so that if there are no pkgnums (empty string) it will create
 607         # a valid query that returns nothing
 608       warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
 609
 610       # and this overrides everything
 611       @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
 612     } # else we're missing some params, ignore the whole business
 613   }
 614
 615   ##
 616   # setup queries, links, subs, etc. for the search
 617   ##
 618
 619   # here is the agent virtualization
 620   if ($params->{CurrentUser}) {
 621     my $access_user =
 622       qsearchs('access_user', { username => $params->{CurrentUser} });
 623
 624     if ($access_user) {
 625       push @where, $access_user->agentnums_sql('table'=>'cust_main');
 626     } else {
 627       push @where, "1=0";
 628     }
 629   } else {
 630     push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
 631   }
 632
 633   push @where,  "cust_pkg_reason.reasonnum = '".$params->{reasonnum}."'" if $params->{reasonnum};
 634
 635   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 636
 637   my $addl_from = 'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
 638                   'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
 639                   'LEFT JOIN cust_location USING ( locationnum ) '.
 640                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 641
 642   if ($params->{reasonnum}) {
 643     $addl_from .= 'LEFT JOIN cust_pkg_reason ON (cust_pkg_reason.pkgnum = cust_pkg.pkgnum) ';
 644   }
 645
 646   my $select;
 647   my $count_query;
 648   if ( $params->{'select_zip5'} ) {
 649     my $zip = 'cust_location.zip';
 650
 651     $select = "DISTINCT substr($zip,1,5) as zip";
 652     $orderby = "ORDER BY substr($zip,1,5)";
 653     $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
 654   } else {
 655     $select = join(', ',
 656                          'cust_pkg.*',
 657                          ( map "part_pkg.$_", qw( pkg freq ) ),
 658                          'pkg_class.classname',
 659                          'cust_main.custnum AS cust_main_custnum',
 660                          FS::UI::Web::cust_sql_fields(
 661                            $params->{'cust_fields'}
 662                          ),
 663                   );
 664     $count_query = 'SELECT COUNT(*)';
 665   }
 666
 667   $count_query .= " FROM cust_pkg $addl_from $extra_sql";
 668
 669   my $sql_query = {
 670     'table'       => 'cust_pkg',
 671     'hashref'     => {},
 672     'select'      => $select,
 673     'extra_sql'   => $extra_sql,
 674     'order_by'    => $orderby,
 675     'addl_from'   => $addl_from,
 676     'count_query' => $count_query,
 677   };
 678
 679 }
 680
 681 1;
 682