CREATE PROCEDURE DBO.[GetPricePackage] @PackageNumber int, @Date Datetime, @ProductNumber int, @RoomTypeNumber int, @NumberOfRooms int, @Adult int, @Children1 int, @Children2 int, @DiscountPercent Float = 0, @Amount Money=-1 OUTPUT, @VatAmount Money OUTPUT AS
SET NOCOUNT ON
/*
  --nb20111021 Case CR019788
  KJ 20070525: Department rewrite.
  nb20070302 take care of empty department and optimize by excluding 0 price packet items
  NB20051013 wrong percent calculation
  NB20040310 Created
  NOTE: Calculates the specified package amount and vat amount
*/
DECLARE @PriceoverwriteValue MONEY
DECLARE @dummyPacketItemNumber INT
IF NOT(ISNULL(@Amount,0)=0 OR @Amount=-1)
  SET @PriceoverwriteValue = @Amount
SET @Amount = 0
IF ISNULL(@VatAmount,0)=0  SET @VatAmount = 0 -- safe
IF (@PackageNumber>0)
BEGIN
--PRINT @PackageNumber
  DECLARE @PackagesplitAfterReduction BIT
  SELECT @PackagesplitAfterReduction =  CONVERT(BIT, KeyValue) FROM Hotelsetup WITH (NOLOCK) WHERE Searchkey ='PackagesplitAfterReduction'
  IF ISNULL(@PackagesplitAfterReduction,0) =0 SET @PackagesplitAfterReduction = 0
  SET @Amount = 0
  SET @VatAmount = 0
  DECLARE @ProductFixedPrice BIT
  DECLARE @ProductDay BIT
  IF @ProductNumber>0
  BEGIN
    SELECT @ProductFixedPrice = FixedPrice, @ProductDay  = DayProduct FROM Products WITH (NOLOCK) WHERE ProductNumber=@ProductNumber
  END
  ELSE
  BEGIN
    SET @ProductFixedPrice = 0
  END
  IF ISNULL(@ProductFixedPrice, 0) = 0 SET @ProductFixedPrice=0
  IF ISNULL(@ProductDay,0)=0 SET @ProductDay=0

  DECLARE @PacketPrice FLOAT
  DECLARE @VatPercent FLOAT
  DECLARE @DebitType SMALLINT
  DECLARE @ProcentDebType BIT
  DECLARE @DateStr CHAR(8)
  DECLARE @RoomPriceAdult MONEY
  DECLARE @RoomPriceChildren1 MONEY
  DECLARE @RoomPriceChildren2 MONEY
  DECLARE #WareCur CURSOR FAST_FORWARD READ_ONLY FOR

  SELECT DISTINCT PacketItem_New.PacketItemNumber,
    dbo.GetVatPercent((SELECT VatValueNumber From Accounts WITH (NOLOCK) WHERE Accounts.AccountNumber=Ware.ResultAccountNumber)) AS VatPercent
  ,  PacketItem_New.PriceOverWrite
  ,  PacketItem_New.DebType
  ,  CASE WHEN PacketItem_New.DebType IN (2,5,8) THEN 1 ELSE 0 END AS ProcentDebType
  FROM Ware WITH (NOLOCK),PacketItem_New WITH (NOLOCK)
  INNER JOIN PacketItem_NewDepartmentXTab pdx WITH (NOLOCK) ON pdx.PacketItemNumber = PacketItem_New.PacketItemNumber AND pdx.Type = 1 -- DepartmentServiceOverwrite
  WHERE Ware.WareNumber = PacketItem_New.WareNumber 
  AND PacketItem_New.PacketNumber=@PackageNumber 
  AND ISNULL(PacketItem_New.PriceOverWrite, 0) <> 0 
--  AND NOT DepartmentServiceOverwrite IS NULL KJ 20070525
  ORDER BY ProcentDebType
  OPEN #WareCur
  FETCH NEXT FROM #WareCur
  INTO @dummyPacketItemNumber, @VatPercent, @PacketPrice, @DebitType, @ProcentDebType
  SET @DateStr = CONVERT (CHAR(8), @Date, 112)
  IF (ISNULL(@PriceoverwriteValue,0)=0)
  BEGIN
    SET @RoomPriceAdult = 0
    SET @RoomPriceChildren1= 0
    SET @RoomPriceChildren2= 0
    EXEC CalcRoomPrice @ProductNumber, @DateStr, @RoomTypeNumber, @Adult, @Children1, @Children2, @RoomPriceAdult OUTPUT, @RoomPriceChildren1 OUTPUT,@RoomPriceChildren2 OUTPUT
  END
  ELSE
  BEGIN
    SET @RoomPriceAdult = @PriceoverwriteValue
    SET @RoomPriceChildren1 = @PriceoverwriteValue
    SET @RoomPriceChildren2 = 0
  END
