CREATE PROCEDURE [dbo].[CalcProvision] @Room_HallType TINYINT, @ReferenceNumber INT, @GuestNumber INT, @CalcPriceDay MONEY, @Dayproduct BIT, @SplitBillNumber INT, 
  @AProvisionAmount MONEY OUTPUT, @AProvisionVATAmount MONEY OUTPUT AS
SET NOCOUNT ON
/* 
  Created by Kent Johansen, January 2021
  Calculates provision amount. This is a "simple" proivision calculation only used for owner report for future reservations
  Addon, environment fee is not calculated

  Changes:

*/

SET @AProvisionAmount = 0
SET @AProvisionVATAmount = 0

DECLARE @ProvisionDoNotCharge BIT
DECLARE @ProvisionWareNumber INT
DECLARE @GuestType INT
DECLARE @AddressType SMALLINT
DECLARE @ProvisionRate FLOAT
DECLARE @ProvisionType TINYINT
DECLARE @ProvPostOnGuest INT
DECLARE @HotelCountryNumber INT
DECLARE @ProvisionWareText VARCHAR(50)
DECLARE @PostVAT BIT
DECLARE @ProvisionVATPercent DECIMAL
DECLARE @AccountProvisionAddressType TINYINT
DECLARE @AccountProvisionRate FLOAT
DECLARE @CountryNumber INT
DECLARE @AccountProvisionWareNumber INT
DECLARE @AccountProvisionWareText VARCHAR(50)
DECLARE @AccountProvisionGuestNumber SMALLINT
DECLARE @AccountProvisionPostVAT BIT
DECLARE @NumberOfDecimalsInDeposit INT
DECLARE @PostIsMade BIT
DECLARE @NumOfDecimals INT
DECLARE @DummyINT INT
DECLARE @ProvisionWareGroupNumber INT
DECLARE @ResultAccountNumber INT
DECLARE @WareTypeWare SMALLINT

SELECT @HotelCountryNumber = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'CountryCode'
IF @@ROWCOUNT = 0
  SELECT TOP 1 @HotelCountryNumber = CountryNumber FROM Countries WITH (NOLOCK)

SELECT @ProvisionDoNotCharge = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ProvisionDoNotCharge'
SET @ProvisionDoNotCharge = ISNULL(@ProvisionDoNotCharge, 0)

SELECT @NumberOfDecimalsInDeposit = ISNULL(KeyValue, 2) FROM Hotelsetup WITH (NOLOCK) WHERE Searchkey = 'NumberOfDecimalsInDeposit'
SET @NumberOfDecimalsInDeposit = ISNULL(@NumberOfDecimalsInDeposit, 2)

SET @PostIsMade = 0

