UFN_QUERY_RESEARCHGROUPMEMBERSLIST

CREATE function [dbo].[UFN_QUERY_RESEARCHGROUPMEMBERSLIST]
(
  @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,

    @SECURITYFEATUREID uniqueidentifier = '696F7786-E5DD-401F-B5B2-086168339FA8',
    @SECURITYFEATURETYPE tinyint = 10
)
returns table
as
  return 
                select
                    RESEARCHGROUPMEMBER.ID,
                    RESEARCHGROUPMEMBER.CONSTITUENTID,
                    CONSTITUENT.NAME as MEMBERNAME,
                    ADDRESS.ADDRESSBLOCK as ADDRESSBLOCK,
          ADDRESS.CITY,
          STATE.ABBREVIATION STATEID_ABBREVIATION,
          ADDRESS.POSTCODE,
          dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK,ADDRESS.CITY,STATE.ID,ADDRESS.POSTCODE,ADDRESS.COUNTRYID) as [FULLADDRESS],
          WEALTH.WEALTHPOINTDATE,
          WEALTH.BUSINESSOWNERSHIPASSETSCONFIRMED,
          WEALTH.BUSINESSOWNERSHIPASSETS,
          WEALTH.TOTALASSETSCONFIRMED,
          WEALTH.TOTALASSETS,
          WEALTH.INCOMECOMPENSATIONASSETSCONFIRMED,
          WEALTH.INCOMECOMPENSATIONASSETS,
          WEALTH.OTHERASSETSCONFIRMED,
          WEALTH.REALESTATEASSETSCONFIRMED,
          WEALTH.REALESTATEASSETS,
          WEALTH.SECURITIESASSETSCONFIRMED,
          WEALTH.SECURITIESASSETS,
          WEALTH.PHILANTHROPICGIFTNUMBERCONFIRMED,
          WEALTH.PHILANTHROPICGIFTNUMBER,
          WEALTH.POLITICALDONATIONNUMBERCONFIRMED,
          WEALTH.POLITICALDONATIONNUMBER,
          nullif(CONSTITUENT.AGE, 0) AGE,
          CONSTITUENT.DATEADDED,
          RE7INTEGRATIONCONSTITUENTMAP.LASTSYNCDATE,
          case when RE7INTEGRATIONCONSTITUENTMAP.ID is null then 0 else 1 end RE7LINKED,
          BUSINESSRELATIONSHIP.RECIPROCALCONSTITUENTID BUSINESSID,
          BUSINESS.NAME BUSINESSNAME,
          PRIMARYBUSINESSJOBINFO.JOBTITLE,
          PROSPECTMANAGERID = case when PROSPECT.PROSPECTMANAGERENDDATE < dbo.UFN_DATE_GETEARLIESTTIME(getdate()) then null else PROSPECT.PROSPECTMANAGERFUNDRAISERID end ,
          PROSPECTMANAGERNAME = case when PROSPECT.PROSPECTMANAGERENDDATE < dbo.UFN_DATE_GETEARLIESTTIME(getdate()) then null else PROSPECTMANAGER.NAME end,
          SPOUSERELATIONSHIP.RECIPROCALCONSTITUENTID SPOUSEID,
          SPOUSE.NAME SPOUSENAME,
          PRIMARYEDUCATION.CLASSOF PRIMARYEDUCATIONCLASSOF,
          EDUCATIONALDEGREECODE.DESCRIPTION PRIMARYEDUCATIONDEGREE,
          EDUCATIONALPROGRAMCODE.DESCRIPTION PRIMARYEDUCATIONPROGRAM,
          PRIMARYEDUCATIONINSTITUTION.NAME PRIMARYEDUCATIONINSTITUTION,
          RE7INTEGRATIONGIVINGSUMMARY.FIRSTGIFTAMOUNT,
          RE7INTEGRATIONGIVINGSUMMARY.FIRSTGIFTDATE,
          RE7INTEGRATIONGIVINGSUMMARY.LARGESTGIFTAMOUNT,
          RE7INTEGRATIONGIVINGSUMMARY.LARGESTGIFTDATE,
          RE7INTEGRATIONGIVINGSUMMARY.LATESTGIFTAMOUNT,
          RE7INTEGRATIONGIVINGSUMMARY.LATESTGIFTDATE,
          RE7INTEGRATIONGIVINGSUMMARY.TOTALGIFTSGIVEN,
          RE7INTEGRATIONGIVINGSUMMARY.TOTALGIFTAMOUNT,
          WEALTHCAPACITY.MAJORGIVINGCAPACITYVALUE,
          WEALTHCAPACITY.ESTIMATEDWEALTHVALUE,
          nullif(MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD, -1) as ANNUALGIFTLIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.ANNUITYLIKELIHOOD, -1) as ANNUITYLIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.BEQUESTLIKELIHOOD, -1) as BEQUESTLIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.CRTLIKELIHOOD, -1) as CRTLIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD, -1) as MAJORGIVINGLIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.MEMBERSHIPLIKELIHOOD, -1) as MEMBERSHIPLIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.ONLINEGIVINGLIKELIHOOD, -1) as ONLINEGIVINGLIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.PATIENTRESPONSELIKELIHOOD, -1) as PATIENTRESPONSELIKELIHOOD,
          nullif(MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD, -1) as PLANNEDGIFTLIKELIHOOD,
          SUGGESTEDMEMBERSHIPLEVEL.RANGE as SUGGESTEDMEMBERSHIPLEVEL,
          SUGGESTEDMEMBERSHIPLEVEL.SCORE as SUGGESTEDMEMBERSHIPLEVELSCORE,
          TARGETGIFTRANGE.RANGE as TARGETGIFTRANGE,
          TARGETGIFTRANGE.SCORE as TARGETGIFTRANGESCORE,
          RESEARCHGROUPMEMBER.DATEADDED RESEARCHGROUPMEMBERDATEADDED,
          WEALTH.AFFLUENCEINDICATORNUMBERCONFIRMED,
          WEALTH.AFFLUENCEINDICATORNUMBER,
          case when DISABLEDWEALTHUPDATES.ID is null then 1 else 0 end WPENABLED,
          cast(RE7INTEGRATIONCONSTITUENTMAP.ID as nvarchar(36)) + '|' + ltrim(rtrim(cast(RE7INTEGRATIONCONSTITUENTMAP.RE7RECORDID as nvarchar(100)))) RE7SYNCKEY,
          CONSTITUENT.LOOKUPID,
          (CONSTITUENT.KEYNAME +
              CASE CONSTITUENT.FIRSTNAME WHEN '' THEN 
                CASE CONSTITUENT.MIDDLENAME WHEN '' THEN '' ELSE ', ' + LEFT(CONSTITUENT.MIDDLENAME,1) + '.' END
              ELSE 
                ', ' + CONSTITUENT.FIRSTNAME + CASE CONSTITUENT.MIDDLENAME WHEN '' THEN '' ELSE ' ' + LEFT(CONSTITUENT.MIDDLENAME,1) + '.' END
              END) as FORMATTEDNAME,
          (select 
                  dbo.UDA_BUILDLIST(SITE.NAME) 
              FROM 
                  dbo.SITE 
                  inner join dbo.CONSTITUENTSITE ON CONSTITUENTSITE.SITEID = SITE.ID
              where
                  CONSTITUENTSITE.CONSTITUENTID = CONSTITUENT.ID
            ) as SITES,
          case when PROSPECT.RESEARCHSTATUSCONFIRMED = 1 then 'Confirmed' else 'Not confirmed' end as RESEARCHSTATUSCONFIRMED


        /*#EXTENSION*/

                from
                    dbo.RESEARCHGROUPMEMBER
          left join dbo.CONSTITUENT on RESEARCHGROUPMEMBER.CONSTITUENTID = CONSTITUENT.ID
          left join dbo.ADDRESS on RESEARCHGROUPMEMBER.CONSTITUENTID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
          left join dbo.STATE on STATE.ID = ADDRESS.STATEID
          left join dbo.WEALTH on RESEARCHGROUPMEMBER.CONSTITUENTID = WEALTH.ID
          left join dbo.WEALTHCAPACITY on RESEARCHGROUPMEMBER.CONSTITUENTID = WEALTHCAPACITY.ID
          left join dbo.RE7INTEGRATIONCONSTITUENTMAP on RESEARCHGROUPMEMBER.CONSTITUENTID = RE7INTEGRATIONCONSTITUENTMAP.ID
          left join dbo.RE7INTEGRATIONGIVINGSUMMARY on RESEARCHGROUPMEMBER.CONSTITUENTID = RE7INTEGRATIONGIVINGSUMMARY.ID
          left join dbo.PROSPECT on RESEARCHGROUPMEMBER.CONSTITUENTID = PROSPECT.ID
          left join dbo.RELATIONSHIP BUSINESSRELATIONSHIP on RESEARCHGROUPMEMBER.CONSTITUENTID = BUSINESSRELATIONSHIP.RELATIONSHIPCONSTITUENTID 
              and BUSINESSRELATIONSHIP.ISPRIMARYBUSINESS = 1
          left join dbo.RELATIONSHIPJOBINFO PRIMARYBUSINESSJOBINFO on BUSINESSRELATIONSHIP.RELATIONSHIPSETID = PRIMARYBUSINESSJOBINFO.RELATIONSHIPSETID 
              and (PRIMARYBUSINESSJOBINFO.ENDDATE is null or PRIMARYBUSINESSJOBINFO.ENDDATE > getdate())
          left join dbo.RELATIONSHIP SPOUSERELATIONSHIP on RESEARCHGROUPMEMBER.CONSTITUENTID = SPOUSERELATIONSHIP.RELATIONSHIPCONSTITUENTID 
              and SPOUSERELATIONSHIP.ISSPOUSE = 1
          left join dbo.EDUCATIONALHISTORY PRIMARYEDUCATION on RESEARCHGROUPMEMBER.CONSTITUENTID = PRIMARYEDUCATION.CONSTITUENTID
              and PRIMARYEDUCATION.ISPRIMARYRECORD = 1
          left join dbo.EDUCATIONALDEGREECODE on  PRIMARYEDUCATION.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
          left join dbo.EDUCATIONALPROGRAMCODE on  PRIMARYEDUCATION.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
          left join dbo.MODELINGANDPROPENSITY on RESEARCHGROUPMEMBER.CONSTITUENTID = MODELINGANDPROPENSITY.ID
          left join dbo.SUGGESTEDMEMBERSHIPLEVEL on MODELINGANDPROPENSITY.SUGGESTEDMEMBERSHIPLEVELID = SUGGESTEDMEMBERSHIPLEVEL.ID
          left join dbo.TARGETGIFTRANGE on MODELINGANDPROPENSITY.TARGETGIFTRANGEID = TARGETGIFTRANGE.ID
          left join dbo.DISABLEDWEALTHUPDATES on RESEARCHGROUPMEMBER.CONSTITUENTID = DISABLEDWEALTHUPDATES.ID
          left join dbo.CONSTITUENT BUSINESS on BUSINESS.ID = BUSINESSRELATIONSHIP.RECIPROCALCONSTITUENTID
          left join dbo.CONSTITUENT PROSPECTMANAGER on PROSPECTMANAGER.ID = PROSPECT.PROSPECTMANAGERFUNDRAISERID
   left join dbo.CONSTITUENT SPOUSE on SPOUSE.ID = SPOUSERELATIONSHIP.RECIPROCALCONSTITUENTID
          left join dbo.CONSTITUENT PRIMARYEDUCATIONINSTITUTION on PRIMARYEDUCATIONINSTITUTION.ID = PRIMARYEDUCATION.EDUCATIONALINSTITUTIONID
        where RESEARCHGROUPMEMBER.RESEARCHGROUPID = @ID
          and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                 or
                 ( --Site security

                  exists ( select top 1 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(CONSTITUENT.ID) as CSITE
                                where exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=CSITE.[SITEID] or (SITEID is null and CSITE.[SITEID] is null)) 
                             )
                   --Constituent security

                  and ( 
                      dbo.UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_IN_NONRACROLE(@CURRENTAPPUSERID,@SECURITYFEATUREID) = 1
                       or
                      exists(SELECT top 1 1 
                              FROM dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORQUERYVIEW(@CURRENTAPPUSERID,@SECURITYFEATUREID) SEC 
                              where SEC.ID = CONSTITUENT.ID
                            )
                      )
                 )
              )