roa2web-service-auto/backend/scripts/whatsapp_import/save_to_oracle.py

#!/usr/bin/env python3
"""
Script pentru salvare note contabile în Oracle din date OCR.
"""
import sys
sys.path.insert(0, "/workspace/roa2web")

import oracledb
from datetime import datetime
from decimal import Decimal

ORACLE_CONFIG = {
    "user": "CONTAFIN_ORACLE",
    "password": "ROMFASTSOFT",
    "dsn": "10.0.20.121:1521/ROA"
}

def get_partner_id(cursor, cui: str) -> int:
    """Găsește partener după CUI."""
    cui_clean = cui.upper().replace("RO", "").strip()
    
    cursor.execute(
        "SELECT ID_PART FROM MARIUSM_AUTO.NOM_PARTENERI WHERE COD_FISCAL = :cui",
        cui=cui_clean
    )
    row = cursor.fetchone()
    if row:
        print(f"  Partner găsit: ID={row[0]} pentru CUI={cui_clean}")
        return row[0]
    
    # Încearcă și cu RO prefix
    cursor.execute(
        "SELECT ID_PART FROM MARIUSM_AUTO.NOM_PARTENERI WHERE COD_FISCAL = :cui",
        cui="RO" + cui_clean
    )
    row = cursor.fetchone()
    if row:
        print(f"  Partner găsit: ID={row[0]} pentru CUI=RO{cui_clean}")
        return row[0]
    
    print(f"  ⚠️ Partner NU găsit pentru CUI {cui_clean}, folosim ID=0")
    return 0

def save_bon_achizitie(
    cursor,
    receipt_date: datetime,
    receipt_number: str,
    amount: Decimal,
    tva_amount: Decimal,
    partner_cui: str,
    partner_name: str,
    cont_cheltuiala: str = "6028",
    explicatie: str = "Import OCR"
):
    """
    Salvează bon fiscal în ACT_TEMP.
    Notă: NU apelează PACK_CONTAFIN - doar inserează în staging.
    """
    an = receipt_date.year
    luna = receipt_date.month
    
    # Generează cod unic
    cursor.execute("SELECT NVL(MAX(COD), 0) + 1 FROM MARIUSM_AUTO.ACT WHERE AN = :an AND LUNA = :luna", an=an, luna=luna)
    cod = cursor.fetchone()[0]
    
    id_part = get_partner_id(cursor, partner_cui)
    
    total_cu_tva = float(amount)
    total_fara_tva = float(amount - tva_amount)
    tva = float(tva_amount)
    nract = int(receipt_number) if receipt_number and receipt_number.isdigit() else 0
    
    print(f"\nNote contabile (COD={cod}):")
    print(f"  Data: {receipt_date.date()}, Nr: {nract}")
    print(f"  Fără TVA: {total_fara_tva:.2f}, TVA: {tva:.2f}, Total: {total_cu_tva:.2f}")
    
    # LINIA 1: Cheltuială = Furnizor
    cursor.execute("""
        INSERT INTO MARIUSM_AUTO.ACT_TEMP (
            LUNA, AN, COD, DATAIREG, DATAACT, NRACT,
            EXPLICATIA, SCD, SCC, SUMA, ID_PARTC, ID_UTIL, DATAORA
        ) VALUES (
            :luna, :an, :cod, TRUNC(SYSDATE), :dataact, :nract,
            :expl, :scd, :scc, :suma, :id_part, 0, SYSDATE
        )
    """, 
        luna=luna, an=an, cod=cod, 
        dataact=receipt_date.date(), nract=nract,
        expl=f"{explicatie} - {partner_name}",
        scd=cont_cheltuiala, scc="401", suma=total_fara_tva, id_part=id_part
    )
    print(f"  ✓ {cont_cheltuiala} = 401: {total_fara_tva:.2f}")
    
    # LINIA 2: TVA = Furnizor
    if tva > 0:
        cursor.execute("""
            INSERT INTO MARIUSM_AUTO.ACT_TEMP (
                LUNA, AN, COD, DATAIREG, DATAACT, NRACT,
                EXPLICATIA, SCD, SCC, SUMA, PROC_TVA, ID_PARTC, ID_UTIL, DATAORA
            ) VALUES (
                :luna, :an, :cod, TRUNC(SYSDATE), :dataact, :nract,
                :expl, :scd, :scc, :suma, :proc_tva, :id_part, 0, SYSDATE
            )
        """, 
            luna=luna, an=an, cod=cod, 
            dataact=receipt_date.date(), nract=nract,
            expl=f"TVA {explicatie}",
            scd="4426", scc="401", suma=tva, proc_tva=1.19, id_part=id_part
        )
        print(f"  ✓ 4426 = 401: {tva:.2f}")
    
    # LINIA 3: Furnizor = Casă
    cursor.execute("""
        INSERT INTO MARIUSM_AUTO.ACT_TEMP (
            LUNA, AN, COD, DATAIREG, DATAACT, NRACT,
            EXPLICATIA, SCD, SCC, SUMA, ID_PARTD, ID_UTIL, DATAORA
        ) VALUES (
            :luna, :an, :cod, TRUNC(SYSDATE), :dataact, :nract,
            :expl, :scd, :scc, :suma, :id_part, 0, SYSDATE
        )
    """, 
        luna=luna, an=an, cod=cod, 
        dataact=receipt_date.date(), nract=nract,
        expl=f"Plata {explicatie}",
        scd="401", scc="5311", suma=total_cu_tva, id_part=id_part
    )
    print(f"  ✓ 401 = 5311: {total_cu_tva:.2f}")
    
    return cod

def test_insert():
    """Test inserare în ACT_TEMP."""
    print("="*50)
    print("TEST: Salvare note contabile în Oracle")
    print("="*50)
    print("\nConectare la Oracle TEST...")
    
    conn = oracledb.connect(**ORACLE_CONFIG)
    cursor = conn.cursor()
    print("Conexiune OK!")
    
    # Date de test din OCR
    receipt_date = datetime(2024, 8, 1)
    receipt_number = "1200302"
    amount = Decimal("263.28")
    tva_amount = Decimal("42.04")
    partner_cui = "RO11201891"
    partner_name = "Benzinărie Test"
    
    try:
        cod = save_bon_achizitie(
            cursor,
            receipt_date=receipt_date,
            receipt_number=receipt_number,
            amount=amount,
            tva_amount=tva_amount,
            partner_cui=partner_cui,
            partner_name=partner_name,
            cont_cheltuiala="6022",
            explicatie=f"Bon benzină {receipt_number}"
        )
        
        # Verificare
        cursor.execute("SELECT SCD, SCC, SUMA FROM MARIUSM_AUTO.ACT_TEMP WHERE COD = :cod ORDER BY ROWID", cod=cod)
        print(f"\nVerificare ACT_TEMP (COD={cod}):")
        for row in cursor:
            print(f"  {row[0]} = {row[1]}: {row[2]:.2f}")
        
        # ROLLBACK - doar test
        print("\n⚠️  ROLLBACK (test only)")
        conn.rollback()
        
        print("\n" + "="*50)
        print("✅ TEST REUȘIT!")
        print("="*50)
        
    except Exception as e:
        print(f"\n❌ EROARE: {e}")
        conn.rollback()
        raise
    finally:
        cursor.close()
        conn.close()

if __name__ == "__main__":
    test_insert()