public int GetRecordsCount(QueryModel query, string connectionStringName = null)
{
var selectModels = GetConvertedQueries(query.TableQueries);
if (selectModels.All(sm => sm.SelectedColumns.Count == 0))
{
return(0);
}
var connectionString = connectionStringName == null ? config.ConnectionString : config.GetUserConnectionString(connectionStringName);
// var connectionString = "Data Source = 193.93.216.233;Initial Catalog = TILT2_DEV;Integrated Security = False;User Id = sae;Password=Devabit1@";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var sqlSelectCommand = selectModels.CreateBaseSqlSelectCommand(query.SelectDistinct);
sqlSelectCommand.ExtendSqlQueryWithFilters(query.Filters, selectModels);
sqlSelectCommand.ChangeSelectedColumnsIfGroupBy();
var totalCountQueryResult =
new SqlCommand(SqlExpression.CountExpression(sqlSelectCommand.ToString()), connection)
.ExecuteScalar();
return((int?)totalCountQueryResult ?? 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;
}
}
