🛡️ AAI ACL SUPREME v21.0
Module: {menu} | Active Database: v16.db
""", unsafe_allow_html=True)
# ==========================================
# 4. LOGIKA ROUTING MENU (DIBALIK LAYAR)
# ==========================================
df = st.session_state.data
if menu == "Dashboard":
c1, c2, c3 = st.columns(3)
c1.metric("Total Exposure", f"Rp {df['nilai_transaksi'].sum():,}")
c2.metric("Total Transaksi", len(df))
c3.metric("Anomali Terdeteksi", len(df[df['nilai_transaksi'] > 195000000]))
st.plotly_chart(px.line(df, x='tanggal_transaksi', y='nilai_transaksi', title="Tren Pengadaan"))
elif menu == "Transformasi Data":
st.subheader("🛠️ Data Cleaning & ETL")
uploaded_file = st.file_uploader("Upload File Baru", type=['xlsx', 'csv'])
if uploaded_file:
st.session_state.data = pd.read_excel(uploaded_file)
st.success("Data Berhasil Diperbarui!")
st.write("Preview Data Saat Ini:", df.head(10))
elif menu == "Pivot & Filtering Data":
st.subheader("Advanced Pivot & Dynamic Filtering")
# Ambil data dari session state
df_pivot = st.session_state.data.copy()
# ==========================================
# 1. MODUL DYNAMIC FILTERING (Sidebar-style inside main)
# ==========================================
with st.expander(" FILTER DATA (Klik untuk Membuka)", expanded=True):
f1, f2, f3 = st.columns(3)
# Filter berdasarkan Vendor
with f1:
vendors = st.multiselect("Pilih Vendor:", options=df_pivot['id_vendor'].unique())
if vendors:
df_pivot = df_pivot[df_pivot['id_vendor'].isin(vendors)]
# Filter berdasarkan Range Nilai
with f2:
min_val, max_val = int(df_pivot['nilai_transaksi'].min()), int(df_pivot['nilai_transaksi'].max())
range_nilai = st.slider("Rentang Nilai Transaksi:", min_val, max_val, (min_val, max_val))
df_pivot = df_pivot[(df_pivot['nilai_transaksi'] >= range_nilai[0]) & (df_pivot['nilai_transaksi'] <= range_nilai[1])]
# Filter berdasarkan Keterangan (Keyword)
with f3:
search = st.text_input("Cari di Keterangan:", "")
if search:
df_pivot = df_pivot[df_pivot['keterangan'].str.contains(search, case=False)]
st.divider()
# ==========================================
# 2. MODUL PIVOT TABLE ENGINE
# ==========================================
st.markdown("### Pivot Table Builder")
p1, p2, p3, p4 = st.columns(4)
with p1:
rows = st.selectbox("Baris (Rows):", ["id_vendor", "keterangan", "no_rekening", "jam"])
with p2:
cols = st.selectbox("Kolom (Columns):", [None, "id_vendor", "keterangan"])
with p3:
vals = st.selectbox("Nilai (Values):", ["nilai_transaksi", "jam"])
with p4:
func = st.selectbox("Fungsi (Agregasi):", ["sum", "count", "mean", "max", "min"])
# Eksekusi Pivot
try:
pivot_table = df_pivot.pivot_table(
index=rows,
columns=cols if cols else None,
values=vals,
aggfunc=func,
fill_value=0
)
# Tampilan Tabel
st.write(f"**Hasil Analisis Pivot ({func} of {vals}):**")
st.dataframe(pivot_table, use_container_width=True)
# ==========================================
# 3. VISUALISASI OTOMATIS BERDASARKAN PIVOT
# ==========================================
st.markdown("### Visualisasi Hasil Pivot")
# Jika kolom None, gunakan bar chart sederhana, jika ada kolom gunakan grouped bar
if cols:
fig_pivot = px.bar(pivot_table, barmode='group', title=f"Perbandingan {vals} per {rows} dan {cols}")
else:
fig_pivot = px.bar(pivot_table, y=vals, title=f"Total {vals} per {rows}", color=pivot_table.index)
st.plotly_chart(fig_pivot, use_container_width=True)
# Tombol Download Hasil Filter & Pivot
csv = pivot_table.to_csv().encode('utf-8')
st.download_button(
label="Download Tabel Pivot (.csv)",
data=csv,
file_name=f"pivot_audit_{datetime.now().strftime('%Y%m%d')}.csv",
mime="text/csv"
)
except Exception as e:
st.error(f"Sistem tidak dapat membuat pivot dengan kombinasi ini. Error: {e}")
elif menu == "Analisis Deskriptif":
st.subheader("📈 Ringkasan Statistik Data")
st.table(df['nilai_transaksi'].describe())
st.plotly_chart(px.histogram(df, x='nilai_transaksi', title="Distribusi Nilai Transaksi"))
elif menu == "Analisis Investigatif (6 Tab)":
t1, t2, t3, t4, t5, t6 = st.tabs(["🏗️ Split", "🎯 Threshold", "🌙 Ghost", "📅 Weekend", "🔢 Pattern", "🔍 Keyword"])
with t1:
st.write("### Potensi Pecah Kontrak (185jt - 200jt)")
st.dataframe(df[df['nilai_transaksi'].between(185000000, 199999999)])
with t3:
st.write("### Jam Kerja Tidak Wajar")
st.dataframe(df[(df['jam'] >= 22) | (df['jam'] <= 5)])
with t6:
st.write("### Scan Kata Kunci")
st.dataframe(df[df['keterangan'].str.contains('Fee|Koordinasi|Titipan', case=False)])
elif menu == "Network Link Analysis":
st.subheader("🕸️ Network Affiliation Map")
net = Network(height='500px', width='100%', bgcolor='#222222', font_color='white')
# Contoh Link Vendor - Rekening
for i, row in df.head(20).iterrows():
net.add_node(row['id_vendor'], label=row['id_vendor'], color='#3b82f6')
net.add_node(row['no_rekening'], label=f"Rek:{row['no_rekening']}", color='#ef4444', shape='diamond')
net.add_edge(row['id_vendor'], row['no_rekening'])
net.save_graph("net.html")
components.html(open("net.html", 'r').read(), height=550)
elif menu == "Diagnostik & Anomali":
st.subheader("🧬 Gap & Duplicate Analysis")
dupes = df[df.duplicated(subset=['nilai_transaksi', 'id_vendor'], keep=False)]
st.error("Ditemukan Transaksi Duplikat (Nilai & Vendor Sama):")
st.dataframe(dupes)
elif menu == "Analisis Prediktif":
st.subheader("🔮 Forecasting Risiko")
st.info(f"Tren Pengadaan Bulan Depan Diprediksi: {AuditEngine.run_analysis(df)['trend']}")
st.plotly_chart(px.scatter(df, x='tanggal_transaksi', y='nilai_transaksi', trendline="ols"))
elif menu == "Relasional (COI)":
st.subheader("🧬 Conflict of Interest (COI) Scan")
st.info("Modul ini membandingkan Nomor Rekening Vendor dengan Nomor Rekening Karyawan.")
file_emp = st.file_uploader("Unggah Database Karyawan (.xlsx)", type=['xlsx'])
if file_emp:
df_emp = pd.read_excel(file_emp)
# Pastikan kolom rekening ada di kedua dataframe
if 'no_rekening' in df_emp.columns and 'no_rekening' in st.session_state.data.columns:
# Cari irisan (inner join) berdasarkan no_rekening
temuan_coi = pd.merge(
st.session_state.data,
df_emp[['nama_karyawan', 'no_rekening']],
on='no_rekening',
how='inner'
)
if not temuan_coi.empty:
st.error(f"🧬 TEMUAN KRITIS: Ditemukan {len(temuan_coi)} transaksi dengan rekening yang identik antara Vendor dan Karyawan!")
# Tampilkan kolom-kolom kunci saja agar jelas
st.dataframe(temuan_coi[['tanggal_transaksi', 'id_vendor', 'nilai_transaksi', 'no_rekening', 'nama_karyawan']])
else:
st.success("🧬 Bersih: Tidak ditemukan kecocokan nomor rekening antara Vendor dan Karyawan.")
else:
st.warning("Pastikan kedua file memiliki kolom 'no_rekening'.")
elif menu == "Pelaporan (LHA)":
st.subheader("📑 Automated LHA Report Generator")
if st.button("🚀 EXPORT DATA TEMUAN KE WORD"):
# Jalankan ulang analisis untuk ambil data terbaru
res = AuditEngine.run_analysis(df)
doc = docx.Document()
# --- HEADER LAPORAN ---
title = doc.add_heading('LAPORAN HASIL ANALISIS INVESTIGASI (LHA)', 0)
title.alignment = 1 # Center
doc.add_paragraph(f"Tanggal Cetak: {datetime.now().strftime('%d-%m-%Y %H:%M')}")
doc.add_paragraph(f"Total Populasi: {len(df)} Transaksi")
# --- FUNGSI OTOMATIS TAMBAH TABEL ---
def create_table_in_word(dataframe, title_text):
doc.add_heading(title_text, level=1)
if not dataframe.empty:
# Ambil 10 kolom pertama saja agar tidak kepotong di Word
df_report = dataframe.head(50)
table = doc.add_table(rows=1, cols=len(df_report.columns))
table.style = 'Table Grid'
# Header Tabel
hdr_cells = table.rows[0].cells
for i, col_name in enumerate(df_report.columns):
hdr_cells[i].text = str(col_name)
# Isi Data
for _, row in df_report.iterrows():
row_cells = table.add_row().cells
for i, val in enumerate(row):
row_cells[i].text = str(val)
else:
doc.add_paragraph("Tidak ditemukan temuan pada kategori ini.")
# --- REPLIKASI TABEL TEMUAN ---
# 1. Tabel Split Purchase
create_table_in_word(res['split'], "I. Temuan Potensi Pecah Kontrak (Split Purchase)")
# 2. Tabel Ghost Hours
create_table_in_word(res['ghost'], "II. Temuan Transaksi Jam Tidak Wajar (Ghost Hours)")
# 3. Tabel Weekend
create_table_in_word(res['weekend'], "III. Temuan Transaksi Hari Libur (Weekend)")
# 4. Tabel Keywords (Opsional jika ada)
keyword_finds = df[df['keterangan'].str.contains('Fee|Koordinasi|Titipan', case=False)]
create_table_in_word(keyword_finds, "IV. Temuan Kata Kunci Berisiko (Keywords)")
# --- SIMPAN DAN DOWNLOAD ---
buf = io.BytesIO()
doc.save(buf)
st.success("Laporan Berhasil Disusun!")
st.download_button(
label="📥 DOWNLOAD LHA LENGKAP (.docx)",
data=buf.getvalue(),
file_name=f"LHA_AUDIT_{datetime.now().strftime('%Y%m%d')}.docx",
mime="application/vnd.openxmlformats-officedocument.wordprocessingml.document"
)
elif menu == "Logout":
st.info("Sesi Berakhir. Silakan tutup tab ini.")
st.stop()
# --- ?? LOGIKA MENU: Document Similarity Detection ---
elif menu == "Document Similarity Detection":
st.subheader("Analisis Kesamaan Konten Isi Dokumen")
# --- TEMPAT UPLOAD FILE ---
st.markdown("Silakan Upload Dokumen Audit Anda")
files = st.file_uploader(
"Pilih file (PDF, DOCX, XLSX, atau CSV)",
accept_multiple_files=True,
type=['pdf', 'docx', 'xlsx', 'csv'],
key="uploader_kesamaan"
)
# --- TOMBOL EKSEKUSI ---
if files:
st.success(f"? {len(files)} dokumen siap dianalisis.")
kol1, kol2 = st.columns(2)
with kol1:
if st.button("?? Jalankan Analisis"):
st.info("Sedang mengekstrak teks dan membandingkan isi...")
# Nanti kita isi logika skor kemiripannya di sini
with kol2:
if st.button("??? Bersihkan"):
st.rerun()
else:
st.info("Silakan unggah minimal 2 dokumen untuk dibandingkan tingkat kemiripannya.")
# --- Tombol Keluar Sistem (Masih pakai yang lama agar tetap jalan) ---
if st.sidebar.button("Keluar Sistem"):
# Hapus semua sisa session state yang masih nyangkut
for key in st.session_state.keys():
del st.session_state[key]
# Matikan status logged_in secara paksa
st.session_state.logged_in = False
# Paksa aplikasi untuk rerun dan otomatis mental ke login
st.rerun()
# --- FOOTER SIDEBAR (SAFE MODE) ---
st.sidebar.markdown("---") # Garis pembatas
st.sidebar.write("Developed by Om Bro EA")
st.sidebar.caption("@Akademi Audit Indonesia - 2026")