#!/usr/bin/perl -w 

######################################################################
# Tony Chen: 
# Date: 2020/02/27
# 
# As it was mentioned before, Robert Light from XSEDE DB group had 
# to recreate a new view to make the following script works.  
# However, there is a logical error in the SQL query which excludes
# some jobs from the result file.  
#
# The previous script has been backed up in case we need it in the future.
# The backup file can be found in the same directory with a new name 
# 'cipres-tgusage-new_bkp-2020-02-27.'
#
# CHANGES/MODIFICATIONS:
# Replaced 'AND  j.start_time  >= to_timestamp(?, 'MM-DD-YYYY')' in WHERE clause
# with 'AND  j.end_time  >= to_timestamp(?, 'MM-DD-YYYY')'. 
#
# NOTE:
# The above modification is from Robert Light with XSEDE DB group. 
######################################################################

use DBI;
use strict;
use Getopt::Long;

my $dbh = DBI->connect('dbi:Pg:dbname=teragrid;host=tgcdb.teragrid.org;','cipres_jobs', 'F8JLcvv135');

$dbh->do("SET datestyle to SQL");

my %options         = ();
if ( @ARGV > 0 ) {
    GetOptions(
            "begindate=s"       => \$options{begindate},
            "enddate=s"         => \$options{enddate},
    );
} 
else { 
    print ("TGUSAGE ERROR: Please supply --begindate mm-dd-yyyy and --enddate mm-dd-yyyy."); 
    exit; 
} # if no arguments are given at command line, prompt for input.                                                                                                 


my $username = 'cipres';
my $start = $options{begindate};
my $end = $options{enddate};

my $sql = qq{
    SELECT  
        j.job_id 
        ,j.account_id
        ,j.resource_id
        ,j.local_jobid     
        ,round(j.local_charge) as local_charge
        ,j.start_time
        ,j.end_time
        ,j.submit_time
        ,round(j.wallduration/3600.0, 2) as wall_hrs
        ,j.nodecount
        ,j.processors
        ,j.queue
        ,round(j.adjusted_charge) as SU
        ,res.resource_name as resource
        ,ac.charge_number
    FROM acct.jobs j
         ,acct.resources res
         ,acct.accounts ac
    WHERE res.resource_id = j.resource_id
        AND  j.account_id = ac.account_id
        AND  j.username   = ?
        AND  j.end_time   >= to_timestamp(?, 'MM-DD-YYYY')
        AND  j.end_time   <= to_timestamp(?, 'MM-DD-YYYY')
};

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

$sth->execute($username,$start,$end);

my $output = '';

while ( my $hash_ref = $sth->fetchrow_hashref('NAME_uc') ) 
{
    my %job = %$hash_ref;

    # CSV for import into Excel or  DB.                                                                                                                                                                                    
    $output .=
	sprintf("%s", defined $job{'RESOURCE'}        ? $job{'RESOURCE'}      : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'LOCAL_JOBID'}   ? $job{'LOCAL_JOBID'}   : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'LOCAL_CHARGE'}  ? $job{'LOCAL_CHARGE'}  : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'START_TIME'}    ? $job{'START_TIME'}    : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'END_TIME'}      ? $job{'END_TIME'}      : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'SUBMIT_TIME'}   ? $job{'SUBMIT_TIME'}   : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'CHARGE_NUMBER'} ? $job{'CHARGE_NUMBER'} : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'WALL_HRS'}      ? $job{'WALL_HRS'}      : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'SU'}            ? $job{'SU'}            : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'NODECOUNT'}     ? $job{'NODECOUNT'}     : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'PROCESSORS'}    ? $job{'PROCESSORS'}    : q{n/a}) . q{,}
    . sprintf("%s", defined $job{'QUEUE'}         ? $job{'QUEUE'}         : q{n/a})
    . "\n";
}

print $output;

$dbh->disconnect();