/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { if (Person != null) { var familyIds = families.Select(f => f.Key).ToList(); var qry = new HistoryService(new RockContext()).Queryable("CreatedByPersonAlias.Person") .Where(h => (h.EntityTypeId == personEntityTypeId && h.EntityId == Person.Id) || (h.EntityTypeId == groupEntityTypeId && familyIds.Contains(h.EntityId))); int?categoryId = gfSettings.GetUserPreference("Category").AsIntegerOrNull(); if (categoryId.HasValue) { qry = qry.Where(a => a.CategoryId == categoryId.Value); } string summary = gfSettings.GetUserPreference("Summary Contains"); if (!string.IsNullOrWhiteSpace(summary)) { qry = qry.Where(h => h.Summary.Contains(summary)); } int personId = int.MinValue; if (int.TryParse(gfSettings.GetUserPreference("Who"), out personId)) { qry = qry.Where(h => h.CreatedByPersonAlias.PersonId == personId); } var drp = new DateRangePicker(); drp.DelimitedValues = gfSettings.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { qry = qry.Where(h => h.CreatedDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); qry = qry.Where(h => h.CreatedDateTime < upperDate); } SortProperty sortProperty = gHistory.SortProperty; if (sortProperty != null) { qry = qry.Sort(sortProperty); } else { qry = qry.OrderByDescending(t => t.CreatedDateTime); } // Combine history records that were saved at the same time var histories = new List <History>(); foreach (var history in qry) { var existingHistory = histories .Where(h => h.CreatedByPersonAliasId == history.CreatedByPersonAliasId && h.CreatedDateTime == history.CreatedDateTime && h.EntityTypeId == history.EntityTypeId && h.EntityId == history.EntityId && h.CategoryId == history.CategoryId && h.RelatedEntityTypeId == history.RelatedEntityTypeId && h.RelatedEntityId == history.RelatedEntityId).FirstOrDefault(); if (existingHistory != null) { existingHistory.Summary += "<br/>" + history.Summary; } else { histories.Add(history); } } gHistory.DataSource = histories.Select(h => new { Id = h.Id, CategoryId = h.CategoryId, Category = h.Category != null ? h.Category.Name : "", EntityTypeId = h.EntityTypeId, EntityId = h.EntityId, Caption = h.Caption ?? string.Empty, Summary = h.Summary, RelatedEntityTypeId = h.RelatedEntityTypeId ?? 0, RelatedEntityId = h.RelatedEntityId ?? 0, CreatedByPersonId = h.CreatedByPersonAlias != null ? h.CreatedByPersonAlias.PersonId : 0, PersonName = h.CreatedByPersonAlias != null && h.CreatedByPersonAlias.Person != null ? h.CreatedByPersonAlias.Person.NickName + " " + h.CreatedByPersonAlias.Person.LastName : "", CreatedDateTime = h.CreatedDateTime }).ToList(); gHistory.EntityTypeId = EntityTypeCache.Read <History>().Id; gHistory.DataBind(); } }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { var rockContext = new RockContext(); var pledgeService = new FinancialPledgeService(rockContext); var sortProperty = gPledges.SortProperty; var pledges = pledgeService.Queryable(); Person person = null; if (TargetPerson != null) { person = TargetPerson; } else { int?personId = gfPledges.GetUserPreference("Person").AsIntegerOrNull(); if (personId.HasValue && ppFilterPerson.Visible) { person = new PersonService(rockContext).Get(personId.Value); } } if (person != null) { // if a person is specified, get pledges for that person ( and also anybody in their GivingUnit ) pledges = pledges.Where(p => p.PersonAlias.Person.GivingId == person.GivingId); } // get the accounts and make sure they still exist by checking the database var accountIds = gfPledges.GetUserPreference("Accounts").Split(',').AsIntegerList(); accountIds = new FinancialAccountService(rockContext).GetByIds(accountIds).Select(a => a.Id).ToList(); if (accountIds.Any() && apFilterAccount.Visible) { pledges = pledges.Where(p => p.AccountId.HasValue && accountIds.Contains(p.AccountId.Value)); } // Date Range var drp = new DateRangePicker(); drp.DelimitedValues = gfPledges.GetUserPreference("Date Range"); var filterStartDate = drp.LowerValue ?? DateTime.MinValue; var filterEndDate = drp.UpperValue ?? DateTime.MaxValue; if (filterEndDate != DateTime.MaxValue) { filterEndDate = filterEndDate.AddDays(1); } /**** * Include any pledges whose Start/EndDates overlap with the Filtered Date Range * * * Pledge1 Range 1/1/2011 - 12/31/2011 * * Pledge2 Range 1/1/0000 - 1/1/9999 * * Pledge3 Range 6/1/2011 - 6/1/2012 * * Filter1 Range 1/1/2010 - 1/1/2013 * * * All Pledges should show * Filter1 Range 1/1/2012 - 1/1/2013 * * * Pledge2 and Pledge3 should show * Filter2 Range 5/1/2012 - 5/2/2012 * * * Pledge2 and Pledge3 should show * Filter3 Range 5/1/2012 - 1/1/9999 * * * Pledge2 and Pledge3 should show * Filter4 Range 5/1/2010 - 5/1/2010 * * * Pledge2 should show ***/ // exclude pledges that start after the filter's end date or end before the filter's start date if (drpDates.Visible) { pledges = pledges.Where(p => !(p.StartDate > filterEndDate) && !(p.EndDate < filterStartDate)); } // Last Modified drp.DelimitedValues = gfPledges.GetUserPreference("Last Modified"); filterStartDate = drp.LowerValue ?? DateTime.MinValue; filterEndDate = drp.UpperValue ?? DateTime.MaxValue; if (filterEndDate != DateTime.MaxValue) { filterEndDate = filterEndDate.AddDays(1); } if (drpLastModifiedDates.Visible) { pledges = pledges.Where(p => !(p.ModifiedDateTime >= filterEndDate) && !(p.ModifiedDateTime <= filterStartDate)); } gPledges.DataSource = sortProperty != null?pledges.Sort(sortProperty).ToList() : pledges.OrderBy(p => p.AccountId).ToList(); gPledges.DataBind(); }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { int?metricId = hfMetricId.Value.AsIntegerOrNull(); if (!metricId.HasValue) { return; } var rockContext = new RockContext(); SortProperty sortProperty = gMetricValues.SortProperty; MetricValueService metricValueService = new MetricValueService(rockContext); var qry = metricValueService.Queryable().Include(a => a.MetricValuePartitions); qry = qry.Where(a => a.MetricId == metricId); var metricValuePartitionsColumn = gMetricValues.Columns.OfType <RockLiteralField>().FirstOrDefault(a => a.ID == "lMetricValuePartitions"); var metric = new MetricService(rockContext).Get(metricId ?? 0); metricValuePartitionsColumn.Visible = metric != null && metric.MetricPartitions.Any(a => a.EntityTypeId.HasValue); var drp = new DateRangePicker(); drp.DelimitedValues = gfMetricValues.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { qry = qry.Where(a => a.MetricValueDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); qry = qry.Where(a => a.MetricValueDateTime < upperDate); } var metricValueType = gfMetricValues.GetUserPreference("Goal/Measure").ConvertToEnumOrNull <MetricValueType>(); if (metricValueType.HasValue) { qry = qry.Where(a => a.MetricValueType == metricValueType.Value); } var entityTypeEntityUserPreference = gfMetricValues.GetUserPreference(this.EntityTypeEntityPreferenceKey) ?? string.Empty; var entityTypeEntityList = entityTypeEntityUserPreference.Split(',').Select(a => a.Split('|')).Where(a => a.Length == 2).Select(a => new { EntityTypeId = a[0].AsIntegerOrNull(), EntityId = a[1].AsIntegerOrNull() }); foreach (var entityTypeEntity in entityTypeEntityList) { if (entityTypeEntity.EntityTypeId.HasValue && entityTypeEntity.EntityId.HasValue) { qry = qry.Where(a => a.MetricValuePartitions.Any(x => x.MetricPartition.EntityTypeId == entityTypeEntity.EntityTypeId && x.EntityId == entityTypeEntity.EntityId)); } } if (sortProperty != null) { qry = qry.Sort(sortProperty); } else { qry = qry.OrderByDescending(s => s.MetricValueDateTime).ThenBy(s => s.YValue).ThenBy(s => s.XValue).ThenByDescending(s => s.ModifiedDateTime); } gMetricValues.SetLinqDataSource(qry); gMetricValues.DataBind(); }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { var rockContext = new RockContext(); var pledgeService = new FinancialPledgeService(rockContext); var sortProperty = gPledges.SortProperty; var pledges = pledgeService.Queryable(); Person person = null; if (TargetPerson != null) { person = TargetPerson; } else { int?personId = gfPledges.GetUserPreference("Person").AsIntegerOrNull(); if (personId.HasValue && ppFilterPerson.Visible) { person = new PersonService(rockContext).Get(personId.Value); } } if (person != null) { // if a person is specified, get pledges for that person ( and also anybody in their GivingUnit ) pledges = pledges.Where(p => p.PersonAlias.Person.GivingId == person.GivingId); } // Filter by configured limit accounts if specified. var accountGuids = GetAttributeValue("Accounts").SplitDelimitedValues().AsGuidList(); if (accountGuids.Any()) { pledges = pledges.Where(p => accountGuids.Contains(p.Account.Guid)); } // get the accounts and make sure they still exist by checking the database var accountIds = gfPledges.GetUserPreference("Accounts").Split(',').AsIntegerList(); accountIds = new FinancialAccountService(rockContext).GetByIds(accountIds).Select(a => a.Id).ToList(); if (accountIds.Any() && apFilterAccount.Visible) { pledges = pledges.Where(p => p.AccountId.HasValue && accountIds.Contains(p.AccountId.Value)); } // Date Range DateRange filterDateRange = DateRangePicker.CalculateDateRangeFromDelimitedValues(gfPledges.GetUserPreference("Date Range")); var filterStartDate = filterDateRange.Start ?? DateTime.MinValue; var filterEndDate = filterDateRange.End ?? DateTime.MaxValue; /**** * Include any pledges whose Start/EndDates overlap with the Filtered Date Range * * * Pledge1 Range 1/1/2011 - 12/31/2011 * * Pledge2 Range 1/1/0000 - 1/1/9999 * * Pledge3 Range 6/1/2011 - 6/1/2012 * * Filter1 Range 1/1/2010 - 1/1/2013 * * * All Pledges should show * Filter1 Range 1/1/2012 - 1/1/2013 * * * Pledge2 and Pledge3 should show * Filter2 Range 5/1/2012 - 5/2/2012 * * * Pledge2 and Pledge3 should show * Filter3 Range 5/1/2012 - 1/1/9999 * * * Pledge2 and Pledge3 should show * Filter4 Range 5/1/2010 - 5/1/2010 * * * Pledge2 should show ***/ // exclude pledges that start after the filter's end date or end before the filter's start date if (drpDates.Visible && (filterDateRange.Start.HasValue || filterDateRange.End.HasValue)) { pledges = pledges.Where(p => !(p.StartDate > filterEndDate) && !(p.EndDate < filterStartDate)); } // Filter query by any configured attribute filters if (AvailableAttributes != null && AvailableAttributes.Any()) { foreach (var attribute in AvailableAttributes) { var filterControl = phAttributeFilters.FindControl("filter_" + attribute.Id.ToString()); pledges = attribute.FieldType.Field.ApplyAttributeQueryFilter(pledges, filterControl, attribute, pledgeService, Rock.Reporting.FilterMode.SimpleFilter); } } // Last Modified DateRange filterModifiedDateRange = DateRangePicker.CalculateDateRangeFromDelimitedValues(gfPledges.GetUserPreference("Last Modified")); var filterModifiedStartDate = filterModifiedDateRange.Start ?? DateTime.MinValue; var filterModifiedEndDate = filterModifiedDateRange.End ?? DateTime.MaxValue; if (drpLastModifiedDates.Visible && (filterModifiedDateRange.Start.HasValue || filterModifiedDateRange.End.HasValue)) { pledges = pledges.Where(p => !(p.ModifiedDateTime >= filterModifiedEndDate) && !(p.ModifiedDateTime <= filterModifiedStartDate)); } gPledges.DataSource = sortProperty != null?pledges.Sort(sortProperty).ToList() : pledges.OrderBy(p => p.AccountId).ToList(); gPledges.DataBind(); var showAccountSummary = this.GetAttributeValue("ShowAccountSummary").AsBoolean(); if (showAccountSummary || TargetPerson == null) { pnlSummary.Visible = true; var summaryList = pledges .GroupBy(a => a.AccountId) .Select(a => new AccountSummaryRow { AccountId = a.Key.Value, TotalAmount = a.Sum(x => x.TotalAmount), Name = a.Where(p => p.Account != null).Select(p => p.Account.Name).FirstOrDefault(), Order = a.Where(p => p.Account != null).Select(p => p.Account.Order).FirstOrDefault() }).ToList(); var grandTotalAmount = (summaryList.Count > 0) ? summaryList.Sum(a => a.TotalAmount) : 0; lGrandTotal.Text = grandTotalAmount.FormatAsCurrency(); rptAccountSummary.DataSource = summaryList.Select(a => new { a.Name, TotalAmount = a.TotalAmount.FormatAsCurrency() }).ToList(); rptAccountSummary.DataBind(); } else { pnlSummary.Visible = false; } }
/// <summary> /// Handles the DisplayFilterValue event of the gfBatchFilter control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="Rock.Web.UI.Controls.GridFilter.DisplayFilterValueArgs"/> instance containing the event data.</param> protected void gfBatchFilter_DisplayFilterValue(object sender, Rock.Web.UI.Controls.GridFilter.DisplayFilterValueArgs e) { switch (e.Key) { case "Row Limit": { // row limit filter was removed, so hide it just in case e.Value = null; break; } case "Date Range": { e.Value = DateRangePicker.FormatDelimitedValues(e.Value); break; } case "Status": { var status = e.Value.ConvertToEnumOrNull <BatchStatus>(); if (status.HasValue) { e.Value = status.ConvertToString(); } else { e.Value = string.Empty; } break; } case "Contains Transaction Type": { var transactionTypeValueId = e.Value.AsIntegerOrNull(); if (transactionTypeValueId.HasValue) { var transactionTypeValue = DefinedValueCache.Read(transactionTypeValueId.Value); e.Value = transactionTypeValue != null?transactionTypeValue.ToString() : string.Empty; } else { e.Value = string.Empty; } break; } case "Campus": { var campus = CampusCache.Read(e.Value.AsInteger()); if (campus != null) { e.Value = campus.Name; } else { e.Value = string.Empty; } break; } } }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { var rockContext = new RockContext(); var communications = new CommunicationService(rockContext) .Queryable("MediumEntityType,Sender,Reviewer") .Where(c => c.Status != CommunicationStatus.Transient); string subject = rFilter.GetUserPreference("Subject"); if (!string.IsNullOrWhiteSpace(subject)) { communications = communications.Where(c => c.Subject.Contains(subject)); } Guid entityTypeGuid = Guid.Empty; if (Guid.TryParse(rFilter.GetUserPreference("Medium"), out entityTypeGuid)) { communications = communications.Where(c => c.MediumEntityType != null && c.MediumEntityType.Guid.Equals(entityTypeGuid)); } string status = rFilter.GetUserPreference("Status"); if (!string.IsNullOrWhiteSpace(status)) { var communicationStatus = (CommunicationStatus)System.Enum.Parse(typeof(CommunicationStatus), status); communications = communications.Where(c => c.Status == communicationStatus); } if (canApprove) { int personId = 0; if (int.TryParse(rFilter.GetUserPreference("Created By"), out personId) && personId != 0) { communications = communications .Where(c => c.SenderPersonAlias != null && c.SenderPersonAlias.PersonId == personId); } } else { communications = communications .Where(c => c.SenderPersonAliasId.HasValue && c.SenderPersonAliasId.Value == CurrentPersonAliasId); } string content = rFilter.GetUserPreference("Content"); if (!string.IsNullOrWhiteSpace(content)) { communications = communications.Where(c => c.MediumDataJson.Contains(content)); } var drp = new DateRangePicker(); drp.DelimitedValues = rFilter.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { communications = communications.Where(a => a.ReviewedDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); communications = communications.Where(a => a.ReviewedDateTime < upperDate); } var recipients = new CommunicationRecipientService(rockContext).Queryable(); var queryable = communications .Select(c => new CommunicationItem { Id = c.Id, Communication = c, Recipients = recipients .Where(r => r.CommunicationId == c.Id) .Count(), PendingRecipients = recipients .Where(r => r.CommunicationId == c.Id && r.Status == CommunicationRecipientStatus.Pending) .Count(), CancelledRecipients = recipients .Where(r => r.CommunicationId == c.Id && r.Status == CommunicationRecipientStatus.Cancelled) .Count(), FailedRecipients = recipients .Where(r => r.CommunicationId == c.Id && r.Status == CommunicationRecipientStatus.Failed) .Count(), DeliveredRecipients = recipients .Where(r => r.CommunicationId == c.Id && (r.Status == CommunicationRecipientStatus.Delivered || r.Status == CommunicationRecipientStatus.Opened)) .Count(), OpenedRecipients = recipients .Where(r => r.CommunicationId == c.Id && r.Status == CommunicationRecipientStatus.Opened) .Count() }); var sortProperty = gCommunication.SortProperty; if (sortProperty != null) { queryable = queryable.Sort(sortProperty); } else { queryable = queryable.OrderByDescending(c => c.Communication.Id); } // Get the medium names var mediums = new Dictionary <int, string>(); foreach (var item in Rock.Communication.MediumContainer.Instance.Components.Values) { var entityType = item.Value.EntityType; mediums.Add(entityType.Id, item.Metadata.ComponentName); } var communicationItems = queryable.ToList(); foreach (var c in communicationItems) { c.MediumName = mediums.ContainsKey(c.Communication.MediumEntityTypeId ?? 0) ? mediums[c.Communication.MediumEntityTypeId ?? 0] : c.Communication.MediumEntityType.FriendlyName; } gCommunication.DataSource = communicationItems; gCommunication.DataBind(); }
/// <summary> /// Binds the grid. /// </summary> private List <ContentChannelItem> GetItems(RockContext rockContext, out bool isFiltered) { isFiltered = false; var items = new List <ContentChannelItem>(); if (_channelId.HasValue) { ContentChannelItemService contentItemService = new ContentChannelItemService(rockContext); var contentItems = contentItemService.Queryable() .Where(c => c.ContentChannelId == _channelId.Value); var drp = new DateRangePicker(); drp.DelimitedValues = gfFilter.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { isFiltered = true; contentItems = contentItems.Where(i => (i.ExpireDateTime.HasValue && i.ExpireDateTime.Value >= drp.LowerValue.Value) || (!i.ExpireDateTime.HasValue && i.StartDateTime >= drp.LowerValue.Value)); } if (drp.UpperValue.HasValue) { isFiltered = true; DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); contentItems = contentItems.Where(i => i.StartDateTime <= upperDate); } var status = gfFilter.GetUserPreference("Status").ConvertToEnumOrNull <ContentChannelItemStatus>(); if (status.HasValue) { isFiltered = true; contentItems = contentItems.Where(i => i.Status == status); } string title = gfFilter.GetUserPreference("Title"); if (!string.IsNullOrWhiteSpace(title)) { isFiltered = true; contentItems = contentItems.Where(i => i.Title.Contains(title)); } // if the block has a person context filter requests for just them if (_person != null) { isFiltered = true; contentItems = contentItems.Where(i => i.CreatedByPersonAlias != null && i.CreatedByPersonAlias.PersonId == _person.Id); } // TODO: Checking security of every item will take longer and longer as more items are added. // Eventually we should implement server-side paging so that we only need to check security for // the items on the current page foreach (var item in contentItems.ToList()) { if (item.IsAuthorized(Rock.Security.Authorization.VIEW, CurrentPerson)) { items.Add(item); } else { isFiltered = true; } } } if (_manuallyOrdered && !isFiltered) { return(items.OrderBy(i => i.Order).ToList()); } else { return(items); } }
/// <summary> /// Binds the attempts grid. /// </summary> protected void BindGrid() { var achievementType = GetAchievementType(); if (achievementType != null) { lHeading.Text = string.Format("{0} Attempts", achievementType.Name); } else { lHeading.Text = "Achievement Attempts"; } var query = GetAttemptsQuery(); // Filter by First Name var firstName = tbFirstName.Text; if (!firstName.IsNullOrWhiteSpace()) { query = query.Where(saa => saa.Streak.PersonAlias.Person.FirstName.StartsWith(firstName) || saa.Streak.PersonAlias.Person.NickName.StartsWith(firstName)); } // Filter by Last Name var lastName = tbLastName.Text; if (!lastName.IsNullOrWhiteSpace()) { query = query.Where(saa => saa.Streak.PersonAlias.Person.LastName.StartsWith(lastName)); } // Filter by start Date var startDateRange = DateRangePicker.CalculateDateRangeFromDelimitedValues(drpStartDate.DelimitedValues); if (startDateRange.Start.HasValue) { query = query.Where(saa => saa.AchievementAttemptStartDateTime >= startDateRange.Start.Value); } if (startDateRange.End.HasValue) { query = query.Where(saa => saa.AchievementAttemptStartDateTime <= startDateRange.End.Value); } // Filter by achievement type var achievementTypeId = statPicker.SelectedValue.AsIntegerOrNull(); if (achievementTypeId.HasValue) { query = query.Where(saa => saa.StreakTypeAchievementTypeId == achievementTypeId.Value); } // Filter by status var status = ddlStatus.SelectedValue; if (!status.IsNullOrWhiteSpace()) { status = status.ToLower().Trim(); if (status == "successful") { query = query.Where(saa => saa.IsSuccessful); } else if (status == "unsuccessful") { query = query.Where(saa => !saa.IsSuccessful && saa.IsClosed); } else if (status == "open") { query = query.Where(saa => !saa.IsClosed); } } var viewModelQuery = query.Select(saa => new AttemptViewModel { Id = saa.Id, PersonId = saa.Streak.PersonAlias.PersonId, LastName = saa.Streak.PersonAlias.Person.LastName, NickName = saa.Streak.PersonAlias.Person.NickName, StartDate = saa.AchievementAttemptStartDateTime, Person = saa.Streak.PersonAlias.Person, EndDate = saa.AchievementAttemptEndDateTime, IsSuccessful = saa.IsSuccessful, IsClosed = saa.IsClosed, Progress = saa.Progress, AchievementName = saa.StreakTypeAchievementType.Name }); // Sort the grid var sortProperty = gAttempts.SortProperty; if (sortProperty != null) { viewModelQuery = viewModelQuery.Sort(sortProperty); } else { viewModelQuery = viewModelQuery .OrderBy(avm => avm.IsClosed) .OrderByDescending(avm => avm.StartDate) .ThenBy(avm => avm.LastName); } gAttempts.SetLinqDataSource(viewModelQuery); gAttempts.DataBind(); }
/// <inheritdoc/> public override string GetTextValue(string value, Dictionary <string, string> configurationValues) { return(DateRangePicker.FormatDelimitedValues(value) ?? value); }
/// <summary> /// Adds the grid columns. /// </summary> /// <param name="dataTable">The data table.</param> private void AddGridColumns(Grid grid, DataTable dataTable) { bool showColumns = GetAttributeValue("ShowColumns").AsBoolean(); var columnList = GetAttributeValue("Columns").SplitDelimitedValues().ToList(); var encryptedFields = GetAttributeValue("EncryptedFields").SplitDelimitedValues().ToList(); int rowsToEval = 10; if (dataTable.Rows.Count < 10) { rowsToEval = dataTable.Rows.Count; } grid.Columns.Clear(); if (!string.IsNullOrWhiteSpace(grid.PersonIdField)) { grid.Columns.Add(new SelectField()); } GridFilterColumnLookup = new Dictionary <Control, string>(); foreach (DataColumn dataTableColumn in dataTable.Columns) { if (columnList.Count > 0 && ((showColumns && !columnList.Contains(dataTableColumn.ColumnName, StringComparer.OrdinalIgnoreCase)) || (!showColumns && columnList.Contains(dataTableColumn.ColumnName, StringComparer.OrdinalIgnoreCase)))) { continue; } BoundField bf = new BoundField(); var splitCaseName = dataTableColumn.ColumnName.SplitCase(); if (dataTableColumn.DataType == typeof(bool)) { bf = new BoolField(); if (GridFilter != null) { var id = "ddl" + dataTableColumn.ColumnName.RemoveSpecialCharacters(); var filterControl = new RockDropDownList() { Label = splitCaseName, ID = id }; GridFilterColumnLookup.Add(filterControl, dataTableColumn.ColumnName); filterControl.Items.Add(BoolToString(null)); filterControl.Items.Add(BoolToString(true)); filterControl.Items.Add(BoolToString(false)); GridFilter.Controls.Add(filterControl); var value = GridFilter.GetUserPreference(id); if (value != null) { filterControl.SetValue(value); } } } else if (dataTableColumn.DataType == typeof(DateTime)) { bf = new DateField(); for (int i = 0; i < rowsToEval; i++) { object dateObj = dataTable.Rows[i][dataTableColumn]; if (dateObj is DateTime) { DateTime dateTime = ( DateTime )dateObj; if (dateTime.TimeOfDay.Seconds != 0) { bf = new DateTimeField(); break; } } } if (GridFilter != null) { var id = "drp" + dataTableColumn.ColumnName.RemoveSpecialCharacters(); var filterControl = new DateRangePicker() { Label = splitCaseName, ID = id, }; GridFilterColumnLookup.Add(filterControl, dataTableColumn.ColumnName); GridFilter.Controls.Add(filterControl); var value = GridFilter.GetUserPreference(id); if (value != null) { DateTime upper; DateTime lower; if (DateRangePicker.TryParse(value, out lower, out upper)) { filterControl.LowerValue = lower; filterControl.UpperValue = upper; } } } } else { if (encryptedFields.Contains(dataTableColumn.ColumnName)) { bf = new EncryptedField(); } bf.HtmlEncode = false; if (GridFilter != null) { var id = "tb" + dataTableColumn.ColumnName.RemoveSpecialCharacters(); var filterControl = new RockTextBox() { Label = splitCaseName, ID = id }; GridFilterColumnLookup.Add(filterControl, dataTableColumn.ColumnName); GridFilter.Controls.Add(filterControl); var key = filterControl.ID; var value = GridFilter.GetUserPreference(key); if (value != null) { filterControl.Text = value; } } } bf.DataField = dataTableColumn.ColumnName; bf.SortExpression = dataTableColumn.ColumnName; bf.HeaderText = splitCaseName; grid.Columns.Add(bf); } }
/// <summary> /// Handles the DisplayFilterValue event of the gfNcoaFilter control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="Rock.Web.UI.Controls.GridFilter.DisplayFilterValueArgs"/> instance containing the event data.</param> protected void gfNcoaFilter_DisplayFilterValue(object sender, Rock.Web.UI.Controls.GridFilter.DisplayFilterValueArgs e) { switch (e.Key) { case "Move Date": case "NCOA Processed Date": { e.Value = DateRangePicker.FormatDelimitedValues(e.Value); break; } case "Processed": { var processed = e.Value.ConvertToEnumOrNull <Processed>(); if (processed.HasValue && processed.Value != Processed.All) { e.Value = processed.ConvertToString(); } else { e.Value = string.Empty; } break; } case "Move Type": { var moveType = e.Value.ConvertToEnumOrNull <MoveType>(); if (moveType.HasValue) { e.Value = moveType.ConvertToString(); } else { e.Value = string.Empty; } break; } case "Address Status": { var addressStatus = e.Value.ConvertToEnumOrNull <AddressStatus>(); if (addressStatus.HasValue) { e.Value = addressStatus.ConvertToString(); } else { e.Value = string.Empty; } break; } case "Invalid Reason": { var invalidReason = e.Value.ConvertToEnumOrNull <AddressInvalidReason>(); if (invalidReason.HasValue) { e.Value = invalidReason.ConvertToString(); } else { e.Value = string.Empty; } break; } case "Campus": { var campus = CampusCache.Get(e.Value.AsInteger()); if (campus != null) { e.Value = campus.Name; } else { e.Value = string.Empty; } break; } } }
/// <summary> /// Gets the query. Set the timeout to 90 seconds in case the user /// has not set any filters and they've imported N years worth of /// batch data into Rock. /// </summary> /// <param name="rockContext">The rock context.</param> /// <returns></returns> private IOrderedQueryable <FinancialBatch> GetQuery(RockContext rockContext) { var batchService = new FinancialBatchService(rockContext); rockContext.Database.CommandTimeout = 90; var qry = batchService.Queryable() .Where(b => b.BatchStartDateTime.HasValue); // filter by date string dateRangeValue = gfBatchFilter.GetUserPreference("Date Range"); if (!string.IsNullOrWhiteSpace(dateRangeValue)) { var drp = new DateRangePicker(); drp.DelimitedValues = dateRangeValue; if (drp.LowerValue.HasValue) { qry = qry.Where(b => b.BatchStartDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { var endOfDay = drp.UpperValue.Value.AddDays(1); qry = qry.Where(b => b.BatchStartDateTime < endOfDay); } } // filter by status var status = gfBatchFilter.GetUserPreference("Status").ConvertToEnumOrNull <BatchStatus>(); if (status.HasValue) { qry = qry.Where(b => b.Status == status); } // filter by batches that contain transactions of the specified transaction type var transactionTypeValueId = gfBatchFilter.GetUserPreference("Contains Transaction Type").AsIntegerOrNull(); if (transactionTypeValueId.HasValue) { qry = qry.Where(a => a.Transactions.Any(t => t.TransactionTypeValueId == transactionTypeValueId.Value)); } // filter by title string title = gfBatchFilter.GetUserPreference("Title"); if (!string.IsNullOrEmpty(title)) { qry = qry.Where(batch => batch.Name.Contains(title)); } // filter by accounting code if (tbAccountingCode.Visible) { string accountingCode = gfBatchFilter.GetUserPreference("Accounting Code"); if (!string.IsNullOrEmpty(accountingCode)) { qry = qry.Where(batch => batch.AccountingSystemCode.Contains(accountingCode)); } } // filter by campus var campus = CampusCache.Read(gfBatchFilter.GetUserPreference("Campus").AsInteger()); if (campus != null) { qry = qry.Where(b => b.CampusId == campus.Id); } // Filter query by any configured attribute filters if (AvailableAttributes != null && AvailableAttributes.Any()) { var attributeValueService = new AttributeValueService(rockContext); var parameterExpression = attributeValueService.ParameterExpression; foreach (var attribute in AvailableAttributes) { var filterControl = phAttributeFilters.FindControl("filter_" + attribute.Id.ToString()); if (filterControl != null) { var filterValues = attribute.FieldType.Field.GetFilterValues(filterControl, attribute.QualifierValues, Rock.Reporting.FilterMode.SimpleFilter); var expression = attribute.FieldType.Field.AttributeFilterExpression(attribute.QualifierValues, filterValues, parameterExpression); if (expression != null) { var attributeValues = attributeValueService .Queryable() .Where(v => v.Attribute.Id == attribute.Id); attributeValues = attributeValues.Where(parameterExpression, expression, null); qry = qry.Where(w => attributeValues.Select(v => v.EntityId).Contains(w.Id)); } } } } IOrderedQueryable <FinancialBatch> sortedQry = null; SortProperty sortProperty = gBatchList.SortProperty; if (sortProperty != null) { switch (sortProperty.Property) { case "TransactionCount": { if (sortProperty.Direction == SortDirection.Ascending) { sortedQry = qry.OrderBy(b => b.Transactions.Count()); } else { sortedQry = qry.OrderByDescending(b => b.Transactions.Count()); } break; } case "TransactionAmount": { if (sortProperty.Direction == SortDirection.Ascending) { sortedQry = qry.OrderBy(b => b.Transactions.Sum(t => (decimal?)(t.TransactionDetails.Sum(d => (decimal?)d.Amount) ?? 0.0M)) ?? 0.0M); } else { sortedQry = qry.OrderByDescending(b => b.Transactions.Sum(t => (decimal?)(t.TransactionDetails.Sum(d => (decimal?)d.Amount) ?? 0.0M)) ?? 0.0M); } break; } default: { sortedQry = qry.Sort(sortProperty); break; } } } else { sortedQry = qry .OrderByDescending(b => b.BatchStartDateTime) .ThenBy(b => b.Name); } return(sortedQry); }
/// <summary> /// Handles the DisplayFilterValue event of the gfBatchFilter control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="Rock.Web.UI.Controls.GridFilter.DisplayFilterValueArgs"/> instance containing the event data.</param> protected void gfBatchFilter_DisplayFilterValue(object sender, Rock.Web.UI.Controls.GridFilter.DisplayFilterValueArgs e) { if (AvailableAttributes != null) { var attribute = AvailableAttributes.FirstOrDefault(a => "Attribute_" + a.Key == e.Key); if (attribute != null) { try { var values = JsonConvert.DeserializeObject <List <string> >(e.Value); e.Value = attribute.FieldType.Field.FormatFilterValues(attribute.QualifierValues, values); return; } catch { // intentionally ignore } } } switch (e.Key) { case "Row Limit": { // row limit filter was removed, so hide it just in case e.Value = null; break; } case "Date Range": { e.Value = DateRangePicker.FormatDelimitedValues(e.Value); break; } case "Status": { var status = e.Value.ConvertToEnumOrNull <BatchStatus>(); if (status.HasValue) { e.Value = status.ConvertToString(); } else { e.Value = string.Empty; } break; } case "Contains Transaction Type": { var transactionTypeValueId = e.Value.AsIntegerOrNull(); if (transactionTypeValueId.HasValue) { var transactionTypeValue = DefinedValueCache.Read(transactionTypeValueId.Value); e.Value = transactionTypeValue != null?transactionTypeValue.ToString() : string.Empty; } else { e.Value = string.Empty; } break; } case "Campus": { var campus = CampusCache.Read(e.Value.AsInteger()); if (campus != null) { e.Value = campus.Name; } else { e.Value = string.Empty; } break; } } }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { int? metricId = hfMetricId.Value.AsIntegerOrNull(); if ( !metricId.HasValue ) { return; } var rockContext = new RockContext(); SortProperty sortProperty = gMetricValues.SortProperty; MetricValueService metricValueService = new MetricValueService( rockContext ); var qry = metricValueService.Queryable(); qry = qry.Where( a => a.MetricId == metricId ); var entityTypeNameColumn = gMetricValues.Columns.OfType<RockLiteralField>().FirstOrDefault( a => a.ID == "lEntityTypeName" ); var metric = new MetricService( rockContext ).Get( metricId ?? 0 ); entityTypeNameColumn.Visible = metric != null && metric.EntityTypeId.HasValue; if ( metric != null ) { _entityNameLookup = new Dictionary<int, string>(); var entityTypeCache = EntityTypeCache.Read( metric.EntityTypeId ?? 0 ); if ( entityTypeCache != null ) { entityTypeNameColumn.HeaderText = entityTypeCache.FriendlyName; IQueryable<IEntity> entityQry = null; if ( entityTypeCache.GetEntityType() == typeof( Rock.Model.Group ) ) { // special case for Group since there could be a very large number (especially if you include families), so limit to GroupType.ShowInGroupList entityQry = new GroupService( rockContext ).Queryable().Where( a => a.GroupType.ShowInGroupList ); } else { Type[] modelType = { entityTypeCache.GetEntityType() }; Type genericServiceType = typeof( Rock.Data.Service<> ); Type modelServiceType = genericServiceType.MakeGenericType( modelType ); var serviceInstance = Activator.CreateInstance( modelServiceType, new object[] { rockContext } ) as IService; MethodInfo qryMethod = serviceInstance.GetType().GetMethod( "Queryable", new Type[] { } ); entityQry = qryMethod.Invoke( serviceInstance, new object[] { } ) as IQueryable<IEntity>; } if ( entityQry != null ) { var entityList = entityQry.ToList(); foreach ( var e in entityList ) { _entityNameLookup.AddOrReplace( e.Id, e.ToString() ); } } } } var drp = new DateRangePicker(); drp.DelimitedValues = gfMetricValues.GetUserPreference( "Date Range" ); if ( drp.LowerValue.HasValue ) { qry = qry.Where( a => a.MetricValueDateTime >= drp.LowerValue.Value ); } if ( drp.UpperValue.HasValue ) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays( 1 ); qry = qry.Where( a => a.MetricValueDateTime < upperDate ); } var metricValueType = gfMetricValues.GetUserPreference( "Goal/Measure" ).ConvertToEnumOrNull<MetricValueType>(); if ( metricValueType.HasValue ) { qry = qry.Where( a => a.MetricValueType == metricValueType.Value ); } var entityParts = gfMetricValues.GetUserPreference( this.EntityPreferenceKey ).Split( '|' ); if ( entityParts.Length == 2 ) { if ( entityParts[0].AsInteger() == metric.EntityTypeId ) { var entityId = entityParts[1].AsIntegerOrNull(); if ( entityId.HasValue ) { qry = qry.Where( a => a.EntityId == entityId ); } } } if ( sortProperty != null ) { qry = qry.Sort( sortProperty ); } else { qry = qry.OrderBy( s => s.Order ).ThenByDescending( s => s.MetricValueDateTime ).ThenBy( s => s.YValue ).ThenBy( s => s.XValue ).ThenByDescending( s => s.ModifiedDateTime ); } gMetricValues.SetLinqDataSource( qry ); gMetricValues.DataBind(); }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { if (_entity != null) { var entityTypeCache = EntityTypeCache.Get(_entity.GetType(), false); if (entityTypeCache != null) { var rockContext = new RockContext(); var historyService = new HistoryService(rockContext); IQueryable <History> qry; if (entityTypeCache.Id == EntityTypeCache.GetId <Rock.Model.Person>()) { // If this is History for a Person, also include any History for any of their Families int? groupEntityTypeId = EntityTypeCache.GetId <Rock.Model.Group>(); List <int> familyIds = (_entity as Person).GetFamilies().Select(a => a.Id).ToList(); qry = historyService.Queryable().Include(a => a.CreatedByPersonAlias.Person) .Where(h => (h.EntityTypeId == entityTypeCache.Id && h.EntityId == _entity.Id) || (h.EntityTypeId == groupEntityTypeId && familyIds.Contains(h.EntityId))); // as per issue #1594, if relatedEntityType is an Attribute then check View Authorization var attributeEntity = EntityTypeCache.Get(Rock.SystemGuid.EntityType.ATTRIBUTE.AsGuid()); var personAttributes = new AttributeService(rockContext).GetByEntityTypeId(entityTypeCache.Id).ToList().Select(a => AttributeCache.Get(a)); var allowedAttributeIds = personAttributes.Where(a => a.IsAuthorized(Rock.Security.Authorization.VIEW, CurrentPerson)).Select(a => a.Id).ToList(); qry = qry.Where(a => (a.RelatedEntityTypeId == attributeEntity.Id) ? allowedAttributeIds.Contains(a.RelatedEntityId.Value) : true); } else { qry = historyService.Queryable().Include(a => a.CreatedByPersonAlias.Person) .Where(h => (h.EntityTypeId == entityTypeCache.Id && h.EntityId == _entity.Id)); } var historyCategories = new CategoryService(rockContext).GetByEntityTypeId(EntityTypeCache.GetId <Rock.Model.History>()).ToList().Select(a => CategoryCache.Get(a)); var allowedCategoryIds = historyCategories.Where(a => a.IsAuthorized(Rock.Security.Authorization.VIEW, CurrentPerson)).Select(a => a.Id).ToList(); qry = qry.Where(a => allowedCategoryIds.Contains(a.CategoryId)); int?categoryId = gfSettings.GetUserPreference("Category").AsIntegerOrNull(); if (categoryId.HasValue) { qry = qry.Where(a => a.CategoryId == categoryId.Value); } int?personId = gfSettings.GetUserPreference("Who").AsIntegerOrNull(); if (personId.HasValue) { qry = qry.Where(h => h.CreatedByPersonAlias.PersonId == personId.Value); } var drp = new DateRangePicker(); drp.DelimitedValues = gfSettings.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { qry = qry.Where(h => h.CreatedDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); qry = qry.Where(h => h.CreatedDateTime < upperDate); } // Combine history records that were saved at the same time var historySummaryList = historyService.GetHistorySummary(qry); string summary = gfSettings.GetUserPreference("Summary Contains"); if (!string.IsNullOrWhiteSpace(summary)) { historySummaryList = historySummaryList.Where(h => h.HistoryList.Any(x => x.SummaryHtml.IndexOf(summary, StringComparison.OrdinalIgnoreCase) >= 0)).ToList(); } SortProperty sortProperty = gHistory.SortProperty; if (sortProperty != null) { historySummaryList = historySummaryList.AsQueryable().Sort(sortProperty).ToList(); } else { historySummaryList = historySummaryList.OrderByDescending(t => t.CreatedDateTime).ToList(); } gHistory.DataSource = historySummaryList; gHistory.EntityTypeId = EntityTypeCache.Get <History>().Id; gHistory.DataBind(); } } }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { if (_channelId.HasValue) { ContentChannelItemService contentItemService = new ContentChannelItemService(new RockContext()); var contentItems = contentItemService.Queryable() .Where(c => c.ContentChannelId == _channelId.Value); var drp = new DateRangePicker(); drp.DelimitedValues = gfFilter.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { contentItems = contentItems.Where(i => (i.ExpireDateTime.HasValue && i.ExpireDateTime.Value >= drp.LowerValue.Value) || (!i.ExpireDateTime.HasValue && i.StartDateTime >= drp.LowerValue.Value)); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); contentItems = contentItems.Where(i => i.StartDateTime <= upperDate); } var status = gfFilter.GetUserPreference("Status").ConvertToEnumOrNull <ContentChannelItemStatus>(); if (status.HasValue) { contentItems = contentItems.Where(i => i.Status == status); } string title = gfFilter.GetUserPreference("Title"); if (!string.IsNullOrWhiteSpace(title)) { contentItems = contentItems.Where(i => i.Title.Contains(title)); } // TODO: Checking security of every item will take longer and longer as more items are added. // Eventually we should implement server-side paging so that we only need to check security for // the items on the current page var items = new List <ContentChannelItem>(); foreach (var item in contentItems.ToList()) { if (item.IsAuthorized(Rock.Security.Authorization.VIEW, CurrentPerson)) { items.Add(item); } } SortProperty sortProperty = gItems.SortProperty; if (sortProperty != null) { items = items.AsQueryable().Sort(sortProperty).ToList(); } else { items = items.OrderByDescending(p => p.StartDateTime).ToList(); } gItems.ObjectList = new Dictionary <string, object>(); items.ForEach(i => gItems.ObjectList.Add(i.Id.ToString(), i)); gItems.EntityTypeId = EntityTypeCache.Read <ContentChannelItem>().Id; gItems.DataSource = items.Select(i => new { i.Id, i.Guid, i.Title, i.StartDateTime, i.ExpireDateTime, i.Priority, Status = DisplayStatus(i.Status), Occurrences = i.EventItemOccurrences.Any() }).ToList(); gItems.DataBind(); } }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid(bool isExporting = false) { int?personId = null; int?givingGroupId = null; bool validRequest = false; if (TargetPerson != null) { personId = TargetPerson.Id; givingGroupId = TargetPerson.GivingGroupId; validRequest = true; } else { int personEntityTypeId = EntityTypeCache.Read("Rock.Model.Person").Id; if (!ContextTypesRequired.Any(e => e.Id == personEntityTypeId)) { validRequest = true; } } if (validRequest) { var rockContext = new RockContext(); var qry = new FinancialScheduledTransactionService(rockContext) .Queryable("ScheduledTransactionDetails,FinancialPaymentDetail.CurrencyTypeValue,FinancialPaymentDetail.CreditCardTypeValue") .AsNoTracking(); // Valid Accounts var accountGuids = GetAttributeValue("Accounts").SplitDelimitedValues().AsGuidList(); if (accountGuids.Any()) { qry = qry.Where(t => t.ScheduledTransactionDetails.Any(d => accountGuids.Contains(d.Account.Guid))); } // Amount Range var nre = new NumberRangeEditor(); nre.DelimitedValues = gfSettings.GetUserPreference("Amount"); if (nre.LowerValue.HasValue) { qry = qry.Where(t => t.ScheduledTransactionDetails.Sum(d => d.Amount) >= nre.LowerValue.Value); } if (nre.UpperValue.HasValue) { qry = qry.Where(t => t.ScheduledTransactionDetails.Sum(d => d.Amount) <= nre.UpperValue.Value); } // Frequency int?frequencyTypeId = gfSettings.GetUserPreference("Frequency").AsIntegerOrNull(); if (frequencyTypeId.HasValue) { qry = qry.Where(t => t.TransactionFrequencyValueId == frequencyTypeId.Value); } // Date Range var drp = new DateRangePicker(); drp.DelimitedValues = gfSettings.GetUserPreference("Created"); if (drp.LowerValue.HasValue) { qry = qry.Where(t => t.CreatedDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); qry = qry.Where(t => t.CreatedDateTime < upperDate); } // Account Id int accountId = int.MinValue; if (int.TryParse(gfSettings.GetUserPreference("Account"), out accountId) && ddlAccount.Visible) { qry = qry.Where(t => t.ScheduledTransactionDetails.Any(d => d.AccountId == accountId)); } // Active only (no filter) if (string.IsNullOrWhiteSpace(gfSettings.GetUserPreference("Include Inactive"))) { qry = qry.Where(t => t.IsActive); } if (givingGroupId.HasValue) { // Person contributes with family qry = qry.Where(t => t.AuthorizedPersonAlias.Person.GivingGroupId == givingGroupId); } else if (personId.HasValue) { // Person contributes individually qry = qry.Where(t => t.AuthorizedPersonAlias.PersonId == personId); } SortProperty sortProperty = gList.SortProperty; if (sortProperty != null) { if (sortProperty.Property == "Amount") { if (sortProperty.Direction == SortDirection.Ascending) { qry = qry.OrderBy(t => t.ScheduledTransactionDetails.Sum(d => (decimal?)d.Amount) ?? 0.00M); } else { qry = qry.OrderByDescending(t => t.ScheduledTransactionDetails.Sum(d => (decimal?)d.Amount) ?? 0.0M); } } else { qry = qry.Sort(sortProperty); } } else { qry = qry .OrderBy(t => t.AuthorizedPersonAlias.Person.LastName) .ThenBy(t => t.AuthorizedPersonAlias.Person.NickName) .ThenByDescending(t => t.IsActive) .ThenByDescending(t => t.StartDate); } _isExporting = isExporting; gList.SetLinqDataSource <FinancialScheduledTransaction>(qry); gList.DataBind(); _isExporting = false; } }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { var queryable = new FinancialTransactionService().Queryable(); // Set up the selection filter if (_batch != null) { // If transactions are for a batch, the filter is hidden so only check the batch id queryable = queryable.Where(t => t.BatchId.HasValue && t.BatchId.Value == _batch.Id); } else { // otherwise set the selection based on filter settings if (_person != null) { queryable = queryable.Where(t => t.AuthorizedPersonId == _person.Id); } // Date Range var drp = new DateRangePicker(); drp.DelimitedValues = rFilter.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { queryable = queryable.Where(t => t.TransactionDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); queryable = queryable.Where(t => t.TransactionDateTime < upperDate); } // Amount Range var nre = new NumberRangeEditor(); nre.DelimitedValues = rFilter.GetUserPreference("Amount Range"); if (nre.LowerValue.HasValue) { queryable = queryable.Where(t => t.Amount >= nre.LowerValue.Value); } if (nre.UpperValue.HasValue) { queryable = queryable.Where(t => t.Amount <= nre.UpperValue.Value); } // Transaction Code string transactionCode = rFilter.GetUserPreference("Transaction Code"); if (!string.IsNullOrWhiteSpace(transactionCode)) { queryable = queryable.Where(t => t.TransactionCode == transactionCode.Trim()); } // Account Id int accountId = int.MinValue; if (int.TryParse(rFilter.GetUserPreference("Account"), out accountId)) { queryable = queryable.Where(t => t.TransactionDetails.Any(d => d.AccountId == accountId)); } // Transaction Type int transactionTypeId = int.MinValue; if (int.TryParse(rFilter.GetUserPreference("Transaction Type"), out transactionTypeId)) { queryable = queryable.Where(t => t.TransactionTypeValueId == transactionTypeId); } // Currency Type int currencyTypeId = int.MinValue; if (int.TryParse(rFilter.GetUserPreference("Currency Type"), out currencyTypeId)) { queryable = queryable.Where(t => t.CurrencyTypeValueId == currencyTypeId); } // Credit Card Type int creditCardTypeId = int.MinValue; if (int.TryParse(rFilter.GetUserPreference("Credit Card Type"), out creditCardTypeId)) { queryable = queryable.Where(t => t.CreditCardTypeValueId == creditCardTypeId); } // Source Type int sourceTypeId = int.MinValue; if (int.TryParse(rFilter.GetUserPreference("Source Type"), out sourceTypeId)) { queryable = queryable.Where(t => t.SourceTypeValueId == sourceTypeId); } } SortProperty sortProperty = rGridTransactions.SortProperty; if (sortProperty != null) { queryable = queryable.Sort(sortProperty); } else { queryable = queryable.OrderBy(t => t.TransactionDateTime); } rGridTransactions.DataSource = queryable.AsNoTracking().ToList(); rGridTransactions.DataBind(); }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { using (var rockContext = new RockContext()) { var qry = new BackgroundCheckService(rockContext) .Queryable("PersonAlias.Person").AsNoTracking() .Where(g => g.PersonAlias != null && g.PersonAlias.Person != null) .Where(g => g.ForeignId == 1); // FirstName string firstName = fRequest.GetUserPreference("First Name"); if (!string.IsNullOrWhiteSpace(firstName)) { qry = qry.Where(t => t.PersonAlias.Person.FirstName.StartsWith(firstName) || t.PersonAlias.Person.NickName.StartsWith(firstName)); } // LastName string lastName = fRequest.GetUserPreference("Last Name"); if (!string.IsNullOrWhiteSpace(lastName)) { qry = qry.Where(t => t.PersonAlias.Person.LastName.StartsWith(lastName)); } // Request Date Range var drpRequestDates = new DateRangePicker(); drpRequestDates.DelimitedValues = fRequest.GetUserPreference("Request Date Range"); if (drpRequestDates.LowerValue.HasValue) { qry = qry.Where(t => t.RequestDate >= drpRequestDates.LowerValue.Value); } if (drpRequestDates.UpperValue.HasValue) { DateTime upperDate = drpRequestDates.UpperValue.Value.Date.AddDays(1); qry = qry.Where(t => t.RequestDate < upperDate); } // Response Date Range var drpResponseDates = new DateRangePicker(); drpResponseDates.DelimitedValues = fRequest.GetUserPreference("Response Date Range"); if (drpResponseDates.LowerValue.HasValue) { qry = qry.Where(t => t.ResponseDate >= drpResponseDates.LowerValue.Value); } if (drpResponseDates.UpperValue.HasValue) { DateTime upperDate = drpResponseDates.UpperValue.Value.Date.AddDays(1); qry = qry.Where(t => t.ResponseDate < upperDate); } // Record Found string recordFound = fRequest.GetUserPreference("Record Found"); if (!string.IsNullOrWhiteSpace(recordFound)) { if (recordFound == "Yes") { qry = qry.Where(t => t.RecordFound.HasValue && t.RecordFound.Value); } else if (recordFound == "No") { qry = qry.Where(t => t.RecordFound.HasValue && !t.RecordFound.Value); } } List <Rock.Model.BackgroundCheck> items = null; SortProperty sortProperty = gRequest.SortProperty; if (sortProperty != null) { if (sortProperty.Property == "Name") { if (sortProperty.Direction == SortDirection.Descending) { items = qry.OrderByDescending(q => q.PersonAlias.Person.LastName).ThenBy(q => q.PersonAlias.Person.FirstName).ToList(); } else { items = qry.OrderBy(q => q.PersonAlias.Person.LastName).ThenBy(q => q.PersonAlias.Person.FirstName).ToList(); } } else { items = qry.Sort(sortProperty).ToList(); } } else { items = qry.OrderByDescending(d => d.RequestDate).ToList(); } gRequest.DataSource = items.Select(b => new BackgroundCheckRow { Name = b.PersonAlias.Person.LastName + ", " + b.PersonAlias.Person.NickName, Id = b.Id, PersonId = b.PersonAlias.PersonId, HasWorkflow = b.WorkflowId.HasValue, RequestDate = b.RequestDate, ResponseDate = b.ResponseDate, RecordFound = b.RecordFound, RecordFoundLabel = b.RecordFound.HasValue ? ( b.RecordFound.Value ? "<span class='label label-warning'>Yes</span>" : "<span class='label label-success'>No</span>") : string.Empty, HasResponseData = !string.IsNullOrWhiteSpace(b.ResponseData), ResponseDocumentText = b.ResponseDocumentId.HasValue ? "<i class='fa fa-file-pdf-o fa-lg'></i>" : "", ResponseDocumentId = b.ResponseDocumentId ?? 0 }).ToList(); gRequest.DataBind(); } }
/// <summary> /// Binds the filter. /// </summary> protected void BindFilter() { string dateRangePreference = rFilter.GetUserPreference(MakeKeyUniqueToGroup("Date Range")); if (string.IsNullOrWhiteSpace(dateRangePreference)) { // set the dateRangePreference to force rFilter_DisplayFilterValue to show our default three month limit dateRangePreference = ","; rFilter.SaveUserPreference(MakeKeyUniqueToGroup("Date Range"), "Date Range", dateRangePreference); } var dateRange = DateRangePicker.CalculateDateRangeFromDelimitedValues(dateRangePreference); // if there is no start date, default to three months ago to minimize the chance of loading too much data drpDates.LowerValue = dateRange.Start ?? RockDateTime.Today.AddMonths(-3); drpDates.UpperValue = dateRange.End; if (_group != null) { var grouplocations = _group.GroupLocations .Where(l => l.Location.Name != null && l.Location.Name != string.Empty) .ToList(); var locations = new Dictionary <string, string> { { string.Empty, string.Empty } }; var locationService = new LocationService(_rockContext); foreach (var location in grouplocations.Select(l => l.Location)) { if (!locations.ContainsKey(location.Id.ToString())) { locations.Add(location.Id.ToString(), locationService.GetPath(location.Id)); } var parentLocation = location.ParentLocation; while (parentLocation != null) { string key = string.Format("P{0}", parentLocation.Id); if (!locations.ContainsKey(key)) { locations.Add(key, locationService.GetPath(parentLocation.Id)); } parentLocation = parentLocation.ParentLocation; } } if (locations.Any()) { ddlLocation.Visible = true; gOccurrences.Columns[2].Visible = true; ddlLocation.DataSource = locations.OrderBy(l => l.Value); ddlLocation.DataBind(); ddlLocation.SetValue(rFilter.GetUserPreference(MakeKeyUniqueToGroup("Location"))); } else { ddlLocation.Visible = false; gOccurrences.Columns[2].Visible = false; } var schedules = new Dictionary <int, string> { { 0, string.Empty } }; grouplocations.SelectMany(l => l.Schedules).OrderBy(s => s.Name).ToList() .ForEach(s => schedules.AddOrIgnore(s.Id, s.Name)); if (schedules.Any()) { ddlSchedule.Visible = true; gOccurrences.Columns[1].Visible = true; ddlSchedule.DataSource = schedules; ddlSchedule.DataBind(); ddlSchedule.SetValue(rFilter.GetUserPreference(MakeKeyUniqueToGroup("Schedule"))); } else { ddlSchedule.Visible = false; gOccurrences.Columns[1].Visible = false; } } }
/// <summary> /// Gfs the pledges_ display filter value. /// </summary> /// <param name="sender">The sender.</param> /// <param name="e">The e.</param> /// <exception cref="System.NotImplementedException"></exception> protected void gfPledges_DisplayFilterValue(object sender, GridFilter.DisplayFilterValueArgs e) { if (AvailableAttributes != null) { var attribute = AvailableAttributes.FirstOrDefault(a => "Attribute_" + a.Key == e.Key); if (attribute != null) { try { var values = JsonConvert.DeserializeObject <List <string> >(e.Value); e.Value = attribute.FieldType.Field.FormatFilterValues(attribute.QualifierValues, values); return; } catch { // intentionally ignore } } } switch (e.Key) { case "Date Range": if (drpDates.Visible) { e.Value = DateRangePicker.FormatDelimitedValues(e.Value); } else { e.Value = string.Empty; } break; case "Last Modified": if (drpLastModifiedDates.Visible) { e.Value = DateRangePicker.FormatDelimitedValues(e.Value); } else { e.Value = string.Empty; } break; case "Person": int?personId = e.Value.AsIntegerOrNull(); if (personId != null && ppFilterPerson.Visible) { var person = new PersonService(new RockContext()).Get(personId.Value); if (person != null) { e.Value = person.ToString(); } else { e.Value = string.Empty; } } else { e.Value = string.Empty; } break; case "Accounts": var accountIdList = e.Value.Split(',').AsIntegerList(); if (accountIdList.Any() && apFilterAccount.Visible) { var service = new FinancialAccountService(new RockContext()); var accounts = service.GetByIds(accountIdList); if (accounts != null && accounts.Any()) { e.Value = accounts.Select(a => a.Name).ToList().AsDelimited(","); } else { e.Value = string.Empty; } } else { e.Value = string.Empty; } break; default: e.Value = string.Empty; break; } }
/// <summary> /// Gfs the pledges_ display filter value. /// </summary> /// <param name="sender">The sender.</param> /// <param name="e">The e.</param> /// <exception cref="System.NotImplementedException"></exception> protected void gfPledges_DisplayFilterValue(object sender, GridFilter.DisplayFilterValueArgs e) { switch (e.Key) { case "Date Range": if (drpDates.Visible) { e.Value = DateRangePicker.FormatDelimitedValues(e.Value); } else { e.Value = string.Empty; } break; case "Last Modified": if (drpLastModifiedDates.Visible) { e.Value = DateRangePicker.FormatDelimitedValues(e.Value); } else { e.Value = string.Empty; } break; case "Person": int?personId = e.Value.AsIntegerOrNull(); if (personId != null && ppFilterPerson.Visible) { var person = new PersonService(new RockContext()).Get(personId.Value); if (person != null) { e.Value = person.ToString(); } else { e.Value = string.Empty; } } else { e.Value = string.Empty; } break; case "Accounts": var accountIdList = e.Value.Split(',').AsIntegerList(); if (accountIdList.Any() && apFilterAccount.Visible) { var service = new FinancialAccountService(new RockContext()); var accounts = service.GetByIds(accountIdList); if (accounts != null && accounts.Any()) { e.Value = accounts.Select(a => a.Name).ToList().AsDelimited(","); } else { e.Value = string.Empty; } } else { e.Value = string.Empty; } break; default: e.Value = string.Empty; break; } }
/// <summary> /// Binds the group members grid. /// </summary> protected void BindInstancesGrid() { if (_template != null) { pnlInstances.Visible = true; lHeading.Text = string.Format("{0} Instances", _template.Name); var rockContext = new RockContext(); var template = new RegistrationTemplateService(rockContext).Get(_template.Id); var waitListCol = gInstances.ColumnsOfType <RockBoundField>().Where(f => f.DataField == "WaitList").First(); waitListCol.Visible = template != null && template.WaitListEnabled; var instanceService = new RegistrationInstanceService(rockContext); var qry = instanceService.Queryable().AsNoTracking() .Where(i => i.RegistrationTemplateId == _template.Id); // Date Range var drp = new DateRangePicker(); drp.DelimitedValues = rFilter.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { qry = qry.Where(i => i.StartDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); qry = qry.Where(i => i.StartDateTime < upperDate); } string statusFilter = rFilter.GetUserPreference("Active Status"); if (!string.IsNullOrWhiteSpace(statusFilter)) { if (statusFilter == "inactive") { qry = qry.Where(i => i.IsActive == false); } else { qry = qry.Where(i => i.IsActive == true); } } SortProperty sortProperty = gInstances.SortProperty; if (sortProperty != null) { qry = qry.Sort(sortProperty); } else { qry = qry.OrderByDescending(a => a.StartDateTime); } var instanceQry = qry.Select(i => new { i.Id, i.Guid, i.Name, i.StartDateTime, i.EndDateTime, i.IsActive, Registrants = i.Registrations.Where(r => !r.IsTemporary).SelectMany(r => r.Registrants).Where(r => !r.OnWaitList).Count(), WaitList = i.Registrations.Where(r => !r.IsTemporary).SelectMany(r => r.Registrants).Where(r => r.OnWaitList).Count() }); gInstances.SetLinqDataSource(instanceQry); gInstances.DataBind(); } else { pnlInstances.Visible = false; } }
/// <summary> /// Binds the grid. /// </summary> protected void BindGrid() { var attendanceService = new AttendanceService(new RockContext()); var attendanceQuery = attendanceService.Queryable(); if (_person != null) { attendanceQuery = attendanceQuery.Where(a => a.PersonId == _person.Id); } else if (_group != null) { attendanceQuery = attendanceQuery.Where(a => a.GroupId == _group.Id); } var attendanceList = attendanceQuery.ToList(); var qry = attendanceList.AsQueryable() .Select(a => new { Location = a.Location, Schedule = a.Schedule, FullName = a.Person.FullName, Group = a.Group, StartDateTime = a.StartDateTime, EndDateTime = a.EndDateTime }); // Filter by Date Range var drp = new DateRangePicker(); drp.DelimitedValues = rFilter.GetUserPreference("Date Range"); if (drp.LowerValue.HasValue) { qry = qry.Where(t => t.StartDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1); qry = qry.Where(t => t.EndDateTime < upperDate); } // Filter by Person if (ddlPeople.SelectedIndex > 0) { if (ddlPeople.SelectedValue.ToLower() != "all") { qry = qry.Where(h => h.FullName == ddlPeople.SelectedValue); } } // Filter by Group if (ddlGroups.SelectedIndex > 0) { if (ddlGroups.SelectedValue.ToLower() != "all") { qry = qry.Where(h => h.Group.Name == ddlGroups.SelectedValue); } } // Filter by Schedule if (ddlSchedules.SelectedIndex > 0) { if (ddlSchedules.SelectedValue.ToLower() != "all") { qry = qry.Where(h => h.Schedule.Name == ddlSchedules.SelectedValue); } } SortProperty sortProperty = gHistory.SortProperty; if (sortProperty != null) { qry = qry.Sort(sortProperty); } else { qry = qry.OrderByDescending(p => p.StartDateTime); } gHistory.DataSource = qry.ToList(); gHistory.DataBind(); }
/// <summary> /// Gets the query. /// </summary> /// <returns></returns> private IOrderedQueryable <FinancialBatch> GetQuery() { var batchService = new FinancialBatchService(new RockContext()); var qry = batchService.Queryable() .Where(b => b.BatchStartDateTime.HasValue); // filter by date string dateRangeValue = gfBatchFilter.GetUserPreference("Date Range"); if (!string.IsNullOrWhiteSpace(dateRangeValue)) { var drp = new DateRangePicker(); drp.DelimitedValues = dateRangeValue; if (drp.LowerValue.HasValue) { qry = qry.Where(b => b.BatchStartDateTime >= drp.LowerValue.Value); } if (drp.UpperValue.HasValue) { var endOfDay = drp.UpperValue.Value.AddDays(1); qry = qry.Where(b => b.BatchStartDateTime < endOfDay); } } // filter by status var status = gfBatchFilter.GetUserPreference("Status").ConvertToEnumOrNull <BatchStatus>(); if (status.HasValue) { qry = qry.Where(b => b.Status == status); } // filter by batches that contain transactions of the specified transaction type var transactionTypeValueId = gfBatchFilter.GetUserPreference("Contains Transaction Type").AsIntegerOrNull(); if (transactionTypeValueId.HasValue) { qry = qry.Where(a => a.Transactions.Any(t => t.TransactionTypeValueId == transactionTypeValueId.Value)); } // filter by title string title = gfBatchFilter.GetUserPreference("Title"); if (!string.IsNullOrEmpty(title)) { qry = qry.Where(batch => batch.Name.StartsWith(title)); } // filter by accounting code if (tbAccountingCode.Visible) { string accountingCode = gfBatchFilter.GetUserPreference("Accounting Code"); if (!string.IsNullOrEmpty(accountingCode)) { qry = qry.Where(batch => batch.AccountingSystemCode.StartsWith(accountingCode)); } } // filter by campus var campus = CampusCache.Read(gfBatchFilter.GetUserPreference("Campus").AsInteger()); if (campus != null) { qry = qry.Where(b => b.CampusId == campus.Id); } IOrderedQueryable <FinancialBatch> sortedQry = null; SortProperty sortProperty = gBatchList.SortProperty; if (sortProperty != null) { switch (sortProperty.Property) { case "TransactionCount": { if (sortProperty.Direction == SortDirection.Ascending) { sortedQry = qry.OrderBy(b => b.Transactions.Count()); } else { sortedQry = qry.OrderByDescending(b => b.Transactions.Count()); } break; } case "TransactionAmount": { if (sortProperty.Direction == SortDirection.Ascending) { sortedQry = qry.OrderBy(b => b.Transactions.Sum(t => (decimal?)(t.TransactionDetails.Sum(d => (decimal?)d.Amount) ?? 0.0M)) ?? 0.0M); } else { sortedQry = qry.OrderByDescending(b => b.Transactions.Sum(t => (decimal?)(t.TransactionDetails.Sum(d => (decimal?)d.Amount) ?? 0.0M)) ?? 0.0M); } break; } default: { sortedQry = qry.Sort(sortProperty); break; } } } else { sortedQry = qry .OrderByDescending(b => b.BatchStartDateTime) .ThenBy(b => b.Name); } return(sortedQry); }
/// <summary> /// Rs the filter_ display filter value. /// </summary> /// <param name="sender">The sender.</param> /// <param name="e">The e.</param> /// <exception cref="System.NotImplementedException"></exception> protected void rFilter_DisplayFilterValue(object sender, GridFilter.DisplayFilterValueArgs e) { var rockContext = new RockContext(); switch (e.Key) { case "Date Range": e.Value = DateRangePicker.FormatDelimitedValues(e.Value); break; case "Person": int?personId = e.Value.AsIntegerOrNull(); e.Value = null; if (personId.HasValue) { var person = new PersonService(rockContext).Get(personId.Value); if (person != null) { e.Value = person.ToString(); } } break; case "Group": int?groupId = e.Value.AsIntegerOrNull(); e.Value = null; if (groupId.HasValue) { var group = new GroupService(rockContext).Get(groupId.Value); if (group != null) { e.Value = group.ToString(); } } break; case "Schedule": int?scheduleId = e.Value.AsIntegerOrNull(); e.Value = null; if (scheduleId.HasValue) { var schedule = new ScheduleService(rockContext).Get(scheduleId.Value); if (schedule != null) { e.Value = schedule.Name; } } break; case "Attended": if (e.Value == "1") { e.Value = "Did Attend"; } else if (e.Value == "0") { e.Value = "Did Not Attend"; } else { e.Value = null; } break; default: e.Value = null; break; } }