/// <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;
}