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(BookHouseOOP.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(BookHouseOOP.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(BookHouseOOP.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 BookHouseOOP.BookClass)
{
t = "Book";
}
else if (item.product is BookHouseOOP.MagazineClass)
{
t = "Magazine";
}
else if (item.product is BookHouseOOP.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(BookHouseOOP.UserClass))
{
command = new SqlDataAdapter("SELECT * FROM CustomerTable", connection);
}
else if (type == typeof(BookHouseOOP.BookClass))
{
command = new SqlDataAdapter("SELECT * FROM BookTable", connection);
}
else if (type == typeof(BookHouseOOP.MagazineClass))
{
command = new SqlDataAdapter("SELECT * FROM MagazineTable", connection);
}
else if (type == typeof(BookHouseOOP.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(BookHouseOOP.UserClass))
{
deleteCommand = new SqlCommand("delete from CustomerTable where id=@id", connection);
}
if (type == typeof(BookHouseOOP.BookClass))
{
deleteCommand = new SqlCommand("delete from BookTable where id=@id", connection);
}
else if (type == typeof(BookHouseOOP.MagazineClass))
{
deleteCommand = new SqlCommand("delete from MagazineTable where id=@id", connection);
}
else if (type == typeof(BookHouseOOP.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);
}
}
