USP_SPONSORSHIPOPPORTUNITYCHILD_SEARCH

CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITYCHILD_SEARCH
(
    @QUICKSEARCH nvarchar(100) = null,
    @KEYNAME nvarchar(100) = null,
    @FIRSTNAME 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,
    @MAXROWS smallint = 500,
    @RESERVATIONKEYID uniqueidentifier = null,
    -- used by add sponsorship

    @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null,
    @CORRESPONDINGSPONSORID uniqueidentifier = null,
    @FINANCIALSPONSORID uniqueidentifier = null
)
with execute as owner
as 

set nocount on;

  declare @tempid uniqueidentifier = newid();

  set @KEYNAME = ISNULL(@KEYNAME, '') + '%';
  set @FIRSTNAME = ISNULL(@FIRSTNAME, '') + '%';
  set @LOOKUPID = ISNULL(@LOOKUPID, '') + '%';

  declare @SQL nvarchar(max);
  set @SQL = '
    select top(@MAXROWS)
      SO.ID,
        CHILDNAME.NAME as NAME, 
      SO.LOOKUPID,
        (select NAME from dbo.SPONSORSHIPOPPORTUNITYGROUP SPR WHERE SPR.ID = SO.SPONSORSHIPOPPORTUNITYGROUPID) OPPORTUNITYGROUP,
        SO.ELIGIBILITY,
        SO.AVAILABILITY,
        C.GENDER, 
        C.BIRTHDATE,
        dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(OPPORTUNITYLOCATION.ID) LOCATION
    from 
      SPONSORSHIPOPPORTUNITY SO
      inner join SPONSORSHIPOPPORTUNITYCHILD SC on SC.ID =SO.ID
      inner join CONSTITUENT C on C.ID=  SC.CONSTITUENTID
      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
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SC.CONSTITUENTID) [CHILDNAME]
     where
      (@SPONSORSHIPOPPORTUNITYID is null or SO.ID = @SPONSORSHIPOPPORTUNITYID) ' + CHAR(13);

  if @KEYNAME <> '%'
    set @SQL = @SQL + 'and (C.KEYNAME like @KEYNAME) ' + CHAR(13);

  if @FIRSTNAME <> '%'
    set @SQL = @SQL + 'and (C.FIRSTNAME like @FIRSTNAME) ' + CHAR(13);

  if @LOOKUPID <> '%'
    set @SQL = @SQL + 'and (SO.LOOKUPID like @LOOKUPID) ' + CHAR(13);

  if @SPONSORSHIPLOCATIONID is not null
    set @SQL = @SQL + 'and (OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(SELECTEDLOCATION.HIERARCHYPATH) = 1) ' + CHAR(13);

  if @SPONSORSHIPOPPORTUNITYGROUPID is not null
    set @SQL = @SQL + 'and (SO.SPONSORSHIPOPPORTUNITYGROUPID = @SPONSORSHIPOPPORTUNITYGROUPID) ' + CHAR(13);

  set @SQL = @SQL + '
      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))))) ' + CHAR(13);

    if @ELIGIBILITYCODE <> -1
      set @SQL = @SQL + 'and (SO.ELIGIBILITYCODE = @ELIGIBILITYCODE) ' + CHAR(13);

    if @AVAILABILITYCODE <> -1
      set @SQL = @SQL + 'and (SO.AVAILABILITYCODE = @AVAILABILITYCODE) ' + CHAR(13);

    if @EXCLUDEOPPORTUNITYID is not null
      set @SQL = @SQL + 'and (SO.ID <> @EXCLUDEOPPORTUNITYID) ' + CHAR(13);

    if @RESTRICTFORSOLESPONSORSHIP <> 0
      set @SQL = @SQL + 'and (
             not exists(select ''x''
                        from dbo.SPONSORSHIP S
                        where SO.ID = S.SPONSORSHIPOPPORTUNITYID
                        and S.STATUSCODE in(0,1))) ' + CHAR(13);

    if isnull(@QUICKSEARCH, '%') <> '%'
      set @SQL = @SQL + 'and (C.NAME like ''%''+@QUICKSEARCH+''%'' or SO.LOOKUPID like @QUICKSEARCH) ' + CHAR(13)

    if @RESERVATIONKEYID is not null
      set @SQL = @SQL + 'and (SO.RESERVATIONKEYID = @RESERVATIONKEYID) ' + CHAR(13);

    if @CORRESPONDINGSPONSORID is not null 
      set @SQL = @SQL + 'and (
             not exists(select ''x''
                         from dbo.SPONSORSHIP S
                         where S.SPONSORSHIPOPPORTUNITYID = SO.ID
                         and S.CONSTITUENTID = @CORRESPONDINGSPONSORID
                         and S.STATUSCODE in(0,1))) ' + CHAR(13);

  if @FINANCIALSPONSORID is not null
    set @SQL = @SQL + 'and (
             (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))) ' + CHAR(13);

    if @ELIGIBILITYCODE    = 1
      set @SQL = @SQL + 'and (dbo.UFN_SPONSORSHIPOPPORTUNITY_AGEVALID(SO.SPONSORSHIPOPPORTUNITYGROUPID,C.BIRTHDATE) = 1) ' + CHAR(13);

  declare @PARAMS nvarchar(max);
  set @PARAMS = '@QUICKSEARCH nvarchar(100), @KEYNAME nvarchar(100), @FIRSTNAME nvarchar(100), @LOOKUPID nvarchar(100), @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier, ' +
                '@SPONSORSHIPPROGRAMID uniqueidentifier, @SPONSORSHIPLOCATIONID uniqueidentifier, @ELIGIBILITYCODE smallint = -1, @AVAILABILITYCODE smallint = -1, ' +
                '@RESTRICTFORSOLESPONSORSHIP bit = 0, @EXCLUDEOPPORTUNITYID uniqueidentifier, @MAXROWS smallint = 500, @RESERVATIONKEYID uniqueidentifier, ' +
                '@SPONSORSHIPOPPORTUNITYID uniqueidentifier, @CORRESPONDINGSPONSORID uniqueidentifier, @FINANCIALSPONSORID uniqueidentifier = null';

  exec sp_executesql @SQL, @PARAMS, @QUICKSEARCH, @KEYNAME, @FIRSTNAME, @LOOKUPID, @SPONSORSHIPOPPORTUNITYGROUPID,
                      @SPONSORSHIPPROGRAMID, @SPONSORSHIPLOCATIONID, @ELIGIBILITYCODE, @AVAILABILITYCODE,
                      @RESTRICTFORSOLESPONSORSHIP, @EXCLUDEOPPORTUNITYID, @MAXROWS, @RESERVATIONKEYID, 
                      @SPONSORSHIPOPPORTUNITYID, @CORRESPONDINGSPONSORID, @FINANCIALSPONSORID;

  ;