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 ReplicateVendors()
{
Output.Clear();
var inputVendors = DbOracleContext.VENDORS;
var outputVendors = DbContext.Vendors;
Output.Append(ReplicateVendorsString);
foreach (var vendor in inputVendors)
{
var newVendor = new Vendors()
{
Name = vendor.NAME
};
outputVendors.AddOrUpdate(v => v.Name, newVendor);
}
DbContext.SaveChanges();
Output.AppendLine(Done);
return Output.ToString();
}
