#!/usr/bin/perl -w
#
# sysquery.pl - allow user to specify what system summary they wish to see
# 
# Synopsis:
# 
#	sysquery.pl			returns a form for searching
#	sysquery.pl?target=form		returns a form for searching
#	sysquery.pl?target=showall	returns all systems with summaries
#	sysquery.pl?target=showall&fqdn=""&os=""&location=""&vendor=""&organization=""
#					returns all systems which meet
#					search criteria
# 
# This Perl script is capable of generating frame-based html.  It 
# is possible to specify a frame in which to display the target's 
# summary information. You do this by specifying the name of the 
# frame as a cgi-argument :
#
#	sysquery.pl?target=form&frame=<frame_name>
#	sysquery.pl?target=showall&frame=<frame_name>
#	sysquery.pl?target=<fqdn>&frame=<frame_name>
# 
# For more information, see the file README.sysquery.and.frames in
# this same directory.
# 
# This Perl script was designed to both create a form prompting a
# user for the name of a system to display a summary for as well
# as process the output of that form.
# 
# The web pages generated by this script are really simple.  Therefore,
# no other Perl modules were employed in their creation.  This script
# exists just to give you an idea of how to implement the "back end"
# of syssumm.
# 
# This Perl script is designed to be called as a CGI program by a
# web server.
# 
# ==============================================================
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 
# 2 of the License, or (at your option) any later version.
# 
# This program is distributed in the hope that it will be 
# useful, but WITHOUT ANY WARRANTY; without even the implied
# warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
# PURPOSE.  See the GNU General Public License for more details.
# 
# Original source:
#	Copyright (c) 1999, 2000 SAIC (Bruce W. Mohler)
# 
# Contributed source:
#	Copyright (c) 1999, 2000 Frank Crawford
#	Copyright (c) 1999, 2000 Bart Swennen
#	Copyright (c) 1999, 2000 Paul Farrall
#
# Major To Do's
#       Cleanup the output for single host display
#       Add links to show sub-tables (software, network) by host
# 
# ==============================================================
# 
# $Header: /export/home/mohlerb/src/perl/syssumm/web.server/RCS/sysquery.pl,v 1.27 2000/02/23 19:27:01 mohlerb Exp mohlerb $
# 
# Revision 1.1  2000/03/26 09:54:12  powellc
# Initial revision
#

use CGI qw(param);
use FileHandle;
use Sys::Hostname;
# CUSTOMIZE
use lib "/home/syssumm";
use common;
use DBI;


##############################################################################
# Global Variables
##############################################################################

# static variables
$Debug = 0;				# set this to 1 during debugging
$DBUserName = "";                       # Must be a valid MySQL user with the right perms
$DBUserPwd = "";			# Must coincide with user - none is possible

# 
# At run-time, certain global variables are defined from a
# persistent configuration file by the function LoadConfigurationFile
# in common.pm.  Refer to the source in that Perl module to 
# determine which variables are defined.
# 
# In addition, certain variables are exported into the calling
# scripts main:: name space including the syssumm home directory,
# uid, and gid.
# 

my $dbh = DBI->connect('DBI:mysql:syssumm', "$DBUserName", "$DBUserPwd");
$CGIScriptName = "syssumm/chuckles.pl";
$PathToSyssummDirectory = "";

# what do we want this script to do? "form", "showall", or "nodeid (which generates the summary)"
$Target = "";				# name of system summary to display
$Frame = "";				# sent to frame <frame>
# search terms on the form
$ServerOS = "";
$Location = "";
$Org = "";
$Vendor = "";

@FormatInfo = ( );
$ThisLineNumber = 0;
$ThisManyColumns = 0;

$TableWidth = "95%";

@GraphicFileSuffixes = (
	"jpg",
	"jpeg",
	"gif",
	"png",
);


##############################################################################
# Subroutines
##############################################################################

########################## Figure out input ########################## 

