/// <summary>
/// Gets the SMS conversation history for a person alias ID. Includes the communication sent by Rock that the person may be responding to.
/// </summary>
/// <param name="personId">The person identifier.</param>
/// <param name="relatedSmsFromDefinedValueId">The related SMS from defined value identifier.</param>
/// <returns>List<CommunicationRecipientResponse>.</returns>
public List <CommunicationRecipientResponse> GetCommunicationConversationForPerson(int personId, int relatedSmsFromDefinedValueId)
{
List <CommunicationRecipientResponse> communicationRecipientResponseList = new List <CommunicationRecipientResponse>();
var smsMediumEntityTypeId = EntityTypeCache.GetId(SystemGuid.EntityType.COMMUNICATION_MEDIUM_SMS).Value;
var personAliasIdQuery = new PersonAliasService(this.Context as RockContext).Queryable().Where(a => a.PersonId == personId).Select(a => a.Id);
var communicationResponseQuery = this.Queryable()
.Where(r => r.RelatedMediumEntityTypeId == smsMediumEntityTypeId &&
r.RelatedSmsFromDefinedValueId == relatedSmsFromDefinedValueId &&
r.FromPersonAliasId.HasValue &&
personAliasIdQuery.Contains(r.FromPersonAliasId.Value)
);
var communicationResponseList = communicationResponseQuery.ToList();
foreach (var communicationResponse in communicationResponseList)
{
var communicationRecipientResponse = new CommunicationRecipientResponse
{
CreatedDateTime = communicationResponse.CreatedDateTime,
PersonId = communicationResponse?.FromPersonAlias?.PersonId,
FullName = communicationResponse?.FromPersonAlias?.Person.FullName,
IsRead = communicationResponse.IsRead,
MessageKey = communicationResponse.MessageKey,
IsOutbound = false,
RecipientPersonAliasId = communicationResponse.FromPersonAliasId,
SMSMessage = communicationResponse.Response,
MessageStatus = CommunicationRecipientStatus.Delivered, // We are just going to call these delivered because we have them. Setting this will tell the UI to not display the status.
CommunicationResponseId = communicationResponse.Id,
};
communicationRecipientResponseList.Add(communicationRecipientResponse);
}
var communicationRecipientQuery = new CommunicationRecipientService(this.Context as RockContext)
.Queryable()
.Where(r => r.MediumEntityTypeId == smsMediumEntityTypeId)
.Where(r => r.Communication.SMSFromDefinedValueId == relatedSmsFromDefinedValueId)
.Where(r => r.PersonAliasId.HasValue)
.Where(r => personAliasIdQuery.Contains(r.PersonAliasId.Value))
.Where(r => r.Status == CommunicationRecipientStatus.Delivered || r.Status == CommunicationRecipientStatus.Pending);
var communicationRecipientList = communicationRecipientQuery.Include(a => a.PersonAlias.Person.PhoneNumbers).Select(a => new
{
a.CreatedDateTime,
a.Communication.SenderPersonAlias.Person,
a.Communication,
PersonAliasId = a.Communication.SenderPersonAliasId,
a.SentMessage,
a.Status
}).ToList();
foreach (var communicationRecipient in communicationRecipientList)
{
var communicationRecipientResponse = new CommunicationRecipientResponse
{
CreatedDateTime = communicationRecipient.CreatedDateTime,
PersonId = communicationRecipient.Person?.Id,
FullName = communicationRecipient.Person?.FullName,
IsRead = true,
IsOutbound = true,
RecipientPersonAliasId = communicationRecipient.PersonAliasId,
SMSMessage = communicationRecipient.SentMessage,
MessageStatus = communicationRecipient.Status,
CommunicationId = communicationRecipient.Communication?.Id,
};
if (communicationRecipient.Person?.IsNameless() == true)
{
// if the person is nameless, we'll need to know their number since we don't know their name
communicationRecipientResponse.MessageKey = communicationRecipient.Person?.PhoneNumbers.FirstOrDefault()?.Number;
}
else
{
// If the Person is not nameless, we just need to show their name, not their number
communicationRecipientResponse.MessageKey = null;
}
communicationRecipientResponseList.Add(communicationRecipientResponse);
}
return(communicationRecipientResponseList.OrderBy(a => a.CreatedDateTime).ToList());
}
/// <summary>
/// Gets the communications and response recipients.
/// </summary>
/// <param name="relatedSmsFromDefinedValueId">The related SMS from defined value identifier.</param>
/// <param name="startDateTime">The start date time.</param>
/// <param name="showReadMessages">if set to <c>true</c> [show read messages].</param>
/// <param name="maxCount">The maximum count.</param>
/// <param name="personId">The person identifier.</param>
/// <returns></returns>
public List <CommunicationRecipientResponse> GetCommunicationResponseRecipients(int relatedSmsFromDefinedValueId, DateTime startDateTime, bool showReadMessages, int maxCount, int?personId)
{
var smsMediumEntityTypeId = EntityTypeCache.GetId(SystemGuid.EntityType.COMMUNICATION_MEDIUM_SMS).Value;
IQueryable <CommunicationResponse> communicationResponseQuery = this.Queryable()
.Where(r => r.RelatedMediumEntityTypeId == smsMediumEntityTypeId && r.RelatedSmsFromDefinedValueId == relatedSmsFromDefinedValueId && r.CreatedDateTime >= startDateTime && r.FromPersonAliasId.HasValue);
if (!showReadMessages)
{
communicationResponseQuery = communicationResponseQuery.Where(r => r.IsRead == false);
}
var personAliasQuery = personId == null
? new PersonAliasService(this.Context as RockContext).Queryable()
: new PersonAliasService(this.Context as RockContext).Queryable().Where(p => p.PersonId == personId);
// do an explicit LINQ inner join on PersonAlias to avoid performance issue where it would do an outer join instead
var communicationResponseJoinQuery =
from cr in communicationResponseQuery
join pa in personAliasQuery on cr.FromPersonAliasId equals pa.Id
select new { cr, pa };
IQueryable <CommunicationResponse> mostRecentCommunicationResponseQuery = communicationResponseJoinQuery
.GroupBy(r => r.pa.PersonId)
.Select(a => a.OrderByDescending(x => x.cr.CreatedDateTime).FirstOrDefault())
.OrderByDescending(a => a.cr.CreatedDateTime).Select(a => a.cr);
IQueryable <CommunicationRecipient> communicationRecipientQuery = new CommunicationRecipientService(this.Context as RockContext).Queryable()
.Where(r =>
r.MediumEntityTypeId == smsMediumEntityTypeId &&
r.Communication.SMSFromDefinedValueId == relatedSmsFromDefinedValueId &&
r.CreatedDateTime >= startDateTime &&
r.Status == CommunicationRecipientStatus.Delivered);
// do an explicit LINQ inner join on PersonAlias to avoid performance issue where it would do an outer join instead
var communicationRecipientJoinQuery =
from cr in communicationRecipientQuery.Where(a => a.PersonAliasId.HasValue)
join pa in personAliasQuery on cr.PersonAliasId.Value equals pa.Id
select new
{
PersonId = pa.PersonId,
Person = pa.Person,
CreatedDateTime = cr.CreatedDateTime,
cr.Communication,
cr.CommunicationId,
CommunicationSMSMessage = cr.Communication.SMSMessage,
SentMessage = cr.SentMessage,
PersonAliasId = cr.PersonAliasId
};
var mostRecentCommunicationRecipientQuery = communicationRecipientJoinQuery
.GroupBy(r => r.PersonId)
.Select(a =>
a.Select(s => new
{
s.Person,
s.CreatedDateTime,
s.CommunicationSMSMessage,
s.CommunicationId,
s.Communication,
s.SentMessage,
s.PersonAliasId
}).OrderByDescending(s => s.CreatedDateTime).FirstOrDefault()
);
var mostRecentCommunicationResponseList = mostRecentCommunicationResponseQuery.Include(a => a.FromPersonAlias.Person).AsNoTracking().Take(maxCount).ToList();
List <CommunicationRecipientResponse> communicationRecipientResponseList = new List <CommunicationRecipientResponse>();
foreach (var mostRecentCommunicationResponse in mostRecentCommunicationResponseList)
{
var communicationRecipientResponse = new CommunicationRecipientResponse
{
CreatedDateTime = mostRecentCommunicationResponse.CreatedDateTime,
PersonId = mostRecentCommunicationResponse?.FromPersonAlias.PersonId,
RecordTypeValueId = mostRecentCommunicationResponse?.FromPersonAlias.Person.RecordTypeValueId,
FullName = mostRecentCommunicationResponse?.FromPersonAlias.Person.FullName,
IsRead = mostRecentCommunicationResponse.IsRead,
MessageKey = mostRecentCommunicationResponse.MessageKey,
IsOutbound = false,
RecipientPersonAliasId = mostRecentCommunicationResponse.FromPersonAliasId,
SMSMessage = mostRecentCommunicationResponse.Response,
CommunicationResponseId = mostRecentCommunicationResponse.Id
};
communicationRecipientResponseList.Add(communicationRecipientResponse);
}
var mostRecentCommunicationRecipientList = mostRecentCommunicationRecipientQuery.Take(maxCount).ToList();
foreach (var mostRecentCommunicationRecipient in mostRecentCommunicationRecipientList)
{
var communicationRecipientResponse = new CommunicationRecipientResponse
{
CreatedDateTime = mostRecentCommunicationRecipient.CreatedDateTime,
PersonId = mostRecentCommunicationRecipient.Person.Id,
RecordTypeValueId = mostRecentCommunicationRecipient.Person.RecordTypeValueId,
FullName = mostRecentCommunicationRecipient.Person.FullName,
IsOutbound = true,
IsRead = true,
MessageKey = null, // communication recipients just need to show their name, not their number
RecipientPersonAliasId = mostRecentCommunicationRecipient.PersonAliasId,
SMSMessage = mostRecentCommunicationRecipient.SentMessage.IsNullOrWhiteSpace() ? mostRecentCommunicationRecipient.CommunicationSMSMessage : mostRecentCommunicationRecipient.SentMessage,
CommunicationId = mostRecentCommunicationRecipient.CommunicationId
};
if (mostRecentCommunicationRecipient?.Person.IsNameless() == true)
{
// if the person is nameless, we'll need to know their number since we don't know their name
communicationRecipientResponse.MessageKey = mostRecentCommunicationRecipient.Person.PhoneNumbers.FirstOrDefault()?.Number;
}
else
{
// If the Person is not nameless, we just need to show their name, not their number
communicationRecipientResponse.MessageKey = null;
}
communicationRecipientResponseList.Add(communicationRecipientResponse);
}
// NOTE: We actually have up to twice the max count at this point, because we are combining results from
// CommunicationRecipient and CommunicationResponse, and we took the maxCount of each of those.
// Now, we see what that combination ends up looking like when we sort it by CreatedDateTime
communicationRecipientResponseList = communicationRecipientResponseList
.GroupBy(r => r.PersonId)
.Select(a => a.OrderByDescending(x => x.CreatedDateTime).FirstOrDefault())
.OrderByDescending(a => a.CreatedDateTime).Take(maxCount).ToList();
return(communicationRecipientResponseList);
}
/// <summary>
/// Gets the SMS conversation history for a person alias ID. Includes the communication sent by Rock that the person may be responding to.
/// </summary>
/// <param name="personAliasId">The person alias identifier.</param>
/// <param name="relatedSmsFromDefinedValueId">The related SMS from defined value identifier.</param>
/// <returns></returns>
public List <CommunicationRecipientResponse> GetCommunicationConversation(int personAliasId, int relatedSmsFromDefinedValueId)
{
List <CommunicationRecipientResponse> communicationRecipientResponseList = new List <CommunicationRecipientResponse>();
var smsMediumEntityTypeId = EntityTypeCache.GetId(SystemGuid.EntityType.COMMUNICATION_MEDIUM_SMS).Value;
/*
* 5/4/2021 MSB
* When we include conversations we need to make sure we include conversations from all aliases related to the
* person so that conversations from merged records still appear here.
*
* Reason: Merge People Conversations
*/
var communicationResponseQuery = this.Queryable()
.Where(r => r.RelatedMediumEntityTypeId == smsMediumEntityTypeId &&
r.RelatedSmsFromDefinedValueId == relatedSmsFromDefinedValueId &&
r.FromPersonAlias != null &&
r.FromPersonAlias.Person.Aliases.Any(fpa => fpa.Id == personAliasId));
var communicationResponseList = communicationResponseQuery.ToList();
foreach (var communicationResponse in communicationResponseList)
{
var communicationRecipientResponse = new CommunicationRecipientResponse
{
CreatedDateTime = communicationResponse.CreatedDateTime,
PersonId = communicationResponse?.FromPersonAlias?.PersonId,
FullName = communicationResponse?.FromPersonAlias?.Person.FullName,
IsRead = communicationResponse.IsRead,
MessageKey = communicationResponse.MessageKey,
IsOutbound = false,
RecipientPersonAliasId = communicationResponse.FromPersonAliasId,
SMSMessage = communicationResponse.Response,
MessageStatus = CommunicationRecipientStatus.Delivered, // We are just going to call these delivered because we have them. Setting this will tell the UI to not display the status.
BinaryFileGuids = communicationResponse.Attachments?.Select(r => r.BinaryFile.Guid).ToList()
};
communicationRecipientResponseList.Add(communicationRecipientResponse);
}
var communicationRecipientQuery = new CommunicationRecipientService(this.Context as RockContext)
.Queryable()
.Where(r => r.MediumEntityTypeId == smsMediumEntityTypeId)
.Where(r => r.Communication.SMSFromDefinedValueId == relatedSmsFromDefinedValueId)
.Where(r => r.PersonAlias != null)
.Where(r => r.PersonAlias.Person.Aliases.Any(fpa => fpa.Id == personAliasId))
.Where(r => r.Status == CommunicationRecipientStatus.Delivered || r.Status == CommunicationRecipientStatus.Pending);
var communicationRecipientList = communicationRecipientQuery.Include(a => a.PersonAlias.Person.PhoneNumbers).Select(a => new
{
a.CreatedDateTime,
a.Communication.SenderPersonAlias.Person,
a.Communication,
PersonAliasId = a.Communication.SenderPersonAliasId,
a.SentMessage,
a.Status
}).ToList();
foreach (var communicationRecipient in communicationRecipientList)
{
var communicationRecipientResponse = new CommunicationRecipientResponse
{
CreatedDateTime = communicationRecipient.CreatedDateTime,
PersonId = communicationRecipient.Person?.Id,
FullName = communicationRecipient.Person?.FullName,
IsRead = true,
IsOutbound = true,
RecipientPersonAliasId = communicationRecipient.PersonAliasId,
SMSMessage = communicationRecipient.SentMessage,
MessageStatus = communicationRecipient.Status,
BinaryFileGuids = communicationRecipient.Communication.Attachments?.Select(c => c.BinaryFile.Guid).ToList()
};
if (communicationRecipient.Person?.IsNameless() == true)
{
// if the person is nameless, we'll need to know their number since we don't know their name
communicationRecipientResponse.MessageKey = communicationRecipient.Person?.PhoneNumbers.FirstOrDefault()?.Number;
}
else
{
// If the Person is not nameless, we just need to show their name, not their number
communicationRecipientResponse.MessageKey = null;
}
communicationRecipientResponseList.Add(communicationRecipientResponse);
}
return(communicationRecipientResponseList.OrderBy(a => a.CreatedDateTime).ToList());
}