internal static Expression ContributionChange(
ParameterExpression parm, CMSDataContext Db,
DateTime? dt1,
DateTime? dt2,
CompareType op,
double pct)
{
if (Db.CurrentUser == null || Db.CurrentUser.Roles.All(rr => rr != "Finance"))
return AlwaysFalse(parm);
var q = Db.GivingCurrentPercentOfFormer(dt1, dt2,
op == CompareType.Greater ? ">" :
op == CompareType.GreaterEqual ? ">=" :
op == CompareType.Less ? "<" :
op == CompareType.LessEqual ? "<=" :
op == CompareType.Equal ? "=" : "<>", pct);
var tag = Db.PopulateTemporaryTag(q.Select(pp => pp.Pid));
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
internal static Expression SavedQuery(ParameterExpression parm,
CMSDataContext Db,
string QueryIdDesc,
CompareType op,
bool tf)
{
var a = QueryIdDesc.SplitStr(":", 2);
var qid = a[0].ToInt();
var savedquery = Db.QueryBuilderClauses.SingleOrDefault(q =>
q.QueryId == qid);
if (savedquery == null)
return AlwaysFalse(parm);
var q2 = Db.PeopleQuery(qid).Select(pp => pp.PeopleId);
var tag = Db.PopulateTemporaryTag(q2);
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
internal static Expression ContributionAmount2(
ParameterExpression parm, CMSDataContext Db,
DateTime? start,
DateTime? end,
int fund,
CompareType op,
decimal amt)
{
if (Db.CurrentUser == null || Db.CurrentUser.Roles.All(rr => rr != "Finance"))
return AlwaysFalse(parm);
IQueryable<int> q = null;
switch (op)
{
case CompareType.GreaterEqual:
q = from c in Db.Contributions2(start, end, 0, false, false, true)
where fund == 0 || c.FundId == fund
group c by c.CreditGiverId into g
where g.Sum(cc => cc.Amount) >= amt
select g.Key ?? 0;
break;
case CompareType.Greater:
q = from c in Db.Contributions2(start, end, 0, false, false, true)
where fund == 0 || c.FundId == fund
group c by c.CreditGiverId into g
where g.Sum(cc => cc.Amount) > amt
select g.Key ?? 0;
break;
case CompareType.LessEqual:
q = from c in Db.Contributions2(start, end, 0, false, false, true)
where fund == 0 || c.FundId == fund
where c.Amount > 0
group c by c.CreditGiverId into g
where g.Sum(cc => cc.Amount) <= amt
select g.Key ?? 0;
break;
case CompareType.Less:
q = from c in Db.Contributions2(start, end, 0, false, false, true)
where fund == 0 || c.FundId == fund
where c.Amount > 0
group c by c.CreditGiverId into g
where g.Sum(cc => cc.Amount) < amt
select g.Key ?? 0;
break;
case CompareType.Equal:
q = from c in Db.Contributions2(start, end, 0, false, false, true)
where fund == 0 || c.FundId == fund
where c.Amount > 0
group c by c.CreditGiverId into g
where g.Sum(cc => cc.Amount) == amt
select g.Key ?? 0;
break;
case CompareType.NotEqual:
q = from c in Db.Contributions2(start, end, 0, false, false, true)
where fund == 0 || c.FundId == fund
where c.Amount > 0
group c by c.CreditGiverId into g
where g.Sum(cc => cc.Amount) != amt
select g.Key ?? 0;
break;
}
var tag = Db.PopulateTemporaryTag(q);
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
internal static Expression RecentPledgeCount(
ParameterExpression parm, CMSDataContext Db,
int days,
int fund,
CompareType op,
int cnt)
{
if (Db.CurrentUser == null || Db.CurrentUser.Roles.All(rr => rr != "Finance"))
return AlwaysFalse(parm);
var now = DateTime.Now;
var dt = now.AddDays(-days);
IQueryable<int> q = null;
switch (op)
{
case CompareType.Greater:
q = from c in Db.Contributions2(dt, now, 0, true, false, true)
where fund == 0 || c.FundId == fund
where c.PledgeAmount > 0
group c by c.CreditGiverId into g
where g.Count() > cnt
select g.Key ?? 0;
break;
case CompareType.GreaterEqual:
q = from c in Db.Contributions2(dt, now, 0, true, false, true)
where fund == 0 || c.FundId == fund
where c.PledgeAmount > 0
group c by c.CreditGiverId into g
where g.Count() >= cnt
select g.Key ?? 0;
break;
case CompareType.Less:
q = from c in Db.Contributions2(dt, now, 0, true, false, true)
where fund == 0 || c.FundId == fund
where c.PledgeAmount > 0
group c by c.CreditGiverId into g
where g.Count() < cnt
select g.Key ?? 0;
break;
case CompareType.LessEqual:
q = from c in Db.Contributions2(dt, now, 0, true, false, true)
where fund == 0 || c.FundId == fund
where c.PledgeAmount > 0
group c by c.CreditGiverId into g
where g.Count() <= cnt
select g.Key ?? 0;
break;
case CompareType.Equal:
if (cnt == 0) // special case, use different approach
{
q = from pid in Db.Pledges0(dt, now, fund, 0)
select pid.PeopleId;
Expression<Func<Person, bool>> pred0 = p => q.Contains(p.PeopleId);
Expression expr0 = Expression.Invoke(pred0, parm);
return expr0;
}
q = from c in Db.Contributions2(dt, now, 0, true, false, true)
where fund == 0 || c.FundId == fund
where c.PledgeAmount > 0
group c by c.CreditGiverId into g
where g.Count() == cnt
select g.Key ?? 0;
break;
case CompareType.NotEqual:
q = from c in Db.Contributions2(dt, now, 0, true, false, true)
where fund == 0 || c.FundId == fund
where c.PledgeAmount > 0
group c by c.CreditGiverId into g
where g.Count() != cnt
select g.Key ?? 0;
break;
}
var tag = Db.PopulateTemporaryTag(q);
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
internal static Expression RecentNewVisitCount(
ParameterExpression parm,
CMSDataContext Db,
int? progid,
int? divid,
int? org,
int orgtype,
string days0,
int days,
CompareType op,
int cnt)
{
var dt1 = DateTime.Today.AddDays(-(days0.ToInt2() ?? 365));
var dt2 = DateTime.Today.AddDays(-days);
IQueryable<int> q = null;
switch (op)
{
case CompareType.Greater:
q = from p in Db.People
let g = from a in p.Attends
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
where a.AttendanceFlag
where a.MeetingDate >= dt1
select a
where !g.Any(aa => aa.MeetingDate < dt2)
where g.Count(aa => aa.MeetingDate > dt2) > cnt
select p.PeopleId;
break;
case CompareType.GreaterEqual:
q = from p in Db.People
let g = from a in p.Attends
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
where a.AttendanceFlag
where a.MeetingDate >= dt1
select a
where !g.Any(aa => aa.MeetingDate < dt2)
where g.Count(aa => aa.MeetingDate > dt2) >= cnt
select p.PeopleId;
break;
case CompareType.Less:
q = from p in Db.People
let g = from a in p.Attends
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
where a.AttendanceFlag
where a.MeetingDate >= dt1
select a
where !g.Any(aa => aa.MeetingDate < dt2)
where g.Count(aa => aa.MeetingDate > dt2) < cnt
select p.PeopleId;
break;
case CompareType.LessEqual:
q = from p in Db.People
let g = from a in p.Attends
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
where a.AttendanceFlag
where a.MeetingDate >= dt1
select a
where !g.Any(aa => aa.MeetingDate < dt2)
where g.Count(aa => aa.MeetingDate > dt2) <= cnt
select p.PeopleId;
break;
case CompareType.Equal:
q = from p in Db.People
let g = from a in p.Attends
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
where a.AttendanceFlag
where a.MeetingDate >= dt1
select a
where !g.Any(aa => aa.MeetingDate < dt2)
where g.Count(aa => aa.MeetingDate > dt2) == cnt
select p.PeopleId;
break;
case CompareType.NotEqual:
q = from p in Db.People
let g = from a in p.Attends
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
where a.AttendanceFlag
where a.MeetingDate >= dt1
select a
where !g.Any(aa => aa.MeetingDate < dt2)
where g.Count(aa => aa.MeetingDate > dt2) != cnt
select p.PeopleId;
break;
}
var tag = Db.PopulateTemporaryTag(q);
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
internal static Expression RecentGivingAsPctOfPrevious(
ParameterExpression parm, CMSDataContext Db,
int Days,
CompareType op,
double pct)
{
var dt1 = DateTime.Today.AddDays(-Days * 2);
var dt2 = DateTime.Today.AddDays(-Days);
var q = Db.GivingCurrentPercentOfFormer(dt1, dt2, op == CompareType.Greater ? ">" : "<=", pct);
var tag = Db.PopulateTemporaryTag(q.Select(pp => pp.Pid));
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
internal static Expression RecentFirstTimeGiver(
ParameterExpression parm, CMSDataContext Db,
int days,
int fund,
CompareType op,
bool tf)
{
if (Db.CurrentUser == null || Db.CurrentUser.Roles.All(rr => rr != "Finance"))
return AlwaysFalse(parm);
var q = from f in Db.FirstTimeGivers(days, fund)
select f.PeopleId;
var tag = Db.PopulateTemporaryTag(q);
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
internal static Expression AttendPctHistory(
ParameterExpression parm, CMSDataContext Db,
int? progid,
int? divid,
int? org,
DateTime? start,
DateTime? end,
CompareType op,
double pct)
{
if (!end.HasValue)
end = start.Value;
end = end.Value.AddDays(1);
// note: this only works for members because visitors do not have att%
var now = DateTime.Now;
var q = from p in Db.People
let m = from et in p.EnrollmentTransactions
where et.TransactionTypeId <= 3 // things that start a change
where et.TransactionStatus == false
where et.TransactionDate < end // transaction starts <= looked for end
where (et.Pending ?? false) == false
where (et.NextTranChangeDate ?? now) >= start // transaction ends >= looked for start
where org == 0 || et.OrganizationId == org
where divid == 0 || et.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || et.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
select et
where m.Any()
select p;
IQueryable<int> q2 = null;
switch (op)
{
case CompareType.Greater:
q2 = from p in q
let g = from a in p.Attends
where a.MeetingDate >= start
where a.MeetingDate < end
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Meeting.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Meeting.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
select a
let n = g.Count(aa => aa.EffAttendFlag == true)
let d = g.Count(aa => aa.EffAttendFlag != null)
where (d == 0 ? 0d : n * 100.0 / d) > pct
select p.PeopleId;
break;
case CompareType.GreaterEqual:
q2 = from p in q
let g = from a in p.Attends
where a.MeetingDate >= start
where a.MeetingDate < end
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Meeting.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Meeting.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
select a
let n = g.Count(aa => aa.EffAttendFlag == true)
let d = g.Count(aa => aa.EffAttendFlag != null)
where (d == 0 ? 0d : n * 100.0 / d) >= pct
select p.PeopleId;
break;
case CompareType.Less:
q2 = from p in q
let g = from a in p.Attends
where a.MeetingDate >= start
where a.MeetingDate < end
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Meeting.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Meeting.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
select a
let n = g.Count(aa => aa.EffAttendFlag == true)
let d = g.Count(aa => aa.EffAttendFlag != null)
where (d == 0 ? 0d : n * 100.0 / d) < pct
select p.PeopleId;
break;
case CompareType.LessEqual:
q2 = from p in q
let g = from a in p.Attends
where a.MeetingDate >= start
where a.MeetingDate < end
where org == 0 || a.Meeting.OrganizationId == org
where divid == 0 || a.Meeting.Organization.DivOrgs.Any(dg => dg.DivId == divid)
where progid == 0 || a.Meeting.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == progid))
select a
let n = g.Count(aa => aa.EffAttendFlag == true)
let d = g.Count(aa => aa.EffAttendFlag != null)
where (d == 0 ? 0d : n * 100.0 / d) <= pct
select p.PeopleId;
break;
case CompareType.NotEqual:
case CompareType.Equal:
return AlwaysFalse(parm);
}
var tag = Db.PopulateTemporaryTag(q2);
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}