Exemplo n.º 1
0
        ///<inheritdoc/>
        public PagedQueryResultModel ProcessQuery(PagedQueryModel queries, List <ComparableFieldModel> comparisons = null, List <CalculatedColumnModel> externalCalculation = null)
        {
            //this.ValidateSelectQueries(queries.Query);
            //this.ValidateJoinQueries(queries.Query);
            var paginated = InjectPaginations(queries);

            return(this.GetDataset(paginated.ToList(), queries.PagingModel.PageSize, queries.PagingModel.PageNumber));
        }
Exemplo n.º 2
0
 public PagedQueryResultModel ProcessQuery([FromBody] PagedQueryModel querie)
 {
     //if (querie == null)
     //{
     //    var json = System.IO.File.ReadAllText("./test.txt");
     //    querie = JsonConvert.DeserializeObject<PagedQueryModel>(json);
     //}
     return(manager.ProcessQuery(querie));
 }
Exemplo n.º 3
0
        ///<inheritdoc/>
        public void EditQuery(long id, PagedQueryModel model)
        {
            var query = Context.Queries.FirstOrDefault(x => x.Id == id);

            if (query == null)
            {
                throw new BadRequestException("Query doen1t exist.");
            }

            query.QueryText = JsonConvert.SerializeObject(model);
            Context.SaveChanges();
        }
Exemplo n.º 4
0
        public async Task <IPagedQueryResult <TModel> > FindPagedListAsync(PagedQueryModel model)
        {
            var pagedList = await BuildFindQuery()
                            .ToPagedQueryResultAsync(model);

            var result = new PagedQueryResult <TModel>
            {
                Items      = pagedList.Items.MapReadOnlyList(MapToModel),
                TotalCount = pagedList.TotalCount
            };

            await AfterFindAsync(result.Items);

            return(result);
        }
Exemplo n.º 5
0
        public string GetSqlScriptFromQueryModel(PagedQueryModel query)
        {
            var script = string.Empty;

            for (int i = 0; i < query.Queries.Count; i++)
            {
                var selectModels     = GetConvertedQueries(query.Queries[i].TableQueries);
                var sqlSelectCommand = selectModels.CreateBaseSqlSelectCommand(query.Queries[i].SelectDistinct);
                sqlSelectCommand.ExtendSqlQueryWithFilters(query.Queries[i].Filters, selectModels);
                sqlSelectCommand.ChangeSelectedColumnsIfGroupBy();
                if (i == query.Queries.Count - 1)
                {
                    sqlSelectCommand.ExtendSqlQueryWithOrderBy(selectModels);
                }
                script += sqlSelectCommand.ToString();
                if (i < query.Queries.Count - 1 && query.Queries.Count > 1)
                {
                    script += "\n union \n";
                }
            }
            return(script);
        }
Exemplo n.º 6
0
 public string GetSqlScriptFromQuery([FromBody] PagedQueryModel model)
 {
     return(dataManager.GetScriptFromQuery(model));
 }
Exemplo n.º 7
0
 public void EditQuery(long id, [FromBody] PagedQueryModel model)
 {
     this.dataManager.EditQuery(id, model);
 }
