public static void TransferFromMssqlToMysql() { var mssqlData = new SupermarketChainMssqlData(); var mySqlData = new SupermarketChainMySqlData(); var mySqlVendors = mssqlData.Vendors.All().ToList(); var mySqlMeasure = mssqlData.Measures.All().ToList(); var mySqlProducts = mssqlData.Products.All().ToList(); var mySqlIncomes = mssqlData.Sales .All() .Select(s => new { Income = (s.Quantity * s.Product.Price), ProductId = s.ProductId }) .ToList(); foreach (var vendor in mySqlVendors) { var vendors = new Vendor() { Name = vendor.Name }; mySqlData.Vendors.Add(vendors); mySqlData.SaveChanges(); } foreach (var measure in mySqlMeasure) { var measures = new Measure() { Name = measure.Name }; mySqlData.Measures.Add(measures); mySqlData.SaveChanges(); } foreach (var product in mySqlProducts) { var products = new Product() { Name = product.Name, Price = product.Price, VendorId = mySqlData.Vendors.All().FirstOrDefault(v => v.Name == product.Vendor.Name).Id, MeasureId = mySqlData.Measures.All().FirstOrDefault(m => m.Name == product.Measure.Name).Id }; mySqlData.Products.Add(products); mySqlData.SaveChanges(); } foreach (var income in mySqlIncomes) { var incomes = new Income() { IncomeValue = income.Income, ProductId = income.ProductId }; mySqlData.Incomes.Add(incomes); mySqlData.SaveChanges(); } }
public static void GenerateJsonReports() { Console.WriteLine("Enter startDate and endDate separated by space in format(yyyy-mm-dd):"); string input = Console.ReadLine(); try { string[] dates = input.Split(' '); DateTime startDate = DateTime.Parse(dates[0]); DateTime endDate = DateTime.Parse(dates[1]); if (startDate > endDate) { throw new ArgumentException("StartDate must be before endDate"); } var myssqlData = new SupermarketChainMssqlData(); var jsSerializer = new JavaScriptSerializer(); var sales = myssqlData.Sales .All() .Where(s => s.SoldDate >= startDate && s.SoldDate <= endDate) .Select( s => new { productId = s.ProductId, productName = s.Product.Name, vendorName = s.Product.Vendor.Name, totalQuantitySold = s.Quantity, totalIncomes = s.Quantity * s.Product.Price }).ToList(); foreach (var sale in sales) { var productJson = jsSerializer.Serialize(sale); SaveDataInMongoDb(productJson); if (!File.Exists(Constants.ReportPath + sale.productId + ".json")) { File.WriteAllText(Constants.ReportPath + sale.productId + ".json", productJson); } } } catch (Exception) { Console.WriteLine("Invalid input."); } }
public static void TransferDataFromOracleToMssql() { var oracleData = new SupermarketChainOracleData(); var mssqlData = new SupermarketChainMssqlData(); int productsAdded = 0; int measuresAdded = 0; int vendorsAdded = 0; Console.WriteLine("Data transfer..."); var oracleVendors = oracleData.Vendors.All().ToList(); var oracleMeasures = oracleData.Measures.All().ToList(); var oracleProducts = oracleData.Products.All().ToList(); foreach (var oracleVendor in oracleVendors) { var checkIfVendorExists = mssqlData.Vendors.SearchFor(p => p.Name == oracleVendor.Name).FirstOrDefault(); if (checkIfVendorExists == null) { mssqlData.Vendors.Add( new Vendor { Name = oracleVendor.Name }); mssqlData.SaveChanges(); vendorsAdded++; } } Console.WriteLine(vendorsAdded + " from " + oracleVendors.Count + " products added."); Console.WriteLine(oracleVendors.Count - vendorsAdded + " vendors already exist in the database."); foreach (var oracleMeasure in oracleMeasures) { var checkIfMeasureExists = mssqlData.Measures.SearchFor(p => p.Name == oracleMeasure.Name).FirstOrDefault(); if (checkIfMeasureExists == null) { mssqlData.Measures.Add( new Measure { Name = oracleMeasure.Name }); mssqlData.SaveChanges(); measuresAdded++; } } Console.WriteLine(measuresAdded + " from " + oracleMeasures.Count + " products added."); Console.WriteLine(oracleMeasures.Count - measuresAdded + " measures already exist in the database."); foreach (var oracleProduct in oracleProducts) { var checkIfProductExists = mssqlData.Products.SearchFor(p => p.Name == oracleProduct.Name).FirstOrDefault(); if (checkIfProductExists == null) { mssqlData.Products.Add( new Product { Name = oracleProduct.Name, MeasureId = mssqlData.Measures.All().FirstOrDefault(m => m.Name == oracleProduct.Measure.Name).Id, Price = oracleProduct.Price, VendorId = mssqlData.Vendors.All().FirstOrDefault(m => m.Name == oracleProduct.Vendor.Name).Id }); mssqlData.SaveChanges(); productsAdded++; } } Console.WriteLine(productsAdded + " from " + oracleProducts.Count + " products added."); Console.WriteLine(oracleProducts.Count - productsAdded + " products already exist in the database."); }
public static void ExportDataToPdf(DateTime startDate, DateTime endDate) { var myssqlData = new SupermarketChainMssqlData(); var document = new Document(PageSize.A4, 50, 50, 10, 10); // Create a new PdfWriter object, specifying the output stream var output = File.Create(Constants.ReportPath + "PdfReport.pdf"); var writer = PdfWriter.GetInstance(document, output); // Open the Document for writing document.Open(); var salesInfoTable = new PdfPTable(5); salesInfoTable.TotalWidth = 100f; salesInfoTable.HorizontalAlignment = 0; salesInfoTable.SpacingBefore = 5; salesInfoTable.SpacingAfter = 5; salesInfoTable.DefaultCell.Border = 0; salesInfoTable.SetWidths(new float[] { 3f, 1.5f, 2f, 3f, 1f }); //set fonts BaseFont bfTimes = BaseFont.CreateFont(BaseFont.TIMES_ROMAN, BaseFont.CP1252, false); Font normal = new Font(bfTimes, 10); Font bold = new Font(bfTimes, 11, Font.BOLD); PdfPCell cellHeader = new PdfPCell(new Phrase("Aggregated Sales Report")); cellHeader.Colspan = 5; cellHeader.HorizontalAlignment = 1; //0=Left, 1=Centre, 2=Right cellHeader.BackgroundColor = new BaseColor(135, 196, 28); cellHeader.PaddingTop = 10f; cellHeader.PaddingBottom = 10f; salesInfoTable.AddCell(cellHeader); salesInfoTable.AddCell(new Phrase("Product", bold)); salesInfoTable.AddCell(new Phrase("Quantity", bold)); salesInfoTable.AddCell(new Phrase("Unit Price", bold)); salesInfoTable.AddCell(new Phrase("Location", bold)); salesInfoTable.AddCell(new Phrase("Sum", bold)); var salesQuery = myssqlData.Sales .All() .Where(s => s.SoldDate >= startDate && s.SoldDate <= endDate) .Select( s => new { productName = s.Product.Name, totalQuantitySold = s.Quantity, unitPrice = s.Product.Price, location = s.Supermarket.Name, totalIncomes = s.Quantity * s.Product.Price }).ToList(); foreach (var sale in salesQuery) { salesInfoTable.AddCell(sale.productName); salesInfoTable.AddCell(Convert.ToDecimal(sale.totalQuantitySold).ToString(CultureInfo.InvariantCulture)); salesInfoTable.AddCell(Convert.ToDecimal(sale.unitPrice).ToString(CultureInfo.InvariantCulture)); salesInfoTable.AddCell(sale.location); salesInfoTable.AddCell(Convert.ToDecimal(sale.totalIncomes).ToString(CultureInfo.InvariantCulture)); } document.Add(salesInfoTable); document.Close(); }
public static void Generate() { string input = Console.ReadLine(); try { string[] dates = input.Split(' '); DateTime startDate = DateTime.Parse(dates[0]); DateTime endDate = DateTime.Parse(dates[1]); if (startDate > endDate) { throw new ArgumentException("StartDate must be before endDate"); } var msSqlData = new SupermarketChainMssqlData(); XmlDocument xml = new XmlDocument(); XmlElement salesElement = xml.CreateElement("sales"); xml.AppendChild(salesElement); var vendors = msSqlData.Sales .SearchFor(s => s.SoldDate >= startDate && s.SoldDate <= endDate) .GroupBy(v => v.Product.Vendor.Name, (key, val) => new { Name = key, Sales = val.Select(d => new { d.SoldDate, Sum = d.Quantity * d.Product.Price }) }); foreach (var vendor in vendors) { XmlElement saleElement = xml.CreateElement("sale"); saleElement.SetAttribute("vendor", vendor.Name); Console.WriteLine(vendor.Name); var sales = vendor.Sales.GroupBy( s => s.SoldDate, (key, val) => new { Date = key, Sum = val.Sum(t => t.Sum) }); foreach (var sale in sales) { XmlElement summaryElement = xml.CreateElement("summary"); summaryElement.SetAttribute("date", sale.Date.ToString("dd-MMM-yyyy")); summaryElement.SetAttribute("total-sum", sale.Sum.ToString(CultureInfo.InvariantCulture)); saleElement.AppendChild(summaryElement); Console.WriteLine(sale.Date.ToString("dd-MMM-yyyy")); Console.WriteLine(sale.Sum); } salesElement.AppendChild(saleElement); } string output = xml.OuterXml; StreamWriter file = new StreamWriter(Constants.XmlReportsPath); file.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>"); XDocument doc = XDocument.Parse(output); file.WriteLine(doc); file.Close(); } catch (Exception) { Console.WriteLine("Invalid input."); } }