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 cdg_overall_dashboard(master: Master, wb: Workbook) -> Workbook:
wb = load_workbook(wb)
ws = wb.worksheets[0]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=2).value
if project_name in master.current_projects:
"""BC Stage"""
bc_stage = master.master_data[0].data[project_name]["CDG approval point"]
# ws.cell(row=row_num, column=4).value = convert_bc_stage_text(bc_stage)
ws.cell(row=row_num, column=3).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = master.master_data[1].data[project_name][
"CDG 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, IndexError):
pass
"""planning stage"""
plan_stage = master.master_data[0].data[project_name]["Project stage"]
# ws.cell(row=row_num, column=5).value = plan_stage
ws.cell(row=row_num, column=4).value = plan_stage
try:
plan_stage_lst_qrt = master.master_data[1].data[project_name][
"Project stage"
]
if plan_stage != plan_stage_lst_qrt:
# ws.cell(row=row_num, column=5).font = Font(
# name="Arial", size=10, color="00fc2525"
# )
ws.cell(row=row_num, column=4).font = Font(
name="Arial", size=10, color="00fc2525"
)
except (KeyError, IndexError):
pass
"""Total WLC"""
wlc_now = master.master_data[0].data[project_name]["Total Forecast"]
# ws.cell(row=row_num, column=6).value = wlc_now
ws.cell(row=row_num, column=5).value = wlc_now
"""WLC variance against lst quarter"""
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, IndexError):
ws.cell(row=row_num, column=6).value = "-"
"""WLC variance against baseline quarter"""
bl = master.bl_index["quarter"][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"""
vfm_cat = master.master_data[0].data[project_name][
"VfM Category single entry"
]
# 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["quarter"][project_name][2]
vfm_cat_baseline = master.master_data[bl_i].data[project_name][
"VfM Category single entry"
]
# 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"
)
current = master.master_data[0].data[project_name]["Project End Date"]
try:
last_quarter = master.master_data[1].data[project_name][
"Full Operations"
]
except IndexError:
pass
bl = master.master_data[bl_i].data[project_name]["Project End Date"]
#
# 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 < DCG_DATE:
ws.cell(row=row_num, column=9).value = "Completed"
try:
last_change = (current - last_quarter).days
if last_change == 0:
ws.cell(row=row_num, column=10).value = "-"
else:
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, UnboundLocalError):
pass
try:
bl_change = (current - bl).days
if bl_change == 0:
ws.cell(row=row_num, column=11).value = "-"
else:
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 cdg information removed
try:
ws.cell(row=row_num, column=12).value = concatenate_dates(
master.master_data[0].data[project_name]["Last date at CDG"],
DCG_DATE,
)
ws.cell(row=row_num, column=13).value = concatenate_dates(
master.master_data[0].data[project_name]["Next date at CDG"],
DCG_DATE,
)
except (KeyError, TypeError):
print(
project_name
+ " last at / next at ipdc data could not be calculated. Check data."
)
# """IPA DCA rating"""
# ipa_dca = convert_rag_text(
# master.master_data[0].data[project_name]["GMPP - IPA DCA"]
# )
# ws.cell(row=row_num, column=15).value = ipa_dca
# if ipa_dca == "None":
# ws.cell(row=row_num, column=15).value = ""
"""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, IndexError):
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["quarter"][project_name][2]
ws.cell(row=row_num, column=23).value = convert_rag_text(
master.master_data[bl_i].data[project_name]["Departmental DCA"]
)
"""list of columns with conditional formatting"""
list_columns = ["o", "q", "s", "t", "u", "w"]
"""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