Exemplo n.º 8
0
        ///<inheritdoc/>
        public PagedQueryResultModel ProcessQuery(PagedQueryModel query,
                                                  string connectionStringName                       = null,
                                                  List <ComparableFieldModel> comparisons           = null,
                                                  List <CalculatedColumnModel> externalCalculations = null)
        {
            try
            {
                if (query.Queries.All(q => q.TableQueries.All(tq => tq.SelectedColumns.Count == 0 && tq.Functions.Count == 0)))
                {
                    return(new PagedQueryResultModel
                    {
                        PageNumber = 1,
                        Result = new QueryResultModel
                        {
                            ColumnHeaders = new List <HeaderModel>(),
                            Rows = new List <RowModel>()
                        }
                    });
                }
                var connectionString = "Data Source = 193.93.216.233;Initial Catalog = TILT2_DEV;Integrated Security = False;User Id = sae;Password=Devabit1@";
                //var connectionString = connectionStringName == null ? config.ConnectionString : config.GetUserConnectionString(connectionStringName);

                var selectModels = GetConvertedQueries(query.Queries[0].TableQueries);
                var offset       = (query.PagingModel.PageNumber - 1) * query.PagingModel.PageSize + query.PagingModel.Skip;
                var pageSize     = query.PagingModel.PageSize;

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    var sqlSelectCommand = selectModels.CreateBaseSqlSelectCommand(query.Queries[0].SelectDistinct);
                    sqlSelectCommand.ExtendSqlQueryWithFilters(query.Queries[0].Filters, selectModels);
                    sqlSelectCommand.ChangeSelectedColumnsIfGroupBy();
                    var totalCountQueryResult = new SqlCommand(SqlExpression.CountExpression(sqlSelectCommand.ToString()), connection).ExecuteScalar();
                    var totalCount            = (int?)totalCountQueryResult ?? 0;
                    if (query.PagingModel.PageNumber == 0)
                    {
                        return(new PagedQueryResultModel()
                        {
                            Result = new QueryResultModel()
                            {
                                Rows = new List <RowModel>()
                            },
                            TotalCount = (int?)totalCountQueryResult ?? 0,
                            PageCount = (long)Math.Ceiling((double)totalCount / query.PagingModel.PageSize),
                            PageNumber = query.PagingModel.PageNumber,
                            HasNext = Math.Ceiling((double)totalCount / query.PagingModel.PageSize) > query.PagingModel.PageNumber,
                        });
                    }
                    sqlSelectCommand.ExtendSqlQueryWithPagination(selectModels, offset, pageSize);
                    var queryResult = ProcessQueryResult(sqlSelectCommand.ToString(), connection);

                    if (sqlSelectCommand.HasGroupBy)
                    {
                        sqlSelectCommand.ChangeSelectedColumnsIfGroupBy(true);
                        var selectString            = sqlSelectCommand.ToString();
                        var groupDetailsQueryResult = ProcessQueryResult(sqlSelectCommand.ToString(), connection);
                        queryResult.SetInternalRows(groupDetailsQueryResult, sqlSelectCommand.GroupByColumns.Select(x => x.Split('.')[1]).ToList());
                    }
                    connection.Close();
                    var pagedQueryResult = new PagedQueryResultModel()
                    {
                        TotalCount = totalCount,
                        PageCount  = (long)Math.Ceiling((double)totalCount / query.PagingModel.PageSize),
                        PageNumber = query.PagingModel.PageNumber,
                        HasNext    = Math.Ceiling((double)totalCount / query.PagingModel.PageSize) > query.PagingModel.PageNumber,
                        Result     = queryResult
                    };

                    if (queryResult.Rows.Count != 0)
                    {
                        // query.Query.PrepareCalculations();
                        // ProcessCalculatedColumns(pagedQueryResult, query);
                        CalculationProcessor.ProcessCalculatedColumns(pagedQueryResult, query.Queries[0].CalculatedColumns);
                    }

                    //comparisons
                    if (sqlSelectCommand.HasGroupBy && queryResult.Rows.Count != 0 && comparisons != null)
                    {
                        var groupings = sqlSelectCommand.GroupByColumns
                                        .Select(x => x.Split('.')[1])
                                        .ToList();
                        comparisons.Reverse();
                        foreach (var comparison in comparisons)
                        {
                            ChangeFilters(comparison, query.Queries[0]);
                            var temp = this.ProcessQuery(query, query.ConnectionStringName);
                            totalCount = temp.TotalCount > totalCount ? temp.TotalCount : totalCount;
                            var columnIndex = queryResult.ColumnHeaders.FindIndex(x => x.Name == comparison.Column.ColumnAlias);

                            pagedQueryResult.Result.ExtendResultWithComparisons(temp.Result, groupings, columnIndex);

                            pagedQueryResult.Result.ColumnHeaders.Insert(columnIndex + 1, new HeaderModel
                            {
                                Name = comparison.Alias,
                                Type = pagedQueryResult.Result.ColumnHeaders[columnIndex].Type
                            });
                        }
                    }

                    if (externalCalculations != null)
                    {
                        CalculationProcessor.ProcessCalculatedColumns(pagedQueryResult, externalCalculations);
                    }

                    foreach (var row in pagedQueryResult.Result.Rows)
                    {
                        if (!query.Queries[0].ShowDetails)
                        {
                            row.Internal = null;
                        }
                        else if (row.Internal != null)
                        {
                            //                  row.Internal.Rows = row.Internal.Rows.Take(100).ToList();
                        }
                    }

                    return(pagedQueryResult);
                }
            }
            catch (Exception e)
            {
                context.Errors.Add(new DataAccess.Models.ErrorQuery()
                {
                    QueryText = Newtonsoft.Json.JsonConvert.SerializeObject(query),
                    Date      = DateTime.Now,
                    Exception = e.Message
                });
                context.SaveChanges();
                throw;
            }
        }
