private void CreateGrids( RockContext rockContext )
{
if ( ContentChannels.Any() )
{
this.Visible = true;
// TODO: security
bool canEdit = true;
phContentChannelGrids.Controls.Clear();
foreach ( var contentChannel in ContentChannels )
{
var pwItems = new PanelWidget();
phContentChannelGrids.Controls.Add( pwItems );
pwItems.ID = string.Format( "pwItems_{0}", contentChannel.Id );
pwItems.Title = contentChannel.Name;
pwItems.Expanded = ExpandedPanels.Contains( contentChannel.Id );
var divItems = new HtmlGenericControl( "div" );
pwItems.Controls.Add( divItems );
divItems.ID = string.Format( "divItems_{0}", contentChannel.Id );
divItems.AddCssClass( "grid" );
divItems.AddCssClass( "grid-panel" );
Grid gItems = new Grid();
divItems.Controls.Add( gItems );
gItems.ID = string.Format( "gItems_{0}", contentChannel.Id );
gItems.DataKeyNames = new string[] { "Id" };
gItems.EmptyDataText = "No Items Found";
gItems.RowItemText = "Item";
gItems.AllowSorting = true;
gItems.Actions.ShowAdd = canEdit;
gItems.IsDeleteEnabled = canEdit;
gItems.Actions.AddClick += gItems_Add;
gItems.RowSelected += gItems_Edit;
gItems.GridRebind += gItems_GridRebind;
gItems.Columns.Add( new RockBoundField
{
DataField = "Title",
HeaderText = "Title",
SortExpression = "Title"
} );
gItems.Columns.Add( new DateTimeField
{
DataField = "StartDateTime",
HeaderText = contentChannel.ContentChannelType.DateRangeType == ContentChannelDateType.DateRange ? "Start" : "Active",
SortExpression = "StartDateTime"
} );
if ( contentChannel.ContentChannelType.DateRangeType == ContentChannelDateType.DateRange )
{
gItems.Columns.Add( new DateTimeField
{
DataField = "ExpireDateTime",
HeaderText = "Expire",
SortExpression = "ExpireDateTime"
} );
}
if ( !contentChannel.ContentChannelType.DisablePriority )
{
var priorityField = new RockBoundField
{
DataField = "Priority",
HeaderText = "Priority",
SortExpression = "Priority",
DataFormatString = "{0:N0}",
};
priorityField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
gItems.Columns.Add( priorityField );
}
// Add attribute columns
int entityTypeId = EntityTypeCache.Read( typeof( Rock.Model.ContentChannelItem ) ).Id;
string qualifier = contentChannel.ContentChannelTypeId.ToString();
foreach ( var attribute in new AttributeService( rockContext ).Queryable()
.Where( a =>
a.EntityTypeId == entityTypeId &&
a.IsGridColumn &&
a.EntityTypeQualifierColumn.Equals( "ContentChannelTypeId", StringComparison.OrdinalIgnoreCase ) &&
a.EntityTypeQualifierValue.Equals( qualifier ) )
.OrderBy( a => a.Order )
.ThenBy( a => a.Name ) )
{
string dataFieldExpression = attribute.Key;
bool columnExists = gItems.Columns.OfType<AttributeField>().FirstOrDefault( a => a.DataField.Equals( dataFieldExpression ) ) != null;
if ( !columnExists )
{
AttributeField boundField = new AttributeField();
boundField.DataField = dataFieldExpression;
boundField.HeaderText = attribute.Name;
boundField.SortExpression = string.Empty;
var attributeCache = Rock.Web.Cache.AttributeCache.Read( attribute.Id );
if ( attributeCache != null )
{
boundField.ItemStyle.HorizontalAlign = attributeCache.FieldType.Field.AlignValue;
}
gItems.Columns.Add( boundField );
}
}
if ( contentChannel.RequiresApproval )
{
var statusField = new BoundField();
gItems.Columns.Add( statusField );
statusField.DataField = "Status";
statusField.HeaderText = "Status";
statusField.SortExpression = "Status";
statusField.HtmlEncode = false;
}
var deleteField = new DeleteField();
gItems.Columns.Add( deleteField );
deleteField.Click += gItems_Delete;
}
}
else
{
this.Visible = false;
}
}
/// <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>
/// Adds the filter controls and grid columns for all of the registration template's form fields
/// that were configured to 'Show on Grid'
/// </summary>
private void AddDynamicControls()
{
phRegistrantFormFieldFilters.Controls.Clear();
// Remove any of the dynamic person fields
var dynamicColumns = new List<string> {
"PersonAlias.Person.BirthDate",
};
foreach ( var column in gRegistrants.Columns
.OfType<BoundField>()
.Where( c => dynamicColumns.Contains( c.DataField ) )
.ToList() )
{
gRegistrants.Columns.Remove( column );
}
// Remove any of the dynamic attribute fields
foreach ( var column in gRegistrants.Columns
.OfType<AttributeField>()
.ToList() )
{
gRegistrants.Columns.Remove( column );
}
// Remove the fees field
foreach ( var column in gRegistrants.Columns
.OfType<TemplateField>()
.Where( c => c.HeaderText == "Fees" )
.ToList() )
{
gRegistrants.Columns.Remove( column );
}
// Remove the delete field
foreach ( var column in gRegistrants.Columns
.OfType<DeleteField>()
.ToList() )
{
gRegistrants.Columns.Remove( column );
}
// Remove any of the dynamic attribute fields on group placements grid
foreach ( var column in gGroupPlacements.Columns
.OfType<AttributeField>()
.ToList() )
{
gGroupPlacements.Columns.Remove( column );
}
// Remove the delete field
foreach ( var column in gRegistrants.Columns
.OfType<GroupPickerField>()
.ToList() )
{
gGroupPlacements.Columns.Remove( column );
}
if ( RegistrantFields != null )
{
foreach ( var field in RegistrantFields )
{
if ( field.FieldSource == RegistrationFieldSource.PersonField && field.PersonFieldType.HasValue )
{
switch ( field.PersonFieldType.Value )
{
case RegistrationPersonFieldType.Campus:
{
var ddlCampus = new RockDropDownList();
ddlCampus.ID = "ddlCampus";
ddlCampus.Label = "Home Campus";
ddlCampus.DataValueField = "Id";
ddlCampus.DataTextField = "Name";
ddlCampus.DataSource = CampusCache.All();
ddlCampus.DataBind();
ddlCampus.Items.Insert( 0, new ListItem( "", "" ) );
ddlCampus.SetValue( fRegistrants.GetUserPreference( "Home Campus" ) );
phRegistrantFormFieldFilters.Controls.Add( ddlCampus );
var templateField = new RockLiteralField();
templateField.ID = "lCampus";
templateField.HeaderText = "Campus";
gRegistrants.Columns.Add( templateField );
var templateField2 = new RockLiteralField();
templateField2.ID = "lCampus";
templateField2.HeaderText = "Campus";
gGroupPlacements.Columns.Add( templateField2 );
break;
}
case RegistrationPersonFieldType.Email:
{
var tbEmailFilter = new RockTextBox();
tbEmailFilter.ID = "tbEmailFilter";
tbEmailFilter.Label = "Email";
tbEmailFilter.Text = fRegistrants.GetUserPreference( "Email" );
phRegistrantFormFieldFilters.Controls.Add( tbEmailFilter );
string dataFieldExpression = "PersonAlias.Person.Email";
var emailField = new RockBoundField();
emailField.DataField = dataFieldExpression;
emailField.HeaderText = "Email";
emailField.SortExpression = dataFieldExpression;
gRegistrants.Columns.Add( emailField );
var emailField2 = new RockBoundField();
emailField2.DataField = dataFieldExpression;
emailField2.HeaderText = "Email";
emailField2.SortExpression = dataFieldExpression;
gGroupPlacements.Columns.Add( emailField2 );
break;
}
case RegistrationPersonFieldType.Birthdate:
{
var drpBirthdateFilter = new DateRangePicker();
drpBirthdateFilter.ID = "drpBirthdateFilter";
drpBirthdateFilter.Label = "Birthdate Range";
drpBirthdateFilter.DelimitedValues = fRegistrants.GetUserPreference( "Birthdate Range" );
phRegistrantFormFieldFilters.Controls.Add( drpBirthdateFilter );
string dataFieldExpression = "PersonAlias.Person.BirthDate";
var birthdateField = new DateField();
birthdateField.DataField = dataFieldExpression;
birthdateField.HeaderText = "Birthdate";
birthdateField.SortExpression = dataFieldExpression;
gRegistrants.Columns.Add( birthdateField );
var birthdateField2 = new DateField();
birthdateField2.DataField = dataFieldExpression;
birthdateField2.HeaderText = "Birthdate";
birthdateField2.SortExpression = dataFieldExpression;
gGroupPlacements.Columns.Add( birthdateField2 );
break;
}
case RegistrationPersonFieldType.Grade:
{
var gpGradeFilter = new GradePicker();
gpGradeFilter.ID = "gpGradeFilter";
gpGradeFilter.Label = "Grade";
gpGradeFilter.UseAbbreviation = true;
gpGradeFilter.UseGradeOffsetAsValue = true;
gpGradeFilter.CssClass = "input-width-md";
// Since 12th grade is the 0 Value, we need to handle the "no user preference" differently
// by not calling SetValue otherwise it will select 12th grade.
var gradeUserPreference = fRegistrants.GetUserPreference( "Grade" ).AsIntegerOrNull();
if ( gradeUserPreference != null )
{
gpGradeFilter.SetValue( gradeUserPreference );
}
phRegistrantFormFieldFilters.Controls.Add( gpGradeFilter );
string dataFieldExpression = "PersonAlias.Person.GraduationYear";
var gradeField = new RockBoundField();
gradeField.DataField = dataFieldExpression;
gradeField.HeaderText = "Graduation Year";
gradeField.SortExpression = dataFieldExpression;
gRegistrants.Columns.Add( gradeField );
var gradeField2 = new RockBoundField();
gradeField2.DataField = dataFieldExpression;
gradeField2.HeaderText = "Graduation Year";
gradeField2.SortExpression = dataFieldExpression;
gGroupPlacements.Columns.Add( gradeField2 );
break;
}
case RegistrationPersonFieldType.Gender:
{
var ddlGenderFilter = new RockDropDownList();
ddlGenderFilter.BindToEnum<Gender>( true );
ddlGenderFilter.ID = "ddlGenderFilter";
ddlGenderFilter.Label = "Gender";
ddlGenderFilter.SetValue( fRegistrants.GetUserPreference( "Gender" ) );
phRegistrantFormFieldFilters.Controls.Add( ddlGenderFilter );
string dataFieldExpression = "PersonAlias.Person.Gender";
var genderField = new EnumField();
genderField.DataField = dataFieldExpression;
genderField.HeaderText = "Gender";
genderField.SortExpression = dataFieldExpression;
gRegistrants.Columns.Add( genderField );
var genderField2 = new EnumField();
genderField2.DataField = dataFieldExpression;
genderField2.HeaderText = "Gender";
genderField2.SortExpression = dataFieldExpression;
gGroupPlacements.Columns.Add( genderField2 );
break;
}
case RegistrationPersonFieldType.MaritalStatus:
{
var ddlMaritalStatusFilter = new RockDropDownList();
ddlMaritalStatusFilter.BindToDefinedType( DefinedTypeCache.Read( Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS.AsGuid() ), true );
ddlMaritalStatusFilter.ID = "ddlMaritalStatusFilter";
ddlMaritalStatusFilter.Label = "Marital Status";
ddlMaritalStatusFilter.SetValue( fRegistrants.GetUserPreference( "Marital Status" ) );
phRegistrantFormFieldFilters.Controls.Add( ddlMaritalStatusFilter );
string dataFieldExpression = "PersonAlias.Person.MaritalStatusValue.Value";
var maritalStatusField = new RockBoundField();
maritalStatusField.DataField = dataFieldExpression;
maritalStatusField.HeaderText = "MaritalStatus";
maritalStatusField.SortExpression = dataFieldExpression;
gRegistrants.Columns.Add( maritalStatusField );
var maritalStatusField2 = new RockBoundField();
maritalStatusField2.DataField = dataFieldExpression;
maritalStatusField2.HeaderText = "MaritalStatus";
maritalStatusField2.SortExpression = dataFieldExpression;
gGroupPlacements.Columns.Add( maritalStatusField2 );
break;
}
case RegistrationPersonFieldType.MobilePhone:
{
var tbPhoneFilter = new RockTextBox();
tbPhoneFilter.ID = "tbPhoneFilter";
tbPhoneFilter.Label = "Phone";
tbPhoneFilter.Text = fRegistrants.GetUserPreference( "Phone" );
phRegistrantFormFieldFilters.Controls.Add( tbPhoneFilter );
var phoneNumbersField = new PhoneNumbersField();
phoneNumbersField.DataField = "PersonAlias.Person.PhoneNumbers";
phoneNumbersField.HeaderText = "Phone(s)";
gRegistrants.Columns.Add( phoneNumbersField );
var phoneNumbersField2 = new PhoneNumbersField();
phoneNumbersField2.DataField = "PersonAlias.Person.PhoneNumbers";
phoneNumbersField2.HeaderText = "Phone(s)";
gGroupPlacements.Columns.Add( phoneNumbersField2 );
break;
}
}
}
else if ( field.Attribute != null )
{
var attribute = field.Attribute;
var control = attribute.FieldType.Field.FilterControl( attribute.QualifierValues, "filter_" + attribute.Id.ToString(), false, Rock.Reporting.FilterMode.SimpleFilter );
if ( control != null )
{
if ( control is IRockControl )
{
var rockControl = (IRockControl)control;
rockControl.Label = attribute.Name;
rockControl.Help = attribute.Description;
phRegistrantFormFieldFilters.Controls.Add( control );
}
else
{
var wrapper = new RockControlWrapper();
wrapper.ID = control.ID + "_wrapper";
wrapper.Label = attribute.Name;
wrapper.Controls.Add( control );
phRegistrantFormFieldFilters.Controls.Add( wrapper );
}
string savedValue = fRegistrants.GetUserPreference( attribute.Key );
if ( !string.IsNullOrWhiteSpace( savedValue ) )
{
try
{
var values = JsonConvert.DeserializeObject<List<string>>( savedValue );
attribute.FieldType.Field.SetFilterValues( control, attribute.QualifierValues, values );
}
catch { }
}
}
string dataFieldExpression = attribute.Id.ToString() + attribute.Key;
bool columnExists = gRegistrants.Columns.OfType<AttributeField>().FirstOrDefault( a => a.DataField.Equals( dataFieldExpression ) ) != null;
if ( !columnExists )
{
AttributeField boundField = new AttributeField();
boundField.DataField = dataFieldExpression;
boundField.AttributeId = attribute.Id;
boundField.HeaderText = attribute.Name;
AttributeField boundField2 = new AttributeField();
boundField2.DataField = dataFieldExpression;
boundField2.AttributeId = attribute.Id;
boundField2.HeaderText = attribute.Name;
var attributeCache = Rock.Web.Cache.AttributeCache.Read( attribute.Id );
if ( attributeCache != null )
{
boundField.ItemStyle.HorizontalAlign = attributeCache.FieldType.Field.AlignValue;
boundField2.ItemStyle.HorizontalAlign = attributeCache.FieldType.Field.AlignValue;
}
gRegistrants.Columns.Add( boundField );
gGroupPlacements.Columns.Add( boundField2 );
}
}
}
}
// Add fee column
var feeField = new RockLiteralField();
feeField.ID = "lFees";
feeField.HeaderText = "Fees";
gRegistrants.Columns.Add( feeField );
var deleteField = new DeleteField();
gRegistrants.Columns.Add( deleteField );
deleteField.Click += gRegistrants_Delete;
var groupPickerField = new GroupPickerField();
groupPickerField.HeaderText = "Group";
groupPickerField.RootGroupId = gpGroupPlacementParentGroup.SelectedValueAsInt();
gGroupPlacements.Columns.Add( groupPickerField );
}
/// <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 sourceIds = new List<int>();
cblTransactionSource.SelectedValues.ForEach( i => sourceIds.Add( i.AsInteger() ) );
var accountIds = new List<int>();
foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() )
{
accountIds.AddRange( cblAccounts.SelectedValuesAsInt );
}
var groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;
var graphBy = hfGraphBy.Value.ConvertToEnumOrNull<TransactionGraphBy>() ?? TransactionGraphBy.Total;
GiversViewBy viewBy = GiversViewBy.Giver;
if ( !HideViewByOption )
{
viewBy = hfViewBy.Value.ConvertToEnumOrNull<GiversViewBy>() ?? GiversViewBy.Giver;
}
// Collection of async queries to run before assembling data
var qryTasks = new List<Task>();
// Get all person summary data
var personInfoList = new List<PersonInfo>();
qryTasks.Add( Task.Run( () =>
{
var dt = FinancialTransactionDetailService.GetGivingAnalyticsPersonSummary(
start, end, minAmount, maxAmount, accountIds, currencyTypeIds, sourceIds )
.Tables[0];
foreach ( DataRow row in dt.Rows )
{
var personInfo = new PersonInfo();
if ( !DBNull.Value.Equals( row["Id"] ) )
{
personInfo.Id = (int)row["Id"];
}
if ( !DBNull.Value.Equals( row["Guid"] ) )
{
personInfo.Guid = row["Guid"].ToString().AsGuid();
}
if ( !DBNull.Value.Equals( row["NickName"] ) )
{
personInfo.NickName = row["NickName"].ToString();
}
if ( !DBNull.Value.Equals( row["LastName"] ) )
{
personInfo.LastName = row["LastName"].ToString();
}
if ( !DBNull.Value.Equals( row["Email"] ) )
{
personInfo.Email = row["Email"].ToString();
}
if ( !DBNull.Value.Equals( row["GivingId"] ) )
{
personInfo.GivingId = row["GivingId"].ToString();
}
if ( !DBNull.Value.Equals( row["FirstGift"] ) )
{
personInfo.FirstGift = row["FirstGift"].ToString().AsDateTime();
}
if ( !DBNull.Value.Equals( row["LastGift"] ) )
{
personInfo.LastGift = row["LastGift"].ToString().AsDateTime();
}
if ( !DBNull.Value.Equals( row["NumberGifts"] ) )
{
personInfo.NumberGifts = (int)row["NumberGifts"];
}
if ( !DBNull.Value.Equals( row["TotalAmount"] ) )
{
personInfo.TotalAmount = (decimal)row["TotalAmount"];
}
if ( !DBNull.Value.Equals( row["IsGivingLeader"] ) )
{
personInfo.IsGivingLeader = (bool)row["IsGivingLeader"];
}
if ( !DBNull.Value.Equals( row["IsAdult"] ) )
{
personInfo.IsAdult = (bool)row["IsAdult"];
}
if ( !DBNull.Value.Equals( row["IsChild"] ) )
{
personInfo.IsChild = (bool)row["IsChild"];
}
personInfo.AccountAmounts = new Dictionary<int, decimal>();
personInfoList.Add( personInfo );
}
} ) );
// Get the account summary values
DataTable dtAccountSummary = null;
qryTasks.Add( Task.Run( () =>
{
dtAccountSummary = FinancialTransactionDetailService.GetGivingAnalyticsAccountTotals(
start, end, accountIds, currencyTypeIds, sourceIds )
.Tables[0];
} ) );
// Get the first/last ever dates
var firstEverVals = new Dictionary<string, DateTime>();
var lastEverVals = new Dictionary<string, DateTime>();
qryTasks.Add( Task.Run( () =>
{
var dt = FinancialTransactionDetailService.GetGivingAnalyticsFirstLastEverDates()
.Tables[0];
foreach ( DataRow row in dt.Rows )
{
if ( !DBNull.Value.Equals( row["GivingId"] ) )
{
if ( !DBNull.Value.Equals( row["FirstEverGift"] ) )
{
firstEverVals.Add( row["GivingId"].ToString(), row["FirstEverGift"].ToString().AsDateTime().Value );
}
if ( !DBNull.Value.Equals( row["LastEverGift"] ) )
{
lastEverVals.Add( row["GivingId"].ToString(), row["LastEverGift"].ToString().AsDateTime().Value );
}
}
}
} ) );
// If a dataview filter was included, find the people who match that criteria
List<int> dataViewPersonIds = null;
var dataViewId = dvpDataView.SelectedValueAsInt();
if ( dataViewId.HasValue )
{
qryTasks.Add( Task.Run( () =>
{
dataViewPersonIds = new List<int>();
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
var dvPersonService = new PersonService( _rockContext );
ParameterExpression paramExpression = dvPersonService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( dvPersonService, paramExpression, out errorMessages );
SortProperty sort = null;
var dataViewPersonIdQry = dvPersonService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
} ) );
}
qryTasks.Add( Task.Run( () =>
{
// 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 hidden column for person id
gGiversGifts.Columns.Add(
new RockBoundField
{
DataField = "Id",
HeaderText = "Person Id",
SortExpression = "Id",
Visible = false,
ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude
} );
// 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 GivingAnalyticsAccountField
{
DataField = string.Format( "Account_{0}", account.Id ),
HeaderText = account.Name,
SortExpression = string.Format( "Account:{0}", account.Id ),
} );
}
}
}
// 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 );
if ( !radFirstTime.Checked )
{
// 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 in Period",
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"
} );
// Add a column for the first gift date ( that matches criteria )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "LastGift",
HeaderText = "Last Gift in Period",
SortExpression = "LastGift"
} );
// Add a column for the last-ever gift date ( to any tax-deductible account )
gGiversGifts.Columns.Add(
new DateField
{
DataField = "LastEverGift",
HeaderText = "Last Gift Ever",
SortExpression = "LastEverGift"
} );
} ) );
// Wait for all the queries to finish
Task.WaitAll( qryTasks.ToArray() );
// If dataview was selected and it's being used to filter results people, not in dataview
if ( dataViewId.HasValue && rblDataViewAction.SelectedValue != "All" && dataViewPersonIds.Any() )
{
personInfoList = personInfoList.Where( c => dataViewPersonIds.Contains( c.Id ) ).ToList();
}
// 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 new PersonService( _rockContext )
.Queryable().AsNoTracking()
.Where( p => dataViewPersonIds.Contains( p.Id ) )
.Select( p => new
{
p.Id,
p.GivingId,
p.GivingLeaderId,
p.Guid,
p.NickName,
p.LastName,
p.Email
} ) )
{
// Check for a first ever gift date
var firstEverGiftDate = firstEverVals
.Where( f => f.Key == person.GivingId )
.Select( f => f.Value )
.FirstOrDefault();
// Check for a last ever gift date
var lastEverGiftDate = lastEverVals
.Where( f => f.Key == person.GivingId )
.Select( f => f.Value )
.FirstOrDefault();
var personInfo = new PersonInfo();
personInfo.Id = person.Id;
personInfo.Guid = person.Guid;
personInfo.NickName = person.NickName;
personInfo.LastName = person.LastName;
personInfo.Email = person.Email;
personInfo.GivingId = person.GivingId;
personInfo.IsGivingLeader = person.Id == person.GivingLeaderId;
personInfo.FirstEverGift = firstEverGiftDate;
personInfo.LastEverGift = lastEverGiftDate;
personInfoList.Add( personInfo );
}
}
// Filter out recs that don't match the view by
switch ( viewBy )
{
case GiversViewBy.Giver:
{
personInfoList = personInfoList.Where( p => p.IsGivingLeader ).ToList();
break;
}
case GiversViewBy.Adults:
{
personInfoList = personInfoList.Where( p => p.IsAdult ).ToList();
break;
}
case GiversViewBy.Children:
{
personInfoList = personInfoList.Where( p => p.IsChild ).ToList();
break;
}
}
// Add the first/last gift dates
foreach ( var personInfo in personInfoList )
{
if ( firstEverVals.ContainsKey( personInfo.GivingId ) )
{
personInfo.FirstEverGift = firstEverVals[personInfo.GivingId];
}
if ( lastEverVals.ContainsKey( personInfo.GivingId ) )
{
personInfo.LastEverGift = lastEverVals[personInfo.GivingId];
}
}
// Check to see if we're only showing first time givers
if ( radFirstTime.Checked )
{
personInfoList = personInfoList
.Where( p => p.IsFirstEverGift )
.ToList();
}
// Check to see if grid should display only people who gave a certain number of times and if so
// set the min value
if ( radByPattern.Checked )
{
int minCount = tbPatternXTimes.Text.AsInteger();
var previousGivingIds = new List<string>();
if ( cbPatternAndMissed.Checked )
{
var missedStart = drpPatternDateRange.LowerValue;
var missedEnd = drpPatternDateRange.UpperValue;
if ( missedStart.HasValue && missedEnd.HasValue )
{
// Get the givingleaderids that gave any amount during the pattern's date range. These
// are needed so that we know who to exclude from the result set
previousGivingIds = new FinancialTransactionDetailService( _rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.Transaction.TransactionDateTime.HasValue &&
d.Transaction.TransactionDateTime.Value >= missedStart.Value &&
d.Transaction.TransactionDateTime.Value < missedEnd.Value &&
( accountIds.Any() && accountIds.Contains( d.AccountId ) || d.Account.IsTaxDeductible ) &&
d.Amount != 0.0M )
.Select( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId )
.ToList();
}
}
personInfoList = personInfoList
.Where( p =>
!previousGivingIds.Contains( p.GivingId ) &&
p.NumberGifts >= minCount )
.ToList();
}
// Add account summary info
foreach ( DataRow row in dtAccountSummary.Rows )
{
if ( !DBNull.Value.Equals( row["GivingId"] ) &&
!DBNull.Value.Equals( row["AccountId"] ) &&
!DBNull.Value.Equals( row["Amount"] ) )
{
string givingId = row["GivingId"].ToString();
int accountId = (int)row["AccountId"];
decimal amount = (decimal)row["Amount"];
foreach ( var personInfo in personInfoList.Where( p => p.GivingId == givingId ) )
{
personInfo.AccountAmounts.AddOrIgnore( accountId, amount );
}
}
}
// Calculate Total
if ( viewBy == GiversViewBy.Giver )
{
pnlTotal.Visible = true;
decimal amountTotal = personInfoList.Sum( p => p.TotalAmount );
lTotal.Text = amountTotal.FormatAsCurrency();
}
else
{
pnlTotal.Visible = false;
}
var qry = personInfoList.AsQueryable();
if ( gGiversGifts.SortProperty != null )
{
if ( gGiversGifts.SortProperty.Property.StartsWith( "Account" ) )
{
int? accountId = gGiversGifts.SortProperty.Property.Substring( 8 ).AsIntegerOrNull();
if ( accountId.HasValue )
{
foreach ( var personInfo in personInfoList )
{
personInfo.SortAmount = personInfo.AccountAmounts.ContainsKey( accountId.Value ) ?
personInfo.AccountAmounts[accountId.Value] : 0.0M;
if ( gGiversGifts.SortProperty.Direction == SortDirection.Ascending )
{
gGiversGifts.DataSource = personInfoList.OrderBy( p => p.SortAmount ).ToList();
}
else
{
gGiversGifts.DataSource = personInfoList.OrderByDescending( p => p.SortAmount ).ToList();
}
}
}
else
{
gGiversGifts.DataSource = qry.OrderBy( p => p.LastName ).ThenBy( p => p.NickName ).ToList();
}
}
else
{
gGiversGifts.DataSource = qry.Sort( gGiversGifts.SortProperty ).ToList();
}
}
else
{
gGiversGifts.DataSource = qry.OrderBy( p => p.LastName ).ThenBy( p => p.NickName ).ToList();
}
gGiversGifts.DataBind();
}
