public float GetByFilterTotal(TaskFilter filter)
{
return(DaoFactory.TimeSpendDao.GetByFilterTotal(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled));
}
public abstract IEnumerable <object[]> BuildReport(TaskFilter filter);
private PagedData<Task> GetTasks(IDataTablesRequest request, TaskFilter taskFilter)
{
var filter = (taskFilter ?? new TaskFilter()).ByString(request.Search.Value);
//.WithPaging(request.Length, request.Start / request.Length);
var query = Query.ForTask(filter).Include(x => x.Project().TaskSteps().SpentTimes());
var pageSize = request.Length;
var pageNumber = request.Start / request.Length;
PagedData<Task> tasks = null;
var sorting = request.Sortings<TaskListColumns>().FirstOrDefault();
if (sorting != null)
{
var column = sorting.Item1;
var direction = sorting.Item2;
if (columnsWithClientSorting.Contains(column))
{
tasks = this.GetSortedOnClientTasks(query, pageSize, pageNumber, column, direction);
}
else
{
tasks = this.GetSortedOnServerTasks(query, pageSize, pageNumber, column, direction);
}
}
else
{
tasks = this.GetTasksWithoutSorting(query, pageSize, pageNumber);
}
return tasks;
}
public int GetByFilterCount(TaskFilter filter)
{
return(_milestoneDao.GetByFilterCount(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled));
}
private SqlQuery CreateQueryFilterCount(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
var minDateTime = DateTime.MinValue;
var maxDateTime = DateTime.MaxValue;
var minDateTimeString = DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss");
query = CreateQueryFilterBase(query, filter);
if (filter.Milestone.HasValue)
{
query.Where("t.milestone_id", filter.Milestone);
}
else if (filter.MyMilestones)
{
if (!filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("t.milestone_id", "m.id") & Exp.EqColumns("m.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existsMilestone));
}
if (filter.ParticipantId.HasValue && filter.ParticipantId.Value.Equals(Guid.Empty))
{
var notExists = new SqlQuery(TasksResponsibleTable + " ptr").Select("ptr.responsible_id").Where(Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.tenant_id", Tenant));
query.Where(!Exp.Exists(notExists));
}
var hasFromDate = !filter.FromDate.Equals(minDateTime) && !filter.FromDate.Equals(maxDateTime);
var hasToDate = !filter.ToDate.Equals(minDateTime) && !filter.ToDate.Equals(maxDateTime);
if (hasFromDate && hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("m.id", "t.milestone_id") & Exp.EqColumns("m.tenant_id", "t.tenant_id") & Exp.Between("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)));
var expExists = Exp.Exists(existsMilestone) & Exp.Eq("t.deadline", minDateTimeString);
query.Where(Exp.Between("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)) | expExists);
}
else if (hasFromDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Ge("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)))
.Where(Exp.Eq("t.deadline", minDateTimeString));
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate))
| Exp.Exists(existsMilestone));
}
else if (hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Le("m.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)))
.Where(!Exp.Eq("m.deadline", minDateTimeString))
.Where("t.deadline", minDateTimeString);
query.Where(!Exp.Eq("t.deadline", minDateTimeString)
& Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate))
| Exp.Exists(existsMilestone));
}
CheckSecurity(query, filter, isAdmin, checkAccess);
return(query);
}
public IEnumerable <Project> GetByFilter(TaskFilter filter)
{
return(projectDao.GetByFilter(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled));
}
public IList <object[]> BuildUsersWorkload(TaskFilter filter)
{
return(reportDao.BuildUsersStatisticsReport(filter));
}
private SqlQuery CreateQueryFilterBase(SqlQuery query, TaskFilter filter)
{
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (ProjectsCommonSettings.Load().HideEntitiesInPausedProjects)
{
query.Where(!Exp.Eq("p.status", ProjectStatus.Paused));
}
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.TagId != 0)
{
if (filter.TagId == -1)
{
query.LeftOuterJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", null);
}
else
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
}
if (filter.Substatus.HasValue)
{
var substatus = filter.Substatus.Value;
if (substatus > -1)
{
query.InnerJoin(StatusTable + " pstat", Exp.EqColumns("pstat.tenant_id", "t.tenant_id") & Exp.Eq("pstat.id", filter.Substatus.Value));
query.Where(Exp.Eq("t.status_id", filter.Substatus.Value) | (Exp.Eq("t.status_id", null) & Exp.EqColumns("t.status", "pstat.statusType") & Exp.Eq("pstat.isDefault", true)));
}
else
{
query.Where("t.status", -substatus).Where("t.status_id", null);
}
}
else if (filter.TaskStatuses.Count != 0)
{
var status = filter.TaskStatuses.First();
query.Where("t.status", status);
}
if (!filter.UserId.Equals(Guid.Empty))
{
query.Where("t.create_by", filter.UserId);
}
if (!filter.DepartmentId.Equals(Guid.Empty) || (filter.ParticipantId.HasValue && !filter.ParticipantId.Value.Equals(Guid.Empty)))
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("t.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("t.id", "ptr1.task_id"));
if (!filter.DepartmentId.Equals(Guid.Empty))
{
existSubtask.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "pst.responsible_id") & Exp.Eq("cug.tenant", Tenant));
existResponsible.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "ptr1.responsible_id") & Exp.Eq("cug.tenant", Tenant));
existSubtask.Where("cug.groupid", filter.DepartmentId);
existResponsible.Where("cug.groupid", filter.DepartmentId);
}
if (filter.ParticipantId.HasValue && !filter.ParticipantId.Value.Equals(Guid.Empty))
{
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.Value.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.Value.ToString()));
}
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
List <int> taskIds;
if (FactoryIndexer <TasksWrapper> .TrySelectIds(s => s.MatchAll(filter.SearchText), out taskIds))
{
IReadOnlyCollection <SubtasksWrapper> subtaskIds;
if (FactoryIndexer <SubtasksWrapper> .TrySelect(s => s.MatchAll(filter.SearchText), out subtaskIds))
{
taskIds.AddRange(subtaskIds.Select(r => r.Task).ToList());
}
query.Where(Exp.In("t.id", taskIds));
}
else
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
}
return(query);
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.MyProjects || filter.MyMilestones)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("t.project_id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (ProjectsCommonSettings.Load().HideEntitiesInPausedProjects)
{
if (!checkAccess && isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id"));
}
query.Where(!Exp.Eq("p.status", ProjectStatus.Paused));
}
}
if (filter.Milestone.HasValue || filter.MyMilestones)
{
query.InnerJoin(MilestonesTable + " pm", Exp.EqColumns("pm.tenant_id", "t.tenant_id") & Exp.EqColumns("pm.project_id", "t.project_id"));
query.Where(Exp.EqColumns("pt.milestone_id", "pm.id"));
if (filter.Milestone.HasValue)
{
query.Where("pm.id", filter.Milestone);
}
else if (filter.MyMilestones)
{
query.Where(Exp.Gt("pm.id", 0));
}
}
if (filter.TagId != 0)
{
if (filter.TagId == -1)
{
query.LeftOuterJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", null);
}
else
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.person_id", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.person_id") & Exp.EqColumns("cug.tenant", "t.tenant_id"));
query.Where("cug.groupid", filter.DepartmentId);
}
var minDate = DateTime.MinValue;
var maxDate = DateTime.MaxValue;
if (!filter.FromDate.Equals(minDate) && !filter.FromDate.Equals(maxDate) &&
!filter.ToDate.Equals(minDate) && !filter.ToDate.Equals(maxDate))
{
query.Where(Exp.Between("t.date", filter.FromDate, filter.ToDate));
}
if (filter.PaymentStatuses.Any())
{
query.Where(Exp.In("payment_status", filter.PaymentStatuses));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.note", filter.SearchText, SqlLike.AnyWhere));
}
if (checkAccess)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("p.id", "t.project_id"));
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("p.id", "t.project_id"));
if (!(filter.MyProjects || filter.MyMilestones))
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = !Exp.Eq("ppp.security", null) & Exp.Eq("ppp.removed", false);
var canReadTasks = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks);
var canReadMilestones = Exp.Eq("pt.milestone_id", 0) | !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Exists(new SqlQuery("projects_tasks_responsible ptr")
.Select("ptr.responsible_id")
.Where(Exp.EqColumns("pt.id", "ptr.task_id") &
Exp.EqColumns("ptr.tenant_id", "pt.tenant_id") &
Exp.Eq("ptr.responsible_id", CurrentUserID)));
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadTasks & canReadMilestones));
}
query.GroupBy("t.id");
return(query);
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin)
{
if (filter.MilestoneStatuses.Count != 0)
{
query.Where("t.status", filter.MilestoneStatuses.First());
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where(Exp.Eq("t.responsible_id", filter.UserId));
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.ParticipantId.HasValue)
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("pt.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("pt.id", "ptr1.task_id"));
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString()));
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
if (!isAdmin)
{
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = Exp.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false);
var canReadMilestones = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Eq("t.responsible_id", CurrentUserID);
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadMilestones));
}
return(query);
}
public TaskFilterOperationResult GetByFilter(TaskFilter filter)
{
if (filter.Offset < 0 || filter.Max < 0)
{
return(null);
}
var isAdmin = ProjectSecurity.IsAdministrator(SecurityContext.CurrentAccount.ID);
var anyOne = ProjectSecurity.IsPrivateDisabled;
var count = DaoFactory.TaskDao.GetByFilterCount(filter, isAdmin, anyOne);
var filterLimit = filter.Max;
var filterOffset = filter.Offset;
if (filterOffset > count.TasksTotal)
{
return(new TaskFilterOperationResult(count)); //there are some records but we cant see them due to offset
}
var taskList = new List <Task>();
if (filter.HasTaskStatuses)
{
taskList = DaoFactory.TaskDao.GetByFilter(filter, isAdmin, anyOne);
}
else if (filterOffset > count.TasksOpen && count.TasksClosed != 0)
{
filter.TaskStatuses.Add(TaskStatus.Closed);
filter.SortBy = "status_changed";
filter.SortOrder = false;
filter.Offset = filterOffset - count.TasksOpen;
taskList = DaoFactory.TaskDao.GetByFilter(filter, isAdmin, anyOne);
}
else
{
//TODO: to one sql query using UNION ALL
if (count.TasksOpen != 0)
{
filter.TaskStatuses.Add(TaskStatus.Open);
taskList = DaoFactory.TaskDao.GetByFilter(filter, isAdmin, anyOne);
}
if (taskList.Count < filterLimit && count.TasksClosed != 0)
{
filter.TaskStatuses.Clear();
filter.TaskStatuses.Add(TaskStatus.Closed);
filter.SortBy = "status_changed";
filter.SortOrder = false;
filter.Offset = 0;
filter.Max = filterLimit - taskList.Count;
taskList.AddRange(DaoFactory.TaskDao.GetByFilter(filter, isAdmin, anyOne));
}
}
filter.Offset = filterOffset;
filter.Max = filterLimit;
filter.TaskStatuses.Clear();
DaoFactory.SubtaskDao.GetSubtasksForTasks(ref taskList);
var taskLinks = DaoFactory.TaskDao.GetLinks(taskList);
Func <Task, int> idSelector = task => task.ID;
Func <Task, IEnumerable <TaskLink>, Task> resultSelector = (task, linksCol) =>
{
task.Links.AddRange(linksCol);
return(task);
};
taskList = taskList.GroupJoin(taskLinks, idSelector, link => link.DependenceTaskId, resultSelector).ToList();
taskList = taskList.GroupJoin(taskLinks, idSelector, link => link.ParentTaskId, resultSelector).ToList();
return(new TaskFilterOperationResult(taskList, count));
}
public IEnumerable <TaskFilterCountOperationResult> GetByFilterCountForStatistic(TaskFilter filter)
{
return(DaoFactory.TaskDao.GetByFilterCountForStatistic(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled));
}
public TaskFilterCountOperationResult GetByFilterCount(TaskFilter filter)
{
return(DaoFactory.TaskDao.GetByFilterCount(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled));
}
private Report(ExtendedReportType reportType, TaskFilter filter)
{
ExtendedReportType = reportType;
Filter = filter;
}
public IList <object[]> BuildUsersActivityReport(TaskFilter filter)
{
return(reportDao.BuildUsersActivityReport(filter));
}
public static TaskFilter FromUri(string uri)
{
var filter = new TaskFilter();
var p = GetParameterFromUri(uri, "ftime");
if (!string.IsNullOrEmpty(p))
{
filter.TimeInterval = (ReportTimeInterval)Enum.Parse(typeof(ReportTimeInterval), p, true);
}
p = GetParameterFromUri(uri, "ffrom");
if (!string.IsNullOrEmpty(p))
{
filter.FromDate = DateTime.ParseExact(p, "yyyyMMdd", null);
}
p = GetParameterFromUri(uri, "fto");
if (!string.IsNullOrEmpty(p))
{
filter.ToDate = DateTime.ParseExact(p, "yyyyMMdd", null);
}
p = GetParameterFromUri(uri, "fu");
if (!string.IsNullOrEmpty(p))
{
filter.UserId = new Guid(p);
}
p = GetParameterFromUri(uri, "fd");
if (!string.IsNullOrEmpty(p))
{
filter.DepartmentId = new Guid(p);
}
p = GetParameterFromUri(uri, "fpid");
if (!string.IsNullOrEmpty(p))
{
filter.ProjectIds = Split(p).Select(v => int.Parse(v)).ToList();
}
p = GetParameterFromUri(uri, "fps");
if (!string.IsNullOrEmpty(p))
{
filter.ProjectStatuses = ToEnumsArray <ProjectStatus>(p);
}
p = GetParameterFromUri(uri, "fpt");
if (!string.IsNullOrEmpty(p))
{
filter.TagId = int.Parse(p);
}
p = GetParameterFromUri(uri, "fms");
if (!string.IsNullOrEmpty(p))
{
filter.MilestoneStatuses = ToEnumsArray <MilestoneStatus>(p);
}
p = GetParameterFromUri(uri, "fts");
if (!string.IsNullOrEmpty(p))
{
filter.TaskStatuses = ToEnumsArray <TaskStatus>(p);
}
p = GetParameterFromUri(uri, "fv");
if (!string.IsNullOrEmpty(p))
{
filter.ViewType = int.Parse(p);
}
p = GetParameterFromUri(uri, "nores");
if (!string.IsNullOrEmpty(p))
{
filter.NoResponsible = bool.Parse(p);
}
p = GetParameterFromUri(uri, "fpays");
if (!string.IsNullOrEmpty(p))
{
filter.PaymentStatuses = ToEnumsArray <PaymentStatus>(p);
}
return(filter);
}
public List <Tuple <Guid, int, int> > GetByFilterCountForReport(TaskFilter filter)
{
return(DaoFactory.MessageDao.GetByFilterCountForReport(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled));
}
private SqlQuery CreateQueryFilterBase(SqlQuery query, TaskFilter filter)
{
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.TaskStatuses.Count != 0)
{
var status = filter.TaskStatuses.First();
if (status == TaskStatus.Open)
{
query.Where(!Exp.Eq("t.status", TaskStatus.Closed));
}
else
{
query.Where("t.status", TaskStatus.Closed);
}
}
if (!filter.UserId.Equals(Guid.Empty))
{
query.Where("t.create_by", filter.UserId);
}
if (!filter.DepartmentId.Equals(Guid.Empty) || (filter.ParticipantId.HasValue && !filter.ParticipantId.Value.Equals(Guid.Empty)))
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("t.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("t.id", "ptr1.task_id"));
if (!filter.DepartmentId.Equals(Guid.Empty))
{
existSubtask.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "pst.responsible_id") & Exp.Eq("cug.tenant", Tenant));
existResponsible.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "ptr1.responsible_id") & Exp.Eq("cug.tenant", Tenant));
existSubtask.Where("cug.groupid", filter.DepartmentId);
existResponsible.Where("cug.groupid", filter.DepartmentId);
}
if (filter.ParticipantId.HasValue && !filter.ParticipantId.Value.Equals(Guid.Empty))
{
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.Value.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.Value.ToString()));
}
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsTasksModule))
{
var taskIds = FullTextSearch.Search(FullTextSearch.ProjectsTasksModule.Match(filter.SearchText));
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsSubtasksModule))
{
taskIds.AddRange(FullTextSearch.Search(FullTextSearch.ProjectsSubtasksModule.Select("task_id").Match(filter.SearchText)));
}
query.Where(Exp.In("t.id", taskIds));
}
else
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
}
return(query);
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.Follow)
{
IEnumerable <string> objects = NotifySource.Instance.GetSubscriptionProvider().GetSubscriptions(NotifyConstants.Event_NewCommentForMessage, NotifySource.Instance.GetRecipientsProvider().GetRecipient(CurrentUserID.ToString()));
if (filter.ProjectIds.Count != 0)
{
objects = objects.Where(r => r.Split('_')[2] == filter.ProjectIds[0].ToString(CultureInfo.InvariantCulture));
}
var ids = objects.Select(r => r.Split('_')[1]).ToArray();
query.Where(Exp.In("t.id", ids));
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("ppp.tenant", "t.tenant_id") & Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " pt", Exp.EqColumns("pt.project_id", "t.project_id"));
query.Where("pt.tag_id", filter.TagId);
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.create_by", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.EqColumns("cug.tenant", "t.tenant_id") & Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.create_by"));
query.Where("cug.groupid", filter.DepartmentId);
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Between("t.create_on", filter.FromDate, filter.ToDate.AddDays(1)));
}
if (filter.MessageStatus.HasValue)
{
query.Where("t.status", filter.MessageStatus.Value);
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsMessagesModule))
{
var mIds = FullTextSearch.Search(FullTextSearch.ProjectsMessagesModule.Match(filter.SearchText));
query.Where(Exp.In("t.id", mIds));
}
else
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
}
if (checkAccess)
{
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
var isInTeam = new SqlQuery(ParticipantTable).Select("security").Where(Exp.EqColumns("p.id", "project_id") & Exp.Eq("removed", false) & Exp.Eq("participant_id", CurrentUserID) & !Exp.Eq("security & " + (int)ProjectTeamSecurity.Messages, (int)ProjectTeamSecurity.Messages));
query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam)));
}
return(query);
}
private IQueryable <TaskResponse> ApplyFilter(IQueryable <TaskResponse> query, TaskFilter filter)
{
if (filter.Name != null)
{
query = query.Where(t => t.Name.StartsWith(filter.Name));
}
if (filter.CompleteDate != null)
{
if (filter.CompleteDate.From != null)
{
query = query.Where(t => t.CompleteDate >= filter.CompleteDate.From);
}
if (filter.CompleteDate.To != null)
{
query = query.Where(t => t.CompleteDate <= filter.CompleteDate.To);
}
}
if (filter.CreateDate != null)
{
if (filter.CreateDate.From != null)
{
query = query.Where(t => t.CreateDate >= filter.CreateDate.From);
}
if (filter.CreateDate.To != null)
{
query = query.Where(t => t.CreateDate <= filter.CreateDate.To);
}
}
if (filter.DueDate != null)
{
if (filter.DueDate.From != null)
{
query = query.Where(t => t.DueDate >= filter.DueDate.From);
}
if (filter.DueDate.To != null)
{
query = query.Where(t => t.DueDate <= filter.DueDate.To);
}
}
if (filter.Status != null)
{
query = query.Where(t => t.Status == filter.Status);
}
if (filter.ProjectId != null)
{
query = query.Where(t => t.Project.Id == filter.ProjectId);
}
if (filter.Tag != null)
{
query = query.Where(t => t.Tags.Contains(filter.Tag));
}
if (filter.HasDueDate != null)
{
query = query.Where(t => t.DueDate != null);
}
return(query);
}
public static TaskFilter FromXml(string xml)
{
if (string.IsNullOrEmpty(xml))
{
throw new ArgumentNullException("xml");
}
var filter = new TaskFilter();
var root = XDocument.Parse(xml).Element("filter");
var date = root.Element("date");
if (date != null)
{
var attribute = date.Attribute("from");
if (attribute != null)
{
filter.FromDate = DateTime.ParseExact(attribute.Value, "yyyyMMdd", null);
}
attribute = date.Attribute("to");
if (attribute != null)
{
filter.ToDate = DateTime.ParseExact(attribute.Value, "yyyyMMdd", null);
}
attribute = date.Attribute("interval");
if (attribute != null)
{
filter.TimeInterval = (ReportTimeInterval)Enum.Parse(typeof(ReportTimeInterval), attribute.Value, true);
}
}
var tag = root.Element("tag");
if (tag != null)
{
filter.TagId = int.Parse(tag.Value);
}
var projects = root.Element("projects");
if (projects != null)
{
foreach (var id in projects.Elements("id"))
{
filter.ProjectIds.Add(int.Parse(id.Value));
}
foreach (var status in projects.Elements("status"))
{
filter.ProjectStatuses.Add((ProjectStatus)int.Parse(status.Value));
}
foreach (var tagName in projects.Elements("tag"))
{
filter.ProjectTag = tagName.Value;
}
}
var tasks = root.Element("tasks");
if (tasks != null)
{
foreach (var status in tasks.Elements("status"))
{
filter.TaskStatuses.Add((TaskStatus)int.Parse(status.Value));
}
}
var users = root.Element("users");
if (users != null)
{
if (users.Attribute("dep") != null)
{
filter.DepartmentId = new Guid(users.Attribute("dep").Value);
}
foreach (var id in users.Elements("id"))
{
filter.UserId = new Guid(id.Value);
}
}
var milestones = root.Element("milestones");
if (milestones != null)
{
foreach (var status in milestones.Elements("status"))
{
filter.MilestoneStatuses.Add((MilestoneStatus)int.Parse(status.Value));
}
}
var view = root.Attribute("view");
if (view != null)
{
filter.ViewType = int.Parse(view.Value);
}
var noResponsible = root.Attribute("noResponsible");
if (noResponsible != null)
{
filter.NoResponsible = bool.Parse(noResponsible.Value);
}
var paymentStatuses = root.Element("paymentStatuses");
if (paymentStatuses != null)
{
foreach (var status in paymentStatuses.Elements("status"))
{
filter.PaymentStatuses.Add((PaymentStatus)int.Parse(status.Value));
}
}
var isShowAverageTime = root.Element("isShowAverageTime");
if (isShowAverageTime != null)
{
filter.IsShowAverageTime = bool.Parse(isShowAverageTime.Value);
}
var typeOfShowAverageTime = root.Element("typeOfShowAverageTime");
if (typeOfShowAverageTime != null)
{
filter.TypeOfShowAverageTime = (AverageTime)Enum.Parse(typeof(AverageTime), typeOfShowAverageTime.Value);
}
var showAvgForTask = root.Element("showAvgForTask");
if (showAvgForTask != null)
{
filter.IsShowAverageTime = true;
filter.TypeOfShowAverageTime = AverageTime.CompletingTasks;
}
return(filter);
}
public virtual ActionResult ListData([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest request, TaskFilter taskFilter = null)
{
var tasks = this.GetTasks(request, taskFilter);
var rows = columns.ExtractRows(tasks.Decorate());
//return null;
return Json(new DataTablesResponse(request.Draw, rows, tasks.TotalItems, tasks.TotalItems));
}
public static string ToUri(TaskFilter filter)
{
var uri = new StringBuilder();
uri.AppendFormat("&ftime={0}", (int)filter.TimeInterval);
if (HasDate(filter.FromDate))
{
uri.AppendFormat("&ffrom={0}", filter.FromDate.ToString("yyyyMMdd"));
}
if (HasDate(filter.ToDate))
{
uri.AppendFormat("&fto={0}", filter.ToDate.ToString("yyyyMMdd"));
}
if (filter.HasProjectIds)
{
uri.AppendFormat("&fpid={0}", string.Join(LIST_SEP, filter.ProjectIds.Select(id => id.ToString()).ToArray()));
}
if (filter.HasProjectStatuses)
{
uri.AppendFormat("&fps={0}", (int)filter.ProjectStatuses.FirstOrDefault());
}
if (filter.TagId > 0)
{
uri.AppendFormat("&fpt={0}", filter.TagId);
}
if (filter.UserId != default(Guid))
{
uri.AppendFormat("&fu={0}", filter.UserId);
}
if (filter.DepartmentId != default(Guid))
{
uri.AppendFormat("&fd={0}", filter.DepartmentId);
}
if (filter.HasMilestoneStatuses)
{
uri.AppendFormat("&fms={0}", (int)filter.MilestoneStatuses.FirstOrDefault());
}
if (filter.HasTaskStatuses)
{
uri.AppendFormat("&fts={0}", (int)filter.TaskStatuses.FirstOrDefault());
}
if (filter.ViewType != 0)
{
uri.AppendFormat("&fv={0}", filter.ViewType);
}
if (filter.NoResponsible)
{
uri.AppendFormat("&noRes={0}", filter.NoResponsible);
}
if (filter.PaymentStatuses.Any())
{
uri.AppendFormat("&fpays={0}", string.Join(LIST_SEP, filter.PaymentStatuses.Select(id => ((int)id).ToString(CultureInfo.InvariantCulture)).ToArray()));
}
if (filter.ViewType == 1 && filter.TypeOfShowAverageTime == AverageTime.CompletingTasks && filter.IsShowAverageTime)
{
uri.AppendFormat("&ctasks={0}", true);
}
else if (filter.IsShowAverageTime)
{
uri.AppendFormat("&atchecked={0}", filter.IsShowAverageTime);
if (filter.TypeOfShowAverageTime != AverageTime.All)
{
uri.AppendFormat("&atstat={0}", filter.TypeOfShowAverageTime);
}
}
return(uri.ToString().Trim('&').ToLower());
}
public virtual ActionResult PendingTaskListEstimated(TaskFilter taskFilter)
{
var tasks = TaskService.GetAll(Query.ForTask(taskFilter).Include(x => x.Project())).ToList();
var model = new TasksListViewModel { Tasks = tasks };
return PartialView(MVC.Tasks.Views.PendingTaskListEstimated, model);
}
public static TaskFilter FromUri(string uri)
{
var filter = new TaskFilter();
var p = GetParameterFromUri(uri, "ftime");
if (!string.IsNullOrEmpty(p))
{
filter.TimeInterval = (ReportTimeInterval)Enum.Parse(typeof(ReportTimeInterval), p, true);
}
p = GetParameterFromUri(uri, "ffrom");
if (!string.IsNullOrEmpty(p))
{
filter.FromDate = DateTime.ParseExact(p, "yyyyMMdd", null);
}
p = GetParameterFromUri(uri, "fto");
if (!string.IsNullOrEmpty(p))
{
filter.ToDate = DateTime.ParseExact(p, "yyyyMMdd", null);
}
p = GetParameterFromUri(uri, "fu");
if (!string.IsNullOrEmpty(p))
{
if (GetParameterFromUri(uri, "reportType") == "6")
{
filter.ParticipantId = new Guid(p);
}
else
{
filter.UserId = new Guid(p);
}
}
p = GetParameterFromUri(uri, "fd");
if (!string.IsNullOrEmpty(p))
{
filter.DepartmentId = new Guid(p);
}
p = GetParameterFromUri(uri, "fpid");
if (!string.IsNullOrEmpty(p))
{
filter.ProjectIds = Split(p).Select(v => int.Parse(v)).ToList();
}
p = GetParameterFromUri(uri, "fps");
if (!string.IsNullOrEmpty(p))
{
filter.ProjectStatuses = ToEnumsArray <ProjectStatus>(p);
}
p = GetParameterFromUri(uri, "fpt");
if (!string.IsNullOrEmpty(p))
{
filter.TagId = int.Parse(p);
}
p = GetParameterFromUri(uri, "fms");
if (!string.IsNullOrEmpty(p))
{
filter.MilestoneStatuses = ToEnumsArray <MilestoneStatus>(p);
}
p = GetParameterFromUri(uri, "fts");
if (!string.IsNullOrEmpty(p))
{
filter.TaskStatuses = ToEnumsArray <TaskStatus>(p);
}
p = GetParameterFromUri(uri, "ftss");
if (!string.IsNullOrEmpty(p))
{
if (p.StartsWith("all"))
{
filter.Substatus = int.Parse(p.Substring(3));
}
else
{
filter.Substatus = int.Parse(p);
}
}
p = GetParameterFromUri(uri, "fv");
if (!string.IsNullOrEmpty(p))
{
filter.ViewType = int.Parse(p);
}
p = GetParameterFromUri(uri, "nores");
if (!string.IsNullOrEmpty(p))
{
filter.NoResponsible = bool.Parse(p);
}
p = GetParameterFromUri(uri, "fpays");
if (!string.IsNullOrEmpty(p))
{
filter.PaymentStatuses = ToEnumsArray <PaymentStatus>(p);
}
p = GetParameterFromUri(uri, "atstat");
if (!string.IsNullOrEmpty(p))
{
filter.ViewAverageTime = ToEnumsArray <AverageTime>(p);
}
p = GetParameterFromUri(uri, "atchecked");
if (!string.IsNullOrEmpty(p))
{
filter.IsShowAverageTime = bool.Parse(p);
}
p = GetParameterFromUri(uri, "atstat");
if (!string.IsNullOrEmpty(p))
{
filter.TypeOfShowAverageTime = (AverageTime)Enum.Parse(typeof(AverageTime), p);
}
p = GetParameterFromUri(uri, "ctasks");
if (!string.IsNullOrEmpty(p) && bool.Parse(p))
{
filter.TypeOfShowAverageTime = AverageTime.CompletingTasks;
filter.IsShowAverageTime = true;
}
return(filter);
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.MilestoneStatuses.Count != 0)
{
query.Where(Exp.In("t.status", filter.MilestoneStatuses));
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (ProjectsCommonSettings.Load().HideEntitiesInPausedProjects)
{
query.Where(!Exp.Eq("p.status", ProjectStatus.Paused));
}
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where(Exp.Eq("t.responsible_id", filter.UserId));
}
if (filter.TagId != 0)
{
if (filter.TagId == -1)
{
query.LeftOuterJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", null);
}
else
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
}
if (filter.ParticipantId.HasValue)
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("pt.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("pt.id", "ptr1.task_id"));
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString()));
query.LeftOuterJoin(TasksTable + " as pt", Exp.EqColumns("pt.milestone_id", "t.id") & Exp.EqColumns("pt.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsMilestonesModule))
{
var mIds = FullTextSearch.Search(FullTextSearch.ProjectsMilestonesModule.Match(filter.SearchText));
query.Where(Exp.In("t.id", mIds));
}
else
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
}
CheckSecurity(query, filter, isAdmin, checkAccess);
return(query);
}
public static string ToXml(TaskFilter filter)
{
var doc = new XDocument();
var root = new XElement("filter");
var date = new XElement("date");
root.Add(date);
date.Add(new XAttribute("interval", filter.TimeInterval));
if (HasDate(filter.FromDate))
{
date.Add(new XAttribute("from", filter.FromDate.ToString("yyyyMMdd")));
}
if (HasDate(filter.ToDate))
{
date.Add(new XAttribute("to", filter.ToDate.ToString("yyyyMMdd")));
}
if (filter.TagId > 0)
{
root.Add(new XElement("tag", filter.TagId));
}
if (filter.HasProjectIds || filter.HasProjectStatuses)
{
var projects = new XElement("projects");
root.Add(projects);
foreach (var id in filter.ProjectIds)
{
projects.Add(new XElement("id", id));
}
foreach (var status in filter.ProjectStatuses)
{
projects.Add(new XElement("status", (int)status));
}
}
if (filter.HasUserId)
{
var users = new XElement("users");
root.Add(users);
if (filter.DepartmentId != default(Guid))
{
users.Add(new XAttribute("dep", filter.DepartmentId.ToString("N")));
}
if (filter.UserId != default(Guid))
{
users.Add(new XElement("id", filter.UserId.ToString("N")));
}
}
if (filter.HasMilestoneStatuses)
{
var milestone = new XElement("milestones");
root.Add(milestone);
foreach (var status in filter.MilestoneStatuses)
{
milestone.Add(new XElement("status", (int)status));
}
}
if (filter.HasTaskStatuses)
{
var tasks = new XElement("tasks");
root.Add(tasks);
foreach (var status in filter.TaskStatuses)
{
tasks.Add(new XElement("status", (int)status));
}
}
if (filter.ViewType != 0)
{
root.Add(new XAttribute("view", filter.ViewType));
}
if (filter.NoResponsible)
{
root.Add(new XAttribute("noResponsible", filter.NoResponsible));
}
if (filter.PaymentStatuses.Any())
{
var paymentStatuses = new XElement("paymentStatuses");
root.Add(paymentStatuses);
foreach (var status in filter.PaymentStatuses)
{
paymentStatuses.Add(new XElement("status", (int)status));
}
}
if (filter.ViewType == 1 && filter.TypeOfShowAverageTime == AverageTime.CompletingTasks && filter.IsShowAverageTime)
{
root.Add(new XElement("showAvgForTask", true));
}
else if (filter.IsShowAverageTime)
{
root.Add(new XElement("isShowAverageTime", filter.IsShowAverageTime));
if (filter.TypeOfShowAverageTime != AverageTime.All)
{
root.Add(new XElement("typeOfShowAverageTime", filter.TypeOfShowAverageTime));
}
}
doc.AddFirst(root);
return(doc.ToString(SaveOptions.DisableFormatting));
}
public override IEnumerable <object[]> BuildReport(TaskFilter filter)
{
return(new List <object[]>());
}
public override IEnumerable <object[]> BuildDocbuilderReport(TaskFilter filter)
{
filter.FromDate = filter.GetFromDate(true);
filter.ToDate = filter.GetToDate(true);
using (var scope = DIHelper.Resolve())
{
var factory = scope.Resolve <EngineFactory>();
IEnumerable <object[]> taskTime = new List <object[]>();
switch (filter.ViewType)
{
case 0:
taskTime = factory.TimeTrackingEngine.GetByFilter(filter)
.Select(r =>
new object[]
{
CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false),
r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : ""
});
taskTime = taskTime.GroupBy(r => (string)r[0], (a, b) =>
{
var enumerable = b as IList <object[]> ?? b.ToList();
var data = (object[])enumerable.First().Clone();
data[1] = enumerable.Sum(c => (float)c[1]);
data[2] = enumerable.Where(r => (PaymentStatus)r[3] == PaymentStatus.Billed).Sum(c => (float)c[1]);
return(data);
});
return(taskTime.OrderBy(r => (string)r[0]));
case 1:
taskTime = factory.TimeTrackingEngine.GetByFilter(filter)
.Select(r =>
new object[]
{
CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false),
r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : "",
r.Task.Project, r.Task.Title
});
taskTime = taskTime.Select(r =>
{
if ((PaymentStatus)r[3] == PaymentStatus.Billed)
{
r[2] = r[1];
}
return(r);
});
var users = taskTime.GroupBy(x => x[0]).Select(x => new object[] { x.Key, x.ToList()
.Select(r => new object[] { r[1], r[2], r[3], r[4], r[5], r[6] }) });
var result = new List <object[]>();
foreach (var user in users) // user = [string, []]
{
var userTasks = (IEnumerable <object[]>)user[1];
var groupedUserTasks = userTasks.GroupBy(x => x[4]).Select(r => new object[] {
new object[] { ((Project)r.Key).Title, ProjectsCommonResource.Status + ": " + LocalizedEnumConverter.ConvertToString(((Project)r.Key).Status),
ProjectResource.ProjectLeader + ": " + CoreContext.UserManager.GetUsers(((Project)r.Key).Responsible).DisplayUserName(false),
TaskResource.CreatingDate + ": " + ((Project)r.Key).CreateOn.ToString("d"),
((Project)r.Key).Description != "" ? ProjectsCommonResource.Description + ": " + ((Project)r.Key).Description : "" }, r.ToList().Select(y => new object[] { y[0], y[1], y[2], y[3], y[5] })
});
result.Add(new object[] { user[0], groupedUserTasks });
}
return(result);
case 2:
taskTime = factory.TimeTrackingEngine.GetByFilter(filter)
.Select(r =>
new object[]
{
CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false),
r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : "",
r.Task.Project.Title
});
return(taskTime.GroupBy(x => (string)x[5]).Select(x => new object[] { x.Key, x.GroupBy(r => (string)r[0], (a, b) =>
{
var enumerable = b as IList <object[]> ?? b.ToList();
var data = (object[])enumerable.First().Clone();
data[1] = enumerable.Sum(c => (float)c[1]);
data[2] = enumerable.Where(r => (PaymentStatus)r[3] == PaymentStatus.Billed).Sum(c => (float)c[1]);
return data;
}) }).OrderBy(x => (string)x[0]));
default:
throw new Exception(ProjectsCommonResource.NoData);
}
}
}
public override IEnumerable <object[]> BuildReport(TaskFilter filter)
{
return(base.BuildReport(filter).Where(r => (int)r[5] == 0));
}
public IEnumerable <TaskFilterCountOperationResult> GetByFilterCountForStatistic(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var result = new List <TaskFilterCountOperationResult>();
var query = new SqlQuery(TasksTable + " t")
.Select("t.status", "r.responsible_id")
.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.InnerJoin(TasksResponsibleTable + " r", Exp.EqColumns("r.task_id", "t.id") & Exp.EqColumns("r.tenant_id", "t.tenant_id"))
.Where("t.tenant_id", Tenant);
if (filter.HasUserId)
{
query.Where(Exp.In("r.responsible_id", filter.GetUserIds()));
}
else
{
query.Where(!Exp.Eq("r.responsible_id", Guid.Empty.ToString()));
}
filter.UserId = Guid.Empty;
query = CreateQueryFilterCount(query, filter, isAdmin, checkAccess);
var queryCount = new SqlQuery()
.SelectCount()
.Select("t1.responsible_id")
.Select("t1.status")
.From(query, "t1")
.GroupBy("responsible_id", "status");
var fromDb = Db.ExecuteList(queryCount)
.Select(r => new
{
Id = Guid.Parse((string)r[1]),
Count = Convert.ToInt32(r[0]),
Status = Convert.ToInt32(r[2])
}).GroupBy(r => r.Id);
foreach (var r in fromDb)
{
var tasksOpen = r.Where(row => row.Status != (int)TaskStatus.Closed).Sum(row => row.Count);
var tasksClosed = r.Where(row => row.Status == (int)TaskStatus.Closed).Sum(row => row.Count);
result.Add(new TaskFilterCountOperationResult {
UserId = r.Key, TasksOpen = tasksOpen, TasksClosed = tasksClosed
});
}
return(result);
}
public int GetByFilterCount(TaskFilter filter)
{
return(timeSpendDao.GetByFilterCount(filter, ProjectSecurity.CurrentUserAdministrator));
}
public virtual List <Project> GetByFilter(TaskFilter filter)
{
return(projectDao.GetByFilter(filter, ProjectSecurity.CurrentUserAdministrator));
}
