perl script will generate html file, converting this to excel is not happening helpme

[Jatla V Ganesh]

#!/usr/bin/perl
use strict;
use XML::Simple;
use Data::Dumper;
use File::stat;
use Time::localtime;
use POSIX ();
use File::Basename;
use File::Path qw(mkpath);
 
#use Env;
 
print "Content-type:text/html\r\n\r\n";
 
my $sourceName = $ARGV[0];
 
my $cobdate;
 
my $TMPFILE;
 
my $TMP_FILE;
 
my $directory ;
 
my $riskError;
 
my $tradeError;
 
my $cobdateTag;
 
my $fileTypeTag;
 
my $rmErrorBodyTag;
 
my $bookIdTag;
 
my $subBookIdTag;
 
my $gbmBookRefTag;
 
my $bookTag;
 
my $ticketNumber;
 
my $riskTypeTag;
 
my $errorDescriptionTag;
 
my $errorTypeTag;
 
my $reg;
 
my( @logLines );
 
my $zipfilename;
 
my $sourceSysIdTag;
 
my $runLogFhnd;
 
my $surfaceError;
 
my $timeStamp;
 
sub LDATA() { "DATA"; }
 
sub LDEBUG(){ "DEBUG"; }
 
sub LINFO() { "INFO"; }
 
sub LWARN() { "WARN"; }
 
sub LERROR(){ "ERROR"; }
 
sub LFATAL(){ "FATAL"; }
 
sub LSTATS(){ "STATS"; }
 
sub myTRUE() { 1; }
sub myFALSE() { 0; }
 
sub mySUCCESS() { 0; }
 
sub myFAIL() { 2; }
 
 
################################################################################
# Logging functions.
 
# Default is logging to STDOUT, but will also log to logfile if file handle
 
################################################################################
 
 
sub logLine($$)
{
    my( $level, $line ) = @_;
        my( $curTs ) = getCurrentDateTimeStr();
 
    if( $level eq LDEBUG )
    {
 
            printf( "%-5s: %s\n", $level, $line );
            if(defined($runLogFhnd))
            {
                my($line) = sprintf( "$$:%-5s: %s\n", $level, $line );
                print $runLogFhnd $line;
            }
 
        }
    else
    {
        printf( "%-5s: %s\n", $level, $line );
        if(defined($runLogFhnd))
        {
            my($line) = sprintf( "$$:%-5s: $curTs %s\n", $level, $line );
            print $runLogFhnd $line;
        }
    }
 
    if($level eq LFATAL)
    {
                my($package, $filename, $line) = caller;
       # printf("%5s: $curTs : Aborting due to FATAL error in '$package' around line No. : $line\n", $level);
                if(defined($runLogFhnd))
                {
        #               my($line) = sprintf("$$:%5s: $curTs : Aborting due to FATAL error in '$package' around line No. : $line\n", $level);
                        print $runLogFhnd $line;
                        close($runLogFhnd);
                }
        exit 2;
    }
}
 
################################################################################
# sub getCurrentDateTimeStr
################################################################################
sub getCurrentDateTimeStr()
{
        my ($sec,$min,$hour,$day,$month,$yr19,@rest) = localtime();
        return($timeStamp);
}
 
################################################################################
# sub checkAndCreateDirPath
# Go through the full dirpath..
################################################################################
sub checkAndCreateDirPath($)
{
        my $dirpath = shift;
 
        unless ( -d "$dirpath" )
        {
                logLine(LINFO, "checkAndCreateDirPath: Creating Dir : $dirpath");
                eval { mkpath($dirpath) };
                if ($@) 
                {
                        logLine(LFATAL, "checkAndCreateDirPath: Failed to create '$dirpath' : $@");
                }
                else
                {
                        logLine(LINFO, "checkAndCreateDirPath: Created  Dir : $dirpath");
                }
 
        }
}
 
 
 
