""" Service untuk import data tracer study. Logika utama ada di sini. """ import io import json import pandas as pd from typing import Dict, Any, List, Optional from sqlalchemy.orm import Session from app.parsers.column_finder import find_column, get_value, check_available_columns from app.parsers.converters import parse_2024_value, parse_date, konversi_jawaban, convert_value_for_field from app.parsers.mappings import ( YEAR_CONFIG, IDENTITAS_MAPPING, PEKERJAAN_MAPPING, STUDI_LANJUT_MAPPING, KESESUAIAN_MAPPING, KOMPETENSI_LULUS_MAPPING, KOMPETENSI_SAAT_INI_MAPPING, METODE_PEMBELAJARAN_MAPPING, WAKTU_CARI_KERJA_MAPPING, CARA_CARI_KERJA_MAPPING, LAMARAN_KEAKTIFAN_MAPPING, KESESUAIAN_PEKERJAAN_MAPPING, NUMERIC_FIELDS, get_field_code, ) def load_full_mapping(): """Load full column mapping dari file JSON""" try: mapping_path = "C:/Users/syifa/TA-PROJEK/Model/preprocessing/full_column_mapping.json" with open(mapping_path, 'r', encoding='utf-8') as f: return json.load(f) except Exception as e: print(f"Warning: Could not load full_column_mapping.json: {e}") return {} def import_data( tahun: str, file_content: bytes, filename: str, db: Session ) -> Dict[str, Any]: """ Import data dari CSV/Excel. Args: tahun: Tahun data (2019-2025) file_content: Bytes content dari file filename: Nama file untuk tentukan format db: SQLAlchemy session Returns: Dict dengan status dan stats """ # Validasi file filename_lower = filename.lower() if not filename_lower.endswith(('.csv', '.xlsx', '.xls')): return { "status": "error", "message": "File harus format CSV atau Excel (.csv, .xlsx, .xls)" } # Baca file if filename_lower.endswith('.csv'): df = pd.read_csv(io.BytesIO(file_content), sep=';', encoding='utf-8-sig') else: df = pd.read_excel(io.BytesIO(file_content)) # Bersihkan baris yang seluruhnya kosong (misal trailing empty rows di CSV) df = df.dropna(how='all') if df.empty: return { "status": "error", "message": "File kosong" } # Get source columns source_cols = list(df.columns) # Statistics stats = { "tahun": tahun, "total_rows": len(df), "imported": 0, "skipped": 0, "errors": [] } # Get year-specific configuration year_config = YEAR_CONFIG.get(tahun, {}) is_2024_format = bool(year_config.get('is_text_format', False)) if is_2024_format: print(f"[Import {tahun}] DETECTED: Format 2024 - menggunakan parser khusus") # Import models from app.models.alumni import Alumni from app.models.models import Pekerjaan, Kompetensi, TracerResponse # Process each row for idx, row in df.iterrows(): nim: str = "" try: # ======================================== # AMBIL DATA IDENTITAS # ======================================== nim = str(get_value(row, source_cols, IDENTITAS_MAPPING['nim'], '')) nama = get_value(row, source_cols, IDENTITAS_MAPPING['nama'], '') # Fallback khusus data historis tanpa NIM if (not nim or str(nim).strip() == '' or str(nim).strip().lower() == 'nan') and nama and 'KHOSNOL' in str(nama).upper(): nim = 'E41171725' if not nim or str(nim).strip() == '' or str(nim).strip().lower() == 'nan': stats["skipped"] += 1 continue # ======================================== # CREATE/UPDATE ALUMNI # ======================================== existing_alumni = db.query(Alumni).filter(Alumni.nim == nim).first() email = get_value(row, source_cols, IDENTITAS_MAPPING['email'], '') raw_prodi = str(get_value(row, source_cols, IDENTITAS_MAPPING['kode_prodi'], '')) # Extract clean prodi code (e.g., "Teknik Informatika (55301)" -> "55301", "57401.0" -> "57401") import re match_prodi = re.search(r'\((\d+)\)', raw_prodi) if match_prodi: kode_prodi = match_prodi.group(1) else: cleaned_prodi = raw_prodi.strip() if cleaned_prodi.endswith('.0'): cleaned_prodi = cleaned_prodi[:-2] kode_prodi = cleaned_prodi # Parse tahun lulus from row or parameter row_tahun = get_value(row, source_cols, IDENTITAS_MAPPING['tahun_lulus'], None) if row_tahun is not None and str(row_tahun).strip() not in ['', 'nan', 'None']: try: tahun_lulus_val = int(float(str(row_tahun).strip())) except (ValueError, TypeError): tahun_lulus_val = int(tahun) if tahun.isdigit() else 2024 else: tahun_lulus_val = int(tahun) if tahun.isdigit() else 2024 # Get status pekerjaan status_sp = str(get_value(row, source_cols, PEKERJAAN_MAPPING['status_saat_ini'], '')) status_kerja = _parse_status_pekerjaan(status_sp) # Get data pekerjaan untuk alumni nama_perusahaan = get_value(row, source_cols, PEKERJAAN_MAPPING['nama_perusahaan'], '') jabatan_raw = get_value(row, source_cols, PEKERJAAN_MAPPING['jabatan'], '') if is_2024_format and jabatan_raw: _, jab_teks = parse_2024_value(jabatan_raw) jabatan = jab_teks or str(jabatan_raw) elif jabatan_raw: jab_conv = konversi_jawaban('F5C', jabatan_raw) jabatan = str(jab_conv) if jab_conv is not None else str(jabatan_raw) else: jabatan = '' if existing_alumni: existing_alumni.nama = nama existing_alumni.email = email existing_alumni.tahun_lulus = tahun_lulus_val existing_alumni.kode_prodi = kode_prodi existing_alumni.status_kerja = status_kerja existing_alumni.pekerjaan = nama_perusahaan existing_alumni.instansi = jabatan db.merge(existing_alumni) else: alumni = Alumni( nim=nim, nama=nama, email=email, tahun_lulus=tahun_lulus_val, kode_prodi=kode_prodi, status_kerja=status_kerja, pekerjaan=nama_perusahaan, instansi=jabatan ) db.add(alumni) db.flush() target_alumni = existing_alumni or db.query(Alumni).filter(Alumni.nim == nim).first() if not target_alumni: continue alumni_id = target_alumni.id # ======================================== # CREATE/UPDATE PEKERJAAN # ======================================== _import_pekerjaan(db, row, source_cols, alumni_id, tahun, is_2024_format) # ======================================== # CREATE/UPDATE KOMPETENSI # ======================================== _import_kompetensi(db, row, source_cols, alumni_id, tahun, is_2024_format) # ======================================== # CREATE/UPDATE TRACER RESPONSE # ======================================== _import_tracer_response(db, row, source_cols, alumni_id, tahun, is_2024_format, nim=str(nim) if nim else None) db.flush() stats["imported"] += 1 except Exception as e: db.rollback() row_idx = int(idx) + 1 if isinstance(idx, (int, float)) else str(idx) stats["errors"].append({ "row": row_idx, "nim": nim if nim else "-", "error": str(e) }) stats["skipped"] += 1 db.commit() return { "status": "success", "message": f"Berhasil import {stats['imported']} data dari tahun {tahun}", "stats": stats } def _parse_status_pekerjaan(status_sp: str) -> Optional[str]: """ Parse status pekerjaan dari nilai F8 (Instrumen Tracer Study). Sesuai dokumen Pertanyaan-dan-jawaban.md: 1 - Bekerja (full time/part time) 2 - Belum memungkinkan bekerja 3 - Wiraswasta 4 - Melanjutkan Pendidikan 5 - Tidak kerja tetapi sedang mencari kerja """ if status_sp is None: return None sp = status_sp.strip().lower() if sp in ['', 'nan', 'none', '0', '0.0', '-']: return None # 1. Cek kode numerik (misal: "1", "1 - ...", "... (1)", "2- ...") import re code_match = re.search(r'(?:^|\s|\()([1-5])(?:\s*[-–)]|$)', sp) if code_match: code = code_match.group(1) if code == '1': return 'bekerja' elif code == '2': return 'belum_bekerja' elif code == '3': return 'wiraswasta' elif code == '4': return 'melanjutkan' elif code == '5': return 'mencari_kerja' # 2. Fallback pencocokan teks (urutan prioritas penting: cek opsi negatif terlebih dahulu) if 'tidak kerja' in sp or 'mencari kerja' in sp or 'sedang mencari' in sp: return 'mencari_kerja' elif 'belum' in sp: return 'belum_bekerja' elif 'wiraswasta' in sp: return 'wiraswasta' elif 'melanjutkan' in sp or 'pendidikan' in sp or 'studi' in sp: return 'melanjutkan' elif 'bekerja' in sp: return 'bekerja' return None def _import_pekerjaan(db: Session, row, source_cols: list, alumni_id: int, tahun: str, is_2024_format: bool): """Import data pekerjaan""" from app.models.models import Pekerjaan existing = db.query(Pekerjaan).filter(Pekerjaan.alumni_id == alumni_id).first() values = {} status_sp = str(get_value(row, source_cols, PEKERJAAN_MAPPING['status_saat_ini'], '')) values['status_pekerjaan'] = _parse_status_pekerjaan(status_sp) values['nama_perusahaan'] = get_value(row, source_cols, PEKERJAAN_MAPPING['nama_perusahaan'], '') jab_raw = get_value(row, source_cols, PEKERJAAN_MAPPING['jabatan'], '') if is_2024_format and jab_raw: _, jab_teks = parse_2024_value(jab_raw) values['jabatan'] = jab_teks or str(jab_raw) elif jab_raw: jab_conv = konversi_jawaban('F5C', jab_raw) values['jabatan'] = str(jab_conv) if jab_conv is not None else str(jab_raw) else: values['jabatan'] = '' jp_raw = get_value(row, source_cols, PEKERJAAN_MAPPING['jenis_perusahaan_full'], '') if is_2024_format and jp_raw: _, jp_teks = parse_2024_value(jp_raw) values['jenis_perusahaan'] = jp_teks or str(jp_raw) elif jp_raw: jp_conv = konversi_jawaban('F1101', jp_raw) values['jenis_perusahaan'] = str(jp_conv) if jp_conv is not None else str(jp_raw) else: values['jenis_perusahaan'] = '' ting_raw = get_value(row, source_cols, PEKERJAAN_MAPPING['tingkatan_tempat_kerja'], '') if is_2024_format and ting_raw: _, ting_teks = parse_2024_value(ting_raw) values['tingkatan_tempat_kerja'] = ting_teks or str(ting_raw) elif ting_raw: ting_conv = konversi_jawaban('F5D', ting_raw) values['tingkatan_tempat_kerja'] = str(ting_conv) if ting_conv is not None else str(ting_raw) else: values['tingkatan_tempat_kerja'] = '' prov_raw = get_value(row, source_cols, PEKERJAAN_MAPPING['provinsi_kerja'], '') if prov_raw is not None and str(prov_raw).strip() not in ['', 'nan', 'None']: if is_2024_format: _, prov_teks = parse_2024_value(prov_raw) values['provinsi'] = prov_teks or str(prov_raw) else: prov_conv = konversi_jawaban('F5A1', prov_raw) values['provinsi'] = str(prov_conv) if prov_conv is not None else str(prov_raw) kab_raw = get_value(row, source_cols, PEKERJAAN_MAPPING['kab_kota_kerja'], '') if kab_raw is not None and str(kab_raw).strip() not in ['', 'nan', 'None']: if is_2024_format: _, kab_teks = parse_2024_value(kab_raw) values['kabupaten_kota'] = kab_teks or str(kab_raw) else: kab_conv = konversi_jawaban('F5A2', kab_raw) values['kabupaten_kota'] = str(kab_conv) if kab_conv is not None else str(kab_raw) # Gabungkan 4 kolom teks pekerjaan (Status + Jenis + Perusahaan + Jabatan) sp_val = values.get('status_pekerjaan', '') or '' jp_val = values.get('jenis_perusahaan', '') or '' np_val = values.get('nama_perusahaan', '') or '' jab_val = values.get('jabatan', '') or '' teks_pekerjaan = f"{sp_val} {jp_val} {np_val} {jab_val}".strip() values['teks_pekerjaan'] = teks_pekerjaan if existing: for key, val in values.items(): if hasattr(existing, key) and val is not None: setattr(existing, key, val) db.merge(existing) else: pekerjaan = Pekerjaan(alumni_id=alumni_id) for key, val in values.items(): if hasattr(pekerjaan, key) and val is not None: setattr(pekerjaan, key, val) db.add(pekerjaan) def _import_kompetensi(db: Session, row, source_cols: list, alumni_id: int, tahun: str, is_2024_format: bool): """Import data kompetensi""" from app.models.models import Kompetensi existing = db.query(Kompetensi).filter(Kompetensi.alumni_id == alumni_id).first() values = {} for field, cols in KOMPETENSI_LULUS_MAPPING.items(): db_key = field.replace('_full', '') val = get_value(row, source_cols, cols) if val is None or pd.isna(val) or str(val).strip() in ['', 'nan', 'None', '-', '_']: continue if is_2024_format: kode, teks = parse_2024_value(val) if teks: values[db_key] = teks elif kode is not None: field_code = get_field_code(field, KOMPETENSI_LULUS_MAPPING) values[db_key] = konversi_jawaban(field_code, kode) else: values[db_key] = str(val).strip() else: field_code = get_field_code(field, KOMPETENSI_LULUS_MAPPING) converted = konversi_jawaban(field_code, val) if converted is not None: values[db_key] = str(converted) elif str(val).strip() in ['0', '0.0']: values[db_key] = None else: values[db_key] = str(val).strip() if existing: for key, val in values.items(): if val is not None and hasattr(existing, key): setattr(existing, key, val) db.merge(existing) else: kompetensi = Kompetensi(alumni_id=alumni_id) for key, val in values.items(): if val is not None and hasattr(kompetensi, key): setattr(kompetensi, key, val) db.add(kompetensi) def _import_tracer_response(db: Session, row, source_cols: list, alumni_id: int, tahun: str, is_2024_format: bool, nim: Optional[str] = None): """Import data tracer response""" from app.models.models import TracerResponse existing = db.query(TracerResponse).filter(TracerResponse.alumni_id == alumni_id).first() year_config = YEAR_CONFIG.get(tahun, {}) skip_jumlah = year_config.get('skip_jumlah_perusahaan', False) # Combine all mappings for tracer response all_mapping = {} all_mapping.update(IDENTITAS_MAPPING) all_mapping.update(PEKERJAAN_MAPPING) all_mapping.update(STUDI_LANJUT_MAPPING) all_mapping.update(KESESUAIAN_MAPPING) all_mapping.update(KOMPETENSI_LULUS_MAPPING) all_mapping.update(KOMPETENSI_SAAT_INI_MAPPING) all_mapping.update(METODE_PEMBELAJARAN_MAPPING) all_mapping.update(WAKTU_CARI_KERJA_MAPPING) all_mapping.update(CARA_CARI_KERJA_MAPPING) all_mapping.update(LAMARAN_KEAKTIFAN_MAPPING) all_mapping.update(KESESUAIAN_PEKERJAAN_MAPPING) tracer_data = {} for field, cols in all_mapping.items(): if field in ['nim', 'nama', 'tahun_lulus', 'kode_prodi']: continue if skip_jumlah and field == 'jumlah_perusahaan': continue val = get_value(row, source_cols, cols) if val is None or pd.isna(val): continue # Skip invalid values if isinstance(val, str) and val.strip() in ['', 'nan', 'None', '-', '_']: continue field_code = get_field_code(field, all_mapping) fc_upper = field_code.upper() if field_code else '' if is_2024_format: kode, teks = parse_2024_value(val) # Integer columns if field in ['bulan_dapat_kerja', 'bulan_dapat_kerja_setelah', 'bulan_cari_sebelum_lulus', 'bulan_cari_setelah_wisuda']: if kode is not None: tracer_data[field] = kode elif isinstance(val, (int, float)): tracer_data[field] = int(val) continue # Non-integer columns: prefer clean text response if teks and len(teks) > 0: tracer_data[field] = teks elif kode is not None: converted = konversi_jawaban(field_code, kode) tracer_data[field] = str(converted) if converted is not None else str(kode) else: tracer_data[field] = str(val).strip() else: # Format lama (2019-2023) converted = konversi_jawaban(field_code, val) if field in ['bulan_dapat_kerja', 'bulan_dapat_kerja_setelah', 'bulan_cari_sebelum_lulus', 'bulan_cari_setelah_wisuda']: try: tracer_data[field] = int(float(str(val).strip())) except (ValueError, TypeError): pass elif converted is not None: tracer_data[field] = str(converted) elif str(val).strip() in ['0', '0.0'] and fc_upper in ['F1761', 'F1762', 'F1763', 'F1764', 'F1765', 'F1766', 'F1767', 'F1768', 'F1769', 'F1770', 'F1771', 'F1772', 'F1773', 'F1774', 'F21', 'F22', 'F23', 'F24', 'F25', 'F26', 'F27']: # 0 for competency/learning method in old format means not answered / None tracer_data[field] = None else: tracer_data[field] = str(val).strip() # Handle date field tanggal_masuk = get_value(row, source_cols, STUDI_LANJUT_MAPPING['tanggal_masuk_studi']) if tanggal_masuk is not None and not pd.isna(tanggal_masuk): parsed_date = parse_date(tanggal_masuk) tracer_data['tanggal_masuk_studi'] = parsed_date else: tracer_data['tanggal_masuk_studi'] = None if existing: if nim and hasattr(existing, 'nim'): existing.nim = nim for key, val in tracer_data.items(): if hasattr(existing, key): setattr(existing, key, val) db.merge(existing) else: tracer = TracerResponse(alumni_id=alumni_id, nim=nim) for key, val in tracer_data.items(): if hasattr(tracer, key): setattr(tracer, key, val) db.add(tracer)