public string getNostroAcocunt(string currency, string bankID) { string MethodName = MethodBase.GetCurrentMethod().Name; string NostroAccount = ""; ModuleName = this.GetType().Name; try { string sqlQuery = "select cd_desc as nostro_acc_number from cocd where code_type = 'NST' and cm_code = '" + currency + "' and bank_id = '" + bankID + "'"; OledbSqlHelper _dbHelper = new OledbSqlHelper(ConnString, bConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); NostroAccount = _dbHelper.getSingleValue(sqlQuery, "nostro_acc_number"); Logger.logDebug(ModuleName, MethodName, "Nostro Account : " + NostroAccount, Logger.LOGLEVEL.INFO); return(NostroAccount); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); throw ex; } finally { Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); } }
public bool Add(string sourceNetwork) { string MethodName = MethodBase.GetCurrentMethod().Name; ModuleName = this.GetType().Name; bool insert = false; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); string translatedPIN = getTranslatedPIN(sourceNetwork, PIN); PIN = translatedPIN; //string whereClause = preparePrimaryWhereClause(); string insertSQL = prepareInsertStatement(); string sqlQuery = insertSQL;// +whereClause; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); bNCRODSConnEncrypted = Convert.ToBoolean(ConfigurationManager.AppSettings["NCRODSEncrypted"]); ODSConnString = ConfigurationManager.ConnectionStrings["NCRODS"].ConnectionString; OledbSqlHelper _dbHelper = new OledbSqlHelper(ODSConnString, bNCRODSConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Inserting", Logger.LOGLEVEL.INFO); insert = _dbHelper.ExecuteNonQuery(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Insert Result: " + insert.ToString(), Logger.LOGLEVEL.INFO); return(insert); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); throw ex; } return(true); }
private string getRecordsCount() { string MethodName = MethodBase.GetCurrentMethod().Name; OleDbDataReader _dataReader = null; string ModuleName = "TPINMigrationUtility"; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); OledbSqlHelper _dbHelper = new OledbSqlHelper(VisionConnString, bVisionConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Fetching Records", Logger.LOGLEVEL.INFO); string sqlQuery = ConfigurationManager.AppSettings["RETREIVE_RECORDS_LIST_COUNT"]; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); string count = _dbHelper.getSingleValue(sqlQuery, "TOTAL"); Logger.logDebug(ModuleName, MethodName, "Query executed.", Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "Total Records Feteched: [" + count + "]", Logger.LOGLEVEL.INFO); lblTotal.Text = count; return(count); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); MessageBox.Show(ex.Message); throw ex; } }
public bool Update() { string MethodName = MethodBase.GetCurrentMethod().Name; ModuleName = this.GetType().Name; bool bUpdated = false; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); string updateSQL = prepareUpdateStatement(); string whereClause = prepareUpdateWhereClause(); string sqlQuery = updateSQL + whereClause; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); bNCRODSConnEncrypted = Convert.ToBoolean(ConfigurationManager.AppSettings["NCRODSEncrypted"]); ODSConnString = ConfigurationManager.ConnectionStrings["NCRODS"].ConnectionString; OledbSqlHelper _dbHelper = new OledbSqlHelper(ODSConnString, bNCRODSConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Updating", Logger.LOGLEVEL.INFO); //iUpdated = _dbHelper.ExecuteScalarWithRecordsAffected(sqlQuery); bUpdated = _dbHelper.ExecuteNonQuery(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Update Result: " + bUpdated.ToString(), Logger.LOGLEVEL.INFO); return(bUpdated); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); throw ex; } }
public bool Update(string updateSQL = null) { string MethodName = MethodBase.GetCurrentMethod().Name; ModuleName = this.GetType().Name; bool updated = false; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); if (updateSQL == null || updateSQL == "") { updateSQL = prepareUpdateStatement() + prepareUpdatePrimaryWhereClause(); } Logger.logDebug(ModuleName, MethodName, "Executing Query : " + updateSQL, Logger.LOGLEVEL.INFO); OledbSqlHelper _dbHelper = new OledbSqlHelper(ODSConnString, bNCRODSConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Updating CC SAF Log", Logger.LOGLEVEL.INFO); updated = _dbHelper.ExecuteNonQuery(updateSQL); Logger.logDebug(ModuleName, MethodName, "Update Result: " + updated.ToString(), Logger.LOGLEVEL.INFO); return(updated); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); throw new Exception(ex.Message); } }
private bool InsertCustomer(string IdentityNumber, string EncryptedPIN, string ChannelID, string RelationShipNumber, string createdBy, string AuthorizedBy, string status, string retryAvailable) { string MethodName = MethodBase.GetCurrentMethod().Name; bool inserted = false; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); //check for IDNumber Masking. If should be maksed then do the masking // Logger.logDebug(ModuleName, MethodName, "Identity Number: " + IdentityNumber, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "Inserting Customer", Logger.LOGLEVEL.INFO); /* * [dbo].[CUSTOMER_PIN_AUTHEN]( * [CUSTOMER_ID] [varchar](50) NOT NULL, * [CHANNEL_GROUP_ID] [varchar](50) NOT NULL, * [RELATIONSHIP_NUM] [varchar](50) NOT NULL, * [TPIN] [varchar](50) NOT NULL, * [RETRY_AVAILABLE] [int] NOT NULL, * [MAX_RETRY_COUNT] [int] NOT NULL, * [LAST_PIN_ENTERED_DATE] [datetime] NULL, * [LAST_PIN_CHANGE_DATE] [datetime] NULL, * [TPIN_FLAG] [varchar](50) NULL, * [PIN_VALUE] [varchar](50) NULL, * [PIN_OFFSET] [varchar](50) NULL, * [RESERVED_1] [varchar](50) NULL, * [RESERVED_2] [varchar](50) NULL, * [RESERVED_3] [varchar](50) NULL, * [STATUS] [varchar](50) NOT NULL, * [CREATED_BY] [varchar](50) NOT NULL, * [CREATED_ON] [datetime] NOT NULL, * [UPDATED_BY] [varchar](50) NULL, * [UPDATED_ON] [datetime] NOT NULL, * [AUTHORIZED_BY] [varchar](50) NULL, * [AUTHORIZED_ON] [datetime] NULL, */ if (createdBy == "") { createdBy = "UNKNOWN SYSTEM GENERATED"; } //string sqlQuery = "INSERT INTO CUSTOMER_PIN_AUTHEN (CUSTOMER_ID, CHANNEL_GROUP_ID, RELATIONSHIP_NUM, TPIN, RETRY_AVAILABLE,MAX_RETRY_COUNT, STATUS,CREATED_BY, CREATED_ON, AUTHORIZED_BY, AUTHORIZED_ON)"; //string queryValues = "VALUES ('" + IdentityNumber + "'," + // "'" + ChannelID + "'," + // "'" + RelationShipNumber + "'," + // "'" + EncryptedPIN + "'," + // "" + retryAvailable + "," + // "" + DefaultMaxRetryCount.ToString() + "," + // "'" + status + "'," + // "'" + createdBy + "'," + // "getdate()," + // "'" + AuthorizedBy + "'," + // "getdate()" + ")"; string sqlQuery = "UPDATE CUSTOMER_PIN_AUTHEN SET "; string queryValues = "TPIN = '" + EncryptedPIN + "'," + " RETRY_AVAILABLE = '" + retryAvailable + "'," + " UPDATED_BY = '" + "ICM" + "'," + " UPDATED_ON = " + "getdate()" + "" + " WHERE RELATIONSHIP_NUM = '" + RelationShipNumber + "'"; sqlQuery = sqlQuery + queryValues; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); if (bInsertMigDB) { OledbSqlHelper _dbHelper = new OledbSqlHelper(NCRODSConnString, bNCRODSConnEncrypted); inserted = _dbHelper.ExecuteNonQuery(sqlQuery); } else { inserted = true; } if (inserted) { writeSuccessQueriestoFile(sqlQuery); } else { writeFailedQueriestoFile(sqlQuery); writeFailedCustomerswithQuerytoFile(IdentityNumber); } Logger.logDebug(ModuleName, MethodName, "Insert Result: " + inserted.ToString(), Logger.LOGLEVEL.INFO); return(inserted); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); MessageBox.Show(ex.Message); throw ex; } }
private bool getPINLengthCount() { string MethodName = MethodBase.GetCurrentMethod().Name; OleDbDataReader _dataReader = null; bool customerFound = false; string ModuleName = "TPINMigrationUtility"; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); OledbSqlHelper _dbHelper = new OledbSqlHelper(VisionConnString, bVisionConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Fetching Records", Logger.LOGLEVEL.INFO); gstatus = "Fetching Records"; lblStatus.Text = gstatus; string sqlQuery = ConfigurationManager.AppSettings["RETREIVE_RECORDS_LIST"]; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); _dataReader = _dbHelper.ExecuteReader(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Query executed. Retrieving Values", Logger.LOGLEVEL.INFO); gstatus = "Parsing Records"; lblStatus.Text = gstatus; CCrypto _crypto = new CCrypto(); if (_dataReader != null) { if (_dataReader.HasRows) { while (_dataReader.Read()) { customerFound = true; //string Status = _dataReader["STATUS"].ToString(); int RetryAvailable = Convert.ToInt16(_dataReader["RETRY_AVAILABLE"]); int MaxRetryCount = Convert.ToInt16(_dataReader["MAX_RETRY_COUNT"]); string TPIN = _dataReader["PIN_CODE"].ToString(); string customerID = _dataReader["CUSTOMER_ID"].ToString(); Logger.logDebug(ModuleName, MethodName, "CIF : " + customerID, Logger.LOGLEVEL.WARNING); //Logger.logDebug(ModuleName, MethodName, "STATUS : " + Status, Logger.LOGLEVEL.WARNING); Logger.logDebug(ModuleName, MethodName, "RETRY AVAILABLE : " + RetryAvailable, Logger.LOGLEVEL.WARNING); Logger.logDebug(ModuleName, MethodName, "MAX RETRY : " + MaxRetryCount, Logger.LOGLEVEL.WARNING); Logger.logDebug(ModuleName, MethodName, "RETRY AVAILABLE : " + RetryAvailable, Logger.LOGLEVEL.WARNING); //Logger.logDebug(ModuleName, MethodName, "ENCRYPTED PIN : " + EncryptedTPIN, Logger.LOGLEVEL.DEBUG); //translate the PIN try { string clearPIN = ""; if (TPIN != null && TPIN != "") { int i = _crypto.Decrypt(TPIN, out clearPIN); if (clearPIN != "") { if (clearPIN.Length == 4) { fourDigitCount += 1; } else if (clearPIN.Length == 5) { fiveDigitCount += 1; } else if (clearPIN.Length == 6) { SixDigitCount += 1; } else { otherDigitCount += 1; } } else { writeFailedCustomerstoFile(customerID); throw new Exception("Translated PIN is empty"); } } else { writeFailedCustomerstoFile(customerID); throw new Exception("TPIN is empty or NULL"); } } catch (Exception ex) { iFailed += 1; lblFailed.Text = iFailed.ToString(); Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); //log query in a separate file // } iProcessed += 1; lblProcessed.Text = iProcessed.ToString(); } } else { Logger.logDebug(ModuleName, MethodName, "No record found", Logger.LOGLEVEL.INFO); customerFound = false; } _dataReader.Close(); //btnMigrate.Enabled = true; gstatus = "Finished"; lblStatus.Text = gstatus; } else { Logger.logDebug(ModuleName, MethodName, "No Records Founds", Logger.LOGLEVEL.INFO); customerFound = false; btnMigrate.Enabled = true; gstatus = "Finished"; lblStatus.Text = gstatus; } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); return(customerFound); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); MessageBox.Show(ex.Message); throw ex; } finally { btnMigrate.Enabled = true; } }
private List <CreditCardSAF> GetSAFRecordsHelper(string sqlQuery) { string MethodName = MethodBase.GetCurrentMethod().Name; DbDataReader _dataReader = null; var list = new List <CreditCardSAF>(); try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); OledbSqlHelper _dbHelper = new OledbSqlHelper(ODSConnString, bNCRODSConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); _dataReader = _dbHelper.ExecuteReader(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Query executed. Retrieving Values", Logger.LOGLEVEL.INFO); //Change Transamount format to 3 decimals Logger.logDebug(ModuleName, MethodName, "Getting NI Decimal Formatting.", Logger.LOGLEVEL.INFO); string NIFormat = ConfigurationManager.AppSettings["NIFormat"]; Logger.logDebug(ModuleName, MethodName, "NI Decimal Formatting : " + NIFormat, Logger.LOGLEVEL.INFO); if (_dataReader != null) { if (_dataReader.HasRows) { int irow = 0; while (_dataReader.Read()) { CreditCardSAF _ccSAF = new CreditCardSAF(); _ccSAF.ni1_account_number = _dataReader["ni1_account_number"].ToString(); _ccSAF.ni1_tran_date = _dataReader["ni1_tran_date"].ToString(); _ccSAF.ni1_msmoney_seq = _dataReader["ni1_msmoney_seq"].ToString(); _ccSAF.ni1_tran_journal = _dataReader["ni1_tran_journal"].ToString(); _ccSAF.ni1_tran_time = _dataReader["ni1_tran_time"].ToString(); _ccSAF.ni1_tran_type = _dataReader["ni1_tran_type"].ToString(); _ccSAF.ni1_tran_code = _dataReader["ni1_tran_code"].ToString(); _ccSAF.ni1_tran_sequence = _dataReader["ni1_tran_sequence"].ToString(); _ccSAF.ni1_teller = _dataReader["ni1_teller"].ToString(); _ccSAF.ni1_branch = _dataReader["ni1_branch"].ToString(); _ccSAF.ni1_narrative = _dataReader["ni1_narrative"].ToString(); _ccSAF.ni1_trfr_narr = _dataReader["ni1_trfr_narr"].ToString(); _ccSAF.ni1_promo = _dataReader["ni1_promo"].ToString(); _ccSAF.ni1_trace_flag = _dataReader["ni1_trace_flag"].ToString(); _ccSAF.ni1_tran_amount = _dataReader["ni1_tran_amount"].ToString(); //Change format of transaction amount //Logger.logDebug(ModuleName, MethodName, "*************************** Change Transaction Amount Format. ***************************", Logger.LOGLEVEL.INFO); //string sTransAmt = _ccSAF.ni1_tran_amount; //decimal dTransAmt = decimal.Parse(sTransAmt); //sTransAmt = dTransAmt.ToString(NIFormat); //_ccSAF.ni1_tran_amount = sTransAmt; _ccSAF.ni1_balance = _dataReader["ni1_balance"].ToString(); _ccSAF.extraction_date = _dataReader["extraction_date"].ToString(); _ccSAF.ni_msg_id_seq_num = _dataReader["ni_msg_id_seq_num"].ToString(); _ccSAF.ni_resp_date = _dataReader["ni_resp_date"].ToString(); _ccSAF.ni_rep_status = _dataReader["ni_rep_status"].ToString(); _ccSAF.ni_rep_error_cd = _dataReader["ni_rep_error_cd"].ToString(); _ccSAF.ni_rep_error_desc = _dataReader["ni_rep_error_desc"].ToString(); _ccSAF.ni_rep_trace = _dataReader["ni_rep_trace"].ToString(); _ccSAF.ni_rep_data_error = _dataReader["ni_rep_data_error"].ToString(); _ccSAF.ni_rep_type = _dataReader["ni_rep_type"].ToString(); _ccSAF.ni_trans_ref_id = _dataReader["ni_trans_ref_id"].ToString(); _ccSAF.recordid = Convert.ToInt32(_dataReader["recordid"].ToString()); _ccSAF.retry_count = Convert.ToInt32(_dataReader["retry_count"].ToString()); _ccSAF.last_retry_time = _dataReader["last_retry_time"].ToString(); _ccSAF.original_icm_stan = _dataReader["original_icm_stan"].ToString(); _ccSAF.original_icm_date = _dataReader["original_icm_date"].ToString(); _ccSAF.is_processed = _dataReader["is_processed"].ToString(); _ccSAF.card_currency = _dataReader["card_currency"].ToString(); list.Add(_ccSAF); //_dataReader.NextResult(); } } else { Logger.logDebug(ModuleName, MethodName, "No record found", Logger.LOGLEVEL.INFO); } _dataReader.Close(); } else { Logger.logDebug(ModuleName, MethodName, "No Records Founds", Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); return(list); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); throw ex; } finally { try { Logger.logDebug(ModuleName, MethodName, "disposing data reader", Logger.LOGLEVEL.INFO); _dataReader.Dispose(); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); } } return(list); }
public AccountBeneficary[] GetRecords(string customerID, string BankID, bool bWithinAHB) { string MethodName = MethodBase.GetCurrentMethod().Name; AccountBeneficary[] _recordsArray = null; DbDataReader _dataReader = null; ModuleName = this.GetType().Name; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); //check for IDNumber Masking. If should be maksed then do the masking // OledbSqlHelper _dbHelper = new OledbSqlHelper(ConnString, bConnEncrypted); string selectSQL = prepareSearchWhereClause(customerID, BankID, bWithinAHB); Logger.logDebug(ModuleName, MethodName, "Retrieve Select :" + selectSQL, Logger.LOGLEVEL.INFO); string sqlQuery = "SELECT * FROM PAYM " + selectSQL; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); _dataReader = _dbHelper.ExecuteReader(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Query executed. Retrieving Values", Logger.LOGLEVEL.INFO); var list = new List <AccountBeneficary>(); if (_dataReader != null) { if (_dataReader.HasRows) { int irow = 0; while (_dataReader.Read()) { AccountBeneficary _record = new AccountBeneficary(); _record.BeneficiaryAcountNumber = _dataReader["BNF_ACCT_ID"].ToString(); _record.BeneficiaryAddress = _dataReader["bnf_address_1"].ToString(); _record.BeneficiaryBankCityZip = _dataReader["bnf_bank_city_zip"].ToString(); _record.BeneficiaryBankCountry = _dataReader["bnf_bank_country"].ToString(); _record.BeneficiaryBankName = _dataReader["FREE_FIELD_1"].ToString(); _record.BeneficiaryCountry = _dataReader["bnf_cntry"].ToString(); _record.BeneficiaryCurrency = _dataReader["BNF_ACCT_CRN"].ToString(); _record.BeneficiaryName = _dataReader["BNF_NAME"].ToString(); _record.BeneficiaryState = _dataReader["bnf_state"].ToString(); Logger.logDebug(ModuleName, MethodName, "BeneficiaryAcountNumber :"+ _record.BeneficiaryAcountNumber, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "BeneficiaryAddress :"+ _record.BeneficiaryAddress, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "BeneficiaryBankCityZip :"+ _record.BeneficiaryBankCityZip, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "BeneficiaryBankCountry :"+ _record.BeneficiaryBankCountry, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "BeneficiaryBankName :"+ _record.BeneficiaryBankName, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "BeneficiaryCountry :"+ _record.BeneficiaryCountry, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "BeneficiaryCurrency :"+ _record.BeneficiaryCurrency, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "BeneficiaryName :"+ _record.BeneficiaryName, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "BeneficiaryState :"+ _record.BeneficiaryState, Logger.LOGLEVEL.INFO); list.Add(_record); //_dataReader.NextResult(); } _recordsArray = list.ToArray(); list = null; } else { Logger.logDebug(ModuleName, MethodName, "No record found", Logger.LOGLEVEL.INFO); } _dataReader.Close(); } else { Logger.logDebug(ModuleName, MethodName, "No Records Founds", Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); return(_recordsArray); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); throw ex; } return(_recordsArray); }
public CustomerPINInfo[] GetRecords() { string MethodName = MethodBase.GetCurrentMethod().Name; CustomerPINInfo[] _recordsArray = null; DbDataReader _dataReader = null; ModuleName = this.GetType().Name; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); //check for IDNumber Masking. If should be maksed then do the masking // bNCRODSConnEncrypted = Convert.ToBoolean(ConfigurationManager.AppSettings["NCRODSEncrypted"]); ODSConnString = ConfigurationManager.ConnectionStrings["NCRODS"].ConnectionString; OledbSqlHelper _dbHelper = new OledbSqlHelper(ODSConnString, bNCRODSConnEncrypted); string selectSQL = prepareSearchWhereClause(); Logger.logDebug(ModuleName, MethodName, "Retrieve Select :" + selectSQL, Logger.LOGLEVEL.INFO); string selectFields = "CP.ID " + ",CP.IDENTITY_NUMBER " + ",CP.CUST_TYPE_CODE " + ",CT.TYPE_CODE_DESC " + ",CP.PIN_TYPE_CODE " + ",PT.TYPE_CODE_DESC AS PIN_TYPE_CODE_DESC" + ",CP.CUSTOMER_STATUS_CODE " + ",CS.STATUS_CODE_DESC" + //",CP.AVAILABLE_IVR_RETRIES " + ",MAX_IVR_RETRIES - UTILIZED_IVR_RETRIES AS AVAILABLE_IVR_RETRIES " + ",CP.MAX_IVR_RETRIES" + ",CP.LAST_PIN_CHANGE_DATE " + ",CP.LAST_PIN_ENTRY_DATE " + ",CP.LAST_SUCCESFUL_AUTH_DATE " + ",CP.LAST_PIN_GENERATION_DATE " + ",CP.LAST_STATUS_UPDATE_DATE " + ",CP.CREATED_BY " + ",CP.CREATED_ON " + ",CP.UPDATED_BY " + ",CP.UPDATED_ON " + ",CP.IS_DELETED " + ",CP.DELETED_BY " + ",CP.DELETED_ON " + ",CP.AUTHORIZED_BY " + ",CP.AUTHORIZED_ON"; string sqlQuery = "SELECT " + selectFields + " FROM CUSTOMER_PIN_INFO CP, PIN_TYPE PT, CUSTOMER_STATUS CS, CUSTOMER_TYPE CT " + selectSQL + " AND PIN_TYPE_CODE = PT.TYPE_CODE AND CUST_TYPE_CODE = CT.TYPE_CODE AND CUSTOMER_STATUS_CODE = CS.STATUS_CODE"; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); _dataReader = _dbHelper.ExecuteReader(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Query executed. Retrieving Values", Logger.LOGLEVEL.INFO); var list = new List <CustomerPINInfo>(); if (_dataReader != null) { if (_dataReader.HasRows) { int irow = 0; while (_dataReader.Read()) { CustomerPINInfo _record = new CustomerPINInfo(); _record.ID = Convert.ToInt32(_dataReader["ID"]); _record.IDENTITY_NUMBER = _dataReader["IDENTITY_NUMBER"].ToString(); _record.CUST_TYPE_CODE = _dataReader["CUST_TYPE_CODE"].ToString(); _record.CUST_TYPE_CODE_DESC = _dataReader["TYPE_CODE_DESC"].ToString(); _record.PIN_TYPE_CODE = _dataReader["PIN_TYPE_CODE"].ToString(); _record.PIN_TYPE_CODE_DESC = _dataReader["PIN_TYPE_CODE_DESC"].ToString(); _record.CUSTOMER_STATUS_CODE = _dataReader["CUSTOMER_STATUS_CODE"].ToString(); _record.CUSTOMER_STATUS_CODE_DESC = _dataReader["STATUS_CODE_DESC"].ToString(); _record.AVAILABLE_IVR_RETRIES = _dataReader["AVAILABLE_IVR_RETRIES"].ToString(); _record.MAX_IVR_RETRIES = _dataReader["MAX_IVR_RETRIES"].ToString(); _record.LAST_PIN_CHANGE_DATE = _dataReader["LAST_PIN_CHANGE_DATE"].ToString(); _record.LAST_PIN_ENTRY_DATE = _dataReader["LAST_PIN_ENTRY_DATE"].ToString(); _record.LAST_SUCCESFUL_AUTH_DATE = _dataReader["LAST_SUCCESFUL_AUTH_DATE"].ToString(); _record.LAST_PIN_GENERATION_DATE = _dataReader["LAST_PIN_GENERATION_DATE"].ToString(); _record.LAST_STATUS_UPDATE_DATE = _dataReader["LAST_STATUS_UPDATE_DATE"].ToString(); _record.CREATED_BY = _dataReader["CREATED_BY"].ToString(); _record.CREATED_ON = _dataReader["CREATED_ON"].ToString(); _record.UPDATED_BY = _dataReader["UPDATED_BY"].ToString(); _record.UPDATED_ON = _dataReader["UPDATED_ON"].ToString(); _record.IS_DELETED = _dataReader["IS_DELETED"].ToString(); _record.DELETED_BY = _dataReader["DELETED_BY"].ToString(); _record.DELETED_ON = _dataReader["DELETED_ON"].ToString(); _record.AUTHORIZED_BY = _dataReader["AUTHORIZED_BY"].ToString(); _record.AUTHORIZED_ON = _dataReader["AUTHORIZED_ON"].ToString(); Logger.logDebug(ModuleName, MethodName, "ID :"+ _record.ID.ToString(), Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "IDENTITY_NUMBER :"+ _record.IDENTITY_NUMBER, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "CUST_TYPE_CODE :"+ _record.CUST_TYPE_CODE, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "PIN_TYPE_CODE :"+ _record.PIN_TYPE_CODE, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "CUSTOMER_STATUS_CODE :"+ _record.CUSTOMER_STATUS_CODE, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "AVAILABLE_IVR_RETRIES :"+ _record.AVAILABLE_IVR_RETRIES, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "LAST_PIN_CHANGE_DATE :"+ _record.LAST_PIN_CHANGE_DATE, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "LAST_PIN_ENTRY_DATE :"+ _record.LAST_PIN_ENTRY_DATE, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "LAST_SUCCESFUL_AUTH_DATE :" + _record.LAST_SUCCESFUL_AUTH_DATE, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "LAST_PIN_GENERATION_DATE :" + _record.LAST_PIN_GENERATION_DATE, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "LAST_STATUS_UPDATE_DATE :" + _record.LAST_STATUS_UPDATE_DATE, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "CREATED_BY :"+ _record.CREATED_BY, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "CREATED_ON :"+ _record.CREATED_ON, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "UPDATED_BY :"+ _record.UPDATED_BY, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "UPDATED_ON :"+ _record.UPDATED_ON, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "IS_DELETED :"+ _record.IS_DELETED, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "DELETED_BY :"+ _record.DELETED_BY, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "DELETED_ON :"+ _record.DELETED_ON, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "AUTHORIZED_BY :"+ _record.AUTHORIZED_BY, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "AUTHORIZED_ON :"+ _record.AUTHORIZED_ON, Logger.LOGLEVEL.INFO); list.Add(_record); //_dataReader.NextResult(); } _recordsArray = list.ToArray(); list = null; } else { Logger.logDebug(ModuleName, MethodName, "No record found", Logger.LOGLEVEL.INFO); } _dataReader.Close(); } else { Logger.logDebug(ModuleName, MethodName, "No Records Founds", Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); return(_recordsArray); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); throw ex; } return(_recordsArray); }
private bool migratePINS() { string MethodName = MethodBase.GetCurrentMethod().Name; OleDbDataReader _dataReader = null; bool customerFound = false; string ModuleName = "TPINMigrationICM"; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); OledbSqlHelper _dbHelper = new OledbSqlHelper(NCRODSConnString, bNCRODSConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Fetching Records", Logger.LOGLEVEL.INFO); gstatus = "Fetching Records"; lblStatus.Text = gstatus; string sqlQuery = ConfigurationManager.AppSettings["RETREIVE_RECORDS_LIST"]; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); _dataReader = _dbHelper.ExecuteReader(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Query executed. Retrieving Values", Logger.LOGLEVEL.INFO); gstatus = "Parsing Records"; lblStatus.Text = gstatus; if (_dataReader != null) { if (_dataReader.HasRows) { while (_dataReader.Read()) { customerFound = true; //string Status = _dataReader["STATUS"].ToString(); int RetryAvailable = Convert.ToInt16(_dataReader["RETRY_AVAILABLE"]); int MaxRetryCount = Convert.ToInt16(_dataReader["MAX_RETRY_COUNT"]); string TPIN = _dataReader["TPIN"].ToString(); string customerID = _dataReader["CUSTOMER_ID"].ToString(); Logger.logDebug(ModuleName, MethodName, "CIF : " + customerID, Logger.LOGLEVEL.WARNING); //Logger.logDebug(ModuleName, MethodName, "STATUS : " + Status, Logger.LOGLEVEL.WARNING); Logger.logDebug(ModuleName, MethodName, "RETRY AVAILABLE : " + RetryAvailable, Logger.LOGLEVEL.WARNING); Logger.logDebug(ModuleName, MethodName, "MAX RETRY : " + MaxRetryCount, Logger.LOGLEVEL.WARNING); Logger.logDebug(ModuleName, MethodName, "RETRY AVAILABLE : " + RetryAvailable, Logger.LOGLEVEL.WARNING); //Logger.logDebug(ModuleName, MethodName, "ENCRYPTED PIN : " + EncryptedTPIN, Logger.LOGLEVEL.DEBUG); //translate the PIN try { string translatedPIN = ""; if (TPIN != null && TPIN != "") { translatedPIN = translatePIN(TPIN); if (translatedPIN != "") { bool inserted = InsertCustomer(customerID, translatedPIN, "1", customerID, "ICM", "ICM", ConfigurationManager.AppSettings["MIGRATION_STATUS"], RetryAvailable.ToString()); if (!inserted) { throw new Exception("Unable to Add customer"); } iSuccess += 1; lblSuccess.Text = iSuccess.ToString(); } else { writeFailedCustomerstoFile(customerID); throw new Exception("Translated PIN is empty"); } } else { writeFailedCustomerstoFile(customerID); throw new Exception("TPIN is empty or NULL"); } } catch (Exception ex) { iFailed += 1; lblFailed.Text = iFailed.ToString(); Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); //log query in a separate file // } iProcessed += 1; lblProcessed.Text = iProcessed.ToString(); } } else { Logger.logDebug(ModuleName, MethodName, "No record found", Logger.LOGLEVEL.INFO); customerFound = false; } _dataReader.Close(); //btnMigrate.Enabled = true; gstatus = "Finished"; lblStatus.Text = gstatus; } else { Logger.logDebug(ModuleName, MethodName, "No Records Founds", Logger.LOGLEVEL.INFO); customerFound = false; btnMigrate.Enabled = true; gstatus = "Finished"; lblStatus.Text = gstatus; } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); return(customerFound); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); MessageBox.Show(ex.Message); throw ex; } finally { btnMigrate.Enabled = true; } }
private bool InsertCustomerPINHistory(string IdentityNumber, string transaltedPIN) { string MethodName = MethodBase.GetCurrentMethod().Name; bool inserted = false; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); //check for IDNumber Masking. If should be maksed then do the masking // Logger.logDebug(ModuleName, MethodName, "Identity Number: " + IdentityNumber, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "Inserting in PIN History", Logger.LOGLEVEL.INFO); string sqlQuery = "INSERT INTO CUSTOMER_PIN_HISTORY (IDENTITY_NUMBER, PIN, CHANNEL_GROUP_ID, CREATED_BY, CREATED_ON, IS_DELETED)"; string queryValues = "VALUES ('" + IdentityNumber + "'," + "'" + transaltedPIN + "'," + "'" + ConfigurationManager.AppSettings["DEFAULT_MIG_HIST_CHANNEL_GROUP_ID"] + "'," + "'" + ConfigurationManager.AppSettings["DEFAULT_MIG_CREATED_BY"] + "'," + "getdate(), 0" + ")"; sqlQuery = sqlQuery + queryValues; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); if (bInsertMigDB) { OledbSqlHelper _dbHelper = new OledbSqlHelper(DBSourceConnString, bDBSourceConnEncrypted); inserted = _dbHelper.ExecuteNonQuery(sqlQuery); } else { inserted = true; } if (inserted) { writeSuccessPINHistoryQueriestoFile(sqlQuery); } else { writeFailedQueriestoFile(sqlQuery); writeFailedPINHistoryCustomerswithQuerytoFile(IdentityNumber); } Logger.logDebug(ModuleName, MethodName, "Insert Result: " + inserted.ToString(), Logger.LOGLEVEL.INFO); return(inserted); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); MessageBox.Show(ex.Message); throw ex; } }
private bool InsertCustomerPINInfo(NCR.EAI.AUB.Entity.CustomerPINInfo _custPINInfo) { string MethodName = MethodBase.GetCurrentMethod().Name; bool inserted = false; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); //check for IDNumber Masking. If should be maksed then do the masking // Logger.logDebug(ModuleName, MethodName, "Identity Number: " + _custPINInfo.IDENTITY_NUMBER, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "Inserting Customer", Logger.LOGLEVEL.INFO); string sqlQuery = "INSERT INTO CUSTOMER_PIN_INFO (IDENTITY_NUMBER,CUST_TYPE_CODE,PIN_TYPE_CODE,CUSTOMER_STATUS_CODE,UTILIZED_IVR_RETRIES,MAX_IVR_RETRIES,LAST_PIN_CHANGE_DATE,LAST_PIN_ENTRY_DATE,LAST_SUCCESFUL_AUTH_DATE,LAST_PIN_GENERATION_DATE,LAST_STATUS_UPDATE_DATE,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,IS_DELETED,DELETED_BY,DELETED_ON,AUTHORIZED_BY,AUTHORIZED_ON)"; //string sqlQuery = "INSERT INTO CUSTOMER_PIN_INFO (IDENTITY_NUMBER, CUST_TYPE_CODE, PIN_TYPE_CODE, CUSTOMER_STATUS_CODE, UTILIZED_IVR_RETRIES,MAX_IVR_RETRIES, CREATED_BY, CREATED_ON, IS_DELETED)"; //string sqlQuery = "INSERT INTO CUSTOMER_PIN_INFO "; string queryValues = "VALUES ('" + _custPINInfo.IDENTITY_NUMBER + "'," + "'" + _custPINInfo.CUST_TYPE_CODE + "'," + "'" + _custPINInfo.PIN_TYPE_CODE + "'," + "'" + _custPINInfo.CUSTOMER_STATUS_CODE + "'," + "" + _custPINInfo.UTILIZED_IVR_RETRIES + "," + "" + _custPINInfo.MAX_IVR_RETRIES + "," + "'" + _custPINInfo.LAST_PIN_CHANGE_DATE + "'," + "'" + _custPINInfo.LAST_PIN_ENTRY_DATE + "'," + "'" + _custPINInfo.LAST_SUCCESFUL_AUTH_DATE + "'," + "'" + _custPINInfo.LAST_PIN_GENERATION_DATE + "'," + "'" + _custPINInfo.LAST_STATUS_UPDATE_DATE + "'," + "'" + _custPINInfo.CREATED_BY + "'," + "'" + _custPINInfo.CREATED_ON + "'," + "'" + _custPINInfo.UPDATED_BY + "'," + "'" + _custPINInfo.UPDATED_ON + "'," + "" + _custPINInfo.IS_DELETED + "," + "'" + _custPINInfo.DELETED_BY + "'," + "'" + _custPINInfo.DELETED_ON + "'," + "'" + _custPINInfo.AUTHORIZED_BY + "'," + "'" + _custPINInfo.AUTHORIZED_ON + "'" + ")"; sqlQuery = sqlQuery + queryValues; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); if (bInsertMigDB) { OledbSqlHelper _dbHelper = new OledbSqlHelper(DBDestConnString, bDBDestConnEncrypted); inserted = _dbHelper.ExecuteNonQuery(sqlQuery); } else { inserted = true; } if (inserted) { writeSuccessQueriestoFile(sqlQuery); } else { writeFailedQueriestoFile(sqlQuery); writeFailedCustomerswithQuerytoFile(_custPINInfo.IDENTITY_NUMBER); } Logger.logDebug(ModuleName, MethodName, "Insert Result: " + inserted.ToString(), Logger.LOGLEVEL.INFO); return(inserted); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); MessageBox.Show(ex.Message); throw ex; } }
private bool migrateCustomerPINInfo() { string MethodName = MethodBase.GetCurrentMethod().Name; DbDataReader _dataReader = null; bool customerFound = false; string ModuleName = "TPINMigrationUtility"; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); OledbSqlHelper _dbHelper = new OledbSqlHelper(DBSourceConnString, bDBSourceConnEncrypted); Logger.logDebug(ModuleName, MethodName, "Fetching Records", Logger.LOGLEVEL.INFO); gstatus = "Fetching Records"; lblStatus.Text = gstatus; string sqlQuery = ConfigurationManager.AppSettings["RETREIVE_RECORDS_LIST"]; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); _dataReader = _dbHelper.ExecuteReader(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Query executed. Retrieving Values", Logger.LOGLEVEL.INFO); gstatus = "Parsing Records"; lblStatus.Text = gstatus; if (_dataReader != null) { if (_dataReader.HasRows) { while (_dataReader.Read()) { customerFound = true; NCR.EAI.AUB.Entity.CustomerPINInfo _custPINInfo = new NCR.EAI.AUB.Entity.CustomerPINInfo(); _custPINInfo.IDENTITY_NUMBER = _dataReader["IDENTITY_NUMBER"].ToString(); Logger.logDebug(ModuleName, MethodName, "CIF : " + _custPINInfo.IDENTITY_NUMBER, Logger.LOGLEVEL.WARNING); _custPINInfo.CUST_TYPE_CODE = _dataReader["CUST_TYPE_CODE"].ToString(); _custPINInfo.PIN_TYPE_CODE = _dataReader["PIN_TYPE_CODE"].ToString(); _custPINInfo.CUSTOMER_STATUS_CODE = _dataReader["CUSTOMER_STATUS_CODE"].ToString(); _custPINInfo.UTILIZED_IVR_RETRIES = _dataReader["UTILIZED_IVR_RETRIES"].ToString(); _custPINInfo.MAX_IVR_RETRIES = _dataReader["MAX_IVR_RETRIES"].ToString(); Logger.logDebug(ModuleName, MethodName, "Getting last pin change date", Logger.LOGLEVEL.WARNING); _custPINInfo.LAST_PIN_CHANGE_DATE = FormatDate(_dataReader["LAST_PIN_CHANGE_DATE"].ToString(), DBSourceDateFormat, DBDestDateFormat); Logger.logDebug(ModuleName, MethodName, "Getting last pin entry date", Logger.LOGLEVEL.WARNING); _custPINInfo.LAST_PIN_ENTRY_DATE = FormatDate(_dataReader["LAST_PIN_ENTRY_DATE"].ToString(), DBSourceDateFormat, DBDestDateFormat); Logger.logDebug(ModuleName, MethodName, "Getting last auth date", Logger.LOGLEVEL.WARNING); _custPINInfo.LAST_SUCCESFUL_AUTH_DATE = FormatDate(_dataReader["LAST_SUCCESFUL_AUTH_DATE"].ToString(), DBSourceDateFormat, DBDestDateFormat); Logger.logDebug(ModuleName, MethodName, "Getting last pin generation date", Logger.LOGLEVEL.WARNING); _custPINInfo.LAST_PIN_GENERATION_DATE = FormatDate(_dataReader["LAST_PIN_GENERATION_DATE"].ToString(), DBSourceDateFormat, DBDestDateFormat); Logger.logDebug(ModuleName, MethodName, "Getting last status update date", Logger.LOGLEVEL.WARNING); _custPINInfo.LAST_STATUS_UPDATE_DATE = FormatDate(_dataReader["LAST_STATUS_UPDATE_DATE"].ToString(), DBSourceDateFormat, DBDestDateFormat); _custPINInfo.CREATED_BY = _dataReader["CREATED_BY"].ToString(); _custPINInfo.CREATED_ON = FormatDate(_dataReader["CREATED_ON"].ToString(), DBSourceDateFormat, DBDestDateFormat); _custPINInfo.UPDATED_BY = _dataReader["UPDATED_BY"].ToString(); _custPINInfo.UPDATED_ON = FormatDate(_dataReader["UPDATED_ON"].ToString(), DBSourceDateFormat, DBDestDateFormat); _custPINInfo.IS_DELETED = _dataReader["IS_DELETED"].ToString(); _custPINInfo.DELETED_BY = _dataReader["DELETED_BY"].ToString(); _custPINInfo.DELETED_ON = FormatDate(_dataReader["DELETED_ON"].ToString(), DBSourceDateFormat, DBDestDateFormat); _custPINInfo.AUTHORIZED_BY = _dataReader["AUTHORIZED_BY"].ToString(); _custPINInfo.AUTHORIZED_ON = FormatDate(_dataReader["AUTHORIZED_ON"].ToString(), DBSourceDateFormat, DBDestDateFormat); //translate the PIN try { bool inserted = InsertCustomerPINInfo(_custPINInfo); if (!inserted) { throw new Exception("Unable to Add customer"); } iSuccess += 1; lblSuccess.Text = iSuccess.ToString(); } catch (Exception ex) { iFailed += 1; lblFailed.Text = iFailed.ToString(); Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); //log query in a separate file // } iProcessed += 1; lblProcessed.Text = iProcessed.ToString(); } } else { Logger.logDebug(ModuleName, MethodName, "No record found", Logger.LOGLEVEL.INFO); customerFound = false; } _dataReader.Close(); //btnMigrate.Enabled = true; gstatus = "Finished"; lblStatus.Text = gstatus; } else { Logger.logDebug(ModuleName, MethodName, "No Records Founds", Logger.LOGLEVEL.INFO); customerFound = false; btnMigrate.Enabled = true; gstatus = "Finished"; lblStatus.Text = gstatus; } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); return(customerFound); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); MessageBox.Show(ex.Message); throw ex; } finally { btnMigrate.Enabled = true; } }
public BillerBeneficary[] GetRecords(string customerID) { string MethodName = MethodBase.GetCurrentMethod().Name; BillerBeneficary[] _recordsArray = null; DbDataReader _dataReader = null; ModuleName = this.GetType().Name; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); //check for IDNumber Masking. If should be maksed then do the masking // OledbSqlHelper _dbHelper = new OledbSqlHelper(ConnString, bConnEncrypted); string sqlQuery = prepareSearchQuery(customerID); Logger.logDebug(ModuleName, MethodName, "Retrieve Select :" + sqlQuery, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); _dataReader = _dbHelper.ExecuteReader(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Query executed. Retrieving Values", Logger.LOGLEVEL.INFO); var list = new List <BillerBeneficary>(); if (_dataReader != null) { if (_dataReader.HasRows) { int irow = 0; while (_dataReader.Read()) { BillerBeneficary _record = new BillerBeneficary(); _record.BeneficiaryNickName = _dataReader["BENEFICIARY_NAME"].ToString(); string billerInfo = _dataReader["BILLER_INFO"].ToString(); Logger.logDebug(ModuleName, MethodName, "Biller Info :"+ billerInfo, Logger.LOGLEVEL.INFO); string[] billerInfoArr = billerInfo.Split('|'); _record.UtilityCompanyCode = billerInfoArr[0]; _record.UtilityAccountType = billerInfoArr[1]; _record.ConsumerNo = billerInfoArr[2]; Logger.logDebug(ModuleName, MethodName, "BeneficiaryNickName :"+ _record.BeneficiaryNickName, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "ConsumerNo :"+ _record.ConsumerNo, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "UtilityCompany :"+ _record.UtilityCompanyCode, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "UtilityAccountType :"+ _record.UtilityAccountType, Logger.LOGLEVEL.INFO); list.Add(_record); //_dataReader.NextResult(); } _recordsArray = list.ToArray(); list = null; } else { Logger.logDebug(ModuleName, MethodName, "No record found", Logger.LOGLEVEL.INFO); } _dataReader.Close(); } else { Logger.logDebug(ModuleName, MethodName, "No Records Founds", Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); return(_recordsArray); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); throw ex; } return(_recordsArray); }
public List <CustomerPINHistory> GetRecords(string maxRecords) { string MethodName = MethodBase.GetCurrentMethod().Name; List <CustomerPINHistory> list = null; OleDbDataReader _dataReader = null; ModuleName = this.GetType().Name; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); //check for IDNumber Masking. If should be maksed then do the masking // bNCRODSConnEncrypted = Convert.ToBoolean(ConfigurationManager.AppSettings["NCRODSEncrypted"]); ODSConnString = ConfigurationManager.ConnectionStrings["NCRODS"].ConnectionString; OledbSqlHelper _dbHelper = new OledbSqlHelper(ODSConnString, bNCRODSConnEncrypted); string selectSQL = prepareSearchWhereClause(); Logger.logDebug(ModuleName, MethodName, "Retrieve Select :" + selectSQL, Logger.LOGLEVEL.INFO); string selectFields = "ID " + ",IDENTITY_NUMBER " + ",PIN " + ",CHANNEL_GROUP_ID " + ",CREATED_BY " + ",CREATED_ON " + ",UPDATED_BY " + ",UPDATED_ON " + ",IS_DELETED " + ",DELETED_BY " + ",DELETED_ON "; //modified by Hashim Ahmed //5th Feb 2016 //to support oracle, there were two options either use row num for oralce and top for sql //in this case two different queries will have to be mantained. //will go with the second option for getting all records and then using linq //string sqlQuery = "SELECT TOP " + maxRecords + " " + selectFields + " FROM CUSTOMER_PIN_HISTORY " + selectSQL + "ORDER BY CREATED_ON DESC"; string sqlQuery = "SELECT " + selectFields + " FROM CUSTOMER_PIN_HISTORY " + selectSQL + "ORDER BY CREATED_ON DESC"; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); _dataReader = _dbHelper.ExecuteReader(sqlQuery); Logger.logDebug(ModuleName, MethodName, "Query executed. Retrieving Values", Logger.LOGLEVEL.INFO); list = new List <CustomerPINHistory>(); if (_dataReader != null) { if (_dataReader.HasRows) { int irow = 0; while (_dataReader.Read()) { CustomerPINHistory _record = new CustomerPINHistory(); _record.ID = Convert.ToInt32(_dataReader["ID"]); _record.IDENTITY_NUMBER = _dataReader["IDENTITY_NUMBER"].ToString(); _record.PIN = _dataReader["PIN"].ToString(); _record.CHANNEL_GROUP_ID = _dataReader["CHANNEL_GROUP_ID"].ToString(); _record.CREATED_BY = _dataReader["CREATED_BY"].ToString(); _record.CREATED_ON = _dataReader["CREATED_ON"].ToString(); _record.UPDATED_BY = _dataReader["UPDATED_BY"].ToString(); _record.UPDATED_ON = _dataReader["UPDATED_ON"].ToString(); _record.IS_DELETED = _dataReader["IS_DELETED"].ToString(); _record.DELETED_BY = _dataReader["DELETED_BY"].ToString(); _record.DELETED_ON = _dataReader["DELETED_ON"].ToString(); Logger.logDebug(ModuleName, MethodName, "ID :"+ _record.ID.ToString(), Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "IDENTITY_NUMBER :"+ _record.IDENTITY_NUMBER, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "CHANNEL_GROUP_ID :"+ _record.CHANNEL_GROUP_ID, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "CREATED_BY :"+ _record.CREATED_BY, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "CREATED_ON :"+ _record.CREATED_ON, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "UPDATED_BY :"+ _record.UPDATED_BY, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "UPDATED_ON :"+ _record.UPDATED_ON, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "IS_DELETED :"+ _record.IS_DELETED, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "DELETED_BY :"+ _record.DELETED_BY, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "DELETED_ON :"+ _record.DELETED_ON, Logger.LOGLEVEL.INFO); list.Add(_record); //_dataReader.NextResult(); } } else { Logger.logDebug(ModuleName, MethodName, "No record found", Logger.LOGLEVEL.INFO); } _dataReader.Close(); } else { Logger.logDebug(ModuleName, MethodName, "No Records Founds", Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); return(list); } catch (Exception ex) { if (_dataReader != null) { _dataReader.Close(); } Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); throw ex; } return(list); }
private bool InsertCustomerTPIN(string IdentityNumber, string EncryptedPIN, string ChannelID, string RelationShipNumber, string createdBy, string AuthorizedBy, string status, string retryAvailable) { string MethodName = MethodBase.GetCurrentMethod().Name; bool inserted = false; try { Logger.logDebug(ModuleName, MethodName, "**********Entered Method**********", Logger.LOGLEVEL.INFO); //check for IDNumber Masking. If should be maksed then do the masking // Logger.logDebug(ModuleName, MethodName, "Identity Number: " + IdentityNumber, Logger.LOGLEVEL.INFO); Logger.logDebug(ModuleName, MethodName, "Inserting Customer", Logger.LOGLEVEL.INFO); /* * [dbo].[CUSTOMER_PIN_AUTHEN]( * [CUSTOMER_ID] [varchar](50) NOT NULL, * [CHANNEL_GROUP_ID] [varchar](50) NOT NULL, * [RELATIONSHIP_NUM] [varchar](50) NOT NULL, * [TPIN] [varchar](50) NOT NULL, * [RETRY_AVAILABLE] [int] NOT NULL, * [MAX_RETRY_COUNT] [int] NOT NULL, * [LAST_PIN_ENTERED_DATE] [datetime] NULL, * [LAST_PIN_CHANGE_DATE] [datetime] NULL, * [TPIN_FLAG] [varchar](50) NULL, * [PIN_VALUE] [varchar](50) NULL, * [PIN_OFFSET] [varchar](50) NULL, * [RESERVED_1] [varchar](50) NULL, * [RESERVED_2] [varchar](50) NULL, * [RESERVED_3] [varchar](50) NULL, * [STATUS] [varchar](50) NOT NULL, * [CREATED_BY] [varchar](50) NOT NULL, * [CREATED_ON] [datetime] NOT NULL, * [UPDATED_BY] [varchar](50) NULL, * [UPDATED_ON] [datetime] NOT NULL, * [AUTHORIZED_BY] [varchar](50) NULL, * [AUTHORIZED_ON] [datetime] NULL, */ if (createdBy == "") { createdBy = "UNKNOWN SYSTEM GENERATED"; } string lastPinChangeDate = ""; string lastPinentryDate = ""; string lastSuccAuthDate = ""; string lastPinGenDate = ""; string lastStatusUpdateDate = ""; string createdOn = ""; string updatedBy = ""; string updatedOn = ""; try { if (bUpdateFromPINInfo) { CustomerPINInfo _tempCustPinInfo = _customerPINInfoList.Where(e => e.IDENTITY_NUMBER == RelationShipNumber).FirstOrDefault(); if (_tempCustPinInfo != null) { if (_tempCustPinInfo.PIN_TYPE_CODE != PIN_TYPE.SPIN.GetHashCode().ToString()) { retryAvailable = "0"; status = "MB"; } Logger.logDebug(ModuleName, MethodName, "Getting last pin change date", Logger.LOGLEVEL.WARNING); lastPinChangeDate = FormatDate(_tempCustPinInfo.LAST_PIN_CHANGE_DATE, DBSourceDateFormat, DBDestDateFormat); Logger.logDebug(ModuleName, MethodName, "Getting last pin entry date", Logger.LOGLEVEL.WARNING); lastPinentryDate = FormatDate(_tempCustPinInfo.LAST_PIN_ENTRY_DATE, DBSourceDateFormat, DBDestDateFormat); Logger.logDebug(ModuleName, MethodName, "Getting last auth date", Logger.LOGLEVEL.WARNING); lastSuccAuthDate = FormatDate(_tempCustPinInfo.LAST_SUCCESFUL_AUTH_DATE, DBSourceDateFormat, DBDestDateFormat); Logger.logDebug(ModuleName, MethodName, "Getting last pin generation date", Logger.LOGLEVEL.WARNING); lastPinGenDate = FormatDate(_tempCustPinInfo.LAST_PIN_GENERATION_DATE, DBSourceDateFormat, DBDestDateFormat); Logger.logDebug(ModuleName, MethodName, "Getting last status update date", Logger.LOGLEVEL.WARNING); lastStatusUpdateDate = FormatDate(_tempCustPinInfo.LAST_STATUS_UPDATE_DATE, DBSourceDateFormat, DBDestDateFormat); createdBy = _tempCustPinInfo.CREATED_BY; createdOn = FormatDate(_tempCustPinInfo.CREATED_ON, DBSourceDateFormat, DBDestDateFormat); updatedBy = _tempCustPinInfo.UPDATED_BY; updatedOn = FormatDate(_tempCustPinInfo.UPDATED_ON, DBSourceDateFormat, DBDestDateFormat); } } } catch (Exception ex) { } if (createdOn == null || createdOn == "") { createdOn = "getdate()"; } if (createdBy == null || createdBy == "") { createdBy = "MIGRATED"; } string sqlQuery = "INSERT INTO CUSTOMER_PIN_AUTHEN (CUSTOMER_ID, CHANNEL_GROUP_ID, RELATIONSHIP_NUM, TPIN, RETRY_AVAILABLE,MAX_RETRY_COUNT, STATUS,CREATED_BY, CREATED_ON, RESERVED_3, AUTHORIZED_BY, AUTHORIZED_ON"; string queryValues = "VALUES ('" + IdentityNumber + "'," + "'" + ChannelID + "'," + "'" + RelationShipNumber + "'," + "'" + EncryptedPIN + "'," + "" + retryAvailable + "," + "" + DefaultMaxRetryCount.ToString() + "," + "'" + status + "'," + "'" + createdBy + "'," + "getdate()," + "'MIGRATED'," + "'" + AuthorizedBy + "'," + "getdate()"; if (updatedOn != "") { sqlQuery += ", UPDATED_ON"; queryValues += ", '" + updatedOn + "'"; } if (updatedBy != "") { sqlQuery += ", UPDATED_BY"; queryValues += ", '" + updatedBy + "'"; } if (lastPinentryDate != "") { sqlQuery += ", LAST_PIN_ENTERED_DATE"; queryValues += ", '" + lastPinentryDate + "'"; } if (lastPinChangeDate != "") { sqlQuery += ", LAST_PIN_CHANGE_DATE"; queryValues += ", '" + lastPinChangeDate + "'"; } sqlQuery = sqlQuery + ")" + queryValues + ")"; Logger.logDebug(ModuleName, MethodName, "Executing Query : " + sqlQuery, Logger.LOGLEVEL.INFO); if (bInsertMigDB) { OledbSqlHelper _dbHelper = new OledbSqlHelper(DBDestConnString, bDBDestConnEncrypted); inserted = _dbHelper.ExecuteNonQuery(sqlQuery); } else { inserted = true; } if (inserted) { writeSuccessQueriestoFile(sqlQuery); } else { writeFailedQueriestoFile(sqlQuery); writeFailedCustomerswithQuerytoFile(IdentityNumber); } Logger.logDebug(ModuleName, MethodName, "Insert Result: " + inserted.ToString(), Logger.LOGLEVEL.INFO); return(inserted); } catch (Exception ex) { Logger.logDebug(ModuleName, MethodName, "Exception : " + ex.Message, Logger.LOGLEVEL.INFO); if (ex.InnerException != null) { Logger.logDebug(ModuleName, MethodName, "Inner Exception: " + ex.InnerException.Message, Logger.LOGLEVEL.INFO); } Logger.logDebug(ModuleName, MethodName, "**********Exiting Method**********", Logger.LOGLEVEL.INFO); MessageBox.Show(ex.Message); throw ex; } }