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 =back
 122
 123 =cut
 124
 125 sub search {
 126   my ($class, $params) = @_;
 127   my @where = ();
 128
 129   ##
 130   # parse agent
 131   ##
 132
 133   if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
 134     push @where,
 135       "cust_main.agentnum = $1";
 136   }
 137
 138   ##
 139   # parse cust_status
 140   ##
 141
 142   if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
 143     push @where, FS::cust_main->cust_status_sql . " = '$1' ";
 144   }
 145
 146   ##
 147   # parse customer sales person
 148   ##
 149
 150   if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
 151     push @where, ($1 > 0) ? "cust_main.salesnum = $1"
 152                           : 'cust_main.salesnum IS NULL';
 153   }
 154
 155
 156   ##
 157   # parse sales person
 158   ##
 159
 160   if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
 161     push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
 162                           : 'cust_pkg.salesnum IS NULL';
 163   }
 164
 165   ##
 166   # parse custnum
 167   ##
 168
 169   if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
 170     push @where,
 171       "cust_pkg.custnum = $1";
 172   }
 173
 174   ##
 175   # custbatch
 176   ##
 177
 178   if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
 179     push @where,
 180       "cust_pkg.pkgbatch = '$1'";
 181   }
 182
 183   ##
 184   # parse status
 185   ##
 186
 187   if (    $params->{'magic'}  eq 'active'
 188        || $params->{'status'} eq 'active' ) {
 189
 190     push @where, FS::cust_pkg->active_sql();
 191
 192   } elsif (    $params->{'magic'}  =~ /^not[ _]yet[ _]billed$/
 193             || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
 194
 195     push @where, FS::cust_pkg->not_yet_billed_sql();
 196
 197   } elsif (    $params->{'magic'}  =~ /^(one-time charge|inactive)/
 198             || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
 199
 200     push @where, FS::cust_pkg->inactive_sql();
 201
 202   } elsif (    $params->{'magic'}  =~ /^on[ _]hold$/
 203             || $params->{'status'} =~ /^on[ _]hold$/ ) {
 204
 205     push @where, FS::cust_pkg->on_hold_sql();
 206
 207
 208   } elsif (    $params->{'magic'}  eq 'suspended'
 209             || $params->{'status'} eq 'suspended'  ) {
 210
 211     push @where, FS::cust_pkg->suspended_sql();
 212
 213   } elsif (    $params->{'magic'}  =~ /^cancell?ed$/
 214             || $params->{'status'} =~ /^cancell?ed$/ ) {
 215
 216     push @where, FS::cust_pkg->cancelled_sql();
 217
 218   }
 219
 220   ### special case: "magic" is used in detail links from browse/part_pkg,
 221   # where "cancelled" has the restriction "and not replaced with a package
 222   # of the same pkgpart".  Be consistent with that.
 223   ###
 224
 225   if ( $params->{'magic'} =~ /^cancell?ed$/ ) {
 226     my $new_pkgpart = "SELECT pkgpart FROM cust_pkg AS cust_pkg_next ".
 227                       "WHERE cust_pkg_next.change_pkgnum = cust_pkg.pkgnum";
 228     # ...may not exist, if this was just canceled and not changed; in that
 229     # case give it a "new pkgpart" that never equals the old pkgpart
 230     push @where, "COALESCE(($new_pkgpart), 0) != cust_pkg.pkgpart";
 231   }
 232
 233   ###
 234   # parse package class
 235   ###
 236
 237   if ( exists($params->{'classnum'}) ) {
 238
 239     my @classnum = ();
 240     if ( ref($params->{'classnum'}) ) {
 241
 242       if ( ref($params->{'classnum'}) eq 'HASH' ) {
 243         @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
 244       } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
 245         @classnum = @{ $params->{'classnum'} };
 246       } else {
 247         die 'unhandled classnum ref '. $params->{'classnum'};
 248       }
 249
 250
 251     } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
 252       @classnum = ( $1 );
 253     }
 254
 255     if ( @classnum ) {
 256
 257       my @c_where = ();
 258       my @nums = grep $_, @classnum;
 259       push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
 260       my $null = scalar( grep { $_ eq '' } @classnum );
 261       push @c_where, 'part_pkg.classnum IS NULL' if $null;
 262
 263       if ( scalar(@c_where) == 1 ) {
 264         push @where, @c_where;
 265       } elsif ( @c_where ) {
 266         push @where, ' ( '. join(' OR ', @c_where). ' ) ';
 267       }
 268
 269     }
 270
 271
 272   }
 273
 274   ###
 275   # parse package report options
 276   ###
 277
 278   my @report_option = ();
 279   if ( exists($params->{'report_option'}) ) {
 280     if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
 281       @report_option = @{ $params->{'report_option'} };
 282     } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
 283       @report_option = split(',', $1);
 284     }
 285
 286   }
 287
 288   if (@report_option) {
 289     # this will result in the empty set for the dangling comma case as it should
 290     push @where,
 291       map{ "0 < ( SELECT count(*) FROM part_pkg_option
 292                     WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 293                     AND optionname = 'report_option_$_'
 294                     AND optionvalue = '1' )"
 295          } @report_option;
 296   }
 297
 298   foreach my $any ( grep /^report_option_any/, keys %$params ) {
 299
 300     my @report_option_any = ();
 301     if ( ref($params->{$any}) eq 'ARRAY' ) {
 302       @report_option_any = @{ $params->{$any} };
 303     } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
 304       @report_option_any = split(',', $1);
 305     }
 306
 307     if (@report_option_any) {
 308       # this will result in the empty set for the dangling comma case as it should
 309       push @where, ' ( '. join(' OR ',
 310         map{ "0 < ( SELECT count(*) FROM part_pkg_option
 311                       WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
 312                       AND optionname = 'report_option_$_'
 313                       AND optionvalue = '1' )"
 314            } @report_option_any
 315       ). ' ) ';
 316     }
 317
 318   }
 319
 320   ###
 321   # parse custom
 322   ###
 323
 324   push @where,  "part_pkg.custom = 'Y'" if $params->{custom};
 325
 326   ###
 327   # parse fcc_line
 328   ###
 329
 330   push @where,  "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
 331                                                         if $params->{fcc_line};
 332
 333   ###
 334   # parse censustract
 335   ###
 336
 337   if ( exists($params->{'censustract'}) ) {
 338     $params->{'censustract'} =~ /^([.\d]*)$/;
 339     my $censustract = "cust_location.censustract = '$1'";
 340     $censustract .= ' OR cust_location.censustract is NULL' unless $1;
 341     push @where,  "( $censustract )";
 342   }
 343
 344   ###
 345   # parse censustract2
 346   ###
 347   if ( exists($params->{'censustract2'})
 348        && $params->{'censustract2'} =~ /^(\d*)$/
 349      )
 350   {
 351     if ($1) {
 352       push @where, "cust_location.censustract LIKE '$1%'";
 353     } else {
 354       push @where,
 355         "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
 356     }
 357   }
 358
 359   ###
 360   # parse country/state/zip
 361   ###
 362   for (qw(state country)) { # parsing rules are the same for these
 363   if ( exists($params->{$_})
 364     && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
 365     {
 366       # XXX post-2.3 only--before that, state/country may be in cust_main
 367       push @where, "cust_location.$_ = '$1'";
 368     }
 369   }
 370   if ( exists($params->{zip}) ) {
 371     push @where, "cust_location.zip = " . dbh->quote($params->{zip});
 372   }
 373
 374   ###
 375   # location_* flags
 376   ###
 377   if ( $params->{location_cust} xor $params->{location_nocust} ) {
 378     my $op = $params->{location_cust} ? '=' : '!=';
 379     push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
 380   }
 381   if ( $params->{location_census} xor $params->{location_nocensus} ) {
 382     my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
 383     push @where, "cust_location.censustract $op";
 384   }
 385   if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
 386     my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
 387     push @where, "cust_location.geocode $op";
 388   }
 389
 390   ###
 391   # parse part_pkg
 392   ###
 393
 394   if ( ref($params->{'pkgpart'}) ) {
 395
 396     my @pkgpart = ();
 397     if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
 398       @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
 399     } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
 400       @pkgpart = @{ $params->{'pkgpart'} };
 401     } else {
 402       die 'unhandled pkgpart ref '. $params->{'pkgpart'};
 403     }
 404
 405     @pkgpart = grep /^(\d+)$/, @pkgpart;
 406
 407     push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
 408
 409   } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 410     push @where, "pkgpart = $1";
 411   }
 412
 413   ###
 414   # parse dates
 415   ###
 416
 417   my $orderby = '';
 418
 419   #false laziness w/report_cust_pkg.html
 420   my %disable = (
 421     'all'             => {},
 422     'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
 423     'active'          => { 'susp'=>1, 'cancel'=>1 },
 424     'suspended'       => { 'cancel' => 1 },
 425     'cancelled'       => {},
 426     ''                => {},
 427   );
 428
 429   if( exists($params->{'active'} ) ) {
 430     # This overrides all the other date-related fields, and includes packages
 431     # that were active at some time during the interval.  It excludes:
 432     # - packages that were set up after the end of the interval
 433     # - packages that were canceled before the start of the interval
 434     # - packages that were suspended before the start of the interval
 435     #   and are still suspended now
 436     my($beginning, $ending) = @{$params->{'active'}};
 437     push @where,
 438       "cust_pkg.setup IS NOT NULL",
 439       "cust_pkg.setup <= $ending",
 440       "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
 441       "(cust_pkg.susp   IS NULL OR cust_pkg.susp   >= $beginning )",
 442       "NOT (".FS::cust_pkg->onetime_sql . ")";
 443   }
 444   else {
 445     my $exclude_change_from = 0;
 446     my $exclude_change_to = 0;
 447
 448     foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
 449
 450       next unless exists($params->{$field});
 451
 452       my($beginning, $ending) = @{$params->{$field}};
 453
 454       next if $beginning == 0 && $ending == 4294967295;
 455
 456       push @where,
 457         "cust_pkg.$field IS NOT NULL",
 458         "cust_pkg.$field >= $beginning",
 459         "cust_pkg.$field <= $ending";
 460
 461       $orderby ||= "ORDER BY cust_pkg.$field";
 462
 463       if ( $field eq 'setup' ) {
 464         $exclude_change_from = 1;
 465       } elsif ( $field eq 'cancel' ) {
 466         $exclude_change_to = 1;
 467       } elsif ( $field eq 'change_date' ) {
 468         # if we are given setup and change_date ranges, and the setup date
 469         # falls in _both_ ranges, then include the package whether it was
 470         # a change or not
 471         $exclude_change_from = 0;
 472       }
 473     }
 474
 475     if ($exclude_change_from) {
 476       push @where, "change_pkgnum IS NULL";
 477     }
 478     if ($exclude_change_to) {
 479       # a join might be more efficient here
 480       push @where, "NOT EXISTS(
 481         SELECT 1 FROM cust_pkg AS changed_to_pkg
 482         WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
 483       )";
 484     }
 485   }
 486
 487   $orderby ||= 'ORDER BY bill';
 488
 489   ###
 490   # parse magic, legacy, etc.
 491   ###
 492
 493   if ( $params->{'magic'} &&
 494        $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
 495   ) {
 496
 497     $orderby = 'ORDER BY pkgnum';
 498
 499     if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
 500       push @where, "pkgpart = $1";
 501     }
 502
 503   } elsif ( $params->{'query'} eq 'pkgnum' ) {
 504
 505     $orderby = 'ORDER BY pkgnum';
 506
 507   } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
 508
 509     $orderby = 'ORDER BY pkgnum';
 510
 511     push @where, '0 < (
 512       SELECT count(*) FROM pkg_svc
 513        WHERE pkg_svc.pkgpart =  cust_pkg.pkgpart
 514          AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
 515                                    WHERE cust_svc.pkgnum  = cust_pkg.pkgnum
 516                                      AND cust_svc.svcpart = pkg_svc.svcpart
 517                                 )
 518     )';
 519
 520   }
 521
 522   ##
 523   # parse the extremely weird 'towernum' param
 524   ##
 525
 526   if ($params->{towernum}) {
 527     my $towernum = $params->{towernum};
 528     $towernum = [ $towernum ] if !ref($towernum);
 529     my $in = join(',', grep /^\d+$/, @$towernum);
 530     if (length $in) {
 531       # inefficient, but this is an obscure feature
 532       eval "use FS::Report::Table";
 533       FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
 534       push @where, "EXISTS(
 535       SELECT 1 FROM tower_pkg_cache
 536       WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
 537         AND tower_pkg_cache.towernum IN ($in)
 538       )"
 539     }
 540   }
 541
 542   ##
 543   # setup queries, links, subs, etc. for the search
 544   ##
 545
 546   # here is the agent virtualization
 547   if ($params->{CurrentUser}) {
 548     my $access_user =
 549       qsearchs('access_user', { username => $params->{CurrentUser} });
 550
 551     if ($access_user) {
 552       push @where, $access_user->agentnums_sql('table'=>'cust_main');
 553     } else {
 554       push @where, "1=0";
 555     }
 556   } else {
 557     push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
 558   }
 559
 560   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 561
 562   my $addl_from = 'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
 563                   'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
 564                   'LEFT JOIN cust_location USING ( locationnum ) '.
 565                   FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 566
 567   my $select;
 568   my $count_query;
 569   if ( $params->{'select_zip5'} ) {
 570     my $zip = 'cust_location.zip';
 571
 572     $select = "DISTINCT substr($zip,1,5) as zip";
 573     $orderby = "ORDER BY substr($zip,1,5)";
 574     $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
 575   } else {
 576     $select = join(', ',
 577                          'cust_pkg.*',
 578                          ( map "part_pkg.$_", qw( pkg freq ) ),
 579                          'pkg_class.classname',
 580                          'cust_main.custnum AS cust_main_custnum',
 581                          FS::UI::Web::cust_sql_fields(
 582                            $params->{'cust_fields'}
 583                          ),
 584                   );
 585     $count_query = 'SELECT COUNT(*)';
 586   }
 587
 588   $count_query .= " FROM cust_pkg $addl_from $extra_sql";
 589
 590   my $sql_query = {
 591     'table'       => 'cust_pkg',
 592     'hashref'     => {},
 593     'select'      => $select,
 594     'extra_sql'   => $extra_sql,
 595     'order_by'    => $orderby,
 596     'addl_from'   => $addl_from,
 597     'count_query' => $count_query,
 598   };
 599
 600 }
 601
 602 1;
 603