sub ParseInputFromWebServer
{	my $Query = "";

	LoadConfigurationFile();

	# print out this line whether or not we've enabled $Debug!
	$Hostname = hostname();
	$Timestamp = localtime();
	print STDERR '-' x 75;
	print STDERR "\nsysquery.pl on $Hostname at $Timestamp\n"; 

	$PathToSyssummDirectory = "$DocrootDirectory/$SyssummDirectory";

	if (param()) {

		# Get cgi parameters
		$Target = param("target");
		$Frame = param("frame");
		$ServerOS = param("osname");
		$Location = param("location") ;
		$Version = param("osversion");
		$Vendor = param("osvendor");
		$NodeID = param("nodeid");
		$PartialName = param("partial_name");
		$SoftwareList = param("software_list");
		$ServicesList = param("services_list");
		$DisksList = param("disks_list");
		$PartialSoft = param("partial_soft");
		$EditFields = param("editfields");
		$ProcessChanges = param("made_changes");
		$ChangedLocation = param("c_location");
		$ChangedBuilding = param("c_building");
		$ChangedRoom = param("c_room");
		$ChangedOrganization = param("c_organization");

		if ( $Debug ) {
			if ( $Target ) { print STDERR "\tTarget is $Target\n"; }
			if ( $Frame ) { print STDERR "\tFrame is $Frame\n"; }
			if ( $ServerOS ) { print STDERR "\tServerOS is $ServerOS\n"; }
			if ( $Location ) { print STDERR "\tLocation is $Location\n"; }
			if ( $Org ) { print STDERR "\tOrg is $Org\n"; }
			if ( $Vendor ) { print STDERR "\tVendor is $Vendor\n"; }
		}

		# Are they asking for a summary or a specific system?
		if ($Target eq "showall") {
			GenerateShowall();
		}
		elsif ( $Target eq "form" ) {
			GenerateForm();
		}
                elsif ( $NodeID and $EditFields ) {
#			GenerateForm();
			GenerateEdit($NodeID);
                }
                elsif ( $NodeID and $ProcessChanges ) {  
                       ProcessChanges($NodeID);
#                       GenerateEdit($NodeID);
                }
                elsif ( $NodeID ) {
                        GenerateSummary($NodeID);
                }
		else {
			# 
			# How did we get here?
			# 
		}

	}
	else {  # No parameters passed, so return Form
		GenerateForm();
	}

	return;
}


########################## Generate output  ########################## 

# 
# This is the main sysquery form
# 

sub GenerateForm
{
	GenerateHeader("System Summary - Query");

	print <<END_TEXT;

<h1>System Summaries</h1>
<table border="5">

END_TEXT

	# this is a little more modular...
	SearchForSystems();
	AllowShowAll();

	print <<END_TEXT;

</table>

END_TEXT

	GenerateFooter();

	return;
}


# 
# This function will generate one or more rows in a
# pre-defined table
# 

sub SearchForSystems
{
	print <<END_TEXT;

<tr border=0>
	<td>
		<center><strong>Choose a Subset of systems to display</strong></center><p>
		<table>
			<tr>
					<form method=get action="$WebServerUrl/cgi-bin/$CGIScriptName">
					<input name="target" value="showall" type="hidden">
				<td>
					<strong>System Name</strong>
				</td>
				<td>
       					<input name=partial_name size=60><BR>
				</td></tr>
			<tr>	<td>
					<strong>Location</strong>
				</td>

				<td>

END_TEXT

	GenerateFormList("location", "nodes");

	print <<END_TEXT;

				</td>

				<td>
					<strong>Operating System</strong>
				</td>

				<td>

END_TEXT

	GenerateFormList("osname", "nodes");

	print <<END_TEXT;

				</td>

				<td rowspan="2" align="center">
					&nbsp;&nbsp;&nbsp;&nbsp;<input type=submit value=\Search\>
				</td>

			</tr>

			<tr>
				<td>
					<strong>Vendor</strong>
				</td>

				<td>

END_TEXT

	GenerateFormList("osvendor", "nodes");

	print <<END_TEXT;

				</td>

				<td>
					<strong>OS Version</strong>
				</td>

				<td>

END_TEXT

	GenerateFormList("osversion", "nodes");

	print <<END_TEXT;

				</td>

			</tr>
			<tr>
                                <td> 
                                        <strong>Software</strong>
                                </td>
                                <td>    
                                        <input name=partial_soft size=60><BR>
                                </td>
			</tr>
<tr><td><strong>Department</strong></td><td>

END_TEXT

        GenerateLinkedFormList("dept_id", "department", "departments");
        
        print <<END_TEXT;
           

</td></tr>
					</form>
		</table>


END_TEXT

	return;
}


