Ejemplo n.º 1
0
        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();
            }
        }
Ejemplo n.º 2
0
        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;

        }
Ejemplo n.º 3
0
        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;
        }
Ejemplo n.º 4
0
        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;

        }
Ejemplo n.º 5
0
        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;
        }