#############################

#!/usr/brcm/ba/bin/perl

use strict;
use warnings;
#****************************************************************
#               MODULE(S) USED
#****************************************************************
use FindBin;
use lib "$FindBin::Bin/lib";
use Database;
use Configs_DB;
#use MIME::Base64;
use MIME::Base64 qw( decode_base64 );
use File::Basename;
use File::Copy;
our ($host, $database,$user, $password);

#****************************************************************
#         Taking input CSV file for the script from user 
#****************************************************************
my $TicketFile 	= $ARGV[0]; #E.g. -> "APP_Attachments/Jan2016 APP ticket Numbers.csv";
if (!defined $TicketFile)
{

	print "Please provide the correct path for the CSV file to process! \n";
	exit 1;
}

#****************************************************************
#       Creting Directory for Storing attachments
#****************************************************************
my $tktFolder	= "APP_Attachments";
if (!-d $tktFolder)
{
	mkdir $tktFolder;
}
#****************************************************************
#               VARIABLES DECLARATION
#****************************************************************

my @CSVcontent 	= ();
my @Tickets 	= ();
#my @foldersAtt	= ();
#my @dates		= ();
my $dbh 		= Database::Connect( Database::DSN( $host, $database ),$user, $password );
my $destFolder	= "";
#****************************************************************
#      "uniq" Function for selecting unique values in an array
#****************************************************************
#sub uniq {
#	my %seen;
#	return grep { !$seen{$_}++ } @_;
#}

#****************************************************************
#                  Copy the CSV File into Array
#****************************************************************
open ( IFILE, "< $TicketFile" ) || die "$TicketFile File not found";
@CSVcontent = <IFILE>;
close IFILE;
shift( @CSVcontent );


#****************************************************************
#   Creating array containing Dates & HD Ticket nos.
#****************************************************************
foreach my $line ( @CSVcontent )
{
	chomp($line);
	my @arr=split(",", $line);
	push ( @Tickets, $arr[0] );
	#push( @dates, $arr[1] );
	my @partDate = split("/", $arr[1]);
	$destFolder = $partDate[2];
	if ( !-d "$tktFolder/$partDate[2]" )
	{
		mkdir "$tktFolder/$partDate[2]";
	}
	#foreach my $val ( @arr )
	#{
		#chomp( $val );
		
		#if ( $val =~ /^HD[0-9]{13}$/ )
		#{
		#	push ( @Tickets, $val );
		#}
		#if ( $val =~ /\d{1,2}\/\d{1,2}\/\d{2}/ )
		#{	
			
			#my $data=substr( $val, 0, index($val, ' ') );
			
		#	push( @dates, $val );
		#}
	#}
}
#my @folderDates = uniq( @dates );
#$destFolder = grep {$_ =~ /20*/}
#****************************************************************
#              Creating Year wise Directories
#****************************************************************

#foreach my $dateT ( @folderDates )
#{
#	chomp($dateT);
	
#	my @partDate = split("/", $dateT);
#	my $partYear = $partDate[2];
	
#	if ( !-d "$tktFolder/$partYear" )
#	{
#		mkdir "$tktFolder/$partYear";
#	}
	
#}

#opendir(my $dh, $tktFolder) || die "Can't opendir $tktFolder: $!";
#@foldersAtt = grep { -d "$tktFolder/$_" and $_ ne '.' and $_ ne '..' } readdir($dh);
#closedir $dh;

print "Script is running...Please Wait, it may take 10-15 mins to complete! \n";
#****************************************************************
#              Copying attachments from DB & Filer
#****************************************************************
foreach my $tkt ( @Tickets )
{
	chomp( $tkt );
	my $sql = "SELECT RequestID,TicketNumber
			FROM Request 
			WHERE TicketNumber = '$tkt'";
	my $sth = $dbh->prepare($sql) or die  "cant execute -- $DBI::errstr";
	$sth->execute();
	my @RequestID = $sth->fetchrow_array;
	
	my ($id, $tNum) = @RequestID;
	chomp($id);
	
	my $sql2 = "SELECT FileName,Filecontents,RequestId,ItemID,COUNT(FileName)
				OVER (PARTITION BY FileName) TOT_CNT 
				FROM Attachments 
				WHERE RequestId = '$id'";
	my $sth2 = $dbh->prepare($sql2) or die  "cant execute -- $DBI::errstr";
	$sth2->execute();
	my $dbAttachments = $sth2->fetchall_arrayref();
	$sth2->finish;
	
	my $sizeAttArray = @$dbAttachments;
	
	if ( @$dbAttachments ) #if attachments are present in DB for the ticket
	{
		foreach my $row ( @$dbAttachments ) 
		{
			my ($fileName, $fileContent, $reqID, $itmID, $count) = @$row;
			chomp( $fileName );
			$fileName =~ s/\\/\//g;
			my $fName = basename( $fileName );
				my $filePath1 = "/tools/busapps/LAMP/dev/APP/htdocs/APP/APP_Attachments/2016/LGop.pdf"; 
			   open (my $upload_filehandle,'<',$filePath1);
			     binmode $upload_filehandle;
				 open ( OFILE1, '>', "$filePath1" );
				 print OFILE1 $fileContent;
				 close OFILE1;
			     my $filecontents;
			     {
				    local $/ = undef; $filecontents = <$upload_filehandle>;
			     } 
				my $fContent = decode_base64( $filecontents );
			#my $fContent = decode_base64( $fileContent );
			#foreach my $dir(@foldersAtt)
			#{				
				#chomp( $dir );
				chomp( $tNum );
				#if( index($reqDt,$dir) != -1 )
				#{	   
					if ( $count == 1 )
					{
						my $filePath = "$tktFolder/$destFolder/".$tNum."_".$fName;
						open ( OFILE, '>', "$filePath" );
						print OFILE $fContent;
						close OFILE;
					}
					else #($count > 1)
					{						
						my $filePath = "$tktFolder/$destFolder/".$tNum."_".$itmID."_".$fName;
						open ( OFILE, '>', "$filePath" );
						print OFILE $fContent;
						close OFILE;							
					}
				#}
			#} 
			
		}
	}
	Database::Disconnect($dbh);
}