diff options
| author | Ivan Kohler <ivan@freeside.biz> | 2012-02-24 00:08:31 -0800 |
|---|---|---|
| committer | Ivan Kohler <ivan@freeside.biz> | 2012-02-24 00:08:31 -0800 |
| commit | 2594f387f6c9acf4df181e2f47ffb42ac0de5b52 (patch) | |
| tree | 1fa67d6a13385d37158cc85b3251b15ef49d62d3 /FS/FS/cust_main | |
| parent | 6cb124e556485bb695d4b466e5a9fa103904f5db (diff) | |
fix advanced customer report w/"add package columns" under mysql, RT#16214
Diffstat (limited to 'FS/FS/cust_main')
| -rw-r--r-- | FS/FS/cust_main/Search.pm | 14 |
1 files changed, 9 insertions, 5 deletions
diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 04e4d24..4da516b 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -736,7 +736,7 @@ sub search { my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; - my $pkg_join = 'LEFT JOIN cust_pkg USING ( custnum ) '; + my $addl_from = ''; my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql"; @@ -750,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) { + } elsif ($dbh->{Driver}->{Name} =~ /^mysql/i) { push @select, "GROUP_CONCAT(part_pkg.pkg SEPARATOR '|') as magic"; - $pkg_join .= " LEFT JOIN part_pkg using ( pkgpart )"; - }else{ + $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 $pkg_join $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; @@ -797,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, |