public static HashSet<FinancialReport> GetFinancialReportData()
        {
            var context = new marketsystemEntities();
            var vendors = context.vendors.OrderBy(v => v.vendorName).Select(v => v.vendorName).ToList();
            var allSales = context.sales
                .Select(s => new
                {
                    Vendor = s.product.vendor.vendorName,
                    Product = s.product.productName,
                    Sum = s.quantity * s.price
                })
                .ToList();

            var vendorsTaxes = allSales
                .Select(s => new
                {
                    s.Vendor,
                    Tax = GetTaxByProduct(s.Product) * s.Sum
                })
                .GroupBy(v => v.Vendor, (k, v) => new
                {
                    Vendor = k,
                    Taxes = v.Sum(t => t.Tax)
                })
                .ToDictionary(t => t.Vendor, t => t.Taxes);

            var vendorsIncomes = allSales
                .GroupBy(s => s.Vendor, (k, v) => new
                {
                    Vendor = k,
                    Incomes = v.Sum(a => a.Sum)
                })
                .ToDictionary(t => t.Vendor, t => t.Incomes);

            var vendorsExpenses = GetVendorsExpenses(context);

            var vendorsReport = GenerateReportData(vendors, vendorsIncomes, vendorsExpenses, vendorsTaxes);

            return vendorsReport;
        }
        public static void UpdateProductsFromMssql()
        {
            var msSqLcontext = new SupermarketContext();
            var mySqlContext = new marketsystemEntities();
            var products = msSqLcontext.Products
                .ToList();

            foreach (var product in products)
            {
                var productId = product.Id;
                var productName = product.ProductName;
                var vendorId = product.VendorId;
                var measureId = product.MeasureId;
                var typeId = product.ProductTypeId;
                var price = product.Price;

                try
                {
                    mySqlContext.products.AddOrUpdate(
                        p => p.productId,
                        new product()
                        {
                            productId = productId,
                            productName = productName,
                            vendorId = (int) vendorId,
                            measureId = (int)measureId,
                            productTypeId = (int)typeId,
                            productPrice = (decimal) price
                        });

                    mySqlContext.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw new ArgumentException();
                }
            }
        }
        ///<summary>
        ///This is summary for methods that transfer records from MQ SQL Server model into MySQL DB.
        ///The method first select all records from MQ SQL Server model model.
        ///Second it inserts record into MySQL DB.
        ///</summary>
        public static void UpdateMeasuresFromMssql()
        {
            var msSqLcontext = new SupermarketContext();
            var mySqlContext = new marketsystemEntities();
            var measures = msSqLcontext.Measures
                 .ToList();

            foreach (var measure in measures)
            {
                var id = measure.Id;
                var name = measure.MeasureName;

                mySqlContext.measures.AddOrUpdate(
                    m => m.measureId,
                    new MySqlModel.measure()
                    {
                       measureId = id,
                       measureName = name
                    });

                mySqlContext.SaveChanges();

            }
        }
        public static void UpdateProductTypesFromMssql()
        {
            var msSqLcontext = new SupermarketContext();
            var mySqlContext = new marketsystemEntities();
            var productTypes = msSqLcontext.ProductTypes
                .ToList();

            foreach (var productType in productTypes)
            {
                var id = productType.Id;
                var name = productType.TypeName;

                mySqlContext.producttypes.AddOrUpdate(
                    pt => pt.typeId,
                    new producttype()
                    {
                        typeId = id,
                        typeName = name
                    });

                mySqlContext.SaveChanges();
            }
        }
        public static void UpdateVendorsFromMssql()
        {
            var msSqLcontext = new SupermarketContext();
            var mySqlContext = new marketsystemEntities();
            var vendors = msSqLcontext.Vendors
                .ToList();

            foreach (var vendor in vendors)
            {
                var id = vendor.Id;
                var name = vendor.VendorName;

                mySqlContext.vendors.AddOrUpdate(
                    v => v.vendorId,
                    new MySqlModel.vendor()
                    {
                        vendorId = id,
                        vendorName = name
                    });

                mySqlContext.SaveChanges();
            }
        }
        public static void UpdateVendorsExpenseFromMssql()
        {
            var msSqLcontext = new SupermarketContext();
            var mySqlContext = new marketsystemEntities();
            var vendorsExpense = msSqLcontext.VendorExpenses
                .ToList();

            foreach (var expense in vendorsExpense)
            {
                var vendorId = expense.VendorId;
                var date = expense.Date;
                var total = expense.Total;

                mySqlContext.vendor_expenses.AddOrUpdate(
                    e => new { e.vendorId, e.expenseDate },
                    new vendor_expenses()
                    {
                        vendorId = vendorId,
                        expenseDate = date,
                        total = total
                    });

                mySqlContext.SaveChanges();
            }
        }
        public static void UpdateSupermarketsFromMssql()
        {
            var msSqLcontext = new SupermarketContext();
            var mySqlContext = new marketsystemEntities();
            var supermarkets = msSqLcontext.Supermarkets
                .ToList();

            foreach (var supermarket in supermarkets)
            {
                var id = supermarket.SupermarketId;
                var name = supermarket.Name;

                mySqlContext.supermarkets.AddOrUpdate(
                    s => s.supermarketId,
                    new supermarket()
                    {
                        supermarketId = id,
                        supermarketName = name
                    });

                mySqlContext.SaveChanges();
            }
        }
        public static void UpdateSalesFromMssql()
        {
            var msSqLcontext = new SupermarketContext();
            var mySqlContext = new marketsystemEntities();
            var sales = msSqLcontext.SupermarketSalesProducts
                .ToList();

            foreach (var sale in sales)
            {
                var id = sale.Id;
                var supermarketId = sale.SupermarketId;
                var productId = sale.ProductId;
                var saleDate = sale.SalesDate;
                var quantity = sale.Quantity;
                var price = sale.Price;

                mySqlContext.sales.AddOrUpdate(
                    s => new
                    {
                        s.supermarketId,
                        s.productId,
                        s.saledOn
                    },
                    new sale()
                    {
                        id = id,
                        supermarketId = supermarketId,
                        productId = productId,
                        saledOn = saleDate,
                        quantity = quantity,
                        price = (decimal) price
                    });

                mySqlContext.SaveChanges();
            }
        }
        //Return vendors expenses from main DB (MSSQL) that have been previously imported from markets seles reports
        private static Dictionary<string, decimal> GetVendorsExpenses(marketsystemEntities context)
        {
            var vendorExpenses = context.vendor_expenses
                .GroupBy(e => e.vendor.vendorName, (k, v) => new
                {
                    Vendor = k,
                    Expenses = v.Sum(s => s.total)
                })
                .ToDictionary(t => t.Vendor, t => t.Expenses);

            return vendorExpenses;
        }