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