/// <summary> /// Imports Products to MySql. /// </summary> public static void ImportToMySql(IEnumerable<Product> products) { var mysqlManager = new MysqlDBManager(); var mySqlContext = mysqlManager.MySqlContext; foreach (var product in products) { var vendorName = product.Vendor.Name; var vendor = mySqlContext.Vendors.FirstOrDefault(v => v.Name == vendorName); if (vendor == null) { vendor = new Vendor() { Name = vendorName, Expenses = new List<Expense>() }; } var mySqlProduct = DeepCopyProduct(product, vendor); mySqlContext.Products.AddOrUpdate(mySqlProduct); mySqlContext.SaveChanges(); } Console.WriteLine("Data imported to MySQL!"); }
/// <summary> /// Exports all vendors and selected data - vendor name, all products (name and income) /// and total expenses per vendor. /// </summary> /// <returns>Collection containing vendors name, all his products, total expenses.</returns> public static IEnumerable<MySqlExportData> GetAllVendors() { var mysqlManager = new MysqlDBManager(); // var mysqlVendors = mysqlManager.MySqlContext.Vendors.Count(); var mysqlVendors = mysqlManager.MySqlContext.Vendors .Select(v => new { VendorName = v.Name, Products = v.Products.Select(p => new { ProductName = p.Name, Income = p.Incomes.Select(s => s.Quantity * (double)p.BuyingPrice).Sum() }), Expense = v.Expenses.Select(e => e.Amount) }) .ToList(); var vendorsData = new List<MySqlExportData>(); mysqlVendors.ForEach(v => { var products = v.Products.ToDictionary(product => product.ProductName, product => product.Income); var vendor = new MySqlExportData() { Expenses = v.Expense.Sum(), VendorName = v.VendorName, Products = products }; vendorsData.Add(vendor); }); Console.WriteLine("MySQL data gathered."); return vendorsData; }
public static List<ExcelReportData> GetReportDate() { // SQLite var sqliteManager = new SQLiteDBManager(); var sqliteProducts = sqliteManager.GetAllProducts(); // System.Console.WriteLine(sqliteProducts.Count()); Console.WriteLine("SQLite data gathered."); // MySQL var mysqlManager = new MysqlDBManager(); // var mysqlVendors = mysqlManager.MySqlContext.Vendors.Count(); var mysqlVendors = mysqlManager.MySqlContext.Vendors .Select(v => new { VendorName = v.Name, Products = v.Products.Select(p => new { ProductName = p.Name, Income = p.Incomes.Select(s => s.Quantity * (double)p.Price).Sum() }), Expense = v.Expenses.Select(e => e.Amount) }) .ToList(); Console.WriteLine("MySQL data gathered."); var excelReportData = new List<ExcelReportData>(); // Aggregation of data mysqlVendors.ForEach(v => { var totalTaxes = 0m; var totalIncome = 0m; v.Products.ToList().ForEach(p => { var tax = 0m; if (sqliteProducts.ContainsKey(p.ProductName)) { tax = (decimal)sqliteProducts[p.ProductName]; } totalTaxes += p.Income == null ? 0 : (decimal)p.Income * tax / 100; totalIncome += p.Income == null ? 0 : (decimal)p.Income; }); var financialResult = totalIncome - totalTaxes - v.Expense.Sum(); var data = new ExcelReportData() { FinancialResult = financialResult, TotalExpense = v.Expense.Sum(), TotalIncome = totalIncome, TotalTaxes = totalTaxes, VendorName = v.VendorName }; excelReportData.Add(data); }); // excelReportDatas.ForEach(Console.WriteLine); Console.WriteLine("Excel report issued."); return excelReportData; }