#!/usr/bin/perl -w
use strict;

#########################
# This script displays usage by using four criteria:
# 1. By project
# 2. By user
# 3. By resource
# 4. By jobs run within a specified period of time given the user, TG account, and start and end times
#########################

# Uncomment to use non-standard perl modules.
use lib qw(/usr/local/apps/tgusage-3.0-r1/perl-libs/lib/perl5/site_perl/5.8.0);

#######################################
# You should use the following libs
#######################################

# use Term::ReadKey;
use Getopt::Long;
use DBI;
use DBI qw(:sql_types);
use DBD::Pg qw(:pg_types);
use Carp;
use POSIX qw(ceil floor);
use Data::Dumper;

############
# Constants
############
my $VERSION   = '3.0-r3';
my $PROG_NAME = $0;

##########################
# Establish DB connection
##########################
my $DBHOST = 'tgcdb.teragrid.org';    # Production (hogatha)
my $DBNAME = 'teragrid';
my $DBPORT = 5432;         # default
my $DBUSER = 'tgusage';
my $DBPASS = 'tgusage';    # read only account
my $dbh    = db_connect();

my $FALSE           = 0;
my $TRUE            = 1;
my $DEBUG           = $FALSE;

my %options         = ();

my $login_user = $ENV{USER};
chomp($login_user);

my $login_user_TG_su   = is_user_a_TG_SU ($login_user);         # Early determination helps 
my ( $login_user_pid, $login_username, $login_user_firstname, $login_user_lastname )
                       = get_login_user_info ($login_user);     # Will need later on

if ( @ARGV > 0 ) {
  GetOptions(
            "a|account=s"       => \$options{account},
            "u|user|username=s" => \$options{user},
            "r|resource=s"      => \$options{resource},
            "version"           => \$options{version},
            "h|?|help"          => \$options{help},
            "I|ID=s"            => \$options{ID},
            "j|jobs"            => \$options{jobs},
            "begindate=s"       => \$options{begindate},
            "enddate=s"         => \$options{enddate},
            "debug"             => \$options{debug}
  );
} else { usage("TGUSAGE ERROR: Please supply an argument.");  }     # if no arguments are given at command line, prompt for input.

##############
## MAIN     ##
##############
if     ( defined $options{jobs}        # Simplifies the logic if job option processed first
          and defined $options{user}
            and defined $options{account}
              and defined $options{begindate}
                and defined $options{enddate} )
       { get_jobs ( $options{user}, $options{account}, $options{begindate}, $options{enddate} ); }
elsif ( defined $options{jobs}        # Simplifies the logic if job option processed first
          and defined $options{user}
              and defined $options{begindate}
                and defined $options{enddate} )
       { get_jobs_all_accounts ( $options{user},  $options{begindate}, $options{enddate} ); }
elsif  ( defined $options{ID}
          and defined $options{account}
           and defined $options{user}  )
       { get_job_by_ID ( $options{ID}, $options{account}, $options{user} ); }
else   { usage ("You must supply an argument."); }

db_disconnect($dbh);
exit 0;

