private async Task <GridOutcome> GetDataQueryAsync(string GridConfigId, GridDataRequest gridDataRequest)
{
StringBuilder queryBuilder = new StringBuilder();
Task <SqlSourceConfiguration> sourceConfigTask = GetSourceConfig(GridConfigId);
Task <GridConfigurationEO> gridConfigurationTask = GetGridConfiguration(GridConfigId);
await Task.WhenAll(sourceConfigTask, gridConfigurationTask);
SqlSourceConfiguration sourceConfig = sourceConfigTask.Result;
GridConfigurationEO gridConfiguration = gridConfigurationTask.Result;
gridConfiguration.Controls = DynamicFormRepository.GetFormControls(gridConfiguration.SearchConfigId).AsList();
Query query = null;
Query countQuery = null;
if (sourceConfig == null)
{
query = db.Query(gridConfiguration.TableName);
}
else
{
query = db.Query().FromRaw(sourceConfig.SqlQuery);
if (sourceConfig.Condition.IsNotNullOrEmpty())
{
query = query.Where(sourceConfig.Condition);
}
}
if (gridConfiguration.Controls.IsNotNullOrEmpty() && gridDataRequest.Parameters.IsNotNullOrEmpty())
{
IEnumerable <FormFieldConfiguration> controls = gridConfiguration.Controls.Where(control => gridDataRequest.Parameters.ContainsKey(control.Name));
foreach (FormFieldConfiguration control in controls)
{
HandleSearchOperators(gridDataRequest, control, query);
}
}
countQuery = db.FromQuery(query).AsCount();
if (gridDataRequest.OrderBy.IsNotNullOrEmpty())
{
query = query.OrderBy(gridDataRequest.OrderBy);
}
if (gridDataRequest.PageNo > 0 && gridDataRequest.PageSize > 0)
{
query = query.Skip((gridDataRequest.PageNo - 1) * gridDataRequest.PageSize).Take(gridDataRequest.PageSize);
}
Task <IEnumerable <dynamic> > resultSet = query.GetAsync <dynamic>();
int cnt = db.ExecuteScalar <int>(countQuery);
Task.WaitAll(resultSet);
Task <GridOutcome> gridOutcome = new Task <GridOutcome>(() => {
return(new GridOutcome
{
ResultSet = resultSet.Result.ToList(),
PageNo = gridDataRequest.PageNo,
TotalRecords = cnt
});
});
gridOutcome.RunSynchronously();
return(await gridOutcome);
}
private string GetDataQuery(string GridConfigId, GridDataRequest gridDataRequest)
{
StringBuilder queryBuilder = new StringBuilder();
string sqlSource = GetQuery("SqlSourceConfiguration");
string sql = GetQuery("GridConfiguration");
SqlSourceConfiguration sourceConfig = Connection.Query <SqlSourceConfiguration>(sqlSource, new { GridConfigId }).FirstOrDefault();
GridConfigurationEO gridConfiguration = Connection.Query <GridConfigurationEO>(sql, new { GridConfigId }).FirstOrDefault();
gridConfiguration.Controls = DynamicFormRepository.GetFormControls(gridConfiguration.SearchConfigId).AsList();
if (sourceConfig == null)
{
queryBuilder.Append("Select ");
queryBuilder.Append("* ");
queryBuilder.Append(" from ");
queryBuilder.Append(gridConfiguration.TableName);
}
else
{
queryBuilder.Append(sourceConfig.SqlQuery);
if (sourceConfig.Condition.IsNotNullOrEmpty())
{
queryBuilder.Append(sourceConfig.Condition);
}
}
if (gridConfiguration.Controls.IsNotNullOrEmpty() && gridDataRequest.Parameters.IsNotNullOrEmpty())
{
if (!queryBuilder.ToString().Contains(" where "))
{
queryBuilder.Append(" where ");
}
gridConfiguration.Controls.ForEach
(control =>
{
if (gridDataRequest.Parameters.ContainsKey(control.Name))
{
queryBuilder.Append(control.Name);
queryBuilder.Append(" ");
queryBuilder.Append(control.SearchOperator);
queryBuilder.Append(" @");
queryBuilder.Append(control.Name);
queryBuilder.Append(" And ");
}
}
);
queryBuilder = queryBuilder.Remove(queryBuilder.Length - 4, 4);
}
if (gridDataRequest.OrderBy.IsNotNullOrEmpty())
{
PaginationSettings paginationSettings = new PaginationSettings
{
OrderBy = gridDataRequest.OrderBy,
PageNo = gridDataRequest.PageNo,
PageSize = gridDataRequest.PageSize
};
string query = queryBuilder.ToString();
queryBuilder.Clear();
queryBuilder.Append(Paginate(query, paginationSettings));
queryBuilder.Append(Environment.NewLine);
queryBuilder.Append(GetCountQuery(queryBuilder.ToString()));
}
return(queryBuilder.ToString());
}
