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; }
private SqlQuery WhereConditional( SqlQuery sqlQuery, String alias, Guid responsibleID, int categoryID, bool? isClosed, DateTime fromDate, DateTime toDate, EntityType entityType, int entityID, int from, int count, OrderBy orderBy) { var aliasPrefix = !String.IsNullOrEmpty(alias) ? alias + "." : ""; if (responsibleID != Guid.Empty) sqlQuery.Where(Exp.Eq("responsible_id", responsibleID)); if (entityID > 0) switch (entityType) { case EntityType.Contact: var isCompany = true; using (var db = GetDb()) { isCompany = db.ExecuteScalar<bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID))); } if (isCompany) return WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy); else return WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy); case EntityType.Person: sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID)); break; case EntityType.Company: var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList(); if (personIDs.Count == 0) sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID)); else { personIDs.Add(entityID); sqlQuery.Where(Exp.In(aliasPrefix + "contact_id", personIDs)); } break; case EntityType.Case: case EntityType.Opportunity: sqlQuery.Where(Exp.Eq(aliasPrefix + "entity_id", entityID) & Exp.Eq(aliasPrefix + "entity_type", (int)entityType)); break; } if (isClosed.HasValue) sqlQuery.Where(aliasPrefix + "is_closed", isClosed); if (categoryID > 0) sqlQuery.Where(Exp.Eq(aliasPrefix + "category_id", categoryID)); if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue) sqlQuery.Where(Exp.Between(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1)))); else if (fromDate != DateTime.MinValue) sqlQuery.Where(Exp.Ge(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate))); else if (toDate != DateTime.MinValue) sqlQuery.Where(Exp.Le(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1)))); if (0 < from && from < int.MaxValue) sqlQuery.SetFirstResult(from); if (0 < count && count < int.MaxValue) sqlQuery.SetMaxResults(count); sqlQuery.OrderBy(aliasPrefix + "is_closed", true); if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy)) { switch ((TaskSortedByType)orderBy.SortedBy) { case TaskSortedByType.Title: sqlQuery .OrderBy(aliasPrefix + "title", orderBy.IsAsc) .OrderBy(aliasPrefix + "deadline", true); break; case TaskSortedByType.DeadLine: sqlQuery.OrderBy(aliasPrefix + "deadline", orderBy.IsAsc) .OrderBy(aliasPrefix + "title", true); break; case TaskSortedByType.Category: sqlQuery.OrderBy(aliasPrefix + "category_id", orderBy.IsAsc) .OrderBy(aliasPrefix + "deadline", true) .OrderBy(aliasPrefix + "title", true); break; case TaskSortedByType.ContactManager: sqlQuery.LeftOuterJoin("core_user u", Exp.EqColumns(aliasPrefix + "responsible_id", "u.id")) .OrderBy("case when u.lastname is null or u.lastname = '' then 1 else 0 end, u.lastname", orderBy.IsAsc) .OrderBy("case when u.firstname is null or u.firstname = '' then 1 else 0 end, u.firstname", orderBy.IsAsc) .OrderBy(aliasPrefix + "deadline", true) .OrderBy(aliasPrefix + "title", true); break; case TaskSortedByType.Contact: sqlQuery.LeftOuterJoin("crm_contact c_tbl", Exp.EqColumns(aliasPrefix + "contact_id", "c_tbl.id")) .OrderBy("case when c_tbl.display_name is null then 1 else 0 end, c_tbl.display_name", orderBy.IsAsc) .OrderBy(aliasPrefix + "deadline", true) .OrderBy(aliasPrefix + "title", true); break; } } else { sqlQuery .OrderBy(aliasPrefix + "deadline", true) .OrderBy(aliasPrefix + "title", true); } return sqlQuery; }
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 void CheckSecurity(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess) { if (checkAccess) { query.Where(Exp.Eq("p.private", false)); return; } if (isAdmin) return; 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 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)); }
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) { 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; }