public IEnumerable <InventoryReportRequest> GetInventoryReport() { List <InventoryReportRequest> requestList = new List <InventoryReportRequest>(); using (var cn = new SqlConnection(Settings.GetConnectionString())) { SqlCommand cmd = new SqlCommand("GetInventoryReport"); cmd.Connection = cn; cmd.CommandType = CommandType.StoredProcedure; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { InventoryReportRequest currentRow = new InventoryReportRequest(); currentRow.MakeName = dr["MakeName"].ToString(); currentRow.ModelName = dr["ModelName"].ToString(); currentRow.StockValue = (decimal)dr["Stock Value"]; currentRow.Count = (int)dr["Count"]; currentRow.Year = Convert.ToInt32(dr["Year"]); currentRow.IsNew = Convert.ToBoolean(dr["IsNew"]); requestList.Add(currentRow); } return(requestList); } } }
public IEnumerable <InventoryReportRequest> GetInventoryReport() { var vehicleDetails = GetNamesFromId(_vehicles); var vehicleGroups = from v in vehicleDetails orderby v.Year group v by v.ModelName; List <InventoryReportRequest> inventoryReportRequests = new List <InventoryReportRequest>(); foreach (var vehicleGroup in vehicleGroups) { foreach (var vehicle in vehicleGroup) { InventoryReportRequest request = new InventoryReportRequest(); request.MakeName = vehicle.MakeName; request.ModelName = vehicle.ModelName; request.Year = vehicle.Year; request.Count = vehicleGroup.Where(v => v.ModelName == v.ModelName).Count(); request.StockValue = (request.Count) * vehicle.SalesPrice; request.IsNew = vehicle.IsNew; inventoryReportRequests.Add(request); } } return(inventoryReportRequests); }
public void GetInventoryReport(InventoryReportRequest inventoryReportRequest) { DateTime endDate = DateTime.UtcNow.Date; DateTime beginDate = endDate.AddDays(-inventoryReportRequest.DaysOrderAndInventoryHistoryToUse); // Find products that are associated with this manufacturer. Task <List <Product> > productsTask = Task.Factory.StartNew(() => m_amazonMwSdbService.GetProductsForManufacturer(inventoryReportRequest.ManufacturerId)); Task <List <OrderSummary> > ordersTask = Task.Factory.StartNew(() => m_amazonMwSdbService.GetOrderSummaryForManufacturer(inventoryReportRequest.ManufacturerId, beginDate, endDate)); Task <List <InventorySummary> > inventoryHistoriesTask = Task.Factory.StartNew(() => m_amazonMwSdbService.GetInventorySummaryForManufacturer(inventoryReportRequest.ManufacturerId, beginDate, endDate)); Task.WaitAll(new Task[] { productsTask, ordersTask, inventoryHistoriesTask }); List <Product> products = productsTask.Result .Where(w => w.ASIN != null) .ToList(); Dictionary <string, LookupProductResponse> lookupProductResponses = products .Select(s => new { s.ASIN, LookupProductResponse = m_productAdvertisingApi.LookupProduct(new LookupProductRequest { ItemId = s.ASIN, IdType = IdType.ASIN, ResponseGroup = ResponseGroup.Medium }) }) .ToDictionary(k => k.ASIN, v => v.LookupProductResponse); Dictionary <string, OrderSummary> groupedOrders = ordersTask.Result .GroupBy(g => g.ASIN) .ToDictionary(k => k.Key, v => v.Single()); Dictionary <string, InventorySummary> inventorySummaries = inventoryHistoriesTask.Result .ToDictionary(k => k.ASIN, v => v); List <InventoryReportLine> inventoryReportLines = products.Select( s => { InventoryReportLine inventoryReportLine = new InventoryReportLine { Name = s.Name, ItemNumber = s.ItemNumber, ASIN = s.ASIN, SKU = s.SKU, Cost = s.Cost, QuantityInCase = s.QuantityInCase }; if (s.ASIN != null) { InventorySummary inventorySummary = null; OrderSummary orderSummary = null; if (inventorySummaries.ContainsKey(s.ASIN)) { inventorySummary = inventorySummaries[s.ASIN]; inventoryReportLine.CurrentAfnFulfillableQuantity = inventorySummary.CurrentAfnFulfillableQuantity; inventoryReportLine.DaysInStockDuringTimeframe = inventorySummary.DaysInStockDuringTimeframe; } if (groupedOrders.ContainsKey(s.ASIN)) { orderSummary = groupedOrders[s.ASIN]; inventoryReportLine.QuantitySoldTimeframe = orderSummary.TotalQuantity; inventoryReportLine.TotalExtensionSoldTimeframe = orderSummary.TotalExtension; } if (inventorySummary != null && orderSummary != null) { int currentAfnFulfillableQuantity = inventorySummary.CurrentAfnFulfillableQuantity; decimal quantitySoldPerDayInStock = inventorySummary.DaysInStockDuringTimeframe > 0 ? orderSummary.TotalQuantity / inventorySummary.DaysInStockDuringTimeframe : 0; inventoryReportLine.QuantitySoldPerDayInStock = Math.Round(quantitySoldPerDayInStock, 2); int recommendedReorderQuantity = (int)Math.Round((quantitySoldPerDayInStock * (inventoryReportRequest.DaysToReorder + inventoryReportRequest.DaysLeadTime) - currentAfnFulfillableQuantity), 0); inventoryReportLine.RecommendedReorderQuantity = recommendedReorderQuantity > 0 ? recommendedReorderQuantity : 0; } LookupProductResponse lookupProductResponse = lookupProductResponses[s.ASIN]; if (lookupProductResponse != null && lookupProductResponse.ProductMetadata != null) { inventoryReportLine.SellersRank = lookupProductResponse.ProductMetadata.SalesRank; } } return(inventoryReportLine); }).ToList(); string fileName = string.Format("{0}_InventoryReport_{1}", inventoryReportRequest.ManufacturerName, DateTime.UtcNow.ToShortDateString()); ExcelUtility.WriteExcelFileToResponse(Response, inventoryReportLines.OrderByDescending(o => o.CurrentAfnFulfillableQuantity), c_worksheet1Name, fileName); }