USP_DESIGNATION_GETHIERARCHY

CREATE procedure [dbo].[USP_DESIGNATION_GETHIERARCHY] (
  @ACTIVEONLY bit = 0
  ,@DESIGNATIONID uniqueidentifier = null
  ,@DESIGNATIONLEVELNAME nvarchar(100) = null
  ,@DESIGNATIONLEVELTYPEID uniqueidentifier = null
  ,@LOOKUPID nvarchar(512) = null
  ,@COMBINEDSEARCH nvarchar(200) = null
  ,@SORTFIELD tinyint = null
  ,@SORTDIR tinyint = null
  ,@DESIGNATIONREPORT1CODEID uniqueidentifier = null
  ,@DESIGNATIONREPORT2CODEID uniqueidentifier = null
  ,@PURPOSEID uniqueidentifier = null
  )
as
set nocount on;

--NOTE: this is the nth iteration of generating this result set - it handles very large hierarchies in a matter of seconds

--other, more traditional (or eligant) approaches have failed to perform adequately - please take this into account before

--making changes to this procedure

declare @NOMATCH_MASK tinyint = 0;
declare @ISREQUIREDANCESTOR_MASK tinyint = 1;
declare @MATCHESCRITERIA_MASK tinyint = 2;
declare @ISINHIERARCHY_MASK tinyint = 4;
declare @ROOTLEVEL tinyint = 0;
declare @NOTFOUND bit = 1;
declare @ROOTID1 uniqueidentifier;
declare @ROOTID2 uniqueidentifier;
declare @ROOTID3 uniqueidentifier;
declare @ROOTID4 uniqueidentifier;
declare @ROOTID5 uniqueidentifier;
declare @EMPTY uniqueidentifier = '00000000-0000-0000-0000-000000000000';

set @SORTDIR = COALESCE(@SORTDIR, 0);

if coalesce(@DESIGNATIONID, @EMPTY) <> @EMPTY
  select @NOTFOUND = 0
    ,@ROOTID1 = DESIGNATIONLEVEL1ID
    ,@ROOTID2 = DESIGNATIONLEVEL2ID
    ,@ROOTID3 = DESIGNATIONLEVEL3ID
    ,@ROOTID4 = DESIGNATIONLEVEL4ID
    ,@ROOTID5 = DESIGNATIONLEVEL5ID
  from dbo.DESIGNATION
  where ID = @DESIGNATIONID
else
  set @NOTFOUND = 0;

set @ACTIVEONLY = COALESCE(@ACTIVEONLY, 0);

--sanitize the search values, adding mask chars at beginning and end so it performs a 'contains' style match

if @DESIGNATIONLEVELNAME is not null
  set @DESIGNATIONLEVELNAME = dbo.UFN_TEXTSEARCH_SANITIZE(@DESIGNATIONLEVELNAME, '/', default, default, default);

if @LOOKUPID is not null
  set @LOOKUPID = dbo.UFN_TEXTSEARCH_SANITIZE(@LOOKUPID, '/', default, default, default);

if @COMBINEDSEARCH is not null
  set @COMBINEDSEARCH = dbo.UFN_TEXTSEARCH_SANITIZE(@COMBINEDSEARCH, '/', default, default, default);

declare @T table (
  ID uniqueidentifier unique
  ,PARENTID uniqueidentifier unique (
    ID
    ,PARENTID
    )
  ,DESIGNATIONLEVELID uniqueidentifier
  ,ROOTDESIGNATIONLEVELID uniqueidentifier
  ,[LEVEL] tinyint
  ,SORTKEY nvarchar(50)
  ,PATHID nvarchar(180) primary key --this primary key is EXTREMELY important for performance!

  ,PARENTPATHID nvarchar(180) unique (
    PATHID
    ,PARENTPATHID
    )
  ,SORTVALUE nvarchar(512)
  ,LEVELNAME nvarchar(512)
  ,PATHNAME nvarchar(max)
  ,ISACTIVE bit
  ,ISREVENUEDESIGNATION bit
  ,USERID nvarchar(512)
  ,VANITYNAME nvarchar(512)
  ,DESIGNATIONREPORT1CODEID uniqueidentifier
  ,DESIGNATIONREPORT2CODEID uniqueidentifier
  ,DESIGNATIONLEVELTYPEID uniqueidentifier
  ,DESIGNATIONLEVELTYPE nvarchar(100)
  ,DESIGNATIONLEVELTYPEHIERARCHYITEMID uniqueidentifier
  ,SYSTEMGENERATED bit
  ,MEETSCRITERIA tinyint unique (
    ID
    ,[LEVEL]
    ,MEETSCRITERIA
    )
  );