/*
PRINT 'procedure GetPricePackage begin after-------------------------------------------------------'
PRINT '@ProductNumber ' + CONVERT(VARCHAR, @ProductNumber)
PRINT '@DateStr ' + CONVERT(VARCHAR, @DateStr)
PRINT '@RoomTypeNumber ' + CONVERT(VARCHAR, @RoomTypeNumber)
PRINT '@Adult ' + CONVERT(VARCHAR, @Adult)
PRINT '@Children1 ' + CONVERT(VARCHAR, @Children1)
PRINT '@Children2 ' + CONVERT(VARCHAR, @Children2)
PRINT '@RoomPriceAdult ' + CONVERT(VARCHAR, @RoomPriceAdult)
PRINT '@RoomPriceChildren1 ' + CONVERT(VARCHAR, @RoomPriceChildren1)
PRINT '@RoomPriceChildren2 ' + CONVERT(VARCHAR, @RoomPriceChildren2)
PRINT '@PacketPrice ' + CONVERT(VARCHAR, @PacketPrice)
PRINT '@DebitType ' + CONVERT(VARCHAR, @DebitType)
PRINT '@ProductDay ' + CONVERT(VARCHAR, @ProductDay)
PRINT '@ProductFixedPrice ' + CONVERT(VARCHAR, @ProductFixedPrice)
PRINT '@NumberOfRooms ' + CONVERT(VARCHAR, @NumberOfRooms)

PRINT 'procedure GetPricePackage end after----------------------------------------------------------------------'
*/
  DECLARE @FixAmountSum FLOAT  SET @FixAmountSum = 0
  DECLARE @Pieces SMALLINT
  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    IF ISNULL(@PacketPrice,0)=0 SET @PacketPrice = 0 -- safe
    IF ISNULL(@DebitType,0)=0 SET @DebitType = 1 -- safe
    IF ISNULL(@VatPercent,0) = 0 SET @VatPercent = 0 --safe
    IF (@DebitType=1)
    BEGIN
      IF (@Adult)=0
        SET @PacketPrice=0
      SET @Pieces = 1
    END
    ELSE
    IF (@DebitType=3)
    BEGIN
      IF (@Adult)=0
      BEGIN
        SET @PacketPrice=0
        SET @Pieces = 1
      END
      ELSE
      BEGIN
        SET @PacketPrice=@PacketPrice*@Adult
        SET @Pieces = @Adult
      END
    END
    ELSE
    IF (@DebitType=2)
    BEGIN
      IF (@ProductDay=0) OR (@ProductDay=1 AND @ProductFixedPrice=1)
      BEGIN
        --nb20111021 Case CR019788
        IF (@ProductDay=0)
          SET @RoomPriceAdult = @RoomPriceAdult / @NumberOfRooms 
          
        SET @Pieces = @NumberOfRooms
      END
      ELSE
        SET @Pieces = @Adult
      IF (@Adult)=0
        SET @PacketPrice=0
      ELSE
      BEGIN
--PRINT '1@PackagesplitAfterReduction ' + CONVERT(VARCHAR,@PackagesplitAfterReduction)
--PRINT '1@RoomPriceAdult ' + CONVERT(VARCHAR, @RoomPriceAdult)      
--PRINT '1@FixAmountSum ' + CONVERT(VARCHAR, @FixAmountSum)      
--PRINT '1@PacketPrice ' + CONVERT(VARCHAR,@PacketPrice)
         IF @PackagesplitAfterReduction = 0
           SET @PacketPrice=@RoomPriceAdult*(@PacketPrice/100)
         ELSE
           SET @PacketPrice=(@RoomPriceAdult - @FixAmountSum)*(@PacketPrice/100)