################################################################################
### sub checkAndCreateDirPath
### Go through the full dirpath..
##################################################################################
 
 
sub CreateBackupFile($)
{
   my $tmpFile=shift;
 
 
 
      if(-f "$tmpFile")
         {
               print "TMP file already exists";
 
                  }else{
 
                        print "creating a file";
 
                                  open my $OUT, '>>', $tmpFile or die "can't create tmp file '$tmpFile' : $!";
                                     }
                                     my $USER_PWD='m_o/Abcdefa321hdrgf@K_M_A';
 
                                               my $previouscob=`sqlplus -s $USER_PWD<< EOF
                                               set heading off;
                                               set pagesize 0;
                                               select to_char(T_MINUS_1_DATE,'YYYYMMDD') from  MYRIAD_SYSTEM_DATE;
                                               EOF`;
 
 
                                               $previouscob =~ s/\s+$//;
 
                                               print "$previouscob";
 
                                               my $prev_file="/var/tmp/checkFilePresent_$previouscob.csv";
                                               if (-f "$prev_file"){
                                               unlink $prev_file;
                                               print "removed previos cobdate files";
                                               }
 
                                               }
 
 
##############################################
 
# my $myriad_env_filepath = $ENV{`MYRIAD_HOME/common/conf/myriad.env`};
#my $myriad_env_filepath = {'/cs/rfcs/myriad/envs/PRD/common/conf/myriad.env'};
 
# my $USER_PWD = $ENV{`MYRIAD_DB_CONN`};
 
#my $USER_PWD='myriad_user/user_myriad1234@PLNMY010_MYRIAD_APP';
#my $USER_PWD='myriad_user/jHgdbfUhfzrhMF82@PZHMY010';
my $USER_PWD='myriad_owner/Abcdefa321hdrgf@KZHMY103_MYRIAD_APP';
 
 $cobdate =`sqlplus -s $USER_PWD<< EOF
set heading off;
set pagesize 0;
select to_char(T_ZERO_DATE,'YYYYMMDD') from  MYRIAD_SYSTEM_DATE;
EOF`;
 
$cobdate =~ s/\s+$//;
 
if ($sourceName eq "PrimoRisk") {
          $directory = "/cs/myrow/myriad/envs/PRD/data/archive/message/primo/riskerror/$cobdate";
          print " Directory :$directory \n";
          $riskError ="RISKERROR";
          $cobdateTag = "COB_DATE";
          $fileTypeTag = "FILE_TYPE";
          $bookIdTag = "BOOK_ID";
          $subBookIdTag ="SUB_BOOK_ID";
          $ticketNumber = "TICKET_NUMBER";
          $errorDescriptionTag = "ERROR_DESCRIPTION";
          $errorTypeTag = "ERROR_TYPE";
          $sourceSysIdTag="SOURCE_SYSTEM_ID";
          $reg ="PRIMO";
         }
   elsif ($sourceName eq "PrimoTrade") {
          $directory = "/cs/myrow/myriad/envs/PRD/data/archive/message/primo/tradeerror/$cobdate";
          $tradeError="TRADEERROR";
          $cobdateTag = "COB_DATE";
          $fileTypeTag = "FILE_TYPE";
          $bookIdTag = "BOOK_ID";
          $subBookIdTag ="SUB_BOOK_ID";
          $ticketNumber = "TICKET_NUMBER";
          $errorDescriptionTag = "ERROR_DESCRIPTION";
          $errorTypeTag = "ERROR_TYPE";
          $sourceSysIdTag="SOURCE_SYSTEM_ID";
          $reg ="PRIMO";
         }
 
   elsif ($sourceName eq "Jane") {
         #$directory = "/cs/myrow/myriad/envs/PRD/data/file_versions/$cobdate";
         $directory = "/cs/myrch/myriad/envs/IT3/data/file_versions/$cobdate";
          $riskError ="risk";
          $tradeError="trade";
          $surfaceError ="surface";
         }
   elsif ($sourceName eq "Giraffe") {
          $directory = "/cs/myrow/myriad/envs/PRD/data/file_versions/$cobdate";
          $riskError ="RISK";
          $tradeError="TRADE";
          $reg = "GIRAFFE";
         }
 
 
my $logDirectory  = "/cs/myrch/" . "/logs";
# Make sure log dir exists
 
checkAndCreateDirPath( $logDirectory );
 
my $TMP_FILE="/var/tmp/checkFilePresent_$cobdate.csv";
 
# Make sure tmp dir exists
 
CreateBackupFile($TMP_FILE);
 
 
# It generates YYYYMMDDhhmmss
 
my $t = localtime;
 
 $timeStamp = sprintf("%04d%02d%02d_%02d-%02d-%02d",
                         $t->year+1900, $t->mon + 1, $t->mday,
                         $t->hour, $t->min,   $t->sec );
 
 
