public void Export(SupermarketContext sQLServerContext, string location)
        {
            foreach (var product in sQLServerContext.Products.Include("Vendor"))
            {
                using (SupermarketContext sqlServerInnerContext = new SupermarketContext())
                {
                    int quantitySold = sqlServerInnerContext.Sales.Include("Product.ProductName").
                        Where(x => x.Product.ProductName == product.ProductName).Select(x => x.Quantity).Sum();
                    decimal totalIncome = sqlServerInnerContext.Sales.Include("Product.ProductName").
                        Where(x => x.Product.ProductName == product.ProductName).Select(x => x.Sum).Sum();

                    MongoDbProductFormat newExportObject = new MongoDbProductFormat()
                    {
                        ProductId = product.Id,
                        ProductName = product.ProductName,
                        VendorName = product.Vendor.VendorName,
                        TotalQuantitySold = quantitySold,
                        TotalIncomes = totalIncome
                    };

                    MongoDbProvider.db.SaveData<MongoDbProductFormat>(newExportObject);
                    //Console.WriteLine("{0}, {1}, {2}, {3}, {4}", product.Id, product.ProductName, product.Vendor.VendorName, quantitySold, totalIncome);

                    ExportToJSON(newExportObject, location);
                }
            }
        }
예제 #2
0
 public static IList<SqlPdfFormat> GenerateSalesReport(
     SupermarketContext sQLServerContext, 
     DateTime startDate, 
     DateTime endDate)
 {
     IList<SqlPdfFormat> data;
   
        data= sQLServerContext.Sales.
            Include("SoldDate.Date").
            Include("Product.ProductName").
            Include("Supermarket.Name").
            Where(x => x.SoldDate.Date >= startDate && x.SoldDate.Date <= endDate).
            Select(x => new SqlPdfFormat
             {
                 ProductName = x.Product.ProductName,
                 Quantity = x.Quantity,
                 Measure = x.Product.Measure.MeasureName,
                 SoldDate = x.SoldDate.Date,
                 UnitPrice = x.UnitPrice,
                 SupermarketName = x.Supermarket.Name,
                 Sum = x.Sum
             }).ToList();
    
     return data;
 }
예제 #3
0
 private MainController()
 {
     Database.SetInitializer(new DropCreateDatabaseAlways<SupermarketContext>());
     //Database.SetInitializer(new MigrateDatabaseToLatestVersion<SupermarketContext, Configuration>());
     this.sQLServerContext = new SupermarketContext();
     this.mySqlContext = new SuperMarketEntitiesModel();
     this.excelConnection = new OleDbConnection();
     this.sqliteContext = new SuperMarketEntities();
 }
        public IList<SqliteExcelFormat> GetDataForExcelFile(
            SuperMarketEntities sqliteContext, SupermarketContext sqlServerContext)
        {
            IList<SqliteExcelFormat> data;

            data = sqliteContext.Products.Include("TaxTables.Tax").Select(x => new SqliteExcelFormat
            {
                Vendor = x.VendorName,
                Incomes = x.TotalIncomes,
                TaxPercentage = x.TaxTable.Tax,
                ProductId = x.ProductId
            }).ToList();

            foreach (var item in data)
            {
                //item.Expenses = sqlServerContext.Expenses.Where(x => x.Vendor.VendorName == item.Vendor).Select(x => x.ExpenseValue).First();
                var vendorId = sqlServerContext.Products.Where(x => item.ProductId == x.Id).Select(x => x.VendorId).First();
                item.Expenses = sqlServerContext.Expenses.Where(x => x.VendorId == vendorId).Select(x => x.ExpenseValue).First();
            }

            return data;
        }
        public void Export(string xmlFullFileName, SupermarketContext sqlServerContext)
        {
            XDocument xmlDoc = XDocument.Load(xmlFullFileName);
            var sales = xmlDoc.Descendants("sale").ToList();

            int vendorId = -1;
            string vendorName = string.Empty;

            foreach (var sale in sales)
            {
                vendorName = sale.Attribute("vendor").Value;
                vendorId = sqlServerContext.Vendors.Where(x => x.VendorName == vendorName).
                    Select(x => x.Id).FirstOrDefault();
                var expenses = sale.Descendants("expenses");

                foreach (var expense in expenses)
                {
                    MongoDbExpenseFormat expenseObject = new MongoDbExpenseFormat()
                    {
                        VendorName = vendorName,
                        VendorId = vendorId,
                        Month = DateTime.Parse(expense.Attribute("month").Value),
                        Expense = decimal.Parse(expense.Value),
                    };

                    MongoDbProvider.db.SaveData<MongoDbExpenseFormat>(expenseObject);

                    Expense sqlExpense = new Expense()
                    {
                        VendorId = expenseObject.VendorId,
                        Month = expenseObject.Month,
                        ExpenseValue = expenseObject.Expense
                    };

                    sqlServerContext.Expenses.Add(sqlExpense);
                    sqlServerContext.SaveChanges();
                }
            }
        }
