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

#!/usr/bin/perl

use DBI;

$col_values=''; #global var for sql results

sub configetl{
	my $cubdb = shift;
	my $dsn;
	my ($dbtype, $database, $host, $user, $pass, $home) =  (); 
	($dbtype, $database, $host, $user, $pass, $home) =  get_etlparams();

$database = $ENV{ED_DB};

$database = $cubdb if($cubdb ne '');

die "No database selected\n" if($database eq '');
	printlog($INFO, "Using database $database");

$dsn = ($ETLODBC eq '1') ?  $ENV{ED_DSN} : "DBI:$dbtype:$database:$host";
	
	$conn = opendb($dsn, $user, $pass);
	if($conn eq 'ERR'){
		printlog($ERROR, "Connection Failed.Can not connect to database using dsn=$dsn, user=$user password=$pass");
		exit(1);
		}

printlog($INFO, "Connected to $user\@$dsn");
	}
	
sub get_etlparams{
	$database = $ENV{ED_DB};
	$user     = $ENV{CUBOT_DBUSER};
	$pass     = $ENV{CUBOT_DBPASS};
	$home     = $ENV{CUBOT_HOME};
	$host     = $ENV{CUBOT_DBHOST};
	$dbtype   = 'mysql';                    #'$CUBOT_DBT';

return $dbtype, $database, $host, $user, $pass, $home;
	}

sub setglob{
    $conn = undef;
    $G_CurWflowId = 0;
    $G_ParWflowId = 0;
    $G_CurTaskId = 0;
    $G_CurTaskType = 0;
    $G_NumRecProc = 0;
    $G_NumRecIgnr = 0;
    $G_NumRecTotal = 0;
    $G_NumRecDisc = 0;
    $G_NumDest = 0;
    $G_ETLMode = 0;
    $G_logseq = 0;
    $ETLMode = 0;
    $G_Err = '';
    $G_Purpose = '';
    $G_inputDataLine = '';
    @G_inputDataArray = ();
    $G_DB = '';
    $G_Datetime = '';
    $G_Log_Outfile = '';
    $DATEWISE_LOG = '';
    $G_xml_stg_dirname = '';
    $G_enclosed = '';
    $G_ora='N';
    $G_numcols=0;
    $G_datecol='N';
    $G_fname = '';
  }
	
sub open_etllogs{
	if(!-e $ETL_LOG){
		`mkdir $ETL_LOG`;
		}
	my $etldb = $CMD_HARGS{'db'};
	open(STDOUT, ">>$ETL_LOG/${etldb}_etl.log");
	open(STDERR, ">>$ETL_LOG/${etldb}_etl.log");
	open(ERROR, ">>$ETL_LOG/errors.txt");
	printlog($INFO, "############## ETL Started ##############");
	}
	
sub opendb{
	my $dsn = shift;
	my $user = shift;
	my $password = shift;
	my $dbh;

if($ETLODBC eq '1'){
		#print "**ODBC**\n";
		#$dbh = DBI->connect("dbi:ODBC:$dsn", "$user", "$pass", {PrintError => 1, RaiseError => 1, AutoCommit => 0}) or return 'ERR';
		#$dbh = DBI->connect("dbi:ODBC:$dsn", "$user", "$pass") or return 'ERR';
		$dbh = DBI->connect("dbi:ODBC:$dsn", "$user", "$password") or return printerr_logseq($DBI::errstr, 'a');
		}
	else{
		$password = '' if not defined $password;
		$user = '' if not defined $user;
		#print "***NON-ODBC***\n";
		#$dbh = DBI->connect($dsn, $user, $password, {PrintError => 1, RaiseError => 1, AutoCommit => 0}) or return 'ERR';
		#$dbh = DBI->connect($dsn, $user, $password) or return 'ERR';
		#$dbh = DBI->connect("$dsn", "$user", "$password") or return printerr_logseq($DBI::errstr, 'a');
		$dbh = DBI->connect("$dsn", "$user", "$password") or return printerr_logseq($DBI::errstr, 'a');
		}
	$dbh->{mysql_auto_reconnect} = 1;
	$dbh;
	}

sub getlogseq_fromfile{
	open(G_LOGSEQ, "$ETL_LOG/logseq.rx") or printlog($ERROR, "Error : $!"); #or genlogseqfile();
	my $logseq = <G_LOGSEQ>;
	printlog($MAX, "Getting logseq($logseq) from -- $ETL_LOG/logseq.rx");
	close(G_LOGSEQ);
	chomp($logseq);
	my $logseqval = 0 + $logseq; #Converting into int
	$logseqval;
	}

