Spaces:
Sleeping
Sleeping
Download data_source.py from procurement-tools/auto-form: direct link, hf CLI and curl.
- Browser
- Download file 5.93 kB
-
https://huggingface.co/spaces/procurement-tools/auto-form/resolve/main/data_source.py
- Command line
-
hf download hf://spaces/procurement-tools/auto-form/data_source.py
-
curl -L -o data_source.py https://huggingface.co/spaces/procurement-tools/auto-form/resolve/main/data_source.py
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() | |
| 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() | |