# pages/input_data_penyakit.py (VERSI FINAL - MULTI-FILE, DETEKSI DUPLIKAT, HAPUS & GANTI) from dash import dcc, html, dash_table, Input, Output, State, callback, ctx, no_update import dash_bootstrap_components as dbc import base64 import io import pandas as pd import zipfile from sqlalchemy import insert, delete, select, and_ import uuid import json # Import json untuk dcc.Store from database import engine, data_penyakit, detail_penyakit, users # --- Kamus untuk Memetakan Nama Bulan ke Angka --- MONTH_MAPPING = { 'januari': '01', 'jan': '01', 'februari': '02', 'feb': '02', 'maret': '03', 'mar': '03', 'april': '04', 'apr': '04', 'mei': '05', 'juni': '06', 'jun': '06', 'juli': '07', 'jul': '07', 'agustus': '08', 'ags': '08', 'agu': '08', 'september': '09', 'sep': '09', 'sept': '09', 'oktober': '10', 'okt': '10', 'november': '11', 'nov': '11', 'desember': '12', 'des': '12', } # --- Kolom Mapping (Definisikan di luar callback) --- KOLOM_MAPPING = { 'Tahun': 'tahun', 'Kode pusk': 'kode_pusk', 'ICD X': 'icd_x', 'Jenis Penyakit': 'jenis_penyakit', 'Totall': 'totall', 'Laki-Laki': 'laki_laki', 'Perempuan': 'perempuan', 'Kasus Baru': 'kasus_baru', 'Kasus Lama': 'kasus_lama', '0-7 hr (Baru)': 'usia_0_7_hr_baru', '0-7 hr (Lama)': 'usia_0_7_hr_lama', '8-28 hr (Baru)': 'usia_8_28_hr_baru', '8-28 hr (Lama)': 'usia_8_28_hr_lama', '1bl-1 th (Baru)': 'usia_1bl_1th_baru', '1bl-1 th (Lama)': 'usia_1bl_1th_lama', '1-4 th (Baru)': 'usia_1_4th_baru', '1-4 th (Lama)': 'usia_1_4th_lama', '5-9 th (Baru)': 'usia_5_9th_baru', '5-9 th (Lama)': 'usia_5_9th_lama', '10-14 th (Baru)': 'usia_10_14th_baru', '10-14 th (Lama)': 'usia_10_14th_lama', '15-19 th (Baru)': 'usia_15_19th_baru', '15-19 th (Lama)': 'usia_15_19th_lama', '20-44 th (Baru)': 'usia_20_44th_baru', '20-44 th (Lama)': 'usia_20_44th_lama', '45-54 th (Baru)': 'usia_45_54th_baru', '45-54 th (Lama)': 'usia_45_54th_lama', '55-59 th (Baru)': 'usia_55_59th_baru', '55-59 th (Lama)': 'usia_55_59th_lama', '60-69 (Baru)': 'usia_60_69th_baru', '60-69 (Lama)': 'usia_60_69th_lama', '70+ (Baru)': 'usia_70pl_baru', '70+ (Lama)': 'usia_70pl_lama', } # --- Layout Halaman Input --- layout = dbc.Container([ # Tempat penyimpanan data sementara di browser dcc.Store(id='temp-file-storage'), html.H3("Input Data Penyakit", className="mt-4 mb-4 text-center"), dcc.Upload( id='upload-data-multi', children=html.Div(['Drag and Drop atau ', html.A('Pilih File Laporan (.zip, .xlsx, .xls)')]), style={'width': '100%', 'height': '60px', 'lineHeight': '60px', 'borderWidth': '1px', 'borderStyle': 'dashed', 'borderRadius': '5px', 'textAlign': 'center', 'marginBottom': '20px'}, accept=".zip,.xlsx,.xls", multiple=True ), html.Div(id='file-list-preview', className="mb-3"), dbc.Row([ dbc.Col(dbc.Button("Unggah ke Database", id='submit-button-multi', color="success", className="w-100 mb-2"), md=6), dbc.Col(dbc.Button("Tampilkan Daftar Data Terunggah", id='show-uploaded-data', color="info", className="w-100"), md=6) ], justify="center", className="mb-3"), # Tempat notifikasi dan tombol konfirmasi html.Div(id='upload-alert-wrapper', className="mt-3", children=[ html.Div(id='upload-alert'), dbc.Row([ dbc.Col(dbc.Button("Hapus & Ganti Data", id="replace-multi-button", color="danger", className="me-2", style={'display': 'none'}), width="auto"), dbc.Col(dbc.Button("Batal", id="cancel-multi-button", color="secondary", style={'display': 'none'}), width="auto") ], justify="center", className="mt-2") ]), html.Div(id='uploaded-data-table', className="mt-4") ], fluid=True) # --- Fungsi Helper --- def safe_int_convert(value, default=0): if pd.isna(value) or str(value).strip() == '': return default try: return int(float(value)) except (ValueError, TypeError): return default def parse_file_and_get_metadata(contents, filename): # ... (fungsi ini tidak berubah) ... extracted_month = None for month_name, month_number in MONTH_MAPPING.items(): if month_name in filename.lower(): extracted_month = month_number break if not extracted_month: raise ValueError(f"Bulan tidak dapat ditemukan dari nama file '{filename}'.") content_type, content_string = contents.split(',') decoded = base64.b64decode(content_string) df = pd.read_excel(io.BytesIO(decoded), header=None) if df.shape[0] < 3: raise ValueError("Format file Excel tidak sesuai: header diharapkan di baris ke-3.") header_row = df.iloc[2].astype(str).str.strip() df_cleaned = df.iloc[3:].copy() df_cleaned.columns = header_row df_cleaned.reset_index(drop=True, inplace=True) if df_cleaned.empty: raise ValueError("Tidak ada baris data valid setelah header.") tahun = safe_int_convert(df_cleaned['Tahun'].iloc[0]) puskesmas = str(df_cleaned['Kode pusk'].iloc[0]).strip() if not tahun or not puskesmas: raise ValueError("Kolom 'Tahun' atau 'Kode pusk' di baris pertama data tidak valid.") return df_cleaned, extracted_month, tahun, puskesmas def insert_records(conn, file_data, user_id): # ... Fungsi untuk melakukan insert, agar bisa dipanggil ulang ... upload_session = str(uuid.uuid4()) df_json = file_data['df'].to_json(orient='records', date_format='iso') stmt_master = insert(data_penyakit).values( id_user=user_id, nama_file=file_data['filename'], bulan=file_data['month'], tahun=file_data['year'], puskesmas=file_data['pusk'], dataframe_csv=df_json, upload_session=upload_session ) result_master = conn.execute(stmt_master) master_id = result_master.inserted_primary_key[0] records_detail = [] for _, row in file_data['df'].iterrows(): detail_record = {'id_data_penyakit': master_id, 'tahun': file_data['year'], 'bulan': file_data['month'], 'kode_pusk': file_data['pusk'], 'upload_session': upload_session} for excel_col, db_col in KOLOM_MAPPING.items(): if excel_col not in ['Tahun', 'Kode pusk']: detail_record[db_col] = safe_int_convert(row.get(excel_col)) if 'usia' in db_col or db_col in ['totall','laki_laki','perempuan','kasus_baru','kasus_lama'] else (str(row.get(excel_col)).strip() if pd.notna(row.get(excel_col)) else None) records_detail.append(detail_record) if records_detail: conn.execute(insert(detail_penyakit), records_detail) return True # --- Callback untuk Preview Daftar File --- @callback(Output('file-list-preview', 'children'), Input('upload-data-multi', 'filename'), prevent_initial_call=True) def update_file_list(filenames): if not filenames: return "" return html.Div([html.H5("File yang akan diunggah:"), dbc.ListGroup([dbc.ListGroupItem(f) for f in filenames])]) # --- TAHAP 1: Cek Duplikat Saat Tombol "Unggah" Diklik --- @callback( Output('upload-alert', 'children'), Output('replace-multi-button', 'style'), Output('cancel-multi-button', 'style'), Output('temp-file-storage', 'data'), Input('submit-button-multi', 'n_clicks'), State('upload-data-multi', 'contents'), State('upload-data-multi', 'filename'), State('login-status', 'data'), prevent_initial_call=True ) def check_duplicates_and_store(n_clicks, list_of_contents, list_of_filenames, login_data): hidden, visible = {'display': 'none'}, {'display': 'inline-block'} if not n_clicks or not list_of_contents: return no_update, hidden, hidden, None user_id = login_data.get('id_user') files_to_process, initial_errors = [], [] for content, filename in zip(list_of_contents, list_of_filenames): try: df, month, year, pusk = parse_file_and_get_metadata(content, filename) files_to_process.append({ 'df_json': df.to_json(orient='split'), # Simpan df sebagai json 'filename': filename, 'month': month, 'year': year, 'pusk': pusk }) except Exception as e: initial_errors.append(f"Gagal memproses '{filename}': {e}") if initial_errors: return dbc.Alert([html.P("Beberapa file gagal divalidasi:"), html.Ul([html.Li(e) for e in initial_errors])], color='danger'), hidden, hidden, None unique_files, duplicate_files_meta = [], [] with engine.connect() as conn: for file_data in files_to_process: is_duplicate = conn.execute(select(data_penyakit).where(and_( data_penyakit.c.bulan == file_data['month'], data_penyakit.c.tahun == file_data['year'], data_penyakit.c.puskesmas == file_data['pusk'] )).limit(1)).first() if is_duplicate: duplicate_files_meta.append(file_data) else: unique_files.append(file_data) # Simpan semua file yang akan diproses ke dcc.Store stored_data = {'unique_files': unique_files, 'duplicate_files': duplicate_files_meta} if not duplicate_files_meta: # KASUS 1: Tidak ada duplikat sama sekali with engine.connect() as conn: for file_data in unique_files: file_data['df'] = pd.read_json(io.StringIO(file_data['df_json']), orient='split') insert_records(conn, file_data, user_id) conn.commit() return dbc.Alert(f"✅ Berhasil mengunggah {len(unique_files)} file baru.", color="success"), hidden, hidden, None else: # KASUS 2: Ada duplikat, tampilkan konfirmasi alert_msg = [html.P(f"⚠️ Ditemukan {len(duplicate_files_meta)} data yang sudah ada di database:")] alert_msg.append(html.Ul([html.Li(f['filename']) for f in duplicate_files_meta])) if unique_files: alert_msg.append(html.P(f"({len(unique_files)} file lainnya adalah data baru).")) alert_msg.append(html.P("Apakah Anda ingin menghapus data lama dan mengganti semuanya dengan yang baru?", className="mt-2 fw-bold")) return dbc.Alert(alert_msg, color="warning"), visible, visible, json.dumps(stored_data) # --- TAHAP 2: Aksi Hapus & Ganti Jika Tombol Diklik --- @callback( Output('upload-alert', 'children', allow_duplicate=True), Output('replace-multi-button', 'style', allow_duplicate=True), Output('cancel-multi-button', 'style', allow_duplicate=True), Output('temp-file-storage', 'data', allow_duplicate=True), Input('replace-multi-button', 'n_clicks'), State('temp-file-storage', 'data'), State('login-status', 'data'), prevent_initial_call=True ) def execute_replace_multi(n_clicks, stored_data_json, login_data): hidden = {'display': 'none'} if not n_clicks or not stored_data_json: return no_update, hidden, hidden, no_update stored_data = json.loads(stored_data_json) user_id = login_data.get('id_user') all_files_to_upload = stored_data['unique_files'] + stored_data['duplicate_files'] with engine.connect() as conn: # Hapus data lama yang duplikat for file_data in stored_data['duplicate_files']: stmt_find_old = select(data_penyakit.c.id_data_penyakit).where(and_( data_penyakit.c.bulan == file_data['month'], data_penyakit.c.tahun == file_data['year'], data_penyakit.c.puskesmas == file_data['pusk'] )) old_ids = [row[0] for row in conn.execute(stmt_find_old).fetchall()] if old_ids: conn.execute(delete(detail_penyakit).where(detail_penyakit.c.id_data_penyakit.in_(old_ids))) conn.execute(delete(data_penyakit).where(data_penyakit.c.id_data_penyakit.in_(old_ids))) # Insert semua data baru (baik yang tadinya unik maupun duplikat) for file_data in all_files_to_upload: file_data['df'] = pd.read_json(io.StringIO(file_data['df_json']), orient='split') insert_records(conn, file_data, user_id) conn.commit() return dbc.Alert(f"✅ Berhasil! Data lama diganti dan {len(all_files_to_upload)} file telah diunggah.", color="success"), hidden, hidden, None # --- TAHAP 2: Aksi Batal Jika Tombol Diklik --- @callback( Output('upload-alert', 'children', allow_duplicate=True), Output('replace-multi-button', 'style', allow_duplicate=True), Output('cancel-multi-button', 'style', allow_duplicate=True), Output('temp-file-storage', 'data', allow_duplicate=True), Input('cancel-multi-button', 'n_clicks'), prevent_initial_call=True ) def cancel_multi_upload(n_clicks): hidden = {'display': 'none'} if n_clicks: return dbc.Alert("Proses unggah dibatalkan.", color="info"), hidden, hidden, None return no_update, hidden, hidden, no_update # --- Callback untuk Menampilkan Tabel Data Terunggah --- @callback( Output('uploaded-data-table', 'children'), [Input('show-uploaded-data', 'n_clicks'), Input('submit-button-multi', 'n_clicks'), Input('replace-multi-button', 'n_clicks')], prevent_initial_call=True ) def show_uploaded_files(n_show, n_submit, n_replace): # ... (fungsi ini tidak berubah) ... if not ctx.triggered: return "" with engine.connect() as conn: stmt = select(data_penyakit.c.id_data_penyakit.label('ID'), data_penyakit.c.nama_file.label('Nama File'), data_penyakit.c.bulan.label('Bulan'), data_penyakit.c.tahun.label('Tahun'), data_penyakit.c.puskesmas.label('Puskesmas'), users.c.username.label('Diunggah Oleh')).join(users, data_penyakit.c.id_user == users.c.id_user, isouter=True).order_by(data_penyakit.c.id_data_penyakit.desc()) df = pd.DataFrame(conn.execute(stmt).fetchall()) if df.empty: return dbc.Alert("Belum ada data yang diunggah.", color="info") return html.Div([html.H5("Data Terunggah ke Database:", className="mt-4"), dash_table.DataTable(columns=[{"name": i, "id": i} for i in df.columns], data=df.to_dict('records'), page_size=10, style_table={'overflowX': 'auto'})])