public static void Transfer(SupermarketsEntities sqlserver) { using (var mysql = new MySqlSupermarket()) { // SET IDENTITY_INSERT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188059.aspx sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Vendors ON"); sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Measures ON"); sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Products ON"); var mysqlTables = new IEnumerable[] { mysql.Vendors, mysql.Measures, mysql.Products }; var sqlserverEntityFactories = new Func<object>[] { () => new Vendor(), () => new Measure(), () => new Product() }; var sqlserverTables = new DbSet[] { sqlserver.Vendors, sqlserver.Measures, sqlserver.Products }; for (int ii = 0; ii < mysqlTables.Length; ii++) { foreach (var mysqlObject in mysqlTables[ii]) { var sqlserverObject = sqlserverEntityFactories[ii](); sqlserverObject.LoadPropertiesFrom(mysqlObject); sqlserverTables[ii].Add(sqlserverObject); } } sqlserver.SaveChanges(); sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Vendors OFF"); sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Measures OFF"); sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Products OFF"); } }
static void Main() { using (var context = new SupermarketsEntities()) { GenerateAggregateReport(context, "../../aggregated-sales-report.xml"); } }
public static void Transfer(SupermarketsEntities sqlserver) { var data = ExcelReader.GetReportsData("files\\reports.zip", "files\\temp"); foreach (var superMarketSales in data.GroupBy(o => o[0])) { var supermarket = sqlserver.Supermarkets.Where(v => v.Name == superMarketSales.Key).FirstOrDefault(); if (supermarket == null) { supermarket = new Supermarket { Name = superMarketSales.Key }; sqlserver.Supermarkets.Add(supermarket); } foreach (var saleData in superMarketSales) { var sale = new ProductSupermarketSale { Supermarket = supermarket, DateSold = DateTime.Parse(saleData[1]), ProductId = int.Parse(saleData[2]), UnitPrice = decimal.Parse(saleData[3]), Quantity = decimal.Parse(saleData[4]), }; sqlserver.Sales.Add(sale); } } sqlserver.SaveChanges(); }
static void Main() { using (var context = new SupermarketsEntities()) { WriteVendorExpensesReport(context, @"..\..\VendorExpenses.xml"); } }
public static void GenerateAggregateReport(SupermarketsEntities sqlserver, string fileName) { Encoding encoding = Encoding.GetEncoding("utf-8"); using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding)) { writer.Formatting = Formatting.Indented; writer.IndentChar = '\t'; writer.Indentation = 1; WriteVendorSales(writer, sqlserver); } }
public static void WriteVendorExpensesReport(SupermarketsEntities sqlserver, string filename) { using (XmlReader reader = XmlReader.Create(filename)) { string currentVendor = string.Empty; while (reader.Read()) { if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "sale")) { currentVendor = reader.GetAttribute("vendor"); if (sqlserver.Vendors.Any(v => v.Name == currentVendor)) { Vendor vendor = new Vendor(); vendor.Name = currentVendor; sqlserver.Vendors.Add(vendor); } } if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "expenses")) { Vendor vendor = sqlserver.Vendors.Where(v => v.Name == currentVendor).FirstOrDefault(); if (vendor != null) { DateTime monthDate = DateTime.Parse(string.Format("01-{0}", reader.GetAttribute("month"))); VendorExpenses expense = new VendorExpenses(); expense.VendorId = vendor.Id; expense.Month = monthDate.Month; expense.Year = monthDate.Year; expense.Expenses = reader.ReadElementContentAsDecimal(); sqlserver.VendorExpenses.Add(expense); AddToMongoDB(expense); } } } sqlserver.SaveChanges(); } }
public static void GeneratePdfReport(SupermarketsEntities sqlserver, string outputFile) { //context.Database.CreateIfNotExists(); PdfPTable table = new PdfPTable(5); table.WidthPercentage = 100; table.SetWidths(new int[] { 37, 16, 11, 25, 11 }); AddTableHeader(table); var aggregatedSales = from sale in sqlserver.Sales.Include("Product").Include("Product.Measure").Include("Supermarket") group sale by sale.DateSold into g select new { Date = g.Key, Sum = g.Sum(y => y.Quantity * y.UnitPrice), Sales = g }; foreach (var saleDate in aggregatedSales) { AddDayHeader(table, saleDate.Date); var salesByDay = from sale in saleDate.Sales.ToList() select new { Product = sale.Product.Name, Quantity = sale.Quantity, Mea = sale.Product.Measure.Name, UnitPrice = sale.UnitPrice, Location = sale.Supermarket.Name, Sum = sale.Quantity * sale.UnitPrice }; foreach (var sale in salesByDay) { table.AddCell(sale.Product); table.AddCell(string.Format("{0} {1}", sale.Quantity.ToString(), sale.Mea)); table.AddCell(sale.UnitPrice.ToString("F2")); table.AddCell(sale.Location); table.AddCell(sale.Sum.ToString("F2")); } AddDayTotal(table, saleDate.Date, saleDate.Sum); } SaveToFile(outputFile, table); }
private static void WriteVendorSales(XmlTextWriter writer, SupermarketsEntities sqlserver) { writer.WriteStartDocument(); writer.WriteStartElement("sales"); var query = from sale in sqlserver.Sales group sale by new { VendorName = sale.Product.Vendor.Name, DateSold = sale.DateSold } into g select new { VendorName = g.Key.VendorName, DateSold = g.Key.DateSold, TotalSum = g.Sum(y => y.Quantity * y.UnitPrice) }; string currVendor = string.Empty; foreach (var item in query) { if (item.VendorName != currVendor) { if (currVendor != String.Empty) { writer.WriteEndElement(); } writer.WriteStartElement("sale"); writer.WriteAttributeString("vendor", item.VendorName); currVendor = item.VendorName; } writer.WriteStartElement("summary"); writer.WriteAttributeString("date", item.DateSold.ToString()); writer.WriteAttributeString("total-sum", item.TotalSum.ToString()); writer.WriteEndElement(); } writer.WriteEndElement(); }
static void Main(string[] args) { // Console.SetIn(new StringReader("N\nN\nN\nN\nN\nY\nY\n")); if (Ask("Run Task 1?")) { using (var sqlserver = new SupermarketsEntities(true)) { // Database.SetInitializer<SupermarketsEntities>(new DropCreateDatabaseAlways<SupermarketsEntities>()); // sqlserver.Database.Initialize(true); if (Ask("Transfer from MySQL?")) { MySqlTransfer.Transfer(sqlserver); Console.WriteLine("Transfer complete"); } if (Ask("Transfer from Excel?")) { ExcelTransfer.Transfer(sqlserver); Console.WriteLine("Transfer complete"); } } } using (var sqlserver = new SupermarketsEntities()) { if (Ask("Run Task 2?")) { var file = @"output\report-aggregate-sales.pdf"; Directory.CreateDirectory("output"); Supermarkets.Task2.PDF.PdfSalesReport.GeneratePdfReport(sqlserver, file); Console.WriteLine(string.Format("PDF aggregate sales report in {0}", file)); } if (Ask("Run Task 3?")) { var file = @"output\report-vendor-sales.xml"; Directory.CreateDirectory("output"); Supermarkets.Task3.XML.GenerateXMLFile.GenerateAggregateReport(sqlserver, file); Console.WriteLine(string.Format("XML vendor sales report in {0}", file)); } if (Ask("Run task 4?")) { Supermarkets.Task4.MongoDB.InsertIntoMongoDB.GenerateMongoDBProductReport(); Console.WriteLine("Data uploaded to MongoDb"); } if (Ask("Run task 5?")) { var file = @"files\VendorExpenses.xml"; Supermarkets.Task5.VendorExpencesXML.GenerateVendorExpenses.WriteVendorExpensesReport(sqlserver, file); Console.WriteLine("Task 5: expenses added to MongoDb and SQLServer"); } if (Ask("Run Task 6?")) { var db = @"output\taxes.sqlite"; File.Delete(db); File.Copy(@"files\\taxes_and_vendor_financials.sqlite", db); var file = @"output\final-report.xlsx"; using (var sqlite = new Supermarkets.SQLite.EntityFramework.SQLiteTaxesEntities()) { Supermarkets.Task6.MongoToSQLite.LoadMongoIntoSQLite.Load(sqlite); Supermarkets.Task6.TotalReport.ExcelWriter.GenerateExcel(sqlite.VendorFinancialResults, file); } Console.WriteLine(string.Format("Loaded SQLite in {0}", db)); Console.WriteLine(string.Format("Final report in {0}", file)); } } }
static void Main(string[] args) { using (var entities = new SupermarketsEntities()) { } }
static void Main() { var filename = Path.Combine(@"..\..\", string.Format("SalesReport{0}.pdf", DateTime.Today.ToString("yyyyMMdd"))); System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.InvariantCulture; using (SupermarketsEntities context = new SupermarketsEntities()) { GeneratePdfReport(context, filename); } System.Diagnostics.Process.Start(filename); }