CREATE PROCEDURE [dbo].[UpdateDetailedReservationLog] @AReferenceNumber INT, @AGuestNumber INT, @AFollowupNumber INT AS

/*
  Created by Kent Johansen march 2010
  Changes: 
  KJ 20210126: Added reservation info
  AMU 20200824: CR197897: Fixed a couple of errors in room lines and totals
  RN 20170621: Extended @s from 8000 to varchar(max) character so we can log very large reservations
  KJ 20170119: Ext: Added split bill info
  KJ 20160304: Bug: Fix for "The data types text and varchar are incompatible in the not equal to operator.".
  KJ 20160125: Reverted changes from rev 18609. Note field changed back to datatype text.
  BA 20151023: CR093932/Adding Currency Code for debugging.
  KJ 20151002: Added Priceoverwrite
  RN 20141222: Fixed missing halllines (CR076230)
  KJ 20141204: Added Room discount
  20140710 RN: VARCHAR prices changed to VARCHAR(max) to accomodate Iceland's prices.
  Brian Andersen, April 2014: Changes to PaymentAfterMonth and PaymentAfterDays so it is
                              registred for the guest, company, agent and agency.
  Brian Andersen, Februar 2014: Added PaymentAfterMonth and PaymentAfterDays (CR069244). It
                                always chooses the Guest address!
  Kåre Christensen, Januar 2012: Added method of payment
  Kåre Christensen, November 2011: Added log information regarding room lines, name list and notes.

*/

SET NOCOUNT ON

DECLARE @s VARCHAR(MAX)
DECLARE @Ptr BINARY(16)
DECLARE @n INT
DECLARE @FOUND BIT
DECLARE @IgnoreGuestNumber BIT

DECLARE @Arr24HoursStarts CHAR(5)
DECLARE @TableBookingEnabled BIT
DECLARE @Type INT
DECLARE @TypeStr VARCHAR(15)
DECLARE @HotelName VARCHAR(30)
DECLARE @Status INT
DECLARE @StatusStr VARCHAR(15)
DECLARE @MethodOfPayment VARCHAR(50)
DECLARE @Segment VARCHAR(50)
DECLARE @PointOfSale VARCHAR(50)

DECLARE @GuestPaymentAfterMonth VARCHAR(5)
DECLARE @GuestPaymentAfterDays VARCHAR(5)
DECLARE @CompanyPaymentAfterMonth VARCHAR(5)
DECLARE @CompanyPaymentAfterDays VARCHAR(5)
DECLARE @AgentPaymentAfterMonth VARCHAR(5)
DECLARE @AgentPaymentAfterDays VARCHAR(5)
DECLARE @AgencyPaymentAfterMonth VARCHAR(5)
DECLARE @AgencyPaymentAfterDays VARCHAR(5)

DECLARE @GuestDiscountRoom MONEY
DECLARE @CompanyDiscountRoom MONEY
DECLARE @AgentDiscountRoom MONEY
DECLARE @AgencyDiscountRoom MONEY

DECLARE @HallCount INT
DECLARE @HallAdults VARCHAR(10)
DECLARE @HallChild1 VARCHAR(10)
DECLARE @HallChild2 VARCHAR(10)

DECLARE @TableCount INT
DECLARE @TableAdults INT
DECLARE @TableChild1 INT
DECLARE @TableChild2 INT

DECLARE @RoomCount  INT
DECLARE @RoomCountTotal INT
DECLARE @RoomCountStr  VARCHAR(10)
DECLARE @GuestNumber VARCHAR(10)
DECLARE @RoomNumber VARCHAR(10)
DECLARE @RoomType VARCHAR(7)
DECLARE @RoomTypeLine VARCHAR(7)
DECLARE @RoomAdults VARCHAR(10)
DECLARE @RoomAdultsTotal INT
DECLARE @RoomChild1 VARCHAR(10)
DECLARE @RoomChild1Total INT
DECLARE @RoomChild2 VARCHAR(10)
DECLARE @RoomChild2Total INT
DECLARE @RoomPaysAdults VARCHAR(10)
DECLARE @RoomPaysChild1 VARCHAR(10)
DECLARE @RoomPaysChild2 VARCHAR(10)
DECLARE @RoomArrival VARCHAR(8)
DECLARE @RoomArrivalMin INT
DECLARE @RoomArrivalTime VARCHAR(5)
DECLARE @RoomArrivalTimeMin VARCHAR(5)
DECLARE @RoomDeparture VARCHAR(8)
DECLARE @RoomDepartureMax INT
DECLARE @RoomDepartureTime VARCHAR(5)
DECLARE @RoomDepartureTimeMax VARCHAR(5)
DECLARE @NumberOfDays VARCHAR(10)
DECLARE @RoomProduct VARCHAR(15)
DECLARE @RoomPrice VARCHAR(max)
DECLARE @RoomPriceTotal MONEY
DECLARE @RoomTotalPrice VARCHAR(max)
DECLARE @RoomTotalPriceTotal MONEY
DECLARE @Lastname VARCHAR(50)
DECLARE @Firstname VARCHAR(50)
DECLARE @CountryCode VARCHAR(3)
DECLARE @PO BIT
DECLARE @PO2 BIT
DECLARE @PO3 BIT
DECLARE @PO4 BIT

DECLARE @HallType VARCHAR(7)
DECLARE @HallPieces VARCHAR(10)
DECLARE @HallNumber VARCHAR(10)
DECLARE @HallArrival VARCHAR(10)
DECLARE @HallArrivalTime VARCHAR(8)
DECLARE @HallDeparture VARCHAR(10)
DECLARE @HallDepartureTime VARCHAR(8)
DECLARE @TableSetupNumber VARCHAR(10)
DECLARE @TableSetup VARCHAR(15)
DECLARE @HallProduct VARCHAR(15)
DECLARE @HallPacket VARCHAR(15)
DECLARE @HallPrice VARCHAR(15)	
DECLARE @HallTotalPrice VARCHAR(15)

DECLARE @ArrangementLineNumber INT
DECLARE @Departments VARCHAR(37)
DECLARE @Date VARCHAR(8)
DECLARE @Time VARCHAR(8)
DECLARe @ArrDepTime VARCHAR(8)
DECLARE @ArrPieces VARCHAR(5)
DECLARE @GroupSortNumber VARCHAR(5)
DECLARE @WareSortNumber VARCHAR(5)
DECLARE @WareText VARCHAR(45)
DECLARE @ArrPrice VARCHAR(max)
DECLARE @ArrTotalPrice VARCHAR(max)
DECLARE @ExDepartment VARCHAR(1)
DECLARE @PCDepartment VARCHAR(1)
DECLARe @ArrHallNumber VARCHAR(5)
DECLARE @PlaceText VARCHAR(15)
DECLARE @OrderCol DATETIME
DECLARE @ArrNote VARCHAR(max)

DECLARE @NotePage VARCHAR(2)
DECLARE @NoteText VARCHAR(255)

DECLARE @CurrentDate VARCHAR(8)

DECLARE @CurrencyCode VARCHAR(5)
DECLARE @CurrencyRate money
DECLARE @ResCountryCode VARCHAR(3)

DECLARE @SplitBillName VARCHAR(100)
DECLARE @SplitBillName1 VARCHAR(100)
DECLARE @SplitBillName2 VARCHAR(100)
DECLARE @SplitBillName3 VARCHAR(100)
DECLARE @SplitBillName4 VARCHAR(100)

