public static void MigrateReports(ElectronicStoresSystemDbContext storeContext)
{
var mySqlContext = new ElectronicStoreMySQLFluentModel();
var reports = storeContext.Sales.OrderBy(s => s.Store.StoreName).ToList();
for (int i = 1, len = reports.Count; i < len; i++)
{
var newReport = new Report
{
ReportId = i,
Price = reports[i].Price,
ProductName = reports[i].Product.ProductName,
Quantity = reports[i].Quantity,
StoreName = reports[i].Store.StoreName,
Sum = reports[i].Sum,
};
using (var ctx = new ElectronicStoreMySQLFluentModel())
{
ctx.Add(newReport);
ctx.SaveChanges();
}
}
}
public static void MigrateXslToSQL(ElectronicStoresSystemDbContext dbContex, List<List<Sale>> sales)
{
Console.WriteLine("Migrating stores from XML Xls to SQL server...");
foreach (var collection in sales)
{
foreach (Sale sale in collection)
{
if (!dbContex.Stores.Any(x => x.StoreName == sale.Store.StoreName))
{
dbContex.Stores.Add(sale.Store);
dbContex.SaveChanges();
}
}
}
List<Sale> sll = new List<Sale>();
foreach (var collection in sales)
{
foreach (Sale sale in collection)
{
int storeId = dbContex.Stores.Where(s => s.StoreName == sale.Store.StoreName).FirstOrDefault().StoreId;
sale.StoreId = storeId;
sale.Store = null;
sll.Add(sale);
dbContex.Sales.Add(sale);
}
}
Console.WriteLine();
dbContex.SaveChanges();
}
private static void MigrateManufacturers(ElectronicStoresSystemDbContext dbContext)
{
var manufacturers = MongoDbProvider.LoadData<MongoManufacturer>(MongoDbProvider.db);
foreach (var manufacturer in manufacturers)
{
dbContext.Manufacturers.Add(MongoParser.ParseManufacturer(manufacturer));
}
}
private static void MigrateCategories(ElectronicStoresSystemDbContext dbContext)
{
var categories = MongoDbProvider.LoadData<MongoCategory>(MongoDbProvider.db);
foreach (var category in categories)
{
dbContext.Categories.Add(MongoParser.ParseCategory(category));
}
}
public static void MigrateXmlToSQL(ElectronicStoresSystemDbContext dbContex, IList<Expense> expenses)
{
Console.WriteLine("Migrating expenses from XML to SQL server...");
foreach (var expense in expenses)
{
int manufacturerId = dbContex.Manufacturers.Where(m => m.ManufacturerName == expense.Manufacturer.ManufacturerName).FirstOrDefault().ManufacturerId;
expense.ManufacturerId = manufacturerId;
expense.Manufacturer = null;
dbContex.Expenses.Add(expense);
}
dbContex.SaveChanges();
Console.WriteLine("XML expenses to SQL migrated successfully");
}
private static void MigrateProducts(ElectronicStoresSystemDbContext dbContext)
{
var products = MongoDbProvider.LoadData<MongoProduct>(MongoDbProvider.db);
foreach (var product in products)
{
var newProduct = MongoParser.ParseProduct(product);
if (newProduct.ProductName.Length > 99)
{
Console.WriteLine("Cought!!!!");
}
dbContext.Products.Add(MongoParser.ParseProduct(product));
}
}
public static void AddSqLiteData(ElectronicStoresSystemDbContext ctx)
{
var products = ctx.Products.ToList();
foreach (var product in products)
{
var data = new AdditionalData
{
InfoDescription = product.ProductName,
Mark = rand.Next(10, 20),
};
SaveData(data);
}
}
public static void MigrateMongoToSql(ElectronicStoresSystemDbContext dbContext)
{
Console.WriteLine("Starting migration of Mongo categories to SQL.");
MigrateCategories(dbContext);
Console.WriteLine("Migrating mongo categories to SQL done...");
Console.WriteLine("Starting migration of Mongo manufacturers to SQL.");
MigrateManufacturers(dbContext);
Console.WriteLine("Migrating mongo manufacturers to SQL done...");
Console.WriteLine("Starting migration of Mongo products to SQL.");
MigrateProducts(dbContext);
Console.WriteLine("Migrating mongo products to SQL done...");
dbContext.SaveChanges();
}
static void Main()
{
//Problem #1 – Load Excel Reports from ZIP File
// Use once if your MongoDB is empty else delete your Mongo DATABASE for the project so it will generate it new every time
MongoStartData.FillSampleCategories();
MongoStartData.FillSampleManufacturers();
MongoStartData.FillSampleProducts();
// Use once if your SQL Database is empty else delete your SQL DATABASE for the project so it will generate
// the data for the tables
ElectronicStoresSystemDbContext dbContext = new ElectronicStoresSystemDbContext();
MongoMigrator.MigrateMongoToSql(dbContext);
using (dbContext)
{
XlsReader.ExtractZipReports();
var sales = XlsReader.ReadAllExcells();
XlsMigrator.MigrateXslToSQL(dbContext, sales);
var expenses = XmlReader.GetXmlInfo();
XmlMigrator.MigrateXmlToSQL(dbContext, expenses);
MySqlInitializer.UpdateDatabase();
MySqlReportsMigrator.MigrateReports(dbContext);
SQLiteManager.AddSqLiteData(dbContext);
}
var reports = MySQLDataProvider.LoadReports();
PDFCreator.CreatePDF(reports);
XmlCreator.CreateXml(reports);
JSONCreator.CreateJSON(reports);
XlsCreator.GenerateExcelResult();
Console.Write("Database update complete! Press any key to close.");
}
public static void CreatePDF(List<Report> reports)
{
Document myDocument = new Document(PageSize.A4.Rotate());
try
{
PdfWriter.GetInstance(myDocument, new FileStream(@"..\..\..\salesReport.pdf", FileMode.Create));
PdfPTable table = new PdfPTable(5);
float[] widths = new float[] { 25f, 10f, 10f, 45f, 10f };
table.SetWidths(widths);
table.DefaultCell.FixedHeight = 27f;
table.DefaultCell.VerticalAlignment = Element.ALIGN_MIDDLE;
myDocument.Open();
var context = new ElectronicStoresSystemDbContext();
string specifier = "0,0.00";
decimal totalSum = 0;
myDocument.Open();
var headerFont = FontFactory.GetFont("Arial", 14, Font.BOLD);
PdfPCell header = new PdfPCell(new Phrase("Aggregate Sales Report", headerFont));
header.Colspan = 5;
header.FixedHeight = 27f;
header.HorizontalAlignment = Element.ALIGN_CENTER;
header.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(header);
decimal currentSum = 0;
var font = FontFactory.GetFont("Arial", 14, Font.BOLD);
//PdfPCell date = new PdfPCell(new Phrase("D", font));
//date.Colspan = 5;
//date.FixedHeight = 27f;
//date.BackgroundColor = new BaseColor(210, 210, 210);
//date.HorizontalAlignment = Element.ALIGN_LEFT;
//date.VerticalAlignment = Element.ALIGN_MIDDLE;
//table.AddCell(date);
PdfPCell product = new PdfPCell(new Phrase("Product", font));
product.FixedHeight = 27f;
product.BackgroundColor = new BaseColor(210, 210, 210);
product.HorizontalAlignment = Element.ALIGN_LEFT;
product.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(product);
PdfPCell quantity = new PdfPCell(new Phrase("Quantity", font));
quantity.FixedHeight = 27f;
quantity.BackgroundColor = new BaseColor(210, 210, 210);
quantity.HorizontalAlignment = Element.ALIGN_LEFT;
quantity.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(quantity);
PdfPCell price = new PdfPCell(new Phrase("Price", font));
price.FixedHeight = 27f;
price.BackgroundColor = new BaseColor(210, 210, 210);
price.HorizontalAlignment = Element.ALIGN_LEFT;
price.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(price);
PdfPCell location = new PdfPCell(new Phrase("Store", font));
location.FixedHeight = 27f;
location.BackgroundColor = new BaseColor(210, 210, 210);
location.HorizontalAlignment = Element.ALIGN_LEFT;
location.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(location);
PdfPCell sumCol = new PdfPCell(new Phrase("Sum", font));
sumCol.FixedHeight = 27f;
sumCol.BackgroundColor = new BaseColor(210, 210, 210);
sumCol.HorizontalAlignment = Element.ALIGN_LEFT;
sumCol.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(sumCol);
foreach (var report in reports)
{
table.AddCell((report.ProductName).ToString());
table.AddCell((report.Quantity).ToString());
table.AddCell(string.Format("{0:C2}", report.Price));
table.AddCell(String.Format("Supermarket \"{0}\"", report.StoreName));
PdfPCell sum = new PdfPCell(new Phrase(string.Format("{0:C2}", report.Sum)));
sum.HorizontalAlignment = Element.ALIGN_RIGHT;
table.AddCell(sum);
currentSum += report.Sum;
totalSum += report.Sum;
}
PdfPCell current = new PdfPCell(new Phrase(string.Format("{0:C2}", currentSum)));
current.FixedHeight = 27f;
current.HorizontalAlignment = Element.ALIGN_RIGHT;
current.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(current);
PdfPCell grandTotal = new PdfPCell(new Phrase("Grand total: "));
grandTotal.Colspan = 3;
grandTotal.FixedHeight = 27f;
grandTotal.BackgroundColor = new BaseColor(210, 210, 210);
grandTotal.HorizontalAlignment = Element.ALIGN_RIGHT;
grandTotal.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(grandTotal);
PdfPCell total = new PdfPCell(new Phrase(string.Format("{0:C2}", totalSum)));
total.BackgroundColor = new BaseColor(210, 210, 210);
total.HorizontalAlignment = Element.ALIGN_RIGHT;
total.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(total);
myDocument.Add(table);
}
catch (DocumentException de)
{
Console.WriteLine(de.Message);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
}
myDocument.Close();
}