# Includes
use DBI;
use CGI qw(:standard);
use strict "vars";

# Init values
my $dsn = "DBI:mysql:longlife:localhost";                   # data source name
my $user_name = "longlife";                                  # user name
my $password = "choices";                                       # password
my $out = "/home/intel/loglists/TXT/";                         # path to the output directory
my $template = "/var/www/html/tech/test_loglistext.html";       # path to output template (design only)
my($sth,$dbh,@work_done);                                       # declare global vars
system("unset LANG");                                           # remove any specific language settings of the system
print "Content-type: text/html;\n\n";

##### Start main

# get tape numbers from user
my $tape = param("tape");
my $tape1 = param("tape1");

chomp($tape);
chomp($tape1);

# connect to database
$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 });
# check if tape numbers start with a letter
# if yes, it is assumed that both numbers start with the same letter
my $pref;
if($tape =~ /^\D/){
   # store tape nuber and prefix letter separately
   $pref = substr($tape,0,1);
   $tape =~ s/^\D//;

}
if($tape1 =~ /^\D/){$tape1 =~ s/^\D//}

##@@@ Main Loop
# iterate trough the whole range and create txt files where possible

for(my $i=$tape;$i<=$tape1;$i++){

   # issue query
   $sth = $dbh->prepare("SELECT the_number,',',client_company_name,',',name,',',_Title,',',_Version,',',_PGM_Duration,',',_Tape_Format FROM tapes_2 WHERE the_number='$pref$i'");

 $sth->execute ();

   my $output = $sth->fetchall_arrayref;
   my @output = @$output;

    if($output[0]){
    open(OUT,">$out$pref$i.txt") or die "could not open output file";
      print OUT <<"end_txt";
end_txt
     
       foreach my $row (@output){
         print OUT $row->[0];
         print OUT $row->[1];
         print OUT $row->[2];
         print OUT $row->[3];
        print OUT $row->[4];
         print OUT $row->[5];
         print OUT $row->[6];
         print OUT $row->[7];
         print OUT $row->[8];
         print OUT $row->[9];
         print OUT $row->[10];
         print OUT $row->[11];
         print OUT $row->[12];
      }

      close(OUT);
      chmod(0777,$file);
 # save tape number for later displaying
      push(@work_done, "$pref$i");

   }

}


# show process information to user
open(TEMPL,"$template");
my @template = <TEMPL>;
close(TEMPL);
foreach my $line (@template){print $line}

print "<br>&nbsp;&nbsp;&nbsp;<b>Tapes processed:</b><br>";
foreach my $tape (@work_done){
print "<br>&nbsp;&nbsp;&nbsp;$tape";
}
print "</body></html>";

## finish up
$sth->finish ();
$dbh->disconnect ();
exit;

open(OUT,">/path/to/file.txt") or die $!;
for(my $i=$tape;$i<=$tape1;$i++){

  # issue query
  $sth = $dbh->prepare("SELECT the_number,',',client_company_name,',',name,',',_Title,',',_Version,',',_PGM_Duration,',',_Tape_Format FROM tapes_2 WHERE the_number='$pref$i'");

$sth->execute ();

  my $output = $sth->fetchall_arrayref;
  my @output = @$output;

   if($output[0]){
     print OUT <<"end_txt";
end_txt
   
      foreach my $row (@output){
        print OUT $row->[0];
        print OUT $row->[1];
        print OUT $row->[2];
        print OUT $row->[3];
       print OUT $row->[4];
        print OUT $row->[5];
        print OUT $row->[6];
        print OUT $row->[7];
        print OUT $row->[8];
        print OUT $row->[9];
        print OUT $row->[10];
        print OUT $row->[11];
        print OUT $row->[12];
     }

     chmod(0777,$file);
# save tape number for later displaying
     push(@work_done, "$pref$i");

  }

}

     close(OUT);