User's Requirement
Update QC information about Execution of Test make outside the product and saved in an external Excel Spreadsheet.
Proposal Solution
Create a TestSet under Unattached folder, link TestCases to it (these must be in TestPlan), create Run and Steps for each Test Instance and update their data taking info from the excel file. All this using an external vbscript.
_________________________________________________________________________
Implementation OUTSIDE the Product
The implementation is an external vbscript (.vbs file) that, using OTA API, will do these operations:
Suppose the data into the excel file are in this order:
Here the code:
'Steps to follow:
'***************************************************************************************************************************
'1. Connection to QC and Project
'2. Creation of an empty TestSet (for the moment only under "Unattached" folder)
'3. Link Tests to the empty TestSet
'4. Create a Loop for each TestInstance of the TestSet
' 4.1 For each TestInstance Create the Run Object
' 4.1.1 From the Run Object create Steps, from StepFactory, that are the same of the Designed Test
' 4.1.2 For each Step update all fields from the designed test, the status and the actual result from the excel sheet
'***************************************************************************************************************************
'***************************************************************************************************************************
' C O N S T A N T S D E F I N I T I O N
'***************************************************************************************************************************
Const QC_ADDRESS = "http://10.10.10.10/qcbin"
Const DOMAIN = "Default"
Const PROJECT = "MY_PROJECT"
Const QCADMINUSR = "qcadmin"
Const QCADMINPWD = "qcpwd"
'Suppose all Tests are under the path below.
'Tests can be also under subfolders of this path
'Tests MUST be NOT directly under Subject Folder
Const PATH_TESTPLAN = "Subject\MyFolder1\MyFolder2"
Const EXCEL_FILE = "C:\temp\myFile.xls"
'***************************************************************************************************************************
'***************************************************************************************************************************
' V A R I A B L E S D E F I N I T I O N
'***************************************************************************************************************************
Dim tdc, objTestSet, objTestInstanceList, objTestInstance
Dim objRun, objStep, objTest
Dim arrPath_TP, idPrevFold, idLastFolder, myComm, RecSet, strAbsPAth
Dim strAllFolderID, strStepData
Dim XLS, wkb, wks
Dim myShell
'***************************************************************************************************************************
set myShell = CreateObject("WScript.Shell")
'***********************************************************************
' 1. Connection to QC and Project
'***********************************************************************
set tdc = createobject("tdapiole80.tdconnection.1")
tdc.InitConnectionEx QC_ADDRESS
tdc.login QCADMINUSR, QCADMINPWD
tdc.Connect DOMAIN, PROJECT
myShell.Popup "I'm connected to the Project " & PROJECT, 2, "QC Project Connection", 64
'***********************************************************************
'**********************************************************************************************
' 2. Creation of an empty TestSet (it will be stored under "Unattached" folder
'**********************************************************************************************
on error resume next
err.clear
set objTestSet = tdc.TestSetFactory.AddItem("TestSet_FakeExecution")
if err.number = 0 then
myShell.Popup "Creation of TestSet under Unattached folder has been done", 2, "QC TestSet Creation", 64
end if
'**********************************************************************************************
'**********************************************************************************************
' 3. Link Test to the Empty TestSet
'**********************************************************************************************
'In this case I need to retrieve all the Tests under the PATH_TESTPLAN.
'To do this I need to do a query to retrieve the AL_ABSOLUTE_PATH of the
'PATH_TESTPLAN and then retrieve all the folder that begin with that path.
'After this I can retrieve all the Tests under those folders.
strAllFolderID = ""
set myComm = tdc.Command
arrPath_TP = split(PATH_TESTPLAN,"\")
'I suppose the 1st element of the array is "Subject" so I analyze from the 2nd
'AL_ITEM_ID of the "Subject" Folder is 2
idPrevFold = 2
for i=1 to ubound(arrPath_TP)
'I do a query to retrieve the correct ID of the i-element folder.
myComm.CommandText = "SELECT AL_ITEM_ID FROM ALL_LISTS " & _
"WHERE " & _
"AL_ITEM_DESCRIPTION = '" & arrPath_TP(i) & "' AND " & _
"AL_FATHER_ID = " & idPrevFold
set RecSet = myComm.Execute
RecSet.First
idPrevFold = RecSet.FieldValue(0)
set RecSet = Nothing
next
idLastFolder = idPrevFold
strAllFolderID = CStr(idLastFolder)
'Now idPrevFold is the correct ID of the last folder of PATH_TESTPLAN and
'from it I can retrieve the AL_ABSOLUTE_PATH
myComm.CommandText = "SELECT AL_ABSOLUTE_PATH FROM ALL_LISTS " & _
"WHERE " & _
"AL_ITEM_ID = " & idLastFolder
set RecSet = myComm.Execute
RecSet.First
strAbsPath = RecSet.FieldValue(0)
set RecSet = Nothing
'Query to retrieve all the Folders and Subfolders under the last folder of PATH_TESTPLAN
myComm.CommandText = "SELECT AL_ITEM_ID FROM ALL_LISTS " & _
"WHERE AL_ABSOLUTE_PATH LIKE '" & strAbsPath & "%'"
set RecSet = myComm.Execute
if RecSet.RecordCount > 0 then
RecSet.First
Do While Not(RecSet.EOR)
strAllFolderID = strAllFolderID & "," & CStr(RecSet.FieldValue(0))
RecSet.Next
Loop
end if
set RecSet = Nothing
'strAllFolderID is a string variable contains all the ID of the Folders
'starting from the last folder of PATH_TESTPLAN constant
'for each element of this array I have to retrieve the list of Tests under it.
'I will do a query again. This time on TEST table.
myComm.CommandText = "SELECT TS_TEST_ID FROM TEST " & _
"WHERE TS_SUBJECT IN (" & strAllFolderID & ")"
set RecSet = myComm.Execute
myShell.Popup "I'm going to link Tests to the TestSet " objTestSet.Name, _
2, "QC Link Tests To TestSet", 64
if RecSet.RecordCount > 0 then
RecSet.First
Do While Not(RecSet.EOR)
'******************************
' Add Test to the TestSet
'******************************
objTestSet.TSTestFactory.AddItem RecSet.FieldValue(0)
Loop
end if
set RecSet = Nothing
'**********************************************************************************************
'**********************************************************************************************
' 4. Create a Loop for each TestInstance of the TestSet
'**********************************************************************************************
'4.0 Open the Excel File
set XLS = CreateObject("Excel.Application")
XLS.Visible = False
set Wkb = XLS.Workbooks.Open(EXCEL_FILE)
set Wks = Wkb.WorkSheets(1)
'Create the List of TestInstance
set objTestInstanceList = objTestSet.TSTestFactory.NewList("")
myShell.Popup "I'm going to create Fake Run and Steps for each TestInstance", _
2, "QC Creation of Fake Executions", 64
'4.1 For each TestInstance Create the Run Object
For each TestInstance in objTestInstanceList
set objTestInstance = objTestSet.TSTestFactory.Item(TestInstance.ID)
'Add the Run Object. Run Name will be
'"Run_Fake_IDOftheTestInstance_yyyymmdd".
' (if month and day are less than 10 it will consist on 1 digit char)
set objRun = objTestInstance.RunFactory.AddItem("Run_Fake_" & objTestInstance.ID & year(date) & month(date) & day(date))
objRun.Field("RN_EXECUTION_DATE") = Date
objRun.Field("RN_EXECUTION_TIME") = Time
'Now I will create for the Run the same number of Steps according to the Design Test
'Retrieve Design Test Informations
set objTest = tdc.TestFactory.Item(objTestInstance.TestID)
'retrieve the DesignStepList
set objDesStepList = objTest.DesignStepFactory.NewList("")
'I create a loop to retrieve DesignStep Informations
for each DesStep in objDesStepList
'Create the Step for the Run object with the same info of the DesignStep
set objStep = objRun.StepFactory.AddItem(DesStep.Name)
objStep.Field("ST_DESCRIPTION") = DesStep.StepDescription
objStep.Field("ST_EXPECTED") = DesStep.StepExpectedResult
objStep.Field("ST_DESSTEP_ID") = DesStep.ID
objStep.Field("ST_TEST_ID") = objTest.ID
objStep.Field("ST_EXECUTION_DATE") = Date
objStep.Field("ST_EXECUTION_TIME") = Time
'Here put the value of Status and Actual from excel
'call a function that return Status and Actual result in a string variable
'I pass the info of TestName, StepName to the function
strStepData = strDataFromExcel(wks, objTest.Name, DeStep.Name)
'strStepData is a string consist of 3 values, Status, ActualResult and Tester, separated from " || " chars.
if len(strStepData) > 0 then
objStep.Status = split(strStepData, " || ")(0)
objStep.Field("ST_ACTUAL") = split(strStepData, " || ")(1)
end if
objStep.Post
objStep.Refresh
set objStep = Nothing
next
set objDesStepList = Nothing
set objTest = Nothing
if len(strStepData) > 0 then
objRun.Field("RN_TESTER_NAME") = split(strStepData, " || ")(2))
end if
objRun.Post
objRun.Refresh
set objRun = Nothing
if len(strStepData) > 0 then
objTestInstance.Field("TC_TESTER_NAME") = split(strStepData, " || ")(2))
end if
objTestInstance.Field("TC_EXEC_DATE") = Date
objTestInstance.Field("TC_EXEC_TIME") = Time
objTestInstance.Post
objTestInstance.Refresh
set objTestInstance = Nothing
Next
'**********************************************************************************************
'Destroy all the objects
set objTestInstanceList = Nothing
set Wks = Nothing
wkb.close
XLS.quit
set wkb = Nothing
set XLS = Nothing
set objTestSet = Nothing
tdc.Disconnect
set tdc = Nothing
myShell.Popup "End Of Program!!!", 2, "QC Fake Execution Creation", 64
set myShell = Nothing
wscript.quit
'**********************************************************************************************
'**********************************************************************************************
' Function to Retrieve Information from Excel Sheet
'**********************************************************************************************
Public Function strDataFromExcel(theSheet, strTestName, strStepName)
Dim Res, bolFound, c, r
Res = ""
bolFound = False
r = 2 'start from 2nd row
'Suppose TestName is under B Column (2nd position) and StepName is under J Column (10th position)
do while Not(bolFound)
if theSheet.Cells(r,2).Value = strTestName And _
theSheet.Cells(r,10).Value = strStepName then
bolFound = True
else
r = r + 1
end if
loop
if bolFound then
'Step is found, retrieve information about Status, Actual Result and also Tester (Responsible Tester)
'Suppose Actual Result is on M Column, Status is on N Column and Tester is on O column
strStatusExcel = wks.Cells(r,14).Value
strActualResult = wks.Cells(r,13).Value
strTesterName = wks.Cells(r,15).Value
Res = strStatusExcel & " || " & strActualResult & " || " & strTesterName
end if
strDataFromExcel = Res
End Function
'**********************************************************************************************
Pag: <
Questo sito è stato realizzato con Jimdo! Registra il tuo sito gratis su https://it.jimdo.com