/// <summary>
/// Method gets categories
/// </summary>
/// <returns>list of categories</returns>
public List <Category> GetCategories()
{
List <Category> categories = new List <Category>();
string selectStatement =
"SELECT category_id AS CategoryID, description as Description FROM furniture_category";
using (SqlConnection connection = FurnitureRentalsDBConnection.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.CategoryDescription = reader["Description"].ToString();
categories.Add(category);
}
}
}
}
return(categories);
}
/// <summary>
/// Method that returns the transaction by rental id
/// </summary>
/// <param name="rentalID">rentalId</param>
/// <returns>a rental transaction</returns>
public RentalTransaction GetRentalTransactionsByID(int rentalID)
{
RentalTransaction transaction = new RentalTransaction();
string selectStatement = "SELECT rental_id as RentalTransactionID, rented_on AS RentedOn, " +
"due_date AS DueDate, total_due AS TotalDue, status AS Status " +
"FROM rental_transaction WHERE rental_id = @RentalID;";
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@RentalID", rentalID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
transaction.RentalID = (int)reader["RentalTransactionID"];
transaction.RentalDate = (DateTime)reader["RentedOn"];
transaction.DueDate = (DateTime)reader["DueDate"];
transaction.TotalDue = (Decimal)reader["TotalDue"];
transaction.Status = reader["Status"].ToString();
}
}
}
}
return(transaction);
}
/// <summary>
/// Method that updates the customer into customer table
/// </summary>
/// <param name="customer">customer object</param>
/// <returns>true if customer updated successfully</returns>
public Boolean UpdateCustomer(Customer customer)
{
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
string sqlStatement = "UPDATE CUSTOMER SET first_name=@FirstName, middle_name = @MiddleName, " +
"last_name = @LastName, sex = @Gender, date_of_birth = @DateOfBirth, phone_number=@PhoneNumber, " +
"address1=@Address1, address2=@Address2, city=@City, state=@State, zipcode=@PostalCode " +
"WHERE customer_id=@CustomerId";
connection.Open();
using (SqlCommand updateCommand = new SqlCommand(sqlStatement, connection))
{
updateCommand.Connection = connection;
updateCommand.Parameters.AddWithValue("@CustomerId", customer.CustomerId);
updateCommand.Parameters.AddWithValue("@FirstName", customer.FirstName.Trim());
updateCommand.Parameters.AddWithValue("@MiddleName", customer.MiddleName.Trim());
updateCommand.Parameters.AddWithValue("@LastName", customer.LastName.Trim());
updateCommand.Parameters.AddWithValue("@Gender", customer.Gender);
updateCommand.Parameters.AddWithValue("@DateOfBirth", customer.DateOfBirth);
updateCommand.Parameters.AddWithValue("@PhoneNumber", customer.HomePhone);
updateCommand.Parameters.AddWithValue("@Address1", customer.Address1.Trim());
updateCommand.Parameters.AddWithValue("@Address2", customer.Address2.Trim());
updateCommand.Parameters.AddWithValue("@City", customer.City.Trim());
updateCommand.Parameters.AddWithValue("@State", customer.State);
updateCommand.Parameters.AddWithValue("@PostalCode", customer.PostalCode);
updateCommand.ExecuteNonQuery();
return(true);
}
}
}
/// <summary>
/// Searches for furniture by furniture id
/// </summary>
/// <param name="furnitureID">furnitureID</param>
/// <returns>furniture with that furniture id</returns>
public Furniture GetFurnitureByID(int furnitureId)
{
Furniture furniture = new Furniture();
string selectStatement = "SELECT furniture.furniture_id as FurnitureID, serial_no AS 'Serial Number', " +
"furniture.description AS Item, furniture_style.description AS Style, " +
"total_available AS 'Total Available', daily_rental_rate AS 'Daily Rental Rate', " +
"daily_fine_rate AS 'Daily Fine Rate' " +
"FROM furniture JOIN furniture_style ON furniture.style_id = furniture_style.style_id JOIN " +
"inventory ON furniture.furniture_id = inventory.furniture_id WHERE furniture.furniture_id = @FurnitureID;";
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@FurnitureID", furnitureId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
furniture.FurnitureID = (int)reader["FurnitureID"];
furniture.SerialNumber = reader["Serial Number"].ToString();
furniture.ItemDescription = reader["Item"].ToString();
furniture.FurnitureStyle = reader["Style"].ToString();
furniture.Quantity = (int)reader["Total Available"];
furniture.DailyRentalRate = (decimal)reader["Daily Rental Rate"];
furniture.DailyFineRate = (decimal)reader["Daily Fine Rate"];
}
}
}
}
return(furniture);
}
/// <summary>
/// Returns list of styles
/// </summary>
/// <returns>list of styles</returns>
public List <Style> GetStyles()
{
List <Style> styles = new List <Style>();
string selectStatement =
"SELECT style_id AS StyleID, description as Description FROM furniture_style";
using (SqlConnection connection = FurnitureRentalsDBConnection.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.StyleDescription = reader["Description"].ToString();
styles.Add(style);
}
}
}
}
return(styles);
}
/// <summary>
/// Method that registers the customer into customer table
/// </summary>
/// <param name="customer">customer object</param>
/// <returns>true if customer registered successfully</returns>
public Boolean RegisterCustomer(Customer customer)
{
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
string sqlStatement = "INSERT INTO CUSTOMER (first_name, middle_name, last_name, sex, " +
"date_of_birth, phone_number, address1, address2, city, state, zipcode) " +
"VALUES (@FirstName, @MiddleName, @LastName, @Gender, @DateOfBirth, @PhoneNumber, @Address1," +
"@Address2, @City, @State, @PostalCode); SELECT SCOPE_IDENTITY() ";
connection.Open();
using (SqlCommand insertCommand = new SqlCommand(sqlStatement, connection))
{
insertCommand.Connection = connection;
insertCommand.Parameters.AddWithValue("@FirstName", customer.FirstName.Trim());
insertCommand.Parameters.AddWithValue("@MiddleName", customer.MiddleName.Trim());
insertCommand.Parameters.AddWithValue("@LastName", customer.LastName.Trim());
insertCommand.Parameters.AddWithValue("@Gender", customer.Gender);
insertCommand.Parameters.AddWithValue("@DateOfBirth", customer.DateOfBirth);
insertCommand.Parameters.AddWithValue("@PhoneNumber", customer.HomePhone);
insertCommand.Parameters.AddWithValue("@Address1", customer.Address1.Trim());
insertCommand.Parameters.AddWithValue("@Address2", customer.Address2.Trim());
insertCommand.Parameters.AddWithValue("@City", customer.City.Trim());
insertCommand.Parameters.AddWithValue("@State", customer.State.Trim());
insertCommand.Parameters.AddWithValue("@PostalCode", customer.PostalCode);
customer.CustomerId = Convert.ToInt32(insertCommand.ExecuteScalar());
return(true);
}
}
}
/// <summary>
/// Finds transactions by customer id
/// </summary>
/// <param name="customerID">customer id</param>
/// <returns>list of customer's transactions</returns>
public List <RentalTransaction> GetCustomerTransactionsByCustomerID(int customerID)
{
RentalTransaction transaction = new RentalTransaction();
List <RentalTransaction> transactionList = new List <RentalTransaction>();
transaction.CustomerID = customerID;
string selectStatement = "SELECT rental_id as RentalTransactionID, rented_on AS RentedOn, due_date AS DueDate, " +
"total_due AS TotalDue, status AS Status FROM rental_transaction WHERE customer_id = @CustomerID";
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@CustomerID", transaction.CustomerID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
RentalTransaction newTransaction = new RentalTransaction();
newTransaction.RentalID = (int)reader["RentalTransactionID"];
newTransaction.RentalDate = (DateTime)reader["RentedOn"];
newTransaction.DueDate = (DateTime)reader["DueDate"];
newTransaction.TotalDue = (Decimal)reader["TotalDue"];
newTransaction.Status = reader["Status"].ToString();
transactionList.Add(newTransaction);
}
}
}
}
return(transactionList);
}
/// <summary>
/// Method that returns the total quantity returned for a given rental item id
/// </summary>
/// <param name="rentalItemID">rental item id of a rental transaction</param>
/// <returns></returns>
public int GetQuantityReturned(int rentalItemID)
{
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction();
int totalQuantityReturned = this.GetQuantityReturned(rentalItemID, connection, sqlTransaction);
sqlTransaction.Dispose();
return(totalQuantityReturned);
}
}
/// <summary>
/// Method that returns the list of furnitures rented
/// </summary>
/// <param name="transactionID">transaction of the rental</param>
/// <returns>a list of furnitures</returns>
public List <Furniture> GetRentalItemByTransactionID(int transactionID)
{
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction();
List <Furniture> furnitureList = this.GetRentalItemByTransactionID(transactionID, connection, sqlTransaction);
sqlTransaction.Dispose();
return(furnitureList);
}
}
/// <summary>
/// Method gets all return itmes for a transaction
/// </summary>
/// <param name="returnTransactionId"> transaction id</param>
/// <returns>items in return transaction</returns>
public List <ReturnItemView> GetAllReturnItems(int returnTransactionId)
{
string sqlStatement = "SELECT rental_id as RentalID, return_item.Quantity as ReturnQuantity, " +
"Furniture.serial_no as SerialNo, Furniture.description as ItemRented, furniture_style.description Style, " +
"rental_item.quantity TotalQuantity from return_item join rental_item on " +
"return_item.rental_item_id = rental_item.rental_item_id join furniture " +
"on rental_item.furniture_id = furniture.furniture_id join furniture_style on " +
"furniture.style_id = furniture_style.style_id where return_item.return_transaction_id=@ReturnTransactionID;";
Console.WriteLine(sqlStatement);
List <ReturnItemView> transactionList = new List <ReturnItemView>();
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(sqlStatement, connection))
{
selectCommand.Parameters.AddWithValue("@ReturnTransactionID", returnTransactionId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
ReturnItemView transaction = new ReturnItemView();
transaction.RentalID = (int)reader["RentalID"];
transaction.SerialNo = reader["SerialNo"].ToString();
transaction.ItemRented = reader["ItemRented"].ToString();
transaction.Style = reader["Style"].ToString();
transaction.TotalQuantity = (int)reader["TotalQuantity"];
transaction.ReturnedQuantity = (int)reader["ReturnQuantity"];
transactionList.Add(transaction);
}
}
}
}
return(transactionList);
}
/// <summary>
/// Checks validity of administrator's login credentials
/// </summary>
/// <param name="userName">admin user name</param>
/// <param name="password">admin password</param>
/// <returns>whether credentials are valid</returns>
public Model.Administrator CheckPassword(string userName, string password)
{
int foundLogins = 0;
Administrator loggedInAdministrator = new Administrator();
var convertedPassword = System.Text.Encoding.UTF8.GetBytes(password);
string encodedPassword = System.Convert.ToBase64String(convertedPassword);
string selectStatement = "SELECT COUNT(*) AS Login, first_name, last_name, admin_id " +
"FROM administrator WHERE username = @UserName AND password = @Password " +
"GROUP BY first_name, last_name, admin_id";
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@UserName", userName);
selectCommand.Parameters.AddWithValue("@Password", encodedPassword);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
foundLogins = (int)reader["Login"];
loggedInAdministrator.AdminstratorID = (int)reader["admin_id"];
loggedInAdministrator.FirstName = reader["first_name"].ToString();
loggedInAdministrator.LastName = reader["last_name"].ToString();
}
}
}
}
if (foundLogins == 1)
{
return(loggedInAdministrator);
}
else
{
return(null);
}
}
/// <summary>
/// Method that returns all the return transactions
/// </summary>
/// <returns>a list of return transactions</returns
public List <ReturnTransaction> GetAllReturnTransactions(int customerId)
{
string sqlStatement = "Select return_transaction_id as ReturnTransactionID, " +
"return_date as ReturnDate, checked_in_by as CheckedinBy, concat(first_name, ' ', last_name) as EmployeeName, " +
"late_fee as LateFee, refund_amount as RefundAmount from return_transaction join employee on checked_in_by = employee_id " +
"where customer_id=@CustomerId;";
Console.WriteLine(sqlStatement);
List <ReturnTransaction> transactionList = new List <ReturnTransaction>();
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(sqlStatement, connection))
{
selectCommand.Parameters.AddWithValue("@CustomerID", customerId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
ReturnTransaction transaction = new ReturnTransaction();
transaction.ReturnTransactionID = (int)reader["ReturnTransactionID"];
transaction.ReturnDate = (DateTime)reader["ReturnDate"];
transaction.CheckedinBy = (int)reader["CheckedinBy"];
transaction.EmployeeName = reader["EmployeeName"].ToString();
transaction.LateFee = (decimal)reader["LateFee"];
transaction.RefundAmount = (decimal)reader["RefundAmount"];
transactionList.Add(transaction);
}
}
}
}
return(transactionList);
}
/// <summary>
/// Adds a new employee to the Employee table of the RentMe database using a parameterized query.
/// </summary>
/// <param name="employee">Employee object with required properties added to the DB</param>
public static bool RegisterEmployee(Employee employee)
{
var convertedPassword = System.Text.Encoding.UTF8.GetBytes(employee.Password);
string encodedPassword = System.Convert.ToBase64String(convertedPassword);
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
string insertStatement =
"INSERT INTO employee " +
"(first_name, middle_name, last_name, sex, date_of_birth, address1, address2, " +
"city, state, zipcode, phone_number, username, password, status) " +
"VALUES (@first_name, @middle_name, @last_name, @sex, @date_of_birth, @address1, " +
"@address2, @city, @state, @zipcode, @phone_number, @username, @password, @status); " +
"SELECT CAST(scope_identity() AS int)";
connection.Open();
using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection))
{
insertCommand.Parameters.AddWithValue("@first_name", employee.FirstName);
insertCommand.Parameters.AddWithValue("@middle_name", employee.MiddleName);
insertCommand.Parameters.AddWithValue("@last_name", employee.LastName);
insertCommand.Parameters.AddWithValue("@sex", employee.Sex);
insertCommand.Parameters.AddWithValue("@date_of_birth", employee.DateOfBirth);
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.PostalCode);
insertCommand.Parameters.AddWithValue("@phone_number", employee.Phone);
insertCommand.Parameters.AddWithValue("@username", employee.UserName);
insertCommand.Parameters.AddWithValue("@password", encodedPassword);
insertCommand.Parameters.AddWithValue("@status", employee.Status);
employee.EmployeeID = Convert.ToInt32(insertCommand.ExecuteScalar());
return(true);
}
}
}
/// <summary>
/// Searches for furniture with a certain style
/// </summary>
/// <param name="styleID">style of furniture</param>
/// <returns>list of furniture with style</returns>
public List <Furniture> GetFurnitureByStyle(int styleID)
{
List <Furniture> furnitureList = new List <Furniture>();
Furniture chosenFurniture = new Furniture();
chosenFurniture.StyleID = styleID;
string selectStatement = "SELECT furniture.furniture_id as FurnitureID, serial_no AS 'Serial Number', furniture.description AS Item, furniture_style.description AS Style, total_available AS 'Total Available', daily_rental_rate AS 'Daily Rental Rate' " +
"FROM furniture JOIN furniture_style ON furniture.style_id = furniture_style.style_id JOIN inventory ON furniture.furniture_id = inventory.furniture_id WHERE furniture.style_id = @StyleID";
;
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@StyleID", chosenFurniture.StyleID);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Furniture furniture = new Furniture();
furniture.FurnitureID = (int)reader["FurnitureID"];
furniture.SerialNumber = reader["Serial Number"].ToString();
furniture.ItemDescription = reader["Item"].ToString();
furniture.FurnitureStyle = reader["Style"].ToString();
furniture.Quantity = (int)reader["Total Available"];
furniture.DailyRentalRate = (decimal)reader["Daily Rental Rate"];
furnitureList.Add(furniture);
}
}
}
}
return(furnitureList);
}
/// <summary>
/// Updates the employee in the employee table.
/// </summary>
/// <param name="employee">Employee object</param>
/// <returns>true if employee is updated successfully</returns>
public static bool UpdateEmployee(Employee employee)
{
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
string sqlStatement = "UPDATE employee" +
" SET first_name = @FirstName, middle_name = @MiddleName, last_name = @LastName, sex = @Gender, " +
"date_of_birth = @DateOfBirth, phone_number=@PhoneNumber, address1=@Address1, address2=@Address2, " +
"city=@City, state=@State, zipcode=@PostalCode, username = @UserName, password = @Password, status = @Status " +
"WHERE employee_id=@EmployeeID";
connection.Open();
using (SqlCommand updateCommand = new SqlCommand(sqlStatement, connection))
{
updateCommand.Connection = connection;
updateCommand.Parameters.AddWithValue("@EmployeeID", employee.EmployeeID);
updateCommand.Parameters.AddWithValue("@FirstName", employee.FirstName);
updateCommand.Parameters.AddWithValue("@MiddleName", employee.MiddleName);
updateCommand.Parameters.AddWithValue("@LastName", employee.LastName);
updateCommand.Parameters.AddWithValue("@Gender", employee.Sex);
updateCommand.Parameters.AddWithValue("@DateOfBirth", employee.DateOfBirth);
updateCommand.Parameters.AddWithValue("@PhoneNumber", employee.Phone);
updateCommand.Parameters.AddWithValue("@Address1", employee.Address1);
updateCommand.Parameters.AddWithValue("@Address2", employee.Address2);
updateCommand.Parameters.AddWithValue("@City", employee.City);
updateCommand.Parameters.AddWithValue("@State", employee.State);
updateCommand.Parameters.AddWithValue("@PostalCode", employee.PostalCode);
updateCommand.Parameters.AddWithValue("@UserName", employee.UserName);
updateCommand.Parameters.AddWithValue("@Password", employee.Password);
updateCommand.Parameters.AddWithValue("@Status", employee.Status);
updateCommand.ExecuteNonQuery();
return(true);
}
}
}
/// <summary>
/// Method that posts the return transactions of a given customer
/// </summary>
/// <param name="returnTransaction">return transaction</param>
/// <param name="transactionList">list of items</param>
/// <returns>true if successfull otherwise false</returns>
public bool PostReturnTransaction(ReturnTransaction returnTransaction, List <ReturnCart> ReturnItemList)
{
SqlTransaction sqlTransaction = null;
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
string sqlStatement = "INSERT INTO Return_Transaction (customer_id, return_date, " +
"checked_in_by, late_fee, refund_amount) " +
"VALUES (@CustomerID, @ReturnDate, @CheckedinBy, @LateFee, @RefundAmount); SELECT SCOPE_IDENTITY() ";
connection.Open();
sqlTransaction = connection.BeginTransaction();
using (SqlCommand insertCommand = new SqlCommand(sqlStatement, connection, sqlTransaction))
{
insertCommand.Connection = connection;
insertCommand.Parameters.AddWithValue("@CustomerID", returnTransaction.CustomerID);
insertCommand.Parameters.AddWithValue("@ReturnDate", returnTransaction.ReturnDate);
insertCommand.Parameters.AddWithValue("@CheckedinBy", returnTransaction.CheckedinBy);
insertCommand.Parameters.AddWithValue("@LateFee", returnTransaction.LateFee);
insertCommand.Parameters.AddWithValue("@RefundAmount", returnTransaction.RefundAmount);
returnTransaction.ReturnTransactionID = Convert.ToInt32(insertCommand.ExecuteScalar());
if (returnTransaction.ReturnTransactionID > 0)
{
this.InsertReturnItem(connection, returnTransaction.ReturnTransactionID, ReturnItemList, sqlTransaction);
}
var rentalIdList = new List <int>();
foreach (ReturnCart returnItem in ReturnItemList)
{
if (!rentalIdList.Contains(returnItem.RentalID))
{
rentalIdList.Add(returnItem.RentalID);
}
this.furnitureDBDAL.UpdateInventory(returnItem.FurnitureID, returnItem.Quantity, connection, sqlTransaction);
}
foreach (int rentalTransactionId in rentalIdList)
{
List <Furniture> furnitureList = this.rentalItemDBDAL.GetRentalItemByTransactionID(rentalTransactionId, connection, sqlTransaction);
bool isCloseTransaction = true;
int totalQuantityRented = 0;
int totalQuantityReturned = 0;
foreach (Furniture furniture in furnitureList)
{
totalQuantityRented = furniture.QuantityOrdered;
totalQuantityReturned = this.GetQuantityReturned(furniture.RentalItemID, connection, sqlTransaction);
if (totalQuantityRented != totalQuantityReturned)
{
isCloseTransaction = false;
}
}
if (isCloseTransaction)
{
this.rentalTransactionDBDAL.CloseRentalTransaction(rentalTransactionId, connection, sqlTransaction);
}
}
sqlTransaction.Commit();
return(true);
}
}
}
/// <summary>
/// Method that returns the selected employee(s) from the employee table
/// </summary>
/// <param name="name">first name last name of the employee</param>
/// <param name="phone">phone number of the employee</param>
/// <param name="customerid">employee id of the employee</param>
/// <returns>Employee object</returns>
public List <Employee> GetEmployees(string name, string phone, int employeeid)
{
List <Employee> employeeList = new List <Employee>();
string sqlStatement = "SELECT employee_id, first_name, middle_name, last_name, sex, " +
"date_of_birth, phone_number, address1, address2, city, state, zipcode, username, password, status FROM employee ";
if (name != null && name.Length > 0)
{
sqlStatement += "where concat(first_name, ' ', last_name) = @Name";
}
else if (phone != null && phone.Length > 0)
{
sqlStatement += "where phone_number = @Phone";
}
else if (employeeid > 0)
{
sqlStatement += "where employee_id = @EmployeeID";
}
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(sqlStatement, connection))
{
if (name != null && name.Length > 0)
{
selectCommand.Parameters.AddWithValue("@Name", name);
}
else if (phone != null && phone.Length > 0)
{
selectCommand.Parameters.AddWithValue("@Phone", phone);
}
else if (employeeid > 0)
{
selectCommand.Parameters.AddWithValue("@EmployeeID", employeeid);
}
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Employee employee = new Employee();
employee.EmployeeID = Convert.ToInt32(reader["employee_id"].ToString());
employee.FirstName = reader["first_name"].ToString();
employee.MiddleName = reader["middle_name"].ToString();
employee.LastName = reader["last_name"].ToString();
employee.Sex = reader["sex"].ToString();
employee.Phone = reader["phone_number"].ToString();
employee.Address1 = reader["address1"].ToString();
employee.Address2 = reader["address2"].ToString();
employee.City = reader["city"].ToString();
employee.State = reader["state"].ToString();
employee.PostalCode = reader["zipcode"].ToString();
employee.DateOfBirth = (DateTime)reader["date_of_birth"];
employee.UserName = reader["username"].ToString();
employee.Password = reader["password"].ToString();
employee.Status = reader["status"].ToString();
employeeList.Add(employee);
}
}
}
}
return(employeeList);
}
/// <summary>
/// Transaction performing the functions of entering a transaction, the rental items in the transaction, and updating the inventory
/// </summary>
/// <param name="transaction">transaction</param>
/// <param name="furnitureList">furniture items</param>
/// <returns>whether functions were committed or not</returns>
public bool EnterRentalTransaction(RentalTransaction transaction, List <Furniture> furnitureList)
{
List <Furniture> addedFurnitureItems = new List <Furniture>();
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
SqlTransaction rentalTransaction = null;
string sqlTransactionStatement = "INSERT INTO RENTAL_TRANSACTION (customer_id, rented_on, due_date, " +
"total_due, checked_out_by, status) " +
"VALUES (@CustomerID, @RentedOn, @DueDate, @TotalDue, @CheckedOutBy, @Status " +
"); SELECT SCOPE_IDENTITY() ";
string sqlQuantityStatement = "UPDATE inventory SET total_available = total_available - @QuantityOrdered " +
"WHERE inventory.furniture_id = @FurnitureID AND total_available = @AvailableQuantity ";
string sqlItemStatement = "INSERT INTO RENTAL_ITEM (rental_id, furniture_id, quantity) " +
"VALUES (@RentalID, @FurnitureID, @QuantityOrdered); SELECT SCOPE_IDENTITY() ";
connection.Open();
rentalTransaction = connection.BeginTransaction();
using (SqlCommand insertTransactionCommand = new SqlCommand(sqlTransactionStatement, connection, rentalTransaction),
updateInventoryCommand = new SqlCommand(sqlQuantityStatement, connection, rentalTransaction),
insertItemCommand = new SqlCommand(sqlItemStatement, connection, rentalTransaction))
{
insertTransactionCommand.Connection = connection;
insertTransactionCommand.Parameters.AddWithValue("@CustomerID", transaction.CustomerID);
insertTransactionCommand.Parameters.AddWithValue("@RentedOn", transaction.RentalDate);
insertTransactionCommand.Parameters.AddWithValue("@DueDate", transaction.DueDate);
insertTransactionCommand.Parameters.AddWithValue("@TotalDue", transaction.TotalDue);
insertTransactionCommand.Parameters.AddWithValue("@CheckedOutBy", transaction.CheckedOutByID);
insertTransactionCommand.Parameters.AddWithValue("@Status", transaction.Status.Trim());
transaction.RentalID = Convert.ToInt32(insertTransactionCommand.ExecuteScalar());
if (transaction.RentalID <= 0)
{
rentalTransaction.Rollback();
return(false);
}
///foreach (Furniture furniture in furnitureList)
for (int index = 0; index < furnitureList.Count; index++)
{
updateInventoryCommand.Connection = connection;
updateInventoryCommand.Parameters.Clear();
updateInventoryCommand.Parameters.AddWithValue("@AvailableQuantity", furnitureList[index].Quantity);
updateInventoryCommand.Parameters.AddWithValue("@QuantityOrdered", furnitureList[index].QuantityOrdered);
updateInventoryCommand.Parameters.AddWithValue("@FurnitureID", furnitureList[index].FurnitureID);
int count = updateInventoryCommand.ExecuteNonQuery();
if (count <= 0)
{
rentalTransaction.Rollback();
return(false);
}
furnitureList[index].RentalTransactionID = transaction.RentalID;
insertItemCommand.Connection = connection;
insertItemCommand.Parameters.Clear();
insertItemCommand.Parameters.AddWithValue("@RentalID", furnitureList[index].RentalTransactionID);
insertItemCommand.Parameters.AddWithValue("@FurnitureID", furnitureList[index].FurnitureID);
insertItemCommand.Parameters.AddWithValue("@QuantityOrdered", furnitureList[index].QuantityOrdered);
furnitureList[index].RentalItemID = Convert.ToInt32(insertItemCommand.ExecuteScalar());
addedFurnitureItems.Add(furnitureList[index]);
if (addedFurnitureItems.Count <= 0)
{
rentalTransaction.Rollback();
return(false);
}
}
}
rentalTransaction.Commit();
return(true);
}
}
/// <summary>
/// Method that returns the customer from customer table
/// </summary>
/// <param name="name">first name last name of the customer</param>
/// <param name="phone">phone number of the customer</param>
/// <param name="customerid">customer id of the customer</param>
/// <returns>list of customer objects</returns>
public List <Customer> GetCustomers(string name, string phone, int customerid)
{
List <Customer> customerList = new List <Customer>();
string sqlStatement = "SELECT customer_id, first_name, middle_name, last_name, sex, " +
"date_of_birth, phone_number, address1, address2, city, state, zipcode from customer ";
if (name != null && name.Length > 0)
{
sqlStatement += "where concat(first_name, ' ', last_name) = @Name";
}
else if (phone != null && phone.Length > 0)
{
sqlStatement += "where phone_number = @Phone";
}
else if (customerid > 0)
{
sqlStatement += "where customer_id = @CustomerID";
}
using (SqlConnection connection = FurnitureRentalsDBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(sqlStatement, connection))
{
if (name != null && name.Length > 0)
{
selectCommand.Parameters.AddWithValue("@Name", name);
}
else if (phone != null && phone.Length > 0)
{
selectCommand.Parameters.AddWithValue("@Phone", phone);
}
else if (customerid > 0)
{
selectCommand.Parameters.AddWithValue("@CustomerID", customerid);
}
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Customer customer = new Customer();
customer.CustomerId = Convert.ToInt32(reader["customer_id"].ToString());
customer.FirstName = reader["first_name"].ToString();
customer.MiddleName = reader["middle_name"].ToString();
customer.LastName = reader["last_name"].ToString();
customer.Gender = reader["sex"].ToString();
customer.HomePhone = reader["phone_number"].ToString();
customer.Address1 = reader["address1"].ToString();
customer.Address2 = reader["address2"].ToString();
customer.City = reader["city"].ToString();
customer.State = reader["state"].ToString();
customer.PostalCode = reader["zipcode"].ToString();
customer.DateOfBirth = (DateTime)reader["date_of_birth"];
customerList.Add(customer);
}
}
}
}
return(customerList);
}
