



CREATE PROCEDURE DBO.GetVatAmountRoomline @ReferenceNumber INT, @GuestNumber INT, @AddressType SMALLINT, @BookedTypeNumber INT, @ArrivalDate INT, @DepartureDate INT, @AdultPax SMALLINT, @Child1Pax SMALLINT, @Child2Pax SMALLINT, @Price MONEY  AS
SET NOCOUNT ON
/*

  NB20041112: Created
  NOTE: Calculates the rooline price and vat
  STATUS: UNDER CONSTRUCTION

*/
DECLARE @FirstDayVatAmount MONEY
, @TotalVatAmount MONEY
, @LineNumberCount INT
, @NumberOfRooms INT
, @ProductNumber INT
, @ProductNumber2 INT
, @ProductNumber3 INT
, @ProductNumber4 INT
, @Day2 SMALLINT
, @Day3 SMALLINT
, @Day4 SMALLINT
, @TotalAmount REAL--SUM of equals prices used instead
, @ArrivalAmount REAL
, @AddonAmount MONEY 
, @AddonVatAmount MONEY
, @PlusProduct BIT
, @TotalPrice MONEY
, @CalcStartDate DATETIME
, @CalcEndDate DATETIME
, @NotUsedVatPercent REAL
, @PackagesplitAfterDiscount BIT
, @DiscountRateAll REAL
, @DiscountRate REAL
, @DiscountRatePackage REAL
, @AmountPackage MONEY
, @VatAmountPackage MONEY
, @DiscountAddOnVatAmount MONEY 
, @NoDiscountOnAddon BIT
, @TotalAddonAmount MONEY
, @TotalAddonVatAmount MONEY
, @TotalAmountPackage MONEY
, @TotalVatAmountPackage MONEY
, @TotalProductVatAmount MONEY
, @TotalArrivalVATAmount MONEY
, @AmountPackageArrival MONEY
, @VatAmountPackageArrival MONEY
, @ArrivalAddonAmount MONEY
, @ArrivalAddonVatAmount MONEY
, @ArrivalAmountPackage MONEY
, @ArrivalVatAmountPackage MONEY
--Select the linecount to multiply at end
SELECT @LineNumberCount=COUNT(ReservationRoomline.LINENUMBER)
, @NumberOfRooms=SUM(ReservationRoomLine.NumberOfRooms)
, @TotalPrice=SUM(ReservationRoomline.TotalPrice)
FROM RESERVATIONROOMLINE WITH (NOLOCK) 
WHERE LineNumber IN (
  SELECT LineNumber FROM ReservationRoomlineXtab WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber and (@GuestNumber = 0 OR ReservationRoomlineXtab.GuestNumber=@GuestNumber)
  ) 
  AND ReservationRoomline.ArrivalDate=@ArrivalDate AND ReservationRoomline.DepartureDate=@DepartureDate 
  AND ReservationRoomline.BookedTypeNumber=@BookedTypeNumber  AND ReservationRoomline.Price=@Price
  AND (ReservationRoomline.Adults=0 OR ReservationRoomline.Adults=@AdultPax)
  AND (ReservationRoomline.Children1=0 OR ReservationRoomline.Children1=@Child1Pax)
  AND (ReservationRoomline.Children2=0 OR ReservationRoomline.Children2=@Child2Pax)
GROUP BY ReservationRoomline.LINENUMBER, ReservationRoomLine.NumberOfRooms, ReservationRoomline.TotalPrice
--Select the first roomline  info to calculate the vat amount 
SELECT TOP 1 @ProductNumber=ReservationRoomline.ProductNumber
, @Day2=ReservationRoomline.Day2, @ProductNumber2=ReservationRoomline.ProductNumber2
, @Day3=ReservationRoomline.Day3, @ProductNumber3=ReservationRoomline.ProductNumber3
, @Day4=ReservationRoomline.Day4, @ProductNumber4=ReservationRoomline.ProductNumber4
FROM RESERVATIONROOMLINE WITH (NOLOCK) 
WHERE LineNumber IN (
  SELECT LineNumber FROM ReservationRoomlineXtab WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber and (@GuestNumber = 0 OR ReservationRoomlineXtab.GuestNumber=@GuestNumber)
  ) 
  AND ReservationRoomline.ArrivalDate=@ArrivalDate AND ReservationRoomline.DepartureDate=@DepartureDate 
  AND ReservationRoomline.BookedTypeNumber=@BookedTypeNumber  AND ReservationRoomline.Price=@Price
  AND (ReservationRoomline.Adults=0 OR ReservationRoomline.Adults=@AdultPax)
  AND (ReservationRoomline.Children1=0 OR ReservationRoomline.Children1=@Child1Pax)
  AND (ReservationRoomline.Children2=0 OR ReservationRoomline.Children2=@Child2Pax)
