/**
* add and update operation in the database
* @param string operationType
* @param string id
* @param string name
* @param string surName
* @param string address
* @param string email
* @param string userName
* @param string password
* @param bool isAdmin
*/
public static bool userOperations(string operationType, string id, string name, string surname, string address, string email, string username, string password, bool isadmin)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance;
bool affected = dbHelper.addOrUpdateAUserFromDB(operationType, id, name, surname, address, email, username, password, isadmin);
return(affected);
}
/**@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);
}
}
/**
* Confirms user's registration
*/
public static bool confirmUser(string userID)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance;
bool affected = dbHelper.confirmAUser(userID);
return(affected);
}
/**
* static userDelete() function
* @param userID
*/
public static bool userDelete(string userID)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance;
bool affected = dbHelper.deleteAnItemObjectFromDBByID(typeof(BookHouseOOP.UserClass), userID);
return(affected);
}
/**
* Deletes Book, Magazine, MusicCD is gathered under 1 function as they all derived from ProductClass
*/
public bool deleteProduct(Type type, string id)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance;
bool affected = dbHelper.deleteAnItemObjectFromDBByID(type, id);
return(affected);
}
/**
* Updates Book, Magazine, MusicCD is gathered under 1 function as they all derived from ProductClass
*/
public bool updateProduct(ProductClass product)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance;
bool affected = dbHelper.updateAProductAtDB(product);
return(affected);
}
} //singleton pattern
/**
* Addnew Book, Magazine, MusicCD is gathered under 1 function as they all derived from ProductClass
*/
public bool addNewProduct(ProductClass product)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance;
bool affected = dbHelper.addAProductToDB(product);
return(affected);
}
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);
}
/** @brief cancelorder() function
* @param string string name
* create a new object email from EmailClass
* calls shoppingCartCancelOrder() and sendEmail() function
*/
public static void cancelOrder(string name, double unitPriceValue)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
dbHelper.shoppingCartCancelOrder(name);
notifySms(name, unitPriceValue);
}
/**this function read book item in database
* and assign bookclass parameter(id,name,price,stock,author,publisher,isbn,page,cover_page_picture)
* @param string id
* @return book
*/
public static BookClass getaBooksFromDBByID(string id)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
BookClass book = new BookClass();
book = dbHelper.getABookFromDBByID(id);
return(book);
}
/**
* books list created from BookClass
* this function reads book items in database
* and add in books
* @return book
*/
public static List <BookClass> getAllBooksFromDB()
{
List <BookClass> books = new List <BookClass>();
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
books = dbHelper.getAllBooksFromDB();
return(books);
}
/** @brief place order() function
* @param string customerID
* @param PaymentType paymentType
* calls shoppingCartPlaceOrder() function
*/
public static void placeOrder(string customerID, PaymentType paymentType, double unitPriceValue)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
dbHelper.shoppingCartPlaceOrder(customerID, paymentType);
sendInvoiceByEmail(unitPriceValue);
//Clear the list
itemsToPurchase.Clear();
}
private void pbCancelOrder_Click(object sender, EventArgs e)
{
ShoppingCartClass.cancelOrder(name, unitPriceValue);
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
DialogResult dialogResult = MessageBox.Show("Want to cancel your item? ", "Information", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (dialogResult == DialogResult.Yes)
{
dbHelper.removeSelectedItemsFromShoppingCart(name);
System.Windows.Forms.Form f = System.Windows.Forms.Application.OpenForms["BookShopForm"];
((BookShopForm)f).populateMyAccountView();
}
}
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 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 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);
}
}
/** dbHelper created from DatabaseHelperClass
* A list of list in the ItemToPurchaseClass' getAllUserPurchases() function is created.
* myAccountUserControls array is created from the MyAccountUserControl
* The size of myAccountUserControls is up to the length of list
*/
public static MyAccountUserControl[] printCustomerPurchases(string customerID)
{
DatabaseHelperClass dbHelper = DatabaseHelperClass.Instance; //SINGLETON PATTERN
List <ItemToPurchaseClass> list = dbHelper.getAllUserPurchases(Convert.ToInt32(customerID));
MyAccountUserControl[] myAccountUserControls = new MyAccountUserControl[list.Count];
int i = 0;
Console.WriteLine("Customer Purchases are:");
foreach (ItemToPurchaseClass item in list)
{
myAccountUserControls[i] = new MyAccountUserControl();
myAccountUserControls[i].id = item.product.id;
myAccountUserControls[i].name = item.product.name;
myAccountUserControls[i].quantity = item.quantity;
myAccountUserControls[i].unitPriceValue = item.product.price;
myAccountUserControls[i].picture = item.product.cover_page_picture;
Console.WriteLine("Id: " + item.product.id + " Name: " + item.product.name + " Quantity: " + item.quantity + " Unit Price Value: " + item.product.price + " Item Type: " + item.product.GetType());
i++;
}
return(myAccountUserControls);
}
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);
}
