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