public ActionResult Filter(TaskSearchCriteria criteria)
{
//save the criteria to a cookie, read it in the index action
var cookie = new HttpCookie(CookieKeys.EVERYTHING_FILTER);
cookie.Expires = DateTime.Now.AddYears(1);
cookie.Value = Newtonsoft.Json.JsonConvert.SerializeObject(criteria);
Response.Cookies.Add(cookie);
return View("Index", _service.GetAllTasks(criteria));
}
public AllTasksViewModel GetAllTasks(TaskSearchCriteria criteria)
{
var result = new AllTasksViewModel();
criteria.RootTasksOnly = true;
result.Tasks = _tasksRepo.GetTasks(criteria);
var projects = _genericRepo.FindAll<Project>();
result.Projects = projects.Select(x => new SelectListItem { Text = x.Title, Value = x.ProjectID.ToString() });
var members = _genericRepo.FindAll<Member>().OrderBy(x => x.LastName);
var memberList = new List<SelectListItem>();
memberList.Add(new SelectListItem { Value = "-1", Text = "Unassigned" });
memberList.AddRange(members.Select(x => new SelectListItem { Text = String.Format("{0} {1}", x.FirstName, x.LastName), Value = x.MemberID.ToString() }));
result.Members = memberList;
result.Criteria = criteria;
return result;
}
public ActionResult Index()
{
var criteria = new TaskSearchCriteria { MemberID = CurrentMember.MemberID };
return View(_service.GetMemberTasks(criteria));
}
public IEnumerable<TaskListItemViewModel> GetTasks(TaskSearchCriteria criteria)
{
var sql = @"
select
p.Title as ProjectTitle,
t.Reference,
t.Title,
t.TaskID,
tp.Label as TaskPriority,
tt.Label as TaskType,
t.EstimatedHours,
t.ActualHours,
t.DueDate,
t.IsComplete,
t.RootTaskID,
t.ParentTaskID,
tp.Label as PriorityDescription,
t.MemberNames
from Task t
inner join Project p on t.projectid = p.projectid
left join tasktype tt on t.tasktypeid = tt.tasktypeid
left join taskpriority tp on t.taskpriorityid = tp.taskpriorityid
left join taskmember tm on t.taskid = tm.taskid
left join member m on tm.memberid = m.memberid
";
var whereClause = String.Empty;
if (criteria.MemberID > 0)
{
if (!String.IsNullOrEmpty(whereClause)) whereClause += " and ";
whereClause += " tm.memberid = @memberid ";
}
if (criteria.MemberID < 0)
{
if (!String.IsNullOrEmpty(whereClause)) whereClause += " and ";
whereClause += " tm.memberid is null ";
}
if (criteria.ProjectID > 0)
{
if (!String.IsNullOrEmpty(whereClause)) whereClause += " and ";
whereClause += " t.projectid = @projectid ";
}
if (!criteria.ShowCompletedTasks)
{
if (!String.IsNullOrEmpty(whereClause)) whereClause += " and ";
whereClause += " t.iscomplete = 0 ";
}
if (criteria.RootTasksOnly)
{
if (!String.IsNullOrEmpty(whereClause)) whereClause += " and ";
whereClause += " t.TaskID = t.RootTaskID ";
}
if (!String.IsNullOrEmpty(whereClause))
{
sql += " where " + whereClause;
}
sql += @"
group by
p.Title,
t.Reference,
t.Title,
t.TaskID,
tp.Label,
tt.Label,
t.EstimatedHours,
t.ActualHours,
t.DueDate,
t.IsComplete,
t.RootTaskID,
t.ParentTaskID,
tp.Label,
t.MemberNames
";
sql += " order by t.RootTaskID, t.TaskID ";
using (var cmd = new SqlCommand(sql))
{
if (criteria.MemberID > 0)
{
cmd.Parameters.Add("@memberid", SqlDbType.Int).Value = criteria.MemberID;
}
if (criteria.ProjectID > 0)
{
cmd.Parameters.Add("@projectid", SqlDbType.Int).Value = criteria.ProjectID;
}
return _helper.ExecuteMultiple<TaskListItemViewModel>(cmd);
}
}
public ListTasksViewModel GetMemberTasks(TaskSearchCriteria criteria)
{
var result = new ListTasksViewModel();
result.Criteria = criteria;
result.Projects = GetMemberProjects(criteria.MemberID.Value);
result.Tasks = _taskRepo.GetTasks(criteria);
return result;
}