|
"""Compare manual FTWZ DTA Sale item values with NSDL export values, per item row (read-only)."""
|
|
import json
|
|
import re
|
|
import sys
|
|
import urllib.parse
|
|
import urllib.request
|
|
from collections import Counter, defaultdict
|
|
from concurrent.futures import ThreadPoolExecutor
|
|
from pathlib import Path
|
|
|
|
import subprocess
|
|
|
|
S = Path(sys.argv[1])
|
|
secret = json.loads(
|
|
subprocess.check_output(
|
|
[
|
|
"aws", "secretsmanager", "get-secret-value", "--secret-id", "nsdl-boe-automation-secrets-TEST",
|
|
"--region", "ap-south-1", "--query", "SecretString", "--output", "text",
|
|
],
|
|
text=True,
|
|
)
|
|
)
|
|
CLIENT = sys.argv[2] if len(sys.argv) > 2 else "wud"
|
|
c = secret["CLIENTS"][CLIENT]
|
|
base = c["FRAPPE_URL"].rstrip("/")
|
|
H = {"Authorization": f"token {c['api_key']}:{c['api_secret']}"}
|
|
|
|
|
|
def get(path):
|
|
return json.load(urllib.request.urlopen(urllib.request.Request(base + path, headers=H), timeout=60))
|
|
|
|
|
|
# ---- NSDL lookup from all stored exports (later files override earlier for the same item) ----
|
|
def norm(s):
|
|
return re.sub(r"\s+", " ", (s or "").strip().upper())
|
|
|
|
|
|
nsdl = defaultdict(list) # request_id -> [row dict]
|
|
cols_seen = None
|
|
for f in sorted((S / "raw").glob("*/dta_export_v1.xlsx")):
|
|
lines = f.read_text(encoding="utf-8", errors="replace").splitlines()
|
|
hdr = lines[2].split("\t")
|
|
cols_seen = hdr
|
|
ix = {h: i for i, h in enumerate(hdr)}
|
|
need = ["REQUEST ID", "INVOICE NO", "ITEM DESCRIPTION", "QUANTITY", "FREIGHT IN INR", "BCD TO BE CONSIDERED FOR CALCULATING IGST"]
|
|
missing = [n for n in need if n not in ix]
|
|
if missing:
|
|
print("export", f.parent.name, "lacks columns", missing)
|
|
continue
|
|
seen_here = defaultdict(list)
|
|
for line in lines[3:]:
|
|
if not line.strip():
|
|
continue
|
|
r = line.split("\t")
|
|
if len(r) <= max(ix[n] for n in need):
|
|
continue
|
|
seen_here[r[ix["REQUEST ID"]].strip()].append(
|
|
{n: r[ix[n]].strip() for n in need}
|
|
)
|
|
for rid, rows in seen_here.items():
|
|
nsdl[rid] = rows # latest export wins
|
|
|
|
print("NSDL requests indexed:", len(nsdl))
|
|
|
|
# ---- manual docs from the last five weeks ----
|
|
_q = urllib.parse.urlencode({"fields": json.dumps(["name", "owner", "creation", "request_id"]), "limit_page_length": "5000", "order_by": "creation asc"})
|
|
lst = get("/api/resource/FTWZ%20DTA%20Sale?" + _q)["data"]
|
|
print("site", base.split("//")[1], "docs:", len(lst), "owners:", Counter(r["owner"] for r in lst).most_common(5))
|
|
manual = [r for r in lst if r["owner"] != "<automation-user>" and r["creation"] >= "2026-09-05" and r.get("request_id") in nsdl]
|
|
print("manual docs created since 2026-09-05 with a matching NSDL request:", len(manual))
|
|
|
|
|
|
def fetch(name):
|
|
try:
|
|
return get("/api/resource/FTWZ%20DTA%20Sale/" + urllib.parse.quote(name))["data"]
|
|
except Exception as exc: # noqa: BLE001
|
|
return {"_error": str(exc)[:80], "name": name}
|
|
|
|
|
|
with ThreadPoolExecutor(8) as pool:
|
|
docs = list(pool.map(fetch, [r["name"] for r in manual]))
|
|
print("fetched:", sum(1 for d in docs if "_error" not in d), "errors:", sum(1 for d in docs if "_error" in d))
|
|
|
|
# ---- per item comparison ----
|
|
FIELDS = {"freight_in_inr": "FREIGHT IN INR", "bcd_to_be_considered_for_calculating_igst": "BCD TO BE CONSIDERED FOR CALCULATING IGST"}
|
|
tally = {f: Counter() for f in FIELDS}
|
|
unmatched = 0
|
|
examples = {f: defaultdict(list) for f in FIELDS}
|
|
for doc in docs:
|
|
if "_error" in doc:
|
|
continue
|
|
rows = nsdl.get(doc.get("request_id"), [])
|
|
for item in doc.get("items", []):
|
|
cand = [
|
|
r
|
|
for r in rows
|
|
if norm(r["INVOICE NO"]) == norm(item.get("invoice_no"))
|
|
and norm(r["ITEM DESCRIPTION"])[:40] == norm(item.get("item_description"))[:40]
|
|
]
|
|
if len(cand) != 1:
|
|
unmatched += 1
|
|
continue
|
|
for fld, col in FIELDS.items():
|
|
nval = cand[0][col]
|
|
mval = item.get(fld)
|
|
nkind = "NSDL blank" if nval == "" else "NSDL filled"
|
|
if mval in (None, ""):
|
|
mkind = "manual empty"
|
|
elif str(mval) == nval or (_f := re.sub(r"\.0+$", "", str(mval))) == re.sub(r"\.0+$", "", nval):
|
|
mkind = "manual = NSDL value"
|
|
elif str(mval) == "0":
|
|
mkind = "manual '0'"
|
|
else:
|
|
mkind = "manual other value"
|
|
tally[fld][(nkind, mkind)] += 1
|
|
if len(examples[fld][(nkind, mkind)]) < 3:
|
|
examples[fld][(nkind, mkind)].append((doc["name"], nval, mval))
|
|
|
|
print("item rows that could not be matched 1:1 to an NSDL row (skipped):", unmatched)
|
|
for fld in FIELDS:
|
|
print("\n==", fld)
|
|
total = sum(tally[fld].values())
|
|
for (nk, mk), n in sorted(tally[fld].items(), key=lambda x: -x[1]):
|
|
print(f" {nk:12s} -> {mk:22s} {n:5d} ({n / total:.0%}) e.g. {examples[fld][(nk, mk)][:2]}")
|
|
print(" total item rows compared:", total)
|
|
|
|
# ---- automated (API-user) docs for contrast ----
|
|
auto_lst = [r for r in lst if r["owner"] == "<automation-user>"]
|
|
print("\nautomated docs on wud:", len(auto_lst))
|