public IList <object[]> BuildUsersStatisticsReport(TaskFilter filter) { var query = new SqlQuery(TasksTable + " t") .Select("r.responsible_id") .InnerJoin(TasksResponsibleTable + " 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())); } query.Where(filter.HasProjectIds ? Exp.In("t.project_id", filter.ProjectIds) : Exp.Gt("t.project_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); } using (var db = new DbManager(DatabaseId)) { return(db.ExecuteList(query) .ConvertAll(r => new object[] { ToGuid(r[0]), Convert.ToInt64(r[1]), Convert.ToInt64(r[2]), Convert.ToInt64(r[3]) })); } }
public List <Tuple <Guid, int, int> > GetByFilterCountForReport(TaskFilter filter, bool isAdmin, bool checkAccess) { var query = new SqlQuery(MilestonesTable + " t") .InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id")) .Select("t.create_by", "t.project_id") .Where("t.tenant_id", Tenant) .Where(Exp.Between("t.create_on", filter.GetFromDate(), filter.GetToDate())); if (filter.HasUserId) { query.Where(Exp.In("t.create_by", filter.GetUserIds())); filter.UserId = Guid.Empty; filter.DepartmentId = Guid.Empty; } query = CreateQueryFilter(query, filter, isAdmin, checkAccess); var queryCount = new SqlQuery() .SelectCount() .Select("t1.create_by", "t1.project_id") .GroupBy("create_by", "project_id") .From(query, "t1"); 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 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 Dictionary <Guid, int> GetByFilterCountForReport(TaskFilter filter, bool isAdmin, bool checkAccess) { using (var db = new DbManager(DatabaseId)) { var query = new SqlQuery(MessagesTable + " t") .InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id")) .Select("t.create_by") .Where("t.tenant_id", Tenant) .Where(Exp.Between("t.create_on", filter.GetFromDate(), filter.GetToDate())); if (filter.HasUserId) { query.Where(Exp.In("t.create_by", filter.GetUserIds())); filter.UserId = Guid.Empty; filter.DepartmentId = Guid.Empty; } query = CreateQueryFilter(query, filter, isAdmin, checkAccess); var queryCount = new SqlQuery() .SelectCount() .Select("t1.create_by") .GroupBy(2) .From(query, "t1"); return(db.ExecuteList(queryCount).ToDictionary(a => Guid.Parse((string)a[1]), b => Convert.ToInt32(b[0]))); } }
public IEnumerable <TaskFilterCountOperationResult> GetByFilterCountForStatistic(TaskFilter filter, bool isAdmin, bool checkAccess) { var result = new List <TaskFilterCountOperationResult>(); var query = new SqlQuery(TasksTable + " t") .Select("t.status", "r.responsible_id") .InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id")) .InnerJoin(TasksResponsibleTable + " r", Exp.EqColumns("r.task_id", "t.id") & Exp.EqColumns("r.tenant_id", "t.tenant_id")) .Where("t.tenant_id", Tenant); if (filter.HasUserId) { query.Where(Exp.In("r.responsible_id", filter.GetUserIds())); } else { query.Where(!Exp.Eq("r.responsible_id", Guid.Empty.ToString())); } filter.UserId = Guid.Empty; query = CreateQueryFilterCount(query, filter, isAdmin, checkAccess); var queryCount = new SqlQuery() .SelectCount() .Select("t1.responsible_id") .Select("t1.status") .From(query, "t1") .GroupBy("responsible_id", "status"); if (filter.ParticipantId.HasValue && !filter.ParticipantId.Value.Equals(Guid.Empty)) { queryCount.Where("t1.responsible_id", filter.ParticipantId.Value); } var fromDb = Db.ExecuteList(queryCount) .Select(r => new { Id = Guid.Parse((string)r[1]), Count = Convert.ToInt32(r[0]), Status = Convert.ToInt32(r[2]) }).GroupBy(r => r.Id); foreach (var r in fromDb) { var tasksOpen = r.Where(row => row.Status != (int)TaskStatus.Closed).Sum(row => row.Count); var tasksClosed = r.Where(row => row.Status == (int)TaskStatus.Closed).Sum(row => row.Count); result.Add(new TaskFilterCountOperationResult { UserId = r.Key, TasksOpen = tasksOpen, TasksClosed = tasksClosed }); } return(result); }
public IList <object[]> BuildUsersActivityReport(TaskFilter filter) { /* query: * select u, sum(case t when 't' then c else 0 end), sum(case t when 'm' then c else 0 end), sum(case t when 'd' then c else 0 end) * from ( * select create_by u, 't' t, count(*) c from projects_tasks where tenant_id = 0 group by 1, 2 * union all * select create_by u, 'm' t, count(*) c from projects_milestones where tenant_id = 0 group by 1, 2 * union all * select create_by u, 'd' t, count(*) c from projects_messages where tenant_id = 0 group by 1, 2) s * group by 1 */ var where = Exp.Eq("tenant_id", Tenant) & Exp.Between("create_on", filter.GetFromDate(true), filter.GetToDate(true)); if (filter.HasUserId) { where &= Exp.In("create_by", filter.GetUserIds()); } var subq = new SqlQuery(TasksTable).Select("create_by u").Select("'t'").Select("count(*) c").Where(where).GroupBy(1, 2) .UnionAll(new SqlQuery(MilestonesTable).Select("create_by u").Select("'m'").Select("count(*) c").Where(where).GroupBy(1, 2)) .UnionAll(new SqlQuery(MessagesTable).Select("create_by u").Select("'d'").Select("count(*) c").Where(where).GroupBy(1, 2)); var q = new SqlQuery() .Select("u") .SelectSum("case t when 't' then c else 0 end") .SelectSum("case t when 'm' then c else 0 end") .SelectSum("case t when 'd' then c else 0 end") .From(subq, "s") .GroupBy(1); using (var db = new DbManager(DatabaseId)) { return(db.ExecuteList(q) .Select(r => new { UserId = new Guid((string)r[0]), Tasks = Convert.ToInt32(r[1]), Milestones = Convert.ToInt32(r[2]), Messages = Convert.ToInt32(r[3]) }) .Select(r => new object[] { r.UserId, r.Tasks, r.Milestones, r.Messages, 0, r.Tasks + r.Milestones + r.Messages }) .ToList()); } }