CREATE PROCEDURE dbo.ArrangementCalcEnvironmentFee @AReferenceNumber INT AS

/*
--This SP runs through the roomlines and hall lines and calculates enviromentfee by calling sp productpricesday. The total fee is saved in an arrangementline on the reservation

--CREATED AV 20070904
KJ 20190321: Optimized call to ProductPricesDay

--Last Changed AV 2008 0505 Fix for arrangementline without date
--Last Changed AV 2008 0423 Fix for arrangementline double registration
--Last Changed AV 20071004 FIX for result always NULL: sp productpricesday must be called with OUTPUT params
--Last Changed: AV 20070925 FIX invalid cursor fetch
--Last Changed: AV 20070904 clean up. Reserved words made UPPERCASE
*/

DECLARE @Result SMALLMONEY
DECLARE @LineNumber INT
DECLARE @NameListCreated BIT

DECLARE @ProductNumber INT

DECLARE @ArrivalDate DATETIME
DECLARE @DepartureDate DATETIME
DECLARE @PostDateTime DATETIME
DECLARE @ArrDate DATETIME
DECLARE @Depdate DATETIME
DECLARE @ArrTime CHAR(5)
DECLARE @DepTime CHAR(5)
DECLARE @GuestNumber INT
DECLARE @Arr24HoursStarts char(5)

DECLARE @Adults INT
DECLARE @Children1 INT
DECLARE @Children2 INT
DECLARE @NumberOfRooms INT
DECLARE @RoomTypeNumber INT
DECLARE @Day2 INT
DECLARE @Day3 INT
DECLARE @Day4 INT

DECLARE @ProductNumber2 INT
DECLARE @ProductNumber3 INT
DECLARE @ProductNumber4 INT

DECLARE @Loopcounter INT
DECLARE @DoContinue BIT

DECLARE @Addon MONEY
DECLARE @BillTextProduct VARCHAR(50) 
DECLARE @BillTextAddOn VARCHAR(50) 
DECLARE @CalcPriceDay MONEY
DECLARE @PacketNumber INT
DECLARE @EnvironmentFee SMALLMONEY
DECLARE @FixedPrice BIT
DECLARE @VATProduct BIT
DECLARE @SpecifiedDepartureAddOn BIT
DECLARE @NamelistSetting INT
DECLARE @OnePersonPaysForAll BIT
DECLARE @EnvironmentFeeType FLOAT

DECLARE @WareNumber INT
DECLARE @ArrangementLineNumber INT
DECLARE @WareText VARCHAR(50)
DECLARE @EXDepartment INT
DECLARE @PK_Department INT
DECLARE @IdentityValue INT
DECLARE @ArrangementDate VARCHAR(8)

SELECT @OnePersonPaysForAll=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey ='OnePersonPaysForAll'
IF IsNull (@OnePersonPaysForAll, 0)=0
  SET @OnePersonPaysForAll=0


