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: rxdb.pl

#!/usr/bin/perl

require "$ETL_HOME/config.pl";

######################################################################################
#ODBC-FIX
#sub execute_query{
#	my $cn = shift;
#	my $qry = shift;
#	printlog($QUERY, $qry);
#
#	my $sth = $cn->prepare($qry) or die $cn->errstr;
#	$sth->execute() or die $cn->errstr;
#}
use Data::Dumper;

sub execute_query{
	my $cn = shift;
	my $qry = shift;

#	return;

printlog($INFO, "Execute Query =>" . $qry);
	$cn->do($qry) or print "XXXXXXX ERROR :: $qry";
}

#ODBC-FIX
sub doselectone{
	my $query = shift;
	printlog($INFO, "DOSELECTONE query >>>" . $query);	
	printlog($INFO, "conn >>>"  . $conn);	
	my $sth = $conn->prepare($query) or return 'ERR';
	my $x = $sth->execute();

printlog($INFO, "c err >[$x]>>"  . $conn->errstr);
	printlog($INFO, "s err >>>"  . $conn->err);
	printlog($INFO, "s2 err >>>"  . $DBI::errstr);

if($x eq "0E0"){
		$sth->finish();
		return "";
	}

my $col_values = $sth->fetchrow_arrayref();
	#$sth->finish();

printlog $INFO, "IN CASE ............ $col_values .....";
	my $i=0;
	foreach my $v(@$col_values){ #Trimming Content 
#		printlog($INFO, "XXXXXXXXXXXXXXXXXXXXSSSSSSSSSSSS THIS[$i] = $v: " . @v);
		$v =~ s/[\s ]+$//g ;
		@$col_values[$i] = $v;
		$i++;
	}

printlog $INFO, Dumper($col_values);
	$col_values;
}

