static void Main()
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("../../ExpensesByVendorAndByMonth.xml");
var context = new SupermarketContext();
XmlNodeList Vendors = xmlDoc.DocumentElement.SelectNodes("/expenses-by-month/vendor");
foreach (XmlElement item in Vendors)
{
string vendorName = item.Attributes["name"].Value;
foreach (XmlNode expense in item.ChildNodes)
{
DateTime expenseMonth = DateTime.Parse(expense.Attributes["month"].Value);
decimal expensePrice = Convert.ToDecimal(expense.InnerText);
try
{
var vendorId = context.Suppliers.First(v => v.Name == vendorName).Id;
context.Expenses.Add(new Expense
{
Date = expenseMonth,
Amount = expensePrice,
SupplierId = vendorId
});
context.SaveChanges();
}
catch
{
throw new ArgumentNullException("Such vendor is not found in the database", vendorName);
}
}
}
}
private static void MoveProducts()
{
var client = new SupermarketModel();
using (client)
{
var products = client.Products;
using (SupermarketContext context = new SupermarketContext())
{
foreach (var item in products)
{
var product = new SupermarketSQL.Models.Product
{
ProductName = item.ProductName,
BasePrice = item.BasePrice,
MeasureId = item.MeasureId,
VendorId = item.VendorId
};
context.Products.Add(product);
}
context.SaveChanges();
}
}
}
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);
}
}
}
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 CreateTable(string pdfFile)
{
SupermarketContext model = new SupermarketContext();
Document document = new Document();
PdfWriter writer = PdfWriter.GetInstance(document,
new FileStream("../../" + pdfFile, FileMode.Create));
using (writer)
{
document.Open();
PdfPTable table = new PdfPTable(5);
table.AddCell("Product");
table.AddCell("Quantity");
table.AddCell("Unit Price");
table.AddCell("Location");
table.AddCell("Sum");
var sales = model.Sales.Include("Product").Select(s => s).ToList();
foreach (var sale in sales)
{
table.AddCell(sale.Product.ProductName.ToString());
table.AddCell(sale.Quantity.ToString());
table.AddCell(sale.UnitPrice.ToString());
table.AddCell(sale.Supermarket.ToString());
table.AddCell(sale.Sum.ToString());
}
document.Add(table);
document.Close();
}
}
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 List <VendorExpense> ReadFileXML(string path)
{
List <VendorExpense> readedVendorsExpense = new List <VendorExpense>();
using (XmlReader reader = XmlReader.Create(path))
{
while (reader.Read())
{
if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "sale"))
{
var newVendor = new VendorExpense();
var name = reader.GetAttribute("vendor");
var dbCobntext = new SupermarketContext();
var vendor = dbCobntext.Vendors.FirstOrDefault(x => x.Name == name);
newVendor.Vendor = vendor;
reader.Read();
reader.Read();
while ((reader.NodeType == XmlNodeType.Element) &&
(reader.Name == "expenses"))
{
var month = reader.GetAttribute("month");
DateTime vendorMonth = DateTime.ParseExact(month, "MMM-yyyy", CultureInfo.InvariantCulture);
var sum = reader.ReadElementString();
newVendor.Date = vendorMonth;
newVendor.MonthExpense = decimal.Parse(sum, CultureInfo.InvariantCulture);
reader.Read();
readedVendorsExpense.Add(newVendor);
newVendor = new VendorExpense();
newVendor.Vendor = vendor;
}
}
}
return(readedVendorsExpense);
}
}
private List <ProductReport> GetProductReports()
{
using (SupermarketContext context = new SupermarketContext())
{
List <ProductReport> productReports = new List <ProductReport>();
productReports = context.Database.SqlQuery <ProductReport>(ProductReportQuery).ToList();
return(productReports);
}
}
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 static void ReplicateOracleDataToSqlServer(SupermarketContext context)
{
ReplicateCategories(context);
ReplicateCustomers(context);
ReplicateMeasures(context);
ReplicateSuppliers(context);
ReplicateProducts(context);
ReplicateOrders(context);
}
public void MigrateDataFromMySqlToSqlServer()
{
using (var supermarketContextSqlServer = new SupermarketContext())
{
using (var supermarketContextMySql = new SupermarketModel())
{
foreach (var measureMySql in supermarketContextMySql.Measures)
{
if (!supermarketContextSqlServer.Measures
.Any(m => m.MeasureName == measureMySql.MeasureName))
{
supermarketContextSqlServer.Measures.Add(new Models.Measure()
{
MeasureName = measureMySql.MeasureName
});
}
}
foreach (var vendorMySql in supermarketContextMySql.Vendors)
{
if (!supermarketContextSqlServer.Vendors
.Any(v => v.VendorName == vendorMySql.VendorName))
{
supermarketContextSqlServer.Vendors.Add(new Models.Vendor()
{
VendorName = vendorMySql.VendorName
});
}
}
supermarketContextSqlServer.SaveChanges();
foreach (var productMySql in supermarketContextMySql.Products)
{
if (!supermarketContextSqlServer.Products
.Any(p => p.Name == productMySql.ProductName))
{
var vendorSqlServer = supermarketContextSqlServer.Vendors
.First(v => v.VendorName == productMySql.Vendor.VendorName);
var measureSqlServer = supermarketContextSqlServer.Measures
.First(m => m.MeasureName == productMySql.Measure.MeasureName);
supermarketContextSqlServer.Products.Add(new Models.Product()
{
BasePrice = productMySql.BasePrice,
Name = productMySql.ProductName,
Measure = measureSqlServer,
Vendor = vendorSqlServer,
});
}
}
}
supermarketContextSqlServer.SaveChanges();
}
}
public SupermarketRepository(SupermarketContext context, IPropertyMappingService propertyMappingService,
IProductPropertyMappingService productPropertyMappingService, IStaffMemberPropertyMappingService staffMemberPropertyMappingService,
IStockPropertyMappingService stockPropertyMappingService)
{
_context = context;
_propertyMappingService = propertyMappingService;
_productPropertyMappingService = productPropertyMappingService;
_staffMemberPropertyMappingService = staffMemberPropertyMappingService;
_stockPropertyMappingService = stockPropertyMappingService;
}
private static void GenerateJSONReports()
{
Console.WriteLine("Enter start date in format \'YYYY-MM-DD\': ");
string startDateInput = Console.ReadLine();
Console.WriteLine("Enter end date in format \'YYYY-MM-DD\': ");
string endDateInput = Console.ReadLine();
try
{
DateTime startDate = DateTime.Parse(startDateInput);
DateTime endDate = DateTime.Parse(endDateInput);
if (startDate > endDate)
{
throw new ArgumentException("End date must be after start date!");
}
var mssqlData = new SupermarketContext();
var jsSerializaer = new JavaScriptSerializer();
var sales =
mssqlData.Sales.Where(s => s.SoldDate >= startDate && s.SoldDate <= endDate)
.Select(
s =>
new
{
productId = s.ProductId,
productName = s.Product.ProductName,
vendorName = s.Product.Vendor.VendorName,
totalQuantitySold = s.Quantity,
totalIncomes = s.Quantity * s.Product.Price
})
.ToList();
foreach (var sale in sales)
{
var jsonReport = jsSerializaer.Serialize(sale);
SaveDataInMongoDb(jsonReport);
if (!File.Exists(JsonReportPath + sale.productId + ".json"))
{
File.WriteAllText(JsonReportPath + sale.productId + ".json", jsonReport);
}
}
}
catch (Exception)
{
Console.WriteLine("Invalid input");
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateProductsTypesFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var productstypes = oracleContext.PRODUCTSTYPES
.Where(pt => pt.ISCOPIED == false && pt.ISDELETED == false)
.Select(pt => new
{
pt.TYPENAME
}).ToList();
if (productstypes.Count > 0)
{
var addedProductsTypesList = new List <string>();
foreach (var type in productstypes)
{
var typeName = type.TYPENAME;
try
{
msSqLcontext.ProductTypes.AddOrUpdate(
pt => pt.TypeName,
new ProductType()
{
TypeName = typeName
});
msSqLcontext.SaveChanges();
addedProductsTypesList.Add(typeName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var typesToChange =
oracleContext.PRODUCTSTYPES.Where(pt => addedProductsTypesList.Contains(pt.TYPENAME)).ToList();
typesToChange.ForEach(pt => pt.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Types from OracleBD into MS SQL Server:");
typesToChange.ForEach(tp => Console.WriteLine("Added types name: {0}", tp.TYPENAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into PRODUCTSTYPES table!");
}
}
private static void FromMySqlNeverAgain()
{
var dbcontext = new SupermarketContext();
var mySqlContx = new SupermarketModel();
using (mySqlContx)
{
var products = mySqlContx.Products.OrderBy(e => e.ID).ToList();
var vendors = mySqlContx.Vendors.ToList();
var mesuares = mySqlContx.Measures.ToList();
using (dbcontext)
{
foreach (var mesuare in mesuares)
{
var newMeasure = new Measure()
{
ID = mesuare.ID,
Name = mesuare.Name
};
dbcontext.Measures.Add(newMeasure);
}
foreach (var vendor in vendors)
{
var newVendor = new Vendor()
{
ID = vendor.ID,
Name = vendor.Name
};
dbcontext.Vendors.Add(newVendor);
}
foreach (var product in products)
{
var some = new Product
{
BasePrice = product.BasePrice,
Measure_ID = product.Measure_ID,
Name = product.Name,
Vendor_ID = product.Vendor_ID,
};
dbcontext.Products.Add(some);
}
dbcontext.SaveChanges();
}
}
}
public static void MigrateFromMySqlToMSSql()
{
using (var productContext = new ProductContext())
{
using (var supermarketContext = new SupermarketContext())
{
MigrateVendorsTable(productContext, supermarketContext);
MigrateMeasuresTable(productContext, supermarketContext);
MigrateProductsTable(productContext, supermarketContext);
supermarketContext.SaveChanges();
}
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateVendorsFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var vendors = oracleContext.VENDORS
.Where(v => v.ISCOPIED == false && v.ISDELETED == false)
.Select(v => new
{
v.VENDORNAME
})
.ToList();
if (vendors.Count > 0)
{
var addedVendorsList = new List <string>();
foreach (var vendor in vendors)
{
var vendorName = vendor.VENDORNAME;
try
{
msSqLcontext.Vendors.AddOrUpdate(
v => v.VendorName,
new Vendor()
{
VendorName = vendorName
});
msSqLcontext.SaveChanges();
addedVendorsList.Add(vendorName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var vendorsToChange = oracleContext.VENDORS.Where(v => addedVendorsList.Contains(v.VENDORNAME)).ToList();
vendorsToChange.ForEach(v => v.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Vendors from OracleBD into MS SQL Server:");
vendorsToChange.ForEach(v => Console.WriteLine("Added vendor name: {0}", v.VENDORNAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into VENDORS table!");
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateMeasuresFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var measures = oracleContext.MEASURES
.Where(m => m.ISCOPIED == false && m.ISDELETED == false)
.Select(m => new
{
m.MEASURENAME
}).ToList();
if (measures.Count > 0)
{
var addedMeasuresList = new List <string>();
foreach (var measure in measures)
{
var measureName = measure.MEASURENAME;
try
{
msSqLcontext.Measures.AddOrUpdate(
m => m.MeasureName,
new Measure()
{
MeasureName = measureName
});
msSqLcontext.SaveChanges();
addedMeasuresList.Add(measureName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var measuresToChange = oracleContext.MEASURES.Where(m => addedMeasuresList.Contains(m.MEASURENAME)).ToList();
measuresToChange.ForEach(m => m.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Measures from OracleBD into MS SQL Server:");
measuresToChange.ForEach(m => Console.WriteLine("Added measure name: {0}", m.MEASURENAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into MEASURES table!");
}
}
private static void MigrateVendorsTable(ProductContext productContext,
SupermarketContext supermarketContext)
{
var productTable = productContext.Vendors;
var supermarketTable = supermarketContext.Vendors;
foreach (var item in productTable)
{
var newItem = new Vendor()
{
Id = item.Id, VendorName = item.VendorName
};
supermarketTable.Add(newItem);
}
}
private static void MigrateMeasuresTable(ProductContext productContext,
SupermarketContext supermarketContext)
{
var productTable = productContext.Measures;
var supermarketTable = supermarketContext.Measures;
foreach (var item in productTable)
{
var newItem = new Measure()
{
Id = item.Id, MeasureName = item.MeasureName
};
supermarketTable.Add(newItem);
}
}
private static void MigrateProductsTable(ProductContext productContext,
SupermarketContext supermarketContext)
{
var productTable = productContext.Products;
var supermarketTable = supermarketContext.Products;
foreach (var item in productTable)
{
var newItem = new Product.Model.Product()
{
Id = item.Id, VendorId = item.VendorId, ProductName = item.ProductName, MeasureId = item.MeasureId, BasePrice = item.BasePrice
};
supermarketTable.Add(newItem);
}
}
private static double PourReportData(PdfPTable table)
{
double totalSum = 0;
double grandTotal = 0;
var msSQLcontext = new SupermarketContext();
msSQLcontext.Vendors.FirstOrDefault();
var salesReport = msSQLcontext.SupermarketSalesProducts
.Select(p => new
{
date = p.SalesDate,
location = p.Supermarket.Name,
product = p.Product.ProductName,
quantity = p.Quantity,
price = p.Price,
measure = p.Product.Measure.MeasureName
})
.GroupBy(d => d.date);
foreach (var item in salesReport)
{
HeaderGeneration(table, item.Key);
double sum = 0;
foreach (var item2 in item)
{
sum = (double)(item2.quantity * item2.price);
table.AddCell(item2.product);
table.AddCell(item2.quantity.ToString() + " " + item2.measure);
table.AddCell(String.Format("{0:0.00}", item2.price));
table.AddCell(item2.location);
table.AddCell(String.Format("{0:0.00}", sum));
totalSum += sum;
}
FooterGeneration(table, totalSum, item.Key);
grandTotal += totalSum;
totalSum = 0;
}
return(grandTotal);
}
public void CreatePdfAggregatedSalesReports(string destinationDirectoryPath)
{
float fontSize = 12f;
Font.FontFamily fontFamily = new Font.FontFamily();
fontFamily = Font.FontFamily.COURIER;
using (var supermarketContext = new SupermarketContext())
{
var distinctDates = supermarketContext.Sales
.Select(s => s.Date).Distinct().ToList();
foreach (var date in distinctDates)
{
using (var currentAggregateSalesReport = new Document())
{
PdfWriter.GetInstance(currentAggregateSalesReport, new FileStream(
string.Format("{0}/Aggregated-Sales-Report-{1}.pdf",
destinationDirectoryPath, date.ToShortDateString()), FileMode.Create));
currentAggregateSalesReport.Open();
PdfPTable table = new PdfPTable(5);
IList <PdfPCell> cells = new List <PdfPCell>();
AddTitleRows(fontSize, fontFamily, date, cells);
var sales = supermarketContext.Sales.Include("Product")
.Include("Supermarket").Where(s => s.Date == date).ToList();
var measures = supermarketContext.Measures;
foreach (var sale in sales)
{
string productName = supermarketContext.Products
.Find(sale.ProductID).Name;
string location = supermarketContext.Supermarkets
.Find(sale.SupermarketID).Name;
string measure = measures.Find(sale.Product.MeasureID).MeasureName;
AddSaleReportRow(fontSize, fontFamily, cells, sale, measure, productName, location);
}
foreach (var cell in cells)
{
table.AddCell(cell);
}
currentAggregateSalesReport.Add(table);
}
}
}
}
private static void FillExpensesInSql(ICollection <Expense> expenses)
{
SupermarketContext context = new SupermarketContext();
using (context)
{
foreach (var expense in expenses)
{
context.Expenses.Add(expense);
}
context.SaveChanges();
}
Console.WriteLine("5. Expenses saved in SQL Server!");
}
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();
}
/// <summary>
/// Check for existing product type, because we want to have distinct data in the database and if the product type do not exists the method add it to the database.
/// </summary>
/// <param name="prodTypeName">the type to be checked</param>
/// <param name="context">the Entity Framework connection to the database</param>
private void CheckProductType(string prodTypeName, SupermarketContext context)
{
var existPodType = context.ProductTypes
.Where(t => t.TypeName == prodTypeName)
.Select(t => t.TypeName)
.FirstOrDefault();
if (existPodType == null)
{
context.ProductTypes.Add(new ProductType
{
TypeName = prodTypeName
});
context.SaveChanges();
}
}
private static void ReplicateMeasures(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var measures = oracleDb.MEASURES.ToList();
foreach (var measure in measures)
{
if (context.Measures.Any(m => m.Name == measure.NAME) == false)
{
context.Measures.Add(new Measure
{
Name = measure.NAME
});
}
}
context.SaveChanges();
}
private void GetSales(string directory,
SupermarketContext supermarketContext, IList <Models.Sale> allSales)
{
foreach (var sale in GetSalesFromExcelFiles(directory, supermarketContext))
{
allSales.Add(sale);
}
string[] subDirectories = Directory.GetDirectories(directory);
if (subDirectories.Length > 0)
{
for (int i = 0; i < subDirectories.Length; i++)
{
GetSales(subDirectories[i], supermarketContext, allSales);
}
}
}
/// <summary>
/// Load the zipped files with the excel reports.
/// </summary>
/// <param name="context">The context relationship with the Entity framework and the database</param>
public void LoadExelReports(SupermarketContext context)
{
DirectoryInfo directoryInfo = new DirectoryInfo(this.FilePath);
Console.WriteLine(@" Loading Exel Reports To MsSqlDB
------------------------------------------------");
Console.WriteLine("Extracting data from file {0}", directoryInfo.FullName);
string tempFolder = string.Format("{0}{1}", Directory.GetCurrentDirectory(), TempFolderName);
string currentReportDate = string.Empty;
using (ZipArchive archive = ZipFile.OpenRead(this.FilePath))
{
foreach (ZipArchiveEntry entry in archive.Entries)
{
if (entry.FullName.EndsWith("/", StringComparison.OrdinalIgnoreCase))
{
//Gets from the folders the date in which the reports are made
currentReportDate = entry.FullName.TrimEnd('/');
}
else
{
if (!Directory.Exists(tempFolder))
{
Directory.CreateDirectory(tempFolder);
}
//extract the readed file always into this "temporary" file
entry.ExtractToFile(Path.Combine(tempFolder, TempFileName), true);
DataTable excelData = this.ReadExcelData(string.Format("{0}{1}", tempFolder, TempFileName));
DataRowCollection arrExelData = excelData.Rows;
string marketName = this.SupermarketName(arrExelData[0].ItemArray);
this.CheckForExistingSupermarket(arrExelData[0].ItemArray, context);
//The loop starts from 2, because of the cell formatting in the excel file, if the loop starts form 0 it will iterate trough array full of empty strings
for (int i = 2; i < arrExelData.Count - 1; i++)
{
InsertIntoDataBase(arrExelData[i].ItemArray, context, currentReportDate, marketName);
}
}
}
}
Console.WriteLine("Zip excel reports imported.");
}
private static void InitializeDataFromExcel()
{
var db = new SupermarketContext();
var generator = new Generator(@"..\..\Sample-Sales-Reports.zip", @"..\..\");
var reportList = generator.Generate();
using (db)
{
foreach (var item in reportList)
{
db.DailyReports.Add(item);
}
db.SaveChanges();
Console.WriteLine("1. Data from Excel saved in SQL Server!");
}
}
private static void ReplicateCategories(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var categories = oracleDb.CATEGORIES.ToList();
foreach (var category in categories)
{
if (context.Categories.Any(c => c.Name == category.NAME) == false)
{
context.Categories.Add(new Category
{
Name = category.NAME,
Description = category.DESCRIPTION
});
}
}
context.SaveChanges();
}
public void FillTable()
{
SupermarketContext context = new SupermarketContext();
using (context)
{
foreach (var report in this.reports)
{
var supermarket = context.Supermarkets.Where(x => x.Name == report.Name).FirstOrDefault();
if (supermarket == null)
{
var newSupermarket = new Supermarket.Model.SalesReports.Supermarket()
{
Name = report.Name
};
context.Supermarkets.Add(newSupermarket);
supermarket = newSupermarket;
context.SaveChanges();
}
for (int i = 0; i < report.ProductID.Count; i++)
{
var newSalesReport = new SalesReport()
{
SupermarketId = supermarket.Id,
ProductId = report.ProductID[i],
Quantity = report.Quantity[i],
UnitPrice = report.UnitPrice[i],
Sum = report.Sum[i],
Date = report.Date
};
context.SalesReports.Add(newSalesReport);
}
}
context.SaveChanges();
}
}
private static void ReplicateCustomers(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var customers = oracleDb.CUSTOMERS.ToList();
foreach (var customer in customers)
{
if (context.Customers.Any(c => c.Name == customer.NAME) == false)
{
context.Customers.Add(new Customer
{
Name = customer.NAME,
Address = customer.ADDRESS,
Phone = customer.PHONE
});
}
}
context.SaveChanges();
}
public void MigrateDataFromExcelFiles(string zipFilePath)
{
ExtractZipFile(zipFilePath);
using (var supermarketContext = new SupermarketContext())
{
IList <Models.Sale> allSales = new List <Models.Sale>();
GetSales(TempFolderForExtract, supermarketContext, allSales);
foreach (var sale in allSales)
{
supermarketContext.Sales.Add(sale);
}
supermarketContext.SaveChanges();
}
Directory.Delete(TempFolderForExtract, true);
}
public void CreateReport()
{
string connectionString = "mongodb://localhost";
var url = new MongoUrl(connectionString);
var client = new MongoClient(url);
var server = client.GetServer();
var database = server.GetDatabase("ProductReports");
database.DropCollection("ProductReports");
var collection = database.GetCollection("ProductReports");
using (var supermarket = new SupermarketContext())
{
var productsById = supermarket.SalesReports.Include("Vendors").Include("Products").GroupBy(r => r.ProductId).ToList();
foreach (var group in productsById)
{
var firstProduct = group.First();
var productDocument = new BsonDocument();
productDocument["product-id"] = firstProduct.ProductId;
productDocument["product-name"] = firstProduct.Product.ProductName.Replace("\"", "\\\"");
productDocument["vendor-id"] = firstProduct.Product.VendorId;
productDocument["vendor-name"] = firstProduct.Product.Vendor.VendorName.Replace("\"", "\\\"");
productDocument["total-quantity-sold"] = group.Sum(g => g.Quantity);
productDocument["total-incomes"] = group.Sum(g => g.Sum).ToString();
collection.Insert(productDocument);
using (StreamWriter documentWriter = new StreamWriter(string.Format("../../Product-Reports/{0}.json", productDocument["product-id"]), false))
{
documentWriter.WriteLine("{");
documentWriter.WriteLine("\t\"product-id\": \"{0}\"", productDocument["product-id"]);
documentWriter.WriteLine("\t\"product-name\": \"{0}\"", productDocument["product-name"]);
documentWriter.WriteLine("\t\"vendor-name\": \"{0}\"", productDocument["vendor-name"]);
documentWriter.WriteLine("\t\"total-quantity-sold\": \"{0}\"", productDocument["total-quantity-sold"]);
documentWriter.WriteLine("\t\"total-incomes\": \"{0}\"", productDocument["total-incomes"]);
documentWriter.WriteLine("}");
}
}
}
}
private static void ReplicateOrders(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var orders = oracleDb.ORDERS.ToList();
foreach (var order in orders)
{
if (context.Orders.Any(o => o.Id == order.ID) == false)
{
context.Orders.Add(new Order
{
Quantity = order.QUANTITY,
ProductId = (int)order.PRODUCT_ID,
Discount = order.DISCOUNT,
Date = order.ORDER_DATE,
CustomerId = (int)order.CUSTOMER_ID
});
}
}
context.SaveChanges();
}
private static void ReplicateProducts(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var products = oracleDb.PRODUCTS.ToList();
foreach (var product in products)
{
if (context.Products.Any(p => p.Name == product.NAME) == false)
{
context.Products.Add(new Product
{
Name = product.NAME,
Price = product.PRICE,
SupplierId = (int)product.SUPPLIER_ID,
CategoryId = (int)product.CATEGORY_ID,
MeasureId = (int)product.MEASURE_ID
});
}
}
context.SaveChanges();
}
private static void ReplicateSuppliers(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var suppliers = oracleDb.SUPPLIERS.ToList();
foreach (var supplier in suppliers)
{
if (context.Suppliers.Any(s => s.Name == supplier.NAME) == false)
{
context.Suppliers.Add(new Supplier
{
Name = supplier.NAME,
Address = supplier.ADDRESS,
Phone = supplier.PHONE
});
}
}
context.SaveChanges();
}
public ImportFromXML(string expensesImportPath, SupermarketContext context)
{
this.ExpensesImportPath = expensesImportPath;
this.SupermarketContext = context;
}
public static void GenerateJsonReports()
{
Console.WriteLine("Enter start date in format \'YYYY-MM-DD\': ");
string startDateInput = Console.ReadLine();
Console.WriteLine("Enter end date in format \'YYYY-MM-DD\': ");
string endDateInput = Console.ReadLine();
Console.WriteLine("Processing data.......");
try
{
DateTime startDate = DateTime.Parse(startDateInput);
DateTime endDate = DateTime.Parse(endDateInput);
if (startDate > endDate)
{
throw new ArgumentException("End date must be after start date!");
}
var mssqlData = new SupermarketContext();
var jsSerializaer = new JavaScriptSerializer();
var sales =
mssqlData.SupermarketSalesProducts.Where(s => s.SalesDate >= startDate && s.SalesDate <= endDate)
.Select(
s => new
{
productId = s.ProductId,
productName = s.Product.ProductName,
vendorName = s.Product.Vendor.VendorName,
totalQuantitySold = s.Quantity,
totalIncomes = s.Quantity * s.Product.Price
}).ToList();
Console.WriteLine("Sending data to MongoDB.....");
foreach (var sale in sales)
{
var jsonReport = jsSerializaer.Serialize(sale);
SaveDataInMongoDb(jsonReport);
if (!File.Exists(JsonReportPath + sale.productId + ".json"))
{
File.WriteAllText(JsonReportPath + sale.productId + ".json", jsonReport);
}
}
}
catch (Exception)
{
Console.WriteLine("Invalid input");
return;
}
Console.WriteLine("JSON reports were generated.");
DirectoryInfo directoryInfo = new DirectoryInfo(JsonReportPath);
Console.WriteLine("Directory: {0}", directoryInfo.FullName);
foreach (var file in directoryInfo.GetFiles())
{
Console.WriteLine("File: {0}", file.Name);
}
Console.WriteLine("JSON reports were imported in MongoDB successfully.");
}
