Changeset 532

Timestamp:

10/30/12 16:01:39 (11 years ago)

Author:

Kris Deugau

Message:

/trunk

Clean up and move SQL to IPDB.pm for pool-selector for static IPs
on allocation confirmation page. See #34.

Location:

trunk/cgi-bin

Files:

• IPDB.pm (modified) (3 diffs)
• main.cgi (modified) (1 diff)

trunk/cgi-bin/IPDB.pm

@@ -28 +28 @@
         &addMaster
         &listSummary &listMaster &listRBlock &listFree &listPool
-        &getTypeList
+        &getTypeList &getPoolSelect
         &getParent &getRoutedCity
         &allocateBlock &updateBlock &deleteBlock &getBlockData
@@ -42 +42 @@
                 &addMaster
                 &listSummary &listMaster &listRBlock &listFree &listPool
-                &getTypeList
+                &getTypeList &getPoolSelect
                 &getParent &getRoutedCity
                 &allocateBlock &updateBlock &deleteBlock &getBlockData
@@ -497 +497 @@
   return $tlist;
 }
+
+
+## IPDB::getPoolSelect()
+# Get a list of pools matching the passed city and type that have 1 or more free IPs
+sub getPoolSelect {
+  my $dbh = shift;
+  my $iptype = shift;
+  my $pcity = shift;
+
+  my ($ptype) = ($iptype =~ /^(.)i$/);
+  return if !$ptype;
+  $ptype .= '_';
+
+  my $plist = $dbh->selectall_arrayref(
+        "SELECT (SELECT city FROM allocations WHERE cidr=poolips.pool) AS poolcit, ".
+        "poolips.pool AS poolblock, COUNT(*) AS poolfree FROM poolips,allocations ".
+        "WHERE poolips.available='y' AND poolips.pool=allocations.cidr ".
+        "AND allocations.city = ? AND poolips.type LIKE ? ".
+        "GROUP BY pool", { Slice => {} }, ($pcity, $ptype) );
+  return $plist;
+} # end getPoolSelect()
 
 

trunk/cgi-bin/main.cgi

@@ -397 +397 @@
 
   if ($webvar{alloctype} =~ /^.i$/) {
-    my ($base,undef) = split //, $webvar{alloctype};    # split into individual chars
-
-# Ewww.  But it works.
-    $sth = $ip_dbh->prepare("SELECT (SELECT city FROM allocations WHERE cidr=poolips.pool), ".
-        "poolips.pool, COUNT(*) FROM poolips,allocations WHERE poolips.available='y' AND ".
-        "poolips.pool=allocations.cidr AND allocations.city='$webvar{pop}' AND poolips.type LIKE '".$base."_' ".
-        "GROUP BY pool");
-    $sth->execute;
-    my $optionlist;
-
-    my @poollist;
-    while (my ($poolcit,$poolblock,$poolfree) = $sth->fetchrow_array) {
-      # city,pool cidr,free IP count
-      if ($poolfree > 0) {
-        my %row = (poolcit => $poolcit, poolblock => $poolblock, poolfree => $poolfree);
-        push (@poollist, \%row);
-      }
-    }
+    my $plist = getPoolSelect($ip_dbh, $webvar{alloctype}, $webvar{pop});
     $page->param(staticip => 1);
-    $page->param(poollist => \@poollist);
+    $page->param(poollist => $plist) if $plist;
     $cidr = "Single static IP";
 ##fixme:  need to handle "no available pools"