USP_DATALIST_FAFNFGLEVEL

CREATE procedure dbo.USP_DATALIST_FAFNFGLEVEL  
(  
 @NFGLEVELROOTID uniqueidentifier = null,  
 @EVENTID uniqueidentifier  
)  
AS  
begin  
 set nocount on;  
 declare @ALLOWGROUPSCHOOSEHIERARCHYLEVEL bit  
 declare @NFGCAMPAIGNID uniqueidentifier
 declare @NFGSITECOUNT int = 0

 --get the default value  

 select @ALLOWGROUPSCHOOSEHIERARCHYLEVEL=0;  

 --get the value of 'Allow households, teams, and companies to choose a level' and CampaignID  

 select @NFGCAMPAIGNID = C.ID, @ALLOWGROUPSCHOOSEHIERARCHYLEVEL = C.ALLOWGROUPSCHOOSEHIERARCHYLEVEL  
 from dbo.FAFNFGCAMPAIGN C  
 inner join dbo.FAFNFGCAMPAIGNLEVEL lev on lev.NFGCAMPAIGNID = C.ID   
 where lev.ID = @NFGLEVELROOTID

 --get the count of nfg

 select @NFGSITECOUNT = count(NFG.ID) 
 from dbo.FAFNFGCAMPAIGN NFG
 inner join dbo.FAFNFGCAMPAIGNDISPLAYSITE NFGSITE on NFGSITE.NFGCAMPAIGNID = NFG.ID
 where NFG.ID = @NFGCAMPAIGNID

 declare @LEVELS table(id int identity(1,1), LEVELID uniqueidentifier, HIERARCHYPATH hierarchyid)
 declare @NFGLEVELSITES table(LEVELID uniqueidentifier, SITEID uniqueidentifier)
 declare @LEVELCOUNT int = 1
 declare @LEVELINDEX int = 1
 declare @NFGLEVELID uniqueidentifier
 declare @NFGLEVELHIERARCHYPATH hierarchyid

 -- if NFG have some sites

 if @NFGSITECOUNT <> 0
     begin
         insert into @LEVELS(LEVELID, HIERARCHYPATH)
         select ID,HIERARCHYPATH
         from dbo.FAFNFGCAMPAIGNLEVEL 
         where NFGCAMPAIGNID = @NFGCAMPAIGNID and ID <> @NFGLEVELROOTID
         select @LEVELCOUNT = max(ID) from @LEVELS

         while @LEVELINDEX <= @LEVELCOUNT
            BEGIN
                select @NFGLEVELID = LEVELID, @NFGLEVELHIERARCHYPATH = HIERARCHYPATH FROM @LEVELS WHERE ID = @LEVELINDEX
                --if level is not selected any site

                if not exists (select * 
                    from @LEVELS L
                    inner join dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE LS on LS.NFGCAMPAIGNLEVELID = L.LEVELID
                    where L.ID = @LEVELINDEX)
                    BEGIN
                        --if level is 2, then get the nfg sites

                        if (select HIERARCHYPATH.GetLevel() from @LEVELS WHERE ID = @LEVELINDEX) = 2
                            begin
                                insert into @NFGLEVELSITES(LEVELID, SITEID)
                                select @NFGLEVELID , SITEID
                                from  dbo.FAFNFGCAMPAIGN C
                                INNER JOIN dbo.FAFNFGCAMPAIGNDISPLAYSITE CS on CS.NFGCAMPAIGNID = C.ID
                                WHERE C.ID = @NFGCAMPAIGNID
                            end
                        else
                            begin--if level is more than 2, then get sites of the parent level

                                insert into @NFGLEVELSITES(LEVELID, SITEID)
                                select @NFGLEVELID , LS.SITEID
                                FROM @LEVELS L
                                inner join @NFGLEVELSITES LS ON LS.LEVELID = L.LEVELID and @NFGLEVELHIERARCHYPATH.GetAncestor (1)= L.HIERARCHYPATH
                            end
                    END
                else
                    begin-- if level is selected sites, get the FAFNFGCAMPAIGNLEVELDISPLAYSITE table data

                        insert into @NFGLEVELSITES(LEVELID, SITEID)
                        select L.LEVELID , LS.SITEID
                        FROM @LEVELS L
                        inner join dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE LS ON LS.NFGCAMPAIGNLEVELID = L.LEVELID
                        where L.ID = @LEVELINDEX
                    end
                SET @LEVELINDEX = @LEVELINDEX + 1
            END
    end

 select NFGL.ID,   
     NFGL.Name,  
     NFGL.HIERARCHYPATH.GetLevel() Level   
 from dbo.FAFNFGCAMPAIGNLEVEL NFGL  
 inner join dbo.FAFNFGCAMPAIGN NFG on NFG.ID = NFGL.NFGCAMPAIGNID
 inner join dbo.EVENTEXTENSION EE on EE.FAFPROGRAMID = NFG.CAMPAIGNID and EE.EVENTID = @EVENTID
 where NFGL.NFGCAMPAIGNID = @NFGCAMPAIGNID  
    and NFGL.HIERARCHYPATH.GetLevel() > = 2
    and @ALLOWGROUPSCHOOSEHIERARCHYLEVEL = 1  
    and @NFGSITECOUNT = 0 or exists (  
     select LEVELID  
     from @NFGLEVELSITES  
     where LEVELID = NFGL.ID   
      and SITEID in (select SITEID from dbo.EVENTSITE where EVENTID = @EVENTID)    
    )  
 order by HIERARCHYPATH   
end