sub updtlogseq_file{
	my $logseq = shift;
	open(G_LOGSEQ, ">$ETL_LOG/logseq.rx") or printlog($ERROR, "Error : $!"); #or genlogseqfile();	
	printlog($INFO, "Writing $logseq into file : >$ETL_LOG/logseq.rx");
	print G_LOGSEQ $logseq;
	close(G_LOGSEQ);
	}

sub printerr_logseq{
	my $errmsg = shift;
	my $mode = shift;

my $logseq = getlogseq_fromfile();
	printlog($MAX, "Here Recv : $logseq");
	$logseq = 1 + $logseq;							#	Next logsq
	printlog($MAX, "New logseq : $logseq");

$mode = ($mode eq 'a') ? ">>" : ">";			#	Append or Write
	open(G_ERRSEQINS, "$mode"."$ETL_LOG/errlogseq.rx") or printlog("Error : $!");
	printlog($MAX, "INSIDE printerr_logseq : $mode .. $errmsg <--- Open ($mode$ETL_LOG/errlogseq.rx");

#Note : logid is AutoIncrement field . 
	#		if CUBDB is not supporting auto incr. then Save logid also into nextline of logseq file 
	#		& use that for next logid. I Can do but bit lazy.
	my $stime = time;
	my $etime = time;
	my $qry = "insert into " . $CMD_HARGS{'db'}.".etl_tasklog(etl_logid, etl_wfname, etl_parwfname, etl_taskname, etl_st_time, etl_end_time, etl_rec_total, etl_rec_proc, etl_rec_disc, etl_rec_ignr, etl_chkpoint, etl_status, etl_logseq,etl_sysprcsid) values ('', \'$CMD_HARGS{'wfid'}\', '', '', $stime, $etime , '', '', '', '', '', \"$errmsg\", $logseq, $$);";
	print G_ERRSEQINS "$qry\n";
	
	close(G_ERRSEQINS);

updtlogseq_file($logseq);

return 'ERR';
	}

sub closedb{
	my $dbh = shift;
	my $result = $dbh->disconnect;
	}
	
sub statuslog{
	print $DATEWISE_LOG $ETLUSER." - ".printtime()." - @_ \n" ;
	printlog($INFO, @_);
	}
	
sub printlog{
	my $DEBUG = shift;
	my $MSG = shift;
	my $ERR = "";

return if $DEBUG eq $MIN;

if($DEBUG le $Queryout && $MSG ne ''){
		$ERR="ERROR:" if $DEBUG eq $ERROR; 
		print STDERR $ETLUSER." - ".printtime()." - ".$ERR.$MSG."\n" ;
	}
}

sub cleanup{
	#close ETL db 
	closedb($conn);
	printlog($INFO, "############## ETL Done  ##############");

#close log files
	close STDERR;
	close STDOUT;
	close ERROR;
	closeerr_logseq();
	}
	
sub getnextlogseq(){
	my $qry = "Select max(etl_logseq) from etl_tasklog";
	my $res = doselectone($qry);
	my $logseq = @$res[0] + 1;
	$logseq; 
	}
	
sub etl_dblog{
	my ($logid, $wfname, $parwfid, $taskname, $sttime, $etime, $total, $proc, $disc, $ignr, $chkpoint, $status, $logseq, $wfid, $taskid) = @_;
	my $qry = '';
	my $res = '';

my $runuser=$CMD_HARGS{"etluser"};
        if($runuser eq ""){
           $runuser="cron";
         }
	
	if($logid eq ''){
			$qry = "Select max(etl_logid) from etl_tasklog";
			$res = doselectone($qry);
			$logid = @$res[0] + 1;
		#	}
		$qry = "insert into etl_tasklog(etl_logid, etl_wfname, etl_parwfname, etl_taskname, etl_st_time, etl_end_time, etl_rec_total, etl_rec_proc, etl_rec_disc, etl_rec_ignr, etl_chkpoint, etl_status, etl_logseq, etl_wfid, etl_nodeid, etl_sysprcsid, etl_runby) values ('$logid', '$wfname', '$parwfid', '$taskname', '$sttime', '$etime', '$total', '$proc', '$disc', '$ignr', '$chkpoint', '$status', '$logseq', '$wfid', '$taskid', $$,  '$runuser')";
		}
	else{
		$qry = "update etl_tasklog set etl_end_time='$etime' , etl_rec_total = '$total' , etl_rec_proc='$proc' , etl_rec_disc='$disc' , etl_rec_ignr='$ignr' , etl_chkpoint='$chkpoint' , etl_status='$status', etl_sysprcsid=-1 , etl_runby='$runuser' where etl_logid = '$logid'";
		}
	printlog($QUERY, $qry);

execute_query($conn, $qry);
	return $logid;
	}
	
sub decpass{
	my $pass = shift;
	printlog($MAX, "Decode Pass :[$pass]");
	}
1;