Esempio n. 1
0
        public static DynamicSql SqlJoin(
            this DynamicSql query, ResourceQueryProjection model,
            ResourceQueryFilter filter)
        {
            query = DynamicSql.DeepClone(query);
            var joins = model.GetFieldsArr()
                        .Where(f => ResourceQueryProjection.Joins.ContainsKey(f))
                        .Select(f => ResourceQueryProjection.Joins[f]);

            if (joins.Any())
            {
                var joinClause = string.Join('\n', joins);
                query.DynamicForm = query.DynamicForm
                                    .Replace(DynamicSql.JOIN, joinClause);
                if (filter != null)
                {
                    var contentFilters = new List <string>();
                    if (filter.lang != null)
                    {
                        var paramName           = query.AddAutoIncrParam(filter.lang);
                        var resourceContentLang = $"{nameof(ResourceContent)}.{nameof(ResourceContent.Lang)}";
                        contentFilters.Add($"{resourceContentLang}=@{paramName}");
                    }
                    if (contentFilters.Any())
                    {
                        var whereClause = "WHERE " + string.Join(" AND ", contentFilters);
                        query.DynamicForm = query.DynamicForm
                                            .Replace(ResourceQueryPlaceholder.RES_CONTENT_FILTER, whereClause);
                    }
                }
            }
            return(query);
        }
Esempio n. 2
0
        public async Task <IActionResult> Get([FromQuery] ResourceQueryFilter filter,
                                              [FromQuery] ResourceQuerySort sort,
                                              [FromQuery] ResourceQueryProjection projection,
                                              [FromQuery] ResourceQueryPaging paging,
                                              [FromQuery] ResourceQueryOptions options)
        {
            var validationResult = _service.ValidateGetResources(
                User, filter, sort, projection, paging, options);

            if (!validationResult.Valid)
            {
                return(BadRequest(validationResult.Result));
            }
            var result = await _service.QueryResourceDynamic(
                projection, options, filter, sort, paging);

            if (options.single_only)
            {
                if (result == null)
                {
                    return(NotFound(new AppResultBuilder().NotFound()));
                }
                return(Ok(new AppResultBuilder().Success(result.SingleResult)));
            }
            return(Ok(new AppResultBuilder().Success(result)));
        }
Esempio n. 3
0
        public async Task <QueryResult <IDictionary <string, object> > > QueryResourceDynamic(
            ResourceQueryProjection projection,
            ResourceQueryOptions options,
            ResourceQueryFilter filter = null,
            ResourceQuerySort sort     = null,
            ResourceQueryPaging paging = null)
        {
            var query = Resources;

            #region General
            if (filter != null)
            {
                query = query.Filter(filter);
            }
            query = query.Project(projection);
            int?totalCount = null;
            if (options.count_total)
            {
                totalCount = query.Count();
            }
            #endregion
            if (!options.single_only)
            {
                #region List query
                if (sort != null)
                {
                    query = query.Sort(sort);
                }
                if (paging != null && (!options.load_all || !ResourceQueryOptions.IsLoadAllAllowed))
                {
                    query = query.SelectPage(paging.page, paging.limit);
                }
                #endregion
            }

            if (options.single_only)
            {
                var single = query.SingleOrDefault();
                if (single == null)
                {
                    return(null);
                }
                var singleResult = GetResourceDynamic(single, projection, options);
                return(new QueryResult <IDictionary <string, object> >()
                {
                    Single = singleResult
                });
            }
            var entities = query.ToList();
            var list     = GetResourceDynamic(entities, projection, options);
            var result   = new QueryResult <IDictionary <string, object> >();
            result.List = list;
            if (options.count_total)
            {
                result.Count = totalCount;
            }
            return(result);
        }
Esempio n. 4
0
 public ValidationData ValidateGetResources(
     ClaimsPrincipal principal,
     ResourceQueryFilter filter,
     ResourceQuerySort sort,
     ResourceQueryProjection projection,
     ResourceQueryPaging paging,
     ResourceQueryOptions options)
 {
     return(new ValidationData());
 }
Esempio n. 5
0
        public List <IDictionary <string, object> > GetResourceDynamic(
            IEnumerable <Resource> rows, ResourceQueryProjection projection,
            ResourceQueryOptions options)
        {
            var list = new List <IDictionary <string, object> >();

            foreach (var o in rows)
            {
                var obj = GetResourceDynamic(o, projection, options);
                list.Add(obj);
            }
            return(list);
        }
 public static IQueryable <Resource> Project(
     this IQueryable <Resource> query, ResourceQueryProjection projection)
 {
     foreach (var f in projection.GetFieldsArr())
     {
         if (ResourceQueryProjection.MAPS.ContainsKey(f))
         {
             foreach (var prop in ResourceQueryProjection.MAPS[f])
             {
                 query = query.Include(prop);
             }
         }
     }
     return(query);
 }
