public ElsevierMaterials.Models.Domain.Material.MaterialBasicInfo GetMaterialInfo(int materialId, int sourceMaterialId, int sourceId, int subgroupId, IMaterialsContextUow context) { FullTextSearch fts = context.FullTextSearch.GetMaterialById(materialId); ElsevierMaterials.Models.Domain.Material.MaterialBasicInfo material = null; IService service = new TotalMateriaService(); material = service.GetMaterialSubgroupListFromService(System.Web.HttpContext.Current.Session["TotalMateriaSession"].ToString(), materialId, sourceMaterialId).Where(m => m.MaterialId == materialId && m.SubgroupId == subgroupId).Select(m => new ElsevierMaterials.Models.Domain.Material.MaterialBasicInfo { MaterialId = m.MaterialId, Name = m.Name, SourceId = m.SourceId, SourceMaterialId = m.SourceMaterialId, Standard = m.Standard, SubgroupId = m.SubgroupId, SubgroupName = (m.SourceText == "-" ? "" : m.SourceText.Replace("<br>", " ").Replace("<br >", " ").Replace("<br />", " ").Replace("<br/>", " ") + "; ") + (m.Standard == "-" ? "" : m.Standard.Replace("<br>", " ").Replace("<br >", " ").Replace("<br />", " ").Replace("<br/>", " ") + "; ") + (m.Specification == "-" ? "" : m.Specification.Replace("<br>", " ").Replace("<br >", " ").Replace("<br />", " ").Replace("<br/>", " ")) }).FirstOrDefault(); if (material != null) { material.Name = fts.material_designation; } return(material); }
public void SearchAsync_ShouldFailIfQueryIsNullOrEmpty() { Mock <ITransaction> tx = new Mock <ITransaction>(); Mock <IReliableDictionary <string, CachedItem <SearchOutput> > > cache = new Mock <IReliableDictionary <string, CachedItem <SearchOutput> > >(); Mock <IGitHubClient> gitHubClient = new Mock <IGitHubClient>(); SearchInput input = new SearchInput { Query = "" }; Result <SearchOutput> result = FullTextSearch.SearchAsync( input: input, cache: cache.Object, tx: tx.Object, gitHubClient: gitHubClient.Object).Result; Assert.False(result.Success); }
private static bool TryGetFullTextSearchIds(MailFilter filter, out List <int> ids) { ids = new List <int>(); var actionSucceed = false; if (!string.IsNullOrEmpty(filter.SearchText) && FullTextSearch.SupportModule(FullTextSearch.MailModule)) { var mailModule = FullTextSearch.MailModule.Match(filter.SearchText) .OrderBy(MailTable.Columns.DateSent, filter.SortOrder == "ascending"); if (filter.PrimaryFolder != 1 && filter.PrimaryFolder != 2) { mailModule.AddAttribute("folder", filter.PrimaryFolder); } else { mailModule.AddAttribute("folder", new[] { 1, 2 }); } ids = FullTextSearch.Search(mailModule); actionSucceed = true; } if (!string.IsNullOrEmpty(filter.FindAddress) && FullTextSearch.SupportModule(FullTextSearch.MailModule)) { var tmpIds = FullTextSearch.Search(FullTextSearch.MailModule.Match(filter.FindAddress, filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts ? MailTable.Columns.To : MailTable.Columns.From)); actionSucceed = true; if (tmpIds.Any()) { ids = ids.Any() ? ids.Where(id => tmpIds.Exists(tid => tid == id)).ToList() : tmpIds; } } return(actionSucceed); }
public IdeaSearchResults GetAll(IdeaSearchSettings searchsettings, int currentUserId) { IdeaSearchResults results = new IdeaSearchResults(); using (var context = new IdeaPoolEntitiesFullTextSearch()) { var query = context.Ideas.AsQueryable(); query = query.Where(x => !x.IsDraft || (x.IsDraft && x.UserId == currentUserId)); if (searchsettings.UserId != 0) { query = query.Where(x => x.UserId == searchsettings.UserId); } if (searchsettings.FieldOfWaterId != 0) { query = query.Where(x => x.IdeasFieldOfWaters.Any(fieldofwater => fieldofwater.FieldOfWaterId == searchsettings.FieldOfWaterId)); } if (searchsettings.StatusId != 0) { query = query.Where(x => x.IdeaStatusId == searchsettings.StatusId); } if (!string.IsNullOrEmpty(searchsettings.SearchText)) { string searchtext = FullTextSearch.Search(searchsettings.SearchText); query = query.Where(x => x.Title.Contains(searchtext) || x.HtmlContent.Contains(searchtext)); } results.TotalCount = query.Count(); query = query .Include(x => x.IdeasFieldOfWaters.Select(field => field.FieldOfWater)) .Include(x => x.IdeaStatus) .Include(x => x.User) .Include(x => x.UnReadDiscussions) .OrderByDescending(x => x.CreatedDate); if (searchsettings.PageLength != 0) { query = query .Skip((searchsettings.CurrentPage - 1) * searchsettings.PageLength) .Take(searchsettings.PageLength); } List <Idea> ideas = query.ToList(); results.Results = MapIdeas(ideas, currentUserId); } return(results); }
private bool FilterRow(object row) { if (string.IsNullOrEmpty(FullTextSearch)) { return(true); } string filterString = FullTextSearch.ToLower(); bool found = false; TrackListItem trackListItem = row as TrackListItem; if (CatalogView.CompareString(trackListItem.Title, filterString)) { found = true; } return(found); }
private bool FilterRow(object row) { if (string.IsNullOrEmpty(FullTextSearch)) { return(true); } string filterString = FullTextSearch.ToLower(); bool found = false; AlbumListItem trackListItem = row as AlbumListItem; if (trackListItem.Title.IndexOf(filterString, StringComparison.InvariantCultureIgnoreCase) >= 0) { found = true; } return(found); }
private bool FilterRow(object row) { if (string.IsNullOrEmpty(FullTextSearch)) { return(true); } string filterString = FullTextSearch.ToLower(); bool found = false; AlbumViewItem cdItem = row as AlbumViewItem; if (CatalogView.CompareString(cdItem.Artist, filterString)) { found = true; } if (CatalogView.CompareString(cdItem.Title, filterString)) { found = true; } if (CatalogView.CompareString(cdItem.Year, filterString)) { found = true; } if (CatalogView.CompareString(cdItem.Genre, filterString)) { found = true; } if (CatalogView.CompareString(cdItem.ArchiveNumber, filterString)) { found = true; } return(found); }
public IEnumerable <Folder> Search(string text, params FolderType[] folderTypes) { if (string.IsNullOrEmpty(text)) { return(new List <Folder>()); } if (FullTextSearch.SupportModule(FullTextSearch.FileModule)) { var ids = FullTextSearch.Search(FullTextSearch.FileFolderModule.Match(text)); using (var dbManager = GetDb()) { return(dbManager .ExecuteList(GetFolderQuery(Exp.In("id", ids))) .ConvertAll(ToFolder) .Where(f => folderTypes.Contains(f.RootFolderType))); } } else { var keywords = text.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .Where(k => 3 <= k.Trim().Length) .ToList(); if (keywords.Count == 0) { return(new List <Folder>()); } var where = Exp.Empty; keywords.ForEach(k => where &= Exp.Like("title", k)); using (var dbManager = GetDb()) { return(dbManager .ExecuteList(GetFolderQuery(where)) .ConvertAll(ToFolder) .Where(f => folderTypes.Contains(f.RootFolderType))); } } }
public void RewriteFullTextQuery(DbCommand cmd) { for (int i = 0; i < cmd.Parameters.Count; i++) { var parameter = cmd.Parameters[i]; if (parameter.DbType != DbType.String) { continue; } var value = parameter.Value as string; if (value == null || !value.StartsWith(_prefix, StringComparison.Ordinal)) { continue; } var oldText = cmd.CommandText; cmd.CommandText = Regex.Replace(oldText, $@"\(?CONTAINS\((\[\w+\]\.\[\w+\]),\s*@{parameter.ParameterName}(?:,\s*(@\w+|\d+)?)\)\)?\s*=\s*1", match => { var lang = match.Groups[2].Success ? ", LANGUAGE " + match.Groups[2].Value : ""; return($"CONTAINS({match.Groups[1].Value}, @{parameter.ParameterName}{lang})"); }); if (oldText != cmd.CommandText) { parameter.Size = 4000; var len = _prefix.Length; var exp = value.Substring(len, value.Length - len); if (_option != FullTextParseOption.NoParse) { if (_option == FullTextParseOption.ParseAsPrefix) { exp = exp + '*'; } exp = FullTextSearch.Parse(exp); } parameter.Value = exp; } } }
private List <Guid> SearchPostsInternal(string searchText) { if (FullTextSearch.SupportModule(FullTextSearch.BlogsModule)) { return(FullTextSearch .Search(searchText, FullTextSearch.BlogsModule) .GetIdentifiers() .Select(id => new Guid(id)) .ToList()); } var keyWords = new List <string>(searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)); keyWords.RemoveAll(kw => kw.Length < 3); var wordResults = new Dictionary <string, int>(keyWords.Count); var counts = new Dictionary <Guid, int>(); var wordsCount = 0; foreach (var word in keyWords.Where(word => !wordResults.ContainsKey(word))) { wordsCount++; var wordResult = _storage.GetPostDao().SearchPostsByWord(word); wordResults.Add(word, wordResult.Count); wordResult.ForEach( pid => { if (counts.ContainsKey(pid)) { counts[pid] = counts[pid] + 1; } else { counts.Add(pid, 1); } }); } return((from kw in counts where kw.Value == wordsCount select kw.Key).ToList()); }
public IEnumerable <File> Search(String searchText, FolderType folderType) { if (FullTextSearch.SupportModule(FullTextSearch.FileModule)) { var ids = FullTextSearch.Search(FullTextSearch.FileModule.Match(searchText)); return(dbManager .ExecuteList(GetFileQuery(Exp.In("id", ids) & Exp.Eq("current_version", true))) .ConvertAll(ToFile) .Where( f => folderType == FolderType.BUNCH ? f.RootFolderType == FolderType.BUNCH : f.RootFolderType == FolderType.USER | f.RootFolderType == FolderType.COMMON) .ToList()); } else { var keywords = searchText .Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .Where(k => 3 <= k.Trim().Length) .ToArray(); if (keywords.Length == 0) { return(Enumerable.Empty <File>()); } var q = GetFileQuery(Exp.Eq("f.current_version", true) & BuildLike(new[] { "f.title" }, keywords)); return(dbManager .ExecuteList(q) .ConvertAll(ToFile) .Where(f => folderType == FolderType.BUNCH ? f.RootFolderType == FolderType.BUNCH : f.RootFolderType == FolderType.USER | f.RootFolderType == FolderType.COMMON) .ToList()); } }
public List <Page> SearchPagesByContent(string content) { if (string.IsNullOrEmpty(content)) { return(new List <Page>()); } IEnumerable <string> pagenames = null; if (FullTextSearch.SupportModule(FullTextSearch.WikiModule)) { pagenames = FullTextSearch.Search(content, FullTextSearch.WikiModule).GetIdentifiers(); } else { var keys = content.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .Select(k => k.Trim()) .Where(k => 3 <= k.Length); var where = Exp.Empty; foreach (var k in keys) { where &= Exp.Like("h.pagename", k) | Exp.Like("h.body", k); } var q = Query("wiki_pages p") .Select("p.pagename") .InnerJoin("wiki_pages_history h", Exp.EqColumns("p.tenant", "h.tenant") & Exp.EqColumns("p.pagename", "h.pagename") & Exp.EqColumns("p.version", "h.version")) .Where(where) .OrderBy("p.modified_on", false) .SetMaxResults(MAX_FIND); pagenames = db .ExecuteList(q) .ConvertAll(r => (string)r[0]); } return(GetPages(pagenames)); }
public int GetMailContactsCount(int tenant, string user, MailContactsFilter filter) { if (filter == null) { throw new ArgumentNullException("filter"); } int result; using (var db = GetDb()) { SqlQuery queryContacts; if (string.IsNullOrEmpty(filter.SearchFilter)) { queryContacts = new SqlQuery(ContactsTable.Name) .SelectCount() .Where(GetUserWhere(user, tenant)); if (filter.Type.HasValue) { queryContacts = queryContacts.Where(ContactsTable.Columns.Type, filter.Type); } } else { const string mail_contacts = "mc"; const string contact_info = "ci"; queryContacts = new SqlQuery(ContactsTable.Name.Alias(mail_contacts)) .SelectCount("distinct " + ContactsTable.Columns.Id.Prefix(mail_contacts)) .InnerJoin(ContactInfoTable.Name.Alias(contact_info), Exp.EqColumns(ContactsTable.Columns.Id.Prefix(mail_contacts), ContactInfoTable.Columns.ContactId.Prefix(contact_info))) .Where(ContactsTable.Columns.Tenant.Prefix(mail_contacts), tenant) .Where(ContactsTable.Columns.User.Prefix(mail_contacts), user); if (filter.Type.HasValue) { queryContacts = queryContacts.Where(ContactsTable.Columns.Type.Prefix(mail_contacts), filter.Type); } if (FullTextSearch.SupportModule(FullTextSearch.MailContactsModule)) { var ids = FullTextSearch.Search(FullTextSearch.MailContactsModule.Match(filter.SearchFilter)); queryContacts = queryContacts.Where(Exp.In(ContactInfoTable.Columns.Id, ids)); } else { queryContacts = queryContacts.Where( Exp.Or(Exp.Like(mail_contacts + '.' + ContactsTable.Columns.Description, filter.SearchFilter, SqlLike.StartWith), Exp.Or(Exp.Like(mail_contacts + '.' + ContactsTable.Columns.ContactName, filter.SearchFilter, SqlLike.StartWith), Exp.Like(contact_info + '.' + ContactInfoTable.Columns.Data, filter.SearchFilter, SqlLike.StartWith)))); } } result = db.ExecuteList(queryContacts).ConvertAll(r => Convert.ToInt32(r[0])).FirstOrDefault(); } return(result); }
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess) { if (filter.Follow) { IEnumerable <string> objects = 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)); } 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 (ProjectsCommonSettings.Load().HideEntitiesInPausedProjects) { query.Where(!Exp.Eq("p.status", ProjectStatus.Paused)); } 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) { if (filter.TagId == -1) { query.LeftOuterJoin(ProjectTagTable + " pt", Exp.EqColumns("pt.project_id", "t.project_id")); query.Where("pt.tag_id", null); } else { 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 (filter.MessageStatus.HasValue) { query.Where("t.status", filter.MessageStatus.Value); } if (!string.IsNullOrEmpty(filter.SearchText)) { if (FullTextSearch.SupportModule(FullTextSearch.ProjectsMessagesModule)) { var mIds = FullTextSearch.Search(FullTextSearch.ProjectsMessagesModule.Match(filter.SearchText)); query.Where(Exp.In("t.id", mIds)); } else { query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere)); } } 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) & !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); }
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))); }
public SearchResultItem[] Search(String searchText) { var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .ToArray(); if (keywords.Length == 0) { return(new List <SearchResultItem>().ToArray()); } var caseModules = GetFullTextSearchModule(EntityType.Case, searchText); var contactModules = GetFullTextSearchModule(EntityType.Contact, searchText); var opportunityModules = GetFullTextSearchModule(EntityType.Opportunity, searchText); var taskModules = GetFullTextSearchModule(EntityType.Task, searchText); _fullTextSearchEnable = FullTextSearch.SupportModule(caseModules) && FullTextSearch.SupportModule(contactModules) && FullTextSearch.SupportModule(opportunityModules) && FullTextSearch.SupportModule(taskModules); if (_fullTextSearchEnable) { _findedIDs = new Dictionary <EntityType, IEnumerable <int> > { { EntityType.Case, FullTextSearch.Search(caseModules) }, { EntityType.Contact, FullTextSearch.Search(contactModules) }, { EntityType.Opportunity, FullTextSearch.Search(opportunityModules) }, { EntityType.Task, FullTextSearch.Search(taskModules) } }; } else { _findedIDs = SearchByCustomFields(keywords) .Union(SearchByRelationshipEvent(keywords)) .Union(SearchByContactInfos(keywords)) .ToLookup(pair => pair.Key, pair => pair.Value) .ToDictionary(group => group.Key, group => group.First()); } var searchQuery = GetSearchQuery(keywords); if (searchQuery == null) { return(new SearchResultItem[0]); } using (var db = GetDb()) { return(ToSearchResultItem(db.ExecuteList(searchQuery))); } }
private Exp BuildWhereExp(EntityType entityType, String[] keywords) { Exp where = Exp.Empty; if (_findedIDs.ContainsKey(entityType)) { where = Exp.In("id", _findedIDs[entityType].ToArray()); } if (FullTextSearch.SupportModule(GetFullTextSearchModule(entityType, ""))) { return(where); } Exp byField; switch (entityType) { case EntityType.Contact: byField = BuildLike(new[] { "first_name", "last_name", "company_name", "title", "notes" }, keywords); break; case EntityType.Opportunity: byField = BuildLike(new[] { "title", "description" }, keywords); break; case EntityType.Task: byField = BuildLike(new[] { "title", "description" }, keywords); break; case EntityType.Case: byField = BuildLike(new[] { "title" }, keywords); break; case EntityType.Invoice: byField = BuildLike(new[] { "number", "description" }, keywords); break; default: throw new ArgumentException(); } if (where != Exp.Empty) { where &= byField; } else { where = byField; } return(where); }
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(); if (keywords.Length > 0) { if (FullTextSearch.SupportModule(FullTextSearch.CRMDealsModule)) { ids = FullTextSearch.Search(searchText, FullTextSearch.CRMDealsModule) .GetIdentifiers() .Select(item => Convert.ToInt32(item.Split('_')[1])).Distinct().ToList(); 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)); }
public SearchResultItem[] Search(String searchText) { var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .ToArray(); if (keywords.Length == 0) { return(new List <SearchResultItem>().ToArray()); } _fullTextSearchEnable = FullTextSearch.SupportModule(FullTextSearch.CRMCasesModule) && FullTextSearch.SupportModule(FullTextSearch.CRMContactsModule) && FullTextSearch.SupportModule(FullTextSearch.CRMDealsModule) && FullTextSearch.SupportModule(FullTextSearch.CRMTasksModule); if (_fullTextSearchEnable) { _findedIDs = new Dictionary <EntityType, IEnumerable <string> >(); _findedIDs.Add(EntityType.Case, FullTextSearch.Search(searchText, FullTextSearch.CRMCasesModule) .GetIdentifiers() .Select(item => item.Split('_')[1]).Distinct() ); _findedIDs.Add(EntityType.Contact, FullTextSearch.Search(searchText, FullTextSearch.CRMContactsModule) .GetIdentifiers() .Select(item => item.Split('_')[1]).Distinct() ); _findedIDs.Add(EntityType.Opportunity, FullTextSearch.Search(searchText, FullTextSearch.CRMDealsModule) .GetIdentifiers() .Select(item => item.Split('_')[1]).Distinct() ); _findedIDs.Add(EntityType.Task, FullTextSearch.Search(searchText, FullTextSearch.CRMTasksModule) .GetIdentifiers() ); } else { _findedIDs = SearchByCustomFields(keywords) .Union(SearchByRelationshipEvent(keywords)) .Union(SearchByContactInfos(keywords)) .ToLookup(pair => pair.Key, pair => pair.Value) .ToDictionary(group => group.Key, group => group.First()); } var searchQuery = GetSearchQuery(keywords); if (searchQuery == null) { return(new SearchResultItem[0]); } using (var db = GetDb()) { return(ToSearchResultItem(db.ExecuteList(searchQuery))); } }
public List <string> SearchCRMContacts(int tenant_id, string user_id, string search_text) { var contacts = new List <string>(); if (!string.IsNullOrEmpty(search_text)) { #region Set up connection to CRM sequrity try { CoreContext.TenantManager.SetCurrentTenant(tenant_id); SecurityContext.AuthenticateMe(CoreContext.Authentication.GetAccountByID(new Guid(user_id))); if (!DbRegistry.IsDatabaseRegistered("crm")) { DbRegistry.RegisterDatabase("crm", ConfigurationManager.ConnectionStrings["crm"]); } } catch (Exception e) { _log.Error("Error connecting to CRM database", e); return(contacts); } #endregion using (var db = new DbManager("crm")) { SqlQuery q; if (FullTextSearch.SupportModule(FullTextSearch.CRMEmailsModule)) { var ids = FullTextSearch.Search(search_text + "*", FullTextSearch.CRMEmailsModule) .GetIdentifiers() .Select(id => int.Parse(id)); q = new SqlQuery("crm_contact c") .Select("c.id", "c.is_company", "trim(concat(c.first_name, ' ', c.last_name, ' ', c.company_name))", "i.data") .InnerJoin("crm_contact_info i", Exp.EqColumns("c.tenant_id", "i.tenant_id") & Exp.EqColumns("c.id", "i.contact_id")) .Where(Exp.In("i.id", ids.Take(FULLTEXTSEARCH_IDS_COUNT).ToList())) .Where("i.type", (int)ContactInfoType.Email); } else { search_text = search_text.Replace("\"", "\\\""); q = new SqlQuery("crm_contact c") .Select("c.id", "c.is_company", "trim(concat(c.first_name, ' ', c.last_name, ' ', c.company_name))", "i.data") .InnerJoin("crm_contact_info i", Exp.EqColumns("c.tenant_id", "i.tenant_id") & Exp.EqColumns("c.id", "i.contact_id")) .Where("c.tenant_id", tenant_id) .Where("i.type", (int)ContactInfoType.Email) .Where("(concat(c.display_name, ' ', i.data) like '%" + search_text + "%')"); } var result = db.ExecuteList(q) .ConvertAll(r => new { Id = Convert.ToInt32(r[0]), Company = Convert.ToBoolean(r[1]), DisplayName = Convert.ToString(r[2]), Email = Convert.ToString(r[3]) }); foreach (var r in result) { var contact = r.Company ? (Contact) new Company() : new Person(); contact.ID = r.Id; if (CRMSecurity.CanAccessTo(contact)) { contacts.Add("\"" + r.DisplayName + "\" <" + r.Email + ">"); } } } } return(contacts); }
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.ProjectsMilestonesModule)) { 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 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)); }
public IList Search(String text, int projectId) { var projWhere = Exp.Empty; var mileWhere = Exp.Empty; var messWhere = Exp.Empty; var taskWhere = Exp.Empty; if (FullTextSearch.SupportModule(FullTextSearch.ProjectsModule)) { var searched = FullTextSearch.Search(text, FullTextSearch.ProjectsModule); var projIds = GetIdentifiers(searched, projectId, EntityType.Project); if (0 < projIds.Length) { projWhere = Exp.In("id", projIds); } var mileIds = GetIdentifiers(searched, projectId, EntityType.Milestone); if (0 < mileIds.Length) { mileWhere = Exp.In("t.id", mileIds); } var messIds = GetIdentifiers(searched, projectId, EntityType.Message); if (0 < messIds.Length) { messWhere = Exp.In("t.id", messIds); } var taskIds = GetIdentifiers(searched, projectId, EntityType.Task); if (0 < taskIds.Length) { taskWhere = Exp.In("t.id", taskIds); } } else { var keywords = text.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .Where(k => 3 <= k.Trim().Length) .ToArray(); if (keywords.Length == 0) { return(new ArrayList()); } var projIdWhere = 0 < projectId?Exp.Eq("p.id", projectId) : Exp.Empty; projWhere = BuildLike(new[] { "title", "description" }, keywords, true) & projIdWhere; mileWhere = BuildLike(new[] { "t.title" }, keywords, true) & projIdWhere; messWhere = BuildLike(new[] { "t.title", "t.content" }, keywords, true) & projIdWhere; taskWhere = BuildLike(new[] { "t.title", "t.description" }, keywords, true) & projIdWhere; } var result = new ArrayList(); if (projWhere != Exp.Empty) { var projDao = new ProjectDao(DatabaseId, Tenant); result.AddRange(projDao.GetProjects(projWhere)); } if (mileWhere != Exp.Empty) { var mileDao = new MilestoneDao(DatabaseId, Tenant); result.AddRange(mileDao.GetMilestones(mileWhere)); } if (messWhere != Exp.Empty) { var messDao = new MessageDao(DatabaseId, Tenant); result.AddRange(messDao.GetMessages(messWhere)); } if (taskWhere != Exp.Empty) { var taskDao = new TaskDao(DatabaseId, Tenant); result.AddRange(taskDao.GetTasks(taskWhere)); } return(result); }
public List <string> SearchMailContacts(int tenant, string user, string searchText, int maxCount) { var contacts = new List <string>(); if (string.IsNullOrEmpty(searchText) || maxCount <= 0) { return(contacts); } const string mail_contacts = "mc"; const string contact_info = "ci"; Func <SqlQuery> getBaseQuery = () => { var query = new SqlQuery(ContactsTable.Name.Alias(mail_contacts)) .InnerJoin(ContactInfoTable.Name.Alias(contact_info), Exp.EqColumns(ContactsTable.Columns.Id.Prefix(mail_contacts), ContactInfoTable.Columns.ContactId.Prefix(contact_info))) .Select( ContactsTable.Columns.ContactName.Prefix(mail_contacts), ContactInfoTable.Columns.Data.Prefix(contact_info)) .Where(ContactInfoTable.Columns.User.Prefix(contact_info), user) .Where(ContactInfoTable.Columns.Tenant.Prefix(contact_info), tenant) .Where(ContactInfoTable.Columns.Type.Prefix(contact_info), (int)ContactInfoType.Email); return(query); }; var ids = new List <int>(); if (FullTextSearch.SupportModule(FullTextSearch.MailContactsModule)) { ids = FullTextSearch.Search(FullTextSearch.MailContactsModule.Match(searchText)); if (!ids.Any()) { return(contacts); } } const int count_per_query = 100; using (var db = GetDb()) { var f = 0; do { var query = getBaseQuery(); if (ids.Any()) { var partIds = ids.Skip(f).Take(count_per_query).ToList(); if (!partIds.Any()) { break; } query .Where(Exp.In(ContactInfoTable.Columns.Id.Prefix(contact_info), partIds)); } else { query .Where( Exp.Like( string.Format("concat({0}, ' <', {1},'>')", ContactsTable.Columns.ContactName.Prefix(mail_contacts), ContactInfoTable.Columns.Data.Prefix(contact_info)), searchText.Replace("\"", "\\\""), SqlLike.AnyWhere)) .SetMaxResults(maxCount); } var partContacts = db.ExecuteList(query) .ConvertAll(r => string.IsNullOrEmpty(r[0] as string) ? r[1] as string : MailUtil.CreateFullEmail((string)r[0], r[1] as string)); foreach (var partContact in partContacts.TakeWhile(partContact => maxCount - contacts.Count != 0)) { contacts.Add(partContact); } if (maxCount - contacts.Count == 0 || !partContacts.Any() || partContacts.Count < count_per_query) { break; } f += count_per_query; } while (true); } return(contacts); }
private Exp WhereConditional(String tblAlias, ICollection <int> exceptIDs, String searchText, InvoiceStatus?status, DateTime issueDateFrom, DateTime issueDateTo, DateTime dueDateFrom, DateTime dueDateTo, EntityType entityType, int entityID, String currency) { var tblAliasPrefix = !String.IsNullOrEmpty(tblAlias) ? tblAlias + "." : ""; var conditions = new List <Exp>(); if (entityID > 0) { switch (entityType) { case EntityType.Contact: case EntityType.Person: case EntityType.Company: conditions.Add(Exp.Eq(tblAliasPrefix + "contact_id", entityID)); break; case EntityType.Case: case EntityType.Opportunity: conditions.Add(Exp.Eq(tblAliasPrefix + "entity_id", entityID) & Exp.Eq(tblAliasPrefix + "entity_type", (int)entityType)); break; } } if (status != null) { conditions.Add(Exp.Eq(tblAliasPrefix + "status", (int)status.Value)); } if (!String.IsNullOrEmpty(searchText)) { searchText = searchText.Trim(); var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .ToArray(); if (keywords.Length > 0) { if (FullTextSearch.SupportModule(FullTextSearch.CRMInvoicesModule)) { var ids = FullTextSearch.Search(FullTextSearch.CRMInvoicesModule.Match(searchText)); conditions.Add(Exp.In(tblAliasPrefix + "id", ids)); } else { conditions.Add(BuildLike(new[] { tblAliasPrefix + "number", tblAliasPrefix + "description" }, keywords)); } } } if (exceptIDs.Count > 0) { conditions.Add(!Exp.In(tblAliasPrefix + "id", exceptIDs.ToArray())); } if (issueDateFrom != DateTime.MinValue && issueDateTo != DateTime.MinValue) { conditions.Add(Exp.Between(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateFrom), TenantUtil.DateTimeToUtc(issueDateTo.AddDays(1).AddMinutes(-1)))); } else if (issueDateFrom != DateTime.MinValue) { conditions.Add(Exp.Ge(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateFrom))); } else if (issueDateTo != DateTime.MinValue) { conditions.Add(Exp.Le(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateTo.AddDays(1).AddMinutes(-1)))); } if (dueDateFrom != DateTime.MinValue && dueDateTo != DateTime.MinValue) { conditions.Add(Exp.Between(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateFrom), TenantUtil.DateTimeToUtc(dueDateTo.AddDays(1).AddMinutes(-1)))); } else if (dueDateFrom != DateTime.MinValue) { conditions.Add(Exp.Ge(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateFrom))); } else if (dueDateTo != DateTime.MinValue) { conditions.Add(Exp.Le(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateTo.AddDays(1).AddMinutes(-1)))); } if (!String.IsNullOrEmpty(currency)) { conditions.Add(Exp.Eq(tblAliasPrefix + "currency", currency)); } if (conditions.Count == 0) { return(null); } return(conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j)); }
public static Exp GetMailFilterConditions(MailFilter filter, bool skipFolder, string alias = "") { var conditions = Exp.Empty; if (!skipFolder) { conditions = Exp.Eq(MailTable.Columns.Folder.Prefix(alias), filter.PrimaryFolder); } if (filter.Unread.HasValue) { conditions &= Exp.Eq(MailTable.Columns.Unread.Prefix(alias), filter.Unread); } if (filter.Attachments.HasValue) { conditions &= Exp.Gt(MailTable.Columns.AttachCount.Prefix(alias), 0); } if (filter.PeriodFrom.HasValue && filter.PeriodTo.HasValue) { var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodFrom.Value * 10000); var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodTo.Value * 10000) + new TimeSpan(1, 0, 0, 0, 0); // 1 day was added to make the "To" date limit inclusive conditions &= Exp.Between(MailTable.Columns.DateSent.Prefix(alias), from, to); } if (filter.Important.HasValue) { conditions &= Exp.Eq(MailTable.Columns.Importance.Prefix(alias), true); } if (filter.WithCalendar.HasValue) { conditions &= !Exp.Eq(MailTable.Columns.CalendarUid.Prefix(alias), null); } if (!string.IsNullOrEmpty(filter.FindAddress) && !FullTextSearch.SupportModule(FullTextSearch.MailModule)) { if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts) { conditions &= Exp.Like(MailTable.Columns.To.Prefix(alias), filter.FindAddress, SqlLike.AnyWhere); } else { conditions &= Exp.Like(MailTable.Columns.From.Prefix(alias), filter.FindAddress, SqlLike.AnyWhere); } } if (filter.MailboxId.HasValue) { conditions &= Exp.Eq(MailTable.Columns.MailboxId.Prefix(alias), filter.MailboxId.Value); } if (!string.IsNullOrEmpty(filter.SearchText) && !FullTextSearch.SupportModule(FullTextSearch.MailModule)) { conditions &= Exp.Or(Exp.Like(MailTable.Columns.From.Prefix(alias), filter.SearchText, SqlLike.AnyWhere), Exp.Or( Exp.Like(MailTable.Columns.To.Prefix(alias), filter.SearchText, SqlLike.AnyWhere), Exp.Or( Exp.Like(MailTable.Columns.Cc.Prefix(alias), filter.SearchText, SqlLike.AnyWhere), Exp.Or( Exp.Like(MailTable.Columns.Bcc.Prefix(alias), filter.SearchText, SqlLike.AnyWhere), Exp.Like(MailTable.Columns.Subject.Prefix(alias), filter.SearchText, SqlLike.AnyWhere))))); } return(conditions); }
private bool IncludeToSearch(EntityType entityType) { return(!FullTextSearch.SupportModule(GetFullTextSearchModule(entityType, "")) || _findedIDs.ContainsKey(entityType)); }
private SqlQuery CreateQueryFilterBase(SqlQuery query, TaskFilter filter) { if (filter.ProjectIds.Count != 0) { query.Where(Exp.In("t.project_id", filter.ProjectIds)); } else { if (ProjectsCommonSettings.Load().HideEntitiesInPausedProjects) { query.Where(!Exp.Eq("p.status", ProjectStatus.Paused)); } 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) { if (filter.TagId == -1) { query.LeftOuterJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id")); query.Where("ptag.tag_id", null); } else { 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.Equals(Guid.Empty) || (filter.ParticipantId.HasValue && !filter.ParticipantId.Value.Equals(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.Equals(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.Value.Equals(Guid.Empty)) { existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.Value.ToString())); existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.Value.ToString())); } query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible)); } if (!string.IsNullOrEmpty(filter.SearchText)) { if (FullTextSearch.SupportModule(FullTextSearch.ProjectsTasksModule)) { var taskIds = FullTextSearch.Search(FullTextSearch.ProjectsTasksModule.Match(filter.SearchText)); if (FullTextSearch.SupportModule(FullTextSearch.ProjectsSubtasksModule)) { taskIds.AddRange(FullTextSearch.Search(FullTextSearch.ProjectsSubtasksModule.Select("task_id").Match(filter.SearchText))); } query.Where(Exp.In("t.id", taskIds)); } else { query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere)); } } return(query); }
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.Equals(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.Equals(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); }
protected void BindSettings() { if (PortalConfig.UseFullTextSearch.HasValue) { trFTSNotInstalled.Visible = false; trFTSNotEnabled.Visible = false; trFTSEnabled.Visible = true; if ((bool)PortalConfig.UseFullTextSearch) { ftsStat.Visible = true; try { FullTextSearchInfo info = FullTextSearch.GetInformation(); lbIndSize.Text = Util.CommonHelper.ByteSizeToStr(info.IndexSize * 1024 * 1024); lbStat.Text = info.PopulateStatus.ToString(); ftsInfoOK.Visible = true; ftsInfoFailed.Visible = false; } catch (Exception ex) { ftsInfoOK.Visible = false; ftsInfoFailed.Visible = true; lbFTSErrorMessage.Text = ex.Message; } } else { trFTSEnabled.Visible = false; ftsStat.Visible = false; trFTSNotEnabled.Visible = true; } } else { if (!FullTextSearch.IsInstalled()) { trFTSNotInstalled.Visible = true; trFTSNotEnabled.Visible = false; trFTSEnabled.Visible = false; } if (FullTextSearch.IsInstalled()) { trFTSNotInstalled.Visible = false; trFTSNotEnabled.Visible = true; trFTSEnabled.Visible = false; } } if (PortalConfig.UseWebDav.HasValue) { if ((bool)PortalConfig.UseWebDav) { trWDDisabled.Visible = false; trWDEnabled.Visible = true; } else { trWDDisabled.Visible = true; trWDEnabled.Visible = false; } } else { trWDDisabled.Visible = true; trWDEnabled.Visible = false; } SmtpBox box = OutgoingEmailServiceConfig.FindSmtpBox(OutgoingEmailServiceType.SendFile, false); if (box != null) { CommonHelper.SafeSelect(ddSmtpBoxes, box.SmtpBoxId.ToString()); } }