public PdfReportHandler(SupermarketSystemData data, string path)
{
this.data = data;
this.reportsFilePath = path;
// this.reportsFilePath = reportsFilePath;
}
private static void MigrateSales()
{
var data = new SupermarketSystemData();
var sales = data.Sales.All().ToList();
using (connection = new MySqlConnection {
ConnectionString = ConnectionString
})
{
try
{
connection.Open();
MySqlCommand createTable = connection.CreateCommand();
createTable.CommandText =
"CREATE TABLE IF NOT EXISTS `Sales` (" +
"Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
"StoreId INT NOT NULL, " +
"ProductId INT NOT NULL, " +
"Quantity INT NOT NULL, " +
"SinglePrice DECIMAL(18,2) NOT NULL, " +
"Sum DECIMAL(18,2) NOT NULL, " +
"`Date` DATETIME NOT NULL" +
");";
createTable.ExecuteNonQuery();
createTable.CommandText = "ALTER TABLE Sales " +
"ADD CONSTRAINT fk_ProductsSales FOREIGN KEY (ProductId) REFERENCES Products(Id)";
createTable.ExecuteNonQuery();
MySqlCommand insertCmd = connection.CreateCommand();
foreach (var sale in sales)
{
string dateFormatForMySql = sale.Date.ToString("yyyy-MM-dd HH:mm:ss");
insertCmd.CommandText =
"INSERT INTO Sales(Id, StoreId, ProductId, Quantity, SinglePrice, Sum, `Date`) " +
"VALUES (" + sale.Id + ", " +
sale.StoreId + ", " +
sale.ProductId + ", " +
sale.Quantity + ", " +
sale.SinglePrice + ", " +
sale.Sum + ", " +
"'" + dateFormatForMySql + "'" +
")";
insertCmd.ExecuteNonQuery();
}
Console.WriteLine("Successfully migrated Sales table from MS SQL to MySQL");
}
catch (MySqlException ex)
{
Console.WriteLine("Something went wrong during the Sales table migration!");
Console.WriteLine(ex.Message);
}
}
connection.Close();
}
private static void MigrateProducts()
{
var data = new SupermarketSystemData();
var products = data.Products.All().ToList();
using (connection = new MySqlConnection {
ConnectionString = ConnectionString
})
{
try
{
connection.Open();
MySqlCommand createTable = connection.CreateCommand();
createTable.CommandText =
"CREATE TABLE IF NOT EXISTS `Products` ( " +
"Id INT NOT NULL AUTO_INCREMENT, " +
"Vendor_Id INT NOT NULL, " +
"Product_Name nvarchar(200) NOT NULL, " +
"Measure_Id INT NOT NULL, " +
"Price DECIMAL(19,4) NOT NULL, " +
"ProductType INT NOT NULL, " +
"CONSTRAINT pk_ProductsId PRIMARY KEY (Id), " +
"CONSTRAINT fk_VendorsProducts FOREIGN KEY (Vendor_Id) REFERENCES Vendors(Id), " +
"CONSTRAINT fk_MeasuresProducts FOREIGN KEY (Measure_Id) REFERENCES Measures(Id) " +
");";
createTable.ExecuteNonQuery();
MySqlCommand insertCmd = connection.CreateCommand();
foreach (var product in products)
{
insertCmd.CommandText =
"INSERT INTO Products(Id, Vendor_Id, Product_Name, Measure_Id, Price, ProductType) " +
"VALUES ( " +
"'" + product.Id + "', " +
"'" + product.VendorId + "', " +
"'" + product.ProductName + "', " +
"'" + product.MeasureId + "', " +
"'" + product.Price + "', " +
"'" + product.ProductType + "'" +
")";
insertCmd.ExecuteNonQuery();
}
Console.WriteLine("Successfully migrated Products table from MS SQL to MySQL");
}
catch (MySqlException ex)
{
Console.WriteLine("Something went wrong during the Products table migration!");
Console.WriteLine(ex.Message);
}
}
connection.Close();
}
private static void MigrateExpenses()
{
var data = new SupermarketSystemData();
var expenses = data.Expenses.All().ToList();
using (connection = new MySqlConnection {
ConnectionString = ConnectionString
})
{
try
{
connection.Open();
MySqlCommand createTable = connection.CreateCommand();
createTable.CommandText =
"CREATE TABLE IF NOT EXISTS `Expenses` (" +
"Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
"VendorId INT NOT NULL, " +
"`Date` DATETIME NOT NULL, " +
"Total DECIMAL(18,2) NOT NULL" +
");";
createTable.ExecuteNonQuery();
createTable.CommandText = "ALTER TABLE Expenses " +
"ADD CONSTRAINT fk_VendorsExpenses FOREIGN KEY (VendorId) REFERENCES Vendors(Id)";
createTable.ExecuteNonQuery();
MySqlCommand insertCmd = connection.CreateCommand();
foreach (var expense in expenses)
{
string formatForMySql = expense.Date.ToString("yyyy-MM-dd HH:mm:ss");
insertCmd.CommandText =
"INSERT INTO Expenses(Id, VendorId, Date, Total) " +
"VALUES (" + expense.Id + ", " +
expense.VendorId + ", " +
"'" + formatForMySql + "', " +
expense.Total +
")";
insertCmd.ExecuteNonQuery();
}
Console.WriteLine("Successfully migrated Expenses table from MS SQL to MySQL");
}
catch (MySqlException ex)
{
Console.WriteLine("Something went wrong during the Expenses table migration!");
Console.WriteLine(ex.Message);
}
}
connection.Close();
}
private static void MigrateProducts()
{
SupermarketSystemData data = new SupermarketSystemData();
con = new OracleConnection {
ConnectionString = ConnectionString
};
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT P_ID, " +
"VENDOR_ID," +
"PRODUCT_NAME, " +
"MEASURE_ID, " +
"PRICE, " +
"PRODUCT_TYPE " +
"FROM PRODUCTS";
using (OracleDataReader reader = cmd.ExecuteReader())
{
var lastProduct = data.Products.All().OrderByDescending(p => p.Id).FirstOrDefault();
int dataId = 0;
if (lastProduct != null)
{
dataId = lastProduct.Id;
}
while (reader.Read())
{
int productId = int.Parse(reader["P_ID"].ToString());
if (dataId < productId)
{
Product product = new Product();
// for debugging
product.Id = productId;
product.VendorId = int.Parse(reader["VENDOR_ID"].ToString());
product.ProductName = reader["PRODUCT_NAME"].ToString();
product.MeasureId = int.Parse(reader["MEASURE_ID"].ToString());
product.Price = decimal.Parse(reader["PRICE"].ToString());
product.ProductType = (ProductType)Enum.Parse(typeof(ProductType), reader["PRODUCT_TYPE"].ToString());
data.Products.Add(product);
}
}
data.SaveChanges();
}
Close();
}
private static void MigrateVendors()
{
var data = new SupermarketSystemData();
var vendors = data.Vendors.All().ToList();
using (connection = new MySqlConnection {
ConnectionString = ConnectionString
})
{
try
{
connection.Open();
MySqlCommand createTable = connection.CreateCommand();
createTable.CommandText =
"CREATE TABLE IF NOT EXISTS `Vendors` ( " +
"Id INT NOT NULL AUTO_INCREMENT, " +
"Vendor_Name nvarchar(50) NOT NULL, " +
"CONSTRAINT pk_VendorsId PRIMARY KEY (Id) " +
");";
createTable.ExecuteNonQuery();
MySqlCommand insertCmd = connection.CreateCommand();
foreach (var vendor in vendors)
{
insertCmd.CommandText =
"INSERT INTO Vendors(Id, Vendor_Name) " +
"VALUES (" + vendor.Id + ", " +
"'" + vendor.VendorName + "'" +
")";
insertCmd.ExecuteNonQuery();
}
Console.WriteLine("Successfully migrated Vendors table from MS SQL to MySQL");
}
catch (MySqlException ex)
{
Console.WriteLine("Something went wrong during the Vendors table migration!");
Console.WriteLine(ex.Message);
}
}
connection.Close();
}
private static void MigrateMeasures()
{
SupermarketSystemData data = new SupermarketSystemData();
con = new OracleConnection {
ConnectionString = ConnectionString
};
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT M_ID, MEASURE_NAME FROM MEASURE_UNITS";
using (OracleDataReader reader = cmd.ExecuteReader())
{
var lastMeasure = data.Measures.All().OrderByDescending(v => v.Id).FirstOrDefault();
int dataId = 0;
if (lastMeasure != null)
{
dataId = lastMeasure.Id;
}
while (reader.Read())
{
int measureId = int.Parse(reader["M_ID"].ToString());
if (dataId < measureId)
{
Measure measure = new Measure();
measure.Id = measureId;
measure.MeasureName = (string)reader["MEASURE_NAME"];
data.Measures.Add(measure);
}
}
data.SaveChanges();
}
Close();
}
private static void MigrateVendors()
{
SupermarketSystemData data = new SupermarketSystemData();
con = new OracleConnection {
ConnectionString = ConnectionString
};
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT V_ID,VENDOR_NAME FROM VENDORS";
using (OracleDataReader reader = cmd.ExecuteReader())
{
var lastVendor = data.Vendors.All().OrderByDescending(v => v.Id).FirstOrDefault();
int dataId = 0;
if (lastVendor != null)
{
dataId = lastVendor.Id;
}
while (reader.Read())
{
int vendorId = int.Parse(reader["V_ID"].ToString());
if (dataId < vendorId)
{
Vendor vendor = new Vendor();
vendor.Id = vendorId;
vendor.VendorName = (string)reader["VENDOR_NAME"];
data.Vendors.Add(vendor);
}
}
data.SaveChanges();
}
Close();
}
public static void Main()
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion <SupermarketSystemDbContext, Configuration>());
var data = new SupermarketSystemData();
Console.WriteLine("----------------------------------------------------------------------------");
// Task 1 - Migrate from Oracle to MSSQL SERVER
Console.WriteLine("Migrating data from ORACLE to MS SQL SERVER");
OracleToSqlDb.MigrateToSql();
Console.WriteLine(" ... migration finished\nPress any key to proceed with loading Excel reports to SQL SERVER");
Console.ReadLine();
Console.WriteLine("----------------------------------------------------------------------------");
// Task 2 - load excell reports from zip
Console.WriteLine("\nLoading Excel reports to SQL SERVER");
SalesReportsMigrator reportsMigrator = new SalesReportsMigrator(reportsFile);
reportsMigrator.MigrateSalesReport();
Console.WriteLine("... migrations finished.\nPress any key to proceed withh generating pdf reports");
Console.ReadLine();
Console.WriteLine("----------------------------------------------------------------------------");
// Task 3 - Generate pdf reports
Console.WriteLine("\nGenerating PDF reports");
PdfReportHandler pdfReport = new PdfReportHandler(data, @"../../../Output/Sales-Reports");
pdfReport.CreateReport(new DateTime(2000, 7, 20), new DateTime(2014, 7, 22));
Console.WriteLine("Genrating PDF reports finished.\nPress any key to proceed withh generating XML reports");
Console.ReadLine();
Console.WriteLine("----------------------------------------------------------------------------");
// Task 4 - Generate XML Sales by Vendor Report
Console.WriteLine("\nGenerating XML reports");
XMLFromToMSSQL xmlParser = new XMLFromToMSSQL(data, @"../../../Input/Sample-Vendor-Expenses.xml");
xmlParser.GenerateSalesByVendorReport(@"../../../Output/Sales-by-Vendors-Report.xml",
new DateTime(2014, 07, 01),
new DateTime(2014, 07, 31));
Console.WriteLine("Genrating XML reports finished.\nPress any key to proceed with generating JSON repots and uploding to MongoDB");
Console.ReadLine();
Console.WriteLine("----------------------------------------------------------------------------");
// Task 5 -JSON Reports in MongoDB
SalesReportHandler reportsHandler = new SalesReportHandler(data, @"../../../Output/Json-Reports");
reportsHandler.SaveReportsToFiles(new DateTime(2014, 7, 20), new DateTime(2014, 7, 22));
string localhost = "localhost";
string cloud = "cloud";
reportsHandler.SaveReportsToMongoDb(localhost);
reportsHandler.SaveReportsToMongoDb(cloud);
Console.WriteLine(" ... finished.\nPress any key to proceed with loading expenses data from XML");
Console.ReadLine();
Console.WriteLine("----------------------------------------------------------------------------");
// Task 6 – Load Expense Data from XML
Console.WriteLine("\nLoading expense data from XML");
xmlParser.SaveExpenses();
Console.WriteLine(" ... finished.\nPress any key to proceed with migrating data to MySQL");
Console.ReadLine();
Console.WriteLine("----------------------------------------------------------------------------");
// Task 7 - Load Data to MySQL
Console.WriteLine("Migrating data to MySQL...");
MsSqlToMySql.MigrateToMySql();
Console.WriteLine(" ... finished.\nEnough already.");
Console.ReadLine();
}
private static void MigrateMeasures()
{
var data = new SupermarketSystemData();
var measures = data.Measures.All().ToList();
using (connection = new MySqlConnection {
ConnectionString = ConnectionString
})
{
try
{
connection.Open();
MySqlCommand createTable = connection.CreateCommand();
createTable.CommandText =
"CREATE TABLE IF NOT EXISTS `Measures` ( " +
"Id INT NOT NULL AUTO_INCREMENT, " +
"Measure_Name nvarchar(50) NOT NULL, " +
"CONSTRAINT pk_MeasuresId PRIMARY KEY (Id) " +
");";
createTable.ExecuteNonQuery();
MySqlCommand insertCmd = connection.CreateCommand();
foreach (var measure in measures)
{
// Using input data check
//insertCmd.CommandText =
// "INSERT INTO Measures(Id, Measure_Name) " +
// "SELECT * FROM(SELECT" +
// "(" +
// "'@Id', " +
// "'@measureName'" +
// ") " +
// "AS tmp ";
//insertCmd.CommandText += "WHERE NOT EXISTS(" +
// "SELECT Id, Measure_Name FROM Measures " +
// "WHERE Id = @Id" +
// " AND " +
// "Measure_Name = '@measureName'" +
// ") LIMIT 1;";
//insertCmd.Parameters.AddWithValue("@Id", measure.Id);
//insertCmd.Parameters.AddWithValue("@measureName", measure.MeasureName);
//insertCmd.CommandTimeout = 15;
//insertCmd.CommandType = CommandType.Text;
//insertCmd.ExecuteNonQuery();
insertCmd.CommandText =
"INSERT INTO Measures(Id, Measure_Name) " +
"VALUES (" + measure.Id + ", " +
"'" + measure.MeasureName + "'" +
")";
insertCmd.ExecuteNonQuery();
}
Console.WriteLine("Successfully migrated Measures table from MS SQL to MySQL");
}
catch (MySqlException ex)
{
Console.WriteLine("Something went wrong during the Measures table migration!");
Console.WriteLine(ex.Message);
}
}
connection.Close();
}
public SalesReportHandler(SupermarketSystemData data, string reportsFilePath)
{
this.data = data;
this.reportsFilePath = reportsFilePath;
}
