public void CollectData(ISheet sheet) { var dbContext = new SupermarketsChainSqlServerEntities(); var vendor = new Vendors(); var product = new Products(); var sale = new Sales(); var supermarket = new Supermarkets(); int quantity; string cellValue; int cell = 1; var saleCell = new Sales(); for (int row = 1; row < sheet.LastRowNum; row++) { if (sheet.GetRow(row) != null) { while (sheet.GetRow(row).GetCell(cell) != null && sheet.GetRow(row).GetCell(cell).ToString() != string.Empty) { cellValue = sheet.GetRow(row).GetCell(cell).ToString(); if (row == 1 && cell == 1) { vendor.Name = cellValue; var supermarketName = vendor.Name.Substring(13); supermarketName = supermarketName.Substring(0, supermarketName.Length - 1); var supermarketTemp = dbContext.Supermarkets .Where(s => s.Name == supermarketName) .Select(s => s.Id) .FirstOrDefault(); saleCell.SupermarketId = supermarketTemp; } if (row > 2 && cellValue != null) { if (cell == 1) { product.Name = cellValue; var productId = dbContext.Products .Where(p => p.Name == product.Name) .Select(p => p.Id) .FirstOrDefault(); saleCell.ProductId = productId; } if (cell == 2) { quantity = int.Parse(cellValue); for (int quantityLines = 0; quantityLines < quantity; quantityLines++) { var newCell = new Sales { ProductId = saleCell.ProductId, SupermarketId = saleCell.SupermarketId, OrderedOn = DateTime.Parse(this.DateOrder) }; dbContext.Sales.Add(newCell); } dbContext.SaveChanges(); } if (cell == 3) { product.Price = decimal.Parse(cellValue); } } cell++; } } product.Name = string.Empty; cell = 1; } }
private string ReplicateSupplyedSupermarkets() { Output.Clear(); var inputSupermarkets = DbOracleContext.SUPERMARKETS; var outputSupermarkets = DbContext.Supermarkets; Output.Append(ReplicateSupermarkersString); foreach (var supermarket in inputSupermarkets) { var newSupermarket = new Supermarkets() { Name = supermarket.NAME }; foreach (var product in supermarket.PRODUCTS) { newSupermarket.Products.Add(DbContext.Products.FirstOrDefault(p => p.Id == product.ID)); } outputSupermarkets.AddOrUpdate(s => s.Name, newSupermarket); } DbContext.SaveChanges(); Output.Append(Done); return Output.ToString(); }