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();
}
}
}
public void ExecuteUpgrade(string script)
{
using (var ctx = new TicketDeskEntities())
{
var cString = (ctx.Connection as EntityConnection).StoreConnection.ConnectionString;//get connection string from entity framework context
//entity framework doesn't help us with executing DML and DDL; so we go back to old-skoo ado.
SqlConnection conn = new SqlConnection(cString);
SqlCommand cmd = new SqlCommand(script, conn);
conn.Open();
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
}
/// <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();
}
}
}
/// <summary>
/// Gets the group members.
/// </summary>
/// <param name="groupName">Name of the group.</param>
/// <returns></returns>
internal UserInfo[] GetGroupMembers(string groupName)
{
groupName = groupName.ToLowerInvariant();
UserInfo[] usersInGroup = null;
using (TicketDeskEntities ctx = new TicketDeskEntities())
{
var users = from m in ctx.AdCachedRoleMembers
where m.GroupName == groupName
select new UserInfo() { Name = m.MemberName, DisplayName = m.MemberDisplayName };
if (users.Count() > 0)
{
usersInGroup = users.ToArray();
}
}
return usersInGroup;
}
/// <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 IEnumerable<string> GetAllKnownDistinctUserNamesFrom()
{
List<string> allUsers = new List<string>();
using (TicketDeskEntities ctx = new TicketDeskEntities())
{
//distincting each one of these just reduces the amount of data coming back from SQL
// it doesn't actually give us a truly distinct master list though.
allUsers.AddRange(ctx.AdCachedRoleMembers.Select(r => r.MemberName.ToLower()).Distinct());
allUsers.AddRange(ctx.Tickets.Select(t => t.CreatedBy.ToLower()).Distinct());
allUsers.AddRange(ctx.Tickets.Select(t => t.Owner.ToLower()).Distinct());
allUsers.AddRange(ctx.Tickets.Select(t => t.AssignedTo.ToLower()).Distinct());
allUsers.AddRange(ctx.Tickets.Select(t => t.CurrentStatusSetBy.ToLower()).Distinct());
allUsers.AddRange(ctx.Tickets.Select(t => t.LastUpdateBy.ToLower()).Distinct());
allUsers.AddRange(ctx.TicketComments.Select(tc => tc.CommentedBy.ToLower()).Distinct());
allUsers.AddRange(ctx.TicketAttachments.Select(ta => ta.UploadedBy.ToLower()).Distinct());
}
var badUsers = allUsers.Where(u => string.IsNullOrEmpty(u)).ToArray();
foreach (var badUser in badUsers)
{
allUsers.Remove(badUser);
}
return allUsers.Distinct();//this gets the truly distinct list
}
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();
}
}
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();
}
}
}
/// <summary>
/// Saves the settings.
/// </summary>
/// <remarks>
/// In the EF implementation we don't need the settings passed as a parameter, but we pass it anyway as other repositories may not work the same way
/// </remarks>
/// <param name="settingsToSave">The settings to save.</param>
/// <returns></returns>
public bool SaveSettings(IEnumerable<Setting> settingsToSave)
{
foreach (var s in settingsToSave)
{
if (s.EntityState == System.Data.EntityState.Detached)
{
ctx.Settings.AddObject(s);
}
}
ctx.SaveChanges();
ctx = new TicketDeskEntities();//reset the entities
return true;
}