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