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

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

require "$ETL_HOME/config.pl";

sub SQL_wfmaster{
	my $wfid = shift;
	my $query = "select ewm_wfname, ewm_wfdesc, ewm_wfstatus from etl_wfmaster where ewm_wfid='$wfid'";
	doselectone("$query");
	}
	
sub SQL_wfdetails{ #Retrieve wflow node order by dtid. Later we will change this for tree structure
	my $wfid = shift;
	my $query = "select ewd_nodeid, ewd_nodetype, ewd_onerror, etm_mapname, ewd_dtid, ewd_posttaskid from etl_wfdetail, etl_tablemapping where ewd_wfid = $wfid and ewd_nodeid = etm_mapid UNION select ewd_nodeid, ewd_nodetype, ewd_onerror, ewm_wfname, ewd_dtid, ewd_posttaskid from etl_wfdetail, etl_wfmaster where ewd_wfid = $wfid and ewd_nodeid = ewm_wfid order by ewd_dtid";
	#my $query = "select ewd_nodeid, ewd_nodetype, ewd_onerror, etm_mapname from etl_wfdetail, etl_tablemapping where ewd_wfid='$wfid' and ewd_nodeid>0 and ewd_nodeid=etm_mapid order by ewd_dtid";

doselectall("$query");
	}

sub SQL_tablemapping{
	$taskid = shift;
	my $query  = "select etm_mapname, etm_repid, etm_storeid, etm_srctableid, etm_destid, etm_valid, etm_xfdisable, etm_validdisable, etm_xftype from etl_tablemapping where etm_mapid='$taskid' and etm_mapid > 0 ";

doselectone("$query");
	}

sub SQL_proctaskdetail{
	$taskid = shift;
	my $query  = "select ept_taskid, ept_pathname, ept_cmd, ept_input , ept_output from etl_proctaskdetail where ept_taskid='$taskid' and ept_taskid > 0 ";

doselectone("$query");
	}
	
sub SQL_taskdetail{
	$taskid = shift;
	my $query = "select etd_srcstoreid, etd_src_tblfile, etd_src_tf_colname, etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname, etd_maporder, etd_srccoldatatyp from etl_taskdetail where etd_taskid='$taskid' and etd_taskid > 0 and etd_maporder > 0 order by etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname";
	
	doselectall("$query");
	}

sub SQL_taskstore {
	my $taskid = shift;
	my $src_dest= shift;

my $query = "select ";
	
	if($src_dest eq 'src'){
		$query="select etd_srcstoreid, etd_src_tblfile, etd_src_tf_colname, etd_srccoldatatyp from etl_taskdetail 
			where etd_taskid='$taskid'  and etd_maporder > 0 and  etd_srcstoreid > 0  
			order by etd_maporder, etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname";#First order was etd_maporder, 
		} 
	elsif($src_dest eq 'dest'){
		$query="select  etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname from etl_taskdetail  
			where etd_taskid='$taskid'  and etd_maporder > 0 and  etd_deststoreid > 0  
			order by etd_maporder, etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname"; #First order was etd_maporder, 
		}

return doselectall("$query");
	}

sub SQL_task_destcol{
	my $taskid = shift;

my $query = "select etd_deststoreid, etd_dest_tblfile, count(etd_dest_tf_colname) from etl_taskdetail 
				where etd_taskid='$taskid'  and etd_maporder > 0 
				group by etd_dest_tblfile, etd_deststoreid 
				order by etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname";

doselectall($query);
	}
	
sub SQL_task_destcolname{#same as SQL_taskstore
	my ($taskid, $desttype) = @_;
	my $query ;

if($desttype eq 'D'){
		$query = "select etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname  from etl_taskdetail 
						where etd_taskid='$taskid'  and etd_maporder > 0 and  etd_deststoreid > 0
						order by etd_maporder, etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname";#added etd_maporder
		}
	else{
		$query = "select etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname  from etl_taskdetail
						where etd_taskid='$taskid'  and etd_maporder > 0 and  etd_deststoreid > 0
						order by etd_maporder, etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname";#added etd_maporder
		}
				   
	doselectall($query);
	}
	
sub SQL_tablemaster{
	my $tblid = shift;
	#BUG:859 -> Added one column etm_maskformat to stre the mask format
	my $query = "select etm_dbmaskid, etm_tablename, etm_tabtype, etm_srcdest, etm_storeid, etm_maskformat from etl_tablemaster where etm_tableid = $tblid";
	doselectone($query);
}

