#!/usr/bin/perl
# ipdb/cgi-bin/main.cgi
# Started munging from noc.vianet's old IPDB 04/22/2004
# Current version 05/18/2004 kdeugau@vianet

#use strict;		
use warnings;	
use CGI::Carp qw/fatalsToBrowser/;
use DBI;
use CommonWeb qw/:ALL/;
use POSIX qw/ceil/;
use NetAddr::IP;

checkDBSanity();

#prototypes
sub viewBy($$);		# feed it the category and query
sub queryResults($$$);	# args is the sql, the page# and the rowCount
# Needs rewrite/rename
sub countRows($);	# returns first element of first row of passed SQL
			# Only usage passes "select count(*) ..."
# Not sure if this is needed any more.
sub parseInput($);

my $RESULTS_PER_PAGE = 50;
my %webvar = parse_post();
cleanInput(\%webvar);

# Start new code:  04/22/2004
###

# Initial display:  Show master blocks with total allocated subnets, total free subnets
# foreach block (allocations[type=cust])
#   check which master it's in
#     increment appropriate counter
# foreach block (freeblocks)
#   check which master it's in
#     increment appropriate counter

# Some things we will need to do every time.

# Why not a global DB handle?
# We already know the DB is happy, (checkDBSanity) otherwise we wouldn't be here.
$ip_dbh = DBI->connect("dbi:mysql:ipdb", "root", "");

# Slurp up the master block list - we need this several places
$sth = $ip_dbh->prepare("select * from masterblocks;");
$sth->execute;
$i=0;
for ($i=0; @data = $sth->fetchrow_array(); $i++) {
  $masterblocks[$i] = new NetAddr::IP $data[0];
  $allocated{"$masterblocks[$i]"} = 0;
  $free{"$masterblocks[$i]"} = 0;
  $bigfree{"$masterblocks[$i]"} = 128;	# Larger number means smaller block.
					# Set to 128 to prepare for IPv6
  $routed{"$masterblocks[$i]"} = 0;
}




#main()

if(!defined($webvar{action})) {
  $webvar{action} = "FUCK";	#shuts up the warnings.
}

if($webvar{action} eq 'index') {
  showSummary();
}
elsif ($webvar{action} eq 'newmaster') {
  printHeader('');

  $cidr = new NetAddr::IP $webvar{cidr};

  print "<div type=heading align=center>Adding $cidr as master block....\n";
  $sth = $ip_dbh->prepare("insert into masterblocks values ('$webvar{cidr}')");
  $sth->execute;
  die $sth->errstr if ($sth->errstr());

# Unrouted blocks aren't associated with a city (yet).  We don't rely on this
# elsewhere though;  legacy data may have traps and pitfalls in it to break this.
# Thus the "routed" flag.

  $sth = $ip_dbh->prepare("insert into freeblocks values ('$webvar{cidr}',".
	$cidr->masklen.",'<NULL>','n')");
  $sth->execute;
  die $sth->errstr if ($sth->errstr());

  print "Success!</div>\n";

  printFooter;
}
elsif($webvar{action} eq 'showmaster') {
  showMaster();
}
elsif($webvar{action} eq 'showrouted') {
  showRBlock();
}
elsif($webvar{action} eq 'search') {
  printHeader('');
  if(!$webvar{input})
  {
    # No search term.  Display everything.
    viewBy('all', '');
  } else {
    # Search term entered.  Display matches.
    # We should really sanitize $webvar{input}, no?
    viewBy($webvar{searchfor}, $webvar{input});
  }
  printFooter();
}

# Not modified or added;  just shuffled
elsif($webvar{action} eq 'assign') {
  assignBlock();
}
elsif($webvar{action} eq 'confirm') {
  confirmAssign();
}
elsif($webvar{action} eq 'edit') {
  edit();
}
elsif($webvar{action} eq 'update') {
  update();
}
elsif($webvar{action} eq 'delete') {
  remove();
}
elsif($webvar{action} eq 'finaldelete') {
  finalDelete();
}



elsif($webvar{action} eq 'free')
{
	showFree();
}
elsif($webvar{action} eq 'free2')
{
	showFreeDetail();
}
elsif($webvar{action} eq 'insert')
{
	insertAssign();
}
elsif($webvar{action} eq 'showedit')
{
	showEdit();
}
elsif($webvar{action} eq 'view')
{
	view();
}
# Eeeewwww..  The default case should be an error, really.  Added "search" check.
else	#handle the search part.
{
	printHeader('');
	if(!$webvar{input})
	{
		viewBy('all', '');
	}
	else
	{
		viewBy(parseInput($webvar{input}), $webvar{input});
	}
	printFooter();
	exit(0);
}


#end main()



