/// <summary>
/// Update Store Member Information
/// </summary>
/// <param name="storeMember">Store Member Object</param>
/// <returns>true if storeMember got successfully updated else false</returns>
public Boolean UpdateStoreMember(StoreMember storeMember)
{
string sqlStatement = "UPDATE dbo.StoreMembers set FName = @FName, LName = @LName, Phone = @Phone, Address1 = @Address1, Address2 = @Address2, City=@City, State=@State, ZipCode=@ZipCode, DOB=@DOB, Sex=@Sex WHERE MemberID=@MemberId ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand sqlCommand = new SqlCommand(sqlStatement, connection))
{
sqlCommand.Parameters.AddWithValue("@FName", storeMember.FirstName);
sqlCommand.Parameters.AddWithValue("@LName", storeMember.LastName);
sqlCommand.Parameters.AddWithValue("@Phone", storeMember.Phone);
sqlCommand.Parameters.AddWithValue("@Address1", storeMember.Address1);
sqlCommand.Parameters.AddWithValue("@Address2", storeMember.Address2);
sqlCommand.Parameters.AddWithValue("@City", storeMember.City);
sqlCommand.Parameters.AddWithValue("@State", storeMember.State);
sqlCommand.Parameters.AddWithValue("@ZipCode", storeMember.Zip);
sqlCommand.Parameters.AddWithValue("@DOB", storeMember.Dob);
sqlCommand.Parameters.AddWithValue("@MemberId", storeMember.MemberID);
sqlCommand.Parameters.AddWithValue("@Sex", storeMember.Sex);
int execution = sqlCommand.ExecuteNonQuery();
if (execution > 0)
{
return(true);
}
}
}
return(false);
}
/// <summary>
/// Get Store Member details using Customer Id
/// </summary>
/// <param name="customerId"></param>
/// <returns>StoreMember Object</returns>
public StoreMember GetStoreMemberByCustomerId(int customerId)
{
StoreMember storeMember = null;
string selectStatement =
"select * from dbo.StoreMembers WHERE MemberID = @MemberID ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@MemberID", customerId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
storeMember = this.RetrieveStoreMember(reader);
}
}
}
}
return(storeMember);
}
/// <summary>
/// Get Store Member details using Name
/// </summary>
/// <param name="phoneNumber"></param>
/// <returns>List of StoreMember Objects</returns>
public List <StoreMember> GetStoreMemberByName(string firstName, string lastName)
{
List <StoreMember> storeMembers = new List <StoreMember>();
string selectStatement =
"select * from dbo.StoreMembers WHERE lower(FName) = lower(@FirstName) and lower(LName) = lower(@LastName) ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@FirstName", firstName);
selectCommand.Parameters.AddWithValue("@LastName", lastName);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
storeMembers.Add(this.RetrieveStoreMember(reader));
}
}
}
}
return(storeMembers);
}
/// <summary>
/// Get Store Member details using Phone Number
/// </summary>
/// <param name="phoneNumber"></param>
/// <returns>List of StoreMember Objects</returns>
public List <StoreMember> GetStoreMemberByPhoneNumber(string phoneNumber)
{
List <StoreMember> storeMembers = new List <StoreMember>();
string selectStatement =
"select * from dbo.StoreMembers WHERE Phone = @Phone ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@Phone", phoneNumber);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
storeMembers.Add(this.RetrieveStoreMember(reader));
}
}
}
}
return(storeMembers);
}
/// <summary>
/// This method gets a rental transaction by rental ID.
/// </summary>
/// <param name="rentalID">rentalID</param>
/// <returns>A Rental Item</returns>
public Item GetRentalTransactionByRentalID(int rentalID)
{
Item item = new Item();
string selectStatement =
"SELECT ScheduledReturn " +
"FROM RentalTransaction " +
"WHERE RentalTransaction.RentalID=@rentalID ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@rentalID", rentalID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
item.ScheduledReturn = (DateTime)reader["ScheduledReturn"];
}
}
}
}
return(item);
}
/// <summary>
/// Returns all Styles
/// </summary>
/// <returns>List of Technicians</returns>
public List <Style> GetStyles()
{
List <Style> styleList = new List <Style>();
string selectStatement =
"SELECT * FROM dbo.Style ORDER BY Description";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Style style = new Style();
style.StyleID = (int)reader["StyleID"];
style.Description = reader["Description"].ToString();
styleList.Add(style);
}
}
}
}
return(styleList);
}
/// <summary>
/// This method gets a list of employees.
/// </summary>
/// <returns>A list.</returns>
internal List <Employee> GetEmployeeList()
{
List <Employee> employeeList = new List <Employee>();
string selectStatement =
@"SELECT EmployeeID, FName, LName
FROM Employees
Where IsActive = 1";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
var employee = new Employee();
employee.EmployeeID = int.Parse(reader["EmployeeID"].ToString());
employee.FName = reader["FName"].ToString();
employee.LName = reader["LName"].ToString();
employee.FullName = employee.FName + " " + employee.LName;
employeeList.Add(employee);
}
}
}
}
return(employeeList);
}
/// <summary>
/// This method gets a list of rental id by member id.
/// </summary>
/// <param name="memberID">memberID</param>
/// <returns>A list</returns>
internal List <int> GetRentalIDListByMemberID(int memberID)
{
List <int> list = new List <int>();
string selectStatement =
@"SELECT RentalID FROM RentalTransaction Where MemberID = @memberID; ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@memberID", memberID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
int rentalID = int.Parse(reader["RentalID"].ToString());
list.Add(rentalID);
}
}
}
}
if (list.Count == 0)
{
throw new ArgumentException("There is no store member with that ID. Please try again.");
}
return(list);
}
/// <summary>
/// This class checks the entered data against the employee database.
/// </summary>
/// <param name="username">String username entered</param>
/// <param name="password">String password entered</param>
/// <returns></returns>
public LoginResult EmployeeLogin(String username, String password)
{
var encryptedPassword = Security.encrypt(password);
string selectStatement =
@"SELECT *
FROM dbo.Employees
WHERE Username = @username and Password = @password COLLATE Latin1_General_CS_AS and IsActive = 1";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@username", username);
selectCommand.Parameters.AddWithValue("@password", encryptedPassword);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
var loginResult = new LoginResult();
loginResult.EmployeeID = reader.GetInt32(0);
loginResult.IsAdmin = reader.GetBoolean(reader.GetOrdinal("IsAdmin"));
loginResult.FName = reader["FName"].ToString();
loginResult.LName = reader["LName"].ToString();
return(loginResult);
}
}
}
}
return(null);
}
/// <summary>
/// This method updates furniture quantity
/// </summary>
/// <param name="returnItem"></param>
/// <returns>boolean if update was posted</returns>
public bool UpdateFurnitureQuantity(ReturnItem returnItem)
{
string updateStatement =
@" Update furnitureItem
SET Quantity = Quantity + @quantity
Where Serial# = @serial#";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
cmd.Parameters.AddWithValue("@serial#", returnItem.SerialNumber);
cmd.Parameters.AddWithValue("@quantity", returnItem.Quantity);
var result = cmd.ExecuteNonQuery();
connection.Close();
return(result > 0);
}
}
}
/// <summary>
/// This method returns an employee with the specified full name.
/// </summary>
/// <param name="fullName">The full name of the employee.</param>
/// <returns>An employee with the specified full name.</returns>
public Employee GetEmployeeByFullName(string fullName)
{
fullName = fullName.Trim();
if (!fullName.Contains(" "))
{
throw new ArgumentException("Ensure you have entered the first and last name separated by a space.");
}
int counter = 0;
Employee employee = null;
string selectStatement =
"SELECT EmployeeID, FName, LName, Sex, DOB, Phone, Address1, Address2, City, State, ZipCode, Username, Password, IsActive, IsAdmin " +
"FROM Employees " +
"WHERE FName=@fName AND LName=@lName; ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@fName", fullName.Substring(0, fullName.IndexOf(" ")));
selectCommand.Parameters.AddWithValue("@lName", fullName.Substring(fullName.IndexOf(" ") + 1, fullName.Length - fullName.IndexOf(" ") - 1));
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
counter++;
if (counter > 1)
{
throw new ArgumentException("There is more than 1 employee with that name. Please search by another parameter.");
}
int employeeID = reader.GetInt32(0);
string fNameFromDB = reader["FName"].ToString();
string lNameFromDB = reader["LName"].ToString();
string sex = reader["Sex"].ToString();
DateTime dob = (DateTime)reader["DOB"];
string phone = reader["Phone"].ToString();
string address1 = reader["Address1"].ToString();
string address2 = reader["Address2"].ToString();
string city = reader["City"].ToString();
string state = reader["State"].ToString();
string zipCode = reader["ZipCode"].ToString();
string username = reader["Username"].ToString();
string password = reader["Password"].ToString();
Boolean isActive = (Boolean)reader["IsActive"];
Boolean isAdmin = (Boolean)reader["IsAdmin"];
employee = new Employee(employeeID, fNameFromDB, lNameFromDB, sex, dob, phone, address1, address2, city, state, zipCode, username,
password, isActive, isAdmin);
}
}
}
}
if (employee != null)
{
return(employee);
}
else
{
throw new ArgumentException("There is no employee with that name in the database. Ensure you have entered their first and last name separated by a space.");
}
}
/// <summary>
/// Adds Store Member to Database
/// </summary>
/// <param name="StoreMember">Store Member Object</param>
/// <returns>true if record got inserted, else false</returns>
public int AddStoreMember(StoreMember StoreMember)
{
string sqlStatement = "INSERT INTO dbo.StoreMembers (fName, lName, dob, phone, address1, address2, city, state, zipcode, sex) " +
"VALUES (@FirstName, @LastName, @Dob, @Phone, @Address1, @Address2, @City, @State, @Zip, @Sex); SELECT SCOPE_IDENTITY() ";
int customerId = 0;
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand sqlCommand = new SqlCommand(sqlStatement, connection))
{
sqlCommand.Parameters.AddWithValue("@FirstName", StoreMember.FirstName);
sqlCommand.Parameters.AddWithValue("@LastName", StoreMember.LastName);
sqlCommand.Parameters.AddWithValue("@Dob", StoreMember.Dob);
sqlCommand.Parameters.AddWithValue("@Phone", StoreMember.Phone);
sqlCommand.Parameters.AddWithValue("@Address1", StoreMember.Address1);
sqlCommand.Parameters.AddWithValue("@Address2", StoreMember.Address2);
sqlCommand.Parameters.AddWithValue("@City", StoreMember.City);
sqlCommand.Parameters.AddWithValue("@State", StoreMember.State);
sqlCommand.Parameters.AddWithValue("@Zip", StoreMember.Zip);
sqlCommand.Parameters.AddWithValue("@Sex", StoreMember.Sex);
customerId = Convert.ToInt32(sqlCommand.ExecuteScalar());
}
}
return(customerId);
}
/// <summary>
/// Returns all Categories
/// </summary>
/// <returns>List of Categories</returns>
public List <Category> GetCategories()
{
List <Category> categoryList = new List <Category>();
string selectStatement =
"SELECT * FROM dbo.Category ORDER BY Description";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Category category = new Category();
category.CategoryID = (int)reader["CategoryID"];
category.Description = reader["Description"].ToString();
categoryList.Add(category);
}
}
}
}
return(categoryList);
}
/// <summary>
/// This method returns an employee with the specified phone.
/// </summary>
/// <param name="phone">The phone of the employee.</param>
/// <returns>An employee with the specified phone.</returns>
public Employee GetEmployeeByPhone(string phone)
{
int counter = 0;
Employee employee = null;
string selectStatement =
"SELECT EmployeeID, FName, LName, Sex, DOB, Phone, Address1, Address2, City, State, ZipCode, Username, Password, IsActive, IsAdmin " +
"FROM Employees " +
"WHERE Phone=@phone; ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@phone", phone);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
counter++;
if (counter > 1)
{
throw new ArgumentException("There is more than 1 employee with that phone. Please search by another parameter.");
}
int employeeID = reader.GetInt32(0);
string fName = reader["FName"].ToString();
string lName = reader["LName"].ToString();
string sex = reader["Sex"].ToString();
DateTime dob = (DateTime)reader["DOB"];
string phoneFromDB = reader["Phone"].ToString();
string address1 = reader["Address1"].ToString();
string address2 = reader["Address2"].ToString();
string city = reader["City"].ToString();
string state = reader["State"].ToString();
string zipCode = reader["ZipCode"].ToString();
string username = reader["Username"].ToString();
string password = reader["Password"].ToString();
Boolean isActive = (Boolean)reader["IsActive"];
Boolean isAdmin = (Boolean)reader["IsAdmin"];
employee = new Employee(employeeID, fName, lName, sex, dob, phoneFromDB, address1, address2, city, state, zipCode, username,
password, isActive, isAdmin);
}
}
}
}
if (employee != null)
{
return(employee);
}
else
{
throw new ArgumentException("There is no employee with that phone in the database. Ensure you have searched using XXX-XXX-XXXX format.");
}
}
/// <summary>
/// This method creates a return item
/// </summary>
/// <param name="returnItem">A ReturnItem</param>
/// <returns>true or false</returns>
public bool CreateReturnItem(ReturnItem returnItem)
{
string insertStatement =
@"INSERT INTO dbo.Returns
(Serial#
, RentalID
, ReturnID
, Quantity
, FineDue
, RefundDue)
SELECT
@serial#
,@rentalID
,@returnID
,@quantity
,@fineDue
,@refundDue
WHERE @quantity <= (SELECT
i.Quantity - coalesce(
(
SELECT SUM(Quantity) From Returns WHERE Serial# = i.Serial# AND RentalID = i.RentalID
),0) AS Quantity
From RentedItem i Where i.RentalID = @rentalID AND (i.Quantity - coalesce(
(
SELECT SUM(Quantity) From Returns WHERE Serial# = i.Serial# AND RentalID = i.RentalID
),0) > 0) AND Serial# = @serial#);";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
cmd.Parameters.AddWithValue("@serial#", returnItem.SerialNumber);
cmd.Parameters.AddWithValue("@rentalID", returnItem.RentalID);
cmd.Parameters.AddWithValue("@returnID", returnItem.ReturnID);
cmd.Parameters.AddWithValue("@quantity", returnItem.Quantity);
cmd.Parameters.AddWithValue("@fineDue", returnItem.FineDue);
cmd.Parameters.AddWithValue("@refundDue", returnItem.RefundDue);
var result = cmd.ExecuteNonQuery();
connection.Close();
if (result > 0)
{
UpdateFurnitureQuantity(returnItem);
}
return(result > 0);
}
}
}
/// <summary>
/// This method adds an employee to the database.
/// </summary>
/// <param name="employee">The employee to be added to the database.</param>
public int AddEmployee(Employee employee)
{
int employeeID = 0;
string insertStatement =
"INSERT INTO Employees (FName,LName,Sex,DOB,Phone,Address1,Address2,City,State,ZipCode,Username,Password,IsActive,IsAdmin) " +
"VALUES (@fName,@lName,@sex,@dob,@phone,@address1,@address2,@city,@state,@zipCode,@username,@password,@isActive,@isAdmin); ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
{
insertCommand.Parameters.AddWithValue("@fName", employee.FName);
insertCommand.Parameters.AddWithValue("@lName", employee.LName);
insertCommand.Parameters.AddWithValue("@sex", employee.Sex);
insertCommand.Parameters.AddWithValue("@dob", employee.DOB.ToString("yyyy'-'MM'-'dd"));
insertCommand.Parameters.AddWithValue("@phone", employee.Phone);
insertCommand.Parameters.AddWithValue("@address1", employee.Address1);
insertCommand.Parameters.AddWithValue("@address2", employee.Address2);
insertCommand.Parameters.AddWithValue("@city", employee.City);
insertCommand.Parameters.AddWithValue("@state", employee.State);
insertCommand.Parameters.AddWithValue("@zipCode", employee.ZipCode);
insertCommand.Parameters.AddWithValue("@username", employee.Username);
insertCommand.Parameters.AddWithValue("@password", Security.encrypt(employee.Password));
insertCommand.Parameters.AddWithValue("@isActive", employee.IsActive ? 1 : 0);
insertCommand.Parameters.AddWithValue("@isAdmin", employee.IsAdmin ? 1 : 0);
insertCommand.ExecuteNonQuery();
}
}
string selectStatement =
"SELECT MAX(EmployeeID) AS EmployeeID " +
"FROM Employees; ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
employeeID = reader.GetInt32(0);
}
}
}
}
return(employeeID);
}
/// <summary>
/// This method returns an employee with the specified ID.
/// </summary>
/// <param name="employeeID">The ID of the employee.</param>
/// <returns>An employee with the specified ID.</returns>
public Employee GetEmployeeByID(int employeeID)
{
Employee employee = null;
string selectStatement =
"SELECT EmployeeID, FName, LName, Sex, DOB, Phone, Address1, Address2, City, State, ZipCode, Username, Password, IsActive, IsAdmin " +
"FROM Employees " +
"WHERE EmployeeID=@employeeID; ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@employeeID", employeeID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
int employeeIDfromDB = reader.GetInt32(0);
string fName = reader["FName"].ToString();
string lName = reader["LName"].ToString();
string sex = reader["Sex"].ToString();
DateTime dob = (DateTime)reader["DOB"];
string phone = reader["Phone"].ToString();
string address1 = reader["Address1"].ToString();
string address2 = reader["Address2"].ToString();
string city = reader["City"].ToString();
string state = reader["State"].ToString();
string zipCode = reader["ZipCode"].ToString();
string username = reader["Username"].ToString();
string password = reader["Password"].ToString();
Boolean isActive = (Boolean)reader["IsActive"];
Boolean isAdmin = (Boolean)reader["IsAdmin"];
employee = new Employee(employeeIDfromDB, fName, lName, sex, dob, phone, address1, address2, city, state, zipCode, username,
password, isActive, isAdmin);
}
}
}
}
if (employee != null)
{
return(employee);
}
else
{
throw new ArgumentException("There is no employee with that ID in the database.");
}
}
/// <summary>
/// This method updates the specified employee in the database.
/// </summary>
/// <param name="oldEmployeeDetails">The employee to be updated in the database.</param>
/// <param name="newEmployeeDetails">The new details of the employee.</param>
public void UpdateEmployee(Employee oldEmployeeDetails, Employee newEmployeeDetails)
{
string insertStatement =
"UPDATE Employees " +
"SET FName=@fName, LName=@lName, Sex=@sex, DOB=@dob, Phone=@phone, Address1=@address1, Address2=@address2, City=@city, " +
"State=@state, ZipCode=@zipCode, Username=@username, Password=@password, IsActive=@isActive, IsAdmin=@isAdmin " +
"WHERE EmployeeID=@employeeID AND FName=@oldFName AND LName=@oldLName AND Sex=@oldSex AND DOB=@oldDOB AND Phone=@oldPhone " +
"AND Address1=@oldAddress1 AND City=@oldCity AND State=@oldState AND ZipCode=@oldZipCode " +
"AND Username=@oldUsername AND IsActive=@oldIsActive AND IsAdmin=@oldIsAdmin; ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
{
insertCommand.Parameters.AddWithValue("@employeeID", oldEmployeeDetails.EmployeeID);
insertCommand.Parameters.AddWithValue("@oldFName", oldEmployeeDetails.FName);
insertCommand.Parameters.AddWithValue("@oldLName", oldEmployeeDetails.LName);
insertCommand.Parameters.AddWithValue("@oldSex", oldEmployeeDetails.Sex);
insertCommand.Parameters.AddWithValue("@oldDOB", oldEmployeeDetails.DOB.ToString("yyyy'-'MM'-'dd"));
insertCommand.Parameters.AddWithValue("@oldPhone", oldEmployeeDetails.Phone);
insertCommand.Parameters.AddWithValue("@oldAddress1", oldEmployeeDetails.Address1);
insertCommand.Parameters.AddWithValue("@oldCity", oldEmployeeDetails.City);
insertCommand.Parameters.AddWithValue("@oldState", oldEmployeeDetails.State);
insertCommand.Parameters.AddWithValue("@oldZipCode", oldEmployeeDetails.ZipCode);
insertCommand.Parameters.AddWithValue("@oldUsername", oldEmployeeDetails.Username);
insertCommand.Parameters.AddWithValue("@oldIsActive", oldEmployeeDetails.IsActive ? 1 : 0);
insertCommand.Parameters.AddWithValue("@oldIsAdmin", oldEmployeeDetails.IsAdmin ? 1 : 0);
insertCommand.Parameters.AddWithValue("@FName", newEmployeeDetails.FName);
insertCommand.Parameters.AddWithValue("@LName", newEmployeeDetails.LName);
insertCommand.Parameters.AddWithValue("@Sex", newEmployeeDetails.Sex);
insertCommand.Parameters.AddWithValue("@DOB", newEmployeeDetails.DOB.ToString("yyyy'-'MM'-'dd"));
insertCommand.Parameters.AddWithValue("@Phone", newEmployeeDetails.Phone);
insertCommand.Parameters.AddWithValue("@Address1", newEmployeeDetails.Address1);
insertCommand.Parameters.AddWithValue("@Address2", newEmployeeDetails.Address2);
insertCommand.Parameters.AddWithValue("@City", newEmployeeDetails.City);
insertCommand.Parameters.AddWithValue("@State", newEmployeeDetails.State);
insertCommand.Parameters.AddWithValue("@ZipCode", newEmployeeDetails.ZipCode);
insertCommand.Parameters.AddWithValue("@Username", newEmployeeDetails.Username);
insertCommand.Parameters.AddWithValue("@Password", Security.encrypt(newEmployeeDetails.Password));
insertCommand.Parameters.AddWithValue("@IsActive", newEmployeeDetails.IsActive ? 1 : 0);
insertCommand.Parameters.AddWithValue("@IsAdmin", newEmployeeDetails.IsAdmin ? 1 : 0);
insertCommand.ExecuteNonQuery();
}
}
}
/// <summary>
/// This method returns a list of rented items from the rental transactions with the specified StoreMemberID.
/// </summary>
/// <param name="storeMemberID">The ID of the StoreMember.</param>
/// <returns>A list of rented items from the rental transactions with the specified StoreMemberID.</returns>
public List <ReturnedItem> GetReturnTransactionsWithItemsByStoreMemberID(int storeMemberID)
{
List <ReturnedItem> returnedList = new List <ReturnedItem>();
string selectStatement =
"SELECT ReturnTransaction.ReturnID AS ReturnID, Returns.RentalID AS RentalID, ReturnTransaction.ReturnDate AS ReturnDate, " +
"RentalTransaction.ScheduledReturn AS ScheduledReturn, Employees.FName AS EmployeeFirst, Employees.LName AS EmployeeLast, " +
"FurnitureItem.Serial# AS ItemSerial, FurnitureItem.Description AS ItemDescription, Returns.Quantity AS ItemQuantity, " +
"Returns.FineDue AS FineDue, Returns.RefundDue AS RefundDue " +
"FROM ReturnTransaction " +
"LEFT JOIN Employees ON ReturnTransaction.EmployeeID=Employees.EmployeeID " +
"LEFT JOIN Returns ON ReturnTransaction.ReturnID=Returns.ReturnID " +
"LEFT JOIN RentalTransaction ON Returns.RentalID=RentalTransaction.RentalID " +
"LEFT JOIN FurnitureItem ON Returns.Serial#=FurnitureItem.Serial# " +
"WHERE RentalTransaction.MemberID=@memberID " +
"ORDER BY ReturnID ASC, RentalID ASC; ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@memberID", storeMemberID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
int returnID = reader.GetInt32(0);
int rentalID = reader.GetInt32(1);
DateTime returnDate = (DateTime)reader["ReturnDate"];
DateTime scheduledReturn = (DateTime)reader["ScheduledReturn"];
string employeeFullName = reader["EmployeeFirst"].ToString() + " " + reader["EmployeeLast"].ToString();
string itemSerial = reader["ItemSerial"].ToString();
string itemDescription = reader["ItemDescription"].ToString();
int itemQuantity = reader.GetInt32(8);
decimal fineDue = reader.GetDecimal(9);
decimal refundDue = reader.GetDecimal(10);
ReturnedItem returnedItem = new ReturnedItem(returnID, rentalID, returnDate, scheduledReturn, employeeFullName, itemSerial,
itemDescription, itemQuantity, fineDue, refundDue);
returnedList.Add(returnedItem);
}
}
}
}
return(returnedList);
}
/// <summary>
/// This method gets a list of returnable items by rental ID.
/// </summary>
/// <param name="rentalID">rentalID</param>
/// <returns>A List</returns>
internal List <ReturnableItem> GetReturnableItemsByRentalID(int rentalID)
{
List <ReturnableItem> returnableItemList = new List <ReturnableItem>();
string selectStatement =
@"SELECT i.Serial#,
i.Quantity - coalesce(
(
SELECT SUM(Quantity) From Returns WHERE Serial# = i.Serial# AND RentalID = i.RentalID
),0) AS Quantity,
f.Description, s.Description As 'Style', c.Description As 'Category'
From RentedItem i
Left Join FurnitureItem f ON i.Serial# = f.Serial#
JOIN dbo.Style s on f.StyleID = s.StyleID
JOIN dbo.Category c on f.CategoryID = c.CategoryID
Where i.RentalID = @rentalID AND (i.Quantity - coalesce(
(
SELECT SUM(Quantity) From Returns WHERE Serial# = i.Serial# AND RentalID = i.RentalID
),0) > 0) ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@rentalID", rentalID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
var returnableItem = new ReturnableItem();
returnableItem.SerialNumber = reader["Serial#"].ToString();
returnableItem.Quantity = int.Parse(reader["Quantity"].ToString());
returnableItem.Description = reader["Description"].ToString();
returnableItem.Style = reader["Style"].ToString();
returnableItem.Category = reader["Category"].ToString();
returnableItem.RentalID = rentalID;
returnableItemList.Add(returnableItem);
}
}
}
}
return(returnableItemList);
}
/// <summary>
/// Find number of available furnitures
/// </summary>
/// <param name="serialNumber"></param>
/// <returns>Number of furnitures available</returns>
public int GetCurrentFurnitureCount(string serialNumber)
{
int quantity = 0;
string selectStatement =
"SELECT quantity from dbo.FurnitureItem where Serial# = @SerialNumber ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@SerialNumber", serialNumber);
quantity = Convert.ToInt32(selectCommand.ExecuteScalar());
}
}
return(quantity);
}
/// <summary>
/// This method updates a return transaction.
/// </summary>
/// <param name="transaction">A return transaction</param>
internal void UpdateTransaction(ReturnTransaction transaction)
{
string updateStatement =
@"Update [dbo].[ReturnTransaction]
SET [FineDueTotal] = @newFineDueTotal,
[RefundDueTotal]=@newRefundDueTotal
Where ReturnID = @returnID";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
cmd.Parameters.AddWithValue("@newFineDueTotal", transaction.FineDueTotal);
cmd.Parameters.AddWithValue("@newRefundDueTotal", transaction.RefundDueTotal);
cmd.Parameters.AddWithValue("@returnID", transaction.ReturnID);
cmd.ExecuteNonQuery();
connection.Close();
}
}
}
/// <summary>
/// Get Furnitures using Serial Number
/// </summary>
/// <param name="serialNumber"></param>
/// <returns>Furniture List</returns>
public List <Furniture> GetFurnituresBySerialNumber(string serialNumber)
{
List <Furniture> furnitureList = new List <Furniture>();
string selectStatement =
"select Serial#, f.Description, Quantity, FineRate, DailyRentalRate, s.Description as Style, c.Description as Category from dbo.FurnitureItem f " +
"JOIN dbo.Style s on f.StyleID = s.StyleID " +
"JOIN dbo.Category c on f.CategoryID = c.CategoryID " +
"WHERE lower(f.Serial#) = lower(@SerialNumber) ";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@SerialNumber", serialNumber);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Furniture furniture = new Furniture();
furniture.SerialNumber = reader["Serial#"].ToString();
furniture.Description = reader["Description"].ToString();
furniture.Quantity = (int)reader["Quantity"];
furniture.FineRate = Convert.ToDouble(reader["FineRate"]);
furniture.DailyRentalRate = Convert.ToDouble(reader["DailyRentalRate"]);
furniture.Style = reader["Style"].ToString();
furniture.Category = reader["Category"].ToString();
furnitureList.Add(furniture);
}
}
}
}
return(furnitureList);
}
/// <summary>
/// This method creates a return transaction.
/// </summary>
/// <param name="transaction">A return transaction</param>
/// <returns>The return transaction ID</returns>
public int CreateReturnTransaction(ReturnTransaction transaction)
{
string insertStatement =
@"INSERT INTO dbo.ReturnTransaction
(ReturnDate
, FineDueTotal
, RefundDueTotal
, EmployeeID )
VALUES
(@returnDate
,@fineDueTotal
,@refundDueTotal
,@employeeID );
SELECT SCOPE_IDENTITY()";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
cmd.Parameters.AddWithValue("@returnDate", transaction.ReturnDate);
cmd.Parameters.AddWithValue("@fineDueTotal", 0);
cmd.Parameters.AddWithValue("@refundDueTotal", 0);
cmd.Parameters.AddWithValue("@employeeID", transaction.EmployeeID);
var idObject = cmd.ExecuteScalar();
var id = int.Parse(idObject.ToString());
connection.Close();
return(id);
}
}
}
/// <summary>
/// Inserts cart items to rental tables
/// </summary>
/// <param name="cart">Cart containing rental items</param>
/// <returns>true if everything got inserted correctly else if there is any exception</returns>
public Boolean InsertRentalTransaction(Cart cart)
{
int rentalTransactionId = 0;
string insertRentalTransactionStatement =
"insert into dbo.RentalTransaction (DateOfRental, ScheduledReturn, EmployeeID, MemberID) " +
"VALUES (@RentalStartDate, @RentalEndDate, @EmployeeId, @MemberId); SELECT SCOPE_IDENTITY() ";
string insertRentalItemStatement =
"insert into dbo.RentedItem (Serial#, RentalID, Quantity) VALUES (@SerialNumber, @RentalId, @Quantity) ";
string updateQuantity =
"update dbo.FurnitureItem set Quantity = (Quantity-@Quantity) where Serial# = @SerialNumber";
using (SqlConnection connection = RentMeDBConnection.GetConnection())
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction("RentalTransaction");
try
{
using (SqlCommand insertCommand = new SqlCommand(insertRentalTransactionStatement, connection, transaction))
{
insertCommand.Parameters.AddWithValue("@RentalStartDate", cart.RentalStartDate);
insertCommand.Parameters.AddWithValue("@RentalEndDate", cart.RentalEndDate);
insertCommand.Parameters.AddWithValue("@EmployeeId", cart.EmployeeID);
insertCommand.Parameters.AddWithValue("@MemberId", cart.MemberID);
rentalTransactionId = Convert.ToInt32(insertCommand.ExecuteScalar());
}
foreach (CartItem item in cart.Items)
{
using (SqlCommand insertCommand = new SqlCommand(insertRentalItemStatement, connection, transaction))
{
insertCommand.Parameters.AddWithValue("@SerialNumber", item.SerialNumber);
insertCommand.Parameters.AddWithValue("@RentalId", rentalTransactionId);
insertCommand.Parameters.AddWithValue("@Quantity", item.Quantity);
insertCommand.ExecuteNonQuery();
}
using (SqlCommand updateCommand = new SqlCommand(updateQuantity, connection, transaction))
{
updateCommand.Parameters.AddWithValue("@SerialNumber", item.SerialNumber);
updateCommand.Parameters.AddWithValue("@Quantity", item.Quantity);
updateCommand.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine(" Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
return(false);
}
}
return(true);
}
