X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_pkg%2FSearch.pm;h=3a8e6d01edc9185f72aa433514268d5b75da817d;hb=b49e60810267101b83b9b2133e5dfc90d1663cdc;hp=47efd31402a4a32731fe017c25b09a8c18fab36b;hpb=c2e6d527477334107535c74b44e238cc656f3449;p=freeside.git diff --git a/FS/FS/cust_pkg/Search.pm b/FS/FS/cust_pkg/Search.pm index 47efd3140..3a8e6d01e 100644 --- a/FS/FS/cust_pkg/Search.pm +++ b/FS/FS/cust_pkg/Search.pm @@ -17,13 +17,15 @@ Valid parameters are =item agentnum -=item magic +=item status -active, inactive, suspended, cancel (or cancelled) +on hold, active, inactive (or one-time charge), suspended, canceled (or cancelled) -=item status +=item magic -active, inactive, suspended, one-time charge, inactive, cancel (or cancelled) +Equivalent to "status", except that "canceled"/"cancelled" will exclude +packages that were changed into a new package with the same pkgpart (i.e. +location or quantity changes). =item custom @@ -110,6 +112,21 @@ Limit to packages whose locations have geocodes. Limit to packages whose locations do not have geocodes. +=item towernum + +Limit to packages associated with a svc_broadband, associated with a sector, +associated with this towernum (or any of these, if it's an arrayref) (or NO +towernum, if it's zero). This is an extreme niche case. + +=item 477part, 477rownum, date + +Limit to packages included in a specific row of one of the FCC 477 reports. +'477part' is the section name (see L methods), 'date' +is the report as-of date (completely unrelated to the package setup/bill/ +other date fields), and '477rownum' is the row number of the report starting +with zero. Row numbers have no inherent meaning, so this is useful only +for explaining a 477 report you've already run. + =back =cut @@ -191,6 +208,12 @@ sub search { push @where, FS::cust_pkg->inactive_sql(); + } elsif ( $params->{'magic'} =~ /^on[ _]hold$/ + || $params->{'status'} =~ /^on[ _]hold$/ ) { + + push @where, FS::cust_pkg->on_hold_sql(); + + } elsif ( $params->{'magic'} eq 'suspended' || $params->{'status'} eq 'suspended' ) { @@ -202,6 +225,19 @@ sub search { push @where, FS::cust_pkg->cancelled_sql(); } + + ### special case: "magic" is used in detail links from browse/part_pkg, + # where "cancelled" has the restriction "and not replaced with a package + # of the same pkgpart". Be consistent with that. + ### + + if ( $params->{'magic'} =~ /^cancell?ed$/ ) { + my $new_pkgpart = "SELECT pkgpart FROM cust_pkg AS cust_pkg_next ". + "WHERE cust_pkg_next.change_pkgnum = cust_pkg.pkgnum"; + # ...may not exist, if this was just canceled and not changed; in that + # case give it a "new pkgpart" that never equals the old pkgpart + push @where, "COALESCE(($new_pkgpart), 0) != cust_pkg.pkgpart"; + } ### # parse package class @@ -245,6 +281,21 @@ sub search { } ### + # parse refnum (advertising source) + ### + + if ( exists($params->{'refnum'}) ) { + my @refnum; + if (ref $params->{'refnum'}) { + @refnum = @{ $params->{'refnum'} }; + } else { + @refnum = ( $params->{'refnum'} ); + } + my $in = join(',', grep /^\d+$/, @refnum); + push @where, "refnum IN($in)" if length $in; + } + + ### # parse package report options ### @@ -261,10 +312,10 @@ sub search { if (@report_option) { # this will result in the empty set for the dangling comma case as it should push @where, - map{ "0 < ( SELECT count(*) FROM part_pkg_option - WHERE part_pkg_option.pkgpart = part_pkg.pkgpart - AND optionname = 'report_option_$_' - AND optionvalue = '1' )" + map{ "EXISTS ( SELECT 1 FROM part_pkg_option + WHERE part_pkg_option.pkgpart = part_pkg.pkgpart + AND optionname = 'report_option_$_' + AND optionvalue = '1' )" } @report_option; } @@ -280,10 +331,10 @@ sub search { if (@report_option_any) { # this will result in the empty set for the dangling comma case as it should push @where, ' ( '. join(' OR ', - map{ "0 < ( SELECT count(*) FROM part_pkg_option - WHERE part_pkg_option.pkgpart = part_pkg.pkgpart - AND optionname = 'report_option_$_' - AND optionvalue = '1' )" + map{ "EXISTS ( SELECT 1 FROM part_pkg_option + WHERE part_pkg_option.pkgpart = part_pkg.pkgpart + AND optionname = 'report_option_$_' + AND optionvalue = '1' )" } @report_option_any ). ' ) '; } @@ -330,7 +381,7 @@ sub search { } ### - # parse country/state + # parse country/state/zip ### for (qw(state country)) { # parsing rules are the same for these if ( exists($params->{$_}) @@ -340,6 +391,9 @@ sub search { push @where, "cust_location.$_ = '$1'"; } } + if ( exists($params->{zip}) ) { + push @where, "cust_location.zip = " . dbh->quote($params->{zip}); + } ### # location_* flags @@ -396,7 +450,8 @@ sub search { '' => {}, ); - if( exists($params->{'active'} ) ) { + if ( exists($params->{'active'} ) ) { + # This overrides all the other date-related fields, and includes packages # that were active at some time during the interval. It excludes: # - packages that were set up after the end of the interval @@ -410,24 +465,60 @@ sub search { "(cust_pkg.cancel IS NULL OR cust_pkg.cancel >= $beginning )", "(cust_pkg.susp IS NULL OR cust_pkg.susp >= $beginning )", "NOT (".FS::cust_pkg->onetime_sql . ")"; - } - else { + + } else { + + my $exclude_change_from = 0; + my $exclude_change_to = 0; + foreach my $field (qw( setup last_bill bill adjourn susp expire contract_end change_date cancel )) { - next unless exists($params->{$field}); + if ( $params->{$field.'_null'} ) { + + push @where, "cust_pkg.$field IS NULL"; + # this should surely be obsoleted by now: OR cust_pkg.$field == 0 ) - my($beginning, $ending) = @{$params->{$field}}; + } else { - next if $beginning == 0 && $ending == 4294967295; + next unless exists($params->{$field}); - push @where, - "cust_pkg.$field IS NOT NULL", - "cust_pkg.$field >= $beginning", - "cust_pkg.$field <= $ending"; + my($beginning, $ending) = @{$params->{$field}}; + + next if $beginning == 0 && $ending == 4294967295; + + push @where, + "cust_pkg.$field IS NOT NULL", + "cust_pkg.$field >= $beginning", + "cust_pkg.$field <= $ending"; + + $orderby ||= "ORDER BY cust_pkg.$field"; - $orderby ||= "ORDER BY cust_pkg.$field"; + if ( $field eq 'setup' ) { + $exclude_change_from = 1; + } elsif ( $field eq 'cancel' ) { + $exclude_change_to = 1; + } elsif ( $field eq 'change_date' ) { + # if we are given setup and change_date ranges, and the setup date + # falls in _both_ ranges, then include the package whether it was + # a change or not + $exclude_change_from = 0; + } + } + + } + + if ($exclude_change_from) { + push @where, "cust_pkg.change_pkgnum IS NULL"; } + if ($exclude_change_to) { + # a join might be more efficient here + push @where, "NOT EXISTS( + SELECT 1 FROM cust_pkg AS changed_to_pkg + WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum + )"; + } + } $orderby ||= 'ORDER BY bill'; @@ -466,6 +557,59 @@ sub search { } ## + # parse the extremely weird 'towernum' param + ## + + if ($params->{towernum}) { + my $towernum = $params->{towernum}; + $towernum = [ $towernum ] if !ref($towernum); + my $in = join(',', grep /^\d+$/, @$towernum); + if (length $in) { + # inefficient, but this is an obscure feature + eval "use FS::Report::Table"; + FS::Report::Table->_init_tower_pkg_cache; # probably does nothing + push @where, "EXISTS( + SELECT 1 FROM tower_pkg_cache + WHERE tower_pkg_cache.pkgnum = cust_pkg.pkgnum + AND tower_pkg_cache.towernum IN ($in) + )" + } + } + + ## + # parse the 477 report drill-down options + ## + + if ($params->{'477part'} =~ /^([a-z]+)$/) { + my $section = $1; + my ($date, $rownum, $agentnum); + if ($params->{'date'} =~ /^(\d+)$/) { + $date = $1; + } + if ($params->{'477rownum'} =~ /^(\d+)$/) { + $rownum = $1; + } + if ($params->{'agentnum'} =~ /^(\d+)$/) { + $agentnum = $1; + } + if ($date and defined($rownum)) { + my $report = FS::Report::FCC_477->report($section, + 'date' => $date, + 'agentnum' => $agentnum, + 'detail' => 1 + ); + my $pkgnums = $report->{detail}->[$rownum] + or die "row $rownum is past the end of the report"; + # '0' so that if there are no pkgnums (empty string) it will create + # a valid query that returns nothing + warn "PKGNUMS:\n$pkgnums\n\n"; # XXX debug + + # and this overrides everything + @where = ( "cust_pkg.pkgnum IN($pkgnums)" ); + } # else we're missing some params, ignore the whole business + } + + ## # setup queries, links, subs, etc. for the search ##