--ALTER PROCEDURE dbo.CalcAutoProfile @AType TINYINT, @AStartDate DATETIME, @AEndDate DATETIME,  @AHotelNumber INT = -1  AS
CREATE PROCEDURE dbo.CalcAutoProfile @AType TINYINT, @AStartDate DATETIME, @AEndDate DATETIME,  @AHotelNumber INT = -1  AS

/*

- Author: AV 

4.3+
- BA/20110906: RQ999/Adding support for Turnover.
- BA/20110412: CR023226/Adding support for Equal Days.
- BA/20101026; RQ493/Add support for allocation in RoomNo sort order (erfa 102)
- BA/20100607: CR014548/Equal Stay couldn't handle Owner rooms that are set OOO and OOO (ReferenceNumber = 0).

---------------------------
-       BA/071220: Added support for checking Reservations.ExcludeAutoProfile

              AV/      : Fix for hotelnumber

        BA/080107: Fix for ensuring that all Room's are inserted into the RoomAllocationProfileXtab table
  AV/BA/080108: AutoProfilexxxDate is set to the NightAudit date (AutoProfileEqualDayDate, AutoProfileEqualStayDate). See ReservationFormUnit.ValidateCell and Search for EqualStay.
 

*/

 

/*

@Type:
 0: Normal
 1: Equal Stays
 2: Equal Days (CR023226)
 3: RoomNo sort (RQ493)
 4: Turnover (RQ999)
*/

 

DECLARE @AllocationProfileNumber INT

DECLARE @Text VARCHAR(20)

DECLARE @RoomNumber INT

DECLARE @Count INT

DECLARE @DepartureDate VARCHAR(8)

DECLARE @SortNumber INT

DECLARE @NewHotelNumber INT

DECLARE @SearchKey VARCHAR(30)
DECLARE @KeyValue CHAR(8)
DECLARE @SystemDate CHAR(8)
SELECT @SystemDate = SystemDate FROM System WITH (NOLOCK)
 

IF @AHotelNumber = -1
  SET @NewHotelNumber  = 0
ELSE
  SET @NewHotelNumber  = @AHotelNumber

IF ISNULL(@AType, 0) < 1 OR ISNULL(@AType, 0) > 4
  SET @AType = 0

IF @AType = 1 SET @Text = 'Equal Stays'
ELSE IF @AType = 2 SET @Text = 'Equal Days' 
ELSE IF @AType = 3 SET @Text = 'RoomNo Sort'
ELSE IF @AType = 4 SET @Text = 'Turnover'
ELSE SET @Text = ''


-- Does the Profile exists in the Database? Else create it.
IF NOT EXISTS(SELECT * FROM AllocationProfile WHERE Type = @AType AND HotelNumber = @NewHotelNumber )
BEGIN
  INSERT INTO AllocationProfile (hotelnumber, text, Type) values (@NewHotelNumber, @Text, @AType)
  SELECT @AllocationProfileNumber = @@Identity FROM AllocationProfile WITH (NOLOCK)    
END
ELSE
  SELECT @AllocationProfileNumber = AllocationProfileNumber FROM AllocationProfile WHERE Type = @AType AND (HotelNumber = @NewHotelNumber)


-- Delete old profile 

DELETE FROM RoomAllocationProfileXtab WHERE AllocationProfileNumber = @AllocationProfileNumber


-- BA/080107: Fix for ensuring that all Room's are inserted into the RoomAllocationProfileXtab table

DECLARE RoomCursor CURSOR FOR
SELECT RoomNumber FROM ROOMS
WHERE ISNULL(HotelNumber, 0) = @NewHotelNumber -- (HotelNumber, 0) = @NewHotelNumber) OR ((@HotelNumber = 0) AND (HotelNumber IS NULL))
OPEN RoomCursor
FETCH NEXT FROM RoomCursor INTO @RoomNumber
WHILE (@@fetch_status = 0)
BEGIN
  INSERT INTO RoomAllocationProfileXtab (AllocationProfileNumber, RoomNumber, SortNumber) VALUES (@AllocationProfileNumber, @RoomNumber, 0)
  FETCH NEXT FROM RoomCursor INTO @RoomNumber
END
CLOSE RoomCursor
DEALLOCATE RoomCursor

-- Count 'Equal Stays' from the reservations and do a sort

