public void getLastXDays(int x)
{
using (var dbContext = new Model1Container())
{
DateTime span = DateTime.Today.AddDays(-x);
var lastTenDays = from db in dbContext.DbLogs
where (db.timeStamp > span)
select db.user;
}
}
//select all jobs from a user
public static List<int> FindAllJobs(String name)
{
using (var dbContext = new Model1Container())
{
dbContext.Database.Connection.Open();
IEnumerable<int> jobs = from db in dbContext.DbLogs where db.user == name select db.jobId;
return jobs.ToList();
}
}
//select all jobs from a user within the past X days
public static List<int> GetLastXDays(int x, string name)
{
using (var dbContext = new Model1Container())
{
DateTime span = DateTime.Today.AddDays(-x);
var lastTenDays = from db in dbContext.DbLogs
where (db.timeStamp > span) && db.user == name
select db.jobId;
return lastTenDays.ToList();
}
}
public static String[] FindJobsOwnedBy(string name)
{
using (var dbContext = new Model1Container())
{
dbContext.Database.Connection.Open();
List<String> arr = new List<string>();
var query = from db in dbContext.DbLogs where db.user == name select new {db.jobId, db.jobState, db.timeStamp};
foreach (var v in query)
arr.Add(v.jobId+","+v.jobState+","+v.timeStamp);
return arr.ToArray();
}
}
// add entry
public static void AddEntry(DateTime timeStamp, string jobState, string user, int jobId)
{
using (var dbContext = new Model1Container())
{
dbContext.Database.Connection.Open();
DbLog logEntry = new DbLog();
logEntry.timeStamp = timeStamp;
logEntry.jobState = jobState;
logEntry.user = user;
logEntry.jobId = jobId;
dbContext.DbLogs.Add(logEntry);
dbContext.SaveChanges();
}
}
//select all jobs submitted by a user within a given time period (this includes both the time and the date)
public static List<int> FindAllSubmitsWithin(string user, DateTime start, DateTime end)
{
using (var dbContext = new Model1Container())
{
dbContext.Database.Connection.Open();
IEnumerable<int> submits = from db in dbContext.DbLogs
where
db.user == user && start < db.timeStamp && db.timeStamp < end
&& db.jobState == "Submitted"
select db.jobId;
Console.WriteLine(user + " has within " + start + " and " + end + ":");
return submits.ToList();
}
}
// doesn't exist
public static void DropTable()
{
using (var dbContext = new Model1Container())
{
dbContext.Database.Connection.Open();
dbContext.Database.ExecuteSqlCommand("truncate table DBLogs");
}
}
//perform the same query as above but restricting the query to only one user
public static Dictionary<string, int> NrOfJobsWithinOne(DateTime start, DateTime end, string user)
{
using (var dbContext = new Model1Container())
{
dbContext.Database.Connection.Open();
var entries = from db in dbContext.DbLogs
where start < db.timeStamp && db.timeStamp < end && user == db.user
group db by db.jobState into g
select new { State = g.Key, Jobs = g };
Dictionary<string, int> d = new Dictionary<string, int>();
foreach (var g in entries)
{
int count = 0;
foreach (var job in g.Jobs)
{
count++;
}
d.Add(g.State, count);
}
return d;
}
}
//select all users
public static List<string> FindUsers()
{
using (var dbContext = new Model1Container())
{
dbContext.Database.Connection.Open();
IEnumerable<string> users = (from db in dbContext.DbLogs select db.user).Distinct();
return users.ToList();
}
}