############################
## Subs                   ##
############################
sub get_job_by_ID {
    my ( $ID, $account, $username ) = @_;

    my $resource = q{};

    debug ( "sub get_job_by_ID ID is $ID, username is $username" );

    # Uncomment to produce column headers. 
    # my $output = "\"LOCAL_JOBID\" \"LOCAL_CHARGE\" \"START_TIME\" \"END_TIME\" \"SUBMIT_TIME\" \"CHARGE_NUMBER\" \"WALL_HRS\" \"SU\" \"NODECOUNT\" \"PROCESSORS\" \"QUEUE\"";
    my $output = "LOCAL_JOBID, LOCAL_CHARGE, START_TIME, END_TIME, SUBMIT_TIME, CHARGE_NUMBER, WALL_HRS, SU, NODECOUNT, PROCESSORS, QUEUE";
    print_allocs($output);

    # Group output by resource
    my $sql = qq{
        SELECT  DISTINCT res.resource_name
          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          = '$username'
           AND  ac.charge_number    = '$account';
    };

    my $sth = prepare_execute( $dbh, $sql );
    while ( $resource = $sth->fetchrow_array() )  {

        # Must be a TG superuser or an account co-member to see someone else's usage
        if ( $login_user ne $username and not $login_user_TG_su ) {
            if ( are_both_on_account ( $account, $username, $login_user ) eq 0 ) {
                print ("Permission denied for $login_user to view $username\'s $account usage.\n");
                print (" Login user $login_user not a TG superuser nor on account $account\n");
                exit 1;     # Same user on possible multiple resources but we failed the test so we're done
            }
            else { ; }      # Both are on the account, display
        }

        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
                        ,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.local_jobid       = '$ID'
                   AND  j.username         = '$username'
                   AND  ac.charge_number    = '$account'
                   AND  res.resource_name   = '$resource';
                };

        my $sth = prepare_execute( $dbh, $sql );

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

            # CSV for import into Excel or DB.
            my $output =
              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});

            # Formatted for display.
#           my $output =
#             sprintf("%-35s", defined $job{'LOCAL_JOBID'}   ? $job{'LOCAL_JOBID'}   : q{n/a})
#           . sprintf("%-10s", defined $job{'LOCAL_CHARGE'}  ? $job{'LOCAL_CHARGE'}  : q{n/a})
#           . sprintf("%-25s", defined $job{'START_TIME'}    ? $job{'START_TIME'}    : q{n/a})
#           . sprintf("%-25s", defined $job{'END_TIME'}      ? $job{'END_TIME'}      : q{n/a})
#           . sprintf("%-25s", defined $job{'SUBMIT_TIME'}   ? $job{'SUBMIT_TIME'}   : q{n/a})
#           . sprintf("%-15s", defined $job{'CHARGE_NUMBER'} ? $job{'CHARGE_NUMBER'} : q{n/a})
#           . sprintf("%-10s", defined $job{'WALL_HRS'}      ? $job{'WALL_HRS'}      : q{n/a})
#           . sprintf("%-10s", defined $job{'SU'}            ? $job{'SU'}            : q{n/a})
#           . sprintf("%-6s" , defined $job{'NODECOUNT'}     ? $job{'NODECOUNT'}     : q{n/a})
#           . sprintf("%-6s" , defined $job{'PROCESSORS'}    ? $job{'PROCESSORS'}    : q{n/a})
#           . sprintf("%-10s", defined $job{'QUEUE'}         ? $job{'QUEUE'}         : q{n/a});

            print_allocs($output);
        }

        if ($sth->rows eq 0) {
#           print "No jobs found for user $username on account $account on resource $resource\n";
            next;
        }

        if ($DBI::err) { warn "Data fetching terminated early by error: $DBI::errstr\n"; }

    }

    if ($sth->rows eq 0) {
        print "\nNo jobs found for user $username on account $account\n\n";
        return;
    }

    if ($DBI::err) { warn "Data fetching terminated early by error: $DBI::errstr\n"; }

}

sub get_jobs_all_accounts {
    my ( $username,  $start, $end ) = @_;

    debug ( "sub get_jobs user is $username,  begin date is $start, end date is $end" );

    my $resource               = q{};
    my $grand_total_charges    = 0;
    my $grand_total_wall_hours = 0;
    my $grand_total_jobs       = 0;

    # my $output = "\"LOCAL_JOBID\" \"LOCAL_CHARGE\" \"START_TIME\" \"END_TIME\" \"SUBMIT_TIME\" \"CHARGE_NUMBER\" \"WALL_HRS\" \"SU\" \"NODECOUNT\" \"PROCESSORS\" \"QUEUE\"";
    my $output = "LOCAL_JOBID, LOCAL_CHARGE, START_TIME, END_TIME, SUBMIT_TIME, CHARGE_NUMBER, WALL_HRS, SU, NODECOUNT, PROCESSORS, QUEUE";


	# TL: commented this out, I don't want headers
    # print_allocs($output);

    # Group output by resource
    my $sql = qq{
        SELECT  DISTINCT res.resource_name
          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          = '$username'
           AND  j.start_time        >= to_timestamp('$start', 'MM-DD-YYYY')
           AND  j.end_time          <= to_timestamp('$end', 'MM-DD-YYYY');
    };

    my $sth = prepare_execute( $dbh, $sql );
    while ( $resource = $sth->fetchrow_array() )  {

		# TL: added the "as resource" so we know name of resource field and added printing of resource
		# column below.
        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          = '$username'
    			   AND  j.start_time        >= to_timestamp('$start', 'MM-DD-YYYY')
    			   AND  j.end_time          <= to_timestamp('$end', 'MM-DD-YYYY')
                   AND  res.resource_name   = '$resource';
    			};

        my $sth = prepare_execute( $dbh, $sql );

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

            # CSV for import into Excel or  DB.
            my $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});

            # Formatted for display.
