Changeset 527 for trunk/cgi-bin/IPDB.pm

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.

File:

• trunk/cgi-bin/IPDB.pm (modified) (3 diffs)

trunk/cgi-bin/IPDB.pm

@@ -28 +28 @@
         &addMaster
         &listSummary &listMaster &listRBlock &listFree
+        &getRoutedCity
         &allocateBlock &deleteBlock &getBlockData
         &getNodeList
@@ -40 +41 @@
                 &addMaster
                 &listSummary &listMaster &listRBlock &listFree
+                &getRoutedCity
                 &allocateBlock &deleteBlock &getBlockData
                 &getNodeList
@@ -373 +375 @@
 
 
-# &listRBlock
-
-
-## IPDB::listFree()
+## IPDB::listRBlock()
 # Gets a list of free blocks in the requested parent/master in both CIDR and range notation
 # Takes a parent/master and an optional flag to look at routed or unrouted blocks, depending
 # on whether the master is a direct master or a routed block
 # Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks
+sub listRBlock {
+  my $dbh = shift;
+  my $routed = shift;
+
+  # Snag the allocations for this block
+  my $sth = $dbh->prepare("SELECT cidr,city,type,custid,swip,description".
+        " FROM allocations WHERE cidr <<= ? ORDER BY cidr");
+  $sth->execute($routed);
+
+  # hack hack hack
+  # set up to flag swip=y records if they don't actually have supporting data in the customers table
+  my $custsth = $dbh->prepare("SELECT count(*) FROM customers WHERE custid = ?");
+
+  my @blocklist;
+  while (my ($cidr,$city,$type,$custid,$swip,$desc) = $sth->fetchrow_array()) {
+    $custsth->execute($custid);
+    my ($ncust) = $custsth->fetchrow_array();
+    my %row = (
+        block => $cidr,
+        city => $city,
+        type => $disp_alloctypes{$type},
+        custid => $custid,
+        swip => ($swip eq 'y' ? 'Yes' : 'No'),
+        partswip => ($swip eq 'y' && $ncust == 0 ? 1 : 0),
+        desc => $desc
+        );
+    $row{subblock} = ($type =~ /^.r$/);         # hmf.  wonder why these won't work in the hash declaration...
+    $row{listpool} = ($type =~ /^.[pd]$/);
+    push (@blocklist, \%row);
+  }
+  return \@blocklist;
+} # end listRBlock()
+
+
+## IPDB::listFree()
+# Gets a list of free blocks in the requested parent/master in both CIDR and range notation
+# Takes a parent/master and an optional "routed or unrouted" flag that defaults to unrouted.
+# Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks
+# Returns some extra flags in the hashrefs for routed blocks, since those can have several subtypes
 sub listFree {
   my $dbh = shift;
   my $master = shift;
-  my $routed = shift || 'y';
+  my $routed = shift || 'n';
 
   # do it this way so we can waste a little less time iterating
-  my $sth = $dbh->prepare("SELECT cidr FROM freeblocks WHERE cidr <<= ? AND routed = ? ORDER BY cidr");
-  $sth->execute($master, $routed);
+  my $sth = $dbh->prepare("SELECT cidr,routed FROM freeblocks WHERE cidr <<= ? AND ".
+        ($routed eq 'n' ? '' : 'NOT')." routed = 'n' ORDER BY cidr");
+  $sth->execute($master);
   my @flist;
-  while (my ($cidr) = $sth->fetchrow_array()) {
+  while (my ($cidr,$rtype) = $sth->fetchrow_array()) {
     $cidr = new NetAddr::IP $cidr;
-    my %row = (fblock => "$cidr", frange => $cidr->range);
+    my %row = (
+        fblock => "$cidr",
+        frange => $cidr->range,
+        );
+    if ($routed eq 'y') {
+      $row{subblock} = ($rtype ne 'y' && $rtype ne 'n');
+      $row{fbtype} = $rtype;
+    }
     push @flist, \%row;
   }
   return \@flist;
-}
+} # end listFree()
+
+
+## IPDB::getRoutedCity()
+# Get the city for a routed block.
+sub getRoutedCity {
+  my $dbh = shift;
+  my $block = shift;
+
+  my ($rcity) = $dbh->selectrow_array("SELECT city FROM routed WHERE cidr = ?", undef, ($block) );
+  return $rcity;
+} # end getRoutedCity()