sub viewBy($$)
{
  my ($category,$query) = @_;

print "<pre>\n";

print "start querysub: query '$query'\n";
# this may happen with more than one subcategory.  Unlikely, but possible.
  my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;

# Possible cases:
# 1) Partial IP/subnet.  Treated as "first-three-octets-match" in old IPDB,
#    I should be able to handle it similarly here.
# 2a) CIDR subnet.  Treated more or less as such in old IPDB.
# 2b) CIDR netmask.  Not sure how it's treated.
# 3) Customer ID.  Not handled in old IPDB
# 4) Customer "name".  If doing customer search, and we have non-numerics,
#	search on customer name.
# 5) Invalid data which might be interpretable as an IP or something, but
#    which probably shouldn't be for reasons of sanity.

  if ($category =~ /all/) {
    print "Showing all allocations\n";
    my $count = countRows('select count(*) from allocations');
    $sql = "select * from allocations order by cidr limit $offset,$RESULTS_PER_PAGE";
    queryResults($sql, $webvar{page}, $count);
  } elsif ($category =~ /cust/) {
    # Query for a customer ID.
    if ($query =~ /^\s*[0-9]+\s*$/) {
      # All numeric.  Search on customer ID.
      $sql = "select * from allocations where custid like '%$query%'";
      queryResults($sql, $webvar{page}, $count);
    } else {
	print "Searching for a customer based on (partial) name....\n";
      $sth = $ip_dbh->prepare("select * from customers where name like '%$query%'");
      $sth->execute;

if ($sth->rows eq 1) {
  @data = $sth->fetchrow_array;
  # Only 1 cust matched.
  print "Found 1 cust.  Displaying...\n";
      $sql = "select * from allocations where custid like '%$data[0]%'";
      queryResults($sql, $webvar{page}, $count);
} elsif ($sth->rows == 0) {
  # D'Oh!  Nothing found!
  printAndExit("No customers found.  Try searching on a smaller string.");
} else {
  # More than one found.  List'em and let the searcher decide.
  print "Found more than one.  Click the customer ID you want to show allocations for:\n";
  startTable('custid','name','city','phone','abuse contact','description');
  $count = 0;
  while (@data = $sth->fetchrow_array) {
    # custid,name,street,street2,city,province,pocode,phone,abuse,def_rdns,description
    @row = ("<a href=\"/ip/cgi-bin/main.cgi?action=search&input=$data[0]&searchfor=cust\">$data[0]</a>",
	$data[1],$data[4],$data[7],$data[8],$data[10]);
    printRow(\@row, 'color1' ) if($count%2==0);
    printRow(\@row, 'color2' ) if($count%2!=0);
  }
  print "</table>\n";
}

    }
  } elsif ($category =~ /ipblock/) {
    # Query is for a partial IP, a CIDR block in some form, or a flat IP.
    $query =~ s/\s+//g;
    print "Looking for IP-based matches on '$query':<br>\n";
    if ($query =~ /\//) {
      print "CIDR query.  Results may vary.\n";
      # 209.91.179/26 should show all /26 subnets in 209.91.179
      ($net,$maskbits) = split /\//, $query;
      if ($query =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{2}$/) {
	# /0->/9 are silly to worry about right now.  :/
	print "Exact subnet search...\n";
	$sth = $ip_dbh->prepare("select * from allocations where cidr='$query'");
	$sth->execute;
	if ($sth->rows == 0) {
	  print "No matches\n";
	} elsif ($sth->rows == 1) {
	  @data = $sth->fetchrow_array;
	  print "Found $data[0]\n";
	} else {
	  print "Too many matches (".$sth->rows.", should be 1).  Database is b0rked.\n";
	}
      } else {
        # select * from allocations where cidr like '$net%' and cidr like '%$maskbits'
      }
    } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
      ($net,$ip) = ($query =~ /(\d{1,3}\.\d{1,3}\.\d{1,3})\.(\d{1,3})/);
      print "Trying to find based on net '$net' and ip '$ip'...";
      $sfor = new NetAddr::IP $query;
      $sth = $ip_dbh->prepare("select * from allocations where cidr like '$net%'");
      $sth->execute;
      while (@data = $sth->fetchrow_array()) {
        $cidr = new NetAddr::IP $data[0];
	if ($cidr->contains($sfor)) {
	  print "Found '$cidr'!\n";
	}
      }
    } elsif ($query =~ /^\d{1,3}\.\d{1,3}\.\d{1,3}\.?$/) {
      print "3-octet block pattern.";
      $sql = "select * from allocations where cidr like '$query%'";
      queryResults($sql, $webvar{page}, $count);
    } else {
      # This shouldn't happen, but if it does, whoever gets it deserves what they get...
      printAndExit("Invalid query.");
    }
  } else {
    # This shouldn't happen, but if it does, whoever gets it deserves what they get...
    printAndExit("Invalid searchfor.");
  }
} # viewBy

# args are: a reference to an array with the row to be printedthe 
# class(stylesheet) to use for formatting. and the class - optional.
# if ommitting the class - call the sub as &printRow(\@array)
sub printRow
{
  my ($rowRef,$class) = @_;

  $class = '' if (!$class);
	
  print "<tr class=\"$class\">\n";
  foreach my $element (@$rowRef) {
    print "<td></td>" if (!defined($element));
    $element =~ s|\n|</br>|g;
    print "<td>$element</td>\n";
  }
  print "</tr>";
} # printRow

# Display certain types of search query.  Note that this can't be
# cleanly reused much of anywhere else as the data isn't neatly tabulated.
sub queryResults($$$) {
  my ($sql, $pageNo, $rowCount) = @_;
  my $offset = 0;
  $offset = $1 if($sql =~ m/.*limit\s+(.*),.*/);
  my $sth = $ip_dbh->prepare($sql);
  $sth->execute();
# Need some error checking...
print "About to start showing allocations: ".$ip_dbh->errstr;
  startTable('Allocation','CustID','Type','City','Description');
  my $count = 0;
  while(my @row = ($sth->fetchrow_array())) {
# We need to munge row[0] here.  We may also need to extract additional data.
    $row[0] = qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$row[0]">$row[0]</a>);
    printRow(\@row, 'color1', 1) if ($count%2==0); 
    printRow(\@row, 'color2', 1) if ($count%2!=0);
    $count++;
  }

# Have to think on this call, it's primarily to clean up unfetched rows from a select.
  $sth->finish();

  my $upper = $offset+$count;
  print "<tr><td colspan=10 bgcolor=white class=regular>Records found: $rowCount<br><i>Displaying: $offset - $upper</i></td></tr>\n";
  print "</table></center>\n";

# print the page thing..
  if ($rowCount > $RESULTS_PER_PAGE) {
    my $pages = ceil($rowCount/$RESULTS_PER_PAGE);
    print qq(<div class="center"> Page: );
    for (my $i = 1; $i <= $pages; $i++) {
      if($i == $pageNo){
	print "<b>$i&nbsp</b>\n";
      } else {
	print qq(<a href="/ip/cgi-bin/main.cgi?page=$i&input=$webvar{input}">$i&nbsp</a>\n);
      }
    }
    print "</div>";
  }
} # queryResults

# Prints table headings.  Accepts any number of arguments;
# each argument is a table heading.
sub startTable {
  print qq(<center><table width="98%" cellspacing="0" class="center"><tr>);

  foreach(@_) {
    print qq(<td class="heading">$_</td>);
  }
  print "</tr>\n";
} # startTable

# parseInput() unused?
###
#	given the input as a param, it returns the type of input:
#	either ipclass, name etc...
#
sub parseInput($)
{
	my $input = $_[0];

	return 'ipclass' if($input =~ m/^\s*\d{1,3}\.\d{1,3}\.\d{1,3}\.?$/);
	return 'ipclass' if($input =~ m/^\s*\d{1,3}\.\d{1,3}\.\d{1,3}\.0$/);
	return 'ip' if($input =~ m/^\s*\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$/);
	return 'ipc_nm' if($input =~ m/^\s*\d{1,3}\.\d{1,3}\.\d{1,3}\s*\/\d{2}$/);
	return 'ipc_nm' if($input =~ m/^\s*\d{1,3}\.\d{1,3}\.\d{1,3}\.0\s*\/\d{2}$/ );
	return 'nm' if($input =~ m/^\s*\/\d{2}$/);
	return 'name' if($input =~ m/^[\s\w\-]+$/);

	return 'unknown';
}

sub countRows($)
{
	my $ip_dbh = connectDB();
	my $sth = $ip_dbh->prepare($_[0]);
	$sth->execute();
	my @a = $sth->fetchrow_array();
	$sth->finish();
	$ip_dbh->disconnect();
	return $a[0];
}

