#!C:/Perl5/bin/perl.exe
##!/usr/local/bin/perl
################################################################################
#
#       Module:         video_db1.pl
#       Author:         Peter R. Schmidt
#       Web Address:    http://www.prstech.com
#       e-Mail:         peter@prstech.com
#       Description:    Video Web-to-Database Program
#
#   Change Log
#
#     Date      Person              Description
#
#   01/12/99    Peter Schmidt       Started program
#
################################################################################
#
#   Functions defined in this program are:
#
#   main
#   initialize_record_variables
#   initialize_global_variables
#   display_heading0
#   ReadParse
#   MethGet
#   MethPost
#   display_heading1
#   display_menu1
#   query_mode_1
#   query_mode_2
#   display_form_1
#   open_temp_sql_file
#   run_sql_statmt1
#   sql_stdout
#   delete_mode_1
#   modify_mode_2
#   modify_mode_1
#   add_mode_1
#   add_mode_2
#   main_menu
#   display_end
#   initialize_custom_variables
#   display_type_code_options
#   get_rand_num
#   construct_char
#   construct_numeric
#   display_menu2
#   populate_form_variables
#
################################################################################
#   MAIN
################################################################################

main: {
    
    &initialize_custom_variables;       #  Initialize variables that are unique to this machine 
    &initialize_global_variables;       #  Initialize global variables 
    
    &display_heading0;              #  Display a standard heading at the top of every page
    
    &ReadParse(*input);             # Read in all the variables set by the form
                                    # Place them into the ${input} associative array
                            
#   $input{STAGE} = 2;                                  # for debugging
#   $input{TAPE_NUM} = 5;                               # for debugging
#   print "Debug: The stage is $input{STAGE}<BR>\n";    # for debugging

    if ($input{STAGE} == 0) {
        &main_menu();               # Main Menu - (Query/Add)
    }

    if (($input{STAGE} == 1) || ($input{STAGE} == 8) ) {
        &add_mode_1();              # Add Mode 1 - collect info - new/same
    }

    if ($input{STAGE} == 2) {
        &query_mode_1();            # QBE mode 1 - get selection criteria
    }

    if ($input{STAGE} == 3) {
        &add_mode_2();              # Add Mode 2 - insert into database
    }

    if ($input{STAGE} == 4) {
        &query_mode_2();            # QBE Mode 2 - display pick list of records selected
    }

    if ($input{STAGE} == 5) {
        &modify_mode_1();           # Modify Mode 1 - get desired changes
    }

    if ($input{STAGE} == 6) {
        &modify_mode_2();           # Modify Mode 2 - update the record
    }

    if ($input{STAGE} == 7) {
        &delete_mode_1();           # Delete Mode - delete the record
    }
        
    &display_end();                 # Display a standard end of page

}

################################################################################
#   Initialize variables that are unique to this machine and this environment
################################################################################

sub initialize_custom_variables {

    $PROG_NAME          = "video_db1.pl";
    $CGI_DIR            = "";
    $SQL_RUNNER_PATH    = "C:/www/cgi-bin/";
    $SQL_RUNNER_NAME    = "sql_runner.bat";
    $TMPDIR             = "C:/temp/";
    $USER_NAME          = "Peter Schmidt";
    $UNIX               = 0;
    
#   $ENV{'INFORMIXSERVER'}  = "ol_demo1";
#   $ENV{'INFORMIXDIR'}     = "D:\\Informix.730";
#   $ENV{'PATH'}            = "D:\\Informix.730\\bin;%PATH%;";  ## << CAUSES PERL TO DIE UNDER NT
    
}

################################################################################
#   Initialize global variables (usually called once at beginning of program)
################################################################################

sub initialize_global_variables {

    $FALSE = 0;
    $TRUE  = 1;

    $DATABASE           = "video";
    $TABLE_NAME1        = "vhs";

    @TYPE_CODE_LIST1 = (
        "MOVIE",
        "DS9",
        "ST1",
        "ST2",
        "VOY",
        "HOME",
        "MAX",
        "OTHER"
    );
    
    @TYPE_CODE_LIST2 = (
        "Movies",
        "Deep Space 9",
        "Star Trek \(Original\)",
        "Star Trek the Next Generation",
        "Star Trek Voyager",
        "Home Movies",
        "Max Headroom",
        "Other"
    );
    
}

################################################################################
#   Display a standard heading at the top of every page
################################################################################

sub display_heading0 {

print <<VIDEO_TAG;
Content-type: text/html\n\n

<HTML>
<TITLE>${USER_NAME}'s Video Database</TITLE> 
<BODY BGCOLOR="#ffffff"> 
<FONT FACE="ARIAL"> 
    
VIDEO_TAG

}

##################################################################################
#
# Function: ReadParse
# Author:   Peter R. Schmidt
#
# Reads in GET or POST data, converts it to unescaped text,
# creates key/value pairs in %in, using '\0' to separate multiple selections
#
# Returns TRUE if there was input, FALSE if there was no input
#
##################################################################################

sub ReadParse {

  local (*in) = @_ if @_;
  local ($i, $key, $val);

  # Read in text
  if (&MethGet) {
    $in = $ENV{'QUERY_STRING'};
  } elsif (&MethPost) {
    read(STDIN,$in,$ENV{'CONTENT_LENGTH'});
  }

  @in = split(/[&;]/,$in);          # Split into tokens, delimiter = & or ;

  foreach $i (0 .. $#in) {          # Foreach element in the $in array...
    
    $in[$i] =~ s/\+/ /g;            # Convert plus's to spaces

    # Split into key and value.
    ($key, $val) = split(/=/,$in[$i],2);    # splits on the first = (equals sign)

    # Convert %XX from hex numbers to alphanumeric
    $key =~ s/%(..)/pack("c",hex($1))/ge;
    $val =~ s/%(..)/pack("c",hex($1))/ge;

    # Associate key and value - Build the associative array
    $in{$key} .= "\0" if (defined($in{$key})); # \0 is the multiple separator
    $in{$key} .= $val;
    
  }

  return scalar(@in);
}

##################################################################################
# MethGet
# Return true if this cgi call was using the GET request, false otherwise
################################################################################

sub MethGet {
  return ($ENV{'REQUEST_METHOD'} eq "GET");
}

##################################################################################
# MethPost
# Return true if this cgi call was using the POST request, false otherwise
################################################################################

sub MethPost {
  return ($ENV{'REQUEST_METHOD'} eq "POST");
}

################################################################################
#   Display the main menu - (Query/Add)
################################################################################

