#!/appl/perl/perl5.003/bin/perl

# average_report.cgi
# By:  	Jim Speights
# Date:	4/7/99

# Description:	  Program to generate an html page containing a license report for the date
#		  specified in the PATH_INFO environment variable.  The date should be in the
#		  following format: YYYY-MM-DD 
#		  The program will generate a usage report with data obtained from the MySql database
#		  running on gilligan.  This database is populated by the following programs, which
#		  use ptcmonitor and ptcstatus and are run by cron on gilligan:
#		
#		  /usr/people/gilligan/speights/scripts/lic_mon3/pop_LIC_MONITOR.pl
#		  /usr/people/gilligan/speights/scripts/lic_mon3/pop_UTILIZATION.pl
#
#		  These programs populate the LIC_MONITOR and UTILIZATION tables in the PTC_MONITOR
#		  database.
#
# 
#
#		 
# --------------------------------------------------------------------------------------------------
# Revision History
#
# 4/7/99	J. Speights	Initial Release
#
# 4/8/99	J. Speights	Removed the following modules:
#
#				"Pro/INTERFACE", "Pro/SHEETMETAL", "Pro/ECAD", 
#				"Pro/SURFACE", "Pro/PROCESS_ASM"
#
# 6/4/99	J. Speights	Reordered modules, reformatted report, added hourly sessions found
#				and peak seats found
#
# --------------------------------------------------------------------------------------------------


require '../cgi-lib.pl';  
use lib '/appl/enterprise/cgi-bin/CommonSK';
use OracleSetup;
use Setup;
 

# global variables
$start_hour = 7;	# start hour for report
$end_hour = 18;   	# end hour for report, will get the 6:10 PM data point.     

MAIN:
{
   
  my( @modules ) = (	"PROE_19891", 
			"PDM_20102", "ILINK_CLIENT_715330",

			"FLYTHRU_715331", "Pro/MESH", 
			"Pro/CABLING", "Pro/DIAGRAM", "Pro/HARNESS-MFG",

			"MECINTERFCE_20175", "MECSTRUCUI_20175",
			"MECSTRUCENG_20175", "MECMOTUI_20175", "MECMOTENG_20175",
			"MECTHERMUI_20175", "MECTHERMENG_20175", "Pro/MEC-Vibration", "Pro/MEC-Cus-Loads"  );

  
  my ($x, $j, $i, @averages, @peaks, @peak_seats, @seats, $count, @date, @charts, @hours_used, $period_hours );
  
  my $start_date="";
  my $end_date="";
  my $seats = 0;  
  
   

  # make database connection
  my ( $sth, $dbh, $row, $rc, $error );
  my @return_vals;

  $lda = &oracle_login('d01', 'pro_admin', 'allparts');
  eval 'use Oraperl; 1' || die $@ if $] = 5;
   
  # If a report date is passed in on the query string use it, else look for a posted report
  # date.  If neither are found, or if the value used is invalid, print an error message

   
  &ReadParse( *input );

  
  
  if( defined( $input{'START_YEAR'})  ) 
  { 
    $start_date = $input{'START_YEAR'}."-".$input{'START_MONTH'}."-".$input{'START_DAY'}; 
    $end_date = $input{'END_YEAR'}."-".$input{'END_MONTH'}."-".$input{'END_DAY'};  
  }
   
    

  if( 	$start_date !~ /[0-9][0-9][0-9][0-9]\-[0-9][0-9]\-[0-9][0-9]/ || 
	$end_date !~ /[0-9][0-9][0-9][0-9]\-[0-9][0-9]\-[0-9][0-9]/  )
  {
#    &CgiDie("An invalid report date has been specified. Unable to generate report.\n");
  } 
   


  


  # get all MySql data points for the requested day
