USP_DATALIST_SPONSORSHIPPROGRAM

CREATE procedure [dbo].[USP_DATALIST_SPONSORSHIPPROGRAM]
(
@INCLUDEOPPORTUNITIESCOUNT bit = 1
)
as
    set nocount on;

declare @OPPORTUNITIES TABLE
(
        ID uniqueidentifier,
        OPPORTUNITIES [int]
)

    if @INCLUDEOPPORTUNITIESCOUNT = 1
        begin

            declare @FILTERLOCATIONCODE table
                (
                    ID uniqueidentifier,
                    FILTERLOCATIONCODE [tinyint],
                    [HIERARCHYPATH] [hierarchyid]
                )

            insert into @FILTERLOCATIONCODE (ID,FILTERLOCATIONCODE,HIERARCHYPATH)
                select distinct SPONSORSHIPPROGRAM.ID,SPONSORSHIPPROGRAM.FILTERLOCATIONCODE,FILTERLOCATION.HIERARCHYPATH    
                    from dbo.SPONSORSHIPPROGRAM    
                    cross apply dbo.UFN_SPONSORSHIPPROGRAM_FILTERLOCATIONS(SPONSORSHIPPROGRAM.FILTERLOCATIONS) X      
                    join dbo.SPONSORSHIPLOCATION FILTERLOCATION on FILTERLOCATION.ID = X.SPONSORSHIPLOCATIONID    
                    where SPONSORSHIPPROGRAM.FILTERLOCATIONCODE >= 1
                union 
                select distinct SPONSORSHIPPROGRAM.ID,SPONSORSHIPPROGRAM.FILTERLOCATIONCODE,null
                    from dbo.SPONSORSHIPPROGRAM    
                    where SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 0;    

             insert into @OPPORTUNITIES (ID,OPPORTUNITIES)
                select SPONSORSHIPPROGRAM.ID , count(*)    
                    from dbo.SPONSORSHIPOPPORTUNITY      
                    inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID      
                    left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID                  
                    where SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 0
                    group by SPONSORSHIPPROGRAM.ID
                union     
                select SPONSORSHIPPROGRAM.ID , count(*)    
                    from dbo.SPONSORSHIPOPPORTUNITY      
                    inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID      
                    left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID                  
                    where SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 1                    
                        and    exists    
                        (    
                        select 'x'      
                        from @FILTERLOCATIONCODE PROGRAMFILTEREDLOCATION    
                        where  PROGRAMFILTEREDLOCATION.FILTERLOCATIONCODE = 1 
                        and  SPONSORSHIPPROGRAM.ID =  PROGRAMFILTEREDLOCATION.ID
                        and OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(PROGRAMFILTEREDLOCATION.HIERARCHYPATH) = 1    
                        ) group by SPONSORSHIPPROGRAM.ID
                union     
                select SPONSORSHIPPROGRAM.ID , count(*)    
                    from dbo.SPONSORSHIPOPPORTUNITY      
                    inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID      
                    left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID                  
                    where SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 2    
                        and    not exists    
                        (    
                        select 'x'      
                        from @FILTERLOCATIONCODE PROGRAMFILTEREDLOCATION    
                        where PROGRAMFILTEREDLOCATION.FILTERLOCATIONCODE = 2 
                        and SPONSORSHIPPROGRAM.ID =  PROGRAMFILTEREDLOCATION.ID
                        and OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(PROGRAMFILTEREDLOCATION.HIERARCHYPATH) = 1    
                        ) group by SPONSORSHIPPROGRAM.ID
        end
    else 
        begin
            insert into @OPPORTUNITIES (ID,OPPORTUNITIES)
                select SPONSORSHIPPROGRAM.ID, 0
                from SPONSORSHIPPROGRAM
        end 

    select 
        SPONSORSHIPPROGRAM.ID,
        SPONSORSHIPPROGRAM.NAME,
        SPONSORSHIPOPPORTUNITYGROUP.NAME OPPORTUNITYGROUPNAME,
        OPPS.OPPORTUNITIES OPPORTUNITIES,
        case when SPONSORSHIPPROGRAM.ISINACTIVE = 1 then 'Inactive' else 'Active' end STATUS
    from dbo.SPONSORSHIPPROGRAM
    inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID
    inner join @OPPORTUNITIES OPPS on OPPS.ID = SPONSORSHIPPROGRAM.ID
    order by SPONSORSHIPPROGRAM.NAME