Included page: .FrontPage.QA.Extensions.SAT.SetUp (edit)

import

TBOT.Generic.Database

ddt: SetUp
key	value
ConnectionString	Server=10.184.25.13,1605;database=AUTOTESTING;User Id=automation; password=automation*100
BytesColumn	DataObject
FileExtension	ZIP
ExpectedDirectory	D:\Installations\FitNesseRoot\files\Extensions\Baseline
DownloadPath	D:\Installations\FitNesseRoot\files\Extensions\Actuals
SourceDirectory	D:\Installations\FitNesseRoot\files\Extensions\Actuals
TargetDirectory	\\C605ZPUOTTFS2.tztst.loc\D_Drive\Trustez\ftp\LocalUser\EFTPS\qa\in
CompareResultDir	D:\Installations\FitNesseRoot\files\Extensions\Result
ignorePathValidations	false
DeleteFile	true

variable defined: PAN=5010
variable defined: TaxYear=2017
variable defined: SettlementDate=08/24/2017

Import Necessary Libraries

import

TBOT.Business

TBOT.Generic.Database

Execute BTMDB..Package

ddt: DynamicQuery	SELECT LTRIM(RTRIM(Server)) AS Server,LTRIM(RTRIM(DB_NAME)) as DB_NAME ,AcctNo,'{{TaxYear}}' AS Tax_Year ,CONVERT(varchar(10),getdate(),111) as ActionDate from BTMDB..Package where AcctNo='{{PAN}}'
Comments#	PAN	TaxYear	DB_NAME?	AcctNo?	Tax_Year?	ActionDate?	Server?
Test0001	5010	2017	$DBName1=	$AcctNo1=	$Tax_Year1=	$Action_DT1=	$Server1=

Download Bankdata record

ddt: KeywordFixture	use {{DB_NAME}} select Action_Cd,DataObject,CONVERT(varchar(10),Action_DT,111) as Action_DT from bankdata where acctno='{{PAN}}' and year={{Year}} and state='' and prodopt=27 and Product_Cd='N'
Server	DB_NAME	PAN	Year	FileName	DOWNLOAD?	UNZIP?	GETFILES?	Action_Cd?	Action_DT?	DataObject?
$Server1	$DBName1	$AcctNo1	$Tax_Year1	5010_2017_EFTPS_Extension_Payment_1.zip	True	True	$EDIFile1=	1	$Action_DT1	System.Byte[]

Generate EFTPSAck

ddt: BusinessKeyword
EDIPath	AckDirPath	EFTPSACK?
$EDIFile1		True

Move File to execute EFTPS-IN

ddt: KeywordFixture
sourcefilename	EXTN	FILECOPYNEW?
5010_2017_EFTPS_Extension_Payment_1	EDI	True

!*****> Assert EFTPS Filing

Validate Filing Assertions

ddt: DynamicQuery	use {{DataBaseName}} select Action_Cd,DataObject,CONVERT(varchar(10),Action_DT,111) as Action_DT from bankdata where acctno='5010' and year=2017 and state='' and prodopt=27 and SeqNo=1 and Product_CD='N'
Comments#	DataBaseName	PAN	TaxYear	SeqNumber	Action_Cd?	Action_DT?	DataObject?
Test0001	$DBName1	$AcctNo1	$Tax_Year1	$Seq_Number1	1	$Action_DT1	System.Byte[]

Checking Filing Status

ddt:DynamicQuery	WAITFOR DELAY '00:00:10'; WAITFOR DELAY '00:00:10'; DECLARE @AssetID NVARCHAR(20) IF OBJECT_ID('tempdb..#tempPrepareExtensions') IS NOT NULL DROP TABLE #tempPrepareExtensions CREATE TABLE #tempPrepareExtensions ( Filing VARCHAR(200), StateCode VARCHAR(3), ProdOpt SMALLINT, Product_Cd CHAR(1), IsRowEnabled BIT, IsEstimatedEnabled BIT, SettlementDate DateTime, IsSettlementDateEnabled BIT, PrepareStatus VARCHAR(100), IsPrepareStatusEnabled BIT, PreparedDate DateTime, IsPreparedDateEnabled BIT, PreparedBy Varchar(3), IsPreparedByEnabled BIT, IsDoFileEnabled BIT, IsFiled BIT, IsFiledEnabled BIT, IsFiledVisible BIT, FiledDate DateTime, IsFiledDateEnabled BIT, FiledBy VARCHAR(3), IsFiledByEnabled BIT, FiledStatus VARCHAR(100), IsFiledStausEnabled BIT, ID INT, SeqNo SMALLINT, PreparedSettlementDate datetime, BatchOpt SMALLINT, Comments VARCHAR(255), ActionCode TINYINT ) select @AssetID=AssetID from BANKDB25.dbo.bankdata where acctno = '{{PAN}} 'and year = '{{YEAR}} 'and prodopt = '27' and Product_Cd='N' insert into #tempPrepareExtensions EXEC bankdb25.BANKDB_P.spGetExtensionFilingData '5010',2017,1 select CASE WHEN (CONVERT(VARCHAR(10),FiledDate,101) =CONVERT(VARCHAR(10),getDate(),101) AND IsFiled=1 AND FiledBy is not null) THEN 'Complete' ELSE 'Not Complete' END As FilingStatus, REPLACE(CONVERT(VARCHAR(15),FiledDate,12),':','') as FiledDateRegEx, REPLACE(CONVERT(VARCHAR(5),FiledDate,108),':','') as FiledDateTimeRegEx, CONVERT(nvarchar(6),PreparedDate,12) AS PreparedDateDateRegEx, CONVERT(nvarchar(15),SettlementDate,12) AS SettlementDateRegEx, @AssetID as AssetID from #tempPrepareExtensions where Filing like 'Federal%' IF OBJECT_ID('tempdb..#tempPrepareExtensions') IS NOT NULL DROP TABLE #tempPrepareExtensions
PAN	YEAR	FilingType	FilingStatus?	FiledDateRegEx?	FiledDateTimeRegEx?	PreparedDateDateRegEx?	SettlementDateRegEx?	AssetID?
5010	2017	Federal EFTPS (1041)	Complete	$FiledDateRegEx1=	$FiledDateTimeRegEx1=	$PreparedDateDateRegEx1=	$SettlementDateRegEx1=	$AssetID1=

File Compare for EDI file

File Compare Block

TBOT.Generic.SFTP
Comments	sourceFile	destinationFile	destinationFullFilePath	resultFileName	IgnorePattern	compareFiles?
Test0001	5010_2017_Extensions.edi	$EDIFile1	Yes		$AssetID1;555690944;170821;170817;0832;ST~813~\d\\;SE~\d~\d*\\;$FiledDateRegEx1;$FiledDateTimeRegEx1;$PreparedDateDateRegEx1;$SettlementDateRegEx1	Both Files are Equal