CREATE PROCEDURE [dbo].[GenerateAvailability] @StartDate datetime, @EndDate datetime, @OnlyAllotment bit, @TheHotelNumber VARCHAR(500), @RealSystemDateStr CHAR (8) = '', @SpecifyOnRoomTypeSegPos BIT = 1, @NightAudit BIT = 0, @ARoomTypeStr VARCHAR(1000) = '', @AProductNumber VARCHAR(1000) = '', @ASoldRoomType INT = 2, @AIncludeOccupiedInOFS BIT = 0, @CalculateOccupied BIT = 1, @AUpdateRoomBookingStatus BIT = 0, @ARegenerateRoomBookingStatus BIT = 0, @AAllotmentNumber INT = 0
AS
SET NOCOUNT ON
/*
  KJ: Save RoomBookingStatusHist.
   AMH/20220120: CR213986: OutOfOrder was filtered out due to not having a reservationaddress 
   KC/20211116: Number of rooms counting was failing when name list per person and one of the reservations in room was cancelled
   MJ/KJ 20210930: SP rewritten for optimization.	
   AMH/20200310: CR183431: Insale was calculated incorrectly for +Products
   KC/20200115: Allotments are saved as soldroomtype = 0 in #RoomBookingStatus.
   KC/20200115:  Parameters with default values is now set to default value if parameter is NULL	
   AMH/20191202: CR174500: Allotments was not returned in result when excluding owner reservations
   AMH/20191122: CR173689: Linked reservations departing later than DayroomsLaterThanTime on an
                 earlier date than the final departure date was regarded as a day room on the
                 final departure date.
   KJ: 20161232: Result set not equal for system date and other dates.
   8.3:
   BA/20160317 Fixing RequestorID so it isn't NULL when inserted into the RoomBookingStatus table.
   BA/20160316 Adding RequestorID calculations. Not implemented for online/RoomBookingStatus2.

   SG/2014-06-17: Allow NULL in #RoomBookingStatus.ProductNumber
   4.3:
   BA/20130925: #ProcessedRoomLines created a constrained primary key. All constraints must be unique
                in the database. Therefore the primary key is now a name less key so MSSQL assigns it
                a name it self. 
   KC/20130920: Fixing error regarding indexes on #ProcessedRoomLines
   KC/20120305:	Including count of owner bookings.
   BA/20110902: CR033070: Creating a reservation on Hotel A with an Allotment, but having MixedResources 
                enabled and booking the Rooms on the Roomline fra Hotel B makes the SoldRooms column
                on the RoomTypes plan fail. The SoldRooms goes to Hotel A and not Hotel B as it should.
   BA/20100928: CR016840: Some scenarious can make it look like that a Double Booking has occured. It 
                can be done by moving an Inhouse reservation to an room where a guest has just left. 
                Therefore only activate the Double Booking Calculations if it is activated in the
                HotelSetup.
   BA/20100518/ DoubleBooking/Do Count multiple res.no. on same rooms as one Sold Room!
      20100519: We must only count SoldRooms pr. room even if two or more ReferenceNumbers exists 
                in the room, but all other Fields in the ROOMBOOKINGSTATUS table must be counted as
                it used to be done. This can lead to a mismatch between adding NumberOfConfirmed,
                NumberOfProvisionals, NumberOfGuaranteed, etc. and SoldRooms.
                Also we must include ArrivalTime and DepartureTime due to the existence of dayrooms. 
                We cannot do calculate SoldRooms enterily on ArrivalDate and DepartureDate.
   3.39 - 4.2:
   KJ 20090901: Disable save availability for now
   BA 20090609: CR008736/Reenabled due to a lot of negativ allotment all over the system
   KJ 20090518: Added save availability in table Availability as part of nightaudit. 
   AV 2008 1030 dont say = null, say is null. Else sql will fail
   4.1; 
   BA 20090401: CR007579: Try to aviod Lock time out when opening the Roomtypes plan
   BA 20090317: CR006600/Roomtypes plan calculated Insale price wrong. The Insale price (Sold rooms) only showed the price for one room and not for all rooms.
   3.39: 
   JP 20070807: BUG: When CopySave to a new reservation allotment gets modified eventhough you tell it not to because of this code. Let's find the problems in the source instead of patching our code in this way. Unclear if the following line is a good idea (SET @TotalChange = @InitialCount - @CurrentCount)
   JP 20070725: BUG: handle a end date before start date by swapping dates
   KJ 20070716: Added
    for selecting only some roomtypes
    3.38:
    JP 20070501: CLEANUP
    JP 20070411: BUG: fix for dayrooms in the future
    JP 20070215: EXT: AllotmentsDeletedBefore
    JP 20060627: OPTIMIZE: added dbo. to tables, views and stored procedures
    3.37:
    3.36:
    JP 20060901: BUG: dayrooms on departuredate was not counted correctly at Fangel
    JP 20060523: BUG: runofhouse not performed because currentcount was used. using 0 will make allotmentmodify be called instead
    JP 20060504: BUG: causes left over allotment in release period when changing roomtype. run of house is not done!
    JP 20060427: BUG: KA: when not MPMS, remove hotelnumber from resources
    JP 20060427: BUG: code which called ga_allotmentrelease made SP take 24 seconds (even when not called), where changing code to a simple update/insert line made it take 8 seconds. Weird
    JP 20060320: BUG: fix for missing rows in AllotmentLines table giving wrong bookingstatus. the code now detects missing rows and adds them before releasing allotment
    JP 20051205: OPTIMIZE: do not save data in roombookingstatus for future since it is never used anyways
    JP 20051122: BUG: Segment and PointOfSale still causes problems
    JP 20051115: BUG: Cannot handle empty Segment or PointOfSale tables
    JP 20051026: OPTIMIZE: less reading of data in the past, plus cleaned up whole proc with proper identation and removed old remarked code 
    JP 20051025: OPTIMIZE: NO NEED TO READ DATA FROM RESERVATIONS WHEN LOOKING IN THE PAST. MUCH FASTER OCCUPANCY PLAN NOW
    JP 20051014: EXT:  DO NOT DELETE FROM ROOMBOOKINGSTATUS AND RoomBookingStatusInsale WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS
    JP 20050927: OPTIMIZE: use BETWEEN for datestamp
    JP 20050926: OPTIMIZE: removed code that fixes negative allotment. It is simply too slow now when calling function. allotment should never be negative!
    3.35:
    JP 20050912 BUG: X - 0 = 0 then X is 0 too!
    JP 20050725 BUG: test for fetch_status <> -2
    JP 20050613 ext: changed so CurrentCount is not used from AllotmentLines table
    JP 20050519 bug: NumberOfDayRooms might be NULL
    JP 20050318 bug: look at VacantRoomsInclAllotment hotelsetting (bel-air)
    AV 20050131 changed: put af default constraint on hotelnumber in roombookingstatus insale table when creating it
    JP 20040109 changed: optimized: minor changes to make smaller SQL's
    JP 20041206 bug: insale prices. NULL -> 0
    JP 20041206 bug: only change insale when called from nightaudit and not when regen. bookingstatus
    JP 20041202 changed: use of @NightAudit instead of selecting it from database
    NB 20041125 bug: Select at end only if not called from Nigthaudit
    JP 20041119 changed: soldprice from firstprice and not totalprice
    AV 20041118 changed: allotment section: if pos and seg number is null then set them to single seg and pos number (Happens when no bookings exist in period but allotments exist )
    JP 20041115 changed: fixed bug in 1.07 when hotelnumber, in Allotment table, was null
    JP 20041103 changed: renamed GenerateAvailabilityView to TGenerateAvailabilityView
    JP 20041101 changed: finished SoldPrice
    JP 20041029 changed: added SoldPrice
    JP 20041020 changed: fixed allotment bug at Skallerup
    JP 20040915 changed: fixed bug when @SpecifyOnRoomTypeSegPos=0
    JP 20040715 changed: fixed Released period can be modified -> not released allotment. ReleaseAllotment routine is called again always
    JP 20040623 changed: fixed PaxAllotment which was multiplied with itself pretty much
    JP 20040617 changed: fixed so avail can be recalced in the past and does not wipe allotment reservations (bornholm)
    JP 20040614 changed: fixed: problem with outoforder not being linked correctly to hotel when SP is called for all hotels (-1)
    JP 20040526 changed: optimized: Removed NumberOfOutOfHouseDayRooms field
    JP 20040525 changed: optimized RoomBookingStatusInsale table so it does not contain 0's.     Changed so roombookingstatus is correctly divided on hotelnumber instead of -1 when chain.     Use of own view instead of TMultiPurposeView
    JP 20040518 changed: less locking of AllotmentLines table by using PaxAllotment from RoomBookingStatus instead. Removed indexes as they slowed down the last select from temp table when order by datestamp. Only release allotment in the nightaudit
    JP 20040517 changed: do not delete all from RoomBookingStatusInsale, only current hotelnumber!
    JP 20040504+0506 changed. allotment divided on hotelnumber, outoforder on allotment (removed again), outoforder per hotel. Updated since allotmentlines are not divided on hotelnumber we have to remove it completly and do something else in Picasso. Tough luck :(
    JP 20040430 changed. fixed insale when allotment not incl
    JP 20040428 changed. moved code from AllotmentModify
    JP 20040419 dayroom changes
    JP 31/03-2004 Jacob Pedersen.   122655 times and counting ;)
    CREATED BY JACOB PEDERSEN along time ago

    NOTES:
      - OnlyAllotment means that only the AllotmentLines table is updated with CurrentCount, Adults, Children1+2. RoomBookingStatus is not modified <--- DOES NOT WORK ANYMORE SO IT IS DISABLED!
      - DO NOT REMOVE COLLATE FROM MASTER DATABASE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
      - There is a huge problem in the whole database design and this stored proc. We count the rooms looking at the whole stay period of a room. Here are two senarios:
        1. If we have 2 linked guests where their period are apart, the room will be blocked in the hole.
        2. If we have linked guests which have different statuses we have no way to make sure that bookingstatus is even counted correctly or columns like Tentative and Waitinglist contain the right numbers. Consider this.
            A person is here for 1 day. But he wishes to stay for another, but the hotel is booked so they add a second row to the room (linked guest) which is then set Waitinglist.  This SP cannot handle this at all. So for now they must be created as single subres and not linked subres
*/
DECLARE @Print BIT
SET @Print = 0

-- BA/090401: CR007579: Try to aviod Lock time out when opening the Roomtypes plan
DECLARE @LockTimeOut INT
SET @LockTimeOut = @@LOCK_TIMEOUT
SET LOCK_TIMEOUT 10000 --nb20060217 Do not remove locks will be caused 

SET @RealSystemDateStr = ISNULL(@RealSystemDateStr, '')
SET @SpecifyOnRoomTypeSegPos = ISNULL(@SpecifyOnRoomTypeSegPos, 1)
SET @NightAudit = ISNULL(@NightAudit, 0)
SET @ARoomTypeStr = ISNULL(@ARoomTypeStr, '')
IF @SpecifyOnRoomTypeSegPos=0
	SET @ASoldRoomType = 0
ELSE
	SET @ASoldRoomType = ISNULL(@ASoldRoomType, 2)
SET @AIncludeOccupiedInOFS = ISNULL(@AIncludeOccupiedInOFS, 0)
SET @CalculateOccupied = ISNULL(@CalculateOccupied, 1)
SET @AUpdateRoomBookingStatus = ISNULL(@AUpdateRoomBookingStatus, 0)
SET @ARegenerateRoomBookingStatus = ISNULL(@ARegenerateRoomBookingStatus, 0)
SET @AAllotmentNumber = ISNULL(@AAllotmentNumber, 0) 

DECLARE
@FormatedStartDate  CHAR (8),
@FormatedEndDate    CHAR (8),
@SystemDateStr      CHAR (8),
-- BA/20100518: DoubleBooking/Count as one
-- BA/20100928: CR016840
@IsDoubleBookingEnabled BIT,
-- BA/20110902: CR033070
@BookMixedResourcesEnabled BIT,
@IncludeOccupiedInOFS BIT,
@IncludeOccupiedInOFSStr VARCHAR(100),
@STARTTime DATETIME

SET @STARTTime = Current_timeStamp

select @IncludeOccupiedInOFSStr = hv.KeyValue from hotelsetup h (nolock)
inner join HotelSetupValueXTab hvx (nolock) on h.HotelSetUpNumber = hvx.HotelSetupNumber
inner join HotelSetupValue hv (nolock) on hvx.FK_ValueNumber = hv.PK_ValueNumber
where SearchKey = 'IncludeOccupiedInOFS'

SET @IncludeOccupiedInOFS = CAST( @IncludeOccupiedInOFSStr AS BIT)

IF @IncludeOccupiedInOFS IS NULL
BEGIN
	SELECT @IncludeOccupiedInOFS = KeyValue FROM HotelSetup  WITH (NOLOCK) WHERE SEARCHKEY = 'IncludeOccupiedInOFS'
	SET @IncludeOccupiedInOFS = ISNULL(@IncludeOccupiedInOFS, 0)
END

SELECT @IsDoubleBookingEnabled = KeyValue FROM HotelSetup  WITH (NOLOCK) WHERE SEARCHKEY = 'EnableDoubleBooking'
SET @IsDoubleBookingEnabled = ISNULL(@IsDoubleBookingEnabled, 0)


SELECT @BookMixedResourcesEnabled = KeyValue FROM HotelSetup  WITH (NOLOCK) WHERE SEARCHKEY = 'BookMixedResourcesEnabled'
SET @BookMixedResourcesEnabled = ISNULL(@BookMixedResourcesEnabled, 0)

IF @EndDate > @StartDate
BEGIN
  SET @FormatedStartDate = CONVERT (CHAR(8), @StartDate, 112)
  SET @FormatedEndDate = CONVERT (CHAR(8), @EndDate, 112)
END
ELSE -- Swap dates
BEGIN
  SET @FormatedStartDate = CONVERT (CHAR(8), @EndDate, 112)
  SET @FormatedEndDate = CONVERT (CHAR(8), @StartDate, 112)
END

IF @ARegenerateRoomBookingStatus = 1
	SET @SystemDateStr = @FormatedStartDate
ELSE
	SELECT @SystemDateStr = SystemDate FROM dbo.System (NOLOCK) WHERE Number = 1  -- Only one record

DECLARE @HotelNumbers TABLE
(
    HotelNumber int NOT NULL,
	HotelCustomerNumber int NOT NULL
)
IF @TheHotelNumber = '-1' OR @TheHotelNumber IS null
SET @TheHotelNumber = ''

IF @TheHotelNumber IS NOT NULL AND @TheHotelNumber <> '' AND @TheHotelNumber <> '-1'
INSERT INTO @HotelNumbers (HotelNumber, HotelCustomerNumber)
SELECT DISTINCT HotelNumber, CustomerNumber FROM Customer C (NOLOCK)
INNER JOIN DBO.iter_intlist_to_table(@TheHotelNumber) L ON L.number=C.HotelNumber
WHERE ISNULL(HotelNumber, -1) > -1 AND MarkAsDeleted = 0 AND CustomerTypeNumber=7
ELSE
INSERT INTO @HotelNumbers (HotelNumber, HotelCustomerNumber)
SELECT DISTINCT HotelNumber, CustomerNumber FROM Customer (NOLOCK)
WHERE ISNULL(HotelNumber, -1) > -1 AND MarkAsDeleted = 0 AND CustomerTypeNumber=7
;
DECLARE @SystemDate datetime = CAST(@SystemDateStr AS datetime)
DECLARE @TmpDate datetime = @StartDate
IF @SystemDate > @TmpDate
	SET @TmpDate = @SystemDate
DECLARE @Dates TABLE
(
	CurrentDate datetime
)
WHILE @TmpDate <= @EndDate
BEGIN
	INSERT INTO @Dates VALUES (@TmpDate)
	SET @TmpDate = DATEADD(day, 1, @TmpDate)
END


if @Print=1
begin
  DECLARE @DADATE VARCHAR(255)
  DECLARE @TINT INT
  SET @TINT = 0
  select @DADATE=convert(varchar, getdate (),121)

  print '1.00: '+@DADATE
END

