public static UsersDTO Login(string email, string password)
{
ConnectionDatabase.getConnection();
string query = "select * from users where email = @email and password = @password";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@password", password);
IDataReader reader = cmd.ExecuteReader();
UsersDTO user = null;
while (reader.Read())
{
int userId = Convert.ToInt32(reader["id"]);
string userName = reader["name"].ToString();
string userEmail = reader["email"].ToString();
string userRole = reader["role"].ToString();
user = new UsersDTO
{
ID = userId,
Name = userName,
Email = userEmail,
Role = userRole
};
}
ConnectionDatabase.closeConnection();
return(user);
}
public static int DeleteTaskById(string taskId)
{
ConnectionDatabase.getConnection();
// remove comment
string sql = @"delete from comments
where taskId = @taskId";
SqlCommand cmd = new SqlCommand(sql, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
cmd.ExecuteNonQuery();
// remove usersTasks
sql = @"delete from usersTasks
where taskId = @taskId";
cmd = new SqlCommand(sql, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
cmd.ExecuteNonQuery();
// remove tasks
sql = @"delete from tasks
where id = @taskId";
cmd = new SqlCommand(sql, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
cmd.ExecuteNonQuery();
ConnectionDatabase.closeConnection();
return(1);
}
public static TasksDTO getTaskByTaskId(int taskId)
{
ConnectionDatabase.getConnection();
string query = @"select * from tasks where id = @taskId";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
IDataReader reader = cmd.ExecuteReader();
TasksDTO task = null;
while (reader.Read())
{
int id = Convert.ToInt32(reader["id"].ToString());
string taskTitle = reader["title"].ToString();
string taskStartDate = reader["startDate"].ToString();
string taskEndDate = reader["endDate"].ToString();
string taskStatus = reader["status"].ToString();
bool taskPrivate = Convert.ToBoolean(reader["private"]);
string urlFile = reader["urlFile"].ToString();
task = new TasksDTO(id, taskTitle, taskStartDate, taskEndDate, taskStatus, taskPrivate, urlFile);
}
ConnectionDatabase.closeConnection();
return(task);
}
public static int updateTask(TasksDTO task, List <int> arrPartnerId)
{
int ownerId = getOwnerByTaskId(task.ID);
ConnectionDatabase.getConnection();
// update task
string query = @"update tasks
set title=@title,startDate=@startDate,endDate=@endDate,status=@status,private=@private
where id=@taskId";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@title", task.Title);
cmd.Parameters.AddWithValue("@startDate", Convert.ToDateTime(task.startDate));
cmd.Parameters.AddWithValue("@endDate", Convert.ToDateTime(task.endDate));
cmd.Parameters.AddWithValue("@status", task.Status);
cmd.Parameters.AddWithValue("@private", task.Private);
cmd.Parameters.AddWithValue("@taskId", task.ID);
cmd.ExecuteNonQuery();
// remove all usersTasks by taskId
string sql = @"delete from usersTasks
where taskId = @taskId";
cmd = new SqlCommand(sql, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", task.ID);
cmd.ExecuteNonQuery();
// add the owner into task
query = @"insert into usersTasks(userId, taskId, isOwner)
values (@userId, @taskId, 1)";
cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@userId", ownerId);
cmd.Parameters.AddWithValue("@taskId", task.ID);
cmd.ExecuteNonQuery();
// remove owner checked in arrPartnerId cause owner is inserted above
arrPartnerId.Remove(ownerId);
// add again by list partnerId
// add partner into task
for (int i = 0; i < arrPartnerId.Count; i++)
{
query = @"insert into usersTasks(userId, taskId, isOwner)
values (@userId, @taskId, 0)";
cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@userId", arrPartnerId[i]);
cmd.Parameters.AddWithValue("@taskId", task.ID);
cmd.ExecuteNonQuery();
}
ConnectionDatabase.closeConnection();
return(1);
}
public static int CreateTask(TasksDTO task, List <int> arrUser, int ownerId)
{
ConnectionDatabase.getConnection();
string query;
if (task.urlFile == null)
{
query = @"insert into tasks(title, startDate, endDate, status, private)
values(@title, @startDate, @endDate, @status, @private);
select MAX(id) FROM tasks";
}
else
{
query = @"insert into tasks(title, startDate, endDate, status, private, urlFile)
values(@title, @startDate, @endDate, @status, @private, @urlFile);
select MAX(id) FROM tasks";
}
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@title", task.Title);
cmd.Parameters.AddWithValue("@startDate", Convert.ToDateTime(task.startDate));
cmd.Parameters.AddWithValue("@endDate", Convert.ToDateTime(task.endDate));
cmd.Parameters.AddWithValue("@status", task.Status);
cmd.Parameters.AddWithValue("@private", task.Private);
if (task.urlFile != null)
{
cmd.Parameters.AddWithValue("@urlFile", task.urlFile);
}
Int32 lastestId = (Int32)cmd.ExecuteScalar();
// add partner into task
for (int i = 0; i < arrUser.Count; i++)
{
query = @"insert into usersTasks(userId, taskId, isOwner)
values (@userId, @taskId, 0)";
cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@userId", arrUser[i]);
cmd.Parameters.AddWithValue("@taskId", lastestId);
cmd.ExecuteNonQuery();
}
// add the owner into task
query = @"insert into usersTasks(userId, taskId, isOwner)
values (@userId, @taskId, 1)";
cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@userId", ownerId);
cmd.Parameters.AddWithValue("@taskId", lastestId);
cmd.ExecuteNonQuery();
ConnectionDatabase.closeConnection();
return(1);
}
public static void updateUser(UpdateUserDTO user)
{
ConnectionDatabase.getConnection();
string query = @"update users set
name = @name
where id = @id";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@name", user.Name);
cmd.Parameters.AddWithValue("@id", user.Id);
cmd.ExecuteNonQuery();
ConnectionDatabase.closeConnection();
}
public static DataTable getListUsers()
{
ConnectionDatabase.getConnection();
string query = "select * from users";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
ConnectionDatabase.closeConnection();
return(ds.Tables[0]);
}
public static ResponseRegisterDTO Register(UsersDTO user)
{
ConnectionDatabase.getConnection();
// check duplicate email
string queryCheckDup = "select count(1) from users where email = @email";
SqlCommand cmdCheckDup = new SqlCommand(queryCheckDup, ConnectionDatabase.conn);
cmdCheckDup.Parameters.AddWithValue("@email", user.Email);
int isDup = Convert.ToInt32(cmdCheckDup.ExecuteScalar());
if (isDup > 0)
{
return(new ResponseRegisterDTO
{
Error = "This Email has been used.",
Success = false
});
}
string query = "insert into users (name, email, password, role)" +
"values (@name, @email, @password, @role)";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@name", user.Name);
cmd.Parameters.AddWithValue("@email", user.Email);
cmd.Parameters.AddWithValue("@password", user.Password);
cmd.Parameters.AddWithValue("@role", user.Role);
int rowEffected = cmd.ExecuteNonQuery();
ConnectionDatabase.closeConnection();
if (rowEffected == 0)
{
return(new ResponseRegisterDTO
{
Error = "Fail",
Success = false
});
}
else
{
return(new ResponseRegisterDTO
{
Success = true
});
}
}
public static DataTable GetAllTasks()
{
ConnectionDatabase.getConnection();
string query = "select * from tasks";
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = ConnectionDatabase.conn.CreateCommand();
cmd.CommandText = query;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
ConnectionDatabase.closeConnection();
return(ds.Tables[0]);
}
public static void updateStatusOfTask(int taskId, string status)
{
ConnectionDatabase.getConnection();
string query = @"update tasks
set status=@status
where id=@taskId";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
cmd.Parameters.AddWithValue("@status", status);
cmd.ExecuteNonQuery();
ConnectionDatabase.closeConnection();
}
public static void expiringTask()
{
ConnectionDatabase.getConnection();
string query = @"update tasks
set status = 'expired'
where id in (select id
from tasks
where endDate < Convert(date, getdate())
and status = 'inprogress')" ;
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.ExecuteNonQuery();
ConnectionDatabase.closeConnection();
}
public static int createComment(int userId, int taskId, string content, bool isRemind)
{
ConnectionDatabase.getConnection();
string query = @"insert into comments(userId, taskId, content, isRemind)
values (@userId, @taskId, @content, @isRemind)";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.AddWithValue("@taskId", taskId);
cmd.Parameters.AddWithValue("@content", content);
cmd.Parameters.AddWithValue("@isRemind", isRemind);
cmd.ExecuteNonQuery();
ConnectionDatabase.closeConnection();
return(1);
}
public static bool deleteUser(int id)
{
ConnectionDatabase.getConnection();
string query = @"delete from users where id = @id";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@id", id);
try
{
cmd.ExecuteNonQuery();
} catch (Exception ex)
{
return(false);
}
ConnectionDatabase.closeConnection();
return(true);
}
public static DataTable getListUsersExceptCurrentUser(int currentUserId)
{
ConnectionDatabase.getConnection();
string query = @"select * from users
where id != @currentUserId
and role != 'admin'";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@currentUserId", currentUserId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
ConnectionDatabase.closeConnection();
return(ds.Tables[0]);
}
public static DataTable getListUserOfTask(int taskId)
{
ConnectionDatabase.getConnection();
string query = @"select *
from usersTasks, users
where taskId = @taskId
and users.id = usersTasks.userId";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
ConnectionDatabase.closeConnection();
return(ds.Tables[0]);
}
public static DataTable getCommentByTaskId(int taskId)
{
ConnectionDatabase.getConnection();
string query = @"select *
from comments c, users u
where c.userId = u.id
and taskId = @taskId";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
cmd = ConnectionDatabase.conn.CreateCommand();
cmd.Parameters.AddWithValue("@taskId", taskId);
cmd.CommandText = query;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
ConnectionDatabase.closeConnection();
return(ds.Tables[0]);
}
public static int getOwnerByTaskId(int taskId)
{
ConnectionDatabase.getConnection();
string query = @"select * from usersTasks
where taskId in (select id
from tasks
where id = @taskId)
and isOwner=1";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
IDataReader reader = cmd.ExecuteReader();
int userId = -1;
while (reader.Read())
{
userId = Convert.ToInt32(reader["userId"]);
}
ConnectionDatabase.closeConnection();
return(userId);
}
public static bool isTaskHasRemind(int taskId)
{
ConnectionDatabase.getConnection();
string query = @"select 1
from comments
where taskId = @taskId
and isRemind = 1" ;
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
int rowEffected = Convert.ToInt32(cmd.ExecuteScalar());
ConnectionDatabase.closeConnection();
if (rowEffected > 0)
{
return(true);
}
return(false);
}
public static List <int> getListPartnerIdFollowTaskId(int taskId)
{
ConnectionDatabase.getConnection();
string query = @"select userId
from usersTasks
where taskId = @taskId";
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@taskId", taskId);
SqlDataReader read = cmd.ExecuteReader();
List <int> arrPartnerId = new List <int>();
while (read.HasRows)
{
while (read.Read())
{
arrPartnerId.Add(read.GetInt32(0));
}
read.NextResult();
}
ConnectionDatabase.closeConnection();
return(arrPartnerId);
}
