public EpplusResult DonorTotalSummary(DonorTotalSummaryOptionsModel model)
{
var excel = new ExcelPackage();
var connection = new SqlConnection(Util.ConnectionString);
var totalSummaryParameters = DonorTotalSummaryParameters(model, useMedianMin: true);
var totalSummary = connection.ExecuteReader("dbo.DonorTotalSummary", totalSummaryParameters, commandType: CommandType.StoredProcedure, commandTimeout: 1200);
excel.AddSheet(totalSummary, "MemberNon");
totalSummary.Close();
var totalSummaryBySizeParameters = DonorTotalSummaryParameters(model);
var totalSummaryBySize = connection.ExecuteReader("dbo.DonorTotalSummaryBySize", totalSummaryBySizeParameters, commandType: CommandType.StoredProcedure, commandTimeout: 1200);
excel.AddSheet(totalSummaryBySize, "BySize");
totalSummaryBySize.Close();
var totalSummaryByAgeParameters = DonorTotalSummaryParameters(model);
var totalSummaryByAge = connection.ExecuteReader("dbo.DonorTotalSummaryByAge", totalSummaryByAgeParameters, commandType: CommandType.StoredProcedure, commandTimeout: 1200);
excel.AddSheet(totalSummaryByAge, "ByAge");
totalSummaryByAge.Close();
return(CreateExcelResult(excel, "DonorTotalSummary.xlsx"));
}
private DynamicParameters DonorTotalSummaryParameters(DonorTotalSummaryOptionsModel model, bool useMedianMin = false)
{
var queryParameters = new DynamicParameters();
queryParameters.Add("@enddt", model.StartDate);
queryParameters.Add("@years", model.NumberOfYears);
queryParameters.Add("@fund", model.Fund.Value.ToInt());
queryParameters.Add("@campus", model.Campus.Value.ToInt());
if (useMedianMin)
{
queryParameters.Add("@medianMin", model.MinimumMedianTotal);
}
if (model?.FundSet != null) // TODO: seems like a redundant null check, if model was null, it would have errored well before this point
{
var fundset = APIContributionSearchModel.GetCustomStatementsList(CurrentDatabase, model.FundSet.Value).JoinInts(",");
queryParameters.Add("@fundids", fundset == "" ? null : fundset);
}
else
{
var authorizedFunds = CurrentDatabase.ContributionFunds.ScopedByRoleMembership(CurrentDatabase).Select(f => f.FundId).ToList();
var authorizedFundsCsv = string.Join(",", authorizedFunds);
queryParameters.Add("@fundids", authorizedFundsCsv);
}
return(queryParameters);
}
public ActionResult DonorTotalSummaryOptions()
{
var m = new DonorTotalSummaryOptionsModel
{
StartDate = DateTime.Today,
NumberOfYears = 5,
MinimumMedianTotal = 100,
Campus = new CodeInfo("Campus0"),
Fund = new CodeInfo("Fund"),
};
return(View(m));
}
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());
return(p);
}
public ActionResult ChaiDonorsReport()
{
//Re-using a model, projected that this report will use most fields in this model, in next rollout.
var model = new DonorTotalSummaryOptionsModel
{
StartDate = DateTime.Today,
NumberOfYears = 5,
MinimumMedianTotal = 100,
Campus = CreateCodeInfoForField("Campus0"),
Fund = CreateCodeInfoForField("Fund"),
};
return(View(model));
}
public EpplusResult ChaiDonorsReportDownload(DonorTotalSummaryOptionsModel m)
{
var p = new DynamicParameters();
p.Add("@fund", m.Fund.Value.ToInt());
var ep = new ExcelPackage();
var cn = new SqlConnection(Util.ConnectionString);
var rd = cn.ExecuteReader("dbo.CHAIDonationsReport", p, commandType: CommandType.StoredProcedure, commandTimeout: 1200);
ep.AddSheet(rd, "CHAIDonations");
return(new EpplusResult(ep, "CHAIDonationsReport.xlsx"));
}
public EpplusResult DonorTotalSummary(DonorTotalSummaryOptionsModel m)
{
var ep = new ExcelPackage();
var cn = new SqlConnection(Util.ConnectionString);
var rd = cn.ExecuteReader("dbo.DonorTotalSummary", DonorTotalSummaryParameters(m, useMedianMin: true), commandType: CommandType.StoredProcedure, commandTimeout: 1200);
ep.AddSheet(rd, "MemberNon");
rd = cn.ExecuteReader("dbo.DonorTotalSummaryBySize", DonorTotalSummaryParameters(m), commandType: CommandType.StoredProcedure, commandTimeout: 1200);
ep.AddSheet(rd, "BySize");
rd = cn.ExecuteReader("dbo.DonorTotalSummaryByAge", DonorTotalSummaryParameters(m), commandType: CommandType.StoredProcedure, commandTimeout: 1200);
ep.AddSheet(rd, "ByAge");
return(new EpplusResult(ep, "DonorTotalSummary.xlsx"));
}
public ActionResult DonorTotalSummaryOptions()
{
var m = new DonorTotalSummaryOptionsModel
{
StartDate = DateTime.Today,
NumberOfYears = 5,
MinimumMedianTotal = 100,
Campus = new CodeInfo("Campus0"),
Fund = new CodeInfo("Fund"),
};
var customfunds = ContributionStatements.CustomFundSetSelectList();
if (customfunds != null)
{
m.FundSet = new CodeInfo(null, customfunds);
}
return(View(m));
}
public ActionResult DonorTotalSummaryOptions()
{
var model = new DonorTotalSummaryOptionsModel
{
StartDate = DateTime.Today,
NumberOfYears = 5,
MinimumMedianTotal = 100,
Campus = CreateCodeInfoForField("Campus0"),
Fund = CreateCodeInfoForField("Fund")
};
var customFundsList = ContributionStatements.CustomFundSetSelectList(CurrentDatabase);
if (customFundsList != null)
{
model.FundSet = new CodeInfo(null, customFundsList);
}
return(View(model));
}
public EpplusResult ChaiDonorsReportDownload(DonorTotalSummaryOptionsModel model)
{
var queryParameters = new DynamicParameters();
queryParameters.Add("@fund", model.Fund.Value.ToInt());
var authorizedFunds = CurrentDatabase.ContributionFunds.ScopedByRoleMembership(CurrentDatabase).Select(f => f.FundId).ToList();
var authorizedFundsCsv = string.Join(",", authorizedFunds);
queryParameters.Add("@authorizedFundIds", authorizedFundsCsv);
var excel = new ExcelPackage();
var connection = new SqlConnection(Util.ConnectionString);
var reader = connection.ExecuteReader("dbo.CHAIDonationsReport2", queryParameters, commandType: CommandType.StoredProcedure, commandTimeout: 1200);
excel.AddSheet(reader, "CHAIDonations");
reader.Close();
return(CreateExcelResult(excel, "CHAIDonationsReport.xlsx"));
}
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.GetCustomStatementsList(m.FundSet.Value).JoinInts(",");
p.Add("@fundids", fundset);
}
else
{
p.Add("@fundids", null);
}
return(p);
}
