public void DonorDetailsTest(string notitles, string uselabelname)
{
using (var db = CMSDataContext.Create(Util.Host))
{
db.SetSetting("NoTitlesOnStatements", notitles);
db.SetSetting("UseLabelNameForDonorDetails", uselabelname);
var bundleList = CreateTestContributionSet(db, Util.Now.Date);
var exportPeople = new ExportPeople(db);
DateTime startDate = Util.Now.AddDays(-180);
DateTime endDate = Util.Now.AddDays(180);
DataTable tableResult = exportPeople.DonorDetails(startDate, endDate, 0, 0, false, null, true, null, null);
var dbContributionsQry = db.Contributions
.Where(x => !ContributionTypeCode.ReturnedReversedTypes.Contains(x.ContributionTypeId) && !ContributionTypeCode.Pledge.Equals(x.ContributionTypeId))
.Where(x => x.ContributionDate >= startDate && x.ContributionDate < endDate)
.Select(x => x);
var tableResultTotals = tableResult.AsEnumerable().Sum(row => row.Field <decimal>("Amount"));
var totalContributions = dbContributionsQry.Sum(x => x.ContributionAmount) ?? 0;
totalContributions.ToDouble().ShouldBe(tableResultTotals.ToDouble());
foreach (var b in bundleList)
{
MockContributions.DeleteAllFromBundle(db, b);
}
db.DeleteSetting("NoTitlesOnStatements");
db.DeleteSetting("UseLabelNameForDonorDetails");
}
}
public void ExportDonorFundTotals_Should_Bring_Pledges(int fundid, int campusid, bool?nontaxdeductible,
bool includeUnclosed, int?tagid, string fundids, int?online)
{
using (var db = CMSDataContext.Create(Util.Host))
{
var bundleList = CreateTestContributionSet(db, Util.Now.Date);
var _exportPeople = new ExportPeople(db);
DateTime exportStartDt = Util.Now.AddDays(-180);
DateTime exportEndDt = Util.Now.AddDays(180);
DataTable tableResult = _exportPeople.ExcelDonorFundTotals(exportStartDt, exportEndDt, fundid, campusid, true, nontaxdeductible, includeUnclosed, tagid, fundids, (int)online);
try
{
var q = from c in tableResult.Select() where c.ItemArray[3].ToInt() > 0 select c;
if (q.IsNotNull())
{
var amountpledged = Double.Parse(q.Select(x => x.ItemArray[3]).SingleOrDefault().ToString());
//The pledged amount correspond to the pledge amount created in CreateTestContributionSet()
amountpledged.ShouldBe(1000.00);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
foreach (var b in bundleList)
{
MockContributions.DeleteAllFromBundle(db, b);
}
}
}
}
public ActionResult Excel(Guid id, string format, bool?titles, bool?useMailFlags)
{
var ctl = new MailingController {
UseTitles = titles ?? false, UseMailFlags = useMailFlags ?? false
};
switch (format)
{
case "Individual":
case "GroupAddress":
return(ExportPeople.FetchExcelList(id, maxExcelRows, useMailFlags ?? false).ToExcel());
case "Library":
return(ExportPeople.FetchExcelLibraryList(id));
case "AllFamily":
return(ExportPeople.FetchExcelListFamily(id));
case "Family":
return(ctl.FetchExcelFamily(id, maxExcelRows));
case "ParentsOf":
return(ctl.FetchExcelParents(id, maxExcelRows));
case "CouplesEither":
return(ctl.FetchExcelCouplesEither(id, maxExcelRows));
case "CouplesBoth":
return(ctl.FetchExcelCouplesBoth(id, maxExcelRows));
case "Involvement":
return(new ExcelResult(ExportInvolvements.InvolvementList(id), "Involvements.xls"));
case "Children":
return(ExportInvolvements.ChildrenList(id, maxExcelRows));
case "Church":
return(ExportInvolvements.ChurchList(id, maxExcelRows));
case "Attend":
return(ExportInvolvements.AttendList(id, maxExcelRows));
case "Promotion":
return(ExportInvolvements.PromoList(id, maxExcelRows));
case "IndividualPicture":
return(ExcelExportModel.Result(id));
case "FamilyMembers":
return(ExportPeople.FetchExcelListFamilyMembers(id));
case "OrgMembers":
return(OrgsMembersExcelModel.Export(Util2.CurrentOrgId ?? 0));
case "Groups":
return(ExportInvolvements.OrgMemberListGroups());
}
return(Content("no format"));
}
public void ExcelDonorTotals_Should_Not_Bring_Reversed_or_Returned_contributions(int campusid, bool pledges, bool?nontaxdeductible, bool includeUnclosed, int?tagid, string fundids)
{
using (var db = CMSDataContext.Create(Util.Host))
{
var bundleList = CreateTestContributionSet(db, Util.Now.Date);
var _exportPeople = new ExportPeople(db);
int?online = null;
DateTime exportStartDt = Util.Now.AddDays(-180);
DateTime exportEndDt = Util.Now.AddDays(180);
DataTable tableResult = _exportPeople.ExcelDonorTotals(exportStartDt, exportEndDt, campusid, pledges, nontaxdeductible, online, includeUnclosed, tagid, fundids, false);
var dbContributionsQry = db.Contributions
.Where(x => !ContributionTypeCode.ReturnedReversedTypes.Contains(x.ContributionTypeId) && !ContributionTypeCode.Pledge.Equals(x.ContributionTypeId))
.Where(x => ContributionStatusCode.Recorded.Equals(x.ContributionStatusId))
.Where(x => x.ContributionDate >= exportStartDt && x.ContributionDate < exportEndDt)
.Select(x => x);
dbContributionsQry = nontaxdeductible.HasValue
? (nontaxdeductible is true)
? dbContributionsQry = dbContributionsQry.Where(x => ContributionTypeCode.NonTaxDed.Equals(x.ContributionTypeId)).Select(x => x)
: dbContributionsQry = dbContributionsQry.Where(x => !ContributionTypeCode.NonTaxDed.Equals(x.ContributionTypeId)).Select(x => x)
: dbContributionsQry;
if (includeUnclosed is false)
{
dbContributionsQry = from c in dbContributionsQry
join bd in db.BundleDetails on c.ContributionId equals bd.ContributionId
join bh in db.BundleHeaders on bd.BundleHeaderId equals bh.BundleHeaderId
where bh.BundleStatusId == 0
select c;
}
var tableResultTotals = tableResult.AsEnumerable().Sum(row => row.Field <decimal>("Amount"));
var totalContributions = dbContributionsQry.Sum(x => x.ContributionAmount) ?? 0;
try
{
totalContributions.ToDouble().ShouldBe(tableResultTotals.ToDouble());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
foreach (var b in bundleList)
{
MockContributions.DeleteAllFromBundle(db, b);
}
}
}
}
public void DonorDetails_Should_Not_Bring_Reversed_or_Returned_contributions(
int fundid, int campusid, bool pledges, bool?nontaxdeductible, bool includeUnclosed, int?tagid, string fundids)
{
using (var db = CMSDataContext.Create(Util.Host))
{
var bundleList = CreateTestContributionSet(db, Util.Now.Date);
var _exportPeople = new ExportPeople();
DateTime exportStartDt = new DateTime(Util.Now.Date.Year, 1, 1);
DateTime exportEndDt = new DateTime(Util.Now.Date.Year, 12, 31);
DataTable tableResult = _exportPeople.DonorDetails(exportStartDt, exportEndDt, fundid, campusid, pledges, nontaxdeductible, includeUnclosed, tagid, fundids);
var dbContributionsQry = db.Contributions
.Where(x => !ContributionTypeCode.ReturnedReversedTypes.Contains(x.ContributionTypeId) && !ContributionTypeCode.Pledge.Equals(x.ContributionTypeId))
.Where(x => ContributionStatusCode.Recorded.Equals(x.ContributionStatusId))
.Where(x => x.ContributionDate >= exportStartDt && x.ContributionDate < exportEndDt)
.Select(x => x);
dbContributionsQry = nontaxdeductible.HasValue
? (nontaxdeductible is true)
? dbContributionsQry = dbContributionsQry.Where(x => ContributionTypeCode.NonTaxDed.Equals(x.ContributionTypeId)).Select(x => x)
: dbContributionsQry = dbContributionsQry.Where(x => !ContributionTypeCode.NonTaxDed.Equals(x.ContributionTypeId)).Select(x => x)
: dbContributionsQry;
if (includeUnclosed is false)
{
dbContributionsQry = from c in dbContributionsQry
join bd in db.BundleDetails on c.ContributionId equals bd.ContributionId
join bh in db.BundleHeaders on bd.BundleHeaderId equals bh.BundleHeaderId
where bh.BundleStatusId == 0
select c;
}
var rc = tableResult.AsEnumerable().Where(row => ContributionTypeCode.ReturnedReversedTypes.Contains(row.Field <int>("ContributionTypeId")));
var tableResultTotals = tableResult.AsEnumerable().Sum(row => row.Field <decimal>("Amount"));
var totalContributions = dbContributionsQry.Sum(x => x.ContributionAmount) ?? 0;
totalContributions.ToDouble().ShouldBe(tableResultTotals.ToDouble());
rc.Count().ShouldBeLessThan(1);
foreach (var b in bundleList)
{
MockContributions.DeleteAllFromBundle(db, b);
}
}
}
public void DonorDetails_Should_Not_Bring_Reversed_or_Returned_contributions(
int campusid, bool pledges, bool?nontaxdeductible, bool includeUnclosed, int?tagid, string fundids)
{
using (var db = CMSDataContext.Create(Util.Host))
{
var bundleList = CreateTestContributionSet(db, Util.Now.Date);
var _exportPeople = new ExportPeople(db);
DateTime exportStartDt = Util.Now.AddDays(-180);
DateTime exportEndDt = Util.Now.AddDays(180);
var tableResult = _exportPeople.GetValidContributionDetails(exportStartDt, exportEndDt, campusid, pledges, nontaxdeductible, includeUnclosed, tagid, fundids);
var rc = tableResult.Where(row => ContributionTypeCode.ReturnedReversedTypes.Contains(row.ContributionTypeId));
rc.Count().ShouldBe(0);
foreach (var b in bundleList)
{
MockContributions.DeleteAllFromBundle(db, b);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
var labelNameFormat = Request.QueryString["format"];
int?qid = Request.QueryString["id"].ToInt2();
var r = Response;
r.Clear();
var useweb = Request.QueryString["web"];
string header =
@"<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">
<head>
<meta http-equiv=Content-Type content=""text/html; charset=utf-8"">
<style>
<!--table
br {mso-data-placement:same-cell;}
tr {vertical-align:top;}
td.Text {mso-number-format:\@}
-->
</style>
</head>
<body>";
r.Charset = "";
if (!qid.HasValue && labelNameFormat != "Groups")
{
r.Write("no queryid");
r.Flush();
r.End();
}
if (useweb != "true")
{
r.ContentType = "application/vnd.ms-excel";
r.AddHeader("Content-Disposition", "attachment;filename=CMSPeople.xls");
}
r.Write(header);
var ctl = new MailingController();
var useTitles = Request.QueryString["titles"];
ctl.UseTitles = useTitles == "true";
var dg = new DataGrid();
dg.EnableViewState = false;
switch (labelNameFormat)
{
case "Individual":
dg.DataSource = ExportPeople.FetchExcelList(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "IndividualPicture":
GridView1.EnableViewState = false;
GridView1.AllowPaging = false;
GridView1.DataSource = ExportPeople.FetchExcelListPics(qid.Value, maxExcelRows);
GridView1.DataBind();
GridView1.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Library":
dg.DataSource = ExportPeople.FetchExcelLibraryList(qid.Value);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Family":
dg.DataSource = ctl.FetchExcelFamily(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "FamilyMembers":
FamilyMembers.EnableViewState = false;
FamilyMembers.AllowPaging = false;
FamilyMembers.DataSource = ExportPeople.FetchExcelListFamilyMembers(qid.Value);
FamilyMembers.DataBind();
FamilyMembers.RenderControl(new HtmlTextWriter(r.Output));
break;
case "AllFamily":
dg.DataSource = ExportPeople.FetchExcelListFamily(qid.Value);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "ParentsOf":
dg.DataSource = ctl.FetchExcelParents(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "CouplesEither":
dg.DataSource = ctl.FetchExcelCouplesEither(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "CouplesBoth":
dg.DataSource = ctl.FetchExcelCouplesBoth(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Involvement":
dg.DataSource = ExportInvolvements.InvolvementList(qid.Value);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Children":
dg.DataSource = ExportInvolvements.ChildrenList(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Church":
dg.DataSource = ExportInvolvements.ChurchList(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Attend":
dg.DataSource = ExportInvolvements.AttendList(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Organization":
dg.DataSource = ExportInvolvements.OrgMemberList(qid.Value);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Groups":
dg.DataSource = ExportInvolvements.OrgMemberListGroups();
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
case "Promotion":
dg.DataSource = ExportInvolvements.PromoList(qid.Value, maxExcelRows);
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
break;
}
r.Write("</body></HTML>");
r.Flush();
r.End();
}
public void ExcelDonorFundTotals_Should_Not_Bring_Reversed_or_Returned_contributions(int fundid, int?campusid, bool pledges, bool?nontaxdeductible, bool includeUnclosed, int?tagid, string fundids, int online)
{
using (var db = CMSDataContext.Create(Util.Host))
{
var bundleList = CreateTestContributionSet(db, Util.Now.Date);
var _exportPeople = new ExportPeople(db);
DateTime exportStartDt = Util.Now.AddDays(-180);
DateTime exportEndDt = Util.Now.AddDays(180);
DataTable tableResult = _exportPeople.ExcelDonorFundTotals(exportStartDt, exportEndDt, fundid, (int)campusid, pledges, nontaxdeductible, includeUnclosed, tagid, fundids, online);
var nontax = nontaxdeductible is null ? "Both" : nontaxdeductible == true ? "NonTaxDed" : "";
IQueryable <Contribution> contributions;
if (!nontax.HasValue())
{
nontax = "TaxDed";
}
contributions = db.Contributions;
if (!includeUnclosed)
{
contributions = from c in contributions
where c.BundleDetails.Any(dd => dd.BundleHeader.BundleStatusId == BundleStatusCode.Closed)
select c;
}
switch (nontax)
{
case "TaxDed":
contributions = pledges
?
(from c in contributions
where !ContributionTypeCode.NonTaxDed.Equals(c.ContributionTypeId)
select c)
:
(from c in contributions
where !ContributionTypeCode.NonTaxDed.Equals(c.ContributionTypeId)
where !ContributionTypeCode.Pledge.Equals(c.ContributionTypeId)
select c);
break;
case "NonTaxDed":
contributions = pledges
?
(from c in contributions
where ContributionTypeCode.NonTaxDed.Equals(c.ContributionTypeId)
select c).Concat(from c in contributions where c.ContributionTypeId == ContributionTypeCode.Pledge select c)
:
(from c in contributions
where ContributionTypeCode.NonTaxDed.Equals(c.ContributionTypeId)
where !ContributionTypeCode.Pledge.Equals(c.ContributionTypeId)
select c);
break;
case "Both":
contributions = pledges
?
(from c in contributions
select c)
:
(from c in contributions
where !ContributionTypeCode.Pledge.Equals(c.ContributionTypeId)
select c);
break;
case "Pledge":
contributions = from c in contributions
where c.ContributionTypeId == ContributionTypeCode.Pledge
select c;
break;
}
if (exportStartDt.ToDate().HasValue)
{
contributions = from c in contributions
where c.ContributionDate >= exportStartDt
select c;
}
if (exportEndDt.ToDate().HasValue)
{
contributions = from c in contributions
where c.ContributionDate < exportEndDt.ToDate().Value.AddDays(1)
select c;
}
if (online == 1)
{
contributions = from c in contributions
where c.BundleDetails.Any(dd => dd.BundleHeader.BundleHeaderTypeId == BundleTypeCode.Online)
select c;
}
else if (online == 0)
{
contributions = from c in contributions
where c.BundleDetails.All(dd => dd.BundleHeader.BundleHeaderTypeId != BundleTypeCode.Online)
select c;
}
if ((campusid ?? 0) != 0)
{
contributions = from c in contributions
where (c.CampusId ?? c.Person.CampusId) == campusid
select c;
}
if (fundids.HasValue())
{
if (fundids != null)
{
contributions = from c in contributions
where fundids.Contains(c.FundId.ToString())
select c;
}
}
var tableResultTotals = tableResult.AsEnumerable().Sum(row => row.Field <decimal>("Amount"));
var totalContributions = contributions.Sum(x => x.ContributionAmount) ?? 0;
totalContributions.ToDouble().ShouldBe(tableResultTotals.ToDouble());
foreach (var b in bundleList)
{
MockContributions.DeleteAllFromBundle(db, b);
}
}
}