sub main_menu {

&display_heading1("Main Menu");

&display_menu1;                         # Menu: Query, Add-New
    
}

################################################################################
#   Display a standard sub-heading (2nd line) at the top of every page
################################################################################

sub display_heading1 {

local ($HEADING_STR) = @_;

print <<VIDEO_TAG;

<FONT COLOR="#0000A0" FACE="ARIAL">
<b><center><h1>${USER_NAME}'s Video Database</h1></b>
</FONT></center>

<FONT COLOR="#000000" FACE="ARIAL">
<b><center><h2>$HEADING_STR</h2></b>
</FONT>
    
</center>
    
<HR SIZE=4 NOSHADE>
<BR>
        
VIDEO_TAG

}

################################################################################
#
#   Function:       display_menu1
#   Author:         Peter R. Schmidt
#   Description:    Display menu buttons for Query, Add-New and Add-Same
#                   Only display Add-Same if the TAPE_NUM is already populated
#
################################################################################

sub display_menu1 {
    
print <<VIDEO_TAG;
<center>
<table border=0 cellspacing=0 cellpadding=0> 
<tr>
<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}">
        <input type=submit name="ACTION" value="Query">
        <input type=hidden name="STAGE"  value="2">
    </FORM>
</center></td>

<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
        <input type=submit name="ACTION" value="Add-New">
        <input type=hidden name="STAGE"  value="1">
    </FORM>
</center></td>
VIDEO_TAG

if ( exists ($input{TAPE_NUM}) ) {
print <<VIDEO_TAG;
<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
        <input type=submit name="ACTION"    value="Add-Same">
        <input type=hidden name="STAGE"     value="8">
        <input type=hidden name="TYPE_CODE" value="$input{TYPE_CODE}">
        <input type=hidden name="TAPE_NUM"  value="$input{TAPE_NUM}">
    </FORM>
</center></td>
VIDEO_TAG
}

print <<VIDEO_TAG;
</tr>
</table>
</center>
VIDEO_TAG

}

################################################################################
#   QBE mode 1 - get selection criteria
#   Display the form for a QBE and get selection criteria
################################################################################

sub query_mode_1 {

&display_heading1("Query");
    
print <<VIDEO_TAG;

<FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 

<FONT COLOR="#000000" FACE="ARIAL">
<b><center><h2>Please enter your selection criteria</h2></b></FONT></center>
    
VIDEO_TAG

&initialize_record_variables(1);        # Initialize variables that make up the record

&display_form_1("QBE");

print <<VIDEO_TAG;

<center>
        <input type=submit name="ACTION"    value="Submit Query">
        <input type=hidden name="STAGE"     value="4">
        <input type=button name="ACTION"    value="Cancel"
            onClick="history.back()">
        <input type=reset  name="ACTION"    value="Reset">
</FORM>
</center>

<center>
<FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}">
    <input type=submit name="ACTION" value="Main Menu">
    <input type=hidden name="STAGE" value="0">
</FORM>

</center>

VIDEO_TAG

}

################################################################################
#
#   Function:       query_mode_2
#   Author:         Peter R. Schmidt
#   Description:    Execute a SQL query and display a pick list of records selected
#
################################################################################

sub query_mode_2 {

    local $ITEM_NUM=0;
    local $SERIAL_LIST="";

    &open_temp_sql_file;            # Open the temp SQL output file
    
    #------------------------------------------------------------------------------------
    #       Create the WHERE clause
    
    $CNT_WHERE=0;
    $WHERE="";
    
    if (length($input{SERIAL_LIST}) > 0 ) {
                    $WHERE = construct_numeric("serial_id",$input{SERIAL_LIST});    # Do serial_id  
    } else {
        foreach $COL_NAME (sort keys(%input)) {
            if (length ($input{$COL_NAME}) && $COL_NAME !~ /STAGE|ACTION/ ) {
                $CNT_WHERE++;
                if ($CNT_WHERE > 1) {
                    $WHERE .= " and\n";
                }
                if ( $COL_NAME =~ /TYPE_CODE|TITLE|COMMENT/) {
                    $WHERE .= construct_char($COL_NAME,$input{$COL_NAME});      # Do character columns
                }

                if ( $COL_NAME =~ /TAPE_NUM|HOURS/) {
                    $WHERE .= construct_numeric($COL_NAME,$input{$COL_NAME});   # Do numeric columns
                }
            }
        }
    }

    #------------------------------------------------------------------------------------
    #       Format the SQL statement - write it to the temp file

    $SQL  = "-- Select a record from the $TABLE_NAME1 table\n";
    $SQL .= "unload to ${SQL_FILENAME3}\n";
    $SQL .= "select * from $TABLE_NAME1\n";
    
    if (length($WHERE) > 0) {
        $SQL .= "where $WHERE\n";
    }
    
    $SQL .= "order by tape_num;\n";
    
    #print "DEBUG: SQL=${SQL}<BR>\n";
    print (TEMP_SQL "$SQL\n");              #   Write the SQL statement to the temp file
    close TEMP_SQL;                         #   Close the temp file

    #------------------------------------------------------------------------------------
    #       Execute using the temp SQL output file
    
    &run_sql_statmt1;                       #   Execute using the temp SQL output file
    
    #------------------------------------------------------------------------------------
    #       Display Output
        
    &display_heading1("Query Results");
    
    if (&sql_stdout) {                      #   Display the stdout of the SQL statement
        return;
    }                   

    #------------------------------------------------------------------------------------
    #       Query Results - display the pick list
    
    if ($CNT_FOUND > 0) {
    
print <<VIDEO_TAG;  
<BR>
<CENTER><table border=5 cellspacing=0 cellpadding=5 bgcolor=silver>
<tr BGCOLOR="teal">
<td><b><center>Type Code</center></b></td>
<td><b><center>Tape Number</center></b></td>
<td><b><center>Title</center></b></td>
<td><b><center>Hours</center></b></td>
<td><b><center>Comment</center></b></td>
<td><b><center>Modify</center></b></td>
</tr>
VIDEO_TAG

        $CNT_FOUND=0;
        $SERIAL_MAX=0;
        $SERIAL_LIST="";

        #----------------------------------------------------------------------------------------------
        #  I know it looks redundent to parse this list twice in a row, but actually it's better
        #  this way. The first pass creates the $SERIAL_LIST and $SERIAL_MAX variables which are
        #  used in the second pass.
        #----------------------------------------------------------------------------------------------
        
        foreach $SQL_ROW (@UNLOAD_LIST) {           # Build list of serial ID's from @UNLOAD_LIST array
            $SERIAL_MAX++;                          # Count number of rows
            foreach $ITEM (split(/\|/,$SQL_ROW)) {  # Split pipe-delimited list into individual items
                $SERIAL_LIST .= $ITEM . "\|";       # Create pipe-delimited list of serial_ids
                last;                               # Only need first item in each row (containing serial_id)
            }
        }

        #----------------------------------------------------------------------------------------------
        #  Go thru the list again, this time create a row in the table for every row in the unload list.
        #----------------------------------------------------------------------------------------------

        foreach $SQL_ROW (@UNLOAD_LIST) {
            $CNT_FOUND++;
            print "<tr>\n";                         # New table row
            $ITEM_NUM=0;
            foreach $ITEM (split(/\|/,$SQL_ROW)) {  # Parse the pipe-delimited unload record
                $ITEM_NUM++;
                if ( $ITEM_NUM <= 1 || $ITEM_NUM >= 7 ) { next; } 
                if (length($ITEM) == 0) {
                    print "<td>&nbsp</td>\n";       # If element is empty, print a non-breaking space.
                } else {
                    print "<td>$ITEM</td>\n";       # Table data
                }
            }
            
print <<VIDEO_TAG;  
<td>
<FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
    <input type=submit name="ACTION"       value="Modify">
    <input type=hidden name="SERIAL_LIST"  value="$SERIAL_LIST">        
    <input type=hidden name="SERIAL_PICK"  value="$CNT_FOUND">                      
    <input type=hidden name="SERIAL_MAX"   value="$SERIAL_MAX">                     
    <input type=hidden name="STAGE"        value="5">       
</FORM>     
</td>
VIDEO_TAG

        }

        print "</table></CENTER>\n";            # end of table
    }
    
    #------------------------------------------------------------------------------------

    print "<BR>\n";

    if ($CNT_FOUND == 0) {
        print "<font color=red>\n";             # display "0 records found" in red color
    }

    #------------------------------------------------------------------------------------
    #  XXX records found (XXX may = 0)
    
    print "<center><H2>$CNT_FOUND records(s) selected</H2></center><BR>\n";
    
    if ($CNT_FOUND == 0) {
        print "</font>\n";
    }

    #------------------------------------------------------------------------------------

    print "<HR SIZE=4 NOSHADE>\n";
    print "<BR>\n";
    
    &display_menu1;                     # Menu: Query, Add-New, Add-Same

    print "<BR>\n";
    print "<HR SIZE=4 NOSHADE>\n";
    print "<BR>\n";

    print "Criteria:<BR>\n";
    print "$WHERE<BR><BR>\n";           # Display criteria used to get this list
}

