use utf8;
use strict;
use warnings;

use Data::Dumper;
use DBI;

my $note_filter = '%'; # filter for WHERE LIKE statement in sql for "rdb_main.dk_lov_header_rec.note"
my $name_filter = '%COUNTRY%'; # filter for WHERE LIKE statement in sql for "rdb_main.dk_lov_header_rec.global_section_name"

# bland credentials
open(my $access_fh, '<', "$ENV{USERPROFILE}".'\Documents\Perl\DW_access.txt') or die "Cannot open \"DW_access.txt\": $!.";
chomp(my $user = <$access_fh>);
chomp(my $pass = <$access_fh>);
close($access_fh);

# read in sql statement from "lov_table_lookup.sql"
my $lov_table_sql;
open(my $lov_table_sql_fh, '<', 'lov_table_lookup.sql') or die "Cannot open \"lov_table_lookup.sql\": $!.";
$lov_table_sql .= $_ while(<$lov_table_sql_fh>);
close($lov_table_sql_fh);

# read in sql statement from "lov_value_lookup.sql"
my $lov_value_sql;
open(my $lov_value_sql_fh, '<', 'lov_value_lookup.sql') or die "Cannot open \"lov_value_lookup.sql\": $!.";
$lov_value_sql .= $_ while(<$lov_value_sql_fh>);
close($lov_value_sql_fh);

# establish database connection
my $dsn = 'dbi:Oracle:DWAPRD';
my @connection = ($dsn, $user, $pass, {InactiveDestroy => 1});
my $dbh = DBI->connect_cached(@connection) or die;

# check that data will be returned
my $sth0 = $dbh->prepare('SELECT COUNT(*) as count FROM rdb_main.dk_lov_header_rec WHERE note LIKE ? AND global_section_name LIKE ?') or die;
$sth0->execute($note_filter, $name_filter) or die;
my $rows_count = $sth0->fetchrow_hashref;
print Dumper($rows_count);
die "\nNO TABLE NUMBERS FOUND\n" if (!$rows_count->{'COUNT'});
$sth0->finish;

# execute "lov_table_lookup.sql"
my $sth1 = $dbh->prepare($lov_table_sql) or die;
$sth1->execute($note_filter, $name_filter) or die;

# store table numbers in array
print "\n----------\n";
my $print_format = '%10s   %12s   %-32s   %-80s';
printf("$print_format\n", 'ROW_NUMBER', 'TABLE_NUMBER', 'GLOBAL_SECTION_NAME', 'NOTE');
my @table_numbers;
my $row_counter = 1;
while(my $row = $sth1->fetchrow_hashref) {
    push(@table_numbers, $row->{'TABLE_NUMBER'});
    foreach (keys(%$row)) {$row->{$_} = '' if (!defined($row->{$_}))}; # change all NULLs to empty strings
    printf("$print_format\n", $row_counter, $row->{'TABLE_NUMBER'}, $row->{'GLOBAL_SECTION_NAME'}, $row->{'NOTE'});
    $row_counter++;
}
print "----------\n";
$sth1->finish;

# adjust $lov_value_sql for variable number of bind parameters
my $bind_inserts;
$bind_inserts .= '?, ' foreach (@table_numbers);
$bind_inserts =~ s/, $//; # discard hanging comma
$lov_value_sql =~ s/LIST_OF_LOV_TABLE_NUMBERS:\?/$bind_inserts/;

# execute "lov_value_lookup.sql"
my $sth2 = $dbh->prepare($lov_value_sql) or die;
print "\n----------\n";
$print_format = '%10s   %12s   %15s   %-15s   %-80s';
printf("$print_format\n", 'ROW_NUMBER', 'TABLE_NUMBER', 'LOV_INTEGER_KEY', 'LOV_DISPLAY_KEY', 'NOTE');
$row_counter = 1;
$sth2->execute(@table_numbers) or die;
while(my $row = $sth2->fetchrow_hashref) {
    foreach (keys(%$row)) {$row->{$_} = '' if (!defined($row->{$_}))}; # change all NULLs to empty strings
    printf("$print_format\n", $row_counter, $row->{'TABLE_NUMBER'}, $row->{'LOV_INTEGER_KEY'}, $row->{'LOV_DISPLAY_KEY'}, $row->{'NOTE'});
    $row_counter++;
}
print "----------\n";
$sth2->finish;

##</code><code>##

-- Find the table number(s) that have the codes you wish to decode
SELECT table_number
     , TRIM(CAST(global_section_name as CHAR(32))) as global_section_name
     , TRIM(CAST(note as CHAR(80))) as note
  FROM rdb_main.dk_lov_header_rec
 WHERE note LIKE ?
   AND global_section_name LIKE ?
 ORDER BY global_section_name

##</code><code>##

-- List all the codes
SELECT table_number
     , lov_integer_key
     , TRIM(CAST(lov_display_key as CHAR(2))) as lov_display_key
     , TRIM(CAST(note as CHAR(30))) as note
  FROM rdb_main.dk_lov_detail_rec
 WHERE table_number IN (LIST_OF_LOV_TABLE_NUMBERS:?)
 ORDER BY table_number, note