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