public ActionResult QueryDataFields(string field, string type)
{
var qb = DbUtil.Db.QueryBuilderScratchPad();
QueryBuilderClause c = null;
qb.CleanSlate(DbUtil.Db);
switch (type)
{
case "Text":
c = qb.AddNewClause(QueryType.PeopleExtraData, CompareType.NotEqual, "");
c.Quarters = field;
break;
case "Date":
c = qb.AddNewClause(QueryType.PeopleExtraDate, CompareType.NotEqual, null);
c.Quarters = field;
break;
case "Int":
c = qb.AddNewClause(QueryType.PeopleExtraInt, CompareType.NotEqual, "");
c.Quarters = field;
break;
case "?":
qb.AddNewClause(QueryType.HasPeopleExtraField, CompareType.Equal, field);
break;
}
DbUtil.Db.SubmitChanges();
return(Redirect("/QueryBuilder/Main/" + qb.QueryId));
}
private void AddMatchAnyThingToGroup(QueryBuilderClause nc)
{
nc = nc.AddNewClause(QueryType.MatchAnything, CompareType.Equal, null);
Db.SubmitChanges();
SelectedId = nc.QueryId;
EditCondition();
}
private void NewCondition(QueryBuilderClause gc, int order)
{
var c = new QueryBuilderClause();
c.ClauseOrder = order;
gc.Clauses.Add(c);
gc.ReorderClauses();
UpdateCondition(c);
}
public IEnumerable <CodeValueItem> StatusFlags()
{
return(from ms in QueryBuilderClause.StatusFlags(DbUtil.Db)
select new CodeValueItem
{
Id = ms.Value,
Code = ms.Tag,
Value = ms.Text
});
}
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;
}
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();
}
}
QueryId = Qb.QueryId;
}
public ActionResult NthTimeAttenders(int id)
{
var name = "VisitNumber-" + id;
var qb = DbUtil.Db.QueryBuilderClauses.FirstOrDefault(c => c.IsPublic && c.Description == name && c.SavedBy == "public");
if (qb == null)
{
qb = DbUtil.Db.QueryBuilderScratchPad();
qb.CleanSlate(DbUtil.Db);
var comp = CompareType.Equal;
QueryBuilderClause clause = null;
switch (id)
{
case 1:
clause = qb.AddNewClause(QueryType.RecentVisitNumber, comp, "1,T");
clause.Quarters = "1";
clause.Days = 7;
break;
case 2:
clause = qb.AddNewClause(QueryType.RecentVisitNumber, comp, "1,T");
clause.Quarters = "2";
clause.Days = 7;
clause = qb.AddNewClause(QueryType.RecentVisitNumber, comp, "0,F");
clause.Quarters = "1";
clause.Days = 7;
break;
case 3:
clause = qb.AddNewClause(QueryType.RecentVisitNumber, comp, "1,T");
clause.Quarters = "3";
clause.Days = 7;
clause = qb.AddNewClause(QueryType.RecentVisitNumber, comp, "0,F");
clause.Quarters = "2";
clause.Days = 7;
break;
}
qb = qb.SaveTo(DbUtil.Db, name, "public", true);
}
TempData["autorun"] = true;
return(Redirect("/QueryBuilder/Main/{0}".Fmt(qb.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 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 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.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 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);
}
private void WriteClause(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(qc);
}
w.WriteEndElement();
}
