Example #1
0
		public EntityQuery(string entityName, string fields = "*", QueryObject query = null, 
			QuerySortObject[] sort = null, int? skip = null, int? limit = null )
		{
			if (string.IsNullOrWhiteSpace(entityName))
				throw new ArgumentException("Invalid entity name.");

			if (string.IsNullOrWhiteSpace(fields))
                fields = "*";

            EntityName = entityName;
			Fields = fields;
			Query = query;
			Sort = sort;
			Skip = skip;
			Limit = limit;
		}
Example #2
0
        private List<EntityRecord> GetListRecords(List<Entity> entities, Entity entity, string listName, int? page = null, QueryObject queryObj = null,
					int? pageSize = null, bool export = false, EntityRelation auxRelation = null, Guid? auxRelatedRecordId = null,
					string auxRelationDirection = "origin-target")
        {
            List<KeyValuePair<string, string>> queryStringOverwriteParameters = new List<KeyValuePair<string, string>>();
            foreach (var key in Request.Query.Keys)
                queryStringOverwriteParameters.Add(new KeyValuePair<string, string>(key, Request.Query[key]));

            return recMan.GetListRecords(entities, entity, listName, page, queryObj, pageSize, export, auxRelation,
                auxRelatedRecordId, auxRelationDirection, queryStringOverwriteParameters);
        }
Example #3
0
        public IActionResult AllActivitiesUserCanSee(string label = "all", int page = 1)
        {
            var response = new ResponseModel();
            try
            {
                //var queryString = HttpContext.Request.QueryString;
                #region << Can user read activities >>
                //Get current user
                ErpUser user = SecurityContext.CurrentUser;
                //Get entity meta
                var entity = entMan.ReadEntity("wv_project_activity").Object;
                //check if user role has permissions
                var canRead = user.Roles.Any(x => entity.RecordPermissions.CanRead.Any(z => z == x.Id));
                var canCreate = user.Roles.Any(x => entity.RecordPermissions.CanCreate.Any(z => z == x.Id));
                var canUpdate = user.Roles.Any(x => entity.RecordPermissions.CanUpdate.Any(z => z == x.Id));
                var canDelete = user.Roles.Any(x => entity.RecordPermissions.CanDelete.Any(z => z == x.Id));

                if (!canRead)
                {
                    response.Success = false;
                    response.Message = "You do not have permission to read the activities in this system";
                    response.Timestamp = DateTime.UtcNow;
                    return Json(response); //return empty object
                }
                #endregion

                var activityQueryResponse = new QueryResponse();
                var userCanSeeProjectIds = new List<Guid>();
                #region << Generate list of projects user can see >>
                {
                    var requestedFields = "id,$user_1_n_project_owner.id,$role_n_n_project_team.id,$role_n_n_project_customer.id";
                    //QueryObject filterObj = EntityQuery.QueryEQ("id", recordId);
                    QueryObject filterObj = null;
                    EntityQuery resultQuery = new EntityQuery("wv_project", requestedFields, filterObj, null, null, null, null);
                    QueryResponse result = recMan.Find(resultQuery);
                    var resultRecordsList = new List<EntityRecord>();
                    if (!result.Success)
                    {
                        response.Success = false;
                        response.Timestamp = DateTime.UtcNow;
                        response.Message = result.Message;
                        response.Object = null;
                        return Json(response);
                    }
                    foreach (var record in result.Object.Data)
                    {
                        //Check if user can view the object
                        var userIsPM = false;
                        var userIsStaff = false;
                        var userIsCustomer = false;
                        #region << Check user roles >>
                        foreach (var userRole in user.Roles)
                        {
                            if (!userIsPM)
                            {
                                userIsPM = ((List<EntityRecord>)record["$user_1_n_project_owner"]).Any(z => (Guid)z["id"] == user.Id);
                            }
                            if (!userIsStaff)
                            {
                                userIsStaff = ((List<EntityRecord>)record["$role_n_n_project_team"]).Any(z => (Guid)z["id"] == userRole.Id);
                            }
                            if (!userIsCustomer)
                            {
                                userIsCustomer = ((List<EntityRecord>)record["$role_n_n_project_customer"]).Any(z => (Guid)z["id"] == userRole.Id);
                            }
                        }
                        #endregion

                        if (userIsPM || userIsStaff || userIsCustomer)
                        {
                            userCanSeeProjectIds.Add((Guid)record["id"]);
                        }
                    }
                }
                #endregion

                #region << Get activities >>
                {
                    var fields = "id,label,created_on,description,subject," +
                    "$user_wv_project_activity_created_by.username,$user_wv_project_activity_created_by.image," +
                    "$project_1_n_activity.name";

                    QueryObject rootFilterSection = null;
                    QueryObject auxFilterSection = null;
                    QueryObject projectIdFilterSection = null;

                    #region << project id filters >>
                    var projectIdRulesList = new List<QueryObject>();
                    foreach (var projectId in userCanSeeProjectIds)
                    {
                        var projectIdRule = EntityQuery.QueryEQ("project_id", projectId);
                        projectIdRulesList.Add(projectIdRule);
                    }
                    projectIdFilterSection = EntityQuery.QueryOR(projectIdRulesList.ToArray());
                    #endregion

                    #region << Aux filters & Sort>>
                    var auxRulesList = new List<QueryObject>();
                    QueryObject auxRule = new QueryObject();
                    if (label != "all")
                    {
                        auxRule = EntityQuery.QueryEQ("label", label);
                        auxRulesList.Add(auxRule);
                    }

                    auxFilterSection = EntityQuery.QueryAND(auxRulesList.ToArray());
                    //Add default sort by created_on
                    var sortRulesList = new List<QuerySortObject>();
                    var defaultSortRule = new QuerySortObject("created_on", QuerySortType.Descending);
                    sortRulesList.Add(defaultSortRule);

                    #endregion

                    rootFilterSection = EntityQuery.QueryAND(projectIdFilterSection, auxFilterSection);

                    //Calculate page
                    var pageSize = 10;
                    var skipRecords = (page - 1) * pageSize;

                    var activityQuery = new EntityQuery("wv_project_activity", fields, rootFilterSection, sortRulesList.ToArray(), skipRecords, pageSize, null);

                    activityQueryResponse = recMan.Find(activityQuery);
                    if (!activityQueryResponse.Success)
                    {
                        response.Success = false;
                        response.Timestamp = DateTime.UtcNow;
                        response.Message = activityQueryResponse.Message;
                        response.Object = null;
                        return Json(response);
                    }
                }
                #endregion

                response.Success = true;
                response.Timestamp = DateTime.UtcNow;
                response.Message = "Successful read";
                response.Object = activityQueryResponse.Object.Data;

                return Json(response);

            }
            catch (Exception ex)
            {
                response.Success = false;
                response.Timestamp = DateTime.UtcNow;
                response.Message = ex.Message;
                response.Object = null;
                return Json(response);
            }
        }
