#!/export/opt1/perl/bin/perl -w
#DOC#################################################
#DOC
#DOC program requester.pl
#DOC
#DOC author - M Evans
#DOC date - October 2003
#DOC
#DOC purpose : Create requests for waveform data from external data centers
#DOC
#DOC variables : @oked - Array containing concatonated data of accepted waveform requests
#DOC             @output - Used to store network infomation for each $reqid.
#DOC             @output2 - Used to store station information for each $reqid and element in @output.
#DOC             @reqid - Array containing $reqid values that have not been processed.
#DOC             $begin - Start time of individual request
#DOC             $center - Data center
#DOC             $chan - FDSN channel code
#DOC             $check - Output from 'inventory_check' (from 'shared.pl')
#DOC             $end - End time of individual request
#DOC             $e_check - Output from 'inventory_check' for E component (from 'shared.pl')
#DOC             $e_mail - E-mail text
#DOC             $format - Format data is required in, (e.g. SAC)
#DOC             $log_flag - Number of waveforms accepted per $reqid
#DOC             $net - Network code
#DOC             $n_check - Output from 'inventory_check' for E component (from 'shared.pl')
#DOC             $protocol - Request protocol, (e.g. NetDC, AUtoDRM)
#DOC             $reqid - Request ID
#DOC             $request - SQLplus request
#DOC             $sleep - Pause between e-mails
#DOC             $sta - Station code
#DOC             $temp - Temporary variable, used to loop over each element in @output and @output2
#DOC             $temp_flag - Counts number of requests sent
#DOC             $to_address - E-mail address of recipient
#DOC             $username - User who made data request
#DOC             $z_check - Output from 'inventory_check' for Z component (from 'shared.pl')
#DOC
#DOC environment variables : $ENV{DATA_CENTER} - Table containing data center information
#DOC                         $ENV{INVENTORY} - Table containing inventory data
#DOC                         $ENV{LDA} - Set on logging into Oracle
#DOC                         $ENV{LOG_TABLE} - Log of requests sent
#DOC                         $ENV{RETURN_ADDRESS} - E-mail address to which replies should be sent (wfars@isc.ac.uk)
#DOC                         $ENV{SOURCE_TABLE} - Table containing waveform requests
#DOC
#DOC calls subroutines : insert_wfars_log
#DOC                     inventory_check (from 'shared.pl')
#DOC                     oracle_login (from 'shared.pl')
#DOC                     oracle_logout (from 'shared.pl')
#DOC                     oracle_request (from 'shared.pl')
#DOC                     request_autodrm
#DOC                     request_netdc
#DOC                     send_mail (from 'shared.pl')
#DOC                     update_request_status
#DOC
#DOC other subroutines : date2number (from 'shared.pl', called by 'inventory_check')
#DOC                     oracle_command (from 'shared.pl', called by 'insert_wfars_log' and 'update_request_status')
#DOC                     
#DOC other programs : '/usr/bin/mail' (called by 'send_mail')
#DOC                     
#DOC##################################################

use Oraperl;
use strict;

do 'shared.pl';

my (@oked, @output, @output2, @reqid);

my ($begin, $center, $chan, $check, $end, $e_check, $e_mail); 
my ($format, $log_flag, $net, $n_check, $protocol, $reqid, $request);
my ($sta, $temp, $temp_flag, $to_address, $username, $z_check);

my ($sleep) = 10;

### Define variables ###

$ENV{DATA_CENTER}    = "$ENV{SCHEMA}.DATA_CENTER";
$ENV{INVENTORY}      = "$ENV{SCHEMA}.INVENTORY";
$ENV{LOG_TABLE}      = "$ENV{SCHEMA}.WFARS_LOG";
$ENV{RETURN_ADDRESS} = "wfars\@isc.ac.uk";
$ENV{SOURCE_TABLE}   = "$ENV{SCHEMA}.WFARS_REQUEST";

### Login to database ###

($ENV{LDA}) = oracle_login();

### Identify unprocessed requests ###

$request = "select distinct REQID from $ENV{SOURCE_TABLE} 
            where REQ_STATUS is null";

(@reqid) = oracle_request($request);


### Loop over each REQID ###

