""" Database & Business Logic Layer untuk Aplikasi Absen Karyawan Online & Integrasi HRIS Pengupahan. Mengelola: 1. Master Lokasi Kerja (Office & Proyek, Radius Terkunci vs Bebas Radius / Free GPS) 2. Master Karyawan (Terintegrasi dengan data karyawan PT. BSS, PT. SBM, PT. BST, PT. SSJ, MSC) 3. Pencatatan Absensi Harian (Masuk & Keluar dengan Koordinat GPS, Deteksi Terlambat, Hitung Jam Kerja) 4. Rumus Akumulasi Keterlambatan HRD: 3 Hari Terlambat = 1 Kali Alfa (floor(terlambat / 3)) """ import os import csv import math import secrets import sqlite3 from datetime import datetime, date BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) DATA_DIR = os.path.join(BASE_DIR, "data") os.makedirs(DATA_DIR, exist_ok=True) DB_PATH = os.path.join(DATA_DIR, "absen_hris_ptbss.db") # Definisi Shift & Jam Kerja Resmi Perusahaan SHIFT_DEFINITIONS = { "OFFICE": { "kode": "OFFICE", "nama": "Jam Kerja Office (Sen-Jum 08:00-16:00 | Sabtu 08:00-13:00)", "kategori": "Office", "jam_masuk": "08:00", "jam_pulang_normal": "16:00", "jam_pulang_sabtu": "13:00", "durasi_normal": 8.0, "durasi_sabtu": 5.0, }, "PROYEK_06": { "kode": "PROYEK_06", "nama": "Jam Kerja Proyek Shift Pagi 06:00 - 18:00 (12 Jam)", "kategori": "Proyek", "jam_masuk": "06:00", "jam_pulang_normal": "18:00", "jam_pulang_sabtu": "18:00", "durasi_normal": 12.0, "durasi_sabtu": 12.0, }, "PROYEK_07": { "kode": "PROYEK_07", "nama": "Jam Kerja Proyek Shift Pagi 07:00 - 19:00 (12 Jam)", "kategori": "Proyek", "jam_masuk": "07:00", "jam_pulang_normal": "19:00", "jam_pulang_sabtu": "19:00", "durasi_normal": 12.0, "durasi_sabtu": 12.0, }, "PROYEK_08": { "kode": "PROYEK_08", "nama": "Jam Kerja Proyek Shift Pagi 08:00 - 20:00 (12 Jam)", "kategori": "Proyek", "jam_masuk": "08:00", "jam_pulang_normal": "20:00", "jam_pulang_sabtu": "20:00", "durasi_normal": 12.0, "durasi_sabtu": 12.0, }, } # Daftar Lokasi Kerja Resmi (Office & Proyek) DEFAULT_LOCATIONS = [ { "kode_lokasi": "LOC-01", "nama_lokasi": "Kantor Operasional Pasar Tua", "kategori": "Office", "mode_radius": "Ditentukan", "latitude": -0.951850, "longitude": 122.787520, "radius_meter": 150, "shift_default": "OFFICE", "jam_masuk_senin_jumat": "08:00", "jam_pulang_senin_jumat": "16:00", "jam_masuk_sabtu": "08:00", "jam_pulang_sabtu": "13:00", "total_jam_kerja": 8.0, "keterangan": "Kantor Operasional Utama Pasar Tua - Jam Office (Sen-Jum 08:00-16:00, Sabtu 08:00-13:00)", }, { "kode_lokasi": "LOC-02", "nama_lokasi": "Kantor Administrasi Sukarno", "kategori": "Office", "mode_radius": "Ditentukan", "latitude": -0.948320, "longitude": 122.791140, "radius_meter": 150, "shift_default": "OFFICE", "jam_masuk_senin_jumat": "08:00", "jam_pulang_senin_jumat": "16:00", "jam_masuk_sabtu": "08:00", "jam_pulang_sabtu": "13:00", "total_jam_kerja": 8.0, "keterangan": "Kantor Pusat Administrasi, HRD & Akuntansi Jl. Sukarno", }, { "kode_lokasi": "LOC-03", "nama_lokasi": "Kantor Maintenance", "kategori": "Office", "mode_radius": "Ditentukan", "latitude": -0.956410, "longitude": 122.781900, "radius_meter": 200, "shift_default": "OFFICE", "jam_masuk_senin_jumat": "08:00", "jam_pulang_senin_jumat": "16:00", "jam_masuk_sabtu": "08:00", "jam_pulang_sabtu": "13:00", "total_jam_kerja": 8.0, "keterangan": "Kantor Divisi Maintenance & Peralatan", }, { "kode_lokasi": "LOC-04", "nama_lokasi": "Kantor Logistik", "kategori": "Office", "mode_radius": "Ditentukan", "latitude": -0.954120, "longitude": 122.784300, "radius_meter": 200, "shift_default": "OFFICE", "jam_masuk_senin_jumat": "08:00", "jam_pulang_senin_jumat": "16:00", "jam_masuk_sabtu": "08:00", "jam_pulang_sabtu": "13:00", "total_jam_kerja": 8.0, "keterangan": "Kantor & Gudang Utama Divisi Logistik / Ekspedisi", }, { "kode_lokasi": "LOC-05", "nama_lokasi": "Kantor Workshop di Lamo", "kategori": "Proyek", "mode_radius": "Ditentukan", "latitude": -1.258400, "longitude": 122.482100, "radius_meter": 350, "shift_default": "PROYEK_07", "jam_masuk_senin_jumat": "07:00", "jam_pulang_senin_jumat": "19:00", "jam_masuk_sabtu": "07:00", "jam_pulang_sabtu": "19:00", "total_jam_kerja": 12.0, "keterangan": "Basecamp & Workshop Alat Berat Lamo (Mendukung Jam Proyek 12 Jam & Office)", }, { "kode_lokasi": "LOC-06", "nama_lokasi": "Lokasi Site Sinoro 2", "kategori": "Proyek", "mode_radius": "Bebas Radius", "latitude": -1.314500, "longitude": 122.441800, "radius_meter": 1000, "shift_default": "PROYEK_06", "jam_masuk_senin_jumat": "06:00", "jam_pulang_senin_jumat": "18:00", "jam_masuk_sabtu": "06:00", "jam_pulang_sabtu": "18:00", "total_jam_kerja": 12.0, "keterangan": "Lokasi Proyek Lapangan Site Sinoro 2 - Total Jam Kerja Proyek 12 Jam (06:00/07:00/08:00)", }, { "kode_lokasi": "LOC-07", "nama_lokasi": "Lokasi Site Batching Plant", "kategori": "Proyek", "mode_radius": "Ditentukan", "latitude": -1.279200, "longitude": 122.468500, "radius_meter": 400, "shift_default": "PROYEK_07", "jam_masuk_senin_jumat": "07:00", "jam_pulang_senin_jumat": "19:00", "jam_masuk_sabtu": "07:00", "jam_pulang_sabtu": "19:00", "total_jam_kerja": 12.0, "keterangan": "Lokasi Proyek Site Batching Plant (PT. SBM / Beton) - Jam Kerja Proyek 12 Jam", }, { "kode_lokasi": "LOC-08", "nama_lokasi": "Lokasi MSC", "kategori": "Office", "mode_radius": "Ditentukan", "latitude": -0.943800, "longitude": 122.795400, "radius_meter": 150, "shift_default": "OFFICE", "jam_masuk_senin_jumat": "08:00", "jam_pulang_senin_jumat": "16:00", "jam_masuk_sabtu": "08:00", "jam_pulang_sabtu": "13:00", "total_jam_kerja": 8.0, "keterangan": "Lokasi Maleo Sport Centre (MSC) - Jam Kerja Office", }, { "kode_lokasi": "LOC-09", "nama_lokasi": "Lokasi Program Programmer", "kategori": "Office", "mode_radius": "Ditentukan", "latitude": -0.949900, "longitude": 122.789800, "radius_meter": 200, "shift_default": "OFFICE", "jam_masuk_senin_jumat": "08:00", "jam_pulang_senin_jumat": "16:00", "jam_masuk_sabtu": "08:00", "jam_pulang_sabtu": "13:00", "total_jam_kerja": 8.0, "keterangan": "Ruang IT, Sistem Informasi HRIS & Lokasi Program Programmer", }, { "kode_lokasi": "LOC-10", "nama_lokasi": "Proyek Lapangan Umum (Bebas Radius GPS)", "kategori": "Proyek", "mode_radius": "Bebas Radius", "latitude": -1.302000, "longitude": 122.455000, "radius_meter": 0, "shift_default": "PROYEK_06", "jam_masuk_senin_jumat": "06:00", "jam_pulang_senin_jumat": "18:00", "jam_masuk_sabtu": "06:00", "jam_pulang_sabtu": "18:00", "total_jam_kerja": 12.0, "keterangan": "Kategori Proyek dengan pembebasan radius GPS untuk personel mobile / titik proyek baru (12 Jam Kerja)", }, ] def get_connection(): conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row return conn def haversine_distance_meters(lat1: float, lon1: float, lat2: float, lon2: float) -> float: """Menghitung jarak dalam meter antara dua titik koordinat GPS (Latitude, Longitude).""" if lat1 is None or lon1 is None or lat2 is None or lon2 is None: return 0.0 r = 6371000.0 # Radius bumi dalam meter phi1 = math.radians(float(lat1)) phi2 = math.radians(float(lat2)) delta_phi = math.radians(float(lat2) - float(lat1)) delta_lambda = math.radians(float(lon2) - float(lon1)) a = ( math.sin(delta_phi / 2.0) ** 2 + math.cos(phi1) * math.cos(phi2) * math.sin(delta_lambda / 2.0) ** 2 ) c = 2.0 * math.atan2(math.sqrt(a), math.sqrt(1.0 - a)) return round(r * c, 1) def is_saturday(tanggal_str: str) -> bool: """Mengecek apakah tanggal (YYYY-MM-DD) adalah hari Sabtu.""" try: dt = datetime.strptime(tanggal_str, "%Y-%m-%d").date() return dt.weekday() == 5 # 5 = Sabtu except Exception: return False def get_schedule_for_shift(shift_kode: str, tanggal_str: str, kategori_kerja: str = "Office"): """ Mengembalikan jadwal jam masuk, jam pulang, dan target durasi jam kerja berdasarkan kategori kerja (Office vs Proyek), pilihan shift, dan hari (Senin-Jumat vs Sabtu). """ shift = SHIFT_DEFINITIONS.get(shift_kode) if not shift: if kategori_kerja == "Proyek": shift = SHIFT_DEFINITIONS["PROYEK_07"] else: shift = SHIFT_DEFINITIONS["OFFICE"] sabtu = is_saturday(tanggal_str) jam_masuk = shift["jam_masuk"] if shift["kode"] == "OFFICE": jam_pulang = shift["jam_pulang_sabtu"] if sabtu else shift["jam_pulang_normal"] target_jam = shift["durasi_sabtu"] if sabtu else shift["durasi_normal"] else: # Jam kerja proyek adalah 12 jam (mulai 06:00, 07:00, atau 08:00) jam_pulang = shift["jam_pulang_normal"] target_jam = 12.0 return { "shift_kode": shift["kode"], "nama_shift": shift["nama"], "jam_jadwal_masuk": jam_masuk, "jam_jadwal_pulang": jam_pulang, "target_jam_kerja": target_jam, "is_sabtu": sabtu, } def evaluate_lateness(jam_masuk_aktual: str, jam_jadwal_masuk: str) -> tuple: """ Mendeteksi apakah karyawan terlambat berdasarkan jam masuk aktual (HH:MM) dan jadwal masuk (HH:MM). Mengembalikan (is_terlambat: int, menit_terlambat: int). """ if not jam_masuk_aktual or not jam_jadwal_masuk: return 0, 0 try: parts_actual = [int(x) for x in jam_masuk_aktual.strip()[:5].split(":")] parts_sched = [int(x) for x in jam_jadwal_masuk.strip()[:5].split(":")] actual_minutes = parts_actual[0] * 60 + parts_actual[1] sched_minutes = parts_sched[0] * 60 + parts_sched[1] diff = actual_minutes - sched_minutes if diff > 0: return 1, diff return 0, 0 except Exception: return 0, 0 def calculate_work_duration(jam_masuk: str, jam_keluar: str) -> float: """Menghitung total jam kerja aktual antara jam masuk dan jam keluar.""" if not jam_masuk or not jam_keluar: return 0.0 try: m_in = [int(x) for x in jam_masuk.strip()[:5].split(":")] m_out = [int(x) for x in jam_keluar.strip()[:5].split(":")] in_mins = m_in[0] * 60 + m_in[1] out_mins = m_out[0] * 60 + m_out[1] if out_mins < in_mins: out_mins += 24 * 60 # Lintas hari return round((out_mins - in_mins) / 60.0, 2) except Exception: return 0.0 def map_employee_default_location(perusahaan: str, departemen: str, jabatan: str): """Menentukan lokasi kerja default, kategori (Office/Proyek), dan shift default secara cerdas.""" p = (perusahaan or "").upper() d = (departemen or "").upper() j = (jabatan or "").upper() if "SBM" in p or "BETON" in d or "BATCHING" in j or "MIXER" in j: return "Proyek", "Lokasi Site Batching Plant", "PROYEK_07" if "MSC" in p or "MALEO SPORT" in d: return "Office", "Lokasi MSC", "OFFICE" if "LAMO" in j: return "Proyek", "Kantor Workshop di Lamo", "PROYEK_07" if "LOGISTIK" in d or "GUDANG" in j or "EXPEDISI" in j: return "Office", "Kantor Logistik", "OFFICE" if "ACCOUNTING" in d or "AKUNTANSI" in j or "KASIR" in j or "PAJAK" in j: return "Office", "Kantor Administrasi Sukarno", "OFFICE" if "HUMAN RESOURCE" in d or "HRD" in j: if "LUWUK" in j: return "Office", "Kantor Operasional Pasar Tua", "OFFICE" return "Office", "Kantor Administrasi Sukarno", "OFFICE" if "KONTRUKSI" in d or "PROYEK" in j or "HSE" in d or "HSE" in j: return "Proyek", "Lokasi Site Sinoro 2", "PROYEK_06" if "MAINTENANCE" in d: if any(k in j for k in ["OPERATOR", "RIGGER", "WELDER", "CRANE", "EXCAVATOR", "EXAFATOR", "BULDOZER", "MANLIFT", "GREDER", "BOMAG"]): return "Proyek", "Lokasi Site Sinoro 2", "PROYEK_06" if any(k in j for k in ["MEKANIK", "BENGKEL", "BUBUTAN", "LAS"]): return "Proyek", "Kantor Workshop di Lamo", "PROYEK_07" return "Office", "Kantor Maintenance", "OFFICE" if "GENERAL SERVICE" in d: if "PROYEK" in j: return "Proyek", "Lokasi Site Sinoro 2", "PROYEK_07" return "Office", "Kantor Operasional Pasar Tua", "OFFICE" return "Office", "Kantor Operasional Pasar Tua", "OFFICE" def init_db(): conn = get_connection() cur = conn.cursor() cur.execute( """ CREATE TABLE IF NOT EXISTS work_locations ( id INTEGER PRIMARY KEY AUTOINCREMENT, kode_lokasi TEXT UNIQUE NOT NULL, nama_lokasi TEXT UNIQUE NOT NULL, kategori TEXT NOT NULL DEFAULT 'Office', mode_radius TEXT NOT NULL DEFAULT 'Ditentukan', latitude REAL NOT NULL DEFAULT -0.95185, longitude REAL NOT NULL DEFAULT 122.78752, radius_meter INTEGER NOT NULL DEFAULT 150, shift_default TEXT NOT NULL DEFAULT 'OFFICE', jam_masuk_senin_jumat TEXT NOT NULL DEFAULT '08:00', jam_pulang_senin_jumat TEXT NOT NULL DEFAULT '16:00', jam_masuk_sabtu TEXT NOT NULL DEFAULT '08:00', jam_pulang_sabtu TEXT NOT NULL DEFAULT '13:00', total_jam_kerja REAL NOT NULL DEFAULT 8.0, keterangan TEXT DEFAULT '', aktif INTEGER NOT NULL DEFAULT 1 ) """ ) cur.execute( """ CREATE TABLE IF NOT EXISTS employees ( id INTEGER PRIMARY KEY AUTOINCREMENT, nip TEXT UNIQUE NOT NULL, nama TEXT NOT NULL, perusahaan TEXT NOT NULL DEFAULT 'PT. BSS', jabatan TEXT NOT NULL DEFAULT 'Staf', departemen TEXT NOT NULL DEFAULT 'General Service', kategori_kerja TEXT NOT NULL DEFAULT 'Office', lokasi_default_id INTEGER, nama_lokasi_default TEXT DEFAULT 'Kantor Operasional Pasar Tua', shift_default TEXT NOT NULL DEFAULT 'OFFICE', gaji_pokok_hris REAL DEFAULT 4500000, potongan_alfa_per_hari REAL DEFAULT 150000, no_hp TEXT DEFAULT '', status_aktif INTEGER NOT NULL DEFAULT 1, FOREIGN KEY (lokasi_default_id) REFERENCES work_locations(id) ) """ ) cur.execute( """ CREATE TABLE IF NOT EXISTS attendance_logs ( id INTEGER PRIMARY KEY AUTOINCREMENT, tanggal TEXT NOT NULL, employee_id INTEGER NOT NULL, nip TEXT NOT NULL, nama_karyawan TEXT NOT NULL, perusahaan TEXT NOT NULL, departemen TEXT NOT NULL, jabatan TEXT NOT NULL, kategori_kerja TEXT NOT NULL DEFAULT 'Office', lokasi_id INTEGER, nama_lokasi TEXT NOT NULL, mode_radius TEXT NOT NULL DEFAULT 'Ditentukan', shift_kode TEXT NOT NULL DEFAULT 'OFFICE', jam_jadwal_masuk TEXT NOT NULL DEFAULT '08:00', jam_jadwal_pulang TEXT NOT NULL DEFAULT '16:00', target_jam_kerja REAL NOT NULL DEFAULT 8.0, jam_masuk TEXT DEFAULT '', jam_keluar TEXT DEFAULT '', durasi_jam_kerja REAL DEFAULT 0.0, status_kehadiran TEXT NOT NULL DEFAULT 'Hadir', is_terlambat INTEGER NOT NULL DEFAULT 0, menit_terlambat INTEGER NOT NULL DEFAULT 0, lat_masuk REAL, lng_masuk REAL, jarak_masuk_meter REAL DEFAULT 0.0, status_gps_masuk TEXT DEFAULT 'Valid', lat_keluar REAL, lng_keluar REAL, jarak_keluar_meter REAL DEFAULT 0.0, status_gps_keluar TEXT DEFAULT '', catatan TEXT DEFAULT '', created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP, UNIQUE(tanggal, employee_id), FOREIGN KEY (employee_id) REFERENCES employees(id), FOREIGN KEY (lokasi_id) REFERENCES work_locations(id) ) """ ) # Migrasi: kolom token untuk link absen pribadi tiap karyawan cur.execute("PRAGMA table_info(employees)") emp_cols = [r[1] for r in cur.fetchall()] if "token_absen" not in emp_cols: cur.execute("ALTER TABLE employees ADD COLUMN token_absen TEXT") conn.commit() seed_initial_data(conn) ensure_employee_tokens(conn) conn.close() def generate_employee_token() -> str: """Token acak yang sulit ditebak (untuk link absen pribadi karyawan).""" return secrets.token_urlsafe(9) def ensure_employee_tokens(conn: sqlite3.Connection): """Isi token untuk karyawan yang belum punya, lalu pastikan token unik.""" cur = conn.cursor() cur.execute("SELECT id FROM employees WHERE token_absen IS NULL OR token_absen = ''") for row in cur.fetchall(): cur.execute( "UPDATE employees SET token_absen = ? WHERE id = ?", (generate_employee_token(), row[0]), ) cur.execute( "CREATE UNIQUE INDEX IF NOT EXISTS idx_employees_token_absen ON employees(token_absen)" ) conn.commit() def seed_initial_data(conn: sqlite3.Connection): cur = conn.cursor() # 1. Seed Lokasi Kerja cur.execute("SELECT COUNT(*) as cnt FROM work_locations") if cur.fetchone()["cnt"] == 0: for loc in DEFAULT_LOCATIONS: cur.execute( """ INSERT INTO work_locations ( kode_lokasi, nama_lokasi, kategori, mode_radius, latitude, longitude, radius_meter, shift_default, jam_masuk_senin_jumat, jam_pulang_senin_jumat, jam_masuk_sabtu, jam_pulang_sabtu, total_jam_kerja, keterangan ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """, ( loc["kode_lokasi"], loc["nama_lokasi"], loc["kategori"], loc["mode_radius"], loc["latitude"], loc["longitude"], loc["radius_meter"], loc["shift_default"], loc["jam_masuk_senin_jumat"], loc["jam_pulang_senin_jumat"], loc["jam_masuk_sabtu"], loc["jam_pulang_sabtu"], loc["total_jam_kerja"], loc["keterangan"], ), ) conn.commit() # Map nama_lokasi -> id cur.execute("SELECT id, nama_lokasi, latitude, longitude, mode_radius FROM work_locations") loc_map = {row["nama_lokasi"]: dict(row) for row in cur.fetchall()} # 2. Seed Master Karyawan (dari CSV 94 Time Sheet jika tersedia + Programmer IT) cur.execute("SELECT COUNT(*) as cnt FROM employees") if cur.fetchone()["cnt"] == 0: csv_path = os.path.join(os.path.dirname(BASE_DIR), "94 Time Sheet", "Master Karyawan.csv") employees_to_insert = [] seen_nips = set() if os.path.exists(csv_path): with open(csv_path, mode="r", encoding="utf-8-sig", errors="replace") as f: reader = csv.reader(f) rows = list(reader) for row in rows[2:]: if not row or len(row) < 3: continue perusahaan = (row[0] or "PT. BSS").strip() or "PT. BSS" nip = (row[1] or "").strip() nama = (row[2] or "").strip() jabatan = (row[3] or "Staf").strip() or "Staf" departemen = (row[4] or "General Service").strip() or "General Service" if not nip or not nama: continue if nip in seen_nips: nip = f"{nip}-B" seen_nips.add(nip) kat, nama_lok, shift_def = map_employee_default_location(perusahaan, departemen, jabatan) # Khusus IR Gunawan Pria Utama, M.KOM atau IT -> Lokasi Program Programmer if "M.KOM" in nama.upper() or "PROGRAMMER" in jabatan.upper(): kat = "Office" nama_lok = "Lokasi Program Programmer" shift_def = "OFFICE" if jabatan == "Staf": jabatan = "Lead Programmer & Sistem HRIS" loc_id = loc_map.get(nama_lok, {}).get("id", 1) employees_to_insert.append( (nip, nama, perusahaan, jabatan, departemen, kat, loc_id, nama_lok, shift_def) ) # Pastikan juga ada personel di setiap 9 lokasi spesifik bila belum terwakili extra_employees = [ ( "IT-2026-001", "Tim Programmer HRIS PT. BSS", "PT. BSS", "Fullstack Programmer HRIS", "Human Resource Development", "Office", loc_map.get("Lokasi Program Programmer", {}).get("id", 9), "Lokasi Program Programmer", "OFFICE", ), ] for emp in extra_employees: if emp[0] not in seen_nips: employees_to_insert.append(emp) seen_nips.add(emp[0]) for emp in employees_to_insert: cur.execute( """ INSERT INTO employees ( nip, nama, perusahaan, jabatan, departemen, kategori_kerja, lokasi_default_id, nama_lokasi_default, shift_default ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) """, emp, ) conn.commit() # 3. Seed Data Absensi Contoh Realistis (Bulan Oktober 2026 & Hari Ini) cur.execute("SELECT COUNT(*) as cnt FROM attendance_logs") if cur.fetchone()["cnt"] == 0: cur.execute("SELECT * FROM employees ORDER BY id LIMIT 25") sample_emps = [dict(r) for r in cur.fetchall()] today_str = date.today().strftime("%Y-%m-%d") # Tanggal histori di bulan berjalan untuk simulasi akumulasi 3x terlambat = 1x Alfa prefix_ym = today_str[:7] # e.g. 2026-10 hist_dates = [ f"{prefix_ym}-21", f"{prefix_ym}-22", f"{prefix_ym}-23", f"{prefix_ym}-24", # Sabtu (Office pulang jam 13:00!) today_str, ] # Hilangkan duplikat jika today_str salah satu dari hist_dates unique_dates = [] for d in hist_dates: if d not in unique_dates: unique_dates.append(d) for idx, emp in enumerate(sample_emps): loc_info = loc_map.get(emp["nama_lokasi_default"], list(loc_map.values())[0]) base_lat = loc_info["latitude"] base_lng = loc_info["longitude"] mode_rad = loc_info["mode_radius"] for d_idx, tgl in enumerate(unique_dates): sched = get_schedule_for_shift(emp["shift_default"], tgl, emp["kategori_kerja"]) jadwal_in = sched["jam_jadwal_masuk"] jadwal_out = sched["jam_jadwal_pulang"] target_jam = sched["target_jam_kerja"] # Variasi skenario agar langsung terlihat di Dashboard & Rekapitulasi HRIS: # - Karyawan idx 0 & idx 5: Terlambat 3 hari berturut-turut -> Akumulasi 1x ALFA! # - Karyawan idx 1: Terlambat 6 hari / 3 hari + 1 Alfa murni # - Karyawan idx 2: Terlambat 2 hari (Peringatan 2/3 menuju 1 Alfa) # - Karyawan idx 3: Sakit 1 hari, Hadir hari lainnya # - Karyawan idx 4: Izin 1 hari, Cuti 1 hari status_kehadiran = "Hadir" jam_in = jadwal_in jam_out = jadwal_out catatan = "Absen via HP GPS Online" if idx in (0, 5) and d_idx in (0, 1, 2): # Terlambat 3 hari -> Terakumulasi 1x Alfa! jam_in = "08:25" if jadwal_in == "08:00" else ("07:20" if jadwal_in == "07:00" else "06:18") status_kehadiran = "Terlambat" catatan = f"Terlambat hari ke-{d_idx + 1} (Akumulasi 3x Terlambat = 1x Alfa)" elif idx == 2 and d_idx in (1, 2): jam_in = "08:15" if jadwal_in == "08:00" else "07:15" status_kehadiran = "Terlambat" catatan = f"Terlambat hari ke-{d_idx} (2/3 menuju 1 Alfa)" elif idx == 3 and d_idx == 1: status_kehadiran = "Sakit" jam_in = "" jam_out = "" catatan = "Surat Keterangan Dokter (Sakit)" elif idx == 4 and d_idx == 1: status_kehadiran = "Izin" jam_in = "" jam_out = "" catatan = "Izin Urusan Keluarga" elif idx == 6 and d_idx == 2: status_kehadiran = "Cuti" jam_in = "" jam_out = "" catatan = "Cuti Tahunan HRD" elif idx == 7 and d_idx == 1: status_kehadiran = "Alfa" jam_in = "" jam_out = "" catatan = "Tanpa Keterangan (Alfa Murni)" else: # Hadir tepat waktu (beberapa menit sebelum jadwal masuk) if jadwal_in == "06:00": jam_in = "05:52" elif jadwal_in == "07:00": jam_in = "06:54" else: jam_in = "07:53" is_late, late_mins = evaluate_lateness(jam_in, jadwal_in) if is_late and status_kehadiran == "Hadir": status_kehadiran = "Terlambat" durasi = calculate_work_duration(jam_in, jam_out) if jam_in and jam_out else 0.0 lat_in = round(base_lat + (idx % 5) * 0.00012, 6) if jam_in else None lng_in = round(base_lng + (idx % 5) * 0.00012, 6) if jam_in else None jarak = haversine_distance_meters(base_lat, base_lng, lat_in, lng_in) if lat_in else 0.0 gps_stat = ( "Bebas Radius (Proyek)" if mode_rad == "Bebas Radius" else f"Di Dalam Radius ({jarak}m)" ) cur.execute( """ INSERT OR IGNORE INTO attendance_logs ( tanggal, employee_id, nip, nama_karyawan, perusahaan, departemen, jabatan, kategori_kerja, lokasi_id, nama_lokasi, mode_radius, shift_kode, jam_jadwal_masuk, jam_jadwal_pulang, target_jam_kerja, jam_masuk, jam_keluar, durasi_jam_kerja, status_kehadiran, is_terlambat, menit_terlambat, lat_masuk, lng_masuk, jarak_masuk_meter, status_gps_masuk, lat_keluar, lng_keluar, jarak_keluar_meter, status_gps_keluar, catatan ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """, ( tgl, emp["id"], emp["nip"], emp["nama"], emp["perusahaan"], emp["departemen"], emp["jabatan"], emp["kategori_kerja"], loc_info["id"], emp["nama_lokasi_default"], mode_rad, sched["shift_kode"], jadwal_in, jadwal_out, target_jam, jam_in, jam_out, durasi, status_kehadiran, is_late, late_mins, lat_in, lng_in, jarak, gps_stat if jam_in else "-", lat_in, lng_in, jarak, gps_stat if jam_out else "-", catatan, ), ) conn.commit()