# Initial display:  Show master blocks with total allocated subnets, total free subnets
sub showSummary
{
  print "Content-type: text/html\n\n";

  startTable('Master netblock', 'Routed netblocks', 'Allocated netblocks',
	'Free netblocks', 'Largest free block');

# Snag the allocations.
# I think it's too confusing to leave out internal allocations.
  my $sth = $ip_dbh->prepare("select * from allocations");
  $sth->execute();
  while (@data = $sth->fetchrow_array()) {
    # cidr,custid,type,city,description
    # We only need the cidr
    my $cidr = new NetAddr::IP $data[0];
    foreach $master (@masterblocks) {
      if ($master->contains($cidr)) {
	$allocated{"$master"}++;
      }
    }
  }

# Snag routed blocks
  my $sth = $ip_dbh->prepare("select * from routed");
  $sth->execute();
  while (@data = $sth->fetchrow_array()) {
    # cidr,maskbits,city
    # We only need the cidr
    my $cidr = new NetAddr::IP $data[0];
    foreach $master (@masterblocks) {
      if ($master->contains($cidr)) {
	$routed{"$master"}++;
      }
    }
  }

# Snag the free blocks.
  my $sth = $ip_dbh->prepare("select * from freeblocks");
  $sth->execute();
  while (@data = $sth->fetchrow_array()) {
    # cidr,maskbits,city
    # We only need the cidr
    my $cidr = new NetAddr::IP $data[0];
    foreach $master (@masterblocks) {
      if ($master->contains($cidr)) {
	$free{"$master"}++;
	if ($cidr->masklen < $bigfree{"$master"}) { $bigfree{"$master"} = $cidr->masklen; }
      }
    }
  }

# Print the data.
  $count=0;
  foreach $master (@masterblocks) {
    @row = ("<a href=\"/ip/cgi-bin/main.cgi?action=showmaster&block=$master\">$master</a>",
	$routed{"$master"}, $allocated{"$master"}, $free{"$master"}, 
	( ($bigfree{"$master"} eq 128) ? ("&lt;NONE&gt;") : ("/".$bigfree{"$master"}) )
	);

    printRow(\@row, 'color1' ) if($count%2==0);
    printRow(\@row, 'color2' ) if($count%2!=0);
    $count++;
  }
  print "</table>\n";
  print qq(<a href="/ip/addmaster.shtml">Add new master block</a>\n);
# For some *very* strange reason, we don't have to call printFooter here.
# If we do, the footer comes in twice...
#  printFooter;
} # showSummary

# Display detail on master
# Alrighty then!  We're showing routed blocks within a single master this time.
# We should be able to steal code from showSummary(), and if I'm really smart
# I'll figger a way to munge the two together.  (Once I've done that, everything
# else should follow.  YMMV.)
sub showMaster {
  printHeader('');

  print qq(<center><div class="heading">Summarizing routed blocks for ).
	qq($webvar{block}:</div></center><br>\n);

  startTable('Routed block','Routed to','Allocated blocks',
	'Free blocks','Largest free block');

  my $master = new NetAddr::IP $webvar{block};

  my $sth = $ip_dbh->prepare("select * from routed");
  $sth->execute();

  $i=0;
  while (@data = $sth->fetchrow_array()) {
    my $cidr = new NetAddr::IP $data[0];
    if ($master->contains($cidr)) {
      $localmasters[$i++] = $cidr;
      $free{"$cidr"} = 0;
      $allocated{"$cidr"} = 0;
    # Retain the routing destination
      $routed{"$cidr"} = $data[2];
    }
  }

# Count the allocations
# Technically, I could just include everything, but this is "more elegant"
# and fractionally faster in that it doesn't have to compare routed blocks
# against themselves.
  $sth = $ip_dbh->prepare("select * from allocations where type='e' or type='c'");
  $sth->execute();
  while (@data = $sth->fetchrow_array()) {
    # cidr,custid,type,city,description
    # We only need the cidr
    my $cidr = new NetAddr::IP $data[0];
    foreach $master (@localmasters) {
      if ($master->contains($cidr)) {
	$allocated{"$master"}++;
      }
    }
  }

# Snag the free blocks.
  $sth = $ip_dbh->prepare("select * from freeblocks");
  $sth->execute();
  while (@data = $sth->fetchrow_array()) {
    # cidr,maskbits,city
    # We only need the cidr
    my $cidr = new NetAddr::IP $data[0];
    foreach $master (@localmasters) {
      if ($master->contains($cidr)) {
	$free{"$master"}++;
      }
    }
  }

# Print the data.
  $count=0;
  foreach $master (@localmasters) {
    @row = ("<a href=\"/ip/cgi-bin/main.cgi?action=showrouted&block=$master\">$master</a>",
	$routed{"$master"}, $allocated{"$master"},
	$free{"$master"}, " &lt;null&gt; ");
    printRow(\@row, 'color1' ) if($count%2==0);
    printRow(\@row, 'color2' ) if($count%2!=0);
    $count++;
  }

  print qq(</table>\n<hr width="60%">\n).
	qq(<center><div class="heading">Unrouted blocks in $master:</div></center><br>\n);

  startTable('Netblock','Range');

# Snag the free blocks.
  $count = 0;
  $sth = $ip_dbh->prepare("select * from freeblocks where routed='n'");
  $sth->execute();
  while (@data = $sth->fetchrow_array()) {
    # cidr,maskbits,city
    # We only need the cidr
    my $cidr = new NetAddr::IP $data[0];
    if ($master->contains($cidr)) {
      @row = ("$cidr", $cidr->range);
      printRow(\@row, 'color1' ) if($count%2==0);
      printRow(\@row, 'color2' ) if($count%2!=0);
      $count++;
    }
  }

  print "</table>\n";
  printFooter;
} # showMaster

# Display details of a routed block
# Alrighty then!  We're showing allocations within a routed block this time.
# We should be able to steal code from showSummary() and showMaster(), and if
# I'm really smart I'll figger a way to munge all three together.  (Once I've
# done that, everything else should follow.  YMMV.
# This time, we check the database before spewing, because we may
# not have anything useful to spew.
sub showRBlock {
  printHeader('');

  my $master = new NetAddr::IP $webvar{block};

  my $sth = $ip_dbh->prepare("select * from allocations where type='e' or type='c'");
  $sth->execute();

  print qq(<center><div class="heading">Summarizing allocated blocks for ).
	qq($master:</div></center><br>\n);

  startTable('CIDR allocation','Type','CustID','Description');

  $count=0;
  while (@data = $sth->fetchrow_array()) {
    # cidr,custid,type,city,description
    my $cidr = new NetAddr::IP $data[0];
    if (!$master->contains($cidr)) { next; }

    @row = ("<a href=\"/ip/cgi-bin/main.cgi?action=edit&block=$data[0]\">$data[0]</a>",
	$data[2], $data[1], $data[4]);
    printRow(\@row, 'color1') if ($count%2 == 0);
    printRow(\@row, 'color2') if ($count%2 != 0);
    $count++;
  }

  print "</table>\n";

# If the routed block has no allocations, by definition it only has
# one free block, and therefore may be deleted.
  if ($count == 0) {
    print qq(<hr width="60%"><center><div class="heading">No allocations in ).
	qq($master.</div></center>\n).
	qq(<form action="/ip/cgi-bin/main.cgi" method=POST>\n).
	qq(<input type=hidden name=action value="delete">\n).
	qq(<input type=hidden name=block value="$master">\n).
	qq(<input type=hidden name=alloctype value="r">\n).
	qq(<input type=submit value=" Remove this block ">\n).
	qq(</form>\n);
  }

  print qq(<hr width="60%">\n<center><div class="heading">Free blocks within routed ).
	qq(submaster $master</div></center>\n);

#  print qq(<table border=3 cellspacing="0" class="center"><tr>);
  startTable('');

# Snag the free blocks.  We don't really *need* to be pedantic about avoiding
# unrouted free blocks, but it's better to let the database do the work if we can.
  $count = 0;
  $sth = $ip_dbh->prepare("select * from freeblocks where routed='y'");
  $sth->execute();
  while (@data = $sth->fetchrow_array()) {
    # cidr,maskbits,city
    my $cidr = new NetAddr::IP $data[0];
    if ($master->contains($cidr)) {
      @row = ($data[0]);
      printRow(\@row, 'color1') if ($count%2 == 0);
      printRow(\@row, 'color2') if ($count%2 != 0);
      $count++;
    }
  }

  print "</table>\n";
  printFooter;
} # showRBlock


