public void EditQuery(Query query)
{
var oldQuery = GetQueryById(query.QueryId);
if (oldQuery == null)
{
return;
}
oldQuery.Title = query.Title;
oldQuery.Description = query.Description;
oldQuery.Script = query.Script;
_context.Entry(oldQuery).State = EntityState.Modified;
_context.SaveChanges();
}
public static void InsertFakeData(QueryDbContext context, int count)
{
for (int i = 0; i < count; i++)
{
context.Todos.Add(
new Infra.Data.SqlServer.Queries.Todo.Entities.Todo()
{
Text = "test" + i, Title = "test" + i, ValidTo = DateTime.Now.AddDays(i + 1)
});
}
context.SaveChanges();
}
///<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;
}
}