--PRINT '1@PacketPrice ' + CONVERT(VARCHAR,@PacketPrice)
--PRINT '1@Pieces ' + CONVERT(VARCHAR, @Pieces)
         IF (@ProductDay=0) OR (@ProductDay=1 AND ISNULL(@PriceoverwriteValue,0)=0)
          SET @PacketPrice = @PacketPrice * @Pieces
         IF @DiscountPercent>0
          --SET @PacketPrice = ROUND(@PacketPrice * (1-(@DiscountPercent/100)),2)
          --nb20051013
          SET @PacketPrice = ROUND(@PacketPrice *(1-(1/Convert(Float,((@DiscountPercent/100)+1)))),2)

      END
    END
    ELSE
    IF (@DebitType=4)
    BEGIN
      SET @Pieces = 1
      IF (@Children1)=0
        SET @PacketPrice=0
    END
    ELSE
    IF (@DebitType=5)
    BEGIN
      IF (@Children1)=0
      BEGIN
        SET @PacketPrice=0
        SET @Pieces = 1
      END
      ELSE
      BEGIN
        SET @PacketPrice=@PacketPrice*@Children1
        SET @Pieces = @Children1
      END
    END
    ELSE
    IF (@DebitType=6)
    BEGIN
      IF (@ProductDay=0) OR (@ProductDay=1 AND @ProductFixedPrice=1)
        SET @Pieces = @NumberOfRooms
      ELSE
        SET @Pieces = @Children1
      IF (@Children1)=0
        SET @PacketPrice=0
      ELSE
      BEGIN
         IF @PackagesplitAfterReduction = 0
           SET @PacketPrice=@RoomPriceChildren1*(@PacketPrice/100)
         ELSE
           SET @PacketPrice=(@RoomPriceChildren1 - @FixAmountSum)*(@PacketPrice/100)
         IF (@ProductDay=0) OR (@ProductDay=1 AND ISNULL(@PriceoverwriteValue,0)=0)
           SET @PacketPrice = @PacketPrice * @Pieces
         IF @DiscountPercent>0 
          --SET @PacketPrice = ROUND(@PacketPrice * (1-(@DiscountPercent/100)),2)
          --nb20051013
          SET @PacketPrice = ROUND(@PacketPrice *(1-(1/Convert(Float,((@DiscountPercent/100)+1)))),2)

      END
    END
    ELSE
    IF (@DebitType=7)
    BEGIN
      SET @Pieces = 1
      IF (@Children2)=0
        SET @PacketPrice=0
    END
    ELSE
    IF (@DebitType=8)
    BEGIN
      IF (@Children2)=0
      BEGIN
        SET @PacketPrice=0
        SET @Pieces = 1
      END
      ELSE
      BEGIN
        SET @PacketPrice=@PacketPrice*@Children2
        SET @Pieces = @Children2
      END
    END
    ELSE
    IF (@DebitType=9)
    BEGIN
      IF (@ProductDay=0) OR (@ProductDay=1 AND @ProductFixedPrice=1)
        SET @Pieces = @NumberOfRooms
      ELSE
        SET @Pieces = @Children2
      IF (@Children2)=0
        SET @PacketPrice=0
      ELSE
      BEGIN
        IF @PackagesplitAfterReduction = 0
          SET @PacketPrice=@RoomPriceChildren2*(@PacketPrice/100)
        ELSE
          SET @PacketPrice=(@RoomPriceChildren2 - @FixAmountSum)*(@PacketPrice/100)
        IF (@ProductDay=0) OR (@ProductDay=1 AND ISNULL(@PriceoverwriteValue,0)=0)
          SET @PacketPrice = @PacketPrice * @Pieces
        IF @DiscountPercent>0
          --SET @PacketPrice = ROUND(@PacketPrice * (1-(@DiscountPercent/100)),2)
          --nb20051013
          SET @PacketPrice = ROUND(@PacketPrice *(1-(1/Convert(Float,((@DiscountPercent/100)+1)))),2)

      END
    END
--PRINT '@PacketPrice ' + CONVERT(VARCHAR,@PacketPrice)
--PRINT '@Pieces ' + CONVERT(VARCHAR, @Pieces)
    IF @PackagesplitAfterReduction = 1
    BEGIN
      IF NOT(@DebitType IN (3,6,7))
        SET @FixAmountSum = @FixAmountSum + @PacketPrice
    END
    SET @PacketPrice = ROUND(CONVERT(MONEY,@PacketPrice * @NumberOfRooms),2)
    IF (@NumberOfRooms<0)
    BEGIN
      SET @PacketPrice = ABS(@PacketPrice)
      SET @Pieces = -1*@Pieces
    END
--PRINT '@PacketPrice ' + CONVERT(VARCHAR,@PacketPrice)
--    IF @PacketPrice>0
--    BEGIN
      SET @PacketPrice = ROUND(@PacketPrice,2)
      SET @Amount = @Amount + @PacketPrice
      SET @VatAmount = @VatAmount + @PacketPrice*(1-(1/Convert(Float,((@VatPercent/100)+1))))
--PRINT '@VatAmount ' + CONVERT(VARCHAR,@VatAmount)
--PRINT '----------------------------------------------------------------------'
--    END
    FETCH NEXT FROM #WareCur
  INTO @dummyPacketItemNumber, @VatPercent, @PacketPrice, @DebitType, @ProcentDebType
  END
  CLOSE #WareCur
  DEALLOCATE #WareCur
  IF ISNULL(@Amount,0)= 0 SET @Amount=0
  IF ISNULL(@VatAmount,0)= 0 SET @VatAmount=0
END
/*
PRINT '@Amount' + CONVERT(VARCHAR,@Amount)
PRINT '@VatAmount' + CONVERT(VARCHAR,@VatAmount)
PRINT 'GetPackagePrice END'
*/