################################################################################
#
#   Function:       initialize_record_variables
#   Author:         Peter R. Schmidt
#   Description:    Initialize the variables in the $input assocative array 
#                   representing the fields of the record
#
#   Arg 1  = 1 = NEW  - initialize all variables
#   Arg 1 != 1 = SAME - do not initialize all variables
#
################################################################################

sub initialize_record_variables {

    local ($NEW_OR_SAME) = @_;          # 1 = NEW, anything else = SAME

    if ($NEW_OR_SAME == 1) {            # if ADD-SAME, do not initialize all
        $input{TYPE_CODE}   = "";       # if ADD-NEW, initialize all
        $input{TAPE_NUM}    = "";
    }

    $input{SERIAL_ID}   = 0;
    $input{TITLE}       = "";
    $input{HOURS}       = "";
    $input{COMMENT}     = "";
}

################################################################################
#
#   Function:       display_form_1
#   Author:         Peter R. Schmidt
#   Description:    Display the main form with variables filled in
#
#   Input Arg 1:    Mode (ADD, QBE, MODIFY)
#
################################################################################

sub display_form_1 {

local ($DISPLAY_MODE) = @_;         # ADD, QBE, MODIFY

print "\n";                         # Newline for readability

if ( $DISPLAY_MODE ne "QBE" ) {     # If QBE, do not verify input with javascript

print <<VIDEO_TAG;

<SCRIPT LANGUAGE=\"JavaScript\">

defaultStatus = "Peter Schmidt's Video Database";       // Populate banner at bottom of form

/////////////////////////////////////////////////////////////////////////////////////////
// This function will verify the cancel request

function verify_cancel (new_url) {

    var msg="";

    msg  = "________________________________________________________\\n\\n"
    msg += "Are you sure you wish to EXIT this form without saving ?\\n\\n";
    msg += "\\tClick OK to EXIT without saving.\\n\\n";
    msg += "\\tClick CANCEL to return to form.\\n";
    msg += "________________________________________________________\\n"

    if (confirm(msg)) {
        if ( new_url == "BACK")
            history.back();
        else 
            location.replace(new_url);
    }
    
}

/////////////////////////////////////////////////////////////////////////////////////////
// This function will redirect to the provided URL

function goto_url (s) {
    location=s;
}

///////////////////////////////////////////////
// Return true if numeric character

function isDigit (c)
{   return ((c >= "0") && (c <= "9")) }

/////////////////////////////////////////////////////////////////////////////////////////
// A utility function that returns true if a strings contains only whitespace characters.

function isblanks(s) {

    for (var i = 0; i < s.length; i++) {
        var c = s.charAt(i);
        if ((c != ' ') && (c != '\\n') && (c != '\\t')) return false;
    }
    return true;
}

/////////////////////////////////////////////////////////////////////////////////////////
//  Verify input fields

function verify(f) {

    var bad_fields  = "";
        
    for (var i = 0; i < f.length; i++) {
    
        var e = f.elements[i];
                
        //////////////////////////////////////////////
        // Check character variables for blank or null
        
        if (e.name == "TITLE") {
             
                if ( (e.value == null) || (e.value == "") || isblanks(e.value)) {
                    bad_fields += "Title is required\\n";
                }
                continue;
        }

        ////////////////////////////////////////////////////
        // Check pull-down lists for non-default selections
        
        if ( (e.name == "TYPE_CODE") ) {
                            
                if ( (e.options[0].selected == true) && (e.options[0].text == "Choose Type of Video") ) {
                    bad_fields += "Type of Video is required\\n";
                }
                continue;
        }   

        ////////////////////////////////////////////////////
        // Check numeric fields
        
        if ( (e.name == "TAPE_NUM")  ||
             (e.name == "HOURS")     ) {
        
            if (e.name == "TAPE_NUM") { f_desc = "Tape Number" }
            if (e.name == "HOURS")    { f_desc = "Hours" }
             
            if (e.name == "TAPE_NUM") {     // Check required numeric fields 
                if ( (e.value == null) || (e.value == "") || isblanks(e.value) ) {
                        bad_fields += f_desc + " is required\\n";
                        continue;
                }
            }

            for (x = 0; x < e.value.length; x++) {  
                    if (!isDigit(e.value.charAt(x))) {  // Check that current character is number.
                        if (e.name == "HOURS") {        
                            if (e.value.charAt(x) == ".") { continue; }  // Allow decimal point
                        }
                        bad_fields += f_desc + " must contain only numeric digits (" + e.value + ")\\n";
                        break;
                    }
            }
            continue;
        }   
    }
        
    ////////////////////////////////////////////////////////////////////////////////
    // Display error window if needed
    
    if (!bad_fields) return true;
    
    msg  = "________________________________________________\\n\\n";
    msg += "The form was not submitted because the following fields are\\n";
    msg += "required or invalid. Please complete these fields and resubmit.\\n";
    msg += "________________________________________________\\n\\n";
    msg += bad_fields + "\\n";
    
    alert(msg);
    return false;
}

////////////////////////////////////////////////////////////////////////
// The end of javascript functions
</SCRIPT>
VIDEO_TAG
}

print <<VIDEO_TAG;

<center>

<table border=0 cellspacing=0 cellpadding=0> 

<tr>
    <td>
        <font face="arial"><B>Type:</b>
    </td>
    
    <td>
VIDEO_TAG

&display_type_code_options("TYPE_CODE",$input{TYPE_CODE});
        
print <<VIDEO_TAG;
    </td>
</tr>

<tr>
    <td>
        <font face="arial"><B>Tape Number:</b>
    </td>
    
    <td>
        <INPUT type="text" name="TAPE_NUM" size=10 maxlength=10 value="$input{TAPE_NUM}">
    </td>
</tr>

<tr>
    <td>
        <font face="arial"><B>Title:</b>
    </td>
    
    <td>
        <INPUT type="text" name="TITLE" size=50 maxlength=50 value="$input{TITLE}">
    </td>
</tr>

<tr>
    <td>
        <font face="arial"><B>Hours:</b>
    </td>
    
    <td>
        <INPUT type="text" name="HOURS" size=10 maxlength=10 value="$input{HOURS}">
    </td>
</tr>

<tr>
    <td>
        <font face="arial"><B>Comment:</b>
    </td>
    
    <td>
        <INPUT type="text" name="COMMENT" size=50 maxlength=50 value="$input{COMMENT}">
    </td>
</tr>

</table>

</center>

<BR>

VIDEO_TAG

}

