CREATE PROCEDURE [dbo].[MISCalcProvision] @SystemDateStr CHAR(8), @CurrentDateStr CHAR(8), @CurrentDateStrDeparturePackage CHAR(8), @Room_HallType TINYINT, @ReferenceNumber INT, @GuestNumber SMALLINT, @CalcPriceDay MONEY, @Addon MONEY,
  @Dayproduct BIT, @MISUpdateType TINYINT, @SplitBillNumber INT, @BookedTypeNumber INT, @PackageSameDayAsLogi BIT, @SegmentNumber INT, @PointOfSaleNumber INT, @AGuestTypeNumber INT, @APurposeCodeNumber INT, @AllocatedTypeNumber INT, @HotelCustomerNumber INT, @Status SMALLINT, @CampaignCode SMALLINT, 
  @ProjectCode SMALLINT, @UserIDNumber INT, @DepartmentNumber INT, @ProductWareLinkNumber BIGINT, @IsPackage BIT, @Text VARCHAR(256), @OwnerReservation BIT, @ProductNumber INT, @ACountryNumber INT, @AArrivalTime DATETIME, @ADepartureTime DATETIME AS
SET NOCOUNT ON

/* Created 2020-04-23, SG
   Calculates provision to be used in MIS.
   Based on the provision section of SP PostDays
   EnviromentFee is not calculated.

   Changes:
   KJ 20241009: Ext: Added From/To Time
   KJ 20240521: Bug: "Fix" for "Arithmetic overflow error converting expression to data type int
   KJ 20240521: Bug: "Fix" for "Arithmetic overflow error converting expression to data type int
   KJ 20231218: Ext: Added Guesttype, purposeCode and country
   SG 2021-03-01: Added WareCount (=1) to UpdateMISTable
   SG 2020-05-28: Added WareNumber to UpdateMISTable
   SG 2020-05-20: Added @SplitBillNumber to call to GetProvisionInfo
   SG 2020-05-15 Added WareType to UpdateMISTable
   SG 2020-05-12 Provision: Always store 1 for isPackage in order to get the views right.
   SG 2020-04-24 Added @WareTypeWare
   Calls 
   UpdateMISTable
   GetProvisionInfo
*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

IF @ShowPrint = 1
BEGIN
  PRINT 'MISCalcProvision, @ReferenceNumber=' + convert(varchar, @ReferenceNumber) + ', @GuestNumber=' + convert(varchar, @GuestNumber) + ', @CurrentDateStr=' + @CurrentDateStr
  PRINT '@CalcPriceDay=' + convert(varchar, @CalcPriceDay)
END

DECLARE @ProvisionDoNotCharge BIT
Declare @ProvisionWareNumber int

Declare @FirstPost Bit
Declare @DiscountRate float
DECLARE @GuestType INT
Declare @AddressType SmallInt
Declare @ProvisionRate float
Declare @ProvisionType tinyint
Declare @ProvisionValue Decimal
Declare @ProvisionValueVAT Decimal
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 INTEGER
DECLARE @PostIsMade BIT
DECLARE @NumOfDecimals INT
Declare @NoProvisionOnAddon bit
Declare @ProvisionOnEnviromentFee bit
Declare @EnviromentFee Money -- = 0
Declare @DepartureAddon Money--Initialisering mangler, fra linje 1772 i PostDays
Declare @DepartureAddonVAT Money

Declare @MISValueTypeProvision 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 @NoProvisionOnAddon = CONVERT(Bit,KeyValue) from HotelSetUp WITH (NOLOCK)  Where  SearchKey='NoProvisionOnAddon'
if isnull(@NoProvisionOnAddon,0)=0 SET @NoProvisionOnAddon=0

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

Select @ProvisionOnEnviromentFee = CONVERT(Bit,ISNULL(KeyValue,0)) from HotelSetUp WITH (NOLOCK)  Where  SearchKey='ProvisionOnEnviromentFee'

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

SET @FirstPost = 1
SET @PostIsMade = 0
SET @EnviromentFee = 0  --MIS don't calculate Environment fee
SET @DepartureAddon = 0    --Midlertidigt, find ud af, hvad den bruges til!
SET @DepartureAddonVAT = 0 --Midlertidigt, find ud af, hvad den bruges til!

-- Start Provision post. Copied from SP PostDays
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) 
  WHERE SplitBillNumber = @SplitbillNumber 
  AND WareGroupNumber IN (SELECT WareGroupNumber FROM Ware WITH (NOLOCK) WHERE WareNumber = @ProvisionWareNumber)

  IF ISNULL(@GuestType,1) = 1 
    SET @GuestType = 1 -- Safe if not marked - guest pay

  if (@GuestType in (2,4,6,8))
  begin
    SET @AddressType = (@GuestType/2)
  end
  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, --ware.Type: See below
    @ProvisionRate OUTPUT, @ProvisionType OUTPUT,
    @PostVAT OUTPUT, @ProvisionVATPercent OUTPUT, @MISValueTypeProvision OUTPUT, @ResultAccountNumber OUTPUT, @ProvisionWareGroupNumber OUTPUT

  IF ISNULL(@ProvisionWareNumber, 0) > 0
  begin          
    IF @ShowPrint=1
    begin
      PRINT '@ProvisionWareNumber'
      PRINT @ProvisionWareNumber
      PRINT '@ProvisionWareText'
      PRINT @ProvisionWareText
      PRINT '@ProvisionRate'
      PRINT @ProvisionRate
      PRINT '@PostVAT'
      PRINT @PostVAT
      PRINT '@ProvisionVATPercent'
      PRINT @ProvisionVATPercent
      PRINT '@WareTypeWare'
      PRINT @WareTypeWare
    end

    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
  END
  ELSE
  BEGIN
    SET @ProvisionWareNumber = 0
    IF @ShowPrint=1
      Print 'ProvisionWareNumber is NULL'
  End

  if @ProvisionWareNumber > 0 AND @ProvPostOnGuest = 1
  begin
    Declare @TempVAT Decimal
	
    SELECT @NumOfDecimals = ISNULL(NumberOfDecimals, @NumberOfDecimalsInDeposit) FROM Ware WITH (NOLOCK) WHERE WareNumber = @ProvisionWareNumber 

    IF @NoProvisionOnAddon = 0
    begin
      SET @TempVAT = (@CalcPriceDay + @Addon + @DepartureAddon) * @ProvisionVATPercent / 100
      if @ProvisionOnEnviromentFee = 1
      Begin
        SET @ProvisionValue = Round((@EnviromentFee + @CalcPriceDay + @Addon + @DepartureAddon)*(@ProvisionRate/100), @NumOfDecimals) * -1
        SET @ProvisionValueVAT = Round((@EnviromentFee + @TempVAT)*(@ProvisionRate/100), @NumOfDecimals) * -1
      End 
      else
      Begin
        SET @ProvisionValue = Round((@CalcPriceDay + @Addon + @DepartureAddon)*(@ProvisionRate/100), @NumOfDecimals) * -1
        SET @ProvisionValueVAT = Round(@TempVAT * (@ProvisionRate / 100), @NumOfDecimals) * -1
      End
    end 
    ELSE
    begin
       SET @TempVAT = @CalcPriceDay * @ProvisionVATPercent / 100
       if @ProvisionOnEnviromentFee = 1
       Begin
         SET @ProvisionValue = Round((@EnviromentFee + @CalcPriceDay)*(@ProvisionRate/100), @NumOfDecimals) * -1
         SET @ProvisionValueVAT = Round((@EnviromentFee + @TempVAT)*(@ProvisionRate/100), @NumOfDecimals) * -1
        End 
        else
        Begin
          SET @ProvisionValue = Round((@CalcPriceDay)*(@ProvisionRate/100), @NumOfDecimals) * -1
          SET @ProvisionValueVAT = Round((@TempVAT)*(@ProvisionRate/100), @NumOfDecimals) * -1
        End
      end

      if (@ShowPrint=1)
      begin
        print ' @ProvisionValue/@ProvisionValueVAT/@ProvisionRate'
        print  @ProvisionValue
        print  @ProvisionValueVAT
        print  @ProvisionRate
      end

      IF ISNULL(@ProvisionValue,0) = 0
        SET @ProvisionValue = 0
	
      if (@ProvisionValue <> 0) AND @PostIsMade = 0
      begin
        Declare @Value money, @ValueVat money, @Package money, @PackageVat Money
      
        If @IsPackage = 1
        Begin
          Set @Value = 0
          Set @ValueVat = 0
          Set @Package = @ProvisionValue
          Set @PackageVat = @ProvisionValueVAT
        End 
        Else
        Begin
          Set @Value = @ProvisionValue
          Set @ValueVat = @ProvisionValueVAT
          Set @Package = 0
          Set @PackageVat = 0
        End

        EXEC UpdateMISTable @SystemDateStr, @Room_HallType, @ReferenceNumber, @GuestNumber, @MISUpdateType, @PackageSameDayAsLogi, 0, 0, 0, 0, @Value, @ValueVat, @PackageVat, @Package, @CurrentDateStr, @CurrentDateStrDeparturePackage, @SegmentNumber, 
          @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @AllocatedTypeNumber, @MISValueTypeProvision, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @UserIDNumber, @ProvisionWareGroupNumber, @ResultAccountNumber, @DepartmentNumber, @ProductWareLinkNumber, 
          1, @ProvisionWareText, @OwnerReservation, @ProductNumber, @WareTypeWare, @ProvisionWareNumber, 1, @ACountryNumber, @AArrivalTime, @ADepartureTime

        SET @PostIsMade = 1
      end
    end

    if @ProvisionWareNumber > 0
    Begin
      if @FirstPost=0
        Fetch FIRST From AccountProvisionCur Into @AccountProvisionAddressType,@AccountProvisionRate,@CountryNumber, @AccountProvisionGuestNumber, @ProvPostOnGuest, @ProvisionType

      While (@@Fetch_Status=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, @MISValueTypeProvision OUTPUT, @ResultAccountNumber OUTPUT, @ProvisionWareGroupNumber OUTPUT
		
      if ISNULL(@AccountProvisionWareNumber, 0) > 0
      begin
        SELECT @NumOfDecimals = ISNULL(NumberOfDecimals, @NumberOfDecimalsInDeposit) FROM Ware WITH (NOLOCK) WHERE WareNumber = @AccountProvisionWareNumber 
        IF @NoProvisionOnAddon = 0
        begin
          SET @TempVAT = (@CalcPriceDay + @Addon + @DepartureAddon) * @ProvisionVATPercent / 100
          if @ProvisionOnEnviromentFee = 1
          Begin
            SET @ProvisionValue = Round((@EnviromentFee + @CalcPriceDay + @Addon + @DepartureAddon)*(@AccountProvisionRate/100), @NumOfDecimals) * -1
            SET @ProvisionValueVAT = Round((@EnviromentFee + @TempVAT)*(@AccountProvisionRate/100), @NumOfDecimals) * -1
          End 
          else
          Begin
            SET @ProvisionValue = Round((@CalcPriceDay + @Addon + @DepartureAddon)*(@AccountProvisionRate/100), @NumOfDecimals) * -1
            SET @ProvisionValueVAT = Round(@TempVAT * (@AccountProvisionRate/100), @NumOfDecimals) * -1
          End
        end 
        ELSE
        begin
          SET @TempVAT = @CalcPriceDay * @ProvisionVATPercent / 100
          if @ProvisionOnEnviromentFee = 1
          Begin
            SET @ProvisionValue = Round((@EnviromentFee + @CalcPriceDay)*(@AccountProvisionRate/100), @NumOfDecimals) * -1
            SET @ProvisionValueVAT = Round((@EnviromentFee + @TempVAT)*(@AccountProvisionRate/100), @NumOfDecimals) * -1
          End
          else
          Begin
            SET @ProvisionValue = Round((@CalcPriceDay)*(@AccountProvisionRate/100), @NumOfDecimals) * -1
            SET @ProvisionValueVAT = Round(@TempVAT * (@AccountProvisionRate/100), @NumOfDecimals) * -1
          End
        end

        if (@ShowPrint=1)
        begin
          print '@CalcPriceDay/@Addon/ @ProvisionValue/@ProvisionValueVAT/@AccountProvisionRate/@NumOfDecimals'
          print @CalcPriceDay
          print @Addon
          print @ProvisionValue
          print @ProvisionValueVAT
          print @AccountProvisionRate
          print @NumOfDecimals
        end
  
        SET @AccountProvisionWareText = RTRIM(LTRIM(Str(@AccountProvisionRate, 5, 2) + ' % ' + @AccountProvisionWareText))
  
        IF ISNULL(@ProvisionValue,0) = 0
          SET @ProvisionValue = 0
        if (@ProvisionValue <> 0) AND @PostIsMade = 0
        begin
          if (@ShowPrint=1)
          begin
            PRINT '@ProvPostOnGuest'
            PRINT @ProvPostOnGuest
          END  
          If @IsPackage = 1
          Begin
            Set @Value = 0
            Set @ValueVat = 0
            Set @Package = @ProvisionValue
            Set @PackageVat = @ProvisionValueVAT
          End
          Else
          Begin
            Set @Value = @ProvisionValue
            Set @ValueVat = @ProvisionValueVAT
            Set @Package = 0
            Set @PackageVat = 0
          End

          EXEC UpdateMISTable @SystemDateStr, @Room_HallType, @ReferenceNumber, @GuestNumber, @MISUpdateType, @PackageSameDayAsLogi, 0, 0, 0, 0, @Value, @ValueVat, @PackageVat, @Package, @CurrentDateStr, @CurrentDateStrDeparturePackage, @SegmentNumber, 
            @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @AllocatedTypeNumber, @MISValueTypeProvision, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @UserIDNumber, @ProvisionWareGroupNumber, @ResultAccountNumber, @DepartmentNumber, @ProductWareLinkNumber, 
            1, @ProvisionWareText, @OwnerReservation, @ProductNumber, @WareTypeWare, @ProvisionWareNumber, 1, @ACountryNumber, @AArrivalTime, @ADepartureTime

          SET @PostIsMade = 1
        end
      end

      Fetch NEXT From AccountProvisionCur Into @AccountProvisionAddressType,@AccountProvisionRate, @CountryNumber, @AccountProvisionGuestNumber, @ProvPostOnGuest, @ProvisionType
    end
    CLOSE AccountProvisionCur
    DEALLOCATE AccountProvisionCur
  End
END