Example #4
0
        public IActionResult AllTaskUserCanSee(string listName, int page = 0)
        {
            var response = new ResponseModel();
            try
            {
                //var queryString = HttpContext.Request.QueryString;
                #region << Can user read tasks >>
                //Get current user
                ErpUser user = SecurityContext.CurrentUser;
                //Get entity meta
                var entity = entMan.ReadEntity("wv_task").Object;
                //check if user role has permissions
                var canRead = user.Roles.Any(x => entity.RecordPermissions.CanRead.Any(z => z == x.Id));
                var canCreate = user.Roles.Any(x => entity.RecordPermissions.CanCreate.Any(z => z == x.Id));
                var canUpdate = user.Roles.Any(x => entity.RecordPermissions.CanUpdate.Any(z => z == x.Id));
                var canDelete = user.Roles.Any(x => entity.RecordPermissions.CanDelete.Any(z => z == x.Id));

                if (!canRead)
                {
                    response.Success = false;
                    response.Message = "You do not have permission to read the projects in this system";
                    response.Timestamp = DateTime.UtcNow;
                    return Json(response); //return empty object
                }
                #endregion
                var taskQueryResponse = new QueryResponse();
                var userCanSeeProjectIds = new List<Guid>();
                #region << Generate list of projects user can see >>
                {
                    var requestedFields = "id,$user_1_n_project_owner.id,$role_n_n_project_team.id,$role_n_n_project_customer.id";
                    //QueryObject filterObj = EntityQuery.QueryEQ("id", recordId);
                    QueryObject filterObj = null;
                    EntityQuery resultQuery = new EntityQuery("wv_project", requestedFields, filterObj, null, null, null, null);
                    QueryResponse result = recMan.Find(resultQuery);
                    var resultRecordsList = new List<EntityRecord>();
                    if (!result.Success)
                    {
                        response.Success = false;
                        response.Timestamp = DateTime.UtcNow;
                        response.Message = result.Message;
                        response.Object = null;
                        return Json(response);
                    }
                    foreach (var record in result.Object.Data)
                    {
                        //Check if user can view the object
                        var userIsPM = false;
                        var userIsStaff = false;
                        var userIsCustomer = false;
                        #region << Check user roles >>
                        foreach (var userRole in user.Roles)
                        {
                            if (!userIsPM)
                            {
                                userIsPM = ((List<EntityRecord>)record["$user_1_n_project_owner"]).Any(z => (Guid)z["id"] == user.Id);
                            }
                            if (!userIsStaff)
                            {
                                userIsStaff = ((List<EntityRecord>)record["$role_n_n_project_team"]).Any(z => (Guid)z["id"] == userRole.Id);
                            }
                            if (!userIsCustomer)
                            {
                                userIsCustomer = ((List<EntityRecord>)record["$role_n_n_project_customer"]).Any(z => (Guid)z["id"] == userRole.Id);
                            }
                        }
                        #endregion

                        if (userIsPM || userIsStaff || userIsCustomer)
                        {
                            userCanSeeProjectIds.Add((Guid)record["id"]);
                        }
                    }
                }
                #endregion

                #region << Get tasks >>
                {
                    var fields = "id,code,number,subject,start_date,end_date,status,priority,$user_1_n_task_owner.id,$user_1_n_task_owner.image";

                    QueryObject rootFilterSection = null;
                    QueryObject auxFilterSection = null;
                    QueryObject projectIdFilterSection = null;

                    #region << project id filters >>
                    var projectIdRulesList = new List<QueryObject>();
                    foreach (var projectId in userCanSeeProjectIds)
                    {
                        var projectIdRule = EntityQuery.QueryEQ("project_id", projectId);
                        projectIdRulesList.Add(projectIdRule);
                    }
                    projectIdFilterSection = EntityQuery.QueryOR(projectIdRulesList.ToArray());
                    #endregion

                    #region << Aux filters & Sort>>
                    var sortRulesList = new List<QuerySortObject>();
                    var queryString = HttpContext.Request.QueryString.ToString();
                    var queryKeyValueList = QueryHelpers.ParseQuery(queryString);
                    var auxRulesList = new List<QueryObject>();
                    var getListObject = entMan.ReadRecordList(entity.Name, listName).Object;
                    //Currently we will hardcode the query generation
                    //auxFilterSection = RecordListQuery.ConvertQuery(getListObject.Query);
                    QueryObject auxRule = new QueryObject();
                    foreach (var query in queryKeyValueList)
                    {
                        switch (query.Key.ToLowerInvariant())
                        {
                            case "code":
                                auxRule = new QueryObject();
                                auxRule = EntityQuery.QueryContains("code", (string)query.Value);
                                auxRulesList.Add(auxRule);
                                break;
                            case "subject":
                                auxRule = new QueryObject();
                                auxRule = EntityQuery.QueryContains("subject", (string)query.Value);
                                auxRulesList.Add(auxRule);
                                break;
                            case "status":
                                auxRule = new QueryObject();
                                auxRule = EntityQuery.QueryEQ("status", (string)query.Value);
                                auxRulesList.Add(auxRule);
                                break;
                            case "priority":
                                auxRule = new QueryObject();
                                auxRule = EntityQuery.QueryEQ("priority", (string)query.Value);
                                auxRulesList.Add(auxRule);
                                break;
                            case "sortby":
                                var sortRule = new QuerySortObject((string)query.Value, QuerySortType.Descending);
                                if (!queryKeyValueList.ContainsKey("sortOrder") || (string)queryKeyValueList["sortOrder"] == "ascending")
                                {
                                    sortRule = new QuerySortObject((string)query.Value, QuerySortType.Ascending);
                                }
                                sortRulesList.Add(sortRule);
                                break;
                        }

                    }
                    auxFilterSection = EntityQuery.QueryAND(auxRulesList.ToArray());
                    //Add default sort by created_on
                    var defaultSortRule = new QuerySortObject("created_on", QuerySortType.Ascending);
                    sortRulesList.Add(defaultSortRule);

                    #endregion

                    rootFilterSection = EntityQuery.QueryAND(projectIdFilterSection, auxFilterSection);

                    //Calculate page
                    var pageSize = getListObject.PageSize;
                    var skipRecords = (page - 1) * pageSize;

                    var taskQuery = new EntityQuery("wv_task", fields, rootFilterSection, sortRulesList.ToArray(), skipRecords, pageSize, null);

                    taskQueryResponse = recMan.Find(taskQuery);
                    if (!taskQueryResponse.Success)
                    {
                        response.Success = false;
                        response.Timestamp = DateTime.UtcNow;
                        response.Message = taskQueryResponse.Message;
                        response.Object = null;
                        return Json(response);
                    }
                }
                #endregion
                var taskList = new List<EntityRecord>();
                #region << Post-process >>

                foreach (var task in taskQueryResponse.Object.Data)
                {
                    var record = new EntityRecord();
                    record["id"] = (Guid)task["id"];
                    record["code"] = (string)task["code"];
                    record["subject"] = (string)task["subject"];
                    record["start_date"] = (DateTime)task["start_date"];
                    record["end_date"] = (DateTime)task["end_date"];
                    record["status"] = (string)task["status"];
                    record["priority"] = (string)task["priority"];
                    var taskOwnerIdList = new List<Guid>();
                    var taskOwnerImageList = new List<string>();
                    var taskOwnerId = (Guid)((List<EntityRecord>)task["$user_1_n_task_owner"])[0]["id"];
                    var taskOwnerImage = (string)((List<EntityRecord>)task["$user_1_n_task_owner"])[0]["image"];
                    taskOwnerIdList.Add(taskOwnerId);
                    taskOwnerImageList.Add(taskOwnerImage);
                    record["$field$user_1_n_task_owner$id"] = taskOwnerIdList;
                    record["$field$user_1_n_task_owner$image"] = taskOwnerImageList;
                    taskList.Add(record);
                }
                #endregion

                response.Success = true;
                response.Timestamp = DateTime.UtcNow;
                response.Message = "Successful read";
                response.Object = taskList;

                return Json(response);

            }
            catch (Exception ex)
            {
                response.Success = false;
                response.Timestamp = DateTime.UtcNow;
                response.Message = ex.Message;
                response.Object = null;
                return Json(response);
            }
        }
        private IMongoQuery ConvertQuery(QueryObject query)
        {
            if (query == null)
                return Query.Null;

            BsonValue value = ConvertObjectToBsonValue(query.FieldValue);

            switch (query.QueryType)
            {
                case QueryType.EQ:
                    return Query.EQ(ProcessQueryIDFieldName(query.FieldName), value);
                case QueryType.NOT:
                    return Query.Not(Query.EQ(ProcessQueryIDFieldName(query.FieldName), value));
                case QueryType.LT:
                    return Query.LT(ProcessQueryIDFieldName(query.FieldName), value);
                case QueryType.LTE:
                    return Query.LTE(ProcessQueryIDFieldName(query.FieldName), value);
                case QueryType.GT:
                    return Query.GT(ProcessQueryIDFieldName(query.FieldName), value);
                case QueryType.GTE:
                    return Query.GTE(ProcessQueryIDFieldName(query.FieldName), value);
                case QueryType.CONTAINS:
                    {
                        var regex = new BsonRegularExpression(string.Format(".*{0}.*", value), "i"); // contains, ignore case
                        return Query.Matches(ProcessQueryIDFieldName(query.FieldName), regex);
                    }
                case QueryType.STARTSWITH:
                    {
                        var regex = new BsonRegularExpression(string.Format("^{0}", value), "i"); // starts with, ignore case
                        return Query.Matches(ProcessQueryIDFieldName(query.FieldName), regex);
                    }
                case QueryType.REGEX:
                    {
                        var regex = new BsonRegularExpression(string.Format("{0}", value));
                        return Query.Matches(ProcessQueryIDFieldName(query.FieldName), regex);
                    }
				case QueryType.RELATED:
					{
						return  Query.And( Query.Not( Query.Size(query.FieldName, 0) ), 
											Query.Not( Query.EQ( query.FieldName, BsonNull.Value ) ));
					}
				case QueryType.NOTRELATED:
					{
						return Query.Or( Query.Size(query.FieldName, 0), 
										Query.EQ(query.FieldName, BsonNull.Value), 
										Query.Not( Query.Exists( query.FieldName)) );
					}
				case QueryType.AND:
                    {
                        List<IMongoQuery> queries = new List<IMongoQuery>();
                        if (query.SubQueries.Count == 1)
                            return ConvertQuery(query.SubQueries[0]);
                        else
                        {
                            foreach (var q in query.SubQueries)
                                queries.Add(ConvertQuery(q));
                            return Query.And(queries);
                        }
                    }
                case QueryType.OR:
                    {
                        List<IMongoQuery> queries = new List<IMongoQuery>();
                        if (query.SubQueries.Count == 1)
                            return ConvertQuery(query.SubQueries[0]);
                        else
                        {
                            foreach (var q in query.SubQueries)
                                queries.Add(ConvertQuery(q));
                            return Query.Or(queries);
                        }
                    }
                default:
                    throw new Exception("Not supported query type");
            }
        }
 public long Count(string entityName, QueryObject query )
 {
     var mongoCollection = MongoStaticContext.Context.GetBsonCollection(RECORD_COLLECTION_PREFIX + entityName);
     var mongoQuery = ConvertQuery(query);
     return mongoCollection.Count(mongoQuery);
 }
        public IEnumerable<IEnumerable<KeyValuePair<string, object>>> Find(string entityName, QueryObject query, QuerySortObject[] sort, int? skip, int? limit)
        {
            var mongoCollection = MongoStaticContext.Context.GetBsonCollection(RECORD_COLLECTION_PREFIX + entityName);
            var mongoQuery = ConvertQuery(query);
            var cursor = mongoCollection.Find(mongoQuery);

            if (sort != null && sort.Length > 0)
            {
                SortByBuilder sortBy = null;
                foreach (var s in sort)
                {
                    if (s.SortType == QuerySortType.Ascending)
                        sortBy = sortBy == null ? SortBy.Ascending(s.FieldName) : sortBy.Ascending(s.FieldName);
                    else
                        sortBy = sortBy == null ? SortBy.Descending(s.FieldName) : sortBy.Descending(s.FieldName);
                }
                cursor.SetSortOrder(sortBy);
            }

            if (skip.HasValue)
                cursor.SetSkip(skip.Value);

            if (limit.HasValue)
                cursor.SetLimit(limit.Value);

            List<List<KeyValuePair<string, object>>> result = new List<List<KeyValuePair<string, object>>>();
            foreach (BsonDocument doc in cursor)
            {
                List<KeyValuePair<string, object>> record = new List<KeyValuePair<string, object>>();
                foreach (var fieldName in doc.Names)
                {
                    if (fieldName == "_id")
                        record.Add(new KeyValuePair<string, object>("id", BsonTypeMapper.MapToDotNetValue(doc["_id"])));
                    else
                        record.Add(new KeyValuePair<string, object>(fieldName, ConvertBsonValueToObject(doc[fieldName])));
                }
                result.Add(record);
            }
            return result;
        }
