#!/usr/local/bin/perl
#======================================================================
# Program: This program retrieves data from an Oracle database
#          and create a line chart image for the web.
#
# Author:  Ron Bueler
#
# Date:    April 22, 2001
#
# Revision History:
# 
#
#======================================================================
use strict;
use diagnostics;
use CGI::Carp qw(fatalsToBrowser);
use DBI;				# Oracle DBI module
use GD::Graph::Data;			# Gathering data easily
use GD::Graph::lines;			# Generating the graphs 
 
$ENV{ORACLE_HOME}='/ora01/app/oracle/product/8.1.6';
$ENV{LD_LIBRARY_PATH}='/ora01/app/oracle/product/8.1.6/lib';

select (STDOUT); $|=1;
 
my ($DEBUG)     = 0;
my ($dbh)       = "";
my ($sth)       = "";
my ($sql)       = "";
my ($rc)        = "";
my ($rv)        = "";
my ($host)      = "pm02rg";
my ($duration)  = "1";
my ($now)       = ` /sbin/date +"%Y-%m-%d %H:%M" `;
my ($yesterday) = "";
my ($count)     = 0;
my (@row)       = ();
my ($data)      = GD::Graph::Data->new();
my ($graph)     = GD::Graph::lines->new(900, 300);

my ($size)      = 0;
my ($pair)      = 0;
my ($form_info) = 0;
my ($key)       = 0;
my ($value)     = 0;
my (%parameters);

{
    #==================================================
    # Take care of some house-keeping stuff
    #--------------------------------------------------

    chomp ($now);
    $yesterday = &calc_yesterday ($now);

    #==================================================
    # Check the input values
    #--------------------------------------------------
 
    $size = $ENV{'CONTENT_LENGTH'};
    read (STDIN, $form_info, $size);
    
#   print "Content-type: text/plain\n\n";

    open (LOG, ">>/var/smart/cgi-bin/log.txt") || warn "no log file";
    foreach $pair (split (/&/, $form_info)) {
    
        ($key, $value) = split (/=/, $pair);

        $key   =~ s/%([\dA-Fa-f][\dA-Fa-f])/pack ("C", hex ($1))/eg;
        $value =~ s/%([\dA-Fa-f][\dA-Fa-f])/pack ("C", hex ($1))/eg;
   
        print LOG ("key = |" . $key . "|, value = |" . $value . "|\n");

        $parameters{$key} = $value;
    }
    close(LOG);

    if (defined($parameters{'host'})) {
        $host = $parameters{'host'};
    }
 
    #==================================================
    # First, connect to the database and get the data
    #--------------------------------------------------

    $dbh = DBI->connect("dbi:Oracle:smart.rst.cw.net", "webdb", "webdb" )
        || die "Can't connect to Oracle database: $DBI::errstr\n";
 
    $sql = <<ENDSQL;
        SELECT
            TO_CHAR(dtg, 'yyyy-mm-dd hh24:mi'),
            utilization
        FROM
            smart.cpus
        WHERE
            TO_CHAR(dtg, 'yyyy-mm-dd hh24:mi') >= '$yesterday'
        AND host like '$host'
        ORDER BY
            dtg
ENDSQL

    $sth = $dbh->prepare ($sql) 
        || die "Can't prepare statement: $DBI::errstr\n";

    $rv = $sth->execute
        || die "Can execute statement: $DBI::errstr\n";

    while (@row = $sth->fetchrow_array) {
        $data->add_point(@row);
    }

    $sth->finish
        || warn "Finish statement failed: $DBI::errstr\n";

    $dbh->disconnect 
        || warn "Disconnection failed: $DBI::errstr\n";
 
    #====================================================
    # Now that we have the data, create a chart from it
    #----------------------------------------------------

    print STDOUT "Content-type: image/png\n\n";

    $graph->set( 
	x_label			=> 'All date/times are in GMT',
	x_label_position	=> 1/2,
	x_label_skip    	=> 12,
	x_labels_vertical	=> 1,
	y_label			=> 'Percent Utilized',
	y_min_value		=> 0,
	y_max_value		=> 100,
	title			=> 'CPU Utilization over the past 24 hours',
	transparent		=> 0,
	bgclr			=> 'lgray',
    )   || warn "Error setting graph attributes: $graph->error\n";

    $graph->set_legend($host)
        || warn "Error setting graph_legend attributes: $graph->error\n";

    $graph->plot($data);

    binmode STDOUT;
    print STDOUT $graph->gd->png;

    exit(0);
}

#======================================================================
#  Calculates yesterday's date
#----------------------------------------------------------------------

sub calc_yesterday {
    my ($today)   = @_;
    my ($year)    = substr ($today,  0, 4);
    my ($month)   = substr ($today,  5, 2);
    my ($day)     = substr ($today,  8, 2);
    my ($hours)   = substr ($today, 11, 2);
    my ($minutes) = substr ($today, 14, 2);

    if ($day ne "01") {

        $day = $day - 1;

    } else {

        if ($month eq "01") {

            $year  = $year - 1;
            $month = 12;
            $day   = 31;

        } elsif (($month eq "02") ||
                 ($month eq "04") ||
                 ($month eq "06") ||
                 ($month eq "08") ||
                 ($month eq "09") ||
                 ($month eq "11")) {

            $month = $month - 1;
            $day   = 31;

        } elsif (($month eq "05") ||
                 ($month eq "07") ||
                 ($month eq "10") ||
                 ($month eq "12")) {

            $month = $month - 1;
            $day   = 30;

        } elsif (($month eq "03") && ($year % 4 == 0)) {

            $month = $month - 1;
            $day   = 29;

        } elsif (($month eq "03") && ($year % 4 != 0)) {

            $month = $month - 1;
            $day   = 28;

        } else {

            printf ("WARNING: Coding error\n\n");
        }
    }

    if (length($day) < 2) {
        $day = "0" . $day;
    }

    if (length($month) < 2) {
        $month = "0" . $month;
    }

    if (length($hours) < 2) {
        $hours = "0" . $hours;
    }

    if (length($minutes) < 2) {
        $minutes = "0" . $minutes;
    }

    return $year . "-" . $month . "-" . $day . " " . $hours . ":" . $minutes;
}
#======================================================================
# end of /var/smart/cgi-bin/host_cpu.cgi
#======================================================================