DECLARE @CopyCustomerFlagDToMemberCardNumber BIT
DECLARE @GuestContactCustomerName VARCHAR(50)
DECLARE @CompanyContactCustomerName VARCHAR(50)
DECLARE @InternalCourseProduction BIT
DECLARE @MainCourseRefNo INT

SELECT @Arr24HoursStarts = KeyValue FROM HotelSetup (NOLOCK) WHERE  SearchKey = 'Arr24HoursStarts'
SELECT @TableBookingEnabled = CONVERT(BIT, KeyValue) FROM HotelSetUp (NOLOCK) WHERE SearchKey = 'TableBookingEnabled'
SELECT @CopyCustomerFlagDToMemberCardNumber = KeyValue FROM HotelSetup (NOLOCK) WHERE  SearchKey = 'CopyCustomerFlagDToMemberCardNumber'
SELECT @InternalCourseProduction = KeyValue FROM HotelSetup (NOLOCK) WHERE SearchKey = 'InternalCourseProduction'

SELECT @Type = ReservationType, @HotelName = LASTNAME FROM ReservationMain(NOLOCK) 
LEFT OUTER JOIN CUSTOMER (NOLOCK) on RESERVATIONMAIN.HOTELNUMBER = CUSTOMER.HotelNumber
WHERE ReferenceNumber = @AReferenceNumber

IF @Type = 0 SET @TypeStr = 'Single'
ELSE IF @Type = 1 SET @TypeStr = 'Group'
ELSE IF @Type = 2 SET @TypeStr = 'WalkIn'
ELSE IF @Type = 3 SET @TypeStr = 'Conference'
ELSE IF @Type = 4 SET @TypeStr = 'Meeting'
ELSE IF @Type = 5 SET @TypeStr = 'DayRoom'
ELSE IF @Type = 6 SET @TypeStr = 'Other1'
ELSE IF @Type = 7 SET @TypeStr = 'Other2'
ELSE IF @Type = 8 SET @TypeStr = 'None'

