#!/usr/bin/env python3 """ ETL Fleet Safety Dashboard - Regional JBT ----------------------------------------- Membaca 7 file Excel sumber, menormalkan lokasi & periode, lalu menyuntikkan data (JSON) + gambar background login ke template HTML menjadi satu file aplikasi web mandiri. Pemakaian: python etl_fleet_safety.py --input ./excel --template template.html \ --background login_bg.png --output Fleet_Safety_JBT.html Nama file Excel dicari berdasarkan kata kunci (lihat SOURCES), jadi file bulan berikutnya cukup ditaruh di folder input yang sama. """ import argparse, base64, datetime as dt, glob, hashlib, io, json, os, re, sys import openpyxl MONTHS = ['januari','februari','maret','april','mei','juni','juli','agustus', 'september','oktober','november','desember'] # Modul dashboard (urutan tab) dan modul input yang bisa diatur per pengguna. VIEW_MODS = ['ringkasan', 'fleet', 'kedap', 'briefing', 'b3', 'apar', 'tagihan', 'rjk', 'master', 'tugas'] INPUT_MODS = ['fleet', 'briefing', 'b3', 'kedap', 'tagihan', 'rjk', 'mt'] # Akun default bila --users tidak diberikan. GANTI kata sandi sebelum dibagikan. DEFAULT_USERS = [ {'username': 'admin', 'password': 'FleetJBT@2026', 'nama': 'Administrator JBT', 'lokasi': 'ALL', 'akses': 'admin'}, {'username': 'semarang', 'password': 'FleetJBT@2026', 'nama': 'PIC Fleet IT Semarang', 'lokasi': 'SMG', 'akses': 'input'}, {'username': 'boyolali', 'password': 'FleetJBT@2026', 'nama': 'PIC Fleet FT Boyolali', 'lokasi': 'BYL', 'akses': 'input'}, {'username': 'cepu', 'password': 'FleetJBT@2026', 'nama': 'PIC Fleet FT Cepu', 'lokasi': 'CPU', 'akses': 'input'}, {'username': 'rewulu', 'password': 'FleetJBT@2026', 'nama': 'PIC Fleet FT Rewulu', 'lokasi': 'RWL', 'akses': 'input'}, {'username': 'maos', 'password': 'FleetJBT@2026', 'nama': 'PIC Fleet FT Maos', 'lokasi': 'MAOS', 'akses': 'input'}, {'username': 'tegal', 'password': 'FleetJBT@2026', 'nama': 'PIC Fleet FT Tegal', 'lokasi': 'TGL', 'akses': 'input'}, {'username': 'cilacap', 'password': 'FleetJBT@2026', 'nama': 'PIC Fleet IT Cilacap', 'lokasi': 'CLP', 'akses': 'input'}, {'username': 'manajer', 'password': 'FleetJBT@2026', 'nama': 'Manajer Region JBT', 'lokasi': 'ALL', 'akses': 'lihat'}, ] def build_user(x): """Satu entri users.json -> akun aplikasi (u/n/l/r/m/i/h).""" akses = str(x.get('akses', '')).lower() role = {'admin': 'admin', 'input': 'input', 'lihat': 'viewer'}.get(akses, 'admin' if x.get('lokasi', 'ALL') == 'ALL' else 'input') keep = lambda val, allowed, default: ([m for m in allowed if m in val] if isinstance(val, list) else default) view = keep(x.get('lihat'), VIEW_MODS, list(VIEW_MODS)) inp = [] if role == 'viewer' else keep(x.get('input'), INPUT_MODS, list(INPUT_MODS)) if 'ringkasan' not in view: view = ['ringkasan'] + view # ringkasan selalu tersedia h = x.get('hash') or hashlib.sha256(('fsjbt:' + x['password']).encode('utf-8')).hexdigest() return dict(u=x['username'].strip().lower(), n=x.get('nama', x['username']), l=x.get('lokasi', 'ALL'), r=role, m=view, i=inp, h=h) # ---------------------------------------------------------------- lokasi --- LOCS = [ ('SMG', 'IT Semarang'), ('BYL', 'FT Boyolali'), ('CPU', 'FT Cepu'), ('RWL', 'FT Rewulu'), ('MAOS', 'FT Maos'), ('TGL', 'FT Tegal'), ('CLP', 'IT Cilacap'), ] def loc_id(text): t = re.sub(r'[^a-z ]', ' ', str(text or '').lower()) if 'cepu' in t and 'boyolali' not in t: return 'CPU' if 'boyolali' in t and 'cepu' in t and '(' not in str(text): return 'CPU' # "BOYOLALI - CEPU" for key, lid in [('semarang','SMG'),('boyolali','BYL'),('rewulu','RWL'), ('maos','MAOS'),('tegal','TGL'),('cilacap','CLP'),('sleko','CLP')]: if key in t: return lid return None # ------------------------------------------------------------- helpers --- def month_from_text(s): s = str(s or '').lower() for i, m in enumerate(MONTHS): if m in s: return i + 1 return None def to_date(v): """datetime | 'dd/mm/yyyy' | 'SENIN, 24 AGUSTUS 2026' | serial Excel -> 'YYYY-MM-DD'""" if v is None or v == '' or v == 0: return None if isinstance(v, dt.datetime): return v.date().isoformat() if isinstance(v, dt.date): return v.isoformat() if isinstance(v, (int, float)): if 30000 < v < 60000: return (dt.date(1899, 12, 30) + dt.timedelta(days=int(v))).isoformat() return None s = str(v).strip() m = re.match(r'^(\d{1,2})[/-](\d{1,2})[/-](\d{2,4})$', s) if m: d, mo, y = map(int, m.groups()); y = y + 2000 if y < 100 else y try: return dt.date(y, mo, d).isoformat() except ValueError: return None m = re.search(r'(\d{1,2})\s+([a-zA-Z]+)\s+(\d{4})', s) if m and month_from_text(m.group(2)): try: return dt.date(int(m.group(3)), month_from_text(m.group(2)), int(m.group(1))).isoformat() except ValueError: return None return None def num(v): if v is None or isinstance(v, bool): return None if isinstance(v, (int, float)): return round(v, 4) if isinstance(v, float) else v s = str(v).strip().replace(' ', '') if re.match(r'^-?\d+([.,]\d+)?$', s): return float(s.replace(',', '.')) return None def txt(v): if v is None: return None if isinstance(v, dt.datetime): return v.date().isoformat() s = re.sub(r'\s+', ' ', str(v)).strip() return s or None def col(letter): n = 0 for ch in letter: n = n * 26 + ord(ch) - 64 return n - 1 def find(folder, *keys): for f in sorted(glob.glob(os.path.join(folder, '*.xlsx'))): name = os.path.basename(f).lower() if all(k.lower() in name for k in keys): return f return None def open_wb(path): return openpyxl.load_workbook(path, read_only=True, data_only=True) def pack(cols, rows): """Format ringkas: nama kolom sekali, baris sebagai array.""" return {'c': cols, 'r': [[r.get(c) for c in cols] for r in rows]} PLATE = re.compile(r'^[A-Z]{1,2}\s?\d{1,4}\s?[A-Z]{0,4}$') # ============================================== 1. Rekapitulasi bulanan === def etl_rekap(path, year): wb = open_wb(path) loc_rows, region_rows = [], [] for ws in wb.worksheets: m = month_from_text(ws.title) if not m or not re.fullmatch(r'\s*[A-Za-z]+\s*', ws.title): continue region = None for r in ws.iter_rows(min_row=6, values_only=True): r = list(r) + [None] * 25 b, c = r[1], r[2] if b is not None and c is None and isinstance(b, str) and b.strip().upper() != 'TOTAL' and 'GRAND' not in b.upper(): region = b.strip(); continue row = dict(y=year, m=m, mt=num(r[3]), fab=num(r[4]), greasing=num(r[5]), pushrod=num(r[7]), drain=num(r[9]), apar=num(r[11]), afff=num(r[13]), aoh=num(r[15]), aohCek=num(r[16]), backing=num(r[18])) if isinstance(b, str) and b.strip().upper() == 'TOTAL' and region and region.upper() == 'JBT': region_rows.append(dict(row, region=region)) # region lain tidak dimuat elif region and region.upper() == 'JBT' and isinstance(c, str): lid = loc_id(c) if lid: loc_rows.append(dict(row, loc=lid)) return loc_rows, region_rows # ============================================ 2. Program Fleet Safety unit === FS_SHEETS = { # kolom per sheet karena tiap terminal memakai layout berbeda 'Semarang': dict(greasing='P', pushrod='Q', mur='R', aoh='S', remb='T', level='U', gel='V', drain='W', susp='X', pneu='Y', apar='Z', afff='AA', backing='AB', tl='AC', ket='AG', evid='AF'), 'Boyolali': dict(greasing='P', pushrod='Q', mur='S', aoh='T', remb='U', level='V', gel='W', drain='X', susp='Y', pneu='Z', apar='AA', afff='AB', backing='AC', tl='AD', ket='AE', evid=None), 'Rewulu': dict(greasing='P', pushrod='Q', mur='R', aoh='T', remb='U', level='V', gel='W', drain='X', susp='Y', pneu='Z', apar='AE', afff='AF', backing='AG', tl='S', ket='AM', evid=None), 'Maos': dict(greasing='P', pushrod='Q', mur='R', aoh='S', remb='T', level='U', gel='V', drain='W', susp='X', pneu='Y', apar='Z', afff='AA', backing='AB', tl='AC', ket='AE', evid='AF'), 'Tegal': dict(greasing='P', pushrod='Q', mur='R', aoh='S', remb='T', level='U', gel='V', drain='W', susp='X', pneu='Y', apar='Z', afff='AA', backing='AB', tl='AC', ket='AG', evid=None), 'Cilacap': dict(greasing='P', pushrod='Q', mur='R', aoh='S', remb='T', level='U', gel='V', drain='W', susp='X', pneu='Y', apar='Z', afff='AA', backing='AB', tl='AC', ket='AE', evid='AF'), } def apar_total(v): """'4' -> 4 ; '1,2,1' (foam, CO2, DCP) -> 4""" if isinstance(v, (int, float)): return int(v) nums = [int(x) for x in re.findall(r'\d+', str(v or ''))] return sum(nums) if nums else None def year_of(v): if isinstance(v, (dt.datetime, dt.date)): return v.year n = num(v); return int(n) if n and 1950 < n < 2100 else None def etl_fleet_units(path, year, month): wb = open_wb(path); out = [] for sheet, cfg in FS_SHEETS.items(): if sheet not in wb.sheetnames: continue fallback = loc_id(sheet) for r in wb[sheet].iter_rows(values_only=True): r = list(r) + [None] * 60 nopol = txt(r[3]) if not nopol or not PLATE.match(nopol.upper().replace(' ', '')): continue g = lambda k: r[col(cfg[k])] if cfg.get(k) else None evid = txt(g('evid')); evid = evid if evid and evid.startswith('http') else None out.append(dict( loc=loc_id(r[2]) or fallback, y=year, m=month, status='Operasi', nopol=nopol.upper().replace(' ', ''), pemilik=txt(r[4]), pola=txt(r[5]), kap=num(r[6]) if num(r[6]) is not None else num((txt(r[6]) or '').split(' ')[0]), jenis=txt(r[7]), builder=txt(r[8]), thnTangki=year_of(r[9]), merk=txt(r[10]), thnHT=year_of(r[11]), type=txt(r[12]), rem=txt(r[13]), odo=num(r[14]), tglGreasing=to_date(g('greasing')), tglPushrod=to_date(g('pushrod')), mur=txt(g('mur')), tglAoh=to_date(g('aoh')), rembesan=txt(g('remb')), levelRem=txt(g('level')), gelembung=txt(g('gel')), drain=txt(g('drain')), drainSusp=txt(g('susp')), drainPneu=txt(g('pneu')), apar=apar_total(g('apar')), afff=txt(g('afff')), backing=txt(g('backing')), tl=txt(g('tl')), ket=txt(g('ket')), evidence=evid)) return out # ======================================================= 3. Sertifikasi B3 === def etl_b3(path, year, month): ws = open_wb(path)['Data Region']; out = [] for r in ws.iter_rows(min_row=7, values_only=True): r = list(r) + [None] * 16 if not r[2]: continue nik = txt(r[3]) or '' link = txt(r[13]); link = link if link and link.startswith('http') else None lembaga = txt(r[10]); lembaga = None if lembaga in (None, '0') else lembaga out.append(dict(loc=loc_id(r[5]), y=year, m=month, nama=txt(r[2]), nik=('•' * 8 + nik[-4:]) if len(nik) > 4 else None, # NIK disamarkan pt=txt(r[4]), produk=txt(r[7]), pola=txt(r[8]), ket=txt(r[14]), jabatan=txt(r[6]), status=(txt(r[9]) or '').capitalize() or None, lembaga=lembaga, tgl=to_date(r[11]), noSert=txt(r[12]) if r[12] not in (0, '0') else None, link=link)) return out # ================================================ 4. Daily Safety Briefing === def grade(p): if p is None: return None return 'A' if p > 0.9 else 'B' if p >= 0.81 else 'C' if p >= 0.52 else 'D' def etl_briefing(path, year, month): wb = open_wb(path) loc_rows, amt_rows = [], [] # ringkasan per terminal (sheet Rekapan, blok RJBT) in_jbt = False for r in wb['Rekapan'].iter_rows(min_row=6, values_only=True): r = list(r) + [None] * 16 if r[2] and 'RJBT' in str(r[2]).upper() and 'TOTAL' not in str(r[2]).upper(): in_jbt = True; continue if in_jbt and r[2] and 'TOTAL' in str(r[2]).upper(): break if in_jbt and r[2]: loc_rows.append(dict(loc=loc_id(r[2]), y=year, m=month, amt=num(r[3]), jadwal=num(r[4]), hadir=num(r[5]), pct=num(r[6]), grade=txt(r[7]), A=num(r[8]), B=num(r[9]), C=num(r[10]), D=num(r[11]), ket=txt(r[12]))) # detail per AMT for name in wb.sheetnames: lid = loc_id(name) if not name.lower().startswith('absensi') or not lid: continue rows = [list(x) + [None] * 45 for x in wb[name].iter_rows(values_only=True)] head = [str(h or '').strip().lower() for h in rows[0]] if 'common_name' in head: # format ekspor SAFIRE iN, iA, iT = head.index('common_name'), head.index('accepted'), head.index('total') iC = head.index('central_id') if 'central_id' in head else None for r in rows[1:]: if not r[iN] or not isinstance(r[iA], (int, float)) or not isinstance(r[iT], (int, float)): continue yy = int(r[1]) if isinstance(r[1], (int, float)) else year mm = month_from_text(r[2]) or month p = r[iA] / r[iT] if r[iT] else None amt_rows.append(dict(loc=lid, y=yy, m=mm, nama=txt(r[iN]), jabatan=None, idSiod=txt(r[iC]) if iC is not None else None, hadir=r[iA], jadwal=r[iT], pct=p, grade=grade(p))) else: # format grid harian (Tegal, Cilacap) hi = next(i for i, r in enumerate(rows) if any(str(c or '').strip().upper() in ('NAMA', 'NAMA AMT') for c in r)) h = [str(c or '').strip().upper() for c in rows[hi]] iN = next(i for i, c in enumerate(h) if c.startswith('NAMA')) iJ = h.index('JABATAN') if 'JABATAN' in h else None iS = h.index('NIP SIOD') if 'NIP SIOD' in h else None iH = next(i for i, c in enumerate(h) if c in ('HADIR', 'KEHADIRAN SAFIRE')) iK = next(i for i, c in enumerate(h) if c in ('JUMLAH HARI KERJA', 'HARI KERJA')) for r in rows[hi + 1:]: if not isinstance(r[0], (int, float)) or not r[iN] or not isinstance(r[iK], (int, float)): continue p = r[iH] / r[iK] if r[iK] else None amt_rows.append(dict(loc=lid, y=year, m=month, nama=txt(r[iN]), idSiod=txt(r[iS]) if iS is not None else None, jabatan=txt(r[iJ]) if iJ is not None else None, hadir=r[iH], jadwal=r[iK], pct=p, grade=grade(p))) return loc_rows, amt_rows # ======================================================= 5. Pelatihan APAR === def etl_apar(path): wb = open_wb(path); out = [] for name in wb.sheetnames: m = re.match(r'Semester\s+(I{1,2})\s+(\d{4})', name.strip()) if not m: continue sem, y = len(m.group(1)), int(m.group(2)) for r in wb[name].iter_rows(min_row=3, max_row=40, values_only=True): r = list(r) + [None] * 8 lid = loc_id(r[2]) if not lid or num(r[3]) is None: continue upd = to_date(r[6]) if not isinstance(r[6], str) else (to_date(r[6]) or txt(r[6])) out.append(dict(loc=lid, y=y, sem=sem, amt=num(r[3]), sudah=num(r[4]), update=upd)) return out # ================================================ 6. Uji kekedapan ALPUKET === def etl_kedap(path): wb = open_wb(path); units, rekap = [], [] for r in wb['ALL ALPUKET SIOD'].iter_rows(min_row=4, values_only=True): r = list(r) + [None] * 24 if not r[3]: continue comps = [txt(c) for c in r[11:17]] units.append(dict(loc=loc_id(r[2]), nopol=txt(r[3]).upper().replace(' ', ''), pemilik=txt(r[4]), pola=txt(r[5]), kap=num(r[6]), jenis=txt(r[7]), builder=txt(r[8]), thn=year_of(r[9]), tglUji=to_date(r[10]), c1=comps[0], c2=comps[1], c3=comps[2], c4=comps[3], c5=comps[4], c6=comps[5], jadwal=to_date(r[17]), tera=to_date(r[18]), ket=txt(r[19]), status=txt(r[20]), cek=txt(r[21]))) for sheet, kat in [('REKAP LOKASI MT RETAIL', 'Retail'), ('REKAP LOKASI MT INDUSTRI_KONS', 'Industri & Konsinyasi'), ('REKAP LOKASI MT BRIDGER', 'Bridger')]: if sheet not in wb.sheetnames: continue rows = [list(x) + [None] * 14 for x in wb[sheet].iter_rows(values_only=True)] title = str(rows[0][1] or ''); m = re.search(r'\((.*?)\)', title) asof = to_date(m.group(1)) if m else None off = 1 if kat == 'Retail' else 0 # sheet retail punya kolom 'Replikasi ALPUKET' for r in rows[4:]: lid = loc_id(r[2]) if not lid or num(r[3 + off]) is None: continue rekap.append(dict(loc=lid, kat=kat, asof=asof, mt=num(r[3 + off]), cek=num(r[4 + off]), kedap=num(r[6 + off]), tdk=num(r[7 + off]), perbaikan=num(r[8 + off]))) return units, rekap # ============================================ 7. Tagihan fleet management === def etl_tagihan(path): wb = open_wb(path); out = [] for ws in wb.worksheets: if ws.title.lower() == 'rekap': continue rows = [list(x) + [None] * 30 for x in ws.iter_rows(values_only=True)] hi = next((i for i, r in enumerate(rows) if 'PERIODE' in [str(c or '').strip().upper() for c in r]), None) if hi is None: continue h = [str(c or '').strip().upper() for c in rows[hi]] ix = lambda k: h.index(k) if k in h else None gv = lambda row, k: num(row[ix(k)]) if ix(k) is not None else None iP, iY, iKet = ix('PERIODE'), ix('TAHUN'), ix('KETERANGAN') denda_cols = [i for i, c in enumerate(h) if c.startswith('DENDA') or c in ('LO OUTSTANDING', 'KONTAMINASI', 'LAKA')] tgls = [i for i, c in enumerate(h) if c == 'TGL'] + [None] * 4 tv = lambda row, i: (to_date(row[i]) if i is not None else None) sv = lambda row, k: (txt(row[ix(k)]) if ix(k) is not None else None) lid = loc_id(ws.title) for r in rows[hi + 1:]: per, y = txt(r[iP]), r[iY] if not per or not isinstance(y, (int, float)): continue nilai = gv(r, 'NILAI TAGIHAN') status = None if iKet is not None: status = next((txt(r[i]) for i in (iKet, iKet + 2, iKet + 1) if isinstance(r[i], str) and txt(r[i]) and txt(r[i]) != '//'), None) if not nilai and not status: continue # periode belum berjalan d = re.match(r'(\d{1,2})\s*-\s*(\d{1,2})', per) out.append(dict(loc=lid, y=int(y), m=month_from_text(per), periode=per.title(), half=half_of(d), kl=gv(r, 'KL'), klkm=gv(r, 'KL.KM') or gv(r, 'KL X KM'), nilai=nilai, denda=sum(num(r[i]) or 0 for i in denda_cols) or 0, nominal=gv(r, 'NOMINAL'), ppn=gv(r, 'PPN'), total=gv(r, 'TOTAL'), dendaJam=gv(r, 'DENDA KELEBIHAN JAM OPS'), lo=gv(r, 'LO OUTSTANDING'), dendaKpi=gv(r, 'DENDA KPI'), dendaLosses=gv(r, 'DENDA LOSSES'), kontaminasi=gv(r, 'KONTAMINASI'), laka=gv(r, 'LAKA'), pr=sv(r, 'PR'), reqPo=sv(r, 'REQ PO'), po=sv(r, 'PO'), tglPo=tv(r, tgls[0]), sa=sv(r, 'SA'), tglSa=tv(r, tgls[1]), invoice=sv(r, 'INVOICE'), tglInv=tv(r, tgls[2]), pa=sv(r, 'PAYMENT APPROVAL'), tglPa=tv(r, tgls[3]), ket=status, status=norm_status(status, nilai))) return out def half_of(d): if not d: return 0 a, b = int(d.group(1)), int(d.group(2)) return 0 if a <= 1 and b >= 28 else 1 if a <= 1 else 2 def norm_status(s, nilai): t = (s or '').lower() if 'paid' in t: return 'Lunas' if t == 'ba' or 'ba ' in t or 'belum' in t: return 'BA belum diterima' if 'payment' in t: return 'Menunggu payment ke mitra' if 'ivendor' in t or 'pa' == t: return 'Proses Ivendor' if 'invoice' in t: return 'Invoicing vendor' if 'bast' in t: return 'Proses BAST' return s or ('Proses' if nilai else None) # ======================================== 8. Ritase & jam kerja AMT (RJK) === RJK_HEAD = { # judul kolom di file RJK -> nama field 'tanggal shipment': 'tgl', 'gate in (state 60)': 'gateIn', 'gate out (state 80)': 'gateOut', 'waktu kembali': 'kembali', 'jam kerja actual': 'jam', 'no. polisi': 'nopol', 'kapasitas mt': 'kap', 'supir aktual': 'supir', 'kernet aktual': 'kernet', 'km aktual': 'km', 'prediksi jarak[km]': 'km', 'total volume[kl]': 'vol', 'jenis sewa mt': 'pola', 'produk': 'produk', 'spbu': 'spbu', 'justifikasi': 'ket', 'keterangan': 'ket', } def jam_group(j): """Kelompok jam kerja sesuai kolom di file sumber.""" if j is None: return None return 'g12' if j > 12 else 'g8' if j > 8 else 'g6' if j > 6 else 'l6' def split_nama(v): """'Wahono [396.99.769/363]' -> ('Wahono', '396.99.769/363')""" s = txt(v) if not s: return None, None m = re.match(r'^(.*?)\s*\[(.*?)\]\s*$', s) return (m.group(1).strip(), m.group(2).strip()) if m else (s, None) def read_rjk_file(path): """Kembalikan (baris valid, jumlah baris tanpa jam kerja per bulan).""" wb = open_wb(path); out = []; skip = {} for ws in wb.worksheets: rows = ws.iter_rows(values_only=True); ix = None for r in rows: if not r: continue if ix is None: if r[0] and str(r[0]).strip().lower() == 'tanggal shipment': ix = {} for j, h in enumerate(r): k = RJK_HEAD.get(str(h or '').strip().lower()) if k and k not in ix: ix[k] = j continue if not isinstance(r[0], dt.datetime): continue g = lambda k: r[ix[k]] if k in ix and ix[k] < len(r) else None jam = num(g('jam')) if jam is None or jam <= 0: # waktu kembali belum terisi -> jam kerja tidak terhitung skip[(r[0].year, r[0].month)] = skip.get((r[0].year, r[0].month), 0) + 1 continue supir, sid = split_nama(g('supir')); kernet, kid = split_nama(g('kernet')) if not supir: continue out.append(dict(tgl=r[0], jam=round(jam, 3), nopol=txt(g('nopol')), supir=supir, sid=sid, kernet=kernet, kid=kid, km=num(g('km')), vol=num(g('vol')), kembali=g('kembali'), ket=txt(g('ket')))) return out, skip def etl_rjk(folder, cap=24): """Baca file RJK per lokasi -> agregat per AMT/bulan, per hari, dan daftar trip > 12 jam.""" per_amt, per_day, trips, notes, seen_file, skips = {}, {}, [], [], {}, [] for f in sorted(glob.glob(os.path.join(folder, '*.xlsx'))): base = os.path.basename(f); lid = loc_id(base) if not lid: notes.append(f'{base}: lokasi tidak dikenali, dilewati'); continue rows, skip = read_rjk_file(f) if not rows: notes.append(f'{base}: tidak ada baris data'); continue # file kembar (isi persis sama dengan lokasi lain) tidak dimuat dua kali fp = hash(tuple(sorted((r['tgl'], r['nopol'], r['supir'], r['jam']) for r in rows[:400]))) if fp in seen_file: notes.append(f'{base}: isinya identik dengan {seen_file[fp]}, tidak dimuat') continue seen_file[fp] = base uniq, day_amt = set(), {} kept = 0 for r in rows: k = (r['tgl'], r['nopol'], r['supir']) if k in uniq: continue # sheet ganda (mis. Juli berisi salinan Juni) uniq.add(k); kept += 1 y, m, d = r['tgl'].year, r['tgl'].month, r['tgl'].date().isoformat() grp = jam_group(r['jam']) a = per_amt.setdefault((lid, y, m, r['supir']), dict(loc=lid, y=y, m=m, nama=r['supir'], nip=r['sid'], trip=0, jam=0, maxJam=0, g12=0, g8=0, g6=0, l6=0, km=0, vol=0, hari=set(), maxHari=0, hariG12=0, anomali=0)) a['trip'] += 1; a['jam'] += r['jam']; a['maxJam'] = max(a['maxJam'], r['jam']); a[grp] += 1 a['km'] += r['km'] or 0; a['vol'] += r['vol'] or 0; a['hari'].add(d) if r['jam'] > cap: a['anomali'] += 1 p = per_day.setdefault((lid, d), dict(loc=lid, tgl=d, y=y, m=m, trip=0, jam=0, g12=0, g8=0, g6=0, l6=0, amt=set(), aG12=0, aG8=0, aG6=0, aL6=0, mulaiTs=None, akhirTs=None, opsTrip=0, opsLewat=0)) # jam operasional hanya dihitung dari ritase yang waktu kembalinya lengkap dan wajar; # ritase tanpa waktu kembali atau yang belum ditutup (> cap jam) tidak ikut dihitung mulai_ts = r['tgl'] if isinstance(r['kembali'], dt.datetime) and mulai_ts < r['kembali'] and r['jam'] <= cap: p['mulaiTs'] = mulai_ts if p['mulaiTs'] is None else min(p['mulaiTs'], mulai_ts) p['akhirTs'] = r['kembali'] if p['akhirTs'] is None else max(p['akhirTs'], r['kembali']) p['opsTrip'] += 1 else: p['opsLewat'] += 1 p['trip'] += 1; p['jam'] += r['jam']; p[grp] += 1; p['amt'].add(r['supir']) day_amt[(d, r['supir'])] = day_amt.get((d, r['supir']), 0) + r['jam'] if r['jam'] > 12: trips.append(dict(loc=lid, y=y, m=m, tgl=d, jamMulai=r['tgl'].strftime('%H:%M'), kembali=r['kembali'].strftime('%H:%M') if isinstance(r['kembali'], dt.datetime) else None, jam=round(r['jam'], 2), nopol=r['nopol'], supir=r['supir'], kernet=r['kernet'], km=r['km'], vol=r['vol'], ket=r['ket'], anomali='Ya' if r['jam'] > cap else None)) # akumulasi jam kerja per AMT per hari (satu AMT bisa beberapa trip sehari) for (d, supir), j in day_amt.items(): p, g = per_day[(lid, d)], jam_group(j) p['aG12' if g == 'g12' else 'aG8' if g == 'g8' else 'aG6' if g == 'g6' else 'aL6'] += 1 key = (lid, int(d[:4]), int(d[5:7]), supir) if key in per_amt: per_amt[key]['maxHari'] = max(per_amt[key]['maxHari'], j) if j > 12: per_amt[key]['hariG12'] += 1 for (y, m), n in skip.items(): skips.append(dict(loc=lid, y=y, m=m, n=n)) notes.append(f'{base}: {kept} trip dimuat' + (f', {len(rows) - kept} baris ganda dilewati' if kept < len(rows) else '') + (f', {sum(skip.values())} baris tanpa waktu kembali' if skip else '')) amt = [] for a in per_amt.values(): a['hari'] = len(a['hari']); a['jam'] = round(a['jam'], 1); a['maxJam'] = round(a['maxJam'], 2) a['maxHari'] = round(a['maxHari'], 2); a['km'] = round(a['km'] or 0, 1); a['vol'] = round(a['vol'] or 0, 1) a['rata'] = round(a['jam'] / a['trip'], 2) if a['trip'] else None amt.append(a) day = [] for p in per_day.values(): p['amt'] = len(p['amt']); p['jam'] = round(p['jam'], 1) a, b = p.pop('mulaiTs'), p.pop('akhirTs') p['mulai'] = a.hour * 60 + a.minute if a else None p['selesai'] = b.hour * 60 + b.minute if b else None # terminal yang beroperasi melewati tengah malam dibatasi 24 jam sehari p['durasi'] = min(round((b - a).total_seconds() / 3600, 2), 24) if a else None day.append(p) return (sorted(amt, key=lambda r: (r['loc'], r['y'], r['m'], r['nama'])), sorted(day, key=lambda r: (r['loc'], r['tgl'])), trips, skips, notes) # ===================================== 9. Data MT pusat (master mobil tangki) === MT_HEAD = { 'lokasi ft/it': 'loc', 'nopol': 'nopol', 'pemilik mobil tangki': 'pemilik', 'kapasitas': 'kap', 'satuan': 'satuan', 'status operasi mt': 'status', 'jenis sewa': 'sewa', 'perusahaan pengelola angkutan': 'pengelola', 'customer reguler/lembaga penyalur': 'customer', 'tgl pembuatan head truck': 'tglHT', 'merk head truck': 'merk', 'type seri head truck': 'type', 'no mesin': 'noMesin', 'no rangka': 'noRangka', 'euro 4 keatas': 'euro', 'merk tangki': 'builder', 'material tangki': 'material', 'tgl pembuatan tangki': 'tglTangki', 'sistem pengereman': 'rem', 'dispensing': 'dispensing', 'konfigurasi kompartemen': 'kompartemen', 'konfigurasi sumbu roda': 'sumbu', 'over dimension over loading': 'odol', 'keterangan': 'ket', 'region': 'region', } def head_key(h): """Judul kolom dicocokkan dari awal teks, sehingga catatan format di belakangnya diabaikan.""" t = re.sub(r'\s+', ' ', str(h or '')).strip().lower() t = re.split(r'\s*[\(\[]', t)[0].strip().rstrip(':') if not t: return None if t in MT_HEAD: return MT_HEAD[t] cocok = [k for k in MT_HEAD if t.startswith(k)] return MT_HEAD[max(cocok, key=len)] if cocok else None TITLE_FIX = {'mercedez bennz': 'Mercedes Benz', 'mercedez benz': 'Mercedes Benz', 'ud truck': 'UD Truck', 'faw': 'FAW', 'hino': 'HINO', 'isuzu': 'ISUZU', 'nissan': 'NISSAN', 'mitsubishi': 'MITSUBISHI', 'meco inox': 'Meco Inox', 'aweco': 'Aweco', 'geluran': 'Geluran', 'meco': 'Meco', 'alluminium alloy': 'Aluminium Alloy', 'alumunium alloy': 'Aluminium Alloy', 'aluminium aloy': 'Aluminium Alloy', 'alumuniun alloy': 'Aluminium Alloy', 'aluminium alloy': 'Aluminium Alloy', 'mild steel': 'Mild Steel', 'carbon steel': 'Carbon Steel', 'tidak operasi': 'Tidak operasi', 'operasi': 'Operasi', 'ya': 'Ya', 'tidak': 'Tidak', 'fab': 'FAB', 'aoh': 'AOH', 'all in': 'All In', 'spbu': 'SPBU'} def tidy(v): s = txt(v) if not s: return None return TITLE_FIX.get(s.lower(), s if s.isupper() and len(s) <= 5 else s.title() if s.isupper() else s) def etl_mt_master(path): """Data MT pusat: satu baris per nopol dengan spesifikasi lengkap.""" wb = open_wb(path); out = [] for ws in wb.worksheets: ix, seen = None, set() for r in ws.iter_rows(values_only=True): if ix is None: if r and any(str(c or '').strip().upper() == 'NOPOL' for c in r): ix = {} for j, h in enumerate(r): k = head_key(h) if k and k not in ix: ix[k] = j continue g = lambda k: r[ix[k]] if k in ix and ix[k] < len(r) else None nopol = txt(g('nopol')) if not nopol or not PLATE.match(nopol.upper().replace(' ', '')): continue if txt(g('loc')) and str(g('loc')).strip().lower() == 'ft medan': continue # baris contoh nopol = nopol.upper().replace(' ', '') if nopol in seen: continue seen.add(nopol) out.append(dict( loc=loc_id(g('loc')), nopol=nopol, pemilik=tidy(g('pemilik')), kap=num(g('kap')), satuan=txt(g('satuan')), status=tidy(g('status')), sewa=tidy(g('sewa')), pengelola=tidy(g('pengelola')), customer=tidy(g('customer')), tglHT=to_date(g('tglHT')), merk=tidy(g('merk')), type=txt(g('type')), noMesin=txt(g('noMesin')), noRangka=txt(g('noRangka')), euro=tidy(g('euro')), builder=tidy(g('builder')), material=tidy(g('material')), tglTangki=to_date(g('tglTangki')), rem=tidy(g('rem')), dispensing=tidy(g('dispensing')), kompartemen=txt(g('kompartemen')), sumbu=txt(g('sumbu')), odol=tidy(g('odol')), ket=txt(g('ket')))) return [r for r in out if r['loc']] # ============================= 10. Input per lokasi dari aplikasi web === KEYFIELD = dict(fleet='nopol', briefing='nama', b3='nama', apar='amt', _apar_lama='amt', kedap='nopol', tagihan='periode', rjk='tgl', mt='nopol') def etl_inputs(folder): """Membaca file Excel hasil 'Unduh Excel' dari menu Input Data (punya sheet _meta).""" out = [] if not folder: return out for f in sorted(glob.glob(os.path.join(folder, '*.xlsx'))): wb = open_wb(f) if '_meta' not in wb.sheetnames: print(f' lewati {os.path.basename(f)}: bukan file dari menu Input Data'); continue meta = list(wb['_meta'].iter_rows(values_only=True)) head = [str(h or '').strip() for h in meta[0]] for mr in meta[1:]: m = dict(zip(head, mr)) if not m.get('sheet') or m['sheet'] not in wb.sheetnames or m.get('modul') not in KEYFIELD: continue keys = str(m.get('kolom') or '').split(','); start = int(m.get('baris_data') or 9) kf, rows = KEYFIELD[m['modul']], [] for r in wb[m['sheet']].iter_rows(min_row=start, values_only=True): rec = {} for k, v in zip(keys, r): if not k or k[0] in '_=': continue # kolom tetap / kolom hitungan if isinstance(v, dt.datetime): v = v.date().isoformat() elif isinstance(v, str): v = v.strip() or None if v == 'N/A': v = None if k == 'nik' and v is not None: v = str(int(v)) if isinstance(v, (int, float)) else str(v) v = '•' * 8 + v[-4:] if len(v) > 4 else v # NIK disamarkan saat dipublikasikan rec[k] = v if rec.get(kf) in (None, ''): continue rows.append(rec) ival = lambda k: int(m[k]) if isinstance(m.get(k), (int, float)) or str(m.get(k) or '').isdigit() else None out.append(dict(mod=m['modul'], loc=m['lokasi'], y=ival('tahun'), m=ival('bulan'), sem=ival('semester'), by=txt(m.get('disimpan_oleh')), at=txt(m.get('disimpan_pada')), rows=rows, src=os.path.basename(f))) print(f' input {m["modul"]:9s} {m["lokasi"]:5s} {m.get("tahun") or "semua"}{("-" + str(m["bulan"])) if m.get("bulan") else (" S" + str(m["semester"])) if m.get("semester") else ""}: {len(rows)} baris ({os.path.basename(f)})') return out # ================================================================= main === def period_of(path): m = month_from_text(os.path.basename(path)); y = re.search(r'(20\d\d)', os.path.basename(path)) return (int(y.group(1)) if y else dt.date.today().year), m def main(): ap = argparse.ArgumentParser() ap.add_argument('--input', default='.'); ap.add_argument('--template', default='template.html') ap.add_argument('--background', required=True); ap.add_argument('--output', default='Fleet_Safety_JBT.html') ap.add_argument('--users', default=None, help='JSON daftar user (opsional)') ap.add_argument('--input-lokasi', default=None, help='Folder berisi Excel hasil menu Input Data (opsional)') ap.add_argument('--rjk', default=None, help='Folder berisi file RJK_AMT_.xlsx (jam kerja AMT)') ap.add_argument('--tugas', default=None, help='File tugas.json hasil "Unduh file penugasan" (opsional)') ap.add_argument('--mt', default=None, help='File Data MT pusat (default: dicari di folder --input dengan kata kunci data_mt)') a = ap.parse_args() f = lambda *k: find(a.input, *k) or sys.exit(f'File tidak ditemukan: {k}') p_rekap, p_unit = f('rekapitulasi', 'fleet'), f('program_fleet_safety') p_b3, p_sb = f('amt_monitoring_b3'), f('safety_briefing') p_apar, p_kedap, p_tag = f('pelatihan_apar'), f('kekedapan'), f('tagihan') y_rekap, _ = period_of(p_rekap) fs_loc, fs_region = etl_rekap(p_rekap, y_rekap) y, m = period_of(p_unit); fs_unit = etl_fleet_units(p_unit, y, m) y, m = period_of(p_b3); b3 = etl_b3(p_b3, y, m) y, m = period_of(p_sb); sb_loc, sb_amt = etl_briefing(p_sb, y, m) apar = etl_apar(p_apar) kedap, kedap_rekap = etl_kedap(p_kedap) tagihan = etl_tagihan(p_tag) inputs = etl_inputs(a.input_lokasi) rjk_amt, rjk_day, rjk_trip, rjk_skip, rjk_notes = etl_rjk(a.rjk) if a.rjk else ([], [], [], [], []) p_mt = a.mt or find(a.input, 'data_mt') mt_master = etl_mt_master(p_mt) if p_mt else [] if mt_master: print(f' mt {len(mt_master)} mobil tangki dimuat dari {os.path.basename(p_mt)}') tasks = json.load(open(a.tugas, encoding='utf-8')) if a.tugas else [] if tasks: print(f' tugas {len(tasks)} penugasan dimuat dari {os.path.basename(a.tugas)}') for n in rjk_notes: print(' rjk', n) data = dict( meta=dict(generated=dt.datetime.now().strftime('%Y-%m-%d %H:%M'), sources=[os.path.basename(p) for p in (p_rekap, p_unit, p_b3, p_sb, p_apar, p_kedap, p_tag)] + ([os.path.basename(p_mt)] if mt_master else []) + (['RJK_AMT_.xlsx'] if rjk_day else [])), locs=[dict(id=i, name=n) for i, n in LOCS], fsLoc=pack(['loc','y','m','mt','fab','greasing','pushrod','drain','apar','afff','aoh','aohCek','backing'], fs_loc), fsRegion=pack(['region','y','m','mt','fab','greasing','drain','apar','afff','backing'], fs_region), fsUnit=pack(['loc','y','m','status','nopol','pemilik','pola','kap','jenis','builder','thnTangki','merk','thnHT','type','rem','odo', 'tglGreasing','tglPushrod','mur','tglAoh','rembesan','levelRem','gelembung','drain','drainSusp','drainPneu', 'apar','afff','backing','tl','ket','evidence'], fs_unit), b3=pack(['loc','y','m','nama','nik','pt','jabatan','produk','pola','status','lembaga','tgl','noSert','link','ket'], b3), sbLoc=pack(['loc','y','m','amt','jadwal','hadir','pct','grade','A','B','C','D','ket'], sb_loc), sbAmt=pack(['loc','y','m','nama','idSiod','jabatan','hadir','jadwal','pct','grade'], sb_amt), apar=pack(['loc','y','sem','amt','sudah','update'], apar), kedap=pack(['loc','nopol','pemilik','pola','kap','jenis','builder','thn','tglUji','c1','c2','c3','c4','c5','c6','jadwal','tera','ket','status','cek'], kedap), kedapRekap=pack(['loc','kat','asof','mt','cek','kedap','tdk','perbaikan'], kedap_rekap), tagihan=pack(['loc','y','m','periode','half','kl','klkm','nilai','dendaJam','lo','dendaKpi','dendaLosses','kontaminasi','laka','denda', 'pr','reqPo','po','tglPo','sa','tglSa','invoice','tglInv','nominal','ppn','total','pa','tglPa','ket','status'], tagihan), inputs=inputs, rjkAmt=pack(['loc','y','m','nama','nip','trip','jam','rata','maxJam','g12','g8','g6','l6','hari','maxHari','hariG12','km','vol','anomali'], rjk_amt), rjkDay=pack(['loc','tgl','y','m','trip','jam','g12','g8','g6','l6','amt','aG12','aG8','aG6','aL6','mulai','selesai','durasi','opsTrip','opsLewat'], rjk_day), rjkTrip=pack(['loc','y','m','tgl','jamMulai','kembali','jam','nopol','supir','kernet','km','vol','ket','anomali'], rjk_trip), rjkSkip=pack(['loc','y','m','n'], rjk_skip), rjkNotes=rjk_notes, tasks=tasks, mtMaster=pack(['loc','nopol','pemilik','kap','satuan','status','sewa','pengelola','customer','tglHT','merk','type', 'noMesin','noRangka','euro','builder','material','tglTangki','rem','dispensing','kompartemen','sumbu','odol','ket'], mt_master), ) # background login -> JPEG terkompresi (base64) agar file tetap ringan from PIL import Image im = Image.open(a.background).convert('RGB') if im.width > 1920: im = im.resize((1920, round(im.height * 1920 / im.width)), Image.LANCZOS) buf = io.BytesIO(); im.save(buf, 'JPEG', quality=82, optimize=True, progressive=True) bg = 'data:image/jpeg;base64,' + base64.b64encode(buf.getvalue()).decode() # logo untuk header dashboard: potongan kiri-atas gambar yang sama sx = im.width / 1672 logo = im.crop((int(40*sx), int(28*sx), int(360*sx), int(128*sx))).convert('RGBA') px = logo.load() # latar putih -> transparan for yy in range(logo.height): for xx in range(logo.width): r, g, b, _ = px[xx, yy]; w = min(r, g, b) px[xx, yy] = (r, g, b, max(0, min(255, int((245 - w) * 255 / 55)))) buf = io.BytesIO(); logo.save(buf, 'PNG', optimize=True) logo_uri = 'data:image/png;base64,' + base64.b64encode(buf.getvalue()).decode() users = json.load(open(a.users, encoding='utf-8')) if a.users else DEFAULT_USERS users = [build_user(x) for x in users] html = open(a.template, encoding='utf-8').read() html = html.replace('"__DATA__"', json.dumps(data, ensure_ascii=False, separators=(',', ':'))) html = html.replace('__BG_URI__', bg).replace('__LOGO_URI__', logo_uri) html = html.replace('"__USERS__"', json.dumps(users)) open(a.output, 'w', encoding='utf-8').write(html) print(f'OK -> {a.output} ({os.path.getsize(a.output)/1e6:.2f} MB)') for k in ('fsLoc','fsRegion','fsUnit','b3','sbLoc','sbAmt','apar','kedap','kedapRekap','tagihan','rjkAmt','rjkDay','rjkTrip','mtMaster'): print(f' {k:11s} {len(data[k]["r"]):5d} baris') if __name__ == '__main__': main()