if @NOTFOUND = 0
begin
  insert into @T (
    ID
    ,ROOTDESIGNATIONLEVELID
    ,PATHID
    ,PARENTPATHID
    ,DESIGNATIONLEVELID
    ,SORTVALUE
    ,[LEVEL]
    ,LEVELNAME
    ,PATHNAME
    ,ISACTIVE
    ,ISREVENUEDESIGNATION
    ,USERID
    ,VANITYNAME
    ,DESIGNATIONREPORT1CODEID
    ,DESIGNATIONREPORT2CODEID
    ,DESIGNATIONLEVELTYPEID
    ,DESIGNATIONLEVELTYPE
    ,DESIGNATIONLEVELTYPEHIERARCHYITEMID
    ,SYSTEMGENERATED
    ,MEETSCRITERIA
    )
 select D.ID
    ,D.DESIGNATIONLEVEL1ID
    ,case 
      when (D.DESIGNATIONLEVEL5ID is not null)
        then CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL2ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL3ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL4ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL5ID, @EMPTY))
      when (D.DESIGNATIONLEVEL4ID is not null)
        then CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL2ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL3ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL4ID, @EMPTY))
      when (D.DESIGNATIONLEVEL3ID is not null)
        then CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL2ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL3ID, @EMPTY))
      when (D.DESIGNATIONLEVEL2ID is not null)
        then CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL2ID, @EMPTY))
      else CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY))
      end
    ,case 
      when (D.DESIGNATIONLEVEL5ID is not null)
        then CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL2ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL3ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL4ID, @EMPTY))
      when (D.DESIGNATIONLEVEL4ID is not null)
        then CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL2ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL3ID, @EMPTY))
      when (D.DESIGNATIONLEVEL3ID is not null)
        then CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY)) + CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL2ID, @EMPTY))
      when (D.DESIGNATIONLEVEL2ID is not null)
        then CONVERT(nvarchar(36), isnull(D.DESIGNATIONLEVEL1ID, @EMPTY))
      else null
      end
    ,COALESCE(D.DESIGNATIONLEVEL5ID, D.DESIGNATIONLEVEL4ID, D.DESIGNATIONLEVEL3ID, D.DESIGNATIONLEVEL2ID, D.DESIGNATIONLEVEL1ID)
    ,case @SORTFIELD
      when 1
        then CONVERT(nvarchar(512), D.ISACTIVE)
      when 2
        then D.USERID
      when 3
        then D.VANITYNAME
      when 4
        then DLT.DESCRIPTION
      when 5
        then CONVERT(nvarchar(512), D.ISREVENUEDESIGNATION)
      else DL.[NAME]
      end
    ,case 
      when (D.DESIGNATIONLEVEL5ID is not null)
        then 5
      when (D.DESIGNATIONLEVEL4ID is not null)
        then 4
      when (D.DESIGNATIONLEVEL3ID is not null)
        then 3
      when (D.DESIGNATIONLEVEL2ID is not null)
        then 2
      else 1
      end
    ,DL.[NAME]
    ,DL.[NAME]
    ,D.ISACTIVE
    ,D.ISREVENUEDESIGNATION
    ,case 
      when D.SYSTEMGENERATED = 1
        then null
      else D.USERID
      end
    ,case 
      when D.SYSTEMGENERATED = 1
      -- SHL BBIS Bug 428630; Returning empty string to not break anything but not start anything either

        then ''
      else D.VANITYNAME
      end
    ,COALESCE(D.DESIGNATIONREPORT1CODEID, DL.DESIGNATIONREPORT1CODEID)
    ,COALESCE(D.DESIGNATIONREPORT2CODEID, DL.DESIGNATIONREPORT2CODEID)
    ,DLT.ID
    ,DLT.DESCRIPTION
    ,D.DESIGNATIONLEVELTYPEHIERARCHYITEMID
    ,D.SYSTEMGENERATED
    ,case 
      when (
          @DESIGNATIONLEVELTYPEID is null
          or DLT.ID = @DESIGNATIONLEVELTYPEID
          )
        and (
          @DESIGNATIONREPORT1CODEID is null
          or D.DESIGNATIONREPORT1CODEID = @DESIGNATIONREPORT1CODEID
          or (
            D.DESIGNATIONREPORT1CODEID is null
            and DL.DESIGNATIONREPORT1CODEID = @DESIGNATIONREPORT1CODEID
            )
          )
        and (
          @DESIGNATIONREPORT2CODEID is null
          or D.DESIGNATIONREPORT2CODEID = @DESIGNATIONREPORT2CODEID
          or (
            D.DESIGNATIONREPORT2CODEID is null
            and DL.DESIGNATIONREPORT2CODEID = @DESIGNATIONREPORT2CODEID
            )
          )
        and (
          @ACTIVEONLY = 0
          or D.ISACTIVE = 1
          )
        and (
          @DESIGNATIONLEVELNAME is null
          or DL.[NAME] like @DESIGNATIONLEVELNAME
          )
        and (
          @LOOKUPID is null
          or D.USERID like @LOOKUPID
          )
        and (
          @COMBINEDSEARCH is null
          or D.USERID like @COMBINEDSEARCH
          or DL.[NAME] like @COMBINEDSEARCH
          or (
            D.SYSTEMGENERATED = 0
            and D.VANITYNAME like @COMBINEDSEARCH
            )
          or exists (
            select ALT.ID
            from dbo.DESIGNATIONALTLOOKUPID ALT
            where ALT.DESIGNATIONID = D.ID
              and ALT.ALTLOOKUPID like @COMBINEDSEARCH
            )
          )
        then @MATCHESCRITERIA_MASK
      else @NOMATCH_MASK
      end | case 
      when (
          (
            @ROOTID1 is null
            or D.DESIGNATIONLEVEL1ID = @ROOTID1
            )
          and (
            @ROOTID2 is null
            or D.DESIGNATIONLEVEL2ID = @ROOTID2
            )
          and (
            @ROOTID3 is null
            or D.DESIGNATIONLEVEL3ID = @ROOTID3
            )
          and (
            @ROOTID4 is null
            or D.DESIGNATIONLEVEL4ID = @ROOTID4
            )
          and (
            @ROOTID5 is null
            or D.DESIGNATIONLEVEL5ID = @ROOTID5
            )
          )
        and (
          @PURPOSEID is null
          or D.DESIGNATIONLEVEL1ID = @PURPOSEID
          or D.DESIGNATIONLEVEL2ID = @PURPOSEID
          or D.DESIGNATIONLEVEL3ID = @PURPOSEID
          or D.DESIGNATIONLEVEL4ID = @PURPOSEID
          or D.DESIGNATIONLEVEL5ID = @PURPOSEID
          )
        then @ISINHIERARCHY_MASK
      else @NOMATCH_MASK
      end
  from dbo.DESIGNATION D
  inner join dbo.DESIGNATIONLEVEL DL on DL.ID = COALESCE(D.DESIGNATIONLEVEL5ID, D.DESIGNATIONLEVEL4ID, D.DESIGNATIONLEVEL3ID, D.DESIGNATIONLEVEL2ID, D.DESIGNATIONLEVEL1ID)
  inner join dbo.DESIGNATIONLEVELTYPE DLT on DL.DESIGNATIONLEVELTYPEID = DLT.ID

  update A
  set A.PARENTID = D.ID
  from @T A
  inner join @T D on A.PARENTPATHID = D.PATHID;

  --assign sortkey to all rows partitioned by PARENTID

  update ALLLEVELS
  set ALLLEVELS.SORTKEY = SORTED.SKEY
  from @T ALLLEVELS
  inner join (
    select RIGHT('00000' + CONVERT(nvarchar(5), case @SORTDIR
            when 1
              then ROW_NUMBER() over (
                  partition by D.PARENTID order by D.SORTVALUE desc
                  )
            else ROW_NUMBER() over (
                partition by D.PARENTID order by D.SORTVALUE
                )
            end), 5) SKEY
      ,ID
    from @T D
    ) SORTED on ALLLEVELS.ID = SORTED.ID;

  declare @level int = 2;

  --now loop thru and inherit values from parents and children

  while (@level < 6)
  begin
    update LEVELTOUPDATE
    set LEVELTOUPDATE.SORTKEY = PARENT.SORTKEY + LEVELTOUPDATE.SORTKEY
      ,LEVELTOUPDATE.PATHNAME = PARENT.PATHNAME + ' \ ' + LEVELTOUPDATE.PATHNAME
    --,LEVELTOUPDATE.DESIGNATIONREPORT1CODEID = COALESCE(LEVELTOUPDATE.DESIGNATIONREPORT1CODEID, PARENT.DESIGNATIONREPORT1CODEID)

    --,LEVELTOUPDATE.DESIGNATIONREPORT2CODEID = COALESCE(LEVELTOUPDATE.DESIGNATIONREPORT2CODEID, PARENT.DESIGNATIONREPORT2CODEID)

    from @T LEVELTOUPDATE
    inner join @T PARENT on LEVELTOUPDATE.PARENTID = PARENT.ID
    where LEVELTOUPDATE.[LEVEL] = @level;

    update LEVELTOUPDATE
    set LEVELTOUPDATE.MEETSCRITERIA = LEVELTOUPDATE.MEETSCRITERIA | @ISREQUIREDANCESTOR_MASK
    from @T LEVELTOUPDATE
    where NOT ((LEVELTOUPDATE.MEETSCRITERIA & (@ISINHIERARCHY_MASK | @MATCHESCRITERIA_MASK)) = (@ISINHIERARCHY_MASK | @MATCHESCRITERIA_MASK)
  or (LEVELTOUPDATE.MEETSCRITERIA & @ISREQUIREDANCESTOR_MASK) = @ISREQUIREDANCESTOR_MASK)
    --where (LEVELTOUPDATE.MEETSCRITERIA & (@ISREQUIREDANCESTOR_MASK | @ISINHIERARCHY_MASK)) = @NOMATCH_MASK

      and LEVELTOUPDATE.[LEVEL] = 6 - @level
      and exists (
        select top 1 1
        from @T CHILD
        where CHILD.PARENTID = LEVELTOUPDATE.ID
          and (
            (CHILD.MEETSCRITERIA & (@ISINHIERARCHY_MASK | @MATCHESCRITERIA_MASK)) = (@ISINHIERARCHY_MASK | @MATCHESCRITERIA_MASK)
            or (CHILD.MEETSCRITERIA & @ISREQUIREDANCESTOR_MASK) = @ISREQUIREDANCESTOR_MASK
           )
        );

    set @level = @level + 1;
  end
