public static void InsertProductReports(ICollection<ProductReport> productReports)
{
using (ProductReportsEntities sqLiteContext = new ProductReportsEntities())
{
foreach (var productReport in productReports)
{
SupermarketSQLite.Model.ProductReport report = new SupermarketSQLite.Model.ProductReport()
{
ProductId = productReport.ProductId,
ProductName = productReport.ProductName,
TotalIncomes = productReport.TotalIncomes,
TotalQuantitySold = productReport.TotalQuantitySold,
VendorName = productReport.VendorName
};
sqLiteContext.ProductReports.Add(report);
}
sqLiteContext.SaveChanges();
}
}
public static void WriteDataFromSQLite()
{
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@" Data Source=../../vendors.xls; Persist Security Info=false; Extended Properties=Excel 8.0;";
var conn1 = new OleDbConnection(connectionString);
conn1.Open();
using (conn1)
{
string sql = "CREATE TABLE [Product Reports] (Vendor string, Income decimal, Expenses decimal, Taxes decimal, [Financial Result] decimal)";
var cmd = new OleDbCommand(sql, conn1);
cmd.ExecuteNonQuery();
}
using (ProductReportsEntities sqLiteContext = new ProductReportsEntities())
{
using (SupermarketReportsEntities db = new SupermarketReportsEntities())
{
DateTime currentDate = DateTime.Now;
var reports = db.SalesReports
.Where(d => d.ReportDate.Year == currentDate.Year && d.ReportDate.Month == currentDate.Month)
.GroupBy(v => v.Product.Vendors_VendorId).ToList();
foreach (var item in reports)
{
string vendorName = item.First().Product.Vendor.VendorName;
decimal income = sqLiteContext.ProductReports.Where(v => v.VendorName == vendorName).Sum(s => s.TotalIncomes);
int vendorID = item.First().Product.Vendor.VendorId;
var expencesList = db.Expenses
.Where(v => v.VendorId == vendorID && v.Date.Year == currentDate.Year && v.Date.Month == currentDate.Month)
.GroupBy(vi => vi.VendorId).FirstOrDefault();
if (expencesList == null)
{
continue;
}
decimal expences = expencesList.Sum(e => e.Expenses);
var taxes = sqLiteContext.Taxes;
decimal totalTaxes = 0;
foreach (var tax in taxes)
{
//string taxName = tax.Product_Name.Replace("”", "\"").Replace("“", "\"").Replace("’", "'");
var vId = sqLiteContext.ProductReports.Where(p => p.VendorName.Replace("”", "\"")
.Replace("“", "\"").Replace("’", "'") == vendorName).FirstOrDefault();
if (vId == null)
{
continue;
}
totalTaxes += vId.TotalIncomes;
}
var conn = new OleDbConnection(connectionString);
conn.Open();
using (conn)
{
string sql = "INSERT INTO [Product_Reports$](Vendor, Income, Expenses, Taxes, [Financial Result]) VALUES (@VendorName, @Income, @Expences, @Taxes, @FinancialResult)";
var cmd = new OleDbCommand(sql, conn);
cmd.Parameters.AddWithValue("@VendorName", vendorName);
cmd.Parameters.AddWithValue("@Income", income);
cmd.Parameters.AddWithValue("@Expences", expences);
cmd.Parameters.AddWithValue("@Taxes", totalTaxes);
cmd.Parameters.AddWithValue("@FinancialResult", income - expences - totalTaxes);
cmd.ExecuteNonQuery();
}
//var taxes = sqLiteContext.Taxes;
//foreach (var tax in taxes)
//{
// string taxName = tax.Product_Name;
// var vId = sqLiteContext.ProductReports.Where(p => p.ProductName.Replace("\\", "") == taxName).FirstOrDefault();
// if (vId == null)
// {
// continue;
// }
// Console.WriteLine("{0} {1}", taxName);
//}
}
}
}
}