UFN_QUERY_FUNDRAISERSTEPSPENDING

CREATE function dbo.UFN_QUERY_FUNDRAISERSTEPSPENDING
(
  @FUNDRAISERID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @ONLYOWNEDINTERACTIONS bit = 0,
  @INCLUDEGENERALINTERACTIONS bit = 0,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
returns table
as
return
(
  -- Major giving plan step

  select 
    INTERACTION.ID as INTERACTIONID,
    PROSPECTPLAN.ID as PROSPECTPLANID,
    NF_PC.NAME as PROSPECT,
    INTERACTION.EXPECTEDDATE,
    INTERACTION.OBJECTIVE,
    (select DESCRIPTION from dbo.PROSPECTPLANTYPECODE where ID = PROSPECTPLAN.PROSPECTPLANTYPECODEID) as PLANTYPE,
    (select DESCRIPTION from dbo.PROSPECTPLANSTATUSCODE where ID = PROSPECTPLAN.PROSPECTPLANSTATUSCODEID) as PLANSTAGE,
    convert(bit, INTERACTION.ISINTERACTION) as ISINTERACTION,
    NF_FC.NAME as OWNER,
    INTERACTIONTYPECODE.ID as CONTACTMETHODID,
    INTERACTIONTYPECODE.DESCRIPTION as CONTACTMETHOD,
    case
      when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID) or
           exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID) or
           exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID)
        then convert(bit, 1)
      else convert(bit, 0)
    end as HASDOCUMENTATION,
    case
      when exists (select ID from INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = INTERACTION.ID) then convert(bit, 1)
      else convert(bit, 0)
    end HASADDITIONALFUNDRAISERS,
    convert(bit, 0) as ISSTEWARDSHIPSTEP,
    convert(bit, 0) as ISSTEWARDSHIPFORGROUPORG,
    (
      select dbo.UDA_BUILDLIST(SITE.NAME)
      from dbo.PROSPECTPLANSITE
        inner join dbo.SITE on PROSPECTPLANSITE.SITEID = SITE.ID
      where PROSPECTPLANSITE.PROSPECTPLANID = PROSPECTPLAN.ID
    ) as SITES,
    convert(bit, 0) ISFUNDINGREQUESTSTEP,
    convert(bit, 0) ISGENERALINTERACTION,
    INTERACTION.CONSTITUENTID as PROSPECTID,
    INTERACTIONCATEGORY.NAME as CATEGORY,
    INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
    INTERACTION.COMMENT,
    INTERACTION.EXPECTEDSTARTTIME,
    INTERACTION.EXPECTEDENDTIME,
    case
      when exists (select top 1 ID from dbo.INTERACTIONPARTICIPANT where INTERACTIONID = INTERACTION.ID) then convert(bit, 1)
      else convert(bit, 0)
    end as HASPARTICIPANTS,
    null as SUMMARY,
    INTERACTION.TIMEZONEENTRYID,
    TIMEZONEENTRY.DISPLAYNAME as TIMEZONE,
    INTERACTION.LOCATION
  from dbo.INTERACTION
    inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = INTERACTION.PROSPECTPLANID
    left join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
    left outer join dbo.INTERACTIONSUBCATEGORY on INTERACTION.INTERACTIONSUBCATEGORYID = INTERACTIONSUBCATEGORY.ID
    left join dbo.INTERACTIONCATEGORY on INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID = INTERACTIONCATEGORY.ID
    left join dbo.TIMEZONEENTRY on INTERACTION.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF_PC
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.FUNDRAISERID) NF_FC
  where
    INTERACTION.STATUSCODE = 1 and
    (
      INTERACTION.FUNDRAISERID = @FUNDRAISERID or
      (
        @ONLYOWNEDINTERACTIONS = 0 and
        exists
        (
          select ID
          from dbo.INTERACTIONADDITIONALFUNDRAISER
          where
            INTERACTIONID = INTERACTION.ID and
            FUNDRAISERID = @FUNDRAISERID
        )
      )
    ) and
    (
      select count(*) 
      from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as SECUREDRECORD 
      where
      (
        (select ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID) = 1 or
        exists
        (
          select 1
          from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'ce00d624-f3af-40fe-9ef9-2ac191a543bb',10)
     where
            SITEID=[SECUREDRECORD].[SITEID] or
            (
              SITEID is null and
              [SECUREDRECORD].[SITEID] is null
            )
        )
      )
    ) > 0 and 
    (
      @SITEFILTERMODE = 0 or
      PROSPECTPLAN.ID in
      (
        select PROSPECTPLANSITE.PROSPECTPLANID
        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
          inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
      )
    )

  union all

  -- Funding request step

  select 
    INTERACTION.ID as INTERACTIONID,
    FUNDINGREQUEST.ID as PROSPECTPLANID,
    NF_PC.NAME as PROSPECT,
    INTERACTION.EXPECTEDDATE,
    INTERACTION.OBJECTIVE,
    'Funding request' as PLANTYPE,
    (select DESCRIPTION from dbo.FUNDINGREQUESTSTAGECODE where ID = FUNDINGREQUEST.FUNDINGREQUESTSTAGECODEID) as PLANSTAGE,
    convert(bit, INTERACTION.ISINTERACTION) as ISINTERACTION,
    NF_FC.NAME as OWNER,
    INTERACTIONTYPECODE.ID as CONTACTMETHODID,
    INTERACTIONTYPECODE.DESCRIPTION as CONTACTMETHOD,
    case
      when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID) or
           exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID) or
           exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID)
        then convert(bit, 1)
      else convert(bit, 0)
    end as HASDOCUMENTATION,
    case
      when exists (select ID from INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = INTERACTION.ID) then convert(bit, 1)
      else convert(bit, 0)
    end HASADDITIONALFUNDRAISERS,
    convert(bit, 0) as ISSTEWARDSHIPSTEP,
    convert(bit, 0) as ISSTEWARDSHIPFORGROUPORG,
    SITE.NAME as SITES,
    convert(bit, 1) as ISFUNDINGREQUESTSTEP,
    convert(bit, 0) as ISGENERALINTERACTION,
    INTERACTION.CONSTITUENTID as PROSPECTID,
    INTERACTIONCATEGORY.NAME as CATEGORY,
    INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
    INTERACTION.COMMENT,
    null as EXPECTEDSTARTTIME,
    null as EXPECTEDENDTIME,
    case
      when exists (select top 1 ID from dbo.INTERACTIONPARTICIPANT where INTERACTIONID = INTERACTION.ID) then convert(bit, 1)
      else convert(bit, 0)
    end HASPARTICIPANTS,
    null as SUMMARY,
    null as TIMEZONEENTRYID,
    null as TIMEZONE,
    null as LOCATION
  from dbo.INTERACTION
    inner join dbo.FUNDINGREQUEST on FUNDINGREQUEST.ID = INTERACTION.FUNDINGREQUESTID
    inner join dbo.FUNDINGPLAN SECUREDRECORD on SECUREDRECORD.ID = FUNDINGREQUEST.FUNDINGPLANID
    inner join dbo.GRANTS on GRANTS.ID = FUNDINGREQUEST.GRANTSID
    left join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
    left outer join dbo.INTERACTIONSUBCATEGORY on INTERACTION.INTERACTIONSUBCATEGORYID = INTERACTIONSUBCATEGORY.ID
    left join dbo.INTERACTIONCATEGORY on INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID = INTERACTIONCATEGORY.ID
    left join dbo.SITE on SITE.ID = SECUREDRECORD.SITEID
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GRANTS.GRANTORID) NF_PC
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.FUNDRAISERID) NF_FC
  where
    INTERACTION.STATUSCODE = 1 and
    (
      INTERACTION.FUNDRAISERID = @FUNDRAISERID or
      (
        @ONLYOWNEDINTERACTIONS = 0 and
        exists
        (
          select ID
          from dbo.INTERACTIONADDITIONALFUNDRAISER
          where
            INTERACTIONID = INTERACTION.ID and
            FUNDRAISERID = @FUNDRAISERID
        )
      )
    ) and
    (
      (select ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID) = 1 or
      exists
      (
        select 1
        from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'ce00d624-f3af-40fe-9ef9-2ac191a543bb',10)
        where
          SITEID=[SECUREDRECORD].[SITEID] or
        (
            SITEID is null and
            [SECUREDRECORD].[SITEID] is null
          )
      )
    ) and
    (
      @SITEFILTERMODE = 0 or
      SITE.ID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
    )

  union all

  -- Stewardship plan step

  select
    STEP.ID as INTERACTIONID,
    STEP.PLANID as PROSPECTPLANID,
    PROSPECT_NF.NAME as PROSPECT,
    STEP.TARGETDATE as EXPECTEDDATE,
    STEP.OBJECTIVE,
    'Stewardship' as PLANTYPE,
    null as PLANSTAGE,
    convert(bit, 0) as ISINTERACTION,
    OWNER_NF.NAME as OWNER,
    INTERACTIONTYPECODE.ID as CONTACTMETHODID,
    INTERACTIONTYPECODE.DESCRIPTION as CONTACTMETHOD,
    case
      when exists(select 1 from dbo.STEWARDSHIPNOTE where STEWARDSHIPNOTE.STEPID = STEP.ID) or
           exists(select 1 from dbo.STEWARDSHIPATTACHMENT where STEWARDSHIPATTACHMENT.STEPID = STEP.ID) or
           exists(select 1 from dbo.STEWARDSHIPMEDIALINK where STEWARDSHIPMEDIALINK.STEPID = STEP.ID)
        then convert(bit, 1)
      else convert(bit, 0)
    end as HASDOCUMENTATION,
    convert(bit, 0) as HASADDITIONALFUNDRAISERS,
    convert(bit, 1) as ISSTEWARDSHIPSTEP,
    case
      when PROSPECT.ISGROUP = 1 or PROSPECT.ISORGANIZATION = 1 then convert(bit, 1)
      else convert(bit, 0)
    end as ISSTEWARDSHIPFORGROUPORG,
    (
      select dbo.UDA_BUILDLIST(SITE.NAME)
      from dbo.STEWARDSHIPPLANSITE
        inner join dbo.SITE on STEWARDSHIPPLANSITE.SITEID = SITE.ID
      where STEWARDSHIPPLANSITE.STEWARDSHIPPLANID = [PLAN].ID
    ) as SITES,
    convert(bit, 0) as ISFUNDINGREQUESTSTEP,
    convert(bit, 0) as ISGENERALINTERACTION,
    PROSPECT.ID as PROSPECTID,
    STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION as CATEGORY,
    null as SUBCATEGORY,
    null as COMMENT,
    STEP.TARGETSTARTTIME as EXPECTEDSTARTTIME,
    STEP.TARGETENDTIME as EXPECTEDENDTIME,
    case
      when exists (select top 1 ID from dbo.STEWARDSHIPPLANSTEPPARTICIPANT where STEWARDSHIPPLANSTEPID = STEP.ID) then convert(bit, 1)
      else convert(bit, 0)
    end as HASPARTICIPANTS,
    null as SUMMARY,
    STEP.TIMEZONEENTRYID,
    TIMEZONEENTRY.DISPLAYNAME as TIMEZONE,
    null as LOCATION
  from dbo.STEWARDSHIPPLANSTEP STEP
    inner join dbo.STEWARDSHIPPLAN [PLAN] on STEP.PLANID = [PLAN].ID
    inner join dbo.CONSTITUENT PROSPECT on [PLAN].CONSTITUENTID = PROSPECT.ID
    left outer join dbo.INTERACTIONTYPECODE on INTERACTIONTYPECODE.ID = STEP.CONTACTMETHODCODEID
    left outer join dbo.STEWARDSHIPSTEPCATEGORYCODE on STEP.CATEGORYCODEID = STEWARDSHIPSTEPCATEGORYCODE.ID
    left join dbo.TIMEZONEENTRY on STEP.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) PROSPECT_NF
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONSTITUENTID) OWNER_NF
  where
    STEP.CONSTITUENTID = @FUNDRAISERID and
    STEP.STATUSCODE = 0 and
    exists
    (
      select STEWARDSHIPPLAN.ID 
      from dbo.STEWARDSHIPPLAN
      where
        STEWARDSHIPPLAN.ID = STEP.PLANID and
        (
          select count(*)
          from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) as SECUREDRECORD
          where
          (
            (select ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID) = 1 or
            exists
            (
              select 1
              from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'ce00d624-f3af-40fe-9ef9-2ac191a543bb',10)
              where
                SITEID=[SECUREDRECORD].[SITEID] or
                (
                  SITEID is null and
                  [SECUREDRECORD].[SITEID] is null
                )
            )
          )
        ) > 0 and
        (
          @SITEFILTERMODE = 0 or STEWARDSHIPPLAN.ID in
          (
            select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
            from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
            inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
          )
        )
    )

  union all

  -- General interaction

  select
    INTERACTION.ID as INTERACTIONID,
    INTERACTION.PROSPECTPLANID,
    NF_C.NAME as PROSPECT,
    INTERACTION.EXPECTEDDATE,
    null as OBJECTIVE,
    null as PLANTYPE,
    null as PLANSTAGE,
    convert(bit, INTERACTION.ISINTERACTION) as ISINTERACTION,
    NF_F.NAME as OWNER,
    INTERACTIONTYPECODE.ID as CONTACTMETHODID,
    INTERACTIONTYPECODE.DESCRIPTION as CONTACTMETHOD,
    case
      when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID=INTERACTION.ID) or
           exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID=INTERACTION.ID) or
           exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID=INTERACTION.ID)
        then convert(bit, 1)
      else convert(bit, 0)
    end as HASDOCUMENTATION,
    case
      when exists (select ID from INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = INTERACTION.ID) then convert(bit, 1)
      else convert(bit, 0)
    end HASADDITIONALFUNDRAISERS,
    convert(bit, 0) as ISSTEWARDSHIPSTEP,
    convert(bit, 0) as ISSTEWARDSHIPFORGROUPORG,
    (
      select dbo.UDA_BUILDLIST(SITE.NAME)
      from dbo.INTERACTIONSITE
        inner join dbo.SITE on INTERACTIONSITE.SITEID = SITE.ID
      where INTERACTIONSITE.INTERACTIONID = INTERACTION.ID
    ) as SITES,
    convert(bit, 0) as ISFUNDINGREQUESTSTEP,
    convert(bit, 1) as ISGENERALINTERACTION,
    INTERACTION.CONSTITUENTID as PROSPECTID,
    INTERACTIONCATEGORY.NAME as CATEGORY,
    INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
    INTERACTION.COMMENT,
    INTERACTION.EXPECTEDSTARTTIME,
    INTERACTION.EXPECTEDENDTIME,
    case
      when exists (select top 1 ID from dbo.INTERACTIONPARTICIPANT where INTERACTIONID = INTERACTION.ID) then convert(bit, 1)
      else convert(bit, 0)
    end as HASPARTICIPANTS,
    INTERACTION.OBJECTIVE as SUMMARY,
    INTERACTION.TIMEZONEENTRYID,
    TIMEZONEENTRY.DISPLAYNAME as TIMEZONE,
    INTERACTION.LOCATION
  from dbo.INTERACTION
    left join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
    left outer join dbo.INTERACTIONSUBCATEGORY on INTERACTION.INTERACTIONSUBCATEGORYID = INTERACTIONSUBCATEGORY.ID
    left join dbo.INTERACTIONCATEGORY on INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID = INTERACTIONCATEGORY.ID
    left join dbo.TIMEZONEENTRY on INTERACTION.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTID) NF_C
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FUNDRAISERID) NF_F
  where
    @INCLUDEGENERALINTERACTIONS = 1 and
    INTERACTION.FUNDINGREQUESTID is null and
    INTERACTION.STATUSCODE = 1 and
    INTERACTION.FUNDRAISERID = @FUNDRAISERID and
    INTERACTION.PROSPECTPLANID is null and
    exists
    (
      select top 1 INTERACTIONSITE.SITEID
      from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
      where
      (
        (select ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID) = 1 or
        exists
        (
          select 1
          from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'ce00d624-f3af-40fe-9ef9-2ac191a543bb',10)
          where
            SITEID=[INTERACTIONSITE].[SITEID] or
            (
              SITEID is null and
              [INTERACTIONSITE].[SITEID] is null
            )
        )
      )
    ) and
    (
      @SITEFILTERMODE = 0 or
      (
        INTERACTION.ID in
        (
          select INTERACTIONSITE.INTERACTIONID
          from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
          inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
 )
      )
    )
)