Changeset 644 for trunk/cgi-bin/admin.cgi

Timestamp:

10/17/14 17:00:26 (10 years ago)

Author:

Kris Deugau

Message:

/trunk

admin.cgi cleanup; update SQL and related coding conventions for IP pool
section

File:

• trunk/cgi-bin/admin.cgi (modified) (2 diffs)

trunk/cgi-bin/admin.cgi

@@ -302 +302 @@
 } elsif ($webvar{action} eq 'showpools') {
 
-  $sth = $ip_dbh->prepare("select pool, count(*) from poolips where available='y' group by pool order by pool");
-  $sth->execute;
-  my @poollist;
-  while (my ($pool,$free) = $sth->fetchrow_array) {
-    my %row = (
-        pool => $pool,
-        free => $free
-        );
-    push @poollist, \%row;
-  }
-  $page->param(poollist => \@poollist);
+  my $plist = $ip_dbh->selectall_arrayref("SELECT a.id,a.cidr AS pool, count(*) AS free FROM poolips p ".
+        "JOIN allocations a ON a.id=p.parent_id ".
+        "WHERE available='y' GROUP BY a.cidr,a.id ORDER BY a.cidr", { Slice => {} });
+  $page->param(poollist => $plist);
 
 } elsif ($webvar{action} eq 'tweakpool') {
@@ -320 +313 @@
 } elsif ($webvar{action} eq 'updatepool') {
 
-  $sth = $ip_dbh->prepare("update poolips set custid='$webvar{custid}', ".
-        "city=?, type='$webvar{type}', available='".
+  my $ip = $ip_dbh->selectrow_array("SELECT ip FROM poolips WHERE id=?", undef, ($webvar{id}) );
+  $page->param(ip => $ip);
+  $sth = $ip_dbh->prepare("UPDATE poolips SET custid=?, city=?, type=?, available='".
         (($webvar{available} eq 'y') ? 'y' : 'n').
         "', notes=?, description=? ".
-        "where ip='$webvar{ip}'");
-  $sth->execute($webvar{city},$webvar{notes},$webvar{desc});
-  $page->param(ip => $webvar{ip});
+        "WHERE id=?");
+  $sth->execute($webvar{custid}, $webvar{city}, $webvar{type}, $webvar{notes}, $webvar{desc}, $webvar{id});
   if ($sth->err) {
     $page->param(errmsg => $sth->errstr);
-    syslog "err", "$authuser could not update pool IP $webvar{ip}: ".$sth->errstr;
+    syslog "err", "$authuser could not update pool IP $ip: ".$sth->errstr;
   } else {
-    syslog "notice", "$authuser updated pool IP $webvar{ip}";
-  }
-  $sth = $ip_dbh->prepare("select pool from poolips where ip='$webvar{ip}'");
-  $sth->execute;
-  my @data = $sth->fetchrow_array;
-  $page->param(pool => $data[0]);
+    syslog "notice", "$authuser updated pool IP $ip";
+  }
+  my $poolid = $ip_dbh->selectrow_array("SELECT parent_id FROM poolips WHERE id=?", undef, ($webvar{id}) );
+  $page->param(poolid => $poolid);
+  my $pool = $ip_dbh->selectrow_array("SELECT cidr FROM allocations WHERE id=?", undef, ($poolid) );
+  $page->param(pool => $pool);
 
 } elsif ($webvar{action} eq 'showusers') {