CREATE PROCEDURE [dbo].[GenerateHallAvailability] @StartPeriod datetime, @EndPeriod datetime, @ConferenceRoomTypeNumbersCSV varchar(255)
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @ArrivalDate VARCHAR(8)
  DECLARE @DepartureDate VARCHAR(8)
  DECLARE @ArrivalTime VARCHAR(5)
  DECLARE @DepartureTime VARCHAR(5)
  DECLARE @ConfTypeNumber int
  DECLARE @Count int
  DECLARE @ConfTypeCount int
  DECLARE @TotalConfTypeCount int
  DECLARE @ID int
  DECLARE @cnt INT
  DECLARE @cnt1 INT
  DECLARE @cnt2 INT
  DECLARE @cnt21 INT
  DECLARE @TmpConfTypeNbr INT
  DECLARE @TmpCount INT
  DECLARE @HallAvailCount INT

  DECLARE @curHallAvail TABLE (
    ID int,
    FromDate datetime,
    ToDate datetime,
    ConferenceRoomTypeNumber int,
	NumberOfAvailHalls int
  )

  DECLARE TmpConfTypeCur CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT DISTINCT Number FROM iter_intlist_to_table(@ConferenceRoomTypeNumbersCSV)
  OPEN TmpConfTypeCur
  FETCH NEXT FROM TmpConfTypeCur INTO @ConfTypeNumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF @ConfTypeNumber > 0
	BEGIN
      SELECT @TotalConfTypeCount = COUNT(*) FROM ConferenceRooms WITH (NOLOCK) WHERE ConferenceRoomTypeNumber = @ConfTypeNumber AND ISNULL(IsTable , 0) = 0 AND ISNULL(MarkAsDeleted, 0) = 0
	  print 'Total count: ' + CONVERT(varchar,@TotalConfTypeCount) + ' for type: ' + CONVERT(varchar,@ConfTypenumber)

	  SET @ID = 1
	  --INSERT INTO @curHallAvail (ID, FromDate, ToDate, ConferenceRoomTypeNumber, NumberOfAvailHalls) VALUES (@ID, '1900-01-01 00:00:00', '1900-01-01 00:00:00', @ConfTypeNumber, @TotalConfTypeCount)

      SET @ArrivalDate = CONVERT(VARCHAR, @StartPeriod,112)
      SET @ArrivalTime = CONVERT(VARCHAR, @StartPeriod,108)
      SET @DepartureDate = CONVERT(varchar, @EndPeriod, 112)
      SET @DepartureTime = CONVERT(VARCHAR, @EndPeriod, 108)

      SELECT DISTINCT COUNT(*) AS cnt, RCL.ConferenceRoomNumber, RCL.ReferenceNumber
      INTO #TRCL
      FROM ReservationConferenceLine RCL WITH (NOLOCK)
      INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = RCL.ReferenceNumber AND Reservations.GuestNumber = RCL.GuestNo AND Reservations.Status <= 5
      WHERE (1=1)
        AND ISNULL(ConferenceRoomNumber, 0) > 0
        AND RCL.BookedConferenceRoomTypeNumber = @ConfTypeNumber
        AND (RCL.ArrivalDate < @DepartureDate AND (RCL.DepartureDate >= @ArrivalDate)
         OR (RCL.ArrivalDate = @DepartureDate AND RCL.ArrivalTime < @DepartureTime))
      GROUP BY RCL.ConferenceRoomNumber, RCL.ReferenceNumber

      SELECT @cnt = SUM(cnt) FROM #TRCL

      SELECT @cnt1 = COUNT(*)
      FROM ReservationConferenceLine RCL WITH (NOLOCK)
      INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = RCL.ReferenceNumber AND Reservations.GuestNumber = RCL.GuestNo AND Reservations.Status <= 5
      WHERE (1=1)
        AND ISNULL(ConferenceRoomNumber, 0) = 0
        AND RCL.BookedConferenceRoomTypeNumber = @ConfTypeNumber
        AND (RCL.ArrivalDate < @DepartureDate AND (RCL.DepartureDate >= @ArrivalDate)
         OR (RCL.ArrivalDate = @DepartureDate AND RCL.ArrivalTime < @DepartureTime))

	  -- Just to show which hall lines is found within the period, remove when sp is released
      -- SELECT * FROM #TRCL ORDER BY ArrivalDate ASC, ArrivalTime ASC, DepartureDate DESC, DepartureTime DESC

      SET @Count = ISNULL(@cnt, 0) + ISNULL(@cnt1, 0)

      print 'count: ' + CONVERT(varchar,@Count)

	  IF @Count = 0
	  BEGIN
	    -- We need to check if there are any Collects conferencerooms occupied:
		--print '@Count is 0 searching for collects for: ' +convert(varchar,@ConfTypeNumber) +'...'
		SET @TmpCount = 0
		DECLARE curTemp CURSOR FOR
          SELECT --ConferenceRoomsXtab.ConferenceRoomNumber,
          --(SELECT Type FROM ConferenceRoomTypes CT WHERE CT.ConferenceRoomTypeNumber = (SELECT ConferenceRoomTypenumber FROM ConferenceRooms CR WHERE CR.ConferenceRoomNumber = ConferenceRoomsXtab.ConferenceRoomNumber)) AS CTType,
          (SELECT ConferenceRoomTypeNumber FROM ConferenceRoomTypes CT WHERE CT.ConferenceRoomTypeNumber = (SELECT ConferenceRoomTypenumber FROM ConferenceRooms CR WHERE CR.ConferenceRoomNumber = ConferenceRoomsXtab.ConferenceRoomNumber)) AS CTTypeID
          FROM ConferenceRoomsXtab
          INNER JOIN conferencerooms ON conferencerooms.ConferenceRoomNumber = ConferenceRoomsXtab.CollectsConferenceRoomNumber
          INNER JOIN CONFERENCEROOMTYPES ON CONFERENCEROOMTYPES.ConferenceRoomTypeNumber = conferencerooms.ConferenceRoomTypeNumber
          WHERE CONFERENCEROOMTYPES.ConferenceRoomTypeNumber = @ConfTypeNumber
        OPEN curTemp
		FETCH NEXT FROM curTemp INTO @TmpConfTypeNbr
		WHILE (@@FETCH_STATUS<>-1)
		BEGIN
		  IF @ConfTypeNumber <> @TmpConfTypeNbr
		  BEGIN
		    --print '@TmpConfTypeNbr: ' + convert(varchar, @tmpconftypenbr)
            SELECT DISTINCT COUNT(*) AS cnt, RCL.ConferenceRoomNumber, RCL.ReferenceNumber
            INTO #TRCL2
            FROM ReservationConferenceLine RCL WITH (NOLOCK)
            INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = RCL.ReferenceNumber AND Reservations.GuestNumber = RCL.GuestNo AND Reservations.Status <= 5
            WHERE (1=1)
              AND ISNULL(ConferenceRoomNumber, 0) > 0
              AND RCL.BookedConferenceRoomTypeNumber = @TmpConfTypeNbr
              AND (RCL.ArrivalDate < @DepartureDate AND (RCL.DepartureDate >= @ArrivalDate)
               OR (RCL.ArrivalDate = @DepartureDate AND RCL.ArrivalTime < @DepartureTime))
            GROUP BY RCL.ConferenceRoomNumber, RCL.ReferenceNumber

            SELECT @cnt2 = SUM(cnt) FROM #TRCL2

            SELECT @cnt21 = COUNT(*)
            FROM ReservationConferenceLine RCL WITH (NOLOCK)
            INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = RCL.ReferenceNumber AND Reservations.GuestNumber = RCL.GuestNo AND Reservations.Status <= 5
            WHERE (1=1)
              AND ISNULL(ConferenceRoomNumber, 0) = 0
              AND RCL.BookedConferenceRoomTypeNumber = @ConfTypeNumber
              AND (RCL.ArrivalDate < @DepartureDate AND (RCL.DepartureDate >= @ArrivalDate)
               OR (RCL.ArrivalDate = @DepartureDate AND RCL.ArrivalTime < @DepartureTime))

            SET @TmpCount = @TmpCount + ISNULL(@cnt2, 0) + ISNULL(@cnt21, 0)
		    --print 'xtab found for:' + convert(varchar,@TmpConfTypeNbr) + ' = ' + CONVERT(varchar, @TmpCount)

		    DROP TABLE #TRCL2
		  END

		  FETCH NEXT FROM curTemp INTO @TmpConfTypeNbr
		END
	    CLOSE curTemp
	    DEALLOCATE curTemp
	  END       

	  SET @HallAvailCount = (@TotalConfTypeCount - @Count)
	  IF @TmpCount > 0
	  BEGIN
	    IF (@TotalConfTypeCount - @TmpCount) <= 0
		  SET @HallAvailCount = 0
	  END

      --print 'Adding to @curHallAvail: ' + convert(varchar, @conftypenumber) + ' availcount: ' + convert(varchar, @HallAvailCount)
	  SET @ID = @ID + 1 
	  INSERT INTO @curHallAvail(ID, FromDate, ToDate, ConferenceRoomTypeNumber, NumberOfAvailHalls) VALUES (@ID, @StartPeriod, @EndPeriod, @ConfTypeNumber, @HallAvailCount)

      DROP TABLE #TRCL
	END
    FETCH NEXT FROM TmpConfTypeCur INTO @ConfTypeNumber
  END

  CLOSE TmpConfTypeCur
  DEALLOCATE TmpConfTypeCur

  SELECT * FROM @curHallAvail
  ORDER BY ID, ConferenceRoomTypeNumber, FromDate ASC, ToDate ASC
END
