improve performance of DID provisioning status report, RT10988

[freeside.git] / httemplate / search / phone_inventory_provisioned.html

diff --git a/httemplate/search/phone_inventory_provisioned.html b/httemplate/search/phone_inventory_provisioned.html
index 37fef8a..52657e7 100644 (file)
--- a/httemplate/search/phone_inventory_provisioned.html
+++ b/httemplate/search/phone_inventory_provisioned.html
@@ -7,77 +7,72 @@
                                    'select'  => 'distinct latanum',
                                  },
               'count_query'   => 'SELECT COUNT(distinct latanum) FROM phone_avail',
-              'header'        => [ 'LATA',
+              'header'        => [ 
+                    'LATA #',
+                    'LATA Description',
                                   'Available',
                                   'Provisioned',
                                   'Have Usage',
                                  ],
               'fields'        => [
-        sub { # LATA
-            my $did_order = shift;
-            my $lata = $did_order->lata;
-            $lata = $lata ? $lata->description : '';
-            $did_order->latanum . " - " . $lata;
+                'latanum',
+        sub { # LATA Description
+            my $phone_avail = shift;
+            return '' unless $phone_avail->lata;
+            $phone_avail->lata->description;
         },
                sub { # Available
                    my $latanum = shift->latanum;
-                   my @dids = qsearch('phone_avail',
+                   my $cnt = qsearchs({ 'table'    => 'phone_avail',
+                                 'hashref'  => 
                                     { 'svcnum' => '',
                                       'latanum' => $latanum,
-                                    }
-                          );
-                   return scalar(@dids);
+                                    },
+                                 'select'   => 'count(1) as cnt',
+                          });
+                   ($cnt) ? $cnt->cnt : '';
                },
                sub { # Provisioned
                    my $latanum = shift->latanum;
-                   my @dids = provisioned_dids($latanum);
-                   return scalar(@dids);
+            my $cnt = qsearchs({   'table'     => 'phone_avail',
+                        'hashref'   => { 'latanum' => $latanum,
+                                         'svcnum' => { op => '>',
+                                                        value => '0'
+                                                     },
+                                        },
+                        'select'    => 'count(1) as cnt'
+                    });
+                   ($cnt) ? $cnt->cnt : '';
                },
                sub { # Have Usage
-            my $did_order = shift;
-                   my @dids = provisioned_dids($did_order->latanum);
-
-                   my $count = 0;
-                   foreach my $did ( @dids ) {
-                my $cust_svc = $did->cust_svc;
-                next unless $cust_svc;
-
-                my $svcnum = $cust_svc->svcnum;
-                my $sql = "select count(1) from cdr where src = 
-                    (select phonenum from svc_phone where svcnum = $svcnum)
-                    or charged_party = (select phonenum from svc_phone where
-                        svcnum = $svcnum)";
-                my $c = $did_order->scalar_sql($sql);
-                $count++ if $c > 0;
-                   }
-                   $count;
+            my $phone_avail = shift;
+            return '' unless $phone_avail->lata;
+            $phone_avail->lata->have_usage;
                },
               ],
               'align'         => 'lccc',
               'links'         => [
                 '',
-               '',
-               '',
-               '',
+                '',
+                '',
+                '',
               ],
               'color' => [ 
                            '',
-                          '',
-                          '',
-                          '',
+                           '',
+                           '',
+                           '',
                          ],
               'style' => [ 
                            '',
-                          '',
-                          '',
-                          '',
+                           '',
+                           '',
+                           '',
                          ],
       )
 %>
 <%init>
 
-warn "started @ ".time;
-
 die "access denied"
   unless ( $FS::CurrentUser::CurrentUser->access_right('List inventory')
         && $FS::CurrentUser::CurrentUser->access_right('List services')
@@ -85,12 +80,4 @@ die "access denied"
 
 # XXX: agent virtualize
 
-sub provisioned_dids {
-    my $latanum = shift;
-    qsearch({   'table' => 'phone_avail',
-                   'hashref' => { 'latanum' => $latanum, },
-                   'extra_sql' => ' and svcnum is not null ',
-               });
-}
-
 </%init>