#!/usr/bin/env perl

# Find the number of runs submitted per user in the given time period
# Sort by total number of runs, then by user/project
# $Id: nmi_usage_stats.pl,v 1.18 2008-03-31 22:26:17 bgietzel Exp $ 

use Getopt::Long;
use Cwd;
use Time::Local;
use Mail::Mailer;

use lib $ENV{'NMI_LIB'} || "/nmi/lib";
use NmiConf;
use NmiTools;
use NmiStats;

GetOptions('mailto=s'  => \$main::opt_mailto,
           'days=s'    => \$main::opt_days,
           'help'      => \$main::opt_help,
           'nmiconf=s' => \$main::opt_nmiconf);

## ---------------------------------------------
## Load NMI Configuration
## ---------------------------------------------
my %conf = NmiConf::init($main::opt_nmiconf);
use NmiDatabaseVariables;
require NmiDatabase;

## ---------------------------------------------
## Database Connection ( for infrastructure )
## ---------------------------------------------

my $DB_HOST = $conf{'DB_HOST'};
my $DB_PORT = $conf{'DB_PORT'};
my $DB_NAME = $conf{'DB_NAME'};
my $DB_READER_USER = "view_account";
my $DB_READER_PASS = "view_account71";

my $DB_NAME = "infrastructure";

my $DB_CONNECT_STR = "DBI:mysql:database=$DB_NAME;host=$DB_HOST";
$DB_CONNECT_STR .= ";port=$DB_PORT" if $DB_PORT;

my $days = 1;
my %results;
my @objs = ();


if (defined $main::opt_help) {
  print <<"END";
usage: $0 [--mailto=<addrList>] [--days=<days to include, defaults to 1>] [--help]
END
  exit 0;
}    

if (defined($opt_days)) {
  $days = $opt_days;
} 

if (defined($opt_mailto)) {
  $mailto = $opt_mailto;
} else {
  die "You must specify an address for mailto\n";
}

my $threshold_date = datetime_offset(0, 0, -7, -$days);

  # Fetch the results from the database.
  my $cmd = qq/SELECT user, project, runid from $NmiDatabaseVariables::DB_TABLE_RUN WHERE start > '$threshold_date'/;
  my $handle = NmiDatabase::execute( $cmd );
  while ( my $row = $handle->fetchrow_hashref() ) {
      my $user = $row->{'user'};
      my $project = $row->{'project'};
      my $runid = $row->{'runid'};
      $results{"$user,$project"} = $results{"$user,$project"} . ",$runid";
  }

  my $total_builds = 0, $total_tests = 0, $total_other = 0, $total_platform_jobs = 0;
  foreach my $user_project (keys %results) {

      my $builds = 0, $tests = 0, $other = 0, $platform_jobs = 0;
      my $run_type;

      my $runid_string = $results{"$user_project"}; 
      my @runids = split(",", $runid_string);
      shift(@runids); 

      foreach $current_runid (@runids) {
        chomp($current_runid);

        my $cmd2 = qq/SELECT distinct run_type,platform from $NmiDatabaseVariables::DB_TABLE_RUN, $NmiDatabaseVariables::DB_TABLE_TASK WHERE name="platform_job" AND $NmiDatabaseVariables::DB_TABLE_RUN.runid=$current_runid AND $NmiDatabaseVariables::DB_TABLE_TASK.runid=$current_runid/;
        my $handle = NmiDatabase::execute( $cmd2 );
        while ( my $row2 = $handle->fetchrow_hashref() ) {
          # count the # of platform jobs for this run
          $run_type = $row2->{'run_type'};
         
          if ($run_type eq "BUILD" || $run_type eq "build") { 
            $builds++;
          } elsif ($run_type eq "TEST" || $run_type eq "test") {
            $tests++;
          } else { 
            $other++;
          }
        }

        my $runid_string = $results{"$user_project"}; 
        my @runids = split(",", $runid_string);
        shift(@runids); 
      }

      my @tmparr = (split /\s*\,\s*/,  $user_project);
      my $user = $tmparr[0];
      my $project = $tmparr[1];  

      my $obj =  new NmiStats();
      $obj->user($user);
      $obj->project($project);    
      $obj->builds($builds);    
      $obj->tests($tests);    
      $obj->other($other);    
      $total_builds += $builds;
      $total_tests += $tests; 
      $total_other += $other;
      $platform_jobs = $builds + $tests + $other;
      $obj->total($platform_jobs);  

      # Find the project url for the user.  Make the assumption that login1 is
      # the actual account name.  
      # TODO: check login2 and login3 if login1 doesn't yield results
      my $cmd = qq/SELECT URL FROM account_request WHERE login1="$user"/;  

      my $dbh = DBI->connect($DB_CONNECT_STR, $DB_READER_USER, $DB_READER_PASS);
      unless (defined $dbh) { 
        print "warning: mysql connect() failed: $DBI::errstr";
        exit;
      }

      my $sth = $dbh->prepare($cmd);

      unless ($sth->execute) {
        print "warning: failed to execute a query: $DBI::errstr";
        exit;
      }

      while ( my $row = $sth->fetchrow_hashref() ) {
        my $url = $row->{'URL'};
        $obj->url($url);
      } 
 
      push(@objs, $obj);   
      $dbh->disconnect;
  }

  &send_report();

