=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
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<FS::Report::FCC_477> 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
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' ) {
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
}
###
+ # parse (customer) 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, "cust_main.refnum IN($in)" if length $in;
+ }
+
+ ###
# parse package report options
###
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;
}
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
). ' ) ';
}
}
###
- # parse country/state
+ # parse country/state/zip
###
for (qw(state country)) { # parsing rules are the same for these
if ( exists($params->{$_})
push @where, "cust_location.$_ = '$1'";
}
}
+ if ( exists($params->{zip}) ) {
+ push @where, "cust_location.zip = " . dbh->quote($params->{zip});
+ }
###
# location_* flags
my $op = $params->{location_cust} ? '=' : '!=';
push @where, "cust_location.locationnum $op cust_main.ship_locationnum";
}
- if ( $params->{location_census} xor $params->{location_nocensus} ) {
- my $op = $params->{location_census} ? "IS NOT NULL" : "IS NULL";
- push @where, "cust_location.censustract $op";
+ if ( $params->{location_census} ) {
+ push @where, "cust_location.censustract IS NOT NULL",
+ "cust_location.censusyear = '2020' ";
+ } elsif ( $params->{location_nocensus} ) {
+ push @where, "( cust_location.censustract IS NULL ".
+ " OR cust_location.censusyear != '2020' )";
}
if ( $params->{location_geocode} xor $params->{location_nogeocode} ) {
my $op = $params->{location_geocode} ? "IS NOT NULL" : "IS NULL";
'' => {},
);
- if( exists($params->{'active'} ) ) {
- # This overrides all the other date-related fields
+ 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
+ # - packages that were canceled before the start of the interval
+ # - packages that were suspended before the start of the interval
+ # and are still suspended now
my($beginning, $ending) = @{$params->{'active'}};
push @where,
"cust_pkg.setup IS NOT NULL",
"cust_pkg.setup <= $ending",
"(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'} ) {
- my($beginning, $ending) = @{$params->{$field}};
+ push @where, "cust_pkg.$field IS NULL";
+ # this should surely be obsoleted by now: OR cust_pkg.$field == 0 )
- next if $beginning == 0 && $ending == 4294967295;
+ } else {
- push @where,
- "cust_pkg.$field IS NOT NULL",
- "cust_pkg.$field >= $beginning",
- "cust_pkg.$field <= $ending";
+ next unless exists($params->{$field});
+
+ my($beginning, $ending) = @{$params->{$field}};
+
+ next if $beginning == 0 && $ending == 4294967295;
- $orderby ||= "ORDER BY cust_pkg.$field";
+ push @where,
+ "cust_pkg.$field IS NOT NULL",
+ "cust_pkg.$field >= $beginning",
+ "cust_pkg.$field <= $ending";
+
+ $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';
}
##
+ # 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
##
push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
}
+ push @where, "cust_pkg_reason.reasonnum = '".$params->{reasonnum}."'" if $params->{reasonnum};
+
my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
my $addl_from = 'LEFT JOIN part_pkg USING ( pkgpart ) '.
'LEFT JOIN cust_location USING ( locationnum ) '.
FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
+ if ($params->{reasonnum}) {
+ $addl_from .= 'LEFT JOIN cust_pkg_reason ON (cust_pkg_reason.pkgnum = cust_pkg.pkgnum) ';
+ }
+
my $select;
my $count_query;
if ( $params->{'select_zip5'} ) {
projects / freeside.git / blobdiff