public List <Task> GetTasksForOwner(User Owner)// done?
{
List <Task> tasks = new List <Task>();
SqlConnection scon = new SqlConnection(connectionString);
scon.Open();
string query = "SELECT * FROM Tasks.Tasks WHERE OwnerUserID = @OwnerUserID";
SqlCommand cmd = new SqlCommand(query, scon);
cmd.Parameters.AddWithValue("@OwnerUserID", Owner.UserID);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
UserGroup tempUserGroup = new UserGroup((int)reader["UserGroup"], Owner, reader["Name"].ToString(), reader["Description"].ToString());
TaskState tempTaskState = new TaskState((int)reader["TaskStateID"], reader["Name"].ToString(), reader["Description"].ToString(), reader["Color"].ToString());
Task tempTask = new Task(int.Parse(reader["TaskID"].ToString()),
reader["Name"].ToString(),
reader["Description"].ToString(),
tempUserGroup,
Owner,
tempTaskState,
(DateTime)reader["DueDate"],
(DateTime)reader["StartDate"],
(DateTime)reader["CompletionDate"], null);
tasks.Add(tempTask);
}
}
scon.Close();
return(tasks);
}
public Task CreateTask(string Name, string Description, UserGroup UserGroup, User Owner, TaskState TaskState, DateTime DueDate, DateTime StartDate, DateTime?CompletionDate, List <TaskCategory> TaskCategories)
{
SqlConnection scon = new SqlConnection(connectionString);
scon.Open();
string query = "INSERT INTO Tasks.Tasks([Name], [Description], OwnerUserID, UserGroupID, TaskStateID, DueDate, StartDate, CompletionDate) OUTPUT INSERTED.TaskID values (@Name, @Description, @OwnerUserID, @UserGroupID, @TaskStateID, @DueDate, @StartDate, @CompletionDate)";
SqlCommand cmd = new SqlCommand(query, scon);
cmd.Parameters.AddWithValue("@Name", Name);
cmd.Parameters.AddWithValue("@Description", Description);
cmd.Parameters.AddWithValue("@OwnerUserID", Owner.UserID);
cmd.Parameters.AddWithValue("@UserGroupID", UserGroup.UserGroupID);
cmd.Parameters.AddWithValue("@TaskStateID", TaskState.TaskStateID);
cmd.Parameters.AddWithValue("@DueDate", DueDate);
cmd.Parameters.AddWithValue("@StartDate", StartDate);
cmd.Parameters.AddWithValue("@CompletionDate", CompletionDate != null ? (object)CompletionDate : (object)DBNull.Value);
int TaskID = (int)cmd.ExecuteScalar();
foreach (TaskCategory ts in TaskCategories)
{
string tsQuery = "INSERT INTO Tasks.TaskTaskCategories(TaskID, TaskCategoryID) OUTPUT INSERTED.TaskCategoryID values (@TaskID, @TaskCategoryID)";
SqlCommand tsCmd = new SqlCommand(tsQuery, scon);
tsCmd.Parameters.AddWithValue("@TaskID", TaskID);
tsCmd.Parameters.AddWithValue("@TaskCategoryID", ts.TaskCategoryID);
tsCmd.ExecuteNonQuery();
}
scon.Close();
Task tempTask = new Task(TaskID, Name, Description, UserGroup, Owner, TaskState, DueDate, StartDate, CompletionDate, null);
return(tempTask);
}
public EditTaskCategoriesForm(IDatabaseManager database, User user, Task task)
{
this.database = database;
this.user = user;
this.task = task;
InitializeComponent();
}
public List <Task> GetTasksForUser(User User)
{
List <Task> tasks = new List <Task>();
SqlConnection scon = new SqlConnection(connectionString);
scon.Open();
string query = @"
SELECT
U.UserID,
U.[Name] AS UserName,
U.Email AS UserEmail,
T.TaskID,
T.TaskStateID,
T.[Name] AS TaskName,
T.[Description] AS TaskDescription,
TS.[Name] AS TaskStateName,
TS.[Description] AS TaskStateDescription,
T.[Description] AS TaskDescription,
T.DueDate,
T.StartDate,
T.CompletionDate,
T.UserGroupID,
UG.[Name] AS UserGroupName,
UG.[Description] AS UserGroupDescription,
TS.Color
FROM Tasks.Tasks T
INNER JOIN Tasks.TaskStates TS ON T.TaskStateID = TS.TaskStateID
INNER JOIN Users.UserGroupUsers UGU ON T.UserGroupID = UGU.UserGroupID
INNER JOIN Users.UserGroups UG ON UGU.UserGroupID = UG.UserGroupID
INNER JOIN Users.Users U ON T.OwnerUserID = U.UserID
WHERE UGU.UserID = @UserID";
SqlCommand cmd = new SqlCommand(query, scon);
cmd.Parameters.AddWithValue("@UserID", User.UserID);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
List <TaskCategory> ltc = new List <TaskCategory>();
string tcQuery = "SELECT TTC.TaskCategoryID AS TTCTaskCategoryID, TC.TaskCategoryID AS TCTaskCategoryID, TC.OwnerID, TC.[Name] AS TCName, TC.[Description] AS TCDescription, TC.Color" +
" FROM Tasks.TaskTaskCategories TTC" +
" INNER JOIN Tasks.TaskCategories TC ON TC.TaskCategoryID=TTC.TaskCategoryID WHERE TTC.TaskID = @TaskID";
SqlCommand tcCmd = new SqlCommand(tcQuery, scon);
tcCmd.Parameters.AddWithValue("@TaskID", (int)reader["TaskID"]);
using (SqlDataReader tcReader = tcCmd.ExecuteReader())
{
while (tcReader.Read())
{
ltc.Add(new TaskCategory((int)tcReader["TTCTaskCategoryID"], null,
(string)tcReader["TCName"], tcReader["TCDescription"].Equals(DBNull.Value) ? null : (string)tcReader["TCDescription"], (string)tcReader["Color"]));
}
}
UserGroup tempUserGroup = new UserGroup((int)reader["UserGroupID"], User, reader["UserGroupName"].ToString(), reader["UserGroupDescription"].ToString());
TaskState tempTaskState = new TaskState((int)reader["TaskStateID"], reader["TaskStateName"].ToString(), reader["TaskStateDescription"].ToString(), reader["Color"].ToString());
Task tempTask = new Task(int.Parse(reader["TaskID"].ToString()),
reader["TaskName"].ToString(),
reader["TaskDescription"].ToString(),
tempUserGroup,
new User((int)reader["UserID"], (string)reader["UserName"], (string)reader["UserEmail"], null),
tempTaskState,
(DateTime)reader["DueDate"],
(DateTime)reader["StartDate"],
reader["CompletionDate"].Equals(DBNull.Value) ? null : (DateTime?)reader["CompletionDate"], ltc);
tasks.Add(tempTask);
}
}
scon.Close();
return(tasks);
}
