public ActionResult <PagedResult <OrderItemReport> > GetOrderItemReport([FromQuery] int?orderNumber, [FromQuery] List <long> productId, [FromQuery] List <OrderStatusEnum> orderStatus, [FromQuery] List <OrderItemStatusEnum> itemStatus, [FromQuery] DateTime?completeByFrom, [FromQuery] DateTime?completeByTo, [FromQuery] string?customer, [FromQuery] long?customerId, [FromQuery] int massUnitId = 1, [FromQuery] int volumeUnitId = 5, [FromQuery] OrderItemReportOrderBy orderBy = OrderItemReportOrderBy.OrderItemStatus, [FromQuery] OrderByDirection direction = OrderByDirection.Asc, int?page = 1, int?pageSize = 10)
{
var data = service.GetOrderItemReport(UserData.CorpClientId.Value, orderNumber, productId, massUnitId, volumeUnitId, orderStatus, itemStatus, completeByFrom, completeByTo, customer, customerId, orderBy, direction, page, pageSize);
if (data.Data.Any())
{
return(Ok(data));
}
return(NoContent());
}
public PagedResult <OrderItemReport> GetOrderItemReport(int corpClientId, int?orderNumber, List <long> productIds, int massUnitId, int volumeUnitId, List <OrderStatusEnum> orderStatus, List <OrderItemStatusEnum> itemStatus, DateTime?completeByFrom, DateTime?completeByTo, string customer, long?customerId, OrderItemReportOrderBy orderBy, OrderByDirection direction, int?page, int?pageSize)
{
return(orderApp.GetOrderItemReport(corpClientId, orderNumber, productIds, massUnitId, volumeUnitId, orderStatus, itemStatus, completeByFrom, completeByTo, customer, customerId, orderBy, direction, page, pageSize));
}
public PagedResult <OrderItemReport> GetOrderItemReport(int corpClientId, int?orderNumber, List <long> productIds, int massUnitId, int volumeUnitId, List <OrderStatusEnum> orderStatus, List <OrderItemStatusEnum> itemStatus, DateTime?completeByFrom, DateTime?completeByTo,
string customer, long?customerId, OrderItemReportOrderBy orderBy, OrderByDirection direction,
int?page, int?pageSize)
{
var sqlParams = new List <SqlParameter>
{
new SqlParameter("@corpClientId", corpClientId),
new SqlParameter("@mid", massUnitId),
new SqlParameter("@vid", volumeUnitId)
};
var query = new StringBuilder(@"SELECT * FROM(SELECT
O.OrderId,
I.OrderItemId,
O.OrderNumber,
C.customerTypeId,
OS.OrderStatusId AS OrderStatusId,
OS.Description AS OrderStatus,
CASE C.CustomerTypeId WHEN 1 THEN RTRIM(c.Name + ' ' + ISNULL(c.LastName,'')) ELSE c.DBAName END AS Customer,
CASE C.CustomerTypeId WHEN 1 THEN c.CPF ELSE c.CNPJ END AS SocialIdentifier,
I.ItemNumber,
P.Name as Product,
OIS.Description as OrderItemStatus,
OIS.OrderItemStatusId as OrderItemStatusId,
I.PriceAfterDiscount as FinalPrice,
I.LastStatusDate,
O.CompleteBy,
P.ProductId,
C.CustomerId,
CASE
WHEN M.MeasureUnitTypeId = 1 THEN
CAST(dbo.ConvertMeasure(M.MeasureUnitId,I.Quantity,@mid) as decimal(18,2))
ELSE
CAST(dbo.ConvertMeasure(M.MeasureUnitId,I.Quantity,@vid) as decimal(18,2))
END as Quantity,
(SELECT M2.ShortName FROM MeasureUnit M2 WHERE M2.MeasureUnitId = IIF(M.MeasureUnitTypeId=1,@mid,@vid)) as MeasureUnit
FROM [Order] O
JOIN OrderStatus OS ON O.OrderStatusId = OS.OrderStatusId
JOIN PaymentStatus PS ON O.PaymentStatusId = PS.PaymentStatusId
JOIN OrderItem I ON O.OrderId = I.OrderId
JOIN Customer C ON C.CustomerId = O.CustomerId
JOIN OrderItemStatus OIS ON I.OrderItemStatusId = OIS.OrderItemStatusId
JOIN MeasureUnit M ON I.MeasureUnitId = M.MeasureUnitId
JOIN Product P ON I.ProductId = P.ProductId
WHERE P.CorpClientId = @corpClientId AND C.CorpClientId = @corpClientId) X WHERE 1 = 1 ");
if (productIds != null && productIds.Any())
{
query.Append($" AND ProductId IN ({string.Join(',', productIds.Select(i => i).ToArray())})");
}
if (customerId.HasValue)
{
query.Append($" AND CustomerId = @customerId");
sqlParams.Add(new SqlParameter("@customerId", customerId)
{
SqlDbType = SqlDbType.BigInt
});
}
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 (!string.IsNullOrEmpty(customer))
{
query.Append(" AND Customer LIKE '%'+ @customer +'%'");
sqlParams.Add(new SqlParameter("@customer", customer)
{
SqlDbType = SqlDbType.VarChar, Size = 500
});
}
if (itemStatus != null && itemStatus.Any())
{
query.Append($" AND OrderItemStatusId IN ({string.Join(',', itemStatus.Select(i => (int)i).ToArray())})");
}
if (orderNumber.HasValue)
{
query.Append($" AND OrderNumber = @OrderNumber");
sqlParams.Add(new SqlParameter("@OrderNumber", orderNumber.Value)
{
SqlDbType = SqlDbType.Int
});
}
if (orderStatus != null && orderStatus.Any())
{
query.Append($" AND orderStatusId IN ({string.Join(',', orderStatus.Select(i => (int)i).ToArray())})");
}
var count = context.OrderItemReports.FromSqlRaw(query.ToString(), sqlParams.ToArray()).Count();
if (orderBy == OrderItemReportOrderBy.OrderItemStatus)
{
query.Append($" ORDER BY {orderBy} {direction}, Product ASC");
}
else if (orderBy == OrderItemReportOrderBy.Product)
{
query.Append($" ORDER BY {orderBy} {direction}, OrderItemStatus ASC");
}
else
{
query.Append($" ORDER BY {orderBy} {direction}, OrderItemStatus ASC, Product ASC");
}
if (page.HasValue && pageSize.HasValue)
{
query.Append($" OFFSET {(page > 0 ? page - 1 : 0) * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY");
}
var data = context.OrderItemReports.FromSqlRaw(query.ToString(), sqlParams.ToArray()).ToList();
var result = mapper.Map <IEnumerable <OrderItemReport> >(data);
return(new PagedResult <OrderItemReport>
{
CurrentPage = page ?? 1,
Data = result,
RecordCount = count,
RecordsPerpage = pageSize ?? count
});
}