74 lines
2.6 KiB
Python
74 lines
2.6 KiB
Python
|
"""
|
||
|
|
Clean up duplicates in PB:
|
||
|
|
1. Delete JEI_invoice_lines duplicates (keep one per invoice+line_no+stock_code)
|
||
|
|
2. Delete the bogus JEISA000 stock code and its lines
|
||
|
|
"""
|
||
|
|
import os, sys, json, urllib.request, urllib.parse
|
||
|
|
from collections import defaultdict
|
||
|
|
|
||
|
|
PB_URL = "https://pb.agent.apphub.co.za"
|
||
|
|
PB_TOKEN = None
|
||
|
|
env = os.path.join(os.path.dirname(__file__), "..", ".env.local")
|
||
|
|
if os.path.exists(env):
|
||
|
|
for line in open(env):
|
||
|
|
if line.startswith("PB_TOKEN="):
|
||
|
|
PB_TOKEN = line.strip().split("=", 1)[1]
|
||
|
|
if not PB_TOKEN:
|
||
|
|
sys.exit("PB_TOKEN missing")
|
||
|
|
H = {"Authorization": PB_TOKEN}
|
||
|
|
|
||
|
|
|
||
|
|
def get(path):
|
||
|
|
req = urllib.request.Request(PB_URL + path, headers=H, method="GET")
|
||
|
|
with urllib.request.urlopen(req, timeout=60) as r:
|
||
|
|
return json.loads(r.read())
|
||
|
|
|
||
|
|
|
||
|
|
def delete(path):
|
||
|
|
req = urllib.request.Request(PB_URL + path, headers=H, method="DELETE")
|
||
|
|
with urllib.request.urlopen(req, timeout=30) as r:
|
||
|
|
return r.read().decode("utf-8")
|
||
|
|
|
||
|
|
|
||
|
|
# 1. Find bogus JEISA000 stock code
|
||
|
|
sc = get("/api/collections/JEI_stock_codes/records?perPage=500")
|
||
|
|
jeisa000 = next((c for c in sc["items"] if c["code"] == "JEISA000"), None)
|
||
|
|
if jeisa000:
|
||
|
|
print(f"Found bogus JEISA000 id={jeisa000['id']}")
|
||
|
|
jid = jeisa000["id"]
|
||
|
|
jfilter = 'stock_code="' + jid + '"'
|
||
|
|
lines = get("/api/collections/JEI_invoice_lines/records?filter=" + urllib.parse.quote(jfilter) + "&perPage=500")
|
||
|
|
print(f" Deleting {len(lines['items'])} lines referencing JEISA000...")
|
||
|
|
for l in lines["items"]:
|
||
|
|
delete(f"/api/collections/JEI_invoice_lines/records/{l['id']}")
|
||
|
|
delete(f"/api/collections/JEI_stock_codes/records/{jid}")
|
||
|
|
print(f" Deleted JEISA000 stock code")
|
||
|
|
else:
|
||
|
|
print("JEISA000 not in PB (already cleaned)")
|
||
|
|
|
||
|
|
# 2. Deduplicate invoice lines
|
||
|
|
print("\nDeduplicating invoice lines...")
|
||
|
|
lines = get("/api/collections/JEI_invoice_lines/records?perPage=5000")
|
||
|
|
print(f" Total lines: {len(lines['items'])}")
|
||
|
|
|
||
|
|
# Group by (invoice, line_no, stock_code)
|
||
|
|
groups = defaultdict(list)
|
||
|
|
for l in lines["items"]:
|
||
|
|
key = (l.get("invoice", ""), l.get("line_no", 0), l.get("stock_code", ""))
|
||
|
|
groups[key].append(l)
|
||
|
|
|
||
|
|
dups_removed = 0
|
||
|
|
for key, items in groups.items():
|
||
|
|
if len(items) > 1:
|
||
|
|
# Keep the first, delete the rest
|
||
|
|
for dup in items[1:]:
|
||
|
|
delete(f"/api/collections/JEI_invoice_lines/records/{dup['id']}")
|
||
|
|
dups_removed += 1
|
||
|
|
|
||
|
|
print(f" Removed {dups_removed} duplicate lines")
|
||
|
|
print(f" Remaining unique lines: {len(lines['items']) - dups_removed}")
|
||
|
|
|
||
|
|
# Final count
|
||
|
|
final = get("/api/collections/JEI_invoice_lines/records?perPage=5000")
|
||
|
|
print(f" Final count: {final['totalItems']}")
|