def LifeCardDownloadingExcel(self):
'''
How it works:
-- Get a list of Testcases already in plan
-- Get a list of the generic Testcases, since some fields will be needed
-- For this second list make "title" indexed dictionary to correlate with
testcases in plan
-- Get the Catalog tree and generate a dictionariy of id -> (Category, SubCategory) labels
-- Generate a list of lists (range or row, cols data in Excel Terms)
-- Each row has the the 11 fields
-- Open Excel, the file and get the sheet
-- Calculate offsets and write the data to the range
-- Save, Close and Quit Excel
'''
wkbook = None
tc_row_offset = 2
tc_col_offset = 1
ticket_row_offset = 2
ticket_col_offset = 4
try:
self.LogAppend('Creating an RPC interface to the server')
rpc = RpcInterface(self.serverurl, self.serverusername, self.serverpassword)
# Get Test Cases In Plan - and Order Them
self.LogAppend('Compiling list of TestCases in Plan from server')
tcips = rpc.listTestCasesExt(self.lifecardcatalog[0], self.lifecardtestplan[0], True)
self.LogAppend('Got %d testcases, processing them' % len(tcips))
tcips = map(TestCaseInPlan, tcips) # build a list of TestCaseInPlan
tcips.sort(key=attrgetter('title')) # sort the list in place using title (our test_id)
# Get Test Cases (for additional needed fields)
self.LogAppend('Compiling list of TestCases from server')
tcases = rpc.listTestCasesExt(self.lifecardcatalog[0], '', True) # Get from server
it_tcases = map(TestCase, tcases) # Create the objects
tcases = dict(map(lambda x: (x.title, x), it_tcases)) # make a title indexed
tcasesb = dict(map(lambda x: (x.page_name, x), it_tcases)) # make a page_name indexed dict
# Get the Test Catalogs and build a dictionary of ids / we only have two levels
self.LogAppend('Start processing catalogs')
testcats = dict()
for catalog in map(TestCatalog, rpc.listSubCatalogsExt(self.lifecardcatalog[0])):
testcats[catalog.id] = (catalog.title, '')
for subcat in map(TestCatalog, rpc.listSubCatalogsExt(catalog.id)):
testcats[subcat.id] = (catalog.title, subcat.title)
self.LogAppend('Creating Excel Range Data')
lrange = list()
# Create a the range input for Excel (list of lists)
for tcip in tcips:
ltcase = list()
tcase = tcases[tcip.title]
ltcase.extend(testcats[tcase.get_catalog_id()]) # category - subcategory
ltcase.extend([tcase.title, tcase.cpeid, tcase.headline])
ltcase.extend([tcip.get_status(), tcip.comment, tcip.tracefile, tcip.testednetwork,
tcip.author, tcip.timestamp.split('T')[0]])
lrange.append(ltcase)
self.LogAppend('Compiling ticket list')
since = datetime.datetime(year=2014, month=1, day=1)
ticket_ids = rpc.getRecentChanges(since)
multicall = rpc.multicall()
self.LogAppend('Getting tickets')
for ticket_id in ticket_ids:
multicall.ticket.get(ticket_id)
tickets = map(Ticket, multicall())
tickets.sort(key=attrgetter('id'))
self.LogAppend('Getting tickets changelog')
multicall = rpc.multicall()
for ticket_id in ticket_ids:
multicall.ticket.changeLog(ticket_id)
# create the ticketchangelog objects from call result
tlogs = multicall()
tlogs = dict(zip(ticket_ids, tlogs))
for ticket_id, clogs in tlogs.iteritems():
tloglist = list()
for clog in clogs:
tloglist.append(TicketChangeLog(clog))
tlogs[ticket_id] = tloglist
tstatusdown = list()
tstatusdown.append('new') if self.lcdownnew else None
tstatusdown.append('open') if self.lcdownopen else None
tstatusdown.append('closed') if self.lcdownclosed else None
tstatusdown.append('fixed') if self.lcdownfixed else None
tstatusdown.append('rejected') if self.lcdownreject else None
tstatusdown.append('investigation') if self.lcdowninvest else None
self.LogAppend('Will download following ticket types: %s' % str(tstatusdown))
self.LogAppend('Generating Excel Data Range for tickets')
ltrange = list()
if self.lcdowncopywithvendorcomments:
ltrange2 = list()
for ticket in tickets:
if ticket.status not in tstatusdown:
self.LogAppend('Skipping ticket %d with status %s' % (ticket.id, ticket.status))
continue
lticket = list()
lticket.extend([ticket.id, ticket.version, ticket.reporter])
# lticket.append('%s\n%s' % (ticket.summary, ticket.description))
lticket.append('%s\n---------------------\n%s' % (ticket.summary, ticket.description))
if ticket.testcaseid in tcasesb:
lticket.append(tcasesb[ticket.testcaseid].title)
else:
lticket.append('Exploratory Testing')
lticket.append(ticket.status.capitalize())
lticket.append(ticket.priority)
# lticket.append('') # skip 'Test Comment Column'
lticket.append(ticket.created)
tlog = tlogs[ticket.id]
owncomment = ''
vencomment = ''
for comment in filter(lambda x: x.name == 'comment', tlog):
# format timestamp author and comment
if not comment.new:
continue
if comment.author == self.lifecardauthorup:
vencomment += '[%s]\n' % (comment.tstamp.strftime('%Y-%m-%dT%H:%M'),)
vencomment += comment.new
vencomment += '\n'
else:
owncomment += '[%s] %s\n' % (comment.tstamp.strftime('%Y-%m-%dT%H:%M'), comment.author)
owncomment += comment.new
owncomment += '\n'
lticket.append(owncomment)
# lticket.extend(['', '', ]) # skip 2 columnts
if not self.lcdownfiltervendorcomments:
lticket.append(vencomment)
else:
lticket.append('')
resoltxt = ''
for resolution in filter(lambda x: x.name == 'resolution', tlog):
if not resolution.new:
continue
resoltxt += '[%s]\n' % (resolution.tstamp.strftime('%Y-%m-%dT%H:%M'),)
resoltxt += resolution.new
resoltxt += '\n'
lticket.append(resoltxt)
ltrange.append(lticket)
if self.lcdowncopywithvendorcomments:
lticket2 = copy.deepcopy(lticket)
lticket2[-2] = vencomment
ltrange2.append(lticket2)
# raise Exception('Ticket List Compiled')
if not lrange and not ltrange:
self.LogAppend('Nothing to download to excel')
else:
if self.lcdownmakecopy:
curdate = datetime.date.today()
prepdate = curdate.strftime('%Y-%m-%d')
appcw = int(curdate.strftime('%W'))
curdate.replace(month=1, day=1)
if curdate.weekday != 0:
# Jan 1st is not monday, so all days until
# first monday are isoweek = 0 and we want
# calendar week 1
appcw += 1
appcw = 'cw%02d' % appcw
dirname, basename = os.path.split(self.lifecardexcel)
excelbase, excelext = os.path.splitext(basename)
basename = prepdate + '-' + excelbase + '-' + appcw + excelext
excelfile = os.path.join(dirname, basename)
self.LogAppend('Copying %s -> %s' % (self.lifecardexcel, excelfile))
shutil.copyfile(self.lifecardexcel, excelfile)
else:
excelfile = self.lifecardexcel
if self.lcdowncopywithvendorcomments:
vendirname, venbasename = os.path.split(excelfile)
venname, venext = os.path.splitext(venbasename)
venname += '-vendor'
venbasename = venname + venext
venexcelfile = os.path.join(vendirname, venbasename)
shutil.copyfile(excelfile, venexcelfile)
exinstances = [(excelfile, ltrange),]
if self.lcdowncopywithvendorcomments:
exinstances.append((venexcelfile, ltrange2))
wkbooks = list()
xls = list()
for excel_file, ticket_range in exinstances:
self.LogAppend('Opening Workbook %s' % excelfile)
pythoncom.CoInitialize()
# xl = Dispatch('Excel.Application')
# xl = win32com.client.gencache.EnsureDispatchEx("Excel.Application")
xl = DispatchEx('Excel.Application') # Opens different instance
xls.append(xl)
xl.Visible = 1
# xl.Interactive = True if self.lcdownkeepexcelopen else False
xl.Interactive = True
wkbook = xl.Workbooks.Open(excel_file)
wkbooks.append(wkbook)
if not lrange:
self.LogAppend('No test cases to write down to Excel')
else:
lempty = [[''] * len(lrange[0])] * len(lrange)
wksheet = wkbook.Sheets('Test Cases')
if False and wksheet.AutoFilterMode:
wksheet.ShowAllData()
topleft = wksheet.Cells(tc_row_offset, tc_col_offset)
botright = wksheet.Cells(tc_row_offset + len(lrange) - 1, tc_col_offset + len(lrange[0]) - 1)
self.LogAppend('Writing Data to Worksheet')
wksheet.Range(topleft, botright).Value = lempty
wksheet.Range(topleft, botright).Value = lrange
self.LogAppend('End downloading test-case-in-plan information')
if not ticket_range:
self.LogAppend('No tickets to write down to Excel')
else:
self.LogAppend('Writing Data to Worksheet')
# FIXME: if there are no tickets ... I would also need to clear the list
# The property .UsedRange should give access to cells that have already
# been used (I can delete the range)
lempty = [['',] * len(ticket_range[0])] * len(tickets)
wksheet = wkbook.Sheets('Bug Tracking')
if False and wksheet.AutoFilterMode:
wksheet.ShowAllData()
topleft = wksheet.Cells(ticket_row_offset, ticket_col_offset)
botright = wksheet.Cells(ticket_row_offset + len(ticket_range) - 1,
ticket_col_offset + len(ticket_range[0]) - 1)
botrightempty = wksheet.Cells(ticket_row_offset + len(tickets) - 1,
ticket_col_offset + len(ticket_range[0]) - 1)
wksheet.Range(topleft, botrightempty).Value = lempty
wksheet.Range(topleft, botright).Value = ticket_range
self.LogAppend('End downloading tickets information to %s' % excel_file)
try:
if not self.lcdownexcelnotsave:
self.LogAppend('Saving workbook')
wkbook.Save()
if not self.lcdownkeepexcelopen:
self.LogAppend('Closing workbook and Quitting Excel')
wkbook.Close(False)
wkbooks.remove(wkbook)
xl.Quit()
xls.remove(xl)
except Exception, e:
self.LogAppend('Error saving/closing/quitting Excel file: %s' % str(e))
self.LogAppend('End downloading to Lifecard')
except Exception, e:
self.LogAppend('Error during Download to LifeCard: %s' % str(e))
