private static void UpdatePeople(DbConnection cn, CMSDataContext Db, int userPeopleId)
{
var cv = new CodeValueModel();
var pcmd = cn.CreateCommand();
pcmd.CommandText = "select * from [Sheet1$]";
var rd = pcmd.ExecuteReader();
while (rd.Read())
{
var i = Util.GetAs(rd, typeof(UpdatePeopleItem)) as UpdatePeopleItem;
var p = Db.LoadPersonById(i.PeopleId);
//var psb = new StringBuilder();
var psb = new List <ChangeDetail>();
p.UpdateValue(psb, "TitleCode", i.Title);
p.UpdateValue(psb, "FirstName", i.First);
p.UpdateValue(psb, "NickName", i.GoesBy);
p.UpdateValue(psb, "LastName", i.Last);
p.UpdateValue(psb, "SuffixCode", i.Suffix);
p.UpdateValue(psb, "EmailAddress", i.Email1);
p.UpdateValue(psb, "EmailAddress2", i.Email2);
p.UpdateValue(psb, "DOB", i.BirthDate.FormatDate());
p.UpdateValue(psb, "WeddingDate", i.Anniversary);
p.UpdateValue(psb, "JoinDate", i.Joined);
p.UpdateValue(psb, "CellPhone", i.Cell.GetDigits());
p.UpdateValue(psb, "WorkPhone", i.Work.GetDigits());
p.UpdateValue(psb, "AltName", i.AltName);
p.UpdateValue(psb, "SchoolOther", i.School);
p.UpdateValue(psb, "OccupationOther", i.Occupation);
p.UpdateValue(psb, "EmployerOther", i.Employer);
p.UpdateValue(psb, "Grade", i.Grade);
p.UpdateValue(psb, "DeceasedDate", i.Deceased);
p.UpdateValue(psb, "MemberStatusId", CviOrNull(cv.MemberStatusCodes().SingleOrDefault(c => c.Value == i.Member)) ?? 20);
p.UpdateValue(psb, "GenderId", CviOrNull(cv.GenderCodes().SingleOrDefault(c => c.Value == i.Gender)) ?? 0);
p.UpdateValue(psb, "MaritalStatusId", CviOrNull(cv.MaritalStatusCodes().SingleOrDefault(c => c.Value == i.Marital)) ?? 0);
p.UpdateValue(psb, "PositionInFamilyId", CviOrNull(cv.FamilyPositionCodes().SingleOrDefault(c => c.Value == i.FamilyPos)) ?? 0);
p.UpdateValue(psb, "CampusId", CviOrNull(cv.AllCampuses().SingleOrDefault(c => c.Value == i.Campus)));
p.LogChanges(Db, psb);
Db.SubmitChanges();
}
}
public IEnumerable<TitleItems> TitleItems()
{
var Model = new CodeValueModel();
return new List<TitleItems>
{
new TitleItems { title = "Member Status Codes", items = Model.MemberStatusCodes() },
new TitleItems { title = "Drop Type Codes", items = Model.DropTypes() },
new TitleItems { title = "Baptism Status Codes", items = Model.BaptismStatuses() },
new TitleItems { title = "Baptism Type Codes", items = Model.BaptismTypes() },
new TitleItems { title = "Campus Codes", items = Model.AllCampuses() },
new TitleItems { title = "Marital Status Codes", items = Model.MaritalStatusCodes() },
new TitleItems { title = "Gender Codes", items = Model.GenderCodes() },
new TitleItems { title = "Family Position Codes", items = Model.FamilyPositionCodes() },
new TitleItems { title = "Contribution Statement Options", items = Model.EnvelopeOptions() },
new TitleItems { title = "Envelope Options", items = Model.EnvelopeOptions() },
new TitleItems { title = "Entry Point Codes", items = Model.EntryPoints() },
new TitleItems { title = "New Member Class", items = Model.NewMemberClassStatusCodes() },
new TitleItems { title = "Approval Codes (negative to remove, 0 to remove all)", items = Model.VolunteerCodes() },
};
}
public string ConvertToSearch()
{
var cc = DbUtil.Db.ScratchPadCondition();
cc.Reset();
if (m.memberstatus > 0)
{
cc.AddNewClause(QueryType.MemberStatusId, CompareType.Equal,
IdValue(cv.MemberStatusCodes(), m.memberstatus));
}
if (m.name.HasValue())
{
string First, Last;
Util.NameSplit(m.name, out First, out Last);
if (First.HasValue())
{
var g = cc.AddNewGroupClause();
g.SetComparisonType(CompareType.AnyTrue);
g.AddNewClause(QueryType.LastName, CompareType.StartsWith, Last);
g.AddNewClause(QueryType.MaidenName, CompareType.StartsWith, Last);
g = cc.AddNewGroupClause();
g.SetComparisonType(CompareType.AnyTrue);
g.AddNewClause(QueryType.FirstName, CompareType.StartsWith, First);
g.AddNewClause(QueryType.NickName, CompareType.StartsWith, First);
g.AddNewClause(QueryType.MiddleName, CompareType.StartsWith, First);
}
else
{
if (Last.AllDigits())
{
cc.AddNewClause(QueryType.PeopleId, CompareType.Equal, Last.ToInt());
}
else
{
var g = cc.AddNewGroupClause();
g.SetComparisonType(CompareType.AnyTrue);
g.AddNewClause(QueryType.LastName, CompareType.StartsWith, m.name);
g.AddNewClause(QueryType.MaidenName, CompareType.StartsWith, m.name);
// g.AddNewClause(QueryType.FirstName, CompareType.StartsWith, m.name);
// g.AddNewClause(QueryType.NickName, CompareType.StartsWith, m.name);
// g.AddNewClause(QueryType.MiddleName, CompareType.StartsWith, m.name);
}
}
}
if (m.address.IsNotNull())
{
if (AddrRegex.IsMatch(m.address))
{
var match = AddrRegex.Match(m.address);
m.address = match.Groups["addr"].Value;
}
m.address = m.address.Trim();
if (m.address.HasValue())
{
var g = cc.AddNewGroupClause();
g.SetComparisonType(CompareType.AnyTrue);
g.AddNewClause(QueryType.PrimaryAddress, CompareType.Contains, m.address);
g.AddNewClause(QueryType.PrimaryAddress2, CompareType.Contains, m.address);
g.AddNewClause(QueryType.PrimaryCity, CompareType.Contains, m.address);
g.AddNewClause(QueryType.PrimaryZip, CompareType.Contains, m.address);
}
}
if (m.communication.IsNotNull())
{
m.communication = m.communication.Trim();
if (m.communication.HasValue())
{
var g = cc.AddNewGroupClause();
g.SetComparisonType(CompareType.AnyTrue);
g.AddNewClause(QueryType.CellPhone, CompareType.Contains, m.communication);
g.AddNewClause(QueryType.EmailAddress, CompareType.Contains, m.communication);
g.AddNewClause(QueryType.EmailAddress2, CompareType.Contains, m.communication);
g.AddNewClause(QueryType.HomePhone, CompareType.Contains, m.communication);
g.AddNewClause(QueryType.WorkPhone, CompareType.Contains, m.communication);
}
}
if (m.birthdate.HasValue() && m.birthdate.NotEqual("na"))
{
DateTime dt;
if (DateTime.TryParse(m.birthdate, out dt))
{
if (Regex.IsMatch(m.birthdate, @"\d+/\d+/\d+"))
{
cc.AddNewClause(QueryType.Birthday, CompareType.Equal, m.birthdate);
}
else
{
cc.AddNewClause(QueryType.Birthday, CompareType.Equal, m.birthdate);
}
}
else
{
int n;
if (int.TryParse(m.birthdate, out n))
{
if (n >= 1 && n <= 12)
{
cc.AddNewClause(QueryType.Birthday, CompareType.Equal, m.birthdate);
}
else
{
cc.AddNewClause(QueryType.Birthday, CompareType.Equal, m.birthdate);
}
}
}
}
if (m.campus > 0)
{
cc.AddNewClause(QueryType.CampusId, CompareType.Equal,
IdValue(cv.AllCampuses(), m.campus));
}
else if (m.campus == -1)
{
cc.AddNewClause(QueryType.CampusId, CompareType.IsNull,
IdValue(cv.AllCampuses(), m.campus));
}
if (m.gender != 99)
{
cc.AddNewClause(QueryType.GenderId, CompareType.Equal,
IdValue(cv.GenderCodes(), m.gender));
}
if (m.marital != 99)
{
cc.AddNewClause(QueryType.MaritalStatusId, CompareType.Equal,
IdValue(cv.MaritalStatusCodes(), m.marital));
}
if (m.statusflags != null)
{
foreach (var f in m.statusflags)
{
cc.AddNewClause(QueryType.StatusFlag, CompareType.Equal, f);
}
}
cc.AddNewClause(QueryType.IncludeDeceased, CompareType.Equal, "1,True");
cc.Save(DbUtil.Db);
return("/Query/" + cc.Id);
}
public SelectList MaritalStatusList()
{
var cv = new CodeValueModel();
return(new SelectList(cv.MaritalStatusCodes(), "Id", "Value"));
}
public IEnumerable <TitleItems> FetchTitleItems()
{
var Model = new CodeValueModel();
return(new List <TitleItems>
{
new TitleItems {
title = "Approval Codes", Instructions = "(negative to remove, 0 to remove all)", items = Model.VolunteerCodes()
},
new TitleItems {
title = "Baptism Status Codes", items = Model.BaptismStatusList()
},
new TitleItems {
title = "Baptism Type Codes", items = Model.BaptismTypeList()
},
new TitleItems {
title = "Bad Address Flag", items = BadAddressFlag(), UseCode = true
},
new TitleItems {
title = "Campus Codes", items = Model.AllCampuses()
},
new TitleItems {
title = "Contribution Statement Options", items = Model.EnvelopeOptionList()
},
new TitleItems {
title = "Decision Type Codes", items = Model.DecisionTypeList()
},
new TitleItems {
title = "Do Not Mail", items = DoNotMail(), UseCode = true
},
new TitleItems {
title = "Drop Type Codes", items = Model.DropTypeList()
},
new TitleItems {
title = "Envelope Options", items = Model.EnvelopeOptionList()
},
new TitleItems {
title = "Entry Point Codes", items = Model.EntryPoints()
},
new TitleItems {
title = "Family Position Codes", items = Model.FamilyPositionCodes()
},
new TitleItems {
title = "Gender Codes", items = Model.GenderCodes()
},
new TitleItems {
title = "Grades", items = Grades(), UseCode = true
},
new TitleItems {
title = "Join Type Codes", items = Model.JoinTypeList()
},
new TitleItems {
title = "Marital Status Codes", items = Model.MaritalStatusCodes()
},
new TitleItems {
title = "Member Status Codes", items = Model.MemberStatusCodes()
},
new TitleItems {
title = "New Member Class", items = Model.NewMemberClassStatusList()
},
new TitleItems {
title = "Receive SMS", items = ReceiveSMS(), UseCode = true
},
});
}
public static IEnumerable <SelectListItem> MaritalStatuses()
{
var cv = new CodeValueModel();
return(CodeValueModel.ConvertToSelect(cv.MaritalStatusCodes(), "Id"));
}
public static IEnumerable <CodeValueItem> Fetch(string field, ref bool useCode)
{
IEnumerable <CodeValueItem> m = null;
var lookups = new CodeValueModel();
switch (field)
{
case "Approval Codes":
m = lookups.VolunteerCodes();
break;
case "Baptism Status":
m = lookups.BaptismStatusList();
break;
case "Baptism Type":
m = lookups.BaptismTypeList();
break;
case "Bad Address Flag":
m = UpdateFieldsModel.BadAddressFlag();
useCode = true;
break;
case "Campus":
m = lookups.AllCampuses();
break;
case "Statement Options":
m = lookups.EnvelopeOptionList();
break;
case "Electronic Statement":
m = UpdateFieldsModel.ElectronicStatement();
useCode = true;
break;
case "Decision Type":
m = lookups.DecisionTypeList();
break;
case "Do Not Call":
m = UpdateFieldsModel.DoNotCall();
useCode = true;
break;
case "Do Not Mail":
m = UpdateFieldsModel.DoNotMail();
useCode = true;
break;
case "Drop Type":
m = lookups.DropTypeList();
break;
case "Envelope Options":
m = lookups.EnvelopeOptionList();
break;
case "Entry Point":
m = lookups.EntryPoints();
useCode = false;
break;
case "Family Position":
m = lookups.FamilyPositionCodes();
break;
case "Gender":
m = lookups.GenderCodes();
break;
case "Grade":
m = UpdateFieldsModel.Grades();
useCode = true;
break;
case "Join Type":
m = lookups.JoinTypeList();
break;
case "Marital Status":
m = lookups.MaritalStatusCodes();
break;
case "Member Status":
m = lookups.MemberStatusCodes();
break;
case "New Member Class":
m = lookups.NewMemberClassStatusList();
break;
case "ReceiveSMS":
m = UpdateFieldsModel.ReceiveSMS();
useCode = true;
break;
}
return(m);
}