IF @FormatedEndDate >= @SystemDateStr  -- NO NEED TO READ DATA FROM RESERVATIONS WHEN LOOKING IN THE PAST. MUCH FASTER OCCUPANCY PLAN NOW
BEGIN
--  IF @OnlyAllotment <> 0      -- JP 17/6-2002 doesn't work anymore as we are using temp tables!
--    SET @OnlyAllotment = 0    --
  DECLARE
  @AllotmentNumber	Int,
  @FreeRoomsAllotment	SmallInt,

  @PaxAllotment		Smallint,

  @InitialCount		Smallint,
  @CurrentCount		Smallint,
  @TotalChange 		Smallint,

  @AvailDate 		datetime,
  @RoomTypeNumber	INT,
  @DateStamp 		char (8),
  @SoldRooms 		Smallint,
  @SoldRoomsCourse  int,
  @SoldRoomsAttendee int,
  @SoldPrice		MONEY,
  @Adults		SmallInt,
  @Children1		SmallInt,
  @Children2		SmallInt,
  @NumberOfConfirmed	Smallint,
  @NumberOfProvisionals	Smallint,
  @NumberOfGuaranteed	Smallint,
  @NumberOfTentative	Smallint,
  @NumberOfWaitinglists	Smallint,
  @NumberOfDayrooms	Smallint,
  @NumberOfArrival Smallint, 
  @NumberOfInhouse Smallint, 
  @NumberOfStay Smallint, 
  @NumberOfDeparture Smallint, 
  @ArrivalPax Smallint, 
  @InhousePax Smallint, 
  @StayPax Smallint, 
  @DeparturePax Smallint,
  @NumberOfWaitingArrival Smallint,
  @NumberOfTentativeArrival SmallInt,
  @WaitingArrivalPax Smallint,
  @TentativeArrivalPax SmallInt,
  @NumberOfOutOfOrder	Smallint,
  @NumberOfOutForService smallint,
  @NumberOfOccupied smallint,
  @NumberOfOccupiedGroup smallint,

  @SegmentNumber	INT,
  @PointOfSaleNumber	INT,
  @SoldRoomType		INT,

  @AllocatedTypeNumber INT,
  @ParentTypeNumber INT,

  @ReservationType smallint,
  @ProductNumber INT,
  @HotelNumber Int,
  @NumberOfRooms SmallInt,

  @NumberOfRoomsSoldToday smallINT,

  @IncludeAllotmentInInsale BIT,
  @IncludeTentative BIT,
  @VacantRoomsInclAllotment BIT,
  @IncludeDayroomsInSoldRooms BIT,
  @DayroomsLaterThanTime CHAR (5),
  @SingleSegmentNumber INT,
  @SinglePointOfSaleNumber INT,
  @CentralOfficeType SMALLINT,
  @AllotmentOverbookType INT,
  @ReservationGroup INT,
  @RequestorID INT

  SELECT @CentralOfficeType=CONVERT(SMALLINT,KeyValue) FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='CentralBookingType'
  IF @CentralOfficeType IS null
    SET @CentralOfficeType = 0
  SELECT @IncludeAllotmentInInsale=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='IncludeAllotmentInInsale'
  if @IncludeAllotmentInInsale IS null
    Set @IncludeAllotmentInInsale = 0
  SELECT @IncludeTentative=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='IncludeTentative'
  if @IncludeTentative IS null
    Set @IncludeTentative = 0
  SELECT @IncludeDayroomsInSoldRooms=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='IncludeDayroomsInSoldRooms'
  if @IncludeDayroomsInSoldRooms IS null
    Set @IncludeDayroomsInSoldRooms = 0
  SELECT @VacantRoomsInclAllotment=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='VacantRoomsInclAllotment'
  if @VacantRoomsInclAllotment IS null
    Set @VacantRoomsInclAllotment = 0
  SELECT @AllotmentOverbookType=CONVERT(INT,KeyValue) FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='AllotmentOverbookingType'
  IF @AllotmentOverbookType IS NULL
	SET @AllotmentOverbookType = 3


  SELECT @DayroomsLaterThanTime=KeyValue FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='DayroomsLaterThanTime'
  IF @DayroomsLaterThanTime IS null
    Set @DayroomsLaterThanTime = '13:00' -- default

  SELECT TOP 1 @SingleSegmentNumber = Number FROM dbo.Segments (NOLOCK) WHERE Number>0 and (MarkAsDeleted='0') OR (MarkAsDeleted is null)  -- Get any SEG to put it on. Only used when checking for overbook
  IF ISNULL(@SingleSegmentNumber, 0) = 0
	SET @SingleSegmentNumber = 0
  SELECT TOP 1 @SinglePointOfSaleNumber = Number FROM dbo.PointOfSales (NOLOCK) WHERE Number>0 and (MarkAsDeleted='0') OR (MarkAsDeleted is null) -- Get any POS to put it on. Only used when checking for overbook
  IF ISNULL(@SinglePointOfSaleNumber, 0) = 0
	SET @SinglePointOfSaleNumber = 0

  -- Check if StartDate or EndDate before Systemdate -> change!
  IF @RealSystemDateStr = ''
    SET @RealSystemDateStr = @SystemDateStr -- for allotment release and dayrooms

  -- Delete generated lines from GenerateAvailability which are only used for checking for overbooking
  SET @SoldRoomType = 0

  IF @NightAudit > 0
  BEGIN
    DECLARE
    @ReservationsDeletedBefore DATETIME, -- The stored date that we before have deleted before. This can never be set to a value lower than it currently is!
    @tmpString CHAR(8)

    SELECT @tmpString=IsNull(KeyValue,0) FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='ReservationsDeletedBefore' -- SAME CODE IS FOUND IN DELETEOLDRESERVATIONS SP
    IF IsNull(@tmpString, 0) = 0
      SET @ReservationsDeletedBefore = CONVERT(DATETIME, '19800101', 112) -- BEFORE: first picasso installation and before data in first converted installation
    ELSE
      SET @ReservationsDeletedBefore = CONVERT(DATETIME, @tmpString, 112)

    SELECT @tmpString=IsNull(KeyValue,0) FROM dbo.HotelSetup (NOLOCK) WHERE SearchKey='AllotmentsDeletedBefore' -- SAME CODE IS FOUND IN DELETEOLDALLOTMENTS SP
    IF (IsNull(@tmpString, 0) > 0) AND (CONVERT(DATETIME, @tmpString, 112) > @ReservationsDeletedBefore)
      SET @ReservationsDeletedBefore = CONVERT(DATETIME, @tmpString, 112)

    DELETE FROM dbo.AllotmentLines WHERE (InitialCount = 0) AND (TotalChange = 0) and (dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp) = 0) -- X - 0 = 0 then X is 0 too!
  END -- IF @NightAudit > 0
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	if @Print=1
	begin
	select @DADATE=convert(varchar, getdate (),121)
	print '1.02: '+@DADATE
	PRINT '@FormatedEndDate'
	PRINT @FormatedEndDate
	PRINT '@FormatedStartDate'
	PRINT @FormatedStartDate
	PRINT '@SystemDateStr  '
	PRINT @SystemDateStr
	end


SELECT
  LINENUMBER
  , BookedTypeNumber, ISNULL(RoomNumber,0) RoomNumberID, NumberOfRooms, CAST(ArrivalDate AS datetime) SpecArrivalDate, CAST(DepartureDate AS datetime) SpecDepartureDate, Adults SpecAdults, Children1 SpecChildren1, Children2 SpecChildren2,
  ArrivalTime SpecArrivalTime, DepartureTime SpecDepartureTime, Price SoldPrice, ISNULL(FK_ReservationGroup, 0) SpecReservationGroup, 
  ISNULL(ProductNumber, 0) as SpecProductNumber, ISNULL(ProductNumber2, 0) as SpecProductNumber2,
  ISNULL(ProductNumber3, 0) as SpecProductNumber3, ISNULL(ProductNumber4, 0) as SpecProductNumber4, 
  ISNULL(Day2, 0) as SpecDay2, ISNULL(Day3, 0) as SpecDay3, ISNULL(Day4, 0) as SpecDay4,
  FirstPrice2 as SpecFirstPrice2, FirstPrice3 as SpecFirstPrice3, FirstPrice4 as SpecFirstPrice4, StatusRL
INTO #T1
FROM dbo.RESERVATIONROOMLINE WITH (NOLOCK) 
WHERE
	ArrivalDate <= @FormatedEndDate 
	AND DepartureDate >= @FormatedStartDate 
	AND DepartureDate >= @SystemDateStr

SELECT
  T1.LINENUMBER
  , T1.BookedTypeNumber, T1.RoomNumberID, T1.NumberOfRooms, T1.SpecArrivalDate, T1.SpecDepartureDate, T1.SpecAdults, T1.SpecChildren1, T1.SpecChildren2,
  T1.SpecArrivalTime, T1.SpecDepartureTime, T1.SoldPrice, T1.SpecReservationGroup, 
  T1.SpecProductNumber, T1.SpecProductNumber2,
  T1.SpecProductNumber3, T1.SpecProductNumber4, 
  T1.SpecDay2, T1.SpecDay3, T1.SpecDay4,
  T1.SpecFirstPrice2, T1.SpecFirstPrice3, T1.SpecFirstPrice4, T1.StatusRL,
  CASE WHEN RoomNumberID > 0 THEN R.HotelNumber ELSE RT.HotelNumber END HotelNumber,
  CASE WHEN RoomNumberID > 0 THEN R.RoomTypeNumber ELSE RT.RoomTypeNumber END AllocatedTypeNumber,
  CASE WHEN RoomNumberID > 0 THEN RT2.ParentRoomTypeNumber ELSE RT.ParentRoomTypeNumber END ParentTypeNumber,
  CASE WHEN RoomNumberID > 0 THEN R.CustomerNumber ELSE 0 END RoomCustomerNumber
  INTO #T2
  FROM #T1 AS T1
  LEFT JOIN dbo.ROOMS R WITH (NOLOCK) ON R.RoomNumber = T1.RoomNumberID 
  LEFT JOIN dbo.RoomTypes RT2 (Nolock) ON R.RoomTypeNumber = RT2.RoomTypeNUmber
  LEFT JOIN dbo.ROOMTYPES RT WITH (NOLOCK) ON RT.RoomTypeNumber = T1.BookedTypeNumber 

DROP TABLE #T1
-- TEST
-- SELECT * FROM #T2 WHERE SpecArrivalDate <= '20210916' AND SpecDepartureDate >= '20210916' AND AllocatedTypeNumber=4 AND SpecProductNumber=71

SELECT
  T2.LINENUMBER
  , T2.BookedTypeNumber, T2.RoomNumberID, T2.NumberOfRooms, T2.SpecArrivalDate, T2.SpecDepartureDate, T2.SpecAdults, T2.SpecChildren1, T2.SpecChildren2,
  T2.SpecArrivalTime, T2.SpecDepartureTime, T2.SoldPrice, T2.SpecReservationGroup, 
  T2.SpecProductNumber, T2.SpecProductNumber2,
  T2.SpecProductNumber3, T2.SpecProductNumber4, 
  T2.SpecDay2, T2.SpecDay3, T2.SpecDay4,
  T2.SpecFirstPrice2, T2.SpecFirstPrice3, T2.SpecFirstPrice4,
  CAST(RR.ArrivalDate as datetime) ArrivalDate, CAST(RR.DepartureDate AS datetime) DepartureDate, RR.Adults, RR.Children1, RR.Children2 , RR.ReferenceNumber, RR.GuestNumber,
  RR.ArrivalTime, RR.DepartureTime, ISNULL(RR.MARKETSEGMENT, 0) SegmentNumber, ISNULL(RR.POINTOFSALE, 0) PointOfSaleNumber,  
  ISNULL(RR.ProductNumber, 0) as ProductNumber, ISNULL(RR.ProductNumber2, 0) as ProductNumber2, 
  ISNULL(RR.ProductNumber3, 0) as ProductNumber3, ISNULL(RR.ProductNumber4, 0) as ProductNumber4,
  ISNULL(RR.Day2, 0) as Day2, ISNULL(RR.Day3, 0) as Day3, ISNULL(RR.Day4, 0) as Day4,
  RR.FirstPrice2, RR.FirstPrice3, RR.FirstPrice4,
  ISNULL(RR.FK_ReservationGroup, 0) ReservationGroup, 
  CASE WHEN @SpecifyOnRoomTypeSegPos = 1 THEN ISNULL(RR.RequestorID, 0) ELSE 0 END RequestorID,
  ISNULL(RRM.AllotmentNumber, 0) AllotmentNumber, 
  CASE WHEN dbo.RESERVATIONADDRESS.LINK=T2.RoomCustomerNumber THEN 1 ELSE 0 END SoldRoomType,
  T2.HotelNumber, T2.AllocatedTypeNumber, ISNULL(T2.ParentTypeNumber, 0) ParentTypeNumber,
  CASE WHEN (RLX.GuestNumber > 0 OR ISNULL(T2.LineNumber, 0) = 0 OR ISNULL(T2.StatusRL, - 1) = -1) 
	THEN ISNULL(RR.Status, 0) ELSE ISNULL(T2.StatusRL, 0) END Status, 
  --(SELECT COUNT(*) FROM dbo.RESERVATIONROOMLINEXTAB WITH (NOLOCK) WHERE (LINENUMBER = T2.LINENUMBER)) LinkedRes,
 
  ISNULL(RRX.FK_ReferenceNumber, 0) RR_ReferenceNumber, ISNULL(RRX.FK_ReferenceNumberSub, 0) RR_ReferenceNumberSub,
  HN.HotelCustomerNumber
INTO #T3
FROM #T2 AS T2 
  INNER JOIN  dbo.RESERVATIONROOMLINEXTAB RLX WITH (NOLOCK) ON T2.LINENUMBER = RLX.LINENUMBER 
  INNER JOIN dbo.RESERVATIONS RR WITH (NOLOCK) ON RLX.ReferenceNumber = RR.ReferenceNumber AND RLX.GuestNumber = RR.GuestNumber 
  INNER JOIN dbo.RESERVATIONMAIN RRM WITH (NOLOCK) ON RR.ReferenceNumber = RRM.REFERENCENUMBER
  INNER JOIN @HotelNumbers HN ON HN.HotelNumber=T2.HotelNumber
  LEFT OUTER JOIN dbo.RESERVATIONADDRESSXTAB RAX WITH (NOLOCK) ON RR.ReferenceNumber = RAX.REFERENCENUMBER 
    AND RR.GuestNumber = RAX.GUESTNUMBER AND RAX.AddressTypeNumber = 1
  LEFT OUTER JOIN  dbo.RESERVATIONADDRESS WITH (NOLOCK) ON RAX.ADDRESSNUMBER = dbo.RESERVATIONADDRESS.AddressNumber
  LEFT OUTER JOIN dbo.ReservationReservationXTAB RRX ON RR.ReferenceNumber = RRX.FK_ReferenceNumberSub
-- TEST
-- SELECT * FROM #T3 WHERE SpecArrivalDate <= '20210916' AND SpecDepartureDate >= '20210916' AND AllocatedTypeNumber=4 AND SpecProductNumber=71 And SegmentNumber=3 AND PointOfSaleNumber=20 and ReservationGroup=1

DROP TABLE #T2

SELECT 
  LINENUMBER,
  ReferenceNumber, GuestNumber,
  BookedTypeNumber, RoomNumberID, NumberOfRooms, SpecArrivalDate, SpecDepartureDate, SpecAdults, SpecChildren1, SpecChildren2,
  SpecArrivalTime, SpecDepartureTime, SoldPrice, SpecReservationGroup, 
  SpecProductNumber, SpecProductNumber2,
  SpecProductNumber3, SpecProductNumber4, 
  SpecDay2, SpecDay3, SpecDay4,
  SpecFirstPrice2, SpecFirstPrice3, SpecFirstPrice4,
  CASE WHEN (GuestNumber = 0) or ReferenceNumber IN (0, -2, -3, -4) THEN SpecArrivalDate ELSE ArrivalDate END ArrivalDate,
  CASE WHEN GuestNumber = 0 or ReferenceNumber IN (0, -2, -3, -4) THEN SpecDepartureDate ELSE DepartureDate END DepartureDate,
  CASE WHEN GuestNumber = 0 or ReferenceNumber IN (0, -2, -3, -4) THEN SpecArrivalTime ELSE ArrivalTime END ArrivalTime,
  CASE WHEN GuestNumber = 0 or ReferenceNumber IN (0, -2, -3, -4) THEN SpecDepartureTime ELSE DepartureTime END DepartureTime,
  Adults, Children1, Children2 ,
  SegmentNumber, PointOfSaleNumber, RequestorID,
  FirstPrice2, FirstPrice3, FirstPrice4,
  ProductNumber, ProductNumber2,ProductNumber3, ProductNumber4,
  Day2, Day3, Day4,
  ReservationGroup,AllotmentNumber,SoldRoomType,
  HotelNumber, AllocatedTypeNumber, ParentTypeNumber,
  Status, RR_ReferenceNumber, RR_ReferenceNumberSub,
  HotelCustomerNumber,
  COUNT(*) OVER (PARTITION BY LINENUMBER) LinkedRes
