public IList<object[]> BuildTaskListReport(ReportFilter filter) { IntersectTagsAndProjects(filter); var query = new SqlQuery("projects_tasks t") .InnerJoin("projects_projects p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("t.project_id", "p.id")) .LeftOuterJoin("projects_milestones m", Exp.EqColumns("m.tenant_id", "t.tenant_id") & Exp.EqColumns("t.milestone_id", "m.id")) .LeftOuterJoin("projects_tasks_responsible ptr", Exp.EqColumns("ptr.tenant_id", "t.tenant_id") & Exp.EqColumns("ptr.task_id", "t.id")) .Select("p.id", "p.title") .Select("m.id", "m.title", "m.deadline", "m.status") .Select("t.id", "t.title", "COALESCE(ptr.responsible_id, t.responsible_id)", "t.status", "t.deadline", "substring(t.description, 0, 510)") .Where("t.tenant_id", Tenant) .OrderBy("p.title", true) .OrderBy("p.id", true) .OrderBy("m.status", true) .OrderBy("m.deadline", true) .OrderBy("m.title", true) .OrderBy("m.id", true) .OrderBy("t.sort_order", false) .OrderBy("t.status", true) .OrderBy("t.priority", true) .OrderBy("t.create_on", true); if (filter.HasProjectIds) { query.Where(Exp.In("p.id", filter.ProjectIds)); } if (filter.HasTaskStatuses) { query.Where(Exp.In("t.status", filter.TaskStatuses)); } if (filter.HasUserId) { query.Where(Exp.In("ptr.responsible_id", filter.GetUserIds())); } if (filter.GetToDate() != DateTime.MaxValue) { query.Where(Exp.Between("t.deadline", new DateTime(1900, 1, 1), filter.GetToDate())); } if(!filter.NoResponsible) { query.Where(!Exp.Eq("ptr.responsible_id", null) & !Exp.Eq("t.responsible_id", Guid.Empty)); } query.GroupBy("t.id"); return DbManager .ExecuteList(query) .ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1], r[2] != null ? Convert.ToInt32(r[2]) : 0, (string)r[3], r[4] != null ? (TenantUtil.DateTimeFromUtc((DateTime)r[4])).ToString("d") : null, r[5] != null ? Convert.ToInt32(r[5]) : -1, Convert.ToInt32(r[6]), (string)r[7], ToGuid(r[8]), (TaskStatus)Convert.ToInt32(r[9]), r[10] != null && !DateTime.MinValue.Equals(r[10]) ? (TenantUtil.DateTimeFromUtc((DateTime)r[10])).ToString("d") : null, ASC.Common.Utils.HtmlUtil.GetText((string)r[11], 500) }); }
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess) { if (filter.TagId != 0) { query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "p.id")); query.Where("ptag.tag_id", filter.TagId); } if (filter.HasUserId || (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty)) { var existParticipant = new SqlQuery(ParticipantTable + " ppp").Select("ppp.participant_id").Where(Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.tenant", Tenant)); if (filter.DepartmentId != Guid.Empty) { existParticipant.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "ppp.participant_id") & Exp.EqColumns("cug.tenant", "ppp.tenant")); existParticipant.Where("cug.groupid", filter.DepartmentId); } if (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty) { existParticipant.Where(Exp.Eq("ppp.participant_id", filter.ParticipantId.ToString())); } query.Where(Exp.Exists(existParticipant)); } if (filter.UserId != Guid.Empty) { query.Where("responsible_id", filter.UserId); } if (filter.Follow) { query.InnerJoin(FollowingProjectTable + " pfpp", Exp.EqColumns("p.id", "pfpp.project_id")); query.Where(Exp.Eq("pfpp.participant_id", CurrentUserID)); } if (filter.ProjectStatuses.Count != 0) { query.Where(Exp.Eq("p.status", filter.ProjectStatuses.First())); } if (!string.IsNullOrEmpty(filter.SearchText)) { if (FullTextSearch.SupportModule(FullTextSearch.ProjectsModule)) { var projIds = FullTextSearch.Search(FullTextSearch.ProjectsModule.Match(filter.SearchText)); query.Where(Exp.In("p.id", projIds)); } else { query.Where(Exp.Like("p.title", filter.SearchText, SqlLike.AnyWhere)); } } query.GroupBy("p.id"); 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)); query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam))); } 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)); } 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) { 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); } if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue) && !filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue)) { 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; }