import os
import streamlit as st
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from datetime import datetime, timedelta
import time
from streamlit_autorefresh import st_autorefresh
# ==========================================
# 0. Password Authentication Function
# ==========================================
def check_password():
"""Returns True if the user had the correct password."""
def password_entered():
"""Checks whether a password entered by the user is correct."""
# Retrieves password from Hugging Face Space Secrets (defaults to admin if not found)
correct_password = os.environ.get("APP_PASSWORD", "admin")
if st.session_state["password"] == correct_password:
st.session_state["password_correct"] = True
del st.session_state["password"] # Clear session state for security
else:
st.session_state["password_correct"] = False
if "password_correct" not in st.session_state:
# Custom Dark Glassmorphism Login Box Styling
st.markdown("""
""", unsafe_allow_html=True)
cols = st.columns([1, 2, 1])
with cols[1]:
st.markdown('', unsafe_allow_html=True)
st.image("https://upload.wikimedia.org/wikipedia/commons/2/2b/Valeo_Logo.svg", width=150)
st.subheader("🔐 Restricted Access Production Monitor")
st.text_input("Please enter dashboard password:", type="password", on_change=password_entered, key="password")
if "password_correct" in st.session_state and not st.session_state["password_correct"]:
st.error("😕 Password incorrect. Please check your credentials.")
st.markdown('
', unsafe_allow_html=True)
return False
return True
# --- Trigger Password Check Gateway ---
if not check_password():
st.stop() # Stop processing dashboard if user isn't logged in
# ==========================================
# 1. Page Configuration & Auto-Refresh
# ==========================================
st.set_page_config(
page_title="Valeo | TASTOS FLEXILINE_1 CHO2",
page_icon="https://upload.wikimedia.org/wikipedia/commons/2/2b/Valeo_Logo.svg",
layout="wide",
initial_sidebar_state="expanded"
)
# Auto-refresh every 30 seconds
st_autorefresh(interval=30 * 1000, limit=None, key="fproduction_counter")
st.markdown("""
""", unsafe_allow_html=True)
now_str = datetime.now().strftime("%H:%M:%S")
st.markdown(f'', unsafe_allow_html=True)
col1, col2 = st.columns([1, 4])
with col1: st.image("https://upload.wikimedia.org/wikipedia/commons/2/2b/Valeo_Logo.svg", width=220)
with col2: st.title("TASTOS FLEXILINE_1 CHO2 Dashboard")
# ==========================================
# 2. Station Configuration Constants
# ==========================================
ST_TABLES = [
"dbo.ST010_2", "dbo.ST020_1", "dbo.ST020_2", "dbo.ST020_3",
"dbo.ST030_1", "dbo.ST030_2", "dbo.ST040_1", "dbo.ST040_2",
"dbo.ST050_2", "dbo.ST060_1", "dbo.ST060_2", "dbo.ST070_1",
"dbo.ST070_2", "dbo.ST070_3", "dbo.ST080_1", "dbo.ST080_2",
"dbo.ST080_3", "dbo.ST090_1", "dbo.ST090_2", "dbo.ST090_3",
"dbo.ST100_1", "dbo.ST100_2", "dbo.ST100_3", "dbo.ST110_1", "dbo.ST110_2"
]
STATION_CT_MAPPING = {
"ST010": 21.0, "ST020": 21.0, "ST030": 21.0, "ST040": 21.0,
"ST050": 21.0, "ST060": 21.0, "ST070": 21.0, "ST080": 21.0,
"ST090": 21.0, "ST100": 21.0, "ST110": 21.0
}
# ==========================================
# 3. Data Loading & Reshaping (Deduplicated & Clean Pallets)
# ==========================================
def get_direct_url(url):
if not url: return None
ts = int(time.time())
if "drive.google.com" in url:
try:
file_id = url.split("/d/")[1].split("/")[0]
return f"https://drive.google.com/uc?export=download&id={file_id}&t={ts}"
except: return url
elif "docs.google.com/spreadsheets" in url:
return url.split('/edit')[0] + f"/export?format=csv&t={ts}"
return url
@st.cache_data(ttl=30)
def fetch_full_integrated_data(view_mode, sel_date, shift_mode):
url = os.environ.get("ASSY_URL")
if not url: return pd.DataFrame()
direct_link = get_direct_url(url)
df = None
encodings = ['utf-8-sig', 'gbk', 'gb18030', 'utf-8']
for enc in encodings:
try:
df = pd.read_csv(direct_link, sep=None, engine='python', on_bad_lines='skip', encoding=enc)
df.columns = df.columns.str.strip()
break
except: continue
if df is None or df.empty:
return pd.DataFrame()
# Standardize HousingCode column name dynamically
target_name = 'Code产品条码'
possible_cols = [c for c in df.columns if target_name in c or 'Code' in str(c) or '条码' in str(c)]
housing_col = possible_cols[0] if possible_cols else 'HousingCode'
if housing_col in df.columns:
df = df.rename(columns={housing_col: 'HousingCode'})
df['HousingCode'] = df['HousingCode'].astype(str).str.strip().str.upper()
df = df.dropna(subset=['HousingCode'])
# Re-synthesize separate station timestamps to map smoothly with layout analysis structure
for table in ST_TABLES:
t_clean = table.replace("dbo.", "")
t_col = next((c for c in df.columns if t_clean in c and ('time' in c.lower() or '时间' in c)), None)
if t_col:
df[f'{t_clean}_EndTime'] = pd.to_datetime(df[t_col], errors='coerce')
st_start_col = next((c for c in df.columns if t_clean in c and ('start' in c.lower() or 'starttime' in c)), None)
if st_start_col:
df[f'{t_clean}_StartTime'] = pd.to_datetime(df[st_start_col], errors='coerce')
# --- REVISED: Safe Pallet Reconstruction Logic (Targets exact numeric Jig numbers) ---
jig_no_cols = [c for c in df.columns if 'jig_no' in c.lower() or '夹具编号' in c]
if jig_no_cols:
# Clean decimals by converting numeric backfills to crisp plain integer string representations
merged_jigs = df[jig_no_cols].apply(pd.to_numeric, errors='coerce').bfill(axis=1)
if not merged_jigs.empty:
df['PalletNo'] = merged_jigs.iloc[:, 0].dropna().apply(lambda x: str(int(x)))
else:
df['PalletNo'] = np.nan
else:
df['PalletNo'] = np.nan
# Calculate overall row timestamp using the max known process end time
time_cols_all = [c for c in df.columns if '_EndTime' in c]
if time_cols_all:
df['LogTime'] = df[time_cols_all].max(axis=1)
else:
df['LogTime'] = pd.to_datetime(datetime.now())
df['LogTime'] = pd.to_datetime(df['LogTime'])
# Date Window Selection & Shift Filtering Logic
if view_mode == "Monthly (Days)":
start_dt = datetime.combine(sel_date.replace(day=1), datetime.min.time())
if sel_date.month == 12:
end_dt = start_dt.replace(year=sel_date.year + 1, month=1)
else:
end_dt = start_dt.replace(month=sel_date.month + 1)
df = df[(df['LogTime'] >= start_dt) & (df['LogTime'] < end_dt)]
if shift_mode == "Day (09-21)":
df = df[(df['LogTime'].dt.hour >= 9) & (df['LogTime'].dt.hour < 21)]
elif shift_mode == "Night (21-09)":
df = df[(df['LogTime'].dt.hour >= 21) | (df['LogTime'].dt.hour < 9)]
else: # Daily Mode
if shift_mode == "Day (09-21)":
start_dt = datetime.combine(sel_date, datetime.min.time()).replace(hour=9)
end_dt = start_dt + timedelta(hours=12)
elif shift_mode == "Night (21-09)":
start_dt = datetime.combine(sel_date, datetime.min.time()).replace(hour=21)
end_dt = start_dt + timedelta(hours=12)
else:
start_dt = datetime.combine(sel_date, datetime.min.time())
end_dt = start_dt + timedelta(days=1)
df = df[(df['LogTime'] >= start_dt) & (df['LogTime'] < end_dt)]
# Clean up double counting duplicates by keeping only the latest physical component sequence
df = df.sort_values('LogTime').drop_duplicates('HousingCode', keep='last')
return df
# ==========================================
# 4. Sidebar Configuration Menu
# ==========================================
with st.sidebar:
st.image("https://upload.wikimedia.org/wikipedia/commons/2/2b/Valeo_Logo.svg", width=150)
st.markdown("### ⚙️ DASHBOARD CONFIG")
view_mode = st.radio("🤔 View Mode:", ["Daily (Hours)", "Monthly (Days)"])
sel_date = st.date_input("📅 Select Date:", value=datetime.now().date())
shift_mode = st.selectbox("🕒 Shift:", ["All Day", "Day (09-21)", "Night (21-09)"])
st.markdown("---")
st.markdown("### 📊 CHOOSE DASHBOARD VIEW")
analysis_menu = st.toggle("📈 STATION RUNTIME TIMELINE ANALYSIS", value=False)
raw_df = fetch_full_integrated_data(view_mode, sel_date, shift_mode)
if not raw_df.empty:
res_cols = [c for c in raw_df.columns if 'Final_Result' in c or '总结果' in c]
process_cols = [c for c in res_cols if 'ST110' not in c]
st110_cols = [c for c in res_cols if 'ST110' in c]
is_ng_process = raw_df[process_cols].apply(lambda x: x.astype(str).str.strip().str.upper() == 'NG').any(axis=1)
raw_df['Summary_Result'] = np.where(is_ng_process, 'NG', 'OK')
if st110_cols:
ng_st110_count = raw_df[st110_cols].apply(lambda x: x.astype(str).str.strip().str.upper() == '--').any(axis=1).sum()
else:
ng_st110_count = 0
if 'LogTime' in raw_df.columns and not raw_df['LogTime'].isna().all():
raw_df['DateTime'] = pd.to_datetime(raw_df['LogTime'])
else:
time_cols = [c for c in raw_df.columns if '_EndTime' in str(c)]
if time_cols:
raw_df['DateTime'] = raw_df[time_cols].bfill(axis=1).iloc[:, 0]
else:
raw_df['DateTime'] = pd.to_datetime(datetime.now())
raw_df['DateTime'] = pd.to_datetime(raw_df['DateTime']).fillna(datetime.combine(sel_date, datetime.min.time()))
raw_df['Hour'] = raw_df['DateTime'].dt.hour
raw_df['Day'] = raw_df['DateTime'].dt.day
total = len(raw_df)
ng_total = (raw_df['Summary_Result'] == 'NG').sum()
trp_rate = ((total - ng_total) / total * 100) if total > 0 else 0
avg_ct = 0
if total > 1:
times = raw_df['DateTime'].sort_values()
diffs = times.diff().dt.total_seconds().dropna()
valid_diffs = diffs[(diffs > 1) & (diffs < 500)]
if not valid_diffs.empty: avg_ct = valid_diffs.mean()
# ==========================================
# VIEW MODE A: STANDARD LINE OVERVIEW
# ==========================================
if not analysis_menu:
header_text = sel_date.strftime('%Y-%m') if view_mode == "Monthly (Days)" else sel_date
st.markdown(f"", unsafe_allow_html=True)
r1, r2, r3, r4 = st.columns(4)
r1.metric("TOTAL ASSY", f"{total:,} pcs")
r2.metric("TOTAL NG (PROD)", f"{ng_total:,} pcs")
r3.metric("ASSY NG", f"{ng_total:,} pcs")
r4.metric("ST110 (LOAD to EOLT)", f"{ng_st110_count:,} pcs")
r5, r6, r7, r8 = st.columns(4)
r5.metric("TRP% [TARGET:85%]", f"{trp_rate:.2f} %")
r6.metric("DLE%", "85.00 %")
r7.metric("AVG CYCLE TIME", f"{avg_ct:.1f} sec" if avg_ct > 0 else "---")
r8.metric("NG RATIO %", f"{(ng_total/total*100):.2f} %" if total > 0 else "0.00 %")
st.markdown("", unsafe_allow_html=True)
if view_mode == "Monthly (Days)":
x_col = 'Day'
unique_days = raw_df['Day'].dropna().unique()
target_order = sorted([int(d) for d in unique_days]) if len(unique_days) > 0 else list(range(1, 32))
else:
x_col = 'Hour'
if shift_mode == "Night (21-09)":
target_order = [21, 22, 23, 0, 1, 2, 3, 4, 5, 6, 7, 8]
elif shift_mode == "Day (09-21)":
target_order = list(range(9, 21))
else:
target_order = list(range(24))
if not raw_df.empty and len(raw_df.dropna(subset=[x_col])) > 0:
grp = raw_df.groupby([x_col, 'Summary_Result']).size().unstack(fill_value=0).reset_index()
for c in ['OK', 'NG']:
if c not in grp.columns: grp[c] = 0
grp[x_col] = grp[x_col].astype(int)
grp[x_col] = pd.Categorical(grp[x_col], categories=target_order, ordered=True)
grp = grp.sort_values(x_col).dropna(subset=[x_col])
x_axis_labels = [f"{int(i):02d}:00" if view_mode == "Daily (Hours)" else f"Day {int(i)}" for i in grp[x_col]]
fig_t = go.Figure()
fig_t.add_trace(go.Bar(x=x_axis_labels, y=grp['OK'], name='OK', marker_color='#2ecc71'))
fig_t.add_trace(go.Bar(x=x_axis_labels, y=grp['NG'], name='NG', marker_color='#e74c3c'))
fig_t.update_layout(template="plotly_dark", barmode='stack', height=400, paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)', xaxis=dict(type='category'))
st.plotly_chart(fig_t, use_container_width=True)
else:
st.info("ℹ️ No production trend data available for this specific period calculation selection.")
st.markdown("", unsafe_allow_html=True)
# --- REVISED: Accurate Pallet Metrics Map and Sorter Setup ---
if 'PalletNo' in raw_df.columns:
active_pallets = raw_df['PalletNo'].dropna().astype(str).str.strip()
active_pallets = active_pallets[(active_pallets != '') & (active_pallets != 'nan')]
# Extract clean unique integers avoiding character slice logic errors
unique_pallets = active_pallets.unique()
active_pallets_sorted = sorted(list(unique_pallets), key=lambda x: int(x) if x.isdigit() else x)
pallet_count = len(active_pallets_sorted)
pallet_list_str = ", ".join(active_pallets_sorted) if pallet_count > 0 else "No active pallets detected"
else:
pallet_count = 0
pallet_list_str = "Pallet column not found in database"
p_stat1, p_stat2 = st.columns([1, 3])
with p_stat1: st.metric("PALLETS ON LINE", f"{pallet_count} Units")
with p_stat2:
st.markdown(f"""
ACTIVE PALLET NUMBERS
{pallet_list_str}
""", unsafe_allow_html=True)
st.write("")
p_col1, p_col2 = st.columns(2)
with p_col1:
st.subheader("Station NG Ranking")
if ng_total > 0:
st_ng = (raw_df[raw_df['Summary_Result'] == 'NG'][process_cols].apply(lambda x: x.astype(str).str.strip().str.upper() == 'NG')).sum()
df_p = st_ng.reset_index().rename(columns={'index': 'Station', 0: 'Count'}).sort_values('Count', ascending=False)
df_p = df_p[df_p['Count'] > 0]
if not df_p.empty:
df_p['Station'] = df_p['Station'].str.replace('_Final_Result', '').str.replace('总结果', '')
fig_st = px.bar(df_p, x='Station', y='Count', text='Count', color_discrete_sequence=['#e74c3c'], template="plotly_dark")
st.plotly_chart(fig_st, use_container_width=True)
else: st.info("No explicit failure records flagged inside station result rows.")
else: st.success("✅ No Process NG found.")
with p_col2:
st.subheader("Pallet NG Distribution")
if 'PalletNo' in raw_df.columns and ng_total > 0:
chart_df = raw_df[raw_df['Summary_Result'] == 'NG'].copy()
# --- REVISED: Group directly on clean unsliced integers ---
chart_df['Pallet_Clean'] = chart_df['PalletNo'].dropna().astype(str).str.strip()
pallet_ng = chart_df.groupby('Pallet_Clean').size().reset_index(name='NG_Count').sort_values('NG_Count', ascending=False)
if not pallet_ng.empty:
fig_pal = px.bar(pallet_ng, x='Pallet_Clean', y='NG_Count', text='NG_Count', color='NG_Count', color_continuous_scale='Reds', template="plotly_dark")
fig_pal.update_layout(xaxis_title="Pallet Number")
st.plotly_chart(fig_pal, use_container_width=True)
else: st.info("No Pallet data linked to NG cases.")
else: st.info("Waiting for NG data or Pallet column...")
# ==========================================
# VIEW MODE B: SEPARATE STATION RUNTIME TIMELINE ANALYSIS
# ==========================================
else:
st.markdown("", unsafe_allow_html=True)
timeline_data = []
for table in ST_TABLES:
t_clean = table.replace("dbo.", "")
end_col = f"{t_clean}_EndTime"
start_col = f"{t_clean}_StartTime"
st_prefix = t_clean.split('_')[0]
current_target_ct = STATION_CT_MAPPING.get(st_prefix, 15.0)
if end_col in raw_df.columns:
sub_df = raw_df.dropna(subset=[end_col]).copy()
if not sub_df.empty:
if start_col in sub_df.columns and not sub_df[start_col].isna().all():
sub_df['Duration'] = (sub_df[end_col] - sub_df[start_col]).dt.total_seconds()
else:
sub_df = sub_df.sort_values(end_col)
sub_df['Duration'] = sub_df[end_col].diff().dt.total_seconds()
def categorize_status(row_duration):
if row_duration <= current_target_ct:
return "🟢 Running (On Target)"
elif current_target_ct < row_duration <= 300:
return "🟡 Waiting / Slow Cycle"
else:
return "🔴 Breakdown / Stop"
sub_df['Status'] = sub_df['Duration'].apply(categorize_status)
for _, row in sub_df.iterrows():
timeline_data.append({
"Station": t_clean,
"LogTime": row[end_col],
"Cycle Time (s)": round(max(0.1, row['Duration']), 1),
"Target CT (s)": current_target_ct,
"Status": row['Status'],
"HousingCode": row['HousingCode']
})
if timeline_data:
df_timeline = pd.DataFrame(timeline_data).sort_values(by="LogTime")
st.markdown("### 🕒 Real-Time Station Status Timeline Map")
fig_timeline = px.scatter(
df_timeline,
x="LogTime", y="Station", color="Status",
hover_data=["HousingCode", "Cycle Time (s)", "Target CT (s)"],
color_discrete_map={
"🟢 Running (On Target)": "#2ecc71",
"🟡 Waiting / Slow Cycle": "#f1c40f",
"🔴 Breakdown / Stop": "#e74c3c"
},
title="Line Part Sequencing & Flow Analysis Across All Stations",
template="plotly_dark", height=500
)
fig_timeline.update_traces(marker=dict(size=12, symbol="square"))
fig_timeline.update_layout(
xaxis_title="Time of Day", yaxis_title="Station Code", legend_title="Machine State",
yaxis={'categoryorder': 'array', 'categoryarray': sorted(df_timeline['Station'].unique(), reverse=True)}
)
st.plotly_chart(fig_timeline, use_container_width=True)
st.markdown("---")
c_break1, c_break2 = st.columns([2, 1])
with c_break1:
st.markdown("### 📊 Accumulated Time Distribution per Station")
fig_status_distribution = px.histogram(
df_timeline, x="Station", color="Status", barmode="stack",
color_discrete_map={
"🟢 Running (On Target)": "#2ecc71",
"🟡 Waiting / Slow Cycle": "#f1c40f",
"🔴 Breakdown / Stop": "#e74c3c"
},
template="plotly_dark", height=380
)
st.plotly_chart(fig_status_distribution, use_container_width=True)
with c_break2:
st.markdown("### 🚨 Top Bottleneck / Waiting Logs")
slow_logs = df_timeline[df_timeline['Status'] != "🟢 Running (On Target)"].sort_values(by="Cycle Time (s)", ascending=False)
if not slow_logs.empty:
st.dataframe(
slow_logs[["Station", "LogTime", "Cycle Time (s)", "HousingCode"]].head(10),
use_container_width=True, hide_index=True
)
else:
st.success("✅ Excellent! All stations currently passing parts inside clean target values.")
else:
st.info("ℹ️ No log time stamps discovered to parse separate station cycle sequences.")
# ==========================================
# 6. Global Expanders Section
# ==========================================
st.markdown("---")
with st.expander("🔍 STATION ERROR DEEP-DIVE: Identify Specific Parameter NGs"):
if view_mode == "Monthly (Days)":
st.warning("⚠️ Parameter deep-dive is optimized for Daily view mode to maintain performance runtime limits.")
elif ng_total > 0:
st.markdown("##### 1. Select an NG Station to Inspect")
ng_stations = []
for col in process_cols:
if col in raw_df.columns and raw_df[raw_df['Summary_Result'] == 'NG'][col].astype(str).str.strip().str.upper().eq('NG').any():
clean_name = col.replace('_Final_Result', '').replace('总结果', '')
ng_stations.append((clean_name, col))
if ng_stations:
station_mapping = dict(ng_stations)
selected_st_clean = st.selectbox("Select Station with Failures:", list(station_mapping.keys()), key="ng_dive_st")
target_final_col = station_mapping[selected_st_clean]
st_failed_df = raw_df[raw_df[target_final_col].astype(str).str.strip().str.upper() == 'NG'].copy()
station_prefix = selected_st_clean.split('_')[0]
all_station_columns = [c for c in raw_df.columns if str(c).startswith(station_prefix)]
core_display_cols = ['HousingCode', 'LogTime']
if 'PalletNo' in raw_df.columns: core_display_cols.append('PalletNo')
param_cols = [c for c in all_station_columns if c not in [target_final_col, 'HousingCode', 'LogTime', 'PalletNo', 'Summary_Result', 'DateTime', 'Hour', 'Day']]
final_display_view = core_display_cols + param_cols
st.markdown(f"##### 2. Failed Parts Log for {selected_st_clean} ({len(st_failed_df)} items found)")
available_cols = [c for c in final_display_view if c in st_failed_df.columns]
st.dataframe(st_failed_df[available_cols].sort_values('LogTime', ascending=False), use_container_width=True)
else: st.info("No active station records matching strict 'NG' conditions in current views.")
else: st.success("✅ Clean Pass! No items have an NG status during this shift timeframe.")
with st.expander("📈 CLICK TO VIEW: Process Parameter Analysis"):
if view_mode == "Monthly (Days)": st.warning("⚠️ Distribution profiling graphs are available when using Daily mode.")
else:
all_cols = raw_df.columns.tolist()
stations = sorted(list(set([str(c).split('_')[0] for c in all_cols if 'ST' in str(c)])))
ca, cb = st.columns(2)
with ca: sel_st = st.selectbox("📌 Select Station:", stations)
st_cols = [c for c in all_cols if str(c).startswith(sel_st)]
params = [c for c in st_cols if not any(k in str(c) for k in ['Result', 'Time', 'Code', 'UID', '时间', '结果'])]
with cb: sel_param = st.selectbox("📊 Select Parameter:", params) if params else None
if sel_param:
pallet_col_box = next((c for c in st_cols if any(k in c for k in ['Jig_No', '编号', 'Pallet'])), None)
plot_df = raw_df.dropna(subset=[sel_param]).copy()
plot_df[sel_param] = pd.to_numeric(plot_df[sel_param], errors='coerce')
fig_box = px.box(plot_df, x=pallet_col_box, y=sel_param, color=pallet_col_box, template="plotly_dark")
st.plotly_chart(fig_box, use_container_width=True)
with st.expander("🔍 CLICK TO VIEW: Search & Data Table"):
search_q = st.text_input("Quick Search Housing Code:", key="bottom_search").strip().upper()
disp_df = raw_df.copy()
if search_q: disp_df = disp_df[disp_df['HousingCode'].str.contains(search_q, na=False)]
st.dataframe(disp_df.sort_values('DateTime', ascending=False), use_container_width=True)
else:
st.error("❌ ไม่พบข้อมูลสำหรับช่วงเวลาที่เลือก หรือกรุณาตรวจสอบการตั้งค่า ASSY_URL ใน Repository Secrets")