INTO #T4
FROM #T3
WHERE Status <= 6

DROP TABLE #T3

SELECT 
  D.CurrentDate,
  LINENUMBER,
  ReferenceNumber, GuestNumber,
  BookedTypeNumber, RoomNumberID, NumberOfRooms, SpecArrivalDate, SpecDepartureDate, SpecAdults, SpecChildren1, SpecChildren2,
  SpecArrivalTime, SpecDepartureTime, SoldPrice, SpecReservationGroup, 
  SpecProductNumber, SpecProductNumber2,
  SpecProductNumber3, SpecProductNumber4, 
  SpecDay2, SpecDay3, SpecDay4,
  SpecFirstPrice2, SpecFirstPrice3, SpecFirstPrice4,
  ArrivalDate, DepartureDate,
  ArrivalTime, DepartureTime,
  Adults, Children1, Children2 ,
  SegmentNumber, PointOfSaleNumber, RequestorID,
  FirstPrice2, FirstPrice3, FirstPrice4,
  ProductNumber, ProductNumber2,ProductNumber3, ProductNumber4,
  Day2, Day3, Day4,
  ReservationGroup,AllotmentNumber,SoldRoomType,
  HotelNumber, AllocatedTypeNumber, ParentTypeNumber,
  Status, RR_ReferenceNumber, RR_ReferenceNumberSub, 
  HotelCustomerNumber, LinkedRes
  
INTO #T5
FROM #T4
INNER JOIN @Dates D ON D.CurrentDate BETWEEN ArrivalDate AND DepartureDate

DROP TABLE #T4

SELECT
  CurrentDate,
  LINENUMBER, BookedTypeNumber, RoomNumberID,
  NumberOfRooms,
  SpecArrivalDate, SpecDepartureDate,
  SpecArrivalTime, 
  CASE WHEN RoomNumberID IS NOT NULL AND ISNULL(SpecDepartureTime,'') = '' THEN '23:59' ELSE SpecDepartureTime END SpecDepartureTime, 
  SoldPrice, 
  ArrivalDate, 
  DepartureDate, 
  CASE WHEN (ReferenceNumber > 0) AND (Status = 3 OR (Status = 4 AND @IncludeTentative = 0)) THEN 0 WHEN GuestNumber=0 THEN SpecAdults ELSE Adults END Adults, 
  CASE WHEN (ReferenceNumber > 0) AND (Status = 3 OR (Status = 4 AND @IncludeTentative = 0)) THEN 0 WHEN GuestNumber=0 THEN SpecChildren1 ELSE Children1 END Children1, 
  CASE WHEN (ReferenceNumber > 0) AND (Status = 3 OR (Status = 4 AND @IncludeTentative = 0)) THEN 0 WHEN GuestNumber=0 THEN SpecChildren2 ELSE Children2 END Children2, 
  CASE WHEN GuestNumber=0 THEN SpecAdults ELSE Adults END OrgAdults,
  CASE WHEN GuestNumber=0 THEN SpecChildren1 ELSE Children1 END OrgChildren1,
  CASE WHEN GuestNumber=0 THEN SpecChildren2 ELSE Children2 END OrgChildren2,
  ArrivalTime,
  DepartureTime,
  CASE WHEN @SpecifyOnRoomTypeSegPos=0 THEN 0 WHEN GuestNumber=0 THEN SpecProductNumber ELSE ProductNumber END ProductNumber, 
  CASE WHEN @SpecifyOnRoomTypeSegPos=0 THEN 0 WHEN GuestNumber=0 THEN SpecProductNumber2 ELSE ProductNumber2 END ProductNumber2, 
  CASE WHEN @SpecifyOnRoomTypeSegPos=0 THEN 0 WHEN GuestNumber=0 THEN SpecProductNumber3 ELSE ProductNumber3 END ProductNumber3, 
  CASE WHEN @SpecifyOnRoomTypeSegPos=0 THEN 0 WHEN GuestNumber=0 THEN SpecProductNumber4 ELSE ProductNumber4 END ProductNumber4,
  CASE WHEN GuestNumber=0 THEN SpecDay2 ELSE Day2 END Day2, 
  CASE WHEN GuestNumber=0 THEN SpecDay3 ELSE Day3 END Day3, 
  CASE WHEN GuestNumber=0 THEN SpecDay4 ELSE Day4 END Day4,
  CASE WHEN GuestNumber=0 THEN SpecFirstPrice2 ELSE FirstPrice2 END FirstPrice2, 
  CASE WHEN GuestNumber=0 THEN SpecFirstPrice3 ELSE FirstPrice3 END FirstPrice3, 
  CASE WHEN GuestNumber=0 THEN SpecFirstPrice4 ELSE FirstPrice4 END FirstPrice4,
  CASE WHEN GuestNumber=0 THEN SpecReservationGroup ELSE ReservationGroup END ReservationGroup,   
  ReferenceNumber, GuestNumber,
  CASE WHEN @SpecifyOnRoomTypeSegPos=1 THEN SegmentNumber ELSE 0 END SegmentNumber, 
  CASE WHEN @SpecifyOnRoomTypeSegPos=1 THEN PointOfSaleNumber ELSE 0 END PointOfSaleNumber,  
  CASE WHEN @SpecifyOnRoomTypeSegPos=1 THEN RequestorID ELSE 0 END RequestorID,
  CASE WHEN (ReferenceNumber <= 0) OR (@BookMixedResourcesEnabled = 1 AND AHX.AllotmentNumber IS NULL) THEN 0 ELSE T5.AllotmentNumber END AllotmentNumber, 
  CASE WHEN @SpecifyOnRoomTypeSegPos=1 THEN SoldRoomType ELSE 0 END SoldRoomType, 
  CASE WHEN RoomNumberID > 0 THEN 1 ELSE 0 END IsAllocatedRoom,
  T5.HotelNumber, AllocatedTypeNumber, ParentTypeNumber, Status, 
  LinkedRes,
  RR_ReferenceNumber, RR_ReferenceNumberSub
  
INTO #T6
FROM #T5 AS T5
LEFT JOIN AllotmentHotelXTAB AHX ON ReferenceNumber > 0 AND @BookMixedResourcesEnabled = 1 AND AHX.AllotmentNumber=T5.AllotmentNumber AND AHX.HotelCustomerNumber=T5.HotelCustomerNumber

DROP TABLE #T5

SELECT
  CurrentDate,
  LINENUMBER, BookedTypeNumber, RoomNumberID,
  CASE WHEN (ReferenceNumber > 0) AND (Status = 3 OR (Status = 4 AND @IncludeTentative = 0)) THEN 0 ELSE NumberOfRooms END NumberOfRooms, 
  NumberOfRooms OrgNumberOfRooms,
  SpecArrivalDate, SpecDepartureDate,
  SpecArrivalTime, 
  SpecDepartureTime, 
  SoldPrice, 
  ArrivalDate, 
  DepartureDate, 
  Adults, 
  Children1, 
  Children2, 
  OrgAdults,
  OrgChildren1,
  OrgChildren2,
  ArrivalTime,
  DepartureTime,
  CASE 
	WHEN Day4 > 0 AND CurrentDate >= DateADD(day, Day4-1, ArrivalDate) THEN ProductNumber4
	WHEN Day3 > 0 AND CurrentDate >= DateADD(day, Day3-1, ArrivalDate) THEN ProductNumber3
	WHEN Day2 > 0 AND CurrentDate >= DateADD(day, Day2-1, ArrivalDate) THEN ProductNumber2
	ELSE ProductNumber
  END ProductNumber,
  Day2,
  Day3,
  Day4,
  FirstPrice2, 
  FirstPrice3, 
  FirstPrice4,
  ReservationGroup,   
  ReferenceNumber, GuestNumber,
  SegmentNumber, 
  PointOfSaleNumber,  
  RequestorID,
  AllotmentNumber, 
  SoldRoomType, 
  IsAllocatedRoom,
  HotelNumber, AllocatedTypeNumber, ParentTypeNumber, Status, 
  LinkedRes,
  RR_ReferenceNumber, RR_ReferenceNumberSub,
  ISNULL(DB.IsDoubleBooking,0) IsDoubleBooking,
  
  CASE WHEN (SpecArrivalDate = SpecDepartureDate) OR (SpecArrivalDate <> SpecDepartureDate AND SpecDepartureDate = CurrentDate AND SpecDepartureTime > @DayroomsLaterThanTime) THEN 1 ELSE 0 END DepartureDateExpression,
  ROW_NUMBER() OVER (PARTITION BY LINENUMBER,CurrentDate ORDER BY SpecDepartureDate, DepartureTime) LineDateIndex 
INTO #IncludeDeparture
FROM #T6 AS T6
OUTER APPLY (
--	SELECT 0 IsDoubleBooking
	SELECT TOP 1 1 IsDoubleBooking
	FROM #T6 DBBooking
	WHERE (@IsDoubleBookingEnabled=1)
	and (T6.RoomNumberID > 0)
	and (DBBooking.CurrentDate=T6.CurrentDate) 
	and (DBBooking.RoomNumberID = T6.RoomNumberID) 
	AND (DBBooking.ReferenceNumber < T6.ReferenceNumber) 
	AND (
			(T6.CurrentDate = T6.SpecArrivalDate AND T6.CurrentDate = DBBooking.SpecArrivalDate AND T6.SpecArrivalTime >= DBBooking.SpecArrivalTime) 
			OR
			(T6.CurrentDate > DBBooking.SpecArrivalDate)
		) 
	AND (
			(T6.CurrentDate = T6.SpecDepartureDate AND T6.CurrentDate = DBBooking.DepartureDate AND T6.SpecDepartureTime <= DBBooking.DepartureTime) 
			OR
			(T6.CurrentDate < DBBooking.DepartureDate)
		) 
  ) DB

  DROP TABLE #T6

 SELECT
  CurrentDate,
  LINENUMBER, BookedTypeNumber, RoomNumberID,
  NumberOfRooms, 
  NumberOfRooms OrgNumberOfRooms,
  SpecArrivalDate, SpecDepartureDate,
  SpecArrivalTime, 
  SpecDepartureTime, 
  SoldPrice, 
  ArrivalDate, 
  DepartureDate, 
  Adults, 
  Children1, 
  Children2, 
  OrgAdults,
  OrgChildren1,
  OrgChildren2,
  ArrivalTime,
  DepartureTime,
  ProductNumber,
  Day2,
  Day3,
  Day4,
  FirstPrice2, 
  FirstPrice3, 
  FirstPrice4,
  ReservationGroup,   
  ReferenceNumber, GuestNumber,
  SegmentNumber, 
  PointOfSaleNumber,  
  RequestorID,
  AllotmentNumber, 
  SoldRoomType, 
  IsAllocatedRoom,
  HotelNumber, AllocatedTypeNumber, ParentTypeNumber, Status, 
  LinkedRes,
  RR_ReferenceNumber, RR_ReferenceNumberSub,
  IsDoubleBooking,
  DepartureDateExpression,
  LineDateIndex,
  ROW_NUMBER() OVER (PARTITION BY LINENUMBER,ReferenceNumber, CurrentDate ORDER BY SpecDepartureDate, DepartureTime DESC) LineReservationNumberIndex,
   ROW_NUMBER() OVER (PARTITION BY LINENUMBER ORDER BY SpecDepartureDate, DepartureTime) LineNumberIndex,
  ROW_NUMBER() OVER (PARTITION BY roomnumberid, CurrentDate, ArrivalDate, DepartureDate, ReferenceNumber, GuestNumber ORDER BY roomnumberid, arrivaldate, arrivaltime) roomNumberIndex
INTO #IncludeDeparture2
FROM #IncludeDeparture

SELECT
  CurrentDate,
  LINENUMBER, BookedTypeNumber, RoomNumberID,
  CASE WHEN LineReservationNumberIndex=1 THEN NumberOfRooms ELSE 0 END NumberOfRooms, 
  NumberOfRooms OrgNumberOfRooms,
  SpecArrivalDate, SpecDepartureDate,
  SpecArrivalTime, 
  SpecDepartureTime, 
  SoldPrice, 
  ArrivalDate, 
  DepartureDate, 
  Adults, 
  Children1, 
  Children2, 
  OrgAdults,
  OrgChildren1,
  OrgChildren2,
  ArrivalTime,
  DepartureTime,
  ProductNumber,
  Day2,
  Day3,
  Day4,
  FirstPrice2, 
  FirstPrice3, 
  FirstPrice4,
  ReservationGroup,   
  ReferenceNumber, GuestNumber,
  SegmentNumber, 
  PointOfSaleNumber,  
  RequestorID,
  AllotmentNumber, 
  SoldRoomType, 
  IsAllocatedRoom,
  HotelNumber, AllocatedTypeNumber, ParentTypeNumber, Status, 
  RR_ReferenceNumber, RR_ReferenceNumberSub,
  IsDoubleBooking,
  LineDateIndex,
  LineNumberIndex,
  CASE WHEN GuestNumber<>0 AND LinkedRes > 1 AND LineNumberIndex=1 THEN ArrivalDate ELSE NULL END OriginalArrDate, 
  CASE WHEN GuestNumber<>0 AND LinkedRes > 1 AND LineNumberIndex=1 THEN DepartureDate ELSE NULL END OriginalDepDate,
  CASE WHEN (SpecArrivalDate = SpecDepartureDate) OR (SpecArrivalDate <> SpecDepartureDate AND SpecDepartureDate = CurrentDate AND SpecDepartureTime > @DayroomsLaterThanTime) THEN 1 ELSE 0 END DepartureDateExpression
  
INTO #IncludeDeparture3
FROM #IncludeDeparture2
WHERE RoomNumberIndex = 1 OR ReferenceNumber > 0


DROP TABLE #IncludeDeparture2

 
SELECT
	AllocatedTypeNumber 
	, CurrentDate
	, HotelNumber 
	, ReservationGroup 
	, SegmentNumber 
	, PointOfSaleNumber 
	, SoldRoomType 
	, ProductNumber 
	, RequestorID
	, IsAllocatedRoom
	, AllotmentNumber
	, ParentTypeNumber
	, TempNumberOfRooms
	, OrgNumberOfRooms
	, ReferenceNumber
	, GuestNumber
	, ArrivalDate
	, DepartureDate
	, Status
	, Adults * PaxMulitiplyRooms2 Adults
	, Children1 * PaxMulitiplyRooms2 Children1
	, Children2 * PaxMulitiplyRooms2 Children2
	, OrgAdults * OrgNumberOfRooms OrgAdults 
	, OrgChildren1 * OrgNumberOfRooms OrgChildren1
	, OrgChildren2 * OrgNumberOfRooms OrgChildren2

  INTO #IncludeDeparture4
  FROM #IncludeDeparture3

  CROSS APPLY (
	SELECT 		
		CASE 
			WHEN AllotmentNumber=0 THEN NumberOfRooms
			WHEN LineDateIndex>1 AND AllotmentNumber<>0 AND @VacantRoomsInclAllotment=1 THEN 0
			WHEN LineDateIndex>1 AND ReferenceNumber IN (0,-2,-3,-4) THEN 0
			ELSE NumberOfRooms END NumberOfRooms0,
		CASE 
			WHEN ReferenceNumber IN (0,-2,-4) THEN 0
			ELSE NumberOfRooms END NumberOfRooms2
		
  ) X1
  CROSS APPLY (
	SELECT
		NumberOfRooms StoreNumberOfRooms, 
		CASE WHEN (LineDateIndex > 1) AND DepartureDateExpression=1 and (ReferenceNumber>0) THEN 1
			ELSE 0 END IsDayRoom0,
		CASE WHEN DepartureDateExpression=1 and (ReferenceNumber>0) THEN 1
			ELSE 0 END IsDayRoom1
  ) X2

  CROSS APPLY (
  SELECT CASE 
		WHEN (IsDayRoom1=1) AND (@IncludeDayroomsInSoldRooms = 0 OR CurrentDate>= @RealSystemDateStr) THEN 0
		WHEN OriginalDepDate IS NOT NULL AND (CurrentDate >= OriginalDepDate OR CurrentDate < OriginalArrDate) AND IsDayRoom0 = 0 THEN 0
		WHEN NumberOfRooms0=0 THEN 1 
		ELSE StoreNumberOfRooms END PaxMulitiplyRooms1

  ) X3
  
  CROSS APPLY (
	SELECT 
	CASE WHEN (IsDayRoom1=1) AND (@IncludeDayroomsInSoldRooms = 0 OR CurrentDate>= @RealSystemDateStr) THEN 0 ELSE NumberOfRooms2 END NumberOfRooms3, 
	CASE WHEN (IsDayRoom1=1) AND (@IncludeDayroomsInSoldRooms = 0 OR CurrentDate>= @RealSystemDateStr) THEN 0 ELSE PaxMulitiplyRooms1 END PaxMulitiplyRooms2
  ) X4

  CROSS APPLY (
	SELECT 
	CASE WHEN IsDoubleBooking=1 THEN 0 ELSE NumberOfRooms3 END TempNumberOfRooms
  ) X5
  
  DROP TABLE #IncludeDeparture3