Example #8
0
        private void ProcessQueryObject(Entity entity, QueryObject obj)
        {
            if (obj == null)
                return;

            if (obj.QueryType != QueryType.AND && obj.QueryType != QueryType.OR)
            {
                var field = entity.Fields.SingleOrDefault(x => x.Name == obj.FieldName);
                if (field == null)
                    throw new Exception(string.Format("There is not entity field '{0}' you try to query by.", obj.FieldName));
                if (field is NumberField || field is AutoNumberField)
                {
                    if (obj.FieldValue != null)
                        obj.FieldValue = Convert.ToDecimal(obj.FieldValue);
                }
                else if (field is GuidField)
                {
                    if (obj.FieldValue != null && obj.FieldValue is string )
                        obj.FieldValue = new Guid(obj.FieldValue as string);
                }
                else if (field is PasswordField && obj.FieldValue != null)
                    obj.FieldValue = PasswordUtil.GetMd5Hash(obj.FieldValue as string);

            }

            if (obj.SubQueries != null && obj.SubQueries.Count > 0)
                foreach (var subObj in obj.SubQueries)
                {
                    ProcessQueryObject(entity, subObj);
                }
        }
Example #9
0
        private List<EntityRecord> GetListRecords(List<Entity> entities, Entity entity, string listName, int? page = null, QueryObject queryObj = null, string filter = null, string search = null)
        {
            RecordList list = null;
            if (entity != null && entity.RecordLists != null)
                list = entity.RecordLists.FirstOrDefault(l => l.Name == listName);

            var searchQuery = CreateSearchQuery(search, list, entity);
            if (searchQuery != null)
            {
                if (queryObj != null)
                    queryObj = EntityQuery.QueryAND(queryObj, searchQuery);
                else
                    queryObj = searchQuery;
            }

            var filterQuery = CreateFilterQuery(filter, entities);
            if (filterQuery != null)
            {
                if (queryObj != null)
                    queryObj = EntityQuery.QueryAND(queryObj, filterQuery);
                else
                    queryObj = filterQuery;
            }

            EntityQuery resultQuery = new EntityQuery(entity.Name, "*", queryObj, null, null, null);
            EntityRelationManager relManager = new EntityRelationManager(Storage);
            EntityRelationListResponse relListResponse = relManager.Read();
            List<EntityRelation> relationList = new List<EntityRelation>();
            if (relListResponse.Object != null)
                relationList = relListResponse.Object;

            if (list != null)
            {
                List<QuerySortObject> sortList = new List<QuerySortObject>();
                if (list.Sorts != null && list.Sorts.Count > 0)
                {
                    foreach (var sort in list.Sorts)
                    {
                        QuerySortType sortType;
                        if (Enum.TryParse<QuerySortType>(sort.SortType, true, out sortType))
                            sortList.Add(new QuerySortObject(sort.FieldName, sortType));
                    }
                    resultQuery.Sort = sortList.ToArray();
                }

                if (list.Query != null)
                {
                    var listQuery = RecordListQuery.ConvertQuery(list.Query);

                    if (queryObj != null)
                    {
                        if (queryObj.SubQueries != null && queryObj.SubQueries.Any())
                            queryObj.SubQueries.Add(listQuery);
                        else
                            queryObj = EntityQuery.QueryAND(listQuery, queryObj);
                    }
                    else
                        queryObj = listQuery;

                    resultQuery.Query = queryObj;
                }

                string queryFields = "id,";
                if (list.Columns != null)
                {
                    foreach (var column in list.Columns)
                    {
                        if (column is RecordListFieldItem)
                        {
                            if (((RecordListFieldItem)column).Meta.Name != "id")
                                queryFields += ((RecordListFieldItem)column).Meta.Name + ", ";
                        }
                        else if (column is RecordListRelationTreeItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationTreeItem)column).RelationId);

                            string relName = relation != null ? string.Format("${0}.", relation.Name) : "";

                            Guid relEntityId = relation.OriginEntityId;
                            Guid relFieldId = relation.OriginFieldId;

                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            var treeId = (column as RecordListRelationTreeItem).TreeId;
                            RecordTree tree = relEntity.RecordTrees.Single(x => x.Id == treeId);

                            var relIdField = relEntity.Fields.Single(x => x.Name == "id");

                            List<Guid> fieldIdsToInclude = new List<Guid>();
                            fieldIdsToInclude.AddRange(tree.NodeObjectProperties);

                            if (!fieldIdsToInclude.Contains(relIdField.Id))
                                fieldIdsToInclude.Add(relIdField.Id);

                            if (!fieldIdsToInclude.Contains(tree.NodeNameFieldId))
                                fieldIdsToInclude.Add(tree.NodeNameFieldId);

                            if (!fieldIdsToInclude.Contains(tree.NodeLabelFieldId))
                                fieldIdsToInclude.Add(tree.NodeLabelFieldId);

                            if (!fieldIdsToInclude.Contains(relField.Id))
                                fieldIdsToInclude.Add(relField.Id);

                            foreach (var fieldId in fieldIdsToInclude)
                            {
                                var f = relEntity.Fields.SingleOrDefault(x => x.Id == fieldId);
                                if (f != null)
                                {
                                    string qFieldName = string.Format("{0}{1},", relName, f.Name);
                                    if (!queryFields.Contains(qFieldName))
                                        queryFields += qFieldName;
                                }
                            }

                            //always add target field in query, its value may be required for relative view and list
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == relation.TargetFieldId);
                            queryFields += field.Name + ", ";
                        }
                        else if (column is RecordListRelationFieldItem)
                        {
                            string targetOriginPrefix = "";
                            if (list.RelationOptions != null)
                            {
                                var options = list.RelationOptions.SingleOrDefault(x => x.RelationId == ((RecordListRelationFieldItem)column).RelationId);
                                if (options != null && options.Direction == "target-origin")
                                    targetOriginPrefix = "$";
                            }

                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationFieldItem)column).RelationId);
                            queryFields += string.Format(targetOriginPrefix + "${0}.{1}, ", relation.Name, ((RecordListRelationFieldItem)column).Meta.Name);

                            //add ID field automatically if not added
                            if (!queryFields.Contains(string.Format(targetOriginPrefix + "${0}.id", relation.Name)))
                                queryFields += string.Format(targetOriginPrefix + "${0}.id,", relation.Name);

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                        else if (column is RecordListListItem || column is RecordListViewItem)
                        {
                            if (!queryFields.Contains(" id, ") && !queryFields.StartsWith("id,"))
                                queryFields += "id, ";
                        }
                        else if (column is RecordListRelationListItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationListItem)column).RelationId);

                            string targetOriginPrefix = "";
                            if (list.RelationOptions != null)
                            {
                                var options = list.RelationOptions.SingleOrDefault(x => x.RelationId == ((RecordListRelationListItem)column).RelationId);
                                if (options != null && options.Direction == "target-origin")
                                    targetOriginPrefix = "$";
                            }

                            string relName = relation != null ? string.Format(targetOriginPrefix + "${0}.", relation.Name) : "";

                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;

                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            string queryFieldName = string.Format("{0}{1}, ", relName, relField.Name);

                            if (!queryFields.Contains(queryFieldName))
                                queryFields += queryFieldName;

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                        else if (column is RecordListRelationViewItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationViewItem)column).RelationId);

                            string targetOriginPrefix = "";
                            if (list.RelationOptions != null)
                            {
                                var options = list.RelationOptions.SingleOrDefault(x => x.RelationId == ((RecordListRelationViewItem)column).RelationId);
                                if (options != null && options.Direction == "target-origin")
                                    targetOriginPrefix = "$";
                            }

                            string relName = relation != null ? string.Format(targetOriginPrefix + "${0}.", relation.Name) : "";

                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;

                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            string queryFieldName = string.Format("{0}{1}, ", relName, relField.Name);

                            if (!queryFields.Contains(queryFieldName))
                                queryFields += queryFieldName;

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                    }

                    if (queryFields.EndsWith(", "))
                        queryFields = queryFields.Remove(queryFields.Length - 2);

                    resultQuery.Fields = queryFields;

                }

                if (list.PageSize > 0)
                {
                    resultQuery.Limit = list.PageSize;
                    if (page != null && page > 0)
                        resultQuery.Skip = (page - 1) * resultQuery.Limit;
                }

            }

            List<EntityRecord> resultDataList = new List<EntityRecord>();

            QueryResponse result = recMan.Find(resultQuery);
            if (!result.Success)
                throw new Exception(result.Message);

            if (list != null)
            {
                foreach (var record in result.Object.Data)
                {
                    EntityRecord dataRecord = new EntityRecord();
                    //always add id value
                    dataRecord["id"] = record["id"];

                    foreach (var column in list.Columns)
                    {
                        if (column is RecordListFieldItem)
                        {
                            dataRecord[column.DataName] = record[((RecordListFieldItem)column).FieldName];
                        }
                        else if (column is RecordListRelationFieldItem)
                        {
                            string propName = string.Format("${0}", ((RecordListRelationFieldItem)column).RelationName);
                            List<EntityRecord> relFieldRecords = (List<EntityRecord>)record[propName];

                            string idDataName = "$field" + propName + "$id";
                            if (!dataRecord.Properties.ContainsKey(idDataName))
                            {
                                List<object> idFieldRecord = new List<object>();
                                if (relFieldRecords != null)
                                {
                                    foreach (var relFieldRecord in relFieldRecords)
                                        idFieldRecord.Add(relFieldRecord["id"]);
                                }
                                dataRecord[idDataName] = idFieldRecord;
                            }

                            List<object> resultFieldRecord = new List<object>();
                            if (relFieldRecords != null)
                            {
                                foreach (var relFieldRecord in relFieldRecords)
                                {
                                    resultFieldRecord.Add(relFieldRecord[((RecordListRelationFieldItem)column).FieldName]);
                                }
                            }
                            dataRecord[column.DataName] = resultFieldRecord;

                        }
                        else if (column is RecordListListItem)
                        {
                            dataRecord[column.DataName] = GetListRecords(entities, entity, ((RecordListListItem)column).ListName);
                        }
                        else if (column is RecordListRelationListItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationListItem)column).RelationId);
                            string relName = string.Format("${0}", relation.Name);

                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;
                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            var relatedRecords = record["$" + relation.Name] as List<EntityRecord>;
                            List<QueryObject> queries = new List<QueryObject>();
                            foreach (var relatedRecord in relatedRecords)
                                queries.Add(EntityQuery.QueryEQ(relField.Name, relatedRecord[relField.Name]));

                            if (queries.Count > 0)
                            {
                                QueryObject subListQueryObj = EntityQuery.QueryOR(queries.ToArray());
                                List<EntityRecord> subListResult = GetListRecords(entities, relEntity, ((RecordListRelationListItem)column).ListName, queryObj: subListQueryObj);
                                dataRecord[((RecordListRelationListItem)column).DataName] = subListResult;
                            }
                            else
                                dataRecord[((RecordListRelationListItem)column).DataName] = new List<object>();
                        }
                        else if (column is RecordListViewItem)
                        {
                            dataRecord[column.DataName] = GetViewRecords(entities, entity, ((RecordListViewItem)column).ViewName, "id", record["id"]);
                        }
                        else if (column is RecordListRelationTreeItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationTreeItem)column).RelationId);
                            string relName = string.Format("${0}", relation.Name);

                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;
                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            var relatedRecords = record["$" + relation.Name] as List<EntityRecord>;
                            dataRecord[((RecordListRelationTreeItem)column).DataName] = relatedRecords;
                        }
                        else if (column is RecordListRelationViewItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationViewItem)column).RelationId);
                            string relName = string.Format("${0}", relation.Name);

                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;
                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            List<EntityRecord> subViewResult = new List<EntityRecord>();
                            var relatedRecords = record["$" + relation.Name] as List<EntityRecord>;
                            foreach (var relatedRecord in relatedRecords)
                            {
                                subViewResult.AddRange(GetViewRecords(entities, relEntity, ((RecordListRelationViewItem)column).ViewName, relField.Name, relatedRecord[relField.Name]));
                            }
                            dataRecord[((RecordListRelationViewItem)column).DataName] = subViewResult;
                        }
                    }

                    resultDataList.Add(dataRecord);
                }
            }
            else
            {
                foreach (var record in result.Object.Data)
                {
                    EntityRecord dataRecord = new EntityRecord();
                    foreach (var prop in record.Properties)
                    {
                        //string propName = "$field" + (prop.Key.StartsWith("$") ? prop.Key : "$" + prop.Key);
                        string propName = prop.Key;
                        dataRecord[propName] = record[prop.Key];
                    }

                    resultDataList.Add(dataRecord);
                }
            }

            return resultDataList;
        }
