コード例 #1
0
        private List <Task> GetCrudeTasks(
            String searchText,
            Guid responsibleID,
            int categoryID,
            bool?isClosed,
            DateTime fromDate,
            DateTime toDate,
            EntityType entityType,
            int entityID,
            int from,
            int count,
            OrderBy orderBy)
        {
            var taskTableAlias = "t";
            var sqlQuery       = WhereConditional(GetTaskQuery(null, taskTableAlias), taskTableAlias, responsibleID,
                                                  categoryID, isClosed, fromDate, toDate, entityType, entityID, from, count,
                                                  orderBy);

            if (!String.IsNullOrEmpty(searchText))
            {
                searchText = searchText.Trim();


                var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
                               .ToArray();

                if (keywords.Length > 0)
                {
                    var modules = SearchDao.GetFullTextSearchModule(EntityType.Task, searchText);
                    if (FullTextSearch.SupportModule(modules))
                    {
                        var taskIDs = FullTextSearch.Search(modules);

                        if (taskIDs.Any())
                        {
                            sqlQuery.Where(Exp.In(taskTableAlias + ".id", taskIDs));
                        }
                        else
                        {
                            return(new List <Task>());
                        }
                    }
                    else
                    {
                        sqlQuery.Where(BuildLike(new[] { taskTableAlias + ".title", taskTableAlias + ".description" },
                                                 keywords));
                    }
                }
            }

            using (var db = GetDb())
            {
                return(db.ExecuteList(sqlQuery)
                       .ConvertAll(row => ToTask(row)));
            }
        }
コード例 #2
0
ファイル: DealDao.cs プロジェクト: xjsaber/CommunityServer
        private Exp WhereConditional(
            ICollection <int> exceptIDs,
            String searchText,
            Guid responsibleID,
            int milestoneID,
            IEnumerable <String> tags,
            int contactID,
            DealMilestoneStatus?stageType,
            bool?contactAlsoIsParticipant)
        {
            var conditions = new List <Exp>();

            var ids = new List <int>();

            if (!String.IsNullOrEmpty(searchText))
            {
                searchText = searchText.Trim();

                var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToArray();
                var modules  = SearchDao.GetFullTextSearchModule(EntityType.Opportunity, searchText);

                if (keywords.Length > 0)
                {
                    if (FullTextSearch.SupportModule(modules))
                    {
                        ids = FullTextSearch.Search(modules);

                        if (ids.Count == 0)
                        {
                            return(null);
                        }
                    }
                    else
                    {
                        conditions.Add(BuildLike(new[] { "tblDeal.title", "tblDeal.description" }, keywords));
                    }
                }
            }

            if (tags != null && tags.Any())
            {
                ids = SearchByTags(EntityType.Opportunity, ids.ToArray(), tags);

                if (ids.Count == 0)
                {
                    return(null);
                }
            }

            if (contactID > 0)
            {
                if (contactAlsoIsParticipant.HasValue && contactAlsoIsParticipant.Value)
                {
                    var relativeContactsID = GetRelativeToEntity(contactID, EntityType.Opportunity, null).ToList();

                    if (relativeContactsID.Count == 0)
                    {
                        conditions.Add(Exp.Eq("tblDeal.contact_id", contactID));
                    }
                    else
                    {
                        if (ids.Count > 0)
                        {
                            ids = relativeContactsID.Intersect(ids).ToList();

                            if (ids.Count == 0)
                            {
                                return(null);
                            }
                        }
                        else
                        {
                            ids = relativeContactsID;
                        }
                    }
                }
                else
                {
                    conditions.Add(Exp.Eq("tblDeal.contact_id", contactID));
                }
            }

            if (0 < milestoneID && milestoneID < int.MaxValue)
            {
                conditions.Add(Exp.Eq("tblDeal.deal_milestone_id", milestoneID));
            }

            if (responsibleID != Guid.Empty)
            {
                conditions.Add(Exp.Eq("tblDeal.responsible_id", responsibleID));
            }

            if (stageType != null)
            {
                conditions.Add(Exp.Eq("tblDM.status", (int)stageType.Value));
            }

            if (ids.Count > 0)
            {
                if (exceptIDs.Count > 0)
                {
                    ids = ids.Except(exceptIDs).ToList();
                    if (ids.Count == 0)
                    {
                        return(null);
                    }
                }

                conditions.Add(Exp.In("tblDeal.id", ids));
            }
            else if (exceptIDs.Count > 0)
            {
                conditions.Add(!Exp.In("tblDeal.id", exceptIDs.ToArray()));
            }

            if (conditions.Count == 0)
            {
                return(null);
            }

            return(conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j));
        }
