"""流水（F31、F38）、盘点及邮件（F39–F40）、批次报表（F34）、客户库存打印（F37）。""" import datetime as dt import smtplib import uuid from email.message import EmailMessage from fastapi import APIRouter, Depends, HTTPException, Query from fastapi.responses import HTMLResponse from pydantic import BaseModel from sqlalchemy import or_, select from sqlalchemy.orm import Session from flyway.api.deps import get_db from flyway.api.lists import Page, like, page_params, paginate from flyway.config import settings from flyway.domain.clock import nz_today from flyway.domain.errors import BusinessError from flyway.models import Client, Product, ProductBatch, Transfer, TransferItem from flyway.printing.fill import esc, nz, time_text from flyway.printing.pages import page from flyway.services.reports import ( InventoryCount, client_stock_by_product, doc_path, inventory_count, kind, nz_window, running_balances, signed, ) from flyway.services.stock import stock router = APIRouter(prefix="/api") def _get(db: Session, model, obj_id): obj = db.get(model, obj_id) if obj_id else None if obj_id and obj is None: raise HTTPException(status_code=404) return obj def _when(t: Transfer) -> str: return f"{nz(t.date):%d/%m/%Y} {time_text(t.date)}" # ---- 明细流水 /TransferItem ---- def _items_query(client_id, product_id, batch_id, keyword): q = select(TransferItem).join(Transfer).join(Client).join(ProductBatch).join(Product) if client_id: q = q.where(Transfer.client_id == client_id) if product_id: q = q.where(ProductBatch.product_id == product_id) if batch_id: q = q.where(TransferItem.batch_id == batch_id) if keyword: q = q.where(or_(like(Transfer.code, keyword), like(Product.title, keyword), like(Client.name, keyword))) return q def _resolve(db, client_id, product_id, batch_id): client = _get(db, Client, client_id) batch = _get(db, ProductBatch, batch_id) product = batch.product if batch else _get(db, Product, product_id) return client, product, batch def _title(client, product, batch) -> str: t = (f"{client.name}'s " if client else "") + "Transactions" if product: t += f" for {product.title}" + (f" (Batch Code:{batch.number})" if batch else "") return t @router.get("/transactions") def transactions(client_id: uuid.UUID | None = None, product_id: uuid.UUID | None = None, batch_id: uuid.UUID | None = None, keyword: str | None = None, page_: Page = Depends(page_params), db: Session = Depends(get_db)): client, product, batch = _resolve(db, client_id, product_id, batch_id) q = _items_query(client_id, product and product.id, batch_id, keyword) sorts = {"date": Transfer.date} if product: # 旧逻辑：按产品看时固定按时间正序，并显示结余 page_.sort = "date" rows, total = paginate(db, q, page_, sorts, "-date") show_balance = bool(product and not keyword and not batch) bal = running_balances(db, product.id, client_id) if show_balance else {} items = [{"id": i.id, "date": i.transfer.date, "product_title": i.batch.product.title, "client_id": i.transfer.client_id, "client_name": i.transfer.client.name, "code": i.transfer.code, "path": doc_path(i.transfer), "quantity": signed(i), "is_draft": i.transfer.is_draft, "balance": bal.get(i.id)} for i in rows] return {"items": items, "total": total, "title": _title(client, product, batch), "show_balance": show_balance} @router.get("/print/transactions", response_class=HTMLResponse) def print_transactions(client_id: uuid.UUID, product_id: uuid.UUID | None = None, batch_id: uuid.UUID | None = None, keyword: str | None = None, db: Session = Depends(get_db)): """TransferItem/ListBlocks/Print.aspx：按列表关键字筛选的全部明细、不分页，有客户 + 产品时带结余。""" client, product, batch = _resolve(db, client_id, product_id, batch_id) if product is None: raise HTTPException(status_code=422, detail="product_id or batch_id is required.") items = db.scalars(_items_query(client_id, product.id, batch_id, keyword).order_by(Transfer.date, Transfer.code)).all() balance, rows = 0, [] for n, i in enumerate(items, start=1): qty = i.quantity * i.unit if not i.transfer.is_draft: balance += -qty if i.transfer.is_load_out else qty rows.append(f"{_when(i.transfer)}" f"{esc(i.transfer.code)}{'' if i.transfer.is_load_out else qty}" f"{qty if i.transfer.is_load_out else ''}{balance}") title = _title(client, product, batch) body = (f"" f"
" f"{''.join(rows)}" f"
Date/TimeReferenceLoad-InLoad-OutBalance
 {esc(title)}