sub send_report {

  my @sorted_objs = sort { $b->total <=> $a->total } @objs;

  my @rcpts = $mailto;

  my $mailer = Mail::Mailer->new;
  my $username = `whoami`;
  chomp $username;
  print "Sending email report to: ", join ' ', @rcpts, "\n";

  $mailer->open( {To => [@rcpts],
                  From => "$username",
                  #'Content-type' => "text/html",
                  Subject => "NMI usage stats for the past $days days"});

  # List the user-project pairs
  my %proj_platforms = ();
  my $proj_builds = ();
  my $proj_tests = ();
  my $proj_other = ();
  my $proj_url = ();
  my $proj_users = ();

  # print terminology URL
  printf $mailer "\nBackground: http://nmi.cs.wisc.edu/node/897\n";
  printf $mailer "Terminology: http://nmi.cs.wisc.edu/node/898\n";

  # Warning - ugly hack ahead!  Kids, don't try this at home.  
  # The info per user is used to calculate the project totals.  However, we'd like to display the project
  #   info first in the report.  Write out the user totals to a text file, then append it to the end of the report.  
  my $date = `date +%s`;
  chomp($date);
  open ( PERUSER, ">/tmp/peruser.date") || die "can't open file /tmp/peruser.$date: $?\n";

  # Find the totals per user-project combo
  printf PERUSER "\nTOTALS PER USER\n\n";

  # TODO: Replace the project text with a link and remove the entire Project URL column
  printf PERUSER "%-10s %-20s %-11s %-25s %-50s", "User", "Project", "#PlatformJobs", "(#Build/#Test/#Unknown)", "Project URL";
  printf PERUSER "\n==================================================================================================================\n";

  foreach my $obj (@sorted_objs) {
      
    my $platform_jobs = $obj->builds + $obj->tests + $obj->other;
    my $project = $obj->project; 
    # Some jobs fail on the fetch steps, so no platform_jobs are run.  Don't count these jobs. 
    if ($platform_jobs > 0 )  {

      my $url = $obj->url;
      if ( $url && ( $url !~ m/http/ ) ) {
        $url = "http://$url";
      }

      printf PERUSER "%-10s %-20s %-13s %-1s %-4d %-1s %-3d %-1s %-6d %1s %-2s %-50s", $obj->user, $obj->project, $platform_jobs, "(", $obj->builds, "/", $obj->tests, "/", $obj->other, ")", "  ", $url;
      printf PERUSER "\n";

      # Count up the user-project totals
      $proj_platforms{$project} += $platform_jobs;
      $proj_builds{$project} +=  $obj->builds;
      $proj_tests{$project} +=  $obj->tests;
      $proj_other{$project} +=  $obj->other;
      $proj_url{$project} =  $url;
      $user = $obj->user; 
      $proj_users{$project} =  "$proj_users{$project}" . " $user";
    }
  }
  $total_platform_jobs = $total_builds + $total_tests + $total_other;
  printf PERUSER "\n=====================================================================================\n";
  printf PERUSER "%-10s %-20s %-11s %-6s %-3d %-1s %-3d %-1s %-3d %1s", total, " ", $total_platform_jobs, "     (", $total_builds, "/", $total_tests, "/", $total_other, ")";
 
  # Find the totals per project, listing all users for each project.
  printf $mailer "\n\n\nTOTALS PER PROJECT\n\n";
  printf $mailer "%-17s %-15s %-25s %-35s", "Project", "#PlatformJobs", "(#Build/#Test/#Unknown)", "Users";
  printf $mailer "\n=====================================================================================\n";

  # Sort the projects by number of platform jobs, most to least
  foreach my $project (sort { $proj_platforms{$b} <=> $proj_platforms{$a} } keys %proj_platforms) {
      printf $mailer "%-17s %-60s %-15s %-1s %-4d %-1s %-3d %-1s %-6d %-2s %-35s",  $project, $proj_url{$project}, $proj_platforms{$project}, "(", $proj_builds{$project}, "/", $proj_tests{$project}, "/", $proj_other{$project}, ")", $proj_users{$project};
      printf $mailer "\n";
  }

  close PERUSER;
  # now, append all of the user totals from the temp file
  open ( PER_USER, "</tmp/peruser.date") || die "can't open file /tmp/peruser.$date: $?\n";
  while (<PER_USER>){
    printf $mailer $_;
  }
  $mailer->close;
}

sub datetime_offset {
    my ($off_sec, $off_min, $off_hour, $off_day) = @_;
    my $now = time  . "\n";
    my $interval = ($off_sec) +
                   ($off_min * 60) +
                   ($off_hour * 60 * 60) +
                   ($off_day * 60 * 60 * 24);
    my $past = $now + $interval;

    my ($sec, $min, $hour, $day, $mon, $year) = gmtime($past);
    $year += 1900;
    $mon += 1;
    $mon = "0" . $mon if $mon < 10;
    $sec = "0" . $sec if $sec < 10;
    $min = "0" . $min if $min < 10;
    $hour = "0" . $hour if $hour < 10;

    return "$year-$mon-$day $hour:$min:$sec";
}
