private static void ReadData(FileInfo exFile, string rootDirPath, string dirName, DateTime reportDate)
{
string fileName = exFile.Name;
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@" Data Source=" + rootDirPath + "\\" + dirName + "\\" + fileName + "; Persist Security Info=false; Extended Properties=Excel 8.0;";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
using (connection)
{
string sql = "SELECT * FROM [Sales$]";
OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataReader reader = cmd.ExecuteReader();
int supermarketID = 0;
int count = 0;
while (reader.Read())
{
if (count == 0)
{
supermarketID = MsSqlManager.InsertInSupermarketTable(reader[0].ToString());
count++;
continue;
}
else if (count == 1 || reader[0].ToString().Trim() == "…" || reader[0].ToString() == "Total sum:")
{
count++;
continue;
}
else
{
int productID = int.Parse(reader[0].ToString());
string mysqlProductName = MySqlManager.GetProductNameById(productID);
productID = MsSqlManager.GetProductIdByName(mysqlProductName);
int quantity = int.Parse(reader[1].ToString());
decimal unitPirce = decimal.Parse(reader[2].ToString());
decimal sum = decimal.Parse(reader[3].ToString());
MsSqlManager.InsertSalesReport(productID, quantity, unitPirce, sum, reportDate, supermarketID);
}
}
}
}
public static void ReadXmlExpenses(string pathToFile)
{
XmlTextReader reader = new XmlTextReader(pathToFile);
string vendorName = string.Empty;
DateTime date = new DateTime();
decimal expenses = 0;
using (reader)
{
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.HasAttributes)
{
reader.MoveToNextAttribute();
if (reader.Name == "vendor")
{
vendorName = reader.Value;
}
else if (reader.Name == "month")
{
date = DateTime.Parse(reader.Value);
}
}
reader.MoveToElement();
}
if (reader.NodeType == XmlNodeType.Element && reader.Name == "expenses")
{
var expenseStr = reader.ReadInnerXml();
expenses = decimal.Parse(expenseStr);
int vendorId = MsSqlManager.InsertExpenses(vendorName, date, expenses);
MongoExpense mongoExpense = new MongoExpense(vendorId, date, expenses);
MongoDbManager.IsertExpenses(mongoExpense, "mongodb://localhost",
"SupermarketProductReports", "Expenses");
}
}
}
}
static void Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
MsSqlManager.MigrateDataFromMySqlToMSSql();
Console.WriteLine("Migrated database from mysql to sqlserver.");
string zipPath = "../../Sample-Sales-Reports.zip";
string destinationPath = "../../SalesReports";
ZipManager.Unzip(zipPath, destinationPath);
Console.WriteLine("Unziped archive...");
ExcelFilesManager.ReadExcelData(destinationPath);
Console.WriteLine("Excel data read.");
//ZipManager.DeleteTempFiles(destinationPath);
//Console.WriteLine("Deleted temp files");
PdfManager.CreateSalesReportPdfFile("../../SalesReports/SalesReport.pdf");
Console.WriteLine("Pdf created");
XmlManager.PrintToXML();
Console.WriteLine("Xml created");
string mongoConnectionString = Settings.Default.MongoDbConnectionString;
ProductReportsManager.CreateAndSaveProductReports(mongoConnectionString, "../../");
Console.WriteLine("Products reports saved in mongo and mssql");
string xmlExpenses = "../../expenses.xml";
XmlManager.ReadXmlExpenses(xmlExpenses);
var productReports = MongoDbManager.ReadProductsReport(mongoConnectionString, "SupermarketProductReports", "ProductsReports");
SQLiteManager.InsertProductReports(productReports);
Console.WriteLine("SQLite populated");
ExcelFilesManager.WriteDataFromSQLite();
Console.WriteLine("Excel file created");
}