SELECT
	AllocatedTypeNumber 
	, CONVERT(char(8),CurrentDate,112) DateStamp
	, HotelNumber 
	, ReservationGroup 
	, SegmentNumber 
	, PointOfSaleNumber 
	, SoldRoomType 
	, ProductNumber 
	, RequestorID
	, IsAllocatedRoom
	, AllotmentNumber
	
	, SUM(ISNULL(TempNumberOfRooms, 0)) SoldRooms
	, SUM(ISNULL(NumberOfArrival, 0)) NumberOfArrival
	, SUM(ISNULL(NumberOfInhouse, 0)) NumberOfInhouse
	, SUM(ISNULL(NumberOfStay, 0)) NumberOfStay
	, SUM(ISNULL(NumberOfDeparture, 0)) NumberOfDeparture
	, SUM(ISNULL(ArrivalPax, 0)) ArrivalPax
	, SUM(ISNULL(InhousePax, 0)) InhousePax
	, SUM(ISNULL(StayPax, 0)) StayPax
	, SUM(ISNULL(DeparturePax, 0)) DeparturePax
	, SUM(ISNULL(NumberOfWaitingArrival, 0)) NumberOfWaitingArrival
	, SUM(ISNULL(NumberOfTentativeArrival, 0)) NumberOfTentativeArrival
	, SUM(ISNULL(WaitingArrivalPax, 0)) WaitingArrivalPax
	, SUM(ISNULL(TentativeArrivalPax, 0)) TentativeArrivalPax
	, MIN(ISNULL(ParentTypeNumber, 0)) ParentTypeNumber 
  INTO #IncludeDeparture5
  FROM #IncludeDeparture4
 
  CROSS APPLY 
  (
	SELECT
	CASE WHEN (ReferenceNumber > 0 OR ReferenceNumber = -3) AND ParentTypeNumber = 0 AND CurrentDate = ArrivalDate THEN TempNumberOfRooms ELSE 0 END NumberOfArrival
    , CASE WHEN (ReferenceNumber > 0 OR ReferenceNumber = -3) AND ParentTypeNumber = 0 AND CurrentDate >= ArrivalDate AND CurrentDate < DepartureDate THEN TempNumberOfRooms ELSE 0 END NumberOfInhouse
    , CASE WHEN (ReferenceNumber > 0 OR ReferenceNumber = -3) AND ParentTypeNumber = 0 AND CurrentDate > ArrivalDate AND CurrentDate < DepartureDate THEN TempNumberOfRooms ELSE 0 END NumberOfStay
    , CASE WHEN (ReferenceNumber > 0 OR ReferenceNumber = -3) AND ParentTypeNumber = 0 AND CurrentDate = DepartureDate THEN TempNumberOfRooms ELSE 0 END NumberOfDeparture
	, CASE WHEN (ReferenceNumber > 0 OR ReferenceNumber = -3) AND ParentTypeNumber = 0 AND CurrentDate = ArrivalDate THEN Adults + Children1 + Children2 ELSE 0 END ArrivalPax
	, CASE WHEN (ReferenceNumber > 0 OR ReferenceNumber = -3) AND ParentTypeNumber = 0 AND CurrentDate >= ArrivalDate AND CurrentDate < DepartureDate THEN Adults + Children1 + Children2 ELSE 0 END InhousePax
	, CASE WHEN (ReferenceNumber > 0 OR ReferenceNumber = -3) AND ParentTypeNumber = 0 AND CurrentDate > ArrivalDate AND CurrentDate < DepartureDate THEN Adults + Children1 + Children2 ELSE 0 END StayPax
	, CASE WHEN (ReferenceNumber > 0 OR ReferenceNumber = -3) AND ParentTypeNumber = 0 AND CurrentDate = DepartureDate THEN Adults + Children1 + Children2 ELSE 0 END DeparturePax
	, CASE WHEN ReferenceNumber > 0 AND ParentTypeNumber = 0 AND CurrentDate = ArrivalDate AND Status = 3 THEN OrgNumberOfRooms ELSE 0 END NumberOfWaitingArrival
	, CASE WHEN ReferenceNumber > 0 AND ParentTypeNumber = 0 AND CurrentDate = ArrivalDate AND Status = 4 THEN OrgNumberOfRooms ELSE 0 END NumberOfTentativeArrival
	, CASE WHEN ReferenceNumber > 0 AND ParentTypeNumber = 0 AND CurrentDate = ArrivalDate AND Status = 3 THEN OrgAdults + OrgChildren1 + OrgChildren2 ELSE 0 END WaitingArrivalPax
	, CASE WHEN ReferenceNumber > 0 AND ParentTypeNumber = 0 AND CurrentDate = ArrivalDate AND Status = 4 THEN OrgAdults + OrgChildren1 + OrgChildren2 ELSE 0 END TentativeArrivalPax

  ) X3
  GROUP BY
	AllocatedTypeNumber 
	, CurrentDate
	, HotelNumber 
	, ReservationGroup 
	, SegmentNumber 
	, PointOfSaleNumber 
	, SoldRoomType 
	, ProductNumber 
	, RequestorID
	, IsAllocatedRoom
	, AllotmentNumber 

DROP TABLE #IncludeDeparture4

SELECT
  CurrentDate,
  LINENUMBER, BookedTypeNumber, RoomNumberID,
  NumberOfRooms,
  OrgNumberOfRooms,
  SpecArrivalDate, SpecDepartureDate,
  SpecArrivalTime, 
  SpecDepartureTime, 
  SoldPrice, 
  ArrivalDate, 
  DepartureDate, 
  Adults, 
  Children1, 
  Children2, 
  ArrivalTime,
  DepartureTime,
  ProductNumber, 
  Day2,
  Day3,
  Day4,
  FirstPrice2, 
  FirstPrice3, 
  FirstPrice4,
  ReservationGroup,   
  ReferenceNumber, GuestNumber,
  SegmentNumber, 
  PointOfSaleNumber,  
  RequestorID,
  AllotmentNumber, 
  SoldRoomType, 
  HotelNumber, AllocatedTypeNumber, ParentTypeNumber, Status, 
  LinkedRes,
  IsAllocatedRoom,
  IsDoubleBooking,
  RR_ReferenceNumber, RR_ReferenceNumberSub,
  DepartureDateExpression,
  LineDateIndex,
  ROW_NUMBER() OVER (PARTITION BY LINENUMBER,ReferenceNumber, CurrentDate ORDER BY SpecDepartureDate, DepartureTime DESC) LineReservationNumberIndex
INTO #T7
FROM #IncludeDeparture
WHERE (ArrivalDate = DepartureDate and CurrentDate= DepartureDate) 
			or (ArrivalDate <> DepartureDate and CurrentDate = DepartureDate and (DepartureTime > @DayroomsLaterThanTime and @DayroomsLaterThanTime<'23:59'))
			or (CurrentDate < DepartureDate)

-- TEST
--select NumberOfRooms, SoldPrice,* from #T4 where CurrentDate='20210917' and AllocatedTypeNumber=4 and SegmentNumber=3 and PointOfSaleNumber=20 and ProductNumber=71 and ReservationGroup=1 And RequestorID=0 order by LINENUMBER, GuestNumber
--select SUM(NumberOfRooms*SoldPrice) from #T4 where CurrentDate='20210917' and AllocatedTypeNumber=4 and SegmentNumber=3 and PointOfSaleNumber=20 and ProductNumber=71 and ReservationGroup=1 And RequestorID=0

DROP TABLE #IncludeDeparture

SELECT
  CurrentDate,
  LINENUMBER, BookedTypeNumber, RoomNumberID, 
  NumberOfRooms, 
  OrgNumberOfRooms,
  SpecArrivalDate, SpecDepartureDate,
  SpecArrivalTime, 
  SpecDepartureTime, 
  CASE 
	WHEN Day4 > 0 AND CurrentDate >= DateADD(day, Day4-1, ArrivalDate) THEN FirstPrice4
	WHEN Day3 > 0 AND CurrentDate >= DateADD(day, Day3-1, ArrivalDate) THEN FirstPrice3
	WHEN Day2 > 0 AND CurrentDate >= DateADD(day, Day2-1, ArrivalDate) THEN FirstPrice2
	ELSE SoldPrice
  END SoldPrice,
  ArrivalDate, 
  DepartureDate, 
  Adults, 
  Children1, 
  Children2, 
  ArrivalTime, 
  DepartureTime, 
  ProductNumber,
  ReservationGroup,   
  ReferenceNumber, GuestNumber,
  SegmentNumber, 
  PointOfSaleNumber,  
  RequestorID,
  AllotmentNumber, 
  SoldRoomType, 
  HotelNumber, AllocatedTypeNumber, ParentTypeNumber, Status, 
  LinkedRes,
  RR_ReferenceNumber, RR_ReferenceNumberSub,
	
  DepartureDateExpression,
  IsDoubleBooking,
  LineDateIndex,
  IsAllocatedRoom,
  LineReservationNumberIndex,
  ROW_NUMBER() OVER (PARTITION BY LINENUMBER ORDER BY SpecDepartureDate, DepartureTime) LineNumberIndex,
  ROW_NUMBER() OVER (PARTITION BY roomnumberid, CurrentDate, ArrivalDate, DepartureDate, ReferenceNumber, GuestNumber ORDER BY roomnumberid, arrivaldate, arrivaltime) roomNumberIndex
  INTO #T8
  FROM #T7 T7

  DROP TABLE #T7

  SELECT
  CurrentDate,
  LINENUMBER, BookedTypeNumber, RoomNumberID, 
  CASE WHEN LineReservationNumberIndex=1 THEN NumberOfRooms ELSE 0 END NumberOfRooms, 
  OrgNumberOfRooms,
  SpecArrivalDate, SpecDepartureDate,
  SpecArrivalTime, 
  SpecDepartureTime, 
  SoldPrice,
  ArrivalDate, 
  DepartureDate, 
  Adults, 
  Children1, 
  Children2, 
  ArrivalTime, 
  DepartureTime, 
  ProductNumber,
  ReservationGroup,   
  ReferenceNumber, GuestNumber,
  SegmentNumber, 
  PointOfSaleNumber,  
  RequestorID,
  AllotmentNumber, 
  SoldRoomType, 
  HotelNumber, AllocatedTypeNumber, ParentTypeNumber, Status, 
  RR_ReferenceNumber, RR_ReferenceNumberSub,
  CASE WHEN GuestNumber<>0 AND LinkedRes > 1 AND LineNumberIndex=1 THEN ArrivalDate ELSE NULL END OriginalArrDate, 
  CASE WHEN GuestNumber<>0 AND LinkedRes > 1 AND LineNumberIndex=1 THEN DepartureDate ELSE NULL END OriginalDepDate,
  DepartureDateExpression,
  IsDoubleBooking,
  LineDateIndex,
  IsAllocatedRoom,
  LineNumberIndex,
  LineReservationNumberIndex,
  roomNumberIndex
  INTO #T9
  FROM #T8 T8
  WHERE RoomNumberIndex = 1 OR ReferenceNumber > 0


  DROP TABLE #T8

-- TEST
--select NumberOfRooms, SoldPrice,* from #T5 where CurrentDate='20210917' and AllocatedTypeNumber=4 and SegmentNumber=3 and PointOfSaleNumber=20 and ProductNumber=71 and ReservationGroup=1 And RequestorID=0  order by LINENUMBER, GuestNumber
--select SUM(NumberOfRooms*SoldPrice) from #T5 where CurrentDate='20210917' and AllocatedTypeNumber=4 and SegmentNumber=3 and PointOfSaleNumber=20 and ProductNumber=71 and ReservationGroup=1 And RequestorID=0

  SELECT
	AllocatedTypeNumber 
	, CurrentDate
	, HotelNumber 
	, ReservationGroup 
	, SegmentNumber 
	, PointOfSaleNumber 
	, SoldRoomType 
	, ProductNumber 
	, RequestorID
	, IsAllocatedRoom
	, AllotmentNumber

	, SUM(ISNULL(TempNumberOfRooms, 0)) SoldRooms
    , SUM(ISNULL(SoldPrice2, 0)) SoldPrice 
    , SUM(ISNULL(Adults2, 0)) Adults
    , SUM(ISNULL(Children1_2, 0)) Children1
    , SUM(ISNULL(Children2_2, 0)) Children2 
    , SUM(ISNULL(NumberOfConfirmed, 0)) NumberOfConfirmed
    , SUM(ISNULL(NumberOfProvisionals, 0)) NumberOfProvisionals
    , SUM(ISNULL(NumberOfGuaranteed, 0)) NumberOfGuaranteed
    , SUM(ISNULL(NumberOfWaitinglists, 0)) NumberOfWaitinglists
    , SUM(ISNULL(NumberOfTentative, 0)) NumberOfTentative
    , SUM(ISNULL(NumberOfOutOfOrder, 0)) NumberOfOutOfOrder
    , SUM(ISNULL(NumberOfOutForService, 0)) NumberOfOutForService
    , SUM(ISNULL(NumberOfOccupied, 0)) NumberOfOccupied
	, SUM(ISNULL(NumberOfDayrooms, 0)) NumberOfDayrooms
	, SUM(ISNULL(AllotAdults, 0) + ISNULL(AllotChildren1, 0) + ISNULL(AllotChildren2, 0)) PaxAllotment
	, SUM(ISNULL(AllotNumberOfRooms2, 0)) AllotNumberOfRooms
	, SUM(ISNULL(NumberOfRoomsSoldCourse, 0)) NumberOfRoomsSoldCourse
	, SUM(ISNULL(NumberOfRoomsSoldAttendee, 0)) NumberOfRoomsSoldAttendee
	, MIN(ISNULL(ParentTypeNumber, 0)) ParentTypeNumber
  INTO #T10
  FROM #T9 AS T9
  CROSS APPLY (
	SELECT 
		NumberOfRooms StoreNumberOfRooms, 
		CASE WHEN (LineDateIndex > 1) AND DepartureDateExpression=1 and (ReferenceNumber>0) THEN 1
			ELSE 0 END IsDayRoom0,
		CASE WHEN DepartureDateExpression=1 and (ReferenceNumber>0) THEN 1
			ELSE 0 END IsDayRoom1,
		CASE 
			WHEN AllotmentNumber=0 THEN NumberOfRooms
			WHEN LineDateIndex>1 AND AllotmentNumber<>0 AND @VacantRoomsInclAllotment=1 THEN 0
			WHEN LineDateIndex>1 AND ReferenceNumber IN (0,-2,-3,-4) THEN 0
			ELSE NumberOfRooms END NumberOfRooms0,
		CASE 
			WHEN AllotmentNumber=0 THEN NumberOfRooms
			WHEN AllotmentNumber<>0 AND @VacantRoomsInclAllotment=1 THEN 0
			WHEN LineDateIndex>1 AND ReferenceNumber IN (0,-2,-3,-4) THEN 0
			ELSE NumberOfRooms END NumberOfRooms1,
		CASE 
			WHEN AllotmentNumber<>0 AND @VacantRoomsInclAllotment=1 THEN 0
			WHEN ReferenceNumber IN (0,-2,-3,-4) THEN 0
			ELSE NumberOfRooms END NumberOfRooms2,
		CASE WHEN AllotmentNumber=0 THEN 0 ELSE NumberOfRooms END AllotNumberOfRooms1,
		CASE WHEN AllotmentNumber=0 THEN 0 WHEN NumberOfRooms>0 THEN Adults*NumberOfRooms ELSE Adults END AllotAdults,
		CASE WHEN AllotmentNumber=0 THEN 0 WHEN NumberOfRooms>0 THEN Children1*NumberOfRooms ELSE Children1 END AllotChildren1,
		CASE WHEN AllotmentNumber=0 THEN 0 WHEN NumberOfRooms>0 THEN Children2*NumberOfRooms ELSE Children2 END AllotChildren2
  ) X1
  CROSS APPLY (
	SELECT 
	CASE WHEN DepartureDateExpression=1 and (ReferenceNumber>0) THEN NumberOfRooms2+AllotNumberOfRooms1 ELSE 0 END NumberOfDayrooms,
	CASE 
		WHEN (IsDayRoom1=1) AND (@IncludeDayroomsInSoldRooms = 0 OR CurrentDate>= @RealSystemDateStr) THEN 0
		WHEN OriginalDepDate IS NOT NULL AND (CurrentDate >= OriginalDepDate OR CurrentDate < OriginalArrDate) AND IsDayRoom0 = 0 THEN 0
		WHEN NumberOfRooms0=0 THEN 1 
		ELSE StoreNumberOfRooms END PaxMulitiplyRooms1
  ) X2
  CROSS APPLY (
	SELECT
	CASE WHEN (IsDayRoom1=1) AND (@IncludeDayroomsInSoldRooms = 0 OR CurrentDate>= @RealSystemDateStr) THEN 0 ELSE NumberOfRooms2 END NumberOfRooms3, 
	CASE WHEN (IsDayRoom1=1) AND (@IncludeDayroomsInSoldRooms = 0 OR CurrentDate>= @RealSystemDateStr) THEN 0 ELSE PaxMulitiplyRooms1 END PaxMulitiplyRooms2, 
	CASE WHEN NumberOfDayrooms > 0 AND ArrivalDate <> DepartureDate AND CurrentDate = DepartureDate THEN 0
		WHEN (IsDayRoom1=1) AND (@IncludeDayroomsInSoldRooms = 0 OR CurrentDate>= @RealSystemDateStr) THEN 0 
		ELSE AllotNumberOfRooms1 END AllotNumberOfRooms2
  ) X3
  CROSS APPLY (
	SELECT
	CASE WHEN IsDoubleBooking=1 THEN 0 ELSE NumberOfRooms3 END TempNumberOfRooms
  ) X4
  CROSS APPLY 
  (
	SELECT
    CASE WHEN ReferenceNumber > 0 AND Status = 0 THEN OrgNumberOfRooms ELSE 0 END NumberOfConfirmed
    , CASE WHEN ReferenceNumber > 0 AND Status = 1 THEN OrgNumberOfRooms ELSE 0 END NumberOfProvisionals
    , CASE WHEN ReferenceNumber > 0 AND Status = 2 THEN OrgNumberOfRooms ELSE 0 END NumberOfGuaranteed
    , CASE WHEN ReferenceNumber > 0 AND Status = 3 THEN OrgNumberOfRooms ELSE 0 END NumberOfWaitinglists
    , CASE WHEN ReferenceNumber > 0 AND Status = 4 THEN OrgNumberOfRooms ELSE 0 END NumberOfTentative
    , CASE WHEN (ReferenceNumber = 0 OR ReferenceNumber = -4) AND (CurrentDate<DepartureDate) THEN NumberOfRooms1 ELSE 0 END NumberOfOutOfOrder
    , CASE WHEN (ReferenceNumber = -2) AND (CurrentDate<DepartureDate) THEN NumberOfRooms1 ELSE 0 END NumberOfOutForService
    , CASE WHEN (ReferenceNumber = -3) AND (CurrentDate<DepartureDate) THEN NumberOfRooms1 ELSE 0 END NumberOfOccupied
	
    , ISNULL(SoldPrice * NumberOfRooms3,0) SoldPrice2
    , Adults * PaxMulitiplyRooms2 Adults2
    , Children1 * PaxMulitiplyRooms2 Children1_2
    , Children2 * PaxMulitiplyRooms2 Children2_2
  ) X5
  CROSS APPLY 
  (
	SELECT 
	CASE WHEN RR_ReferenceNumberSub > 0 AND RR_ReferenceNumber = RR_ReferenceNumberSub THEN TempNumberOfRooms ELSE 0 END NumberOfRoomsSoldCourse,
	CASE WHEN RR_ReferenceNumberSub > 0 AND RR_ReferenceNumber <> RR_ReferenceNumberSub THEN TempNumberOfRooms ELSE 0 END NumberOfRoomsSoldAttendee
  ) X6
  GROUP BY
	AllocatedTypeNumber 
	, CurrentDate
	, HotelNumber 
	, ReservationGroup 
	, SegmentNumber 
	, PointOfSaleNumber 
	, SoldRoomType 
	, ProductNumber 
	, RequestorID
	, IsAllocatedRoom
	, AllotmentNumber

