search services by tower/sector, #15950

11 files changed, 218 insertions, 25 deletions

diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 598cb6a..e4bc6c5 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -2802,6 +2802,22 @@ sub scalar_sql {
   defined($scalar) ? $scalar : '';
 }
 
+=item count [ WHERE ]
+
+Convenience method for the common case of "SELECT COUNT(*) FROM table", 
+with optional WHERE.  Must be called as method on a class with an 
+associated table.
+
+=cut
+
+sub count {
+  my($self, $where) = (shift, shift);
+  my $table = $self->table or die 'count called on object of class '.ref($self);
+  my $sql = "SELECT COUNT(*) FROM $table";
+  $sql .= " WHERE $where" if $where;
+  $self->scalar_sql($sql);
+}
+
 =back
 
 =head1 SUBROUTINES
diff --git a/FS/FS/svc_Tower_Mixin.pm b/FS/FS/svc_Tower_Mixin.pm
index 8caef6d..0b55884 100644
--- a/FS/FS/svc_Tower_Mixin.pm
+++ b/FS/FS/svc_Tower_Mixin.pm
@@ -14,4 +14,43 @@ sub tower_sector {
   qsearchs('tower_sector', { sectornum => $self->sectornum });
 }
 
+=item tower_sector_sql HASHREF
+
+Class method which returns a list of WHERE clause fragments to 
+search for services with tower/sector given by HASHREF.  Can 
+contain 'towernum' and 'sectornum' keys, either of which can be 
+an arrayref or a single value.  To use this, the search needs to
+join to tower_sector.
+
+towernum or sectornum can also contain 'none' to allow null values.
+
+=cut
+
+sub tower_sector_sql {
+  my $class = shift;
+  my $params = shift;
+  return '' unless keys %$params;
+  my $where = '';
+
+  my @where;
+  for my $field (qw(towernum sectornum)) {
+    my $value = $params->{$field} or next;
+    if ( ref $value and grep { $_ } @$value ) {
+      my $in = join(',', map { /^(\d+)$/ ? $1 : () } @$value);
+      my @orwhere;
+      push @orwhere, "tower_sector.$field IN ($in)" if $in;
+      push @orwhere, "tower_sector.$field IS NULL" if grep /^none$/, @$value;
+      push @where, '( '.join(' OR ', @orwhere).' )';
+    }
+    elsif ( $value =~ /^(\d+)$/ ) {
+      push @where, "tower_sector.$field = $1";
+    }
+    elsif ( $value eq 'none' ) {
+      push @where, "tower_sector.$field IS NULL";
+    }
+  }
+  @where;
+}
+
+
 1;
diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm
index 6596354..82a5a8a 100644
--- a/FS/FS/svc_acct.pm
+++ b/FS/FS/svc_acct.pm
@@ -1464,7 +1464,7 @@ sub set_password {
   if ( !$encoding ) {
     # set encoding to system default
     ($encoding, $encryption) =
-      split(/-/, lc($conf->config('default-password-encoding')));
+      split(/-/, lc($conf->config('default-password-encoding') || ''));
     $encoding ||= 'legacy';
     $self->_password_encoding($encoding);
   }
@@ -2848,6 +2848,9 @@ sub search {
     push @where, "svcpart = $1";
   }
 
+  # sector and tower
+  my @where_sector = $class->tower_sector_sql($params);
+  push @where, @where_sector if @where_sector;
 
   # here is the agent virtualization
   #if ($params->{CurrentUser}) {
@@ -2875,6 +2878,9 @@ sub search {
                   ' LEFT JOIN cust_pkg  USING ( pkgnum  ) '.
                   ' LEFT JOIN cust_main USING ( custnum ) ';
 
+  $addl_from .= ' LEFT JOIN tower_sector USING ( sectornum )'
+    if @where_sector;
+
   my $count_query = "SELECT COUNT(*) FROM svc_acct $addl_from $extra_sql";
   #if ( keys %svc_acct ) {
   #  $count_query .= ' WHERE '.
diff --git a/FS/FS/svc_broadband.pm b/FS/FS/svc_broadband.pm
index 7be8a36..fed877d 100755
--- a/FS/FS/svc_broadband.pm
+++ b/FS/FS/svc_broadband.pm
@@ -4,7 +4,7 @@ use strict;
 use vars qw(@ISA $conf);
 
 use base qw(FS::svc_Radius_Mixin FS::svc_Tower_Mixin FS::svc_Common);
-use NetAddr::IP;
+{ no warnings 'redefine'; use NetAddr::IP; }
 use FS::Record qw( qsearchs qsearch dbh );
 use FS::svc_Common;
 use FS::cust_svc;
@@ -159,6 +159,10 @@ Parameters:
 
 =item routernum - arrayref
 
+=item sectornum - arrayref
+
+=item towernum - arrayref
+
 =item order_by
 
 =back
@@ -215,6 +219,13 @@ sub search {
       push @where, "addr_block.routernum = $1";
     }
   }
+
+  #sector and tower, as above
+  my @where_sector = $class->tower_sector_sql($params);
+  if ( @where_sector ) {
+    push @where, @where_sector;
+    push @from, 'LEFT JOIN tower_sector USING ( sectornum )';
+  }
  
   #svcnum
   if ( $params->{'svcnum'} =~ /^(\d+)$/ ) {
diff --git a/httemplate/browse/tower.html b/httemplate/browse/tower.html
index 660e924..0de3b32 100644
--- a/httemplate/browse/tower.html
+++ b/httemplate/browse/tower.html
@@ -9,12 +9,10 @@
                  'disableable' => 1,
                  'disabled_statuspos' => 1,
                  'header'      => [ 'Name', 'Sectors', ],
-                 'fields'      => [ 'towername',
+                 'fields'      => [ $tower_sub,
                                     $sector_sub,
                                   ],
-                 'links'       => [ $link,
-                                    '',
-                                  ],
+                 'links'       => [ ],
              )
 %>
 <%init>
@@ -22,22 +20,66 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Configuration');
 
-my $link = [ "${p}edit/tower.html?", 'towernum' ];
+my $num_svc_links = sub {
+  my ($query_string, $sectors) = @_;
+  my $num_svc_broadband = FS::svc_broadband->count("sectornum IN($sectors)");
+  my $num_svc_acct =      FS::svc_acct->count("sectornum IN($sectors)");
+
+  {
+    'data' => $num_svc_broadband ? 
+                '<B>'.$num_svc_broadband.'</B>'.
+                ' broadband'
+                : '',
+    'link' => $p.'search/svc_broadband.cgi?'.$query_string,
+    'size' => '-1',
+    'align'=> 'right',
+    'colspan'=> 2,
+  },
+
+  {
+    'data' => $num_svc_acct ? 
+                '<B>'.$num_svc_acct.'</B>'.
+                ' login'
+                : '',
+    'link' => $p.'search/svc_acct.cgi?magic=advanced;'.$query_string,
+    'size' => '-1',
+    'align'=> 'right',
+    'colspan'=> 2,
+  },
+};
+
+my $tower_sub = sub {
+  my $tower = shift;
+  my $sectors = join(',', map { $_->sectornum } $tower->tower_sector);
+  [ #rows
+    [
+      { 'data' => $tower->towername, },
+      { 'data' => ' (edit) ', size => '-1', 
+        'link' => $p.'edit/tower.html?' . $tower->towernum },
+    ],
+    # turn these into rows
+    map { [ $_ ] } 
+      &{$num_svc_links}( 'towernum='.$tower->towernum, $sectors )
+  ] #rows
+};
 
 my $sector_sub = sub {
   my $tower = shift;
   
-  [ map {
-
-          [
-            { 'data' => $_->sectorname,
-              'link' => ( $_->ip_addr ? 'http://'. $_->ip_addr : '' ),
-            },
-          ],
-
-        }
-      $tower->tower_sector
-  ];
+  [ #rows
+    map {
+      my $sector = $_;
+      my $sectornum = $sector->sectornum;
+      [
+        {
+          'data' => $sector->sectorname,
+          'link' => ( $sector->ip_addr ? 'http://'. $sector->ip_addr : '' ),
+        },
+        
+        &{$num_svc_links}( 'sectornum='.$sectornum, $sectornum )
+      ]
+    } $tower->tower_sector
+  ] #rows
 
 };
 
diff --git a/httemplate/elements/select-tower_sector.html b/httemplate/elements/select-tower_sector.html
index a64d886..1973ef9 100644
--- a/httemplate/elements/select-tower_sector.html
+++ b/httemplate/elements/select-tower_sector.html
@@ -1,3 +1,4 @@
+% if ( ! $opt{'multiple'} ) {
 <& /elements/select-table.html,
     table       => 'tower_sector',
     name_col    => 'description',
@@ -5,3 +6,45 @@
     empty_label => ' ',
     @_ 
 &>
+% }
+% else { # currently only multiple sectors, not towers
+<& /elements/select-table.html,
+    table       => 'tower',
+    name_col    => 'towername',
+    id          => 'towernum',
+    field       => 'dummy_towernum'
+    onchange    => 'change_towernum(this.value);',
+    element_etc => 'STYLE="vertical-align:top"',
+&>
+<SELECT NAME="sectornum" ID="sectornum" MULTIPLE SIZE="6"></SELECT>
+<SCRIPT TYPE="text/javascript">
+var sectors_of = <% encode_json(\%sectors_of) %>;
+var select_sectornum = document.getElementById('sectornum');
+function change_towernum(towernum) {
+  select_sectornum.options.length = 0;
+  for (var sectornum in sectors_of[towernum]) {
+    var o = document.createElement('OPTION');
+    o.value = sectornum;
+    o.text = sectors_of[towernum][sectornum];
+    o.selected = true;
+    select_sectornum.add(o, null);
+  }
+  return;
+}
+change_towernum('');
+% }
+</SCRIPT>
+<BR>
+<INPUT TYPE="checkbox" VALUE="none" NAME="sectornum" CHECKED> No sector
+<%init>
+my %opt = @_;
+
+my %sectors_of;
+if ( $opt{'multiple'} ) {
+  foreach my $sector ( qsearch('tower_sector',{}) ) {
+    $sectors_of{$sector->towernum} ||= {};
+    $sectors_of{$sector->towernum}->{$sector->sectornum} = $sector->sectorname;
+    $sectors_of{''}->{$sector->sectornum} = $sector->description;
+  }
+}
+</%init>
diff --git a/httemplate/elements/tr-selectmultiple-part_pkg.html b/httemplate/elements/tr-selectmultiple-part_pkg.html
index d959a5b..0b83da0 100644
--- a/httemplate/elements/tr-selectmultiple-part_pkg.html
+++ b/httemplate/elements/tr-selectmultiple-part_pkg.html
@@ -1,6 +1,5 @@
-<TR>
-  <TD ALIGN="right"><% $opt{'label'} || 'Packages' %></TD>
-  <TD>
+<& tr-td-label.html, label => emt('Packages'), %opt &>
+  <TD <% $style %>>
     <% include( '/elements/select-table.html',
                   'table'         => 'part_pkg',
 		  'name_col'      => 'pkg',
@@ -8,12 +7,14 @@
 		  'element_etc'   => 'multiple',
 		  %opt,
               )
-    %>
+  %>
   </TD>
 </TR>
 
 <%init>
 
 my %opt = @_;
+my $style = $opt{'cell_style'};
+$style = 'STYLE="'.$style.'"' if $style;
 
 </%init>
diff --git a/httemplate/search/report_svc_acct.html b/httemplate/search/report_svc_acct.html
index ea6cd6f..14c284f 100755
--- a/httemplate/search/report_svc_acct.html
+++ b/httemplate/search/report_svc_acct.html
@@ -72,6 +72,16 @@
     
     <& /elements/tr-selectmultiple-part_pkg.html &> 
 
+%   my $conf = new FS::Conf;
+%   if ( $conf->exists('svc_acct-tower_sector') 
+%        and FS::tower_sector->count > 0 ) {
+    <& /elements/tr-select-tower_sector.html,
+          'multiple'    => 1,
+          'label'       => 'Tower/Sector',
+    &>
+%   }
+
+
     <TR>
       <TH CLASS="background" COLSPAN=2>&nbsp;</TH>
     </TR>
diff --git a/httemplate/search/report_svc_broadband.html b/httemplate/search/report_svc_broadband.html
index 8571ef1..ee4dfce 100755
--- a/httemplate/search/report_svc_broadband.html
+++ b/httemplate/search/report_svc_broadband.html
@@ -26,13 +26,20 @@
                     'multiple'      => 'multiple',
               )
     %>
-% }
 
     <% include( '/elements/tr-selectmultiple-part_pkg.html',
                 %pkg_search,
               )
     %> 
 