IF @ProvisionDoNotCharge = 0
BEGIN
  SELECT @ProvisionWareNumber = Ware.WareNumber 
  FROM Ware WITH (NOLOCK) 
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
  AND WareGroups.Payment = 5
  AND ISNULL(WareGroups.MarkAsDeleted, '0') = '0'
  WHERE Ware.MarkAsDeleted IS NULL 
  AND Ware.Type = 10 -- provision bill on guest. Type ligegyldig da det alene handler om at finde en folio (FindFolioNumber). Det korrekte warenumber findes senere.

  -- Get Provision AddressType
  SELECT @GuestType = GuestType 
  FROM SplitBillWareGroupXTab WITH (NOLOCK) 
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = SplitBillWaregroupXTab.WaregroupNumber
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
    AND Ware.WareNumber = @ProvisionWareNumber
  WHERE SplitBillNumber = @SplitbillNumber 

  SET @GuestType = ISNULL(@GuestType, 1)

  IF @GuestType in (2,4,6,8)
    SET @AddressType = (@GuestType/2)
  ELSE
  BEGIN
    IF @GuestType=1
      SET @AddressType = 1
    ELSE IF @GuestType = 3
      SET @AddressType = 2
    ELSE IF @GuestType = 5
      SET @AddressType = 3
    ELSE IF @GuestType = 7
      SET @AddressType = 4
  END

  SELECT @ProvisionType = Reservationaddress.ProvisionType, @ProvPostOnGuest = ReservationAddress.ProvisionPostOnGuest
  FROM ReservationAddress WITH (NOLOCK)
  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
    AND ReservationAddressXTab.ReferenceNumber = @ReferenceNumber 
    AND ReservationAddressXTab.GuestNumber IN (@GuestNumber, 0)
  WHERE ReservationAddress.ProvisionType IN (2, 3) 
  AND ReservationAddress.AddressType = @AddressType
              
  SET @ProvPostOnGuest = ISNULL(@ProvPostOnGuest, 0)
  SET @ProvisionWareNumber = NULL
 
	EXEC GetProvisionInfo @ReferenceNumber, @GuestNumber, @DayProduct, @HotelCountryNumber, @Room_HallType, @SplitBillNumber, 
    @ProvisionWareNumber OUTPUT, @ProvisionWareText OUTPUT, @WareTypeWare OUTPUT, @ProvisionRate OUTPUT, @ProvisionType OUTPUT, 
    @PostVAT OUTPUT, @ProvisionVATPercent OUTPUT, @DummyINT OUTPUT, @ResultAccountNumber OUTPUT, @ProvisionWareGroupNumber OUTPUT
	
  SET @ProvisionWareNumber = ISNULL(@ProvisionWareNumber, 0) 
  IF @ProvisionWareNumber > 0
	BEGIN
    DECLARE AccountProvisionCur CURSOR SCROLL READ_ONLY FOR
		  SELECT ReservationAddress.AddressType, ReservationAddress.ProvisionRate, ReservationAddress.CountryNumber, ReservationAddressXTab.GuestNumber, ReservationAddress.ProvisionPostOnGuest, ReservationAddress.ProvisionType
      FROM ReservationAddress WITH (NOLOCK)
      INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON	ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
      WHERE ReservationAddressXTab.ReferenceNumber = @ReferenceNumber
      AND (ReservationAddressXTab.GuestNumber = @GuestNumber OR ReservationAddressXTab.GuestNumber = 0)
      AND ReservationAddress.ProvisionType IN (2, 3)
      AND ReservationAddress.AddressType > 0
		OPEN AccountProvisionCur

		FETCH NEXT FROM AccountProvisionCur INTO @AccountProvisionAddressType, @AccountProvisionRate, @CountryNumber, @AccountProvisionGuestNumber, @ProvPostOnGuest, @ProvisionType

    IF @ProvPostOnGuest = 1
    BEGIN
	    DECLARE @TempVAT Decimal
	
	    SELECT @NumOfDecimals = ISNULL(NumberOfDecimals, @NumberOfDecimalsInDeposit) FROM Ware WITH (NOLOCK) WHERE WareNumber = @ProvisionWareNumber 

	    SET @TempVAT = @CalcPriceDay * @ProvisionVATPercent / 100
		  SET @AProvisionAmount = ROUND((@CalcPriceDay)*(@ProvisionRate/100), @NumOfDecimals)
		  SET @AProvisionVATAmount = ROUND(@TempVAT * (@ProvisionRate/100), @NumOfDecimals)
	    SET @AProvisionAmount = ISNULL(@AProvisionAmount, 0)
	    IF @AProvisionAmount <> 0 AND @PostIsMade = 0
	      SET @PostIsMade = 1
    END

    WHILE @@FETCH_STATUS = 0
    BEGIN
      IF @PostIsMade = 0
      BEGIN
        EXEC GetProvisionInfo @ReferenceNumber, @GuestNumber, @DayProduct, @HotelCountryNumber, @Room_HallType, @SplitBillNumber, 
          @AccountProvisionWareNumber OUTPUT, @AccountProvisionWareText OUTPUT, @WareTypeWare OUTPUT, @ProvisionRate OUTPUT, @ProvisionType OUTPUT, 
          @AccountProvisionPostVAT OUTPUT, @ProvisionVATPercent OUTPUT, @DummyINT OUTPUT, @ResultAccountNumber OUTPUT, @ProvisionWareGroupNumber OUTPUT
		
		    IF ISNULL(@AccountProvisionWareNumber, 0) > 0
		    BEGIN
		      SELECT @NumOfDecimals = ISNULL(NumberOfDecimals, @NumberOfDecimalsInDeposit) FROM Ware WITH (NOLOCK) WHERE WareNumber = @AccountProvisionWareNumber

          SET @TempVAT = @CalcPriceDay * @ProvisionVATPercent / 100
          SET @AProvisionAmount = ROUND(@CalcPriceDay * (@AccountProvisionRate/100), @NumOfDecimals)
          SET @AProvisionVATAmount = ROUND(@TempVAT * (@AccountProvisionRate/100), @NumOfDecimals)
          SET @AProvisionAmount = ISNULL(@AProvisionAmount, 0)
          IF @AProvisionAmount <> 0 AND @PostIsMade = 0
  			    SET @PostIsMade = 1
		    END
      END

      FETCH NEXT FROM AccountProvisionCur Into @AccountProvisionAddressType, @AccountProvisionRate, @CountryNumber, @AccountProvisionGuestNumber, @ProvPostOnGuest, @ProvisionType
    END
	  CLOSE AccountProvisionCur
	  DEALLOCATE AccountProvisionCur
  END
END