shanxia/bx/bx-backend/app/utils/codegen.py

"""Generate business codes like BYJ20260424001 / BX20260424001."""

from datetime import datetime
from sqlalchemy.orm import Session
from sqlalchemy import text


def generate_apply_code(db: Session, prefix: str) -> str:
    """
    Generate a sequential code: {prefix}{YYYYMMDD}{3-digit-sequence}.
    prefix examples: 'BYJ' for advance, 'BX' for reimburse.
    Thread-safe via DB-level auto-increment fallback.
    """
    today = datetime.now().strftime("%Y%m%d")
    like_pattern = f"{prefix}{today}%"

    # Find the max sequence number for today
    result = db.execute(
        text("SELECT MAX(CAST(SUBSTRING(apply_code, -3) AS UNSIGNED)) FROM ("
             "SELECT apply_code FROM bx_advance_apply WHERE apply_code LIKE :p1 "
             "UNION ALL "
             "SELECT apply_code FROM bx_reimburse_apply WHERE apply_code LIKE :p2"
             ") t"),
        {"p1": like_pattern, "p2": like_pattern}
    ).scalar()

    seq = (result or 0) + 1
    return f"{prefix}{today}{seq:03d}"