def _create_known_bugs_table(
sheet, project: Project, teams: List[Team],
offset=Pos()) -> Tuple[Dict[Team, CellReference], Region]:
dev_teams: List[DevTeam] = [team for team in teams if team.is_dev()]
header_table = _create_known_bugs_table_header(sheet, offset)
total_cells_by_teams = {}
cells_offset = header_table.pos_below()
row = 0
for team in dev_teams:
write_row(
sheet,
offset=RelPos(cells_offset, row, 1),
cell_generator=[
team.name, project.known_bugs_count[team],
calculate_new_bugs_count_for_project(project, team),
[
f'=SUM('
f'{RelPos(cells_offset, row, 2).to_cell()}:{RelPos(cells_offset, row, 3).to_cell()}'
f') * {team.bugfix_rate} / 5',
formats.bold_total_no_borders_format
]
],
cell_format=formats.numeric_format)
total_cells_by_teams[team] = CellReference(
RelPos(cells_offset, row, 4, sheet_name=project.name),
f'{project.name} bugfix')
row += 1
return \
total_cells_by_teams, \
Region(header_table.offset, header_table.rows + len(dev_teams), header_table.columns)
def _create_dev_activities_table(
sheet, project: Project, teams: List[Team],
offset=Pos()) -> Tuple[Dict[Team, CellReference], Region]:
dev_owned_issues = [
x for x in project.issues
if x.owned_by_team is not None and x.owned_by_team.is_dev()
]
if not dev_owned_issues:
return dict(), Region(offset, 1, 0)
header_table = _create_project_table_header(sheet, teams, offset)
for row in range(0, len(dev_owned_issues)):
issue = dev_owned_issues[row]
rel_offset = RelPos(header_table.pos_below(), row)
write_row(sheet,
rel_offset,
cell_generator=_create_cells_for_issue(
issue, teams, rel_offset),
cell_format=formats.numeric_format)
# totals row
_add_totals_row(
sheet,
Region(header_table.pos_below(), len(dev_owned_issues),
header_table.columns), 4)
# adjust column widths
sheet.set_column(
offset.column + 1, offset.column + 1,
max(int(len(x.issue_summary) * 0.75)
for x in dev_owned_issues) if dev_owned_issues else 10)
sheet.set_column(
offset.column, offset.column,
max(len(x.issue_key)
for x in dev_owned_issues) if dev_owned_issues else 10)
# cell references to totals for every team incl dev and qa
total_cells_by_team = {}
dev_teams = [x for x in teams if x.is_dev()]
team_column = header_table.offset.column + 11 + 2 * len(dev_teams)
for team in dev_teams:
total_cells_by_team[team] = CellReference(
RelPos(offset,
header_table.pos_below().row + len(dev_owned_issues),
team_column, project.name), project.name)
team_column += 1
qa_teams = [x for x in teams if x.is_qa()]
team_column = header_table.offset.column + 12 + 3 * len(dev_teams)
for team in qa_teams:
total_cells_by_team[team] = CellReference(
RelPos(offset,
header_table.pos_below().row + len(dev_owned_issues),
team_column, project.name), f'{project.name} checks')
team_column += 1
return \
total_cells_by_team, \
Region(header_table.offset, header_table.rows + len(dev_owned_issues) + 1, header_table.columns)
def _create_team_capacity_table(
sheet, start_date: date, end_date: date, team: Team, offset: Pos = Pos()
) -> Region:
sheet.write(offset.row, offset.column, 'People', formats.green_header_format)
column_count = write_row(
sheet,
RelPos(offset, 0, 1),
cell_generator=(x.strftime(MONTH_FORMAT) for x in get_months_range(start_date, end_date)),
cell_format=formats.green_header_format
)
v_offset = offset.row + 1
for worker in team.members:
sheet.write(v_offset, offset.column, worker.name)
write_row(
sheet,
Pos(v_offset, offset.column + 1),
cell_generator=(worker.efficiency(dt) for dt in get_months_range(start_date, end_date)),
cell_format=formats.numeric_format
)
v_offset += 1
sheet.write(offset.row + len(team.members) + 1, 0, 'Total', formats.bold_total_format)
write_row(
sheet,
RelPos(offset, len(team.members) + 1, 1),
cell_func=lambda col, _:
f'=SUM({RelPos(offset, 1, col).to_cell()}:{RelPos(offset, len(team.members), col).to_cell()})',
col_count=column_count,
cell_format=formats.bold_total_format
)
return Region(Pos(0, 0), 2 + len(team.members), column_count)
def save_metadata(sheet, start_date: date, end_date: date,
allocations_by_team: Dict[Team, Dict[str, Region]]):
offset = Pos(0, 0)
write_row(sheet, offset, cell_generator=[METADATA_HEADER])
write_row(sheet,
RelPos(offset, 1),
cell_generator=[
'Period',
start_date.isoformat(),
end_date.isoformat()
])
write_row(sheet, RelPos(offset, 2), cell_generator=['Team calendars'])
offset = RelPos(offset, 3)
row = 0
for team, allocations in allocations_by_team.items():
write_row(sheet,
RelPos(offset, row),
cell_generator=['Team', team.name])
row += 1
for item_name, table_pos in allocations.items():
write_row(sheet,
RelPos(offset, row),
cell_generator=[
'Item', item_name, table_pos.offset.row,
table_pos.offset.column, table_pos.rows,
table_pos.columns
])
row += 1
def _create_known_bugs_table_header(sheet, offset: Pos) -> Region:
write_row(sheet,
offset,
cell_generator=[
'', ['Team', formats.centered_header_format], 'Known bugs',
'New bugs', 'Time to fix'
],
cell_format=formats.centered_vertical_text_header_format,
col_width=5)
return Region(offset, 1, 5)
def _add_totals_row(sheet, region: Region, skip_columns: int = 0):
totals = []
for column in range(0, region.columns):
if column == 0:
totals.append('Total')
elif column >= skip_columns:
totals.append(
f'=SUM('
f'{RelPos(region.offset, column=column).to_cell()}'
f':{RelPos(region.offset, region.rows - 1, column).to_cell()}'
f')')
else:
totals.append('')
offset = RelPos(region.offset, region.rows)
write_row(sheet, offset, totals, cell_format=formats.bold_total_format)
def _create_qa_activities_table(
sheet, project: Project, teams: List[Team],
offset: Pos) -> Tuple[Dict[Team, List[CellReference]], Region]:
qa_owned_issues = [
x for x in project.issues
if x.owned_by_team is not None and x.owned_by_team.is_qa()
]
if not qa_owned_issues:
return dict(), Region(offset, 1, 0)
qa_teams = [team for team in teams if team.is_qa()]
header_table = _create_qa_activities_table_header(sheet, qa_teams, offset)
total_cells_by_teams = {}
for row in range(0, len(qa_owned_issues)):
issue = qa_owned_issues[row]
rel_offset = RelPos(header_table.pos_below(), row)
write_row(sheet,
rel_offset,
cell_generator=_create_cells_for_qa_issue(
issue, qa_teams, rel_offset),
cell_format=formats.numeric_format)
col = 2
for team in qa_teams:
if team not in total_cells_by_teams:
total_cells_by_teams[team] = []
total_cells_by_teams[team].append(
CellReference(
RelPos(rel_offset, column=col, sheet_name=project.name),
issue.issue_summary))
col += 1
# totals row
_add_totals_row(
sheet,
Region(header_table.pos_below(), len(qa_owned_issues),
header_table.columns), 2)
return \
total_cells_by_teams, \
Region(header_table.offset, header_table.rows + len(qa_owned_issues) + 1, header_table.columns)
def _create_qa_activities_table_header(sheet, qa_teams: List[Team],
offset: Pos) -> Region:
merge_cells(sheet, RelPos(offset, 0, 0), RelPos(offset, 1, 0), 'Key',
formats.centered_header_format)
merge_cells(sheet, RelPos(offset, 0, 1), RelPos(offset, 1, 1), 'Summary',
formats.centered_header_format)
rel_offset = RelPos(offset, 0, 2)
merge_cells(sheet, rel_offset, RelPos(rel_offset, 0, len(qa_teams)),
'QA effort', formats.centered_header_border_format)
rel_offset = RelPos(rel_offset, 1, 0)
write_row(
sheet,
rel_offset,
cell_generator=[[
qa_teams[i].name, formats.centered_vertical_text_header_format if i
!= 0 else formats.centered_vertical_text_header_left_border_format
] for i in range(len(qa_teams))] +
[['Total', formats.centered_vertical_text_header_right_border_format]],
cell_format=formats.centered_vertical_text_header_format,
col_width=5)
return Region(offset, 2, 3 + len(qa_teams))
def _create_project_table_header(sheet, teams: List[Team],
offset=Pos()) -> Region:
dev_teams = [team for team in teams if team.is_dev()]
qa_teams = [team for team in teams if team.is_qa()]
column_count = 14 + 3 * len(dev_teams) + len(qa_teams)
merge_cells(sheet, RelPos(offset, 1, 0), RelPos(offset, 2, 0), 'Key',
formats.centered_header_format)
merge_cells(sheet, RelPos(offset, 1, 1), RelPos(offset, 2, 1), 'Summary',
formats.centered_header_format)
merge_cells(sheet, RelPos(offset, 1, 2), RelPos(offset, 1, 3),
'Confidence', formats.centered_header_border_format)
_, col = merge_cells(sheet,
RelPos(offset, 1, 4),
RelPos(offset, 2, 4),
'Arch design',
formats.centered_vertical_text_header_format,
width=5)
merge_cells(sheet, RelPos(offset, 1, 5),
RelPos(offset, 1, 5 + len(dev_teams)), 'Impl & unit tests',
formats.centered_header_border_format)
rel_offset = RelPos(offset, 0, 5 + len(dev_teams) + 1)
merge_cells(sheet,
RelPos(rel_offset, 1, 0),
RelPos(rel_offset, 2, 0),
'Integration',
formats.centered_vertical_text_header_format,
width=5)
merge_cells(sheet,
RelPos(rel_offset, 1, 1),
RelPos(rel_offset, 2, 1),
'Test automation',
formats.centered_vertical_text_header_format,
width=5)
merge_cells(sheet,
RelPos(rel_offset, 1, 2),
RelPos(rel_offset, 2, 2),
'Stabilization',
formats.centered_vertical_text_header_format,
width=5)
merge_cells(sheet,
RelPos(rel_offset, 1, 3),
RelPos(rel_offset, 2, 3),
'Documentation',
formats.centered_vertical_text_header_format,
width=5)
merge_cells(sheet, RelPos(rel_offset, 1, 4),
RelPos(rel_offset, 1, 4 + len(dev_teams)), 'Perf engineering',
formats.centered_header_border_format)
rel_offset = RelPos(rel_offset, 0, 4 + len(dev_teams) + 1)
merge_cells(sheet, RelPos(rel_offset, 1, 0),
RelPos(rel_offset, 1, len(dev_teams)), 'Feature dev subtotal',
formats.centered_header_border_format)
rel_offset = RelPos(rel_offset, 0, len(dev_teams) + 1)
merge_cells(sheet, RelPos(rel_offset, 1, 0),
RelPos(rel_offset, 1, len(qa_teams)), 'QA effort',
formats.centered_header_border_format)
rel_offset = RelPos(rel_offset, 1, len(qa_teams))
merge_cells(sheet, RelPos(rel_offset, 0, 1), RelPos(rel_offset, 1, 1),
'Feature total', formats.centered_header_format)
write_row(
sheet,
RelPos(offset, 2, 2),
cell_generator=[[
'Reqs', formats.centered_vertical_text_header_left_border_format
], [
'Design', formats.centered_vertical_text_header_right_border_format
]],
col_width=5)
rel_offset = RelPos(offset, 2, 5)
columns = write_row(
sheet,
rel_offset,
cell_generator=[[
dev_teams[i].name,
formats.centered_vertical_text_header_format if i != 0 else
formats.centered_vertical_text_header_left_border_format
] for i in range(len(dev_teams))] +
[['Total', formats.centered_vertical_text_header_right_border_format]],
cell_format=formats.centered_vertical_text_header_format,
col_width=5)
rel_offset = RelPos(rel_offset, 0, columns + 4)
columns = write_row(
sheet,
rel_offset,
cell_generator=[[
dev_teams[i].name,
formats.centered_vertical_text_header_format if i != 0 else
formats.centered_vertical_text_header_left_border_format
] for i in range(len(dev_teams))] +
[['Total', formats.centered_vertical_text_header_right_border_format]],
cell_format=formats.centered_vertical_text_header_format,
col_width=5)
rel_offset = RelPos(rel_offset, 0, columns)
columns = write_row(
sheet,
rel_offset,
cell_generator=[[
dev_teams[i].name,
formats.centered_vertical_text_header_format if i != 0 else
formats.centered_vertical_text_header_left_border_format
] for i in range(len(dev_teams))] +
[['Total', formats.centered_vertical_text_header_right_border_format]],
cell_format=formats.centered_vertical_text_header_format,
col_width=5)
rel_offset = RelPos(rel_offset, 0, columns)
write_row(
sheet,
rel_offset,
cell_generator=[[
qa_teams[i].name, formats.centered_vertical_text_header_format if i
!= 0 else formats.centered_vertical_text_header_left_border_format
] for i in range(len(qa_teams))] +
[['Total', formats.centered_vertical_text_header_right_border_format]],
cell_format=formats.centered_vertical_text_header_format,
col_width=5)
return Region(RelPos(offset), 3, column_count)
def _create_team_calendar_table(
sheet,
capacity_table: Region,
start_date: date,
end_date: date,
production_calendar: dict,
projects: List[Project],
total_cells: List[CellReference]
) -> Dict[str, Region]:
offset = RelPos(capacity_table.pos_below(), 1)
sheet.write(offset.row, offset.column, '', formats.green_header_format)
column_count = write_row(
sheet,
RelPos(offset, 0, 1),
cell_generator=(x.strftime(MONTH_FORMAT) for x in get_months_range(start_date, end_date)),
cell_format=formats.green_header_format
)
sheet.write(offset.row + 1, offset.column, 'People')
write_row(
sheet,
RelPos(offset, 1, 1),
cell_func=lambda col, _: f'={Pos(capacity_table.pos_below().row - 1, col).to_cell()}',
col_count=column_count,
cell_format=formats.numeric_format
)
sheet.write(offset.row + 2, offset.column, 'Working days')
workdays_count = get_month_workdays_count(start_date, end_date, production_calendar)
write_row(
sheet,
RelPos(offset, 2, 1),
cell_generator=get_months_range(start_date, end_date),
cell_func=lambda _, d: workdays_count[d],
col_count=column_count
)
sheet.write(offset.row + 3, offset.column, 'Working weeks')
write_row(
sheet,
RelPos(offset, 3, 1),
cell_func=lambda col, _: f'={Pos(offset.row + 2, col).to_cell()} / 5',
col_count=column_count
)
sheet.write(offset.row + 4, offset.column, 'Man * weeks')
write_row(
sheet,
RelPos(offset, 4, 1),
cell_func=lambda c, _: f'={Pos(offset.row + 1, c).to_cell()} * {Pos(offset.row + 3, c).to_cell()}',
col_count=column_count,
cell_format=formats.numeric_format
)
# TODO consider either time of the year or real vacations of people (e.g. create vacation field for worker)
# TODO or use both at once - when real dates of vacations are not known
sheet.write(offset.row + 5, offset.column, 'Vacations')
calendar_table_totals_row = capacity_table.pos_below().row - 1
write_row(
sheet,
RelPos(offset, 5, 1),
cell_func=lambda c, _: f'={Pos(calendar_table_totals_row, c).to_cell()} * 5 / 12',
col_count=column_count,
cell_format=formats.numeric_format
)
sheet.write(offset.row + 6, offset.column, 'Support tasks')
write_row(
sheet,
RelPos(offset, 6, 1),
cell_func=lambda c, _: 1.5,
col_count=column_count,
cell_format=formats.numeric_format
)
sheet.write(offset.row + 7, offset.column, 'Remaining', formats.bold_total_format)
write_row(
sheet,
RelPos(offset, 7, 1),
cell_func=lambda c, _:
f'={Pos(offset.row + 4, c).to_cell()}'
f'-{Pos(offset.row + 5, c).to_cell()}'
f'-{Pos(offset.row + 6, c).to_cell()}',
col_count=column_count,
cell_format=formats.bold_total_format
)
sheet.set_column(0, 0, width=15)
# allocations region
offset = RelPos(offset, 8)
time_allocation_cells_by_item: Dict[str, Region] = {}
row_count = 0
for cell_ref in total_cells:
allocation_pos = RelPos(offset, row_count)
sheet.write(allocation_pos.row, allocation_pos.column, cell_ref.title)
time_allocation_cells_by_item[cell_ref.title] = Region(allocation_pos, 1, column_count)
row_count += 1
# difference between total available and allocated resources
offset = RelPos(offset, row_count)
sheet.write(offset.row, 0, 'Difference', formats.bold_total_format)
for i in range(1, column_count + 1):
sheet.write(
offset.row,
offset.column + i,
f'={RelPos(offset, - len(projects) - 1, i).to_cell()}'
f'-SUM({RelPos(offset, - len(projects), i).to_cell()}:{RelPos(offset, - 1, i).to_cell()})',
formats.bold_total_format
)
# summary by project rows
offset = RelPos(offset, len(projects))
write_row(
sheet, offset, cell_generator=['Item', 'Needed', 'Allocated', 'Diff'], cell_format=formats.green_header_format
)
offset = RelPos(offset, 1)
row_count = 0
for cell_ref in total_cells:
row_offset = RelPos(offset, row_count)
allocation_pos = time_allocation_cells_by_item[cell_ref.title].offset
write_row(
sheet,
row_offset,
cell_generator=[
cell_ref.title,
f'={cell_ref.pos.to_cell()}',
f'=SUM({allocation_pos.to_cell()}:{RelPos(allocation_pos, column=column_count).to_cell()})',
f'={RelPos(row_offset, column=2).to_cell()}-{RelPos(row_offset, column=1).to_cell()}'
],
cell_format=formats.numeric_format
)
row_count += 1
return time_allocation_cells_by_item