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,  --Hall: 1, room: 0
									   @MISUpdateType TINYINT,
									   @SplitBillNumber int,
									   @BookedTypeNumber Int,  --Room or Hall BookedTypeNumber
									   @PackageSameDayAsLogi BIT,
									   @SegmentNumber INT, 
	                                   @PointOfSaleNumber INT,
									   @AllocatedTypeNumber INT, 
	                                   @HotelCustomerNumber INT, 
	                                   @Status SmallInt, 
	                                   @CampaignCode SmallInt, 
	                                   @ProjectCode SmallInt, 
	                                   @UserIDNumber int, 
	                                   @DepartmentNumber int, 
	                                   @ProductWareLinkNumber int, 
	                                   @IsPackage bit, 
	                                   @Text varchar(256),
	                                   @OwnerReservation bit,
	                                   @ProductNumber int

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:
   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 @GuestNumber=0
        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
	
	/* Ware.Type: 
	(wtWare, wtPower, wtRessource, wtPerson, wtTransfer, wtOwner, wtWater,
    wtOwnerElectricity, wtOwnerCleaning, wtOwnerAdditional,
    wtProvisionOnBillDK, wtProvisionOnBillEU, wtProvisionOnBillOther,
    wtProvisionOnAccountDK, wtProvisionOnAccountEU, wtProvisionOnAccountOther,
    wtProvisionOnRestaurentDK, wtProvisionOnRestaurentEU, wtProvisionOnRestaurentOther, Provision: 10..18
	wtDiscount, wtTicket,
    wtReservationFee, wtNoShowFee, wtInvoiceFee, wtEnvironmentFee, wtAddonFee, wtCreditCardFee, wtServiceCharge
    , wtCityTax
    , wtDiscountBtn //29
    , wtOwnerInclVAT, wtOwnerTPBFExcelVAT, wtOwnerTPBFInclVAT
  ); */
	
	
    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)
	  begin
	    Print 'ProvisionWareNumber is NULL'
	  End
    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
	  --exec Postering @TransactionDatetime, 1, @PostVAT, 0, @GuestNumber, 0, @UserIDNumber, @ProvisionFolioNumber, 0, 0, 0, 0, @TempStr, 1, @ProvisionValue, @HotelCountryNumber, 0, 0 , 0, 0, @FK_LogiTurnOverDepartment, @ProvisionWareNumber, 0, 0, 0, 0, @VisualRoomNumber, @DummyTransNrShowOnBillOnAdd OUTPUT, @HotelCustomerNumber
	  --UPDATE MISTABLE instead
      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,  
			-- -1,    --FolioNumber 
			@MISUpdateType, 
			@PackageSameDayAsLogi, 
			0, --@BookedNumberOfRooms
			0, --@TotalAdults
			0, --@TotalChildren1
			0, --@TotalChildren2   
			@Value,      --TotalPriceDay
			@ValueVat,   --VatOfPrice 
			@PackageVat, --@VatOfPacket  
			@Package,    --@PackagePrice
			@CurrentDateStr, 
			@CurrentDateStrDeparturePackage, 
			@SegmentNumber, 
			@PointOfSaleNumber, 
			@AllocatedTypeNumber, 
			@MISValueTypeProvision, 
			@HotelCustomerNumber, 
			@Status, 
			@CampaignCode, 
			@ProjectCode, 
			@UserIDNumber, 
			@ProvisionWareGroupNumber, 
			@ResultAccountNumber,  
			@DepartmentNumber, 
			@ProductWareLinkNumber, 
			1,   --@IsPackage "isPackageOrProvision" 
			@ProvisionWareText,
			@OwnerReservation,
			@ProductNumber,
			@WareTypeWare,  --Ware.Type
			@ProvisionWareNumber,
			1 --BookedWareCount

	  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  
			--exec Postering @TransactionDatetime,1, @AccountProvisionPostVAT, 0, @GuestNumber, 0, @UserIDNumber, @AccountProvisionFolioNumber, 0, 0, 0, 0, @AccountProvisionWareText, 1, @ProvisionValue, @HotelCountryNumber, 0, 0 , 0, 0, @FK_LogiTurnOverDepartment, @AccountProvisionWareNumber, 0, 0 , 0, 0, @VisualRoomNumber, @DummyTransNrShowOnBillOnAdd OUTPUT, @HotelCustomerNumber--,@ProductWareLinkNumber
			--UPDATE MISTABLE instead
            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,  
				-- -1,    --FolioNumber 
				@MISUpdateType, 
				@PackageSameDayAsLogi, 
				0, --@BookedNumberOfRooms
				0, --@TotalAdults
				0, --@TotalChildren1
				0, --@TotalChildren2   
			    @Value,      --TotalPriceDay
		        @ValueVat,   --VatOfPrice 
			    @PackageVat, --@VatOfPacket  
			    @Package,    --@PackagePrice
				@CurrentDateStr, 
				@CurrentDateStrDeparturePackage, 
				@SegmentNumber, 
				@PointOfSaleNumber, 
				@AllocatedTypeNumber, 
				@MISValueTypeProvision, 
				@HotelCustomerNumber, 
				@Status, 
				@CampaignCode, 
				@ProjectCode, 
				@UserIDNumber, 
				@ProvisionWareGroupNumber, 
				@ResultAccountNumber, 
				@DepartmentNumber, 
				@ProductWareLinkNumber, 
				1, --@IsPackage "isPackageOrProvision" 
				@ProvisionWareText,
				@OwnerReservation,
				@ProductNumber,
				@WareTypeWare,  --Ware.Type
				@ProvisionWareNumber,
				1 --BookedWareCount

			SET @PostIsMade = 1
		  end
		end

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