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)
{
var modules = SearchDao.GetFullTextSearchModule(EntityType.Task, searchText);
if (FullTextSearch.SupportModule(modules))
{
var taskIDs = FullTextSearch.Search(modules);
if (taskIDs.Any())
{
sqlQuery.Where(Exp.In(taskTableAlias + ".id", taskIDs));
}
else
{
return(new List <Task>());
}
}
else
{
sqlQuery.Where(BuildLike(new[] { taskTableAlias + ".title", taskTableAlias + ".description" },
keywords));
}
}
}
using (var db = GetDb())
{
return(db.ExecuteList(sqlQuery)
.ConvertAll(row => ToTask(row)));
}
}
private Exp WhereConditional(
ICollection <int> exceptIDs,
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant)
{
var conditions = new List <Exp>();
var ids = new List <int>();
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToArray();
var modules = SearchDao.GetFullTextSearchModule(EntityType.Opportunity, searchText);
if (keywords.Length > 0)
{
if (FullTextSearch.SupportModule(modules))
{
ids = FullTextSearch.Search(modules);
if (ids.Count == 0)
{
return(null);
}
}
else
{
conditions.Add(BuildLike(new[] { "tblDeal.title", "tblDeal.description" }, keywords));
}
}
}
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)
{
conditions.Add(Exp.Eq("tblDeal.contact_id", contactID));
}
else
{
if (ids.Count > 0)
{
ids = relativeContactsID.Intersect(ids).ToList();
if (ids.Count == 0)
{
return(null);
}
}
else
{
ids = relativeContactsID;
}
}
}
else
{
conditions.Add(Exp.Eq("tblDeal.contact_id", contactID));
}
}
if (0 < milestoneID && milestoneID < int.MaxValue)
{
conditions.Add(Exp.Eq("tblDeal.deal_milestone_id", milestoneID));
}
if (responsibleID != Guid.Empty)
{
conditions.Add(Exp.Eq("tblDeal.responsible_id", responsibleID));
}
if (stageType != null)
{
conditions.Add(Exp.Eq("tblDM.status", (int)stageType.Value));
}
if (ids.Count > 0)
{
if (exceptIDs.Count > 0)
{
ids = ids.Except(exceptIDs).ToList();
if (ids.Count == 0)
{
return(null);
}
}
conditions.Add(Exp.In("tblDeal.id", ids));
}
else if (exceptIDs.Count > 0)
{
conditions.Add(!Exp.In("tblDeal.id", exceptIDs.ToArray()));
}
if (conditions.Count == 0)
{
return(null);
}
return(conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j));
}
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 Exp WhereConditional(
ICollection <int> exceptIDs,
String searchText,
int contactID,
bool?isClosed,
IEnumerable <String> tags)
{
var conditions = new List <Exp>();
var ids = new List <int>();
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
if (keywords.Length > 0)
{
var modules = SearchDao.GetFullTextSearchModule(EntityType.Case, searchText);
if (FullTextSearch.SupportModule(modules))
{
ids = FullTextSearch.Search(modules);
if (ids.Count == 0)
{
return(null);
}
}
else
{
conditions.Add(BuildLike(new[] { "title" }, keywords));
}
}
}
if (contactID > 0)
{
var sqlQuery = new SqlQuery("crm_entity_contact")
.Select("entity_id")
.Where(Exp.Eq("contact_id", contactID) & Exp.Eq("entity_type", (int)EntityType.Case));
if (ids.Count > 0)
{
sqlQuery.Where(Exp.In("entity_id", ids));
}
using (var db = GetDb())
{
ids = db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList();
}
if (ids.Count == 0)
{
return(null);
}
}
if (isClosed.HasValue)
{
conditions.Add(Exp.Eq("is_closed", isClosed));
}
if (tags != null && tags.Any())
{
ids = SearchByTags(EntityType.Case, ids.ToArray(), tags);
if (ids.Count == 0)
{
return(null);
}
}
if (ids.Count > 0)
{
if (exceptIDs.Count > 0)
{
ids = ids.Except(exceptIDs).ToList();
if (ids.Count == 0)
{
return(null);
}
}
conditions.Add(Exp.In("id", ids));
}
else if (exceptIDs.Count > 0)
{
conditions.Add(!Exp.In("id", exceptIDs.ToArray()));
}
if (conditions.Count == 0)
{
return(null);
}
return(conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j));
}