") return page(title, body) # ---- 客户单据流水 /Transfer?clientId= ---- def _client(db: Session, client_id: uuid.UUID) -> Client: return _get(db, Client, client_id) @router.get("/clients/{client_id}/documents") def client_documents(client_id: uuid.UUID, page_: Page = Depends(page_params), db: Session = Depends(get_db)): _client(db, client_id) q = select(Transfer).where(Transfer.client_id == client_id, Transfer.is_draft.is_(False)) rows, total = paginate(db, q, page_, {"date": Transfer.date}, "-date") return {"items": [{"id": t.id, "date": t.date, "kind": kind(t), "code": t.code, "path": doc_path(t), "is_local": t.is_local, "operator": t.operator} for t in rows], "total": total} @router.get("/print/clients/{client_id}/documents", response_class=HTMLResponse) def print_client_documents(client_id: uuid.UUID, db: Session = Depends(get_db)): client = _client(db, client_id) docs = db.scalars(select(Transfer).where(Transfer.client_id == client_id, Transfer.is_draft.is_(False)) .order_by(Transfer.date.desc())).all() rows = "".join(f"{_when(t)}" f"{'Load-Out' if t.is_load_out else 'Load-In'}{esc(t.code)}" f"{esc(t.operator or '')}" for n, t in enumerate(docs, start=1)) title = f"{client.name}'s Transactions" body = (f"" f"
" f"{rows}" f"
Date/TimeDescriptionReferenceOperator
 {esc(title)}
") return page(title, body) # ---- 客户库存打印 ---- @router.get("/print/clients/{client_id}/stock", response_class=HTMLResponse) def print_client_stock(client_id: uuid.UUID, db: Session = Depends(get_db)): client = _client(db, client_id) rows = [] for n, r in enumerate(client_stock_by_product(db, client_id), start=1): p, s = r["product"], r["stock"] batches = len(r["batches"]) rows.append(f"{esc(p.title)}" f"{s} / {p.default_unit} = {s / p.default_unit:g}{batches}") title = f"{client.name}'s Stock Summary" body = (f"" f"
" f"{''.join(rows)}" f"
Product nameRemaining StockBatches
 {esc(title)}
