Requisito utente
Aggiornare Quality Center per quanto concerne l'inserimento di Esecuzioni di Test effettuate esternamente al prodotto e registrate in un File Excel.
Soluzione Proposta
Tramite l'utilizzo di uno script esterno creare strategia sotto folder Unattached, inserire i casi di test (questi devono essere presenti in test plan), creare Run e Steps per ciascuna istanza di test e aggiornare i dati prendendo le info da file excel.
__________________________________________________________________________
Implementazione FUORI dal Prodotto
L'implementazione prevede la creazione di un vbscript (file .vbs) che, tramite l'utilizzo delle OTA API, effettui le operazioni di:
Suppongo che il foglio excel abbia i seguenti dati sotto le relative posizioni:
Nell'esempio i commenti all'interno del codice sono scritti in lingua Inglese:
'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
'**********************************************************************************************