sub SQL_tablekeys{
	my $tblid = shift;

my $query = "select etk_prikey, etk_index, etk_colname, etk_keyindex from etl_tablekeys where etk_tableid = $tblid";
	doselectall($query);
}
sub SQL_snapcol_details{
	my ($snapdatecol) = shift;

my $query = "select ecm_colname from etl_columnmaster where ecm_colid=$snapdatecol";
	doselectone($query);
	}

sub SQL_desttable_details{
	my $mapid = shift;

#my $query = "select edd_tableid, edd_trunctable, edd_replacedata, edd_postsqlid, edd_presqlid, edd_snapshot, edd_snapfreq, ecm_colname, edd_snapdatecol from etl_desttable_details, etl_columnmaster where edd_tableid = $tblid and edd_snapdatecol=ecm_colid";
	my $query = "select edd_mapid, edd_trunctable, edd_replacedata, edd_postsqlid, edd_presqlid, edd_snapshot, edd_snapfreq, edd_snapdatecol from etl_desttable_details where edd_mapid = $mapid";

doselectone($query);
}
	
sub SQL_columnmaster{
	my $tblid = shift;

my $query = "select ecm_colid, ecm_colname, ecm_coltype, ecm_colorder, ecm_colsize, ecm_defaultval, ecm_nullable from etl_columnmaster where ecm_tableid = $tblid order by ecm_colorder";#dont remove order by
	doselectall($query);
}

sub SQL_columnmaster_NoSource{
	my $tblid = shift;

my $query = "select ecm_colid, ecm_colname, ecm_coltype, ecm_colorder, ecm_colsize, ecm_defaultval, ecm_nullable from etl_columnmaster where ecm_tableid = $tblid  and ecm_colname != 'No_Source' order by ecm_colorder";#dont remove order b y
	doselectall($query);
}
sub SQL_querydetails{
	my $tableid = shift;

my $query = "select eqd_query, eqd_seqid from etl_querydetails where eqd_qryid = $tableid order by eqd_seqid";
	my $qrypart = doselectall($query);
	$query = '';
	foreach my $part(@$qrypart){
		@$part[0] =~ s/^{//;     # Bug No 1222
		@$part[0] =~ s/}$//;
		$query = $query.@$part[0];
		}

return $query;
}
	
sub SQL_storemaster {
	my $storeid = shift;

my $query = "select est_storename, est_storetype, est_dirdsn, est_createdate, est_storevalid, est_storedesc, est_lstupdt_ts, est_expand, est_storecat, est_dbdirid from etl_store where est_storeid = $storeid";

doselectone($query);
	}

sub SQL_dbstore {
	my $storeid = shift;
	my $query = "select edb_dsn, edb_name, edb_type, edb_user, edb_pass, edb_hostname, edb_port, edb_dbschema from etl_dbstore where edb_storeid='$storeid'";

doselectone($query);
	}

sub SQL_fstore_map{
	my $mapid = shift;

my $query = "select M.etm_storeid, M.etm_srctableid, T.etm_tablename, T.etm_valid, F.efs_maskfmt, F.efs_ftype, F.efs_fdelim, F.efs_txtquali from etl_tablemapping M, etl_tablemaster T, etl_fstore F where M.etm_mapid=$mapid and T.etm_tableid = M.etm_srctableid and T.etm_dbmaskid = F.efs_maskid";

doselectone($query);
	}

sub update_stginfo{
	my ($infile, $stgtime, $stat, $msg) = @_;

my $query = "update etl_stginfo set esi_stgtime=$stgtime, esi_status=\'$stat\', esi_message=\'$msg\' where esi_infile=\'$infile\'";

execute_query($conn, $query);
}

sub add_stginfo{
	my ($infile, $atime, $ctime, $size, $stgtime, $stat, $msg) = @_;

my $query = "insert into etl_stginfo (esi_infile, esi_atime, esi_ctime, esi_size, esi_stgtime, esi_status, esi_message) values (\'$infile\', $atime, $ctime, $size, $stgtime, \'$stat\', \'$msg\')";

execute_query($conn, $query);
}

sub SQL_stginfo{
	my $infile = shift;

my $query = "select esi_id, esi_atime, esi_ctime, esi_size, esi_stgtime, esi_status, esi_message from etl_stginfo where esi_infile = '$infile'";

doselectone($query);
	}

