public static double getMonthlyMeetingTime(User currentUser, string date)//, int personal, int professional, int other)
{
double timeSpent = 0;
SqlConnection connection = DBConnection.GetConnection();
string selectStatement =
"SELECT sum(a.seconds) " +
"FROM activities a " +
"left join tasks t " +
"on t.taskID = a.taskID " +
"WHERE " +
"t.task_owner = @user and " +
"t.isMeeting = 1 and " +
"cast(t.currentDate as date) >= dateadd(month, -1, @date) and " +
"a.startTime >= dateadd(month, -1, @date)";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@user", currentUser.userID);
selectCommand.Parameters.AddWithValue("@date", date);
try
{
connection.Open();
if (selectCommand.ExecuteScalar() != DBNull.Value)
{
timeSpent = (Convert.ToDouble(selectCommand.ExecuteScalar()) / 60.0) / 60.0;
}
else
{
timeSpent = 0.0;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(timeSpent);
}
//Returns the password for the passed user
public static String getPassword(string username)
{
string password = "";
SqlConnection connection = DBConnection.GetConnection();
string selectStatement = "SELECT enc_password " +
"FROM users " +
"WHERE username = @Username";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@Username", username);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow);
while (reader.Read())
{
password = reader["enc_password"].ToString();
}
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(password);
}
public static DataSet timeSpentReport(int id, DateTime start, DateTime end)
{
SqlConnection connection = DBConnection.GetConnection();
string selectStatement =
"SELECT t.title, t.createdDate, SUM(a.seconds) AS Expr1, t.completed " +
"FROM tasks t " +
"JOIN activities a ON t.taskID = a.taskID " +
"WHERE t.task_owner = @id AND a.startTime >= @start AND a.startTime <= @end " +
"GROUP BY t.title, t.createdDate, t.completed";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@start", start);
selectCommand.Parameters.AddWithValue("@end", end);
selectCommand.Parameters.AddWithValue("@id", id);
// Create a new data adapter based on the specified query.
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectStatement, connection);
// Populate a new data table
DataSet dataSet = new DataSet();
try
{
connection.Open();
SqlDataReader sdr = selectCommand.ExecuteReader();
dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataSet.Tables.Add("tasks");
dataSet.Tables.Add("activities");
//Load DataReader into the DataTable.
dataSet.Tables[0].Load(sdr);
dataSet.Tables[0].Columns.Add(new DataColumn("Task_Type", typeof(string)));
dataSet.Tables[0].Columns.Add(new DataColumn("TaskPriority", typeof(string)));
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(dataSet);
}
public static int updateTaskIncomplete(int taskID)
{
SqlConnection connection = DBConnection.GetConnection();
string updateStatement = "UPDATE tasks " +
"set completed = 0, " +
"currentDate = GETDATE() " +
"WHERE taskID = @taskID";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@taskID", taskID);
SqlDataReader reader = null;
try
{
connection.Open();
// Returns the number of rows affected by this update
return(updateCommand.ExecuteNonQuery());
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
}
public static int updateSubtaskIncomplete(int subtaskID)
{
SqlConnection connection = DBConnection.GetConnection();
string updateStatement = "UPDATE subtask " +
"set st_CompleteDate = @null " +
"WHERE subtaskID = @subtaskID";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@subtaskID", subtaskID);
updateCommand.Parameters.AddWithValue("@null", DBNull.Value);
SqlDataReader reader = null;
try
{
connection.Open();
// Returns the number of rows affected by this update
return(updateCommand.ExecuteNonQuery());
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
}
//Creates a new user in the database
public static void createUser(string username, string fName, string lName, string email, string phone, string password, string hint)
{
SqlConnection connection = DBConnection.GetConnection();
string insertStatement =
"INSERT users " +
"(username, fName, lName, email, phone, enc_password, hint) " +
"VALUES " +
"(@username, @fName, @lName, @email, @phone, @password, @hint)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@username", username);
insertCommand.Parameters.AddWithValue("@fName", fName);
insertCommand.Parameters.AddWithValue("@lName", lName);
insertCommand.Parameters.AddWithValue("@email", email);
insertCommand.Parameters.AddWithValue("@phone", phone);
insertCommand.Parameters.AddWithValue("@password", password);
insertCommand.Parameters.AddWithValue("@hint", hint);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public static int updateTaskAlreadyInActivities(int taskID, int timerSecs)
{
SqlConnection connection = DBConnection.GetConnection();
string updateStatement = "UPDATE activities set " +
"seconds = seconds + @seconds " +
"WHERE taskID = @taskID";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@seconds", timerSecs);
updateCommand.Parameters.AddWithValue("@taskID", taskID);
SqlDataReader reader = null;
try
{
connection.Open();
return(updateCommand.ExecuteNonQuery());
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
}
//Updates the password for the passed user to the passed password
public static void updatePassword(string username, string password)
{
SqlConnection connection = DBConnection.GetConnection();
string updateStatement = "UPDATE users " +
"SET enc_password = @Password " +
"WHERE username = @Username";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@Username", username);
updateCommand.Parameters.AddWithValue("@Password", password);
SqlDataReader reader = null;
try
{
connection.Open();
updateCommand.ExecuteNonQuery();
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
}
//This will return the total time for tasks
public static int getTotalTime(int userID)
{
int time;
SqlConnection connection = DBConnection.GetConnection();
string selectStatement = "SELECT SUM(a.seconds) " +
"FROM tasks t " +
"JOIN activities a ON t.taskID = a.taskID " +
"WHERE t.task_owner = @userID and " +
"startTime > dateadd(day, -30, getdate()) " +
"GROUP BY t.task_owner";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@userID", userID);
try
{
connection.Open();
selectCommand.ExecuteNonQuery();
time = (int)selectCommand.ExecuteScalar();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(time);
}
public static double getTimeSpentOnTask(int taskID)
{
int taskTime;
SqlConnection connection = DBConnection.GetConnection();
string selectStatement = "SELECT sum(a.seconds) " +
"FROM tasks t " +
"JOIN activities a ON t.taskID = a.taskID " +
"WHERE a.taskID = @taskID and " +
"startTime > dateadd(day, -30, getdate()) " +
"group by a.taskID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@taskID", taskID);
try
{
connection.Open();
//selectCommand.ExecuteNonQuery();
taskTime = (int)selectCommand.ExecuteScalar();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(taskTime);
}
/*Delete subtask by subtask id*/
public static void deleteSubTask(int subtaskID)
{
SqlConnection connection = DBConnection.GetConnection();
string deleteStatement = "DELETE FROM subtask " +
"WHERE subtaskID = @subtaskID";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@subtaskID", subtaskID);
SqlDataReader reader = null;
try
{
connection.Open();
deleteCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
}
// Only update the task to the currentDate if it is not complete
public static int updateIncompleteTasksToCurrentDate()
{
SqlConnection connection = DBConnection.GetConnection();
string updateStatement = "UPDATE tasks set " +
"currentDate = GETDATE() " +
"WHERE completed = 0 and " +
"isMeeting = 0";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
SqlDataReader reader = null;
try
{
connection.Open();
return(updateCommand.ExecuteNonQuery());
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
}
public static List <Subtask> getSubtasksForTask(User currentUser, int taskID)
{
List <Subtask> subtasks = new List <Subtask>();
SqlConnection connection = DBConnection.GetConnection();
string selectStatement =
"SELECT * FROM subtask " +
"WHERE " +
"taskID = @task " +
"order by st_Deadline, st_Priority";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@user", currentUser.userID);
selectCommand.Parameters.AddWithValue("@task", taskID);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Subtask subtask = new Subtask();
subtask.taskID = Convert.ToInt32(reader["taskID"]);
subtask.subtaskID = Convert.ToInt32(reader["subtaskID"]);
subtask.st_Description = reader["st_Description"].ToString();
//task.completed = Convert.ToInt32(reader["completed"]);
subtask.st_CreatedDate = (DateTime)reader["st_CreatedDate"];
if (reader["st_CompleteDate"] != DBNull.Value)
{
subtask.st_CompleteDate = (DateTime)reader["st_CompleteDate"];
}
else
{
subtask.st_CompleteDate = DateTime.MaxValue;
}
if (reader["st_Deadline"] != DBNull.Value)
{
subtask.st_Deadline = (DateTime)reader["st_Deadline"];
}
else
{
subtask.st_Deadline = DateTime.MaxValue;
}
if (reader["st_Priority"] != DBNull.Value)
{
subtask.st_Priority = Convert.ToInt32(reader["st_Priority"]);
if (subtask.st_Priority == 1)
{
subtask.Priority = "High";
}
else if (subtask.st_Priority == 2)
{
subtask.Priority = "Medium";
}
else
{
subtask.Priority = "Low";
}
}
else
{
subtask.st_Priority = -1;
}
subtasks.Add(subtask);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(subtasks);
}
/*Delete task by task id*/
public static void deleteTask(int taskID)
{
//delete list subtasks of this task id
SqlConnection connection = DBConnection.GetConnection();
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction();
string selectStatement = "SELECT * " +
"FROM subtask WHERE taskID = @taskID ";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection, sqlTransaction);
selectCommand.Parameters.AddWithValue("@taskID", taskID);
SqlDataReader reader = null;
try
{
List <int> listSubtasks = new List <int>();
reader = selectCommand.ExecuteReader();
while (reader.Read())
{
int subtaskID = Convert.ToInt32(reader["subtaskID"]);
listSubtasks.Add(subtaskID);
}
reader.Close();
//delete list of subtask of this task id
for (int i = 0; i < listSubtasks.Count; i++)
{
//delete this subtask
string deleteSubtaskStatement = "DELETE FROM subtask " +
"WHERE subtaskID = @subtaskID";
SqlCommand deleteSubtaskCommand = new SqlCommand(deleteSubtaskStatement, connection, sqlTransaction);
deleteSubtaskCommand.Parameters.AddWithValue("@subtaskID", listSubtasks[i]);
deleteSubtaskCommand.ExecuteNonQuery();
}
//final, delete this task
string deleteStatement = "DELETE FROM tasks " +
"WHERE taskID = @taskID";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection, sqlTransaction);
deleteCommand.Parameters.AddWithValue("@taskID", taskID);
deleteCommand.ExecuteNonQuery();
sqlTransaction.Commit();
}
catch (SqlException ex)
{
sqlTransaction.Rollback();
throw ex;
}
catch (Exception ex)
{
sqlTransaction.Rollback();
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
}
/* Add this method to get the list of tasks have created date between fromDate and toDate with completed or not*/
public static DataSet GetListTaskByCreatedDate(int id, DateTime fromDate, DateTime toDate, bool isCompleted)
{
SqlConnection connection = DBConnection.GetConnection();
string selectStatement =
"SELECT * FROM tasks where task_owner = @id AND (createdDate between @fromDate and @toDate)";
if (isCompleted)
{
selectStatement = selectStatement + " and completed = 1";
}
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@fromDate", fromDate);
selectCommand.Parameters.AddWithValue("@toDate", toDate);
selectCommand.Parameters.AddWithValue("@id", id);
// Create a new data adapter based on the specified query.
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectStatement, connection);
// Populate a new data table
DataSet dataSet = new DataSet();
try
{
connection.Open();
SqlDataReader sdr = selectCommand.ExecuteReader();
dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataSet.Tables.Add("tasks");
//Load DataReader into the DataTable.
dataSet.Tables[0].Load(sdr);
dataSet.Tables[0].Columns.Add(new DataColumn("Task_Type", typeof(string)));
dataSet.Tables[0].Columns.Add(new DataColumn("TaskPriority", typeof(string)));
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
if (dataSet.Tables[0].Rows[i]["taskType"].ToString() == "1")
{
dataSet.Tables[0].Rows[i]["Task_Type"] = "Professional";
}
else if (dataSet.Tables[0].Rows[i]["taskType"].ToString() == "2")
{
dataSet.Tables[0].Rows[i]["Task_Type"] = "Personal";
}
else if (dataSet.Tables[0].Rows[i]["taskType"].ToString() == "3")
{
dataSet.Tables[0].Rows[i]["Task_Type"] = "Other";
}
else
{
dataSet.Tables[0].Rows[i]["Task_Type"] = "Meeting";
}
if (dataSet.Tables[0].Rows[i]["task_priority"].ToString() == "1")
{
dataSet.Tables[0].Rows[i]["TaskPriority"] = "High";
}
else if (dataSet.Tables[0].Rows[i]["task_priority"].ToString() == "2")
{
dataSet.Tables[0].Rows[i]["TaskPriority"] = "Medium";
}
else
{
dataSet.Tables[0].Rows[i]["TaskPriority"] = "Low";
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(dataSet);
}
/*Returns specified task based on taskID */
public static Models.Task getATask(int taskID)
{
Models.Task task = new Models.Task();
SqlConnection connection = DBConnection.GetConnection();
string selectStatement = "SELECT * FROM tasks WHERE taskID = @taskID";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@taskID", taskID);
SqlDataReader reader = null;
try
{
connection.Open();
reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow);
if (reader.Read())
{
task.task_owner = Convert.ToInt32(reader["task_owner"]);
task.taskID = Convert.ToInt32(reader["taskID"]);
task.taskType = Convert.ToInt32(reader["taskType"]);
if (reader["task_priority"] == DBNull.Value)
{
task.task_priority = -1;
}
else
{
task.task_priority = Convert.ToInt32(reader["task_priority"]);
}
//task.completed = Convert.ToInt32(reader["completed"]);
task.completed = Convert.ToBoolean(reader["completed"]);
task.title = reader["title"].ToString();
task.createdDate = (DateTime)reader["createdDate"];
task.currentDate = (DateTime)reader["currentDate"];
if (reader["deadline"] == DBNull.Value)
{
task.deadline = DateTime.MaxValue;
}
else
{
task.deadline = (DateTime)reader["deadline"];
}
task.isMeeting = Convert.ToInt16(reader["isMeeting"]);
}
else
{
task = null;
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(task);
}
/*
* This method use for add subtask into database.
* It return the subtask id of new subtask.
*/
public static int AddSubTask(YouCompleteMe.Models.Subtask subtask)
{
SqlConnection connection = DBConnection.GetConnection();
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction();
string insertStatement = "INSERT subtask " +
"(taskID, st_Description, st_CreatedDate, st_CompleteDate, st_Deadline, st_Priority) " +
"VALUES (@taskID, @st_Description, @st_CreatedDate, @st_CompleteDate, @st_Deadline, @st_Priority)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection, sqlTransaction);
insertCommand.Parameters.AddWithValue("@taskID", subtask.taskID);
insertCommand.Parameters.AddWithValue("@st_Description", subtask.st_Description);
insertCommand.Parameters.AddWithValue("@st_CreatedDate", subtask.st_CreatedDate);
if (subtask.st_CompleteDate == DateTime.MaxValue)
{
insertCommand.Parameters.AddWithValue("@st_CompleteDate", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@st_CompleteDate", subtask.st_CompleteDate);
}
if (subtask.st_Deadline == DateTime.MaxValue)
{
insertCommand.Parameters.AddWithValue("@st_Deadline", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@st_Deadline", subtask.st_Deadline);
}
if (subtask.st_Priority == -1)
{
insertCommand.Parameters.AddWithValue("@st_Priority", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@st_Priority", subtask.st_Priority);
}
try
{
insertCommand.ExecuteNonQuery();
string selectStatement = "SELECT IDENT_CURRENT('subtask') FROM subtask";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection, sqlTransaction);
int subtaskID = Convert.ToInt32(selectCommand.ExecuteScalar());
//add notes to notes table
if (subtask.note != "")
{
string insertNoteStatement = "INSERT note " +
"(taskID, subtaskID, note_message) " +
"VALUES (@taskID, @subtaskID, @note_message)";
SqlCommand insertNoteCommand = new SqlCommand(insertNoteStatement, connection, sqlTransaction);
insertNoteCommand.Parameters.AddWithValue("@taskID", subtask.taskID);
insertNoteCommand.Parameters.AddWithValue("@note_message", subtask.note);
insertNoteCommand.Parameters.AddWithValue("@subtaskID", subtaskID);
insertNoteCommand.ExecuteNonQuery();
}
sqlTransaction.Commit();
return(subtaskID);
}
catch (SqlException ex)
{
sqlTransaction.Rollback();
throw ex;
}
finally
{
connection.Close();
}
}
public static List <Models.Task> getMonthlyUsersTasks(User currentUser, string date)//, int personal, int professional, int other)
{
List <Models.Task> tasks = new List <Models.Task>();
SqlConnection connection = DBConnection.GetConnection();
string selectStatement =
"SELECT distinct task_owner, t.taskID, t.title FROM tasks t " +
"left join activities a " +
"on t.taskID = a.taskID " +
"WHERE " +
"task_owner = @user and " +
"isMeeting = 0 and " +
"cast(currentDate as date) >= dateadd(month, -1, @date) and " +
"startTime >= dateadd(day, -30, getdate())";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@user", currentUser.userID);
selectCommand.Parameters.AddWithValue("@date", date);
//selectCommand.Parameters.AddWithValue("@type", "("+personal + ", " + professional + ", " + other+")");
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Models.Task task = new Models.Task();
task.task_owner = Convert.ToInt32(reader["task_owner"]);
task.taskID = Convert.ToInt32(reader["taskID"]);
//task.taskType = Convert.ToInt32(reader["taskType"]);
//if (reader["task_priority"] == DBNull.Value)
// task.task_priority = -1;
//else
// task.task_priority = Convert.ToInt32(reader["task_priority"]);
////task.completed = Convert.ToInt32(reader["completed"]);
//task.completed = Convert.ToBoolean(reader["completed"]);
task.title = reader["title"].ToString();
//task.createdDate = (DateTime)reader["createdDate"];
//task.currentDate = (DateTime)reader["currentDate"];
//if (reader["deadline"] == DBNull.Value)
// task.deadline = DateTime.MaxValue;
//else
// task.deadline = (DateTime)reader["deadline"];
tasks.Add(task);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(tasks);
}
/*
* This method use for add task into database.
* It return the task id of new task.
*/
public static int AddTask(YouCompleteMe.Models.Task task)
{
SqlConnection connection = DBConnection.GetConnection();
connection.Open();
//SqlTransaction sqlTransaction = connection.BeginTransaction();
string insertStatement =
"INSERT tasks " +
"(task_owner, taskType, title, createdDate, currentDate, deadline, task_priority, completed, isMeeting) " +
"VALUES (@task_owner, @taskType, @title, @createdDate, @currentDate, @deadline, @task_priority, @completed, @meeting)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@task_owner", task.task_owner);
insertCommand.Parameters.AddWithValue("@title", task.title);
insertCommand.Parameters.AddWithValue("@createdDate", task.createdDate);
insertCommand.Parameters.AddWithValue("@currentDate", task.currentDate);
if (task.deadline == DateTime.MaxValue)
{
insertCommand.Parameters.AddWithValue("@deadline", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@deadline", task.deadline);
}
if (task.task_priority == -1)
{
insertCommand.Parameters.AddWithValue("@task_priority", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@task_priority", task.task_priority);
}
insertCommand.Parameters.AddWithValue("@completed", task.completed);
insertCommand.Parameters.AddWithValue("@taskType", task.taskType);
insertCommand.Parameters.AddWithValue("@meeting", task.isMeeting);
try
{
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('tasks') FROM tasks";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);//, sqlTransaction);
int taskID = Convert.ToInt32(selectCommand.ExecuteScalar());
if (task.note != "")
{
string insertNoteStatement = "INSERT note " +
"(taskID, subtaskID, note_message) " +
"VALUES (@taskID, @subtaskID, @note_message)";
SqlCommand insertNoteCommand = new SqlCommand(insertNoteStatement, connection);//, sqlTransaction);
insertNoteCommand.Parameters.AddWithValue("@taskID", taskID);
insertNoteCommand.Parameters.AddWithValue("@note_message", task.note);
insertNoteCommand.Parameters.AddWithValue("@subtaskID", DBNull.Value);
insertNoteCommand.ExecuteNonQuery();
}
//sqlTransaction.Commit();
return(taskID);
}
catch (SqlException ex)
{
//sqlTransaction.Rollback();
throw ex;
}
finally
{
connection.Close();
}
}
public static List <Models.Task> getCurrentDeadlines(User currentUser, string date)
{
List <Models.Task> tasks = new List <Models.Task>();
SqlConnection connection = DBConnection.GetConnection();
string selectStatement =
"SELECT * FROM tasks " +
"WHERE " +
"task_owner = @user and " +
"cast(deadline as date) = @date " +
"order by deadline";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@user", currentUser.userID);
selectCommand.Parameters.AddWithValue("@date", date);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Models.Task task = new Models.Task();
task.task_owner = Convert.ToInt32(reader["task_owner"]);
task.taskID = Convert.ToInt32(reader["taskID"]);
task.taskType = Convert.ToInt32(reader["taskType"]);
if (reader["task_priority"] == DBNull.Value)
{
task.task_priority = -1;
}
else
{
task.task_priority = Convert.ToInt32(reader["task_priority"]);
}
//task.completed = Convert.ToInt32(reader["completed"]);
task.completed = Convert.ToBoolean(reader["completed"]);
task.title = reader["title"].ToString();
task.createdDate = (DateTime)reader["createdDate"];
task.currentDate = (DateTime)reader["currentDate"];
if (reader["deadline"] == DBNull.Value)
{
task.deadline = DateTime.MaxValue;
}
else
{
task.deadline = (DateTime)reader["deadline"];
}
tasks.Add(task);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(tasks);
}
public static List <Note> getNotes(int taskID, int subtaskID)
{
List <Note> notes = new List <Note>();
string selectStatement = "";
SqlConnection connection = DBConnection.GetConnection();
if (subtaskID == -1)
{
selectStatement =
"SELECT * FROM note " +
"WHERE " +
"taskID = @taskID and " +
"subtaskID is null";
}
else
{
selectStatement =
"SELECT * FROM note " +
"WHERE " +
"taskID = @taskID and " +
"subtaskID = @stID";
}
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@taskID", taskID);
selectCommand.Parameters.AddWithValue("@stID", subtaskID);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Note note = new Note();
note.taskID = Convert.ToInt32(reader["taskID"]);
if (reader["subtaskID"] == DBNull.Value)
{
note.subtaskID = 0;
}
else
{
note.subtaskID = Convert.ToInt32(reader["subtaskID"]);
}
note.noteID = Convert.ToInt32(reader["noteID"]);
note.note_message = reader["note_message"].ToString();
notes.Add(note);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(notes);
}
