""" SAP Material Readiness export importer. Parses the tab-separated SAP export (Project + MO + Routing + Material per MO all flattened into one file) and syncs it into our local master tables using upsert-by-natural-key so re-importing the same or a refreshed export always updates existing records instead of creating duplicates. Design rules (important - read before changing): - Fields that come FROM SAP (project info, routing name, MO dates/status, material readiness numbers) are always refreshed on re-import. - Fields that are LOCAL to the shopfloor app (a workstation's assigned Team/ shift group, a MO's own operational status/ideal_time_minutes, active flags) are NEVER overwritten by an import - they're only set once, when a record is first created by an import, so the shop floor configuration already done by Leaders/Supervisors is never silently clobbered. """ import csv import hashlib import io import json from datetime import datetime import database as db REQUIRED_COLUMNS = [ "Project Code", "Project Name", "Start Date", "Finish Date", "MO Number", "MO Status", "Routing Code", "Routing Name", "ItemCode", "MaterialName", ] def _decode(raw_bytes): # A real UTF-16 file has a byte-order-mark - detect that explicitly and # trust it. Otherwise, try strict encodings (utf-8) BEFORE utf-16: utf-16 # decoding rarely raises an error even on plain UTF-8/ASCII bytes (it just # reinterprets byte-pairs), so trying it first can silently turn a normal # UTF-8 export into garbled text instead of failing loudly. if raw_bytes[:2] in (b"\xff\xfe", b"\xfe\xff"): return raw_bytes.decode("utf-16") for enc in ("utf-8-sig", "utf-8", "utf-16", "latin-1"): try: return raw_bytes.decode(enc) except (UnicodeDecodeError, UnicodeError): continue raise ValueError("Tidak bisa membaca encoding file. Pastikan ini export SAP asli (.txt).") def _num(s): """Parses Indonesian/European-formatted numbers: '.' thousands, ',' decimal.""" if s is None: return None s = s.strip() if not s: return None s = s.replace(".", "").replace(",", ".") try: return float(s) except ValueError: return None def _date(s): """Parses SAP's 'DD.MM.YY' into ISO 'YYYY-MM-DD'. Blank -> None.""" if not s: return None s = s.strip() if not s: return None for fmt in ("%d.%m.%y", "%d.%m.%Y"): try: return datetime.strptime(s, fmt).strftime("%Y-%m-%d") except ValueError: continue return s # keep the raw value rather than silently dropping it def _row_key(mo_number, item_code, po_number, latest_order_date): """Identifies one persistent material-requirement LINE (one item, one PO/ order tranche) for an MO. Deliberately excludes quantities/dates that are expected to change on every re-import (RequiredQty, PrevCumNeed, CurrCumNeed, PO_ETA, Status, OnHand, ...) - those are refresh-on-conflict fields, not identity fields. Including a changeable number here would make a routine SAP revision look like a brand-new line and leave the old, now-stale row behind as a duplicate instead of updating it in place.""" raw = "|".join(str(x or "") for x in (mo_number, item_code, po_number, latest_order_date)) return hashlib.sha1(raw.encode("utf-8")).hexdigest() def _guess_product_group(routing_name): """Best-effort guess of the product line a routing belongs to, e.g. 'Core Stacking LDT' -> 'LDT'. Only used for brand-new routings created by an import; existing routings' product_group is never touched here, so a Leader's manual grouping in the admin UI always takes precedence.""" if not routing_name: return None last_word = routing_name.strip().split()[-1] if 2 <= len(last_word) <= 6 and last_word.isalpha() and last_word.isupper(): return last_word return None def import_sap_export(conn, raw_bytes, filename=""): text = _decode(raw_bytes) reader = csv.reader(io.StringIO(text), delimiter="\t") try: header = next(reader) except StopIteration: raise ValueError("File kosong") header = [h.strip().lstrip("\ufeff") for h in header] idx = {h: i for i, h in enumerate(header)} missing = [c for c in REQUIRED_COLUMNS if c not in idx] if missing: raise ValueError(f"Kolom wajib tidak ditemukan di file: {', '.join(missing)}") def get(row, col): i = idx.get(col) if i is None or i >= len(row): return "" return row[i].strip() # Pre-fetch existing keys so we can tell new vs. updated accurately. existing_projects = {r["project_code"] for r in conn.execute("SELECT project_code FROM projects")} existing_routings = {r["id"] for r in conn.execute("SELECT id FROM routings")} existing_mos = {r["mo_number"] for r in conn.execute("SELECT mo_number FROM manufacturing_orders")} existing_materials = {r["row_key"] for r in conn.execute("SELECT row_key FROM mo_materials")} counts = { "rows_processed": 0, "rows_skipped": 0, "projects_new": 0, "projects_updated": 0, "routing_new": 0, "routing_updated": 0, "mo_new": 0, "mo_updated": 0, "materials_new": 0, "materials_updated": 0, } errors = [] now = db.now_str() touched_projects, touched_routings, touched_mos = set(), set(), set() for row_num, row in enumerate(reader, start=2): if not row or not any(row): continue mo_number = get(row, "MO Number") if not mo_number: counts["rows_skipped"] += 1 continue counts["rows_processed"] += 1 try: project_code = get(row, "Project Code") project_name = get(row, "Project Name") start_date = _date(get(row, "Start Date")) finish_date = _date(get(row, "Finish Date")) routing_code = get(row, "Routing Code") routing_name = get(row, "Routing Name") sap_status = get(row, "MO Status") trafo_id = get(row, "Trafo ID") sn = get(row, "SN") so_number = get(row, "SO_Number") required_date = _date(get(row, "Required Date")) planned_start_date = _date(get(row, "Planned Start Date")) planned_end_date = _date(get(row, "Planned End Date")) ship_date = _date(get(row, "Ship Date")) status_production = get(row, "Status Production") rec_start = _date(get(row, "Recommendation Start Date")) rec_end = _date(get(row, "Recommendation End Date")) item_code = get(row, "ItemCode") material_name = get(row, "MaterialName") # ---------------- Project (pure reference data: always refresh) ---------------- if project_code and project_code not in touched_projects: touched_projects.add(project_code) is_new = project_code not in existing_projects conn.execute( """INSERT INTO projects (project_code, project_name, start_date, finish_date, updated_at) VALUES (?,?,?,?,?) ON CONFLICT(project_code) DO UPDATE SET project_name=excluded.project_name, start_date=excluded.start_date, finish_date=excluded.finish_date, updated_at=excluded.updated_at""", (project_code, project_name, start_date, finish_date, now), ) existing_projects.add(project_code) counts["projects_new" if is_new else "projects_updated"] += 1 # ---------------- Routing (a process stage; name refreshed, local config kept) ---- # NOTE: SAP only knows the routing/stage - it has no idea which physical # workstation(s) on the shop floor that stage is actually performed at. # That link is configured locally by a Leader/Supervisor in Master Routing, # and is never touched here (same as product_group/sequence below). if routing_code and routing_code not in touched_routings: touched_routings.add(routing_code) is_new = routing_code not in existing_routings if is_new: conn.execute( """INSERT INTO routings (id, name, product_group, sequence, active) VALUES (?,?,?,0,1)""", (routing_code, routing_name, _guess_product_group(routing_name)), ) existing_routings.add(routing_code) counts["routing_new"] += 1 else: conn.execute("UPDATE routings SET name=? WHERE id=?", (routing_name, routing_code)) counts["routing_updated"] += 1 # ---------------- Manufacturing Order (SAP fields refreshed; local fields kept) --- if mo_number not in touched_mos: touched_mos.add(mo_number) is_new = mo_number not in existing_mos if is_new: default_desc = project_name if not trafo_id else f"{project_name} \u2014 Trafo {trafo_id}" default_status = "Completed" if sap_status == "CL" else "Active" conn.execute( """INSERT INTO manufacturing_orders (mo_number, description, routing_id, status, ideal_time_minutes, active, project_code, so_number, trafo_id, sn, sap_status, status_production, required_date, planned_start_date, planned_end_date, ship_date, recommendation_start_date, recommendation_end_date, sap_updated_at) VALUES (?,?,?,?,0,1, ?,?,?,?,?,?, ?,?,?,?, ?,?, ?)""", ( mo_number, default_desc, routing_code or None, default_status, project_code, so_number, trafo_id, sn, sap_status, status_production, required_date, planned_start_date, planned_end_date, ship_date, rec_start, rec_end, now, ), ) existing_mos.add(mo_number) counts["mo_new"] += 1 else: # Never touch: description, status (our own), ideal_time_minutes, active. # Auto-escalate to Completed if SAP says the MO is closed (never downgrade). if sap_status == "CL": conn.execute( "UPDATE manufacturing_orders SET status='Completed' WHERE mo_number=? AND status!='Completed'", (mo_number,), ) conn.execute( """UPDATE manufacturing_orders SET routing_id=COALESCE(?, routing_id), project_code=?, so_number=?, trafo_id=?, sn=?, sap_status=?, status_production=?, required_date=?, planned_start_date=?, planned_end_date=?, ship_date=?, recommendation_start_date=?, recommendation_end_date=?, sap_updated_at=? WHERE mo_number=?""", ( routing_code or None, project_code, so_number, trafo_id, sn, sap_status, status_production, required_date, planned_start_date, planned_end_date, ship_date, rec_start, rec_end, now, mo_number, ), ) counts["mo_updated"] += 1 # ---------------- Material readiness line (dedup by stable content hash) --------- if item_code: required_qty = _num(get(row, "RequiredQty")) issued_qty = _num(get(row, "IssuedQty")) remaining_need = _num(get(row, "RemainingNeed")) on_hand = _num(get(row, "OnHand")) is_commited = _num(get(row, "IsCommited")) available_stock = _num(get(row, "AvailableStock")) po_number = get(row, "PO Number") total_po_qty = _num(get(row, "TotalPOQty")) po_eta = _date(get(row, "PO_ETA")) prev_cum = _num(get(row, "PrevCumNeed")) curr_cum = _num(get(row, "CurrCumNeed")) mat_status = get(row, "Status") latest_order_date = _date(get(row, "LatestOrderDate")) row_key = _row_key(mo_number, item_code, po_number, latest_order_date) is_new = row_key not in existing_materials conn.execute( """INSERT INTO mo_materials (row_key, mo_number, item_code, material_name, specification, group_name, sub_group, procurement_method, lead_time, latest_order_date, warehouse, required_qty, issued_qty, remaining_need, on_hand, is_commited, available_stock, po_number, total_po_qty, po_eta, prev_cum_need, curr_cum_need, status, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(row_key) DO UPDATE SET material_name=excluded.material_name, specification=excluded.specification, group_name=excluded.group_name, sub_group=excluded.sub_group, procurement_method=excluded.procurement_method, lead_time=excluded.lead_time, warehouse=excluded.warehouse, required_qty=excluded.required_qty, issued_qty=excluded.issued_qty, remaining_need=excluded.remaining_need, on_hand=excluded.on_hand, is_commited=excluded.is_commited, available_stock=excluded.available_stock, total_po_qty=excluded.total_po_qty, po_eta=excluded.po_eta, prev_cum_need=excluded.prev_cum_need, curr_cum_need=excluded.curr_cum_need, status=excluded.status, updated_at=excluded.updated_at""", ( row_key, mo_number, item_code, material_name, get(row, "Specification"), get(row, "Group"), get(row, "Sub Group"), get(row, "ProcurementMethod"), get(row, "LeadTime"), latest_order_date, get(row, "Warehouse"), required_qty, issued_qty, remaining_need, on_hand, is_commited, available_stock, po_number, total_po_qty, po_eta, prev_cum, curr_cum, mat_status, now, ), ) existing_materials.add(row_key) counts["materials_new" if is_new else "materials_updated"] += 1 except Exception as e: # noqa: BLE001 - we want to keep going and report per-row issues counts["rows_skipped"] += 1 if len(errors) < 20: errors.append(f"Baris {row_num}: {e}") conn.commit() conn.execute( "INSERT INTO app_settings (key, value) VALUES ('last_sap_import', ?) " "ON CONFLICT(key) DO UPDATE SET value=excluded.value", ( json.dumps({ "filename": filename, "at": now, "counts": counts, "errors": errors, }), ), ) conn.commit() return {"ok": True, "counts": counts, "errors": errors}