################################################################################
#
#   Function:       open_temp_sql_file
#   Author:         Peter R. Schmidt
#   Description:    Open a temp output file used to hold a SQL statement
#                   Create other related filenames used in a query
#
################################################################################

sub open_temp_sql_file {

    $RAND=&get_rand_num(9999);                          # Get a ramdom number
    
    $SQL_FILENAME1 = "> ${TMPDIR}tmp_sql$RAND.sql";     # Create a name that will open for writing
    
    $SQL_FILENAME2 = "${TMPDIR}tmp_sql$RAND.sql";       # Filename without the ">" sign
    
    $SQL_FILENAME3 = "${TMPDIR}tmp_sql$RAND.unl";       # Create Name of a ".unl" Unload File 
    $SQL_FILENAME3 =~ s/\//\\\\/g;                      # Convert forward slashes to back-slashes
    
    $SQL_FILENAME4 = $SQL_FILENAME3;                    # Start out with the .unl filename
    $SQL_FILENAME4 =~ s/unl/out/g;                      # Replace ".unl" with ".out" (results file)
    
    #print "DEBUG: TEMP SQL FILENAME IS ($SQL_FILENAME1)<BR>\n";

    open (TEMP_SQL,$SQL_FILENAME1)
          || print "Can't open temp sql output file: $SQL_FILENAME1 in shelter_query2.pl<BR>\n$!<BR>\n";
}
    
################################################################################
#
#   Function:       run_sql_statmt
#   Author:         Peter R. Schmidt
#   Description:    Execute the SQL statement in the temp SQL output file   
#                   Populate Perl array @SQL_DATA with the results
#
#   This program uses the Perl "eval" function, which executes a mini-perl
#   program, that is contained in a variable (in this case $RUNTXT). Then, to make
#   things even more confusing, the statement being executed contains another statement
#   surrounded by back-ticks, which works just like a unix shell-script (which executes
#   the statement surrounded by back-ticks - in this case "sql_runner.bat").
#
#   "sql_runner.bat" is a simple DOS batch program.  
#   The batch program expects the following input arguments:
#
#   Arg 1:  The name of the database
#   Arg 2:  The filename of the file containing the SQL statement(s) to execute
#   Arg 3:  The filename of the .unl file that the SQL statement unload into.
#           Obviously this is only used if the SQL statement does an "unload"
#           but you always need to supply a name here anyway as a "placeholder"
#   Arg 4:  The filename that the STDOUT/STDERR will go into when the SQL is executed.
#           This would typically be a ".out" file.
#
#   sql_runner.bat simply runs "dbaccess" to execute the SQL statement in Arg 2.
#   It then just "displays" the resulting STDOUT/STDERR of dbaccess followed by
#   the file containing the unloaded data (in Arg 3) to it's own STDOUT.
#   
#   The net result is that the perl array $SQL_DATA contains one element for every 
#   line of STDOUT/STDERR followed by one element for line of "unloaded" pipe-delimited
#   data.
#
#   I'm sure there are many other (better?) ways to skin this cat, but this one 
#   is really simple and works nicely. What more can a developer ask?
#
#   Here is the entire text of "sql_runner.bat". 
#   Notice it deletes the temp files that were created.
#   Also, note that STDERR is redirected to STDOUT.
#
#       @echo off
#       
#       REM Arg1: Database Name
#       REM Arg2: Filename containing SQL script
#       REM Arg3: Filename of ".unl" file (if any)
#       REM Arg4: Filename to write stdout/stderr to
#       
#       set INFORMIXDIR=D:\Informix.730
#       set INFORMIXSERVER=ol_mr_dell
#       set ONCONFIG=ONCONFIG.ol_mr_dell
#       set PATH=D:\Informix.730\bin;%PATH%;
#
#       dbaccess %1 %2 > %4 2>&1
#
#       if not exist %4 goto TAG4
#       type %4
#       erase %4
#       :TAG4
#       if not exist %3 goto TAG3
#       type %3
#       erase %3
#       :TAG3
#
################################################################################

