public static bool TrySelectCase(string text, out List <int> result)
{
var success = false;
result = new List <int>();
List <int> casesId;
if (FactoryIndexer <CasesWrapper> .TrySelectIds(s => s.MatchAll(text), out casesId))
{
result.AddRange(casesId);
success = true;
}
IReadOnlyCollection <FieldsWrapper> casesCustom;
if (FactoryIndexer <FieldsWrapper> .TrySelect(s => s.MatchAll(text).Where(r => r.EntityType, 7), out casesCustom))
{
result.AddRange(casesCustom.Select(r => r.EntityId).ToList());
success = true;
}
IReadOnlyCollection <EventsWrapper> events;
if (!FactoryIndexer <EventsWrapper> .TrySelect(s => s.MatchAll(text).Where(r => r.EntityType, 7).Gt(r => r.EntityId, 0), out events))
{
result.AddRange(events.Select(r => r.EntityId).ToList());
success = true;
}
return(success);
}
public static bool TrySelectContact(string text, out List <int> result)
{
var success = false;
result = new List <int>();
List <int> contactsId;
if (FactoryIndexer <ContactsWrapper> .TrySelectIds(s => s.MatchAll(text), out contactsId))
{
result.AddRange(contactsId);
success = true;
}
IReadOnlyCollection <InfoWrapper> infos;
if (FactoryIndexer <InfoWrapper> .TrySelect(s => s.MatchAll(text), out infos))
{
result.AddRange(infos.Select(r => r.ContactId).ToList());
success = true;
}
IReadOnlyCollection <FieldsWrapper> personCustom;
if (FactoryIndexer <FieldsWrapper> .TrySelect(s => s.MatchAll(text).In(r => r.EntityType, new[] { 0, 4, 5 }), out personCustom))
{
result.AddRange(personCustom.Select(r => r.EntityId).ToList());
success = true;
}
IReadOnlyCollection <EventsWrapper> events;
if (FactoryIndexer <EventsWrapper> .TrySelect(s => s.MatchAll(text).Gt(r => r.ContactId, 0), out events))
{
result.AddRange(events.Select(r => r.ContactId).ToList());
success = true;
}
return(success);
}
internal IList <Bookmark> SearchBookmarks(IEnumerable <string> searchStringList, int firstResult, int maxResults, bool skipCountQuery)
{
try
{
if (searchStringList == null)
{
return(new List <Bookmark>());
}
searchStringList = searchStringList
.Select(w => (w ?? string.Empty).Trim().Trim('/', '\\').ToLower())
.Where(w => w.Length >= 3);
if (!searchStringList.Any())
{
return(new List <Bookmark>());
}
IReadOnlyCollection <BookmarksUserWrapper> bookmarks;
if (FactoryIndexer <BookmarksUserWrapper> .TrySelect(r => r.MatchAll(string.Join(" ", searchStringList.ToArray())), out bookmarks))
{
return(GetBookmarksByIDs(bookmarks.Select(r => r.BookmarkID).ToList()));
}
var sb = new StringBuilder();
sb.Append(@" from bookmarking_bookmark b, bookmarking_tag t, bookmarking_bookmarktag g
where b.Tenant = @Tenant and g.BookmarkID = b.ID and t.TagID = g.TagID ");
var i = 0;
foreach (var w in searchStringList)
{
sb.AppendFormat(@" and ( lower(b.url) like @w{0} or lower(b.name) like @w{0} or lower(b.description) like @w{0} or lower(t.name) like @w{0} ) ", i);
i++;
}
sb.Append(@" order by b.Date desc ");
var query = DbManager.Connection.CreateCommand("select distinct b.ID, b.URL, b.Date, b.Name, b.Description, b.UserCreatorID" + sb + " limit @FirstResult, @MaxResults")
.AddParameter("Tenant", Tenant)
.AddParameter("FirstResult", firstResult)
.AddParameter("MaxResults", maxResults);
var countQuery = DbManager.Connection.CreateCommand("select count(distinct b.ID)" + sb)
.AddParameter("Tenant", Tenant);
i = 0;
foreach (var w in searchStringList)
{
query.AddParameter("w" + i, "%" + w + "%");
countQuery.AddParameter("w" + i, "%" + w + "%");
i++;
}
if (!skipCountQuery)
{
SetBookmarksCount(countQuery.ExecuteScalar <long>());
}
var bookmakrs = query.ExecuteList().ConvertAll(Converters.ToBookmark);
return(bookmakrs);
}
catch
{
return(null);
}
}
public static bool TryGetFullTextSearchChains(MailSearchFilterData filter, string user,
out List <MailWrapper> mailWrappers)
{
mailWrappers = new List <MailWrapper>();
if (!FactoryIndexer <MailWrapper> .Support)
{
return(false);
}
var userId = new Guid(user);
Selector <MailWrapper> selector = null;
if (!string.IsNullOrEmpty(filter.SearchText))
{
selector = new Selector <MailWrapper>().MatchAll(filter.SearchText);
}
if (!string.IsNullOrEmpty(filter.FromAddress))
{
Selector <MailWrapper> tempSelector;
if (filter.PrimaryFolder == FolderType.Sent || filter.PrimaryFolder == FolderType.Draft)
{
tempSelector = new Selector <MailWrapper>().Match(s => s.ToText, filter.FromAddress);
}
else
{
tempSelector = new Selector <MailWrapper>().Match(s => s.FromText, filter.FromAddress);
}
if (selector != null)
{
selector &= tempSelector;
}
else
{
selector = tempSelector;
}
}
if (!string.IsNullOrEmpty(filter.ToAddress))
{
Selector <MailWrapper> tempSelector;
if (filter.PrimaryFolder == FolderType.Sent || filter.PrimaryFolder == FolderType.Draft)
{
tempSelector = new Selector <MailWrapper>().Match(s => s.FromText, filter.ToAddress);
}
else
{
tempSelector = new Selector <MailWrapper>().Match(s => s.ToText, filter.ToAddress);
}
if (selector != null)
{
selector &= tempSelector;
}
else
{
selector = tempSelector;
}
}
if (selector == null)
{
selector = new Selector <MailWrapper>();
}
selector.Where(r => r.Folder, (int)filter.PrimaryFolder);
if (filter.MailboxId.HasValue)
{
selector.Where(r => r.MailboxId, filter.MailboxId.Value);
}
if (filter.Unread.HasValue)
{
selector.Where(s => s.Unread, filter.Unread.Value);
}
if (filter.Important.HasValue)
{
selector.Where(s => s.Importance, filter.Important.Value);
}
if (filter.Attachments.HasValue)
{
selector.Where(s => s.HasAttachments, filter.Attachments.Value);
}
if (filter.PrimaryFolder == FolderType.UserFolder && filter.UserFolderId.HasValue)
{
selector.InAll(s => s.UserFolders.Select(f => f.Id), new[] { filter.UserFolderId.Value });
}
if (filter.WithCalendar.HasValue)
{
selector.Where(m => m.WithCalendar, filter.WithCalendar.Value);
}
if (filter.CustomLabels != null && filter.CustomLabels.Any())
{
selector.InAll(r => r.Tags.Select(t => t.Id), filter.CustomLabels.ToArray());
}
if (filter.FromDate.HasValue)
{
if (filter.PrevFlag.GetValueOrDefault(false))
{
selector.Ge(r => r.ChainDate, filter.FromDate.Value);
}
else
{
selector.Le(r => r.ChainDate, filter.FromDate.Value);
}
}
if (filter.PeriodFrom.HasValue && filter.PeriodTo.HasValue)
{
var fromTs = TimeSpan.FromMilliseconds(filter.PeriodFrom.Value);
var from = Defines.BaseJsDateTime.Add(fromTs);
var toTs = TimeSpan.FromMilliseconds(filter.PeriodTo.Value);
var to = Defines.BaseJsDateTime.Add(toTs);
selector.Ge(s => s.DateSent, from);
selector.Le(s => s.DateSent, to);
}
if (filter.Page.HasValue)
{
selector.Limit(filter.Page.Value, filter.PageSize.GetValueOrDefault(25));
}
else if (filter.PageSize.HasValue)
{
selector.Limit(0, filter.PageSize.Value);
}
selector.Where(r => r.UserId, userId)
.Sort(r => r.ChainDate, filter.SortOrder == Defines.ASCENDING);
IReadOnlyCollection <MailWrapper> result;
if (!FactoryIndexer <MailWrapper> .TrySelect(s => selector, out result))
{
return(false);
}
mailWrappers = result.ToList();
return(true);
}
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.Substatus.HasValue)
{
var substatus = filter.Substatus.Value;
if (substatus > -1)
{
query.InnerJoin(StatusTable + " pstat", Exp.EqColumns("pstat.tenant_id", "t.tenant_id") & Exp.Eq("pstat.id", filter.Substatus.Value));
query.Where(Exp.Eq("t.status_id", filter.Substatus.Value) | (Exp.Eq("t.status_id", null) & Exp.EqColumns("t.status", "pstat.statusType") & Exp.Eq("pstat.isDefault", true)));
}
else
{
query.Where("t.status", -substatus).Where("t.status_id", null);
}
}
else if (filter.TaskStatuses.Count != 0)
{
var status = filter.TaskStatuses.First();
query.Where("t.status", status);
}
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))
{
List <int> taskIds;
if (FactoryIndexer <TasksWrapper> .TrySelectIds(s => s.MatchAll(filter.SearchText), out taskIds))
{
IReadOnlyCollection <SubtasksWrapper> subtaskIds;
if (FactoryIndexer <SubtasksWrapper> .TrySelect(s => s.MatchAll(filter.SearchText), out subtaskIds))
{
taskIds.AddRange(subtaskIds.Select(r => r.Task).ToList());
}
query.Where(Exp.In("t.id", taskIds));
}
else
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
}
return(query);
}