sub showFree
{
	$| = 1;
	my $count = 0;
	print "Content-type: text/html\n\n";
	my $ip_dbh = connectDB();
	my $sth = $ip_dbh->prepare("select count(*) from `ips` where available='1'");
	$sth->execute();
	my $number = ($sth->fetchrow_array())[0];

	$sth = $ip_dbh->prepare("select SUBSTRING_INDEX(ip, '.', '3') sorter from ips where available = '1' group by sorter order by sorter");
	$sth->execute();
	my $a;
	$sth->bind_columns(\$a);
	my @ipclass;
	while($sth->fetch())
	{
		push(@ipclass, $a);
	}
	
	startTable('','IP class', 'Free IPs', 'Largest Block');
	foreach my $ipc (@ipclass) 
	{
		next if (!$ipc);
		$sth = $ip_dbh->prepare("select ip, ABS(SUBSTRING_INDEX(ip, '.', '-1')) sorter from ips where ip like '$ipc.%' and available = '1' order by sorter");
		$sth->execute();
		my $ip;
		my @ips;
		
		while($ip = ($sth->fetchrow_array())[0])
		{
			push(@ips, $ip);
		}
		
		my $length = scalar(@ips);
		next if($length < 1);
		my $last = $length-1;
		my $run = 0;
		my $max = 0;
		my $oldSubnet;
		for(my $i = 0; $i < $length; $i++)
		{
			
			$ips[$i] =~ m/.*\.(\d+)$/;
			my $subnet = $1;

			$oldSubnet = $subnet if(!$oldSubnet);
			#case when this subnet is contiguous.

			if($i == $last ) #case when we're on the last one
			{
				if($subnet == ($oldSubnet+1)){
					$run++;}
				if($run > $max){
					$max = $run;}
			}
			elsif ($subnet == ($oldSubnet+1) || $subnet==$oldSubnet ) 
			{
				$run++;
				$oldSubnet = $subnet;
			}
			else  #case when this subnet is non-contiguous.
			{
				if($run > $max)
				{
					$max = $run;
				}
				$run = 1;
				undef($oldSubnet);
			}
			
		}#end for

		$sth = $ip_dbh->prepare("select count(*) from ips where ip LIKE '$ipc%' and available = '1'");
		$sth->execute();
		my $numberFree = ($sth->fetchrow_array())[0];
		next if($numberFree == 0);
		my $viewButton = qq(<a href="cgi-bin/main.cgi?input=$ipc&page=1"> <img src="images/eye.png" border="0"></a> );
		$ipc = qq(<a href="cgi-bin/main.cgi?action=free2&ipc=$ipc">$ipc</a>);

		my @row = ($viewButton, $ipc, $numberFree, $max);
		
		printRow(\@row, 'color1' ) if($count%2==0);
		printRow(\@row, 'color2' ) if($count%2!=0);
		$count++;
	}#end foreach ipclass

	$sth->finish();
	$ip_dbh->disconnect();
	print "</table>";
	print "<div class=regular>There are a total of <b>$number</b> unused IPs.</div>";
	
}#end sub showFree

sub showIndex
{
	$| = 1;
	my $count = 0;
	print "Content-type: text/html\n\n";
	my $ip_dbh = connectDB();
	my $sth = $ip_dbh->prepare("select count(*) from `ips`");
	$sth->execute();
	my $number = ($sth->fetchrow_array())[0];

	$sth = $ip_dbh->prepare("select SUBSTRING_INDEX(ip, '.', '3') sorter from ips group by sorter order by sorter");
	$sth->execute();
	my $a;
	$sth->bind_columns(\$a);
	my @ipclass;
	while($sth->fetch())
	{
		push(@ipclass, $a);
	}

	
	startTable('','IP class', 'Free IPs', 'Largest Block');
	foreach my $ipc (@ipclass) 
	{
		next if (!$ipc);
		$sth = $ip_dbh->prepare("select ip, ABS(SUBSTRING_INDEX(ip, '.', '-1')) sorter from ips where ip like '$ipc%' order by sorter");
		$sth->execute();
		my $ip;
		my @ips;
		
		while($ip = ($sth->fetchrow_array())[0])
		{
			push(@ips, $ip);
		}
		
		my $length = scalar(@ips);
		next if($length < 1);
		my $last = $length-1;
		my $run = 0;
		my $max = 0;
		my $oldSubnet;
		for(my $i = 0; $i < $length; $i++)
		{
			
			$ips[$i] =~ m/.*\.(\d+)$/;
			my $subnet = $1;

			$oldSubnet = $subnet if(!$oldSubnet);
			#case when this subnet is contiguous.

			if($i == $last ) #case when we're on the last one
			{
				if($subnet == ($oldSubnet+1)){
					$run++;}
				if($run > $max){
					$max = $run;}
			}
			elsif ($subnet == ($oldSubnet+1) || $subnet==$oldSubnet ) 
			{
				$run++;
				$oldSubnet = $subnet;
			}
			else  #case when this subnet is non-contiguous.
			{
				if($run > $max)
				{
					$max = $run;
				}
				$run = 1;
				undef($oldSubnet);
			}
			
		}#end for

		$sth = $ip_dbh->prepare("select count(*) from ips where ip LIKE '$ipc%' and available =1");
		$sth->execute();
		my $numberFree = ($sth->fetchrow_array())[0];
		next if($numberFree == 0);
		my $viewButton = qq(<a href="cgi-bin/main.cgi?input=$ipc&page=1"> <img src="images/eye.png" border="0"></a> );
		$ipc = qq(<a href="cgi-bin/main.cgi?action=free2&ipc=$ipc">$ipc</a>);

		my @row = ($viewButton, $ipc, $numberFree, $max);
		
		printRow(\@row, 'color1' ) if($count%2==0);
		printRow(\@row, 'color2' ) if($count%2!=0);
		$count++;
	}#end foreach ipclass

	$sth->finish();
	$ip_dbh->disconnect();
	print "</table>";
	print "<div class=regular>There are a total of <b>$number</b> unused IPs.</div>";
	print qq(<div class=regular><a href="http://noc.vianet.ca/ip/cgi-bin/main.cgi?action=free">Show me</a> only subnets with available ips.</div>);
	
	
}#end sub showIndex

