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, cancel (or cancelled)
26 on hold, active, inactive (or one-time charge), suspended, cancel (or cancelled)
30 boolean selects custom packages
36 pkgpart or arrayref or hashref of pkgparts
40 arrayref of beginning and ending epoch date
44 arrayref of beginning and ending epoch date
48 arrayref of beginning and ending epoch date
52 arrayref of beginning and ending epoch date
56 arrayref of beginning and ending epoch date
60 arrayref of beginning and ending epoch date
64 arrayref of beginning and ending epoch date
72 a value suited to passing to FS::UI::Web::cust_header
76 specifies the user for agent virtualization
80 boolean; if true, returns only packages with more than 0 FCC phone lines.
84 Limit to packages with a service location in the specified state and country.
85 For FCC 477 reporting, mostly.
89 Limit to packages whose service locations are the same as the customer's
90 default service location.
94 Limit to packages whose service locations are not the customer's default
99 Limit to packages whose service locations have census tracts.
101 =item location_nocensus
103 Limit to packages whose service locations do not have a census tract.
105 =item location_geocode
107 Limit to packages whose locations have geocodes.
109 =item location_geocode
111 Limit to packages whose locations do not have geocodes.
118 my ($class, $params) = @_;
125 if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) {
127 "cust_main.agentnum = $1";
134 if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) {
135 push @where, FS::cust_main->cust_status_sql . " = '$1' ";
139 # parse customer sales person
142 if ( $params->{'cust_main_salesnum'} =~ /^(\d+)$/ ) {
143 push @where, ($1 > 0) ? "cust_main.salesnum = $1"
144 : 'cust_main.salesnum IS NULL';
152 if ( $params->{'salesnum'} =~ /^(\d+)$/ ) {
153 push @where, ($1 > 0) ? "cust_pkg.salesnum = $1"
154 : 'cust_pkg.salesnum IS NULL';
161 if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
163 "cust_pkg.custnum = $1";
170 if ( $params->{'pkgbatch'} =~ /^([\w\/\-\:\.]+)$/ and $1 ) {
172 "cust_pkg.pkgbatch = '$1'";
179 if ( $params->{'magic'} eq 'active'
180 || $params->{'status'} eq 'active' ) {
182 push @where, FS::cust_pkg->active_sql();
184 } elsif ( $params->{'magic'} =~ /^not[ _]yet[ _]billed$/
185 || $params->{'status'} =~ /^not[ _]yet[ _]billed$/ ) {
187 push @where, FS::cust_pkg->not_yet_billed_sql();
189 } elsif ( $params->{'magic'} =~ /^(one-time charge|inactive)/
190 || $params->{'status'} =~ /^(one-time charge|inactive)/ ) {
192 push @where, FS::cust_pkg->inactive_sql();
194 } elsif ( $params->{'magic'} =~ /^on[ _]hold$/
195 || $params->{'status'} =~ /^on[ _]hold$/ ) {
197 push @where, FS::cust_pkg->on_hold_sql();
200 } elsif ( $params->{'magic'} eq 'suspended'
201 || $params->{'status'} eq 'suspended' ) {
203 push @where, FS::cust_pkg->suspended_sql();
205 } elsif ( $params->{'magic'} =~ /^cancell?ed$/
206 || $params->{'status'} =~ /^cancell?ed$/ ) {
208 push @where, FS::cust_pkg->cancelled_sql();
213 # parse package class
216 if ( exists($params->{'classnum'}) ) {
219 if ( ref($params->{'classnum'}) ) {
221 if ( ref($params->{'classnum'}) eq 'HASH' ) {
222 @classnum = grep $params->{'classnum'}{$_}, keys %{ $params->{'classnum'} };
223 } elsif ( ref($params->{'classnum'}) eq 'ARRAY' ) {
224 @classnum = @{ $params->{'classnum'} };
226 die 'unhandled classnum ref '. $params->{'classnum'};
230 } elsif ( $params->{'classnum'} =~ /^(\d*)$/ && $1 ne '0' ) {
237 my @nums = grep $_, @classnum;
238 push @c_where, 'part_pkg.classnum IN ('. join(',',@nums). ')' if @nums;
239 my $null = scalar( grep { $_ eq '' } @classnum );
240 push @c_where, 'part_pkg.classnum IS NULL' if $null;
242 if ( scalar(@c_where) == 1 ) {
243 push @where, @c_where;
244 } elsif ( @c_where ) {
245 push @where, ' ( '. join(' OR ', @c_where). ' ) ';
254 # parse package report options
257 my @report_option = ();
258 if ( exists($params->{'report_option'}) ) {
259 if ( ref($params->{'report_option'}) eq 'ARRAY' ) {
260 @report_option = @{ $params->{'report_option'} };
261 } elsif ( $params->{'report_option'} =~ /^([,\d]*)$/ ) {
262 @report_option = split(',', $1);
267 if (@report_option) {
268 # this will result in the empty set for the dangling comma case as it should
270 map{ "0 < ( SELECT count(*) FROM part_pkg_option
271 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
272 AND optionname = 'report_option_$_'
273 AND optionvalue = '1' )"
277 foreach my $any ( grep /^report_option_any/, keys %$params ) {
279 my @report_option_any = ();
280 if ( ref($params->{$any}) eq 'ARRAY' ) {
281 @report_option_any = @{ $params->{$any} };
282 } elsif ( $params->{$any} =~ /^([,\d]*)$/ ) {
283 @report_option_any = split(',', $1);
286 if (@report_option_any) {
287 # this will result in the empty set for the dangling comma case as it should
288 push @where, ' ( '. join(' OR ',
289 map{ "0 < ( SELECT count(*) FROM part_pkg_option
290 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
291 AND optionname = 'report_option_$_'
292 AND optionvalue = '1' )"
303 push @where, "part_pkg.custom = 'Y'" if $params->{custom};
309 push @where, "(part_pkg.fcc_ds0s > 0 OR pkg_class.fcc_ds0s > 0)"
310 if $params->{fcc_line};
316 if ( exists($params->{'censustract'}) ) {
317 $params->{'censustract'} =~ /^([.\d]*)$/;
318 my $censustract = "cust_location.censustract = '$1'";
319 $censustract .= ' OR cust_location.censustract is NULL' unless $1;
320 push @where, "( $censustract )";
326 if ( exists($params->{'censustract2'})
327 && $params->{'censustract2'} =~ /^(\d*)$/
331 push @where, "cust_location.censustract LIKE '$1%'";
334 "( cust_location.censustract = '' OR cust_location.censustract IS NULL )";
339 # parse country/state
341 for (qw(state country)) { # parsing rules are the same for these
342 if ( exists($params->{$_})
343 && uc($params->{$_}) =~ /^([A-Z]{2})$/ )
345 # XXX post-2.3 only--before that, state/country may be in cust_main
346 push @where, "cust_location.$_ = '$1'";
353 if ( $params->{location_cust} xor $params->{location_nocust} ) {
354 my $op = $params->{location_cust} ? '=' : '!=';
355 push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
357 if ( $params->{location_census} xor $params->{location_nocensus} ) {
358 my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
359 push @where, "cust_location.censustract $op";
361 if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
362 my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
363 push @where, "cust_location.geocode $op";
370 if ( ref($params->{'pkgpart'}) ) {
373 if ( ref($params->{'pkgpart'}) eq 'HASH' ) {
374 @pkgpart = grep $params->{'pkgpart'}{$_}, keys %{ $params->{'pkgpart'} };
375 } elsif ( ref($params->{'pkgpart'}) eq 'ARRAY' ) {
376 @pkgpart = @{ $params->{'pkgpart'} };
378 die 'unhandled pkgpart ref '. $params->{'pkgpart'};
381 @pkgpart = grep /^(\d+)$/, @pkgpart;
383 push @where, 'pkgpart IN ('. join(',', @pkgpart). ')' if scalar(@pkgpart);
385 } elsif ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
386 push @where, "pkgpart = $1";
395 #false laziness w/report_cust_pkg.html
398 'one-time charge' => { 'last_bill'=>1, 'bill'=>1, 'adjourn'=>1, 'susp'=>1, 'expire'=>1, 'cancel'=>1, },
399 'active' => { 'susp'=>1, 'cancel'=>1 },
400 'suspended' => { 'cancel' => 1 },
405 if( exists($params->{'active'} ) ) {
406 # This overrides all the other date-related fields, and includes packages
407 # that were active at some time during the interval. It excludes:
408 # - packages that were set up after the end of the interval
409 # - packages that were canceled before the start of the interval
410 # - packages that were suspended before the start of the interval
411 # and are still suspended now
412 my($beginning, $ending) = @{$params->{'active'}};
414 "cust_pkg.setup IS NOT NULL",
415 "cust_pkg.setup <= $ending",
416 "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )",
417 "(cust_pkg.susp IS NULL OR cust_pkg.susp >= $beginning )",
418 "NOT (".FS::cust_pkg->onetime_sql . ")";
421 foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) {
423 next unless exists($params->{$field});
425 my($beginning, $ending) = @{$params->{$field}};
427 next if $beginning == 0 && $ending == 4294967295;
430 "cust_pkg.$field IS NOT NULL",
431 "cust_pkg.$field >= $beginning",
432 "cust_pkg.$field <= $ending";
434 $orderby ||= "ORDER BY cust_pkg.$field";
439 $orderby ||= 'ORDER BY bill';
442 # parse magic, legacy, etc.
445 if ( $params->{'magic'} &&
446 $params->{'magic'} =~ /^(active|inactive|suspended|cancell?ed)$/
449 $orderby = 'ORDER BY pkgnum';
451 if ( $params->{'pkgpart'} =~ /^(\d+)$/ ) {
452 push @where, "pkgpart = $1";
455 } elsif ( $params->{'query'} eq 'pkgnum' ) {
457 $orderby = 'ORDER BY pkgnum';
459 } elsif ( $params->{'query'} eq 'APKG_pkgnum' ) {
461 $orderby = 'ORDER BY pkgnum';
464 SELECT count(*) FROM pkg_svc
465 WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
466 AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
467 WHERE cust_svc.pkgnum = cust_pkg.pkgnum
468 AND cust_svc.svcpart = pkg_svc.svcpart
475 # setup queries, links, subs, etc. for the search
478 # here is the agent virtualization
479 if ($params->{CurrentUser}) {
481 qsearchs('access_user', { username => $params->{CurrentUser} });
484 push @where, $access_user->agentnums_sql('table'=>'cust_main');
489 push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
492 my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
494 my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
495 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
496 'LEFT JOIN cust_location USING ( locationnum ) '.
497 FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
501 if ( $params->{'select_zip5'} ) {
502 my $zip = 'cust_location.zip';
504 $select = "DISTINCT substr($zip,1,5) as zip";
505 $orderby = "ORDER BY substr($zip,1,5)";
506 $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )";
510 ( map "part_pkg.$_", qw( pkg freq ) ),
511 'pkg_class.classname',
512 'cust_main.custnum AS cust_main_custnum',
513 FS::UI::Web::cust_sql_fields(
514 $params->{'cust_fields'}
517 $count_query = 'SELECT COUNT(*)';
520 $count_query .= " FROM cust_pkg $addl_from $extra_sql";
523 'table' => 'cust_pkg',
526 'extra_sql' => $extra_sql,
527 'order_by' => $orderby,
528 'addl_from' => $addl_from,
529 'count_query' => $count_query,