public static List <TasksDTO> GetAllTasksByUserIdComplyWithDayOfWeek(int userId, int day_of_week, string datetime, bool onlyMine)
{
ConnectionDatabase.getConnection();
string query;
if (userId != -1)
{
if (onlyMine)
{
query = @"select * from (
select * from tasks
where id in (select taskId
from usersTasks
where userId = @userId)
) as temp
where DATEPART(WEEKDAY,startDate) = @day_of_week
and startDate >= (SELECT DATEADD(DAY, 2- DATEPART(WEEKDAY, @datetime), CAST(@datetime AS DATE)) [WeekStart])
and startDate <= (SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, @datetime), CAST(@datetime AS DATE)) [WeekEnd])
order by case when status = 'expired' then 1
when status = 'inprogress' then 2
when status = 'done' then 3
else 4
end asc, startDate desc" ;
}
else
{
query = @"select * from (
select * from tasks
where id in (select taskId
from usersTasks
where userId = @userId)
union
select * from tasks
where id in (select taskId
from usersTasks
where userId != @userId)
and private = 0
) as temp
where DATEPART(WEEKDAY,startDate) = @day_of_week
and startDate >= (SELECT DATEADD(DAY, 2- DATEPART(WEEKDAY, @datetime), CAST(@datetime AS DATE)) [WeekStart])
and startDate <= (SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, @datetime), CAST(@datetime AS DATE)) [WeekEnd])
order by case when status = 'expired' then 1
when status = 'inprogress' then 2
when status = 'done' then 3
else 4
end asc, startDate desc" ;
}
}
else
{
query = @"select * from (select *
from tasks) as temp
where DATEPART(WEEKDAY,startDate) = @day_of_week
and startDate >= (SELECT DATEADD(DAY, 2- DATEPART(WEEKDAY, @datetime), CAST(@datetime AS DATE)) [WeekStart])
and startDate <= (SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, @datetime), CAST(@datetime AS DATE)) [WeekEnd])
order by case when status = 'expired' then 1
when status = 'inprogress' then 2
when status = 'done' then 3
else 4
end asc, startDate desc" ;
}
SqlCommand cmd = new SqlCommand(query, ConnectionDatabase.conn);
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.AddWithValue("@day_of_week", day_of_week);
if (datetime == null)
{
cmd.Parameters.AddWithValue("@datetime", DateTime.Now.ToString("MM/dd/yyyy"));
}
else
{
cmd.Parameters.AddWithValue("@datetime", Convert.ToDateTime(datetime).ToString("MM/dd/yyyy"));
}
SqlDataReader reader = cmd.ExecuteReader();
List <TasksDTO> arr = new List <TasksDTO>();
while (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string title = reader.GetString(1);
string startDate = reader.GetValue(2).ToString().Split(Convert.ToChar(" "))[0];
string endDate = reader.GetValue(3).ToString().Split(Convert.ToChar(" "))[0];
string status = reader.GetString(4);
bool Private = Convert.ToBoolean(reader.GetValue(5));
string urlFile = reader["urlFile"].ToString();
arr.Add(new TasksDTO(id,
title,
startDate,
endDate,
status,
Private,
urlFile));
}
reader.NextResult();
}
return(arr);
}