sub run_sql_statmt1 {

#   $RUNTXT  = join("","\@SQL_DATA=\`set\`  ");     # << Use this to see all environmentals

    $RUNTXT  = join("",
            "\@SQL_DATA=\`${SQL_RUNNER_PATH}${SQL_RUNNER_NAME} ",
            "${DATABASE} ",
            "${SQL_FILENAME2} ",                    # .SQL statement
            "\"${SQL_FILENAME3}\" ",                # .UNL filename (May not be used)
            "\"${SQL_FILENAME4}\" ",                # .OUT filename (STDOUT/STDERR)
            "\`  ");                                # End with a trailing back-tick

    #print "DEBUG: RUNTXT=${RUNTXT}<BR>\n";
        
    eval $RUNTXT;       # Execute the SQL statement, populate @SQL_DATA with the results

    if ($@) {           # I'm not sure when this would happen or even if it works
        print "Error: $@\nRUNTXT=$RUNTXT<BR>\n"; 
        return;
    } 
    
    if ($#SQL_DATA < 0) {
            print "Error: Informix statement failed<BR>\n";
            print "SQL_DATA = $#SQL_DATA <BR>\n";
            print "RUNTXT = $RUNTXT<BR>\n";
    }

}

################################################################################
#
#   Function:       sql_stdout
#   Author:         Peter R. Schmidt
#   Description:    Display stdout (and stderr) of the SQL execution
#                   Populate the @UNLOAD_LIST array of records unloaded 
#                   from the @SQL_DATA array. 
#
#   If the data row contains a pipe symbol, I presume it is a "unloaded" record.
#   If it does not, I presume it is the STDOUT/STDERR from running dbaccess.
#   @UNLOAD_LIST should end up containing one pipe-delimited element for every 
#   row unloaded.
#
#   Return Arg 1:   $TRUE = error(s) found, $FALSE = No error found
#
################################################################################

sub sql_stdout {

    local $FLAG_ERROR=$FALSE;           # local variable
    
    $CNT_FOUND=0;                       # global variable
    $FLAG_INSERT=$FALSE;                # global variable
    $FLAG_UPDATE=$FALSE;                # global variable
    $FLAG_DELETE=$FALSE;                # global variable
    @UNLOAD_LIST = ();                  # global variable (initialize array)
    
    print "SQL Results:<BR>\n";
    
    foreach $SQL_ROW (@SQL_DATA) {
        if ($SQL_ROW eq "\n") { next; };                    # Exclude from displaying this line
        if ($SQL_ROW =~ /Database selected/i) { next; };    # Exclude from displaying this line
        if ($SQL_ROW =~ /Database closed/i)   { next; };    # Exclude from displaying this line

        if ($SQL_ROW =~ /\|/) {                 # Pipe delimited record ?
            push (@UNLOAD_LIST,$SQL_ROW);       # Push onto the list of unloaded records
            $CNT_FOUND++;                       # Count number of records unloaded
        } else {                                # else - must be STDOUT/STDERR
            if ($SQL_ROW =~ /Error/i) {         # Error found ?
                $FLAG_ERROR=$TRUE;              # Flag if error is found
            }
            if ($SQL_ROW =~ /1 row\(s\) inserted\./i) {     # Successful insert ?
                $FLAG_INSERT=$TRUE;
            }           
            if ($SQL_ROW =~ /1 row\(s\) updated\./i) {      # Successful update ?
                $FLAG_UPDATE=$TRUE;
            }           
            if ($SQL_ROW =~ /1 row\(s\) deleted\./i) {      # Successful delete ?
                $FLAG_DELETE=$TRUE;
            }           
            print "$SQL_ROW<BR>\n";             # Display this line to output
        }
    }
    
    if ($FLAG_ERROR) {                          # If error, display the SQL statment involved
        print "<BR>\nSQL Statement:<BR>\n";
        $SQL2 = $SQL;                           # Copy $SQL to $SQL2
        $SQL2 =~ s/\n/<BR>/g;                   # replace newlines with <BR>
        print "${SQL2}<BR>\n";
        print "<BR>\n";
        return $TRUE;                           # If error, do not delete the SQL temp file
    }

    unlink ($SQL_FILENAME2);                    # Delete the SQL temp file
    
    return $FALSE;                              # Good return (no errors)
    
}

################################################################################
#
#   Function:       delete_mode_1
#   Author:         Peter R. Schmidt
#   Description:    Delete the record
#
################################################################################

sub delete_mode_1 {

    &display_heading1("Delete record");

    &open_temp_sql_file;            # Open the temp SQL output file
    
    #------------------------------------------------------------------------------------
    #       Format a SQL statement

    $SQL  = "-- Delete a record from the $TABLE_NAME1 table\n";
    $SQL .= "delete from $TABLE_NAME1 \n";
    $SQL .= "where serial_id = \"$input{SERIAL_ID}\";\n";
    
    #print "DEBUG: SQL=${SQL}<BR>\n";
    print (TEMP_SQL "$SQL\n");              #   Write the SQL statement to the temp file
    close TEMP_SQL;
        
    #------------------------------------------------------------------------------------
    #       Execute using the temp SQL output file
    
    &run_sql_statmt1;                       #   Execute using the temp SQL output file
    
    if (&sql_stdout) {                      #   Display the stdout of the SQL statement
        return;
    }
    
    print "<center><h3>\n";
    
    if ($FLAG_DELETE) {
        print "<font color=red>Record Deleted</font>\n";
    } else {
        print "<font color=red>Warning: Record NOT Deleted</font>\n";
    }
    
    print "</center></h3>\n";
    
    $SERIAL_NEXT = ($input{SERIAL_PICK} + 1);       # Calculate new NEXT value
    $SERIAL_PREV = ($input{SERIAL_PICK} - 1);       # Calculate new PREVIOUS value

    &display_menu2;                                 # Menu: Next, Previous, First, Last, ...
    
print <<VIDEO_TAG;
    
    <center>
    
    <table border=0 cellspacing=5 cellpadding=0> 
    <tr>
        <td><center>
            <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}">
                <input type=submit name="ACTION" value="Main Menu">
                <input type=hidden name="STAGE" value="0">
            </FORM>
        </center></td>
    </tr>
    </table>
    </center>
    
VIDEO_TAG
    
}

################################################################################
#
#   Function:       modify_mode_2
#   Author:         Peter R. Schmidt
#   Description:    Update the record
#
################################################################################

