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