Exemplo n.º 9
0
        private IEnumerable <PagedQueryModel> InjectPaginations(PagedQueryModel queries)
        {
            var mustTake    = queries.PagingModel.PageSize;
            var firstToTake = (queries.PagingModel.PageNumber - 1) * queries.PagingModel.PageSize;

            foreach (var query in queries.Queries)
            {
                var count = this.dal.GetRecordsCount(query, queries.ConnectionStringName);
                if (firstToTake > count)
                {
                    yield return(new PagedQueryModel()
                    {
                        PagingModel = new PaginationModel()
                        {
                            PageSize = 0,
                            PageNumber = 0
                        },
                        Queries = new List <QueryModel>()
                        {
                            query
                        }
                    });

                    firstToTake -= count;
                }
                else
                {
                    if (mustTake + firstToTake < count)
                    {
                        yield return(new PagedQueryModel()
                        {
                            PagingModel = new PaginationModel()
                            {
                                Skip = firstToTake,
                                PageSize = mustTake,
                                PageNumber = 1
                            },
                            Queries = new List <QueryModel>()
                            {
                                query
                            }
                        });
                    }
                    else
                    {
                        yield return(new PagedQueryModel()
                        {
                            PagingModel = new PaginationModel()
                            {
                                Skip = firstToTake,
                                PageSize = mustTake,
                                PageNumber = 1
                            },
                            Queries = new List <QueryModel>()
                            {
                                query
                            }
                        });

                        mustTake   -= (count - firstToTake);
                        firstToTake = 0;
                    }
                }
            }
        }
Exemplo n.º 10
0
        public string GetScriptFromQuery(PagedQueryModel query)
        {
            var sqlScript = tableDal.GetSqlScriptFromQueryModel(query);

            return(sqlScript);
        }
Exemplo n.º 11
0
 public void EditQuery(long id, PagedQueryModel model)
 {
     this.dal.EditQuery(id, model);
 }
Exemplo n.º 12
0
        public static async Task <PagedResultModel <T> > PagedAsync <T>(this IQueryable <T> source, PagedQueryModel query, CancellationToken cancellationToken)
        {
            var totalItemCount = await source.CountAsync(cancellationToken);

            var page = query.Page ?? 1;
            var size = query.Size ?? 30;

            var pageSize   = Math.Clamp(size, min: 1, max: 200);
            var pageCount  = (totalItemCount - 1) / pageSize + 1;
            var pageNumber = Math.Clamp(page, min: 1, max: pageCount);

            var items = await source.Skip((pageNumber - 1) *pageSize).Take(pageSize).ToListAsync(cancellationToken);

            return(new PagedResultModel <T>(items, pageNumber, pageCount, totalItemCount));
        }
Exemplo n.º 13
0
        public async Task <ActionResult <PagedResultModel <MovieDetailsModel> > > GetAll([FromQuery] PagedQueryModel model, CancellationToken cancellationToken)
        {
            var query = new GetAllMoviesQuery(model.Page, model.Size);

            var result = await Executor.ExecuteAsync(query, cancellationToken);

            return(Ok(result));
        }