#!/usr/bin/perl
#tracker-admin.pl
#provide web administration of tracker configuration and device tables.

use CGI::Pretty qw(:standard);
use DBI;
use tracker;
use tracker_cgi;
use MIME::Base64;

$| = 1;
print $tracker::tracker_header;

my ($device, $config, $log, $dbconn);

unless ( $dbconn = tracker::conndb() )
	{ tracker::cgi_die("Database connect failed:" . $dbconn->errstr) }

#if device is set, then we are in device editing mode
if ($device = param("device"))
	{
	#the user hit the save button on the device form	
	if (param('Save'))
		{
		unless ( ( param("name") =~ /^[\x20-\x7e]*$/ ) &&
			( param("address") =~ /^[\w-.]+$/) &&
			( param("status") =~ /^\d+$/) &&
			( param("snmp_ro_string") =~ /^[\x20-\x7e]*$/ ) &&
			( param("snmp_rw_string") =~ /^[\x20-\x7e]*$/ ) &&
			( param("ignore_ports") =~ /^[\x20-\x7e]*$/ ) )
			{
			$dbconn->disconnect;
			print tracker::html_errors(["Invalid input"]), end_html;
			exit
			}
			
		#If it is an existing device name, update it, otherwise insert a new record in the DB
		my $sth = $dbconn->prepare("SELECT COUNT(*) FROM devices WHERE name = " . $dbconn->quote(param("name")) . ";" );
		$sth->execute() or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
		if ($sth->fetchrow_array() )
			{
			my $update = "UPDATE devices SET " . 
				"address = " . $dbconn->quote(lc(param("address"))) .
				", status = " . param("status") .
				", snmp_ro_string = " . $dbconn->quote(param("snmp_ro_string")) .
				", snmp_rw_string = " . $dbconn->quote(param("snmp_rw_string")) .
				", ignore_ports = " . $dbconn->quote(param("ignore_ports")) .
				" WHERE name = " . $dbconn->quote(param("name")) . ";";
			$sth = $dbconn->prepare($update);
			$sth->execute or tracker::cgi_die("Database update failed:" . $dbconn->errstr);
			if (param('came_from'))
				{ print redir(decode_base64(param('came_from'))) }
			else { print redir(url(-relative=>1) . '?device=view') }
			}
		else
			{
			my $insert = "INSERT INTO devices " . 
				"(name, address, status, snmp_ro_string, snmp_rw_string, ignore_ports) VALUES (" .
				$dbconn->quote(lc(param("name"))) . "," .
				$dbconn->quote(lc(param("address"))) . "," .
				param("status") . "," .
				$dbconn->quote(param("snmp_ro_string")) . "," .
				$dbconn->quote(param("snmp_rw_string")) . "," .
				$dbconn->quote("" . param("ignore_ports")) . ");";
			$sth = $dbconn->prepare($insert);
			$sth->execute or tracker::cgi_die("Database update failed:" . $dbconn->errstr);
			print redir( url(-relative => 1) . '?device=edit&name=' . param('name') )
			}
		$dbconn->commit or tracker::cgi_die("Database commit transaction failed:" . $dbconn->errstr);
		}
	elsif ($device eq 'delete')
		{
		my $sth = $dbconn->prepare("DELETE FROM devices WHERE name = " . $dbconn->quote(param("name")) . ";");
		$sth->execute or tracker::cgi_die("Database delete failed:" . $dbconn->errstr);
		$dbconn->commit or tracker::cgi_die("Database commit transaction failed:" . $dbconn->errstr);
		print h3("Deleted device " . param("name")), p(), p(),
			"<script>window.location = '", decode_base64(param('came_from')), "'; </script>", end_html()
		}
	elsif ($device eq 'edit')
		{
		my ($id, $type);
		my (@type_values, %type_labels, @status_values, %status_labels);
		
		#build the values and labels for the device status drop-down menu from the DB
			{ push @type_values, $id; $type_labels{$id} = $type }
		$sth = $dbconn->prepare("SELECT id, name FROM device_states;");
		$sth->execute or  tracker::cgi_die("Database select failed:" . $dbconn->errstr);
		while ( ($id, $type) = $sth->fetchrow_array )
			{ push @status_values, $id; $status_labels{$id} = $type }
			
		if ( ($name = param("name")) && (param("name") =~ /^[\x20-\x7e]*$/))
			{
			my (@row, $description, $ignore_port_display);
			my $select = "SELECT address, status, snmp_ro_string, snmp_rw_string, object_id, cdp_id, " .
				tracker::timestamp("last_contacted") . ", ignore_ports, error FROM devices WHERE name = " . $dbconn->quote($name) . ";";
			my $sth = $dbconn->prepare($select);
			$sth->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
			if (@row = $sth->fetchrow_array)
				{
				param("status", $row[1]);
				param("address", $row[0]);
				param("snmp_ro_string", $row[2]);
				param("snmp_rw_string", $row[3]);
				if ($row[8])
					{
					my $sth = $dbconn->prepare("SELECT description FROM cisco_products WHERE snmp_oid = '$row[8]';");
					$sth->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
					unless (($description) = $sth->fetchrow_array ) { $description = "unknown" }
					}
				param("ignore_ports", $row[7]);
				print start_form(), hidden("device","edit"), hidden("name", $name), hidden("came_from"),
					table( {-cellpadding=>1},
					Tr(td({colspan=>2}, h5("Required values:"))),
					Tr(td(["Device Name:", $name ])),
					Tr(td(["Device object ID:", "$row[4] ($description)"])),
					Tr(td(["Device last contacted:", $row[6] ])),
					Tr(td(["Device CDP ID:", $row[5] ])),
					Tr(td(["Error:", $row[8] ])),
					Tr(td(["Network address:", textfield(-size=>60, -name=>"address") ])),
					Tr(td(["Device Status:"]), td(popup_menu(-name=>'status',-values=>\@status_values,-labels=>\%status_labels))),
					Tr(td({colspan=>2}, hr())),
					Tr(td({colspan=>2}, h5("The following values will assume the global defaults if left blank:"))),
					Tr(td(["SNMP read-only community string:", textfield(-size=>15, -name=>"snmp_ro_string") ])),
					Tr(td(["SNMP read-write community string:", textfield(-size=>15, -name=>"snmp_rw_string") ])),
					Tr(td({-colspan=>2}, hr())),
					Tr(td({colspan=>2}, h5("Ignored ports:"))),
					Tr(td({colspan=>2}, "A colon separated list of ports to ignore:")),
					Tr(td({colspan=>2}, textfield(-size=>40, -name=>"ignore_ports"))),
					Tr(td({-colspan=>2}, hr())),
					Tr(td({-align=>'center'}, [submit(-name=>"Save",-value=>"Save"), defaults("Cancel")]))
					), end_form(), a( { -href=> "telnet://$row[0]" }, "telnet to $name" )
				}
			else
				{
				print html_errors(["Device $name not found."])
				}
			}
		else
			{
			print start_form(), hidden("device","edit"), hidden("came_from"),
				table( {-cellpadding=>1},
				Tr(td({colspan=>2}, h5("Required values:"))),
				Tr(td(["Device Name:", textfield(-size=>60, -name=>"name") ])),
				Tr(td(["Network address:", textfield(-size=>60, -name=>"address") ])),
				Tr(td(["Device Status:"]), td(popup_menu(-default=>'4', -name=>'status',-values=>\@status_values,-labels=>\%status_labels,-default=>1))),
				Tr(td({colspan=>2}, hr())),
				Tr(td({colspan=>2}, h5("The following values will assume the global defaults if left blank:"))),
				Tr(td(["SNMP read-only community string:", textfield(-size=>15, -name=>"snmp_ro_string") ])),
				Tr(td(["SNMP read-write community string:", textfield(-size=>15, -name=>"snmp_rw_string") ])),
				Tr(td({-colspan=>2}, hr())),
				Tr(td({colspan=>2}, h5("Ignored ports:"))),
				Tr(td({colspan=>2}, "A colon separated list of ports to ignore:")),
				Tr(td({colspan=>2}, textfield(-size=>40, -name=>"ignore_ports"))),
				Tr(td({-colspan=>2}, hr())),
				Tr(td({-align=>'center'}, [submit(-name=>"Save",-value=>"Save"), defaults("Cancel")]))
				), end_form()
			}	
		}
	elsif ($device eq "search")
		{
		
		my ($window) = 20;
		
		my (@query_parameters, $address, $name, $type, $status, $offset);
		my @url_parameters = ( "device=search" );
                if ( ($address = lc(param("address"))) && ( param("address") =~ /^[\w-.]+$/ ))
			{
			push @url_parameters, "address=$address";
			push @query_parameters, "(d.address like " . $dbconn->quote('%' . $address . '%') . ")"
			}
		if ( ($name = lc(param("name"))) && ( param("name") =~ /^[\w-.]+$/ ))
			{
			push @url_parameters, "name=$name";
			push @query_parameters, "(d.name like " . $dbconn->quote('%' . $name . '%') . ")"
			}
		if (($status = param("status")) && ( param("status") =~ /^[\w,]+$/ ))
			{
			my ($entry, @list);
			push @url_parameters, "status=$status";
			foreach $entry (split(',', $status))
				{
				push @list, "(s.name =" . $dbconn->quote($entry) . ")"
				}
			push @query_parameters, '(' . join( " OR ", @list) . ')'
			}

		push @query_parameters, "(d.status = s.id)";
		
		$query_parameters = join(" AND ", @query_parameters);
		#how many rows are there?
		my $sth = $dbconn->prepare("SELECT count(d.name) FROM devices d, device_states s WHERE $query_parameters;");
		$sth->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
		($rows) = $sth->fetchrow_array();
		if ($rows < 1) { print "None" }
		
		if (param("offset") && (param("offset") =~ /^\d*$/) )
			{ $offset = param("offset") }
		else { $offset = 0 }
		
		$real_offset = $window * $offset;
		
		if (param("offset")) 
			{
			if  ($offset == 1)
				{ print a({-href=>url() . "?" . join('&', @url_parameters)}, "<< Back", p() ) }
			else
				{ print a({-href=>url() . "?" . join('&', @url_parameters, "offset=" . ($offset-1))}, "<< Back", p() ) }
			}

		my ($name, $object_id, $status, $table_contents);
		my $select = "SELECT d.name, d.object_id, s.name FROM devices d, device_states s " . 
			"WHERE  $query_parameters ORDER BY d.name, d.object_id " . tracker::offset($window,$real_offset) . ";";
		$sth = $dbconn->prepare($select);
		$sth->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
		print "<table border=1>";
		while ( ($name, $object_id, $status) = $sth->fetchrow_array )
			{
			my ($sth2, @row);
			if ($object_id)
				{
				$sth2 = $dbconn->prepare("SELECT description FROM cisco_products WHERE snmp_oid = '$object_id';");
				$sth2->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
				if (@row = $sth2->fetchrow_array) { $object_id = $row[0] }
				}
			print Tr(td([a({href=>url(relative=>1) . "?device=edit&name=$name&came_from=" . encode_base64(url(-relative => 1, -query => 1))}, $name),
				$status, $object_id, 
				a({href=>url(relative=>1) . "?device=delete&name=$name&came_from=" . encode_base64(url(-relative => 1, -query => 1))}, "delete"),
				a({href=>"telnet://$name"}, "telnet")]));
			}
		print "</table>",p();
		if ($rows > ($real_offset + 20) ) 
			{ print a({-href=>url() . "?" . join('&', @url_parameters, "offset=" . ($offset+1))}, "More >>") }
		
		}	
	
	else
		{
                my $select = "SELECT query, name from device_page;";
		$sth = $dbconn->prepare($select);
		$sth->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
                print h3("Device lists:");
		while ( ($query, $name) = $sth->fetchrow_array )
			{
                        print a({-href=>url(relative=>1) . "?device=search&$query"}, $name), p()
			}
		print h3("Find device by name:");
		param("device", "search");
		print start_form(-action=>url(relative => 1)), hidden("device"), "Name:", textfield(-size=>30, -name=>"name"), 
			submit(-name=>"Go"), end_form();
		print h3("Create new device:"); 
		print p(), a({href=>url(relative=>1) . "?device=edit"}, "New device"), p(), p() 
			

		}
	}
	
	
	
