From: mark Date: Wed, 18 Jan 2012 02:48:58 +0000 (+0000) Subject: search services by tower/sector, #15950 X-Git-Tag: freeside_2_3_1~12 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=6dda95db961112c4a422ebe282b6dfc7935be40e search services by tower/sector, #15950 --- diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index a9d15cb25..d6c964278 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -3040,6 +3040,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 8caef6dc8..0b5588466 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 50553c5c7..201e8818e 100644 --- a/FS/FS/svc_acct.pm +++ b/FS/FS/svc_acct.pm @@ -1462,7 +1462,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); } @@ -2846,6 +2846,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}) { @@ -2873,6 +2876,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 a85fc5cb0..18514af19 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; @@ -158,6 +158,10 @@ Parameters: =item routernum - arrayref +=item sectornum - arrayref + +=item towernum - arrayref + =item order_by =back @@ -214,6 +218,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 660e92469..0de3b324c 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 ? + ''.$num_svc_broadband.''. + ' broadband' + : '', + 'link' => $p.'search/svc_broadband.cgi?'.$query_string, + 'size' => '-1', + 'align'=> 'right', + 'colspan'=> 2, + }, + + { + 'data' => $num_svc_acct ? + ''.$num_svc_acct.''. + ' 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 a64d88608..a9cdbb6f3 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"', +&> + + +
+ 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; + } +} + diff --git a/httemplate/elements/tr-selectmultiple-part_pkg.html b/httemplate/elements/tr-selectmultiple-part_pkg.html index d959a5bae..0b83da02c 100644 --- a/httemplate/elements/tr-selectmultiple-part_pkg.html +++ b/httemplate/elements/tr-selectmultiple-part_pkg.html @@ -1,6 +1,5 @@ - - <% $opt{'label'} || 'Packages' %> - +<& tr-td-label.html, label => emt('Packages'), %opt &> + > <% include( '/elements/select-table.html', 'table' => 'part_pkg', 'name_col' => 'pkg', @@ -8,12 +7,14 @@ 'element_etc' => 'multiple', %opt, ) - %> + %> <%init> my %opt = @_; +my $style = $opt{'cell_style'}; +$style = 'STYLE="'.$style.'"' if $style; diff --git a/httemplate/search/report_svc_acct.html b/httemplate/search/report_svc_acct.html index ea6cd6fc0..14c284fb7 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', + &> +% } + +   diff --git a/httemplate/search/report_svc_broadband.html b/httemplate/search/report_svc_broadband.html index 8571ef184..ee4dfce90 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', + &> +% } + +% }   diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 12d5991ea..be649a5f8 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 403396b99..605b829b4 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) {