public void ReportFilterTest() { var filter = new ReportFilter(); filter.DepartmentId = Guid.NewGuid(); filter.FromDate = DateTime.Now.Date; filter.ToDate = DateTime.Now.Date; filter.MilestoneStatuses.Add(MilestoneStatus.Open); filter.MilestoneStatuses.Add(MilestoneStatus.Closed); filter.ProjectIds.Add(1); filter.ProjectIds.Add(3); filter.ProjectStatuses.Add(ProjectStatus.Open); filter.ProjectStatuses.Add(ProjectStatus.Closed); filter.ProjectTag = "tag"; filter.TaskStatuses.Add(TaskStatus.Open); filter.TaskStatuses.Add(TaskStatus.Closed); filter.TimeInterval = ReportTimeInterval.Absolute; filter.UserId = Guid.NewGuid(); filter.ViewType = 4; var xml = filter.ToXml(); var uri = filter.ToUri(); AreEquals(filter, ReportFilter.FromXml(xml)); AreEquals(filter, ReportFilter.FromUri(uri)); filter.TimeInterval = ReportTimeInterval.CurrMonth; xml = filter.ToXml(); uri = filter.ToUri(); AreEquals(filter, ReportFilter.FromXml(xml)); AreEquals(filter, ReportFilter.FromUri(uri)); }
public IList<object[]> BuildMilestonesReport(ReportFilter filter) { IntersectTagsAndProjects(filter); var query = new SqlQuery() .From("projects_projects p") .InnerJoin("projects_milestones m", Exp.EqColumns("p.id", "m.project_id") & Exp.EqColumns("m.tenant_id", "p.tenant_id")) .Select("p.id", "p.title", "m.id", "m.title", "m.deadline", "m.status") .Where("m.status", MilestoneStatus.Open) .Where("p.tenant_id", Tenant) .OrderBy("p.title", true) .OrderBy("m.deadline", false); if (filter.HasProjectIds) { query.Where(Exp.In("p.id", filter.ProjectIds)); } if (filter.GetFromDate() != DateTime.MinValue) { query.Where(Exp.Ge("m.deadline", filter.GetFromDate())); } if (filter.GetToDate() != DateTime.MaxValue) { query.Where(Exp.Le("m.deadline", filter.GetToDate())); } return DbManager .ExecuteList(query) .ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1], Convert.ToInt32(r[2]), (string)r[3], ((DateTime)r[4]).ToString("d"), ToString((MilestoneStatus)Convert.ToInt32(r[5])) }); }
public void TestDates() { CoreContext.TenantManager.SetCurrentTenant(0); var filter = new ReportFilter(); filter.TimeInterval = ReportTimeInterval.NextWeek; var d = filter.GetFromDate(); d = filter.GetToDate(); }
public string GetReportUri(bool onlyOpenTasks, Guid userID) { var filter = new ReportFilter(); filter.TimeInterval = ReportTimeInterval.Absolute; filter.UserId = userID; filter.MilestoneStatuses.Add(MilestoneStatus.Open); filter.MilestoneStatuses.Add(MilestoneStatus.Closed); filter.TaskStatuses.Add(onlyOpenTasks ? TaskStatus.Open : TaskStatus.Closed); return string.Format("reports.aspx?action=generate&reportType=10&{0}", filter.ToUri()); }
private void AreEquals(ReportFilter f1, ReportFilter f2) { Assert.AreEqual(f1.TimeInterval, f2.TimeInterval); if (f1.TimeInterval == ReportTimeInterval.Absolute) { Assert.AreEqual(f1.FromDate, f2.FromDate); Assert.AreEqual(f1.ToDate, f2.ToDate); } Assert.AreEqual(f1.DepartmentId, f2.DepartmentId); Assert.AreEqual(f1.UserId, f2.UserId); Assert.AreEqual(f1.ViewType, f2.ViewType); Assert.AreEqual(f1.ProjectTag, f2.ProjectTag); CollectionAssert.AreEquivalent(f1.MilestoneStatuses, f2.MilestoneStatuses); CollectionAssert.AreEquivalent(f1.ProjectIds, f2.ProjectIds); CollectionAssert.AreEquivalent(f1.ProjectStatuses, f2.ProjectStatuses); CollectionAssert.AreEquivalent(f1.TaskStatuses, f2.TaskStatuses); }
public IList<object[]> BuildReport(ReportType type, ReportFilter filter) { filter = (ReportFilter)filter.Clone(); switch (type) { case ReportType.MilestonesExpired: case ReportType.MilestonesNearest: return BuildMilestonesReport(filter); case ReportType.UsersWithoutActiveTasks: return BuildUsersWithoutActiveTasks(filter); case ReportType.ProjectsWithoutActiveMilestones: return BuildProjectsWithoutActiveMilestones(filter); case ReportType.ProjectsWithoutActiveTasks: return BuildProjectsWithoutActiveTasks(filter); case ReportType.UsersActivity: throw new NotImplementedException(); case ReportType.UsersWorkload: return BuildUsersWorkload(filter); case ReportType.ProjectsList: return BuildProjectsListReport(filter); case ReportType.TasksByUsers: case ReportType.TasksByProjects: case ReportType.TasksExpired: return BuildTasksListReport(filter); case ReportType.TimeSpend: return BuildTimeSpendReport(filter); default: throw new ArgumentOutOfRangeException("reportType"); } }
public static ReportFilter FromXml(string xml) { if (string.IsNullOrEmpty(xml)) { throw new ArgumentNullException("xml"); } var filter = new ReportFilter(); var root = XDocument.Parse(xml).Element("filter"); var date = root.Element("date"); if (date != null) { var attribute = date.Attribute("from"); if (attribute != null) { filter.FromDate = DateTime.ParseExact(attribute.Value, "yyyyMMdd", null); } attribute = date.Attribute("to"); if (attribute != null) { filter.ToDate = DateTime.ParseExact(attribute.Value, "yyyyMMdd", null); } attribute = date.Attribute("interval"); if (attribute != null) { filter.TimeInterval = (ReportTimeInterval)Enum.Parse(typeof(ReportTimeInterval), attribute.Value, true); } } var projects = root.Element("projects"); if (projects != null) { foreach (var id in projects.Elements("id")) { filter.ProjectIds.Add(int.Parse(id.Value)); } foreach (var status in projects.Elements("status")) { filter.ProjectStatuses.Add((ProjectStatus)int.Parse(status.Value)); } foreach (var tag in projects.Elements("tag")) { filter.ProjectTag = tag.Value; } } var tasks = root.Element("tasks"); if (tasks != null) { foreach (var status in tasks.Elements("status")) { filter.TaskStatuses.Add((TaskStatus)int.Parse(status.Value)); } } var users = root.Element("users"); if (users != null) { if (users.Attribute("dep") != null) { filter.DepartmentId = new Guid(users.Attribute("dep").Value); } foreach (var id in users.Elements("id")) { filter.UserId = new Guid(id.Value); } } var milestones = root.Element("milestones"); if (milestones != null) { foreach (var status in milestones.Elements("status")) { filter.MilestoneStatuses.Add((MilestoneStatus)int.Parse(status.Value)); } } var view = root.Attribute("view"); if (view != null) { filter.ViewType = int.Parse(view.Value); } var noResponsible = root.Attribute("noResponsible"); if (noResponsible != null) { filter.NoResponsible = bool.Parse(noResponsible.Value); } return(filter); }
public IList<object[]> BuildProjectsListReport(ReportFilter filter) { filter = (ReportFilter)filter.Clone(); return SelectOnlyCanReadProjects(reportDao.BuildProjectListReport(filter)); }
public static ReportFilter FromUri(string uri) { if (string.IsNullOrEmpty(uri)) throw new ArgumentNullException("uri"); var filter = new ReportFilter(); var p = GetParameterFromUri(uri, "ftime"); if (!string.IsNullOrEmpty(p)) filter.TimeInterval = (ReportTimeInterval)Enum.Parse(typeof(ReportTimeInterval), p, true); p = GetParameterFromUri(uri, "ffrom"); if (!string.IsNullOrEmpty(p)) filter.FromDate = DateTime.ParseExact(p, "yyyyMMdd", null); p = GetParameterFromUri(uri, "fto"); if (!string.IsNullOrEmpty(p)) filter.ToDate = DateTime.ParseExact(p, "yyyyMMdd", null); p = GetParameterFromUri(uri, "fu"); if (!string.IsNullOrEmpty(p)) filter.UserId = new Guid(p); p = GetParameterFromUri(uri, "fd"); if (!string.IsNullOrEmpty(p)) filter.DepartmentId = new Guid(p); p = GetParameterFromUri(uri, "fpid"); if (!string.IsNullOrEmpty(p)) filter.ProjectIds = Split(p).Select(v => int.Parse(v)).ToList(); p = GetParameterFromUri(uri, "fps"); if (!string.IsNullOrEmpty(p)) filter.ProjectStatuses = Split(p).Select(v => (ProjectStatus)Enum.Parse(typeof(ProjectStatus), v, true)).ToList(); p = GetParameterFromUri(uri, "fpt"); if (!string.IsNullOrEmpty(p)) filter.ProjectTag = p; p = GetParameterFromUri(uri, "fms"); if (!string.IsNullOrEmpty(p)) filter.MilestoneStatuses = Split(p).Select(v => (MilestoneStatus)Enum.Parse(typeof(MilestoneStatus), v, true)).ToList(); p = GetParameterFromUri(uri, "fts"); if (!string.IsNullOrEmpty(p)) filter.TaskStatuses = Split(p).Select(v => (TaskStatus)Enum.Parse(typeof(TaskStatus), v, true)).ToList(); p = GetParameterFromUri(uri, "fv"); if (!string.IsNullOrEmpty(p)) filter.ViewType = int.Parse(p); p = GetParameterFromUri(uri, "nores"); if (!string.IsNullOrEmpty(p)) filter.NoResponsible = bool.Parse(p); return filter; }
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; } }
private IList<object[]> BuildTimeSpendReport(ReportFilter filter) { var data = SelectOnlyCanReadTimeSpend(reportDao.BuildTimeReport(filter)).ToList(); if(filter.ViewType == 0) { data = data.GroupBy(r => r[0]).Select(r => new[] { r.Key, r.Sum(item => (double)item[4]) }).ToList(); return data.ConvertAll(r => new object[] { new Guid((string)r[0]), 0, 0, "", ToTimeSpend((double)r[1]) }); } return data.ConvertAll(r => new object[] {new Guid((string)r[0]), Convert.ToInt32(r[1]), Convert.ToInt32(r[2]),(string) r[3], ToTimeSpend((double)r[4])}); }
private IList<object[]> BuildProjectsWithoutActiveTasks(ReportFilter filter) { return SelectOnlyCanReadProjects(reportDao.BuildProjectWithoutActiveTask(filter)); }
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) }); }
private IList<object[]> BuildUsersWorkload(ReportFilter filter) { return reportDao.BuildUsersStatisticsReport(filter); }
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[]> 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 string GetReportUri() { var filter = new ReportFilter { TimeInterval = ReportTimeInterval.Absolute, UserId = SecurityContext.CurrentAccount.ID }; filter.ProjectStatuses.Add(ProjectStatus.Open); return string.Format("reports.aspx?action=generate&reportType=7&{0}", filter.ToUri()); }
private static IList<object[]> BuildUserActivityReport(ReportFilter filter) { var result = new List<object[]>(); var users = new List<UserInfo>(); if (filter.UserId != Guid.Empty) { users.Add(CoreContext.UserManager.GetUsers(filter.UserId)); } else if (filter.DepartmentId != Guid.Empty) { users.AddRange(CoreContext.UserManager.GetUsersByGroup(filter.DepartmentId)); } else { users.AddRange(CoreContext.UserManager.GetUsers()); } foreach (var u in users.OrderBy(u => u, UserInfoComparer.Default)) { var tasks = 0; var milestones = 0; var discussions = 0; var files = 0; var pid = ProductEntryPoint.ID; var fromDate = filter.GetFromDate(true); var toDate = filter.GetToDate(true); UserActivityManager.GetUserActivities( TenantProvider.CurrentTenantID, u.ID, pid, new[] { pid }, UserActivityConstants.AllActionType, null, fromDate, toDate) .ForEach(a => { var data = a.AdditionalData.Split(new[] { '|' })[0]; if (a.ActionType == UserActivityConstants.ContentActionType && data == EntityType.Task.ToString()) tasks++; if (a.ActionType == UserActivityConstants.ContentActionType && data == EntityType.Milestone.ToString()) milestones++; if (a.ActionType == UserActivityConstants.ContentActionType && data == EntityType.Message.ToString()) discussions++; if (a.ActionType == UserActivityConstants.ActivityActionType && data == EntityType.File.ToString()) files++; }); result.Add(new object[] { u.ID, tasks, milestones, discussions, files, tasks + milestones + discussions + files }); } return result; }
public static string BuildReport(ReportType reportType, ReportFilter filter, ReportViewType viewType) { //prepare filter if (reportType == ReportType.MilestonesExpired) { filter.ToDate = TenantUtil.DateTimeNow(); } if (reportType == ReportType.TasksExpired) { filter.ToDate = TenantUtil.DateTimeNow(); filter.TaskStatuses.Add(TaskStatus.Open); } //exec IList<object[]> result = null; if (reportType == ReportType.UsersActivity) { result = BuildUserActivityReport(filter); } else { result = Global.EngineFactory.GetReportEngine().BuildReport(reportType, filter); if (reportType == ReportType.TasksExpired) { var tmp = new List<object[]>(); foreach (var row in result) if (row[10] != null)//task has due date tmp.Add(row); result = tmp; } } if (result == null || result.Count == 0) { return null; } //add user info switch (reportType) { case ReportType.UsersWithoutActiveTasks: case ReportType.UsersWorkload: case ReportType.TimeSpend: case ReportType.UsersActivity: result = AddUserInfo(result, 0); result = result .OrderBy(r => CoreContext.UserManager.GetUsers((Guid)r[0]), UserInfoComparer.Default) .ToList(); break; case ReportType.ProjectsWithoutActiveMilestones: case ReportType.ProjectsWithoutActiveTasks: case ReportType.ProjectsList: result = AddUserInfo(result, 2); result = result .OrderBy(r => (string)r[1]) .ToList(); break; case ReportType.TasksByProjects: case ReportType.TasksByUsers: case ReportType.TasksExpired: result = AddUserInfo(result, 8); result = AddStatusCssClass(result); break; } return ReportTransformer.Transform(result, reportType, filter.ViewType, viewType); }
private IList<object[]> BuildMilestonesReport(ReportFilter filter) { return SelectOnlyCanReadMilestones(reportDao.BuildMilestonesReport(filter)); }
public static string ToUri(ReportFilter filter) { var uri = new StringBuilder(); uri.AppendFormat("&ftime={0}", filter.TimeInterval); if (HasDate(filter.FromDate)) { uri.AppendFormat("&ffrom={0}", filter.FromDate.ToString("yyyyMMdd")); } if (HasDate(filter.ToDate)) { uri.AppendFormat("&fto={0}", filter.ToDate.ToString("yyyyMMdd")); } if (filter.HasProjectIds) { uri.AppendFormat("&fpid={0}", string.Join(LIST_SEP, filter.ProjectIds.Select(id => id.ToString()).ToArray())); } if (filter.HasProjectStatuses) { uri.AppendFormat("&fps={0}", string.Join(LIST_SEP, filter.ProjectStatuses.Select(s => s.ToString()).ToArray())); } if (!string.IsNullOrEmpty(filter.ProjectTag)) { uri.AppendFormat("&fpt={0}", HttpUtility.UrlEncode(filter.ProjectTag)); } if (filter.UserId != default(Guid)) { uri.AppendFormat("&fu={0:N}", filter.UserId); } if (filter.DepartmentId != default(Guid)) { uri.AppendFormat("&fd={0:N}", filter.DepartmentId); } if (filter.HasMilestoneStatuses) { uri.AppendFormat("&fms={0}", string.Join(LIST_SEP, filter.MilestoneStatuses.Select(s => s.ToString()).ToArray())); } if (filter.HasTaskStatuses) { uri.AppendFormat("&fts={0}", string.Join(LIST_SEP, filter.TaskStatuses.Select(s => s.ToString()).ToArray())); } if (filter.ViewType != 0) { uri.AppendFormat("&fv={0}", filter.ViewType); } if (filter.NoResponsible) { uri.AppendFormat("&noRes={0}", filter.NoResponsible); } return uri.ToString().Trim('&').ToLower(); }
private IList<object[]> BuildUsersWithoutActiveTasks(ReportFilter filter) { var result = new List<object[]>(); var users = new List<Guid>(); if (filter.UserId != Guid.Empty) users.Add(filter.UserId); else if (filter.DepartmentId != Guid.Empty) users.AddRange(CoreContext.UserManager.GetUsersByGroup(filter.DepartmentId).Select(u => u.ID)); else if (!filter.HasProjectIds) users.AddRange(CoreContext.UserManager.GetUsers().Select(u => u.ID)); else filter.ProjectIds.ForEach(id => users.AddRange(projectDao.GetTeam(id).Select(r=> r.ID))); foreach (object[] row in reportDao.BuildUsersStatisticsReport(filter)) { users.Remove((Guid)row[0]); if ((long)row[1] == 0 && (long)row[2] == 0) { result.Add(row); } } foreach (var u in users) { result.Add(new object[] { u, 0, 0, 0 }); } return result; }
public string GetActivityReportUri() { var filter = new ReportFilter {TimeInterval = ReportTimeInterval.Absolute, UserId = UserID}; return string.Format("{0}?action=generate&reportType=5&{1}", VirtualPathUtility.ToAbsolute(PathProvider.BaseVirtualPath + "reports.aspx"), filter.ToUri()); }
private IList<object[]> BuildTasksListReport(ReportFilter filter) { if (filter.TaskStatuses.Count == Enum.GetValues(typeof(TaskStatus)).Length) { filter.TaskStatuses.Clear(); } return SelectOnlyCanReadTasks(reportDao.BuildTaskListReport(filter)); }
public static ReportFilter FromUri(string uri) { if (string.IsNullOrEmpty(uri)) { throw new ArgumentNullException("uri"); } var filter = new ReportFilter(); var p = GetParameterFromUri(uri, "ftime"); if (!string.IsNullOrEmpty(p)) { filter.TimeInterval = (ReportTimeInterval)Enum.Parse(typeof(ReportTimeInterval), p, true); } p = GetParameterFromUri(uri, "ffrom"); if (!string.IsNullOrEmpty(p)) { filter.FromDate = DateTime.ParseExact(p, "yyyyMMdd", null); } p = GetParameterFromUri(uri, "fto"); if (!string.IsNullOrEmpty(p)) { filter.ToDate = DateTime.ParseExact(p, "yyyyMMdd", null); } p = GetParameterFromUri(uri, "fu"); if (!string.IsNullOrEmpty(p)) { filter.UserId = new Guid(p); } p = GetParameterFromUri(uri, "fd"); if (!string.IsNullOrEmpty(p)) { filter.DepartmentId = new Guid(p); } p = GetParameterFromUri(uri, "fpid"); if (!string.IsNullOrEmpty(p)) { filter.ProjectIds = Split(p).Select(v => int.Parse(v)).ToList(); } p = GetParameterFromUri(uri, "fps"); if (!string.IsNullOrEmpty(p)) { filter.ProjectStatuses = Split(p).Select(v => (ProjectStatus)Enum.Parse(typeof(ProjectStatus), v, true)).ToList(); } p = GetParameterFromUri(uri, "fpt"); if (!string.IsNullOrEmpty(p)) { filter.ProjectTag = p; } p = GetParameterFromUri(uri, "fms"); if (!string.IsNullOrEmpty(p)) { filter.MilestoneStatuses = Split(p).Select(v => (MilestoneStatus)Enum.Parse(typeof(MilestoneStatus), v, true)).ToList(); } p = GetParameterFromUri(uri, "fts"); if (!string.IsNullOrEmpty(p)) { filter.TaskStatuses = Split(p).Select(v => (TaskStatus)Enum.Parse(typeof(TaskStatus), v, true)).ToList(); } p = GetParameterFromUri(uri, "fv"); if (!string.IsNullOrEmpty(p)) { filter.ViewType = int.Parse(p); } p = GetParameterFromUri(uri, "nores"); if (!string.IsNullOrEmpty(p)) { filter.NoResponsible = bool.Parse(p); } return(filter); }
private IList<object[]> BuildProjectsWithoutActiveMilestones(ReportFilter filter) { return SelectOnlyCanReadProjects(reportDao.BuildProjectWithoutOpenMilestone(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; } }
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); }
public static ReportFilter FromXml(string xml) { if (string.IsNullOrEmpty(xml)) throw new ArgumentNullException("xml"); var filter = new ReportFilter(); var root = XDocument.Parse(xml).Element("filter"); var date = root.Element("date"); if (date != null) { var attribute = date.Attribute("from"); if (attribute != null) filter.FromDate = DateTime.ParseExact(attribute.Value, "yyyyMMdd", null); attribute = date.Attribute("to"); if (attribute != null) filter.ToDate = DateTime.ParseExact(attribute.Value, "yyyyMMdd", null); attribute = date.Attribute("interval"); if (attribute != null) filter.TimeInterval = (ReportTimeInterval)Enum.Parse(typeof(ReportTimeInterval), attribute.Value, true); } var projects = root.Element("projects"); if (projects != null) { foreach (var id in projects.Elements("id")) { filter.ProjectIds.Add(int.Parse(id.Value)); } foreach (var status in projects.Elements("status")) { filter.ProjectStatuses.Add((ProjectStatus)int.Parse(status.Value)); } foreach (var tag in projects.Elements("tag")) { filter.ProjectTag = tag.Value; } } var tasks = root.Element("tasks"); if (tasks != null) { foreach (var status in tasks.Elements("status")) { filter.TaskStatuses.Add((TaskStatus)int.Parse(status.Value)); } } var users = root.Element("users"); if (users != null) { if (users.Attribute("dep") != null) { filter.DepartmentId = new Guid(users.Attribute("dep").Value); } foreach (var id in users.Elements("id")) { filter.UserId = new Guid(id.Value); } } var milestones = root.Element("milestones"); if (milestones != null) { foreach (var status in milestones.Elements("status")) { filter.MilestoneStatuses.Add((MilestoneStatus)int.Parse(status.Value)); } } var view = root.Attribute("view"); if (view != null) { filter.ViewType = int.Parse(view.Value); } var noResponsible = root.Attribute("noResponsible"); if (noResponsible != null) { filter.NoResponsible = bool.Parse(noResponsible.Value); } return filter; }
public IList<object[]> BuildProjectListReport(ReportFilter filter) { IntersectUsersAndProjects(filter); IntersectTagsAndProjects(filter); var query = new SqlQuery("projects_projects p") .Select("p.id") .Select("p.title") .Select("p.responsible_id") .Select("p.status") .Select(new SqlQuery("projects_milestones m").SelectCount().Where(Exp.EqColumns("m.project_id", "p.id"))) .Select(new SqlQuery("projects_tasks t").SelectCount().Where(Exp.EqColumns("t.project_id", "p.id"))) .Select(new SqlQuery("projects_project_participant pp").SelectCount().Where(Exp.EqColumns("pp.project_id", "p.id") & Exp.Eq("removed", false))) .Select("p.private") .Where("p.tenant_id", Tenant) .OrderBy("p.title", true); if (filter.HasProjectStatuses) { query.Where(Exp.In("p.status", filter.ProjectStatuses)); } if (filter.HasProjectIds) { query.Where(Exp.In("p.id", filter.ProjectIds)); } return DbManager .ExecuteList(query) .ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1], ToGuid(r[2]), ToString((ProjectStatus)Convert.ToInt32(r[3])), (long)r[4], (long)r[5], (long)r[6], Convert.ToBoolean(r[7]), }); }
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); }
public string GetTasksReportUri() { var filter = new ReportFilter {TimeInterval = ReportTimeInterval.Absolute, UserId = UserID}; filter.TaskStatuses.Add(TaskStatus.Open); filter.TaskStatuses.Add(TaskStatus.Closed); return string.Format("{0}?action=generate&reportType=10&{1}", VirtualPathUtility.ToAbsolute(PathProvider.BaseVirtualPath + "reports.aspx"), filter.ToUri()); }
public static string ToXml(ReportFilter filter) { var doc = new XDocument(); var root = new XElement("filter"); var date = new XElement("date"); root.Add(date); date.Add(new XAttribute("interval", filter.TimeInterval)); if (HasDate(filter.FromDate)) { date.Add(new XAttribute("from", filter.FromDate.ToString("yyyyMMdd"))); } if (HasDate(filter.ToDate)) { date.Add(new XAttribute("to", filter.ToDate.ToString("yyyyMMdd"))); } if (filter.HasProjectIds || filter.HasProjectStatuses || !string.IsNullOrEmpty(filter.ProjectTag)) { var projects = new XElement("projects"); root.Add(projects); foreach (var id in filter.ProjectIds) { projects.Add(new XElement("id", id)); } foreach (var status in filter.ProjectStatuses) { projects.Add(new XElement("status", (int)status)); } if (!string.IsNullOrEmpty(filter.ProjectTag)) { projects.Add(new XElement("tag", filter.ProjectTag)); } } if (filter.HasUserId) { var users = new XElement("users"); root.Add(users); if (filter.DepartmentId != default(Guid)) { users.Add(new XAttribute("dep", filter.DepartmentId.ToString("N"))); } if (filter.UserId != default(Guid)) { users.Add(new XElement("id", filter.UserId.ToString("N"))); } } if (filter.HasMilestoneStatuses) { var milestone = new XElement("milestones"); root.Add(milestone); foreach (var status in filter.MilestoneStatuses) { milestone.Add(new XElement("status", (int)status)); } } if (filter.HasTaskStatuses) { var tasks = new XElement("tasks"); root.Add(tasks); foreach (var status in filter.TaskStatuses) { tasks.Add(new XElement("status", (int)status)); } } if (filter.ViewType != 0) { root.Add(new XAttribute("view", filter.ViewType)); } if (filter.NoResponsible) { root.Add(new XAttribute("noResponsible", filter.NoResponsible)); } doc.AddFirst(root); return(doc.ToString(SaveOptions.DisableFormatting)); }