#           my $output =
#             sprintf("%-35s", defined $job{'LOCAL_JOBID'}   ? $job{'LOCAL_JOBID'}   : q{n/a})
#           . sprintf("%-10s", defined $job{'LOCAL_CHARGE'}  ? $job{'LOCAL_CHARGE'}  : q{n/a})
#           . sprintf("%-25s", defined $job{'START_TIME'}    ? $job{'START_TIME'}    : q{n/a})
#           . sprintf("%-25s", defined $job{'END_TIME'}      ? $job{'END_TIME'}      : q{n/a})
#           . sprintf("%-25s", defined $job{'SUBMIT_TIME'}   ? $job{'SUBMIT_TIME'}   : q{n/a})
#           . sprintf("%-15s", defined $job{'CHARGE_NUMBER'} ? $job{'CHARGE_NUMBER'} : q{n/a})
#           . sprintf("%-10s", defined $job{'WALL_HRS'}      ? $job{'WALL_HRS'}      : q{n/a})
#           . sprintf("%-10s", defined $job{'SU'}            ? $job{'SU'}            : q{n/a})
#           . sprintf("%-6s" , defined $job{'NODECOUNT'}     ? $job{'NODECOUNT'}     : q{n/a})
#           . sprintf("%-6s" , defined $job{'PROCESSORS'}    ? $job{'PROCESSORS'}    : q{n/a})
#           . sprintf("%-10s", defined $job{'QUEUE'}         ? $job{'QUEUE'}         : q{n/a});

            print_allocs($output);

        }

        if ($sth->rows eq 0) {
            print "No jobs found for user $username on resource $resource between $start and $end\n";
            next;
        }

        if ($DBI::err) { warn "Data fetching terminated early by error: $DBI::errstr\n"; }

    }

    if ($sth->rows eq 0) {
        print "\nNo jobs found for user $username on any resource between $start and $end\n\n";
        return;
    }

    if ($DBI::err) { warn "Data fetching terminated early by error: $DBI::errstr\n"; }

}