end

select D.ID
  ,D.DESIGNATIONLEVELID
  ,D.LEVELNAME
  ,D.DESIGNATIONLEVELTYPE
  ,D.ISACTIVE
  ,D.PARENTID
  ,D.[LEVEL]
  ,D.DESIGNATIONLEVELTYPEHIERARCHYITEMID
  ,(
    select count(DESIGNATIONLEVELTYPEHIERARCHYITEM.ID)
    from dbo.DESIGNATIONLEVELTYPEHIERARCHYITEM
    where DESIGNATIONLEVELTYPEHIERARCHYITEM.PARENTID = D.DESIGNATIONLEVELTYPEHIERARCHYITEMID
    ) HIERARCHYCHILDCOUNT
  ,D.DESIGNATIONLEVELTYPE PURPOSETYPE
  ,D.ISREVENUEDESIGNATION as REVENUEDESIGNATION
  ,D.VANITYNAME
  ,D.USERID
  ,case 
    when D.SYSTEMGENERATED = 1
      then null
    else D.PATHNAME
    end PATHNAME
  ,case 
    when ((D.[LEVEL] - @ROOTLEVEL) > 1)
      then 'CATALOG:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.IndentLevel' + CONVERT(nvarchar(1), (D.[LEVEL] - @ROOTLEVEL)) + '.png'
    else null
    end IMAGEKEY
  ,D.SYSTEMGENERATED
  ,(
    select DESCRIPTION
    from dbo.DESIGNATIONREPORT1CODE
    where ID = D.DESIGNATIONREPORT1CODEID
    ) DESIGNATIONREPORT1CODE
  ,(
    select DESCRIPTION
    from dbo.DESIGNATIONREPORT2CODE
    where ID = D.DESIGNATIONREPORT2CODEID
    ) DESIGNATIONREPORT2CODE
  ,D.MEETSCRITERIA
from @T D
where (D.MEETSCRITERIA & (@ISINHIERARCHY_MASK | @MATCHESCRITERIA_MASK)) = (@ISINHIERARCHY_MASK | @MATCHESCRITERIA_MASK)
  or (D.MEETSCRITERIA & @ISREQUIREDANCESTOR_MASK) = @ISREQUIREDANCESTOR_MASK
order by SORTKEY
  --REVISIT: there is a potential flaw in the filter logic which may include descendants when the criteria is only found in the parent [NAME] (because the child names are built from the parent names) - we want it to

  --work this way when locating a descendent, but may not want it to when we locate a ascendent - not sure - this could go either way...