elsif ($config = param("config"))
	{
	if ($config eq 'edit')
		{
		if (param('Save') && (param("name") =~ /^\w*$/) && (param("value") =~ /^\w*$/) )
			{
			my $sth = $dbconn->prepare("SELECT COUNT(*) FROM config WHERE name = " . $dbconn->quote(param("name")) . ";");
			$sth->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
			if ($sth->fetchrow_array == 1)
				{
				my $update = "UPDATE config SET value = " . $dbconn->quote(param("value")) . " WHERE name = " .
					$dbconn->quote(param(name)) . ";";
				$sth = $dbconn->prepare($update);
				$sth->execute or tracker::cgi_die("Database update failed:" . $dbconn->errstr);
				print "<script>window.location = '", url(realtive=>1), "?config=view'; </script>", end_html()
				}
			else
				{
				my $insert = "INSERT INTO config (name, value) VALUES ("  . $dbconn->quote(param("name")) . "," .
					$dbconn->quote(param("value")) . ");";
				$sth = $dbconn->prepare($insert);
				$sth->execute or tracker::cgi_die("Database insert failed:" . $dbconn->errstr);
				print "<script>window.location = '", url(realtive=>1), "?config=view'; </script>", end_html()
				}
			$dbconn->commit or tracker::cgi_die("Database commit transaction failed:" . $dbconn->errstr);
			}
		else
			{
			my ($name, @row);
			if (($name = param("name")) && (param("name") =~ /^\w*$/))
				{
				my $sth = $dbconn->prepare("SELECT value FROM config WHERE name = " . $dbconn->quote($name) . ";");
				$sth->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
				if (@row = $sth->fetchrow_array) { param("value", $row[0]) }
				print start_form(), hidden("config","edit"), hidden("name",$name), table( {-cellpadding=>1},
					Tr(td(["Config variable:", $name ])),
					Tr(td(["Value:", textfield(-size=>30, -name=>"value") ])),
					Tr(td({-colspan=>2}, hr())),
					Tr(td({-align=>'center'}, [submit(-name=>"Save",-value=>"Save"), defaults("Cancel")]))
					), end_form()
				}
			else
				{	
				print start_form(), hidden("config","edit"), table( {-cellpadding=>1},
					Tr(td(["Config variable:", textfield(-size=>30, -name=>"name") ])),
					Tr(td(["Value:", textfield(-size=>30, -name=>"value") ])),
					Tr(td({-colspan=>2}, hr())),
					Tr(td({-align=>'center'}, [submit(-name=>"Save",-value=>"Save"), defaults("Cancel")]))
					), end_form()
				}
			
			}
		}
	elsif (($config eq 'delete') && (param("name") =~ /^\w+$/))
		{
		my $sth = $dbconn->prepare("DELETE FROM config WHERE name = " . $dbconn->quote(param("name")) . ";");
		$sth->execute or tracker::cgi_die("Database delete failed:" . $dbconn->errstr);
		$dbconn->commit or tracker::cgi_die("Database commit transaction failed:" . $dbconn->errstr);
		print h3("Deleted config variable " . param("name")), p(), p(),
			a({href=>url(realtive=>1) . "?config=view"}, "Continue")
		}

	else
		{
		my ($table_contents, $name, $value, %config);
		my $sth = $dbconn->prepare("SELECT name, value FROM config ORDER BY name DESC;");
		$sth->execute or tracker::cgi_die("Database select failed:" . $dbconn->errstr);
		$table_contents = Tr( td({width=>'40%'},"Variable"), td({width=>'40%'},"Value") );
		while ( ($name, $value) = $sth->fetchrow_array )
			{
			$table_contents .= Tr(td([ a({href=>url(realtive=>1) . "?config=edit&name=$name"}, $name),
				$value,
				a({href=>url(realtive=>1) . "?config=delete&name=$name"}, "delete") ]))
			}
		print table({border=>1},$table_contents), 
			a({href=>url(relative=>1) . "?config=edit"}, "New configuration variable")
		}
	}


$dbconn->disconnect();
		
print 	p(), p(),
	a({href=>url(-relative=>1)."?device=view"}, "Manage devices"),
	" -/- ", 
	a({href=>url(-relative=>1)."?config=view"}, "Configure Tracker"),
	end_html();


sub redir
	{
	my $url = shift;
	return "<script>window.location = '" . $url .  "'; </script>" . CGI::end_html();
	}