private static void CreateSQLite() { SQLiteConnection.CreateFile("SQLiteDatabase.sqlite"); SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=SQLiteDatabase.sqlite;Version=3;"); m_dbConnection.Open(); string sql = "CREATE TABLE taxes (productname NVARCHAR(100), tax INT)"; SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection); command.ExecuteNonQuery(); var context = new SupermarketChainContext(); var products = context.Products.Select(p => p.ProductName).Distinct(); var r = new Random(); foreach (var item in products) { string prodName = item; string insert = "insert into taxes (productname, tax) values ('" + prodName + "', '" + r.Next(10, 30) + "')"; SQLiteCommand cmd = new SQLiteCommand(insert, m_dbConnection); cmd.ExecuteNonQuery(); } string taxReader = "select * from taxes order by tax desc"; SQLiteCommand readCommand = new SQLiteCommand(taxReader, m_dbConnection); SQLiteDataReader reader = readCommand.ExecuteReader(); while (reader.Read()) Console.WriteLine("Name: " + reader["productName"] + "\tScore: " + reader["tax"] + "%"); m_dbConnection.Close(); }
static void Main() { DateTime startDate = DateTime.Now; DateTime endDate = DateTime.Now; var context = new SupermarketChainContext(); var productData = context.SaleReports .Where(s => DateTime.Compare(s.SaleTime, startDate) > 0 && DateTime.Compare(s.SaleTime, endDate) < 0) .GroupBy(sl => sl.ProductId).Select(g => new { sum = g.Sum(p => p.Quantity), productID = g.Select(p => p.ProductId), productName = g.Select(p => p.Product.ProductName), price = g.Sum(p => p.Product.Price), vendor = g.Select(p => p.Vendor.VendorName) }); foreach (var item in productData) { decimal decNumber; double sum = Double.Parse(item.price.ToString()); double mult = sum * item.sum; JSONObject JO = new JSONObject(); JO.productID = item.productID.ElementAt(0); JO.productName = item.productName.ElementAt(0); JO.quantitySold = item.sum; JO.income = mult; JO.vendorName = item.vendor.ElementAt(0); var serializer = new JavaScriptSerializer(); var json = serializer.Serialize(JO); File.WriteAllText(@"..\..\..\Json-Reports\" + item.productID.ElementAt(0) + ".json", json); } }
static void Main() { MongoClient client = new MongoClient("mongodb://localhost"); MongoServer server = client.GetServer(); MongoDatabase db = server.GetDatabase("Reports"); var saleReports = db.GetCollection<MongoDBObject>("SalesByProductReports"); var context = new SupermarketChainContext(); var serializer = new JavaScriptSerializer(); string[] filePaths = Directory.GetFiles(@"..\..\..\Json-Reports"); foreach (var item in filePaths) { var des = serializer.Deserialize<SuperMarketChain.JSON.JSONObject>(System.IO.File.ReadAllText(item)); saleReports.Insert(new MongoDBObject() { Id = ObjectId.GenerateNewId().ToString(), ProductID = des.productID, ProductName = des.productName, income = des.income, QuantitySold = des.quantitySold, Vendor = des.vendorName }); } }
public static void GetVendorReport(DateTime firstDate, DateTime secondDate) { var context = new SupermarketChainContext(); var reportData = new Dictionary<string, Dictionary<DateTime, decimal>>(); var sales = context.SaleReports .Where(s => s.SaleTime >= firstDate && s.SaleTime <= secondDate) .Select(s => new { Vendor = s.Vendor, Date = s.SaleTime, Quantity = s.Quantity, Product = s.Product }); foreach (var s in sales) { string vendorName = s.Vendor.VendorName; if (reportData.ContainsKey(vendorName)) { reportData[vendorName].Add(s.Date, (decimal)s.Quantity * s.Product.Price); } else { var dic = new Dictionary<DateTime, Decimal>(); dic.Add(s.Date, (decimal)s.Quantity * s.Product.Price); reportData.Add(vendorName, dic); } } var xmlReport = new XElement("sales"); foreach (var r in reportData) { XElement sale = new XElement("sale", new XAttribute("vendor", r.Key)); for (int i = 0; i < r.Value.Keys.Count; i++) { sale.Add(new XElement("summary", new XAttribute("date", r.Value.Keys.ElementAt(i).ToShortDateString()), new XAttribute("sum", r.Value[r.Value.Keys.ElementAt(i)]))); } xmlReport.Add(sale); } xmlReport.Save("../../../SalesReport.xml"); Console.WriteLine("XML Sales Report Created Check The Root Directory"); }
public static void UploadVendorsToSQL(ICollection<Vendor> vendors, SupermarketChainContext context) { foreach (var vendor in vendors) { var vendorSQL = new SuperMarketChain.Model.Vendor() { VendorName = vendor.VendorName }; if (context.Vendors.Where(v => v.VendorName.Equals(vendorSQL.VendorName)).Count() == 0) { context.Vendors.Add(vendorSQL); Console.WriteLine("Vendor " + vendorSQL.VendorName + " will be uploaded to SQL db." + vendorSQL.VendorName.Count()); } else { Console.WriteLine("Vendor " + vendorSQL.VendorName + " is already in SQL db and will not be uploaded."); } } if (context.ChangeTracker.HasChanges()) { context.SaveChanges(); } }
public static void UploadProductsToSQL(ICollection<Product> products, SupermarketChainContext context) { foreach (var product in products) { var vendorName = GetVendorName("EVGENI-PC", "admin", "1111", product.VendorId); var vendorId = context.Vendors.Where(v => v.VendorName.Equals(vendorName)).Select(v => v.ID).FirstOrDefault(); var productSQL = new SuperMarketChain.Model.Product() { VendorId = vendorId, ProductName = product.ProductName, MeasureID = product.MeasureId, Price = product.Price }; if (context.Products.Where(p => p.ProductName.Equals(productSQL.ProductName)).Count() == 0) { context.Products.Add(productSQL); Console.WriteLine("Product " + productSQL.ProductName + " will be uploaded to SQL db"); } else { Console.WriteLine("Product " + productSQL.ProductName + " is already in SQL db and will not be uploaded."); } } if (context.ChangeTracker.HasChanges()) { context.SaveChanges(); } }
public static void UploadMeasuresToSQL(ICollection<Measure> measures, SupermarketChainContext context) { foreach (var measure in measures) { var measureSQL = new SuperMarketChain.Model.Measure() { ID = measure.Id, MeasureName = measure.MeasureName }; if (context.Measures.Where(m => m.MeasureName.Equals(measureSQL.MeasureName)).Count() == 0) { context.Measures.Add(measureSQL); Console.WriteLine("Measure " + measureSQL.MeasureName + " will be uploaded to SQL db."); } else { Console.WriteLine("Measure " + measureSQL.MeasureName + " is already in SQL db and will not be uploaded."); } } if (context.ChangeTracker.HasChanges()) { context.SaveChanges(); } }
private int productId(string title, SupermarketChainContext cont) { var names = cont.Products.Where(v => v.ProductName == title).Select(v => v.Id); foreach (var item in names) { return item; } return 0; }
private bool productExist(string name, decimal price, int vendId) { var cont = new SupermarketChainContext(); return cont.Products.Any(p => p.ProductName == name && Decimal.Equals(p.Price, price) && p.VendorId == vendId); }
private int vendorId(string title, SupermarketChainContext cont) { var names = cont.Vendors.Where(v => v.VendorName == title).Select(v => v.ID); foreach (var item in names) { return item; } return 0; }
private bool vendorExist(string title, SupermarketChainContext cont) { return cont.Vendors.Any(v => v.VendorName == title); }
private bool saleExist(int productId, double quantity, DateTime time, int vendId) { var cont = new SupermarketChainContext(); return cont.SaleReports.Any(s => s.ProductId == productId && s.Quantity == quantity && s.VendorId == vendId && DateTime.Compare(s.SaleTime, time) == 0); }
private void readExcell(string path) { decimal decNumber; double doubNumber; var context = new SupermarketChainContext(); string title = ""; bool isTitle = false; bool isProd = false; string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";" + @"Extended Properties='Excel 8.0;HDR=Yes;'"; using (OleDbConnection connection = new OleDbConnection(conString)) { connection.Open(); OleDbCommand command = new OleDbCommand("select * from [Sales$]", connection); using (var dbContextTransaction = context.Database.BeginTransaction()) { try { using (OleDbDataReader dr = command.ExecuteReader()) { isTitle = false; isProd = false; int counter = 0; while (dr.Read()) { var row1Col0 = dr[counter]; var row1Col1 = dr[counter + 1]; var row1Col2 = dr[counter + 2]; var row1Col3 = dr[counter + 3]; if (isTitle == false) { title = dr[counter].ToString(); isTitle = true; if (vendorExist(dr[counter].ToString(), context) == false) { context.Vendors.Add(new Vendor { VendorName = title }); context.SaveChanges(); } continue; } else if (dr[counter].ToString() == "Total sum:") { continue; } if (isProd == false) { isProd = true; continue; } Decimal.TryParse(dr[counter + 2].ToString(), out decNumber); int id = vendorId(title, context); if (productExist(dr[counter].ToString(), decNumber, id) == false) { context.Products.Add(new Product { ProductName = dr[counter].ToString(), MeasureID = 1, Price = decNumber, VendorId = id }); } Double.TryParse(dr[counter + 1].ToString(), out doubNumber); if (saleExist(productId(dr[counter].ToString(), context), doubNumber, date, id) == false) { context.SaleReports.Add(new SaleReport { ProductId = productId(dr[counter].ToString(), context), Quantity = doubNumber, SaleTime = date, VendorId = id }); } context.SaveChanges(); } } dbContextTransaction.Commit(); } catch (Exception ex) { dbContextTransaction.Rollback(); } } } }