private void PopulateReturnSelection(ListViewItem item)
{
SerialNumberLabel.Text = item.SubItems[0].Text;
QuantityAvailableLabel.Text = item.SubItems[4].Text;
PopulateQuantityRequired(Int32.Parse(item.SubItems[4].Text));
currentItem = new ReturnableItem();
currentItem.SerialNumber = item.SubItems[0].Text;
currentItem.Description = item.SubItems[1].Text;
currentItem.Style = item.SubItems[2].Text;
currentItem.Category = item.SubItems[3].Text;
currentItem.RentalID = int.Parse(item.SubItems[5].Text);
}
/// <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);
}
