"""Utilities: Backup/Restore, CSV import/export, Day Book, Close FY, global search.""" import io import csv import json import logging from datetime import datetime, timezone from typing import Optional from bson import ObjectId from fastapi import APIRouter, HTTPException, Depends, Request, UploadFile, File, Query from fastapi.responses import StreamingResponse from auth import get_current_user, require_admin logger = logging.getLogger("rm-regal.utilities") router = APIRouter(prefix="/api", tags=["utilities"]) BACKUP_COLLECTIONS = ["users", "companies", "parties", "items", "invoices", "invoices_trash", "expenses", "counters", "activity_logs"] def _ser(doc: dict) -> dict: if not doc: return doc d = dict(doc) if "_id" in d: d["_id"] = str(d["_id"]) return d # -------- Legacy Backup & Restore (renamed to avoid collision with new backup_engine.py) -------- @router.get("/backup/legacy-export") async def backup_export(request: Request, user=Depends(require_admin)): """Download a JSON snapshot of all collections.""" db = request.app.state.db bundle = {"version": 1, "exported_at": datetime.now(timezone.utc).isoformat(), "data": {}} for col in BACKUP_COLLECTIONS: docs = await db[col].find().to_list(50000) bundle["data"][col] = [_ser(d) for d in docs] buf = io.BytesIO(json.dumps(bundle, indent=2, default=str).encode("utf-8")) fname = f"rm-regal-backup-{datetime.now().strftime('%Y%m%d-%H%M%S')}.json" return StreamingResponse( buf, media_type="application/json", headers={"Content-Disposition": f'attachment; filename="{fname}"'}, ) @router.post("/backup/legacy-restore") async def backup_restore(file: UploadFile = File(...), wipe: bool = Query(False), request: Request = None, user=Depends(require_admin)): """Restore from a previously-exported JSON snapshot. If wipe=True the matching collections are cleared first.""" db = request.app.state.db try: raw = await file.read() bundle = json.loads(raw) data = bundle.get("data", {}) except Exception as e: raise HTTPException(400, f"Invalid backup file: {e}") summary = {} for col, rows in data.items(): if col not in BACKUP_COLLECTIONS or not isinstance(rows, list): continue if wipe: await db[col].delete_many({}) inserted = 0 for row in rows: try: _id = row.pop("_id", None) or row.pop("id", None) doc = {**row} if _id: try: doc["_id"] = ObjectId(_id) except Exception: doc["_id"] = _id # Skip duplicate _id without wipe if doc.get("_id") is not None: exists = await db[col].find_one({"_id": doc["_id"]}) if exists and not wipe: continue await db[col].insert_one(doc) inserted += 1 except Exception as e: logger.warning("restore skip %s: %s", col, e) summary[col] = inserted await db.activity_logs.insert_one({ "user_id": user["id"], "user_email": user["email"], "action": "restore", "entity": "backup", "meta": summary, "timestamp": datetime.now(timezone.utc).isoformat(), }) return {"ok": True, "restored": summary, "wiped": wipe} # -------- Items CSV Import / Export -------- ITEM_CSV_COLS = ["name", "code", "hsn", "unit", "category", "gst_rate", "sale_price", "purchase_price", "mrp", "opening_stock", "current_stock", "low_stock_threshold"] @router.get("/items/export-csv") async def items_export_csv( request: Request, company_id: str = Query(...), format: str = Query("csv"), # csv | xlsx | pdf user=Depends(get_current_user), ): from exports import export_dispatch db = request.app.state.db items = await db.items.find({"company_id": company_id}).to_list(20000) rows = [{k: it.get(k, "") for k in ITEM_CSV_COLS} for it in items] return export_dispatch(rows, "items", format, title="Items Master") @router.post("/items/import-csv") async def items_import_csv(file: UploadFile = File(...), company_id: str = Query(...), request: Request = None, user=Depends(require_admin)): db = request.app.state.db try: text = (await file.read()).decode("utf-8-sig") except Exception as e: raise HTTPException(400, f"Cannot read CSV: {e}") reader = csv.DictReader(io.StringIO(text)) created = 0 updated = 0 errors = [] for i, row in enumerate(reader, start=2): name = (row.get("name") or "").strip() if not name: errors.append(f"Row {i}: missing name") continue # v12.12 — Multi-image support (CSV). # Accepts: cover_image, images (semicolon- or pipe-separated), or image_1 .. image_10 columns. cover_raw = (row.get("cover_image") or row.get("cover") or "").strip() bulk_raw = (row.get("images") or "").strip() per_col = [(row.get(f"image_{n}") or "").strip() for n in range(1, 11)] gallery = [] if bulk_raw: for token in bulk_raw.replace("|", ";").split(";"): t = token.strip() if t: gallery.append(t) for v in per_col: if v: gallery.append(v) # Dedupe + cap 10, cover first seen = set() images = [] if cover_raw and cover_raw not in seen: seen.add(cover_raw); images.append(cover_raw) for v in gallery: if v not in seen: seen.add(v); images.append(v) images = images[:10] cover_final = images[0] if images else "" doc = { "name": name, "code": (row.get("code") or "").strip(), "hsn": (row.get("hsn") or "").strip(), "unit": (row.get("unit") or "PCS").strip() or "PCS", "category": (row.get("category") or "General").strip() or "General", "gst_rate": float(row.get("gst_rate") or 18), "sale_price": float(row.get("sale_price") or 0), "purchase_price": float(row.get("purchase_price") or 0), "mrp": float(row.get("mrp") or 0), "opening_stock": float(row.get("opening_stock") or 0), "current_stock": float(row.get("current_stock") or row.get("opening_stock") or 0), "low_stock_threshold": float(row.get("low_stock_threshold") or 5), "company_id": company_id, "cover_image": cover_final, "images": images, "photo_url": cover_final, } existing = await db.items.find_one({"company_id": company_id, "name": name}) if existing: await db.items.update_one({"_id": existing["_id"]}, {"$set": doc}) updated += 1 else: doc["created_at"] = datetime.now(timezone.utc).isoformat() await db.items.insert_one(doc) created += 1 await db.activity_logs.insert_one({ "user_id": user["id"], "user_email": user["email"], "action": "import", "entity": "item", "meta": {"created": created, "updated": updated, "errors": len(errors)}, "timestamp": datetime.now(timezone.utc).isoformat(), }) return {"ok": True, "created": created, "updated": updated, "errors": errors[:20]} # -------- Parties Bulk Import (Excel) -------- PARTY_TEMPLATE_COLS = [ "Name*", "Contact No.", "Email ID", "Address", "Opening Balance", "Opening Date (dd/MM/yyyy)", "GSTIN No.", "Group Name", "Shipping Address", "Party", # CUSTOMER / SUPPLIER / EXPENSE / General ] @router.get("/parties/import/template") async def parties_import_template(): """Download a ready-to-fill XLSX template for bulk import.""" from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter wb = Workbook() ws = wb.active ws.title = "Parties" # Headers head_fill = PatternFill("solid", fgColor="0B5132") head_font = Font(bold=True, color="FFFFFF", size=11) for i, col in enumerate(PARTY_TEMPLATE_COLS, start=1): c = ws.cell(row=1, column=i, value=col) c.fill = head_fill c.font = head_font c.alignment = Alignment(horizontal="center", vertical="center") # Sample rows samples = [ ["Ramesh Traders", "9876543210", "ramesh@example.com", "Shop 12, MG Road, Goa", 0, "01/04/2025", "30ABCDE1234F1Z5", "Wholesale", "Same as address", "CUSTOMER"], ["Sharma Steel Suppliers", "9123456780", "sharma@example.com", "Plot 7, Industrial Area, Verem", 12500, "15/04/2025", "30FGHIJ5678K2L9", "Steel", "", "SUPPLIER"], ["Electricity Board", "", "", "Goa Power Co.", -3200, "01/04/2025", "", "Utilities", "", "EXPENSE"], ["Walk-in Customer", "9988776655", "", "Panaji", 0, "", "", "General", "", "General"], ] for r, row in enumerate(samples, start=2): for col_i, val in enumerate(row, start=1): ws.cell(row=r, column=col_i, value=val) # Column widths widths = [22, 14, 22, 28, 14, 22, 18, 14, 28, 12] for i, w in enumerate(widths, start=1): ws.column_dimensions[get_column_letter(i)].width = w ws.freeze_panes = "A2" # Notes sheet n = wb.create_sheet("Instructions") notes = [ "Bulk-import Parties — How to use this template:", "", "1) Fill rows starting at row 2. Do NOT change the header text in row 1.", "2) Required fields: 'Name*' (the rest are optional).", "3) 'Party' accepts: CUSTOMER, SUPPLIER, EXPENSE, General (case-insensitive).", " - If left blank, defaults to General.", "4) 'Opening Balance' is positive when the party owes you (receivable),", " negative when you owe them (payable).", "5) 'Opening Date' format: dd/MM/yyyy (e.g. 01/04/2025)", "6) GSTIN: 15-character GSTIN (e.g. 30ABCDE1234F1Z5). Validated lightly.", "7) Duplicates: rows are matched by Name within the active company.", " - If a party with same name exists, it will be SKIPPED unless you choose 'Update' mode.", "8) Save the file and upload from: Parties → Import button.", "", "Tip: Phone numbers should be 10 digits (Indian).", ] for i, line in enumerate(notes, start=1): c = n.cell(row=i, column=1, value=line) if i == 1: c.font = Font(bold=True, size=14, color="0B5132") elif line.startswith(("1)", "2)", "3)", "4)", "5)", "6)", "7)", "8)")): c.font = Font(bold=True) n.column_dimensions["A"].width = 100 buf = io.BytesIO() wb.save(buf) buf.seek(0) headers = {"Content-Disposition": 'attachment; filename="RBSRegal-Parties-Import-Template.xlsx"'} return StreamingResponse(buf, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers=headers) def _norm_party_type(v): s = str(v or "").strip().upper().replace(" ", "").replace("-", "") # Typo tolerance if s in ("CUSTAMER",): s = "CUSTOMER" if s in ("EXPNESE",): s = "EXPENSE" mapping = { "CUSTOMER": "customer", "BUYER": "customer", "CLIENT": "customer", "SUPPLIER": "vendor", "VENDOR": "vendor", "EXPENSE": "expense", "GENERAL": "customer", "": "customer", } return mapping.get(s, "customer") def _parse_date_ddmmyyyy(v): if v is None or v == "": return "" if isinstance(v, datetime): return v.strftime("%Y-%m-%d") s = str(v).strip() for fmt in ("%d/%m/%Y", "%d-%m-%Y", "%Y-%m-%d", "%d/%m/%y"): try: return datetime.strptime(s, fmt).strftime("%Y-%m-%d") except Exception: continue return "" @router.post("/parties/import") async def parties_import_xlsx( file: UploadFile = File(...), company_id: str = Query(...), mode: str = Query("skip"), # skip | update request: Request = None, user=Depends(require_admin), ): """Import parties from an .xlsx file produced from /parties/import/template.""" from openpyxl import load_workbook db = request.app.state.db raw = await file.read() try: wb = load_workbook(io.BytesIO(raw), data_only=True, read_only=True) except Exception as e: raise HTTPException(400, f"Cannot read Excel file: {e}") # Pick the first non-instructions sheet sheet = None for s in wb.sheetnames: if "instruct" not in s.lower(): sheet = wb[s] break if sheet is None: raise HTTPException(400, "No data sheet found in file") rows = list(sheet.iter_rows(values_only=True)) if not rows or len(rows) < 2: raise HTTPException(400, "Sheet is empty — fill rows starting at row 2") header = [str(c or "").strip() for c in rows[0]] def idx(*aliases): for a in aliases: for i, h in enumerate(header): if h.lower().strip().replace(".", "").replace("*", "").replace(" ", "") == a.lower().replace(".", "").replace(" ", ""): return i return -1 iName = idx("Name", "Name*") iPhone = idx("Contact No", "Phone", "Mobile") iEmail = idx("Email ID", "Email") iAddr = idx("Address", "Billing Address") iOpen = idx("Opening Balance", "Balance") iDate = idx("Opening Date (dd/MM/yyyy)", "Opening Date", "Date") iGstin = idx("GSTIN No", "GSTIN") iGroup = idx("Group Name", "Group", "Category") iShip = idx("Shipping Address", "Ship") iType = idx("Party", "Type") if iName < 0: raise HTTPException(400, "'Name' column not found — please use the official template") created, updated, skipped, errors = 0, 0, 0, [] now_iso = datetime.now(timezone.utc).isoformat() for ridx, row in enumerate(rows[1:], start=2): if not row or all((c is None or str(c).strip() == "") for c in row): continue name = (str(row[iName] or "").strip()) if iName >= 0 and iName < len(row) else "" if not name: errors.append(f"Row {ridx}: missing Name — skipped") continue def get(i, _row=row): return _row[i] if (0 <= i < len(_row)) else None try: opening = float(get(iOpen) or 0) except Exception: opening = 0.0 doc = { "name": name, "phone": str(get(iPhone) or "").strip(), "email": str(get(iEmail) or "").strip(), "address": str(get(iAddr) or "").strip(), "shipping_address": str(get(iShip) or "").strip(), "gstin": str(get(iGstin) or "").strip().upper(), "group": str(get(iGroup) or "").strip(), "type": _norm_party_type(get(iType)), "opening_balance": opening, "opening_date": _parse_date_ddmmyyyy(get(iDate)), "outstanding": opening, "company_id": company_id, } existing = await db.parties.find_one({"company_id": company_id, "name": name}) if existing: if mode == "update": doc["updated_at"] = now_iso await db.parties.update_one({"_id": existing["_id"]}, {"$set": doc}) updated += 1 else: skipped += 1 else: doc["created_at"] = now_iso await db.parties.insert_one(doc) created += 1 await db.activity_logs.insert_one({ "user_id": user["id"], "user_email": user["email"], "action": "import", "entity": "party", "meta": {"created": created, "updated": updated, "skipped": skipped, "errors": len(errors), "mode": mode}, "timestamp": now_iso, }) return {"ok": True, "created": created, "updated": updated, "skipped": skipped, "errors": errors[:50]} # -------- Items Bulk Import (Excel) -------- ITEM_TEMPLATE_COLS = [ "Item name*", "Item code", "Description", "Category", "HSN", "Colour", "Size", "Brand", "Default Mrp", "Sale price", "Purchase price", "Online Store Price", "Discount Type", "Sale Discount", "Current stock quantity", "Minimum stock quantity", "Item Location", "Tax Rate", "Inclusive Of Tax", "Base Unit (x)", "Secondary Unit (y)", "Conversion Rate (n) (x = ny)", # v12.12 — Multi-image columns. Provide public URLs or data: URLs. Up to 10 images. # The first non-empty image (or "Cover Image" if filled) becomes the cover. "Cover Image", "Image 1", "Image 2", "Image 3", "Image 4", "Image 5", "Image 6", "Image 7", "Image 8", "Image 9", "Image 10", ] @router.get("/items/import/template") async def items_import_template(): """Download a ready-to-fill XLSX template matching Vyapar export format.""" from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter wb = Workbook() ws = wb.active ws.title = "Items" head_fill = PatternFill("solid", fgColor="0B5132") head_font = Font(bold=True, color="FFFFFF", size=11) for i, col in enumerate(ITEM_TEMPLATE_COLS, start=1): c = ws.cell(row=1, column=i, value=col) c.fill = head_fill c.font = head_font c.alignment = Alignment(horizontal="center", vertical="center") samples = [ ["CALENTA MECHANICAL 10LTR", "CAL10", "Mechanical valve 10L", "Plumbing", "8481", "", "10L", "Calenta", 7500, 6969, 5500, 7200, "Discount %", 0, 5, 1, "Rack A1", "GST@18%", "N", "PCS", "BOX", 12, "", "", "", "", "", "", "", "", "", "", ""], ["ANGLE GRINDER 750W 100MM", "AGM1072P", "FREM brand grinder", "Tools", "8467", "Yellow", "100MM", "FREM", 2200, 1737.29, 1400, 0, "Discount %", 0, 8, 2, "Rack B3", "GST@18%", "N", "PCS", "", 0, "", "", "", "", "", "", "", "", "", "", ""], ["TMT STEEL ROD 12MM", "TMT12", "Fe-500D grade", "Steel", "7214", "", "12mm", "Tata", 110, 95, 78, 0, "Discount %", 0, 500, 50, "Yard", "GST@18%", "N", "PCS", "BUNDLE", 10, "", "", "", "", "", "", "", "", "", "", ""], ["20MM SWING CHECK VALVE", "VAL20", "Brass body", "Plumbing", "8481", "", "20mm", "Sant", 450, 380, 290, 0, "Discount %", 0, 25, 5, "Rack A2", "GST@18%", "N", "PCS", "", 0, "", "", "", "", "", "", "", "", "", "", ""], ] for r, row in enumerate(samples, start=2): for col_i, val in enumerate(row, start=1): ws.cell(row=r, column=col_i, value=val) widths = [30, 12, 22, 14, 10, 10, 10, 14, 12, 12, 14, 16, 14, 12, 14, 14, 14, 12, 12, 12, 14, 12, 26, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22] for i, w in enumerate(widths, start=1): ws.column_dimensions[get_column_letter(i)].width = w ws.freeze_panes = "A2" n = wb.create_sheet("Instructions") notes = [ "Bulk-import Items / Products — How to use this template:", "", "1) Fill rows starting at row 2. Do NOT change header text in row 1.", "2) Required field: 'Item name*' only.", "3) 'Tax Rate' formats supported: GST@18% | 18% | 18 | IGST@5% → parsed to 18 / 5.", "4) 'Inclusive Of Tax' — Y / N / Yes / No / 1 / 0.", "5) 'Discount Type' — 'Discount %' or 'Discount Rs'.", "6) 'Current stock quantity' is your present stock; can be negative if oversold.", "7) 'Minimum stock quantity' triggers low-stock alerts on dashboard.", "8) 'Base Unit (x)' & 'Secondary Unit (y)' — e.g. Base=PCS, Sec=BOX, Conv=12 means 1 BOX = 12 PCS.", "9) Multi-Image columns (Cover + Image 1..10):", " - Paste a public HTTPS URL (e.g. https://cdn.shop.com/x.jpg) OR a data:image/... base64 string.", " - 'Cover Image' is the primary thumbnail shown in lists & billing. If empty, 'Image 1' is auto-promoted to cover.", " - You can fill up to 10 images. Empty cells are ignored.", " - The same image is automatically de-duplicated across rows.", "10) Duplicates: matched by Item name within the company.", " - Mode 'Skip' keeps existing, 'Update' overwrites all fields EXCEPT current_stock.", "11) Save and upload from: Items → Import button.", ] for i, line in enumerate(notes, start=1): c = n.cell(row=i, column=1, value=line) if i == 1: c.font = Font(bold=True, size=14, color="0B5132") elif line.startswith(tuple(f"{x})" for x in range(1, 11))): c.font = Font(bold=True) n.column_dimensions["A"].width = 110 buf = io.BytesIO() wb.save(buf) buf.seek(0) headers = {"Content-Disposition": 'attachment; filename="RGERegalgoa-Items-Import-Template.xlsx"'} return StreamingResponse(buf, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers=headers) def _parse_tax_rate(v, default=18.0): """Accepts 'GST@18%', '18%', '18', 'IGST@5%' → 18 / 5.""" if v is None or v == "": return default s = str(v).strip() import re m = re.search(r"(\d+(?:\.\d+)?)", s) return float(m.group(1)) if m else default def _parse_bool_yn(v, default=False): if v is None or v == "": return default s = str(v).strip().lower() return s in ("y", "yes", "true", "1", "t") @router.get("/items/export") async def items_export_xlsx( request: Request, company_id: str = Query(...), user=Depends(get_current_user), ): """Download all items as XLSX in the 22-column Vyapar Export Items format (same schema as the import template). Filename: Export Items.xlsx """ from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter db = request.app.state.db items = await db.items.find({"company_id": company_id}).to_list(50000) items.sort(key=lambda x: (x.get("category") or "", x.get("name") or "")) wb = Workbook() ws = wb.active ws.title = "Items" head_fill = PatternFill("solid", fgColor="0B5132") head_font = Font(bold=True, color="FFFFFF", size=11) for i, col in enumerate(ITEM_TEMPLATE_COLS, start=1): c = ws.cell(row=1, column=i, value=col) c.fill = head_fill c.font = head_font c.alignment = Alignment(horizontal="center", vertical="center") def _bool_yn(v): return "Y" if v else "N" for r, it in enumerate(items, start=2): tax = it.get("gst_rate", 0) tax_str = f"GST@{tax:g}%" if tax else "" imgs = list(it.get("images") or []) cover = it.get("cover_image") or (imgs[0] if imgs else "") or it.get("photo_url") or "" # Ensure cover is in the gallery, then pad gallery to 10 if cover and cover not in imgs: imgs = [cover] + imgs imgs = imgs[:10] while len(imgs) < 10: imgs.append("") vals = [ it.get("name") or "", it.get("code") or "", it.get("description") or "", it.get("category") or "", it.get("hsn") or "", it.get("colour") or "", it.get("size") or "", it.get("brand") or "", it.get("mrp") or 0, it.get("sale_price") or 0, it.get("purchase_price") or 0, it.get("online_price") or 0, it.get("discount_type") or "Discount %", it.get("sale_discount") or 0, it.get("current_stock") or 0, it.get("low_stock_threshold") or 0, it.get("item_location") or "", tax_str, _bool_yn(it.get("tax_inclusive", False)), it.get("unit") or "PCS", it.get("secondary_unit") or "", it.get("conversion_rate") or 0, cover, imgs[0], imgs[1], imgs[2], imgs[3], imgs[4], imgs[5], imgs[6], imgs[7], imgs[8], imgs[9], ] for col_i, val in enumerate(vals, start=1): ws.cell(row=r, column=col_i, value=val) widths = [30, 12, 22, 14, 10, 10, 10, 14, 12, 12, 14, 16, 14, 12, 14, 14, 14, 12, 12, 12, 14, 12, 26, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22] for i, w in enumerate(widths, start=1): ws.column_dimensions[get_column_letter(i)].width = w ws.freeze_panes = "A2" await db.activity_logs.insert_one({ "user_id": user["id"], "user_email": user["email"], "action": "export", "entity": "item", "meta": {"count": len(items), "format": "xlsx"}, "timestamp": datetime.now(timezone.utc).isoformat(), }) buf = io.BytesIO() wb.save(buf) buf.seek(0) headers = {"Content-Disposition": 'attachment; filename="Export Items.xlsx"'} return StreamingResponse(buf, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers=headers) @router.post("/items/import") async def items_import_xlsx( file: UploadFile = File(...), company_id: str = Query(...), mode: str = Query("skip"), request: Request = None, user=Depends(require_admin), ): from openpyxl import load_workbook db = request.app.state.db raw = await file.read() try: wb = load_workbook(io.BytesIO(raw), data_only=True, read_only=True) except Exception as e: raise HTTPException(400, f"Cannot read Excel file: {e}") sheet = None for s in wb.sheetnames: if "instruct" not in s.lower(): sheet = wb[s] break if sheet is None: raise HTTPException(400, "No data sheet found") rows = list(sheet.iter_rows(values_only=True)) if len(rows) < 2: raise HTTPException(400, "Sheet is empty — fill rows starting at row 2") header = [str(c or "").strip() for c in rows[0]] def norm(s): return s.lower().strip().replace(".", "").replace("*", "").replace(" ", "").replace("/", "").replace("%", "").replace("(", "").replace(")", "").replace("=", "") def idx(*aliases): for a in aliases: na = norm(a) for i, h in enumerate(header): if norm(h) == na: return i return -1 iName = idx("Item name", "Item name*", "Name", "Product Name") iCode = idx("Item code", "Code", "SKU") iDesc = idx("Description", "Notes") iCat = idx("Category", "Group") iHsn = idx("HSN", "HSN Code", "HSN/SAC") iColour = idx("Colour", "Color") iSize = idx("Size") iBrand = idx("Brand") iMrp = idx("Default Mrp", "MRP") iSale = idx("Sale price", "Selling Price") iPur = idx("Purchase price", "Cost", "Buy Price") iOnline = idx("Online Store Price", "Online Price") iDscType = idx("Discount Type") iDsc = idx("Sale Discount", "Discount") iCurStock = idx("Current stock quantity", "Current Stock", "Stock", "Opening Stock") iMinStock = idx("Minimum stock quantity", "Min Stock", "Reorder", "Low Stock Alert") iLoc = idx("Item Location", "Location", "Rack") iTax = idx("Tax Rate", "GST", "GST %", "Tax") iIncl = idx("Inclusive Of Tax", "Inclusive", "Incl Tax") iBaseU = idx("Base Unit (x)", "Base Unit", "Unit", "UOM") iSecU = idx("Secondary Unit (y)", "Secondary Unit") iConv = idx("Conversion Rate (n) (x = ny)", "Conversion Rate", "Conv Rate") iCover = idx("Cover Image", "Cover", "Main Image", "Primary Image") iImgs = [idx(f"Image {n}", f"Image {n} URL", f"Photo {n}") for n in range(1, 11)] if iName < 0: raise HTTPException(400, "'Item name' column missing — please use the official template") created, updated, skipped, errors = 0, 0, 0, [] now_iso = datetime.now(timezone.utc).isoformat() def fnum(v, default=0.0): try: return float(v) if v not in (None, "") else default except Exception: return default for ridx, row in enumerate(rows[1:], start=2): if not row or all((c is None or str(c).strip() == "") for c in row): continue def get(i, _row=row): return _row[i] if 0 <= i < len(_row) else None name = str(get(iName) or "").strip() if not name: errors.append(f"Row {ridx}: missing Item name — skipped") continue cur_stock = fnum(get(iCurStock), 0) # Collect multi-image columns (v12.12) — preserve order, dedupe, max 10. cover_raw = str(get(iCover) or "").strip() img_raw = [] for ix in iImgs: if ix >= 0: v = str(get(ix) or "").strip() if v: img_raw.append(v) # Build final images list: cover first (if present), then Image 1..10, deduped. seen = set() images = [] if cover_raw and cover_raw not in seen: seen.add(cover_raw); images.append(cover_raw) for v in img_raw: if v not in seen: seen.add(v); images.append(v) images = images[:10] cover_final = images[0] if images else "" doc = { "name": name, "code": str(get(iCode) or "").strip(), "description": str(get(iDesc) or "").strip(), "category": (str(get(iCat) or "").strip() or "General"), "hsn": str(get(iHsn) or "").strip(), "colour": str(get(iColour) or "").strip(), "size": str(get(iSize) or "").strip(), "brand": str(get(iBrand) or "").strip(), "mrp": fnum(get(iMrp), 0), "sale_price": fnum(get(iSale), 0), "purchase_price": fnum(get(iPur), 0), "online_price": fnum(get(iOnline), 0), "discount_type": str(get(iDscType) or "Discount %").strip(), "sale_discount": fnum(get(iDsc), 0), "opening_stock": cur_stock, "current_stock": cur_stock, "low_stock_threshold": fnum(get(iMinStock), 0), "item_location": str(get(iLoc) or "").strip(), "gst_rate": _parse_tax_rate(get(iTax), 18), "tax_inclusive": _parse_bool_yn(get(iIncl), False), "unit": (str(get(iBaseU) or "").strip() or "PCS"), "secondary_unit": str(get(iSecU) or "").strip(), "conversion_rate": fnum(get(iConv), 0), "company_id": company_id, # Multi-image fields — mirrored to legacy photo_url so existing list/billing UI keeps working "cover_image": cover_final, "images": images, "photo_url": cover_final, } existing = await db.items.find_one({"company_id": company_id, "name": name}) if existing: if mode == "update": # Don't overwrite live current_stock if user already had transactions doc.pop("current_stock", None) doc["updated_at"] = now_iso await db.items.update_one({"_id": existing["_id"]}, {"$set": doc}) updated += 1 else: skipped += 1 else: doc["created_at"] = now_iso await db.items.insert_one(doc) created += 1 await db.activity_logs.insert_one({ "user_id": user["id"], "user_email": user["email"], "action": "import", "entity": "item", "meta": {"created": created, "updated": updated, "skipped": skipped, "errors": len(errors), "mode": mode}, "timestamp": now_iso, }) return {"ok": True, "created": created, "updated": updated, "skipped": skipped, "errors": errors[:50]} @router.post("/items/bulk-update") async def items_bulk_update( payload: dict, request: Request, company_id: str = Query(...), user=Depends(require_admin), ): """Apply a partial patch to many items in one call. Body: { "updates": [ {"id": "...", "patch": {"sale_price": 110, "current_stock": 50}}, ... ] } """ db = request.app.state.db updates = payload.get("updates") or [] if not updates: raise HTTPException(400, "No updates provided") allowed = { "name", "code", "hsn", "description", "category", "colour", "size", "brand", "mrp", "sale_price", "purchase_price", "online_price", "discount_type", "sale_discount", "current_stock", "low_stock_threshold", "item_location", "gst_rate", "tax_inclusive", "unit", "secondary_unit", "conversion_rate", } now_iso = datetime.now(timezone.utc).isoformat() succ, errs = 0, [] for u in updates: iid = u.get("id") patch = u.get("patch") or {} if not iid: errs.append("missing id") continue clean = {k: v for k, v in patch.items() if k in allowed} if not clean: errs.append(f"{iid}: no valid fields") continue clean["updated_at"] = now_iso try: res = await db.items.update_one({"_id": ObjectId(iid), "company_id": company_id}, {"$set": clean}) if res.matched_count: succ += 1 else: errs.append(f"{iid}: not found") except Exception as ex: errs.append(f"{iid}: {ex}") await db.activity_logs.insert_one({ "user_id": user["id"], "user_email": user["email"], "action": "bulk_update", "entity": "item", "meta": {"updated": succ, "errors": len(errs)}, "timestamp": now_iso, }) return {"ok": True, "updated": succ, "errors": errs[:50]} # -------- Day Book -------- @router.get("/reports/daybook") async def daybook(request: Request, company_id: Optional[str] = None, date: Optional[str] = None, user=Depends(get_current_user)): """All transactions for a single day (sales + purchases + expenses).""" db = request.app.state.db target = date or datetime.now(timezone.utc).strftime("%Y-%m-%d") q_base = {"company_id": company_id} if company_id else {} invoices = await db.invoices.find( {**q_base, "invoice_date": target}, {"invoice_no": 1, "type": 1, "party_name": 1, "total": 1, "payment_received": 1, "invoice_date": 1, "status": 1}, ).to_list(2000) expenses = await db.expenses.find({**q_base, "date": target}).to_list(2000) rows = [] for inv in invoices: rows.append({ "kind": inv.get("type"), "ref": inv.get("invoice_no"), "party": inv.get("party_name", ""), "amount": float(inv.get("total", 0)), "paid": float(inv.get("payment_received", 0)), "status": inv.get("status", ""), }) for ex in expenses: rows.append({ "kind": "expense", "ref": f"EXP-{str(ex.get('_id'))[-6:]}", "party": f"{ex.get('category', '')} — {ex.get('vendor', '')}".strip(" —"), "amount": float(ex.get("amount", 0)), "paid": float(ex.get("amount", 0)), "status": "—", }) sales_total = sum(r["amount"] for r in rows if r["kind"] == "sale") purchase_total = sum(r["amount"] for r in rows if r["kind"] == "purchase") expense_total = sum(r["amount"] for r in rows if r["kind"] == "expense") return { "date": target, "rows": rows, "sales_total": round(sales_total, 2), "purchase_total": round(purchase_total, 2), "expense_total": round(expense_total, 2), "net": round(sales_total - purchase_total - expense_total, 2), } # -------- Global Search -------- @router.get("/search") async def search(request: Request, q: str = Query(..., min_length=2), company_id: Optional[str] = None, user=Depends(get_current_user)): db = request.app.state.db base = {"company_id": company_id} if company_id else {} regex = {"$regex": q, "$options": "i"} items = await db.items.find({**base, "$or": [{"name": regex}, {"code": regex}, {"hsn": regex}]}, {"name": 1, "code": 1}).to_list(20) parties = await db.parties.find({**base, "$or": [{"name": regex}, {"phone": regex}, {"gstin": regex}]}, {"name": 1, "type": 1, "gstin": 1}).to_list(20) invoices = await db.invoices.find({**base, "$or": [{"invoice_no": regex}, {"party_name": regex}]}, {"invoice_no": 1, "type": 1, "party_name": 1, "total": 1, "invoice_date": 1}).to_list(20) return { "items": [_ser(d) for d in items], "parties": [_ser(d) for d in parties], "invoices": [_ser(d) for d in invoices], } # -------- Close Financial Year -------- @router.post("/admin/close-fy") async def close_financial_year(request: Request, company_id: str = Query(...), user=Depends(require_admin)): """Reset per-company invoice counters so new fiscal year starts at 00001 for every type.""" db = request.app.state.db prefixes = ["sale", "purchase", "quotation", "challan", "credit_note", "debit_note", "return", "sale_order", "proforma"] keys = [f"{company_id}:{p}" for p in prefixes] res = await db.counters.delete_many({"_id": {"$in": keys}}) await db.activity_logs.insert_one({ "user_id": user["id"], "user_email": user["email"], "action": "close_fy", "entity": "company", "entity_id": company_id, "meta": {"counters_reset": res.deleted_count}, "timestamp": datetime.now(timezone.utc).isoformat(), }) return {"ok": True, "counters_reset": res.deleted_count} # -------- Loyalty points (party) -------- @router.post("/parties/{pid}/loyalty") async def adjust_loyalty(pid: str, delta: float = Query(...), reason: Optional[str] = "", request: Request = None, user=Depends(get_current_user)): db = request.app.state.db try: oid = ObjectId(pid) except Exception: raise HTTPException(400, "Invalid party id") await db.parties.update_one({"_id": oid}, {"$inc": {"loyalty_points": float(delta)}}) await db.activity_logs.insert_one({ "user_id": user["id"], "user_email": user["email"], "action": "loyalty", "entity": "party", "entity_id": pid, "meta": {"delta": delta, "reason": reason}, "timestamp": datetime.now(timezone.utc).isoformat(), }) p = await db.parties.find_one({"_id": oid}) return {"ok": True, "loyalty_points": p.get("loyalty_points", 0)} @router.get("/parties/{pid}/loyalty") async def get_loyalty(pid: str, request: Request, user=Depends(get_current_user)): """Current balance + recent loyalty transactions for one party. Used by the new Loyalty UI on the Parties page.""" db = request.app.state.db try: oid = ObjectId(pid) except Exception: raise HTTPException(400, "Invalid party id") p = await db.parties.find_one({"_id": oid}) if not p: raise HTTPException(404, "Party not found") # Pull last 20 loyalty transactions (most recent first) history = await db.activity_logs.find( {"entity": "party", "entity_id": pid, "action": "loyalty"} ).sort("timestamp", -1).limit(20).to_list(20) for h in history: h["_id"] = str(h["_id"]) return { "party_id": pid, "party_name": p.get("name", ""), "loyalty_points": float(p.get("loyalty_points", 0) or 0), "history": history, } @router.get("/loyalty/leaderboard") async def loyalty_leaderboard(company_id: str = Query(...), limit: int = 10, request: Request = None, user=Depends(get_current_user)): """Top customers by loyalty balance — fuels the Loyalty dashboard card.""" db = request.app.state.db cursor = db.parties.find( {"company_id": company_id, "loyalty_points": {"$gt": 0}}, {"name": 1, "phone": 1, "loyalty_points": 1, "type": 1}, ).sort("loyalty_points", -1).limit(min(limit, 50)) out = [] async for p in cursor: out.append({ "id": str(p["_id"]), "name": p.get("name", ""), "phone": p.get("phone", ""), "type": p.get("type", "customer"), "loyalty_points": float(p.get("loyalty_points", 0) or 0), }) return out