DROP TABLE #T9



SELECT
  AllocatedTypeNumber RoomTypeNumber
, CONVERT(char(8),CurrentDate,112) DateStamp
, HotelNumber 
, ReservationGroup 
, SegmentNumber 
, PointOfSaleNumber 
, SoldRoomType 
, ProductNumber 
, RequestorID

, SUM(SoldRooms) SoldRooms
, SUM(SoldPrice) SoldPrice 
, SUM(Adults) Adults
, SUM(Children1) Children1
, SUM(Children2) Children2 
, SUM(NumberOfConfirmed) NumberOfConfirmed
, SUM(NumberOfProvisionals) NumberOfProvisionals
, SUM(NumberOfGuaranteed) NumberOfGuaranteed
, SUM(NumberOfWaitinglists) NumberOfWaitinglists
, SUM(NumberOfTentative) NumberOfTentative
, SUM(NumberOfOutOfOrder) NumberOfOutOfOrder
, SUM(NumberOfOutForService) NumberOfOutForService
, SUM(NumberOfOccupied) NumberOfOccupied
, SUM(NumberOfDayrooms) NumberOfDayrooms
, SUM(PaxAllotment) PaxAllotment
, SUM(NumberOfRoomsSoldCourse) NumberOfRoomsSoldCourse
, SUM(NumberOfRoomsSoldAttendee) NumberOfRoomsSoldAttendee
, CONVERT(int,0) NumberOfOccupiedGroup -- CONVERT(int,0) makes the column nullable
, CONVERT(int,0) FreeRoomsAllotment
, CONVERT(int,0) TotalRoomsAllotment
, CONVERT(int,0) NumberOfRoomsSoldToday
, CONVERT(int,0) NumberOfArrival
, CONVERT(int,0) NumberOfInhouse
, CONVERT(int,0) NumberOfStay
, CONVERT(int,0) NumberOfDeparture
, CONVERT(int,0) ArrivalPax
, CONVERT(int,0) InhousePax
, CONVERT(int,0) StayPax
, CONVERT(int,0) DeparturePax
, CONVERT(int,0) NumberOfWaitingArrival
, CONVERT(int,0) NumberOfTentativeArrival
, CONVERT(int,0) WaitingArrivalPax
, CONVERT(int,0) TentativeArrivalPax
INTO #RoomBookingStatus
FROM #T10
GROUP BY
AllocatedTypeNumber 
, CurrentDate 
, HotelNumber 
, ReservationGroup 
, SegmentNumber 
, PointOfSaleNumber 
, SoldRoomType 
, ProductNumber 
, RequestorID


MERGE #RoomBookingStatus T
	USING (SELECT AllocatedTypeNumber 
	, DateStamp
	, HotelNumber 
	, ReservationGroup 
	, SegmentNumber 
	, PointOfSaleNumber 
	, SoldRoomType 
	, ProductNumber 
	, RequestorID
	, SUM(NumberOfArrival) NumberOfArrival
	, SUM(NumberOfInhouse) NumberOfInhouse
	, SUM(NumberOfStay) NumberOfStay
	, SUM(NumberOfDeparture) NumberOfDeparture
	, SUM(ArrivalPax) ArrivalPax
	, SUM(InhousePax) InhousePax
	, SUM(StayPax) StayPax
	, SUM(DeparturePax) DeparturePax
	, SUM(NumberOfWaitingArrival) NumberOfWaitingArrival
	, SUM(NumberOfTentativeArrival) NumberOfTentativeArrival
	, SUM(WaitingArrivalPax) WaitingArrivalPax
	, SUM(TentativeArrivalPax) TentativeArrivalPax
	FROM #IncludeDeparture5
	GROUP BY AllocatedTypeNumber 
	, DateStamp
	, HotelNumber 
	, ReservationGroup 
	, SegmentNumber 
	, PointOfSaleNumber 
	, SoldRoomType 
	, ProductNumber 
	, RequestorID
	) S
	ON ( T.RoomTypeNumber    = S.AllocatedTypeNumber AND 
		 T.DateStamp         = S.DateStamp AND 
		 T.SegmentNumber     = S.SegmentNumber AND 
		 T.PointOfSaleNumber = S.PointOfSaleNumber AND 
		 T.SoldRoomType      = S.SoldRoomType AND
		 T.HotelNumber       = S.HotelNumber AND
		 T.ProductNumber	 = S.ProductNumber AND
		 T.ReservationGroup  = S.ReservationGroup AND 
		 T.RequestorID       = S.RequestorID)
	WHEN NOT MATCHED BY TARGET THEN 
		INSERT (RoomTypeNumber, DateStamp, SegmentNumber, PointOfSaleNumber, SoldRoomType, HotelNumber, ProductNumber, ReservationGroup, RequestorID, 
		NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax, NumberOfWaitingArrival, 
		NumberOfTentativeArrival, WaitingArrivalPax, TentativeArrivalPax)
		VALUES (S.AllocatedTypeNumber, S.DateStamp, S.SegmentNumber, S.PointOfSaleNumber, S.SoldRoomType, S.HotelNumber, S.ProductNumber, S.ReservationGroup, S.RequestorID, 
		S.NumberOfArrival, S.NumberOfInhouse, S.NumberOfStay, S.NumberOfDeparture, S.ArrivalPax, S.InhousePax, S.StayPax, S.DeparturePax, S.NumberOfWaitingArrival, 
		S.NumberOfTentativeArrival, S.WaitingArrivalPax, S.TentativeArrivalPax)
	WHEN MATCHED THEN
		UPDATE SET NumberOfArrival = T.NumberOfArrival + S.NumberOfArrival,
		NumberOfInhouse = T.NumberOfInhouse + S.NumberOfInhouse,
		NumberOfStay = T.NumberOfStay + S.NumberOfStay,
		NumberOfDeparture = T.NumberOfDeparture + S.NumberOfDeparture,
		ArrivalPax = T.ArrivalPax + S.ArrivalPax,
		InhousePax = T.InhousePax + S.InhousePax,
		StayPax = T.StayPax + S.StayPax,
		DeparturePax = T.DeparturePax + S.DeparturePax,
		NumberOfWaitingArrival = T.NumberOfWaitingArrival + S.NumberOfWaitingArrival, 
		NumberOfTentativeArrival = T.NumberOfTentativeArrival + S.NumberOfTentativeArrival, 
		WaitingArrivalPax = T.WaitingArrivalPax + S.WaitingArrivalPax, 
		TentativeArrivalPax = T.TentativeArrivalPax + S.TentativeArrivalPax
	;

DROP TABLE #IncludeDeparture5

SELECT
	AllotmentNumber
	, AllocatedTypeNumber RoomTypeNumber  
    , CONVERT(char(8),CurrentDate,112) DateStamp	
	, SUM(AllotNumberOfRooms) CurrentCount
INTO #AllotmentLines
FROM #T10
WHERE AllotNumberOfRooms > 0
GROUP BY
	  AllotmentNumber
	, AllocatedTypeNumber 
	, CurrentDate 


-- TEST
--select * from #RoomBookingStatus where Datestamp='20210916' and RoomTypeNumber=4 and SegmentNumber=3 and PointOfSaleNumber=20 and ProductNumber=71 and ReservationGroup=1
--SELECT 
--RoomTypeNumber
--, Datestamp
--, HotelNumber 
--, ReservationGroup 
--, SegmentNumber 
--, PointOfSaleNumber 
--, SoldRoomType 
--, ProductNumber 
--, RequestorID
--, SoldRooms
--, SoldPrice 
--, Adults
--, Children1
--, Children2 
--, NumberOfConfirmed
--, NumberOfProvisionals
--, NumberOfGuaranteed
--, NumberOfWaitinglists
--, NumberOfTentative
--, NumberOfOutOfOrder
--, NumberOfOutForService
--, NumberOfOccupied
--, PaxAllotment
--FROM #RoomBookingStatus
--ORDER BY RoomTypeNumber,DateStamp,SegmentNumber,PointOfSaleNumber,SoldRoomType,ProductNumber,HotelNumber,ReservationGroup,RequestorID

  ------------------------------------------------------------------------------------------------------------------------------------
  --- Calculating Occupied for reservations without room number
  ------------------------------------------------------------------------------------------------------------------------------------
IF @CalculateOccupied = 1
BEGIN
  --TEST
	--WITH NoRoomNumber
	--AS
	--(
	--	SELECT
	--		  AllocatedTypeNumber RoomTypeNumber  
	--		, CONVERT(char(8),CurrentDate,112) DateStamp	
	--		, HotelNumber 
	--		, SoldRoomType 
	--		, MIN(ParentTypeNumber) ParentTypeNumber
	--		, SUM(CASE WHEN IsAllocatedRoom=0 THEN SoldRooms ELSE 0 END) NumberOfRooms
	--		, SUM(CASE WHEN IsAllocatedRoom=1 THEN SoldRooms+NumberOfOccupied ELSE 0 END) NumberOfAllocatedRooms
	--		, NULL ProductNumber
	--	FROM #T6
	--	GROUP BY
	--		AllocatedTypeNumber 
	--		,CurrentDate 
	--		,HotelNumber 
	--		,SoldRoomType 
	--)
	--SELECT RoomTypeNumber,DateStamp,HotelNumber,SoldRoomType,ParentTypeNumber,NumberOfRooms,NumberOfAllocatedRooms, ProductNumber
	--FROM NoRoomNumber
	--ORDER BY RoomTypeNumber,DateStamp,HotelNumber

	;
	WITH NoRoomNumber
	AS
	(
		SELECT
			  AllocatedTypeNumber RoomTypeNumber  
			, CONVERT(char(8),CurrentDate,112) DateStamp	
			, HotelNumber 
			, SoldRoomType 
			, MIN(ISNULL(ParentTypeNumber,0)) ParentTypeNumber
			, SUM(CASE WHEN IsAllocatedRoom=0 THEN SoldRooms ELSE 0 END) NumberOfRooms
			, SUM(CASE WHEN IsAllocatedRoom=1 THEN SoldRooms+NumberOfOccupied ELSE 0 END) NumberOfAllocatedRooms
			, NULL ProductNumber
		FROM #T10
		WHERE AllocatedTypeNumber > 0
		GROUP BY
			AllocatedTypeNumber 
			,CurrentDate 
			,HotelNumber 
			,SoldRoomType 
	),
	SubRooms
	AS
	(
		SELECT NRN.ParentTypeNumber RoomTypeNumber, MAX(Occupied) Occupied, DateStamp, HotelNumber
		FROM NoRoomNumber NRN
		CROSS APPLY (
			SELECT NumberOfRoomsAllowed MaxRooms
			FROM ROOMTYPES RT WITH (NOLOCK) 
			WHERE RT.RoomTypeNumber = NRN.RoomTypeNumber
		) RT
		OUTER APPLY (
			SELECT (NumberOfAllocatedRooms * RT.MaxRooms) - NRN.NumberOfAllocatedRooms NumberOfVacantBeds
			FROM NoRoomNumber 
			WHERE RoomTypeNumber = NRN.ParentTypeNumber 
			AND DateStamp = NRN.DateStamp 
			AND HotelNumber = NRN.HotelNumber 
			AND SoldRoomType = NRN.SoldRoomType
		) P
		CROSS APPLY (
			SELECT
			CASE WHEN MaxRooms > 0 THEN CEILING(CAST(CASE WHEN NumberOfVacantBeds > 0 THEN NumberOfRooms - NumberOfVacantBeds ELSE NumberOfRooms END AS FLOAT) / CAST(MaxRooms AS FLOAT)) ELSE 0 END Occupied
		) X1
		WHERE NRN.ParentTypeNumber > 0 --IS NOT NULL
		GROUP BY NRN.ParentTypeNumber, DateStamp, HotelNumber
		UNION ALL
		SELECT SubRoomTypeNumber RoomTypeNumber, NumberOfRooms * NumberOfRoomsAllowed Occupied, DateStamp, HotelNumber
		FROM NoRoomNumber NRN
		CROSS APPLY (
			SELECT RT.RoomTypeNumber SubRoomTypeNumber, NumberOfRoomsAllowed 
			FROM ROOMTYPES RT WITH (NOLOCK)	
			WHERE RT.ParentRoomTypeNumber = NRN.RoomTypeNumber
		) RT
		WHERE NRN.ParentTypeNumber = 0 --IS NULL
	)
	SELECT RoomTypeNumber, Occupied, DateStamp, HotelNumber
	INTO #NumberOfSubRooms
	FROM SubRooms

	DROP TABLE #T10
