CREATE PROCEDURE dbo.OnlineAddReservationOrder
(
	@ReferenceNumber int,
	@GuestNumber smallint,
	@FolioNumber int,
	@HotelNumber int,
	@DeliveryPlace nvarchar(100),
	@DeliveryTime datetime,
	@CreateTimeLocal datetime,
	@ReservationOrderId int OUTPUT,
	@HotelCustomerNumber int OUTPUT,
	@SplitBillNumber int OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;

	IF (@FolioNumber IS NULL) OR (@FolioNumber=0)
	BEGIN
		IF @GuestNumber=0
			SELECT @SplitbillNumber=CASE 
				WHEN ISNULL(Day2,0) = 0 OR DayNo < Day2 THEN SplitBillNumber1
				WHEN ISNULL(Day3,0) = 0 OR DayNo < Day3 THEN SplitBillNumber2
				WHEN ISNULL(Day4,0) = 0 OR DayNo < Day4 THEN SplitBillNumber3
				ELSE SplitBillNumber4
			END 
			FROM RESERVATIONROOMLINE RL 
			INNER JOIN RESERVATIONROOMLINEXTAB X on X.LINENUMBER=RL.LINENUMBER AND X.ReferenceNumber=@ReferenceNumber AND X.GuestNumber=@GuestNumber 
			INNER JOIN (SELECT ReferenceNumber, GuestNumber, DATEDIFF(day, ArrivalDate, @CreateTimeLocal)+1 DayNo FROM Reservations R) R ON  R.ReferenceNumber=X.ReferenceNumber AND R.GuestNumber=X.GuestNumber
		ELSE
			SELECT @SplitbillNumber=CASE 
				WHEN ISNULL(Day2,0) = 0 OR DayNo < Day2 THEN SplitBillNumber1
				WHEN ISNULL(Day3,0) = 0 OR DayNo < Day3 THEN SplitBillNumber2
				WHEN ISNULL(Day4,0) = 0 OR DayNo < Day4 THEN SplitBillNumber3
				ELSE SplitBillNumber4
			END 
			FROM (
				SELECT DATEDIFF(day, ArrivalDate, @CreateTimeLocal)+1 DayNo, Day2,Day3,Day4,SplitBillNumber1, SplitBillNumber2, SplitBillNumber3, SplitBillNumber4
				FROM Reservations R
				WHERE R.ReferenceNumber=@ReferenceNumber
				AND R.GuestNumber=@GuestNumber
			) R
	END

	INSERT INTO [dbo].[OnlineReservationOrder]
    (
		[ReferenceNumber]
        ,[GuestNumber]
        ,[DeliveryPlace]
        ,[DeliveryTime]
        ,[CreateTime]
        ,[CreateTimeLocal]
		,[FolioNumber]
	)
     VALUES
     (
	   @ReferenceNumber
       ,@GuestNumber
       ,@DeliveryPlace
       ,@DeliveryTime
       ,GETDATE()
	   ,@CreateTimeLocal
	   ,@FolioNumber
	)

	SET @ReservationOrderId=@@IDENTITY

	SELECT @HotelCustomerNumber=CustomerNumber FROM CUSTOMER C WHERE C.CustomerTypeNumber=7 AND C.HotelNumber=@HotelNumber
END
