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()); }