-- TEST
--SELECT RoomTypeNumber, DateStamp, HotelNumber, Occupied
--FROM #NumberOfSubRooms
--ORDER BY RoomTypeNumber, DateStamp, HotelNumber

	;
	DECLARE #cur CURSOR FOR
	WITH RT
	AS
	(
		SELECT DISTINCT RT1.HotelNumber, RT1.RoomTypeNumber, RT1.ParentRoomTypeNumber, RT1.NumberOfSubRooms, RT1.NumberOfRoomsAllowed, CASE WHEN RT2.RoomTypeNumber IS NULL THEN 0 ELSE 1 END HasChildren
		FROM ROOMTYPES RT1
		LEFT OUTER JOIN ROOMTYPES RT2 ON RT2.ParentRoomTypeNumber=RT1.RoomTypeNumber AND RT2.HotelNumber=RT1.HotelNumber
	),
	RC
	AS
	(
		SELECT RoomTypeNumber, COUNT(*) RoomCount
		FROM ROOMS (NOLOCK) 
		GROUP BY RoomTypeNumber
	)
	SELECT HN.HotelNumber, RV.DateStamp, CUR.RoomTypeNumber, RV.RoomsVacant, CUR.VacantRooms
	FROM @HotelNumbers HN
	CROSS APPLY (
		SELECT TOP 1 1 OK
		FROM RT 
		WHERE ISNULL(RT.ParentRoomTypeNumber,0)=0 
		AND RT.HasChildren=0
		AND RT.HotelNumber=HN.HotelNumber
	) X1
	CROSS APPLY (
		SELECT TOP 1 1 OK
		FROM RT 
		WHERE ISNULL(RT.ParentRoomTypeNumber,0)>0 
		AND RT.HotelNumber=HN.HotelNumber
	) X2
	CROSS APPLY (
		SELECT COUNT(*) TotalRooms
		FROM ROOMS RO (NOLOCK) 
		LEFT OUTER JOIN ROOMS RO2 (NOLOCK) ON RO2.HotelNumber=RO.HotelNumber AND RO2.ParentRoomNumber=RO.RoomNumber
		WHERE ISNULL(RO.ParentRoomNumber, 0) = 0 
		AND RO.HotelNumber = HN.HotelNumber
		AND RO2.RoomNumber IS NULL
	) TRC
	CROSS APPLY
	(
		SELECT DateStamp, TRC.TotalRooms - IsNULL(Sum(soldrooms + NumberOfOccupied+ NumberOfOutOfOrder),0) RoomsVacant
		FROM #ROOMBOOKINGSTATUS RBS 
		INNER JOIN RT ON RBS.RoomTypeNumber = RT.RoomTypeNumber AND RT.HasChildren=0 AND ISNULL(RT.ParentRoomtypeNumber, 0) = 0
		WHERE RBS.HotelNumber=HN.HotelNumber
		GROUP BY RBS.DateStamp
	) RV
	CROSS APPLY
	(
		SELECT rt.RoomTypeNumber, MAX(RC.RoomCount) - 
			(SUM(ISNULL(rbs.soldrooms,0)) + SUM(ISNULL(rbs.NumberOfOccupied,0)) + SUM(ISNULL(rbs.NumberOfOutOfOrder,0)) + SUM(ISNULL(nsb.Occupied,0))) VacantRooms0
		FROM RT
		INNER JOIN RC ON RC.RoomTypeNumber=RT.RoomTypeNumber
		INNER JOIN #ROOMBOOKINGSTATUS Rbs   on rt.RoomTypeNumber = rbs.RoomTypeNumber AND rbs.datestamp = RV.DateStamp
		INNER JOIN #NumberOfSubRooms nsb  ON rt.RoomTypeNumber = nsb.RoomTypeNumber AND nsb.datestamp = RV.DateStamp
		WHERE rt.HotelNumber = HN.HotelNumber
		AND ISNULL(rt.ParentRoomTypeNumber, 0) = 0 
		AND rt.HasChildren=1
		GROUP BY rt.RoomTypeNumber
	) RV2
	CROSS APPLY (
		SELECT rv2.RoomTypeNumber, Rv2.VacantRooms0 VacantRooms
		UNION 
		SELECT rt.RoomTypeNumber, RC.RoomCount VacantRooms
		FROM RT
		INNER JOIN RC ON RC.RoomTypeNumber=RT.RoomTypeNumber
		LEFT OUTER JOIN #ROOMBOOKINGSTATUS Rbs ON RBS.datestamp = RV.DateStamp AND RT.RoomTypeNumber = rbs.RoomTypeNumber
		LEFT OUTER JOIN #NumberOfSubRooms nsb ON NSB.datestamp = RV.DateStamp AND RT.RoomTypeNumber = nsb.RoomTypeNumber
		WHERE ISNULL(rt.ParentRoomTypeNumber, 0) = 0
		AND rt.HasChildren=1
		AND rt.HotelNumber = HN.HotelNumber
		AND rbs.RoomTypeNumber IS NULL
		AND nsb.RoomTypeNumber IS NULL
	) CUR
	WHERE RoomsVacant < 0
	AND VacantRooms > 0
	ORDER BY HotelNumber, DateStamp

	DECLARE @curHotelNumber int, @curDateStamp char(8), @curRoomTypeNumber int, @curRoomsVacant int, @curVacantRooms int, @curNumberOfSubRooms int, @curVacantRoomsWithBed int
	DECLARE @curOldHotelNumber int = NULL, @curOldDateStamp char(8)=NULL, @curCount int
	DECLARE @curNeededRooms int
	DECLARE @curTable TABLE
	(
		HotelNumber int, 
		DateStamp char(8), 
		RoomTypeNumber int,
		RoomCount int
	)
	OPEN #cur
	FETCH NEXT FROM #cur INTO @curHotelNumber, @curDateStamp, @curRoomTypeNumber, @curRoomsVacant, @curVacantRooms
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		IF @curOldHotelNumber IS NULL OR @curOldDateStamp IS NULL OR @curOldHotelNumber<>@curHotelNumber OR @curOldDateStamp<>@curDateStamp
		BEGIN
			SET @curOldHotelNumber = @curHotelNumber 
			SET @curOldDateStamp = @curDateStamp
			SET @curNeededRooms = -@curRoomsVacant
		END
		IF @curNeededRooms > 0
		BEGIN
			IF @curVacantRooms <= @curNeededRooms
				SET @curCount = @curVacantRooms
			ELSE 
				SET @curCount = @curNeededRooms	
		
			INSERT INTO @curTable (HotelNumber, DateStamp, RoomTypeNumber, RoomCount) VALUES (@curHotelNumber, @curDateStamp, @curRoomTypeNumber, @curCount)
			SET @curNeededRooms = @curNeededRooms - @curCount
		END
		FETCH NEXT FROM #cur INTO @curHotelNumber, @curDateStamp, @curRoomTypeNumber, @curRoomsVacant, @curVacantRooms
	END
	CLOSE #cur
	DEALLOCATE #cur
	--SELECT * FROM @Table ORDER BY HotelNumber, DateStamp, RoomTypeNumber

	MERGE #NumberOfSubRooms T
	USING (
		SELECT C.HotelNumber, C.Datestamp, RT.RoomTypeNumber SubRoomType, SUM(RT.NumberOfRoomsAllowed*C.RoomCount) Occupied
		FROM @CurTable C
		INNER JOIN RoomTypes RT (NOLOCK) ON RT.ParentRoomTypeNumber=C.RoomTypeNumber
		GROUP BY C.HotelNumber, C.Datestamp, RT.RoomTypeNumber) S
	ON (T.HotelNumber=S.HotelNumber AND T.DateStamp=S.Datestamp AND T.RoomTypeNumber=S.SubRoomType)
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (RoomTypeNumber, Occupied, DateStamp, HotelNumber) VALUES (S.SubRoomType, S.Occupied, S.DateStamp, S.HotelNumber)
	WHEN MATCHED THEN
		UPDATE SET Occupied = T.Occupied+S.Occupied
	;

;
-- TEST
--SELECT RoomTypeNumber, DateStamp, HotelNumber, SUM(Occupied) Occupied FROM #NumberOfSubRooms WHERE Occupied > 0 GROUP BY RoomTypeNumber, DateStamp, HotelNumber

	MERGE #RoomBookingStatus T
	USING (SELECT RoomTypeNumber, DateStamp, HotelNumber, SUM(Occupied) Occupied FROM #NumberOfSubRooms WHERE Occupied > 0 GROUP BY RoomTypeNumber, DateStamp, HotelNumber) S
	ON ( T.RoomTypeNumber    = S.RoomTypeNumber AND 
		 T.DateStamp         = S.DateStamp AND 
		 T.SegmentNumber     = 0 AND 
		 T.PointOfSaleNumber = 0 AND 
		 T.SoldRoomType      = @ASoldRoomType AND
		 T.HotelNumber       = S.HotelNumber AND
		 T.ProductNumber	 = 0 AND
		 T.ReservationGroup  = 0 AND 
		 T.RequestorID       = 0)
	WHEN NOT MATCHED BY TARGET THEN 
		INSERT (RoomTypeNumber, DateStamp, SegmentNumber, PointOfSaleNumber, SoldRoomType, HotelNumber, ProductNumber, ReservationGroup, NumberOfOccupied, SoldRooms, RequestorID)
		VALUES (S.RoomTypeNumber, S.DateStamp, 0, 0, @ASoldRoomType, S.HotelNumber, 0, 0, S.Occupied, 0, 0)
	WHEN MATCHED THEN
		UPDATE SET NumberOfOccupied = T.NumberOfOccupied + S.Occupied
	;
END -- IF @CalculateOccupied = 1
-- TEST
--SELECT 
--RoomTypeNumber
--, Datestamp
--, HotelNumber 
--, ReservationGroup 
--, SegmentNumber 
--, PointOfSaleNumber 
--, SoldRoomType 
--, ProductNumber 
--, RequestorID
--, SoldRooms
--, SoldPrice 
--, Adults
--, Children1
--, Children2 
--, NumberOfConfirmed
--, NumberOfProvisionals
--, NumberOfGuaranteed
--, NumberOfWaitinglists
--, NumberOfTentative
--, NumberOfOutOfOrder
--, NumberOfOutForService
--, NumberOfOccupied
--, PaxAllotment
--FROM #RoomBookingStatus
--ORDER BY RoomTypeNumber,DateStamp,SegmentNumber,PointOfSaleNumber,SoldRoomType,ProductNumber,HotelNumber,ReservationGroup,RequestorID

  ------------------------------------------------------------------------------------------------------------------------------------
  --- Calculating Sold rooms for reservation groups
  ------------------------------------------------------------------------------------------------------------------------------------
  DECLARE @Key INT
	DECLARE #ResGroupCur CURSOR FOR
	SELECT DateStamp, RBS.RoomTypeNumber, RBS.HotelNumber, ReservationGroup, SUM(SoldRooms)
	FROM #ROOMBOOKINGSTATUS RBS (NOLOCK)
	INNER JOIN ROOMTYPES RT (NOLOCK) ON RBS.RoomTypeNumber = RT.RoomTypeNumber AND ISNULL(RT.ParentRoomTypeNumber, 0) > 0
	WHERE RBS.ReservationGroup > 0
	GROUP BY  DateStamp, RBS.RoomTypeNumber, RBS.HotelNumber, ReservationGroup
  DECLARE @MaxRooms int
  OPEN #ResGroupCur

  FETCH NEXT FROM #ResGroupCur
	INTO @DateStamp, @RoomTypeNumber, @HotelNumber, @ReservationGroup,  @NumberOfRooms
  
  WHILE @@FETCH_STATUS = 0 
  BEGIN	
	WITH RoomtypeTree(ParentRoomTypeNumber, RoomTypeNumber, Type, NumberOfSubRooms) AS 
	(
		SELECT ParentRoomTypeNumber, RoomTypeNumber, Type, NumberOfSubRooms
		FROM ROOMTYPES (NOLOCK) 
		WHERE RoomTypeNumber = @RoomTypeNumber
		UNION ALL
		SELECT RT.ParentRoomTypeNumber, RT.RoomTypeNumber, RT.Type, RT.NumberOfSubRooms
		FROM ROOMTYPES AS RT (NOLOCK)
			INNER JOIN RoomtypeTree AS RTT
			ON RT.RoomTypeNumber = RTT.ParentRoomTypeNumber 
	)
	SELECT @MaxRooms = NumberOfSubRooms 
	FROM RoomtypeTree
	WHERE ParentRoomTypeNumber IS NULL

	IF ISNULL(@MaxRooms,0) > 0
	  SET @MaxRooms = @MaxRooms * CEILING(CAST(@NumberOfRooms AS FLOAT) / CAST(@MaxRooms AS FLOAT))	

	DECLARE #UpdateCur CURSOR FOR
	  SELECT ID FROM ReservationGroup (NOLOCK)
	  WHERE ID <> @ReservationGroup

	OPEN #UpdateCur

	FETCH NEXT FROM #UpdateCur INTO @Key

	WHILE @@FETCH_STATUS = 0 
	BEGIN
	UPDATE #ROOMBOOKINGSTATUS
	SET NumberOfOccupiedGroup = NumberOfOccupiedGroup + @MaxRooms
	WHERE
	RoomTypeNumber    = @RoomTypeNumber AND 
	DateStamp         = @DateStamp AND 
	SegmentNumber     = 0 AND 
	PointOfSaleNumber = 0 AND 
	SoldRoomType	  = 0 AND
	HotelNumber       = @HotelNumber AND
	ProductNumber	  = 0 AND
	ReservationGroup  = @Key AND 
	RequestorID       = 0
	        
	IF @@ROWCOUNT = 0
		INSERT INTO #ROOMBOOKINGSTATUS (RoomTypeNumber, DateStamp, SegmentNumber, PointOfSaleNumber, SoldRoomType, HotelNumber, ProductNumber, ReservationGroup, NumberOfOccupiedGroup, RequestorID)
		VALUES (@RoomTypeNumber, @DateStamp, 0, 0, 0, @HotelNumber, 0, @Key, @MaxRooms, 0)
      FETCH NEXT FROM #UpdateCur INTO @Key

    END

    CLOSE #UpdateCur
    DEALLOCATE #UpdateCur
	
    FETCH NEXT FROM #ResGroupCur
      INTO @DateStamp, @RoomTypeNumber, @HotelNumber, @ReservationGroup,  @NumberOfRooms
    
  END
  
  CLOSE #ResGroupCur
  DEALLOCATE #ResGroupCur	

if @Print=1
begin
  select @DADATE=convert(varchar, getdate (),121)
  print '1.05: '+@DADATE
end
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- R E L E A S E   A L L O T M E N T  A F T E R ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  -- go through #allotmentlines and see if any rows are missing in real DB. if they are we need to add our info - a better fix would be to actually have the rows, but no idea where and when they disappear
  DECLARE @RowCount INT

  DECLARE #AllotCur CURSOR FAST_FORWARD FOR
    SELECT AllotmentNumber, RoomTypeNumber, DateStamp/*, CurrentCount*/ FROM #AllotmentLines (NOLOCK)

  OPEN #AllotCur
  FETCH NEXT FROM #AllotCur
    INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp--, @CurrentCount

  WHILE @@FETCH_STATUS <> -1
  BEGIN
    IF @@FETCH_STATUS <> -2
    BEGIN
      SELECT @RowCount=COUNT(*) FROM AllotmentLines (NOLOCK) WHERE AllotmentNumber=@AllotmentNumber AND RoomTypeNumber=@RoomTypeNumber AND DateStamp=@DateStamp
      IF @RowCount = 0
      BEGIN
        if @Print=1
          PRINT 'Inserting missing AllotmentLine(1)...'

        INSERT INTO AllotmentLines (AllotmentNumber, RoomTypeNumber, DateStamp, InitialCount, TotalChange) VALUES (@AllotmentNumber, @RoomTypeNumber, @DateStamp, 0, 0)
      END

    END -- IF @@FETCH_STATUS <> -2

    FETCH NEXT FROM #AllotCur
    INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp--, @CurrentCount
  END -- WHILE

  CLOSE #AllotCur
  DEALLOCATE #AllotCur

if @Print=1
begin
  select @DADATE=convert(varchar, getdate (),121)
  print '1.06: '+@DADATE
end

  IF @NightAudit > 0 -- JP 20050613 we won't be needing released data until nightaudit has been run. currentcount is only written to database at night to avoid locks
    IF @OnlyAllotment = 0 -- and (@TheHotelNumber = 0)  -- allotment is only stored for local hotel
      EXEC dbo.ReleaseAllotment @RealSystemDateStr  -- now it's always filled!

if @Print=1
begin
  select @DADATE=convert(varchar, getdate (),121)
  print '1.07: '+@DADATE
end
--TEST
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- U P D A T E  R O O M B O O K I N G S T A T  W I T H  A L L O T M E N T ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	SELECT Al.AllotmentNumber,AL.RoomTypeNumber, AL.DateStamp, HotelNumber1 HotelNumber,
		CASE WHEN @SpecifyOnRoomTypeSegPos = 1 THEN ISNULL(SegmentNumber,@SingleSegmentNumber) ELSE 0 END SegmentNumber, 
		CASE WHEN @SpecifyOnRoomTypeSegPos = 1 THEN ISNULL(PointOfSaleNumber, @SinglePointOfSaleNumber) ELSE 0 END PointOfSaleNumber,
		(InitialCount + TotalChange - ISNULL(CurrentCount,0)) FreeRoomsAllotment,
		(InitialCount + TotalChange ) TotalRoomsAllotment,
		Currentcount
	INTO #AllotmentUpdate
	FROM dbo.AllotmentLines AL (NOLOCK)  -- NOT #AllotmentLines because we need the released info!
    INNER JOIN dbo.Allotments A (NOLOCK) ON A.AllotmentNumber = Al.AllotmentNumber AND (A.MarkAsDeleted IS NULL OR CONVERT(DATETIME, DateStamp, 112) < A.MarkAsDeleted)
	INNER JOIN Roomtypes RT (NOLOCK) ON al.RoomTypeNumber = rt.RoomTypeNumber 
	INNER JOIN @HotelNumbers H ON H.HotelNumber=ISNULL(RT.HotelNumber,0)
	CROSS APPLY (
		SELECT CASE WHEN @CentralOfficeType = 0 THEN 0 ELSE ISNULL(RT.HotelNumber,0) END HotelNumber1
	) X
	OUTER APPLY (
		SELECT TOP 1 CurrentCount FROM #AllotmentLines 
		WHERE AllotmentNumber=AL.AllotmentNumber 
		AND RoomTypeNumber=AL.RoomTypeNumber 
		AND DateStamp=AL.DateStamp
	) XAL
	OUTER APPLY (
        SELECT MIN(SegmentNumber) SegmentNumber, MIN(PointOfSaleNumber) PointOfSaleNumber FROM #RoomBookingStatus RBS
        WHERE @SpecifyOnRoomTypeSegPos = 1 AND RBS.RoomTypeNumber=AL.RoomTypeNumber AND RBS.DateStamp=AL.DateStamp AND RBS.HotelNumber=HotelNumber1 
	) XRBS
	WHERE AL.DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate		
	AND (@AAllotmentNumber = 0 OR Al.AllotmentNumber = @AAllotmentNumber)
	IF @SpecifyOnRoomTypeSegPos=1
		SET @ProductNumber=@AProductNumber
	ELSE
		SET @ProductNumber=0

	MERGE #RoomBookingStatus T
	USING (
		SELECT RoomTypeNumber, DateStamp, HotelNumber, SegmentNumber, PointOfSaleNumber, SUM(FreeRoomsAllotment) FreeRoomsAllotment, SUM(TotalRoomsAllotment) TotalRoomsAllotment
		FROM #AllotmentUpdate
		GROUP BY RoomTypeNumber, DateStamp, HotelNumber, SegmentNumber, PointOfSaleNumber
	) S
	ON (T.RoomTypeNumber=S.RoomTypeNumber AND T.DateStamp=S.DateStamp AND T.HotelNumber=S.HotelNumber AND T.SegmentNumber=S.SegmentNumber 
		AND T.PointOfSaleNumber=S.PointOfSaleNumber AND T.SoldRoomType = @ASoldRoomType AND T.ProductNumber = @ProductNumber AND T.ReservationGroup = 0 AND T.RequestorID = 0)
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ProductNumber, ReservationGroup, SoldRooms ,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment, SoldPrice, RequestorID, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee)
		VALUES (S.DateStamp, S.RoomTypeNumber, S.SegmentNumber, S.PointOfSaleNumber, S.HotelNumber, @ASoldRoomType, @ProductNumber,0,0, 0, 0, 0, 0, 0, 0, 0, 0, 0, S.FreeRoomsAllotment, S.TotalRoomsAllotment, 0, 0, 0, 0)
	WHEN MATCHED THEN
		UPDATE SET FreeRoomsAllotment = T.FreeRoomsAllotment+S.FreeRoomsAllotment, TotalRoomsAllotment=T.TotalRoomsAllotment+S.TotalRoomsAllotment
	;
	IF @AllotmentOverbookType = 3
	BEGIN
		DECLARE #AllotCur CURSOR FAST_FORWARD FOR
		SELECT AllotmentNumber, RoomTypeNumber, DateStamp, FreeRoomsAllotment
		FROM #AllotmentUpdate
		WHERE FreeRoomsAllotment < 0

		OPEN #AllotCur
		FETCH NEXT FROM #AllotCur
		INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp, @FreeRoomsAllotment

		WHILE @@FETCH_STATUS <> -1
		BEGIN
			IF @@FETCH_STATUS <> -2
			BEGIN
				IF @Print=1
					PRINT 'Fixing negative allotment (calling AllotmentModify)...  '+CONVERT(VARCHAR, @FreeRoomsAllotment)

				DECLARE @ModifyStart DATETIME =  CONVERT(DATETIME, @DateStamp, 112), @ModifyEnd DATETIME
				SET @ModifyEnd = @ModifyStart + 1
				EXEC dbo.AllotmentModify @AllotmentNumber, @ModifyStart, @ModifyEnd, @RoomTypeNumber, @ARegenerateRoomBookingStatus -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - LOCKING PROBLEM - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
			END
			FETCH NEXT FROM #AllotCur
			INTO @AllotmentNumber, @RoomTypeNumber, @DateStamp, @FreeRoomsAllotment
		END
		CLOSE #AllotCur
		DEALLOCATE #AllotCur
	END

