CREATE PROCEDURE dbo.MisStoreLogiAndCalcDiscount @Room_HallType TINYINT,@OrderBookBudgetCalcDiscount BIT, @DiscountAsNettoPrice BIT, @ChargeDiscountAsProduct BIT, @DiscountVatValueWare FLOAT,  @SplitBillNumber INT, @IsFixedPriceProduct BIT, @tmpUpdateType TINYINT,
  @tmpSameDayAsLogi BIT, @ValueType TINYINT, @BookedNumberOfRooms INT, @TotalAdults INT, @TotalChildren1 INT, @TotalChildren2 INT, @ReferenceNumber INT, @GuestNumber INT, @SystemDateStr CHAR(8), @CurrentDateStr CHAR(8), @CurrentDateStrDeparturePackage CHAR(8),
  @SegmentNumber INT, @PointOfSaleNumber INT, @AGuestTypeNumber INT, @APurposeCodeNumber INT, @RoomTypeNumber INT, @HotelCustomerNumber INT, @Status SMALLINT, @CampaignCode INT, @ProjectCode INT, @StaffID INT, @WareGroupNumber INT, @WareNumber INT, @OwnerReservation BIT, @DepartmentNumber INT, @Pwln BIGINT,
  @AccountNumber INT, @DiscountAccountNumber INT, @TotalPrice MONEY, @VatOfPrice MONEY, @PackagePrice MONEY, @PackageVatOfPrice MONEY, @ProductNumber INT, @ADiscountMISValueType INT, @ADiscountWareNumber INT, @ACountryNumber INT, @AArrivalTime DATETIME, @ADepartureTime DATETIME AS
SET NOCOUNT ON

/*
  Changes:
  KJ 20241113: Bug: Fix for "VAT on Discount not correct when charge discount as product"
  KJ 20241009: Ext: Added From/To Time
  KJ 20240521: Bug: "Fix" for "Arithmetic overflow error converting expression to data type int
  KJ 20231218: Ext: Added Guesttype, purposeCode and country
  KJ 20230412: Valuetype wrong for discount if ruuning charge discount as product
  SG 20210309: Added BookedWareCount
  SG 20210224: Added @NumberOfRooms (BookedWareCount)
  SG 20200514: Added @WareType and @WareNumber
  SG 20200207: Don't subtract package before discount. Calculate discount when @DiscountAsNettoPrice.
  SG 20160902: Don't calculate discount when @DiscountAsNettoPrice
  SG 20130129, CR 051930: No discount was calculated when VAT=0
  OF 20120907: Bug: Discount was added back in time from reservations.
  OF 20120601: New. For storing logi data in mis and calculating discount.
*/

DECLARE @AllDiscountRate FLOAT
DECLARE @DiscountRate FLOAT
DECLARE @AddressType INT
DECLARE @tmpPrice MONEY
DECLARE @DiscountPrice MONEY
DECLARE @DiscountPriceVat MONEY
DECLARE @WareType INT
DECLARE @WareName VARCHAR(50)

-- Calc Discount
SET @AllDiscountRate = 0
SET @DiscountRate = 0
--SET @TotalPrice = @TotalPrice - @PackagePrice
--SET @VatOfPrice = @VatOfPrice - @PackageVatOfPrice
-- Not taken HotelSetup "PackageSplitAfterDiscount" into account. 
-- When set, there should not be calculated discount on packages, but the discount on packages should be transferred to stay

SELECT @WareType = Type, @WareName = WareName 
FROM Ware WITH (NOLOCK)
WHERE WareNumber = @WareNumber