SET @SortNumber = 1

IF @AType = 1 -- EQUAL STAY
BEGIN 
  DECLARE AlCursor  CURSOR FOR
  SELECT MIN(RoomNumber), COUNT(DISTINCT ReservationRoomline.Linenumber), MAX(ReservationRoomline.DepartureDate)  
  FROM ReservationRoomLine WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) 
      ON ReservationRoomLineXTab.ReferenceNumber > 0 /*CR014548*/ AND 
         ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber AND 
         RoomNumber >0 AND DepartureDate>= CONVERT(VARCHAR, @AStartDate, 112) AND 
         ArrivalDate<=CONVERT(VARCHAR,@AEndDate,112)
    INNER JOIN Reservations WITH (NOLOCK) 
      ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber AND 
         ReservationRoomLineXTab.GuestNumber = Reservations.Guestnumber AND 
         Status <=6 AND ISNULL(Reservations.ExcludeAutoProfile, 0) = 0 
    INNER JOIN ReservationMain WITH (NOLOCK) 
      ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber AND 
         (ReservationMain.HotelNumber = @AHotelNumber OR @AHotelNumber = -1)
  GROUP BY Roomnumber
  ORDER BY COUNT(DISTINCT ReservationRoomline.Linenumber), MAX(ReservationRoomline.DepartureDate)

  -- Insert 'Equal stays' into the RoomAllocationProfileXtab

  OPEN AlCursor

  FETCH NEXT FROM AlCursor INTO @RoomNumber, @Count, @DepartureDate
  WHILE (@@fetch_status = 0)
  BEGIN
  --  INSERT INTO RoomAllocationProfileXtab (AllocationProfileNumber, RoomNumber, SortNumber) VALUES (@AllocationProfileNumber, @RoomNumber, @SortNumber)

    UPDATE RoomAllocationProfileXtab 
    SET SortNumber = @SortNumber
    WHERE AllocationProfileNumber = @AllocationProfileNumber AND RoomNumber = @RoomNumber

    SET @SortNumber = @SortNumber + 1

    FETCH NEXT FROM AlCursor INTO @RoomNumber, @Count, @DepartureDate
  END

  CLOSE AlCursor

  DEALLOCATE AlCursor
END ELSE
IF @AType = 2 -- Equal Days (CR023226)
BEGIN
  DECLARE AlCursor CURSOR FOR
  SELECT RL.RoomNumber, SUM(datediff(day, RL.ArrivalDate, RL.DepartureDate)) AS Days
  FROM ReservationRoomLine RL WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXTab RLX WITH (NOLOCK) 
      ON RLX.ReferenceNumber > 0 AND 
         RL.LineNumber = RLX.LineNumber AND 
         RoomNumber > 0 AND DepartureDate >= CONVERT(VARCHAR, @AStartDate, 112) AND 
         ArrivalDate <= CONVERT(VARCHAR, @AEndDate,112)
    INNER JOIN Reservations R WITH (NOLOCK) 
      ON RLX.ReferenceNumber = R.ReferenceNumber AND 
         RLX.GuestNumber = R.Guestnumber AND 
         Status <= 6 AND ISNULL(R.ExcludeAutoProfile, 0) = 0 
    INNER JOIN ReservationMain RM WITH (NOLOCK) 
      ON R.ReferenceNumber = RM.ReferenceNumber AND 
         (RM.HotelNumber = @AHotelNumber OR @AHotelNumber = -1)
  GROUP BY RoomNumber      
  ORDER BY Days
    
  OPEN AlCursor

  FETCH NEXT FROM AlCursor INTO @RoomNumber, @Count /*Days*/
  WHILE (@@fetch_status = 0)
  BEGIN
    UPDATE RoomAllocationProfileXtab 
    SET SortNumber = @SortNumber
    WHERE AllocationProfileNumber = @AllocationProfileNumber AND RoomNumber = @RoomNumber

    SET @SortNumber = @SortNumber + 1

    FETCH NEXT FROM AlCursor INTO @RoomNumber, @Count /*Days*/
  END
  CLOSE AlCursor
  DEALLOCATE AlCursor
