def cdg_narrative_dashboard(
master: Master,
# milestones: MilestoneData,
wb_path: Workbook
) -> Workbook:
wb = load_workbook(wb_path)
ws = wb.active
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in master.current_projects:
# Group
ws.cell(row=row_num, column=2).value = master.project_information[project_name]["Directorate"]
# Abbreviation
ws.cell(row=row_num, column=4).value = master.project_information[project_name]["Abbreviations"]
# Stage
bc_stage = master.master_data[0]["data"][project_name][DATA_KEY_DICT["IPDC approval point"]]
ws.cell(row=row_num, column=5).value = convert_bc_stage_text(bc_stage)
costs = master.master_data[0]["data"][project_name][DATA_KEY_DICT["Total Forecast"]]
ws.cell(row=row_num, column=6).value = dandelion_number_text(costs)
overall_dca = convert_rag_text(
master.master_data[0]["data"][project_name][DATA_KEY_DICT["Departmental DCA"]]
)
ws.cell(row=row_num, column=7).value = overall_dca
if overall_dca == "None":
ws.cell(row=row_num, column=7).value = ""
sro_n = master.master_data[0]["data"][project_name]["SRO Narrative"]
ws.cell(row=row_num, column=8).value = sro_n
"""list of columns with conditional formatting"""
list_columns = ["g"]
"""same loop but the text is black. In addition these two loops go through the list_columns list above"""
for column in list_columns:
for i, dca in enumerate(rag_txt_list):
text = black_text
fill = fill_colour_list[i]
dxf = DifferentialStyle(font=text, fill=fill)
rule = Rule(
type="containsText", operator="containsText", text=dca, dxf=dxf
)
for_rule_formula = 'NOT(ISERROR(SEARCH("' + dca + '",' + column + "5)))"
rule.formula = [for_rule_formula]
ws.conditional_formatting.add(column + "5:" + column + "60", rule)
for row_num in range(2, ws.max_row + 1):
for col_num in range(5, ws.max_column + 1):
if ws.cell(row=row_num, column=col_num).value == 0:
ws.cell(row=row_num, column=col_num).value = "-"
return wb
def cdg_dashboard(
master: Master,
# milestones: MilestoneData,
wb_path: Workbook
) -> Workbook:
wb = load_workbook(wb_path)
ws = wb.active
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in master.current_projects:
# Group
ws.cell(row=row_num, column=2).value = master.project_information[project_name]["Directorate"]
# Abbreviation
ws.cell(row=row_num, column=4).value = master.project_information[project_name]["Abbreviations"]
# Stage
bc_stage = master.master_data[0]["data"][project_name][DATA_KEY_DICT["IPDC approval point"]]
ws.cell(row=row_num, column=5).value = convert_bc_stage_text(bc_stage)
# try:
# bc_stage_lst_qrt = master.master_data[1].data[project_name][
# "IPDC approval point"
# ]
# if bc_stage != bc_stage_lst_qrt:
# # ws.cell(row=row_num, column=4).font = Font(
# # name="Arial", size=10, color="00fc2525"
# # )
# ws.cell(row=row_num, column=3).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
# except KeyError:
# pass
# Total Costs
costs = master.master_data[0]["data"][project_name][DATA_KEY_DICT["Total Forecast"]]
ws.cell(row=row_num, column=6).value = dandelion_number_text(costs, none_handle="none")
# try:
# wlc_lst_quarter = master.master_data[1].data[project_name][
# "Total Forecast"
# ]
# diff_lst_qrt = wlc_now - wlc_lst_quarter
# if float(diff_lst_qrt) > 0.49 or float(diff_lst_qrt) < -0.49:
# # ws.cell(row=row_num, column=7).value = diff_lst_qrt
# ws.cell(row=row_num, column=6).value = diff_lst_qrt
# else:
# # ws.cell(row=row_num, column=7).value = "-"
# ws.cell(row=row_num, column=6).value = "-"
#
# try:
# percentage_change = ((wlc_now - wlc_lst_quarter) / wlc_now) * 100
# if percentage_change > 5 or percentage_change < -5:
# # ws.cell(row=row_num, column=7).font = Font(
# # name="Arial", size=10, color="00fc2525"
# # )
# ws.cell(row=row_num, column=6).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
# except ZeroDivisionError:
# pass
# except KeyError:
# ws.cell(row=row_num, column=6).value = "-"
# Total Income
income = master.master_data[0]["data"][project_name]["Total Income"]
ws.cell(row=row_num, column=7).value = dandelion_number_text(income, none_handle="none")
# Total Benefits
benefits = master.master_data[0]["data"][project_name]["Total Benefits"]
ws.cell(row=row_num, column=8).value = dandelion_number_text(benefits, none_handle="none")
# VfM Category
vfm = master.master_data[0]["data"][project_name]["VfM Category"]
ws.cell(row=row_num, column=9).value = vfm
# """WLC variance against baseline quarter"""
# bl = master.bl_index["ipdc_costs"][project_name][2]
# wlc_baseline = master.master_data[bl].data[project_name]["Total Forecast"]
# try:
# diff_bl = wlc_now - wlc_baseline
# if float(diff_bl) > 0.49 or float(diff_bl) < -0.49:
# # ws.cell(row=row_num, column=8).value = diff_bl
# ws.cell(row=row_num, column=7).value = diff_bl
# else:
# # ws.cell(row=row_num, column=8).value = "-"
# ws.cell(row=row_num, column=7).value = "-"
# except TypeError: # exception is here as some projects e.g. Hs2 phase 2b have (real) written into historical totals
# pass
# try:
# percentage_change = ((wlc_now - wlc_baseline) / wlc_now) * 100
# if percentage_change > 5 or percentage_change < -5:
# # ws.cell(row=row_num, column=8).font = Font(
# # name="Arial", size=10, color="00fc2525"
# # )
# ws.cell(row=row_num, column=7).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
#
# except (
# ZeroDivisionError,
# TypeError,
# ): # zerodivision error obvious, type error handling as above
# pass
# """vfm category now"""
# if (
# master.master_data[0].data[project_name]["VfM Category single entry"]
# is None
# ):
# vfm_cat = (
# str(
# master.master_data[0].data[project_name][
# "VfM Category lower range"
# ]
# )
# + " - "
# + str(
# master.master_data[0].data[project_name][
# "VfM Category upper range"
# ]
# )
# )
# # ws.cell(row=row_num, column=10).value = vfm_cat
# ws.cell(row=row_num, column=8).value = vfm_cat
#
# else:
# vfm_cat = master.master_data[0].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=10).value = vfm_cat
# ws.cell(row=row_num, column=8).value = vfm_cat
#
# """vfm category baseline"""
# bl_i = master.bl_index["ipdc_benefits"][project_name][2]
# try:
# if (
# master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# is None
# ):
# vfm_cat_baseline = (
# str(
# master.master_data[bl_i].data[project_name][
# "VfM Category lower range"
# ]
# )
# + " - "
# + str(
# master.master_data[bl_i].data[project_name][
# "VfM Category upper range"
# ]
# )
# )
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
# else:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
#
# except KeyError:
# try:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category single entry"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
# except KeyError:
# vfm_cat_baseline = master.master_data[bl_i].data[project_name][
# "VfM Category"
# ]
# # ws.cell(row=row_num, column=11).value = vfm_cat_baseline
#
# if vfm_cat != vfm_cat_baseline:
# if vfm_cat_baseline is None:
# pass
# else:
# ws.cell(row=row_num, column=8).font = Font(
# name="Arial", size=8, color="00fc2525"
# )
#
# abb = master.abbreviations[project_name]["abb"]
# current = get_milestone_date(
# abb, milestones.milestone_dict, "current", " Full Operations"
# )
# last_quarter = get_milestone_date(
# abb, milestones.milestone_dict, "last", " Full Operations"
# )
# bl = get_milestone_date(
# abb, milestones.milestone_dict, "bl_one", " Full Operations"
# )
# ws.cell(row=row_num, column=9).value = current
# if current is not None and current < IPDC_DATE:
# ws.cell(row=row_num, column=9).value = "Completed"
# try:
# last_change = (current - last_quarter).days
# ws.cell(row=row_num, column=10).value = plus_minus_days(last_change)
# if last_change is not None and last_change > 46:
# ws.cell(row=row_num, column=10).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
# except TypeError:
# pass
# try:
# bl_change = (current - bl).days
# ws.cell(row=row_num, column=11).value = plus_minus_days(bl_change)
# if bl_change is not None and bl_change > 85:
# ws.cell(row=row_num, column=11).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
# except TypeError:
# pass
# last at/next at ipdc information removed
# try:
# ws.cell(row=row_num, column=12).value = concatenate_dates(
# master.master_data[0].data[project_name]["Last time at BICC"],
# IPDC_DATE,
# )
# ws.cell(row=row_num, column=13).value = concatenate_dates(
# master.master_data[0].data[project_name]["Next at BICC"],
# IPDC_DATE,
# )
# except (KeyError, TypeError):
# print(
# project_name
# + " last at / next at ipdc data could not be calculated. Check data."
# )
# DCA ratings
overall_dca = convert_rag_text(
master.master_data[0]["data"][project_name][DATA_KEY_DICT["Departmental DCA"]]
)
ws.cell(row=row_num, column=10).value = overall_dca
if overall_dca == "None":
ws.cell(row=row_num, column=10).value = ""
conf_list = ["Costs Confidence", "Schedule Confidence", "Benefits Confidence"]
for i, key in enumerate(conf_list):
dca = convert_rag_text(
master.master_data[0]["data"][project_name][key]
)
ws.cell(row=row_num, column=11+i).value = dca
risk_list = [
"Benefits",
"Capability",
"Cost",
"Objectives",
"Purpose",
"Schedule",
"Sponsorship",
"Stakeholders",
]
for i, key in enumerate(risk_list):
risk = master.master_data[0]["data"][project_name][key]
if risk == "YES":
ws.cell(row=row_num, column=14+i).value = risk
# """DCA rating - this quarter"""
# ws.cell(row=row_num, column=17).value = convert_rag_text(
# master.master_data[0].data[project_name]["Departmental DCA"]
# )
# """DCA rating - last qrt"""
# try:
# ws.cell(row=row_num, column=19).value = convert_rag_text(
# master.master_data[1].data[project_name]["Departmental DCA"]
# )
# except KeyError:
# ws.cell(row=row_num, column=19).value = ""
# """DCA rating - 2 qrts ago"""
# try:
# ws.cell(row=row_num, column=20).value = convert_rag_text(
# master.master_data[2].data[project_name]["Departmental DCA"]
# )
# except (KeyError, IndexError):
# ws.cell(row=row_num, column=20).value = ""
# """DCA rating - 3 qrts ago"""
# try:
# ws.cell(row=row_num, column=21).value = convert_rag_text(
# master.master_data[3].data[project_name]["Departmental DCA"]
# )
# except (KeyError, IndexError):
# ws.cell(row=row_num, column=21).value = ""
# """DCA rating - baseline"""
# bl_i = master.bl_index["ipdc_costs"][project_name][2]
# ws.cell(row=row_num, column=23).value = convert_rag_text(
# master.master_data[bl_i].data[project_name]["Departmental DCA"]
# )
def sro_narrative():
sro_n = master.master_data[0].data[project_name]["SRO Narrative"]
ws.cell(row=row_num, column=22).value = sro_n
# sro_narrative()
"""list of columns with conditional formatting"""
list_columns = ["j", "k", "l", "m"]
"""same loop but the text is black. In addition these two loops go through the list_columns list above"""
for column in list_columns:
for i, dca in enumerate(rag_txt_list):
text = black_text
fill = fill_colour_list[i]
dxf = DifferentialStyle(font=text, fill=fill)
rule = Rule(
type="containsText", operator="containsText", text=dca, dxf=dxf
)
for_rule_formula = 'NOT(ISERROR(SEARCH("' + dca + '",' + column + "5)))"
rule.formula = [for_rule_formula]
ws.conditional_formatting.add(column + "5:" + column + "60", rule)
for row_num in range(2, ws.max_row + 1):
for col_num in range(5, ws.max_column + 1):
if ws.cell(row=row_num, column=col_num).value == 0:
ws.cell(row=row_num, column=col_num).value = "-"
return wb
def project_report_meta_data(
doc: Document,
master: Dict,
project_name: str,
):
p_master = master.master_data[0]["data"][project_name]
try:
p_master_last = master.master_data[1]["data"][project_name]
except KeyError:
p_master_last = p_master
"""Meta data table"""
# doc.add_section(WD_SECTION_START.NEW_PAGE)
# paragraph = doc.add_paragraph()
# paragraph.alignment = WD_ALIGN_PARAGRAPH.RIGHT
# paragraph.add_run("Key Info").bold = True
doc.add_paragraph().add_run("Key Info").bold = True
if p_master["WLC COMMENTS"] is not None:
# doc.add_paragraph()
doc.add_paragraph().add_run("* Total costs comment. " +
str(p_master["WLC COMMENTS"]))
"""Costs meta data"""
t = doc.add_table(rows=1, cols=4)
hdr_cells = t.rows[0].cells
hdr_cells[0].text = "ON SCHEDULE:"
on_time = str(p_master["PROJECT DEL TO CURRENT TIMINGS ?"])
on_time_old = str(p_master_last["PROJECT DEL TO CURRENT TIMINGS ?"])
hdr_cells[1].text = on_time
# cell_colouring(hdr_cells[1], on_time, on_time_old)
make_text_red(hdr_cells[1], on_time, on_time_old)
hdr_cells[2].text = "ON GMPP:"
on_gmpp = str(p_master["GMPP ID: IS THIS PROJECT ON GMPP"])
on_gmpp_last = str(p_master_last["GMPP ID: IS THIS PROJECT ON GMPP"])
hdr_cells[3].text = on_gmpp
make_text_red(hdr_cells[3], on_gmpp, on_gmpp_last)
# cell_colouring(hdr_cells[3], on_gmpp, on_gmpp_last)
row_cells = t.add_row().cells
row_cells[0].text = "ON BUDGET:"
on_budget = str(p_master["PROJECT ON BUDGET?"])
on_budget_last = str(p_master_last["PROJECT ON BUDGET?"])
row_cells[1].text = on_budget
make_text_red(row_cells[1], on_budget, on_budget_last)
# cell_colouring(row_cells[1], on_budget, on_budget_last)
row_cells[2].text = "TOTAL COST:"
if p_master["WLC NON GOV"] is None or p_master["WLC NON GOV"] == 0:
total = dandelion_number_text(p_master["WLC TOTAL"])
total_last = dandelion_number_text(p_master_last["WLC TOTAL"])
else:
total = dandelion_number_text(p_master["WLC TOTAL"] +
p_master["WLC NON GOV"])
total_last = dandelion_number_text(p_master_last["WLC TOTAL"] +
p_master_last["WLC NON GOV"])
row_cells[3].text = total
make_text_red(row_cells[3], total, total_last)
# row_cells = t.add_row().cells
# row_cells[0].text = "SRO CLEARANCE DATE:"
# row_cells[1].text = str(p_master["DATE CLEARED BY SRO"])
# row_cells[2].text = "MOST RECENT PERM SEC REVIEW:"
# row_cells[3].text = str(p_master["DATE OF MOST RECENT PERM SEC REVIEW"])
# set column width
column_widths = (Cm(4), Cm(3), Cm(4), Cm(3))
set_col_widths(t, column_widths)
# make column keys bold
make_columns_bold([t.columns[0], t.columns[2]])
# change_text_size([t.columns[0], t.columns[1], t.columns[2], t.columns[3]], 10)
# make_text_red([t.columns[1], t.columns[3]])
return doc