# ODBC-FIX
sub doselectall{#same as one but later we can optimize
	my $query = shift;

my $newquery = "select count(1) from ($query) tmptab";
	print "Q=$newquery\n";
	local $sth = $conn->prepare($newquery) or return 'ERR';
	$sth->execute();
	local @numrows = $sth->fetchrow_array();
	$sth->finish();

my $n = $numrows[0];

my $sth = $conn->prepare($query);
	$sth->execute();

my $i = 0;
	my $ref;
	my @c_values;
	for($i=0; $i<$n; $i++){
		$ref = $sth->fetchrow_arrayref();
		push(@c_values, [@$ref]);
		my @newarray = grep(s/\s*$//g, [@$ref]);
#		printlog($INFO, "CCCCCCCCCCCCC = " . [@c_values]);
#printlog $INFO, ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n";
#		printlog $INFO, Dumper(@newarray);
#printlog $INFO, ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n";
	}

prinlog $INFO,  Dumper(@c_values);

$sth->finish();
	\@c_values;
}

# ODBC-FIX
sub doselectall2{#same as one but later we can optimize
	my $n = shift;
	my $query = shift;

printlog($INFO, "DOSELECTALL2 Preparing ....... [$n] - DDDDDDDDDDD: $query");

my $sth = $conn->prepare($query) or printlog($INFO, "Query prepare failed - " . $sth->err);
	$sth->execute();

my $i = 0;
	my $ref;
	my @c_values;
	for($i=0; $i<$n; $i++){
		$ref = $sth->fetchrow_arrayref();
		push(@c_values, [@$ref]);
#		printlog($INFO, "CCCCCCCCCCCCC = " . [@c_values]);
	}

printlog ($INFO, Dumper(@c_values));

$sth->finish();
	\@c_values;
}

sub getnumrows{
	my $query = shift;
	my $newquery = "select count(1) from ($query) tmptab";
	print "Q=$newquery\n";
	local $sth = $conn->prepare($newquery) or return 'ERR';
	$sth->execute();
	local @numrows = $sth->fetchrow_array();
	$sth->finish();

my $n = $numrows[0];

return $n;
}

sub sqlerr{
	printlog($ERROR, $conn->errstr());
}

sub getconn{
	my ($storeid, $stype, $storeip, $openmode, $shname, $crtcsv, $ignrrows, $hdrrow) = @_;

my $newconn;
	if($stype eq 'D'){
		my $dbstore = SQL_dbstore($storeid);
		my ($dsn, $dbname, $dbtype, $dbuser, $dbpass) = @$dbstore;
		format_dbtype(\$dbtype);
		my $dbdsn;

$dbdsn = ($ETLODBC eq '1')?$dsn:"DBI:"."$dbtype:"."$dbname:"."$storeip";

$newconn = opendb($dbdsn, $dbuser, $dbpass);
	}
	elsif($stype eq 'T'){
		my $fstore = SQL_fstore($storeid);
		my ($fpath, $getcmd, $fnamefmt, $fname, $ftype, $fdelim, $fuser, $fpass, $fident, $metaid, $shorder) = @{@$fstore[0]};
		# Currently getcmd an fnamefmt are of no use #
		my $filename = $fpath."/".$fname;
		printlog($MAX, "Connection to file ...  $filename");
		$newconn = openfile($filename, $openmode);
	}
	else{
		my $fstore = SQL_fstore($storeid);
		my ($fpath, $getcmd, $fnamefmt, $fname, $ftype, $fdelim, $fuser, $fpass, $fident, $metaid, $shorder) = @{@$fstore[0]};
		# Will use $crtcsv to indicate csv files already Generated or Need to generate.. right now no Use#	$RXXLS_DIR
		if($crtcsv eq "1"){
			my $filename = $fpath."/".$fname;
			$ignrrows++ if($hdrrow eq "Y");
			printlog($MAX, "PASSING IGNORE ROWS ################################ ($filename, $shname, $ignrrows)");
			rxwritecsv($filename, $shname, $ignrrows, "");
		}
		if($shname eq "*"){
			$shname="allsheets.csv";
		}

my $csvfilename =  $RXXLS_DIR."/".$fname."/".$shname;
		printlog($MAX, "Connection to file ...  $csvfilename");
		$newconn = openfile($csvfilename, $openmode);
	}
	if($newconn eq 'ERR'){
		printlog($ERROR, "Error: $! (Connection to store-$storeid failed).");
		return 'ERR';
	}

$newconn;
}

sub closeconn{
	my ($conn, $type) = @_;
	if($type eq 'D'){
		$conn->disconnect;
	}
	else{
		close($conn);
	}
}

sub getstmt{
	my $myconn = shift;
	my $qry = shift;
	printlog($QUERY, "getstmt Query Recv: $qry");

my $stmt = $myconn->prepare($qry) or return 'ERR'; 
#	$stmt->execute();
	$stmt;
}

sub exec_stmt{
	my $stmt = shift;
	$$stmt->execute() or return 'ERR';
}

sub doselectmany{
	my $stmt = shift;	#for now we will assume only one query.
	my @row = ();
	@row = $stmt->fetchrow_array;
	if ($DBI::err) {
		printlog($ERROR, $DBI::errstr);
		return 'ERR';
	}

if(@row eq ''){
		printlog($ERROR, "No Data");
		return 'ERR';
	}

@row;
}

sub get_qrytask{
	my ($qry) = @_;
	my $retqry;
	my $strid;
	my @q = ();
	my @id = ();
	my $laststrid;

my $qryrow = SQL_qtaskdtl($taskid);
	foreach my $qrypart(@$qryrow){
		$strid = @$qrypart[3];
		if($laststrid eq $strid){
			$retqry = $retqry.@$qrypart[1];	
		}
		else{
			if($retqry ne ''){
				push(@q, $retqry);
				push(@id, $strid);
				$retqry='';
			}
			$retqry = $retqry.@$qrypart[1];	
		}
		$laststrid = $strid;
	}
	if($retqry ne ''){
		push(@q, $retqry);
		push(@id, $strid);
	}

@$qry[0] = \@q;
	@$qry[1] = \@id;
}

sub getdtl_qrytask{
	my ($taskid, $destids, $qrys) = @_;
	my $retqry;
	my $strid;
	my $laststrid;

my $qryrow = SQL_qtaskdtl($taskid);
	foreach my $qrypart(@$qryrow){
		$strid = @$qrypart[3];
		if($laststrid eq $strid){
			$retqry = $retqry.@$qrypart[1];	
		}
		else{
			if($retqry ne ''){
				push(@$qrys, $retqry);
				push(@$destids, $strid);
				$retqry='';
			}
			$retqry = $retqry.@$qrypart[1];	
		}
		$laststrid = $strid;
	}
	if($retqry ne ''){
		push(@$qrys, $retqry);
		push(@$destids, $strid);
	}
}

sub make_selqry{
	my $storegrp = shift;
	my $tasktype = shift;
	my $cols = '';
	my @qry = ();
	my @fromtab = ();
	my $lastid = '';
	$#qry = 0;
	my ($storeid, $tblfile, $colname, $coltype) = ();

if($tasktype eq 'M'){
		foreach $grprow(@$storegrp){
			($storeid, $tblfile, $colname, $coltype) = @$grprow;
			if($storeid eq $lastid){
				$cols = "$cols".", $colname";
			}
			else{
				if($cols ne ''){
					@fromtab = join(",", @fromtab);
					$cols = "$cols"." from @fromtab";
					$qry[$#qry++] = $cols;
					$#fromtab = 0;
				}
				$cols = "select $colname";
			}

push(@fromtab, " $tblfile");  #ADD COMMA FOR MULTI TABLE

undef %saw;
			@saw{@fromtab} = ();
			@fromtab = keys %saw;

$lastid = $storeid;
		}
		if($cols ne ''){
			@fromtab = join(",", @fromtab);
			$cols = "$cols"." from @fromtab";
			$qry[$#qry++] = $cols;
			$#fromtab = 0;
		}

}
	elsif($tasktype eq 'A'){
		foreach $grprow(@$storegrp){
			($storeid, $tblfile, $colname, $coltype) = @$grprow;
			$qry[$#qry++] = "select * from ".$tblfile;
		}
	}
	printlog($MAX, "Select Query : @qry");
	@qry;
}

sub make_insqry{
	my $storegrp = shift;
	my $tasktype = shift;
	my $cols = '';
	my @qry = ();
	my @tblname = ();
	my @totab = ();
	my $lastid = '';
	my @ord_storeid = ();
	my @retval = ();
	$#qry = 0;
	$#tblname= 0;
	my ($storeid, $tblfile, $colname, $coltype) = ();

if(($tasktype eq 'M') || ($tasktype eq 'S')){
		foreach my $grprow(@$storegrp){
			#	printlog($MAX, "DEST MAP @$grprow ");
			($storeid, $tblfile, $colname, $coltype) = @$grprow;
			printlog("($storeid, $tblfile, $colname, $coltype) = @$grprow; last:$lastid : NowTBL=$tblfile lastTBL=$lasttblfile");
			if($storeid eq $lastid){
				$ord_storeid[$#qry] = $storeid;

if($tblfile ne $lasttblfile){
					@totab = join(",", @totab);
					$cols = "$cols".") values (@totab)";
					$tblname[$#tblname++] = $tblfile;
					$qry[$#qry++] = $cols;
					@totab = ();
					$cols = "insert into $tblfile($colname";
					#			printlog($MAX, "# 1 #:VALUES FOR ARRAYS :[COLS<$cols>:TBL<@tblname>:ORDER<@ord_storeid>]");
				}
				else{
					$cols = "$cols".", $colname";
				}
			}
			else{
				if($cols ne ''){
					@totab = join(",", @totab);
					$cols = "$cols".") values (@totab)";
					$tblname[$#tblname++] = $lasttblfile;
					$ord_storeid[$#qry] = $lastid;
					$qry[$#qry++] = $cols;
					@totab = ();
				}
				#else{
				#	$tblname[$#tblname++] = $tblfile;
				#	$ord_storeid[$#ord_storeid++] = $storeid;
				#	}
				$cols = "insert into $tblfile($colname";
				#		printlog($MAX, "# 2 #:VALUES FOR ARRAYS :[COLS<$cols>:TBL<@tblname>:ORDER<@ord_storeid>]");
			}

push(@totab, " ?");  #ADD COMMA FOR MULTI TABLE

$lastid = $storeid;
			$lasttblfile = $tblfile;
		}

if($cols ne ''){
			@totab = join(",", @totab);
			$cols = "$cols"." ) values (@totab)";
			$tblname[$#tblname++] = $lasttblfile;
			$ord_storeid[$#qry] = $storeid;
			$qry[$#qry++] = $cols;
			$#totab = 0;
			#	printlog($MAX, "# 3 #:VALUES FOR ARRAYS :[COLS<$cols>:TBL<@tblname>:ORDER<@ord_storeid>]");
		}

#	undef %saw;
		#	@saw{@ord_storeid} = ();
		#	@ord_storeid = keys %saw;
		#	for(my $q=0; $q<$#qry)
		printlog($MAX, "Storeid : @ord_storeid");
		printlog($MAX, "Queries : @qry");
		printlog($MAX, "Tables : @tblname");
	}
	elsif($tasktype eq 'A'){
		foreach my $grprow(@$storegrp){
			($storeid, $tblfile, $colname, $coltype) = @$grprow;
			$tblname[$#tblname++] = $tblfile;
			$ord_storeid[$#qry] = $storeid;
			$qry[$#qry++] = "insert into $tblfile values ";
		}
	}
	$retval[0] = \@ord_storeid;
	$retval[1] = \@qry;
	$retval[2] = \@tblname;

@retval;
}

sub get_qry{
	my $id = shift;
	my $tblctr = shift;
	my @dest_id = shift;
	my @dest_query = shift;
	my $k = 0;
	for ($j=0; $j<=$#{$dest_id[0]}; $j++) {
		for ($k=0; $k<=$#dest_id; $k++) {
#				printlog($MAX, "XX:",$tblctr, $dest_query[$k][$tblctr],"($k)($j)", $dest_id[$k][$j], $dest_query[$k][$j]," .*&*.");
			if($dest_id[$k][$j] eq $id){
#				printlog($MAX, "Query Sending :[( $dest_id[$k][$j] eq $id ) $k .. $tblctr ] $dest_query[$k][$tblctr]");
				return $dest_query[$k][$tblctr];
			}
		}
	}
}

sub truncate_alldest{
	my ($dest_storeids, $tblfilename, $hdestconn, $hdest_tblfiles) = @_;
	foreach my $destid(@$dest_storeids){
		my ($deststorename, $deststoremode, $deststoretype, $deststoreip) = @{$$hdest_tblfiles{$destid}};
		truncate_dest($destid, $deststoretype, $tblfilename, $hdestconn);
	}
}

sub truncate_dest{
	my ($destid, $desttype, $tblfile, $myconn) = @_;
	if($desttype eq 'D'){
		my $myqry = "truncate table ".$tblfile;

my $doerr = "OK";
		$myconn->do($myqry) or $doerr = "ERR";

if($doerr eq "ERR"){
			printlog($MAX, "Truncate Not Supported Using delete from $tblfile;");
			my $myqry = "delete from ".$tblfile;
			$myconn->do($myqry) or $doerr = "ERR";
			if($doerr eq "ERR"){
				printlog($ERROR, "Could Not delete items from $tblfile;");
			}
		}

}
	elsif($desttype eq 'T'){
	}
}