public virtual PagedResult <IssueItem> GetApprovedIssueItems(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.GetApprovedIssueItemsSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Issue.IssueDate DESC");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.GetApprovedIssueItemsSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var issueItems = connection.Query <IssueItem, Issue, Item, UnitOfMeasure, StoreLocator, IssueItem>(search.RawSql,
(issueItem, issue, item, unitofmeasure, storeLocator) => { issueItem.Issue = issue; issueItem.Item = item; issueItem.Item.UnitOfMeasure = unitofmeasure; issueItem.StoreLocator = storeLocator; return(issueItem); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <IssueItem>(issueItems, totalCount));
}
}