Bayhaqy/Input_Data_Tools_DEV

1import gradio as gr2import pandas as pd3import os4 5# ========== Constants ==========6EXCEL_FILE = "data_store.xlsx"7DATA_SHEET = "DATA"8USER_SHEET = "USERS"9 10# ========== Load or Create Data ==========11def load_data():12    if os.path.exists(EXCEL_FILE):13        xls = pd.read_excel(EXCEL_FILE, sheet_name=None)14        df_data = xls.get(DATA_SHEET, pd.DataFrame(columns=["USERNAME", "COUNTRYCODE", "SBUCODE", "BRANDCODE", "CONCEPTCODE", "CHANNEL", "REMARK"]))15        df_users = xls.get(USER_SHEET, pd.DataFrame([{"USERNAME": "ADMIN", "PASSWORD": "ADMIN"}]))16    else:17        df_data = pd.DataFrame(columns=["USERNAME", "COUNTRYCODE", "SBUCODE", "BRANDCODE", "CONCEPTCODE", "CHANNEL", "REMARK"])18        df_users = pd.DataFrame([{"USERNAME": "ADMIN", "PASSWORD": "ADMIN"}])19        save_data(df_data, df_users)20    return df_data, df_users21 22def save_data(data_df, users_df):23    with pd.ExcelWriter(EXCEL_FILE, engine='openpyxl', mode='w') as writer:24        data_df.to_excel(writer, index=False, sheet_name=DATA_SHEET)25        users_df.to_excel(writer, index=False, sheet_name=USER_SHEET)26 27# ========== Initial Data ==========28df, users_df = load_data()29 30# ========== Helpers ==========31def to_upper(df_input):32    return df_input.applymap(lambda x: str(x).upper().strip() if pd.notnull(x) else x)33 34def get_active_data():35    return df[df["REMARK"].str.upper() != "DELETE"].reset_index(drop=True)36 37def login(username, password):38    username, password = username.strip().upper(), password.strip()39    is_valid = not users_df[(users_df["USERNAME"] == username) & (users_df["PASSWORD"] == password)].empty40    if is_valid:41        return "", gr.update(visible=False), gr.update(visible=True), get_active_data(), show_users()42    return "Invalid login", gr.update(visible=True), gr.update(visible=False), pd.DataFrame(), pd.DataFrame()43 44def bulk_submit(dataframe):45    global df46    new_df = pd.DataFrame(dataframe).dropna(how="all")47    if new_df.empty:48        return get_active_data()49    new_df = to_upper(new_df)50    required = ["USERNAME", "COUNTRYCODE", "SBUCODE", "BRANDCODE", "CONCEPTCODE", "CHANNEL", "REMARK"]51    new_df = new_df[required].dropna()52    pk_cols = required53    merged = pd.merge(new_df, df[pk_cols], on=pk_cols, how="left", indicator=True)54    df_filtered = new_df[merged["_merge"] == "left_only"]55    df = pd.concat([df, df_filtered], ignore_index=True)56    save_data(df, users_df)57    return get_active_data()58 59def delete_selected_rows(selected_df):60    global df61    try:62        if selected_df.empty:63            return "Tidak ada baris yang dipilih", get_active_data()64        for _, row in selected_df.iterrows():65            mask = (66                (df["USERNAME"] == row["USERNAME"]) &67                (df["COUNTRYCODE"] == row["COUNTRYCODE"]) &68                (df["SBUCODE"] == row["SBUCODE"]) &69                (df["BRANDCODE"] == row["BRANDCODE"]) &70                (df["CONCEPTCODE"] == row["CONCEPTCODE"]) &71                (df["CHANNEL"] == row["CHANNEL"]) &72                (df["REMARK"].str.upper() != "DELETE")73            )74            df.loc[mask, "REMARK"] = "DELETE"75        save_data(df, users_df)76        return "Berhasil dihapus.", get_active_data()77    except Exception as e:78        return f"Error: {e}", get_active_data()79 80def add_user(username, password):81    global users_df82    username, password = username.strip().upper(), password.strip()83    if not username or not password:84        return "Username/password tidak boleh kosong", show_users()85    if username in users_df["USERNAME"].values:86        return "Username sudah ada", show_users()87    users_df = pd.concat([users_df, pd.DataFrame([{"USERNAME": username, "PASSWORD": password}])], ignore_index=True)88    save_data(df, users_df)89    return f"User {username} ditambahkan", show_users()90 91def delete_user(username):92    global users_df93    username = username.strip().upper()94    if username == "ADMIN":95        return "ADMIN tidak boleh dihapus", show_users()96    users_df = users_df[users_df["USERNAME"] != username].reset_index(drop=True)97    save_data(df, users_df)98    return f"User {username} dihapus", show_users()99 100def show_users():101    return users_df.copy()102 103def show_guide():104    return """105### Panduan Penggunaan Aplikasi1061. **Login**107   - Gunakan username dan password.1082. **Input Data**109   - Lihat data aktif.110   - Input data baru dengan kolom lengkap.111   - Pilih baris dan klik "Delete Selected Rows" untuk tandai delete.1123. **Manajemen User**113   - Tambah dan hapus user (kecuali ADMIN).1144. **Data yang dihapus tidak benar-benar dihapus**, hanya ditandai `REMARK = DELETE`.115"""116 117# ========== UI ==========118with gr.Blocks() as app:119    gr.Markdown("## Login")120    login_box = gr.Column(visible=True)121    with login_box:122        user = gr.Textbox(label="Username")123        pw = gr.Textbox(label="Password", type="password")124        btn_login = gr.Button("Login")125        login_msg = gr.Textbox(visible=True, interactive=False, show_label=False)126 127    tab_section = gr.Tabs(visible=False)128    with tab_section:129        with gr.Tab("Input Data"):130            gr.Markdown("### Data Aktif")131            df_list = gr.Dataframe(132                label="List Data Aktif",133                interactive=False,134                column_widths=["auto"]*7,135                wrap=True,136                show_copy_button=True,137                # show_download_button=True,138                show_fullscreen_button=True,139                show_search=True,140                show_row_numbers=True,141                pinned_columns=["USERNAME"]142            )143            selected_df = gr.Dataframe(visible=False)144            gr.Markdown("---")145            gr.Markdown("### Input Data Baru")146            df_input = gr.Dataframe(147                headers=["USERNAME", "COUNTRYCODE", "SBUCODE", "BRANDCODE", "CONCEPTCODE", "CHANNEL", "REMARK"],148                row_count=5,149                col_count=(7, "fixed"),150                label="Form Input"151            )152            btn_submit = gr.Button("Submit Data")153            btn_delete = gr.Button("Delete Selected Rows")154            delete_msg = gr.Textbox(visible=True, interactive=False, show_label=False)155 156        with gr.Tab("User Management"):157            gr.Markdown("### Kelola User")158            new_user = gr.Textbox(label="New Username")159            new_pass = gr.Textbox(label="New Password")160            btn_add = gr.Button("Add User")161            del_user = gr.Textbox(label="Delete Username")162            btn_del = gr.Button("Delete User")163            user_msg = gr.Textbox(visible=True, interactive=False, show_label=False)164            user_table = gr.Dataframe(label="User List", interactive=False)165 166        with gr.Tab("User Guide"):167            gr.Markdown(show_guide())168 169    # ==== Events ====170    btn_login.click(fn=login, inputs=[user, pw], outputs=[login_msg, login_box, tab_section, df_list, user_table])171    btn_submit.click(fn=bulk_submit, inputs=[df_input], outputs=[df_list])172    df_list.select(fn=lambda df, evt: df.iloc[[evt.index]] if evt.index is not None else pd.DataFrame(),173                   inputs=[df_list], outputs=[selected_df])174    btn_delete.click(fn=delete_selected_rows, inputs=[selected_df], outputs=[delete_msg, df_list])175    btn_add.click(fn=add_user, inputs=[new_user, new_pass], outputs=[user_msg, user_table])176    btn_del.click(fn=delete_user, inputs=[del_user], outputs=[user_msg, user_table])177 178app.launch()