#!/usr/bin/perl  -w 
 
$| = 1; 
 
#use lib '/apps/sybmon/gem/lib' ; 
 
use strict; 
use DBI; 
use warnings; 
use diagnostics ; 
use Cwd ; 
use File::Copy ; 
use File::Basename; 
use Net::SMTP; 
use MIME::Lite; 
#use Net::FTP; 
#use Net::SMTP::SSL; 
 
#------------ Connection related and data variables  
 
use vars qw( $opt_S $opt_U $opt_P $dvndb ) ; 
use vars qw( $data @data1 @param $row_data $outfile $test $para1 @newparm ); 
 
#------------ MAIL/SMS variables  
 
use vars qw( $from_address $to $cc $cc1 $mail_host $subject $message_body $msg $senderr @attach $sendOK $message1 $user $pass) ; 
 
 
$from_address = 'gtlcomp@gulftainer.com'; 
 
 
$to =  'pramesh@gulftainer.com,ramesh.daini@gmail.com'; 
#$cc =  'gjeeni@gulftainer.com,pramesh@gulftainer.com,ageorge@gulftainer.com,dangus@gulftainer.com'; 
 
$mail_host = 'smtp.office365.com'; 
 
$user = 'gtlcomp@gulftainer.com' ; 
$pass =  '0f1GHDTL'; 
 
my $smtpport = 587; 
 
#------------ Processing and Error capture Variables 
 
use vars qw($curdir $file_header $logfile $errorfile @procERR ) ; 
 
#------------------------------------------- Initialise Global variables---------------------------------------------------  
 
$opt_S = 'GTLDEVSERVER' ; 
$opt_U	= 'sa' ; 
$opt_P	= 'sybase'; 
$dvndb = 'sctdb' ; 
 
$curdir = cwd ; 
 
$row_data = 'inspdet.txt'; 
 
opendir(MYDIR, $curdir) || die "Cannot open directory: $curdir \n $!"; 
my @copyfiles = (readdir MYDIR); 
closedir(MYDIR) ; 
 
foreach (@copyfiles) { 
next if (m/^\.+/) ; 
unlink($_) if (m/\.txt$/) ; 
} 
 
 
my($dbh)= DBI->connect("dbi:Sybase:server=$opt_S",$opt_U,$opt_P); 
 
my($firstdt,$wday, $row, $todaydt ) ; 
 
# finding today's date 
$firstdt = $dbh -> prepare("select convert(char(10),getdate(),103)") ; 
$firstdt -> execute(); 
$todaydt = $firstdt -> fetchrow(); 
print "$todaydt\n" ; 
 
#----------------------------Program will run every sunday ----------------------------------------------------- 
 
$wday =  $dbh -> prepare("select datepart(dw,getdate())") ; 
$wday -> execute(); 
$row = $wday -> fetchrow(); 
 
push(@param,'ID'); 
 
#if($row == 2 ) { 
#  push(@param,'ID'); 
#} 
 
#---------------------------------- Expire file creation --------------------------------------------- 
foreach $para1 (@param) { 
          
          if ($para1 eq "ID"){  
              $outfile = 'inspout.txt'; 
	      $subject = "Inspection Details Generated on: $todaydt "; 
	      &filecreate(); 
 
          }      
         
} 
 
