HAMZAH02/Auditing_Tools

1import pandas as pd2import gradio as gr3import os4import tempfile5import openpyxl6 7def find_email_column(df):8    for col in df.columns:9        if 'email' in col.lower():10            return col11    raise ValueError("No email column found in the uploaded file.")12 13def verify_access_by_sheet(access_file, hr_file):14    ext = os.path.splitext(access_file.name)[-1].lower()15 16    if ext in ['.xls', '.xlsx']:17        xls = pd.ExcelFile(access_file)18        sheet_dfs = {sheet: xls.parse(sheet) for sheet in xls.sheet_names}19    elif ext == '.csv':20        df = pd.read_csv(access_file)21        sheet_dfs = {"CSV": df}22    else:23        raise ValueError("Unsupported file format. Please upload CSV or Excel files.")24 25    hr_df = pd.read_excel(hr_file) if hr_file.name.endswith(('.xls', '.xlsx')) else pd.read_csv(hr_file)26    hr_email_col = find_email_column(hr_df)27    hr_df['Email_Username'] = hr_df[hr_email_col].str.split('@').str[0]28 29    results = {}30    combined_df = []31 32    for sheet_name, access_df in sheet_dfs.items():33        try:34            access_email_col = find_email_column(access_df)35            access_df['Email_Username'] = access_df[access_email_col].str.split('@').str[0]36 37            merged_df = access_df.merge(hr_df[['Email_Username', 'Employment Status']], on='Email_Username', how='left')38            merged_df['AccessStatus'] = merged_df['Employment Status'].apply(39                lambda x: 'Valid Staff' if pd.notnull(x) and x.lower() == 'active'40                else 'Former Staff' if pd.notnull(x) and x.lower() == 'inactive'41                else 'Unknown Staff' 42            )43            merged_df['Platform'] = sheet_name44 45            temp_dir = tempfile.mkdtemp()46            file_path = os.path.join(temp_dir, f"{sheet_name}_verified.csv")47            merged_df.to_csv(file_path, index=False)48 49            message = "✅ All good — no unknown or former staff found." if merged_df['AccessStatus'].eq('Valid Staff').all() else "⚠️ Some access records are not matched to active staff."50 51            results[sheet_name] = (merged_df, message, file_path)52            combined_df.append(merged_df)53        except Exception as e:54            results[sheet_name] = (pd.DataFrame(), f"❌ Error processing sheet '{sheet_name}': {str(e)}", None)55 56    return results, pd.concat(combined_df, ignore_index=True) if combined_df else pd.DataFrame()57 58with gr.Blocks() as demo:59    gr.Markdown("## 🛡️ Staff Access Verifier")60 61    with gr.Row():62        access_file = gr.File(label="Access log file")63        hr_file = gr.File(label="HR Master file")64 65    submit_btn = gr.Button("Submit")66    sheet_filter = gr.Dropdown(label="Filter by Sheet", choices=[], value=None, interactive=True)67    mismatch_filter = gr.Checkbox(label="Show only mismatches", value=False)68    column_selector = gr.CheckboxGroup(label='Select columns to display', choices=[], interactive=True)69    output_df = gr.DataFrame(label="Verified Access Records", interactive=False)70    dropdown = gr.Dropdown(label="Download verified sheet", choices=[])71    download_file = gr.File(label="Download CSV")72    file_state = gr.State({})73    full_df_state = gr.State(pd.DataFrame())74 75    def process_files(access_file, hr_file):76        results, combined_df = verify_access_by_sheet(access_file, hr_file)77        sheet_names = list(results.keys())78        file_map = {k: v[2] for k, v in results.items() if v[2] is not None}79        available_columns = list(combined_df.columns)80        return(81            gr.update(choices=sheet_names,value=None),82            gr.update(choices=sheet_names,value=None),83            file_map,84            combined_df,85            gr.update(choices=available_columns, value=['AccessStatus','Employment Status'])86        )87        88    submit_btn.click(89        process_files,90        inputs=[access_file, hr_file],91        outputs=[sheet_filter, dropdown, file_state, full_df_state, column_selector]92    )93 94    def filter_dataframe(df, sheet_name, mismatches_only,selected_columns):95        if df.empty:96            return df97        filtered = df.copy()98        if sheet_name:99            filtered = filtered[filtered['Platform'] == sheet_name]100        if mismatches_only:101            filtered = filtered[filtered['AccessStatus'] != 'Valid Staff']102 103        # ✅ Choose which columns to display104        display_columns = [col for col in selected_columns if col in filtered.columns]105        return filtered[display_columns]106 107 108    sheet_filter.change(filter_dataframe, inputs=[full_df_state, sheet_filter, mismatch_filter,column_selector], outputs=output_df)109    mismatch_filter.change(filter_dataframe, inputs=[full_df_state, sheet_filter, mismatch_filter,column_selector], outputs=output_df)110    column_selector.change(filter_dataframe, inputs=[full_df_state, sheet_filter, mismatch_filter,column_selector], outputs=output_df)111 112    def download_selected(sheet_name, file_map):113        return file_map.get(sheet_name, None)114 115    dropdown.change(download_selected, inputs=[dropdown, file_state], outputs=download_file)116 117demo.launch(share=True)118