public List <TicketSlaInfo> GetAllUnnotifiedAndExpiredSla(LoginUser loginUser) { List <TicketSlaInfo> unnotifiedAndExpiredSla = new List <TicketSlaInfo>(); using (SqlConnection connection = new SqlConnection(loginUser.ConnectionString)) { using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandText = @" SELECT t.TicketId, t.TicketStatusId, OrganizationId, SlaViolationTimeClosed, SlaWarningTimeClosed, SlaViolationLastAction, SlaWarningLastAction, SlaViolationInitialResponse, SlaWarningInitialResponse FROM Tickets AS t WITH(NOLOCK) LEFT JOIN SlaNotifications AS sn WITH(NOLOCK) ON t.TicketID = sn.TicketID WHERE t.DateClosed IS NULL AND ( ( t.SlaViolationTimeClosed IS NOT NULL AND t.SlaViolationTimeClosed < DATEADD(DAY, 1, GETUTCDATE()) AND t.SlaViolationTimeClosed > DATEADD(DAY, -1, GETUTCDATE()) AND t.SlaViolationTimeClosed > DATEADD(MINUTE, 10, ISNULL(sn.TimeClosedViolationDate, '1/1/1980')) ) OR ( t.SlaWarningTimeClosed IS NOT NULL AND t.SlaWarningTimeClosed < DATEADD(DAY, 1, GETUTCDATE()) AND t.SlaWarningTimeClosed > DATEADD(DAY, -1, GETUTCDATE()) AND t.SlaWarningTimeClosed > DATEADD(MINUTE, 10, ISNULL(sn.TimeClosedWarningDate, '1/1/1980')) ) OR ( t.SlaViolationLastAction IS NOT NULL AND t.SlaViolationLastAction < DATEADD(DAY, 1, GETUTCDATE()) AND t.SlaViolationLastAction > DATEADD(DAY, -1, GETUTCDATE()) AND t.SlaViolationLastAction > DATEADD(MINUTE, 10, ISNULL(sn.LastActionViolationDate, '1/1/1980')) ) OR ( t.SlaWarningLastAction IS NOT NULL AND t.SlaWarningLastAction < DATEADD(DAY, 1, GETUTCDATE()) AND t.SlaWarningLastAction > DATEADD(DAY, -1, GETUTCDATE()) AND t.SlaWarningLastAction > DATEADD(MINUTE, 10, ISNULL(sn.LastActionWarningDate, '1/1/1980')) ) OR ( t.SlaViolationInitialResponse IS NOT NULL AND t.SlaViolationInitialResponse < DATEADD(DAY, 1, GETUTCDATE()) AND t.SlaViolationInitialResponse > DATEADD(DAY, -1, GETUTCDATE()) AND t.SlaViolationInitialResponse > DATEADD(MINUTE, 10, ISNULL(sn.InitialResponseViolationDate, '1/1/1980')) ) OR ( t.SlaWarningInitialResponse IS NOT NULL AND t.SlaWarningInitialResponse < DATEADD(DAY, 1, GETUTCDATE()) AND t.SlaWarningInitialResponse > DATEADD(DAY, -1, GETUTCDATE()) AND t.SlaWarningInitialResponse > DATEADD(MINUTE, 10, ISNULL(sn.InitialResponseWarningDate, '1/1/1980')) ) ) "; command.CommandText = @" SELECT t.TicketId, t.TicketStatusId, OrganizationId, SlaViolationTimeClosed, SlaWarningTimeClosed, SlaViolationLastAction, SlaWarningLastAction, SlaViolationInitialResponse, SlaWarningInitialResponse FROM Tickets t with(nolock) LEFT JOIN SlaNotifications sn with(nolock) ON t.TicketID = sn.TicketID WHERE t.ticketID = 3887997"; command.CommandType = CommandType.Text; connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); if (reader.HasRows) { while (reader.Read()) { var col = reader.GetOrdinal("SlaViolationTimeClosed"); TicketSlaInfo ticket = new TicketSlaInfo(LoginUser) { TicketId = (Int32)reader["TicketId"], TicketStatusId = (Int32)reader["TicketStatusId"], OrganizationId = (Int32)reader["OrganizationId"], SlaViolationTimeClosed = reader.IsDBNull(col) ? (DateTime?)null : (DateTime?)reader.GetDateTime(col), SlaWarningTimeClosed = reader.GetNullableDateTime("SlaWarningTimeClosed"), SlaViolationLastAction = reader.GetNullableDateTime("SlaViolationLastAction"), SlaWarningLastAction = reader.GetNullableDateTime("SlaWarningLastAction"), SlaViolationInitialResponse = reader.GetNullableDateTime("SlaViolationInitialResponse"), SlaWarningInitialResponse = reader.GetNullableDateTime("SlaWarningInitialResponse") }; unnotifiedAndExpiredSla.Add(ticket); } } } } return(unnotifiedAndExpiredSla); }
private void ProcessTicket(TicketSlaInfo ticket) { UpdateHealth(); bool isPaused = false; bool isPending = false; int? slaTriggerId = null; Logs.WriteEvent("Getting SlaTicket record"); SlaTicket slaTicket = SlaTickets.GetSlaTicket(LoginUser, ticket.TicketId); if (slaTicket != null) { isPaused = ticket.IsSlaPaused(slaTicket.SlaTriggerId, ticket.OrganizationId); isPending = slaTicket.IsPending; slaTriggerId = slaTicket.SlaTriggerId; } Logs.WriteEventFormat("IsPaused: {0}; IsPending: {1}", isPaused.ToString(), isPending.ToString()); if (!isPaused && !isPending) { SlaTriggersView triggers = new SlaTriggersView(LoginUser); triggers.LoadByTicketId(ticket.TicketId); bool warnGroup = false; bool warnUser = false; bool vioGroup = false; bool vioUser = false; foreach (SlaTriggersViewItem item in triggers) { warnGroup = item.NotifyGroupOnWarning || warnGroup; warnUser = item.NotifyUserOnWarning || warnUser; vioGroup = item.NotifyGroupOnViolation || vioGroup; vioUser = item.NotifyUserOnViolation || vioUser; } SlaNotification notification = SlaNotifications.GetSlaNotification(LoginUser, ticket.TicketId); if (notification == null) { notification = (new SlaNotifications(LoginUser)).AddNewSlaNotification(); notification.TicketID = ticket.TicketId; } DateTime notifyTime; if (ticket.SlaViolationInitialResponse != null && ticket.SlaViolationInitialResponse <= DateTime.UtcNow) { notifyTime = (DateTime)ticket.SlaViolationInitialResponse; if (!IsTooOld(notifyTime)) { if (notification.InitialResponseViolationDate == null || Math.Abs(((DateTime)notification.InitialResponseViolationDateUtc - notifyTime).TotalMinutes) > 5) { NotifyViolation(ticket.TicketId, vioUser, vioGroup, false, SlaViolationType.InitialResponse, notification, slaTriggerId); notification.InitialResponseViolationDate = notifyTime; } } } else if (ticket.SlaWarningInitialResponse != null && ticket.SlaWarningInitialResponse <= DateTime.UtcNow) { notifyTime = (DateTime)ticket.SlaWarningInitialResponse; if (!IsTooOld(notifyTime)) { if (notification.InitialResponseWarningDate == null || Math.Abs(((DateTime)notification.InitialResponseWarningDateUtc - notifyTime).TotalMinutes) > 5) { NotifyViolation(ticket.TicketId, warnUser, warnGroup, true, SlaViolationType.InitialResponse, notification, slaTriggerId); notification.InitialResponseWarningDate = notifyTime; } } } if (ticket.SlaViolationLastAction != null && ticket.SlaViolationLastAction <= DateTime.UtcNow) { notifyTime = (DateTime)ticket.SlaViolationLastAction; if (!IsTooOld(notifyTime)) { if (notification.LastActionViolationDate == null || Math.Abs(((DateTime)notification.LastActionViolationDateUtc - notifyTime).TotalMinutes) > 5) { NotifyViolation(ticket.TicketId, vioUser, vioGroup, false, SlaViolationType.LastAction, notification, slaTriggerId); notification.LastActionViolationDate = notifyTime; } } } else if (ticket.SlaWarningLastAction != null && ticket.SlaWarningLastAction <= DateTime.UtcNow) { notifyTime = (DateTime)ticket.SlaWarningLastAction; if (!IsTooOld(notifyTime)) { if (notification.LastActionWarningDate == null || Math.Abs(((DateTime)notification.LastActionWarningDateUtc - notifyTime).TotalMinutes) > 5) { NotifyViolation(ticket.TicketId, warnUser, warnGroup, true, SlaViolationType.LastAction, notification, slaTriggerId); notification.LastActionWarningDate = notifyTime; } } } if (ticket.SlaViolationTimeClosed != null && ticket.SlaViolationTimeClosed <= DateTime.UtcNow) { notifyTime = (DateTime)ticket.SlaViolationTimeClosed; if (!IsTooOld(notifyTime)) { if (notification.TimeClosedViolationDate == null || Math.Abs(((DateTime)notification.TimeClosedViolationDateUtc - notifyTime).TotalMinutes) > 5) { NotifyViolation(ticket.TicketId, vioUser, vioGroup, false, SlaViolationType.TimeClosed, notification, slaTriggerId); notification.TimeClosedViolationDate = notifyTime; } } } else if (ticket.SlaWarningTimeClosed != null && ticket.SlaWarningTimeClosed <= DateTime.UtcNow) { notifyTime = (DateTime)ticket.SlaWarningTimeClosed; if (!IsTooOld(notifyTime)) { if (notification.TimeClosedWarningDate == null || Math.Abs(((DateTime)notification.TimeClosedWarningDateUtc - notifyTime).TotalMinutes) > 5) { NotifyViolation(ticket.TicketId, warnUser, warnGroup, true, SlaViolationType.TimeClosed, notification, slaTriggerId); notification.TimeClosedWarningDate = notifyTime; } } } notification.Collection.Save(); } }