Untitled

import cx_Oracle

# Oracle database connection details
db_username = "your_username"
db_password = "your_password"
db_host = "your_host"
db_port = "your_port"
db_service_name = "your_service_name"

# Query to fetch invoice_num
query1 = """
    select invoice_num from rms_billing.rms_bill 
    where invoice_num is not null 
    and bill_due > 0 
    and invoice_num like 'EST-%' 
    and bill_date >= '1640975400'
"""

# Query to check payment success/failure
query2 = """
    select * from rms_billing.Rms_Billines 
    where bill_Line_Seq_Nbr in (
        select bill_Line_Seq_Nbr 
        from rms_billing.Rms_Billines 
        where invoice_Num = :invoice_num 
        and op_Id = 'ETPI' 
        and bu_Id = 'ENTERPRISE' 
        and price_Type is not null 
        group by bill_Line_Seq_Nbr 
        having round(sum(nvl(charges_With_Round,0.00) + nvl(adjustments,0.00) + nvl(payment_Amount,0.00) + nvl(userfield1,0.00) + nvl(userfield2,0.00)), 2) > 0.00
    )
    and invoice_Num = :invoice_num 
    and op_Id = 'ETPI' 
    and bu_Id = 'ENTERPRISE' 
    order by charges_With_Round desc
"""

# Log file name
log_file = "payment_failures.log"

# Connect to Oracle database
dsn = cx_Oracle.makedsn(db_host, db_port, service_name=db_service_name)
connection = cx_Oracle.connect(user=db_username, password=db_password, dsn=dsn)

try:
    # Execute first query to get invoice_num
    cursor1 = connection.cursor()
    cursor1.execute(query1)
    invoice_nums = [row[0] for row in cursor1.fetchall()]
    cursor1.close()
    
    # Process each invoice_num
    for invoice_num in invoice_nums:
        # Execute second query with invoice_num parameter
        cursor2 = connection.cursor()
        cursor2.execute(query2, invoice_num=invoice_num)
        records = cursor2.fetchall()
        cursor2.close()
        
        if len(records) > 0:
            # Payment success
            print(f"Payment successful for invoice_num: {invoice_num}")
        else:
            # Payment failure
            print(f"Payment failure for invoice_num: {invoice_num}")
            with open(log_file, "a") as log:
                log.write(f"Payment failure for invoice_num: {invoice_num}\n")

    # Count of failed records
    count_failed_records = len([record for record in invoice_nums if len(record) > 0])
    print(f"Count of failed records: {count_failed_records}")
    
except cx_Oracle.Error as error:
    print("Oracle Error:", error)
    
finally:
    # Close the database connection
    connection.close()