UFN_QUERY_SOCIALMEDIAACCOUNTSBYCONSTITUENT

CREATE function [dbo].[UFN_QUERY_SOCIALMEDIAACCOUNTSBYCONSTITUENT]
(
  @ID uniqueidentifier
)
returns table
as
  return 

with SMA_CTE as(
                        select     
                        SOCIALMEDIAACCOUNT.ID,
                        SOCIALMEDIAACCOUNT.CONSTITUENTID,
                        SOCIALMEDIAACCOUNT.CONFIRMED,
                        SOCIALMEDIAACCOUNT.CONFIRMEDBYAPPUSERID,    --Confirmed By

                        SOCIALMEDIAACCOUNT.REJECTED,
                        SOCIALMEDIAACCOUNT.REJECTEDBYAPPUSERID,    --Rejected By

                        INFOSOURCECODE.DESCRIPTION as SOURCE,                --Source

                        SOCIALMEDIAACCOUNT.WPMATCHCODE,                    --Match code


              SOCIALMEDIASERVICE.NAME SERVICE,
              SOCIALMEDIAACCOUNT.USERID,
              SOCIALMEDIAACCOUNT.URL,
              SOCIALMEDIAACCOUNT.EMAILADDRESS,
              SOCIALMEDIAACCOUNTTYPECODE.DESCRIPTION as ACCOUNTTYPE,

                            case
                                when SOCIALMEDIAACCOUNT.CONFIRMED = 1 then '5'
                                when SOCIALMEDIAACCOUNT.REJECTED =  1 then '0'
                                else
                                     isnull(coalesce(CONFIDENCERATING.CONFIDENCE,MATCHCODE.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                            end as CONFIDENCE                        --Confidence


                        from
                          dbo.SOCIALMEDIAACCOUNT
            inner join 
              dbo.SOCIALMEDIASERVICE on SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID = SOCIALMEDIASERVICE.ID
            left outer join
              dbo.SOCIALMEDIAACCOUNTTYPECODE on SOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID = SOCIALMEDIAACCOUNTTYPECODE.ID
                        left outer join
                            dbo.MATCHCODE on SOCIALMEDIAACCOUNT.WPMATCHCODE = MATCHCODE.MATCHCODE
            left outer join 
              dbo.INFOSOURCECODE on SOCIALMEDIAACCOUNT.INFOSOURCECODEID = INFOSOURCECODE.ID
                        left outer join 
                            dbo.WEALTHSOURCE on SOCIALMEDIAACCOUNT.WEALTHSOURCE = WEALTHSOURCE.SOURCE    
                        left outer join
                            dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID 
            where
              SOCIALMEDIAACCOUNT.CONSTITUENTID=@ID
                    )
                select
                    SMA_CTE.ID,
                    SMA_CTE.CONFIRMED,
                    coalesce(nullif(CONFIRMEDAPPUSER.DISPLAYNAME, ''), CONFIRMEDAPPUSER.USERNAME) CONFIRMEDBYUSER, --Confirmed by

                    SMA_CTE.REJECTED,
                    coalesce(nullif(REJECTEDAPPUSER.DISPLAYNAME, ''), REJECTEDAPPUSER.USERNAME) REJECTEDBYUSER, --Rejected by

                    SMA_CTE.SOURCE,                                                    --Source

                    SMA_CTE.WPMATCHCODE as MC,                                                        --Match code

                    MATCHCODE.DESCRIPTION MCDESCRIPTION,        --Match code description


                  SMA_CTE.SERVICE,                    --Social Media Service

                  SMA_CTE.URL,                        --URL

                  SMA_CTE.USERID,                    --USERID

          SMA_CTE.EMAILADDRESS,
          SMA_CTE.ACCOUNTTYPE,

                    SMA_CTE.CONFIDENCE,
                        case
                            when SMA_CTE.CONFIRMED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_check_16.png'
                            when SMA_CTE.REJECTED = 1 then  'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
                            else
                                        case SMA_CTE.CONFIDENCE
                                            when '0' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
                                            when '1' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_1_16.png'
                                            when '2' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_2_16.png'    
                                            when '3' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_3_16.png'
                                            when '4' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_4_16.png'         
                                            when '5' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_16.png'    
                                        end
                        end as STATUSICON

                    /*#EXTENSION*/ 
                from
                    SMA_CTE
                left join dbo.APPUSER CONFIRMEDAPPUSER on CONFIRMEDAPPUSER.ID = SMA_CTE.CONFIRMEDBYAPPUSERID
                left join dbo.APPUSER REJECTEDAPPUSER on REJECTEDAPPUSER.ID = SMA_CTE.REJECTEDBYAPPUSERID
                left join dbo.MATCHCODE on MATCHCODE.MATCHCODE = SMA_CTE.WPMATCHCODE