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.
120 my ($class, $params) = @_;
127 if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
129 "cust_main.agentnum = $1";
136 if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
137 push @where, FS::cust_main->cust_status_sql . " = '$1' ";
141 # parse customer sales person
144 if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
145 push @where, ($1 > 0) ? "cust_main.salesnum = $1"
146 : 'cust_main.salesnum IS NULL';
154 if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
155 push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
156 : 'cust_pkg.salesnum IS NULL';
163 if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
165 "cust_pkg.custnum = $1";
172 if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
174 "cust_pkg.pkgbatch = '$1'";
181 if ( $params->{'magic'} eq 'active'
182 || $params->{'status'} eq 'active' ) {
184 push @where, FS::cust_pkg->active_sql();
186 } elsif ( $params->{'magic'} =~ /^not[ _]yet[ _]billed$/
187 || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
189 push @where, FS::cust_pkg->not_yet_billed_sql();
191 } elsif ( $params->{'magic'} =~ /^(one-time charge|inactive)/
192 || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
194 push @where, FS::cust_pkg->inactive_sql();
196 } elsif ( $params->{'magic'} =~ /^on[ _]hold$/
197 || $params->{'status'} =~ /^on[ _]hold$/ ) {
199 push @where, FS::cust_pkg->on_hold_sql();
202 } elsif ( $params->{'magic'} eq 'suspended'
203 || $params->{'status'} eq 'suspended' ) {
205 push @where, FS::cust_pkg->suspended_sql();
207 } elsif ( $params->{'magic'} =~ /^cancell?ed$/
208 || $params->{'status'} =~ /^cancell?ed$/ ) {
210 push @where, FS::cust_pkg->cancelled_sql();
214 ### special case: "magic" is used in detail links from browse/part_pkg,
215 # where "cancelled" has the restriction "and not replaced with a package
216 # of the same pkgpart". Be consistent with that.
219 if ( $params->{'magic'} =~ /^cancell?ed$/ ) {
220 my $new_pkgpart = "SELECT pkgpart FROM cust_pkg AS cust_pkg_next ".
221 "WHERE cust_pkg_next.change_pkgnum = cust_pkg.pkgnum";
222 # ...may not exist, if this was just canceled and not changed; in that
223 # case give it a "new pkgpart" that never equals the old pkgpart
224 push @where, "COALESCE(($new_pkgpart), 0) != cust_pkg.pkgpart";
228 # parse package class
231 if ( exists($params->{'classnum'}) ) {
234 if ( ref($params->{'classnum'}) ) {
236 if ( ref($params->{'classnum'}) eq 'HASH' ) {
237 @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
238 } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
239 @classnum = @{ $params->{'classnum'} };
241 die 'unhandled classnum ref '. $params->{'classnum'};
245 } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
252 my @nums = grep $_, @classnum;
253 push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
254 my $null = scalar( grep { $_ eq '' } @classnum );
255 push @c_where, 'part_pkg.classnum IS NULL' if $null;
257 if ( scalar(@c_where) == 1 ) {
258 push @where, @c_where;
259 } elsif ( @c_where ) {
260 push @where, ' ( '. join(' OR ', @c_where). ' ) ';
269 # parse package report options
272 my @report_option = ();
273 if ( exists($params->{'report_option'}) ) {
274 if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
275 @report_option = @{ $params->{'report_option'} };
276 } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
277 @report_option = split(',', $1);
282 if (@report_option) {
283 # this will result in the empty set for the dangling comma case as it should
285 map{ "0 < ( SELECT count(*) FROM part_pkg_option
286 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
287 AND optionname = 'report_option_$_'
288 AND optionvalue = '1' )"
292 foreach my $any ( grep /^report_option_any/, keys %$params ) {
294 my @report_option_any = ();
295 if ( ref($params->{$any}) eq 'ARRAY' ) {
296 @report_option_any = @{ $params->{$any} };
297 } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
298 @report_option_any = split(',', $1);
301 if (@report_option_any) {
302 # this will result in the empty set for the dangling comma case as it should
303 push @where, ' ( '. join(' OR ',
304 map{ "0 < ( SELECT count(*) FROM part_pkg_option
305 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
306 AND optionname = 'report_option_$_'
307 AND optionvalue = '1' )"
318 push @where, "part_pkg.custom = 'Y'" if $params->{custom};
324 push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
325 if $params->{fcc_line};
331 if ( exists($params->{'censustract'}) ) {
332 $params->{'censustract'} =~ /^([.\d]*)$/;
333 my $censustract = "cust_location.censustract = '$1'";
334 $censustract .= ' OR cust_location.censustract is NULL' unless $1;
335 push @where, "( $censustract )";
341 if ( exists($params->{'censustract2'})
342 && $params->{'censustract2'} =~ /^(\d*)$/
346 push @where, "cust_location.censustract LIKE '$1%'";
349 "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
354 # parse country/state
356 for (qw(state country)) { # parsing rules are the same for these
357 if ( exists($params->{$_})
358 && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
360 # XXX post-2.3 only--before that, state/country may be in cust_main
361 push @where, "cust_location.$_ = '$1'";
368 if ( $params->{location_cust} xor $params->{location_nocust} ) {
369 my $op = $params->{location_cust} ? '=' : '!=';
370 push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
372 if ( $params->{location_census} xor $params->{location_nocensus} ) {
373 my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
374 push @where, "cust_location.censustract $op";
376 if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
377 my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
378 push @where, "cust_location.geocode $op";
385 if ( ref($params->{'pkgpart'}) ) {
388 if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
389 @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
390 } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
391 @pkgpart = @{ $params->{'pkgpart'} };
393 die 'unhandled pkgpart ref '. $params->{'pkgpart'};
396 @pkgpart = grep /^(\d+)$/, @pkgpart;
398 push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
400 } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
401 push @where, "pkgpart = $1";
410 #false laziness w/report_cust_pkg.html
413 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
414 'active' => { 'susp'=>1, 'cancel'=>1 },
415 'suspended' => { 'cancel' => 1 },
420 if( exists($params->{'active'} ) ) {
421 # This overrides all the other date-related fields, and includes packages
422 # that were active at some time during the interval. It excludes:
423 # - packages that were set up after the end of the interval
424 # - packages that were canceled before the start of the interval
425 # - packages that were suspended before the start of the interval
426 # and are still suspended now
427 my($beginning, $ending) = @{$params->{'active'}};
429 "cust_pkg.setup IS NOT NULL",
430 "cust_pkg.setup <= $ending",
431 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
432 "(cust_pkg.susp IS NULL OR cust_pkg.susp >= $beginning )",
433 "NOT (".FS::cust_pkg->onetime_sql . ")";
436 foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
438 next unless exists($params->{$field});
440 my($beginning, $ending) = @{$params->{$field}};
442 next if $beginning == 0 && $ending == 4294967295;
445 "cust_pkg.$field IS NOT NULL",
446 "cust_pkg.$field >= $beginning",
447 "cust_pkg.$field <= $ending";
449 $orderby ||= "ORDER BY cust_pkg.$field";
454 $orderby ||= 'ORDER BY bill';
457 # parse magic, legacy, etc.
460 if ( $params->{'magic'} &&
461 $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
464 $orderby = 'ORDER BY pkgnum';
466 if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
467 push @where, "pkgpart = $1";
470 } elsif ( $params->{'query'} eq 'pkgnum' ) {
472 $orderby = 'ORDER BY pkgnum';
474 } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
476 $orderby = 'ORDER BY pkgnum';
479 SELECT count(*) FROM pkg_svc
480 WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
481 AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
482 WHERE cust_svc.pkgnum = cust_pkg.pkgnum
483 AND cust_svc.svcpart = pkg_svc.svcpart
490 # setup queries, links, subs, etc. for the search
493 # here is the agent virtualization
494 if ($params->{CurrentUser}) {
496 qsearchs('access_user', { username => $params->{CurrentUser} });
499 push @where, $access_user->agentnums_sql('table'=>'cust_main');
504 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
507 my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
509 my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
510 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
511 'LEFT JOIN cust_location USING ( locationnum ) '.
512 FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
516 if ( $params->{'select_zip5'} ) {
517 my $zip = 'cust_location.zip';
519 $select = "DISTINCT substr($zip,1,5) as zip";
520 $orderby = "ORDER BY substr($zip,1,5)";
521 $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
525 ( map "part_pkg.$_", qw( pkg freq ) ),
526 'pkg_class.classname',
527 'cust_main.custnum AS cust_main_custnum',
528 FS::UI::Web::cust_sql_fields(
529 $params->{'cust_fields'}
532 $count_query = 'SELECT COUNT(*)';
535 $count_query .= " FROM cust_pkg $addl_from $extra_sql";
538 'table' => 'cust_pkg',
541 'extra_sql' => $extra_sql,
542 'order_by' => $orderby,
543 'addl_from' => $addl_from,
544 'count_query' => $count_query,