if @Print=1
begin
  select @DADATE=convert(varchar, getdate (),121)
  print '1.08: '+@DADATE
end

IF (@IncludeOccupiedInOFS = 1) AND (@AIncludeOccupiedInOFS = 1)
BEGIN
	UPDATE #ROOMBOOKINGSTATUS
	SET NumberOfOutForService = NumberOfOutForService + NumberOfOccupied
	WHERE NumberOfOccupied <> 0
END
--TEST
--SELECT A.DateStamp, A.RoomTypeNumber, A.AllotmentNumber, A.CurrentCount FROM #AllotmentLines A ORDER BY A.DateStamp, A.RoomTypeNumber, A.AllotmentNumber
--SELECT 
--RoomTypeNumber
--, Datestamp
--, HotelNumber 
--, ReservationGroup 
--, SegmentNumber 
--, PointOfSaleNumber 
--, SoldRoomType 
--, ProductNumber 
--, RequestorID
--, SoldRooms
--, SoldPrice 
--, Adults
--, Children1
--, Children2 
--, NumberOfConfirmed
--, NumberOfProvisionals
--, NumberOfGuaranteed
--, NumberOfWaitinglists
--, NumberOfTentative
--, NumberOfOutOfOrder
--, NumberOfOutForService
--, NumberOfOccupied
--, PaxAllotment
--FROM #RoomBookingStatus
--ORDER BY RoomTypeNumber,DateStamp,SegmentNumber,PointOfSaleNumber,SoldRoomType,ProductNumber,HotelNumber,ReservationGroup,RequestorID
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- P U T  D A T A  I N T O  R O O M B O O K I N G S T A T  F O R  P A S T ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- if nightaudit load data from #roombookingstatus into roombookingstatus-table - The roombookingstatus will be locked until transaction has completed!
  IF @NightAudit > 0 AND @SpecifyOnRoomTypeSegPos = 1
  BEGIN
    DECLARE @TotalRoomsAllotment SMALLINT

    DELETE FROM dbo.RoomBookingStatus
    WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate AND CONVERT(DATETIME,DateStamp,112)>=@ReservationsDeletedBefore AND HotelNumber IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers)  -- DO NOT DELETE BOOKINGSTATUS WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS

    DECLARE #RoomBookingStatusCur CURSOR FAST_FORWARD FOR
    SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ProductNumber, ReservationGroup,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
    NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms, FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment, NumberOfRoomsSoldToday,
    NumberOfOutOfOrder, NumberOfOutForService, NumberOfOccupied, NumberOfOccupiedGroup, SoldPrice, RequestorID , NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
	NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax, NumberOfWaitingArrival, NumberOfTentativeArrival, 
	WaitingArrivalPax, TentativeArrivalPax
    FROM #RoomBookingStatus
--      WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND HotelNumber = @TheHotelNumber OPTION (KEEP PLAN)
    WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND HotelNumber IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers) AND DateStamp >= @SystemDateStr OPTION (KEEP PLAN)
    
    Open #RoomBookingStatusCur
    Fetch Next From #RoomBookingStatusCur
    Into @DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRoomType, @ProductNumber, @ReservationGroup, @SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed,
         @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment, 
         @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder, @NumberOfOutForService, @NumberOfOccupied, @NumberOfOccupiedGroup, @SoldPrice, @RequestorID, @SoldRoomsCourse, @SoldRoomsAttendee,
		 @NumberOfArrival, @NumberOfInhouse, @NumberOfStay, @NumberOfDeparture, @ArrivalPax, @InhousePax, @StayPax, @DeparturePax, @NumberOfWaitingArrival, 
		 @NumberOfTentativeArrival, @WaitingArrivalPax, @TentativeArrivalPax

    While  (@@FETCH_STATUS <> -1)
    BEGIN
      IF @@FETCH_STATUS <> -2
      BEGIN
        UPDATE dbo.RoomBookingStatus SET
          SoldRooms=@SoldRooms, 
          Adults=@Adults, 
          Children1=@Children1, 
          Children2=@Children2, 
          NumberOfConfirmed=@NumberOfConfirmed, 
          NumberOfProvisionals=@NumberOfProvisionals,
          NumberOfGuaranteed=@NumberOfGuaranteed, 
          NumberOfTentative=@NumberOfTentative, 
          SoldPrice=@SoldPrice,
          NumberOfWaitinglists=@NumberOfWaitinglists, 
          NumberOfDayrooms=@NumberOfDayrooms,
          FreeRoomsAllotment=@FreeRoomsAllotment,
          TotalRoomsAllotment=@TotalRoomsAllotment,
          PaxAllotment=@PaxAllotment,
          NumberOfRoomsSoldToday=@NumberOfRoomsSoldToday,
          NumberOfOutOfOrder=@NumberOfOutOfOrder,
          NumberOfOutForService=@NumberOfOutForService,
          NumberOfOccupied=@NumberOfOccupied,
		  NumberOfOccupiedGroup= @NumberOfOccupiedGroup,
		  NumberOfRoomsSoldCourse=@SoldRoomsCourse, 
		  NumberOfRoomsSoldAttendee=@SoldRoomsAttendee,
		  NumberOfArrival=@NumberOfArrival,
		  NumberOfInhouse=@NumberOfInhouse, 
		  NumberOfStay=@NumberOfStay, 
		  NumberOfDeparture=@NumberOfDeparture, 
		  ArrivalPax=@ArrivalPax, 
		  InhousePax=@InhousePax, 
		  StayPax=@StayPax, 
		  DeparturePax=@DeparturePax,
		  NumberOfWaitingArrival=@NumberOfWaitingArrival , 
		  NumberOfTentativeArrival=@NumberOfTentativeArrival, 
		  WaitingArrivalPax=@WaitingArrivalPax, 
		  TentativeArrivalPax=@TentativeArrivalPax
		         
          --, NumberOfOutOfHouseDayRooms=@NumberOfOutOfHouseDayRooms
        WHERE DateStamp=@DateStamp AND 
			  RoomTypeNumber=@RoomTypeNumber AND 
			  SegmentNumber=@SegmentNumber AND 
			  PointOfSaleNumber=@PointOfSaleNumber AND 
			  HotelNumber=@HotelNumber AND 
			  SoldRoomType = @SoldRoomType AND 
			  ProductNumber = @ProductNumber AND
			  ReservationGroup = @ReservationGroup AND
			  RequestorID = ISNULL(@RequestorID, 0)
        IF @@rowcount = 0
          INSERT INTO dbo.RoomBookingStatus (DateStamp, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, HotelNumber, SoldRoomType, ProductNumber, ReservationGroup, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder,NumberOfOutForService,NumberOfOccupied, SoldPrice, RequestorID, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
		  NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax, NumberOfWaitingArrival, NumberOfTentativeArrival,
		  WaitingArrivalPax, TentativeArrivalPax)
          VALUES (@DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRoomType, @ProductNumber, @ReservationGroup, @SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment, @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder,@NumberOfOutForService,  @NumberOfOCcupied, @SoldPrice, ISNULL(@RequestorID, 0), @SoldRoomsCourse, @SoldRoomsAttendee,
		  @NumberOfArrival, @NumberOfInhouse, @NumberOfStay, @NumberOfDeparture, @ArrivalPax, @InhousePax, @StayPax, @DeparturePax, @NumberOfWaitingArrival, 
		  @NumberOfTentativeArrival, @WaitingArrivalPax, @TentativeArrivalPax)
      END

      Fetch Next From #RoomBookingStatusCur
      Into @DateStamp, @RoomTypeNumber, @SegmentNumber, @PointOfSaleNumber, @HotelNumber, @SoldRoomType, @ProductNumber, @ReservationGroup,@SoldRooms, @Adults, @Children1, @Children2, @NumberOfConfirmed, 
      @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglists, @NumberOfDayrooms, @FreeRoomsAllotment, @TotalRoomsAllotment,
      @PaxAllotment, @NumberOfRoomsSoldToday, @NumberOfOutOfOrder,@NumberOfOutForService, @NumberOfOccupied, @NumberOfOccupiedGroup, @SoldPrice, @RequestorID, @SoldRoomsCourse, @SoldRoomsAttendee,
	  @NumberOfArrival, @NumberOfInhouse, @NumberOfStay, @NumberOfDeparture, @ArrivalPax, @InhousePax, @StayPax, @DeparturePax, @NumberOfWaitingArrival, 
	  @NumberOfTentativeArrival, @WaitingArrivalPax, @TentativeArrivalPax
    END

    CLOSE #RoomBookingStatusCur
    DEALLOCATE #RoomBookingStatusCur
  END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- U P D A T E   R O O M B O O K I N G S T A T  D U R I N G  T H E  D A Y ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF @AUpdateRoomBookingStatus = 1 AND @SpecifyOnRoomTypeSegPos = 1
  BEGIN
	
	DELETE FROM dbo.RoomBookingStatus
	WHERE DateStamp >= @SystemDateStr 
	AND DateStamp IN 
	(SELECT rbs.DateStamp FROM dbo.RoomBookingStatus rbs (NOLOCK)
	 LEFT OUTER JOIN #RoomBookingStatus rbst (NOLOCK) ON rbs.DateStamp = rbst.DateStamp AND 
														 rbs.RoomTypeNumber = rbst.ROomTypeNumber AND
														 rbs.SegmentNumber = rbst.SegmentNumber AND
														 rbs.PointOfSaleNumber = rbst.PointOfSaleNumber AND
														 rbs.HotelNumber = rbst.HotelNumber AND
														 rbs.SoldRoomType = rbst.SoldRoomType AND
														 rbs.ReservationGroup = rbst.ReservationGroup AND
														 rbs.ProductNumber = rbst.ProductNumber AND
														 rbs.RequestorID = rbst.RequestorID
	 WHERE rbst.DateStamp IS NULL)

	 INSERT INTO dbo.RoomBookingStatus (DateStamp, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, HotelNumber, SoldRoomType, ReservationGroup, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder,NumberOfOutForService, NumberOfOccupied, NumberOfOccupiedGroup, SoldPrice, ProductNumber, RequestorID, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
	 NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax,NumberOfWaitingArrival, NumberOfTentativeArrival,
	 WaitingArrivalPax, TentativeArrivalPax)
	 SELECT rbst.DateStamp, rbst.RoomTypeNumber, rbst.SegmentNumber, rbst.PointOfSaleNumber, rbst.HotelNumber, rbst.SoldRoomType, rbst.ReservationGroup, rbst.SoldRooms, rbst.Adults, rbst.Children1, rbst.Children2, rbst.NumberOfConfirmed, rbst.NumberOfProvisionals, rbst.NumberOfGuaranteed, rbst.NumberOfTentative, rbst.NumberOfWaitinglists, rbst.NumberOfDayrooms, rbst.FreeRoomsAllotment, rbst.TotalRoomsAllotment, rbst.PaxAllotment, rbst.NumberOfRoomsSoldToday, rbst.NumberOfOutOfOrder,rbst.NumberOfOutForService, rbst.NumberOfOccupied, rbst.NumberOfOccupiedGroup, rbst.SoldPrice, rbst.ProductNumber, rbst.RequestorID, rbst.NumberOfRoomsSoldCourse, rbst.NumberOfRoomsSoldAttendee,
	 rbst.NumberOfArrival, rbst.NumberOfInhouse, rbst.NumberOfStay, rbst.NumberOfDeparture, rbst.ArrivalPax, rbst.InhousePax, rbst.StayPax, rbst.DeparturePax,
	 rbst.NumberOfWaitingArrival, rbst.NumberOfTentativeArrival, rbst.WaitingArrivalPax, rbst.TentativeArrivalPax
	 FROM #RoomBookingStatus rbst (NOLOCK)
	 LEFT OUTER JOIN dbo.RoomBookingStatus rbs (NOLOCK) ON rbs.DateStamp = rbst.DateStamp AND 
														 rbs.RoomTypeNumber = rbst.ROomTypeNumber AND
														 rbs.SegmentNumber = rbst.SegmentNumber AND
														 rbs.PointOfSaleNumber = rbst.PointOfSaleNumber AND
														 rbs.HotelNumber = rbst.HotelNumber AND
														 rbs.SoldRoomType = rbst.SoldRoomType AND
														 rbs.ReservationGroup = rbst.ReservationGroup AND
														 rbs.ProductNumber = rbst.ProductNumber AND
														 rbs.RequestorID = rbst.RequestorID
	WHERE rbs.Datestamp IS NULL

	UPDATE dbo.RoomBookingStatus
	SET SoldRooms=rbst.SoldRooms, 
          Adults=rbst.Adults, 
          Children1=rbst.Children1, 
          Children2=rbst.Children2, 
          NumberOfConfirmed=rbst.NumberOfConfirmed, 
          NumberOfProvisionals=rbst.NumberOfProvisionals,
          NumberOfGuaranteed=rbst.NumberOfGuaranteed, 
          NumberOfTentative=rbst.NumberOfTentative, 
          SoldPrice=rbst.SoldPrice,
          NumberOfWaitinglists=rbst.NumberOfWaitinglists, 
          NumberOfDayrooms=rbst.NumberOfDayrooms,
          FreeRoomsAllotment=rbst.FreeRoomsAllotment,
          TotalRoomsAllotment=rbst.TotalRoomsAllotment,
          PaxAllotment=rbst.PaxAllotment,
          NumberOfRoomsSoldToday=rbst.NumberOfRoomsSoldToday,
          NumberOfOutOfOrder=rbst.NumberOfOutOfOrder,
          NumberOfOutForService=rbst.NumberOfOutForService,
          NumberOfOccupied=rbst.NumberOfOccupied,
		  NumberOfOccupiedGroup= rbst.NumberOfOccupiedGroup,
		  NumberOfRoomsSoldCourse=rbst.NumberOfRoomsSoldCourse,
		  NumberOfRoomsSoldAttendee=rbst.NumberOfRoomsSoldAttendee,
		  NumberOfArrival=rbst.NumberOfArrival,
		  NumberOfInhouse=rbst.NumberOfInhouse, 
		  NumberOfStay=rbst.NumberOfStay, 
		  NumberOfDeparture=rbst.NumberOfDeparture, 
		  ArrivalPax=rbst.ArrivalPax, 
		  InhousePax=rbst.InhousePax, 
		  StayPax=rbst.StayPax, 
		  DeparturePax=rbst.DeparturePax,
		  NumberOfWaitingArrival=rbst.NumberOfWaitingArrival, 
		  NumberOfTentativeArrival=rbst.NumberOfTentativeArrival, 
		  WaitingArrivalPax=rbst.WaitingArrivalPax, 
		  TentativeArrivalPax=rbst.TentativeArrivalPax
	FROM #RoomBookingStatus rbst
	INNER JOIN dbo.RoomBookingStatus rbs (NOLOCK) ON rbs.DateStamp = rbst.DateStamp AND 
														 rbs.RoomTypeNumber = rbst.ROomTypeNumber AND
														 rbs.SegmentNumber = rbst.SegmentNumber AND
														 rbs.PointOfSaleNumber = rbst.PointOfSaleNumber AND
														 rbs.HotelNumber = rbst.HotelNumber AND
														 rbs.SoldRoomType = rbst.SoldRoomType AND
														 rbs.ReservationGroup = rbst.ReservationGroup AND
														 rbs.ProductNumber = rbst.ProductNumber AND
														 rbs.RequestorID = rbst.RequestorID
  END

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- P U T  Y E S T E R D A Y S  B O O K I N G S T A T U S   I N T O  O T H E R   T A B L E ------     I N S A L E     ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
if @Print=1
begin
  select @DADATE=convert(varchar, getdate (),121)
  print '1.09: '+@DADATE