sub get_jobs {
    my ( $username, $account, $start, $end ) = @_;
    $account = uc($account);

    debug ( "sub get_jobs user is $username, account is $account, begin date is $start, end date is $end" );

    my $resource               = q{};
    my $grand_total_charges    = 0;
    my $grand_total_wall_hours = 0;
    my $grand_total_jobs       = 0;

    # my $output = "\"LOCAL_JOBID\" \"LOCAL_CHARGE\" \"START_TIME\" \"END_TIME\" \"SUBMIT_TIME\" \"CHARGE_NUMBER\" \"WALL_HRS\" \"SU\" \"NODECOUNT\" \"PROCESSORS\" \"QUEUE\"";
    my $output = "LOCAL_JOBID, LOCAL_CHARGE, START_TIME, END_TIME, SUBMIT_TIME, CHARGE_NUMBER, WALL_HRS, SU, NODECOUNT, PROCESSORS, QUEUE";


	# TL: commented this out, I don't want headers
    # print_allocs($output);

    # Group output by resource
    my $sql = qq{
        SELECT  DISTINCT res.resource_name
          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          = '$username'
           AND  j.start_time        >= to_timestamp('$start', 'MM-DD-YYYY')
           AND  j.end_time          <= to_timestamp('$end', 'MM-DD-YYYY')
           AND  ac.charge_number    = '$account';
    };

    my $sth = prepare_execute( $dbh, $sql );
    while ( $resource = $sth->fetchrow_array() )  {

        # Must be a TG superuser or an account co-member to see someone else's usage
        if ( $login_user ne $username and not $login_user_TG_su ) {
            if ( are_both_on_account ( $account, $username, $login_user ) eq 0 ) {
                print ("Permission denied for $login_user to view $username\'s $account usage.\n");
                print (" Login user $login_user not a TG superuser nor on account $account\n");
                exit 1;     # Same user on possible multiple resources but we failed the test so we're done
            }
            else { ; }      # Both are on the account, display
        }

		# TL: added the "as resource" so we know name of resource field and added printing of resource
		# column below.
        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          = '$username'
    			   AND  j.start_time        >= to_timestamp('$start', 'MM-DD-YYYY')
    			   AND  j.end_time          <= to_timestamp('$end', 'MM-DD-YYYY')
    			   AND  ac.charge_number    = '$account'
                   AND  res.resource_name   = '$resource';
    			};

        my $sth = prepare_execute( $dbh, $sql );

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

            # CSV for import into Excel or  DB.
            my $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});

            # Formatted for display.
#           my $output =
#             sprintf("%-35s", defined $job{'LOCAL_JOBID'}   ? $job{'LOCAL_JOBID'}   : q{n/a})
#           . sprintf("%-10s", defined $job{'LOCAL_CHARGE'}  ? $job{'LOCAL_CHARGE'}  : q{n/a})
#           . sprintf("%-25s", defined $job{'START_TIME'}    ? $job{'START_TIME'}    : q{n/a})
#           . sprintf("%-25s", defined $job{'END_TIME'}      ? $job{'END_TIME'}      : q{n/a})
#           . sprintf("%-25s", defined $job{'SUBMIT_TIME'}   ? $job{'SUBMIT_TIME'}   : q{n/a})
#           . sprintf("%-15s", defined $job{'CHARGE_NUMBER'} ? $job{'CHARGE_NUMBER'} : q{n/a})
#           . sprintf("%-10s", defined $job{'WALL_HRS'}      ? $job{'WALL_HRS'}      : q{n/a})
#           . sprintf("%-10s", defined $job{'SU'}            ? $job{'SU'}            : q{n/a})
#           . sprintf("%-6s" , defined $job{'NODECOUNT'}     ? $job{'NODECOUNT'}     : q{n/a})
#           . sprintf("%-6s" , defined $job{'PROCESSORS'}    ? $job{'PROCESSORS'}    : q{n/a})
#           . sprintf("%-10s", defined $job{'QUEUE'}         ? $job{'QUEUE'}         : q{n/a});

            print_allocs($output);

        }

        if ($sth->rows eq 0) {
            print "No jobs found for user $username on account $account on resource $resource between $start and $end\n";
            next;
        }

        if ($DBI::err) { warn "Data fetching terminated early by error: $DBI::errstr\n"; }

    }

    if ($sth->rows eq 0) {
        print "\nNo jobs found for user $username on account $account on any resource between $start and $end\n\n";
        return;
    }

    if ($DBI::err) { warn "Data fetching terminated early by error: $DBI::errstr\n"; }

}

# A generic header
sub print_header  {
    my ( $account, $resource, $start_date, $end_date ) = @_;
    $account = uc($account);

    print "\n";
    print "Account: $account\n";
    print "Resource: $resource\n";

    # Local project ID missing but it may not be required
    print "Allocation Period: $start_date to $end_date\n";
    print "\n";

    print
    "Name (Last First) or Account       Total      Remaining        Usage\n";
    print
    "----------------------------     ----------  ------------   ---------- \n";
}

# Print output
sub print_allocs {
    my $output = shift;
    print " $output \n";
}

