Changeset 814 for trunk

Timestamp:

03/08/16 16:51:06 (8 years ago)

Author:

Kris Deugau

Message:

/trunk

Updates to getBlockData() for several new logical data arrangements

File:

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

trunk/cgi-bin/IPDB.pm

@@ -3161 +3161 @@
 
   # Note city, vrf, parent_id and master_id removed due to JOIN uncertainty for block allocations
-  my $commonfields = q(custid, type, circuitid, description, notes, modifystamp AS lastmod,
-        privdata, vlan, rdns);
+  my $commonfields = q(a.custid, a.type, a.circuitid, a.description, a.notes, a.modifystamp AS lastmod,
+        a.privdata, a.vlan, a.rdns);
   my $bkfields = q(b.backup_id AS hasbk, b.bkbrand, b.bkmodel, b.bktype, b.bkport, b.bksrc,
         b.bkuser, b.bkvpass, b.bkepass, b.bkip);
@@ -3168 +3168 @@
   if ($type eq 'i') {
     my $binfo = $dbh->selectrow_hashref(qq(
-        SELECT p.id, p.ip AS block, p.city, p.vrf, p.parent_id, p.master_id, $commonfields,
-                d.zone >> p.ip AS revavail,
-                $bkfields
-        FROM poolips p
-        LEFT JOIN dnsavail d ON p.master_id = d.parent_alloc AND p.ip << d.zone
-        LEFT JOIN backuplist b ON p.backup_id = b.backup_id
-        WHERE id = ?
+        SELECT a.id, a.ip AS block, a.city, a.vrf, a.parent_id, a.master_id, $commonfields,
+                d.zone >> a.ip AS revavail,
+                $bkfields,
+                v.location
+        FROM poolips a
+        LEFT JOIN dnsavail d ON a.master_id = d.parent_alloc AND a.ip << d.zone
+        LEFT JOIN backuplist b ON a.backup_id = b.backup_id
+        JOIN allocations m ON a.master_id = m.id JOIN vrfs v ON m.vrf = v.vrf
+        WHERE a.id = ?
         ), undef, ($id) );
     return $binfo;
   } else {
     my $binfo = $dbh->selectrow_hashref(qq(
-        SELECT a.id, a.cidr AS block, a.city, a.vrf, a.parent_id, a.master_id, swip, $commonfields,
+        SELECT a.id, 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,
-                $bkfields
+                $bkfields,
+                v.location
         FROM allocations a
         LEFT JOIN freeblocks f ON a.id=f.reserve_for
         LEFT JOIN dnsavail d ON a.master_id = d.parent_alloc AND (a.cidr <<= d.zone OR a.cidr >> d.zone)
         LEFT JOIN backuplist b ON a.backup_id = b.backup_id
+        JOIN allocations m ON a.master_id = m.id JOIN vrfs v ON m.vrf = v.vrf
         WHERE a.id = ?
         ), undef, ($id) );