USP_DATALIST_ADDRESSBOOKFAF

CREATE procedure dbo.USP_DATALIST_ADDRESSBOOKFAF 
(
    @CLIENTUSERSID int, 
    @FULLNAME nvarchar(100) = '',
    --@CONTACTTYPECODELIST nvarchar(25) = '',

    @PAGENUMBER int = 1,
    @TOTALROWS int = 15,
    @EVENTID uniqueidentifier = null
)
as

set nocount on;

declare @GroupData table
(
    ISGROUP bit DEFAULT(1),
    TeamName nvarchar(100),
    TeamMemberNum  int,
    ID uniqueidentifier,
    FIRSTNAME nvarchar(255),
    MIDDLENAME nvarchar(255),
    LASTNAME nvarchar(255),
    PREFERREDCOMMUNICATION nvarchar(255),
    --CONTACTTYPE nvarchar(255),

    --CONTACTTYPECODE tinyint,

    SALUTATION  nvarchar(255),
    EMAILADDRESS  nvarchar(255),
    ADDRESSBLOCK  nvarchar(255),
    CITY  nvarchar(100),
    STATEID uniqueidentifier,
    POSTCODE nvarchar(24),
    COUNTRYID uniqueidentifier,
    CELLPHONE nvarchar(25),
    TEXTMESSAGESEND bit,
    HOMEPHONE nvarchar(25),
    CONSTITUENTID uniqueidentifier,
    BUSINESSPHONE nvarchar(25),
    BUSINESSADDRESS nvarchar(300),
    SECONDARYEMAILADDRESS nvarchar(300)

    , SECONDARYCITY nvarchar(100)  
    , SECONDARYSTATEID uniqueidentifier  
    , SECONDARYPOSTCODE nvarchar(24)  
    , SECONDARYCOUNTRYID uniqueidentifier  

  , COMPANYNAME nvarchar(255) 
  , NOTES nvarchar(1000)  

)

declare @CONSTITUENTID uniqueidentifier
set @CONSTITUENTID=dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

insert into @GroupData(TEAMNAME,TEAMMEMBERNUM) 
EXEC dbo.USP_DATALIST_ADDRESSBOOKFAFGROUP @CONSTITUENTID,@EVENTID

set nocount on;

WITH [Numbers](num)  
 AS  
 (     
  SELECT 0  
  UNION ALL  
  SELECT ROW_NUMBER() OVER(ORDER BY [id]) as num FROM sys.syscolumns 
 )  
 -- Split given string using given delimiter  

 ,[Split](Num, String)  
 AS  
 (  
  SELECT   
   Num  , 'dave change this'
   --,SUBSTRING 

   --(  

   -- @CONTACTTYPECODELIST,

   -- Num,  

   -- CASE CHARINDEX(',', @CONTACTTYPECODELIST, Num)  

   --  WHEN 0 THEN LEN(@CONTACTTYPECODELIST) - Num + 1  

   -- ELSE CHARINDEX(',', @CONTACTTYPECODELIST, Num) - Num 

   --  END  

  -- ) AS String  

  FROM [Numbers]  
  --WHERE Num <= LEN(@CONTACTTYPECODELIST)  

  --AND  (SUBSTRING(@CONTACTTYPECODELIST, Num - 1, 1) = ',' OR Num = 0) 

 )

 INSERT INTO @GroupData 
 SELECT 0,NULL,NULL,
    ABF.[ID],
    ABF.[FIRSTNAME],
    ABF.[MIDDLENAME],
    ABF.[LASTNAME],
    ABF.[PREFERREDCOMMUNICATION],
    --ABF.[CONTACTTYPE],

    --ABF.[CONTACTTYPECODE],

    ABF.[SALUTATION],
    ABF.[EMAILADDRESS],
    ABF.[ADDRESSBLOCK],
    ABF.[CITY],
    ABF.[STATEID],
    ABF.[POSTCODE],
    ABF.[COUNTRYID],
    ABF.[CELLPHONE],
    ABF.[TEXTMESSAGESEND],
    ABF.[HOMEPHONE],
    ABF.CONSTITUENTID,
    ABF.BUSINESSPHONE,
    ABF.BUSINESSADDRESS,
    ABF.SECONDARYEMAILADDRESS
    , ABF.SECONDARYCITY
    , ABF.SECONDARYSTATEID
    , ABF.SECONDARYPOSTCODE
    , ABF.SECONDARYCOUNTRYID

  , ABF.COMPANYNAME
    , ABF.NOTES

    FROM dbo.ADDRESSBOOKFAF ABF
    WHERE ABF.ClientUsersID = @ClientUsersID 
    AND (RTRIM(LTRIM(ABF.FirstName + ' ' + ABF.LastName)) like '' + RTRIM(LTRIM(@FULLNAME)) + '%')  
    --AND (CONTACTTYPECODE IN (SELECT string FROM [Split]) OR @CONTACTTYPECODELIST  = '')


SET NOCOUNT ON  ;
 WITH c as
  (
    SELECT row_number() over (order by FIRSTNAME) as sequence,     
    CASE WHEN 1=1 THEN (SELECT COUNT(*) FROM @GroupData) END AS TOTALCOUNT,*
    FROM @GroupData 
)


SELECT TOP (@TOTALROWS)
    ABF.ISGROUP,
    ABF.TeamName,
    ABF.TeamMemberNum,
    ABF.TOTALCOUNT,
    ABF.[ID],
    ABF.[FIRSTNAME],
    ABF.[MIDDLENAME],
        ABF.[LASTNAME],
        ABF.[PREFERREDCOMMUNICATION],
    --ABF.[CONTACTTYPE],

    --ABF.[CONTACTTYPECODE],

    ABF.[SALUTATION],
    ABF.[EMAILADDRESS],
    ABF.[ADDRESSBLOCK],
    ABF.[CITY],
    ABF.[STATEID],
    ABF.[POSTCODE],
        ABF.[COUNTRYID],
        ABF.[CELLPHONE],
        ABF.[TEXTMESSAGESEND],
        ABF.[HOMEPHONE],

        ABF.CONSTITUENTID,
        ABF.BUSINESSPHONE,
        ABF.BUSINESSADDRESS,
        ABF.SECONDARYEMAILADDRESS

    , ABF.SECONDARYCITY
    , ABF.SECONDARYSTATEID
    , ABF.SECONDARYPOSTCODE
    , ABF.SECONDARYCOUNTRYID

  , ABF.COMPANYNAME
    , ABF.NOTES

    FROM C ABF
  WHERE sequence > (@PAGENUMBER-1) * @TOTALROWS OR @PAGENUMBER < 1
  order by ISGROUP ASC
  ;