--Safety after select
IF IsNull (@ProductNumber , 0) = 0
  SET @ProductNumber  = 0
IF IsNull (@Day2, 0) = 0
  SET @Day2 = 0
IF IsNull (@Day3, 0) = 0
  SET @Day3 = 0
IF IsNull (@Day4, 0) = 0
  SET @Day4 = 0
--General option
SELECT @PackagesplitAfterDiscount = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK)  WHERE  SearchKey like '%PackageSplitAfterDiscount%'
IF ISNULL(@PackagesplitAfterDiscount,0)=0 SET @PackagesplitAfterDiscount = 0
SELECT @NoDiscountOnAddon = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK)  WHERE  SearchKey='NoDiscountOnAddon'
IF ISNULL(@NoDiscountOnAddon,0)=0 SET @NoDiscountOnAddon=0
EXEC GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 0,  @DiscountRateAll OUTPUT
EXEC GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 1,  @DiscountRate OUTPUT
IF @PackagesplitAfterDiscount = 1
  SET @DiscountRatePackage = @DiscountRate + @DiscountRateAll
ELSE
  SET @DiscountRatePackage = 0
--Calculate product prices or if plus product 1. part of price of the plus product
SET @PlusProduct = 0
SET @CalcStartDate = Convert (DATETIME, @ArrivalDate)
IF (@Day2=0)
  SET @CalcEndDate = Convert (DATETIME, @DepartureDate)
ELSE
BEGIN
  SET @CalcEndDate = Convert (DATETIME, @ArrivalDate)+@Day2-1
  SET @PlusProduct = 1
END
IF @ProductNumber > 0
BEGIN
  --Get prices for the product (only first day)
  EXEC ProductPrices @ProductNumber, @CalcStartDate,@CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @TotalAmount OUTPUT, @ArrivalAmount OUTPUT,  @AddonAmount OUTPUT, @AddonVatAmount OUTPUT, @PlusProduct
  EXEC GetProductPackagePartPrice @ProductNumber, @NotUsedVatPercent, @CalcStartDate, @CalcStartDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountRatePackage, @AmountPackageArrival OUTPUT, @VatAmountPackageArrival OUTPUT 
  SET @ArrivalAddonAmount = @AddonAmount
  SET @ArrivalAddonVatAmount = @AddonVatAmount
  SET @ArrivalAmountPackage = @AmountPackage
  SET @ArrivalVatAmountPackage = @VatAmountPackage 
  --Get total prices for the product
  EXEC ProductPrices @ProductNumber, @CalcStartDate,@CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @TotalAmount OUTPUT, @ArrivalAmount OUTPUT,  @AddonAmount OUTPUT, @AddonVatAmount OUTPUT, @PlusProduct
  EXEC GetProductPackagePartPrice @ProductNumber, @NotUsedVatPercent, @CalcStartDate, @CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountRatePackage, @AmountPackage OUTPUT, @VatAmountPackage OUTPUT
END
ELSE
BEGIN
  SET @AddonAmount= 0
  SET @AddonVatAmount= 0
  SET @AmountPackage= 0
  SET @VatAmountPackage= 0     
