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