def getworkitemtitle(wrkitemid):
# HTTP Basic Auth with personal access token
client = TFSAPI("TFS URL HERE", project="TFS PROJECT", pat='patid')
# For single Workitem
workitem = client.get_workitem(int(wrkitemid))
# Get all fields
# Case insensitive. Remove space in field name
return workitem['Title']
def getacptnccriteria(wrkitemid):
# HTTP Basic Auth with personal access token
client = TFSAPI("TFS URL HERE", project="TFS PROJECT", pat='patid')
# For single Workitem
workitem = client.get_workitem(int(wrkitemid))
actnccrit = html2text.html2text(
workitem['Microsoft.VSTS.Common.AcceptanceCriteria'])
# Get all fields
# Case insensitive. Remove space in field name
return actnccrit
def getquerydetails():
# HTTP Basic Auth with personal access token
client = TFSAPI("http://tfs2.dell.com:8080/tfs/eDell/",
project="eDellPrograms",
pat='dgi5cehn7jg3rpn5enmovn2xj47thnx77vsg5uyv7wiz227ckpaa')
query = client.run_query('c03cebf1-1bb2-4c8a-9e75-76ba65ef4020')
# Get all found workitems
result = query.result
workitems = result.workItemRelations
a = workitems[0].target
wrkitemlist = []
lstkeys = []
lstvalues = []
openwith = {}
for wrktimid in workitems:
trgt = wrktimid.target
wrkitemlist.append(int(trgt.id))
for b in workitems:
strchanges = []
stre2estrtchanges = []
trgt = b.target
sit_strt = False
e2e_strt = False
ttltimeelapsed = ''
applicatn = ''
# expctdappln =sys.argv[1]
wrkitm = client.get_workitem(int(trgt.id))
wrkitmfields = wrkitm.fields
if ('Dell.SDLC.Application' in wrkitmfields.keys()):
applicatn = wrkitmfields['Dell.SDLC.Application']
if ('OSC' in applicatn):
if (str(wrkitm['System.WorkItemType']) == "Story"
or str(wrkitm['System.WorkItemType']) == "Feature"):
rev = wrkitm.revisions
for re in rev:
fieldacss = re.data
for key, value in fieldacss.items():
if (key == 'fields'):
allfields = fieldacss[key]
if ('Dell.SDLC.Application' in allfields.keys()):
applicatn = fieldacss[key][
'Dell.SDLC.Application']
revstate = fieldacss[key]['System.State']
strchngdby = fieldacss[key]['System.ChangedBy']
# stre2ereqd = fieldacss[key]['Dell.SDLC.QERequired']
if ('OSC' in applicatn):
if (revstate == 'Ready for E2E testing'):
sit_strt = True
changedate = fieldacss[key][
'System.ChangedDate']
if (trgt.id in wrkitemlist):
wrkitemlist.remove(trgt.id)
stre2estrtchanges.append(changedate)
# strchanges.append(strchngdby)
# strchanges.append(stre2ereqd)
if (revstate == 'E2E Testing Started'):
e2e_strt = True
e2echangedate = fieldacss[key][
'System.ChangedDate']
if (trgt.id in wrkitemlist):
wrkitemlist.remove(trgt.id)
strchanges.append(e2echangedate)
strchanges.append(strchngdby)
# strchanges.append(stre2ereqd)
if (sit_strt == True and e2e_strt == True):
e2eprpdate = parser.parse(stre2estrtchanges[0])
e2estrtpdate = parser.parse(strchanges[-2])
ttltimeelapsed = e2estrtpdate - e2eprpdate
openwith[trgt.id] = [
str(trgt.id),
str(ttltimeelapsed.days),
stre2estrtchanges[0], strchanges[-2],
strchanges[-1]
]
for key, value in openwith.items():
print(key, ' --> ', value)
lstkeys.append(key)
lstvalues.append(value)
dfObj = pd.DataFrame(
lstvalues,
columns=['id', 'totaltime', 'readyfor2e', 'e2estarted', 'owner'])
# name_dict = {
# 'Name': lstkeys,
# 'changeby': lstvalues[0][2],
# 'totaltimeelapsed': lstvalues[0]
# }
dfObj.to_csv('OSC.csv')
#!/usr/bin/env python3
from tfs import TFSAPI
user = "******"
password = "******"
# Use DefaultCollection
#client = TFSAPI("https://http://tfs02.corp.tsafe.systems:8080/tfs/Viableware", user=user, password=password)
# Use CustomCollection
client = TFSAPI("https://http://tfs02.corp.tsafe.systems:8080/tfs/Viableware",
project="Software Development",
user=user,
password=password)
# Set path to ProjectName in project parameter
#client = TFSAPI("https://tfs.tfs.ru/tfs/", project="DefaultCollection/ProjectName", user=user, password=password)
workitem = client.get_workitem(12870) # Test connection with Workitem id
print(workitem)
class TfsService:
"""
TFS Service class
"""
def __init__(self, tfs_server, tfs_project='DefaultCollection'):
self.__tfs_server = tfs_server
self.__tfs_project = tfs_project
self.__is_connected = False
self.__tfs_client = None
return
@property
def is_connected(self):
return self.__is_connected
def connect(self, user_name, user_password, connection_test_item_id):
'''Connect to TFS Service function
Using HttpNtlmAuth authication
Parameters:
user_name (str): user name
user_password (str): user password
connection_test_item_id (int): id of test workitem for connection test
Returns:
is_connected (Boolean): True if successfully connected. False owerwise.
'''
self.__tfs_client = TFSAPI(self.__tfs_server, project=self.__tfs_project,
user=user_name, password=user_password, auth_type=HttpNtlmAuth)
try:
wi = self.__tfs_client.get_workitem(int(connection_test_item_id))
self.__is_connected = True if wi else False
except:
self.__is_connected = False
return self.__is_connected
def get_workitem(self, item_id):
'''Get workitem and load properties
Parameters:
item_id (int): workitem id
Returns:
workitem (TfsWorkitem): Workitem with properties. None owerwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
wi = self.__tfs_client.get_workitem(item_id)
if wi:
return TfsWorkitem(wi)
else:
return None
def get_workitems(self, item_ids):
'''Get workitems and load properties
Parameters:
item_ids (list of int): workitem ids list
Returns:
workitems (list of TfsWorkitem): List of workitems with properties. None owerwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if not isinstance(item_ids, list):
raise NameError('item_ids should be list')
ids = [int(id) for id in item_ids]
workitems = self.__tfs_client.get_workitems(ids)
if workitems:
res = [TfsWorkitem(wi) for wi in workitems]
return res
else:
return None
def save_raw_workitem(self, item_id, props):
'''Save workitem with given properties
Parameters:
item_id (int): workitem id
props (dict(str, str)): dictionary of properties
Returns:
result (Boolean): True if saved, False owerwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if not isinstance(props, dict):
raise NameError('props should be dictonary')
wi = self.__tfs_client.get_workitem(int(item_id))
if wi:
for prop_name, prop_value in props.items():
wi[prop_name] = prop_value
return True
else:
return False
def create_workitem(self, workitem_type, required_fields, props=None):
'''Create new tfs workitem with given type
Parameters:
workitem_type (str): workitem type
required_fields (dict(str, str)): Required. Dictonary of setted fields.
props (dict(str, str)): additional dictionary of properties which will be setted after creating item
Returns:
workitem (TfsWorkitem): Workitem with properties. None overwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if (required_fields != None) and (not isinstance(required_fields, dict)):
raise NameError('required_fields should be dictonary')
if (props != None) and (not isinstance(props, dict)):
raise NameError('props should be dictonary')
workitem = self.__tfs_client.create_workitem(workitem_type, fields=required_fields)
if workitem:
wi = TfsWorkitem(workitem)
if props != None:
for prop_name, prop_value in props.items():
wi[prop_name] = prop_value
return wi
else:
return None
def copy_workitem(self,
workitem, with_links_and_attachments=True,
suppress_notifications=True, props=None):
'''Create copy of tfs workitem and set properties
Parameters:
workitem (TfsWorkitem or int): workitem or workitem ID
with_links_and_attachments (Boolean): create copy with links and attachments
suppress_notifications (Boolean): suppress notifications
props (dict(str, str)): dictionary of properties
Returns:
workitem (TfsWorkitem): Copy of given Workitem with properties. None overwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if (props != None) and (not isinstance(props, dict)):
raise NameError('props should be dictonary')
id = workitem.id if workitem is TfsWorkitem else workitem
source_wi = self.__tfs_client.get_workitem(id)
if source_wi:
copy_wi = self.__tfs_client.copy_workitem(source_wi,
with_links_and_attachments=with_links_and_attachments,
suppress_notifications=suppress_notifications)
if copy_wi:
wi = TfsWorkitem(copy_wi)
if props != None:
for prop_name, prop_value in props.items():
wi[prop_name] = prop_value
return wi
else:
return None
else:
raise NameError('Source workitem is None')
def run_query(self, query):
'''Runs tfs query and return list of workitems
Parameters:
query (str): Query string. Named query in folder or query GUID
Returns:
workitem (list of TfsWorkitem): List of workitems. None overwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
query = self.__tfs_client.run_query(query)
if query:
workitems = [TfsWorkitem(wi) for wi in query.workitems] # Lazy generator
return workitems
else:
return None
# uri_params: https://docs.microsoft.com/en-us/rest/api/azure/devops/wit/Wiql/Query%2520By%2520Wiql?view=azure-devops-rest-5.0&viewFallbackFrom=vsts-rest-4.1#uri-parameters
def run_wiql(self, wiql, uri_params=None):
'''Runs tfs wiql and return list of workitems
Parameters:
wiql (str): wiql query string.
uri_params (dict (str, str)): extra URI parameters as a dictionary (only works for parameters that come at the end of the link)
Returns:
workitem (list of TfsWorkitem): List of workitems. None overwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if (uri_params != None) and (not isinstance(uri_params, dict)):
raise NameError('URI params should be dictonary. Search \'Wiql - Query By Wiql\' URI Parameters web page')
query = self.__tfs_client.run_wiql(wiql) if uri_params == None else self.__tfs_client.run_wiql(wiql, params=uri_params)
if query:
workitems = [TfsWorkitem(wi) for wi in query.workitems] # Lazy generator
return workitems
else:
return None
def find_items(self, search_string,
wi_fields = ['Id', 'State', 'Title', 'Description']):
'''
Search items in TFS. Execute WIQL query.
Parameters:
search_string (str): what to search.
wi_fields (list of str): list of string where to search
Returns:
workitem (list of TfsWorkitem): List of workitems. None overwise
'''
if not wi_fields:
raise NameError('Workitem fields is empty')
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
fields_str = ', '.join(['[{}]'.format(field) for field in wi_fields])
conditionals = ('({field} CONTAINS \'{search})\''.format(field=field, search=search_string) for field in wi_fields)
conditional = ' OR '.join(conditionals)
wiql = '''
SELECT {fields}
FROM Worktitems
WHERE {conditional}
ORDER BY [System.Id]
'''.format(fields=fields_str, conditional=conditional)
return self.run_wiql(wiql)
#### https://devopshq.github.io/tfs/advanced.html ####
##### https://docs.microsoft.com/ru-ru/rest/api/azure/devops/wit/?view=azure-devops-rest-5.0 ######
# System.LinkTypes.Hierarchy-Reverse
def add_parent_link(self, source_workitem, dest_workitem):
'''Add parent link from source workitem to destination workitem
Parameters:
source_workitem (TfsWorkitem): Source workitem
dest_workitem (TfsWorkitem): Destination workitem
Returns:
Result (Boolean): True if parent link was added, False overwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if source_workitem.add_parent_link(dest_workitem):
return True
else:
return False
# System.LinkTypes.Hierarchy-Forward
def add_child_link(self, source_workitem, dest_workitem):
'''Add child link from source workitem to destination workitem
Parameters:
source_workitem (TfsWorkitem): Source workitem
dest_workitem (TfsWorkitem): Destination workitem
Returns:
Result (Boolean): True if child link was added, False overwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if source_workitem.add_child_link(dest_workitem):
return True
else:
return False
# Microsoft.VSTS.Common.Affects-Forward
def add_affect_link(self, source_workitem, dest_workitem):
'''Add affects link from source workitem to destination workitem
Parameters:
source_workitem (TfsWorkitem): Source workitem
dest_workitem (TfsWorkitem): Destination workitem
Returns:
Result (Boolean): True if affects link was added, False overwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if source_workitem.add_affect_link(dest_workitem):
return True
else:
return False
# Microsoft.VSTS.Common.Affects-Reverse
def add_affected_by_link(self, source_workitem, dest_workitem):
'''Add affected by link from source workitem to destination workitem
Parameters:
source_workitem (TfsWorkitem): Source workitem
dest_workitem (TfsWorkitem): Destination workitem
Returns:
Result (Boolean): True if affected by link was added, False overwise
'''
if not self.__is_connected:
raise NameError('Disconnected from TFS Service')
if source_workitem.add_affected_by_link(dest_workitem):
return True
else:
return False
class TfsIntegration:
def __init__(self, tfs_url, project, user, password):
self.project = project
self.client = TFSAPI(tfs_url,
project=project,
user=user,
password=password,
auth_type=HttpNtlmAuth)
# connect_timeout=10, read_timeout=30
def get_workitem(self, workitem_id):
return self.client.get_workitem(workitem_id)
def get_workitems(self):
# NOTE: Fields in SELECT really ignored, wiql return Work Items with all fields
query = """SELECT
[System.Id],
[System.WorkItemType],
[System.Title],
[System.ChangedDate]
FROM workitems
WHERE
[System.AreaPath] = '{}'
ORDER BY [System.Title]""".format(self.project)
# ORDER BY [System.ChangedDate]""".format(self.project)
wiql = self.client.run_wiql(query)
# Get founded Work Item ids
# ids = wiql.workitem_ids
return wiql.workitems
def get_testcases(self):
query = """SELECT
[System.Id],
[System.WorkItemType],
[System.Title],
[System.ChangedDate]
FROM workitems
WHERE
[System.WorkItemType] = 'Test Case' AND
[System.AreaPath] = '{}'
ORDER BY [System.Title]""".format(self.project)
wiql = self.client.run_wiql(query)
return wiql.workitems
def get_testcases_from_testsuite(self, test_suite_id):
test_suite = self.get_workitem(test_suite_id)
assert test_suite['WorkItemType'] == 'User Story'
testcases = []
for relation in test_suite.data['relations']:
if relation['rel'] == 'Microsoft.VSTS.Common.TestedBy-Forward':
workitem_id = self.__get_workitem_id_from_url(relation['url'])
testcases.append(self.get_workitem(workitem_id))
# Order by title
testcases = sorted(testcases, key=lambda testcase: testcase['Title'])
return testcases
def get_work_item_by_title(self, title):
query = """SELECT
[System.Id],
[System.WorkItemType],
[System.Title],
[System.ChangedDate]
FROM workitems
WHERE
[System.AreaPath] = '{}' AND
[System.Title] = '{}'""".format(self.project, title)
workitems = self.client.run_wiql(query).workitems
assert len(workitems) <= 1
if len(workitems) == 0:
return None
return workitems[0]
@staticmethod
def __get_workitem_id_from_url(url):
# Example:
# 'url':'https://tfs.e-unicred.com.br/Unicred/70711659-4c4f-4ac0-965c-ae87fbae6d49/_apis/wit/workItems/45327'
workitem_id = int(url.split('/')[-1])
return workitem_id
def get_testcases_from_current_sprint(self):
query = """SELECT
[System.Id],
[System.WorkItemType],
[System.Title],
[System.ChangedDate]
FROM workitems
WHERE
[System.WorkItemType] = 'Test Case' AND
[System.AreaPath] = '{}' AND
[System.IterationPath] = @CurrentIteration
ORDER BY [System.Title]""".format(self.project)
wiql = self.client.run_wiql(query)
return wiql.workitems
def get_testsuites(self):
query = """
SELECT
*
FROM
workitems
WHERE
[System.WorkItemType] = 'User Story' AND
[System.AreaPath] = '{}' AND
[Related Link Count] >= 1
ORDER BY
[System.Title]""".format(self.project)
wiql = self.client.run_wiql(query)
workitems = wiql.workitems
# Filter for relations.rel = Microsoft.VSTS.Common.TestedBy-Forward
filtered = set()
for workitem in workitems:
for relation in workitem.data['relations']:
if relation['rel'] == 'Microsoft.VSTS.Common.TestedBy-Forward':
filtered.add(workitem)
return list(filtered)