public List <CustomerMatchInfoDetails> GetMatchingCustomers(NameAndAddress nameAndAddress, SuppressionOptions suppressionOptions, string matchType)
{
CustomerMatchParameter customerMatchParameter = new CustomerMatchParameter();
customerMatchParameter.MatchType = matchType;
customerMatchParameter.Address1 = nameAndAddress.Address.Address1;
customerMatchParameter.Address2 = nameAndAddress.Address.Address2;
customerMatchParameter.Address3 = nameAndAddress.Address.Address3;
customerMatchParameter.Address4 = nameAndAddress.Address.Address4;
customerMatchParameter.Postcode = nameAndAddress.Address.Postcode;
customerMatchParameter.Dob = nameAndAddress.Dob;
customerMatchParameter.Phone = nameAndAddress.Phone;
customerMatchParameter.Email = nameAndAddress.Email;
customerMatchParameter.FirstName = nameAndAddress.FirstName;
customerMatchParameter.Surname = nameAndAddress.Surname;
customerMatchParameter.Title = nameAndAddress.Title;
List <MatchedCustomer> matchedCustomers = _customerDataAccess.GetMatchingCustomers(customerMatchParameter);
List <CustomerMatchInfoDetails> customerMatchDetails = new List <CustomerMatchInfoDetails>();
foreach (var c in matchedCustomers)
{
customerMatchDetails.Add(_customerDataAccess.GetCustomerMatchDetails(c.CustomerId, suppressionOptions));
}
return(customerMatchDetails);
}
// TODO : Test refactored match calls from this handler class
/// <summary>
/// Call match_customers_func for a specified match type
/// </summary>
/// <param name="customerMatchParameter"></param>
/// <returns></returns>
private List <MatchedCustomer> MatchWithSpecifiedMatchType(CustomerMatchParameter customerMatchParameter)
{
List <MatchedCustomer> customerIds;
try
{
var matchCustomerFunc = "select customer_id from [dbo].[match_customers_func](@matchtype,@title,@firstname,@surname,@dob,@email,@phone,@postcode,@address1,@address2,@address3,@address4)";
using (SqlConnection conn = new SqlConnection(_mciConnectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(matchCustomerFunc, conn)
{
CommandType = CommandType.Text
};
command.Parameters.AddParameter(new SqlParameter("@matchtype", customerMatchParameter.MatchType ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@title", customerMatchParameter.Title ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@firstName", customerMatchParameter.FirstName ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@surname", customerMatchParameter.Surname ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@dob", PrepareDobString(customerMatchParameter.Dob) ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@email", customerMatchParameter.Email ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@phone", customerMatchParameter.Phone ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@postcode", customerMatchParameter.Postcode ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@address1", customerMatchParameter.Address1 ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@address2", customerMatchParameter.Address2 ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@address3", customerMatchParameter.Address3 ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@address4", customerMatchParameter.Address4 ?? (object)DBNull.Value));
var adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
adapter.Dispose();
int?[] matchedCustomerIds = table.AsEnumerable().Select(s => s.Field <int?>("customer_id")).ToArray();
customerIds = BuildCustomerIdList(matchedCustomerIds);
return(customerIds);
}
}
catch (Exception ex)
{
// _logger.Error("CustomerMatchDataAccessHandler.MatchWithSpecifiedMatchType():" + "ErrorTag: " + ErrorTagProvider.ErrorTagDatabase + " -- " + ex.Message, ex);
LogException("CustomerMatchDataAccessHandler.MatchWithSpecifiedMatchType(): ErrorTag: ", customerMatchParameter, ex);
if (_writeParameterValuesToLog)
{
LogErrorParrameters(customerMatchParameter);
}
throw new Exception(String.Format(DatabaseMessage.DatabaseException, ErrorTagProvider.ErrorTagDatabase));
}
}
private void LogException(string messagePrefix, CustomerMatchParameter customerMatchParameter, Exception ex)
{
_logger.Error(
messagePrefix +
ErrorTagProvider.ErrorTagDatabase + " -- " + ex.Message, ex);
if (_writeParameterValuesToLog)
{
LogErrorParrameters(customerMatchParameter);
}
}
public List <MatchedCustomer> MatchCustomers(CustomerMatchParameter customerMatchParameter)
{
List <MatchedCustomer> customerIds = null;
// Known specific match type which is not listed in the list of MatchTypes for which default
// matching rules should be applied
if (MatchTypes.Contains(customerMatchParameter.MatchType)
&&
!MatchTypesOverrideMatchRules.Contains(customerMatchParameter.MatchType)
)
{
customerIds = MatchWithSpecifiedMatchType(customerMatchParameter);
}
else // Unknown match type - let the Db routines decide
{
customerIds = MatchWithDefaultMatchTypeRules(customerMatchParameter);
}
return(customerIds);
}
public List <MatchedCustomer> GetMatchingCustomers(CustomerMatchParameter p)
{
List <MatchedCustomer> customerIds = null;
try
{
customerIds = _customerDataAccess.GetMatchingCustomers(p);
}
catch (SqlException ex)
{
_logger.Error("GetMatchingCustomers:" + "ErrorTag: " + ErrorTagProvider.ErrorTagDatabase + " -- " + ex.Message, ex);
throw new Exception(string.Format(DatabaseMessage.DatabaseException, ErrorTagProvider.ErrorTagDatabase));
}
catch (Exception ex)
{
_logger.Error("GetMatchingCustomers:" + "ErrorTag: " + ErrorTagProvider.ErrorTagDatabase + " -- " + ex.Message, ex);
throw new Exception(string.Format(DatabaseMessage.DatabaseException, ErrorTagProvider.ErrorTagDatabase));
}
return(customerIds);
}
private int?GetPersistantKey(NameAndAddress nameAndAddress)
{
CustomerMatchParameter param = new CustomerMatchParameter();
param.FirstName = nameAndAddress.FirstName;
param.Surname = nameAndAddress.Surname;
param.Dob = nameAndAddress.Dob;
param.Title = nameAndAddress.Title;
param.Email = nameAndAddress.Email;
param.Phone = nameAndAddress.Phone;
param.Address1 = nameAndAddress.Address.Address1;
param.Address2 = nameAndAddress.Address.Address2;
param.Address3 = nameAndAddress.Address.Address3;
param.Address4 = nameAndAddress.Address.Address4;
param.Postcode = nameAndAddress.Address.Postcode;
param.MatchType = "NAME_AND_ADDRESS";
var matchedCustomers = _customerDataAccess.GetMatchingCustomers(param).FirstOrDefault();
return(matchedCustomers?.CustomerId);
}
private void LogErrorParrameters(CustomerMatchParameter customerMatchParameter)
{
var matchType = customerMatchParameter.MatchType;
var title = string.IsNullOrEmpty(customerMatchParameter.Title) ? "null" : customerMatchParameter.Title;
var firstName = string.IsNullOrEmpty(customerMatchParameter.FirstName) ? "null" : customerMatchParameter.FirstName;
var surName = string.IsNullOrEmpty(customerMatchParameter.Surname) ? "" : customerMatchParameter.Surname;
var dob = PrepareDobString(customerMatchParameter.Dob);
var email = string.IsNullOrEmpty(customerMatchParameter.Email) ? "null" : customerMatchParameter.Email;
var phone = string.IsNullOrEmpty(customerMatchParameter.Phone) ? "null" : customerMatchParameter.Phone;
var postcode = string.IsNullOrEmpty(customerMatchParameter.Postcode) ? "null" : customerMatchParameter.Postcode;
var address1 = string.IsNullOrEmpty(customerMatchParameter.Address1) ? "null" : customerMatchParameter.Address1;
var address2 = string.IsNullOrEmpty(customerMatchParameter.Address2) ? "null" : customerMatchParameter.Address2;
var address3 = string.IsNullOrEmpty(customerMatchParameter.Address3) ? "null" : customerMatchParameter.Address3;
var address4 = string.IsNullOrEmpty(customerMatchParameter.Address4) ? "null" : customerMatchParameter.Address4;
_logger.Error(
$"Parameters CustomerMatchDataAccessHandler:- Name=FirstName={firstName}, LastName={surName}, Dob={dob}, " +
$"Address=AddresLine1={address1}, AddressLine2={address2}, AddressLine3={address3}, " +
$"AddressLine4={address4}, PostCode={postcode}");
}
public int?GetPersistantKey(NameAndAddress nameAndAddress)
{
CustomerMatchParameter param = new CustomerMatchParameter();
param.FirstName = nameAndAddress.FirstName;
param.Surname = nameAndAddress.Surname;
param.Dob = nameAndAddress.Dob;
param.Title = nameAndAddress.Title;
param.Email = nameAndAddress.Email;
param.Phone = nameAndAddress.Phone;
param.Address1 = nameAndAddress.Address.Address1;
param.Address2 = nameAndAddress.Address.Address2;
param.Address3 = nameAndAddress.Address.Address3;
param.Address4 = nameAndAddress.Address.Address4;
param.Postcode = nameAndAddress.Address.Postcode;
// GICTS-3 Customer Matching on first character : Don't set a default match type
//
param.MatchType = nameAndAddress.MatchType;
// changed for ACSU-113 to use passed value or default to POSTCODE_AND_NAME
/*
* if (string.IsNullOrEmpty(param.MatchType) == true)
* {
* // default to POSTCODE_AND_NAME
* param.MatchType = MatchType.POSTCODE_AND_NAME.ToString();
* }
* else
* {
* param.MatchType = nameAndAddress.MatchType;
* }
*/
var matchedCustomers = _mailingHistoryDataAccess.GetMatchingCustomers(param).FirstOrDefault();
return(matchedCustomers?.CustomerId);
}
public List <MatchedCustomer> GetMatchingCustomers(CustomerMatchParameter p)
{
// GITCS-3 : Call new injected Handler inplace of the block below
return(_customerMatchDataAccessHandler.MatchCustomers(p));
}
/// <summary>
/// Call new stored proc
/// </summary>
/// <param name="customerMatchParameter"></param>
/// <returns></returns>
private List <MatchedCustomer> MatchWithDefaultMatchTypeRules(CustomerMatchParameter customerMatchParameter)
{
string errorLogPrefix = "CustomerMatchDataAccessHandler.MatchWithDefaultMatchTypeRules(): ErrorTag: ";
List <MatchedCustomer> customerIds = new List <MatchedCustomer>();
using (var conn = new SqlConnection(_mciConnectionString))
{
using (var cmd = new SqlCommand("dbo.match_customer_default_rules", conn))
{
try
{
cmd.Connection.Open();
cmd.Parameters.AddParameter(new SqlParameter("@p_title",
customerMatchParameter.Title ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@p_first_name",
customerMatchParameter.FirstName ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@p_surname",
customerMatchParameter.Surname ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@p_dob",
PrepareDobString(customerMatchParameter.Dob) ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@p_postcode",
customerMatchParameter.Postcode ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@p_housename", (object)DBNull.Value)) // Available to us ?
.AddParameter(new SqlParameter("@p_housenumber", (object)DBNull.Value)) //
.AddParameter(new SqlParameter("@p_address1",
customerMatchParameter.Address1 ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@p_address2",
customerMatchParameter.Address2 ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@p_address3",
customerMatchParameter.Address3 ?? (object)DBNull.Value))
.AddParameter(new SqlParameter("@p_address4",
customerMatchParameter.Address4 ?? (object)DBNull.Value))
.AddParameter(
new SqlParameter("@p_customer_id", SqlDbType.Int).SetDirection(
ParameterDirection.Output));
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
var customerId =
cmd.Parameters["@p_customer_id"].Value.ToString()
.ToIntOrDefault(); // Ensure we handle empty string results ...
if (customerId > 0)
{
customerIds.Add(new MatchedCustomer {
CustomerId = customerId
});
}
}
catch (SqlException ex)
{
LogException(errorLogPrefix, customerMatchParameter, ex);
throw new Exception(string.Format(DatabaseMessage.DatabaseException,
ErrorTagProvider.ErrorTagDatabase));
}
catch (Exception ex)
{
LogException(errorLogPrefix, customerMatchParameter, ex);
if (_writeParameterValuesToLog)
{
LogErrorParrameters(customerMatchParameter);
}
throw new Exception(string.Format(DatabaseMessage.DatabaseException,
ErrorTagProvider.ErrorTagDatabase));
}
}
}
return(customerIds);
}
public dynamic GetMailingHistoryList(Models.MailingHistory.MailingHistory mailingHistory = null, NameAndAddress nameAndAddress = null, IEnumerable <int> customerIdList = null)
{
CustomerMatchParameter parameter = new CustomerMatchParameter();
if (nameAndAddress != null)
{
parameter.MatchType = "NAME_AND_ADDRESS";
parameter.Address1 = nameAndAddress.Address.Address1;
parameter.Address2 = nameAndAddress.Address.Address2;
parameter.Address3 = nameAndAddress.Address.Address3;
parameter.Address4 = nameAndAddress.Address.Address4;
parameter.Postcode = nameAndAddress.Address.Postcode;
parameter.Dob = nameAndAddress.Dob;
parameter.Phone = nameAndAddress.Phone;
parameter.Email = nameAndAddress.Email;
parameter.FirstName = nameAndAddress.FirstName;
parameter.Surname = nameAndAddress.Surname;
parameter.Title = nameAndAddress.Title;
if (mailingHistory != null)
{
parameter.FromDate = mailingHistory.FromDate;
parameter.ToDate = mailingHistory.ToDate;
parameter.Product = mailingHistory.Product;
}
}
List <ExpandoObject> expandoObject = new List <ExpandoObject>();
try
{
List <MatchedCustomer> customerIds = new List <MatchedCustomer>();
if (customerIdList == null)
{
customerIds = _dataAccess.GetMatchingCustomers(parameter);
}
else
{
foreach (var id in customerIdList)
{
customerIds.Add(new MatchedCustomer {
CustomerId = id
});
}
}
List <int> ids = new List <int>();
foreach (var item in customerIds)
{
ids.Add(item.CustomerId);
}
List <MailingHistoryResult> list = new List <MailingHistoryResult>();
if (mailingHistory != null)
{
list = _dataAccess.GetCustomerMailingHistory(null, ids, mailingHistory.Product,
mailingHistory.FromDate, mailingHistory.ToDate, string.Empty);
if (mailingHistory.FieldsTobeReturned.Contains(null) || mailingHistory.FieldsTobeReturned.Count == 0)
{
return(list);
}
}
else
{
list = _dataAccess.GetCustomerMailingHistory(null, ids, string.Empty,
null, null, null);
return(list);
}
DataShapedObject dataShapedObject = new DataShapedObject();
foreach (var item in list)
{
if (mailingHistory.FieldsTobeReturned != null &&
mailingHistory.FieldsTobeReturned.Count != 0)
{
expandoObject.Add(dataShapedObject.Create(item, mailingHistory.FieldsTobeReturned));
}
}
}
catch (Exception ex)
{
_logger.Info("MailingHistoryService.GetMailingHistoryList " + ex.Message, ex);
throw new Exception(ex.Message);
}
return(expandoObject);
}
