Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
1.8 kB
2
Indexable
Never
import cx_Oracle
import configparser
import sys
import pandas as pd

# cx_Oracle.init_oracle_client(lib_dir=r"C:\Users\2009319.INDIA\Documents\ETPI\instantclient_21_6")

config = configparser.ConfigParser()
config.read(r'/app/scripts/billingIpBatch/scripts/PaymentFailureDataIssue.config')

username = config.get('config', 'username')
password = config.get('config', 'password')
coun = config.get('config', 'connectstring')

# coun = cx_Oracle.makedsn(host='192.168.217.206', port='1596', sid='hobs2stdb')
# username = 'hobread'
# password = 'hobread'
conn = cx_Oracle.connect(username, password, coun)
data = sys.argv[1]
# data ='1680307200'

invoice_num_query = f"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>='{data}' "
invoice_nums = pd.read_sql(invoice_num_query, conn)['INVOICE_NUM']

for invoice_num in invoice_nums:
    bill_lines_query = f"""
        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
    """
    bill_lines = pd.read_sql(bill_lines_query, conn)

    if not bill_lines.empty:
        pass
    else:
        print("Data issue for the :" + invoice_num)