public virtual List<Project> GetByFilter(TaskFilter filter)
{
var listProjects = new List<Project>();
while (true)
{
var projects = projectDao.GetByFilter(filter).Where(CanRead).ToList();
if (filter.LastId != 0)
{
var lastProjectIndex = projects.FindIndex(r => r.ID == filter.LastId);
if (lastProjectIndex >= 0)
{
projects = projects.SkipWhile((r, index) => index <= lastProjectIndex).ToList();
}
}
listProjects.AddRange(projects);
if (filter.Max <= 0 || filter.Max>150000) break;
listProjects = listProjects.Take((int) filter.Max).ToList();
if (listProjects.Count == filter.Max || projects.Count == 0) break;
if (listProjects.Count != 0)
filter.LastId = listProjects.Last().ID;
filter.Offset += filter.Max;
}
return listProjects;
}
public IList<object[]> BuildUsersWithoutActiveTasks(TaskFilter 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(projectDao.GetTeam(filter.ProjectIds).Select(r => r.ID));
}
else if (!filter.HasProjectIds)
{
users.AddRange(CoreContext.UserManager.GetUsers().Select(u => u.ID));
}
foreach (var row in reportDao.BuildUsersStatisticsReport(filter))
{
users.Remove((Guid)row[0]);
if ((long)row[1] == 0 && (long)row[2] == 0)
{
result.Add(row);
}
}
result.AddRange(users.Select(u => new object[] { u, 0, 0, 0 }));
return result;
}
public List<TimeSpend> GetByFilter(TaskFilter filter)
{
var listTimeSpend = new List<TimeSpend>();
while (true)
{
var timeSpend = _timeSpendDao.GetByFilter(filter);
timeSpend = GetTasks(timeSpend).FindAll(r => ProjectSecurity.CanRead(r.Task));
if (filter.LastId != 0)
{
var lastTimeSpendIndex = timeSpend.FindIndex(r => r.ID == filter.LastId);
if (lastTimeSpendIndex >= 0)
{
timeSpend = timeSpend.SkipWhile((r, index) => index <= lastTimeSpendIndex).ToList();
}
}
listTimeSpend.AddRange(timeSpend);
if (filter.Max <= 0 || filter.Max > 150000) break;
listTimeSpend = listTimeSpend.Take((int)filter.Max).ToList();
if (listTimeSpend.Count == filter.Max || timeSpend.Count == 0) break;
if (listTimeSpend.Count != 0)
filter.LastId = listTimeSpend.Last().ID;
filter.Offset += filter.Max;
}
return listTimeSpend;
}
public static string BuildReport(ReportType reportType, TaskFilter filter, ReportViewType viewType, int templateID)
{
//prepare filter
if (templateID != 0 && !filter.FromDate.Equals(DateTime.MinValue))
{
var interval = filter.ToDate.DayOfYear - filter.FromDate.DayOfYear;
switch (reportType)
{
case ReportType.TasksByUsers:
case ReportType.TasksByProjects:
{
filter.FromDate = TenantUtil.DateTimeNow().Date.AddDays(-interval);
filter.ToDate = TenantUtil.DateTimeNow().Date;
}
break;
case ReportType.MilestonesNearest:
{
filter.FromDate = TenantUtil.DateTimeNow().Date;
filter.ToDate = TenantUtil.DateTimeNow().Date.AddDays(interval);
}
break;
}
}
//exec
var report = Report.CreateNewReport(reportType, filter);
var data = report.BuildReport().ToList();
if (!data.Any())
report = Report.CreateNewReport(ReportType.EmptyReport, filter);
return ReportTransformer.Transform(data.ToList(), report, filter.ViewType, viewType, templateID);
}
public IEnumerable<MessageWrapper> GetMessageByFilter(int projectid, int tag, Guid departament, Guid participant,
ApiDateTime createdStart, ApiDateTime createdStop, int lastId,
bool myProjects, bool follow)
{
var messageEngine = EngineFactory.GetMessageEngine();
var taskFilter = new TaskFilter
{
DepartmentId = departament,
UserId = participant,
FromDate = createdStart,
ToDate = createdStop,
SortBy = _context.SortBy,
SortOrder = !_context.SortDescending,
SearchText = _context.FilterValue,
TagId = tag,
Offset = _context.StartIndex,
Max = _context.Count,
MyProjects = myProjects,
LastId = lastId,
Follow = follow
};
if (projectid != 0)
taskFilter.ProjectIds.Add(projectid);
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
_context.TotalCount = messageEngine.GetByFilterCount(taskFilter);
return messageEngine.GetByFilter(taskFilter).NotFoundIfNull().Select(r => new MessageWrapper(r)).ToSmartList();
}
public IEnumerable<ProjectWrapperFull> GetProjectsByFilter(int tag, ProjectStatus? status, Guid participant, Guid manager, bool follow, int lastId)
{
var filter = new TaskFilter
{
ParticipantId = participant,
UserId = manager,
SortBy = _context.SortBy,
SortOrder = !_context.SortDescending,
SearchText = _context.FilterValue,
TagId = tag,
Follow = follow,
LastId = lastId,
Offset = _context.StartIndex,
Max = _context.Count
};
if (status != null)
filter.ProjectStatuses.Add((ProjectStatus)status);
_context.SetDataFiltered();
_context.SetDataPaginated();
_context.SetDataSorted();
return EngineFactory.GetProjectEngine().GetByFilter(filter).NotFoundIfNull().Select(r => new ProjectWrapperFull(r)).ToSmartList();
}
protected void Page_Load(object sender, EventArgs e)
{
if (UserID == Guid.Empty)
UserID = SecurityContext.CurrentAccount.ID;
var filter = new TaskFilter { ParticipantId = UserID, MyProjects = true };
var tasks = Global.EngineFactory.GetTaskEngine().GetByFilter(filter);
var projects = Global.EngineFactory.GetProjectEngine().GetByParticipant(UserID).Select(prj => new ProjectVm(prj)).OrderBy(r=> r.ProjectTitle).ToList();
foreach (var project in projects)
{
project.OpenedTasksCount = tasks.Count(t => t.Project.ID == project.ProjectId && t.Status == TaskStatus.Open);
project.ClosedTasksCount = tasks.Count(t => t.Project.ID == project.ProjectId && t.Status == TaskStatus.Closed);
}
ProjectsRepeater.DataSource = projects;
ProjectsRepeater.DataBind();
//load user activity
//
var activities = UserActivityManager.GetUserActivities(
TenantProvider.CurrentTenantID,
UserID,
ProductEntryPoint.ID,
new[] { ProductEntryPoint.ID },
UserActivityConstants.AllActionType,
null,
0, 10);
LastActivityRepeater.DataSource = activities.ConvertAll(a => new ActivityVm(a));
LastActivityRepeater.DataBind();
}
public List<TimeSpend> GetByFilter(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var query = CreateQuery();
if (filter.Max != 0 && !filter.Max.Equals(int.MaxValue))
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["TimeSpend"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("t." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("t." + sort, sortColumns[sort]);
}
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(query).ConvertAll(ToTimeSpend);
}
}
public ReportTemplateWrapper SaveReportTemplate(string name, string period, int periodItem, int hour, bool autoGenerated, ReportType reportType,
int tag, int project, TaskStatus? status, Guid departament, Guid userId, ReportTimeInterval reportTimeInterval,
ApiDateTime fromDate, ApiDateTime toDate, int viewType, bool noResponsible)
{
ProjectSecurity.DemandAuthentication();
if (name == null || name.Trim().Length == 0) throw new ArgumentNullException("name");
var filter = new TaskFilter
{
TagId = tag,
DepartmentId = departament,
UserId = userId,
TimeInterval = reportTimeInterval,
FromDate = fromDate,
ToDate = toDate,
ViewType = viewType,
NoResponsible = noResponsible
};
if (project != 0)
filter.ProjectIds.Add(project);
if (status != null)
filter.TaskStatuses.Add((TaskStatus)status);
var template = new ReportTemplate(reportType) { Filter = filter };
SaveOrUpdateTemplate(template, name, period, periodItem, hour, autoGenerated);
return new ReportTemplateWrapper(template);
}
public IEnumerable<TimeWrapper> GetTaskTimeByFilter(int projectid, bool myProjects, int? milestone, bool myMilestones, int tag,
Guid departament, Guid participant, ApiDateTime createdStart, ApiDateTime createdStop, int lastId, PaymentStatus? status)
{
var taskFilter = new TaskFilter
{
DepartmentId = departament,
UserId = participant,
FromDate = createdStart,
ToDate = createdStop,
SortBy = _context.SortBy,
SortOrder = !_context.SortDescending,
SearchText = _context.FilterValue,
TagId = tag,
Offset = _context.StartIndex,
Max = _context.Count,
LastId = lastId,
MyProjects = myProjects,
MyMilestones = myMilestones,
Milestone = milestone
};
if (projectid != 0)
taskFilter.ProjectIds.Add(projectid);
if (status.HasValue)
taskFilter.PaymentStatuses.Add(status.Value);
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
return EngineFactory.GetTimeTrackingEngine().GetByFilter(taskFilter).NotFoundIfNull().Select(r => new TimeWrapper(r)).ToSmartList();
}
protected void Page_Load(object sender, EventArgs e)
{
CurrentUser = SecurityContext.CurrentAccount.ID;
var filter = new TaskFilter
{
SortBy = "title",
SortOrder = true,
ProjectStatuses = new List<ProjectStatus> { ProjectStatus.Open }
};
if (!ProjectSecurity.IsAdministrator(CurrentUser))
filter.ParticipantId = CurrentUser;
UserProjects = Global.EngineFactory.GetProjectEngine().GetByFilter(filter);
if (UserProjects.Any() && (Project == null || !UserProjects.Contains(Project)))
Project = UserProjects[0];
var tasks = Global.EngineFactory.GetTaskEngine().GetByProject(Project.ID, null, Guid.Empty);
OpenUserTasks = tasks.Where(r => r.Status == TaskStatus.Open).OrderBy(r => r.Title);
ClosedUserTasks = tasks.Where(r => r.Status == TaskStatus.Closed).OrderBy(r => r.Title);
Users = Global.EngineFactory.GetProjectEngine().GetTeam(Project.ID).OrderBy(r => DisplayUserSettings.GetFullUserName(r.UserInfo)).ToList();
if (!string.IsNullOrEmpty(Request.QueryString["taskId"]))
{
Target = int.Parse(Request.QueryString["taskId"]);
}
}
public List<Milestone> GetByFilter(TaskFilter filter)
{
var listMilestones = new List<Milestone>();
while (true)
{
var milestones = _milestoneDao.GetByFilter(filter, ProjectSecurity.CurrentUserAdministrator);
if (filter.LastId != 0)
{
var lastMilestoneIndex = milestones.FindIndex(r => r.ID == filter.LastId);
if (lastMilestoneIndex >= 0)
{
milestones = milestones.SkipWhile((r, index) => index <= lastMilestoneIndex).ToList();
}
}
listMilestones.AddRange(milestones);
if (filter.Max <= 0 || filter.Max > 150000) break;
listMilestones = listMilestones.Take((int) filter.Max).ToList();
if (listMilestones.Count == filter.Max || milestones.Count == 0) break;
if (listMilestones.Count != 0)
filter.LastId = listMilestones.Last().ID;
filter.Offset += filter.Max;
}
return listMilestones;
}
public List<TimeSpend> GetByFilter(TaskFilter filter)
{
var query = CreateQuery();
if (filter.Max != 0 && !filter.Max.Equals(int.MaxValue))
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
if (filter.TagId != 0)
{
query.InnerJoin("projects_project_tag ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["TimeSpend"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("t." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("t." + sort, sortColumns[sort]);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.person_id", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.person_id") & Exp.EqColumns("cug.tenant", "t.tenant_id"));
query.Where("cug.groupid", filter.DepartmentId);
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue) &&
!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Between("t.date", filter.FromDate, filter.ToDate));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.note", filter.SearchText, SqlLike.AnyWhere));
}
return DbManager.ExecuteList(query).ConvertAll(r => ToTimeSpend(r));;
}
protected override IEnumerable<KeyValuePair<string, object>> GetClientVariables(HttpContext context)
{
var filter = new TaskFilter
{
SortBy = "title",
SortOrder = true,
ProjectStatuses = new List<ProjectStatus> { ProjectStatus.Open }
};
var projects = Global.EngineFactory.GetProjectEngine().GetByFilter(filter)
.Select(pr => new
{
id = pr.ID,
title = pr.Title,
security = new
{
canCreateMilestone = ProjectSecurity.CanCreateMilestone(pr),
canCreateMessage = ProjectSecurity.CanCreateMessage(pr),
canCreateTask = ProjectSecurity.CanCreateTask(pr),
canEditTeam = ProjectSecurity.CanEditTeam(pr),
canReadFiles = ProjectSecurity.CanReadFiles(pr),
canReadMilestones = ProjectSecurity.CanReadMilestones(pr),
canReadMessages = ProjectSecurity.CanReadMessages(pr),
canReadTasks = ProjectSecurity.CanReadTasks(pr),
isInTeam = ProjectSecurity.IsInTeam(pr, SecurityContext.CurrentAccount.ID, false),
canLinkContact = ProjectSecurity.CanLinkContact(pr),
},
isPrivate = pr.Private,
status = pr.Status
});
var tags = Global.EngineFactory.GetTagEngine().GetTags().Select(r => new { id = r.Key, title = r.Value });
yield return RegisterObject("Projects", new { response = projects });
yield return RegisterObject("Tags", new { response = tags });
if (context.Request.UrlReferrer != null && string.IsNullOrEmpty(HttpUtility.ParseQueryString(context.Request.GetUrlRewriter().Query)["prjID"]) && string.IsNullOrEmpty(HttpUtility.ParseQueryString(context.Request.UrlReferrer.Query)["prjID"]))
{
filter = new TaskFilter
{
SortBy = "deadline",
SortOrder = false,
MilestoneStatuses = new List<MilestoneStatus> { MilestoneStatus.Open }
};
var milestones = Global.EngineFactory.GetMilestoneEngine().GetByFilter(filter)
.Select(m => new
{
id = m.ID,
title = m.Title,
deadline = SetDate(m.DeadLine, TimeZoneInfo.Local)
});
yield return RegisterObject("Milestones", new { response = milestones });
}
}
protected ProjectCommonModule(Project currentProject)
{
CurrentProject = currentProject;
Filter = new TaskFilter
{
ProjectIds = new List<int> { CurrentProject.ID },
MilestoneStatuses = new List<MilestoneStatus> { MilestoneStatus.Open },
TaskStatuses = new List<TaskStatus> { TaskStatus.Open }
};
}
protected void Page_Load(object sender, EventArgs e)
{
_hintPopup.Options.IsPopup = true;
_hintPopupTaskRemove.Options.IsPopup = true;
_hintPopupTaskRemind.Options.IsPopup = true;
moveTaskContainer.Options.IsPopup = true;
var filter = new TaskFilter();
if (RequestContext.IsInConcreteProject)
filter.ProjectIds.Add(RequestContext.GetCurrentProjectId());
AllTasksCount = Global.EngineFactory.GetTaskEngine().GetByFilterCount(filter);
}
public static Report CreateNewReport(ReportType reportType, TaskFilter filter)
{
switch (reportType)
{
case ReportType.MilestonesExpired:
return new Report(new MilestonesExpiredReport(), filter);
case ReportType.MilestonesNearest:
return new Report(new MilestonesNearestReport(), filter);
case ReportType.ProjectsList:
return new Report(new ProjectsListReport(), filter);
case ReportType.ProjectsWithoutActiveMilestones:
return new Report(new ProjectsWithoutActiveMilestonesReport(), filter);
case ReportType.ProjectsWithoutActiveTasks:
return new Report(new ProjectsWithoutActiveTasksReport(), filter);
case ReportType.TasksByProjects:
return new Report(new TasksByProjectsReport(), filter);
case ReportType.TasksByUsers:
return new Report(new TasksByUsersReport(), filter);
case ReportType.TasksExpired:
return new Report(new TasksExpiredReport(), filter);
case ReportType.TimeSpend:
return new Report(new TimeSpendReport(), filter);
case ReportType.UsersWithoutActiveTasks:
return new Report(new UsersWithoutActiveTasksReport(), filter);
case ReportType.UsersWorkload:
return new Report(new UsersWorkloadReport(), filter);
case ReportType.UsersActivity:
return new Report(new UsersActivityReport(), filter);
case ReportType.EmptyReport:
return new Report(new EmptyReport(), filter);
}
throw new Exception("There is not report Type");
}
public List<Task> GetByFilter(TaskFilter filter)
{
var listTask = new List<Task>();
var showAll = !(filter.Max > 0 && filter.Max < 150000);
var taskStatus = filter.TaskStatuses.Any();
if (!taskStatus)
{
filter.TaskStatuses.Add(TaskStatus.Open);
}
do
{
var tasks = _taskDao.GetByFilter(filter).FindAll(CanRead);
var lastTaskIndex = tasks.FindIndex(r => r.ID == filter.LastId);
if (lastTaskIndex >= 0)
tasks = tasks.SkipWhile((r, index) => index <= lastTaskIndex).ToList();
listTask.AddRange(tasks);
listTask = listTask.Take((int) filter.Max).ToList();
if (listTask.Count != 0)
filter.LastId = listTask.Last().ID;
if ((showAll || (listTask.Count < filter.Max || tasks.Count == 0)) && !taskStatus)
{
filter.TaskStatuses.Clear();
taskStatus = true;
continue;
}
filter.Offset += filter.Max;
if (tasks.Count == 0 || showAll) break;
}
while (listTask.Count < filter.Max);
_subtaskDao.GetSubtasks(ref listTask);
return listTask;
}
public int GetByFilterCount(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var query = new SqlQuery(TimeTrackingTable + " t")
.InnerJoin(TasksTable + " pt",
Exp.EqColumns("t.tenant_id", "pt.tenant_id") &
Exp.EqColumns("t.relative_task_id", "pt.id"))
.Select("t.id")
.Where("t.tenant_id", Tenant);
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
var queryCount = new SqlQuery().SelectCount().From(query, "t1");
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteScalar<int>(queryCount);
}
}
public List<Milestone> GetByFilter(TaskFilter filter)
{
var listMilestones = new List<Milestone>();
while (true)
{
var milestones = milestoneDao.GetByFilter(filter).FindAll(CanRead);
if (filter.LastId != 0)
{
var lastMilestoneIndex = milestones.FindIndex(r => r.ID == filter.LastId);
if (lastMilestoneIndex >= 0)
{
milestones = milestones.SkipWhile((r, index) => index <= lastMilestoneIndex).ToList();
}
}
listMilestones.AddRange(milestones);
if (filter.ParticipantId.HasValue)
{
var tasks = taskDao.GetByResponsible(filter.ParticipantId.Value, new[] { TaskStatus.Open });
listMilestones = listMilestones.FindAll(milestone => tasks.Any(task => task.Milestone == milestone.ID));
}
if (filter.Max <= 0 || filter.Max > 150000) break;
listMilestones = listMilestones.Take((int) filter.Max).ToList();
if (listMilestones.Count == filter.Max || milestones.Count == 0) break;
if (listMilestones.Count != 0)
filter.LastId = listMilestones.Last().ID;
filter.Offset += filter.Max;
}
return listMilestones;
}
public List<TimeSpend> GetByFilter(TaskFilter filter)
{
var listTimeSpend = new List<TimeSpend>();
var isAdmin = ProjectSecurity.CurrentUserAdministrator;
var anyOne = ProjectSecurity.IsPrivateDisabled;
while (true)
{
var timeSpend = timeSpendDao.GetByFilter(filter, isAdmin, anyOne);
timeSpend = GetTasks(timeSpend).Where(r=> r.Task != null).ToList();
if (filter.LastId != 0)
{
var lastTimeSpendIndex = timeSpend.FindIndex(r => r.ID == filter.LastId);
if (lastTimeSpendIndex >= 0)
{
timeSpend = timeSpend.SkipWhile((r, index) => index <= lastTimeSpendIndex).ToList();
}
}
listTimeSpend.AddRange(timeSpend);
if (filter.Max <= 0 || filter.Max > 150000) break;
listTimeSpend = listTimeSpend.Take((int)filter.Max).ToList();
if (listTimeSpend.Count == filter.Max || timeSpend.Count == 0) break;
if (listTimeSpend.Count != 0)
filter.LastId = listTimeSpend.Last().ID;
filter.Offset += filter.Max;
}
return listTimeSpend;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "p.id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.HasUserId || (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty))
{
var existParticipant = new SqlQuery(ParticipantTable + " ppp").Select("ppp.participant_id").Where(Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.tenant", Tenant));
if (filter.DepartmentId != Guid.Empty)
{
existParticipant.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "ppp.participant_id") & Exp.EqColumns("cug.tenant", "ppp.tenant"));
existParticipant.Where("cug.groupid", filter.DepartmentId);
}
if (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty)
{
existParticipant.Where(Exp.Eq("ppp.participant_id", filter.ParticipantId.ToString()));
}
query.Where(Exp.Exists(existParticipant));
}
if (filter.UserId != Guid.Empty)
{
query.Where("responsible_id", filter.UserId);
}
if (filter.Follow)
{
query.InnerJoin(FollowingProjectTable + " pfpp", Exp.EqColumns("p.id", "pfpp.project_id"));
query.Where(Exp.Eq("pfpp.participant_id", CurrentUserID));
}
if (filter.ProjectStatuses.Count != 0)
{
query.Where(Exp.Eq("p.status", filter.ProjectStatuses.First()));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsModule))
{
var projIds = FullTextSearch.Search(FullTextSearch.ProjectsModule.Match(filter.SearchText));
query.Where(Exp.In("p.id", projIds));
}
else
{
query.Where(Exp.Like("p.title", filter.SearchText, SqlLike.AnyWhere));
}
}
query.GroupBy("p.id");
if (checkAccess)
{
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
var isInTeam = new SqlQuery(ParticipantTable).Select("security").Where(Exp.EqColumns("p.id", "project_id") & Exp.Eq("removed", false) & Exp.Eq("participant_id", CurrentUserID));
query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam)));
}
return query;
}
public int GetByFilterCount(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var query = new SqlQuery(ProjectsTable + " p")
.Select("p.id")
.Where("p.tenant_id", Tenant);
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
var queryCount = new SqlQuery().SelectCount().From(query, "t1");
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteScalar<int>(queryCount);
}
}
public List<Project> GetByFilter(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var query = new SqlQuery(ProjectsTable + " p")
.Select(ProjectColumns.Select(c => "p." + c).ToArray())
.Select(new SqlQuery(MilestonesTable + " m").SelectCount().Where(Exp.EqColumns("m.tenant_id", "p.tenant_id") & Exp.EqColumns("m.project_id", "p.id")).Where(Exp.Eq("m.status", MilestoneStatus.Open)))
.Select(new SqlQuery(TasksTable + " t").SelectCount().Where(Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id")).Where(!Exp.Eq("t.status", TaskStatus.Closed)))
.Select(new SqlQuery(ParticipantTable + " pp").SelectCount().Where(Exp.EqColumns("pp.tenant", "p.tenant_id") & Exp.EqColumns("pp.project_id", "p.id") & Exp.Eq("pp.removed", false)))
.Select("p.private")
.Where("p.tenant_id", Tenant);
if (filter.Max > 0 && filter.Max < 150000)
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
query.OrderBy("(case p.status when 2 then 1 when 1 then 2 else 0 end)", true);
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["Project"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("p." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("p." + sort, sortColumns[sort]);
}
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(query).ConvertAll(ToProjectFull).ToList();
}
}
public int GetByFilterCount(TaskFilter filter)
{
var isAdmin = ProjectSecurity.IsAdministrator(SecurityContext.CurrentAccount.ID);
return _milestoneDao.GetByFilterCount(filter, isAdmin);
}
public int GetByFilterCount(TaskFilter filter)
{
return messageDao.GetByFilterCount(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled);
}
public List<Message> GetByFilter(TaskFilter filter)
{
var messages = messageDao.GetByFilter(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled);
var commentsCount = commentDao.GetCommentsCount(messages.Select(r => (ProjectEntity)r).ToList());
return messages.Select((message, index) =>
{
message.CommentsCount = commentsCount[index];
return message;
}).ToList();
}
public TaskFilterOperationResult GetByFilter (TaskFilter filter)
{
if (filter.Offset < 0 || filter.Max < 0)
return null;
var isAdmin = ProjectSecurity.IsAdministrator(SecurityContext.CurrentAccount.ID);
var anyOne = ProjectSecurity.IsPrivateDisabled;
var count = taskDao.GetByFilterCount(filter, isAdmin, anyOne);
var filterLimit = filter.Max;
var filterOffset = filter.Offset;
if (filterOffset > count.TasksTotal)
return new TaskFilterOperationResult(count); //there are some records but we cant see them due to offset
var taskList = new List<Task>();
if (filter.HasTaskStatuses)
{
taskList = taskDao.GetByFilter(filter, isAdmin, anyOne);
}
else if (filterOffset > count.TasksOpen && count.TasksClosed != 0)
{
filter.TaskStatuses.Add(TaskStatus.Closed);
filter.Offset = filterOffset - count.TasksOpen;
taskList = taskDao.GetByFilter(filter, isAdmin, anyOne);
}
else
{
//TODO: to one sql query using UNION ALL
if (count.TasksOpen != 0)
{
filter.TaskStatuses.Add(TaskStatus.Open);
taskList = taskDao.GetByFilter(filter, isAdmin, anyOne);
}
if (taskList.Count < filterLimit && count.TasksClosed != 0)
{
filter.TaskStatuses.Clear();
filter.TaskStatuses.Add(TaskStatus.Closed);
filter.Offset = 0;
filter.Max = filterLimit - taskList.Count;
taskList.AddRange(taskDao.GetByFilter(filter, isAdmin, anyOne));
}
}
filter.Offset = filterOffset;
filter.Max = filterLimit;
filter.TaskStatuses.Clear();
subtaskDao.GetSubtasks(ref taskList);
var taskLinks = taskDao.GetLinks(taskList).ToList();
taskList = taskList.GroupJoin(taskLinks, task => task.ID, link => link.DependenceTaskId, (task, linksCol) =>
{
task.Links.AddRange(linksCol);
return task;
}).ToList();
taskList = taskList.GroupJoin(taskLinks, task => task.ID, link => link.ParentTaskId, (task, linksCol) =>
{
task.Links.AddRange(linksCol);
return task;
}).ToList();
return new TaskFilterOperationResult(taskList, count);
}
public int GetByFilterCount(TaskFilter filter)
{
return taskDao.GetByFilterCount(filter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled).TasksTotal;
}
public IEnumerable<MilestoneWrapper> GetMilestonesByFilter(int projectid, int tag, MilestoneStatus? status,
ApiDateTime deadlineStart, ApiDateTime deadlineStop, Guid? taskResponsible,
int lastId, bool myProjects, Guid milestoneResponsible)
{
var milestoneEngine = EngineFactory.GetMilestoneEngine();
var filter = new TaskFilter
{
UserId = milestoneResponsible,
ParticipantId = taskResponsible,
TagId = tag,
FromDate = deadlineStart,
ToDate = deadlineStop,
SortBy = _context.SortBy,
SortOrder = !_context.SortDescending,
SearchText = _context.FilterValue,
Offset = _context.StartIndex,
Max = _context.Count,
LastId = lastId,
MyProjects = myProjects
};
if(projectid != 0)
filter.ProjectIds.Add(projectid);
if (status != null)
filter.MilestoneStatuses.Add((MilestoneStatus)status);
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
_context.TotalCount = milestoneEngine.GetByFilterCount(filter);
return milestoneEngine.GetByFilter(filter).NotFoundIfNull().Select(r => new MilestoneWrapper(r)).ToSmartList();
}
