/// <summary> /// Gets a list of all the connection requests for the current person that have a critical status /// </summary> /// <param name="rockContext"></param> /// <returns></returns> private List <ConnectionRequest> GetConnections(RockContext rockContext) { var connections = RockPage.GetSharedItem("ActiveConnections") as List <ConnectionRequest>; if (connections == null) { var query = new ConnectionRequestService(rockContext).Queryable() .Where( r => r.ConnectionState == ConnectionState.Active || (r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate < RockDateTime.Now)) .Where(r => r.ConnectorPersonAliasId == CurrentPersonAliasId); var types = GetAttributeValue(AttributeKey.ConnectionTypes).SplitDelimitedValues().ToList(); if (types.Count() > 0) { query = query.Where(r => types.Contains(r.ConnectionOpportunity.ConnectionType.Guid.ToString())); } if (GetAttributeValue(AttributeKey.CriticalConnectionsOnly).AsBoolean()) { query = query.Where(r => r.ConnectionStatus.IsCritical == true); } connections = query.ToList(); RockPage.SaveSharedItem("ActiveConnections", connections); } return(connections); }
private void BindData() { string contents = GetAttributeValue(AttributeKey.Contents); string appRoot = ResolveRockUrl("~/"); string themeRoot = ResolveRockUrl("~~/"); contents = contents.Replace("~~/", themeRoot).Replace("~/", appRoot); var includedConnectionTypeGuids = GetAttributeValue(AttributeKey.IncludedConnectionTypes).SplitDelimitedValues().AsGuidList(); var excludedConnectionTypeGuids = GetAttributeValue(AttributeKey.ExcludedConnectionTypes).SplitDelimitedValues().AsGuidList(); DateTime midnightToday = RockDateTime.Today.AddDays(1); var rockContext = new RockContext(); var connectionRequests = new ConnectionRequestService(rockContext).Queryable() .Where(a => a.ConnectorPersonAlias != null && a.ConnectorPersonAlias.PersonId == CurrentPersonId) .Where(r => r.ConnectionState == ConnectionState.Active || (r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday)); if (includedConnectionTypeGuids.Any()) { connectionRequests = connectionRequests.Where(a => includedConnectionTypeGuids.Contains(a.ConnectionOpportunity.ConnectionType.Guid)); } if (excludedConnectionTypeGuids.Any()) { connectionRequests = connectionRequests.Where(a => !excludedConnectionTypeGuids.Contains(a.ConnectionOpportunity.ConnectionType.Guid)); } connectionRequests = connectionRequests.OrderBy(r => r.PersonAlias.Person.LastName).ThenBy(r => r.PersonAlias.Person.NickName); var mergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields(this.RockPage, this.CurrentPerson, new Rock.Lava.CommonMergeFieldsOptions { GetLegacyGlobalMergeFields = false }); mergeFields.Add("ConnectionRequests", connectionRequests.ToList()); var lastActivityNotes = connectionRequests.Select(r => new { ConnectionRequestId = r.Id, LastActivity = r.ConnectionRequestActivities.OrderByDescending( a => a.CreatedDateTime).FirstOrDefault() }).ToDictionary(k => k.ConnectionRequestId, v => v.LastActivity); mergeFields.Add("LastActivityLookup", lastActivityNotes); Dictionary <string, object> linkedPages = new Dictionary <string, object>(); linkedPages.Add("DetailPage", LinkedPageRoute(AttributeKey.DetailPage)); mergeFields.Add("LinkedPages", linkedPages); lContents.Text = contents.ResolveMergeFields(mergeFields); }
/// <summary> /// Gets the summary data. /// </summary> private void GetSummaryData() { var midnightToday = RockDateTime.Today.AddDays(1); SummaryState = new List <ConnectionTypeSummary>(); var rockContext = new RockContext(); var connectionOpportunityService = new ConnectionOpportunityService(rockContext); var followingService = new FollowingService(rockContext); var opportunityEntityTypeId = EntityTypeCache.Get <ConnectionOpportunity>().Id; var followedOpportunityIds = followingService.Queryable() .AsNoTracking() .Where(f => f.PersonAliasId == CurrentPersonAliasId && f.EntityTypeId == opportunityEntityTypeId && string.IsNullOrEmpty(f.PurposeKey)) .Select(f => f.EntityId) .ToList(); var opportunityQuery = connectionOpportunityService.Queryable() .AsNoTracking() .Where(co => co.IsActive && co.ConnectionType.IsActive); var typeFilter = GetAttributeValue(AttributeKey.ConnectionTypes).SplitDelimitedValues().AsGuidList(); if (typeFilter.Any()) { opportunityQuery = opportunityQuery.Where(o => typeFilter.Contains(o.ConnectionType.Guid)); } var selfAssignedOpportunities = new List <int>(); var isSelfAssignedOpportunitiesQueried = false; var opportunities = opportunityQuery.ToList(); // Loop through opportunities foreach (var opportunity in opportunities) { // Check to see if person can edit the opportunity because of edit rights to this block or edit rights to // the opportunity bool canEdit = UserCanEdit || opportunity.IsAuthorized(Authorization.EDIT, CurrentPerson); bool campusSpecificConnector = false; var campusIds = new List <int>(); if (CurrentPersonId.HasValue) { // Check to see if person belongs to any connector group that is not campus specific if (!canEdit) { canEdit = opportunity .ConnectionOpportunityConnectorGroups .Any(g => !g.CampusId.HasValue && g.ConnectorGroup != null && g.ConnectorGroup.Members.Any(m => m.PersonId == CurrentPersonId.Value)); } // If user is not yet authorized to edit the opportunity, check to see if they are a member of one of the // campus-specific connector groups for the opportunity, and note the campus if (!canEdit) { foreach (var groupCampus in opportunity .ConnectionOpportunityConnectorGroups .Where(g => g.CampusId.HasValue && g.ConnectorGroup != null && g.ConnectorGroup.Members.Any(m => m.PersonId == CurrentPersonId.Value))) { campusSpecificConnector = true; canEdit = true; campusIds.Add(groupCampus.CampusId.Value); } } } if (opportunity.ConnectionType.EnableRequestSecurity && !isSelfAssignedOpportunitiesQueried) { isSelfAssignedOpportunitiesQueried = true; selfAssignedOpportunities = new ConnectionRequestService(rockContext) .Queryable() .Where(a => a.ConnectorPersonAlias.PersonId == CurrentPersonId.Value) .Select(a => a.ConnectionOpportunityId) .Distinct() .ToList(); } var canView = opportunity.IsAuthorized(Authorization.VIEW, CurrentPerson) || (opportunity.ConnectionType.EnableRequestSecurity && selfAssignedOpportunities.Contains(opportunity.Id)); // Is user is authorized to view this opportunity type... if (canView) { // Check if the opportunity's type has been added to summary yet, and if not, add it var connectionTypeSummary = SummaryState.Where(c => c.Id == opportunity.ConnectionTypeId).FirstOrDefault(); if (connectionTypeSummary == null) { connectionTypeSummary = new ConnectionTypeSummary { Id = opportunity.ConnectionTypeId, Name = opportunity.ConnectionType.Name, EnableRequestSecurity = opportunity.ConnectionType.EnableRequestSecurity, ConnectionRequestDetailPageId = opportunity.ConnectionType.ConnectionRequestDetailPageId, ConnectionRequestDetailPageRouteId = opportunity.ConnectionType.ConnectionRequestDetailPageRouteId, Opportunities = new List <OpportunitySummary>(), IconMarkup = opportunity.ConnectionType.IconCssClass.IsNullOrWhiteSpace() ? string.Empty : $@"<i class=""{opportunity.ConnectionType.IconCssClass}""></i>", Order = opportunity.ConnectionType.Order }; SummaryState.Add(connectionTypeSummary); } // get list of idle requests (no activity in past X days) var connectionRequestsQry = new ConnectionRequestService(rockContext).Queryable().Where(a => a.ConnectionOpportunityId == opportunity.Id); if (cpCampusFilter.SelectedCampusId.HasValue) { connectionRequestsQry = connectionRequestsQry.Where(a => a.CampusId.HasValue && a.CampusId == cpCampusFilter.SelectedCampusId); } var currentDateTime = RockDateTime.Now; int activeRequestCount = connectionRequestsQry .Where(cr => cr.ConnectionState == ConnectionState.Active || (cr.ConnectionState == ConnectionState.FutureFollowUp && cr.FollowupDate.HasValue && cr.FollowupDate.Value < midnightToday) ) .Count(); // only show if the opportunity is active and there are active requests if (opportunity.IsActive || (!opportunity.IsActive && activeRequestCount > 0)) { // idle count is: // (the request is active OR future follow-up who's time has come) // AND // (where the activity is more than DaysUntilRequestIdle days old OR no activity but created more than DaysUntilRequestIdle days ago) List <int> idleConnectionRequests = connectionRequestsQry .Where(cr => ( cr.ConnectionState == ConnectionState.Active || (cr.ConnectionState == ConnectionState.FutureFollowUp && cr.FollowupDate.HasValue && cr.FollowupDate.Value < midnightToday) ) && ( (cr.ConnectionRequestActivities.Any() && cr.ConnectionRequestActivities.Max(ra => ra.CreatedDateTime) < SqlFunctions.DateAdd("day", -cr.ConnectionOpportunity.ConnectionType.DaysUntilRequestIdle, currentDateTime)) || (!cr.ConnectionRequestActivities.Any() && cr.CreatedDateTime < SqlFunctions.DateAdd("day", -cr.ConnectionOpportunity.ConnectionType.DaysUntilRequestIdle, currentDateTime)) ) ) .Select(a => a.Id).ToList(); // get list of requests that have a status that is considered critical. List <int> criticalConnectionRequests = connectionRequestsQry .Where(r => r.ConnectionStatus.IsCritical && ( r.ConnectionState == ConnectionState.Active || (r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday) ) ) .Select(a => a.Id).ToList(); // Add the opportunity var opportunitySummary = new OpportunitySummary { Id = opportunity.Id, Order = opportunity.Order, Name = opportunity.Name, IsActive = opportunity.IsActive, IconCssClass = opportunity.IconCssClass, IdleConnectionRequests = idleConnectionRequests, CriticalConnectionRequests = criticalConnectionRequests, DaysUntilRequestIdle = opportunity.ConnectionType.DaysUntilRequestIdle, CanEdit = canEdit, IsFollowed = followedOpportunityIds.Contains(opportunity.Id) }; // If the user is limited requests with specific campus(es) set the list, otherwise leave it to be null opportunitySummary.CampusSpecificConnector = campusSpecificConnector; opportunitySummary.ConnectorCampusIds = campusIds.Distinct().ToList(); connectionTypeSummary.Opportunities.Add(opportunitySummary); } } } // Get a list of all the authorized opportunity ids var allOpportunities = SummaryState.SelectMany(s => s.Opportunities).Select(o => o.Id).Distinct().ToList(); // Get all the active and past-due future followup request ids, and include the campus id and personid of connector var activeRequestsQry = new ConnectionRequestService(rockContext) .Queryable().AsNoTracking() .Where(r => allOpportunities.Contains(r.ConnectionOpportunityId) && (r.ConnectionState == ConnectionState.Active || (r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday))) .Select(r => new { r.Id, r.ConnectionOpportunityId, r.CampusId, ConnectorPersonId = r.ConnectorPersonAlias != null ? r.ConnectorPersonAlias.PersonId : -1 }); if (cpCampusFilter.SelectedCampusId.HasValue) { activeRequestsQry = activeRequestsQry.Where(a => a.CampusId.HasValue && a.CampusId == cpCampusFilter.SelectedCampusId); } var activeRequests = activeRequestsQry.ToList(); // Based on the active requests, set additional properties for each opportunity foreach (var opportunity in SummaryState.SelectMany(s => s.Opportunities)) { // Get the active requests for this opportunity that user is authorized to view (based on campus connector) var opportunityRequests = activeRequests .Where(r => r.ConnectionOpportunityId == opportunity.Id && ( !opportunity.CampusSpecificConnector || (r.CampusId.HasValue && opportunity.ConnectorCampusIds.Contains(r.CampusId.Value)) )) .ToList(); // The active requests assigned to the current person opportunity.AssignedToYouConnectionRequests = opportunityRequests.Where(r => r.ConnectorPersonId == CurrentPersonId).Select(a => a.Id).ToList(); // The active requests that are unassigned opportunity.UnassignedConnectionRequests = opportunityRequests.Where(r => r.ConnectorPersonId == -1).Select(a => a.Id).ToList(); // Flag indicating if current user is connector for any of the active types opportunity.HasActiveRequestsForConnector = opportunityRequests.Any(r => r.ConnectorPersonId == CurrentPersonId); // Total number of requests for opportunity/campus/connector opportunity.TotalRequests = opportunityRequests.Count(); } //Set the Idle tooltip var connectionTypes = opportunities.Where(o => allOpportunities.Contains(o.Id)).Select(o => o.ConnectionType).Distinct().ToList(); StringBuilder sb = new StringBuilder(); if (connectionTypes.Select(t => t.DaysUntilRequestIdle).Distinct().Count() == 1) { sb.Append(String.Format("Idle (no activity in {0} days)", connectionTypes.Select(t => t.DaysUntilRequestIdle).Distinct().First())); } else { sb.Append("Idle (no activity in several days)<br/><ul class='list-unstyled'>"); foreach (var connectionType in connectionTypes) { sb.Append(String.Format("<li>{0}: {1} days</li>", connectionType.Name, connectionType.DaysUntilRequestIdle)); } sb.Append("</ul>"); } var statusTemplate = GetAttributeValue(AttributeKey.StatusTemplate); var statusMergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields(this.RockPage); statusMergeFields.Add("ConnectionOpportunities", allOpportunities); statusMergeFields.Add("ConnectionTypes", connectionTypes); statusMergeFields.Add("IdleTooltip", sb.ToString().EncodeHtml()); lStatusBarContent.Text = statusTemplate.ResolveMergeFields(statusMergeFields); BindSummaryData(); }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { OpportunitySummary opportunitySummary = null; if (SelectedOpportunityId.HasValue) { opportunitySummary = SummaryState.SelectMany(t => t.Opportunities.Where(o => o.Id == SelectedOpportunityId.Value)).FirstOrDefault(); } if (opportunitySummary != null) { using (var rockContext = new RockContext()) { // Get queryable of all requests that belong to the selected opportunity, and user is authorized to view (based on security or connector group) var requests = new ConnectionRequestService(rockContext) .Queryable().AsNoTracking() .Where(r => r.ConnectionOpportunityId == SelectedOpportunityId.Value && ( !opportunitySummary.CampusSpecificConnector || (r.CampusId.HasValue && opportunitySummary.ConnectorCampusIds.Contains(r.CampusId.Value)) )); // Filter by Requester if (ppRequester.PersonId.HasValue) { requests = requests .Where(r => r.PersonAlias != null && r.PersonAlias.PersonId == ppRequester.PersonId.Value); } // Filter by Connector if (tglMyOpportunities.Checked) { requests = requests .Where(r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == CurrentPersonId); } else if (ppConnector.PersonId.HasValue) { requests = requests .Where(r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == ppConnector.PersonId.Value); } // Filter by State if (tglMyOpportunities.Checked) { requests = requests .Where(r => r.ConnectionState == ConnectionState.Active || (r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < _midnightToday)); } else { var states = new List <ConnectionState>(); bool futureFollowup = false; foreach (string stateValue in cblState.SelectedValues) { futureFollowup = futureFollowup || stateValue.AsInteger() == -2; var state = stateValue.ConvertToEnumOrNull <ConnectionState>(); if (state.HasValue) { states.Add(state.Value); } } if (futureFollowup || states.Any()) { requests = requests .Where(r => (futureFollowup && r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < _midnightToday) || states.Contains(r.ConnectionState)); } } // Filter by Status List <int> statusIds = cblStatus.SelectedValuesAsInt; if (statusIds.Any()) { requests = requests .Where(r => statusIds.Contains(r.ConnectionStatusId)); } // Filter by Campus List <int> campusIds = cblCampus.SelectedValuesAsInt; if (campusIds.Count > 0) { requests = requests .Where(r => r.Campus != null && campusIds.Contains(r.CampusId.Value)); } // Filter by Last Activity Note List <int> lastActivityIds = cblLastActivity.SelectedValuesAsInt; if (lastActivityIds.Any()) { requests = requests .Where(r => lastActivityIds.Contains( r.ConnectionRequestActivities.OrderByDescending(a => a.CreatedDateTime).Select(a => a.ConnectionActivityTypeId).FirstOrDefault())); } SortProperty sortProperty = gRequests.SortProperty; if (sortProperty != null) { requests = requests.Sort(sortProperty); } else { requests = requests .OrderBy(r => r.PersonAlias.Person.LastName) .ThenBy(r => r.PersonAlias.Person.NickName); } gRequests.DataSource = requests.ToList() .Select(r => new { r.Id, r.Guid, PersonId = r.PersonAlias.PersonId, Name = r.PersonAlias.Person.FullNameReversed, Campus = r.Campus, Group = r.AssignedGroup != null ? r.AssignedGroup.Name : "", Connector = r.ConnectorPersonAlias != null ? r.ConnectorPersonAlias.Person.FullName : "", LastActivity = FormatActivity(r.ConnectionRequestActivities.OrderByDescending(a => a.CreatedDateTime).FirstOrDefault()), LastActivityNote = gRequests.Columns[6].Visible ? r.ConnectionRequestActivities.OrderByDescending( a => a.CreatedDateTime).Select(a => a.Note).FirstOrDefault() : "", Status = r.ConnectionStatus.Name, StatusLabel = r.ConnectionStatus.IsCritical ? "warning" : "info", ConnectionState = r.ConnectionState, StateLabel = FormatStateLabel(r.ConnectionState, r.FollowupDate) }) .ToList(); gRequests.DataBind(); lOpportunityIcon.Text = string.Format("<i class='{0}'></i>", opportunitySummary.IconCssClass); lConnectionRequest.Text = String.Format("{0} Connection Requests", opportunitySummary.Name); } } else { pnlGrid.Visible = false; } }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { OpportunitySummary opportunitySummary = null; if ( SelectedOpportunityId.HasValue ) { opportunitySummary = SummaryState.SelectMany( t => t.Opportunities.Where( o => o.Id == SelectedOpportunityId.Value ) ).FirstOrDefault(); } if ( opportunitySummary != null ) { using ( var rockContext = new RockContext() ) { // Get queryable of all requests that belong to the selected opportunity, and user is authorized to view (based on security or connector group) var requests = new ConnectionRequestService( rockContext ) .Queryable().AsNoTracking() .Where( r => r.ConnectionOpportunityId == SelectedOpportunityId.Value && ( !opportunitySummary.CampusSpecificConnector || ( r.CampusId.HasValue && opportunitySummary.ConnectorCampusIds.Contains( r.CampusId.Value ) ) ) ); // Filter by Requester if ( ppRequester.PersonId.HasValue ) { requests = requests .Where( r => r.PersonAlias != null && r.PersonAlias.PersonId == ppRequester.PersonId.Value ); } // Filter by Connector if ( tglMyOpportunities.Checked ) { requests = requests .Where( r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == CurrentPersonId ); } else if ( ppConnector.PersonId.HasValue ) { requests = requests .Where( r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == ppConnector.PersonId.Value ); } // Filter by State if ( tglMyOpportunities.Checked ) { requests = requests .Where( r => r.ConnectionState == ConnectionState.Active || ( r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < _midnightToday ) ); } else { var states = new List<ConnectionState>(); bool futureFollowup = false; foreach ( string stateValue in cblState.SelectedValues ) { futureFollowup = futureFollowup || stateValue.AsInteger() == -2; var state = stateValue.ConvertToEnumOrNull<ConnectionState>(); if ( state.HasValue ) { states.Add( state.Value ); } } if ( futureFollowup || states.Any() ) { requests = requests .Where( r => ( futureFollowup && r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < _midnightToday ) || states.Contains( r.ConnectionState ) ); } } // Filter by Status List<int> statusIds = cblStatus.SelectedValuesAsInt; if ( statusIds.Any() ) { requests = requests .Where( r => statusIds.Contains( r.ConnectionStatusId ) ); } // Filter by Campus List<int> campusIds = cblCampus.SelectedValuesAsInt; if ( campusIds.Count > 0 ) { requests = requests .Where( r => r.Campus != null && campusIds.Contains( r.CampusId.Value ) ); } // Filter by Last Activity Note List<int> lastActivityIds = cblLastActivity.SelectedValuesAsInt; if ( lastActivityIds.Any() ) { requests = requests .Where( r => lastActivityIds.Contains( r.ConnectionRequestActivities.OrderByDescending( a => a.CreatedDateTime ).Select( a => a.ConnectionActivityTypeId ).FirstOrDefault() ) ); } SortProperty sortProperty = gRequests.SortProperty; if ( sortProperty != null ) { requests = requests.Sort( sortProperty ); } else { requests = requests .OrderBy( r => r.PersonAlias.Person.LastName ) .ThenBy( r => r.PersonAlias.Person.NickName ); } var requestList = requests.ToList(); var roleIds = requestList.Where( r => r.AssignedGroupMemberRoleId.HasValue).Select( r => r.AssignedGroupMemberRoleId.Value ).ToList(); var roles = new GroupTypeRoleService( rockContext ) .Queryable().AsNoTracking() .Where( r => roleIds.Contains( r.Id ) ) .ToDictionary( k => k.Id, v => v.Name ); gRequests.DataSource = requests.ToList() .Select( r => new { r.Id, r.Guid, PersonId = r.PersonAlias.PersonId, Name = r.PersonAlias.Person.FullNameReversed, Campus = r.Campus, Group = r.AssignedGroup != null ? r.AssignedGroup.Name : "", GroupStatus = r.AssignedGroupMemberStatus != null ? r.AssignedGroupMemberStatus.ConvertToString() : "", GroupRole = r.AssignedGroupMemberRoleId.HasValue ? roles[r.AssignedGroupMemberRoleId.Value] : "", Connector = r.ConnectorPersonAlias != null ? r.ConnectorPersonAlias.Person.FullName : "", LastActivity = FormatActivity( r.ConnectionRequestActivities.OrderByDescending( a => a.CreatedDateTime ).FirstOrDefault() ), LastActivityNote = gRequests.Columns[6].Visible ? r.ConnectionRequestActivities.OrderByDescending( a => a.CreatedDateTime ).Select( a => a.Note ).FirstOrDefault() : "", Status = r.ConnectionStatus.Name, StatusLabel = r.ConnectionStatus.IsCritical ? "warning" : "info", ConnectionState = r.ConnectionState, StateLabel = FormatStateLabel( r.ConnectionState, r.FollowupDate ) } ) .ToList(); gRequests.DataBind(); lOpportunityIcon.Text = string.Format( "<i class='{0}'></i>", opportunitySummary.IconCssClass ); lConnectionRequest.Text = String.Format( "{0} Connection Requests", opportunitySummary.Name ); } } else { pnlGrid.Visible = false; } }
private void BindGrids() { BindFilters(); var viewableOppIds = viewableConnectionOpportunities.Select( x => x.Id ).ToList(); var requests = new ConnectionRequestService( _rockContext ).Queryable() .Where( r => r.ConnectionRequestActivities.Any( a => a.CreatedDateTime > dateRange.LowerValue && a.CreatedDateTime < dateRange.UpperValue ) ) .Where( r => viewableOppIds.Contains( r.ConnectionOpportunityId ) ); var conOppId = rddConnectionOpportunity.SelectedValueAsId(); if ( conOppId > 0 ) { requests = requests.Where( r => r.ConnectionOpportunityId == conOppId ); } var campId = campusPicker.SelectedValueAsId(); if ( campId > 0 ) { requests = requests.Where( r => r.CampusId == campId ); } var connectorId = ppAssignedPerson.PersonId; if ( connectorId > 0 ) { requests = requests.Where( r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == connectorId ); } // Filter by State var midnightToday = RockDateTime.Today.AddDays( 1 ); var states = new List<ConnectionState>(); bool futureFollowup = false; foreach ( string stateValue in cblState.SelectedValues ) { futureFollowup = futureFollowup || stateValue.AsInteger() == -2; var state = stateValue.ConvertToEnumOrNull<ConnectionState>(); if ( state.HasValue ) { states.Add( state.Value ); } } if ( futureFollowup || states.Any() ) { requests = requests .Where( r => ( futureFollowup && r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday ) || states.Contains( r.ConnectionState ) ); } // Filter by Status List<string> statusIds = cblStatus.SelectedValues; if ( statusIds.Any() ) { requests = requests .Where( r => statusIds.Contains( r.ConnectionStatus.Name ) ); } SortProperty sortProperty = workflowReportTable.SortProperty; if ( sortProperty != null ) { requests = requests.Sort( sortProperty ); } else { requests = requests .OrderBy( r => (r.ConnectionState != ConnectionState.Inactive && r.ConnectionState != ConnectionState.Connected ) ? 0 : 1 ) .ThenBy( r => r.ConnectorPersonAlias.Person.NickName ) .ThenBy( r => r.ConnectorPersonAlias.Person.LastName ) .ThenBy( r => r.ConnectionOpportunity.Name ); } var requestsData = requests.ToList() .Select( r => new { r.Id, r.Guid, PersonId = r.PersonAlias.PersonId, Name = r.PersonAlias.Person.FullName, Campus = r.Campus, Group = r.AssignedGroup != null ? r.AssignedGroup.Name : "", Connector = r.ConnectorPersonAlias != null ? r.ConnectorPersonAlias.Person.FullName : "", LastActivity = FormatActivity( r.ConnectionRequestActivities.OrderByDescending( a => a.CreatedDateTime ).FirstOrDefault() ), CreatedDateTime = r.ConnectionRequestActivities.OrderByDescending( a => a.CreatedDateTime ).FirstOrDefault().CreatedDateTime, Opened = ( r.CreatedDateTime.HasValue ? ( r.CreatedDateTime.Value.ToShortDateString() + " " + r.CreatedDateTime.Value.ToShortTimeString() + " " ) : "" ) + "(<span class='small'>" + r.CreatedDateTime.ToRelativeDateString() + "</small>)", Status = r.ConnectionStatus.Name, StatusLabel = r.ConnectionStatus.IsCritical ? "warning" : "info", ConnectionState = r.ConnectionState, StateLabel = FormatStateLabel( r.ConnectionState, r.FollowupDate ), Opportunity = r.ConnectionOpportunity, Completed = r.ConnectionState != ConnectionState.Inactive && r.ConnectionState != ConnectionState.Connected ? 0 : 1, AgeInt = (DateTime.Now - r.CreatedDateTime).Value.Days } ) .ToList(); /* DateTime oneMonthAgo = DateTime.Now.AddMonths( -1 ); DateTime twoMonthsAgo = DateTime.Now.AddMonths( -2 ); DateTime threeMonthsAgo = DateTime.Now.AddMonths( -3 ); IEnumerable<GroupedData> groupedRequestsData = null; switch ( rddlGroupBy.SelectedValue ) { case "1": workflowReportTable.Visible = false; workflowGroupedReportTable.Visible = true; workflowGroupedReportTableItemName = "Workflow"; groupedRequestsData = requestsData.GroupBy( x => x.Opportunity.Id ).Select( grp => new GroupedData { GroupedItem = grp.FirstOrDefault().Opportunity.Name, Count = grp.Count(), OneMonthOldStats = "Open: " + grp.Where( x => x.Status != "Inactive" && ( x.CreatedDateTime ?? DateTime.Now ) > oneMonthAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt < 30 ).Count(), TwoMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < oneMonthAgo && ( x.CreatedDateTime ?? DateTime.Now ) > twoMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 30 && x.AgeInt < 60 ).Count(), ThreeMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < twoMonthsAgo && ( x.CreatedDateTime ?? DateTime.Now ) > threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 60 && x.AgeInt < 90 ).Count(), OlderThanThreeMonthsStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 90 ).Count(), TotalStats = "Open: " + grp.Where( x => x.Completed == 0 ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 ).Count() } ).OrderBy( x => x.GroupedItem ); workflowGroupedReportTable.DataSource = groupedRequestsData.ToList(); workflowGroupedReportTable.DataBind(); break; case "2": workflowReportTable.Visible = false; workflowGroupedReportTable.Visible = true; workflowGroupedReportTableItemName = "Campus"; groupedRequestsData = requestsData.GroupBy( x => x.Campus.Id ).Select( grp => new GroupedData { GroupedItem = grp.FirstOrDefault().Campus.Name, Count = grp.Count(), OneMonthOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) > oneMonthAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt < 30 ).Count(), TwoMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < oneMonthAgo && ( x.CreatedDateTime ?? DateTime.Now ) > twoMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 30 && x.AgeInt < 60 ).Count(), ThreeMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < twoMonthsAgo && ( x.CreatedDateTime ?? DateTime.Now ) > threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 60 && x.AgeInt < 90 ).Count(), OlderThanThreeMonthsStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 90 ).Count(), TotalStats = "Open: " + grp.Where( x => x.Completed == 0 ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 ).Count() } ).OrderBy( x => x.GroupedItem ); workflowGroupedReportTable.DataSource = groupedRequestsData.ToList(); workflowGroupedReportTable.DataBind(); break; case "3": workflowReportTable.Visible = false; workflowGroupedReportTable.Visible = true; workflowGroupedReportTableItemName = "Assigned Worker"; groupedRequestsData = requestsData.GroupBy( x => x.AssignedEntityName ).Select( grp => new GroupedData { GroupedItem = grp.FirstOrDefault().AssignedEntityName, Count = grp.Count(), OneMonthOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) > oneMonthAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt < 30 ).Count(), TwoMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < oneMonthAgo && ( x.CreatedDateTime ?? DateTime.Now ) > twoMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 30 && x.AgeInt < 60 ).Count(), ThreeMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < twoMonthsAgo && ( x.CreatedDateTime ?? DateTime.Now ) > threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 60 && x.AgeInt < 90 ).Count(), OlderThanThreeMonthsStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 90 ).Count(), TotalStats = "Open: " + grp.Where( x => x.Completed == 0 ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 ).Count() } ).OrderBy( x => x.GroupedItem ); workflowGroupedReportTable.DataSource = groupedRequestsData.ToList(); workflowGroupedReportTable.DataBind(); break; case "4": workflowReportTable.Visible = false; workflowGroupedReportTable.Visible = true; workflowGroupedReportTableItemName = "Status"; groupedRequestsData = requestsData.GroupBy( x => x.Status ).Select( grp => new GroupedData { GroupedItem = grp.FirstOrDefault().Status, Count = grp.Count(), OneMonthOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) > oneMonthAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt < 30 ).Count(), TwoMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < oneMonthAgo && ( x.CreatedDateTime ?? DateTime.Now ) > twoMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 30 && x.AgeInt < 60 ).Count(), ThreeMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < twoMonthsAgo && ( x.CreatedDateTime ?? DateTime.Now ) > threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 60 && x.AgeInt < 90 ).Count(), OlderThanThreeMonthsStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 90 ).Count(), TotalStats = "Open: " + grp.Where( x => x.Completed == 0 ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 ).Count() } ).OrderBy( x => x.GroupedItem ); workflowGroupedReportTable.DataSource = groupedRequestsData.ToList(); workflowGroupedReportTable.DataBind(); break; default:*/ workflowReportTable.Visible = true; workflowGroupedReportTable.Visible = false; workflowReportTable.DataSource = requestsData; workflowReportTable.DataKeyNames = new string[] { "Id" }; workflowReportTable.DataBind(); /* break; } */ //IQueryable<int> tmp = workflowData.AsQueryable().Where(x => x.Completed == 1).Select(x => ((x.CompletedDateTime - x.CreatedDateTime) ?? TimeSpan.Zero).Days); //int _0to90_AverageTime = tmp.Count() == 0 ? -1 : (int)tmp.Average(); //rlWorkflowStats.Text = "Average time to completion is " + _0to90_AverageTime + " days."; System.Web.Script.Serialization.JavaScriptSerializer jsSerializer = new System.Web.Script.Serialization.JavaScriptSerializer(); List<object[]> campusCount = requestsData.GroupBy( wd => wd.Campus.Id ).Select( wd => new object[] { wd.FirstOrDefault().Campus.Name, wd.Count() } ).ToList(); campusCount.Insert( 0, new string[] { "Campus", "Count" } ); workflowChartData2 = jsSerializer.Serialize( campusCount ).EncodeHtml(); List<object[]> wfTypeCount = requestsData.GroupBy( wd => wd.Opportunity.Id ).Select( wd => new object[] { wd.FirstOrDefault().Opportunity.Name, wd.Count() } ).ToList(); wfTypeCount.Insert( 0, new string[] { "Workflow Type", "Count" } ); workflowChartData3 = jsSerializer.Serialize( wfTypeCount ).EncodeHtml(); List<object[]> statusCount = requestsData.GroupBy( wd => wd.Status ).Select( wd => new object[] { wd.FirstOrDefault().Status, wd.Count() } ).ToList(); statusCount.Insert( 0, new string[] { "Status", "Count" } ); workflowChartData4 = jsSerializer.Serialize( statusCount ).EncodeHtml(); List<object[]> workerCount = requestsData.GroupBy( wd => wd.Connector ).Select( wd => new object[] { wd.FirstOrDefault().Connector, wd.Count() } ).ToList(); workerCount.Insert( 0, new string[] { "Worker", "Count" } ); workflowChartData5 = jsSerializer.Serialize( workerCount ).EncodeHtml(); List<String> statuses = requestsData.GroupBy( wd => wd.Status ).Select( x => x.FirstOrDefault().Status ).ToList(); List<object[]> ageList = requestsData.Select( wd => statuses.Select( x => x == wd.Status ? ( object ) wd.AgeInt : null ).ToArray() ).ToList(); ageList.Insert( 0, statuses.ToArray() ); workflowChartData1 = jsSerializer.Serialize( ageList ).EncodeHtml(); }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { OpportunitySummary opportunitySummary = null; if ( SelectedOpportunityId.HasValue ) { opportunitySummary = SummaryState.SelectMany( t => t.Opportunities.Where( o => o.Id == SelectedOpportunityId.Value ) ).FirstOrDefault(); } if ( opportunitySummary != null ) { using ( var rockContext = new RockContext() ) { // Get queryable of all requests that belong to the selected opportunity, and user is authorized to view (based on security or connector group) var requests = new ConnectionRequestService( rockContext ) .Queryable().AsNoTracking() .Where( r => r.ConnectionOpportunityId == SelectedOpportunityId.Value && ( !opportunitySummary.CampusSpecificConnector || ( r.CampusId.HasValue && opportunitySummary.ConnectorCampusIds.Contains( r.CampusId.Value ) ) ) ); // Filter by Requester if ( ppRequester.PersonId.HasValue ) { requests = requests .Where( r => r.PersonAlias != null && r.PersonAlias.PersonId == ppRequester.PersonId.Value ); } // Filter by Connector if ( tglMyOpportunities.Checked ) { requests = requests .Where( r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == CurrentPersonId ); } else if ( ppConnector.PersonId.HasValue ) { requests = requests .Where( r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == ppConnector.PersonId.Value ); } // Filter by State var midnightToday = RockDateTime.Today.AddDays(1); if ( tglMyOpportunities.Checked ) { requests = requests .Where( r => r.ConnectionState == ConnectionState.Active || ( r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday ) ); } else { var states = new List<ConnectionState>(); bool futureFollowup = false; foreach ( string stateValue in cblState.SelectedValues ) { futureFollowup = futureFollowup || stateValue.AsInteger() == -2; var state = stateValue.ConvertToEnumOrNull<ConnectionState>(); if ( state.HasValue ) { states.Add( state.Value ); } } if ( futureFollowup || states.Any() ) { requests = requests .Where( r => ( futureFollowup && r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday ) || states.Contains( r.ConnectionState ) ); } } // Filter by Status List<int> statusIds = cblStatus.SelectedValuesAsInt; if ( statusIds.Any() ) { requests = requests .Where( r => statusIds.Contains( r.ConnectionStatusId ) ); } // Filter by Campus List<int> campusIds = cblCampus.SelectedValuesAsInt; if ( campusIds.Count > 0 ) { requests = requests .Where( r => r.Campus != null && campusIds.Contains( r.CampusId.Value ) ); } gRequests.DataSource = requests.ToList() .Select( r => new { r.Id, r.Guid, Name = r.PersonAlias.Person.FullName, Campus = r.Campus, Group = r.AssignedGroup != null ? r.AssignedGroup.Name : "", Connector = r.ConnectorPersonAlias != null ? r.ConnectorPersonAlias.Person.FullName : "", Activities = r.ConnectionRequestActivities.Select( a => a.ConnectionActivityType.Name ).ToList().AsDelimited( "</br>" ), Status = r.ConnectionStatus.Name, StatusLabel = r.ConnectionStatus.IsCritical ? "warning" : "info", StateLabel = FormatStateLabel( r.ConnectionState, r.FollowupDate ) } ) .ToList(); gRequests.DataBind(); lOpportunityIcon.Text = string.Format( "<i class='{0}'></i>", opportunitySummary.IconCssClass ); lConnectionRequest.Text = String.Format( "{0} Connection Requests", opportunitySummary.Name ); } } else { pnlGrid.Visible = false; } }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { OpportunitySummary opportunitySummary = null; if (SelectedOpportunityId.HasValue) { opportunitySummary = SummaryState.SelectMany(t => t.Opportunities.Where(o => o.Id == SelectedOpportunityId.Value)).FirstOrDefault(); } if (opportunitySummary != null) { using (var rockContext = new RockContext()) { // Get queryable of all requests that belong to the selected opportunity, and user is authorized to view (based on security or connector group) var requests = new ConnectionRequestService(rockContext) .Queryable().AsNoTracking() .Where(r => r.ConnectionOpportunityId == SelectedOpportunityId.Value && ( !opportunitySummary.CampusSpecificConnector || (r.CampusId.HasValue && opportunitySummary.ConnectorCampusIds.Contains(r.CampusId.Value)) )); // Filter by Requester if (ppRequester.PersonId.HasValue) { requests = requests .Where(r => r.PersonAlias != null && r.PersonAlias.PersonId == ppRequester.PersonId.Value); } // Filter by Connector if (tglMyOpportunities.Checked) { requests = requests .Where(r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == CurrentPersonId); } else if (ppConnector.PersonId.HasValue) { requests = requests .Where(r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == ppConnector.PersonId.Value); } // Filter by State var midnightToday = RockDateTime.Today.AddDays(1); if (tglMyOpportunities.Checked) { requests = requests .Where(r => r.ConnectionState == ConnectionState.Active || (r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday)); } else { var states = new List <ConnectionState>(); bool futureFollowup = false; foreach (string stateValue in cblState.SelectedValues) { futureFollowup = futureFollowup || stateValue.AsInteger() == -2; var state = stateValue.ConvertToEnumOrNull <ConnectionState>(); if (state.HasValue) { states.Add(state.Value); } } if (futureFollowup || states.Any()) { requests = requests .Where(r => (futureFollowup && r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday) || states.Contains(r.ConnectionState)); } } // Filter by Status List <int> statusIds = cblStatus.SelectedValuesAsInt; if (statusIds.Any()) { requests = requests .Where(r => statusIds.Contains(r.ConnectionStatusId)); } // Filter by Campus List <int> campusIds = cblCampus.SelectedValuesAsInt; if (campusIds.Count > 0) { requests = requests .Where(r => r.Campus != null && campusIds.Contains(r.CampusId.Value)); } Guid groupTypeGuid = GetAttributeValue("GeofenceGroupType").AsGuid(); var groupLocations = rockContext.GroupLocations .Where(gl => gl.Group.GroupType.Guid == groupTypeGuid && gl.Location.GeoFence != null).ToList(); var results = ( from r in requests.ToList() let homeLocation = r.PersonAlias.Person.GetHomeLocation(rockContext) let region = groupLocations.Where(gl => homeLocation != null && homeLocation.GeoPoint != null && homeLocation.GeoPoint.Intersects(gl.Location.GeoFence) ).Select(gl => gl.Location).FirstOrDefault() select new ConnectionResult() { Id = r.Id, Guid = r.Guid, PersonId = r.PersonAlias.PersonId, PersonName = r.PersonAlias.Person.FullNameReversed, CampusName = r.Campus.Name, GroupName = r.AssignedGroup != null ? r.AssignedGroup.Name : "", ConnectorName = r.ConnectorPersonAlias != null ? r.ConnectorPersonAlias.Person.FullName : "", LastActivity = FormatActivity(r.ConnectionRequestActivities.OrderByDescending(a => a.CreatedDateTime).FirstOrDefault()), Status = r.ConnectionStatus, StatusLabel = r.ConnectionStatus.IsCritical ? "warning" : "info", State = r.ConnectionState, StateLabel = FormatStateLabel(r.ConnectionState, r.FollowupDate), RegionId = (region != null ? region.Id : 0), RegionName = (region != null ? region.Name : "No Region"), PersonCity = homeLocation != null ? homeLocation.City : "No Location Info", PersonState = homeLocation != null ? homeLocation.State : "No Location Info", PhoneNumber = String.Join(", ", r.PersonAlias.Person.PhoneNumbers.Where(p => p.IsUnlisted != true && !String.IsNullOrWhiteSpace(p.Number)).Select(p => p.NumberFormatted).ToList()) }).AsQueryable(); // Filter by Region List <int> regionIds = cblRegion.SelectedValuesAsInt; if (regionIds.Any()) { results = results .Where(r => regionIds.Contains(r.RegionId)); } //Sort in Memory SortProperty sortProperty = gRequests.SortProperty; if (sortProperty != null) { results = results.Sort(sortProperty); } else { results = results .OrderBy(r => r.PersonName); } gRequests.SetLinqDataSource(results); gRequests.DataBind(); lOpportunityIcon.Text = string.Format("<i class='{0}'></i>", opportunitySummary.IconCssClass); lConnectionRequest.Text = String.Format("{0} Connection Requests", opportunitySummary.Name); } } else { pnlGrid.Visible = false; } }
private void BindGrids() { BindFilters(); var viewableOppIds = viewableConnectionOpportunities.Select(x => x.Id).ToList(); var requests = new ConnectionRequestService(_rockContext).Queryable() .Where(r => r.ConnectionRequestActivities.Any(a => a.CreatedDateTime > dateRange.LowerValue && a.CreatedDateTime < dateRange.UpperValue)) .Where(r => viewableOppIds.Contains(r.ConnectionOpportunityId)); var conOppId = rddConnectionOpportunity.SelectedValueAsId(); if (conOppId > 0) { requests = requests.Where(r => r.ConnectionOpportunityId == conOppId); } var campId = campusPicker.SelectedValueAsId(); if (campId > 0) { requests = requests.Where(r => r.CampusId == campId); } var connectorId = ppAssignedPerson.PersonId; if (connectorId > 0) { requests = requests.Where(r => r.ConnectorPersonAlias != null && r.ConnectorPersonAlias.PersonId == connectorId); } // Filter by State var midnightToday = RockDateTime.Today.AddDays(1); var states = new List <ConnectionState>(); bool futureFollowup = false; foreach (string stateValue in cblState.SelectedValues) { futureFollowup = futureFollowup || stateValue.AsInteger() == -2; var state = stateValue.ConvertToEnumOrNull <ConnectionState>(); if (state.HasValue) { states.Add(state.Value); } } if (futureFollowup || states.Any()) { requests = requests .Where(r => (futureFollowup && r.ConnectionState == ConnectionState.FutureFollowUp && r.FollowupDate.HasValue && r.FollowupDate.Value < midnightToday) || states.Contains(r.ConnectionState)); } // Filter by Status List <string> statusIds = cblStatus.SelectedValues; if (statusIds.Any()) { requests = requests .Where(r => statusIds.Contains(r.ConnectionStatus.Name)); } SortProperty sortProperty = workflowReportTable.SortProperty; if (sortProperty != null) { requests = requests.Sort(sortProperty); } else { requests = requests .OrderBy(r => (r.ConnectionState != ConnectionState.Inactive && r.ConnectionState != ConnectionState.Connected) ? 0 : 1) .ThenBy(r => r.ConnectorPersonAlias.Person.NickName) .ThenBy(r => r.ConnectorPersonAlias.Person.LastName) .ThenBy(r => r.ConnectionOpportunity.Name); } var requestsData = requests.ToList() .Select(r => new { r.Id, r.Guid, PersonId = r.PersonAlias.PersonId, Name = r.PersonAlias.Person.FullName, Campus = r.Campus, Group = r.AssignedGroup != null ? r.AssignedGroup.Name : "", Connector = r.ConnectorPersonAlias != null ? r.ConnectorPersonAlias.Person.FullName : "", LastActivity = FormatActivity(r.ConnectionRequestActivities.OrderByDescending(a => a.CreatedDateTime).FirstOrDefault()), CreatedDateTime = r.ConnectionRequestActivities.OrderByDescending(a => a.CreatedDateTime).FirstOrDefault().CreatedDateTime, Opened = (r.CreatedDateTime.HasValue ? (r.CreatedDateTime.Value.ToShortDateString() + " " + r.CreatedDateTime.Value.ToShortTimeString() + " ") : "") + "(<span class='small'>" + r.CreatedDateTime.ToRelativeDateString() + "</small>)", Status = r.ConnectionStatus.Name, StatusLabel = r.ConnectionStatus.IsCritical ? "warning" : "info", ConnectionState = r.ConnectionState, StateLabel = FormatStateLabel(r.ConnectionState, r.FollowupDate), Opportunity = r.ConnectionOpportunity, Completed = r.ConnectionState != ConnectionState.Inactive && r.ConnectionState != ConnectionState.Connected ? 0 : 1, AgeInt = (DateTime.Now - r.CreatedDateTime).Value.Days }) .ToList(); /* * * DateTime oneMonthAgo = DateTime.Now.AddMonths( -1 ); * DateTime twoMonthsAgo = DateTime.Now.AddMonths( -2 ); * DateTime threeMonthsAgo = DateTime.Now.AddMonths( -3 ); * IEnumerable<GroupedData> groupedRequestsData = null; * * switch ( rddlGroupBy.SelectedValue ) * { * case "1": * workflowReportTable.Visible = false; * workflowGroupedReportTable.Visible = true; * workflowGroupedReportTableItemName = "Workflow"; * * groupedRequestsData = requestsData.GroupBy( x => x.Opportunity.Id ).Select( grp => new GroupedData * { * GroupedItem = grp.FirstOrDefault().Opportunity.Name, * Count = grp.Count(), * OneMonthOldStats = "Open: " + grp.Where( x => x.Status != "Inactive" && ( x.CreatedDateTime ?? DateTime.Now ) > oneMonthAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt < 30 ).Count(), * TwoMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < oneMonthAgo && ( x.CreatedDateTime ?? DateTime.Now ) > twoMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 30 && x.AgeInt < 60 ).Count(), * ThreeMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < twoMonthsAgo && ( x.CreatedDateTime ?? DateTime.Now ) > threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 60 && x.AgeInt < 90 ).Count(), * OlderThanThreeMonthsStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 90 ).Count(), * TotalStats = "Open: " + grp.Where( x => x.Completed == 0 ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 ).Count() * } ).OrderBy( x => x.GroupedItem ); * * workflowGroupedReportTable.DataSource = groupedRequestsData.ToList(); * workflowGroupedReportTable.DataBind(); * break; * case "2": * workflowReportTable.Visible = false; * workflowGroupedReportTable.Visible = true; * workflowGroupedReportTableItemName = "Campus"; * * groupedRequestsData = requestsData.GroupBy( x => x.Campus.Id ).Select( grp => new GroupedData * { * GroupedItem = grp.FirstOrDefault().Campus.Name, * Count = grp.Count(), * OneMonthOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) > oneMonthAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt < 30 ).Count(), * TwoMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < oneMonthAgo && ( x.CreatedDateTime ?? DateTime.Now ) > twoMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 30 && x.AgeInt < 60 ).Count(), * ThreeMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < twoMonthsAgo && ( x.CreatedDateTime ?? DateTime.Now ) > threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 60 && x.AgeInt < 90 ).Count(), * OlderThanThreeMonthsStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 90 ).Count(), * TotalStats = "Open: " + grp.Where( x => x.Completed == 0 ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 ).Count() * } ).OrderBy( x => x.GroupedItem ); * * workflowGroupedReportTable.DataSource = groupedRequestsData.ToList(); * workflowGroupedReportTable.DataBind(); * break; * case "3": * workflowReportTable.Visible = false; * workflowGroupedReportTable.Visible = true; * workflowGroupedReportTableItemName = "Assigned Worker"; * * groupedRequestsData = requestsData.GroupBy( x => x.AssignedEntityName ).Select( grp => new GroupedData * { * GroupedItem = grp.FirstOrDefault().AssignedEntityName, * Count = grp.Count(), * OneMonthOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) > oneMonthAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt < 30 ).Count(), * TwoMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < oneMonthAgo && ( x.CreatedDateTime ?? DateTime.Now ) > twoMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 30 && x.AgeInt < 60 ).Count(), * ThreeMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < twoMonthsAgo && ( x.CreatedDateTime ?? DateTime.Now ) > threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 60 && x.AgeInt < 90 ).Count(), * OlderThanThreeMonthsStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 90 ).Count(), * TotalStats = "Open: " + grp.Where( x => x.Completed == 0 ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 ).Count() * } ).OrderBy( x => x.GroupedItem ); * * workflowGroupedReportTable.DataSource = groupedRequestsData.ToList(); * workflowGroupedReportTable.DataBind(); * break; * case "4": * workflowReportTable.Visible = false; * workflowGroupedReportTable.Visible = true; * workflowGroupedReportTableItemName = "Status"; * * groupedRequestsData = requestsData.GroupBy( x => x.Status ).Select( grp => new GroupedData * { * GroupedItem = grp.FirstOrDefault().Status, * Count = grp.Count(), * OneMonthOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) > oneMonthAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt < 30 ).Count(), * TwoMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < oneMonthAgo && ( x.CreatedDateTime ?? DateTime.Now ) > twoMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 30 && x.AgeInt < 60 ).Count(), * ThreeMonthsOldStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < twoMonthsAgo && ( x.CreatedDateTime ?? DateTime.Now ) > threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 60 && x.AgeInt < 90 ).Count(), * OlderThanThreeMonthsStats = "Open: " + grp.Where( x => x.Completed == 0 && ( x.CreatedDateTime ?? DateTime.Now ) < threeMonthsAgo ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 && x.AgeInt >= 90 ).Count(), * TotalStats = "Open: " + grp.Where( x => x.Completed == 0 ).Count() + "<br/>Closed: " + grp.Where( x => x.Completed == 1 ).Count() * } ).OrderBy( x => x.GroupedItem ); * * workflowGroupedReportTable.DataSource = groupedRequestsData.ToList(); * workflowGroupedReportTable.DataBind(); * break; * default:*/ workflowReportTable.Visible = true; workflowGroupedReportTable.Visible = false; workflowReportTable.DataSource = requestsData; workflowReportTable.DataKeyNames = new string[] { "Id" }; workflowReportTable.DataBind(); /* * break; * } */ //IQueryable<int> tmp = workflowData.AsQueryable().Where(x => x.Completed == 1).Select(x => ((x.CompletedDateTime - x.CreatedDateTime) ?? TimeSpan.Zero).Days); //int _0to90_AverageTime = tmp.Count() == 0 ? -1 : (int)tmp.Average(); //rlWorkflowStats.Text = "Average time to completion is " + _0to90_AverageTime + " days."; System.Web.Script.Serialization.JavaScriptSerializer jsSerializer = new System.Web.Script.Serialization.JavaScriptSerializer(); List <object[]> campusCount = requestsData.GroupBy(wd => wd.Campus.Id).Select(wd => new object[] { wd.FirstOrDefault().Campus.Name, wd.Count() }).ToList(); campusCount.Insert(0, new string[] { "Campus", "Count" }); workflowChartData2 = jsSerializer.Serialize(campusCount).EncodeHtml(); List <object[]> wfTypeCount = requestsData.GroupBy(wd => wd.Opportunity.Id).Select(wd => new object[] { wd.FirstOrDefault().Opportunity.Name, wd.Count() }).ToList(); wfTypeCount.Insert(0, new string[] { "Workflow Type", "Count" }); workflowChartData3 = jsSerializer.Serialize(wfTypeCount).EncodeHtml(); List <object[]> statusCount = requestsData.GroupBy(wd => wd.Status).Select(wd => new object[] { wd.FirstOrDefault().Status, wd.Count() }).ToList(); statusCount.Insert(0, new string[] { "Status", "Count" }); workflowChartData4 = jsSerializer.Serialize(statusCount).EncodeHtml(); List <object[]> workerCount = requestsData.GroupBy(wd => wd.Connector).Select(wd => new object[] { wd.FirstOrDefault().Connector, wd.Count() }).ToList(); workerCount.Insert(0, new string[] { "Worker", "Count" }); workflowChartData5 = jsSerializer.Serialize(workerCount).EncodeHtml(); List <String> statuses = requestsData.GroupBy(wd => wd.Status).Select(x => x.FirstOrDefault().Status).ToList(); List <object[]> ageList = requestsData.Select(wd => statuses.Select(x => x == wd.Status ? ( object )wd.AgeInt : null).ToArray()).ToList(); ageList.Insert(0, statuses.ToArray()); workflowChartData1 = jsSerializer.Serialize(ageList).EncodeHtml(); }