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); }