public IList <object[]> BuildUsersStatisticsReport(ReportFilter filter)
{
var query = new SqlQuery("projects_tasks t")
.Select("r.responsible_id")
.InnerJoin("projects_tasks_responsible r", Exp.EqColumns("r.task_id", "t.id") & Exp.EqColumns("r.tenant_id", "t.tenant_id"))
.SelectSum("0")
.SelectSum("case t.status when 2 then 0 else 1 end")
.SelectSum("case t.status when 2 then 1 else 0 end")
.Where("t.tenant_id", Tenant)
.GroupBy(1);
if (filter.HasUserId)
{
query.Where(Exp.In("r.responsible_id", filter.GetUserIds()));
}
else
{
query.Where(!Exp.Eq("r.responsible_id", Guid.Empty.ToString()));
}
if (filter.HasProjectIds)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
return(DbManager
.ExecuteList(query)
.ConvertAll(r => new object[] { ToGuid(r[0]), Convert.ToInt64(r[1]), Convert.ToInt64(r[2]), Convert.ToInt64(r[3]) }));
}
public IList <object[]> BuildUsersWorkReport(ReportFilter filter)
{
IntersectTagsAndProjects(filter);
var query = new SqlQuery("projects_tasks t")
.InnerJoin("projects_tasks_responsible ptr", Exp.EqColumns("ptr.task_id", "t.id") & Exp.EqColumns("ptr.tenant_id", "t.tenant_id"))
.LeftOuterJoin("projects_projects p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("p.tenant_id", "t.tenant_id"))
.Select("ptr.responsible_id", "t.id", "t.title", "p.id", "p.title")
.Where("t.tenant_id", Tenant)
.Where("t.status", TaskStatus.Closed)
.Where(Exp.Between("t.last_modified_on", filter.GetFromDate(true), filter.GetToDate(true)))
.OrderBy("t.sort_order", true);
if (filter.HasProjectIds)
{
query.Where(Exp.In("p.id", filter.ProjectIds));
}
if (filter.HasUserId)
{
query.Where(Exp.In("ptr.responsible_id", filter.GetUserIds()));
}
else
{
query.Where(!Exp.Eq("ptr.responsible_id", Guid.Empty.ToString()));
}
return(DbManager
.ExecuteList(query)
.ConvertAll(r => new object[] { ToGuid(r[0]), Convert.ToInt32(r[1]), (string)r[2], Convert.ToInt32(r[3]), (string)r[4] }));
}
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) }));
}
public IList <object[]> BuildTimeReport(ReportFilter filter)
{
var query = new SqlQuery("projects_time_tracking t")
.LeftOuterJoin("projects_tasks r", Exp.EqColumns("t.relative_task_id", "r.id") & Exp.EqColumns("r.tenant_id", "t.tenant_id"))
.Select("t.person_id", "t.project_id", "t.relative_task_id", "r.title")
.SelectSum("t.hours")
.Where("t.tenant_id", Tenant)
.Where(!Exp.Eq("t.relative_task_id", 0))
.Where(Exp.Between("t.date", filter.GetFromDate(true), filter.GetToDate(true)))
.GroupBy(1, 2, 3);
if (filter.HasProjectIds)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
if (filter.HasUserId)
{
query.Where(Exp.In("t.person_id", filter.GetUserIds()));
}
return(DbManager.ExecuteList(query));
}
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;
}
}