CREATE PROCEDURE [dbo].[OnlineCustomerLogin] @UsernamePhone  VARCHAR(100), @PasswordEmail  VARCHAR(100), @ACustomerNumber INT = 0, @Loginmode INT = 0, @CustomerTypeNumber INT = 0, @AHotelNumber INT = 0 AS
SET NOCOUNT ON

/*
	Created by Troels Buch Feb 2007
	
	Changes -
	SG 20160822: Changed CustomerFieldsPerHotel at loginmode=6
	TBU20160719: Removed CustomerFieldsPerHotel at loginmode=0
	SG 20160628: Added  DiscountAll, DiscountHalls
	SG 20151124: Added AND Customer.CustomerTypeNumber = 8 for loginmode 9 and 10
	TBU20151110: Changed loginmode=7 - Changes to CompanyCo login
	SG 20150825: Added loginmode 11 (SSO). Email must be unik (not guarantied by Picasso)
	TBU20150508: Removed check for contactperson on Loginmode=0 - Error
	TBU20150508: Added check for contactperson on Loginmode=0
	TBU20150219: Added FlagD at LoginMode=8
	TBU20150205: Added FlagD
	SG20150106: Loginmode 10: Don't use countrycode
    SG20141202: Added loginmode 9 and 10.
    SG20141119: (8.1) Email flyttet til tabel MailAddresses
    SG20141106: Added loginmode 8 for contactpersons
	KS20140523: ProvisionAccount changed to IsNull(Customer.ProvisionAccount, 0 ) AS ProvisionAccount for Login Mode 6
	KS20140523: Added OnlineBookingAllowed for Login Mode 6
	KJ20131023: Discount per hotel
	TB20130919: Changed @Loginmode = 7
	TB20130916: Added ProvisionBill
	TB20130905: Added DiscountRooms
	TB20130809: Added loginmode=7
	TB20130321: Added OnlineBookingAllowed
	TB20120524: Added ProvisionPostOnGuest, ProvisionAccount to loginmode 0
	TB20120117: Added ProvisionPostOnGuest, ProvisionAccount 
	LJE20110321: Added "AND Customer.CustomerTypeNumber = 8" for loginmode=4
	TB20090713: Added Order By for loginmode 4
	TB20090707: Added LoginMode 6
	TB20090612: Changed @Loginmode = 4 - Added @CustomerTypeNumber
	MF20080108: Fixed CountryName lookup for LoginMode 0
	TB20071108: Added LoginMod 3+4
	TB20070215: Added MemberCardNumber
	
	@LoginMode:	
	0=	User: Inet login tesxfield on customer  
		Password: Password textfield on customer
	1=	User: Phone  
		Password: email			
	2=	User: CPR/ Card No.
		Password: Phone or email
	3=	Find customer that link to a customer. Using @ACustomerNumber
	4=	Find contactperson that link to a company/firm
	5=  Login with customernumber and customertypenumber - @PasswordEmail = customertypenumber
	6=	Find customer with customernumber
	7=  Find linked customers
	8=  Find customer with customernumber (also contactperson)
	9=  Login as contact using inet login and password
	10= Login as contact using User: Phone and password: email (phone without countrycode)
	11= Login as contact using only email (only for Single Sign on, external user verification)
*/
DECLARE @MemberCardNumber VARCHAR(100)
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), '+', '') = @UsernamePhone 
      OR (REPLACE(dbo.QualifyPhoneNumber (Cellphonenumber, @Countrynumber), '+', '')) = @UsernamePhone)  AND MailAddresses.MailAddress = @PasswordEmail

IF @Loginmode = 0
BEGIN --  use inetlogin and password to login
  SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, --Countrynumber, (SELECT CountryName FROM Countries (NOLOCK) WHERE Countrynumber = @Countrynumber)AS CountryName, 
   Phonenumber,
   Cellphonenumber, MailAddresses.MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm,Countries.CountryNumber, Countries.CountryName,
   IsNull(Customer.ProvisionPostOnGuest, 0) AS ProvisionPostOnGuest, IsNull(Customer.ProvisionAccount, 0 ) AS ProvisionAccount, OnlineBookingAllowed, 
   IsNull(CustomerFieldsPerHotel.DiscountRooms, 0) AS DiscountRooms,
   IsNull(CustomerFieldsPerHotel.DiscountAll, 0) AS DiscountAll, 
   IsNull(CustomerFieldsPerHotel.DiscountHalls, 0) AS DiscountHalls, 
   IsNull(Customer.ProvisionBill, 0 ) AS ProvisionBill
   FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
   LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber
   LEFT OUTER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON CustomerFieldsPerHotel.CustomerNumber = Customer.CustomerNumber
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   WHERE InetCustomer.Customerlogin = @UsernamePhone AND  InetCustomer.CustomerPassword = @PasswordEmail
   AND Customer.MARKASDELETED <> 1 
   ORDER BY IsNull(CustomerFieldsPerHotel.DiscountRooms, 0) 
   --AND CustomerFieldsPerHotel.HotelNumber = @AHotelNumber   
   --AND ( (CustomerFieldsPerHotel.HotelNumber = 0 AND CustomerTypeNumber <> 8) OR (CustomerTypeNumber = 8)   )
   
