Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        public CheckPagingResult SearchWithPagination(CheckPagingRequest r)
        {
            CheckPagingResult result = new CheckPagingResult();

            result = _checkRepository.GetCheckWithPaging(r);
            return(result);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        //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);
        }
Ejemplo n.º 7
0
 private string BuildPagination(CheckPagingRequest r, string query)
 {
     return(query + " LIMIT " + r.PageSize + " OFFSET " + r.CurrentPage * r.PageSize);
 }