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