Пример #1
0
        public async Task <PagedView <CustomerDto> > GetPagedCustomers
            (string name, string cpf, string email, int page, int pageSize)
        {
            int totalItems = 0;
            PagedView <CustomerDto> result = new PagedView <CustomerDto>();

            var offset = QueryCommon.OffSet(page, pageSize);

            string sql = @"SELECT  FirstName + ' ' + LastName As  Name,
                                   Email,
                                   Phone
                            FROM dbo.CUSTOMER
                            WHERE (Cpf = @Cpf OR ISNULL(@Cpf,'') = '') 
                            AND
                            (Email = @Email OR ISNULL(@Email,'') = '') 
                            AND
                            ((FirstName like @FirstName OR ISNULL(@FirstName,'') = '') 
                            OR
                            (LastName like @LastName OR ISNULL(@LastName,'') = ''))
                            ";

            string sqlPaged      = QueryCommon.PagedQuery(orderBy: "Name", sql, offset, pageSize);
            string sqlTotalCount = QueryCommon.TotalItemsQuery(sql);

            var customers = await _database.GetConnection()
                            .QueryAsync <CustomerDto>(sqlPaged,
                                                      new
            {
                Cpf       = cpf ?? string.Empty,
                Email     = email ?? string.Empty,
                FirstName = $"%{name}%" ?? $"%{string.Empty}%",
                LastName  = $"%{name}%" ?? $"%{string.Empty}%"
            });

            totalItems = await _database.GetConnection()
                         .ExecuteScalarAsync <int>(sqlTotalCount,
                                                   new
            {
                Cpf       = cpf ?? string.Empty,
                Email     = email ?? string.Empty,
                FirstName = name ?? string.Empty,
                LastName  = name ?? string.Empty
            });

            result.Data       = customers.ToList();
            result.Page       = page;
            result.PageSize   = pageSize;
            result.TotalPages = totalItems / pageSize + (totalItems % pageSize == 0 ? 0 : 1);
            result.TotalItems = totalItems;

            return(result);
        }