sub modify_mode_2 {

    &open_temp_sql_file;            # Open the temp SQL output file
    
    #------------------------------------------------------------------------------------
    #       Format a SQL statement

    $SQL  = "-- Update a record from the $TABLE_NAME1 table\n";
    $SQL .= "update $TABLE_NAME1 set\n";
    $SQL .= "   type_code = \"$input{TYPE_CODE}\",\n";
    $SQL .= "   tape_num  = \"$input{TAPE_NUM}\",\n";
    $SQL .= "   title     = \"$input{TITLE}\",\n";
    $SQL .= "   hours     = \"$input{HOURS}\",\n";
    $SQL .= "   comment   = \"$input{COMMENT}\"\n";
    $SQL .= "where serial_id = \"$input{SERIAL_ID}\";\n";
    
    #print "DEBUG: SQL=${SQL}<BR>\n";
    print (TEMP_SQL "$SQL\n");              #   Write the SQL statement to the temp file
    close TEMP_SQL;
        
    #------------------------------------------------------------------------------------
    #       Execute using the temp SQL output file
    
    &run_sql_statmt1;                       #   Execute using the temp SQL output file
    
    if (&sql_stdout) {                      #   Display the stdout of the SQL statement
        return;
    }
    
    print "<center><h3>\n";
    
    if ($FLAG_UPDATE) {
        print "<font color=red>Record Updated</font>\n";
    } else {
        print "<font color=red>Warning: Record NOT Updated</font>\n";
    }
    
    print "</center></h3>\n";
    print "<HR SIZE=4 NOSHADE>\n";
    
    &modify_mode_1;
}

################################################################################
#
#   Function:       modify_mode_1
#   Author:         Peter R. Schmidt
#   Description:    Modify - update a row
#
################################################################################

sub modify_mode_1 {

    local $FLAG_DATA=$FALSE;

    &display_heading1("Modify");
    
    #------------------------------------------------------------------------------------
    #       Get the current serial_id from the passed serial_list
    
    #print "DEBUG: SERIAL_LIST: $input{SERIAL_LIST}<BR>\n";
    #print "DEBUG: SERIAL_PICK: $input{SERIAL_PICK}<BR>\n";
    
    @SERIAL_LIST = split(/\|/,$input{SERIAL_LIST});         # Convert SERIAL_LIST to Perl array
    $SERIAL_PICK = ($input{SERIAL_PICK} - 1);               # Offset by 1
    $input{SERIAL_ID} = $SERIAL_LIST[$SERIAL_PICK];         # Get Serial ID of current record

    #------------------------------------------------------------------------------------
    #       Write to the temp SQL output file the SQL statement to be executed

    &open_temp_sql_file;            # Open the temp SQL output file
    
    #------------------------------------------------------------------------------------
    #       Format a SQL statement

    $SQL  = "-- Select a record from the $TABLE_NAME1 table\n";
    $SQL .= "unload to ${SQL_FILENAME3}\n";
    $SQL .= "select * from $TABLE_NAME1\n";
    $SQL .= "where serial_id = \"$input{SERIAL_ID}\";\n";
    
    #print "DEBUG: SQL=${SQL}<BR>\n";
    print (TEMP_SQL "$SQL\n");              #   Write the SQL statement to the temp file
    close TEMP_SQL;
    
    #------------------------------------------------------------------------------------
    #       Execute using the temp SQL output file
    
    &run_sql_statmt1;                       #       Execute using the temp SQL output file
    
    #------------------------------------------------------------------------------------
    #       Display Output
        
    if (&sql_stdout) {                      # Display the stdout of the SQL statement
        return;
    }                   
    
    #------------------------------------------------------------------------------------
    #       What if "NOT FOUND" ?  

    if ($CNT_FOUND == 0) {
        print "<center><h3>\n";
        print "Warning: record not found for update!<BR>\n";
        print "(maybe it was just deleted)<BR>\n";
        print "serial_id was $input{SERIAL_ID}<BR>\n";
        print "</center></h3>\n";
        &display_menu2;                     # Menu: Next, Previous, First, Last, ...
        return;
    }
        
    #------------------------------------------------------------------------------------
    # Populate the $input associative array from the @UNLOAD_LIST array

    &populate_form_variables;       
    
    $SERIAL_NEXT = ($input{SERIAL_PICK} + 1);   # Calculate the relative position of NEXT
    $SERIAL_PREV = ($input{SERIAL_PICK} - 1);   # Calculate the relative position of PREVIOUS
    
    #------------------------------------------------------------------------------------
    # Display "Viewing XXX of YYY"

    print "<center><h3>Viewing $input{SERIAL_PICK} of $input{SERIAL_MAX}</h3></center>\n";
    
    &display_menu2;                             # Menu: Next, Previous, First, Last, ...
    
    print "<FORM METHOD=\"POST\" ACTION=\"${CGI_DIR}${PROG_NAME}\"\n ";
    print " onSubmit=\"return verify\(this\)\;\">\n ";
    
    &display_form_1("MODIFY");
        
print <<VIDEO_TAG;

<center>

<table border=0 cellspacing=5 cellpadding=0> 
<tr>
    <td><center>
                <input type=submit name="ACTION" value="Update">
                <input type=hidden name="SERIAL_LIST"  value="$input{SERIAL_LIST}">     
                <input type=hidden name="SERIAL_PICK"  value="$input{SERIAL_PICK}">                     
                <input type=hidden name="SERIAL_MAX"   value="$input{SERIAL_MAX}">                      
                <input type=hidden name="SERIAL_ID"    value="$input{SERIAL_ID}">                       
                <input type=hidden name="STAGE"        value="6">
        </FORM>
    </center></td>
    
    <td><center>
        <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
                <input type=submit name="ACTION" value="Delete">
                <input type=hidden name="SERIAL_LIST"  value="$input{SERIAL_LIST}">     
                <input type=hidden name="SERIAL_PICK"  value="$input{SERIAL_PICK}">                     
                <input type=hidden name="SERIAL_MAX"   value="$input{SERIAL_MAX}">                      
                <input type=hidden name="SERIAL_ID"    value="$input{SERIAL_ID}">                       
                <input type=hidden name="STAGE"        value="7">
        </FORM>
    </center></td>

    <td><center>
        <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}">
            <input type=submit name="ACTION" value="Main Menu">
            <input type=hidden name="STAGE" value="0">
        </FORM>
    </center></td>
</tr>
</table>
</center>

VIDEO_TAG

}