sub showFreeDetail
{
	printHeader('');
	
	my $ip_dbh = connectDB();
	my $sth = $ip_dbh->prepare("select ip, ABS(SUBSTRING_INDEX(ip, '.', '-1')) sorter from ips where ip like '$webvar{ipc}%' and available = '1' order by sorter");
	$sth->execute();
	my @ips;
	while(my @a = $sth->fetchrow_array())
	{
		push(@ips, $a[0]);
	}
	
	my $length = scalar(@ips);
	my $last = $length - 1 ;
	my %blocks;	#name=subnet it starts at , value=number of ips
	my $oldSubnet = -1;
	my $first;
	my $run = 0;

	for(my $i = 0; $i < $length; $i++)
	{

		$ips[$i] =~ m/.*\.(\d+)/;
		my $subnet = $1;
		$first = $subnet if(!defined($first));

		if($i == $last ) #case when we're on the last one
		{
			if($subnet == ($oldSubnet+1))
			{
				$run++;
				$blocks{$first} = $run;
			}
			else
			{

				$blocks{$first} = $run;
				$blocks{$subnet} = 1;
			}
			
		}
		elsif ($subnet == ($oldSubnet+1) || $subnet== -1 ) #contig.
		{
			$run++;
			$oldSubnet = $subnet;
		}
		else  #case when this subnet is non-contiguous.
		{
			$blocks{$first} = $run;

			$first = $subnet;
			$run = 1;			#because we have to count this one.
			$oldSubnet=$subnet;
		}


	}
	
	my $rows;	#to be subbed into detail.html
	my $count = 0;
	foreach my $key (sort(keys %blocks)) 
	{
		if($count%2==0)
		{
			$rows .= qq(<tr class="color1"> <td>$key</td> <td>$blocks{$key}</td><td><a href="main.cgi?action=assign&ipc=$webvar{ipc}&subnet=$key&size=$blocks{$key}"><font color="blue"><b>Assign</b></font></a></td></tr>\n);
		}
		else
		{
			$rows .= qq(<tr class="color2"> <td>$key</td> <td>$blocks{$key}</td><td><a href="main.cgi?action=assign&ipc=$webvar{ipc}&subnet=$key&size=$blocks{$key}"> <font color="blue"><b>Assign</b></font></a></td> </tr>\n);
		}
		$count++;
	}

	open(HTML, "../detail.html") || die "could not open detail.html : $!";
	my $html = join('', <HTML>);
	$html =~ s/\$\$SUBROWS\$\$/$rows/;
	$html =~ s/\$\$IPC\$\$/$webvar{ipc}/;
	print $html;
	close(HTML);
	printFooter();

}

sub assignBlock
{
  printHeader('');

  open HTML, "../assign.html"
	or die "Could not open assign.html: $!";
  my $html = join('',<HTML>);
  close(HTML);

  print $html;

  printFooter();
} # assignBlock

# Take info on requested IP assignment and see what we can provide.
sub confirmAssign
{
  printHeader('');

# Going to manually validate some items.
# custid and city are automagic.
  validateInput();
  if (!$webvar{maskbits}) {
    printAndExit("Please enter a CIDR block length.");
  }

# Snag the list of free blocks that are big enough for the requested allocation.
# Not yet sure what to do other than break if there's nothing.  <g>
# Not entirely sure how to detect that, either.  :(

# need some logic to handle routing "special case"...
  if ($webvar{alloctype} eq 'r') {
    $sth = $ip_dbh ->prepare("select * from freeblocks where maskbits<=$webvar{maskbits}".
	" and routed='n' order by maskbits desc");
    $failmsg = "We do not have a free routeable block of that size.<br>".
	"You will have to either route a set of smaller netblocks or a single smaller netblock."
  } else {
    $sth = $ip_dbh->prepare("select * from freeblocks where city='$webvar{city}'".
	" and maskbits<=$webvar{maskbits} and routed='y' order by maskbits desc");
    $failmsg = "You will have to route another superblock to $webvar{city}<br>".
	"from one of the master blocks in Sudbury.";
  }

  $sth->execute();
  @data = $sth->fetchrow_array();
  if ($data[0] eq "") {
    printAndExit("No suitable free block found.<br>".$failmsg);
  }

  # cidr,maskbits,city
  # We only need the cidr
  my $cidr = new NetAddr::IP $data[0];

print "Found one!<br>\n";
#    foreach $master (@masterblocks) {
#      if ($master->contains($cidr)) {
#        $free{"$master"}++;
#      }
#    }
#  }

$alloc_from = $cidr;

if ($cidr->masklen() == $webvar{maskbits}) {
  print "OK:  Allocating $cidr to $webvar{custid}<br>\n";
} else {
  print "err case:  need to allocate subblock of free block $cidr<br>\n";

print "<pre>\n";
  print "Need a /$webvar{maskbits} from $cidr:<br>\n";
  $maskbits = $cidr->masklen();
  while ($maskbits++ < $webvar{maskbits}) {
    @subblocks = $cidr->split($maskbits);
  print "$subblocks[0]\t$subblocks[1]\n";
  } # while
  $cidr = $subblocks[0];
print "</pre>\n";
}


# Check for new customer iff we're doing a customer allocation
  $custbits = "";
  if ($webvar{alloctype} eq 'c') {

# This check to validate off fargo (?) instead
    $sth = $ip_dbh->prepare("select * from customers where custid='$webvar{custid}'");
    $sth->execute;

    if ($sth->rows ne 1) {
      open CUSTHTML, "../newcust.html"
        or die "Could not open newcust.html: $!";
      $custbits = join '', <CUSTHTML>;
      close CUSTHTML;
      $custbits =~ s|\$\$CUSTID\$\$|$webvar{custid}|g;
      $custbits =~ s|\$\$CUSTCITY\$\$|$webvar{city}|g;
    }
  }

  open HTML, "../confirm.html"
    or die "Could not open confirm.html: $!";
  my $html = join '', <HTML>;
  close HTML;

# Stick in customer info as necessary - if it's blank, it just ends
# up as blank lines ignored in the rendering of the page
  $html =~ s|\$\$CUSTBITS\$\$|$custbits|g;

# Stick in the allocation data
  $html =~ s|\$\$ALLOC_TYPE\$\$|$webvar{alloctype}|g;
  $html =~ s|\$\$ALLOC_FROM\$\$|$alloc_from|g;
  $html =~ s|\$\$CIDR\$\$|$cidr|g;
  $html =~ s|\$\$CITY\$\$|$webvar{city}|g;
  $html =~ s|\$\$CUSTID\$\$|$webvar{custid}|g;
  $webvar{desc} = desanitize($webvar{desc});
  $html =~ s|\$\$DESC\$\$|$webvar{desc}|g;
  $html =~ s|\$\$NOTES\$\$|$webvar{notes}|g;
  $html =~ s|\$\$ACTION\$\$|insert|g;

  print $html;

  printFooter();
}

