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