public List <ReportUserClass> getMostRichCustomers() { try { DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN SqlConnection connection = dbHelper.getConnection(); SqlCommand command = new SqlCommand("SELECT S.customerid,C.name,C.surname,SUM(S.paymentamount) AS totalpayment FROM ShoppingCartTable S, CustomerTable C WHERE S.customerid = C.id GROUP BY S.customerid, C.name, C.surname Order by totalpayment desc", connection); List <ReportUserClass> list = new List <ReportUserClass>(); SqlDataReader read = command.ExecuteReader(); if (read != null) { while (read.Read()) { ReportUserClass item = new ReportUserClass(); item.customerID = read["customerid"].ToString(); item.name = read["name"].ToString(); item.surName = read["surname"].ToString(); item.totalpayment = Convert.ToDouble(read["totalpayment"]); list.Add(item); } } return(list); } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at getMostRichCustomers function" + e.Message); Console.WriteLine("Click {0}", e.Message); return(null); } }
public bool confirmAUser(string id) { try { SqlConnection connection = dbHelper.getConnection(); SqlCommand command = new SqlCommand("UPDATE CustomerTable SET isconfirmed='True' WHERE id=@id", connection); command.Parameters.AddWithValue("@id", id); int affected = 0; affected = command.ExecuteNonQuery(); if (affected == 0) { return(false); } else { return(true); } } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at confirmAUser function" + e.Message); Console.WriteLine("Click {0}", e.Message); return(false); } }
public List <ItemToPurchaseClass> getMostBoughtItems(string itemtype) { try { DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN SqlConnection connection = dbHelper.getConnection(); SqlCommand command = new SqlCommand("SELECT S.itemid,S.picture,S.name,S.paymentamount,SUM(S.quantity) AS totalquantity FROM ShoppingCartTable S WHERE S.itemtype = @itemtype GROUP BY S.itemid, S.picture, S.name, S.paymentamount Order by totalquantity desc", connection); command.Parameters.AddWithValue("@itemtype", itemtype); List <ItemToPurchaseClass> list = new List <ItemToPurchaseClass>(); SqlDataReader readShoppingCart = command.ExecuteReader(); if (readShoppingCart != null) { while (readShoppingCart.Read()) { ItemToPurchaseClass item = new ItemToPurchaseClass(); ProductClass book = new BookClass(); item.product = book; item.product.id = readShoppingCart["itemid"].ToString(); item.quantity = Convert.ToInt32(readShoppingCart["totalquantity"]); item.product.price = Convert.ToDouble(readShoppingCart["paymentamount"]); item.product.name = readShoppingCart["name"].ToString(); item.product.cover_page_picture = readShoppingCart["picture"].ToString(); list.Add(item); } } return(list); } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at getMostBoughtItems function" + e.Message); Console.WriteLine("Click {0}", e.Message); return(null); } }
/**@brief getAllUserPurchases() function * * @param int userID */ public List <ItemToPurchaseClass> getAllUserPurchases(int userID) { try { DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN SqlConnection connection = dbHelper.getConnection(); SqlCommand command = new SqlCommand("SELECT * FROM ShoppingCartTable WHERE customerid=@id", connection); command.Parameters.AddWithValue("@id", userID); List <ItemToPurchaseClass> list = new List <ItemToPurchaseClass>(); SqlDataReader readShoppingCart = command.ExecuteReader(); if (readShoppingCart != null) { while (readShoppingCart.Read()) { ItemToPurchaseClass item = new ItemToPurchaseClass(); ProductClass book = new BookClass(); item.product = book; item.product.id = readShoppingCart["itemid"].ToString(); item.quantity = Convert.ToInt32(readShoppingCart["quantity"]); item.product.price = Convert.ToDouble(readShoppingCart["paymentamount"]); item.product.name = readShoppingCart["name"].ToString(); item.product.cover_page_picture = readShoppingCart["picture"].ToString(); list.Add(item); } } return(list); } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at getAllUserPurchases function" + e.Message); Console.WriteLine("Click {0}", e.Message); return(null); } }
public bool updateAProductAtDB(ProductClass product) { try { SqlConnection connection = dbHelper.getConnection(); SqlCommand command = null; if (product.GetType() == typeof(OnlineBookStore.BookClass)) { command = new SqlCommand("UPDATE BookTable SET name=@name,price=@price,stock=@stock,isbn=@isbn,author=@author,publisher=@publisher,page=@page,cover_page_picture=@cover_page_picture WHERE id=@id", connection); command.Parameters.AddWithValue("@id", ((BookClass)product).id); command.Parameters.AddWithValue("@name", ((BookClass)product).name); command.Parameters.AddWithValue("@price", ((BookClass)product).price); command.Parameters.AddWithValue("@stock", ((BookClass)product).stock); command.Parameters.AddWithValue("@isbn", ((BookClass)product).isbn); command.Parameters.AddWithValue("@author", ((BookClass)product).author); command.Parameters.AddWithValue("@publisher", ((BookClass)product).publisher); command.Parameters.AddWithValue("@page", ((BookClass)product).page); command.Parameters.AddWithValue("@cover_page_picture", ((BookClass)product).cover_page_picture); } else if (product.GetType() == typeof(OnlineBookStore.MagazineClass)) { command = new SqlCommand("UPDATE MagazineTable SET name=@name,price=@price,stock=@stock,issue=@issue,type=@type,cover_page_picture=@cover_page_picture WHERE id=@id", connection); command.Parameters.AddWithValue("@id", ((MagazineClass)product).id); command.Parameters.AddWithValue("@name", ((MagazineClass)product).name); command.Parameters.AddWithValue("@price", ((MagazineClass)product).price); command.Parameters.AddWithValue("@stock", ((MagazineClass)product).stock); command.Parameters.AddWithValue("@issue", ((MagazineClass)product).issue); command.Parameters.AddWithValue("@type", ((MagazineClass)product).magazineType); command.Parameters.AddWithValue("@cover_page_picture", ((MagazineClass)product).cover_page_picture); } else if (product.GetType() == typeof(OnlineBookStore.MusicCDsClass)) { command = new SqlCommand("UPDATE MusicCDsTable SET name=@name,price=@price,stock=@stock,singer=@singer,type=@type,picture=@picture WHERE id=@id", connection); command.Parameters.AddWithValue("@id", ((MusicCDsClass)product).id); command.Parameters.AddWithValue("@name", ((MusicCDsClass)product).name); command.Parameters.AddWithValue("@price", ((MusicCDsClass)product).price); command.Parameters.AddWithValue("@stock", ((MusicCDsClass)product).stock); command.Parameters.AddWithValue("@singer", ((MusicCDsClass)product).singer); command.Parameters.AddWithValue("@type", ((MusicCDsClass)product).type); command.Parameters.AddWithValue("@picture", ((MusicCDsClass)product).cover_page_picture); } int affected = command.ExecuteNonQuery(); if (affected > 0) { return(true); } return(false); } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at updateAProductAtDB function" + e.Message); Console.WriteLine("Click {0}", e.Message); return(false); } }
public void shoppingCartCancelOrder(string name) { try { DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN SqlConnection connection = dbHelper.getConnection(); SqlCommand command = new SqlCommand("DELETE FROM ShoppingCartTable WHERE name=@name)", connection); command.Parameters.AddWithValue("@name", name); } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at shoppingCartCancelOrder function" + e.Message); Console.WriteLine("Click {0}", e.Message); } }
public override void update(string communicationType, UserClass user, string name, double unitPriceValue) { //string message = "{0} is sent to UserName " + user.userName + " UserEmail: " + user.email + " for product: " + name + " with price: " + unitPriceValue + "Time: " + DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString(); if (communicationType == "email") { string message = "Email is sent to UserName " + user.userName + " UserEmail: " + user.email + " for product: " + name + " with price: " + unitPriceValue + " Time: " + DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString(); FileWriterClass.WriteFile(AppConstants.EMAIL_LOG_FILE_LOCATION, message); Console.WriteLine("Email Log: {0}", message); } else if (communicationType == "sms") { string message = "SMS is sent to UserName " + user.userName + " UserEmail: " + user.email + " for product: " + name + " with price: " + unitPriceValue + " Time: " + DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString(); FileWriterClass.WriteFile(AppConstants.SMS_LOG_FILE_LOCATION, message); Console.WriteLine("SMS Log: {0}", message); } }
public bool PreFilterMessage(ref Message m) { if (m.Msg == 0x201 || m.Msg == 0x203) { // Trap left click + double-click string name = "Unknown"; Control ctl = Control.FromHandle(m.HWnd); if (ctl != null) { name = ctl.Name; } //Point pos = new Point(m.LParam.ToInt32()); string message = "Username " + user.userName + " Clicked object " + name + " at " + DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString(); FileWriterClass.WriteFile(@"Logs\" + user.userName + "_clicklogs.txt", message); Console.WriteLine("Click {0}", message); } return(false); }
/** * * @param string customerID * @param PaymentType paymentType */ public void shoppingCartPlaceOrder(string customerID, PaymentType paymentType) { try { DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN SqlConnection connection = dbHelper.getConnection(); SqlCommand command = new SqlCommand("INSERT INTO ShoppingCartTable (customerid,itemid,name,itemtype,quantity,paymentamount,paymenttype,picture) values(@customerid,@itemid,@name,@itemtype,@quantity,@paymentamount,@paymenttype,@picture)", connection); foreach (var item in ShoppingCartClass.itemsToPurchase) { command.Parameters.Clear(); string t = ""; if (item.product is OnlineBookStore.BookClass) { t = "Book"; } else if (item.product is OnlineBookStore.MagazineClass) { t = "Magazine"; } else if (item.product is OnlineBookStore.MusicCDsClass) { t = "MusicCDs"; } Console.WriteLine("Type i budur: " + t); command.Parameters.AddWithValue("@customerid", customerID); command.Parameters.AddWithValue("@itemid", item.product.id); command.Parameters.AddWithValue("@name", item.product.name); command.Parameters.AddWithValue("@itemtype", t); command.Parameters.AddWithValue("@quantity", item.quantity); command.Parameters.AddWithValue("@paymentamount", item.product.price); command.Parameters.AddWithValue("@paymenttype", paymentType); command.Parameters.AddWithValue("@picture", item.product.cover_page_picture); command.ExecuteNonQuery(); } } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at shoppingCartPlaceOrder function, customerID: " + customerID + " " + e.Message); Console.WriteLine("Click {0}", e.Message); } }
public DataTable getAllItemsForAClassFromATableByItsType(Type type) { try { SqlConnection connection = dbHelper.getConnection(); DataTable dt = new DataTable(); SqlDataAdapter command = null; if (type == typeof(OnlineBookStore.UserClass)) { command = new SqlDataAdapter("SELECT * FROM CustomerTable", connection); } else if (type == typeof(OnlineBookStore.BookClass)) { command = new SqlDataAdapter("SELECT * FROM BookTable", connection); } else if (type == typeof(OnlineBookStore.MagazineClass)) { command = new SqlDataAdapter("SELECT * FROM MagazineTable", connection); } else if (type == typeof(OnlineBookStore.MusicCDsClass)) { command = new SqlDataAdapter("SELECT * FROM MusicCDsTable", connection); } else { return(null); } { command.Fill(dt); } return(dt); } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at getAllItemsForAClassFromATableByItsType function" + e.Message); Console.WriteLine("Click {0}", e.Message); return(null); } }
public bool deleteAnItemObjectFromDBByID(Type type, string id) { try { }catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at deleteAnItemObjectFromDBByID function" + e.Message); Console.WriteLine("Click {0}", e.Message); } SqlConnection connection = dbHelper.getConnection(); SqlCommand deleteCommand = null; if (type == typeof(OnlineBookStore.UserClass)) { deleteCommand = new SqlCommand("delete from CustomerTable where id=@id", connection); } if (type == typeof(OnlineBookStore.BookClass)) { deleteCommand = new SqlCommand("delete from BookTable where id=@id", connection); } else if (type == typeof(OnlineBookStore.MagazineClass)) { deleteCommand = new SqlCommand("delete from MagazineTable where id=@id", connection); } else if (type == typeof(OnlineBookStore.MusicCDsClass)) { deleteCommand = new SqlCommand("delete from MusicCDsTable where id=@id", connection); } deleteCommand.Parameters.AddWithValue("@id", id); int affected = deleteCommand.ExecuteNonQuery(); if (affected > 0) { return(true); } return(false); }
public List <BookClass> getAllBooksFromDB() { try { List <BookClass> books = new List <BookClass>(); DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN SqlConnection connection = dbHelper.getConnection(); SqlCommand command = new SqlCommand("SELECT * FROM BookTable", connection); SqlDataReader readBooks = command.ExecuteReader(); if (readBooks != null) { while (readBooks.Read()) { BookClass book = new BookClass(); book.id = readBooks["id"].ToString(); book.name = readBooks["name"].ToString(); book.price = Convert.ToDouble(readBooks["price"]); book.stock = Convert.ToInt32(readBooks["stock"]); book.author = readBooks["author"].ToString(); book.publisher = readBooks["publisher"].ToString(); book.isbn = readBooks["isbn"].ToString(); book.page = Convert.ToInt32(readBooks["page"]); book.cover_page_picture = readBooks["cover_page_picture"].ToString(); books.Add(book); } } return(books); } catch (Exception e) { FileWriterClass.WriteFile(AppConstants.EXCEPTION_LOG_FILE_LOCATION, "Exception at getAllBooksFromDB function" + e.Message); Console.WriteLine("Click {0}", e.Message); return(null); } }