# The following function queries for distinct values in the database.
# These distinct values form the data for pull down menus.

sub GenerateFormList
{	my $FormListName = shift;
	my $DatabaseName = shift;

        my $q3 = "SELECT DISTINCT $FormListName from $DatabaseName";
        my $in3 = $dbh->prepare($q3);
        $in3->execute;
        my $list_listing = $in3->fetchall_arrayref;


        print "<select name=\"$FormListName\">\n";
        # we'll always have an "All"
        print "<option value=\"\">All</option>\n";
        
        foreach my $row (@$list_listing) {
           my ($choices_options) = @$row;
	   print "<option value=\"$choices_options\">$choices_options</option>\n";
	}

	print "</select>\n";

	return;
}

sub GenerateLinkedFormList
{       my $IDColumnName = shift;
        my $NeedColumnName = shift;
        my $DatabaseName = shift;
        my $SelectedOptionName = shift;
                                
        my $q3 = "SELECT $IDColumnName, $NeedColumnName from $DatabaseName";
        my $in3 = $dbh->prepare($q3);
        $in3->execute;
        my $list_listing = $in3->fetchall_arrayref;
                
                        
        print "<select name=\"$IDColumnName\">\n";
        # we'll always have an "All"    
        print "<option value=\"\">All</option>\n";
        
        foreach my $row (@$list_listing) {
           my ($choices_value, $choices_options) = @$row;
	   if ($SelectedOptionName ne $choices_value) {
              print "<option value=\"$choices_value\">$choices_options</option>\n";
           } else  {
              print "<option selected value=\"$choices_value\">$choices_options</option>\n";
           }
        }
        print "</select>\n";

        return;
}



# 
# This function will generate one or more rows in a
# pre-defined table
# 

sub AllowShowAll
{
	print <<END_TEXT;

<tr border=0>
	<td>
		<center>
		<strong>Or, show all systems for which summaries exist</strong>
		<form method=get action="$WebServerUrl/cgi-bin/$CGIScriptName">
		<input name="target" value="showall" type="hidden">
		<input type=submit value="Show All">
		</form>
		</center>
	</td>
</tr>

END_TEXT

	return;
}


