private void BuildRoleMembersCache()
{
using (TicketDeskEntities ctx = new TicketDeskEntities())
{
var configuredTdRoles = new string[] { TdStaffRoleName, TdSubmittersRoleName, TdAdminRoleName };
var sqlCacheMembers = ctx.AdCachedRoleMembers.ToList();//go ahead and fetch entire table
foreach (var tdRole in configuredTdRoles)
{
List<UserInfo> adUsersForTdRole = new List<UserInfo>();
adUsersForTdRole.AddRange(AdRepository.GetGroupMembersFromAd(tdRole));
foreach (var sMember in sqlCacheMembers.Where(rm => rm.GroupName == tdRole))//find roles in SQL that aren't in AD anymore
{
if (adUsersForTdRole.Count(au => au.Name == sMember.MemberName) < 1)
{
ctx.AdCachedRoleMembers.DeleteObject(sMember);
}
}
foreach (var aMember in adUsersForTdRole)// file roles in AD that need to be updated/added to SQL
{
var sMember = sqlCacheMembers.SingleOrDefault(sm => sm.GroupName == tdRole && sm.MemberName == aMember.Name);
if (sMember == null)//doesn't exist in SQL, insert
{
sMember = new AdCachedRoleMember();
sMember.GroupName = tdRole;
ctx.AdCachedRoleMembers.AddObject(sMember);
}
sMember.MemberName = aMember.Name;
sMember.MemberDisplayName = aMember.DisplayName;
}
ctx.SaveChanges();
}
}
}
/// <summary>
/// Gets the user property from the SQL store.
/// </summary>
/// <remarks>
/// If the SQL store doesn't contain data for the user/property requested it will
/// add the user/property to the table so it gets fetched next time the system
/// refreshes from AD. In the meantime though, the method will return null.
/// </remarks>
/// <param name="userName">Name of the user.</param>
/// <param name="propertyName">Name of the property.</param>
/// <returns>The property stored in the SQL cache; null if no value cached</returns>
internal string GetUserProperty(string userName, string propertyName)
{
userName = userName.ToLowerInvariant();
string propertyValue = null;
using (TicketDeskEntities ctx = new TicketDeskEntities())
{
var val = ctx.AdCachedUserProperties.FirstOrDefault(p => p.UserName == userName && p.PropertyName == propertyName);
if (val == null)
{
//add this property/user to the table for the next automated refresh
var newUserProp = new AdCachedUserProperty()
{
UserName = userName,
PropertyName = propertyName,
PropertyValue = null,
LastRefreshed = null,
IsActiveInAd = true
};
ctx.AdCachedUserProperties.AddObject(newUserProp);
}
else
{
propertyValue = val.PropertyValue;
}
ctx.SaveChanges();
}
return propertyValue;
}
private void BuildUserPropertiesCache(bool includeInactiveAd)
{
var refreshTime = DateTime.Now.AddMinutes((AdUserPropertiesSqlCacheRefreshMinutes * -1));
using (TicketDeskEntities ctx = new TicketDeskEntities())
{
var propertiesForRefresh = ctx.AdCachedUserProperties.Where(up => !up.LastRefreshed.HasValue || up.LastRefreshed.Value <= refreshTime);
if(!includeInactiveAd)
{
propertiesForRefresh = propertiesForRefresh.Where(up => up.IsActiveInAd);
}
foreach(var property in propertiesForRefresh.ToList())
{
bool userFound;
var value = AdRepository.GetUserPropertyFromAd(property.UserName, property.PropertyName, out userFound);
property.IsActiveInAd = userFound;
if (userFound)//if the user wasn't found, we don't alter the last value fetched (last fetched value lives forever)
{
property.PropertyValue = value;
}
property.LastRefreshed = DateTime.Now;
ctx.SaveChanges();
}
}
}
private void EnsureStandardPropertiesForAllKnownUsers()
{
var allKnownUsers = GetAllKnownDistinctUserNamesFrom();
using (TicketDeskEntities ctx = new TicketDeskEntities())
{
//go ahead and fetch entire table. The table is kinda big, but realistically
// there should only be two rows per valid user... even with a thousand users,
// that's not an obsurde amount of data.
var existingUserProperties = ctx.AdCachedUserProperties.ToList();
//loop 1 to add all display name properties for any users not already in the SQL cache
var existingProps = existingUserProperties.Where(ep => ep.PropertyName == "displayName");
foreach (var knownUser in allKnownUsers)
{
if (existingProps.Count(ep => string.Equals(ep.UserName, knownUser, StringComparison.InvariantCultureIgnoreCase)) < 1)
{
var newUserProp = new AdCachedUserProperty()
{
UserName = knownUser,
PropertyName = "displayName",
IsActiveInAd = true
};
ctx.AdCachedUserProperties.AddObject(newUserProp);
}
}
//loop 2 to add all email properties for any users not already in the SQL cache
existingProps = existingUserProperties.Where(ep => ep.PropertyName == "mail");
foreach (var knownUser in allKnownUsers)
{
if (existingProps.Count(ep => string.Equals(ep.UserName, knownUser, StringComparison.InvariantCultureIgnoreCase)) < 1)
{
var newUserProp = new AdCachedUserProperty()
{
UserName = knownUser,
PropertyName = "mail",
IsActiveInAd = true
};
ctx.AdCachedUserProperties.AddObject(newUserProp);
}
}
ctx.SaveChanges();
}
}
/// <summary>
/// Ensures a valid next delivery date for any newly queud notifications when a notification has been rescheduled.
/// </summary>
/// <param name="ticketId">The ticket id.</param>
/// <param name="notifyUser">The notify user.</param>
/// <param name="lastSentCommentId">The last sent comment id.</param>
/// <param name="nextDeliveryAttemptDate">The next delivery attempt date.</param>
/// <remarks>
/// While the system is processing ticket emails, new notifications could get queued up.
/// This method should be called if the sending process has to re-schedule a notifiaciton
/// for a later re-try attempt. This will keep the notification that was being processed from
/// having it's next delivery date kicked out further than newer comments that have queued up.
/// It should look for any new notifications that are queued for the same user and ticket,
/// and ensure that their next-delivery date remains greater than the last one that was processed
/// and rescheduled.
/// </remarks>
public void EnsureValidNextDeliveryDateForNewlyQueudNotifications(int ticketId, string notifyUser, int lastSentCommentId, DateTime nextDeliveryAttemptDate)
{
using (var ctxNew = new TicketDeskEntities())
{
//the class's standard context will have cached data from any past reads. Do this on new context
var newNotes = from tn in ctxNew.TicketEventNotifications
where tn.TicketId == ticketId && tn.NotifyUser == notifyUser && tn.CommentId > lastSentCommentId && tn.NextDeliveryAttemptDate <= nextDeliveryAttemptDate
select tn;
if (newNotes.Count() > 0)
{
int advanceSeconds = 1;
foreach (var newNote in newNotes)
{
newNote.NextDeliveryAttemptDate = nextDeliveryAttemptDate.AddSeconds(advanceSeconds);
advanceSeconds++;
}
ctxNew.SaveChanges();
}
}
}