public ActionResult <IEnumerable <SummarizedOrderReport> > GetSummarizedReport([FromQuery] List <OrderItemStatusEnum> itemStatus,
[FromQuery] List <long> products,
[FromQuery] int volumeUnitId = 5, [FromQuery] int massUnitId = 1,
[FromQuery] SummarizedOrderOrderBy orderBy = SummarizedOrderOrderBy.OrderItemStatus,
[FromQuery] OrderByDirection direction = OrderByDirection.Asc, int?page = 1, int?pageSize = 10)
{
var data = service.GetSummarizedReport(UserData.CorpClientId.Value, volumeUnitId, massUnitId, itemStatus, products, orderBy, direction, page, pageSize);
if (data != null)
{
return(Ok(data));
}
return(NoContent());
}
public PagedResult <SummarizedOrderReport> GetSummarizedReport(int corpClientId, int volumeUnitId, int massUnitId, List <OrderItemStatusEnum> itemStatus, List <long> productIds, SummarizedOrderOrderBy orderBy, OrderByDirection direction, int?page, int?pageSize)
{
return(orderApp.GetSummarizedReport(corpClientId, volumeUnitId, massUnitId, itemStatus, productIds, orderBy, direction, page, pageSize));
}
public PagedResult <SummarizedOrderReport> GetSummarizedReport(int corpClientId, int volumeUnitId, int massUnitId,
List <OrderItemStatusEnum> itemStatus,
List <long> productIds,
SummarizedOrderOrderBy 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
NEWID() as RowId,
ProductId,
Product,
OrderItemStatusId,
OrderItemStatus,
MeasureUnitTypeId,
SUM(Quantity) as Quantity,
CASE
WHEN MeasureUnitTypeId = 1 THEN @mid
ELSE @vid
END as MeasureUnitId,
(SELECT M.Description FROM MeasureUnit M WHERE M.MeasureUnitId = IIF(X.MeasureUnitTypeId=1,@mid,@vid)) as MeasureUnit,
(SELECT M.ShortName FROM MeasureUnit M WHERE M.MeasureUnitId = IIF(X.MeasureUnitTypeId=1,@mid,@vid)) as ShortMeasureUnit
FROM
(SELECT
P.ProductId,
P.Name as Product,
CASE
WHEN U.MeasureUnitTypeId = 1 THEN
CAST(dbo.ConvertMeasure(I.MeasureUnitId,I.Quantity,@mid) as decimal(18,2))
ELSE
CAST(dbo.ConvertMeasure(I.MeasureUnitId,I.Quantity,@vid) as decimal(18,2))
END as Quantity,
CASE
WHEN U.MeasureUnitTypeId = 1 THEN 1
ELSE 5
END as MeasureUnitId,
U.MeasureUnitTypeId,
S.OrderItemStatusId,
S.Description AS OrderItemStatus
FROM OrderItem I
JOIN Product P ON I.ProductId = P.ProductId
JOIN OrderItemStatus S ON I.OrderItemStatusId = S.OrderItemStatusId
JOIN MeasureUnit U ON I.MeasureUnitId = U.MeasureUnitId
WHERE P.CorpClientId = @corpClientId) X
GROUP BY
ProductId,
Product,
MeasureUnitTypeId,
OrderItemStatusId,
OrderItemStatus,
MeasureUnitId
) X WHERE 1 = 1 ");
if (itemStatus != null && itemStatus.Any())
{
query.Append($" AND OrderItemStatusId IN ({string.Join(',', itemStatus.Select(i => (int)i).ToArray())})");
}
if (productIds != null && productIds.Any())
{
query.Append($" AND ProductId IN ({string.Join(',', productIds.Select(i => i).ToArray())})");
}
var count = context.SummarizedOrderReports.FromSqlRaw(query.ToString(), sqlParams.ToArray()).Count();
if (orderBy == SummarizedOrderOrderBy.OrderItemStatus)
{
query.Append($" ORDER BY {orderBy} {direction}, Product ASC");
}
else if (orderBy == SummarizedOrderOrderBy.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.SummarizedOrderReports.FromSqlRaw(query.ToString(), sqlParams.ToArray()).ToList();
var result = mapper.Map <IEnumerable <SummarizedOrderReport> >(data);
return(new PagedResult <SummarizedOrderReport>
{
CurrentPage = page ?? 1,
Data = result,
RecordCount = count,
RecordsPerpage = pageSize ?? count
});
}