sub SQL_fstore{
	my $maskid = shift;

my $query = "select efs_storeid, efs_maskfmt, efs_disp_name, efs_desttbl, efs_ftype, efs_fdelim, efs_txtquali, efs_fidentmeta, efs_archsource, efs_archfnamefmt, efs_archdir, erp_lstupdt_ts, efs_valid, efs_expand from etl_fstore where efs_maskid = $maskid";

doselectone($query);	
	}

sub OLDSQL_fstore {
	my $storeid = shift;
	my $metaid = shift;
	my $query; 
	printlog($MAX, "METAID::$metaid");
	if($metaid ne ""){
		$query = "select efs_fpath, efs_fgetcmd, efs_fnamefmt, efs_fname, efs_ftype, efs_fdelim, efs_fuser, efs_fpass, efs_fidentmeta, efm_metaid, efm_shorder from etl_fstore, etl_fmaster where efs_storeid=efm_storeid and efs_storeid='$storeid' and efm_metaid = '$metaid'";
		}
	else{
		$query = "select efs_fpath, efs_fgetcmd, efs_fnamefmt, efs_fname, efs_ftype, efs_fdelim, efs_fuser, efs_fpass, efs_fidentmeta, efm_metaid, efm_shorder from etl_fstore, etl_fmaster where efs_storeid=efm_storeid and efs_storeid='$storeid'";
		}
	
	doselectall($query);
	}

sub get_validation_count{
	my $mapid = shift;

my $query = "select count(ecv_mapid) from etl_col_validation where length(ecv_rule) and ecv_mapid=$mapid";
	my $res = doselectone($query);

return $res[0];
	}

sub get_xform_count{
	my $mapid = shift;

my $query = "select count(ecx_mapid) from etl_col_xform where length(ecx_xform) and ecx_mapid=$mapid";
	my $res = doselectone($query);

return $res[0];
	}

sub SQL_xform{
	my $mapid = shift;

my $query = "select ecx_srccolid, src.ecm_colname, ecx_destcolid , dest.ecm_colname, ecx_include, ecx_desttabid, ecx_srctabid, ecx_xform from etl_col_xform join etl_columnmaster src left join etl_columnmaster dest on dest.ecm_colid = ecx_destcolid where src.ecm_colid = ecx_srccolid and ecx_mapid = $mapid and dest.ecm_colname is NOT NULL order by ecx_colorder";

doselectall($query);
}

sub SQL_col_validation{
	my $mapid = shift;
	my $query = '';
#	$query = "select ecv_rule, ecv_failvalue, ecv_colid, ecv_cond, ecv_colorder from etl_col_validation where ecv_mapid = $mapid order by ecv_colorder";
	#$query = "select ecv_rule, ecv_failvalue, ecv_colid, ecm_colname, ecv_cond, ecv_colorder from etl_col_validation, etl_columnmaster where ecv_mapid = $mapid and  ecv_colid  = ecm_colid order by ecv_colorder";
	$query = "select ecv_rule, ecv_failvalue, ecv_colid, ecm_colname, ecv_cond, ecv_colorder, ecx_include from etl_col_validation, etl_columnmaster, etl_col_xform where ecv_mapid = $mapid and ecv_mapid = ecx_mapid and ecv_colid = ecm_colid and ecv_colid = ecx_srccolid order by ecv_colorder";

doselectall($query);
}
	
sub SQL_srctable_details{
	my $tblid = shift;
	my $query = "select esd_skipnrows, esd_ishdrrow from etl_srctable_details where esd_tableid=$tblid";

doselectone($query);
}

sub SQL_fmaster {
	my $storeid = shift;
	my $metaid = shift;
	my $query = "select efm_shname, efm_ignrrows, efm_hdrrow, efm_metaid, efm_shorder from etl_fmaster where efm_storeid='$storeid' and efm_metaid = '$metaid'";
	
	doselectall($query);
	}

sub SQL_fmaster_metaid{
	my $storeid = shift;
	my $shname = shift;
	my $query = "select efm_metaid from etl_fmaster where efm_storeid='$storeid' and efm_shname = '$shname'";

doselectone($query);
	}

sub SQL_fmetadata{
	my $storeid = shift;
	my $metaid = shift;

my $query = "select efm_storeid, efm_colname, efm_coltype, efm_fwidth, efm_colorder from etl_fmetadata where efm_storeid='$storeid' and efm_metaid='$metaid' order by efm_colorder";

doselectall($query);
	}
	