+%     if ( FS::tower_sector->count > 0 ) {
+    <& /elements/tr-select-tower_sector.html,
+          'multiple'    => 1,
+          'label'       => 'Tower/Sector',
+    &>
+%     }
+
+%   }
     <TR>
       <TH CLASS="background" COLSPAN=2>&nbsp;</TH>
     </TR>
diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi
index 12d5991..be649a5 100755
--- a/httemplate/search/svc_acct.cgi
+++ b/httemplate/search/svc_acct.cgi
@@ -119,6 +119,10 @@ for (qw( domain domsvc agentnum custnum popnum svcpart cust_fields )) {
   $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
 }
 
+for (qw( towernum sectornum )) {
+  $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_);
+}
+
 my $timepermonth = '';
 
 my $orderby = 'ORDER BY svcnum';
diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi
index 403396b..605b829 100755
--- a/httemplate/search/svc_broadband.cgi
+++ b/httemplate/search/svc_broadband.cgi
@@ -8,6 +8,7 @@
               'header'      => [ '#',
                                  'Service',
                                  'Router',
+                                 @tower_header,
                                  'IP Address',
                                  FS::UI::Web::cust_header($cgi->param('cust_fields')),
                                ],
@@ -17,22 +18,26 @@
                                    my $blocknum = shift->blocknum or return '';
                                    $routerbyblock{$blocknum}->routername;
                                  },