sub GenerateShowall
{
	GenerateHeader("System Summary - Show All Systems Which Have Summaries");

	print <<END_TEXT;

<h1>Summaries exist for the following systems:</h1>
<p>

END_TEXT

           @build_where = ();
              my $PartialName_quoted = $dbh->quote ("%".$PartialName."%");
              push @build_where, "fqdn LIKE $PartialName_quoted" if $PartialName;
              my $ServerOS_quoted = $dbh->quote ($ServerOS);
              push @build_where, "osname = $ServerOS_quoted" if $ServerOS;
              my $Vendor_quoted = $dbh->quote ($Vendor);
              push @build_where, "osvendor = $Vendor_quoted" if $Vendor;
              my $Version_quoted = $dbh->quote ($Version);
              push @build_where, "osversion = $Version_quoted" if $Version;
              my $Location_quoted = $dbh->quote ($Location);
              push @build_where, "location = $Location_quoted" if $Location;
              my $PartialSoft_quoted = $dbh->quote ("%".$PartialSoft."%");
              push @build_where, "software_name LIKE $PartialSoft_quoted and nodes.node_id = software_installed.node_id and software_installed.software_id = software.software_id" if $PartialSoft;
              push @build_databases, ", software_installed, software" if $PartialSoft;


              if (@build_where eq 6) {
                 $where_clause = "WHERE @build_where[0] and @build_where[1] and @build_where[2] and @build_where[3] and @build_where[4] and @build_where[5]";
              } elsif (@build_where eq 5) {
                 $where_clause = "WHERE @build_where[0] and @build_where[1] and @build_where[2] and @build_where[3] and @build_where[4]";
              } elsif (@build_where eq 4) {
                 $where_clause = "WHERE @build_where[0] and @build_where[1] and @build_where[2] and @build_where[3]";
              } elsif (@build_where eq 3) {
                 $where_clause = "WHERE @build_where[0] and @build_where[1] and @build_where[2]";
              } elsif (@build_where eq 2) {
                 $where_clause = "WHERE @build_where[0] and @build_where[1]";                    
              } elsif (@build_where eq 1) {
                 $where_clause = "WHERE @build_where[0]";                    
              } else {
                 $where_clause = "";
              }

              if (@build_databases eq 1) {
                 $extra_db_clause = " @build_databases[0]";
              } else {
                 $extra_db_clause = "";
              }

           my $q1 = "SELECT DISTINCT nodes.node_id, fqdn, osname, lastupdate from nodes $extra_db_clause $where_clause ORDER BY fqdn";   
           my $in = $dbh->prepare($q1);
           $in->execute;
           my $host_array_listing = $in->fetchall_arrayref();

	# emit table headings
	print <<END_TEXT;

<table border="1">
<tr>
<th>DB #</th>
<th>Hostname</th>
<th>Operating System</th>
<th>Last Update</th>
</tr>

END_TEXT

	foreach my $row (@$host_array_listing) {

           my ($node_id, $fqdn, $osname, $lastupdate) = @$row;

		print "<tr><td>$node_id</td>";
		print "<td><A HREF=$WebServerUrl/cgi-bin/$CGIScriptName?nodeid=$node_id>$fqdn</A></td>\n";
		print "<td>$osname</td>\n";
		print "<td>$lastupdate</td>\n";
		print "<tr>\n";
	}

	if (! defined(@$host_array_listing) ) {

	print <<END_TEXT;

<tr>
<td>
</td>
</tr>

</table>

<h1>No machines found with those parameters</h1>
<p>The query submitted was:<BR> <b>$q1</b></p>
<p>Please use your browser's back button or for a list of all existing system summaries, click on this link
<br>\n<A HREF=\"$WebServerUrl/cgi-bin/$CGIScriptName?target=showall\">Show All Summaries</A></p><hr>

END_TEXT
	}
	else {
		print "</table>\n";
	}

	GenerateFooter();

	return;
}


