///<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));
}
