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<Project> GetByFilter(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var query = new SqlQuery(ProjectsTable + " p")
.Select(ProjectColumns.Select(c => "p." + c).ToArray())
.Select(new SqlQuery(MilestonesTable + " m").SelectCount().Where(Exp.EqColumns("m.tenant_id", "p.tenant_id") & Exp.EqColumns("m.project_id", "p.id")).Where(Exp.Eq("m.status", MilestoneStatus.Open)))
.Select(new SqlQuery(TasksTable + " t").SelectCount().Where(Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id")).Where(!Exp.Eq("t.status", TaskStatus.Closed)))
.Select(new SqlQuery(ParticipantTable + " pp").SelectCount().Where(Exp.EqColumns("pp.tenant", "p.tenant_id") & Exp.EqColumns("pp.project_id", "p.id") & Exp.Eq("pp.removed", false)))
.Select("p.private")
.Where("p.tenant_id", Tenant);
if (filter.Max > 0 && filter.Max < 150000)
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
query.OrderBy("(case p.status when 2 then 1 when 1 then 2 else 0 end)", true);
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["Project"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("p." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("p." + sort, sortColumns[sort]);
}
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
using (var db = new DbManager(DatabaseId))
{
return db.ExecuteList(query).ConvertAll(ToProjectFull).ToList();
}
}
private List<MailMessageItem> GetFilteredChains(
IDbManager db,
int id_tenant,
string id_user,
MailFilter filter,
DateTime? utc_chain_from_date,
int from_message,
bool? prev_flag,
out bool has_more)
{
var res = new List<MailMessageItem>();
var chains_to_skip = new List<ChainInfo>();
var skip_flag = false;
var chunck_index = 0;
var sort_order = filter.SortOrder == "ascending";
if (prev_flag.GetValueOrDefault(false))
sort_order = !sort_order;
var query_messages = new SqlQuery(MailTable.name)
.Select(
MailTable.Columns.id,
MailTable.Columns.from,
MailTable.Columns.to,
MailTable.Columns.reply,
MailTable.Columns.subject,
MailTable.Columns.importance,
MailTable.Columns.date_sent,
MailTable.Columns.size,
MailTable.Columns.attach_count,
MailTable.Columns.unread,
MailTable.Columns.is_answered,
MailTable.Columns.is_forwarded,
MailTable.Columns.is_from_crm,
MailTable.Columns.is_from_tl,
MailTable.Columns.folder_restore,
MailTable.Columns.folder,
MailTable.Columns.chain_id,
MailTable.Columns.id_mailbox,
MailTable.Columns.chain_date)
.Where(GetUserWhere(id_user, id_tenant))
.Where(MailTable.Columns.is_removed, false)
.ApplyFilter(filter)
.OrderBy(MailTable.Columns.chain_date, sort_order);
if (null != utc_chain_from_date)
{
query_messages = query_messages.Where(sort_order ?
Exp.Ge(MailTable.Columns.chain_date, utc_chain_from_date) :
Exp.Le(MailTable.Columns.chain_date, utc_chain_from_date));
skip_flag = true;
}
// We are increasing the size of the page to check whether it is necessary to show the Next button.
while (res.Count < filter.PageSize + 1)
{
query_messages.SetFirstResult(chunck_index * chunk_size * filter.PageSize).SetMaxResults(chunk_size * filter.PageSize);
chunck_index++;
var tenant_obj = CoreContext.TenantManager.GetTenant(id_tenant);
var list = db
.ExecuteList(query_messages)
.ConvertAll(r =>
ConvertToConversation(r, tenant_obj));
if (0 == list.Count)
break;
foreach (var item in list)
{
var chain_info = new ChainInfo {id = item.ChainId, mailbox = item.MailboxId};
// Skip chains that was stored before and within from_message's chain.
if (skip_flag)
{
var tenant = CoreContext.TenantManager.GetTenant(id_tenant);
if (item.ChainDate != TenantUtil.DateTimeFromUtc(tenant, utc_chain_from_date.GetValueOrDefault()))
skip_flag = false;
else
{
if (item.Id == from_message)
skip_flag = false;
chains_to_skip.Add(chain_info);
continue;
}
}
if (chains_to_skip.Contains(chain_info))
continue;
var already_contains = false;
foreach(var chain in res){
if(chain.ChainId == item.ChainId && chain.MailboxId == item.MailboxId){
already_contains = true;
if(chain.Date < item.Date)
res[res.IndexOf(chain)] = item;
break;
}
}
if(!already_contains)
res.Add(item);
if (filter.PageSize + 1 == res.Count)
break;
}
var is_all_needed_conversation_gathered = filter.PageSize + 1 == res.Count;
if (is_all_needed_conversation_gathered)
break;
var is_enough_messages_for_page = chunk_size*filter.PageSize <= list.Count;
if (!is_enough_messages_for_page)
break;
}
has_more = res.Count > filter.PageSize;
if (has_more)
res.RemoveAt(filter.PageSize);
return res;
}
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;
}
private static List<UserActivity> GetUserActivities(int tenant, Guid? userOrDept, Guid product, IEnumerable<Guid> modules, int action, IEnumerable<string> containers, DateTime from, DateTime to, int offset, int count)
{
// HACK: optimize sql query for MySql Server 5.1
var table = "webstudio_useractivity" + (userOrDept.GetValueOrDefault() != default(Guid) && DbRegistry.GetSqlDialect(dbid).GetType().Name == "MySQLDialect" ? " use index (productid)" : string.Empty);
var q = new SqlQuery(table)
.Select("id", "tenantid", "productid", "moduleid", "userid", "contentid", "containerid")
.Select("actiontype", "actiontext", "businessvalue", "additionaldata", "activitydate")
.Select("url", "title", "partid", "imagefilename", "htmlpreview", "securityid")
.Where("tenantid", tenant);
//userid
if (userOrDept.GetValueOrDefault() != default(Guid))
{
if (CoreContext.UserManager.UserExists(userOrDept.Value))
{
q.Where("userid", userOrDept.Value.ToString());
}
else
{
q.Where(Exp.In("userid", CoreContext.UserManager.GetUsersByGroup(userOrDept.Value).Select(u => u.ID.ToString()).ToArray()));
}
}
//productId
if (product != default(Guid))
{
q.Where("productid", product.ToString());
}
//moduleIds
if (modules != null && modules.Any())
{
q.Where(Exp.In("moduleid", modules.Select(m => m.ToString()).ToArray()));
}
//actionType
if (action != UserActivityConstants.AllActionType)
{
q.Where("actiontype", action);
}
//containerIds
if (containers != null && containers.Any())
{
q.Where(Exp.In("containerid", containers.ToArray()));
}
//dates
if (from != DateTime.MinValue)
{
q.Where(Exp.Ge("activitydate", TenantUtil.DateTimeToUtc(from).Date));
}
if (to != DateTime.MaxValue)
{
q.Where(Exp.Le("activitydate", TenantUtil.DateTimeToUtc(to).Date.AddTicks(TimeSpan.TicksPerDay - 1)));
}
//limits
if (0 < offset) q.SetFirstResult(offset);
if (0 < count) q.SetMaxResults(count);
q.OrderBy("id", false);
var key = BuildKey(q);
var result = cache.Get(key) as List<UserActivity>;
if (result == null)
{
using (var db = GetDbManager())
{
result = GetActivities(db, q, count);
lock (cache)
{
var depkey = BuildDependencyKey(tenant, product);
if (cache.Get(depkey) == null)
{
cache.Insert(depkey, depkey);
}
cache.Insert(key, result, new CacheDependency(null, new[] { depkey }), DateTime.Now.Add(expiration), Cache.NoSlidingExpiration);
}
}
}
return result;
}
private static List<UserActivity> GetActivities(DbManager db, SqlQuery q, int max)
{
int queried;
var list = QueryUserActivities(q, db, out queried);
if (0 < max && list.Count < max && queried == max)
{
q.SetMaxResults(max * MAX_FETCH_FORWARD);
// some items filtered out, query additional
var from = queried;
do
{
q.SetFirstResult(from);
// query again with cursor moved to last result
var additionalList = QueryUserActivities(q, db, out queried);
list.AddRange(additionalList);
from += queried;// page to next set
} while (list.Count < max && queried == max * MAX_FETCH_FORWARD && 0 < queried);
}
if (0 < max)
{
list = list.Take(max).ToList();// force limit
}
return list;
}
public static List<UserActivity> GetProjectsActivities(int tenantId, Guid productId, Guid? userId, int projectId, string type, string searchText, DateTime from, DateTime to, int offset, int count, int lastId, string sortBy, bool sortOrder)
{
// HACK: optimize sql query for MySql Server 5.1
var table = "webstudio_useractivity" + (userId.GetValueOrDefault() != default(Guid) && DbRegistry.GetSqlDialect(dbid).GetType().Name == "MySQLDialect" ? " use index (productid)" : string.Empty);
var query = new SqlQuery(table)
.Select("id", "tenantid", "productid", "moduleid", "userid", "contentid", "containerid")
.Select("actiontype", "actiontext", "businessvalue", "additionaldata", "activitydate")
.Select("url", "title", "partid", "imagefilename", "htmlpreview", "securityid")
.Where("productid", productId.ToString())
.Where("moduleid", productId.ToString())
.Where("tenantid", tenantId);
if (projectId != 0)
{
query.Where("containerid", projectId);
}
if (userId != default(Guid))
{
query.Where("userid", userId);
}
if (!string.IsNullOrEmpty(type))
{
query.Where(Exp.Like("additionaldata", type, SqlLike.StartWith));
}
if (from != DateTime.MinValue && from != DateTime.MaxValue)
{
query.Where(Exp.Ge("activitydate", TenantUtil.DateTimeFromUtc(from).Date));
}
if (to != DateTime.MinValue && to != DateTime.MaxValue)
{
query.Where(Exp.Le("activitydate", TenantUtil.DateTimeFromUtc(to).Date.AddTicks(TimeSpan.TicksPerDay - 1)));
}
if (!string.IsNullOrEmpty(searchText))
{
query.Where(Exp.Like("title", searchText, SqlLike.AnyWhere));
}
if (count > 0 && count < 150000)
{
query.SetFirstResult(offset);
query.SetMaxResults(count * 2);
}
query.OrderBy(sortBy, sortOrder);
var key = BuildKey(query);
var result = cache.Get(key) as List<UserActivity>;
if (result == null)
{
using (var db = GetDbManager())
{
result = GetActivities(db, query, count * 2);
lock (cache)
{
var depkey = BuildDependencyKey(tenantId, productId);
if (cache.Get(depkey) == null)
{
cache.Insert(depkey, depkey);
}
cache.Insert(key, result, new CacheDependency(null, new[] { depkey }), DateTime.Now.Add(expiration), Cache.NoSlidingExpiration);
}
}
}
return result;
}
