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