Week Overview(1124)

C++编程陷阱与正确实践
  
float型参数不能直接传入数值,否则编译器不能判断它是double型还是float型
Write(string section , string key , float value)
Write(“Infor”,”Name”, 0.1);      //error
应传入float型变量
float p=0.1;
Write(“Infor”,”Name”, p);       //correct
 
子类不能用指针
SMConfig * m_config;
m_config=new SMConfig;
class SMConfig
{
       template<class T>
       class SectionWriter
       {
       }
}
 
m_config->SectionWriter<MAPS::value_type>(f); //error
SMConfig:: SectionWriter<MAPS::value_type>(f); //correct
 
free(buf)后buf!=NULL
 
不能直接把字符串常量赋值给char *
char * buf;
buf=(char *)malloc(256);
buf=”abc”;                //error, buf不是const char *,所以不能这样赋值
 
char * buf;
buf=(char *)malloc(256);
memcpy(buf,”abc”,3);
*(buf+3)=0          //0等于’/0’, 表示buf结尾,否则打印buf将打印abc及其后面的乱码
 
EA的用法
Project->Version Control->Work Offline
project view, 双击main, 右键check out,就可在本地改动文件了
画箭头:选中testcase的方框,再选中tool box中custom下 Dependency,从testcase方框内拖到函数方框内
画diagram frame: 右键 Insert element at cursor->diagram frame
把函数从Class Model模块下拖到Test Model模块下
toolbox下 custom->testcase, 右键 Advanced Settings->use rectangle notation
## pip install streamlit==1.47.1 ## pip install pandas==1.5.1 ## pip install plotly==5.24.1 ## pip install chinese-calendar==1.9.0 ## pip install openpyxl==3.1.2 ## 并把 file_path 改成你的 Excel 绝对路径 import streamlit as st import pandas as pd import plotly.graph_objects as go from datetime import datetime, timedelta import chinese_calendar from openpyxl import load_workbook st.set_page_config(page_title="GE90 Gate1 TAT Tracker", layout="wide") st.title("GE90 Gate1 TAT Tracker Dashboard") file_path = r"C:\Users\28976\PycharmProjects\GE Gate1 Dashboard\G1 Status Review Report-to ZhiBo.xlsm" sheet_names = ["WIP G1", "Closed G1"] # ========== 只保留 Excel 大纲第一层级并保留原始行序 ========== def filter_df_by_outline_and_attach_row(ws, df, header_row=1, keep_level_max=1, exclude_hidden=True): """ 根据 openpyxl 读取到的 outlineLevel/hidden 过滤 DataFrame,仅保留 <= keep_level_max 的可见行, 并在 DataFrame 中附加 Excel 行号 'ExcelRow',用于后续严格按 Excel 顺序展示。 假设 df 第一条数据对应 Excel 第 header_row+1 行。 """ start_excel_row = header_row + 1 levels, visibles, excel_rows = [], [], [] for i in range(len(df)): excel_row = start_excel_row + i dim = ws.row_dimensions.get(excel_row) level = getattr(dim, "outlineLevel", 0) if dim else 0 hidden = getattr(dim, "hidden", False) if dim else False excel_rows.append(excel_row) levels.append(level) visibles.append(not hidden) out = df.copy() out["ExcelRow"] = excel_rows out["_outline_level"] = levels out["_visible"] = visibles mask = (out["_outline_level"] <= keep_level_max) if exclude_hidden: mask &= out["_visible"] out = out[mask].drop(columns=["_outline_level", "_visible"]) return out @st.cache_data def load_data(): all_data = {} # 打开一次工作簿,便于逐 sheet 读取大纲层级/隐藏状态 try: wb = load_workbook(file_path, data_only=True, read_only=False) except Exception: wb = None # 如果表头不是第 1 行,请修改 HEADER_ROW HEADER_ROW = 1 KEEP_LEVEL_MAX = 1 EXCLUDE_HIDDEN = True for sheet in sheet_names: # 1) pandas 读取 try: df0 = pd.read_excel(file_path, sheet_name=sheet) except Exception: all_data[sheet] = pd.DataFrame() continue # 2) 保留 Projected 列(WIP 会用到) # 不要把 Projected 重命名成 Actual;保留两者 # 若你的 WIP 表叫 "Projected G1 Close Date" 就在重命名映射中映射为 Projected_G1_Close_Date base_cols = [ 'ESN', 'Operator', 'SV Type', 'Induction Date', 'Target G1 Close Date', 'Actual G1 Close Date', 'Projected G1 Close Date', 'G1 Actual TAT', 'G1 Target TAT', 'Workstop Item', 'Workstop Module', 'Workstop Start Date', 'Workstop Item Close Date', 'Categories/Delay reasons', 'Details/Comments', 'G1 Stopped Days' ] optional_cols = [] if 'Workstop Description' in df0.columns: optional_cols.append('Workstop Description') # 只取存在的列 cols_to_use = [c for c in base_cols if c in df0.columns] + optional_cols df0 = df0[cols_to_use].copy() # 3) 按 Excel 大纲第一层级过滤 + 附加 ExcelRow(若能打开工作簿) if wb is not None and sheet in wb.sheetnames: ws = wb[sheet] try: df0 = filter_df_by_outline_and_attach_row( ws, df0, header_row=HEADER_ROW, keep_level_max=KEEP_LEVEL_MAX, exclude_hidden=EXCLUDE_HIDDEN ) except Exception: # 失败就退化为未过滤,但仍继续执行 df0["ExcelRow"] = range(HEADER_ROW+1, HEADER_ROW+1+len(df0)) else: # 无 wb 时至少保留一个顺序标记 df0["ExcelRow"] = range(HEADER_ROW+1, HEADER_ROW+1+len(df0)) # 4) 统一列名 rename_map = { 'SV Type': 'SV_Type', 'Induction Date': 'Induction_Date', 'Target G1 Close Date': 'Target_G1_Close_Date', 'Actual G1 Close Date': 'Actual_G1_Close_Date', 'Projected G1 Close Date': 'Projected_G1_Close_Date', 'G1 Actual TAT': 'G1_Actual_TAT', 'G1 Target TAT': 'G1_Target_TAT', 'Workstop Item': 'Workstop_Item', 'Workstop Module': 'Workstop_Module', 'Workstop Start Date': 'Workstop_Start_Date', 'Workstop Item Close Date': 'Workstop_Item_Close_Date', 'Categories/Delay reasons': 'Delay_Reason', 'Details/Comments': 'Details', 'G1 Stopped Days': 'G1_Stopped_Days', 'Workstop Description': 'Workstop_Description' } df = df0.rename(columns=rename_map) # 5) 缺列统一补空,保证后续访问安全 for must in [ 'ESN','Operator','SV_Type','Induction_Date', 'Target_G1_Close_Date','Actual_G1_Close_Date','Projected_G1_Close_Date', 'G1_Actual_TAT','G1_Target_TAT', 'Workstop_Item','Workstop_Module', 'Workstop_Start_Date','Workstop_Item_Close_Date', 'Delay_Reason','Details','G1_Stopped_Days','Workstop_Description', 'ExcelRow' ]: if must not in df.columns: df[must] = pd.NA # 6) 日期字段容错转换 for col in ['Induction_Date','Target_G1_Close_Date','Actual_G1_Close_Date','Projected_G1_Close_Date', 'Workstop_Start_Date','Workstop_Item_Close_Date']: try: df[col] = pd.to_datetime(df[col], errors='coerce') except Exception: df[col] = pd.NaT df['Sheet'] = sheet try: df['ESN'] = df['ESN'].astype(str) except Exception: pass # 7) 最终按 ExcelRow 排序,确保和 Excel 第一层级显示顺序一致 df = df.sort_values('ExcelRow', kind="stable", na_position="last").reset_index(drop=True) all_data[sheet] = df return all_data all_data = load_data() all_df = pd.concat(all_data.values(), ignore_index=True) # ===== Row 1: From / To ===== c_from, c_to = st.columns(2) with c_from: today = datetime.today().date() default_from = datetime(today.year, 1, 1).date() from_date = st.date_input("📅 From:", value=default_from) with c_to: to_date = st.date_input("📅 To:", value=today) # ===== Row 2: Time Axis Unit + Select Sheet ===== c_unit, c_sheet = st.columns(2) with c_unit: view_mode = st.selectbox("⏱️ Time Axis Unit", ['Day', '3-Day', 'Week', 'Month', 'Quarter']) with c_sheet: selected_sheet = st.selectbox("📑 Select Sheet", sheet_names) # Tick Mapping one_day_ms = 24 * 60 * 60 * 1000 dtick_map = { 'Day': "D1", '3-Day': 3 * one_day_ms, 'Week': 7 * one_day_ms, 'Month': "M1", 'Quarter': "M3", } dtick = dtick_map.get(view_mode, "D1") # === Category Color Mapping === DELAY_COLOR_MAP = { "W/S Escalation pre-approval list": "#A9D08E", "W/S Escalation exception list": "#A9D08E", "W/S level creep": "#FFFF00", "W/S in level creep": "#FFFF00", "Prod. stopped by DR/SR": "#F4B084", "DR/SR Miss-recommitment/Overdue": "#F4B084", "W/S management": "#C00000", "Engine Disassemble Process Challenge": "#FFC000", "GE Commercial Approval Delay": "#5B9BD5", "Operator Approval Delay": "#5B9BD5", "Safran W/S approval": "#5B9BD5", "Module W/S Related lssues": "#FFCCFF", "Public Holiday": "#808080", "Manpower Limition": "#33CCCC", "Others": "#33CCCC" } DEFAULT_DELAY_COLOR = "#CD5C5C" UNCATEGORIZED_LABEL = "Uncategorized" UNCATEGORIZED_COLOR = "#808080" def category_and_color(raw_reason): label = (str(raw_reason).strip() if pd.notnull(raw_reason) else "") if not label: return UNCATEGORIZED_LABEL, UNCATEGORIZED_COLOR return label, DELAY_COLOR_MAP.get(label, DEFAULT_DELAY_COLOR) # --- Session State --- if "selected_esn" not in st.session_state: st.session_state.selected_esn = "" if "filter_esns" not in st.session_state: st.session_state.filter_esns = set() if st.button("🔙 Back To Overview"): st.session_state.selected_esn = "" st.session_state.filter_esns = set() st.rerun() # ===== Search Options ===== st.markdown("### 🔍 Search Options") with st.form("search_form"): input_esn = st.text_input("🔎 Enter ESN", value="").strip() operator_choices = [ "", "CCA", "CAL", "CPA", "CSN", "UAE", "ETH", "ETD", "MSR", "CES", "ANA", "SIA", "AFL", "SVA", "THY", "Aercap", "AIC", "CKK", "DAL", "EVA", "FDX", "JAL", "JAI", "QTR", "THA", "Safran", "Others" ] operator = st.selectbox("🧑‍✈️ Operator", operator_choices, index=0) sv_type_choices = ["", "PR-H", "PR-M", "QT-H", "QT-M", "QT-L", "Minor"] sv_type = st.selectbox("🔧 SV Type", sv_type_choices, index=0) submitted = st.form_submit_button("🔍 Search") # --- Search / Mode Logic --- if submitted: match_df = all_df.copy() if input_esn: match_df = match_df[match_df['ESN'] == input_esn] if not match_df.empty: st.session_state.selected_esn = input_esn selected_sheet = match_df.iloc[0]['Sheet'] st.session_state.filter_esns = set() st.rerun() else: st.warning("No Matching Record Found.") else: if operator: match_df = match_df[match_df['Operator'] == operator] if sv_type: match_df = match_df[match_df['SV_Type'] == sv_type] # 只取当前 sheet 的 ESN,并保持 Excel 原始顺序 sheet_order = all_data[selected_sheet]["ESN"].tolist() filtered_esns = [e for e in sheet_order if e in set(match_df[match_df['Sheet'] == selected_sheet]['ESN'].astype(str).tolist())] if filtered_esns: st.session_state.selected_esn = "" st.session_state.filter_esns = set(filtered_esns) st.rerun() else: st.warning("No Matching Record Found.") # === Filter Current Sheet Data By From/To === df = all_data[selected_sheet].copy() selected_esn = st.session_state.selected_esn filter_esns = st.session_state.filter_esns if from_date > to_date: st.error("Invalid Date Range: 'From' Must Be Earlier Than Or Equal To 'To'.") st.stop() date_col = 'Induction_Date' if selected_sheet == "WIP G1" else 'Actual_G1_Close_Date' mask = (df[date_col].dt.date >= from_date) & (df[date_col].dt.date <= to_date) df = df[mask].copy() # 只保留当前 sheet,并按 ExcelRow 保序 df = df.sort_values('ExcelRow', kind="stable", na_position="last").reset_index(drop=True) # 过滤 ESN:严格保持 Excel 原始顺序 sheet_esn_order = df['ESN'].tolist() # 包含重复;用于顺序参考 esn_order_unique = [] seen = set() for e in sheet_esn_order: if pd.isna(e): continue s = str(e) if s not in seen: esn_order_unique.append(s) seen.add(s) if selected_esn: esn_scope = [selected_esn] if selected_esn in esn_order_unique else [] elif filter_esns: esn_scope = [e for e in esn_order_unique if e in filter_esns] else: esn_scope = esn_order_unique if not esn_scope: st.warning("No ESN Matched After Filters.") st.stop() def build_parent_fields(esn, subdf, sheet_name): start = subdf['Induction_Date'].min() if sheet_name == "WIP G1": # WIP:优先用 Projected 作为母条结束时间 if 'Projected_G1_Close_Date' in subdf.columns and subdf['Projected_G1_Close_Date'].notna().any(): end = subdf['Projected_G1_Close_Date'].max() else: end = subdf['Actual_G1_Close_Date'].max() else: # Closed:用 Actual 作为母条结束时间 end = subdf['Actual_G1_Close_Date'].max() try: op = subdf['Operator'].dropna().astype(str).iloc[0] except Exception: op = "" try: svt = subdf['SV_Type'].dropna().astype(str).iloc[0] except Exception: svt = "" return start, end, op, svt def parent_label(esn, operator, sv_type): return f"ESN: {esn}<br>Operator: {operator}<br>SV Type: {sv_type}" tasks = [] any_children = False esn_mode = bool(selected_esn) is_wip = (selected_sheet == "WIP G1") for esn in esn_scope: esn_df = df[df['ESN'] == esn].copy() if esn_df.empty: continue p_start, p_end, p_op, p_svt = build_parent_fields(esn, esn_df, selected_sheet) if pd.isna(p_start) or pd.isna(p_end): continue parent_days = (p_end - p_start).days + 1 if parent_days <= 0: continue tasks.append({ 'Task': parent_label(esn, p_op, p_svt), 'Start': p_start, 'End': p_end, 'Type': 'Engine', 'Color': 'purple', 'Hover': f"Start: {p_start.date()}<br>End: {p_end.date()}<br>G1 Projected TAT: {parent_days}", 'Days': parent_days, 'Delay_Reason': None }) # Closed 在 Overview 也显示子条;WIP 始终显示 is_overview = (not esn_mode) and (len(filter_esns) == 0) show_children_closed = (selected_sheet == "Closed G1") and (esn_mode or is_overview) children_allowed = is_wip or show_children_closed if children_allowed: has_child = False # 保持与 Excel 顺序一致:子条也按 ExcelRow 排序 esn_df = esn_df.sort_values('ExcelRow', kind="stable", na_position="last") for _, row in esn_df.iterrows(): ws_start = row.get('Workstop_Start_Date', pd.NaT) ws_end = row.get('Workstop_Item_Close_Date', pd.NaT) if pd.isna(ws_start) or pd.isna(ws_end): continue child_days = (ws_end - ws_start).days + 1 if child_days <= 0: continue delay_reason = row.get('Delay_Reason', None) cat_label, cat_color = category_and_color(delay_reason) desc_text = row.get('Workstop_Description', None) if pd.isna(desc_text) or (str(desc_text).strip() == ""): desc_text = row.get('Details', "") ws_module = row.get('Workstop_Module', "") if pd.isna(ws_module): ws_module = "" tasks.append({ 'Task': ws_module, 'Start': ws_start, 'End': ws_end, 'Type': 'Module', 'Color': cat_color, 'Hover': f"Start: {ws_start.date()}<br>End: {ws_end.date()}<br>G1 Stopped Days: {row.get('G1_Stopped_Days','')}<br>Workstop Description: {desc_text}", 'Days': child_days, 'Delay_Reason': cat_label, 'PStart': p_start, 'PDays': parent_days }) has_child = True any_children = any_children or has_child # ===== Plot ===== if not tasks: st.warning("No Data To Display.") else: task_df = pd.DataFrame(tasks).dropna(subset=['Start', 'End']) if task_df.empty: st.warning("No Data To Display.") else: # 这里的顺序就是 append 的顺序(已经是 Excel 顺序的 ESN + 该 ESN 下按 ExcelRow 的子条) task_df['y_pos'] = range(len(task_df)) fig = go.Figure() for _, row in task_df.iterrows(): start = row['Start'] end = row['End'] + timedelta(hours=4) if start == row['End'] else row['End'] fig.add_trace(go.Scatter( x=[start, end], y=[row['y_pos'], row['y_pos']], mode='lines', line=dict(color=row['Color'], width=30 if row['Type'] == 'Engine' else 20), hovertext=row['Hover'], hoverinfo='text', name=row['Task'], showlegend=False )) # 标注 if row['Type'] == 'Engine': for i in range(int(row['Days'])): x_pos = row['Start'] + timedelta(days=i) fig.add_annotation(x=x_pos, y=row['y_pos'] - 0.40, text=str(i+1), showarrow=False, font=dict(size=11, color="black")) else: pstart = row.get('PStart', None) pdays = row.get('PDays', None) child_days = int(row['Days']) if (pstart is not None) and (pdays is not None) and (not pd.isna(pstart)): start_idx = (row['Start'] - pstart).days + 1 end_idx = (row['End'] - pstart).days + 1 within_parent = (1 <= start_idx <= pdays) and (1 <= end_idx <= pdays) if within_parent: s_label, e_label = str(start_idx), str(end_idx) else: s_label, e_label = "1", str(child_days) else: s_label, e_label = "1", str(child_days) fig.add_annotation(x=row['Start'], y=row['y_pos'] - 0.40, text=s_label, showarrow=False, font=dict(size=11, color="black")) fig.add_annotation(x=row['End'], y=row['y_pos'] - 0.40, text=e_label, showarrow=False, font=dict(size=11, color="black")) # 仅“输入 ESN + Day/3-Day”标法定节假日 if st.session_state.selected_esn and view_mode in ['Day', '3-Day']: for d in pd.date_range(start=start, end=end): try: is_hol, hol_name = chinese_calendar.get_holiday_detail(d) if is_hol and hol_name: fig.add_vline(x=d, line=dict(color='red', width=1, dash='dot')) except Exception: pass # Legend fig.add_trace(go.Scatter( x=[None], y=[None], mode='markers', marker=dict(size=12, color='purple'), name='Engine', showlegend=True )) module_df = task_df[task_df['Type'] == 'Module'] if not module_df.empty: tmp = module_df[['Delay_Reason', 'Color']].copy() tmp['Delay_Reason'] = tmp['Delay_Reason'].fillna('Uncategorized') for _, r in tmp.drop_duplicates().iterrows(): fig.add_trace(go.Scatter( x=[None], y=[None], mode='markers', marker=dict(size=12, color=r['Color']), name=str(r['Delay_Reason']), showlegend=True )) # X 轴 xaxis_args = dict(title='Date', type='date', showgrid=True, rangeslider_visible=True) if view_mode == 'Week': one_week_ms = 7 * one_day_ms xaxis_args.update(tickmode='linear', dtick=one_week_ms, tickformatstops=[dict(dtickrange=[one_week_ms, one_week_ms], value="Week %W")]) elif view_mode == 'Quarter': span_start = task_df['Start'].min() - pd.DateOffset(years=1) span_end = task_df['End'].max() + pd.DateOffset(years=1) quarter_starts = pd.date_range(span_start, span_end, freq='QS') xaxis_args.update(tickmode='array', tickvals=quarter_starts, ticktext=[f"{d.year}Q{((d.month-1)//3)+1}" for d in quarter_starts]) else: xaxis_args.update(dtick=dtick) if view_mode in ['Day', '3-Day']: xaxis_args.update(tickformat="%b-%d") elif view_mode == 'Month': xaxis_args.update(tickformat="%b %Y") # Y 轴与尺寸 y_min = task_df['y_pos'].min() - 0.9 y_max = task_df['y_pos'].max() + 0.6 fig.update_layout( title="📊 Gantt Chart", height=150 + 70 * len(task_df), width=6000, xaxis=xaxis_args, yaxis=dict( tickvals=task_df['y_pos'], ticktext=task_df['Task'], # 任务标签顺序 = 我们 append 的顺序 = Excel 顺序 title="", automargin=True, range=[y_max, y_min], # 维持现有“上新下旧”的视觉;如需从上到下递增,把这一行换成 autorange=True autorange=False ), hovermode="x unified", margin=dict(l=320, r=40, t=70, b=80) ) st.plotly_chart(fig, use_container_width=True) else: st.warning("No Data To Display.")
09-17
### 安装指定版本的库 可以使用以下命令安装指定版本的库: ```bash pip install streamlit==1.47.1 pandas==1.5.1 plotly==5.24.1 chinese-calendar==1.9.0 openpyxl==3.1.2 ``` ### 修改 Excel 文件路径 在代码中找到 `file_path` 变量并将其修改为你的 Excel 文件的绝对路径,例如: ```python file_path = r"C:\Your\Actual\Path\To\Your\ExcelFile.xlsm" ``` ### 代码检查与修改 从提供的代码来看,没有明显的语法错误。不过,可能会在运行时因为文件路径、文件格式、数据缺失等问题出现异常。以下是一些可能需要注意的点: #### 1. 文件路径问题 确保 `file_path` 指向的文件确实存在,并且有读取权限。 #### 2. 日期转换问题 在日期字段容错转换部分,代码使用了 `pd.to_datetime` 并设置 `errors='coerce'`,这可以处理一些无效日期,但如果数据中存在非常特殊的日期格式,可能仍然会有问题。 #### 3. 数据缺失问题 代码中对缺失列进行了补空处理,但如果某些关键列的数据全部缺失,可能会影响后续的计算和绘图。 ### 完整代码示例(仅修改文件路径部分) ```python import streamlit as st import pandas as pd import plotly.graph_objects as go from datetime import datetime, timedelta import chinese_calendar from openpyxl import load_workbook st.set_page_config(page_title="GE90 Gate1 TAT Tracker", layout="wide") st.title("GE90 Gate1 TAT Tracker Dashboard") # 修改为你的 Excel 文件绝对路径 file_path = r"C:\Your\Actual\Path\To\Your\ExcelFile.xlsm" sheet_names = ["WIP G1", "Closed G1"] # ========== 只保留 Excel 大纲第一层级并保留原始行序 ========== def filter_df_by_outline_and_attach_row(ws, df, header_row=1, keep_level_max=1, exclude_hidden=True): """ 根据 openpyxl 读取到的 outlineLevel/hidden 过滤 DataFrame,仅保留 <= keep_level_max 的可见行, 并在 DataFrame 中附加 Excel 行号 'ExcelRow',用于后续严格按 Excel 顺序展示。 假设 df 第一条数据对应 Excel 第 header_row+1 行。 """ start_excel_row = header_row + 1 levels, visibles, excel_rows = [], [], [] for i in range(len(df)): excel_row = start_excel_row + i dim = ws.row_dimensions.get(excel_row) level = getattr(dim, "outlineLevel", 0) if dim else 0 hidden = getattr(dim, "hidden", False) if dim else False excel_rows.append(excel_row) levels.append(level) visibles.append(not hidden) out = df.copy() out["ExcelRow"] = excel_rows out["_outline_level"] = levels out["_visible"] = visibles mask = (out["_outline_level"] <= keep_level_max) if exclude_hidden: mask &= out["_visible"] out = out[mask].drop(columns=["_outline_level", "_visible"]) return out @st.cache_data def load_data(): all_data = {} # 打开一次工作簿,便于逐 sheet 读取大纲层级/隐藏状态 try: wb = load_workbook(file_path, data_only=True, read_only=False) except Exception: wb = None # 如果表头不是第 1 行,请修改 HEADER_ROW HEADER_ROW = 1 KEEP_LEVEL_MAX = 1 EXCLUDE_HIDDEN = True for sheet in sheet_names: # 1) pandas 读取 try: df0 = pd.read_excel(file_path, sheet_name=sheet) except Exception: all_data[sheet] = pd.DataFrame() continue # 2) 保留 Projected 列(WIP 会用到) # 不要把 Projected 重命名成 Actual;保留两者 # 若你的 WIP 表叫 "Projected G1 Close Date" 就在重命名映射中映射为 Projected_G1_Close_Date base_cols = [ 'ESN', 'Operator', 'SV Type', 'Induction Date', 'Target G1 Close Date', 'Actual G1 Close Date', 'Projected G1 Close Date', 'G1 Actual TAT', 'G1 Target TAT', 'Workstop Item', 'Workstop Module', 'Workstop Start Date', 'Workstop Item Close Date', 'Categories/Delay reasons', 'Details/Comments', 'G1 Stopped Days' ] optional_cols = [] if 'Workstop Description' in df0.columns: optional_cols.append('Workstop Description') # 只取存在的列 cols_to_use = [c for c in base_cols if c in df0.columns] + optional_cols df0 = df0[cols_to_use].copy() # 3) 按 Excel 大纲第一层级过滤 + 附加 ExcelRow(若能打开工作簿) if wb is not None and sheet in wb.sheetnames: ws = wb[sheet] try: df0 = filter_df_by_outline_and_attach_row( ws, df0, header_row=HEADER_ROW, keep_level_max=KEEP_LEVEL_MAX, exclude_hidden=EXCLUDE_HIDDEN ) except Exception: # 失败就退化为未过滤,但仍继续执行 df0["ExcelRow"] = range(HEADER_ROW+1, HEADER_ROW+1+len(df0)) else: # 无 wb 时至少保留一个顺序标记 df0["ExcelRow"] = range(HEADER_ROW+1, HEADER_ROW+1+len(df0)) # 4) 统一列名 rename_map = { 'SV Type': 'SV_Type', 'Induction Date': 'Induction_Date', 'Target G1 Close Date': 'Target_G1_Close_Date', 'Actual G1 Close Date': 'Actual_G1_Close_Date', 'Projected G1 Close Date': 'Projected_G1_Close_Date', 'G1 Actual TAT': 'G1_Actual_TAT', 'G1 Target TAT': 'G1_Target_TAT', 'Workstop Item': 'Workstop_Item', 'Workstop Module': 'Workstop_Module', 'Workstop Start Date': 'Workstop_Start_Date', 'Workstop Item Close Date': 'Workstop_Item_Close_Date', 'Categories/Delay reasons': 'Delay_Reason', 'Details/Comments': 'Details', 'G1 Stopped Days': 'G1_Stopped_Days', 'Workstop Description': 'Workstop_Description' } df = df0.rename(columns=rename_map) # 5) 缺列统一补空,保证后续访问安全 for must in [ 'ESN','Operator','SV_Type','Induction_Date', 'Target_G1_Close_Date','Actual_G1_Close_Date','Projected_G1_Close_Date', 'G1_Actual_TAT','G1_Target_TAT', 'Workstop_Item','Workstop_Module', 'Workstop_Start_Date','Workstop_Item_Close_Date', 'Delay_Reason','Details','G1_Stopped_Days','Workstop_Description', 'ExcelRow' ]: if must not in df.columns: df[must] = pd.NA # 6) 日期字段容错转换 for col in ['Induction_Date','Target_G1_Close_Date','Actual_G1_Close_Date','Projected_G1_Close_Date', 'Workstop_Start_Date','Workstop_Item_Close_Date']: try: df[col] = pd.to_datetime(df[col], errors='coerce') except Exception: df[col] = pd.NaT df['Sheet'] = sheet try: df['ESN'] = df['ESN'].astype(str) except Exception: pass # 7) 最终按 ExcelRow 排序,确保和 Excel 第一层级显示顺序一致 df = df.sort_values('ExcelRow', kind="stable", na_position="last").reset_index(drop=True) all_data[sheet] = df return all_data all_data = load_data() all_df = pd.concat(all_data.values(), ignore_index=True) # ===== Row 1: From / To ===== c_from, c_to = st.columns(2) with c_from: today = datetime.today().date() default_from = datetime(today.year, 1, 1).date() from_date = st.date_input("📅 From:", value=default_from) with c_to: to_date = st.date_input("📅 To:", value=today) # ===== Row 2: Time Axis Unit + Select Sheet ===== c_unit, c_sheet = st.columns(2) with c_unit: view_mode = st.selectbox("⏱️ Time Axis Unit", ['Day', '3-Day', 'Week', 'Month', 'Quarter']) with c_sheet: selected_sheet = st.selectbox("📑 Select Sheet", sheet_names) # Tick Mapping one_day_ms = 24 * 60 * 60 * 1000 dtick_map = { 'Day': "D1", '3-Day': 3 * one_day_ms, 'Week': 7 * one_day_ms, 'Month': "M1", 'Quarter': "M3", } dtick = dtick_map.get(view_mode, "D1") # === Category Color Mapping === DELAY_COLOR_MAP = { "W/S Escalation pre-approval list": "#A9D08E", "W/S Escalation exception list": "#A9D08E", "W/S level creep": "#FFFF00", "W/S in level creep": "#FFFF00", "Prod. stopped by DR/SR": "#F4B084", "DR/SR Miss-recommitment/Overdue": "#F4B084", "W/S management": "#C00000", "Engine Disassemble Process Challenge": "#FFC000", "GE Commercial Approval Delay": "#5B9BD5", "Operator Approval Delay": "#5B9BD5", "Safran W/S approval": "#5B9BD5", "Module W/S Related lssues": "#FFCCFF", "Public Holiday": "#808080", "Manpower Limition": "#33CCCC", "Others": "#33CCCC" } DEFAULT_DELAY_COLOR = "#CD5C5C" UNCATEGORIZED_LABEL = "Uncategorized" UNCATEGORIZED_COLOR = "#808080" def category_and_color(raw_reason): label = (str(raw_reason).strip() if pd.notnull(raw_reason) else "") if not label: return UNCATEGORIZED_LABEL, UNCATEGORIZED_COLOR return label, DELAY_COLOR_MAP.get(label, DEFAULT_DELAY_COLOR) # --- Session State --- if "selected_esn" not in st.session_state: st.session_state.selected_esn = "" if "filter_esns" not in st.session_state: st.session_state.filter_esns = set() if st.button("🔙 Back To Overview"): st.session_state.selected_esn = "" st.session_state.filter_esns = set() st.rerun() # ===== Search Options ===== st.markdown("### 🔍 Search Options") with st.form("search_form"): input_esn = st.text_input("🔎 Enter ESN", value="").strip() operator_choices = [ "", "CCA", "CAL", "CPA", "CSN", "UAE", "ETH", "ETD", "MSR", "CES", "ANA", "SIA", "AFL", "SVA", "THY", "Aercap", "AIC", "CKK", "DAL", "EVA", "FDX", "JAL", "JAI", "QTR", "THA", "Safran", "Others" ] operator = st.selectbox("🧑‍✈️ Operator", operator_choices, index=0) sv_type_choices = ["", "PR-H", "PR-M", "QT-H", "QT-M", "QT-L", "Minor"] sv_type = st.selectbox("🔧 SV Type", sv_type_choices, index=0) submitted = st.form_submit_button("🔍 Search") # --- Search / Mode Logic --- if submitted: match_df = all_df.copy() if input_esn: match_df = match_df[match_df['ESN'] == input_esn] if not match_df.empty: st.session_state.selected_esn = input_esn selected_sheet = match_df.iloc[0]['Sheet'] st.session_state.filter_esns = set() st.rerun() else: st.warning("No Matching Record Found.") else: if operator: match_df = match_df[match_df['Operator'] == operator] if sv_type: match_df = match_df[match_df['SV_Type'] == sv_type] # 只取当前 sheet 的 ESN,并保持 Excel 原始顺序 sheet_order = all_data[selected_sheet]["ESN"].tolist() filtered_esns = [e for e in sheet_order if e in set(match_df[match_df['Sheet'] == selected_sheet]['ESN'].astype(str).tolist())] if filtered_esns: st.session_state.selected_esn = "" st.session_state.filter_esns = set(filtered_esns) st.rerun() else: st.warning("No Matching Record Found.") # === Filter Current Sheet Data By From/To === df = all_data[selected_sheet].copy() selected_esn = st.session_state.selected_esn filter_esns = st.session_state.filter_esns if from_date > to_date: st.error("Invalid Date Range: 'From' Must Be Earlier Than Or Equal To 'To'.") st.stop() date_col = 'Induction_Date' if selected_sheet == "WIP G1" else 'Actual_G1_Close_Date' mask = (df[date_col].dt.date >= from_date) & (df[date_col].dt.date <= to_date) df = df[mask].copy() # 只保留当前 sheet,并按 ExcelRow 保序 df = df.sort_values('ExcelRow', kind="stable", na_position="last").reset_index(drop=True) # 过滤 ESN:严格保持 Excel 原始顺序 sheet_esn_order = df['ESN'].tolist() # 包含重复;用于顺序参考 esn_order_unique = [] seen = set() for e in sheet_esn_order: if pd.isna(e): continue s = str(e) if s not in seen: esn_order_unique.append(s) seen.add(s) if selected_esn: esn_scope = [selected_esn] if selected_esn in esn_order_unique else [] elif filter_esns: esn_scope = [e for e in esn_order_unique if e in filter_esns] else: esn_scope = esn_order_unique if not esn_scope: st.warning("No ESN Matched After Filters.") st.stop() def build_parent_fields(esn, subdf, sheet_name): start = subdf['Induction_Date'].min() if sheet_name == "WIP G1": # WIP:优先用 Projected 作为母条结束时间 if 'Projected_G1_Close_Date' in subdf.columns and subdf['Projected_G1_Close_Date'].notna().any(): end = subdf['Projected_G1_Close_Date'].max() else: end = subdf['Actual_G1_Close_Date'].max() else: # Closed:用 Actual 作为母条结束时间 end = subdf['Actual_G1_Close_Date'].max() try: op = subdf['Operator'].dropna().astype(str).iloc[0] except Exception: op = "" try: svt = subdf['SV_Type'].dropna().astype(str).iloc[0] except Exception: svt = "" return start, end, op, svt def parent_label(esn, operator, sv_type): return f"ESN: {esn}<br>Operator: {operator}<br>SV Type: {sv_type}" tasks = [] any_children = False esn_mode = bool(selected_esn) is_wip = (selected_s
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值