CREATE PROCEDURE [dbo].[OnlineCreateCustomer]
  @ReferenceNumber INT, @Firstname VARCHAR(50), @Lastname VARCHAR(50), 
  @Address1 VARCHAR(50), @Address2 VARCHAR(50), @Zipcodeoverwrite VARCHAR(10), 
  @City VARCHAR(50), @Countrycode VARCHAR(3), @Phonenumber VARCHAR(30), 
  @Cellphonenumber VARCHAR(30), @Email VARCHAR (100), @Companyname VARCHAR(50), 
  @CustomerTypeNumber INT, @eanNumber VARCHAR(50) ='', @ANoNews BIT = 0, @ACustomerNumber INT = 0,
  @UserName VARCHAR(50)='', @Password VARCHAR(50)=''
 AS SET NOCOUNT ON
/*
    Created by Troels Buch December 2005

    Changes: N/A
    TBU20150504: Added @UserName VARCHAR(50)='', @Password VARCHAR(50)=''
    TBU20150430: Changed (AND LEN(@Phonenumber) > 5) and Replace "-", ""
    TBU20150422: Added AND LEN(@Phonenumber) > 5
	TB20140612: Changed SELECT @CustomerNumberExisting
	TB20140307: Added @ACustomerNumber
    VD20130902: Mail addresses now situated in a separate table - KUN i 8.1+!
	TB20100121: Use default pointofsale if no point of sale on rerervation
	TB20090805: Added @ANoNews - KUN i 4.2
	AV+TB : FIX for Customer Number variable NULL check
	AV: fix so that not all addresess will be updated
	NLB20070102: QualifyPhoneNumber() is no longer used
	NLB20061228: Select statement for @CustomernumberTmp altered
	KJ 20061219: Moved GetNextNumber for Customer
	NLB20061218: @eanNumber saved in Customer table
	TB20060929: Changed check on @PurposeCodeNumber from 0 to 1
	TB20051223: Added function QualifyPhoneNumber
 	TB20051207: New check on @Number for Firm
	TB20051207: New check on @Number
	TB20051207:  Add Customer to Marketing
	TB20051207: Changed @Countrycode from int to varchar

    NOTES: 	- 
	@CustomerTypeNumber:
		1= guest
		2= company
*/

DECLARE @Customernumber INT--, @CustomernumberTmp  INT
DECLARE @CustomerNumberExisting INT
DECLARE @PointOfSaleNumber INT
DECLARE @SegmentNumber INT
DECLARE @GuestTypeNumber INT
DECLARE @PurposeCodeNumber INT
DECLARE @UserIdNumber INT
DECLARE @Countrynumber INT

SELECT @Countrynumber = CountryNumber FROM Countries WITH (NOLOCK) WHERE CountryCode = @Countrycode

DECLARE @UseProductType BIT
DECLARE @UseNoNews BIT
SELECT @UseProductType =KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('PicassoOnlineUseProductTypes')

/* Read HotelSetup's */
IF @UseProductType = 1
BEGIN
   SELECT @PointOfSaleNumber =  PointOfSale FROM Reservations WITH (NOLOCK) WHERE referencenumber = @ReferenceNumber
   -- TB20100121 - Use default pointofsale if no point of sale on rerervation
   IF IsNull(@PointOfSaleNumber, 0) = 0
   BEGIN
       SELECT @PointOfSaleNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('PicassoOnlinePointOfSaleNumber')    
   END
   --
END
ELSE
BEGIN
SELECT @PointOfSaleNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('PicassoOnlinePointOfSaleNumber')
IF  @PointOfSaleNumber = 0
   SET @PointOfSaleNumber = null
END

SELECT @SegmentNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('PicassoOnlineSegmentNumber')
IF @SegmentNumber = 0
   SET @SegmentNumber = null

SELECT @GuestTypeNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('PicassoOnlineGuestTypeNumber')
IF @GuestTypeNumber = 0
   SET @GuestTypeNumber = null

SELECT @PurposeCodeNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('PicassoOnlinePurposeCodeNumber')
IF @PurposeCodeNumber = 0
   SET @PurposeCodeNumber = null

SELECT @UserIdNumber = UserIdNumber  FROM Personel WITH (NOLOCK)  WHERE userIdCode = 'PicassoOnline' AND UserTypeNumber = 5
/*
SELECT @CustomerNumberExisting = CustomerNumber FROM Customer WITH (NOLOCK) 
WHERE ((Phonenumber = @Phonenumber AND @Phonenumber <> '' AND LEN(@Phonenumber) > 5) OR (IsNull(Cellphonenumber, '') = @Cellphonenumber AND @Cellphonenumber <> '') 
OR (Phonenumber = @Cellphonenumber AND @Cellphonenumber <> '') OR (IsNull(Cellphonenumber, '') = @Phonenumber AND IsNull(Cellphonenumber, '') <> '' ) ) 
AND markasdeleted=0 
IF ISNULL(@CustomerNumberExisting, 0)  <> 0
BEGIN
  SELECT * FROM Customer WITH (NOLOCK) WHERE customernumber = @CustomerNumberExisting
END*/

