from flask import Flask, request, jsonify, send_file, render_template import pandas as pd import io, re, datetime, os, uuid, traceback, copy from openpyxl import load_workbook from openpyxl.styles import Alignment from openpyxl.utils import get_column_letter from difflib import SequenceMatcher from werkzeug.utils import secure_filename app = Flask(__name__) app.config['MAX_CONTENT_LENGTH'] = 50 * 1024 * 1024 # 50MB UPLOAD_FOLDER = "uploads" OUTPUT_FOLDER = "outputs" os.makedirs(UPLOAD_FOLDER, exist_ok=True) os.makedirs(OUTPUT_FOLDER, exist_ok=True) # ========================================================= # HELPERS # ========================================================= def clean_col(col): if col is None or (isinstance(col, float) and pd.isna(col)): return "" col = str(col) col = col.replace("\n", " ").replace("\t", " ") col = col.replace("\u201c", '"').replace("\u201d", '"') col = col.replace("\u2018", "'").replace("\u2019", "'") col = col.replace("\u2013", "-").replace("\u2014", "-") col = re.sub(r"\s+", " ", col).strip() return col def make_unique_columns(cols): seen = {} out = [] for c in cols: if c not in seen: seen[c] = 0 out.append(c) else: seen[c] += 1 out.append(f"{c}_{seen[c]}") return out def is_similar(a, b, threshold=0.75): return SequenceMatcher(None, clean_col(a).lower(), clean_col(b).lower()).ratio() >= threshold def format_phone(val): if val is None or (isinstance(val, float) and pd.isna(val)): return None # Convert float (e.g. 6282384193061.0) to int first to avoid floating point digits if isinstance(val, float): try: return str(int(val)) except (ValueError, OverflowError): pass s = str(val).strip() try: if "e" in s.lower() or "." in s: s = str(int(float(s))) except Exception: pass d = "".join(c for c in s if c.isdigit()) return d if d else None def parse_bulan(val): if val is None or (isinstance(val, float) and pd.isna(val)): return None # Tambah ini — handle pandas NaT try: if pd.isnull(val): return None except Exception: pass if isinstance(val, (datetime.datetime, datetime.date)): return val.strftime("%B") s = str(val).strip() try: return datetime.datetime.strptime(s, "%b '%y").strftime("%B") except: return s MONTH_MAP = { 'jan': 'januari', 'january': 'januari', 'januari': 'januari', 'feb': 'februari', 'february': 'februari', 'februari': 'februari', 'mar': 'maret', 'march': 'maret', 'maret': 'maret', 'apr': 'april', 'april': 'april', 'may': 'mei', 'mei': 'mei', 'jun': 'juni', 'june': 'juni', 'jul': 'juli', 'july': 'juli', 'aug': 'agustus', 'august': 'agustus', 'agustus': 'agustus', 'sep': 'september', 'sept': 'september', 'september': 'september', 'oct': 'oktober', 'october': 'oktober', 'oktober': 'oktober', 'nov': 'november', 'november': 'november', 'dec': 'desember', 'december': 'desember', 'desember': 'desember', } NUM_MONTHS = { '1': 'januari', '01': 'januari', '2': 'februari', '02': 'februari', '3': 'maret', '03': 'maret', '4': 'april', '04': 'april', '5': 'mei', '05': 'mei', '6': 'juni', '06': 'juni', '7': 'juli', '07': 'juli', '8': 'agustus', '08': 'agustus', '9': 'september', '09': 'september', '10': 'oktober', '11': 'november', '12': 'desember', } def normalize_bulan(val): if val is None or (isinstance(val, float) and pd.isna(val)): return "" if isinstance(val, (datetime.datetime, datetime.date)): return val.strftime("%B").lower() text = str(val).strip().lower() if not text: return "" # Try numeric month if text in NUM_MONTHS: return NUM_MONTHS[text] # Try friendly formats like '03/2024' or '2024-03-01' for fmt in ("%m/%d/%Y", "%d/%m/%Y", "%Y-%m-%d", "%m/%d/%y", "%d-%m-%Y", "%Y/%m/%d"): try: return datetime.datetime.strptime(text, fmt).strftime("%B").lower() except ValueError: continue # Normalize text by removing punctuation and spaces alpha = ''.join(ch for ch in text if ch.isalpha()) if alpha in MONTH_MAP: return MONTH_MAP[alpha] return text def read_neurosensum(content, sheet_name, skip_col0=True, header_row_count=3, info_cols_end=8): wb = load_workbook(io.BytesIO(content), data_only=True) ws = wb[sheet_name] rows_raw = list(ws.iter_rows(values_only=True)) r1 = rows_raw[0] r2 = rows_raw[1] r3 = rows_raw[2] headers = [] for i in range(len(r1)): if skip_col0 and i == 0: headers.append("__SKIP__") elif i < info_cols_end: val = str(r1[i]).replace("\n", " ").strip() if r1[i] else f"__COL_{i}__" headers.append(val) else: val = r3[i] if r3[i] is not None else (r2[i] if r2[i] is not None else f"__COL_{i}__") if isinstance(val, str) and val.strip() == "": val = f"__COL_{i}__" headers.append(val) data = [rows_raw[i] for i in range(header_row_count, len(rows_raw))] df = pd.DataFrame(data, columns=headers) drop = [c for c in df.columns if str(c).startswith("__")] df = df.drop(columns=drop) df = df.dropna(how="all").reset_index(drop=True) return df def apply_formatting(output_file, output_sheet, scale_cols, hp_col_name="HP"): wb2 = load_workbook(output_file) ws_out = wb2[output_sheet] header_row = [cell.value for cell in ws_out[1]] center_align = Alignment(horizontal="center", vertical="center") left_align = Alignment(horizontal="left", vertical="center") hp_col_idx = None for i, h in enumerate(header_row): if h == hp_col_name: hp_col_idx = i + 1 break for row in ws_out.iter_rows(min_row=2, max_row=ws_out.max_row): for cell in row: col_name = header_row[cell.column - 1] if hp_col_idx and cell.column == hp_col_idx: if cell.value is not None: raw = str(cell.value).strip() try: if 'e' in raw.lower() or ('.' in raw): raw = str(int(float(raw))) except Exception: pass if raw.endswith('.0'): raw = raw[:-2] cell.value = raw cell.data_type = 's' cell.number_format = "@" cell.alignment = left_align elif col_name in scale_cols: cell.alignment = center_align wb2.save(output_file) def spk_date_to_bulan(val): """Convert SPK DATE string like '01/23/2026' or datetime to month name.""" if val is None or (isinstance(val, float) and pd.isna(val)): return None if isinstance(val, (datetime.datetime, datetime.date)): return val.strftime("%B") s = str(val).strip() for fmt in ("%m/%d/%Y", "%d/%m/%Y", "%Y-%m-%d", "%m/%d/%y", "%d-%m-%Y"): try: return datetime.datetime.strptime(s, fmt).strftime("%B") except ValueError: continue return None def validate_exact(df, expected, filename, label=""): cols = list(df.columns) if len(cols) != len(expected): raise Exception(f"[ERROR] {label} '{filename}'\nJumlah kolom beda: expected {len(expected)}, actual {len(cols)}") for i, (a, b) in enumerate(zip(cols, expected)): if a != b: raise Exception(f"[ERROR] {label} '{filename}'\nIndex {i}\nExpected: {b}\nActual: {a}") def validate_flexible(df, expected, flexible, filename, label=""): cols = list(df.columns) if len(cols) != len(expected): raise Exception(f"[ERROR] {label} '{filename}'\nJumlah kolom beda: expected {len(expected)}, actual {len(cols)}") for i, (a, b) in enumerate(zip(cols, expected)): if b in flexible: if not is_similar(a, b): raise Exception(f"[ERROR] {label} '{filename}'\nIndex {i} (flexible)\nExpected: {b}\nActual: {a}") else: if a != b: raise Exception(f"[ERROR] {label} '{filename}'\nIndex {i}\nExpected: {b}\nActual: {a}") # ========================================================= # TEMPLATES / EXPECTED COLUMNS # ========================================================= EXPECTED_NEW = [clean_col(c) for c in [ "Submitted On"," Terima kasih atas kesediaan Bapak/Ibu. Silakan tuliskan nama lengkap Anda.", " Apa hubungan Bapak/Ibu dengan pemegang polis?", "Apakah Bapak/Ibu mengalami secara langsung proses tersebut?", "Manakah media pembelian asuransi berikut ini yang Bapak/Ibu gunakan saat membeli asuransi?", "Apakah Bapak/Ibu pernah berinteraksi dengan petugas marketing Asuransi Astra?", "Apakah Bapak/Ibu pernah bertemu langsung dengan petugas marketing Asuransi Astra?", "Apakah Bapak/Ibu pernah berinteraksi melalui telepon?", "Apakah Bapak/Ibu pernah berinteraksi melalui pesan singkat (email, sms, whatsapp, line, dll)?", "Pada saat pembelian polis, apakah kendaraan Bapak/Ibu dilakukan survei oleh petugas surveyor?", "Apakah Bapak/Ibu pernah mengakses website gardaoto.com?", "Penampilan petugas bersih dan rapi","Kejelasan dan intonasi suara", "Suara petugas terdengar ramah dan bersahabat", "Kecepatan membalas pesan saat chat via pesan singkat", "Tata bahasa membalas pesan saat chat via pesan singkat", "Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)", "Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)", "Penjelasan yang disampaikan petugas mudah dipahami", "Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan", "Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?", "Waktu tunggu dihubungi marketing pada proses awal penawaran","Antusias petugas dalam melayani", "Hasil survei kendaraan sesuai dengan kondisi kendaraan", "Proses survei kendaraan secara keseluruhan (pada saat pembelian polis)", "Apakah Anda mendapatkan informasi terkait pengiriman polis?","Apakah Anda sudah menerima polis?", "Kepuasan Bapak/Ibu terhadap polis diterima tepat waktu sesuai janji", "Dalam bentuk apa polis yang Bapak/Ibu terima?", "Secara keseluruhan, seberapa puaskah Anda terhadap proses pembelian Asuransi Garda Oto? Silakan isi dengan 1 berarti sangat tidak puas dan 10 artinya sangat puas.", "Setelah Bapak/Ibu mendapatkan layanan pembelian polis, seberapa besar tingkat keinginan Bapak/Ibu merekomendasi Asuransi Astra? Silakan isi dengan 1 berarti sangat tidak ingin merekomendasikan dan 10 artinya sangat ingin merekomendasikan.", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Akses \u2013 Call Center", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Center Mall", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Siaga \u2013 Layanan Bantuan Darurat", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - myGarda \u2013 Aplikasi Mobile", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garansi hasil kerja bengkel 6 bulan", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Spare Part Asli", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Tidak Tahu", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Other", "Sebagai konfirmasi layanan, mari isi nomor handphone Bapak/Ibu terlebih dahulu, dimulai dari angka kedua ya!", "Phone_CC","No Polis","Nama Customer","Nama Marketing","Cabang Kota","Segment","Bulan","Bulan Layanan", "Terima kasih atas penilaian yang Bapak/Ibu berikan. Kira-kira apakah ada saran perbaikan lain yang ingin Bapak/Ibu sampaikan untuk meningkatkan layanan kami kepada Anda?", "Kategori Feedback","branch","policyno","region","segment","Preferred Channels","Tags", ]] FLEXIBLE_NEW = [clean_col(c) for c in ["Phone_CC","No Polis","Nama Customer","Nama Marketing","Cabang Kota","Segment","Bulan","Bulan Layanan"]] CSI_NEW_COLS = ["HP","Polis","Name on Policy","Nama Marketing","Cabang/Kota","CABANG","Cabang Acq","REGION","REGION ACQ","Segment","Bulan Layanan","Bulan Reported","Media","Penampilan petugas bersih dan rapi","Kejelasan dan intonasi suara","Suara petugas terdengar ramah dan bersahabat","Kecepatan membalas pesan saat chat via pesan singkat","Tata bahasa membalas pesan saat chat via pesan singkat","Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)","Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)","Penjelasan yang disampaikan petugas mudah dipahami","Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan","Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?","Antusias petugas dalam melayani","Waktu tunggu sampai dihubungi oleh marketing","Kepuasan terhadap polis diterima tepat waktu sesuai janji","Hasil survei kendaraan sesuai dengan kondisi kendaraan","Proses survei kendaraan secara keseluruhan (pada saat pembelian polis)","SARAN","KATEGORI FEEDBACK"] SCALE_NEW = CSI_NEW_COLS[13:28] EXPECTED_RENEW = [clean_col(c) for c in [ "Submitted On"," Terima kasih atas kesediaan Bapak/Ibu. Silakan tuliskan nama lengkap Anda.", " Apa hubungan Bapak/Ibu dengan pemegang polis?", " Apakah Bapak/Ibu mengalami secara langsung proses tersebut?", " Manakah media pembelian asuransi berikut ini yang Bapak/Ibu gunakan saat membeli asuransi?", " Bagaimana cara Bapak/Ibu mengetahui informasi renewal polis pertama kali?", " Kapankah Bapak/Ibu menghubungi/dihubungi untuk melakukan proses renewal polis?", " Pada saat Bapak/Ibu melakukan renewal polis, siapa yang membantu proses tersebut?", " Apakah Bapak/Ibu pernah berinteraksi dengan petugas marketing Asuransi Astra?", " Apakah Bapak/Ibu pernah bertemu langsung dengan petugas marketing Asuransi Astra?", " Apakah Bapak/Ibu pernah berinteraksi melalui telepon?", " Apakah Bapak/Ibu pernah berinteraksi melalui pesan singkat (email, sms, whatsapp, line, dll)?", " Pada saat pembelian polis renewal, apakah kendaraan Bapak/Ibu dilakukan survei oleh petugas surveyor?", " Apakah Bapak/Ibu pernah mengakses website gardaoto.com?", " Penampilan petugas bersih dan rapi"," Kejelasan dan intonasi suara", " Suara petugas terdengar ramah dan bersahabat", " Kecepatan membalas pesan saat chat via pesan singkat", " Tata bahasa membalas pesan saat chat via pesan singkat", " Selanjutnya, berapakah tingkat kepuasan Bapak/Ibu terhadap follow up reminder berakhirnya periode polis", " Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)", " Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)", " Penjelasan yang disampaikan petugas mudah dipahami", " Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan", " Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?", " Antusias petugas dalam melayani", " Hasil survei kendaraan sesuai dengan kondisi kendaraan", " Proses survei kendaraan secara keseluruhan (pada saat renewal polis)", " Apakah Bapak/Ibu mendapatkan informasi terkait pengiriman polis?", " Apakah Bapak/Ibu sudah menerima polis?", " Dalam bentuk apa polis yang Bapak/Ibu terima?", " Kepuasan Bapak/Ibu terhadap polis diterima tepat waktu sesuai janji", " Secara keseluruhan, seberapa puaskah Anda terhadap proses renewal Asuransi Garda Oto? Silakan isi dengan 1 berarti sangat tidak puas dan 10 artinya sangat puas. ", " Setelah Bapak/Ibu mendapatkan layanan pembelian polis, seberapa besar tingkat keinginan Bapak/Ibu merekomendasi Asuransi Astra? Silakan isi dengan 1 berarti sangat tidak ingin merekomendasikan dan 10 artinya sangat ingin merekomendasikan. ", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Akses \u2013 Call Center", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Center Mall ", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Siaga \u2013 Layanan Bantuan Darurat", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - myGarda \u2013 Aplikasi Mobile", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garansi hasil kerja bengkel 6 bulan", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Spare Part Asli", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Tidak Tahu", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Other", " Sebagai konfirmasi layanan, mari isi nomor handphone Bapak/Ibu terlebih dahulu, dimulai dari angka kedua ya!", "Phone CC","No Polis","Nama Customer","Nama Marketing","Cabang Kota","Segment","Bulan","Bulan Layanan", " Terima kasih atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?", "Kategori Feedback","branch","policyno","region","segment","Preferred Channels","Tags", ]] FLEXIBLE_RENEW = [clean_col(c) for c in ["Phone CC","No Polis","Nama Customer","Nama Marketing","Cabang Kota","Segment","Bulan","Bulan Layanan"]] CSI_RENEW_COLS = ["HP","Polis","Name on Policy","Nama Marketing","Cabang/Kota","CABANG","CABANG ACQ","REGION","REGION ACQ","Segment","Bulan Layanan","Bulan Reported","Media","Penampilan petugas bersih dan rapi","Kejelasan dan intonasi suara","Suara petugas terdengar ramah dan bersahabat","Kecepatan membalas pesan saat chat via pesan singkat","Tata bahasa membalas pesan saat chat via pesan singkat","Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)","Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)","Penjelasan yang disampaikan petugas mudah dipahami","Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan","Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?","Antusias petugas dalam melayani","Follow up reminder berakhirnya periode polis","Kepuasan terhadap polis diterima tepat waktu sesuai janji","Hasil survei kendaraan sesuai dengan kondisi kendaraan","Proses survei kendaraan secara keseluruhan (pada saat renewal polis)","SARAN","KATEGORI FEEDBACK"] SCALE_RENEW = CSI_RENEW_COLS[13:28] EXPECTED_GASI = [clean_col(c) for c in [ "Submitted On"," Terima kasih atas kesediaan Bapak/Ibu. Silakan tuliskan nama lengkap Anda.", " Apa hubungan Bapak/Ibu dengan pemegang polis?", " Manakah proses layanan darurat yang Bapak/Ibu terima?", " Apakah Bapak/Ibu mengalami secara langsung proses tersebut?", " Manakah media yang Bapak/Ibu pilih saat melakukan request layanan darurat Garda Siaga?", " Apakah Bapak/Ibu berinteraksi dengan petugas melalui telepon saat petugas sedang menuju lokasi?", " Apakah Bapak/Ibu berinteraksi dengan petugas melalui pesan singkat (SMS/WhatsApp) saat petugas sedang menuju lokasi?", " Kemudahan melakukan request layanan darurat melalui aplikasi myGarda", " Kecepatan dalam merespons request customer", " Informasi dan solusi yang diberikan oleh petugas Call Center Garda Siaga", " Intonasi dan tata bahasa yang digunakan oleh petugas Call Center Garda Siaga ketika memberikan informasi dan solusi", " Update posisi yang dilakukan petugas saat mendatangi Anda", " Kecepatan mengangkat telepon"," Tata bahasa saat di telepon", " Kecepatan membalas pesan singkat"," Tata bahasa membalas pesan singkat", " Kerapian dan kebersihan penampilan petugas Garda Siaga ", " Kondisi dan peralatan mobil derek atau motor Garda Siaga", " Informasi dan tata bahasa yang digunakan ketika memberikan bantuan", " Empati yang ditunjukkan oleh petugas", " Informasi dan solusi yang diberikan oleh petugas Garda Siaga", " Pengetahuan dan keterampilan yang dimiliki oleh petugas Garda Siaga ", " Secara keseluruhan, seberapa puaskah Anda terhadap proses layanan bantuan darurat Garda Siaga? Silakan isi dengan 1 berarti sangat tidak puas dan 10 artinya sangat puas. ", " Setelah Bapak/Ibu mendapatkan layanan bantuan darurat Garda Siaga, seberapa besar tingkat keinginan Bapak/Ibu merekomendasi Asuransi Astra? Silakan isi dengan 1 berarti sangat tidak ingin merekomendasikan dan 10 artinya sangat ingin merekomendasikan. ", " Sebagai konfirmasi layanan, mari isi nomor handphone Bapak/Ibu terlebih dahulu, dimulai dari angka kedua ya! ", "Phone_CC","No Polis","Nama Customer","PETUGAS","ID Base","Cabang2","Cabang3","Coverage","Segmen","Tgl","Bulan Layanan", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Akses \u2013 Call Center", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Center Mall ", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Siaga \u2013 Layanan Bantuan Darurat", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - myGarda \u2013 Aplikasi Mobile", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Spare Part Asli", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garansi Hasil Kerja Bengkel 6 Bulan", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Tidak Tahu", " Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Other", " Terima kasih atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?", "Kategori Feedback","Preferred Channels","Tags", ]] FLEXIBLE_GASI = [clean_col(c) for c in ["Phone_CC","No Polis","Nama Customer","PETUGAS","ID Base","Cabang2","Cabang3","Coverage","Segmen","Tgl","Bulan Layanan"]] CSI_GASI_COLS = ["HP","Polis","Name on Policy","Petugas","Cabang","Cabang2","REGION","Bulan Layanan","Bulan Reported","Media","Kemudahan melakukan request layanan darurat melalui aplikasi myGarda","Kecepatan dalam merespons request customer","Update posisi yang dilakukan petugas saat mendatangi Anda","Kecepatan mengangkat telepon","Kecepatan membalas pesan singkat","Kondisi dan peralatan mobil derek atau motor Garda Siaga","Informasi dan solusi yang diberikan oleh petugas Call Center Garda Siaga","Intonasi dan tata bahasa yang digunakan oleh petugas Call Center Garda Siaga ketika memberikan informasi dan solusi","Tata bahasa saat di telepon","Tata bahasa membalas pesan singkat","Kerapian dan kebersihan penampilan petugas Garda Siaga","Informasi dan tata bahasa yang digunakan ketika memberikan bantuan","Empati yang ditunjukkan oleh petugas","Informasi dan solusi yang diberikan oleh petugas Garda Siaga","Pengetahuan dan keterampilan yang dimiliki oleh petugas Garda Siaga","SARAN","KATEGORI FEEDBACK"] SCALE_GASI = CSI_GASI_COLS[10:25] FOTO = 'Silakan unggah foto atau video tambahan (opsional). Jika tidak ada, silakan klik "SKIP >". Saran pengambilan foto/video: Tampak jauh, tampak dekat, kualitas jelas (tidak buram), pencahayaan cukup, dan fokus panel yang dikeluhkan' EXPECTED_KLAIM = make_unique_columns([clean_col(c) for c in [ "Submitted On","Terima kasih atas kesediaan Bapak/Ibu. Silakan tuliskan nama lengkap Anda.", "Apa hubungan Bapak/Ibu dengan pemegang polis?", "Apakah Bapak/Ibu mengalami secara langsung proses tersebut?", "Bagaimana cara Bapak/Ibu membuat laporan klaim pertama kali?", "Dimana Bapak/Ibu melakukan survey klaim kendaraan?", "Apakah Bapak/Ibu bertemu langsung tatap muka dengan surveyor?", "Apakah Bapak/Ibu berinteraksi melalui WhatsApp dengan surveyor?", "Apakah Bapak/Ibu pernah berinteraksi melalui telepon dengan surveyor?", "Apakah Bapak/Ibu pernah berinteraksi melalui video call dengan surveyor?", "Kemudahan pelaporan klaim via myGarda","Kemudahan terhubung dengan Garda Akses Call Center", "Lama waktu antrian","Kenyamanan ruang tunggu dan fasilitas yang ada", "Ketepatan waktu datangnya petugas survey","Penampilan petugas bersih dan rapi", "Kejelasan dan intonasi suara","Suara petugas terdengar ramah dan bersahabat", "Keantusiasan dan sikap responsif petugas survei klaim","Lama waktu survey kendaraan", "Berapa lamakah (dalam menit) proses survey yang berlangsung? (isi angka saja)", "Berapa lamakah (dalam menit) proses survey yang Bapak/Ibu harapkan? (isi angka saja)", "Penjelasan mengenai hasil survey kendaraan lengkap dan mudah dipahami", "Penjelasan mengenai surat perintah kerja bengkel lengkap dan mudah dipahami", "Lama waktu penerbitan SPK (Surat Perintah Kerja untuk masuk bengkel)", "Kecepatan membalas pesan singkat","Tata bahasa membalas pesan singkat", "Konfirmasi yang dilakukan petugas sebelum melakukan call", "Ketepatan waktu petugas sesuai janji call", "Kejelasan pengarahan proses survei & foto kendaraan dari petugas", "Kemudahan proses foto kerusakan kendaraan secara mandiri (self survey )", "Kemudahan proses self survey secara keseluruhan","Kelancaran proses self survey secara keseluruhan", "Apakah kendaraan Bapak/Ibu sudah selesai diperbaiki di bengkel dan sudah menerima kembali kendaraan tersebut (kendaraan sudah diambil dari bengkel)?", "Keantusiasan dan sikap responsif petugas bengkel", "Informasi progres perbaikan kendaraan yang diberikan oleh bengkel", "Kejelasan Informasi kapan waktu penyelesaian perbaikan kendaraan", "Ketepatan waktu pengerjaan perbaikan kendaraan dengan yang dijanjikan oleh bengkel", "Hasil pekerjaan bengkel", "Terima kasih atas penilaian yang Bapak/Ibu berikan. Agar kami dapat melakukan perbaikan layanan, mohon Bapak/Ibu informasikan hasil kerja bengkel yang kurang memuaskan:", 'Mohon bantuan Bapak/Ibu untuk mengunggah foto atau video yang menunjukkan kendala pada hasil perbaikan kendaraan. Saran pengambilan foto/video: Foto dari tampak jauh, foto tampak dekat, kualitas jelas (tidak buram), pencahayaan cukup, dan fokus pada panel yang dikeluhkan', FOTO, FOTO, f"{FOTO} .1", f"{FOTO} .2", "Secara keseluruhan, seberapa puaskah Anda terhadap proses klaim Asuransi Garda Oto? Silakan isi dengan 1 berarti sangat tidak puas dan 10 artinya sangat puas.", "Setelah Bapak/Ibu mendapatkan layanan klaim, seberapa besar tingkat keinginan Bapak/Ibu merekomendasi Asuransi Astra? Silakan isi dengan 1 berarti sangat tidak ingin merekomendasikan dan 10 artinya sangat ingin merekomendasikan.", "Sebagai konfirmasi layanan, mari isi nomor handphone Bapak/Ibu terlebih dahulu, dimulai dari angka kedua ya!", "Phone_CC","No Polis","Name on Policy","Surveyor Name","Cabang Bengkel","Cabang_2","Cabang_3","Segement Desc","SPK No","Nama Bengkel","SPK Date","Vehicle Type", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Akses \u2013 Call Center", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Center Mall", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Siaga \u2013 Layanan Bantuan Darurat", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - myGarda \u2013 Aplikasi Mobile", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garansi hasil kerja bengkel 6 bulan", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Spare Part Asli", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Tidak Tahu", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Other", "Terima kasih atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?", "Kategori Feedback","Preferred Channels","Tags", ]]) FLEXIBLE_KLAIM = [clean_col(c) for c in ["Phone_CC","No Polis","Name on Policy","Surveyor Name","Cabang Bengkel","Cabang_2","Cabang_3","Segement Desc","SPK No","Nama Bengkel","SPK Date","Vehicle Type"]] EXPECTED_KLAIM_DESK = [clean_col(c) for c in [ "Submitted On","Terima kasih atas kesediaan Bapak/Ibu. Silakan tuliskan nama lengkap Anda.", "Apa hubungan Bapak/Ibu dengan pemegang polis?", "Apakah Bapak/Ibu mengalami secara langsung proses tersebut?", "Bagaimana cara Bapak/Ibu membuat laporan klaim pertama kali?", "Dimana Bapak/Ibu melakukan survey klaim kendaraan?", "Apakah Bapak/Ibu bertemu langsung tatap muka dengan surveyor?", "Apakah Anda berinteraksi melalui WhatsApp dengan surveyor?", "Apakah Bapak/Ibu pernah berinteraksi melalui telepon dengan surveyor?", "Kemudahan pelaporan klaim via myGarda","Kemudahan terhubung dengan Garda Akses Call Center", "Kejelasan dan intonasi suara","Suara petugas terdengar ramah dan bersahabat", "Keantusiasan dan sikap responsif petugas survei klaim","Lama waktu survey kendaraan", "Penjelasan mengenai hasil survey kendaraan lengkap dan mudah dipahami", "Penjelasan mengenai surat perintah kerja bengkel lengkap dan mudah dipahami", "Lama waktu penerbitan SPK (Surat Perintah Kerja untuk masuk bengkel)", "Kecepatan membalas pesan singkat","Tata bahasa membalas pesan singkat", "Konfirmasi yang dilakukan petugas sebelum melakukan call","Ketepatan waktu petugas sesuai janji call", "Kejelasan pengarahan proses survei & foto kendaraan dari petugas", "Kemudahan proses foto kerusakan kendaraan secara mandiri (self survey )", "Kemudahan proses self survey secara keseluruhan","Kelancaran proses self survey secara keseluruhan", "Apakah kendaraanmu sudah selesai diperbaiki di bengkel?", "Keantusiasan dan sikap responsif petugas bengkel", "Informasi progres perbaikan kendaraan yang diberikan oleh bengkel", "Kejelasan Informasi kapan waktu penyelesaian perbaikan kendaraan", "Ketepatan waktu pengerjaan perbaikan kendaraan dengan yang dijanjikan oleh bengkel", "Hasil pekerjaan bengkel", "Terima kasih atas penilaian yang Bapak/Ibu berikan. Agar kami dapat melakukan perbaikan layanan, mohon Bapak/Ibu informasikan hasil kerja bengkel yang kurang memuaskan:", 'Mohon bantuan Bapak/Ibu untuk mengunggah foto atau video yang menunjukkan kendala pada hasil perbaikan kendaraan. Saran pengambilan foto/video: Foto dari tampak jauh, foto tampak dekat, kualitas jelas (tidak buram), pencahayaan cukup, dan fokus pada panel yang dikeluhkan', f"{FOTO}", f"{FOTO} .1", f"{FOTO} .2", f"{FOTO} .3", "Secara keseluruhan, seberapa puaskah Anda terhadap proses klaim Asuransi Garda Oto? Silakan isi dengan 1 berarti sangat tidak puas dan 10 artinya sangat puas.", "Setelah Bapak/Ibu mendapatkan layanan klaim, seberapa besar tingkat keinginan Bapak/Ibu merekomendasi Asuransi Astra? Silakan isi dengan 1 berarti sangat tidak ingin merekomendasikan dan 10 artinya sangat ingin merekomendasikan.", "Sebagai konfirmasi layanan, mari isi nomor handphone Bapak/Ibu terlebih dahulu, dimulai dari angka kedua ya!", "Phone_CC","No Polis","Name on Policy","Surveyor Name","Cabang Bengkel","Cabang_2","Cabang_3","Segement Desc","SPK No","Nama Bengkel","SPK Date","Vehicle Type", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Akses \u2013 Call Center", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Center Mall", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garda Siaga \u2013 Layanan Bantuan Darurat", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - myGarda \u2013 Aplikasi Mobile", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Garansi hasil kerja bengkel 6 bulan", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Spare Part Asli", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Tidak Tahu", "Pertanyan terakhir, kira-kira dari list dibawah ini, manakah fitur layanan dan benefit yang Bapak/Ibu ketahui? - Other", "Terima kasih ya atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?", "Kategori Feedback","Preferred Channels","Tags", ]] KLAIM_DESK_RENAME = { "Apakah Anda berinteraksi melalui WhatsApp dengan surveyor?": "Apakah Bapak/Ibu berinteraksi melalui WhatsApp dengan surveyor?", "Apakah kendaraanmu sudah selesai diperbaiki di bengkel?": "Apakah kendaraan Bapak/Ibu sudah selesai diperbaiki di bengkel dan sudah menerima kembali kendaraan tersebut (kendaraan sudah diambil dari bengkel)?", "Terima kasih ya atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?": "Terima kasih atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?", } KLAIM_ONLY_COLS = [ "Apakah Bapak/Ibu pernah berinteraksi melalui video call dengan surveyor?", "Lama waktu antrian","Kenyamanan ruang tunggu dan fasilitas yang ada", "Ketepatan waktu datangnya petugas survey","Penampilan petugas bersih dan rapi", "Berapa lamakah (dalam menit) proses survey yang berlangsung? (isi angka saja)", "Berapa lamakah (dalam menit) proses survey yang Bapak/Ibu harapkan? (isi angka saja)", FOTO, ] CSI_KLAIM_COLS = ['No. HP','PolicyNo','Customer Name','Surveyor Name','CABANG2','Cabang3','Cabang2_ReportMV','Cabang Bengkel','Region','Region_ReportMV','Region Bengkel','Nama Bengkel','SPK DATE','SPK NO','Bulan Layanan','Bulan Reported','Media','Ketepatan waktu datangnya petugas survey','Penampilan petugas bersih dan rapi','Kejelasan dan intonasi suara','Suara petugas terdengar ramah dan bersahabat','Keantusiasan dan sikap responsif petugas survei klaim','Penjelasan mengenai hasil survey kendaraan lengkap dan mudah dipahami','Penjelasan mengenai surat perintah kerja bengkel lengkap dan mudah dipahami','Kecepatan membalas pesan singkat','Tata bahasa membalas pesan singkat','Konfirmasi yang dilakukan petugas sebelum melakukan call','Ketepatan waktu petugas sesuai janji call','Kejelasan pengarahan proses survei & foto kendaraan dari petugas','Lama waktu antrian','Kenyamanan ruang tunggu dan fasilitas yang ada','Lama waktu survey kendaraan','Lama waktu penerbitan SPK (Surat Perintah Kerja untuk masuk bengkel)','Kemudahan proses foto kerusakan kendaraan secara mandiri (self survey )','Kemudahan proses self survey secara keseluruhan','Kelancaran proses self survey secara keseluruhan','Kemudahan pelaporan klaim via myGarda','Kemudahan terhubung dengan Garda Akses Call Center','Keantusiasan dan sikap responsif petugas bengkel','Informasi progres perbaikan kendaraan yang diberikan oleh bengkel','Kejelasan Informasi kapan waktu penyelesaian perbaikan kendaraan','Hasil pekerjaan bengkel','Ketepatan waktu pengerjaan perbaikan kendaraan dengan yang dijanjikan oleh bengkel','Saran','Kategori Feedback'] SCALE_KLAIM = ['Ketepatan waktu datangnya petugas survey','Penampilan petugas bersih dan rapi','Kejelasan dan intonasi suara','Suara petugas terdengar ramah dan bersahabat','Keantusiasan dan sikap responsif petugas survei klaim','Penjelasan mengenai hasil survey kendaraan lengkap dan mudah dipahami','Penjelasan mengenai surat perintah kerja bengkel lengkap dan mudah dipahami','Kecepatan membalas pesan singkat','Tata bahasa membalas pesan singkat','Konfirmasi yang dilakukan petugas sebelum melakukan call','Ketepatan waktu petugas sesuai janji call','Kejelasan pengarahan proses survei & foto kendaraan dari petugas','Lama waktu antrian','Kenyamanan ruang tunggu dan fasilitas yang ada','Lama waktu survey kendaraan','Lama waktu penerbitan SPK (Surat Perintah Kerja untuk masuk bengkel)','Kemudahan proses foto kerusakan kendaraan secara mandiri (self survey )','Kemudahan proses self survey secara keseluruhan','Kelancaran proses self survey secara keseluruhan','Kemudahan pelaporan klaim via myGarda','Kemudahan terhubung dengan Garda Akses Call Center','Keantusiasan dan sikap responsif petugas bengkel','Informasi progres perbaikan kendaraan yang diberikan oleh bengkel','Kejelasan Informasi kapan waktu penyelesaian perbaikan kendaraan','Hasil pekerjaan bengkel','Ketepatan waktu pengerjaan perbaikan kendaraan dengan yang dijanjikan oleh bengkel'] # ========================================================= # BUILD FUNCTIONS (same logic as Colab, unchanged) # ========================================================= def build_new_from_resp(row, bulan_reported): return {"HP":format_phone(row.get("Phone_CC")),"Polis":row.get("No Polis"),"Name on Policy":row.get("Nama Customer"),"Nama Marketing":row.get("Nama Marketing"),"Cabang/Kota":row.get("Cabang Kota"),"CABANG":None,"Cabang Acq":None,"REGION":None,"REGION ACQ":None,"Segment":row.get("Segment"),"Bulan Layanan":parse_bulan(row.get("Bulan")),"Bulan Reported":bulan_reported,"Media":"Online","Penampilan petugas bersih dan rapi":row.get("Penampilan petugas bersih dan rapi"),"Kejelasan dan intonasi suara":row.get("Kejelasan dan intonasi suara"),"Suara petugas terdengar ramah dan bersahabat":row.get("Suara petugas terdengar ramah dan bersahabat"),"Kecepatan membalas pesan saat chat via pesan singkat":row.get("Kecepatan membalas pesan saat chat via pesan singkat"),"Tata bahasa membalas pesan saat chat via pesan singkat":row.get("Tata bahasa membalas pesan saat chat via pesan singkat"),"Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)":row.get("Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)"),"Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)":row.get("Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)"),"Penjelasan yang disampaikan petugas mudah dipahami":row.get("Penjelasan yang disampaikan petugas mudah dipahami"),"Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan":row.get("Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan"),"Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?":row.get("Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?"),"Antusias petugas dalam melayani":row.get("Antusias petugas dalam melayani"),"Waktu tunggu sampai dihubungi oleh marketing":row.get("Waktu tunggu dihubungi marketing pada proses awal penawaran"),"Kepuasan terhadap polis diterima tepat waktu sesuai janji":row.get("Kepuasan Bapak/Ibu terhadap polis diterima tepat waktu sesuai janji"),"Hasil survei kendaraan sesuai dengan kondisi kendaraan":row.get("Hasil survei kendaraan sesuai dengan kondisi kendaraan"),"Proses survei kendaraan secara keseluruhan (pada saat pembelian polis)":row.get("Proses survei kendaraan secara keseluruhan (pada saat pembelian polis)"),"SARAN":row.get("Terima kasih atas penilaian yang Bapak/Ibu berikan. Kira-kira apakah ada saran perbaikan lain yang ingin Bapak/Ibu sampaikan untuk meningkatkan layanan kami kepada Anda?"),"KATEGORI FEEDBACK":row.get("Kategori Feedback")} def build_new_from_neuro(row, bulan_reported): return {"HP":format_phone(row.get("Phone")),"Polis":row.get("No. Polis"),"Name on Policy":row.get("Name on Policy (Database)"),"Nama Marketing":row.get("Salesman"),"Cabang/Kota":row.get("Cabang"),"CABANG":None,"Cabang Acq":None,"REGION":None,"REGION ACQ":None,"Segment":row.get("Segment"),"Bulan Layanan":parse_bulan(row.get("Bulan")),"Bulan Reported":bulan_reported,"Media":"Telesurvey","Penampilan petugas bersih dan rapi":row.get("Penampilan petugas bersih dan rapi"),"Kejelasan dan intonasi suara":row.get("Kejelasan dan intonasi suara"),"Suara petugas terdengar ramah dan bersahabat":row.get("Suara petugas terdengar ramah dan bersahabat"),"Kecepatan membalas pesan saat chat via pesan singkat":row.get("Kecepatan membalas pesan"),"Tata bahasa membalas pesan saat chat via pesan singkat":row.get("Tata bahasa membalas pesan"),"Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)":row.get("Penjelasan petugas mengenai produk asuransi detail (yaitu jenis pertanggungan, manfaat dan benefit)"),"Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)":row.get("Penjelasan petugas mengenai proses pembelian asuransi detail (dari survei hingga pengiriman polis)"),"Penjelasan yang disampaikan petugas mudah dipahami":row.get("Penjelasan yang disampaikan petugas mudah dipahami"),"Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan":row.get("Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan"),"Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?":row.get("Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?"),"Antusias petugas dalam melayani":row.get("Antusias petugas dalam melayani"),"Waktu tunggu sampai dihubungi oleh marketing":row.get("Waktu tunggu dihubungi marketing pada proses awal penawaran"),"Kepuasan terhadap polis diterima tepat waktu sesuai janji":row.get("Kepuasan terkait dengan polis diterima tepat waktu sesuai janji"),"Hasil survei kendaraan sesuai dengan kondisi kendaraan":row.get("Hasil survei kendaraan sesuai dengan kondisi kendaraan"),"Proses survei kendaraan secara keseluruhan (pada saat pembelian polis)":row.get("Proses survei kendaraan secara keseluruhan (pada saat pembelian polis)"),"SARAN":row.get("Saran Perbaikan"),"KATEGORI FEEDBACK":row.get("Kategorisasi Feedback")} def build_renew_from_resp(row, bulan_reported): return {"HP":format_phone(row.get("Phone_CC")),"Polis":row.get("No Polis"),"Name on Policy":row.get("Nama Customer"),"Nama Marketing":row.get("Nama Marketing"),"Cabang/Kota":row.get("Cabang Kota"),"CABANG":None,"CABANG ACQ":None,"REGION":None,"REGION ACQ":None,"Segment":row.get("Segment"),"Bulan Layanan":parse_bulan(row.get("Bulan")),"Bulan Reported":bulan_reported,"Media":"Online","Penampilan petugas bersih dan rapi":row.get("Penampilan petugas bersih dan rapi"),"Kejelasan dan intonasi suara":row.get("Kejelasan dan intonasi suara"),"Suara petugas terdengar ramah dan bersahabat":row.get("Suara petugas terdengar ramah dan bersahabat"),"Kecepatan membalas pesan saat chat via pesan singkat":row.get("Kecepatan membalas pesan saat chat via pesan singkat"),"Tata bahasa membalas pesan saat chat via pesan singkat":row.get("Tata bahasa membalas pesan saat chat via pesan singkat"),"Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)":row.get("Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)"),"Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)":row.get("Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)"),"Penjelasan yang disampaikan petugas mudah dipahami":row.get("Penjelasan yang disampaikan petugas mudah dipahami"),"Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan":row.get("Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan"),"Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?":row.get("Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?"),"Antusias petugas dalam melayani":row.get("Antusias petugas dalam melayani"),"Follow up reminder berakhirnya periode polis":row.get("Selanjutnya, berapakah tingkat kepuasan Bapak/Ibu terhadap follow up reminder berakhirnya periode polis"),"Kepuasan terhadap polis diterima tepat waktu sesuai janji":row.get("Kepuasan Bapak/Ibu terhadap polis diterima tepat waktu sesuai janji"),"Hasil survei kendaraan sesuai dengan kondisi kendaraan":row.get("Hasil survei kendaraan sesuai dengan kondisi kendaraan"),"Proses survei kendaraan secara keseluruhan (pada saat renewal polis)":row.get("Proses survei kendaraan secara keseluruhan (pada saat renewal polis)"),"SARAN":row.get("Terima kasih atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?"),"KATEGORI FEEDBACK":row.get("Kategori Feedback")} def build_renew_from_neuro(row, bulan_reported): return {"HP":format_phone(row.get("Phone")),"Polis":row.get("No. Polis"),"Name on Policy":row.get("Name on Policy (Database)"),"Nama Marketing":row.get("Salesman"),"Cabang/Kota":row.get("Cabang"),"CABANG":None,"CABANG ACQ":None,"REGION":None,"REGION ACQ":None,"Segment":row.get("Segment"),"Bulan Layanan":parse_bulan(row.get("Bulan")),"Bulan Reported":bulan_reported,"Media":"Telesurvey","Penampilan petugas bersih dan rapi":row.get("Penampilan petugas bersih dan rapi"),"Kejelasan dan intonasi suara":row.get("Kejelasan dan intonasi suara"),"Suara petugas terdengar ramah dan bersahabat":row.get("Suara petugas terdengar ramah dan bersahabat"),"Kecepatan membalas pesan saat chat via pesan singkat":row.get("Kecepatan membalas pesan"),"Tata bahasa membalas pesan saat chat via pesan singkat":row.get("Tata bahasa membalas pesan"),"Detail penjelasan petugas mengenai produk asuransi (yaitu jenis pertanggungan, manfaat dan benefit)":row.get("Penjelasan petugas mengenai produk asuransi detail (yaitu jenis pertanggungan, manfaat dan benefit)"),"Detail penjelasan petugas mengenai proses pembelian asuransi (dari survei hingga pengiriman polis)":row.get("Penjelasan petugas mengenai proses pembelian asuransi detail (dari survei hingga pengiriman polis)"),"Penjelasan yang disampaikan petugas mudah dipahami":row.get("Penjelasan yang disampaikan petugas mudah dipahami"),"Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan":row.get("Petugas tanggap terhadap informasi yang diminta/memahami pertanyaan yang diajukan pelanggan"),"Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?":row.get("Follow up/tindak lanjut atas hal yang perlu dikonfirmasi (pembayaran, pengiriman polis)?"),"Antusias petugas dalam melayani":row.get("Antusias petugas dalam melayani"),"Follow up reminder berakhirnya periode polis":row.get("Follow up reminder berakhirnya periode polis"),"Kepuasan terhadap polis diterima tepat waktu sesuai janji":row.get("Kepuasan terkait dengan polis diterima tepat waktu sesuai janji"),"Hasil survei kendaraan sesuai dengan kondisi kendaraan":row.get("Hasil survei kendaraan sesuai dengan kondisi kendaraan"),"Proses survei kendaraan secara keseluruhan (pada saat renewal polis)":row.get("Proses survei kendaraan secara keseluruhan (pada saat renewal polis)"),"SARAN":row.get("Saran Perbaikan"),"KATEGORI FEEDBACK":row.get("Kategorisasi Feedback")} def build_gasi_from_resp(row, bulan_reported): return {"HP":format_phone(row.get("Phone_CC")),"Polis":row.get("No Polis"),"Name on Policy":row.get("Nama Customer"),"Petugas":row.get("PETUGAS"),"Cabang":row.get("Cabang2"),"Cabang2":row.get("Cabang3"),"REGION":None,"Bulan Layanan":parse_bulan(row.get("Bulan Layanan")),"Bulan Reported":bulan_reported,"Media":"Online","Kemudahan melakukan request layanan darurat melalui aplikasi myGarda":row.get("Kemudahan melakukan request layanan darurat melalui aplikasi myGarda"),"Kecepatan dalam merespons request customer":row.get("Kecepatan dalam merespons request customer"),"Update posisi yang dilakukan petugas saat mendatangi Anda":row.get("Update posisi yang dilakukan petugas saat mendatangi Anda"),"Kecepatan mengangkat telepon":row.get("Kecepatan mengangkat telepon"),"Kecepatan membalas pesan singkat":row.get("Kecepatan membalas pesan singkat"),"Kondisi dan peralatan mobil derek atau motor Garda Siaga":row.get("Kondisi dan peralatan mobil derek atau motor Garda Siaga"),"Informasi dan solusi yang diberikan oleh petugas Call Center Garda Siaga":row.get("Informasi dan solusi yang diberikan oleh petugas Call Center Garda Siaga"),"Intonasi dan tata bahasa yang digunakan oleh petugas Call Center Garda Siaga ketika memberikan informasi dan solusi":row.get("Intonasi dan tata bahasa yang digunakan oleh petugas Call Center Garda Siaga ketika memberikan informasi dan solusi"),"Tata bahasa saat di telepon":row.get("Tata bahasa saat di telepon"),"Tata bahasa membalas pesan singkat":row.get("Tata bahasa membalas pesan singkat"),"Kerapian dan kebersihan penampilan petugas Garda Siaga":row.get("Kerapian dan kebersihan penampilan petugas Garda Siaga"),"Informasi dan tata bahasa yang digunakan ketika memberikan bantuan":row.get("Informasi dan tata bahasa yang digunakan ketika memberikan bantuan"),"Empati yang ditunjukkan oleh petugas":row.get("Empati yang ditunjukkan oleh petugas"),"Informasi dan solusi yang diberikan oleh petugas Garda Siaga":row.get("Informasi dan solusi yang diberikan oleh petugas Garda Siaga"),"Pengetahuan dan keterampilan yang dimiliki oleh petugas Garda Siaga":row.get("Pengetahuan dan keterampilan yang dimiliki oleh petugas Garda Siaga"),"SARAN":row.get("Terima kasih atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?"),"KATEGORI FEEDBACK":row.get("Kategori Feedback")} def build_gasi_from_neuro(row, bulan_reported): return {"HP":format_phone(row.get("Phone")),"Polis":row.get("No. Polis"),"Name on Policy":row.get("Nama Customer (Database)"),"Petugas":row.get("Nama Petugas"),"Cabang":row.get("Wilayah"),"Cabang2":row.get("Cabang3"),"REGION":None,"Bulan Layanan":parse_bulan(row.get("Bulan Layanan")),"Bulan Reported":bulan_reported,"Media":"Telesurvey","Kemudahan melakukan request layanan darurat melalui aplikasi myGarda":row.get("Kemudahan melakukan request layanan darurat melalui aplikasi myGarda"),"Kecepatan dalam merespons request customer":row.get("Kecepatan dalam merespons request customer"),"Update posisi yang dilakukan petugas saat mendatangi Anda":row.get("Update posisi yang dilakukan petugas saat mendatangi Anda"),"Kecepatan mengangkat telepon":row.get("Kecepatan mengangkat telepon"),"Kecepatan membalas pesan singkat":row.get("Kecepatan membalas pesan"),"Kondisi dan peralatan mobil derek atau motor Garda Siaga":row.get("Kondisi dan peralatan mobil derek atau motor Garda Siaga"),"Informasi dan solusi yang diberikan oleh petugas Call Center Garda Siaga":row.get("Informasi dan solusi yang diberikan oleh petugas Call Center Garda Siaga"),"Intonasi dan tata bahasa yang digunakan oleh petugas Call Center Garda Siaga ketika memberikan informasi dan solusi":row.get("Intonasi dan tata bahasa yang digunakan oleh petugas Call Center Garda Siaga ketika memberikan informasi dan solusi"),"Tata bahasa saat di telepon":row.get("Tata bahasa saat di telepon"),"Tata bahasa membalas pesan singkat":row.get("Tata bahasa membalas pesan"),"Kerapian dan kebersihan penampilan petugas Garda Siaga":row.get("Kerapian dan kebersihan penampilan petugas Garda Siaga"),"Informasi dan tata bahasa yang digunakan ketika memberikan bantuan":row.get("Informasi dan tata bahasa yang digunakan ketika memberikan bantuan"),"Empati yang ditunjukkan oleh petugas":row.get("Empati yang ditunjukkan oleh petugas"),"Informasi dan solusi yang diberikan oleh petugas Garda Siaga":row.get("Informasi dan solusi yang diberikan oleh petugas Garda Siaga"),"Pengetahuan dan keterampilan yang dimiliki oleh petugas Garda Siaga":row.get("Pengetahuan dan keterampilan yang dimiliki oleh petugas Garda Siaga"),"SARAN":row.get("Saran Perbaikan"),"KATEGORI FEEDBACK":row.get("Kategorisasi Feedback")} def build_klaim_from_resp(row, bulan_reported): return {"No. HP":format_phone(row.get("Phone_CC")),"PolicyNo":row.get("No Polis"),"Customer Name":row.get("Name on Policy"),"Surveyor Name":row.get("Surveyor Name"),"CABANG2":row.get("Cabang_2"),"Cabang3":row.get("Cabang_3"),"Cabang2_ReportMV":None,"Cabang Bengkel":row.get("Cabang Bengkel"),"Region":None,"Region_ReportMV":None,"Region Bengkel":None,"Nama Bengkel":row.get("Nama Bengkel"),"SPK DATE":row.get("SPK Date"),"SPK NO":row.get("SPK No"),"Bulan Layanan":None,"Bulan Reported":bulan_reported,"Media":"Online","Ketepatan waktu datangnya petugas survey":row.get("Ketepatan waktu datangnya petugas survey"),"Penampilan petugas bersih dan rapi":row.get("Penampilan petugas bersih dan rapi"),"Kejelasan dan intonasi suara":row.get("Kejelasan dan intonasi suara"),"Suara petugas terdengar ramah dan bersahabat":row.get("Suara petugas terdengar ramah dan bersahabat"),"Keantusiasan dan sikap responsif petugas survei klaim":row.get("Keantusiasan dan sikap responsif petugas survei klaim"),"Penjelasan mengenai hasil survey kendaraan lengkap dan mudah dipahami":row.get("Penjelasan mengenai hasil survey kendaraan lengkap dan mudah dipahami"),"Penjelasan mengenai surat perintah kerja bengkel lengkap dan mudah dipahami":row.get("Penjelasan mengenai surat perintah kerja bengkel lengkap dan mudah dipahami"),"Kecepatan membalas pesan singkat":row.get("Kecepatan membalas pesan singkat"),"Tata bahasa membalas pesan singkat":row.get("Tata bahasa membalas pesan singkat"),"Konfirmasi yang dilakukan petugas sebelum melakukan call":row.get("Konfirmasi yang dilakukan petugas sebelum melakukan call"),"Ketepatan waktu petugas sesuai janji call":row.get("Ketepatan waktu petugas sesuai janji call"),"Kejelasan pengarahan proses survei & foto kendaraan dari petugas":row.get("Kejelasan pengarahan proses survei & foto kendaraan dari petugas"),"Lama waktu antrian":row.get("Lama waktu antrian"),"Kenyamanan ruang tunggu dan fasilitas yang ada":row.get("Kenyamanan ruang tunggu dan fasilitas yang ada"),"Lama waktu survey kendaraan":row.get("Lama waktu survey kendaraan"),"Lama waktu penerbitan SPK (Surat Perintah Kerja untuk masuk bengkel)":row.get("Lama waktu penerbitan SPK (Surat Perintah Kerja untuk masuk bengkel)"),"Kemudahan proses foto kerusakan kendaraan secara mandiri (self survey )":row.get("Kemudahan proses foto kerusakan kendaraan secara mandiri (self survey )"),"Kemudahan proses self survey secara keseluruhan":row.get("Kemudahan proses self survey secara keseluruhan"),"Kelancaran proses self survey secara keseluruhan":row.get("Kelancaran proses self survey secara keseluruhan"),"Kemudahan pelaporan klaim via myGarda":row.get("Kemudahan pelaporan klaim via myGarda"),"Kemudahan terhubung dengan Garda Akses Call Center":row.get("Kemudahan terhubung dengan Garda Akses Call Center"),"Keantusiasan dan sikap responsif petugas bengkel":row.get("Keantusiasan dan sikap responsif petugas bengkel"),"Informasi progres perbaikan kendaraan yang diberikan oleh bengkel":row.get("Informasi progres perbaikan kendaraan yang diberikan oleh bengkel"),"Kejelasan Informasi kapan waktu penyelesaian perbaikan kendaraan":row.get("Kejelasan Informasi kapan waktu penyelesaian perbaikan kendaraan"),"Hasil pekerjaan bengkel":row.get("Hasil pekerjaan bengkel"),"Ketepatan waktu pengerjaan perbaikan kendaraan dengan yang dijanjikan oleh bengkel":row.get("Ketepatan waktu pengerjaan perbaikan kendaraan dengan yang dijanjikan oleh bengkel"),"Saran":row.get("Terima kasih atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?"),"Kategori Feedback":row.get("Kategori Feedback")} def build_klaim_from_klaimdesk(row, bulan_reported): return {"No. HP":format_phone(row.get("Phone_CC")),"PolicyNo":row.get("No Polis"),"Customer Name":row.get("Name on Policy"),"Surveyor Name":row.get("Surveyor Name"),"CABANG2":row.get("Cabang_2"),"Cabang3":row.get("Cabang_3"),"Cabang2_ReportMV":None,"Cabang Bengkel":row.get("Cabang Bengkel"),"Region":None,"Region_ReportMV":None,"Region Bengkel":None,"Nama Bengkel":row.get("Nama Bengkel"),"SPK DATE":row.get("SPK Date"),"SPK NO":row.get("SPK No"),"Bulan Layanan":None,"Bulan Reported":bulan_reported,"Media":"Online","Ketepatan waktu datangnya petugas survey":None,"Penampilan petugas bersih dan rapi":None,"Kejelasan dan intonasi suara":row.get("Kejelasan dan intonasi suara"),"Suara petugas terdengar ramah dan bersahabat":row.get("Suara petugas terdengar ramah dan bersahabat"),"Keantusiasan dan sikap responsif petugas survei klaim":row.get("Keantusiasan dan sikap responsif petugas survei klaim"),"Penjelasan mengenai hasil survey kendaraan lengkap dan mudah dipahami":row.get("Penjelasan mengenai hasil survey kendaraan lengkap dan mudah dipahami"),"Penjelasan mengenai surat perintah kerja bengkel lengkap dan mudah dipahami":row.get("Penjelasan mengenai surat perintah kerja bengkel lengkap dan mudah dipahami"),"Kecepatan membalas pesan singkat":row.get("Kecepatan membalas pesan singkat"),"Tata bahasa membalas pesan singkat":row.get("Tata bahasa membalas pesan singkat"),"Konfirmasi yang dilakukan petugas sebelum melakukan call":row.get("Konfirmasi yang dilakukan petugas sebelum melakukan call"),"Ketepatan waktu petugas sesuai janji call":row.get("Ketepatan waktu petugas sesuai janji call"),"Kejelasan pengarahan proses survei & foto kendaraan dari petugas":row.get("Kejelasan pengarahan proses survei & foto kendaraan dari petugas"),"Lama waktu antrian":None,"Kenyamanan ruang tunggu dan fasilitas yang ada":None,"Lama waktu survey kendaraan":row.get("Lama waktu survey kendaraan"),"Lama waktu penerbitan SPK (Surat Perintah Kerja untuk masuk bengkel)":row.get("Lama waktu penerbitan SPK (Surat Perintah Kerja untuk masuk bengkel)"),"Kemudahan proses foto kerusakan kendaraan secara mandiri (self survey )":row.get("Kemudahan proses foto kerusakan kendaraan secara mandiri (self survey )"),"Kemudahan proses self survey secara keseluruhan":row.get("Kemudahan proses self survey secara keseluruhan"),"Kelancaran proses self survey secara keseluruhan":row.get("Kelancaran proses self survey secara keseluruhan"),"Kemudahan pelaporan klaim via myGarda":row.get("Kemudahan pelaporan klaim via myGarda"),"Kemudahan terhubung dengan Garda Akses Call Center":row.get("Kemudahan terhubung dengan Garda Akses Call Center"),"Keantusiasan dan sikap responsif petugas bengkel":row.get("Keantusiasan dan sikap responsif petugas bengkel"),"Informasi progres perbaikan kendaraan yang diberikan oleh bengkel":row.get("Informasi progres perbaikan kendaraan yang diberikan oleh bengkel"),"Kejelasan Informasi kapan waktu penyelesaian perbaikan kendaraan":row.get("Kejelasan Informasi kapan waktu penyelesaian perbaikan kendaraan"),"Hasil pekerjaan bengkel":row.get("Hasil pekerjaan bengkel"),"Ketepatan waktu pengerjaan perbaikan kendaraan dengan yang dijanjikan oleh bengkel":row.get("Ketepatan waktu pengerjaan perbaikan kendaraan dengan yang dijanjikan oleh bengkel"),"Saran":row.get("Terima kasih ya atas penilaian yang Bapak/Ibu berikan! Kira-kira apakah ada saran perbaikan lain yang ingin disampaikan untuk meningkatkan layanan kami kepada Bapak/Ibu?"),"Kategori Feedback":row.get("Kategori Feedback")} # ========================================================= # ROUTES # ========================================================= ALL_REGIONS = [ "Sumatera Jateng", "Telerenewal", "MGO Jakarta", "Jabar Sulawesi", "DKI JAKARTA", "Jatim Bali Kalimantan", "Prokhus", ] REGION_CONFIG = { "NEW": { "sheet": "New", "column": "REGION", "regions": ["Sumatera Jateng","DKI JAKARTA","Prokhus","MGO Jakarta","Jatim Bali Kalimantan","Jabar Sulawesi"], }, "RENEW": { "sheet": "Renew", "column": "REGION", "regions": ["Sumatera Jateng","Telerenewal","MGO Jakarta","Jabar Sulawesi","DKI JAKARTA","Jatim Bali Kalimantan","Prokhus"], }, "KLAIM": { "sheet": "Klaim", "column": "Region", "regions": ["Jabar Sulawesi","Jatim Bali Kalimantan","Sumatera Jateng","DKI JAKARTA"], }, "GASI": { "sheet": "Gasi", "column": "REGION", "regions": ["Sumatera Jateng","DKI JAKARTA","Jabar Sulawesi","Jatim Bali Kalimantan"], }, } def create_excel_table(ws): from openpyxl.worksheet.table import Table, TableStyleInfo max_row = ws.max_row max_col = ws.max_column if max_row < 2: return end_col = get_column_letter(max_col) table_range = f"A1:{end_col}{max_row}" table = Table( displayName=f"Table_{ws.title[:20].replace(' ','_')}", ref=table_range ) style = TableStyleInfo( name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False, ) table.tableStyleInfo = style ws.add_table(table) @app.route("/") def index(): return render_template("index.html") @app.route("/api/regions") def api_regions(): return jsonify(ALL_REGIONS) @app.route("/split_region", methods=["POST"]) def split_region(): source_file = request.files.get("source_file") bulan = request.form.get("bulan", "").strip() tahun = request.form.get("tahun", "").strip() regions_raw = request.form.get("regions", "") # comma-separated or "ALL" if not source_file: return jsonify({"error": "Upload file sumber dulu!"}), 400 if not bulan: return jsonify({"error": "Isi Bulan dulu!"}), 400 if not tahun: return jsonify({"error": "Isi Tahun dulu!"}), 400 selected_regions = ALL_REGIONS if regions_raw == "ALL" else [r for r in regions_raw.split("||") if r] logs = [] content = source_file.read() excel_file = pd.ExcelFile(io.BytesIO(content)) logs.append(f"Sheet tersedia: {', '.join(excel_file.sheet_names)}") # Load data loaded_data = {} for csi_type, config in REGION_CONFIG.items(): sheet_name = config["sheet"] if sheet_name in excel_file.sheet_names: df = pd.read_excel(io.BytesIO(content), sheet_name=sheet_name) df.columns = [str(c).strip() for c in df.columns] loaded_data[csi_type] = df logs.append(f"Loaded {sheet_name}: {len(df)} baris") generated_files = [] for region in selected_regions: logs.append(f"\n— Region: {region} —") output_filename = f"Raw Data CSI {region} YTD {bulan}{tahun}.xlsx" output_path = os.path.join(OUTPUT_FOLDER, secure_filename(output_filename)) has_sheet = False try: with pd.ExcelWriter(output_path, engine="openpyxl") as writer: for csi_type, config in REGION_CONFIG.items(): if csi_type not in loaded_data: continue if region not in config["regions"]: continue df = loaded_data[csi_type].copy() region_col = config["column"] if region_col not in df.columns: logs.append(f" Kolom '{region_col}' tidak ditemukan di {csi_type}") continue filtered = df[df[region_col].astype(str).str.strip() == region].copy() logs.append(f" {csi_type}: {len(filtered)} baris") if len(filtered) == 0: continue filtered.to_excel(writer, sheet_name=csi_type, index=False) has_sheet = True if not has_sheet: logs.append(f" Tidak ada data, file dilewati") if os.path.exists(output_path): os.remove(output_path) continue # Format table + autofit wb = load_workbook(output_path) for ws in wb.worksheets: create_excel_table(ws) for col_cells in ws.columns: length = max((len(str(c.value)) if c.value is not None else 0) for c in col_cells) ws.column_dimensions[col_cells[0].column_letter].width = min(length + 3, 50) wb.save(output_path) generated_files.append(output_filename) logs.append(f" ✓ Berhasil: {output_filename}") except Exception as e: logs.append(f" ERROR di {region}: {str(e)}") logs.append(f"\n✅ Selesai — {len(generated_files)} file dibuat") return jsonify({"success": True, "logs": logs, "files": generated_files}) @app.route("/process", methods=["POST"]) def process(): mode = request.form.get("mode") bulan = request.form.get("bulan", "").strip() logs = [] try: # ── MERGE RESPONSES ────────────────────────────────── if mode in ("merge_new", "merge_renew", "merge_gasi", "merge_klaim"): resp_files = request.files.getlist("resp_files") if not resp_files or resp_files[0].filename == "": return jsonify({"error": "Belum ada file responses yang diupload!"}), 400 expected = {"merge_new":EXPECTED_NEW,"merge_renew":EXPECTED_RENEW, "merge_gasi":EXPECTED_GASI,"merge_klaim":EXPECTED_KLAIM}[mode] flexible = {"merge_new":FLEXIBLE_NEW,"merge_renew":FLEXIBLE_RENEW, "merge_gasi":FLEXIBLE_GASI,"merge_klaim":FLEXIBLE_KLAIM}[mode] out_name = {"merge_new":"MERGED_RESPONSES_NEW","merge_renew":"MERGED_RESPONSES_RENEW", "merge_gasi":"MERGED_RESPONSES_GASI","merge_klaim":"MERGED_RESPONSES_KLAIM"}[mode] all_data = [] for f in resp_files: content = f.read() logs.append(f"Memeriksa: {f.filename}") df = pd.read_excel(io.BytesIO(content)) cleaned = make_unique_columns([clean_col(c) for c in df.columns]) df.columns = cleaned validate_flexible(df, expected, flexible, f.filename) logs.append(f" ✓ OK — {len(df)} baris") all_data.append(df) merged = pd.concat(all_data, ignore_index=True) out_file = os.path.join(OUTPUT_FOLDER, f"{out_name}.xlsx") with pd.ExcelWriter(out_file, engine="openpyxl") as writer: merged.to_excel(writer, sheet_name="Merged Responses", index=False) logs.append(f"\n✅ MERGE BERHASIL — {len(merged)} baris total") return jsonify({"success": True, "logs": logs, "filename": f"{out_name}.xlsx"}) # ── MERGE KLAIM + KLAIM DESK ───────────────────────── elif mode == "merge_klaim_desk": klaim_file = request.files.get("klaim_file") klaimdesk_file = request.files.get("klaimdesk_file") if not klaim_file or not klaimdesk_file: return jsonify({"error": "Upload file Klaim dan Klaim Desk dulu!"}), 400 df_k = pd.read_excel(io.BytesIO(klaim_file.read()), sheet_name="responses") df_k.columns = make_unique_columns([clean_col(c) for c in df_k.columns]) validate_exact(df_k, EXPECTED_KLAIM, klaim_file.filename, "Klaim") logs.append(f"Klaim OK — {len(df_k)} baris") df_kd = pd.read_excel(io.BytesIO(klaimdesk_file.read()), sheet_name="responses") df_kd.columns = [clean_col(c) for c in df_kd.columns] validate_exact(df_kd, EXPECTED_KLAIM_DESK, klaimdesk_file.filename, "Klaim Desk") logs.append(f"Klaim Desk OK — {len(df_kd)} baris") df_kd = df_kd.rename(columns=KLAIM_DESK_RENAME) for col in KLAIM_ONLY_COLS: if col not in df_kd.columns: df_kd[col] = None df_kd = df_kd.reindex(columns=EXPECTED_KLAIM) merged = pd.concat([df_k, df_kd], ignore_index=True) # Fill Bulan Layanan from SPK Date where missing if "Bulan Layanan" in merged.columns and "SPK Date" in merged.columns: mask = merged["Bulan Layanan"].isna() | (merged["Bulan Layanan"] == "") merged.loc[mask, "Bulan Layanan"] = merged.loc[mask, "SPK Date"].apply(spk_date_to_bulan) out_file = os.path.join(OUTPUT_FOLDER, "MERGED_KLAIM.xlsx") with pd.ExcelWriter(out_file, engine="openpyxl") as writer: merged.to_excel(writer, sheet_name="Merged Klaim", index=False) logs.append(f"\n✅ MERGE BERHASIL — {len(merged)} baris ({len(df_k)} Klaim + {len(df_kd)} Klaim Desk)") return jsonify({"success": True, "logs": logs, "filename": "MERGED_KLAIM.xlsx"}) # ── CSI NEW ────────────────────────────────────────── elif mode == "csi_new": resp_files = request.files.getlist("resp_files") neuro_file = request.files.get("neuro_file") if not resp_files or resp_files[0].filename == "": return jsonify({"error": "Upload Responses dulu!"}), 400 if not neuro_file: return jsonify({"error": "Upload Neurosensum dulu!"}), 400 if not bulan: return jsonify({"error": "Isi Bulan Reported dulu!"}), 400 all_resp = [] for f in resp_files: df = pd.read_excel(io.BytesIO(f.read())) df.columns = [str(c).strip() for c in df.columns] logs.append(f"Responses '{f.filename}' — {len(df)} baris") all_resp.append(df) df_resp = pd.concat(all_resp, ignore_index=True) neuro_content = neuro_file.read() df_neuro = read_neurosensum(neuro_content, f"{bulan} 2026") logs.append(f"Neurosensum — {len(df_neuro)} baris") rows = [build_new_from_resp(r, bulan) for _, r in df_resp.iterrows()] rows += [build_new_from_neuro(r, bulan) for _, r in df_neuro.iterrows()] df_out = pd.DataFrame(rows, columns=CSI_NEW_COLS) out_file = os.path.join(OUTPUT_FOLDER, "OUTPUT_CSI_NEW.xlsx") with pd.ExcelWriter(out_file, engine="openpyxl") as writer: df_out.to_excel(writer, sheet_name="RAW DATA CSI NEW", index=False) apply_formatting(out_file, "RAW DATA CSI NEW", SCALE_NEW) logs.append(f"\n✅ SELESAI — {len(df_out)} baris total") return jsonify({"success": True, "logs": logs, "filename": "OUTPUT_CSI_NEW.xlsx"}) # ── CSI RENEW ──────────────────────────────────────── elif mode == "csi_renew": resp_files = request.files.getlist("resp_files") neuro_file = request.files.get("neuro_file") if not resp_files or resp_files[0].filename == "": return jsonify({"error": "Upload Responses dulu!"}), 400 if not neuro_file: return jsonify({"error": "Upload Neurosensum dulu!"}), 400 if not bulan: return jsonify({"error": "Isi Bulan Reported dulu!"}), 400 all_resp = [] for f in resp_files: df = pd.read_excel(io.BytesIO(f.read())) df.columns = [str(c).strip() for c in df.columns] logs.append(f"Responses '{f.filename}' — {len(df)} baris") all_resp.append(df) df_resp = pd.concat(all_resp, ignore_index=True) neuro_content = neuro_file.read() df_neuro = read_neurosensum(neuro_content, f"{bulan} 2026") logs.append(f"Neurosensum — {len(df_neuro)} baris") rows = [build_renew_from_resp(r, bulan) for _, r in df_resp.iterrows()] rows += [build_renew_from_neuro(r, bulan) for _, r in df_neuro.iterrows()] df_out = pd.DataFrame(rows, columns=CSI_RENEW_COLS) out_file = os.path.join(OUTPUT_FOLDER, "OUTPUT_CSI_RENEW.xlsx") with pd.ExcelWriter(out_file, engine="openpyxl") as writer: df_out.to_excel(writer, sheet_name="RAW DATA CSI RENEW", index=False) apply_formatting(out_file, "RAW DATA CSI RENEW", SCALE_RENEW) logs.append(f"\n✅ SELESAI — {len(df_out)} baris total") return jsonify({"success": True, "logs": logs, "filename": "OUTPUT_CSI_RENEW.xlsx"}) # ── CSI GASI ───────────────────────────────────────── elif mode == "csi_gasi": resp_files = request.files.getlist("resp_files") neuro_file = request.files.get("neuro_file") if not resp_files or resp_files[0].filename == "": return jsonify({"error": "Upload Responses dulu!"}), 400 if not neuro_file: return jsonify({"error": "Upload Neurosensum dulu!"}), 400 if not bulan: return jsonify({"error": "Isi Bulan Reported dulu!"}), 400 all_resp = [] for f in resp_files: df = pd.read_excel(io.BytesIO(f.read())) df.columns = [str(c).strip() for c in df.columns] logs.append(f"Responses '{f.filename}' — {len(df)} baris") all_resp.append(df) df_resp = pd.concat(all_resp, ignore_index=True) neuro_content = neuro_file.read() df_neuro = read_neurosensum(neuro_content, f"{bulan} 2026") logs.append(f"Neurosensum — {len(df_neuro)} baris") rows = [build_gasi_from_resp(r, bulan) for _, r in df_resp.iterrows()] rows += [build_gasi_from_neuro(r, bulan) for _, r in df_neuro.iterrows()] df_out = pd.DataFrame(rows, columns=CSI_GASI_COLS) out_file = os.path.join(OUTPUT_FOLDER, "OUTPUT_CSI_GASI.xlsx") with pd.ExcelWriter(out_file, engine="openpyxl") as writer: df_out.to_excel(writer, sheet_name="RAW DATA CSI GASI", index=False) apply_formatting(out_file, "RAW DATA CSI GASI", SCALE_GASI) logs.append(f"\n✅ SELESAI — {len(df_out)} baris total") return jsonify({"success": True, "logs": logs, "filename": "OUTPUT_CSI_GASI.xlsx"}) # ── CSI KLAIM ───────────────────────────────────────── elif mode == "csi_klaim": klaim_file = request.files.get("klaim_file") klaimdesk_file = request.files.get("klaimdesk_file") if not klaim_file: return jsonify({"error": "Upload file Klaim dulu!"}), 400 if not klaimdesk_file: return jsonify({"error": "Upload file Klaim Desk dulu!"}), 400 if not bulan: return jsonify({"error": "Isi Bulan Reported dulu!"}), 400 df_k = pd.read_excel(io.BytesIO(klaim_file.read()), sheet_name="responses") df_k.columns = make_unique_columns([clean_col(c) for c in df_k.columns]) validate_exact(df_k, EXPECTED_KLAIM, klaim_file.filename, "Klaim") logs.append(f"Klaim OK — {len(df_k)} baris") df_kd = pd.read_excel(io.BytesIO(klaimdesk_file.read()), sheet_name="responses") df_kd.columns = [clean_col(c) for c in df_kd.columns] validate_exact(df_kd, EXPECTED_KLAIM_DESK, klaimdesk_file.filename, "Klaim Desk") logs.append(f"Klaim Desk OK — {len(df_kd)} baris") df_kd = df_kd.rename(columns=KLAIM_DESK_RENAME) rows = [build_klaim_from_resp(r, bulan) for _, r in df_k.iterrows()] rows += [build_klaim_from_klaimdesk(r, bulan) for _, r in df_kd.iterrows()] df_out = pd.DataFrame(rows, columns=CSI_KLAIM_COLS) out_file = os.path.join(OUTPUT_FOLDER, "OUTPUT_CSI_KLAIM.xlsx") with pd.ExcelWriter(out_file, engine="openpyxl") as writer: df_out.to_excel(writer, sheet_name="RAW DATA CSI KLAIM", index=False) apply_formatting(out_file, "RAW DATA CSI KLAIM", SCALE_KLAIM, hp_col_name="No. HP") logs.append(f"\n✅ SELESAI — {len(df_out)} baris ({len(df_k)} Klaim + {len(df_kd)} Klaim Desk)") return jsonify({"success": True, "logs": logs, "filename": "OUTPUT_CSI_KLAIM.xlsx"}) # ── MERGE ONLY (Responses + Neurosensum, tanpa olah CSI) ── elif mode in ("mergeonly_new", "mergeonly_renew", "mergeonly_gasi"): resp_files = request.files.getlist("resp_files") neuro_file = request.files.get("neuro_file") if not resp_files or resp_files[0].filename == "": return jsonify({"error": "Upload Responses dulu!"}), 400 if not neuro_file: return jsonify({"error": "Upload Neurosensum dulu!"}), 400 if not bulan: return jsonify({"error": "Isi Bulan Reported dulu!"}), 400 all_resp = [] for f in resp_files: df = pd.read_excel(io.BytesIO(f.read())) df.columns = [str(c).strip() for c in df.columns] logs.append(f"Responses '{f.filename}' — {len(df)} baris") all_resp.append(df) df_resp = pd.concat(all_resp, ignore_index=True) neuro_content = neuro_file.read() df_neuro = read_neurosensum(neuro_content, f"{bulan} 2026") logs.append(f"Neurosensum — {len(df_neuro)} baris") if mode == "mergeonly_new": build_resp = build_new_from_resp build_neuro = build_new_from_neuro csi_cols = CSI_NEW_COLS elif mode == "mergeonly_renew": build_resp = build_renew_from_resp build_neuro = build_renew_from_neuro csi_cols = CSI_RENEW_COLS else: build_resp = build_gasi_from_resp build_neuro = build_gasi_from_neuro csi_cols = CSI_GASI_COLS rows = [build_resp(r, bulan) for _, r in df_resp.iterrows()] rows += [build_neuro(r, bulan) for _, r in df_neuro.iterrows()] merged = pd.DataFrame(rows, columns=csi_cols) out_name = {"mergeonly_new": "MERGED_RESP_NEURO_NEW", "mergeonly_renew": "MERGED_RESP_NEURO_RENEW", "mergeonly_gasi": "MERGED_RESP_NEURO_GASI"}[mode] sheet_label = {"mergeonly_new": "Merged New", "mergeonly_renew": "Merged Renew", "mergeonly_gasi": "Merged Gasi"}[mode] out_file = os.path.join(OUTPUT_FOLDER, f"{out_name}.xlsx") with pd.ExcelWriter(out_file, engine="openpyxl") as writer: merged.to_excel(writer, sheet_name=sheet_label, index=False) logs.append(f"\n✅ MERGE BERHASIL — {len(merged)} baris ({len(df_resp)} Resp + {len(df_neuro)} Neuro)") return jsonify({"success": True, "logs": logs, "filename": f"{out_name}.xlsx"}) else: return jsonify({"error": f"Mode tidak dikenal: {mode}"}), 400 except Exception as e: tb = traceback.format_exc() return jsonify({"error": str(e), "traceback": tb}), 500 # ========================================================= # CSI FOR CORPLAN # ========================================================= CORPLAN_COLS = [ "Layanan", "Polis", "Name on Policy", "Nama Marketing", "Cabang/Kota", "CABANG", "CABANG ACQ", "REGION", "REGION ACQ", "Bulan Layanan", "Bulan Reported", "Segment", "Media", "SARAN", "Kategori", "Nilai Keseluruhan" ] def build_corplan_row(row, layanan): """Build a standardised Corplan row from any CSI sheet row.""" # HP col varies if layanan == "New": hp = row.get("HP"); polis = row.get("Polis"); name = row.get("Name on Policy") marketing = row.get("Nama Marketing"); cabang_kota = row.get("Cabang/Kota") cabang = row.get("CABANG"); cabang_acq = row.get("Cabang Acq") region = row.get("REGION"); region_acq = row.get("REGION ACQ") bulan_l = row.get("Bulan Layanan"); bulan_r = row.get("Bulan Reported") segment = row.get("Segment"); media = row.get("Media") saran = row.get("SARAN"); kategori = row.get("KATEGORI FEEDBACK") nilai = row.get("NILAI KESELURUHAN") elif layanan == "Renew": hp = row.get("HP"); polis = row.get("polis") or row.get("Polis") name = row.get("Name on Policy"); marketing = row.get("Nama Marketing") cabang_kota = row.get("Cabang/Kota"); cabang = row.get("CABANG") cabang_acq = row.get("CABANG ACQ"); region = row.get("REGION") region_acq = row.get("REGION ACQ"); bulan_l = row.get("Bulan Layanan") bulan_r = row.get("Bulan Reported"); segment = row.get("Segment") media = row.get("Media"); saran = row.get("SARAN") kategori = row.get("Kategori") or row.get("KATEGORI FEEDBACK") nilai = row.get("NILAI KESELURUHAN") elif layanan == "Klaim": hp = row.get("No. HP"); polis = row.get("PolicyNo") name = row.get("Customer Name"); marketing = row.get("Surveyor Name") cabang_kota = None; cabang = None; cabang_acq = None region = row.get("Region"); region_acq = None bulan_l = row.get("Bulan Layanan") or spk_date_to_bulan(row.get("SPK DATE")) bulan_r = row.get("Bulan Reported"); segment = None media = row.get("Media"); saran = row.get("SARAN") or row.get("Saran") kategori = row.get("Kategori Feedback") or row.get("Kategori") nilai = row.get("NILAI KESELURUHAN") elif layanan == "Gasi": hp = row.get("HP"); polis = row.get("polis") or row.get("Polis") name = row.get("Name on Policy"); marketing = row.get("Petugas") cabang_kota = None; cabang = row.get("Cabang"); cabang_acq = None region = row.get("REGION"); region_acq = None bulan_l = row.get("Bulan Layanan"); bulan_r = row.get("Bulan Reported") segment = None; media = row.get("Media") saran = row.get("SARAN"); kategori = row.get("Kategori Feedback") or row.get("Kategori") nilai = row.get("NILAI KESELURUHAN") else: return None # Skip rows where Nilai Keseluruhan is 0, blank, or NaN try: if nilai is None or (isinstance(nilai, float) and pd.isna(nilai)): return None if float(nilai) == 0: return None except (ValueError, TypeError): if str(nilai).strip() in ("", "0"): return None return { "Layanan": layanan, "Polis": polis, "Name on Policy": name, "Nama Marketing": marketing, "Cabang/Kota": cabang_kota, "CABANG": cabang, "CABANG ACQ": cabang_acq, "REGION": region, "REGION ACQ": region_acq, "Bulan Layanan": bulan_l, "Bulan Reported": bulan_r, "Segment": segment, "Media": media, "SARAN": saran, "Kategori": kategori, "Nilai Keseluruhan": nilai, } @app.route("/csi_corplan", methods=["POST"]) def csi_corplan(): source_file = request.files.get("source_file") bulan_filter = request.form.get("bulan", "").strip() if not source_file: return jsonify({"error": "Upload file sumber CSI dulu!"}), 400 if not bulan_filter: return jsonify({"error": "Isi Bulan Reported untuk filter dulu!"}), 400 logs = [] try: content = source_file.read() excel_file = pd.ExcelFile(io.BytesIO(content)) logs.append(f"Sheet tersedia: {', '.join(excel_file.sheet_names)}") bulan_normalized = normalize_bulan(bulan_filter) logs.append(f"Filter bulan: {bulan_normalized}") SHEET_MAP = {"New": "New", "Renew": "Renew", "Klaim": "Klaim", "Gasi": "Gasi"} all_rows = [] for layanan, sheet_name in SHEET_MAP.items(): if sheet_name not in excel_file.sheet_names: logs.append(f"Sheet '{sheet_name}' tidak ditemukan, dilewati") continue df = pd.read_excel(io.BytesIO(content), sheet_name=sheet_name) df.columns = [str(c).strip() for c in df.columns] before = len(df) if "Bulan Reported" in df.columns: df = df[df["Bulan Reported"].astype(str).str.strip().apply(normalize_bulan) == bulan_normalized] else: logs.append(f"Sheet '{sheet_name}' tidak punya kolom Bulan Reported, dilewati") continue built = [] for _, row in df.iterrows(): r = build_corplan_row(row, layanan) if r is not None: built.append(r) logs.append(f"{layanan}: {len(built)} baris valid dari {before} total (filter+skip {before - len(built)})") all_rows.extend(built) if not all_rows: return jsonify({"error": f"Tidak ada data untuk bulan {bulan_normalized}!"}), 400 df_out = pd.DataFrame(all_rows, columns=CORPLAN_COLS) output_filename = f"OUTPUT_CSI_CORPLAN_{bulan_normalized}.xlsx" out_file = os.path.join(OUTPUT_FOLDER, output_filename) with pd.ExcelWriter(out_file, engine="openpyxl") as writer: df_out.to_excel(writer, sheet_name="CSI FOR CORPLAN", index=False) wb = load_workbook(out_file) ws = wb["CSI FOR CORPLAN"] from openpyxl.worksheet.table import Table, TableStyleInfo max_row = ws.max_row; max_col = ws.max_column if max_row >= 2: end_col = get_column_letter(max_col) tbl = Table(displayName="CorplanTable", ref=f"A1:{end_col}{max_row}") tbl.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True) ws.add_table(tbl) for col_cells in ws.columns: length = max((len(str(c.value)) if c.value is not None else 0) for c in col_cells) ws.column_dimensions[col_cells[0].column_letter].width = min(length + 3, 50) wb.save(out_file) logs.append(f"\n✅ CSI FOR CORPLAN SELESAI — {len(df_out)} baris") return jsonify({"success": True, "logs": logs, "filename": output_filename}) except Exception as e: tb = traceback.format_exc() return jsonify({"error": str(e), "traceback": tb}), 500 # ========================================================= # SLA REGION SPLITTER # ========================================================= SLA_ALL_REGIONS = [ "Jatim Bali Kalimantan", "Telerenewal", "MGO", "DKI", "Sumatera Jateng", "Preferred & Prokhus", "Jabar Sulawesi", ] SLA_REGION_CONFIG = { "NEW": ["Jatim Bali Kalimantan","MGO","DKI","Sumatera Jateng","Preferred & Prokhus","Jabar Sulawesi","Digital Channel","HO"], "RENEW": ["Jatim Bali Kalimantan","Telerenewal","MGO","DKI","Sumatera Jateng","Preferred & Prokhus","Jabar Sulawesi","Digital Channel","HO"], } # Full region->branch mapping read from the SUMMARY TOTAL structure # Col A = Region (merged), Col B = Branch (individual rows), for detail section rows 18+ # Region in summary top section (rows 8-15): col B # Detail section (rows 19+): col A=region(merged), col B=branch # Static branch mapping per region (from SUMMARY TOTAL structure) SLA_REGION_BRANCHES = { "DKI": ["Serpong","Serang","Bekasi","Cilandak","Pondok Indah","Kelapa Gading 2","Pluit","Tendean"], "DKI Jakarta": ["Serpong","Serang","Bekasi","Cilandak","Pondok Indah","Kelapa Gading 2","Pluit","Tendean"], "Jabar Sulawesi": ["Bandung","Karawang","Bogor","Cirebon","Manado","Makassar"], "Sumatera Jateng": ["Jogja","Solo","Semarang","Lampung","Medan","Padang","Palembang","Pekanbaru","Batam","Jambi"], "Jatim Bali Kalimantan": ["Balikpapan","Pontianak","Banjarmasin","Denpasar","Kediri","Malang","Samarinda","Surabaya"], "PREFERRED & PROKHUS": ["Kelapa Gading","Joglo","Mangga Dua - Pontianak","BSD - Serang","Area Unit DIY","Cilandak - Puri"], "Preferred & Prokhus": ["Kelapa Gading","Joglo","Mangga Dua - Pontianak","BSD - Serang","Area Unit DIY","Cilandak - Puri"], } def calc_sla_stats(df, region_col, filter_val, satisfaction_col, completion_col): """Calculate SLA stats for a given filter value.""" mask = df[region_col].astype(str).str.strip() == str(filter_val).strip() sub = df[mask] total = len(sub) if total == 0: return {"total": 0, "incomplete": 0, "complete": 0, "pct_complete": None, "riang": 0, "tidak_riang": 0, "total_sat": 0, "pct_s": None, "pct_ns": None, "ytd": None} incomplete = int((sub[completion_col].astype(str).str.strip() == "Incomplete").sum()) if completion_col and completion_col in sub.columns else 0 complete = int((sub[completion_col].astype(str).str.strip() == "Complete").sum()) if completion_col and completion_col in sub.columns else 0 pct_complete = (complete / total) if total > 0 else None riang = int((sub[satisfaction_col].astype(str).str.strip() == "Riang").sum()) if satisfaction_col and satisfaction_col in sub.columns else 0 tidak_riang= int((sub[satisfaction_col].astype(str).str.strip() == "Tidak Riang").sum())if satisfaction_col and satisfaction_col in sub.columns else 0 total_sat = riang + tidak_riang pct_s = (riang / total_sat) if total_sat > 0 else None pct_ns = (1 - pct_s) if pct_s is not None else None ytd = (pct_s * 100) if pct_s is not None else None return {"total": total, "incomplete": incomplete, "complete": complete, "pct_complete": pct_complete, "riang": riang, "tidak_riang": tidak_riang, "total_sat": total_sat, "pct_s": pct_s, "pct_ns": pct_ns, "ytd": ytd} def write_summary_total(ws_sum, region, df_new, df_renew, has_new, has_renew): """Write SUMMARY TOTAL sheet (no table) for a specific region.""" from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter # --- Detect correct column names from actual data --- def find_col(df, candidates): for c in candidates: if c in df.columns: return c return None # NEW column names new_region_col = find_col(df_new, ["Region","region","REGION"]) if has_new else None new_branch_col = find_col(df_new, ["Branch","branch","BRANCH"]) if has_new else None new_sat_col = find_col(df_new, ["AM","Satisfaction","Riang/Tidak Riang"]) if has_new else None new_comp_col = find_col(df_new, ["AN","Complete/Incomplete","Status Completed"]) if has_new else None # Try index-based if name not found (col AM = index 38, AN = 39 in 0-based) if has_new and new_sat_col is None and len(df_new.columns) > 38: new_sat_col = df_new.columns[38] # col AM (0-based: 38) if has_new and new_comp_col is None and len(df_new.columns) > 39: new_comp_col = df_new.columns[39] # col AN (0-based: 39) # RENEW column names ren_region_col = find_col(df_renew, ["Region","region","REGION"]) if has_renew else None ren_branch_col = find_col(df_renew, ["Branch","branch","BRANCH"]) if has_renew else None ren_sat_col = find_col(df_renew, ["AM","Satisfaction","Riang/Tidak Riang"]) if has_renew else None ren_comp_col = find_col(df_renew, ["AN","Complete/Incomplete","Status Completed"]) if has_renew else None ren_prod_sat_col = find_col(df_renew, ["AP","RenewalSatisfaction"]) if has_renew else None if has_renew and ren_sat_col is None and len(df_renew.columns) > 38: ren_sat_col = df_renew.columns[38] if has_renew and ren_comp_col is None and len(df_renew.columns) > 39: ren_comp_col = df_renew.columns[39] if has_renew and ren_prod_sat_col is None and len(df_renew.columns) > 41: ren_prod_sat_col = df_renew.columns[41] # col AP (0-based: 41) # Normalize region name for lookup region_variants = [region, region.upper(), region.lower()] region_norm = region.upper() if "PROKHUS" in region.upper() or "PREFERRED" in region.upper() else region # Get branch list for this region branches = [] for rv in region_variants + [region_norm]: if rv in SLA_REGION_BRANCHES: branches = SLA_REGION_BRANCHES[rv] break # Filter NEW data for this region if has_new and new_region_col: df_new_r = df_new[df_new[new_region_col].astype(str).str.strip() == region] else: df_new_r = pd.DataFrame() # Filter RENEW data for this region if has_renew and ren_region_col: df_renew_r = df_renew[df_renew[ren_region_col].astype(str).str.strip() == region] else: df_renew_r = pd.DataFrame() # --- Styles --- bold = Font(bold=True) header_fill = PatternFill("solid", fgColor="D9E1F2") title_fill = PatternFill("solid", fgColor="BDD7EE") nasional_fill= PatternFill("solid", fgColor="FFF2CC") center = Alignment(horizontal="center", vertical="center") left = Alignment(horizontal="left", vertical="center") pct_fmt = "0.00%" num_fmt = "#,##0" def pct(v): return v if v is not None else "" # ---- ROW 1: empty ---- # ---- ROW 4: Section titles ---- row = 4 # NEW title (cols B-K) ws_sum.merge_cells(f"B{row}:K{row}") c = ws_sum.cell(row=row, column=2, value="NEW") c.font = Font(bold=True, size=14); c.alignment = center # Renewal title (cols O-X) ws_sum.merge_cells(f"O{row}:X{row}") c = ws_sum.cell(row=row, column=15, value="Renewal") c.font = Font(bold=True, size=14); c.alignment = center # Renewal Production title (cols AB-AG) ws_sum.merge_cells(f"AB{row}:AG{row}") c = ws_sum.cell(row=row, column=28, value="Renewal Production") c.font = Font(bold=True, size=14); c.alignment = center # ---- ROW 6: Column headers ---- row = 6 new_headers = ["","Total Data","Incomplete","Complete","% Complete","Riang","Tidak Riang","Total","% S","%NS","YTD"] renew_headers = ["","Total Data","Incomplete","Complete","% Complete","Riang","Tidak Riang","Total","% S","%NS","YTD"] prod_headers = ["","Total Data","Riang","Tidak Riang","% S","%NS","YTD"] for i, h in enumerate(new_headers): c = ws_sum.cell(row=row, column=2+i, value=h) c.font = bold; c.fill = header_fill; c.alignment = center for i, h in enumerate(renew_headers): c = ws_sum.cell(row=row, column=15+i, value=h) c.font = bold; c.fill = header_fill; c.alignment = center for i, h in enumerate(prod_headers): c = ws_sum.cell(row=row, column=28+i, value=h) c.font = bold; c.fill = header_fill; c.alignment = center # ---- ROWS 7-16: Region-level summary (top section) ---- # Get all regions for top section (from SLA_ALL_REGIONS) all_top_regions = [r for r in SLA_ALL_REGIONS if r != "Telerenewal"] top_rows_start = 7 # Dealer Management row (row 7) - only Renew/Prod row7_label = "Dealer Management" ws_sum.cell(row=7, column=15, value=row7_label).font = bold ws_sum.cell(row=7, column=28, value=row7_label).font = bold # Dealer Management uses col M (region-level) in renew if has_renew and ren_region_col: dm_mask = df_renew[ren_region_col].astype(str).str.strip() == "Dealer Management" dm_data = df_renew[dm_mask] dm_total = len(dm_data) if dm_total > 0: dm_riang = int((dm_data[ren_sat_col].astype(str).str.strip() == "Riang").sum()) if ren_sat_col else 0 dm_tr = int((dm_data[ren_sat_col].astype(str).str.strip() == "Tidak Riang").sum()) if ren_sat_col else 0 dm_ts = dm_riang + dm_tr dm_ps = dm_riang / dm_ts if dm_ts > 0 else None for col, val, fmt in [ (16, dm_total, num_fmt),(17, None, None),(18, None, None),(19, None, pct_fmt), (20, dm_riang, num_fmt),(21, dm_tr, num_fmt),(22, dm_ts, num_fmt), (23, pct(dm_ps), pct_fmt),(24, pct(1-dm_ps) if dm_ps else "", pct_fmt), (25, pct(dm_ps*100) if dm_ps else "", "0.00"), ]: c = ws_sum.cell(row=7, column=col, value=val) if fmt: c.number_format = fmt c.alignment = center # Rows 8-15: each top-level region top_data_start = 8 top_region_list = [r for r in SLA_ALL_REGIONS if r != "Dealer Management"] def write_top_row(r, rname): # col B = region name (NEW) ws_sum.cell(row=r, column=2, value=rname).alignment = left # col O = region name (Renew) ws_sum.cell(row=r, column=15, value=rname).alignment = left # col AB = region name (Prod) ws_sum.cell(row=r, column=28, value=rname).alignment = left # NEW stats (use col M = region_col, col AM = sat, col AN = comp) if has_new and new_region_col and rname in SLA_REGION_CONFIG["NEW"]: s = calc_sla_stats(df_new, new_region_col, rname, new_sat_col, new_comp_col) ws_sum.cell(row=r, column=3, value=s["total"]).number_format = num_fmt ws_sum.cell(row=r, column=4, value=s["incomplete"]).number_format = num_fmt ws_sum.cell(row=r, column=5, value=s["complete"]).number_format = num_fmt ws_sum.cell(row=r, column=6, value=pct(s["pct_complete"])).number_format = pct_fmt ws_sum.cell(row=r, column=7, value=s["riang"]).number_format = num_fmt ws_sum.cell(row=r, column=8, value=s["tidak_riang"]).number_format = num_fmt ws_sum.cell(row=r, column=9, value=s["total_sat"]).number_format = num_fmt ws_sum.cell(row=r, column=10, value=pct(s["pct_s"])).number_format = pct_fmt ws_sum.cell(row=r, column=11, value=pct(s["pct_ns"])).number_format = pct_fmt ws_sum.cell(row=r, column=12, value=pct(s["ytd"])).number_format = "0.00" for col in range(3, 13): ws_sum.cell(row=r, column=col).alignment = center # RENEW stats if has_renew and ren_region_col and rname in SLA_REGION_CONFIG["RENEW"]: s = calc_sla_stats(df_renew, ren_region_col, rname, ren_sat_col, ren_comp_col) ws_sum.cell(row=r, column=16, value=s["total"]).number_format = num_fmt ws_sum.cell(row=r, column=17, value=s["incomplete"]).number_format = num_fmt ws_sum.cell(row=r, column=18, value=s["complete"]).number_format = num_fmt ws_sum.cell(row=r, column=19, value=pct(s["pct_complete"])).number_format = pct_fmt ws_sum.cell(row=r, column=20, value=s["riang"]).number_format = num_fmt ws_sum.cell(row=r, column=21, value=s["tidak_riang"]).number_format = num_fmt ws_sum.cell(row=r, column=22, value=s["total_sat"]).number_format = num_fmt ws_sum.cell(row=r, column=23, value=pct(s["pct_s"])).number_format = pct_fmt ws_sum.cell(row=r, column=24, value=pct(s["pct_ns"])).number_format = pct_fmt ws_sum.cell(row=r, column=25, value=pct(s["ytd"])).number_format = "0.00" for col in range(16, 26): ws_sum.cell(row=r, column=col).alignment = center # Renewal Production (col AP) if ren_prod_sat_col: sp = calc_sla_stats(df_renew, ren_region_col, rname, ren_prod_sat_col, None) ws_sum.cell(row=r, column=29, value=sp["total"]).number_format = num_fmt ws_sum.cell(row=r, column=30, value=sp["riang"]).number_format = num_fmt ws_sum.cell(row=r, column=31, value=sp["tidak_riang"]).number_format = num_fmt ws_sum.cell(row=r, column=32, value=pct(sp["pct_s"])).number_format = pct_fmt ws_sum.cell(row=r, column=33, value=pct(sp["pct_ns"])).number_format = pct_fmt ws_sum.cell(row=r, column=34, value=pct(sp["ytd"])).number_format = "0.00" for col in range(29, 35): ws_sum.cell(row=r, column=col).alignment = center for i, rname in enumerate(top_region_list): write_top_row(top_data_start + i, rname) # Nasional row nasional_row = top_data_start + len(top_region_list) for col in [2, 15, 28]: c = ws_sum.cell(row=nasional_row, column=col, value="Nasional") c.font = bold; c.fill = nasional_fill # Sum NEW if has_new: for col_idx, formula_col in [(3,"C"),(4,"D"),(5,"E"),(7,"G"),(8,"H"),(9,"I")]: r_start = top_data_start; r_end = nasional_row - 1 total = sum( (ws_sum.cell(row=rr, column=col_idx).value or 0) for rr in range(r_start, r_end + 1) if isinstance(ws_sum.cell(row=rr, column=col_idx).value, (int, float)) ) c = ws_sum.cell(row=nasional_row, column=col_idx, value=total) c.number_format = num_fmt; c.font = bold; c.alignment = center # pct complete tot = ws_sum.cell(row=nasional_row, column=3).value or 0 comp = ws_sum.cell(row=nasional_row, column=5).value or 0 ws_sum.cell(row=nasional_row, column=6, value=comp/tot if tot else "").number_format = pct_fmt # pct S ri = ws_sum.cell(row=nasional_row, column=7).value or 0 ts = ws_sum.cell(row=nasional_row, column=9).value or 0 ps = ri/ts if ts else None ws_sum.cell(row=nasional_row, column=10, value=pct(ps)).number_format = pct_fmt ws_sum.cell(row=nasional_row, column=11, value=pct(1-ps) if ps else "").number_format = pct_fmt ws_sum.cell(row=nasional_row, column=12, value=pct(ps*100) if ps else "").number_format = "0.00" for col in range(3, 13): ws_sum.cell(row=nasional_row, column=col).font = bold # ---- ROWS 18+: Detail section (branch level) ---- detail_start = nasional_row + 3 # Header row h_row = detail_start ws_sum.cell(row=h_row, column=1, value="Region").font = bold ws_sum.cell(row=h_row, column=2, value="Branch").font = bold ws_sum.cell(row=h_row, column=14, value="Region").font = bold ws_sum.cell(row=h_row, column=15, value="Branch").font = bold ws_sum.cell(row=h_row, column=27, value="Region").font = bold ws_sum.cell(row=h_row, column=28, value="Branch").font = bold for col_offset, headers in [ (3, ["Total Data","Incomplete","Complete","% Complete","Riang","Tidak Riang","Total","% S","%NS","YTD"]), (16, ["Total Data","Incomplete","Complete","% Complete","Riang","Tidak Riang","Total","% S","%NS","YTD"]), (29, ["Total Data","Riang","Tidak Riang","% S","%NS","YTD"]), ]: for i, h in enumerate(headers): c = ws_sum.cell(row=h_row, column=col_offset+i, value=h) c.font = bold; c.fill = header_fill; c.alignment = center # Write branch rows grouped by region current_row = detail_start + 1 # Only write data for the target region region_key = region # Normalize for rk in SLA_REGION_BRANCHES: if rk.upper() == region.upper(): region_key = rk break target_branches = SLA_REGION_BRANCHES.get(region_key, []) # Also try from actual data if branches unknown if not target_branches: if has_new and new_branch_col and len(df_new_r) > 0: target_branches = df_new_r[new_branch_col].dropna().unique().tolist() elif has_renew and ren_branch_col and len(df_renew_r) > 0: target_branches = df_renew_r[ren_branch_col].dropna().unique().tolist() if target_branches: region_start_row = current_row # Write region label (merged) in col A, N, AA ws_sum.cell(row=current_row, column=1, value=region) ws_sum.cell(row=current_row, column=14, value=region) ws_sum.cell(row=current_row, column=27, value=region) for b_idx, branch in enumerate(target_branches): r = current_row + b_idx # Branch name ws_sum.cell(row=r, column=2, value=branch).alignment = left ws_sum.cell(row=r, column=15, value=branch).alignment = left ws_sum.cell(row=r, column=28, value=branch).alignment = left # NEW branch stats (uses col N = branch col in NewFIX) if has_new and new_branch_col and not df_new_r.empty: s = calc_sla_stats(df_new_r, new_branch_col, branch, new_sat_col, new_comp_col) for col, val, fmt in [ (3,s["total"],num_fmt),(4,s["incomplete"],num_fmt),(5,s["complete"],num_fmt), (6,pct(s["pct_complete"]),pct_fmt),(7,s["riang"],num_fmt),(8,s["tidak_riang"],num_fmt), (9,s["total_sat"],num_fmt),(10,pct(s["pct_s"]),pct_fmt),(11,pct(s["pct_ns"]),pct_fmt), (12,pct(s["ytd"]),"0.00"), ]: c = ws_sum.cell(row=r, column=col, value=val) if fmt: c.number_format = fmt c.alignment = center # RENEW branch stats if has_renew and ren_branch_col and not df_renew_r.empty: s = calc_sla_stats(df_renew_r, ren_branch_col, branch, ren_sat_col, ren_comp_col) for col, val, fmt in [ (16,s["total"],num_fmt),(17,s["incomplete"],num_fmt),(18,s["complete"],num_fmt), (19,pct(s["pct_complete"]),pct_fmt),(20,s["riang"],num_fmt),(21,s["tidak_riang"],num_fmt), (22,s["total_sat"],num_fmt),(23,pct(s["pct_s"]),pct_fmt),(24,pct(s["pct_ns"]),pct_fmt), (25,pct(s["ytd"]),"0.00"), ]: c = ws_sum.cell(row=r, column=col, value=val) if fmt: c.number_format = fmt c.alignment = center # Renewal Production if ren_prod_sat_col: sp = calc_sla_stats(df_renew_r, ren_branch_col, branch, ren_prod_sat_col, None) for col, val, fmt in [ (29,sp["total"],num_fmt),(30,sp["riang"],num_fmt),(31,sp["tidak_riang"],num_fmt), (32,pct(sp["pct_s"]),pct_fmt),(33,pct(sp["pct_ns"]),pct_fmt),(34,pct(sp["ytd"]),"0.00"), ]: c = ws_sum.cell(row=r, column=col, value=val) if fmt: c.number_format = fmt c.alignment = center # Merge region cells in col A, N, AA end_row = current_row + len(target_branches) - 1 if end_row > current_row: ws_sum.merge_cells(f"A{current_row}:A{end_row}") ws_sum.merge_cells(f"N{current_row}:N{end_row}") ws_sum.merge_cells(f"AA{current_row}:AA{end_row}") for col in [1, 14, 27]: ws_sum.cell(row=current_row, column=col).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) # Nasional row also in detail section nasional_detail_row = current_row + len(target_branches) + 1 for col in [1, 14, 27]: ws_sum.cell(row=nasional_detail_row, column=col, value="Nasional").font = bold for col in [2, 15, 28]: ws_sum.cell(row=nasional_detail_row, column=col, value="Nasional").font = bold # Autofit columns for col_cells in ws_sum.columns: try: length = max((len(str(c.value)) if c.value is not None else 0) for c in col_cells) ws_sum.column_dimensions[col_cells[0].column_letter].width = min(length + 3, 40) except Exception: pass # ========================================================= # SUMMARY TOTAL - COPY & ADJUST FROM TEMPLATE # ========================================================= SLA_SUMMARY_REGION_CFG = { "DKI": {"top_row": 9, "detail": (19, 26)}, "Jabar Sulawesi": {"top_row": 10, "detail": (27, 32)}, "Sumatera Jateng": {"top_row": 11, "detail": (33, 42)}, "Jatim Bali Kalimantan": {"top_row": 12, "detail": (43, 50)}, "Preferred & Prokhus": {"top_row": 13, "detail": (51, 56)}, "MGO": {"top_row": 14, "detail": None}, "Telerenewal": {"top_row": 15, "detail": None}, } def copy_ws(wb_src, sheet_name, wb_dst, dst_name): """Copy a worksheet including styles and merges.""" ws_src = wb_src[sheet_name] ws_dst = wb_dst.create_sheet(dst_name) for col_letter, col_dim in ws_src.column_dimensions.items(): ws_dst.column_dimensions[col_letter].width = col_dim.width for row_num, row_dim in ws_src.row_dimensions.items(): ws_dst.row_dimensions[row_num].height = row_dim.height for row in ws_src.iter_rows(): for cell in row: new_cell = ws_dst.cell(row=cell.row, column=cell.column, value=cell.value) if cell.has_style: new_cell.font = copy.copy(cell.font) new_cell.fill = copy.copy(cell.fill) new_cell.border = copy.copy(cell.border) new_cell.alignment = copy.copy(cell.alignment) new_cell.number_format = cell.number_format for mc in ws_src.merged_cells.ranges: ws_dst.merge_cells(str(mc)) return ws_dst def adjust_summary_for_region(ws, region): """Adjust SUMMARY TOTAL sheet for a specific region per spec.""" from openpyxl.styles import Alignment as OxAlign cfg = SLA_SUMMARY_REGION_CFG.get(region) if not cfg: return target_top_row = cfg["top_row"] detail_range = cfg["detail"] def read_row(r): data = {} for cell in ws[r]: if cell.value is not None or cell.has_style: data[cell.column] = { "value": cell.value, "font": copy.copy(cell.font) if cell.has_style else None, "fill": copy.copy(cell.fill) if cell.has_style else None, "border": copy.copy(cell.border) if cell.has_style else None, "alignment": copy.copy(cell.alignment) if cell.has_style else None, "number_format": cell.number_format, } return data def read_rows(start, end): return [read_row(r) for r in range(start, end + 1)] def write_row(target_r, row_data): for col, cd in row_data.items(): c = ws.cell(row=target_r, column=col, value=cd["value"]) if cd.get("font"): c.font = cd["font"] if cd.get("fill"): c.fill = cd["fill"] if cd.get("border"): c.border = cd["border"] if cd.get("alignment"): c.alignment = cd["alignment"] if cd.get("number_format"):c.number_format = cd["number_format"] # Capture data before clearing top_row_data = read_row(target_top_row) nasional_data = read_row(16) detail_data = read_rows(detail_range[0], detail_range[1]) if detail_range else [] # Unmerge all cells from row 9 onwards to_remove = [str(mc) for mc in list(ws.merged_cells.ranges) if mc.min_row >= 9] for mc_str in to_remove: try: ws.merged_cells.remove(mc_str) except Exception: pass # Clear rows 9-17 and 19+ max_r = max(ws.max_row, 68) for r in list(range(9, 18)) + list(range(19, max_r + 1)): for c in range(1, ws.max_column + 1): try: ws.cell(row=r, column=c).value = None except Exception: pass # Write adjusted rows write_row(9, top_row_data) write_row(10, nasional_data) if not detail_data: for c in range(1, ws.max_column + 1): try: ws.cell(row=18, column=c).value = None except Exception: pass # Write detail rows starting at row 19 if detail_data: for i, rd in enumerate(detail_data): write_row(19 + i, rd) d_start = 19 d_end = 19 + len(detail_data) - 1 if d_end > d_start: for col_range in [f"A{d_start}:A{d_end}", f"N{d_start}:N{d_end}", f"AA{d_start}:AA{d_end}"]: try: ws.merge_cells(col_range) except Exception: pass ctr = OxAlign(horizontal="center", vertical="center") for col in [1, 14, 27]: ws.cell(row=d_start, column=col).alignment = ctr @app.route("/split_sla", methods=["POST"]) def split_sla(): source_file = request.files.get("source_file") bulan = request.form.get("bulan", "").strip() tahun = request.form.get("tahun", "").strip() regions_raw = request.form.get("regions", "") if not source_file: return jsonify({"error": "Upload file SLA dulu!"}), 400 if not bulan: return jsonify({"error": "Isi Bulan dulu!"}), 400 if not tahun: return jsonify({"error": "Isi Tahun dulu!"}), 400 selected_regions = SLA_ALL_REGIONS if regions_raw == "ALL" else [r for r in regions_raw.split("||") if r] logs = [] try: content = source_file.read() excel_file = pd.ExcelFile(io.BytesIO(content)) logs.append(f"Sheet tersedia: {', '.join(excel_file.sheet_names)}") # Load sheets df_new = df_renew = None if "NewFIX" in excel_file.sheet_names: df_new = pd.read_excel(io.BytesIO(content), sheet_name="NewFIX") df_new.columns = [str(c).strip() for c in df_new.columns] logs.append(f"NewFIX: {len(df_new)} baris") if "RenewFIX" in excel_file.sheet_names: df_renew = pd.read_excel(io.BytesIO(content), sheet_name="RenewFIX") df_renew.columns = [str(c).strip() for c in df_renew.columns] logs.append(f"RenewFIX: {len(df_renew)} baris") if df_new is None and df_renew is None: return jsonify({"error": "Sheet NewFIX atau RenewFIX tidak ditemukan!"}), 400 generated_files = [] for region in selected_regions: logs.append(f"\n— Region: {region} —") has_new = df_new is not None and region in SLA_REGION_CONFIG["NEW"] has_renew = df_renew is not None and region in SLA_REGION_CONFIG["RENEW"] if not has_new and not has_renew: logs.append(f" Skip — region tidak ada di config") continue output_filename = f"Raw Data SLA {region} YTD {bulan}{tahun}.xlsx" output_path = os.path.join(OUTPUT_FOLDER, secure_filename(output_filename)) has_sheet = False with pd.ExcelWriter(output_path, engine="openpyxl") as writer: # NEW sheet if has_new: region_col = None for c in df_new.columns: if str(c).strip() == "Region": region_col = c; break if region_col: filtered = df_new[df_new[region_col].astype(str).str.strip() == region].copy() logs.append(f" NEW: {len(filtered)} baris") if len(filtered) > 0: filtered.to_excel(writer, sheet_name="NEW", index=False) has_sheet = True # RENEW sheet if has_renew: region_col = None for c in df_renew.columns: if str(c).strip() == "Region": region_col = c; break if region_col: filtered = df_renew[df_renew[region_col].astype(str).str.strip() == region].copy() logs.append(f" RENEW: {len(filtered)} baris") if len(filtered) > 0: filtered.to_excel(writer, sheet_name="RENEW", index=False) has_sheet = True if not has_sheet: logs.append(f" Tidak ada data, file dilewati") if os.path.exists(output_path): os.remove(output_path) continue # Format table + autofit + SUMMARY TOTAL wb = load_workbook(output_path) for ws_sheet in wb.worksheets: create_excel_table(ws_sheet) for col_cells in ws_sheet.columns: length = max((len(str(c.value)) if c.value is not None else 0) for c in col_cells) ws_sheet.column_dimensions[col_cells[0].column_letter].width = min(length + 3, 50) # Add SUMMARY TOTAL from template if available in source file wb_src = load_workbook(io.BytesIO(content)) if "SUMMARY TOTAL" in wb_src.sheetnames: ws_sum = copy_ws(wb_src, "SUMMARY TOTAL", wb, "SUMMARY TOTAL") adjust_summary_for_region(ws_sum, region) # Move SUMMARY TOTAL to first position sheets = wb.sheetnames idx = sheets.index("SUMMARY TOTAL") wb.move_sheet("SUMMARY TOTAL", offset=-idx) wb.save(output_path) generated_files.append(output_filename) logs.append(f" ✓ Berhasil: {output_filename}") logs.append(f"\n✅ Selesai — {len(generated_files)} file dibuat") return jsonify({"success": True, "logs": logs, "files": generated_files}) except Exception as e: tb = traceback.format_exc() return jsonify({"error": str(e), "traceback": tb}), 500 # ========================================================= # PICA GENERATOR # ========================================================= SLA_PICA_BRANCHES = [ {"region": "DKI Jakarta", "branch": "Serpong"}, {"region": "DKI Jakarta", "branch": "Serang"}, {"region": "DKI Jakarta", "branch": "Bekasi"}, {"region": "DKI Jakarta", "branch": "Cilandak"}, {"region": "DKI Jakarta", "branch": "Pondok Indah"}, {"region": "DKI Jakarta", "branch": "Kelapa Gading 2"}, {"region": "DKI Jakarta", "branch": "Pluit"}, {"region": "DKI Jakarta", "branch": "Tendean"}, {"region": "Jabar Sulawesi", "branch": "Bandung"}, {"region": "Jabar Sulawesi", "branch": "Karawang"}, {"region": "Jabar Sulawesi", "branch": "Bogor"}, {"region": "Jabar Sulawesi", "branch": "Cirebon"}, {"region": "Jabar Sulawesi", "branch": "Manado"}, {"region": "Jabar Sulawesi", "branch": "Makassar"}, {"region": "Sumatera Jateng", "branch": "Jogja"}, {"region": "Sumatera Jateng", "branch": "Solo"}, {"region": "Sumatera Jateng", "branch": "Semarang"}, {"region": "Sumatera Jateng", "branch": "Lampung"}, {"region": "Sumatera Jateng", "branch": "Medan"}, {"region": "Sumatera Jateng", "branch": "Padang"}, {"region": "Sumatera Jateng", "branch": "Palembang"}, {"region": "Sumatera Jateng", "branch": "Pekanbaru"}, {"region": "Sumatera Jateng", "branch": "Batam"}, {"region": "Sumatera Jateng", "branch": "Jambi"}, {"region": "Jatim Bali Kalimantan", "branch": "Balikpapan"}, {"region": "Jatim Bali Kalimantan", "branch": "Pontianak"}, {"region": "Jatim Bali Kalimantan", "branch": "Banjarmasin"}, {"region": "Jatim Bali Kalimantan", "branch": "Denpasar"}, {"region": "Jatim Bali Kalimantan", "branch": "Kediri"}, {"region": "Jatim Bali Kalimantan", "branch": "Malang"}, {"region": "Jatim Bali Kalimantan", "branch": "Samarinda"}, {"region": "Jatim Bali Kalimantan", "branch": "Surabaya"}, {"region": "PREFERRED & PROKHUS", "branch": "Kelapa Gading"}, {"region": "PREFERRED & PROKHUS", "branch": "Joglo"}, {"region": "PREFERRED & PROKHUS", "branch": "Mangga Dua - Pontianak"}, {"region": "PREFERRED & PROKHUS", "branch": "BSD - Serang"}, {"region": "PREFERRED & PROKHUS", "branch": "Area Unit DIY"}, {"region": "PREFERRED & PROKHUS", "branch": "Cilandak - Puri"}, ] def parse_ytd_values(raw): """Parse pasted YTD column — one value per line, comma as decimal sep, skip #VALUE!""" result = [] for line in raw.strip().splitlines(): s = line.strip().replace(",", ".") if not s or "VALUE" in s.upper(): result.append(None) continue try: result.append(float(s)) except ValueError: result.append(None) return result @app.route("/pica", methods=["POST"]) def pica(): try: th_new = float(request.form.get("th_new", 90)) th_renew = float(request.form.get("th_renew", 90)) th_prod = float(request.form.get("th_prod", 95)) raw_new = request.form.get("val_new", "") raw_renew = request.form.get("val_renew", "") raw_prod = request.form.get("val_prod", "") bulan = request.form.get("bulan", "").strip() vals_new = parse_ytd_values(raw_new) vals_renew = parse_ytd_values(raw_renew) vals_prod = parse_ytd_values(raw_prod) flat = SLA_PICA_BRANCHES def pica_text(fail_new, fail_renew, fail_prod): """Return the correct PICA description text based on which ones failed.""" if fail_new and fail_renew and fail_prod: return "SLA New & Renew & Renewal Production: Polis tidak diterima sesuai SLA" elif fail_new and fail_renew: return "SLA New & Renew: Polis tidak diterima sesuai SLA" elif fail_renew and fail_prod: return "SLA Renew & Renewal Production: Polis tidak diterima sesuai SLA" elif fail_new and fail_prod: return "SLA New & Renewal Production: Polis tidak diterima sesuai SLA" elif fail_new: return "SLA New: Polis tidak diterima sesuai SLA" elif fail_renew: return "SLA Renew: Polis tidak diterima sesuai SLA" elif fail_prod: return "SLA Renewal Production: Produksi polis setelah jatuh tempo" return "" from collections import defaultdict, OrderedDict region_rows = OrderedDict() for item in flat: if item["region"] not in region_rows: region_rows[item["region"]] = [] for i, item in enumerate(flat): region = item["region"] branch = item["branch"] nv = vals_new[i] if i < len(vals_new) else None rv = vals_renew[i] if i < len(vals_renew) else None pv = vals_prod[i] if i < len(vals_prod) else None fail_new = nv is not None and nv < th_new fail_renew = rv is not None and rv < th_renew fail_prod = pv is not None and pv < th_prod if fail_new or fail_renew or fail_prod: desc = pica_text(fail_new, fail_renew, fail_prod) region_rows[region].append({ "bulan": bulan, "region": region, "branch": branch, "desc": desc, }) results = [] for region, rows in region_rows.items(): if not rows: continue results.append({ "region": region, "count": len(rows), "rows": rows, }) return jsonify({"success": True, "results": results, "bulan": bulan}) except Exception as e: tb = traceback.format_exc() return jsonify({"error": str(e), "traceback": tb}), 500 # ========================================================= # STS REGION SPLITTER # ========================================================= STS_REGIONS = [ "Jabar-Sulawesi", "Jakarta", "Sumatera-Jateng", "Jatim-Bali-Kalimantan", ] def apply_sts_formatting(ws): """Apply Aptos Narrow 11, All Borders on data, header fill colors.""" from openpyxl.styles import Font, PatternFill, Border, Side, Alignment font_data = Font(name="Aptos Narrow", size=11) font_header = Font(name="Aptos Narrow", size=11, bold=False) thin = Side(style="thin") all_border = Border(left=thin, right=thin, top=thin, bottom=thin) # Header fill colors abu = PatternFill("solid", fgColor="E8E8E8") # RGB 232,232,232 kuning = PatternFill("solid", fgColor="FFFF00") # RGB 255,255,0 no_fill= PatternFill(fill_type=None) # Col index -> fill for row 1 # A-J (1-10): Abu, K(11): Kuning, L(12): Abu, # M-P (13-16): Kuning, Q-Y (17-25): Abu, # Z-AA (26-27): Kuning, AB(28): Abu, # AC-AD (29-30): Kuning, AE-AI (31-35): Abu def header_fill(col): if 1 <= col <= 10: return abu if col == 11: return kuning if col == 12: return abu if 13 <= col <= 16: return kuning if 17 <= col <= 25: return abu if 26 <= col <= 27: return kuning if col == 28: return abu if 29 <= col <= 30: return kuning if 31 <= col <= 35: return abu return no_fill max_row = ws.max_row max_col = ws.max_column for row in ws.iter_rows(min_row=1, max_row=max_row): for cell in row: col = cell.column r = cell.row # Font for all cells cell.font = Font(name="Aptos Narrow", size=11) if r == 1: # Header: fill color + all borders cell.fill = header_fill(col) cell.border = all_border else: # Data rows: all borders only (no fill change) cell.border = all_border # Autofit columns for col_cells in ws.columns: length = max((len(str(c.value)) if c.value is not None else 0) for c in col_cells) ws.column_dimensions[col_cells[0].column_letter].width = min(length + 3, 50) @app.route("/split_sts", methods=["POST"]) def split_sts(): source_file = request.files.get("source_file") bulan = request.form.get("bulan", "").strip() tahun = request.form.get("tahun", "").strip() regions_raw = request.form.get("regions", "") if not source_file: return jsonify({"error": "Upload file STS dulu!"}), 400 if not bulan: return jsonify({"error": "Isi Bulan dulu!"}), 400 if not tahun: return jsonify({"error": "Isi Tahun dulu!"}), 400 selected_regions = STS_REGIONS if regions_raw == "ALL" else [r for r in regions_raw.split("||") if r] logs = [] try: content = source_file.read() excel_file = pd.ExcelFile(io.BytesIO(content)) logs.append(f"Sheet tersedia: {', '.join(excel_file.sheet_names)}") if "Sheet1" not in excel_file.sheet_names: return jsonify({"error": "Sheet 'Sheet1' tidak ditemukan di file!"}), 400 df = pd.read_excel(io.BytesIO(content), sheet_name="Sheet1") df.columns = [str(c).strip() for c in df.columns] logs.append(f"Sheet1: {len(df)} baris") # Region column — col P (index 15, 0-based) region_col = None if "Region" in df.columns: region_col = "Region" elif len(df.columns) > 15: region_col = df.columns[15] # col P fallback if not region_col: return jsonify({"error": "Kolom Region (P) tidak ditemukan!"}), 400 logs.append(f"Kolom region: '{region_col}'") logs.append(f"Unique regions: {df[region_col].dropna().unique().tolist()}") generated_files = [] for region in selected_regions: logs.append(f"\n— Region: {region} —") filtered = df[df[region_col].astype(str).str.strip() == region].copy() logs.append(f" {len(filtered)} baris") if len(filtered) == 0: logs.append(f" Tidak ada data, dilewati") continue output_filename = f"Raw Data STS {region} YTD {bulan}{tahun}.xlsx" output_path = os.path.join(OUTPUT_FOLDER, secure_filename(output_filename)) with pd.ExcelWriter(output_path, engine="openpyxl") as writer: filtered.to_excel(writer, sheet_name="Sheet1", index=False) # Apply formatting wb = load_workbook(output_path) ws = wb["Sheet1"] apply_sts_formatting(ws) wb.save(output_path) generated_files.append(output_filename) logs.append(f" ✓ Berhasil: {output_filename}") logs.append(f"\n✅ Selesai — {len(generated_files)} file dibuat") return jsonify({"success": True, "logs": logs, "files": generated_files}) except Exception as e: tb = traceback.format_exc() return jsonify({"error": str(e), "traceback": tb}), 500 @app.route("/download/") def download(filename): path = os.path.join(OUTPUT_FOLDER, secure_filename(filename)) if not os.path.exists(path): return "File tidak ditemukan", 404 return send_file(path, as_attachment=True) if __name__ == "__main__": app.run(debug=True, port=5000)