CREATE PROCEDURE dbo.CalcPacketPrice @DayProduct Bit, @RoomtypeNumber int, @DateStamp Char(8),@PacketNumber int, @ProductNumber int, @Adult int, @Children1 int, @Children2 int, @PackagePrice Float OUTPUT, @VatByPacketValue Float Output, @NamelistSetting int = -1, @OnePersonPaysForAll Bit = 0  AS
SET NOCOUNT ON

/*
Changes:
  KJ 20150105: Bug: Fix for "Error when post of more than one pieces of a menu ware not in percent". Case #CR081859
  KJ: 20140624: Ext: Menu ware split in whole amount. Req #1561
  KJ: 20110307: Ext: Menuware in package. Req# 818
  KJ: 20070823: Bug: Fix for "Packetlines depends on roomtypenumber"
  KJ: 20070823: VAT not calculated
  KJ: 20070214: Cleanup
  KJ: 20061222: Added ShowPrint
  04-01-2005 : KS : imp. @OnePersonPaysForAll 
  03-01-2005 : KS : imp. @NamelistSetting

*/

-- TODO overstyring af pris

Declare @TempAdult int
Declare @TempChild1 int
Declare @TempChild2 int

DECLARE @VatValueNumber INT 
DECLARE @VatValueWareText VARCHAR(10)
DECLARE @VatValueIndex1 FLOAT
DECLARE @VatValueIndex2 FLOAT
DECLARE @VatValueIndex3 FLOAT
DECLARE @VatValueIndex4 FLOAT
DECLARE @VatValueIndex5 FLOAT

DECLARE @WareNumber INT
DECLARE @HandleAsMenuWare BIT
DECLARE @MenuProcentage MONEY
DECLARE @MenuVatValueNumber INT
DECLARE @MenuVatValueWare FLOAT
DECLARE @MenuWarePrice FLOAT
DECLARE @InPercent BIT

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

if @NamelistSetting = 1 AND @OnePersonPaysForAll=0
begin
  SET @TempAdult = @Adult
  SET @TempChild1 = @Children1
  SET @TempChild2 = @Children2

  SET @Adult = 1
  SET @Children1 = 1
  SET @Children2 = 1
end

if IsNull(@PacketNumber,0)=0   Set  @PacketNumber = 0 -- safe

