static IEnumerable <Person> ByImportedPayments(IImportingPerson source)
{
if (string.IsNullOrEmpty(source.FinalFour))
{
return(Enumerable.Empty <Person>());
}
return(AppFramework.Table <Payment>().Rows
.Where(p => p.Method == "Credit Card" && p.CheckNumber == source.FinalFour)
.Select(p => p.Person)
.Where(p => GetMatchScore(source, p) == 0));
}
static IEnumerable <Person> ByEmail(IImportingPerson source)
{
if (string.IsNullOrEmpty(source.Email))
{
yield break;
}
var email = AppFramework.Table <EmailAddress>().Rows
.FirstOrDefault(e => e.Email.Equals(source.Email, StringComparison.OrdinalIgnoreCase));
if (email?.Person != null)
{
yield return(email.Person);
}
}
private void doImport_ItemClick(object sender, ItemClickEventArgs e)
{
using (AppFramework.Current.DataContext.BeginLoadData())
ProgressWorker.Execute(MdiParent, progress => {
progress.Maximum = AppFramework.Table <StagedPerson>().Rows.Count;
foreach (var source in AppFramework.Table <StagedPerson>().Rows)
{
Person target;
progress.Progress++;
if (source.Person != null)
{
target = source.Person;
progress.Caption = $"Importing {source.FullName} to {target.FullName}";
}
else
{
progress.Caption = $"Importing {source.FullName} as new person";
target = new Person {
Address = source.Address,
City = source.City,
FullName = source.FullName,
HerName = source.HerName,
HisName = source.HisName,
LastName = source.LastName,
Phone = source.Phone,
State = source.State,
Zip = source.Zip,
Salutation = "",
Source = "Migration",
};
AppFramework.Table <Person>().Rows.Add(target);
}
foreach (var payment in source.StagedPayments)
{
AppFramework.Table <Payment>().Rows.Add(new Payment {
Account = payment.Account,
Amount = payment.Amount,
CheckNumber = payment.CheckNumber,
Comments = payment.Comments,
Company = payment.Company,
Date = payment.Date,
Method = payment.Method,
Person = target,
Modifier = "Migration",
// TODO: Change ExternalId to string and always set it.
ExternalSource = "Migration",
ExternalId = int.TryParse(payment.ExternalId, out var id) ? id : new int?()
});
}
static IEnumerable <Person> ByPhone(IImportingPerson source)
{
var phone = source.Phone.FormatPhoneNumber();
if (string.IsNullOrEmpty(phone))
{
yield break;
}
var match = AppFramework.Table <Person>().Rows.FirstOrDefault(p => p.Phone == phone);
if (match != null && GetMatchScore(source, match) == 0)
{
yield return(match);
}
}
static IEnumerable <Person> Fuzzy(IImportingPerson source)
{
IEnumerable <Person> candidates = AppFramework.Table <Person>().Rows;
// Filter by each field, but only if that field has any matches.
if (!string.IsNullOrEmpty(source.Address))
{
var sourceAddress = AddressInfo.Parse(source.Address);
var sourceState = UsStates.Abbreviate(source.State);
candidates = candidates.Where(p =>
IsMatch(p.Zip, source.Zip) && IsMatch(p.State, sourceState) &&
IsMatch(p.City, source.City) && AddressInfo.Parse(p.Address) == sourceAddress)
.DefaultIfEmpty(candidates);
}
candidates = candidates.Where(p => p.LastName.Equals(source.LastName, StringComparison.CurrentCultureIgnoreCase))
.DefaultIfEmpty(candidates);
if (!string.IsNullOrWhiteSpace(source.LastName))
{
candidates = candidates.LevenshteinDistanceOf(p => p.LastName).ComparedTo(source.LastName)
.BestMatches()
.DefaultIfEmpty(candidates);
}
// Match the imported first name against either HisName or HerName.
if (!string.IsNullOrWhiteSpace(source.FirstName))
{
candidates = candidates.LevenshteinDistanceOf(p => p.HisName).ComparedTo(source.FirstName)
.Union(candidates.LevenshteinDistanceOf(p => p.HerName).ComparedTo(source.FirstName))
.BestMatches()
.Distinct()
.DefaultIfEmpty(candidates);
}
// If none of the matches found anything, give up.
if (candidates == AppFramework.Table <Person>().Rows)
{
return(Enumerable.Empty <Person>());
}
return(candidates);
}
public static Genderizer Create() => new Genderizer(AppFramework.Table <Person>().Rows);
public void Import(string fileName, SynchronizationContext uiThread, IProgressReporter progress)
{
var methodMap = Names.PaymentMethods.ToDictionary(k => k, StringComparer.CurrentCultureIgnoreCase);
methodMap.Add("American Express", "Credit Card");
methodMap.Add("MasterCard", "Credit Card");
methodMap.Add("Visa", "Credit Card");
progress.Caption = "Reading " + Path.GetFileName(fileName);
var genderizer = Genderizer.Create();
var connector = DB.OpenFile(fileName);
var matchTasks = new LinkedList <Task>();
progress.Maximum = connector.ExecuteScalar <int>("SELECT COUNT(*) FROM [Sheet1$]");
using (var reader = connector.ExecuteReader("SELECT * FROM [Sheet1$]")) {
StagedPerson person = new StagedPerson();
string company = null;
// The source file is denormalized, and contains one row per payment,
// with columns describing the person as part of the payment. People
// are separated by rows with values in the second column.
while (reader.Read())
{
if (progress.WasCanceled)
{
return;
}
progress.Progress++;
// If we're at a boundary between people, skip the row, and start
// a new person. The second row in the boundary will noop.
if (!reader.IsDBNull(1))
{
person = new StagedPerson();
continue;
}
// Stores columns that have been used for actual properties. All
// other non-empty columns will be added to Comments.
var usedValues = new Dictionary <int, string>();
// Gets the ordinal of a named column, and suppresses that column
// from being listed in the Comments field.
Func <string, int> GetField = (string name) => {
Int32 ordinal = reader.GetOrdinal(name);
usedValues[ordinal] = reader[ordinal].ToString();
return(ordinal);
};
var fullName = reader.GetNullableString(GetField("Name"));
if (fullName == null)
{
continue; // Bad data; ignore.
}
progress.Caption = "Reading payments for " + fullName;
int comma = fullName.IndexOf(',');
if (comma < 0 || fullName.EndsWith(", LLC"))
{
person.LastName = person.FullName = fullName;
}
else
{
genderizer.SetFirstName(fullName.Substring(comma + 1).Trim(), person);
person.LastName = fullName.Remove(comma).Trim();
person.FullName = (person.HisName ?? person.HerName) + " " + person.LastName;
}
person.FullName = reader.GetNullableString(GetField("Ship To Address 1")) ?? person.FullName;
SetAddress(person,
reader.GetNullableString(GetField("Name Address")),
reader.GetNullableString(GetField("Name Street1")),
reader.GetNullableString(GetField("Name Street2")),
ref company
);
// If these values exist discretely in other columns (Ship To),
// don't include them in Comments.
usedValues.Add(-1, person.City);
usedValues.Add(-2, person.State);
usedValues.Add(-3, person.Zip);
// Only add the person to the table if we actually have a payment
// too (as opposed to the second boundary row).
if (person.Table == null)
{
AppFramework.Table <StagedPerson>().Rows.Add(person);
// Do the CPU-intensive part on separate threads so it can utilize all cores.
// But only set the result on the UI thread to avoid threading bugs, both for
// change events in the grid (after the caller re-enables them) and since the
// child collection is not thread-safe.
async void SetPerson(StagedPerson thisPerson)
{
Person inferredTarget = await Task.Run(() => Matcher.FindBestMatch(thisPerson));
if (inferredTarget != null)
{
uiThread.Post(_ => thisPerson.Person = inferredTarget, null);
}
}
SetPerson(person);
}
// TODO: Warn on bad zip
GetField("Date"); // Exclude Date from Comments, even if we don't fetch it below.
StagedPayment payment = new StagedPayment {
Date = reader.GetNullableDateTime(GetField("Date of Check")) ?? reader.GetDateTime(GetField("Date")),
Method = reader.GetNullableString(GetField("Pay Meth")) ?? "Donation",
Amount = (decimal)reader.GetDouble(GetField("Amount")),
CheckNumber = reader.GetNullableString(GetField("Check #")),
Account = Names.DefaultAccount,
ExternalId = reader.GetNullableString(GetField("Num")) ?? Guid.NewGuid().ToString(),
StagedPerson = person,
Company = company,
Comments = Enumerable
.Range(0, reader.FieldCount)
.Where(i => !usedValues.ContainsKey(i) && !reader.IsDBNull(i) && !usedValues.ContainsValue(reader[i].ToString()))
.Select(i => reader.GetName(i) + ": " + reader[i])
.Join(Environment.NewLine)
};
payment.Method = methodMap.GetOrNull(payment.Method) ?? payment.Method;
AppFramework.Table <StagedPayment>().Rows.Add(payment);
}
}
}
private void clearStaging_ItemClick(object sender, ItemClickEventArgs e)
{
// TODO: Use SQL delete statement for efficiency?
AppFramework.Table <StagedPerson>().Rows.Clear();
}