SELECT @Arr24HoursStarts = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='Arr24HoursStarts'
SET @Result = 0

  --ROOMS

  DECLARE RoomLineCur CURSOR FORWARD_ONLY FOR
  SELECT DISTINCT Linenumber FROM ReservationRoomLineXtab WITH (NOLOCK) WHERE ReferenceNumber = @AReferenceNumber
  OPEN RoomLineCur
  FETCH NEXT FROM RoomLineCur INTO @linenumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN

    IF EXISTS (SELECT * FROM Reservationroomlinextab WITH (NOLOCK) WHERE LineNumber=@LineNumber and GuestNumber = 0)
      SET @NameListCreated = 0
    ELSE
      SET @NameListCreated = 1
    SELECT @NumberOfRooms = ISNULL(NumberOfRooms,1), @RoomTypeNumber=BookedTypeNumber FROM ReservationRoomLine WITH (NOLOCK) WHERE LineNumber=@LineNumber

    IF @NameListCreated = 1
    BEGIN
      SET @NumberOfRooms = 1
      DECLARE RoomLineCur1 CURSOR FORWARD_ONLY FOR
      SELECT DISTINCT GuestNumber, ProductNumber, Productnumber2, Productnumber3, ProductNumber4, Day2, Day3, Day4, CONVERT(DATETIME, Arrivaldate, 112),CONVERT(DATETIME, DepartureDate,112), Adults, Children1, Children2 FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber= @AReferenceNumber AND GuestNumber IN (SELECT GuestNumber FROM ReservationRoomLineXTab WITH (NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND LineNumber = @LineNumber) AND Status < 7
      OPEN RoomLineCur1
      FETCH NEXT FROM RoomLineCur1 INTO @GuestNumber, @ProductNumber, @Productnumber2, @Productnumber3, @Productnumber4, @Day2, @Day3, @Day4,@ArrivalDate,@DepartureDate, @Adults, @Children1, @Children2
      WHILE    (@@FETCH_STATUS <> -1)
      BEGIN
        SET @LoopCounter=0
        SET @DoContinue = 1
         WHILE   @LoopCounter <=4 and @DoContinue =1
         BEGIN
           SET @Loopcounter = @LoopCounter+1
           IF @LoopCounter = 1
           BEGIN
             IF ISNULL(@Day2, 0) = 0
             BEGIN
               SET @ArrDate = @Arrivaldate
               SET @Depdate = @departuredate
               SET @DoContinue=0
             end
             ELSE
             BEGIN
               SET @ArrDate= @Arrivaldate
               SET @depdate= @Arrivaldate + @day2 - 1
             END            


           end
           ELSE IF @LoopCounter = 2
           BEGIN
             SET @productnumber=@productnumber2
             IF ISNULL(@Day3, 0) = 0
             BEGIN
               SET @arrdate= @Arrivaldate +@Day2 - 1
               SET @depdate= @departuredate
               SET @DoContinue=0
             end
             ELSE
             BEGIN
               SET @arrdate = @Arrivaldate +@day2 -1
               SET @depdate = @Arrivaldate +@day3 -1
             END             
           end
           ELSE IF @LoopCounter = 3
           BEGIN
             SET @productnumber=@productnumber3
             IF ISNULL(@Day4, 0) = 0
             BEGIN
               SET @arrdate= @Arrivaldate +@day3 -1
               SET @depdate= @departuredate
               SET @DoContinue=0
             end
             ELSE
             BEGIN
               SET @arrdate= @Arrivaldate +@day3 -1
               SET @depdate= @Arrivaldate +@day4 -1
             END             
           end
           ELSE IF @LoopCounter = 4
           BEGIN
             SET @productnumber=@productnumber4
             SET @arrdate= @Arrivaldate +@day4 - 1
             SET @depdate= @Departuredate
             SET @DoContinue=0
           END
		 
		 SELECT @NameListSetting = NameListSetting FROM Products (NOLOCK) WHERE ProductNumber = @ProductNumber
		 SET @NamelistSetting = ISNULL(@NameListSetting, 0)

		 IF @NameListSetting = 0
		 BEGIN
			SELECT @NameListSetting=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NameListReservationPerPerson'
			SET @NamelistSetting = ISNULL(@NameListSetting, 0)
		 END

         SET @Adults = ISNULL(@Adults,0)
         SET @Children1 = ISNULL(@Children1,0)
         SET @Children2 = ISNULL(@Children2,0)
         SET @ProductNumber = ISNULL(@productNumber,0) 
  
         SET @PostDateTime = @ArrDate
         WHILE @PostDateTime < @DepDate
         BEGIN
           EXEC dbo.ProductPricesDay @ProductNumber, @ArrDate,  @DepDate,@PostDateTime , @RoomTypeNumber, @NumberOfRooms,  @Adults, @Children1, @Children2, @Addon OUTPUT, @BillTextProduct OUTPUT, @BillTextAddOn OUTPUT, @CalcPriceDay OUTPUT, @PacketNumber OUTPUT, @EnvironmentFee OUTPUT, @FixedPrice OUTPUT, @VATProduct OUTPUT,  @SpecifiedDepartureAddOn OUTPUT, @NamelistSetting, @OnePersonPaysForAll, @EnvironmentFeeType, @AGetPackage = 0
           SET @EnvironmentFee = Round(@EnvironmentFee,0)
           SET @Result = @Result + ISNULL(@EnvironmentFee, 0)
           SET @PostDateTime = @PostDateTime + 1
         END

        END --loop counter
        FETCH NEXT FROM RoomLineCur1 INTO @GuestNumber, @ProductNumber, @Productnumber2, @Productnumber3, @Productnumber4, @Day2, @Day3, @Day4,@ArrivalDate,@DepartureDate, @Adults, @Children1, @Children2
     END --cursor
     CLOSE RoomLineCur1
     DEALLOCATE   RoomLineCur1
   END
   ELSE
   BEGIN 
       SELECT DISTINCT @ProductNumber=ProductNumber,@ProductNumber2=ProductNumber2,@ProductNumber3=ProductNumber3,@ProductNumber4=ProductNumber4,@day2=day2,@day3=day3,@day4=day4,@arrivaldate=CONVERT(DATETIME,arrivaldate,112),@departuredate=CONVERT(DATETIME,departuredate,112),@Adults=Adults,@Children1=Children1,@Children2=Children2 from reservationroomline WITH (nolock) where lineNumber=@Linenumber
      
       SET @LoopCounter=0
       SET @DoContinue=1

       WHILE  @LoopCounter <=4 and @DoContinue=1
       BEGIN
         SET @Loopcounter = @LoopCounter+1
         IF @LoopCounter = 1
         BEGIN
           IF ISNULL(@Day2, 0) = 0
           BEGIN
             SET @arrdate= @Arrivaldate
             SET @depdate= @departuredate
             SET @DoContinue=0
           end
           ELSE
           BEGIN
             SET @arrdate= @Arrivaldate
             SET @depdate= @Arrivaldate + @day2 - 1
           END            
         end
         ELSE IF @LoopCounter = 2
         BEGIN
           SET @productnumber=@productnumber2
           IF ISNULL(@Day3, 0) = 0
           BEGIN
             SET @arrdate= @Arrivaldate +@day2 -1
             SET @depdate= @departuredate
             SET @DoContinue=0
           end
           ELSE
           BEGIN
             SET @Arrdate= @Arrivaldate + @day2 -1
             SET @Depdate= @Arrivaldate + @day3 -1
           END             
         end
         ELSE IF @LoopCounter = 3
         BEGIN
           SET @productnumber=@productnumber3
           IF ISNULL(@Day4, 0) = 0
           BEGIN
             SET @arrdate= @Arrivaldate +@day3 -1
             SET @depdate = @departuredate
             SET @DoContinue=0
           END
           ELSE
           BEGIN
             SET @arrdate= @Arrivaldate +@day3 -1
             SET @depdate= @Arrivaldate +@day4 -1
           END             
         end
         ELSE IF @LoopCounter = 4
         BEGIN
           SET @productnumber=@productnumber4
           SET @arrdate= @Arrivaldate +@day4 -1
           SET @depdate= @Departuredate
           SET @DoContinue=0
         end
         
         SET @GuestNumber = 0

         SET @Adults = ISNULL(@Adults, 0)
         SET @Children1 = ISNULL(@Children1, 0)
         SET @Children2 = ISNULL(@Children2, 0)
         SET @ProductNumber = ISNULL(@productNumber, 0) 

		 SELECT @NameListSetting = NameListSetting FROM Products (NOLOCK) WHERE ProductNumber = @ProductNumber
		 SET @NamelistSetting = ISNULL(@NameListSetting, 0)

		 IF @NameListSetting = 0
		 BEGIN
			SELECT @NameListSetting=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NameListReservationPerPerson'
			SET @NamelistSetting = ISNULL(@NameListSetting, 0)
		 END

         SET @PostDateTime = @ArrDate
         WHILE @PostDateTime < @DepDate
         BEGIN
/*
           print 'product: '+ convert(varchar(5),@productnumber)
           print '@arrdate'+convert(varchar(8),@Arrdate,112)
           print '@DepDate'+convert(varchar(8),@DepDate,112)
           print '@PostDateTime'+convert(varchar(8),@PostDateTime,112)
           print '@RoomTypeNumber: '+ convert(varchar(5),@RoomTypeNumber)
           print '@NumberOfRooms: '+ convert(varchar(5),@NumberOfRooms)
           print '@Adults: '+ convert(varchar(5),@Adults)
           print '@Children1: '+ convert(varchar(5),@Children1)
           print '@Children2: '+ convert(varchar(5),@Children2)
           print 'evfee:' +convert(varchar(20),@postdatetime,112) +' ' +convert(varchar(20),isnull(@EnvironmentFee,0))
*/
           EXEC dbo.ProductPricesDay @ProductNumber, @ArrDate,  @DepDate,@PostDateTime, @RoomTypeNumber, @NumberOfRooms,  @Adults, @Children1, @Children2, @Addon OUTPUT, @BillTextProduct OUTPUT, @BillTextAddOn OUTPUT, @CalcPriceDay OUTPUT, @PacketNumber OUTPUT, @EnvironmentFee OUTPUT, @FixedPrice OUTPUT, @VATProduct OUTPUT,  @SpecifiedDepartureAddOn OUTPUT, @NamelistSetting, @OnePersonPaysForAll, @EnvironmentFeeType, @AGetPackage = 0
           SET @EnvironmentFee = Round(@EnvironmentFee,0)
           SET @Result = @Result + ISNULL(@EnvironmentFee, 0)
           SET @PostDateTime = @PostDateTime + 1
         END
       END
   END

  FETCH NEXT FROM RoomLineCur INTO @linenumber
END --første curoser
CLOSE RoomLineCur
DEALLOCATE   RoomLineCur

--HALLS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  DECLARE ConflineCur Cursor Forward_Only For
  SELECT distinct ReservationConfLinenumber from ReservationConferenceline  WITH (NOLOCK) where referencenumber= @AReferenceNumber and ISNULL(TableBooking, 0) = 0
  Open ConflineCur
  FETCH Next From ConflineCur Into @linenumber
  WHILE   (@@FETCH_STATUS <> -1)
  BEGIN
    SELECT DISTINCT @RoomTypeNumber = BookedConferenceRoomTypeNumber, @ProductNumber=ProductNumber, @arrivaldate=CONVERT(DATETIME, arrivaldate,112) , @arrtime=arrivaltime, @DepartureDate =  CONVERT(DATETIME, @departuredate,112),@deptime=departuretime,@Adults=Adults,@Children1=Children1,@Children2=Children2, @NumberOfRooms=NumberOfRooms from reservationConferenceline WITH (nolock) where ReservationConflineNumber=@Linenumber

    SET @NumberOfRooms = ISNULL(@NumberOFRooms, 0)
    SET @Adults = ISNULL(@Adults, 0)
    SET @Children1 = ISNULL(@Children1, 0)
    SET @Children2 = ISNULL(@Children2, 0)
    SET @ProductNumber = ISNULL(@ProductNumber, 0) 
  
    IF @Arrtime <  @Arr24HoursStarts
      SET @arrdate= @Arrivaldate - 1
    ELSE
      SET @arrdate= @Arrivaldate

    IF @Deptime <  @Arr24HoursStarts
      SET @depdate =  @DepartureDate - 1
    ELSE
      SET @depdate =  @DepartureDate

    SELECT @NameListSetting = NameListSetting FROM Products (NOLOCK) WHERE ProductNumber = @ProductNumber
    SET @NamelistSetting = ISNULL(@NameListSetting, 0)

    IF @NameListSetting = 0
    BEGIN
	  SELECT @NameListSetting=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NameListReservationPerPerson'
	  SET @NamelistSetting = ISNULL(@NameListSetting, 0)
    END	

    SET @PostDateTime = @ArrDate
    WHILE @PostDateTime < @DepDate
    BEGIN
      EXEC dbo.ProductPricesDay @ProductNumber, @ArrDate,  @DepDate,@PostDateTime , @RoomTypeNumber, @NumberOfRooms,  @Adults, @Children1, @Children2, @Addon OUTPUT, @BillTextProduct OUTPUT, @BillTextAddOn OUTPUT, @CalcPriceDay OUTPUT, @PacketNumber OUTPUT, @EnvironmentFee OUTPUT, @FixedPrice OUTPUT, @VATProduct OUTPUT,  @SpecifiedDepartureAddOn OUTPUT, @NamelistSetting, @OnePersonPaysForAll, @EnvironmentFeeType, @AGetPackage = 0
      SET @EnvironmentFee = Round(@EnvironmentFee,0)
      SET @Result = @Result + ISNULL(@EnvironmentFee, 0)
      SET @PostDateTime = @PostDateTime + 1
    END

    FETCH NEXT FROM ConflineCur INTO @linenumber
  END
  CLOSE ConfLineCur
  DEALLOCATE  ConfLineCur

--Write the @Result amount to arrangementline on reservation

 SELECT @WareNumber = WareNumber, @WareText = WareName FROM Ware WITH (NOLOCK) INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber  
  WHERE WareGroups.Payment = 4 and Ware.SortNumber = 4 

  IF ISNULL(@WareNumber, 0) > 0
  BEGIN
    DECLARE DeleteCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT  ArrangementLinenumber FROM TArrangementLinesView WITH (NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND WareNumber = @WareNumber
    OPEN DeleteCur
    FETCH NEXT FROM DeleteCur INTO @ArrangementLineNumber
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF ISNULL(@ArrangementLineNumber, 0) > 0
      BEGIN
        DELETE FROM ArrangementReservationsXTab WHERE ArrangementLineNumber = @ArrangementLineNumber
        DELETE FROM ArrangementLineDepartmentXTab WHERE FK_ArrangementLineNumber = @ArrangementLineNumber
        DELETE FROM ArrangementLines WHERE ArrangementLineNumber = @ArrangementLineNumber
      END
      FETCH NEXT FROM DeleteCur INTO @ArrangementLineNumber
    END
    CLOSE DeleteCur
    DEALLOCATE DeleteCur

    SELECT @ArrangementDate = ArrivalDate FROM Reservations WITH (NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND GuestNumber = 0
    
    IF @ArrangementDate IS NOT NULL
    BEGIN 
      SELECT TOP 1 @ExDepartment =  Departments.PK_Department FROM Departments WITH (NOLOCK) WHERE ISNULL(AutoCharge,0) = 0 AND MarkAsDeleted = 0-- it must not be autocharge else it will be posted in nighaudit.
    
      INSERT INTO ArrangementLines (Date, Time, Price, Pieces, WareNumber, WareText, FK_Exdepartment) VALUES (@ArrangementDate, '12:00', @Result, 1, @WareNumber, @WareText, @Exdepartment)
      SELECT @IdentityValue = @@identity  FROM ArrangementLines WITH (NOLOCK)
      INSERT INTO ArrangementReservationsXTab (ArrangementLineNumber, ReferenceNumber, GuestNumber, PlusProductLineNumber) VALUES(@IdentityValue, @AReferenceNumber, 0, 0)

      DECLARE DepCur Cursor Forward_Only For
      SELECT DISTINCT FK_Department FROM WareDepartmentsXtab WITH (NOLOCK) WHERE WareNumber=@WareNumber AND Type = 2
      OPEN DepCur
      FETCH NEXT FROM DepCur INTO @PK_Department
      WHILE  (@@FETCH_STATUS <> -1)
      BEGIN
        INSERT INTO ArrangementLineDepartmentXTab (FK_ArrangementLineNumber, FK_DepartmentNumber) VALUES (@IdentityValue, @PK_Department)
        FETCH NEXT FROM DepCur INTO @PK_Department
      END
      CLOSE DepCur
      DEALLOCATE DepCur
    END
  END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

