public SLORMContext GroupBy(params string[] columnNames)
{
if (columnNames == null)
{
throw new ArgumentNullException(nameof(columnNames));
}
foreach (var currentColumnName in columnNames)
{
var column = ColumnsInTable.GetFromName(currentColumnName);
if (column == null)
{
continue;
}
var columnAlreadyInList = ColumnsToGroupBy.GetFromName(column.Name) != null;
if (columnAlreadyInList)
{
continue;
}
ColumnsToGroupBy.Add(column);
}
return(this);
}
public async Task <ICollection <string> > GetColumnUniqueValues(string columnName)
{
// TODO: This method should be in the query builder.
if (string.IsNullOrWhiteSpace(columnName))
{
throw new ArgumentNullException(columnName);
}
var uniqueValues = new List <string>();
if (!ColumnsInTable.Any(c => c.Name == columnName))
{
return(uniqueValues);
}
await Connection.EnsureConnected();
using (var command = Connection.CreateCommand())
{
command.CommandText = $"SELECT DISTINCT({columnName}) FROM {TableName}";
using (var reader = command.ExecuteReader())
while (reader.Read())
{
uniqueValues.Add(reader[0].ToString());
}
}
return(uniqueValues);
}
public bool IsQueryable()
{
if (!ColumnsInTable.Any())
{
return(false);
}
if (!ColumnsToGroupBy.Any() && !ColumnsToCount.Any() && !ColumnsToSum.Any())
{
return(false);
}
return(true);
}
public SLORMContext Filter(ColumnFilterRequest filterRequest)
{
if (filterRequest == null)
{
throw new ArgumentNullException(nameof(filterRequest));
}
var column = ColumnsInTable.GetFromName(filterRequest.ColumnName);
if (column == null)
{
return(this);
}
// Filtering incorrect value formats
if (filterRequest.FilterRigor == FilterRigor.Equals)
{
switch (column.DataType)
{
case ColumnDataType.Number:
double parsedValue;
foreach (var currentValue in filterRequest.Values)
{
if (!double.TryParse(currentValue, out parsedValue))
{
return(this);
}
}
break;
case ColumnDataType.Date:
DateTime parsedDateValue;
foreach (var currentValue in filterRequest.Values)
{
if (!DateTime.TryParse(currentValue, out parsedDateValue))
{
return(this);
}
}
break;
}
}
var columnToFilter = new ColumnFilter(column, filterRequest);
ColumnsToFilter.Add(columnToFilter);
return(this);
}
public SLORMContext Sum(string columnName)
{
var column = ColumnsInTable.GetFromName(columnName);
if (column == null || column.DataType != ColumnDataType.Number)
{
return(this);
}
var columnAlreadyInList = ColumnsToSum.GetFromName(column.Name) != null;
if (columnAlreadyInList)
{
return(this);
}
ColumnsToSum.Add(column);
return(this);
}
public SLORMContext OrderBy(ColumnOrderingRequest orderingRequest)
{
var column = ColumnsInTable.GetFromName(orderingRequest.ColumnName);
if (column == null)
{
return(this);
}
var alreadyGroupedByColumn = ColumnsToGroupBy.GetFromName(column.Name) != null ||
ColumnsToSum.GetFromName(column.Name) != null ||
ColumnsToCount.GetFromName(column.Name) != null;
if (!alreadyGroupedByColumn)
{
GroupBy(column.Name);
}
var columnOrdering = new ColumnOrdering(column, orderingRequest);
ColumnsToOrderBy.Add(columnOrdering);
return(this);
}
