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); }