END
ELSE IF @Loginmode = 1
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, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm, OnlineBookingAllowed
   FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   WHERE (REPLACE(dbo.QualifyPhoneNumber (Phonenumber, @Countrynumber), '+', '') = @UsernamePhone OR (REPLACE(dbo.QualifyPhoneNumber (Cellphonenumber, @Countrynumber), '+', '')) = @UsernamePhone)  AND MailAddress = @PasswordEmail
   AND Customer.MARKASDELETED <> 1
END
ELSE IF @Loginmode = 2
BEGIN -- use cpr/card 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, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber,  InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm
   FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber 
   WHERE   (MemberCardNumber = @UsernamePhone) -- @UsernamePhone = cpr/card
   AND ( PHONENUMBER = @PasswordEmail OR MailAddress = @PasswordEmail ) --  @PasswordEmail Can be both phonenumber or email
   AND Customer.MARKASDELETED <> 1
END
ELSE IF @Loginmode = 3
BEGIN
  SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, Customer.Countrynumber, /* (SELECT CountryName FROM Countries (NOLOCK) WHERE Countries.Countrynumber = Customer.CountryNumber) AS CountryName, */ Phonenumber,
  Cellphonenumber, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
  Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm, Countries.CountryName
  FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
  LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
  LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
  LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber
  INNER JOIN CustomerLinks ON Customer.CustomerNumber = CustomerLinks.CustomerNumber   WHERE CustomerLinkNumber  = @ACustomerNumber
  AND Customer.MARKASDELETED <> 1 
END
ELSE IF @Loginmode = 4
BEGIN
  SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, Customer.Countrynumber, /* (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE Countries.Countrynumber = Customer.CountryNumber) AS CountryName, */ Phonenumber,
  Cellphonenumber, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
  Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm, Countries.CountryName
  FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
  LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
  LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
  LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber
  INNER JOIN CustomerLinks ON Customer.CustomerNumber = CustomerLinks.CustomerLinkNumber
WHERE CustomerLinks.CustomerNumber = @ACustomerNumber AND ((@CustomerTypeNumber > 0 AND CustomerTypeNumber = @CustomerTypeNumber) OR (@CustomerTypeNumber = 0))
  AND Customer.MARKASDELETED <> 1 
  AND Customer.CustomerTypeNumber = 8
  ORDER BY Lastname, Customer.Customernumber
END
ELSE IF @Loginmode = 5
BEGIN -- Login with customernumber and customertypenumber - @PasswordEmail = customertypenumber
  SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, --Countrynumber, (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE Countrynumber = @Countrynumber)AS CountryName, 
   Phonenumber,
   Cellphonenumber, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm,Countries.CountryNumber, Countries.CountryName
   FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
   LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber
   WHERE Customer.MARKASDELETED <> 1 and Customer.CustomerNumber  = @ACustomerNumber AND customertypenumber = @PasswordEmail
END
ELSE IF @Loginmode = 6 
BEGIN
   SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, --Countrynumber, (SELECT CountryName FROM Countries (NOLOCK) WHERE Countrynumber = @Countrynumber)AS CountryName, 
   Phonenumber,
   Cellphonenumber, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm,Countries.CountryNumber, Countries.CountryName,
   Customer.ProvisionPostOnGuest, IsNull(Customer.ProvisionAccount, 0 ) AS ProvisionAccount, OnlineBookingAllowed, 
   IsNull(CustomerFieldsPerHotel.DiscountRooms, 0) AS DiscountRooms, 
   IsNull(CustomerFieldsPerHotel.DiscountAll, 0) AS DiscountAll, 
   IsNull(CustomerFieldsPerHotel.DiscountHalls, 0) AS DiscountHalls, 
   IsNull(Customer.ProvisionBill, 0 ) AS ProvisionBill
   FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
   LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber
   LEFT OUTER JOIN CustomerFieldsPerHotel WITH (NOLOCK) ON CustomerFieldsPerHotel.CustomerNumber = Customer.CustomerNumber
   WHERE Customer.MARKASDELETED <> 1 and Customer.CustomerNumber  = @ACustomerNumber
   AND IsNull(CustomerFieldsPerHotel.HotelNumber, @AHotelNumber) = @AHotelNumber 
