/// <summary> /// Saves the timestamp, start and end, process name and window title into the database. /// /// In case the user doesn't want the window title to be stored (For privacy reasons), /// it is obfuscated. /// </summary> /// <param name="window"></param> /// <param name="process"></param> internal static void InsertSnapshot(WindowsActivityEntry entry) { try { if (Shared.Settings.AnonymizeSensitiveData) { var activityCategory = ProcessToActivityMapper.Map(entry.Process, entry.WindowTitle); entry.WindowTitle = string.Format("{0} (category: {1})", Dict.Anonymized, activityCategory); // obfuscate window title } // if end time is missing, don't store anything if (entry.TsEnd == DateTime.MinValue) { Database.GetInstance().LogWarning("TsEnd of WindowsActivitySwitch was empty."); return; } var query = string.Format(QUERY_INSERT, "strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')", Database.GetInstance().QTime2(entry.TsStart), Database.GetInstance().QTime2(entry.TsEnd), Database.GetInstance().Q(entry.WindowTitle), Database.GetInstance().Q(entry.Process)); Database.GetInstance().ExecuteDefaultQuery(query); } catch (Exception e) { Logger.WriteToLogFile(e); } }
/// <summary> /// Fetches the activities a developer has on his computer for a given date in an /// ordered list according to time. Each activity matched to a single window ** temporary ** /// </summary> /// <param name="date"></param> /// <returns></returns> internal static List <WindowsActivity> GetDayWindowTitleData(DateTimeOffset date) { var orderedActivityList = new List <WindowsActivity>(); try { var query = "SELECT tsStart, tsEnd, window, process, (strftime('%s', tsEnd) - strftime('%s', tsStart)) as 'durInSec' " + "FROM " + Settings.DbTable + " " + "WHERE " + Database.GetInstance().GetDateFilteringStringForQuery(VisType.Day, date, "tsStart") + " AND " + Database.GetInstance().GetDateFilteringStringForQuery(VisType.Day, date, "tsEnd") + " " + "ORDER BY tsStart;"; var table = Database.GetInstance().ExecuteReadQuery(query); if (table != null) { WindowsActivity previousWindowsActivityEntry = null; foreach (DataRow row in table.Rows) { // fetch items from database var e = new WindowsActivity(); e.StartTime = DateTime.Parse((string)row["tsStart"], CultureInfo.InvariantCulture); e.EndTime = DateTime.Parse((string)row["tsEnd"], CultureInfo.InvariantCulture); e.DurationInSeconds = row.IsNull("durInSec") ? 0 : Convert.ToInt32(row["durInSec"], CultureInfo.InvariantCulture); var processName = (string)row["process"]; // make window titles more readable (TODO: improve!) var windowTitle = (string)row["window"]; windowTitle = WindowTitleWebsitesExtractor.GetWebsiteDetails(processName, windowTitle); //windowTitle = WindowTitleArtifactExtractor.GetArtifactDetails(processName, windowTitle); //windowTitle = WindowTitleCodeExtractor.GetProjectName(windowTitle); // map process and window to activity e.ActivityCategory = ProcessToActivityMapper.Map(processName, windowTitle); // check if we add a as first item or not if (previousWindowsActivityEntry != null) { e.WindowProcessList.Add(new WindowProcessItem(processName, windowTitle)); orderedActivityList.Add(e); } else // first item { orderedActivityList.Add(e); } previousWindowsActivityEntry = e; } table.Dispose(); } } catch (Exception e) { Logger.WriteToLogFile(e); } return(orderedActivityList); }
/// <summary> /// Saves the timestamp, start and end, process name and window title into the database. /// /// In case the user doesn't want the window title to be stored (For privacy reasons), /// it is obfuscated. /// </summary> /// <param name="window"></param> /// <param name="process"></param> internal static void InsertSnapshot(WindowsActivityEntry entry) { try { // if user is browsing in InPrivate-mode, obfuscate window title (doesn't work in Google Chrome!) if (ProcessToActivityMapper.IsBrowser(entry.Process) && Settings.InkognitoBrowsingTerms.Any(entry.WindowTitle.ToLower().Contains)) { entry.WindowTitle = Dict.Anonymized; // obfuscate window title } // if user enabled private tracking, obfuscate window title if (Shared.Settings.AnonymizeSensitiveData) { var activityCategory = ProcessToActivityMapper.Map(entry.Process, entry.WindowTitle); entry.WindowTitle = string.Format("{0} (category: {1})", Dict.Anonymized, activityCategory); // obfuscate window title } // if end time is missing, don't store anything if (entry.TsEnd == DateTime.MinValue) { Database.GetInstance().LogWarning("TsEnd of WindowsActivitySwitch was empty."); return; } var query = string.Format(QUERY_INSERT, "strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')", Database.GetInstance().QTime2(entry.TsStart), Database.GetInstance().QTime2(entry.TsEnd), Database.GetInstance().Q(entry.WindowTitle), Database.GetInstance().Q(entry.Process)); Database.GetInstance().ExecuteDefaultQuery(query); } catch (Exception e) { Logger.WriteToLogFile(e); } }
/// <summary> /// Fetches the activities a developer has on his computer for a given date in an /// ordered list according to time. /// </summary> /// <param name="date"></param> /// <returns></returns> internal static List <WindowsActivity> GetDayTimelineData(DateTimeOffset date) { var orderedActivityList = new List <WindowsActivity>(); try { var query = "SELECT t1.time as 'tsStart', t2.time as 'tsEnd', t1.window, t1.process, (strftime('%s', t2.time) - strftime('%s', t1.time)) as 'durInSec' " //t1.id, t1.time as 'from', t2.time as 'to' + "FROM " + Settings.DbTable + " t1 LEFT JOIN " + Settings.DbTable + " t2 on t1.id + 1 = t2.id " + "WHERE " + Database.GetInstance().GetDateFilteringStringForQuery(VisType.Day, date, "t1.time") + " and " + Database.GetInstance().GetDateFilteringStringForQuery(VisType.Day, date, "t2.time") + " " + "ORDER BY t1.time;"; var table = Database.GetInstance().ExecuteReadQuery(query); WindowsActivity _previousWindowsActivityEntry = null; foreach (DataRow row in table.Rows) { // fetch items from database var e = new WindowsActivity(); e.StartTime = DateTime.Parse((string)row["tsStart"], CultureInfo.InvariantCulture); e.EndTime = DateTime.Parse((string)row["tsEnd"], CultureInfo.InvariantCulture); e.DurationInSeconds = row.IsNull("durInSec") ? 0 : Convert.ToInt32(row["durInSec"], CultureInfo.InvariantCulture); var processName = (string)row["process"]; // make window titles more readable (TODO: improve!) var windowTitle = (string)row["window"]; windowTitle = WindowTitleWebsitesExtractor.GetWebsiteDetails(processName, windowTitle); windowTitle = WindowTitleArtifactExtractor.GetArtifactDetails(processName, windowTitle); windowTitle = WindowTitleCodeExtractor.GetProjectName(windowTitle); // map process and window to activity ProcessToActivityMapper.Map(e, processName, windowTitle); // check if we add a new item, or merge with the previous one if (_previousWindowsActivityEntry != null) { // previous item is same, update it (duration and tsEnd) if (e.ActivityCategory == _previousWindowsActivityEntry.ActivityCategory) { var lastItem = orderedActivityList.Last(); lastItem.DurationInSeconds += e.DurationInSeconds; lastItem.EndTime = e.EndTime; lastItem.WindowProcessList.Add(new WindowProcessItem(processName, windowTitle)); } // previous item is different, add it to list else { e.WindowProcessList.Add(new WindowProcessItem(processName, windowTitle)); orderedActivityList.Add(e); } } else // first item { orderedActivityList.Add(e); } _previousWindowsActivityEntry = e; } table.Dispose(); } catch (Exception e) { Logger.WriteToLogFile(e); } return(orderedActivityList); }