sub SQL_fmetawdth{
	my ($storeid, $metaid) = @_;
	my $query = "select efm_fwidth from etl_fmetadata where efm_storeid='$storeid' and efm_metaid='$metaid' order by efm_colorder";

doselectall($query);
	}
	
sub SQL_mapcol{
	my $taskid = shift;
	my $desttype = shift;
	my $srctype = shift;
	my $query;

if($desttype eq 'D'){
		$query ="select etd_taskid, etd_srcstoreid, etd_src_tblfile ,etd_src_tf_colname ,etd_deststoreid ,etd_dest_tblfile ,etd_dest_tf_colname ,etd_maporder ,etd_srccoldatatyp ,etd_destcoldatatyp ,etd_dest_tblfilename ,etd_src_tblfilename from etl_taskdetail where etd_taskid='$taskid' and  etd_maporder > 0 order by etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname";
		}
	elsif($desttype eq 'T'){ # Case (I):DB-FILE  & Case (II):FILE-FILE
		$query = "select efm_colorder, etd_srcstoreid, etd_src_tblfile, etd_src_tf_colname, etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname from (select * from etl_taskdetail, etl_fmetadata where etd_taskid = '$taskid' and etd_maporder>0 and efm_storeid = etd_deststoreid and efm_colname = etd_dest_tf_colname) as a order by etd_dest_tf_colname";
		}
	elsif($srctype eq 'T' || $srctype eq 'E'){ # Case (I):FILE-DB
		$query = "select efm_colorder, etd_srcstoreid, etd_src_tblfile, etd_src_tf_colname, etd_deststoreid, etd_dest_tblfile, etd_dest_tf_colname from etl_taskdetail, etl_fmetadata where etd_taskid='$taskid' and  etd_maporder > 0 and efm_storeid=etd_srcstoreid and etd_src_tf_colname=efm_colname order by efm_colorder, etd_deststoreid, etd_dest_tblfile";
		}
	doselectall($query);
	}
	
sub SQL_qtaskdtl{
	my $taskid = shift;
	my $query = "select eqt_taskid, eqt_query, eqt_seqid, eqt_strid from etl_querytaskdetail where eqt_taskid='$taskid' order by eqt_strid, eqt_seqid";
	doselectall($query);
	}

sub SQL_prcsmaster{
	my $prcsid = shift;
	my $qry = "select epm_proc_name, epm_proc_desc, epm_proc_type, epm_createdate, epm_proc_params from etl_prcs_master where epm_proc_id='$prcsid'";
	doselectone($qry);
	}

sub  SQL_wfdetails_etluser{
	my $wfid = shift;
	my  $storeid=get_storeid();
	my $query = "select ewd_nodeid, ewd_nodetype, ewd_onerror, etm_mapname, ewd_dtid, ewd_posttaskid from etl_wfdetail, etl_tablemapping where ewd_wfid = $wfid and ewd_nodeid = etm_mapid and etm_storeid in (@$storeid) UNION select ewd_nodeid, ewd_nodetype, ewd_onerror, etm_mapname, ewd_dtid, ewd_posttaskid from etl_wfdetail, etl_tablemapping where ewd_nodeid = etm_mapid and ewd_wfid = $wfid and ewd_nodetype != 'T' UNION select ewd_nodeid, ewd_nodetype, ewd_onerror, ewm_wfname, ewd_dtid, ewd_posttaskid from etl_wfdetail, etl_wfmaster where ewd_wfid = $wfid and ewd_nodeid = ewm_wfid order by ewd_dtid";
	doselectall("$query");
}

sub get_storeid{
	my $user=$CMD_HARGS{'etluser'};
	my $query = "select esp_storeid from etl_strpermission where esp_user='$user'";
	doselectone("$query");
}

sub get_dateform_col{
      my $mapid= shift;
      my $qry="select ecm_dateformat, ecm_colorder from etl_columnmaster,etl_tablemapping where ecm_tableid=etm_destid and etm_mapid='$mapid' and  ecm_coltype like 'date%' and ecm_dateformat !='' and ecm_dateformat is NOT NULL"; 
         doselectall($qry); 
}

sub get_install_param{
        my $ipid=shift;
        my $query= "select ip_val from install_params where ip_id=$ipid" ;
        doselectone("$query");
}
  
1;