def get_Stories(jira, filter_id='', FRs=[], add_clause='', WBStag = 'WBS'):
if len(filter_id) > 0:
jql = jira.filter(filter_id).jql
else:
jql = '"Feature Link" in (' + ','.join(str(key) for key in FRs) + ')'
if len(add_clause) > 0:
jql = jql + ' AND ' + add_clause
raw_Stories = jira.search_issues(jql,maxResults=1000)
Stories_keys = [[get_story_parent(x, jira) for x in raw_Stories],
[x.key for x in raw_Stories]]
Stories_list = list(map(lambda x:
[', '.join([parse_sprints(sprint) for sprint in cmn.nvl(getattr(x.fields,jira.sprintfield),[])]),
x.fields.summary,
x.fields.duedate,
', '.join([str(label) for label in cmn.nvl(x.fields.labels,[])]),
', '.join([str(fixVersion.name) for fixVersion in cmn.nvl(x.fields.fixVersions,[])]),
cmn.nvl(x.fields.aggregatetimeestimate,0)/3600,
cmn.nvl(x.fields.timeoriginalestimate,0)/3600,
cmn.nvl(x.fields.timeestimate,0)/3600,
cmn.nvl(x.fields.aggregatetimeoriginalestimate,0)/3600,
get_plan(x, WBStag=WBStag),
cmn.nvl(x.fields.aggregatetimespent,0)/3600,
cmn.nvl(x.fields.timespent,0)/3600,
', '.join([str(subtask) for subtask in cmn.nvl(x.fields.subtasks,[])]),
x.fields.status.name,
get_status_mapped(x),
get_assignee(x.fields.assignee),
getTeam(x)],
raw_Stories))
Stories_df = pd.DataFrame(Stories_list,
index=Stories_keys,
columns=[ 'Sprint',
'Summary',
'Due Date',
'Labels',
'Fix Version/s',
'Σ Remaining Estimate',
'Original Estimate',
'Remaining Estimate',
'Σ Original Estimate',
'WBS Dev LOE',
'Σ Time Spent',
'Time Spent',
'Sub-Tasks',
'Status',
'Status Mapped',
'Assignee',
'Team'])
return Stories_df
def alignComponents(jql, jira, update, add_clause=""):
stories = jira.search_issues(jql,maxResults=1000)
print('Loaded {} stories'.format(len(stories)))
stories_keys = list([x.key for x in stories])
stories_list = list(map(lambda x:
[parse_components(x.fields.components)],
stories))
stories_df = pd.DataFrame(stories_list,
index=stories_keys,
columns=['components'])
subtasks_jql = 'parent in (' + ','.join(str(key) for key in stories_keys) + ') and created > -10d'
if add_clause != "":
subtasks_jql = subtasks_jql + ' AND ' + add_clause
subtasks = jira.search_issues(subtasks_jql,maxResults=1000)
print('Loaded {} subtasks'.format(len(subtasks)))
subtasks_keys = list([x.key for x in subtasks])
subtasks_list = list(map(lambda x:
[x.fields.parent.key,
', '.join([jc.parse_sprints(sprint) for sprint in cmn.nvl(getattr(x.fields,jira.sprintfield),[])]),
x.fields.status.name,
parse_components(x.fields.components)],
subtasks))
subtasks_df = pd.DataFrame(subtasks_list,
index=subtasks_keys,
columns=['parent','sprints','status','components'])
subtasks_df = subtasks_df.join(stories_df,on='parent',rsuffix='_story')
subtasks_df['diff'] = subtasks_df.apply(lambda row: row.components^row.components_story,axis=1)
subtasks_df['cmn'] = subtasks_df.apply(lambda row: row.components&row.components_story,axis=1)
print('KEY STATUS SUBTASK STORY DIFF CMN')
for (key,subtask) in subtasks_df.iterrows():
if subtask.sprints != "" and subtask.status != 'Closed':
if len(subtask['diff']) > 0 and len(subtask['cmn']) == 0:
print('{} {} {} {} {} {}'.format(key,
str(subtask.status),
str(subtask.components),
str(subtask.components_story),
str(subtask['diff']),
str(subtask['cmn'])))
if update and len(subtask.components) == 0:
if len(subtask['diff']) > 1:
print('Check {} multiple component {}'.format(key,str(subtask['diff'])))
else:
issue = jira.issue(key)
issue.update(notify=False,
fields={"components": [{'name': component} for component in subtask['diff']]})
print('Updated {} set component {}'.format(key,str(subtask['diff'])))
def detailedSprintReport(jira, parsedBurndown, Burndown, getdailyrep):
now = Burndown['now']
startTime = Burndown['startTime']
endTime = Burndown['endTime']
if 'completeTime' in Burndown:
completeTime = Burndown['completeTime']
else:
completeTime = None
sprintScope = parsedBurndown.query('added == True')
if len(sprintScope) == 0:
return None
jql = 'issue in (' + ','.join(
str(key) for key in sprintScope.index.get_level_values(2)) + ')'
sprintIssues = jira.search_issues(jql, maxResults=1000)
print('Preparing detailed report...')
sprintDetailedReport = sprintScope.apply(lambda x: issueDetails(
x, parsedBurndown, sprintIssues, now, startTime,
cmn.nvl(completeTime, endTime), jira, getdailyrep),
axis=1)
sprintDetailedReport = sprintDetailedReport.query(
'completed != completed or completed > added')
sprintDetailedReport['notParent'] = sprintDetailedReport.apply(
lambda row: row.name[1] != row.name[2], axis=1)
sprintDetailedReport[
'Parent'] = sprintDetailedReport.index.get_level_values(1)
sprintDetailedReport['Key'] = sprintDetailedReport.index.get_level_values(
2)
sprintDetailedReport = sprintDetailedReport.sort_values(
['Parent', 'notParent', 'Key'])
#sprintDetailedReport.to_excel('C:\\Users\\rudu0916\\Documents\\SprintReport_IPAM_Sp8.xlsx')
#Initial scope
#initialScope = parsedBurndown.query('initialScope == True')
#initialScope = initialScope.join(initialScope.apply(lambda x: getInitialEstimate(parsedBurndown, x.name[2], startTime),axis=1))
#initialScope = initialScope.join(initialScope.apply(lambda x: getTimeSpent(parsedBurndown, x.name[2], startTime, cmn.nvl(completeTime,endTime)),axis=1))
#Additional scope
#additionalScope = parsedBurndown.query('additionalScope == True')
#additionalScope = additionalScope.join(additionalScope.apply(lambda x: getInitialEstimate(parsedBurndown, x.name[2],startTime),axis=1))
#additionalScope = additionalScope.join(additionalScope.apply(lambda x: getTimeSpent(parsedBurndown, x.name[2], x.name[0], cmn.nvl(completeTime,endTime)),axis=1))
#Descoped
#descoped = parsedBurndown.query('descope == True')
#descoped = descoped.join(descoped.apply(lambda x: getInitialEstimate(parsedBurndown, x.name[2], startTime),axis=1))
#descoped = descoped.join(additionalScope.apply(lambda x: getTimeSpent(parsedBurndown, x.name[2], startTime, cmn.nvl(completeTime,endTime)),axis=1))
return sprintDetailedReport
def get_subtasks(jira, parents):
jql = 'parent in (' + ','.join(str(key) for key in parents.index.levels[1]) + ')'
raw_subtasks = jira.search_issues(jql,maxResults=1000)
subtasks_keys = [[parents[parents.index.get_level_values(1) == x.fields.parent.key].iloc[0].name[0] for x in raw_subtasks],
[x.fields.parent.key for x in raw_subtasks],
[x.key for x in raw_subtasks]]
subtasks_list = list(map(lambda x:
[', '.join([parse_sprints(sprint) for sprint in cmn.nvl(getattr(x.fields,jira.sprintfield),[])]),
', '.join([str(component.name) for component in cmn.nvl(x.fields.components,[])]),
x.fields.summary,
x.fields.duedate,
', '.join([str(label) for label in cmn.nvl(x.fields.labels,[])]),
get_step(x),
', '.join([str(fixVersion.name) for fixVersion in cmn.nvl(x.fields.fixVersions,[])]),
cmn.nvl(x.fields.timeoriginalestimate,0)/3600,
cmn.nvl(x.fields.timeestimate,0)/3600,
cmn.nvl(x.fields.timespent,0)/3600,
x.fields.status.name,
get_priority(x),
get_status_mapped(x),
get_assignee(x.fields.assignee)],
raw_subtasks))
subtasks_df = pd.DataFrame(subtasks_list,
index = subtasks_keys,
columns=['Sprint',
'Components',
'Summary',
'Due Date',
'Labels',
'Step',
'Fix Version/s',
'Original Estimate',
'Remaining Estimate',
'Time Spent',
'Status',
'Priority',
'Status Mapped',
'Assignee'])
return subtasks_df
def allWBStoHLEstimate(jira, WBS, force=False):
for x in WBS.iterrows():
if str(x[0]) == 'nan':
print('skipped - no FR')
else:
print(x[0])
try:
issue = jira.issue(x[0])
total_jira = round(cmn.nvl(issue.fields.customfield_23121, 0),
1)
analysis_jira = round(
cmn.nvl(issue.fields.customfield_23422, 0), 1)
design_jira = round(cmn.nvl(issue.fields.customfield_23423, 0),
1)
build_jira = round(cmn.nvl(issue.fields.customfield_23424, 0),
1)
bugfix_jira = round(cmn.nvl(issue.fields.customfield_23425, 0),
1)
qa_jira = round(cmn.nvl(issue.fields.customfield_23426, 0), 1)
if abs(total_jira - round(x[1]['Tech LOE wbs'], 1)) > 0.2:
print('Different estimations exist:')
print('HL Estimate, md ' + str(total_jira) + ' ' +
str(round(x[1]['Tech LOE wbs'], 1)))
print('Analysis LOE, md ' + str(analysis_jira) +
' ' + str(round(x[1]['Analysis LOE wbs'], 1)))
print('Design LOE, md ' + str(design_jira) + ' ' +
str(round(x[1]['Design LOE wbs'], 1)))
print('Build LOE, md ' + str(build_jira) + ' ' +
str(round(x[1]['Build LOE wbs'], 1)))
print('Bug Fixing LOE, md ' + str(bugfix_jira) + ' ' +
str(round(x[1]['Bug Fixing LOE wbs'], 1)))
print('QA LOE, md ' + str(qa_jira) + ' ' +
str(round(x[1]['QA LOE wbs'], 1)))
if force:
WBStoHLEstimate(issue, x)
print('Updated with force')
elif (total_jira == 0) and (x[1]['Tech LOE wbs'] != 0):
WBStoHLEstimate(issue, x)
print('Updated')
else:
print('Already filled')
except Exception as e:
print(e)
continue
return
def get_plan(issue, WBSitem = 'HL Estimate, md', WBStag = 'WBS'):
if cmn.nvl(WBStag,'') == '':
return None
dsc = issue.fields.description
if dsc is None:
return None
m = re.search('#' + WBStag + '(\{.*\})',dsc)
if m is None:
return None
WBSdata = eval(m.group(1).replace('nan','0.0'))
#'Dev LOE' is legacy, remove later
if WBSitem in WBSdata:
return WBSdata[WBSitem] * 8
elif 'Dev LOE' in WBSdata:
return WBSdata['Dev LOE'] * 8
else:
return None
def get_WorkItems(jira, jql=''):
raw_WorkItems = jira.search_issues(jql,maxResults=1000)
WorkItems_keys = [[getattr(x.fields,jira.epiclinkfield) for x in raw_WorkItems],
[x.key for x in raw_WorkItems]]
WorkItems_list = list(map(lambda x:
[', '.join([parse_sprints(sprint) for sprint in cmn.nvl(getattr(x.fields,jira.sprintfield),[])]),
', '.join([str(component.name) for component in cmn.nvl(x.fields.components,[])]),
x.fields.summary,
x.fields.duedate,
', '.join([str(label) for label in cmn.nvl(x.fields.labels,[])]),
', '.join([str(fixVersion.name) for fixVersion in cmn.nvl(x.fields.fixVersions,[])]),
cmn.nvl(x.fields.aggregatetimeestimate,0)/3600,
cmn.nvl(x.fields.timeoriginalestimate,0)/3600,
cmn.nvl(x.fields.timeestimate,0)/3600,
cmn.nvl(x.fields.aggregatetimeoriginalestimate,0)/3600,
cmn.nvl(x.fields.aggregatetimespent,0)/3600,
cmn.nvl(x.fields.timespent,0)/3600,
', '.join([str(subtask) for subtask in cmn.nvl(x.fields.subtasks,[])]),
x.fields.status.name,
x.fields.priority.name,
get_status_mapped(x),
get_assignee(x.fields.assignee)],
raw_WorkItems))
WorkItems_df = pd.DataFrame(WorkItems_list,
index=WorkItems_keys,
columns=[ 'Sprint',
'Components',
'Summary',
'Due Date',
'Labels',
'Fix Version/s',
'Σ Remaining Estimate',
'Original Estimate',
'Remaining Estimate',
'Σ Original Estimate',
'Σ Time Spent',
'Time Spent',
'Sub-Tasks',
'Status',
'Priority',
'Status Mapped',
'Assignee'])
return WorkItems_df
def get_FRs(jira, filter_id, WBStag = 'WBS'):
raw_FRs = jira.search_issues(jira.filter(filter_id).jql,maxResults=1000)
FRs_keys = list([x.key for x in raw_FRs])
FRs_list = list(map(lambda x:
[cmn.nvl(get_plan(x, WBStag=WBStag, WBSitem='HL Estimate, md'), cmn.nvl(x.fields.customfield_23121,0)),
', '.join([parse_sprints(sprint) for sprint in cmn.nvl(getattr(x.fields,jira.sprintfield),[])]),
cmn.nvl(get_plan(x, WBStag=WBStag, WBSitem='Analysis LOE, md'), cmn.nvl(x.fields.customfield_23422,0)),
cmn.nvl(get_plan(x, WBStag=WBStag, WBSitem='Build LOE, md'), cmn.nvl(x.fields.customfield_23424,0)),
cmn.nvl(get_plan(x, WBStag=WBStag, WBSitem='Design LOE, md'), cmn.nvl(x.fields.customfield_23423,0)),
cmn.nvl(get_plan(x, WBStag=WBStag, WBSitem='QA LOE, md'), cmn.nvl(x.fields.customfield_23426,0)),
cmn.nvl(get_plan(x, WBStag=WBStag, WBSitem='Bug Fixing LOE, md'), cmn.nvl(x.fields.customfield_23425,0)),
x.fields.summary,
getCustomers(x),
x.fields.duedate,
', '.join([str(label) for label in cmn.nvl(x.fields.labels,[])]),
', '.join([str(fixVersion.name) for fixVersion in cmn.nvl(x.fields.fixVersions,[])]),
x.fields.status.name,
get_status_mapped(x),
', '.join(getFRTeams(x))],
raw_FRs))
FRs_df = pd.DataFrame(FRs_list,
index=FRs_keys,
columns=['HL Estimate, md',
'Sprint',
'Analysis LOE, md',
'Build LOE, md',
'Design LOE, md',
'QA LOE, md',
'Bug Fixing LOE, md',
'Summary',
'Customers',
'Due Date',
'Labels',
'Fix Version/s',
'Status',
'Status Mapped',
'Teams'])
return FRs_df
def getCustomers(issue):
try:
customers = ', '.join([str(customer.value) for customer in cmn.nvl(issue.fields.customfield_21820,[])])
except AttributeError:
customers = ''
return customers
def issueDetails(issue, parsedBurndown, sprintIssues, now, startTime, endTime,
jira, getdailyrep):
parent = issue.name[1]
key = issue.name[2]
initialScope = issue['initialScope']
additionalScope = issue['additionalScope']
decsopeFilter = parsedBurndown.query('key == "' + key +
'" and added == False')
#TODO doesn't account for case when issue removed from sprint and added again during sprint
if len(decsopeFilter) > 0:
descope = True
descoped = decsopeFilter.iloc[0].name[0]
descoped_str = decsopeFilter.iloc[0]['timestamp_str']
else:
descope = ''
descoped = None
descoped_str = ''
added = issue.name[0]
added_str = issue['timestamp_str']
completed = getCompletedDate(parsedBurndown, key, endTime)
if completed is None:
completed_str = ''
remainingEstimate = getRemainingEstimate(
parsedBurndown, key, min(now, endTime, cmn.nvl(descoped, endTime)))
else:
completed_str = parseBurndownTimestamp(completed).strftime(
'%Y.%m.%d %H:%M')
remainingEstimate = 0
initialEstimate = getInitialEstimate(parsedBurndown, key, startTime)
timeSpent = getTimeSpent(parsedBurndown, key, startTime,
cmn.nvl(descoped, endTime))
jiraIssue = [x for x in sprintIssues if x.key == key][0]
Summary = jiraIssue.fields.summary
Status = jiraIssue.fields.status.name
Status_mapped = jc.statuses_mapped.loc[
jiraIssue.fields.status.name]['Type']
assignee = jc.get_assignee(jiraIssue.fields.assignee)
if getdailyrep:
dailyreport = dr.dailyreport(jira, jiraIssue)
else:
dailyreport = ""
return pd.Series({
'parent': parent,
'key': key,
'initialScope': initialScope,
'additionalScope': additionalScope,
'descope': descope,
'added': added,
'added_str': added_str,
'descoped': descoped_str,
'completed': completed,
'completed_str': completed_str,
'initialEstimate': initialEstimate,
'timeSpent': timeSpent,
'remainingEstimate': remainingEstimate,
'Summary': Summary,
'Status': Status,
'Assignee': assignee,
'Status_mapped': Status_mapped,
'dailyreport': dailyreport
})
def parseBurndown(Burndown):
columns = [
'timestamp_str', 'timeSpent', 'oldEstimate', 'newEstimate',
'deltaEstimate', 'changeDate', 'changeDate_str', 'notDone', 'done',
'newStatus', 'added', 'initialScope', 'additionalScope', 'descope'
]
data = []
index1 = []
index2 = []
index3 = []
startTime = Burndown['startTime']
endTime = Burndown['endTime']
if 'completeTime' in Burndown:
completeTime = Burndown['completeTime']
else:
completeTime = None
for timestamp, changes in Burndown['changes'].items():
timestamp_str = parseBurndownTimestamp(timestamp).strftime(
'%Y.%m.%d %H:%M')
timestamp = int(timestamp)
for change in changes:
key = change['key']
notDone = ''
done = ''
newStatus = ''
timeSpent = 0
oldEstimate = None
newEstimate = None
deltaEstimate = 0
changeDate = None
changeDate_str = ''
added = ''
initialScope = ''
additionalScope = ''
descope = ''
if key in Burndown['issueToParentKeys'].values():
index2 = index2 + [key]
elif key in Burndown['issueToParentKeys'].keys():
index2 = index2 + [Burndown['issueToParentKeys'][key]]
else:
index2 = index2 + ['']
if 'column' in change:
column = change['column']
if 'notDone' in column:
notDone = column['notDone']
if 'done' in column:
done = column['done']
if 'newStatus' in column:
newStatus = column['newStatus']
if 'timeC' in change:
timeC = change['timeC']
if 'timeSpent' in timeC:
timeSpent = timeC['timeSpent'] / 3600
if 'oldEstimate' in timeC:
oldEstimate = timeC['oldEstimate'] / 3600
else:
oldEstimate = 0
if 'newEstimate' in timeC:
newEstimate = timeC['newEstimate'] / 3600
else:
newEstimate = 0
deltaEstimate = newEstimate - oldEstimate
if 'changeDate' in timeC:
changeDate = timeC['changeDate']
changeDate_str = parseBurndownTimestamp(
timeC['changeDate']).strftime('%Y.%m.%d %H:%M')
changeDate = int(changeDate)
if 'added' in change:
added = change['added']
if added:
if timestamp <= startTime:
initialScope = True
else:
additionalScope = True
else:
if (timestamp > startTime) and (timestamp < cmn.nvl(
completeTime, endTime)):
descope = True
elif timestamp <= startTime:
raise ValueError('Added is False before sprint start')
data = data + [[
timestamp_str, timeSpent, oldEstimate, newEstimate,
deltaEstimate, changeDate, changeDate_str, notDone, done,
newStatus, added, initialScope, additionalScope, descope
]]
index1 = index1 + [timestamp]
index3 = index3 + [key]
res = pd.DataFrame(data, index=[index1, index2, index3], columns=columns)
res.index.levels[0].name = 'timestamp'
res.index.levels[1].name = 'parent'
res.index.levels[2].name = 'key'
return res
def my_program(i_args):
l_myArgs = i_args
# parse the argments
l_exclude = ""
l_mysqlCNF = ""
l_init = ""
l_zip = ""
l_user = ""
l_pwd = ""
l_database = ""
l_repository = ""
l_expired = ""
l_mode = ""
opts, args = getopt.getopt(l_myArgs, '-i-h-t-v-z-u:-p:-d:-r:-e:-m:', [
'init', 'help', 'trace', 'version', 'zip=', 'user='******'password='******'directory=', 'repository=', 'expired=', 'mode='
])
try:
for opt_name, opt_value in opts:
if opt_name in ('-i', '--zip'):
l_init = 'yes'
continue
if opt_name in ('-h', '--help'):
Usage()
exit(0)
if opt_name in ('-t', '--trace'):
print('-t')
import pdb
pdb.set_trace()
continue
if opt_name in ('-v', '--version'):
print("Version is 0.01 ")
exit(0)
if opt_name in ('-z', '--zip'):
l_zip = opt_value
continue
if opt_name in ('-u', '--user'):
l_user = opt_value
continue
if opt_name in ('-p', '--password'):
l_pwd = opt_value
continue
if opt_name in ('-d', '--database'):
l_database = opt_value
continue
if opt_name in ('-r', '--repository'):
l_repository = opt_value
continue
if opt_name in ('-e', '--expired'):
l_expired = opt_value
continue
if opt_name in ('-m', '--mode'):
l_mode = opt_value
continue
except GetoptError as opt:
print('unsupported parameter')
except Exception as e:
print("unknowned error (%s)" % e)
l_init = common.nvl(l_init, 'n')
l_zip = common.nvl(l_zip, cfg.ZIP_BACKUP)
l_user = common.nvl(l_user, cfg.USER_BACKUP)
l_pwd = common.nvl(l_pwd, cfg.PWD_BACKUP)
l_database = common.nvl(l_database, cfg.DATABASE_BACKUP)
l_repository = common.nvl(l_repository, cfg.REPOSITORY_BACKUP)
l_expired = common.nvl(l_expired, cfg.EXPIRED_BACKUP)
l_mode = common.nvl(l_mode, cfg.MODE_BACKUP)
l_mysqlCNF = common.nvl(l_mysqlCNF, cfg.MYSQL_CNF)
l_exclude = common.nvl(l_exclude, cfg.EXCLUDE_BACKUP)
l_user_admin = cfg.USER_ADMIN
l_pwd_admin = cfg.PWD_ADMIN
l_port = cfg.PORT_BACKUP
modes = ['inc', 'full']
if l_mode in modes:
mysqlbk.execute_backup(l_database, l_port, l_mysqlCNF, l_user, l_pwd,
l_exclude, l_repository, l_mode, l_zip)
if (len(common.toStr(l_expired)) != 0):
mysqlbk.del_expired_backupset(l_repository, l_expired)
if 'n' != l_init:
mysqlbk.Init_backup_env(l_user_admin, l_pwd_admin, l_user, l_pwd,
l_port)
def format_Stories(workbook,
worksheet,
FRs,
Stories,
number_columns,
project,
timesheet):
number_rows = len(Stories.index)
format_all = workbook.add_format(cmn.format_all)
header_fmt = workbook.add_format(cmn.header_fmt)
effort_fmt = workbook.add_format(cmn.effort_fmt)
percent_fmt = workbook.add_format(cmn.percent_fmt)
worksheet.set_column('A:B', 11, format_all)
worksheet.set_column('C:C', 40, format_all)
worksheet.set_column('D:D', 11, format_all)
worksheet.set_column('E:F', 15, format_all)
worksheet.set_column('G:G', 18, format_all)
worksheet.set_column('H:K', 8, effort_fmt)
worksheet.set_column('L:N', 8, percent_fmt)
worksheet.set_column('O:Q', 8, effort_fmt)
worksheet.set_column('R:S', 8, percent_fmt)
worksheet.set_column('T:V', 8, effort_fmt)
worksheet.set_column('W:X', 8, percent_fmt)
worksheet.set_column('Y:AN',8, format_all)
worksheet.set_row(0, None, header_fmt)
format_alert = workbook.add_format(cmn.format_alert)
format_warn = workbook.add_format(cmn.format_warn)
format_impl = workbook.add_format(cmn.format_impl)
format_done = workbook.add_format(cmn.format_done)
format_cancelled = workbook.add_format(cmn.format_cancelled)
#Fact exceeds plan (forecast rate * spent rate) for over 20%
worksheet.conditional_format('J2:J{}'.format(number_rows+2), {'type': 'formula', 'criteria': '=($L2*$M2)>=1.2', 'format': format_alert})
#Cancelled
worksheet.conditional_format('A2:AN{}'.format(number_rows+1), {'type': 'formula', 'criteria': '=$AC2="Cancelled"', 'format': format_cancelled})
#Story is completed
worksheet.conditional_format('A2:AN{}'.format(number_rows+1), {'type': 'formula', 'criteria': '=$N2=1', 'format': format_done})
#Development is completed
worksheet.conditional_format('A2:AN{}'.format(number_rows+1), {'type': 'formula', 'criteria': '=$S2=1', 'format': format_impl})
#Progress pars
worksheet.conditional_format('N2:N{}'.format(number_rows+2), cmn.format_bar_done)
worksheet.conditional_format('S2:S{}'.format(number_rows+2), cmn.format_bar_impl)
worksheet.conditional_format('X2:X{}'.format(number_rows+2), cmn.format_bar_impl)
#Gained effort exceeds gained progress for over then 20%
worksheet.conditional_format('R2:R{}'.format(number_rows+2), {'type': 'formula', 'criteria': '=($R2-$S2)>=0.2', 'format': format_warn})
#Forecast exceeds plan for over than 20%
worksheet.conditional_format('L2:L{}'.format(number_rows+2),
{'type': 'cell',
'criteria': '>=',
'value': 1.2,
'format': format_alert})
total_effort_fmt = workbook.add_format({**cmn.effort_fmt, **{'bold': True, 'top': 6}})
total_percent_fmt = workbook.add_format({**cmn.percent_fmt, **{'bold': True, 'top': 6}})
worksheet.write_string(number_rows+1, 6, "Total",total_effort_fmt)
for column in [7, 8, 9, 10, 14, 15, 16, 19, 20, 21]:
worksheet.write_formula(xl_rowcol_to_cell(number_rows+1, column),
"=SUM({:s}:{:s})".format(xl_rowcol_to_cell(1, column), xl_rowcol_to_cell(number_rows, column)),
total_effort_fmt)
if FRs is not None:
for row in range(number_rows):
if cmn.nvl(Stories.iloc[row].name[0],'') != '':
try:
comment = (FRs.loc[Stories.iloc[row].name[0]]['Summary'] + '\n' +
str(int(FRs.loc[Stories.iloc[row].name[0]]['nonqa_rate'] * 100)) + '% - ' +
str(round(FRs.loc[Stories.iloc[row].name[0]]['HL Estimate, md'],1)) + '/' +
str(round(FRs.loc[Stories.iloc[row].name[0]]['total_fact_md'],1)) + '/' +
str(round(FRs.loc[Stories.iloc[row].name[0]]['total_remaining_md'],1)))
worksheet.write_comment('A{}'.format(row+2),comment)
except:
print('Cannot create comment for Story ' + Stories.iloc[row].name[1])
#Total fcast/plan
worksheet.write_formula(xl_rowcol_to_cell(number_rows+1, 11),
"={:s}/{:s}".format(xl_rowcol_to_cell(number_rows+1, 8),
xl_rowcol_to_cell(number_rows+1, 7)),
total_percent_fmt)
#Total fact/fcast
worksheet.write_formula(xl_rowcol_to_cell(number_rows+1, 12),
"={:s}/{:s}".format(xl_rowcol_to_cell(number_rows+1, 9),
xl_rowcol_to_cell(number_rows+1, 8)),
total_percent_fmt)
#Done, %
worksheet.write_formula(xl_rowcol_to_cell(number_rows+1, 13),
"=SUM({:s}:{:s})/SUM({:s}:{:s})".format(xl_rowcol_to_cell(1, 35),
xl_rowcol_to_cell(number_rows, 35),
xl_rowcol_to_cell(1, 8),
xl_rowcol_to_cell(number_rows, 8)),
total_percent_fmt)
#Impl fact/fcast
worksheet.write_formula(xl_rowcol_to_cell(number_rows+1, 17),
"={:s}/{:s}".format(xl_rowcol_to_cell(number_rows+1, 15),
xl_rowcol_to_cell(number_rows+1, 14)),
total_percent_fmt)
#Impl, %
worksheet.write_formula(xl_rowcol_to_cell(number_rows+1, 18),
"=SUM({:s}:{:s})/SUM({:s}:{:s})".format(xl_rowcol_to_cell(1, 37),
xl_rowcol_to_cell(number_rows, 37),
xl_rowcol_to_cell(1, 14),
xl_rowcol_to_cell(number_rows, 14)),
total_percent_fmt)
#QA fact/fcast
worksheet.write_formula(xl_rowcol_to_cell(number_rows+1, 22),
"={:s}/{:s}".format(xl_rowcol_to_cell(number_rows+1, 20),
xl_rowcol_to_cell(number_rows+1, 19)),
total_percent_fmt)
#QA, %
worksheet.write_formula(xl_rowcol_to_cell(number_rows+1, 23),
"=SUM({:s}:{:s})/SUM({:s}:{:s})".format(xl_rowcol_to_cell(1, 36),
xl_rowcol_to_cell(number_rows, 36),
xl_rowcol_to_cell(1, 19),
xl_rowcol_to_cell(number_rows, 19)),
total_percent_fmt)
if timesheet is not None:
worksheet.write_string(number_rows+2, 6, "Total ERP",total_effort_fmt)
worksheet.write_number(number_rows+2, 7, ter.PROJECTS_INFO[project]['projectLOE'], total_effort_fmt)
worksheet.write_number(number_rows+2, 9, timesheet['MD'].sum(), total_effort_fmt)
worksheet.autofilter(0,0,number_rows+1,number_columns+1)
return
def calc_subtasks(Story, Stories, subtasks):
#print('calc_subtasks in Story=' + Story)
Story_row = Stories[Stories.index.get_level_values(1) == Story].iloc[0]
story_estimate = original_estimate(Story_row)
story_fact = Story_row['Time Spent']
total_fact = Story_row['Σ Time Spent']
story_done_cnt = 0
story_done_estimate = 0
story_implemented_cnt = 0
story_implemented_estimate = 0
story_remaining_estimate = 0
subtasks_c = subtasks[(subtasks.index.get_level_values(1) == Story) & (subtasks['Status Mapped']!='Cancelled')]
subtasks_c_all = subtasks[subtasks.index.get_level_values(1) == Story]
cnt_subtasks = len(subtasks_c)
cnt_subtasks_all = len(subtasks_c_all)
if story_estimate > 0 or cnt_subtasks == 0:
story_cnt = 1
if Story_row['Status Mapped'] == 'Done':
story_done_cnt = 1
story_implemented_cnt = 1
story_implemented_estimate = story_estimate
story_done_estimate = story_estimate
elif Story_row['Status Mapped'] == 'In QA':
story_implemented_cnt = 1
story_implemented_estimate = story_estimate
elif Story_row['Status Mapped'] == 'Cancelled':
story_estimate = 0
else:
story_remaining_estimate = Story_row['Remaining Estimate']
else:
story_cnt = 0
cnt_total = cnt_subtasks + story_cnt
cnt_nonqa_subtasks = len(subtasks_c[subtasks_c['Step']!='QA'])
cnt_nonqa = cnt_nonqa_subtasks + story_cnt
cnt_qa = cnt_total-cnt_nonqa
cnt_done_subtasks = len(subtasks_c[subtasks_c['Status Mapped']=='Done'])
cnt_done = cnt_done_subtasks + story_done_cnt
cnt_done_qa = len(subtasks_c[(subtasks_c['Step']=='QA') & (subtasks_c['Status Mapped']=='Done')])
cnt_dev_implemented_subtasks = len(subtasks_c[(subtasks_c['Step']!='QA') & subtasks_c['Status Mapped'].isin(['In QA', 'Done'])])
cnt_dev_implemented = cnt_dev_implemented_subtasks + story_implemented_cnt
if cmn.nvl(Story_row['WBS Dev LOE'],0) > 0:
total_plan = Story_row['WBS Dev LOE']
else:
total_plan = Story_row['Σ Original Estimate']
if cnt_subtasks > 0:
total_estimate = sum(subtasks_c.apply(original_estimate,axis=1)) + story_estimate
total_remaining = sum(subtasks_c.apply(remaining_estimate,axis=1)) + story_remaining_estimate
elif cnt_subtasks_all > 0:
total_estimate = sum(subtasks_c_all.apply(original_estimate,axis=1)) + story_estimate
total_remaining = story_remaining_estimate
else:
total_estimate = story_estimate
total_remaining = story_remaining_estimate
total_estimate_for_rate = total_estimate
if total_plan > 0:
if total_estimate/total_plan <= 0.2:
total_estimate_for_rate = total_plan
if cnt_nonqa_subtasks > 0:
nonqa_estimate = sum(subtasks_c[subtasks_c['Step']!='QA'].apply(original_estimate,axis=1)) + story_estimate
nonqa_fact = sum(subtasks_c[subtasks_c['Step']!='QA'].apply(lambda row: row['Time Spent'],axis=1)) + story_fact
nonqa_remaining = sum(subtasks_c[subtasks_c['Step']!='QA'].apply(remaining_estimate,axis=1)) + story_remaining_estimate
else:
nonqa_estimate = story_estimate
nonqa_fact = story_fact
nonqa_remaining = story_remaining_estimate
qa_estimate = total_estimate - nonqa_estimate
qa_fact = total_fact - nonqa_fact
qa_remaining = total_remaining - nonqa_remaining
if cnt_done_subtasks > 0:
done_estimate = sum(subtasks_c[subtasks_c['Status Mapped']=='Done'].apply(original_estimate,axis=1)) + story_done_estimate
else:
done_estimate = story_done_estimate
if cnt_done_qa > 0:
done_qa_estimate = sum(subtasks_c[(subtasks_c['Step']=='QA') & (subtasks_c['Status Mapped']=='Done')].apply(original_estimate,axis=1))
else:
done_qa_estimate = 0
if cnt_dev_implemented_subtasks > 0:
implemented_dev_estimate = sum(subtasks_c[(subtasks_c['Step']!='QA') & subtasks_c['Status Mapped'].isin(['In QA', 'Done'])].apply(original_estimate,axis=1)) + story_implemented_estimate
else:
implemented_dev_estimate = story_implemented_estimate
if total_estimate_for_rate > 0:
total_rate = done_estimate / total_estimate_for_rate
total_spent_rate = total_fact / total_estimate_for_rate
else:
if cnt_done > 0:
total_rate = cnt_done / cnt_total
else:
total_rate = 0
total_spent_rate = 0
if nonqa_estimate > 0:
nonqa_rate = implemented_dev_estimate / nonqa_estimate
nonqa_spent_rate = nonqa_fact / nonqa_estimate
else:
if cnt_dev_implemented > 0:
nonqa_rate = cnt_dev_implemented / cnt_nonqa
else:
nonqa_rate = 0
nonqa_spent_rate = 0
if qa_estimate > 0:
qa_rate = done_qa_estimate / qa_estimate
qa_spent_rate = qa_fact / qa_estimate
else:
if cnt_done_qa > 0:
qa_rate = cnt_done_qa / cnt_qa
else:
qa_rate = 0
qa_spent_rate = 0
if total_plan > 0:
total_fcast_plan_rate = total_estimate / total_plan
else:
total_fcast_plan_rate = 0
if cnt_total == 0 or total_estimate == 0:
if Story_row['Status Mapped'] == 'Done':
total_rate = 1
nonqa_rate = 1
qa_rate = 1
elif Story_row['Status Mapped'] == 'In QA':
total_rate = 0
nonqa_rate = 1
qa_rate = 0
return pd.Series({'cnt_total': cnt_total,
'cnt_nonqa': cnt_nonqa,
'cnt_qa': cnt_qa,
'cnt_done': cnt_done,
'cnt_done_qa': cnt_done_qa,
'cnt_dev_implemented': cnt_dev_implemented,
'total_plan' : total_plan,
'total_estimate': total_estimate,
'nonqa_estimate': nonqa_estimate,
'qa_estimate': qa_estimate,
'total_fcast_plan_rate': total_fcast_plan_rate,
'story_fact': story_fact,
'nonqa_fact': nonqa_fact,
'qa_fact': qa_fact,
'total_remaining': total_remaining,
'nonqa_remaining': nonqa_remaining,
'qa_remaining': qa_remaining,
'total_spent_rate': total_spent_rate,
'nonqa_spent_rate': nonqa_spent_rate,
'qa_spent_rate': qa_spent_rate,
'done_estimate': done_estimate,
'done_qa_estimate': done_qa_estimate,
'implemented_dev_estimate': implemented_dev_estimate,
'total_rate': total_rate,
'nonqa_rate': nonqa_rate,
'qa_rate': qa_rate})