"""
JIRA AI Sprint Dashboard — Streamlit App
IP (Indirect Procurement) project — Ford Jira
Deploy: Streamlit Community Cloud (free) from GitHub
"""
import os
import io
import streamlit as st
import requests
import pandas as pd
import plotly.graph_objects as go
import plotly.express as px
from datetime import datetime, timedelta
from base64 import b64encode
from functools import reduce
from urllib.parse import quote as urlquote
# ── Page config ──────────────────────────────────────────────
st.set_page_config(
page_title="IP Sprint Dashboard",
page_icon="📊",
layout="wide",
initial_sidebar_state="expanded",
)
# ── Jira credentials (env vars for Cloud Run, st.secrets for local) ──
JIRA_BASE = os.environ.get("JIRA_BASE_URL") or st.secrets["jira"]["base_url"]
JIRA_EMAIL = os.environ.get("JIRA_EMAIL") or st.secrets["jira"]["email"]
JIRA_TOKEN = os.environ.get("JIRA_API_TOKEN") or st.secrets["jira"]["api_token"]
PROJECT_KEY = "IP"
SP_FIELD = "customfield_10040"
TEAM_LABELS = [
"IndirectMDM", "I2P", "EIIM", "IMEU", "IMNA", "S2R",
"SAPIntegration", "SNO-Aurora-Data-and-Env-Mgt",
"SNO-Aurora-I2P-IT-L2", "SNO-Global-IERPAP-Support",
]
DONE_STATUSES = ["Done", "Closed Complete"]
AUTH_HEADER = {
"Authorization": f"Basic {b64encode(f'{JIRA_EMAIL}:{JIRA_TOKEN}'.encode()).decode()}",
"Content-Type": "application/json",
}
# ── Jira API helpers ─────────────────────────────────────────
def jira_get(endpoint, agile=False):
prefix = "rest/agile/1.0" if agile else "rest/api/3"
r = requests.get(f"{JIRA_BASE}/{prefix}/{endpoint}", headers=AUTH_HEADER, timeout=30)
r.raise_for_status()
return r.json()
@st.cache_data(ttl=300, show_spinner="Fetching active sprint...")
def get_active_sprint():
boards = jira_get(f"board?projectKeyOrId={PROJECT_KEY}&maxResults=50", agile=True)
for b in boards.get("values", []):
try:
sprints = jira_get(f"board/{b['id']}/sprint?state=active&maxResults=5", agile=True)
if sprints.get("values"):
return sprints["values"][0]
except Exception:
continue
return None
@st.cache_data(ttl=300, show_spinner="Fetching sprint issues...")
def get_sprint_issues(sprint_id):
all_issues = []
start_at = 0
fields = f"summary,status,assignee,priority,issuetype,{SP_FIELD},created,updated,labels,parent"
while True:
data = jira_get(
f"sprint/{sprint_id}/issue?maxResults=100&startAt={start_at}&fields={fields}",
agile=True,
)
all_issues.extend(data.get("issues", []))
start_at += 100
if start_at >= data.get("total", 0):
break
return all_issues
@st.cache_data(ttl=300, show_spinner="Fetching today's changes...")
def get_today_changes(sprint_id):
today = datetime.now().strftime("%Y-%m-%d")
all_issues = []
start_at = 0
while True:
jql = requests.utils.quote(
f'project = {PROJECT_KEY} AND sprint = {sprint_id} AND updated >= "{today}" ORDER BY updated DESC'
)
data = jira_get(
f"search/jql?jql={jql}&maxResults=100&startAt={start_at}"
f"&fields=summary,status,assignee,updated,labels&expand=changelog"
)
all_issues.extend(data.get("issues", []))
start_at += 100
if data.get("isLast", True):
break
return all_issues
# ── Data processing ──────────────────────────────────────────
def issues_to_df(issues):
rows = []
for iss in issues:
f = iss["fields"]
epic_key = ""
epic_name = ""
if f.get("parent"):
epic_key = f["parent"].get("key", "")
if f["parent"].get("fields", {}).get("summary"):
epic_name = f["parent"]["fields"]["summary"]
sp = float(f.get(SP_FIELD) or 0)
labels = f.get("labels") or []
team = "No Team"
for tl in TEAM_LABELS:
if tl in labels:
team = tl
break
status = f["status"]["name"]
cat = "Other"
if status in DONE_STATUSES:
cat = "Done"
elif status == "In Progress":
cat = "In Progress"
elif status == "Ready for acceptance":
cat = "Review"
elif status in ("To Do", "New"):
cat = "To Do"
elif status == "Canceled":
cat = "Canceled"
rows.append({
"Key": iss["key"],
"Summary": f.get("summary", ""),
"Status": status,
"Category": cat,
"Assignee": f["assignee"]["displayName"] if f.get("assignee") else "Unassigned",
"Priority": f["priority"]["name"] if f.get("priority") else "None",
"Type": f["issuetype"]["name"],
"SP": sp,
"Team": team,
"Labels": "; ".join(labels),
"Epic": epic_key,
"Epic Name": epic_name,
"Created": f.get("created", "")[:10],
"Updated": f.get("updated", "")[:16].replace("T", " "),
})
return pd.DataFrame(rows)
def build_burndown(df, sprint):
start = pd.to_datetime(sprint["startDate"]).replace(tzinfo=None).normalize()
end = pd.to_datetime(sprint["endDate"]).replace(tzinfo=None).normalize()
today = pd.Timestamp.now().normalize()
last_day = min(today, end)
total_sp = df["SP"].sum()
total_days = (end - start).days
# daily done / planned lookups
daily_done = {}
daily_planned = {}
for _, row in df.iterrows():
sp = row["SP"]
if sp > 0:
created = pd.to_datetime(row["Created"]).normalize()
added = max(created, start)
k = added.strftime("%Y-%m-%d")
daily_planned[k] = daily_planned.get(k, 0) + sp
if row["Category"] == "Done" and sp > 0 and row["Updated"]:
done_d = pd.to_datetime(row["Updated"]).normalize()
if done_d < start:
done_d = start
k = done_d.strftime("%Y-%m-%d")
daily_done[k] = daily_done.get(k, 0) + sp
dates, ideal, actual, planned = [], [], [], []
cum_done = 0
cum_planned = 0
day = start
idx = 0
while day <= end:
ds = day.strftime("%Y-%m-%d")
label = day.strftime("%b %d")
dates.append(label)
cum_planned += daily_planned.get(ds, 0)
ideal_val = round(total_sp - (total_sp * idx / total_days), 1) if total_days > 0 else 0
ideal.append(ideal_val)
if day <= last_day:
cum_done += daily_done.get(ds, 0)
actual.append(round(total_sp - cum_done, 1))
planned.append(cum_planned)
else:
actual.append(None)
planned.append(None)
day += timedelta(days=1)
idx += 1
return dates, ideal, actual, planned, total_sp
# ── Main app ─────────────────────────────────────────────────
def main():
# ── Global CSS: pill-shaped tabs ─────────────────────────
_pill_css = ''
st.markdown(_pill_css, unsafe_allow_html=True)
# Fetch data
sprint = get_active_sprint()
if not sprint:
st.error("No active sprint found in IP project.")
return
issues = get_sprint_issues(sprint["id"])
df = issues_to_df(issues)
changes_issues = get_today_changes(sprint["id"])
changed_today_count = len(changes_issues)
# Sprint info
s_name = sprint["name"]
s_start = sprint["startDate"][:10]
s_end = sprint["endDate"][:10]
days_left = max(0, (pd.to_datetime(s_end).date() - datetime.now().date()).days)
# ── Sidebar filters ──────────────────────────────────────
st.sidebar.title("Filters")
st.sidebar.caption(f"Last updated: {datetime.now().strftime('%b %d, %Y %H:%M')} UTC")
st.sidebar.markdown('', unsafe_allow_html=True)
teams = ["All"] + sorted(df["Team"].unique().tolist())
sel_team = st.sidebar.selectbox("Team", teams)
assignees = ["All"] + sorted(df["Assignee"].unique().tolist())
sel_assignee = st.sidebar.selectbox("Assignee", assignees)
statuses = ["All"] + sorted(df["Category"].unique().tolist())
sel_status = st.sidebar.selectbox("Status Category", statuses)
epics = ["All"] + sorted(df[df["Epic Name"] != ""]["Epic Name"].unique().tolist())
sel_epic = st.sidebar.selectbox("Epic", epics)
# Apply filters
fdf = df.copy()
if sel_team != "All":
fdf = fdf[fdf["Team"] == sel_team]
if sel_assignee != "All":
fdf = fdf[fdf["Assignee"] == sel_assignee]
if sel_status != "All":
fdf = fdf[fdf["Category"] == sel_status]
if sel_epic != "All":
fdf = fdf[fdf["Epic Name"] == sel_epic]
# ── Sidebar: Export & Email ───────────────────────────────
st.sidebar.markdown("---")
st.sidebar.markdown("**Export**")
# Active filter summary
active_filters = []
if sel_team != "All":
active_filters.append(f"Team: {sel_team}")
if sel_assignee != "All":
active_filters.append(f"Assignee: {sel_assignee}")
if sel_status != "All":
active_filters.append(f"Status: {sel_status}")
if sel_epic != "All":
active_filters.append(f"Epic: {sel_epic}")
filter_desc = ", ".join(active_filters) if active_filters else "All Data (no filters)"
# Compute stats for email/export summary
exp_total = len(fdf)
exp_done = len(fdf[fdf["Category"] == "Done"])
exp_sp = fdf["SP"].sum()
exp_done_sp = fdf[fdf["Category"] == "Done"]["SP"].sum()
exp_ip_sp = fdf[fdf["Category"] == "In Progress"]["SP"].sum()
exp_rem_sp = exp_sp - exp_done_sp - exp_ip_sp
exp_comp = round(exp_done_sp / exp_sp * 100, 1) if exp_sp > 0 else 0
# ── Download (single button, format selector) ──
dl_format = st.sidebar.selectbox("Download Format", ["CSV", "Excel"], key="dl_fmt")
export_cols = ["Key", "Summary", "Status", "Category", "Assignee", "SP", "Priority", "Type", "Team", "Epic", "Epic Name", "Created", "Updated"]
if dl_format == "CSV":
csv_data = fdf[export_cols].to_csv(index=False).encode("utf-8")
st.sidebar.download_button("Download", csv_data, file_name=f"sprint-{s_name}-{datetime.now().strftime('%Y%m%d')}.csv", mime="text/csv", key="dl_btn")
else:
excel_buf = io.BytesIO()
with pd.ExcelWriter(excel_buf, engine="openpyxl") as writer:
fdf[export_cols].to_excel(writer, index=False, sheet_name="Sprint Issues")
summary_rows = [("Sprint", s_name), ("Period", f"{s_start} to {s_end}"), ("Filters", filter_desc), ("Total Issues", exp_total), ("Done Issues", exp_done), ("Total SP", exp_sp), ("Done SP", exp_done_sp), ("In Progress SP", exp_ip_sp), ("Remaining SP", exp_rem_sp), ("Completion %", f"{exp_comp}%"), ("Dashboard URL", "https://fordai-sprint-dashboard.hf.space")]
pd.DataFrame(summary_rows, columns=["Metric", "Value"]).to_excel(writer, index=False, sheet_name="Summary")
user_summary = fdf.groupby("Assignee").agg(Planned=("SP", "sum"), Done=("SP", lambda x: x[fdf.loc[x.index, "Category"] == "Done"].sum()), InProgress=("SP", lambda x: x[fdf.loc[x.index, "Category"] == "In Progress"].sum())).reset_index()
user_summary["Remaining"] = user_summary["Planned"] - user_summary["Done"] - user_summary["InProgress"]
user_summary.sort_values("Planned", ascending=False).to_excel(writer, index=False, sheet_name="SP by User")
st.sidebar.download_button("Download", excel_buf.getvalue(), file_name=f"sprint-{s_name}-{datetime.now().strftime('%Y%m%d')}.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", key="dl_btn")
# ── Ford header banner with Refresh ─────────────────────────
hdr_left, hdr_right = st.columns([6, 1])
with hdr_left:
ford_logo = ''
header_html = ''
header_html += ford_logo
header_html += '
JIRA Sprint Dashboard
'
header_html += '
Indirect Procurement
'
header_html += '
'
kpi_html += f'
SPRINT
{s_name}
{s_start} → {s_end}
'
kpi_html += f'
DAYS REMAINING
{days_left}
Sprint deadline
'
kpi_html += f'
TOTAL ISSUES
{len(fdf)}
{done_issues} done • {completion}% complete
'
kpi_html += f'
CHANGED TODAY
{changed_today_count}
Issues updated today
'
kpi_html += '
'
f'
{s}
'
f'
'
f'
{cnt}
'
f'
{pct}%
'
html += '
STATUS DISTRIBUTION
'
html += ''.join(bars)
html += '
Behind by {behind_sp:g} SP vs ideal
', unsafe_allow_html=True)
elif behind_sp < 0:
st.markdown(f'Ahead by {abs(behind_sp):g} SP vs ideal
', unsafe_allow_html=True)
# ── Tab 2: Story Points ─────────────────────────────────────
with tab2:
# Compute per-user SP data
user_sp = fdf.groupby("Assignee").agg(
PlannedSP=("SP", "sum"),
DoneSP=("SP", lambda x: x[fdf.loc[x.index, "Category"] == "Done"].sum()),
InProgressSP=("SP", lambda x: x[fdf.loc[x.index, "Category"] == "In Progress"].sum()),
).reset_index()
user_sp["RemainingSP"] = user_sp["PlannedSP"] - user_sp["DoneSP"] - user_sp["InProgressSP"]
user_sp["SPpct"] = (user_sp["DoneSP"] / user_sp["PlannedSP"] * 100).fillna(0).round(0).astype(int)
user_sp = user_sp.sort_values("PlannedSP", ascending=False)
sp_left, sp_right = st.columns([3, 2])
# ── Left: SP by User table ───────────────────────────
with sp_left:
# Build HTML table rows — single-line to avoid Streamlit code-block rendering
table_rows = []
for _, row in user_sp.iterrows():
p = row["PlannedSP"]
d = row["DoneSP"]
ip = row["InProgressSP"]
rem = row["RemainingSP"]
pct = row["SPpct"]
d_w = round(d / p * 100) if p > 0 else 0
ip_w = round(ip / p * 100) if p > 0 else 0
pct_color = "#22c55e" if pct >= 75 else ("#f59e0b" if pct >= 40 else "#7c83ff")
tr = (
f''
f'| {row["Assignee"]} | '
f' | '
f'{p:g} | '
f'{d:g} | '
f'{ip:g} | '
f'{rem:g} | '
f'{pct}% | '
f'
'
)
table_rows.append(tr)
# Build full table as single-line concatenated HTML
sp_html = ''
sp_html += '
'
sp_html += 'STORY POINTS BY USER
'
sp_html += '
'
sp_html += '
'
sp_html += ''
sp_html += '| USER | '
sp_html += 'SP
BAR | '
sp_html += 'PLANNED
SP | '
sp_html += 'DONE
SP | '
sp_html += 'IN PROGRESS
SP | '
sp_html += 'REMAINING
SP | '
sp_html += 'SP % | '
sp_html += '
'
sp_html += ''.join(table_rows)
sp_html += '
'
sum_html += '
STORY POINTS SUMMARY
'
sum_html += f'
{all_total_sp:g}
'
sum_html += '
Total Story Points
'
sum_html += '
'
sum_html += f'
'
sum_html += f'
{all_ip_sp:g}
In Progress SP
'
sum_html += f'
{all_rem_sp:g}
Remaining SP
'
sum_html += '
'
sum_html += f'
'
sum_html += f'
0%{sp_comp}% Complete100%
'
sum_html += '
'
sum_html += 'Done'
sum_html += 'In Progress'
sum_html += 'Remaining'
sum_html += '