foreach $reqid (@reqid) {

    $log_flag = 0;

    $request = "select distinct USERNAME, CENTER, NET, FORMAT 
                from $ENV{SOURCE_TABLE} 
                where REQ_STATUS is null
                and REQID = '$reqid'";
                
    (@output) = oracle_request($request);

    ### Loop over each CENTER, NET, FORMAT ###
    
    foreach $temp (@output) {
    
   
        undef @oked;
    
        ($username, $center, $net, $format) = split(/::/,$temp);
        
        $request = "select distinct STA, CHAN, BEGIN, END 
                    from $ENV{SOURCE_TABLE} 
                    where REQ_STATUS is null
                    and NET = '$net' 
                    and REQID = $reqid";
                    
        (@output2) = oracle_request($request);
        
        ### Loop over each STA, CHAN, BEGIN, END ###

        
        foreach $temp (@output2) {
        
            ($sta, $chan, $begin, $end) = split(/::/,$temp);
            
            ### Check for wildcarded entries '*', and check INVENTORY for each channel ###
            
            if ($chan =~ /\*/) {
            
                $e_check = inventory_check($center, $net, $sta, substr($chan,0,2)."E", $begin, $end);
                $n_check = inventory_check($center, $net, $sta, substr($chan,0,2)."N", $begin, $end);
                $z_check = inventory_check($center, $net, $sta, substr($chan,0,2)."Z", $begin, $end);
                
                ### Ensure all channels are available, else dump request ###
                
                $check = "U";
                
                if ($e_check eq "R")  {
                    if ($n_check eq "R") {
                        if ($z_check eq "R") {
                            $check ="R";
                            push (@oked,join("::", $reqid, $center, $net, $sta, $chan, $begin, $end));
                        }
                    }
                }
            
            } else {
            
                $check = inventory_check($center, $net, $sta, $chan, $begin, $end);
                
                if ($check eq "R") { push (@oked,join("::", $reqid, $center, $net, $sta, $chan, $begin, $end)); }
                
            }
            
            ### Update REQ_STATUS ###
            
            update_req_status($reqid, $sta, $check);
                    
        } # End station loop
        
        ### Begin request process if suitable data found ###
        
        if (defined @oked) {
        
            $log_flag++;
        
            ### Identify correct format from DATA_CENTER ###
        
            $request = "select protocol, to_address from $ENV{DATA_CENTER} where center = '$center'";
            ($temp) = oracle_request($request);
            
                   
            ($protocol, $to_address) = split(/::/,$temp);
            
            ### Compose request according to format and send ###
            
            if ($protocol eq "AUTODRM") {
            
                ($e_mail) = request_autodrm(@oked);
                
                sleep 1;
                
            } elsif ($protocol eq "BREQFAST") {
                
                ($e_mail) = request_breqfast(@oked);
                
                sleep $sleep;
                
            } elsif ($protocol eq "NETDC") {
                
                ($e_mail) = request_netdc(@oked);
                
                sleep 60;
            }
            
            send_mail($to_address,$e_mail);
            #send_mail("matt\@isc.ac.uk",$e_mail);
                        
            print "$reqid\n";
            $temp_flag++;
            
            
        } # End 'if' statement
    
     
    
    } # End Center loop
    
    if ($log_flag != "0") {
    
        ### Insert line in WFARS_LOG ###
                    
        insert_wfars_log($reqid, $username, $format);
        
    } 
    
} # End REQID loop


print "----\n= $temp_flag\n";


oracle_logout();

exit(0);

#DOC#################################################
#DOC
#DOC Subroutines defined:
#DOC
#DOC insert_wfars_log
#DOC request_autodrm
#DOC request_breqfast
#DOC request_netdc
#DOC update_req_status
#DOC
#DOC#################################################

#DOC#################################################
#DOC
#DOC subroutine insert_wfars_log
#DOC
#DOC author - M Evans
#DOC date - October 2003
#DOC
#DOC purpose : Update REQ_STATUS field 
#DOC
#DOC input : $reqid - Request ID number
#DOC         $username - User name
#DOC         $format - Format data requested in, (currently SAC)
#DOC
#DOC output : none
#DOC
#DOC internal : $command - String containing SQL command
#DOC
#DOC environment variables : $ENV{LOG_TABLE} - Table containing waveform request log
#DOC
#DOC calls subroutines : oracle_command (from 'shared.pl')
#DOC
#DOC#################################################

sub insert_wfars_log {
    
    my ($reqid, $username, $format) = @_;
    my ($command);
    
    $command = "insert into $ENV{LOG_TABLE} values
                ('$reqid','$username','$format',SYSDATE,'')";
    
    oracle_command($command);
    
}