コード例 #3
0
        public List <RelationshipEvent> GetItems(
            String searchText,
            EntityType entityType,
            int entityID,
            Guid createBy,
            int categoryID,
            DateTime fromDate,
            DateTime toDate,
            int from,
            int count,
            OrderBy orderBy)
        {
            var sqlQuery = GetRelationshipEventQuery(null);

            if (entityID > 0)
            {
                switch (entityType)
                {
                case EntityType.Contact:
                    var isCompany = false;
                    isCompany = Db.ExecuteScalar <bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID)));

                    if (isCompany)
                    {
                        return(GetItems(searchText, EntityType.Company, entityID, createBy, categoryID, fromDate, toDate, from, count, orderBy));
                    }
                    else
                    {
                        return(GetItems(searchText, EntityType.Person, entityID, createBy, categoryID, fromDate, toDate, from, count, orderBy));
                    }

                case EntityType.Person:
                    sqlQuery.Where(Exp.Eq("contact_id", entityID));
                    break;

                case EntityType.Company:

                    var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();

                    if (personIDs.Count == 0)
                    {
                        sqlQuery.Where(Exp.Eq("contact_id", entityID));
                    }
                    else
                    {
                        personIDs.Add(entityID);
                        sqlQuery.Where(Exp.In("contact_id", personIDs));
                    }

                    break;

                case EntityType.Case:
                case EntityType.Opportunity:
                    sqlQuery.Where(Exp.Eq("entity_id", entityID) &
                                   Exp.Eq("entity_type", (int)entityType));
                    break;
                }
            }

            if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
            {
                sqlQuery.Where(Exp.Between("create_on", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
            }
            else if (fromDate != DateTime.MinValue)
            {
                sqlQuery.Where(Exp.Ge("create_on", TenantUtil.DateTimeToUtc(fromDate)));
            }
            else if (toDate != DateTime.MinValue)
            {
                sqlQuery.Where(Exp.Le("create_on", TenantUtil.DateTimeToUtc(toDate).AddDays(1).AddMinutes(-1)));
            }

            if (createBy != Guid.Empty)
            {
                sqlQuery.Where(Exp.Eq("create_by", createBy));
            }

            if (categoryID != 0)
            {
                sqlQuery.Where(Exp.Eq("category_id", categoryID));
            }

            if (!String.IsNullOrEmpty(searchText))
            {
                searchText = searchText.Trim();

                var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
                               .ToArray();

                var modules = SearchDao.GetFullTextSearchModule(EntityType.RelationshipEvent, searchText);

                var ids = new List <int>();
                if (!FullTextSearch.SupportModule(modules))
                {
                    if (keywords.Length > 0)
                    {
                        sqlQuery.Where(BuildLike(new[] { "content" }, keywords));
                    }
                }
                else
                {
                    ids = FullTextSearch.Search(modules);

                    if (ids.Count == 0)
                    {
                        return(new List <RelationshipEvent>());
                    }
                    sqlQuery.Where(Exp.In("id", ids));
                }
            }

            if (0 < from && from < int.MaxValue)
            {
                sqlQuery.SetFirstResult(from);
            }

            if (0 < count && count < int.MaxValue)
            {
                sqlQuery.SetMaxResults(count);
            }

            if (orderBy != null && Enum.IsDefined(typeof(RelationshipEventByType), orderBy.SortedBy))
            {
                switch ((RelationshipEventByType)orderBy.SortedBy)
                {
                case RelationshipEventByType.Category:
                    sqlQuery.OrderBy("category_id", orderBy.IsAsc);
                    break;

                case RelationshipEventByType.Content:
                    sqlQuery.OrderBy("content", orderBy.IsAsc);
                    break;

                case RelationshipEventByType.CreateBy:
                    sqlQuery.OrderBy("create_by", orderBy.IsAsc);
                    break;

                case RelationshipEventByType.Created:
                    sqlQuery.OrderBy("create_on", orderBy.IsAsc);
                    break;
                }
            }
            else
            {
                sqlQuery.OrderBy("create_on", false);
            }

            return(Db.ExecuteList(sqlQuery)
                   .ConvertAll(row => ToRelationshipEvent(row)));
        }
コード例 #4
0
        private Exp WhereConditional(
            ICollection <int> exceptIDs,
            String searchText,
            int contactID,
            bool?isClosed,
            IEnumerable <String> tags)
        {
            var conditions = new List <Exp>();

            var ids = new List <int>();

            if (!String.IsNullOrEmpty(searchText))
            {
                searchText = searchText.Trim();

                var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
                               .ToArray();

                if (keywords.Length > 0)
                {
                    var modules = SearchDao.GetFullTextSearchModule(EntityType.Case, searchText);

                    if (FullTextSearch.SupportModule(modules))
                    {
                        ids = FullTextSearch.Search(modules);

                        if (ids.Count == 0)
                        {
                            return(null);
                        }
                    }
                    else
                    {
                        conditions.Add(BuildLike(new[] { "title" }, keywords));
                    }
                }
            }

            if (contactID > 0)
            {
                var sqlQuery = new SqlQuery("crm_entity_contact")
                               .Select("entity_id")
                               .Where(Exp.Eq("contact_id", contactID) & Exp.Eq("entity_type", (int)EntityType.Case));

                if (ids.Count > 0)
                {
                    sqlQuery.Where(Exp.In("entity_id", ids));
                }
                using (var db = GetDb())
                {
                    ids = db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList();
                }
                if (ids.Count == 0)
                {
                    return(null);
                }
            }

            if (isClosed.HasValue)
            {
                conditions.Add(Exp.Eq("is_closed", isClosed));
            }

            if (tags != null && tags.Any())
            {
                ids = SearchByTags(EntityType.Case, ids.ToArray(), tags);

                if (ids.Count == 0)
                {
                    return(null);
                }
            }

            if (ids.Count > 0)
            {
                if (exceptIDs.Count > 0)
                {
                    ids = ids.Except(exceptIDs).ToList();

                    if (ids.Count == 0)
                    {
                        return(null);
                    }
                }

                conditions.Add(Exp.In("id", ids));
            }
            else if (exceptIDs.Count > 0)
            {
                conditions.Add(!Exp.In("id", exceptIDs.ToArray()));
            }

            if (conditions.Count == 0)
            {
                return(null);
            }

            return(conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j));
        }