public int FetchOrCreateCampusId(string name)
{
var c = Campus.SingleOrDefault(pp => pp.Description == name);
if (c == null)
{
var max = 10;
if (Campus.Any())
{
max = Campus.Max(mm => mm.Id) + 10;
}
c = new Campu()
{
Id = max, Description = name, Code = name.Truncate(20)
};
Campus.InsertOnSubmit(c);
SubmitChanges();
}
return(c.Id);
}
public bool DoUpload(string text, bool testing = false)
{
var rt = Db2.UploadPeopleRuns.OrderByDescending(mm => mm.Id).First();
var csv = new CsvReader(new StringReader(text), false, '\t');
var list = csv.ToList();
var list0 = list.First().Select(kk => kk).ToList();
names = list0.ToDictionary(i => i.TrimEnd(),
i => list0.FindIndex(s => s == i), StringComparer.OrdinalIgnoreCase);
if (names.ContainsKey("campus"))
{
var campuslist = (from li in list.Skip(1)
where li.Length == names.Count
group li by li[names["campus"]]
into campus
where campus.Key.HasValue()
select campus.Key).ToList();
var dbc = from c in campuslist
join cp in Db.Campus on c equals cp.Description into j
from cp in j.DefaultIfEmpty()
select new {cp, c};
var clist = dbc.ToList();
if (clist.Count > 0)
{
var maxcampusid = 0;
if (Db.Campus.Any())
maxcampusid = Db.Campus.Max(c => c.Id);
foreach (var i in clist)
if (i.cp == null)
{
var cp = new Campu {Description = i.c, Id = ++maxcampusid};
if (!testing)
Db.Campus.InsertOnSubmit(cp);
}
}
}
var now = DateTime.Now;
if (!testing)
Db.SubmitChanges();
Campuses = Db.Campus.ToDictionary(cp => cp.Description, cp => cp.Id);
var q = (from li in list.Skip(1)
where li.Length == names.Count
group li by li[names["familyid"]]
into fam
select fam).ToList();
rt.Count = q.Sum(ff => ff.Count());
Db2.SubmitChanges();
var standardnames = new List<string>
{
"familyid", "title", "first", "last", "goesby", "altname", "gender", "marital", "maidenName",
"address", "address2", "city", "state", "zip", "position", "birthday", "cellphone", "homephone",
"workphone", "email", "email2", "suffix", "middle", "joindate", "dropdate", "baptismdate", "weddingdate",
"memberstatus", "employer", "occupation"
};
var orgs = new Dictionary<string, int>();
var membertypes = new Dictionary<string, int>();
foreach (var fam in q)
{
Family f = null;
FindPerson3 pid;
Person p = null;
foreach (var a in fam)
{
var potentialdup = false;
var first = a[names["first"]];
var last = a[names["last"]];
DateTime dt;
DateTime? dob = null;
if (names.ContainsKey("birthday"))
if (DateTime.TryParse(a[names["birthday"]], out dt))
{
dob = dt;
if (dob.Value < SqlDateTime.MinValue)
dob = null;
}
string email = null;
string cell = null;
string homephone = null;
if (names.ContainsKey("email"))
email = a[names["email"]].Trim();
if (names.ContainsKey("cellphone"))
cell = a[names["cellphone"]].GetDigits();
if (names.ContainsKey("homephone"))
homephone = a[names["homephone"]].GetDigits();
pid = Db.FindPerson3(first, last, dob, email, cell, homephone, null).FirstOrDefault();
if (noupdate && pid != null)
{
if (!testing)
{
var pd = Db.LoadPersonById(pid.PeopleId.Value);
pd.AddEditExtraBool("FoundDup", true);
}
potentialdup = true;
pid = null;
}
if (pid != null) // found
{
p = Db.LoadPersonById(pid.PeopleId.Value);
psb = new List<ChangeDetail>();
fsb = new List<ChangeDetail>();
UpdateField(p, a, "TitleCode", "title");
UpdateField(p, a, "FirstName", "first");
UpdateField(p, a, "NickName", "goesby");
UpdateField(p, a, "LastName", "last");
UpdateField(p, a, "EmailAddress", "email");
UpdateField(p, a, "EmailAddress2", "email2");
UpdateField(p, a, "DOB", "birthday");
UpdateField(p, a, "AltName", "altname");
UpdateField(p, a, "SuffixCode", "suffix");
UpdateField(p, a, "MiddleName", "middle");
UpdateField(p, a, "CellPhone", "cellphone", GetDigits(a, "cellphone"));
UpdateField(p, a, "WorkPhone", "workphone", GetDigits(a, "workphone"));
UpdateField(p, a, "GenderId", "gender", Gender(a));
UpdateField(p, a, "MaritalStatusId", "marital", Marital(a));
UpdateField(p, a, "PositionInFamilyId", "position", Position(a));
if (!testing)
UpdateField(p, a, "CampusId", "campus", Campus(a));
UpdateField(p.Family, a, "AddressLineOne", "address");
UpdateField(p.Family, a, "AddressLineTwo", "address2");
UpdateField(p.Family, a, "CityName", "city");
UpdateField(p.Family, a, "StateCode", "state");
UpdateField(p.Family, a, "ZipCode", "zip");
if (!testing)
{
p.LogChanges(Db, psb, PeopleId);
p.Family.LogChanges(Db, fsb, p.PeopleId, PeopleId);
Db.SubmitChanges();
p.AddEditExtraBool("InsertPeopleUpdated", true);
}
}
else // new person
{
if (f == null || !a[names["familyid"]].HasValue())
{
f = new Family();
SetField(f, a, "AddressLineOne", "address");
SetField(f, a, "AddressLineTwo", "address2");
SetField(f, a, "CityName", "city");
SetField(f, a, "StateCode", "state");
SetField(f, a, "ZipCode", "zip");
SetField(f, a, "HomePhone", "homephone", GetDigits(a, "homephone"));
Db.Families.InsertOnSubmit(f);
if (!testing)
Db.SubmitChanges();
}
string goesby = null;
if (names.ContainsKey("goesby"))
goesby = a[names["goesby"]];
p = Person.Add(Db, false, f, 10, null,
a[names["first"]],
goesby,
a[names["last"]],
dob.FormatDate(),
0, 0, 0, null, testing);
p.FixTitle();
SetField(p, a, "AltName", "altname");
SetField(p, a, "SuffixCode", "suffix");
SetField(p, a, "MiddleName", "middle");
SetField(p, a, "MaidenName", "maidenname");
SetField(p, a, "EmployerOther", "employer");
SetField(p, a, "OccupationOther", "occupation");
SetField(p, a, "CellPhone", "cellphone", GetDigits(a, "cellphone"));
SetField(p, a, "WorkPhone", "workphone", GetDigits(a, "workphone"));
SetField(p, a, "EmailAddress", "email");
SetField(p, a, "EmailAddress2", "email2");
SetField(p, a, "GenderId", "gender", Gender(a));
SetField(p, a, "MaritalStatusId", "marital", Marital(a));
SetField(p, a, "WeddingDate", "weddingdate", GetDate(p, a, "weddingdate"));
SetField(p, a, "JoinDate", "joindate", GetDate(p, a, "joindate"));
SetField(p, a, "DropDate", "dropdate", GetDate(p, a, "dropdate"));
SetField(p, a, "BaptismDate", "baptismdate", GetDate(p, a, "baptismdate"));
SetField(p, a, "PositionInFamilyId", "position", Position(a));
SetField(p, a, "TitleCode", "title", Title(a));
if (!testing)
SetField(p, a, "CampusId", "campus", Campus(a));
if (names.ContainsKey("memberstatus"))
{
var ms = a[names["memberstatus"]];
var qms = from mm in Db.MemberStatuses
where mm.Description == ms
select mm;
var m = qms.SingleOrDefault();
if (m == null)
{
var nx = Db.MemberStatuses.Max(mm => mm.Id) + 1;
m = new MemberStatus {Id = nx, Description = ms, Code = nx.ToString()};
Db.MemberStatuses.InsertOnSubmit(m);
}
p.MemberStatusId = m.Id;
}
}
var nq = from name in names.Keys
where !standardnames.Contains(name, StringComparer.OrdinalIgnoreCase)
select name;
foreach (var name in nq)
{
var b = name.Split('.');
if (name.EndsWith(".txt"))
p.AddEditExtraText(b[0], a[names[name]].Trim());
else if (name.EndsWith(".org"))
{
if (testing)
continue;
var d = a[names[name]].Trim().Trim();
if (!d.HasValue())
continue;
var oid = 0;
if (orgs.ContainsKey(b[0]))
oid = orgs[b[0]];
else
{
var org = Db.Organizations.First();
var o = org.CloneOrg(Db);
o.OrganizationName = b[0].SplitUpperCaseToString();
oid = o.OrganizationId;
orgs.Add(b[0], oid);
}
var mtid = 0;
if (membertypes.ContainsKey(d))
mtid = membertypes[d];
else
{
var mt = Organization.FetchOrCreateMemberType(Db, d);
mtid = mt.Id;
membertypes.Add(d, mtid);
}
OrganizationMember.InsertOrgMembers(Db, oid, p.PeopleId, mtid, DateTime.Today, null, false);
}
else if (name.EndsWith(".dt"))
{
var d = a[names[name]].Trim().ToDate();
if (d.HasValue)
p.AddEditExtraDate(b[0], d.Value);
}
else if (name.EndsWith(".int"))
p.AddEditExtraInt(b[0], a[names[name]].Trim().ToInt());
else if (name.EndsWith(".bit"))
{
var v = a[names[name]];
if (v.HasValue())
p.AddEditExtraBool(b[0], v.ToInt() == 1);
}
else
p.AddEditExtraCode(name, a[names[name]].Trim());
}
rt.Processed++;
Db2.SubmitChanges();
if (!testing)
{
p.AddEditExtraBool("InsertPeopleAdded", true);
if (potentialdup)
p.AddEditExtraBool("FoundDup", true);
Db.SubmitChanges();
}
}
if (!testing)
Db.SubmitChanges();
}
rt.Completed = DateTime.Now;
Db2.SubmitChanges();
return true;
}
public ActionResult UpdatePeople0(string text)
{
if (Request.HttpMethod.ToUpper() == "GET")
{
ViewData["text"] = "";
return View();
}
var list = text.Split('\n').Select(li => li.Split('\t'));
var list0 = list.First().ToList();
var names = list0.ToDictionary(i => i.TrimEnd(),
i => list0.FindIndex(s => s == i));
if (names.ContainsKey("Campus"))
{
var campuslist = (from li in list.Skip(1)
where li.Length == names.Count
group li by li[names["Campus"]] into campus
select campus.Key).ToList();
var dbc = from c in campuslist
join cp in DbUtil.Db.Campus on c equals cp.Description into j
from cp in j.DefaultIfEmpty()
select new { cp, c };
var clist = dbc.ToList();
var maxcampusid = DbUtil.Db.Campus.Max(c => c.Id);
foreach (var i in clist)
if (i.cp == null)
{
var cp = new Campu { Description = i.c, Id = ++maxcampusid };
DbUtil.Db.Campus.InsertOnSubmit(cp);
}
}
DbUtil.Db.SubmitChanges();
var campuses = DbUtil.Db.Campus.ToDictionary(cp => cp.Description, cp => cp.Id);
var q = from li in list.Skip(1)
where li.Length == names.Count
select li;
foreach (var li in q)
{
DateTime? dob = null;
if (names.ContainsKey("Birthday"))
dob = li[names["Birthday"]].ToDate();
string email = "", cell = "", homephone = "";
if (names.ContainsKey("CellPhone"))
cell = li[names["CellPhone"]].GetDigits();
if (names.ContainsKey("Email"))
email = li[names["Email"]].Trim();
if (names.ContainsKey("HomePhone"))
homephone = li[names["HomePhone"]].GetDigits();
var first = li[names["First"]].Trim();
var last = li[names["Last"]].Trim();
var pid = DbUtil.Db.FindPerson3(first, last, dob, email, cell, homephone, null).FirstOrDefault();
if (pid == null)
continue;
var p = DbUtil.Db.LoadPersonById(pid.PeopleId.Value);
foreach (var name in names)
switch (name.Key)
{
case "Title":
p.TitleCode = li[name.Value];
break;
case "Campus":
p.CampusId = campuses[li[name.Value]];
break;
case "Gender":
switch (li[name.Value])
{
case "Male":
p.GenderId = 1;
break;
case "Female":
p.GenderId = 2;
break;
}
break;
case "Married":
switch (li[name.Value])
{
case "Married":
p.MaritalStatusId = 20;
break;
case "Single":
p.MaritalStatusId = 10;
break;
default:
p.MaritalStatusId = 0;
break;
}
break;
case "Address":
p.AddressLineOne = li[name.Value];
break;
case "Address2":
p.AddressLineTwo = li[name.Value];
break;
case "City":
p.CityName = li[name.Value];
break;
case "State":
p.StateCode = li[name.Value];
break;
case "Zip":
p.ZipCode = li[name.Value];
break;
case "Position":
switch (li[name.Value])
{
case "Primary":
p.PositionInFamilyId = 10;
break;
case "Secondary":
p.PositionInFamilyId = 20;
break;
case "Child":
p.PositionInFamilyId = 30;
break;
}
break;
case "Birthday":
p.DOB = dob.FormatDate();
break;
case "CellPhone":
p.CellPhone = cell;
break;
case "HomePhone":
p.HomePhone = homephone;
break;
case "Email":
p.EmailAddress = email;
break;
}
DbUtil.Db.SubmitChanges();
}
return Redirect("/");
}
public static Campu FetchOrCreateCampus(CMSDataContext Db, string campus)
{
if (!Util.HasValue(campus))
return null;
var cam = Db.Campus.SingleOrDefault(pp => pp.Description == campus);
if (cam == null)
{
int max = 10;
if (Db.Campus.Any())
max = Db.Campus.Max(mm => mm.Id) + 10;
cam = new Campu() { Id = max, Description = campus, Code = campus.Truncate(20) };
Db.Campus.InsertOnSubmit(cam);
Db.SubmitChanges();
}
else if (!Util.HasValue(cam.Code))
{
cam.Code = campus.Truncate(20);
Db.SubmitChanges();
}
return cam;
}
public bool DoUpload(string text)
{
var rt = Db2.UploadPeopleRuns.OrderByDescending(mm => mm.Id).First();
var sep = text.First(vv => new char[] {'\t', ','}.Contains(vv));
var csv = new CsvReader(new StringReader(text), false, sep);
csv.SupportsMultiline = true;
var list = csv.ToList();
var list0 = list.First().Select(kk => kk).ToList();
names = list0.ToDictionary(i => i.TrimEnd(),
i => list0.FindIndex(s => s == i), StringComparer.OrdinalIgnoreCase);
extravaluenames = (from name in names.Keys
where !standardnames.Contains(name, StringComparer.OrdinalIgnoreCase)
where !standardrecregnames.Contains(name)
select name).ToList();
recregnames = (from name in names.Keys
where standardrecregnames.Contains(name)
select name).ToList();
var db = DbUtil.Create(host);
if (names.ContainsKey("campus"))
{
var campuslist = (from li in list.Skip(1)
where li.Length == names.Count
group li by li[names["campus"]]
into campus
where campus.Key.HasValue()
select campus.Key).ToList();
var dbc = from c in campuslist
join cp in db.Campus on c equals cp.Description into j
from cp in j.DefaultIfEmpty()
select new {cp, c};
var clist = dbc.ToList();
if (clist.Count > 0)
{
var maxcampusid = 0;
if (db.Campus.Any())
maxcampusid = db.Campus.Max(c => c.Id);
foreach (var i in clist)
if (i.cp == null)
{
var cp = new Campu {Description = i.c, Id = ++maxcampusid};
if (!testing)
db.Campus.InsertOnSubmit(cp);
}
}
}
var now = DateTime.Now;
if (!testing)
db.SubmitChanges();
Campuses = db.Campus.ToDictionary(cp => cp.Description, cp => cp.Id);
var q = (from li in list.Skip(1)
where li.Length == names.Count
group li by li[names["familyid"]]
into fam
select fam).ToList();
rt.Count = q.Sum(ff => ff.Count());
Db2.SubmitChanges();
foreach (var fam in q)
{
Family f = null;
var prevpid = 0;
FindPerson3 pid;
Person p = null;
foreach (var a in fam)
{
if (!testing)
{
db.SubmitChanges();
db.Dispose();
db = DbUtil.Create(host);
}
var potentialdup = false;
var first = a[names["first"]];
var last = a[names["last"]];
DateTime dt;
DateTime? dob = null;
if (names.ContainsKey("birthday"))
if (DateTime.TryParse(a[names["birthday"]], out dt))
{
dob = dt;
if (dob.Value < SqlDateTime.MinValue)
dob = null;
}
string email = null;
string cell = null;
string homephone = null;
if (names.ContainsKey("email"))
email = a[names["email"]].Trim();
if (names.ContainsKey("cellphone"))
cell = a[names["cellphone"]].GetDigits();
if (names.ContainsKey("homephone"))
homephone = a[names["homephone"]].GetDigits();
pid = db.FindPerson3(first, last, dob, email, cell, homephone, null).FirstOrDefault();
if (noupdate && pid != null)
{
if (!testing)
{
var pd = db.LoadPersonById(pid.PeopleId.Value);
pd.AddEditExtraBool("FoundDup", true);
}
potentialdup = true;
pid = null;
}
if (pid != null) // found
{
p = db.LoadPersonById(pid.PeopleId.Value);
prevpid = p.PeopleId;
psb = new List<ChangeDetail>();
fsb = new List<ChangeDetail>();
UpdateField(p, a, "TitleCode", "title");
UpdateField(p, a, "FirstName", "first");
UpdateField(p, a, "NickName", "goesby");
UpdateField(p, a, "LastName", "last");
UpdateField(p, a, "EmailAddress", "email");
UpdateField(p, a, "EmailAddress2", "email2");
UpdateField(p, a, "DOB", "birthday");
UpdateField(p, a, "AltName", "altname");
UpdateField(p, a, "SuffixCode", "suffix");
UpdateField(p, a, "MiddleName", "middle");
UpdateField(p, a, "CellPhone", "cellphone", GetDigits(a, "cellphone"));
UpdateField(p, a, "WorkPhone", "workphone", GetDigits(a, "workphone"));
UpdateField(p, a, "GenderId", "gender", Gender(a));
UpdateField(p, a, "MaritalStatusId", "marital", Marital(a));
UpdateField(p, a, "PositionInFamilyId", "position", Position(a));
if (!testing)
UpdateField(p, a, "CampusId", "campus", Campus(a));
UpdateField(p.Family, a, "AddressLineOne", "address");
UpdateField(p.Family, a, "AddressLineTwo", "address2");
UpdateField(p.Family, a, "CityName", "city");
UpdateField(p.Family, a, "StateCode", "state");
UpdateField(p.Family, a, "ZipCode", "zip");
if (names.ContainsKey("memberstatus"))
UpdateMemberStatus(db, p, a);
if (!testing)
{
p.LogChanges(db, psb, PeopleId);
p.Family.LogChanges(db, fsb, p.PeopleId, PeopleId);
db.SubmitChanges();
p.AddEditExtraBool("InsertPeopleUpdated", true);
}
}
else // new person
{
if(!testing)
if (prevpid > 0)
f = db.LoadFamilyByPersonId(prevpid);
if (f == null || !a[names["familyid"]].HasValue())
{
f = new Family();
SetField(f, a, "AddressLineOne", "address");
SetField(f, a, "AddressLineTwo", "address2");
SetField(f, a, "CityName", "city");
SetField(f, a, "StateCode", "state");
SetField(f, a, "ZipCode", "zip");
SetField(f, a, "HomePhone", "homephone", GetDigits(a, "homephone"));
db.Families.InsertOnSubmit(f);
if (!testing)
db.SubmitChanges();
}
string goesby = null;
if (names.ContainsKey("goesby"))
goesby = a[names["goesby"]];
p = Person.Add(db, false, f, 10, null,
a[names["first"]],
goesby,
a[names["last"]],
dob.FormatDate(),
0, 0, 0, null, testing);
prevpid = p.PeopleId;
p.FixTitle();
SetField(p, a, "AltName", "altname");
SetField(p, a, "SuffixCode", "suffix");
SetField(p, a, "MiddleName", "middle");
SetField(p, a, "MaidenName", "maidenname");
SetField(p, a, "EmployerOther", "employer");
SetField(p, a, "OccupationOther", "occupation");
SetField(p, a, "CellPhone", "cellphone", GetDigits(a, "cellphone"));
SetField(p, a, "WorkPhone", "workphone", GetDigits(a, "workphone"));
SetField(p, a, "EmailAddress", "email");
SetField(p, a, "EmailAddress2", "email2");
SetField(p, a, "GenderId", "gender", Gender(a));
SetField(p, a, "MaritalStatusId", "marital", Marital(a));
SetField(p, a, "WeddingDate", "weddingdate", GetDate(p, a, "weddingdate"));
SetField(p, a, "JoinDate", "joindate", GetDate(p, a, "joindate"));
SetField(p, a, "DropDate", "dropdate", GetDate(p, a, "dropdate"));
SetField(p, a, "BaptismDate", "baptismdate", GetDate(p, a, "baptismdate"));
SetField(p, a, "PositionInFamilyId", "position", Position(a));
SetField(p, a, "TitleCode", "title", Title(a));
if (names.ContainsKey("memberstatus"))
SetMemberStatus(db, p, a);
if (!testing)
{
SetField(p, a, "CampusId", "campus", Campus(a));
p.AddEditExtraBool("InsertPeopleAdded", true);
if (potentialdup)
p.AddEditExtraBool("FoundDup", true);
db.SubmitChanges();
}
}
if (names.ContainsKey("createddate"))
SetCreatedDate(p, a);
if (names.ContainsKey("backgroundcheck"))
SetBackgroundCheckDate(db, p, a);
if (recregnames.Any())
SetRecRegs(p, a);
if (extravaluenames.Any())
ProcessExtraValues(db, p, a);
rt.Processed++;
Db2.SubmitChanges();
}
if (!testing)
db.SubmitChanges();
}
rt.Completed = DateTime.Now;
Db2.SubmitChanges();
return true;
}