#DOC#################################################
#DOC
#DOC subroutine request_autodrm
#DOC
#DOC author - M Evans
#DOC date - October 2003
#DOC
#DOC purpose : Send AutoDRM request 
#DOC
#DOC input : @input - Array containing concatonated request data
#DOC
#DOC output : $e_mail - AutoDRM formated output e-mail
#DOC
#DOC internal : $request - SQL request
#DOC            $line - Used to loop over each @input element
#DOC            $reqid - Request ID number
#DOC            $center - Data center
#DOC            $net - Network code
#DOC            $sta - Station code
#DOC            $chan - Channel code
#DOC            $begin - Start time of waveform ('DD-MM-YYYY HH24:MI:SS' format)
#DOC            $end - End time of waveform ('DD-MM-YYYY HH24:MI:SS' format)
#DOC            $begin1 - Start time of waveform ('YYYY/MM/DD HH24:MI:SS' format)
#DOC            $end1 - End time of waveform ('YYYY/MM/DD HH24:MI:SS' format)
#DOC
#DOC environment variables : $ENV{RETURN_ADDRESS} - E-mail address to which reply should be sent
#DOC
#DOC calls subroutines : oracle_request (from 'shared.pl')
#DOC
#DOC#################################################

sub request_autodrm {

    my (@input) = @_;
    my ($e_mail, $request, $line);
    my ($reqid, $center, $net, $sta, $chan, $begin, $end);
    
    ($reqid, $center, $net, $sta, $chan, $begin, $end) = split(/::/,$input[0]);
    
    $request = "select to_char( to_date('$begin','DD-MM-YYYY HH24:MI:SS') , 'YYYY/MM/DD HH24:MI:SS') from dual";
    ($begin) = oracle_request($request);
        
    $request = "select to_char( to_date('$end','DD-MM-YYYY HH24:MI:SS') , 'YYYY/MM/DD HH24:MI:SS') from dual";
    ($end)   = oracle_request($request);

    
    $e_mail .= "BEGIN GSE2.0\n";
    $e_mail .= "E-MAIL $ENV{RETURN_ADDRESS}\n";
    $e_mail .= "MSG_TYPE REQUEST\n";
    $e_mail .= "MSG_ID $reqid\n";
    $e_mail .= "TIME $begin TO $end\n";
    $e_mail .= "STA_LIST ";
    
    foreach $line (@input) {
    
        ($reqid, $center, $net, $sta, $chan, $begin, $end) = split(/::/,$line);
        
        $e_mail .= "$sta ";
    
    }
    
    $e_mail .= "\nCHAN_LIST $chan\n";
    $e_mail .= "WAVEFORM GSE2.0 CM6\n";
    $e_mail .= "STOP\n";
    
    return $e_mail;

}

#DOC#################################################
#DOC
#DOC subroutine request_breqfast
#DOC
#DOC author - M Evans
#DOC date - October 2003
#DOC
#DOC purpose : Send breqfast request 
#DOC
#DOC input : @input - Array containing concatonated request data
#DOC
#DOC output : $e_mail - NetDC formated output e-mail
#DOC
#DOC internal : $request - SQL request
#DOC            $line - Used to loop over each @input element
#DOC            $reqid - Request ID number
#DOC            $center - Data center
#DOC            $net - Network code
#DOC            $sta - Station code
#DOC            $chan - Channel code
#DOC            $begin - Start time of waveform ('DD-MM-YYYY HH24:MI:SS' format)
#DOC            $end - End time of waveform ('DD-MM-YYYY HH24:MI:SS' format)
#DOC            $begin1 - Start time of waveform ('YYYY MM DD HH24 MI SS' format)
#DOC            $end1 - End time of waveform ('YYYY MM DD HH24 MI SS' format)
#DOC
#DOC environment variables : $ENV{RETURN_ADDRESS} - E-mail address to which reply should be sent
#DOC
#DOC calls subroutines : oracle_request (from 'shared.pl')
#DOC
#DOC#################################################