################################################################################
#
#   Function:       add_mode_1
#   Author:         Peter R. Schmidt
#   Description:    Collect info used to add a new record
#
################################################################################

sub add_mode_1 {

&display_heading1("Add New Records");

print "<FORM NAME=\"brief\" METHOD=\"POST\" ACTION=\"${CGI_DIR}${PROG_NAME}\"\n ";
print " onSubmit=\"return verify\(this\)\;\">\n ";

# Initialize records variables in the $input associative array

&initialize_record_variables($input{STAGE}); 

&display_form_1("ADD");             # Display the main form

print <<VIDEO_TAG;

<center>

<table border=0 cellspacing=0 cellpadding=0> 
<tr>
    <td><center>
        <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
            <input type=submit name="ACTION" value="Submit">
            <input type=hidden name="STAGE" value="3">
        </FORM>
    </center></td>
</tr>

<tr>
    <td><center>
        <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}">
            <input type=submit name="ACTION" value="Main Menu">
            <input type=hidden name="STAGE" value="0">
        </FORM>
    </center></td>
</tr>
</table>
</center>
VIDEO_TAG

if ($input{STAGE} == 8) {
print <<VIDEO_TAG;
    <SCRIPT LANGUAGE="JavaScript">
    document.brief.TITLE.focus();
    </SCRIPT>
VIDEO_TAG
}

}

################################################################################
#
#   Function:       add_mode_2 
#   Author:         Peter R. Schmidt
#   Description:    Insert a new record into the database
#
################################################################################

sub add_mode_2 {

    local $FLAG_DATA=$FALSE;

    &display_heading1("Add Results");

    &open_temp_sql_file;            # Open the temp SQL output file

    #-----------------------------------------------------------------------------------
    #   Create and write the SQL statement to be executed to the temp SQL output file 
    
    $SQL  = "-- Insert a record into $TABLE_NAME1\n";
    $SQL .= "insert into $TABLE_NAME1 ( \n serial_id";

    foreach $COL_NAME (sort keys(%input)) {                 # For each item filled in on the form
        if (length ($input{$COL_NAME})  && 
            $COL_NAME !~ /ACTION|STAGE/ &&                  # Excluding these items
            $input{$COL_NAME} !~ /Choose Type of Video/ ) {
                $FLAG_DATA=$TRUE;
                $SQL .= ",\n " . lc("$COL_NAME");           # List the column names
        }
    }

    $SQL .= "\n ) VALUES ( \n \"0\"";

    foreach $COL_NAME (sort keys(%input)) {                 # For each item filled in on the form
        if (length ($input{$COL_NAME}) && 
            $COL_NAME !~ /ACTION|STAGE/ &&                  # Excluding these items
            $input{$COL_NAME} !~ /Choose Type of Video/ ) {
                $SQL .= ",\n \"$input{$COL_NAME}\"";        # List the values being inserted
        }
    }

    $SQL .= ");\n";                                         # Close paren and semi-colon, newline
    
    #print "DEBUG: SQL=${SQL}<BR>\n";
    print (TEMP_SQL "$SQL\n");              #   Write the SQL statement to the temp file
    close TEMP_SQL;
    
    #------------------------------------------------------------------------------------
    #       Make sure there is something to insert

    if (!$FLAG_DATA) {
        print "<CENTER><FONT color=red><H1>\n";
        print "<B>Warning<BR> no data found to insert into database</B><BR><BR>\n";
        print "</FONT>\n";
        print "</CENTER></H1>\n";
        &display_menu1;                     # Menu: Query, Add-New, Add-Same
        return;
    }
    
    #------------------------------------------------------------------------------------
    #       Execute using the temp SQL output file
    
    &run_sql_statmt1;                       #       Execute using the temp SQL output file
    
    #------------------------------------------------------------------------------------
    #       Display Output
    
    if (&sql_stdout) {                      # Display the stdout of the SQL statement
        return;
    }                   
    
    #------------------------------------------------------------------------------------
    #       Successful insert to the database

    if ($FLAG_INSERT) {
        print "<HR SIZE=4 NOSHADE>\n";
        print "<H2><CENTER>Record Added.</CENTER></H2>\n";
    }

    &display_menu1;                         # Menu: Query, Add-New, Add-Same
}

################################################################################
# Display a standard end of page
################################################################################

sub display_end {

    print "\n";
    print "<HR SIZE=4 NOSHADE>";
    print "\n";
    print "</body>\n";
    print "</html>\n";

}

################################################################################
#   Display a list of "Type Code" options as a HTML "select" list
################################################################################

sub display_type_code_options {

    local ($ITEM_NAME,$ITEM) = @_;
    local $x = 0;
    
    print "<select name=\"${ITEM_NAME}\">\n";
    print "<option value=\"\">Choose Type of Video\n";
    
    for ($x = 0; $x <= $#TYPE_CODE_LIST1; $x++) {
    
        if ($ITEM eq $TYPE_CODE_LIST1[$x]) {
            print "<option value=\"$TYPE_CODE_LIST1[$x]\" selected>";
        } else {
            print "<option value=\"$TYPE_CODE_LIST1[$x]\">";
        }
        print "$TYPE_CODE_LIST2[$x]\n"; 
    
    }
    
    print "</select>\n";
}

################################################################################
#   Function:       get_rand_num
#   Author:         Peter R. Schmidt
#   Description:    Generate a random while number (integer) 
#                   using the current time and process ID as a seed
#   Input Arg 1:    Maximum highest number you want to generate
################################################################################

sub get_rand_num {

    local ($MAX) = @_;      # Arg 1 = Maximum highest number you want to generate
    srand(time|$$);         # Set the random number seed using time and process id
    int(rand($MAX)) + 1;    # Return a random number between 1 and MAX
}

################################################################################
#
#   Function Name:  construct_char
#   Author:         Peter R. Schmidt
#   Description:    Construct part of a SQL where-clause for a character type column
#
#   Input Arg 1:    Name of column to search on
#   Input Arg 2:    Value to search on
#
#   Return Arg:     Where clause
#
################################################################################

