CREATE PROCEDURE [dbo].[OnlineFindCustomer] 
 @Phonenumber VARCHAR(100), 
 @Email VARCHAR(100), 
 @SearchByPassword BIT = 0,
 @HotelNumber INT = 0 AS
SET NOCOUNT ON

/*
    Created by Troels Buch Nov 2005
    
    Changes:
      vd 20181018: EANs now located in a seperate table. 
      VD20130902: Mail addresses now situated in a separate table - KUN i 8.1+!
      TB20130614: Added @HotelNumber 		
      TB20070125: Added NOLOCK to SELECT @Countrynumber
      TB20070125: Added - AND Customer.MARKASDELETED <> 1
      NLB20061205: Added 'CompanyFrom' (EAN) to resultset
      TB20061103: Added @SearchByPassword	
      TB20060919: Changed  INNER JOIN to LEFT JOIN
      TB20060918: Added login and password
      KJ 20060830: Added Rooms table to get roomnumber- used for owner login
      TB20051227: Added function QualifyPhoneNumber to find @Countrynumber
      TB20051223: Added function QualifyPhoneNumber
      TB20051221: Added check om cellphonenumber for finding @Countrynumber     

    NOTES: 	- 	
*/

DECLARE @NewPhoneNumber VARCHAR(30)
SET @NewPhoneNumber = dbo.QualifyPhoneNumber (@Phonenumber, 1)

DECLARE @Countrynumber INT
SELECT @Countrynumber = Countrynumber 
FROM Customer WITH (NOLOCK) 
  LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
WHERE 
 (REPLACE(dbo.QualifyPhoneNumber (Phonenumber, @Countrynumber), '+', '') = @Phonenumber OR 
 (REPLACE(dbo.QualifyPhoneNumber (Cellphonenumber, @Countrynumber), '+', '')) = @Phonenumber) AND
  MailAddresses.MailAddress = @Email

IF @SearchByPassword = 0
  SET @Phonenumber = dbo.QualifyPhoneNumber (@Phonenumber, @Countrynumber)

IF @SearchByPassword = 0
BEGIN -- Use phone and email to find customer
  SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, Countrynumber, 
   (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE Countrynumber = @Countrynumber) AS CountryName, 
    Phonenumber, Cellphonenumber, Establishdate, Segmentnumber, PointOfSaleNumber, 
    GuestTypeNumber, PurposeCodeNumber, ArchiveDate, Salesmannumber, CustomerTypeNumber, Rooms.Number, 
    InetCustomer.Customerlogin, InetCustomer.CustomerPassword, MailAddresses.MailAddress, CustomerEANs.EAN
  FROM Customer WITH (NOLOCK) 
    LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
    LEFT JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber AND rooms.HotelNumber= @HotelNumber
    LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
	LEFT JOIN CustomerEANs WITH (NOLOCK) ON CustomerEANs.CustomerNumber = Customer.CustomerNumber AND CustomerEANs.EANType = 0
  WHERE 
   (REPLACE(dbo.QualifyPhoneNumber (Phonenumber, @Countrynumber), '+', '') = @Phonenumber OR 
   (REPLACE(dbo.QualifyPhoneNumber (Cellphonenumber, @Countrynumber), '+', '')) = @Phonenumber) AND 
    MailAddresses.MailAddress = @Email AND Customer.MARKASDELETED <> 1
END
ELSE
BEGIN -- use inetlogin and password to login
   SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, Countrynumber, 
  (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE Countrynumber = @Countrynumber) AS CountryName, 
   Phonenumber, Cellphonenumber, Establishdate, Segmentnumber, PointOfSaleNumber, 
   GuestTypeNumber, PurposeCodeNumber, ArchiveDate, Salesmannumber, CustomerTypeNumber, Rooms.Number, 
   InetCustomer.Customerlogin, InetCustomer.CustomerPassword, MailAddresses.MailAddress, CustomerEANs.EAN
   FROM Customer WITH (NOLOCK) 
     LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
     LEFT JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber AND rooms.HotelNumber= @HotelNumber
     LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
	 LEFT JOIN CustomerEANs WITH (NOLOCK) ON CustomerEANs.CustomerNumber = Customer.CustomerNumber AND CustomerEANs.EANType = 0
   WHERE 
     InetCustomer.Customerlogin = @Phonenumber AND InetCustomer.CustomerPassword = @Email AND Customer.MARKASDELETED <> 1
END
GO

