public static int InsertInSupermarketTable(string supermarketName) { SupermarketReportsEntities db = new SupermarketReportsEntities(); using (db) { var supermarket = new SupermarketMSSql.Model.Supermarket() { Name = supermarketName }; bool doesExist = db.Supermarkets.Any(s => s.Name == supermarket.Name); //bool doesExist = db.Supermarkets.Select(s => s.Name == supermarket.Name).ToList().Count > 0; if (!doesExist) { db.Supermarkets.Add(supermarket); db.SaveChanges(); } return(db.Supermarkets .Where(s => s.Name == supermarket.Name) .First() .SupermarketId); } }
public static int InsertExpenses(string vendorName, DateTime date, decimal expenses) { int vendorID; SupermarketReportsEntities db = new SupermarketReportsEntities(); using (db) { vendorID = db.Vendors .Where(v => v.VendorName == vendorName) .First() .VendorId; Expens newExpense = new Expens { VendorId = vendorID, Date = date, Expenses = expenses }; db.Expenses.Add(newExpense); db.SaveChanges(); } return(vendorID); }
public static void PrintToXML() { using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities()) { var doc = new XDocument(); var sales = new XElement("sales"); var salesReports = msSqlDb.SalesReports .Include("Product") .Include("Supermarket") .Include("Vendor") .GroupBy(x => x.Product.Vendor.VendorName) .ToList(); foreach (var report in salesReports) { var sale = new XElement("sale", new XAttribute("vendor", report.First().Product.Vendor.VendorName)); foreach (var item in report) { sale.Add(new XElement("summary", new XAttribute("date", item.ReportDate.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture)), new XAttribute("total-sum", item.Sum))); } sales.Add(sale); } doc.Add(sales); doc.Save("../../SalesReports/Sales-by-Vendors-report.xml"); } }
public static void CreateSalesReportPdfFile(string location) { using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities()) { ICollection <SupermarketMSSql.Model.SalesReport> result = msSqlDb.SalesReports.Include("Product").Include("Supermarket").ToList(); FillPdfReportFile(result, location); } }
public static int GetProductIdByName(string mysqlProductName) { SupermarketReportsEntities db = new SupermarketReportsEntities(); using (db) { var product = db.Products.Where(p => p.ProductName == mysqlProductName).First(); return(product.Productid); } }
public static void MigrateDataFromMySqlToMSSql() { using (SupermarketMySqlEntities mySqlDb = new SupermarketMySqlEntities()) { using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities()) { var vendors = mySqlDb.Vendors; foreach (var vendor in vendors) { SupermarketMSSql.Model.Vendor newVendor = new SupermarketMSSql.Model.Vendor { VendorName = vendor.VendorName }; msSqlDb.Vendors.Add(newVendor); } msSqlDb.SaveChanges(); var measures = mySqlDb.Measures; foreach (var measure in measures) { SupermarketMSSql.Model.Measure newMeasure = new SupermarketMSSql.Model.Measure { MeasureName = measure.MeasureName }; msSqlDb.Measures.Add(newMeasure); } msSqlDb.SaveChanges(); var products = mySqlDb.Products; foreach (var product in products) { var vendor = msSqlDb.Vendors.Where(v => v.VendorName == product.Vendor.VendorName).First(); var measure = msSqlDb.Measures.Where(m => m.MeasureName == product.Measure.MeasureName).First(); SupermarketMSSql.Model.Product newProduct = new SupermarketMSSql.Model.Product { ProductName = product.ProductName, BasePrice = product.BasePrice, Measures_MeasureId = measure.MeasureId, Vendors_VendorId = vendor.VendorId }; msSqlDb.Products.Add(newProduct); } msSqlDb.SaveChanges(); } } }
public static void InsertSalesReport(int productID, int quantity, decimal unitPrice, decimal sum, DateTime reportDate, int supermarketID) { SupermarketReportsEntities db = new SupermarketReportsEntities(); using (db) { SalesReport report = new SalesReport() { ProductId = productID, Quantity = quantity, ReportDate = reportDate, SupermarketId = supermarketID, UnitPrice = unitPrice, Sum = sum }; db.SalesReports.Add(report); db.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); //} } } } }