sub GenerateSummary
{
        my $what_nodeid = shift;
        my $nodeid_quoted = $dbh->quote ($what_nodeid);
        my $query_nodeid = "SELECT * from nodes WHERE node_id = $nodeid_quoted";   
        my $summary_output = $dbh->prepare($query_nodeid);
        $summary_output->execute;
        %summary_info = %{$summary_output->fetchrow_hashref};

	GenerateHeader("System Summary - $summary_info{fqdn} Summary");

	print <<END_TEXT;

<h1>System Summary for $summary_info{fqdn} :</h1>
<p>
END_TEXT

        print "<p></p><table border=\"2\" width=\"$TableWidth\">\n";
        foreach (keys %summary_info) {
           print "<tr><td><strong>$_ </strong></td><td>$summary_info{$_} </td></tr> \n";
        }
        print "</table>\n";
        if ( $SoftwareList eq "all") {
           my $query_allsoftware = "select software.software_name, software.software_desc from nodes, software, software_installed WHERE nodes.node_id = $nodeid_quoted and nodes.node_id = software_installed.node_id and software_installed.software_id = software.software_id";
           my $software_output = $dbh->prepare($query_allsoftware);
           $software_output->execute;
	   my $software_array_ref = $software_output->fetchall_arrayref();
           print "<p></p><table border=\"2\" width=\"$TableWidth\">\n";
           foreach my $row (@$software_array_ref) {
	      my ($sname,$sdesc) = @$row;
              print "<tr><td><strong>$sname </strong></td><td>$sdesc </td></tr> \n";
           }
           print "</table>\n";
	} elsif ( $ServicesList eq "all")  {
           my $query_allservices = "select service_name, service_desc from nodes, services, services_used WHERE nodes.node_id = $nodeid_quoted and nodes.node_id = services_used.node_id and services_used.service_id = services.service_id";
           my $services_output = $dbh->prepare($query_allservices);
           $services_output->execute;
           my $services_array_ref = $services_output->fetchall_arrayref();
           print "<p></p><table border=\"2\" width=\"$TableWidth\">\n";
           foreach my $row (@$services_array_ref) {
              my ($sname,$sdesc) = @$row;
              print "<tr><td><strong>$sname </strong></td><td>$sdesc </td></tr> \n";
           }
           print "</table>\n";
        } elsif ( $DisksList eq "all")  {
           my $query_alldisks = "select disk_id, disk_description from disks WHERE node_id = $nodeid_quoted";
           my $disks_output = $dbh->prepare($query_alldisks);
           $disks_output->execute;
           my $disks_array_ref = $disks_output->fetchall_arrayref();
           print "<p></p><table border=\"2\" width=\"$TableWidth\">\n";
           foreach my $row (@$disks_array_ref) {
              my ($sname,$sdesc) = @$row;
              print "<tr><td><strong>$sname </strong></td><td>$sdesc </td></tr> \n";
           }
           print "</table>\n";   
       	} else {
            print "<p></p><table border=\"0\" width=\"$TableWidth\">\n";
            print "<tr>\n";
            print "<td><A HREF=\"$WebServerUrl/cgi-bin/$CGIScriptName?nodeid=$what_nodeid&software_list=all\">Show All Software</A></td>\n";
	    print "<td><A HREF=\"$WebServerUrl/cgi-bin/$CGIScriptName?nodeid=$what_nodeid&services_list=all\">Show All Services</A></td>\n";
	    print "<td><A HREF=\"$WebServerUrl/cgi-bin/$CGIScriptName?nodeid=$what_nodeid&disks_list=all\">Show All Disks</A></td>\n";
	    print "<td><A HREF=\"$WebServerUrl/cgi-bin/$CGIScriptName?nodeid=$what_nodeid&editfields=all\">Edit Selected Fields</A></td>\n";
            print "</tr></table>\n";
	}
	GenerateFooter();

	return;
}

# 
# This function will output the start of the <head>
# section including the <title>.  It will then look
# for an external file that will complete the <head>
# section and begin the <body> section so that the
# user can customize the appearance of the web pages
# for their site.
# 
# If the external file doesn't exist, a minimal set
# of lines closing the <head> section and opening the
# <body> section will be emitted.
# 
# See the Install documentation on the web site for
# examples of setting up the external file.
# 

sub GenerateHeader
{	my $Title = shift;

	print "Content-type: text/html\n\n";

	print "<html>\n";
	print "<head>\n";
	print "<title>$Title</title>\n";

	my $PathToHeaderFile = "$PathToSyssummDirectory/$HeaderFileName";

	if ( -f $PathToHeaderFile ) {
		open HEADER, "<$PathToHeaderFile" or
			die "Couldn't open header file $PathToHeaderFile: $!";
		my @HeaderInfo = <HEADER>;
		close(HEADER);
		print @HeaderInfo;
	}
	else {
		# no external file, emit miminal lines to open <body>
		print "<!-- Built-in header information -->\n\n";
		print "</head>\n\n";
		print "<body>\n";
	}

	return;
}


sub GenerateFooter
{
	print "</body>\n";
	print "<br>\n";			# hard-code vertical spacing
	print "<p align=center>\n";
	print "Send comments about this web page to <a href=\"mailto:$WebmasterEmailAddress\">$Webmaster</a>\n";
	print "</p>\n";
	print "</html>\n";

	return;
}

sub StartTable
{	my @TableArgs = @_;

	# process "command line args"
	foreach $Arg (@TableArgs) {
		my ( $Tag, $Value ) = split(/=/, $Arg, 2);
		if ( $Tag eq "columns" ) {
			$ThisManyColumns = $Value;	# global
		}
		elsif ( $Tag eq "title" ) {
			print "<h2>$Value</h2>\n";
		}
		else {
			die "Error processing table command arg at line $ThisLineNumber";
		}
	}

	# so, what are you waiting for?
	print "<table border=\"2\" width=\"$TableWidth\">\n";

	return;
}


sub EndTable
{
	print "</table>\n";

	return;
}

