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 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 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 void TestDates()
{
CoreContext.TenantManager.SetCurrentTenant(0);
var filter = new ReportFilter();
filter.TimeInterval = ReportTimeInterval.NextWeek;
var d = filter.GetFromDate();
d = filter.GetToDate();
}
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 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] }));
}
private String ParseFilter(ReportType repType, ReportFilter filter)
{
var sb = new StringBuilder();
var filterData = new StringBuilder();
var row = "<tr><td class='filterRowTitle'>{0}:</td><td>{1}</td></tr>";
var desc = string.Empty;
var statuses = string.Empty;
filterData.Append("<table cellspacing='0' cellpadding='0' class='filterTable'>");
switch (repType)
{
case ReportType.MilestonesExpired:
desc = String.Format(ReportResource.ReportLateMilestones_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, ProjectResource.Tags, (filter.ProjectTag != string.Empty && filter.ProjectTag != null) ? filter.ProjectTag.HtmlEncode() : ProjectsCommonResource.All);
filterData.AppendFormat(row, ProjectResource.Project, (filter.ProjectIds != null && filter.ProjectIds.Count == 1) ? Global.EngineFactory.GetProjectEngine().GetByID(filter.ProjectIds[0]).Title.HtmlEncode() : ProjectResource.AllProjects);
break;
case ReportType.MilestonesNearest:
desc = String.Format(ReportResource.ReportUpcomingMilestones_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, ProjectResource.Tags, (filter.ProjectTag != string.Empty && filter.ProjectTag != null) ? filter.ProjectTag.HtmlEncode() : ProjectsCommonResource.All);
filterData.AppendFormat(row, ProjectResource.Project, (filter.ProjectIds != null && filter.ProjectIds.Count == 1) ? Global.EngineFactory.GetProjectEngine().GetByID(filter.ProjectIds[0]).Title.HtmlEncode() : ProjectResource.AllProjects);
filterData.AppendFormat(row, ReportResource.ChooseTimeInterval, filter.GetFromDate(false).ToString(DateTimeExtension.DateFormatPattern) + " - " + filter.GetToDate(false).ToString(DateTimeExtension.DateFormatPattern));
break;
case ReportType.ProjectsList:
desc = String.Format(ReportResource.ReportProjectList_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, CustomNamingPeople.Substitute <ReportResource>("Department").HtmlEncode(), filter.DepartmentId != Guid.Empty ? CoreContext.GroupManager.GetGroupInfo(filter.DepartmentId).Name.HtmlEncode() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllDepartments"));
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
if (filter.ProjectStatuses.Count > 0)
{
for (int i = 0; i < filter.ProjectStatuses.Count; i++)
{
statuses += ResourceEnumConverter.ConvertToString(filter.ProjectStatuses[i]);
statuses += i < filter.TaskStatuses.Count - 1 ? ", " : string.Empty;
}
}
else
{
statuses += ResourceEnumConverter.ConvertToString(ProjectStatus.Open) + ", ";
statuses += ResourceEnumConverter.ConvertToString(ProjectStatus.Closed);
}
filterData.AppendFormat(row, ReportResource.ProjectsStatus, statuses);
break;
case ReportType.ProjectsWithoutActiveMilestones:
desc = String.Format(ReportResource.ReportProjectsWithoutActiveMilestones_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, CustomNamingPeople.Substitute <ReportResource>("Department").HtmlEncode(), filter.DepartmentId != Guid.Empty ? CoreContext.GroupManager.GetGroupInfo(filter.DepartmentId).Name.HtmlEncode() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllDepartments"));
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
break;
case ReportType.ProjectsWithoutActiveTasks:
desc = String.Format(ReportResource.ReportProjectsWithoutActiveTasks_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, CustomNamingPeople.Substitute <ReportResource>("Department").HtmlEncode(), filter.DepartmentId != Guid.Empty ? CoreContext.GroupManager.GetGroupInfo(filter.DepartmentId).Name.HtmlEncode() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllDepartments"));
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
break;
case ReportType.TasksByProjects:
desc = String.Format(ReportResource.ReportTaskList_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, ProjectResource.Tags, (filter.ProjectTag != string.Empty && filter.ProjectTag != null) ? filter.ProjectTag.HtmlEncode() : ProjectsCommonResource.All);
filterData.AppendFormat(row, ProjectResource.Project, (filter.ProjectIds != null && filter.ProjectIds.Count == 1) ? Global.EngineFactory.GetProjectEngine().GetByID(filter.ProjectIds[0]).Title.HtmlEncode() : ProjectResource.AllProjects);
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
for (int i = 0; i < filter.TaskStatuses.Count; i++)
{
statuses += ResourceEnumConverter.ConvertToString(filter.TaskStatuses[i]);
statuses += i < filter.TaskStatuses.Count - 1 ? ", " : string.Empty;
}
filterData.AppendFormat(row, ReportResource.TasksStatus, statuses);
if (filter.ToDate != DateTime.MinValue && filter.ToDate != DateTime.MaxValue)
{
filterData.AppendFormat(row, TaskResource.DeadLine, filter.ToDate.ToString(DateTimeExtension.DateFormatPattern));
}
break;
case ReportType.TasksByUsers:
desc = String.Format(ReportResource.ReportUserTasks_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, CustomNamingPeople.Substitute <ReportResource>("Department").HtmlEncode(), filter.DepartmentId != Guid.Empty ? CoreContext.GroupManager.GetGroupInfo(filter.DepartmentId).Name.HtmlEncode() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllDepartments"));
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
for (int i = 0; i < filter.TaskStatuses.Count; i++)
{
statuses += ResourceEnumConverter.ConvertToString(filter.TaskStatuses[i]);
statuses += i < filter.TaskStatuses.Count - 1 ? ", " : string.Empty;
}
filterData.AppendFormat(row, ReportResource.TasksStatus, statuses);
if (filter.ToDate != DateTime.MinValue && filter.ToDate != DateTime.MaxValue)
{
filterData.AppendFormat(row, TaskResource.DeadLine, filter.ToDate.ToString(DateTimeExtension.DateFormatPattern));
}
break;
case ReportType.TasksExpired:
desc = String.Format(ReportResource.ReportLateTasks_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, ProjectResource.Tags, (filter.ProjectTag != string.Empty && filter.ProjectTag != null) ? filter.ProjectTag.HtmlEncode() : ProjectsCommonResource.All);
filterData.AppendFormat(row, ProjectResource.Project, (filter.ProjectIds != null && filter.ProjectIds.Count == 1) ? Global.EngineFactory.GetProjectEngine().GetByID(filter.ProjectIds[0]).Title.HtmlEncode() : ProjectResource.AllProjects);
break;
case ReportType.TimeSpend:
desc = String.Format(ReportResource.ReportTimeSpendSummary_Description, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, CustomNamingPeople.Substitute <ReportResource>("Department").HtmlEncode(), filter.DepartmentId != Guid.Empty ? CoreContext.GroupManager.GetGroupInfo(filter.DepartmentId).Name.HtmlEncode() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllDepartments"));
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
if (filter.TimeInterval != ReportTimeInterval.Absolute || (filter.TimeInterval != ReportTimeInterval.Absolute && filter.ToDate != DateTime.MaxValue))
{
filterData.AppendFormat(row, ReportResource.ChooseTimeInterval, filter.GetFromDate(false).ToString(DateTimeExtension.DateFormatPattern) + " - " + filter.GetToDate(false).ToString(DateTimeExtension.DateFormatPattern));
}
filterData.AppendFormat(row, ProjectResource.Project, (filter.ProjectIds != null && filter.ProjectIds.Count == 1) ? Global.EngineFactory.GetProjectEngine().GetByID(filter.ProjectIds[0]).Title.HtmlEncode() : ProjectResource.AllProjects);
break;
case ReportType.UsersActivity:
desc = String.Format(ReportResource.ReportUserActivity_Descripton, "<ul>", "</ul>", "<li>", "</li>");
filterData.AppendFormat(row, CustomNamingPeople.Substitute <ReportResource>("Department").HtmlEncode(), filter.DepartmentId != Guid.Empty ? CoreContext.GroupManager.GetGroupInfo(filter.DepartmentId).Name.HtmlEncode() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllDepartments"));
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
if (filter.TimeInterval != ReportTimeInterval.Absolute || (filter.TimeInterval != ReportTimeInterval.Absolute && filter.ToDate != DateTime.MaxValue))
{
filterData.AppendFormat(row, ReportResource.ChooseTimeInterval, filter.GetFromDate(false).ToString(DateTimeExtension.DateFormatPattern) + " - " + filter.GetToDate(false).ToString(DateTimeExtension.DateFormatPattern));
}
break;
case ReportType.UsersWithoutActiveTasks:
desc = String.Format(ReportResource.ReportEmployeesWithoutActiveTasks_Description, "<ul>", "</ul>", "<li>", "</li>");
if (filter.ViewType == 0)
{
filterData.AppendFormat(row, CustomNamingPeople.Substitute <ReportResource>("Department").HtmlEncode(), filter.DepartmentId != Guid.Empty ? CoreContext.GroupManager.GetGroupInfo(filter.DepartmentId).Name.HtmlEncode() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllDepartments"));
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
}
if (filter.ViewType == 1)
{
filterData.AppendFormat(row, ProjectResource.Tags, (filter.ProjectTag != string.Empty && filter.ProjectTag != null) ? filter.ProjectTag.HtmlEncode() : ProjectsCommonResource.All);
filterData.AppendFormat(row, ProjectResource.Project, (filter.ProjectIds != null && filter.ProjectIds.Count == 1) ? Global.EngineFactory.GetProjectEngine().GetByID(filter.ProjectIds[0]).Title.HtmlEncode() : ProjectResource.AllProjects);
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
}
break;
case ReportType.UsersWorkload:
desc = String.Format(ReportResource.ReportEmployment_Description, "<ul>", "</ul>", "<li>", "</li>");
if (filter.ViewType == 0)
{
filterData.AppendFormat(row, CustomNamingPeople.Substitute <ReportResource>("Department").HtmlEncode(), filter.DepartmentId != Guid.Empty ? CoreContext.GroupManager.GetGroupInfo(filter.DepartmentId).Name.HtmlEncode() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllDepartments"));
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
}
if (filter.ViewType == 1)
{
filterData.AppendFormat(row, ProjectResource.Tags, (filter.ProjectTag != string.Empty && filter.ProjectTag != null) ? filter.ProjectTag.HtmlEncode() : ProjectsCommonResource.All);
filterData.AppendFormat(row, ProjectResource.Project, (filter.ProjectIds != null && filter.ProjectIds.Count == 1) ? Global.EngineFactory.GetProjectEngine().GetByID(filter.ProjectIds[0]).Title.HtmlEncode() : ProjectResource.AllProjects);
filterData.AppendFormat(row, ReportResource.User, filter.UserId != Guid.Empty ? CoreContext.UserManager.GetUsers(filter.UserId).DisplayUserName() : CustomNamingPeople.Substitute <ProjectsCommonResource>("AllUsers").HtmlEncode());
}
break;
}
filterData.AppendFormat(row, ReportResource.GenerationDate, TenantUtil.DateTimeNow().ToString(DateTimeExtension.ShortDatePattern));
filterData.Append("</table>");
sb.AppendFormat("<div id='faux'><div id='leftcolumn'>{0}</div><div id='rightcolumn'><table cellspacing='0' cellpadding='0'><tr><td valign='top'><b>{1}:</b></td><td>{2}</td></tr></table></div><div class='clear'></div></div>",
filterData.ToString(), ProjectResource.ProjectDescription, desc);
return(sb.ToString());
}