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));
}