########################## Utility  ########################## 

# borrowed from ugsearch.pl (DESC)
# originally derived from _CGI Programming_, Gundavaram, pp 66-67

sub ReturnError
{	my $Status = shift;
	my $Keyword = shift;
	my $Message = shift;

	print "Content-type: text/html\n";
	print "Status: $Status $Keyword\n\n";

	print "<title>sysquery.pl - Unexpected error</title>\n";
	print "<h1> $Keyword </h1>\n";
	print "$Message<br>\n";
	print "Please contact <a href=mailto:\"$WebmasterEmailAddress\">$Webmaster</a> for more information.\n\n";

	exit 1
}

sub GenerateEdit
{
        my $what_nodeid = shift;
        my $nodeid_quoted = $dbh->quote ($what_nodeid);
        my $query_nodeid = "SELECT fqdn, location, building, room, organization, dept_id from nodes WHERE node_id = $nodeid_quoted";
        my $summary_output = $dbh->prepare($query_nodeid);
        $summary_output->execute;
        %summary_info = %{$summary_output->fetchrow_hashref};

        GenerateHeader("System Summary - $summary_info{fqdn} Summary");

        print <<END_TEXT;

<h1>Editing Information for $summary_info{fqdn} :</h1>
<p>
END_TEXT

        print "<p></p><table border=\"2\" width=\"$TableWidth\"><form method=get action=$WebServerUrl/cgi-bin/$CGIScriptName>\n";
	print "<input name=nodeid value=$what_nodeid type=hidden>\n";
	print "<input name=made_changes value=yes type=hidden>\n";
        print "<tr><td><strong>Host Name </strong></td><td>$summary_info{fqdn} </td></tr> \n";
        print "<tr><td><strong>Location </strong></td><td><input name=c_location size=60 value='$summary_info{location}'> </td></tr> \n";
        print "<tr><td><strong>Building </strong></td><td><input name=c_building size=60 value='$summary_info{building}'> </td></tr> \n";
        print "<tr><td><strong>Room </strong></td><td><input name=c_room size=60 value='$summary_info{room}'> </td></tr> \n";
        print "<tr><td><strong>Organization </strong></td><td><input name=c_organization size=60 value='$summary_info{organization}'> </td></tr> \n";
        print "<tr><td><strong>Department </strong></td><td> \n";
        GenerateLinkedFormList("dept_id", "department", "departments", $summary_info{dept_id});
        print "</td></tr> \n";
        print "<tr><td><input type=submit value=\Commit\></td><td><INPUT TYPE=reset></td></tr> \n";
        print "</form></table>\n";

        GenerateFooter();

        return;
}

sub ProcessChanges
{
        my $what_nodeid = shift;
        my $nodeid_quoted = $dbh->quote ($what_nodeid);
        my $location_quoted = $dbh->quote ($ChangedLocation);
        my $building_quoted = $dbh->quote ($ChangedBuilding);
        my $room_quoted = $dbh->quote ($ChangedRoom);
        my $org_quoted = $dbh->quote ($ChangedOrganization);
        my $query_update = "UPDATE nodes set location=$location_quoted,building=$building_quoted,room=$room_quoted,organization=$org_quoted WHERE node_id = $nodeid_quoted";
        my $aaaa = $dbh->prepare($query_update);
        $aaaa->execute; 
#        %summary_info = %{$summary_output->fetchrow_hashref};
        
        GenerateHeader("Changes complete for - $summary_info{fqdn} Summary");
        
        print <<END_TEXT;
        
<h2>Changes made for $summary_info{fqdn} :</h2>
<p>     
END_TEXT

        print "<A HREF=$WebServerUrl/cgi-bin/$CGIScriptName?nodeid=$what_nodeid>Return to summary</A><BR>\n";
        print "<A HREF=$WebServerUrl/cgi-bin/$CGIScriptName?target=form>Return to main page</A>\n";

        GenerateFooter();
        
        return;
}  



##############################################################################
# Mainline
##############################################################################

# don't buffer the output
STDOUT->autoflush(1);

ParseInputFromWebServer();

##############################################################################
# We're done!
##############################################################################
exit(0);
