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;
}
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);
}
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);
}
}
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;
}
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);
}
}
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;
}
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;
}
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);
}
}
}
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;
}
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();
}
}
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");
}
}
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;
}