private void IntersectTagsAndProjects(ReportFilter filter) { if (!string.IsNullOrEmpty(filter.ProjectTag)) { var query = new SqlQuery() .From("projects_tags t") .From("projects_project_tag p") .Select("p.project_id") .Where(Exp.EqColumns("t.id", "p.tag_id")) .Where("t.tenant_id", Tenant) .Where("lower(t.title)", filter.ProjectTag.ToLower()); var ids = DbManager.ExecuteList(query).Select(r => Convert.ToInt32(r[0])); if (filter.HasProjectIds) { filter.ProjectIds.AddRange(ids); } else { filter.SetProjectIds(ids); } filter.ProjectTag = null; } }
public IList<object[]> BuildProjectWithoutOpenMilestone(ReportFilter filter) { IntersectUsersAndProjects(filter); IntersectTagsAndProjects(filter); var query = new SqlQuery("projects_projects p") .LeftOuterJoin("projects_milestones m", Exp.EqColumns("p.id", "m.project_id") & Exp.EqColumns("p.tenant_id", "m.tenant_id")) .Select("p.id") .Where("p.tenant_id", Tenant) .GroupBy(1) .Having(Exp.Eq("sum(case m.status when 0 then 1 else 0 end)", 0)); if (filter.HasProjectIds) { query.Where(Exp.In("p.id", filter.ProjectIds)); } var ids = DbManager .ExecuteList(query) .ConvertAll(r => Convert.ToInt32(r[0])) .ToArray(); filter.SetProjectIds(ids); return BuildProjectListReport(filter); }
private void IntersectUsersAndProjects(ReportFilter filter) { if (filter.HasUserId) { var query = new SqlQuery("projects_project_participant") .Select("project_id") .Where(Exp.In("participant_id", filter.GetUserIds()) & Exp.Eq("removed", false)) .GroupBy(1); if (filter.HasProjectIds) { query.Where(Exp.In("project_id", filter.ProjectIds)); } var ids = DbManager .ExecuteList(query) .ConvertAll(r => Convert.ToInt32(r[0])) .ToArray(); filter.SetProjectIds(ids); filter.UserId = Guid.Empty; filter.DepartmentId = Guid.Empty; } }
protected IList<object[]> RecieveData() { var projectCollection = new ArrayList(); var filter = new ReportFilter(); if (!ShowClosedProjects) filter.ProjectStatuses.Add(ProjectStatus.Open); switch (CurrentFilter) { case ProjectFilter.My: case ProjectFilter.Default: filter.UserId = SecurityContext.CurrentAccount.ID; break; case ProjectFilter.Following: filter.SetProjectIds(Global.EngineFactory.GetParticipantEngine().GetFollowingProjects(SecurityContext.CurrentAccount.ID)); break; case ProjectFilter.ByLabel: filter = new ReportFilter(); filter.SetProjectIds(Global.EngineFactory.GetTagEngine().GetTagProjects(Tag)); break; } return Global.EngineFactory.GetReportEngine().BuildProjectsListReport(filter); }