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