public List <Tuple <Guid, int, int> > GetByFilterAverageTime(TaskFilter filter, bool isAdmin, bool checkAccess) { filter = (TaskFilter)filter.Clone(); var query = new SqlQuery(TasksTable + " t") .Select("ROUND(AVG(TIME_TO_SEC(TIMEDIFF(t.status_changed, t.create_on))/60/60))", "tr.responsible_id, t.project_id") .InnerJoin(TasksResponsibleTable + " tr", Exp.EqColumns("t.tenant_id", "tr.tenant_id") & Exp.EqColumns("t.id", "tr.task_id")) .InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id")) .Where("t.tenant_id", Tenant) .Where(Exp.Eq("t.status", 2)) .Where(Exp.Between("t.status_changed", filter.GetFromDate(), filter.GetToDate())); if (filter.GetFromDate() != DateTime.MinValue && filter.GetToDate() != DateTime.MaxValue) { query.Where((Exp.Between("t.create_on", filter.GetFromDate(), filter.GetToDate()) & !Exp.Eq("t.start_date", DateTime.MinValue)) | Exp.Between("t.start_date", filter.GetFromDate(), filter.GetToDate())); } if (filter.HasUserId) { query.Where(Exp.In("tr.responsible_id", filter.GetUserIds())); filter.UserId = Guid.Empty; filter.DepartmentId = Guid.Empty; } query = CreateQueryFilterCount(query, filter, isAdmin, checkAccess); query.GroupBy("tr.responsible_id", "project_id"); return(Db.ExecuteList(query).ConvertAll(a => new Tuple <Guid, int, int>(Guid.Parse((string)a[1]), Convert.ToInt32(a[2]), Convert.ToInt32(a[0])))); }
public List <Tuple <Guid, int, int> > GetByFilterCountForReport(TaskFilter filter, bool isAdmin, bool checkAccess) { filter = (TaskFilter)filter.Clone(); var query = new SqlQuery(TasksTable + " t") .InnerJoin(TasksResponsibleTable + " tr", Exp.EqColumns("t.tenant_id", "tr.tenant_id") & Exp.EqColumns("t.id", "tr.task_id")) .Select("tr.responsible_id", "t.project_id") .InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id")) .Where("t.tenant_id", Tenant); if (filter.GetFromDate() != DateTime.MinValue && filter.GetToDate() != DateTime.MaxValue) { query.Where((Exp.Between("t.create_on", filter.GetFromDate(), filter.GetToDate()) & !Exp.Eq("t.start_date", DateTime.MinValue)) | Exp.Between("t.start_date", filter.GetFromDate(), filter.GetToDate())); } if (filter.HasUserId) { query.Where(Exp.In("tr.responsible_id", filter.GetUserIds())); filter.UserId = Guid.Empty; filter.DepartmentId = Guid.Empty; } query = CreateQueryFilterCount(query, filter, isAdmin, checkAccess); var queryCount = new SqlQuery() .SelectCount() .Select("t1.responsible_id", "t1.project_id") .From(query, "t1") .GroupBy("responsible_id", "project_id"); return(Db.ExecuteList(queryCount).ConvertAll(b => new Tuple <Guid, int, int>(Guid.Parse((string)b[1]), Convert.ToInt32(b[2]), Convert.ToInt32(b[0])))); }
public int GetByFilterCountForReport(TaskFilter filter, bool isAdmin, bool checkAccess) { filter = (TaskFilter)filter.Clone(); var query = new SqlQuery(ProjectsTable + " p") .Select("p.id") .Where("p.tenant_id", Tenant); if (filter.ProjectStatuses.Contains(ProjectStatus.Closed) || filter.ProjectStatuses.Contains(ProjectStatus.Paused)) { query.Where(Exp.Between("p.status_changed", filter.GetFromDate(), filter.GetToDate()) & Exp.Eq("p.status", filter.ProjectStatuses[0])); filter.ProjectStatuses.Clear(); } if (filter.GetFromDate() != DateTime.MinValue && filter.GetToDate() != DateTime.MaxValue) { query.Where(Exp.Between("p.create_on", filter.GetFromDate(), filter.GetToDate())); } query = CreateQueryFilter(query, filter, isAdmin, checkAccess); query.GroupBy("p.id"); var queryCount = new SqlQuery().SelectCount().From(query, "t1"); return(Db.ExecuteScalar <int>(queryCount)); }
public object[] GetByFilterCountForReport(TaskFilter filter) { var cloneFilter = (TaskFilter)filter.Clone(); var open = 0; var paused = 0; var closed = 0; if (!filter.ProjectStatuses.Any() || filter.ProjectStatuses.Contains(ProjectStatus.Open)) { cloneFilter.ProjectStatuses = new List <ProjectStatus> { ProjectStatus.Open }; open = DaoFactory.ProjectDao.GetByFilterCountForReport(cloneFilter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled); } if (!filter.ProjectStatuses.Any() || filter.ProjectStatuses.Contains(ProjectStatus.Paused)) { cloneFilter.ProjectStatuses = new List <ProjectStatus> { ProjectStatus.Paused }; paused = DaoFactory.ProjectDao.GetByFilterCountForReport(cloneFilter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled); } if (!filter.ProjectStatuses.Any() || filter.ProjectStatuses.Contains(ProjectStatus.Closed)) { cloneFilter.ProjectStatuses = new List <ProjectStatus> { ProjectStatus.Closed }; closed = DaoFactory.ProjectDao.GetByFilterCountForReport(cloneFilter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled); } return(new object[3] { open, paused, closed }); }
public List <Project> GetByFilterForReport(TaskFilter filter, bool isAdmin, bool checkAccess) { filter = (TaskFilter)filter.Clone(); Exp exp = null; if (filter.ProjectStatuses.Contains(ProjectStatus.Closed)) { filter.ProjectStatuses.Remove(ProjectStatus.Closed); exp = Exp.Between("p.status_changed", filter.GetFromDate(), filter.GetToDate()) & Exp.Eq("p.status", 1) | Exp.In("p.status", filter.ProjectStatuses); filter.ProjectStatuses.Clear(); } ; var query = CreateQueryForFilter(filter, isAdmin, checkAccess); query.Where(Exp.Between("p.create_on", filter.GetFromDate(), filter.GetToDate())); query.Where(Exp.Eq("p.status", 0) | (Exp.Between("p.status_changed", filter.GetFromDate(), filter.GetToDate()))); if (exp != null) { query.Where(exp); } return(Db.ExecuteList(query).ConvertAll(ToProjectFull)); }