Esempio n. 7
0
        public QueryResult <IDictionary <string, object> > GetResourceDynamic(
            IEnumerable <ResourceQueryRow> rows, ResourceQueryProjection projection,
            ResourceQueryOptions options, int?totalCount = null)
        {
            var list = new List <IDictionary <string, object> >();

            foreach (var o in rows)
            {
                var obj = GetResourceDynamic(o, projection, options);
                list.Add(obj);
            }
            var resp = new QueryResult <IDictionary <string, object> >();

            resp.Results = list;
            if (options.count_total)
            {
                resp.TotalCount = totalCount;
            }
            return(resp);
        }
Esempio n. 8
0
        public IDictionary <string, object> GetResourceDynamic(
            Resource row, ResourceQueryProjection projection,
            ResourceQueryOptions options)
        {
            var obj = new Dictionary <string, object>();

            foreach (var f in projection.GetFieldsArr())
            {
                switch (f)
                {
                case ResourceQueryProjection.INFO:
                {
                    var entity = row;
                    obj["id"]   = entity.Id;
                    obj["name"] = entity.Name;
                }
                break;
                }
            }
            return(obj);
        }
Esempio n. 9
0
        public static DynamicSql SqlProjectFields(
            this DynamicSql query, ResourceQueryProjection model)
        {
            query = DynamicSql.DeepClone(query);
            var finalFields = model.GetFieldsArr()
                              .Where(f => ResourceQueryProjection.Projections.ContainsKey(f))
                              .Select(f => ResourceQueryProjection.Projections[f]);

            if (finalFields.Any())
            {
                var projectionClause = string.Join(',', finalFields);
                query.DynamicForm = query.DynamicForm
                                    .Replace(DynamicSql.PROJECTION, projectionClause);
            }
            var finalResults = model.GetFieldsArr()
                               .Where(f => ResourceQueryProjection.Results.ContainsKey(f))
                               .Select(f => ResourceQueryProjection.Results[f]);

            query.MultiResults.AddRange(finalResults);
            return(query);
        }
        public async Task <IActionResult> Get([FromQuery][QueryObject] ResourceQueryFilter filter,
                                              [FromQuery] ResourceQuerySort sort,
                                              [FromQuery] ResourceQueryProjection projection,
                                              [FromQuery] ResourceQueryPaging paging,
                                              [FromQuery] ResourceQueryOptions options)
        {
            var validationData = _service.ValidateGetResources(
                User, filter, sort, projection, paging, options);

            if (!validationData.IsValid)
            {
                return(BadRequest(AppResult.FailValidation(data: validationData)));
            }
            var result = await _service.QueryResourceDynamic(
                projection, options, filter, sort, paging);

            if (options.single_only && result == null)
            {
                return(NotFound(AppResult.NotFound()));
            }
            return(Ok(AppResult.Success(result)));
        }
Esempio n. 11
0
        public static DynamicSql SqlExtras(
            this DynamicSql query, ResourceQueryProjection model,
            ResourceQueryFilter filter)
        {
            query = DynamicSql.DeepClone(query);
            var extras = model.GetFieldsArr()
                         .Where(f => ResourceQueryProjection.Extras.ContainsKey(f))
                         .Select(f => ResourceQueryProjection.Extras[f]);

            if (extras.Any())
            {
                var extraSqls     = string.Join(';', extras);
                var originalQuery = query.PreparedViewForm;
                query.DynamicForm += ";\n" + extraSqls;
                query.DynamicForm  = query.DynamicForm
                                     .Replace(ResourceQueryPlaceholder.RES_SUB_QUERY, originalQuery);
                if (filter != null)
                {
                    if (model.fields.Contains(ResourceQueryProjection.CATEGORIES))
                    {
                        var contentFilters = new List <string>();
                        if (filter.lang != null)
                        {
                            var paramName = query.AddAutoIncrParam(filter.lang);
                            var ecLang    = $"{nameof(EntityCategoryContent)}.{nameof(EntityCategoryContent.Lang)}";
                            contentFilters.Add($"{ecLang}=@{paramName}");
                        }
                        if (contentFilters.Any())
                        {
                            var whereClause = "WHERE " + string.Join(" AND ", contentFilters);
                            query.DynamicForm = query.DynamicForm
                                                .Replace(ResourceQueryPlaceholder.CATE_CONTENT_FILTER, whereClause);
                        }
                    }
                }
            }
            return(query);
        }
