#!/usr/bin/env perl
#----------------------------------------------------------------------------
#
# Name: update_opus_cgg_table
#
# This perl script is a development tool to create a new user table, either
# cgg1_keyword or cgg4_order, in the OPUS_DB database using keyword database
# tables. The user must supply the name of the OPUS_DB (rather than rely on the
# ENV value) and the correct table name. The ENV KYWD_DB must be set to 
# the name of the keyword database.
#
# Interactive Usage:
#	>update_opus_cgg_table.pl <opus_db> <cgg1_keyword | cgg4_order>
#        where opus_db is one of the development opus databases and
#        either the name cgg1_keyword or the name cgg4_order is supplied.
#
# History:
# Date     OPR      Who         Reason
# -------- -------- ----------  ---------------------------------------------
# 03/07/05 47831    Baum        Initial code
# 10/12/05 53526    MSwam       Allow different KWDBs
# 10/23/06 56835    Sherbert    Update for KWDB prefixes on column names
# 04/01/09 62227    Sherbert    Read KWDB fm cmd line
# 05/23/11 68236    MSwam       Replace *= with LEFT OUTER JOIN for MSSQL
#----------------------------------------------------------------------------
# set up external routines
unshift @INC,(split /:/, $ENV{PATH});
require 'printmsg.pl';       # prints a formatted status message
require 'do_dbi_pkg.pl';     # DoDBI package - convenience routines
#----------------------------------------------------------------------------
    use strict;    # require explicit context for all variables (using "my")

    my $usage =<<"EOM";
Usage:
	update_opus_cgg_table.pl opus_db kw_db [cgg1_keyword | cgg4_order]
        
        where opus_db is one of the development opus databases
          and kw_db   is one of the development keyword databases
        and either the name "cgg1_keyword" or "cgg4_order" is used
EOM
 
#   Check for single argument
    my $num_arg = scalar @ARGV;

    if ($num_arg != 3) {
      PrintMsg ("I",$usage."Try again.");
      exit(1);
    }
    my $OPUS_DB = lc(shift @ARGV);
    my $KW_DB   = lc(shift @ARGV);
    my $opus_table = lc(shift @ARGV);
    
#   Get the required external variables

    my $DSQUERY = $ENV{"DSQUERY"};
    my $username = $ENV{"USER"};

    if (!defined( $DSQUERY)) {
       PrintMsg("E","ENV DSQUERY is not defined.");
       exit(1);
    }
    if (!defined( $OPUS_DB)) {
       PrintMsg("E","ENV OPUS_DB is not defined.");
       exit(1);
    }
    if (!defined( $KW_DB)) {
       PrintMsg("E","ENV KW_DB is not defined.");
       exit(1);
    }
    if ($opus_table ne "cgg1_keyword" and $opus_table ne "cgg4_order") {
       PrintMsg("E",$usage."Invalid second argument.");
       exit(1);
    }
    
#   Begin processing
    my $tablename = $username."\.".$opus_table;
    PrintMsg ("I","--- Creating user table $tablename ----------");

#   Open server connection and OPUS database for queries, and set err exit code
    my $db = DoDBIopen( $DSQUERY, $OPUS_DB, 1);
    
#   Drop old table and ignore error if table not present
    PrintMsg ("I","Drop old table - ignore error message if table not present");
    $db->do("drop table $tablename");    
#   Create new table
    my $count;
    if ($opus_table eq "cgg1_keyword") {
       $count = update_cgg1_keyword();
    }
    if ($opus_table eq "cgg4_order") {
       $count = update_cgg4_order();
    }
    DoDBIclose($db);
    PrintMsg ("I","--- $count records in user table $tablename ----------");
    exit(0);  