END ELSE
IF @AType = 3 -- RoomNo Sort
BEGIN
  DECLARE RoomCursor CURSOR FOR
  SELECT RoomNumber FROM ROOMS
  WHERE ISNULL(HotelNumber, 0) = @NewHotelNumber 
  ORDER BY Number
  OPEN RoomCursor
  FETCH NEXT FROM RoomCursor INTO @RoomNumber
  WHILE (@@fetch_status = 0)
  BEGIN
    UPDATE RoomAllocationProfileXtab SET SortNumber = @SortNumber
    WHERE AllocationProfileNumber = @AllocationProfileNumber AND RoomNumber = @RoomNumber
    FETCH NEXT FROM RoomCursor INTO @RoomNumber
    SET @SortNumber = @SortNumber + 1
  END
  CLOSE RoomCursor
  DEALLOCATE RoomCursor
END ELSE
IF @AType = 4 -- Turnover
BEGIN
  -- BA/20110906: SQL Provided by Kent.
  DECLARE TurnoverCursor CURSOR FOR
    SELECT r.RoomNumber --, SUM(t.PriceOverwrite * (CASE t.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN a.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END) AS Turnover
    FROM Transactions t WITH (NOLOCK) 
    INNER JOIN Folios f WITH (NOLOCK) ON f.FolioNumber = t.FolioNumber
    INNER JOIN RESERVATIONS RE WITH (NOLOCK) 
      ON RE.ReferenceNumber = F.ReferenceNumber AND RE.GuestNumber = T.GuestNumber AND
         ISNULL(RE.ExcludeAutoProfile, 0) = 0 
    INNER JOIN Accounts a WITH (NOLOCK) ON a.AccountNumber = t.AccountNumber
    INNER JOIN Customer c WITH (NOLOCK) ON c.CustomerNumber = t.HotelCustomerNumber
      AND c.HotelNumber = @AHotelNumber AND c.CustomerTypeNumber = 7 AND ISNULL(c.MarkAsDeleted, 0) = 0
    INNER JOIN Rooms r WITH (NOLOCK) ON CONVERT(VARCHAR, r.Number) = CONVERT(VARCHAR, t.Note)
    INNER JOIN Ware w WITH (NOLOCK) ON w.WareNumber = t.WareNumber
    INNER JOIN WareGroups wg (NOLOCK) ON wg.WareGroupNumber = w.WareGroupNumber
      AND wg.Payment IN (2,7) -- Rooms and manuel rooms
    WHERE t.ShowOnBillFlag = 1
      AND CONVERT(CHAR(8), t.DateStamp, 112) >= CONVERT(CHAR(8), @AStartDate, 112)
      AND CONVERT(CHAR(8), t.DateStamp, 112) <= CONVERT(CHAR(8), @AEndDate, 112)
    GROUP BY r.RoomNumber
--    ORDER BY Turnover
    ORDER BY SUM(t.PriceOverwrite * (CASE t.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN a.AccountType IN (4,5,6) THEN 1 ELSE 1+(VatValue/100) END)
  OPEN TurnoverCursor
  FETCH NEXT FROM TurnoverCursor INTO @RoomNumber
  WHILE (@@fetch_status = 0)
  BEGIN
    UPDATE RoomAllocationProfileXtab SET SortNumber = @SortNumber
    WHERE AllocationProfileNumber = @AllocationProfileNumber AND RoomNumber = @RoomNumber
    FETCH NEXT FROM TurnoverCursor INTO @RoomNumber
    SET @SortNumber = @SortNumber + 1
  END
  CLOSE TurnoverCursor
  DEALLOCATE TurnoverCursor
END

-- Update the date settings: These corresponds to hsAutoProfileEqualStayDate, hsAutoProfileEqualDate in the HotelSetupTL.TSetting.
-- The date is used together with formRoomSelect
SET @SearchKey = CASE @AType 
  WHEN 0 THEN 'Normal' 
  WHEN 1 THEN 'EqualStay'
  WHEN 2 THEN 'EqualDay'
  WHEN 3 THEN 'RoomNoSort'
  WHEN 4 THEN 'Turnover'
END

SET @Searchkey = 'AutoProfile'  + @SearchKey + 'Date'

IF NOT EXISTS(SELECT * FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = @Searchkey)
  INSERT INTO HotelSetup (Keyvalue, Searchkey) VALUES (@SystemDate, @Searchkey )
ELSE
  UPDATE HotelSetup SET KeyValue = @SystemDate WHERE SearchKey = @Searchkey