public string ExportDataIntoMySql()
{
var context = new SupermarketsChainSqlServerEntities();
var measures = context.Measures;
var supermarkets = context.Supermarkets;
var vendors = context.Vendors;
var expenses = context.Expenses;
var products = context.Products;
var sales = context.Sales;
var connection = new MySqlConnection(MySqlDataSource);
connection.Open();
using (connection)
{
InsertMeasuresIntoDb(measures, connection);
InsertSupermarketsIntoDb(supermarkets, connection);
InsertVendorsIntoDb(vendors, connection);
InsertExpensesIntoDb(expenses, connection);
InsertProductsIntoDb(products, connection);
InsertSalesIntoDb(sales, connection);
InsertSupermarketsProductsIntoDb(supermarkets, connection);
InsertVendorsExpensesIntoDb(vendors, connection);
}
return ExportToMySQLSuccess;
}
private static IQueryable<SalesByProductReport> GetProductDataForReport(DateTime startDate, DateTime endDate)
{
var dbContext = new SupermarketsChainSqlServerEntities();
var products = dbContext.Products
.Where(
p => p.Sales
.Any(s => s.OrderedOn >= startDate && s.OrderedOn <= endDate))
.Select(
p => new SalesByProductReport
{
ProductId = p.Id,
ProductName = p.Name,
VendorName = p.Vendors.Name,
TotalQuantitySold = p.Sales.Count(s => s.ProductId == p.Id),
TotalIncomes = p.Price * p.Sales.Count(s => s.ProductId == p.Id)
});
return products;
}
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 IList<Sale> GetSalesData(DateTime startDate, DateTime endDate)
{
var dbContext = new SupermarketsChainSqlServerEntities();
var salesReportData = dbContext.Sales
.Where(s => s.OrderedOn >= startDate && s.OrderedOn <= endDate)
.GroupBy(
s => new
{
ProductName = s.Products.Name,
Location = s.Supermarkets.Name,
UnitPrice = s.Products.Price,
Measure = s.Products.Measures.Name,
Quantity = dbContext.Sales.Where(ss => ss.OrderedOn == s.OrderedOn).Count(ss => ss.ProductId == s.ProductId),
Sum = s.Products.Price * dbContext.Sales.Where(ss => ss.OrderedOn == s.OrderedOn).Count(ss => ss.ProductId == s.ProductId),
s.OrderedOn
})
.Select(s => new Sale
{
OrderedOn = s.Key.OrderedOn,
Info = new Info
{
ProductName = s.Key.ProductName,
Location = s.Key.Location,
UnitPrice = s.Key.UnitPrice,
Measure = s.Key.Measure,
Quantity = s.Key.Quantity,
Sum = s.Key.Sum
}
})
.ToList();
return salesReportData;
}