auto-form / data_source.py
Menixis's picture
Claude Fable 5
refactor: move PO data access (load_live_data, lookup_po_in_bytes) into data_source.py repository module; add check_data() health probe
1ea4202
Raw History Blame Contribute Delete
5.93 kB
# -*- coding: utf-8 -*-
"""
data_source — repository layer: all PO-data access lives here.
- load_live_data(url) : fetch the live Excel from OneDrive (1-h st.cache_data).
- lookup_po_in_bytes(...) : find a PO row in the Excel bytes (pure openpyxl).
- check_data() : health probe — exercises the exact data path the app uses.
The UI (streamlit_app.py) never touches the data source directly; it goes
through this module only.
"""
import os
from io import BytesIO
from datetime import datetime, date
import streamlit as st
import openpyxl
from openpyxl.utils.datetime import from_excel
# คอลัมน์วันที่จาก Power BI export ที่บางครั้งส่งออกมาเป็นเลข serial ของ Excel
# (เช่น 46153) แทน datetime — ต้องแปลงกลับเป็นวันที่ตอนอ่าน
DATE_COLUMNS = {"poCreateDate"}
# =============================================================================
# Config: live data source — OneDrive "anyone with the link" (ตั้งใน Space secret DATA_URL)
# =============================================================================
DATA_URL = os.environ.get("DATA_URL", "").strip()
@st.cache_data(ttl=3600, show_spinner=False)
def load_live_data(url):
"""ดึงไฟล์ Excel ล่าสุดจาก OneDrive ผ่าน anonymous link (เติม download=1) — cache 1 ชม.
คืนค่า (bytes, เวลาที่ดึง)"""
import requests
if not url:
raise RuntimeError("ยังไม่ได้ตั้งค่า DATA_URL (Space secret)")
dl = url if "download=1" in url else url + ("&" if "?" in url else "?") + "download=1"
r = requests.get(dl, headers={"User-Agent": "Mozilla/5.0"}, timeout=120,
allow_redirects=True)
r.raise_for_status()
if r.content[:2] != b"PK":
raise RuntimeError("ไม่ได้ไฟล์ Excel (ลิงก์อาจต้อง login หรือไม่ใช่ anonymous)")
return r.content, datetime.now()
def check_data(url=None):
"""Health probe: (ok, detail). Runs the same load_live_data path (incl. cache)
the app itself uses, so a green result proves the real data source works."""
try:
b, fetched = load_live_data(DATA_URL if url is None else url)
return True, f"{len(b)} bytes, fetched {fetched:%Y-%m-%d %H:%M}"
except Exception as e: # noqa: BLE001
return False, str(e)
# =============================================================================
# Helper: lookup PO ใน Excel bytes
# =============================================================================
def _strip_col_wrapper(name):
"""'Data for Auto Form[poNo]' -> 'poNo' (header แบบ Power BI export)"""
if name is None:
return None
s = str(name).strip()
if "[" in s and s.endswith("]"):
s = s.split("[", 1)[1][:-1].strip()
return s
def lookup_po_in_bytes(po_number, file_bytes, sheet_name=None, po_column="poNo"):
wb = openpyxl.load_workbook(BytesIO(file_bytes), data_only=True, read_only=True)
try:
if sheet_name and sheet_name in wb.sheetnames:
ws = wb[sheet_name]
else:
ws = wb.active
rows_iter = ws.iter_rows(values_only=True)
# หา header row อัตโนมัติ: รองรับไฟล์ export ตรงจาก Power BI ที่มีแถว banner/ว่าง
# ด้านบน และ header แบบ 'Data for Auto Form[poNo]' — สแกน 15 แถวแรก
headers = None
po_col_idx = None
for _ in range(15):
try:
raw = next(rows_iter)
except StopIteration:
break
if not raw:
continue
stripped = [_strip_col_wrapper(c) for c in raw]
if po_column in stripped:
headers = stripped
po_col_idx = stripped.index(po_column)
break
if headers is None:
return {"__error__": f"ไม่พบคอลัมน์ '{po_column}' ในไฟล์ (sheet '{ws.title}')"}, []
available_cols = [h for h in headers if h]
po_str = str(po_number).strip()
for row in rows_iter:
if po_col_idx >= len(row):
continue
cell = row[po_col_idx]
if cell is None:
continue
if isinstance(cell, float) and cell.is_integer():
cell_str = str(int(cell))
else:
cell_str = str(cell).strip()
if cell_str == po_str:
result = {}
for i in range(min(len(headers), len(row))):
if headers[i]:
val = row[i]
# date cols จาก Power BI อาจมาเป็นเลข serial ของ Excel — แปลงก่อน
if headers[i] in DATE_COLUMNS and isinstance(val, (int, float)) \
and not isinstance(val, bool):
try:
val = from_excel(val)
except (ValueError, OverflowError):
pass
if isinstance(val, datetime):
val = val.strftime("%Y-%m-%d") if not (val.hour or val.minute) \
else val.strftime("%Y-%m-%d %H:%M")
elif isinstance(val, date):
val = val.strftime("%Y-%m-%d")
result[headers[i]] = val
return result, available_cols
return None, available_cols
finally:
wb.close()