Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
1.8 kB
0
Indexable
Never
import cx_Oracle
import configparser
import sys
from pandas import read_sql as pd_read_sql
from json import loads as json_loads

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

config=configparser.ConfigParser()
config.read(r'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)
cursor = conn.cursor()
data = sys.argv[1]
#data ='1680307200'

invoice_num_query = pd_read_sql(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}' ",con = conn)

invoice_num_cursor = conn.cursor()
invoice_num_cursor.execute(invoice_num_query)
for invoice_num in invoice_num_cursor:
    invoice_num = invoice_num[0]
    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"
    # print(bill_lines_query)

    bill_lines_cursor = conn.cursor()
    bill_lines_cursor.execute(bill_lines_query)

    if bill_lines_cursor.fetchone():
        pass
    else:
        print("Data issue for the :" + invoice_num)