sub usage  {
    print STDERR "\n", @_, "\n";

    print STDERR qq{
USAGE: $PROG_NAME [options]

             [OPTIONS] 
             -a --account <account name> 
             -u --user --username <user name>
             -r --resource <resource name>
             -I --ID <local job id>  --username=<username>  --account=<account name> 
             -j --username=<username> --account=<account name> --begindate=<mm-dd-yyyy> --enddate=<mm-dd-yyyy>
             -v --version
             -h --help 

example:
    $PROG_NAME --account TG-ABC040001

TeraGrid resources:
};

    my $sql = qq{
        SELECT DISTINCT resource
          FROM tgusage.user_alloc_view
    };

    my $sth = prepare_execute( $dbh, $sql );

    # Outer loop is the resources available to the account
    while ( my $resource = $sth->fetchrow_array() )  {
        print "    $resource\n";
    }

exit (1);
}

sub debug {
    return unless ($DEBUG);
    my ( $package, $file, $line ) = caller();
    print join( '', "DEBUG (at $file $line): ", @_, "\n" );
}


#####################
# Data privacy subs
#####################

# For use in determining if the login user can view usage.
sub get_login_user_info {
    my $login_user = shift;

    my $sql = qq{ 
        SELECT DISTINCT sa.person_id
                        ,sa.username
                        ,p.first_name
                        ,p.last_name
                   FROM acct.system_accounts sa
                        ,acct.superusers su
                        ,acct.people p
                  WHERE sa.person_id = p.person_id
                    AND sa.username  = '$login_user'
        };

    my $sth = prepare_execute( $dbh, $sql );

    if ($sth->rows eq 0) {
        debug( "User $login_user\'s PID not found" );
        return 0;
    }
    else {
        my @login_user_info = $sth->fetchrow_array();
        my ( $pid, $username, $firstname, $lastname ) = @login_user_info;
        debug ("$lastname, $firstname has username $username and person_id of $pid");
        return @login_user_info;
    }

}

# See if user is a TG superuser.
sub is_user_a_TG_SU {
    my $username = shift;

    my $sql = qq{ 
        SELECT DISTINCT sa.person_id
                        ,p.first_name
                        ,p.last_name
                   FROM acct.system_accounts sa
                        ,acct.superusers su
                        ,acct.people p
                  WHERE sa.person_id = p.person_id
                    AND sa.username  = '$username'
                    AND su.person_id = p.person_id
        };

    my $sth = prepare_execute( $dbh, $sql );

    if ($sth->rows eq 0) {
        debug( "User $username is NOT a TeraGrid superuser" );
        return 0;
    }
    else { 
        my @su_data = $sth->fetchrow_array();
        my ( $pid, $firstname, $lastname ) = @su_data;
        debug ("$lastname, $firstname is Unix user $username and is a TG superuser");
        return 1;
    }

}

# Determine if a user is the PI on an account, return 0 (false) or 1 (true).
sub is_user_a_PI {
    my ( $tg_account, $username ) = @_;

    my $sql = qq{
        SELECT DISTINCT  sa.person_id
                        ,p.first_name
                        ,p.last_name
                   FROM  acct.system_accounts sa
                        ,acct.people p
                        ,acct.principal_investigators pi
                        ,acct.accounts ac
                        ,acct.accounts_access aa
                  WHERE  sa.username = '$username'
                    AND sa.person_id = p.person_id
                    AND pi.person_id = p.person_id
                    AND aa.person_id = p.person_id
                    AND aa.account_id = ac.account_id
                    AND ac.charge_number = '$tg_account';
        };
 
    my $sth = prepare_execute( $dbh, $sql );
    my @pi_data = $sth->fetchrow_array();
 
    # If the Unix login user is a PI on this project allow visibility
    if ($sth->rows gt 0) {
        my ( $pid, $firstname, $lastname ) = @pi_data;
        debug ("$lastname, $firstname has a person_id of $pid and is a PI on $tg_account");
        return 1;
    } 
    else { 
        debug ("$username is NOT a PI $tg_account");
        return 0;
    }

}

