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