Damir Radulić
f9ebcddf28
#1 JWT middleware extended: - Was: /api/admin/* only - Now: any POST/PUT/PATCH/DELETE under /api/* requires Bearer JWT - Whitelist (still anonymous): /api/auth/login, /refresh, /forgot-password, /password/reset, /reset-password, /setup-password, /google; /api/gdpr/consent; any path ending /avatar - 14 mutating endpoints verified to return 401 without token #2 Avatar upload demo mode (routers/clan_panel_router.py): - Anonymous → returns {demo_mode:true, slika_url:null, message:'Demo mode — slika nije spremljena. Prijavite se za pravu pohranu.'}, no FS write, no DB write - Authenticated (valid JWT, allowed role) → real save as before - Auth check now uses auth.auth_v2.decode_token (proper secret + revocation) instead of the broken local _resolve_role #3 Mock mailer (auth/mailer.py): - send_email writes RFC 822 .eml to /tmp/pgz_mailbox + appends to INDEX.jsonl - send_password_reset, send_invite helpers with HR text + HTML alt - Real SMTP active when PGZ_SMTP_HOST is set (env-driven, off by default) - forgot-password and admin invite both call mailer; audit logs mail status #5 Rate limiting on /api/auth/login: - Per-user: 5 wrong attempts → 5-minute DB-backed lockout (was 5 → 15 min). Configurable via PGZ_LOGIN_LOCK_THRESHOLD/MINUTES. - Per-IP: 10 fails / 5-min sliding window in-memory → HTTP 429 Configurable via PGZ_LOGIN_IP_THRESHOLD/WINDOW_SEC. Successful login clears the IP counter. - Failed attempts respond '(N/5) — račun je zaključan na 5 minuta' - New audit actions: login.ratelimit.ip; login.fail meta now includes fails count, locked, lock_minutes #4 Live test report: 46/46 across 6 demo users — login, JWT gate on 14 mutating endpoints, public path whitelist, demo-mode avatar + real save, forgot-password e-mail to mailbox, no-leak unknown email, 5-fail lockout, 423 during lockout, audit coverage.
1127 lines
49 KiB
Python
1127 lines
49 KiB
Python
#!/usr/bin/env python3
|
|
# erp/ocr.py — PGŽ Sport ERP OCR router (M5)
|
|
# Author: Damir Radulić <damir@rinet.one> / dradulic@outlook.com
|
|
# Date: 2026-05-04
|
|
# Description: /api/erp/ocr/upload + /parse — Tesseract OCR + DeepSeek V3 LLM extraction
|
|
# Persists into pgz_sport.invoice_uploads, then offers structured invoice parse.
|
|
|
|
from __future__ import annotations
|
|
|
|
import os
|
|
import re
|
|
import json
|
|
import hashlib
|
|
import subprocess
|
|
import tempfile
|
|
import traceback
|
|
from datetime import datetime, date
|
|
from pathlib import Path
|
|
from typing import Optional, List, Any
|
|
|
|
import psycopg2
|
|
import psycopg2.extras
|
|
import requests
|
|
from fastapi import APIRouter, UploadFile, File, Form, HTTPException, Header, Query, Body
|
|
from fastapi.responses import JSONResponse, FileResponse
|
|
|
|
try:
|
|
from erp.permissions import (
|
|
can_view_invoice, can_edit_invoice, can_pay_invoice, can_comment_invoice,
|
|
invoice_actions, audit_invoice, fetch_audit, is_pgz_admin,
|
|
)
|
|
except Exception:
|
|
# Fallback (always-allow) for unauth dev
|
|
def can_view_invoice(u, i): return True
|
|
def can_edit_invoice(u, i): return True
|
|
def can_pay_invoice(u, i): return True
|
|
def can_comment_invoice(u, i): return True
|
|
def invoice_actions(u, i): return {"view": True, "edit": True, "pay": True, "comment": True, "delete": False}
|
|
def audit_invoice(u, iid, op, field=None, old=None, new=None): pass
|
|
def fetch_audit(t, r, limit=50): return []
|
|
def is_pgz_admin(u): return False
|
|
|
|
try:
|
|
from auth.auth_v2 import get_current_user as _auth_user
|
|
except Exception:
|
|
_auth_user = None
|
|
|
|
router = APIRouter(prefix="/api/erp", tags=["erp-ocr"])
|
|
|
|
# === Config ===
|
|
DB = dict(host="10.10.0.2", port=6432, dbname="rinet_v3", user="rinet",
|
|
password="R1net2026!SecureDB#v7")
|
|
UPLOAD_DIR = Path("/opt/pgz-sport/_data/uploads/invoices")
|
|
UPLOAD_DIR.mkdir(parents=True, exist_ok=True)
|
|
|
|
DEEPSEEK_API_KEY = os.getenv("DEEPSEEK_API_KEY", "sk-33d29054d1ab4377b7d1a84bc0a423c7")
|
|
DEEPSEEK_URL = "https://api.deepseek.com/v1/chat/completions"
|
|
DEEPSEEK_MODEL = os.getenv("DEEPSEEK_MODEL", "deepseek-chat")
|
|
|
|
ALLOWED_EXT = {".pdf", ".jpg", ".jpeg", ".png", ".tif", ".tiff", ".webp"}
|
|
MAX_BYTES = 12 * 1024 * 1024 # 12 MB
|
|
|
|
ADMIN_TOKEN = "admin-pgz-2026"
|
|
|
|
|
|
def _db():
|
|
c = psycopg2.connect(**DB)
|
|
c.autocommit = True
|
|
return c
|
|
|
|
|
|
def _is_admin(authorization: Optional[str]) -> bool:
|
|
if not authorization:
|
|
return False
|
|
t = authorization.replace("Bearer ", "").strip()
|
|
return t == ADMIN_TOKEN
|
|
|
|
|
|
def _resolve_user(authorization: Optional[str]) -> Optional[dict]:
|
|
"""Resolve current user via auth_v2 JWT, fallback to admin token (returns synthetic pgz_admin)."""
|
|
if _auth_user:
|
|
try:
|
|
u = _auth_user(authorization)
|
|
if u: return u
|
|
except Exception:
|
|
pass
|
|
if _is_admin(authorization):
|
|
return {"id": 0, "email": "admin@token", "user_type": "pgz_admin",
|
|
"klub_id": None, "savez_id": None, "_synthetic": True}
|
|
return None
|
|
|
|
|
|
def _safe_filename(orig: str) -> str:
|
|
base = re.sub(r"[^A-Za-z0-9._-]+", "_", (orig or "upload").strip())[:120]
|
|
if not base:
|
|
base = "upload"
|
|
ts = datetime.now().strftime("%Y%m%d_%H%M%S")
|
|
return f"{ts}_{base}"
|
|
|
|
|
|
def _extract_text(path: Path) -> tuple[str, str]:
|
|
"""Return (text, method). Tries pdftotext first, falls back to tesseract."""
|
|
suf = path.suffix.lower()
|
|
if suf == ".pdf":
|
|
try:
|
|
r = subprocess.run(
|
|
["pdftotext", "-layout", "-q", str(path), "-"],
|
|
capture_output=True, timeout=45,
|
|
)
|
|
txt = r.stdout.decode("utf-8", "ignore")
|
|
if len(txt.strip()) > 80:
|
|
return txt, "pdftotext"
|
|
except Exception:
|
|
pass
|
|
# Rasterize + tesseract
|
|
try:
|
|
with tempfile.TemporaryDirectory(prefix="ocr_") as td:
|
|
subprocess.run(
|
|
["pdftoppm", "-r", "200", str(path), f"{td}/page"],
|
|
timeout=120, check=True,
|
|
)
|
|
chunks = []
|
|
for img in sorted(Path(td).glob("page-*.ppm"))[:5]:
|
|
r = subprocess.run(
|
|
["tesseract", str(img), "-", "-l", "hrv+eng", "--psm", "6"],
|
|
capture_output=True, timeout=90,
|
|
)
|
|
chunks.append(r.stdout.decode("utf-8", "ignore"))
|
|
return "\n".join(chunks), "tesseract"
|
|
except Exception as e:
|
|
return "", f"pdf_err:{e}"
|
|
if suf in {".jpg", ".jpeg", ".png", ".tif", ".tiff", ".webp"}:
|
|
try:
|
|
r = subprocess.run(
|
|
["tesseract", str(path), "-", "-l", "hrv+eng", "--psm", "6"],
|
|
capture_output=True, timeout=120,
|
|
)
|
|
return r.stdout.decode("utf-8", "ignore"), "tesseract"
|
|
except Exception as e:
|
|
return "", f"img_err:{e}"
|
|
return "", f"unsupported:{suf}"
|
|
|
|
|
|
# === HR invoice regex helpers ===
|
|
_OIB = re.compile(r"\b(\d{11})\b")
|
|
_IBAN = re.compile(r"\b(HR\d{19})\b")
|
|
_DATE_DOT = re.compile(r"\b(\d{1,2})[.\s\-/]+(\d{1,2})[.\s\-/]+(20\d{2})\b")
|
|
_DATE_ISO = re.compile(r"\b(20\d{2})[\-/](\d{1,2})[\-/](\d{1,2})\b")
|
|
_AMOUNT_TOTAL = re.compile(
|
|
r"(?i)(?:UKUPNO|TOTAL|SVEUKUPNO|ZA NAPLATU|ZA PLATITI|ZA UPLATU|IZNOS\s+UKUPNO)[\s:€]*([\d.\s]{1,12}[,.]\d{2})"
|
|
)
|
|
_AMOUNT_VAT = re.compile(r"(?i)(?:PDV|VAT)[\s:%]*?([\d.\s]{1,8}[,.]\d{2})")
|
|
_INVOICE_NO = re.compile(r"(?i)(?:ra[čc]un|invoice|broj|fakture|br\.)\s*[:#]?\s*([A-Z0-9\-/.]{3,30})")
|
|
|
|
|
|
def _parse_amount(s: str) -> Optional[float]:
|
|
if not s:
|
|
return None
|
|
s = s.replace(" ", "").replace("\xa0", "")
|
|
# Croatian style "1.234,56" → 1234.56
|
|
if "," in s and "." in s:
|
|
s = s.replace(".", "").replace(",", ".")
|
|
elif "," in s:
|
|
s = s.replace(",", ".")
|
|
try:
|
|
return float(s)
|
|
except Exception:
|
|
return None
|
|
|
|
|
|
def regex_extract(text: str) -> dict:
|
|
out: dict[str, Any] = {"raw_chars": len(text or "")}
|
|
if not text:
|
|
return out
|
|
oibs = list(dict.fromkeys(_OIB.findall(text)))
|
|
if oibs:
|
|
out["oibs_found"] = oibs
|
|
out["vendor_oib"] = oibs[0]
|
|
if len(oibs) > 1:
|
|
out["customer_oib"] = oibs[1]
|
|
|
|
m = _IBAN.search(text.replace(" ", ""))
|
|
if m:
|
|
out["iban"] = m.group(1)
|
|
|
|
m = _INVOICE_NO.search(text)
|
|
if m:
|
|
out["invoice_no"] = m.group(1).strip().rstrip(".,;")
|
|
|
|
for rx, order in [(_DATE_DOT, "dmy"), (_DATE_ISO, "ymd")]:
|
|
m = rx.search(text)
|
|
if m:
|
|
g = m.groups()
|
|
try:
|
|
if order == "dmy":
|
|
out["invoice_date"] = f"{g[2]}-{int(g[1]):02d}-{int(g[0]):02d}"
|
|
else:
|
|
out["invoice_date"] = f"{g[0]}-{int(g[1]):02d}-{int(g[2]):02d}"
|
|
# validate
|
|
date.fromisoformat(out["invoice_date"])
|
|
break
|
|
except Exception:
|
|
out.pop("invoice_date", None)
|
|
|
|
totals = [_parse_amount(x) for x in _AMOUNT_TOTAL.findall(text)]
|
|
totals = [t for t in totals if t and t > 0.01]
|
|
if totals:
|
|
out["amount_gross"] = max(totals)
|
|
out["amounts_found"] = totals[:6]
|
|
|
|
vats = [_parse_amount(x) for x in _AMOUNT_VAT.findall(text)]
|
|
vats = [v for v in vats if v and v > 0.01]
|
|
if vats:
|
|
# smallest plausible PDV (less than gross)
|
|
if "amount_gross" in out:
|
|
cand = [v for v in vats if v < out["amount_gross"]]
|
|
if cand:
|
|
out["amount_vat"] = max(cand)
|
|
else:
|
|
out["amount_vat"] = max(vats)
|
|
|
|
if "amount_gross" in out and "amount_vat" in out:
|
|
out["amount_net"] = round(out["amount_gross"] - out["amount_vat"], 2)
|
|
|
|
# Vendor name guess: first non-numeric, non-OIB line in header
|
|
for line in text.split("\n")[:12]:
|
|
ln = line.strip()
|
|
if 4 < len(ln) < 80 and not _OIB.search(ln) and not re.match(r"^[\d\s.,\-/€:]+$", ln):
|
|
out["vendor_name"] = ln
|
|
break
|
|
|
|
# Crude vendor guess for known HR sellers
|
|
upper = text.upper()
|
|
for keyword, label in [
|
|
("INA d.d.", "INA"), ("INA-MAZIVA", "INA"), ("TIFON", "TIFON"),
|
|
("PETROL", "PETROL"), ("HAC", "HAC"), ("BINA-ISTRA", "BINA-ISTRA"),
|
|
("HRVATSKE AUTOCESTE", "HAC"),
|
|
]:
|
|
if keyword in upper:
|
|
out.setdefault("vendor_brand", label)
|
|
break
|
|
|
|
return out
|
|
|
|
|
|
# === DeepSeek V3 LLM extraction ===
|
|
SYSTEM_PROMPT = (
|
|
"Ti si stručnjak za hrvatske račune (R-1, fiskalne, HUB-3). "
|
|
"Korisnik daje tekst računa izvučen OCR-om. Vrati ISKLJUČIVO valjani JSON, bez markdowna i komentara. "
|
|
"Ako neko polje nije sigurno - vrati null. Iznosi su brojevi (decimal s točkom). Datum je 'YYYY-MM-DD'."
|
|
)
|
|
|
|
LLM_SCHEMA_HINT = """{
|
|
"izdavatelj_naziv": str|null,
|
|
"izdavatelj_oib": str|null,
|
|
"izdavatelj_adresa": str|null,
|
|
"kupac_naziv": str|null,
|
|
"kupac_oib": str|null,
|
|
"datum": "YYYY-MM-DD"|null,
|
|
"broj_racuna": str|null,
|
|
"iznos_neto": float|null,
|
|
"iznos_pdv": float|null,
|
|
"iznos_brutto": float|null,
|
|
"stopa_pdv": float|null,
|
|
"valuta": "EUR"|"HRK"|null,
|
|
"nacin_placanja": str|null,
|
|
"IBAN": str|null,
|
|
"opis_svrhe": str|null,
|
|
"vrsta_troska": "gorivo"|"cestarina"|"hotel"|"restoran"|"oprema"|"ostalo"|null,
|
|
"stavke": [
|
|
{"opis": str, "kolicina": float, "jedinica": str, "cijena": float, "ukupno": float}
|
|
]
|
|
}"""
|
|
|
|
|
|
def deepseek_extract(text: str, hint: dict | None = None) -> dict:
|
|
"""Call DeepSeek chat completions for structured JSON extraction."""
|
|
if not DEEPSEEK_API_KEY:
|
|
return {"error": "no_api_key"}
|
|
if not text or len(text.strip()) < 20:
|
|
return {"error": "empty_text"}
|
|
|
|
user_msg = (
|
|
f"Iz teksta računa ispod izvuci polja po shemi:\n{LLM_SCHEMA_HINT}\n\n"
|
|
f"REGEX hint (može biti nepotpun ili netočan): {json.dumps(hint or {}, ensure_ascii=False)}\n\n"
|
|
f"--- TEKST RAČUNA ---\n{text[:8000]}\n--- KRAJ ---"
|
|
)
|
|
payload = {
|
|
"model": DEEPSEEK_MODEL,
|
|
"messages": [
|
|
{"role": "system", "content": SYSTEM_PROMPT},
|
|
{"role": "user", "content": user_msg},
|
|
],
|
|
"response_format": {"type": "json_object"},
|
|
"temperature": 0.0,
|
|
"max_tokens": 1200,
|
|
}
|
|
headers = {
|
|
"Authorization": f"Bearer {DEEPSEEK_API_KEY}",
|
|
"Content-Type": "application/json",
|
|
}
|
|
try:
|
|
r = requests.post(DEEPSEEK_URL, headers=headers, json=payload, timeout=60)
|
|
except Exception as e:
|
|
return {"error": f"net:{e}"}
|
|
if r.status_code != 200:
|
|
return {"error": f"http_{r.status_code}", "detail": r.text[:300]}
|
|
try:
|
|
body = r.json()
|
|
content = body["choices"][0]["message"]["content"]
|
|
return json.loads(content)
|
|
except Exception as e:
|
|
return {"error": f"parse:{e}", "raw": (r.text[:500] if r else "")}
|
|
|
|
|
|
# === Endpoints ===
|
|
|
|
@router.post("/ocr/upload")
|
|
async def ocr_upload(
|
|
file: UploadFile = File(...),
|
|
klub_id: Optional[int] = Form(None),
|
|
tenant_id: int = Form(1),
|
|
invoice_kind: str = Form("ostalo"),
|
|
authorization: Optional[str] = Header(None),
|
|
):
|
|
"""Upload an invoice file (PDF/image) → store on disk + insert pgz_sport.invoice_uploads."""
|
|
suffix = "." + (file.filename or "").rsplit(".", 1)[-1].lower()
|
|
if suffix not in ALLOWED_EXT:
|
|
raise HTTPException(400, f"Tip datoteke nije podržan: {suffix}. Dozvoljeno: {sorted(ALLOWED_EXT)}")
|
|
|
|
raw = await file.read()
|
|
if not raw:
|
|
raise HTTPException(400, "Prazna datoteka")
|
|
if len(raw) > MAX_BYTES:
|
|
raise HTTPException(400, f"Datoteka prevelika ({len(raw)} > {MAX_BYTES} bajtova)")
|
|
|
|
sha256 = hashlib.sha256(raw).hexdigest()
|
|
fname = _safe_filename(file.filename or "upload")
|
|
if not fname.endswith(suffix):
|
|
fname += suffix
|
|
path = UPLOAD_DIR / fname
|
|
path.write_bytes(raw)
|
|
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
"""
|
|
INSERT INTO pgz_sport.invoice_uploads
|
|
(klub_id, file_name, file_path, file_size, mime, sha256, ocr_status, meta)
|
|
VALUES (%s, %s, %s, %s, %s, %s, 'pending', %s)
|
|
RETURNING id, klub_id, file_name, ocr_status, uploaded_at
|
|
""",
|
|
(klub_id, file.filename, str(path), len(raw), file.content_type or "",
|
|
sha256, json.dumps({"tenant_id": tenant_id, "invoice_kind": invoice_kind})),
|
|
)
|
|
row = cur.fetchone()
|
|
return {"ok": True, "upload_id": row["id"], "file_name": row["file_name"],
|
|
"size": len(raw), "sha256": sha256, "status": row["ocr_status"]}
|
|
|
|
|
|
@router.post("/ocr/parse")
|
|
async def ocr_parse(
|
|
upload_id: Optional[int] = Form(None),
|
|
file: Optional[UploadFile] = File(None),
|
|
use_llm: bool = Form(True),
|
|
authorization: Optional[str] = Header(None),
|
|
):
|
|
"""Run OCR + (optional) DeepSeek LLM extraction.
|
|
Either pass upload_id (parse a previously uploaded file) or send file directly (one-shot)."""
|
|
tmp_to_clean: Optional[Path] = None
|
|
upload_row = None
|
|
try:
|
|
if upload_id:
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute("SELECT * FROM pgz_sport.invoice_uploads WHERE id=%s", (upload_id,))
|
|
upload_row = cur.fetchone()
|
|
if not upload_row:
|
|
raise HTTPException(404, f"Upload id={upload_id} ne postoji")
|
|
target = Path(upload_row["file_path"])
|
|
if not target.exists():
|
|
raise HTTPException(404, f"Datoteka ne postoji na disku: {target}")
|
|
elif file:
|
|
suffix = "." + (file.filename or "").rsplit(".", 1)[-1].lower()
|
|
if suffix not in ALLOWED_EXT:
|
|
raise HTTPException(400, f"Tip datoteke nije podržan: {suffix}")
|
|
raw = await file.read()
|
|
if not raw:
|
|
raise HTTPException(400, "Prazna datoteka")
|
|
tmp = tempfile.NamedTemporaryFile(prefix="parse_", suffix=suffix, delete=False)
|
|
tmp.write(raw); tmp.close()
|
|
target = Path(tmp.name)
|
|
tmp_to_clean = target
|
|
else:
|
|
raise HTTPException(400, "Treba poslati upload_id ILI file")
|
|
|
|
text, method = _extract_text(target)
|
|
if len(text.strip()) < 20:
|
|
return {"ok": False, "ocr_method": method, "raw_chars": len(text),
|
|
"error": "OCR nije uspio izvući dovoljno teksta"}
|
|
|
|
regex_fields = regex_extract(text)
|
|
regex_fields["ocr_method"] = method
|
|
|
|
llm_fields: dict = {}
|
|
if use_llm:
|
|
llm_fields = deepseek_extract(text, hint=regex_fields)
|
|
|
|
# Merge: LLM overrides regex when valid
|
|
merged = dict(regex_fields)
|
|
for k in ("izdavatelj_naziv", "izdavatelj_oib", "kupac_oib", "datum",
|
|
"broj_racuna", "iznos_neto", "iznos_pdv", "iznos_brutto",
|
|
"stopa_pdv", "valuta", "IBAN", "opis_svrhe", "vrsta_troska",
|
|
"izdavatelj_adresa", "nacin_placanja"):
|
|
v = llm_fields.get(k) if isinstance(llm_fields, dict) else None
|
|
if v not in (None, "", "null"):
|
|
merged[k] = v
|
|
|
|
# Normalize aliases for UI / DB
|
|
if "izdavatelj_naziv" in merged: merged.setdefault("vendor_name", merged["izdavatelj_naziv"])
|
|
if "izdavatelj_oib" in merged: merged.setdefault("vendor_oib", merged["izdavatelj_oib"])
|
|
if "izdavatelj_adresa" in merged: merged.setdefault("vendor_address", merged["izdavatelj_adresa"])
|
|
if "kupac_oib" in merged: merged.setdefault("customer_oib", merged["kupac_oib"])
|
|
if "datum" in merged: merged.setdefault("invoice_date", merged["datum"])
|
|
if "broj_racuna" in merged: merged.setdefault("invoice_no", merged["broj_racuna"])
|
|
if "iznos_brutto" in merged: merged.setdefault("amount_gross", merged["iznos_brutto"])
|
|
if "iznos_neto" in merged: merged.setdefault("amount_net", merged["iznos_neto"])
|
|
if "iznos_pdv" in merged: merged.setdefault("amount_vat", merged["iznos_pdv"])
|
|
if "stopa_pdv" in merged: merged.setdefault("vat_rate", merged["stopa_pdv"])
|
|
if "valuta" in merged: merged.setdefault("currency", merged["valuta"])
|
|
if "IBAN" in merged: merged.setdefault("iban", merged["IBAN"])
|
|
if "opis_svrhe" in merged: merged.setdefault("description", merged["opis_svrhe"])
|
|
if "vrsta_troska" in merged: merged.setdefault("category", merged["vrsta_troska"])
|
|
|
|
# Persist back to invoice_uploads when we have upload_row
|
|
if upload_row:
|
|
try:
|
|
with _db() as c:
|
|
c.cursor().execute(
|
|
"""UPDATE pgz_sport.invoice_uploads
|
|
SET ocr_status='done', processed_at=NOW(),
|
|
ocr_engine=%s, ocr_text=%s,
|
|
ai_invoice_no=%s, ai_invoice_date=%s,
|
|
ai_vendor_name=%s, ai_vendor_oib=%s,
|
|
ai_amount_gross=%s, ai_currency=%s, ai_iban=%s,
|
|
ai_extracted=%s, ai_engine=%s
|
|
WHERE id=%s""",
|
|
(
|
|
method, text[:50000],
|
|
merged.get("invoice_no"),
|
|
merged.get("invoice_date") if isinstance(merged.get("invoice_date"), str) else None,
|
|
merged.get("vendor_name"),
|
|
merged.get("vendor_oib"),
|
|
merged.get("amount_gross"),
|
|
merged.get("currency", "EUR"),
|
|
merged.get("iban"),
|
|
json.dumps({"regex": regex_fields, "llm": llm_fields, "merged": merged},
|
|
ensure_ascii=False, default=str),
|
|
("deepseek-v3" if use_llm and "error" not in (llm_fields or {}) else "regex"),
|
|
upload_row["id"],
|
|
),
|
|
)
|
|
except Exception as e:
|
|
merged["_persist_warn"] = str(e)[:200]
|
|
|
|
return {
|
|
"ok": True,
|
|
"upload_id": (upload_row["id"] if upload_row else None),
|
|
"ocr_method": method,
|
|
"raw_chars": len(text),
|
|
"regex": regex_fields,
|
|
"llm": llm_fields,
|
|
"extracted": merged,
|
|
"raw_text_preview": text[:1500],
|
|
}
|
|
finally:
|
|
if tmp_to_clean and tmp_to_clean.exists():
|
|
try:
|
|
tmp_to_clean.unlink()
|
|
except Exception:
|
|
pass
|
|
|
|
|
|
# === Invoices CRUD (M5) ===
|
|
|
|
@router.get("/invoices")
|
|
def invoices_list(
|
|
tenant_id: Optional[int] = Query(None),
|
|
klub_id: Optional[int] = Query(None),
|
|
status: Optional[str] = Query(None),
|
|
kind: Optional[str] = Query(None),
|
|
limit: int = Query(100, le=500),
|
|
offset: int = Query(0),
|
|
):
|
|
sql = """SELECT i.id, i.klub_id, k.naziv AS klub_naziv,
|
|
i.invoice_kind, i.invoice_no, i.internal_no,
|
|
i.vendor_name, i.vendor_oib, i.customer_name, i.customer_oib,
|
|
i.invoice_date, i.due_date, i.paid_date, i.currency,
|
|
i.amount_net, i.amount_vat, i.amount_gross, i.vat_rate,
|
|
i.payment_status, i.payment_method, i.iban_to,
|
|
i.description, i.category, i.tenant_id,
|
|
i.created_at, i.approved_at
|
|
FROM pgz_sport.invoices i
|
|
LEFT JOIN pgz_sport.klubovi k ON k.id = i.klub_id
|
|
WHERE 1=1"""
|
|
args: list = []
|
|
if tenant_id is not None:
|
|
sql += " AND i.tenant_id=%s"; args.append(tenant_id)
|
|
if klub_id is not None:
|
|
sql += " AND i.klub_id=%s"; args.append(klub_id)
|
|
if status:
|
|
sql += " AND i.payment_status=%s"; args.append(status)
|
|
if kind:
|
|
sql += " AND i.invoice_kind=%s"; args.append(kind)
|
|
sql += " ORDER BY i.invoice_date DESC NULLS LAST, i.id DESC LIMIT %s OFFSET %s"
|
|
args += [limit, offset]
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(sql, args)
|
|
rows = cur.fetchall()
|
|
return {"ok": True, "rows": rows, "count": len(rows)}
|
|
|
|
|
|
@router.get("/invoices/{invoice_id}")
|
|
def invoices_get(invoice_id: int, authorization: Optional[str] = Header(None)):
|
|
user = _resolve_user(authorization)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
"""SELECT i.*, k.naziv AS klub_naziv, k.savez_id
|
|
FROM pgz_sport.invoices i
|
|
LEFT JOIN pgz_sport.klubovi k ON k.id = i.klub_id
|
|
WHERE i.id=%s""", (invoice_id,))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
raise HTTPException(404, "Račun ne postoji")
|
|
if user and not can_view_invoice(user, row):
|
|
raise HTTPException(403, "Nemate ovlasti vidjeti ovaj račun")
|
|
cur.execute("SELECT * FROM pgz_sport.invoice_lines WHERE invoice_id=%s ORDER BY line_no, id",
|
|
(invoice_id,))
|
|
lines = cur.fetchall()
|
|
cur.execute(
|
|
"""SELECT id, file_name, file_size, mime, sha256, ocr_status, ocr_engine,
|
|
ai_extracted, uploaded_at, processed_at
|
|
FROM pgz_sport.invoice_uploads WHERE invoice_id=%s
|
|
ORDER BY uploaded_at DESC""", (invoice_id,))
|
|
uploads = cur.fetchall()
|
|
cur.execute(
|
|
"""SELECT id, payment_date, amount, currency, payment_method, iban_from,
|
|
iban_to, reference, bank_transaction_id, matched_status, created_at
|
|
FROM pgz_sport.payments WHERE invoice_id=%s ORDER BY payment_date DESC""",
|
|
(invoice_id,))
|
|
payments = cur.fetchall()
|
|
audit = fetch_audit("pgz_sport.invoices", invoice_id, 50)
|
|
actions = invoice_actions(user, row) if user else {"view": True, "edit": False, "pay": False, "comment": False, "delete": False}
|
|
return {"ok": True, "invoice": row, "lines": lines, "uploads": uploads,
|
|
"payments": payments, "audit": audit, "actions": actions}
|
|
|
|
|
|
@router.get("/invoices/{invoice_id}/file")
|
|
def invoices_file(invoice_id: int, authorization: Optional[str] = Header(None)):
|
|
"""Streamira originalnu datoteku skena/računa (slika ili PDF)."""
|
|
user = _resolve_user(authorization)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute("SELECT i.id, i.klub_id FROM pgz_sport.invoices i WHERE i.id=%s", (invoice_id,))
|
|
inv = cur.fetchone()
|
|
if not inv:
|
|
raise HTTPException(404, "Račun ne postoji")
|
|
if user and not can_view_invoice(user, inv):
|
|
raise HTTPException(403, "Nemate ovlasti")
|
|
cur.execute(
|
|
"""SELECT file_path, file_name, mime FROM pgz_sport.invoice_uploads
|
|
WHERE invoice_id=%s ORDER BY uploaded_at DESC LIMIT 1""", (invoice_id,))
|
|
up = cur.fetchone()
|
|
if not up:
|
|
raise HTTPException(404, "Datoteka skena ne postoji za ovaj račun")
|
|
p = Path(up["file_path"])
|
|
if not p.exists():
|
|
raise HTTPException(404, f"Datoteka ne postoji na disku")
|
|
return FileResponse(str(p), media_type=up.get("mime") or "application/octet-stream",
|
|
filename=up.get("file_name") or p.name)
|
|
|
|
|
|
@router.get("/invoices/uploads/{upload_id}/file")
|
|
def upload_file(upload_id: int, authorization: Optional[str] = Header(None)):
|
|
user = _resolve_user(authorization)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute("SELECT * FROM pgz_sport.invoice_uploads WHERE id=%s", (upload_id,))
|
|
up = cur.fetchone()
|
|
if not up:
|
|
raise HTTPException(404, "Upload ne postoji")
|
|
if user and not is_pgz_admin(user) and user.get("klub_id") != up.get("klub_id"):
|
|
raise HTTPException(403, "Nemate ovlasti")
|
|
p = Path(up["file_path"])
|
|
if not p.exists():
|
|
raise HTTPException(404, "Datoteka ne postoji")
|
|
return FileResponse(str(p), media_type=up.get("mime") or "application/octet-stream",
|
|
filename=up.get("file_name") or p.name)
|
|
|
|
|
|
@router.post("/invoices/{invoice_id}/comment")
|
|
def invoices_comment(invoice_id: int, body: dict = Body(...),
|
|
authorization: Optional[str] = Header(None)):
|
|
"""Savez admin / klub admin / pgz admin može dodati komentar (audit log entry)."""
|
|
user = _resolve_user(authorization)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute("SELECT i.*, k.savez_id FROM pgz_sport.invoices i LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id WHERE i.id=%s", (invoice_id,))
|
|
inv = cur.fetchone()
|
|
if not inv:
|
|
raise HTTPException(404, "Račun ne postoji")
|
|
if user and not can_comment_invoice(user, inv):
|
|
raise HTTPException(403, "Nemate ovlasti komentirati")
|
|
txt = (body.get("comment") or "").strip()
|
|
if not txt:
|
|
raise HTTPException(400, "Komentar je prazan")
|
|
audit_invoice(user, invoice_id, "comment", field="komentar", old=None, new=txt[:500])
|
|
return {"ok": True, "invoice_id": invoice_id, "comment": txt}
|
|
|
|
|
|
@router.get("/invoices/{invoice_id}/audit")
|
|
def invoices_audit(invoice_id: int, limit: int = 100,
|
|
authorization: Optional[str] = Header(None)):
|
|
user = _resolve_user(authorization)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute("SELECT i.id, i.klub_id FROM pgz_sport.invoices i WHERE i.id=%s", (invoice_id,))
|
|
inv = cur.fetchone()
|
|
if not inv:
|
|
raise HTTPException(404, "Račun ne postoji")
|
|
if user and not can_view_invoice(user, inv):
|
|
raise HTTPException(403, "Nemate ovlasti")
|
|
return {"ok": True, "audit": fetch_audit("pgz_sport.invoices", invoice_id, limit)}
|
|
|
|
|
|
@router.post("/invoices")
|
|
def invoices_create(body: dict = Body(...), authorization: Optional[str] = Header(None)):
|
|
"""Create an invoice from parsed OCR result.
|
|
Body: {klub_id, tenant_id, invoice_kind, invoice_no, vendor_name, vendor_oib,
|
|
invoice_date, amount_gross, amount_net, amount_vat, vat_rate, currency,
|
|
iban_to, description, category, lines:[{...}], upload_id?}"""
|
|
required = ["invoice_kind", "invoice_no", "invoice_date", "amount_gross"]
|
|
for k in required:
|
|
if body.get(k) in (None, ""):
|
|
raise HTTPException(400, f"Nedostaje polje: {k}")
|
|
|
|
klub_id = body.get("klub_id")
|
|
tenant_id = body.get("tenant_id", 1)
|
|
upload_id = body.get("upload_id")
|
|
lines = body.get("lines") or []
|
|
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
"""INSERT INTO pgz_sport.invoices
|
|
(klub_id, invoice_kind, invoice_no, internal_no,
|
|
vendor_oib, vendor_name, vendor_address,
|
|
customer_oib, customer_name,
|
|
invoice_date, due_date, currency,
|
|
amount_net, amount_vat, amount_gross, vat_rate,
|
|
payment_status, payment_method, iban_to,
|
|
description, category, account_code, tenant_id, meta)
|
|
VALUES (%s,%s,%s,%s, %s,%s,%s, %s,%s,
|
|
%s,%s,COALESCE(%s,'EUR'),
|
|
%s,%s,%s,%s,
|
|
COALESCE(%s,'unpaid'),%s,%s,
|
|
%s,%s,%s,%s,%s)
|
|
ON CONFLICT (klub_id, invoice_kind, invoice_no, vendor_oib)
|
|
DO UPDATE SET amount_gross=EXCLUDED.amount_gross,
|
|
amount_net=EXCLUDED.amount_net,
|
|
amount_vat=EXCLUDED.amount_vat,
|
|
updated_at=NOW()
|
|
RETURNING id, invoice_no, amount_gross, payment_status""",
|
|
(
|
|
klub_id, body["invoice_kind"], body["invoice_no"], body.get("internal_no"),
|
|
body.get("vendor_oib"), body.get("vendor_name"), body.get("vendor_address"),
|
|
body.get("customer_oib"), body.get("customer_name"),
|
|
body["invoice_date"], body.get("due_date"), body.get("currency"),
|
|
body.get("amount_net"), body.get("amount_vat"), body["amount_gross"], body.get("vat_rate"),
|
|
body.get("payment_status"), body.get("payment_method"), body.get("iban_to"),
|
|
body.get("description"), body.get("category"), body.get("account_code"),
|
|
tenant_id, json.dumps(body.get("meta", {})),
|
|
),
|
|
)
|
|
inv = cur.fetchone()
|
|
inv_id = inv["id"]
|
|
|
|
# Replace lines
|
|
cur.execute("DELETE FROM pgz_sport.invoice_lines WHERE invoice_id=%s", (inv_id,))
|
|
for i, ln in enumerate(lines, start=1):
|
|
cur.execute(
|
|
"""INSERT INTO pgz_sport.invoice_lines
|
|
(invoice_id, line_no, description, quantity, unit, unit_price,
|
|
vat_rate, line_net, line_vat, line_gross, account_code, cost_center, meta)
|
|
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
|
|
(
|
|
inv_id, ln.get("line_no", i), ln.get("description") or ln.get("opis") or "",
|
|
ln.get("quantity") or ln.get("kolicina") or 1,
|
|
ln.get("unit") or ln.get("jedinica") or "kom",
|
|
ln.get("unit_price") or ln.get("cijena"),
|
|
ln.get("vat_rate", 25),
|
|
ln.get("line_net"), ln.get("line_vat"),
|
|
ln.get("line_gross") or ln.get("ukupno"),
|
|
ln.get("account_code"), ln.get("cost_center"),
|
|
json.dumps(ln.get("meta", {})),
|
|
),
|
|
)
|
|
|
|
# Link upload to invoice
|
|
if upload_id:
|
|
cur.execute(
|
|
"UPDATE pgz_sport.invoice_uploads SET invoice_id=%s WHERE id=%s",
|
|
(inv_id, upload_id),
|
|
)
|
|
|
|
return {"ok": True, "invoice": inv}
|
|
|
|
|
|
@router.put("/invoices/{invoice_id}")
|
|
def invoices_update(invoice_id: int, body: dict = Body(...), authorization: Optional[str] = Header(None)):
|
|
"""Update / approve invoice. Body may include any of: payment_status, paid_date,
|
|
approved (bool), notes, category, account_code, due_date."""
|
|
user = _resolve_user(authorization)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute("SELECT i.*, k.savez_id FROM pgz_sport.invoices i LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id WHERE i.id=%s", (invoice_id,))
|
|
inv = cur.fetchone()
|
|
if not inv:
|
|
raise HTTPException(404, "Račun ne postoji")
|
|
if user and not can_edit_invoice(user, inv):
|
|
raise HTTPException(403, "Nemate ovlasti uređivati ovaj račun")
|
|
|
|
fields = []
|
|
args: list = []
|
|
changes = []
|
|
for col in ("payment_status", "paid_date", "due_date", "category",
|
|
"account_code", "notes", "vat_rate", "amount_net", "amount_vat",
|
|
"amount_gross", "payment_method", "iban_to"):
|
|
if col in body:
|
|
fields.append(f"{col}=%s")
|
|
args.append(body[col])
|
|
changes.append((col, inv.get(col), body[col]))
|
|
if body.get("approved"):
|
|
fields.append("approved_at=NOW()")
|
|
changes.append(("approved_at", inv.get("approved_at"), "now"))
|
|
if not fields:
|
|
raise HTTPException(400, "Nema polja za izmjenu")
|
|
fields.append("updated_at=NOW()")
|
|
args.append(invoice_id)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(f"UPDATE pgz_sport.invoices SET {','.join(fields)} WHERE id=%s RETURNING *", args)
|
|
row = cur.fetchone()
|
|
for f, o, n in changes:
|
|
audit_invoice(user, invoice_id, "update", field=f, old=o, new=n)
|
|
return {"ok": True, "invoice": row}
|
|
|
|
|
|
@router.post("/invoices/{invoice_id}/pay")
|
|
def invoices_pay(invoice_id: int, body: dict = Body(default={}),
|
|
authorization: Optional[str] = Header(None)):
|
|
"""Označi račun kao plaćen + insert payment record.
|
|
Body: {iban_to, iban_from, paid_date, reference, bank_transaction_id, payment_method, amount}
|
|
"""
|
|
user = _resolve_user(authorization)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute("SELECT i.*, k.savez_id FROM pgz_sport.invoices i LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id WHERE i.id=%s", (invoice_id,))
|
|
inv = cur.fetchone()
|
|
if not inv:
|
|
raise HTTPException(404, "Račun ne postoji")
|
|
if user and not can_pay_invoice(user, inv):
|
|
raise HTTPException(403, "Nemate ovlasti označiti račun kao plaćen")
|
|
if (inv.get("payment_status") or "").lower() == "paid":
|
|
raise HTTPException(409, "Račun je već označen kao plaćen")
|
|
|
|
paid_date = body.get("paid_date") or date.today().isoformat()
|
|
payment_method = body.get("payment_method") or "transfer"
|
|
iban_from = body.get("iban_from")
|
|
iban_to = body.get("iban_to") or inv.get("iban_to")
|
|
reference = body.get("reference")
|
|
tx_id = body.get("bank_transaction_id") or body.get("tx_id")
|
|
amount = body.get("amount") or inv.get("amount_gross")
|
|
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
"""UPDATE pgz_sport.invoices
|
|
SET payment_status='paid', paid_date=%s,
|
|
payment_method=COALESCE(%s,payment_method),
|
|
iban_from=COALESCE(%s,iban_from),
|
|
iban_to=COALESCE(%s,iban_to),
|
|
updated_at=NOW()
|
|
WHERE id=%s
|
|
RETURNING id, invoice_no, paid_date, amount_gross, payment_status,
|
|
iban_from, iban_to, payment_method""",
|
|
(paid_date, payment_method, iban_from, iban_to, invoice_id),
|
|
)
|
|
row = cur.fetchone()
|
|
# Insert payment record
|
|
cur.execute(
|
|
"""INSERT INTO pgz_sport.payments
|
|
(klub_id, invoice_id, payment_date, amount, currency, payment_method,
|
|
iban_from, iban_to, reference, bank_transaction_id, matched_status)
|
|
VALUES (%s,%s,%s,%s,COALESCE(%s,'EUR'),%s,%s,%s,%s,%s,'matched')
|
|
RETURNING id""",
|
|
(inv.get("klub_id"), invoice_id, paid_date, amount,
|
|
inv.get("currency"), payment_method, iban_from, iban_to,
|
|
reference, tx_id),
|
|
)
|
|
pay = cur.fetchone()
|
|
audit_invoice(user, invoice_id, "pay", field="payment_status",
|
|
old=inv.get("payment_status"), new="paid")
|
|
return {"ok": True, "invoice": row, "payment_id": pay["id"] if pay else None}
|
|
|
|
|
|
# ── R5.3 BULK OPERATIONS ──────────────────────────────────────────────
|
|
@router.post("/invoices/bulk-pay")
|
|
def invoices_bulk_pay(body: dict = Body(...), authorization: Optional[str] = Header(None)):
|
|
"""Bulk označi listu računa kao plaćene.
|
|
Body: {ids: [int], paid_date?, payment_method?, iban_from?, iban_to?, reference?, tx_id?}"""
|
|
user = _resolve_user(authorization)
|
|
ids = body.get("ids") or []
|
|
if not ids or not isinstance(ids, list):
|
|
raise HTTPException(400, "ids je obavezna ne-prazna lista")
|
|
paid_date = body.get("paid_date") or date.today().isoformat()
|
|
payment_method = body.get("payment_method") or "transfer"
|
|
iban_from = body.get("iban_from")
|
|
iban_to = body.get("iban_to")
|
|
reference = body.get("reference")
|
|
tx_id = body.get("bank_transaction_id") or body.get("tx_id")
|
|
|
|
results = {"paid": [], "skipped": [], "forbidden": [], "errors": []}
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
"""SELECT i.*, k.savez_id FROM pgz_sport.invoices i
|
|
LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id
|
|
WHERE i.id = ANY(%s)""", (ids,))
|
|
rows = cur.fetchall()
|
|
for inv in rows:
|
|
if (inv.get("payment_status") or "").lower() == "paid":
|
|
results["skipped"].append(inv["id"]); continue
|
|
if user and not can_pay_invoice(user, inv):
|
|
results["forbidden"].append(inv["id"]); continue
|
|
try:
|
|
with _db() as c:
|
|
cur = c.cursor()
|
|
cur.execute(
|
|
"""UPDATE pgz_sport.invoices
|
|
SET payment_status='paid', paid_date=%s,
|
|
payment_method=COALESCE(%s,payment_method),
|
|
iban_from=COALESCE(%s,iban_from),
|
|
iban_to=COALESCE(%s,iban_to),
|
|
updated_at=NOW()
|
|
WHERE id=%s""",
|
|
(paid_date, payment_method, iban_from, iban_to, inv["id"]),
|
|
)
|
|
cur.execute(
|
|
"""INSERT INTO pgz_sport.payments
|
|
(klub_id, invoice_id, payment_date, amount, currency, payment_method,
|
|
iban_from, iban_to, reference, bank_transaction_id, matched_status)
|
|
VALUES (%s,%s,%s,%s,COALESCE(%s,'EUR'),%s,%s,%s,%s,%s,'matched')""",
|
|
(inv.get("klub_id"), inv["id"], paid_date, inv.get("amount_gross"),
|
|
inv.get("currency"), payment_method, iban_from, iban_to, reference, tx_id),
|
|
)
|
|
audit_invoice(user, inv["id"], "bulk_pay",
|
|
field="payment_status", old=inv.get("payment_status"), new="paid")
|
|
results["paid"].append(inv["id"])
|
|
except Exception as e:
|
|
results["errors"].append({"id": inv["id"], "err": str(e)[:200]})
|
|
return {"ok": True, "summary": {k: len(v) for k, v in results.items()}, "details": results}
|
|
|
|
|
|
@router.post("/invoices/bulk-cancel")
|
|
def invoices_bulk_cancel(body: dict = Body(...), authorization: Optional[str] = Header(None)):
|
|
"""Bulk otkaži (status='cancelled') — samo pgz_admin ili klub_admin svog kluba."""
|
|
user = _resolve_user(authorization)
|
|
ids = body.get("ids") or []
|
|
razlog = body.get("razlog") or body.get("reason") or "(bulk cancel)"
|
|
if not ids:
|
|
raise HTTPException(400, "ids je obavezna ne-prazna lista")
|
|
results = {"cancelled": [], "skipped": [], "forbidden": [], "errors": []}
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
"""SELECT i.*, k.savez_id FROM pgz_sport.invoices i
|
|
LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id
|
|
WHERE i.id = ANY(%s)""", (ids,))
|
|
rows = cur.fetchall()
|
|
for inv in rows:
|
|
if (inv.get("payment_status") or "").lower() in ("paid", "cancelled"):
|
|
results["skipped"].append(inv["id"]); continue
|
|
if user and not can_edit_invoice(user, inv):
|
|
results["forbidden"].append(inv["id"]); continue
|
|
try:
|
|
with _db() as c:
|
|
c.cursor().execute(
|
|
"""UPDATE pgz_sport.invoices
|
|
SET payment_status='cancelled',
|
|
notes = COALESCE(notes,'') || E'\n[CANCEL] ' || %s,
|
|
updated_at=NOW() WHERE id=%s""",
|
|
(razlog, inv["id"]),
|
|
)
|
|
audit_invoice(user, inv["id"], "bulk_cancel",
|
|
field="payment_status", old=inv.get("payment_status"),
|
|
new=f"cancelled: {razlog}")
|
|
results["cancelled"].append(inv["id"])
|
|
except Exception as e:
|
|
results["errors"].append({"id": inv["id"], "err": str(e)[:200]})
|
|
return {"ok": True, "summary": {k: len(v) for k, v in results.items()}, "details": results}
|
|
|
|
|
|
# ── R5.4 XLSX EXPORT ───────────────────────────────────────────────────
|
|
@router.get("/export/invoices.xlsx")
|
|
def invoices_export_xlsx(
|
|
tenant_id: Optional[int] = Query(None),
|
|
klub_id: Optional[int] = Query(None),
|
|
od: Optional[str] = Query(None, description="datum od YYYY-MM-DD"),
|
|
do: Optional[str] = Query(None, description="datum do YYYY-MM-DD"),
|
|
status: Optional[str] = None,
|
|
kind: Optional[str] = None,
|
|
authorization: Optional[str] = Header(None),
|
|
):
|
|
"""XLSX export računa za knjigovodstvo. Stupci: ID, datum, vrsta, broj,
|
|
izdavatelj, OIB, klub, neto, PDV, brutto, valuta, status, IBAN, opis."""
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Font, PatternFill, Alignment
|
|
from io import BytesIO
|
|
from fastapi.responses import StreamingResponse
|
|
|
|
user = _resolve_user(authorization)
|
|
sql = """SELECT i.id, i.invoice_date, i.invoice_kind, i.invoice_no,
|
|
i.vendor_name, i.vendor_oib, i.customer_oib,
|
|
i.amount_net, i.amount_vat, i.amount_gross, i.vat_rate,
|
|
i.currency, i.payment_status, i.payment_method,
|
|
i.iban_to, i.description, i.category,
|
|
i.paid_date, i.tenant_id, i.klub_id,
|
|
k.naziv AS klub_naziv, k.savez_id
|
|
FROM pgz_sport.invoices i
|
|
LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id
|
|
WHERE 1=1"""
|
|
args: list = []
|
|
if tenant_id is not None: sql += " AND i.tenant_id=%s"; args.append(tenant_id)
|
|
if klub_id is not None: sql += " AND i.klub_id=%s"; args.append(klub_id)
|
|
if od: sql += " AND i.invoice_date >= %s"; args.append(od)
|
|
if do: sql += " AND i.invoice_date <= %s"; args.append(do)
|
|
if status: sql += " AND i.payment_status=%s"; args.append(status)
|
|
if kind: sql += " AND i.invoice_kind=%s"; args.append(kind)
|
|
sql += " ORDER BY i.invoice_date DESC, i.id DESC"
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(sql, args)
|
|
rows = cur.fetchall()
|
|
|
|
# Filter po user permissions
|
|
if user and not is_pgz_admin(user):
|
|
rows = [r for r in rows if can_view_invoice(user, r)]
|
|
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Računi"
|
|
headers = ["ID", "Datum", "Vrsta", "Broj računa", "Izdavatelj", "OIB",
|
|
"Klub", "Iznos neto", "PDV", "Brutto", "Stopa PDV",
|
|
"Valuta", "Status", "Datum uplate", "IBAN primatelja",
|
|
"Opis", "Kategorija"]
|
|
bold = Font(bold=True, color="FFFFFF")
|
|
fill = PatternFill("solid", fgColor="003087")
|
|
for col_idx, h in enumerate(headers, 1):
|
|
cell = ws.cell(row=1, column=col_idx, value=h)
|
|
cell.font = bold; cell.fill = fill
|
|
cell.alignment = Alignment(horizontal="center")
|
|
for r_idx, r in enumerate(rows, 2):
|
|
ws.cell(row=r_idx, column=1, value=r.get("id"))
|
|
ws.cell(row=r_idx, column=2, value=str(r.get("invoice_date") or ""))
|
|
ws.cell(row=r_idx, column=3, value=r.get("invoice_kind"))
|
|
ws.cell(row=r_idx, column=4, value=r.get("invoice_no"))
|
|
ws.cell(row=r_idx, column=5, value=r.get("vendor_name"))
|
|
ws.cell(row=r_idx, column=6, value=r.get("vendor_oib"))
|
|
ws.cell(row=r_idx, column=7, value=r.get("klub_naziv"))
|
|
ws.cell(row=r_idx, column=8, value=float(r["amount_net"]) if r.get("amount_net") is not None else None)
|
|
ws.cell(row=r_idx, column=9, value=float(r["amount_vat"]) if r.get("amount_vat") is not None else None)
|
|
ws.cell(row=r_idx, column=10, value=float(r["amount_gross"]) if r.get("amount_gross") is not None else None)
|
|
ws.cell(row=r_idx, column=11, value=float(r["vat_rate"]) if r.get("vat_rate") is not None else None)
|
|
ws.cell(row=r_idx, column=12, value=r.get("currency"))
|
|
ws.cell(row=r_idx, column=13, value=r.get("payment_status"))
|
|
ws.cell(row=r_idx, column=14, value=str(r.get("paid_date") or ""))
|
|
ws.cell(row=r_idx, column=15, value=r.get("iban_to"))
|
|
ws.cell(row=r_idx, column=16, value=r.get("description"))
|
|
ws.cell(row=r_idx, column=17, value=r.get("category"))
|
|
# Auto width
|
|
widths = [6, 12, 12, 18, 28, 14, 24, 12, 12, 12, 8, 6, 11, 12, 22, 30, 12]
|
|
for i, w in enumerate(widths, 1):
|
|
ws.column_dimensions[ws.cell(row=1, column=i).column_letter].width = w
|
|
ws.freeze_panes = "A2"
|
|
|
|
buf = BytesIO()
|
|
wb.save(buf); buf.seek(0)
|
|
fname = f"racuni_{date.today().isoformat()}.xlsx"
|
|
return StreamingResponse(
|
|
buf, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
|
headers={"Content-Disposition": f'attachment; filename="{fname}"'},
|
|
)
|
|
|
|
|
|
# ── R5.6 STATS ─────────────────────────────────────────────────────────
|
|
@router.get("/stats")
|
|
def erp_stats(
|
|
klub_id: Optional[int] = Query(None),
|
|
tenant_id: Optional[int] = Query(None),
|
|
authorization: Optional[str] = Header(None),
|
|
):
|
|
"""Statistika ERP-a: ukupno troškova mjesec/kvartal/godina po klubu/savezu,
|
|
breakdown po vrstama (gorivo/cestarina/hotel/oprema/ostalo)."""
|
|
user = _resolve_user(authorization)
|
|
today = date.today()
|
|
month_start = today.replace(day=1).isoformat()
|
|
qmonth = ((today.month - 1) // 3) * 3 + 1
|
|
quarter_start = today.replace(month=qmonth, day=1).isoformat()
|
|
year_start = today.replace(month=1, day=1).isoformat()
|
|
|
|
where = ["1=1"]; args: list = []
|
|
if klub_id is not None:
|
|
where.append("klub_id=%s"); args.append(klub_id)
|
|
if tenant_id is not None:
|
|
where.append("tenant_id=%s"); args.append(tenant_id)
|
|
where_sql = " AND ".join(where)
|
|
|
|
def q_sum(date_from):
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
f"""SELECT COUNT(*) AS n,
|
|
COALESCE(SUM(amount_gross),0)::float AS total,
|
|
COALESCE(SUM(CASE WHEN payment_status='paid' THEN amount_gross END),0)::float AS paid,
|
|
COALESCE(SUM(CASE WHEN payment_status<>'paid' THEN amount_gross END),0)::float AS unpaid
|
|
FROM pgz_sport.invoices
|
|
WHERE {where_sql} AND invoice_date >= %s""",
|
|
args + [date_from],
|
|
)
|
|
return cur.fetchone()
|
|
|
|
def q_breakdown(date_from):
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
f"""SELECT invoice_kind, COUNT(*) AS n,
|
|
COALESCE(SUM(amount_gross),0)::float AS total
|
|
FROM pgz_sport.invoices
|
|
WHERE {where_sql} AND invoice_date >= %s
|
|
GROUP BY invoice_kind ORDER BY total DESC""",
|
|
args + [date_from],
|
|
)
|
|
return cur.fetchall()
|
|
|
|
def q_top(date_from):
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(
|
|
f"""SELECT i.klub_id, k.naziv AS klub_naziv,
|
|
COUNT(*) AS n, COALESCE(SUM(i.amount_gross),0)::float AS total
|
|
FROM pgz_sport.invoices i
|
|
LEFT JOIN pgz_sport.klubovi k ON k.id=i.klub_id
|
|
WHERE {where_sql} AND i.invoice_date >= %s
|
|
GROUP BY i.klub_id, k.naziv ORDER BY total DESC LIMIT 10""",
|
|
args + [date_from],
|
|
)
|
|
return cur.fetchall()
|
|
|
|
# Putni nalozi totals
|
|
def q_pn(date_from):
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
pn_where = ["report_type='putni_nalog'"]; pn_args: list = []
|
|
if klub_id is not None:
|
|
pn_where.append("klub_id=%s"); pn_args.append(klub_id)
|
|
if tenant_id is not None:
|
|
pn_where.append("tenant_id=%s"); pn_args.append(tenant_id)
|
|
cur.execute(
|
|
f"""SELECT COUNT(*) AS n,
|
|
COALESCE(SUM(cost_total),0)::float AS total,
|
|
COALESCE(SUM(dnevnice_amount),0)::float AS dnevnice,
|
|
COALESCE(SUM(cost_transport),0)::float AS transport
|
|
FROM pgz_sport.expense_reports
|
|
WHERE {' AND '.join(pn_where)} AND date_from >= %s""",
|
|
pn_args + [date_from],
|
|
)
|
|
return cur.fetchone()
|
|
|
|
return {
|
|
"ok": True,
|
|
"as_of": today.isoformat(),
|
|
"filters": {"klub_id": klub_id, "tenant_id": tenant_id},
|
|
"invoices": {
|
|
"month": {"since": month_start, **q_sum(month_start), "by_kind": q_breakdown(month_start)},
|
|
"quarter": {"since": quarter_start, **q_sum(quarter_start), "by_kind": q_breakdown(quarter_start)},
|
|
"year": {"since": year_start, **q_sum(year_start), "by_kind": q_breakdown(year_start)},
|
|
},
|
|
"top_klubovi_godina": q_top(year_start),
|
|
"putni_nalozi": {
|
|
"month": {"since": month_start, **q_pn(month_start)},
|
|
"quarter": {"since": quarter_start, **q_pn(quarter_start)},
|
|
"year": {"since": year_start, **q_pn(year_start)},
|
|
},
|
|
}
|
|
|
|
|
|
@router.get("/invoices/uploads/list")
|
|
def uploads_list(klub_id: Optional[int] = None, status: Optional[str] = None, limit: int = 50):
|
|
sql = """SELECT id, klub_id, file_name, file_size, mime, ocr_status, ocr_engine,
|
|
ai_invoice_no, ai_invoice_date, ai_vendor_name, ai_vendor_oib,
|
|
ai_amount_gross, ai_currency, invoice_id, uploaded_at, processed_at
|
|
FROM pgz_sport.invoice_uploads WHERE 1=1"""
|
|
args: list = []
|
|
if klub_id is not None:
|
|
sql += " AND klub_id=%s"; args.append(klub_id)
|
|
if status:
|
|
sql += " AND ocr_status=%s"; args.append(status)
|
|
sql += " ORDER BY uploaded_at DESC LIMIT %s"; args.append(limit)
|
|
with _db() as c:
|
|
cur = c.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
|
cur.execute(sql, args)
|
|
rows = cur.fetchall()
|
|
return {"ok": True, "rows": rows}
|