# Determine if two users are both on an account and return 0 (no) or 1 (yes).
sub are_both_on_account {
    my ( $tg_account, $username, $login_user ) = @_;
    my ( $sql, $sth );

    $sql = qq{
        SELECT DISTINCT ac.charge_number
                   FROM acct.accounts ac,
                        acct.accounts_access aa,
                        acct.system_accounts sa,
                        acct.people p
                  WHERE sa.username = '$login_user' AND
                        sa.person_id = p.person_id AND
                        p.person_id = aa.person_id AND
                        aa.account_id = ac.account_id AND
                        ac.charge_number = '$tg_account'
              INTERSECT
        SELECT DISTINCT ac.charge_number
                   FROM acct.accounts ac,
                        acct.accounts_access aa,
                        acct.system_accounts sa,
                        acct.people p
                  WHERE sa.username = '$username' AND
                        sa.person_id = p.person_id AND
                        p.person_id = aa.person_id AND
                        aa.account_id = ac.account_id AND
                        ac.charge_number = '$tg_account';
        };

    $sth = prepare_execute( $dbh, $sql );

    if ($sth->rows eq 0) {
        debug ("$login_user and $username are NOT on $tg_account"); 
        return 0;
    }
    else { 
        debug ("$login_user and $username are both on $tg_account");
        return 1;
   }

}

#############
# DB Subs
#############

# Check if the person is a PI.
sub is_person_a_PI {
    my ( $dbh, $person_id, $account ) = @_;
    my $value = 0;
    my $mysql = "select auth_is_pi(?, ?)";

    $value = dbexecsql( $dbh, $mysql, [ $person_id, $account ] );

    if ( $value->[0] != 0 ) {
        return ($TRUE);
    }
    else {
        return ($FALSE);
    }
}

# Execute sql statements. If called in a list context it will return all result rows 
#  else if called in a scalar context it will return the last result row.
# Holdover from previous release mainly because it's used in the is_person_a_PI sub.
sub dbexecsql {
    my $dbh      = shift;
    my $sql      = shift;
    my $arg_list = shift;

    my ( @values, $result );
    my $i      = 0;
    my $retval = -1;
    my $prepared_sql;

    eval {
        debug("SQL going in =  $sql");
        $prepared_sql = $dbh->prepare($sql);

        $i = 1;
        foreach my $arg (@$arg_list) {
            $arg = '' unless $arg;
            $prepared_sql->bind_param( $i, $arg );
            debug("arg ($i) = $arg");
            $i++;
        }

        $prepared_sql->execute;

        @values = ();
        while ( $result = $prepared_sql->fetchrow_arrayref ) {
            push( @values, [@$result] );
            foreach (@$result) { $_ = '' unless defined($_); }
            debug( "result row = ", join( ":", @$result ), "" );
        }
    };

    if ($@) { dberror($@); }

    debug( "wantarray = ", wantarray, "" );
    return wantarray ? @values : $values[-1];
}

sub db_connect {
    my %attr = ( PrintError => 0, RaiseError => 1 );
    my $dbh = DBI->connect( "dbi:Pg:dbname=$DBNAME;host=$DBHOST;port=$DBPORT",
        $DBUSER, $DBPASS, \%attr );
    dberror("Can't connect to Teragrid Central DB: $DBI::errstr\n")
        unless ($dbh);
    $dbh->do("SET search_path to acct,tgusage");
    $dbh->do("SET datestyle to SQL");
    return $dbh;
}

sub prepare_execute {
    my ( $dbh, $sql ) = @_;
    my $sth = $dbh->prepare($sql)
        or warn("Cannot prepare SQL '$sql':\n $dbh->errstr()");
    $sth->execute or warn("Cannot execute SQL '$sql':\n $sth->errstr()");
    return $sth;
}

sub db_commit {
    my $dbh = shift;
    return $dbh->commit;
}

sub db_disconnect {
    my $dbh   = shift;
    my $dbcon = $dbh->disconnect
        or warn "Disconnection failed: $DBI::errstr\n";
    return $dbcon;
}