IF (@OrderBookBudgetCalcDiscount = 1) AND (@TotalPrice <> 0) AND (@CurrentDateStr >= @SystemDateStr)
BEGIN
  DECLARE @FolioNumber INT
  EXEC FindFolioNumber @ReferenceNumber, @GuestNumber, 0, @WareNumber, @SplitbillNumber, 0, @FolioNumber OUTPUT, @AddressType OUTPUT

  EXEC GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 0, @AllDiscountRate OUTPUT
  IF @Room_HallType = 0 -- ROOM
    EXEC GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 1, @DiscountRate OUTPUT
  ELSE
  BEGIN
    -- HALL
    IF @IsFixedPriceProduct = 1
      EXEC GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 2, @DiscountRate OUTPUT
    ELSE
      EXEC GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 3, @DiscountRate OUTPUT
  END

  SET @tmpPrice = @TotalPrice
  
  DECLARE @UsedAccountNumber INT
  DECLARE @UsedValueType TINYINT
  DECLARE @UsedWareNumber INT

  IF @ChargeDiscountAsProduct = 0
  BEGIN
    SET @UsedAccountNumber = @DiscountAccountNumber
    SET @UsedValueType = @ADiscountMISValueType
    SET @UsedWareNumber = @ADiscountWareNumber
  END
  ELSE
  BEGIN
    SET @UsedAccountNumber = @AccountNumber
    SET @UsedValueType = @ValueType
    SET @UsedWareNumber = @WareNumber
    SELECT @DiscountVatValueWare = ISNULL(dbo.GetVatPercent(Accounts.VatValueNumber), 0)
    FROM Ware WITH (NOLOCK)
    INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Ware.ResultAccountNumber
    WHERE Ware.WareNumber = @WareNumber
  END

  IF @DiscountAsNettoPrice=0
  BEGIN
    IF @AllDiscountRate > 0
    BEGIN
      
      SET @DiscountPrice = Round((@TotalPrice*(@AllDiscountRate/100)),2) * -1 --Discount, negative
      IF (@DiscountPrice <> 0)
      BEGIN
        SET @DiscountPriceVat = (@DiscountPrice*(1-(1/((@DiscountVatValueWare/100)+1))))
        EXEC UpdateMISTable @SystemDateStr, @Room_HallType, @ReferenceNumber, @GuestNumber, @tmpUpdateType, @tmpSameDayAsLogi, 0, 0, 0, 0, @DiscountPrice, @DiscountPriceVat, 0, 0, @CurrentDateStr, @CurrentDateStrDeparturePackage, 
          @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @RoomTypeNumber, @UsedValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @UsedAccountNumber, @DepartmentNumber, -1, 0, @WareName, @OwnerReservation, @ProductNumber,
          @WareType, @UsedWareNumber, 0, @ACountryNumber, @AArrivalTime, @ADepartureTime
      END
    END

    IF @DiscountRate > 0
    BEGIN
      SET @DiscountPrice = Round((@TotalPrice*(@DiscountRate/100)),2)*-1

      IF (@DiscountPrice <> 0)
      BEGIN
        SET @DiscountPriceVat = (@DiscountPrice*(1-(1/((@DiscountVatValueWare/100)+1))))
        EXEC UpdateMISTable @SystemDateStr, @Room_HallType, @ReferenceNumber, @GuestNumber, @tmpUpdateType, @tmpSameDayAsLogi, 0, 0, 0, 0,  @DiscountPrice, @DiscountPriceVat, 0, 0, @CurrentDateStr, @CurrentDateStrDeparturePackage, 
          @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @RoomTypeNumber, @UsedValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @UsedAccountNumber, @DepartmentNumber, -1, 0, @WareName, @OwnerReservation, @ProductNumber,
          @WareType, @UsedWareNumber, 0, @ACountryNumber, @AArrivalTime, @ADepartureTime
      END
    END
  END
  ELSE
    SET @TotalPrice = @TotalPrice - (Round((@tmpPrice*((@AllDiscountRate + @DiscountRate)/100)),2))
END
-- Calc Discount END

--SET @TotalPrice = @TotalPrice + @PackagePrice
--SET @VatOfPrice = @VatOfPrice + @PackageVatOfPrice
--print 'ReferenceNumber=' + convert(varchar, @ReferenceNumber) + ', DateStamp=' + @CurrentDateStr + ', WareNumber=' + convert(varchar, @wareNumber) + ', @NumberOfRooms=' + convert(varchar, @NumberOfRooms) + ', @BookedNumberOfRooms=' + convert(varchar, @BookedNumberOfRooms)
--Store total before discount

DECLARE @piecesOrNoOfRooms INT

SELECT @piecesOrNoOfRooms = Al.Pieces
FROM Reservations R WITH (NOLOCK)
LEFT OUTER JOIN ArrangementReservationsXtab X WITH (NOLOCK) ON R.ReferenceNumber = X.ReferenceNumber 
  AND R.GuestNumber = X.GuestNumber
INNER JOIN ArrangementLines Al WITH (NOLOCK) ON X.ArrangementLineNumber = Al.ArrangementLineNumber
WHERE R.ReferenceNumber = @referenceNumber
AND Al.WareNumber = @wareNumber
AND Al.date = @currentDateStr

SET @PiecesOrNoOfRooms = ISNULL(@PiecesOrNoOfRooms, @BookedNumberOfRooms)

IF (@TotalPrice <> 0 OR @TotalAdults > 0 OR @TotalChildren1 > 0 OR @TotalChildren2 > 0 OR @PiecesOrNoOfRooms > 0)
  EXEC UpdateMISTable @SystemDateStr, @Room_HallType, @ReferenceNumber, @GuestNumber, @tmpUpdateType, @tmpSameDayAsLogi, @BookedNumberOfRooms, @TotalAdults, @TotalChildren1, @TotalChildren2, 
    @TotalPrice, @VatOfPrice, 0, 0, @CurrentDateStr, @CurrentDateStrDeparturePackage, @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @RoomTypeNumber, @ValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @AccountNumber, @DepartmentNumber, 
    @Pwln, 0, @WareName, @OwnerReservation, @ProductNumber, @WareType, @WareNumber, @PiecesOrNoOfRooms, @ACountryNumber, @AArrivalTime, @ADepartureTime