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