USP_SPONSORSHIPOPPORTUNITYPROJECT_SEARCH

CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITYPROJECT_SEARCH
(
    @QUICKSEARCH nvarchar(100) = null,
    @KEYNAME nvarchar(100) = null,
    @LOOKUPID nvarchar(100)=null,
    @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier = null,
    @SPONSORSHIPPROGRAMID uniqueidentifier = null,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @ELIGIBILITYCODE smallint = -1,
    @AVAILABILITYCODE smallint = -1,
    @RESTRICTFORSOLESPONSORSHIP bit = 0,
    @EXCLUDEOPPORTUNITYID uniqueidentifier = null,
    @CATEGORYID uniqueidentifier = null,
    @MAXROWS smallint = 500,
    -- used by add sponsorship

    @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null,
    @CORRESPONDINGSPONSORID uniqueidentifier = null,
    @FINANCIALSPONSORID uniqueidentifier = null
)
as 

declare @tempid uniqueidentifier = newid();

if ISNULL(@KEYNAME,'%') = '%' and ISNULL(@LOOKUPID,'%')='%' and ISNULL(@SPONSORSHIPLOCATIONID,@tempid)=@tempid
begin
    set @KEYNAME = '%';
    set @LOOKUPID='%';
end
else
begin
  if @KEYNAME is not null 
  begin
     set @KEYNAME = @KEYNAME + '%';
  end
  if @LOOKUPID is not null 
  begin
     set @LOOKUPID = @LOOKUPID + '%';
  end 
end

select top(@MAXROWS)
    SO.ID,
    dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SO.ID) as NAME,
    SO.LOOKUPID,
    (select NAME from dbo.SPONSORSHIPOPPORTUNITYGROUP SPR WHERE SPR.ID = SO.SPONSORSHIPOPPORTUNITYGROUPID) OPPORTUNITYGROUP,
    case SO.ELIGIBILITYCODE when 0 then 'Pending' when 1 then 'Open' when 2 then 'Closed' end ELIGIBILITY,
    SO.AVAILABILITY,
    CAT.DESCRIPTION AS CATEGORY,
    dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(OPPORTUNITYLOCATION.ID) LOCATION
from 
    SPONSORSHIPOPPORTUNITY SO
inner join SPONSORSHIPOPPORTUNITYPROJECT SP on SP.ID = SO.ID
left outer join dbo.SPONSORSHIPLOCATION SELECTEDLOCATION on SELECTEDLOCATION.ID = @SPONSORSHIPLOCATIONID
left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SO.SPONSORSHIPLOCATIONID
left outer join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.ID = @SPONSORSHIPPROGRAMID
left outer join dbo.SPROPPPROJECTCATEGORYCODE CAT on CAT.ID = SP.SPROPPPROJECTCATEGORYCODEID
where (@SPONSORSHIPOPPORTUNITYID is null or SO.ID = @SPONSORSHIPOPPORTUNITYID)
and (SP.NAME LIKE @KEYNAME or @KEYNAME IS NULL)
and (SO.LOOKUPID LIKE @LOOKUPID or @LOOKUPID IS NULL)
and (@QUICKSEARCH is null or SP.NAME like '%'+@QUICKSEARCH+'%' or SO.LOOKUPID like @QUICKSEARCH)
and (@SPONSORSHIPLOCATIONID is null or OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(SELECTEDLOCATION.HIERARCHYPATH) = 1)
and SO.SPONSORSHIPOPPORTUNITYGROUPID = ISNULL(@SPONSORSHIPOPPORTUNITYGROUPID,SO.SPONSORSHIPOPPORTUNITYGROUPID)
and (@SPONSORSHIPPROGRAMID is null or
     (SO.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPPROGRAM.SPONSORSHIPOPPORTUNITYGROUPID and
      (SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 0 or
       (SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 1 and
        exists(select 'x'
               from dbo.UFN_SPONSORSHIPPROGRAM_FILTERLOCATIONS(SPONSORSHIPPROGRAM.FILTERLOCATIONS) X
                 inner join dbo.SPONSORSHIPLOCATION FILTERLOCATION on FILTERLOCATION.ID = X.SPONSORSHIPLOCATIONID
                 where OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(FILTERLOCATION.HIERARCHYPATH) = 1)) or
       (SPONSORSHIPPROGRAM.FILTERLOCATIONCODE = 2 and
        not exists(select 'x'
                   from dbo.UFN_SPONSORSHIPPROGRAM_FILTERLOCATIONS(SPONSORSHIPPROGRAM.FILTERLOCATIONS) X
                     inner join dbo.SPONSORSHIPLOCATION FILTERLOCATION on FILTERLOCATION.ID = X.SPONSORSHIPLOCATIONID
                   where OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(FILTERLOCATION.HIERARCHYPATH) = 1)))))
and (@ELIGIBILITYCODE = -1 or SO.ELIGIBILITYCODE = @ELIGIBILITYCODE)
and (@AVAILABILITYCODE = -1 or SO.AVAILABILITYCODE = @AVAILABILITYCODE)
and (@EXCLUDEOPPORTUNITYID is null or SO.ID <> @EXCLUDEOPPORTUNITYID)
and (@CATEGORYID is null or SP.SPROPPPROJECTCATEGORYCODEID = @CATEGORYID)
and (@RESTRICTFORSOLESPONSORSHIP = 0 or
         not exists(select 'x'
                    from dbo.SPONSORSHIP S
                    where SO.ID = S.SPONSORSHIPOPPORTUNITYID
                    and S.STATUSCODE in(0,1)))
and (@CORRESPONDINGSPONSORID is null or
     not exists(select 'x'
                 from dbo.SPONSORSHIP S
                 where S.SPONSORSHIPOPPORTUNITYID = SO.ID
                 and S.CONSTITUENTID = @CORRESPONDINGSPONSORID
                 and S.STATUSCODE in(0,1)))
and (@FINANCIALSPONSORID is null or
     (select UNIQUEOPPORTUNITIESFORGIFTDONOR from dbo.SPONSOR where ID = @FINANCIALSPONSORID) = 0 or
     not exists(select 'x'
                  from dbo.REVENUE
                  inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                  inner join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
                  where REVENUE.CONSTITUENTID = @FINANCIALSPONSORID
                  and SPONSORSHIP.SPONSORSHIPOPPORTUNITYID = SO.ID
                  and SPONSORSHIP.STATUSCODE in(0,1)));