public IEnumerable <MailContactData> GetContacts(string search, int?contactType, int?pageSize, int fromIndex, string sortorder)
{
var filter = new MailContactsFilter
{
SearchFilter = search,
Type = contactType,
StartIndex = fromIndex,
Count = pageSize.GetValueOrDefault(25),
SortOrder = sortorder
};
var contacts = MailBoxManager.GetMailContacts(
TenantId,
Username,
filter);
int totalCount;
if (contacts.Any() && contacts.Count() < pageSize)
{
totalCount = fromIndex + contacts.Count();
}
else
{
totalCount = MailBoxManager.GetMailContactsCount(TenantId,
Username,
filter);
}
_context.SetTotalCount(totalCount);
return(contacts.ToContactData());
}
public List <ContactCardDto> GetMailContacts(int tenant, string user, MailContactsFilter filter)
{
if (filter == null)
{
throw new ArgumentNullException("filter");
}
var result = new List <ContactCardDto>();
const string mail_contacts = "mc";
const string contact_info = "ci";
var sortOrder = filter.SortOrder == "ascending";
using (var db = GetDb())
{
var ids = GetFilteredContactIds(db, tenant, user, filter);
if (!ids.Any())
{
return(result);
}
var queryContacts = 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.Id.Prefix(mail_contacts),
ContactsTable.Columns.User.Prefix(mail_contacts),
ContactsTable.Columns.Tenant.Prefix(mail_contacts),
ContactsTable.Columns.ContactName.Prefix(mail_contacts),
ContactsTable.Columns.Description.Prefix(mail_contacts),
ContactsTable.Columns.Type.Prefix(mail_contacts),
ContactsTable.Columns.HasPhoto.Prefix(mail_contacts),
ContactInfoTable.Columns.Id.Prefix(contact_info),
ContactInfoTable.Columns.Tenant.Prefix(contact_info),
ContactInfoTable.Columns.User.Prefix(contact_info),
ContactInfoTable.Columns.ContactId.Prefix(contact_info),
ContactInfoTable.Columns.Data.Prefix(contact_info),
ContactInfoTable.Columns.Type.Prefix(contact_info),
ContactInfoTable.Columns.IsPrimary.Prefix(contact_info))
.Where(ContactsTable.Columns.Tenant.Prefix(mail_contacts), tenant)
.Where(ContactsTable.Columns.User.Prefix(mail_contacts), user)
.Where(Exp.In(ContactsTable.Columns.Id.Prefix(mail_contacts), ids))
.OrderBy(ContactsTable.Columns.ContactName.Prefix(mail_contacts), sortOrder);
result = db.ExecuteList(queryContacts).ToContactCardDto();
}
return(result);
}
private List <int> GetFilteredContactIds(DbManager db, int tenant, string user, MailContactsFilter filter)
{
SqlQuery queryContacts;
var sortOrder = filter.SortOrder == "ascending";
const string mail_contacts = "mc";
const string contact_info = "ci";
if (string.IsNullOrEmpty(filter.SearchFilter))
{
queryContacts = new SqlQuery(ContactsTable.Name.Alias(mail_contacts))
.Select(ContactsTable.Columns.Id.Prefix(mail_contacts));
}
else
{
queryContacts = new SqlQuery(ContactsTable.Name.Alias(mail_contacts))
.Select(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)))
.Distinct();
if (FullTextSearch.SupportModule(FullTextSearch.MailContactsModule))
{
var ids = FullTextSearch.Search(FullTextSearch.MailContactsModule.Match(filter.SearchFilter));
queryContacts = queryContacts.Where(Exp.In(ContactInfoTable.Columns.Id.Prefix(contact_info), ids));
}
else
{
queryContacts = queryContacts.Where(
Exp.Or(
Exp.Like(mail_contacts + '.' + ContactsTable.Columns.Description, filter.SearchFilter, SqlLike.StartWith),
Exp.Or(
Exp.Like(ContactsTable.Columns.ContactName.Prefix(mail_contacts), filter.SearchFilter, SqlLike.StartWith),
Exp.Like(ContactInfoTable.Columns.Data.Prefix(contact_info), filter.SearchFilter, SqlLike.StartWith))));
}
}
if (filter.Type.HasValue)
{
queryContacts = queryContacts.Where(ContactsTable.Columns.Type.Prefix(mail_contacts), filter.Type);
}
queryContacts = queryContacts.Where(ContactsTable.Columns.Tenant.Prefix(mail_contacts), tenant)
.Where(ContactsTable.Columns.User.Prefix(mail_contacts), user)
.OrderBy(ContactsTable.Columns.ContactName.Prefix(mail_contacts), sortOrder)
.SetFirstResult(filter.StartIndex)
.SetMaxResults(filter.Count);
return(db.ExecuteList(queryContacts).ConvertAll(r => Convert.ToInt32(r[0])));
}
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);
}
