from flask import Blueprint, render_template, redirect, url_for, flash, request, jsonify, send_file from flask_login import login_required, current_user from app import db from app.models import Penduduk from datetime import datetime import io import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter penduduk_bp = Blueprint('penduduk', __name__, url_prefix='/penduduk') TAHAP_OPTIONS = [ 'Tahap 1 2025', 'Tahap 2 2025', 'Tahap 3 2025', 'Tahap 1 2026', 'Tahap 2 2026', 'Tahap 3 2026', ] # ─── HEADER ALIAS ───────────────────────────────────────────────────────────── # Mapping nama header di berbagai format Excel ke nama field internal. # Key = uppercase-stripped dari header Excel # Value = field name internal HEADER_ALIAS = { # Nama 'NAMA' : 'nama', 'NAMA LENGKAP' : 'nama', # Jenis Kelamin 'JEN. KELAMIN' : 'jenis_kelamin', 'JENIS KELAMIN' : 'jenis_kelamin', 'JK' : 'jenis_kelamin', 'KELAMIN' : 'jenis_kelamin', # Kebangsaan 'KEBANGSAAN' : 'kebangsaan', # Tempat & Tanggal Lahir 'TEMPAT LAHIR' : 'tempat_lahir', 'TANGGAL LAHIR' : 'tanggal_lahir', 'TGL LAHIR' : 'tanggal_lahir', # Agama 'AGAMA' : 'agama', # Pendidikan 'PENDIDIKAN' : 'pendidikan', 'PENDIDIKAN TERAKHIR' : 'pendidikan', # Alamat 'RT' : 'rt', 'RW' : 'rw', 'KELURAHAN' : 'kelurahan', 'KECAMATAN' : 'kecamatan', 'KOTA' : 'kota', 'KOTA/KABUPATEN' : 'kota', 'KABUPATEN/KOTA' : 'kota', 'PROPINSI' : 'provinsi', # ← typo umum di data pemerintah 'PROVINSI' : 'provinsi', 'KODEPOS' : 'kodepos', 'KODE POS' : 'kodepos', # Status 'STATUS RUMAH' : 'status_rumah', 'STATUS PEKERJAAN' : 'status_pekerjaan', 'PEKERJAAN' : 'status_pekerjaan', 'PEKERJAAN/ BIDANG USAHA' : 'bidang_usaha', # header asli dari data pemerintah 'PEKERJAAN/BIDANG USAHA' : 'bidang_usaha', 'BIDANG USAHA' : 'bidang_usaha', # Disabilitas 'JENIS DISABILITAS' : 'jenis_disabilitas', 'DISABILITAS' : 'jenis_disabilitas', # PKD 'JENIS PKD' : 'jenis_pkd', 'PKD' : 'jenis_pkd', # Tahap & Tahun 'TAHAP' : 'tahap', 'TAHUN PENERIMA' : 'tahun_penerima', 'TAHUN' : 'tahun_penerima', } # ─── CLEANSING MAP ──────────────────────────────────────────────────────────── # Setiap map: key = nilai uppercase dari Excel → value = nilai standar DB. # Dibuat berdasarkan analisis data asli file pemerintah. CLEAN_JENIS_KELAMIN = { 'L' : 'L', 'LAKI-LAKI' : 'L', 'LAKI LAKI' : 'L', 'LAKILAKI' : 'L', 'P' : 'P', 'PEREMPUAN' : 'P', 'WANITA' : 'P', } CLEAN_AGAMA = { 'ISLAM' : 'Islam', 'MUSLIM' : 'Islam', 'KRISTEN' : 'Kristen', 'KRISTEN PROTESTAN': 'Kristen', 'PROTESTAN' : 'Kristen', 'KATOLIK' : 'Katholik', 'KATHOLIK' : 'Katholik', 'KATOLIK ROMA' : 'Katholik', 'HINDU' : 'Hindu', 'BUDHA' : 'Buddha', # ← typo umum 'BUDDHA' : 'Buddha', 'BUDHA/BUDDHA' : 'Buddha', 'KONGHUCU' : 'Konghucu', 'KONG HU CU' : 'Konghucu', 'KEPERCAYAAN' : 'Lainnya', 'LAINNYA' : 'Lainnya', } CLEAN_PENDIDIKAN = { # Tidak Sekolah 'TIDAK/BELUM SEKOLAH' : 'Tidak Sekolah', 'BELUM/TIDAK SEKOLAH' : 'Tidak Sekolah', 'TIDAK SEKOLAH' : 'Tidak Sekolah', 'BELUM SEKOLAH' : 'Tidak Sekolah', 'TIDAK BERSEKOLAH' : 'Tidak Sekolah', 'BELUM BERSEKOLAH' : 'Tidak Sekolah', 'TIDAK/BELUM BERSEKOLAH' : 'Tidak Sekolah', 'BELUM/TIDAK BERSEKOLAH' : 'Tidak Sekolah', # SD 'SD' : 'SD', 'SD/SEDERAJAT' : 'SD', 'TAMAT SD' : 'SD', 'TAMAT SD/SEDERAJAT' : 'SD', 'LULUS SD' : 'SD', 'BELUM TAMAT SD' : 'SD', # masih SD, belum tamat → tetap SD 'BELUM TAMAT SD/SEDERAJAT' : 'SD', 'SEKOLAH DASAR' : 'SD', # SMP 'SMP' : 'SMP', 'SMP/SEDERAJAT' : 'SMP', 'SLTP' : 'SMP', 'SLTP/SEDERAJAT' : 'SMP', 'TAMAT SMP' : 'SMP', 'TAMAT SLTP' : 'SMP', 'TAMAT SMP/SEDERAJAT' : 'SMP', 'TAMAT SLTP/SEDERAJAT' : 'SMP', 'SEKOLAH MENENGAH PERTAMA' : 'SMP', # SMA/SMK 'SMA' : 'SMA/SMK', 'SMK' : 'SMA/SMK', 'SMA/SMK' : 'SMA/SMK', 'SMA/SEDERAJAT' : 'SMA/SMK', 'SLTA' : 'SMA/SMK', 'SLTA/SEDERAJAT' : 'SMA/SMK', 'TAMAT SMA' : 'SMA/SMK', 'TAMAT SMK' : 'SMA/SMK', 'TAMAT SLTA' : 'SMA/SMK', 'TAMAT SMA/SEDERAJAT' : 'SMA/SMK', 'TAMAT SLTA/SEDERAJAT' : 'SMA/SMK', 'SEKOLAH MENENGAH ATAS' : 'SMA/SMK', # D1 'D1' : 'D1', 'DIPLOMA I' : 'D1', 'DIPLOMA 1' : 'D1', # D2 'D2' : 'D2', 'DIPLOMA II' : 'D2', 'DIPLOMA 2' : 'D2', # D3 'D3' : 'D3', 'DIPLOMA III' : 'D3', 'DIPLOMA 3' : 'D3', 'AKADEMI' : 'D3', 'AKADEMI/DIPLOMA III' : 'D3', 'AKADEMI/DIPLOMA III/S. MUDA' : 'D3', 'D.III' : 'D3', # S1 'S1' : 'S1', 'S-1' : 'S1', 'STRATA I' : 'S1', 'STRATA 1' : 'S1', 'DIPLOMA IV' : 'S1', # D4/S1 setara 'DIPLOMA IV/STRATA I' : 'S1', 'D4' : 'S1', 'D.IV' : 'S1', 'SARJANA' : 'S1', # S2 'S2' : 'S2', 'S-2' : 'S2', 'STRATA II' : 'S2', 'STRATA 2' : 'S2', 'MAGISTER' : 'S2', 'PASCASARJANA' : 'S2', # S3 'S3' : 'S3', 'S-3' : 'S3', 'STRATA III' : 'S3', 'STRATA 3' : 'S3', 'DOKTOR' : 'S3', 'DOKTORAL' : 'S3', } CLEAN_STATUS_RUMAH = { 'MILIK SENDIRI' : 'Milik Sendiri', 'MILIK PRIBADI' : 'Milik Sendiri', 'PRIBADI' : 'Milik Sendiri', 'MILIK' : 'Milik Sendiri', 'SEWA' : 'Sewa/Kontrak', 'KONTRAK' : 'Sewa/Kontrak', # ← nilai umum di data pemerintah 'SEWA/KONTRAK' : 'Sewa/Kontrak', 'SEWA / KONTRAK' : 'Sewa/Kontrak', 'MENUMPANG' : 'Menumpang', 'NUMPANG' : 'Menumpang', 'DINAS' : 'Dinas', 'MILIK NEGARA' : 'Dinas', # ← dari data pemerintah 'RUMAH DINAS' : 'Dinas', 'LAINNYA' : 'Lainnya', 'LAIN-LAIN' : 'Lainnya', } CLEAN_STATUS_PEKERJAAN = { # Bekerja — berbagai pekerjaan spesifik → dikategorikan 'Bekerja' 'BEKERJA' : 'Bekerja', 'KARYAWAN' : 'Bekerja', 'KARYAWAN SWASTA' : 'Bekerja', 'KARYAWAN NEGERI' : 'Bekerja', 'PNS' : 'Bekerja', 'PEGAWAI NEGERI' : 'Bekerja', 'TNI' : 'Bekerja', 'POLRI' : 'Bekerja', 'WIRASWASTA' : 'Bekerja', 'WIRAUSAHA' : 'Bekerja', 'PEDAGANG' : 'Bekerja', 'BURUH' : 'Bekerja', 'BURUH HARIAN' : 'Bekerja', 'BURUH HARIAN LEPAS' : 'Bekerja', 'BURUH TANI' : 'Bekerja', 'BURUH TANI/PERKEBUNAN' : 'Bekerja', 'PETANI' : 'Bekerja', 'NELAYAN' : 'Bekerja', 'SOPIR' : 'Bekerja', 'DRIVER' : 'Bekerja', 'GURU' : 'Bekerja', 'DOKTER' : 'Bekerja', 'PERAWAT' : 'Bekerja', 'BIDAN' : 'Bekerja', 'PEGAWAI' : 'Bekerja', 'INDUSTRI' : 'Bekerja', 'WARTAWAN' : 'Bekerja', 'PELAUT' : 'Bekerja', 'SENIMAN' : 'Bekerja', # Tidak Bekerja 'TIDAK BEKERJA' : 'Tidak Bekerja', 'BELUM BEKERJA' : 'Tidak Bekerja', 'BELUM/TIDAK BEKERJA' : 'Tidak Bekerja', 'TIDAK/BELUM BEKERJA' : 'Tidak Bekerja', 'PENGANGGURAN' : 'Tidak Bekerja', # Pelajar/Mahasiswa 'PELAJAR' : 'Pelajar/Mahasiswa', 'MAHASISWA' : 'Pelajar/Mahasiswa', 'PELAJAR/MAHASISWA' : 'Pelajar/Mahasiswa', 'SISWA' : 'Pelajar/Mahasiswa', # Ibu Rumah Tangga 'IRT' : 'Ibu Rumah Tangga', 'IBU RUMAH TANGGA' : 'Ibu Rumah Tangga', 'MENGURUS RUMAH TANGGA' : 'Ibu Rumah Tangga', # ← dari data pemerintah 'URUSAN RUMAH TANGGA' : 'Ibu Rumah Tangga', # Pensiunan 'PENSIUNAN' : 'Pensiunan', 'PENSIUN' : 'Pensiunan', } CLEAN_DISABILITAS = { # Tidak Ada 'TIDAK ADA' : 'Tidak Ada', 'NORMAL' : 'Tidak Ada', '-' : 'Tidak Ada', 'NULL' : 'Tidak Ada', '' : 'Tidak Ada', # Fisik 'FISIK' : 'Fisik', 'TUNA DAKSA' : 'Fisik', 'TUNADADAKSA' : 'Fisik', 'PARAPLEGIA' : 'Fisik', 'LUMPUH' : 'Fisik', 'LUMPUH LAYU' : 'Fisik', 'LUMPUH LAYU ATAU KAKU' : 'Fisik', 'AMPUTASI' : 'Fisik', 'CEREBRAL PALSY' : 'Fisik', 'DISABILITAS DAKSA' : 'Fisik', 'TUBUH MINI/CEBOL (DWARF)': 'Fisik', 'TUBUH MINI' : 'Fisik', 'CEBOL' : 'Fisik', # Sensorik 'SENSORIK' : 'Sensorik', 'TUNA NETRA' : 'Sensorik', 'TUNANETRA' : 'Sensorik', 'BUTA' : 'Sensorik', 'LOW VISION' : 'Sensorik', 'DISABILITAS NETRA' : 'Sensorik', 'TUNA RUNGU' : 'Sensorik', 'TUNARUNGU' : 'Sensorik', 'TUNA RUNGU/PENDENGARAN': 'Sensorik', 'DISABILITAS RUNGU/PENDENGARAN': 'Sensorik', 'TUNA WICARA' : 'Sensorik', 'TUNAWICARA' : 'Sensorik', 'DISABILITAS WICARA' : 'Sensorik', # Mental 'MENTAL' : 'Mental', 'SKIZOFRENIA' : 'Mental', 'GANGGUAN KEPRIBADIAN' : 'Mental', 'GANGGUAN KECEMASAN' : 'Mental', 'DEPRESI' : 'Mental', 'HIPERAKTIF' : 'Mental', 'AUTISME' : 'Mental', 'AUTIS' : 'Mental', # Intelektual 'INTELEKTUAL' : 'Intelektual', 'TUNA GRAHITA' : 'Intelektual', 'TUNAGRAHITA' : 'Intelektual', 'DISABILITAS GRAHITA/INTELEKTUAL' : 'Intelektual', 'DOWN SYNDROME' : 'Intelektual', 'DOWN SYNDROM' : 'Intelektual', # Ganda — kalau ada koma/tilde berarti lebih dari satu jenis 'GANDA' : 'Ganda', } @penduduk_bp.route('/') @login_required def index(): tahap_filter = request.args.get('tahap', '') kecamatan_filter = request.args.get('kecamatan', '') kelurahan_filter = request.args.get('kelurahan', '') page = request.args.get('page', 1, type=int) query = Penduduk.query if tahap_filter: query = query.filter(Penduduk.tahap == tahap_filter) if kecamatan_filter: query = query.filter(Penduduk.kecamatan.ilike(f'%{kecamatan_filter}%')) if kelurahan_filter: query = query.filter(Penduduk.kelurahan.ilike(f'%{kelurahan_filter}%')) penduduk = query.order_by(Penduduk.created_at.desc()).paginate( page=page, per_page=20, error_out=False ) # Ambil daftar tahap & kecamatan unik all_tahap = db.session.query(Penduduk.tahap).distinct().all() all_kecamatan = db.session.query(Penduduk.kecamatan).distinct().all() return render_template('penduduk/index.html', penduduk=penduduk, tahap_options=TAHAP_OPTIONS, all_tahap=[t[0] for t in all_tahap if t[0]], all_kecamatan=[k[0] for k in all_kecamatan if k[0]], tahap_filter=tahap_filter, kecamatan_filter=kecamatan_filter, kelurahan_filter=kelurahan_filter, ) @penduduk_bp.route('/tambah', methods=['GET', 'POST']) @login_required def tambah(): if request.method == 'POST': try: tgl_lahir = None if request.form.get('tanggal_lahir'): tgl_lahir = datetime.strptime(request.form['tanggal_lahir'], '%Y-%m-%d').date() p = Penduduk( nama=request.form['nama'], jenis_kelamin=request.form['jenis_kelamin'], kebangsaan=request.form.get('kebangsaan', 'WNI'), tempat_lahir=request.form.get('tempat_lahir'), tanggal_lahir=tgl_lahir, agama=request.form.get('agama'), pendidikan=request.form.get('pendidikan'), rt=request.form.get('rt'), rw=request.form.get('rw'), kelurahan=request.form.get('kelurahan'), kecamatan=request.form.get('kecamatan'), kota=request.form.get('kota'), provinsi=request.form.get('provinsi'), kodepos=request.form.get('kodepos'), status_rumah=request.form.get('status_rumah'), status_pekerjaan=request.form.get('status_pekerjaan'), bidang_usaha=request.form.get('bidang_usaha'), jenis_disabilitas=request.form.get('jenis_disabilitas', 'Tidak Ada'), jenis_pkd=request.form.get('jenis_pkd'), tahap=request.form['tahap'], tahun_penerima=request.form.get('tahun_penerima', type=int), created_by=current_user.id, ) db.session.add(p) db.session.commit() flash('Data penduduk berhasil ditambahkan.', 'success') return redirect(url_for('penduduk.index')) except Exception as e: db.session.rollback() flash(f'Gagal menyimpan data: {str(e)}', 'danger') return render_template('penduduk/form.html', action='tambah', data=None, tahap_options=TAHAP_OPTIONS, ) @penduduk_bp.route('/edit/', methods=['GET', 'POST']) @login_required def edit(id): p = Penduduk.query.get_or_404(id) if request.method == 'POST': try: tgl_lahir = None if request.form.get('tanggal_lahir'): tgl_lahir = datetime.strptime(request.form['tanggal_lahir'], '%Y-%m-%d').date() p.nama = request.form['nama'] p.jenis_kelamin = request.form['jenis_kelamin'] p.kebangsaan = request.form.get('kebangsaan', 'WNI') p.tempat_lahir = request.form.get('tempat_lahir') p.tanggal_lahir = tgl_lahir p.agama = request.form.get('agama') p.pendidikan = request.form.get('pendidikan') p.rt = request.form.get('rt') p.rw = request.form.get('rw') p.kelurahan = request.form.get('kelurahan') p.kecamatan = request.form.get('kecamatan') p.kota = request.form.get('kota') p.provinsi = request.form.get('provinsi') p.kodepos = request.form.get('kodepos') p.status_rumah = request.form.get('status_rumah') p.status_pekerjaan = request.form.get('status_pekerjaan') p.bidang_usaha = request.form.get('bidang_usaha') p.jenis_disabilitas = request.form.get('jenis_disabilitas', 'Tidak Ada') p.jenis_pkd = request.form.get('jenis_pkd') p.tahap = request.form['tahap'] p.tahun_penerima = request.form.get('tahun_penerima', type=int) db.session.commit() flash('Data penduduk berhasil diperbarui.', 'success') return redirect(url_for('penduduk.index')) except Exception as e: db.session.rollback() flash(f'Gagal memperbarui data: {str(e)}', 'danger') return render_template('penduduk/form.html', action='edit', data=p, tahap_options=TAHAP_OPTIONS, ) @penduduk_bp.route('/hapus/', methods=['POST']) @login_required def hapus(id): p = Penduduk.query.get_or_404(id) db.session.delete(p) db.session.commit() flash('Data penduduk berhasil dihapus.', 'success') return redirect(url_for('penduduk.index')) @penduduk_bp.route('/detail/') @login_required def detail(id): p = Penduduk.query.get_or_404(id) return render_template('penduduk/detail.html', data=p) @penduduk_bp.route('/api/tahap') @login_required def api_tahap(): """API untuk mendapatkan daftar tahap yang tersedia.""" tahap_list = db.session.query(Penduduk.tahap, db.func.count(Penduduk.id))\ .group_by(Penduduk.tahap).all() return jsonify([{'tahap': t[0], 'count': t[1]} for t in tahap_list]) # ─── KOLOM EXCEL TEMPLATE ───────────────────────────────────────────────────── EXCEL_COLUMNS = [ ('nama', 'Nama Lengkap', True), ('jenis_kelamin', 'Jenis Kelamin', True), ('kebangsaan', 'Kebangsaan', False), ('tempat_lahir', 'Tempat Lahir', False), ('tanggal_lahir', 'Tanggal Lahir', False), ('agama', 'Agama', False), ('pendidikan', 'Pendidikan', False), ('rt', 'RT', False), ('rw', 'RW', False), ('kelurahan', 'Kelurahan', False), ('kecamatan', 'Kecamatan', False), ('kota', 'Kota/Kabupaten', False), ('provinsi', 'Provinsi', False), ('kodepos', 'Kode Pos', False), ('status_rumah', 'Status Rumah', False), ('status_pekerjaan', 'Status Pekerjaan', False), ('bidang_usaha', 'Bidang Usaha', False), ('jenis_disabilitas', 'Jenis Disabilitas', False), ('jenis_pkd', 'Jenis PKD', False), ('tahap', 'Tahap', True), ('tahun_penerima', 'Tahun Penerima', False), ] # ─── HELPER CLEANSING ───────────────────────────────────────────────────────── def _clean_lookup(raw_val, clean_map, default=None): """ Lookup nilai dari clean_map dengan key uppercase-stripped. Kalau tidak ketemu, return default. """ if raw_val is None: return default key = str(raw_val).strip().upper() return clean_map.get(key, default) def _clean_disabilitas(raw_val): """ Disabilitas bisa berisi beberapa nilai dipisah koma atau tilde (~). Kalau lebih dari satu jenis → 'Ganda'. Kalau satu → lookup ke CLEAN_DISABILITAS. """ if raw_val is None or str(raw_val).strip() in ('', '-', 'NULL'): return 'Tidak Ada' raw = str(raw_val).strip() # Pisah berdasarkan pemisah umum: koma atau tilde separators = [',', '~'] parts = [raw] for sep in separators: if sep in raw: parts = [p.strip() for p in raw.split(sep) if p.strip()] break # Bersihkan tanda testing atau catatan (misal "~TESTING INPUT 2") parts = [p for p in parts if not p.upper().startswith('TESTING')] if len(parts) > 1: return 'Ganda' key = parts[0].upper() return CLEAN_DISABILITAS.get(key, 'Tidak Ada') def _resolve_header(raw_header): """ Normalisasi nama header Excel → field internal. Strip spasi, tanda *, uppercase, lalu lookup HEADER_ALIAS. """ cleaned = str(raw_header or '').replace('*', '').strip().upper() # Bersihkan spasi berlebih di tengah cleaned = ' '.join(cleaned.split()) return HEADER_ALIAS.get(cleaned) def _parse_tanggal(raw_val): """Coba berbagai format tanggal, return date object atau None.""" if not raw_val: return None raw = str(raw_val).strip() for fmt in ('%Y-%m-%d', '%d/%m/%Y', '%d-%m-%Y', '%Y/%m/%d', '%d %m %Y'): try: return datetime.strptime(raw, fmt).date() except ValueError: continue return None def _make_template_workbook(): """Buat workbook Excel template kosong dengan header + sheet panduan.""" wb = openpyxl.Workbook() ws = wb.active ws.title = 'Data Penduduk' header_fill = PatternFill('solid', fgColor='1E3A5F') required_fill = PatternFill('solid', fgColor='2563EB') header_font = Font(bold=True, color='FFFFFF', size=10) center = Alignment(horizontal='center', vertical='center', wrap_text=True) thin = Side(style='thin', color='CCCCCC') border = Border(left=thin, right=thin, top=thin, bottom=thin) for col_idx, (field, label, required) in enumerate(EXCEL_COLUMNS, start=1): cell = ws.cell(row=1, column=col_idx, value=label + (' *' if required else '')) cell.font = header_font cell.fill = required_fill if required else header_fill cell.alignment = center cell.border = border col_widths = [25, 15, 15, 20, 15, 12, 15, 6, 6, 20, 20, 20, 20, 10, 18, 20, 20, 18, 20, 15, 15] for i, w in enumerate(col_widths, start=1): ws.column_dimensions[get_column_letter(i)].width = w ws.row_dimensions[1].height = 30 ws.freeze_panes = 'A2' sample = [ 'Budi Santoso', 'L', 'WNI', 'Jakarta', '1985-06-15', 'Islam', 'SMA/SMK', '003', '005', 'Karang Anyar', 'Jatinegara', 'Jakarta Timur', 'DKI Jakarta', '13350', 'Sewa/Kontrak', 'Bekerja', 'Pedagang', 'Tidak Ada', 'BPNT', 'Tahap 1 2026', '2026', ] for col_idx, val in enumerate(sample, start=1): cell = ws.cell(row=2, column=col_idx, value=val) cell.alignment = Alignment(vertical='center') cell.fill = PatternFill('solid', fgColor='F0F9FF') cell.border = border # Sheet Panduan ws2 = wb.create_sheet('Panduan') ws2.column_dimensions['A'].width = 28 ws2.column_dimensions['B'].width = 60 ws2['A1'] = 'PANDUAN PENGISIAN DATA' ws2['A1'].font = Font(bold=True, size=12, color='1E3A5F') ws2.merge_cells('A1:B1') panduan = [ ('', ''), ('Kolom wajib (*)', 'Nama Lengkap, Jenis Kelamin, Tahap'), ('Jenis Kelamin', 'L (Laki-laki) atau P (Perempuan). Juga diterima: LAKI-LAKI, PEREMPUAN'), ('Tanggal Lahir', 'Format: YYYY-MM-DD contoh: 1990-05-20'), ('Agama', 'Islam / Kristen / Katholik / Hindu / Buddha / Konghucu / Lainnya'), ('Pendidikan', 'Tidak Sekolah / SD / SMP / SMA/SMK / D1 / D2 / D3 / S1 / S2 / S3'), ('', 'Juga diterima: TAMAT SD/SEDERAJAT, SLTA/SEDERAJAT, DIPLOMA IV/STRATA I, dll'), ('Status Rumah', 'Milik Sendiri / Sewa/Kontrak / Menumpang / Dinas / Lainnya'), ('', 'Juga diterima: MILIK PRIBADI, KONTRAK, MILIK NEGARA'), ('Status Pekerjaan', 'Bekerja / Tidak Bekerja / Pelajar/Mahasiswa / Ibu Rumah Tangga / Pensiunan'), ('', 'Juga diterima: KARYAWAN SWASTA, BURUH HARIAN LEPAS, WIRASWASTA, IRT, dll'), ('Jenis Disabilitas', 'Tidak Ada / Fisik / Mental / Intelektual / Sensorik / Ganda'), ('', 'Juga diterima: TUNA DAKSA, SKIZOFRENIA, TUNA RUNGU, DOWN SYNDROME, dll'), ('', 'Beberapa jenis sekaligus (dipisah koma/~) → otomatis jadi "Ganda"'), ('Tahap', 'Contoh: Tahap 1 2026'), ('Tahun Penerima', 'Angka tahun, contoh: 2026'), ('', ''), ('CATATAN', 'Baris header (baris 1) JANGAN dihapus atau diubah.'), ('', 'Sistem menerima header dari format data pemerintah (Propinsi, Jen. Kelamin, dll).'), ('', 'Nilai tidak dikenali → diisi NULL / dikosongkan, data tetap disimpan.'), ('', 'Duplikat (Nama + RT + RW sama) → dilewati otomatis.'), ] note_fill = PatternFill('solid', fgColor='FFF7ED') for r, (key, val) in enumerate(panduan, start=2): ka = ws2.cell(row=r, column=1, value=key) vb = ws2.cell(row=r, column=2, value=val) if key not in ('', 'CATATAN'): ka.font = Font(bold=True, size=10) if key == 'CATATAN': ka.font = Font(bold=True, color='C2410C') ka.fill = note_fill vb.fill = note_fill return wb @penduduk_bp.route('/template-excel') @login_required def template_excel(): """Download template Excel kosong.""" wb = _make_template_workbook() buf = io.BytesIO() wb.save(buf) buf.seek(0) return send_file( buf, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', as_attachment=True, download_name='template_import_penduduk.xlsx', ) @penduduk_bp.route('/import-excel', methods=['GET', 'POST']) @login_required def import_excel(): """Halaman & proses import data penduduk dari Excel dengan data cleansing otomatis.""" if request.method == 'POST': file = request.files.get('file_excel') if not file or file.filename == '': flash('Pilih file Excel terlebih dahulu.', 'danger') return redirect(url_for('penduduk.import_excel')) if not file.filename.endswith(('.xlsx', '.xls')): flash('Format file harus .xlsx atau .xls.', 'danger') return redirect(url_for('penduduk.import_excel')) # Ambil parameter tambahan dari form tahap_override = request.form.get('tahap_override', '').strip() skip_duplicate = request.form.get('skip_duplicate', 'on') == 'on' try: wb = openpyxl.load_workbook(file, data_only=True) ws = wb['Data Penduduk'] if 'Data Penduduk' in wb.sheetnames else wb.active # ── 1. Resolve header ────────────────────────────────────────── raw_headers = [ws.cell(row=1, column=c).value for c in range(1, ws.max_column + 1)] col_map = {} # col_index (0-based) → field name unrecognized_headers = [] for i, h in enumerate(raw_headers): field = _resolve_header(h) if field: col_map[i] = field elif h: unrecognized_headers.append(str(h)) if not col_map: flash('Tidak ada kolom yang dikenali. Pastikan file menggunakan header yang benar.', 'danger') return redirect(url_for('penduduk.import_excel')) # ── 2. Proses per baris ──────────────────────────────────────── inserted = 0 skipped = 0 cleaned = 0 # baris yang perlu cleansing nilai errors = [] # log peringatan per baris for row_num, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2): # Skip baris kosong total if all(v is None or str(v).strip() == '' for v in row): continue # Petakan kolom → dict raw = {} for col_i, field in col_map.items(): val = row[col_i] if col_i < len(row) else None raw[field] = val # simpan raw, bersihkan per field di bawah # ── Kolom wajib ─────────────────────────────────────────── nama = str(raw.get('nama') or '').strip() if not nama: errors.append(f'Baris {row_num}: Kolom Nama kosong → dilewati.') skipped += 1 continue # Tahap: pakai override dari form jika diisi, fallback ke kolom tahap = tahap_override or str(raw.get('tahap') or '').strip() if not tahap: errors.append(f'Baris {row_num}: Kolom Tahap kosong ({nama}) → dilewati.') skipped += 1 continue # ── Jenis Kelamin ───────────────────────────────────────── jk_raw = str(raw.get('jenis_kelamin') or '').strip() jk = _clean_lookup(jk_raw, CLEAN_JENIS_KELAMIN) if jk is None: errors.append(f'Baris {row_num}: Jenis kelamin "{jk_raw}" tidak dikenali ({nama}) → dilewati.') skipped += 1 continue if jk_raw.upper() not in ('L', 'P'): cleaned += 1 # butuh konversi # ── Cek duplikat ────────────────────────────────────────── rt = str(raw.get('rt') or '').strip() or None rw = str(raw.get('rw') or '').strip() or None if skip_duplicate: dup = Penduduk.query.filter_by(nama=nama, rt=rt, rw=rw).first() if dup: errors.append(f'Baris {row_num}: Duplikat "{nama}" RT {rt}/RW {rw} → dilewati.') skipped += 1 continue # ── Cleansing nilai kategorikal ─────────────────────────── agama = _clean_lookup(raw.get('agama'), CLEAN_AGAMA) pendidikan = _clean_lookup(raw.get('pendidikan'), CLEAN_PENDIDIKAN) status_rumah = _clean_lookup(raw.get('status_rumah'), CLEAN_STATUS_RUMAH) status_kerja = _clean_lookup(raw.get('status_pekerjaan'), CLEAN_STATUS_PEKERJAAN) disabilitas = _clean_disabilitas(raw.get('jenis_disabilitas')) # Log kolom yang tidak bisa di-resolve (bukan error, data tetap masuk) row_warnings = [] if raw.get('agama') and agama is None: row_warnings.append(f'agama="{raw["agama"]}"') if raw.get('pendidikan') and pendidikan is None: row_warnings.append(f'pendidikan="{raw["pendidikan"]}"') if raw.get('status_rumah') and status_rumah is None: row_warnings.append(f'status_rumah="{raw["status_rumah"]}"') if raw.get('status_pekerjaan') and status_kerja is None: row_warnings.append(f'status_pekerjaan="{raw["status_pekerjaan"]}"') if row_warnings: errors.append(f'Baris {row_num} ({nama}): Nilai tidak dikenali → dikosongkan: {", ".join(row_warnings)}') cleaned += 1 # ── Bidang usaha ────────────────────────────────────────── # Jika status_kerja = Bekerja, ambil bidang usaha dari kolom # Jika ada kolom pekerjaan/bidang usaha langsung, pakai itu bidang_usaha = str(raw.get('bidang_usaha') or '').strip() or None # ── Tanggal Lahir ───────────────────────────────────────── tgl_lahir = _parse_tanggal(raw.get('tanggal_lahir')) # ── Kebangsaan ──────────────────────────────────────────── kebangsaan_raw = str(raw.get('kebangsaan') or '').strip() # Normalisasi: INDONESIA → WNI (sesuai enum model) kebangsaan = 'WNI' if kebangsaan_raw.upper() in ('INDONESIA', 'WNI', '') else kebangsaan_raw or 'WNI' # ── Tahun Penerima ──────────────────────────────────────── try: tahun = int(str(raw.get('tahun_penerima') or '').strip()) or None except (ValueError, TypeError): tahun = None # ── Simpan ke DB ────────────────────────────────────────── p = Penduduk( nama = nama, jenis_kelamin = jk, kebangsaan = kebangsaan, tempat_lahir = str(raw.get('tempat_lahir') or '').strip() or None, tanggal_lahir = tgl_lahir, agama = agama, pendidikan = pendidikan, rt = rt, rw = rw, kelurahan = str(raw.get('kelurahan') or '').strip() or None, kecamatan = str(raw.get('kecamatan') or '').strip() or None, kota = str(raw.get('kota') or '').strip() or None, provinsi = str(raw.get('provinsi') or '').strip() or None, kodepos = str(raw.get('kodepos') or '').strip() or None, status_rumah = status_rumah, status_pekerjaan = status_kerja, bidang_usaha = bidang_usaha, jenis_disabilitas= disabilitas, jenis_pkd = str(raw.get('jenis_pkd') or '').strip() or None, tahap = tahap, tahun_penerima = tahun, created_by = current_user.id, ) db.session.add(p) inserted += 1 # Commit per 200 baris agar tidak overload memori if inserted % 200 == 0: db.session.flush() db.session.commit() return render_template('penduduk/import_result.html', inserted=inserted, skipped=skipped, cleaned=cleaned, errors=errors, unrecognized_headers=unrecognized_headers, tahap=tahap_override or '(dari file)', ) except Exception as e: db.session.rollback() flash(f'Gagal memproses file: {str(e)}', 'danger') return redirect(url_for('penduduk.import_excel')) # GET — tampilkan form return render_template('penduduk/import_excel.html', tahap_options=TAHAP_OPTIONS) """Buat workbook Excel template kosong dengan header + sheet panduan.""" wb = openpyxl.Workbook() # ── Sheet DATA ── ws = wb.active ws.title = 'Data Penduduk' # Style header header_fill = PatternFill('solid', fgColor='1E3A5F') required_fill = PatternFill('solid', fgColor='2563EB') header_font = Font(bold=True, color='FFFFFF', size=10) center = Alignment(horizontal='center', vertical='center', wrap_text=True) thin = Side(style='thin', color='CCCCCC') border = Border(left=thin, right=thin, top=thin, bottom=thin) for col_idx, (field, label, required) in enumerate(EXCEL_COLUMNS, start=1): cell = ws.cell(row=1, column=col_idx, value=label + (' *' if required else '')) cell.font = header_font cell.fill = required_fill if required else header_fill cell.alignment = center cell.border = border # Lebar kolom col_widths = [25, 15, 15, 20, 15, 12, 15, 6, 6, 20, 20, 20, 20, 10, 18, 20, 20, 18, 20, 15, 15] for i, w in enumerate(col_widths, start=1): ws.column_dimensions[get_column_letter(i)].width = w ws.row_dimensions[1].height = 30 # Freeze header ws.freeze_panes = 'A2' # Contoh baris data sample = [ 'Budi Santoso', 'L', 'WNI', 'Jakarta', '1985-06-15', 'Islam', 'SMA/SMK', '003', '005', 'Karang Anyar', 'Jatinegara', 'Jakarta Timur', 'DKI Jakarta', '13350', 'Sewa/Kontrak', 'Bekerja', 'Pedagang', 'Tidak Ada', 'BPNT', 'Tahap 1 2026', '2026', ] for col_idx, val in enumerate(sample, start=1): cell = ws.cell(row=2, column=col_idx, value=val) cell.alignment = Alignment(vertical='center') cell.fill = PatternFill('solid', fgColor='F0F9FF') cell.border = border # ── Sheet PANDUAN ── ws2 = wb.create_sheet('Panduan') ws2.column_dimensions['A'].width = 25 ws2.column_dimensions['B'].width = 55 panduan_title_font = Font(bold=True, size=12, color='1E3A5F') sub_font = Font(bold=True, size=10) note_fill = PatternFill('solid', fgColor='FFF7ED') ws2['A1'] = 'PANDUAN PENGISIAN DATA' ws2['A1'].font = panduan_title_font ws2.merge_cells('A1:B1') panduan = [ ('', ''), ('Kolom wajib (*)', 'Nama Lengkap, Jenis Kelamin, Tahap'), ('Jenis Kelamin', 'L (Laki-laki) atau P (Perempuan)'), ('Tanggal Lahir', 'Format: YYYY-MM-DD contoh: 1990-05-20'), ('Agama', 'Islam / Kristen / Katholik / Hindu / Buddha / Konghucu / Lainnya'), ('Pendidikan', 'Tidak Sekolah / SD / SMP / SMA/SMK / D1 / D2 / D3 / S1 / S2 / S3'), ('Status Rumah', 'Milik Sendiri / Sewa/Kontrak / Menumpang / Dinas / Lainnya'), ('Status Pekerjaan', 'Bekerja / Tidak Bekerja / Pelajar/Mahasiswa / Ibu Rumah Tangga / Pensiunan'), ('Jenis Disabilitas', 'Tidak Ada / Fisik / Mental / Intelektual / Sensorik / Ganda'), ('Tahap', 'Contoh: Tahap 1 2026 / Tahap 2 2026 / dst.'), ('Tahun Penerima', 'Angka tahun, contoh: 2026'), ('', ''), ('CATATAN', 'Baris pertama (header) JANGAN dihapus atau diubah.'), ('', 'Baris contoh (baris ke-2) bisa dihapus sebelum import.'), ('', 'Nilai yang tidak sesuai pilihan akan dilewati (skipped).'), ('', 'Duplikat data (Nama + RT + RW sama) akan dilewati.'), ] for r, (key, val) in enumerate(panduan, start=2): ka = ws2.cell(row=r, column=1, value=key) vb = ws2.cell(row=r, column=2, value=val) if key not in ('', 'CATATAN'): ka.font = sub_font if key == 'CATATAN': ka.font = Font(bold=True, color='C2410C') ka.fill = note_fill vb.fill = note_fill return wb