public static List <ExtractedItem> GetWebsitesVisited(DateTimeOffset date)
{
var files = new List <ExtractedItem>();
try
{
var onlySearchForProgramsWhereRulesExists = GetSqlForProgramsToSearch();
var query = "SELECT process, window, sum(difference) / 60.0 as 'durInMin' "
+ "FROM ( "
+ "SELECT t1.id, t1.process, t1.window, t1.time as 'from', t2.time as 'to', (strftime('%s', t2.time) - strftime('%s', t1.time)) as 'difference' "
+ "FROM " + Shared.Settings.WindowsActivityTable + " t1 LEFT JOIN " + Shared.Settings.WindowsActivityTable + " 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") + " "
+ onlySearchForProgramsWhereRulesExists
+ "GROUP BY t1.id, t1.time "
+ ") "
+ "WHERE difference > 0 "
+ "GROUP BY window "
+ "ORDER BY durInMin DESC;";
var table = Database.GetInstance().ExecuteReadQuery(query);
foreach (DataRow row in table.Rows)
{
var process = (string)row["process"];
var windowTitle = (string)row["window"];
var website = GetWebsiteDetails(process, windowTitle);
var durInMin = (double)row["durInMin"];
if (string.IsNullOrEmpty(website) || durInMin < 1)
{
continue;
}
var art = new ExtractedItem
{
ItemName = website,
DurationInMins = durInMin
};
files.Add(art);
}
table.Dispose();
}
catch (Exception e)
{
Shared.Logger.WriteToLogFile(e);
}
return(files);
}
public static List<ExtractedItem> GetWebsitesVisited(DateTimeOffset date)
{
var files = new List<ExtractedItem>();
try
{
var onlySearchForProgramsWhereRulesExists = GetSqlForProgramsToSearch();
var query = "SELECT process, window, sum(difference) / 60.0 as 'durInMin' "
+ "FROM ( "
+ "SELECT t1.id, t1.process, t1.window, t1.time as 'from', t2.time as 'to', (strftime('%s', t2.time) - strftime('%s', t1.time)) as 'difference' "
+ "FROM " + Shared.Settings.WindowsActivityTable + " t1 LEFT JOIN " + Shared.Settings.WindowsActivityTable + " 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") + " "
+ onlySearchForProgramsWhereRulesExists
+ "GROUP BY t1.id, t1.time "
+ ") "
+ "WHERE difference > 0 "
+ "GROUP BY window "
+ "ORDER BY durInMin DESC;";
var table = Database.GetInstance().ExecuteReadQuery(query);
foreach (DataRow row in table.Rows)
{
var process = (string)row["process"];
var windowTitle = (string)row["window"];
var website = GetWebsiteDetails(process, windowTitle);
var durInMin = (double)row["durInMin"];
if (string.IsNullOrEmpty(website) || durInMin < 1) continue;
var art = new ExtractedItem
{
ItemName = website,
DurationInMins = durInMin
};
files.Add(art);
}
table.Dispose();
}
catch (Exception e)
{
Shared.Logger.WriteToLogFile(e);
}
return files;
}
public static List <ExtractedItem> GetTimeSpentInOutlook(DateTimeOffset date)
{
var emailInfos = new List <ExtractedItem>();
try
{
var query = "SELECT window, sum(difference) / 60.0 as 'durInMin' "
+ "FROM ( "
+ "SELECT t1.id, t1.process, t1.window, t1.time as 'from', t2.time as 'to', (strftime('%s', t2.time) - strftime('%s', t1.time)) as 'difference' "
+ "FROM " + Settings.WindowsActivityTable + " t1 LEFT JOIN " + Settings.WindowsActivityTable + " 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") + " "
+ "AND lower(t1.process) = 'outlook' "
+ "GROUP BY t1.id, t1.time "
+ ") "
+ "WHERE difference > 0 "
+ "GROUP BY window "
+ "ORDER BY durInMin DESC;";
var table = Database.GetInstance().ExecuteReadQuery(query);
foreach (DataRow row in table.Rows)
{
var windowTitle = (string)row["window"];
var emailDetails = CleanWindowTitle(windowTitle);
var durInMin = (double)row["durInMin"];
if (string.IsNullOrEmpty(emailDetails) || durInMin < 1)
{
continue;
}
var art = new ExtractedItem
{
ItemName = emailDetails,
DurationInMins = durInMin
};
emailInfos.Add(art);
}
table.Dispose();
}
catch (Exception e)
{
Logger.WriteToLogFile(e);
}
return(emailInfos);
}
public static List<ExtractedItem> GetTimeSpentInOutlook(DateTimeOffset date)
{
var emailInfos = new List<ExtractedItem>();
try
{
var query = "SELECT window, sum(difference) / 60.0 as 'durInMin' "
+ "FROM ( "
+ "SELECT t1.id, t1.process, t1.window, t1.time as 'from', t2.time as 'to', (strftime('%s', t2.time) - strftime('%s', t1.time)) as 'difference' "
+ "FROM " + Settings.WindowsActivityTable + " t1 LEFT JOIN " + Settings.WindowsActivityTable + " 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") + " "
+ "AND lower(t1.process) = 'outlook' "
+ "GROUP BY t1.id, t1.time "
+ ") "
+ "WHERE difference > 0 "
+ "GROUP BY window "
+ "ORDER BY durInMin DESC;";
var table = Database.GetInstance().ExecuteReadQuery(query);
foreach (DataRow row in table.Rows)
{
var windowTitle = (string)row["window"];
var emailDetails = CleanWindowTitle(windowTitle);
var durInMin = (double)row["durInMin"];
if (string.IsNullOrEmpty(emailDetails) || durInMin < 1) continue;
var art = new ExtractedItem
{
ItemName = emailDetails,
DurationInMins = durInMin
};
emailInfos.Add(art);
}
table.Dispose();
}
catch (Exception e)
{
Logger.WriteToLogFile(e);
}
return emailInfos;
}
