Ejemplo n.º 1
0
        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;
        }
Ejemplo n.º 2
0
        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;

        }
Ejemplo n.º 3
0
        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;
        }
Ejemplo n.º 4
0
        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));
        }
Ejemplo n.º 5
0
        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;
        }
Ejemplo n.º 6
0
        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;
        }
Ejemplo n.º 7
0
        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;
        }