#
# -------------------------------------------------------------------------------------------------------------------------------
# --                        
# -- Mandator:              Open MaxDB Group - Data Management.
# -- Legacy System:         omdg
# -- Script:                omdg_sqlsamples_i_html.pl
# -- Theme:                 Script to load data to table hotel.omdg_samples.
# --                        Demonstration of
# --                        - Connectivity of MaxDB using perl
# --                        - Demonstration of writing multiple line LONG columns
# -- Author:                RDa
# -- Synopsis:              perl omdg_sqlsamples_i_html.pl
# -- Interface definition:  hotel.omdg_sqlsamples
# -- Modify date:           2008.02.02 - RDa - (OMDG, Ralf Dahmen) - Creation for demonstration
# -------------------------------------------------------------------------------------------------------------------------------
#
use strict;
use DBI;
use Time::HiRes;

  my @lines;
  my $filcnt;
  my $filename;
  my @colnames;

  # A ODBC system DSN, named admin must be predefined, which refers to a MaxDB instance
  # Create a $dbh session, if succeeds continue otherwise die the execution
  # Connect to DB User mona,red 
  my $dbh = DBI->connect("DBI:ODBC:dsn=admin","MONA","RED")  or
                          die "Unable to connect" . $DBI::errstr . "\n";
  # You have to set this value for MaxDB/Oracle
     $dbh->{LongReadLen}=1000000;

  my $cmd = "
             DELETE FROM hotel.omdg
           ";

  # Prepare and Execute the SELECT statement or die
  # Store the result (pointer) in variable $sth (statement handler)
  my $sth = $dbh->prepare($cmd) or
     die "Unable to prepare command: " . $DBI::errstr . "\n";

     $sth->execute() or
     die "Unable to execute command: " . $DBI::errstr . "\n";

  # Read all delimiter files written by perl omdg_sqlsamples_s_html.pl
  foreach $filename (glob("raw/*.del")) {
    $filcnt++;
    my $script;
    my $result;
    my $grp;
    my $cat;
    my $ide;
    unless (open (DEL, "< $filename")) {
       printlog('E', "File could not be opened: $filename");
       return 0;
    }

    while (my $line = <DEL>) {
      @colnames  = split(/\t/, $line);
      if (@colnames < 2) {
        printlog('E', "Die Datei '$filename' ist nicht tab-separiert und kann nicht verarbeitet werden.");
        return;
      }
      $colnames[@colnames - 1] =~ s/[\r\n]*$//;
      $grp = $colnames[0];
      $cat = $colnames[1];
      $ide = $colnames[2];
    }
    my $lobname = $filename;
       $lobname =~ s,raw/,,,;
       $lobname =~ s'del'lob';
    unless (open (LOB, "< raw/$lobname")) {
       print ('E', "File could not be opened: $lobname\n");
       return 0;
    }
    while (my $line = <LOB>) {
      if ($line =~ /^$/) {
        $line = '//';
      }
      $script .= $line;
    }
    $script =~ s/'/''/g;

    my $txtname = $filename;
       $txtname =~ s,raw/,,,;
       $txtname =~ s'del'txt';
    unless (open (TXT, "< raw/$txtname")) {
#       print ('E', "File could not be opened: $txtname\n");
    }
    while (my $line = <TXT>) {
     if ($line =~ /^$/) {
       next;
     }
#     $result .= '> ' . substr($line,0,80);
      $result .= '> ' . $line;
    }
    $result =~ s/'/''/g;

    my $cmd = "
               INSERT INTO hotel.omdg
                              (groupid
                              ,categoryid
                              ,ident
                              ,statement
                              ,result)
                     VALUES ('$grp','$cat',$ide,'$script','$result')
             ";
    my $sth = $dbh->prepare($cmd) or
       warn "Unable to prepare command: " . $DBI::errstr . "\n";

       $sth->execute() or
       warn "Unable to execute command: " . $DBI::errstr . "\n";
  }

  # Release user session with implicit COMMIT
  $dbh->disconnect or warn $DBI::errstr;
  print "omdg_sqlsamples_i_html.pl: " . $filcnt . ' files read.';