Example #10
0
        public List<EntityRecord> GetListRecords(List<Entity> entities, Entity entity, string listName, int? page = null, QueryObject queryObj = null,
            int? pageSize = null, bool export = false, EntityRelation auxRelation = null, Guid? auxRelatedRecordId = null,
            string auxRelationDirection = "origin-target", List<KeyValuePair<string, string>> overwriteArgs = null, bool returnAllRecords = false)
        {
            if (entity == null)
                throw new Exception($"Entity '{entity.Name}' do not exist");

            RecordList list = null;
            if (entity != null && entity.RecordLists != null)
                list = entity.RecordLists.FirstOrDefault(l => l.Name == listName);

            if (list == null)
                throw new Exception($"Entity '{entity.Name}' do not have list named '{listName}'");

            //List<KeyValuePair<string, string>> queryStringOverwriteParameters = new List<KeyValuePair<string, string>>();
            //foreach (var key in Request.Query.Keys)
            //	queryStringOverwriteParameters.Add(new KeyValuePair<string, string>(key, Request.Query[key]));

            EntityQuery resultQuery = new EntityQuery(entity.Name, "*", queryObj, null, null, null, overwriteArgs);
            EntityRelationManager relManager = new EntityRelationManager();
            EntityRelationListResponse relListResponse = relManager.Read();
            List<EntityRelation> relationList = new List<EntityRelation>();
            if (relListResponse.Object != null)
                relationList = relListResponse.Object;

            if (list != null)
            {
                List<QuerySortObject> sortList = new List<QuerySortObject>();
                if (list.Sorts != null && list.Sorts.Count > 0)
                {
                    foreach (var sort in list.Sorts)
                    {
                        QuerySortType sortType;
                        if (Enum.TryParse<QuerySortType>(sort.SortType, true, out sortType))
                            sortList.Add(new QuerySortObject(sort.FieldName, sortType));
                    }
                    resultQuery.Sort = sortList.ToArray();
                }

                if (list.Query != null)
                {
                    var listQuery = RecordListQuery.ConvertQuery(list.Query);

                    if (queryObj != null)
                    {
                        //if (queryObj.SubQueries != null && queryObj.SubQueries.Any())
                        //	queryObj.SubQueries.Add(listQuery);
                        //else
                        queryObj = EntityQuery.QueryAND(listQuery, queryObj);
                    }
                    else
                        queryObj = listQuery;

                    resultQuery.Query = queryObj;
                }

                if (auxRelation != null && auxRelatedRecordId != null)
                {
                    string relationField = $"${auxRelation.Name}.id";
                    if (auxRelationDirection == "target-origin")
                        relationField = "$" + relationField;

                    var auxRelQuery = EntityQuery.QueryEQ(relationField, auxRelatedRecordId);
                    if (resultQuery.Query != null)
                        resultQuery.Query = EntityQuery.QueryAND(resultQuery.Query, auxRelQuery);
                    else
                        resultQuery.Query = auxRelQuery;

                }

                string queryFields = "id,";
                if (list.Columns != null)
                {
                    foreach (var column in list.Columns)
                    {
                        if (column is RecordListFieldItem)
                        {
                            if (((RecordListFieldItem)column).Meta.Name != "id")
                                queryFields += ((RecordListFieldItem)column).Meta.Name + ", ";
                        }
                        else if (column is RecordListRelationTreeItem)
                        {
                            if (export)
                                continue;

                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationTreeItem)column).RelationId);

                            string relName = relation != null ? string.Format("${0}.", relation.Name) : "";

                            Guid relEntityId = relation.OriginEntityId;
                            Guid relFieldId = relation.OriginFieldId;

                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            var treeId = (column as RecordListRelationTreeItem).TreeId;
                            RecordTree tree = relEntity.RecordTrees.Single(x => x.Id == treeId);

                            var relIdField = relEntity.Fields.Single(x => x.Name == "id");

                            List<Guid> fieldIdsToInclude = new List<Guid>();
                            fieldIdsToInclude.AddRange(tree.NodeObjectProperties);

                            if (!fieldIdsToInclude.Contains(relIdField.Id))
                                fieldIdsToInclude.Add(relIdField.Id);

                            if (!fieldIdsToInclude.Contains(tree.NodeNameFieldId))
                                fieldIdsToInclude.Add(tree.NodeNameFieldId);

                            if (!fieldIdsToInclude.Contains(tree.NodeLabelFieldId))
                                fieldIdsToInclude.Add(tree.NodeLabelFieldId);

                            if (!fieldIdsToInclude.Contains(relField.Id))
                                fieldIdsToInclude.Add(relField.Id);

                            foreach (var fieldId in fieldIdsToInclude)
                            {
                                var f = relEntity.Fields.SingleOrDefault(x => x.Id == fieldId);
                                if (f != null)
                                {
                                    string qFieldName = string.Format("{0}{1},", relName, f.Name);
                                    if (!queryFields.Contains(qFieldName))
                                        queryFields += qFieldName;
                                }
                            }

                            //always add target field in query, its value may be required for relative view and list
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == relation.TargetFieldId);
                            queryFields += field.Name + ", ";
                        }
                        else if (column is RecordListRelationFieldItem)
                        {
                            string targetOriginPrefix = "";
                            if (list.RelationOptions != null)
                            {
                                var options = list.RelationOptions.SingleOrDefault(x => x.RelationId == ((RecordListRelationFieldItem)column).RelationId);
                                if (options != null && options.Direction == "target-origin")
                                    targetOriginPrefix = "$";
                            }

                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationFieldItem)column).RelationId);
                            queryFields += string.Format(targetOriginPrefix + "${0}.{1}, ", relation.Name, ((RecordListRelationFieldItem)column).Meta.Name);

                            //add ID field automatically if not added
                            if (!queryFields.Contains(string.Format(targetOriginPrefix + "${0}.id", relation.Name)))
                                queryFields += string.Format(targetOriginPrefix + "${0}.id,", relation.Name);

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                        else if (column is RecordListListItem || column is RecordListViewItem)
                        {
                            if (export)
                                continue;

                            if (!queryFields.Contains(" id, ") && !queryFields.StartsWith("id,"))
                                queryFields += "id, ";
                        }
                        else if (column is RecordListRelationListItem)
                        {
                            if (export)
                                continue;

                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationListItem)column).RelationId);

                            string targetOriginPrefix = "";
                            if (list.RelationOptions != null)
                            {
                                var options = list.RelationOptions.SingleOrDefault(x => x.RelationId == ((RecordListRelationListItem)column).RelationId);
                                if (options != null && options.Direction == "target-origin")
                                    targetOriginPrefix = "$";
                            }

                            string relName = relation != null ? string.Format(targetOriginPrefix + "${0}.", relation.Name) : "";

                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;

                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            string queryFieldName = string.Format("{0}{1}, ", relName, relField.Name);

                            if (!queryFields.Contains(queryFieldName))
                                queryFields += queryFieldName;

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                        else if (column is RecordListRelationViewItem)
                        {
                            if (export)
                                continue;

                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationViewItem)column).RelationId);

                            string targetOriginPrefix = "";
                            if (list.RelationOptions != null)
                            {
                                var options = list.RelationOptions.SingleOrDefault(x => x.RelationId == ((RecordListRelationViewItem)column).RelationId);
                                if (options != null && options.Direction == "target-origin")
                                    targetOriginPrefix = "$";
                            }

                            string relName = relation != null ? string.Format(targetOriginPrefix + "${0}.", relation.Name) : "";

                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;

                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            string queryFieldName = string.Format("{0}{1}, ", relName, relField.Name);

                            if (!queryFields.Contains(queryFieldName))
                                queryFields += queryFieldName;

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                    }

                    if (queryFields.EndsWith(", "))
                        queryFields = queryFields.Remove(queryFields.Length - 2);

                    resultQuery.Fields = queryFields;

                }

                if (returnAllRecords)
                {
                    resultQuery.Skip = null;
                    resultQuery.Limit = null;
                }
                else
                {
                    if (!pageSize.HasValue)
                        pageSize = list.PageSize;

                    if (pageSize.Value > 0)
                    {
                        resultQuery.Limit = pageSize.Value;
                        if (page != null && page > 0)
                            resultQuery.Skip = (page - 1) * resultQuery.Limit;
                    }
                }
            }

            List<EntityRecord> resultDataList = new List<EntityRecord>();

            QueryResponse result = Find(resultQuery);
            if (!result.Success)
                if (result.Errors.Count > 0)
                {
                    throw new Exception(result.Message + ". Reason: " + result.Errors[0].Message);
                }
                else
                {
                    throw new Exception(result.Message);
                }

            if (list != null)
            {
                foreach (var record in result.Object.Data)
                {
                    EntityRecord dataRecord = new EntityRecord();
                    //always add id value
                    dataRecord["id"] = record["id"];

                    foreach (var column in list.Columns)
                    {
                        if (column is RecordListFieldItem)
                        {
                            dataRecord[column.DataName] = record[((RecordListFieldItem)column).FieldName];
                        }
                        else if (column is RecordListRelationFieldItem)
                        {
                            string propName = string.Format("${0}", ((RecordListRelationFieldItem)column).RelationName);
                            List<EntityRecord> relFieldRecords = (List<EntityRecord>)record[propName];

                            string idDataName = "$field" + propName + "$id";
                            if (!dataRecord.Properties.ContainsKey(idDataName))
                            {
                                List<object> idFieldRecord = new List<object>();
                                if (relFieldRecords != null)
                                {
                                    foreach (var relFieldRecord in relFieldRecords)
                                        idFieldRecord.Add(relFieldRecord["id"]);
                                }
                                dataRecord[idDataName] = idFieldRecord;
                            }

                            List<object> resultFieldRecord = new List<object>();
                            if (relFieldRecords != null)
                            {
                                foreach (var relFieldRecord in relFieldRecords)
                                {
                                    resultFieldRecord.Add(relFieldRecord[((RecordListRelationFieldItem)column).FieldName]);
                                }
                            }
                            dataRecord[column.DataName] = resultFieldRecord;

                        }
                        else if (column is RecordListListItem)
                        {
                            if (export)
                                continue;

                            dataRecord[column.DataName] = GetListRecords(entities, entity, ((RecordListListItem)column).ListName);
                        }
                        else if (column is RecordListRelationListItem)
                        {
                            if (export)
                                continue;

                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationListItem)column).RelationId);
                            string relName = string.Format("${0}", relation.Name);

                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;
                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            var relatedRecords = record["$" + relation.Name] as List<EntityRecord>;
                            List<QueryObject> queries = new List<QueryObject>();
                            foreach (var relatedRecord in relatedRecords)
                                queries.Add(EntityQuery.QueryEQ(relField.Name, relatedRecord[relField.Name]));

                            if (queries.Count > 0)
                            {
                                QueryObject subListQueryObj = EntityQuery.QueryOR(queries.ToArray());
                                List<EntityRecord> subListResult = GetListRecords(entities, relEntity, ((RecordListRelationListItem)column).ListName, queryObj: subListQueryObj);
                                dataRecord[((RecordListRelationListItem)column).DataName] = subListResult;
                            }
                            else
                                dataRecord[((RecordListRelationListItem)column).DataName] = new List<object>();
                        }
                        else if (column is RecordListViewItem)
                        {
                            if (export)
                                continue;

                            dataRecord[column.DataName] = GetViewRecords(entities, entity, ((RecordListViewItem)column).ViewName, "id", record["id"]);
                        }
                        else if (column is RecordListRelationTreeItem)
                        {
                            if (export)
                                continue;

                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationTreeItem)column).RelationId);
                            string relName = string.Format("${0}", relation.Name);

                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;
                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            var relatedRecords = record["$" + relation.Name] as List<EntityRecord>;
                            dataRecord[((RecordListRelationTreeItem)column).DataName] = relatedRecords;
                        }
                        else if (column is RecordListRelationViewItem)
                        {
                            if (export)
                                continue;

                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationViewItem)column).RelationId);
                            string relName = string.Format("${0}", relation.Name);

                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;
                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            List<EntityRecord> subViewResult = new List<EntityRecord>();
                            var relatedRecords = record["$" + relation.Name] as List<EntityRecord>;
                            foreach (var relatedRecord in relatedRecords)
                            {
                                subViewResult.AddRange(GetViewRecords(entities, relEntity, ((RecordListRelationViewItem)column).ViewName, relField.Name, relatedRecord[relField.Name]));
                            }
                            dataRecord[((RecordListRelationViewItem)column).DataName] = subViewResult;
                        }
                    }

                    resultDataList.Add(dataRecord);
                }
            }
            else
            {
                foreach (var record in result.Object.Data)
                {
                    EntityRecord dataRecord = new EntityRecord();
                    foreach (var prop in record.Properties)
                    {
                        //string propName = "$field" + (prop.Key.StartsWith("$") ? prop.Key : "$" + prop.Key);
                        string propName = prop.Key;
                        dataRecord[propName] = record[prop.Key];
                    }

                    resultDataList.Add(dataRecord);
                }
            }

            return resultDataList;
        }
