512 lines
19 KiB
Python
512 lines
19 KiB
Python
"""
|
||
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)
|