public ActionResult <PagedResult <OrderSummary> > GetOrderSummary([FromQuery] string?customer, [FromQuery] DateTimeOffset?createdOnFrom, [FromQuery] DateTimeOffset?createdOnTo, [FromQuery] DateTimeOffset?paidOnFrom, [FromQuery] DateTimeOffset?paidOnTo, [FromQuery] DateTime?completeByFrom, [FromQuery] DateTime?completeByTo, [FromQuery] List <int> paymentStatus, [FromQuery] List <int> orderStatus, [FromQuery] OrderSummaryOrderBy orderBy = OrderSummaryOrderBy.CreatedOn, [FromQuery] OrderByDirection direction = OrderByDirection.Desc, int?page = 1, int?pageSize = 10) { var data = service.GetOrderSummary(UserData.CorpClientId.Value, customer, createdOnFrom, createdOnTo, paidOnFrom, paidOnTo, completeByFrom, completeByTo, paymentStatus, orderStatus, orderBy, direction, page, pageSize); if (data.Data.Any()) { return(Ok(data)); } return(NoContent()); }
public PagedResult <OrderSummary> GetOrderSummary(int corpClientId, string customer, DateTimeOffset?createdOnFrom, DateTimeOffset?createdOnTo, DateTimeOffset?paidOnFrom, DateTimeOffset?paidOnTo, DateTime?completeByFrom, DateTime?completeByTo, List <int> paymentStatus, List <int> orderStatus, OrderSummaryOrderBy orderBy, OrderByDirection direction, int?page, int?pageSize) { return(orderApp.GetOrderSummary(corpClientId, customer, createdOnFrom, createdOnTo, paidOnFrom, paidOnTo, completeByFrom, completeByTo, paymentStatus, orderStatus, orderBy, direction, page, pageSize)); }
public PagedResult <OrderSummary> GetOrderSummary(int corpClientId, string customer, DateTimeOffset?createdOnFrom, DateTimeOffset?createdOnTo, DateTimeOffset?paidOnFrom, DateTimeOffset?paidOnTo, DateTime?completeByFrom, DateTime?completeByTo, List <int> paymentStatus, List <int> orderStatus, OrderSummaryOrderBy orderBy, OrderByDirection direction, int?page, int?pageSize) { var sqlParams = new List <SqlParameter> { new SqlParameter("@corpClientId", corpClientId) }; var query = new StringBuilder(@"Select * FROM (SELECT C.CorpClientId, O.OrderId, O.OrderNumber, CASE C.CustomerTypeId WHEN 1 THEN RTRIM(c.Name + ' ' + ISNULL(c.LastName,'')) ELSE c.DBAName END AS Name, CASE C.CustomerTypeId WHEN 1 THEN c.CPF ELSE c.CNPJ END AS SocialIdentifier, P.PaymentStatusId, OS.OrderStatusId, C.CustomerTypeId, C.CustomerId, P.Description AS PaymentStatus, OS.Description AS OrderStatus, O.CompleteBy CompleteBy, O.CreatedOn CreatedOn, O.PaidOn PaidOn, ISNULL((SELECT SUM(OI.PriceAfterDiscount) FROM OrderItem OI WHERE OI.OrderId = O.OrderId AND OI.OrderItemStatusId NOT IN (3)),0) AS FinalPrice FROM Customer C JOIN [Order] O ON C.CustomerId = O.CustomerId JOIN PaymentStatus P ON O.PaymentStatusId = P.PaymentStatusId JOIN OrderStatus OS ON O.OrderStatusId = OS.OrderStatusId ) X WHERE CorpClientId = @corpClientId "); if (!string.IsNullOrEmpty(customer)) { query.Append(" AND Name LIKE '%'+ @customer +'%'"); sqlParams.Add(new SqlParameter("@customer", customer) { SqlDbType = SqlDbType.VarChar, Size = 500 }); } if (createdOnFrom.HasValue) { query.Append(" AND CAST(CreatedOn AS DATE) >= @createdOnFrom"); sqlParams.Add(new SqlParameter("@createdOnFrom", createdOnFrom.Value.ToUniversalTime().Date) { SqlDbType = SqlDbType.Date }); } if (createdOnTo.HasValue) { query.Append(" AND CAST(CreatedOn AS DATE) <= @createdOnTo"); sqlParams.Add(new SqlParameter("@createdOnTo", createdOnTo.Value.ToUniversalTime().Date) { SqlDbType = SqlDbType.Date }); } if (paidOnFrom.HasValue) { query.Append(" AND CAST(PaidOn AS DATE) >= @paidOnFrom"); sqlParams.Add(new SqlParameter("@paidOnFrom", paidOnFrom.Value.ToUniversalTime().Date) { SqlDbType = SqlDbType.Date }); } if (paidOnTo.HasValue) { query.Append(" AND CAST(PaidOn AS DATE) <= @paidOnTo"); sqlParams.Add(new SqlParameter("@paidOnTo", paidOnTo.Value.ToUniversalTime().Date) { SqlDbType = SqlDbType.Date }); } if (completeByFrom.HasValue) { query.Append(" AND CompleteBy >= @completeByFrom"); sqlParams.Add(new SqlParameter("@completeByFrom", completeByFrom) { SqlDbType = SqlDbType.Date }); } if (completeByTo.HasValue) { query.Append(" AND CompleteBy <= @completeByTo"); sqlParams.Add(new SqlParameter("@completeByTo", completeByTo) { SqlDbType = SqlDbType.Date }); } if (paymentStatus != null && paymentStatus.Any()) { query.Append($" AND paymentStatusId IN ({string.Join(',', paymentStatus.ToArray())})"); } if (orderStatus != null && orderStatus.Any()) { query.Append($" AND orderStatusId IN ({string.Join(',', orderStatus.ToArray())})"); } var count = context.OrderSummaries.FromSqlRaw(query.ToString(), sqlParams.ToArray()).Count(); if (orderBy == OrderSummaryOrderBy.OrderStatus) { query.Append($" ORDER BY {orderBy} {direction}, CompleteBy"); } else if (orderBy == OrderSummaryOrderBy.CompleteBy) { query.Append($" ORDER BY {orderBy} {direction}, OrderStatus"); } else { query.Append($" ORDER BY {orderBy} {direction},CompleteBy, OrderStatus"); } if (page.HasValue && pageSize.HasValue) { query.Append($" OFFSET {(page > 0 ? page - 1 : 0) * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY"); } var data = context.OrderSummaries.FromSqlRaw(query.ToString(), sqlParams.ToArray()).ToList(); var result = mapper.Map <IEnumerable <OrderSummary> >(data); return(new PagedResult <OrderSummary> { CurrentPage = page ?? 1, Data = result, RecordCount = count, RecordsPerpage = pageSize ?? count }); }