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