public IEntityCollectionInfo <Product> GetEntityCollectionInfo(int pageNumber, int pageSize)
{
var collectionInfo = new ProductCollectionInfo();
collectionInfo.Entities = _commonDb.Query <Product>($"select * from products order by id offset {pageNumber * pageSize - pageSize} rows fetch next {pageSize} rows only");
_commonDb.GetPageCount(pageSize, "select count(1) from products", collectionInfo);
return(collectionInfo);
}
public ProductCollectionInfo Get(int pageNumber, int pageSize)
{
try
{
var products = _commonDb.Query <ProductWithDescription>($"select * from ({SubQuery}) subQuery order by code offset {pageNumber * pageSize - pageSize} rows fetch next {pageSize} rows only");
var collectionInfo = new ProductCollectionInfo();
_commonDb.GetPageCount(pageSize, "select count(1) from products", collectionInfo);
collectionInfo.Entities = products;
return(collectionInfo);
}
catch (Exception e)
{
throw new ProductOperationException($"Products get error!", e);
}
}
public ProductCollectionInfo GetByFilterProducts(int pageNumber, int pageSize, Guid?type, Guid?unit, IEnumerable <Guid> categories)
{
var queryBuilder = new StringBuilder($"select p.* from ({SubQuery}) p").AppendLine();
var codes = categories as Guid[] ?? categories?.ToArray();
if (codes != null && codes.Any())
{
var joinCodes = codes.Aggregate("", (c, n) => c + $"'{n}',");
var categoryFilterString = $"inner join (select * from productcategories where codeCategory in ({joinCodes.Remove(joinCodes.Length - 1)})) c on p.code = c.codeProduct";
queryBuilder.AppendLine(categoryFilterString);
}
var isWhere = false;
if (type.HasValue)
{
isWhere = true;
queryBuilder.AppendLine("where");
queryBuilder.AppendLine($"codeType = '{type.Value}'");
}
if (unit.HasValue)
{
queryBuilder.AppendLine($"{(isWhere ? "and" : "where")} codeUnit = '{unit.Value}'");
}
queryBuilder.AppendLine("group by p.code, p.description, p.price, p.isAvailable, p.deliveryDate, p.codeType, p.codeUnit");
queryBuilder.AppendLine($"order by code offset {pageNumber * pageSize - pageSize} rows fetch next {pageSize} rows only");
try
{
var query = queryBuilder.ToString();
var products = _commonDb.Query <ProductWithDescription>(query);
var collectionInfo = new ProductCollectionInfo();
_commonDb.GetPageCount(pageSize, $"select count(1) from ({query}) countSet", collectionInfo);
collectionInfo.Entities = products;
return(collectionInfo);
}
catch (Exception e)
{
throw new ProductOperationException($"Products get error!", e);
}
}