") return page(title, body) # ---- 盘点 ---- TH = "background:#006699;color:#fff;font-size:15px;font-weight:bold;border-left:1px solid #0070A8;padding:3px 10px;" TD = "color:#00557F;border-left:1px solid #E1EEF4;font-size:12px;padding:3px 10px;" TD_ALT = TD + "background:#E1EEf4;" def _num(v: float) -> str: return f"{v:g}" def count_table(c: InventoryCount, links: bool) -> str: """盘点表（页面与邮件共用，内联样式以兼容邮件客户端）。""" def link(path: str, text: str) -> str: return f"{esc(text)}" if links else esc(text) head = "".join(f"{esc(x)}" for x in ["Reference", "Date", "Source/Dest", *[p.title for p in c.products], "Total (ctns)"]) rows = [] for day in c.days: for r in day.rows: ref = link(doc_path(r.transfer), r.transfer.code) if r.transfer.is_load_out and r.source is not None: ref += ", " + link(doc_path(r.source), r.source.code) place = esc(r.place) if "{}" in r.place: place = place.replace("{}", link(f"/clients/{r.place_client.id}", r.place_client.name) if r.place_client else "") cells = "".join(f"{_num(r.quantities[p.id]) if p.id in r.quantities else ''}" for p in c.products) rows.append(f"{ref}{day.day:%d %b %Y}" f"{place}{cells}{_num(r.total)}") bal = "".join(f"{_num(day.balances.get(p.id, 0))}" for p in c.products) rows.append(f"Balance{day.day:%d %b %Y}" f"{bal}{_num(sum(day.balances.values()))}") return (f"
" f"Inventory Count for {esc(c.client.name)}
" f"
" f"{head}" f"{''.join(rows)}
") @router.get("/print/clients/{client_id}/inventory-count", response_class=HTMLResponse) def print_inventory_count(client_id: uuid.UUID, from_date: dt.date | None = None, to_date: dt.date | None = None, db: Session = Depends(get_db)): client = _client(db, client_id) start, end = nz_window(from_date, to_date, nz_today()) c = inventory_count(db, client, start, end) form = (f"
" f" " f" " f"
") return page(f"Inventory Count for {client.name}", count_table(c, links=True).replace("", "" + form, 1)) class EmailIn(BaseModel): subject: str | None = None from_date: dt.date | None = None to_date: dt.date | None = None def send_email(to_addr: str, to_name: str, subject: str, html: str) -> None: if not (settings.smtp_host and settings.system_email): raise BusinessError("Email is not configured (FLYWAY_SMTP_HOST / FLYWAY_SYSTEM_EMAIL).") msg = EmailMessage() msg["From"] = f"{settings.system_email_sender} <{settings.system_email}>" msg["To"] = f"{to_name} <{to_addr}>" msg["Subject"] = subject msg.set_content("This email contains an HTML inventory count.") msg.add_alternative(html, subtype="html") with smtplib.SMTP(settings.smtp_host, settings.smtp_port, timeout=30) as smtp: if settings.smtp_starttls: smtp.starttls() if settings.smtp_user: smtp.login(settings.smtp_user, settings.smtp_password) smtp.send_message(msg) @router.post("/clients/{client_id}/inventory-count/email") def email_inventory_count(client_id: uuid.UUID, body: EmailIn, db: Session = Depends(get_db)): client = _client(db, client_id) if not (client.email or "").strip(): raise BusinessError("This client has no email address.", "email") start, end = nz_window(body.from_date, body.to_date, nz_today()) html = count_table(inventory_count(db, client, start, end), links=False) try: send_email(client.email.strip(), client.name, body.subject or "Inventory Count", html) except (OSError, ValueError, smtplib.SMTPException) as e: # ValueError：名称 / 地址含换行等非法头部 raise BusinessError(f"Failed to send email: {e}") from e return {"message": f"Email has been successfully sent to the {client.name} ({client.email})."} # ---- 批次报表 ---- @router.get("/print/batches/report", response_class=HTMLResponse) def batch_report(ids: list[uuid.UUID] = Query(...), db: Session = Depends(get_db)): """ProductBatch/ListBlocks/Report.aspx。""" css = ("") parts = [] for b in db.scalars(select(ProductBatch).where(ProductBatch.id.in_(ids)).order_by(ProductBatch.number)): unit = b.product.default_unit s = stock(db, batch_id=b.id) parts.append(f"" f"" f"
Product:{esc(b.product.title)}
Batch:{esc(b.number)}
Stock:{s // unit} ({s}/{unit})

" f"") items = db.scalars(select(TransferItem).join(Transfer).where(TransferItem.batch_id == b.id, Transfer.is_draft.is_(False))).all() by_client: dict = {} for i in sorted(items, key=lambda i: (i.transfer.date, i.transfer.is_load_out, i.transfer.code)): by_client.setdefault(i.transfer.client, []).append(i) for client, rows in by_client.items(): cs = sum(i.remaining_stock for i in rows if not i.transfer.is_load_out) balance, trs = 0, [] for i in rows: total = signed(i) balance += total sign = -1 if i.transfer.is_load_out else 1 trs.append(f"" f"" f"") parts.append(f"" f"") parts.append("
{nz(i.transfer.date):%d %b %Y}{esc(i.transfer.code)}{i.quantity * sign} ({total}/{i.unit}){balance // unit} ({balance}/{unit})
ClientStock
{esc(client.name)}" f"{cs // unit} ({cs}/{unit})
" f"" f"{''.join(trs)}
Date/TimeReferenceLoad In/OutBalance

") body = ("" "
" "Product Batch Report
" + "".join(parts)) return page("Product Batch Report", body, css)