Imports System.Data.SqlClient

Public Function GetMyDarnTable() As Dataset

     Dim Adataset As Dataset = 'call that stored procedure from code here.
     
     Return Adataset

End Function

USE [ClaytorsCottages]
GO
/****** Object:  StoredProcedure [dbo].[proc_getAvailableRooms]    Script Date: 12/08/2011 16:50:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_getAvailableRooms]
	@Begin DATE,	--User's specified Check-in Date
	@End DATE,		--User's specified Check-out Date
	@Jac BIT,		--Boolean indicating user would like a room with jacuzzi
	@Priv BIT,		--Boolean indicating user would like a room with private access
	@Fire BIT,		--Boolean indicating user would like a room with a fireplace
	@Bed NVarchar(6)--Indicates King, Queen, or Double bed type
	
AS
BEGIN
	BEGIN
		--Consolidate the AvailableRooms table and GuestReservations table
		--such that I can choose from all the rooms and their reservations
		--dates, whether the room is currently reserved or not. 
		SELECT  tblAvailableRooms.AvailID, tblGuestReservations.ReservationID, 
				tblGuestReservations.ResBegin, tblGuestReservations.ResEnd
		INTO #tmpMatches
		FROM tblAvailableRooms
		LEFT JOIN tblGuestReservations
		ON tblAvailableRooms.AvailID=tblGuestReservations.AvailID
		ORDER BY tblGuestReservations.ReservationID DESC
	END
	
	BEGIN
		--Select all of the available rooms for which the Reservation
		--begin and end dates are either NULL, or do not fall between
		--the user's desired begin and end dates. 
		SELECT * INTO #tmpResults FROM #tmpMatches 
		WHERE ((#tmpMatches.ResBegin NOT BETWEEN @Begin AND @End)
		AND (#tmpMatches.ResEnd NOT BETWEEN @Begin AND @End))
		OR (#tmpMatches.ReservationID IS NULL)
	END
	
	--Select all of the available rooms for which the user's begin
	--and end dates do not fall between any current reservation dates
	--as well as those that are null
	BEGIN
		SELECT AvailID, ReservationID INTO #tmpFinalResults
		FROM #tmpResults
		WHERE ((@Begin NOT BETWEEN ResBegin AND ResEnd) 
		AND (@End NOT BETWEEN ResBegin AND ResEnd))
		OR (ReservationID IS NULL)
	END
	
	
	--Call stored procedure stored procedure ConsolidateRoomChoice
	--This procedure is supposed to narrow the selected records
	--based on the user's specified amenities. 
	CREATE TABLE #FinalResults
	(
		AvailID INT,
		Jacuzzi BIT,
		PrivateAccess BIT,
		Fireplace BIT,
		BedType NVarchar(6)
	)
	
	INSERT #FinalResults EXEC [dbo].[proc_ConsolidateRoomChoices]
			@Jacuzzi = @Jac,
			@Private = @Priv,
			@Fireplace = @Fire,
			@BedType = @Bed
			
	SELECT * FROM #FinalResults
	
	RETURN
END

USE [ClaytorsCottages]
GO
/****** Object:  StoredProcedure [dbo].[proc_ConsolidateRoomChoices]    Script Date: 12/08/2011 16:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_ConsolidateRoomChoices]
/*
NAME: proc_ConsolidateRoomChoices
Description: Get the final result set of available rooms matching
				the user's exact specification. 
Author: Amber Sherman
Modification Log: Change

Description								Date		Changed By
Created procedure						11/30/2011	Amber Sherman
*/
-----------------------------------------------------------------------
	@Jacuzzi BIT,
	@Private BIT,
	@Fireplace BIT,
	@BedType NVarchar(6)	
AS
BEGIN
		BEGIN
				--Get the AvailID, the BedID, and the RoomTypeID
				--and stuff them into a temporary table so I have 
				--something organized to choose from. 
				SELECT B.AvailID, A.BedID, A.RoomTypeID 
				INTO #tempA
				FROM tblAvailableRooms AS A
				INNER JOIN #tmpFinalResults AS B
				ON A.AvailID = B.AvailID
					
				--Join the temporary table, the room types, and the bed times
				--along with all desired amenities, to be narrowed down.
				SELECT A.AvailID, B.Jacuzzi, B.PrivateAccess, B.Fireplace, C.BedType
				INTO #tempB
				FROM tblRoomTypes As B
				INNER JOIN #tempA AS A ON A.RoomTypeID = B.RoomTypeID
				INNER JOIN tblBedTypes AS C ON A.BedID = C.BedID
				ORDER BY A.AvailID
				
				--Create a table variable to put the result set into
				DECLARE @Final TABLE 
				(
					AvailID INT,
					Jacuzzi BIT,
					PrivateAccess BIT,
					Fireplace BIT,
					BedType NVarchar(6)
				) 
				
				--Fill the table variable with results from the temporary table
				--that exactly match the user's specifications
				INSERT INTO @Final(AvailID, Jacuzzi,PrivateAccess, Fireplace, BedType)
					SELECT * FROM #tempB
					WHERE(Jacuzzi = @Jacuzzi AND PrivateAccess = @Private
						AND Fireplace = @Fireplace AND BedType = @BedType)
					ORDER BY AvailID

				SELECT * FROM @Final			
			RETURN
		END
END