OXIESEC PANEL

Name	Size	Modified	Perms
📄 -d	10 bytes	02/03/2023 07:34:13 AM	rw-r--r--
📁 ..	-	02/03/2023 08:06:37 AM	rwxr-xr-x
📄 config.pl	7.61 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 cross2normal.pl	3.71 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 dataextract.sh	2.51 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 etl.pl	5.42 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 etlproc	9.99 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 func.pl	4.01 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 globvar.sh	127 bytes	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 initetl.pl	1.88 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 libawk.awk	3.07 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 makeXtab2NormalExcel.pl	5.73 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 process.pl	6.91 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 process_changed.pl	5.69 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 readwrite.pl	4.16 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxcsv.pl	7.94 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxdb.pl	11.06 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxfile.pl	4.68 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxsalesforce.pl	3.5 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxxml.pl	13.83 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxxml2.pl	4.18 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 sendmail.pl	1.06 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 sourcequery.pl	4.18 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 sql.pl	23.98 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 store.pl	7.97 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 tags	13.14 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 task.pl	33.48 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📁 test	-	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 userfunc.pl	5.05 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 valid.pl	3.64 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 workflow.pl	3.94 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 xform.pl	2.62 KB	02/03/2023 07:34:13 AM	rwxr-xr-x

Editing: rxcsv.pl

#!/usr/bin/perl
#use warnings;

#my @r=(1, 0); &rxwritecsv('/tmp/test.xls', "jan", 3, \@r, 'N');	#Example Calling Following Function