#------------------------------- subroutine for file creation  --------------------------------------------------- 
sub filecreate { 
	open (DAT, ">$row_data") ; 
 
	if( defined $dbh ) { 
	         
            	my $sth = $dbh->prepare("select isnull(cust_id,'-'),isnull(substring(cust_name,1,30),'-'),isnull(substring(vessel_name,1,30),'-'),isnull(substring(voyno,1,30),'-'),convert(char(10),log_date,103) from sctdb..sct_insp_details where gt_status = 1"); 
		$sth->execute ; 
		do { 
			while(@data1 = $sth->fetchrow_array) { 
				my($datarow) = join('ZZZ',@data1) ; 
				print DAT join('ZZZ',$datarow),"\n";  
			 	 
			 } 
		} while($sth->{syb_more_results}); 
	} 
 
	close DAT ; 
	&format_file () ; 
	&message_bodynew(); 
	&sendmsgbymail() ; 
} 
#--------------------------------------- subroutine for message body ----------------------------------------------- 
sub message_bodynew { 
         $message_body = ''; 
         open (DAT, "<$outfile") or die "unable to open file $row_data\n"; 
         while(<DAT>)  
         {  
           # Good practice to store $_ value because  subsequent operations may change it.  
           my($line) = $_;  
           # Good practice to always strip the trailing  newline from the line.  
           chomp($line);  
           # Convert the line to upper case.  
            $line =~ tr/[a-z]/[A-Z]/;  
           # Print the line to the screen and add a newline  
           # print "$line\n"; 
            $message_body =  "$message_body" . "$line\n" ; 
            
                   } 
        close DAT ; 
} 
 
#------------------------------- subroutine for file creation  --------------------------------------------------- 
sub format_file { 
 
	open (DAT, "<$row_data") or die "unable to open file $row_data\n"; 
	open (OUT, ">$outfile"); 
	print (OUT "CustID    CustomerName                   Vessel_Name         	   Voyageno                     Log Date   \n") ; 
	print (OUT "===============================================================================================================\n") ; 
	 
	while( <DAT> ) { 
 
		chomp; 
		chomp; 
		s/\s+$//;		 
		next if /^\s*$/;	# SKIP# Remove trailing spaces/tabs blank lines 
		next if /^#/;		# Skip Remarks 
		next if $_ eq '0' ;	# Skip line containing return value. 
                #print "******* $_\n" ; 
		 
		my ($custid, $custname, $vesselname, $voyageno, $logdate ) =split('ZZZ',$_) if defined $_; 
		#$days = ('00'.$days); 
		#$days = substr($days,length($days) - 2,length($days)); 
 
	        #print "$fileno $comp_no $refno $fmdt $todt  $days  $adddt $emp_name\n" ; 
	         
		 
format OUT =  
#Custid       Cust_Name                        Vessel_name        		Voyno   			 log_date       
@<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<   
$custid, $custname, $vesselname, $voyageno, $logdate 
. 
		write OUT; 
 
	} 
 
	print (OUT "===============================================================================================================\n") ; 
	print (OUT "THIS IS AN AUTO-GENERATED MESSAGE - PLEASE DO NOT REPLY TO THIS MESSAGE\n"); 
	close DAT ; 
	close OUT ; 
} 
 
#----------------------------- E-mail subroutine  start here ------------------------------------------- 
sub sendmsgbymail { 
 
# $msg $senderr @attach $sendOK 
 
	undef $senderr ; 
 
	if ( defined $cc ) { 
 
		$msg = MIME::Lite->new ( 
		  From => $from_address, 
		  To => $to, 
		  Cc => $cc, 
		  Subject => $subject, 
		  Type =>'multipart/mixed' 
		) or die "Error creating multipart container: $!\n"; 
 
	} else { 
 
		$msg = MIME::Lite->new ( 
		  From => $from_address, 
		  To => $to, 
		  Subject => $subject, 
		  Type =>'multipart/mixed' 
		) or die "Error creating multipart container: $!\n"; 
 
	} 
 
	$msg->attach ( 
	  Type => 'TEXT', 
	  Data => $message_body 
	) or die "Error adding the text message part: $!\n"; 
	 
 
#---------------------- Send the email ------------------------------- 
        
         
	MIME::Lite->send('smtp',$mail_host,AuthUser=>$user, AuthPass=>$pass, SSL=>0, Port=>$smtpport, Timeout=>30,Debug => 1 ) ; 
	 
	undef $@; 
	eval { $msg->send  } ; 
 
	if ($@) { 
		$senderr=1 ; 
		undef $sendOK ; 
 
	}	else { $sendOK = 1 ; }	 
	 
} 
 
#---------------------------------------------end of file ------------------------------------------------ 
 
1;