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());
}
}
