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 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 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 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)); }
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 override IEnumerable <object[]> BuildReport(TaskFilter filter) { filter.FromDate = filter.GetFromDate(true); filter.ToDate = filter.GetToDate(true); var taskTime = Global.EngineFactory.GetTimeTrackingEngine().GetByFilter(filter).Select(r => new object[] { r.Person, r.Task.Project.ID, r.Task.Project.Title, r.Task.ID, r.Task.Title, r.Hours, 0, r.PaymentStatus }); if (filter.ViewType == 0) { taskTime = taskTime.GroupBy(r => (Guid)r[0], (a, b) => { var enumerable = b as IList <object[]> ?? b.ToList(); var data = (object[])enumerable.First().Clone(); data[5] = enumerable.Sum(c => (float)c[5]); data[6] = enumerable.Where(r => (PaymentStatus)r[7] == PaymentStatus.Billed).Sum(c => (float)c[5]); return(data); }); } if (filter.ViewType == 1) { taskTime = taskTime.Select(r => { if ((PaymentStatus)r[7] == PaymentStatus.Billed) { r[6] = r[5]; } return(r); }); } taskTime = AddUserInfo(taskTime, 0); taskTime = taskTime.OrderBy(r => CoreContext.UserManager.GetUsers((Guid)r[0]), UserInfoComparer.Default); return(taskTime); }
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 List <Tuple <Guid, int> > GetByFilterAverageTime(TaskFilter filter, bool isAdmin, bool checkAccess) { var query = new SqlQuery(ProjectsTable + " p") .Select("ROUND(AVG(TIME_TO_SEC(TIMEDIFF(p.status_changed, p.create_on))/60/60))", "pp.participant_id") .InnerJoin(ParticipantTable + " pp", Exp.EqColumns("p.tenant_id", "pp.tenant") & Exp.EqColumns("p.id", "pp.project_id")) .Where("p.tenant_id", Tenant) .Where(Exp.Eq("p.status", 1)); if (filter.GetFromDate() != DateTime.MinValue && filter.GetToDate() != DateTime.MaxValue) { query.Where(Exp.Between("p.create_on", filter.GetFromDate(), filter.GetToDate())); query.Where(Exp.Between("p.status_changed", filter.GetFromDate(), filter.GetToDate())); } query.GroupBy("pp.participant_id"); query = CreateQueryFilter(query, filter, isAdmin, checkAccess); return(Db.ExecuteList(query).ConvertAll(a => new Tuple <Guid, int>(Guid.Parse((string)a[1]), Convert.ToInt32(a[0])))); }
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()); } }
public override IEnumerable <object[]> BuildDocbuilderReport(TaskFilter filter) { filter.FromDate = filter.GetFromDate(true); filter.ToDate = filter.GetToDate(true); using (var scope = DIHelper.Resolve()) { var factory = scope.Resolve <EngineFactory>(); IEnumerable <object[]> taskTime = new List <object[]>(); switch (filter.ViewType) { case 0: taskTime = factory.TimeTrackingEngine.GetByFilter(filter) .Select(r => new object[] { CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false), r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : "" }); taskTime = taskTime.GroupBy(r => (string)r[0], (a, b) => { var enumerable = b as IList <object[]> ?? b.ToList(); var data = (object[])enumerable.First().Clone(); data[1] = enumerable.Sum(c => (float)c[1]); data[2] = enumerable.Where(r => (PaymentStatus)r[3] == PaymentStatus.Billed).Sum(c => (float)c[1]); return(data); }); return(taskTime.OrderBy(r => (string)r[0])); case 1: taskTime = factory.TimeTrackingEngine.GetByFilter(filter) .Select(r => new object[] { CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false), r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : "", r.Task.Project, r.Task.Title }); taskTime = taskTime.Select(r => { if ((PaymentStatus)r[3] == PaymentStatus.Billed) { r[2] = r[1]; } return(r); }); var users = taskTime.GroupBy(x => x[0]).Select(x => new object[] { x.Key, x.ToList() .Select(r => new object[] { r[1], r[2], r[3], r[4], r[5], r[6] }) }); var result = new List <object[]>(); foreach (var user in users) // user = [string, []] { var userTasks = (IEnumerable <object[]>)user[1]; var groupedUserTasks = userTasks.GroupBy(x => x[4]).Select(r => new object[] { new object[] { ((Project)r.Key).Title, ProjectsCommonResource.Status + ": " + LocalizedEnumConverter.ConvertToString(((Project)r.Key).Status), ProjectResource.ProjectLeader + ": " + CoreContext.UserManager.GetUsers(((Project)r.Key).Responsible).DisplayUserName(false), TaskResource.CreatingDate + ": " + ((Project)r.Key).CreateOn.ToString("d"), ((Project)r.Key).Description != "" ? ProjectsCommonResource.Description + ": " + ((Project)r.Key).Description : "" }, r.ToList().Select(y => new object[] { y[0], y[1], y[2], y[3], y[5] }) }); result.Add(new object[] { user[0], groupedUserTasks }); } return(result); case 2: taskTime = factory.TimeTrackingEngine.GetByFilter(filter) .Select(r => new object[] { CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false), r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : "", r.Task.Project.Title }); return(taskTime.GroupBy(x => (string)x[5]).Select(x => new object[] { x.Key, x.GroupBy(r => (string)r[0], (a, b) => { var enumerable = b as IList <object[]> ?? b.ToList(); var data = (object[])enumerable.First().Clone(); data[1] = enumerable.Sum(c => (float)c[1]); data[2] = enumerable.Where(r => (PaymentStatus)r[3] == PaymentStatus.Billed).Sum(c => (float)c[1]); return data; }) }).OrderBy(x => (string)x[0])); default: throw new Exception(ProjectsCommonResource.NoData); } } }