my $errorFileName=$sourceName.'Errors'.$timeStamp.'.html';
 
$zipfilename=$sourceName.'Errors'.$timeStamp.'.zip';
 
 
my $errorFile = $logDirectory.'/'.$errorFileName;
 
my $tlogname = $sourceName.'Errors'.$timeStamp;
 
my( $runLog ) = $logDirectory.'/'.$tlogname.'.log';
 
open($runLogFhnd, "+>>$runLog") || logLine(LFATAL, "Failed to open '$runLog' for writing.");
 
 
########################################################################
# Creating file for display error Details.
#######u##################################################################
 
open(DATA,"+>>$errorFile") || die "Couldn't open file file.txt, $!";
 
 
print DATA "<html> <TABLE BORDER> \n ";
 
print  DATA "<tr><td>COB Date</td><td>FO RMS</td><td>Feed Name</td><td>Book</td><td>Sub Book</td><td>GBM BOOK REF</td><td>BOOK ID</td><td>Ticket Number</td><td>Error File Type</td><td>Error Type</td><td>Error  Description</td></tr>\n";
 
`chmod 777 $errorFile`;
 
   opendir (DIR, $directory) or exit 0;
 
my $num= `cd $directory; ls *ERROR*xml*gz -o *error*xml.gz |wc -l`;
 
 
       if($num >=1){
       `cd $directory; gunzip *error*gz; gunzip *ERROR*gz`;
}
   foreach (sort grep(/^.*\.xml$/,readdir(DIR))){
 
        my $file = $_;
 
my $fileref= `grep $file* $TMP_FILE | wc -l`;
 
if($fileref==0)    
 
{
if ( $file =~ m/error/i )
{
logLine(LINFO, "Source Name : $sourceName ");
 
logLine(LINFO, "Get File Name: $file ");
 
my @words = split /_/, $file;
 
 
my $errorListTag;
 
my $errorTag;
 
logLine(LINFO, "Get File first word: $words[0] ,$reg ");
 
if ( $sourceName eq "Jane" && ( ( grep {$_ eq $tradeError} @words ) || (grep {$_ eq $riskError} @words )|| ( grep {$_ eq $surfaceError} @words )))
{ 
$reg = $words[0];
 
logLine(LINFO, "This check is for only Jane Feed : $reg ");
}
 
if ($words[0] =~ m/$reg/)
{
 
print " regex is working $reg \n";
 
 
if (grep {$_ eq $tradeError} @words) {
 
$errorListTag="tradeErrorList";
 
$errorTag="tradeError";
 
logLine(LINFO, "This is Trade Error: $errorListTag ");
 
}
 
else
 {
 
$errorListTag="rmErrorList";
 
$errorTag="rmError";
 
logLine(LINFO, "This is Risk/Surface  Error: $errorListTag ");
}
 
 
my $path;
 
if ($words[0] eq "GIRAFFE")
{
 
 
$cobdateTag = "COB_DATE";
 
$fileTypeTag = "FILE_TYPE";
 
$bookIdTag = "BOOK_ID";
 
$subBookIdTag ="SUB_BOOK";
 
$ticketNumber = "TICKET_NUMBER";
 
$errorDescriptionTag = "ERROR_DESCRIPTION";
 
$errorTypeTag = "ERROR_TYPE";
 
$sourceSysIdTag ="SOURCE_SYSTEM_ID";
 
print "This is GIRAFFE feed \n";
 
 $path = "$directory/$file";
 
}
 
elsif ($words[0] ne "PRIMO")
{
 
$cobdateTag = "cobDate";
 
$fileTypeTag = "fileType";
 
$bookIdTag = "book";
 
$subBookIdTag ="subBook";
 
$ticketNumber = "ticketNumber";
 
$errorDescriptionTag = "description";
 
$errorTypeTag = "errorType";
 
$sourceSysIdTag = "sourceSystemCode";
 
$gbmBookRefTag = "GBM_BOOK_REF";
 
$bookTag = "bookId";
 
logLine(LINFO, "This is for Jane: $sourceSysIdTag ");
 
 $path = "$directory/$file";
}
else
{ 
$path = "$directory/$file";
 
}
 
logLine(LINFO, "get File complete path: $path ");
 
my $ererrorReport = XMLin($path,ForceArray=>['rmError','book','tradeError']);
 
##########################################################################
# Reading Header Values :Cobdate and File type.
##########################################################################
 
my $rmErrorHeaderdata=$ererrorReport->{rmErrorHeader};
 
#print Dumper($rmErrorHeaderdata);
 
my $cobdate=$rmErrorHeaderdata->{$cobdateTag};
 
my $errorFileType=$rmErrorHeaderdata->{$fileTypeTag};
 
my $forms=$rmErrorHeaderdata->{$sourceSysIdTag};
 
logLine(LINFO, "get Error file details CobDate ErrorFileType Fo RMS: $cobdate $errorFileType $forms ");
 
 
###########################################################################
# Reading Body values:Book,Subbook,Ticketnumber,ErrorType,ErrorDescription
###########################################################################
 
my $rmErrorBodydata = $ererrorReport->{rmErrorBody};
 
my $bookListdata = $rmErrorBodydata->{bookList};
 
my $books = $bookListdata->{book};
 
#print Dumper($books);
 
foreach my $book (@{$bookListdata->{book}})
{
my $bookId = $book->{$bookIdTag};
 
my $subBookId=$book->{$subBookIdTag};
 
my $gbmBookRef=$book->{$gbmBookRefTag};
 
my $bookRef=$book->{$bookTag};
 
my $list = $book->{$errorListTag};
 
my $rmErrorData = $list->{$errorTag};
 
#print Dumper($rmErrorData);
 
foreach my $rmErrorData (@{$list->{$errorTag}})
 
{
 
my $ticketNumber = $rmErrorData->{$ticketNumber};
 
my $errorDesc = $rmErrorData->{$errorDescriptionTag};
 
my $errorType = $rmErrorData->{$errorTypeTag};
 
logLine(LINFO, "get Error file details  Book: $bookId  Subbook : $subBookId  TicketNumber: $ticketNumber  ErrorType : $errorType ");
 
print DATA "<tr><td>$cobdate</td><td>$forms</td><td>$file</td><td>$bookId</td><td>$subBookId</td><td>$gbmBookRef</td><td>$bookRef</td><td>$ticketNumber</td><td>$errorFileType</td><td>$errorType</td><td>$errorDesc</td></tr>\n";
 
}
}
 
#my $bakFileName=$file.".bak";
 
#my $bakFile="$directory/$bakFileName";
 
#`mv $path $bakFile`;
#open(DATA,"+>>$TMP_FILE") || die "Couldn't open file, $!";
 
 
#print DATA "$file \n ";
 
#close(DATA);
 
}
}
open(DATA1,"+>>$TMP_FILE") || die "Couldn't open file, $!";
 
 
print DATA1 "$file \n ";
 
close(DATA1);
 
}
closedir(DIR);
`cd $directory; gzip $file`;
}
 
