/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindGiversGrid()
{
// Get all the selected criteria values
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(drpSlidingDateRange.DelimitedValues);
var start = dateRange.Start;
var end = dateRange.End;
var minAmount = nreAmount.LowerValue;
var maxAmount = nreAmount.UpperValue;
var currencyTypeIds = new List <int>();
cblCurrencyTypes.SelectedValues.ForEach(i => currencyTypeIds.Add(i.AsInteger()));
var sourceTypeIds = new List <int>();
cblTransactionSource.SelectedValues.ForEach(i => sourceTypeIds.Add(i.AsInteger()));
var accountIds = new List <int>();
foreach (var cblAccounts in phAccounts.Controls.OfType <RockCheckBoxList>())
{
accountIds.AddRange(cblAccounts.SelectedValuesAsInt);
}
var dataViewId = dvpDataView.SelectedValueAsInt();
GiversViewBy viewBy = GiversViewBy.Giver;
if (!HideViewByOption)
{
viewBy = hfViewBy.Value.ConvertToEnumOrNull <GiversViewBy>() ?? GiversViewBy.Giver;
}
// Clear all the existing grid columns
var selectField = new SelectField();
var oldSelectField = gGiversGifts.ColumnsOfType <SelectField>().FirstOrDefault();
if (oldSelectField != null)
{
selectField.SelectedKeys.AddRange(oldSelectField.SelectedKeys);
}
gGiversGifts.Columns.Clear();
// Add a column for selecting rows
gGiversGifts.Columns.Add(selectField);
// Add a column for the person's name
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "PersonName",
HeaderText = "Person",
SortExpression = "LastName,NickName"
});
// add a column for email (but is only included on excel export)
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Email",
HeaderText = "Email",
SortExpression = "Email",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
});
// Add a column for total amount
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = "TotalAmount",
HeaderText = "Total",
SortExpression = "TotalAmount"
});
// Add columns for the selected account totals
if (accountIds.Any())
{
var accounts = new FinancialAccountService(_rockContext)
.Queryable().AsNoTracking()
.Where(a => accountIds.Contains(a.Id))
.ToList();
foreach (int accountId in accountIds)
{
var account = accounts.FirstOrDefault(a => a.Id == accountId);
if (account != null)
{
gGiversGifts.Columns.Add(
new CurrencyField
{
DataField = account.Id.ToString(),
HeaderText = account.Name,
SortExpression = account.Id.ToString()
});
}
}
}
// Add a column for the number of gifts
var numberGiftsField = new RockBoundField
{
DataField = "NumberGifts",
HeaderText = "Number of Gifts",
SortExpression = "NumberGifts",
DataFormatString = "{0:N0}",
};
numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
gGiversGifts.Columns.Add(numberGiftsField);
// Add a column to indicate if this is a first time giver
gGiversGifts.Columns.Add(
new BoolField
{
DataField = "IsFirstEverGift",
HeaderText = "Is First Gift",
SortExpression = "IsFirstEverGift"
});
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstGift",
HeaderText = "First Gift",
SortExpression = "FirstGift"
});
// Add a column for the first-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "FirstEverGift",
HeaderText = "First Gift Ever",
SortExpression = "FirstEverGift"
});
var transactionDetailService = new FinancialTransactionDetailService(_rockContext);
var personService = new PersonService(_rockContext);
// If dataview was selected get the person id's returned by the dataview
var dataViewPersonIds = new List <int>();
if (dataViewId.HasValue)
{
var dataView = new DataViewService(_rockContext).Get(dataViewId.Value);
if (dataView != null)
{
var errorMessages = new List <string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages);
SortProperty sortProperty = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where(paramExpression, whereExpression, sortProperty)
.Select(p => p.Id);
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
}
// Check to see if grid should display only people who gave a certain number of times and if so
// set the min value
int minCount = 0;
var previousPersonIds = new List <int>();
if (radByPattern.Checked)
{
minCount = tbPatternXTimes.Text.AsInteger();
var missedStart = drpPatternDateRange.LowerValue;
var missedEnd = drpPatternDateRange.UpperValue;
if (missedStart.HasValue && missedEnd.HasValue)
{
// Get the givingids that gave any amount during the pattern's date range. These
// are needed so that we know who to exclude from the result set
var previousGivingIds = transactionDetailService
.Queryable().AsNoTracking()
.Where(d =>
d.Transaction.TransactionDateTime.HasValue &&
d.Transaction.TransactionDateTime.Value >= missedStart.Value &&
d.Transaction.TransactionDateTime.Value < missedEnd.Value &&
accountIds.Contains(d.AccountId) &&
d.Amount != 0.0M)
.Select(d => d.Transaction.AuthorizedPersonAlias.Person.GivingId);
// Now get the person ids from the givingids
previousPersonIds = personService
.Queryable().AsNoTracking()
.Where(p => previousGivingIds.Contains(p.GivingId))
.Select(p => p.Id)
.ToList();
}
}
// Call the stored procedure to get all the giving data that matches the selected criteria.
// The stored procedure returns two tables. First is a list of all matching transaction summary
// information and the second table is each giving leader's first-ever gift date to a tax-deductible account
DataSet ds = FinancialTransactionDetailService.GetGivingAnalytics(start, end, minAmount, maxAmount,
accountIds, currencyTypeIds, sourceTypeIds, dataViewId, viewBy);
// Get the results table
DataTable dtResults = ds.Tables[0];
// Get the first-ever gift dates and load them into a dictionary for faster matching
DataTable dtFirstEver = ds.Tables[1];
var firstEverVals = new Dictionary <int, DateTime>();
foreach (DataRow row in ds.Tables[1].Rows)
{
if (!DBNull.Value.Equals(row["FirstEverGift"]))
{
firstEverVals.Add((int)row["PersonId"], (DateTime)row["FirstEverGift"]);
}
}
// Add columns to the result set for the first-ever data
dtResults.Columns.Add(new DataColumn("IsFirstEverGift", typeof(bool)));
dtResults.Columns.Add(new DataColumn("FirstEverGift", typeof(DateTime)));
foreach (DataRow row in dtResults.Rows)
{
bool rowValid = true;
// Get the person id
int personId = (int)row["Id"];
if (radByPattern.Checked)
{
// If pattern was specified check minimum gifts and other date range
int numberGifts = (int)row["NumberGifts"];
if (numberGifts < minCount)
{
rowValid = false;
}
else
{
// If this giving leader gave during the pattern date, remove the row since we
// only want those who did not
if (previousPersonIds.Contains(personId))
{
rowValid = false;
}
}
}
if (dataViewId.HasValue)
{
// If a dataview filter was specified, and this row is not part of dataview,
// remove it
if (!dataViewPersonIds.Contains(personId))
{
rowValid = false;
// Remove person id from list so that list can be used later to optionally
// add rows for remaining people who were in the dataview, but not in the
// result set
dataViewPersonIds.Remove(personId);
}
}
if (rowValid)
{
// Set the first ever information for each row
bool isFirstEverGift = false;
DateTime firstGift = (DateTime)row["FirstGift"];
if (firstEverVals.ContainsKey(personId))
{
DateTime firstEverGift = firstEverVals[personId];
isFirstEverGift = firstEverGift.Equals(firstGift);
row["FirstEverGift"] = firstEverGift;
}
// If only first time givers should be included, remove any that are not
if (radFirstTime.Checked && !isFirstEverGift)
{
rowValid = false;
}
else
{
row["IsFirstEverGift"] = isFirstEverGift;
}
}
if (!rowValid)
{
row.Delete();
}
}
// if dataview was selected and it includes people not in the result set,
if (dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any())
{
// Query for the names of each of these people
foreach (var person in personService
.Queryable().AsNoTracking()
.Select(p => new {
p.Id,
p.Guid,
p.NickName,
p.LastName,
p.Email
}))
{
// Check for a first ever gift date
var firstEverGiftDate = firstEverVals
.Where(f => f.Key == person.Id)
.Select(f => f.Value)
.FirstOrDefault();
DataRow row = dtResults.NewRow();
row["Id"] = person.Id;
row["Guid"] = person.Guid;
row["NickName"] = person.NickName;
row["LastName"] = person.LastName;
row["PersonName"] = person.NickName + " " + person.LastName;
row["Email"] = person.Email;
row["IsFirstEverGift"] = false;
row["FirstEverGift"] = firstEverGiftDate;
dtResults.Rows.Add(row);
}
}
// Update the changes (deletes) in the datatable
dtResults.AcceptChanges();
// Calculate Total
if (viewBy == GiversViewBy.Giver)
{
pnlTotal.Visible = true;
object amountTotalObj = dtResults.Compute("Sum(TotalAmount)", null);
if (amountTotalObj != null)
{
decimal amountTotal = amountTotalObj.ToString().AsDecimal();
lTotal.Text = amountTotal.FormatAsCurrency();
}
else
{
lTotal.Text = string.Empty;
}
}
else
{
pnlTotal.Visible = false;
}
// Sort the results
System.Data.DataView dv = dtResults.DefaultView;
if (gGiversGifts.SortProperty != null)
{
try
{
var sortProperties = new List <string>();
foreach (string prop in gGiversGifts.SortProperty.Property.SplitDelimitedValues(false))
{
sortProperties.Add(string.Format("[{0}] {1}", prop, gGiversGifts.SortProperty.DirectionString));
}
dv.Sort = sortProperties.AsDelimited(", ");
}
catch
{
dv.Sort = "[LastName] ASC, [NickName] ASC";
}
}
else
{
dv.Sort = "[LastName] ASC, [NickName] ASC";
}
gGiversGifts.DataSource = dv;
gGiversGifts.DataBind();
}
/// <summary>
/// Gets the gifts.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="minAmount">The minimum amount.</param>
/// <param name="maxAmount">The maximum amount.</param>
/// <param name="accountIds">The account ids.</param>
/// <param name="currencyTypeIds">The currency type ids.</param>
/// <param name="sourceTypeIds">The source type ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <param name="giversViewBy">The givers view by.</param>
/// <returns></returns>
public static DataSet GetGivingAnalytics(
DateTime? start, DateTime? end, decimal? minAmount, decimal? maxAmount,
List<int> accountIds, List<int> currencyTypeIds, List<int> sourceTypeIds, int? dataViewId, GiversViewBy giversViewBy )
{
Dictionary<string, object> parameters = new Dictionary<string, object>();
if ( start.HasValue )
{
parameters.Add( "StartDate", start.Value );
}
if ( end.HasValue )
{
parameters.Add( "EndDate", end.Value );
}
if ( minAmount.HasValue )
{
parameters.Add( "MinAmount", minAmount.Value );
}
if ( maxAmount.HasValue )
{
parameters.Add( "MaxAmount", maxAmount.Value );
}
if ( accountIds != null && accountIds.Any() )
{
parameters.Add( "AccountIds", accountIds.AsDelimited(",") );
}
if ( currencyTypeIds != null && currencyTypeIds.Any() )
{
parameters.Add( "CurrencyTypeIds", currencyTypeIds.AsDelimited(",") );
}
if ( sourceTypeIds != null && sourceTypeIds.Any() )
{
parameters.Add( "SourceTypeIds", sourceTypeIds.AsDelimited(",") );
}
string viewBy = "G";
switch ( giversViewBy )
{
case GiversViewBy.Giver: viewBy = "G"; break;
case GiversViewBy.Adults: viewBy = "A"; break;
case GiversViewBy.Children: viewBy = "C"; break;
case GiversViewBy.Family: viewBy = "F"; break;
}
parameters.Add( "ViewBy", viewBy );
var result = DbService.GetDataSet( "spFinance_GivingAnalyticsQuery", System.Data.CommandType.StoredProcedure, parameters, 180 );
return result;
}
/// <summary>
/// Loads the attendance reporting settings from user preferences.
/// </summary>
private void LoadSettingsFromUserPreferences()
{
string keyPrefix = string.Format("giving-analytics-{0}-", this.BlockId);
string slidingDateRangeSettings = this.GetUserPreference(keyPrefix + "SlidingDateRange");
if (string.IsNullOrWhiteSpace(slidingDateRangeSettings))
{
// default to current year
drpSlidingDateRange.SlidingDateRangeMode = SlidingDateRangePicker.SlidingDateRangeType.Current;
drpSlidingDateRange.TimeUnit = SlidingDateRangePicker.TimeUnitType.Year;
}
else
{
drpSlidingDateRange.DelimitedValues = slidingDateRangeSettings;
}
hfGroupBy.Value = this.GetUserPreference(keyPrefix + "GroupBy");
nreAmount.DelimitedValues = this.GetUserPreference(keyPrefix + "AmountRange");
var currencyTypeIdList = this.GetUserPreference(keyPrefix + "CurrencyTypeIds").Split(',').ToList();
cblCurrencyTypes.SetValues(currencyTypeIdList);
var sourceIdList = this.GetUserPreference(keyPrefix + "SourceIds").Split(',').ToList();
cblTransactionSource.SetValues(sourceIdList);
var accountIdList = this.GetUserPreference(keyPrefix + "AccountIds").Split(',').ToList();
foreach (var cblAccounts in phAccounts.Controls.OfType <RockCheckBoxList>())
{
cblAccounts.SetValues(accountIdList);
}
dvpDataView.SetValue(this.GetUserPreference(keyPrefix + "DataView"));
HideShowDataViewResultOption();
rblDataViewAction.SetValue(this.GetUserPreference(keyPrefix + "DataViewAction"));
hfGraphBy.Value = this.GetUserPreference(keyPrefix + "GraphBy");
ShowBy showBy = this.GetUserPreference(keyPrefix + "ShowBy").ConvertToEnumOrNull <ShowBy>() ?? ShowBy.Chart;
DisplayShowBy(showBy);
GiversViewBy viewBy = this.GetUserPreference(keyPrefix + "ViewBy").ConvertToEnumOrNull <GiversViewBy>() ?? GiversViewBy.Giver;
hfViewBy.Value = viewBy.ConvertToInt().ToString();
GiversFilterBy giversFilterby = this.GetUserPreference(keyPrefix + "GiversFilterByType").ConvertToEnumOrNull <GiversFilterBy>() ?? GiversFilterBy.All;
switch (giversFilterby)
{
case GiversFilterBy.FirstTime:
radFirstTime.Checked = true;
break;
case GiversFilterBy.Pattern:
radByPattern.Checked = true;
break;
default:
radAllGivers.Checked = true;
break;
}
string attendeesFilterByPattern = this.GetUserPreference(keyPrefix + "GiversFilterByPattern");
string[] attendeesFilterByPatternValues = attendeesFilterByPattern.Split('|');
if (attendeesFilterByPatternValues.Length == 3)
{
tbPatternXTimes.Text = attendeesFilterByPatternValues[0];
cbPatternAndMissed.Checked = attendeesFilterByPatternValues[1].AsBooleanOrNull() ?? false;
drpPatternDateRange.DelimitedValues = attendeesFilterByPatternValues[2];
}
}
/// <summary>
/// Gets the gifts.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="minAmount">The minimum amount.</param>
/// <param name="maxAmount">The maximum amount.</param>
/// <param name="accountIds">The account ids.</param>
/// <param name="currencyTypeIds">The currency type ids.</param>
/// <param name="sourceTypeIds">The source type ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <param name="giversViewBy">The givers view by.</param>
/// <returns></returns>
public static DataSet GetGivingAnalytics(
DateTime?start, DateTime?end, decimal?minAmount, decimal?maxAmount,
List <int> accountIds, List <int> currencyTypeIds, List <int> sourceTypeIds, int?dataViewId, GiversViewBy giversViewBy)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
if (start.HasValue)
{
parameters.Add("StartDate", start.Value);
}
if (end.HasValue)
{
parameters.Add("EndDate", end.Value);
}
if (minAmount.HasValue)
{
parameters.Add("MinAmount", minAmount.Value);
}
if (maxAmount.HasValue)
{
parameters.Add("MaxAmount", maxAmount.Value);
}
if (accountIds != null && accountIds.Any())
{
parameters.Add("AccountIds", accountIds.AsDelimited(","));
}
if (currencyTypeIds != null && currencyTypeIds.Any())
{
parameters.Add("CurrencyTypeIds", currencyTypeIds.AsDelimited(","));
}
if (sourceTypeIds != null && sourceTypeIds.Any())
{
parameters.Add("SourceTypeIds", sourceTypeIds.AsDelimited(","));
}
string viewBy = "G";
switch (giversViewBy)
{
case GiversViewBy.Giver: viewBy = "G"; break;
case GiversViewBy.Adults: viewBy = "A"; break;
case GiversViewBy.Children: viewBy = "C"; break;
case GiversViewBy.Family: viewBy = "F"; break;
}
parameters.Add("ViewBy", viewBy);
var result = DbService.GetDataSet("spFinance_GivingAnalyticsQuery", System.Data.CommandType.StoredProcedure, parameters, 180);
return(result);
}