SELECT @Status = Status FROM Reservations(NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND GuestNumber = @AGuestNumber
IF @Status = 0 SET @StatusStr = 'Confirmed'
ELSE IF @Status = 1 SET @StatusStr = 'Provisional'
ELSE IF @Status = 2 SET @StatusStr = 'Guaranteed'
ELSE IF @Status = 3 SET @StatusStr = 'Waitinglist'
ELSE IF @Status = 4 SET @StatusStr = 'Tentative'
ELSE IF @Status = 5 SET @StatusStr = 'Inhouse'
ELSE IF @Status = 6 SET @StatusStr = 'OutOfHouse'
ELSE IF @Status = 7 SET @StatusStr = 'Cancelled'
ELSE IF @Status = 8 SET @StatusStr = 'NoShow'

SELECT @HallCount = SUM(ISNULL(NumberOfRooms, 0)), @HallAdults = SUM(ISNULL(Adults, 0)), @HallChild1 = SUM(ISNULL(Children1, 0)), @HallChild2 = SUM(ISNULL(Children2, 0)) 
FROM ReservationConferenceLine (NOLOCK)
WHERE ReferenceNumber = @AReferenceNumber AND ISNULL(TableBooking,0) = 0

SET @HallCount = ISNULL(@HallCount, 0)
SET @HallAdults = ISNULL(@HallAdults, 0)
SET @HallChild1 = ISNULL(@HallChild1, 0)
SET @HallChild2 = ISNULL(@HallChild2, 0)

IF @TableBookingEnabled = 1
BEGIN
  SELECT @TableCount = SUM(NumberOfRooms), @TableAdults = SUM(Adults), @TableChild1 = SUM(Children1), @TableChild2 = SUM(Children2) 
  FROM ReservationConferenceLine (NOLOCK)
  WHERE ReferenceNumber = @AReferenceNumber AND ISNULL(TableBooking, 0) = 1

  SET @TableCount = ISNULL(@TableCount, 0)
  SET @TableAdults = ISNULL(@TableAdults, 0)
  SET @TableChild1 = ISNULL(@TableChild1, 0)
  SET @TableChild2 = ISNULL(@TableChild2, 0)
END

IF @AGuestNumber = 0
	SET @IgnoreGuestNumber = 1
else
	SET @IgnoreGuestNumber = 0
	
SELECT @RoomCount = SUM(NumberOfRooms), @RoomAdults = SUM(Adults*NumberOfRooms), @RoomChild1 = SUM(Children1*NumberOfRooms), @RoomChild2 = SUM(Children2*NumberOfRooms) 
FROM ReservationRoomLine (NOLOCK)
WHERE LineNumber IN (SELECT LineNumber FROM ReservationRoomLineXTab (NOLOCK) WHERE ReferenceNumber = @AReferenceNumber AND (GuestNumber = @AGuestNumber OR @IgnoreGuestNumber = 1)) 

SET @RoomCount = ISNULL(@RoomCount, 0)
SET @RoomAdults = ISNULL(@RoomAdults, 0)
SET @RoomChild1 = ISNULL(@RoomChild1, 0)
SET @RoomChild2 = ISNULL(@RoomChild2, 0)

SELECT @MethodOfPayment = W.WareName, @Segment = S.Text, @PointOfSale = P.Text, @CurrencyCode = ISNULL(C.CurrencyCode, '<NA>'), @CurrencyRate = LastCurrencyRate, @ResCountryCode = ISNULL(C.CountryCode, '<NA>'),
@SplitBillName = ISNULL(sb.Name, ''), @SplitBillName1 = ISNULL(sb1.Name, ''), @SplitBillName2 = ISNULL(sb2.Name, ''), @SplitBillName3 = ISNULL(sb3.Name, ''), @SplitBillName4 = ISNULL(sb4.Name, '')
FROM RESERVATIONS R (NOLOCK)
LEFT OUTER JOIN Ware W (NOLOCK) ON R.METHODOFPAYMENT = W.WareNumber
LEFT OUTER JOIN SEGMENTS S (NOLOCK) ON S.NUMBER = R.MARKETSEGMENT
LEFT OUTER JOIN POINTOFSALES P (NOLOCK) ON P.NUMBER = R.POINTOFSALE  
LEFT OUTER JOIN Countries C (NOLOCK) ON R.Currency = C.CountryNumber
LEFT OUTER JOIN SplitBill sb (NOLOCK) ON sb.SplitBillNumber = R.SPLITBILL
LEFT OUTER JOIN SplitBill sb1 (NOLOCK) ON sb.SplitBillNumber = R.SplitBillNumber1
LEFT OUTER JOIN SplitBill sb2 (NOLOCK) ON sb.SplitBillNumber = R.SplitBillNumber2
LEFT OUTER JOIN SplitBill sb3 (NOLOCK) ON sb.SplitBillNumber = R.SplitBillNumber3
LEFT OUTER JOIN SplitBill sb4 (NOLOCK) ON sb.SplitBillNumber = R.SplitBillNumber4
WHERE R.ReferenceNumber = @AReferenceNumber AND R.GuestNumber = @AGuestNumber 

SELECT @GuestPaymentAfterMonth = CAST(RA.PaymentAfterMonth AS VARCHAR(5)), @GuestPaymentAfterDays = CAST(RA.PaymentAfterDays AS VARCHAR(5)), @GuestDiscountRoom = RA.RoomDiscountRate
FROM RESERVATIONADDRESSXTAB RAX WITH (NOLOCK)
INNER JOIN RESERVATIONADDRESS RA WITH (NOLOCK)
  ON RAX.ADDRESSNUMBER = RA.AddressNumber AND RA.ADDRESSTYPE = 1 -- Guest
WHERE RAX.REFERENCENUMBER = @AReferenceNumber AND RAX.GUESTNUMBER = @AGuestNumber

SELECT @CompanyPaymentAfterMonth = CAST(RA.PaymentAfterMonth AS VARCHAR(5)), @CompanyPaymentAfterDays = CAST(RA.PaymentAfterDays AS VARCHAR(5)), @CompanyDiscountRoom = RA.RoomDiscountRate
FROM RESERVATIONADDRESSXTAB RAX WITH (NOLOCK)
INNER JOIN RESERVATIONADDRESS RA WITH (NOLOCK)
  ON RAX.ADDRESSNUMBER = RA.AddressNumber AND RA.ADDRESSTYPE = 2 -- Company
WHERE RAX.REFERENCENUMBER = @AReferenceNumber AND RAX.GUESTNUMBER = @AGuestNumber

SELECT @AgencyPaymentAfterMonth = CAST(RA.PaymentAfterMonth AS VARCHAR(5)), @AgencyPaymentAfterDays = CAST(RA.PaymentAfterDays AS VARCHAR(5)), @AgencyDiscountRoom = RA.RoomDiscountRate
FROM RESERVATIONADDRESSXTAB RAX WITH (NOLOCK)
INNER JOIN RESERVATIONADDRESS RA WITH (NOLOCK)
  ON RAX.ADDRESSNUMBER = RA.AddressNumber AND RA.ADDRESSTYPE = 3 -- Agency
WHERE RAX.REFERENCENUMBER = @AReferenceNumber AND RAX.GUESTNUMBER = @AGuestNumber

SELECT @AgentPaymentAfterMonth = CAST(RA.PaymentAfterMonth AS VARCHAR(5)), @AgentPaymentAfterDays = CAST(RA.PaymentAfterDays AS VARCHAR(5)), @AgentDiscountRoom = RA.RoomDiscountRate
FROM RESERVATIONADDRESSXTAB RAX WITH (NOLOCK)
INNER JOIN RESERVATIONADDRESS RA WITH (NOLOCK)
  ON RAX.ADDRESSNUMBER = RA.AddressNumber AND RA.ADDRESSTYPE = 4 -- Agent
WHERE RAX.REFERENCENUMBER = @AReferenceNumber AND RAX.GUESTNUMBER = @AGuestNumber

SET @GuestContactCustomerName = ''
SET @CompanyContactCustomerName = ''
IF @CopyCustomerFlagDToMemberCardNumber = 1
BEGIN
  SELECT @GuestContactCustomerName = Customer.FirstName + ' ' + Customer.LASTNAME
  FROM RESERVATIONADDRESSXTAB RAX WITH (NOLOCK)
  INNER JOIN RESERVATIONADDRESS RA WITH (NOLOCK)
    ON RAX.ADDRESSNUMBER = RA.AddressNumber AND RA.ADDRESSTYPE = 1 -- meetingOwner for DI
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = RA.ContactpersonCustomerNumber
  WHERE RAX.REFERENCENUMBER = @AReferenceNumber AND RAX.GUESTNUMBER = @AGuestNumber

  SET @GuestContactCustomerName = dbo.GetStringFixedLength(@GuestContactCustomerName, 50)

  SELECT @CompanyContactCustomerName = Customer.FirstName + ' ' + Customer.LASTNAME
  FROM RESERVATIONADDRESSXTAB RAX WITH (NOLOCK)
  INNER JOIN RESERVATIONADDRESS RA WITH (NOLOCK)
    ON RAX.ADDRESSNUMBER = RA.AddressNumber AND RA.ADDRESSTYPE = 2 -- meetingContact for DI
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = RA.ContactpersonCustomerNumber
  WHERE RAX.REFERENCENUMBER = @AReferenceNumber AND RAX.GUESTNUMBER = @AGuestNumber
  SET @CompanyContactCustomerName = dbo.GetStringFixedLength(@CompanyContactCustomerName, 50)
END

IF @InternalCourseProduction = 1
BEGIN
  SELECT @MainCourseRefNo = FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumberSub = @AReferenceNumber
END

SET @MethodOfPayment = ISNULL(@MethodOfPayment,'')
SET @Segment = ISNULL(@Segment,'')
SET @PointOfSale = ISNULL(@PointOfSale,'')

SET @GuestPaymentAfterMonth = ISNULL(@GuestPaymentAfterMonth,'0')
SET @GuestPaymentAfterDays = ISNULL(@GuestPaymentAfterDays,'0')
SET @CompanyPaymentAfterMonth = ISNULL(@CompanyPaymentAfterMonth,'0')
SET @CompanyPaymentAfterDays = ISNULL(@CompanyPaymentAfterDays,'0')
SET @AgencyPaymentAfterMonth = ISNULL(@AgencyPaymentAfterMonth,'0')
SET @AgencyPaymentAfterDays = ISNULL(@AgencyPaymentAfterDays,'0')
SET @AgentPaymentAfterMonth = ISNULL(@AgentPaymentAfterMonth,'0')
SET @AgentPaymentAfterDays = ISNULL(@AgentPaymentAfterDays,'0')

SET @GuestDiscountRoom = ISNULL(@GuestDiscountRoom, 0)
SET @CompanyDiscountRoom = ISNULL(@CompanyDiscountRoom, 0)
SET @AgencyDiscountRoom = ISNULL(@AgencyDiscountRoom, 0)
SET @AgentDiscountRoom = ISNULL(@AgentDiscountRoom, 0)

SET @s = ' Status=' + @StatusStr + CHAR(13)
SET @s = @s + 'ReservationMain: Type=' + @TypeStr + CHAR(13)
SET @s = @s + 'ReservationMain: Hotel name=' + @HotelName + CHAR(13)
SET @s = @s + 'Method of payment: ' + @MethodOfPayment + CHAR(13)

SET @s = @s + 'Guest payment terms Month: ' + @GuestPaymentAfterMonth + ' Days: ' + @GuestPaymentAfterDays + ' R diccount: ' + CAST(@GuestDiscountRoom AS VARCHAR(50)) + CHAR(13)
SET @s = @s + 'Company payment terms Month: ' + @CompanyPaymentAfterMonth + ' Days: ' + @CompanyPaymentAfterDays + ' R diccount: ' + CAST(@CompanyDiscountRoom AS VARCHAR(50)) + CHAR(13)
SET @s = @s + 'Agency payment terms Month: ' + @AgencyPaymentAfterMonth + ' Days: ' + @AgencyPaymentAfterDays + ' R diccount: ' + CAST(@AgencyDiscountRoom AS VARCHAR(50)) + CHAR(13)
SET @s = @s + 'Agent payment terms Month: ' + @AgentPaymentAfterMonth + ' Days: ' + @AgentPaymentAfterDays + ' R diccount: ' + CAST(@AgentDiscountRoom AS VARCHAR(50)) + CHAR(13)

SET @s = @s + 'Segment: ' + @Segment + CHAR(13)
SET @s = @s + 'Point of sales: ' + @PointOfSale + CHAR(13) 

SET @s = @s + 'Country code: ' + @ResCountryCode + CHAR(13)
SET @s = @s + 'Currency code: ' + @CurrencyCode + CHAR(13)
SET @s = @s + 'Currency rate: ' + CAST(@CurrencyRate AS VARCHAR(20)) + CHAR(13)
SET @s = @s + 'Split Bill: ' + @SplitBillName + CHAR(13)
SET @s = @s + 'Split Bill 1: ' + @SplitBillName1 + CHAR(13)
SET @s = @s + 'Split Bill 2: ' + @SplitBillName2 + CHAR(13)
SET @s = @s + 'Split Bill 3: ' + @SplitBillName3 + CHAR(13)
SET @s = @s + 'Split Bill 4: ' + @SplitBillName4 + CHAR(13)

IF @CopyCustomerFlagDToMemberCardNumber = 1
BEGIN
  SET @s = @s + 'Meeting owner: ' + @GuestContactCustomerName + CHAR(13)
  SET @s = @s + 'Meeting contact: ' + @CompanyContactCustomerName + CHAR(13)
END

IF @InternalCourseProduction = 1
BEGIN
  SET @s = @s + 'Main Course refno: ' + CAST(@MainCourseRefNo AS VARCHAR(50)) + CHAR(13)
END

SET @s = @s + 'SQL Time: ' + CONVERT(VARCHAR(50), GETDATE(), 105) + '  ' + CONVERT(VARCHAR(5), GETDATE(), 108) --WRITE Real time in the top of the log
SET @s = @s + CHAR(13) + CHAR(13)
SET @s = @s + 'Halls: ' + CONVERT(VARCHAR(3), @HallCount) + ' Adults: ' + CONVERT(VARCHAR(3), @HallAdults) + ' Child1: ' + CONVERT(VARCHAR(3), @HallChild1) + ' Child2: ' + CONVERT(VARCHAR(3), @HallChild2)
SET @s = @s + CHAR(13)

IF @TableBookingEnabled = 1
BEGIN
  SET @s = @s + 'Tables: ' + CONVERT(VARCHAR(3), @TableCount) + ' Adults: ' + CONVERT(VARCHAR(3), @TableAdults) + ' Child1: ' + CONVERT(VARCHAR(3), @TableChild1) + ' Child2: ' + CONVERT(VARCHAR(3), @TableChild2)
  SET @s = @s + CHAR(13)
END

SET @s = @s + 'Rooms: ' + CONVERT(VARCHAR(3), @RoomCount) + ' Adults: ' + CONVERT(VARCHAR(3), @RoomAdults) + ' Child1: ' + CONVERT(VARCHAR(3), @RoomChild1) + ' Child2: ' + CONVERT(VARCHAR(3), @RoomChild2)

SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s

SET @RoomCountTotal = 0
SET @RoomArrivalMin = 99999999
SET @RoomDepartureMax = 0
SET @RoomAdultsTotal = 0
SET @RoomChild1Total = 0
SET @RoomChild2Total = 0
SET @RoomPriceTotal = 0
SET @RoomTotalPriceTotal = 0
SET @FOUND = 0

Declare RoomCur Cursor Fast_Forward Read_Only FOR
	SELECT DISTINCT RESERVATIONS.Status, RT.Type, RL.Type, ReservationRoomLine.NumberOfRooms, CONVERT(VARCHAR(8), ReservationRoomLine.ArrivalDate, 112), ReservationRoomLine.ArrivalTime, 
	CONVERT(VARCHAR(8), ReservationRoomLine.DepartureDate, 112), ReservationRoomLine.DepartureTime,
	Rooms.Number, ReservationRoomLine.Adults, ReservationRoomLine.Children1, ReservationRoomLine.Children2, PRODUCTS.ProductName, 
	CAST(ReservationRoomLine.Price AS VARCHAR), CAST(ReservationRoomLine.TotalPrice AS VARCHAR), 
  ReservationRoomLine.PriceOverwritten, ReservationRoomLine.PriceOverwritten2, ReservationRoomLine.PriceOverwritten3, ReservationRoomLine.PriceOverwritten4
	FROM ReservationRoomLine (NOLOCK)
	LEFT OUTER JOIN ROOMS(NOLOCK) ON ReservationRoomLine.RoomNumber = Rooms.RoomNumber
	LEFT OUTER JOIN ROOMTYPES RL(NOLOCK) ON ReservationRoomLine.BookedTypeNumber = RL.RoomTypeNumber
	LEFT OUTER JOIN ROOMTYPES RT(NOLOCK) ON Rooms.RoomTypeNumber = RT.RoomTypeNumber
	LEFT OUTER JOIN PRODUCTS(NOLOCK) ON ReservationRoomLine.ProductNumber = Products.ProductNumber
	LEFT OUTER JOIN RESERVATIONROOMLINEXTAB(NOLOCK) ON ReservationRoomLine.LineNumber = RESERVATIONROOMLINEXTAB.LineNumber
	LEFT OUTER JOIN RESERVATIONS(NOLOCK) ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber
	WHERE RESERVATIONROOMLINEXTAB.ReferenceNumber = @AReferenceNumber AND (RESERVATIONROOMLINEXTAB.GuestNumber = @AGuestNumber OR @IgnoreGuestNumber = 1)
	
Open RoomCur

FETCH NEXT FROM RoomCur INTO
@Status, @RoomType, @RoomTypeLine, @RoomCountStr, @RoomArrival, @RoomArrivalTime, 
  @RoomDeparture, @RoomDepartureTime, 
  @RoomNumber ,@RoomAdults, @RoomChild1, @RoomChild2, @RoomProduct, @RoomPrice, @RoomTotalPrice, @PO, @PO2, @PO3, @PO4
  
IF @@FETCH_STATUS = 0
	SET @FOUND = 1
  
IF @FOUND = 1
BEGIN
	SET @s = CHAR(13) + CHAR(13)

	SET @s = @s + 'ROOMS'
	SET @s = @s + CHAR(13)
	SET @s = @s + 'St    Allo Book Ps Arrival  Time  Depart   Time  Room  Ad C1 C2 Product  Price   Total PO PO2 PO3 PO4'

	SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
	UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s
END
  
WHILE (@@Fetch_Status=0)
BEGIN
	IF @Status = 0 SET @StatusStr = 'Confirmed'
	ELSE IF @Status = 1 SET @StatusStr = 'Provisional'
	ELSE IF @Status = 2 SET @StatusStr = 'Guaranteed'
	ELSE IF @Status = 3 SET @StatusStr = 'Waitinglist'
	ELSE IF @Status = 4 SET @StatusStr = 'Tentative'
	ELSE IF @Status = 5 SET @StatusStr = 'Inhouse'
	ELSE IF @Status = 6 SET @StatusStr = 'OutOfHouse'
	ELSE IF @Status = 7 SET @StatusStr = 'Cancelled'
	ELSE IF @Status = 8 SET @StatusStr = 'NoShow'
	
	SET @StatusStr = dbo.GetStringFixedLength(@StatusStr ,5)
	
	SET @RoomType = dbo.GetStringFixedLength(@RoomType,4)
	
	IF @RoomType = @RoomTypeLine 
		SET @RoomTypeLine = ''
	SET @RoomTypeLine = dbo.GetStringFixedLength(@RoomTypeLine,4)
		
	SET @RoomCountStr = dbo.GetStringFixedLength(@RoomCountStr,2)
	SET @RoomArrival = dbo.GetStringFixedLength(@RoomArrival,8)
	SET @RoomArrivalTime = dbo.GetStringFixedLength(@RoomArrivalTime,5)
	SET @RoomDeparture = dbo.GetStringFixedLength(@RoomDeparture,8)
	SET @RoomDepartureTime = dbo.GetStringFixedLength(@RoomDepartureTime,5)
	SET @RoomNumber = dbo.GetStringFixedLength(@RoomNumber, 5)
	SET @RoomAdults = dbo.GetStringFixedLength(@RoomAdults,2)
	SET @RoomChild1 = dbo.GetStringFixedLength(@RoomChild1,2)
	SET @RoomChild2 = dbo.GetStringFixedLength(@RoomChild2,2)
	SET @RoomProduct = dbo.GetStringFixedLength(@RoomProduct,7)
	SET @RoomPrice = CONVERT(VARCHAR, @RoomPrice)
	SET @RoomTotalPrice = CONVERT(VARCHAR, @RoomTotalPrice)	
	
	SET @RoomCount = CAST(@RoomCountStr AS INT)
	SET @RoomCountTotal = @RoomCountTotal + @RoomCount
	IF CAST(@RoomArrival as INT) < @RoomArrivalMin 
	BEGIN
		SET @RoomArrivalMin = CAST(@RoomArrival as INT)
		SET @RoomArrivalTimeMin = @RoomArrivalTime 
	END
	
	IF CAST(@RoomDeparture  as INT) > @RoomDepartureMax 
	BEGIN
		SET @RoomDepartureMax  = CAST(@RoomDeparture as INT)
		SET @RoomDepartureTimeMax  = @RoomDepartureTime 
	END
	
	SET @RoomAdultsTotal = @RoomAdultsTotal + CAST(@RoomAdults as INT) * @RoomCount
	SET @RoomChild1Total = @RoomChild1Total + CAST(@RoomChild1 as INT) * @RoomCount
	SET @RoomChild2Total = @RoomChild2Total + CAST(@RoomChild2 as INT) * @RoomCount
	SET @RoomPriceTotal = @RoomPriceTotal + CAST(@RoomPrice AS MONEY)
	SET @RoomTotalPriceTotal = @RoomTotalPriceTotal + CAST(@RoomTotalPrice AS MONEY)
	
	SET @s = CHAR(13) + @StatusStr + ' ' + @RoomType + ' ' + @RoomTypeLine + ' ' + @RoomCountStr + ' ' + @RoomArrival + ' ' + @RoomArrivalTime + ' ' + @RoomDeparture + ' ' + 
    @RoomDepartureTime + ' ' + @RoomNumber + ' ' + @RoomAdults + ' ' + @RoomChild1 + ' ' + @RoomChild2 + ' ' + @RoomProduct + ' ' + ' ' + @RoomPrice + ' ' + @RoomTotalPrice + ' ' +
    CONVERT(CHAR(2), ISNULL(@PO,0)) + ' ' + CONVERT(CHAR(3), ISNULL(@PO2,0)) + ' ' + CONVERT(CHAR(3), ISNULL(@PO3,0)) + ' ' + CONVERT(CHAR(3), ISNULL(@PO4,0))
	
	SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
	UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s	
	
	FETCH NEXT FROM RoomCur INTO
		@Status, @RoomType, @RoomTypeLine, @RoomCountStr, @RoomArrival, @RoomArrivalTime, 
		@RoomDeparture, @RoomDepartureTime, 
		@RoomNumber,@RoomAdults, @RoomChild1, @RoomChild2, @RoomProduct, @RoomPrice, @RoomTotalPrice, @PO, @PO2, @PO3, @PO4
END 

CLOSE RoomCur
DEALLOCATE RoomCur

IF @FOUND = 1
BEGIN
	SET @s = CHAR(13) + CHAR(13) + 
		dbo.GetStringFixedLength('',16) + 
		dbo.GetStringFixedLength(CAST(@RoomCountTotal AS VARCHAR(3)),2) + ' ' +
		dbo.GetStringFixedLength(CAST(@RoomArrivalMin as VARCHAR(8)),8) + ' ' +
		dbo.GetStringFixedLength(@RoomArrivalTimeMin,5) + ' ' +
		dbo.GetStringFixedLength(CAST(@RoomDepartureMax as VARCHAR(8)),8) + ' ' +
		dbo.GetStringFixedLength(@RoomDepartureTimeMax,5) +
		dbo.GetStringFixedLength('',7) + 
		dbo.GetStringFixedLength(CAST(@RoomAdultsTotal AS VARCHAR(3)),2) + ' ' +
		dbo.GetStringFixedLength(CAST(@RoomChild1Total AS VARCHAR(3)),2) + ' ' +
		dbo.GetStringFixedLength(CAST(@RoomChild2Total AS VARCHAR(3)),2) + 
		dbo.GetStringFixedLength('',10) +
		dbo.GetStringFixedLength(CAST(@RoomPriceTotal as VARCHAR(max)),7) + ' ' +
		dbo.GetStringFixedLength(CAST(@RoomTotalPriceTotal as VARCHAR(max)),8)
		
	SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
	UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s	
END

IF @AGuestNumber = 0
BEGIN
	SET @RoomArrivalMin = 99999999
	SET @RoomDepartureMax = 0
	SET @RoomAdultsTotal = 0
	SET @RoomChild1Total = 0
	SET @RoomChild2Total = 0
	SET @RoomPriceTotal = 0
	SET @RoomTotalPriceTotal = 0	
	SET @FOUND = 0

	Declare NameCur Cursor Fast_Forward Read_Only FOR
		SELECT R.GuestNumber, R.Status, RA.Lastname, RA.FirstName, RA.CountryCode,RTR.Type,RT.Type,R.ArrivalDate,R.ArrivalTime,R.DepartureDate,R.DepartureTime,
		RO.Number, R.Adults, R.Children1, R.Children2,R.PaysAdults, R.PaysChildren1, R.PaysChildren2, P.ProductName, CAST(R.Price AS VARCHAR(max)), CAST(R.TotalPrice AS VARCHAR(max)),
    R.PriceOverwritten, R.PriceOverwritten2, R.PriceOverwritten3, R.PriceOverwritten4
		FROM RESERVATIONS R (NOLOCK)
		LEFT OUTER JOIN RESERVATIONADDRESSXTAB RAX(NOLOCK) ON R.ReferenceNumber = RAX.ReferenceNumber AND R.GuestNumber = RAX.GuestNumber
		LEFT OUTER JOIN RESERVATIONADDRESS RA(NOLOCK) ON RAX.AddressNumber = RA.AddressNumber
		LEFT OUTER JOIN RESERVATIONROOMLINEXTAB RLX(NOLOCK) ON R.ReferenceNumber = RLX.ReferenceNumber AND R.GuestNumber = RLX.GuestNumber
		LEFT OUTER JOIN RESERVATIONROOMLINE RL(NOLOCK) ON RL.LineNumber = RLX.LineNumber
		LEFT OUTER JOIN ROOMS RO(NOLOCK) ON RL.RoomNumber = RO.RoomNumber
		LEFT OUTER JOIN ROOMTYPES RTR(NOLOCK) ON RL.BookedTypeNumber = RTR.RoomTypeNumber
		LEFT OUTER JOIN ROOMTYPES RT(NOLOCK) ON RO.RoomTypeNumber = RT.RoomTypeNumber
		LEFT OUTER JOIN PRODUCTS P(NOLOCK) ON R.ProductNumber = P.ProductNumber
		WHERE R.ReferenceNumber = @AReferenceNumber AND R.GuestNumber <> 0 AND RA.AddressType = 1		
	OPEN NameCur

	FETCH NEXT FROM NameCur INTO
		@GuestNumber, @Status, @LastName,@FirstName, @CountryCode, @RoomType, @RoomTypeLine, @RoomArrival, @RoomArrivalTime, 
		@RoomDeparture, @RoomDepartureTime, @RoomNumber,@RoomAdults, @RoomChild1, @RoomChild2,
		@RoomPaysAdults, @RoomPaysChild1, @RoomPaysChild2, @RoomProduct, @RoomPrice, @RoomTotalPrice, @PO, @PO2, @PO3, @PO4
		
	IF @@FETCH_STATUS = 0
		SET @FOUND = 1
		
	IF @FOUND = 1
	BEGIN
		SET @s = CHAR(13) + CHAR(13) + 'NAME LIST'
		SET @s = @s + CHAR(13)
		SET @s = @s +  'No Status LName    FName    Ct  Allo Book Arrival  Time  D  Depart   Time  No    Ad C1 C2 PA P1 P2 Product  Price   Total PO PO2 PO3 PO4'

		SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
		UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s
	END

	WHILE (@@Fetch_Status=0)
	BEGIN
		SET @GuestNumber = dbo.GetStringFixedLength(@GuestNumber,2)

		IF @Status = 0 SET @StatusStr = 'Confirmed'
		ELSE IF @Status = 1 SET @StatusStr = 'Provisional'
		ELSE IF @Status = 2 SET @StatusStr = 'Guaranteed'
		ELSE IF @Status = 3 SET @StatusStr = 'Waitinglist'
		ELSE IF @Status = 4 SET @StatusStr = 'Tentative'
		ELSE IF @Status = 5 SET @StatusStr = 'Inhouse'
		ELSE IF @Status = 6 SET @StatusStr = 'OutOfHouse'
		ELSE IF @Status = 7 SET @StatusStr = 'Cancelled'
		ELSE IF @Status = 8 SET @StatusStr = 'NoShow'
		
		SET @StatusStr = dbo.GetStringFixedLength(@StatusStr ,6)
		SET @LastName = dbo.GetStringFixedLength(@Lastname, 8)
		SET @FirstName = dbo.GetStringFixedLength(@Firstname, 8)
		SET @CountryCode = dbo.GetStringFixedLength(@CountryCode, 4)
		
		SET @RoomType = dbo.GetStringFixedLength(@RoomType,4)
		
		IF @RoomType = @RoomTypeLine 
			SET @RoomTypeLine = ''
			
		SET @RoomTypeLine = dbo.GetStringFixedLength(@RoomTypeLine,4)
		SET @RoomArrival = dbo.GetStringFixedLength(@RoomArrival,8)
		SET @RoomArrivalTime = dbo.GetStringFixedLength(@RoomArrivalTime,5)
		SET @NumberOfDays = CAST(@RoomDeparture as INT) - CAST(@RoomArrival AS INT)
		SET @NumberOfDays = dbo.GetStringFixedLength(@NumberOfDays, 2)
		SET @RoomDeparture = dbo.GetStringFixedLength(@RoomDeparture,8)
		SET @RoomDepartureTime = dbo.GetStringFixedLength(@RoomDepartureTime,5)
		SET @RoomNumber = dbo.GetStringFixedLength(@RoomNumber, 5)
		SET @RoomAdults = dbo.GetStringFixedLength(@RoomAdults,2)
		SET @RoomChild1 = dbo.GetStringFixedLength(@RoomChild1,2)
		SET @RoomChild2 = dbo.GetStringFixedLength(@RoomChild2,2)
		SET @RoomPaysAdults = dbo.GetStringFixedLength(@RoomPaysAdults,2)
		SET @RoomPaysChild1 = dbo.GetStringFixedLength(@RoomPaysChild1,2)
		SET @RoomPaysChild2 = dbo.GetStringFixedLength(@RoomPaysChild2,2)
		SET @RoomProduct = dbo.GetStringFixedLength(@RoomProduct,7)
		SET @RoomPrice = CONVERT(VARCHAR, @RoomPrice)
		SET @RoomTotalPrice = CONVERT(VARCHAR, @RoomTotalPrice)
		
		IF CAST(@RoomArrival as INT) < @RoomArrivalMin 
		BEGIN
			SET @RoomArrivalMin = CAST(@RoomArrival as INT)
			SET @RoomArrivalTimeMin = @RoomArrivalTime 
		END
		
		IF CAST(@RoomDeparture  as INT) > @RoomDepartureMax 
		BEGIN
			SET @RoomDepartureMax  = CAST(@RoomDeparture as INT)
			SET @RoomDepartureTimeMax  = @RoomDepartureTime 
		END
		
		SET @RoomAdultsTotal = @RoomAdultsTotal + CAST(@RoomAdults as INT)
		SET @RoomChild1Total = @RoomChild1Total + CAST(@RoomChild1 as INT)
		SET @RoomChild2Total = @RoomChild2Total + CAST(@RoomChild2 as INT)
		SET @RoomPriceTotal = @RoomPriceTotal + CAST(@RoomPrice AS MONEY)
		SET @RoomTotalPriceTotal = @RoomTotalPriceTotal + CAST(@RoomTotalPrice AS MONEY)		
		
		SET @s = CHAR(13) + @GuestNumber + ' '+ @StatusStr + ' ' + @Lastname + ' ' + @Firstname + ' ' + @CountryCode + ' ' + @RoomType + ' ' + @RoomTypeLine + ' ' + @RoomArrival + ' ' + 
				@RoomArrivalTime + ' ' + @NumberOfDays + ' ' + @RoomDeparture + ' ' + @RoomDepartureTime + ' ' + @RoomNumber + ' ' + @RoomAdults + ' ' + @RoomChild1 + ' ' + @RoomChild2 + ' ' + 
				@RoomPaysAdults + ' ' + @RoomPaysChild1 + ' ' + @RoomPaysChild2 + ' ' + @RoomProduct + ' ' + ' ' + @RoomPrice + ' ' + @RoomTotalPrice + ' ' +
        CONVERT(CHAR(2), ISNULL(@PO,0)) + ' ' + CONVERT(CHAR(3), ISNULL(@PO2,0)) + ' ' + CONVERT(CHAR(3), ISNULL(@PO3,0)) + ' ' + CONVERT(CHAR(3), ISNULL(@PO4,0))
		
		SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
		UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s	

		FETCH NEXT FROM NameCur INTO
			@GuestNumber, @Status, @LastName,@FirstName, @CountryCode, @RoomType, @RoomTypeLine, @RoomArrival, @RoomArrivalTime, 
			@RoomDeparture, @RoomDepartureTime, @RoomNumber,@RoomAdults, @RoomChild1, @RoomChild2,
			@RoomPaysAdults, @RoomPaysChild1, @RoomPaysChild2, @RoomProduct, @RoomPrice, @RoomTotalPrice, @PO, @PO2, @PO3, @PO4
	END

	IF @FOUND = 1
	BEGIN
		SET @s = CHAR(13) + CHAR(13) + 
			dbo.GetStringFixedLength('',42) + 
			dbo.GetStringFixedLength(CAST(@RoomArrivalMin as VARCHAR(8)),8) + ' ' +
			dbo.GetStringFixedLength(@RoomArrivalTimeMin,5) + ' ' +
			dbo.GetStringFixedLength('',3) + 
			dbo.GetStringFixedLength(CAST(@RoomDepartureMax as VARCHAR(8)),8) + ' ' +
			dbo.GetStringFixedLength(@RoomDepartureTimeMax,5) +
			dbo.GetStringFixedLength('',7) + 
			dbo.GetStringFixedLength(CAST(@RoomAdultsTotal AS VARCHAR(3)),2) + ' ' +
			dbo.GetStringFixedLength(CAST(@RoomChild1Total AS VARCHAR(3)),2) + ' ' +
			dbo.GetStringFixedLength(CAST(@RoomChild2Total AS VARCHAR(3)),2) + 
			dbo.GetStringFixedLength('',19) +
			dbo.GetStringFixedLength(CAST(@RoomPriceTotal as VARCHAR(max)),7) + ' ' +
			dbo.GetStringFixedLength(CAST(@RoomTotalPriceTotal as VARCHAR(max)),8)

		SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
		UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s
	END

	CLOSE NameCur
	DEALLOCATE NameCur
END

SET @FOUND = 0

DECLARE HallCur Cursor Fast_Forward Read_Only FOR
  SELECT ConferenceRoomTypes.Type, NumberOfRooms, ConferenceRooms.Number, 
  CONVERT(VARCHAR(8), ArrivalDate, 112), ArrivalTime, CONVERT(VARCHAR(8), DepartureDate, 112), DepartureTime,
  Adults, Children1, Children2, ConferenceRoomTables.SortNumber, ConferenceRoomTables.Text, 
  Products.ProductName, Packet.Text, CAST(RESERVATIONCONFERENCELINE.Price AS VARCHAR), CAST(RESERVATIONCONFERENCELINE.TotalPrice AS VARCHAR)
  FROM ReservationConferenceLine(NOLOCK)  
  LEFT OUTER JOIN ConferenceRooms(NOLOCK) ON ReservationConferenceLine.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
  LEFT OUTER JOIN ConferenceRoomTypes (NOLOCK) ON ReservationConferenceLine.BookedConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
  LEFT OUTER JOIN Products(NOLOCK) ON ReservationConferenceLine.ProductNumber = Products.ProductNumber 
  LEFT OUTER JOIN ConferenceRoomTables(NOLOCK) ON ReservationConferenceLine.ConferenceRoomSetupNumber = ConferenceRoomTables.ConferenceRoomTableNumber
  LEFT OUTER JOIN Packet(NOLOCK) ON ReservationConferenceLine.PackageNumber = Packet.PacketNumber
  WHERE ReferenceNumber = @AReferenceNumber AND (ISNULL(LinkLineNumber, 0) = 0)
OPEN HallCur

FETCH NEXT FROM HallCur INTO @HallType, @HallPieces, @HallNumber, 
  @HallArrival, @HallArrivalTime, 
  @HallDeparture, @HallDepartureTime, 
  @HallAdults, @HallChild1, @HallChild2, @TableSetupNumber, @TableSetup,
  @HallProduct, @HallPacket, @HallPrice, @HallTotalPrice
  
IF @@FETCH_STATUS = 0
	SET @FOUND = 1
	
IF @FOUND = 1
BEGIN
	SET @s = CHAR(13) + CHAR(13) + 'HALLS'
	SET @s = @s + CHAR(13)
	SET @s = @s +  'Ty  Ps No  Arr      Time  Dep      Time  Ad C1 C2 Table   Product Packet  Price Total'

	SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
	UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s
END

WHILE (@@Fetch_Status=0)
BEGIN
  SET @HallType = dbo.GetStringFixedLength(@HallType,3)
  SET @HallPieces = dbo.GetStringFixedLength(@HallPieces,2)
  SET @HallNumber = dbo.GetStringFixedLength(@HallNumber,3)
  SET @HallArrival = dbo.GetStringFixedLength(@HallArrival,8)
  SET @HallArrivalTime = dbo.GetStringFixedLength(@HallArrivalTime,5)
  SET @HallDeparture = dbo.GetStringFixedLength(@HallDeparture,8)
  SET @HallDepartureTime = dbo.GetStringFixedLength(@HallDepartureTime,5)
  SET @HallAdults = dbo.GetStringFixedLength(@HallAdults,2)
  SET @HallChild1 = dbo.GetStringFixedLength(@HallChild1,2)
  SET @HallChild2 = dbo.GetStringFixedLength(@HallChild2,2)
  SET @TableSetup = dbo.GetStringFixedLength(@TableSetup,7)
  SET @HallProduct = dbo.GetStringFixedLength(@HallProduct,7)
  SET @HallPacket = dbo.GetStringFixedLength(@HallPacket,7)
  SET @HallPrice = dbo.GetStringFixedLength(@HallPrice,15)
  SET @HallTotalPrice = dbo.GetStringFixedLength(@HallTotalPrice,15)

  SET @s = CHAR(13) + @HallType + ' ' + @HallPieces + ' ' + @HallNumber + ' ' + @HallArrival + ' ' + @HallArrivalTime + ' ' +  @HallDeparture + ' ' +  @HallDepartureTime + ' ' + @HallAdults + ' ' + @HallChild1+ ' ' + @HallChild2 + ' ' + @TableSetup + ' ' +@HallProduct+ ' ' + @HallPacket + ' ' + @HallPrice + ' ' + @HallTotalPrice

  SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
  UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s

  FETCH NEXT FROM HallCur INTO @HallType, @HallPieces, @HallNumber, 
    @HallArrival, @HallArrivalTime, 
    @HallDeparture, @HallDepartureTime, 
    @HallAdults, @HallChild1, @HallChild2, @TableSetupNumber, @TableSetup,
    @HallProduct, @HallPacket, @HallPrice, @HallTotalPrice
END

CLOSE HallCur
DEALLOCATE HallCur

SET @FOUND = 0
SET @CurrentDate = ''

DECLARE ArrCur Cursor Fast_Forward Read_Only FOR
  SELECT DISTINCT TArrangementExtraView.ArrangementLineNumber, Date, Time,DepartureTime, Pieces, GroupSortNumber, WareSortNumber, WareText, 
  CONVERT(VARCHAR, Price), CONVERT(VARCHAR, TotalPrice), ExDepartment, ConferenceRooms.Number, PlaceText, Departments.DepartmentNumber,
  CASE WHEN TArrangementExtraView.Time < @Arr24HoursStarts THEN CONVERT(DATETIME,TArrangementExtraView.Time, 108) + CONVERT(DATETIME,'23:59') ELSE CONVERT(DATETIME, TArrangementExtraView.Time,108) END AS OrderCol,
  Note
  FROM TArrangementExtraView (NOLOCK) 
  LEFT OUTER JOIN ConferenceRooms (NOLOCK) ON TArrangementExtraView.HallNumber = ConferenceRooms.ConferenceRoomNumber
  LEFT OUTER JOIN Departments(NOLOCK) ON Departments.PK_Department = TArrangementExtraView.FK_PacketChargeDepartment
  INNER JOIN ArrangementReservationsXtab (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = TArrangementExtraView.ArrangementLineNumber
    AND ArrangementReservationsXtab.GuestNumber = @AGuestNumber
  WHERE TArrangementExtraView.ReferenceNumber = @AReferenceNumber
  ORDER BY Date, Ordercol
OPEN ArrCur

FETCH NEXT FROM ArrCur INTO @ArrangementLineNumber, @Date, @Time, @ArrDepTime, @ArrPieces, @GroupSortNumber, @WareSortNumber, @WareText, @ArrPrice, @ArrTotalPrice, 
  @ExDepartment, @ArrHallNumber, @PlaceText, @PCDepartment, @OrderCol, @ArrNote
  
IF @@FETCH_STATUS = 0
	SET @FOUND = 1

IF @FOUND = 1
BEGIN
	SET @s = CHAR(13) + CHAR(13) + 'ARRANGEMENT:' + CHAR(13)

	SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
	UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s
END

WHILE (@@Fetch_Status=0)
BEGIN
  SET @s = ''
  SET @Departments = ''
  SELECT @Departments = dbo.GetArrangementLineDepartmentStr(@ArrangementLineNumber,0,0)

  SET @Time = dbo.GetStringFixedLength(@Time,5)
  SET @ArrDepTime = dbo.GetStringFixedLength(@ArrDepTime,5)
  SET @ArrPieces = dbo.GetStringFixedLength(@ArrPieces,3)
  SET @GroupSortNumber = dbo.GetStringFixedLength(@GroupSortNumber,3)
  SET @WareSortNumber = dbo.GetStringFixedLength(@WareSortNumber,3)
  SET @WareText = dbo.GetStringFixedLength(@WareText,18)
  SET @ArrPrice = CONVERT(VARCHAR, @ArrPrice)
  SET @ArrTotalPrice = CONVERT(VARCHAR, @ArrTotalPrice)
  SET @PCDepartment = dbo.GetStringFixedLength(@PCDepartment,1)
  SET @ExDepartment = dbo.GetStringFixedLength(@ExDepartment,1)
  SET @Departments = dbo.GetStringFixedLength(@Departments,8)
  SET @ArrHallNumber = dbo.GetStringFixedLength(@ArrHallNumber,3)
  SET @PlaceText = dbo.GetStringFixedLength(@PlaceText,8)

  IF @CurrentDate <> @Date
  BEGIN
    SET @s = @s + char(13)
    SET @s = @s +  'DATE: ' + CONVERT(VARCHAR(15), CONVERT(Datetime, @Date), 105)
    SET @s = @s + char(13)
    SET @s = @s +  'Arr   ' + 'Dep   ' + 'Pcs ' +'Grp ' + 'No. ' + 'Contents           ' +  'Price ' + 'Total   ' + 'Pd '+ 'Ex '+'Deps    ' + 'Hall ' +'Place '
    SET @s = @s + char(13)
    
    SET @CurrentDate = @Date
  END

  SET @s = @s  +  @Time +' ' +@ArrDepTime +' ' + @ArrPieces  +' ' + @GroupsortNumber +' ' +@waresortNumber +' ' + @WareText +' ' + @ArrPrice +' ' +@ArrTotalPrice +' ' + @PCDepartment +' ' +' ' + @ExDepartment +' ' + @Departments +' ' + @ArrHallNumber+' ' + @PlaceText
  SET @s = @s + CHAR(13)
  IF @ArrNote <> ''
	SET @s = @s + '      ' + @ArrNote + CHAR(13)

  SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
  UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s

  FETCH NEXT FROM ArrCur INTO @ArrangementLineNumber, @Date, @Time, @ArrDepTime, @ArrPieces, @GroupSortNumber, @WareSortNumber, @WareText, @ArrPrice, @ArrTotalPrice, 
  @ExDepartment, @ArrHallNumber, @PlaceText, @PCDepartment, @OrderCol, @ArrNote
END

CLOSE ArrCur
DEALLOCATE ArrCur

DECLARE NoteCur Cursor Fast_Forward Read_Only FOR
	select NotePage,text From RESERVATIONNOTES (NOLOCK) 
	where ReferenceNumber = @AReferenceNumber AND GuestNumber = @AGuestNumber
	order by NotePage
Open NoteCur

FETCH NEXT FROM NoteCur INTO @NotePage, @NoteText

SET @FOUND = 0
	
WHILE (@@Fetch_Status=0)
BEGIN
  IF @Found = 0
  BEGIN
	  SET @s = CHAR(13) + CHAR(13) + 'NOTES'

	  SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
	  UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s
    SET @Found = 1
  END

	SET @S = CHAR(13) + CHAR(13) + 'Notepage: ' + @NotePage + CHAR(13)
	SET @S = @S + @NoteText 
	
	SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
	UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s
	FETCH NEXT FROM NoteCur INTO @NotePage, @NoteText
END

CLOSE NoteCur
DEALLOCATE NoteCur

-- Set address info
DECLARE @FirstName1 VARCHAR(50)
DECLARE @LastName1 VARCHAR(50)
DECLARE @Address1 VARCHAR(100)
DECLARE @Address2 VARCHAR(100)
DECLARE @Address3 VARCHAR(100)
DECLARE @CountryCode1 VARCHAR(3)
DECLARE @ZipCode VARCHAR(10)
DECLARE @City VARCHAR(50)
DECLARE @Link VARCHAR(10)
DECLARE @Phone VARCHAR(255)
DECLARE @Cell VARCHAR(255)
DECLARE @DateOfBirth VARCHAR(15)
DECLARE @Email VARCHAR(150)
DECLARE @PassportNumber VARCHAR(25)
DECLARE @AddressType VARCHAR(3)

DECLARE AddressCur Cursor Fast_Forward Read_Only FOR
  SELECT FirstName, LastName, Address1, Address2, Address3, CountryCode, ZipCode, City, CONVERT(VARCHAR(10), Link), Phone, Cell, CONVERT(VARCHAR(15), CONVERT(Datetime, DateOfBirth), 105), 
    Email, PassPortNumber, CONVERT(VARCHAR(3), ReservationAddress.AddressType)
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = Reservations.GuestNumber
    AND Reservations.ReferenceNumber = @AReferenceNumber
    AND Reservations.GuestNumber = @AGuestNumber
  INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXtab.AddressNumber
  ORDER BY ReservationAddress.AddressType
OPEN AddressCur

FETCH NEXT FROM AddressCur INTO @FirstName1, @LastName1, @Address1, @Address2, @Address3, @CountryCode1, @ZipCode, @City, @Link, @Phone, @Cell, @DateOfBirth, @Email, @PassPortNumber, @AddressType

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @s = CHAR(13) + 'Addresses' + CHAR(13) +
    'Type: ' + @AddressType + CHAR(13) +
    'First: ' + @FirstName1 + CHAR(13) +
    'Last: ' + @LastName1 + CHAR(13) +
    'Address1: ' + @Address1 + CHAR(13) +
    'Address2: ' + @Address2 + CHAR(13) +
    'Address3: ' + @Address3 + CHAR(13) +
    'Country: ' + @CountryCode1 + CHAR(13) +
    'Zip: ' + @ZipCode + CHAR(13) +
    'City: ' + @City + CHAR(13) +
    'Link: ' + @Link + CHAR(13) +
    'Phone: ' + @Phone + CHAR(13) +
    'Cell: ' + @Cell + CHAR(13) +
    'Birth ' + @DateOfBirth + CHAR(13) +
    'Mail: ' + @Email + CHAR(13) +
    'Passport: ' + @PassPortNumber

	  SELECT @Ptr = TEXTPTR(ReservationLog), @n = DATALENGTH(ReservationLog) FROM Followup(NOLOCK) WHERE FollowupNumber = @AFollowupNumber
	  UPDATETEXT Followup.ReservationLog @Ptr @n 0 @s

  FETCH NEXT FROM AddressCur INTO @FirstName, @LastName, @Address1, @Address2, @Address3, @CountryCode, @ZipCode, @City, @Link, @Phone, @Cell, @DateOfBirth, @Email, @PassPortNumber, @AddressType
END

CLOSE AddressCur
DEALLOCATE AddressCur