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: 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_DB} : "DBI:$dbtype:$database:$host";
	#$dsn = ($ETLODBC eq '1') ?  $ENV{ED_DSN} : "DBI:$dbtype:$database:$host";

$conn = opendb($dsn, $user, $pass);
	$G_mydestdbconn = undef;

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");#, {odbc_cursortype => DBI::SQL_CURSOR_DYNAMIC, PrintError => 1, RaiseError => 1}) 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');
		$dbh->{ChopBlanks} = 1;
	}
	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;
	}

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;
	etl_dblog('', $CMD_HARGS{'wfid'}, '', '', $sttime, $etime, $total, '', '', '', '', $errmsg, $logseq, $CMD_HARGS{'wfid'}, $$);
	#my $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_sysprcsid) values ($logid, \'$CMD_HARGS{'wfid'}\', '', '', $stime, $etime , '', '', '', '', '', \"$errmsg\", $logseq, $$);"; #TODO: ODBC-BUG
	#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, $$);"; #TODO: ODBC-BUG
	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 = '';

printlog($INFO,"########## $logid, $wfname, $parwfid, $taskname, $sttime, $etime, $total, $proc, $disc, $ignr, $chkpoint, $status, $logseq, $wfid, $taskid");
	my $runuser=$CMD_HARGS{"etluser"};
	if($runuser eq ""){
		$runuser="cron";
	}

$sttime = $sttime ? $sttime : 'NULL';
	$etime = $etime ? $etime : 'NULL';
	$total = $total ? $total : 'NULL';
	$proc = $proc ? $proc : 'NULL';
	$disc = $disc ? $disc : 'NULL';
	$ignr = $ignr ? $ignr : 'NULL';
	$logseq = $logseq ? $logseq : 'NULL';
	$wfid = $wfid ? $wfid : 'NULL';
	$taskid = $taskid ? $taskid : 'NULL';
	$parwfid = $parwfid ? $parwfid : 'NULL';

if($logid eq ''){
		$qry = "select max(etl_logid) as mx 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;