USP_DATAFORMTEMPLATE_VIEW_DESIGNATIONPAGEDATA

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DESIGNATIONPAGEDATA (
  @ID uniqueidentifier
  ,@CURRENTAPPUSERID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@NAME nvarchar(512) = null output
  ,@DESIGNATIONLEVEL1ID uniqueidentifier = null output
  ,@DESIGNATIONLEVEL1NAME nvarchar(100) = null output
  ,@DESIGNATIONLEVEL1TYPE nvarchar(100) = null output
  ,@DESIGNATION1ID uniqueidentifier = null output
  ,@DESIGNATIONLEVEL2NAME nvarchar(100) = null output
  ,@DESIGNATION2ID uniqueidentifier = null output
  ,@DESIGNATIONLEVEL3NAME nvarchar(100) = null output
  ,@DESIGNATION3ID uniqueidentifier = null output
  ,@DESIGNATIONLEVEL4NAME nvarchar(100) = null output
  ,@DESIGNATION4ID uniqueidentifier = null output
  ,@DESIGNATIONLEVEL5NAME nvarchar(100) = null output
  ,@THISDESIGNATIONLEVELID uniqueidentifier = null output
  ,@THISDESIGNATIONLEVELNAME nvarchar(100) = null output
  ,@ISACTIVE bit = null output
  ,@HASKPIRIGHTS bit = null output
  ,@ISTOPLEVELDESIGNATION bit = null output
  ,@THISDESIGNATIONLEVELTYPENAME nvarchar(100) = null output
  ,@ATTRIBUTEDEFINED bit = null output
  ,@HASGLMAPPING bit = null output
  ,@ISBASICCMS bit = null output
  ,@HASWEBDETAILSLINK bit = null output
  ,@HASWEBTHUMBNAIL bit = null output
  ,@SYSTEMGENERATED1 bit = null output
  ,@SYSTEMGENERATED2 bit = null output
  ,@SYSTEMGENERATED3 bit = null output
  ,@SYSTEMGENERATED4 bit = null output
  ,@THISDESIGNATIONID uniqueidentifier = null output
  ,@LEVEL tinyint = null output
  ,@DESIGNATIONLEVEL2ID uniqueidentifier = null output
  ,@DESIGNATIONLEVEL3ID uniqueidentifier = null output
  ,@DESIGNATIONLEVEL4ID uniqueidentifier = null output
  ,@QUERYSEPARATOR nvarchar(10) = null output
  ,@QUERYSTRING nvarchar(2000) = null output
  )
