CREATE PROCEDURE [dbo].[OnlineCreateReservation] @HotelChainNumber INT,  @ProductTypeNumber INT,@ReferenceNumber INT OUTPUT AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen July 2004

    Changes:
	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,
@PointOfSaleNumber INT,
@SegmentNumber INT,
@GuestTypeNumber INT,
@PurposeCodeNumber INT,
@ReservationType INT,
@UseSegmentFromProducttype BIT,
@Currency INT,
@TempState INT,
@PicassoOnlineStaffID INT, -- BA/20100112: CR011929
@AutoConfirmationHotelsetup BIT,
@AutoconfirmationValue TINYINT,
@CustomerNumber INT

/* Find P.O.S */
--DECLARE @ProductTypeNumber INT
--SET @ProductTypeNumber = 2
IF @ProductTypeNumber <> 0
	SELECT @PointOfSaleNumber =  PointOfSaleNumber FROM ProductTypes WITH (NOLOCK) WHERE ProductTypeNumber = @ProductTypeNumber
ELSE
	SELECT @PointOfSaleNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlinePointOfSaleNumber'

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'

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 */

SELECT @GuestTypeNumber = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlineGuestTypeNumber'
SELECT @PurposeCodeNumber = IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlinePurposeCodeNumber'
SELECT @TempState=IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='PicassoOnlineTempState'
SELECT @ReservationType=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='OnlineReservationType'
SELECT @CustomerNumber from Customer (nolock) where hotelnumber = @HotelChainNumber
SELECT @ReservationType = dbo.GetHotelSetupKeyValue('OnlineReservationType', @CustomerNumber)
SELECT @AutoConfirmationHotelsetup=IsNull(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='AutoConfirmationDefaultValue'

--TBU20140910 -- Check default restype
/*IF IsNull(@ReservationType, '') = '' 
BEGIN
	SELECT @ReservationType=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='DefaultResType'
	IF IsNull(@ReservationType, '') = '' 
	BEGIN
		SET @ReservationType = 0		
	END
END*/

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

--print @ReservationType 
-- BA/20100112: CR011929/StaffID wasn't set when creating online reservations
SELECT @PicassoOnlineStaffID = UserIDNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = 5 -- Personel.pas (TUserType.utPicassoOnline)

--UPDATE ReservationIdentity SET @ReferenceNumber = IdentityField = IdentityField + 1  -- New referencenumber
--UPDATE ReservationAddressIdentity SET @AddressNumber = IdentityField = IdentityField + 1  -- New addressnumber
EXEC GetNextNumber 'RESERVATIONIDENTITY', 1, @ReferenceNumber OUTPUT,  @HotelChainNumber
EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @AddressNumber OUTPUT

--BEGIN TRAN

-- BA/20100112: CR011929
INSERT INTO ReservationMain (ReferenceNumber, HotelNumber, ReservationType, OriginType, StaffID, AutoConfirmed, SessionTimeStamp, CreationInProgress) 
VALUES (@ReferenceNumber, @HotelChainNumber, @ReservationType, 1, @PicassoOnlineStaffID, @AutoconfirmationValue, GetDate(), 1)  -- TODO allotmentnumber if customer has allotment agreement

INSERT INTO Reservations (ReferenceNumber, GuestNumber, Status, MarketSegment, PointOfSale, GuestType, PurposeCode, Currency, AutoConfirmed, RequestorID)
VALUES (@ReferenceNumber, 0, @TempState, @SegmentNumber, @PointOfSaleNumber, @GuestTypeNumber, @PurposeCodeNumber, @Currency, @AutoconfirmationValue, -2)  -- Status 0 = CONFIRMED

INSERT INTO ReservationAddress (AddressNumber,  Addresstype,  Lastname) VALUES (@AddressNumber, 1 /* Guest */, 'PicassoOnline')  -- 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,
@SystemDateStr CHAR(8),
@Text VARCHAR(8000),
@UserIDNumber INT
SELECT  @SystemDateStr = SYSTEMDATE FROM System  WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record YYYYMMDD
SELECT @SystemDateTime = CONVERT (DATETIME, @SystemDateStr, 112)+CONVERT (CHAR, GetDate(), 108)

/* Find the UserIDNumber for PicassoOnline. If not found, create it */
SELECT @UserIDNumber=UserIDNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber=5
IF @@rowcount = 0
BEGIN
  EXEC GetNextNumber 'PERSONELIDENTITY', 1, @UserIDNumber OUTPUT
  --EXEC GetNextNumber 'RESERVATIONADDRESSIDENTITY', 1, @AddressNumber OUTPUT
  --UPDATE PersonelIdentity SET @UserIDNumber= IdentityField = IdentityField + 1
  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)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--COMMIT TRAN


