public int UpdateRewardsPoints(Customer value)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"UPDATE Customer SET `Rewards Points` = @RewardsPoints WHERE `Customer Email` = @CustomerEmail";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@CustomerEmail", value.Email);
cmd.Parameters.AddWithValue("@RewardsPoints", value.RewardsPoints);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
return(value.RewardsPoints);
}
//-1 is returned if something goes wrong; -1 is a recognizable sentinel value across the system
return(-1);
}
public void DeleteItem(int id)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
//defining the string
string stm = @"DELETE FROM Item WHERE `Item ID` = @id";
//making new command
MySqlCommand cmd = new MySqlCommand(stm, con);
//preparing the command and executing it; this deletes the record with the right id
cmd.Parameters.AddWithValue("@id", id);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
}
}
public void SaveEmployee(Employee value)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"INSERT INTO Employee(`Employee First Name`, `Employee Last Name`, `Employee Address`, `Employee SSN`, `Employee Birth Date`, `Username`, `Password`) VALUES(@EmployeeFirstName, @EmployeeLastName, @EmployeeAddress, @EmployeeSSN, @EmployeeBirthDate, @Username, @Password)";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@EmployeeFirstName", value.FirstName);
cmd.Parameters.AddWithValue("@EmployeeLastName", value.LastName);
cmd.Parameters.AddWithValue("@EmployeeAddress", value.Address);
cmd.Parameters.AddWithValue("@EmployeeSSN", value.SSN);
cmd.Parameters.AddWithValue("@EmployeeBirthDate", value.BirthDate);
cmd.Parameters.AddWithValue("@Username", value.Username);
cmd.Parameters.AddWithValue("@Password", value.Password);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
}
}
public void SaveCustomer(string FirstName, string LastName, string Email, string Password, string PhoneNumber, int RewardsPoints)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"INSERT INTO Customer(`Customer Email`, `Customer First Name`, `Customer Last Name`, `Customer Phone Number`, `Rewards Points`, `Password`) VALUES(@CustomerEmail, @CustomerFirstName, @CustomerLastName, @CustomerPhoneNumber, @RewardsPoints, @Password)";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@CustomerEmail", Email);
cmd.Parameters.AddWithValue("@CustomerFirstName", FirstName);
cmd.Parameters.AddWithValue("@CustomerLastName", LastName);
cmd.Parameters.AddWithValue("@CustomerPhoneNumber", PhoneNumber);
cmd.Parameters.AddWithValue("@RewardsPoints", RewardsPoints);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
}
}
public List <Item> GetTransactionItems(int id)
{
List <Item> transItems = new List <Item>();
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = "SELECT * FROM Item WHERE `Transaction ID` = @transID";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@transID", id);
cmd.Prepare();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
transItems.Add(ParseItemFromRdr(rdr));
}
}
db.CloseConnection();
return(transItems);
}
else
{
return(new List <Item>());
}
}
public Item GetItem(int id)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
Item temp = new Item();
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = "SELECT * FROM Item WHERE `Item ID` = @id;";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@id", id);
cmd.Prepare();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
temp = ParseItemFromRdr(rdr);
}
}
db.CloseConnection();
return(temp);
}
else
{
return(new Item());
}
}
public List <Item> GetAllItems()
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = "SELECT * FROM Item";
MySqlCommand cmd = new MySqlCommand(stm, con);
List <Item> items = new List <Item>();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
items.Add(ParseItemFromRdr(rdr));
}
}
db.CloseConnection();
return(items);
}
else
{
//if something goes wrong, we just return an empty list
return(new List <Item>());
}
}
public void AddItem(Item value)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"INSERT INTO Item (`Item Name`, `Item Price`, `Item Year`, `Item Cost`, `Is Purchased`, `Card Condition`, `Card Sport`, `Card Team`, `Memorabilia Description`) VALUES(@ItemName, @ItemPrice, @ItemYear, @ItemCost, @IsPurchased, @CardCondition, @CardSport, @CardTeam, @MemorabiliaDescription)";
//making a command with the connection
MySqlCommand cmd = new MySqlCommand(stm, con);
//adding sample data
cmd.Parameters.AddWithValue("@ItemName", value.ItemName);
cmd.Parameters.AddWithValue("@ItemPrice", value.ItemPrice);
cmd.Parameters.AddWithValue("@ItemYear", value.ItemYear);
cmd.Parameters.AddWithValue("@ItemCost", value.ItemCost);
cmd.Parameters.AddWithValue("@IsPurchased", "false");
cmd.Parameters.AddWithValue("@CardCondition", value.ItemCardCondition);
cmd.Parameters.AddWithValue("@CardSport", value.ItemCardSport);
cmd.Parameters.AddWithValue("@CardTeam", value.ItemCardTeam);
cmd.Parameters.AddWithValue("@MemorabiliaDescription", value.ItemMemorabiliaDescription);
//preparing the command string before touching the database
cmd.Prepare();
//actually executing the command to insert a new item
cmd.ExecuteNonQuery();
db.CloseConnection();
}
}
public void UpdateItem(Item updatedItem)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
//defining the string
string stm = @"UPDATE Item SET `Item Name` = @ItemName, `Item Price` = @ItemPrice, `Item Year` = @ItemYear, `Card Condition` = @CardCondition, `Card Sport` = @CardSport, `Card Team` = @CardTeam, `Memorabilia Description` = @MemorabiliaDescription WHERE `Item ID` = @ItemID";
//making new command
MySqlCommand cmd = new MySqlCommand(stm, con);
//preparing the command and executing it
cmd.Parameters.AddWithValue("@ItemID", updatedItem.ItemID);
cmd.Parameters.AddWithValue("@ItemName", updatedItem.ItemName);
cmd.Parameters.AddWithValue("@ItemPrice", updatedItem.ItemPrice);
cmd.Parameters.AddWithValue("@ItemYear", updatedItem.ItemYear);
cmd.Parameters.AddWithValue("@CardCondition", updatedItem.ItemCardCondition);
cmd.Parameters.AddWithValue("@CardSport", updatedItem.ItemCardSport);
cmd.Parameters.AddWithValue("@CardTeam", updatedItem.ItemCardTeam);
cmd.Parameters.AddWithValue("@MemorabiliaDescription", updatedItem.ItemMemorabiliaDescription);
cmd.Prepare();
cmd.ExecuteNonQuery();
db.CloseConnection();
}
//if the open fails, the api should just do nothing; hopefully this helps prevent some crashes
}
public List <Transaction> GetAllTransactions()
{
//making an item selector up front
IGetTransactionItems readObj = new ReadItemData();
//making an empty list of transactions
List <Transaction> transactions = new List <Transaction>();
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = "SELECT * FROM Transact";
MySqlCommand cmd = new MySqlCommand(stm, con);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Transaction newTrans = new Transaction()
{
TransactionID = rdr.GetInt32(0), TransactionDate = DateTime.Parse(rdr.GetString(1)), AmtDiscounted = rdr.GetDouble(2), PaymentType = rdr.GetString(3), EmployeeID = rdr.GetInt32(4), CustomerEmail = rdr.GetString(5)
};
List <Item> transItems = readObj.GetTransactionItems(newTrans.TransactionID);
newTrans.ItemIDs = GetItemIDs(transItems);
newTrans.Subtotal = GetSubtotal(transItems);
transactions.Add(newTrans);
}
}
db.CloseConnection();
return(transactions);
}
return(transactions);
}
public void SaveTransaction(string PaymentType, int EmployeeID, string CustomerEmail, List <int> ItemIDs, double AmtDiscounted, DateTime TransactionDate)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"INSERT INTO Transact(`Transaction Date`, `Amount Discounted`, `Payment Type`, `Employee ID`, `Customer Email`) VALUES(@TransactionDate, @AmountDiscounted, @PaymentType, @EmployeeID, @CustomerEmail)";
MySqlCommand cmd = new MySqlCommand(stm, con);
//inserting the transaction
cmd.Parameters.AddWithValue("@TransactionDate", TransactionDate);
cmd.Parameters.AddWithValue("@AmountDiscounted", AmtDiscounted);
cmd.Parameters.AddWithValue("@PaymentType", PaymentType);
cmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
cmd.Parameters.AddWithValue("@CustomerEmail", CustomerEmail);
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"SELECT `Transaction ID` FROM Transact ORDER BY `Transaction Date` DESC LIMIT 1";
cmd.Prepare();
var transactionID = cmd.ExecuteScalar();
foreach (int itemId in ItemIDs)
{
cmd.CommandText = @"UPDATE Item SET `Transaction ID` = @TransactionID, `Is Purchased` = 'true' WHERE `Item ID` = @ItemID";
cmd.Parameters.AddWithValue("@TransactionID", transactionID);
cmd.Parameters.AddWithValue("@ItemID", itemId);
cmd.Prepare();
cmd.ExecuteNonQuery();
}
db.CloseConnection();
}
}
//the idea behind logging in a customer is to return their rewards points: -1 means the customer wasn't found, anything else means they're signed in
public int FindCustomer(Customer value)
{
//connecting to and opening the database
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
int temp = -1;
//if the open succeeded, we proceed with the sql commands
MySqlConnection con = db.GetCon();
string stm = @"SELECT `Customer Email`, `Rewards Points` FROM Customer WHERE `Customer Email` = @email AND `Password` = @password;";
MySqlCommand cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@email", value.Email);
cmd.Parameters.AddWithValue("@password", value.Password);
cmd.Prepare();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
//try to return the rewards points from the customer
try {
temp = Convert.ToInt32(rdr[1]);
} catch {
db.CloseConnection();
//if the customer doesn't exist, this will throw an exception; if the customer doesn't exist, return -1 as rewards points
return(temp);
}
}
}
db.CloseConnection();
return(temp);
}
//this sentinel value will indicate that the connection failed, which might be helpful on the front-end
return(-2);
}