# Effort Estimator - final UI updates per user's last requests
# If needed in Colab uncomment:
# !pip install --quiet gradio openpyxl pandas
import pandas as pd
import gradio as gr
import warnings
warnings.filterwarnings('ignore')
# ---------------- Load data ----------------
data_path = "Effort Estimation Sample Data v0.1.xlsx"
sheet1 = pd.read_excel(data_path, sheet_name="Efforts Breakdown")
sheet2 = pd.read_excel(data_path, sheet_name="Complexity Matrix")
# ---------------- Constants & UI colors ----------------
phase_display_names = {
"Design effort (days)": "Design",
"Build effort (days)": "Build",
"Testing effort (days)": "Testing",
"Post Go Live (days)": "Go Live"
}
EY_COLORS = {
"black": "#0b0b0b",
"white": "#FFFFFF",
"turbo": "#FFE600",
"sonic_silver": "#797878",
"sizzling_sunrise": "#FFDB00"
}
competencies = sorted(list(sheet1["Competency"].dropna().unique()))
FIXED_COMPLEXITIES = ["L", "M", "H"]
MAX_SUBMODULES = 30
# ---------------- Helper functions ----------------
def get_modules(competency):
if not competency:
return []
return list(sheet1[sheet1["Competency"] == competency]["Module"].dropna().unique())
def get_submodules(competency, modules):
if not competency or not modules:
return []
all_pairs = []
for module in modules:
submodules = list(sheet1[
(sheet1["Competency"] == competency) &
(sheet1["Module"] == module)
]["Sub - Module"].dropna().unique())
for sm in submodules:
all_pairs.append((module, sm))
return all_pairs
def get_complexities_for_submodule(competency, module, submodule):
return FIXED_COMPLEXITIES
def get_phases_for_submodule(competency, module, submodule):
if not competency or not module or not submodule:
return []
filtered = sheet1[
(sheet1["Competency"] == competency) &
(sheet1["Module"] == module) &
(sheet1["Sub - Module"] == submodule)
]
if filtered.empty:
return []
phases = []
for col, disp in phase_display_names.items():
if col in filtered.columns:
col_vals = filtered[col].dropna()
if not col_vals.empty:
try:
numeric_mask = col_vals.astype(float) > 0
if numeric_mask.any():
phases.append(disp)
except:
phases.append(disp)
return phases
def create_submodule_details_html(competency, modules, module_submodule_pairs):
if not competency or not modules or not module_submodule_pairs:
return ""
html = f"""
📋 Sub-Module Details (Available)
"""
grouped = {}
for module, sm in module_submodule_pairs:
grouped.setdefault(module, []).append(sm)
for module, sms in grouped.items():
html += f"
Module: {module}"
for sm in sms:
complexities = get_complexities_for_submodule(competency, module, sm)
phases = get_phases_for_submodule(competency, module, sm)
html += f"
"
html += f"{sm}
Complexities: {', '.join(complexities)} | Phases: {', '.join(phases) if phases else 'None'}"
html += "
"
html += "
"
html += "
"
return html
def calculate_effort_per_submodule(competency, selected_modules, selected_sm_identifiers, submodule_counts, per_submodule_complexities, per_submodule_phases, buffer_percent):
if not competency:
return "❌ Please select a Competency."
if not selected_modules:
return "❌ Please select at least one Module."
if not selected_sm_identifiers:
return "❌ Please select at least one Sub-Module."
overall_complexities = set()
overall_phases = set()
for smid in selected_sm_identifiers:
cs = per_submodule_complexities.get(smid, [])
ps = per_submodule_phases.get(smid, [])
overall_complexities.update(cs)
overall_phases.update(ps)
if not overall_complexities:
return "❌ Please select at least one Complexity Level for the chosen sub-modules."
if not overall_phases:
return "❌ Please select at least one Phase for the chosen sub-modules."
total_effort = 0.0
breakdown_rows = []
for smid in selected_sm_identifiers:
try:
module, submodule = smid.split("||", 1)
except:
continue
count = int(submodule_counts.get(smid, 1))
complexities = per_submodule_complexities.get(smid, [])
phases = per_submodule_phases.get(smid, [])
for complexity in complexities:
filtered_data = sheet1[
(sheet1["Competency"] == competency) &
(sheet1["Module"] == module) &
(sheet1["Sub - Module"] == submodule)
]
if filtered_data.empty:
continue
weight_rows = sheet2[
(sheet2["Competency"] == competency) &
(sheet2["Complexity"] == complexity)
]
weight = float(weight_rows["Weightage"].values[0]) if not weight_rows.empty else 1.0
combination_effort = 0.0
phase_details = []
for phase in phases:
phase_col = next((col for col, disp in phase_display_names.items() if disp == phase), None)
if phase_col and phase_col in filtered_data.columns:
phase_effort = float(filtered_data[phase_col].sum()) * weight
phase_total = phase_effort * count
combination_effort += phase_total
phase_details.append(f"{phase}: {phase_effort:.1f}x{count} = {phase_total:.1f} days")
if combination_effort > 0:
total_effort += combination_effort
breakdown_rows.append({
'module': module,
'submodule': submodule,
'complexity': complexity,
'count': count,
'phases': ', '.join(phases),
'effort': round(combination_effort, 2),
'phase_details': ' | '.join(phase_details)
})
if total_effort == 0:
return "❌ No effort data found for the selected combinations."
final_effort = total_effort * (1 + buffer_percent / 100)
# build results HTML
# summary block uses white text on dark background to be visible
summary_html = f"""
Competency: {competency}
Selected Modules/Sub-Modules: {', '.join(selected_sm_identifiers)}
Buffer Percentage: {buffer_percent}%
"""
table_html = f"""
Effort Breakdown
| Module |
Sub Module |
Complexity |
Count |
Phases |
Effort (days) |
"""
# all rows except header: white background for clarity
for row in breakdown_rows:
table_html += f"""
| {row['module']} |
{row['submodule']} |
{row['complexity']} |
{row['count']} |
{row['phases']} |
{row['effort']} |
"""
table_html += "
"
totals_html = f"""
| Total Efforts (before buffer): | {total_effort:.2f} days |
| Buffer Applied ({buffer_percent}%): | + {total_effort * buffer_percent/100:.2f} days |
| Total Efforts (with buffer): | {final_effort:.2f} days |
"""
# detailed phase breakdown area - white text on dark background
phase_html = ""
phase_html += "
Detailed Phase Breakdown:
"
for row in breakdown_rows:
prefix = f"{row['module']} - {row['submodule']} ({row['complexity']}):"
phase_html += (
f"
"
f"{prefix} "
f"{row['phase_details']}
"
)
phase_html += "
{summary_html}
{table_html}
{totals_html}
{phase_html}
Results will open on a new page after calculation.
")
# RESULTS PAGE (hidden initially) - no extra header
with gr.Column(visible=False, elem_classes="panel") as page_results:
results = gr.HTML("", label="Estimation Results")
results_back = gr.Button("⬅️ Back to Inputs", elem_classes="ey-button")
# EVENTS
def on_competency_change(comp):
return gr.update(choices=get_modules(comp), value=[])
competency.change(fn=on_competency_change, inputs=competency, outputs=modules)
def on_modules_change(comp, mods):
module_sm_pairs = get_submodules(comp, mods) if mods else []
updates = []
for i in range(MAX_SUBMODULES):
if i < len(module_sm_pairs):
module, sm_name = module_sm_pairs[i]
identifier = f"{module}||{sm_name}"
updates.append(gr.update(visible=True))
updates.append(gr.update(label=f"{module} \u2192 {sm_name}", value=False))
updates.append(gr.update(value=identifier, visible=False))
updates.append(gr.update(visible=True, value=1))
comp_choices = FIXED_COMPLEXITIES
phase_choices = get_phases_for_submodule(comp, module, sm_name)
updates.append(gr.update(choices=comp_choices, value=None))
updates.append(gr.update(choices=phase_choices, value=[]))
else:
updates.append(gr.update(visible=False))
updates.append(gr.update(label="", value=False))
updates.append(gr.update(value="", visible=False))
updates.append(gr.update(visible=False, value=1))
updates.append(gr.update(choices=[], value=None))
updates.append(gr.update(choices=[], value=[]))
details_html = create_submodule_details_html(comp, mods, module_sm_pairs) if (comp and mods and module_sm_pairs) else ""
return updates + [details_html]
row_outputs = []
for i in range(MAX_SUBMODULES):
row_outputs += [per_sm_row[i], per_sm_checkbox[i], per_sm_name[i], per_sm_count[i], per_sm_complexities[i], per_sm_phases[i]]
modules.change(
fn=on_modules_change,
inputs=[competency, modules],
outputs=row_outputs + [submodule_details]
)
def collect_and_compute(comp, mods, *args_and_buffer):
total_expected = MAX_SUBMODULES * 5 + 1
if len(args_and_buffer) != total_expected:
return ("❌ Internal: unexpected inputs passed to calculator.", gr.update(visible=True), gr.update(visible=False))
row_args = args_and_buffer[:-1]
buffer_val = args_and_buffer[-1]
selected_sm_identifiers = []
submodule_counts = {}
per_sub_comps = {}
per_sub_phases = {}
for i in range(MAX_SUBMODULES):
base = i * 5
chk = row_args[base + 0]
identifier = row_args[base + 1]
cnt = row_args[base + 2]
comps_sel_raw = row_args[base + 3]
phases_sel = row_args[base + 4] or []
if comps_sel_raw is None or comps_sel_raw == "":
comps_sel = []
else:
comps_sel = [comps_sel_raw]
if chk:
if not identifier:
continue
selected_sm_identifiers.append(identifier)
try:
submodule_counts[identifier] = int(cnt) if (cnt is not None and str(cnt).strip() != "") else 1
except:
submodule_counts[identifier] = 1
per_sub_comps[identifier] = comps_sel
per_sub_phases[identifier] = phases_sel
html = calculate_effort_per_submodule(comp, mods, selected_sm_identifiers, submodule_counts, per_sub_comps, per_sub_phases, buffer_val)
# show results page (hide inputs)
return (html, gr.update(visible=False), gr.update(visible=True))
calculation_inputs = [competency, modules]
for i in range(MAX_SUBMODULES):
calculation_inputs += [per_sm_checkbox[i], per_sm_name[i], per_sm_count[i], per_sm_complexities[i], per_sm_phases[i]]
calculation_inputs += [buffer_percent]
calculate_btn.click(fn=collect_and_compute, inputs=calculation_inputs, outputs=[results, page_inputs, page_results])
def back_to_inputs():
return (gr.update(visible=True), gr.update(visible=False), "")
results_back.click(fn=back_to_inputs, inputs=[], outputs=[page_inputs, page_results, results])
# LOGIN BUTTON EVENT (popup)
def on_login_click(username, password):
success, _ = login(username, password)
if success:
return gr.update(visible=True), gr.update(visible=False, value=""), gr.update(visible=False)
else:
err_html = f"❌ Invalid username or password
"
return gr.update(visible=False), gr.update(visible=True, value=err_html), gr.update(visible=True)
login_btn.click(
fn=on_login_click,
inputs=[username_input, password_input],
outputs=[main_app_panel, login_msg_html, login_panel]
)
demo.launch(share=True, debug=True)
