add service definition browse by number of active services

4 files changed, 37 insertions, 1 deletions

diff --git a/httemplate/browse/part_svc.cgi b/httemplate/browse/part_svc.cgi
index 3fece2925..ef0de13cc 100755
--- a/httemplate/browse/part_svc.cgi
+++ b/httemplate/browse/part_svc.cgi
@@ -13,6 +13,20 @@ my @part_svc =
     qsearch('part_svc', \%search );
 my $total = scalar(@part_svc);
 
+my %num_active_cust_svc = ();
+if ( $cgi->param('active') ) {
+  my $active_sth = dbh->prepare(
+    'SELECT COUNT(*) FROM cust_svc WHERE svcpart = ?'
+  ) or die dbh->errstr;
+  foreach my $part_svc ( @part_svc ) {
+    $active_sth->execute($part_svc->svcpart) or die $active_sth->errstr;
+    $num_active_cust_svc{$part_svc->svcpart} =
+      $active_sth->fetchrow_arrayref->[0];
+  }
+  @part_svc = sort { $num_active_cust_svc{$b->svcpart} <=>
+                     $num_active_cust_svc{$a->svcpart}     } @part_svc;
+}
+
 %>
 <%= header('Service Definition Listing', menubar( 'Main Menu' => $p) ) %>
 
@@ -45,6 +59,9 @@ function part_export_areyousure(href) {
   <TR>
     <TH COLSPAN=<%= $cgi->param('showdisabled') ? 2 : 3 %>>Service</TH>
     <TH>Table</TH>
+<% if ( $cgi->param('active') ) { %>
+    <TH><FONT SIZE=-1>Customer<BR>Services</FONT></TH>
+<% } %>
     <TH>Export</TH>
     <TH>Field</TH>
     <TH COLSPAN=2>Modifier</TH>
@@ -75,6 +92,11 @@ function part_export_areyousure(href) {
       <%= $hashref->{svc} %></A></TD>
     <TD ROWSPAN=<%= $rowspan %>>
       <%= $hashref->{svcdb} %></TD>
+<% if ( $cgi->param('active') ) { %>
+    <TD ROWSPAN=<%= $rowspan %>>
+      <FONT COLOR="#00CC00"><B><%= $num_active_cust_svc{$hashref->{svcpart}} %></B></FONT>&nbsp;<A HREF="<%=$p%>search/<%= $hashref->{svcdb} %>.cgi?svcpart=<%= $hashref->{svcpart} %>">active</A>
+    </TD>
+<% } %>
     <TD ROWSPAN=<%= $rowspan %>><%= itable() %>
 <%
 #  my @part_export =
diff --git a/httemplate/index.html b/httemplate/index.html
index 2d4f6b51c..7d90efc71 100644
--- a/httemplate/index.html
+++ b/httemplate/index.html
@@ -127,6 +127,7 @@
         <LI><A HREF="search/cust_pkg_report.cgi">packages (by next bill date range)</A>
       </UL>
       <A HREF="browse/part_pkg.cgi?active=1">Package definitions (by number of active packages)</A><BR><BR>
+      <A HREF="browse/part_svc.cgi?active=1">Service definitions (by number of active services)</A><BR><BR>
     Customers
       <UL>
         <LI><A HREF="search/cust_main-otaker.cgi">Search customers by order-taker</A>
diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi
index e20649cd9..671575b55 100755
--- a/httemplate/search/svc_acct.cgi
+++ b/httemplate/search/svc_acct.cgi
@@ -46,13 +46,19 @@ if ( $query eq 'svcnum' ) {
   $orderby = "ORDER BY ${tblname}username";
 } elsif ( $query eq 'uid' ) {
   $sortby=\*uid_sort;
-  $orderby = ( $unlinked ? 'AND' : 'WHERE' ).
+  $orderby = ( $unlinked ? ' AND' : ' WHERE' ).
              " ${tblname}uid IS NOT NULL ORDER BY ${tblname}uid";
 } elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) {
   $unlinked .= ( $unlinked ? 'AND' : 'WHERE' ).
                " popnum = $1";
   $sortby=\*username_sort;
   $orderby = "ORDER BY ${tblname}username";
+} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) {
+  $unlinked .= ( $unlinked ? ' AND' : ' WHERE' ).
+               " $1 = ( SELECT svcpart FROM cust_svc ".
+               "        WHERE cust_svc.svcnum = svc_acct.svcnum ) ";
+  $sortby=\*uid_sort;
+  #$sortby=\*svcnum_sort;
 } else {
   $sortby=\*uid_sort;
   @svc_acct = @{&usernamesearch};
diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi
index c0acf1143..948b1d9ae 100755
--- a/httemplate/search/svc_domain.cgi
+++ b/httemplate/search/svc_domain.cgi
@@ -23,6 +23,13 @@ if ( $query eq 'svcnum' ) {
       'svcnum' => $_->svcnum,
       'pkgnum' => '',
     }), qsearch('svc_domain',{});
+} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) {
+  @svc_domain =
+    qsearch( 'svc_domain', {}, '',
+               " WHERE $1 = ( SELECT svcpart FROM cust_svc ".
+               "              WHERE cust_svc.svcnum = svc_domain.svcnum ) "
+    );
+  $sortby=\*svcnum_sort;
 } else {
   $cgi->param('domain') =~ /^([\w\-\.]+)$/; 
   my($domain)=$1;