private string BuildOrderBy(CheckPagingRequest r, string query)
{
SearchCriteria s = r.SearchCriteria;
if (s.OrderBy == OrderBy.CreatedDate)
{
query = BuildOrderClause("CreatedDate", s.Order, query);
}
else if (s.OrderBy == OrderBy.IssuedDate)
{
query = BuildOrderClause("DateIssued", s.Order, query);
}
else if (s.OrderBy == OrderBy.Amount)
{
query = BuildOrderClause("Amount", s.Order, query);
}
else if (s.OrderBy == OrderBy.CheckNumber)
{
query = BuildOrderClause("CheckNumber", s.Order, query);
}
else if (s.OrderBy == OrderBy.IssuedTo)
{
query = BuildOrderClause("IssuedTo", s.Order, query);
}
return(query);
}
public CheckPagingResult SearchWithPagination(CheckPagingRequest r)
{
CheckPagingResult result = new CheckPagingResult();
result = _checkRepository.GetCheckWithPaging(r);
return(result);
}
private string BuildCondition(CheckPagingRequest r, string query)
{
SearchCriteria s = r.SearchCriteria;
bool shouldIncludeWhere = true;
if (!string.IsNullOrEmpty(s.CheckNumber))
{
query = BuildWhereClause(query, "CheckNumber", "Like", "'%" + s.CheckNumber + "%'", shouldIncludeWhere);
shouldIncludeWhere = false;
}
if (s.AmountFrom != 0 && s.AmountTo != 0)
{
query = BuildWhereClause(query, "Amount", "Between", s.AmountFrom + " And " + s.AmountTo,
shouldIncludeWhere);
shouldIncludeWhere = false;
}
if (s.IssuedDateFrom.HasValue && s.IssuedDateTo.HasValue)
{
DateTime addOneDay = s.IssuedDateTo.Value.AddDays(1);
query = BuildWhereClause(query, "DateIssued", "Between",
"'" + s.IssuedDateFrom.Value.ToString("yyyy-MM-dd") + "' And '" + addOneDay.ToString("yyyy-MM-dd") +
"'",
shouldIncludeWhere);
shouldIncludeWhere = false;
}
if (s.SelectedBank != null)
{
query = BuildWhereClause(query, "Bank_Id", "=", s.SelectedBank.Id.ToString(),
shouldIncludeWhere);
shouldIncludeWhere = false;
}
if (!string.IsNullOrEmpty(s.IssuedTo))
{
query = BuildWhereClause(query, "IssuedTo", "Like", "'%" + s.IssuedTo + "%'",
shouldIncludeWhere);
shouldIncludeWhere = false;
}
if (s.CreatedDateFrom.HasValue && s.CreatedDateTo.HasValue)
{
DateTime addOneDay = s.CreatedDateTo.Value.AddDays(1);
query = BuildWhereClause(query, "CreatedDate", "Between",
"'" + s.CreatedDateFrom.Value.ToString("yyyy-MM-dd") + "' And '" + addOneDay.ToString("yyyy-MM-dd") +
"'",
shouldIncludeWhere);
shouldIncludeWhere = false;
}
if (s.ShouldJoinTable)
{
query = BuildWhereClause(query, "Departments.Id", "=", Convert.ToString(s.SelectedDepartment.Id),
shouldIncludeWhere);
}
return(query);
}
private string AddRelation(CheckPagingRequest r, string query)
{
SearchCriteria s = r.SearchCriteria;
if (s.ShouldJoinTable)
{
string partialQuery = string.Empty;
partialQuery = query + " Inner join Banks on Checks.Bank_Id = Banks.Id";
query = partialQuery + " Inner join Departments on Banks.Department_Id = Departments.Id";
}
return(query);
}
private CheckPagingResult Search(int currentPage, int pageSize)
{
CheckService service = new CheckService(_checkRepository);
CheckPagingRequest r = new CheckPagingRequest();
SearchCriteria s = BuilSearchQuery();
r.PageSize = pageSize;
r.CurrentPage = currentPage;
r.SearchCriteria = s;
CheckPagingResult result = service.SearchWithPagination(r);
return(result);
}
//The library Devart DotConnect for SQLite has minor bugs when arranging data with skip and take.
//Basically, what happens is 'Order By' is not being generated in SQL using Skip and take.
//My work around is to create SQL by hand.
public CheckPagingResult GetCheckWithPaging(CheckPagingRequest r)
{
CheckPagingResult result = new CheckPagingResult();
string query = "Select * From Checks";
string queryForCount = "Select Count(Checks.Id) From Checks";
query = AddRelation(r, query);
queryForCount = AddRelation(r, queryForCount);
queryForCount = BuildCondition(r, queryForCount);
result.TotalItems = _context.Database.SqlQuery <int>(queryForCount).First();
query = BuildCondition(r, query);
query = BuildOrderBy(r, query);
query = BuildPagination(r, query);
result.Results = _context.Checks.SqlQuery(query).ToList();
result.PageCount = result.TotalItems / r.PageSize;
return(result);
}
private string BuildPagination(CheckPagingRequest r, string query)
{
return(query + " LIMIT " + r.PageSize + " OFFSET " + r.CurrentPage * r.PageSize);
}