f315aad14c
4 decimal places in STOC.PRET caused FACT-008 errors during invoicing because pack_facturare.descarca_gestiune does exact price matching. Also add pack_facturare flow analysis documentation. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
495 lines
17 KiB
Python
495 lines
17 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Create inventory notes (note de inventar) in Oracle to populate stock
|
|
for articles from imported GoMag orders.
|
|
|
|
Inserts into: DOCUMENTE, ACT, RUL, STOC (id_set=90103 pattern).
|
|
|
|
Usage:
|
|
python3 scripts/create_inventory_notes.py # dry-run (default)
|
|
python3 scripts/create_inventory_notes.py --apply # apply with confirmation
|
|
python3 scripts/create_inventory_notes.py --apply --yes # skip confirmation
|
|
python3 scripts/create_inventory_notes.py --quantity 5000 --gestiune 1
|
|
"""
|
|
|
|
import argparse
|
|
import sqlite3
|
|
import sys
|
|
from datetime import datetime
|
|
from pathlib import Path
|
|
|
|
import oracledb
|
|
|
|
# ─── Configuration ───────────────────────────────────────────────────────────
|
|
|
|
SCRIPT_DIR = Path(__file__).resolve().parent
|
|
PROJECT_DIR = SCRIPT_DIR.parent
|
|
API_DIR = PROJECT_DIR / "api"
|
|
SQLITE_DB = API_DIR / "data" / "import.db"
|
|
TNS_DIR = str(API_DIR)
|
|
|
|
ORA_USER = "MARIUSM_AUTO"
|
|
ORA_PASSWORD = "ROMFASTSOFT"
|
|
ORA_DSN = "ROA_CENTRAL"
|
|
|
|
# Inventory note constants (from existing cod=1140718 pattern)
|
|
ID_SET = 90103
|
|
ID_FDOC = 51
|
|
ID_UTIL = 8
|
|
ID_SECTIE = 6
|
|
ID_SUCURSALA = 167
|
|
ID_VALUTA = 3
|
|
ID_PARTC = 481
|
|
ID_TIP_RULAJ = 6
|
|
ADAOS_PERCENT = 0.30 # 30% markup
|
|
|
|
# Gestiune defaults (MARFA PA)
|
|
DEFAULT_GESTIUNE = 1
|
|
GEST_CONT = "371"
|
|
GEST_ACONT = "816"
|
|
|
|
|
|
# ─── Oracle helpers ──────────────────────────────────────────────────────────
|
|
|
|
def get_oracle_conn():
|
|
return oracledb.connect(
|
|
user=ORA_USER, password=ORA_PASSWORD,
|
|
dsn=ORA_DSN, config_dir=TNS_DIR
|
|
)
|
|
|
|
|
|
# ─── SQLite: get articles from imported orders ──────────────────────────────
|
|
|
|
def get_all_skus_from_sqlite():
|
|
"""Get ALL distinct SKUs from imported orders (regardless of mapping_status)."""
|
|
conn = sqlite3.connect(str(SQLITE_DB))
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("""
|
|
SELECT DISTINCT oi.sku
|
|
FROM order_items oi
|
|
JOIN orders o ON o.order_number = oi.order_number
|
|
WHERE o.status = 'IMPORTED'
|
|
""")
|
|
skus = {row[0] for row in cur.fetchall()}
|
|
conn.close()
|
|
return skus
|
|
|
|
|
|
# ─── Oracle: resolve SKUs to articles ────────────────────────────────────────
|
|
|
|
def resolve_articles(ora_conn, all_skus):
|
|
"""Resolve SKUs to {codmat: {id_articol, cont, codmat}} via Oracle.
|
|
Tries both mapped (ARTICOLE_TERTI) and direct (NOM_ARTICOLE) lookups.
|
|
"""
|
|
articles = {} # codmat -> {id_articol, cont, codmat}
|
|
cur = ora_conn.cursor()
|
|
sku_list = list(all_skus)
|
|
|
|
# 1. Mapped: SKU -> codmat via articole_terti (priority)
|
|
placeholders = ",".join(f":m{i}" for i in range(len(sku_list)))
|
|
binds = {f"m{i}": sku for i, sku in enumerate(sku_list)}
|
|
cur.execute(f"""
|
|
SELECT at.codmat, na.id_articol, na.cont
|
|
FROM articole_terti at
|
|
JOIN nom_articole na ON na.codmat = at.codmat
|
|
AND na.sters = 0 AND na.inactiv = 0
|
|
WHERE at.sku IN ({placeholders})
|
|
AND at.activ = 1 AND at.sters = 0
|
|
""", binds)
|
|
|
|
mapped_skus = set()
|
|
for codmat, id_articol, cont in cur:
|
|
articles[codmat] = {
|
|
"id_articol": id_articol, "cont": cont, "codmat": codmat
|
|
}
|
|
|
|
# Find which SKUs were resolved via mapping
|
|
cur.execute(f"""
|
|
SELECT DISTINCT at.sku FROM articole_terti at
|
|
WHERE at.sku IN ({placeholders}) AND at.activ = 1 AND at.sters = 0
|
|
""", binds)
|
|
mapped_skus = {row[0] for row in cur}
|
|
|
|
# 2. Direct: remaining SKUs where SKU = codmat
|
|
remaining = all_skus - mapped_skus
|
|
if remaining:
|
|
rem_list = list(remaining)
|
|
placeholders = ",".join(f":s{i}" for i in range(len(rem_list)))
|
|
binds = {f"s{i}": sku for i, sku in enumerate(rem_list)}
|
|
cur.execute(f"""
|
|
SELECT codmat, id_articol, cont
|
|
FROM nom_articole
|
|
WHERE codmat IN ({placeholders})
|
|
AND sters = 0 AND inactiv = 0
|
|
""", binds)
|
|
for codmat, id_articol, cont in cur:
|
|
if codmat not in articles:
|
|
articles[codmat] = {
|
|
"id_articol": id_articol, "cont": cont, "codmat": codmat
|
|
}
|
|
|
|
return articles
|
|
|
|
|
|
def get_prices(ora_conn, articles):
|
|
"""Get sale prices from CRM_POLITICI_PRET_ART for each article.
|
|
Returns {id_articol: {pret_vanzare, proc_tvav}}
|
|
"""
|
|
if not articles:
|
|
return {}
|
|
|
|
cur = ora_conn.cursor()
|
|
id_articols = [a["id_articol"] for a in articles.values()]
|
|
placeholders = ",".join(f":a{i}" for i in range(len(id_articols)))
|
|
binds = {f"a{i}": aid for i, aid in enumerate(id_articols)}
|
|
|
|
cur.execute(f"""
|
|
SELECT pa.id_articol, pa.pret, pa.proc_tvav
|
|
FROM crm_politici_pret_art pa
|
|
WHERE pa.id_articol IN ({placeholders})
|
|
AND pa.pret > 0
|
|
AND ROWNUM <= 1000
|
|
""", binds)
|
|
|
|
prices = {}
|
|
for id_articol, pret, proc_tvav in cur:
|
|
# Keep first non-zero price found
|
|
if id_articol not in prices:
|
|
prices[id_articol] = {
|
|
"pret_vanzare": float(pret),
|
|
"proc_tvav": float(proc_tvav) if proc_tvav else 1.19
|
|
}
|
|
|
|
return prices
|
|
|
|
|
|
def get_current_stock(ora_conn, articles, gestiune, year, month):
|
|
"""Check current stock levels. Returns {id_articol: available_qty}."""
|
|
if not articles:
|
|
return {}
|
|
|
|
cur = ora_conn.cursor()
|
|
id_articols = [a["id_articol"] for a in articles.values()]
|
|
placeholders = ",".join(f":a{i}" for i in range(len(id_articols)))
|
|
binds = {f"a{i}": aid for i, aid in enumerate(id_articols)}
|
|
binds["gest"] = gestiune
|
|
binds["an"] = year
|
|
binds["luna"] = month
|
|
|
|
cur.execute(f"""
|
|
SELECT id_articol, NVL(cants,0) + NVL(cant,0) - NVL(cante,0) as disponibil
|
|
FROM stoc
|
|
WHERE id_articol IN ({placeholders})
|
|
AND id_gestiune = :gest AND an = :an AND luna = :luna
|
|
""", binds)
|
|
|
|
stock = {}
|
|
for id_articol, disponibil in cur:
|
|
stock[id_articol] = float(disponibil)
|
|
|
|
return stock
|
|
|
|
|
|
# ─── Oracle: create inventory note ──────────────────────────────────────────
|
|
|
|
def create_inventory_note(ora_conn, articles_to_insert, quantity, gestiune, year, month):
|
|
"""Insert DOCUMENTE + ACT + RUL + STOC for inventory note."""
|
|
cur = ora_conn.cursor()
|
|
now = datetime.now()
|
|
today = now.replace(hour=0, minute=0, second=0, microsecond=0)
|
|
|
|
# Get sequences
|
|
cur.execute("SELECT SEQ_COD.NEXTVAL FROM dual")
|
|
cod = cur.fetchone()[0]
|
|
|
|
cur.execute("SELECT SEQ_IDFACT.NEXTVAL FROM dual")
|
|
id_fact = cur.fetchone()[0]
|
|
|
|
# NNIR pattern: YYYYMM + 4-digit seq
|
|
cur.execute("SELECT MAX(nnir) FROM act WHERE an = :an AND luna = :luna",
|
|
{"an": year, "luna": month})
|
|
max_nnir = cur.fetchone()[0] or 0
|
|
nnir = max_nnir + 1
|
|
|
|
# NRACT: use a simple incrementing number
|
|
cur.execute("SELECT MAX(nract) FROM act WHERE an = :an AND luna = :luna AND id_set = :s",
|
|
{"an": year, "luna": month, "s": ID_SET})
|
|
max_nract = cur.fetchone()[0] or 0
|
|
nract = max_nract + 1
|
|
|
|
# 1. INSERT DOCUMENTE
|
|
cur.execute("""
|
|
INSERT INTO documente (id_doc, dataora, id_util, sters, tva_incasare,
|
|
nract, dataact, id_set, dataireg)
|
|
VALUES (:id_doc, :dataora, :id_util, 0, 1,
|
|
:nract, :dataact, :id_set, :dataireg)
|
|
""", {
|
|
"id_doc": id_fact,
|
|
"dataora": now,
|
|
"id_util": ID_UTIL,
|
|
"nract": nract,
|
|
"dataact": today,
|
|
"id_set": ID_SET,
|
|
"dataireg": today,
|
|
})
|
|
|
|
inserted_count = 0
|
|
for art in articles_to_insert:
|
|
pret = art["pret"]
|
|
proc_tvav = art["proc_tvav"]
|
|
suma = -(quantity * pret)
|
|
|
|
# 2. INSERT ACT
|
|
cur.execute("""
|
|
INSERT INTO act (cod, luna, an, dataireg, nract, dataact,
|
|
scd, ascd, scc, ascc, suma,
|
|
nnir, id_util, dataora, id_sectie, id_set,
|
|
id_fact, id_partc, id_sucursala, id_fdoc,
|
|
id_gestout, id_valuta)
|
|
VALUES (:cod, :luna, :an, :dataireg, :nract, :dataact,
|
|
'607', '7', :scc, :ascc, :suma,
|
|
:nnir, :id_util, :dataora, :id_sectie, :id_set,
|
|
:id_fact, :id_partc, :id_sucursala, :id_fdoc,
|
|
:id_gestout, :id_valuta)
|
|
""", {
|
|
"cod": cod,
|
|
"luna": month,
|
|
"an": year,
|
|
"dataireg": today,
|
|
"nract": nract,
|
|
"dataact": today,
|
|
"scc": GEST_CONT,
|
|
"ascc": GEST_ACONT,
|
|
"suma": suma,
|
|
"nnir": nnir,
|
|
"id_util": ID_UTIL,
|
|
"dataora": now,
|
|
"id_sectie": ID_SECTIE,
|
|
"id_set": ID_SET,
|
|
"id_fact": id_fact,
|
|
"id_partc": ID_PARTC,
|
|
"id_sucursala": ID_SUCURSALA,
|
|
"id_fdoc": ID_FDOC,
|
|
"id_gestout": gestiune,
|
|
"id_valuta": ID_VALUTA,
|
|
})
|
|
|
|
# 3. INSERT RUL
|
|
cur.execute("""
|
|
INSERT INTO rul (cod, an, luna, nnir, id_articol, id_gestiune,
|
|
pret, cante, cont, acont,
|
|
dataact, dataout, id_util, dataora,
|
|
id_fact, proc_tvav, id_tip_rulaj, id_set,
|
|
id_sucursala, nract, id_valuta)
|
|
VALUES (:cod, :an, :luna, :nnir, :id_articol, :id_gestiune,
|
|
:pret, :cante, :cont, :acont,
|
|
:dataact, :dataout, :id_util, :dataora,
|
|
:id_fact, :proc_tvav, :id_tip_rulaj, :id_set,
|
|
:id_sucursala, :nract, :id_valuta)
|
|
""", {
|
|
"cod": cod,
|
|
"an": year,
|
|
"luna": month,
|
|
"nnir": nnir,
|
|
"id_articol": art["id_articol"],
|
|
"id_gestiune": gestiune,
|
|
"pret": pret,
|
|
"cante": -quantity,
|
|
"cont": GEST_CONT,
|
|
"acont": GEST_ACONT,
|
|
"dataact": today,
|
|
"dataout": today,
|
|
"id_util": ID_UTIL,
|
|
"dataora": now,
|
|
"id_fact": id_fact,
|
|
"proc_tvav": proc_tvav,
|
|
"id_tip_rulaj": ID_TIP_RULAJ,
|
|
"id_set": ID_SET,
|
|
"id_sucursala": ID_SUCURSALA,
|
|
"nract": nract,
|
|
"id_valuta": ID_VALUTA,
|
|
})
|
|
|
|
# 4. MERGE STOC
|
|
cur.execute("""
|
|
MERGE INTO stoc s
|
|
USING (SELECT :id_articol AS id_articol, :id_gestiune AS id_gestiune,
|
|
:an AS an, :luna AS luna FROM dual) src
|
|
ON (s.id_articol = src.id_articol
|
|
AND s.id_gestiune = src.id_gestiune
|
|
AND s.an = src.an AND s.luna = src.luna
|
|
AND s.pret = :pret AND s.cont = :cont AND s.acont = :acont)
|
|
WHEN MATCHED THEN
|
|
UPDATE SET s.cante = s.cante + (:cante),
|
|
s.dataora = :dataora,
|
|
s.dataout = :dataout
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (id_articol, id_gestiune, an, luna, pret, cont, acont,
|
|
cante, dataora, datain, dataout, proc_tvav,
|
|
id_sucursala, id_valuta)
|
|
VALUES (:id_articol, :id_gestiune, :an, :luna, :pret, :cont, :acont,
|
|
:cante, :dataora, :datain, :dataout, :proc_tvav,
|
|
:id_sucursala, :id_valuta)
|
|
""", {
|
|
"id_articol": art["id_articol"],
|
|
"id_gestiune": gestiune,
|
|
"an": year,
|
|
"luna": month,
|
|
"pret": pret,
|
|
"cont": GEST_CONT,
|
|
"acont": GEST_ACONT,
|
|
"cante": -quantity,
|
|
"dataora": now,
|
|
"datain": today,
|
|
"dataout": today,
|
|
"proc_tvav": proc_tvav,
|
|
"id_sucursala": ID_SUCURSALA,
|
|
"id_valuta": ID_VALUTA,
|
|
})
|
|
|
|
inserted_count += 1
|
|
|
|
ora_conn.commit()
|
|
return cod, id_fact, nnir, nract, inserted_count
|
|
|
|
|
|
# ─── Main ────────────────────────────────────────────────────────────────────
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(
|
|
description="Create inventory notes for GoMag order articles"
|
|
)
|
|
parser.add_argument("--quantity", type=int, default=10000,
|
|
help="Quantity per article (default: 10000)")
|
|
parser.add_argument("--gestiune", type=int, default=DEFAULT_GESTIUNE,
|
|
help=f"Warehouse ID (default: {DEFAULT_GESTIUNE})")
|
|
parser.add_argument("--apply", action="store_true",
|
|
help="Apply changes (default: dry-run)")
|
|
parser.add_argument("--yes", action="store_true",
|
|
help="Skip confirmation prompt")
|
|
args = parser.parse_args()
|
|
|
|
now = datetime.now()
|
|
year, month = now.year, now.month
|
|
|
|
print(f"=== Create Inventory Notes (id_set={ID_SET}) ===")
|
|
print(f"Gestiune: {args.gestiune}, Quantity: {args.quantity}")
|
|
print(f"Period: {year}/{month:02d}")
|
|
print()
|
|
|
|
# 1. Get SKUs from SQLite
|
|
if not SQLITE_DB.exists():
|
|
print(f"ERROR: SQLite DB not found at {SQLITE_DB}")
|
|
sys.exit(1)
|
|
|
|
all_skus = get_all_skus_from_sqlite()
|
|
print(f"SKUs from imported orders: {len(all_skus)} total")
|
|
|
|
if not all_skus:
|
|
print("No SKUs found. Nothing to do.")
|
|
return
|
|
|
|
# 2. Connect to Oracle and resolve ALL SKUs (mapped + direct)
|
|
ora_conn = get_oracle_conn()
|
|
|
|
articles = resolve_articles(ora_conn, all_skus)
|
|
print(f"Resolved to {len(articles)} unique articles (codmat)")
|
|
print(f"Unresolved: {len(all_skus) - len(articles)} SKUs (missing from Oracle)")
|
|
|
|
if not articles:
|
|
print("No articles resolved. Nothing to do.")
|
|
ora_conn.close()
|
|
return
|
|
|
|
# 3. Get prices
|
|
prices = get_prices(ora_conn, articles)
|
|
|
|
# 4. Check current stock
|
|
stock = get_current_stock(ora_conn, articles, args.gestiune, year, month)
|
|
|
|
# 5. Build list of articles to insert
|
|
articles_to_insert = []
|
|
skipped = []
|
|
|
|
for codmat, art in sorted(articles.items()):
|
|
id_articol = art["id_articol"]
|
|
current = stock.get(id_articol, 0)
|
|
|
|
if current >= args.quantity:
|
|
skipped.append((codmat, current))
|
|
continue
|
|
|
|
price_info = prices.get(id_articol, {})
|
|
pret_vanzare = price_info.get("pret_vanzare", 1.30)
|
|
proc_tvav = price_info.get("proc_tvav", 1.19)
|
|
pret_achizitie = round(pret_vanzare / (1 + ADAOS_PERCENT), 2)
|
|
|
|
articles_to_insert.append({
|
|
"codmat": codmat,
|
|
"id_articol": id_articol,
|
|
"pret": pret_achizitie,
|
|
"pret_vanzare": pret_vanzare,
|
|
"proc_tvav": proc_tvav,
|
|
"current_stock": current,
|
|
})
|
|
|
|
# 6. Display summary
|
|
print()
|
|
if skipped:
|
|
print(f"Skipped {len(skipped)} articles (already have >= {args.quantity} stock):")
|
|
for codmat, qty in skipped[:5]:
|
|
print(f" {codmat}: {qty:.0f}")
|
|
if len(skipped) > 5:
|
|
print(f" ... and {len(skipped) - 5} more")
|
|
print()
|
|
|
|
if not articles_to_insert:
|
|
print("All articles already have sufficient stock. Nothing to do.")
|
|
ora_conn.close()
|
|
return
|
|
|
|
print(f"Articles to create stock for: {len(articles_to_insert)}")
|
|
print(f"{'CODMAT':<25} {'ID_ARTICOL':>12} {'PRET_ACH':>10} {'PRET_VANZ':>10} {'TVA':>5} {'STOC_ACT':>10}")
|
|
print("-" * 80)
|
|
for art in articles_to_insert:
|
|
tva_pct = round((art["proc_tvav"] - 1) * 100)
|
|
print(f"{art['codmat']:<25} {art['id_articol']:>12} "
|
|
f"{art['pret']:>10.2f} {art['pret_vanzare']:>10.2f} "
|
|
f"{tva_pct:>4}% {art['current_stock']:>10.0f}")
|
|
print("-" * 80)
|
|
print(f"Total: {len(articles_to_insert)} articles x {args.quantity} qty each")
|
|
|
|
if not args.apply:
|
|
print("\n[DRY-RUN] No changes made. Use --apply to execute.")
|
|
ora_conn.close()
|
|
return
|
|
|
|
# 7. Confirm and apply
|
|
if not args.yes:
|
|
answer = input(f"\nInsert {len(articles_to_insert)} articles with qty={args.quantity}? [y/N] ")
|
|
if answer.lower() != "y":
|
|
print("Cancelled.")
|
|
ora_conn.close()
|
|
return
|
|
|
|
cod, id_fact, nnir, nract, count = create_inventory_note(
|
|
ora_conn, articles_to_insert, args.quantity, args.gestiune, year, month
|
|
)
|
|
|
|
print(f"\nDone! Created inventory note:")
|
|
print(f" COD = {cod}")
|
|
print(f" ID_FACT (documente.id_doc) = {id_fact}")
|
|
print(f" NNIR = {nnir}")
|
|
print(f" NRACT = {nract}")
|
|
print(f" Articles inserted: {count}")
|
|
print(f"\nVerify:")
|
|
print(f" SELECT * FROM act WHERE cod = {cod};")
|
|
print(f" SELECT * FROM rul WHERE cod = {cod};")
|
|
|
|
ora_conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|