if (@PacketNumber>0)
begin
  Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue1'
  if (CHARINDEX(',',@VatValueWareText)>0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',',@VatValueWareText) ,1, '.')
  SET @VatValueIndex1 = CONVERT(Float,@VatValueWareText) 

  Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue2'
  if (CHARINDEX(',',@VatValueWareText)>0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',',@VatValueWareText) ,1, '.')
  SET @VatValueIndex2 = CONVERT(Float,@VatValueWareText) 

  Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue3'
  if (CHARINDEX(',',@VatValueWareText)>0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',',@VatValueWareText) ,1, '.')
  SET @VatValueIndex3 = CONVERT(Float,@VatValueWareText) 

  Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue4'
  if (CHARINDEX(',',@VatValueWareText)>0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',',@VatValueWareText) ,1, '.')
  SET @VatValueIndex4 = CONVERT(Float,@VatValueWareText) 

  Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue5'
  if (CHARINDEX(',',@VatValueWareText)>0)
    SET @VatValueWareText = STUFF(@VatValueWareText, CHARINDEX(',',@VatValueWareText) ,1, '.')
  SET @VatValueIndex5 = CONVERT(Float,@VatValueWareText) 

  if ISNULL(@VatValueIndex1,0) = 0  SET @VatValueIndex1 = 0
  if ISNULL(@VatValueIndex2,0) = 0  SET @VatValueIndex2 = 0
  if ISNULL(@VatValueIndex3,0) = 0  SET @VatValueIndex3 = 0
  if ISNULL(@VatValueIndex4,0) = 0  SET @VatValueIndex4 = 0
  if ISNULL(@VatValueIndex5,0) = 0  SET @VatValueIndex5 = 0

  Declare @WarePrice Float
  Declare @DebitType SmallInt
  Declare @ResultAccountNumber Integer 
  Declare @VatValueWare Float

  Declare @RoomPriceAdult Money
  Declare @RoomPriceChildren1 Money
  Declare @RoomPriceChildren2 Money

  if IsNull(@DayProduct,0)=0   Set  @DayProduct = 0 -- safe

  --Exec CalcRoomPrice @ProductNumber, @DateStamp, @RoomtypeNumber, @Adult, @Children1, @Children2, @RoomPriceAdult OUTPUT, @RoomPriceChildren1 OUTPUT,@RoomPriceChildren2 OUTPUT 
  --nb20190705 Replaced with function
  SELECT @RoomPriceAdult = Adult, @RoomPriceChildren1 = ChildPax1, @RoomPriceChildren2 = ChildPax2 
  FROM dbo.GetRoomPrice (@ProductNumber, @DateStamp, @RoomTypeNumber, @Adult, @Children1, @Children2, -1, 0, 0, 0)

  Declare WareCur Cursor Forward_Only For
    SELECT Ware.ResultAccountNumber, PacketItem_New.PriceOverWrite, PacketItem_New.DebType, Ware.WareNumber
    FROM Ware WITH (NOLOCK)
    INNER JOIN PacketItem_New WITH (NOLOCK) ON Ware.WareNumber = PacketItem_New.WareNumber AND PacketItem_New.PacketNumber=@PacketNumber
    LEFT OUTER JOIN PacketItem_NewRoomTypeXtab WITH (NOLOCK) ON PacketItem_NewRoomTypeXtab.PacketItemNumber = PacketItem_New.PacketItemNumber  
    WHERE ((ISNULL(PacketItem_NewRoomTypeXtab.RoomTypeNumber,-1) = -1) OR (PacketItem_NewRoomTypeXtab.RoomTypeNumber = @RoomTypeNumber))
  Open WareCur

  Fetch Next From WareCur Into @ResultAccountNumber,@WarePrice, @DebitType, @WareNumber

  Set @VatByPacketValue=0
  Set @PackagePrice = 0

  IF @ShowPrint = 1
  BEGIN
    PRINT 'dbo.CalcPacketPrice'
    print @PacketNumber
  END

  While  (@@FETCH_STATUS <> -1)
  begin
    if IsNull(@WarePrice,0)=0  SET  @WarePrice = 0 -- safe
    if IsNull(@DebitType,0)=0   Set  @DebitType = 1 -- safe

    if (@DebitType=1)
    begin
      if (@Adult)=0
        SET @WarePrice=0 
    end
    else if (@DebitType=2)
    begin
      if (@Adult)=0
        SET @WarePrice=0
      else
        SET @WarePrice=@RoomPriceAdult*(@WarePrice/100)
    end
    else if (@DebitType=3)
    begin
      if (@Adult)=0
        SET @WarePrice=0
      else
        SET @WarePrice=@WarePrice*@Adult
    end
    else if (@DebitType=4)
    begin
      if (@Children1)=0
        SET @WarePrice=0 
    end
    else if (@DebitType=5)
    begin
      if (@Children1)=0
        SET @WarePrice=0
      else
        SET @WarePrice=@WarePrice*@Children1
    end
    else if (@DebitType=6)
    begin
      if (@Children1)=0
        SET @WarePrice=0
      else
        SET @WarePrice=@RoomPriceChildren1*(@WarePrice/100)
    end
    else if (@DebitType=7)
    begin
      if (@Children2)=0
        SET @WarePrice=0 
    end
    else if (@DebitType=8)
    begin
      if (@Children2)=0
        SET @WarePrice=0
      else
        SET @WarePrice=@WarePrice*@Children2
    end
    else if (@DebitType=9)
    begin
      if (@Children2)=0
        SET @WarePrice=0
      else
        SET @WarePrice=@RoomPriceChildren2*(@WarePrice/100)
    end
    
