improve speed in customer search, #13364

[freeside.git] / httemplate / search / svc_phone.cgi

diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi
index dccc674..2943408 100644 (file)
--- a/httemplate/search/svc_phone.cgi
+++ b/httemplate/search/svc_phone.cgi
@@ -81,8 +81,8 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
     $redirect = '';
 
-    my $and_date = " AND startdate >= $beginning ".
-                   " AND startdate <= $ending ";
+    #my $and_date = " AND startdate >= $beginning AND startdate <= $ending ";
+    my $and_date = " AND enddate >= $beginning AND enddate <= $ending ";
 
     my $fromwhere = " FROM cdr WHERE cdr.svcnum = svc_phone.svcnum $and_date";
 
@@ -102,14 +102,14 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
       my $f_w =
         " FROM cdr_termination LEFT JOIN cdr USING ( acctid ) ".
-        " WHERE cdr.acctid = svc_phone.phonenum ". # XXX connectone-specific
+        " WHERE cdr.carrierid = CAST(svc_phone.phonenum AS BIGINT) ". # XXX connectone-specific, has to match svc_external.id :/
         $and_date;
 
       push @select,
-        " ( SELECT SUM(billsec) $f_w ) AS term_billsec ".
+        " ( SELECT SUM(billsec) $f_w ) AS term_billsec ",
         " ( SELECT SUM(cdr_termination.rated_price) $f_w ) AS term_rated_price";
 
-      push @header, 'Term Min', 'Term Billed',
+      push @header, 'Term Min', 'Term Billed';
       push @fields,
         sub { sprintf('%.3f', shift->get('term_billsec') / 60 ); },
         sub { $money_char. sprintf('%.2f', shift->get('rated_price') ); };
@@ -157,10 +157,12 @@ my $sql_query = {
   'select'    => join(', ',
                    'svc_phone.*',
                    'part_svc.svc',
-                    'cust_main.custnum',
-                    FS::UI::Web::cust_sql_fields(),
+                   @select,
+                   'cust_main.custnum',
+                   FS::UI::Web::cust_sql_fields(),
                  ),
-  'extra_sql' => "$extra_sql $orderby",
+  'extra_sql' => $extra_sql,
+  'order_by'  => $orderby,
   'addl_from' => $addl_from,
 };