Changeset 157 for trunk/cgi-bin/main.cgi


Ignore:
Timestamp:
02/08/05 15:56:33 (19 years ago)
Author:
Kris Deugau
Message:

/trunk

Merge changes from /branches/sql-cleanup into mainline

  • Merge was clean
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/cgi-bin/main.cgi

    r142 r157  
    5656cleanInput(\%webvar);
    5757
    58 # Stuff that gets loaded from the database
    59 #my @citylist;
    60 #my @poplist;
    61 #my %allocated; # Count for allocated blocks in a master block
    62 #my %free;      # Count for free blocks (routed and unrouted) in a master block
    63 #my %bigfree;   # Tracking largest free block in a master block
    64 #my %routed;    # Number of routed blocks in a master block
    65 
    66 # Slurp up the master block list - we need this several places
    67 # While we're at it, initialize the related hashes.
    68 #$sth = $ip_dbh->prepare("select * from masterblocks order by cidr");
    69 #$sth->execute;
    70 #for (my $i=0; my @data = $sth->fetchrow_array(); $i++) {
    71 #  $masterblocks[$i] = new NetAddr::IP $data[0];
    72 #  $allocated{"$masterblocks[$i]"} = 0;
    73 #  $free{"$masterblocks[$i]"} = 0;
    74 #  $bigfree{"$masterblocks[$i]"} = 128; # Larger number means smaller block.
    75 #                                       # Set to 128 to prepare for IPv6
    76 #  $routed{"$masterblocks[$i]"} = 0;
    77 #}
    78 
    79 
    80 
    8158
    8259#main()
    … …  
    10986# Thus the "routed" flag.
    11087
    111     $sth = $ip_dbh->prepare("insert into freeblocks values ('$webvar{cidr}',".
    112         $cidr->masklen.",'<NULL>','n')");
     88    $sth = $ip_dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
     89        " values ('$webvar{cidr}',".$cidr->masklen.",'<NULL>','n')");
    11390    $sth->execute;
    11491
    … …  
    348325    # Fix up types from pools (which are single-char)
    349326    # Fixing the database would be...  painful.  :(
     327##fixme LEGACY CODE
    350328    if ($data[2] =~ /^[cdsmw]$/) {
    351329      $data[2] .= 'i';
    … …  
    354332        $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]);
    355333    # Allow listing of pool if desired/required.
    356     if ($data[2] =~ /^[cdsmw]p$/) {
     334    if ($data[2] =~ /^.[pd]$/) {
    357335      $row[0] .= ' &nbsp; <a href="/ip/cgi-bin/main.cgi?action=listpool'.
    358336        "&pool=$data[0]\">List IPs</a>";
    … …  
    493471
    494472  # Fetch only the blocks relevant to this master
    495   $sth = $ip_dbh->prepare("select * from routed where cidr <<= '$master' order by cidr");
     473  $sth = $ip_dbh->prepare("select cidr,city from routed where cidr <<= '$master' order by cidr");
    496474  $sth->execute();
    497475
    … …  
    504482    $bigfree{"$cidr"} = 128;
    505483    # Retain the routing destination
    506     $routed{"$cidr"} = $data[2];
     484    $routed{"$cidr"} = $data[1];
    507485  }
    508486
    … …  
    596574  my $master = new NetAddr::IP $webvar{block};
    597575
    598   $sth = $ip_dbh->prepare("select * from routed where cidr='$master'");
     576  $sth = $ip_dbh->prepare("select city from routed where cidr='$master'");
    599577  $sth->execute;
    600578  my @data = $sth->fetchrow_array;
    601579
    602580  print qq(<center><div class="heading">Summarizing allocated blocks for ).
    603         qq($master ($data[2]):</div></center><br>\n);
     581        qq($master ($data[0]):</div></center><br>\n);
    604582
    605583  startTable('CIDR allocation','Customer Location','Type','CustID','Description/Name');
    606584
    607585  # Snag the allocations for this block
    608   $sth = $ip_dbh->prepare("select * from allocations where cidr <<= '$master' order by cidr");
     586  $sth = $ip_dbh->prepare("select cidr,city,type,custid,description".
     587        " from allocations where cidr <<= '$master' order by cidr");
    609588  $sth->execute();
    610589
    611590  my $count=0;
    612591  while (my @data = $sth->fetchrow_array()) {
    613     # cidr,custid,type,city,description,notes,maskbits,circuitid
     592    # cidr,city,type,custid,description, as per the SELECT
    614593    my $cidr = new NetAddr::IP $data[0];
    615594
    616595    # Clean up extra spaces that are borking things.
    617     $data[2] =~ s/\s+//g;
     596#    $data[2] =~ s/\s+//g;
    618597
    619598    my @row = ("<a href=\"/ip/cgi-bin/main.cgi?action=edit&block=$data[0]\">$data[0]</a>",
    620         $data[3], $disp_alloctypes{$data[2]}, $data[1], $data[4]);
     599        $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]);
    621600    # If the allocation is a pool, allow listing of the IPs in the pool.
    622     if ($data[2] =~ /^[cdsmw]p$/) {
     601    if ($data[2] =~ /^.[pd]$/) {
    623602      $row[0] .= ' &nbsp; <a href="/ip/cgi-bin/main.cgi?action=listpool'.
    624603        "&pool=$data[0]\">List IPs</a>";
    … …  
    653632  # unrouted free blocks, but it's better to let the database do the work if we can.
    654633  $count = 0;
    655   $sth = $ip_dbh->prepare("select * from freeblocks where routed='y' and cidr <<= '$master' order by cidr");
     634  $sth = $ip_dbh->prepare("select cidr from freeblocks where routed='y' and cidr <<= '$master' order by cidr");
    656635  $sth->execute();
    657636  while (my @data = $sth->fetchrow_array()) {
    … …  
    675654  my $cidr = new NetAddr::IP $webvar{pool};
    676655
     656  my ($pooltype,$poolcity);
     657
    677658  # Snag pool info for heading
    678   $sth = $ip_dbh->prepare("select * from allocations where cidr='$cidr'");
     659  $sth = $ip_dbh->prepare("select type,city from allocations where cidr='$cidr'");
    679660  $sth->execute;
    680   my @data = $sth->fetchrow_array;
    681   my $type = $data[2];  # We'll need this later.
     661  $sth->bind_columns(\$pooltype, \$poolcity);
     662  $sth->fetch() || carp $sth->errstr;
    682663
    683664  print qq(<center><div class="heading">Listing pool IPs for $cidr<br>\n).
    684         qq(($disp_alloctypes{$type} in $data[3])</div></center><br>\n);
    685   print qq(<div class="indent"><b>Reserved IPs:</b><br>\n);
    686   print qq(<div class="indent"><table><tr class=color1><td>Network IP:</td><td>).
     665        qq(($disp_alloctypes{$pooltype} in $poolcity)</div></center><br>\n);
     666  # Only display net/gw/bcast if it's a "real" netblock and not a PPP(oE) lunacy
     667  if ($pooltype =~ /^.d$/) {
     668    print qq(<div class="indent"><b>Reserved IPs:</b><br>\n);
     669    print qq(<div class="indent"><table><tr class=color1><td>Network IP:</td><td>).
    687670        $cidr->addr."</td></tr>\n";
    688   $cidr++;
    689   print "<tr class=color2><td>Gateway:</td><td>".$cidr->addr."</td></tr>\n";
    690   $cidr--;  $cidr--;
    691   print "<tr class=color1><td>Broadcast:</td><td>".$cidr->addr."</td></tr>\n".
     671    $cidr++;
     672    print "<tr class=color2><td>Gateway:</td><td>".$cidr->addr."</td></tr>\n";
     673    $cidr--;  $cidr--;
     674    print "<tr class=color1><td>Broadcast:</td><td>".$cidr->addr."</td></tr>\n".
    692675        "<tr><td>Netmask:</td><td>".$cidr->mask."</td></tr>\n".
    693676        "</table></div></div>\n";
     677  }
    694678
    695679# probably have to add an "edit IP allocation" link here somewhere.
    696680
    697681  startTable('IP','Customer ID','Available?','Description','');
    698   $sth = $ip_dbh->prepare("select * from poolips where pool='$webvar{pool}' order by ip");
     682  $sth = $ip_dbh->prepare("select ip,custid,available,description,type".
     683        " from poolips where pool='$webvar{pool}' order by ip");
    699684  $sth->execute;
    700685  my $count = 0;
    701686  while (my @data = $sth->fetchrow_array) {
    702687    # pool,ip,custid,city,ptype,available,notes,description,circuitid
    703     # If desc is null, make it not null.  <g>
    704     if ($data[7] eq '') {
    705       $data[7] = '&nbsp;';
     688    # ip,custid,available,description,type
     689    # If desc is "null", make it not null.  <g>
     690    if ($data[3] eq '') {
     691      $data[3] = '&nbsp;';
    706692    }
    707693    # Some nice hairy Perl to decide whether to allow unassigning each IP
    708     #   -> if $data[5] (aka poolips.available) == 'n' then we print the unassign link
     694    #   -> if $data[2] (aka poolips.available) == 'n' then we print the unassign link
    709695    #      else we print a blank space
    710     my @row = ( qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$data[1]">$data[1]</a>),
    711         $data[2],$data[5],$data[7],
    712         ( ($data[5] eq 'n') ?
    713           ("<a href=\"/ip/cgi-bin/main.cgi?action=delete&block=$data[1]&".
    714            "alloctype=$data[4]i\">Unassign this IP</a>") :
     696    my @row = ( qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$data[0]">$data[0]</a>),
     697        $data[1],$data[2],$data[3],
     698        ( ($data[2] eq 'n') ?
     699          ("<a href=\"/ip/cgi-bin/main.cgi?action=delete&block=$data[0]&".
     700           "alloctype=$data[4]\">Unassign this IP</a>") :
    715701          ("&nbsp;") )
    716702        );
    … …  
    802788#  + Different flavours of netblock
    803789
    804   if ($webvar{alloctype} =~ /^[cdsmw]i$/) {
     790  if ($webvar{alloctype} =~ /^.i$/) {
    805791    my ($base,undef) = split //, $webvar{alloctype};    # split into individual chars
    806792    my $sql;
    … …  
    809795    # ... aside from #^%#$%#@#^%^^!!!! legacy data.  GRRR.
    810796    # Note that we want to retain the requested city to relate to customer info.
     797##fixme This needs thought.
     798##SELECT DISTINCT pool, Count(*) FROM poolips where available='y' GROUP BY pool;
    811799    if ($base =~ /^[ds]$/) {
    812800      $sql = "select * from poolips where available='y' and".
    … …  
    862850##fixme
    863851# This section needs serious Pondering.
    864         if ($webvar{alloctype} =~ /^[cdsmw]p$/) {
     852        if ($webvar{alloctype} =~ /^.[pd]$/) {
    865853          if (($webvar{city} !~ /^(Sudbury|North Bay)$/) && ($webvar{alloctype} eq 'dp')) {
    866854            printError("You must chose Sudbury or North Bay for DSL pools.");
    … …  
    878866        }
    879867        if ($webvar{allocfrom} ne '-') {
    880           $sql = "select * from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
     868          $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
    881869                " and cidr <<= '$webvar{allocfrom}' and routed='y' order by cidr,maskbits desc";
    882870        } else {
    883           $sql = "select * from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
     871          $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
    884872                " and routed='y' order by cidr,maskbits desc";
    885873        }
    … …  
    907895      $cidr = $subblocks[0];
    908896    }
    909   } # if ($webvar{alloctype} =~ /^[cdsmw]i$/)
     897  } # if ($webvar{alloctype} =~ /^.i$/)
    910898
    911899  open HTML, "../confirm.html"
    … …  
    973961        "'$webvar{alloctype}' by $authuser failed: '$msg'";
    974962    printError("Allocation of $webvar{fullcidr} as $disp_alloctypes{$webvar{alloctype}}".
    975         " failed: $msg\n");
     963        " failed:<br>\n$msg\n");
    976964  }
    977965
    … …  
    10651053  $data[2] =~ s/\s//;
    10661054
     1055##fixme LEGACY CODE
    10671056  # Postfix "i" on pool IP types
    10681057  if ($data[2] =~ /^[cdsmw]$/) {
    … …  
    11271116    # Relatively simple SQL transaction here.
    11281117    my $sql;
    1129     if (my $pooltype = ($webvar{alloctype} =~ /^([cdsmw])i$/) ) {
     1118    if (my $pooltype = ($webvar{alloctype} =~ /^(.)i$/) ) {
    11301119      # Note the hack ( available='n' ) to work around "update" additions
    11311120      # to static IP space.  Eww.
    … …  
    11381127        "type='$webvar{alloctype}',circuitid='$webvar{circid}' where cidr='$webvar{block}'";
    11391128    }
    1140 syslog "debug", $sql;
     1129    # Log the details of the change.
     1130    syslog "debug", $sql;
    11411131    $sth = $ip_dbh->prepare($sql);
    11421132    $sth->execute;
    … …  
    12161206    $desc = "N/A";
    12171207    $notes = "N/A";
    1218   } elsif ($webvar{alloctype} =~ /^[cdsmw]i$/) { # done with alloctype=rr
     1208  } elsif ($webvar{alloctype} =~ /^.i$/) { # done with alloctype=rr
    12191209
    12201210    # Unassigning a static IP
    … …  
    12291219    $alloctype .="i";
    12301220
    1231   } else { # done with alloctype=[cdsmw]i
     1221  } else { # done with alloctype=~ /^.i$/
    12321222
    12331223    my $sth = $ip_dbh->prepare("select cidr,custid,type,city,circuitid,description,notes from ".
    … …  
    12541244
    12551245  # Set the warning text.
    1256   if ($alloctype =~ /^[cdsmw]p$/) {
     1246  if ($alloctype =~ /^.[pd]$/) {
    12571247    $html =~ s|<!--warn-->|<tr bgcolor="black"><td colspan="2"><div class="red">Warning: clicking confirm will remove this record entirely.<br>Any IPs allocated from this pool will also be removed!</div></td></tr>|;
    12581248  } else {
Note: See TracChangeset for help on using the changeset viewer.