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, 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.AVAILABLE_IVR_RETRIES + "," +
"'" + _custPINInfo.LAST_PIN_CHANGE_DATE + "'," +
"'" + _custPINInfo.LAST_PIN_ENTRY_DATE + "'," +
"'" + _custPINInfo.LAST_PIN_GENERATION_DATE + "'," +
"'" + _custPINInfo.LAST_STATUS_UPDATE_DATE + "'," +
"'" + _custPINInfo.LAST_SUCCESFUL_AUTH_DATE + "'," +
"'" + _custPINInfo.CREATED_BY + "'," +
"'" + _custPINInfo.CREATED_ON + "'," +
"'" + _custPINInfo.UPDATED_BY + "'," +
"'" + _custPINInfo.UPDATED_ON + "'," +
"'" + _custPINInfo.AUTHORIZED_BY + "'," +
"'" + _custPINInfo.AUTHORIZED_ON + "'," +
"" + _custPINInfo.IS_DELETED + "" +
")";
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;
}
}
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 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;
}
}