def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("BankingProductdepositRate (04) -> Initialized")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment(
"BankingProductdepositRate (04) -> Connection Status: " +
str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = ['TnS_Rate.csv']
transform.df_cols = [
'Product ID (Mandatory)',
'CUA Effective From (Mandatory) Date/Time',
'Rate Type (Mandatory)', 'Rate (Mandatory)',
'calculationFrequency (Auto Generated)',
'applicationFrequency (Auto Generated)', 'Additional Details',
'Additional Information', 'Additional Information URL'
]
transform.joinerColumn = 'Rate Type (Mandatory)'
tableQuery = (
"select Productid, lastUpdated, depositRateType, "
"rate, calculationFrequency,applicationFrequency, "
"additionalValue, additionalInfo, additionalInfoUri from bankingproductdepositRate"
).lower()
masterQuery = (
"SELECT ID, DEPOSITRATETYPE FROM MASTER_DEPOSITRATETYPE").lower()
insertQuery = (
"INSERT INTO bankingproductdepositRate (depositRateId, Productid, lastUpdated, "
"depositRateType, rate, calculationFrequency,applicationFrequency, additionalValue, "
"additionalInfo, additionalInfoUri, createdOn, createdBy, systemCreatedOn, systemCreatedBy ) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
).lower()
insertQuery = (
"insert into bankingproductdepositrate (depositrateid, productid, lastupdated, depositratetype, "
"rate, calculationfrequency,applicationfrequency, additionalvalue, additionalinfo, additionalinfouri, "
"createdon, createdby, systemcreatedon, systemcreatedby ) "
"values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) "
"select * from (select %s as a, %s as productid, %s lastupdated, %s as b, %s as c, %s as d, %s as e, "
"%s as f, %s as g, %s as h, %s as i, %s as j, %s as k, %s as l from dual) a "
"where (a.productid, a.lastupdated, a.b) not in (select productid, lastUpdated, depositratetype from bankingproductdepositrate)"
)
updateQuery = (
"update bankingproductdepositRate set depositRateType = %s, "
"rate = %s, calculationFrequency = %s, applicationFrequency = %s, "
"additionalValue = %s, systemCreatedOn = %s, systemCreatedBy = %s "
"where Productid = %s and depositRateId = %s and lastupdated = %s")
log.logComment("BankingProductdepositRate (04) -> Transforming...")
dataOutput = transform.transformData(masterQuery, tableQuery)
insertData = dataOutput[0]
insertDataFinal = []
for d in insertData:
y = d.split("|")
try:
y[3] = str("{:.2f}".format(float(d.split("|")[3]) * 1))
except:
y[3] = '0'
#y[4] = str(float(d.split("|")[4]) * 100)
#y = y + [conn.updatedDate, conn.createdBy, conn.updatedDate, conn.createdBy]
y = '|'.join(y)
insertDataFinal.append(y)
seqQuery = "select max(cast(depositRateId as decimal)) + 1 from bankingproductdepositRate".lower(
)
seq = conn.executeQuery(seqQuery)[0][0]
if seq == None:
seq = 1
param = {
'name': 'bankingProductDeositRate',
'insertQuery': insertQuery,
'insertSelection': insertDataFinal,
'size': 10,
'seq': seq
}
from time import sleep
sleep(3)
if self.FailInd == 0:
log.logComment("BankingProductdepositRate (04) -> Inserting data")
#transform.insert(**param)
#transform.insert('bankingProductDeositRate', insertQuery, 8, masterQuery, tableQuery, update = False)
conn.insertQuery(**param)
conn.closeConnector()
log.logComment(
"BankingProductdepositRate (04) -> Connector Closed")
else:
log.logComment(
"BankingProductdepositRate (04) -> Inserting data -> Failed")
def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("BankingProductFeature (09) -> Initialized")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment("BankingProductFeature (09) -> Connection Status: " + str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = ['CCC_Feature.csv', 'TeD_Feature.csv', 'TnS_Feature.csv']
transform.df_cols = ['Product ID (Mandatory)', 'CUA Effective From (Mandatory) Date/Time',
'Feature Type', 'Value/Details', 'Additional Information',
'Additional Information URL'
]
transform.joinerColumn = 'Feature Type'
tableQuery = ("select Productid, lastUpdated, featureType, additionalValue, "
"additionalInfo, additionalInfoUri from BankingProductFeature").lower()
masterQuery = ("SELECT ID, FEATURETYPE FROM MASTER_Features").lower()
insertQuery = ("INSERT INTO BankingProductFeature (featureId, Productid, lastUpdated, "
"featureType, additionalValue, additionalInfo, additionalInfoUri, "
"createdOn, createdBy, systemCreatedOn, systemCreatedBy ) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)").lower()
log.logComment("BankingProductFeature (09) -> Transforming...")
dataOutput = transform.transformData(masterQuery, tableQuery)
dataOutput = dataOutput[0]
seqQuery = "select max(cast(featureId as Decimal)) from BankingProductFeature".lower()
seq = conn.executeQuery(seqQuery)[0][0]
if seq == None:
seq = 1
param = {
'name' : 'bankingProductDeositRate',
'insertQuery' : insertQuery,
'insertSelection' : dataOutput,
'size' : 7,
'seq' : seq
}
if self.FailInd == 0:
log.logComment("BankingProductFeature (09) -> Inserting data")
#transform.insert(**param)
#transform.insert('bankingProductDeositRate', insertQuery, 8, masterQuery, tableQuery, update = False)
conn.insertQuery(**param)
conn.closeConnector()
log.logComment("BankingProductFeature (09) -> Connector Closed")
else:
log.logComment("BankingProductFeature (09) -> Inserting data -> Failed")
def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("BankingProductConstraint (03) -> Initialized")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment("BankingProductConstraint (03) -> Connection Status: " +
str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = [
'TeD_Constraints.csv', 'CCC_Constraints.csv', 'TnS_Constraints.csv'
]
transform.df_cols = [
'Product ID (Mandatory)',
'CUA Effective From (Mandatory) Date/Time', 'Constraint Type',
'Value/Details', 'Additional Information',
'Additional Information URL'
]
transform.joinerColumn = 'Constraint Type'
tableQuery = (
"select Productid, lastUpdated, constraintType, additionalValue, "
"additionalInfo, additionalInfoUri from bankingProductConstraint"
).lower()
masterQuery = (
"SELECT ID, CONSTRAINTTYPE FROM MASTER_CONSTRAINTS").lower()
insertQuery = (
"INSERT INTO bankingProductConstraint (constraintId, Productid, lastUpdated, "
"constraintType, additionalValue, additionalInfo, additionalInfoUri, "
"createdOn, createdBy, systemCreatedOn, systemCreatedBy ) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)").lower()
insertQuery = (
"insert into bankingproductconstraint (constraintid, productid, lastupdated, constrainttype, "
"additionalvalue, additionalinfo, additionalinfouri, createdon, createdby, systemcreatedon, "
"systemcreatedby ) "
"select * from (select %s as a, %s as productid, %s lastupdated, %s as b, %s as c, %s as d, %s as e, "
"%s as f, %s as g, %s as h, %s as i from dual) a "
"where (a.productid, a.lastupdated, a.b, a.c) not in (select productid, lastUpdated, constrainttype, additionalvalue from bankingproductconstraint)"
).lower()
log.logComment("BankingProductConstraint (03) -> Transforming...")
dataOutput = transform.transformData(masterQuery, tableQuery)
dataOutput = dataOutput[0]
#dataOutput = list(set(dataOutput))
seqQuery = "select max(constraintId) from bankingProductConstraint"
seq = conn.executeQuery(seqQuery)[0][0]
if seq == None:
seq = 1
param = {
'name': 'bankingProductDeositRate',
'insertQuery': insertQuery,
'insertSelection': dataOutput,
'size': 7,
'seq': seq
}
if self.FailInd == 0:
log.logComment("BankingProductConstraint (03) -> Inserting data")
#transform.insert(**param)
#transform.insert('bankingProductDeositRate', insertQuery, 8, masterQuery, tableQuery, update = False)
conn.insertQuery(**param)
conn.closeConnector()
log.logComment(
"BankingProductConstraint (03) -> Connector Closed")
else:
log.logComment(
"BankingProductConstraint (03) -> Inserting data -> Failed")
def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("BankingProductLendingRate (011) -> Initialized")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment(
"BankingProductLendingRate (011) -> Connection Status: " +
str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = ['CCC_Rate.csv']
transform.df_cols = [
'Product ID (Mandatory)',
'CUA Effective From (Mandatory) Date/Time',
'Rate Type (Mandatory)', 'Rate (Mandatory)', 'Comparison Rate',
'calculationFrequency (Auto Generated)',
'Additional Value (Auto Generated)', 'Additional Details',
'Additional Information', 'Additional Information URL'
]
transform.joinerColumn = 'Rate Type (Mandatory)'
tableQuery = (
"SELECT Productid, lastUpdated, lendingRateType, rate, "
"comparisonRate, calculationFrequency, applicationFrequency, "
"additionalValue, additionalInfo, additionalInfoUri "
"FROM BANKINGPRODUCTLENDINGRATE").lower()
masterQuery = (
"SELECT ID, LENDINGRATETYPE FROM MASTER_LENDINGRATETYPE").lower()
insertQuery = (
"INSERT INTO BANKINGPRODUCTLENDINGRATE "
"( lendingRateId, Productid, lastUpdated, lendingRateType, rate, "
"comparisonRate, calculationFrequency, applicationFrequency, "
"additionalValue, additionalInfo, additionalInfoUri, createdOn, "
"createdBy, systemCreatedOn, systemCreatedBy ) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
).lower()
log.logComment("BankingProductLendingRate (011) -> Transforming...")
dataOutput = transform.transformData(masterQuery, tableQuery)
insertData = dataOutput[0]
insertDataFinal = []
for d in insertData:
y = d.split("|")
if y[4] == '':
y[4] = '0'
else:
y[4] = str("{:.2f}".format(float(d.split("|")[4])))
#y[4] = str(float(d.split("|")[4]) * 100)
#y = y + [conn.updatedDate, conn.createdBy, conn.updatedDate, conn.createdBy]
y = '|'.join(y)
insertDataFinal.append(y)
seqQuery = "select max(lendingRateId) from BANKINGPRODUCTLENDINGRATE".lower(
)
seq = conn.executeQuery(seqQuery)[0][0]
if seq == None:
seq = 1
param = {
'name': 'bankingProductDeositRate',
'insertQuery': insertQuery,
'insertSelection': insertDataFinal,
'size': 11,
'seq': seq
}
if self.FailInd == 0:
log.logComment(
"BankingProductLendingRate (011) -> Inserting data")
#transform.insert(**param)
conn.insertQuery(**param)
#transform.insert('bankingProductDeositRate', insertQuery, 8, masterQuery, tableQuery, update = False)
conn.closeConnector()
log.logComment(
"BankingProductLendingRate (011) -> Connector Closed")
else:
log.logComment(
"BankingProductLendingRate (011) -> Inserting data -> Failed"
)
def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("BankingProductFee (010) -> Initialized")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment("BankingProductFee (010) -> Connection Status: " + str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = ['CCC_Fees.csv', 'TeD_Fees.csv', 'TnS_Fees.csv']
transform.df_cols = ['Product ID (Mandatory)', 'CUA Effective From (Mandatory) Date/Time',
'Fee type', 'Fee Name', 'Amount (one of these is mandatory)',
'Balance Rate (one of these is mandatory)',
'Transaction Rate (one of these is mandatory)',
'Accrued Rate (one of these is mandatory)', 'Accrual Frequency',
'Currency (Default)', 'Additionalvalue (AutoGenerated)',
'Additional Information', 'Additional Information URL'
]
transform.joinerColumn = 'Fee Name'
tableQuery = ("select productid, lastUpdated, feeType, name, amount, "
"balanceRate, transactionRate, accruedRate, accrualFrequency, "
"currency, additionalValue, additionalInfo, additionalInfoUri "
"from bankingproductfee").lower()
masterQuery = ("select ID, FeeType FROM MASTER_FEES").lower()
insertQuery = ("INSERT INTO bankingproductfee"
"(feeid, productid, lastUpdated, feeType, name, amount, "
"balanceRate, transactionRate, accruedRate, accrualFrequency, "
"currency, additionalValue, additionalInfo, additionalInfoUri, "
"createdOn, createdBy, systemCreatedOn, systemCreatedBy) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)").lower()
log.logComment("BankingProductFee (010) -> Transforming...")
dataOutput = transform.transformData(masterQuery, tableQuery)
insertData = dataOutput[0]
insertDataFinal = []
for d in insertData:
y = d.split("|")
try:
y[5] = str(float(d.split("|")[5]) * 1)
except:
y[5] = '0'
try:
y[6] = str(float(d.split("|")[6]) * 1)
except:
y[6] = '0'
try:
y[7] = str(float(d.split("|")[7]) * 1)
except:
y[7] = '0'
try:
y[4] = str(float(d.split("|")[4]) * 1)
except:
y[4] = '0'
y = '|'.join(y)
insertDataFinal.append(y)
seqQuery = "select max(cast(feeid as decimal)) + 1 from bankingproductfee".lower()
seq = conn.executeQuery(seqQuery)[0][0]
if seq == None:
seq = 1
param = {
'name' : 'bankingProductDeositRate',
'insertQuery' : insertQuery,
'insertSelection' : insertDataFinal,
'size' : 14,
'seq' : seq
}
if self.FailInd == 0:
log.logComment("BankingProductFee (010) -> Inserting data")
#transform.insert(**param)
#transform.insert('bankingProductDeositRate', insertQuery, 8, masterQuery, tableQuery, update = False)
conn.insertQuery(**param)
conn.closeConnector()
log.logComment("BankingProductFee (010) -> Connector Closed")
else:
log.logComment("BankingProductFee (010) -> Inserting data -> Failed")
def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("Product (025) -> Initialized")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment("Product (025) -> Connection Status: " + str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = ['TeD_Products.csv', 'TnS_Products.csv', 'CCC_Products.csv']
transform.df_cols = ['Product ID (Mandatory)', 'productCategory (Mandatory)', 'CUA Effective From (Mandatory) Date/Time',
'Effective From', 'Effective To', 'Product Name (Mandatory)', 'Description', 'Apply Here URL (Mandatory)',
'isTailored (Mandatory)', 'Overview URL', 'Terms URL', 'Eligibility URL', 'Fees And PricingURL',
'Bundle URL'
]
transform.joinerColumn = 'productCategory (Mandatory)'
tableQuery = ("select productId, productCategory, lastUpdated, "
"effectiveFrom, effectiveTo, name, description, "
"applicationUri, isTailored, "
"overviewUri, termsUri, eligibilityUri, feesAndPricingUri, "
"bundleUri from product").lower()
masterQuery = ("SELECT ID, PRODUCTCATEGORY FROM PRODUCT_CATEGORY").lower()
insertQuery = ("INSERT INTO product (productId, productCategory, lastUpdated, "
"effectiveFrom, effectiveTo, name, description, applicationUri, isTailored,"
"overviewUri, termsUri, eligibilityUri, feesAndPricingUri, bundleUri) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)").lower()
log.logComment("Product (025) -> Transforming...")
transform.transformData(masterQuery, tableQuery)
param = {
'name' : 'bankingProductDeositRate',
'insertQuery' : insertQuery,
'size' : 14,
'masterQuery' : masterQuery,
'tableQuery' : tableQuery,
'update' : False,
'audit' : False
}
if self.FailInd == 0:
log.logComment("Product (025) -> Inserting data")
transform.insert(**param)
#transform.insert('bankingProductDeositRate', insertQuery, 8, masterQuery, tableQuery, update = False)
conn.executeQuery("update product set brand = 'CUA', brandname = 'CUA' where brand is null", returnVal = False)
conn.closeConnector()
log.logComment("Product (025) -> Connector Closed")
else:
log.logComment("Product (025) -> Inserting data -> Failed")