Esempio n. 12
0
        public IDictionary <string, object> GetResourceDynamic(
            ResourceQueryRow row, ResourceQueryProjection projection,
            ResourceQueryOptions options)
        {
            var obj = new Dictionary <string, object>();

            foreach (var f in projection.GetFieldsArr())
            {
                switch (f)
                {
                case ResourceQueryProjection.INFO:
                {
                    var entity = row.Resource;
                    obj["archived"]    = entity.Archived;
                    obj["area_id"]     = entity.AreaId;
                    obj["building_id"] = entity.BuildingId;
                    obj["code"]        = entity.Code;
                    obj["floor_id"]    = entity.FloorId;
                    obj["id"]          = entity.Id;
                    obj["image_url"]   = entity.ImageUrl;
                    obj["location_id"] = entity.LocationId;
                    obj["logo_url"]    = entity.LogoUrl;
                    obj["owner_id"]    = entity.OwnerId;
                    obj["phone"]       = entity.Phone;
                    obj["type_id"]     = entity.TypeId;
                }
                break;

                case ResourceQueryProjection.CONTENT:
                {
                    var entity = row.Content;
                    if (entity != null)
                    {
                        obj["content_id"]  = entity.Id;
                        obj["lang"]        = entity.Lang;
                        obj["name"]        = entity.Name;
                        obj["description"] = entity.Description;
                    }
                }
                break;

                case ResourceQueryProjection.CONTENT_CONTENT:
                {
                    var entity = row.Content;
                    if (entity != null)
                    {
                        obj["content"] = entity.Content;
                    }
                }
                break;

                case ResourceQueryProjection.LOCATION:
                {
                    var entity = row.Location;
                    obj["location"] = new
                    {
                        id   = entity.Id,
                        code = entity.Code,
                        name = entity.Name,
                    };
                }
                break;

                case ResourceQueryProjection.AREA:
                {
                    var entity = row.Area;
                    obj["area"] = new
                    {
                        id   = entity.Id,
                        code = entity.Code,
                        name = entity.Name,
                    };
                }
                break;

                case ResourceQueryProjection.OWNER:
                {
                    var entity = row.Owner;
                    obj["owner"] = new
                    {
                        id   = entity.Id,
                        code = entity.Code,
                        name = entity.Name,
                    };
                }
                break;

                case ResourceQueryProjection.CATEGORIES:
                {
                    var entities = row.Resource.CategoriesOfResources;
                    obj["categories"] = entities?.Select(o =>
                        {
                            var cate    = o.Category;
                            var content = o.CategoryContent;
                            return(new
                            {
                                id = cate.Id,
                                archived = cate.Archived,
                                content_id = content.Id,
                                name = content.Name,
                                lang = content.Lang
                            });
                        }).ToList();
                }
                break;
                }
            }
            return(obj);
        }
Esempio n. 13
0
        public async Task <QueryResult <ResourceQueryRow> > QueryResource(
            ResourceQueryFilter filter         = null,
            ResourceQuerySort sort             = null,
            ResourceQueryProjection projection = null,
            ResourceQueryPaging paging         = null,
            ResourceQueryOptions options       = null)
        {
            var conn     = context.Database.GetDbConnection();
            var openConn = conn.OpenAsync();
            var query    = ResourceQuery.CreateDynamicSql();

            #region General
            if (filter != null)
            {
                query = query.SqlFilter(filter);
            }
            if (projection != null)
            {
                query = query.SqlJoin(projection, filter);
            }
            DynamicSql countQuery = null; int?totalCount = null; Task <int> countTask = null;
            if (options != null && options.count_total)
            {
                countQuery = query.SqlCount("*");
            }
            if (projection != null)
            {
                query = query.SqlProjectFields(projection);
            }
            #endregion
            await openConn;
            if (options != null && !options.single_only)
            {
                #region List query
                if (sort != null)
                {
                    query = query.SqlSort(sort);
                }
                if (paging != null && (!options.load_all || !ResourceQueryOptions.IsLoadAllAllowed))
                {
                    query = query.SqlSelectPage(paging.page, paging.limit);
                }
                #endregion
                #region Count query
                if (options.count_total)
                {
                    countTask = conn.ExecuteScalarAsync <int>(
                        sql: countQuery.PreparedForm,
                        param: countQuery.DynamicParameters);
                }
                #endregion
            }
            if (projection != null)
            {
                query = query.SqlExtras(projection, filter);
            }
            var multipleResult = await conn.QueryMultipleAsync(
                sql : query.PreparedForm,
                param : query.DynamicParameters);

            using (multipleResult)
            {
                var queryResult = multipleResult.Read(
                    types: query.GetTypesArr(),
                    map: (objs) => ProcessMultiResults(query, objs),
                    splitOn: string.Join(',', query.GetSplitOns()));
                if (projection != null)
                {
                    var extraKeys = projection.GetFieldsArr()
                                    .Where(f => ResourceQueryProjection.Extras.ContainsKey(f));
                    IEnumerable <CateOfResQueryRow> categories = null;
                    foreach (var key in extraKeys)
                    {
                        switch (key)
                        {
                        case ResourceQueryProjection.CATEGORIES:
                            categories = GetCategoriesQueryResult(multipleResult);
                            break;
                        }
                    }
                    ProcessExtras(queryResult, categories);
                }
                if (options != null && options.single_only)
                {
                    var single = queryResult.FirstOrDefault();
                    if (single == null)
                    {
                        return(null);
                    }
                    return(new QueryResult <ResourceQueryRow>
                    {
                        SingleResult = single
                    });
                }
                if (options != null && options.count_total)
                {
                    totalCount = await countTask;
                }
                return(new QueryResult <ResourceQueryRow>
                {
                    Results = queryResult,
                    TotalCount = totalCount
                });
            }
        }