public static void CreateTask(Task task)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
context.Tasks.Add(task);
context.SaveChanges();
}
}
public static User GetUserById(int id)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var user = (from usr in context.Users
where usr.UserId == id
select usr).FirstOrDefault<User>();
return user;
}
}
public static string GetStatMessById(int id)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var mess = (from taskStat in context.TasksStatus
where taskStat.Id == id
select taskStat.Status).FirstOrDefault<string>();
return mess;
}
}
public static Task GetTaskById(int id)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var resultTask = (from task in context.Tasks
where task.Id == id
select task).FirstOrDefault<Task>();
return resultTask;
}
}
public static List<User> GetAllActiveUsers()
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var users = (from usr in context.Users
where usr.IsDeleted == false
select usr).ToList<User>();
return users;
}
}
public static List<WorkingCard> GetAllByUserName(int id)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
User usr = UserUtility.GetUserById(id);
List<WorkingCard> allCards = (from card in context.WorkingCard
where card.UserId == usr.UserId
select card).ToList<WorkingCard>();
return allCards;
}
}
public static List<Task> GetAllActiveTasks()
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var tasks = (from task in context.Tasks
where task.IsDeleted == false
select task).ToList<Task>();
return tasks;
}
}
public static List<UsersTask> GetAllUsersOnTask(int taskId)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
List<UsersTask> result = (from usrTsk in context.UsersTasks
where usrTsk.TaskId == taskId
select usrTsk).ToList<UsersTask>();
return result;
}
}
public static WorkingCard GetWorkingCardById(int id)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var wCard = (from card in context.WorkingCard
where card.Id == id
select card).FirstOrDefault<WorkingCard>();
return wCard;
}
}
public static List<UsersTask> GetAllUserTasks(string userName)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
User usr = UserUtility.GetUserByName(userName);
var UserTasks = (from usrTasks in context.UsersTasks.Include("Task")
where usrTasks.UserID == usr.UserId && usrTasks.Task.IsDeleted == false
select usrTasks).ToList<UsersTask>();
return UserTasks;
}
}
public static void DeleteUserById(int id)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var user = GetUserById(id);
context.Users.Attach(user);
if (user != null)
{
user.IsDeleted = true;
context.SaveChanges();
}
}
}
public static void DeleteTaskById(int id)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var taskResult = GetTaskById(id);
context.Tasks.Attach(taskResult);
if (taskResult != null)
{
taskResult.IsDeleted = true;
context.SaveChanges();
}
}
}
public static void CreateTask(string t, string d, int s, DateTime? sd = null, DateTime? ed = null)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
Task task = new Task();
task.Title = t;
task.Description = d;
task.StatusId = s;
task.StartDate = sd;
task.EndDate = ed;
context.Tasks.Add(task);
context.SaveChanges();
}
}
public static void AddTaskToUser(int userId, int taskId, DateTime? startDate = null, int? workedHours = null, DateTime? endDate = null)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
UsersTask usrTask = new UsersTask();
usrTask.UserID = userId;
usrTask.TaskId = taskId;
usrTask.StartDate = startDate;
usrTask.WorkedHours = workedHours;
usrTask.EndDate = endDate;
context.UsersTasks.Add(usrTask);
context.SaveChanges();
}
}
public static void UpdateUsersTaskDate(int taskId, DateTime date)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
List<UsersTask> tasksToUpdate = (from usrTask in context.UsersTasks
where usrTask.TaskId == taskId
select usrTask).ToList<UsersTask>();
for (int i = 0; i < tasksToUpdate.Count(); i++)
{
tasksToUpdate[i].StartDate = date;
}
context.SaveChanges();
}
}
public static void UpdateTask(int id, string t, string d, int si, DateTime? sd = null)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var taskResult = GetTaskById(id);
context.Tasks.Attach(taskResult);
if (taskResult != null)
{
taskResult.Title = t;
taskResult.Description = d;
taskResult.StatusId = si;
taskResult.StartDate = sd;
context.SaveChanges();
}
}
}
public static List<Task> GetAllUserTasks(string id)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
User user = GetUserByName(id);
context.Users.Attach(user);
if (user != null)
{
var tasks = user.UsersTasks.Select(u => u.Task);
return tasks.ToList();
}
else
throw new Exception("Error!");
}
}
public static void AddCardToDb(int usrId, int taskId, DateTime? startDate, TimeSpan hoursMin, string description, bool isFilled)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
WorkingCard wCard = new WorkingCard();
wCard.UserId = usrId;
wCard.TaskId = taskId;
wCard.StartDate = DateTime.Parse(startDate.Value.ToString());
wCard.WorkingHours = hoursMin;
wCard.Description = description;
wCard.IsFilled = isFilled;
context.WorkingCard.Add(wCard);
context.SaveChanges();
}
}
public static List<User> GetAllUsersNotInTask(int taskId)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
string query = @"select *
from [TimeTrackerDb].[dbo].[Users] as usr
where usr.IsDeleted = 0 and
usr.UserId not in (select usrTask.UserID
from [TimeTrackerDb].[dbo].[UsersTasks] as usrTask
where usrTask.TaskId = {0}
)";
object[] parameters = { taskId };
var result = (context as IObjectContextAdapter).ObjectContext.ExecuteStoreQuery<User>(query, parameters);
return result.ToList<User>();
}
}
public static void UpdateWoringCard(WorkingCard wCard)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
WorkingCard result = (from card in context.WorkingCard
where card.Id == wCard.Id
select card).FirstOrDefault<WorkingCard>();
result.StartDate = wCard.StartDate;
result.WorkingHours = wCard.WorkingHours;
result.Description = wCard.Description;
context.SaveChanges();
}
}
public static void UpdateUserWorkingHoursOnTask(int usrId, int tskId, double hours)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
UsersTask usrTask = new UsersTask();
usrTask = (from usrTsk in context.UsersTasks
where usrTsk.TaskId == tskId && usrTsk.UserID == usrId
select usrTsk).FirstOrDefault<UsersTask>();
context.UsersTasks.Attach(usrTask);
if (!usrTask.WorkedHours.HasValue)
{
usrTask.WorkedHours = 0;
}
if (hours > 0)
{
usrTask.WorkedHours += hours;
}
context.SaveChanges();
}
}
public static User GetUserByName(string userName)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var userRes = (from usr in context.Users
where usr.UserName == userName
select usr).FirstOrDefault<User>();
return userRes;
}
}
public static void UpdateUser(int id, string un, string fn, string ln, string p, string e)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var user = GetUserById(id);
context.Users.Attach(user);
if (user != null)
{
user.UserName = un;
user.FirstName = fn;
user.LastName = ln;
user.Position = p;
user.Email = e;
context.SaveChanges();
}
}
}
public static bool IsUserActive(string userName)
{
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
var user = (from usr in context.Users
where usr.UserName == userName
select usr).FirstOrDefault<User>();
if (user.IsDeleted == true)
return false;
}
return true;
}
public static void UpdateWorkingHoursOnTask(int tskId, int hours)
{
Task tsk = TaskUtility.GetTaskById(tskId);
using (TimeTrackerDbEntities context = new TimeTrackerDbEntities())
{
context.Tasks.Attach(tsk);
if (hours > 0)
{
tsk.WorkedHoursCurrent += hours;
}
context.SaveChanges();
}
}
