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