Beispiel #1
0
        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;
            }
        }
Beispiel #5
0
        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;
            }
        }
Beispiel #8
0
        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);
        }
Beispiel #9
0
        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;
            }
        }
Beispiel #15
0
        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;
            }
        }