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); }