internal Expression NeedAttendance()
{
var mindt = Util.Now.AddDays(-Days).Date;
var dow = Quarters.ToInt2();
Expression <Func <Person, bool> > pred = p => (
from m in p.OrganizationMembers
where m.Organization.OrganizationStatusId == Codes.OrgStatusCode.Active
let sc = m.Organization.OrgSchedules.FirstOrDefault()
where sc != null
where !dow.HasValue || sc.SchedDay == dow
where CodeIntIds.Contains(m.MemberTypeId)
where Organization == 0 || m.OrganizationId == Organization
where Division == 0 || m.Organization.DivOrgs.Any(dg => dg.DivId == Division)
where Program == 0 || m.Organization.DivOrgs.Any(dg => dg.Division.ProgDivs.Any(pg => pg.ProgId == Program))
where !m.Organization.Meetings.Any(mm => mm.MeetingDate > mindt && (mm.HeadCount > 0 || mm.Attends.Any(aa => aa.AttendanceFlag)))
select m
).Any();
Expression expr = Expression.Invoke(pred, parm); // substitute parm for p
if (op == CompareType.NotEqual || op == CompareType.NotOneOf)
{
expr = Expression.Not(expr);
}
return(expr);
}
internal Expression HasManagedGiving()
{
if (!db.FromBatch)
{
if (db.CurrentUser == null || db.CurrentUser.Roles.All(rr => rr != "Finance"))
{
return(AlwaysFalse());
}
}
var tf = CodeIds == "1";
var fundid = Quarters.AllDigits() ? Quarters.ToInt2() : db.Setting(Quarters, "").ToInt2();
Expression <Func <Person, bool> > pred = p => (from e in p.RecurringAmounts
where e.Amt > 0
where fundid == null || fundid == e.FundId
select e).Any();
Expression expr = Expression.Convert(Expression.Invoke(pred, parm), typeof(bool));
if (op == CompareType.Equal ^ tf)
{
expr = Expression.Not(expr);
}
return(expr);
}
internal Expression RecentNewVisitCount()
{
var days0 = Quarters.ToInt2();
var cnt = TextValue.ToInt();
var q = db.RecentNewVisitCount(Program, Division, Organization, OrgType ?? 0, days0, Days);
switch (op)
{
case CompareType.Greater:
q = q.Where(cc => cc.Cnt > cnt); break;
case CompareType.GreaterEqual:
q = q.Where(cc => cc.Cnt >= cnt); break;
case CompareType.Less:
q = q.Where(cc => cc.Cnt < cnt); break;
case CompareType.LessEqual:
q = q.Where(cc => cc.Cnt <= cnt); break;
case CompareType.Equal:
q = q.Where(cc => cc.Cnt == cnt); break;
case CompareType.NotEqual:
q = q.Where(cc => cc.Cnt != cnt); break;
}
Expression <Func <Person, bool> > pred = p => q.Select(c => c.PeopleId).Contains(p.PeopleId);
Expression expr = Expression.Invoke(pred, parm);
return(expr);
}
internal Expression GivingChange()
{
var days = Quarters.ToInt2() ?? 365;
var pct = TextValue.ToDecimal() ?? 0;
var q = db.GivingChange(days);
switch (op)
{
case CompareType.Greater:
q = q.Where(vv => vv.PctChange > pct);
break;
case CompareType.GreaterEqual:
q = q.Where(vv => vv.PctChange >= pct);
break;
case CompareType.Less:
q = q.Where(vv => vv.PctChange < pct);
break;
case CompareType.LessEqual:
q = q.Where(vv => vv.PctChange <= pct);
break;
case CompareType.Equal:
q = q.Where(vv => vv.PctChange == pct);
break;
case CompareType.NotEqual:
q = q.Where(vv => vv.PctChange != pct);
break;
}
var tag = db.PopulateTemporaryTag(q.Select(pp => pp.PeopleId));
Expression<Func<Person, bool>> pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
private Expression PledgeAmountBothJoint(DateTime? startdt, DateTime? enddt)
{
if (!db.FromBatch)
if (db.CurrentUser == null || db.CurrentUser.Roles.All(rr => rr != "Finance"))
return AlwaysFalse();
var amt = TextValue.ToDecimal() ?? 0;
var fund = Quarters.ToInt2();
IQueryable<int> q = null;
switch (op)
{
case CompareType.Greater:
q = from c in db.GetPledgedTotalsBothIfJoint(startdt, enddt, fund)
where c.PledgeAmount > amt
select c.PeopleId.Value;
break;
case CompareType.GreaterEqual:
q = from c in db.GetPledgedTotalsBothIfJoint(startdt, enddt, fund)
where c.PledgeAmount.Value >= amt
select c.PeopleId.Value;
break;
case CompareType.Less:
q = from c in db.GetPledgedTotalsBothIfJoint(startdt, enddt, fund)
where c.PledgeAmount.Value > 0
where c.PledgeAmount.Value <= amt
select c.PeopleId.Value;
break;
case CompareType.LessEqual:
q = from c in db.GetPledgedTotalsBothIfJoint(startdt, enddt, fund)
where c.PledgeAmount.Value > 0
where c.PledgeAmount.Value <= amt
select c.PeopleId.Value;
break;
case CompareType.Equal:
if (amt == 0)
q = from pid in db.Pledges0(startdt, enddt, fund, 0)
select pid.PeopleId;
else
q = from c in db.GetPledgedTotalsBothIfJoint(startdt, enddt, fund)
where c.PledgeAmount.Value > 0
where c.PledgeAmount.Value == amt
select c.PeopleId.Value;
break;
case CompareType.NotEqual:
q = from c in db.GetPledgedTotalsBothIfJoint(startdt, enddt, fund)
where c.PledgeAmount.Value > 0
where c.PledgeAmount.Value != amt
select c.PeopleId.Value;
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 Expression RecentGivingAsPctOfPrevious()
{
var days = Quarters.ToInt2() ?? 365;
var dt1 = DateTime.Today.AddDays(-days * 2);
var dt2 = DateTime.Today.AddDays(-days);
var pct = double.Parse(TextValue);
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 Expression IsFamilyPledger()
{
var tf = CodeIds == "1";
var fundid = Quarters.ToInt2();
var td = Util.Now;
var fd = td.AddDays(Days == 0 ? -365 : -Days);
var q = db.FamilyGiver(fd, td, fundid).Where(vv => vv.FamPledge == true);
var tag = db.PopulateTemporaryTag(q.Select(pp => pp.PeopleId));
Expression<Func<Person, bool>> pred = p => op == CompareType.Equal && tf
? db.TagPeople.Where(vv => vv.Id == tag.Id).Select(vv => vv.PeopleId).Contains(p.PeopleId)
: !db.TagPeople.Where(vv => vv.Id == tag.Id).Select(vv => vv.PeopleId).Contains(p.PeopleId);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
internal Expression VisitNumber()
{
var n = Quarters.ToInt2() ?? 1;
var cdt = db.Setting("DbConvertedDate", "1/1/1900").ToDate();
Expression <Func <Person, bool> > pred = null;
switch (op)
{
case CompareType.Greater:
pred = p => p.CreatedDate > cdt &&
p.Attends.Any(aa => aa.SeqNo == n && aa.MeetingDate > DateValue);
break;
case CompareType.Less:
pred = p => p.CreatedDate > cdt &&
p.Attends.Any(aa => aa.SeqNo == n && aa.MeetingDate < DateValue);
break;
case CompareType.GreaterEqual:
pred = p => p.CreatedDate > cdt &&
p.Attends.Any(aa => aa.SeqNo == n && aa.MeetingDate >= DateValue);
break;
case CompareType.LessEqual:
pred = p => p.CreatedDate > cdt &&
p.Attends.Any(aa => aa.SeqNo == n && aa.MeetingDate <= DateValue);
break;
case CompareType.Equal:
pred = p => p.CreatedDate > cdt &&
p.Attends.Any(aa => aa.SeqNo == n && aa.MeetingDate.Date == DateValue);
break;
case CompareType.NotEqual:
case CompareType.IsNull:
pred = p => p.CreatedDate > cdt &&
!p.Attends.Any(aa => aa.SeqNo == n && aa.MeetingDate.Date == DateValue);
break;
case CompareType.IsNotNull:
pred = p => p.CreatedDate > cdt &&
p.Attends.Any(aa => aa.SeqNo == n);
break;
}
Expression expr = Expression.Invoke(pred, parm);
return(expr);
}
internal Expression HasRecentNewAttend()
{
var tf = CodeIds == "1";
var days0 = Quarters.ToInt2();
var tag = db.NewTemporaryTag();
db.TagRecentStartAttend(ProgramInt ?? 0, DivisionInt ?? 0, OrganizationInt, OrgTypeInt ?? 0, days0 ?? 365, Days, tag.Id);
Expression <Func <Person, bool> > pred = p => op == CompareType.Equal && tf
? p.Tags.Any(t => t.Id == tag.Id)
: p.Tags.All(t => t.Id != tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return(expr);
}
internal Expression RecentVisitNumber()
{
var tf = CodeIds == "1";
int n = Quarters.ToInt2() ?? 1;
var dt = DateTime.Now.AddDays(-Days);
var cdt = db.Setting("DbConvertedDate", "1/1/1900").ToDate();
Expression <Func <Person, bool> > pred = p => p.CreatedDate > cdt &&
p.Attends.Any(aa => aa.SeqNo == n && aa.MeetingDate > dt);
Expression expr = Expression.Invoke(pred, parm);
if (!(op == CompareType.Equal && tf))
{
expr = Expression.Not(expr);
}
return(expr);
}
internal Expression RecentVisitNumberOrgs()
{
var tf = CodeIds == "1";
int visitnum = Quarters.ToInt2() ?? 1;
var mindate = Util.Now.AddDays(-Days);
var minpersoncreatedt = db.Setting("DbConvertedDate", "1/1/1900").ToDate();
var orgids = OrganizationInt > 0
? OrganizationInt.ToString()
: db.Connection.Query <int>(
"SELECT OrganizationId FROM dbo.OrgSearch(NULL, @ProgId, @DivId, @TypeId, NULL, NULL, NULL, NULL, NULL, NULL)",
new { ProgId = ProgramInt, DivId = DivisionInt, TypeId = OrgTypeInt }).JoinInts(",");
var sql = @"
;WITH attendnumbers AS (
SELECT a.PeopleId, MeetingDate,
ROW_NUMBER() OVER (PARTITION BY a.PeopleId ORDER BY MeetingDate) AS SeqNo
FROM dbo.Attend a
JOIN dbo.People p ON p.PeopleId = a.PeopleId
WHERE AttendanceFlag = 1
AND p.CreatedDate > @minpersoncreatedt
AND a.OrganizationId IN (SELECT value FROM dbo.SplitInts(@orgids))
)
SELECT
PeopleId
FROM attendnumbers
WHERE SeqNo = @visitnum
AND MeetingDate > @mindate
";
var peopleids = db.Connection.Query <int>(sql, new { minpersoncreatedt, orgids, mindate, visitnum });
var tag = db.PopulateTempTag(peopleids);
Expression <Func <Person, bool> > pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
if (op == CompareType.Equal ^ tf)
{
expr = Expression.Not(expr);
}
return(expr);
}
internal Expression IsFamilyGiver()
{
var tf = CodeIds == "1";
var fundid = Quarters.ToInt2();
var td = Util.Now;
var fd = td.AddDays(Days == 0 ? -365 : -Days);
Tag tag = null;
if (op == CompareType.Equal && tf)
{
var q = db.FamilyGiver(fd, td, fundid).Where(vv => vv.FamGive == true);
tag = db.PopulateTemporaryTag(q.Select(pp => pp.PeopleId));
}
else
{
var q = db.FamilyGiver(fd, td, fundid).Where(vv => vv.FamGive == false);
tag = db.PopulateTemporaryTag(q.Select(pp => pp.PeopleId));
}
Expression<Func<Person, bool>> pred = pred = p => p.Tags.Any(t => t.Id == tag.Id);
Expression expr = Expression.Invoke(pred, parm);
return expr;
}
public Expression IsRecentGiver()
{
if (!db.FromBatch)
{
if (db.CurrentUser == null || db.CurrentUser.Roles.All(rr => rr != "Finance"))
{
return(AlwaysFalse());
}
}
var tf = CodeIds == "1";
var fundid = Quarters.AllDigits() ? Quarters.ToInt2() : db.Setting(Quarters, "").ToInt2();
var q = fundid > 0
? db.RecentGiverFund(Days, fundid).Select(v => v.PeopleId.Value)
: db.RecentGiver(Days).Select(v => v.PeopleId.Value);
var tag = db.PopulateTemporaryTag(q);
Expression <Func <Person, bool> > pred = null;
/*
* if (op == CompareType.Equal ^ tf)
* IS EQUIVALENT TO AND JUST A SIMPLER WAY TO SAY
* if ((op == CompareType.NotEqual && tf == true) || (op == CompareType.Equal && tf == false))
* IN OTHER WORDS (not equal true) IS THE SAME AS (equal false)
*/
if (op == CompareType.Equal ^ tf)
{
pred = p => !db.TagPeople.Where(vv => vv.Id == tag.Id).Select(vv => vv.PeopleId).Contains(p.PeopleId);
}
else
{
pred = p => db.TagPeople.Where(vv => vv.Id == tag.Id).Select(vv => vv.PeopleId).Contains(p.PeopleId);
}
Expression expr = Expression.Invoke(pred, parm);
return(expr);
}
internal Expression RecentPledgeAmount()
{
if (!db.FromBatch)
{
if (db.CurrentUser == null || db.CurrentUser.Roles.All(rr => rr != "Finance"))
{
return(AlwaysFalse());
}
}
var fund = Quarters.AllDigits() ? Quarters.ToInt2() : db.Setting(Quarters, "").ToInt2();
var amt = TextValue.ToDecimal() ?? 0;
var now = Util.Now;
var dt = now.AddDays(-Days).Date;
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
group c by c.CreditGiverId into g
where g.Sum(cc => cc.PledgeAmount) > amt
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
group c by c.CreditGiverId into g
where g.Sum(cc => cc.PledgeAmount) >= amt
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.Amount > 0
group c by c.CreditGiverId into g
where g.Sum(cc => cc.PledgeAmount) < amt
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.Amount > 0
group c by c.CreditGiverId into g
where g.Sum(cc => cc.PledgeAmount) <= amt
select g.Key ?? 0;
break;
case CompareType.Equal:
q = from c in db.Contributions2(dt, now, 0, true, false, true)
where fund == 0 || c.FundId == fund
where c.Amount > 0
group c by c.CreditGiverId into g
where g.Sum(cc => cc.PledgeAmount) == amt
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.Amount > 0
group c by c.CreditGiverId into g
where g.Sum(cc => cc.PledgeAmount) != 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 Expression RecentPledgeCount()
{
if (!db.FromBatch)
{
if (db.CurrentUser == null || db.CurrentUser.Roles.All(rr => rr != "Finance"))
{
return(AlwaysFalse());
}
}
var fund = Quarters.AllDigits() ? Quarters.ToInt2() : db.Setting(Quarters, "").ToInt2();
var cnt = TextValue.ToInt();
var now = Util.Now;
var dt = now.AddDays(-Days).Date;
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);
}
private Expression ContributionAmountBothJoint(DateTime?startdt, DateTime?enddt, int?fundid = null)
{
if (!db.FromBatch)
{
if (db.CurrentUser == null || db.CurrentUser.Roles.All(rr => rr != "Finance"))
{
return(AlwaysFalse());
}
}
var amt = TextValue.ToDecimal() ?? 0;
var fund = fundid ?? (Quarters.AllDigits() ? Quarters.ToInt2() : db.Setting(Quarters, "").ToInt2());
IQueryable <int> q = null;
switch (op)
{
case CompareType.Greater:
q = from c in db.GetContributionTotalsBothIfJoint(startdt, enddt, fund)
where c.Amount > amt
select c.PeopleId.Value;
break;
case CompareType.GreaterEqual:
q = from c in db.GetContributionTotalsBothIfJoint(startdt, enddt, fund)
where c.Amount >= amt
select c.PeopleId.Value;
break;
case CompareType.Less:
q = from c in db.GetContributionTotalsBothIfJoint(StartDate, enddt, fund)
where c.Amount > 0
where c.Amount <= amt
select c.PeopleId.Value;
break;
case CompareType.LessEqual:
q = from c in db.GetContributionTotalsBothIfJoint(startdt, enddt, fund)
where c.Amount > 0
where c.Amount <= amt
select c.PeopleId.Value;
break;
case CompareType.Equal:
if (amt == 0)
{
q = from pid in db.Contributions0(startdt, enddt, fund, 0, false, false, true)
select pid.PeopleId.Value;
}
else
{
q = from c in db.GetContributionTotalsBothIfJoint(startdt, enddt, fund)
where c.Amount > 0
where c.Amount == amt
select c.PeopleId.Value;
}
break;
case CompareType.NotEqual:
q = from c in db.GetContributionTotalsBothIfJoint(startdt, enddt, fund)
where c.Amount > 0
where c.Amount != amt
select c.PeopleId.Value;
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);
}
