From ef5eceda8446cb44a5c5e2ad0d8ff979b9eb7bd8 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Sat, 14 Jul 2012 21:11:57 -0700 Subject: [PATCH] fix 477 report part V, RT#18503 --- FS/FS/cust_pkg.pm | 40 ++++++++++++++++++++++++++++++---------- 1 file changed, 30 insertions(+), 10 deletions(-) diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index 8297d2211..aed99e51d 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -3599,20 +3599,40 @@ sub search { 'LEFT JOIN part_pkg USING ( pkgpart ) '. 'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '; - my $count_query = "SELECT COUNT(*) FROM cust_pkg $addl_from $extra_sql"; + my $select; + my $count_query; + if ( $params->{'select_zip5'} ) { + my $zip = 'cust_location.zip'; + + $select = "DISTINCT substr($zip,1,5) as zip"; + $orderby = "ORDER BY substr($zip,1,5)"; + $addl_from .= 'LEFT JOIN cust_location ON ( + cust_location.locationnum = COALESCE( + cust_pkg.locationnum, + cust_main.ship_locationnum, + cust_main.bill_locationnum + ) + )'; + $count_query = "SELECT COUNT( DISTINCT substr($zip,1,5) )"; + } else { + $select = join(', ', + 'cust_pkg.*', + ( map "part_pkg.$_", qw( pkg freq ) ), + 'pkg_class.classname', + 'cust_main.custnum AS cust_main_custnum', + FS::UI::Web::cust_sql_fields( + $params->{'cust_fields'} + ), + ); + $count_query = 'SELECT COUNT(*)'; + } + + $count_query .= " FROM cust_pkg $addl_from $extra_sql"; my $sql_query = { 'table' => 'cust_pkg', 'hashref' => {}, - 'select' => join(', ', - 'cust_pkg.*', - ( map "part_pkg.$_", qw( pkg freq ) ), - 'pkg_class.classname', - 'cust_main.custnum AS cust_main_custnum', - FS::UI::Web::cust_sql_fields( - $params->{'cust_fields'} - ), - ), + 'select' => $select, 'extra_sql' => $extra_sql, 'order_by' => $orderby, 'addl_from' => $addl_from, -- 2.11.0