sub construct_char {

    local ($COL_NAME,$SEARCH_STR) = @_;
    local $RET_VAL = "";
    local $TOKEN   = "";
    local $CNT     = 0;
    
    $COL_NAME = lc($COL_NAME);  # Convert to lower case
    
    do_once: {
    
        if ( $SEARCH_STR eq "=") {
            $RET_VAL = "$COL_NAME is null";                 # IN NULL
            last;
        } 
    
        if ( $SEARCH_STR eq "!=") {
            $RET_VAL = "$COL_NAME is not null";             # IS NOT NULL
            last;
        } 
        
        if ( $SEARCH_STR =~ /\*/) {                         # MATCHES
            $RET_VAL = "$COL_NAME matches \"$SEARCH_STR\"";
            last;
        } 

        if ( $SEARCH_STR =~ /\|/) {                         # IN (pipe symbol in criteria)
            $RET_VAL = "$COL_NAME in (";                    # Open parens
            $CNT=0;
            foreach $TOKEN (split(/\|/,$SEARCH_STR)) {  
                if ( $CNT ) {
                    $RET_VAL .= ",";                        # Add a comma to the string
                }
                $RET_VAL .= "\"" . $TOKEN . "\"";
                $CNT++;
            }
            $RET_VAL .= "\)";                               # Closed parens
            last;
        } 
        
        $RET_VAL = "$COL_NAME = \"$SEARCH_STR\"";           # Equals
    }
    
    return $RET_VAL;
}

################################################################################
#
#   Function Name:  construct_numeric
#   Author:         Peter R. Schmidt
#   Description:    Construct part of a SQL where-clause for a numeric type column
#
#   Input Arg 1:    Name of column to search on
#   Input Arg 2:    Value to search on
#
#   Return Arg:     Where clause
#
################################################################################

sub construct_numeric {

    local ($COL_NAME,$SEARCH_STR) = @_;
    local $RET_VAL = "";
    local $TOKEN   = "";
    local $CNT     = 0;
    
    $COL_NAME = lc($COL_NAME);  # Convert to lower case
    
    do_once: {

        if ( $SEARCH_STR =~ /\|/) {                         # IN (pipe symbol in criteria)
            $RET_VAL = "$COL_NAME in (";                    # Add open paren to string
            $CNT=0;
            foreach $TOKEN (split(/\|/,$SEARCH_STR)) {  
                if ( $CNT ) {
                    $RET_VAL .= ",";                        # Add comma to string
                }
                $RET_VAL .= "\"" . $TOKEN . "\"";
                $CNT++;
            }
            $RET_VAL .= "\)";                               # Add closed paren to string
            last;
        } 
        
        if ( $SEARCH_STR =~ /\<|\>/) {                      # Less then, greater then, etc
            $RET_VAL = "$COL_NAME $SEARCH_STR";
            last;
        } 

        if ( $SEARCH_STR =~ /\:/) {                         # Between
            ($PART1,$PART2) = split(":",$SEARCH_STR,2);
            $RET_VAL = "$COL_NAME between $PART1 and $PART2";
            last;
        } 
        
        $RET_VAL = "$COL_NAME = \"$SEARCH_STR\"";           # Equals
    }
    
    return $RET_VAL;
    
}

################################################################################
#   Display the menu: Next - Previous - First - Last - List - Query - Add-New
################################################################################

sub display_menu2 {

print "<center><table border=0 cellspacing=5 cellpadding=0>\n";
print "<tr>\n";

if ($SERIAL_NEXT <= $input{SERIAL_MAX}) {
print <<VIDEO_TAG;
<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
        <input type=submit name="ACTION" value="Next">
            <input type=hidden name="SERIAL_LIST"  value="$input{SERIAL_LIST}">     
            <input type=hidden name="SERIAL_PICK"  value="$SERIAL_NEXT">                        
            <input type=hidden name="SERIAL_MAX"   value="$input{SERIAL_MAX}">                      
            <input type=hidden name="STAGE"        value="5">
    </FORM>
</center></td>
VIDEO_TAG
}

if ($SERIAL_PREV > 0) {
print <<VIDEO_TAG;
<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
        <input type=submit name="ACTION" value="Previous">
            <input type=hidden name="SERIAL_LIST"  value="$input{SERIAL_LIST}">     
            <input type=hidden name="SERIAL_PICK"  value="$SERIAL_PREV">                        
            <input type=hidden name="SERIAL_MAX"   value="$input{SERIAL_MAX}">                      
            <input type=hidden name="STAGE"        value="5">
    </FORM>
</center></td>
VIDEO_TAG
}

if ($input{SERIAL_PICK} > 1) {
print <<VIDEO_TAG;
<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
        <input type=submit name="ACTION" value="First">
            <input type=hidden name="SERIAL_LIST"  value="$input{SERIAL_LIST}">     
            <input type=hidden name="SERIAL_PICK"  value="1">                       
            <input type=hidden name="SERIAL_MAX"   value="$input{SERIAL_MAX}">                      
            <input type=hidden name="STAGE"        value="5">
    </FORM>
</center></td>
VIDEO_TAG
}

if ($input{SERIAL_PICK} < $input{SERIAL_MAX}) {
print <<VIDEO_TAG;
<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
        <input type=submit name="ACTION" value="Last">
            <input type=hidden name="SERIAL_LIST"  value="$input{SERIAL_LIST}">     
            <input type=hidden name="SERIAL_PICK"  value="$input{SERIAL_MAX}">                      
            <input type=hidden name="SERIAL_MAX"   value="$input{SERIAL_MAX}">                      
            <input type=hidden name="STAGE"        value="5">
    </FORM>
</center></td>
VIDEO_TAG
}

print <<VIDEO_TAG;
<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
        <input type=submit name="ACTION"       value="List">
        <input type=hidden name="SERIAL_LIST"  value="$input{SERIAL_LIST}">     
        <input type=hidden name="STAGE"        value="4">       
    </FORM>     
</center></td>

<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}">
        <input type=submit name="ACTION" value="Query">
        <input type=hidden name="STAGE" value="2">
    </FORM>
</center></td>

<td><center>
    <FORM METHOD="POST" ACTION="${CGI_DIR}${PROG_NAME}"> 
        <input type=submit name="ACTION" value="Add-New">
        <input type=hidden name="STAGE" value="1">
    </FORM>
</center></td>
</tr>
</table>
</center>
VIDEO_TAG

}

################################################################################
# Populate the $input associative array from the @UNLOAD_LIST array
# The @UNLOAD_LIST array contains a pipe-delimited list of fields
################################################################################

sub populate_form_variables {

    local $PLACEHOLDER;         # Needed because .unl files have a trailing pipe
            
    (   
        $input{SERIAL_ID},
        $input{TYPE_CODE},
        $input{TAPE_NUM},
        $input{TITLE},
        $input{HOURS},
        $input{COMMENT},
        $PLACEHOLDER
    )   
        = split(/\|/,$UNLOAD_LIST[0],7);

}

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