$csr = &ora_open($lda, "select to_char(RECORD_DATE, 'YYYY-MM-DD HH24:MI:SS'), MODULE1, LIC_IN_USE / (LIC_IN_USE + LIC_FREE), (LIC_IN_USE + LIC_FREE), LIC_IN_USE 
from UTILIZATION 
where 
RECORD_DATE between to_date(\'$start_date 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\') and to_date(\'$end_date 23:59:59\', \'YYYY-MM-DD HH24:MI:SS\')
and 
to_number(to_char(RECORD_DATE, 'HH24')) between $start_hour and $end_hour 
and 
to_number(to_char(RECORD_DATE, 'W')) between 0 and 4") || &oracle_error(2);

#  $sth = $dbh->prepare("select RECORD_DATE, MODULE, LIC_IN_USE / (LIC_IN_USE + LIC_FREE), (LIC_IN_USE + LIC_FREE), LIC_IN_USE 
#			      from UTILIZATION 
#			      where RECORD_DATE 
#			      between \'$start_date 00:00:00\' and \'$end_date 23:59:59\' and
#			      hour( RECORD_DATE ) between $start_hour and $end_hour and
#			      weekday( RECORD_DATE ) between 0 and 4");
  $no_row = 0; 
  while (($out_recdate, $out_module1, $out_cal1, $out_cal2, $out_cal3) = &ora_fetch($csr))
  {
  $no_row += 1;
  push(@out_recdate1, $out_recdate);
  push(@out_module11, $out_module1);
  push(@out_cal11, $out_cal1);
  push(@out_cal21, $out_cal2);
  push(@out_cal31, $out_cal3);
  }

  $i=0;
  # get average and peak values for each module
  foreach $x ( @modules )
  {
    
      # initialize $count and averages
      $count = 0;
      $averages[$i] = 0;
      $peaks[$i] = 0;
      $hours_used[$i] = 0;
      $peak_seats[$i] = 0;

      # iterate through data array for all points for this module
      for ($j=0; $j<=$no_row; $j++ ) 
      {

    	# Add all data points for this module, and keep track of the peak 
	 
        if(  $out_module11[$j] =~ /$x/  )
   	{
	  $averages[$i] +=  $out_cal11[$j];
	  $seats[$i] = $out_cal21[$j];
	  $count++;

          # we found another data point, so add one to the hours_used for every seat in use
  	  # at this time
          $hours_used[$i] +=  $out_cal31[$j];

          # if the current peak for this module is less than the percentage we are 
	  # looking at, swap them 
          if( $peaks[$i] < $out_cal11[$j] )
	  { 
	    $peaks[$i] = $out_cal11[$j]; 
	    $peak_seats[$i] = int( $peaks[$i] * $seats[$i] );
	  
	  }
	  
	}

      }

      if( $count != 0 )
      {
        $averages[$i] = $averages[$i] / ( 1.0 * $count) ;
      }
     
      $i++;

      
       

  }
   
   

  # print header for html page, then generate bar chart
  $period_hours =  $count;
  &print_html_header( $start_date, $end_date, $period_hours );  
  &html_bar_chart( "Average and Peak Utilization <BR> Between 7:00 AM to 6:00 PM <BR>From $start_date to $end_date", 
			\@averages, \@peaks, \@modules, \@seats, \@hours_used, \@peak_seats );
   

 &ora_close($csr); 
  exit(0); 
        
        
   
 
}

   




sub print_html_header
{

my $start_date = $_[0];
my $end_date  = $_[1];
my $period_hours = $_[2];

print <<EOT;
Content-Type: text/html\n\n
 
<HTML>
<HEAD>
<TITLE>PTC Average and Peak License Utilization For $start_date to $end_date</TITLE>
</HEAD>
  
<BODY BGCOLOR=#ffffff LINK=blue ALINK=#OO66OO VLINK=#880000>
<BR>
<FONT SIZE=+1><B>PTC Average and Peak License Utilization </B>
		<BR> Monday-Friday, 7:00 AM to 6:00 PM <BR>$start_date to $end_date
		<BR> <I>$period_hours available work hours for this period</I></FONT>       
<BR><BR>
 

   
 


EOT


}










# sub html_bar_chart
# Description:  Creates an html bar chart based on the contents of below arrays.
#		  
#
# Notes:	The number of charts to be created is equal to the number of
#		titles in the @chart_titles array.
#
#		The html page created uses redbar.jpg and whitebar.jpg for
#		the graphics source files for the actual bar on each bar chart.
#		These files must be available in order to view the html file.

sub html_bar_chart
{
  my( $chart ) = $_[0];
  my( $averages ) = $_[1];
  my( $peaks ) =  $_[2];
  my( $labels ) =  $_[3];
  my( $seats ) =  $_[4];
  my( $hours_used ) = $_[5];
  my( $peak_seats ) = $_[6];
  my( @avg_percentages, @peak_percentages );
  
  my( $redbar ) =   "\'http://gilligan.stortek.com/images/redbar.jpg\'";
  my( $whitebar ) = "\'http://gilligan.stortek.com/images/whitebar.jpg\'"; 
  my( $bluebar ) = "\'http://gilligan.stortek.com/images/bluebar.jpg\'"; 
   				 
  my( $vscale )= 165;  			# Scale, used to multiply the percentage to get bar chart height
  my( $td_bar_width) = $vscale + 65;	# Width of data cell in table where the bar goes
  my( $chart_height )= $vscale + 10;
   
  my( $i, $x, $y, @temp );  
  my( $xtitle ) = "<TD width=15> &nbsp  Percentage</TD>";	 
   
    
  # print chart header
  print <<EOT;
    <BR> 

    <TABLE WIDTH=650 BORDER=1 cellpadding=5 cellspacing=0>  
      <TR>
      	<TD WIDTH=15> &nbsp </TD>
    	<TD WIDTH=$td_bar_width ><BR>  <TABLE>	<TR><TD><IMG WIDTH=50 HEIGHT=10 BORDER=0 SRC=$redbar > &nbsp Average Utilization</TD></TR>
					<TR><TD><IMG WIDTH=50 HEIGHT=10 BORDER=0 SRC=$bluebar > &nbsp Peak Utilization</TD></TR>
				</TABLE>
	</TD>
      </TR>
         
EOT



  # create a label and bar for each average
       
  for ($i=0; $i< $#$averages; $i++)
  {
    $avg_percentages[$i] = int( 100 * $$averages[$i] );
    $peak_percentages[$i] = int( 100 * $$peaks[$i] );

    $$averages[$i] = int( $$averages[$i]*$vscale + 1 );
    $$peaks[$i] = int( $$peaks[$i]*$vscale + 1 );


    # print section headings
    if( $i == 0 )
    {
      print  <<EOT; 
	     <TR> 
			<TD  COLSPAN=2 WIDTH=250 ALIGN=LEFT BGCOLOR=SILVER ><I>Pro/Engineer, Pro/PDM, Pro/Intralink</I></TD>
	     </TR>
EOT
    }


    # print section headings
    if( $i == 3 )
    {
      print  <<EOT; 
	     <TR> 
			<TD  COLSPAN=2 WIDTH=250 ALIGN=LEFT BGCOLOR=SILVER ><I>Miscellaneous Floating Modules</I></TD>
	     </TR>
EOT
    }


    # print section headings
    if( $i == 8 )
    {
      print  <<EOT; 
	     <TR> 
			<TD  COLSPAN=2 WIDTH=250 ALIGN=LEFT BGCOLOR=SILVER ><I>Mechanica Floating Modules</I></TD>
	     </TR>
EOT
    }
      



    # print bar and label

    print  <<EOT; 
	     <TR> 
			<TD WIDTH=250 ALIGN=LEFT>$$labels[$i] ( $$seats[$i] seats )</TD>
		  	<TD>

			<TABLE>
			<TR>
			  <TD WIDTH=$td_bar_width ALIGN=LEFT><IMG WIDTH=$$averages[$i] HEIGHT=10 BORDER=0 SRC=$redbar >
						   &nbsp $avg_percentages[$i]% </TD>
			  <TD ALIGN=LEFT> <I>$$hours_used[$i] hourly sessions</I></TD>
			</TR>
			<TR>
			  <TD WIDTH=$td_bar_width ALIGN=LEFT><IMG WIDTH=$$peaks[$i] HEIGHT=10 BORDER=0 SRC=$bluebar >
						   &nbsp $peak_percentages[$i]% </TD>
			  <TD> <I>$$peak_seats[$i] seats</I></TD>
			</TR>
			</TABLE>

			</TD>
	     </TR>
EOT
     
  }
   
  
   		
   print( "</TR></TD></TABLE></TD></TR>
    	   <TR> <TD width=15> &nbsp </TD> <TD> &nbsp&nbsp&nbsp&nbsp&nbsp ");
 


   
  print("</TABLE><BR><I>*Hourly sessions are measured every hour, on the hour, ");
  print("and are equal to the number of active sessions at that time.</I></BODY></HTML>"); 
   

} # end sub html_bar_chart














# sub html_auth_report
# Description:  Creates html text suitable for insertion into an existing html file.
#   		The text contains a listing of nodes appearing in the global array
#		@todays_list that do not appear in the file specified by the first
#		argument to this function auth_nodes_file. 
#
# Arguments:	$auth_nodes_file - contains a list of authorized nodes.  The first
#		part of the fully qualified host name should be used only.
#
# Returns:	A single string containing html formatted text suitable for
#		insertion directly into an html file.  The text will contain
#		a list of unauthorized nodes.

sub html_auth_report
{
  my( @todays_list, $x, $report_text, $return_string, @nodes, @temp, $nodename, @unauthorized);
  my( $auth_nodes_file ) = $_[0];
  local( *INFILE ) ;

  open( INFILE, "<$auth_nodes_file " ) || die "can't open AUTHORIZED NODES file: $!\n"; 
  

  # read in list of authorized nodes
 
  @nodes = <INFILE>;
  close( INFILE );

  #check to see that each node on todays_list is authorized
  foreach $x (@todays_list)
  {
    @temp = split(" ", $x);

    # the authorized nodes list has the first part of the hostname only, so
    # get rid of any periods in the names before comparing.

    if( grep( /\./, $temp[1] ) == 1 )
    {
      @temp= split( '.', $temp[1] );
      $nodename = $temp[0];
    }
    else
    {
      $nodename = $temp[1];
    }

    if( grep( /$nodename/i, @nodes ) == 0 )
    {
        
      if( $#unauthorized < 0 )
      { 
        push(@unauthorized, "<BR><BR>The following unauthorized users were found:<BR><BR>Pro SN | Node | User | Start Date | Start Time | End Date | End Time<BR><BR>"); 
        push(@unauthorized,"<BR>", $x );
      }
      else
      {
        push(@unauthorized,"<BR>", $x );
      } 

    }
 

  } # end foreach $x

  
  if( $#unauthorized == 0 )
  { 
    push(@unauthorized, "<BR>No unauthorized users reported."); 
  }


  # create report text string, starting with a title for the section
  $report_text = "<BR><BR><BR><BR> <HR><P><B>Authorization Report</B></P><BR><BR>\n";

  # append unauthorized array line by line to report_text string
  foreach $x (@unauthorized)
  {
    $report_text .= $x." \n";
  }

  # return test string
  $return_string = $report_text;


} #end sub html_auth_report