1 package FS::cust_pkg::Search;
13 Returns a qsearch hash expression to search for parameters specified in HASHREF.
22 on hold, active, inactive (or one-time charge), suspended, canceled (or cancelled)
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).
32 boolean selects custom packages
38 pkgpart or arrayref or hashref of pkgparts
42 arrayref of beginning and ending epoch date
46 arrayref of beginning and ending epoch date
50 arrayref of beginning and ending epoch date
54 arrayref of beginning and ending epoch date
58 arrayref of beginning and ending epoch date
62 arrayref of beginning and ending epoch date
66 arrayref of beginning and ending epoch date
74 a value suited to passing to FS::UI::Web::cust_header
78 specifies the user for agent virtualization
82 boolean; if true, returns only packages with more than 0 FCC phone lines.
86 Limit to packages with a service location in the specified state and country.
87 For FCC 477 reporting, mostly.
91 Limit to packages whose service locations are the same as the customer's
92 default service location.
96 Limit to packages whose service locations are not the customer's default
101 Limit to packages whose service locations have census tracts.
103 =item location_nocensus
105 Limit to packages whose service locations do not have a census tract.
107 =item location_geocode
109 Limit to packages whose locations have geocodes.
111 =item location_geocode
113 Limit to packages whose locations do not have geocodes.
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.
126 my ($class, $params) = @_;
133 if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
135 "cust_main.agentnum = $1";
142 if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
143 push @where, FS::cust_main->cust_status_sql . " = '$1' ";
147 # parse customer sales person
150 if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
151 push @where, ($1 > 0) ? "cust_main.salesnum = $1"
152 : 'cust_main.salesnum IS NULL';
160 if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
161 push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
162 : 'cust_pkg.salesnum IS NULL';
169 if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
171 "cust_pkg.custnum = $1";
178 if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
180 "cust_pkg.pkgbatch = '$1'";
187 if ( $params->{'magic'} eq 'active'
188 || $params->{'status'} eq 'active' ) {
190 push @where, FS::cust_pkg->active_sql();
192 } elsif ( $params->{'magic'} =~ /^not[ _]yet[ _]billed$/
193 || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
195 push @where, FS::cust_pkg->not_yet_billed_sql();
197 } elsif ( $params->{'magic'} =~ /^(one-time charge|inactive)/
198 || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
200 push @where, FS::cust_pkg->inactive_sql();
202 } elsif ( $params->{'magic'} =~ /^on[ _]hold$/
203 || $params->{'status'} =~ /^on[ _]hold$/ ) {
205 push @where, FS::cust_pkg->on_hold_sql();
208 } elsif ( $params->{'magic'} eq 'suspended'
209 || $params->{'status'} eq 'suspended' ) {
211 push @where, FS::cust_pkg->suspended_sql();
213 } elsif ( $params->{'magic'} =~ /^cancell?ed$/
214 || $params->{'status'} =~ /^cancell?ed$/ ) {
216 push @where, FS::cust_pkg->cancelled_sql();
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.
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";
234 # parse package class
237 if ( exists($params->{'classnum'}) ) {
240 if ( ref($params->{'classnum'}) ) {
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'} };
247 die 'unhandled classnum ref '. $params->{'classnum'};
251 } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
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;
263 if ( scalar(@c_where) == 1 ) {
264 push @where, @c_where;
265 } elsif ( @c_where ) {
266 push @where, ' ( '. join(' OR ', @c_where). ' ) ';
275 # parse package report options
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);
288 if (@report_option) {
289 # this will result in the empty set for the dangling comma case as it should
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' )"
298 foreach my $any ( grep /^report_option_any/, keys %$params ) {
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);
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' )"
324 push @where, "part_pkg.custom = 'Y'" if $params->{custom};
330 push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
331 if $params->{fcc_line};
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 )";
347 if ( exists($params->{'censustract2'})
348 && $params->{'censustract2'} =~ /^(\d*)$/
352 push @where, "cust_location.censustract LIKE '$1%'";
355 "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
360 # parse country/state/zip
362 for (qw(state country)) { # parsing rules are the same for these
363 if ( exists($params->{$_})
364 && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
366 # XXX post-2.3 only--before that, state/country may be in cust_main
367 push @where, "cust_location.$_ = '$1'";
370 if ( exists($params->{zip}) ) {
371 push @where, "cust_location.zip = " . dbh->quote($params->{zip});
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";
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";
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";
394 if ( ref($params->{'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'} };
402 die 'unhandled pkgpart ref '. $params->{'pkgpart'};
405 @pkgpart = grep /^(\d+)$/, @pkgpart;
407 push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
409 } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
410 push @where, "pkgpart = $1";
419 #false laziness w/report_cust_pkg.html
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 },
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'}};
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 . ")";
445 my $exclude_change_from = 0;
446 my $exclude_change_to = 0;
448 foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
450 next unless exists($params->{$field});
452 my($beginning, $ending) = @{$params->{$field}};
454 next if $beginning == 0 && $ending == 4294967295;
457 "cust_pkg.$field IS NOT NULL",
458 "cust_pkg.$field >= $beginning",
459 "cust_pkg.$field <= $ending";
461 $orderby ||= "ORDER BY cust_pkg.$field";
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
471 $exclude_change_from = 0;
475 if ($exclude_change_from) {
476 push @where, "change_pkgnum IS NULL";
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
487 $orderby ||= 'ORDER BY bill';
490 # parse magic, legacy, etc.
493 if ( $params->{'magic'} &&
494 $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
497 $orderby = 'ORDER BY pkgnum';
499 if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
500 push @where, "pkgpart = $1";
503 } elsif ( $params->{'query'} eq 'pkgnum' ) {
505 $orderby = 'ORDER BY pkgnum';
507 } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
509 $orderby = 'ORDER BY pkgnum';
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
523 # parse the extremely weird 'towernum' param
526 if ($params->{towernum}) {
527 my $towernum = $params->{towernum};
528 $towernum = [ $towernum ] if !ref($towernum);
529 my $in = join(',', grep /^\d+$/, @$towernum);
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)
543 # setup queries, links, subs, etc. for the search
546 # here is the agent virtualization
547 if ($params->{CurrentUser}) {
549 qsearchs('access_user', { username => $params->{CurrentUser} });
552 push @where, $access_user->agentnums_sql('table'=>'cust_main');
557 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
560 my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
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');
569 if ( $params->{'select_zip5'} ) {
570 my $zip = 'cust_location.zip';
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) )";
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'}
585 $count_query = 'SELECT COUNT(*)';
588 $count_query .= " FROM cust_pkg $addl_from $extra_sql";
591 'table' => 'cust_pkg',
594 'extra_sql' => $extra_sql,
595 'order_by' => $orderby,
596 'addl_from' => $addl_from,
597 'count_query' => $count_query,