sub insertAssign
{
  # Some things are done more than once.
  validateInput();
  printHeader('');

print "<pre>\n";

# here we do the donkeywork of actually adding a block.
# Check cidr and alloc_from to see how bad it's going to be.

  my $sth;

# MySQL doesn't enforce referential integrity, but Postgres can.
# So we insert the customer data (if any) before the allocation.
# Note that city may be DIFFERENT than the city used for allocation!
if ($webvar{newcust} eq 'y') {
  $sth = $ip_dbh->prepare("insert into customers values ('$webvar{custid}', ".
	"'$webvar{custname}', '$webvar{custaddr1}', '$webvar{custaddr2}', ".
	"'$webvar{custcity}', '$webvar{custprov}', '$webvar{custpocode}', ".
	"'$webvar{custphone}', '$webvar{custabuse}', '$webvar{custrdns}', ".
	"'$webvar{custdesc}')");
  $sth->execute;
  print "customers: '".$sth->errstr."'\n";
}

$cidr = new NetAddr::IP $webvar{fullcidr};
$alloc_from = new NetAddr::IP $webvar{alloc_from};

  if ($webvar{fullcidr} eq $webvar{alloc_from}) {
    # Easiest case- insert in one table, delete in the other, and go home.
    # insert into allocations values (cidr,custid,type,city,desc) and
    # delete from freeblocks where cidr='cidr'
    # For data safety on non-transaction DBs, we delete first.

# new logic:  is it a routing allocation?
    if ($webvar{alloctype} eq 'r') {
# easiest case in the set.  Just update the table to indicate where
# the block is routed, and that it's available for further breakdown.
      $sth = $ip_dbh->prepare("update freeblocks set routed='y',city='$webvar{city}'".
	" where cidr='$webvar{fullcidr}'");
      $sth->execute;
      $sth = $ip_dbh->prepare("insert into routed values ('$webvar{fullcidr}',".
	$cidr->masklen.",'$webvar{city}')");
      $sth->execute;
    } else {
# Gotta clean the data for db transactions before actually doing this.
      $sth = $ip_dbh->prepare("delete from freeblocks where cidr='$webvar{fullcidr}'");
      $sth->execute;
      $sth = $ip_dbh->prepare("insert into allocations values ('$webvar{fullcidr}',".
	"'$webvar{custid}','$webvar{alloctype}','$webvar{city}','$webvar{desc}',".
	"'$webvar{notes}')");
      $sth->execute;
# Gotta check for errors too.
    }
  } else {
    # OK, this isn't so easy.  How bad is it going to get?
    # i=0
    # while (  )
    #  newfree[i++] = current->split(newalloc->maskbits)[1]

    $wantmaskbits = $cidr->masklen;

    $maskbits = $alloc_from->masklen();
$i=0;
    while ($maskbits++ < $wantmaskbits) {
      @subblocks = $alloc_from->split($maskbits);
      $newfreeblocks[$i++] = $subblocks[1];
    } # while
# We should now have a collection of the block(s) to be added to freeblocks in @newfreeblocks
# The next block of execution should be in a transaction on the db.
# However, transactions aside, this should fail in the safest way possible.
    # Delete old freeblocks entry
    $sth = $ip_dbh->prepare("delete from freeblocks where cidr='$webvar{alloc_from}'");
    $sth->execute();
print "Delete from freeblocks: '".$ip_dbh->errstr."'<br>\n";
# now we have to do some magic for routing blocks
    if ($webvar{alloctype} eq 'r') {
      # Insert the new freeblocks entries
      # Note that non-routed blocks are assigned to Sudbury by default
# --> that should be changed to a definite null entry of some kind.
      $sth = $ip_dbh->prepare("insert into freeblocks values (?, ?, 'Sudbury','n')");
      foreach $block (@newfreeblocks) {
 	$sth->execute("$block", $block->masklen);
print "Adding free block $block: '".$ip_dbh->errstr."'<br>\n";
      }
      # Insert the entry in the routed table
      $sth = $ip_dbh->prepare("insert into routed values ('$cidr',".
	$cidr->masklen.",'$webvar{city}')");
      $sth->execute;
print "Adding routing entry: '".$ip_dbh->errstr."'<br>\n";
      # Insert the (almost) same entry in the freeblocks table
      $sth = $ip_dbh->prepare("insert into freeblocks values ('$cidr',".
	$cidr->masklen.",'$webvar{city}','y')");
      $sth->execute;
print "Adding freeblocks entry: '".$ip_dbh->errstr."'<br>\n";
    } else {
      # Insert the new freeblocks entries
      $sth = $ip_dbh->prepare("insert into freeblocks values (?, ?, ?,'y')");
      foreach $block (@newfreeblocks) {
 	$sth->execute("$block", $block->masklen, $webvar{city});
      }
      # Insert the allocations entry
      $sth = $ip_dbh->prepare("insert into allocations values ('$webvar{fullcidr}',".
	"'$webvar{custid}','$webvar{alloctype}','$webvar{city}','$webvar{desc}')");
      $sth->execute;
    }
  }

  print qq(<div class="center"><div class="heading">The information was sucessfully added</div></div>);
  printFooter();

}

sub validateInput
{
	#number, city, CustomerID, name, swip, desc, notes
	
#	if(defined($webvar{number}) and $webvar{number} eq '-')
#	{
#		printAndExit("Please choose the number of IPs to assign");
#	}

  if ($webvar{city} eq '-') {
    printAndExit("Please choose a city.");
  }
  if ($webvar{alloctype} eq "c") {
    if (!$webvar{custid}) {
      printAndExit("Please enter a customer ID.");
    }
  } elsif ($webvar{alloctype} =~ /[eir]/){
    # All non-customer allocations MUST be entered with "our" customer ID.
    # I have Defined this as 6750400 for consistency.
    $webvar{custid} = "6750400";
  } else {
    # Danger! Danger!  alloctype should ALWAYS be set by a dropdown.  Anyone
    # managing to call things in such a way as to cause this deserves a cryptic error.
    printAndExit("Invalid alloctype");
  }
#	if(!$webvar{name})
#	{
#		printAndExit("Please enter a customer name");
#	}

	return 1;
}