예제 #6
0
 public static IList<SqlXmlFormat> GenerateXmlSalesReport(
     SupermarketContext sQLServerContext,
     DateTime startDate,
     DateTime endDate)
 {
     IList<SqlXmlFormat> data;
     /*
     string sqlString = @"SELECT v.VendorName, sd.Date, SUM(s.Sum) FROM Vendors v
     LEFT JOIN Products p ON p.VendorId =  v.Id
     LEFT JOIN Sales s ON s.ProductId = p.Id
     LEFT JOIN SoldDates sd ON s.SoldDateId = sd.SoldDateId
     GROUP BY sd.Date, v.VendorName";
     */
     var result =
                 from v in sQLServerContext.Vendors
                 join p in sQLServerContext.Products on v.Id equals p.VendorId
                 join s in sQLServerContext.Sales on p.Id equals s.ProductId
                 join sd in sQLServerContext.Sales on s.SoldDateId equals sd.SoldDateId
                 where sd.SoldDate.Date >= startDate && sd.SoldDate.Date <= endDate
                 group v by new
                 {
                     v.VendorName,
                     sd.SoldDate,
                     s.Sum
                 }
                 into x orderby x.Key.VendorName
                        select new SqlXmlFormat
                        {
                            VendorName = x.Key.VendorName,
                            SoldDate = x.Key.SoldDate.Date,
                            Sum = x.Key.Sum
                        };
   
     data = result.ToList();
     return data;
 }
 public void Export(
     SuperMarketEntities sqliteContext, SupermarketContext sqlServerContext, string fileName)
 {
     IList<SqliteExcelFormat> data = GetDataForExcelFile(sqliteContext, sqlServerContext);
     CreateExcelFile(fileName, data);
 }
 public FromMySQLToSQLServer(SupermarketContext sqlServerContext,
                             SuperMarketEntitiesModel mySqlContext)
 {
     this.sqlServerContext = sqlServerContext;
     this.mySqlContext = mySqlContext;
 }
        private void ExportSingleExcelFile(FileInfo file, int dateId, 
            OleDbConnection excelConnection, SupermarketContext sqlServerDb)
        {
            excelConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + 
                string.Format("Data Source={0};", file.FullName) +
                @"Extended Properties=""Excel 12.0;HDR=YES""";
            excelConnection.Open();

            int supermarketId = -1;
            string supermarketName = file.Name.Substring(0, file.Name.IndexOf("-Sales-"));

            if (sqlServerDb.Supermarkets.
                       Where(x => x.Name == supermarketName).Count() == 0)
            {
                sqlServerDb.Supermarkets.Add(new Supermarket()
                {
                    Name = supermarketName
                });
                sqlServerDb.SaveChanges();
            }
            supermarketId = sqlServerDb.Supermarkets.
                Where(x => x.Name == supermarketName).
                Select(x => x.SupermarketId).First();

            DataTable table = new DataTable();

            using (excelConnection)
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(@"SELECT * FROM [Sales$]", excelConnection);
                
                adapter.Fill(table);

                for (int i = 0; i < table.Rows.Count - 1; i++)
                {
                    int productId = -1, quantity = -1;
                    decimal unitPrice = -1, sum = -1;

                    IEnumerable<object> data = table.Rows[i].ItemArray.SkipWhile(x => x.GetType().Equals(typeof(System.DBNull)));
                    int currentColumnIndex = 0;
                    foreach (var item in data)
                    {
                        if (item.GetType() == typeof(System.DBNull))
                        {
                            //Console.WriteLine(item.GetType());
                            continue;
                        }
                        else
                        {
                            switch (currentColumnIndex)
                            {
                                case 0:
                                    {
                                        productId = (int)((double) item);
                                        break;
                                    }
                                case 1:
                                    {
                                        quantity = (int)((double) item);
                                        break;
                                    }
                                case 2:
                                    {
                                        unitPrice = (decimal)((double) item);
                                        break;
                                    }
                                case 3:
                                    {
                                        sum = (decimal)((double) item);
                                        break;
                                    }

                            }   
                        }

                        currentColumnIndex++;
                    }

                    if (productId != -1)
                    {
                        //Console.WriteLine("{0}. {1} -> {2}", i, currentColumnIndex, item);
                        AddSale(productId, quantity, unitPrice, sum,
                            sqlServerDb, dateId, supermarketId);
                    }
                }
            }
        }
        public void Export(string zipFileName, string extractionFolder, 
                           OleDbConnection excelConnection, SupermarketContext sqlServerDb)
        {
            ExtractTo(zipFileName, extractionFolder);
            DirectoryInfo directory = new DirectoryInfo(extractionFolder);
            foreach (var dir in directory.GetDirectories())
            {
                int dateId = -1;
                //Console.WriteLine("{0}", dir.Name);
                DateTime date = DateTime.Parse(dir.Name);
                if (sqlServerDb.SoldDates.Where(x => x.Date == date).Count() == 0)
                {
                    sqlServerDb.SoldDates.Add(new SoldDate()
                    {
                        Date = date
                    });
                    sqlServerDb.SaveChanges();
                }
                dateId = sqlServerDb.SoldDates.Where(x => x.Date.Equals(date)).Select(x => x.SoldDateId).First();

                foreach (var file in dir.GetFiles())
                {
                    ExportSingleExcelFile(file, dateId, excelConnection, sqlServerDb);
                }
            }

        }
        private void AddSale(int productId, int quantity, decimal unitPrice, decimal sum, 
            SupermarketContext sqlServerDb, int dateId, int supermarketId)
        {
            Sale sale = new Sale()
            {
                ProductId = productId,
                Quantity = quantity,
                UnitPrice = unitPrice,
                Sum = sum,
                SoldDateId = dateId,
                SupermarketId = supermarketId
            };

            sqlServerDb.Sales.Add(sale);
            sqlServerDb.SaveChanges();
        }