+                                 @tower_fields,
                                  'ip_addr',
                                  \&FS::UI::Web::cust_fields,
                                ],
               'links'       => [ $link,
                                  $link,
                                  '', #$link_router,
+                                 (map '', @tower_fields),
                                  $link,
                                  ( map { $_ ne 'Cust. Status' ? $link_cust : '' }
                                        FS::UI::Web::cust_header($cgi->param('cust_fields'))
                                  ),
                                ],
-              'align'       => 'rllr'. FS::UI::Web::cust_aligns(),
+              'align'       => 'rll'.('r' x @tower_fields).'r'.
+                                FS::UI::Web::cust_aligns(),
               'color'       => [ 
                                  '',
                                  '',
                                  '',
+                                 (map '', @tower_fields),
                                  '',
                                  FS::UI::Web::cust_colors(),
                                ],
@@ -40,6 +45,7 @@
                                  '',
                                  '',
                                  '',
+                                 (map '', @tower_fields),
                                  '',
                                  FS::UI::Web::cust_styles(),
                                ],
@@ -60,7 +66,7 @@ else {
   foreach (qw(custnum agentnum svcpart)) {
     $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
   }
-  foreach (qw(pkgpart routernum)) {
+  foreach (qw(pkgpart routernum towernum sectornum)) {
     $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_);
   }
 }
@@ -71,6 +77,14 @@ if ( $cgi->param('sortby') =~ /^(\w+)$/ ) {
 
 my $sql_query = FS::svc_broadband->search(\%search_hash);
 
+my @tower_header;
+my @tower_fields;
+if ( FS::tower_sector->count > 0 ) {
+  push @tower_header, 'Tower/Sector';
+  push @tower_fields, sub { $_[0]->tower_sector ? 
+                            $_[0]->tower_sector->description : '' };
+}
+
 my %routerbyblock = ();
 foreach my $router (qsearch('router', {})) {
   foreach ($router->addr_block) {