public bool addAProductToDB(ProductClass product)
{
try
{
SqlConnection connection = dbHelper.getConnection();
SqlCommand command = null;
if (product.GetType() == typeof(OnlineBookStore.BookClass))
{
command = new SqlCommand("INSERT INTO BookTable (name,price,stock,isbn,author,publisher,page,cover_page_picture) values(@name,@price,@stock,@isbn,@author,@publisher,@page,@cover_page_picture)", connection);
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("INSERT INTO MagazineTable (name,price,stock,issue,type,cover_page_picture) values(@name,@price,@stock,@issue,@type,@cover_page_picture)", connection);
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("INSERT INTO MusicCDsTable (name,price,stock,singer,type,picture) values(@name,@price,@stock,@singer,@type,@picture)", connection);
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 addAProductToDB function" + e.Message);
Console.WriteLine("Click {0}", e.Message);
return(false);
}
}
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);
}
}
/**@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 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);
}
}
/**
* musicCDsList created from MusicCDsClass
* this function reads musicCDs items in database
* and add in musicCDsList
* @return musicCDsList
*/
public static List <MusicCDsClass> getAllMusicCDsFromDB()
{
List <MusicCDsClass> musicCDsList = new List <MusicCDsClass>();
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
SqlConnection connection = dbHelper.getConnection();
SqlCommand command = new SqlCommand("SELECT * FROM MusicCDsTable", connection);
SqlDataReader readMusicCDs = command.ExecuteReader();
if (readMusicCDs != null)
{
while (readMusicCDs.Read())
{
MusicCDsClass musicCDs = new MusicCDsClass();
musicCDs.id = readMusicCDs["id"].ToString();
musicCDs.name = readMusicCDs["name"].ToString();
musicCDs.price = Convert.ToDouble(readMusicCDs["price"]);
musicCDs.singer = readMusicCDs["singer"].ToString();
musicCDs.stock = Convert.ToInt32(readMusicCDs["stock"]);
musicCDs.cover_page_picture = readMusicCDs["picture"].ToString();
musicCDsList.Add(musicCDs);
}
}
return(musicCDsList);
}
/**this function read amagazine item in a database
* @param string id
* @return magazine
*/
public static MagazineClass getAMagazineFromDB(string id)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
SqlConnection connection = dbHelper.getConnection();
SqlCommand command = new SqlCommand("SELECT * FROM MagazineTable where id=@id", connection);
command.Parameters.AddWithValue("@id", Convert.ToInt32(id));
SqlDataReader readMagazine = command.ExecuteReader();
MagazineClass magazine = new MagazineClass();
if (readMagazine != null)
{
while (readMagazine.Read())
{
magazine.id = readMagazine["id"].ToString();
magazine.name = readMagazine["name"].ToString();
magazine.price = Convert.ToDouble(readMagazine["price"]);
magazine.stock = Convert.ToInt32(readMagazine["stock"]);
magazine.issue = readMagazine["issue"].ToString();
magazine.cover_page_picture = readMagazine["cover_page_picture"].ToString();
}
}
return(magazine);
}
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);
}
}
/** @brief button delete user
* call userDelete() function in AdminUserClass
* The parameter of the userDelete() function is id.
*/
private void btnDeleteUser_Click(object sender, EventArgs e)
{
SqlConnection connection = dbHelper.getConnection();
bool operationResult = AdminUserClass.userDelete(txtUserId.Text);
if (operationResult)
{
MessageBox.Show("User deleted successfully", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Error!", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool addOrUpdateAUserFromDB(string operationType, string id, string name, string surname, string address, string email, string username, string password, bool isadmin)
{
try
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
SqlConnection connection = dbHelper.getConnection();
SqlCommand command;
if (operationType == "Add")
{
command = new SqlCommand("INSERT INTO CustomerTable (name,surname,address,email,username,password,isadmin) values(@name,@surname,@address,@email,@username,@password,@isadmin)", connection);
}
else if (operationType == "Update")
{
command = new SqlCommand("UPDATE CustomerTable SET name=@name ,surname=@surname,address=@address,email=@email,username=@username,password=@password,isadmin=@isadmin where id=@id", connection);
}
else
{
return(false); //Non supported operation
}
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@surname", surname);
command.Parameters.AddWithValue("@address", address);
command.Parameters.AddWithValue("@email", email);
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@password", password);
command.Parameters.AddWithValue("@isadmin", isadmin ? 1 : 0);
command.Parameters.AddWithValue("@id", id);
int affected = 0;
affected = command.ExecuteNonQuery();
if (affected == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception e)
{
Console.WriteLine(e.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 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);
}
}
/**this function read a musicCDs item in a database
* @param string id
* @return musicCDs
*/
public static MusicCDsClass getAMusicCDsFromDB(string id)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
SqlConnection connection = dbHelper.getConnection();
SqlCommand command = new SqlCommand("SELECT * FROM MusicCDsTable where id=@id", connection);
command.Parameters.AddWithValue("@id", Convert.ToInt32(id));
SqlDataReader readMusicCDs = command.ExecuteReader();
MusicCDsClass musicCDs = new MusicCDsClass();
if (readMusicCDs != null)
{
while (readMusicCDs.Read())
{
musicCDs.id = readMusicCDs["id"].ToString();
musicCDs.name = readMusicCDs["name"].ToString();
musicCDs.price = Convert.ToDouble(readMusicCDs["price"]);
musicCDs.singer = readMusicCDs["singer"].ToString();
musicCDs.stock = Convert.ToInt32(readMusicCDs["stock"]);
musicCDs.cover_page_picture = readMusicCDs["picture"].ToString();
}
}
return(musicCDs);
}
/**
* magazineList created fromMagazineClass
* this function reads magazine items in database
* and add in magazinelist
* @return magazineList
*/
public static List <MagazineClass> getAllMagazineFromDB()
{
List <MagazineClass> magazineList = new List <MagazineClass>();
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
SqlConnection connection = dbHelper.getConnection();
SqlCommand command = new SqlCommand("SELECT * FROM MagazineTable", connection);
SqlDataReader readMagazine = command.ExecuteReader();
if (readMagazine != null)
{
while (readMagazine.Read())
{
MagazineClass magazine = new MagazineClass();
magazine.id = readMagazine["id"].ToString();
magazine.name = readMagazine["name"].ToString();
magazine.price = Convert.ToDouble(readMagazine["price"]);
magazine.stock = Convert.ToInt32(readMagazine["stock"]);
magazine.issue = readMagazine["issue"].ToString();
magazine.cover_page_picture = readMagazine["cover_page_picture"].ToString();
magazineList.Add(magazine);
}
}
return(magazineList);
}