/* vd20190521 New way of searching customer  - Outer procedure */

CREATE PROCEDURE [dbo].[GettingCustomerSearchResult]
  @IncludeDeleted BIT,
  @IncludeLinkRecords BIT,
  @ExcludeSalesChannels BIT,     
  @SwitchNameOrder BIT,
  @RoomNumber INTEGER,
  @CustomerType SMALLINT, 
  @CustomerNumber INTEGER,
 -- @HostelID INTEGER,
  @FirstName VARCHAR(50),
  @LastName VARCHAR(60),
  @Phone VARCHAR(30),
  @Mobile VARCHAR(30),
  @MemberCard VARCHAR(128),
  @MemberCardEncrypted VARCHAR(128),
  @IATANumber VARCHAR(30),
  @CompanyNumber VARCHAR(30),
  @DebtorAccount VARCHAR(20),
  @MailAddress VARCHAR(100),
  @EAN VARCHAR(20),
  @CardString VARCHAR(255),
  @Criteria NVARCHAR(MAX)
AS

DECLARE @CALLING_PROCEDURE NVARCHAR(MAX)

BEGIN

--DROP TABLE IF EXISTS #RESULTSET;

  IF EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
           WHERE id = object_id(N'[dbo].[#RESULTSET]') AND 
		         OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE #RESULTSET

  CREATE TABLE #RESULTSET
  (
    FullName VARCHAR(110),
    Link INTEGER,
    LinkLevel TINYINT,
    CustomerNumber INTEGER,
    CustomerTypeNumber SMALLINT,
    MarkAsDeleted BIT,
    NBDate VARCHAR(8),
    Title VARCHAR(100),
    FirstName VARCHAR(50),
    LastName VARCHAR(60),
    Address1 VARCHAR(100),
    Address2 VARCHAR(100),
    Address3 VARCHAR(100),
    ZipCodeOverWrite VARCHAR(10),
    CityOverWrite VARCHAR(50),
    CountryNumber INTEGER,
    PhoneNumber VARCHAR(30),
    CellPhoneNumber VARCHAR(30),
    FaxNumber VARCHAR(30),
    WWWAddress VARCHAR(100),
    CompanyNumber VARCHAR(30),
    IATANumber VARCHAR(30),
    MemberCardNumber VARCHAR(128),
    ExternDebitorNumber INTEGER,
    FlagA VARCHAR(300),
    FlagB VARCHAR(300),
    FlagC VARCHAR(300),
    FlagD VARCHAR(300),
    MappedCustomerNumber INTEGER,
    DebtorAccount VARCHAR(20),
    Depersonalised BIT,
    MailAddress VARCHAR(100),
    EAN VARCHAR(20),
    NOTE1 NVARCHAR(MAX),
    NOTE2 NVARCHAR(MAX),
    NOTE3 NVARCHAR(MAX),
    GDPRMailDate SMALLDATETIME,
    USERIDCODE VARCHAR(20),
    Segments1 VARCHAR(30),
    GuestTypes1 VARCHAR(50),
    Purpose VARCHAR(50),
    PointOfSale VARCHAR(30),
    CountryCode VARCHAR(3),
    CountryName VARCHAR(30),
    --HostelSite VARCHAR(100),
    --HostelID INTEGER,
    SplitBill VARCHAR(20),
    Payment VARCHAR(50),
    OwnerRoomNumber INTEGER,
    InetLogin VARCHAR(50),
    InetPassword VARCHAR(50),
    CardString VARCHAR(255),
    GDPRAction DATETIME
  )

  SET @CALLING_PROCEDURE = 'EXEC CustomerSearchResult ' + 
    CAST(@IncludeDeleted AS CHAR) + ', ' +
	CAST(@IncludeLinkRecords AS CHAR) + ', ' +
	CAST(@ExcludeSalesChannels AS CHAR) + ', ' +
	CAST(@SwitchNameOrder AS CHAR) + ', ' +
	CAST(@RoomNumber AS CHAR) + ', ' +
	CAST(@CustomerType AS CHAR) + ', ' +
	CAST(@CustomerNumber AS CHAR) + ', ' +
 -- CAST(@HostelID AS CHAR) + ', ' +
	 '''' +
	@FirstName + ''', ''' + 
	@LastName + ''', ''' + 
    @Phone + ''', ''' + 
    @Mobile + ''', ''' + 
    @MemberCard + ''', ''' + 
    @MemberCardEncrypted + ''', ''' + 
    @IATANumber + ''', ''' + 
    @CompanyNumber + ''', ''' + 
    @DebtorAccount + ''', ''' + 
    @MailAddress + ''', ''' + 
    @EAN + ''', ''' + 
    @CardString + ''', ''' + 
    REPLACE(@Criteria, CHAR(39), CHAR(39) + CHAR(39)) + ''''

  EXEC (@CALLING_PROCEDURE)	

  SELECT * FROM #RESULTSET (NOLOCK)
  ORDER BY Link, LinkLevel

--DROP TABLE IF EXISTS #RESULTSET;

  IF EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
           WHERE id = object_id(N'[dbo].[#RESULTSET]') AND 
		         OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE #RESULTSET

END