as
begin
  set nocount on;
  set @DATALOADED = 0;

  select @DATALOADED = 1
    ,@NAME = D.[NAME]
    ,@DESIGNATIONLEVEL1ID = D.DESIGNATIONLEVEL1ID
    ,@DESIGNATIONLEVEL1NAME = DESIGNATIONLEVEL.[NAME]
    ,@DESIGNATIONLEVEL1TYPE = FPT.DESCRIPTION
    ,@DESIGNATION1ID = D1.ID
    ,@DESIGNATIONLEVEL2NAME = LEVEL2.[NAME]
    ,@DESIGNATION2ID = D2.ID
    ,@DESIGNATIONLEVEL3NAME = LEVEL3.[NAME]
    ,@DESIGNATION3ID = D3.ID
    ,@DESIGNATIONLEVEL4NAME = LEVEL4.[NAME]
    ,@DESIGNATION4ID = D4.ID
    ,@DESIGNATIONLEVEL5NAME = LEVEL5.[NAME]
    ,@THISDESIGNATIONLEVELID = COALESCE(D.DESIGNATIONLEVEL5ID, D.DESIGNATIONLEVEL4ID, D.DESIGNATIONLEVEL3ID, D.DESIGNATIONLEVEL2ID, D.DESIGNATIONLEVEL1ID)
    ,@THISDESIGNATIONLEVELNAME = COALESCE(LEVEL5.[NAME], LEVEL4.[NAME], LEVEL3.[NAME], LEVEL2.[NAME], DESIGNATIONLEVEL.[NAME])
    ,@ISACTIVE = D.ISACTIVE
    ,@HASKPIRIGHTS = dbo.UFN_SECURITY_APPUSER_GRANTED_KPI(@CURRENTAPPUSERID, 'A7463D6E-87B2-4F0F-BF44-C5D29BA77D5E')
    ,--GUID is ID of FP Goal KPI Type

    @ISTOPLEVELDESIGNATION = case 
      when D.DESIGNATIONLEVEL2ID is null
        then 1
      else 0
      end
    ,@ISBASICCMS = ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID as bit), 0)
    ,@HASWEBDETAILSLINK = case 
      when DESIGNATIONDETAILSLINK.ID is null
        then 0
      else 1
      end
    ,@HASWEBTHUMBNAIL = case 
      when DESIGNATIONIMAGE.ID is null
        then 0
      else 1
      end
    ,@SYSTEMGENERATED1 = COALESCE(D1.SYSTEMGENERATED, 0)
    ,@SYSTEMGENERATED2 = COALESCE(D2.SYSTEMGENERATED, 0)
    ,@SYSTEMGENERATED3 = COALESCE(D3.SYSTEMGENERATED, 0)
    ,@SYSTEMGENERATED4 = COALESCE(D4.SYSTEMGENERATED, 0)
    ,@THISDESIGNATIONID = D.ID
    ,@LEVEL = 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
    ,@DESIGNATIONLEVEL2ID=D.DESIGNATIONLEVEL2ID
    ,@DESIGNATIONLEVEL3ID=D.DESIGNATIONLEVEL3ID
    ,@DESIGNATIONLEVEL4ID=D.DESIGNATIONLEVEL4ID
  from dbo.DESIGNATION D
  inner join dbo.DESIGNATIONLEVEL on D.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID
  inner join dbo.DESIGNATIONLEVELTYPE FPT on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = FPT.ID
  left join dbo.DESIGNATIONLEVEL LEVEL2 on D.DESIGNATIONLEVEL2ID = LEVEL2.ID
  left join dbo.DESIGNATIONLEVEL LEVEL3 on D.DESIGNATIONLEVEL3ID = LEVEL3.ID
  left join dbo.DESIGNATIONLEVEL LEVEL4 on D.DESIGNATIONLEVEL4ID = LEVEL4.ID
  left join dbo.DESIGNATIONLEVEL LEVEL5 on D.DESIGNATIONLEVEL5ID = LEVEL5.ID
  left join dbo.MICROSITEPAGE on (dbo.MICROSITEPAGE.OBJECTID = D.ID)
    and (MICROSITEPAGE.EXCLUDED = 0)
  left join dbo.DESIGNATIONDETAILSLINK on DESIGNATIONDETAILSLINK.DESIGNATIONID = D.ID
  left join dbo.DESIGNATIONIMAGE on DESIGNATIONIMAGE.DESIGNATIONID = D.ID
  left join dbo.DESIGNATION D1 on D.DESIGNATIONLEVEL1ID = D1.DESIGNATIONLEVEL1ID
    and D1.DESIGNATIONLEVEL2ID is null
  left join dbo.DESIGNATION D2 on D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
    and D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID
    and D2.DESIGNATIONLEVEL3ID is null
  left join dbo.DESIGNATION D3 on D.DESIGNATIONLEVEL1ID = D3.DESIGNATIONLEVEL1ID
    and D.DESIGNATIONLEVEL2ID = D3.DESIGNATIONLEVEL2ID
    and D.DESIGNATIONLEVEL3ID = D3.DESIGNATIONLEVEL3ID
    and D3.DESIGNATIONLEVEL4ID is null
  left join dbo.DESIGNATION D4 on D.DESIGNATIONLEVEL1ID = D4.DESIGNATIONLEVEL1ID
    and D.DESIGNATIONLEVEL2ID = D4.DESIGNATIONLEVEL2ID
    and D.DESIGNATIONLEVEL3ID = D4.DESIGNATIONLEVEL3ID
    and D.DESIGNATIONLEVEL4ID = D4.DESIGNATIONLEVEL4ID
    and D4.DESIGNATIONLEVEL5ID is null
  where D.ID = @ID
    and D.SYSTEMGENERATED = 0;

  select @THISDESIGNATIONLEVELTYPENAME = DESIGNATIONLEVELTYPE.DESCRIPTION
  from dbo.DESIGNATIONLEVELTYPE
  where DESIGNATIONLEVELTYPE.ID = (
      select DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID
      from dbo.DESIGNATIONLEVEL
      where DESIGNATIONLEVEL.ID = @THISDESIGNATIONLEVELID
      );

  --if exists(select ID from dbo.PDACCOUNTSTRUCTURE where PDACCOUNTTABLESAVAILABLEFORSEGMENTID = '2B1E041E-8FA3-4301-A5DB-E6531E9C3CED')

  --set @HASGLMAPPING = 1

  if (
      select count(ID)
      from dbo.PDACCOUNTSTRUCTURE
      where PDACCOUNTTABLESAVAILABLEFORSEGMENTID = '2B1E041E-8FA3-4301-A5DB-E6531E9C3CED'
      ) = 1
    set @HASGLMAPPING = 1
  set @ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('Designation', @CURRENTAPPUSERID);

  set @QUERYSEPARATOR = N'?';
  set @QUERYSTRING = '';

  select 
     top 1 @QUERYSTRING = isnull('https://fenxt.blackbaud.com/ledger/project/' + convert(nvarchar(20),FENXTPROJECT.PROJECTID) + '?environmentid='+FENXTINTEGRATION.ENVIRONMENTID+'&displayashosted=true','')
  from dbo.DESIGNATIONALTLOOKUPID 
  inner join dbo.FENXTPROJECT on DESIGNATIONALTLOOKUPID.ALTLOOKUPID = FENXTPROJECT.UIPROJECTID
  inner join dbo.FENXTINTEGRATION on FENXTPROJECT.PDACCOUNTSYSTEMID = FENXTINTEGRATION.PDACCOUNTSYSTEMID
  where DESIGNATIONALTLOOKUPID.DESIGNATIONID = @ID 
     and DESIGNATIONALTLOOKUPID.ALTLOOKUPIDTYPECODEID = 'E5837E89-B51A-4BAD-8E06-50D515DC64A2'
  order by FENXTINTEGRATION.FEDESIGNATIONSOURCE desc, DESIGNATIONALTLOOKUPID.DATEADDED;

  return 0;
end