#This function writes CSV file as o/p.
#i/p arguments are	1: $infile		i/p xls file name.
#					2: $insheet		sheetname if want csv of particular sheet else pass "". * means allsheets.csv for whole
#					3: $ignrrows	No. of rows to be ignored as blank row. pass "" for if no rows ignore.
#					4: $expcols		(Array ref) Export only these columns (eg. 0, 1, 2 ....).Note Ordering doesn't effect
#					5: $ident		Pass ident=Y if u want only first sheet to get exported(bcoz all have same meta)
#my $RXXLS_DIR="/tmp/myxls/";								#Staging Dir For Excel File (csv o/p will be in this dir)
sub rxwritecsv{
	my ($infile, $insheet, $ignrrows, $expcols, $ident, $ppid) = @_;
	die "You must provide a filename to be parsed as an Excel file" unless $infile;

printlog($MAX, "RECVD $G_Purpose [$ppid][$RXXLS_DIR] ==================== $infile, $insheet, $ignrrows, $expcols, $ident");

my $tmp_stage_dirname="$RXXLS_DIR".'/'.getfname("$infile");

#	my ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,$ctime,$blksize,$blocks) = stat($infile);
	my @infstat = stat($infile);
	my @stgfstat = stat($tmp_stage_dirname);

my @mf = get_masked_files($tmp_stage_dirname, "*.*");

if(($#stgfstat > -1) and ($#mf > -1)){
		if($infstat[9] lt $stgfstat[9]){
			printlog($MAX, "Source File Not Changed After Last Staging: ($infstat[9] lt $stgfstat[9])");
			return 1;
		}
		else{
			printlog($MAX, "Regenrating Files Becoz ($infstat[9] lt $stgfstat[9]) i.e. Source File Changed After Stage");
		}
	}
	else{
		printlog($MAX, "Regenrating Files: $#stgfstat -- $#mf");
	}

if($G_Purpose eq "xformpreview"){
		return 1 if isDirExist($tmp_stage_dirname);
	}
	my $xlsxf=index($infile,".xlsx");
	my $Workbook;
	if($xlsxf > 0){
		use Spreadsheet::XLSX;
		$Workbook =Spreadsheet::XLSX ->new ($infile);
	}
	else{
		use Spreadsheet::ParseExcel;
		my $XLS = new Spreadsheet::ParseExcel;
		$Workbook = $XLS->Parse($infile) or printlog($ERROR, "Parse Failllllllllllllllllllllllled");
	}

#Now Delete & Create Staging Dir
	printlog($INFO, "$RXXLS_DIR This Will Take Some More Time Depending On File [$infile] Size.");
	truncate_old_xls_stgdir($tmp_stage_dirname);

printlog($INFO, "Excel FILE  :", $Workbook->{File});
	printlog($INFO, "Sheet COUNT :", $Workbook->{SheetCount});

my($WorkSheet, $Cell, $CellRow, $CellCol);
	my $flag=0;

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

write_xls_fmtfile("$tmp_stage_dirname", "$infile", "$ident", $Workbook);

printlog($MAX, "Processing Start ..");

##########################################################################################################
	my $allsh = 0;
	for(my $Sheet=0; $Sheet < $Workbook->{SheetCount} ; $Sheet++){
		$WorkSheet = $Workbook->{Worksheet}[$Sheet];
		printlog($MAX, "Processing $WorkSheet->{Name} in is $insheet");

if($insheet ne "" && $insheet ne "*"){
			if($insheet ne $WorkSheet->{Name}){
				next;
			}
		}

#Create File 
		my $tmpfname;
		if($insheet eq "*"){
			if($allsh ne "1"){
				$allsh=1;
				$tmpfname=">$tmp_stage_dirname/allsheets.csv";
			}
			else{#Append to already created file
				$tmpfname=">>$tmp_stage_dirname/allsheets.csv";
			}
		}
		else{
			$tmpfname=">$tmp_stage_dirname/".$WorkSheet->{Name}."";
		}
		printlog($MAX, "Start Processing $WorkSheet->{Name}");
		write_xls_csvsheet($tmpfname, $ignrrows, $expcols, $WorkSheet);
		last if((defined $ident) and ($ident eq "Y"));
	}
	undef $XLS;
}

sub isinarray{
	my ($val, $arr) = @_;
	foreach my $tmpv(@$arr){
		return 1 if($tmpv eq $val) ;
	}
	return 0;
}

sub getdirname{#Get Just-Parent Dir Name
	my $fpath = $_[0];
	$fpath =~ s/\/\//\//g;
	$fpath =~ m/\/[^\/]*$/g;
	my $lastdir = $`;

$lastdir =  getfname($lastdir);

$lastdir;
}

sub getfname{#Get File Name
	$_[0] =~ m/\/[^\/]*$/g;
	my $tmp = $&;
	$tmp =~ s/\///g;
	$tmp;
}

sub write_xls_fmtfile{
	my $tmp_stage_dirname = shift;
	my $infile = shift;
	my $ident = shift;
	my $Workbook = shift;

my $fnm = getfname("$infile");
	my $XLSFMTFILE;
	open($XLSFMTFILE, ">$tmp_stage_dirname/$fnm.fmt") or $G_Err = $! && return "($tmp_stage_dirname/$fnm.fmt)Can't generate fmt file(Check Permission , Disk Space ..).";
	if($ident eq "Y"){
		print $XLSFMTFILE ("SHCOUNT:1\n");
	}
	else{
		print $XLSFMTFILE ("SHCOUNT:$Workbook->{SheetCount}\n");
	}
	printlog($MAX, "Init Start ..");

my $i=0;
	foreach my $oWkS (@{$Workbook->{Worksheet}}) {
		next if not defined $oWkS->{MinRow};
		printlog($MAX, "$i--Init Done [$oWkS->{Name}]");	
		$i++;
		print $XLSFMTFILE ("SHEET$i");
		print $XLSFMTFILE (":$oWkS->{Name}");
		print $XLSFMTFILE (":$oWkS->{MinRow}");
		print $XLSFMTFILE (":$oWkS->{MaxRow}");
		print $XLSFMTFILE (":$oWkS->{MinCol}");
		print $XLSFMTFILE (":$oWkS->{MaxCol}");
		print $XLSFMTFILE ("\n");
		last if($ident eq "Y");
	}

close($XLSFMTFILE);
}

sub write_xls_csvsheet{
	my $tmpfname = shift;
	my $ignrrows = shift;
	my $expcols = shift;
	my $WorkSheet = shift;

my $FILEH;
	open($FILEH, $tmpfname) or die "File Creation failed Error:$!\n";
	printlog($MAX, "Current SHEET: $WorkSheet->{Name}");
	for(my $CellRow= $WorkSheet->{MinRow}; defined $WorkSheet->{MaxRow} && $CellRow<= $WorkSheet->{MaxRow}; $CellRow++){
		if($ignrrows ne "" && $CellRow<$ignrrows){
			next;
		}

my @csvdata = ();
		for(my $CellCol= $WorkSheet->{MinCol} ; defined $WorkSheet->{MaxCol} && $CellCol<= $WorkSheet->{MaxCol}; $CellCol++){
			if($expcols ne "" && !isinarray($CellCol, $expcols)){
				next;
			}

$Cell = $WorkSheet->{Cells}[$CellRow][$CellCol];
			if($Cell){
				#if($Cell->is_merged()){
				#	if($Cell->{_Value}){
				#		$orig_cell{$CellCol}=$Cell->{_Value};
				#	}
				#	$my_val=$orig_cell{$CellCol};
				#}
				#else{
					if(($Cell->{Val} =~ /^([-+]?)\d+\.\d+$/) || ($Cell->{Type} == 'Date')){
						$my_val=$Cell->{Val};
					}
					else{
						$my_val=$Cell->{_Value};
					}
				#}
				$my_val=~s/\n/ /g;
				push(@csvdata, $my_val);
			}
			else{
				push(@csvdata, "");
			}
		}

@csvdata=join($G_PerlOutDelim, @csvdata);#I mEaN cSv ;-);

print $FILEH @csvdata, "\n";
	}
	close($FILEH);
}

sub isDirExist{
	my $dirname = shift;

my $direxist ="Y";
	opendir($D, $dirname) or $direxist ="N";
	if($direxist eq "Y"){
		printlog($MAX, "Dir [$dirname] Exists.");
		closedir $D;
		return 1;
	}
	closedir $D;
	return 0;
}

sub	truncate_old_xls_stgdir{
	my $tmp_stage_dirname = shift;

my @alloldfiles = <$tmp_stage_dirname/*>;
	foreach my $oldfile(@alloldfiles){
		unlink $oldfile;
		printlog($MAX, "$oldfile removed");
	}
	rmdir($tmp_stage_dirname);
	mkdir($tmp_stage_dirname) ;#or die "Can't Create Staging Dir: $!";
	printlog($MAX, "$tmp_stage_dirname <=== XLS Stage Dir");
}

sub wait_for_xls{
	my $maskfile = shift;
	my $maskfmt = shift;
	my $allsh = shift;

my $loop = 0;
	my $dbstginfo = SQL_stginfo($maskfile);
	printlog($MAX, "----------@@- LOCK STATUS FOR $maskfile ==============> @$dbstginfo[5]");

my $tm = time;
	if($dbstginfo eq 'ERR'){#No Entry for this file
		my @fstat = ();
		@fstat = stat($maskfile);#(@fstat[9], @fstat[11], @fstat[8]);#$atime -- $ctime -- $size
		add_stginfo($maskfile, $fstat[9], $fstat[8], $fstat[11], $tm, 'R', "Stage on $tm");
	}

if(($dbstginfo ne 'ERR') and (@$dbstginfo[5] eq 'R')){
		while(1){
			$dbstginfo = SQL_stginfo($maskfile);
			if((@$dbstginfo[5] ne 'R')){
				last;
			}
			printlog($ERROR, "Please wait some other ETL process is trying to parse same file : $loop");
			sleep 30;#Poll after 30sec
			$loop++;
			if($loop > 15){#If waiting more than 6-7 mins it means somthing wrong
				update_stginfo($maskfile, $tm, 'V', "Last used as stage on $tm");
			}
		}
	}
	else{
		printlog($MAX, "#################### Runing XLS GEN Code ####################");
		#rxwritecsv
		update_stginfo($maskfile, $tm, 'R', "Stage starts on $tm");
		system("perl $CUBOT_HOME/bin/etl/etl.pl fname=$maskfile maskfmt=$maskfmt allsh=$allsh noetl=1 db=$G_DB Queryout=$Queryout");
		$tm = time;
		update_stginfo($maskfile, $tm, 'V', "Last used as stage on $tm");
	}

return 1;
}