public IEnumerable<ContactWrapper> GetContacts(IEnumerable<String> tags, int contactType, ContactListViewType contactListView)
{
IEnumerable<ContactWrapper> result;
OrderBy contactsOrderBy;
ContactSortedByType sortBy;
var searchString = _context.FilterValue;
if (ASC.Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out sortBy))
contactsOrderBy = new OrderBy(sortBy, !_context.SortDescending);
else if (String.IsNullOrEmpty(_context.SortBy))
contactsOrderBy = new OrderBy(ContactSortedByType.DisplayName, true);
else
contactsOrderBy = null;
if (contactsOrderBy != null)
{
result = ToListContactWrapper(DaoFactory.GetContactDao()
.GetContacts(searchString,
tags,
contactType,
contactListView,
(int) _context.StartIndex,
(int) _context.Count,
contactsOrderBy));
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToListContactWrapper(DaoFactory.GetContactDao()
.GetContacts(searchString,
tags,
contactType,
contactListView,
0,
0,
null));
}
var totalCount = DaoFactory.GetContactDao().GetContactsCount(searchString,
tags,
contactType,
contactListView);
_context.SetTotalCount(totalCount);
return result.ToSmartList();
}
public List <Cases> GetCases(
String searchText,
int contactID,
bool?isClosed,
IEnumerable <String> tags,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetCasesSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) &&
contactID <= 0 &&
isClosed == null &&
(tags == null || !tags.Any()));
var whereConditional = WhereConditional(CRMSecurity.GetPrivateItems(typeof(Cases)).ToList(), searchText,
contactID, isClosed,
tags);
if (withParams && whereConditional == null)
{
return(new List <Cases>());
}
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
sqlQuery.OrderBy("is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(SortedByType), orderBy.SortedBy))
{
switch ((SortedByType)orderBy.SortedBy)
{
case SortedByType.Title:
sqlQuery.OrderBy("title", orderBy.IsAsc);
break;
case SortedByType.CreateBy:
sqlQuery.OrderBy("create_by", orderBy.IsAsc);
break;
case SortedByType.DateAndTime:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
}
}
return(Db.ExecuteList(sqlQuery).ConvertAll(ToCases));
}
private List <Deal> GetCrudeDeals(
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
var withParams = !(String.IsNullOrEmpty(searchText) &&
responsibleID == Guid.Empty &&
milestoneID <= 0 &&
(tags == null || !tags.Any()) &&
contactID <= 0 &&
stageType == null &&
contactAlsoIsParticipant == null &&
fromDate == DateTime.MinValue &&
toDate == DateTime.MinValue);
var sqlQuery = GetDbDealByFilters(new List <int>(),
searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
orderBy);
if (withParams && sqlQuery == null)
{
return(new List <Deal>());
}
if (0 < from && from < int.MaxValue)
{
sqlQuery = sqlQuery.Skip(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery = sqlQuery.Take(count);
}
return(_mapper.Map <List <DbDeal>, List <Deal> >(sqlQuery.ToList()));
}
public List <Cases> GetCases(
String searchText,
int contactID,
bool?isClosed,
IEnumerable <String> tags,
int from,
int count,
OrderBy orderBy)
{
var dbCasesQuery = GetDbCasesByFilters(_crmSecurity.GetPrivateItems(typeof(Cases)).ToList(), searchText,
contactID, isClosed,
tags);
if (dbCasesQuery == null)
{
return(new List <Cases>());
}
if (0 < from && from < int.MaxValue)
{
dbCasesQuery = dbCasesQuery.Skip(from);
}
if (0 < count && count < int.MaxValue)
{
dbCasesQuery = dbCasesQuery.Take(count);
}
dbCasesQuery = dbCasesQuery.OrderBy("IsClosed", orderBy.IsAsc);
if (orderBy != null && Enum.IsDefined(typeof(Enums.SortedByType), orderBy.SortedBy))
{
switch ((SortedByType)orderBy.SortedBy)
{
case SortedByType.Title:
dbCasesQuery = dbCasesQuery.OrderBy("Title", orderBy.IsAsc);
break;
case SortedByType.CreateBy:
dbCasesQuery = dbCasesQuery.OrderBy("CreateBy", orderBy.IsAsc);
break;
case SortedByType.DateAndTime:
dbCasesQuery = dbCasesQuery.OrderBy("CreateOn", orderBy.IsAsc);
break;
}
}
return(_mapper.Map <List <DbCase>, List <Cases> >(dbCasesQuery.ToList()));
}
public List<Invoice> GetInvoices(
String searchText,
InvoiceStatus? status,
DateTime issueDateFrom,
DateTime issueDateTo,
DateTime dueDateFrom,
DateTime dueDateTo,
EntityType entityType,
int entityID,
String currency,
int from,
int count,
OrderBy orderBy)
{
if (CRMSecurity.IsAdmin)
return GetCrudeInvoices(
searchText,
status,
issueDateFrom,
issueDateTo,
dueDateFrom,
dueDateTo,
entityType,
entityID,
currency,
from,
count,
orderBy);
var crudeInvoices = GetCrudeInvoices(
searchText,
status,
issueDateFrom,
issueDateTo,
dueDateFrom,
dueDateTo,
entityType,
entityID,
currency,
0,
from + count,
orderBy);
if (crudeInvoices.Count == 0) return crudeInvoices;
if (crudeInvoices.Count < from + count) return CRMSecurity.FilterRead(crudeInvoices).Skip(from).ToList();
var result = CRMSecurity.FilterRead(crudeInvoices).ToList();
if (result.Count == crudeInvoices.Count) return result.Skip(from).ToList();
var localCount = count;
var localFrom = from + count;
while (true)
{
crudeInvoices = GetCrudeInvoices(
searchText,
status,
issueDateFrom,
issueDateTo,
dueDateFrom,
dueDateTo,
entityType,
entityID,
currency,
localFrom,
localCount,
orderBy);
if (crudeInvoices.Count == 0) break;
result.AddRange(CRMSecurity.FilterRead(crudeInvoices));
if ((result.Count >= count + from) || (crudeInvoices.Count < localCount)) break;
localFrom += localCount;
localCount = localCount * 2;
}
return result.Skip(from).Take(count).ToList();
}
public IEnumerable<ContactWithTaskWrapper> GetSimpleContacts(
IEnumerable<string> tags,
int? contactStage,
int? contactType,
ContactListViewType contactListView,
Guid? responsibleid,
bool? isShared,
ApiDateTime fromDate,
ApiDateTime toDate)
{
IEnumerable<ContactWithTaskWrapper> result;
OrderBy contactsOrderBy;
ContactSortedByType sortBy;
var searchString = _context.FilterValue;
if (Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out sortBy))
{
contactsOrderBy = new OrderBy(sortBy, !_context.SortDescending);
}
else if (String.IsNullOrEmpty(_context.SortBy))
{
contactsOrderBy = new OrderBy(ContactSortedByType.DisplayName, true);
}
else
{
contactsOrderBy = null;
}
var fromIndex = (int)_context.StartIndex;
var count = (int)_context.Count;
var contactStageInt = contactStage.HasValue ? contactStage.Value : -1;
var contactTypeInt = contactType.HasValue ? contactType.Value : -1;
if (contactsOrderBy != null)
{
result = ToSimpleListContactWrapper(DaoFactory.GetContactDao().GetContacts(
searchString,
tags,
contactStageInt,
contactTypeInt,
contactListView,
fromDate,
toDate,
fromIndex,
count,
contactsOrderBy,
responsibleid,
isShared));
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToSimpleListContactWrapper(DaoFactory.GetContactDao().GetContacts(
searchString,
tags,
contactStageInt,
contactTypeInt,
contactListView,
fromDate,
toDate,
0,
0,
null,
responsibleid,
isShared));
}
int totalCount;
if (result.Count() < count)
{
totalCount = fromIndex + result.Count();
}
else
{
totalCount = DaoFactory.GetContactDao().GetContactsCount(
searchString,
tags,
contactStageInt,
contactTypeInt,
contactListView,
fromDate,
toDate,
responsibleid,
isShared);
}
_context.SetTotalCount(totalCount);
return result;
}
private List <Deal> GetCrudeDeals(
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetDealSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) &&
responsibleID == Guid.Empty &&
milestoneID <= 0 &&
(tags == null || !tags.Any()) &&
contactID <= 0 &&
stageType == null &&
contactAlsoIsParticipant == null &&
fromDate == DateTime.MinValue &&
toDate == DateTime.MinValue);
var whereConditional = WhereConditional(new List <int>(),
searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant);
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Having(Exp.Between("close_date", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate)));
}
else if (withParams && whereConditional == null)
{
return(new List <Deal>());
}
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
if (orderBy != null && Enum.IsDefined(typeof(DealSortedByType), orderBy.SortedBy))
{
switch ((DealSortedByType)orderBy.SortedBy)
{
case DealSortedByType.Title:
sqlQuery.OrderBy("tblDeal.title", orderBy.IsAsc);
break;
case DealSortedByType.BidValue:
sqlQuery.OrderBy("tblDeal.bid_value", orderBy.IsAsc);
break;
case DealSortedByType.Responsible:
sqlQuery.OrderBy("tblDeal.responsible_id", orderBy.IsAsc)
.OrderBy("tblDM.sort_order", orderBy.IsAsc)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.actual_close_date", false)
.OrderBy("tblDeal.expected_close_date", true)
.OrderBy("tblDeal.title", true);
break;
case DealSortedByType.Stage:
sqlQuery.OrderBy("tblDM.sort_order", orderBy.IsAsc)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.actual_close_date", false)
.OrderBy("tblDeal.expected_close_date", true)
.OrderBy("tblDeal.title", true);
break;
case DealSortedByType.DateAndTime:
sqlQuery.OrderBy("close_date", orderBy.IsAsc);
break;
default:
throw new ArgumentException();
}
}
else
{
sqlQuery.OrderBy("tblDM.sort_order", true)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.title", true);
}
return(Db.ExecuteList(sqlQuery).ConvertAll(ToDeal));
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
String alias,
Guid responsibleID,
int categoryID,
bool? isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
var aliasPrefix = !String.IsNullOrEmpty(alias) ? alias + "." : "";
if (responsibleID != Guid.Empty)
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
if (entityID > 0)
switch (entityType)
{
case EntityType.Contact:
var isCompany = true;
using (var db = GetDb())
{
isCompany = db.ExecuteScalar<bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID)));
}
if (isCompany)
return WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy);
else
return WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy);
case EntityType.Person:
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In(aliasPrefix + "contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq(aliasPrefix + "entity_id", entityID) &
Exp.Eq(aliasPrefix + "entity_type", (int)entityType));
break;
}
if (isClosed.HasValue)
sqlQuery.Where(aliasPrefix + "is_closed", isClosed);
if (categoryID > 0)
sqlQuery.Where(Exp.Eq(aliasPrefix + "category_id", categoryID));
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Between(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
else if (fromDate != DateTime.MinValue)
sqlQuery.Where(Exp.Ge(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate)));
else if (toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Le(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
if (0 < from && from < int.MaxValue)
sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue)
sqlQuery.SetMaxResults(count);
sqlQuery.OrderBy(aliasPrefix + "is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
{
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy(aliasPrefix + "title", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy(aliasPrefix + "deadline", orderBy.IsAsc)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy(aliasPrefix + "category_id", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.ContactManager:
sqlQuery.LeftOuterJoin("core_user u", Exp.EqColumns(aliasPrefix + "responsible_id", "u.id"))
.OrderBy("case when u.lastname is null or u.lastname = '' then 1 else 0 end, u.lastname", orderBy.IsAsc)
.OrderBy("case when u.firstname is null or u.firstname = '' then 1 else 0 end, u.firstname", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Contact:
sqlQuery.LeftOuterJoin("crm_contact c_tbl", Exp.EqColumns(aliasPrefix + "contact_id", "c_tbl.id"))
.OrderBy("case when c_tbl.display_name is null then 1 else 0 end, c_tbl.display_name", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
}
}
else
{
sqlQuery
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
}
return sqlQuery;
}
public List<Task> GetTasks(
String searchText,
Guid responsibleID,
int categoryID,
bool? isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
if (CRMSecurity.IsAdmin)
return GetCrudeTasks(
searchText,
responsibleID,
categoryID,
isClosed,
fromDate,
toDate,
entityType,
entityID,
from,
count,
orderBy);
var crudeTasks = GetCrudeTasks(
searchText,
responsibleID,
categoryID,
isClosed,
fromDate,
toDate,
entityType,
entityID,
0,
from + count,
orderBy);
if (crudeTasks.Count == 0) return crudeTasks;
if (crudeTasks.Count < from + count) return CRMSecurity.FilterRead(crudeTasks).Skip(from).ToList();
var result = CRMSecurity.FilterRead(crudeTasks).ToList();
if (result.Count == crudeTasks.Count) return result.Skip(from).ToList();
var localCount = count;
var localFrom = from + count;
while (true)
{
crudeTasks = GetCrudeTasks(
searchText,
responsibleID,
categoryID,
isClosed,
fromDate,
toDate,
entityType,
entityID,
localFrom,
localCount,
orderBy);
if (crudeTasks.Count == 0) break;
result.AddRange(CRMSecurity.FilterRead(crudeTasks));
if ((result.Count >= count + from) || (crudeTasks.Count < localCount)) break;
localFrom += localCount;
localCount = localCount * 2;
}
return result.Skip(from).Take(count).ToList();
}
public int GetTasksCount(
String searchText,
Guid responsibleID,
int categoryID,
bool? isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int count,
OrderBy orderBy)
{
var sqlQuery = Query("crm_task").SelectCount();
sqlQuery = WhereConditional(sqlQuery, responsibleID,
categoryID, isClosed, fromDate, toDate, entityType, entityID, 0, count,
orderBy);
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
if (keywords.Length > 0)
if (FullTextSearch.SupportModule(FullTextSearch.CRMTasksModule))
{
var taskIDs = FullTextSearch.Search(searchText, FullTextSearch.CRMTasksModule)
.GetIdentifiers()
.Select(item => Convert.ToInt32(item)).ToArray();
if (taskIDs.Length != 0)
sqlQuery.Where(Exp.In("id", taskIDs));
else
return 0;
}
else
sqlQuery.Where(BuildLike(new[] { "title" }, keywords));
}
return DbManager.ExecuteScalar<int>(sqlQuery);
}
public IEnumerable <RelationshipEventWrapper> GetHistory(
string entityType,
int entityId,
int categoryId,
Guid createBy,
ApiDateTime fromDate,
ApiDateTime toDate)
{
var entityTypeObj = ToEntityType(entityType);
switch (entityTypeObj)
{
case EntityType.Contact:
var contact = DaoFactory.ContactDao.GetByID(entityId);
if (contact == null || !CRMSecurity.CanAccessTo(contact))
{
throw new ItemNotFoundException();
}
break;
case EntityType.Case:
var cases = DaoFactory.CasesDao.GetByID(entityId);
if (cases == null || !CRMSecurity.CanAccessTo(cases))
{
throw new ItemNotFoundException();
}
break;
case EntityType.Opportunity:
var deal = DaoFactory.DealDao.GetByID(entityId);
if (deal == null || !CRMSecurity.CanAccessTo(deal))
{
throw new ItemNotFoundException();
}
break;
default:
if (entityId != 0)
{
throw new ArgumentException();
}
break;
}
RelationshipEventByType eventByType;
IEnumerable <RelationshipEventWrapper> result;
OrderBy eventOrderBy;
if (Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out eventByType))
{
eventOrderBy = new OrderBy(eventByType, !_context.SortDescending);
}
else if (string.IsNullOrEmpty(_context.SortBy))
{
eventOrderBy = new OrderBy(RelationshipEventByType.Created, false);
}
else
{
eventOrderBy = null;
}
if (eventOrderBy != null)
{
result = ToListRelationshipEventWrapper(DaoFactory.RelationshipEventDao.GetItems(
_context.FilterValue,
entityTypeObj,
entityId,
createBy,
categoryId,
fromDate,
toDate,
(int)_context.StartIndex,
(int)_context.Count,
eventOrderBy));
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToListRelationshipEventWrapper(DaoFactory.RelationshipEventDao.GetItems(
_context.FilterValue,
entityTypeObj,
entityId,
createBy,
categoryId,
fromDate,
toDate,
0,
0,
null));
}
return(result.ToSmartList());
}
public List<Contact> GetContacts(String searchText, IEnumerable<String> tags, int contactStatus, ContactListViewType contactListView, int from,
int count, OrderBy orderBy)
{
if (CRMSecurity.IsAdmin)
return GetCrudeContacts(
searchText,
tags,
contactStatus,
contactListView,
from,
count,
orderBy);
var crudeContacts = GetCrudeContacts(
searchText,
tags,
contactStatus,
contactListView,
0,
from + count,
orderBy);
if (crudeContacts.Count == 0) return crudeContacts;
if (crudeContacts.Count < from + count) return crudeContacts
.FindAll(CRMSecurity.CanAccessTo).Skip(from).ToList();
var result = crudeContacts.FindAll(CRMSecurity.CanAccessTo);
if (result.Count == crudeContacts.Count) return result.Skip(from).ToList();
var localCount = count;
var localFrom = from + count;
while (true)
{
crudeContacts = GetCrudeContacts(
searchText,
tags,
contactStatus,
contactListView,
localFrom,
localCount,
orderBy);
if (crudeContacts.Count == 0) break;
result.AddRange(crudeContacts.Where(CRMSecurity.CanAccessTo));
if ((result.Count >= count + from) || (crudeContacts.Count < localCount)) break;
localFrom += localCount;
localCount = localCount * 2;
}
return result.Skip(from).Take(count).ToList();
}
public List<Cases> GetCases(
String searchText,
int contactID,
bool? isClosed,
IEnumerable<String> tags,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetCasesSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) &&
contactID <= 0 &&
isClosed == null &&
(tags == null || !tags.Any()));
var whereConditional = WhereConditional(CRMSecurity.GetPrivateItems(typeof(Cases)).ToList(), searchText,
contactID, isClosed,
tags);
if (withParams && whereConditional == null)
return new List<Cases>();
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue) sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue) sqlQuery.SetMaxResults(count);
sqlQuery.OrderBy("is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(SortedByType), orderBy.SortedBy))
switch ((SortedByType)orderBy.SortedBy)
{
case SortedByType.Title:
sqlQuery.OrderBy("title", orderBy.IsAsc);
break;
case SortedByType.CreateBy:
sqlQuery.OrderBy("create_by", orderBy.IsAsc);
break;
case SortedByType.DateAndTime:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
}
using (var db = GetDb())
{
return db.ExecuteList(sqlQuery).ConvertAll(ToCases);
}
}
private List <Contact> GetCrudeContacts(
String searchText,
IEnumerable <String> tags,
int contactStatus,
ContactListViewType contactListView,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetContactSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) && (tags == null || !tags.Any()) && contactStatus <= 0 &&
contactListView == ContactListViewType.All);
var whereConditional = WhereConditional(new List <int>(), searchText, tags, contactStatus, contactListView);
if (withParams && whereConditional == null)
{
return(new List <Contact>());
}
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
if (orderBy != null)
{
if (!Enum.IsDefined(typeof(ContactSortedByType), orderBy.SortedBy.ToString()))
{
orderBy.SortedBy = ContactSortedByType.DisplayName;
}
switch ((ContactSortedByType)orderBy.SortedBy)
{
case ContactSortedByType.DisplayName:
sqlQuery.OrderBy("display_name", orderBy.IsAsc);
break;
case ContactSortedByType.Created:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
case ContactSortedByType.ContactType:
sqlQuery.OrderBy("status_id", orderBy.IsAsc);
break;
default:
sqlQuery.OrderBy("display_name", orderBy.IsAsc);
break;
}
}
return(DbManager.ExecuteList(sqlQuery).ConvertAll(contact => ToContact(contact)));
}
private IQueryable <DbDeal> GetDbDealByFilters(
ICollection <int> exceptIDs,
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate,
OrderBy orderBy)
{
var sqlQuery = Query(CrmDbContext.Deals).Join(CrmDbContext.DealMilestones,
x => x.DealMilestoneId,
y => y.Id,
(x, y) => new { x, y }
);
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 (!_bundleSearch.TrySelectOpportunity(searchText, out ids))
{
foreach (var k in keywords)
{
sqlQuery = sqlQuery.Where(x => Microsoft.EntityFrameworkCore.EF.Functions.Like(x.x.Title, k + "%") || Microsoft.EntityFrameworkCore.EF.Functions.Like(x.x.Description, k + "%"));
}
}
else if (ids.Count == 0)
{
return(null);
}
}
}
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)
{
sqlQuery = sqlQuery.Where(x => x.x.ContactId == contactID);
}
else
{
if (ids.Count > 0)
{
ids = relativeContactsID.Intersect(ids).ToList();
if (ids.Count == 0)
{
return(null);
}
}
else
{
ids = relativeContactsID;
}
}
}
else
{
sqlQuery = sqlQuery.Where(x => x.x.ContactId == contactID);
}
}
if (0 < milestoneID && milestoneID < int.MaxValue)
{
sqlQuery = sqlQuery.Where(x => x.x.DealMilestoneId == milestoneID);
}
if (responsibleID != Guid.Empty)
{
sqlQuery = sqlQuery.Where(x => x.x.ResponsibleId == responsibleID);
}
if (ids.Count > 0)
{
if (exceptIDs.Count > 0)
{
ids = ids.Except(exceptIDs).ToList();
if (ids.Count == 0)
{
return(null);
}
}
sqlQuery = sqlQuery.Where(x => ids.Contains(x.x.Id));
}
else if (exceptIDs.Count > 0)
{
sqlQuery = sqlQuery.Where(x => !exceptIDs.Contains(x.x.Id));
}
if ((stageType != null) || (fromDate != DateTime.MinValue && toDate != DateTime.MinValue))
{
if (stageType != null)
{
sqlQuery = sqlQuery.Where(x => x.y.Status == stageType.Value);
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery = sqlQuery.Where(x => x.y.Status == 0 ? x.x.ExpectedCloseDate >= _tenantUtil.DateTimeToUtc(fromDate) && x.x.ExpectedCloseDate <= _tenantUtil.DateTimeToUtc(toDate)
: x.x.ActualCloseDate >= _tenantUtil.DateTimeToUtc(fromDate) && x.x.ActualCloseDate <= _tenantUtil.DateTimeToUtc(toDate));
}
}
if (orderBy != null && Enum.IsDefined(typeof(DealSortedByType), orderBy.SortedBy))
{
switch ((DealSortedByType)orderBy.SortedBy)
{
case DealSortedByType.Title:
{
sqlQuery = orderBy.IsAsc ? sqlQuery.OrderBy(x => x.x.Title)
: sqlQuery.OrderByDescending(x => x.x.Title);
break;
}
case DealSortedByType.BidValue:
{
sqlQuery = orderBy.IsAsc ? sqlQuery.OrderBy(x => x.x.BidValue)
: sqlQuery.OrderByDescending(x => x.x.BidValue);
break;
}
case DealSortedByType.Responsible:
{
if (orderBy.IsAsc)
{
sqlQuery = sqlQuery.OrderBy(x => x.x.ResponsibleId)
.ThenBy(x => x.y.SortOrder)
.ThenBy(x => x.x.ContactId)
.ThenByDescending(x => x.x.ActualCloseDate)
.ThenBy(x => x.x.ExpectedCloseDate)
.ThenBy(x => x.x.Title);
}
else
{
sqlQuery = sqlQuery.OrderByDescending(x => x.x.ResponsibleId)
.OrderByDescending(x => x.y.SortOrder)
.ThenBy(x => x.x.ContactId)
.ThenByDescending(x => x.x.ActualCloseDate)
.ThenBy(x => x.x.ExpectedCloseDate)
.ThenBy(x => x.x.Title);
}
break;
}
case DealSortedByType.Stage:
{
if (orderBy.IsAsc)
{
sqlQuery = sqlQuery.OrderBy(x => x.y.SortOrder)
.ThenBy(x => x.x.ContactId)
.ThenByDescending(x => x.x.ActualCloseDate)
.ThenBy(x => x.x.ExpectedCloseDate)
.ThenBy(x => x.x.Title);
}
else
{
sqlQuery = sqlQuery.OrderByDescending(x => x.y.SortOrder)
.ThenBy(x => x.x.ContactId)
.ThenByDescending(x => x.x.ActualCloseDate)
.ThenBy(x => x.x.ExpectedCloseDate)
.ThenBy(x => x.x.Title);
}
break;
}
case DealSortedByType.DateAndTime:
{
sqlQuery.OrderBy("x.x.close_date", orderBy.IsAsc);
break;
}
default:
throw new ArgumentException();
}
}
else
{
sqlQuery = sqlQuery.OrderBy(x => x.y.SortOrder)
.ThenBy(x => x.x.ContactId)
.ThenBy(x => x.x.Title);
}
return(sqlQuery.Select(x => x.x));
}
public IEnumerable <RelationshipEventWrapper> GetHistory(
String entityType,
int entityId,
int categoryId,
Guid createBy,
ApiDateTime fromDate,
ApiDateTime toDate)
{
if (!String.IsNullOrEmpty(entityType) && !(
String.Compare(entityType, "contact", true) == 0 ||
String.Compare(entityType, "opportunity", true) == 0 ||
String.Compare(entityType, "case", true) == 0))
{
throw new ArgumentException();
}
RelationshipEventByType eventByType;
IEnumerable <RelationshipEventWrapper> result;
OrderBy eventOrderBy;
if (Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out eventByType))
{
eventOrderBy = new OrderBy(eventByType, !_context.SortDescending);
}
else if (String.IsNullOrEmpty(_context.SortBy))
{
eventOrderBy = new OrderBy(RelationshipEventByType.Created, false);
}
else
{
eventOrderBy = null;
}
if (eventOrderBy != null)
{
result = ToListRelationshipEventWrapper(DaoFactory.GetRelationshipEventDao().GetItems(
_context.FilterValue,
ToEntityType(entityType),
entityId,
createBy,
categoryId,
fromDate,
toDate,
(int)_context.StartIndex,
(int)_context.Count,
eventOrderBy));
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToListRelationshipEventWrapper(DaoFactory.GetRelationshipEventDao().GetItems(
_context.FilterValue,
ToEntityType(entityType),
entityId,
createBy,
categoryId,
fromDate,
toDate,
0,
0,
null));
}
return(result.ToSmartList());
}
public IEnumerable<InvoiceBaseWrapper> GetInvoices(
InvoiceStatus? status,
ApiDateTime issueDateFrom,
ApiDateTime issueDateTo,
ApiDateTime dueDateFrom,
ApiDateTime dueDateTo,
String entityType,
int entityid,
String currency
)
{
if (!String.IsNullOrEmpty(entityType) && !(
String.Compare(entityType, "contact", true) == 0 ||
String.Compare(entityType, "opportunity", true) == 0 ||
String.Compare(entityType, "case", true) == 0))
throw new ArgumentException();
IEnumerable<InvoiceBaseWrapper> result;
InvoiceSortedByType sortBy;
OrderBy invoiceOrderBy;
var searchString = _context.FilterValue;
if (InvoiceSortedByType.TryParse(_context.SortBy, true, out sortBy))
{
invoiceOrderBy = new OrderBy(sortBy, !_context.SortDescending);
}
else if (String.IsNullOrEmpty(_context.SortBy))
{
invoiceOrderBy = new OrderBy(InvoiceSortedByType.Number, true);
}
else
{
invoiceOrderBy = null;
}
var fromIndex = (int)_context.StartIndex;
var count = (int)_context.Count;
if (invoiceOrderBy != null)
{
result = ToListInvoiceBaseWrappers(
DaoFactory.GetInvoiceDao().GetInvoices(
searchString,
status,
issueDateFrom, issueDateTo,
dueDateFrom, dueDateTo,
ToEntityType(entityType), entityid,
currency,
fromIndex, count,
invoiceOrderBy));
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToListInvoiceBaseWrappers(
DaoFactory.GetInvoiceDao().GetInvoices(
searchString,
status,
issueDateFrom, issueDateTo,
dueDateFrom, dueDateTo,
ToEntityType(entityType), entityid,
currency,
0,
0,
null));
}
int totalCount;
if (result.Count() < count)
{
totalCount = fromIndex + result.Count();
}
else
{
totalCount = DaoFactory.GetInvoiceDao().GetInvoicesCount(
searchString,
status,
issueDateFrom, issueDateTo,
dueDateFrom, dueDateTo,
ToEntityType(entityType), entityid,
currency);
}
_context.SetTotalCount(totalCount);
return result.ToSmartList();
}
public IEnumerable<OpportunityWrapper> GetDeals(
Guid responsibleid,
int opportunityStagesid,
IEnumerable<string> tags,
int contactid,
DealMilestoneStatus? stageType,
bool? contactAlsoIsParticipant,
ApiDateTime fromDate,
ApiDateTime toDate)
{
DealSortedByType dealSortedByType;
IEnumerable<OpportunityWrapper> result;
var searchString = _context.FilterValue;
OrderBy dealsOrderBy;
if (Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out dealSortedByType))
{
dealsOrderBy = new OrderBy(dealSortedByType, !_context.SortDescending);
}
else if (string.IsNullOrEmpty(_context.SortBy))
{
dealsOrderBy = new OrderBy(DealSortedByType.Stage, true);
}
else
{
dealsOrderBy = null;
}
var fromIndex = (int)_context.StartIndex;
var count = (int)_context.Count;
if (dealsOrderBy != null)
{
result = ToListOpportunityWrapper(DaoFactory.GetDealDao().GetDeals(
searchString,
responsibleid,
opportunityStagesid,
tags,
contactid,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
fromIndex,
count,
dealsOrderBy)).ToList();
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToListOpportunityWrapper(DaoFactory.GetDealDao().GetDeals(
searchString,
responsibleid,
opportunityStagesid,
tags,
contactid,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
0, 0, null)).ToList();
}
int totalCount;
if (result.Count() < count)
{
totalCount = fromIndex + result.Count();
}
else
{
totalCount = DaoFactory
.GetDealDao()
.GetDealsCount(searchString,
responsibleid,
opportunityStagesid,
tags,
contactid,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate);
}
_context.SetTotalCount(totalCount);
return result.ToSmartList();
}
public IEnumerable<InvoiceItemWrapper> GetInvoiceItems(int status, bool? inventoryStock)
{
IEnumerable<InvoiceItemWrapper> result;
InvoiceItemSortedByType sortBy;
OrderBy invoiceOrderBy;
var searchString = _context.FilterValue;
if (InvoiceItemSortedByType.TryParse(_context.SortBy, true, out sortBy))
{
invoiceOrderBy = new OrderBy(sortBy, !_context.SortDescending);
}
else if (String.IsNullOrEmpty(_context.SortBy))
{
invoiceOrderBy = new OrderBy(InvoiceItemSortedByType.Name, true);
}
else
{
invoiceOrderBy = null;
}
var fromIndex = (int)_context.StartIndex;
var count = (int)_context.Count;
if (invoiceOrderBy != null)
{
result = DaoFactory.GetInvoiceItemDao().GetInvoiceItems(
searchString,
status,
inventoryStock,
fromIndex, count,
invoiceOrderBy)
.ConvertAll(ToInvoiceItemWrapper);
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = DaoFactory.GetInvoiceItemDao().GetInvoiceItems(
searchString,
status,
inventoryStock,
0, 0,
null)
.ConvertAll(ToInvoiceItemWrapper);
}
int totalCount;
if (result.Count() < count)
{
totalCount = fromIndex + result.Count();
}
else
{
totalCount = DaoFactory.GetInvoiceItemDao().GetInvoiceItemsCount(
searchString,
status,
inventoryStock);
}
_context.SetTotalCount(totalCount);
return result.ToSmartList();
}
public IEnumerable<TaskWrapper> GetAllTasks(
Guid responsibleid,
int categoryid,
bool? isClosed,
ApiDateTime fromDate,
ApiDateTime toDate,
string entityType,
int entityid)
{
TaskSortedByType taskSortedByType;
if (!string.IsNullOrEmpty(entityType) &&
!(
string.Compare(entityType, "contact", StringComparison.OrdinalIgnoreCase) == 0 ||
string.Compare(entityType, "opportunity", StringComparison.OrdinalIgnoreCase) == 0 ||
string.Compare(entityType, "case", StringComparison.OrdinalIgnoreCase) == 0)
)
throw new ArgumentException();
var searchText = _context.FilterValue;
IEnumerable<TaskWrapper> result;
OrderBy taskOrderBy;
if (Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out taskSortedByType))
{
taskOrderBy = new OrderBy(taskSortedByType, !_context.SortDescending);
}
else if (string.IsNullOrEmpty(_context.SortBy))
{
taskOrderBy = new OrderBy(TaskSortedByType.DeadLine, true);
}
else
{
taskOrderBy = null;
}
var fromIndex = (int)_context.StartIndex;
var count = (int)_context.Count;
if (taskOrderBy != null)
{
result = ToTaskListWrapper(
DaoFactory
.GetTaskDao()
.GetTasks(
searchText,
responsibleid,
categoryid,
isClosed,
fromDate,
toDate,
ToEntityType(entityType),
entityid,
fromIndex,
count,
taskOrderBy)).ToList();
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
result = ToTaskListWrapper(
DaoFactory
.GetTaskDao()
.GetTasks(
searchText,
responsibleid,
categoryid,
isClosed,
fromDate,
toDate,
ToEntityType(entityType),
entityid,
0,
0, null)).ToList();
int totalCount;
if (result.Count() < count)
{
totalCount = fromIndex + result.Count();
}
else
{
totalCount = DaoFactory
.GetTaskDao()
.GetTasksCount(
searchText,
responsibleid,
categoryid,
isClosed,
fromDate,
toDate,
ToEntityType(entityType),
entityid);
}
_context.SetTotalCount(totalCount);
return result;
}
public IEnumerable<RelationshipEventWrapper> GetHistory(
string entityType,
int entityId,
int categoryId,
Guid createBy,
ApiDateTime fromDate,
ApiDateTime toDate)
{
var entityTypeObj = ToEntityType(entityType);
switch (entityTypeObj) {
case EntityType.Contact:
var contact = DaoFactory.GetContactDao().GetByID(entityId);
if (contact == null || !CRMSecurity.CanAccessTo(contact))
throw new ItemNotFoundException();
break;
case EntityType.Case:
var cases = DaoFactory.GetCasesDao().GetByID(entityId);
if (cases == null || !CRMSecurity.CanAccessTo(cases))
throw new ItemNotFoundException();
break;
case EntityType.Opportunity:
var deal = DaoFactory.GetDealDao().GetByID(entityId);
if (deal == null || !CRMSecurity.CanAccessTo(deal))
throw new ItemNotFoundException();
break;
default:
if (entityId != 0)
{
throw new ArgumentException();
}
break;
}
RelationshipEventByType eventByType;
IEnumerable<RelationshipEventWrapper> result;
OrderBy eventOrderBy;
if (Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out eventByType))
{
eventOrderBy = new OrderBy(eventByType, !_context.SortDescending);
}
else if (string.IsNullOrEmpty(_context.SortBy))
{
eventOrderBy = new OrderBy(RelationshipEventByType.Created, false);
}
else
{
eventOrderBy = null;
}
if (eventOrderBy != null)
{
result = ToListRelationshipEventWrapper(DaoFactory.GetRelationshipEventDao().GetItems(
_context.FilterValue,
entityTypeObj,
entityId,
createBy,
categoryId,
fromDate,
toDate,
(int)_context.StartIndex,
(int)_context.Count,
eventOrderBy));
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToListRelationshipEventWrapper(DaoFactory.GetRelationshipEventDao().GetItems(
_context.FilterValue,
entityTypeObj,
entityId,
createBy,
categoryId,
fromDate,
toDate,
0,
0,
null));
}
return result.ToSmartList();
}
private List<Contact> GetCrudeContacts(
String searchText,
IEnumerable<String> tags,
int contactStage,
int contactType,
ContactListViewType contactListView,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy,
Guid responsibleid = default(Guid))
{
var sqlQuery = GetContactSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) && (tags == null || !tags.Any()) &&
contactStage <= 0 && contactType <= 0 &&
contactListView == ContactListViewType.All &&
responsibleid == default(Guid) &&
fromDate == DateTime.MinValue &&
toDate == DateTime.MinValue);
var whereConditional = WhereConditional(new List<int>(),
searchText,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
responsibleid);
if (withParams && whereConditional == null)
return new List<Contact>();
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue) sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue) sqlQuery.SetMaxResults(count);
if (orderBy != null)
{
if (!Enum.IsDefined(typeof(ContactSortedByType), orderBy.SortedBy.ToString()))
orderBy.SortedBy = ContactSortedByType.Created;
switch ((ContactSortedByType)orderBy.SortedBy)
{
case ContactSortedByType.DisplayName:
sqlQuery.OrderBy("display_name", orderBy.IsAsc);
break;
case ContactSortedByType.Created:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
case ContactSortedByType.ContactType:
sqlQuery.OrderBy("status_id", orderBy.IsAsc);
break;
case ContactSortedByType.FirstName:
sqlQuery.OrderBy("first_name", orderBy.IsAsc);
sqlQuery.OrderBy("last_name", orderBy.IsAsc);
break;
case ContactSortedByType.LastName:
sqlQuery.OrderBy("last_name", orderBy.IsAsc);
sqlQuery.OrderBy("first_name", orderBy.IsAsc);
break;
default:
sqlQuery.OrderBy("display_name", orderBy.IsAsc);
break;
}
}
using (var db = GetDb())
{
return db.ExecuteList(sqlQuery).ConvertAll(contact => ToContact(contact));
}
}
public IEnumerable<ContactWrapper> GetContacts(IEnumerable<String> tags,
int contactStage,
int contactType,
ContactListViewType contactListView,
ApiDateTime fromDate,
ApiDateTime toDate)
{
IEnumerable<ContactWrapper> result;
OrderBy contactsOrderBy;
ContactSortedByType sortBy;
var searchString = _context.FilterValue;
if (Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out sortBy))
{
contactsOrderBy = new OrderBy(sortBy, !_context.SortDescending);
}
else if (String.IsNullOrEmpty(_context.SortBy))
{
contactsOrderBy = new OrderBy(ContactSortedByType.Created, false);
}
else
{
contactsOrderBy = null;
}
var fromIndex = (int)_context.StartIndex;
var count = (int)_context.Count;
if (contactsOrderBy != null)
{
result = ToListContactWrapper(DaoFactory.GetContactDao()
.GetContacts(searchString,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
fromIndex,
count,
contactsOrderBy));
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToListContactWrapper(DaoFactory.GetContactDao()
.GetContacts(searchString,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
0,
0,
null));
}
int totalCount;
if (result.Count() < count)
{
totalCount = fromIndex + result.Count();
}
else
{
totalCount = DaoFactory.GetContactDao().GetContactsCount(searchString,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate);
}
_context.SetTotalCount(totalCount);
return result;
}
private List<Contact> GetCrudeContacts(
String searchText,
IEnumerable<String> tags,
int contactStatus,
ContactListViewType contactListView,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetContactSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) && (tags == null || !tags.Any()) && contactStatus <= 0 &&
contactListView == ContactListViewType.All);
var whereConditional = WhereConditional(new List<int>(), searchText, tags, contactStatus, contactListView);
if (withParams && whereConditional == null)
return new List<Contact>();
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue) sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue) sqlQuery.SetMaxResults(count);
if (orderBy != null)
{
if (!Enum.IsDefined(typeof(ContactSortedByType), orderBy.SortedBy.ToString()))
orderBy.SortedBy = ContactSortedByType.DisplayName;
switch ((ContactSortedByType)orderBy.SortedBy)
{
case ContactSortedByType.DisplayName:
sqlQuery.OrderBy("display_name", orderBy.IsAsc);
break;
case ContactSortedByType.Created:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
case ContactSortedByType.ContactType:
sqlQuery.OrderBy("status_id", orderBy.IsAsc);
break;
default:
sqlQuery.OrderBy("display_name", orderBy.IsAsc);
break;
}
}
return DbManager.ExecuteList(sqlQuery).ConvertAll(contact => ToContact(contact));
}
public List<Contact> GetContacts(String searchText,
IEnumerable<String> tags,
int contactStage,
int contactType,
ContactListViewType contactListView,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy,
Guid? responsibleId = null,
bool? isShared = null)
{
if (CRMSecurity.IsAdmin)
{
if (!isShared.HasValue)
{
return GetCrudeContacts(
searchText,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
from,
count,
orderBy,
responsibleId,
isShared,
false);
}
else
{
var crudeContacts = GetCrudeContacts(
searchText,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
0,
from + count,
orderBy,
responsibleId,
isShared,
false);
if (crudeContacts.Count == 0) return crudeContacts;
var result = crudeContacts.Where(c => (isShared.Value == true ? c.ShareType != ShareType.None : c.ShareType == ShareType.None)).ToList();
if (result.Count == crudeContacts.Count) return result.Skip(from).ToList();
var localCount = count;
var localFrom = from + count;
while (true)
{
crudeContacts = GetCrudeContacts(
searchText,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
localFrom,
localCount,
orderBy,
responsibleId,
isShared,
false);
if (crudeContacts.Count == 0) break;
result.AddRange(crudeContacts.Where(c => (isShared.Value == true ? c.ShareType != ShareType.None : c.ShareType == ShareType.None)));
if ((result.Count >= count + from) || (crudeContacts.Count < localCount)) break;
localFrom += localCount;
localCount = localCount * 2;
}
return result.Skip(from).Take(count).ToList();
}
}
else
{
var crudeContacts = GetCrudeContacts(
searchText,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
0,
from + count,
orderBy,
responsibleId,
isShared,
false);
if (crudeContacts.Count == 0) return crudeContacts;
var tmp = isShared.HasValue ? crudeContacts.Where(c => (isShared.Value == true ? c.ShareType != ShareType.None : c.ShareType == ShareType.None)).ToList() : crudeContacts;
if (crudeContacts.Count < from + count)
{
return tmp.FindAll(CRMSecurity.CanAccessTo).Skip(from).ToList();
}
var result = tmp.FindAll(CRMSecurity.CanAccessTo);
if (result.Count == crudeContacts.Count) return result.Skip(from).ToList();
var localCount = count;
var localFrom = from + count;
while (true)
{
crudeContacts = GetCrudeContacts(
searchText,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
localFrom,
localCount,
orderBy,
responsibleId,
isShared,
false);
if (crudeContacts.Count == 0) break;
tmp = isShared.HasValue ? crudeContacts.Where(c => (isShared.Value == true ? c.ShareType != ShareType.None : c.ShareType == ShareType.None)).ToList() : crudeContacts;
result.AddRange(tmp.Where(CRMSecurity.CanAccessTo));
if ((result.Count >= count + from) || (crudeContacts.Count < localCount)) break;
localFrom += localCount;
localCount = localCount * 2;
}
return result.Skip(from).Take(count).ToList();
}
}
public List<InvoiceItem> GetInvoiceItems(
string searchText,
int status,
bool? inventoryStock,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetInvoiceItemSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) || status != 0 || inventoryStock.HasValue);
var whereConditional = WhereConditional(new List<int>(), searchText, status, inventoryStock);
// WhereConditional(CRMSecurity.GetPrivateItems(typeof(Invoice)).ToList(), searchText);
if (withParams && whereConditional == null)
return new List<InvoiceItem>();
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue) sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue) sqlQuery.SetMaxResults(count);
if (orderBy != null && Enum.IsDefined(typeof(InvoiceItemSortedByType), orderBy.SortedBy))
{
switch ((InvoiceItemSortedByType)orderBy.SortedBy)
{
case InvoiceItemSortedByType.Name:
sqlQuery.OrderBy("title", orderBy.IsAsc);
break;
case InvoiceItemSortedByType.SKU:
sqlQuery.OrderBy("stock_keeping_unit", orderBy.IsAsc);
break;
case InvoiceItemSortedByType.Price:
sqlQuery.OrderBy("price", orderBy.IsAsc);
break;
case InvoiceItemSortedByType.Quantity:
sqlQuery.OrderBy("case when track_inventory = 1 then stock_quantity else quantity end", false).OrderBy("title", orderBy.IsAsc);
break;
case InvoiceItemSortedByType.Created:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
default:
sqlQuery.OrderBy("title", true);
break;
}
}
else
{
sqlQuery.OrderBy("title", true);
}
using (var db = GetDb())
{
return db.ExecuteList(sqlQuery).ConvertAll(ToInvoiceItem);
}
}
private List<Contact> GetCrudeContacts(
String searchText,
IEnumerable<String> tags,
int contactStage,
int contactType,
ContactListViewType contactListView,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy,
Guid? responsibleid = null,
bool? isShared = null,
bool selectIsSharedInNewScheme = true)
{
var sqlQuery = GetContactSqlQuery(null);
var withParams = HasSearchParams(searchText,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
responsibleid,
isShared);
var whereConditional = WhereConditional(new List<int>(),
searchText,
tags,
contactStage,
contactType,
contactListView,
fromDate,
toDate,
responsibleid,
isShared);
if (withParams && whereConditional == null)
return new List<Contact>();
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue) sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue) sqlQuery.SetMaxResults(count);
if (orderBy != null)
{
if (!Enum.IsDefined(typeof(ContactSortedByType), orderBy.SortedBy.ToString()))
orderBy.SortedBy = ContactSortedByType.Created;
switch ((ContactSortedByType)orderBy.SortedBy)
{
case ContactSortedByType.DisplayName:
sqlQuery.OrderBy("display_name", orderBy.IsAsc);
break;
case ContactSortedByType.Created:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
case ContactSortedByType.ContactType:
sqlQuery.OrderBy("status_id", orderBy.IsAsc);
break;
case ContactSortedByType.FirstName:
sqlQuery.OrderBy("first_name", orderBy.IsAsc);
sqlQuery.OrderBy("last_name", orderBy.IsAsc);
break;
case ContactSortedByType.LastName:
sqlQuery.OrderBy("last_name", orderBy.IsAsc);
sqlQuery.OrderBy("first_name", orderBy.IsAsc);
break;
default:
sqlQuery.OrderBy("display_name", orderBy.IsAsc);
break;
}
}
using (var db = GetDb())
{
var contacts = db.ExecuteList(sqlQuery).ConvertAll(ToContact);
return selectIsSharedInNewScheme && isShared.HasValue ?
contacts.Where(c => (isShared.Value ? c.ShareType != ShareType.None : c.ShareType == ShareType.None)).ToList() :
contacts;
}
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
Guid responsibleID,
int categoryID,
bool? isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
if (responsibleID != Guid.Empty)
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
if (entityID > 0)
switch (entityType)
{
case EntityType.Contact:
var isCompany = Convert.ToBoolean(DbManager.ExecuteScalar(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID))));
if (isCompany)
return WhereConditional(sqlQuery, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy);
else
return WhereConditional(sqlQuery, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, 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 (isClosed.HasValue)
sqlQuery.Where("is_closed", isClosed);
if (categoryID > 0)
sqlQuery.Where(Exp.Eq("category_id", categoryID));
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Between("deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
else if (fromDate != DateTime.MinValue)
sqlQuery.Where(Exp.Ge("deadline", TenantUtil.DateTimeToUtc(fromDate)));
else if (toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Le("deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
if (0 < from && from < int.MaxValue)
sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue)
sqlQuery.SetMaxResults(count);
sqlQuery.OrderBy("is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy("title", orderBy.IsAsc)
.OrderBy("deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy("deadline", orderBy.IsAsc)
.OrderBy("title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy("category_id", orderBy.IsAsc)
.OrderBy("deadline", true)
.OrderBy("title", true);
break;
}
else
sqlQuery.OrderBy("deadline", true)
.OrderBy("title", true);
return sqlQuery;
}
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)));
}
private List<Task> GetCrudeTasks(
String searchText,
Guid responsibleID,
int categoryID,
bool? isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
var taskTableAlias = "t";
var sqlQuery = WhereConditional(GetTaskQuery(null, taskTableAlias), taskTableAlias, responsibleID,
categoryID, isClosed, fromDate, toDate, entityType, entityID, from, count,
orderBy);
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
if (keywords.Length > 0)
if (FullTextSearch.SupportModule(FullTextSearch.CRMTasksModule))
{
var taskIDs = FullTextSearch.Search(searchText, FullTextSearch.CRMTasksModule)
.GetIdentifiers()
.Select(item => Convert.ToInt32(item)).ToArray();
if (taskIDs.Length != 0)
sqlQuery.Where(Exp.In(taskTableAlias + ".id", taskIDs));
else
return new List<Task>();
}
else
sqlQuery.Where(BuildLike(new[] {taskTableAlias + ".title" }, keywords));
}
using (var db = GetDb())
{
return db.ExecuteList(sqlQuery)
.ConvertAll(row => ToTask(row));
}
}
public IEnumerable<CasesWrapper> GetCases(int contactid, bool? isClosed, IEnumerable<string> tags)
{
IEnumerable<CasesWrapper> result;
SortedByType sortBy;
OrderBy casesOrderBy;
var searchString = _context.FilterValue;
if (EnumExtension.TryParse(_context.SortBy, true, out sortBy))
{
casesOrderBy = new OrderBy(sortBy, !_context.SortDescending);
}
else if (string.IsNullOrEmpty(_context.SortBy))
{
casesOrderBy = new OrderBy(SortedByType.Title, true);
}
else
{
casesOrderBy = null;
}
var fromIndex = (int)_context.StartIndex;
var count = (int)_context.Count;
var tagsList = tags.ToList();
if (casesOrderBy != null)
{
result = ToListCasesWrappers(
DaoFactory
.GetCasesDao()
.GetCases(
searchString,
contactid,
isClosed,
tagsList,
fromIndex,
count,
casesOrderBy)).ToList();
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
{
result = ToListCasesWrappers(
DaoFactory
.GetCasesDao()
.GetCases(
searchString, contactid, isClosed,
tagsList,
0,
0,
null)).ToList();
}
int totalCount;
if (result.Count() < count)
{
totalCount = fromIndex + result.Count();
}
else
{
totalCount = DaoFactory.GetCasesDao().GetCasesCount(searchString, contactid, isClosed, tagsList);
}
_context.SetTotalCount(totalCount);
return result.ToSmartList();
}
public List <Deal> GetDeals(
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
if (CRMSecurity.IsAdmin)
{
return(GetCrudeDeals(searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
from,
count,
orderBy));
}
var crudeDeals = GetCrudeDeals(searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
0,
from + count,
orderBy);
if (crudeDeals.Count == 0)
{
return(crudeDeals);
}
if (crudeDeals.Count < from + count)
{
return(crudeDeals.FindAll(CRMSecurity.CanAccessTo).Skip(from).ToList());
}
var result = crudeDeals.FindAll(CRMSecurity.CanAccessTo);
if (result.Count == crudeDeals.Count)
{
return(result.Skip(from).ToList());
}
var localCount = count;
var localFrom = from + count;
while (true)
{
crudeDeals = GetCrudeDeals(searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
localFrom,
localCount,
orderBy);
if (crudeDeals.Count == 0)
{
break;
}
result.AddRange(crudeDeals.Where(CRMSecurity.CanAccessTo));
if ((result.Count >= count + from) || (crudeDeals.Count < localCount))
{
break;
}
localFrom += localCount;
localCount = localCount * 2;
}
return(result.Skip(from).Take(count).ToList());
}
public List<Deal> GetDeals(
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable<String> tags,
int contactID,
DealMilestoneStatus? stageType,
bool? contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
if (CRMSecurity.IsAdmin)
return GetCrudeDeals(searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
from,
count,
orderBy);
var crudeDeals = GetCrudeDeals(searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
0,
from + count,
orderBy);
if (crudeDeals.Count == 0) return crudeDeals;
if (crudeDeals.Count < from + count) return crudeDeals.FindAll(CRMSecurity.CanAccessTo).Skip(from).ToList();
var result = crudeDeals.FindAll(CRMSecurity.CanAccessTo);
if (result.Count == crudeDeals.Count) return result.Skip(from).ToList();
var localCount = count;
var localFrom = from + count;
while (true)
{
crudeDeals = GetCrudeDeals(searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant,
fromDate,
toDate,
localFrom,
localCount,
orderBy);
if (crudeDeals.Count == 0) break;
result.AddRange(crudeDeals.Where(CRMSecurity.CanAccessTo));
if ((result.Count >= count + from) || (crudeDeals.Count < localCount)) break;
localFrom += localCount;
localCount = localCount * 2;
}
return result.Skip(from).Take(count).ToList();
}
public IEnumerable<RelationshipEventWrapper> GetHistory(
String entityType,
int entityId,
int categoryId,
Guid createBy,
ApiDateTime fromDate,
ApiDateTime toDate)
{
if (!String.IsNullOrEmpty(entityType) && !(
String.Compare(entityType, "contact", true) == 0 ||
String.Compare(entityType, "opportunity", true) == 0 ||
String.Compare(entityType, "case", true) == 0))
throw new ArgumentException();
RelationshipEventByType eventByType;
IEnumerable<RelationshipEventWrapper> result;
OrderBy eventOrderBy;
if (Web.CRM.Classes.EnumExtension.TryParse(_context.SortBy, true, out eventByType))
eventOrderBy = new OrderBy(eventByType, !_context.SortDescending);
else if (String.IsNullOrEmpty(_context.SortBy))
eventOrderBy = new OrderBy(RelationshipEventByType.Created, false);
else
eventOrderBy = null;
if (eventOrderBy != null)
{
result = ToListRelationshipEventWrapper(DaoFactory.GetRelationshipEventDao().GetItems(
_context.FilterValue,
ToEntityType(entityType),
entityId,
createBy,
categoryId,
fromDate,
toDate,
(int)_context.StartIndex,
(int)_context.Count,
eventOrderBy));
_context.SetDataPaginated();
_context.SetDataFiltered();
_context.SetDataSorted();
}
else
result =ToListRelationshipEventWrapper(DaoFactory.GetRelationshipEventDao().GetItems(
_context.FilterValue,
ToEntityType(entityType),
entityId,
createBy,
categoryId,
fromDate,
toDate,
0,
0,
null));
return result.ToSmartList();
}
private List<Deal> GetCrudeDeals(
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable<String> tags,
int contactID,
DealMilestoneStatus? stageType,
bool? contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetDealSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) &&
responsibleID == Guid.Empty &&
milestoneID <= 0 &&
(tags == null || !tags.Any()) &&
contactID <= 0 &&
stageType == null &&
contactAlsoIsParticipant == null &&
fromDate == DateTime.MinValue &&
toDate == DateTime.MinValue);
var whereConditional = WhereConditional(new List<int>(),
searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant);
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
sqlQuery.Having(Exp.Between("close_date", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate)));
else if (withParams && whereConditional == null)
return new List<Deal>();
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue)
sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue)
sqlQuery.SetMaxResults(count);
if (orderBy != null && Enum.IsDefined(typeof(DealSortedByType), orderBy.SortedBy))
switch ((DealSortedByType)orderBy.SortedBy)
{
case DealSortedByType.Title:
sqlQuery.OrderBy("tblDeal.title", orderBy.IsAsc);
break;
case DealSortedByType.BidValue:
sqlQuery.OrderBy("tblDeal.bid_value", orderBy.IsAsc);
break;
case DealSortedByType.Responsible:
sqlQuery.OrderBy("tblDeal.responsible_id", orderBy.IsAsc)
.OrderBy("tblDM.sort_order", orderBy.IsAsc)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.actual_close_date", false)
.OrderBy("tblDeal.expected_close_date", true)
.OrderBy("tblDeal.title", true);
break;
case DealSortedByType.Stage:
sqlQuery.OrderBy("tblDM.sort_order", orderBy.IsAsc)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.actual_close_date", false)
.OrderBy("tblDeal.expected_close_date", true)
.OrderBy("tblDeal.title", true);
break;
case DealSortedByType.DateAndTime:
sqlQuery.OrderBy("close_date", orderBy.IsAsc);
break;
default:
throw new ArgumentException();
}
else
sqlQuery.OrderBy("tblDM.sort_order", true)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.title", true);
using (var db = GetDb())
{
return db.ExecuteList(sqlQuery).ConvertAll(ToDeal);
}
}
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 = Query(CrmDbContext.RelationshipEvent).AsNoTracking();
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
var isCompany = false;
isCompany = Query(CrmDbContext.Contacts).Where(x => x.Id == entityID).Select(x => x.IsCompany).Single();
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 = sqlQuery.Where(x => x.ContactId == entityID);
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
{
sqlQuery = sqlQuery.Where(x => x.ContactId == entityID);
}
else
{
personIDs.Add(entityID);
sqlQuery = sqlQuery.Where(x => personIDs.Contains(x.ContactId));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery = sqlQuery.Where(x => x.EntityId == entityID && x.EntityType == entityType);
break;
}
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery = sqlQuery.Where(x => x.CreateOn >= _tenantUtil.DateTimeToUtc(fromDate) && x.CreateOn <= _tenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1)));
}
else if (fromDate != DateTime.MinValue)
{
sqlQuery = sqlQuery.Where(x => x.CreateOn >= _tenantUtil.DateTimeToUtc(fromDate));
}
else if (toDate != DateTime.MinValue)
{
sqlQuery = sqlQuery.Where(x => x.CreateOn <= _tenantUtil.DateTimeToUtc(toDate).AddDays(1).AddMinutes(-1));
}
if (createBy != Guid.Empty)
{
sqlQuery = sqlQuery.Where(x => x.CreateBy == createBy);
}
if (categoryID != 0)
{
sqlQuery = sqlQuery.Where(x => x.CategoryId == categoryID);
}
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
List <int> eventsIds;
if (!_factoryIndexer.TrySelectIds(s => s.MatchAll(searchText), out eventsIds))
{
if (keywords.Length > 0)
{
foreach (var k in keywords)
{
sqlQuery = sqlQuery.Where(x => Microsoft.EntityFrameworkCore.EF.Functions.Like(x.Content, k + "%"));
}
}
}
else
{
if (eventsIds.Count == 0)
{
return(new List <RelationshipEvent>());
}
sqlQuery = sqlQuery.Where(x => eventsIds.Contains(x.Id));
}
}
if (0 < from && from < int.MaxValue)
{
sqlQuery = sqlQuery.Skip(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery = sqlQuery.Take(count);
}
if (orderBy != null && Enum.IsDefined(typeof(RelationshipEventByType), orderBy.SortedBy))
{
switch ((RelationshipEventByType)orderBy.SortedBy)
{
case RelationshipEventByType.Category:
sqlQuery = sqlQuery.OrderBy("CategoryId", orderBy.IsAsc);
break;
case RelationshipEventByType.Content:
sqlQuery = sqlQuery.OrderBy("Content", orderBy.IsAsc);
break;
case RelationshipEventByType.CreateBy:
sqlQuery = sqlQuery.OrderBy("CreateBy", orderBy.IsAsc);
break;
case RelationshipEventByType.Created:
sqlQuery = sqlQuery.OrderBy("CreateOn", orderBy.IsAsc);
break;
}
}
else
{
sqlQuery = sqlQuery.OrderBy("CreateOn", orderBy.IsAsc);
}
return(_mapper.Map <List <DbRelationshipEvent>, List <RelationshipEvent> >(sqlQuery.ToList()));
}
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;
using (var db = GetDb())
{
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);
using (var db = GetDb())
{
return db.ExecuteList(sqlQuery)
.ConvertAll(row => ToRelationshipEvent(row));
}
}
public List<Invoice> GetCrudeInvoices(
String searchText,
InvoiceStatus? status,
DateTime issueDateFrom,
DateTime issueDateTo,
DateTime dueDateFrom,
DateTime dueDateTo,
EntityType entityType,
int entityID,
String currency,
int from,
int count,
OrderBy orderBy)
{
var invoicesTableAlias = "i_tbl";
var sqlQuery = GetInvoiceSqlQuery(null, invoicesTableAlias);
var withParams = hasParams(searchText, status, issueDateFrom, issueDateTo, dueDateFrom, dueDateTo, entityType, entityID, currency);
var whereConditional = WhereConditional(invoicesTableAlias, new List<int>(), searchText, status, issueDateFrom, issueDateTo, dueDateFrom, dueDateTo, entityType, entityID, currency);
// WhereConditional(CRMSecurity.GetPrivateItems(typeof(Invoice)).ToList(), searchText);
if (withParams && whereConditional == null)
return new List<Invoice>();
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue) sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue) sqlQuery.SetMaxResults(count);
if (orderBy != null && Enum.IsDefined(typeof(InvoiceSortedByType), orderBy.SortedBy))
{
switch ((InvoiceSortedByType)orderBy.SortedBy)
{
case InvoiceSortedByType.Number:
sqlQuery.OrderBy(invoicesTableAlias + ".number", orderBy.IsAsc);
break;
case InvoiceSortedByType.Status:
sqlQuery.OrderBy(invoicesTableAlias + ".status", orderBy.IsAsc);
break;
case InvoiceSortedByType.DueDate:
sqlQuery.OrderBy(invoicesTableAlias + ".due_date", orderBy.IsAsc);
break;
case InvoiceSortedByType.IssueDate:
sqlQuery.OrderBy(invoicesTableAlias + ".issue_date", orderBy.IsAsc);
break;
case InvoiceSortedByType.Contact:
sqlQuery.LeftOuterJoin("crm_contact c_tbl", Exp.EqColumns(invoicesTableAlias + ".contact_id", "c_tbl.id"))
.OrderBy("case when c_tbl.display_name is null then 1 else 0 end, c_tbl.display_name", orderBy.IsAsc)
.OrderBy(invoicesTableAlias + ".number", true);
break;
default:
sqlQuery.OrderBy(invoicesTableAlias + ".number", true);
break;
}
}
else
{
sqlQuery.OrderBy(invoicesTableAlias + ".number", true);
}
using (var db = GetDb())
{
return db.ExecuteList(sqlQuery).ConvertAll(ToInvoice);
}
}
public List <Contact> GetContacts(String searchText, IEnumerable <String> tags, int contactStatus, ContactListViewType contactListView, int from,
int count, OrderBy orderBy)
{
if (CRMSecurity.IsAdmin)
{
return(GetCrudeContacts(
searchText,
tags,
contactStatus,
contactListView,
from,
count,
orderBy));
}
var crudeContacts = GetCrudeContacts(
searchText,
tags,
contactStatus,
contactListView,
0,
from + count,
orderBy);
if (crudeContacts.Count == 0)
{
return(crudeContacts);
}
if (crudeContacts.Count < from + count)
{
return(crudeContacts
.FindAll(CRMSecurity.CanAccessTo).Skip(from).ToList());
}
var result = crudeContacts.FindAll(CRMSecurity.CanAccessTo);
if (result.Count == crudeContacts.Count)
{
return(result.Skip(from).ToList());
}
var localCount = count;
var localFrom = from + count;
while (true)
{
crudeContacts = GetCrudeContacts(
searchText,
tags,
contactStatus,
contactListView,
localFrom,
localCount,
orderBy);
if (crudeContacts.Count == 0)
{
break;
}
result.AddRange(crudeContacts.Where(CRMSecurity.CanAccessTo));
if ((result.Count >= count + from) || (crudeContacts.Count < localCount))
{
break;
}
localFrom += localCount;
localCount = localCount * 2;
}
return(result.Skip(from).Take(count).ToList());
}