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.
121 =item 477part, 477rownum, date
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.
135 my ($class, $params) = @_;
142 if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
144 "cust_main.agentnum = $1";
151 if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
152 push @where, FS::cust_main->cust_status_sql . " = '$1' ";
156 # parse customer sales person
159 if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
160 push @where, ($1 > 0) ? "cust_main.salesnum = $1"
161 : 'cust_main.salesnum IS NULL';
169 if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
170 push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
171 : 'cust_pkg.salesnum IS NULL';
178 if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
180 "cust_pkg.custnum = $1";
187 if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
189 "cust_pkg.pkgbatch = '$1'";
196 if ( $params->{'magic'} eq 'active'
197 || $params->{'status'} eq 'active' ) {
199 push @where, FS::cust_pkg->active_sql();
201 } elsif ( $params->{'magic'} =~ /^not[ _]yet[ _]billed$/
202 || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
204 push @where, FS::cust_pkg->not_yet_billed_sql();
206 } elsif ( $params->{'magic'} =~ /^(one-time charge|inactive)/
207 || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
209 push @where, FS::cust_pkg->inactive_sql();
211 } elsif ( $params->{'magic'} =~ /^on[ _]hold$/
212 || $params->{'status'} =~ /^on[ _]hold$/ ) {
214 push @where, FS::cust_pkg->on_hold_sql();
217 } elsif ( $params->{'magic'} eq 'suspended'
218 || $params->{'status'} eq 'suspended' ) {
220 push @where, FS::cust_pkg->suspended_sql();
222 } elsif ( $params->{'magic'} =~ /^cancell?ed$/
223 || $params->{'status'} =~ /^cancell?ed$/ ) {
225 push @where, FS::cust_pkg->cancelled_sql();
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.
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";
243 # parse package class
246 if ( exists($params->{'classnum'}) ) {
249 if ( ref($params->{'classnum'}) ) {
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'} };
256 die 'unhandled classnum ref '. $params->{'classnum'};
260 } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
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;
272 if ( scalar(@c_where) == 1 ) {
273 push @where, @c_where;
274 } elsif ( @c_where ) {
275 push @where, ' ( '. join(' OR ', @c_where). ' ) ';
284 # parse refnum (advertising source)
287 if ( exists($params->{'refnum'}) ) {
289 if (ref $params->{'refnum'}) {
290 @refnum = @{ $params->{'refnum'} };
292 @refnum = ( $params->{'refnum'} );
294 my $in = join(',', grep /^\d+$/, @refnum);
295 push @where, "refnum IN($in)" if length $in;
299 # parse package report options
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);
312 if (@report_option) {
313 # this will result in the empty set for the dangling comma case as it should
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' )"
322 foreach my $any ( grep /^report_option_any/, keys %$params ) {
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);
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' )"
348 push @where, "part_pkg.custom = 'Y'" if $params->{custom};
354 push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
355 if $params->{fcc_line};
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 )";
371 if ( exists($params->{'censustract2'})
372 && $params->{'censustract2'} =~ /^(\d*)$/
376 push @where, "cust_location.censustract LIKE '$1%'";
379 "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
384 # parse country/state/zip
386 for (qw(state country)) { # parsing rules are the same for these
387 if ( exists($params->{$_})
388 && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
390 # XXX post-2.3 only--before that, state/country may be in cust_main
391 push @where, "cust_location.$_ = '$1'";
394 if ( exists($params->{zip}) ) {
395 push @where, "cust_location.zip = " . dbh->quote($params->{zip});
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";
405 if ( $params->{location_census} xor $params->{location_nocensus} ) {
406 my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
407 push @where, "cust_location.censustract $op";
409 if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
410 my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
411 push @where, "cust_location.geocode $op";
418 if ( ref($params->{'pkgpart'}) ) {
421 if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
422 @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
423 } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
424 @pkgpart = @{ $params->{'pkgpart'} };
426 die 'unhandled pkgpart ref '. $params->{'pkgpart'};
429 @pkgpart = grep /^(\d+)$/, @pkgpart;
431 push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
433 } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
434 push @where, "pkgpart = $1";
443 #false laziness w/report_cust_pkg.html
446 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
447 'active' => { 'susp'=>1, 'cancel'=>1 },
448 'suspended' => { 'cancel' => 1 },
453 if( exists($params->{'active'} ) ) {
454 # This overrides all the other date-related fields, and includes packages
455 # that were active at some time during the interval. It excludes:
456 # - packages that were set up after the end of the interval
457 # - packages that were canceled before the start of the interval
458 # - packages that were suspended before the start of the interval
459 # and are still suspended now
460 my($beginning, $ending) = @{$params->{'active'}};
462 "cust_pkg.setup IS NOT NULL",
463 "cust_pkg.setup <= $ending",
464 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
465 "(cust_pkg.susp IS NULL OR cust_pkg.susp >= $beginning )",
466 "NOT (".FS::cust_pkg->onetime_sql . ")";
469 my $exclude_change_from = 0;
470 my $exclude_change_to = 0;
472 foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
474 next unless exists($params->{$field});
476 my($beginning, $ending) = @{$params->{$field}};
478 next if $beginning == 0 && $ending == 4294967295;
481 "cust_pkg.$field IS NOT NULL",
482 "cust_pkg.$field >= $beginning",
483 "cust_pkg.$field <= $ending";
485 $orderby ||= "ORDER BY cust_pkg.$field";
487 if ( $field eq 'setup' ) {
488 $exclude_change_from = 1;
489 } elsif ( $field eq 'cancel' ) {
490 $exclude_change_to = 1;
491 } elsif ( $field eq 'change_date' ) {
492 # if we are given setup and change_date ranges, and the setup date
493 # falls in _both_ ranges, then include the package whether it was
495 $exclude_change_from = 0;
499 if ($exclude_change_from) {
500 push @where, "change_pkgnum IS NULL";
502 if ($exclude_change_to) {
503 # a join might be more efficient here
504 push @where, "NOT EXISTS(
505 SELECT 1 FROM cust_pkg AS changed_to_pkg
506 WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
511 $orderby ||= 'ORDER BY bill';
514 # parse magic, legacy, etc.
517 if ( $params->{'magic'} &&
518 $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
521 $orderby = 'ORDER BY pkgnum';
523 if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
524 push @where, "pkgpart = $1";
527 } elsif ( $params->{'query'} eq 'pkgnum' ) {
529 $orderby = 'ORDER BY pkgnum';
531 } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
533 $orderby = 'ORDER BY pkgnum';
536 SELECT count(*) FROM pkg_svc
537 WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
538 AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
539 WHERE cust_svc.pkgnum = cust_pkg.pkgnum
540 AND cust_svc.svcpart = pkg_svc.svcpart
547 # parse the extremely weird 'towernum' param
550 if ($params->{towernum}) {
551 my $towernum = $params->{towernum};
552 $towernum = [ $towernum ] if !ref($towernum);
553 my $in = join(',', grep /^\d+$/, @$towernum);
555 # inefficient, but this is an obscure feature
556 eval "use FS::Report::Table";
557 FS::Report::Table->_init_tower_pkg_cache; # probably does nothing
558 push @where, "EXISTS(
559 SELECT 1 FROM tower_pkg_cache
560 WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum
561 AND tower_pkg_cache.towernum IN ($in)
567 # parse the 477 report drill-down options
570 if ($params->{'477part'} =~ /^([a-z]+)$/) {
572 my ($date, $rownum, $agentnum);
573 if ($params->{'date'} =~ /^(\d+)$/) {
576 if ($params->{'477rownum'} =~ /^(\d+)$/) {
579 if ($params->{'agentnum'} =~ /^(\d+)$/) {
582 if ($date and defined($rownum)) {
583 my $report = FS::Report::FCC_477->report($section,
585 'agentnum' => $agentnum,
588 my $pkgnums = $report->{detail}->[$rownum]
589 or die "row $rownum is past the end of the report";
590 # '0' so that if there are no pkgnums (empty string) it will create
591 # a valid query that returns nothing
592 warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug
594 # and this overrides everything
595 @where = ( "cust_pkg.pkgnum IN($pkgnums)" );
596 } # else we're missing some params, ignore the whole business
600 # setup queries, links, subs, etc. for the search
603 # here is the agent virtualization
604 if ($params->{CurrentUser}) {
606 qsearchs('access_user', { username => $params->{CurrentUser} });
609 push @where, $access_user->agentnums_sql('table'=>'cust_main');
614 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
617 my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
619 my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
620 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
621 'LEFT JOIN cust_location USING ( locationnum ) '.
622 FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
626 if ( $params->{'select_zip5'} ) {
627 my $zip = 'cust_location.zip';
629 $select = "DISTINCT substr($zip,1,5) as zip";
630 $orderby = "ORDER BY substr($zip,1,5)";
631 $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
635 ( map "part_pkg.$_", qw( pkg freq ) ),
636 'pkg_class.classname',
637 'cust_main.custnum AS cust_main_custnum',
638 FS::UI::Web::cust_sql_fields(
639 $params->{'cust_fields'}
642 $count_query = 'SELECT COUNT(*)';
645 $count_query .= " FROM cust_pkg $addl_from $extra_sql";
648 'table' => 'cust_pkg',
651 'extra_sql' => $extra_sql,
652 'order_by' => $orderby,
653 'addl_from' => $addl_from,
654 'count_query' => $count_query,