/*
    SET @HandleAsMenuWare = 0
    
    SELECT @IsMenuWare = COUNT(*) FROM MenuWareXTab WHERE MenuNumber = @WareNumber
    IF @IsMenuWare = 1
    BEGIN
      SELECT @SumPercentage = SUM(mw.procentage)
      FROM dbo.ware w WITH (NOLOCK) 
      INNER JOIN dbo.MenuWareXTab mw WITH (NOLOCK) ON w.warenumber = mw.warenumber 
      AND mw.menunumber = @WareNumber
      
      IF @SumPercentage <> 100
        SET @HandleAsMenuWare = 0
      ELSE
        SET @HandleAsMenuWare = 1
    END
    ELSE
      SET @HandleAsMenuWare = 0
*/
    EXEC WareIsMenuWare @WareNumber, @WarePrice, 1, @HandleAsMenuWare OUTPUT

    IF @HandleAsMenuWare = 1
    BEGIN
      DECLARE db_ware_cur CURSOR STATIC FOR
        SELECT mw.Procentage, Accounts.VatValueNumber, ISNUll(MenuWare.InPercent, 0)
        FROM Ware w WITH (NOLOCK)          
        INNER JOIN MenuWareXTab mw WITH (NOLOCK) ON w.WareNumber = mw.WareNumber 
          AND mw.MenuNumber = @WareNumber
        INNER JOIN ACCOUNTS WITH (NOLOCK) ON w.ResultAccountNumber = Accounts.AccountNumber
		INNER JOIN MenuWare WITH (NOLOCK) ON MenuWare.FK_MenuNumber = mw.MenuNumber
      OPEN db_ware_cur

      FETCH NEXT FROM db_ware_cur INTO @MenuProcentage, @MenuVatValueNumber, @InPercent
          
      WHILE @@FETCH_STATUS = 0
      BEGIN
        EXEC GetVatvalueFromVATValueNumber @MenuVatValueNumber, @MenuVatValueWare OUTPUT
		IF @InPercent = 1
          SET @MenuWarePrice = ((@WarePrice*@MenuProcentage)/100.0)
		ELSE
		  SET @MenuWarePrice = @MenuProcentage
      
        Set @VatByPacketValue = @VatByPacketValue+(@MenuWarePrice*(1-(1/((@MenuVatValueWare/100)+1))))
        Set @PackagePrice = @PackagePrice + @MenuWarePrice

        FETCH NEXT FROM db_ware_cur INTO @MenuProcentage, @MenuVatValueNumber, @InPercent
      END
      
      CLOSE db_ware_cur
      DEALLOCATE db_ware_cur
    END
    ELSE
    BEGIN
      Select @VatValueNumber = VatValueNumber from Accounts WITH (nolock) where AccountNumber=@ResultAccountNumber
      SET @VatValueWare = 
      CASE @VatValueNumber
        WHEN 1 THEN @VatValueIndex1
        WHEN 2 THEN @VatValueIndex2
        WHEN 3 THEN @VatValueIndex3
        WHEN 4 THEN @VatValueIndex4
        WHEN 5 THEN @VatValueIndex5
      END
      Set @VatByPacketValue=@VatByPacketValue+(@WarePrice*(1-(1/((@VatValueWare/100)+1))))
      Set @PackagePrice = @PackagePrice + @WarePrice
    END
    
    Fetch Next From WareCur Into @ResultAccountNumber,@WarePrice, @DebitType, @WareNumber
  end
  Deallocate WareCur

  if IsNull(@PackagePrice,0)=0 SET @PackagePrice = 0
  if IsNull(@VatByPacketValue,0)=0 SET @VatByPacketValue = 0

  if @NamelistSetting = 1 AND @OnePersonPaysForAll=0
  begin
    SET @PackagePrice = @PackagePrice * (@TempAdult + @TempChild1 + @TempChild2)
    SET @VatByPacketValue = @VatByPacketValue * (@TempAdult + @TempChild1 + @TempChild2)
  end

  IF @ShowPrint = 1
  BEGIN
    print '@PackagePrice'
    print @PackagePrice
  END
end
else
begin
  SET @PackagePrice = 0
  SET @VatByPacketValue = 0
end