Changeset 663

Timestamp:

12/30/14 16:09:20 (9 years ago)

Author:

Kris Deugau

Message:

/trunk

Clean up some minor snafu-ness in listSubs()
Commit new subs listContainers() and listAllocations() for alternate

browse listings

Bring getPoolSelect() up to date with current database structure

File:

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

trunk/cgi-bin/IPDB.pm

@@ -29 +29 @@
         &initIPDBGlobals &connectDB &finish &checkDBSanity
         &addMaster &touchMaster
-        &listSummary &listSubs &listFree &listPool
+        &listSummary &listSubs &listContainers &listAllocations &listFree &listPool
         &getMasterList &getTypeList &getPoolSelect &findAllocateFrom
         &ipParent &subParent &blockParent &getRoutedCity
@@ -44 +44 @@
                 &initIPDBGlobals &connectDB &finish &checkDBSanity
                 &addMaster &touchMaster
-                &listSummary &listSubs &listFree &listPool
+                &listSummary &listSubs &listContainers &listAllocations &listFree &listPool
                 &getMasterList &getTypeList &getPoolSelect &findAllocateFrom
                 &ipParent &subParent &blockParent &getRoutedCity
@@ -506 +506 @@
 
   # snag some more details
-  my $substh = $dbh->prepare("SELECT count(*) FROM allocations WHERE cidr <<= ? AND type='rm' AND master_id = ? AND NOT cidr = ? ");
+  my $substh = $dbh->prepare("SELECT count(*) FROM allocations WHERE cidr <<= ? AND ".
+        "AND type ~ '[mc]\$' AND master_id = ? AND NOT cidr = ? ");
   my $alsth = $dbh->prepare("SELECT count(*) FROM allocations WHERE cidr <<= ? ".
         "AND NOT type='rm' AND NOT type='mm' AND master_id = ?");
@@ -550 +551 @@
 
 
+## IPDB::listContainers()
+# List all container-type allocations in a given parent
+# Takes a database handle and a hash:
+#  - parent is the ID of the parent block
+# Returns an arrayref to a list of hashrefs with the CIDR block, location, type,
+# description, block ID, and counts for the nmber uf suballocations (all types),
+# free blocks, and the CIDR size of the largest free block
+sub listContainers {
+  my $dbh = shift;
+  my %args = @_;
+
+  # Just In Case
+  $args{vrf} = '' if !$args{vrf};
+
+  # Snag the allocations for this block
+  my $sth = $dbh->prepare("SELECT cidr,city,type,custid,swip,description,id,master_id".
+        " FROM allocations WHERE parent_id = ? AND type ~ '[mc]\$' ORDER BY cidr");
+  $sth->execute($args{parent});
+
+  my $alsth = $dbh->prepare("SELECT count(*) FROM allocations WHERE cidr <<= ? ".
+        "AND NOT type='rm' AND NOT type='mm' AND master_id = ?");
+  my $freesth = $dbh->prepare("SELECT count(*) FROM freeblocks WHERE cidr <<= ? AND master_id = ?");
+  my $lfreesth = $dbh->prepare("SELECT masklen(cidr) AS maskbits FROM freeblocks WHERE cidr <<= ?".
+        " AND master_id = ? ORDER BY masklen(cidr) LIMIT 1");
+
+  my @blocklist;
+  while (my ($cidr,$city,$type,$custid,$swip,$desc,$id,$mid) = $sth->fetchrow_array()) {
+    $alsth->execute($cidr, $mid);
+    my ($alloc) = $alsth->fetchrow_array();
+    $freesth->execute($cidr, $mid);
+    my ($free) = $freesth->fetchrow_array();
+    $lfreesth->execute($cidr, $mid);
+    my ($lfree) = $lfreesth->fetchrow_array();
+    $lfree = "/$lfree" if $lfree;
+    $lfree = '<NONE>' if !$lfree;
+    my %row = (
+        block => $cidr,
+        suballocs => $alloc,
+        subfree => $free,
+        lfree => $lfree,
+        city => $city,
+        type => $disp_alloctypes{$type},
+        desc => $desc,
+        id => $id,
+        );
+    push (@blocklist, \%row);
+  }
+  return \@blocklist;
+} # end listContainers()
+
+
+## IPDB::listAllocations()
+# List all end-use allocations in a given parent
+# Takes a database handle and a hash:
+#  - parent is the ID of the parent block
+# Returns an arrayref to a list of hashrefs with the CIDR block, location, type,
+# custID, SWIP flag, description, block ID, and master ID
+sub listAllocations {
+  my $dbh = shift;
+  my %args = @_;
+
+  # Snag the allocations for this block
+  my $sth = $dbh->prepare("SELECT cidr,city,type,custid,swip,description,id,master_id".
+        " FROM allocations WHERE parent_id = ? AND type !~ '[mc]\$' ORDER BY cidr");
+  $sth->execute($args{parent});
+
+  # 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,$id,$mid) = $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,
+        id => $id,
+        );
+#    $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 listAllocations()
+
+
 ## IPDB::listFree()
 # Gets a list of free blocks in the requested parent/master and VRF instance in both CIDR and range notation
@@ -670 +763 @@
   $ptype .= '_';
 
-  my $plist = $dbh->selectall_arrayref(
-        "SELECT count(*) AS poolfree,p.pool AS poolblock, a.city AS poolcit, a.rdepth AS poolrdepth ".
-        "FROM poolips p ".
-        "JOIN allocations a ON p.pool=a.cidr ".
-        "WHERE p.available='y' AND a.city = ? AND p.type LIKE ? ".
-        "GROUP BY p.pool,a.city,a.rdepth",
+  my $plist = $dbh->selectall_arrayref( q(
+        SELECT count(*) AS poolfree,p.pool AS poolblock, a.city AS poolcit
+        FROM poolips p
+        JOIN allocations a ON p.parent_id=a.id
+        WHERE p.available='y' AND a.city = ? AND p.type LIKE ?
+        GROUP BY p.pool,a.city
+        ),
         { Slice => {} }, ($pcity, $ptype) );
   return $plist;