public RetrieveResponse <OrdersPerStatusResponse> OrdersPerStatus(IDbConnection connection) { var response = new OrdersPerStatusResponse(); for (int i = 0; i < 12; i++) { response.labels.Add(DateTime.Now.AddMonths(-i).ToString("MMMM")); } var orderStatuses = new OrderStatusesRepository().List(connection, new ListRequest()).Entities; foreach (var orderStatus in orderStatuses) { var dataset = new OrdersPerStatusResponse.Dataset(); dataset.backgroundColor = orderStatus.BackgroundColor; dataset.borderColor = orderStatus.BackgroundColor; dataset.label = orderStatus.Name; for (int j = 0; j < 12; j++) { var firstDayOfMonth = new DateTime(DateTime.Now.AddMonths(-j).Year, DateTime.Now.AddMonths(-j).Month, 1); var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1); var orderFields = OrdersRow.Fields; var ordersRequest = new OrderListRequest(); ordersRequest.ColumnSelection = ColumnSelection.KeyOnly; ordersRequest.Criteria = (new Criteria(orderFields.OrderDate.Name) >= firstDayOfMonth & new Criteria(orderFields.OrderDate.Name) <= lastDayOfMonth & new Criteria(orderFields.OrderStatusId.Name) == orderStatus.OrderStatusId.Value & new Criteria(orderFields.IsActive.Name) == 1 & new Criteria(orderFields.NotReal.Name) == 0 ); var orders = new OrdersRepository().List(connection, ordersRequest).Entities; if (!orders.Any()) { dataset.data.Add(Decimal.Zero); continue; } var orderDetailsFields = OrderDetailsRow.Fields; var orderDetailsListRequest = new ListRequest(); orderDetailsListRequest.ColumnSelection = ColumnSelection.Details; orderDetailsListRequest.Criteria = (new Criteria(orderDetailsFields.OrderId.Name).In(orders.Select(o => o.OrderId))); var orderDetails = new OrderDetailsRepository().List(connection, orderDetailsListRequest) .Entities; var totalForMonth = Decimal.Zero; if (orderDetails.Any()) { totalForMonth = orderDetails.Select(od => od.LineTotal).Aggregate((a, b) => a + b) ?? Decimal.Zero; } dataset.data.Add(totalForMonth); } response.datasets.Add(dataset); } return(new RetrieveResponse <OrdersPerStatusResponse> { Entity = response }); }