private static QueryBuilderClause ImportClause(XElement r)
{
var c = new QueryBuilderClause
{
Field = Attribute(r, "Field"),
ClauseOrder = Attribute(r, "ClauseOrder").ToInt(),
Comparison = Attribute(r, "Comparison"),
TextValue = Attribute(r, "TextValue"),
DateValue = AttributeDate(r, "DateValue"),
CodeIdValue = Attribute(r, "CodeIdValue"),
StartDate = AttributeDate(r, "StartDate"),
EndDate = AttributeDate(r, "EndDate"),
Program = Attribute(r, "Program").ToInt(),
Division = Attribute(r, "Division").ToInt(),
Organization = Attribute(r, "Organization").ToInt(),
Days = Attribute(r, "Days").ToInt(),
Quarters = Attribute(r, "Quarters"),
Tags = Attribute(r, "Tags"),
Schedule = Attribute(r, "Schedule").ToInt(),
Age = Attribute(r, "Age").ToInt(),
SavedBy = Util.UserName
};
if (c.Field == "Group")
{
foreach (var rr in r.Elements())
{
c.Clauses.Add(ImportClause(rr));
}
}
return(c);
}
internal Expression Expression(QueryBuilderClause qbc, ParameterExpression parm, CMSDataContext Db)
{
var c = new Condition()
{
Age = qbc.Age,
Campus = qbc.Campus,
Comparison = qbc.Comparison,
DateValue = qbc.DateValue,
CodeIdValue = qbc.CodeIdValue,
Days = qbc.Days,
Division = qbc.Division,
EndDate = qbc.EndDate,
ConditionName = qbc.Field,
Organization = qbc.Organization,
OrgType = qbc.OrgType,
Program = qbc.Program,
Quarters = qbc.Quarters,
Schedule = qbc.Schedule,
StartDate = qbc.StartDate,
Tags = qbc.Tags,
TextValue = qbc.TextValue,
SavedQuery = qbc.SavedQueryIdDesc
};
return c.GetExpression(parm, Db, qbc.SetIncludeDeceased, qbc.SetParentsOf);
}
internal Expression Expression(QueryBuilderClause qbc, ParameterExpression parm, CMSDataContext Db)
{
var c = new Condition()
{
Age = qbc.Age,
Campus = qbc.Campus,
Comparison = qbc.Comparison,
DateValue = qbc.DateValue,
CodeIdValue = qbc.CodeIdValue,
Days = qbc.Days,
Division = qbc.Division,
EndDate = qbc.EndDate,
ConditionName = qbc.Field,
Organization = qbc.Organization,
OrgType = qbc.OrgType,
Program = qbc.Program,
Quarters = qbc.Quarters,
Schedule = qbc.Schedule,
StartDate = qbc.StartDate,
Tags = qbc.Tags,
TextValue = qbc.TextValue,
SavedQuery = qbc.SavedQueryIdDesc
};
return(c.GetExpression(parm, Db, qbc.SetIncludeDeceased, qbc.SetParentsOf));
}
internal string Display(QueryBuilderClause c)
{
if (DisplayAs.HasValue() && Params.HasValue())
{
return(formatArgs(DisplayAs, c));
}
return(Util.PickFirst(DisplayAs, Name));
}
private void DeleteClause(QueryBuilderClause qb, CMSDataContext Db)
{
foreach (var c in qb.Clauses)
{
DeleteClause(c, Db);
}
Db.QueryBuilderClauses.DeleteOnSubmit(qb);
}
public static QueryBuilderClause NewGroupClause()
{
var qb = new QueryBuilderClause();
qb.SetQueryType(QueryType.Group);
qb.SetComparisonType(CompareType.AllTrue);
return(qb);
}
public QueryBuilderClause AddNewGroupClause(CompareType op)
{
var qb = new QueryBuilderClause();
qb.ClauseOrder = qb.MaxClauseOrder() + 1;
qb.SetQueryType(QueryType.Group);
this.Clauses.Add(qb);
qb.SetComparisonType(op);
return(qb);
}
public QueryBuilderClause Clone(CMSDataContext Db)
{
var q = new QueryBuilderClause();
q.CopyFrom(this);
Db.QueryBuilderClauses.InsertOnSubmit(q);
foreach (var c in Clauses)
{
q.Clauses.Add(c.Clone(Db));
}
return(q);
}
public void CopyFromAll(QueryBuilderClause from, CMSDataContext Db)
{
foreach (var c in Clauses)
{
DeleteClause(c, Db);
}
CopyFrom(from);
foreach (var c in from.Clauses)
{
Clauses.Add(c.Clone(Db));
}
}
public QueryBuilderClause SaveTo(CMSDataContext db, string name, string user, bool ispublic)
{
var saveto = new QueryBuilderClause();
db.QueryBuilderClauses.InsertOnSubmit(saveto);
saveto.CopyFromAll(this, db);
saveto.SavedBy = user;
saveto.Description = name;
saveto.IsPublic = ispublic;
db.SubmitChanges();
return(saveto);
}
public QueryBuilderClause AddNewClause(QueryType type, CompareType op, object value)
{
var qb = new QueryBuilderClause();
qb.ClauseOrder = qb.MaxClauseOrder() + 1;
qb.SetQueryType(type);
this.Clauses.Add(qb);
qb.SetComparisonType(op);
if (type == QueryType.MatchAnything)
{
qb.CodeIdValue = "1,true";
return(qb);
}
if (type == QueryType.HasMyTag)
{
qb.Tags = value.ToString();
qb.CodeIdValue = "1,true";
return(qb);
}
switch (qb.FieldInfo.Type)
{
case FieldType.NullBit:
case FieldType.Bit:
case FieldType.Code:
case FieldType.NullCode:
case FieldType.CodeStr:
qb.CodeIdValue = value.ToString();
break;
case FieldType.Date:
case FieldType.DateSimple:
qb.DateValue = (DateTime?)value;
break;
case FieldType.Number:
case FieldType.NullNumber:
case FieldType.NullInteger:
case FieldType.String:
case FieldType.StringEqual:
case FieldType.Integer:
case FieldType.IntegerSimple:
case FieldType.IntegerEqual:
qb.TextValue = value.ToString();
break;
default:
throw new ArgumentException("type not allowed");
}
return(qb);
}
public static int Import(CMSDataContext Db, string text, string name)
{
var x = XDocument.Parse(text);
QueryBuilderClause c = null;
foreach (var xc in x.Root.Elements())
{
if (name.HasValue())
{
c = InsertClause(Db, xc, null, name);
}
else
{
c = InsertClause(Db, xc, null, null);
}
}
return(c.QueryId);
}
private string formatArgs(string fmt, QueryBuilderClause c)
{
var p = new List <object>();
foreach (var s in ParamList)
{
var s2 = s;
if (s2 == "Week")
{
s2 = "Quarters";
}
else if (s2 == "Ministry")
{
s2 = "Program";
}
else if (s2 == "View")
{
s2 = "Quarters";
}
else if (s2 == "PmmLabels")
{
s2 = "Tags";
}
object prop = Util.GetProperty(c, s2);
if (prop is DateTime?)
{
var dt = prop as DateTime?;
if (dt.Value != dt.Value.Date)
{
prop = dt.FormatDateTm();
}
else
{
prop = dt.FormatDate();
}
}
if (s == "SavedQueryValue")
{
prop = ((string)prop).Split(',')[1];
}
p.Add(prop);
}
return(fmt.Fmt(p.ToArray()));
}
private void CopyFrom(QueryBuilderClause from)
{
Age = from.Age;
ClauseOrder = from.ClauseOrder;
CodeIdValue = from.CodeIdValue;
Comparison = from.Comparison;
DateValue = from.DateValue;
Days = from.Days;
Description = from.Description;
Division = from.Division;
EndDate = from.EndDate;
Field = from.Field;
Organization = from.Organization;
Program = from.Program;
Quarters = from.Quarters;
SavedQueryIdDesc = from.SavedQueryIdDesc;
Schedule = from.Schedule;
StartDate = from.StartDate;
Tags = from.Tags;
TextValue = from.TextValue;
}
internal string ToString(QueryBuilderClause c)
{
string fld = c.FieldInfo.Display(c);
if (c.Field == "MatchAnything")
{
return(fld);
}
switch (FieldType)
{
case FieldType.NullBit:
case FieldType.Bit:
case FieldType.Code:
case FieldType.NullCode:
case FieldType.CodeStr:
return(Display.Fmt(fld, c.CodeValues));
case FieldType.String:
case FieldType.StringEqual:
case FieldType.StringEqualOrStartsWith:
case FieldType.Number:
case FieldType.NumberLG:
case FieldType.NullNumber:
case FieldType.Integer:
case FieldType.IntegerSimple:
case FieldType.IntegerEqual:
case FieldType.NullInteger:
return(Display.Fmt(fld, c.TextValue));
case FieldType.Date:
case FieldType.DateSimple:
return(Display.Fmt(fld, c.DateValue));
case FieldType.DateField:
return(Display.Fmt(fld, c.CodeIdValue));
default:
throw new ArgumentException();
}
}
private void WriteClause(QueryBuilderClause clause, API.APIWriter w)
{
//w.WriteStartElement("Condition");
//w.WriteAttributeString("ClauseOrder", clause.ClauseOrder.ToString());
//w.WriteAttributeString("Field", clause.Field);
//if (clause.Description.HasValue())
// w.WriteAttributeString("Description", clause.Description);
//w.WriteAttributeString("Comparison", clause.Comparison);
//if (clause.TextValue.HasValue())
// w.WriteAttributeString("TextValue", clause.TextValue);
//if (clause.DateValue.HasValue)
// w.WriteAttributeString("DateValue", clause.DateValue.ToString());
//if (clause.CodeIdValue.HasValue())
// w.WriteAttributeString("CodeIdValue", clause.CodeIdValue);
//if (clause.StartDate.HasValue)
// w.WriteAttributeString("StartDate", clause.StartDate.ToString());
//if (clause.EndDate.HasValue)
// w.WriteAttributeString("EndDate", clause.EndDate.ToString());
//if (clause.Program > 0)
// w.WriteAttributeString("Program", clause.Program.ToString());
//if (clause.Division > 0)
// w.WriteAttributeString("Division", clause.Division.ToString());
//if (clause.Organization > 0)
// w.WriteAttributeString("Organization", clause.Organization.ToString());
//if (clause.Days > 0)
// w.WriteAttributeString("Days", clause.Days.ToString());
//if (clause.Quarters.HasValue())
// w.WriteAttributeString("Quarters", clause.Quarters);
//if (clause.Tags.HasValue())
// w.WriteAttributeString("Tags", clause.Tags);
//if (clause.Schedule > 0)
// w.WriteAttributeString("Schedule", clause.Schedule.ToString());
//if (clause.Age.HasValue)
// w.WriteAttributeString("Age", clause.Age.ToString());
//foreach (var qc in clause.Clauses)
// WriteClause(qc);
//w.WriteEndElement();
}
private void WriteClause(XmlWriter w, QueryBuilderClause clause)
{
w.WriteStartElement("Condition");
w.WriteAttributeString("ClauseOrder", clause.ClauseOrder.ToString());
w.WriteAttributeString("Field", clause.Field);
if (clause.Description.HasValue())
w.WriteAttributeString("Description", clause.Description);
w.WriteAttributeString("Comparison", clause.Comparison);
if (clause.TextValue.HasValue())
w.WriteAttributeString("TextValue", clause.TextValue);
if (clause.DateValue.HasValue)
w.WriteAttributeString("DateValue", clause.DateValue.ToString());
if (clause.CodeIdValue.HasValue())
w.WriteAttributeString("CodeIdValue", clause.CodeIdValue);
if (clause.StartDate.HasValue)
w.WriteAttributeString("StartDate", clause.StartDate.ToString());
if (clause.EndDate.HasValue)
w.WriteAttributeString("EndDate", clause.EndDate.ToString());
if (clause.Program > 0)
w.WriteAttributeString("Program", clause.Program.ToString());
if (clause.Division > 0)
w.WriteAttributeString("Division", clause.Division.ToString());
if (clause.Organization > 0)
w.WriteAttributeString("Organization", clause.Organization.ToString());
if (clause.Days > 0)
w.WriteAttributeString("Days", clause.Days.ToString());
if (clause.Quarters.HasValue())
w.WriteAttributeString("Quarters", clause.Quarters);
if (clause.Tags.HasValue())
w.WriteAttributeString("Tags", clause.Tags);
if (clause.Schedule > 0)
w.WriteAttributeString("Schedule", clause.Schedule.ToString());
if (clause.Age.HasValue)
w.WriteAttributeString("Age", clause.Age.ToString());
foreach (var qc in clause.Clauses)
WriteClause(w, qc);
w.WriteEndElement();
}
private static QueryBuilderClause InsertClause(CMSDataContext Db, XElement r, int?parent, string name = null)
{
var c = new QueryBuilderClause
{
Field = Attribute(r, "Field"),
GroupId = parent,
ClauseOrder = Attribute(r, "ClauseOrder").ToInt(),
Comparison = Attribute(r, "Comparison"),
TextValue = Attribute(r, "TextValue"),
DateValue = AttributeDate(r, "DateValue"),
CodeIdValue = Attribute(r, "CodeIdValue"),
StartDate = AttributeDate(r, "StartDate"),
EndDate = AttributeDate(r, "EndDate"),
Program = Attribute(r, "Program").ToInt(),
Division = Attribute(r, "Division").ToInt(),
Organization = Attribute(r, "Organization").ToInt(),
Days = Attribute(r, "Days").ToInt(),
Quarters = Attribute(r, "Quarters"),
Tags = Attribute(r, "Tags"),
Schedule = Attribute(r, "Schedule").ToInt(),
Age = Attribute(r, "Age").ToInt(),
Description = name,
SavedBy = Util.UserName
};
Db.QueryBuilderClauses.InsertOnSubmit(c);
Db.SubmitChanges();
if (c.Field == "Group")
{
foreach (var rr in r.Elements())
{
InsertClause(Db, rr, c.QueryId);
}
}
return(c);
}
private void CopyFrom(QueryBuilderClause from)
{
Age = from.Age;
ClauseOrder = from.ClauseOrder;
CodeIdValue = from.CodeIdValue;
Comparison = from.Comparison;
DateValue = from.DateValue;
Days = from.Days;
Description = from.Description;
Division = from.Division;
EndDate = from.EndDate;
Field = from.Field;
Organization = from.Organization;
Program = from.Program;
Quarters = from.Quarters;
OrgType = from.OrgType;
SavedQueryIdDesc = from.SavedQueryIdDesc;
Schedule = from.Schedule;
StartDate = from.StartDate;
Tags = from.Tags;
TextValue = from.TextValue;
}
private void detach_Clauses(QueryBuilderClause entity)
{
this.SendPropertyChanging();
entity.Parent = null;
}
public void CopyFromAll(QueryBuilderClause from, CMSDataContext Db)
{
foreach (var c in Clauses)
DeleteClause(c, Db);
CopyFrom(from);
foreach (var c in from.Clauses)
Clauses.Add(c.Clone(Db));
}
public QueryBuilderClause AddNewClause(QueryType type, CompareType op, object value)
{
var qb = new QueryBuilderClause();
qb.ClauseOrder = qb.MaxClauseOrder() + 1;
qb.SetQueryType(type);
this.Clauses.Add(qb);
qb.SetComparisonType(op);
if (type == QueryType.MatchAnything)
{
qb.CodeIdValue = "1,true";
return qb;
}
if (type == QueryType.HasMyTag)
{
qb.Tags = value.ToString();
qb.CodeIdValue = "1,true";
return qb;
}
switch (qb.FieldInfo.Type)
{
case FieldType.NullBit:
case FieldType.Bit:
case FieldType.Code:
case FieldType.NullCode:
case FieldType.CodeStr:
qb.CodeIdValue = value.ToString();
break;
case FieldType.Date:
case FieldType.DateSimple:
qb.DateValue = (DateTime?)value;
break;
case FieldType.Number:
case FieldType.NullNumber:
case FieldType.NullInteger:
case FieldType.String:
case FieldType.StringEqual:
case FieldType.Integer:
case FieldType.IntegerSimple:
case FieldType.IntegerEqual:
qb.TextValue = value.ToString();
break;
default:
throw new ArgumentException("type not allowed");
}
return qb;
}
public QueryBuilderClause AddNewGroupClause(CompareType op)
{
var qb = new QueryBuilderClause();
qb.ClauseOrder = qb.MaxClauseOrder() + 1;
qb.SetQueryType(QueryType.Group);
this.Clauses.Add(qb);
qb.SetComparisonType(op);
return qb;
}
private List<QueryClauseDisplay> ClauseAndSubs(List<QueryClauseDisplay> list, QueryBuilderClause qc)
{
list.Add(new QueryClauseDisplay { Level = (level * 25), Clause = qc });
level++;
var q = qc.Clauses.OrderBy(c => c.ClauseOrder);
foreach (var c in q)
list = ClauseAndSubs(list, c);
level--;
return list;
}
internal string Display(QueryBuilderClause c)
{
if (DisplayAs.HasValue() && Params.HasValue())
return formatArgs(DisplayAs, c);
return Util.PickFirst(DisplayAs, Name);
}
internal Expression Expression(QueryBuilderClause c, ParameterExpression parm, CMSDataContext Db)
{
switch (c.FieldInfo.QueryType)
{
case QueryType.ActiveRecords:
c.SetIncludeDeceased();
return(Expressions.ActiveRecords(parm,
CompType,
c.CodeIds == "1"));
case QueryType.DaysAfterNthVisitDateRange:
return(Expressions.DaysAfterNthVisitDateRange(parm, Db,
c.StartDate,
c.EndDate,
c.Quarters.ToInt(),
c.Program,
c.Division,
c.Organization,
CompType,
c.TextValue.ToInt()));
case QueryType.AttendPct:
return(Expressions.AttendPct(parm,
c.Program,
c.Division,
c.Organization,
CompType,
decimal.Parse(c.TextValue)));
case QueryType.AttendPctHistory:
return(Expressions.AttendPctHistory(parm, Db,
c.Program,
c.Division,
c.Organization,
c.StartDate,
c.EndDate,
CompType,
double.Parse(c.TextValue)));
case QueryType.AttendCntHistory:
return(Expressions.AttendCntHistory(parm, Db,
c.Program,
c.Division,
c.Organization,
c.StartDate,
c.EndDate,
CompType,
c.TextValue.ToInt()));
case QueryType.AttendTypeAsOf:
return(Expressions.AttendanceTypeAsOf(parm,
c.StartDate,
c.EndDate,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
CompType,
c.CodeIntIds));
case QueryType.AttendMemberTypeAsOf:
return(Expressions.AttendMemberTypeAsOf(Db,
parm,
c.StartDate,
c.EndDate,
c.Program,
c.Division,
c.Organization,
CompType,
string.Join(",", c.CodeStrIds)));
// B --------------------
case QueryType.PmmBackgroundCheckStatus:
return(Expressions.BackgroundCheckStatus(parm,
c.Tags,
c.Quarters,
CompType,
c.CodeIntIds));
case QueryType.Birthday:
return(Expressions.Birthday(parm, CompType, c.TextValue));
// C ------------------------
case QueryType.CampusId:
return(Expressions.CampusId(parm,
CompType,
c.CodeIntIds));
case QueryType.CheckInVisits:
return(Expressions.CheckInVisits(parm, Db,
CompType, c.TextValue.ToInt()));
case QueryType.CreatedBy:
return(Expressions.CreatedBy(parm, Db,
CompType, c.TextValue));
case QueryType.ContributionAmount2:
return(Expressions.ContributionAmount2(parm, Db,
c.StartDate,
c.EndDate, c.Quarters.ToInt(),
CompType,
Decimal.Parse(c.TextValue)));
case QueryType.ContributionChange:
return(Expressions.ContributionChange(parm, Db,
c.StartDate,
c.EndDate,
CompType,
double.Parse(c.TextValue)));
// D --------------------
case QueryType.DaysBetween12Attendance:
return(Expressions.DaysBetween12Attendance(parm, Db,
c.Days,
c.Program,
c.Division,
c.Organization,
CompType,
c.TextValue.ToInt()));
case QueryType.DaysTillBirthday:
return(Expressions.DaysTillBirthday(parm, Db,
CompType,
c.TextValue.ToInt()));
case QueryType.DaysTillAnniversary:
return(Expressions.DaysTillAnniversary(parm, Db,
CompType,
c.TextValue.ToInt()));
case QueryType.DaysSinceContact:
return(Expressions.DaysSinceContact(parm,
CompType,
c.TextValue.ToInt()));
case QueryType.DuplicateNames:
return(Expressions.DuplicateNames(Db,
parm,
CompType,
c.CodeIds == "1"));
case QueryType.DuplicateEmails:
return(Expressions.DuplicateEmails(Db,
parm,
CompType,
c.CodeIds == "1"));
// E ----------------------------
case QueryType.EmailRecipient:
return(Expressions.EmailRecipient(parm,
CompType,
c.TextValue.ToInt()));
// F ----------------------------
case QueryType.FamHasPrimAdultChurchMemb:
return(Expressions.FamHasPrimAdultChurchMemb(parm, CompType, c.CodeIds == "1"));
case QueryType.FamilyHasChildren:
return(Expressions.FamilyHasChildren(parm, CompType, c.CodeIds == "1"));
case QueryType.FamilyHasChildrenAged:
return(Expressions.FamilyHasChildrenAged(parm, c.Age.ToInt(), CompType, c.CodeIds == "1"));
case QueryType.FamilyHasChildrenAged2:
return(Expressions.FamilyHasChildrenAged2(parm, c.Quarters, CompType, c.CodeIds == "1"));
case QueryType.FamilyHasChildrenAged3:
return(Expressions.FamilyHasChildrenAged3(parm, c.Quarters, CompType, c.CodeIntIds));
// H --------------------
case QueryType.HasBalanceInCurrentOrg:
return(Expressions.HasBalanceInCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasCurrentTag:
return(Expressions.HasCurrentTag(Db, parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasMyTag:
return(Expressions.HasMyTag(parm,
c.Tags,
CompType,
c.CodeIds == "1"));
case QueryType.HasLowerName:
return(Expressions.HasLowerName(Db,
parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasPicture:
return(Expressions.HasPicture(parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasRelatedFamily:
return(Expressions.HasRelatedFamily(parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasParents:
return(Expressions.HasParents(parm,
CompType,
c.CodeIds == "1"));//HasValidEmailAddress
case QueryType.HasInvalidEmailAddress:
return(Expressions.HasInvalidEmailAddress(Db, parm,
CompType,
c.CodeIds == "1"));
case QueryType.HaveVolunteerApplications:
return(Expressions.HasVolunteerApplications(parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasManagedGiving:
return(Expressions.HasManagedGiving(parm, Db,
CompType,
c.CodeIds == "1"));
case QueryType.HasMemberDocs:
return(Expressions.HasMemberDocs(parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasContacts:
return(Expressions.HasContacts(parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasTaskWithName:
return(Expressions.HasTaskWithName(parm,
CompType,
c.TextValue));
case QueryType.HasIncompleteTask:
return(Expressions.HasIncompleteTask(parm,
CompType,
c.TextValue));
case QueryType.HasOptoutsForEmail:
return(Expressions.HasEmailOptout(parm,
c.EndDate,
CompType,
c.TextValue));
// I -----------------
case QueryType.IsCurrentPerson:
return(Expressions.IsCurrentPerson(Db, parm,
CompType,
c.CodeIds == "1"));
case QueryType.InCurrentOrg:
return(Expressions.InCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1"));
case QueryType.InactiveCurrentOrg:
return(Expressions.InactiveCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1"));
case QueryType.InOneOfMyOrgs:
return(Expressions.InOneOfMyOrgs(parm, Db,
CompType,
c.CodeIds == "1"));
case QueryType.IsUser:
return(Expressions.IsUser(parm,
CompType,
c.CodeIds == "1"));
case QueryType.IsMemberOf:
return(Expressions.IsMemberOf(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIds == "1"));
case QueryType.IsInactiveMemberOf:
return(Expressions.IsInactiveMemberOf(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIds == "1"));
case QueryType.IsPendingMemberOf:
return(Expressions.IsPendingMemberOf(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIds == "1"));
case QueryType.IsTopGiver:
return(Expressions.IsTopGiver(parm, Db,
c.Days,
c.Quarters,
CompType,
c.CodeIds == "1"));
case QueryType.IsTopPledger:
return(Expressions.IsTopPledger(parm, Db,
c.Days,
c.Quarters,
CompType,
c.CodeIds == "1"));
case QueryType.InBFClass:
return(Expressions.InBFClass(parm,
CompType,
c.CodeIds == "1"));
case QueryType.IncludeDeceased:
c.SetIncludeDeceased();
return(Expressions.IncludeDeceased(parm,
CompType,
c.CodeIds == "1"));
case QueryType.IsHeadOfHousehold:
return(Expressions.IsHeadOfHousehold(parm,
CompType,
c.CodeIds == "1"));
// K -------------------
case QueryType.KidsRecentAttendCount:
return(Expressions.KidsRecentAttendCount(parm,
c.Days,
CompType,
c.TextValue.ToInt()));
// L -------------------
case QueryType.LeadersUnderCurrentOrg:
return(Expressions.LeadersUnderCurrentOrg(parm, Db,
CompType,
c.CodeIds == "1"));
// M -------------------
case QueryType.MatchAnything:
return(Expressions.MatchAnything(parm));
case QueryType.MembersUnderCurrentOrg:
return(Expressions.MembersUnderCurrentOrg(parm, Db,
CompType,
c.CodeIds == "1"));
case QueryType.MadeContactTypeAsOf:
return(Expressions.MadeContactTypeAsOf(parm,
c.StartDate,
c.EndDate,
c.Program,
CompType,
c.CodeIntIds));
case QueryType.MembOfOrgWithCampus:
return(Expressions.MembOfOrgWithCampus(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
CompType,
c.CodeIntIds));
case QueryType.MembOfOrgWithSched:
return(Expressions.MembOfOrgWithSched(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIntIds));
case QueryType.MemberTypeCodes:
return(Expressions.MemberTypeIds(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Schedule,
c.Campus ?? 0,
CompType,
c.CodeIntIds));
case QueryType.MemberTypeAsOf:
return(Expressions.MemberTypeAsOf(parm,
c.StartDate,
c.EndDate,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
CompType,
c.CodeIntIds));
case QueryType.MeetingId:
return(Expressions.MeetingId(parm,
CompType,
c.TextValue.ToInt()));
// N -------------------
case QueryType.NumberOfMemberships:
return(Expressions.NumberOfMemberships(parm,
c.Program,
c.Division,
c.Organization,
c.Schedule,
CompType,
c.TextValue.ToInt()));
case QueryType.NumberOfFamilyMembers:
return(Expressions.NumberOfFamilyMembers(parm,
CompType,
c.TextValue.ToInt()));
// O --------------------------
//case QueryType.OrgMemberCreatedDate:
// return Expressions.OrgMemberCreatedDate(parm,
// c.Program,
// c.Division,
// c.Organization,
// CompType,
// c.DateValue);
case QueryType.OrgInactiveDate:
return(Expressions.OrgInactiveDate(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.DateValue));
case QueryType.OrgJoinDateCompare:
return(Expressions.OrgJoinDateCompare(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIdValue));
case QueryType.OrgJoinDateDaysAgo:
return(Expressions.OrgJoinDateDaysAgo(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.TextValue.ToInt()));
case QueryType.OrgJoinDate:
return(Expressions.OrgJoinDate(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.DateValue));
// P ----------------
case QueryType.ParentsOf:
c.SetParentsOf(CompType, c.CodeIds == "1");
return(Expressions.ParentsOf(parm,
CompType,
c.CodeIds == "1"));
case QueryType.PendingCurrentOrg:
return(Expressions.PendingCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1"));
case QueryType.HasPeopleExtraField:
return(Expressions.HasPeopleExtraField(parm,
CompType,
c.TextValue));
case QueryType.PeopleExtra:
return(Expressions.PeopleExtra(parm,
CompType,
c.CodeStrIds));
case QueryType.PeopleExtraData:
return(Expressions.PeopleExtraData(parm,
c.Quarters,
CompType,
c.TextValue));
case QueryType.PeopleExtraDate:
return(Expressions.PeopleExtraDate(parm,
c.Quarters,
CompType,
c.DateValue));
case QueryType.PeopleExtraInt:
return(Expressions.PeopleExtraInt(parm,
c.Quarters,
CompType,
c.TextValue.ToInt2()));
case QueryType.PeopleIds:
return(Expressions.PeopleIds(parm,
CompType,
(c.TextValue ?? "").Split(',').Select(aa => aa.ToInt()).ToArray()));
case QueryType.PreviousCurrentOrg:
return(Expressions.PreviousCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1"));
// R ----------------
case QueryType.RecentCreated:
return(Expressions.RecentCreated(parm,
c.Days,
CompType,
c.CodeIds == "1"));
case QueryType.RecentJoinChurch:
return(Expressions.RecentJoinChurch(
parm,
c.Days,
CompType,
c.CodeIds == "1"));
case QueryType.RecentAttendType:
return(Expressions.RecentAttendType(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Days,
CompType,
c.CodeIntIds));
// case QueryType.RecentAttendDayOfWeek:
// return Expressions.RecentAttendDayOfWeek(parm,
// c.Program,
// c.Division,
// c.Organization,
// c.OrgType ?? 0,
// c.Days,
// CompType,
// c.TextValue.ToInt());
case QueryType.RecentContactMinistry:
return(Expressions.RecentContactMinistry(parm,
c.Days,
CompType,
c.CodeIntIds));
case QueryType.RecentContactType:
return(Expressions.RecentContactType(parm,
c.Days,
CompType,
c.CodeIntIds));
case QueryType.RecentContactReason:
return(Expressions.RecentContactReason(parm,
c.Days,
CompType,
c.CodeIntIds));
case QueryType.RecentDecisionType:
return(Expressions.RecentDecisionType(parm,
c.Days,
CompType,
c.CodeIntIds));
case QueryType.RecentEmailCount:
return(Expressions.RecentEmailCount(parm,
c.Days,
CompType,
c.TextValue.ToInt()));
case QueryType.RecentHasIndContributions:
return(Expressions.RecentHasIndContributions(parm, Db,
c.Days,
CompType,
c.CodeIds == "1"));
case QueryType.RecentContributionCount:
return(Expressions.RecentContributionCount(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
c.TextValue.ToInt()));
case QueryType.RecentFirstTimeGiver:
return(Expressions.RecentFirstTimeGiver(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
c.CodeIds == "1"));
case QueryType.RecentContributionAmount:
return(Expressions.RecentContributionAmount(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
Decimal.Parse(c.TextValue)));
case QueryType.RecentGivingAsPctOfPrevious:
return(Expressions.RecentGivingAsPctOfPrevious(parm, Db,
c.Quarters.ToInt2() ?? 365,
CompType,
Double.Parse(c.TextValue)));
case QueryType.RecentPledgeCount:
return(Expressions.RecentPledgeCount(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
c.TextValue.ToInt()));
case QueryType.RecentPledgeAmount:
return(Expressions.RecentPledgeAmount(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
Decimal.Parse(c.TextValue)));
case QueryType.RecentAttendCount:
return(Expressions.RecentAttendCount(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Days,
CompType,
c.TextValue.ToInt()));
case QueryType.RecentAttendCountAttCred:
return(Expressions.RecentAttendCountAttCred(parm,
c.Program,
c.Division,
c.Organization,
c.Quarters.ToInt(),
c.Days,
CompType,
c.TextValue.ToInt()));
case QueryType.RecentNewVisitCount:
return(Expressions.RecentNewVisitCount(parm, Db,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Quarters,
c.Days,
CompType,
c.TextValue.ToInt()));
case QueryType.RecentRegistrationType:
return(Expressions.RecentRegistrationType(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Days,
CompType,
c.CodeIntIds));
case QueryType.RecentAttendMemberType:
return(Expressions.RecentAttendMemberType(parm,
c.Program,
c.Division,
c.Organization,
c.Days,
CompType,
c.CodeIntIds));
case QueryType.RecentVisitNumber:
return(Expressions.RecentVisitNumber(parm, Db,
c.Quarters,
c.Days,
CompType,
c.CodeIds == "1"));
case QueryType.RecActiveOtherChurch:
return(Expressions.RecActiveOtherChurch(parm,
CompType,
c.CodeIds == "1"));
case QueryType.RecInterestedCoaching:
return(Expressions.RecInterestedCoaching(parm,
CompType,
c.CodeIds == "1"));
case QueryType.RegisteredForMeetingId:
return(Expressions.RegisteredForMeetingId(parm,
CompType,
c.TextValue.ToInt()));
// S -------------------------
case QueryType.SavedQuery:
return(Expressions.SavedQuery(parm, Db,
c.SavedQueryIdDesc,
CompType,
c.CodeIds == "1"));
case QueryType.SmallGroup:
return(Expressions.SmallGroup(parm,
c.Program,
c.Division,
c.Organization,
CompType, c.TextValue));
case QueryType.StatusFlag:
return(Expressions.StatusFlag(parm,
CompType,
c.CodeIntIds));
// U ----------------------
case QueryType.UserRole:
return(Expressions.UserRole(parm,
CompType,
c.CodeIntIds));
// V -------------------
case QueryType.VisitNumber:
return(Expressions.VisitNumber(parm, Db,
c.Quarters,
CompType,
c.DateValue));
case QueryType.VisitedCurrentOrg:
return(Expressions.VisitedCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1"));
case QueryType.VolunteerApprovalCode:
return(Expressions.VolunteerApprovalCode(parm,
CompType,
c.CodeIntIds));
case QueryType.VolAppStatusCode:
return(Expressions.VolAppStatusCode(parm,
CompType,
c.CodeIntIds));
case QueryType.VolunteerProcessedDateMonthsAgo:
return(Expressions.VolunteerProcessedDateMonthsAgo(parm,
CompType,
int.Parse(c.TextValue)));
// W ----------------------
case QueryType.WasMemberAsOf:
return(Expressions.WasMemberAsOf(parm,
c.StartDate,
c.EndDate,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
CompType,
c.CodeIds == "1"));
case QueryType.WeddingDate:
return(Expressions.WeddingDate(parm, CompType, c.TextValue));
case QueryType.WidowedDate:
return(Expressions.WidowedDate(parm,
Db,
CompType,
c.DateValue));
case QueryType.MemberStatusId:
case QueryType.MaritalStatusId:
case QueryType.GenderId:
case QueryType.DropCodeId:
case QueryType.JoinCodeId:
if (CompType == CompareType.IsNull || CompType == CompareType.IsNotNull)
{
return(Expressions.CompareConstant(parm, c.Field, CompType, -1));
}
return(Expressions.CompareConstant(parm, c.Field, CompType,
IsMultiple ? (object)c.CodeIntIds : (object)c.CodeIds.ToInt()));
case QueryType.PrimaryAddress:
case QueryType.PrimaryAddress2:
case QueryType.PrimaryZip:
case QueryType.PrimaryCountry:
case QueryType.PrimaryCity:
case QueryType.FirstName:
case QueryType.MiddleName:
case QueryType.MaidenName:
case QueryType.NickName:
case QueryType.CellPhone:
case QueryType.WorkPhone:
case QueryType.HomePhone:
case QueryType.EmailAddress:
case QueryType.EmailAddress2:
if (CompType == CompareType.IsNull || CompType == CompareType.IsNotNull)
{
return(Expressions.CompareConstant(parm, c.Field, CompType, null));
}
return(Expressions.CompareStringConstant(parm, c.Field, CompType, c.TextValue ?? ""));
default:
if (CompType == CompareType.IsNull || CompType == CompareType.IsNotNull)
{
return(Expressions.CompareConstant(parm,
c.Field,
CompType,
null));
}
switch (FieldType)
{
case FieldType.NullBit:
case FieldType.Bit:
return(Expressions.CompareConstant(parm,
c.Field,
CompType,
c.CodeIds == "1"));
case FieldType.Code:
return(Expressions.CompareConstant(parm,
c.Field,
CompType,
IsMultiple ? (object)c.CodeIntIds : (object)c.CodeIds.ToInt()));
case FieldType.NullCode:
return(Expressions.CompareCodeConstant(parm,
c.Field,
CompType,
IsMultiple ? (object)c.CodeIntIds : (object)c.CodeIds.ToInt()));
case FieldType.CodeStr:
return(Expressions.CompareConstant(parm,
c.Field,
CompType,
IsMultiple ? (object)c.CodeStrIds : (object)c.CodeIdValue));
case FieldType.String:
return(Expressions.CompareConstant(parm,
c.Field,
CompType,
c.TextValue));
case FieldType.Number:
case FieldType.NullNumber:
return(Expressions.CompareConstant(parm,
c.Field,
CompType,
decimal.Parse(c.TextValue)));
case FieldType.Integer:
case FieldType.IntegerSimple:
case FieldType.NullInteger:
return(Expressions.CompareIntConstant(parm,
c.Field,
CompType,
c.TextValue));
case FieldType.Date:
case FieldType.DateSimple:
if (c.Field == "DeceasedDate")
{
c.SetIncludeDeceased();
}
return(Expressions.CompareDateConstant(parm,
c.Field,
CompType,
c.DateValue));
default:
throw new ArgumentException();
}
}
}
internal Expression Expression(QueryBuilderClause c, ParameterExpression parm, CMSDataContext Db)
{
switch (c.FieldInfo.QueryType)
{
case QueryType.DaysAfterNthVisitDateRange:
return Expressions.DaysAfterNthVisitDateRange(parm, Db,
c.StartDate,
c.EndDate,
c.Quarters.ToInt(),
c.Program,
c.Division,
c.Organization,
CompType,
c.TextValue.ToInt());
case QueryType.AttendPct:
return Expressions.AttendPct(parm,
c.Program,
c.Division,
c.Organization,
CompType,
decimal.Parse(c.TextValue));
case QueryType.AttendPctHistory:
return Expressions.AttendPctHistory(parm, Db,
c.Program,
c.Division,
c.Organization,
c.StartDate,
c.EndDate,
CompType,
double.Parse(c.TextValue));
case QueryType.AttendCntHistory:
return Expressions.AttendCntHistory(parm, Db,
c.Program,
c.Division,
c.Organization,
c.Schedule,
c.StartDate,
c.EndDate,
CompType,
c.TextValue.ToInt());
case QueryType.AttendTypeAsOf:
return Expressions.AttendanceTypeAsOf(parm,
c.StartDate,
c.EndDate,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
CompType,
c.CodeIntIds);
case QueryType.AttendMemberTypeAsOf:
return Expressions.AttendMemberTypeAsOf(Db,
parm,
c.StartDate,
c.EndDate,
c.Program,
c.Division,
c.Organization,
CompType,
string.Join(",", c.CodeStrIds));
// B --------------------
case QueryType.PmmBackgroundCheckStatus:
return Expressions.BackgroundCheckStatus(parm,
c.Tags,
c.Quarters,
CompType,
c.CodeIntIds);
case QueryType.Birthday:
return Expressions.Birthday(parm, CompType, c.TextValue);
// C ------------------------
case QueryType.CampusId:
return Expressions.CampusId(parm,
CompType,
c.CodeIntIds);
case QueryType.CheckInVisits:
return Expressions.CheckInVisits(parm, Db,
CompType, c.TextValue.ToInt());
case QueryType.CreatedBy:
return Expressions.CreatedBy(parm, Db,
CompType, c.TextValue);
case QueryType.ContributionAmount2:
return Expressions.ContributionAmount2(parm, Db,
c.StartDate,
c.EndDate, c.Quarters.ToInt(),
CompType,
Decimal.Parse(c.TextValue));
case QueryType.ContributionChange:
return Expressions.ContributionChange(parm, Db,
c.StartDate,
c.EndDate,
CompType,
double.Parse(c.TextValue));
// D --------------------
case QueryType.DaysBetween12Attendance:
return Expressions.DaysBetween12Attendance(parm, Db,
c.Days,
c.Program,
c.Division,
c.Organization,
CompType,
c.TextValue.ToInt());
case QueryType.DaysTillBirthday:
return Expressions.DaysTillBirthday(parm, Db,
CompType,
c.TextValue.ToInt());
case QueryType.DaysTillAnniversary:
return Expressions.DaysTillAnniversary(parm, Db,
CompType,
c.TextValue.ToInt());
case QueryType.DaysSinceContact:
return Expressions.DaysSinceContact(parm,
CompType,
c.TextValue.ToInt());
case QueryType.DuplicateNames:
return Expressions.DuplicateNames(Db,
parm,
CompType,
c.CodeIds == "1");
case QueryType.DuplicateEmails:
return Expressions.DuplicateEmails(Db,
parm,
CompType,
c.CodeIds == "1");
// E ----------------------------
case QueryType.EmailRecipient:
return Expressions.EmailRecipient(parm,
CompType,
c.TextValue.ToInt());
// F ----------------------------
case QueryType.FamHasPrimAdultChurchMemb:
return Expressions.FamHasPrimAdultChurchMemb(parm, CompType, c.CodeIds == "1");
case QueryType.FamilyHasChildren:
return Expressions.FamilyHasChildren(parm, CompType, c.CodeIds == "1");
case QueryType.FamilyHasChildrenAged:
return Expressions.FamilyHasChildrenAged(parm, c.Age.ToInt(), CompType, c.CodeIds == "1");
case QueryType.FamilyHasChildrenAged2:
return Expressions.FamilyHasChildrenAged2(parm, c.Quarters, CompType, c.CodeIds == "1");
case QueryType.FamilyHasChildrenAged3:
return Expressions.FamilyHasChildrenAged3(parm, c.Quarters, CompType, c.CodeIntIds);
// H --------------------
case QueryType.HasBalanceInCurrentOrg:
return Expressions.HasBalanceInCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1");
case QueryType.HasCurrentTag:
return Expressions.HasCurrentTag(Db, parm,
CompType,
c.CodeIds == "1");
case QueryType.HasMyTag:
return Expressions.HasMyTag(parm,
c.Tags,
CompType,
c.CodeIds == "1");
case QueryType.HasLowerName:
return Expressions.HasLowerName(Db,
parm,
CompType,
c.CodeIds == "1");
case QueryType.HasPicture:
return Expressions.HasPicture(parm,
CompType,
c.CodeIds == "1");
case QueryType.HasRelatedFamily:
return Expressions.HasRelatedFamily(parm,
CompType,
c.CodeIds == "1");
case QueryType.HasParents:
return Expressions.HasParents(parm,
CompType,
c.CodeIds == "1");//HasValidEmailAddress
case QueryType.HasInvalidEmailAddress:
return Expressions.HasInvalidEmailAddress(Db, parm,
CompType,
c.CodeIds == "1");
case QueryType.HaveVolunteerApplications:
return Expressions.HasVolunteerApplications(parm,
CompType,
c.CodeIds == "1");
case QueryType.HasManagedGiving:
return Expressions.HasManagedGiving(parm, Db,
CompType,
c.CodeIds == "1");
case QueryType.HasMemberDocs:
return Expressions.HasMemberDocs(parm,
CompType,
c.CodeIds == "1");
case QueryType.HasContacts:
return Expressions.HasContacts(parm,
CompType,
c.CodeIds == "1");
case QueryType.HasTaskWithName:
return Expressions.HasTaskWithName(parm,
CompType,
c.TextValue);
case QueryType.HasIncompleteTask:
return Expressions.HasIncompleteTask(parm,
CompType,
c.TextValue);
case QueryType.HasOptoutsForEmail:
return Expressions.HasEmailOptout(parm,
c.EndDate,
CompType,
c.TextValue);
// I -----------------
case QueryType.IsCurrentPerson:
return Expressions.IsCurrentPerson(Db, parm,
CompType,
c.CodeIds == "1");
case QueryType.InCurrentOrg:
return Expressions.InCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1");
case QueryType.InactiveCurrentOrg:
return Expressions.InactiveCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1");
case QueryType.InOneOfMyOrgs:
return Expressions.InOneOfMyOrgs(parm, Db,
CompType,
c.CodeIds == "1");
case QueryType.IsUser:
return Expressions.IsUser(parm,
CompType,
c.CodeIds == "1");
case QueryType.IsMemberOf:
return Expressions.IsMemberOf(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIds == "1");
case QueryType.IsInactiveMemberOf:
return Expressions.IsInactiveMemberOf(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIds == "1");
case QueryType.IsPendingMemberOf:
return Expressions.IsPendingMemberOf(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIds == "1");
case QueryType.IsTopGiver:
return Expressions.IsTopGiver(parm, Db,
c.Days,
c.Quarters,
CompType,
c.CodeIds == "1");
case QueryType.IsTopPledger:
return Expressions.IsTopPledger(parm, Db,
c.Days,
c.Quarters,
CompType,
c.CodeIds == "1");
case QueryType.InBFClass:
return Expressions.InBFClass(parm,
CompType,
c.CodeIds == "1");
case QueryType.IncludeDeceased:
c.SetIncludeDeceased();
return Expressions.IncludeDeceased(parm,
CompType,
c.CodeIds == "1");
case QueryType.IsHeadOfHousehold:
return Expressions.IsHeadOfHousehold(parm,
CompType,
c.CodeIds == "1");
// K -------------------
case QueryType.KidsRecentAttendCount:
return Expressions.KidsRecentAttendCount(parm,
c.Days,
CompType,
c.TextValue.ToInt());
// L -------------------
case QueryType.LeadersUnderCurrentOrg:
return Expressions.LeadersUnderCurrentOrg(parm, Db,
CompType,
c.CodeIds == "1");
// M -------------------
case QueryType.MatchAnything:
return Expressions.MatchAnything(parm);
case QueryType.MembersUnderCurrentOrg:
return Expressions.MembersUnderCurrentOrg(parm, Db,
CompType,
c.CodeIds == "1");
case QueryType.MadeContactTypeAsOf:
return Expressions.MadeContactTypeAsOf(parm,
c.StartDate,
c.EndDate,
c.Program,
CompType,
c.CodeIntIds);
case QueryType.MedicalLength:
return Expressions.MedicalLength(parm,
CompType,
c.TextValue.ToInt());
case QueryType.MembOfOrgWithCampus:
return Expressions.MembOfOrgWithCampus(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
CompType,
c.CodeIntIds);
case QueryType.MembOfOrgWithSched:
return Expressions.MembOfOrgWithSched(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIntIds);
case QueryType.MemberTypeCodes:
return Expressions.MemberTypeIds(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Schedule,
c.Campus ?? 0,
CompType,
c.CodeIntIds);
case QueryType.MemberTypeAsOf:
return Expressions.MemberTypeAsOf(parm,
c.StartDate,
c.EndDate,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
CompType,
c.CodeIntIds);
case QueryType.MeetingId:
return Expressions.MeetingId(parm,
CompType,
c.TextValue.ToInt());
// N -------------------
case QueryType.NumberOfMemberships:
return Expressions.NumberOfMemberships(parm,
c.Program,
c.Division,
c.Organization,
c.Schedule,
CompType,
c.TextValue.ToInt());
case QueryType.NumberOfFamilyMembers:
return Expressions.NumberOfFamilyMembers(parm,
CompType,
c.TextValue.ToInt());
// O --------------------------
//case QueryType.OrgMemberCreatedDate:
// return Expressions.OrgMemberCreatedDate(parm,
// c.Program,
// c.Division,
// c.Organization,
// CompType,
// c.DateValue);
case QueryType.OrgInactiveDate:
return Expressions.OrgInactiveDate(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.DateValue);
case QueryType.OrgJoinDateCompare:
return Expressions.OrgJoinDateCompare(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.CodeIdValue);
case QueryType.OrgJoinDateDaysAgo:
return Expressions.OrgJoinDateDaysAgo(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.TextValue.ToInt());
case QueryType.OrgJoinDate:
return Expressions.OrgJoinDate(parm,
c.Program,
c.Division,
c.Organization,
CompType,
c.DateValue);
// P ----------------
case QueryType.ParentsOf:
c.SetParentsOf(CompType, c.CodeIds == "1");
return Expressions.ParentsOf(parm,
CompType,
c.CodeIds == "1");
case QueryType.PendingCurrentOrg:
return Expressions.PendingCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1");
case QueryType.HasPeopleExtraField:
return Expressions.HasPeopleExtraField(parm,
CompType,
c.TextValue);
case QueryType.PeopleExtra:
return Expressions.PeopleExtra(parm,
CompType,
c.CodeStrIds);
case QueryType.PeopleExtraData:
return Expressions.PeopleExtraData(parm,
c.Quarters,
CompType,
c.TextValue);
case QueryType.PeopleExtraDate:
return Expressions.PeopleExtraDate(parm,
c.Quarters,
CompType,
c.DateValue);
case QueryType.PeopleExtraInt:
return Expressions.PeopleExtraInt(parm,
c.Quarters,
CompType,
c.TextValue.ToInt2());
case QueryType.PeopleIds:
return Expressions.PeopleIds(parm,
CompType,
(c.TextValue ?? "").Split(',').Select(aa => aa.ToInt()).ToArray());
case QueryType.PreviousCurrentOrg:
return Expressions.PreviousCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1");
// R ----------------
case QueryType.RecentCreated:
return Expressions.RecentCreated(parm,
c.Days,
CompType,
c.CodeIds == "1");
case QueryType.RecentJoinChurch:
return Expressions.RecentJoinChurch(
parm,
c.Days,
CompType,
c.CodeIds == "1");
case QueryType.RecentAttendType:
return Expressions.RecentAttendType(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Days,
CompType,
c.CodeIntIds);
case QueryType.RecentContactMinistry:
return Expressions.RecentContactMinistry(parm,
c.Days,
CompType,
c.CodeIntIds);
case QueryType.RecentContactType:
return Expressions.RecentContactType(parm,
c.Days,
CompType,
c.CodeIntIds);
case QueryType.RecentContactReason:
return Expressions.RecentContactReason(parm,
c.Days,
CompType,
c.CodeIntIds);
case QueryType.RecentDecisionType:
return Expressions.RecentDecisionType(parm,
c.Days,
CompType,
c.CodeIntIds);
case QueryType.RecentEmailCount:
return Expressions.RecentEmailCount(parm,
c.Days,
CompType,
c.TextValue.ToInt());
case QueryType.RecentHasIndContributions:
return Expressions.RecentHasIndContributions(parm, Db,
c.Days,
CompType,
c.CodeIds == "1");
case QueryType.RecentContributionCount:
return Expressions.RecentContributionCount(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
c.TextValue.ToInt());
case QueryType.RecentFirstTimeGiver:
return Expressions.RecentFirstTimeGiver(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
c.CodeIds == "1");
case QueryType.RecentContributionAmount:
return Expressions.RecentContributionAmount(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
Decimal.Parse(c.TextValue));
case QueryType.RecentGivingAsPctOfPrevious:
return Expressions.RecentGivingAsPctOfPrevious(parm, Db,
c.Quarters.ToInt2() ?? 365,
CompType,
Double.Parse(c.TextValue));
case QueryType.RecentPledgeCount:
return Expressions.RecentPledgeCount(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
c.TextValue.ToInt());
case QueryType.RecentPledgeAmount:
return Expressions.RecentPledgeAmount(parm, Db,
c.Days, c.Quarters.ToInt(),
CompType,
Decimal.Parse(c.TextValue));
case QueryType.RecentAttendCount:
return Expressions.RecentAttendCount(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Days,
CompType,
c.TextValue.ToInt());
case QueryType.RecentAttendCountAttCred:
return Expressions.RecentAttendCountAttCred(parm, Db,
c.Program,
c.Division,
c.Organization,
c.Quarters.ToInt(),
c.Schedule,
c.Days,
CompType,
c.TextValue.ToInt());
case QueryType.RecentNewVisitCount:
return Expressions.RecentNewVisitCount(parm, Db,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Quarters,
c.Days,
CompType,
c.TextValue.ToInt());
case QueryType.RecentRegistrationType:
return Expressions.RecentRegistrationType(parm,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
c.Days,
CompType,
c.CodeIntIds);
case QueryType.RecentAttendMemberType:
return Expressions.RecentAttendMemberType(parm,
c.Program,
c.Division,
c.Organization,
c.Days,
CompType,
c.CodeIntIds);
case QueryType.RecentVisitNumber:
return Expressions.RecentVisitNumber(parm, Db,
c.Quarters,
c.Days,
CompType,
c.CodeIds == "1");
case QueryType.RecActiveOtherChurch:
return Expressions.RecActiveOtherChurch(parm,
CompType,
c.CodeIds == "1");
case QueryType.RecInterestedCoaching:
return Expressions.RecInterestedCoaching(parm,
CompType,
c.CodeIds == "1");
case QueryType.RegisteredForMeetingId:
return Expressions.RegisteredForMeetingId(parm,
CompType,
c.TextValue.ToInt());
// S -------------------------
case QueryType.SavedQuery:
return Expressions.SavedQuery(parm, Db,
c.SavedQueryIdDesc,
CompType,
c.CodeIds == "1");
case QueryType.SmallGroup:
return Expressions.SmallGroup(parm,
c.Program,
c.Division,
c.Organization,
CompType, c.TextValue);
case QueryType.StatusFlag:
return Expressions.StatusFlag(parm,
CompType,
c.CodeValues);
// U ----------------------
case QueryType.UserRole:
return Expressions.UserRole(parm,
CompType,
c.CodeIntIds);
// V -------------------
case QueryType.VisitNumber:
return Expressions.VisitNumber(parm, Db,
c.Quarters,
CompType,
c.DateValue);
case QueryType.VisitedCurrentOrg:
return Expressions.VisitedCurrentOrg(Db, parm,
CompType,
c.CodeIds == "1");
case QueryType.VolunteerApprovalCode:
return Expressions.VolunteerApprovalCode(parm,
CompType,
c.CodeIntIds);
case QueryType.VolAppStatusCode:
return Expressions.VolAppStatusCode(parm,
CompType,
c.CodeIntIds);
case QueryType.VolunteerProcessedDateMonthsAgo:
return Expressions.VolunteerProcessedDateMonthsAgo(parm,
CompType,
int.Parse(c.TextValue));
// W ----------------------
case QueryType.WasMemberAsOf:
return Expressions.WasMemberAsOf(parm,
c.StartDate,
c.EndDate,
c.Program,
c.Division,
c.Organization,
c.OrgType ?? 0,
CompType,
c.CodeIds == "1");
case QueryType.WeddingDate:
return Expressions.WeddingDate(parm, CompType, c.TextValue);
case QueryType.WidowedDate:
return Expressions.WidowedDate(parm,
Db,
CompType,
c.DateValue);
case QueryType.MemberStatusId:
case QueryType.MaritalStatusId:
case QueryType.GenderId:
case QueryType.DropCodeId:
case QueryType.JoinCodeId:
if (CompType == CompareType.IsNull || CompType == CompareType.IsNotNull)
return Expressions.CompareConstant(parm, c.Field, CompType, -1);
return Expressions.CompareConstant(parm, c.Field, CompType,
IsMultiple ? (object)c.CodeIntIds : (object)c.CodeIds.ToInt());
case QueryType.PrimaryAddress:
case QueryType.PrimaryAddress2:
case QueryType.PrimaryZip:
case QueryType.PrimaryCountry:
case QueryType.PrimaryCity:
case QueryType.FirstName:
case QueryType.MiddleName:
case QueryType.MaidenName:
case QueryType.NickName:
case QueryType.CellPhone:
case QueryType.WorkPhone:
case QueryType.HomePhone:
case QueryType.EmailAddress:
case QueryType.EmailAddress2:
if (CompType == CompareType.IsNull || CompType == CompareType.IsNotNull)
return Expressions.CompareConstant(parm, c.Field, CompType, null);
return Expressions.CompareStringConstant(parm, c.Field, CompType, c.TextValue ?? "");
default:
if (CompType == CompareType.IsNull || CompType == CompareType.IsNotNull)
return Expressions.CompareConstant(parm,
c.Field,
CompType,
null);
switch (FieldType)
{
case FieldType.NullBit:
case FieldType.Bit:
return Expressions.CompareConstant(parm,
c.Field,
CompType,
c.CodeIds == "1");
case FieldType.Code:
return Expressions.CompareConstant(parm,
c.Field,
CompType,
IsMultiple ? (object)c.CodeIntIds : (object)c.CodeIds.ToInt());
case FieldType.NullCode:
return Expressions.CompareCodeConstant(parm,
c.Field,
CompType,
IsMultiple ? (object)c.CodeIntIds : (object)c.CodeIds.ToInt());
case FieldType.CodeStr:
return Expressions.CompareConstant(parm,
c.Field,
CompType,
IsMultiple ? (object)c.CodeStrIds : (object)c.CodeIdValue);
case FieldType.String:
return Expressions.CompareConstant(parm,
c.Field,
CompType,
c.TextValue);
case FieldType.Number:
case FieldType.NullNumber:
return Expressions.CompareConstant(parm,
c.Field,
CompType,
decimal.Parse(c.TextValue));
case FieldType.Integer:
case FieldType.IntegerSimple:
case FieldType.NullInteger:
return Expressions.CompareIntConstant(parm,
c.Field,
CompType,
c.TextValue);
case FieldType.Date:
case FieldType.DateSimple:
if (c.Field == "DeceasedDate")
c.SetIncludeDeceased();
return Expressions.CompareDateConstant(parm,
c.Field,
CompType,
c.DateValue);
default:
throw new ArgumentException();
}
}
}
private void DeleteClause(QueryBuilderClause qb, CMSDataContext Db)
{
foreach (var c in qb.Clauses)
DeleteClause(c, Db);
Db.QueryBuilderClauses.DeleteOnSubmit(qb);
}
public void LoadScratchPad()
{
Qb = Db.QueryBuilderScratchPad();
if (QueryId.HasValue && QueryId.Value != Qb.QueryId)
{
var existing = Db.LoadQueryById(QueryId.Value);
if (existing != null)
{
Qb.CopyFromAll(existing, DbUtil.Db);
Description = Qb.Description;
SavedQueryDesc = Qb.Description;
Qb.Description = Util.ScratchPad;
Db.SubmitChanges();
SelectedId = Qb.QueryId;
EditCondition();
}
}
QueryId = Qb.QueryId;
if (SelectedId == null)
SelectedId = Qb.QueryId;
}
public void CopyAsNew()
{
var Qb = Db.LoadQueryById(SelectedId).Clone(DbUtil.Db);
if (!Qb.IsGroup)
{
var g = new QueryBuilderClause();
g.SetQueryType(QueryType.Group);
g.SetComparisonType(CompareType.AllTrue);
Qb.Parent = g;
Qb = g;
}
Db.SubmitChanges();
QueryId = Qb.QueryId;
}
private void UpdateCondition(QueryBuilderClause c)
{
c.Field = ConditionName;
c.Comparison = Comparison;
switch (c.FieldInfo.Type)
{
case FieldType.String:
case FieldType.StringEqual:
case FieldType.StringEqualOrStartsWith:
c.TextValue = TextValue;
break;
case FieldType.Integer:
case FieldType.IntegerSimple:
case FieldType.IntegerEqual:
case FieldType.NullInteger:
c.TextValue = IntegerValue;
break;
case FieldType.Number:
case FieldType.NumberLG:
case FieldType.NullNumber:
c.TextValue = NumberValue;
break;
case FieldType.Date:
case FieldType.DateSimple:
c.DateValue = DateParse(DateValue);
break;
case FieldType.Code:
case FieldType.NullCode:
case FieldType.CodeStr:
case FieldType.DateField:
case FieldType.Bit:
case FieldType.NullBit:
if (c.HasMultipleCodes && CodeValues != null)
c.CodeIdValue = string.Join(";", CodeValues);
else
c.CodeIdValue = CodeValue;
break;
}
c.Program = Program ?? 0;
c.Division = Division ?? 0;
c.Organization = Organization ?? 0;
if (MinistryVisible)
c.Program = Ministry ?? 0;
c.Schedule = Schedule ?? 0;
c.Campus = Campus ?? 0;
c.OrgType = OrgType ?? 0;
c.StartDate = DateParse(StartDate);
c.EndDate = DateParse(EndDate);
c.Days = Days.ToInt();
c.Age = Age.ToInt();
c.Quarters = Quarters;
if (Tags != null)
c.Tags = string.Join(";", Tags);
else if (PmmLabels != null)
c.Tags = string.Join(",", PmmLabels);
else
c.Tags = null;
c.SavedQueryIdDesc = SavedQueryDesc;
Db.SubmitChanges();
}
private void NewCondition(QueryBuilderClause gc, int order)
{
var c = new QueryBuilderClause();
c.ClauseOrder = order;
gc.Clauses.Add(c);
gc.ReorderClauses();
UpdateCondition(c);
}
public QueryBuilderClause Clone(CMSDataContext Db)
{
var q = new QueryBuilderClause();
q.CopyFrom(this);
Db.QueryBuilderClauses.InsertOnSubmit(q);
foreach (var c in Clauses)
q.Clauses.Add(c.Clone(Db));
return q;
}
private void AddMatchAnyThingToGroup(QueryBuilderClause nc)
{
nc = nc.AddNewClause(QueryType.MatchAnything, CompareType.Equal, null);
Db.SubmitChanges();
SelectedId = nc.QueryId;
EditCondition();
}
public string SaveQuery()
{
var admin = HttpContext.Current.User.IsInRole("Admin");
var saveto = Db.QueryBuilderClauses.FirstOrDefault(c =>
(c.SavedBy == Util.UserName || admin) && c.Description == SavedQueryDesc);
var isStatusFlag = Regex.IsMatch(SavedQueryDesc, @"\AF\d\d:", RegexOptions.IgnoreCase);
if (isStatusFlag)
{
var prefix = SavedQueryDesc.Substring(0, 4);
var exis = Db.QueryBuilderClauses.FirstOrDefault(c => c.Description.StartsWith(prefix));
if (exis != null)
if ((!admin && exis.SavedBy != Util.UserName) || exis.Description != SavedQueryDesc)
return "error: StatusFlag {0} already exists".Fmt(prefix);
}
if (saveto == null)
{
saveto = new QueryBuilderClause();
Db.QueryBuilderClauses.InsertOnSubmit(saveto);
saveto.SavedBy = Util.UserName;
}
saveto.CopyFromAll(Qb, DbUtil.Db); // save Qb on top of existing
if (!admin)
saveto.SavedBy = Util.UserName;
saveto.Description = SavedQueryDesc;
saveto.IsPublic = IsPublic;
Db.SubmitChanges();
Description = SavedQueryDesc;
return "";
}
public QueryBuilderClause SaveTo(CMSDataContext db, string name, string user, bool ispublic)
{
var saveto = new QueryBuilderClause();
db.QueryBuilderClauses.InsertOnSubmit(saveto);
saveto.CopyFromAll(this, db);
saveto.SavedBy = user;
saveto.Description = name;
saveto.IsPublic = ispublic;
db.SubmitChanges();
return saveto;
}
internal string ToString(QueryBuilderClause c)
{
string fld = c.FieldInfo.Display(c);
if (c.Field == "MatchAnything")
return fld;
switch (FieldType)
{
case FieldType.NullBit:
case FieldType.Bit:
case FieldType.Code:
case FieldType.NullCode:
case FieldType.CodeStr:
return Display.Fmt(fld, c.CodeValues);
case FieldType.String:
case FieldType.StringEqual:
case FieldType.StringEqualOrStartsWith:
case FieldType.Number:
case FieldType.NumberLG:
case FieldType.NullNumber:
case FieldType.Integer:
case FieldType.IntegerSimple:
case FieldType.IntegerEqual:
case FieldType.NullInteger:
return Display.Fmt(fld, c.TextValue);
case FieldType.Date:
case FieldType.DateSimple:
return Display.Fmt(fld, c.DateValue);
case FieldType.DateField:
return Display.Fmt(fld, c.CodeIdValue);
default:
throw new ArgumentException();
}
}
private void detach_Clauses(QueryBuilderClause entity)
{
this.SendPropertyChanging();
entity.Parent = null;
}
public void SaveQuery()
{
var saveto = Db.QueryBuilderClauses.FirstOrDefault(c =>
(c.SavedBy == Util.UserName || c.SavedBy == "public") && c.Description == SavedQueryDesc);
if (saveto == null)
{
saveto = new QueryBuilderClause();
Db.QueryBuilderClauses.InsertOnSubmit(saveto);
}
saveto.CopyFromAll(Qb, DbUtil.Db); // save Qb on top of existing
if (saveto.SavedBy != "public")
saveto.SavedBy = Util.UserName;
saveto.Description = SavedQueryDesc;
saveto.IsPublic = IsPublic;
Db.SubmitChanges();
Description = SavedQueryDesc;
}
private static QueryBuilderClause InsertClause(CMSDataContext Db, XElement r, int? parent, string name=null)
{
var c = new QueryBuilderClause
{
Field = Attribute(r, "Field"),
GroupId = parent,
ClauseOrder = Attribute(r, "ClauseOrder").ToInt(),
Comparison = Attribute(r, "Comparison"),
TextValue = Attribute(r, "TextValue"),
DateValue = AttributeDate(r, "DateValue"),
CodeIdValue = Attribute(r, "CodeIdValue"),
StartDate = AttributeDate(r, "StartDate"),
EndDate = AttributeDate(r, "EndDate"),
Program = Attribute(r, "Program").ToInt(),
Division = Attribute(r, "Division").ToInt(),
Organization = Attribute(r, "Organization").ToInt(),
Days = Attribute(r, "Days").ToInt(),
Quarters = Attribute(r, "Quarters"),
Tags = Attribute(r, "Tags"),
Schedule = Attribute(r, "Schedule").ToInt(),
Age = Attribute(r, "Age").ToInt(),
Description = name,
SavedBy = Util.UserName
};
Db.QueryBuilderClauses.InsertOnSubmit(c);
Db.SubmitChanges();
if(c.Field == "Group")
foreach (var rr in r.Elements())
InsertClause(Db, rr, c.QueryId);
return c;
}
private string formatArgs(string fmt, QueryBuilderClause c)
{
var p = new List<object>();
foreach (var s in ParamList)
{
var s2 = s;
if (s2 == "Week")
s2 = "Quarters";
else if (s2 == "Ministry")
s2 = "Program";
else if (s2 == "View")
s2 = "Quarters";
else if (s2 == "PmmLabels")
s2 = "Tags";
object prop = Util.GetProperty(c, s2);
if (prop is DateTime?)
{
var dt = prop as DateTime?;
if (dt.Value != dt.Value.Date)
prop = dt.FormatDateTm();
else
prop = dt.FormatDate();
}
if (s == "SavedQueryValue")
prop = ((string)prop).Split(',')[1];
p.Add(prop);
}
return fmt.Fmt(p.ToArray());
}
public QBExportResult(int QueryId)
{
clause = DbUtil.Db.QueryBuilderClauses.Single(cc => cc.QueryId == QueryId);
}
private static QueryBuilderClause ImportClause(XElement r)
{
var c = new QueryBuilderClause
{
Field = Attribute(r, "Field"),
ClauseOrder = Attribute(r, "ClauseOrder").ToInt(),
Comparison = Attribute(r, "Comparison"),
TextValue = Attribute(r, "TextValue"),
DateValue = AttributeDate(r, "DateValue"),
CodeIdValue = Attribute(r, "CodeIdValue"),
StartDate = AttributeDate(r, "StartDate"),
EndDate = AttributeDate(r, "EndDate"),
Program = Attribute(r, "Program").ToInt(),
Division = Attribute(r, "Division").ToInt(),
Organization = Attribute(r, "Organization").ToInt(),
Days = Attribute(r, "Days").ToInt(),
Quarters = Attribute(r, "Quarters"),
Tags = Attribute(r, "Tags"),
Schedule = Attribute(r, "Schedule").ToInt(),
Age = Attribute(r, "Age").ToInt(),
SavedBy = Util.UserName
};
if (c.Field == "Group")
foreach (var rr in r.Elements())
c.Clauses.Add(ImportClause(rr));
return c;
}
public QBExportResult(int QueryId)
{
clause = DbUtil.Db.QueryBuilderClauses.Single(cc => cc.QueryId == QueryId);
}
public void InsertGroupAbove()
{
var cc = Db.LoadQueryById(SelectedId);
var g = new QueryBuilderClause();
g.SetQueryType(QueryType.Group);
g.SetComparisonType(CompareType.AllTrue);
g.ClauseOrder = cc.ClauseOrder;
if (cc.IsFirst)
cc.Parent = g;
else
{
var currParent = cc.Parent;
// find all clauses from cc down at same level
var q = from c in cc.Parent.Clauses
orderby c.ClauseOrder
where c.ClauseOrder >= cc.ClauseOrder
select c;
foreach (var c in q)
c.Parent = g; // change to new parent
g.Parent = currParent;
}
if (cc.SavedBy.HasValue())
{
g.SavedBy = Util.UserName;
g.Description = cc.Description;
g.CreatedOn = cc.CreatedOn;
cc.IsPublic = false;
cc.Description = null;
cc.SavedBy = null;
}
Db.SubmitChanges();
if (g.IsFirst)
{
Qb = g;
QueryId = g.QueryId;
}
}
public static QueryBuilderClause NewGroupClause()
{
var qb = new QueryBuilderClause();
qb.SetQueryType(QueryType.Group);
qb.SetComparisonType(CompareType.AllTrue);
return qb;
}