sub validateNumber($$$)
{
	if(scalar(@_) < 3 )
	{
		die "Not enough arguments for the validateNumber sub";
	}
	
	my ($subnet, $ipc, $number) = @_;
	
	my $upper = $number + $subnet;	#upper bound
	
	if($subnet % $number !=0)
	{
		printAndExit("A block of $number ips cannot start on subnet $subnet.  Consult subnet calculator.")
	}

	if($upper > 256)
	{
		printAndExit("$number IPs starting at $subnet is greater than 255. Consider shifting to plan B.");
	}
	
	#check ips table to make sure that all ips in the range are free.
	my $ip_dbh = connectDB();
	my $sth = $ip_dbh->prepare("select ABS(SUBSTRING_INDEX(ip, '.', -1)) subnet, SUBSTRING_INDEX(ip,'.', 3) ipclass, available from ips having subnet >= $subnet and subnet < $upper and ipclass='$ipc'");
	$sth->execute();
	die $sth->errstr if($sth->errstr);
	
	my ($s, $i, $free);
	$sth->bind_columns(\$s, \$i, \$free);
	
	while($sth->fetch())
	{
		if($free != 1)
		{
			printAndExit("IP address $i.$s has already been assigned");
		}
	}
	my $rows = $sth->rows();
	
	if($rows < $number)
	{
		printAndExit("There are not enough free ips starting at subnet $subnet to assign $number ips. Try a lower subnet.");
	}
}

sub showEdit
{
	printHeader('');
	my $ipclass = $webvar{ipc};
	my $subnet = $webvar{subnet};
	if(!defined($subnet) || !defined($ipclass))
	{
		printAndExit("Not enough information to continue.\n");
	}

	open(HTML, "../editDisplay.html")|| die "Could not open editDisplay.html";
	my $html = join('', <HTML>);
	close(HTML);
	
	my $ip_dbh = connectDB();
	my $sth = $ip_dbh->prepare("select city, name, swip, custid, description, notes from `blocks` where ipclass='$ipclass' and subnet='$subnet'");
	$sth->execute() ||die;
	my ($city, $name, $swip, $cust_id, $desc, $notes);
	$sth->bind_columns(\$city, \$name, \$swip, \$cust_id, \$desc, \$notes);
	my $rv  = $sth->fetch();
	$sth->finish();
	$ip_dbh->disconnect();
	
	if($rv == 0)
	{
		printAndExit("Nothing was found for ipclass $ipclass, subnet $subnet");
	}

	$html =~ s|\$\$IPC\$\$|$ipclass|g;
	$html =~ s|\$\$SUBNET\$\$|$subnet|g;
	$html =~ s|\$\$CITY\$\$|$city|g;
	$html =~ s|\$\$NAME\$\$|$name|g;
	if(!defined($swip)){
		$swip = '-';}
	elsif($swip eq 'Y'){
		$swip = 'Yes';}
	elsif($swip eq 'N'){
		$swip = 'No';}
	else{
		$swip = '-';}
	$html =~ s|<option>$swip</option>|<option selected>$swip</option>|g;
	$html =~ s|\$\$CUSTID\$\$|$cust_id|g;
	$html =~ s|\$\$DESC\$\$|$desc|g;
	$html =~ s|\$\$NOTES\$\$|$notes|g;

	print $html;

	printFooter();
}

# Displays details of a specific allocation in a form
# Allows update/delete
# action=edit
sub edit
{
  printHeader('');

  # gotta snag block info from db
  $sth = $ip_dbh->prepare("select * from allocations where cidr='$webvar{block}'");
  $sth->execute;
  @data = $sth->fetchrow_array;

  open (HTML, "../editDisplay.html") || die "Could not open editDisplay.html :$!";
  my $html = join('', <HTML>);

  # We can't let the city be changed here;  this block is a part of
  # a larger routed allocation and therefore by definition can't be moved.
  # block and city are static.
  $html =~ s/\$\$BLOCK\$\$/$webvar{block}/g;
  $html =~ s/\$\$CITY\$\$/$data[3]/g;

  # Only 'e', 'c', or 'i' types should ever show up here... I hope.
  # Whew.  Depending on what's in the db for the allocation type,
  # make sure that shows up as selected.  Gotta love Perl.
  # Doing this allows cleanly changing an allocation from customer
  # to end-use/internal or vice versa without mangling everything.
  # Note that anything changed *to* an internal allocation
  # Will Have Its Customer ID Reset To 6750400.
  $type = '<select option name="alloctype"><option value="c"' .
	( ($data[2] eq 'c') ? (" selected>") : (">") ) .
	'Customer</option><option value="e"' .
	( ($data[2] eq 'e') ? (" selected>") : (">") ) .
	'End-use</option><option value="i"' .
	( ($data[2] eq 'i') ? (" selected>") : (">") ) .
	'Internal</option></select>';

  $html =~ s/\$\$TYPE\$\$/$type/g;

  # These can be modified, although CustID changes may get ignored.
  $html =~ s/\$\$CUSTID\$\$/$data[1]/g;
  $html =~ s/\$\$DESC\$\$/$data[4]/g;
  $html =~ s/\$\$NOTES\$\$/$data[5]/g;

  print $html;

  printFooter();
} # edit()

# Stuff new info about a block into the db
# action=update
sub update {
  printHeader('');
print "<pre>\n";

print " block: $webvar{block}\n";
print "  type: $webvar{alloctype}\n";
print "custid: $webvar{custid}\n";
print "  desc: $webvar{desc}\n";
print " notes: $webvar{notes}\n";

# -> Always update desc and notes
# better yet, just update the customer id every time, based on allocation type...

if ($webvar{alloctype} eq 'c') {
  print "Updating customer alloc:\n   ";
    $sth = $ip_dbh->prepare("update allocations set type='$webvar{alloctype}',".
	"custid='$webvar{custid}',description='$webvar{desc}',notes='$webvar{notes}' ".
	"where cidr='$webvar{block}'");
} else {
  print "Updating non-customer alloc:\n   ";
    $sth = $ip_dbh->prepare("update allocations set type='$webvar{alloctype}',".
	"custid='6750400',description='$webvar{desc}',notes='$webvar{notes}' ".
	"where cidr='$webvar{block}'");
}
$sth->execute;

if($sth->errstr()) {
  print $sth->errstr()
} else {
  print "Update successful.\n";
}

print "</pre>\n";
  printFooter;
} # update()

