#!/usr/bin/suidperl
#
# jobinfo -- Example of getting job info from pbsacct DB
#
# Copyright 2009 University of Tennessee
# Portions copyright 2006 Ohio Supercomputer Center
#
# License:  GNU GPL v2; see ../COPYING for details.
# Revision info:
# $HeadURL$
# $Revision$
# $Date$
#
use strict;
use POSIX;
use DBI;

#
# Main program begins here
#

# my system name in DB
my $system=`hostname`;
chomp($system);

my $dbhost="localhost";
my $dbuser="pbsacct";
my $dbpasswd="pbsRroxor";
my $dbname="pbsacct";
my $dbtable="Jobs";
my $columns="jobname,username,groupname,account,submithost,queue,qos,feature,gres,nproc,walltime,cput AS cputime,mem_req AS mem_requested,mem_kb AS mem_used,FROM_UNIXTIME(submit_ts) AS submit_time,FROM_UNIXTIME(start_ts) AS start_time,FROM_UNIXTIME(end_ts) AS end_time,exit_status";

while ( $ARGV[0] =~ /^-.*/ )
  {
    if ( $ARGV[0] eq "-d" )
      {
        $dbname=$ARGV[1];
        shift(@ARGV);
      }
    elsif ( $ARGV[0] eq "-h" )
      {
        $system=$ARGV[1];
        shift(@ARGV);
      }
    elsif ( $ARGV[0] eq "-p" )
      {
        $dbpasswd=$ARGV[1];
        shift(@ARGV);
      }
    elsif ( $ARGV[0] eq "-s" )
      {
        $dbhost=$ARGV[1];
        shift(@ARGV);
      }
    elsif ( $ARGV[0] eq "-t" )
      {
        $dbtable=$ARGV[1];
        shift(@ARGV);
      }
    elsif ( $ARGV[0] eq "-u" )
      {
        $dbuser=$ARGV[1];
        shift(@ARGV);
      }

    shift(@ARGV);
  }

# input verification
# bale out if $columns contains something that couldn't possibly be
# in an SQL column name (e.g. semicolons)
if ( $columns =~ /[^A-Za-z0-9\*\(\)\+\-\,\.\_\/ ]/ )
{
    die "$0:  bogus character(s) in column list\n";
}

# user name and group membership
my @pwent = getpwuid($<);
my $username = $pwent[0];
my @groups = ();
my @grent = getgrgid($pwent[3]);
my $groupname = $grent[0];
push(@groups,$groupname);
while ( @grent=getgrent() )
  {
    my @members = split(/\s+/,$grent[3]);
    my @ingroup = grep(/$username$/,@members);
    if ( $#ingroup>=0 )
      {
	$groupname = $grent[0];
	my @found = grep(/^$groupname$/,@groups);
	if ( $#found<0 )
	  {
	    push(@groups,$groupname);
	  }
      }
  }
#print STDERR "username = $username, groups = ( @groups )\n";

# connect to DB
my $dsn = "DBI:mysql:database=$dbname;host=$dbhost;";
my $dbh = DBI->connect($dsn,$dbuser,$dbpasswd) ||
  die "$0:  unable to connect to DB!\n";

foreach my $jobid ( @ARGV )
  {
    my $query = "SELECT ".$columns." FROM Jobs WHERE system=\'".$system."\' AND jobid LIKE \'".$jobid.".\%\' AND ( username='".$username."'";
    for my $groupname ( @groups )
      {
	$query .= " OR groupname='".$groupname."'";
      }
    $query .= " )";
    #print STDERR $query."\n";
    my $sth = $dbh->prepare($query) or die "Could not prepare query:  ".$dbh::errstr;
    $sth->execute() or die "Could not execute query:  ".$dbh::errstr;
    my @colname = split(/ *, */,$columns);
    for ( my $i=0 ; $i<=$#colname ; $i++ )
      {
	if ( $colname[$i] =~ / AS / )
	  {
	    $colname[$i] =~ s/.* AS //;
	  }
      }
    if ( $sth->rows > 0 )
    {
	print $system." jobid ".$jobid."\n";
	while ( my @data = $sth->fetchrow_array() )
	{
	    #print STDERR ($#data+1)." fields\n";
	    for ( my $i=0 ; $i<($#data+1) ; $i++ )
	    {
	      if ( $colname[$i] eq "mem_used" || $colname[$i] eq "vmem_used" )
		{
		  print "\t".$colname[$i]." = ".$data[$i]."kb\n";
		}
	      else
		{
		  print "\t".$colname[$i]." = ".$data[$i]."\n";
		}
	    } 
	}
    }
    else
    {
	print "No information found for ".$system." jobid ".$jobid."\n";
    }
    $sth->finish;
    print "\n";
  }

# disconnect from DB
$dbh->disconnect();