Example #11
0
		private void ProcessQueryObject(Entity entity, QueryObject obj)
		{
			if (obj == null)
				return;

			if (obj.QueryType != QueryType.AND && obj.QueryType != QueryType.OR &&
				obj.QueryType != QueryType.RELATED && obj.QueryType != QueryType.NOTRELATED)
			{
				var field = entity.Fields.SingleOrDefault(x => x.Name == obj.FieldName);
				if (! ( obj.QueryType == QueryType.RELATED || obj.QueryType == QueryType.NOTRELATED ) )
				{
					if (field == null)
						throw new Exception(string.Format("There is not entity field '{0}' you try to query by.", obj.FieldName));
				}
				
				if (field is NumberField || field is AutoNumberField)
				{
					if (obj.FieldValue != null)
						obj.FieldValue = Convert.ToDecimal(obj.FieldValue);
				}
				else if (field is GuidField)
				{
					if (obj.FieldValue != null && obj.FieldValue is string)
					{
						var stringGuid = obj.FieldValue as string;
						if (!string.IsNullOrWhiteSpace(stringGuid))
							obj.FieldValue = new Guid(stringGuid);
						else
							obj.FieldValue = null;
					}
				}
				else if (field is CheckboxField)
				{
					if (obj.FieldValue != null && obj.FieldValue is string)
						obj.FieldValue = bool.Parse(obj.FieldValue as string);
				}
				else if (field is PasswordField && obj.FieldValue != null)
					obj.FieldValue = PasswordUtil.GetMd5Hash(obj.FieldValue as string);
			}

			if (obj.QueryType == QueryType.RELATED || obj.QueryType == QueryType.NOTRELATED)
			{
				var relation = relationRepository.Read(obj.FieldName);
				if( relation == null )
					throw new Exception(string.Format("There is not relation with name '{0}' used in your query.", obj.FieldName));

				if( relation.RelationType != EntityRelationType.ManyToMany )
					throw new Exception(string.Format("Only many to many relations can used in Related and NotRelated query operators.", obj.FieldName));

				var direction = obj.FieldValue as string ?? "origin-target";
                if ( relation.OriginEntityId == relation.TargetEntityId )
				{
					if (direction == "target-origin")
						obj.FieldName = $"#{obj.FieldName}_origins";
					else
						obj.FieldName = $"#{obj.FieldName}_targets";

				}
				else 
				{
					if (entity.Id == relation.OriginEntityId)
						obj.FieldName = $"#{obj.FieldName}_targets";
					else
						obj.FieldName = $"#{obj.FieldName}_origins";
				}
			}

			if (obj.QueryType == QueryType.AND || obj.QueryType == QueryType.OR)
			{
				if (obj.SubQueries != null && obj.SubQueries.Count > 0)
					foreach (var subObj in obj.SubQueries)
					{
						ProcessQueryObject(entity, subObj);
					}
			}
		}
