CREATE FUNCTION dbo.GetArrangementLineTextArrDep(@ADate VARCHAR(8), @AReferenceNumber INT, @AGuestNumber INT, @IncludeNote BIT, @AToDate CHAR(8) = '', @ADateFormat VARCHAR(50)) RETURNS VARCHAR (8000) AS
BEGIN  
  DECLARE @ArrLine INT
  DECLARE @ArrTime VARCHAR(5)
  DECLARE @WareText VARCHAR(255)
  DECLARE @Date CHAR(8)
  DECLARE @dummy INT
  DECLARE @curDate VARCHAR(8)
  DECLARE @Result VARCHAR (8000)
  DECLARE @Arr24HoursStarts CHAR(5)
  DECLARE @Pieces VARCHAR(5)
  DECLARE @ArrNote VARCHAR(500)

  SELECT @Arr24HoursStarts = KeyValue FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'Arr24HoursStarts'
  IF ISNULL(@Arr24HoursStarts, '') = ''
    SET @Arr24HoursStarts = '07:00'

  SET @Result = ''
  SET @curDate = ''

  IF @AGuestNumber <= 0
  BEGIN
    DECLARE GetArrLineTextCur CURSOR FAST_FORWARD READ_ONLY FOR
      SELECT DISTINCT ArrangementLines.ArrangementLineNumber,
      CASE WHEN (ISNULL(ArrangementLines.Time, '') = '') THEN '00:00' ELSE ArrangementLines.Time END AS ArrTime, ArrangementLines.WareText, ArrangementLines.Date,
      CASE WHEN CASE WHEN (ISNULL(ArrangementLines.Time, '') = '') THEN '00:00' ELSE ArrangementLines.Time END < '05:00' THEN ArrangementLines.Date ELSE ArrangementLines.Date - 1 END AS CalcStartDate,
      CAST(ArrangementLines.Pieces AS VARCHAR(5)),
      CAST(ArrangementLines.Note AS VARCHAR(500))
      FROM ArrangementLines WITH (NOLOCK)
      INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
      WHERE ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumber
      AND (
        (@AToDate = '' AND ArrangementLines.Date = @ADate) OR
        (@AToDate <> '' AND ArrangementLines.Date >= @ADate AND ArrangementLines.Date <= @AToDate)    
      )
	  ORDER BY CalcStartDate, ArrTime ASC
    OPEN GetArrLineTextCur

    FETCH NEXT FROM GetArrLineTextCur INTO @ArrLine, @ArrTime, @WareText, @Date, @dummy, @Pieces, @ArrNote

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF @Result <> ''
        SET @Result = @Result + CHAR(13) + CHAR(10)
      IF @AToDate <> '' AND @Date <> @curDate
      BEGIN
        IF @Result <> ''
          SET @Result = @Result + CHAR(13) + CHAR(10)
        SET @Result = @Result + FORMAT(CONVERT(DATETIME, @Date, 112), @ADateFormat) + CHAR(13) + CHAR(10)
      END
      
      SET @Result = @Result + @ArrTime + '. Pcs.: ' + @Pieces + ' : ' + @WareText 
      IF @IncludeNote = 1 AND ISNULL(@ArrNote, '') <> ''
        SET @Result = @Result + CHAR(13) + CHAR(10) + ISNULL(@ArrNote, '')
      SET @curDate = @Date
      FETCH NEXT FROM GetArrLineTextCur INTO @ArrLine, @ArrTime, @WareText, @Date, @dummy, @Pieces, @ArrNote
    END
    CLOSE GetArrLineTextCur
    DEALLOCATE GetArrLineTextCur
  END
  ELSE
  BEGIN
    DECLARE GetArrLineTextCur CURSOR FAST_FORWARD READ_ONLY FOR
      SELECT ArrangementLines.ArrangementLineNumber,
      CASE WHEN (ISNULL(ArrangementLines.Time, '') = '') THEN '00:00' ELSE ArrangementLines.Time END, ArrangementLines.WareText, ArrangementLines.Date,
      CASE WHEN CASE WHEN (ISNULL(ArrangementLines.Time, '') = '') THEN '00:00' ELSE ArrangementLines.Time END < '05:00' THEN ArrangementLines.Date ELSE ArrangementLines.Date - 1 END AS CalcStartDate,
      CAST(DBO.GetArrPax(ArrangementLines.Arrangementlinenumber, @AReferenceNumber, @AGuestNumber, 0) AS VARCHAR(5)) AS Pieces,
      CAST(ArrangementLines.Note AS VARCHAR(500))
      FROM ArrangementLines WITH (NOLOCK)
      INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
      WHERE ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumber
        AND ArrangementReservationsXtab.GuestNumber = @AGuestNumber
        AND (
          (@AToDate = '' AND ArrangementLines.Date = @ADate) OR
          (@AToDate <> '' AND ArrangementLines.Date >= @ADate AND ArrangementLines.Date <= @AToDate)    
      )
      ORDER BY CalcStartDate, ArrangementLines.Time ASC
    OPEN GetArrLineTextCur

    FETCH NEXT FROM GetArrLineTextCur INTO @ArrLine, @ArrTime, @WareText, @Date, @dummy, @Pieces, @ArrNote

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      IF @Result <> ''
        SET @Result = @Result + CHAR(13) + CHAR(10)
      IF @AToDate <> '' AND @Date <> @curDate
      BEGIN
        IF @Result <> ''
          SET @Result = @Result + CHAR(13) + CHAR(10)
        SET @Result = @Result + FORMAT(CONVERT(DATETIME, @Date, 112), @ADateFormat) + CHAR(13) + CHAR(10)
      END
      
      SET @Result = @Result + @ArrTime + '. Pcs.: ' + @Pieces + ' : ' + @WareText 
      IF @IncludeNote = 1 AND ISNULL(@ArrNote, '') <> ''
        SET @Result = @Result + CHAR(13) + CHAR(10) + ISNULL(@ArrNote, '')

      SET @curDate = @Date
      FETCH NEXT FROM GetArrLineTextCur INTO @ArrLine, @ArrTime, @WareText, @Date, @dummy, @Pieces, @ArrNote
    END
    CLOSE GetArrLineTextCur
    DEALLOCATE GetArrLineTextCur
  END
  RETURN @Result  
END