public static void Export() { string MyConString = "SERVER=localhost;DATABASE=supermarketchain;UID=root;"; MySqlConnection connection = new MySqlConnection(MyConString); var context= new SupermarketChainDbContext(); var vendors = context.Vendors.Select(v => v.Name); AddVendorsToMySQL(vendors, connection); AddProductsToMySQL(context, connection); AddExpensesToMySQL(context, connection); var incomes = context.Sales.GroupBy(s => s.Product).Select(p => new { productName=p.Select(s=>s.Product.Name), sum=p.Sum(s=>s.Sum) }); foreach (var income in incomes) { try { string cmdPull = @"SELECT id FROM supermarketchain.products where name='" + income.productName.FirstOrDefault() + "'"; MySqlCommand pull = new MySqlCommand(cmdPull, connection); connection.Open(); MySqlDataReader reader = pull.ExecuteReader(); int? productId = null; while (reader.Read()) { productId = int.Parse(reader.GetString(0)); } connection.Close(); string cmdPush = "INSERT INTO incomes(sum,product_id)VALUES ('" + income.sum + "','" + productId + "')"; MySqlCommand cmd = new MySqlCommand(cmdPush, connection); connection.Open(); int result = cmd.ExecuteNonQuery(); connection.Close(); //lblError.Text = "Data Saved"; } catch (Exception ex) { //Console.Write("not entered"); Console.WriteLine(ex.Message); //lblError.Text = ex.Message; } } }
public static void Export() { bool validDate; var startDate = EnterFirstDate(); var endDate = EnterEndDate(startDate); var context = new SupermarketChainDbContext(); var client = new MongoClient(); var db = client.GetDatabase("SuperMarketChainReports"); var collection = db.GetCollection<BsonDocument>("SalesByProductReports"); var products = context.Sales.Where(s => s.Date <= endDate && s.Date >= startDate) .GroupBy(s => s.ProductId) .Select( p => new { productName = p.Select(s => s.Product.Name), productId = p.Key, vendorName = p.Select(s => s.Product.Vendor.Name), totalQuantity = p.Sum(s => s.Quantity), totalIncome = p.Sum(s => s.Sum) }); if (!products.Any()) { Console.WriteLine("There are no sales during that period {0} - {1}", startDate, endDate); } else { foreach (var product in products) { var document = new BsonDocument { { "product-id", product.productId }, { "product-name", product.productName.First() }, { "vendor-name", product.vendorName.First() }, { "total-quantity-sold", product.totalQuantity }, { "total-incomes", product.totalIncome.ToString() } }; // Upload to the DB collection.InsertOneAsync(document).Wait(); // Export Data SaveToJson(document, product.productId); } } }
private static void AddExpensesToMySQL(SupermarketChainDbContext context, MySqlConnection connection) { var expenses = context.Expenses.Select(e => new { sum = e.Sum, date = e.Date, vendorName = e.Vendor.Name }); foreach (var expense in expenses) { try { string cmdPull = @"SELECT id FROM supermarketchain.vendors where name='" + expense.vendorName + "'"; MySqlCommand pull = new MySqlCommand(cmdPull, connection); connection.Open(); MySqlDataReader reader = pull.ExecuteReader(); int? vendorId = null; while (reader.Read()) { vendorId = int.Parse(reader.GetString(0)); } connection.Close(); string cmdPush = "INSERT INTO expenses(sum,period,vendor_id)VALUES ('" + expense.sum + "','" + expense.date.ToString("yyyy-MM-dd HH:mm:ss") + "','" + vendorId + "')"; MySqlCommand cmd = new MySqlCommand(cmdPush, connection); connection.Open(); int result = cmd.ExecuteNonQuery(); connection.Close(); //lblError.Text = "Data Saved"; } catch (Exception ex) { //Console.Write("not entered"); Console.WriteLine(ex.Message); //lblError.Text = ex.Message; } } }
public static void Main(string[] args) { //// Testing SupermarketChainDbContext var dbContext = new SupermarketChainDbContext(); Console.WriteLine(dbContext.Vendors.First(v => v.Name == "Kamenitza").Name); //Console.WriteLine(dbContext.Vendors.Count()); //// Testing repository //var dbVendors = new Repository<Vendor>(); //dbVendors.Add(new Vendor { Name = "Zagorka" }); //dbVendors.SaveChanges(); //// Testing unit of work var data = new SupermarketChainData(); //Console.WriteLine(data.Vendors.All().FirstOrDefault(v => v.Name == "Zagorka").Name); //Console.WriteLine(data.Supermarkets.All().FirstOrDefault(v => v.Name == "Supermarket “Bourgas – Plaza”").Name); //PDFReportGenerator.GeneratePdfReport(DateTime.ParseExact("20-Jul-2014", "dd-MMM-yyyy", CultureInfo.InvariantCulture), DateTime.ParseExact("23-Jul-2014", "dd-MMM-yyyy", CultureInfo.InvariantCulture), data); //JsonReportsToMongoDB.Export(); //XMLReportSalesByVendor.GenerateReport(DateTime.ParseExact("20-Jul-2014", "dd-MMM-yyyy", CultureInfo.InvariantCulture), DateTime.ParseExact("23-Jul-2014", "dd-MMM-yyyy", CultureInfo.InvariantCulture), dbContext); //ImportExpensesFromXML.Import(); ExportDataToMySQL.Export(); }
public static void GenerateReport(DateTime startDate, DateTime endDate, SupermarketChainDbContext context) { var salesByVendors = context.Sales.Where(s => s.Date >= startDate && s.Date < endDate) .GroupBy(s => new { Vendor = s.Product.Vendor.Name, Date = s.Date}) .Select(s => new { Vendor = s.Key.Vendor, Date = s.Key.Date, TotalSum = s.Sum(a => a.Sum) }); var groupedByDaysAndVendorsSales = salesByVendors.GroupBy(s => s.Vendor).ToList(); var report = new XDocument(); var salesReportXml = new XElement("sales"); foreach (var sale in groupedByDaysAndVendorsSales) { var vendorXML = new XElement("sale", new XAttribute("vendor", sale.Key)); foreach (var row in sale) { var newRowXML = new XElement("summary", new XAttribute("date", row.Date.ToString("dd-MMM-yyyy")), new XAttribute("total-sum", row.TotalSum)); vendorXML.Add(newRowXML); } salesReportXml.Add(vendorXML); } report.Add(salesReportXml); Console.WriteLine(report); report.Save("../../salesReportByVendors.xml"); }
private static void AddProductsToMySQL(SupermarketChainDbContext context, MySqlConnection connection) { var products = context.Products.Select(p => new { name = p.Name, vendorName = p.Vendor.Name }); foreach (var product in products) { try { string cmdPull = @"SELECT id FROM supermarketchain.vendors where name='" + product.vendorName + "'"; MySqlCommand pull = new MySqlCommand(cmdPull, connection); connection.Open(); MySqlDataReader reader = pull.ExecuteReader(); int? vendorId = null; while (reader.Read()) { vendorId = int.Parse(reader.GetString(0)); } connection.Close(); string cmdPush = "INSERT INTO products(name,vendor_id)VALUES ('" + product.name + "','" + vendorId + "')"; MySqlCommand cmd = new MySqlCommand(cmdPush, connection); connection.Open(); int result = cmd.ExecuteNonQuery(); connection.Close(); //lblError.Text = "Data Saved"; } catch (Exception ex) { //Console.Write("not entered"); Console.WriteLine(ex.Message); //lblError.Text = ex.Message; } } }