Example #12
0
        private List<EntityRecord> GetListRecords(List<Entity> entities, Entity entity, string listName, int? page = null, QueryObject queryObj = null, string filter = null)
        {
            var filterQuery = CreateFilterQuery(filter, entities);
            if (filterQuery != null)
            {
                if (queryObj != null)
                    queryObj = EntityQuery.QueryAND(queryObj, filterQuery);
                else
                    queryObj = filterQuery;
            }

            EntityQuery resultQuery = new EntityQuery(entity.Name, "*", queryObj, null, null, null);
            EntityRelationManager relManager = new EntityRelationManager(Storage);
            EntityRelationListResponse relListResponse = relManager.Read();
            List<EntityRelation> relationList = new List<EntityRelation>();
            if (relListResponse.Object != null)
                relationList = relListResponse.Object;

            RecordList list = null;
            if (entity != null && entity.RecordLists != null)
                list = entity.RecordLists.FirstOrDefault(l => l.Name == listName);

            if (list != null)
            {
                List<QuerySortObject> sortList = new List<QuerySortObject>();
                if (list.Sorts != null && list.Sorts.Count > 0)
                {
                    foreach (var sort in list.Sorts)
                    {
                        QuerySortType sortType;
                        if (Enum.TryParse<QuerySortType>(sort.SortType, true, out sortType))
                            sortList.Add(new QuerySortObject(sort.FieldName, sortType));
                    }
                    resultQuery.Sort = sortList.ToArray();
                }

                if (list.Query != null)
                {
                    if (queryObj != null)
                    {
                        List<QueryObject> subQueries = new List<QueryObject>();
                        subQueries.Add(RecordListQuery.ConvertQuery(list.Query));
                        queryObj.SubQueries = subQueries;
                    }
                    else
                        queryObj = RecordListQuery.ConvertQuery(list.Query);

                    resultQuery.Query = queryObj;
                }

                string queryFields = "id,";
                if (list.Columns != null)
                {
                    foreach (var column in list.Columns)
                    {
                        if (column is RecordListFieldItem)
                        {
                            if (((RecordListFieldItem)column).Meta.Name != "id")
                                queryFields += ((RecordListFieldItem)column).Meta.Name + ", ";
                        }
                        else if (column is RecordListRelationFieldItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationFieldItem)column).RelationId);
                            queryFields += string.Format("${0}.{1}, ", relation.Name, ((RecordListRelationFieldItem)column).Meta.Name);

                            //add ID field automatically if not added
                            if (!queryFields.Contains(string.Format("${0}.id", relation.Name)))
                                queryFields += string.Format("${0}.id,", relation.Name);

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                        else if (column is RecordListListItem || column is RecordListViewItem)
                        {
                            if (!queryFields.Contains(" id, ") && !queryFields.StartsWith("id,"))
                                queryFields += "id, ";
                        }
                        else if (column is RecordListRelationListItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationListItem)column).RelationId);

                            string relName = relation != null ? string.Format("${0}.", relation.Name) : "";

                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;

                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            string queryFieldName = string.Format("{0}{1}, ", relName, relField.Name);

                            if (!queryFields.Contains(queryFieldName))
                                queryFields += queryFieldName;

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                        else if (column is RecordListRelationViewItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationViewItem)column).RelationId);

                            string relName = relation != null ? string.Format("${0}.", relation.Name) : "";

                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;

                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            string queryFieldName = string.Format("{0}{1}, ", relName, relField.Name);

                            if (!queryFields.Contains(queryFieldName))
                                queryFields += queryFieldName;

                            //always add origin field in query, its value may be required for relative view and list
                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            queryFields += field.Name + ", ";
                        }
                    }

                    if (queryFields.EndsWith(", "))
                        queryFields = queryFields.Remove(queryFields.Length - 2);

                    resultQuery.Fields = queryFields;

                }

                if (list.PageSize > 0)
                {
                    resultQuery.Limit = list.PageSize;
                    if (page != null && page > 0)
                        resultQuery.Skip = (page - 1) * resultQuery.Limit;
                }

            }

            List<EntityRecord> resultDataList = new List<EntityRecord>();

            QueryResponse result = recMan.Find(resultQuery);
            if (!result.Success)
                return resultDataList;

            if (list != null)
            {
                foreach (var record in result.Object.Data)
                {
                    EntityRecord dataRecord = new EntityRecord();
                    //always add id value
                    dataRecord["id"] = record["id"];

                    foreach (var column in list.Columns)
                    {
                        if (column is RecordListFieldItem)
                        {
                            dataRecord[column.DataName] = record[((RecordListFieldItem)column).FieldName];
                        }
                        else if (column is RecordListRelationFieldItem)
                        {
                            string propName = string.Format("${0}", ((RecordListRelationFieldItem)column).RelationName);
                            List<EntityRecord> relFieldRecords = (List<EntityRecord>)record[propName];

                            string idDataName = "$field" + propName + "$id";
                            if (!dataRecord.Properties.ContainsKey(idDataName))
                            {
                                List<object> idFieldRecord = new List<object>();
                                if (relFieldRecords != null)
                                {
                                    foreach (var relFieldRecord in relFieldRecords)
                                        idFieldRecord.Add(relFieldRecord["id"]);
                                }
                                dataRecord[idDataName] = idFieldRecord;
                            }

                            List<object> resultFieldRecord = new List<object>();
                            if (relFieldRecords != null)
                            {
                                foreach (var relFieldRecord in relFieldRecords)
                                {
                                    resultFieldRecord.Add(relFieldRecord[((RecordListRelationFieldItem)column).FieldName]);
                                }
                            }
                            dataRecord[column.DataName] = resultFieldRecord;

                        }
                        else if (column is RecordListListItem)
                        {
                            QueryObject subListQueryObj = new QueryObject();
                            subListQueryObj.QueryType = QueryType.AND;
                            subListQueryObj.SubQueries = new List<QueryObject>();
                            subListQueryObj.SubQueries.Add(new QueryObject { FieldName = "id", FieldValue = record["id"], QueryType = QueryType.EQ });

                            List<EntityRecord> subListResult = GetListRecords(entities, entity, ((RecordListListItem)column).ListName, queryObj: subListQueryObj);
                            dataRecord[column.DataName] = subListResult;
                        }
                        else if (column is RecordListRelationListItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationListItem)column).RelationId);
                            string relName = string.Format("${0}", relation.Name);

                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;
                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            QueryObject subListQueryObj = EntityQuery.QueryEQ(relField.Name, record[field.Name]);

                            List<EntityRecord> subListResult = GetListRecords(entities, relEntity, ((RecordListRelationListItem)column).ListName, queryObj: subListQueryObj);
                            dataRecord[column.DataName] = subListResult;
                        }
                        else if (column is RecordListViewItem)
                        {
                            List<EntityRecord> subViewResult = GetViewRecords(entities, entity, ((RecordListViewItem)column).ViewName, "id", record["id"]);
                            dataRecord[column.DataName] = subViewResult;
                        }
                        else if (column is RecordListRelationViewItem)
                        {
                            EntityRelation relation = relationList.FirstOrDefault(r => r.Id == ((RecordListRelationViewItem)column).RelationId);
                            string relName = string.Format("${0}", relation.Name);

                            Guid fieldId = entity.Id == relation.OriginEntityId ? relation.OriginFieldId : relation.TargetFieldId;
                            Field field = entity.Fields.FirstOrDefault(f => f.Id == fieldId);
                            Guid relEntityId = entity.Id == relation.OriginEntityId ? relation.TargetEntityId : relation.OriginEntityId;
                            Guid relFieldId = entity.Id == relation.OriginEntityId ? relation.TargetFieldId : relation.OriginFieldId;
                            Entity relEntity = entities.FirstOrDefault(e => e.Id == relEntityId);
                            Field relField = relEntity.Fields.FirstOrDefault(f => f.Id == relFieldId);

                            List<EntityRecord> subViewResult = GetViewRecords(entities, relEntity, ((RecordListRelationViewItem)column).ViewName, relField.Name, record[field.Name]);
                            dataRecord[column.DataName] = subViewResult;

                        }
                    }

                    resultDataList.Add(dataRecord);
                }
            }
            else
            {
                foreach (var record in result.Object.Data)
                {
                    EntityRecord dataRecord = new EntityRecord();
                    foreach (var prop in record.Properties)
                    {
                        //string propName = "$field" + (prop.Key.StartsWith("$") ? prop.Key : "$" + prop.Key);
                        string propName = prop.Key;
                        dataRecord[propName] = record[prop.Key];
                    }

                    resultDataList.Add(dataRecord);
                }
            }

            return resultDataList;
        }
