public IEnumerable <PrintingEdition> FilterPrintEdidtionsByPriceAnfType(PrintingEditionType printingEditionType, int minPrice, int maxPrice)
{
var type = (int)printingEditionType;
using (IDbConnection db = Connection)
{
return(db.Query <PrintingEdition>($"SELECT * FROM PrintingEditions WHERE Type = {type} AND Price BETWEEN {minPrice} AND {maxPrice}"));
}
}
public IEnumerable <PrintingEdition> FilterByType(PrintingEditionType printingEditionType)
{
var type = (int)printingEditionType;
using (IDbConnection db = Connection)
{
return(db.Query <PrintingEdition>($"SELECT * FROM PrintingEditions WHERE Type = {type}"));
}
}
private string MakeFilterString(string column, string filter, PrintingEditionType type, PrintingEditionStatus status)
{
string columnName = GetColumn(column);
string result = null;
if (filter != "none")
{
result = $"WHERE {columnName} LIKE '%{filter}%'";
}
if (type != PrintingEditionType.None)
{
if (!String.IsNullOrEmpty(result))
{
result = $"{result} AND PrintingEditions.Type = '{(int)type}' ";
}
if (String.IsNullOrEmpty(result))
{
result = $"WHERE PrintingEditions.Type = '{(int)type}' ";
}
}
if (status != PrintingEditionStatus.None && status != PrintingEditionStatus.NotAvailable)
{
if (!String.IsNullOrEmpty(result))
{
result = $"{result} AND PrintingEditions.Status = '{(int)status}' ";
}
if (String.IsNullOrEmpty(result))
{
result = $"WHERE PrintingEditions.Status = '{(int)status}' ";
}
}
if (status == PrintingEditionStatus.NotAvailable)
{
if (!String.IsNullOrEmpty(result))
{
result = $"{result} AND PrintingEditions.Status <> '{(int)status}' ";
}
if (String.IsNullOrEmpty(result))
{
result = $"WHERE PrintingEditions.Status <> '{(int)status}' ";
}
}
return(result);
}
public IEnumerable <PrintingEdition> FilterPrintEdidtionsByPriceAnfType(PrintingEditionType printingEditionType, int minPrice, int maxPrice)
{
return(Context.PrintingEditions
.Where(print => print.Type == printingEditionType && (print.Price >= minPrice && print.Price <= maxPrice)));
}
public IEnumerable <PrintingEdition> FilterByType(PrintingEditionType typeName)
{
return(Context.PrintingEditions.Where(print => print.Type == typeName));
}
private Func <AuthorInPrintingEditions, bool> MakefilterExpression(string sortedElement, string filter, PrintingEditionType type, PrintingEditionStatus status)
{
Expression body = null;
ParameterExpression parameter = Expression.Parameter(typeof(AuthorInPrintingEditions));
if (filter != "none")
{
Expression property = GetProperty(sortedElement, parameter);
MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
var someValue = Expression.Constant(filter, typeof(string));
body = Expression.Call(property, method, someValue);
}
if (type != PrintingEditionType.None)
{
Expression property = GetProperty(_printTypeColumn, parameter);
var someValue = Expression.Constant(type, typeof(PrintingEditionType));
BinaryExpression containsMethodExpForType = Expression.Equal(property, someValue);
if (body != null)
{
body = Expression.And(body, containsMethodExpForType);
}
if (body == null)
{
body = containsMethodExpForType;
}
}
if (status != PrintingEditionStatus.None && status != PrintingEditionStatus.NotAvailable)
{
Expression property = GetProperty(_printStatusColumn, parameter);
var someValue = Expression.Constant(status, typeof(PrintingEditionStatus));
BinaryExpression containsMethodExpForStatus = Expression.Equal(property, someValue);
if (body != null)
{
body = Expression.And(body, containsMethodExpForStatus);
}
if (body == null)
{
body = containsMethodExpForStatus;
}
}
if (status == PrintingEditionStatus.NotAvailable)
{
Expression property = GetProperty(_printStatusColumn, parameter);
var someValue = Expression.Constant(status, typeof(PrintingEditionStatus));
BinaryExpression containsMethodExpForStatus = Expression.NotEqual(property, someValue);
if (body != null)
{
body = Expression.And(body, containsMethodExpForStatus);
}
if (body == null)
{
body = containsMethodExpForStatus;
}
}
if (body == null)
{
return(null);
}
Func <AuthorInPrintingEditions, bool> filterExpression = Expression.Lambda <Func <AuthorInPrintingEditions, bool> >(body, parameter).Compile();
return(filterExpression);
}
public IEnumerable <AuthorInPrintingEditions> SortAndFilterData(SortOrder sortOrder, string sortedElement, int firstElement, int lastElement,
string filter, PrintingEditionType type, PrintingEditionStatus status)
{
IQueryable <AuthorInPrintingEditions> result = Context.AuthorInPrintingEditions
.Include(authInPe => authInPe.Author)
.Include(authInPe => authInPe.PrintingEdition)
.ThenInclude(printEd => printEd.Currency);
ParameterExpression parameter = Expression.Parameter(typeof(AuthorInPrintingEditions));
Expression property = GetProperty(sortedElement, parameter);
Func <AuthorInPrintingEditions, dynamic> sortBy = null;
IEnumerable <AuthorInPrintingEditions> authorInPrintingEditions = new List <AuthorInPrintingEditions>();
if (property != null)
{
sortBy = Expression.Lambda <Func <AuthorInPrintingEditions, dynamic> >(Expression.TypeAs(property, typeof(object)), parameter).Compile();
}
var filterExpression = MakefilterExpression(sortedElement, filter, type, status);
if (filterExpression != null && sortOrder == SortOrder.Ascending)
{
authorInPrintingEditions = result.AsEnumerable().Where(filterExpression).OrderBy(sortBy).AsEnumerable()
.Skip(firstElement).Take(lastElement - firstElement);
}
if (filterExpression != null && sortOrder == SortOrder.Descending)
{
authorInPrintingEditions = result.AsEnumerable().Where(filterExpression).OrderByDescending(sortBy)
.Skip(firstElement).Take(lastElement - firstElement);
}
if (filterExpression == null && property != null)
{
authorInPrintingEditions = (sortOrder == SortOrder.Ascending ? result.OrderBy(sortBy) : result.OrderByDescending(sortBy)).AsQueryable()
.Skip(firstElement).Take(lastElement - firstElement);
result.AsEnumerable();
}
if (property == null)
{
authorInPrintingEditions = result.AsEnumerable().Skip(firstElement).Take(lastElement - firstElement);
}
return(authorInPrintingEditions);
}
public IEnumerable <AuthorsInPrintingEditionsViewModel> GetAuthorsInPrintingEditionsPage(SortOrder order, int firstElement, int lastElement,
string currentCurrencyName, string filter, string column, PrintingEditionStatus status, PrintingEditionType type)
{
IEnumerable <AuthorsInPrintingEditionsViewModel> modelsList =
_printStoreService.SortAndFilterData(order, currentCurrencyName, column, firstElement, lastElement, filter, type, status);
return(modelsList);
}
public IEnumerable <AuthorsInPrintingEditionsViewModel> SortAndFilterData(SortOrder sortOrder, string currentCurrencyName,
string column, int firstElement, int lastElement, string filter, PrintingEditionType type, PrintingEditionStatus status)
{
IEnumerable <AuthorInPrintingEditions> authorsInPriningEditions = _authorInPrintingEditionRepository
.SortAndFilterData(sortOrder, column, firstElement, lastElement, filter, type, status);
IEnumerable <AuthorsInPrintingEditionsViewModel> modelsList = AuthorsInPrintForming(authorsInPriningEditions, currentCurrencyName);
return(modelsList);
}
public IEnumerable <AuthorInPrintingEditions> SortAndFilterData(SortOrder sortOrder, string column, int firstElement, int lastElement,
string filter, PrintingEditionType type, PrintingEditionStatus status)
{
var rows = lastElement - firstElement;
string sort = "";
if (sortOrder == SortOrder.Ascending)
{
sort = "ASC";
}
if (sortOrder == SortOrder.Descending)
{
sort = "DESC";
}
string columnName = GetColumn(column);
string filterString = MakeFilterString(column, filter, type, status);
var query = $"SELECT * FROM AuthorInPrintingEditions " +
$"JOIN PrintingEditions ON PrintingEditions.Id = AuthorInPrintingEditions.PrintingEditionId " +
$"JOIN Authors ON Authors.Id = AuthorInPrintingEditions.AuthorId " +
$"JOIN Currencies on Currencies.Id = PrintingEditions.CurrencyId " +
$"{filterString}" +
$"ORDER BY {columnName} {sort} " +
$"OFFSET {firstElement} ROWS FETCH NEXT {rows} ROWS ONLY";
return(GetResultList(query));
}
