public static IQueryable<EntityFramework.Data.Dates> ReadDates() { ParadiseSupermarketChainEntities supermarketEntities = new ParadiseSupermarketChainEntities(); IQueryable<EntityFramework.Data.Dates> dates = from d in supermarketEntities.Dates select d; return dates; }
public static IQueryable<EntityFramework.Data.Sales> ReadProductsByDate(int dateId) { ParadiseSupermarketChainEntities supermarketEntities = new ParadiseSupermarketChainEntities(); IQueryable<EntityFramework.Data.Sales> sales = from s in supermarketEntities.Sales where s.DateId == dateId select s; return sales; }
static void VendorXml() { ParadiseSupermarketChainEntities sqlCon = new ParadiseSupermarketChainEntities(); var query = from product in sqlCon.Products join vendor in sqlCon.Vendors on product.VendorId equals vendor.Id join sale in sqlCon.Sales on product.Id equals sale.ProductId join date in sqlCon.Dates on sale.DateId equals date.Id orderby date.Date orderby product.Name select new { Price = product.BasePrice, Name = vendor.Name, Date = date.Date }; Dictionary<Tuple<string, DateTime>, decimal> vendorCollection = new Dictionary<Tuple<string, DateTime>, decimal>(); foreach (var item in query) { if (!vendorCollection.ContainsKey(new Tuple<string, DateTime>(item.Name, item.Date))) { vendorCollection.Add(new Tuple<string, DateTime>(item.Name, item.Date), 0); } vendorCollection[new Tuple<string, DateTime>(item.Name, item.Date)] += item.Price; } XMLWriter writer = new XMLWriter(); string vendorName = ""; foreach (var item in vendorCollection) { if (vendorName == item.Key.Item1) { writer.AddNewSubElement(item.Key.Item2, item.Value); } else { writer.AddNewElement(item.Key.Item1); writer.AddNewSubElement(item.Key.Item2, item.Value); writer.EndElement(); vendorName = item.Key.Item1; } } }
//PDFGenerator.GeneratePDF(); static void ProductReportsToJsonAndMongo() { ParadiseSupermarketChainEntities sqlCon = new ParadiseSupermarketChainEntities(); var query = from product in sqlCon.Products join vendor in sqlCon.Vendors on product.VendorId equals vendor.Id join sale in sqlCon.Sales on product.Id equals sale.ProductId join date in sqlCon.Dates on sale.DateId equals date.Id orderby product.Id select new { Id = product.Id, Name = product.Name, Quantity = sale.Quantity, Sum = sale.Sum, VendorName = vendor.Name, Date = date.Date }; Dictionary<int, Tuple<string, int, decimal, string, DateTime>> collection = new Dictionary<int, Tuple<string, int, decimal, string, DateTime>>(); foreach (var item in query) { if (!collection.ContainsKey(item.Id)) { collection.Add(item.Id, new Tuple<string, int, decimal, string, DateTime>((string)item.Name, (int)item.Quantity, (decimal)item.Sum, (string)item.VendorName, (DateTime)item.Date)); continue; } int newQuantity = collection[item.Id].Item2 + (int)item.Quantity; decimal newSum = collection[item.Id].Item3 + item.Sum; collection[item.Id] = new Tuple<string, int, decimal, string, DateTime>((string)item.Name, newQuantity, newSum, (string)item.VendorName, (DateTime)item.Date); } foreach (var item in collection) { JSON.Extract(item.Key, item.Value.Item1, item.Value.Item4, item.Value.Item2, item.Value.Item3); MongoWriter mongoWriter = new MongoWriter("Products"); mongoWriter.WriteProduct(item.Key, item.Value.Item1, item.Value.Item4, item.Value.Item2, item.Value.Item3, item.Value.Item5); } }
static void ExcelReports() { //Console.WriteLine("Input zip file path: "); //string zipFilePath = Console.ReadLine(); ArchiveAccess archive = new ArchiveAccess(@"E:\newTeamwork\project\Sample-Reports.zip"); archive.Extract(); OpenAccessMySQL mysqlCon = new OpenAccessMySQL(); ParadiseSupermarketChainEntities sqlCon = new ParadiseSupermarketChainEntities(); using (mysqlCon) { using (sqlCon) { foreach (var product in mysqlCon.Products) { var efProduct = new EntityFramework.Data.Products(); efProduct.Id = product.Id; efProduct.Name = product.ProductName; efProduct.BasePrice = (decimal)product.BasePrice; var measure = sqlCon.Measurements.Where(m => m.Name == product.Measure.MeasureName).FirstOrDefault(); if (measure == null) { measure = new EntityFramework.Data.Measurements(); measure.Name = product.Measure.MeasureName; sqlCon.Measurements.Add(measure); } efProduct.Measurements = measure; var vendor = sqlCon.Vendors.Where(v => v.Name == product.Vendor.VendorName).FirstOrDefault(); if (vendor == null) { vendor = new EntityFramework.Data.Vendors(); vendor.Name = product.Vendor.VendorName; sqlCon.Vendors.Add(vendor); } efProduct.Vendors = vendor; sqlCon.Products.Add(efProduct); try { sqlCon.SaveChanges(); } catch (Exception e) { //Console.WriteLine(e.InnerException.InnerException.Message); } } } } // get the info from the unzipped folder string unzippedFolderName = "Paradise-Sample-Reports"; string unzippedFolderPath = String.Format(@"../../../{0}", unzippedFolderName); foreach (var dir in Directory.GetDirectories(archive.ExtractPath)) { foreach (var file in Directory.GetFiles(dir, "*.xls")) { string currentFolderName = Path.GetFileName(dir); DateTime currentDate = DateTime.Parse(currentFolderName); string fileName = Path.GetFileName(file); using (var db = new ParadiseSupermarketChainEntities()) { var excelComs = new ExcelAccess(file); string supermarketName = null; int rowIndex = 0; excelComs.Open(); excelComs.ReadSheetActionRow("Sales", (row) => { rowIndex++; if (rowIndex <= 2) { if (row.Count == 1 && row[0].ToString().IndexOf("Supermarket") != -1) { // this is the supermarket Name supermarketName = row[0] + ""; } // skip the first 2 rows return; } if (row.Count == 4) { // add a product int productId = int.Parse((row[0] + "")); double quantity = double.Parse((row[1] + "")); decimal unitPrice = decimal.Parse((row[2] + "")); decimal sum = decimal.Parse((row[3] + "")); Sales productSales = new Sales(); productSales.ProductId = productId; productSales.Quantity = quantity; productSales.UnitPrice = unitPrice; productSales.Sum = sum; // find out if the supermarket exists Supermarkets supermarket = db.Supermarkets.Where(s => s.Name == supermarketName).FirstOrDefault(); if (supermarket == null) { supermarket = new Supermarkets(); supermarket.Name = supermarketName; db.Supermarkets.Add(supermarket); } productSales.Supermarkets = supermarket; // find out if the date exists Dates date = db.Dates.Where(d => d.Date == currentDate).FirstOrDefault(); if (date == null) { date = new Dates(); date.Date = currentDate; db.Dates.Add(date); } productSales.Dates = date; db.Sales.Add(productSales); db.SaveChanges(); } else { // the final line: the sum of the products } }); excelComs.Close(); } } } Directory.Delete(archive.ExtractPath, true); }
static void LoadVendor() { ParadiseSupermarketChainEntities sqlCon = new ParadiseSupermarketChainEntities(); using (sqlCon) { MongoWriter mongo = new MongoWriter("Expenses"); XmlReader reader = new XmlReader("E:\\Vendor-Expenses.xml"); while (reader.ReadNextSale()) { while (reader.ReadNextExpenses()) { mongo.WriteExpenses(reader.Vendor, reader.Month, reader.Expenses); var expence = new EntityFramework.Data.Expenses() { Expenses1 = decimal.Parse(reader.Expenses), ExpensesDate = DateTime.Parse(reader.Month) }; int vendorIdd = 1; var VendorId = from vendor in sqlCon.Vendors where vendor.Name == reader.Vendor select new { Id = vendor.Id }; var vendorIdc = sqlCon.Vendors.Where(v => v.Name == reader.Vendor).FirstOrDefault(); expence.VendorId = vendorIdd; sqlCon.Expenses.Add(expence); sqlCon.SaveChanges(); } } } }