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