make customer location columns available in various reports, #940

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

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index ca4d167..349f3e3 100644 (file)
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -85,7 +85,7 @@ sub smart_search {
       'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
                      ' ( '.
                          join(' OR ', map "$_ = '$phonen'",
-                                          qw( daytime night fax )
+                                          qw( daytime night mobile fax )
                              ).
                      ' ) '.
                      " AND $agentnums_sql", #agent virtualization
@@ -140,10 +140,12 @@ sub smart_search {
     my $num = $1;
 
     if ( $num =~ /^(\d+)$/ && $num <= 2147483647 ) { #need a bigint custnum? wow
+      my $agent_custid_null = $conf->exists('cust_main-default_agent_custid')
+                                ? ' AND agent_custid IS NULL ' : '';
       push @cust_main, qsearch( {
         'table'     => 'cust_main',
         'hashref'   => { 'custnum' => $num, %options },
-        'extra_sql' => " AND $agentnums_sql", #agent virtualization
+        'extra_sql' => " AND $agentnums_sql $agent_custid_null",
       } );
     }
 
@@ -455,6 +457,8 @@ HASHREF.  Valid parameters are
 
 =item address
 
+=item zip
+
 =item refnum
 
 =item cancelled_pkgs
@@ -473,6 +477,10 @@ listref of start date, end date
 
 listref of start date, end date
 
+=item anniversary_date
+
+listref of start date, end date
+
 =item payby
 
 listref
@@ -510,6 +518,7 @@ sub search {
     'usernum'       => '',
     'status'        => '',
     'address'       => '',
+    'zip'           => '',
     'paydate_year'  => '',
     'invoice_terms' => '',
     'custbatch'     => '',
@@ -572,11 +581,32 @@ sub search {
     )";
   }
 
+  ##
+  # zipcode
+  ##
+  if ( $params->{'zip'} =~ /\S/ ) {
+    my $zip = dbh->quote($params->{'zip'} . '%');
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.zip LIKE $zip
+    )";
+  }
+
   ###
   # refnum
   ###
-  if ( $params->{'refnum'} =~ /^(\d+)$/ ) {
-    push @where, "refnum = $1";
+  if ( $params->{'refnum'}  ) {
+
+    my @refnum = ref( $params->{'refnum'} )
+                   ? @{ $params->{'refnum'} }
+                   :  ( $params->{'refnum'} );
+
+    @refnum = grep /^(\d*)$/, @refnum;
+
+    push @where, '( '. join(' OR ', map "cust_main.refnum = $_", @refnum ). ' )'
+      if @refnum;
+
   }
 
   ##
@@ -603,10 +633,31 @@ sub search {
     if $params->{'with_geocode'};
 
   ##
+  # "with email address(es)" checkbox
+  ##
+
+  push @where,
+    'EXISTS ( SELECT 1 FROM cust_main_invoice
+                WHERE cust_main_invoice.custnum = cust_main.custnum
+                  AND length(dest) > 5
+            )'  # AND dest LIKE '%@%'
+    if $params->{'with_email'};
+
+  ##
+  # "without postal mail invoices" checkbox
+  ##
+
+  push @where,
+    "NOT EXISTS ( SELECT 1 FROM cust_main_invoice
+                    WHERE cust_main_invoice.custnum = cust_main.custnum
+                      AND dest = 'POST' )"
+    if $params->{'no_POST'};
+
+  ##
   # dates
   ##
 
-  foreach my $field (qw( signupdate birthdate spouse_birthdate )) {
+  foreach my $field (qw( signupdate birthdate spouse_birthdate anniversary_date )) {
 
     next unless exists($params->{$field});
 
@@ -763,11 +814,20 @@ sub search {
   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 
   my $addl_from = '';
+  # always make address fields available in results
+  for my $pre ('bill_', 'ship_') {
+    $addl_from .= 
+      'LEFT JOIN cust_location AS '.$pre.'location '.
+      'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) ';
+  }
 
   my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql";
 
   my @select = (
                  'cust_main.custnum',
+                 # there's a good chance that we'll need these
+                 'cust_main.bill_locationnum',
+                 'cust_main.ship_locationnum',
                  FS::UI::Web::cust_sql_fields($params->{'cust_fields'}),
                );
 
@@ -777,22 +837,33 @@ sub search {
   if ($params->{'flattened_pkgs'}) {
 
     #my $pkg_join = '';
+    $addl_from .= ' LEFT JOIN cust_pkg USING ( custnum ) ';
 
     if ($dbh->{Driver}->{Name} eq 'Pg') {
 
-      push @select, "array_to_string(array(select pkg from cust_pkg left join part_pkg using ( pkgpart ) where cust_main.custnum = cust_pkg.custnum $pkgwhere),'|') as magic";
+      push @select, "
+        ARRAY_TO_STRING(
+          ARRAY(
+            SELECT pkg FROM cust_pkg LEFT JOIN part_pkg USING ( pkgpart )
+              WHERE cust_main.custnum = cust_pkg.custnum $pkgwhere
+          ), '|'
+        ) AS magic
+      ";
 
     } elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
       push @select, "GROUP_CONCAT(part_pkg.pkg SEPARATOR '|') as magic";
-      $addl_from .= ' LEFT JOIN cust_pkg USING ( custnum ) '; #Pg too w/flatpkg?
       $addl_from .= ' LEFT JOIN part_pkg USING ( pkgpart ) ';
       #$pkg_join  .= ' LEFT JOIN part_pkg USING ( pkgpart ) ';
     } else {
       warn "warning: unknown database type ". $dbh->{Driver}->{Name}. 
-           "omitting packing information from report.";
+           "omitting package information from report.";
     }
 
-    my $header_query = "SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count FROM cust_main $addl_from $extra_sql $pkgwhere group by cust_main.custnum order by count desc limit 1";
+    my $header_query = "
+      SELECT COUNT(cust_pkg.custnum = cust_main.custnum) AS count
+        FROM cust_main $addl_from $extra_sql $pkgwhere
+          GROUP BY cust_main.custnum ORDER BY count DESC LIMIT 1
+    ";
 
     my $sth = dbh->prepare($header_query) or die dbh->errstr;
     $sth->execute() or die $sth->errstr;