#---------------------------------------------------------------------
# end of main procedure -- subroutines follow
#---------------------------------------------------------------------
sub update_cgg1_keyword {  # no args
# create empty user table   
    my $query =<<"EOQ";
create table $tablename ( 
   instrument varchar(3) not null
   ,file_type varchar(3) not null
   ,order_index int not null
   ,fixed_index int not null
   ,keyword_str varchar(8) COLLATE SQL_Latin1_General_CP1_CS_AS not null
   ,keyword_typ varchar(3) not null
   ,keyword_val varchar(20) not null
   ,comment_str varchar(72) not null
   ,cnv_flag varchar(1) not null
   ,optional varchar(1) not null
)
EOQ
    DoDBI($db, $query);

#   insert records using KWDB tables
    $query =<<"EOQ";
insert $tablename 
select kwm_instrument instrument,
       kwm_groupname file_type,
       kwm_keyword_order order_index,
       isnull(kwm_fixedindex, 0) fixed_index,
       kwm_keyword COLLATE SQL_Latin1_General_CP1_CS_AS keyword_str,
       isnull(kwd_datatype, ' ') keyword_typ,
       isnull(kwm_default_val,' ') keyword_val,
       isnull(kwd_comment, ' ') comment_str,
       isnull(kwm_conv_flg, 'F') cnv_flag,
       isnull(kwm_option_flg, 'N') optional
from   $KW_DB..keyword_descrip
LEFT OUTER JOIN $KW_DB..keyword_main
ON     kwm_kd_id = kwd_kd_id
where  kwm_keyword_order IS NOT NULL and
       kwm_keyword_order != -99
union
select hdc_instrument instrument,
       hdc_groupname file_type,
       hdc_comment_order order_index,
       isnull(hdc_fixedindex,0) fixed_index,
       '        ' COLLATE SQL_Latin1_General_CP1_CS_AS keyword_str,
       'COM' keyword_typ,
       '                    ' keyword_val,
       isnull(hdc_comment, ' ') comment_str,
       ' ' conv_flg,
       ' ' optional
from $KW_DB..heading_comments
order by instrument,file_type,keyword_str,order_index
EOQ

    my $count = DoDBI($db,$query);   

#   Add index to table
    $query =<<"EOQ";
create unique clustered index cgg1_keyword_1 on $tablename
(instrument,file_type,keyword_str,order_index)
EOQ

    DoDBI($db,$query);
    $count;
}
#---------------------------------------------------------------------------
sub update_cgg4_order {  # no args
#   select new table using KW_DB header_file table
    my $query =<<"EOQ";
select convert( varchar(3), substring(hdf_section_name, 1, 3)) instrument,
       hdf_section_name header_type,
       hdf_group_order ftype_order,
       hdf_instrument cgg1_instr,
       hdf_groupname cgg1_ftype
into $tablename
from   $KW_DB..header_file
where  NOT( ( convert(float,hdf_section_number) <   9.5 )
         OR ( convert(float,hdf_section_number) >= 10   
           AND convert(float,hdf_section_number) < 10.7 )
         OR ( convert(float,hdf_section_number) >= 11   
           AND convert(float,hdf_section_number) < 11.7 )
         OR ( convert(float,hdf_section_number) >= 12   
           AND convert(float,hdf_section_number) < 12.6 )
         OR ( convert(float,hdf_section_number) >= 13   
           AND convert(float,hdf_section_number) < 13.5 )
         OR ( convert(float,hdf_section_number) >= 14   
           AND convert(float,hdf_section_number) < 14.5 )
         OR ( convert(float,hdf_section_number) >= 15.6 
           AND convert(float,hdf_section_number) < 15.9 )
          )
       and hdf_section_name not like 'WFC%'
union
select convert( varchar(3), 'WF3' ) instrument,
       hdf_section_name header_type,
       hdf_group_order ftype_order,
       hdf_instrument cgg1_instr,
       hdf_groupname cgg1_ftype
from   $KW_DB..header_file
where  hdf_section_name like 'WFC%'
EOQ

    my $count = DoDBI($db,$query);   

#   Add index to table
    $query =<<"EOQ";
create unique clustered index cgg4_order_1 on cgg4_order
(instrument, header_type, ftype_order)
EOQ

    DoDBI($db,$query);
    $count;
}