#!/users/contrib/bin/perl


use Sybase::Sybperl;
use Env qw(BATCH_USER_NAME BATCH_PASSWORD);

##############
############## Subroutines
##############

# Send the input string to the DB server and get the results
# $_[0] - sql string
sub DBEXEC {
	&dbcmd($dbproc, $_[0]);
	&dbsqlexec($dbproc);
	&dbresults($dbproc);
			}
# Turn UTC in to people time
sub GETTIME {
	if ($_[0] eq "999999999") {
	$time = ("******** **********");
	} 
        elsif ($_[0] eq "0") {
	$time = ("******** **********");
	}
	else {
	($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($_[0]);
	$mon++;
	$time = sprintf ("%.2d:%.2d:%.2d %.2d/%.2d/%.2d", $hour, $min, $sec, $mon, $mday, $year + 1900);
	}
        }
# set job status
sub JOBSTATUS {
	if ($_[0] == 9) {$jstatus1 = ("ACTIVATED");}
        elsif ($_[0] == 5) {$jstatus2 = ("FAILURE");}
        elsif ($_[0] == 8) {$jstatus3 = ("INACTIVE");}
        elsif ($_[0] == 11) {$jstatus4 = ("ON_HOLD");}
        elsif ($_[0] == 7) {$jstatus5 = ("ON_ICE");}
        elsif ($_[0] == 12) {$jstatus5 = ("QUE_WAIT");}
        elsif ($_[0] == 10) {$jstatus6 = ("RESTART");}
        elsif ($_[0] == 1) {$jstatus7 = ("RUNNING");}
        elsif ($_[0] == 3) {$jstatus8 = ("STARTING");}
        elsif ($_[0] == 4) {$jstatus9 = ("SUCCESS");}
        elsif ($_[0] == 6) {$jstatus10 = ("TERMINATED");}
	}

				
############ Setup report format
############

format STDOUT =
@<<<<<<<<<<<<<<<<<<<<<
$etime
.

############
############ Start
############

# if ($ARGV[0] eq "print") {
# open(STDOUT,"| lp -dbigtooth -o2");
# }
# else {
# open(STDOUT,"| elm -s 'Nightly cycle check' \xxxxxxxx\@smtplink");
# }

# login to the database
$dbproc = &dblogin($BATCH_USER_NAME, $BATCH_PASSWORD, 'SQL_AUTOSYS_DEV');

#$currdate =  &GETTIME(time);
#print "Date: $currdate \n\n";
#print " \n";
#print "Jobname                        Last Start           Last End            \n";
#print "------------------------------ -------------------- --------------------\n";

# Submit SQL Statement
	&DBEXEC("select job.job_name, job_status.last_start, job_status.last_end from job, job_status
	where job.joid = job_status.joid and job.job_name in
	('pa_box_settle')
	order by job_status.last_end");
	 while(@status = &dbnextrow($dbproc)){
	 $name = ($status[0]);
	 $stime = &GETTIME($status[1]);
	 $etime = &GETTIME($status[2]);
         write(STDOUT);}

#print "\n\n";
print " \n";
close(STDOUT);