IF IsNull(@ACustomerNumber, 0) = 0 
	SELECT @CustomerNumberExisting = CustomerNumber FROM Customer WITH (NOLOCK) 
	WHERE (( 
	REPLACE(Phonenumber, '-', '') = REPLACE(@Phonenumber, '-', '') AND @Phonenumber <> '' AND LEN(@Phonenumber) > 5) 
	OR (REPLACE(Cellphonenumber, '-', '') = REPLACE(@Cellphonenumber, '-', '') AND @Cellphonenumber <> '' AND LEN(@Cellphonenumber) > 5) 
	OR (REPLACE(Phonenumber, '-', '') = REPLACE(@Cellphonenumber, '-', '') AND @Cellphonenumber <> '' AND LEN(@Cellphonenumber) > 5) 
	OR (REPLACE(Cellphonenumber, '-', '') = REPLACE(@Phonenumber, '-', '') AND LEN(@Phonenumber) > 5) ) 
	AND markasdeleted=0
ELSE
	SET @CustomerNumberExisting = @ACustomerNumber
	
IF ISNULL(@CustomerNumberExisting, 0)  <> 0
BEGIN
	--print 'Existing customer'
	--print @CustomerNumberExisting
  select * FROM Customer WITH (NOLOCK) WHERE customernumber = @CustomerNumberExisting
END

DECLARE @Establishdate DATETIME
DECLARE @CurrentDateStr VARCHAR(8)

SELECT @Establishdate = GETDATE()
SET @CurrentDateStr = CONVERT (CHAR (8), @Establishdate, 112)

IF ISNULL(@CustomerNumberExisting, 0) = 0
BEGIN
  EXEC GetNextNumber 'CUSTOMERIDENTITY', 1, @Customernumber OUTPUT
  IF @CustomerTypeNumber = 1
  BEGIN
     INSERT INTO Customer (CustomerNumber, CustomerTypeNumber, Firstname, Lastname, 
       Address1, Address2, Zipcodeoverwrite, Cityoverwrite, CountryNumber, Phonenumber, CellPhonenumber,
       --Email, 
       Segmentnumber, PointOfSaleNumber, GuestTypeNumber, 
       PurposeCodeNumber, Salesmannumber, Establishdate, Archivedate)  
     VALUES(@Customernumber, 1, @Firstname,  @Lastname,
      @Address1, @Address2, @Zipcodeoverwrite, @City, @Countrynumber, @Phonenumber, @Cellphonenumber, 
      -- @Email, 
      @SegmentNumber, @PointOfSaleNumber, @GuestTypeNumber, 
      @PurposeCodeNumber, @UserIdNumber, @CurrentDateStr, @CurrentDateStr)  

     IF @ANoNews = 1
       UPDATE Customer SET NoNews = 1 WHERE CustomerNumber = @Customernumber
     --@UserName VARCHAR(50)='', @Password VARCHAR(50)=''
     IF @UserName <> ''     
        INSERT INTO InetCustomer (CustomerNumber, Customerlogin, CustomerPassword) VALUES (@Customernumber,@UserName,@Password) 

  END
  ELSE IF @CustomerTypeNumber = 2 
  BEGIN
     INSERT INTO Customer (CustomerNumber, CustomerTypeNumber, Firstname, Lastname, 
       Address1, Address2, Zipcodeoverwrite, Cityoverwrite, CountryNumber, Phonenumber, CellPhonenumber, 
       -- Email, 
       Segmentnumber, PointOfSaleNumber, GuestTypeNumber, 
       PurposeCodeNumber, Salesmannumber, Establishdate, Archivedate, CompanyForm)  
     VALUES(@Customernumber, 2, @Firstname,  @Companyname, 
       @Address1, @Address2, @Zipcodeoverwrite, @City, @Countrynumber, @Phonenumber, @Cellphonenumber, 
       -- @Email, 
       @SegmentNumber, @PointOfSaleNumber, @GuestTypeNumber, 
       @PurposeCodeNumber, @UserIdNumber, @CurrentDateStr, @CurrentDateStr, @eanNumber)
      
     IF @ANoNews = 1
       UPDATE Customer SET NoNews = 1 WHERE CustomerNumber = @Customernumber
  END  
  IF @CustomerTypeNumber BETWEEN 1 AND 2 AND ISNULL(@EMail, '') <> ''  
  BEGIN
    INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 0)	
    INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 1)	
    INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 2)	
    INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 3)
    INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 4)	
    INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 5)	
    INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 6)	
    INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 7) 
  END  

  UPDATE ReservationAddress SET Link = @Customernumber  WHERE AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=0)
  AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXtab WITH (NOLOCK) WHERE AddressTypeNumber = @CustomerTypeNumber)
END
ELSE
  BEGIN
    UPDATE ReservationAddress SET Link = @CustomerNumberExisting  WHERE AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=0)
     AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXtab WITH (NOLOCK) WHERE AddressTypeNumber = @CustomerTypeNumber)
  END


