public List<Duplicate> PossibleDuplicates2()
{
using (var ctx = new CMSDataContext(Util.ConnectionString))
{
ctx.SetNoLock();
string street = GetStreet(ctx) ?? "--";
var nick = NickName ?? "--";
var maid = MaidenName ?? "--";
var em = EmailAddress ?? "--";
if (!em.HasValue())
em = "--";
var bd = BirthDay ?? -1;
var bm = BirthMonth ?? -1;
var byr = BirthYear ?? -1;
var q = from p in ctx.People
where p.PeopleId != PeopleId
let firstmatch = p.FirstName == FirstName || (p.NickName ?? "") == FirstName || (p.MiddleName ?? "") == FirstName
|| p.FirstName == nick || (p.NickName ?? "") == nick || (p.MiddleName ?? "") == nick
let lastmatch = p.LastName == LastName || (p.MaidenName ?? "") == LastName
|| (p.MaidenName ?? "") == maid || p.LastName == maid
let nobday = (p.BirthMonth == null && p.BirthYear == null && p.BirthDay == null)
|| (BirthMonth == null && BirthYear == null && BirthDay == null)
let bdmatch = (p.BirthDay ?? -2) == bd && (p.BirthMonth ?? -2) == bm && (p.BirthYear ?? -2) == byr
let bdmatchpart = (p.BirthDay ?? -2) == bd && (p.BirthMonth ?? -2) == bm
let emailmatch = p.EmailAddress != null && p.EmailAddress == em
let addrmatch = (p.AddressLineOne ?? "").Contains(street) || (p.Family.AddressLineOne ?? "").Contains(street)
let s1 = firstmatch && bdmatchpart
let s2 = firstmatch && bdmatch
let s3 = firstmatch && lastmatch && nobday
let s4 = firstmatch && addrmatch
let s5 = firstmatch && emailmatch
let s6 = lastmatch && bdmatch
where s1 || s2 || s3 || s4 || s5 || s6
select new Duplicate
{
s1 = s1,
s2 = s2,
s3 = s3,
s4 = s4,
s5 = s5,
s6 = s6,
PeopleId = p.PeopleId,
First = p.FirstName,
Last = p.LastName,
Nick = p.NickName,
Middle = p.MiddleName,
BMon = p.BirthMonth,
BDay = p.BirthDay,
BYear = p.BirthYear,
Email = p.EmailAddress,
FamAddr = p.Family.AddressLineOne,
PerAddr = p.AddressLineOne,
Member = p.MemberStatus.Description
};
try
{
var list = q.ToList();
var t = new Duplicate
{
s0 = true,
PeopleId = PeopleId,
First = FirstName,
Last = LastName,
Nick = NickName,
Middle = MiddleName,
BMon = BirthMonth,
BDay = BirthDay,
BYear = BirthYear,
Email = EmailAddress,
FamAddr = Family.AddressLineOne,
PerAddr = AddressLineOne,
Member = MemberStatus.Description
};
list.Insert(0, t);
return list;
}
catch (Exception)
{
throw;
}
}
}
public static Person FindPerson(string first, string last, DateTime? DOB, string email, string phone, out int count)
{
count = 0;
if (!first.HasValue() || !last.HasValue())
return null;
first = first.Trim();
last = last.Trim();
var fone = Util.GetDigits(phone);
var ctx = new CMSDataContext(Util.ConnectionString);
ctx.SetNoLock();
var q = from p in ctx.People
where (p.FirstName == first || p.NickName == first || p.MiddleName == first)
where (p.LastName == last || p.MaidenName == last)
select p;
var list = q.ToList();
count = list.Count;
if (count == 0) // not going to find anything
{
ctx.Dispose();
return null;
}
if (DOB.HasValue && DOB > DateTime.MinValue)
{
var dt = DOB.Value;
if (dt > Util.Now)
dt = dt.AddYears(-100);
var q2 = from p in q
where p.BirthDay == dt.Day && p.BirthMonth == dt.Month && p.BirthYear == dt.Year
select p;
count = q2.Count();
if (count == 1) // use only birthday if there and unique
return PersonFound(ctx, q2);
}
if (email.HasValue())
{
var q2 = from p in q
where p.EmailAddress == email
select p;
count = q2.Count();
if (count == 1)
return PersonFound(ctx, q2);
}
if (phone.HasValue())
{
var q2 = from p in q
where p.CellPhone.Contains(fone) || p.Family.HomePhone.Contains(fone)
select p;
count = q2.Count();
if (count == 1)
return PersonFound(ctx, q2);
}
return null;
}
public List<Duplicate> PossibleDuplicates()
{
var fone = Util.GetDigits(Util.PickFirst(CellPhone, HomePhone));
using (var ctx = new CMSDataContext(Util.ConnectionString))
{
ctx.SetNoLock();
string street = GetStreet(ctx) ?? "--";
var nick = NickName ?? "--";
var maid = MaidenName ?? "--";
var em = EmailAddress ?? "--";
if (!em.HasValue())
em = "--";
var bd = BirthDay ?? -1;
var bm = BirthMonth ?? -1;
var byr = BirthYear ?? -1;
var q = from p in ctx.People
let firstmatch = p.FirstName == FirstName || (p.NickName ?? "") == FirstName || (p.MiddleName ?? "") == FirstName
|| p.FirstName == nick || (p.NickName ?? "") == nick || (p.MiddleName ?? "") == nick
let lastmatch = p.LastName == LastName || (p.MaidenName ?? "") == LastName
|| (p.MaidenName ?? "") == maid || p.LastName == maid
let nobday = (p.BirthMonth == null && p.BirthYear == null && p.BirthDay == null)
|| (BirthMonth == null && BirthYear == null && BirthDay == null)
let bdmatch = (p.BirthDay ?? -2) == bd && (p.BirthMonth ?? -2) == bm && (p.BirthYear ?? -2) == byr
let bdmatchpart = (p.BirthDay ?? -2) == bd && (p.BirthMonth ?? -2) == bm
let emailmatch = p.EmailAddress != null && p.EmailAddress == em
let addrmatch = (p.AddressLineOne ?? "").Contains(street) || (p.Family.AddressLineOne ?? "").Contains(street)
let phonematch = (p.CellPhoneLU == CellPhoneLU
|| p.CellPhoneLU == Family.HomePhoneLU
|| p.CellPhone == WorkPhoneLU
|| p.Family.HomePhoneLU == CellPhoneLU
|| p.Family.HomePhoneLU == Family.HomePhoneLU
|| p.Family.HomePhoneLU == WorkPhoneLU
|| p.WorkPhoneLU == CellPhoneLU
|| p.WorkPhoneLU == Family.HomePhoneLU
|| p.WorkPhoneLU == WorkPhoneLU)
let samefamily = p.FamilyId == FamilyId && p.PeopleId != PeopleId
let nmatches = samefamily ? 0 :
(firstmatch ? 1 : 0)
+ (bdmatch ? 1 : 0)
+ (emailmatch ? 1 : 0)
+ (phonematch ? 1 : 0)
+ (addrmatch ? 1 : 0)
where (lastmatch && nmatches >= 3)
|| ((firstmatch && lastmatch && bdmatchpart))
where p.PeopleId != PeopleId
select new Duplicate
{
PeopleId = p.PeopleId,
First = p.FirstName,
Last = p.LastName,
Nick = p.NickName,
Middle = p.MiddleName,
BMon = p.BirthMonth,
BDay = p.BirthDay,
BYear = p.BirthYear,
Email = p.EmailAddress,
FamAddr = p.Family.AddressLineOne,
PerAddr = p.AddressLineOne,
Member = p.MemberStatus.Description
};
var list = q.ToList();
return list;
}
}
