OXIESEC PANEL

Name	Size	Modified	Perms
📁 ..	-	02/03/2023 08:06:37 AM	rwxr-xr-x
📄 config.pl	6.53 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 dataextract.sh	2.51 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 etl.pl	5.46 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 etlproc	11.81 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 func.pl	4.01 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 globvar.sh	330 bytes	02/03/2023 07:34:08 AM	rw-r--r--
📄 initetl.pl	1.88 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 libawk.awk	3.07 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 makeXtab2NormalExcel.pl	5.25 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 process.pl	6.92 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 process_changed.pl	5.69 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 readwrite.pl	4.16 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 rxcsv.pl	7.94 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 rxdb.pl	8.84 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 rxfile.pl	4.72 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 rxsalesforce.pl	3.33 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 rxsalesforce.pl.bak	3.33 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 rxxml.pl	12.66 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 rxxml2.pl	4.18 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 s.log	8.86 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 sendmail.pl	1.06 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 sourcequery.pl	4.17 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 sql.pl	13.89 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 sql_mssql.pl	13.89 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 store.pl	7.6 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 task.pl	30.55 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 task_mssql.pl	30.8 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 tmpxml.pl	1.49 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 userfunc.pl	5.5 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 valid.pl	3.9 KB	02/03/2023 07:34:08 AM	rw-r--r--
📄 workflow.pl	3.72 KB	02/03/2023 07:34:08 AM	rwxr-xr-x
📄 xform.pl	2.62 KB	02/03/2023 07:34:08 AM	rw-r--r--

Editing: rxdb.pl

#!/usr/bin/perl

require "$ETL_HOME/config.pl";

######################################################################################
sub execute_query{
	my $cn = shift;
	my $qry = shift;
	printlog($QUERY, $qry);
	$cn->do($qry);
	}
	
sub doselectone{
	my $query = shift;
	printlog($QUERY, $qry);
	$col_values = $conn->selectrow_arrayref("$query") or return 'ERR';
	$col_values;
	}
	
sub doselectall{#same as one but later we can optimize
	my $query = shift;
	printlog($QUERY, $qry);
	$col_values = $conn->selectall_arrayref("$query") or return 'ERR';
	$col_values;
	}

#sub doselectmany{# rewrite this
#	my $query = shift;
#	$col_values = $conn->selectall_arrayref("$query");
#	$col_values;
#	}

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'){
	}
}