CREATE PROCEDURE [dbo].[OnlineCreateReservation] 
(
	@HotelChainNumber INT,  
	@ProductTypeNumber INT,
	@ReferenceNumber INT OUTPUT,
	@UserIDNumber int = NULL,
	@MethodOfPayment int = NULL,
	@Status tinyint = NULL,
	@FirstName varchar(50) = NULL,
	@LastName varchar(50) = NULL,
	@Email varchar(150) = NULL,
	@Phone varchar(255) = NULL,
	@ArrivalTime datetime = NULL,
	@DepartureTime datetime = NULL,
	@CreationInProgress bit = 1,
	@CampaignId int = NULL,
	@DiscountPct smallmoney = NULL,
	@Address1 varchar(100) = NULL,
	@Address2 varchar(100) = NULL,
	@Address3 varchar(100) = NULL,
	@ZipCode varchar(10) = NULL,
	@City varchar(50) = NULL,
	@CountryCode varchar(3) = NULL,
	@PointOfSaleNumber INT = NULL,
	@SegmentNumber INT = NULL,
	@CreatedReferenceNumber INT = NULL,
  @ExtrasDiscountRate MONEY = NULL,
  @ExtraDicountIsFixedAmount BIT = NULL,
  @AllDicountIsFixedAmount BIT = NULL,
  @GuestTypeNumber INT = NULL,
  @PurposeCodeNumber INT = NULL,
  @CountryNumber INT = NULL,
  @CustomerNumber INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	/*
		Created by Jacob Pedersen July 2004

		Changes:
    MLJ 2024-11-04: Added @CustomerNumber
    MLJ 2024-10-01: Added @CountryNumber
    MLJ 2024-03-12: Added @GuestTypeNumber and @PurposeCodeNumber
    MLJ 2024-01-29: Added @@ExtrasDiscountRate, @ExtraDicountIsFixedAmount and @AllDicountIsFixedAmount
    MLJ 2023-10-03: Added @PointOfSaleNumber, @SegmentNumber, @CreatedReferenceNumber
		MLJ 2023-06-27: Added @CampaignId, @DiscountPct and address parameters
		SG 2023-02-15: Set default for @Status correct
		MLJ 2022-09-01: Fixed bugs and added more pareameters
		AHE 2022-03-03: IF @PurposeCodeNumber = 0 THEN @PurposeCodeNumber = NULL
		AHE 2019-10-21: Added default values for TempState
		SG 2019-10-04: Added SessionTimeStamp, CreationInProgress to ReservationMain
		SG 2019-04-09: Added default values for GuestType and PurposeCode
		TBU20170320: Added SELECT @ReservationType = dbo.GetHotelSetupKeyValue('OnlineReservationType', @CustomerNumber)
		TBU20170320: Added update from local > KJ 20161230: Using UPPER results in table scan. removed to use index
		KJ: 20160613: Added RequestorID (RequestorId = 2 means online
		TBU20160203 Change - @AutoconfirmationValue = 1
		TBU20160203: Added @AutoconfirmationValue
		TBU20140910: Added check on IF IsNull(@ReservationType, '') = '' 
		BA/20100112: CR011929/StaffID wasn't set when creating online reservations
		JP 20070322 EXT: added HotelCurrency
		TB20070315:  Removed  @SegmentNumber from ProductTypes
		JP 20070315: EXT: added Currency to Reservations (takes the one from hotelsetup)
		TB20070301: Added  New way to read @SegmentNumber
		AV20070118: Added OriginType to ReservationMain (1 is Online)
		KJ20060116: Added HotelNumber to insert into Followup
		TB20051219:  Added @HotelChainNumber to GetNextNumber 'RESERVATIONIDENTITY'
		TB20050922: Call GetNextNumber to create Picassoonline user in Personel
		TB20050817: Added ReservationType - Reservationtype was always 0 (Single)
		TB20050810:  Added @ProductTypeNumber as parameter - Used to find P.O.S if ProductType (Holidaytypes) is used.
		TB20050518: Added  @HotelChainNumber to be save
		TB20050513: Change call to update Tabel 'ReservationIdentity' to be called from a procedure
		TB20050415: Add PicassoOnlineTempState from hotelsetup as status instead of just 0
		20050309   : Added AddressTypeNumber to ReservationAddressXTab
		2004-10-14 JP: added POS,Segment,GuestType,PurposeCode

		NOTES: 	- 

	*/
	DECLARE @AddressNumber INT,
	@ReservationType INT,
	@UseSegmentFromProducttype BIT,
	@Currency INT,
	@AutoConfirmationHotelsetup BIT,
	@AutoconfirmationValue TINYINT,
	@HotelCustomerNumber INT

	IF @PointOfSaleNumber=0
		SET @PointOfSaleNumber = NULL
	IF @PointOfSaleNumber IS NULL
	BEGIN
		IF @ProductTypeNumber <> 0
			SELECT @PointOfSaleNumber =  PointOfSaleNumber FROM ProductTypes WITH (NOLOCK) WHERE ProductTypeNumber = @ProductTypeNumber
		ELSE
			SELECT @PointOfSaleNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlinePointOfSaleNumber'
	END
	IF (@PointOfSaleNumber IS NOT NULL) AND NOT EXISTS(SELECT * FROM POINTOFSALES WHERE NUMBER=@PointOfSaleNumber)
		SET @PointOfSaleNumber = NULL

	IF @SegmentNumber=0
		SET @SegmentNumber = NULL
	IF @SegmentNumber IS NULL
	BEGIN
		SELECT @UseSegmentFromProducttype = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='OnlineUseSegmentFromProducttype'
		IF @ProductTypeNumber <> 0 AND @UseSegmentFromProducttype = 1
		 print 'gfg'
		   --SELECT @SegmentNumber =  SegmentNumber FROM ProductTypes WITH (NOLOCK) WHERE ProductTypeNumber = @ProductTypeNumber
		ELSE
		   SELECT @SegmentNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlineSegmentNumber'
	END
	IF (@SegmentNumber IS NOT NULL) AND NOT EXISTS(SELECT * FROM SEGMENTS WHERE NUMBER=@SegmentNumber)
		SET @SegmentNumber = NULL
	
	SELECT @Currency=ISNULL(KeyValue, -1) FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey='HotelCurrency'
	IF @Currency = -1
	  SELECT @Currency=KeyValue FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey='CountryCode'

	/* Read HotelSetup's */
  IF ISNULL(@GuestTypeNumber,0) = 0
  	SELECT @GuestTypeNumber = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlineGuestTypeNumber'
	IF ISNULL(@GuestTypeNumber,0) = 0
	  SET @GuestTypeNumber = NULL
  IF ISNULL(@PurposeCodeNumber,0) = 0
  	SELECT @PurposeCodeNumber = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlinePurposeCodeNumber'
	IF ISNULL(@PurposeCodeNumber,0) = 0
	  SET @PurposeCodeNumber = NULL
	IF @Status IS NULL
	BEGIN
		SELECT @Status = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlineTempState'
		IF @Status IS NULL
		BEGIN
		  SET @Status = 0
		END  
	END	
	SELECT @ReservationType=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='OnlineReservationType'
	SELECT @HotelCustomerNumber=CustomerNumber from Customer (nolock) where hotelnumber = @HotelChainNumber
	SELECT @ReservationType = dbo.GetHotelSetupKeyValue('OnlineReservationType', @HotelCustomerNumber)
	SELECT @AutoConfirmationHotelsetup=IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='AutoConfirmationDefaultValue'

	SET @AutoconfirmationValue = 0
	IF @AutoConfirmationHotelsetup = 1
	BEGIN
		SET @AutoconfirmationValue = 1
	END

	IF @UserIDNumber IS NULL
		SELECT @UserIDNumber = UserIDNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = 5 -- Personel.pas (TUserType.utPicassoOnline)

	IF ISNULL(@CreatedReferenceNumber,0)=0
		EXEC GetNextNumber 'RESERVATIONIDENTITY', 1, @ReferenceNumber OUTPUT,  @HotelChainNumber
	ELSE
		SET @ReferenceNumber=@CreatedReferenceNumber
	EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @AddressNumber OUTPUT

	INSERT INTO ReservationMain (ReferenceNumber, HotelNumber, ReservationType, OriginType, StaffID, AutoConfirmed, SessionTimeStamp, CreationInProgress) 
	VALUES (@ReferenceNumber, @HotelChainNumber, @ReservationType, 1, @UserIDNumber, @AutoconfirmationValue, GetDate(), ISNULL(@CreationInProgress,1))  -- TODO allotmentnumber if customer has allotment agreement

	INSERT INTO Reservations (
		ReferenceNumber, 
		GuestNumber, 
		Status, 
		MarketSegment, 
		PointOfSale, 
		GuestType, 
		PurposeCode, 
		Currency, 
		AutoConfirmed, 
		RequestorID,
		METHODOFPAYMENT,
		ArrivalDate,
		ArrivalTime,
		DepartureDate,
		DepartureTime,
		FK_CampaignCode,
		DiscountCustomerType
	) VALUES (
		@ReferenceNumber, 
		0, 
		@Status, 
		@SegmentNumber, 
		@PointOfSaleNumber, 
		@GuestTypeNumber, 
		@PurposeCodeNumber, 
		@Currency, 
		@AutoconfirmationValue, 
		-2,
		@MethodOfPayment,
		CONVERT(CHAR(8), @ArrivalTime, 112),
		CONVERT(CHAR(5), @ArrivalTime, 108),
		CONVERT(CHAR(8), @DepartureTime, 112),
		CONVERT(CHAR(5), @DepartureTime, 108),
		@CampaignId,
		CASE WHEN @CampaignId IS NULL THEN NULL ELSE 1/*Guest*/ END
	)

	INSERT INTO ReservationAddress (
		AddressNumber,  
		Addresstype,  
		FirstName,
		Lastname,
		Email,
		Phone,
		DISCOUNTRATE,
		Address1,
		Address2,
		Address3,
		ZipCode,
		City,
		CountryCode,
    CountryNumber,
    ExtrasDiscountRate,
    ExtraDicountIsFixedAmount,
    AllDiscountIsFixedAmount,
    Link
	) VALUES (
		@AddressNumber, 
		1 /* Guest */, 
		@FirstName,
		ISNULL(@LastName, 'PicassoOnline'),
		@Email,
		@Phone,
		@DiscountPct,
		@Address1,
		@Address2,
		@Address3,
		@ZipCode,
		@City,
		@CountryCode,
    @CountryNumber,
    @ExtrasDiscountRate,
    @ExtraDicountIsFixedAmount,
    @AllDicountIsFixedAmount,
    @CustomerNumber
	)  -- NOTE!  'PicassoOnline' is used in the Online Service, so if this is changed the service must be changed to work!

	INSERT INTO ReservationAddressXTab (ReferenceNumber, GuestNumber, AddressNumber, AddressTypeNumber) VALUES (@ReferenceNumber, 0, @AddressNumber, 1) -- Added AddressTypeNumber

	-- FOLLOWUP ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

	DECLARE @NewFollowUpNumber INT,
	@SystemDateTime DATETIME,
	@Text VARCHAR(8000)

  EXEC SystemDateTime '', @SystemDateTime OUTPUT

	/* Find the UserIDNumber for PicassoOnline. If not found, create it */
	IF @UserIDNumber IS NULL
	BEGIN
	  EXEC GetNextNumber 'PERSONELIDENTITY', 1, @UserIDNumber OUTPUT
	  INSERT INTO Personel (UserIDNumber, UserIDCode, FirstName, LastName, PasswordHash, UserTypeNumber) VALUES (@UserIDNumber, 'PicassoOnline', 'PicassoOnline', 'PicassoOnline', dbo.GetPasswordHash('Picasso'), 5 /*utPicassoOnline*/)
	END

	/* Create log */
	set @Text = 'Created'
	INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType, HotelNumber)
	VALUES (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 1, @HotelChainNumber)
	SELECT @NewFollowUpNumber = @@identity
	INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@ReferenceNumber, 0, @NewFollowUpNumber)
END


