private PlacementsAccount AutoMerge(PlacementsAccount acct1, PlacementsAccount acct2)
{
var mergedAccount = new PlacementsAccount();
mergedAccount.PlacementsId = Guid.NewGuid();
mergedAccount.Name = String.IsNullOrWhiteSpace(acct1.Name) ? acct2.Name : acct1.Name;
mergedAccount.Phone = String.IsNullOrWhiteSpace(acct1.Phone) ? acct2.Phone : acct1.Phone;
mergedAccount.Email = String.IsNullOrWhiteSpace(acct1.Email) ? acct2.Email : acct1.Email;
mergedAccount.Category = String.IsNullOrWhiteSpace(acct1.Category) ? acct2.Category : acct1.Category;
mergedAccount.Address1 = String.IsNullOrWhiteSpace(acct1.Address1) ? acct2.Address1 : acct1.Address1;
mergedAccount.Address2 = String.IsNullOrWhiteSpace(acct1.Address2) ? acct2.Address2 : acct1.Address2;
mergedAccount.City = String.IsNullOrWhiteSpace(acct1.City) ? acct2.City : acct1.City;
mergedAccount.State = String.IsNullOrWhiteSpace(acct1.State) ? acct2.State : acct1.State;
mergedAccount.Zip = String.IsNullOrWhiteSpace(acct1.Zip) ? acct2.Zip : acct1.Zip;
mergedAccount.Country = String.IsNullOrWhiteSpace(acct1.Country) ? acct2.Country : acct1.Country;
mergedAccount.FaxPhone = String.IsNullOrWhiteSpace(acct1.FaxPhone) ? acct2.FaxPhone : acct1.FaxPhone;
mergedAccount.Description = String.IsNullOrWhiteSpace(acct1.Description) ? acct2.Description : acct1.Description;
mergedAccount.CreditStatus = String.IsNullOrWhiteSpace(acct1.CreditStatus) ? acct2.CreditStatus : acct1.CreditStatus;
mergedAccount.DFPId = acct1.DFPId == 0 ? acct2.DFPId : acct1.DFPId;
mergedAccount.SFId = String.IsNullOrWhiteSpace(acct1.SFId) ? acct2.SFId : acct1.SFId;
mergedAccount.Website = String.IsNullOrWhiteSpace(acct1.Website) ? acct2.Website : acct1.Website;
mergedAccount.UserId = String.IsNullOrWhiteSpace(acct1.UserId) ? acct2.UserId : acct1.UserId;
mergedAccount.ParentId = acct1.ParentId == Guid.Empty ? acct2.ParentId : acct1.ParentId;
return(mergedAccount);
}
public static AccountMerger.MergeResult ResolveMerge(PlacementsAccount acct1, PlacementsAccount acct2)
{
Console.Clear();
Console.WriteLine("Press y to merge, p1 if account 1 is parent and p2 if account 2 is parent or any other key to keep accounts separate.");
Console.WriteLine("Account 1:");
Console.WriteLine(acct1.ToString());
Console.WriteLine();
Console.WriteLine("Account 2:");
Console.WriteLine(acct2.ToString());
var input = Console.ReadLine();
Console.Clear();
if (input == "y")
{
return(AccountMerger.MergeResult.Merge);
}
else if (input == "p1")
{
return(AccountMerger.MergeResult.LeftIsParentOfRight);
}
else if (input == "p2")
{
return(AccountMerger.MergeResult.RightIsParentOfLeft);
}
else
{
return(AccountMerger.MergeResult.NoMatch);
}
}
/// <summary>
/// The beginnings of some basic sanitization of the data. Ideally this would be made more robust.
/// </summary>
/// <param name="acct"></param>
/// <returns></returns>
private PlacementsAccount SanitizeAccount(PlacementsAccount acct)
{
if (!String.IsNullOrWhiteSpace(acct.Website) && !acct.Website.ToLower().StartsWith("http://"))
{
acct.Website = "http://" + acct.Website;
}
return(acct);
}
/// <summary>
/// Basic matching algorithm that builds a confidence ranking based on matching properties, for now focusing on Name, Phone, and Email
/// </summary>
/// <param name="item1"></param>
/// <param name="item2"></param>
/// <returns></returns>
private int MatchConfidence(PlacementsAccount item1, PlacementsAccount item2)
{
var confidence = 0;
if (item1.Email != null && item2.Email != null && item1.Email.ToLower() == item2.Email.ToLower())
{
confidence += 20;
}
if (item1.Phone != null && item2.Phone != null && item1.Phone == item2.Phone)
{
confidence += 20;
}
if (item1.Name != null && item2.Name != null && item1.Name.ToLower() == item2.Name.ToLower())
{
confidence += 50;
}
else if (item1.Name != null && item2.Name != null)
{
// Grab only the alphanumeric chars to filter out punctuation or any weird chars that could vary.
// The method used here should perform better than a reg expression.
var item1NameSanitized = new string(item1.Name.ToLower().Where(c => (char.IsLetterOrDigit(c) ||
char.IsWhiteSpace(c))).ToArray());
var item2NameSanitized = new string(item2.Name.ToLower().Where(c => (char.IsLetterOrDigit(c) ||
char.IsWhiteSpace(c))).ToArray());
// Breakdown the name into individual words and look for ones in common
var item1NameTokens = new HashSet <string>(item1NameSanitized.ToLower().Split(' '));
item1NameTokens.RemoveWhere(w => (ignored.Contains(w)));
var item2NameTokens = new HashSet <string>(item2NameSanitized.ToLower().Split(' '));
item2NameTokens.RemoveWhere(w => (ignored.Contains(w)));
var matchedWords = item1NameTokens.Intersect(item2NameTokens).Count();
var totalWords = item1NameTokens.Count < item2NameTokens.Count ? item1NameTokens.Count : item2NameTokens.Count;
if (matchedWords == totalWords)
{
confidence += 50;
}
else if ((double)matchedWords / totalWords > 0.5)
{
confidence += 30;
}
else if (matchedWords >= 2)
{
confidence += 20;
}
}
return(confidence);
}
private static PlacementsAccount ConvertGoogleCompanyToPlacementsAccount(Company googCompany)
{
var acct = new PlacementsAccount();
acct.PlacementsId = Guid.NewGuid();
acct.Name = googCompany.name;
acct.Phone = googCompany.primaryPhone;
acct.Email = googCompany.email;
acct.FaxPhone = googCompany.faxPhone;
acct.Description = googCompany.comment;
acct.Address1 = googCompany.address;
acct.CreditStatus = googCompany.creditStatus.ToString();
acct.DFPId = googCompany.id;
return(acct);
}
private static PlacementsAccount ConvertSalesForceAccountToPlacementsAccount(Account salesforceAcct)
{
var acct = new PlacementsAccount();
acct.PlacementsId = Guid.NewGuid();
acct.Name = salesforceAcct.Name;
acct.Phone = salesforceAcct.Phone;
acct.FaxPhone = salesforceAcct.Fax;
acct.Description = salesforceAcct.Description;
acct.Address1 = salesforceAcct.BillingStreet;
acct.City = salesforceAcct.BillingCity;
acct.Zip = salesforceAcct.BillingPostalCode;
acct.State = salesforceAcct.BillingState;
acct.Country = salesforceAcct.BillingCountry;
acct.Category = salesforceAcct.Industry;
acct.Website = salesforceAcct.Website;
acct.UserId = salesforceAcct.OwnerID;
acct.SFId = salesforceAcct.Id;
return(acct);
}
/// <summary>
/// Merge 2 given lists of Accounts into one list using some matching heuristics on some relevant fields like name, email, phone
///
/// Basic algorithm:
/// For every account in one list
/// Compare it to every account in the other list until there is a probable match.
/// If it's a strong match then auto merge.
/// If it's a weak match then request user input.
/// Remove the account from the second list
/// If no match then add the account from first list as is
/// Add all remaining accounts from second list
///
///
/// </summary>
/// <param name="salesForceAccounts"></param>
/// <param name="googleAccounts"></param>
/// <param name="mergeCheck"></param>
/// <returns></returns>
public AccountMergeStats MergeAccountLists(List <PlacementsAccount> salesForceAccounts, List <PlacementsAccount> googleAccounts, Func <PlacementsAccount, PlacementsAccount, MergeResult> mergeCheck = null)
{
var stats = new AccountMergeStats();
MergedAccountList = new List <PlacementsAccount>();
foreach (PlacementsAccount sfAcc in salesForceAccounts)
{
PlacementsAccount matchedAccountToRemove = null;
foreach (PlacementsAccount googAcc in googleAccounts)
{
var confidence = MatchConfidence(sfAcc, googAcc);
if (confidence >= 50)
{
// automerge
MergedAccountList.Add(SanitizeAccount(AutoMerge(sfAcc, googAcc)));
stats.Total++;
stats.AutoMerged++;
matchedAccountToRemove = googAcc;
break;
}
else if (confidence >= 20)
{
// request user input
if (mergeCheck != null)
{
var choice = mergeCheck(sfAcc, googAcc);
// If the choice is null then there is no match and we proceed without merge, otherwise use the chosen account.
if (choice == MergeResult.Merge)
{
MergedAccountList.Add(SanitizeAccount(AutoMerge(sfAcc, googAcc)));
stats.Total++;
stats.ManuallyMerged++;
matchedAccountToRemove = googAcc;
break;
}
else if (choice == MergeResult.LeftIsParentOfRight)
{
if (googAcc.ParentId == Guid.Empty)
{
googAcc.ParentId = sfAcc.PlacementsId;
}
}
else if (choice == MergeResult.RightIsParentOfLeft)
{
if (sfAcc.ParentId == Guid.Empty)
{
sfAcc.ParentId = googAcc.PlacementsId;
}
}
}
}
}
if (matchedAccountToRemove == null)
{
// No matching google account - add the salesforce account
MergedAccountList.Add(SanitizeAccount(sfAcc));
stats.Total++;
}
else
{
googleAccounts.Remove(matchedAccountToRemove);
}
}
foreach (PlacementsAccount googAcc in googleAccounts)
{
// Add all remaining, unmatched google accounts
MergedAccountList.Add(SanitizeAccount(googAcc));
stats.Total++;
}
return(stats);
}
