protected override ActiveSession CheckSessionRepositoryExisting(ActiveSession activeSession)
{
try
{
dbu.ExecuteRead(string.Format(@"
SELECT id, ActiveHours, LastInputTime, ClientUtcOffset
FROM Logins
WHERE UserName ='******' AND MachineName = '{1}' AND FORMAT(SessionBegin, 'yyyy-MM-dd HH:mm:ss') = '{2}'",
activeSession.UserName, activeSession.MachineName, activeSession.SessionBegin.Value.ToString("yyyy-MM-dd HH:mm:ss")), null, delegate(SqlDataReader sdr)
{
activeSession.DbId = sdr.GetInt32(0);
activeSession.ActivityHours += sdr.GetDouble(1);
if (sdr.IsDBNull(2))
{
activeSession.LastInputTime = null;
}
else
{
activeSession.LastInputTime = sdr.GetDateTime(2);
}
}, -1);
}
catch (Exception ex)
{
Utils.LoggingUtils.DefaultLogger.AddLogMessage(this, MessageType.Error, "Ошибка при попытке получить сессиию из БД. Message: {0}. Trace: {1}", ex.Message, ex.StackTrace);
}
return(activeSession);
}
public double GetWorkingHours(string userName, DateTime periodBegin, DateTime periodEnd)
{
double res = 0;
dbU.ExecuteRead(@"select SUM(DATEDIFF(minute, ChunkBegin, ChunkEnd) * IsUserActive) * 1.0 / 60 as WorkingMinutes from SessionActivityProfiles
left join Logins on Logins.Id = SessionActivityProfiles.SessionId
where UserName = @userName and ChunkBegin BETWEEN @periodBegin AND @periodEnd", new Dictionary<string, object>()
{
{ "@periodBegin", periodBegin },
{ "@periodEnd", periodEnd },
{ "@userName", userName }
}
, delegate(SqlDataReader sdr)
{
if(!sdr.IsDBNull(0))
res = (double)sdr.GetDecimal(0);
}, -1);
return res;
}
public int GetCodeMetricsHistoryCount()
{
string query = @"SELECT COUNT(cmh.Id)
FROM CodeMetricsHistory cmh
LEFT JOIN MetricsValues mv ON cmh.Id = mv.CodeMetricsHistory_Id
LEFT JOIN Revisions r ON cmh.RevisionNumber = r.RevisionNumber
LEFT JOIN Users u ON r.UserId = u.Id
WHERE MetricName IS NOT NULL AND ProjectName <> ''";
int count = 0;
dbu.ExecuteRead(query,
null, delegate(SqlDataReader sdr)
{
count = sdr.GetInt32(0);
}, -1);
return(count);
}
/// <summary>
/// Handle unexpectedly ended sessions
/// </summary>
public void ClearOrphanedSessions()
{
List <OrphanedSession> orphanedSessions = new List <OrphanedSession>();
string query = @"SELECT Id, ClientUtcOffset
FROM Logins WHERE
(SessionEnd IS NULL OR SessionState = 'Active') AND Comment IS NULL ";
try
{
DataTable dt = dbu.ExecuteDataTable(query, null);
orphanedSessions = dt.AsEnumerable().Select(r => new OrphanedSession
{
Id = Convert.ToInt32(r["Id"]),
UtcOffset = r["ClientUtcOffset"] == DBNull.Value ? 0 : Convert.ToDouble(r["ClientUtcOffset"])
}).ToList();
}
catch (Exception ex)
{
LoggingUtils.DefaultLogger.AddLogMessage(this, MessageType.Error, "Ошибка при попытке получить разорванные сессии. Type:{0}. Message: {1}", ex.GetType(), ex.Message);
}
foreach (OrphanedSession orphanedSession in orphanedSessions)
{
DateTime?lastSessionActivityProfile = null;
try
{
dbu.ExecuteRead(string.Format(@"
SELECT TOP 1 ChunkBegin
FROM SessionActivityProfiles
WHERE SessionId = {0} ORDER BY ChunkBegin DESC",
orphanedSession.Id), null, delegate(SqlDataReader sdr)
{
lastSessionActivityProfile = sdr.GetDateTime(0);
}, -1);
string comment = null;
if (!lastSessionActivityProfile.HasValue)
{
comment = "Session was aborted before any activity was registered";
dbu.ExecuteScalarQuery(@"UPDATE Logins set SessionEnd = (SELECT SessionBegin FROM Logins WHERE Id = @dbId), SessionState = @SessionState, Comment = @Comment WHERE id = @dbID",
new Dictionary <string, object>()
{
{ "@SessionState", "MonitorAborted" },
{ "@Comment", comment },
{ "@dbID", orphanedSession.Id }
});
}
else if ((AppData.DateTimeConvertUtils.ConvertTimeByUtcOffset(DateTime.Now, orphanedSession.UtcOffset).Value -
lastSessionActivityProfile.Value).TotalHours > LogonTracerAdministrationConfig.Instance.OrhpanedSessionTimeoutInHours)
{
comment = string.Format("Session was aborted or unavaliable longer than {0} hours", LogonTracerAdministrationConfig.Instance.OrhpanedSessionTimeoutInHours);
dbu.ExecuteScalarQuery(@"UPDATE Logins set SessionEnd = @SessionEnd, SessionState = @SessionState, Comment = @Comment WHERE id = @dbID",
new Dictionary <string, object>()
{
{ "@SessionEnd", (object)lastSessionActivityProfile ?? DBNull.Value },
{ "@SessionState", "MonitorAborted" },
{ "@Comment", comment },
{ "@dbID", orphanedSession.Id }
});
}
}
catch (Exception ex)
{
Utils.LoggingUtils.DefaultLogger.AddLogMessage(this, MessageType.Error, "Ошибка при попытке обработать разорванные сессии. Type:{0}. Message: {1}", ex.GetType(), ex.Message);
}
}
}