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