
CREATE PROCEDURE DBO.[GetPricePackagePartProduct] @ProductNumber INT, @ArrivalDate DATETIME, @DepartureDate DATETIME, @BookedTypeNumber INT, @NumberOfRooms INT, @Adult INT, @Children1 INT, @Children2 INT, @PackageDiscountPercent REAL = 0, @AmountTotal MONEY=-1 OUTPUT, @AmountTotalVat MONEY OUTPUT, @1stDayOnly BIT=0 AS
SET NOCOUNT ON
/*
--nb20070813 Charge stay at departure when period > 1 day updated back to rev from 20070809
--nb20070810 Charge stay at departure when period > 1 day updated
--nb20070809 Charge stay at departure when period > 1 day
  NB20041025 Created
  NOTE

*/
DECLARE @PackageArrival INT
DECLARE @PackageStay INT
DECLARE @PackageDeparture INT
DECLARE @PackageAmount MONEY
DECLARE @PackageAmountVat MONEY
DECLARE @PackageTotal MONEY    SET @PackageTotal = 0
DECLARE @PackageTotalVat MONEY SET @PackageTotalVat = 0
DECLARE @CurrentDate DATETIME
if ISNULL(@Adult,0)=0    Set @Adult = 0
if ISNULL(@Children1,0)=0 Set @Children1 = 0
if ISNULL(@Children2,0)=0 Set @Children2 = 0
DECLARE PackageCur Cursor READ_ONLY FAST_FORWARD FOR
SELECT ArrivalPacketNumber
, StayPacketNumber
, DeparturePacketNumber
, CONVERT(DATETIME, DateStamp)
  FROM ProductPrice WITH (NOLOCK)
  WHERE ProductNumber = @ProductNumber
    AND RoomtypeNumber = @BookedTypeNumber
    AND (NOT ArrivalPacketNumber IS NULL OR NOT StayPacketNumber IS NULL OR NOT DeparturePacketNumber IS NULL)
    AND CONVERT(DATETIME, DateStamp) >= @ArrivalDate AND CONVERT(DATETIME, DateStamp) <= @DepartureDate
OPEN PackageCur
FETCH NEXT FROM PackageCur
INTO @PackageArrival, @PackageStay, @PackageDeparture, @CurrentDate
WHILE (@@FETCH_STATUS=0)
BEGIN
  --nb20070809 Struktur change needed by Charge stay at departure when period > 1 day
  SET @PackageAmount = 0
  SET @PackageAmountVat = 0

  IF ISNULL(@PackageArrival,0)=0 SET @PackageArrival=0;
  IF ISNULL(@PackageStay,0)=0 SET @PackageStay=0;
  IF ISNULL(@PackageDeparture,0)=0 SET @PackageDeparture=0;
--  IF @CurrentDate > @ArrivalDate AND @CurrentDate < @DepartureDate AND @PackageStay > 0
--nb20070809 Charge stay at departure when period > 1 day
  IF @CurrentDate > @ArrivalDate AND @CurrentDate < @DepartureDate AND @PackageStay > 0 AND @1stDayOnly = 0
  BEGIN
--PRINT ' IF @CurrentDate > @ArrivalDate AND @CurrentDate < @DepartureDate AND @PackageStay > 0'
    SET @PackageAmount = @AmountTotal
    IF ISNULL(@PackageAmount,0)=0 SET @PackageAmount = -1
    EXEC GetPricePackage @PackageStay, @CurrentDate, @ProductNumber, @BookedTypeNumber, @NumberOfRooms, @Adult, @Children1, @Children2, @PackageDiscountPercent, @PackageAmount OUTPUT, @PackageAmountVat OUTPUT
  END
  ELSE
  IF @CurrentDate = @ArrivalDate AND @PackageArrival > 0
  BEGIN
--PRINT 'IF @CurrentDate = @ArrivalDate AND @PackageArrival > 0'
    SET @PackageAmount = @AmountTotal
    IF ISNULL(@PackageAmount,0)=0 SET @PackageAmount = -1
    EXEC GetPricePackage @PackageArrival, @CurrentDate, @ProductNumber, @BookedTypeNumber, @NumberOfRooms, @Adult, @Children1, @Children2, @PackageDiscountPercent, @PackageAmount OUTPUT, @PackageAmountVat OUTPUT
  END
--  ELSE
--nb20070809 Charge departure at departure also when stay is charged at departure when period > 1 day
  IF @CurrentDate = @DepartureDate AND @PackageDeparture > 0 AND @1stDayOnly = 0
  BEGIN
    --nb20070809 Charge departure at departure also when stay is charged at departure when period > 1 day
    SET @PackageTotal = @PackageTotal + @PackageAmount
    SET @PackageTotalVat = @PackageTotalVat + @PackageAmountVat

--PRINT 'IF @CurrentDate = @DepartureDate AND @PackageDeparture > 0'
    SET @PackageAmount = @AmountTotal
    IF ISNULL(@PackageAmount,0)=0 SET @PackageAmount = -1
    EXEC GetPricePackage @PackageDeparture, @CurrentDate, @ProductNumber, @BookedTypeNumber, @NumberOfRooms, @Adult, @Children1, @Children2, @PackageDiscountPercent, @PackageAmount OUTPUT, @PackageAmountVat OUTPUT
  END

  SET @PackageTotal = @PackageTotal + @PackageAmount
  SET @PackageTotalVat = @PackageTotalVat + @PackageAmountVat
  FETCH NEXT FROM PackageCur
  INTO @PackageArrival, @PackageStay, @PackageDeparture, @CurrentDate
END

SET @AmountTotal = @PackageTotal
SET @AmountTotalVat = @PackageTotalVat
/*
print 'procedure GetPricePackagePartProduct begin ---------------'
print convert(varchar, @AmountTotal )  
print convert(varchar,  @AmountTotalVat)
print 'procedure GetPricePackagePartProduct end ---------------'
*/
CLOSE PackageCur
DEALLOCATE PackageCur