END
--Final Vat calculation
SET @TotalAddonAmount = @AddonAmount
SET @TotalAddonVatAmount = @AddonVatAmount
SET @TotalAmountPackage = @AmountPackage
SET @TotalVatAmountPackage = @VatAmountPackage     
--Plus product
IF (@Day2>0)
BEGIN
--Calculate prices of plus product 2. part 
  SET @CalcStartDate = Convert (DATETIME, @ArrivalDate)+@Day2-1
  IF (@Day3=0)
    SET @CalcEndDate = Convert (DATETIME, @DepartureDate)
  ELSE
    SET @CalcEndDate = Convert (DATETIME, @ArrivalDate)+@Day3-1
  IF @ProductNumber > 0
  BEGIN
    EXEC ProductPrices @ProductNumber, @CalcStartDate,@CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @TotalAmount OUTPUT, @ArrivalAmount OUTPUT,  @AddonAmount OUTPUT, @AddonVatAmount OUTPUT, @PlusProduct
    EXEC GetProductPackagePartPrice @ProductNumber, @NotUsedVatPercent, @CalcStartDate, @CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountRatePackage, @AmountPackage OUTPUT, @VatAmountPackage OUTPUT
  END
  ELSE
  BEGIN
    SET @AddonAmount= 0
    SET @AddonVatAmount= 0
    SET @AmountPackage= 0
    SET @VatAmountPackage= 0     
  END
  --Final Vat calculation
  SET @TotalAddonAmount = @TotalAddonAmount + @AddonAmount
  SET @TotalAddonVatAmount = @TotalAddonVatAmount + @AddonVatAmount
  SET @TotalAmountPackage = @TotalAmountPackage + @AmountPackage
  SET @TotalVatAmountPackage = @TotalVatAmountPackage + @VatAmountPackage     
END
IF (@Day3>0)
BEGIN
--Calculate prices of plus product 2. part 
  SET @CalcStartDate = Convert (DATETIME, @ArrivalDate)+@Day3-1
  IF (@Day4=0)
    SET @CalcEndDate = Convert (DATETIME, @DepartureDate)
  ELSE
    SET @CalcEndDate = Convert (DATETIME, @ArrivalDate)+@Day4-1
  IF @ProductNumber > 0
  BEGIN
    EXEC ProductPrices @ProductNumber, @CalcStartDate,@CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @TotalAmount OUTPUT, @ArrivalAmount OUTPUT,  @AddonAmount OUTPUT, @AddonVatAmount OUTPUT, @PlusProduct
    EXEC GetProductPackagePartPrice @ProductNumber, @NotUsedVatPercent, @CalcStartDate, @CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountRatePackage, @AmountPackage OUTPUT, @VatAmountPackage OUTPUT
  END
  ELSE
  BEGIN
    SET @AddonAmount= 0
    SET @AddonVatAmount= 0
    SET @AmountPackage= 0
    SET @VatAmountPackage= 0     
  END
  --Final Vat calculation
  SET @TotalAddonAmount = @TotalAddonAmount + @AddonAmount
  SET @TotalAddonVatAmount = @TotalAddonVatAmount + @AddonVatAmount
  SET @TotalAmountPackage = @TotalAmountPackage + @AmountPackage
  SET @TotalVatAmountPackage = @TotalVatAmountPackage + @VatAmountPackage     
END
IF (@Day4>0)
BEGIN
--Calculate prices of plus product 2. part 
  SET @CalcStartDate = Convert (DATETIME, @ArrivalDate)+@Day3-1
  SET @CalcEndDate = Convert (DATETIME, @DepartureDate)
  IF @ProductNumber > 0
  BEGIN
    EXEC ProductPrices @ProductNumber, @CalcStartDate,@CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @TotalAmount OUTPUT, @ArrivalAmount OUTPUT,  @AddonAmount OUTPUT, @AddonVatAmount OUTPUT, @PlusProduct
    EXEC GetProductPackagePartPrice @ProductNumber, @NotUsedVatPercent, @CalcStartDate, @CalcEndDate, @BookedTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @DiscountRatePackage, @AmountPackage OUTPUT, @VatAmountPackage OUTPUT
  END
  ELSE
  BEGIN
    SET @AddonAmount= 0
    SET @AddonVatAmount= 0
    SET @AmountPackage= 0
    SET @VatAmountPackage= 0     
  END
  --Final Vat calculation
  SET @TotalAddonAmount = @TotalAddonAmount + @AddonAmount
  SET @TotalAddonVatAmount = @TotalAddonVatAmount + @AddonVatAmount
  SET @TotalAmountPackage = @TotalAmountPackage + @AmountPackage
  SET @TotalVatAmountPackage = @TotalVatAmountPackage + @VatAmountPackage     
END
IF (@NoDiscountOnAddon=0)
   SET @DiscountAddOnVatAmount= Round((@AddonVATAmount) * (@DiscountRate/100),2)
ELSE
   SET @DiscountAddOnVatAmount= 0
--SET @TotalArrivalAmount = 
--dbo.GetVatAmountRoomline 
SET @TotalProductVatAmount=@TotalVatAmountPackage + (@TotalAddonVatAmount -@DiscountAddOnVatAmount) + (@Price - @TotalAmountPackage)

