UFN_QUERY_POLITICALGIFTSBYCONSTITUENT

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

                    with GIFTS_CTE as(
                        select     
                        WPPOLITICALDONATION.ID,
                        WPPOLITICALDONATION.WEALTHID,
                        WPPOLITICALDONATION.CONFIRMED,
                        WPPOLITICALDONATION.CONFIRMEDBYAPPUSERID,
                        WPPOLITICALDONATION.REJECTED,
                        WPPOLITICALDONATION.REJECTEDBYAPPUSERID,
                        WPPOLITICALDONATION.SOURCE,
                        WPPOLITICALDONATION.MC,
                        WPPOLITICALDONATION.CITY,
                        WPPOLITICALDONATION.STATE,
                        WPPOLITICALDONATION.ZIP,
                        WPPOLITICALDONATION.TRANSACTIONDATE,
                        WPPOLITICALDONATION.AMOUNT,
                        WPPOLITICALDONATION.FULLNAME,
                        WPPOLITICALDONATION.OCCUPATION,
              WPPOLITICALDONATION.DN_ORGANIZATIONNAME,
                        WPPOLITICALDONATION.RECURSIVEMATCH,
                            case
                                when WPPOLITICALDONATION.CONFIRMED = 1 then '5'
                                when WPPOLITICALDONATION.REJECTED =  1 then '0'
                                else
                                     isnull(coalesce(CONFIDENCERATING.CONFIDENCE,MATCHCODE.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                            end as CONFIDENCE

                        from
                        dbo.WPPOLITICALDONATION
                        left outer join
                            dbo.MATCHCODE on WPPOLITICALDONATION.MC = MATCHCODE.MATCHCODE
                        left outer join 
                            dbo.WEALTHSOURCE on WPPOLITICALDONATION.SOURCE = WEALTHSOURCE.SOURCE    
                        left outer join
                            dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID 
            where
              WPPOLITICALDONATION.WEALTHID=@ID
                    )
                select
                    GIFTS_CTE.ID,
                    GIFTS_CTE.CONFIRMED,
                    coalesce(nullif(CONFIRMEDAPPUSER.DISPLAYNAME, ''), CONFIRMEDAPPUSER.USERNAME) CONFIRMEDBYUSER, --Confirmed by

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

                    GIFTS_CTE.SOURCE,
                    GIFTS_CTE.MC,
                    MATCHCODE.DESCRIPTION MCDESCRIPTION,
                    GIFTS_CTE.AMOUNT,
                    GIFTS_CTE.CITY,
                    GIFTS_CTE.STATE,
                    GIFTS_CTE.ZIP,
                    GIFTS_CTE.TRANSACTIONDATE,
                    GIFTS_CTE.FULLNAME,
          GIFTS_CTE.DN_ORGANIZATIONNAME,
          GIFTS_CTE.OCCUPATION,
          GIFTS_CTE.CONFIDENCE,
                        case
                            when GIFTS_CTE.CONFIRMED = 1 and GIFTS_CTE.RECURSIVEMATCH = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_check_16.png'
                            when GIFTS_CTE.CONFIRMED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_check_16.png'
                            when GIFTS_CTE.REJECTED = 1 then  'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
                            else
                                case GIFTS_CTE.RECURSIVEMATCH
                                    when '1' then         
                                        case GIFTS_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_star_16.png'
                                            when '2' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_2_star_16.png'    
                                            when '3' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_3_star_16.png'
                                            when '4' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_4_star_16.png'         
                                            when '5' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_16.png'
                                        end 
                                    when '0' then
                                        case GIFTS_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
                        end as STATUSICON

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