END
ELSE IF @Loginmode = 7
BEGIN
	SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, Customer.Countrynumber, Phonenumber,
	Cellphonenumber, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
	Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, 
	CompanyForm, Countries.CountryName  
	FROM CUSTOMER WITH (NOLOCK)  
	LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
	LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
    LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
    LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber 
	WHERE CUSTOMER.Customernumber IN (SELECT CustomerLinkNumber FROM CustomerLinks WITH (NOLOCK) 
	INNER JOIN customer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = CustomerLinks.CustomerNumber
	INNER JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0) 
	WHERE IsNull(Customer.Customernumber, 0) = @ACustomerNumber
	AND IsNull(InetCustomer.Customerlogin, 0) = @UsernamePhone
	AND IsNull(InetCustomer.CustomerPassword, 0) = @PasswordEmail )	
	AND Customer.MARKASDELETED <> 1

	/*SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, Customer.Countrynumber, Phonenumber,
	Cellphonenumber, Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
	Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, 
	CompanyForm, Countries.CountryName  
	FROM CUSTOMER WITH (NOLOCK)  
	LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
    LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
    LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber 
	WHERE CUSTOMER.Customernumber IN (SELECT CustomerLinkNumber FROM CustomerLinks WITH (NOLOCK) 
	INNER JOIN customer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = CustomerLinks.CustomerNumber
	INNER JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)  
	WHERE IsNull(Customer.Customernumber, 0) = @ACustomerNumber
	AND IsNull(InetCustomer.Customerlogin, 0) = @UsernamePhone
	AND IsNull(InetCustomer.CustomerPassword, 0) = @PasswordEmail )	
	AND Customer.MARKASDELETED <> 1*/
END ELSE IF @Loginmode = 8 
BEGIN
   SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, --Countrynumber, (SELECT CountryName FROM Countries (NOLOCK) WHERE Countrynumber = @Countrynumber)AS CountryName, 
   Phonenumber,
   Cellphonenumber, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm,Countries.CountryNumber, Countries.CountryName,
   Customer.ProvisionPostOnGuest, IsNull(Customer.ProvisionAccount, 0 ) AS ProvisionAccount, OnlineBookingAllowed, FLAGD, 
   0 AS DiscountRooms, 
   0 AS DiscountAll, 
   0 AS DiscountHalls, 
   IsNull(Customer.ProvisionBill, 0 ) AS ProvisionBill
   FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
   LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber
   WHERE Customer.MARKASDELETED <> 1 and Customer.CustomerNumber  = @ACustomerNumber
END ELSE IF @Loginmode = 9
BEGIN --  use inetlogin and password to login, contactperson
  SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, --Countrynumber, (SELECT CountryName FROM Countries (NOLOCK) WHERE Countrynumber = @Countrynumber)AS CountryName, 
   Phonenumber,
   Cellphonenumber, MailAddresses.MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm,Countries.CountryNumber, Countries.CountryName,
   IsNull(Customer.ProvisionPostOnGuest, 0) AS ProvisionPostOnGuest, IsNull(Customer.ProvisionAccount, 0 ) AS ProvisionAccount, OnlineBookingAllowed, 
   0 AS DiscountRooms,
   0 AS DiscountAll, 
   0 AS DiscountHalls, 
   IsNull(Customer.ProvisionBill, 0 ) AS ProvisionBill, FLAGD
   FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
   LEFT OUTER JOIN Countries WITH (NOLOCK) ON Customer.CountryNumber = Countries.CountryNumber
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   WHERE InetCustomer.Customerlogin = @UsernamePhone AND  InetCustomer.CustomerPassword = @PasswordEmail
   AND Customer.MARKASDELETED <> 1  
   AND Customer.CustomerTypeNumber = 8 
END ELSE IF @Loginmode = 10
BEGIN --  Use phone and email to to login, contactperson
   SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, Countrynumber, (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE Countrynumber = @Countrynumber) AS CountryName, Phonenumber,
   Cellphonenumber, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm, OnlineBookingAllowed, FLAGD
   FROM Customer WITH (NOLOCK) LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   --WHERE (REPLACE(dbo.QualifyPhoneNumber (Phonenumber, @Countrynumber), '+', '') = @UsernamePhone OR (REPLACE(dbo.QualifyPhoneNumber (Cellphonenumber, @Countrynumber), '+', '')) = @UsernamePhone)  AND MailAddress = @PasswordEmail
   WHERE (Phonenumber = @UsernamePhone OR Cellphonenumber = @UsernamePhone)  AND MailAddress = @PasswordEmail
   AND Customer.MARKASDELETED <> 1
   AND Customer.CustomerTypeNumber = 8 
END ELSE IF @Loginmode = 11
BEGIN --  Use email to to login, contactperson (only used for single sign on, user already verified)
   SELECT Customer.Customernumber, Firstname, Lastname, Address1, Address2, ZipcodeOverwrite, Cityoverwrite, Countrynumber, (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE Countrynumber = @Countrynumber) AS CountryName, Phonenumber,
   Cellphonenumber, MailAddress as Email, Establishdate, Segmentnumber, PointOfSaleNumber, GuestTypeNumber, PurposeCodeNumber, ArchiveDate,
   Salesmannumber, CustomerTypeNumber, Rooms.Number, MemberCardNumber, InetCustomer.Customerlogin, InetCustomer.CustomerPassword, CompanyForm, OnlineBookingAllowed, FLAGD
   FROM Customer WITH (NOLOCK) 
   LEFT JOIN InetCustomer WITH (NOLOCK) ON IsNull(Customer.Customernumber, 0) = IsNull(InetCustomer.Customernumber, 0)
   LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.CustomerNumber = Customer.CustomerNumber
   LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
   WHERE  MailAddresses.MailAddress = @PasswordEmail AND Customer.MARKASDELETED <> 1
END


