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 List<int> GetTaskCount(List<int> projectId, TaskStatus? taskStatus, bool isAdmin)
{
var query = new SqlQuery(TasksTable + " t")
.Select("t.project_id").SelectCount()
.Where(Exp.In("t.project_id", projectId))
.Where("t.tenant_id", Tenant)
.GroupBy("t.project_id");
if (taskStatus != null)
{
if (taskStatus == TaskStatus.Open)
query.Where(!Exp.Eq("t.status", TaskStatus.Closed));
else
query.Where("t.status", TaskStatus.Closed);
}
if (!isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.LeftOuterJoin(TasksResponsibleTable + " ptr", Exp.EqColumns("t.tenant_id", "ptr.tenant_id") & Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.responsible_id", CurrentUserID))
.LeftOuterJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.participant_id", CurrentUserID))
.Where(Exp.Eq("p.private", false) | !Exp.Eq("ptr.responsible_id", null) | (Exp.Eq("p.private", true) & !Exp.Eq("ppp.security", null) & !Exp.Eq("ppp.security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks)));
}
using (var db = new DbManager(DatabaseId))
{
var result = db.ExecuteList(query);
return projectId.ConvertAll(
pid =>
{
var res = result.Find(r => Convert.ToInt32(r[0]) == pid);
return res == null ? 0 : Convert.ToInt32(res[1]);
}
);
}
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin)
{
if (filter.MilestoneStatuses.Count != 0)
{
query.Where("t.status", filter.MilestoneStatuses.First());
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where(Exp.Eq("t.responsible_id", filter.UserId));
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.ParticipantId.HasValue)
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("pt.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("pt.id", "ptr1.task_id"));
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString()));
query.LeftOuterJoin(TasksTable + " as pt", Exp.EqColumns("pt.milestone_id", "t.id") & Exp.EqColumns("pt.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
if (!isAdmin)
{
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = Exp.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false);
var canReadMilestones = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Eq("t.responsible_id", CurrentUserID);
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadMilestones));
}
return query;
}
public List<Project> GetOpenProjectsWithTasks(Guid participantId)
{
var query = new SqlQuery(ProjectsTable + " p")
.Select(ProjectColumns.Select(c => "p." + c).ToArray())
.InnerJoin(TasksTable + " t", Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id"))
.Where("p.tenant_id", Tenant)
.Where("p.status", ProjectStatus.Open)
.OrderBy("p.title", true)
.GroupBy("p.id");
if (!participantId.Equals(Guid.Empty))
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("ppp.tenant", "p.tenant_id") & Exp.EqColumns("ppp.project_id", "p.id") & Exp.Eq("ppp.removed", false))
.Where("ppp.participant_id", participantId);
}
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(query).ConvertAll(ToProject).ToList();
}
}
public IList<object[]> BuildUsersStatisticsReport(TaskFilter filter)
{
var query = new SqlQuery(TasksTable + " t")
.Select("r.responsible_id")
.InnerJoin(TasksResponsibleTable + " 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()));
}
query.Where(filter.HasProjectIds ? Exp.In("t.project_id", filter.ProjectIds) : Exp.Gt("t.project_id", 0));
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(query)
.ConvertAll(r => new object[] {ToGuid(r[0]), Convert.ToInt64(r[1]), Convert.ToInt64(r[2]), Convert.ToInt64(r[3])});
}
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin)
{
if (filter.Follow)
{
var objects = new List<String>(NotifySource.Instance.GetSubscriptionProvider().GetSubscriptions(NotifyConstants.Event_NewCommentForMessage, NotifySource.Instance.GetRecipientsProvider().GetRecipient(CurrentUserID.ToString())));
if (filter.ProjectIds.Count != 0)
{
objects = objects.Where(r => r.Split('_')[2] == filter.ProjectIds[0].ToString(CultureInfo.InvariantCulture)).ToList();
}
var ids = objects.Select(r => r.Split('_')[1]).ToArray();
query.Where(Exp.In("t.id", ids));
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("ppp.tenant", "t.tenant_id") & Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " pt", Exp.EqColumns("pt.project_id", "t.project_id"));
query.Where("pt.tag_id", filter.TagId);
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.create_by", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.EqColumns("cug.tenant", "t.tenant_id") & Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.create_by"));
query.Where("cug.groupid", filter.DepartmentId);
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Between("t.create_on", filter.FromDate, filter.ToDate.AddDays(1)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
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) & !Exp.Eq("security & " + (int)ProjectTeamSecurity.Messages, (int)ProjectTeamSecurity.Messages));
query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam)));
}
return query;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.MilestoneStatuses.Count != 0)
{
query.Where("t.status", filter.MilestoneStatuses.First());
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where(Exp.Eq("t.responsible_id", filter.UserId));
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.ParticipantId.HasValue)
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("pt.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("pt.id", "ptr1.task_id"));
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString()));
query.LeftOuterJoin(TasksTable + " as pt", Exp.EqColumns("pt.milestone_id", "t.id") & Exp.EqColumns("pt.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsModule, FullTextSearch.ProjectsCommentsModule))
{
var mIds = FullTextSearch.Search(FullTextSearch.ProjectsMilestonesModule.Match(filter.SearchText));
query.Where(Exp.In("t.id", mIds));
}
else
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
}
CheckSecurity(query, filter, isAdmin, checkAccess);
return query;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.MyProjects || filter.MyMilestones)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("t.project_id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
if (filter.Milestone.HasValue || filter.MyMilestones)
{
query.InnerJoin(MilestonesTable + " pm", Exp.EqColumns("pm.tenant_id", "t.tenant_id") & Exp.EqColumns("pm.project_id", "t.project_id"));
query.Where(Exp.EqColumns("pt.milestone_id", "pm.id"));
if (filter.Milestone.HasValue)
{
query.Where("pm.id", filter.Milestone);
}
else if (filter.MyMilestones)
{
query.Where(Exp.Gt("pm.id", 0));
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
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 (filter.PaymentStatuses.Any())
{
query.Where(Exp.In("payment_status", filter.PaymentStatuses));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.note", filter.SearchText, SqlLike.AnyWhere));
}
if (checkAccess)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("p.id", "t.project_id"));
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("p.id", "t.project_id"));
if (!(filter.MyProjects || filter.MyMilestones))
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = !Exp.Eq("ppp.security", null) & Exp.Eq("ppp.removed", false);
var canReadTasks = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks);
var canReadMilestones = Exp.Eq("pt.milestone_id", 0) | !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Exists(new SqlQuery("projects_tasks_responsible ptr")
.Select("ptr.responsible_id")
.Where(Exp.EqColumns("pt.id", "ptr.task_id") &
Exp.EqColumns("ptr.tenant_id", "pt.tenant_id") &
Exp.Eq("ptr.responsible_id", CurrentUserID)));
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadTasks & canReadMilestones));
}
query.GroupBy("t.id");
return query;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin)
{
query = CreateQueryFilterBase(query, filter, isAdmin);
if (filter.ParticipantId.HasValue && filter.ParticipantId == Guid.Empty)
{
query.Where(Exp.Eq("ptr.task_id", null));
}
if (filter.Milestone.HasValue)
{
query.Where("t.milestone_id", filter.Milestone);
}
else if (filter.MyMilestones)
{
if (!filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
query.Where(Exp.Gt("m.id", 0));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge(GetSortFilter("deadline", true), TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le(GetSortFilter("deadline", true), TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!isAdmin)
{
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = Exp.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false);
var canReadTasks = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks);
var canReadMilestones = Exp.Eq("t.milestone_id", 0) | !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var exists = new SqlQuery("projects_tasks_responsible ptr1").Select("ptr1.responsible_id").Where(Exp.EqColumns("t.id", "ptr1.task_id") & Exp.EqColumns("ptr1.tenant_id", "t.tenant_id") & Exp.Eq("ptr1.responsible_id", CurrentUserID));
var responsible = Exp.Exists(exists);
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadTasks & canReadMilestones));
}
return query;
}
private SqlQuery CreateQueryFilterCount(SqlQuery query, TaskFilter filter, bool isAdmin)
{
query = CreateQueryFilterBase(query, filter, isAdmin);
if (filter.Milestone.HasValue)
{
query.Where("t.milestone_id", filter.Milestone);
}
else if (filter.MyMilestones)
{
if (!filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("t.milestone_id", "m.id") & Exp.EqColumns("m.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existsMilestone));
}
if (filter.ParticipantId.HasValue && filter.ParticipantId == Guid.Empty)
{
var notExists = new SqlQuery(TasksResponsibleTable + " ptr").Select("ptr.responsible_id").Where(Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.tenant_id", Tenant));
query.Where(!Exp.Exists(notExists));
}
var hasFromDate = !filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue);
var hasToDate = !filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue);
if (hasFromDate && hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("m.id", "t.milestone_id") & Exp.EqColumns("m.tenant_id", "t.tenant_id") & Exp.Between("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)));
var expExists = Exp.Exists(existsMilestone) & Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"));
query.Where(Exp.Between("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)) | expExists);
}
else if (hasFromDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Ge("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)))
.Where(Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss")));
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate))
| Exp.Exists(existsMilestone));
}
else if (hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Le("m.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)))
.Where(!Exp.Eq("m.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss")))
.Where("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"));
query.Where(!Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"))
& Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate))
| Exp.Exists(existsMilestone));
}
if (!isAdmin)
{
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = Exp.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false);
var canReadTasks = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks);
var canReadMilestones = Exp.Eq("t.milestone_id", 0) | !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Exists(new SqlQuery("projects_tasks_responsible ptr")
.Select("ptr.responsible_id")
.Where(Exp.EqColumns("t.id", "ptr.task_id") &
Exp.EqColumns("ptr.tenant_id", "t.tenant_id") &
Exp.Eq("ptr.responsible_id", CurrentUserID)));
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadTasks & canReadMilestones));
}
return query;
}
private SqlQuery CreateQueryFilterBase(SqlQuery query, TaskFilter filter, bool isAdmin)
{
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.TaskStatuses.Count != 0)
{
var status = filter.TaskStatuses.First();
if (status == TaskStatus.Open)
query.Where(!Exp.Eq("t.status", TaskStatus.Closed));
else
query.Where("t.status", TaskStatus.Closed);
}
if(!filter.UserId.Equals(Guid.Empty))
{
query.Where("t.create_by", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty || (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty))
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("t.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("t.id", "ptr1.task_id"));
if (filter.DepartmentId != Guid.Empty)
{
existSubtask.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "pst.responsible_id") & Exp.Eq("cug.tenant", Tenant));
existResponsible.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "ptr1.responsible_id") & Exp.Eq("cug.tenant", Tenant));
existSubtask.Where("cug.groupid", filter.DepartmentId);
existResponsible.Where("cug.groupid", filter.DepartmentId);
}
if (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty)
{
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString()));
}
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
return query;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
query = CreateQueryFilterBase(query, filter);
if (filter.ParticipantId.HasValue && filter.ParticipantId == Guid.Empty)
{
query.Where(Exp.Eq("ptr.task_id", null));
}
if (filter.Milestone.HasValue)
{
query.Where("t.milestone_id", filter.Milestone);
}
else if (filter.MyMilestones)
{
if (!filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
query.Where(Exp.Gt("m.id", 0));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge(GetSortFilter("deadline", true), TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le(GetSortFilter("deadline", true), TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
CheckSecurity(query, filter, isAdmin, checkAccess);
return query;
}
private SqlQuery CreateQueryFilterCount(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
query = CreateQueryFilterBase(query, filter);
if (filter.Milestone.HasValue)
{
query.Where("t.milestone_id", filter.Milestone);
}
else if (filter.MyMilestones)
{
if (!filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("t.milestone_id", "m.id") & Exp.EqColumns("m.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existsMilestone));
}
if (filter.ParticipantId.HasValue && filter.ParticipantId == Guid.Empty)
{
var notExists = new SqlQuery(TasksResponsibleTable + " ptr").Select("ptr.responsible_id").Where(Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.tenant_id", Tenant));
query.Where(!Exp.Exists(notExists));
}
var hasFromDate = !filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue);
var hasToDate = !filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue);
if (hasFromDate && hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("m.id", "t.milestone_id") & Exp.EqColumns("m.tenant_id", "t.tenant_id") & Exp.Between("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)));
var expExists = Exp.Exists(existsMilestone) & Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"));
query.Where(Exp.Between("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)) | expExists);
}
else if (hasFromDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Ge("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)))
.Where(Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss")));
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate))
| Exp.Exists(existsMilestone));
}
else if (hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Le("m.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)))
.Where(!Exp.Eq("m.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss")))
.Where("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"));
query.Where(!Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"))
& Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate))
| Exp.Exists(existsMilestone));
}
CheckSecurity(query, filter, isAdmin, checkAccess);
return query;
}