# Delete an allocation.
sub remove
{
	printHeader('');

	#show confirm screen.
	open(HTML, "../confirmRemove.html")|| die "Could not open confirmRemove.html :$!";
	my $html = join('', <HTML>);
	close(HTML);
	if (!defined($webvar{block})) {
		printAndExit("Error 332");
	}

my ($cidr, $custid, $type, $city, $desc, $notes);

if ($webvar{alloctype} eq 'r') {
  $sth = $ip_dbh->prepare("select cidr,city from routed where cidr='$webvar{block}'");
  $sth->execute();

	die $sth->errstr() if($sth->errstr());

  $sth->bind_columns(\$cidr,\$city);
  $sth->execute();
  $sth->fetch || die $sth->errstr();
  $custid = "N/A";
  $alloctype = $webvar{alloctype};
  $desc = "N/A";
  $notes = "N/A";
} else {
	my $sth = $ip_dbh->prepare("select * from allocations where cidr='$webvar{block}'");
	$sth->execute();
#	die $sth->errstr() if($sth->errstr());

	$sth->bind_columns(\$cidr, \$custid, \$alloctype, \$city, \$desc, \$notes);
	$sth->fetch() || die;
}

  if ($alloctype eq 'c') {
    $type = "Customer";
  } elsif ($alloctype eq 'e') {
    $type = "End-use";
  } elsif ($alloctype eq 'i') {
    $type = "Internal";
  } elsif ($alloctype eq 'r') {
    $type = "Routing";
  } else {
    # If someone manages to get here, they deserve what they get.
    die "Bad alloctype";
  }

print "Type: '$type'  alloctype: '$alloctype'";
	$html =~ s|Please confirm|Please confirm <b>removal</b> of|;
	$html =~ s|\$\$BLOCK\$\$|$cidr|g;
	$html =~ s|\$\$TYPEFULL\$\$|$type|g;
	$html =~ s|\$\$ALLOCTYPE\$\$|$alloctype|g;
	$html =~ s|\$\$CITY\$\$|$city|g;
	$html =~ s|\$\$CUSTID\$\$|$custid|g;
	$html =~ s|\$\$DESC\$\$|$desc|g;
	$html =~ s|\$\$NOTES\$\$|$notes|g;

	$html =~ s|\$\$ACTION\$\$|finaldelete|g;

	$html =~ s|<!--warn-->|<tr bgcolor="black"><td colspan="2"><div class="red">Warning: clicking confirm will remove this record entirely.</div></td></tr>|;

	print $html;
}

###
#
#	given a netmask of the form /24 it will return the number of IPs
#	e.g.:  nm2number('/24') = 256
#	Note: no checking.... if the caller does not provide a valid netmask,
#	the passed value is returned untouched.
#
sub nm2number
{
	my $netmask = $_[0];

	if( $netmask =~ m|.*/24.*|){
		$netmask = 256;}
	elsif( $netmask =~ m|.*/25.*|){
		$netmask = 128;}
	elsif( $netmask =~ m|.*/26.*|){
		$netmask = 64;}
	elsif( $netmask =~ m|.*/27.*|){
		$netmask = 32;}
	elsif( $netmask =~ m|.*/28.*|){
		$netmask = 16;}
	elsif( $netmask =~ m|.*/29.*|){
		$netmask = 8;}
	elsif( $netmask =~ m|.*/30.*|){
		$netmask = 4;}
	elsif( $netmask =~ m|.*/31.*|){
		$netmask = 2;}
	elsif( $netmask =~ m|.*/32.*|){
		$netmask = 1;}

	return $netmask;
}

# Delete an allocation.  Return it to the freeblocks table;  munge
# data as necessary to keep as few records as possible in freeblocks
# to prevent weirdness when allocating blocks later.
sub finalDelete
{
  printHeader('');

# if allocation block (type e, c, or i), we delete from allocations
# and clean up routed entries in freeblocks.
# If we're deleting a routed block, we delete from routed and clean
# up non-routed entries in freeblocks.

  $cidr = new NetAddr::IP $webvar{block};

# Routing is the "special case"
  if ($webvar{alloctype} eq 'r') {
    $sth = $ip_dbh->prepare("delete from routed where cidr='$webvar{block}'");
    $sth->execute;
# check ^ for errors

    $sth = $ip_dbh->prepare("select * from freeblocks where ".
 	"maskbits<=".$cidr->masklen." and routed='n' order by maskbits desc");
  } else {
    $sth = $ip_dbh->prepare("delete from allocations where cidr='$webvar{block}'");
    $sth->execute;
# check ^ for errors

    $sth = $ip_dbh->prepare("select * from freeblocks where city='$webvar{city}'".
 	" and maskbits<=".$cidr->masklen." and routed='y' order by maskbits desc");
  }

# Now we look for larger-or-equal-sized free blocks in the same master (routed)
# (super)block. If there aren't any, we can't combine blocks anyway.  If there
# are, we check to see if we can combine blocks.
# Execute the statement prepared in the if-else above.
  $sth->execute;

  $i=0;
  while (@data = $sth->fetchrow_array) {
    my $testIP = new NetAddr::IP $data[0];

# NetAddr::IP->compact() attempts to produce the smallest inclusive block
# from the caller and the passed terms.
# EG:  if you call $cidr->compact($ip1,$ip2,$ip3) when $cidr, $ip1, $ip2,
#	and $ip3 are consecutive /27's starting on .0, you will get an
#	array containing a single /25 as element 0

    @together = $testIP->compact($cidr);
    $num = @together;
    if ($num == 1) {
      $cidr = $together[0];
      $combinelist[$i++] = $testIP;
    }
  }

# Clear old freeblocks entries - if any.  $i==0 if not.
  if ($i>0) {
    $sth = $ip_dbh->prepare("delete from freeblocks where cidr=?");
    foreach $block (@combinelist) {
      $sth->execute($block);
    }
  }

# insert "new" freeblocks entry
  if ($webvar{alloctype} eq 'r') {
    $sth = $ip_dbh->prepare("insert into freeblocks values ('$cidr',".$cidr->masklen.
		",'Sudbury','n')");
  } else {
    $sth = $ip_dbh->prepare("insert into freeblocks values ('$cidr',".$cidr->masklen.
		",'$webvar{city}','y')");
  }
  $sth->execute;

  print "<div class=heading align=center>Success!  $webvar{block} deleted.</div>\n";

  printFooter;
} # finalDelete

sub view
{
	printHeader('');
	my $ipclass = $webvar{ipc};
	my $subnet = $webvar{subnet};
	if(!defined($subnet) || !defined($ipclass))
	{
		printAndExit("Not enough information to continue.\n");
	}

	open(HTML, "../view.html")|| die "Could not open view.html";
	my $html = join('', <HTML>);
	close(HTML);
	
	my $ip_dbh = connectDB();
	my $sth = $ip_dbh->prepare("select city, name, swip, custid, description, notes from `blocks` where ipclass='$ipclass' and subnet='$subnet'");
	$sth->execute() ||die;
	my ($city, $name, $swip, $cust_id, $desc, $notes);
	$sth->bind_columns(\$city, \$name, \$swip, \$cust_id, \$desc, \$notes);
	my $rv  = $sth->fetch();
	$sth->finish();
	$ip_dbh->disconnect();
	
	if($rv == 0)
	{
		printAndExit("Nothing was found for ipclass $ipclass, subnet $subnet");
	}

	$html =~ s|\$\$IPC\$\$|$ipclass|g;
	$html =~ s|\$\$SUBNET\$\$|$subnet|g;
	$html =~ s|\$\$CITY\$\$|$city|g;
	$html =~ s|\$\$NAME\$\$|$name|g;
	
	if(!defined($swip)){
		$swip = 'N.A.';}
	elsif($swip eq 'Y'){
		$swip = 'Yes';}
	elsif($swip eq 'N'){
		$swip = 'No';}

	$html =~ s|\$\$SWIP\$\$|$swip|g;
	$html =~ s|\$\$CUSTID\$\$|$cust_id|g;
	$html =~ s|\$\$DESC\$\$|$desc|g;
	$html =~ s|\$\$NOTES\$\$|$notes|g;

	print $html;

	printFooter();	

}