fix advanced customer report w/"add package columns" under mysql, RT#16214

[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 db752ad..4da516b 100644 (file)
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -64,7 +64,8 @@ sub smart_search {
   my %options = @_;
 
   #here is the agent virtualization
-  my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql;
+  my $agentnums_sql = 
+    $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main');
 
   my @cust_main = ();
 
@@ -730,11 +731,12 @@ sub search {
   $orderby ||= 'ORDER BY custnum';
 
   # here is the agent virtualization
-  push @where, $FS::CurrentUser::CurrentUser->agentnums_sql;
+  push @where,
+    $FS::CurrentUser::CurrentUser->agentnums_sql(table => 'cust_main');
 
   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 
-  my $addl_from = 'LEFT JOIN cust_pkg USING ( custnum  ) ';
+  my $addl_from = '';
 
   my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql";
 
@@ -748,19 +750,22 @@ sub search {
 
   if ($params->{'flattened_pkgs'}) {
 
+    my $pkg_join = '';
+
     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";
 
-    }elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) {
-      push @select, "GROUP_CONCAT(pkg SEPARATOR '|') as magic";
-      $addl_from .= " LEFT JOIN part_pkg using ( pkgpart )";
-    }else{
+    } 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?
+      $pkg_join  .= ' LEFT JOIN part_pkg USING ( pkgpart ) ';
+    } else {
       warn "warning: unknown database type ". $dbh->{Driver}->{Name}. 
            "omitting packing 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 $pkg_join $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;
@@ -795,6 +800,7 @@ sub search {
   my $sql_query = {
     'table'         => 'cust_main',
     'select'        => $select,
+    'addl_from'     => $addl_from,
     'hashref'       => {},
     'extra_sql'     => $extra_sql,
     'order_by'      => $orderby,
@@ -880,7 +886,7 @@ sub rebuild_fuzzyfiles {
     flock(LOCK,LOCK_EX)
       or die "can't lock $dir/cust_main.$fuzzy: $!";
 
-    open (CACHE,">$dir/cust_main.$fuzzy.tmp")
+    open (CACHE, '>:encoding(UTF-8)', "$dir/cust_main.$fuzzy.tmp")
       or die "can't open $dir/cust_main.$fuzzy.tmp: $!";
 
     foreach my $field ( $fuzzy, "ship_$fuzzy" ) {
@@ -920,7 +926,7 @@ sub append_fuzzyfiles {
 
     if ( $value ) {
 
-      open(CACHE,">>$dir/cust_main.$field")
+      open(CACHE, '>>:encoding(UTF-8)', "$dir/cust_main.$field" )
         or die "can't open $dir/cust_main.$field: $!";
       flock(CACHE,LOCK_EX)
         or die "can't lock $dir/cust_main.$field: $!";
@@ -944,7 +950,7 @@ sub append_fuzzyfiles {
 sub all_X {
   my( $self, $field ) = @_;
   my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
-  open(CACHE,"<$dir/cust_main.$field")
+  open(CACHE, '<:encoding(UTF-8)', "$dir/cust_main.$field")
     or die "can't open $dir/cust_main.$field: $!";
   my @array = map { chomp; $_; } <CACHE>;
   close CACHE;