Spaces:
Sleeping
Sleeping
| import os | |
| import gradio as gr | |
| import pandas as pd | |
| EXCEL_FILE = "database.xlsx" | |
| # ======= Helper functions for Excel storage ======= | |
| def load_data(): | |
| if os.path.exists(EXCEL_FILE): | |
| try: | |
| xls = pd.ExcelFile(EXCEL_FILE) | |
| df_data = pd.read_excel(xls, "DATA") | |
| df_users = pd.read_excel(xls, "USERS") | |
| return df_data, df_users | |
| except Exception as e: | |
| print(f"Error loading Excel file: {e}") | |
| # fallback empty | |
| # If file does not exist or error, create empty | |
| df_data = pd.DataFrame(columns=["USERNAME", "COUNTRYCODE", "SBUCODE", "BRANDCODE", "CONCEPTCODE", "CHANNEL", "REMARK"]) | |
| df_users = pd.DataFrame([{"USERNAME": "ADMIN", "PASSWORD": "ADMIN"}]) | |
| save_data(df_data, df_users) | |
| return df_data, df_users | |
| def save_data(df_data, df_users): | |
| with pd.ExcelWriter(EXCEL_FILE, engine="openpyxl") as writer: | |
| df_data.to_excel(writer, sheet_name="DATA", index=False) | |
| df_users.to_excel(writer, sheet_name="USERS", index=False) | |
| # ======= Global Data Store ======= | |
| df, users_df = load_data() | |
| # ======= Your existing helper functions ======= | |
| def to_upper(df_input): | |
| return df_input.applymap(lambda x: str(x).upper().strip() if pd.notnull(x) else x) | |
| def get_active_data(): | |
| return df[df["REMARK"].str.upper() != "DELETE"].reset_index(drop=True) | |
| def login(username, password): | |
| username = username.strip().upper() | |
| password = password.strip() | |
| is_valid = not users_df[(users_df["USERNAME"] == username) & (users_df["PASSWORD"] == password)].empty | |
| if is_valid: | |
| return "", gr.update(visible=False), gr.update(visible=True), get_active_data(), show_users() | |
| return "Invalid login", gr.update(visible=True), gr.update(visible=False), pd.DataFrame(), pd.DataFrame() | |
| def bulk_submit(dataframe): | |
| global df | |
| new_df = pd.DataFrame(dataframe).dropna(how="all") | |
| if new_df.empty: | |
| return get_active_data() | |
| new_df = to_upper(new_df) | |
| required = ["USERNAME", "COUNTRYCODE", "SBUCODE", "BRANDCODE", "CONCEPTCODE", "CHANNEL", "REMARK"] | |
| new_df = new_df[required].dropna() | |
| pk_cols = required | |
| merged = pd.merge(new_df, df[pk_cols], on=pk_cols, how="left", indicator=True) | |
| df_filtered = new_df[merged["_merge"] == "left_only"] | |
| df = pd.concat([df, df_filtered], ignore_index=True) | |
| save_data(df, users_df) # SAVE after submit | |
| return get_active_data() | |
| def delete_selected_rows(selected_df): | |
| global df | |
| try: | |
| if selected_df.empty: | |
| return "Tidak ada baris yang dipilih", get_active_data() | |
| for _, row in selected_df.iterrows(): | |
| mask = ( | |
| (df["USERNAME"] == row["USERNAME"]) & | |
| (df["COUNTRYCODE"] == row["COUNTRYCODE"]) & | |
| (df["SBUCODE"] == row["SBUCODE"]) & | |
| (df["BRANDCODE"] == row["BRANDCODE"]) & | |
| (df["CONCEPTCODE"] == row["CONCEPTCODE"]) & | |
| (df["CHANNEL"] == row["CHANNEL"]) & | |
| (df["REMARK"].str.upper() != "DELETE") | |
| ) | |
| df.loc[mask, "REMARK"] = "DELETE" | |
| save_data(df, users_df) # SAVE after delete | |
| return "Berhasil dihapus.", get_active_data() | |
| except Exception as e: | |
| return f"Error: {e}", get_active_data() | |
| def add_user(username, password): | |
| global users_df | |
| username = username.strip().upper() | |
| password = password.strip() | |
| if not username or not password: | |
| return "Username/password tidak boleh kosong", show_users() | |
| if username in users_df["USERNAME"].values: | |
| return "Username sudah ada", show_users() | |
| users_df = pd.concat([users_df, pd.DataFrame([{"USERNAME": username, "PASSWORD": password}])], ignore_index=True) | |
| save_data(df, users_df) # SAVE after add user | |
| return f"User {username} ditambahkan", show_users() | |
| def delete_user(username): | |
| global users_df | |
| username = username.strip().upper() | |
| if username == "ADMIN": | |
| return "ADMIN tidak boleh dihapus", show_users() | |
| users_df = users_df[users_df["USERNAME"] != username].reset_index(drop=True) | |
| save_data(df, users_df) # SAVE after delete user | |
| return f"User {username} dihapus", show_users() | |
| def show_users(): | |
| return users_df.copy() | |
| def show_guide(): | |
| return """ | |
| ### Panduan Penggunaan Aplikasi | |
| 1. **Login** | |
| - Gunakan username dan password untuk masuk. | |
| 2. **Input Data** | |
| - Lihat data aktif. | |
| - Input data baru dengan kolom lengkap. | |
| - Pilih baris dan klik "Delete Selected Rows" untuk tandai delete. | |
| 3. **Manajemen User** | |
| - Tambah dan hapus user (kecuali ADMIN). | |
| 4. **Data yang dihapus tidak benar-benar dihapus**, hanya ditandai `REMARK = DELETE`. | |
| """ | |
| # ======= UI Layout and Events (unchanged) ======= | |
| with gr.Blocks() as app: | |
| gr.Markdown("## Login") | |
| login_box = gr.Column(visible=True) | |
| with login_box: | |
| user = gr.Textbox(label="Username") | |
| pw = gr.Textbox(label="Password", type="password") | |
| btn_login = gr.Button("Login") | |
| login_msg = gr.Textbox(visible=True, interactive=False, show_label=False) | |
| tab_section = gr.Tabs(visible=False) | |
| with tab_section: | |
| with gr.Tab("Input Data"): | |
| gr.Markdown("### Data Aktif") | |
| df_list = gr.Dataframe( | |
| label="List Data Aktif", | |
| interactive=False, | |
| column_widths=["auto"]*7, | |
| wrap=True, | |
| show_copy_button=True, | |
| show_download_button=True, | |
| show_fullscreen_button=True, | |
| show_search=True, | |
| show_row_numbers=True, | |
| pinned_columns=["USERNAME"] | |
| ) | |
| selected_df = gr.Dataframe(visible=False) | |
| gr.Line() | |
| gr.Markdown("### Input Data Baru") | |
| df_input = gr.Dataframe( | |
| headers=["USERNAME", "COUNTRYCODE", "SBUCODE", "BRANDCODE", "CONCEPTCODE", "CHANNEL", "REMARK"], | |
| row_count=5, | |
| col_count=(7, "fixed"), | |
| label="Form Input" | |
| ) | |
| btn_submit = gr.Button("Submit Data") | |
| btn_delete = gr.Button("Delete Selected Rows") | |
| delete_msg = gr.Textbox(visible=True, interactive=False, show_label=False) | |
| with gr.Tab("User Management"): | |
| gr.Markdown("### Kelola User") | |
| new_user = gr.Textbox(label="New Username") | |
| new_pass = gr.Textbox(label="New Password") | |
| btn_add = gr.Button("Add User") | |
| del_user = gr.Textbox(label="Delete Username") | |
| btn_del = gr.Button("Delete User") | |
| user_msg = gr.Textbox(visible=True, interactive=False, show_label=False) | |
| user_table = gr.Dataframe(label="User List", interactive=False) | |
| with gr.Tab("User Guide"): | |
| gr.Markdown(show_guide()) | |
| # ==== Events ==== | |
| btn_login.click(fn=login, inputs=[user, pw], outputs=[login_msg, login_box, tab_section, df_list, user_table]) | |
| btn_submit.click(fn=bulk_submit, inputs=[df_input], outputs=[df_list]) | |
| df_list.select(fn=lambda df, evt: df.iloc[[evt.index]] if evt.index is not None else pd.DataFrame(), | |
| inputs=[df_list], outputs=[selected_df]) | |
| btn_delete.click(fn=delete_selected_rows, inputs=[selected_df], outputs=[delete_msg, df_list]) | |
| btn_add.click(fn=add_user, inputs=[new_user, new_pass], outputs=[user_msg, user_table]) | |
| btn_del.click(fn=delete_user, inputs=[del_user], outputs=[user_msg, user_table]) | |
| app.launch(auth=lambda u, p: (u.upper(), p) in zip(users_df["USERNAME"], users_df["PASSWORD"])) |