///<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)); }
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)); }
///<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(); }
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); }
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); }
public string GetSqlScriptFromQuery([FromBody] PagedQueryModel model) { return(dataManager.GetScriptFromQuery(model)); }
public void EditQuery(long id, [FromBody] PagedQueryModel model) { this.dataManager.EditQuery(id, model); }
///<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; } }
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; } } } }
public string GetScriptFromQuery(PagedQueryModel query) { var sqlScript = tableDal.GetSqlScriptFromQueryModel(query); return(sqlScript); }
public void EditQuery(long id, PagedQueryModel model) { this.dal.EditQuery(id, model); }
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)); }
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)); }