/// <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);
}
public async Task ReturnCarAsync(int carId)
{
Car car = await _context.Cars.FindAsync(carId);
if (car == null)
{
throw new Exception("Car not found");
}
// ? car is not rented
if (car.IsAvailable == true)
{
throw new Exception("Car cannot be returned because it has not neen rented");
}
RentalTransaction rt = await _context.RentalTransactions.FindAsync(car.CurrentRentalTransactionId);
if (rt == null)
{
throw new Exception("Related rental transaction not found");
}
rt.DateReturned = DateTime.Now;
car.IsAvailable = true;
car.CurrentRentalTransactionId = null;
await _context.SaveChangesAsync();
}
/// <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>
/// Loads grid for rental transactions
/// </summary>
public void LoadRentalTransactionDataGridView()
{
try
{
RentalTransactionDataGridView.AllowUserToAddRows = false;
RentalTransactionDataGridView.RowHeadersVisible = false;
transactionList = this.rentalTransactionController.GetRentalTransactionsByCustomerID(this.currentCustomer.CustomerId);
foreach (RentalTransaction transaction in transactionList)
{
int totalRentalSpan = (int)(transaction.DueDate - transaction.RentalDate).TotalDays;
if (totalRentalSpan == 0)
{
totalRentalSpan = 1;
}
decimal dailyRate = transaction.TotalDue / totalRentalSpan;
int daysSinceRental = (int)(DateTime.Today - transaction.RentalDate).TotalDays;
transaction.CurrentAmountDue = dailyRate * daysSinceRental;
if (transaction.CurrentAmountDue > transaction.TotalDue || daysSinceRental == 0)
{
transaction.CurrentAmountDue = transaction.TotalDue;
}
}
rentalTransactionBindingSource.DataSource = transactionList;
foreach (DataGridViewRow row in RentalTransactionDataGridView.Rows)
{
RentalTransaction transaction = (RentalTransaction)row.DataBoundItem;
}
/*
* RentalTransactionDataGridView.AutoResizeColumns();
* RentalTransactionDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
* RentalTransactionDataGridView.AutoResizeRows();
* RentalTransactionDataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
*
* int width = 0;
*
* foreach (DataGridViewColumn column in RentalTransactionDataGridView.Columns)
* {
* width += column.Width;
* }
* RentalTransactionDataGridView.Width = width;
*/
}
catch (Exception)
{
MessageBox.Show("There was a problem reaching the database. Please check the database connection.");
}
}
private void SubmitRentalButton_Click(object sender, EventArgs e)
{
int value;
if (this.currentCustomer.CustomerId <= 0)
{
MessageBox.Show("Please select a customer before adding items to the cart feature.");
}
else if (!this.CheckRowQuantities())
{
MessageBox.Show("Please make sure a positive integer value is entered for all quantities");
}
else if (string.IsNullOrEmpty(this.RentalTotalTextBox.Text))
{
MessageBox.Show("Please add items to the order before submitting it.");
}
else
{
SubmitTransactionDialog confirmTransactionForm = new SubmitTransactionDialog();
DialogResult addedResult = confirmTransactionForm.ShowDialog();
if (addedResult == DialogResult.OK && int.TryParse(this.DaysRentingTextBox.Text, out value) && int.Parse(this.DaysRentingTextBox.Text) > 0 && this.DaysRentingTextBox.Text.Length > 0)
{
RentalTransaction transaction = new RentalTransaction();
transaction.CustomerID = this.currentCustomer.CustomerId;
transaction.RentalDate = DateTime.Today;
transaction.DueDate = transaction.RentalDate.AddDays(int.Parse(this.DaysRentingTextBox.Text));
transaction.TotalDue = decimal.Parse(this.RentalTotalTextBox.Text.Substring(2));
transaction.CheckedOutByID = this.currentEmployee.EmployeeID;
transaction.Status = "Pending";
bool successfulTransaction = this.rentalTransactionController.EnterRentalTransaction(transaction, furnitureList);
if (!successfulTransaction)
{
MessageBox.Show("The transaction was not successfully processed. Please check all values to ensure that a transaction can be successful.");
}
else
{
MessageBox.Show("The transaction " + "(ID: " + transaction.RentalID + ") was successfully processed");
furnitureList.Clear();
this.LoadRentalDataGridView();
}
}
else
{
MessageBox.Show("The transaction was not successfully processed. Please check all values to ensure that a transaction can be successful.");
}
}
}
/// <summary>
/// Adds the given Rental Transaction to the Repository
/// </summary>
/// <param name="customerId">The Id of the Customer to add the Rental Transaction to</param>
/// <param name="transaction">The Rental Transaction to add</param>
public void AddRentalTransaction(int customerId, RentalTransaction transaction)
{
var customer = GetCustomerById(customerId);
if (customer == null)
{
throw new Exception($"Customer ({customerId}) Not Found");
}
customer.RentalTransactions.Add(transaction);
UpdateCustomer(customer);
}
public async Task RentCarAsync(int carId)
{
Car car = await _context.Cars.FindAsync(carId);
if (car == null)
{
throw new Exception("Car not found");
}
RentalTransaction rt = new RentalTransaction()
{
Id = 0,
CarId = carId,
DateRented = DateTime.Now,
DateReturned = null
};
await _context.RentalTransactions.AddAsync(rt);
await _context.SaveChangesAsync();
car.IsAvailable = false;
car.CurrentRentalTransactionId = rt.Id;
await _context.SaveChangesAsync();
}
/// <summary>
/// Method that adds an return item to the cart
/// </summary>
/// <param name="rentalId">rental id of the returning item</param>
/// <param name="rentalItemId">rental item id of the returning item</param>
/// <param name="furnitureId">furniture id of the returning item</param>
/// <param name="returnQuantity">return quantity of the returning item"></param>
public void addReturn(int rentalId, int rentalItemId, int furnitureId, int returnQuantity)
{
Furniture furniture = this.furnitureController.GetFurnitureByID(furnitureId);
RentalTransaction rentalTransaction = this.rentalTransactionController.GetRentalTransactionsByID(rentalId);
decimal dailyRentalRate = furniture.DailyRentalRate;
decimal dailyFineRate = furniture.DailyFineRate;
DateTime rentedOn = rentalTransaction.RentalDate;
DateTime dueDate = rentalTransaction.DueDate;
ReturnCart returnCartItem = new ReturnCart();
returnCartItem.RentalID = rentalId;
returnCartItem.RentalItemID = rentalItemId;
returnCartItem.FurnitureID = furniture.FurnitureID;
returnCartItem.SerialNo = furniture.SerialNumber;
returnCartItem.ItemRented = furniture.ItemDescription;
returnCartItem.Style = furniture.FurnitureStyle;
returnCartItem.Quantity = returnQuantity;
returnCartItem.LateFee = 0;
returnCartItem.Refund = 0;
int totalQuantity = returnQuantity;
bool itemNotFoundInCart = true;
foreach (ReturnCart returnItem in returnCartItemList)
{
if (returnItem.RentalID == rentalId && returnItem.FurnitureID == furnitureId)
{
returnItem.Quantity += returnQuantity;
totalQuantity = returnItem.Quantity;
itemNotFoundInCart = false;
if (dueDate > DateTime.Now) // refund amount
{
int days = (dueDate - DateTime.Now).Days;
returnItem.Refund = (dailyRentalRate * days * totalQuantity);
}
else if (dueDate < DateTime.Now) // late fee
{
int days = (DateTime.Now - dueDate).Days;
returnItem.LateFee = (dailyFineRate * days * totalQuantity);
}
}
}
if (itemNotFoundInCart)
{
if (dueDate > DateTime.Now) // refund amount
{
int days = (dueDate - DateTime.Now).Days;
returnCartItem.Refund = (dailyRentalRate * days * totalQuantity);
}
else if (dueDate < DateTime.Now) // late fee
{
int days = (DateTime.Now - dueDate).Days;
returnCartItem.LateFee = (dailyFineRate * days * totalQuantity);
}
returnCartItemList.Add(returnCartItem);
}
returnTransaction.LateFee = CalculateLateFee();
returnTransaction.RefundAmount = CalculateRefundAmount();
txtLateFee.Text = Convert.ToString(returnTransaction.LateFee);
txtRefundTotal.Text = Convert.ToString(returnTransaction.RefundAmount);
returnItemBindingSource.DataSource = null;
returnItemBindingSource.DataSource = this.returnCartItemList;
}
/// <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>
/// Adds the and return transaction.
/// </summary>
/// <param name="entity">The entity.</param>
/// <returns></returns>
public int AddAndReturnTransaction(RentalTransaction entity)
{
return(this.rentalTransaction.AddAndReturn(entity));
}
/// <summary>
/// Updates the transaction.
/// </summary>
/// <param name="entity">The entity.</param>
public void UpdateTransaction(RentalTransaction entity)
{
this.rentalTransaction.Update(entity);
}
/// <summary>
/// Deletes the transaction.
/// </summary>
/// <param name="entity">The entity.</param>
public void DeleteTransaction(RentalTransaction entity)
{
this.rentalTransaction.Delete(entity);
}
/// <summary>
/// Adds the specified entity.
/// </summary>
/// <param name="entity">The entity.</param>
public void Add(RentalTransaction entity)
{
this.rentalTransaction.Add(entity);
}
/// <summary>
/// Enters a rental transaction
/// </summary>
/// <param name="transaction">transaction</param>
/// <param name="furnitureList">list of furniture</param>
/// <returns>whether transaction was entered</returns>
public bool EnterRentalTransaction(RentalTransaction transaction, List <Furniture> furnitureList)
{
return(this.rentalTransactionDBDAL.EnterRentalTransaction(transaction, furnitureList));
}
