public Expression IsFamilyGiverFunds() { var tf = CodeIds == "1"; var fundcsv = APIContributionSearchModel.GetCustomFundSetList(db, Quarters); if (fundcsv == null) { return(AlwaysFalse()); } var fundlist = string.Join(",", fundcsv); var td = Util.Now; var fd = td.AddDays(Days == 0 ? -365 : -Days).Date; Tag tag = null; if (op == CompareType.Equal ^ tf) { var q = db.FamilyGiverFunds(fd, td, fundlist).Where(vv => vv.FamGive == false); tag = db.PopulateTemporaryTag(q.Select(pp => pp.PeopleId)); } else { var q = db.FamilyGiverFunds(fd, td, fundlist).Where(vv => vv.FamGive == true); 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); }
public IEnumerable <RangeInfo> GetTotalsByFundRange() { var fundids = APIContributionSearchModel.GetCustomFundSetList(FundSet).JoinInts(","); var list = (from r in DbUtil.Db.GetContributionsRange(Dt1, Dt2, CampusId, false, true, Pledged, FundId, fundids) orderby r.Range select r).ToList(); RangeTotal = new RangeInfo { Total = list.Sum(vv => vv.Total ?? 0), Count = list.Sum(vv => vv.Count ?? 0), DonorCount = list.Sum(vv => vv.DonorCount ?? 0), }; var list2 = from r in list select new RangeInfo { RangeId = r.Range, Total = r.Total ?? 0, Count = r.Count ?? 0, DonorCount = r.DonorCount ?? 0, PctCount = (r.Count ?? 0) / Convert.ToDecimal(RangeTotal.Count) * 100, PctTotal = (r.Total ?? 0) / RangeTotal.Total * 100, }; return(list2); }
public DynamicParameters GetDynamicParameters() { var p = new DynamicParameters(); p.Add("@StartDate", Dt1); p.Add("@EndDate", Dt2); p.Add("@CampusId", CampusId); p.Add("@Online", Online); p.Add("@TaxNonTax", TaxDedNonTax); p.Add("@IncludeUnclosedBundles", IncUnclosedBundles); var fundset = APIContributionSearchModel.GetCustomFundSetList(FundSet).JoinInts(","); p.Add("@FundSet", fundset); if (FilterByActiveTag) { var tagid = DbUtil.Db.TagCurrent().Id; p.Add("@ActiveTagFilter", tagid); } else { p.Add("@ActiveTagFilter"); } return(p); }
public IEnumerable <GetTotalContributionsRange> TotalsByRange() { var customFundIds = APIContributionSearchModel.GetCustomFundSetList(DbUtil.Db, FundSet); var authorizedFundIds = DbUtil.Db.ContributionFunds.ScopedByRoleMembership(DbUtil.Db).Select(f => f.FundId).ToList(); string fundIds = string.Empty; if (customFundIds?.Count > 0) { fundIds = authorizedFundIds.Where(f => customFundIds.Contains(f)).JoinInts(","); } else { fundIds = authorizedFundIds.JoinInts(","); } var list = (from r in DbUtil.Db.GetTotalContributionsRange(Dt1, Dt2, CampusId, NonTaxDeductible ? (bool?)true : false, IncUnclosedBundles, fundIds) orderby r.Range select r).ToList(); RangeTotal = new GetTotalContributionsRange { Count = list.Sum(t => t.Count), Total = list.Sum(t => t.Total), }; return(list); }
public EpplusResult ToExcel(string type) { bool?nontaxdeductible = null; // both switch (TaxDedNonTax) { case "TaxDed": nontaxdeductible = false; break; case "NonTaxDed": nontaxdeductible = true; break; } var tagid = FilterByActiveTag ? DbUtil.Db.TagCurrent()?.Id : (int?)null; //var fundids = APIContributionSearchModel.GetCustomFundSetList(DbUtil.Db. FundSet); var customFundIds = APIContributionSearchModel.GetCustomFundSetList(DbUtil.Db, FundSet); var authorizedFundIds = DbUtil.Db.ContributionFunds.ScopedByRoleMembership().Select(f => f.FundId).ToList(); string fundIds = string.Empty; if (customFundIds?.Count > 0) { fundIds = authorizedFundIds.Where(f => customFundIds.Contains(f)).JoinInts(","); } else { fundIds = authorizedFundIds.JoinInts(","); } switch (type) { case "ledgerincome": var cd = new CommandDefinition("dbo.LedgerIncomeExport", new { fd = Dt1, td = Dt2, campusid, nontaxded = nontaxdeductible, includeunclosed = IncUnclosedBundles }, commandType: CommandType.StoredProcedure); return(DbUtil.Db.Connection.ExecuteReader(cd).ToExcel("LedgerIncome.xlsx")); case "donorfundtotals": return(ExportPeople.ExcelDonorFundTotals(Dt1, Dt2, fundid, campusid, false, nontaxdeductible, IncUnclosedBundles, tagid, fundIds) .ToExcel("DonorFundTotals.xlsx")); case "donortotals": return(ExportPeople.ExcelDonorTotals(Dt1, Dt2, campusid, false, nontaxdeductible, IncUnclosedBundles, tagid, fundIds) .ToExcel("DonorTotals.xlsx")); case "donordetails": return(ExportPeople.DonorDetails(Dt1, Dt2, fundid, campusid, false, nontaxdeductible, IncUnclosedBundles, tagid, fundIds) .ToExcel("DonorDetails.xlsx")); } return(null); }
public ActionResult FundList(TotalsByFundModel m) { return(Content($@" <pre> {string.Join(",", APIContributionSearchModel.GetCustomFundSetList(DbUtil.Db, m.FundSet))} </pre> ")); }
public IEnumerable <AgeRangeInfo> GetTotalsByFundAgeRange() { var model = new BundleModel(); var fundids = APIContributionSearchModel.GetCustomFundSetList(DbUtil.Db, FundSet).JoinInts(","); var ageRangeInfos = model.TotalsByFundAgeRange(0, Dt1.GetValueOrDefault(), Dt2.GetValueOrDefault(), string.Empty, CampusId, fundids); RangeTotal = model.RangeTotal; return(ageRangeInfos); }
public static StatementSpecification GetStatementSpecification(string name) { var standardheader = DbUtil.Db.ContentHtml("StatementHeader", Resource1.ContributionStatementHeader); var standardnotice = DbUtil.Db.ContentHtml("StatementNotice", Resource1.ContributionStatementNotice); if (name == null || name == "all") { return(new StatementSpecification() { Description = "All Statements", Notice = standardnotice, Header = standardheader, Funds = null }); } if (name == "Standard Statements") { var funds = APIContributionSearchModel.GetCustomFundSetList(name); return(new StatementSpecification() { Description = "Standard Statements", Notice = standardnotice, Header = standardheader, Funds = funds }); } var xd = XDocument.Parse(Util.PickFirst(DbUtil.Db.ContentOfTypeText("CustomStatements"), "<CustomStatement/>")); var ele = xd.XPathSelectElement($"//Statement[@description='{name}']"); if (ele == null) { return(null); } var desc = ele.Attribute("description")?.Value; var cs = new StatementSpecification(); cs.Description = desc; var headerele = ele.Element("Header"); cs.Header = headerele != null ? string.Concat(headerele.Nodes().Select(x => x.ToString()).ToArray()) : standardheader; var noticeele = ele.Element("Notice"); cs.Notice = noticeele != null ? string.Concat(noticeele.Nodes().Select(x => x.ToString()).ToArray()) : standardnotice; cs.Funds = APIContributionSearchModel.GetCustomFundSetList(desc); return(cs); }
public IEnumerable <GetTotalContributionsRange> TotalsByRange() { var fundids = APIContributionSearchModel.GetCustomFundSetList(FundSet).JoinInts(","); var list = (from r in DbUtil.Db.GetTotalContributionsRange(Dt1, Dt2, CampusId, NonTaxDeductible ? (bool?)null : false, IncUnclosedBundles, fundids) orderby r.Range select r).ToList(); RangeTotal = new GetTotalContributionsRange { Count = list.Sum(t => t.Count), Total = list.Sum(t => t.Total), }; return(list); }
public EpplusResult ToExcel(string type) { bool?nontaxdeductible = null; // both switch (TaxDedNonTax) { case "TaxDed": nontaxdeductible = false; break; case "NonTaxDed": nontaxdeductible = true; break; } var tagid = FilterByActiveTag ? DbUtil.Db.TagCurrent()?.Id : (int?)null; var fundids = APIContributionSearchModel.GetCustomFundSetList(FundSet); var funds = fundids.JoinInts(","); switch (type) { case "ledgerincome": var cd = new CommandDefinition("dbo.LedgerIncomeExport", new { fd = Dt1, td = Dt2, campusid, nontaxded = nontaxdeductible, includeunclosed = IncUnclosedBundles }, commandType: CommandType.StoredProcedure); return(DbUtil.Db.Connection.ExecuteReader(cd).ToExcel("LedgerIncome.xlsx")); case "donorfundtotals": return(ExportPeople.ExcelDonorFundTotals(Dt1, Dt2, fundid, campusid, false, nontaxdeductible, IncUnclosedBundles, tagid, funds) .ToExcel("DonorFundTotals.xlsx")); case "donortotals": return(ExportPeople.ExcelDonorTotals(Dt1, Dt2, campusid, false, nontaxdeductible, IncUnclosedBundles, tagid, funds) .ToExcel("DonorTotals.xlsx")); case "donordetails": return(ExportPeople.DonorDetails(Dt1, Dt2, fundid, campusid, false, nontaxdeductible, IncUnclosedBundles, tagid, funds) .ToExcel("DonorDetails.xlsx")); } return(null); }
public IEnumerable <AgeRangeInfo> GetTotalsByFundAgeRange() { var model = new BundleModel(); var customFundIds = APIContributionSearchModel.GetCustomFundSetList(DbUtil.Db, FundSet); var authorizedFundIds = DbUtil.Db.ContributionFunds.ScopedByRoleMembership(DbUtil.Db).Select(f => f.FundId).ToList(); string fundIds = string.Empty; if (customFundIds?.Count > 0) { fundIds = authorizedFundIds.Where(f => customFundIds.Contains(f)).JoinInts(","); } else { fundIds = authorizedFundIds.JoinInts(","); } var ageRangeInfos = model.TotalsByFundAgeRange(0, Dt1.GetValueOrDefault(), Dt2.GetValueOrDefault(), string.Empty, CampusId, fundIds); RangeTotal = model.RangeTotal; return(ageRangeInfos); }
public IEnumerable <RangeInfo> GetTotalsByFundRange() { var customFundIds = APIContributionSearchModel.GetCustomFundSetList(DbUtil.Db, FundSet); var authorizedFundIds = DbUtil.Db.ContributionFunds.ScopedByRoleMembership().Select(f => f.FundId).ToList(); string fundIds = string.Empty; if (customFundIds?.Count > 0) { fundIds = authorizedFundIds.Where(f => customFundIds.Contains(f)).JoinInts(","); } else { fundIds = authorizedFundIds.JoinInts(","); } var list = (from r in DbUtil.Db.GetContributionsRange(Dt1, Dt2, CampusId, false, true, Pledged, FundId, fundIds) orderby r.Range select r).ToList(); RangeTotal = new RangeInfo { Total = list.Sum(vv => vv.Total ?? 0), Count = list.Sum(vv => vv.Count ?? 0), DonorCount = list.Sum(vv => vv.DonorCount ?? 0), }; var list2 = from r in list select new RangeInfo { RangeId = r.Range, Total = r.Total ?? 0, Count = r.Count ?? 0, DonorCount = r.DonorCount ?? 0, PctCount = (r.Count ?? 0) / Convert.ToDecimal(RangeTotal.Count) * 100, PctTotal = (r.Total ?? 0) / RangeTotal.Total * 100, }; return(list2); }
private DynamicParameters DonorTotalSummaryParameters(DonorTotalSummaryOptionsModel m, bool useMedianMin = false) { var p = new DynamicParameters(); p.Add("@enddt", m.StartDate); p.Add("@years", m.NumberOfYears); if (useMedianMin) { p.Add("@medianMin", m.MinimumMedianTotal); } p.Add("@fund", m.Fund.Value.ToInt()); p.Add("@campus", m.Campus.Value.ToInt()); if (m?.FundSet != null) { var fundset = APIContributionSearchModel.GetCustomFundSetList(m.FundSet.Value).JoinInts(","); p.Add("@fundids", fundset); } else { p.Add("@fundids", null); } return(p); }
public Expression IsRecentGiverFunds() { if (!db.FromBatch) { if (db.CurrentUser == null || db.CurrentUser.Roles.All(rr => rr != "Finance")) { return(AlwaysFalse()); } } var tf = CodeIds == "1"; var fundcsv = APIContributionSearchModel.GetCustomFundSetList(db, Quarters); if (fundcsv == null) { return(AlwaysFalse()); } //throw new Exception($"fundset '{Quarters}' was not found"); var fundlist = string.Join(",", fundcsv); var q = db.RecentGiverFunds(Days, fundlist).Select(v => v.PeopleId.Value); var tag = db.PopulateTemporaryTag(q); Expression <Func <Person, bool> > pred = null; 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); }
public ActionResult FundList(TotalsByFundModel model) { return(SimpleContent($@"<pre>{string.Join(",", APIContributionSearchModel.GetCustomFundSetList(CurrentDatabase, model.FundSet))}</pre>")); }