Example #13
0
        public long Count(string entityName, QueryObject query)
        {
            string tableName = RECORD_COLLECTION_PREFIX + entityName;
            using (DbConnection con = DbContext.Current.CreateConnection())
            {
                string sql = $"SELECT COUNT( id ) FROM {tableName} ";
                if(ContainsRelationalQuery(query))
                    sql = $"SELECT COUNT( DISTINCT id ) FROM {tableName} ";

                string whereSql = string.Empty;
                string whereJoinSql = string.Empty;

                Entity entity = new EntityManager().ReadEntity(entityName).Object;
                List<NpgsqlParameter> parameters = new List<NpgsqlParameter>();
                GenerateWhereClause(query, entity, ref whereSql, ref whereJoinSql, ref parameters);

                if (whereJoinSql.Length > 0)
                    sql = sql + "  " + whereJoinSql;

                if (whereSql.Length > 0)
                    sql = sql + " WHERE " + whereSql;

                NpgsqlCommand command = con.CreateCommand(sql);

                if (parameters.Count > 0)
                    command.Parameters.AddRange(parameters.ToArray());

                return (long)command.ExecuteScalar();
            }
        }
Example #14
0
        private void GenerateWhereClause(QueryObject query, Entity entity, ref string sql, ref string joinSql, ref List<NpgsqlParameter> parameters,
            List<KeyValuePair<string, string>> overwriteArgs = null)
        {
            Field field = null;
            FieldType fieldType = FieldType.GuidField;
            string paramName = null;
            string completeFieldName = null;
            if (!string.IsNullOrWhiteSpace(query.FieldName))
            {
                if (!query.FieldName.Contains(RELATION_NAME_RESULT_SEPARATOR))
                {
                    field = entity.Fields.SingleOrDefault(x => x.Name == query.FieldName);
                    fieldType = field.GetFieldType();
                    string entityTablePrefix = GetTableNameForEntity(entity) + ".";
                    completeFieldName = entityTablePrefix + query.FieldName;
                    paramName = "@" + query.FieldName + "_" + Guid.NewGuid().ToString().Replace("-", "");

                    bool skipClause;
                    var value = ExtractQueryFieldValue(query.FieldValue, field, overwriteArgs, out skipClause) ?? DBNull.Value;
                    if (skipClause)
                        return;

                    parameters.Add(new NpgsqlParameter(paramName, value));
                }
                else
                {
                    var relationData = query.FieldName.Split(RELATION_SEPARATOR).Select(x => x.Trim()).Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
                    if (relationData.Count > 2)
                        throw new Exception(string.Format("The specified query filter field '{0}' is incorrect. Only first level relation can be specified.", query.FieldName));

                    string relationName = relationData[0];
                    string relationFieldName = relationData[1];
                    string direction = "origin-target";

                    if (string.IsNullOrWhiteSpace(relationName) || relationName == "$" || relationName == "$$")
                        throw new Exception(string.Format("Invalid relation '{0}'. The relation name is not specified.", query.FieldName));
                    else if (!relationName.StartsWith("$"))
                        throw new Exception(string.Format("Invalid relation '{0}'. The relation name is not correct.", query.FieldName));
                    else
                        relationName = relationName.Substring(1);

                    //check for target priority mark $$
                    if (relationName.StartsWith("$"))
                    {
                        direction = "target-origin";
                        relationName = relationName.Substring(1);
                    }

                    if (string.IsNullOrWhiteSpace(relationFieldName))
                        throw new Exception(string.Format("Invalid query result field '{0}'. The relation field name is not specified.", query.FieldName));

                    RelationFieldMeta relationFieldMeta = new RelationFieldMeta();
                    relationFieldMeta.Name = "$" + relationName;
                    relationFieldMeta.Direction = direction;

                    relationFieldMeta.Relation = relMan.Read().Object.SingleOrDefault(x => x.Name == relationName);
                    if (relationFieldMeta.Relation == null)
                        throw new Exception(string.Format("Invalid relation '{0}'. The relation does not exist.", query.FieldName));

                    if (relationFieldMeta.Relation.TargetEntityId != entity.Id && relationFieldMeta.Relation.OriginEntityId != entity.Id)
                        throw new Exception(string.Format("Invalid relation '{0}'. The relation does relate to queries entity.", query.FieldName));

                    if (relationFieldMeta.Direction != direction)
                        throw new Exception(string.Format("You are trying to query relation '{0}' from origin->target and target->origin direction in single query. This is not allowed.", query.FieldName));

                    //Entity entity = entMan.ReadEntity(query.EntityName).Object;
                    relationFieldMeta.TargetEntity = entMan.ReadEntity(relationFieldMeta.Relation.TargetEntityId).Object;
                    relationFieldMeta.OriginEntity = entMan.ReadEntity(relationFieldMeta.Relation.OriginEntityId).Object;

                    //this should not happen in a perfect (no bugs) world
                    if (relationFieldMeta.OriginEntity == null)
                        throw new Exception(string.Format("Invalid query result field '{0}'. Related (origin)entity is missing.", query.FieldName));
                    if (relationFieldMeta.TargetEntity == null)
                        throw new Exception(string.Format("Invalid query result field '{0}'. Related (target)entity is missing.", query.FieldName));

                    relationFieldMeta.TargetField = relationFieldMeta.TargetEntity.Fields.Single(x => x.Id == relationFieldMeta.Relation.TargetFieldId);
                    relationFieldMeta.OriginField = relationFieldMeta.OriginEntity.Fields.Single(x => x.Id == relationFieldMeta.Relation.OriginFieldId);

                    //this should not happen in a perfect (no bugs) world
                    if (relationFieldMeta.OriginField == null)
                        throw new Exception(string.Format("Invalid query result field '{0}'. Related (origin)field is missing.", query.FieldName));
                    if (relationFieldMeta.TargetField == null)
                        throw new Exception(string.Format("Invalid query result field '{0}'. Related (target)field is missing.", query.FieldName));

                    Entity joinToEntity = null;
                    if (relationFieldMeta.TargetEntity.Id == entity.Id)
                        joinToEntity = relationFieldMeta.OriginEntity;
                    else
                        joinToEntity = relationFieldMeta.TargetEntity;

                    relationFieldMeta.Entity = joinToEntity;

                    var relatedField = joinToEntity.Fields.SingleOrDefault(x => x.Name == relationFieldName);
                    if (relatedField == null)
                        throw new Exception(string.Format("Invalid query result field '{0}'. The relation field does not exist.", query.FieldName));

                    string relationJoinSql = string.Empty;
                    completeFieldName = relationName + "." + relationFieldName;
                    fieldType = relatedField.GetFieldType();
                    paramName = "@" + relationFieldName + "_" + Guid.NewGuid().ToString().Replace("-", "");

                    bool skipClause;
                    var value = ExtractQueryFieldValue(query.FieldValue, relatedField, overwriteArgs, out skipClause) ?? DBNull.Value;
                    if (skipClause)
                        return;

                    parameters.Add(new NpgsqlParameter(paramName, value));

                    if (relationFieldMeta.Relation.RelationType == EntityRelationType.OneToOne)
                    {
                        //when the relation is origin -> target entity
                        if (relationFieldMeta.Relation.OriginEntityId == entity.Id)
                        {
                            relationJoinSql = string.Format(FILTER_JOIN,
                                GetTableNameForEntity(relationFieldMeta.TargetEntity), relationName,
                                relationName, relationFieldMeta.TargetField.Name,
                                GetTableNameForEntity(relationFieldMeta.OriginEntity), relationFieldMeta.OriginField.Name);
                        }
                        else //when the relation is target -> origin, we have to query origin entity
                        {
                            relationJoinSql = string.Format(FILTER_JOIN,
                                   GetTableNameForEntity(relationFieldMeta.OriginEntity), relationName,
                                   relationName, relationFieldMeta.OriginField.Name,
                                   GetTableNameForEntity(relationFieldMeta.TargetEntity), relationFieldMeta.TargetField.Name);
                        }
                    }
                    else if (relationFieldMeta.Relation.RelationType == EntityRelationType.OneToMany)
                    {
                        //when origin and target entity are different, then direction don't matter
                        if (relationFieldMeta.Relation.OriginEntityId != relationFieldMeta.Relation.TargetEntityId)
                        {
                            //when the relation is origin -> target entity
                            if (relationFieldMeta.Relation.OriginEntityId == entity.Id)
                            {
                                relationJoinSql = string.Format(FILTER_JOIN,
                                    GetTableNameForEntity(relationFieldMeta.TargetEntity), relationName,
                                    relationName, relationFieldMeta.TargetField.Name,
                                    GetTableNameForEntity(relationFieldMeta.OriginEntity), relationFieldMeta.OriginField.Name);
                            }
                            else //when the relation is target -> origin, we have to query origin entity
                            {
                                relationJoinSql = string.Format(FILTER_JOIN,
                                    GetTableNameForEntity(relationFieldMeta.OriginEntity), relationName,
                                    relationName, relationFieldMeta.OriginField.Name,
                                    GetTableNameForEntity(relationFieldMeta.TargetEntity), relationFieldMeta.TargetField.Name);
                            }
                        }
                        else //when the origin entity is same as target entity direction matters
                        {
                            if (relationFieldMeta.Direction == "target-origin")
                            {
                                relationJoinSql = string.Format(FILTER_JOIN,
                                   GetTableNameForEntity(relationFieldMeta.OriginEntity), relationName,
                                   relationName, relationFieldMeta.OriginField.Name,
                                   GetTableNameForEntity(relationFieldMeta.TargetEntity), relationFieldMeta.TargetField.Name);
                            }
                            else
                            {
                                relationJoinSql = string.Format(FILTER_JOIN,
                                    GetTableNameForEntity(relationFieldMeta.TargetEntity), relationName,
                                    relationName, relationFieldMeta.TargetField.Name,
                                    GetTableNameForEntity(relationFieldMeta.OriginEntity), relationFieldMeta.OriginField.Name);
                            }
                        }
                    }
                    else if (relationFieldMeta.Relation.RelationType == EntityRelationType.ManyToMany)
                    {
                        string relationTable = "rel_" + relationFieldMeta.Relation.Name;
                        string targetJoinAlias = relationName + "_target";
                        string originJoinAlias = relationName + "_origin";
                        string targetJoinTable = GetTableNameForEntity(relationFieldMeta.TargetEntity);
                        string originJoinTable = GetTableNameForEntity(relationFieldMeta.OriginEntity);

                        //if target is entity we query
                        if (entity.Id == relationFieldMeta.TargetEntity.Id)
                        {
                            relationJoinSql = string.Format(FILTER_JOIN,
                                     /*LEFT OUTER JOIN*/ relationTable, /* */ targetJoinAlias /*ON*/,
                                     targetJoinAlias, /*.*/ "target_id", /* =  */
                                     targetJoinTable, /*.*/ relationFieldMeta.TargetField.Name);

                            relationJoinSql = relationJoinSql + "\r\n" + string.Format(FILTER_JOIN,
                                    /*LEFT OUTER JOIN*/ originJoinTable, /* */ originJoinAlias /*ON*/,
                                    targetJoinAlias, /*.*/ "origin_id", /* =  */
                                    originJoinTable, /*.*/ relationFieldMeta.OriginField.Name);

                            completeFieldName = originJoinAlias + "." + relationFieldName;
                        }
                        else // if origin is entity we query
                        {
                            relationJoinSql = string.Format(FILTER_JOIN,
                                    /*LEFT OUTER JOIN*/ relationTable, /* */ originJoinAlias /*ON*/,
                                    originJoinAlias, /*.*/ "origin_id", /* =  */
                                    originJoinTable, /*.*/ relationFieldMeta.OriginField.Name);

                            relationJoinSql = relationJoinSql + "\r\n" + string.Format(FILTER_JOIN,
                                      /*LEFT OUTER JOIN*/ targetJoinTable, /* */ targetJoinAlias /*ON*/,
                                    originJoinAlias, /*.*/ "target_id", /* =  */
                                    targetJoinAlias, /*.*/ relationFieldMeta.TargetField.Name);

                            completeFieldName = targetJoinAlias + "." + relationFieldName;
                        }
                    }

                    if (!joinSql.Contains(relationJoinSql))
                        joinSql = joinSql + "\r\n" + relationJoinSql;

                }

                if ((fieldType == FieldType.MultiSelectField || fieldType == FieldType.TreeSelectField) &&
                      !(query.QueryType == QueryType.EQ || query.QueryType == QueryType.NOT))
                    throw new Exception("The query operator is not supported on field '" + fieldType.ToString() + "'");
            }

            if (sql.Length > 0)
                sql = sql + " AND ";

            switch (query.QueryType)
            {
                case QueryType.EQ:
                    {
                        //if (fieldType == FieldType.MultiSelectField)
                        //{
                        //	var parameter = parameters.Single(x => x.ParameterName == paramName);
                        //	parameter.Value = new List<string>() { (string)query.FieldValue };
                        //	sql = sql + " " + paramName + " IN ( " + completeFieldName + " )";
                        //}
                        //else if (fieldType == FieldType.TreeSelectField)
                        //{
                        //	var parameter = parameters.Single(x => x.ParameterName == paramName);
                        //	parameter.Value = new List<Guid>() { (Guid)query.FieldValue };
                        //	sql = sql + " " + paramName + " IN ( " + completeFieldName + " )";
                        //}
                        //else
                        if (query.FieldValue == null || DBNull.Value == query.FieldValue)
                            sql = sql + " " + completeFieldName + " IS NULL";
                        else
                            sql = sql + " " + completeFieldName + "=" + paramName;
                        return;
                    }
                case QueryType.NOT:
                    {
                        //if (fieldType == FieldType.MultiSelectField)
                        //{
                        //	var parameter = parameters.Single(x => x.ParameterName == paramName);
                        //	parameter.Value = new List<string>() { (string)query.FieldValue };
                        //	sql = sql + " " + paramName + " NOT IN ( " + completeFieldName + " )";
                        //}
                        //else if (fieldType == FieldType.TreeSelectField)
                        //{
                        //	var parameter = parameters.Single(x => x.ParameterName == paramName);
                        //	parameter.Value = new List<Guid>() { (Guid)query.FieldValue };
                        //	sql = sql + " " + paramName + " NOT IN ( " + completeFieldName + " )";
                        //}
                        //else
                        if (query.FieldValue == null || DBNull.Value == query.FieldValue)
                            sql = sql + " " + completeFieldName + " IS NOT NULL";
                        else
                            sql = sql + " " + completeFieldName + "<>" + paramName;

                        return;
                    }
                case QueryType.LT:
                    {
                        sql = sql + " " + completeFieldName + "<" + paramName;
                        return;
                    }
                case QueryType.LTE:
                    {
                        sql = sql + " " + completeFieldName + "<=" + paramName;
                        return;
                    }
                case QueryType.GT:
                    {
                        sql = sql + " " + completeFieldName + ">" + paramName;
                        return;
                    }
                case QueryType.GTE:
                    {
                        sql = sql + " " + completeFieldName + ">=" + paramName;
                        return;
                    }
                case QueryType.CONTAINS:
                    {
                        var parameter = parameters.Single(x => x.ParameterName == paramName);
                        parameter.Value = "%" + parameter.Value + "%";
                        sql = sql + " " + completeFieldName + " ILIKE " + paramName;
                        return;
                    }
                case QueryType.STARTSWITH:
                    {
                        var parameter = parameters.Single(x => x.ParameterName == paramName);
                        parameter.Value = parameter.Value + "%";
                        sql = sql + " " + completeFieldName + " ILIKE " + paramName;
                        return;
                    }
                case QueryType.REGEX:
                    {
                        var regexOperator = "~";
                        switch (query.RegexOperator)
                        {
                            case QueryObjectRegexOperator.MatchCaseSensitive:
                                regexOperator = "~";
                                break;
                            case QueryObjectRegexOperator.MatchCaseInsensitive:
                                regexOperator = "~*";
                                break;
                            case QueryObjectRegexOperator.DontMatchCaseSensitive:
                                regexOperator = "!~";
                                break;
                            case QueryObjectRegexOperator.DontMatchCaseInsensitive:
                                regexOperator = "!~*";
                                break;
                        }

                        sql = sql + " " + completeFieldName + " " + regexOperator + " " + paramName;
                        return;
                    }
                case QueryType.RELATED:
                    {
                        //TODO
                        throw new NotImplementedException();
                    }
                case QueryType.NOTRELATED:
                    {
                        //TODO
                        throw new NotImplementedException();
                    }
                case QueryType.AND:
                    {
                        if (query.SubQueries.Count == 1)
                            GenerateWhereClause(query.SubQueries[0], entity, ref sql, ref joinSql, ref parameters, overwriteArgs);
                        else
                        {
                            string andSql = string.Empty;
                            foreach (var q in query.SubQueries)
                            {
                                string subQuerySql = string.Empty;
                                GenerateWhereClause(q, entity, ref subQuerySql, ref joinSql, ref parameters, overwriteArgs);
                                if (andSql.Length == 0)
                                    andSql = subQuerySql;
                                else if (subQuerySql.Length > 0)
                                    andSql = andSql + " AND " + subQuerySql;
                            }

                            if (andSql.Length > 0)
                                sql = sql + " ( " + andSql + " )";
                        }
                        return;
                    }
                case QueryType.OR:
                    {
                        if (query.SubQueries.Count == 1)
                            GenerateWhereClause(query.SubQueries[0], entity, ref sql, ref joinSql, ref parameters, overwriteArgs);
                        else
                        {
                            string orSql = string.Empty;
                            foreach (var q in query.SubQueries)
                            {
                                string subQuerySql = string.Empty;
                                GenerateWhereClause(q, entity, ref subQuerySql, ref joinSql, ref parameters, overwriteArgs);
                                if (orSql.Length == 0)
                                    orSql = subQuerySql;
                                else if (subQuerySql.Length > 0)
                                    orSql = orSql + " OR " + subQuerySql;
                            }

                            if (orSql.Length > 0)
                                sql = sql + " ( " + orSql + " )";
                        }
                        return;
                    }
                default:
                    throw new Exception("Not supported query type");
            }
        }
Example #15
0
        private static bool ContainsRelationalQuery(QueryObject query)
        {
            Queue<QueryObject> queue = new Queue<QueryObject>();

            if (query == null)
                return false;

            queue.Enqueue(query);
            while(queue.Count > 0 )
            {
                var q = queue.Dequeue();
                if( q.SubQueries != null && q.SubQueries.Count > 0 )
                {
                    foreach (var sq in q.SubQueries)
                        queue.Enqueue(sq);
                }
                if (q.FieldName != null && q.FieldName.Contains(RELATION_SEPARATOR))
                    return true;
            }

            return false;
        }