private static void ReadExcelsFromDirectory(string filePath) { DataTable dt = new DataTable("newtable"); using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"")) { connection.Open(); string selectSql = @"SELECT * FROM [Sales$]"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection)) { adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } connection.Close(); } string location = dt.Rows[1][0].ToString(); SuperMarket newSupermarket = new SuperMarket() { Name = location, }; for (int i = 3; i < dt.Rows.Count - 1; i++) { int prodId = 0; string productId = dt.Rows[i][0].ToString(); int.TryParse(productId, out prodId); if (prodId > 0) { using (var ctx = new SupermarketEntities()) { if (ctx.Products.Find(prodId) != null) { var supermarket = ctx.SuperMarkets.Where(s => s.Name == newSupermarket.Name).ToList(); if (supermarket.Count == 0) { ctx.SuperMarkets.Add(newSupermarket); supermarket.Add(newSupermarket); } Sale newSale = new Sale() { ProductId = prodId, SuperMarketId = supermarket[0].Id, Date = DateTime.Now, Quantity = int.Parse(dt.Rows[i][1].ToString()), Price = decimal.Parse(dt.Rows[i][2].ToString()), Sum = decimal.Parse(dt.Rows[i][3].ToString()) }; ctx.Sales.Add(newSale); ctx.SaveChanges(); } } } } }
public static void CreateReports() { SupermarketEntities sqlDb = new SupermarketEntities(); using (sqlDb) { var queryProducts = from vendors in sqlDb.Vendors join products in sqlDb.Products on vendors.Id equals products.VendorId join sales in sqlDb.Sales on products.Id equals sales.ProductId select new { ProductId = products.Id, PriductName = products.Name, VendorName = vendors.Name, Quantity = sales.Quantity, Income = sales.Sum }; var goupedProducts = from products in queryProducts group products by products.ProductId into p select new { ProductId = p.Select(a => a.ProductId).FirstOrDefault(), ProductName = p.Select(a => a.PriductName).FirstOrDefault(), VendorName = p.Select(a => a.VendorName).FirstOrDefault(), TotalQuantitySold = p.Sum(a => a.Quantity), TotalIncomes = p.Sum(a => a.Income) }; foreach (var grouped in goupedProducts) { string json = JsonConvert.SerializeObject(grouped); string path = "..\\Product-Reports\\" + grouped.ProductId + ".json"; using (StreamWriter writer = new StreamWriter(path, false)) { writer.WriteLine(json); } var mongoClient = new MongoClient("mongodb://localhost/"); var mongoServer = mongoClient.GetServer(); var supermarketDb = mongoServer.GetDatabase("supermarket"); var productsReport = supermarketDb.GetCollection("productsReport"); ProductReportMongo prodReport = new ProductReportMongo { ProductId = grouped.ProductId, ProductName = grouped.ProductName, VendorName = grouped.VendorName, TotalQuantitySold = grouped.TotalQuantitySold, TotalIncomes = grouped.TotalIncomes }; productsReport.Insert(prodReport); } } }
private static void GenerateExcelVendorReport() { SupermarketEntities sqlDb = new SupermarketEntities(); using (sqlDb) { SupermarketSqliteEntities sqliteDb = new SupermarketSqliteEntities(); using (sqliteDb) { var vendorsInfo = from sales in sqlDb.Sales join products in sqlDb.Products on sales.ProductId equals products.Id join vendors in sqlDb.Vendors on products.VendorId equals vendors.Id join expenses in sqlDb.Expenses on vendors.Id equals expenses.VendorId group new { ProductName = products.Name, VendorName = vendors.Name, Sum = sales.Sum, Expenses = expenses.Value, } by vendors.Name; var slqTaxes = sqliteDb.Taxes; foreach (var vendor in vendorsInfo) { Console.WriteLine(vendor.Key); foreach (var inner in vendor) { foreach (var tax in inner.ProductName) { } } } } } }
public static void TakeDataFromMySql() { SupermarketEntities sqlDb = new SupermarketEntities(); using (sqlDb) { SupermarketData mySql = new SupermarketData(); using (mySql) { List<string> measureNames = mySql.Measures.Select(x => x.Name).ToList(); foreach (string measureName in measureNames) { int measurementCount = sqlDb.Measures.Select(x => x.Name).Where(x => x == measureName).Count(); if (measurementCount == 0) { sqlDb.Measures.Add(new Measure { Name = measureName }); } } sqlDb.SaveChanges(); List<string> vendorNames = mySql.Vendors.Select(x => x.Name).ToList(); foreach (string vendorName in vendorNames) { int vendorNamesCount = sqlDb.Vendors.Select(x => x.Name).Where(x => x == vendorName).Count(); if (vendorNamesCount == 0) { sqlDb.Vendors.Add(new Vendor { Name = vendorName }); } } sqlDb.SaveChanges(); var products = mySql.Products.ToList(); foreach (Product mySqlProduct in products) { string productName = mySqlProduct.Name; string productVendorName = mySqlProduct.Vendor.Name; string productMeasureName = mySqlProduct.Measure.Name; decimal productBasePrice = mySqlProduct.BasePrice; var sqlProduct = sqlDb.Products.Where(x => x.Name == productName).ToList(); if (sqlProduct.Count==0) { Product newProduct = new Product { Name=productName }; sqlDb.Products.Add(newProduct); sqlProduct.Add(newProduct); } sqlProduct[0].BasePrice = productBasePrice; Vendor sqlVendor = sqlDb.Vendors.Where( x => x.Name == productVendorName).FirstOrDefault(); sqlProduct[0].Vendor= sqlVendor; Measure sqlMeasure = sqlDb.Measures.Where( x => x.Name == productMeasureName).FirstOrDefault(); sqlProduct[0].Measure = sqlMeasure; sqlDb.SaveChanges(); } } } }
public static void GeneratePdfDocument() { Document pdfDoc = new Document(PageSize.A4, 5, 5, 15, 15); PdfWriter.GetInstance(pdfDoc, new FileStream("report.pdf", FileMode.Create)); pdfDoc.Open(); PdfPTable table = new PdfPTable(5); PdfPCell cell = new PdfPCell(); cell.Colspan = 5; cell.HorizontalAlignment = Element.ALIGN_MIDDLE; cell.Phrase = new Phrase("Aggregated Sales Report"); table.AddCell(cell); SupermarketEntities sqlDb = new SupermarketEntities(); using (sqlDb) { var salesProducts = from sales in sqlDb.Sales join products in sqlDb.Products on sales.ProductId equals products.Id join measures in sqlDb.Measures on products.MeasureId equals measures.Id select new { Date=sales.Date, Product=products.Name, Quantity=sales.Quantity, UnitPrice=sales.Price, Sum=sales.Sum, Measures=measures.Name, SupermarketId=sales.SuperMarketId }; var salesQueryProducts = from sales in salesProducts join supermarkets in sqlDb.SuperMarkets on sales.SupermarketId equals supermarkets.Id select new { Date = sales.Date, Product = sales.Product, Quantity = sales.Quantity, UnitPrice = sales.UnitPrice, Sum = sales.Sum, Measures=sales.Measures, Location = supermarkets.Name }; var groupedProducts = from groupedSales in salesQueryProducts group groupedSales by groupedSales.Date; foreach (var groupItem in groupedProducts) { cell.Phrase = new Phrase("Date: " + groupItem.First().Date.Date); cell.BackgroundColor = BaseColor.LIGHT_GRAY; table.AddCell(cell); PdfPCell secondCell = new PdfPCell(); secondCell.Phrase = new Phrase("Product"); secondCell.BackgroundColor = BaseColor.LIGHT_GRAY; secondCell.Phrase.Font.SetStyle(Font.BOLD); table.AddCell(secondCell); secondCell.Phrase = new Phrase("Quantity"); secondCell.BackgroundColor = BaseColor.LIGHT_GRAY; secondCell.Phrase.Font.SetStyle(Font.BOLD); table.AddCell(secondCell); secondCell.Phrase = new Phrase("UnitPrice"); secondCell.BackgroundColor = BaseColor.LIGHT_GRAY; secondCell.Phrase.Font.SetStyle(Font.BOLD); table.AddCell(secondCell); secondCell.Phrase = new Phrase("Location"); secondCell.BackgroundColor = BaseColor.LIGHT_GRAY; secondCell.Phrase.Font.SetStyle(Font.BOLD); table.AddCell(secondCell); secondCell.Phrase = new Phrase("Sum"); secondCell.BackgroundColor = BaseColor.LIGHT_GRAY; secondCell.Phrase.Font.SetStyle(Font.BOLD); table.AddCell(secondCell); secondCell.BackgroundColor = BaseColor.WHITE; decimal sum = 0; foreach (var item in groupItem) { secondCell.Phrase = new Phrase(item.Product); table.AddCell(secondCell); secondCell.Phrase = new Phrase(item.Quantity + " " + item.Measures); table.AddCell(secondCell); secondCell.Phrase = new Phrase(item.UnitPrice.ToString()); table.AddCell(secondCell); secondCell.Phrase = new Phrase(item.Location); table.AddCell(secondCell); secondCell.Phrase = new Phrase(item.Sum.ToString()); table.AddCell(secondCell); sum += item.Sum; } cell.Colspan = 4; cell.BackgroundColor = BaseColor.WHITE; cell.HorizontalAlignment = PdfPCell.ALIGN_RIGHT; cell.Phrase = new Phrase("Total sum for " + groupItem.First().Date.Date); cell.HorizontalAlignment = PdfPCell.ALIGN_MIDDLE; table.AddCell(cell); secondCell.Phrase = new Phrase(sum.ToString()); table.AddCell(secondCell); cell.Colspan = 5; cell.BackgroundColor = BaseColor.LIGHT_GRAY; } } pdfDoc.Add(table); pdfDoc.Close(); }