NAME

EAI::Wrap - framework for easy creation of Enterprise Application Integration tasks

SYNOPSIS

# site.config
%config = (
	sensitive => {
		myftp => {user => 'someone', pwd => 'password', privKey => 'pathToPrivateKey', hostkey => 'hostkey to be presented'},
		mydb => {user => 'someone', pwd => 'password'}
	},
	checkLookup => {"task_script.pl" => {errmailaddress => "test\@test.com", errmailsubject => "testjob failed", timeToCheck => "0800", freqToCheck => "B", logFileToCheck => "test.log", logcheck => "started.*"}},
	folderEnvironmentMapping => {Test => "Test", Dev => "Dev", "" => "Prod"},
	errmailaddress => 'To@somewhere.com',
	errmailsubject => "errMailSubject",
	fromaddress => 'from@somewhere.com',
	smtpServer => "MailServer",
	smtpTimeout => 60,
	logRootPath => "C:/dev/EAI/Logs",
	historyFolder => "History",
	redoDir => "redo",
	task => {
		retrySecondsErr => 60*5,
		retrySecondsPlanned => 60*15,
	},
	DB => {
		server => {Prod => "ProdServer", Test => "TestServer"},
		cutoffYr2000 => 60,
		DSN => 'driver={SQL Server};Server=$DB->{server}{$execute{env}};database=$DB->{database};TrustedConnection=Yes;',
		schemaName => "dbo",
	},
	FTP => {
		maxConnectionTries => 5, 
		plinkInstallationPath => "C:/dev/EAI/putty/PLINK.EXE",
	},
	File => {
		format_thousandsep => ",",
		format_decimalsep => ".",
	}
);

# task_script.pl
use EAI::Wrap;
%common = (
	FTP => {
		remoteHost => {"Prod" => "ftp.com", "Test" => "ftp-test.com"},
		remoteDir => "/reports",
		port => 22,
		user => "myuser",
		privKey => 'C:/keystore/my_private_key.ppk',
		FTPdebugLevel => 0, # ~(1|2|4|8|16|1024|2048)
	},
	DB => {
		tablename => "ValueTable",
		deleteBeforeInsertSelector => "rptDate = ?",
		dontWarnOnNotExistingFields => 1,
		database => "DWH",
	},
	task => {
		plannedUntil => "2359",
	},
);
@loads = (
	{
		File => {
			filename => "Datafile1.XML",
			format_XML => 1,
			format_sep => ',',
			format_xpathRecordLevel => '//reportGrp/CM1/*',
			format_fieldXpath => {rptDate => '//rptHdr/rptDat', NotionalVal => 'NotionalVal', tradeRef => 'tradeRefId', UTI => 'UTI'}, 
			format_header => "rptDate,NotionalVal,tradeRef,UTI",
		},
	},
	{
		File => {
			filename => "Datafile2.txt",
			format_sep => "\t",
			format_skip => 1,
			format_header => "rptDate	NotionalVal	tradeRef	UTI",
		},
	}
);
setupEAIWrap();
openDBConn(\%common) or die;
openFTPConn(\%common) or die;
while (!$execute{processEnd}) {
	for my $load (@loads) {
		getFilesFromFTP($load);
		if (checkFiles($load)) {
			readFileData($load);
			dumpDataIntoDB($load);
			markProcessed($load);
		}
	}
	processingEnd();
}

DESCRIPTION

EAI::Wrap provides a framework for defining EAI jobs directly in Perl, sparing the creator of low-level tasks as FTP-Fetching, file-parsing and storing into a database. It also can be used to handle other workflows, like creating files from the database and uploading to FTP-Servers or using other externally provided tools.

The definition is done by first setting up configuration hashes and then providing a high-level scripting of the job itself using the provided API (although any perl code is welcome here!).

EAI::Wrap has a lot of infrastructure already included, like logging using Log4perl, database handling with DBI and DBD::ODBC, FTP services using Net::SFTP::Foreign, file parsing using Text::CSV (text files), Data::XLSX::Parser and Spreadsheet::ParseExcel (excel files), XML::LibXML (xml files), file writing with Spreadsheet::WriteExcel and Excel::Writer::XLSX (excel files), Text::CSV (text files).

Furthermore it provides very flexible commandline options, allowing almost all configurations to be set on the commandline. Commandline options (e.g. additional information passed on with the interactive option) of the task script are fetched at INIT allowing use of options within the configuration, e.g. $opt{process}{interactive_startdate} for a passed start date.

Also the logging configured in $ENV{EAI_WRAP_CONFIG_PATH}/log.config (logfile root path set in $ENV{EAI_WRAP_CONFIG_PATH}/site.config) starts immediately at INIT of the task script, to use a logger, simply make a call to get_logger(). For the logging configuration, see EAI::Common, setupLogging.

