def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("Initialized MasterConstraint (015)")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment("MasterConstraint (015) -> Connection Status: " +
str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = [
'CCC_Constraints.csv', 'TeD_Constraints.csv', 'TnS_Constraints.csv'
]
masterQuery = (
"SELECT ID, CONSTRAINTTYPE FROM MASTER_CONSTRAINTS").lower()
insertStatement = ("INSERT INTO MASTER_CONSTRAINTS "
"(ID, CONSTRAINTTYPE) VALUES (%s, %s)").lower()
transform.joinerColumn = 'Constraint Type'
if self.FailInd == 0:
log.logComment("MasterConstraint (015) -> Inserting data")
transform.masterInsert(masterQuery, insertStatement)
log.logComment("MasterConstraint (015) -> Connection Closed")
conn.closeConnector()
else:
log.logComment(
"MasterConstraint (015) -> Inserting data -> Failed")
def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("Initialized MasterLendingRate (022)")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment("MasterLendingRate (022) -> Connection Status: " +
str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = ['CCC_Rate.csv']
masterQuery = (
"SELECT ID, LENDINGRATETYPE FROM MASTER_LENDINGRATETYPE").lower()
insertStatement = ("INSERT INTO MASTER_LENDINGRATETYPE "
"(ID, LENDINGRATETYPE) VALUES (%s, %s)").lower()
transform.joinerColumn = 'Rate Type (Mandatory)'
if self.FailInd == 0:
log.logComment("MasterLendingRate (022) -> Inserting data")
transform.masterInsert(masterQuery, insertStatement)
log.logComment("MasterLendingRate (022) -> Connection Closed")
conn.closeConnector()
else:
log.logComment(
"MasterLendingRate (022) -> Inserting data -> Failed")
def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("Initialized ProductCategory (026)")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment("ProductCategory (026) -> Connection Status: " +
str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = [
'CCC_Products.csv', 'TeD_Products.csv', 'TnS_Products.csv'
]
masterQuery = "SELECT ID, PRODUCTCATEGORY FROM PRODUCT_CATEGORY".lower(
)
insertStatement = ("INSERT INTO PRODUCT_CATEGORY "
"(ID, PRODUCTCATEGORY) VALUES (%s, %s)").lower()
transform.joinerColumn = 'productCategory (Mandatory)'
if self.FailInd == 0:
log.logComment("ProductCategory (026) -> Inserting data")
transform.masterInsert(masterQuery, insertStatement)
log.logComment("ProductCategory (026) -> Connection Closed")
conn.closeConnector()
else:
log.logComment("ProductCategory (026) -> Inserting data -> Failed")
def __init__(self, root, RunByUsername, log):
#root = "E:/CUA OpenBank API/OpenBanking/ETL"
log.logComment("Initialized MasterEligibility (019)")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment(" MasterEligibility (019) -> Connection Status: " +
str(self.FailInd))
transform = Transform(conn, root, log)
transform.fileList = [
'TnS_Eligibility.csv', 'TeD_Eligibility.csv', 'CCC_Eligibility.csv'
]
masterQuery = "select id, eligibilityType from master_eligibility".lower(
)
insertStatement = ("INSERT INTO master_eligibility "
"(ID, eligibilityType) VALUES (%s, %s)").lower()
transform.joinerColumn = 'Eligibility Type'
if self.FailInd == 0:
log.logComment(" MasterEligibility (019) -> Inserting data")
transform.masterInsert(masterQuery, insertStatement)
log.logComment(" MasterEligibility (019) -> Connection Closed")
conn.closeConnector()
else:
log.logComment(
" MasterEligibility (019) -> Inserting data -> Failed")
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("BankingAdditionalInfo (01) -> Initialized")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
log.logComment("BankingAdditionalInfo (01) -> Connection Status: " + str(self.FailInd))
transform = Transform(conn, root, log)
query = ("SELECT PRODUCTID, OVERVIEWURI, TERMSURI, ELIGIBILITYURI, FEESANDPRICINGURI,"
"BUNDLEURI, lastupdated from BANKINGADDITIONALINFORMATION").lower()
# Rearrange DF columns
df_cols = ['Product ID (Mandatory)', 'Overview URL', 'Terms URL',
'Eligibility URL', 'Fees And PricingURL', 'Bundle URL', 'CUA Effective From (Mandatory) Date/Time'
]
productFiles = ['TeD_Products.csv', 'TnS_Products.csv', 'CCC_Products.csv']
conn.openConnector()
conn.cursor.execute(query)
product_table = conn.cursor.fetchall()
import pandas as pd
product_table = pd.DataFrame(product_table)
#####
firstRun = 1
for file in productFiles:
if firstRun == 1:
product = transform.getDF(file)
product = product[df_cols]
firstRun = 0
else:
newFile = transform.getDF(file)
newFileCols = newFile.columns
productCols = product.columns
sizeofNewFile = len(newFile)
sizeofProduct = len(product)
# add any column which new file do not have
for column in df_cols:
if column not in newFileCols:
addColumn = {column : [None] * sizeofNewFile}
newFile[column] = pd.Series(addColumn)
# add any column which product file do not have
for column in newFileCols:
if column not in productCols:
addColumn = {column : [None] * sizeofProduct}
product[column] = pd.Series(addColumn)
# arrange columns in new DF
newFile = newFile[df_cols]
product = product[df_cols]
# merge into product df
product = pd.concat([product, newFile])
#####
# Accumulate all rows into one ordered string
insertData = []
for i in range(0, len(product)):
insertData.append('|'.join([str(x) for x in product.iloc[[i],:].values.tolist()[0]]))
# Gather data already present in table in var(insertData) format
tableData = []
for i in range(0, len(product_table)):
# tableData.append('|'.join(product_table.iloc[[i],:].values.tolist()[0]))
# Convert all elements to STR as join does not work on timestamp
d = [ str(x) for x in product_table.iloc[[i],:].values.tolist()[0] ]
tableData.append('|'.join(d))
# Identify data needs to be inserted
insertSelection = []
for insertDat in insertData:
if insertDat not in tableData:
insertSelection.append(insertDat)
insertQuery = ("INSERT INTO BANKINGADDITIONALINFORMATION "
"(PRODUCTID, OVERVIEWURI, TERMSURI, ELIGIBILITYURI, FEESANDPRICINGURI,"
"BUNDLEURI, lastupdated) VALUES (%s, %s, %s, %s, %s, %s, %s)").lower()
reject = []
if len(insertSelection) > 0:
for val in insertSelection:
val = val.replace("|False|","|0|")
val = val.replace("|nan","|")
val = val.replace("|None","|")
d = val.split('|')
if(d[1] in ['NaT', 'nan']):
d[1] = None
if(d[2] in ['NaT', 'nan']):
d[2] = None
#cursor.execute(insertQuery, (d[0], d[1], d[2], d[3], d[4], d[5]))
try:
conn.cursor.execute(insertQuery, (d[0], d[1], d[2], d[3], d[4], d[5], d[6]))
except:
reject.append(val)
#log value
log = {'status' : 'Insert Success',
'Rows inserted' : len(insertSelection)
}
else:
log = {'status' : 'Insert Success',
'Rows inserted' : len(insertSelection)
}
# committed to the database
conn.closeConnector()
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):
log.logComment("BankingProductdepositRateTier (05) -> Initialized")
conn = Connector(root, RunByUsername, log)
self.FailInd = conn.openConnector()
transform = Transform(conn, root, log)
transform.fileList = ['RatesFile.csv']
# parentTable = "select productid, depositRateId, applicationfrequency, depositratetype, rate from bankingproductdepositrate".lower()
parentTable = "select productid, depositrateid, applicationfrequency, depositratetype, trim(replace(cast(rate as char), '0','')) from bankingproductdepositrate".lower(
)
parentdata = pd.DataFrame(conn.executeQuery(parentTable))
preLoad = (
'select depositRateId, name, unitOfMeasure, minimumValue, maximumValue,'
'rateApplicationMethod from bankingproductdepositRateTier'
).lower()
preLoadData = conn.executeQuery(preLoad)
preLoadDataList = []
if len(preLoadData) != 0:
for row in preLoadData:
row = [str(x) for x in row]
preLoadDataList.append('|'.join(row))
extractDataTS = transform.getDF('TnS_Rate.csv')
extractDataTS = pd.merge(parentdata,
extractDataTS,
how='left',
left_on=[0, 2, 3, 4],
right_on=[
'Product ID (Mandatory)',
'applicationFrequency (Auto Generated)',
'Rate Type (Mandatory)',
'Rate (Mandatory)'
])
extractDataTS = extractDataTS.dropna(subset=['Rate Type (Mandatory)'])
df_cols = [
1, 'Name', 'Unit of Measure', 'Minimum Value', 'Maximum Value',
'Rate Application Method'
]
extractDataTS = extractDataTS[df_cols]
extractDataRF = transform.getDF('RatesFile.csv')
if not extractDataRF.empty:
extractDataRF = pd.merge(parentdata,
extractDataRF,
how='left',
left_on=[0, 2, 3],
right_on=[
'Product ID',
'Additional Value (Auto Generated)',
'Rate Type (Mandatory)'
])
extractDataRF = extractDataRF.dropna(
subset=['Rate Type (Mandatory)'])
df_cols = [
1, 'Name', 'Unit of Measure', 'minimumValue', 'maximumValue',
'Rate Application Method'
]
extractDataRF = extractDataRF[df_cols]
extractDataTS.columns = df_cols
extractData = pd.concat([extractDataTS, extractDataRF])
extractdataOutput = []
for i in range(len(extractData)):
dat = extractData.iloc[[i], :].values.tolist()[0]
dat = [str(x) for x in dat]
extractdataOutput.append('|'.join(dat))
insertSelection = []
for row in extractdataOutput:
if row not in preLoadDataList:
insertSelection.append(row)
seqQuery = 'Select max(cast(depositRateTierId as decimal)) from bankingproductdepositRateTier'.lower(
)
seq = conn.executeQuery(seqQuery)
seq = seq[0][0]
if seq == None:
seq = 0
else:
seq = int(seq)
insertQuery = (
'insert into bankingproductdepositRateTier '
'(depositRateTierId, depositRateId, name, unitOfMeasure, minimumValue, '
'maximumValue, rateApplicationMethod, '
'createdOn, createdBy, systemCreatedOn, systemCreatedBy) '
'values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)').lower()
reject = []
for row in insertSelection:
seq += 1
d = "|".join([
str(x) for x in [
seq, row, conn.updatedDate, conn.createdBy,
conn.updatedDate, conn.createdBy
]
])
d = d.replace("|False|", "|0|")
d = d.replace("|nan", "|")
d = d.replace("|None", "|")
d = d.split("|")
if d[4] == '':
d[4] = '0'
if d[5] == '':
d[5] = '0'
if conn.insertSingleRow(insertQuery, d) == 1:
reject.append(d)
log.logComment(reject)
conn.closeConnector()
def __init__(self,
sourceFile,
validationChecklist,
exportpath,
root,
log=None):
#sourceFile = 'F:/CUA OpenBank API/OpenBanking/Integrated-v-1-4/Data/PRODUCTS_V2 - Ver 0.4 for July 2020.xlsx'
#self.dataframe = pd.read_excel(sourceFile, sheet_name = 'TRANS_AND_SAVINGS_ACCOUNTS')
#self.validationChecklist = 'F:/CUA OpenBank API/OpenBanking/Integrated-v-1-4/Data/validation_checklist.txt'
self.valid = '1'
# Adding this as part of skipping duplicate product check.
# to turn duplicate check on, make this indicator to 1
self.productRepeteCheck = 0
self.validationChecklist = validationChecklist
exportpath = exportpath + '/ErrorList.txt'
sheets = [
'CRED_AND_CHRG_CARDS', 'TRANS_AND_SAVINGS_ACCOUNTS',
'TERM_DEPOSITS'
]
self.healthreport = []
self.product = []
self.lastupdated = []
for sheet in sheets:
self.dataframe = pd.read_excel(sourceFile, sheet_name=sheet)
dfCheck = self.dataframe.iloc[:, 2:]
dfCheck = dfCheck.dropna()
if dfCheck.empty:
#self.healthreport.append("/*"*10 + sheet + " is empty.")
continue
self.healthreport.append('*' * 100)
self.healthreport.append(sheet)
self.healthreport.append('*' * 100)
self.main(sheet)
if log:
conn = Connector(root, '', log)
conn.openConnector()
self.healthreport.append('*' * 100)
self.healthreport.append(
"*" * 30 +
"Product ID with provided updated date already exist")
self.healthreport.append('*' * 100)
self.DBProd = conn.executeQuery(
'select distinct productid, lastupdated from product')
self.DBProd = [[x[0], x[1]] for x in self.DBProd]
# self.DBLastUpd = [x[1] for x in self.DBProd]
conn.closeConnector()
i = 0
for productID in self.product:
for row in self.DBProd:
if productID == row[0] and self.lastupdated[i] == row[
1] and self.productRepeteCheck == 1:
self.healthreport.append(productID + " - already exists")
self.valid = '0'
i += 1
if len(self.product) != len(set(
self.product)) and self.productRepeteCheck == 1:
self.healthreport.append(
"*" * 10 + "Product ID is repeted in the provided sheet")
self.valid = '0'
if (len(self.healthreport) > 1):
self.writeHealth(exportpath)
else:
self.valid = '1'
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")
