#!/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
Between 7:00 AM to 6:00 PM
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 < PTC Average and Peak License Utilization For $start_date to $end_date
PTC Average and Peak License Utilization
Monday-Friday, 7:00 AM to 6:00 PM
$start_date to $end_date
$period_hours available work hours for this period


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 ) = "   Percentage"; # print chart header print < 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 } # print section headings if( $i == 3 ) { print < EOT } # print section headings if( $i == 8 ) { print < EOT } # print bar and label print < EOT } print( "
 
  Average Utilization
  Peak Utilization
Pro/Engineer, Pro/PDM, Pro/Intralink
Miscellaneous Floating Modules
Mechanica Floating Modules
$$labels[$i] ( $$seats[$i] seats )
  $avg_percentages[$i]% $$hours_used[$i] hourly sessions
  $peak_percentages[$i]% $$peak_seats[$i] seats
        "); print("
*Hourly sessions are measured every hour, on the hour, "); print("and are equal to the number of active sessions at that time."); } # 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 = ; 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, "

The following unauthorized users were found:

Pro SN | Node | User | Start Date | Start Time | End Date | End Time

"); push(@unauthorized,"
", $x ); } else { push(@unauthorized,"
", $x ); } } } # end foreach $x if( $#unauthorized == 0 ) { push(@unauthorized, "
No unauthorized users reported."); } # create report text string, starting with a title for the section $report_text = "




Authorization Report



\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