fix "column refnum is ambiguous" error pulling up churn detail when you have a single...

[freeside.git] / FS / FS / cust_pkg / Search.pm

diff --git a/FS/FS/cust_pkg/Search.pm b/FS/FS/cust_pkg/Search.pm
index 5091236..311dbdb 100644 (file)
--- a/FS/FS/cust_pkg/Search.pm
+++ b/FS/FS/cust_pkg/Search.pm
@@ -118,6 +118,15 @@ 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.
 
 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
 =back
 
 =cut


@@ -272,6 +281,21 @@ sub search {
   }
 
   ###
   }
 
   ###

+  # 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
   ###
 
   # parse package report options
   ###
 


@@ -288,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, 
   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;
   }
 
          } @report_option;
   }
 


@@ -307,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 ',
     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
       ). ' ) ';
     }
            } @report_option_any
       ). ' ) ';
     }


@@ -426,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
     # 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


@@ -440,40 +465,51 @@ 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 . ")";
       "(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 )) {
 
     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 {
+
+        next unless exists($params->{$field});
+
+        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";
+
+        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;
+        }

 
 

-      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) {
     }
 
     if ($exclude_change_from) {

-      push @where, "change_pkgnum IS NULL";
+      push @where, "cust_pkg.change_pkgnum IS NULL";

     }
     if ($exclude_change_to) {
       # a join might be more efficient here
     }
     if ($exclude_change_to) {
       # a join might be more efficient here


@@ -482,6 +518,7 @@ sub search {
         WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
       )";
     }
         WHERE cust_pkg.pkgnum = changed_to_pkg.change_pkgnum
       )";
     }

+

   }
 
   $orderby ||= 'ORDER BY bill';
   }
 
   $orderby ||= 'ORDER BY bill';


@@ -540,6 +577,39 @@ sub search {
   }
 
   ##
   }
 
   ##

+  # 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
   ##
 
   # setup queries, links, subs, etc. for the search
   ##