sub request_breqfast {
    
    my (@input) = @_;
    my ($e_mail, $request, $line);
    my ($reqid, $center, $net, $sta, $chan, $begin, $end);
    my ($begin1, $end1);
    
    ($reqid, $center, $net, $sta, $chan, $begin, $end) = split(/::/,$input[0]);
    
    $request = "select to_char( to_date('$begin','DD-MM-YYYY HH24:MI:SS') , 'YYYY MM DD HH24 MI SS') from dual";
    ($begin1) = oracle_request($request);
        
    $request = "select to_char( to_date('$end','DD-MM-YYYY HH24:MI:SS') , 'YYYY MM DD HH24 MI SS') from dual";
    ($end1)   = oracle_request($request);

    if (substr($chan,2,1) eq "*") {
      substr($chan,2,1) = "?";
    }
    
    $e_mail .= ".NAME WFARS\n";
    $e_mail .= ".INST ISC\n";
    $e_mail .= ".MAIL ISC, Pipers Lane, Thatcham, Berks, UK\n";
    $e_mail .= ".EMAIL $ENV{RETURN_ADDRESS}\n";
    $e_mail .= ".PHONE (+44) 1635 861022\n";
    $e_mail .= ".LABEL $reqid\n";
    $e_mail .= ".MEDIA Electronic\n";
    $e_mail .= ".END\n";
    
    foreach $line (@input) {
    
        ($reqid, $center, $net, $sta, $chan, $begin, $end) = split(/::/,$line);
        
        $e_mail .= "$sta $net $begin1 $end1 1 $chan\n";
    
    }
        
    return $e_mail;

}

#DOC#################################################
#DOC
#DOC subroutine request_netdc
#DOC
#DOC author - M Evans
#DOC date - October 2003
#DOC
#DOC purpose : Send NetDC request 
#DOC
#DOC input : @input - Array containing concatonated request data
#DOC
#DOC output : $e_mail - NetDC formated output e-mail
#DOC
#DOC internal : $request - SQL request
#DOC            $line - Used to loop over each @input element
#DOC            $reqid - Request ID number
#DOC            $center - Data center
#DOC            $net - Network code
#DOC            $sta - Station code
#DOC            $chan - Channel code
#DOC            $begin - Start time of waveform ('DD-MM-YYYY HH24:MI:SS' format)
#DOC            $end - End time of waveform ('DD-MM-YYYY HH24:MI:SS' format)
#DOC            $begin1 - Start time of waveform ('YYYY MM DD HH24 MI SS' format)
#DOC            $end1 - End time of waveform ('YYYY MM DD HH24 MI SS' format)
#DOC
#DOC environment variables : $ENV{RETURN_ADDRESS} - E-mail address to which reply should be sent
#DOC
#DOC calls subroutines : oracle_request (from 'shared.pl')
#DOC
#DOC#################################################

sub request_netdc {
    
    my (@input) = @_;
    my ($e_mail, $request, $line);
    my ($reqid, $center, $net, $sta, $chan, $begin, $end);
    my ($begin1, $end1);
    
    ($reqid, $center, $net, $sta, $chan, $begin, $end) = split(/::/,$input[0]);
    
    $request = "select to_char( to_date('$begin','DD-MM-YYYY HH24:MI:SS') , 'YYYY MM DD HH24 MI SS') from dual";
    ($begin1) = oracle_request($request);
        
    $request = "select to_char( to_date('$end','DD-MM-YYYY HH24:MI:SS') , 'YYYY MM DD HH24 MI SS') from dual";
    ($end1)   = oracle_request($request);

    
    $e_mail .= ".NETDC_REQUEST\n";
    $e_mail .= ".NAME WFARS\n";
    $e_mail .= ".INST ISC\n";
    $e_mail .= ".EMAIL $ENV{RETURN_ADDRESS}\n";
    $e_mail .= ".LABEL $reqid\n";
    $e_mail .= ".MEDIA FTP\n";
    $e_mail .= ".END\n";
    
    foreach $line (@input) {
    
        ($reqid, $center, $net, $sta, $chan, $begin, $end) = split(/::/,$line);
        
        $e_mail .= ".DATA $center $net $sta * $chan \"$begin1\" \"$end1\"\n";
    
    }
        
    return $e_mail;

}

#DOC#################################################
#DOC
#DOC subroutine update_req_status
#DOC
#DOC author - M Evans
#DOC date - October 2003
#DOC
#DOC purpose : Update REQ_STATUS field 
#DOC
#DOC input : $reqid - Request ID number
#DOC         $sta - Station code
#DOC         $check - New REQ_STATUS value, ('R')
#DOC
#DOC output : none
#DOC
#DOC internal : $command - String containing SQL command
#DOC
#DOC environment variables : $ENV{SOURCE_TABLE} - Table containing waveform request data
#DOC
#DOC calls subroutines : oracle_command (from 'shared.pl')
#DOC
#DOC#################################################


sub update_req_status {
        
    my ($reqid, $sta, $check) = @_;
    my ($command);
    
    $command = "update $ENV{SOURCE_TABLE}
                set REQ_STATUS = '$check'
                where STA = '$sta'
                and REQID = '$reqid'";

    oracle_command($command);
    
}

#DOC#
#DOC##
#DOC### EOF
#DOC##
#DOC#