API

%config

global config (set in $ENV{EAI_WRAP_CONFIG_PATH}/site.config, amended with $ENV{EAI_WRAP_CONFIG_PATH}/additional/*.config), contains special parameters (default error mail sending, logging paths, etc.) and site-wide pre-settings for the five categories in task scripts, described below under configuration categories)

%common

common configs for the task script, may contain one configuration hash for each configuration category.

@loads

list of hashes defining specific load processes within the task script. Each hash may contain one configuration hash for each configuration category.

configuration categories

In the above mentioned hashes can be five categories (sub-hashes): DB, File, FTP, process and task. These allow further parameters to be set for the respective parts of EAI::Wrap (EAI::DB, EAI::File and EAI::FTP), process parameters and task parameters. The parameters are described in detail in section CONFIGURATION REFERENCE.

The process category is on the one hand used to pass information within each process (data, additionalLookupData, filenames, hadErrors or custom commandline parameters starting with interactive), on the other hand for additional configurations not suitable for DB, File or FTP (e.g. uploadCMD). The task category contains parameters used on the task script level and is therefore only allowed in %config and %common. It contains parameters for skipping, retrying and redoing the whole task script.

The settings in DB, File, FTP and task are "merge" inherited in a cascading manner (i.e. missing parameters are merged, parameters already set below are not overwritten):

%config (defined in site.config and other associated configs loaded at INIT)
merged into ->
%common (common task parameters defined in script)
merged into each of ->
$loads[]

special config parameters and DB, FTP, File, task parameters from command line options are merged at the respective level (config at the top, the rest at the bottom) and always override any set parameters. Only scalar parameters can be given on the command line, no lists and hashes are possible. Commandline options are given in the format:

--<category> <parameter>=<value>

for the common level and

--load<i><category> <parameter>=<value>

for the loads level.

Command line options are also available to the script via the hash %opt or the list of hashes @optloads, so in order to access the cmdline option --process interactive_date=202300101 you could either use $common{process}{interactive_date} or $opt{process}{interactive_date}.

In order to use --load1process interactive_date=202300101, you would use $loads[1]{process}{interactive_date} or $optloads[1]{process}{interactive_date}.

The merge inheritance for DB, FTP, File and task can be prevented by using an underscore after the hashkey, ie. DB_, FTP_, File_ and task_. In this case the parameters are not merged from common. However, they are always inherited from config.

%execute

hash of parameters for current task execution which is not set by the user but can be used to set other parameters and control the flow. Most important here are $execute{env} giving the current used environment (Prod, Test, Dev, whatever), $execute{envraw} (Production is empty here), the several files lists (being procesed, for deletion, moving, etc.), flags for ending/interrupting processing, directory locations as home and history, etc.

Detailed information about the several parameters used can be found in section execute of the configuration parameter reference, there are parameters for files (filesProcessed, filesToArchive, filesToDelete, filesToMoveinHistory, filesToMoveinHistoryUpload, filesToRemove and retrievedFiles), directories (homedir, historyFolder, historyFolderUpload and redoDir), process controlling parameters (failcount, firstRunSuccess, retryBecauseOfError, retrySeconds and processEnd).

Retrying with querying $execute{processEnd} can happen on two reasons: First, because task => {plannedUntil => "HHMM"} is set to a time until the task has to be retried, however this is done at most until midnight. Second, because an error occurred, in this case $process->{hadErrors} is set on each load that failed. $execute{retryBecauseOfError} is also important in this context as it prevents the repeated run of following API procedures if the process didn't have an error:

getLocalFiles, getFilesFromFTP, getFiles, checkFiles, extractArchives, getAdditionalDBData, readFileData, dumpDataIntoDB, writeFileFromDB, putFileInLocalDir, uploadFileToFTP, uploadFileCMD, and uploadFile.

After the first successful run of the task, $execute{firstRunSuccess} is set to prevent any error messages resulting of files having been moved/removed while rerunning the task until the defined planned time (task => {plannedUntil => "HHMM"}) has been reached.

INIT

The INIT procedure is executed at the EAI::Wrap module initialization (when EAI::Wrap is used in the task script) and loads the site configuration, starts logging and reads commandline options. This means that everything passed to the script via command line may be used in the definitions, especially the task{interactive.*} parameters, here the name and the type of the parameter are not checked by the consistency checks (all other parameters not allowed or having the wrong type would throw an error).

removeFilesinFolderOlderX

remove files on FTP server being older than a time back (given in day/mon/year in remove => {removeFolders => ["",""], day=>, mon=>, year=>1}), see EAI::FTP::removeFilesOlderX

openDBConn ($)