CREATE PROCEDURE DBO.GetArrangementDebTypePieces(
@IsNewArrangement INT,
@DebType int,
@Found int,
@Old_ProductNumber int,
@New_ProductNumber int,
@CurrentDate char(8),
@FormatedOldEndDate char(8),
@FormatedOldStartDate char(8),
@FormatedStartDate char(8),
@FormatedEndDate char(8),
@Old_NumberOfPersons int,
@New_NumberOfpersons int,
@Old_NumberOfChildren1 int,
@New_NumberOfChildren1 int,
@Old_NumberOfChildren2 int,
@New_NumberOfChildren2 int,
@Old_Pieces int,
@New_Pieces int,
@FromNameList int,
@AUseArrangementTolerance int,
@APieces int OUTPUT,
@APriceOverwrite REAL OUTPUT,
@ATotalPrice real OUTPUT)
AS
BEGIN
  DECLARE @Pieces INT
  DECLARE @PriceOverwrite REAL
  DECLARE @TotalPrice REAL

  SET @Pieces = @APieces
  SET @PriceOverwrite = @APriceOverwrite
  SET @TotalPrice = @ATotalPrice

  IF @DebType = 1     --Fixed Price Per room /Hall (adult)
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @FromNameList = 1
      BEGIN
        SELECT
          @Pieces = 1
        SELECT
          @TotalPrice = @PriceOverWrite
      END
      ELSE
      BEGIN
        SELECT
          @pieces = 1 * @New_Pieces
        SELECT
          @TotalPrice = @PriceoverWrite * @New_Pieces
      END
    END
    ELSE
    BEGIN
      IF @FromNameList = 1
      BEGIN
        SELECT
          @Pieces = 0
        SELECT
          @TotalPrice = 0
      END
      ELSE
      BEGIN
        SELECT
          @Pieces = @New_Pieces - @Old_Pieces
        SELECT
          @TotalPrice = 0
      END
    END
  END
  ELSE
  IF @DebType = 2   -- % of room price /Hall (adult)
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @Fromnamelist = 1
      BEGIN
        SELECT
          @Pieces = @New_numberofPersons
      END
      ELSE
        SELECT
          @pieces = @New_NumberOfpersons * @New_Pieces
      SELECT
        @TotalPrice = 0
      SELECT
        @PriceOverWrite = 0
    END
    ELSE
    BEGIN
      IF @FromNameList = 1
      BEGIN
        IF @new_NumberOfPersons <> @Old_NumberOfPersons --is used if namelistreservationperperson = false and we have put a new person to the room
          SELECT
            @Pieces = @new_NumberOfPersons - @Old_NumberOfPersons
        ELSE
          SELECT
            @Pieces = 0
        SELECT
          @TotalPrice = 0
        SELECT
          @PriceOverWrite = 0
      END
      ELSE
      BEGIN
        IF @New_NumberOfpersons - @Old_NumberOfPersons = 0
          AND @New_Pieces - @old_Pieces = 0
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
          SELECT
            @PriceOverWrite = 0
        END
        ELSE
        BEGIN
          SELECT
            @Pieces = (@New_NumberOfPersons * @New_Pieces) - (@Old_NumberOfPersons * @Old_Pieces)
          SELECT
            @TotalPrice = 0
          SELECT
            @PriceOverWrite = 0
        END
      END
    END
  END
  ELSE
  IF @DebType = 3  --Price * Adult pax
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @Fromnamelist = 1
      BEGIN
        SELECT
          @Pieces = @New_numberofPersons
        SELECT
          @TotalPrice = @PriceoverWrite * @New_numberofPersons
      END
      ELSE
      BEGIN
        SELECT
          @pieces = @New_NumberOfpersons * @New_Pieces
        SELECT
          @TotalPrice = @New_NumberOfpersons * @PriceoverWrite * @New_Pieces
      END
    END
    ELSE
    BEGIN
      IF @FromNamelist = 1
      BEGIN
        IF @new_NumberOfPersons <> @Old_NumberOfPersons --is used if namelistreservationperperson = false and we have put a new person to the room
        BEGIN
          SELECT
            @Pieces = @new_NumberOfPersons - @Old_NumberOfPersons
          SELECT
            @TotalPrice = @PriceoverWrite * (@new_NumberOfPersons - @Old_NumberOfPersons)
        END
        ELSE
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
        END
      END
      ELSE
      BEGIN
        IF @New_NumberOfpersons - @Old_NumberOfPersons = 0
          AND @New_Pieces - @old_Pieces = 0
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
        END
        ELSE
        BEGIN
          SELECT
            @TotalPrice = ((@New_NumberOfpersons * @New_Pieces) - (@Old_NumberOfPersons * @Old_Pieces)) * @PriceOverWrite
          SELECT
            @Pieces = (@New_NumberOfPersons * @New_Pieces) - (@Old_NumberOfPersons * @Old_Pieces)
        END
      END
    END
  END
  ELSE
  IF @DebType = 4 --Fixed Price Per room /Hall (children 1)
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @FromNameList = 1
      BEGIN
        SELECT
          @Pieces = 1
        SELECT
          @TotalPrice = @PriceOverWrite
      END
      ELSE
      BEGIN
        SELECT
          @pieces = 1 * @New_Pieces
        SELECT
          @TotalPrice = @PriceoverWrite
      END
    END
    ELSE
    BEGIN
      IF @FromNameList = 1
      BEGIN
        SELECT
          @Pieces = 0
        SELECT
          @TotalPrice = 0
      END
      ELSE
      BEGIN
        SELECT
          @Pieces = @New_Pieces - @Old_Pieces
        SELECT
          @TotalPrice = 0
      END
    END
  END
  ELSE
  IF @DebType = 5   --Price * Children 1 pax
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @FromNameList = 1
      BEGIN
        SELECT
          @Pieces = @New_NumberofChildren1
        SELECT
          @TotalPrice = @PriceoverWrite * @New_NumberofChildren1
      END
      ELSE
      BEGIN
        SELECT
          @pieces = @New_NumberOfChildren1 * @New_Pieces
        SELECT
          @TotalPrice = @New_NumberOfChildren1 * @PriceoverWrite
      END
    END
    ELSE
    BEGIN
      IF @FromNameList = 1
      BEGIN
        IF @new_NumberOfChildren1 <> @Old_NumberOfChildren1 --is used if namelistreservationperperson = false and we have put a new child1 to the room
        BEGIN
          SELECT
            @Pieces = @new_NumberOfChildren1 - @Old_NumberOfchildren1
          SELECT
            @TotalPrice = @PriceoverWrite * (@new_NumberOfChildren1 - @Old_NumberOfchildren1)
        END
        ELSE
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
        END
      END
      ELSE
      BEGIN
        IF @New_NumberOfChildren1 - @Old_NumberOfChildren1 = 0
          AND @New_Pieces - @old_Pieces = 0
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
        END
        ELSE
        BEGIN
          SELECT
            @TotalPrice = ((@New_NumberOfChildren1 * @New_Pieces) - (@Old_NumberOfChildren1 * @Old_Pieces)) * @PriceOverWrite
          SELECT
            @Pieces = (@New_NumberOfChildren1 * @New_Pieces) - (@Old_NumberOfChildren1 * @Old_Pieces)
        END
      END
    END
  END
  ELSE
  IF @DebType = 6   -- % of room price /Hall (Children 1)
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @Fromnamelist = 1
        SELECT
          @pieces = @New_NumberOfChildren1
      ELSE
        SELECT
          @pieces = @New_NumberOfChildren1 * @New_Pieces
      SELECT
        @TotalPrice = 0
      SELECT
        @PriceOverWrite = 0
    END
    ELSE
    BEGIN
      IF @FromNameList = 1
      BEGIN
        IF @new_NumberOfChildren1 <> @Old_NumberOfChildren1 --is used if namelistreservationperperson = false and we have put a new Child to the room
          SELECT
            @Pieces = @new_NumberOfChildren1 - @Old_NumberOfChildren1
        ELSE
          SELECT
            @Pieces = 0
        SELECT
          @TotalPrice = 0
        SELECT
          @PriceOverWrite = 0
      END
      ELSE
      BEGIN
        IF @New_NumberOfChildren1 - @Old_NumberOfChildren1 = 0
          AND @New_Pieces - @old_Pieces = 0
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
          SELECT
            @PriceOverWrite = 0
        END
        ELSE
        BEGIN
          SELECT
            @Pieces = (@New_NumberOfChildren1 * @New_Pieces) - (@Old_NumberOfChildren1 * @Old_Pieces)
          SELECT
            @TotalPrice = 0
          SELECT
            @PriceOverWrite = 0
        END
      END
    END
  END
  ELSE
  IF @DebType = 7 --Fixed Price Per room /Hall (children 2)
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @FromNameList = 1
      BEGIN
        SELECT
          @Pieces = 1
        SELECT
          @TotalPrice = @PriceOverWrite
      END
      ELSE
      BEGIN
        SELECT
          @pieces = 1 * @New_Pieces
        SELECT
          @TotalPrice = @PriceoverWrite
      END
    END
    ELSE
    BEGIN
      IF @FromNameList = 1
      BEGIN
        SELECT
          @Pieces = 0
        SELECT
          @TotalPrice = 0
      END
      ELSE
      BEGIN
        SELECT
          @Pieces = @New_Pieces - @Old_Pieces
        SELECT
          @TotalPrice = 0
      END
    END
  END
  ELSE
  IF @DebType = 8   --Price * Children 2 pax
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @FromNameList = 1
      BEGIN
        SELECT
          @Pieces = @New_NumberOfChildren2
        SELECT
          @TotalPrice = @PriceoverWrite * @New_NumberOfChildren2
      END
      ELSE
      BEGIN
        SELECT
          @pieces = @New_NumberOfChildren2 * @New_Pieces
        SELECT
          @TotalPrice = @New_NumberOfChildren2 * @PriceoverWrite
      END
    END
    ELSE
    BEGIN
      IF @FromNameList = 1
      BEGIN
        IF @new_NumberOfChildren2 <> @Old_NumberOfchildren2 --is used if namelistreservationperperson = false and we have put a new child2 to the room
        BEGIN
          SELECT
            @Pieces = @new_NumberOfChildren2 - @Old_NumberOfChildren2
          SELECT
            @TotalPrice = @PriceoverWrite * (@new_NumberOfChildren2 - @Old_NumberOfChildren2)
        END
        ELSE
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
        END
      END
      ELSE
      BEGIN
        IF @New_NumberOfChildren2 - @Old_NumberOfChildren2 = 0
          AND @New_Pieces - @old_Pieces = 0
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
        END
        ELSE
        BEGIN
          SELECT
            @TotalPrice = ((@New_NumberOfChildren2 * @New_Pieces) - (@Old_NumberOfChildren2 * @Old_Pieces)) * @PriceOverWrite
          SELECT
            @Pieces = (@New_NumberOfChildren2 * @New_Pieces) - (@Old_NumberOfChildren2 * @Old_Pieces)
        END
      END
    END
  END
  ELSE
  IF @DebType = 9   -- % of room price /Hall (Children 2)
  BEGIN
    IF (@found = 0)
      OR (@New_ProductNumber <> @Old_ProductNumber)
      OR (@CurrentDate > @FormatedOldEnddate)
      OR (@CurrentDate < @FormatedOldStartDate)
      OR (@currentdate = @FormatedEnddate
      AND @formatedenddate < @FormatedOldEnddate)
      OR (@Currentdate = @FormatedStartDate
      AND @formatedStartdate > @formatedOldStartDate)
      OR (@CurrentDate = @FormatedOldEndDate
      AND @FormatedEnddate > @FormatedOldEnddate)
      OR (@CurrentDate = @FormatedOldStartDate
      AND @FormatedStartdate < @FormatedOldStartDate)
    BEGIN
      IF @Fromnamelist = 1
        SELECT
          @Pieces = @New_NumberOfChildren2
      ELSE
        SELECT
          @pieces = @New_NumberOfChildren2 * @New_Pieces
      SELECT
        @TotalPrice = 0
      SELECT
        @PriceOverWrite = 0
    END
    ELSE
    BEGIN
      IF @FromNameList = 1
      BEGIN
        IF @new_NumberOfChildren2 <> @Old_NumberOfChildren2 --is used if namelistreservationperperson = false and we have put a new Child2 to the room
          SELECT
            @Pieces = @new_NumberOfChildren2 - @Old_NumberOfChildren2
        ELSE
          SELECT
            @Pieces = 0
        SELECT
          @TotalPrice = 0
        SELECT
          @PriceOverWrite = 0
      END
      ELSE
      BEGIN
        IF @New_NumberOfChildren2 - @Old_NumberOfChildren2 = 0
          AND @New_Pieces - @old_Pieces = 0
        BEGIN
          SELECT
            @Pieces = 0
          SELECT
            @TotalPrice = 0
          SELECT
            @PriceOverWrite = 0
        END
        ELSE
        BEGIN
          SELECT
            @Pieces = (@New_NumberOfChildren2 * @New_Pieces) - (@Old_NumberOfChildren2 * @Old_Pieces)
          SELECT
            @TotalPrice = 0
          SELECT
            @PriceOverWrite = 0
        END
      END
    END
  END

  IF @IsNewArrangement = 0 -- Only do this if we are modifying arrangementline
  BEGIN
    IF @AUseArrangementTolerance = 1
    BEGIN
      /* If the reservation period has been reduced, we cannot alter the pieces due to "Use Arrangement tolerance" functionality */
      IF ((@FormatedOldStartDate < @FormatedStartDate) AND (@CurrentDate >= @FormatedStartDate) AND (@CurrentDate <= @FormatedEndDate)) OR
  	     ((@FormatedOldEndDate > @FormatedEndDate) AND (@CurrentDate >= @FormatedStartDate) AND (@CurrentDate <= @FormatedEndDate))
	    SELECT @Pieces = 0
	  ELSE IF ((@FormatedOldEndDate < @FormatedEndDate) AND (@CurrentDate <= @FormatedOldEndDate))
	    SELECT @Pieces = 0
	  ELSE IF ((@FormatedOldStartDate > @FormatedStartDate) AND (@CurrentDate <= @FormatedOldStartDate))
	    SELECT @Pieces = 0
	END
  END

  SET @APieces = @Pieces
  SET @APriceOverwrite = @PriceOverwrite
  SET @ATotalPrice = @TotalPrice
END