public List <ActivitySummaryModel> GetActivitySummary(DateTime dateFrom, DateTime dateTo, string userNames)
{
List <ActivitySummaryModel> activitySummary = new List <ActivitySummaryModel>();
try
{
string query = @"SELECT * FROM dbo.ActivitySummary";
DataTable dt = dbu.ExecuteDataTable(query, null);
activitySummary = dt.AsEnumerable().Select(r => new ActivitySummaryModel
{
UserName = r["UserName"] == DBNull.Value ? null : (string)r["UserName"],
Moment = r["Moment"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(r["Moment"]),
ActiveHours = r["ActiveHours"] == DBNull.Value ? 0 : Convert.ToDouble(r["ActiveHours"]),
LinearHours = r["LinearHours"] == DBNull.Value ? 0 : Convert.ToDouble(r["LinearHours"]) / 60,
SessionsCount = r["SessionsCount"] == DBNull.Value ? 0 : Convert.ToDouble(r["SessionsCount"]),
CommitsCount = r["CommitsCount"] == DBNull.Value ? 0 : Convert.ToDouble(r["CommitsCount"]),
PublicationsCount = r["PublicationsCount"] == DBNull.Value ? 0 : Convert.ToDouble(r["PublicationsCount"]),
LocalBuildsCount = r["LocalBuildsCount"] == DBNull.Value ? 0 : Convert.ToDouble(r["LocalBuildsCount"])
}).Where(asm => asm.Moment >= dateFrom && asm.Moment <= dateTo && userNames.Split(',').Contains(asm.UserName)).ToList();
return(activitySummary);
}
catch (Exception ex)
{
return(activitySummary);
}
}
public UserGroupManagementMatrix GetUsersGroupsMatrix()
{
UserGroupManagementMatrix userGroupManagementMatrix = new UserGroupManagementMatrix();
userGroupManagementMatrix.Groups = new List <string>();
string query = @"GetUserGroupMatrix";
DataTable dt = dbu.ExecuteDataTable(query, null, true);
foreach (DataColumn item in dt.Columns)
{
if (item.ColumnName != "UserName")
{
userGroupManagementMatrix.Groups.Add(item.ColumnName);
}
}
userGroupManagementMatrix.UsersInGroups = dt.AsEnumerable().Select(r =>
{
UserGroupOwning userGroupOwning = new UserGroupOwning();
userGroupOwning.IsUserInGroups = new List <bool>();
userGroupOwning.UserName = r["UserName"] == DBNull.Value ? null : (string)r["UserName"];
foreach (string group in userGroupManagementMatrix.Groups)
{
userGroupOwning.IsUserInGroups.Add(r[group] == DBNull.Value ? false : true);
}
return(userGroupOwning);
}).ToList();
return(userGroupManagementMatrix);
}
public IEnumerable <Session> GetSessions(DisplaySessionsMode displaySessionsMode)
{
List <Session> sessions = new List <Session>();
string query = @"select Id, UserName, MachineName, SessionBegin, SessionEnd, ActiveHours, SessionState, ClientUtcOffset, LastInputTime, ClientName, ClientDisplayDetails, ClientReportedIPAddress, ClientBuildNumber FROM Logins ";
if (displaySessionsMode != DisplaySessionsMode.All)
{
query += string.Format("WHERE SessionState = '{0}'", displaySessionsMode.ToString());
}
query += " ORDER BY Id DESC";
DataTable dt = dbu.ExecuteDataTable(query, null);
sessions = dt.AsEnumerable().Select(r => new Session()
{
Id = (int)r["Id"],
UserName = r["UserName"].ToString(),
MachineName = r["MachineName"].ToString(),
SessionBegin = Convert.ToDateTime(r["SessionBegin"]),
SessionEnd = r["SessionEnd"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(r["SessionEnd"]),
ActiveHours = Convert.ToDouble(r["ActiveHours"]),
SessionState = r["SessionState"].ToString(),
UtcOffset = Convert.ToDouble(r["ClientUtcOffset"] == DBNull.Value ? 0 : r["ClientUtcOffset"]),
LastInputTime = r["LastInputTime"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(r["LastInputTime"]),
ClientName = r["ClientName"] == DBNull.Value ? "-" : r["ClientName"].ToString(),
ClientDisplayDetails = r["ClientDisplayDetails"] == DBNull.Value ? "-" : r["ClientDisplayDetails"].ToString(),
ClientIPAddress = r["ClientReportedIPAddress"] == DBNull.Value ? "-" : r["ClientReportedIPAddress"].ToString(),
ClientBuildNumber = r["ClientBuildNumber"] == DBNull.Value ? 0 : (int)r["ClientBuildNumber"]
}).Select(s =>
{
if (s.SessionEnd == DateTime.MinValue)
{
s.SessionEnd = null;
}
else
{
s.SessionEnd = s.SessionEnd.Value.AddHours(-s.UtcOffset);
}
if (s.LastInputTime == DateTime.MinValue)
{
s.LastInputTime = null;
}
else
{
s.LastInputTime = s.LastInputTime.Value.AddHours(-s.UtcOffset);
}
s.SessionBegin = s.SessionBegin.Value.AddHours(-s.UtcOffset);
s.LinearHours = Math.Round(Convert.ToDouble(
s.SessionEnd.HasValue ? (s.SessionEnd.Value - s.SessionBegin.Value).TotalMinutes : (AppData.DateTimeConvertUtils.ConvertTimeByUtcOffset(DateTime.Now, s.UtcOffset).Value - s.SessionBegin.Value).TotalMinutes
) / 60, 4);
s.ActiveTime = s.LinearHours == 0 ? 0 : s.ActiveHours / s.LinearHours;
return(s);
}).ToList();
return(sessions);
}
public IEnumerable <UserActiveHours> GetUsersActiveHours(DateTime dateFrom, DateTime dateTo, string userNames)
{
List <UserActiveHours> usersActiveHours = new List <UserActiveHours>();
string query = @"SELECT UserName, SessionBegin, SessionEnd, ActiveHours, ClientUtcOffset
FROM [DevelopmentDashboard].[dbo].[Logins]
WHERE SessionBegin BETWEEN @StartDate AND @EndDate AND UserName IN
(" + String.Join(",", userNames.Split(',').Select(un => { string newUserName = "******" + un + "'"; return(newUserName); }).ToArray()) + ")";
DataTable dt = dbu.ExecuteDataTable(query, new Dictionary <string, object>
{
{ "@StartDate", dateFrom },
{ "@EndDate", dateTo }
});
usersActiveHours = dt.AsEnumerable().Select(r => new UserActiveHours
{
UserName = r["UserName"].ToString(),
SessionBegin = Convert.ToDateTime(r["SessionBegin"]),
SessionEnd = Convert.ToDateTime(r["SessionEnd"]),
ActiveHours = Convert.ToDouble(r["ActiveHours"])
}).ToList();
return(usersActiveHours);
}
public IEnumerable <UsersByGroup> GetUserNamesByGroups()
{
List <UsersByGroup> usersByGroups = new List <UsersByGroup>();
string query = "SELECT DISTINCT Name FROM Groups ORDER BY Name";
DataTable dt = dbu.ExecuteDataTable(query, null);
string[] groups = dt.AsEnumerable().Select(r => r["Name"].ToString()).ToArray();
foreach (string group in groups)
{
query = "SELECT DISTINCT UserName FROM Users u LEFT JOIN UsersInGroups uig ON u.Id = uig.UserId LEFT JOIN Groups g ON uig.GroupId = g.Id WHERE g.Name = @GroupName";
dt = dbu.ExecuteDataTable(query, new Dictionary <string, object> {
{ "@GroupName", group }
});
UsersByGroup ubg = new UsersByGroup();
ubg.key = group;
ubg.items = dt.AsEnumerable().Select(r => r["UserName"].ToString()).ToArray();
usersByGroups.Add(ubg);
}
query = @"SELECT DISTINCT l.UserName FROM Logins l
LEFT JOIN Users u ON u.UserName = l.UserName
LEFT JOIN UsersInGroups uig ON u.Id = uig.UserId
WHERE uig.UserId IS NULL";
dt = dbu.ExecuteDataTable(query, null);
UsersByGroup noGroup = new UsersByGroup();
noGroup.key = "No Group";
noGroup.items = dt.AsEnumerable().Select(r => r["UserName"].ToString()).ToArray();
usersByGroups.Add(noGroup);
return(usersByGroups);
}
/// <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);
}
}
}
public IEnumerable <MainPageUser> GetMainPageUsers()
{
string query = @"SELECT DISTINCT u.UserName,
(SELECT SUM(DISTINCT ActiveHours)
FROM Logins
WHERE SessionBegin > FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00')
AND SessionBegin <= FORMAT(GETDATE(), 'yyyy-MM-dd 23:59:59') AND UserName = u.UserName) AS DayActiveHours,
(SELECT SUM(DISTINCT l.ActiveHours)
FROM Logins l
WHERE SessionBegin > DATEADD(wk,DATEDIFF(wk,0,GETDATE()-1),0)
AND SessionBegin <= FORMAT(GETDATE(), 'yyyy-MM-dd 23:59:59') AND l.UserName = u.UserName) AS WeekActiveHours,
(SELECT TOP 1 LastInputTime FROM Logins WHERE UserName = u.UserName ORDER BY LastInputTime desc) AS LastInputTime,
(SELECT TOP 1 ClientUtcOffset FROM Logins WHERE UserName = u.UserName ORDER BY LastInputTime desc) AS LastInputTimeUtcOffset,
(SELECT COUNT(DISTINCT r.Id) FROM Revisions r
WHERE Moment > DATEADD(wk,DATEDIFF(wk,0,GETDATE()-1),0)
AND Moment <= FORMAT(GETDATE(), 'yyyy-MM-dd 23:59:59') AND r.UserId = u.Id ) AS WeekCommits
FROM Users u
LEFT JOIN Logins l ON u.UserName = l.UserName
ORDER BY LastInputTime desc";
DataTable dt = dbu.ExecuteDataTable(query, null);
return(dt.AsEnumerable().Select(r => new MainPageUser
{
UserName = r["UserName"].ToString(),
DayActiveHours = r["DayActiveHours"] == DBNull.Value ? 0 : Convert.ToDouble(r["DayActiveHours"]),
WeekActiveHours = r["WeekActiveHours"] == DBNull.Value ? 0 : Convert.ToDouble(r["WeekActiveHours"]),
LastInputTime = r["LastInputTime"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(r["LastInputTime"]),
LastInputTimeUtcOffset = r["LastInputTimeUtcOffset"] == DBNull.Value ? 0 : Convert.ToInt32(r["LastInputTimeUtcOffset"]),
WeekCommitsCount = r["WeekCommits"] == DBNull.Value ? 0 : Convert.ToInt32(r["WeekCommits"]),
}).Select(mpu =>
{
if (mpu.LastInputTime == DateTime.MinValue)
{
mpu.LastInputTime = null;
mpu.Status = "red";
}
else
{
mpu.LastInputTime = mpu.LastInputTime.Value.AddHours(-mpu.LastInputTimeUtcOffset);
if ((AppData.DateTimeConvertUtils.ConvertTimeByUtcOffset(DateTime.Now, mpu.LastInputTimeUtcOffset).Value -
mpu.LastInputTime.Value.AddHours(mpu.LastInputTimeUtcOffset)).TotalMinutes <
DDWebConfig.Instance.CheckActiveStatusTimeInMinutes)
{
mpu.Status = "green";
}
else if ((AppData.DateTimeConvertUtils.ConvertTimeByUtcOffset(DateTime.Now, mpu.LastInputTimeUtcOffset).Value -
mpu.LastInputTime.Value.AddHours(mpu.LastInputTimeUtcOffset)).TotalDays > DDWebConfig.Instance.YellowStatusUserAbsencePeriodInDays &&
(AppData.DateTimeConvertUtils.ConvertTimeByUtcOffset(DateTime.Now, mpu.LastInputTimeUtcOffset).Value
- mpu.LastInputTime.Value.AddHours(mpu.LastInputTimeUtcOffset)).TotalDays < DDWebConfig.Instance.RedStatusUserAbsencePeriodInDays)
{
mpu.Status = "yellow";
}
else if ((AppData.DateTimeConvertUtils.ConvertTimeByUtcOffset(DateTime.Now, mpu.LastInputTimeUtcOffset).Value -
mpu.LastInputTime.Value.AddHours(mpu.LastInputTimeUtcOffset)).TotalDays > DDWebConfig.Instance.RedStatusUserAbsencePeriodInDays)
{
mpu.Status = "red";
}
}
return mpu;
}));
}
