/// <summary>
/// Processes the calculations for model.
/// </summary>
/// <param name="results">The results model.</param>
/// <param name="calculations">List of calculations to be processed.</param>
public static void ProcessCalculatedColumns(PagedQueryResultModel results, List <CalculatedColumnModel> calculations)
{
if (calculations.Count() == 0)
{
return;
}
var groupWideCalculations = calculations.Where(x => x.Calculation.IsGroupWide(results.Result.ColumnHeaders)).ToList();
var internalCalculations = calculations.Except(groupWideCalculations).ToList();
foreach (var calculation in internalCalculations)
{
if (results.Result.Rows[0].Internal == null)
{
groupWideCalculations.Add(calculation);
continue;
}
var type = "calculated";
foreach (var row in results.Result.Rows)
{
foreach (var internalRow in row.Internal.Rows)
{
var result = CalculationParser.ProcessCalculation(calculation.Calculation, row.Internal.ColumnHeaders,
internalRow);
type = result.GetResultType();
internalRow.Values.Add(result.ToString());
}
}
results.Result.Rows[0].Internal.ColumnHeaders.Add(new HeaderModel {
Name = calculation.Alias, Type = type
});
}
foreach (var calculation in groupWideCalculations)
{
var type = "calculated";
foreach (var row in results.Result.Rows)
{
var result = CalculationParser.ProcessCalculation(calculation.Calculation, results.Result.ColumnHeaders, row);
type = result.GetResultType();
row.Values.Add(result.ToString());
}
results.Result.ColumnHeaders.Add(new HeaderModel {
Name = calculation.Alias, Type = type
});
}
}
private async Task <byte[]> ConvertResultToByteArray(PagedQueryResultModel data)
{
byte[] bytesInStream;
using (var memoryStream = new MemoryStream())
{
using (TextWriter writer = new StreamWriter(memoryStream))
{
await writer.WriteLineAsync(string.Join(";",
data.Result.ColumnHeaders.Select(c => c.Name)));
foreach (var resultRow in data.Result.Rows)
{
await writer.WriteLineAsync(string.Join(";", resultRow.Values));
}
await writer.FlushAsync();
bytesInStream = memoryStream.ToArray();
}
}
return(bytesInStream);
}
///<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;
}
}
