Changeset 527


Ignore:
Timestamp:
10/23/12 13:32:47 (12 years ago)
Author:
Kris Deugau
Message:

/trunk

Clean up and move SQL for showRBlock to IPDB.pm
Tweak listFree() since the segments in showMaster and showRBlock it
replaced weren't quite as identical as I thought
Convert template to use odd and squeeze a bit more HTML out of
main.cgi and IPDB.pm

See #34.

Location:
trunk
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/cgi-bin/IPDB.pm

    r525 r527  
    2828        &addMaster
    2929        &listSummary &listMaster &listRBlock &listFree
     30        &getRoutedCity
    3031        &allocateBlock &deleteBlock &getBlockData
    3132        &getNodeList
    … …  
    4041                &addMaster
    4142                &listSummary &listMaster &listRBlock &listFree
     43                &getRoutedCity
    4244                &allocateBlock &deleteBlock &getBlockData
    4345                &getNodeList
    … …  
    373375
    374376
    375 # &listRBlock
    376 
    377 
    378 ## IPDB::listFree()
     377## IPDB::listRBlock()
    379378# Gets a list of free blocks in the requested parent/master in both CIDR and range notation
    380379# Takes a parent/master and an optional flag to look at routed or unrouted blocks, depending
    381380# on whether the master is a direct master or a routed block
    382381# Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks
     382sub listRBlock {
     383  my $dbh = shift;
     384  my $routed = shift;
     385
     386  # Snag the allocations for this block
     387  my $sth = $dbh->prepare("SELECT cidr,city,type,custid,swip,description".
     388        " FROM allocations WHERE cidr <<= ? ORDER BY cidr");
     389  $sth->execute($routed);
     390
     391  # hack hack hack
     392  # set up to flag swip=y records if they don't actually have supporting data in the customers table
     393  my $custsth = $dbh->prepare("SELECT count(*) FROM customers WHERE custid = ?");
     394
     395  my @blocklist;
     396  while (my ($cidr,$city,$type,$custid,$swip,$desc) = $sth->fetchrow_array()) {
     397    $custsth->execute($custid);
     398    my ($ncust) = $custsth->fetchrow_array();
     399    my %row = (
     400        block => $cidr,
     401        city => $city,
     402        type => $disp_alloctypes{$type},
     403        custid => $custid,
     404        swip => ($swip eq 'y' ? 'Yes' : 'No'),
     405        partswip => ($swip eq 'y' && $ncust == 0 ? 1 : 0),
     406        desc => $desc
     407        );
     408    $row{subblock} = ($type =~ /^.r$/);         # hmf.  wonder why these won't work in the hash declaration...
     409    $row{listpool} = ($type =~ /^.[pd]$/);
     410    push (@blocklist, \%row);
     411  }
     412  return \@blocklist;
     413} # end listRBlock()
     414
     415
     416## IPDB::listFree()
     417# Gets a list of free blocks in the requested parent/master in both CIDR and range notation
     418# Takes a parent/master and an optional "routed or unrouted" flag that defaults to unrouted.
     419# Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks
     420# Returns some extra flags in the hashrefs for routed blocks, since those can have several subtypes
    383421sub listFree {
    384422  my $dbh = shift;
    385423  my $master = shift;
    386   my $routed = shift || 'y';
     424  my $routed = shift || 'n';
    387425
    388426  # do it this way so we can waste a little less time iterating
    389   my $sth = $dbh->prepare("SELECT cidr FROM freeblocks WHERE cidr <<= ? AND routed = ? ORDER BY cidr");
    390   $sth->execute($master, $routed);
     427  my $sth = $dbh->prepare("SELECT cidr,routed FROM freeblocks WHERE cidr <<= ? AND ".
     428        ($routed eq 'n' ? '' : 'NOT')." routed = 'n' ORDER BY cidr");
     429  $sth->execute($master);
    391430  my @flist;
    392   while (my ($cidr) = $sth->fetchrow_array()) {
     431  while (my ($cidr,$rtype) = $sth->fetchrow_array()) {
    393432    $cidr = new NetAddr::IP $cidr;
    394     my %row = (fblock => "$cidr", frange => $cidr->range);
     433    my %row = (
     434        fblock => "$cidr",
     435        frange => $cidr->range,
     436        );
     437    if ($routed eq 'y') {
     438      $row{subblock} = ($rtype ne 'y' && $rtype ne 'n');
     439      $row{fbtype} = $rtype;
     440    }
    395441    push @flist, \%row;
    396442  }
    397443  return \@flist;
    398 }
     444} # end listFree()
     445
     446
     447## IPDB::getRoutedCity()
     448# Get the city for a routed block.
     449sub getRoutedCity {
     450  my $dbh = shift;
     451  my $block = shift;
     452
     453  my ($rcity) = $dbh->selectrow_array("SELECT city FROM routed WHERE cidr = ?", undef, ($block) );
     454  return $rcity;
     455} # end getRoutedCity()
    399456
    400457

  • trunk/cgi-bin/main.cgi

    r524 r527  
    238238  $page->param(routedlist => $rlist);
    239239
    240   my $flist = listFree($ip_dbh, $webvar{block}, 'n');
     240  my $flist = listFree($ip_dbh, $webvar{block});
    241241  $page->param(unrouted => $flist);
    242242} # showMaster
    … …  
    252252sub showRBlock {
    253253
    254   my $master = new NetAddr::IP $webvar{block};
    255 
    256   $sth = $ip_dbh->prepare("select city from routed where cidr='$master'");
    257   $sth->execute;
    258   my ($rcity) = $sth->fetchrow_array;
    259 
    260   $page->param(master => "$master");
     254  $page->param(master => $webvar{block});
     255  $page->param(delrouted => $IPDBacl{$authuser} =~ /d/);
     256
     257  my $rcity = getRoutedCity($ip_dbh, $webvar{block});
    261258  $page->param(rcity => $rcity);
    262259
    263   # Snag the allocations for this block
    264   $sth = $ip_dbh->prepare("select cidr,city,type,custid,swip,description".
    265         " from allocations where cidr <<= '$master' order by cidr");
    266   $sth->execute();
    267 
    268   # hack hack hack
    269   # set up to flag swip=y records if they don't actually have supporting data in the customers table
    270   my $custsth = $ip_dbh->prepare("select count(*) from customers where custid=?");
    271 
    272   my $rowclass = 0;
    273   my @blocklist;
    274   while (my ($cidr,$city,$type,$custid,$swip,$desc) = $sth->fetchrow_array()) {
    275     $custsth->execute($custid);
    276     my ($ncust) = $custsth->fetchrow_array();
    277 
    278     my %row = (
    279         rowclass => $rowclass++ % 2,
    280         block => $cidr,
    281         city => $city,
    282         type => $disp_alloctypes{$type},
    283         custid => $custid,
    284         swip => ($swip eq 'y' ? ($ncust == 0 ? 'Yes<small>*</small>' : 'Yes') : 'No'),
    285         desc => $desc
    286         );
    287     $row{subblock} = ($type =~ /^.r$/);         # hmf.  wonder why these won't work in the hash declaration...
    288     $row{listpool} = ($type =~ /^.[pd]$/);
    289     push (@blocklist, \%row);
    290   }
    291   $page->param(blocklist => \@blocklist);
    292 
    293   $page->param(delrouted => $IPDBacl{$authuser} =~ /d/);
    294 
    295   # Snag the free blocks.  We don't really *need* to be pedantic about avoiding
    296   # unrouted free blocks, but it's better to let the database do the work if we can.
    297   $rowclass = 0;
    298   my @unassigned;
    299   $sth = $ip_dbh->prepare("select cidr,routed from freeblocks where cidr <<= '$master'".
    300         " order by cidr");
    301   $sth->execute();
    302   while (my ($cidr_db,$routed) = $sth->fetchrow_array()) {
    303     my $cidr = new NetAddr::IP $cidr_db;
    304 
    305     my %row = (
    306         rowclass => $rowclass++ % 2,
    307         subblock => ($routed ne 'y' && $routed ne 'n'),
    308         fblock => $cidr_db,
    309         fbtype => $routed,
    310         frange => $cidr->range,
    311         );
    312     push @unassigned, \%row;
    313   }
    314   $page->param(unassigned => \@unassigned);
    315 
     260  my $blist = listRBlock($ip_dbh, $webvar{block});
     261  $page->param(blocklist => $blist);
     262
     263  my $flist = listFree($ip_dbh, $webvar{block}, 'y');
     264  $page->param(unassigned => $flist);
    316265} # showRBlock
    317266

  • trunk/templates/showrouted.tmpl

    r517 r527  
    1515
    1616<TMPL_LOOP NAME=blocklist>
    17 <tr class="row<TMPL_VAR NAME=rowclass>">
     17<tr class="row<TMPL_IF __odd__>0<TMPL_ELSE>1</TMPL_IF>">
    1818<td>
    1919<TMPL_IF subblock>Sub </TMPL_IF><a href="<TMPL_VAR NAME=webpath>/cgi-bin/main.cgi?action=edit&amp;block=<TMPL_VAR NAME=block>"><TMPL_VAR NAME=block></a>
    … …  
    2323<td><TMPL_VAR NAME=type></td>
    2424<td><TMPL_VAR NAME=custid></td>
    25 <td><TMPL_VAR NAME=swip></td>
     25<td><TMPL_VAR NAME=swip><TMPL_IF partswip><small>*</small></TMPL_IF></td>
    2626<td><TMPL_VAR NAME=desc></td>
    2727</tr>
    … …  
    5858
    5959<TMPL_LOOP name=unassigned>
    60 <tr class="row<TMPL_VAR NAME=rowclass>">
     60<tr class="row<TMPL_IF __odd__>0<TMPL_ELSE>1</TMPL_IF>">
    6161<td><TMPL_IF subblock>Sub </TMPL_IF><a href="<TMPL_VAR NAME=webpath>/cgi-bin/main.cgi?action=assign&amp;block=<TMPL_VAR NAME=fblock>&amp;fbtype=<TMPL_VAR NAME=fbtype>"><TMPL_VAR NAME=fblock></a></td>
    6262<td><TMPL_VAR NAME=frange></td>
Note: See TracChangeset for help on using the changeset viewer.