77 lines
2.5 KiB
Python
77 lines
2.5 KiB
Python
import sys
|
||
sys.path.append('Backend')
|
||
from app.config.database import engine
|
||
from sqlalchemy import text
|
||
import re
|
||
|
||
def parse_status_pekerjaan_new(status_sp):
|
||
if status_sp is None:
|
||
return None
|
||
|
||
sp = str(status_sp).strip().lower()
|
||
if sp in ['', 'nan', 'none', '0', '0.0', '-']:
|
||
return None
|
||
|
||
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'
|
||
|
||
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
|
||
|
||
with engine.connect() as conn:
|
||
# Get all records from tracer_responses
|
||
rows = conn.execute(text("SELECT alumni_id, status_saat_ini FROM tracer_responses;")).fetchall()
|
||
print(f"Total tracer responses: {len(rows)}")
|
||
|
||
updated_alumni = 0
|
||
updated_pekerjaan = 0
|
||
|
||
for alumni_id, raw_status in rows:
|
||
correct_status = parse_status_pekerjaan_new(raw_status)
|
||
if correct_status:
|
||
conn.execute(
|
||
text("UPDATE alumni SET status_kerja = :st WHERE id = :aid"),
|
||
{"st": correct_status, "aid": alumni_id}
|
||
)
|
||
conn.execute(
|
||
text("UPDATE pekerjaan SET status_pekerjaan = :st WHERE alumni_id = :aid"),
|
||
{"st": correct_status, "aid": alumni_id}
|
||
)
|
||
updated_alumni += 1
|
||
updated_pekerjaan += 1
|
||
|
||
conn.commit()
|
||
print(f"Successfully updated status for {updated_alumni} rows in alumni and pekerjaan!")
|
||
|
||
# Check new status counts
|
||
res_alumni = conn.execute(text("SELECT status_kerja, COUNT(*) FROM alumni GROUP BY status_kerja;")).fetchall()
|
||
print("\nUpdated alumni status_kerja counts:")
|
||
for r in res_alumni:
|
||
print(f" {r[0]}: {r[1]}")
|
||
|
||
res_pek = conn.execute(text("SELECT status_pekerjaan, COUNT(*) FROM pekerjaan GROUP BY status_pekerjaan;")).fetchall()
|
||
print("\nUpdated pekerjaan status_pekerjaan counts:")
|
||
for r in res_pek:
|
||
print(f" {r[0]}: {r[1]}")
|