print DATA "</table></html>\n";
 
close (DATA);
 
`cd $logDirectory;zip $zipfilename  $errorFileName;chmod 777 $zipfilename`;
 
my $msg ='Email with ErrorDetails Attachment from Myr';
 
my $addr ='[e-mail]';
 
`echo "" | mailx -s '$msg' -a '$logDirectory/$zipfilename' '$addr'`;
 
 
logLine(LINFO, "Successfully sent mail to the user ");

[RonB]

You can't post 500+ lines of code and expect someone to troubleshoot it and fix it for you.

What part of the script is failing and in what way is it failing? What errors and/or warnings is it giving you?

Try to write a SHORT but complete script which demonstrates the problem.

[RonB]

Get rid of the prototypes. Perl's prototypes don't work the same way as some other languages and are almost always not what you should be doing.

Far More than Everything You've Ever Wanted to Know about Prototypes in Perl -- by Tom Christiansen

[RonB]

Drop your logging routines and instead use the Log::Log4perl module.

Your getCurrentDateT imeStr() sub is not doing what you think. You should use the strftime() function from the POSIX module, which you are already loading.

Don't use TitleCase for multi word vars and subs. Use an _ underscore to separate the words and use lowercase.

Your indentation is inconsistent which makes it difficult to follow the code blocks. You can use Perl::Tidy to clean/fix the formatting.

If I look closer, I'm sure I'll find other issues that need to be addressed.

[Jatla V Ganesh]

Hi..Thanks RonB for giving your reply..As of now perl script will generate html file in IE, it is not opening in IE it is taking so much time, converting this to excel is not happening as the existing installed Perl version doesn’t support this, could you please he me in this implementing it if any.