private string GetFilterBy()
{
var filter = String.Empty;
var info = new SearchTextInfo(SearchText);
var searchText = info.SearchText;
var sqlSearchText = info.SqlSearchText;
var searchTextIsNumber = info.SearchTextIsNumber;
var searchTextIsPhone = info.SearchTextIsPhone;
switch (SearchBy)
{
case SearchUserBy.Auto: {
if (searchTextIsNumber)
{
filter += String.Format(@" u.Id = {0} or s.Id = {0} ", searchText);
}
else
{
filter = AddFilterCriteria(filter,
String.Format(@"
LOWER(u.Login) like '{0}' or
LOWER(u.Name) like '{0}' or
LOWER(s.Name) like '{0}' ",
sqlSearchText));
}
if (searchTextIsPhone && searchText.Length >= 6)
{
filter += String.Format(" or (REPLACE(Contacts.ContactText, '-', '') like '{0}' and Contacts.Type = 1)" +
" or (REPLACE(ContactsAddresses.ContactText, '-', '') like '{0}' and ContactsAddresses.Type = 1) ",
sqlSearchText.Replace("-", ""));
}
break;
}
case SearchUserBy.ByClientName: {
filter = AddFilterCriteria(filter,
String.Format(" LOWER(s.Name) like '{0}'", sqlSearchText));
break;
}
case SearchUserBy.ByClientId: {
filter = AddFilterCriteria(filter, String.Format(" s.Id = {0} ", searchTextIsNumber ? searchText : "-1"));
break;
}
case SearchUserBy.ByJuridicalName: {
filter = AddFilterCriteria(filter, String.Format(" LOWER(p.JuridicalName) like '{0}'", sqlSearchText));
break;
}
case SearchUserBy.ByLogin: {
filter = AddFilterCriteria(filter, String.Format(" LOWER(u.Login) like '{0}' ", sqlSearchText));
break;
}
case SearchUserBy.ByPayerId: {
filter = AddFilterCriteria(filter, String.Format(" u.PayerId = {0} ", searchTextIsNumber ? searchText : "-1"));
break;
}
case SearchUserBy.ByUserId: {
filter = AddFilterCriteria(filter, String.Format(" u.Id = {0} ", searchTextIsNumber ? searchText : "-1"));
break;
}
case SearchUserBy.ByUserName: {
filter = AddFilterCriteria(filter, String.Format(" LOWER(u.Name) like '{0}' ", sqlSearchText));
break;
}
case SearchUserBy.ByContacts: {
if (searchTextIsPhone || searchTextIsNumber)
{
filter = AddFilterCriteria(filter,
String.Format(@"
(REPLACE(Contacts.ContactText, '-', '') like '{0}' and Contacts.Type = 1 ) or
(REPLACE(ContactsAddresses.ContactText, '-', '') like '{0}' and ContactsAddresses.Type = 1 ) or
(REPLACE(PayerCont.ContactText, '-', '') like '{0}' and PayerCont.Type = 1 )
", sqlSearchText.Replace("-", "")));
}
else
{
filter = AddFilterCriteria(filter,
String.Format(@"
(LOWER(Contacts.ContactText) like '{0}' and Contacts.Type = 0 and ((cg.Specialized = false) or (cg.id = ifnull(u.ContactGroupId, (cg.Specialized = false))))) or
(LOWER(ContactsAddresses.ContactText) like '{0}' and ContactsAddresses.Type = 0 and ((cga.Specialized = false) or (cga.id = ifnull(u.ContactGroupId, (cga.Specialized = false))))) or
(LOWER(ContactsPerson.ContactText) like '{0}' and ContactsPerson.Type = 0) or
(LOWER(PayerCont.ContactText) like '{0}' and PayerCont.Type = 0)
", sqlSearchText));
}
break;
}
case SearchUserBy.ByPersons: {
filter = AddFilterCriteria(filter, String.Format(" LOWER(Persons.Name) like '{0}' ", sqlSearchText));
break;
}
case SearchUserBy.AddressMail:
filter = AddFilterCriteria(filter, String.Format(" (concat(a.Id, '@waybills.analit.net') = '{0}' or concat(a.Id, '@refused.analit.net') = '{0}') ", sqlSearchText.Replace("%", string.Empty)));
break;
}
return(filter);
}
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);
}
