public DataSet GetContributionTransactions(int groupId, int?personId, [FromBody] ContributionStatementOptions options)
{
var qry = Get().Where(a => a.TransactionDateTime >= options.StartDate);
if (options.EndDate.HasValue)
{
qry = qry.Where(a => a.TransactionDateTime < options.EndDate.Value);
}
var transactionTypeContribution = Rock.Web.Cache.DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid());
if (transactionTypeContribution != null)
{
int transactionTypeContributionId = transactionTypeContribution.Id;
qry = qry.Where(a => a.TransactionTypeValueId == transactionTypeContributionId);
}
if (personId.HasValue)
{
// get transactions for a specific person
qry = qry.Where(a => a.AuthorizedPersonAlias.PersonId == personId.Value);
}
else
{
// get transactions for all the persons in the specified group that have specified that group as their GivingGroup
GroupMemberService groupMemberService = new GroupMemberService(( RockContext )Service.Context);
var personIdList = groupMemberService.GetByGroupId(groupId).Where(a => a.Person.GivingGroupId == groupId).Select(s => s.PersonId).ToList();
qry = qry.Where(a => personIdList.Contains(a.AuthorizedPersonAlias.PersonId));
}
if (options.AccountIds != null)
{
qry = qry.Where(a => a.TransactionDetails.Any(x => options.AccountIds.Contains(x.AccountId)));
}
var selectQry = qry.Select(a => new
{
a.TransactionDateTime,
CurrencyTypeValueName = a.FinancialPaymentDetail != null ? a.FinancialPaymentDetail.CurrencyTypeValue.Value : string.Empty,
a.Summary,
Details = a.TransactionDetails.Select(d => new
{
d.AccountId,
AccountName = d.Account.Name,
d.Summary,
d.Amount
}).OrderBy(x => x.AccountName),
}).OrderBy(a => a.TransactionDateTime);
DataTable dataTable = new DataTable("contribution_transactions");
dataTable.Columns.Add("TransactionDateTime", typeof(DateTime));
dataTable.Columns.Add("CurrencyTypeValueName");
dataTable.Columns.Add("Summary");
dataTable.Columns.Add("Amount", typeof(decimal));
dataTable.Columns.Add("Details", typeof(DataTable));
var list = selectQry.ToList();
dataTable.BeginLoadData();
foreach (var fieldItems in list)
{
DataTable detailTable = new DataTable("transaction_details");
detailTable.Columns.Add("AccountId", typeof(int));
detailTable.Columns.Add("AccountName");
detailTable.Columns.Add("Summary");
detailTable.Columns.Add("Amount", typeof(decimal));
var transactionDetails = fieldItems.Details.ToList();
// remove any Accounts that were not included (in case there was a mix of included and not included accounts in the transaction)
if (options.AccountIds != null)
{
transactionDetails = transactionDetails.Where(a => options.AccountIds.Contains(a.AccountId)).ToList();
}
foreach (var detail in transactionDetails)
{
var detailArray = new object[] {
detail.AccountId,
detail.AccountName,
detail.Summary,
detail.Amount
};
detailTable.Rows.Add(detailArray);
}
var itemArray = new object[] {
fieldItems.TransactionDateTime,
fieldItems.CurrencyTypeValueName,
fieldItems.Summary,
transactionDetails.Sum(a => a.Amount),
detailTable
};
dataTable.Rows.Add(itemArray);
}
dataTable.EndLoadData();
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable);
return(dataSet);
}
public DataSet GetContributionPersonGroupAddress([FromBody] ContributionStatementOptions options)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("startDate", options.StartDate);
if (options.EndDate.HasValue)
{
parameters.Add("endDate", options.EndDate.Value);
}
else
{
parameters.Add("endDate", DateTime.MaxValue);
}
if (options.AccountIds != null)
{
parameters.Add("accountIds", options.AccountIds.AsDelimited(","));
}
else
{
parameters.Add("accountIds", DBNull.Value);
}
if (options.PersonId.HasValue)
{
parameters.Add("personId", options.PersonId);
}
else
{
parameters.Add("personId", DBNull.Value);
}
if (options.IncludeIndividualsWithNoAddress)
{
parameters.Add("includeIndividualsWithNoAddress", options.IncludeIndividualsWithNoAddress);
}
else
{
parameters.Add("includeIndividualsWithNoAddress", false);
}
parameters.Add("orderByPostalCode", options.OrderByPostalCode);
var result = DbService.GetDataSet("spFinance_ContributionStatementQuery", System.Data.CommandType.StoredProcedure, parameters);
if (result.Tables.Count > 0)
{
var dataTable = result.Tables[0];
dataTable.TableName = "contribution_person_group_address";
if (options.DataViewId.HasValue)
{
var dataView = new DataViewService(new RockContext()).Get(options.DataViewId.Value);
if (dataView != null)
{
List <string> errorMessages = new List <string>();
var personList = dataView.GetQuery(null, null, out errorMessages).OfType <Rock.Model.Person>().Select(a => new { a.Id, a.GivingGroupId }).ToList();
HashSet <int> personIds = new HashSet <int>(personList.Select(a => a.Id));
HashSet <int> groupsIds = new HashSet <int>(personList.Where(a => a.GivingGroupId.HasValue).Select(a => a.GivingGroupId.Value).Distinct());
foreach (var row in dataTable.Rows.OfType <DataRow>().ToList())
{
var personId = row["PersonId"];
var groupId = row["GroupId"];
if (personId != null && personId is int)
{
if (!personIds.Contains(( int )personId))
{
dataTable.Rows.Remove(row);
}
}
else if (groupId != null && groupId is int)
{
if (!groupsIds.Contains(( int )groupId))
{
dataTable.Rows.Remove(row);
}
}
}
}
}
}
return(result);
}
public DataSet GetContributionTransactions(int groupId, [FromBody] ContributionStatementOptions options)
{
return(GetContributionTransactions(groupId, null, options));
}
public DataSet GetContributionPersonGroupAddress([FromBody] ContributionStatementOptions options)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
var startDate = options.StartDate;
var endDate = options.EndDate ?? DateTime.MaxValue;
// The SQL date type has a more limited range than C# DateTime. We need to conform the date values to fit
// in the SQL date range or an error will be thrown in the stored procedure.
if (startDate < SqlDateTime.MinValue.Value)
{
startDate = SqlDateTime.MinValue.Value;
}
if (endDate > SqlDateTime.MaxValue.Value)
{
endDate = SqlDateTime.MaxValue.Value;
}
if (options.AccountIds != null)
{
parameters.Add("accountIds", options.AccountIds.AsDelimited(","));
}
else
{
parameters.Add("accountIds", DBNull.Value);
}
if (options.PersonId.HasValue)
{
parameters.Add("personId", options.PersonId);
}
else
{
parameters.Add("personId", DBNull.Value);
}
if (options.IncludeIndividualsWithNoAddress)
{
parameters.Add("includeIndividualsWithNoAddress", options.IncludeIndividualsWithNoAddress);
}
else
{
parameters.Add("includeIndividualsWithNoAddress", false);
}
parameters.Add("orderByPostalCode", options.OrderByPostalCode);
parameters.Add("startDate", startDate);
parameters.Add("endDate", endDate);
var result = DbService.GetDataSet("spFinance_ContributionStatementQuery", System.Data.CommandType.StoredProcedure, parameters);
if (result.Tables.Count > 0)
{
var dataTable = result.Tables[0];
dataTable.TableName = "contribution_person_group_address";
if (options.DataViewId.HasValue)
{
var dataView = new DataViewService(new RockContext()).Get(options.DataViewId.Value);
if (dataView != null)
{
var dataViewGetQueryArgs = new DataViewGetQueryArgs();
var personList = dataView.GetQuery(dataViewGetQueryArgs).OfType <Rock.Model.Person>().Select(a => new { a.Id, a.GivingGroupId }).ToList();
HashSet <int> personIds = new HashSet <int>(personList.Select(a => a.Id));
HashSet <int> groupsIds = new HashSet <int>(personList.Where(a => a.GivingGroupId.HasValue).Select(a => a.GivingGroupId.Value).Distinct());
foreach (var row in dataTable.Rows.OfType <DataRow>().ToList())
{
var personId = row["PersonId"];
var groupId = row["GroupId"];
if (personId != null && personId is int)
{
if (!personIds.Contains(( int )personId))
{
dataTable.Rows.Remove(row);
}
}
else if (groupId != null && groupId is int)
{
if (!groupsIds.Contains(( int )groupId))
{
dataTable.Rows.Remove(row);
}
}
}
}
}
}
return(result);
}
