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')