print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking Mode Of Opper")
opper = DataValidation(
pd.DataFrame(),
"GAM.SOL_ID,FORACID,CIF_ID,ACCT_NAME,SCHM_TYPE,MODE_OF_OPER_CODE",
"tbaadm", "ITC")
opper.checkOper()
opper.df.rename(columns={'GAM.SOL_ID': 'SOL_ID'}, inplace=True)
#50%
SOL = DataValidation(pd.DataFrame(), "SOL_DESC, SOL_ID", "tbaadm", "CBT")
SOL.df = SOL.query2("SELECT " + SOL.colStatement + " FROM tbaadm.SOL", "2")
opper.df = pd.merge(opper.df, SOL.df, on=['SOL_ID'], how='left')
#75%
tns = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=' + DBHOST + ')(PORT=' + DBPORT + '))(CONNECT_DATA= (SID=' + DBSID + ')))'
con = cx_Oracle.connect(DBUSER, DBPASS, tns)
cursor = con.cursor()
sql = '''INSERT INTO GHB_CLEANSING.RES_DWS_05B_RESULT VALUES(:SOL_DESC, :SOL_ID, :FORACID, :CIF_ID, :ACCT_NAME, :SCHM_TYPE, :MODE_OF_OPER_CODE, '{0}', TO_DATE('{1}', '{2}'))'''.format(
job_id, job_start_date_time, g_date_format)
cursor.executemany(sql, opper.df.values.tolist())
con.commit()
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking Interest Code")
intCode = DataValidation(
pd.DataFrame(),
"GAM.SOL_ID,FORACID,CIF_ID,ACCT_NAME,SCHM_CODE, CUST_TYPE_CODE,INT_TBL_CODE,ENTITY_ID, INT_TBL_CODE_SRL_NUM",
"tbaadm", "ITC")
intCode.checkInterestCode()
#50%
GSP = DataValidation(pd.DataFrame(),
"CUST_TYPE_CODE,GSP.INTEREST_CODE,SCHM_CODE",
"CUSTOM", "C_GSP")
GSP.df = GSP.query2(
"SELECT " + GSP.colStatement +
" FROM C_GSP GSP WHERE (SCHM_TYPE=\'TDA\' OR SCHM_TYPE=\'SBA\' OR SCHM_TYPE=\'CAA\')",
"2")
joined = pd.merge(intCode.df,
GSP.df,
on=['CUST_TYPE_CODE', 'SCHM_CODE'],
how='left')
joined['GSP.INTEREST_CODE'] = joined['GSP.INTEREST_CODE'].str.upper()
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking Clearing")
clearing = DataValidation(pd.DataFrame(), "SOL_ID,CLG_ZONE_CODE,CLG_ZONE_DATE,TOT_CR_PART_TRAN_AMT,ZONE_STAT","tbaadm","OZH")
clearing.checkClearing()
centralTime = DataValidation(pd.DataFrame(), "DB_STAT_DATE","tbaadm","GCT")
centralTime.df = centralTime.query2("SELECT " + centralTime.colStatement + " FROM tbaadm.gct", "2")
CheckingDate = centralTime.df.DB_STAT_DATE[0] + relativedelta(days=-2)
clearing.df = clearing.df.loc[(clearing.df.CLG_ZONE_DATE < CheckingDate)]
#50%
clearing.df = clearing.df.reset_index(drop=True)
for i in range(clearing.df.shape[0]):
#for i in range(0, 100):
sql_2 = '''
INSERT INTO RES_DWS_02_RESULT
select sol_desc, '{3}', '{4}', TO_DATE('{5}','{2}'), '{6}', '{7}', '{0}', TO_DATE('{1}','{2}')
from
(select sol_desc, sol_id from tbaadm.sol
where sol_id = '{3}')
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking Duplicated TDA Booking Number")
TDABOOK = DataValidation(
pd.DataFrame(),
"GAM.SOL_ID,GAM.FORACID,CIF_ID,GAM.ACCT_NAME,GAM.SCHM_CODE,BOOK.PASSBOOK_NO",
"custom", "C_TDM")
TDABOOK.checkBookDupTDA()
#50%
SOL = DataValidation(pd.DataFrame(), "SOL_DESC, SOL_ID", "tbaadm", "CBT")
SOL.df = SOL.query2("SELECT " + SOL.colStatement + " FROM tbaadm.SOL", "2")
TDABOOK.duplicate.rename(columns={
'GAM.SOL_ID': 'SOL_ID',
'GAM.SCHM_CODE': 'SCHM_CODE'
},
inplace=True)
TDABOOK.duplicate = pd.merge(TDABOOK.duplicate,
SOL.df,
on=['SOL_ID'],
how='left')
TDABOOK.duplicate.rename(columns={
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking Wrong Pegged FLAG")
pegged = DataValidation(
pd.DataFrame(),
"SOL_ID,FORACID,ACCT_NAME,SCHM_CODE,CUST_TYPE_CODE,INT_TBL_CODE,PEGGED_FLG,START_DATE,CIF_ID",
"tbaadm", "ITC")
pegged.checkPeggedFlag()
#50%
#SOL = DataValidation(pd.DataFrame(), "SOL_DESC, SOL_ID","tbaadm","CBT")
#SOL.query("SELECT " + SOL.colStatement + " FROM tbaadm.SOL", "2")
#pegged.df = pd.merge(pegged.df,SOL.df,on = ['SOL_ID'],how = 'left')
pegged.df.rename(columns={
'PEGGED_FLG': 'PEGGED_FLAG',
'INT_TBL_CODE': 'INTEREST_CODE'
},
inplace=True)
pegged.df['JOB_KEY'] = job_id
pegged.df['START_DATE'] = pd.to_datetime(pegged.df['START_DATE'])
#75%
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking ITCintCode")
transITC = DataValidation(
pd.DataFrame(),
"SOL_ID, FORACID, CIF_ID, ACCT_NAME, SCHM_CODE, SCHM_TYPE, INT_TBL_CODE, INT_TBL_CODE_SRL_NUM",
"tbaadm", "GAM")
transITC.checkINTtransaction()
transITC.df.drop(['key', 'Wrong_INT'], axis=1, inplace=True)
#50%
SOL = DataValidation(pd.DataFrame(), "SOL_DESC, SOL_ID", "tbaadm", "CBT")
SOL.df = SOL.query2("SELECT " + SOL.colStatement + " FROM tbaadm.SOL", "2")
transITC.df = pd.merge(transITC.df, SOL.df, on=['SOL_ID'], how='left')
#75%
#tns = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=' + DBHOST + ')(PORT=' + DBPORT + '))(CONNECT_DATA= (SID='+ DBSID +')))'
#con = cx_Oracle.connect(DBUSER,DBPASS,tns)
#cursor = con.cursor()
#sql = '''INSERT INTO GHB_CLEANSING.RES_DWS_05D_RESULT VALUES(:SOL_DESC, :SOL_ID, :FORACID, :CIF_ID, :ACCT_NAME, :SCHM_CODE, :SCHM_TYPE, :INT_TBL_CODE, :INT_TBL_CODE_SRL_NUM, '{0}', TO_DATE('{1}', '{2}'))'''.format(job_id, job_start_date_time, g_date_format)
#cursor.executemany(sql, transITC.df.values.tolist())
#con.commit()
#cursor.close()
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking AAS")
joint = DataValidation(
pd.DataFrame(),
"SOL_ID,FORACID,ACCT_NAME,SCHM_TYPE,SCHM_CODE,CIF_M,CIF_J2,CIF_J3,CIF_J4,CIF_J5,CIF_J6,CIF_J7,CIF_J8,CIF_J9",
"tbaadm", "GAM")
joint.checkjoint()
joint.df['key'] = joint.df['FORACID'].map(transform_iforacid)
joint.df['Wrong_CIF_M'] = np.where(
joint.df.groupby('key').CIF_M.transform('nunique') > 1, 'T', '')
joint.df['Wrong_CIF_J2'] = np.where(
joint.df.groupby('key').CIF_J2.transform('nunique') > 1, 'T', '')
joint.df['Wrong_CIF_J3'] = np.where(
joint.df.groupby('key').CIF_J3.transform('nunique') > 1, 'T', '')
joint.df['Wrong_CIF_J4'] = np.where(
joint.df.groupby('key').CIF_J4.transform('nunique') > 1, 'T', '')
joint.df['Wrong_CIF_J5'] = np.where(
joint.df.groupby('key').CIF_J5.transform('nunique') > 1, 'T', '')
joint.df['Wrong_CIF_J6'] = np.where(
joint.df.groupby('key').CIF_J6.transform('nunique') > 1, 'T', '')
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking Wrong WTAX FLAG")
WTFLAG = DataValidation(
pd.DataFrame(),
"SOL_ID,FORACID,CIF_ID,ACCT_NAME,WTAX_PCNT,WTAX_FLG,CUST_ID,SCHM_CODE",
"tbaadm", "GAM")
WTFLAG.checkWTAXFlag()
#50%
SOL = DataValidation(pd.DataFrame(), "SOL_DESC, SOL_ID", "tbaadm", "CBT")
SOL.df = SOL.query2("SELECT " + SOL.colStatement + " FROM tbaadm.SOL", "2")
WTFLAG.missFlag = pd.merge(WTFLAG.missFlag,
SOL.df,
on=['SOL_ID'],
how='left')
#75%
#tns = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=' + DBHOST + ')(PORT=' + DBPORT + '))(CONNECT_DATA= (SID='+ DBSID +')))'
#con = cx_Oracle.connect(DBUSER,DBPASS,tns)
#cursor = con.cursor()
#sql = '''INSERT INTO res_dws_04a_result VALUES(:SOL_DESC, :SOL_ID, :FORACID, :CIF_ID, :ACCT_NAME,:WTAX_PCNT, :WTAX_FLG, '{0}', TO_DATE('{1}', '{2}'), :CUST_ID, :SCHM_CODE)'''.format(job_id, job_start_date_time, g_date_format)
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking SBA")
SBABook = DataValidation(
pd.DataFrame(),
"SOL_ID,GAM.FORACID,CIF_ID,ACCT_NAME,SCHM_CODE,BEGIN_CHQ_NUM,CHQ_ISSU_DATE",
"tbaadm", "CBT")
SBABook.checkBookDupSBA()
SBABook.duplicate.rename(columns={'GAM.FORACID': 'FORACID'}, inplace=True)
SBABook.duplicate.drop(['CHQ_ISSU_DATE'], axis=1, inplace=True)
#50%
SOL = DataValidation(pd.DataFrame(), "SOL_DESC, SOL_ID", "tbaadm", "CBT")
SOL.df = SOL.query2("SELECT " + SOL.colStatement + " FROM tbaadm.SOL", "2")
SBABook.duplicate = pd.merge(SBABook.duplicate,
SOL.df,
on=['SOL_ID'],
how='left')
#75%
#tns = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=' + DBHOST + ')(PORT=' + DBPORT + '))(CONNECT_DATA= (SID='+ DBSID +')))'
#con = cx_Oracle.connect(DBUSER,DBPASS,tns)
#cursor = con.cursor()
print "Unexpected error:", sys.exc_info()[0]
# STAMP START JOB
sql = "UPDATE JOB_TRN_BACKEND \
SET STATUS='P' \
, PROGRESS='0' \
, JOB_START_DATE_TIME=TO_DATE('" + job_start_date_time + "','" + g_date_format + "') \
, PROCESS_ID='" + str(process_id) + "' \
WHERE JOB_KEY = '" + str(job_id) + "' "
#print sql
db1.update_rows(sql)
start = timer()
print("Start Checking Wrong WTAX PCNT")
PCNT = DataValidation(
pd.DataFrame(),
"SOL_ID,ACC.CUST_TYPE_CODE,FORACID,CIF_ID,GAM.ACCT_NAME,GAM.SCHM_CODE,GSP.INTEREST_CODE,WTAX_PCNT,GSP.WITHOLDING_TAX",
"tbaadm", "GAM")
PCNT.checkPCNTFlag()
PCNT.df.rename(columns={
'WTAX_PCNT': 'WRONG_PCNT',
'GSP.WITHOLDING_TAX': 'CORRECT_PCNT',
'ACC.CUST_TYPE_CODE': 'CUST_TYPE_CODE',
'GAM.ACCT_NAME': 'ACCT_NAME',
'GAM.SCHM_CODE': 'SCHM_CODE',
'GSP.INTEREST_CODE': 'INTEREST_CODE'
},
inplace=True)
#50%
SOL = DataValidation(pd.DataFrame(), "SOL_DESC, SOL_ID", "tbaadm", "CBT")
SOL.df = SOL.query2("SELECT " + SOL.colStatement + " FROM tbaadm.SOL", "2")
PCNT.df = pd.merge(PCNT.df, SOL.df, on=['SOL_ID'], how='left')