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

Timestamp:

11/02/17 16:24:13 (7 years ago)

Author:

Kris Deugau

Message:

/trunk

Add IP count and percent-free to the heading and showsubs layout 2 by request

File:

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

trunk/cgi-bin/IPDB.pm

@@ -1003 +1003 @@
 
   # Snag the allocations for this block
-  my $sth = $dbh->prepare("SELECT cidr,city,type,custid,swip,description,vrf,id,master_id".
+  my $sth = $dbh->prepare("SELECT masklen(cidr),cidr,city,type,custid,swip,description,vrf,id,master_id".
         " FROM allocations WHERE parent_id = ? ORDER BY cidr");
   $sth->execute($args{parent});
@@ -1018 +1018 @@
   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");
+        " AND master_id = ? ORDER BY masklen(cidr)");
 
   my @blocklist;
-  while (my ($cidr,$city,$type,$custid,$swip,$desc,$vrf,$id,$mid) = $sth->fetchrow_array()) {
+  while (my ($blockmask,$cidr,$city,$type,$custid,$swip,$desc,$vrf,$id,$mid) = $sth->fetchrow_array()) {
     $desc .= " - vrf:$vrf" if $desc && $vrf;
     $desc = "vrf:$vrf" if !$desc && $vrf;
@@ -1033 +1033 @@
     my ($free) = $freesth->fetchrow_array();
     $lfreesth->execute($cidr, $mid);
+
+    # make sure we capture the first one separately...
     my ($lfree) = $lfreesth->fetchrow_array();
+    my $num_ipfree = ($lfree ? 2**(32-$lfree) : 0);
+    # ... so this can proceed without accidentally whacking it
+    while (my ($freemask) = $lfreesth->fetchrow_array) {
+      $num_ipfree += 2**(32-$freemask);
+    }
+    if ($type =~ /^.[dp]$/) {
+      ($num_ipfree) = $dbh->selectrow_array("SELECT count(*) FROM poolips WHERE parent_id = ? AND available = 'y'", undef, $id);
+    }
+    my $pctfree = sprintf "%0.1f", $num_ipfree / 2**(32-$blockmask) * 100;
     $lfree = "/$lfree" if $lfree;
     $lfree = '<NONE>' if !$lfree;
+
     my %row = (
         block => $cidr,
@@ -1049 +1061 @@
 #    $row{subblock} = ($type =~ /^.r$/);         # hmf.  wonder why these won't work in the hash declaration...
     $row{listpool} = ($type =~ /^.[pd]$/);
+    # only set this on suitable types (pools, containers)
+    $row{pctfree} = "$num_ipfree IPs, $pctfree" if $type =~ /^.[cpdm]/;
     push (@blocklist, \%row);
   }
@@ -3321 +3335 @@
   } else {
     my $binfo = $dbh->selectrow_hashref(qq(
-        SELECT a.id, a.cidr AS block, a.city, a.vrf, a.parent_id, a.master_id, a.swip, $commonfields,
+        SELECT a.id, masklen(a.cidr), a.cidr AS block, a.city, a.vrf, a.parent_id, a.master_id, a.swip, $commonfields,
                 f.cidr AS reserve, f.id as reserve_id,
                 d.zone >>= a.cidr AS revavail, d.zone << a.cidr AS revpartial, d.location,
@@ -3333 +3347 @@
         WHERE a.id = ?
         ), undef, ($id) );
+
+    if ($binfo->{type} =~ /^.[dp]$/) {
+      ($binfo->{nfree}) = $dbh->selectrow_array("SELECT count(*) FROM poolips WHERE parent_id = ? AND available = 'y'", undef, $id);
+    } else {
+      # assemble free IP count
+      my $tmp = $dbh->prepare("SELECT 2^(32-masklen(cidr)) AS fc FROM freeblocks WHERE parent_id = ?");
+      $tmp->execute($id);
+      my $nfree = 0;
+      while (my ($fc) = $tmp->fetchrow_array) {
+        $nfree += $fc;
+      }
+      $binfo->{nfree} = $nfree;
+    }
 
     return $binfo;