public IList <UserSearchItem> Find() { var administrator = SecurityContext.Administrator; var orderFilter = String.Format("ORDER BY {0} {1}", GetSortProperty(), GetSortDirection()); var filter = String.Empty; var oldSearchText = SearchText; SearchText = MiniMailPrepare(); filter = AddFilterCriteria(filter, GetFilterBy()); filter = AddFilterCriteria(filter, GetTypeFilter(ClientType)); filter = AddFilterCriteria(filter, GetStatusFilter(SearchStatus)); if (!String.IsNullOrEmpty(filter)) { filter = String.Format(" and ({0}) ", filter); } var regionMask = administrator.RegionMask; if (Region != null) { regionMask &= Region.Id; } var sqlStr = String.Format(@" SELECT u.Id as UserId, u.Login as Login, u.Name as UserName, u.PayerId as PayerId, u.Enabled as UserEnabled, if (max(uui.UpdateDate) >= max(uui.UncommitedUpdateDate), uui.UpdateDate, uui.UncommitedUpdateDate) as UpdateDate, if (uui.UpdateDate is not null, if (max(uui.UpdateDate) >= max(uui.UncommitedUpdateDate), 0, 1), 0) as UpdateIsUncommited, max(uui.AFAppVersion) as AFVersion, if(s.Type = 0, 2, 1) as ClientType, if(rcs.InvisibleOnFirm = 2, 1, 0) as InvisibleClient, s.Id as ClientId, s.Name as ClientName, s.Disabled as ServiceDisabled, p.JuridicalName as JuridicalName, r.Region as RegionName, r.RegionCode as RegionCode, u.AllowDownloadUnconfirmedOrders as AllowDownloadUnconfirmedOrders, u.SubmitOrders as SubmitOrders, u.IgnoreCheckMinOrder as IgnoreCheckMinOrder FROM Customers.Users u join usersettings.UserUpdateInfo uui ON uui.UserId = u.Id join Customers.Services s on s.Id = u.RootService join farm.Regions r ON r.RegionCode = s.HomeRegion left join Customers.Suppliers sup on sup.Id = u.RootService left join Customers.Clients ON Clients.Id = u.ClientId left join Usersettings.RetClientsSet rcs on rcs.ClientCode = Clients.Id left join Customers.UserAddresses ua on ua.UserId = u.Id left join Customers.Addresses a on a.Id = ua.AddressId left join contacts.contact_groups cg ON cg.ContactGroupOwnerId = ifnull(Clients.ContactGroupOwnerId, sup.ContactGroupOwnerId) left join contacts.contact_groups cga ON a.ContactGroupId = cga.Id left join contacts.RegionalDeliveryGroups rdg on rdg.ContactGroupId = cg.Id left join contacts.RegionalDeliveryGroups rdga on rdga.ContactGroupId = cga.Id left join contacts.Contacts ON Contacts.ContactOwnerId = cg.Id and if(rdg.ContactGroupId is not null, (rdg.RegionId & sup.RegionMask > 0), 1) left join contacts.Contacts as ContactsAddresses ON ContactsAddresses.ContactOwnerId = cga.Id and if(rdga.ContactGroupId is not null, (rdga.RegionId & sup.RegionMask > 0), 1) left join contacts.Persons ON Persons.ContactGroupId = cg.Id and if(rdg.ContactGroupId is not null, (rdg.RegionId & sup.RegionMask > 0), 1) left join contacts.Contacts as ContactsPerson on ContactsPerson.ContactOwnerId = Persons.Id join Billing.Payers p on p.PayerId = u.PayerId left join contacts.payerownercontacts poc on poc.Payer = p.PayerId left join contacts.Contacts PayerCont on PayerCont.Id = poc.Contact WHERE ((Clients.MaskRegion & :RegionMask > 0) or (sup.RegionMask & :RegionMask > 0)) {0} GROUP BY u.Id {1} ", filter, orderFilter); var result = session.CreateSQLQuery(sqlStr) .SetParameter("RegionMask", regionMask) .ToList <UserSearchItem>(); ArHelper.Evict(session, result); var logins = result.Select(t => t.Login).ToList(); var adInfo = ADHelper.GetPartialUsersInformation(logins); for (var i = 0; i < result.Count; i++) { if (adInfo == null) { continue; } result[i].DisabledInAd = adInfo[result[i].Login].DisabledInAd; result[i].IsLocked = adInfo[result[i].Login].IsLocked; result[i].IsLoginExists = adInfo[result[i].Login].IsLoginExists; } var info = new SearchTextInfo(SearchText); if (result.Count > 0 && ((info.SearchTextIsPhone && info.SearchText.Length >= 6) || SearchBy == SearchUserBy.ByContacts)) { var findedUsers = result.Select(r => r.UserId).Implode(); var findInUsers = session.CreateSQLQuery(string.Format(@" select u.Id from Customers.Users u left join Customers.UserAddresses ua on ua.UserId = u.Id left join Customers.Addresses a on a.Id = ua.AddressId left join contacts.contact_groups cg ON u.ContactGroupId = cg.Id left join contacts.contact_groups cga ON a.ContactGroupId = cga.Id left join Customers.Suppliers sup on sup.Id = u.RootService left join Customers.Clients ON Clients.Id = u.ClientId left join contacts.RegionalDeliveryGroups rdg on rdg.ContactGroupId = cg.Id left join contacts.RegionalDeliveryGroups rdga on rdga.ContactGroupId = cga.Id left join contacts.Contacts ON Contacts.ContactOwnerId = cg.Id and if(rdg.ContactGroupId is not null, (rdg.RegionId & sup.RegionMask > 0), 1) left join contacts.Contacts as ContactAddress ON ContactAddress.ContactOwnerId = cga.Id and if(rdga.ContactGroupId is not null, (rdga.RegionId & sup.RegionMask > 0), 1) where ((Clients.MaskRegion & :RegionMask > 0) or (sup.RegionMask & :RegionMask > 0)) and ((REPLACE(Contacts.ContactText, '-', '') like '{0}' and Contacts.Type = 1) or (REPLACE(ContactAddress.ContactText, '-', '') like '{0}' and ContactAddress.Type = 1)) and u.Id in ({1}) ", info.SqlSearchText.Replace("-", ""), findedUsers)) .SetParameter("RegionMask", regionMask) .List <uint>(); if (findInUsers.Count > 0) { var bufResult = result.Where(r => findInUsers.Contains(r.UserId)).ToList(); result = bufResult; } } SearchText = oldSearchText; return(result); }