#!/usr/bin/env perl 
#---------------------------------------------------------------------------
#
# Name:        cdbs_report
#
# Description: Generate a report for a CDBS delivery number, showing the
#              reference files that are part of the delivery and the
#              opus_load_date and archive_date for each file.
#
# Usage:       cdbs_report deliveryNumber (default is latest one)
#
#---------------------------------------------------------------------------
unshift @INC, ( split /:/, $ENV{PATH} );
require 'printmsg.pl';
require 'do_dbi_pkg.pl';     # DoDBI package - convenience routines

my $PROCESS_FAILURE = 1;
my $PROCESS_SUCCESS = 0;

# command line args
$delivery_number=$ARGV[0];

my $CDBS_SERVER = $ENV{"CDBS_SERVER"};
my $CDBS_DB     = $ENV{"CDBS_DB"};
my $db = DoDBIopen( $CDBS_SERVER, $CDBS_DB, $PROCESS_FAILURE);

$instrument=NOT_FOUND;

# get the instrument name associated with the delivery number ---------------
#
# HRS
$cmd=qq|select file_name from hrs_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
    $instru =HRS;
    $instrument=hrs_file; 
}

#
# FOC
$cmd=qq|select file_name from foc_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
    $instru =FOC;
    $instrument=foc_file; 
}

#
# WFPC2
$cmd=qq|select file_name from wfpc2_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
    $instru =WFPC2;
    $instrument=wfpc2_file; 
}

#
# FOS
$cmd=qq|select file_name from fos_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
    $instru =FOS;
    $instrument=fos_file; 
}

#
# NICMOS
$cmd=qq|select file_name from nic_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
		$instru =NICMOS;
      		$instrument=nic_file; 
}


#
# STIS
$cmd=qq|select file_name from stis_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
		$instru =STIS;
      		$instrument=stis_file; 
}

#
# ACS
$cmd=qq|select file_name from acs_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
		$instru =ACS;
      		$instrument=acs_file; 
}

#
# WFC3
$cmd=qq|select file_name from wfc3_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
		$instru =WFC3;
      		$instrument=wfc3_file; 
}

#
# COS
$cmd=qq|select file_name from cos_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
		$instru =COS;
      		$instrument=cos_file; 
}

#
# SYNPHOT
$cmd=qq|select file_name from synphot_file where delivery_number=$delivery_number|;

my $sth = DoDBIexecute($db,$cmd);
while ( my ($hrs_file) = DoDBIfetch($db, $cmd, $sth) ){
		$instru =SYNPHOT;
      		$instrument=synphot_file; 
}


if ($instrument eq "NOT_FOUND") {
	print "\n\n";
#	print "$delivery_number is not a valid delivery number\n";
	print "$delivery_number is not for a supported instrument.\n";
	exit;
}


# get the associated information for the delivery out of the file level
# relation.
     
    $cmd=qq|select file_name,opus_load_date,archive_date,reference_file_type,useafter_date from $instrument where delivery_number=$delivery_number and expansion_number<2|;

my $sth = DoDBIexecute($db,$cmd);
while ( @data = DoDBIfetch($db, $cmd, $sth) ){


		$data[4]=~ s/:\d\d:\d\d\d//;
		$data[2]=~ s/:\d\d:\d\d\d//;
		$data[1]=~ s/:\d\d:\d\d\d//; 

#print $instru," ",$data[3]," ",$data[0]," ",$data[4]," ",$data[2]," ",$data[1],"\n";
		write;
}                                        

DoDBIclose($db);
exit;


# report it

####################################################################################

format STDOUT_TOP = 

                                      CDBS Installation Report
                                      ------------------------
                                                                                                 
Instrument  Reference  File Name         Useafter Date        Archive Date         OPUS Load Date
            File Type
----------  ---------  ----------------- --------------       ------------         -------------
.


format STDOUT = 
@<<<<<<<<<< @<<<<<<<<< @<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<
$instru,$data[3],$data[0],$data[4],$data[2],$data[1]
.