/// <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();
}
