/// <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); }