end

  IF (@NightAudit > 0) AND (@FormatedEndDate > @RealSystemDateStr) AND @SpecifyOnRoomTypeSegPos = 1 -- not from regeneratebookingstatus sp
  BEGIN
    IF @TheHotelNumber = '' OR NOT EXISTS (SELECT * FROM @HotelNumbers WHERE HotelNumber <> 0) -- only drop table when localhotel or all hotels
      DELETE FROM dbo.RoomBookingStatusInsale WHERE CONVERT(DATETIME,DateStamp,112)>=@ReservationsDeletedBefore   -- DO NOT DELETE BOOKINGSTATUS WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS
    ELSE
      DELETE FROM dbo.RoomBookingStatusInsale WHERE HotelNumber IN (select distinct HotelNumber FROM @HotelNumbers) AND CONVERT(DATETIME,DateStamp,112)>=@ReservationsDeletedBefore   -- DO NOT DELETE BOOKINGSTATUS WHEN WE ACTUALLY DO NOT HAVE ANY RESERVATIONS

    IF @TheHotelNumber <> ''
    BEGIN
      DECLARE #StatusCopyCur CURSOR FAST_FORWARD FOR

        SELECT DateStamp, IsNull(HotelNumber, 0), SoldRoomType, SoldRooms,Adults,Children1,Children2,SoldPrice,TotalRoomsAllotment FROM #RoomBookingStatus (NOLOCK)
        WHERE HotelNumber IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers) OPTION (KEEP PLAN)
    END
    ELSE -- all hotels
    BEGIN
      DECLARE #StatusCopyCur CURSOR FAST_FORWARD FOR

        SELECT DateStamp, IsNull(HotelNumber, 0),SoldRoomType, SoldRooms,Adults,Children1,Children2,SoldPrice,TotalRoomsAllotment FROM #RoomBookingStatus (NOLOCK) OPTION (KEEP PLAN)
    END

    OPEN #StatusCopyCur
    FETCH NEXT FROM #StatusCopyCur
    INTO @DateStamp, @HotelNumber, @SoldRoomType, @SoldRooms, @Adults, @Children1, @Children2, @SoldPrice, @TotalRoomsAllotment

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF @@FETCH_STATUS <> -2
      BEGIN
        IF @IncludeAllotmentInInsale = 0
          SET @TotalRoomsAllotment = 0

        IF (@SoldRooms+@TotalRoomsAllotment > 0) OR (@SoldPrice > 0)  -- no need to do anything if no number
        BEGIN
          UPDATE dbo.RoomBookingStatusInsale SET SoldRooms=SoldRooms+@SoldRooms+@TotalRoomsAllotment, SoldPrice=SoldPrice+@SoldPrice 
          WHERE DateStamp=@DateStamp AND HotelNumber=@HotelNumber AND SoldRoomType = @SoldRoomType
          IF @@rowcount = 0
            INSERT INTO dbo.RoomBookingStatusInsale (DateStamp,HotelNumber, SoldRoomType, SoldRooms,SoldPrice)
            VALUES (@DateStamp, @HotelNumber, @SoldRoomType, @SoldRooms+@TotalRoomsAllotment,@SoldPrice)
        END
      END	

      FETCH NEXT FROM #StatusCopyCur
      INTO @DateStamp, @HotelNumber, @SoldRoomType, @SoldRooms, @Adults, @Children1, @Children2, @SoldPrice, @TotalRoomsAllotment
    END

    CLOSE #StatusCopyCur
    DEALLOCATE #StatusCopyCur

    -- Fixes problem in product search
    UPDATE Products SET MinimumDays=0 WHERE MinimumDays IS NULL
    UPDATE Products SET MaximumDays=0 WHERE MaximumDays IS NULL
  END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
END -- IF @FormatedEndDate >= @SystemDateStr

SET @STARTTime = Current_timeStamp

if @Print=1
begin
  select @DADATE=convert(varchar, getdate (),121)
  print '1.10: '+@DADATE
end

if @NightAudit = 0 AND @AUpdateRoomBookingStatus = 0
BEGIN
  IF @FormatedStartDate<@SystemDateStr
  BEGIN
    IF @FormatedEndDate<@SystemDateStr
    BEGIN
      SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRoomType,ProductNumber, ReservationGroup,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
      NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
      NumberOfOutOfOrder,NumberOfOutForService, NumberOfOccupied, NumberOfOccupiedGroup,SoldPrice, RequestorID, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
	  NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax,NumberOfWaitingArrival, NumberOfTentativeArrival,
	  WaitingArrivalPax, TentativeArrivalPax
      FROM dbo.RoomBookingStatus (NOLOCK)
      WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate  AND ISNULL(HotelNumber,0) IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers)
      AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
      AND (@AProductNumber = '' OR ProductNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@AProductNumber)))
      AND (@ASoldRoomType = 2 OR SoldRoomType = @ASoldRoomType)
      ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
    END
    ELSE
    BEGIN
	  IF  @SpecifyOnRoomTypeSegPos = 1
		  SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ProductNumber,ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
		  NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
		  NumberOfOutOfOrder,NumberOfOutForService, NumberOfOccupied, NumberOfOccupiedGroup,SoldPrice, RequestorID, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
		  NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax, NumberOfWaitingArrival, NumberOfTentativeArrival,
		  WaitingArrivalPax, TentativeArrivalPax
		  FROM #RoomBookingStatus (NOLOCK)   -- So that the SP can used as a query                                                                                                                    -- same as below
		  WHERE DateStamp BETWEEN @SystemDateStr AND @FormatedEndDate AND ISNULL(HotelNumber, 0) IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers)
		  AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
		  AND (@AProductNumber = '' OR ProductNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@AProductNumber)))
		  AND (@ASoldRoomType = 2 OR SoldRoomType = @ASoldRoomType)
		  UNION
		  SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRoomType, ProductNumber,ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
		  NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
		  NumberOfOutOfOrder,NumberOfOutForService, NumberOfOCcupied, NumberOfOccupiedGroup,SoldPrice, RequestorID, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
		  NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax,NumberOfWaitingArrival, NumberOfTentativeArrival,
		  WaitingArrivalPax, TentativeArrivalPax
		  FROM dbo.RoomBookingStatus (NOLOCK)
		  WHERE DateStamp < @SystemDateStr AND DateStamp >= @FormatedStartDate AND ISNULL(HotelNumber, 0) IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers)
		  AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
		  AND (@AProductNumber = '' OR ProductNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@AProductNumber)))
		  AND (@ASoldRoomType = 2 OR SoldRoomType = @ASoldRoomType)
		  ORDER BY HotelNumber,DateStamp -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
	ELSE
		SELECT DateStamp,RoomTypeNumber,HotelNumber, ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
		  NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
		  NumberOfOutOfOrder,NumberOfOutForService, NumberOfOccupied, NumberOfOccupiedGroup,SoldPrice
		  FROM #RoomBookingStatus (NOLOCK)   -- So that the SP can used as a query                                                                                                                    -- same as below
		  WHERE DateStamp BETWEEN @SystemDateStr AND @FormatedEndDate AND ISNULL(HotelNumber, 0) IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers)
		  AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
		  
		  UNION
		  SELECT DateStamp,RoomTypeNumber,HotelNumber,ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
		  NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
		  NumberOfOutOfOrder,NumberOfOutForService, NumberOfOCcupied, NumberOfOccupiedGroup,SoldPrice 
		  FROM dbo.RoomBookingStatus (NOLOCK)
		  WHERE DateStamp < @SystemDateStr AND DateStamp >= @FormatedStartDate AND ISNULL(HotelNumber, 0) IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers)
		  AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
		
		  ORDER BY HotelNumber,DateStamp -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
    END
  END
  ELSE IF @SpecifyOnRoomTypeSegPos = 1
  BEGIN
    SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber, SoldRoomType, ProductNumber,ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
    NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
    NumberOfOutOfOrder,NumberOfOutForService, NumberOfOccupied, NumberOfOccupiedGroup,SoldPrice, RequestorID, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
	NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax,NumberOfWaitingArrival, NumberOfTentativeArrival,
	WaitingArrivalPax, TentativeArrivalPax
    FROM #RoomBookingStatus (NOLOCK)   -- So that the SP can used as a query                                                                                                                    -- same as above
    WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate AND ISNULL(HotelNumber, 0) IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers)
    AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
    AND (@ASoldRoomType = 2 OR SoldRoomType = @ASoldRoomType)
    ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
  END
  ELSE -- NOT @SpecifyOnRoomTypeSegPos
  BEGIN
    SELECT DateStamp,RoomTypeNumber,HotelNumber, ReservationGroup, SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,
		  NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, 
		  NumberOfOutOfOrder,NumberOfOutForService, NumberOfOccupied, NumberOfOccupiedGroup,SoldPrice FROM #RoomBookingStatus (NOLOCK)
    WHERE DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate AND ISNULL(HotelNumber, 0) IN (SELECT DISTINCT HotelNumber FROM @HotelNumbers)
    AND (@ARoomTypeStr = '' OR RoomTypeNumber IN (SELECT DISTINCT Number FROM DBO.iter_intlist_to_table(@ARoomTypeStr)))
    ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
  END
END

IF ISNULL(@NightAudit, 0) = 1
BEGIN
  DELETE RoomBookingStatusHist WHERE SystemDate = @SystemDateStr

  INSERT INTO RoomBookingStatusHist (SystemDate, DateStamp, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, 
    TotalRoomsAllotment, HotelNumber, NumberOfRoomsSoldToday, NumberOfOutOfOrder, PaxAllotment, SoldPrice, NumberOfOutForService, SoldRoomType, ReservationGroup, NumberOfOccupiedGroup, ProductNumber, SoldSubRooms, RequestorID, NumberOfOccupied, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
	NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax, NumberOfWaitingArrival, NumberOfTentativeArrival,
	WaitingArrivalPax, TentativeArrivalPax)
  SELECT @SystemDateStr AS [SystemDate], DateStamp, RoomTypeNumber, SegmentNumber, PointOfSaleNumber, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, 
    TotalRoomsAllotment, HotelNumber, NumberOfRoomsSoldToday, NumberOfOutOfOrder, PaxAllotment, SoldPrice, NumberOfOutForService, SoldRoomType, ReservationGroup, NumberOfOccupiedGroup, ProductNumber, SoldSubRooms, RequestorID, NumberOfOccupied, NumberOfRoomsSoldCourse, NumberOfRoomsSoldAttendee,
	NumberOfArrival, NumberOfInhouse, NumberOfStay, NumberOfDeparture, ArrivalPax, InhousePax, StayPax, DeparturePax, NumberOfWaitingArrival, NumberOfTentativeArrival,
	WaitingArrivalPax, TentativeArrivalPax
  FROM RoomBookingStatus
  WHERE DateStamp >= @SystemDateStr
END

if @Print=1
begin
  select @DADATE=convert(varchar, getdate (),121)
  print '2.00: '+@DADATE
END

-- BA/090401: CR007579: Try to aviod Lock time out when opening the Roomtypes plan
DECLARE @SqlStr VARCHAR(25)
SET @SqlStr = 'SET LOCK_TIMEOUT '+ CONVERT(VARCHAR, @LockTimeOut)
EXEC (@SqlStr)

--DROP TABLE #T4
--DROP TABLE #T6
--DROP TABLE #RoomBookingStatus
--DROP TABLE #AllotmentLines
--DROP TABLE #NumberOfSubRooms
